From 02bf599d7d944727320cf6b69e0624575d64d3fb Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Tue, 27 Mar 2018 10:20:37 +0200 Subject: [PATCH] remove old copies of perl files - pdfextract changes --- Tools/pdfextract.pl | 31 ++- app_v3/Tools/old.creorgadb.pl | 197 ++++++++++++++++++ app_v3/Tools/old.pdfextract.pl | 366 +++++++++++++++++++++++++++++++++ 3 files changed, 585 insertions(+), 9 deletions(-) create mode 100644 app_v3/Tools/old.creorgadb.pl create mode 100644 app_v3/Tools/old.pdfextract.pl diff --git a/Tools/pdfextract.pl b/Tools/pdfextract.pl index f301956..3130134 100644 --- a/Tools/pdfextract.pl +++ b/Tools/pdfextract.pl @@ -6,6 +6,7 @@ use Getopt::Long; use File::Basename; use File::Path qw/make_path/; use Data::Dumper; +use File::Copy qw/copy/; use lib('.'); use sqlite; use utf8; @@ -153,7 +154,7 @@ if ($totext == 1) { print "Import des données Page: ".basename($n)."\n"; &importstatementdata($stmtdata,$n); } - #unlink("$n.txt"); + unlink("$n.txt"); } } } @@ -373,11 +374,17 @@ sub importinvoicedata(){ if (exists($impdata->{reference})) { push @upd,"reference='".$impdata->{reference}."'"; my $nfname = dirname($fname).$sep.$template.'_'.$impdata->{checkservice}.'_'.$impdata->{reference}.".pdf"; - if (-e $nfname){ - unlink($nfname); - } - rename($fname,$nfname); - push @upd,"invoicefile='".basename($nfname)."'"; + print "Copy to new file: ".$nfname."\n"; + copy ($fname,$nfname); + my $insdbfile = basename($nfname); + if (-e $nfname){ + print "remove extracted file: ".$fname."\n"; + unlink($fname); + }else { + $insdbfile = basename($fname); + } + + push @upd,"invoicefile='".$insdbfile."'"; } if (exists($impdata->{totalamount})) { push @upd,"invoiceamount=".$impdata->{totalamount}.""; @@ -423,12 +430,18 @@ sub importinvoicedata(){ if (exists($impdata->{reference})) { push(@ins1,"reference");push (@ins2,"'".$impdata->{reference}."'"); my $nfname = dirname($fname).$sep.$template.'_'.$impdata->{checkservice}.'_'.$impdata->{reference}.".pdf"; + print "Copy to new file: ".$nfname."\n"; + copy ($fname,$nfname); + my $insdbfile = basename($nfname); if (-e $nfname){ - unlink($nfname); + print "remove extracted file: ".$fname."\n"; + unlink($fname); + }else { + $insdbfile = basename($fname); } - rename($fname,$nfname); + #rename($fname,$nfname); - push(@ins1,"invoicefile");push (@ins2,"'".basename($nfname)."'"); + push(@ins1,"invoicefile");push (@ins2,"'".$insdbfile."'"); } } #accmonth,childuuid,invoicedate,invoiceamount,reference diff --git a/app_v3/Tools/old.creorgadb.pl b/app_v3/Tools/old.creorgadb.pl new file mode 100644 index 0000000..ef7be3a --- /dev/null +++ b/app_v3/Tools/old.creorgadb.pl @@ -0,0 +1,197 @@ +#!C:\Perl\bin\perl.exe + +use strict; +use File::Basename; +use Getopt::Long; +use Data::Dumper; +use utf8; +use lib ('.'); +use sqlite; + +my $dbfile = ""; +my $templatedb = ""; +GetOptions("dbfile|db=s" => \$dbfile, + "template|t=s" => \$templatedb + ); +#-db "C:\\Test\\sqlite\\bas\\9475a95d-e2ad-4586-8432-d44a604b3fd3.sqlite" -t "C:\\Workspace\\creorga\\app\\defaults\\profile\\creorga.sqlite" + +sub getcoldef($){ + my $strddl = shift; + my $curddl = $strddl; + $curddl =~ s/\s+/\ /g; + my $bi = index($curddl,'(')+1; + my $ei = rindex($curddl,')'); + + $curddl = substr($curddl,$bi,length($curddl)-$bi-(length($curddl)-$ei));#curddl.substring(curddl.indexOf('(')+1,curddl.lastIndexOf(')')).trim().replace(/\s+/g," "); + my @colsfull = split(/,/,$curddl);# curddl =curddl.replace(new RegExp("\\b(" + appdb.keywords.join("|") + ")\\b", "g"), ""); + my $tblobj = (); + foreach my $c (@colsfull){ + $c =~ s/^\s+//; + $c =~ s/\s+$//; + my @coldef = split(/\ /,$c); + my $type = uc($coldef[1]); + if (($type =~ /^TEXT/) || ($type =~ /^REAL/) || ($type =~ /^INTEGER/) || ($type =~ /^BOOLEAN/) || ($type =~ /^DATE/) || ($type =~ /^DATETIME/)) { + $tblobj->{$coldef[0]} = $type; + } + } + return $tblobj; +} + +if ((! -e $dbfile) || (! -e $templatedb)) { + print "incomplete input!\n"; + exit(1); +} + +my $db = sqlite->new($templatedb); + +my $dbdefsql = "SELECT type, name,tbl_name,sql FROM sqlite_master order by name,tbl_name,type;"; +my $defdbschemacfg = $db->dbquerysorted($dbdefsql); +#$db = undef; +$db = sqlite->new($dbfile); +my $tcurcfg = $db->dbquerysorted($dbdefsql); +# var re = /(\w+).*,/; +my $keycnt = keys(%{$defdbschemacfg}); +my $bvaccum = 0; +my $stexec = 0; + +foreach my $pd (sort {$a <=> $b} keys(%{$defdbschemacfg})){ + + if ($defdbschemacfg->{$pd}->{'type'} eq 'table') { + my $bupdate = 0; + my $bexists = 0; + my $cucols = ''; + my $oldobj = (); + foreach my $pc (keys(%{$tcurcfg})){ + if (($tcurcfg->{$pc}->{tbl_name} eq $defdbschemacfg->{$pd}->{tbl_name}) && ($tcurcfg->{$pc}->{type} eq $defdbschemacfg->{$pd}->{type})){ + print $defdbschemacfg->{$pd}->{type}.": ".$defdbschemacfg->{$pd}->{tbl_name}. "\n"; + if ($tcurcfg->{$pc}->{sql} ne $defdbschemacfg->{$pd}->{sql}){ + $bupdate = 1; + $oldobj= getcoldef($tcurcfg->{$pc}->{sql}); + } + $bexists = 1; + last; + } + } + if (($bexists==1) && ($bupdate== 1)){ + my $sql_installnew = $defdbschemacfg->{$pd}->{sql}; + my $newobj = getcoldef($defdbschemacfg->{$pd}->{sql}); + my @copycols = (); + for my $x (keys(%{$newobj})){ + if (exists($oldobj->{$x})) { + push @copycols,$x; + } + } + + my @ainssql = (); + push(@ainssql,"DROP TABLE IF EXISTS new_".$defdbschemacfg->{$pd}->{tbl_name}. ";"); + my $sql_tmptbl = $sql_installnew; + $sql_tmptbl =~ s/CREATE\ TABLE\ /CREATE TABLE new_/; + $sql_tmptbl =~ s/"//g; + push(@ainssql,$sql_tmptbl); + push(@ainssql,"INSERT INTO new_".$defdbschemacfg->{$pd}->{tbl_name}." (".join(',',@copycols).") SELECT ".join(',',@copycols)." FROM ".$defdbschemacfg->{$pd}->{tbl_name}.";"); + push(@ainssql,"DROP TABLE ".$defdbschemacfg->{$pd}->{tbl_name}.";"); + push(@ainssql,"ALTER TABLE new_".$defdbschemacfg->{$pd}->{tbl_name}. " RENAME TO ".$defdbschemacfg->{$pd}->{tbl_name}.";"); + $bvaccum = 1; + print Dumper(@ainssql); + my $stexec = 0; + for(my $s=0;$sdbexec($ainssql[$s]); + } + } + #print "tbl done\n"; + } + elsif ($bexists == 0){ + my $sql_installnew = $defdbschemacfg->{$pd}->{sql}; + $db->dbexec($sql_installnew); + } +} elsif (($defdbschemacfg->{$pd}->{'type'} eq 'trigger') || ($defdbschemacfg->{$pd}->{'type'} eq 'index')) { + my $bexists = 0; + my $bupdate = 0; + foreach my $pc (keys(%{$tcurcfg})){ + if (($tcurcfg->{$pc}->{tbl_name} eq $defdbschemacfg->{$pd}->{tbl_name}) && ($tcurcfg->{$pc}->{type} eq $defdbschemacfg->{$pd}->{type})){ + print $defdbschemacfg->{$pd}->{type}.": ".$defdbschemacfg->{$pd}->{tbl_name}. "\n"; + if ($tcurcfg->{$pc}->{sql} ne $defdbschemacfg->{$pd}->{sql}){ + $bupdate = 1; + } + $bexists = 1; + last; + } + } + if (($bexists==1) && ($bupdate== 1)){ + $bvaccum = 1; + my @ainssql = (); + if ($defdbschemacfg->{$pd}->{type} eq 'trigger'){ + push @ainssql,"DROP TRIGGER IF EXISTS ".$defdbschemacfg->{$pd}->{'name'}.";"; + } elsif ($defdbschemacfg->{$pd}->{type} eq 'index') { + push @ainssql,"DROP INDEX IF EXISTS ".$defdbschemacfg->{$pd}->{'name'}.";"; + } + push @ainssql,$defdbschemacfg->{$pd}->{'sql'}; + my $stexec = 0; + for(my $s=0;$sdbexec($ainssql[$s]); + } + } + }elsif ($bexists == 0) { + my $sql_installnew = $defdbschemacfg->{$pd}->{sql}; + #$tcurcfg->dbexec($sql_installnew); + } + } +} +# //dump("goto check defaults!\n"); +# appdb.check_defaultdata(); +if ($bvaccum == 1) { + print "Exec vacuum;\n"; + $db->dbexec("vacuum;"); +} +# check_defaultdata: function(){ +# +# appdb.closeConnection(); +# //dump("Local DBFile: " + appdb.dbFile.path + "\n"); +# var tables = ['creche','groups','workinghours','vacancy','costs','planningtemplate']; +# for (var t in tables) { +# var sql = "select count(*) as cnt from "+ tables[t]+";"; +# +# var res = appdb.dbquery(sql); +# +# if ((res) && (res.sqldata[0].cnt == '0')) { +# dump(sql + " --> "+JSON.stringify(res)+ "\n"); +# var inssql = []; +# if (tables[t] == 'creche') { +# inssql.push("INSERT INTO creche (crechename, adress, city, country, zip, maxchilds, uuid, minage, maxage) VALUES ('"+curcfg.name+"', '', '', 'Luxembourg', '', 28, '"+ curcfg.uuid +"', 2, 84);"); +# } else if (tables[t] == 'groups') { +# var sinssql = "INSERT INTO groups (grpname, maxchilds, minage, maxage, uuid) VALUES "; +# sinssql += "('Groupe Bébé', 6, 2, 12, '"+ appdb.generate_uuid() +"'),"; +# sinssql += "('Groupe plus 1 an ', 6, 12, 24, '"+ appdb.generate_uuid() +"'),"; +# sinssql += "('Groupe de 2 ans à 3 ans', 8, 24, 36, '"+ appdb.generate_uuid() +"'),"; +# sinssql += "('Groupe de 3 à 4 ans ', 8, 36, 48, '"+ appdb.generate_uuid() +"'),"; +# sinssql += "('Groupe plus 4 ans ( Scolaires)', 11, 48, 84, '"+ appdb.generate_uuid() +"');" +# inssql.push(sinssql); +# } else if (tables[t] == 'workinghours') { +# inssql.push("INSERT INTO workinghours (uuid, datestart, montimeopen, montimeclose, tuetimeopen, tuetimeclose, wedtimeopen, wedtimeclose, thutimeopen, thutimeclose, fritimeopen, fritimeclose, sattimeopen, sattimeclose, suntimeopen, suntimeclose, crecheuuid) VALUES ('"+appdb.generate_uuid()+"', strftime(\"%Y\",date('now','-1 year')) || '-01-01', '07:00', '19:00', '07:00', '19:00', '07:00', '19:00', '07:00', '19:00', '07:00', '19:00', null, null, null, null, '"+ curcfg.uuid+"');"); +# +# } else if (tables[t] == 'costs') { +# var sinssql = "INSERT INTO costs (startdate, costsperhour, weeklyhourslimit, dailylunchcosts, uuid) VALUES "; +# sinssql += "('2015-01-01', 0.0, 0.0, 0.0, '"+ appdb.generate_uuid()+"');"; +# inssql.push(sinssql); +# } else if (tables[t] == 'planningtemplate') { +# //dump("Planningtemplate\n"); +# var sinssql = "INSERT INTO planningtemplate (uuid, montimebegin, montimeend, monlunch, tuetimebegin, tuetimeend, tuelunch, wedtimebegin, wedtimeend, wedlunch, thutimebegin, thutimeend, thulunch, fritimebegin, fritimeend, frilunch, templatename) VALUES "; +# sinssql += "('"+ appdb.generate_uuid()+"', '08:00', '18:00', 1, '08:00', '18:00', 1, '08:00', '18:00', 1, '08:00', '18:00', 1, '08:00', '18:00',1, 'plain temps (60h)'),"; +# sinssql += "('"+ appdb.generate_uuid()+"', '08:00', '13:00', 1, '08:00', '13:00', 1, '08:00', '13:00', 1, '08:00', '13:00', 1, '08:00', '13:00',1,'matin (30h)'),"; +# sinssql += "('"+ appdb.generate_uuid()+"', '13:00', '18:00', 1, '13:00', '18:00', 1, '13:00', '18:00', 1, '13:00', '18:00', 1, '13:00', '18:00',1, 'après-midi (30h)');"; +# inssql.push(sinssql); +# //dump(inssql + "\n"); +# } +# for (var i in inssql){ +# appdb.dbexec_silent(inssql[i]); +# } +# //dump("Set default Data:" + inssql + "\n"); +# +# } +# } +# } + diff --git a/app_v3/Tools/old.pdfextract.pl b/app_v3/Tools/old.pdfextract.pl new file mode 100644 index 0000000..34f054a --- /dev/null +++ b/app_v3/Tools/old.pdfextract.pl @@ -0,0 +1,366 @@ +#!C:\Strawberry\bin\perl.exe + +use strict; +use PDF::API2; +use Getopt::Long; +use File::Basename; +use File::Path qw/make_path/; +use Data::Dumper; +use File::Copy qw/copy/; +use lib('.'); +use sqlite; +use utf8; +my $dbfile=""; +my $pdffile=""; +my $outputdir=""; +my $toolsdir=""; +my $delorig=0; +my $totext =1; +my $template = ""; +my $log =""; +my $db = undef; +my $cmonth = "none"; +my $frmonth = {"Janvier" => '01',"Février"=> '02',"Mars" => '03',"Avril" => '04', "Mai" => '05',"Juin" => '06',"Juillet" => '07',"Août" => '08',"Septembre" => '09',"Octobre" => '10',"Novembre" => '11',"Décembre" => '12'}; + +GetOptions("dbfile|db=s" => \$dbfile, + "pdf|p=s" => \$pdffile, + "outdir|o=s" => \$outputdir, + "toolsdir|t=s" => \$toolsdir, + "type|x=s" => \$template, #template => [inv|stmt] + "log|l=s" => \$log + ); +my $sep = '/'; +$toolsdir = dirname($0); +open(LOG,">>".$log); +print LOG "DB:".$dbfile."\r\n"; +print LOG "PDF:".$pdffile."\r\n"; +print LOG "OUTPUTDIR:".$outputdir."\r\n"; +#print LOG "TOOLSDIR:".$toolsdir."\r\n"; +print LOG "TEMPLATE:".$template."\r\n"; +print LOG "LOG:".$log."\r\n"; +close(LOG); +my $pdftotext = ""; +if ($^O eq "MSWin32") { + $sep = "\\"; + $pdftotext=$toolsdir.$sep.'pdftotext.exe'; +}else { + $pdftotext=$toolsdir.$sep.'pdftotext'; +} +#if ($toolsdir eq "") { + +#} + +if (($log ne "") && (! -e $log)){ + open(LOG,">".$log); + close(LOG); +} + +#my $filename = "C:\\projects\\creorga\\calimero\\factures201512.pdf"; +if (! -d $outputdir) { + make_path($outputdir); +} +if (! -e $pdftotext) { + $totext = 0; +} + + +if ((! -e $dbfile) || (! -e $pdffile) || (! -d $outputdir) || (! -d $toolsdir)) { + open(LOG,">>".$log); + print LOG localtime().":ERROR:incomplete input!\n"; + close(LOG); + exit(1); +} + +print "1: Split du PDF en pages!\r\n"; +my $oldpdf = PDF::API2->open($pdffile); +#1.split pdffile +$template = lc($template); +my @nfiles = (); +my $xx = $oldpdf->pages; +for my $page_nb (1..$xx) { + my $newpdf = PDF::API2->new; + my $page = $newpdf->importpage($oldpdf, $page_nb); + my $npdfname = $outputdir.'/'.$template.substr(basename($pdffile),0,-4).".".$page_nb.".pdf"; + push @nfiles,$npdfname; + if (-e $npdfname){ + unlink($npdfname); + } + $newpdf->saveas($npdfname); +} +#2.convert pdftotxt & get data +if (-e $dbfile) { + #$dbfile =~ s/\\\\/\\/g; + $db = sqlite->new($dbfile); +} +if ($totext == 1) { + foreach my $n (@nfiles){ + if (-e $n.'.txt'){ + unlink($n.'.txt'); + } + unlink($n.'.txt'); + print "Lire des donnees de la page: ".basename($n)."\n"; + + my $cmd = '"'.$pdftotext.'" -q -table -eol unix "'.$n.'" "'.$n.'.txt"'; + my $st = system($cmd); + if (($st == 0) && (-e "$n.txt")){ + my @pdata = (); + open(PDFDATA,"$n.txt"); + while (my $l = ) { + chomp($l); + if ($l ne "") { + push @pdata,$l; + } + } + close(PDFDATA); + if (lc($template) eq "inv") { + my $childdata = &parseinvoicedata(\@pdata); + print "Import des donnees Check-Service No.: ".$childdata->{checkservice}."\n"; + &importinvoicedata($childdata,$n); + }elsif (lc($template eq "stmt")){ + my $stmtdata = &parsestatementdata(\@pdata); + print "Import des données Page: ".basename($n)."\n"; + &importstatementdata($stmtdata,$n); + } + unlink("$n.txt"); + } + } +} + +sub parseinvoicedata(){ + my $tmpdata = shift; + my @invoicedata = @{$tmpdata}; + my $pxdata = (); + foreach my $p (@invoicedata){ + if ($p =~ /N. Facture/) { + my ($tmp) = $p =~ m/.+\s(\d{4,}.\d{1,2}.\d{4,})\s.+$/; + $pxdata->{reference} = $tmp; + } + if ($p =~ /Date de la/) { + my ($d,$m,$y) = $p =~ m/.+\s(\d{1,2}).(\d{1,2}).(\d{4,})$/; + if (length($d) == 1) { $d = "0".$d;} + if (length($m) == 1) { $m = "0".$m;} + $pxdata->{invoicedate} = $y.'-'.$m.'-'.$d; + } + if (($p =~ /facture/) && ($pxdata->{invoicedate} eq "--")) { + my ($d,$m,$y) = $p =~ m/.+\s(\d{1,2}).(\d{1,2}).(\d{4,})$/; + if (length($d) == 1) { $d = "0".$d;} + if (length($m) == 1) { $m = "0".$m;} + $pxdata->{invoicedate} = $y.'-'.$m.'-'.$d; + } + if ($p =~ /Carte N./) { + my ($tmp) = $p =~ m/.+\s(\d+)$/; + $pxdata->{checkservice} = $tmp; + } + if ($p =~ /Heure/) { + my ($tmp1,$tmp2) = $p =~ m/.+\s(\d+).(\d+)+$/; + $pxdata->{hoursamount} = $tmp1.'.'.$tmp2; + } + if ($p =~ /Repas/) { + my ($tmp1,$tmp2) = $p =~ m/.+\s(\d+).(\d+)+$/; + $pxdata->{lunchamount} = $tmp1.'.'.$tmp2; + } + if ($p =~ /Montant\s.\spayer/) { + my ($tmp1,$tmp2) = $p =~ m/.+\s(\d+).(\d+)+$/; + $pxdata->{totalamount} = $tmp1.'.'.$tmp2; + } + #print Dumper(@pdata); + } + return $pxdata; +} + +sub parsestatementdata(){ + my $tmpdata = shift; + my @xstmtdata = @{$tmpdata}; + my $sxdata = (); + + foreach my $p (@xstmtdata){ + if ($p =~ /P.riode/) { + my ($m1,$y1,$m2,$y2) = $p =~ m/.+\s(.+)\s+(\d+)\s+.\s+(.+)\s+(\d+)$/; + if (($m1 eq $m2) && ($y1 eq $y2)){ + $cmonth=$y1.'-'.$frmonth->{$m1}; + } + } + if ($p =~ /\d{13,}/) { + my ($csnum,$am) = $p =~ m/.+\s+(\d{13,})\s+([\d|\ |,]+)$/; + $am =~ s/\s+//; + $am =~ s/,/./; + $sxdata->{$cmonth}->{$csnum}=$am; + } + #print $p."\n"; + } + return $sxdata; +} + +sub importinvoicedata(){ + my $impdata = shift; + my $fname = shift; + if (defined($db)){ + my $child = $db->dbquerysorted("select uuid from childs where replace(checkservicenumber,' ','') = '".$impdata->{checkservice}."';"); + if (keys(%{$child}) == 1) { + my @refx = split(/\./,$impdata->{reference}); + if (length($refx[1]) == 1) { + $refx[1] = '0'.$refx[1]; + } + my $accdata = $db->dbquerysorted("select accmonth,childuuid from accounting where childuuid='".$child->{0}->{uuid}."' and accmonth=date('".$refx[0].'-'.$refx[1]."-01');"); + + if (keys(%{$accdata}) == 1) { + #make update + my @upd = (); + if (exists($impdata->{reference})) { + push @upd,"reference='".$impdata->{reference}."'"; + my $nfname = dirname($fname).$sep.$template.'_'.$impdata->{checkservice}.'_'.$impdata->{reference}.".pdf"; + if (-e $nfname){ + unlink($nfname); + } + copy($fname,$nfname); + print "Copy file to new name: ".$nfname."\n"; + my $insfname = basename($nfname); + if (-e $nfname){ + unlink($fname); + print "remove file: ".$fname."\n"; + } else{ + $insfname = basename($fname); + } + #rename($fname,$nfname); + push @upd,"invoicefile='".$insfname."'"; + } + if (exists($impdata->{totalamount})) { + push @upd,"invoiceamount=".$impdata->{totalamount}.""; + } + if (exists($impdata->{invoicedate})) { + push @upd,"invoicedate=date('".$impdata->{invoicedate}."')"; + } + my $sql = "update accounting set ".join(',',@upd)." where childuuid='".$child->{0}->{uuid}."' and accmonth=date('".$refx[0].'-'.$refx[1]."-01');"; + #print $sql."\n"; + my $r = $db->dbexec($sql); + + if (($log ne "") && (-e $log)){ + if (!defined($r)) { + open(LOG,">>".$log); + print LOG localtime().":ERROR:".$sql."\n"; + close(LOG); + } else { + open(LOG,">>".$log); + print LOG localtime().":SUCCESS:".$sql."\n"; + close(LOG); + } + } + }else { + my @ins1 = (); + my @ins2 = (); + if (exists($impdata->{invoicedate}) && (length($impdata->{invoicedate})== 10 )){ + #my @refx = split(/\./,$impdata->{reference}); + #if (length($refx[1]) == 1) { + # $refx[1] = '0'.$refx[1]; + #} + push(@ins1,"accmonth");push (@ins2,"date('".$refx[0].'-'.$refx[1]."-01')"); + push(@ins1,"childuuid");push (@ins2,"'".$child->{0}->{uuid}."'"); + push(@ins1,"invoicedate");push (@ins2,"date('".$impdata->{invoicedate}."')"); + if (exists($impdata->{totalamount})) { + push(@ins1,"invoiceamount");push (@ins2,"".$impdata->{totalamount}.""); + } + if (exists($impdata->{reference})) { + push(@ins1,"reference");push (@ins2,"'".$impdata->{reference}."'"); + my $nfname = dirname($fname).$sep.$template.'_'.$impdata->{checkservice}.'_'.$impdata->{reference}.".pdf"; + if (-e $nfname){ + unlink($nfname); + } + rename($fname,$nfname); + + push(@ins1,"invoicefile");push (@ins2,"'".basename($nfname)."'"); + } + } + #accmonth,childuuid,invoicedate,invoiceamount,reference + my $sql = "insert into accounting (".join(',',@ins1).") VALUES (".join(',',@ins2).");"; + #print $sql."\n"; + my $r = $db->dbexec($sql); + if (($log ne "") && (-e $log)){ + if (!defined($r)) { + open(LOG,">>".$log); + print LOG localtime().":ERROR:".$sql."\n"; + close(LOG); + } else { + open(LOG,">>".$log); + print LOG localtime().":SUCCESS:".$sql."\n"; + close(LOG); + } + } + + } + } + } +} + +sub importstatementdata(){ + my $simpdata = shift; + my $fname = shift; + #print Dumper($simpdata); + my $n=0; + foreach my $m (keys(%{$simpdata})){ + $n++; + if (-e dirname($fname).$sep."prestation.".$m."-".$n.".pdf"){ + unlink(dirname($fname).$sep."prestation.".$m."-".$n.".pdf"); + } + rename($fname,dirname($fname).$sep."prestation.".$m."-".$n.".pdf"); + foreach my $csnum (keys(%{$simpdata->{$m}})){ + $simpdata->{$m}->{fnum} = $n; + } + } + foreach my $m (keys(%{$simpdata})){ + $n++; + if ($m =~ /\d{4,}-\d{2,}/) { + foreach my $csnum (keys(%{$simpdata->{$m}})){ + print "Import Check-Service no: " + $csnum + "\n"; + if (defined($db)){ + my $child = $db->dbquerysorted("select uuid from childs where replace(checkservicenumber,' ','') = '".$csnum."';"); + if (keys(%{$child}) == 1) { + my $accdata = $db->dbquerysorted("select accmonth,childuuid from accounting where childuuid='".$child->{0}->{uuid}."' and accmonth=date('".substr($m,0,4).'-'.substr($m,5,2)."-01');"); + if (keys(%{$accdata}) == 1) { + #make update + my @upd = (); + push @upd,"benefitamount='".$simpdata->{$m}->{$csnum}."'"; + push @upd,"benefitfile='prestation.".$m."-".$simpdata->{$m}->{fnum}.".pdf'"; + my $sql = "update accounting set ".join(',',@upd)." where childuuid='".$child->{0}->{uuid}."' and accmonth=date('".substr($m,0,4).'-'.substr($m,5,2)."-01');"; + #print $sql."\n"; + my $r = $db->dbexec($sql); + if (($log ne "") && (-e $log)){ + if (!defined($r)) { + open(LOG,">>".$log); + print LOG localtime().":ERROR:".$sql."\n"; + close(LOG); + } else { + open(LOG,">>".$log); + print LOG localtime().":SUCCESS:".$sql."\n"; + close(LOG); + } + } + }else { + my @ins1 = (); + my @ins2 = (); + push(@ins1,"accmonth");push (@ins2,"date('".substr($m,0,4).'-'.substr($m,5,2)."-01')"); + push(@ins1,"childuuid");push (@ins2,"'".$child->{0}->{uuid}."'"); + push(@ins1,"benefitamount");push (@ins2,"".$simpdata->{$m}->{$csnum}.""); + push(@ins1,"benefitfile");push (@ins2,"'prestation.".$m."-".$simpdata->{$m}->{fnum}.".pdf'"); + + #accmonth,childuuid,invoicedate,invoiceamount,reference + my $sql = "insert into accounting (".join(',',@ins1).") VALUES (".join(',',@ins2).");"; + #print $sql."\n"; + my $r = $db->dbexec($sql); + if (($log ne "") && (-e $log)){ + if (!defined($r)) { + open(LOG,">>".$log); + print LOG localtime().":ERROR:".$sql."\n"; + close(LOG); + } else { + open(LOG,">>".$log); + print LOG localtime().":SUCCESS:".$sql."\n"; + close(LOG); + } + } + } + } + } + } + } + } +} \ No newline at end of file -- 2.39.5