From d58d443adc23cbc15d6174c2b6a08dde00059a48 Mon Sep 17 00:00:00 2001 From: Metabox Date: Wed, 24 Apr 2019 17:17:44 +1000 Subject: [PATCH] Implemented export import to CSV. --- htdocs/cgi-bin/config.cgi | 243 ++++++++++++++++++++++++++++---------- 1 file changed, 180 insertions(+), 63 deletions(-) diff --git a/htdocs/cgi-bin/config.cgi b/htdocs/cgi-bin/config.cgi index 14b5b90..fd33ffb 100755 --- a/htdocs/cgi-bin/config.cgi +++ b/htdocs/cgi-bin/config.cgi @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/perl -w # # Programed in vim by: Will Budic # Open Source License -> https://choosealicense.com/licenses/isc/ @@ -10,6 +10,7 @@ use Switch; use CGI; use CGI::Session '-ip_match'; +use CGI::Carp qw ( fatalsToBrowser ); use DBI; use DateTime; @@ -26,12 +27,17 @@ our $SESSN_EXPR = '+30m'; our $RELEASE_VER = '1.3'; #END OF SETTINGS +#15mg data post limit +$CGI::POST_MAX = 1024 * 15000; + my $cgi = CGI->new; my $session = new CGI::Session("driver:File",$cgi, {Directory=>$LOG_PATH}); my $sid=$session->id(); my $dbname =$session->param('database'); my $userid =$session->param('alias'); my $password=$session->param('passw'); +my $sys = `uname -n`; +#my $acumululator=""; if(!$userid||!$dbname){ print $cgi->redirect("login_ctr.cgi?CGISESSID=$sid"); @@ -46,16 +52,24 @@ my $rv; my $dbs; my $today = DateTime->now; my $tz = $cgi->param('tz'); - -my $csvp = $cgi->param("csv"); -if($csvp && $csvp<3){ - exportLogToCSV($csvp); -} -elsif($csvp){ - exportCategoriesToCSV($csvp); -} - - +my $csvp = $cgi->param('csv'); + + switch ($csvp){ + case "1" {&exportLogToCSV} + case "2" {&exportLogToCSV} + case "3" {&exportCategoriesToCSV} + case "4" {&exportCategoriesToCSV} + } + + if($cgi->param('data_cat')){ + &importCatCSV; + }elsif($cgi->param('data_log')){ + &importLogCSV; + } + + + + ##################### &getConfiguration; ##################### @@ -79,7 +93,7 @@ my $status = "Ready for change!"; my $tbl = ' - + '; while(my @row = $dbs->fetchrow_array()) { @@ -88,7 +102,7 @@ my $tbl = '
* CATEGORIES CONFIGURATION *
IDCategoryDescription
IDCategoryDescription
- '; + '; } } @@ -99,21 +113,19 @@ my $frm = qq( - - + - + - +
'.$row[0].'
WARNING! - Removing and changing categories is permanent! Adding one must have unique ID.
- Blanking an category name will remove and seek change LOG records to Unspecified (id 1)!
+ Removing and changing categories is permanent!
Adding one must have unique ID.
+ Blanking an category name will remove and seek change LOG
records to Unspecified (id 1)!
Also ONLY the category Unspecified You can't REMOVE!
If changing here things? Make a backup! (copy existing db file)
-

); @@ -158,16 +170,39 @@ my $frmVars = qq( # #Page printout from here! # +my $prc_hdr = $PRC_WIDTH-2; print '
'; -print "

Log Configuration In -> $dbname

"; - print "\n
\n" . $frm ."\n

\n"; - print "\n
\n" . $frmVars."\n

\n"; - print "\n
\nSTATUS:" .$status. "\n

\n"; - print "
Back to Main Log

\n"; - print '
Export Log to CSV
'; - print '
View the Log in CSV Format.
'; - print '
Export Categories to CSV
'; - print '
View the Categories in CSV Format.
'; +print "
Log Configuration In -> $dbname
"; + print "\n
\n" . $frm ."\n
\n"; + print "\n
\n" . $frmVars."\n
\n"; + print "\n
\nSTATUS:" .$status. "\n
\n"; + print qq( +
Back to Main Log

\n + + + + \n + \n + \n + \n + \n + \n + \n + \n + \n + +

CSV File Format

\n + Import Categories:
+

\n + Import Log:
\n +

To Server -> $sys -> $dbname

+ + +
[Export Log to CSV]   + [View the Log in CSV Format]
\n +
[Export Categories to CSV]   + [View the Categories in CSV Format]
\n
+ ); print '
'; @@ -205,7 +240,7 @@ if ($change == 1){ $d->execute(); while(my @r = $d->fetchrow_array()) { - $s = "UPDATE LOG SET CAT_ID=1 WHERE rowid=".$r[0].";"; + $s = "UPDATE LOG SET ID_CAT=1 WHERE rowid=".$r[0].";"; $d = $db->prepare($s); $d->execute(); } @@ -307,12 +342,12 @@ sub changeSystemSettings{ $dbs->execute(); while (my @r=$dbs->fetchrow_array()){ my $var = $cgi->param('var'.$r[0]); - if($var){ + if(defined $var){ switch ($r[1]) { - case "REC_LIMIT" {$REC_LIMIT=$var; updConfSetting($r[0],$var);} - case "TIME_ZONE" {$TIME_ZONE=$var; updConfSetting($r[0],$var);} - case "PRC_WIDTH" {$PRC_WIDTH=$var; updConfSetting($r[0],$var);} - case "SESSN_EXPR"{$SESSN_EXPR=$var; updConfSetting($r[0],$var);} + case "REC_LIMIT" {$REC_LIMIT=$var; updConfSetting($r[0],$var)} + case "TIME_ZONE" {$TIME_ZONE=$var; updConfSetting($r[0],$var)} + case "PRC_WIDTH" {$PRC_WIDTH=$var; updConfSetting($r[0],$var)} + case "SESSN_EXPR"{$SESSN_EXPR=$var; updConfSetting($r[0],$var)} } } } @@ -335,24 +370,7 @@ sub updConfSetting{ } } -sub exportLogToCSVWorking{ - try{ - - my $csv = Text::CSV->new ( { binary => 1, strict => 1 } ); - $dbs = $db->prepare("SELECT * FROM LOG;"); - $dbs->execute(); - print $cgi->header(-charset=>"UTF-8" -type=>"text/html"); - print "ID_CAT,DATE,LOG,AMMOUNT\n"; - while (my $r=$dbs->fetchrow_arrayref()){ - print $csv->print(*STDOUT, $r)."\n"; - } - $db->disconnect(); - exit; - } - catch{ - print "SERVER ERROR->exportLogToCSV:".$_; - } -} + sub exportLogToCSV{ try{ @@ -371,11 +389,12 @@ sub exportLogToCSV{ print "ID_CAT,DATE,LOG,AMMOUNT\n"; while (my $r=$dbs->fetchrow_arrayref()){ - print $csv->print(*STDOUT, $r)."\n"; + print $csv->print(*STDOUT, $r); } - if($csvp && $csvp==1){ - print "\n\n"; + if($csvp==1){ + print ""; } + $dbs->finish(); $db->disconnect(); exit; } @@ -387,7 +406,7 @@ sub exportCategoriesToCSV{ try{ my $csv = Text::CSV->new ( { binary => 1, strict => 1 } ); - $dbs = $db->prepare("SELECT * FROM CAT;"); + $dbs = $db->prepare("SELECT ID, NAME, DESCRIPTION FROM CAT ORDER BY ID;"); $dbs->execute(); if($csvp==4){ @@ -398,13 +417,14 @@ sub exportCategoriesToCSV{ print $cgi->header(-charset=>"UTF-8", -type=>"application/octet-stream", -attachment=>"$dbname.categories.csv"); } - print "ID_CAT,DATE,LOG,AMMOUNT\n"; - while (my $r=$dbs->fetchrow_arrayref()){ - print $csv->print(*STDOUT, $r)."\n"; - } - if($csvp && $csvp==3){ - print "\n\n"; + #print "ID,NAME,DESCRIPTION\n"; + while (my $row=$dbs->fetchrow_arrayref()){ + print $csv->print(*STDOUT, $row).; } + if($csvp==4){ + print ""; + } + $dbs->finish(); $db->disconnect(); exit; } @@ -412,3 +432,100 @@ sub exportCategoriesToCSV{ print "SERVER ERROR->exportLogToCSV:".$_; } } + + +sub importCatCSV{ + my $hndl = $cgi->upload("data_cat"); + my $csv = Text::CSV->new ( { binary => 1, strict => 1 } ); + while (my $line = <$hndl>) { + chomp $line; + if ($csv->parse($line)) { + my @flds = $csv->fields(); + updateCATDB(@flds); + }else{ + warn "Data could not be parsed: $line\n"; + } + } +} + +sub updateCATDB{ + my @flds = @_; + if(@flds>2){ + try{ + my $id = $flds[0]; + my $name = $flds[1]; + my $desc = $flds[2]; + #$acumululator .= $id."-".$name; + + #is it existing entry? + $dbs = $db->prepare("SELECT ID, NAME, DESCRIPTION FROM CAT WHERE ID = '$id';"); + $dbs->execute(); + if(not defined $dbs->fetchrow_array()){ + $dbs = $db->prepare('INSERT INTO CAT VALUES (?,?,?)'); + $dbs->execute($id, $name, $desc); + $dbs->finish; + } + else{ + #TODO Update + } + + } + catch{ + print "SERVER ERROR->updateCATDB:".$_; + } + } +} +sub importLogCSV{ + my $hndl = $cgi->upload("data_log"); + my $csv = Text::CSV->new ( { binary => 1, strict => 1 } ); + while (my $line = <$hndl>) { + chomp $line; + if ($csv->parse($line)) { + my @flds = $csv->fields(); + updateLOGDB(@flds); + }else{ + warn "Data could not be parsed: $line\n"; + } + } + $db->disconnect(); + print $cgi->redirect('main.cgi'); + exit; +} +sub updateLOGDB{ + my @flds = @_; + if(@flds>3){ + try{ + my $id_cat = $flds[0]; + my $date = $flds[1]; + my $log = $flds[2]; + my $amm = $flds[3]; + my $pdate = DateTime::Format::SQLite->parse_datetime($date); + #Check if valid date log entry? + if($id_cat==0||$id_cat==""||!$pdate){ + return; + } + #is it existing entry? + $dbs = $db->prepare("SELECT ID_CAT, DATE, LOG, AMMOUNT FROM LOG WHERE date = '$date';"); + $dbs->execute(); + if(!$dbs->fetchrow_array()){ + $dbs = $db->prepare('INSERT INTO LOG VALUES (?,?,?,?)'); + $dbs->execute( $id_cat, $pdate, $log, $amm); + } + #Renumerate + $dbs = $db->prepare('select rowid from LOG ORDER BY DATE;'); + $dbs->execute(); + my @row = $dbs->fetchrow_array(); + my $cnt = 1; + while(my @row = $dbs->fetchrow_array()) { + my $st_upd = $db->prepare("UPDATE LOG SET rowid=".$cnt. + " WHERE rowid='".$row[0]."';"); + $st_upd->execute(); + $cnt = $cnt + 1; + } + $dbs->finish; + } + catch{ + print "SERVER ERROR->exportLogToCSV:".$_; + } + } +} -- 2.34.1