DIR : /home/kozerus/public_html/ko_center/ko_center.pl /home/kozerus/public_html/ko_center #!/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 ole_ol_list() { my $obj = shift; my $target = shift; $dttm=&dttm; ## for (my $i = 1; $i <= $obj->Folders->Count; $i++) { ## if ( $obj->Folders->Item($i)->Name eq $target ) { ## return $obj->Folders->Item($i); ## } ## } ## use strict; # use Win32::OLE; # use Win32::OLE::Variant; # use Win32::OLE::Const 'Microsoft Outlook'; $psvfil="ol_list.psv"; open( PSVFIL, ">$psvfil" ) || die "$progid psvfil ERR: OPEN psvfil=[$psvfil]\n"; $psvhdr="mnum|dttm|mbox|mto|sendername|receivedtime|subject|body|\n"; print( PSVFIL $psvhdr ); $mbox="inbox"; $mnum=0; my $Outlook = Win32::OLE->new('Outlook.Application', 'Quit'); my $ol = Win32::OLE::Const->Load($Outlook); my $namespace = $Outlook->GetNamespace("MAPI"); my $Folder = $namespace->GetDefaultFolder(olFolderInbox); my $items = $Folder->Items; print $items->Count; for my $itemIndex (1..$items->Count) { my $message = $items->Item($itemIndex); next if not defined $message; next if not defined $message->{SenderName}; next if not defined $message->{ReceivedTime}; next if not defined $message->{To}; next if not defined $message->{Subject}; next if not defined $message->{Body}; ## print "Sender: ", $message->{SenderName}, "\n"; ## print "Received:", $message->{ReceivedTime}, "\n"; ## print "To:", $message->{To}, "\n"; ## print "Subject:", $message->{Subject}, "\n"; ## print "Body:\n", $message->{Body}, "\n"; ## print "================================================\n"; $mnum++; $mto="mto"; if ( not defined $message->{To} ) { $mto="mto"; } else { $mto=$message->{To}; } if ( not defined $message->{SenderName} ) { $sendername="sendername"; } else { $sendername=$message->{SenderName}; } if ( not defined $message->{ReceivedTime} ) { $receivedtime="receivedtime"; } else { $receivedtime=$message->{ReceivedTime}; } if ( not defined $message->{Subject} ) { $subject="subject"; } else { $subject=$message->{Subject}; } if ( not defined $message->{Body} ) { $body="body"; } else { $body=$message->{Body}; } ## $mto=$message->{To}; $sendername=$message->{SenderName}; $receivedtime=$message->{receivedtime}; $subject=$message->{subject}; $body=$message->{body}; $psvrec="$mnum|$dttm|$mbox|$mto|$sendername|$receivedtime|$subject|$body|\n"; print( $psvrec ); print( PSVFIL $psvrec ); } close( PSVFIL ); } sub ole_ol_folders( $option1, $option2, $option3 ) { use strict; # use Win32::OLE qw( in valof with ); # use Win32::OLE::Variant; # use Win32::OLE::Const 'Microsoft Outlook'; # # Defines # my $Outlook; # An instance of Outlook my $oFolder; # Folder object my $folder; # Folder name my %Folders; # Hash table of folder names -> EntryID $| = 1; # Autoflush STDOUT # Use existing instance if Outlook is already running eval { $Outlook = Win32::OLE->GetActiveObject('Outlook.Application') }; die "Outlook is not installed" if $@; unless (defined $Outlook) { $Outlook = Win32::OLE->new('Outlook.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Outlook"; } # disabled my $ol = Win32::OLE::Const->Load($Outlook); # disabled my $namespace = $Outlook->GetNamespace("MAPI"); # disabled my $parent_mbox = $namespace->GetDefaultFolder(olFolderInbox)->Parent->Name(); # List all of our folders # Note that although Perl arrays generaly start with index 0, # these collections are indexed starting at 1. # disable foreach my $i (1..$namespace->Folders->Count) { # disable $folder = $namespace->Folders($i)->Name; # disable $oFolder = $namespace->Folders($i); # disable print "Folder: $folder\n"; # If this is our parent folder (eg. "Personal Folders"), then # recursively display all of the sub-folders. # Store the EntryID for every folder in this tree. # Save the folder<->EntryID pair # disable if ($folder =~ /^\Q${parent_mbox}\E$/) { # disable $Folders{$folder} = $namespace->Folders->Item($i)->EntryID; ## Print_Subs($oFolder, 1); # disable ole_ol_folders($oFolder, 1); # disable } # disable } # disabled $oFolder = $namespace->GetDefaultFolder(olFolderInbox); # disabled my $oItems = $oFolder->Items; # disabled if ($oItems->Count == 0) { # disabled print "No messages found\n"; # disabled } # disabled else { # disabled print "There are " . $oItems->Count . " messages in your Inbox:\n"; # disabled print "(You may be prompted by Outlook to allow this program to access\n"; # disabled print "your Inbox. Go to Tools->Macro->Security to adjust your settings.)\n\n"; # disabled # disabled my $j=0; # disabled foreach my $oItem (in $oItems) { ### requires explicit package name $j++; ### requires explicit package name $EmailAddr = $oItem->SenderEmailAddress; ### requires explicit package name $Sender = $oItem->SenderName; ### requires explicit package name $Subject = $oItem->Subject; ### requires explicit package name $Received = $oItem->ReceivedTime; # disabled my $psvfil="ko_center_ol_folders.psv"; # disabled open( PSVFIL, "$psvfil>>" ); ### printf( "$j|$Received|$Sender|$EmailAddr|$Subject|\n" ); ###E printf( PSVFIL "$j|$Received|$Sender|$EmailAddr|$Subject|\n" ); # disabled close( PSVFIL ); ## printf "From: %-25.25s Subject: %-25.25s Received: %-22.22s\n", ## $Sender, $Subject, $Received; ## Should be equivalent to printf, but something is odd with write ## write; # Use formatted output template # disabled } # disabled } } sub ole_ol_folders1( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid ole_ol_folders $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); # ====================================================================== # # Title: Win32_Outlook_Folders.pl # Author: Shawn A. Clifford <shawn.a.clifford@lmco.com> # Date: 9-July-2004 # Purpose: Locate our "Personal Folders" and list all sub-folders # # Modification History: # # 03-Aug-2004 SAC Modified to find the default folder and its # parent. This way there is no longer an # assumption of what the names of these folders # are, as users may rename them or redirect # incoming email to a different folder than Inbox. # # ====================================================================== # use strict; # use Win32::OLE qw( in valof with ); # use Win32::OLE::Variant; # use Win32::OLE::Const 'Microsoft Outlook'; # Defines my $Outlook; # An instance of Outlook my $oFolder; # Folder object my $folder; # Folder name my %Folders; # Hash table of folder names -> EntryID $| = 1; # Autoflush STDOUT # Use existing instance if Outlook is already running eval { $Outlook = Win32::OLE->GetActiveObject('Outlook.Application') }; die "Outlook is not installed" if $@; unless (defined $Outlook) { $Outlook = Win32::OLE->new('Outlook.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Outlook"; } my $ol = Win32::OLE::Const->Load($Outlook); my $namespace = $Outlook->GetNamespace("MAPI"); my $parent_mbox = $namespace->GetDefaultFolder(olFolderInbox)->Parent->Name(); printf( "ole_ol_folders $dttm MSG_400: parent_mbox=[$parent_mbox]=[Personal Folders]\n" ); # List all of our folders # Note that although Perl arrays generaly start with index 0, # these collections are indexed starting at 1. my $output = ""; foreach my $i (1..$namespace->Folders->Count) { $folder = $namespace->Folders($i)->Name; $oFolder = $namespace->Folders($i); print "Folder: $folder\n"; # If this is our parent folder (eg. "Personal Folders"), then # recursively display all of the sub-folders. # Store the EntryID for every folder in this tree. # # Save the folder<->EntryID pair if ($folder =~ /^\Q${parent_mbox}\E$/) { $Folders{$folder} = $namespace->Folders->Item($i)->EntryID; ole_ol_subfolders($oFolder, 1); } } # At this point we have all of our folders<->EntryIDs stored # in a hash table %Folders so that we can manipulate a given # folder directly. # As a demonstration, we will work with our inbox $psvfil="ko_center_ol_folders.psv"; $psvhdr="sender|received|emailaddr|subject|\n"; open( PSVFIL, ">$psvfil" ) || die "$progid $dttm ol_folders ERR: open psvfil=[$psvfil]\n"; printf( PSVFIL $psvhdr ); $mnum=0; print( "$progid $dttm MSG_430: mbox=[$mbox] psvfil=[$psvfil] pqpqpqpqpqpqpqpqpqpqpq\n"); my ($EmailAddr, $Sender, $Subject, $Received); my ($mnum, $mbox, $mdttm, $mfrom, $mfromaddr ); # Get an Outlook folder oject for our Inbox # $oFolder = $namespace->GetFolderFromID($Folders{'Inbox'}); # Get an Outlook folder object for our default folder # This is the proper way to locate the "Inbox". However, the # hash table is still useful for working with any other # Outlook folder by name. $mbox='Inbox'; $mbox='jk@konet.org'; $mbox='admini@konet.org'; # $oFolder = $namespace->GetFolderFromID($Folders{'sk@ko1.us'}); # $oFolder = $namespace->GetFolderFromID($Folders{'Inbox'}); $oFolder = $namespace->GetDefaultFolder(olFolderInbox); my $oItems = $oFolder->Items; if ($oItems->Count == 0) { $mcnt=$oItems->Count; print "$progid $dttm MSG_440: mcnt=[$mcnt] mbox=[$mbox] mcnt=[$mcnt] psvfil=[$psvfil] pqpqpqpqpqpqpqpqpqpqpq\n"; print "$progid $dttm MSG_440: No messages found\n"; } else { print "There are " . $mcnt . " messages in your Inbox:\n"; print "(You may be prompted by Outlook to allow this program to access\n"; print "your Inbox. Go to Tools->Macro->Security to adjust your settings.)\n\n"; # # From: Jerry Kassil Subject: Happy Birthday Received: 1/12/2007 8:24:03 PM # From: Senator Mel Martinez and Subject: We Start Today Received: 1/22/2007 8:52:16 PM # Win32::OLE(0.1709) error 0x8002000e: "Invalid number of parameters" # in METHOD/PROPERTYGET "" at ko_center.pl line 462 $j=0; foreach my $oItem (in $oItems) { $EmailAddr = $oItem->SenderEmailAddress; $Sender = $oItem->SenderName; $Subject = $oItem->Subject; $Received = $oItem->ReceivedTime; printf "From: %-25.25s Subject: %-25.25s Received: %-22.22s\n", $Sender, $Subject, $Received; ## Should be equivalent to printf, but something is odd with write ## write; # Use formatted output template } ### foreach my $oItem (in $oItems) { ### $j++; ### $EmailAddr = $oItem->SenderEmailAddress; ### $Sender = $oItem->SenderName; ### $Subject = $oItem->Subject; ### $Received = $oItem->ReceivedTime; ### ## Should be equivalent to printf, but something is odd with write ### printf( PSVFIL "%d|%s|%s|%s|%s|\n", $j,$Received,Sender,$EmailAddr,$Subject ); ### } } } sub ole_ol_subfolders { my $Top = shift; my $depth = shift; die "insufficient args: ole_ol_subfolders()\n" if (! $depth); # Collection indexes start at 1, not 0 foreach my $j (1..$Top->Folders->Count) { $folder = $Top->Folders->Item($j)->Name; $folders{$folder} = $Top->Folders->Item($j)->EntryID; $output = "$j|$folder|$folders{$folder}|"; # Comment this out if you only want to build the hash table my $psvfil = "ole_ol_subfolders.psv"; open( PSVFIL, "$psvfil>>" ); # print "ole_ol_subfolders MSG_480: j=$j depth=[x${depth}] folder=[$folder]\n"; printf( PSVFIL "$j|x${depth}|$folder|\n" ); close( PSVFIL ); if ($Top->Folders->Item($j)->Folders->Count > 1) { ole_ol_subfolders($Top->Folders->Item($j), $depth+1); } } # # Format records # ## format STDOUT_TOP = ## Sender Subject Received Date ## ------------------------- ------------------------- ---------------------- ## . ## ## format STDOUT = ## ^<<<<<<<<<<<<<<<<<<<<<<<< ^<<<<<<<<<<<<<<<<<<<<<<<< ^<<<<<<<<<<<<<<<<<<<<< ## ${Sender}, ${Subject}, ${Received} return($output); } sub pdf2info( $option1, $option2, $option3 ) { use Win32::OLE; ## $file = "Z:/xampp/htdocs/ko_saic/work_20121206/konet_ts_13-03-4_20120407-20120413.pdf"; $file = $option1; printf( "$progid $dttm MSG_1460: file=[$file]\n" ); if( ! -f $file ) { printf( "$progid $dttm ERR_1460: file=[$file]\n" ); exit; } $PDFDoc = Win32::OLE->new("AcroExch.PDDoc"); $PDFDoc->Open($file); $numPages = $PDFDoc->GetNumPages(); $PDFDoc->Close(); print $numPages; } sub xls2pdf( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; $xlsfil=$option1; $pdffil=$xlsfil; $pdffil=~ s/xls/pdf/; printf( "$progid xls2pdf $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); if( ! -f $xlsfil ) { printf( "$progid test1 $dttm ERR_630: NOTFOUND xlsfil=[$xlsfil] pdffil=[$pdffil] \n"); } else { printf( "$progid test1 $dttm MSG_630: FOUND xlsfil=[$xlsfil] pdffil=[$pdffil] \n"); } # open new excel instanc # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ts2csv $dttm ERR: OLE->new excel=[$excel]\n" ); printf( "$progid dta2xls_tx $dttm MSG_270: OLE->new excel=[$excel]\n" ); } else { printf( "$progid dta2xls_tx $dttm MSG_271: GetActiveObject DEFINED excel=[$excel]\n" ); } $book = $excel->Workbooks->Open("$xlsfil") || die printf( "$progid ts2csv $dttm ERR_280: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); $sheet = $book->Worksheets(1) || die printf( "$progid xls2pdf $dttm ERR_820: xlsfil=[$xlsfil] sheet=[$sheet] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG_820: xlsfil=[$xlsfil] sheet=[$sheet] excel=[$excel]\n" ); # color: ActivePrinter => "ACROBAT:", $excel->ActivePrinter->{Value} = "Adobe PDF"; # $excel->ActivePrinter = "Adobe PDF"; ## $active_printer = $excel->ActivePrinter; ## if ($active_printer) { ## print("$progid ts2csv $dttm MSG: active_printer=[$active_printer]\n"); ## } # Save as PDF # ActivePrinter => 'Acrobat PDFWriter'}) # ActivePrinter => 'Acrobat PDF on Ne07:'}) lockup # ActivePrinter => 'Acrobat PDF'}) # Copy => 1, $excel->ActiveWindow->SelectedSheets->PrintOut({ActivePrinter => 'Acrobat PDF', PrintToFile => 1, PrToFileName => "$xlspdf", copies => 1 }) || printf( "$progid $dttm xls2pdf ERR_836\n" ); printf( "$progid $dttm xls2pdf MSG_836\n" ); my @msgtext = $excel->ActiveWindow->SelectedSheets->PrintOut( {ActivePrinter => 'Adobe PDF', PrintToFile => 1, PrToFileName => "$xlspdf", copies => 1 }); ## print_r(@msgtxt); ## || printf( "$progid xls2pdf $dttm ERR_835: printout excel=[$excel]\n" ); printf( "$progid xls2pdf $dttm MSG_835: printout excel=[$excel]\n" ); ## my @msgtext = $excel->ActiveSheet->PrintOut({ ## PrintToFile => 1, ## PrToFileName => "$xlspdf", ## Copies => 1 ## }) ## || printf( "$progid xls2pdf $dttm ERR: PRINT xlspdf=[$xlsxpdf] active_printer=[$active_printer]\n" );; ## printf( "$progid xls2pdf $dttm MSG: PRINT xlspdf=[$xlspdf] active_printer=[$active_printer]\n" );; # $excel->Workbooks->quit() || printf( "ERR_845: excel->Workbooks->quit()\n" ); $excel->quit || printf( "ERR_846: excel->quit excel=[$excel]\n" ); $book->Close() || printf( "$progid ts2csv $dttm ERR_850: book->Close() book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_850: book->Close() book=[$book] excel=[$excel]\n"); $excel->Quit() || printf( "$progid ts2csv $dttm ERR_855: excel->Quit() book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_855: excel->Quit() book=[$book] excel=[$excel]\n"); } sub ole_xls( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; ## use strict; ## use warnings; ## use Win32::OLE; ## use Win32::OLE::Variant; ## use Win32::OLE qw(in with); ## use Win32::OLE::Const 'Microsoft Excel'; ## $Win32::OLE::Warn=3; ## $xlsfil="konet_ts_14_03_3_20130330_20130405.xls"; ## $xlsfil="j:\\ko_center\\oy3\\test1.xls"; ## $xlsfil="test1.xls"; ## $xlsfil=".\\test1.xls"; ## $xlsnew=".\\new_test1.xls"; ## $xlsfil="J:\\ko_center\\oy3\\test1.xls"; ## $xlsnew="J:\\ko_center\\oy3\\new_test1.xls"; if( $option1 ne "" ) { $xlsfil="$option1"; } if( $option2 ne "" ) { $xlsnew="$option2"; } printf( "$progid ole_xls $dttm MSG_650: xlsfil=[$xlsfil] xlsnew=[$xlsnew] o3=[$option3]\n"); $excel=""; ## if( $option1 ne "" ) { $xlsnew=$option1; } printf( "$progid test1 $dttm BEG_250: xlsfil=[$xlsfil] xlsnew=[$xlsnew] \n"); if( -f $xlsfil ) { printf( "$progid test1 $dttm BEG_260: FOUND xlsfil=[$xlsfil] xlsnew=[$xlsnew] \n"); } # open new excel instance # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ts2csv $dttm ERR: OLE->new excel=[$excel]\n" ); printf( "$progid dta2xls_tx $dttm MSG_270: OLE->new excel=[$excel]\n" ); } else { printf( "$progid dta2xls_tx $dttm MSG_271: GetActiveObject DEFINED excel=[$excel]\n" ); } ## printf( "$progid ts2csv $dttm BEG: open xls_templet=[$xls_templet] excel=[$excel]\n" ); ## $book = $excel->Workbooks->Open("$xlstmp") ## || die printf( "$progid ts2csv $dttm ERR: open xlsfil=[$xls_templet] book=[$book] excel=[$excel]\n"); $book = $excel->Workbooks->Open("$xlsfil") || die printf( "$progid ts2csv $dttm ERR_280: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid test1 $dttm MSG_280: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid ts2csv $dttm ERR_285: sheet xlsfil=[$xlsfil] sheet=[$sheet] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_285: sheet xlsfil=[$xlsfil] sheet=[$sheet] book=[$book] excel=[$excel]\n"); $sheetname = $book->Worksheets(1) -> { Name }; ## die printf( "$progid db2ts $dttm ERR_290: sheet xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid db2ts $dttm MSG_290: sheetname=[$sheetname] xlsnew=[$xlsnew] sheet=[$sheet] book=[$book] excel=[$excel]\n"); $sheet -> Activate || die printf( "$progid test1 $dttm ERR_299: Activate sheet xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid test1 $dttm MSG_200: Activate sheet=[$sheet] xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid test1 $dttm MSG_300: sheet=[$sheet] book=[$book] excel=[$excel] [$xlsfil][$xlsnew]\n" ); $excel->{'Visible'} = 0; # 0 is hidden, 1 is visible $excel->{DisplayAlerts} = 0; # 0 is hide alerts printf( "$progid test1 $dttm MSG_305: sheet=[$sheet] book=[$book] excel=[$excel] [$xlsfil][$xlsnew]\n" ); $new_title="new_title_$dttm"; $cur_title=$sheet->Range("$title_range")->{Value}; $sheet->Range("$title_range")->{value} = $new_title; $sheet->Range("j12")->{value} = "1200"; $sheet->Range("k12")->{value} = "1400"; $sheet->Range("m12")->{value} = "c"; $sheet->Range("j13")->{value} = "1300"; $sheet->Range("k13")->{value} = "1400"; $sheet->Range("m13")->{value} = "g"; $sheet->Range("r18")->{value} = "0"; $sheet->Range("s18")->{value} = "450"; $sheet->Range("u18")->{value} = "c"; $sheet->Range("z14")->{value} = "1200"; $sheet->Range("aa14")->{value} = "1400"; $sheet->Range("ac14")->{value} = "g"; $sheet->Range("z16")->{value} = "1300"; $sheet->Range("aa16")->{value} = "1400"; $sheet->Range("ac16")->{value} = "c"; $sheet->Range("b9:d9")->{value} = "2012-01-8"; $sheet->Range("f9")->{value} = "2012-01-09"; $sheet->Range("j9")->{value} = "2012-01-10"; $sheet->Range("n9")->{value} = "2012-01-11"; $sheet->Range("r9")->{value} = "2012-01-12"; $sheet->Range("v9")->{value} = "2012-01-13"; printf( "$progid $dttm test1 MSG_319: cur_title=[$cur_title] new_title=[$new_title]\n" ); # Pie Chart my $range = $sheet->Range('j40:j43'); printf( "BUGS_345: range=[$range]\n" ); my $chart = $book->charts->add; $chart->chartwizard($range,xl3DPie,7,xlRows,1,0,2,"test Percentages"); # Bar Graph and Rotate my $range = $sheet->range('j40:j43'); my $chart = $excel->charts->add; $chart->{type} = xl3DColumn; for (my $i = 30; $i <=180; $i+=10) {$chart->{rotation} = $i; sleep(1);} # Line Chart and Save my $range = $sheet->range('j40:j43'); my $chart = $excel->charts->add; $chart->{charttype} = xlLine; $chart->SetSourceData({source => $range, PlotBy => xlColumns}); $chart->{HasTitle} = 1; $chart->charttitle->{text} = "tes1_title"; my $chartobj = $sheet->chartObjects; ## $giffil="j:\\ko_center\\oy3\\new_excel.gif ## $Chart->Export({FileName => $giffil, FilterName => 'GIF', Interactive => 0}); # Save as PDF ## $excel->activewindow->SelectedSheets->PrintOut({Copy => 1, ActivePrinter => 'Acrobat PDFWriter'}); $book->saveAs($xlsnew) || die "$progid $dttm test1 ERR_320: book->saveas($xlsnew) sheet=[$sheet] book=[$book] excel=[$exel]\n"; printf( "$progid $dttm test1 MSG_320: book->saveas($xlsnew) sheet=[$sheet] book=[$book] excel=[$excel]\n"); $book->save || die "$progid $dttm test1 ERR_321: book->save sheet=[$sheet] book=[$book] excel=[$excel]\n"; printf( "$progid $dttm test1 MSG_321: book->save sheet=[$sheet] book=[$book] excel=[$excel]\n"); $book->close || die "$progid $dttm test1 ERR_325: book->close sheet=[$sheet] book=[$book] excel=[$excel]\n"; printf( "$progid $dttm test1 MSG_325: book->close sheet=[$sheet] book=[$book] excel=[$excel]\n" ); $excel->quit() || die "$progid $dttm test1 ERR_330: excel->quit sheet=[$sheet] book=[$book] excel=[$excel]\n"; printf( "$progid $dttm test1 MSG_330: excel->quit sheet=[$sheet] book=[$book] excel=[$excel]\n" ); undef $book || printf( "$progid ts2csv $dttm ERR_331: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_331: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); undef $excel || printf( "$progid ts2csv $dttm ERR_332: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_332: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); printf( "$progid test1 $dttm END_345: sheet=[$sheet] book=[$book] excel=[$excel] \n"); printf( "$progid test1 $dttm END_345: xlsfil=[$xlsfil] xlsnew=[$xlsnew] \n"); } sub ole_xls_saveas( $option1, $option2, $option3 ) { } sub ole_templet2ts( $yyyy, $fy, $p, $q, $begtdt, $enddt, $dtm_data, $dayofweek_info ) { $yyyy=shift; $fy=shift; $p=shift, $q=shift, $begdt=shift, $enddt=shift, $dtm_data=shift; $dayofweek_info=shift; $xlsfil=$ts_path_xlsfil_win; $tstemplet_xlsfil="konet_ts_templet.xls"; $pathwork="s:/xampp/htdocs/kxls/work/"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $begdttm=$begdt; $begdttm =~ s/-//g; $enddttm=$enddt; $enddttm =~ s/-//g; $ts_title=sprintf( "period_%02d-%02d-%1d_%s-%s", $fy, $p, $q, $begdttm, $enddttm ); $ts_txtfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.txt", $fy, $p, $q, $begdttm, $enddttm ); $ts_xlsfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.xls", $fy, $p, $q, $begdttm, $enddttm ); $ts_xlspdf=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.pdf", $fy, $p, $q, $begdttm, $enddttm ); $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; ( $mydt, $myt1, $myt2, $myhr, $mycd, $mydesc ) = split( /\|/, $dtm_data ); if( $myhr == "" ) { $myhr = $myt2 - $myt1; } $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); $mynow = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now'))" ); ## $begdt = &sqlval( $dbfile, date('$begdt') ); ## $begdt0 = &sqlval( $dbfile, date('begdt','weekdat 0') ); ## printf( "$progid $dttm MSG_222: now=[$now] begdt=[$begdt] \n" ); ## printf( "$progid $dttm MSG_222: begdt0=[$begdt0] begdt6=[$begdt6] enddt=[$enddt]\n"; $dayofweek_order = "1 2 3 4 5 6 0"; $dayofweek_info[0] = "1 E:20 Sun"; $dayofweek_info[1] = "2 I:20 Mon"; $dayofweek_info[2] = "3 M:20 Tue"; $dayofweek_info[3] = "4 Q:20 Wed"; $dayofweek_info[4] = "5 U:20 Thr"; $dayofweek_info[5] = "6 X:20 Fri"; $dayofweek_info[6] = "0 AC:20 Sat"; ( $dayofweek_order, $rowcol, $dayofweek_name ) = split( / /, $dayofweek_info[$dayofweek_num] ); printf( "$prodid $dttm MSG_950: $mydt, $myhr, $mycd, $mydesc\n" ); $daynames_text= "Sat Sun Mon Tue Wed Thr Fri"; @daynames = split( / /, $daynames_text ); printf( "ole__xls_templet2ts BUGS_928 $daynames_text $dayname[0] $dayname[6] $mynow kkkkkkkkkkkkkkkkkkkkk\n" ); for( $daynum=0; $daynum<7; $daynum++ ) { ## $sql_daydate=sprintf( "select strftime('\%Y-\%m-\\\%d', \'%s\', \'weekday %d\' );", $begdt, $daynum ); ## $sql_daydate=sprintf( "select strftime('\%Y-\%m-\\\%d', \'%s\', \'weekday %d\' );", $begdt, $daynum ); $sql_daydate = "select strftime('\%Y-\%m-\%d', "; $sql_daydate .= "\'".$begdt."\', "; $sql_daydate .= "\'weekday ". $daynum ."\'"; ## $myday = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now', 'weekday $daynum' )" ); $daydates[$daynum] = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); ## $daydates[$daynum] = &sqlval( $dbfile, $sql_daydate ); ## $daydat = &sqlval( $dbfile, $sql_daydate ); ## $daydate = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now','+$daynum')" ); $dayname = $daynames[$daynum]; $daydate = $daydates[$daynum]; ## printf( "$progid $dttm MSG_960: daynum=[$daynum] daydate=[$daydate] dayname=[$dayname] \n" ); ## next; } ## for( $daynum=0; $daynum<7; $daynum++ ) { ## printf( "$progid $dttm MSG_966: dnum=[%d] daydates=[%s] dayname=[%s] info=[$info]\n", ## $daynum, $daydates[$daynum], $daynames[$daynum], $dayofweek_info[$daynum]); ## } ## printf( "$progid $dttm MSG_990: dayofweek_order=[$dayofweek_order] dayofweek_name=[$dayofweek_name] $rowcol\n" ); ## printf( "$progid $dttm MSG_831: dayofweek_info=[$dayofweek_info]\n" ); ## printf( "$progid $dttm MSG_835: ts_templet_xlsfil=[$ts_templet_xlsfil]\n" ); ## printf( "$progid $dttm MSG_835: ts_xlsfil=[$ts_xlsfil]\n" ); ## printf( "$progid $dttm MSG_840: ts_title=[$ts_title]\n" ); $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"; $dayname_ranges[0]="B8:E8"; $dayname_ranges[1]="F8:I8"; $dayname_ranges[2]="J8:M8"; $dayname_ranges[3]="N8:Q8"; $dayname_ranges[4]="R8:U8"; $dayname_ranges[5]="V8:Y8"; $dayname_ranges[6]="Z8:AB8"; $daydate_ranges[0]="B9:E9"; $daydate_ranges[1]="F9:I9"; $daydate_ranges[2]="J9:M9"; $daydate_ranges[3]="N9:Q9"; $daydate_ranges[4]="R9:U9"; $daydate_ranges[5]="V9:Y9"; $daydate_ranges[6]="Z9:AB9"; # $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!; $tmpfil=$tstemplet_path_xlsfil_win; $xlsfil=$ts_path_xlsfil_win; $xlspdf=$ts_path_xlxfil_win; $xlspdf =~ s/xls/pdf/; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $tsv_path_xlsfil_win="$pathwork_win$ts_xlsfil"; $tmpfil=$tstemplet_path_xlsfil_win; $xlsfil=$ts_path_xlsfil_win; if( -f $ts_path_xlsfil_win ) { printf( "$progid $dttm MSG_1060: FOUND ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); } else { printf( "$progid $dttm ERR_1065: NOTFOUND ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); if( -f $tstemplet_path_xlsfil_win ) { printf( "$progid $dttm MSG_1930: FOUND tsnew_path_xlsfil_win=[$tsnew_path_xlsfil_win]\n" ); $ts_path_xlsfil_win="$pathwork_win$ts_xlsfil"; $xlsfil=$ts_path_xlsfil_win; printf( "$progid $dttm MSG_870: ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); printf( "$progid $dttm MSG_871: dtm_data=[$dtm_data] dayofweek_info=[$dayofweek_info]\n" ); # $cmd_cp="copy $tstemplet_path_xlsfil_win $xlsfil"; $cmd_cp="copy $tmpfil $xlsfil"; if( ! -f $xlsfil ) { printf( "$progid $dttm dt2kxls MSG_2030: cmd_cp=[$cmd_cp]\n" ); system( $cmd_cp ); } } else { printf( "$progid $dttm ERR_885: NOTFOUND tsnew_path_xlsfil_win=[$tsnew_path_xlsfil_win]\n" ); # exit; } } printf( "$progid $dttm MSG_900: ole_templet2ts ts_path_xlsfil_win=[$tsw_path_xlsfil_win]\n" ); printf( "$progid $dttm MSG_900: ts_xlsfil=[$ts_xlsfil] [$yyyy][$fy][$p][$q][$wk][$begdt][$enddt]\n"); printf( "$progid $dttm MSG_900: ts_title=[$ts_title] [$yyyy][$fy][$p][$q][$wk][$begdt][$enddt]\n"); # open new excel instance # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ole_ts_new $dttm ERR_815: OLE->new excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm MSG_815: OLE->new excel=[$excel]\n" ); } else { printf( "$progid ole_ts_new $dttm MSG_816: GetActiveObject DEFINED excel=[$excel]\n" ); } ## $book = $excel->Workbooks->Open("$xlstmp") ## $book = $excel->Workbooks->Open("$from_xls") ## || die printf( "$progid ts2csv $dttm ERR: open from_xls=[$from_xls] book=[$book] excel=[$excel]\n"); ## $book = $excel->Workbooks->Open("$xlsfil") ## $book = $excel->Worksbooks->Open("$to_xls") $book = $excel->Workbooks->Open($xlsfil) || die printf( "$progid ts2csv $dttm ERR_1094: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid $dttm MSG_1094: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid ole_ts $dttm ERR_1100: sheet xlsfil=[$xlsfil] sheet=[$sheet] book=[$book] excel=[$excel]\n"); ## printf( "$progid $dttm MSG_1101: sheet xlsfil=[$xlsfil] sheet=[$sheet] book=[$book] excel=[$excel]\n"); $sheetname = $book->Worksheets(1) -> { Name }; ## die printf( "$progid db2ts $dttm ERR_835: sheet xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid ole_xls_new $dttm MSG_840: sheetname=[$sheetname] xlsnew=[$xlsnew] sheet=[$sheet] book=[$book] excel=[$excel]\n"); $sheet -> Activate || die printf( "$progid test1 $dttm ERR_841: Activate sheet xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid test1 $dttm MSG_841: Activate sheet=[$sheet] xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid ole_xls_new $dttm MSG_842: sheet=[$sheet] book=[$book] excel=[$excel] [$xlsfil][$xlsnew]\n" ); $excel->{'Visible'} = 0; # 0 is hidden, 1 is visible $excel->{DisplayAlerts} = 0; # 0 is hide alerts printf( "$progid $dttm MSG_840: sheet=[$sheet] book=[$book] excel=[$excel] [$from_xls][$to_xls]\n" ); printf( "$progid $dttm MSG_850: from_xls=[$xlsvil]\n" ); $new_title=$ts_title; $cur_title=$sheet->Range("$title_range")->{Value}; $sheet->Range("$title_range")->{value} = $new_title; $daynums_text="0 1 2 3 4 5 6"; @daynums = split( / /, $daynums_text ); foreach $daynum (@daynums) { $dayname=$daynames[$daynum]; $daydate=$daydates[$daynum]; $dayname_range=$dayname_ranges[$daynum]; $daydate_range=$daydate_ranges[$daynum]; ## printf( "$progid $dttm MSG_1140: $daynum=[$daynum] dayname=[$dayname] daydate=[$daydate]\n" ); ## printf( "$progid $dttm MSG_1140: $daynum=[$daynum] dayname_range=[$dayname_range] daydate=[$daydate_range]\n" ); $sheet->Range("$dayname_range")->{value} = $dayname; $sheet->Range("$daydate_range")->{value} = $daydate; } $cols_t1_text = qw!B F J N R V Z!; $cols_t2_text = qw!C G K O S W AA!; $cols_hr_text = qw!D H L P T X AB!; $cols_cd_text = qw!E I M Q U Y AC!; $day_row=8; $dt_row=9; @tm_rows_text = qw!12 13 14 15 16 17 18 19 20 21!; ## printf( "BUGS_1200 mydt=[$mydt] myt1=[$myt1] myt2=[$myt2] mycd=[$mycd]\n" ); $j=100; $daynum=0; foreach $cols ( split( / +/, "b|c|e f|g|i j|k|m n|o|q r|s|u v|w|y z|aa|ac" )) { ## printf( "$progid $dttm MSG_1154: cols=[$cols]\n" ); ( $col_t1, $col_t2, $col_cd ) = split( /\|/, $cols ); $dayname=$daynames[$daynum]; $daydate=$daydates[$daynum]; printf( "BUGS_1171 cols=[$cols][row] daynum=[$daynum] daydate=[$daydate] dayname=[$dayname]\n" ); printf( "BUGS_1172 col_t1=[$col_t1] col_t2=[$col_t2] col_cd=[$col_cd]\n" ); ## next; ## printf( "BUGS_1240 mydt=[$mydt] daydate=[$daydate] daynum=[$daynum] dayname=[$dayname]\n" ); foreach $row ( split( / /, "12 13 14 15 16 17 18 19 20 21" )) { $range_t1=sprintf( "%s%d", $col_t1, $row ); $range_t2=sprintf( "%s%d", $col_t2, $row ); $range_cd=sprintf( "%s%d", $col_cd, $row ); if( $mydt eq $daydate ) { $sheet_t1 = $sheet->Range($range_t1)->{Value}; $sheet_t2 = $sheet->Range($range_t2)->{Value}; $sheet_cd = $sheet->Range($range_cd)->{Value}; if( $sheet_t1 ne "" ) { next; } if( $sheet_t2 ne "" ) { next; } if( $sheet_cd ne "" ) { next; } ## printf( "$progid $dttm MSG_1250: mydt=[$mydt][$myt1][$myt2][$mycd]\n" ); ## printf( "$progid $dttm MSG_1250: daynum=[$daynum] daydate=[$daydate] dayname=[$dayname]\n" ); ## printf( "$progid $dttm MSG_1250: range_t1=[$range_t1][$range_t2] range_cd=[$range_cd]\n" ); $sheet->Range($range_t1)->{Value} = $myt1; $sheet->Range($range_t2)->{Value} = $myt2; $sheet->Range($range_cd)->{Value} = $mycd; last; printf( "$progid $dttm MSG_1251: FOUND $mydt $daynum $daydate $dayname\n" ); } else { ## printf( "$progid $dttm MSG_1252: SKIP $mydt $daynum $daydate $dayname\n" ); ## $j=$j+100; ## printf( "BUGS_1240 col=[$col][$row] range=[$range]\n" ); ## $sheet->Range($range_t1)->{Value} = "1200"; ## $sheet->Range($range_t2)->{Value} = "1300"; ## $sheet->Range($range_cd)->{Value} = "z"; } } $daynum++; } ## foreach $col ( split( / /, "B F J N R V Z" )) { ## foreach $row ( split( / /, "12 13 14 15 16 17 18 19 20 21" )) { ## printf( "BUGS_1155a range=[$row:$col]\n" ); ## $colrow=sprintf( "%s%d", $col,$row ); ## $val=$sheet->Range($colrow)->{Value}; ## $sheet->Range("$col:$row")->{Value} = "100"; ## printf( "BUGS_1155b val=[$val] range=[$colrow]\n" ); ## } ## } ## ### @rows_t1 = split( / /, "12 13 14 15 16 17 18 19 20 21" ) { ### foreach $t1_row ( split( / /, "12 13 14 15 16 17 18 19 20 21" )) { ### printf( "BUGS_1160: col=$t1_col row=[$t1_row]\n" ); ### $t1_val=$sheet->Range($t1_col:$t1_row)->{Value}; ### if( $t1_val == "" ) { ### $sheet->Range("$t1_col:$t1_row")->{value} = $myt1; #### last; ### } ### else { ### $sheet->Range("$t1_col:$t1_row")->{value} = "1000"; ### } ### } ### } ### $slot=12; ## if ( $cur_range_j12=$sheet->Range("$j$slot")->{Value} != "" ) ## || ( $cur_range_k12=$sheet->Range("$k$slot")->{Value} != "" ) ## || ( $cur_range_m12=$sheet->Range("$m$slot")->{Value} != "" ) ## $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); printf( "$progid ole_templet2ts $dttm MSG_1225: colrow=[$colrow] value=[$value] sheet=[$sheet] excel=[$excel]\n" ); # This turns off the "This file already exists" message. $excel-> {DisplayAlerts} = 0; ## die printf( "$progid ole_ts_new $dttm ERR_1230: excel->DisplayAlerts = 0 xlsnew=[$xlsnew] excel=[$excel]\n" ); $book->SaveAs ("$xlsfil") || die printf( "$progid ole_ts_new $dttm ERR_1231: book->saveas xlsfil=[$xlsfil] excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm MSG_1232: book->saveas to_xls=[$xlsfil] excel=[$excel]\n" ); ## $book->Save() || ## die printf( "$progid ole_ts_new $dttm ERR_1234: book->save xlsnew=[$xlsfil] excel=[$excel]\n" ); ## printf( "$progid ole_ts_new $dttm MSG_1234: book->save xlsfil=[$xlsfil] excel=[$excel]\n" ); $book->Close() || die printf( "$progid ts2csv $dttm ERR_1240: book->close xlsfil=[$xlsfil] excel=[$excel]\n" ); printf( "$progid ts2csv $dttm MSG_1240: book->close xlsfil=[$xlsfil] excel=[$excel]\n" ); ## $book->Quit() ## || die printf( "$progid ts2csv $dttm ERR_1245: book->quit xlsfil=[$xlsfil] excel=[$excel]\n" ); ## prrintf( "$progid ts2csv $dttm MSG_1245: book->quit to_xls=[$to_xls] excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm END_1250: \n" ); return; # pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq # printf( "$progid ts2csv $dttm BEG_1140: display xlsfil=[$xlsfil]\n" ); # @hrs=""; $hrs[0][0]=""; $daynum=0; $flipflop=1; # $excel->{Visible} = 0; # black and white: ActivePrinter => '\\NETPRINTSRV\p21 on Ne01:', # color: ActivePrinter => "ACROBAT:", # default printer: ActivePrinter => $pobj, ## printf( "$progid ts2csv $dttm BEG: excel=[$excel]\n" ); $period=$sheet->Range("$period_range")->{Value} || die printf( "$progid ts2csv $dttm ERR: period=[$period] excel=[$excel]\n" ); ## printf( "$progid ts2csv $dttm MSG_600: period=[$period]\n" ); # printf( "$progid ts2csv $dttm MSG_600: period=[$period] excel=[$excel]\n" ); # get period info from formated title # period_13-1_20091227-20090102 $period =~ s/-/_/g; # period_13_1_20091227_20090102 @period_flds=split(/_/,$period); $period_pp=$period_flds[1]; $period_p=$period_flds[2]; $begdt_text=$period_flds[3]; $enddt_text=$period_flds[4]; $fy=substr($enddt_text,0,4); $begdt=sprintf( "%04d-%02d-%02d", substr($begdt_text,0,4), substr($begdt_text,4,2), substr($begdt_text,6,2) ); $enddt=sprintf( "%04d-%02d-%02d", substr($enddt_text,0,4), substr($enddt_text,4,2), substr($enddt_text,6,2) ); # $keytext=sprintf( "%s,%s,%s,%s,%s,", $fy,$period_pp, $period_p, $begdt, $enddt ); $keytext=sprintf( "%s,%s,%s", $fy,$period_pp, $period_p ); $title=$sheet->Range("$period_range")->{Value}; ## printf( "pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq\n" ); ## printf( "$progid ts2csv $dttm MSG_630:\ntitle=[$title]\n" ); ## printf( "bdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbd\n" ); ## return; $daynum=0; foreach $col (@week_cols_h1 ) { ## printf( "$progid $dttm MSG_640: daynum=[$daynum] col=[$col] xlsfil=[$xlsfil]\n" ); $day[$daynum]=$sheet->Range("$col$day_row:$col$day_row")->{Value}; $sheet->Range("$col$dt_row:$col$dt_row")->{NumberFormat} ="yyyy-mm-dd"; $dt[$daynum]=$sheet->Range("$col$dt_row:$col$dt_row")->{Value}; @dt_flds=split(/\//,$dt[$daynum] ); $effdt[$daynum]=sprintf( "%04d-%02d-%02d", $dt_flds[2],$dt_flds[0], $dt_flds[1] ); ## printf( "($col$day_row) $daynum day=$day[$daynum]\n" ); ## printf( "BUGS_645: ($col$dt_row) $daynum dt=$dt[$daynum] effdt=$effdt[$daynum]\n" ); $tsdrec_hdr=sprintf( "$period,$filnam_id$pkey_h1,$keytext,$hrstext,$xlsfil,status," ); $tsdrec_days = ""; foreach $row (@time_rows) { # $week_cols_h1 = qw!B F J N R V Z!; $range_h1=sprintf( "%s%s:%s%s", $week_cols_h1[$daynum],$row,$week_cols_h1[$daynum],$row ); $range_h2=sprintf( "%s%s:%s%s", $week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],$row ); $range_tm=sprintf( "%s%s:%s%s", $week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],$row ); $range_cd=sprintf( "%s%s:%s%s", $week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],$row ); ## printf( "$progid ts2csv $dttm MSG: r=($range) r1=($range1) r2=($range2)\n" ); $hrs[$daynum][0]=$sheet->Range("$range_h1")->{Value}; $hrs[$daynum][1]=$sheet->Range("$range_h2")->{Value}; $hrs[$daynum][2]=$sheet->Range("$range_tm")->{Value}; $hrs[$daynum][3]=$sheet->Range("$range_cd")->{Value}; $rangetext=sprintf( "h1=(%s) h2=(%s) tm=(%s) cd=(%s)", $range_h1,$range_h2,$range_tm,$range_cd ); ## printf( "$progid ts2csv $dttm MSG: rangetext=[$rangetext]\n " ); # $hrs[$daynum][0], $h1=$hrs[$daynum][0]; if( "$h1" eq "" ) { $h1=sprintf( "%04d", $pkey_h1 ); } $pkey_h1=sprintf( "%04d", ($pkey_h1+250)); if( $pkey_h1 > 2400 ) { $pkey_h1="0000"; } ## printf( "$progid ts2csv $dttm MSG_670: daynum=$daynum h1=[$h1] [pkey_h1=[$pkey_h1] hrs=$hrs[$daynum][0]\n" ); $hrstext=sprintf( "%s,%s,%s,%s,%s,%s,", $effdt[$daynum], $day[$daynum], $h1, $hrs[$daynum][1], $hrs[$daynum][2], $hrs[$daynum][3] ); ## $tsdrec_days .= "$hrs[$daynum][0],$hrs[$daynum][1],$hrs[$daynum][2],$hrs[$daynum][3],"; ## printf( TSDCSV "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n" ); $tsdrec = $tsdrec_hdr . $hrstext . "\n"; printf( TSDCSV $tsdrec ); ## $tsdrec = $tsdrec_hdr . $tsdrec_days; ## printf( TSDCSV "$tsdrec\n" ); ## $csvrec_hdr = ""; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,\n"; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,,,\n"; ## $csvrec_hrs = ""; ## $csvrec_hdrdt=""; ## printf( "$progid $dttm MSG_698: tsdrec=[$tsdrec]\n" ); ## printf( "$progid $dttm MSG_699: tssrec=[$tssrec]\n" ); ## ## printf( "$progid $dttm MSG_700: daynum=[$daynum]\n" ); ## $csvrec_hdrdt .= "$period,$hdrdt[$daynum],"; ## ## $csvrec_dtl .= "$week_cols_h1[$daynum],$week_cols_h1[$daynum],"; ## $csvrec_dtl .= "$week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],"; ## $csvrec_dtl .= "$week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],"; ## $csvrec_dtl .= "$week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],"; ## $csvrec_dtl .= "$hrs[$daynum][0],"; ## $csvrec_dtl .= "$hrs[$daynum][1],"; ## $csvrec_dtl .= "$hrs[$daynum][2],"; ## $csvrec_dtl .= "$hrs[$daynum][3],"; ## ## $csvrec_hrs = ""; ## for( $slots=1; $slots<10; $slots++ ) { ## $csvrec_hrs .= "$period,"; ## $csvrec_hrs .= "$begdt[$daynum][$slot],"; ## $csvrec_hrs .= "$enddt[$daynum][$slot],"; ## $csvrec_hrs .= "$hrs[$daynum][$slot],"; ## $csvrec_hrs .= "$code[$daynum][$slot],\n"; ## } ## ## $csvrec_hdrdt .= "\n"; ## $csvrec_dtl .= "\n"; ## $csvrec = ""; ## $csvrec .= $csvrec_hdr . $csvrec_hdrdt . $csvrec_dtl . "," . $csvrec_hrs . "\n"; ## $csvrec .= "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n"; # printf( "BUGS_730: csvrec=[$csvrec] xlscsv=[$slxcsv]\n" ); ## printf( XLSCSV "$csvrec\n" ); # printf( XLSCSV "$pkey,$keytext,$hrstext,,$col$row,desc,\n" ); # printf( XLSCSV "$keytext,$hrstext,amt,amtc,amtg,$col$row,desc,\n" ); # printf( "BUGS_680: $filnam_id$pkey_h1,$keytext,$hrstext,$col$row,desc\n" ); # $sheet->Range("$range_h1")->{Value}=$row*100; # $sheet->Range("$range_h2")->{Value}=$row*100+150; # if( $flipflop == 1 ) { # $sheet->Range("$range_cd")->{Value}="c"; # } # else { # $sheet->Range("$range_cd")->{Value}="g"; # } # $flipflop=$flipflop*(-1); } $daynum++; printf( "\n" ); } $book->Save({Filename =>'$to_xls', FileFormat => xlWorkbookNormal}) || die "$progid $dttm ERR_860: book->saveas $to_xls\n"; printf( "$progid $dttm MSG_860: book->saveas $to_xls\n" ); ## $book->SaveAs({Filename =>'$to_xls', FileFormat => xlWorkbookNormal}) ## || die "$progid $dttm ERR_860: book->saveas $to_xls\n"; ## printf( "$progid $dttm MSG_860: book->saveas $to_xls\n" ); $book->Close() || die "$progid $dttm ERR_861: book->close $to_xls\n"; printf( "$progid $dttm MSG_861: book->close $to_xls\n" ); $excel->Quit() || printf( "$progid ole_ts $dttm ERR_870: excel->Quit() book=[$book] excel=[$excel]\n"); printf( "$progid ole_ts $dttm MSG_875: excel->Quit() book=[$book] excel=[$excel]\n"); # $book->Save # || printf( "$progid xls2pdf $dttm ERR: book->Save book=[$book] excel=[$excel]\n"); # printf( "$progid xls2pdf $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); undef $book || printf( "$progid xls2pdf $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); undef $excel || printf( "$progid xls2pdf $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); } sub dtmdta( $fy, $pp, $q, $begdt, $enddt, $dtm_data, $options ) { $fy=shift; $pp=shift; $q=shift; $begdt=shift; $enddt=shift; $dtm_data=shift; $options=shift; printf( "$progid $dttm dtmdta MSG_1472: fy=[$fy] p=[$p] q=[q] begdt=[$begdt] enddt=[$enddt] dbfile=[$dbfile]\n" ); $mynow = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now'))" ); $p=sprintf( "%02d", $pp ); ## $where_clause="where date('$mydt') between date(begdt) and date(enddt)"; if(( $fy eq "" ) & ( $p eq "" ) & ( $q eq "" )) { $where_clause=""; } if(( $fy ne "" ) & ( $p eq "" ) & ( $q eq "" )) { $where_clause="where fy='$fy' "; } if(( $fy ne "" ) & ( $p ne "" ) & ( $q eq "" )) { $where_clause="where fy='$fy' and p='$p' "; } if(( $ne eq "" ) & ( $p ne "" ) & ( $q ne "" )) { $where_clause="where fy='$fy' and p='$p' and q='$q' "; } $sql_periods="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est, inv from periods $where_clause"; ## $sql_periods="select yyyy,fy,p,q,wk,begdt,enddt,oy,endday,desc,f1 est from periods where date('2012-11-12') between date(begdt) and date(enddt);"; printf( "$progid $dttm MSG_1470 dbfile=[$dbfile] mynow=[$mynow] sql_periods=[$sql_periods]\n" ); @$rows = &sqlvals( $dbfile, $sql_periods ); $rowcnt=0; $co="konet"; $user="kassilj"; $user_name="joachim kassil"; $co_name="konet llc"; $tstemplet_xlsfil="konet_ts_templet.xls"; $invtemplet_xlsfil="konet_inv_templet.xls"; $pathwork="s:/xampp/htdocs/kxls/work/"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; ## printf( "$progid $dttm MSG_1470 dbfile=[$dbfile] sql_periods=[$sql_periods]\n" ); ## &ole_templet2ts_detail_close(); foreach my $row (@$rows) { $rowcnt++; ( $yyyy, $fy, $p, $q, $wk, $begdt, $enddt, $endday, $desc, $oy, $est, $inv ) = @$row; printf( "\n$progid $dttm MSG_1490: $rowcnt $begdt - $enddt fy=[%d]-[%02d] q=[%d] wk=[%d] oy=[%d] est=[%d] $inv=[%d]\n", $rowcnt, $yyyy, $fy, $p, $q, $wk, $oy, $est, $inv ); # $ts_txtfil=sprintf( "konet_ts_fy-pp-w_yyyymmdd-yyyymmdd.txt" ); $begdttm=$begdt; $begdttm =~ s/-//g; $enddttm=$enddt; $enddttm =~ s/-//g; $daynum=0; $my_begdt = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); $my_enddt = &sqlval( $dbfile, "select date('$enddt', '$daynum day' )" ); ## $difdt = &sqlval( $dbfile, "select julian('$begdt') - julian('$enddt') )" ); ## $my_enddt = &sqlval( $dbfile, "select date('$enddt', '$daynum day' )" ); printf( "$progid $dttm MSG_1500: my_begdt=[$my_begdt] my_enddt=[$my_enddt]\n" ); $dtmdt = $my_begdt; ## for( $daynum=$dtmdt_begdt; $daynum<$dtmdt_enddt; $daynum++ ) { $daynum=0; ## while( $dtmdt < $my_enddt ) { $psvdta = ""; $psvfil = sprintf( "%s_%s_dtm.psv", $progid, $dttm ); open( PSVFIL, ">$psvfil" ) || die "$progid psvfil ERR_1510: OPEN psvfil=[$psvfil]\n"; ## $psvhdr="mnum|dttm|mbox|mto|sendername|receivedtime|subject|body|\n"; for( $daynum=0; $daynum < 32; $daynum++ ) { $sql_daydate = "select strftime('\%Y-\%m-\%d', "; $sql_daydate .= "\'".$begdt."\', "; $sql_daydate .= "\'weekday ". $daynum ."\'"; $daydates[$daynum] = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); $dayname = $daynames[$daynum]; $daydate = $daydates[$daynum]; $dtmdt = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); $dtmdt_day = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); printf( "$progid $dttm MSG_1520: $daynum daydate=[$daydate] dtmdt=[$dtmdt] my_enddt=[$my_enddt]\n" ); $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $dtmdt ); $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); if( $dayofweek_num == 6 ) { # sat $psvdta .= "$dtmdt|0000|0000||c|\n"; $psvdta .= "$dtmdt|1000|1200||c|\n"; $psvdta .= "$dtmdt|2100|2400||c|\n"; } if( $dayofweek_num == 0 ) { # sun $psvdta .= "$dtmdt|0000|0150||c|\n"; $psvdta .= "$dtmdt|1400|1600||c|\n"; $psvdta .= "$dtmdt|2300|2400||c|\n"; } if( $dayofweek_num == 1 ) { # mon $psvdta .= "$dtmdt|1300|1600||c|\n"; $psvdta .= "$dtmdt|2000|2400||c|\n"; } if( $dayofweek_num > 2 ) { # tue $psvdta .= "$dtmdt|0000|0150||c|\n"; $psvdta .= "$dtmdt|1000|1300||g|\n"; $psvdta .= "$dtmdt|1500|1700||c|\n"; $psvdta .= "$dtmdt|2230|2400||c|\n"; } if( $dayofweek_num > 3 ) { # wed $psvdta .= "$dtmdt|0000|0350||c|\n"; $psvdta .= "$dtmdt|1000|1700||g|\n"; $psvdta .= "$dtmdt|1500|1700||c|\n"; $psvdta .= "$dtmdt|2230|2400||c|\n"; } if( $dayofweek_num > 4 ) { # thr $psvdta .= "$dtmdt|0000|0350||c|\n"; $psvdta .= "$dtmdt|1000|1300||g|\n"; $psvdta .= "$dtmdt|1500|1700||c|\n"; $psvdta .= "$dtmdt|2230|2400||c|\n"; } if( $dayofweek_num > 5 ) { # fri $psvdta .= "$dtmdt|0000|0300||c|\n"; $psvdta .= "$dtmdt|1000|1200||c|\n"; } $daynum++; if( $dtmdt ge $my_enddt ) { last; } if( $daynum > 35 ) { last; } } print( PSVFIL $psvdta ); close( PSVFIL ); printf( "$progid $dttm MSG_1521: [$psvdta\n] daydate=[$daydate] dtmdt=[$dtmdt] my_enddt=[$my_enddt]\n" ); ## &ole_templet2ts( $yyyy, $fy, $p, $q, $begdt, $enddt, $dtm_data, $dayofweek_info ); ## &ole_templet2ts_detail( $yyyy, $fy, $p, $q, $begdt, $enddt, $dtm_data, $dayofweek_info ); } ## &ole_templet2ts_detail_close(); printf( "$progid $dttm MSG_2620: yyyy=[$yyyy] fy=[$fy] p=[$p] q=[$q] wk=[$wk]\n" ); } ## sub db2kxls( $fy, $p, $q, $begdt, $enddt, $dtm_data, $dayofweek_info ) { ## sub db2kxls( $fy, $pp, $q, $begdt, $enddt, $dtm_data, $options ) { sub db2saic( $option1, $option2, $option3, $option4, $option5, $option6, $option7 ) { $option1=shift; $option2=shift; $option3=shift; $option4=shift; $option5=shift; $option6=shift; $option7=shift; $my_fy=$option1; $my_pp=$option2; $my_q=$option3; $my_begdt=$option4; $my_enddt=$option5; $my_dtm_data=$option6; $dayofweek_info=$option7; $my_p=sprintf( "%02d", $my_pp ); printf( "$progid $dttm dtmdta MSG_1615: my_fy=[$my_fy] my_p=[$my_p] my_q=[$my_q] myh_begdt=[$my_begdt] mY_enddt=[$my_enddt] dbfile=[$dbfile]\n" ); $mynow = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now'))" ); ## $where_clause="where date('$mydt') between date(begdt) and date(enddt)"; if(( $my_fy eq "" ) && ( $my_p eq "" ) & ( $my_q eq "" )) { $where_clause=""; } if(( $my_fy ne "" ) && ( $my_p eq "" ) & ( $my_q eq "" )) { $where_clause="where fy='$my_fy' "; } if(( $my_fy ne "" ) && ( $my_p ne "" ) & ( $my_q eq "" )) { $where_clause="where fy='$my_fy' and p='$my_p' "; } if(( $ne eq "" ) && ( $my_p ne "" ) & ( $my_q ne "" )) { $where_clause="where fy='$my_fy' and p='$my_p' and q='$my_q' "; } # get periods ranges regardless of any quater vaues entered $sql_pdates="select yyyy, fy, p, oy, min(begdt) pbegdt, max(enddt) penddt, sum(est) pest, sum(inv) pinv from periods $where_clause group by yyyy, fy, p, oy "; printf( "$progid $dttm MSG_1625: dbfile=[$dbfile] mynow=[$mynow]\n sql_pdates=[$sql_pdates]\n" ); @$pdates_rows = &sqlvals( $dbfile, $sql_pdates ); foreach my $pdate_row (@$pdates_rows) { $rowcnt++; ( $yyyy, $fy, $p, $oy, $pbegdt, $penddt, $pesc, $pinv) = @$pdate_row; printf( "\n$progid $dttm MSG_1620: $rowcnt $pdegdt - $penddt\n" ); printf( "$progid $dttm MSG_1630: fy=[%d]-[%02d] pbegdt=[%s] penddt=[%s] pest=[%d] $pinv=[%d]\n", $fy, $p, $q, $pbegdt, $penddt, $pest, $pinv ); } printf( "$progid $dttm MSG_1635: yyyy=[%04d] fy=[%d]-[%02d] pbegdt=[%s] penddt=[%s] oy=[%d] est=[%d] $inv=[%d]\n", $yyyy, $fy, $p, $pbegdt, $penddt, $oy, $pest, $pinv ); ## $sql_periods="select yyyy,fy,p,q,wk,begdt,enddt,oy,endday,desc,f1 est from periods where date('2012-11-12') between date(begdt) and date(enddt);"; $sql_periods="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est, inv from periods $where_clause"; printf( "$progid $dttm MSG_1645 dbfile=[$dbfile] mynow=[$mynow] sql_periods=[$sql_eriods]\n" ); @$rows = &sqlvals( $dbfile, $sql_periods ); $rowcnt=0; $co="konet"; $user="kassilj"; $user_name="joachim kassil"; $co_name="konet llc"; $tstemplet_xlsfil="konet_ts_templet.xls"; $invtemplet_xlsfil="konet_inv_templet.xls"; $emailtemplet_txtfil="konet_email_templet.txt"; $psrtemplet_xlswrd="konet_psr_templet.wrd"; $pathwork="s:/xampp/htdocs/kxls/work/"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $emailtemplet_path_txtfil_win="$pathwork_win$emailtemplet_txtfil"; $psrtemplet_path_wrdfil_win="$pathwork_win$psrtemplet_wrdfil"; $esttemplet_path_xlsfil_win="$pathwork_win$emplet_xlsfil"; ## printf( "$progid $dttm MSG_1470 dbfile=[$dbfile] sql_periods=[$sql_periods]\n" ); ## &ole_templet2ts_detail_close(); foreach my $row (@$rows) { $rowcnt++; ( $yyyy, $fy, $p, $q, $wk, $begdt, $enddt, $endday, $desc, $oy, $est, $inv ) = @$row; printf( "\n$progid $dttm MSG_1620: $rowcnt $begdt - $enddt\n" ); printf( "$progid $dttm MSG_1620: fy=[%d]-[%02d]-[%d] %s - %s wk=[%d] oy=[%d] est=[%d] $inv=[%d]\n", $yyyy, $fy, $p, $q, $begdt, $enddt, $wk, $oy, $est, $inv ); # $ts_txtfil=sprintf( "konet_ts_fy-pp-w_yyyymmdd-yyyymmdd.txt" ); ## $sql_dtm="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est, inv from periods $where_clause"; $sql_dtm="select dt, t1, t2, hr, cd, desc from dtm where dt >= '$begdt' and dt <= '$begdt'"; ## del printf( "$progid $dttm MSG_1640 dbfile=[$dbfile] mynow=[$mynow] sql_dtm=[$sql_periods]\n" ); ## del @$dtm_rows = &sqlvals( $dbfile, $sql_dtm ); ## del $dtm_rowcnt=0; ## del $co="konet"; ## del $dtm_rowcnt=0; ## del foreach my $dtm_row (@$dtm_rows) { ## del ## del ( $dtm_dt, $dtm_t1,$dtm_t2, $dtm_hr, $dtm_cd, $dtm_desc ) = @$dtm_row; ## del printf( "\n$progid $dttm MSG_1650: $dtm_rowcnt dtm_dt=$dtm_dt dtm_t1-t2=$dtm_t1 - $dtm_t2 $dtm_hr $dtm_cd\n" ); ## del ## del $dtm_rowcnt++; # jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj #### printf( "NEXT_1688: jjjjjjjjjjjjjjjjjjjjjjjjj\n" ); #### next; $tstemplet_xlsfil="konet_ts_templet.xls"; $pathwork="s:/xampp/htdocs/kxls/work/"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $begdttm=$begdt; $begdttm =~ s/-//g; $enddttm=$enddt; $enddttm =~ s/-//g; ### $ts_title=sprintf( "period_%02d-%02d-%1d_%s-%s", $fy, $p, $q, $begdttm, $enddttm ); ### $ts_txtfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.txt", $fy, $p, $q, $begdttm, $enddttm ); ### $ts_xlsfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.xls", $fy, $p, $q, $begdttm, $enddttm ); ### $ts_xlspdf=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.pdf", $fy, $p, $q, $begdttm, $enddttm ); ## konet_invoice_fy-pp_yyyymmdd-yyyymmdd.png ### $inv_title=sprintf( "period_%02d-%02d-%1d_%s-%s", $fy, $p, $q, $begdttm, $enddttm ); ### $inv_txtfil=sprintf( "konet_invoice_%02d-%02d-%1d_%s.txt", $fy, $p, $begdttm, $enddttm ); ### $inv_xlsfil=sprintf( "konet_ts_%02d-%02d-%1d_%s.xls", $fy, $p,$begdttm, $enddttm ); ### $inv_xlspdf=sprintf( "konet_ts_%02d-%02d-%1d_%s.pdf", $fy, $p,$begdttm, $enddttm ); ### ### $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; ### $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; ### ### $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; ### $inv_path_xlsfil_win="$pathwork_win$inv_xlsfil"; ### $tmpfil=$invtemplet_path_xlsfil_win; ### $xlsfil=$inv_path_xlsfil_win; ### ### $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; ## my ( $mydt, $myt1, $myt2, $myhr, $mycd, $mydesc ) = split( /\|/, $dtm_data ); ## $mydt = $dtm_dt; ## $myt1 = $dtm_t1; ## $myt2 = $dtm_t2; ## $myhr = $dtm_hr; ## $mycd = $dtm_cd; ## $mydesc = $dtm_desc; ## if( $myhr == "" ) { $myhr = $myt2 - $myt1; } ## $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); ## $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); $mynow = &sqlval( $dbfile, "select strftime( '\%Y-\%m-\%d', date('now'))" ); $dayofweek_order = "1 2 3 4 5 6 0"; $dayofweek_info[0] = "1 E:20 Sun"; $dayofweek_info[1] = "2 I:20 Mon"; $dayofweek_info[2] = "3 M:20 Tue"; $dayofweek_info[3] = "4 Q:20 Wed"; $dayofweek_info[4] = "5 U:20 Thr"; $dayofweek_info[5] = "6 X:20 Fri"; $dayofweek_info[6] = "0 AC:20 Sat"; ( $dayofweek_order, $rowcol, $dayofweek_name ) = split( / /, $dayofweek_info[$dayofweek_num] ); printf( "$prodid $dttm MSG_1720: $mydt, $myhr, $mycd, $mydesc\n" ); $daynames_text= "Sat Sun Mon Tue Wed Thr Fri"; @daynames = split( / /, $daynames_text ); printf( "ole__xls_templet2ts BUGS_928 $daynames_text $dayname[0] $dayname[6] $mynow \n" ); for( $daynum=0; $daynum<7; $daynum++ ) { $sql_daydate = "select strftime('\%Y-\%m-\%d', "; $sql_daydate .= "\'".$begdt."\', "; $sql_daydate .= "\'weekday ". $daynum ."\'"; $daydates[$daynum] = &sqlval( $dbfile, "select date('$begdt', '$daynum day' )" ); $dayname = $daynames[$daynum]; $daydate = $daydates[$daynum]; } ## for( $daynum=0; $daynum<7; $daynum++ ) { ## printf( "$progid $dttm MSG_966: dnum=[%d] daydates=[%s] dayname=[%s] info=[$info]\n", ## $daynum, $daydates[$daynum], $daynames[$daynum], $dayofweek_info[$daynum]); ## } $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"; $dayname_ranges[0]="B8:E8"; $dayname_ranges[1]="F8:I8"; $dayname_ranges[2]="J8:M8"; $dayname_ranges[3]="N8:Q8"; $dayname_ranges[4]="R8:U8"; $dayname_ranges[5]="V8:Y8"; $dayname_ranges[6]="Z8:AB8"; $daydate_ranges[0]="B9:E9"; $daydate_ranges[1]="F9:I9"; $daydate_ranges[2]="J9:M9"; $daydate_ranges[3]="N9:Q9"; $daydate_ranges[4]="R9:U9"; $daydate_ranges[5]="V9:Y9"; $daydate_ranges[6]="Z9:AB9"; # $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!; $inv_title_range="i:6"; $inv_submitdt_range="i:6"; $inv_begdt_range="b:24"; $inv_enddt_range="c:24"; $inv_maxhr="f42"; $inv_prevhr="g42"; $inv_ytdhr="h42"; $inv_rate="e42"; $inv_prev="c:24"; $inv_ytd="c:24"; $inv_ftrfilnam="i59"; $ts_title=sprintf( "period_%02d-%02d-%1d_%s-%s", $fy, $p, $q, $begdttm, $enddttm ); $ts_txtfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.txt", $fy, $p, $q, $begdttm, $enddttm ); $ts_xlsfil=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.xls", $fy, $p, $q, $begdttm, $enddttm ); $ts_xlspdf=sprintf( "konet_ts_%02d-%02d-%1d_%s-%s.pdf", $fy, $p, $q, $begdttm, $enddttm ); ## konet_invoice_fy-pp_yyyymmdd-yyyymmdd.png $inv_title=sprintf( "period_%02d-%02d-%1d_%s-%s", $fy, $p, $q, $begdttm, $enddttm ); $inv_txtfil=sprintf( "konet_invoice_%02d-%02d-%1d_%s.txt", $fy, $p, $begdttm, $enddttm ); $inv_xlsfil=sprintf( "konet_invoice_%02d-%02d-%1d_%s.xls", $fy, $p,$begdttm, $enddttm ); $inv_xlspdf=sprintf( "konet_invoice_%02d-%02d-%1d_%s.pdf", $fy, $p,$begdttm, $enddttm ); $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $inv_path_xlsfil_win="$pathwork_win$inv_xlsfil"; $tmpfil=$invtemplet_path_xlsfil_win; $xlsfil=$inv_path_xlsfil_win; ## $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $tmpfil=$invtemplet_path_xlsfil_win; $xlsfil=$inv_path_xlsfil_win; $xlspdf=$inv_path_xlxfil_win; $xlspdf =~ s/xls/pdf/; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; $inv_path_xlsfil_win="$pathwork_win$inv_xlsfil"; $tmpfil=$invtemplet_path_xlsfil_win; $xlsfil=$inv_path_xlsfil_win; if( -f $inv_path_xlsfil_win ) { printf( "$progid $dttm MSG_1825: FOUND inv_path_xlsfil_win=[$inv_path_xlsfil_win]\n" ); } else { printf( "$progid $dttm ERR_1830: NOTFOUND inv_path_xlsfil_win=[$inv_path_xlsfil_win]\n" ); if( -f $invtemplet_path_xlsfil_win ) { printf( "$progid $dttm MSG_1880: FOUND tsnew_path_xlsfil_win=[$tsnew_path_xlsfil_win]\n" ); $inv_path_xlsfil_win="$pathwork_win$inv_xlsfil"; $xlsfil=$inv_path_xlsfil_win; printf( "$progid $dttm MSG_1835: inv_path_xlsfil_win=[$inv_path_xlsfil_win]\n" ); printf( "$progid $dttm MSG_1835: dtm_data=[$dtm_data] dayofweek_info=[$dayofweek_info]\n" ); $cmd_cp_inv="copy $tmpfil $xlsfil"; if( ! -f $xlsfil ) { printf( "$progid $dttm dt2kxls MSG_1840: cmd_cp_inv=[$cmd_cp_inv]\n" ); system( $cmd_cp_inv ); } } else { printf( "$progid $dttm ERR_1845: NOTFOUND invtemplet_path_xlsfil_win=[$invtemplet_path_xlsfil_win]\n" ); ## exit; } } $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $ts_path_xlsfil_win="$pathwork_win$ts_xlsfil"; $tmpfil=$tstemplet_path_xlsfil_win; $xlsfil=$ts_path_xlsfil_win; if( -f $ts_path_xlsfil_win ) { printf( "$progid $dttm MSG_1830: FOUND ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); } else { printf( "$progid $dttm ERR_1800: NOTFOUND ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); if( -f $tstemplet_path_xlsfil_win ) { printf( "$progid $dttm MSG_1810: FOUND tsnew_path_xlsfil_win=[$tsnew_path_xlsfil_win]\n" ); $ts_path_xlsfil_win="$pathwork_win$ts_xlsfil"; $xlsfil=$ts_path_xlsfil_win; printf( "$progid $dttm MSG_1815: ts_path_xlsfil_win=[$ts_path_xlsfil_win]\n" ); printf( "$progid $dttm MSG_1815: dtm_data=[$dtm_data] dayofweek_info=[$dayofweek_info]\n" ); $cmd_cp_ts="copy $tmpfil $xlsfil"; if( ! -f $xlsfil ) { printf( "$progid $dttm dt2kxls MSG_2030: cmd_cp_ts=[$cmd_cp_ts]\n" ); system( $cmd_cp_ts ); } } else { printf( "$progid $dttm ERR_1826: NOTFOUND invtemplet_path_xlsfil_win=[$invtemplet_path_xlsfil_win]\n" ); exit; } } ## printf( "BUGS_1885_EXIT\n" ); ## exit; # open new excel instance # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ole_ts_new $dttm ERR_815: OLE->new excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm MSG_815: OLE->new excel=[$excel]\n" ); } else { printf( "$progid ole_ts_new $dttm MSG_816: GetActiveObject DEFINED excel=[$excel]\n" ); } $book = $excel->Workbooks->Open($xlsfil) || die printf( "$progid ts2csv $dttm ERR_1094: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid $dttm MSG_1094: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid ole_ts $dttm ERR_1100: sheet xlsfil=[$xlsfil] sheet=[$sheet] book=[$book] excel=[$excel]\n"); $sheetname = $book->Worksheets(1) -> { Name }; $sheet -> Activate || die printf( "$progid test1 $dttm ERR_841: Activate sheet xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid test1 $dttm MSG_841: Activate sheet=[$sheet] xlsnew=[$xlsnew] book=[$book] excel=[$excel]\n"); printf( "$progid ole_xls_new $dttm MSG_842: sheet=[$sheet] book=[$book] excel=[$excel] [$xlsfil][$xlsnew]\n" ); $excel->{'Visible'} = 0; # 0 is hidden, 1 is visible $excel->{DisplayAlerts} = 0; # 0 is hide alerts printf( "$progid $dttm MSG_840: sheet=[$sheet] book=[$book] excel=[$excel] [$from_xls][$to_xls]\n" ); printf( "$progid $dttm MSG_850: from_xls=[$xlsvil]\n" ); ## $where_clause="where date('$mydt') between date(begdt) and date(enddt)"; ## $sql_dtm="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est from periods $where_clause"; # $where_clause="where date($dt) between date($begdt) and date($enddt)"; # $where_clause=sprintf( "where date(dt) between date('%s') and date('%s');", $begdt, $enddt ); $where_clause=sprintf( "where dt between '%s' and '%s';", $begdt, $enddt ); ## $where_clause=""; $sql_dtm="select dt, t1, t2, hr, cd, desc from dtm $where_clause"; printf( "$progid $dttm MSG_1930 dbfile=[$dbfile] sql_dtm=[$sql_dtm]\n" ); printf( "$progid $dttm MSG_1930 mydt_1=[$mydt_1] mydt_2=[$mydt_2]\n" ); printf( "$progid $dttm MSG_1930 where_clause=[$where_clause] dt=[$dt] begdt=[$begdt][$enddt]\n" ); @$dtm_rows = &sqlvals( $dbfile, $sql_dtm ); $rowcnt=0; $psvfil="$progid"."_bugs.psv"; open( PSVFIL, ">$psvfil" ) || die "$progid psvfil ERR_1880: OPEN psvfil=[$psvfil]\n"; # $psvhdr="mnum|dttm|mbox|mto|sendername|receivedtime|subject|body|\n"; $hrcnt=0; $rowcnt=0; foreach my $dtm_row (@$dtm_rows) { $rowcnt++; $hrccnt++; ( $dtm_dt, $dtm_t1, $dtm_t2, $dtm_hr, $dtm_cd, $dtm_desc ) = @$dtm_row; $dtm_data = sprintf( "%s|%s|%s|%s|%s|%s|%s\n", $dtm_dt,$dtm_t1,$dtm_t2,$dtm_hr,$dtm_cd,$dtm_desc ); printf( "\n$progid $dttm MSG_1950: $rowcnt dtm_dt=[$dtm_dt] dtm_t1=[$dtm_t1] dtm_data=[$dtm_data] jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj\n" ); ## if( $rowcnt > 15 ) { ## printf( "\n$progid $dttm MSG_1945: $rowcnt $dtm_dt[$dtm_data] LAST_LIMIT_1945 dtm_dt=$dtm_dt dtm_t1=[$dtm_t1] dtm_t2=$dtm_t2 dtm_cd=[$dtm_cd]\n" ); ## last; ## } printf( "\n$progid $dttm MSG_1950: $rowcnt dtm_data=[$dtm_data] dtm_dt=$dtm_dt dtm_t1=[$dtm_t1] dtm_t2=$dtm_t2 dtm_cd=[$dtm_cd]\n" ); printf( "\n$progid $dttm NEXT_1950: $rowcnt dtm_data=[$dtm_data] dtm_dt=$dtm_dt dtm_t1=[$dtm_t1] dtm_t2=$dtm_t2 dtm_cd=[$dtm_cd]\n" ); ## next; ## &dt2kxls( $dtm_data, $option2, $option3 ); $new_title=$ts_title; $cur_title=$sheet->Range("$title_range")->{Value}; $sheet->Range("$title_range")->{value} = $new_title; $daynums_text="0 1 2 3 4 5 6"; @daynums = split( / /, $daynums_text ); foreach $daynum (@daynums) { $dayname=$daynames[$daynum]; $daydate=$daydates[$daynum]; $dayname_range=$dayname_ranges[$daynum]; $daydate_range=$daydate_ranges[$daynum]; ## printf( "$progid $dttm MSG_1875: $daynum=[$daynum] dayname=[$dayname] daydate=[$daydate]\n" ); ## printf( "$progid $dttm MSG_1875: $daynum=[$daynum] dayname_range=[$dayname_range] daydate=[$daydate_range]\n" ); $psvrec="$progid|$dttm|$dayname_range|$daydate_range|$dayname|$daydate|\n"; print( PSVFIL $psvrec ); $sheet->Range("$dayname_range")->{value} = $dayname; $sheet->Range("$daydate_range")->{value} = $daydate; } $cols_t1_text = qw!B F J N R V Z!; $cols_t2_text = qw!C G K O S W AA!; $cols_hr_text = qw!D H L P T X AB!; $cols_cd_text = qw!E I M Q U Y AC!; $day_row=8; $dt_row=9; @tm_rows_text = qw!12 13 14 15 16 17 18 19 20 21!; $j=100; $daynum=0; foreach $cols ( split( / +/, "b|c|e f|g|i j|k|m n|o|q r|s|u v|w|y z|aa|ac" )) { ( $col_t1, $col_t2, $col_cd ) = split( /\|/, $cols ); printf( "$progid $dttm MSG_1220: cols=[$cols]\n" ); printf( "BUGS_1989: col_t1=$col_t1 col_t2=[$col_t2] $col_cd\n" ); $dayname=$daynames[$daynum]; $daydate=$daydates[$daynum]; $bugrec=sprintf( "BUGS_1990 daydate=[%s] dtm_dt=[%s] dtm_t1=[%s] dtm_t2=[%s] dtm_cd=[%s]\n", $daydate, $dtm_dt,$dtm_t1,$dtm_t2,$dtm_cd ); print( PSVFIL $bugrec ); print( PSVFIL "$progid $dttm MSG_2000: dtm_dt=$dtm_dt dtm_cd=$dtm_cd\n" ); printf( "$progid $dttm MSG_2000: dtm_dt=$dtm_dt dtm_cd=$dtm_cd\n" ); ## foreach $row ( split( / /, "12 13 14 15 16 17 18 19 20 21" )) { foreach $row ( split( / /, "13 14 15 16 17 18 19 20" )) { $range_t1=sprintf( "%s%d", $col_t1, $row ); $range_t2=sprintf( "%s%d", $col_t2, $row ); $range_cd=sprintf( "%s%d", $col_cd, $row ); $psvrec="$progid|$dttm|bugs_1949|range_cd=$range_cd range_t1=$range_t1 $col_t1|$col_t2|$col_cd|$dayname_range|$daydate_range|$dayname|$daydate|\n"; print( PSVFIL $psvrec ); $psvrec = "$progid|$dttm|compare|$dtm_dt|$daydate|$range_t1|$range_t2|$range_cd|\n"; $yyyy=substr( $dtm_dt, 0, 4); $mm=substr( $dtm_dt, 5, 2); $dd=substr( $dtm_dt, 8, 2); $yyyymmdd=sprintf( "%04d-%02d-%02d", $yyyy, $mm, $dd ); ## $dtm_dt=$yyyymmdd; ## printf( "BUGS_2011: NEXT dtm_dt=[$dtm_dt] daydate=[$daydate] yyyy=$yyyy mm=$mm dd=$dd yyyymmdd=[$yyyymmdd\n" ); if( $dtm_dt eq $daydate ) { printf( "$progid $dttm MSG_2020: dtm_dt=[$dtm_dt][$daydate] FOUND ranges_cd=$range_cd dtm_dtm_dt=[$dtm_dt] $daynum daydate=[$daydate] dtm_t12cd=$dtm_t1 $dtm_t2 $dtm_cd jjjjjjjjjjjjjjjjjjj\n" ); $sheet_t1 = $sheet->Range($range_t1)->{Value}; $sheet_t2 = $sheet->Range($range_t2)->{Value}; $sheet_cd = $sheet->Range($range_cd)->{Value}; if( $sheet_t1 ne "" ) { next; } if( $sheet_t2 ne "" ) { next; } if( $sheet_cd ne "" ) { next; } $psvrec="$progid|$dttm|bugs_1950_done|dtm_dt=$dtm_dt|dtm_t1=$dtm_t1|$dtm_t2|$dtm_cd|$range_t1|$range_t2|$range_cd|$dayname_range|$daydate_range|$dayname|$daydate|\n"; print( PSVFIL $psvrec ); if(( $dtm_cd eq "c" ) || ( $dtm_cd eq "g" )) { $sheet->Range($range_t1)->{Value} = $dtm_t1; $sheet->Range($range_t2)->{Value} = $dtm_t2; $sheet->Range($range_cd)->{Value} = $dtm_cd; last; } } else { if( $daydate > $dtm_dt ) { last; } ## printf( "$progid $dttm SKIP_2040: dtm_dt=[%s][$daydate] NOTFOUND ranges_cd=$range_cd dtm_dtm_dt=[$dtm_dt] $daynum daydate=[$daydate] dtm_t1 t2 cd = $dtm_t1 $dtm_t2 $dtm_cd kkkkkkkkkkkkkkkkkk\n" ); printf( "$progid $dttm MSG_2040: dtm_dt=[$dtm_dt][$daydate] FOUND ranges_cd=$range_cd dtm_dtm_dt=[$dtm_dt] $daynum daydate=[$daydate] $dayname range_cd=[$range_cd] range_t1=[$range_t1] zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz\n" ); $psvrec="$progid|$dttm|bugs_2040skip|dtm_dt=$dtm_dt|dtm_t1=$dtm_t1|$dtm_t2|$dtm_cd|$range_t1|$range_t2|$range_cd|$dayname_range|$daydate_range|$dayname|$daydate|\n"; print( PSVFIL $psvrec ); ## printf( "$progid $dttm MSG_2040: SKIP dtm_dt=[$dtm_dt] mydt=[$mydt] daydate=[$daydate] $daynum [$dayname]\n" ); ## $j=$j+100; ## printf( "BUGS_1240 col=[$col][$row] range=[$range]\n" ); ## $sheet->Range($range_t1)->{Value} = "1200"; ## $sheet->Range($range_t2)->{Value} = "1300"; ## $sheet->Range($range_cd)->{Value} = "z"; } } $daynum++; } close( PSVFIL ); $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); printf( "$progid db2kxls $dttm MSG_1922: sheet=[$sheet] excel=[$excel]\n" ); } # This turns off the "This file already exists" message. $excel-> {DisplayAlerts} = 0; ## $book->SaveAs ("$xlsfil") || ## die printf( "$progid ole_ts_new $dttm ERR_1780: book->SaveAs xlsfil=[$xlsfil] excel=[$excel]\n" ); ## printf( "$progid db2kxls $dttm MSG_1780: book->saveas to_xls=[$xlsfil] excel=[$excel]\n" ); $book->Save () || die printf( "$progid ole_ts_new $dttm ERR_2055: book->Save xlsfil=[$xlsfil] excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm MSG_2055: book->save to_xls=[$xlsfil] excel=[$excel]\n" ); $book->Close() || die printf( "$progid ts2csv $dttm ERR_2060: book->close xlsfil=[$xlsfil] excel=[$excel]\n" ); printf( "$progid ts2csv $dttm MSG_2060: book->close xlsfil=[$xlsfil] excel=[$excel]\n" ); printf( "$progid ole_ts_new $dttm END_1785: \n" ); $excel-> Quit; ## undef $book || ## printf( "$progid db2kxls $dttm ERR_2066: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid db2kxls $dttm MSG_2066: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## undef $excel || ## printf( "$progid db2kxls $dttm ERR_2070: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid db2kxls $dttm MSG_2070: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); # err $excel->ActiveWorkbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->ActiveWorkbook->Close(0) book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->ActiveWorkbooks->Close(0) book=[$book] excel=[$excel]\n"); ## $book = $excel->Workbooks->Open("$xlsfil") # $excel->Workbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # $book->Close() # || printf( "$progid ts2csv $dttm ERR: book->Close() book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Close() book=[$book] excel=[$excel]\n"); # $excel->Quit # || printf( "$progid ts2csv $dttm ERR: excel->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Quit book=[$book] excel=[$excel]\n"); # $book->Quit # || printf( "$progid ts2csv $dttm ERR: book->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); # $book->Save # || printf( "$progid ts2csv $dttm ERR: book->Save book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); ## undef $book || ## printf( "$progid ts2csv $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## undef $excel || ## printf( "$progid ts2csv $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); } ## } ## printf( "$progid $dttm db2kxls MSG: fy=[$fy] p=[$p] q=[q] begdt=[$begdt] enddt=[$enddt] beg\n" ); return; # kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk } sub ole_ol( $option1, $option2, $option3 ) { ## use strict; ## use Win32::OLE qw/in/; use constant INBOX => 6; $ol = Win32::OLE->GetActiveObject('Outlook.Application') || Win32::OLE->new('Outlook.Application', 'Quit'); $mapi = $ol->GetNameSpace("MAPI"); printf( "$progid $dttm MSG_452: ol=[$ol] mapi=[$mapi]\n" ); ## $inbox = $mapi->GetDefaultFolder(olFolderInbox) ## $inbox = $mapi->GetDefaultFolder('INBOX') $inbox = $mapi->GetDefaultFolder(INBOX) || die "$progid $dttm ERR_455: inbox=[$inbox] mapi=[$mapi]\n"; printf( "$progid $dttm MSG_455: inbox=[$inbox] ol=[$ol] mapi=[$mapi] inbox=[$inbox]\n" ); ## my $mapi = $ol->GetNameSpace("MAPI"); ## my $inbox = $mapi->GetDefaultFolder(olFolderInbox); my $dir = cwd . "\\"; $dir =~ s/\//\\/g; my $atch1, $file1, $atch2, $file2; printf( "$progid MSG_002: ol=[$ol] mapi=[$mapi] inbox=[$inbox] dir=[$dir]\n"); # Win32::OLE ->GetObject('Outlook.Application') # Win32::OLE ->BuiltinDocumentProperties(wdPropertyComments) # Win32::OLE ->Value; $inbox_items=ref($inbox->{Items}); printf( "$progid MSG_057: inbox_items=[$inbox_items] \n"); my $itemcount = $inbox->Items->Count; printf( "$progid MSG_060: itemcount=[$itemcount]\n" ); my $dir = cwd . "\\"; $dir =~ s/\//\\/g; my $atch1, $file1, $atch2, $file2; printf( "$progid MSG_460: inbox=[$inbox] mapi=[$mapi] dir=[$dir]\n"); # Win32::OLE ->GetObject('Outlook.Application') # Win32::OLE ->BuiltinDocumentProperties(wdPropertyComments) # Win32::OLE ->Value; $inbox_items=ref($inbox->{Items}); printf( "$progid MSG_492: inbox_items=[$inbox_items] \n"); $itemcount = $inbox->Items->Count; printf( "$progid MSG_495: itemcount=[$itemcount]\n" ); ## my $r; ## eval {$r = Win32::OLE->GetActiveObject('Outlook.Application')}; ## if ($@ || !defined($r)) { ## $r = Win32::OLE->new('Outlook.Application', sub {$_[0]->Quit;}) or die "ERR_450\n"; ## } ## ## my $namespace = $r->GetNameSpace( 'MAPI' ) or die "can't open MAPI + namespace\n"; ## my $count = 0; ## my $folder; # re-reading this, I remember what this is for # you pass the names of the sub sub [...] folder # you want to examine as arguments on the command line # e.g. perl extract.pl archive 1999 lists vmsperl ## if( $folder = $namespace->GetDefaultFolder( INBOX )) { ## $foldername = $folder->{Name}; ## print "$folder->{Name}\n"; ## ## my $f = $folder; ## foreach( @ARGV ) { ## mkdir $_, 0666; ## chdir $_; ## $f = $f->Folders( $_ ); # walk down the folder path ## print "$f->{Name}\n" or die Win32::OLE->LastError() . "\n"; ## } foreach $it ($inbox->Items) { ++$count; printf( "BUGS_518: it=$it count=$count inbox=[$inbox] ol=[$ol] mapi=[$mapi] \n" ); next; $mto = $it->{To}; $mcc = $it->{CC}; $subject = $it->{Subject}; $receivedtime = $it->{ReceivedTime}; $sendernamefrom = $it->{SenderName}; ## $receivedtime = $it->{receivedtime}; ## print "$it->{ReceivedTime} $it->{Subject}\n"; ## $subject = $it->{Subject}; $subject =~ tr/a-zA-Z0-9/_/c; $subject =~ s/_+/_/g; $body = $it->{body}; printf( "$progid $dttm MSG_540: mto=[$mto] subject=[$subject] receivedtime=[$ReceivedTime]\n" ); $output = ""; $output .= "$mtr, $mcc, $sendernamefrom,$sub,$subject, $receivedtime, $body \n"; ## open OUT, sprintf( ">%03s-$subj.txt", $count ) or die "Cannot open + $count for output: $!\n"; ## print OUT <<EOM; ## To: $it->{To} ## CC: $it->{CC} ## From: $it->{SenderName} ## Subject: $it->{Subject} ## Date: $it->{ReceivedTime} ## $it->{Body} ## EOM ## close OUT; } ## } } sub ole_ol_send( $option1, $option2, $option3 ) { # use Win32::OLE; # use Win32::OLE qw(in with); # use Win32::OLE::Variant; # use Win32::OLE::Const 'Microsoft Outlook'; my $Outlook = Win32::OLE->GetActiveObject ('Outlook.Application') || Win32::OLE->new('Outlook.Application'); my $Outlook = new Win32::OLE('Outlook.Application'); $attach_file = "J:\\kxls\\oy3\\test.txt"; # Create Mail Item my $item = $Outlook->CreateItem(0); # 0 = mail item. unless ($item) { die "Outlook is not running, cannot send mail.\n"; } $item->{'Subject'} = $mail_props{'subject'} || '[No Subject]'; $item->{'To'} = join (";", split(/[ ,;]+/, $mail_props{'to'})); $item->{'Body'} = $mail_props{'body'} || "\r\n"; $item->{'From'} = $mail_props{'from'} if (exists $mail_props{'from'}); $item->{'Cc'} = $mail_props{'cc'} if (exists $mail_props{'cc'}); $item->{'Bcc'} = $mail_props{'bcc'} if (exists $mail_props{'bcc'}); # 2=high, 1=normal, 0=low $item->{'Importance'} =(exists $mail_props{'importance'})? $mail_props{'importance'}: 1; # Add Attachemnts if (exists $mail_props{'attachments'}) { $attach = $item->{'Attachments'}; foreach my $attach_file (@mail_props{'attachments'}) { if ($attach_file and -f $attach_file) { $attach->add($attach_file); } } } } sub ole_word() { # use Win32::OLE; # use Win32::OLE qw(in with); # use Win32::OLE::Variant; # use Win32::OLE::Const 'Microsoft Word'; $Word = Win32::OLE->GetActiveObject('Word.Application') || Win32::OLE->new('Word.Application'); $Word->{'Visible'} = 0; $Word->{DisplayAlerts} = 0; # Open File $txtfil="j:\\kxls\\oy3\\test.txt"; $docfil="j:\\kxls\\oy3\\test.doc"; # my $file = $Word->Documents->Open(''); # my $file = $Word->Documents->Open(''); my $file = $word->documents->open('$txtfil') || die "$progid $dttm ERR_502: open txtfil=[$txtfil]\n"; printf( "$progid $dttm MSG_502: open txtfil=[$txtfil]\n" ); # Replace Content of Document $rng = $file->{content}; $rng->{Text} = '$dttm'; # Change Page Margins ( my $test = $file->pagesetup, topmargin => 40, bottommargin => 40, leftmargin => 40, rightmargin => 40); # Print File printf( "$progid $dttm MSG_515: txtfil=[$txtfil] docfil=[$docfil] ole_word printout\n" ); # $File->PrintOut(); # or # $Word->ActiveDocument->PrintOut({ # Background => 0, # Range => wdPrintAllDocument, # Item => wdPrintDocumentContent, # PageType => wdPrintAllPages}) # # sleep to let Word print before closing # sleep 5; # Save As $word->ActiveDocument->saveas({FileName => $docfil, FileFormat => wdFormatDocument}); # Quit/Close $file->close(); $word->quit(); } sub sql_cr_periods( $sqlbin, $sqldb, $sqltbl ) { $sqlbin=shift; $sqldb=shift; $sqltbl=shift; printf( "$progid sql_cr_jobs $dttm MSG: sqldb=$sqldb sqlcmd=$sqlcmd o1=$option1 o2=$option2\n" ); # filnam TEXT, my $db = DBI->connect( "dbi:SQLite:$sqldb" ); # $sqlcmd_cr=qq{ create table $sqltbl ( id int(11), jobfil TEXT, wc REAL, rundate DATA ); }; $sqlcmd=qq{CREATE TABLE periods ( fy TEXT, pd TEXT, p TEXT, wk TEXT, begdt DATE, begday TEXT, enddt DATE, endday TEXT, aors INTEGER, aorc INTEGER, ltrs INTEGER, hrs INTEGER, hrc INTEGER, hrg INTEGER, desc TEXT ); }; ## PRIMARY KEY (id)); printf( "$progid sql_cr $dttm DBI: sqlcmd=[$sqlcmd]\n" ); $db->do( "$sqlcmd" ); if( $db->err() ) { printf( "$prodid sql_cr_jobs $dttm DB_ERR: sqldb=[$sqldb] dbi_errstr=[$DBI::errstr]\n" ); } # $sqlcmd="select * from sqlite_master"; # $sqlcmd_drop="drop table cfg"; # $sqlcmd_cr=qq{ create table cfg (id INTEGER PRIMARY KEY, fld TEXT, val TEXT);}; # $sqlcmd_insert=qq{ insert into cfg values (NULL, 'fld0','val0' );}; $db->disconnect(); } sub sql_cr_hrs( $sqlbin, $sqldb, $sqltbl ) { $sqlbin=shift; $sqldb=shift; $sqltbl=shift; printf( "$progid sql_cr_jobs $dttm MSG: sqldb=$sqldb sqlcmd=$sqlcmd o1=$option1 o2=$option2\n" ); # $sqlbin -csv $sqldb ".import $csvfil $sqltbl" # filnam TEXT, my $db = DBI->connect( "dbi:SQLite:$sqldb" ); # $sqlcmd_cr=qq{ create table $sqltbl ( id int(11), jobfil TEXT, wc REAL, rundate DATA ); }; # id INTEGER PRIMARY KEY, $sqlcmd=qq{create table $sqltbl ( id integer primary key, fy TEXT, pp TEXT, p TEXT, effdt TEXT, day TEXT, h1 NUMBER, h2 NUMBER, tm TEXT, cd TEXT, s TEXT, cr TEXT, desc TEXT, UNIQUE (effdt,h1) on conflict ignore ); }; printf( "$progid sql_cr $dttm DBI: sqlcmd=[$sqlcmd]\n" ); $db->do( "$sqlcmd" ); if( $db->err() ) { printf( "$prodid sql_cr_jobs $dttm DB_ERR: sqldb=[$sqldb] dbi_errstr=[$DBI::errstr]\n" ); } # $sqlcmd="select * from sqlite_master"; # $sqlcmd_drop="drop table cfg"; # $sqlcmd_cr=qq{ create table cfg (id INTEGER PRIMARY KEY, fld TEXT, val TEXT);}; # $sqlcmd_insert=qq{ insert into cfg values (NULL, 'fld0','val0' );}; $db->disconnect(); } sub test2( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test2 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub test3( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test3 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub test4( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test4 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub test5( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test5 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub test6( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test6 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub sqlq( "$sqldb, $sqlcmd, $sqltbl ) { $sqldb=shift; $sqlcmd=shift; $sqltbl=shift; ## $sqlcmd=qq{ select * from tss limit 5; }; ## $sqlcmd="select myid, mydt, cab, item, amps, sn, hrctag, rpdu_bank, rpdu__outnum from kohw_hrc_defrag order by amps desc"; ## $sqlcmde="select * from koplans"; # $sqlcmd="select mycd, mydt, planid, cab, item, amps, sn,hostname,hrctag,os,ip from koplans order by planid,item, amps desc"; ## if( $sqlin ne "" ) { $sqlcmd=$sqlin; } if( $sqlcmd eq "" ) { return; } ## printf( "$progid $dttm sqlq MSG_2650: sqlcmd=[$sqlcmd] jjjjjjjjjjjjjjjjjjjjjjjjjjjjj\n[$sqldb]\n" ); ## return; my $db = DBI->connect( "dbi:SQLite:$sqldb" ) || die printf( "$progid $dttm ERR_2610: connect\n" );; ## $sqlcmd =~ s/\'/"/g; ## my $mycols = $db->selectcol_arrayref("$sqlcmd"); ## my $myrows = $db->selectrow_arrayref("$sqlcmd"); $rows = $db->selectall_arrayref($sqlcmd); $db->disconnect(); ## printf( "$progid $dttm sqlq MSG_2620: sqlcmd=[$sqlcmd] [$sqldb] [$rows]\n" ); return( $rows ); ### $j=0; ### foreach $row (@$rows) { ### $j++; ### printf( "$j row=[$row]\n" ); ### ( $mycd, $mydt, $planid, $cab, $item, $amps, $sn, $hostname, $hrctag, $os, $ip ) = @$row; ### ### printf( "$progid plan2plans MSG_2610: j=$j myid=[$myid][$mydt] planid, cab=[$cab] item=[$item] amps=[$amps]\n" ); ### } ### # printf( "$progid $dttm sqlq MSG_2620: sqlcmd=[$sqlcmd] [$sqldb] [$rows]\n" ); return( $rows ); ## return( $rows, $mycols, $myrows); } sub sqldo( "$sqldb, $sqlcmd, $sqltbl ) { $sqldb=shift; $sqlcmd=shift; $sqltbl=shift; if( $sqlcmd eq "" ) { return; } my $db = DBI->connect( "dbi:SQLite:$sqldb" ) || die printf( "$progid $dttm ERR_2679: connect sqldb=[$sqldb]\n" );; ## printf( "$progid $dttm sqldo MSG_2680: status=[$status] sqlcmd=[$sqlcmd] [$sqldb] [$rows]\n" ); ## my $mycols = $db->selectcol_arrayref("$sqlcmd"); ## my $myrows = $db->selectrow_arrayref("$sqlcmd"); ## $dbh->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')"); $status = $db->do("$sqlcmd"); $db->disconnect(); ## printf( "$progid $dttm sqldo MSG_2690: status=[$status] sqlcmd=[$sqlcmd] [$sqldb] [$rows]\n" ); return( $status ); } sub sql_query( $sqlin, $sqldb, $sqltbl ) { $sqlin=shift; $sqldb=shift; $sqltbl=shift; printf( "$progid sql_query $dttm MSG: sqldb=$sqldb sqlcmd=$sqlcmd o1=$option1 o2=$option2\n" ); my $db = DBI->connect( "dbi:SQLite:$sqldb" ) || die printf( "$progid $dttm ERR_350: connect\n" );; $sqlcmd=qq{ select * from tss limit 5; }; ## if( $sqlin ne "" ) { $sqlcmd=$sqlin; } ## ## printf( "$progid sql_sel_us $dttm MSG: sqlcmd=[$sqlcmd]\n" ); ## my $mycols = $db->selectcol_arrayref("$sqlcmd"); ## my $myrows = $db->selectrow_arrayref("$sqlcmd"); ## my $rows = $db->selectall_arrayref("$sqlcmd"); $db->disconnect(); ## $j=0; ## foreach $mycol (@mycols) { printf( "$j++ mycol=[$mycol]\n" ); } ## $j=0; foreach $myrow (@myrows) { printf( "$j++ myrow=[$myrow]\n" ); } exit; my $j=0; foreach my $row (@$rows) { my ( $user, $host, $info, $shell, $spwchg, $spwchg_dt, $spwchg_age ) = @$row; printf( "$progid sql_sel_us MSG: user=[$user] host=[$host] $info $homedir spwchg=[$spwchg][$spwchg_dt][$spwchg_age]\n" ); } exit; $sqlcmd=qq{ select id,dttm, user, host, info, homedir, spwchg, spwchg_dt, spwchg_age from us }; printf( "$progid sql_sel_us $dttm MSG: sqlcmd=[$sqlcmd]\n" ); my $rows = $db->selectall_arrayref("$sqlcmd"); $db->disconnect(); foreach my $row (@$rows) { my ($id, $dttm, $user, $host, $info, $homedir, $spwchg, $spwchg_dt, $spwchg_age ) = @$row; printf( "$progid sql_sel_us MSG: id=$id $dttm [$user][$host] $info $homedir spwchg=[$spwchg][$spwchg_dt][$spwchg_age]\n" ); } exit; $sqlcmd=qq{ select * from us }; printf( "$progid sql_sel_us $dttm MSG: sqlcmd=[$sqlcmd]\n" ); my $rows = $db->selectall_arrayref("$sqlcmd"); $db->disconnect(); foreach my $row (@$rows) { my ($id, $dttm, $user, $host, $pass, $status, $shell, $uid, $gid, $info, $homedir, $bootpw, $audid, $audflg, $mintm, $maxpwln, $exptm, $lftm, $spwchg, $upwchg, $acctexp, $llog, $expwarn, $ussrpick, $syspnpw, $rstrpw, $nullpw, $admnum, $syschpw, $sysltpw, $timeod, $slogint, $ulogint, $slogin, $culogin, $ulogin, $umaxlntr, $alock, $lockout, $spwchg_dt, $spwchg_age, $upwchg_dt, $upwchg_age, $slogint_dt, $slogint_age, $ulogint_dt, $ulogint_age, $myint, $mydttm, $myreal, $mydata, $mytext, $desc ) = @$row; $psvdta ="$id|$dttm|$user|$host|$status|$shell|"; printf( "$progid sql_query MSG: id=$id host=$host user=$user, info=$info, spwchg=$spwchg, dt=$spwchg_dt age=$spwchg_age\n" ); } } sub help_ole() { print <<EOF; #___ Fonts # $sheet -> Range("G7:H7") -> Font -> {Bold} = "True"; # $sheet -> Range("G7:H7") -> Font -> {Italic} = "True"; # $sheet -> Range("G7:H7") -> Font -> {Underline} = xlUnderlineStyleSingle; # $sheet -> Range("G7:H7") -> Font -> {Size} = 8; # $sheet -> Range("G7:H7") -> Font -> {Name} = "Arial"; # $sheet -> Range("G7:H7") -> Font -> {ColorIndex} = 4; #___ SUM FORMULAS # $sheet -> Range("A3") -> {FormulaR1C1} = "=SUM(R[-2]C:R[-1]C)"; # Sum rows # $sheet -> Range("C1") -> {FormulaR1C1} = "=SUM(RC[-2]:RC[-1])"; # Sum columns #___ HIDE COLUMNS # $sheet -> Range("G:H") -> EntireColumn -> {Hidden} = "True"; #___ MERGE CELLS # $sheet -> Range("H10:J10") -> Merge; #___ INSERT PICTURE # $sheet -> Pictures -> Insert("picture_name"); # Insert in upper-left corner # $excel -> ActiveSheet -> Pictures -> Insert("picture_name"); # Insert in active cell #___ SET COLUMN WIDTH/ROW HEIGHT # $sheet -> Range('A:A') -> {ColumnWidth} = 9.14; # $sheet -> Range("8:8") -> {RowHeight} = 30; # $sheet -> Range("G:H") -> {Columns} -> Autofit; #___ FIND LAST ROW/COLUMN WITH DATA # $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row}; # $last_col = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByColumns}) -> {Column}; #___ ADD BORDERS # $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {LineStyle} = xlDouble; # $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {Weight} = xlThick; # $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {ColorIndex} = 1; # $sheet -> Range("A3:I3") -> Borders(xlEdgeLeft) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlEdgeLeft) -> {Weight} = xlThin; # $sheet -> Range("A3:I3") -> Borders(xlEdgeTop) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlEdgeTop) -> {Weight} = xlThin; # $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {Weight} = xlThin; # $sheet -> Range("A3:I3") -> Borders(xlEdgeRight) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlEdgeRight) -> {Weight} = xlThin; # $sheet -> Range("A3:I3") -> Borders(xlInsideVertical) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlInsideVertical) -> {Weight} = xlThin; # $sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {LineStyle} = xlContinuous; # $sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {Weight} = xlThin; EOF } sub xls2pdf_save( $option1, $option2, $option3 ) { $pathfile_xlsfil=shift; $option2=shift; $option3=shift; # printf( "$progid xls2pdf $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); $pathfile_xlsfil="s:\\xampp\\htdocs\\ko_center\\work\\ts_test.xls"; if( $option1 ne "" ) { $xlsfil = "$option1"; } $xlsfil=$pathfile_xlsfil; @xls_suffix_list = "\.xls"; ($basename, $dirpath, $suffix) = fileparse( $xlsfil, @xls_suffix_list); if( "$dirpath" eq "\.\\" ) { $xlsfil=sprintf("%s/%s", $cwd, $xlsfil ); } # printf( "$progid xls2pdf $dttm MSG: xlsfil=[$xlsfil]\n" ); # printf( "$progid xls2pdf $dttm MSG: basename=[$basename]\n" ); # printf( "$progid xls2pdf $dttm MSG: dirpath=[$dirpath] suffix=[$suffix]\n" ); printf( "$progid xls2pdf $dttm MSG: xlsfil=[$xlsfil] pathfile_xlsfil=[$pathfile_xlsfil]\n" ); $xlsfil="j:\\ko_center\\oy3\\test.xls"; if( ! -f $xlsfil ) { printf( "$progid xls2pdf $dttm ERR: NOTFOUND xlsfil=[$xlsfil]\n" ); return; } return; $xlsbak = $xlsfil; $xlsbak =~ s/\.xls/_$dttm.xls/; $xlstmp = $xlsfil; $xlstmp =~ s/\.xls/_tmp.xls/; $xlscsv = $xlsfil; $xlscsv =~ s/\.xls/.csv/; $xlspdf = $xlsfil; $xlspdf =~ s/\.xls/.pdf/; printf( "$progid xls2pdf $dttm MSG_800: xlsfil=[$xlsfil] xlspdf=[$xlspdf]\n" ); # open new excel instance if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid xls2pdf $dttm ERR: OLE->new excel=[$excel]\n" ); printf( "$progid xls2pdf $dttm MSG: OLE->new excel=[$excel]\n" ); } else { printf( "$progid xls2pdf $dttm MSG: GetActiveObject DEFINED excel=[$excel]\n" ); } $excel->{'Visible'} = 0; # 0 is hidden, 1 is visible $excel->{DisplayAlerts}=0; # 0 is hide alerts printf( "$progid xls2pdf $dttm MSG_810: open xlsfil=[$xlsfil] excel=[$excel]\n" ); $book = $excel->Workbooks->Open("$xlsfil") || die printf( "$progid xls2pdf $dttm ERR_815: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_815: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid xls2pdf $dttm ERR_820: xlsfil=[$xlsfil] sheet=[$sheet] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG_820: xlsfil=[$xlsfil] sheet=[$sheet] excel=[$excel]\n" ); # color: ActivePrinter => "ACROBAT:", $excel->ActivePrinter->{Value} = "Adobe PDF"; # $excel->ActivePrinter = "Adobe PDF"; ## $active_printer = $excel->ActivePrinter; ## if ($active_printer) { ## print("$progid ts2csv $dttm MSG: active_printer=[$active_printer]\n"); ## } # Save as PDF # ActivePrinter => 'Acrobat PDFWriter'}) # ActivePrinter => 'Acrobat PDF on Ne07:'}) lockup # ActivePrinter => 'Acrobat PDF'}) # Copy => 1, $excel->ActiveWindow->SelectedSheets->PrintOut({ActivePrinter => 'Acrobat PDF', PrintToFile => 1, PrToFileName => "$xlspdf", copies => 1 }) || printf( "$progid $dttm xls2pdf ERR_836\n" ); printf( "$progid $dttm xls2pdf MSG_836\n" ); my @msgtext = $excel->ActiveWindow->SelectedSheets->PrintOut( {ActivePrinter => 'Adobe PDF', PrintToFile => 1, PrToFileName => "$xlspdf", copies => 1 }); print_r(@msgtxt); ## || printf( "$progid xls2pdf $dttm ERR_835: printout excel=[$excel]\n" ); printf( "$progid xls2pdf $dttm MSG_835: printout excel=[$excel]\n" ); ## my @msgtext = $excel->ActiveSheet->PrintOut({ ## PrintToFile => 1, ## PrToFileName => "$xlspdf", ## Copies => 1 ## }) ## || printf( "$progid xls2pdf $dttm ERR: PRINT xlspdf=[$xlsxpdf] active_printer=[$active_printer]\n" );; ## printf( "$progid xls2pdf $dttm MSG: PRINT xlspdf=[$xlspdf] active_printer=[$active_printer]\n" );; # $excel->Workbooks->quit() || printf( "ERR_845: excel->Workbooks->quit()\n" ); $excel->quit || printf( "ERR_846: excel->quit excel=[$excel]\n" ); $book->Close() || printf( "$progid ts2csv $dttm ERR_850: book->Close() book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_850: book->Close() book=[$book] excel=[$excel]\n"); $excel->Quit() || printf( "$progid ts2csv $dttm ERR_855: excel->Quit() book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG_855: excel->Quit() book=[$book] excel=[$excel]\n"); # $book->Save # || printf( "$progid xls2pdf $dttm ERR: book->Save book=[$book] excel=[$excel]\n"); # printf( "$progid xls2pdf $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); undef $book || printf( "$progid xls2pdf $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); undef $excel || printf( "$progid xls2pdf $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid xls2pdf $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); } sub ts2csv_runjob( $option1, $option2, $option3 ) { $pathfile_xlsfil=shift; $option2=shift; $option3=shift; ## printf( "$progid $dttm MSG_480 pathfile_xlsfil=[$pathfile_xlsfil]\n" ); $pkey_h1="0000"; @xls_suffix_list = "\.xls"; ($basename, $dirpath, $suffix) = fileparse( $xlsfil, @xls_suffix_list); $tmp_basename=$basename; $tmp_basename=~s/-/_/g; @filnam_flds=split( /_/, $tmp_basename ); $filnam_fy=sprintf( "20%02d", $filnam_flds[2] ); $filnam_pp=sprintf( "%02d", $filnam_flds[3] ); $filnam_p=sprintf( "%1d", $filnam_flds[4] ); $filnam_enddt=sprintf( "%04d-%02d-%02d", substr($filnam_flds[5],0,4 ), substr($filnam_flds[5],4,2 ), substr($filnam_flds[5],6,2 )); $filnam_id=sprintf( "%02d%02d%1d", substr($filnam_fy,2,2), $filnam_pp,$filnam_p); ## printf( "$progid ts2csv $dttm MSG: filnam_fy=[$filnam_fy]\n" ); ## printf( "$progid ts2csv $dttm MSG: filnam_pp=[$filnam_pp]\n" ); ## printf( "$progid ts2csv $dttm MSG: filnam_p=[$filnam_p]\n" ); ## printf( "$progid ts2csv $dttm MSG: filnam_enddt=[$filnam_enddt]\n" ); ## printf( "$progid ts2csv $dttm MSG: filnam_id=[$filnam_id]\n" ); $xlsfil=$pathfile_xlsfil; if( "$dirpath" eq "\.\\" ) { $xlsfil=sprintf("%s/%s", $cwd, $xlsfil ); } # printf( "$progid ts2csv $dttm MSG: xlsfil=[$xlsfil]\n" ); # printf( "$progid ts2csv $dttm MSG: basename=[$basename]\n" ); # printf( "$progid ts2csv $dttm MSG: dirpath=[$dirpath] suffix=[$suffix]\n" ); # printf( "$progid ts2csv $dttm MSG_512: xlsfil=[$xlsfil]\n" ); # if( ! -f $xls_templet ) { # printf( "$progid ts2csv $dttm ERR: NOTFOUND xls_templet=[$xls_templet]\n" ); # return; # } if( ! -f $xlsfil ) { printf( "$progid ts2csv $dttm ERR: NOTFOUND xlsfil=[$xlsfil]\n" ); return; } $xlsbak = $xlsfil; $xlsbak =~ s/\.xls/_$dttm.xls/; $xlstmp = $xlsfil; $xlstmp =~ s/\.xls/_tmp.xls/; $xlscsv = $xlsfil; $xlscsv =~ s/\.xls/.csv/; $tsscsv = $xlsfil; $tsscsv =~ s/\.xls/_tss.csv/; $tsdcsv = $xlsfil; $tsdcsv =~ s/\.xls/_tsd.csv/; $xlspdf = $xlsfil; $xlspdf =~ s/\.xls/.pdf/; ## printf( "BUGS_540:\n tsscsv=[$tsscsv]\n tsdcsv=[$tsdcsv]\n xlscsv=[$xlscsv] \n xlspdf=[$xlspdf]\n" ); ## return; ## if( -f $xlstmp ) { ## printf( "$progid ts2csv $dttm ERR: FOUND xlstmp=[$xlstmp]\n" ); ## return; ## } ## printf( "$progid ts2csv $dttm DISABLED: copy($xlsfil,$xlstmp)\n" ); ## printf( "$progid ts2csv $dttm MSG: copy($xlsfil,$xlstmp)\n" ); ## copy( $xlsfil, $xlstmp ); ## if( -f $xlsbak ) { ## printf( "$progid ts2csv $dttm DISABLED: FOUND xlsbak=[$xlsbak]\n" ); ## printf( "$progid ts2csv $dttm ERR: FOUND xlsbak=[$xlsbak]\n" ); ## return; ## } ## printf( "$progid ts2csv $dttm BEG: GetActiveObject=[$excel] \$\@=[$@]\n" ); ## open( XLSCSV, ">$xlscsv" ) || die "$progid ts2csv ERR: OPEN tscsv=[$tscsv]\n"; open( TSSCSV, ">$tsscsv" ) || die "$progid ts2csv ERR: OPEN tsscsv=[$tsscsv]\n"; open( TSDCSV, ">$tsdcsv" ) || die "$progid ts2csv ERR: OPEN tsdcsv=[$tsdcsv]\n"; # open existing excel instance ## $excel = Win32::OLE->GetActiveObject('Excel.Application') ## || printf( "$progid ts2csv $dttm ERR: GetActiveObject [$excel]\n" ); ## printf( "$progid ts2csv $dttm MSG: GetActiveObject excel=[$excel]\n" ); # open new excel instance # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ts2csv $dttm ERR: OLE->new excel=[$excel]\n" ); ## printf( "$progid dta2xls_tx $dttm MSG_550: OLE->new excel=[$excel]\n" ); } else { ## printf( "$progid dta2xls_tx $dttm MSG_560: GetActiveObject DEFINED excel=[$excel]\n" ); } ## printf( "$progid ts2csv $dttm BEG: open xls_templet=[$xls_templet] excel=[$excel]\n" ); ## $book = $excel->Workbooks->Open("$xlstmp") ## || die printf( "$progid ts2csv $dttm ERR: open xlsfil=[$xls_templet] book=[$book] excel=[$excel]\n"); $book = $excel->Workbooks->Open("$xlsfil") || die printf( "$progid ts2csv $dttm ERR: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG_570: open xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid ts2csv $dttm ERR_580: sheet xlsfil=[$xlsfil] excel=[$excel]\n"); # pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq # printf( "$progid ts2csv $dttm BEG_590: display xlsfil=[$xlsfil]\n" ); # @hrs=""; $hrs[0][0]=""; $daynum=0; $flipflop=1; # $excel->{Visible} = 0; # black and white: ActivePrinter => '\\NETPRINTSRV\p21 on Ne01:', # color: ActivePrinter => "ACROBAT:", # default printer: ActivePrinter => $pobj, ## printf( "$progid ts2csv $dttm BEG: excel=[$excel]\n" ); $period=$sheet->Range("$period_range")->{Value} || die printf( "$progid ts2csv $dttm ERR: period=[$period] excel=[$excel]\n" ); ## printf( "$progid ts2csv $dttm MSG_600: period=[$period]\n" ); # printf( "$progid ts2csv $dttm MSG_600: period=[$period] excel=[$excel]\n" ); # get period info from formated title # period_13-1_20091227-20090102 $period =~ s/-/_/g; # period_13_1_20091227_20090102 @period_flds=split(/_/,$period); $period_pp=$period_flds[1]; $period_p=$period_flds[2]; $begdt_text=$period_flds[3]; $enddt_text=$period_flds[4]; $fy=substr($enddt_text,0,4); $begdt=sprintf( "%04d-%02d-%02d", substr($begdt_text,0,4), substr($begdt_text,4,2), substr($begdt_text,6,2) ); $enddt=sprintf( "%04d-%02d-%02d", substr($enddt_text,0,4), substr($enddt_text,4,2), substr($enddt_text,6,2) ); # $keytext=sprintf( "%s,%s,%s,%s,%s,", $fy,$period_pp, $period_p, $begdt, $enddt ); $keytext=sprintf( "%s,%s,%s", $fy,$period_pp, $period_p ); $title=$sheet->Range("$period_range")->{Value}; ## printf( "pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq\n" ); ## printf( "$progid ts2csv $dttm MSG_630:\ntitle=[$title]\n" ); ## printf( "bdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbd\n" ); ## return; $daynum=0; foreach $col (@week_cols_h1 ) { ## printf( "$progid $dttm MSG_640: daynum=[$daynum] col=[$col] xlsfil=[$xlsfil]\n" ); $day[$daynum]=$sheet->Range("$col$day_row:$col$day_row")->{Value}; $sheet->Range("$col$dt_row:$col$dt_row")->{NumberFormat} ="yyyy-mm-dd"; $dt[$daynum]=$sheet->Range("$col$dt_row:$col$dt_row")->{Value}; @dt_flds=split(/\//,$dt[$daynum] ); $effdt[$daynum]=sprintf( "%04d-%02d-%02d", $dt_flds[2],$dt_flds[0], $dt_flds[1] ); ## printf( "($col$day_row) $daynum day=$day[$daynum]\n" ); ## printf( "BUGS_645: ($col$dt_row) $daynum dt=$dt[$daynum] effdt=$effdt[$daynum]\n" ); $tsdrec_hdr=sprintf( "$period,$filnam_id$pkey_h1,$keytext,$hrstext,$xlsfil,status," ); $tsdrec_days = ""; foreach $row (@time_rows) { # $week_cols_h1 = qw!B F J N R V Z!; $range_h1=sprintf( "%s%s:%s%s", $week_cols_h1[$daynum],$row,$week_cols_h1[$daynum],$row ); $range_h2=sprintf( "%s%s:%s%s", $week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],$row ); $range_tm=sprintf( "%s%s:%s%s", $week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],$row ); $range_cd=sprintf( "%s%s:%s%s", $week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],$row ); ## printf( "$progid ts2csv $dttm MSG: r=($range) r1=($range1) r2=($range2)\n" ); $hrs[$daynum][0]=$sheet->Range("$range_h1")->{Value}; $hrs[$daynum][1]=$sheet->Range("$range_h2")->{Value}; $hrs[$daynum][2]=$sheet->Range("$range_tm")->{Value}; $hrs[$daynum][3]=$sheet->Range("$range_cd")->{Value}; $rangetext=sprintf( "h1=(%s) h2=(%s) tm=(%s) cd=(%s)", $range_h1,$range_h2,$range_tm,$range_cd ); ## printf( "$progid ts2csv $dttm MSG: rangetext=[$rangetext]\n " ); # $hrs[$daynum][0], $h1=$hrs[$daynum][0]; if( "$h1" eq "" ) { $h1=sprintf( "%04d", $pkey_h1 ); } $pkey_h1=sprintf( "%04d", ($pkey_h1+250)); if( $pkey_h1 > 2400 ) { $pkey_h1="0000"; } ## printf( "$progid ts2csv $dttm MSG_670: daynum=$daynum h1=[$h1] [pkey_h1=[$pkey_h1] hrs=$hrs[$daynum][0]\n" ); $hrstext=sprintf( "%s,%s,%s,%s,%s,%s,", $effdt[$daynum], $day[$daynum], $h1, $hrs[$daynum][1], $hrs[$daynum][2], $hrs[$daynum][3] ); ## $tsdrec_days .= "$hrs[$daynum][0],$hrs[$daynum][1],$hrs[$daynum][2],$hrs[$daynum][3],"; ## printf( TSDCSV "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n" ); $tsdrec = $tsdrec_hdr . $hrstext . "\n"; printf( TSDCSV $tsdrec ); ## $tsdrec = $tsdrec_hdr . $tsdrec_days; ## printf( TSDCSV "$tsdrec\n" ); ## $csvrec_hdr = ""; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,\n"; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,,,\n"; ## $csvrec_hrs = ""; ## $csvrec_hdrdt=""; ## printf( "$progid $dttm MSG_698: tsdrec=[$tsdrec]\n" ); ## printf( "$progid $dttm MSG_699: tssrec=[$tssrec]\n" ); ## ## printf( "$progid $dttm MSG_700: daynum=[$daynum]\n" ); ## $csvrec_hdrdt .= "$period,$hdrdt[$daynum],"; ## ## $csvrec_dtl .= "$week_cols_h1[$daynum],$week_cols_h1[$daynum],"; ## $csvrec_dtl .= "$week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],"; ## $csvrec_dtl .= "$week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],"; ## $csvrec_dtl .= "$week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],"; ## $csvrec_dtl .= "$hrs[$daynum][0],"; ## $csvrec_dtl .= "$hrs[$daynum][1],"; ## $csvrec_dtl .= "$hrs[$daynum][2],"; ## $csvrec_dtl .= "$hrs[$daynum][3],"; ## ## $csvrec_hrs = ""; ## for( $slots=1; $slots<10; $slots++ ) { ## $csvrec_hrs .= "$period,"; ## $csvrec_hrs .= "$begdt[$daynum][$slot],"; ## $csvrec_hrs .= "$enddt[$daynum][$slot],"; ## $csvrec_hrs .= "$hrs[$daynum][$slot],"; ## $csvrec_hrs .= "$code[$daynum][$slot],\n"; ## } ## ## $csvrec_hdrdt .= "\n"; ## $csvrec_dtl .= "\n"; ## $csvrec = ""; ## $csvrec .= $csvrec_hdr . $csvrec_hdrdt . $csvrec_dtl . "," . $csvrec_hrs . "\n"; ## $csvrec .= "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n"; # printf( "BUGS_730: csvrec=[$csvrec] xlscsv=[$slxcsv]\n" ); ## printf( XLSCSV "$csvrec\n" ); # printf( XLSCSV "$pkey,$keytext,$hrstext,,$col$row,desc,\n" ); # printf( XLSCSV "$keytext,$hrstext,amt,amtc,amtg,$col$row,desc,\n" ); # printf( "BUGS_680: $filnam_id$pkey_h1,$keytext,$hrstext,$col$row,desc\n" ); # $sheet->Range("$range_h1")->{Value}=$row*100; # $sheet->Range("$range_h2")->{Value}=$row*100+150; # if( $flipflop == 1 ) { # $sheet->Range("$range_cd")->{Value}="c"; # } # else { # $sheet->Range("$range_cd")->{Value}="g"; # } # $flipflop=$flipflop*(-1); } $daynum++; printf( "\n" ); } $tssrec=sprintf( "%s,%s,%s,%s,hrs,d1,d2,d3,d4,d5,d6,d7, e1,e2,e3,e4,e5,e6,e7,h1,h2,h3,h4,h5,h6,h7\n", $title, $begdt, $enddt ); printf( TSSCSV "$tssrec\n" ); # close( XLSCSV ); close( TSSCSV ); close( TSDCSV ); ## my $pobj = $excel->ActivePrinter; ## if ($pobj) { print("Name of default printer =>>$pobj<<\n"); } ## printf( "$progid ts2csv $dttm END: display\n" ); printf( "$progid ts2csv $dttm END_760: quit \nxlsfil=[$xlsfil] \nbook=[$book] \nexcel=[$excel]\n" ); $xlstmp="ko_center_tmp.xls"; $excel-> {DisplayAlerts} = 0; # This turns off the "This file already exists" message. ## $book->SaveAs ("$xlstmp") || die printf( "$progid ts2csv $dttm ERR: book->saveas xlstmp=[$xlstmp] excel=[$excel]\n" ); $book->Close() || die printf( "$progid ts2csv $dttm ERR: book->close xlstmp=[$xlstmp] excel=[$excel]\n" ); ## $book->quit || die printf( "$progid ts2csv $dttm ERR: book->quit xlstmp=[$xlstmp] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: quit: xlstmp=[$xlstmp] excel=[$excel]\n" ); # $excel->Workbooks->quit() || printf( "ERR: excel->Workbooks->quit()\n" ); ## $excel->quit || die printf( "$progid ts2csv $dttm ERR: excel->quit excel=[$excel]\n" ); ### $workbook -> SaveAs ("C:\\file_name.xls"); ### $excel -> Quit; # err $excel->ActiveWorkbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->ActiveWorkbook->Close(0) book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->ActiveWorkbooks->Close(0) book=[$book] excel=[$excel]\n"); ## $book = $excel->Workbooks->Open("$xlsfil") # $excel->Workbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # $book->Close() # || printf( "$progid ts2csv $dttm ERR: book->Close() book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Close() book=[$book] excel=[$excel]\n"); # $excel->Quit # || printf( "$progid ts2csv $dttm ERR: excel->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Quit book=[$book] excel=[$excel]\n"); # $book->Quit # || printf( "$progid ts2csv $dttm ERR: book->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); # $book->Save # || printf( "$progid ts2csv $dttm ERR: book->Save book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); ## undef $book || ## printf( "$progid ts2csv $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## undef $excel || ## printf( "$progid ts2csv $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## printf( "$progid ts2csv $dttm END: unlink=[$xlstmp]\n" ); ## unlink( $xlstmp ); ## printf( "$progid ts2csv $dttm END: xlsfil=[$xlsfil]\n" ); ## $cmd=sprintf( "cat %s", $xlscsv ); ## system($cmd); ## printf( "$progid ts2csv $dttm END: cmd=[$cmd]\n" ); ## $cmd=sprintf( "%s -csv %s \".import %s hrs\"", $sqlbin, $sqldb, $xlscsv ); ## system($cmd); ## printf( "$progid ts2csv $dttm END_765: cmd=[$cmd]\n" ); } sub db2ts( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; $sqldb="ko_center.db3"; printf( "$progid $dttm MSG_1700: option1=[$option1][$option2] dbfile=[$sqldb]\n" ); } sub sqlval2( $dbfile, $sql ) { $dbfile=shift; $sql=shift; # use strict; # use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbfile", "","", { RaiseError => 1 },) or die $DBI::errstr; my $sth = $dbh->prepare( $sql ); my $headers = $sth->{NAME}; my ($id, $name, $price) = @$headers; printf "%s %-10s %s\n", $id, $name, $price; $sth->execute(); my $row; while($row = $sth->fetchrow_hashref()) { printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price}; } $sth->finish(); $dbh->disconnect(); } sub sqlval( $dbfile, $sql ) { $dbfile=shift; $sql=shift; my $db = DBI->connect( "dbi:SQLite:$dbfile" ) || die printf( "$progid $dttm sqlval ERR_1735: dbi->connect dbfile=[$dbfile] \n" ); my $rows = $db->selectall_arrayref("$sql") || die printf( "$progid $dttm sqlval ERR_1736: db->selectall_arrayref($sql)\n" ); $db->disconnect(); foreach my $row (@$rows) { $j++; foreach my $col (@$row ) { $val= $col; ## printf( "$dttm _1745: $j val=[$val] sql=[$sql][$dbfile]\n" ); } ## last; } return( $val ); } sub sqlvals( $dbfile, $sql ) { $dbfile=shift; $sql=shift; my $db = DBI->connect( "dbi:SQLite:$dbfile" ) || die printf( "$progid $dttm sqlval ERR_1755: dbi->connect dbfile=[$dbfile] \n" ); my $rows = $db->selectall_arrayref("$sql") || die printf( "$progid $dttm sqlval ERR_1756: db->selectall_arrayref($sql)\n" ); $db->disconnect(); return( @$rows ); $data=""; printf( "$progid $dttm MSG_1760: dbfile=[$dbfile] sql=[$sql]\n" ); foreach my $row (@$rows) { $j++; foreach my $col (@$row) { $data .= "$col|"; ## printf( "BUGS_1765: col=[$col] sql=[$sql]\n" ); } $data .= "\n"; ## last; } return( $data ); } sub sql_dumper() { # my $sth = $dbh->column_info('','',$table,$column); # or $column='' for all # my $info = $sth->fetchall_arrayref({}); # use Data::Dumper; print Dumper($info); # while (my $row = $result->fetchrow_hashref) { # my $csv = join(',', values %$row); # #print "$csv\n"; # } # # $result->finish; # $db->disconnect; } sub sqldata( $dbfile, $sql ) { $dbfile=shift; $sql=shift; printf( "$progid $dttm sqldata MSG_1730: dbfile=[$dbfile] sql=[$sql]\n" ); return; ## $sql="select yyyy, p, q, wk, begdt, begday, enddt, endday, desc from periods"; ## $sql="select yyyy from periods"; $db = DBI->connect( "dbi:SQLite:$dbfile" ) || die printf( "$progid $dttm ERR_1730: dbi->connect dbfile=[$dbfile] \n" ); $rows = $db->selectall_arrayref($sql) || die printf( "$progid $dttm ERR_1731: db->selectall_arrayref($sql)\n".$dbh->errstr() ); ### $dbh_errstr=$db->errstr(); ### printf( "$progid $dttm MSG"_1732: $dbh_errstr\n" ); $db->disconnect(); my $j=0; foreach my $row (@$rows) { # my ( $yyyy, $host, $info, $shell, $spwchg, $spwchg_dt, $spwchg_age ) = @$row; ( $yyyy, $p, $q, $wk, $begdt, $begday, $enddt, $endday, $desc ) = @$row; # printf( "$progid sqldata MSG_1740: user=[$user] host=[$host] $info $homedir spwchg=[$spwchg][$spwchg_dt][$spwchg_age]\n" ); } printf( "$progid sqldata MSG_1742: yyyy=[$yyyy] fy=[$fy] p=[$p] q=[$q] begdt=[$begdt][$enddt] [$endday]\n" ); exit; $data=""; foreach my $col (@$rows) { $j++; # ( $yyyy, $fy, $p, $q, $wk, $begdt, $begday, $enddt, $endday, desc ) = @$col; ( $yyyy ) = @$col; printf( "$progid sqldata MSG_1736: $j [$yyyy][$fy][$p][$q][$wk][$begdt][enddt][$begday][$endday][$desc]\n" ); } printf( "$progid $dttm sqlvals MSG_1755: $j data=[$data]\n" ); return( $data ); } sub db2saic1( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; $dtm_data_1=$option1; $dtm_data_2=$option2; my ( $mydt_1, $myt1_1, $myt2_1, $myhr_1, $mycd_1, $mydesc_1 ) = split( /\|/, $dtm_data_1 ); my ( $mydt_2, $myt1_2, $myt2_2, $myhr_2, $mycd_2, $mydesc_2 ) = split( /\|/, $dtm_data_2 ); if( $myhr_1 == "" ) { $myhr_1 = $myt2_1 - $myt1_1; } if( $myhr_2 == "" ) { $myhr_2 = $myt2_2 - $myt1_2; } ## $where_clause="where date('$mydt') between date(begdt) and date(enddt)"; ## $sql_dtm="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est from periods $where_clause"; $where_clause="where date('mydt') between date($mydt_1) and date($mydt_2)"; $where_clause=""; $sql_dtm="select dt, t1, t2, hr, cd, desc from dtm $where_clause"; printf( "$progid $dttm MSG_2560 dbfile=[$dbfile] sql_dtm=[$sql_dtm]\n" ); printf( "$progid $dttm MSG_2560 mydt_1=[$mydt_1] mydt_2=[$mydt_2]\n" ); @$rows = &sqlvals( $dbfile, $sql_dtm ); $rowcnt=0; $hrcnt=0; foreach my $row (@$rows) { $rowcnt++; $hrccnt++; ( $mydt, $myt1, $myt2, $myhr, $mycd, $mydesc ) = @$row; $dtm_data = "$mydt|$myt1|$myt2|$myhr|$mycd|$mydesc"; printf( "\n$progid $dttm MSG_3005: $rowcnt dtm_data=[$dtm_data]" ); printf( "\n$progid $dttm MSG_3006: $rowcnt mydt=$mydt myt1=[$myt1] myt2=$myt2 mycd=[$mycd]\n" ); &dt2kxls( $dtm_data, $option2, $option3 ); } return; } # ERR database is locked sub dt2saic( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; $dtm_data=$option1; my ( $mydt, $myt1, $myt2, $myhr, $mycd, $mydesc ) = split( /\|/, $dtm_data ); # @filnam_flds=split( /_/, $tmp_basename ); printf( "$progid $dttm MSG_2521: option1=[$option1][$option2] dbfile=[$dbfile]\n" ); if( $myhr == "" ) { $myhr = $myt2 - $myt1; } # ERR database is locked # echo ".dump" | sqlite3 ko_center.db3 | sqlite3 ko_center_dump.db3 # get dayofweek ## $sql_daynum="select count(*) from periods"; $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); $dayofweek = &sqlval( $dbfile, $sql_dayofweek ); ## printf( "$progid $dttm MSG_1805: dayofweek=[$dayofweek] sql_dayofweek=[$sql_dayofweek]\n" ); printf( "$progid $dttm MSG_2600: option1=[$option1] [$mydt] [$myt1][$myt2] [$myhr][$mycd] [$mydesc]\n" ); ## printf( "$progid $dttm MSG_2600: dbfile=[$dbfile]\n" ); # get dayofweek $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); $dayofweek_num = &sqlval( $dbfile, $sql_dayofweek ); $dayofweek_order = "1 2 3 4 5 6 0"; $dayofweek_info[0] = "1 E:20 Sun"; $dayofweek_info[1] = "2 I:20 Mon"; $dayofweek_info[2] = "3 M:20 Tue"; $dayofweek_info[3] = "4 Q:20 Wed"; $dayofweek_info[4] = "5 U:20 Thr"; $dayofweek_info[5] = "6 X:20 Fri"; $dayofweek_info[6] = "0 AC:20 Sat"; ( $dayofweek_order, $rowcol, $dayofweek_name ) = split( / /, $dayofweek_info[$dayofweek_num] ); printf( "$progid $dttm MSG_2145: dayofweek_order=[$dayofweek_order] dayofweek_name=[$dayofweek_name] $rowcol\n" ); printf( "$progid $dttm MSG_2240: dayofweek_info=[$dayofweek_info]\n" ); ## printf( "$progid $dttm MSG_1870: dayofweek=[$dayofweek] sql_dayopweek=[$sql_dayofweek]\n"); ## $sql_dayofweek=sprintf( "select strftime('\%w', '%s' );", $mydt ); ## $seldayofweek=sprintf("strftime('\%w', '%s' )",$mydt ); $where_clause="where date('$mydt') between date(begdt) and date(enddt)"; $sql_periods="select yyyy, fy, p, q, wk, begdt, enddt, endday, desc, oy, est from periods $where_clause"; ## $sql_periods="select yyyy,fy,p,q,wk,begdt,enddt,oy,endday,desc,f1 est from periods where date('2012-11-12') between date(begdt) and date(enddt);"; printf( "$progid $dttm MSG_1880 dbfile=[$dbfile] sql_periods=[$sql_periods]\n" ); @$rows = &sqlvals( $dbfile, $sql_periods ); $rowcnt=0; $co="konet"; $user="kassilj"; $user_name="joachim kassil"; $co_name="konet llc"; $tstemplet_xlsfil="konet_ts_templet.xls"; $invtemplet_xlsfil="konet_inv_templet.xls"; $pathwork="s:/xampp/htdocs/kxls/work/"; $pathwork_win="s:\\xampp\\htdocs\\ko_center\\work\\"; $tstemplet_path_xlsfil_win="$pathwork_win$tstemplet_xlsfil"; $invtemplet_path_xlsfil_win="$pathwork_win$invtemplet_xlsfil"; ## &ole_templet2ts_detail_close(); foreach my $row (@$rows) { $rowcnt++; ( $yyyy, $fy, $p, $q, $wk, $begdt, $enddt, $endday, $desc, $oy, $est ) = @$row; printf( "\n$progid $dttm MSG_3085: $rowcnt $yyyy fy=[%d] period=[%d] q=[%d] wk=[%d] oy=[%d] est=[%d]\n", $rowcnt, $yyyy, $fy, $p, $q, $wk, $oy, $est ); # $ts_txtfil=sprintf( "konet_ts_fy-pp-w_yyyymmdd-yyyymmdd.txt" ); $begdttm=$begdt; $begdttm =~ s/-//g; $enddttm=$enddt; $enddttm =~ s/-//g; &ole_templet2ts( $yyyy, $fy, $p, $q, $begdt, $enddt, $dtm_data, $dayofweek_info ); ## &ole_templet2ts_detail( $yyyy, $fy, $p, $q, $begdt, $enddt, $dtm_data, $dayofweek_info ); } ## &ole_templet2ts_detail_close(); printf( "$progid $dttm MSG_2620: yyyy=[$yyyy] fy=[$fy] p=[$p] q=[$q] wk=[$wk]\n" ); printf( "$progid $dttm MGS_2620: begdt=[$begdt] enddt=[$enddt] begday=[$begday]\n" ); printf( "$progid db2ts $dttm END_1180: \n" ); return; # pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq # printf( "$progid ts2csv $dttm BEG_1140: display xlsfil=[$xlsfil]\n" ); # @hrs=""; $hrs[0][0]=""; $daynum=0; $flipflop=1; # $excel->{Visible} = 0; # black and white: ActivePrinter => '\\NETPRINTSRV\p21 on Ne01:', # color: ActivePrinter => "ACROBAT:", # default printer: ActivePrinter => $pobj, ## printf( "$progid ts2csv $dttm BEG: excel=[$excel]\n" ); $period=$sheet->Range("$period_range")->{Value} || die printf( "$progid ts2csv $dttm ERR: period=[$period] excel=[$excel]\n" ); ## printf( "$progid ts2csv $dttm MSG_600: period=[$period]\n" ); # printf( "$progid ts2csv $dttm MSG_600: period=[$period] excel=[$excel]\n" ); # get period info from formated title # period_13-1_20091227-20090102 $period =~ s/-/_/g; # period_13_1_20091227_20090102 @period_flds=split(/_/,$period); $period_pp=$period_flds[1]; $period_p=$period_flds[2]; $begdt_text=$period_flds[3]; $enddt_text=$period_flds[4]; $fy=substr($enddt_text,0,4); $begdt=sprintf( "%04d-%02d-%02d", substr($begdt_text,0,4), substr($begdt_text,4,2), substr($begdt_text,6,2) ); $enddt=sprintf( "%04d-%02d-%02d", substr($enddt_text,0,4), substr($enddt_text,4,2), substr($enddt_text,6,2) ); # $keytext=sprintf( "%s,%s,%s,%s,%s,", $fy,$period_pp, $period_p, $begdt, $enddt ); $keytext=sprintf( "%s,%s,%s", $fy,$period_pp, $period_p ); $title=$sheet->Range("$period_range")->{Value}; ## printf( "pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq\n" ); ## printf( "$progid ts2csv $dttm MSG_630:\ntitle=[$title]\n" ); ## printf( "bdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbdbd\n" ); ## return; $daynum=0; foreach $col (@week_cols_h1 ) { ## printf( "$progid $dttm MSG_640: daynum=[$daynum] col=[$col] xlsfil=[$xlsfil]\n" ); $day[$daynum]=$sheet->Range("$col$day_row:$col$day_row")->{Value}; $sheet->Range("$col$dt_row:$col$dt_row")->{NumberFormat} ="yyyy-mm-dd"; $dt[$daynum]=$sheet->Range("$col$dt_row:$col$dt_row")->{Value}; @dt_flds=split(/\//,$dt[$daynum] ); $effdt[$daynum]=sprintf( "%04d-%02d-%02d", $dt_flds[2],$dt_flds[0], $dt_flds[1] ); ## printf( "($col$day_row) $daynum day=$day[$daynum]\n" ); ## printf( "BUGS_645: ($col$dt_row) $daynum dt=$dt[$daynum] effdt=$effdt[$daynum]\n" ); $tsdrec_hdr=sprintf( "$period,$filnam_id$pkey_h1,$keytext,$hrstext,$xlsfil,status," ); $tsdrec_days = ""; foreach $row (@time_rows) { # $week_cols_h1 = qw!B F J N R V Z!; $range_h1=sprintf( "%s%s:%s%s", $week_cols_h1[$daynum],$row,$week_cols_h1[$daynum],$row ); $range_h2=sprintf( "%s%s:%s%s", $week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],$row ); $range_tm=sprintf( "%s%s:%s%s", $week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],$row ); $range_cd=sprintf( "%s%s:%s%s", $week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],$row ); ## printf( "$progid ts2csv $dttm MSG: r=($range) r1=($range1) r2=($range2)\n" ); $hrs[$daynum][0]=$sheet->Range("$range_h1")->{Value}; $hrs[$daynum][1]=$sheet->Range("$range_h2")->{Value}; $hrs[$daynum][2]=$sheet->Range("$range_tm")->{Value}; $hrs[$daynum][3]=$sheet->Range("$range_cd")->{Value}; $rangetext=sprintf( "h1=(%s) h2=(%s) tm=(%s) cd=(%s)", $range_h1,$range_h2,$range_tm,$range_cd ); ## printf( "$progid ts2csv $dttm MSG: rangetext=[$rangetext]\n " ); # $hrs[$daynum][0], $h1=$hrs[$daynum][0]; if( "$h1" eq "" ) { $h1=sprintf( "%04d", $pkey_h1 ); } $pkey_h1=sprintf( "%04d", ($pkey_h1+250)); if( $pkey_h1 > 2400 ) { $pkey_h1="0000"; } ## printf( "$progid ts2csv $dttm MSG_670: daynum=$daynum h1=[$h1] [pkey_h1=[$pkey_h1] hrs=$hrs[$daynum][0]\n" ); $hrstext=sprintf( "%s,%s,%s,%s,%s,%s,", $effdt[$daynum], $day[$daynum], $h1, $hrs[$daynum][1], $hrs[$daynum][2], $hrs[$daynum][3] ); ## $tsdrec_days .= "$hrs[$daynum][0],$hrs[$daynum][1],$hrs[$daynum][2],$hrs[$daynum][3],"; ## printf( TSDCSV "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n" ); $tsdrec = $tsdrec_hdr . $hrstext . "\n"; printf( TSDCSV $tsdrec ); ## $tsdrec = $tsdrec_hdr . $tsdrec_days; ## printf( TSDCSV "$tsdrec\n" ); ## $csvrec_hdr = ""; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,\n"; ## $csvrec_hdr .= "$period,$xlscsv,,,,,,,,\n"; ## $csvrec_hrs = ""; ## $csvrec_hdrdt=""; ## printf( "$progid $dttm MSG_698: tsdrec=[$tsdrec]\n" ); ## printf( "$progid $dttm MSG_699: tssrec=[$tssrec]\n" ); ## ## printf( "$progid $dttm MSG_700: daynum=[$daynum]\n" ); ## $csvrec_hdrdt .= "$period,$hdrdt[$daynum],"; ## ## $csvrec_dtl .= "$week_cols_h1[$daynum],$week_cols_h1[$daynum],"; ## $csvrec_dtl .= "$week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],"; ## $csvrec_dtl .= "$week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],"; ## $csvrec_dtl .= "$week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],"; ## $csvrec_dtl .= "$hrs[$daynum][0],"; ## $csvrec_dtl .= "$hrs[$daynum][1],"; ## $csvrec_dtl .= "$hrs[$daynum][2],"; ## $csvrec_dtl .= "$hrs[$daynum][3],"; ## ## $csvrec_hrs = ""; ## for( $slots=1; $slots<10; $slots++ ) { ## $csvrec_hrs .= "$period,"; ## $csvrec_hrs .= "$begdt[$daynum][$slot],"; ## $csvrec_hrs .= "$enddt[$daynum][$slot],"; ## $csvrec_hrs .= "$hrs[$daynum][$slot],"; ## $csvrec_hrs .= "$code[$daynum][$slot],\n"; ## } ## ## $csvrec_hdrdt .= "\n"; ## $csvrec_dtl .= "\n"; ## $csvrec = ""; ## $csvrec .= $csvrec_hdr . $csvrec_hdrdt . $csvrec_dtl . "," . $csvrec_hrs . "\n"; ## $csvrec .= "$filnam_id$pkey_h1,$keytext,$hrstext,$col$row,$xlsfil,$status,desc\n"; # printf( "BUGS_730: csvrec=[$csvrec] xlscsv=[$slxcsv]\n" ); ## printf( XLSCSV "$csvrec\n" ); # printf( XLSCSV "$pkey,$keytext,$hrstext,,$col$row,desc,\n" ); # printf( XLSCSV "$keytext,$hrstext,amt,amtc,amtg,$col$row,desc,\n" ); # printf( "BUGS_680: $filnam_id$pkey_h1,$keytext,$hrstext,$col$row,desc\n" ); # $sheet->Range("$range_h1")->{Value}=$row*100; # $sheet->Range("$range_h2")->{Value}=$row*100+150; # if( $flipflop == 1 ) { # $sheet->Range("$range_cd")->{Value}="c"; # } # else { # $sheet->Range("$range_cd")->{Value}="g"; # } # $flipflop=$flipflop*(-1); } $daynum++; printf( "\n" ); } # $tssrec=sprintf( "%s,%s,%s,%s,hrs,d1,d2,d3,d4,d5,d6,d7, e1,e2,e3,e4,e5,e6,e7,h1,h2,h3,h4,h5,h6,h7\n", # $title, $begdt, $enddt ); # printf( TSSCSV "$tssrec\n" ); # close( XLSCSV ); # close( TSSCSV ); # close( TSDCSV ); ## my $pobj = $excel->ActivePrinter; ## if ($pobj) { print("Name of default printer =>>$pobj<<\n"); } ## printf( "$progid ts2csv $dttm END: display\n" ); printf( "$progid db2ts $dttm END_1280: quit \nxlsfil=[$xlsfil] \nbook=[$book] \nexcel=[$excel]\n" ); $xlstmp="ko_center_tmp.xls"; $excel-> {DisplayAlerts} = 0; # This turns off the "This file already exists" message. $book->SaveAs ("$xlsfil") || die printf( "$progid ts2csv $dttm ERR: book->saveas xlstmp=[$xlstmp] excel=[$excel]\n" ); $book->Close() || die printf( "$progid ts2csv $dttm ERR: book->close xlstmp=[$xlstmp] excel=[$excel]\n" ); ## $book->quit || die printf( "$progid ts2csv $dttm ERR: book->quit xlstmp=[$xlstmp] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: quit: xlstmp=[$xlstmp] excel=[$excel]\n" ); # $excel->Workbooks->quit() || printf( "ERR: excel->Workbooks->quit()\n" ); ## $excel->quit || die printf( "$progid ts2csv $dttm ERR: excel->quit excel=[$excel]\n" ); ### $workbook -> SaveAs ("C:\\file_name.xls"); ### $excel -> Quit; # err $excel->ActiveWorkbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->ActiveWorkbook->Close(0) book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->ActiveWorkbooks->Close(0) book=[$book] excel=[$excel]\n"); ## $book = $excel->Workbooks->Open("$xlsfil") # $excel->Workbooks->Close # || printf( "$progid ts2csv $dttm ERR: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Workbooks->Close book=[$book] excel=[$excel]\n"); # $book->Close() # || printf( "$progid ts2csv $dttm ERR: book->Close() book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Close() book=[$book] excel=[$excel]\n"); # $excel->Quit # || printf( "$progid ts2csv $dttm ERR: excel->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: excel->Quit book=[$book] excel=[$excel]\n"); # $book->Quit # || printf( "$progid ts2csv $dttm ERR: book->Quit book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); # $book->Save # || printf( "$progid ts2csv $dttm ERR: book->Save book=[$book] excel=[$excel]\n"); # printf( "$progid ts2csv $dttm MSG: book->Quit book=[$book] excel=[$excel]\n"); ## undef $book || ## printf( "$progid ts2csv $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## undef $excel || ## printf( "$progid ts2csv $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); ## printf( "$progid ts2csv $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); ## printf( "$progid ts2csv $dttm END: unlink=[$xlstmp]\n" ); ## unlink( $xlstmp ); ## printf( "$progid ts2csv $dttm END: xlsfil=[$xlsfil]\n" ); ## $cmd=sprintf( "cat %s", $xlscsv ); ## system($cmd); ## printf( "$progid ts2csv $dttm END: cmd=[$cmd]\n" ); ## $cmd=sprintf( "%s -csv %s \".import %s hrs\"", $sqlbin, $sqldb, $xlscsv ); ## system($cmd); ## printf( "$progid ts2csv $dttm END_765: cmd=[$cmd]\n" ); } # xls update from csv sub csv2ts( $option1, $option2, $option3 ) { $pathfile_xlsfil=shift; $option2=shift; $option3=shift; # printf( "$progid ts2csv $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); # use Win32::OLE qw(in with); # use Win32::OLE::Const 'Microsoft Excel'; # use Win32::OLE::Variant; $Win32::OLE::Warn = 3; # die on errors... @xls_suffix_list = "\.xls"; ($basename, $dirpath, $suffix) = fileparse( $xlsfil, @xls_suffix_list); $xlsfil=$pathfile_xlsfil; if( "$dirpath" eq "\.\\" ) { $xlsfil=sprintf("%s/%s", $cwd, $xlsfil ); } # printf( "$progid ts2csv $dttm MSG: xlsfil=[$xlsfil]\n" ); # printf( "$progid ts2csv $dttm MSG: basename=[$basename]\n" ); # printf( "$progid ts2csv $dttm MSG: dirpath=[$dirpath] suffix=[$suffix]\n" ); printf( "$progid ts2csv $dttm MSG: xlsfil=[$xlsfil]\n" ); # if( ! -f $xls_templet ) { # printf( "$progid ts2csv $dttm ERR: NOTFOUND xls_templet=[$xls_templet]\n" ); # return; # } $xlsbak = $xlsfil; if( ! -f $xlsfil ) { printf( "$progid ts2csv $dttm ERR: NOTFOUND xlsfil=[$xlsfil]\n" ); return; } $xlsbak = $xlsfil; $xlsbak =~ s/\.xls/_$dttm.xls/; $xlstmp = $xlsfil; $xlstmp =~ s/\.xls/_tmp.xls/; $xlscsv = $xlsfil; $xlscsv =~ s/\.xls/.csv/; $xlspdf = $xlsfil; $xlspdf =~ s/\.xls/.pdf/; if( -f $xlstmp ) { printf( "$progid ts2csv $dttm ERR: FOUND xlstmp=[$xlstmp]\n" ); return; } if( -f $xlsbak ) { printf( "$progid ts2csv $dttm ERR: FOUND xlsbak=[$xlsbak]\n" ); return; } printf( "$progid ts2csv $dttm MSG: xlsfil=[$xlsfil]\n" ); printf( "$progid ts2csv $dttm MSG: xlsbak=[$xlsbak]\n" ); printf( "$progid ts2csv $dttm MSG: xlstmp=[$xlstmp]\n" ); printf( "$progid ts2csv $dttm BEG: GetActiveObject=[$excel] \$\@=[$@]\n" ); open( XLSCSV, ">$xlscsv" ); # open existing excel instance ## $excel = Win32::OLE->GetActiveObject('Excel.Application') ## || printf( "$progid ts2csv $dttm ERR: GetActiveObject [$excel]\n" ); ## printf( "$progid ts2csv $dttm MSG: GetActiveObject excel=[$excel]\n" ); # open new excel instance # if (! defined $excel) { if (("$excel" eq "1" ) || ( "$excel" eq "" )) { $excel = Win32::OLE->new('Excel.Application', 'Quit') || die printf( "$progid ts2csv $dttm ERR: OLE->new excel=[$excel]\n" ); printf( "$progid dta2xls_tx $dttm MSG: OLE->new excel=[$excel]\n" ); } else { printf( "$progid dta2xls_tx $dttm MSG: GetActiveObject DEFINED excel=[$excel]\n" ); } printf( "$progid ts2csv $dttm BEG: open xls_templet=[$xls_templet] excel=[$excel]\n" ); $book = $excel->Workbooks->Open("$xlsfil") || die printf( "$progid ts2csv $dttm ERR: open xlsfil=[$xls_templet] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG: open xls_templet=[$xls_templet] book=[$book] excel=[$excel]\n" ); $sheet = $book->Worksheets(1) || die printf( "$progid ts2csv $dttm ERR: sheet xlsfil=[$xlsfil] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG: sheet: xlsfil=[$xlsfil] excel=[$excel]\n" ); # pqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpqpq printf( "$progid ts2csv $dttm BEG: display\n" ); # @hrs=""; $hrs[0][0]=""; $daynum=0; $flipflop=1; # $excel->{Visible} = 0; # black and white: ActivePrinter => '\\NETPRINTSRV\p21 on Ne01:', # color: ActivePrinter => "ACROBAT:", # default printer: ActivePrinter => $pobj, printf( "$progid ts2csv $dttm BEG_800: excel=[$excel]\n" ); $period=$sheet->Range("$period_range")->{Value} || die printf( "$progid ts2csv $dttm ERR: period=[$period] excel=[$excel]\n" ); printf( "$progid ts2csv $dttm MSG: period=[$period] excel=[$excel]\n" ); # period_13-1_20091227-20090102 $period =~ s/-/_/g; # period_13_1_20091227_20090102 @period_flds=split(/_/,$period); $period_pp=$period_flds[1]; $period_p=$period_flds[2]; $begdt_text=$period_flds[3]; $enddt_text=$period_flds[4]; $fy=substr($enddt_text,0,4); $begdt=sprintf( "%04d-%02d-%02d", substr($begdt_text,0,4), substr($begdt_text,4,2), substr($begdt_text,6,2) ); $enddt=sprintf( "%04d-%02d-%02d", substr($enddt_text,0,4), substr($enddt_text,4,2), substr($enddt_text,6,2) ); # $keytext=sprintf( "%s,%s,%s,%s,%s,", $fy,$period_pp, $period_p, $begdt, $enddt ); $keytext=sprintf( "%s,%s,%s,", $fy,$period_pp, $period_p ); printf( "$progid ts2csv $dttm MSG: keytext=[$keytext]\n" ); $daynum=0; foreach $col (@week_cols_h1 ) { $day[$daynum]=$sheet->Range("$col$day_row:$col$day_row")->{Value}; $sheet->Range("$col$dt_row:$col$dt_row")->{NumberFormat} ="yyyy-mm-dd"; $dt[$daynum]=$sheet->Range("$col$dt_row:$col$dt_row")->{Value}; @dt_flds=split(/\//,$dt[$daynum] ); $effdt[$daynum]=sprintf( "%04d-%02d-%02d", $dt_flds[2],$dt_flds[0], $dt_flds[1] ); printf( "($col$day_row) $daynum day=$day[$daynum]\n" ); printf( "($col$dt_row) $daynum dt=$dt[$daynum] effdt=$effdt[$daynum]\n" ); foreach $row (@time_rows) { # $week_cols_h1 = qw!B F J N R V Z!; $range_h1=sprintf( "%s%s:%s%s", $week_cols_h1[$daynum],$row,$week_cols_h1[$daynum],$row ); $range_h2=sprintf( "%s%s:%s%s", $week_cols_h2[$daynum],$row,$week_cols_h2[$daynum],$row ); $range_tm=sprintf( "%s%s:%s%s", $week_cols_tm[$daynum],$row,$week_cols_tm[$daynum],$row ); $range_cd=sprintf( "%s%s:%s%s", $week_cols_cd[$daynum],$row,$week_cols_cd[$daynum],$row ); printf( "$progid ts2csv $dttm MSG: r=($range) r1=($range1) r2=($range2)\n" ); $hrs[$daynum][0]=$sheet->Range("$range_h1")->{Value}; $hrs[$daynum][1]=$sheet->Range("$range_h2")->{Value}; $hrs[$daynum][2]=$sheet->Range("$range_tm")->{Value}; $hrs[$daynum][3]=$sheet->Range("$range_cd")->{Value}; $rangetext=sprintf( "h1=(%s) h2=(%s) tm=(%s) cd=(%s)", $range_h1,$range_h2,$range_tm,$range_cd ); printf( "rangetext=[$rangetext]\n " ); $hrstext=sprintf( "%s,%s,%s,%s,%s,%s,", $effdt[$daynum], $day[$daynum], $hrs[$daynum][0], $hrs[$daynum][1], $hrs[$daynum][2], $hrs[$daynum][3] ); printf( "$keytext,$hrstext,$col:$row,desc\n " ); printf( XLSCSV "$keytext$hrstext,$col:$row,desc\n" ); # $sheet->Range("$range_h1")->{Value}=$row*100; # $sheet->Range("$range_h2")->{Value}=$row*100+150; # if( $flipflop == 1 ) { # $sheet->Range("$range_cd")->{Value}="c"; # } # else { # $sheet->Range("$range_cd")->{Value}="g"; # } # $flipflop=$flipflop*(-1); } $daynum++; printf( "\n" ); } close( XLSCSV ); printf( "$progid ts2csv $dttm END: display\n" ); # $range = $sheet->Range("A30:E30"); # $range->{Value} = [qw|hdr_a hdr_b hdr_c hdr_d hdr_e|]; # $range->Font->{Bold} = 1; # $range = $sheet->Range("A31:E31"); # $range->{Value} = [qw|110 120 130 140 150|]; # $range = $sheet->Range("B12:C12")->{Value} = [qw!0000 150!]; # $range = $sheet->Range("E12:E12")->{Value} = [qw!c!]; # # $range = $sheet->Range("F12:G12")->{Value} = [qw!50 250!]; # $range = $sheet->Range("I12:I12")->{Value} = [qw!c!]; # # $range = $sheet->Range("F21:G21")->{Value} = [qw!2250 2350!]; # $range = $sheet->Range("I21:I21")->{Value} = [qw!g!]; ## if( -f $xlsfil ) { ## if( -f $xlsbak ) { ## printf( "$progid ts2csv $dttm ERR: FOUND xlsbak=[$xlsbak])\n"); ## return; ## } ## printf( "$progid ts2csv $dttm MSG: copy($xlsfil, $xlsbak)\n"); ## copy( $xlsfil, $xlsbak ); ## if( -f $xlsbak ) { ## printf( "$progid ts2csv $dttm MSG: unlink($xlsfil)\n"); ## unlink( $xlsfil ); ## } ## } ## printf( "$progid ts2csv $dttm BEG: SAVEAS: xlsfil=[$xlsfil] excel=[$excel]\n" ); $book->SaveAs ("$xlstmp") || die printf( "$progid ts2csv $dttm ERR: SAVEAS xlstmp=[$xlstmp] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG: SAVEAS: xlstmp=[$xlstmp] excel=[$excel]\n" ); # xls2pdf $pdffil=$xlsfil; $pdffil=~s/\.xls$/.pdf/; printf( "$progid ts2csv $dttm MSG: PDF: pdffil=[$pdffil] excel=[$excel]\n" ); # File->Print->{'Name:'}="Adobe PDF"; # File->Print->{Save PDF File As'} = "$pdffil"; undef $book || printf( "$progid ts2csv $dttm ERR: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG: UNDEF_BOOK: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); undef $excel || printf( "$progid ts2csv $dttm ERR: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n"); printf( "$progid ts2csv $dttm MSG: UNDEF_EXEL: xlsfil=[$xlsfil] book=[$book] excel=[$excel]\n" ); printf( "$progid ts2csv $dttm MSG: copy($xlsfil,$xlsbak)\n" ); copy( $xlsfil, $xlsbak ); if( -f $xlsbak ) { printf( "$progid ts2csv $dttm MSG: unlink($xlsfil)\n" ); unlink( $xlsfil ); printf( "$progid ts2csv $dttm MSG: copy($xlstmp,$xlsfil)\n" ); copy( $xlstmp, $xlsfil ); if( -f $xlsfil ) { printf( "$progid ts2csv $dttm MSG: unlink($xlstmp)\n" ); unlink( $xlstmp ); } } printf( "$progid ts2csv $dttm END: xlsfil=[$xlsfil]\n" ); # printf( "$progid ts2csv $dttm END: xlstmp=[$xlstmp]\n" ); # printf( "$progid ts2csv $dttm END: xlsbak=[$xlsbak]\n" ); return; # get already active Excel application or open new printf( "$progid test $dttm BEG1: o1=[$option1] o2=[$option2] o3=[$option3]\n"); my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); # open Excel file printf( "$progid ts2csv $dttm MSG: xlsfil=[$xlsfil]\n" ); # my $Book = $Excel->Workbooks->Open("j:/ko/bin/ts1.xls"); # my $Book = $Excel->Workbooks->Open("$xlsfil"); my $Book = $Excel->Workbooks->Open("j:/ko/bin/ts1.xls"); printf( "$progid test $dttm BEG2: o1=[$option1] o2=[$option2] o3=[$option3]\n"); } sub ol2csv_test( $option1, $option2, $option3, $option4 ) { use Cwd; # use Win32::OLE qw(in with); # use Win32::OLE::Const 'Microsoft Excel'; # use Win32::OLE::Variant; $xlfil="ko_center.xls"; if( $option1 != "" ) { $xlfil="$option1"; } printf( "$progid MSG_001: xlfil=[$xlfil] BEG:\n" ); return; } sub ol2csv( $option1, $option2, $option3 ) { use Cwd; # use Win32::OLE qw(in with); # use Win32::OLE::Const 'Microsoft Outlook'; # use Win32::OLE::Variant; $csvfil="outlook.csv"; if( $option1 != "" ) { $csvfil="$option1"; } printf( "$progid MSG_001: csvfil=[$csvfil] BEG:\n" ); return; # 01.#move all 2010 messages from inbox to personal folders "Old Inbox 2010" # 02. # 03.require 'win32ole' # 04.outlook = WIN32OLE.new('Outlook.Application') # 05.mapi = outlook.GetNameSpace('MAPI') # 06.#inbox # 07.inbox = mapi.GetDefaultFolder(6) # 08. # 09.#personal folders # 10.personal_folders = mapi.Folders.Item('Personal_folder') # 11. # 12.#subfolder old inbox 2010 under personal folders # 13.old_inbox = personal_folders.Folders.Item('Old Inbox 2011') # 14. # 15.#all items that are older than 1.1.2011 move to archive # 16.inbox.Items.Count.downto(1) do |i| # 17.message = inbox.Items(i) printf( "$progid $dttm dt2kxls MSG_1930: copy $tsnew_xslfil $ts_xslfil\n" ); # 18.if message.CreationTime < "2011/01/01 00:00:00" # 19.message.Move(old_inbox) # 20.end my $ol = Win32::OLE->GetActiveObject('Outlook.Application') || Win32::OLE->new('Outlook.Application', 'Quit'); my $mapi = $ol->GetNameSpace("MAPI"); my $inbox = $mapi->GetDefaultFolder(olFolderInbox); my $dir = cwd . "\\"; $dir =~ s/\//\\/g; my $atch1, $file1, $atch2, $file2; printf( "$progid MSG_002: inbox=[$inbox] dir=[$dir]\n"); # Win32::OLE ->GetObject('Outlook.Application') # Win32::OLE ->BuiltinDocumentProperties(wdPropertyComments) # Win32::OLE ->Value; $inbox_items=ref($inbox->{Items}); printf( "$progid MSG_057: inbox_items=[$inbox_items] \n"); my $itemcount = $inbox->Items->Count; printf( "$progid MSG_060: itemcount=[$itemcount]\n" ); $j=0; $jlim=10; $k=0; $klim=10; $i=0; $ilim=20; ## printf( "$progid MSG_003: $i $itemcount recip_count=[$recip_count] to=[$to] from=[$from] received=[$received] lines=[$lines] crtime=[$crtime]\n" ); $hdrrec="i,crtime,to,recip_name,recip_addr,subject,from,received,sender,"; printf( "$hdrrec\n" ); open( CSVFIL, ">>$csvfil" ) || die "$progid ERR_30: csvfil=[$csvfil]\n"; printf( CSVFIL "$hdrrec\n" ); $j=0; $r=1; # foreach my $msg (in $Folder->{Items}){ foreach my $msg (in $inbox->{Items}){ $j++; $received=$msg->{Received}; $from=$msg->{From}; $sender=$msg->{sender}; $lines=$msg->{Lines}; $crtime=$msg->{CreationTime}; $subject=$msg->{Subject}; my $to = $msg->{'To'}; my $crtime=$msg->{CreationTime}; my $subject = $msg->{'Subject'}; my $from = $msg->{'From'}; my $received = $msg->{'Received'}; my $sender=$msg->{'Sender'}; if( defined $msg->Recipients->Count ) { $recip_count= $msg->Recipients->Count; $recip_addr="recip_addr"; $recip_name="recip_name"; for my $r (1..$nrecip) { my $recip_addr=$msg->Recipients($r)->Address; my $recip_name=$msg->Recipients($r)->Name; } } $csvrec="$i,$crtime,$to,$recip_name,$recip_addr,$subject,$from,$received,$sender,"; printf( "$csvrec\n" ); printf( CSVFIL "$csvrec\n" ); # print $msg->{CreationTime} . "\n"; # printf( "$progid MSG_030 j=$j msg=[$msg] crtime=[$crtime] recieved=[$recieved]\n"); printf( "$csvrec\n" ); printf( CSVFIL "$csvrec\n" ); $flag_atch="0"; if( $flag_atch == "1" ) { foreach my $atch (in $msg->{Attachments}){ $k++; # printf( "$progid MSG_040: k=$k atch=$atch\n"); if($atch->{FileName} =~ m/.xls$/i){ if($atch->{FileName} =~ /Name of attachment1/i){ $atch1 = $atch; $file1 = $dir . "file1.xls"; } if($atch->{FileName} =~ /Name of attachment2/i){ $atch2 = $atch; $file2 = $dir . "file2.xls"; } } if( $k > $klim ) { last; } } } ## if( $j > $jlim ) { last; } } # if($atch1 && $atch2) { # printf( "$progid MSG_050: file1=[$file1] file3=[$file2]\n" ); # print $file1 . "\n" . $file2 . "\n"; # $atch1->SaveAsFile($file1); # $atch2->SaveAsFile($file2); # } close( CSVFIL ); } # ========================================================================================= sub test2( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; printf( "$progid test2 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); # use Win32::OLE qw(in with); # use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # die on errors... $xlsfil="ts1.xls"; if( "$option1" ne "" ) { $xlsfil=$option1; } if( -f $xlsfil ) { printf( "$progid test2 $dttm MSG: FOUND xlsfil=[$xlsfil]\n" ); } else { printf( "$progid test2 $dttm ERR: NOTFOUND xlsfil=[$xlsfil]\n" ); } # get already active Excel application or open new printf( "$progid test2 $dttm BEG1: o1=[$option1] o2=[$option2] o3=[$option3]\n"); my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); # open Excel file printf( "$progid test2 $dttm MSG: xlsfil=[$xlsfil]\n" ); # my $Book = $Excel->Workbooks->Open("j:/ko/bin/ts1.xls"); # my $Book = $Excel->Workbooks->Open("$xlsfil"); my $Book = $Excel->Workbooks->Open("j:/ko/bin/ts1.xls"); printf( "$progid test2 $dttm BEG2: o1=[$option1] o2=[$option2] o3=[$option3]\n"); my $Sheet = $Book->Worksheets(1); printf( "$progid test2 $dttm BEG3: o1=[$option1] o2=[$option2] o3=[$option3]\n"); printf( "$progid test2 $dttm BEGLOOP: xlsfil=[$xlsfil]\n" ); foreach my $row (7..20) { printf( "$progid test2 $dttm BEGLOOP_ROW: xlsfil=[$xlsfil] row=$row\n" ); foreach my $col (1..5) { printf( "$progid test2 $dttm BEGLOOP_COL: xlsfil=[$xlsfil] col=$col\n" ); next unless defined $Sheet->Cells($row,$col)->{'Value'}; # print out the contents of a cell printf "$progid test2 $dttm LOOP: ($row, $col) value=[%s] formula=[%s]\n", $Sheet->Cells($row,$col)->{'Value'}, $Sheet->Cells($row,$col)->{'Formula'}; # $sheet->Cells($row, $col)->{'Value'} = 1; printf( "$progid test2 $dttm LOOP_MSG: xlsfil=[$xlsfil] col=$col\n" ); } } $row=30; $col=1; printf( "$progid test2 $dttm LOOP_UPDATE: ($row,$col) v=[$value]\n" ); foreach my $row (30..32) { printf( "$progid test2 $dttm LOOP_UPDROW: ($row,$col) v=[$value]\n" ); foreach my $col(1..2) { printf( "$progid test2 $dttm LOOP_UPDCOL: ($row,$col) v=[$value]\n" ); $val1=sprintf( "_(%02d,%02d)_", $row, $col ); printf( "$progid test2 $dttm LOOP_UPD1: ($row,$col) v=[$val1]\n" ); $Sheet->Cells($row, $col)->{'Value'} = $val1; printf( "$progid test2 $dttm LOOP_UPD2: ($row,$col) v=[$val1]\n" ); } } $Book->Close; } sub test3( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; # printf( "$progid test3 $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n" ); # use Win32::OLE; $xlsout="ts1.xls"; if( $option1 ne "" ) { $xlsout=$option1; } $root_xlsout="J:/ko/bin"; $pathfile_xlsout=sprintf( "%s/%s",$root_xlsout,$xlsout ); $pathfile_xlsout_win=$pathfile_xlsout; $pathfile_xlsout_win =~ s!/!\\\\!g; printf( "$progid test3 $dttm MSG: pathfile_xlsout=[$pathfile_xlsout]\n" ); printf( "$progid test3 $dttm MSG: pathfile_xlsout_win=[$pathfile_xlsout_win]\n" ); eval{ $ex = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit')}; # use existing instance if Excel is already running # eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; # die "$progid test3 $dttm ERR: Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "$pogid test3 $dttm ERR: Excel not started"; } ## eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; ## die "$progid test3 $dttm ERR: Excel not installed" if $@; ## printf( "$progid test3 $dttm EX_XLS: \$\@=[$@]\n" ); ## if (defined $ex) { ## $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) ## or die "$pogid test3 $dttm ERR: Excel not started"; ## printf( "$progid test3 $dttm NEWXLS: \$\_[0]->Quit=[$_[0]->Quit\n" ); ## } ## else { ## printf( "$progid test3 $dttm NEWXLS: UNDEFINED \$\_[0]->Quit=[".$_[0]."->Quit\n" ); ## } # get a new workbook printf( "$progid test3 $dttm MSG: ex=[$ex]\n" ); my $book = $ex->Workbooks->Open($xlsout); # $book = $ex->Workbooks->Add(); $sheet = $book->Worksheets(1); # my $sheet = $Book->Worksheets("Sheet1"); $sheet->Activate(); # write a 2 rows by 3 columns range $y = 2; $x = 1; $sheet->Range("A$x:J$y")->{Value} = [ ['Date','Total (IN)','Suc +c (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ +(OUT)','MO(IN)','MO(OUT)'], [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]]; $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'], [$max_pk_msg,$pk_msg]]; foreach(@parameters) { $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}]; $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}]; } # print "XyzzyPerl" $array = $sheet->Range("A2:I1")->{Value}; for (@$array) { for (@$_) { print defined($_) ? "$_|" : "<undef>|"; } print "\n"; } # save and exit # $book->SaveAs ("J:\\ko\\bin\\$xlsout.xls") ; printf( "$progid test3 $dttm SAVEAS: pathfile_xlsout_win=[$pathfile_xlsout_win]\n" ); $book->SaveAs ("$pathfile_xlsout_win") ; printf( "$progid test3 $dttm UNDEF_BOOK: pathfile_xlsout_win=[$pathfile_xlsout_win]\n" ); undef $book; printf( "$progid test3 $dttm UNDEF_EX: pathfile_xlsout_win=[$pathfile_xlsout_win]\n" ); undef $ex; printf( "$progid test3 $dttm END: pathfile_xlsout_win=[$pathfile_xlsout_win]\n" ); } sub go( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; # printf( "$progid go $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n" ); } # # sub ungrep( $option1, $option2, $option3 ) { $option1=shift; $option2=shift; $option3=shift; # printf( "$progid go $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n" ); # ifile with filenames prefix to each line # file-1: file1_text_line-1 # file-1: file1_text_line-n # ... # file-n: file1_text_line-2 # file-n: file1_text_line-2 # # file-1 # file1_test_line-1 # file1_text_line-n # ... # file-2 # file2_test_line-1 # file2_text_line-n $ifil=$option1; $sqlpath="sql"; if( ! -f $ifil ) { printf( "$progid ungrep $dttm ERR_1320: NOTFOUND ifil=[$ifil] sqlpath=[$sqlpath]\n" ); return; } $icnt=0; $dtag=&dttm; ## printf( "$progid ungrep $dttm BEG_1330: $wlcnt ts2csv_wlfile=[$ts2csv_wlfile]\n" ); open( IFIL, "<$ifil" ); while( <IFIL> ) { $icnt++; $lastfile=""; $irec=$_; chomp($irec); ( $ofil, $sqltxt ) = split( /\:/, $irec ); $sqltxt .= "\n"; ## printf( "\n\n$progid ungrep $dttm MSG_1340: ungrep irec=[$irec] ofil=[$ofil] ===\n" ); $pathfile = "$sqlpath/$ofil"; $pathsave = "$sqlpath/$ofil"; $pathsave =~ s/.sql$/_$dtag.sql/; ## printf( "\n\n$progid ungrep $dttm MSG_1340: ungrep pathfile=[$pathfile] sqltxt=[$sqltxt]\n" ); if( -f $pathfile ) { if( $pathfile ne $lastpath ) { $cmd_mv=sprintf( "mv $pathfile $pathsave" ); system( $cmd_mv ); printf( "\n\n$progid ungrep $dttm OPEN_1341: ungrep $icnt cmd_mv=[$cmd_mv] [$pathfile][$lastpath]\n" ); printf( "\n\n$progid ungrep $dttm OPEN_1341: ungrep $icnt open pathfile= [$pathfile][$lastpath]\n" ); if( $icnt > 1 ) { close( OFIL ); } open( OFIL, ">$pathfile" ); print( OFIL $sqltxt ); } else { # printf( "\n\n$progid ungrep $dttm WRITE_1342: ungrep $icnt pathfile=[$pathfile] last=[$lastpath] sqltxt=[$sqltxt]\n" ); print( OFIL $sqltxt ); } } else { if( $pathfile ne $lastpath ) { printf( "\n\n$progid ungrep $dttm OPEN_1351: ungrep $icnt open pathfile=[$pathfile] lastpath=[$lastpath]\n" ); if( $icnt > 1 ) { close( OFIL ); } open( OFIL, ">$pathfile" ); } else { ## printf( "\n\n$progid ungrep $dttm WRITE_1352: ungrep $icnt [$pathfile][$lastpath] sqltxt=[$sqltxt]\n" ); print( OFIL $sqltxt ); } } $lastpath=$pathfile; } close( IFIL ); printf( "$progid ungrep $dttm END_1350: $ifil ungrep=[$ungrep]\n" ); } sub ts2csv_runwl( $ts2csv_wlfile ) { $ts2csv_wlfile=shift; $wlcnt=0; ## printf( "$progid ts2csv_runwl $dttm BEG_1330: $wlcnt ts2csv_wlfile=[$ts2csv_wlfile]\n" ); open( WLFILE, "<$ts2csv_wlfile" ); while( <WLFILE> ) { $wlcnt++; $wlrec=$_; chomp($wlrec); $xlsfil=$wlrec; printf( "\n\n$progid ts2csv_runwl $dttm MSG_1340: $wlcnt wlrec=[$wlrec] ====================\n" ); &ts2csv_runjob( $xlsfil, $option2, $option3 ); } close( WLFILE ); printf( "$progid ts2csv_runwl $dttm END_1350: $wlcnt ts2csv_wlfile=[$ts2csv_wlfile]\n" ); } sub xlsx2csv( $xlsfil ) { $xlsfil=shift; printf( "$progid $dttm xlsx2csv MSG_4200: xlsfil=[$xlsfil]\n" ); ## use Text::Iconv; ## my $converter = Text::Iconv -> new ("utf-8", "windows-1251"); ### Text::Iconv is not really required. ### This can be any object with the convert method. Or nothing. # disable use Spreadsheet::XLSX; $dtag=&dttm; $jobid=$xlsxfil; $jobid =~ s/\.xlsx//; $jobid=$xlsfil; $jobid =~ s/\.xlsx//; ## $xlsxfil="s:\\xampp\\htdocs\\ko_saic\\krhw.xlsx"; ## my $excel = Spreadsheet::XLSX->new('krhw.xlsx', $converter); my $excel = Spreadsheet::XLSX->new( $xlsfil, $converter); foreach my $sheet (@{$excel -> {Worksheet}}) { $sheet_name=$sheet->{Name}; ## printf("Sheet: %s\n", $sheet_name ); $sheet->{MaxRow} ||= $sheet->{MinRow}; $sheet_name =~ tr/A-Z/a-z/; $sheet_name =~ s/ /_/g; $sheet_name =~ s/\,/;/g; $sheet_name =~ s/\|/:/g; $csvfil= sprintf( "%s-%s-%s.csv", $jobid, $sheet_name, $dtag ); printf( "$progid $dttm MSG_4230: csvfil=[$csvfil]\n" ); open( CSVFIL, ">$csvfil" ); foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { $sheet->{MaxCol} ||= $sheet->{MinCol}; $coltext=""; foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) { my $cell = $sheet->{Cells}[$row][$col]; if ($cell) { $cell_value=$cell->{Val}; $cell_value =~ tr/A-Z/a-z/; ## $cell_value =~ s/,/;/g; ## $cell_value =~ s/|/ or /g; $cell_value =~ s/ /_/g; $coltext .= "$cell_value,"; ## printf("(%s,%s)=>%s\n",$row,$col,$cell->{Val}); } } $rowtext = sprintf( "%s,%s,%s,%s,%s,%s,%s\n", $myid,$mydf,$jobid,$sheet_name,$row,$coltext,$xlsfil, ); printf( $rowtext ); print( CSVFIL $rowtext ); } close( CSVFIL ); } } sub xls2csv_runjob( $xlsfil ) { $xlsfil=shift; # use strict; # disabled use Spreadsheet::ParseExcel; @sheets; printf( "$progid xls2csv_runjob $dttm MSG_1350: xlsfil=[$xlsfil]\n" ); return; if( ! -f $xlsfil ) { printf( "$progid xls2csv_runjob $dttm ERR: NOTFOUND xlsfil=[$xlsfil]\n" ); return; } my $xls = new Spreadsheet::ParseExcel; die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV; my $book = $xls->Parse($xlsfil); my( $sheet, $cell ); print "file :", $book->{File} , "\n"; print "count :", $book->{SheetCount} , "\n"; print "author:", $book->{Author} , "\n" if defined $book->{Author}; print "$progid xls2csv $dttm SHEET:", $sheet->{Name}, "\n"; $maxsheet=$book->{SheetCount}; $maxsheet=1; $s=0; $smin=0;$smax=0; $slim=1; $r=0; $rmin=0;$rmax=0; $rlim=0; $c=0; $cmin=0;$cmax=0; $clim=0; $slim=$book->{SheetCount}; $smax=$book->{SheetCount}; $sheet = $book->{Worksheet}[$s]; print "$progid xls2csv_runjob $dttm MSG: $csvfil", $sheet->{Name}, "\n"; $text=sprintf( "sheets=(0,%s)\n",$book->{SheetCount} ); print "$progid xls2csv_runjob $dttm SHEETS: $text\n"; $text=sprintf( "rows=(%s,%s)\n",$sheet->{MinRow},$sheet->{MaxRow} ); print "$progid xls2csv_runjob $dttm ROWS: $text\n"; $text=sprintf( "cols=(%s,%s)\n",$sheet->{MinCol},$sheet->{MaxCol} ); print "$progid xls2csv_runjob $dttm COLS: $text\n"; # build sheets array for($s=0 ; defined $book->{SheetCount} && $s <= $sheet->{SheetCount} ; $s++) { if( $s > $slim ) { last; } $rmin=$sheet->{MinRow}; $rmax=$sheet->{MaxRow}; $cmin=$sheet->{MinCol}; $cmax=$sheet->{MaxCol}; for($r = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $r <= $sheet->{MaxRow} ; $r++) { if( $r>$rlim ) { $rlim=$r; } for(my $c = $sheet->{MinCol} ; defined $sheet->{MaxCol} && $c <= $sheet->{MaxCol} ; $c++) { if( $c>$clim ) { $clim=$c; } $cell = $sheet->{Cells}[$r][$c]; $text=""; if(($cell) && ( length($cell->Value) > 0 )) { # $celltext=sprintf( "(%2d,%2d)_%s",$r,$c,$cell->Value ); $celltext=sprintf( "%s",$cell->Value ); if( $celltext =~ /period/ ) { printf( "$progid xls2csv $dttm FOUND: ($r,$c)=[$celltext]\n" ); $period_text=$celltext; $period_text =~ s/-/_/g; @period_flds=split( /_/, $period_text ); $period_p=$period_flds[1]; $period_pp=$period_flds[2]; $period_begdt=sprintf( "%4d-%02d-%02d", substr($period_flds[3],0,4), substr($period_flds[3],4,2), substr($period_flds[3],6,4) ); $period_enddt=sprintf( "%4d-%02d-%02d", substr($period_flds[4],0,4), substr($period_flds[4],4,2), substr($period_flds[4],6,4) ); $period_fy=substr($period_enddt,0,4); printf( "%s,%02d,%s,%s,%s", $period_fy, $period_p, $period_pp, $period_begdt, $period_enddt ); # return; } $sheets[$s][$r][$c]=$celltext; # if( '$cell->Value' eq "0.0" ) { next; } # if( "$cell->Value" eq "0.0" ) { next; } # if( $cell->Value eq "0.0" ) { next; } $text=sprintf( "(%d,%02d,%02d)[%s]",$s,$r,$c,$cell->Value); $text=sprintf( "%s",$cell->Value); # printf( "$text," ); } } } } # parse sheets array printf( "$progid test3 $dttm MSG: slim=[$slim] rlim=[$rlim] clim=[$clim]\n" ); for( $s=0;$s<$slim;$s++ ) { for( $r=0;$r<$rlim;$r++ ) { for( $c=0;$c<$clim;$c++ ) { printf( "$sheets[$s][$r][$c]|" ); } printf( "\n" ); } } printf( "BUGS_190 ================================================\n" ); # cells_day $s=0; $r=7; $rbeg=7;$rend=7; $rdif=1; $cbeg=1; $cend=25; $cdif=4; for($c=$cbeg; $c<=$cend; $c=$c+$cdif ) { $r=7; $cell_day=$sheets[$s][$r][$c]; printf( "CELL_DAY: ($r,$c)=$cell_day\n" ); # cells_dt $r=8; $cell_dt=$sheets[$s][$r][$c]; @cell_dt_flds=split(/\//,$cell_dt ); $cell_dt=sprintf( "%4d-%02d-%02d", $cell_dt_flds[2], $cell_dt_flds[1], $cell_dt_flds[0] ); printf( "CELL_DT: ($r,$c) $cell_dt\n" ); # cells_tmrec $rbeg=10;$rend=20; $rdif=1; for($r=$rbeg; $r<=$rend; $r=$r+$rdif ) { $cell_h1=$sheets[$s][$r][$c]; $cell_h2=$sheets[$s][$r][$c+1]; $cell_tm=$sheets[$s][$r][$c+2]; $cell_code=$sheets[$s][$r][$c+3]; $tmrec=sprintf( "%s,%s,%s,%s,%s,,%s,%s,%s,%s,%s,%s,", $period_fy,$period_p,$period_pp, $period_begdt, $period_enddt, $cell_dt,$cell_day, $cell_h1,$cell_h2,$cell_tm,$cell_code ); if( $tmrec eq ",,,,,," ) { next; } if( $tmrec =~ m/0.0/ ) { next; } printf( "DATAREC: [$r,$c][$tmrec]\n" ); } } # cells_day # $rbeg=10;$rend=20; $cbeg=1; $cend=25; $cdif=4; # for($c=$cbeg; $c<=$cend; $c=$c+$cdif ) { $cell_day=$sheet[$s][$r][$c]} for($r=1; $r<=5; $r++ ) { for($c=1; $c<=5; $c++ ) { if( $sheet[$s][$r][$c] eq "" ) { next; } if( $sheet[$s][$r][$c] =~ /0.0/ ) { next; } if( $sheet[$s][$r][$c] =~ m/\,\,\,0\.0\,\,/ ) { next; } printf( "($r,$c) [$sheet[$s][$r][$c]]\n" ); } } ## |( 7, 1)_Sat||||( 7, 5)_Sun||||( 7, 9)_Mon||||( 7,13)_Tue||||( 7,17)_Wed||||( 7,21)_Thu||||( 7,25)_Fri||| ## |( 8, 1)_12/27/2008||||( 8, 5)_12/28/2008||||( 8, 9)_12/29/2008||||( 8,13)_12/30/2008||||(8,17)_12/31/2008||||( 8,21)_1/1/2009||||( 8,25)_1/2/2009||| ## |( 9, 1)_in|( 9, 2)_out|||( 9, 5)_in|( 9, 6)_out|||( 9, 9)_in|( 9,10)_out|||( 9,13)_in|( 9,14)_out|||( 9,17)_in|( 9,18)_out|||( 9,21)_in|( 9,22)_out|||( 9,25)_in|( 9,26)_out|| ## |||||||||||||||||||||||||||| # return; for(my $s=0; $s < $maxsheet ; $s++) { $sheet = $book->{Worksheet}[$s]; $minrow=$sheet->{MinRow}; $maxrow=$sheet->{MaxRow}; $mincol=$sheet->{MinCol}; $maxcol=$sheet->{MaxCol}; print "$progid xls2csv $dttm ROW: row=($minrow,$maxrow)\n"; print "$progid xls2csv $dttm COL: col=($mincol,$maxcol)\n"; for($r=$minrow; $r<=$maxrow; $r++) { printf( "BUGS ROW r=$r $minrow,$maxrow\n" ); for($c=$mincol; $c<=$maxcol; $c++) { printf( "BUGS COL r=$r c=$c $mincol,$maxcol\n" ); $cell = $sheet->{Cells}[$r][$c]; $status=$celltext=$cell->Value; printf( "BUGS status=[$status] [$cell->Value]\n" ); $text=sprintf( "(%d,%02d,%02d)[%s]", $s,$r,$c,$celltext); # if(($cell) && ( length($celltext) > 0 )) { # if( "$cell->Value" eq "0.0" ) { $text=""; } # if( "$cell->Value" eq "0.0" ) { $text=""; } # if( "$cell->Value" eq "0.0" ) { $text=""; } # } printf( "$celltext" ); } printf( "ENDROW\n" ); } } } sub ShowObjs { my $obj = shift; foreach (sort keys %$obj) { print "Keys: $_ - $obj->{$_}\n"; } } # word beg pqpqpqpq pqpqpqpq pqpqpqpq pqpqpqpq pqpqpqpq pqpqpqpq # perl ole word sub db2doc( $option1, $option2, $option3 ) { $docfil=shift; $option2=shift; $option3=shift; # printf( "$progid db2doc $dttm MSG: o1=[$option1] o2=[$option2] o3=[$option3]\n"); # use warnings; # use strict; # use Win32::OLE; # use Win32::OLE::Const 'Microsoft.Word'; # wd constants # use Win32::OLE::Const 'Microsoft Office'; # mso constants $jpgfil="j:/ko/bin/ko.jpg"; @doc_suffix_list = "\.doc"; ($basename, $dirpath, $suffix) = fileparse( $docfil, @doc_suffix_list); # $docfil=$pathfile_docfil; if( "$dirpath" eq "\.\\" ) { $docfil=sprintf("%s/%s", $cwd, $docfil ); } printf( "$progid docfil $dttm MSG: docfil=[$docfil]\n" ); printf( "$progid db2doc $dttm MSG: basename=[$basename]\n" ); printf( "$progid db2doc $dttm MSG: dirpath=[$dirpath] suffix=[$suffix]\n" ); printf( "$progid db2doc $dttm MSG: docfil=[$docfil]\n" ); $docbak = $docfil; $docbak =~ s/\.doc/_$dttm.doc/; $doctmp = $docfil; $doctmp =~ s/\.doc/_tmp.doc/; $doccsv = $docfil; $doccsv =~ s/\.doc/.csv/; $docpdf = $docfil; $docpdf =~ s/\.doc/.pdf/; if( -f $docfil ) { printf( "$progid docfil $dttm ERR: FOUND copy($docfil,$docbak)\n" ); copy( $docfil, $docbak ); ## return; } $pngfil=$jpgfil; $pngfil =~ s/\.jpg/\.png/; $giffil=$jpgfil; $giffil =~ s/\.jpg/\.gif/; printf( "$progid db2doc $dttm MSG: jpgfil=[$jpgfil] l=$left t=$top w=$width h=$height\n" ); printf( "$progid db2doc $dttm MSG: pngfil=[$giffil] mspFalse,msoTrue\n" ); my $cur_style = 'a'; my $cur_bookmark = 'a'; my $word = CreateObject Win32::OLE 'Word.Application' or die $!; $word->{'Visible'} = 1; my $document = $word->Documents->Add || printf( "$progid db2doc $dttm ERR: word=[$word] document=[$document]\n" ); printf( "$progid db2doc $dttm MSG: word=[$word] document=[$document]\n" ); my $selection = $word->Selection; if( ! -f $pngfil ) { printf( "$progid db2doc $dttm ERR: NOTFOUND pngfil[$pngfil]\n" ); } $left=-75; $top=-400; $width=50; $height=25; $document->Shapes->AddPicture ( $pngfil, False, # link to file True, # save with document $left, $top, $width, $height, $document->ActiveWindow->Selection->{Range} ); $selection -> TypeText("beg png\n"); $selection -> TypeParagraph; $selection -> TypeText("end png\n"); $selection -> TypeParagraph; $selection -> TypeText("ko_hrs.pl db2doc $dttm"); $selection -> TypeParagraph; $selection -> TypeText("beg png\n"); $selection -> TypeParagraph; $selection -> TypeText("Heading 1"); $selection -> {'Style'} = "Heading 1"; $selection -> TypeParagraph; my $heading_1 = $document->Styles("Heading 1"); my $heading_1_font = $heading_1 -> Font; $heading_1_font -> {Name} = "Arial"; $heading_1_font -> {Size} = 11; $heading_1_font -> {Bold} = 1; $selection -> TypeText("Heading 2"); $selection -> {'Style'} = "Heading 2"; $selection -> TypeParagraph; my $heading_2 = $document->Styles("Heading 2"); my $heading_2_font = $heading_2 -> Font; $heading_2_font -> {Name} = "Arial"; $heading_2_font -> {Size} = 8; $heading_2_font -> {Bold} = 0; $selection -> TypeText("Heading 1 test1\m"); $selection -> {'Style'} = "Heading 1"; $selection -> TypeParagraph; $selection -> TypeText("Heading 2 test2\n"); $selection -> {'Style'} = "Heading 2"; $selection -> TypeParagraph; $selection -> TypeText("Heading 1 test3\n"); $selection -> {'Style'} = "Heading 1"; $selection -> TypeParagraph; # my $picture = insert_picture($document, '$jpgfil', 100, 100, 100, 30); # bugs # my $picture = if( ! -f $pngfil ) { printf( "$progid db2doc $dttm ERR: NOTFOUND pngfil[$pngfil]\n" ); } $left=10; $top=10; $width=25; $height=12; $document->Shapes->AddPicture ( $pngfil, False, # link to file True, # save with document $left, $top, $width, $height, $document->ActiveWindow->Selection->{Range} ); $left=400; $top=400; $width=50; $height=25; $document->Shapes->AddPicture ( $pngfil, False, # link to file True, # save with document $left, $top, $width, $height, $document->ActiveWindow->Selection->{Range} ); # my $image_2 = $document->InlineShapes->AddPicture('$giffil', False, True); $selection -> {'Style'} = "Heading 1"; $selection -> TypeParagraph; my $style_body1 = $document->Styles("Heading 1"); my $style_body1_font = $style_body1 -> Font; # $style_body1_font -> {Name} = "Arial"; $style_body1_font -> {Name} = "Courier New"; $style_body1_font -> {Size} = 8; $style_body1_font -> {Bold} = 0; for($line=1; $line<10; $line++ ) { $selection -> TypeText("--- $line $dttm style_body1 ---\n"); } # Change Page Margins # with (my $test = $File->PageSetup, # TopMargin => 40, # BottomMargin => 40, # LeftMargin => 40, # RightMargin => 40); # Print File $File->PrintOut(); # or # $Word->ActiveDocument->PrintOut({ # Background => 0, # Range => wdPrintAllDocument, # Item => wdPrintDocumentContent, # PageType => wdPrintAllPages}) # sleep 5; # sleep to let Word print before closing # printf( "showobjs word=[$word]\n" ); # ShowObjs( $word ); # printf( "showobjs document=[$document]\n" ); # ShowObjs( $document ); # sub save_doc_as {my $document = shift; my $filename = shift; $document->SaveAs($filename);} # sub close_doc {my $document = shift; $document -> Close;} printf( "$progid db2doc $dttm BEG: document->SaveAs\ndocfil=[$docfil]\nword=[$word]\ndocument=[$document]\n" ); $document->SaveAs( $docfil ) || printf( "$progid db2doc $dttm ERR: SaveAs\n docfil=[$docfil]\n word=[$word] document=[$document]\n" ); printf( "$progid db2doc $dttm MSG: SaveAs\n docfil=[$docfil]\n word=[$word] document=[$document]\n" ); $document->Close || printf( "$progid db2doc $dttm ERR: Close\n word=[$word] document=[$document]\n" ); printf( "$progid db2doc $dttm MSG: Close\n word=[$word] document=[$document]\n" ); # my $document = $word->Documents->Close # || printf( "$progid db2doc $dttm ERR: word->Documents->Close word=[$word] document=[$document]\n" ); # printf( "$progid db2doc $dttm MSG: word->Documents->Close word=[$word] document=[$document]\n" ); # uncomment the following two if word should shut down $word->Quit || printf( "$progid db2doc $dttm ERR: word->Quit\n word=[$word] document=[$document]\n" ); printf( "$progid db2doc $dttm MSG: word->Quit\n word=[$word] document=[$document]\n" ); undef $word || printf( "$progid db2doc $dttm ERR: UNDEF_BOOK: \ndocfil=[$docfil]\ndocument=[$document]\nword=[$word]\n"); printf( "$progid db2doc $dttm MSG: UNDEF_BOOK: \ndocfil=[$docfil]\ndocument=[$document]\nword=[$word]\n" ); return; # selection is the insertion point. my $selection = $word->Selection; my $st_bold_10_arial = create_style($document, "Arial", 10, 1, 0); my $st_italic_10_arial = create_style($document, "Arial", 10, 0, 1); my $st_italic_20_arial = create_style($document, "Times new Roman", 20, 0, 1); set_style($document, $st_bold_10_arial); text ($document, "This text is bold, 10 points and arial"); enter ($document); set_style($document, $st_italic_10_arial); text ($document, "This text is italic, 10 points and arial"); enter ($document); set_style($document, $st_italic_20_arial); text ($document, "This text is italic, 20 points and Times"); # insert something into the header switch_view($document, wdSeekCurrentPageHeader); my $st_header = create_style($document, "Verdana", 8, 0, 0); set_style($document, $st_header); text ($document, "this is the document's header, it will be repeated on all pages"); # insert something into the footer switch_view($document, wdSeekCurrentPageFooter); my $st_footer = create_style($document, "Verdana", 8, 0, 0); set_style($document, $st_footer); text ($document, "this is the document's footer, it will be repeated on all pages"); # Inserting current page, total page text ($document, " Page "); $selection->Fields->Add ($selection->{Range}, wdFieldPage ); text ($document, " of "); $selection->Fields->Add ($selection->{Range}, wdFieldNumPages); # go back to body of document switch_view($document, wdSeekMainDocument); # my $picture = insert_picture($document, 'c:\some\path\to\the\file.eps', 100, 100, 100, 30); my $picture = insert_picture($document, '$jpgfil', 100, 100, 100, 30); my $st_12_arial = create_style($document, "Arial", 12, 0, 0); ############################# Demonstrating Bookmarks ##### enter ($document); # set_style($document, $st_12_arial); # text ($document, "Not bold"); # bold ($document, 1); # text ($document, " but this IS bold "); # bold ($document, 0); # text ($document, "this is not bold anymore"); # enter ($document); # # my $first_bookmark = insert_bookmark($document); # text ($document, "here's a bookmark"); # enter ($document); # text ($document, "Writing some lines"); # enter ($document); # text ($document, "Before going to the bookmark"); # enter ($document); # # my $second_bookmark = insert_bookmark($document); # goto_bookmark($document, $first_bookmark); # text ($document, "Went back to first bookmark"); # goto_bookmark($document, $second_bookmark); # text ($document, "Went back to second bookmark"); # ############################################################# ############### indenting ############## my $st_indent = create_style($document, "Arial", 10, 0, 0); style_indents($document, $st_indent, 100, 200); enter ($document); set_style ($document, $st_indent); text ($document, join " ", 1 .. 100); enter ($document); text ($document, join " ", 'a' .. 'cz'); items ($document, "Fruits", $st_indent, qw(Apple Pear Peach Cherry Nectarine Orange)); items ($document, "Shapes", $st_indent, qw(Triangle Rhombus Trapezoid Pentagon Circle)); ## inserting a box my $st_box = create_style($document, "Arial", 8, 0, 1); style_indents($document, $st_box, 0, 0); my $box = insert_box($document, 350, 550, 140, 40); set_style($document, $st_box); text ($document, "hello out of the box"); # save_doc_as($document, 'c:\generated.doc'); # save_doc_as($document, '$docfil'); ## uncomment the following two if word should shut down # close_doc($document); # $word->Quit; } sub text { my $document = shift; my $text = shift; $document->ActiveWindow->Selection -> TypeText($text); } # aka new line, newline or NL sub enter { my $document = shift; $document->ActiveWindow->Selection -> TypeParagraph; } sub set_style { my $document = shift; my $style_arg = shift; # bugs # $document->ActiveWindow->Selection -> {Style} = $style_arg -> {name}; } sub create_style { my $document = shift; my $fontname = shift; my $font_size = shift; my $bold = shift; my $italic = shift; # bugs # my $style = $document->Styles->Add($cur_style); my $style_font = $style->{Font}; $style_font -> {Name } = $fontname; $style_font -> {Size } = $font_size; $style_font -> {Bold } = $bold; $style_font -> {Italic} = $italic; my %style; $style{name} = $cur_style++; return \%style; } # use switch_view to change to header, footer, main document and so on... # possible constants for view are: wdSeekCurrentPageFooter # # o wdSeekCurrentPageHeader # o wdSeekEndnotes # o wdSeekEvenPagesFooter # o wdSeekEvenPagesHeader # o wdSeekFirstPageFooter # o wdSeekFirstPageHeader # o wdSeekFootnotes # o wdSeekMainDocument # o wdSeekPrimaryFooter # o wdSeekPrimaryHeader # sub switch_view { my $document = shift; my $view = shift; $document -> ActiveWindow -> ActivePane -> View -> {SeekView} = $view; } sub insert_picture { my $document = shift; my $file = shift; my $left = shift; my $top = shift; my $width = shift; my $height = shift; # bugs # my $picture = # bugs # $document-> Shapes -> AddPicture ( # bugs # $file, # bugs # msoFalse, # link to file # bugs # msoTrue, # save with document # bugs # $left, $top, $width, $height, # bugs # $document->ActiveWindow->Selection->{Range} # bugs # ); # bugs # return $picture; } sub bold { my $document = shift; my $bold = shift; $document->ActiveWindow->Selection->{Font}->{Bold} = $bold ? msoTrue : msoFalse; } sub goto_bookmark { my $document = shift; my $bookmark = shift; $document->ActiveWindow->Selection -> GoTo(wdGoToBookmark, 0, 0, $bookmark->{Name}); } sub insert_bookmark { my $document = shift; my $bookmark = $document -> Bookmarks -> Add ($cur_bookmark++, $document->ActiveWindow->Selection->Range); return $bookmark; } sub style_indents { my $document = shift; my $style_arg = shift; my $first_line_indent = shift; my $other_line_indent = shift; my $style = $document->Styles($style_arg->{name}); $style->ParagraphFormat->{LeftIndent } = $other_line_indent; $style->ParagraphFormat->{FirstLineIndent} = -$other_line_indent + $first_line_indent; } sub items { my $document = shift; my $title = shift; my $style = shift; my @array = @_; enter($document); set_style($document, $style); bold($document, 1); text($document, $title); bold($document, 0); text($document, "\x09"); foreach my $a (@array) { text($document, $a); text($document, "\x0b"); } } sub insert_box { my $document = shift; my $left = shift; my $top = shift; my $width = shift; my $height = shift; my $shape = $document->Shapes->AddTextbox(msoTextOrientationHorizontal, $left, $top, $width, $height); $shape -> Select; my $selection = $word->Selection; $selection -> ShapeRange -> Line -> {DashStyle} = msoLineRoundDot; return $shape } sub close_doc { my $document = shift; $document -> Close; } sub save_doc_as { my $document = shift; my $filename = shift; $document->SaveAs($filename); } sub style_keep_with_next { my $document = shift; my $style_arg = shift; my $style = $document->Styles($style_arg->{name}); $style->{ParagraphFormat}->{KeepWithNext} = msoTrue; } sub style_keep_together { my $document = shift; my $style_arg = shift; my $style = $document->Styles($style_arg->{name}); $style->{ParagraphFormat}->{KeepTogether} = msoTrue; } sub style_border { my $document = shift; my $style_arg = shift; my $border = shift; my $border_style = shift; my $border_width = shift; my $border_color = shift; my $style = $document->Styles($style_arg->{name}); $style->Borders($border) -> {LineStyle} = $border_style; $style->Borders($border) -> {LineWidth} = $border_width; $style->Borders($border) -> {Color } = $border_color; } sub style_tab_at_position { my $document = shift; my $style_arg = shift; my $position = shift; my $left_or_right= shift; my $style = $document->Styles($style_arg->{name}); $style->ParagraphFormat->{TabStops}->Add($word->InchesToPoints($position), $left_or_right); } sub style_space_before { my $document = shift; my $style_arg = shift; my $space = shift; my $style = $document->Styles($style_arg->{name}); $style->ParagraphFormat->{SpaceBefore} = $space; } sub style_space_after { my $document = shift; my $style_arg = shift; my $space = shift; my $style = $document->Styles($style_arg->{name}); $style->ParagraphFormat->{SpaceAfter} = $space; } sub style_alignment { my $document = shift; my $style_arg = shift; my $alignment = shift; my $style = $document->Styles($style_arg->{name}); $style->ParagraphFormat->{Alignment} = $alignment; } sub goto_end_of_document { my $document = shift; $document->ActiveWindow->Selection->{Range} -> EndKey(wdStory); #my $selection = $word->Selection; #$selection -> EndKey (wdStory); } sub insert_page_break { my $document = shift; #my $selection = $word->Selection; #$selection -> InsertBreak(wdPageBreak); $document->ActiveWindow->Selection->{Range} -> InsertBreak(wdPageBreak); } sub landscape { my $document = shift; $document->PageSetup->{Orientation} = wdOrientLandscape; } # word end bdbdbdbd bdbdbdbd bdbdbdbd bdbdbdbd bdbdbdbd bdbdbdbd bdbdbdbd bdbdbdbd sub sendmail1() { open(MAIL, '| /usr/lib/sendmail user@there.com') or die; print MAIL <<'EOF'; To: user@there.com From: user@here.com Hello, World! EOF } sub sendmail2() { use Net::SMTP; $smtp = Net::SMTP->new('mail.example.com'); # connect to an SMTP server $smtp->mail('user@here.com'); # use the sender's address here $smtp->to('user@there.com'); # recipient's address $smtp->data(); # Start the mail # Send the header. $smtp->datasend("To: user\@there.com\n"); $smtp->datasend("From: user\@here.com\n"); $smtp->datasend("Subject: sendmail2 email\n"); $smtp->datasend("\n"); # Send the body. $smtp->datasend("Hello, World!\n"); $smtp->dataend(); # Finish sending the mail $smtp->quit; # Close the SMTP connection #Another alternative is Mail::Sender, which can be used like so: # # use Mail::Sender; # # $sender = Mail::Sender->new({smtp => 'mail.yourdomain.com', from => 'your@address.com'}); # $sender->MailFile({ # to => 'some@address.com', subject => 'Here is the file', # msg => "I'm sending you the list you wanted.", # file => 'filename.txt', # }); # # Or Mail::Sendmail, which can be used like this: # use Mail::Sendmail; # # %mail = ( To => 'you@there.com', # From => 'me@here.com', # Message => "This is a minimalistic message" # ); # # if (sendmail %mail) { print "Mail sent OK.\n" } # else { print "Error sending mail: $Mail::Sendmail::error\n"; } } sub mth_loop() { $j=0; $jj=sprintf( "%02d", $j ); # foreach my $mth (@$mth_names_array) { foreach my $mth (@mth_names_array) { $mthcaps=$mth; $mthcaps =~ tr/a-z/A-Z/; printf( "$progid mth_names $dttm MSG: [$j][$jj] mth=[%s][%s]\n", $mth,$mthcaps ); printf( "$progid mth_names $dttm MSG: [$j][$jj] mth_names_array=[%s]\n", $mth_names_array[$j] ); printf( "$progid mth_names $dttm MSG: [$j][$jj] mth_names=[%s]\n", $mth_names{$mthcaps} ); $j++; $jj=sprintf( "%02d", $j ); } printf( "$progid mth_names $dttm BUGS: $j mth=[%s]\n", $mth ); } # The maximum number of columns in an Excel worksheet is ... sub n2l { my $max_colnum = 256; my ($n) = @_; my $l; if ($n >= 1 && $n <= 26) { $l = n2ll($n - 1); } elsif ($n > 26 && $n <= $max_colnum) { my $chr1 = n2ll( int(($n - 1)/26-1) ); my $chr2 = n2ll( ($n-1)%26); $l = $chr1 . $chr2; } else { die "$progid $dttm ERR_5530: n=[$n] gt (1-$max_colnum)"; } return $l; } sub n2ll { my ($n) = @_; if ($n > 26) { die "n=$n out of range"; } my $l= chr ($n+ord("A")); return $l; } sub kosystem { # my( $cmd ) = @_; # my( $status ) = ""; print "gd MSG: kosystem=[$cmd]\n"; $status = system( $cmd ); if ( $status != 0 ) { print "MSG: status=[$status]\n"; } return( $status ); } sub misc { # $mode = 0666; # mkdir($dir_name, $mode ); # rmdir($dir_name); # chdir($dir_name); # $dir = $ENV{CDW}; # YYYY-MM-DD # m/ (\d{4})-(\d\d)-(\d\d) / # $1=YYYY $2=MM $3=DD print "enter usrcmd q=quit: "; $usrcmd = <STDIN>; } 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 ); }

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

koh5_pano

Navigation