DIR : /home/kozerus/public_html/ko/ko.pl
/home/kozerus/public_html/ko
#!/usr/bin/perl
#!/usr/bin/perl -w
# 20121202_2300 jk ko_center_win.pl for ole
# 20121127_1130 jk ungrep
# 20121127_1130 jk plan2plans koplan insert,update
# 20121126_0900 jk plan2plans items
# 20121126_0900 jk plan2plans sumplans sumcabs
# 20121126_0900 jk plan2plans
# 20121122_2000 jk sqlq
# 20121122_2000 jk n2l
# 20121122_1900 jk xlsx2csv
# 20121122_1900 jk xls2csv
# 20121120_2200 jk dtmdta
# 20121120_2200 jk db2saic
# 20121120_2200 jk ol2csv
# 20121120_2200 jk csv2db3
# 20121120_2200 jk xls2pdf
# 20121120_2200 jk db2saic
# 20121119_0230 jk db2kxls email & inv
# 20121118_1915 jk db2kxls qbegdt, pbegdt, substt( mycd, 0,1 ); csvout
# 20121116_0600 jk db2kxls range errors
# 20121115_2400 jk dtmdta
# 20121115_2400 jk db2kxls
# 20121111_1200 jk dt2kxls
# 20121107_1830 jk ole_ol_list
# 20121106_1030 jk ole_xls
# 20121106_1030 jk xls2pdf
# 20121106_1030 jk ole_word
# 20121106_1030 jk ol2csv
# 20121106_1030 jk sql_query
use lib "/usr/lib64/perl5";
use File::Basename;
use File::Copy;
use Cwd;
use DBI;
# use Win32::OLE qw(in with);
# use Win32::OLE::Const 'Microsoft Excel';
# use Win32::OLE::Variant;
$dbfile="ko_center.db3";
main();
sub main() {
&startup();
if ( $#ARGV == -1 ) {
print "$progid $version $dttm";
print "\n";
## print "$progid <jobid>\n";
print "$porgid mkwl|catwl|viwl|wimwl|runwl|go|runjob <jobid>\n";
print "\n";
print "$progid mkwl|catwl|viwl|wimwl|runwl|go|runjob <jobid> <sitecode>\n";
print "\n";
print "help_ole \n";
print "\n";
print "pdf2info <pdffil> \n";
print "xls2pdf <xlsfil> \n";
print "ts2csv mkwl|catwl|vimwl|runwl # readonly\n";
print "ts2csv runjob <xlsfil> # readonly\n";
# print "db2kxls \"yyyy-mm-dd|hhhh|hhhh|hr|cd|desc\" # select from tds readonly\n";
# print "db2kxls \"yyyy-mm-dd|hhhh|hhhh|hr|cd|desc\" # select from tds readonly\n";
print "dtmdta <fy> <p> <q> # select from tds readonly\n";
print "db2kxls <fy> <p> <q> # select from tds readonly\n";
print "\n";
print "csv2ts <xlsfil> # update\n";
print "\n";
print "db2doc <docfil> # update\n";
print "\n";
print "xl2csv <xlsfil> # excel to csv\n";
print "ol2csv <csvfil> # outlook to csv\n";
print "ol2db3 <db3fil> # outlook to db3\n";
print "csv2db3 <csvfil> # csv to db3\n";
print "\n";
print "test # xls\n";
print "test1 <xlsfil> # xls\n";
print "test2 <xlsfil> # xls\n";
print "\n";
print "ole_xls\n";
print "ole_xls_saveas\n";
print "ole_ts\n";
print "ole_word\n";
print "ole_ol\n";
print "ole_ol_folders\n";
print "ole_ol_list\n";
print "xls2pdf\n";
print "pdf2info\n";
print "\n";
print "cr_periods # xls\n";
print "cr_hrs # xls\n";
print "cr_ts # xls\n";
print "test # xls\n";
print "save # xls\n";
print "\n";
print "n2l\n";
print "sqlq\n";
print "plan2plans\n";
print "plan2plans sumplans\n";
print "plan2plans sumcabs <planid>\n";
print "plan2plans items <planid>\n";
print "\n";
print "ungrep\n";
return;
}
$option = $ARGV[0];
$option1 = $ARGV[1];
$option2 = $ARGV[2];
$option3 = $ARGV[3];
$option4 = $ARGV[4];
$option5 = $ARGV[5];
$option6 = $ARGV[6];
$option7 = $ARGV[7];
if( "$option" eq "save" ) {
$plfil="ko_center.pl";
$phpfil="ko_center.php";
$db3fil="ko_center.db3";
$plfil_dttm="ko_center_$dttm.pl";
$phpfil_dttm="ko_center_$dttm.php";
$db3fil_dttm="ko_center_$dttm.db3";
$cmd_cp_plfil="cp -p $plfil $plfil_dttm";
$cmd_cp_phpfil="cp -p $phpfil $phpfil_dttm";
$cmd_cp_db3fil="cp -p $db3fil $db3fil_dttm";
printf( "$progid $dttm MSG: save $cmd_cp_plfil \n" );
printf( "$progid $dttm MSG: save $cmd_cp_phpfil \n" );
printf( "$progid $dttm MSG: save $cmd_cp_db3fil \n" );
system( "cp -p $plfil $plfil_dttm" );
system( "cp -p $phpfil $phpfil_dttm" );
system( "cp -p $db3fil $db3fil_dttm" );
exit;
}
if( "$option" eq "ol2db3" ) {
&ol2db3( $option1, $option2, $option3, $option4);
exit;
}
if( "$option" eq "csv2db3" ) {
&csv2db3( $option1, $option2, $option3, $option4);
exit;
}
if( "$option" eq "xl2csv" ) {&xl2csv( $option1, $option2, $option3); exit;}
if( "$option" eq "ol2csv" ) {&ol2csv( $option1, $option2, $option3); exit;}
if( "$option" eq "ole_ol" ) { ole_ol($option1, $option2, $option3); exit;}
if( "$option" eq "ole_ol_list" ) { ole_ol_list($option1, $option2, $option3); exit;}
if( "$option" eq "ole_ol_folders" ) { ole_ol_folders($option1, $option2, $option3); exit;}
if( "$option" eq "ole_xls" ) { ole_xls($option1, $option2, $option3); exit;}
if( "$option" eq "ole_ts" ) { ole_xls_ts($option1, $option2, $option3); exit;}
if( "$option" eq "ole_xls_saveas" ) { ole_xls_saveas($option1, $option2, $option3); exit;}
if( "$option" eq "ole_word" ) { ole_word($option1, $option2, $option3); exit;}
if( "$option" eq "xls2pdf" ) { xls2pdf($option1, $option2, $option3); exit;}
if( "$option" eq "pdf2info" ) { pdf2info($option1, $option2, $option3); exit;}
if( "$option" eq "test" ) { test($option1, $option2, $option3); exit;}
if( "$option" eq "test1" ) { test1($option1, $option2, $option3); exit;}
if( "$option" eq "test2" ) { test2($option1, $option2, $option3); exit;}
if( "$option" eq "test3" ) { test3($option1, $option2, $option3); exit;}
if( "$option" eq "go" ) { go($option1, $option2, $option3); exit;}
if( "$option" eq "ungrep" ) { ungrep($option1, $option2, $option3); exit;}
if( "$option" eq "cr_periods" ) { sql_cr_periods( $sqlbin, $sqldb, "periods" ); exit;}
if( "$option" eq "cr_hrs" ) { sql_cr_hrs($sqlbin, $sqldb, "hrs"); exit;}
if( "$option" eq "cr_ts" ) { sql_cr_ts($sqlbin, $sqldb, "ts"); exit;}
if( "$option" eq "help_ole" ) { help_ole(); exit; }
if( "$option" eq "db2doc" ) {
$docfil=$option1;
db2doc( $option1, $option2, $option3 );
exit;
}
# xls read only
# if( "$option" eq "ts2csv" ) {
# $xlsfil=$option1;
# ts2csv( $option1, $option2, $option3 );
# exit;
# }
# xls update
if( "$option" eq "csv2ts" ) {
$xlsfil=$option1;
csv2ts( $option1, $option2, $option3 );
exit;
}
if( "$option" eq "dt2saic" ) {
## printf( "$progid $dttm MSG_135: [$option1][$option2]\n" );
dt2saic( $option1, $option2, $option3 );
exit;
}
if( "$option" eq "dtmdta" ) {
## printf( "$progid $dttm MSG_135: [$option1][$option2]\n" );
## sub dtmdta( $fy, $p, $q, $begdt, $enddt, $dtm_data, $options );
## dtmdta( $option1, $option2, $option3 );
## dtmdta( $fy, $p, $q, $begdt, $enddt, $dtm_data, $options );
dtmdta( $option1, $option2, $option3, $option4, $option5, $option6, $option7 );
exit;
}
if( "$option" eq "db2saic" ) {
printf( "$progid $dttm MSG_135: [$option1][$option2][$option3][$option4][$option5]\n" );
## db2saic( $option1, $option2, $option3 );
db2saic( $option1, $option2, $option3, $option4, $option5, $option6, $option7 );
exit;
}
if( "$option" eq "db2ts" ) {
## printf( "$progid $dttm MSG_145: [$option1][$option2]\n" );
db2ts( $option1, $option2, $option3 );
exit;
}
if( "$option" eq "n2l" ) {
$n=$option1;
$l=n2l( $n );
printf( "$progid $dttm n2l MSG_205: n=[$n] [$l]\n" );
exit;
}
if( "$option" eq "sqlq" ) {
$sqlcmd=q{ select * from kohw_hrc_defrag; };
if( "$option1" eq "1" ) {
$sqlcmd=q{ select myid,mydt,planid,cab,cab0,cab1,amps from kohw_hrc_defrag; };
}
$rows=sqlq( $sqldb, $sqlcmd, $sqltbl );
printf( "$progid $dttm sqlq MSG_225: sqlcmd=[$sqlcmdn] [$rows]\n" );
exit;
}
if( "$option" eq "plan2plans" ) {
$status=plan2plans( $option1, $option2, $option3 );
printf( "$progid $dttm plan2plans MSG_210: n=[$n] [$l]\n" );
exit;
}
if( "$option" eq "xls2csv" ) {
$xlsfil=$option1;
printf( "$progid $dttm xls2csv MSG_211: [$option1][$option2]\n" );
xlsx2csv( $xlsfil );
# xls2csv_runjob( $xlsfil );
exit;
}
if( "$option" eq "xlsx2csv" ) {
$xlsxfil=$option1;
printf( "$progid $dttm xlsx2csv MSG_200: [$option1][$option2]\n" );
xlsx2csv( $xlsxfil );
exit;
}
if( "$option" eq "ts2csv" ) {
$ts2csv_wlfile="ko_ole_ts2csv.wl";
$ts2csv_wlfile="$progid.wl";
# printf( "ts2csv $dttm $uname MSG_100: BEG wlfile=[$ts2csv_wlfile] ==============================\n" );
# printf( "xls2csv $dttm $uname MSG: BEG xls2csv_logfile=[$xls2csv_logfile] ==============================\n" );
if( "$option1" eq "mkwl" ) {
# wlfile format=e:/perms/ftp/pris/ihv12/a2i/todo_a2i_A00002.csv
# $cmd="ls j:/kxls/weekly/*.xls > $ts2csv_wlfile";
$cmd="ls j:/kxls/oy3/*.xls > $ts2csv_wlfile";
print( "ipc ts2csv $uname MSG: cmd=[$cmd]\n" );
print( "ipc ts2csv mkwl $dttm $uname MSG: cmd=[$cmd]\n");
print( "ipc ts2csv MKWL BUGS $dttm $uname MSG: cmd=[$cmd]\n");
system( $cmd );
# &ts2csv_mkwl();
# save_wlfile( $ts2csv_wlfile );
exit;
}
if( "$option1" eq "vimwl" ) {
$vi="vim";
$cmd="$vi $ts2csv_wlfile";
print( "$progid ts2csv vimwl $dttm MSG_BEG: cmd=[$cmd]\n");
system( $cmd );
print( "$progid ts2csv vimwl $dttm MSG_END: cmd=[\n$cmd\n]\n");
exit;
}
if( "$option1" eq "catwl" ) {
$cmd="cat $ts2csv_wlfile";
print( "$progid ts2csv catwl $dttm MSG_BEG: cmd=[$cmd]\n");
system( $cmd );
print( "$progid ts2csv catwl $dttm MSG_END: cmd=[$cmd]\n");
exit;
}
if( "$option1" eq "runwl" ) {
# $ts2csv_wlfile="$option2";
# print( "$progid ts2csv runwl $dttm MSG_145: ts2csv_wlfile=[$ts2csv_wlfile]\n" );
&ts2csv_runwl($ts2csv_wlfile);
exit;
}
if( "$option1" eq "runjob" ) {
$xlsfil=$option2;
&ts2csv_runjob($xlsfil);
exit;
}
if( "$option1" eq "go" ) {
print( "ipc xls2csv go ERR: command pending\n" );
# &xls2csv_runjob();
exit;
}
}
if( "$option" eq "pipe" ) { while(<STDIN>) { &pipe($_); } exit;}
if( "$option" eq "pipe_age" ) { while(<STDIN>) { &pipe_age($_); } exit;}
if( "$option" eq "datefmt" ) {
while(<STDIN>) {
&pipe_datefmt($_);
}
exit;
}
print( "$progid main go ERR: unsupported command [$option] [$option1][$option2]\n" );
}
sub startup() {
$progid="ko_center";
$version="1.0";
$dttm=&dttm;
$xls_templet="J:/ko/bin/ts.xls";
$xls_templet="J:/kxls/oy3/ko_center_templet.xls";
$cwd = getcwd();
$period_range="J3:J3";
$title_range="J3:J3";
$title_range2="J6:J6";
$sat_range="B8:E8";
$sun_range="F8:I8";
$mon_range="J8:M8";
$tue_range="N8:Q8";
$wed_range="R8:U8";
$thr_range="V8:Y8";
$fri_range="Z8:AB8";
$satdt_range="B8:E8";
$sundt_range="F8:I8";
$mondt_range="J8:M8";
$tuedt_range="N8:Q8";
$weddt_range="R8:U8";
$thrdt_range="V8:Y8";
$fridt_range="Z8:AB8";
# $t1[$daynum][$slot];
# $t2[$daynum][$slot];
# $t12[$daynum][$slot];
# $code[$daynum][$slot];
@week_cols_h1 = qw!B F J N R V Z!;
@week_cols_h2 = qw!C G K O S W AA!;
@week_cols_tm = qw!D H L P T X AB!;
@week_cols_cd = qw!E I M Q U Y AC!;
$day_row=8;
$dt_row=9;
@time_rows = qw!12 13 14 15 16 17 18 19 20 21!;
$Win32::OLE::Warn = 3; # die on errors...
$sqlbin="j:/ko/bin/sqlite3";
## $sqldb="s:/xampp/htdocs/ko_center/ko_center.db3";
$sqldb="/var/www/html/ko_center/ko_center.db3";
}
sub test( $option1, $option2, $option3 ) {
$option1=shift; $option2=shift; $option3=shift;
printf( "$progid test $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n");
}
sub test1( $option1, $option2, $option3 ) {
$option1=shift; $option2=shift; $option3=shift;
printf( "$progid test $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n");
}
sub test2( $option1, $option2, $option3 ) {
$option1=shift; $option2=shift; $option3=shift;
printf( "$progid test $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n");
}
sub plan2plans( $option1, $option2, $option3 ) {
$option1=shift; $option2=shift; $option3=shift;
if( $option1 eq "" ) {
$planid="";
plan2plans_plans();
return;
}
if( $option1 eq "plans" ) {
$planid=$option2;
plan2plans_plans( $planid );
return;
}
if( $option1 eq "cabs" ) {
$planid=$option2;
plan2plans_cabs($planid);
return;
}
if( $option1 eq "items" ) {
$planid=$option2;
plan2plans_items($planid);
return;
}
if( $option1 eq "cabamps" ) {
$planid=$option2;
plan2plans_cabamps($planid);
return;
}
if( $option1 eq "update" ) {
$planid=$option2;
plan2plans_update($sqldb, $sqlcmd, $sqltbl );
return;
}
if( $option1 eq "insert" ) {
$planid=$option2;
$myid=""; $mydt=""; $loadmode="a"; $cabnum=0; $cablim=0; $cab="cab"; $cab1="cab1"; $cab2="cab2";
plan2plans_insert($sqldb, $sqltbl, $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $amps );
return;
}
# plan2plans_update( $sqldb, $sqlcmd, $sqltbl );
if( $option1 eq "go" ) {
$planid=$option2;
# printf( "$progid $dttm MSG_440: planid=[$planid] option1=[$option1]\n" );
plan2plans_go( $planid );
return;
}
printf( "$progid $dttm plan2plans ERR_440: option1=[$option1] option2=[$option2]\n" );
return;
}
sub plan2plans_go( $planid) {
$planid=shift;
$itemsnum=0; $itemslim="";
$cabsnum=0; $cabslim="";
$sampsnum=0; $sampslim="";
$samps_limit="300";
printf( "$progid $dttm plan2plans_go MSG_450: planid=[$planid]\n" );
# get list of items
$sqlcmd_select_items = "select myid,mydt,planid,loadmode,cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_from_items = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where_items = sprintf( "where loadmode == '' " );
}
else {
$sqlcmd_where_items = sprintf( "where planid == \"%s\" and loadmode == '' ", $planid );
}
$sqlcmd_order_items = "order by cast( amps as real ) desc, item, cab, cab1, cab2 ";
if( $itemslim eq "" ) {
$sqlcmd_limit = "";
}
else {
$sqlcmd_limit = "limit $itemslim ";
}
$sqlcmd_group_items="";
$sqlcmd_items = $sqlcmd_select_items;
$sqlcmd_items .= $sqlcmd_from_items;
$sqlcmd_items .= $sqlcmd_where_items;
$sqlcmd_items .= $sqlcmd_order_items;
$sqlcmd_items .= $sqlcmd_group_items;
$rows_items= sqlq( $sqldb, $sqlcmd_items, $sqltbl );
$items_limit=0;
$items_num=0;
foreach my $row_items (@$rows_items ) {
$items_limit++;
$items_num++;
( $myid,$mydt,$planid,$loadmode,$cabnum,$cablim,$cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip ) = @$row_items;
## printf( "$myid,$mydt,$planid,$loadmode,$cabnum,$cablim,$cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip\n" );
# printf( "$progid ITEMS: $planid|$items_num|$loadmode|$cabnum|$cablim|$cab|$cab1|$cab2|$item|$amps\n" );
}
printf( "$progid $dttm plan2plans_go MSMG_490: items_limit=[$items_limit] sqlcmd_items=[$sqlcmd_items]\n" );
# get list of cabs
$sqlcmd_select_cabs = "select myid,mydt,planid,loadmode,cabnum,cablim,cab,cab1,cab2,sum(amps) samps ";
$sqlcmd_from_cabs = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where loadmode == '' " );
}
else {
$sqlcmd_where = sprintf( "where planid == \"%s\" and loadmode == '' ", $planid );
}
$sqlcmd_group_cabs = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2 ";
$sqlcmd_order_cabs = "order by myid, mydt, planid, loadmode, cabnum, cablim, samps desc, cab, cab1, cab2 ";
if( $cabslim eq "" ) {
$sqlcmd_limit_cabs="";
}
else {
$sqlcmd_limit_cabs = "limit $cabslim ";
}
$sqlcmd_where_cabs = "where planid == \"$planid\" and loadmode == '' ";
$sqlcmd_cabs = $sqlcmd_select_cabs . $sqlcmd_from_cabs . $sqlcmd_where_cabs . $sqlcmd_group_cabs . $sqlcmd_order_cabs . $sqlcmd_limit_cabs;
$rows_cabs=sqlq( $sqldb, $sqlcmd_cabs, $sqltbl );
$cabs_limit=0; $cabs_num=0;
foreach my $row_cabs (@$rows_cabs) {
$cabs_limit++; $cabs_num++;
( $myid,$mydt,$planid,$loadmode,$cabnum,$cablim,$cab,$cab1,$cab2,$samps ) = @$row_cabs;
if( $samps >= $samps_limit ) {
# printf( "$progid cabs_FULL: $cabs_num|$myid|$mydt|$planid|$loadmode|$cabnum|$cablim|$cab|$cab1|$cab2|$samps\n" );
}
else {
# printf( "$progid cabs_open: $cabs_num|$myid|$mydt|$planid|$loadmode|$cabnum|$cablim|$cab|$cab1|$cab2|$samps\n" );
}
}
printf( "$progid $dttm MSG_510: items_limit=[$items_limit]\n" );
printf( "$progid $dttm MSG_510: cabs_limit=[$cabs_limit]\n" );
printf( "$progid $dttm MSG_510: samps_limit=[$samps_limit]\n" );
# create plan templets foreach loadmode a,b cabnum<cablim
## $cabs_limit=4;
## $loadmode="b";
$createplans="false";
# delete preexisting plan templets with loadmode not blanke
$sqlcmd_delete_koplans_loadmode = "delete from koplans where planid=='$planid' and loadmode != ''";
if( $createplans eq "true" ) {
$status=sqldo( $sqldb, $sqlcmd_delete_koplans_loadmode, $sqltbl );
printf( "$progid $dttm MSG_552: [$status] sqlcmd_delete_koplans_loadmode=[$sqlcmd_delete_koplans_loadmode]\n" );
}
## $sqlcmd_cr_koplan = "create table if not exists koplan select * from koplans where planid=='$planid' and loadmode==''";
for( $plan_cabslim=1; $plan_cabslim<=($cabs_limit); $plan_cabslim++ ) {
if( $createplans eq "true" ) {
$sqlcmd_cr_koplan = "create table if not exists koplan as select * from koplans where planid=='$planid' and loadmode=='' ";
$sqlcmd_insert_koplan = "insert into koplan select * from koplans where planid=='$planid' and loadmode=='' ";
$sqlcmd_drop_koplan = "drop table if exists koplan";
$sqlcmd_delete_koplans_loadmode = "delete from koplans where planid=='$planid' and loadmode != '' ";
$sqlcmd_update_koplan_cabsnum = "update koplan set cabnum='$plan_cabslim' where planid=='$planid' and loadmode=='' ";
$sqlcmd_update_koplan_cabslim = "update koplan set cablim='$cabs_limit' where planid=='$planid' and loadmode=='' ";
$sqlcmd_update_koplan_cabs2 = "update koplan set cab2='$cabs2' where planid=='$planid' ";
$sqlcmd_update_koplan_loadmode = "update koplan set loadmode='$loadmode' where planid=='$planid' and loadmode=='' ";
$sqlcmd_update_koplan_loadmode_a = "update koplan set loadmode='a' where planid=='$planid' and loadmode=='' ";
$sqlcmd_update_koplan_loadmode_b = "update koplan set loadmode='b' where planid=='$planid' and loadmode=='a' ";
$sqlcmd_insert_koplans = "insert into koplans select * from koplan where planid=='$planid' and loadmode!='' ";
# printf( "$progid $dttm MSG_550: [$planid] [$loadmode][$plan_cabslim][$cabs_limit] [$cab][$cab1][$cab2]=[$sqlcmd_insert_koplans]\n" );
$status=sqldo( $sqldb, $sqlcmd_cr_koplan, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_cr_koplan=[$sqlcmd_cr_koplan]\n" );
$status=sqldo( $sqldb, $sqlcmd_insert_koplan, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_insert_koplan=[$sqlcmd_insert_koplan]\n" );
$status=sqldo( $sqldb, $sqlcmd_update_koplan_cabsnum, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_update_koplan_cabsnum=[$sqlcmd_update_koplan_cabsnum]\n" );
$status=sqldo( $sqldb, $sqlcmd_update_koplan_cabslim, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_update_koplan_cabslim=[$sqlcmd_update_koplan_cabslim]\n" );
$status=sqldo( $sqldb, $sqlcmd_update_koplan_loadmode_a, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_update_koplan_loadmode_a=[$sqlcmd_update_koplan_loadmode_a]\n" );
$status=sqldo( $sqldb, $sqlcmd_insert_koplans, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
$status=sqldo( $sqldb, $sqlcmd_update_koplan_loadmode_b, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_update_koplan_loadmode_b=[$sqlcmd_update_koplan_loadmode_b]\n" );
$status=sqldo( $sqldb, $sqlcmd_insert_koplans, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
$status=sqldo( $sqldb, $sqlcmd_drop_koplan, $sqltbl );
printf( "$progid $dttm MSG_550: [$status] sqlcmd_cr_koplan=[$sqlcmd_cr_koplan]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplan=[$sqlcmd_insert_koplan]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_cr_koplan=[$sqlcmd_cr_koplan]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_drop_koplan=[$sqlcmd_drop_koplan]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_update_koplan_cabsnum=[$sqlcmd_update_koplan_cabsnum]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_update_koplan_cabs2=[$sqlcmd_update_koplan_cabs2]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_update_koplan_loadmode=[$sqlcmd_update_koplan_loadmode]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
printf( "$progid $dttm MSG_550: sqlcmd_insert_koplans=[$sqlcmd_insert_koplans]\n" );
}
}
# process plans
for( $plan_cabslim=1; $plan_cabslim<=$cabs_limit; $plan_cabslim++ ) {
$cablim=$cabs_limit;
$cabnum=$plan_cabslim;
if( $cabnum == '05' ) { last; }
printf( "$progid $dttm plan2plans_go MSG_620: cabnum=[$cabnum] cablim=[$cablim]\n" );
$cabnum02 = sprintf( "%02d", $cabnum );
$sqlcmd_items = "select myid,mydt,planid,loadmode,cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_items .= "from koplans where planid =='$planid' and cabnum =='$cabnum02' and loadmode == 'b' ";
$sqlcmd_items .= "order by planid, loadmode, cabnum,cablim, cast( amps as real ) desc, cab,cab1,cab2 ";
$rows_items = sqlq( $sqldb, $sqlcmd_items, $sqltbl );
$items_num=0; $items_limit=0;
printf( "$progid $dttm plan2plans_go MSG_680: cabnum=[$cabnum] cablim=[$cablim] sqlcmd_items=[$sqlcmd_items]\n" );
$rows_items = sqlq( $sqldb, $sqlcmd_items, $sqltbl );
foreach my $row_items (@$rows_items ) {
$items_num++;
$items_limit++;
( $myid,$mydt,$items_planid,$items_loadmode,$items_cabnum,$items_cablim,$items_cab,$items_cab1,$items_cab2,$items_item,$items_amps,$items_sn,$items_hostname,$items_hrctag,$items_os,$items_ip ) = @$row_items;
## printf( "$myid,$mydt,$planid,$loadmode,$cabnum,$cablim,$cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip\n" );
## printf( "$progid $dttm ITEMS_630: $items_planid|$items_loadmode|$items_cabnum|$items_cablim|$items_cab|$items_cab1|$items_cab2|$items_item|$items_amps \n" );
$sqlcmd_select_cabs = "select myid,mydt,planid,loadmode,cabnum,cablim,cab,cab1,cab2,sum(amps) samps ";
$sqlcmd_from_cabs = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where loadmode == 'b' " );
}
else {
$sqlcmd_where = sprintf( "where planid == '$planid' and loadmode == 'b' and cabnum=='$cabnum' and cablim=='$cablim' " );
}
$sqlcmd_group_cabs = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2 ";
$sqlcmd_order_cabs = "order by myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2 ";
if( $cabslim eq "" ) {
$sqlcmd_limit_cabs="";
}
else {
$sqlcmd_limit_cabs = "limit $cabslim ";
}
$cabnum2 = sprintf( "%02d", $cabnum );
$sqlcmd_where_cabs = "where planid == '$planid' and loadmode == 'b' and cabnum=='$cabnum2' ";
$sqlcmd_cabs = $sqlcmd_select_cabs . $sqlcmd_from_cabs . $sqlcmd_where_cabs . $sqlcmd_group_cabs . $sqlcmd_order_cabs . $sqlcmd_limit_cabs;
$rows_cabs=sqlq( $sqldb, $sqlcmd_cabs, $sqltbl );
$cabs_limit=0; $cabs_num=0;
## printf( "$progid $dttm ITEMS_656: $items_planid|$items_loadmode|$items_cabnum|$items_cablim|$items_cab|$items_cab1|$items_cab2|$items_item|$items_amps \n" );
## printf( "$progid $dttm ITEMS_659: sqlcmd_cabs=[$sqlcmd_cabs]\n" );
foreach my $row_cabs (@$rows_cabs) {
if( $cabnum > $cablim ) { last; }
printf( "$progid $dttm ITEMS_660: $items_planid|$items_loadmode|$items_cabnum|$items_cablim|$items_cab|$items_cab1|$items_cab2|$items_item|$items_amps \n" );
$cabs_limit++; $cabs_num++;
( $cabs_myid,$cabs_mydt,$cabs_planid,$cabs_loadmode,$cabs_cabnum,$cabs_cablim,$cabs_cab,$cabs_cab1,$cabs_cab2,$cabs_samps ) = @$row_cabs;
printf( "$progid $dttm MSG_668_CABS: [$cabs_planid][$cabs_loadmode][$cabs_cabnum][$cabs_cablim] [$cabs_cab][$cabs_cab1][$cabs_cab2]\n" );
$new_cabs_samps = $cabs_samps + $items_amps;
printf( "$progid $dttm MSG_669_CABS: new_cabs_samps=[$new_cabs_samps] cabs_samps=[$cabs_samps] items_amps=[$items_amps]\n" );
if( $new_cabs_samps >= $samps_limit ) {
printf( "$progid cabs_FULL_670: $cabs_num|$cabs_myid|$cabs_mydt|$cabs_planid|$cabs_loadmode|$cabs_cabnum|$cabs_cablim|$cabs_cab|$cabs_cab1|$cabs_cab2|$cabs_samps\n" );
}
else {
printf( "$progid cabs_open_671: $cabs_num|$cabs_myid|$cabs_mydt|$cabs_planid|$cabs_loadmode|$cabs_cabnum|$cabs_cablim|$cabs_cab|$cabs_cab1|$cabs_cab2|$cabs_samps\n" );
$cabnum2=sprintf( "%02d", $cabnum );
$sqlcmd_update_koplans_cab2="update koplans set cab2='$cabs_cab' where planid=='$planid' and loadmode == 'b' and cabnum == '$cabnum2' and cablim == '$cablim' and item == '$items_item' ";
$status = sqldo( $sqldb, $sqlcmd_update_koplans_cab2, $sqltbl );
printf( "$progid cabs_open_681: [$status] sqlcmd_update_koplans_cab2=[$sqlcmd_update_koplans_cab2]\n" );
$sqlcmd_update_koplans_cab="update koplans set cab='$cabs_cab' where planid=='$planid' and loadmode == 'b' and cabnum == '$cabnum2' and cablim == '$cablim' and item == '$items_item' ";
$status = sqldo( $sqldb, $sqlcmd_update_koplans_cab, $sqltbl );
printf( "$progid cabs_open_682: [$status] sqlcmd_update_koplans_cab=[$sqlcmd_update_koplans_cab]\n" );
last;
}
}
}
}
return;
}
sub plan2plans_items1( $planid) {
$planid=shift;
printf( "$progid $dttm plan2plans_items1 MSG_730: planid=[$planid] RETURN\n" );
exit;
_
# get list of items
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_from = "from koplans ";
## $sqlcmd_where = sprintf( "where planid like \"%c%s%c\" ", '%', $planid, '%' );
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "" );
}
else {
$sqlcmd_where = sprintf( "where planid like \"\%%s\%\" ", $planid );
}
$sqlcmd_order = "order by amps desc, item";
$sqlcmd_items = $sqlcmd_select . $sqlcmd_from . $sqlcmd_where . $sqlcmd_order;
printf( "$progid $dttm plan2plans_items MSG_460: planid=[$planid] sqlcmd_items=[$sqlcmd_items] sqldb=[$sqldb]\n");
return;
$rows_items=sqlq( $sqldb, $sqlcmd_items, $sqltbl );
printf( "$progid $dttm plan2plans MSG_470: sqlcmd_items=[$sqlcmd_items]\n" );
## $j=0;
## foreach $myrow (@myrows) { printf( "$j++ myrow=[$myrow]\n" ); }
## exit;
my $j=0;
foreach my $row_items (@$rows_items) {
my ( $myid, $mydt, $planid,$loadmode,$cabnum,$cablim, $cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip ) = @$row_items;
## printf( "$progid sqlq MSG_405: myid=[$myid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$item][$amps] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
## printf( "$progid sqlq MSG_406: planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$item][$amps] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
## printf( "$progid sqlq MSG_407: planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$cab1]->[$cab2] [$item][$amps]\n" );
}
}
sub plan2plans_cabamps( $sqldb, $sqlcmd, $sqltbl ) {
$sqldb=shift; $sqlcmd=shift; $sqltbl=shift;
$mycab="cz42";
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim, cab, cab1, cab2, sum(amps samps ";
$sqlcmd_from = "from koplans ";
## $sqlcmd_where = sprintf( "where planid == \"%c%s%c\" and cab2 = \"%s\" ", '%', $planid, '%', $mycab );
$sqlcmd_where = sprintf( "where planid like \"%s\" and cab2 = \"%s\" ",$planid, $mycab );
$sqlcmd_groupby = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab1, cab2 ";
$sqlcmd_order = "order by myid, mydt, planid, loadmode, cabnum, cablim, cab1, cab2, samps desc ";
$sqlcmd_cab = $sqlcmd_select;
$sqlcmd_cab .= $sqlcmd_from;
$sqlcmd_cab .= $sqlcmd_where . $sqlcmd_groupby . $sqlcmd_order;
$rows_cab = sqlq( $sqldb, $sqlcmd_cabs, $sqltbl );
my $j=0;
foreach my $row_cab (@$rows_cab) {
my ( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $samps ) = @$row_cabs;
## printf( "$progid sqlq MSG_420: myid=[$miid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab] [$cab1][$cab2] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
printf( "$progid sqlq MSG_420: planid=[$planid][$loadmode][$cabnum][$cablim] [$cab][$cab1][$cab2] samps=[$samps]\n" );
}
}
sub plan2plans_update( $sqldb, $sqlcmd, $sqltbl ) {
$sqldb=shift; $sqlcmd=shift; $sqltbl=shift;
printf( "$progid $dttm plan2plans_update MSG_123: disabled sqlcmd=[$sqlcmd]\n" );
return;
$sqlcmd="update koplans set cab1=cab";
printf( "$progid sqlq MSG_440: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
$status=sqldo( $sqldb, $sqlcmd, $sqltbl );
printf( "$progid sqlq MSG_450: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
## $db->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')");
$sqlcmd_insert = " insert into koplans ";
$sqlcmd_insert_flds = "( myid, mydt, planid, loadmoad, cabnum, cablim, cab, cab1, cab2, item ) ";
$sqlcmd_insert_values = "( '$myid', '$mydt', '$planid', '$loadmode', '$cabnum','$cablim', '$cab', '$cab1', '$cab2', '$item' ); ";
$sqlcmd_insert = "insert into koplans ";
$sqlcmd_insert .= $sqlcmd_insert_flds;
$sqlcmd_insert .= $sqlcmd_insert_values;
$status = sqldo( $sqlcmd_insert );
printf( "$progid $dttm plan2plans MSG_460: sqlcmd_insert=[$sqlcmd_insert]\n" );
# $sqlcmd=qq{ select id,dttm, user, host, info, homedir, spwchg, spwchg_dt, spwchg_age from us };
}
sub plan2plans_insert( $sqldb, $sqltbl, $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $amps ) {
$sqldb=shift; $sqltbl=shift;
$myid-shift; $mydt=shift; $planid=shift; $loadmode=shift; $cabnum=shift; $cablim=shift; $cab=shift; $cab1=shift; $cab2=shift; $amps=shift;
printf( "$progid $dttm plan2plans_update MSG_123: disabled sqlcmd=[$sqlcmd]\n" );
return;
$sqlcmd="update koplans set cab1=cab";
$sqlcmd_insert = "insert or replace into $sqltbl ";
$sqlcmd_insert_fields = "( myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2, amps ) ";
$sqlcmd_insert_values = "( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $amps ) ";
printf( "$progid sqlq MSG_440: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
$status=sqldo( $sqldb, $sqlcmd, $sqltbl );
printf( "$progid sqlq MSG_450: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
## $db->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')");
$sqlcmd_insert = " insert into koplans ";
$sqlcmd_insert_flds = "( myid, mydt, planid, loadmoad, cabnum, cablim, cab, cab1, cab2, item ) ";
$sqlcmd_insert_values = "( '$myid', '$mydt', '$planid', '$loadmode', '$cabnum','$cablim', '$cab', '$cab1', '$cab2', '$item' ); ";
$sqlcmd_insert = "insert into koplans ";
foreach my $row_cabs (@$rows_cabs) {
if( $cabnum >= $cablim ) { last; }
( $cabs_myid,$cabs_mydt,$cabs_planid,$cabs_loadmode,$cabs_cabnum,$cabs_cablim,$cabs_cab,$cabs_cab1,$cabs_cab2,$cabs_samps ) = @$row_cabs;
if( $cabs_samps > $samps_limit ) {
printf( "$progid cabs_FULL_701: $cabs_num|$cabs_myid|$cabs_mydt|$cabs_planid|$cabs_loadmode|$cabs_cabnum|$cabs_cablim|$cabs_cab|$cabs_cab1|$cabs_cab2|$cabs_samps\n" );
}
else {
printf( "$progid cabs_open_702: $cabs_num|$cabs_myid|$cabs_mydt|$cabs_planid|$cabs_loadmode|$cabs_cabnum|$cabs_cablim|$cabs_cab|$cabs_cab1|$cabs_cab2|$cabs_samps\n" );
$sqlcmd_update_koplans_cab2="update koplans set cab2=cab where planid=='$planid' and loadmode=='b' and cabnum=='$cabnum' and cablim=='$cablim' ";
}
}
return;
}
sub plan2plans_items1( $planid) {
$planid=shift;
printf( "BUGS_450 plan2plans_items planid=[$planid] RETURN\n" );
exit;
return;
_
# get list of items
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_from = "from koplans ";
## $sqlcmd_where = sprintf( "where planid like \"%c%s%c\" ", '%', $planid, '%' );
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "" );
}
else {
$sqlcmd_where = sprintf( "where planid like \"\%%s\%\" ", $planid );
}
$sqlcmd_order = "order by amps desc, item";
$sqlcmd_items = $sqlcmd_select . $sqlcmd_from . $sqlcmd_where . $sqlcmd_order;
printf( "$progid $dttm plan2plans_items MSG_460: planid=[$planid] sqlcmd_items=[$sqlcmd_items] sqldb=[$sqldb]\n");
return;
$rows_items=sqlq( $sqldb, $sqlcmd_items, $sqltbl );
printf( "$progid $dttm plan2plans MSG_470: sqlcmd_items=[$sqlcmd_items]\n" );
## $j=0;
## foreach $myrow (@myrows) { printf( "$j++ myrow=[$myrow]\n" ); }
## exit;
my $j=0;
foreach my $row_items (@$rows_items) {
my ( $myid, $mydt, $planid,$loadmode,$cabnum,$cablim, $cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip ) = @$row_items;
## printf( "$progid sqlq MSG_405: myid=[$myid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$item][$amps] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
## printf( "$progid sqlq MSG_406: planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$item][$amps] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
## printf( "$progid sqlq MSG_407: planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab][$cab1]->[$cab2] [$item][$amps]\n" );
}
}
sub plan2plans_cabamps( $sqldb, $sqlcmd, $sqltbl ) {
$sqldb=shift; $sqlcmd=shift; $sqltbl=shift;
$mycab="cz42";
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim, cab, cab1, cab2, sum(amps samps ";
$sqlcmd_from = "from koplans ";
## $sqlcmd_where = sprintf( "where planid == \"%c%s%c\" and cab2 = \"%s\" ", '%', $planid, '%', $mycab );
$sqlcmd_where = sprintf( "where planid like \"%s\" and cab2 = \"%s\" ",$planid, $mycab );
$sqlcmd_groupby = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab1, cab2 ";
$sqlcmd_order = "order by myid, mydt, planid, loadmode, cabnum, cablim, cab1, cab2, samps desc ";
$sqlcmd_cab = $sqlcmd_select;
$sqlcmd_cab .= $sqlcmd_from;
$sqlcmd_cab .= $sqlcmd_where . $sqlcmd_groupby . $sqlcmd_order;
$rows_cab = sqlq( $sqldb, $sqlcmd_cabs, $sqltbl );
my $j=0;
foreach my $row_cab (@$rows_cab) {
my ( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $samps ) = @$row_cabs;
## printf( "$progid sqlq MSG_420: myid=[$miid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] cab=[$cab] [$cab1][$cab2] cab1=[$cab1][$cab2] hostname=[$hostname] sn=[$sn] hrctag=[$hrctag] os=[$os] ip=[$ip]\n" );
printf( "$progid sqlq MSG_420: planid=[$planid][$loadmode][$cabnum][$cablim] [$cab][$cab1][$cab2] samps=[$samps]\n" );
}
}
sub plan2plans_update( $sqldb, $sqlcmd, $sqltbl ) {
$sqldb=shift; $sqlcmd=shift; $sqltbl=shift;
printf( "$progid $dttm plan2plans_update MSG_123: disabled sqlcmd=[$sqlcmd]\n" );
return;
$sqlcmd="update koplans set cab1=cab";
printf( "$progid sqlq MSG_440: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
$status=sqldo( $sqldb, $sqlcmd, $sqltbl );
printf( "$progid sqlq MSG_450: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
## $db->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')");
$sqlcmd_insert = " insert into koplans ";
$sqlcmd_insert_flds = "( myid, mydt, planid, loadmoad, cabnum, cablim, cab, cab1, cab2, item ) ";
$sqlcmd_insert_values = "( '$myid', '$mydt', '$planid', '$loadmode', '$cabnum','$cablim', '$cab', '$cab1', '$cab2', '$item' ); ";
$sqlcmd_insert = "insert into koplans ";
$sqlcmd_insert .= $sqlcmd_insert_flds;
$sqlcmd_insert .= $sqlcmd_insert_values;
$status = sqldo( $sqlcmd_insert );
printf( "$progid $dttm plan2plans MSG_460: sqlcmd_insert=[$sqlcmd_insert]\n" );
# $sqlcmd=qq{ select id,dttm, user, host, info, homedir, spwchg, spwchg_dt, spwchg_age from us };
}
sub plan2plans_insert( $sqldb, $sqltbl, $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $amps ) {
$sqldb=shift; $sqltbl=shift;
$myid-shift; $mydt=shift; $planid=shift; $loadmode=shift; $cabnum=shift; $cablim=shift; $cab=shift; $cab1=shift; $cab2=shift; $amps=shift;
printf( "$progid $dttm plan2plans_update MSG_123: disabled sqlcmd=[$sqlcmd]\n" );
return;
$sqlcmd="update koplans set cab1=cab";
$sqlcmd_insert = "insert or replace into $sqltbl ";
$sqlcmd_insert_fields = "( myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2, amps ) ";
$sqlcmd_insert_values = "( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $amps ) ";
printf( "$progid sqlq MSG_440: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
$status=sqldo( $sqldb, $sqlcmd, $sqltbl );
printf( "$progid sqlq MSG_450: status==[$status] sqlcmd=[$sqlcmd] sqldb=[$sqldb]\n" );
## $db->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')");
$sqlcmd_insert = " insert into koplans ";
$sqlcmd_insert_flds = "( myid, mydt, planid, loadmoad, cabnum, cablim, cab, cab1, cab2, item ) ";
$sqlcmd_insert_values = "( '$myid', '$mydt', '$planid', '$loadmode', '$cabnum','$cablim', '$cab', '$cab1', '$cab2', '$item' ); ";
$sqlcmd_insert = "insert into koplans ";
$sqlcmd_insert .= $sqlcmd_insert_flds;
$sqlcmd_insert .= $sqlcmd_insert_values;
$status = sqldo( $sqlcmd_insert );
printf( "$progid $dttm plan2plans MSG_460: sqlcmd_insert=[$sqlcmd_insert]\n" );
# $sqlcmd=qq{ select id,dttm, user, host, info, homedir, spwchg, spwchg_dt, spwchg_age from us };
}
sub plan2plans_plans($planid) {
$planid=shift;
# get plan summary beg
if( $planid eq "" ) {
$sqlcmd_plans_cab1 = "select planid, sum(amps) samps from koplans where cab==cab1 group by planid order by planid";
}
else {
$sqlcmd_plans_cab1 = "select planid, sum(amps) samps from koplans where cab==cab1 and planid like \"\%$planid\%\" group by planid order by planid";
}
$rows_plans_cab1 = sqlq( $sqldb, $sqlcmd_plans_cab1, $sqltbl );
foreach my $row_plans_cab1 (@$rows_plans_cab1) {
my ( $planid, $samps ) = @$row_plans_cab1;
printf( "$progid sqlq MSG_501: cab1 planid=[$planid] samps=[$samps]\n" );
}
if( $planid eq "" ) {
$sqlcmd_plans_cab2 = "select planid, sum(amps) samps from koplans where cab==cab2 group by planid order by planid";
}
else {
$sqlcmd_plans_cab2 = "select planid, sum(amps) samps from koplans where cab==cab2 and planid like \"\%$planid\%\" group by planid order by planid";
}
$rows_plans_cab2 = sqlq( $sqldb, $sqlcmd_plans_cab2, $sqltbl );
foreach my $row_plans_cab2 (@$rows_plans_cab2) {
my ( $planid, $samps ) = @$row_plans_cab2;
printf( "$progid sqlq MSG_502: cab2 planid=[$planid] samps=[$samps]\n" );
}
}
sub plan2plans_cabs( $planid ) {
$planid=shift;
## printf( "$progid $dttm plan2plans_cabs MSG: planid=[$planid] sqldb=[$sqldb]\n" );
# get sum of cab1
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim, cab,cab1,cab2, sum(amps) samps ";
$sqlcmd_from = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where cab == cab1 " );
}
else {
$sqlcmd_where = sprintf( "where planid like \"\%%s\%\" and cab == cab1 ", $planid );
}
## $sqlcmd_where = sprintf( "where planid == \"%s\" and cab == cab1 ", $planid );
$sqlcmd_groupby = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab,cab1,cab2 ";
## $sqlcmd_order = "order by myid, mydt, planid, loadmode, cabnum, cablim, cab1, cast(samps as number) desc ";
$sqlcmd_order = "order by myid, mydt, planid, loadmode, cabnum, cablim, samps desc, cab, cab1, cab2 ";
$sqlcmd_cab1 = $sqlcmd_select;
$sqlcmd_cab1 .= $sqlcmd_from;
$sqlcmd_cab1 .= $sqlcmd_where . $sqlcmd_groupby . $sqlcmd_order;
printf( "$progid $dttm plan2plans MSG_470: sqlcmd_cab1=[$sqlcmd_cab1]\n" );
$rows_cab1 = sqlq( $sqldb, $sqlcmd_cab1, $sqltbl );
my $j=0;
foreach my $row_cab1 (@$rows_cab1) {
my ( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab, $cab1, $cab2, $samps ) = @$row_cab1;
## printf( "$progid sqlq MSG_610: myid=[$myid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] [$cablim] cab1=[$cab1] samps=[$samps]\n" );
printf( "$progid sqlq MSG_615: planid=[$planid][$loadmode][$cabnum][$cablim] cab1=[$cab][$cab1][$cab2] samps=[$samps]\n" );
}
# get sum of cab2
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim, cab, cab1,cab2, sum(amps) samps ";
$sqlcmd_from = "from koplans ";
## $sqlcmd_where = sprintf( "where planid == \"%s\" and cab == cab2 ", $planid );
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where cab == cab2 " );
}
else {
$sqlcmd_where = sprintf( "where planid like \"\%%s\%\" and cab == cab2 ", $planid );
}
$sqlcmd_groupby = "group by myid, mydt, planid, loadmode, cabnum, cablim, cab, cab1, cab2 ";
$sqlcmd_order = "order by myid, mydt, planid, loadmode, cabnum, cablim, samps desc, cab, cab1, cab2 ";
$sqlcmd_cab2 = $sqlcmd_select;
$sqlcmd_cab2 .= $sqlcmd_from;
$sqlcmd_cab2 .= $sqlcmd_where . $sqlcmd_groupby . $sqlcmd_order;
printf( "$progid $dttm plan2plans MSG_630: sqlcmd_cabs=[$sqlcmd_cab2]\n" );
$rows_cab2 = sqlq( $sqldb, $sqlcmd_cab2, $sqltbl );
my $j=0;
foreach my $row_cab2 (@$rows_cab2) {
my ( $myid, $mydt, $planid, $loadmode, $cabnum, $cablim, $cab,$cab2,$cab2, $samps ) = @$row_cab2;
## printf( "$progid sqlq MSG_640: myid=[$myid][$mydt] planid=[$planid][$loadmode][$cabnum][$cablim] cab2=[$cab2] samps=[$samps]\n" );
printf( "$progid sqlq MSG_640: planid=[$planid][$loadmode][$cabnum][$cablim] cab2=[$cab][$cab1][$cab2] samps=[$samps]\n" );
}
}
sub plan2plans_items( $planid ) {
$planid=shift;
# get list of items cab1
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_from = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where cab == cab1 " );
}
else {
$sqlcmd_where = sprintf( "where cab == cab1 and planid == \"%s\" ", $planid );
}
$sqlcmd_order = "order by planid, amps desc, item";
$sqlcmd_items_cab1 = $sqlcmd_select . $sqlcmd_from . $sqlcmd_where . $sqlcmd_order;
printf( "$progid $dttm plan2plans_items MSG_680: planid=[$planid] sqlcmd_items_cab1=[$sqlcmd_items_cab1] sqldb=[$sqldb]\n");
$rows_items_cab1=sqlq( $sqldb, $sqlcmd_items_cab1, $sqltbl );
printf( "$progid $dttm plan2plans_items MSG_690: sqlcmd_items=[$sqlcmd_items_cab1]\n" );
my $j=0;
foreach my $row_items_cab1 (@$rows_items_cab1) {
my ( $myid, $mydt, $planid,$loadmode,$cabnum,$cablim, $cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip ) = @$row_items_cab1;
printf( "$progid sqlq MSG_694: planid=[$planid][$loadmode][$cabnum][$cablim] cab1=[$cab][$cab1][$cab2] [$item][$amps]\n" );
}
# get list of items from cab2
$sqlcmd_select = "select myid, mydt, planid, loadmode, cabnum,cablim,cab,cab1,cab2,item,amps,sn,hostname,hrctag,os,ip ";
$sqlcmd_from = "from koplans ";
if( $planid eq "" ) {
$sqlcmd_where = sprintf( "where cab == cab2 ", $planid );
}
else {
$sqlcmd_where = sprintf( "where cab == cab2 and planid == \"%s\" ", $planid );
}
$sqlcmd_order = "order by amps desc, item";
$sqlcmd_items_cab2 = $sqlcmd_select . $sqlcmd_from . $sqlcmd_where . $sqlcmd_order;
printf( "$progid $dttm plan2plans MSG_700: planid=[$planid] sqlcmd_items_cab2=[$sqlcmd_items_cab2] sqldb=[$sqldb]\n");
$rows_items_cab2=sqlq( $sqldb, $sqlcmd_items_cab2, $sqltbl );
printf( "$progid $dttm plan2plans MSG_710: sqlcmd_items_cab2=[$sqlcmd_items_cab2]\n" );
my $j=0;
foreach my $row_items_cab2 (@$rows_items_cab2) {
my ( $myid, $mydt, $planid,$loadmode,$cabnum,$cablim, $cab,$cab1,$cab2,$item,$amps,$sn,$hostname,$hrctag,$os,$ip ) = @$row_items_cab2;
printf( "$progid sqlq MSG_710: planid=[$planid][$loadmode][$cabnum][$cablim] cab2=[$cab][$cab1][$cab2] [$item][$amps]\n" );
}
}
sub dttm {
my( $dttm ) = "yyyymmdd_hhmmss";
$t=time(); $now=localtime($t);
($sec, $min, $hour, $dom, $mon, $year, $wday, $yday, $isdst) = localtime($t);
$year += 1900; $mon++; $hour++; $dd = $dom - 21;
@month_names = (
"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"
);
@month_nam = (
"Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
);
@day_nam = ( "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" );
$dttm = sprintf( "%d%2.2d%2.2d_%2.2d%2.2d%2.2d",
$year, $mon, $dom, $hour, $min, $sec );
$cur_yyyymm = sprintf( "%4d%02d", $year, $mon );
$yymmdd = substr( $dttm, 2, 6 );
return( $dttm );
}
sub dttm {
my( $dttm ) = "yyyymmdd_hhmmss";
$t=time(); $now=localtime($t);
($sec, $min, $hour, $dom, $mon, $year, $wday, $yday, $isdst) = localtime($t);
$year += 1900; $mon++; $hour++; $dd = $dom - 21;
@month_names = (
"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"
);
@month_nam = (
"Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
);
@day_nam = ( "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" );
$dttm = sprintf( "%d%2.2d%2.2d_%2.2d%2.2d%2.2d",
$year, $mon, $dom, $hour, $min, $sec );
$cur_yyyymm = sprintf( "%4d%02d", $year, $mon );
$yymmdd = substr( $dttm, 2, 6 );
return( $dttm );
}
//
koh5_pano
Drag mouse to navigate.
Navigation
- Left/Right Mouse drag: Changes camera heading.
- Up/Sown Mouse drag: Changes camera pitch.
- Scroll wheel: Changes camera field of view.
- I-Key: Displays Info panel with canvas size, image size and FPS.
17.Aug.2010, Martin Wengenmayer