From 90475307bb5095efda908c1c327e3d3238c41e6b Mon Sep 17 00:00:00 2001 From: Will Budicm Date: Sun, 30 Aug 2020 16:28:34 +1000 Subject: [PATCH] Export to CVS tested, finished. --- Current Development Check List.md | 8 ++-- htdocs/cgi-bin/data.cgi | 71 ++++++++++++++++++------------- htdocs/cgi-bin/wsrc/main.js | 17 ++++---- 3 files changed, 53 insertions(+), 43 deletions(-) diff --git a/Current Development Check List.md b/Current Development Check List.md index 667b564..4ac9fa5 100644 --- a/Current Development Check List.md +++ b/Current Development Check List.md @@ -8,15 +8,15 @@ This version is not compatible in data structure to prior versions. Data migrati ### v.2.0 SUN RC2 Encountered -* Export to CVS button on selected logs. -* Multiple search views and their settings, should be preserved as last preset. Maybe even have name multiple ones in a dropdown. -* Multiple category assignment to be enabled, where the first selected is the primary, others put in separate cross reference table. -* Sub users list with passwords in config to be provided, with access and category, permissions settings. Default enabled permission is Event view category. +* ✔ Export to CVS button on selected logs. * ✔ RTF based view of log entries. * ✔ Check and test cross release migration. * ✔ Minimise Log form when in View Mode. * ✔ Restore of backup on different version of LifeLog should fail with detecting this in provided error. * Provided Backup/Restore Specifications, and message that restoration has been invalidated as that is an invalid backup file. +* Multiple search views and their settings, should be preserved as last preset. Maybe even have name multiple ones in a dropdown. +* Multiple category assignment to be enabled, where the first selected is the primary, others put in a separate cross reference table, parseed as hashtags maybe. +* Sub users list with passwords in config to be provided, with access and category, permissions settings. Default enabled permission is Event view category. ### v.1.9 SUN RC1 Encountered diff --git a/htdocs/cgi-bin/data.cgi b/htdocs/cgi-bin/data.cgi index 8b4dec9..1d5a1be 100755 --- a/htdocs/cgi-bin/data.cgi +++ b/htdocs/cgi-bin/data.cgi @@ -24,9 +24,9 @@ use Text::CSV; use lib "system/modules"; require Settings; -my $cgi = CGI->new; +my $cgi = CGI->new; my $session = new CGI::Session("driver:File",$cgi, {Directory => Settings::logPath()}); -my $sid=$session->id(); +my $sid =$session->id(); my $dbname =$session->param('database'); my $userid =$session->param('alias'); my $password=$session->param('passw'); @@ -35,7 +35,11 @@ if(!$userid||!$dbname){ print $cgi->redirect("login_ctr.cgi?CGISESSID=$sid"); exit; } - +# $cgi->param('chk',(1,2)); +# $cgi->param('opr',2); +# $dbname = "data_admin_log.db"; +# $userid = "admin"; +# $password= "admin"; my $database = Settings::logPath().$dbname; my $dsn= "DBI:SQLite:dbname=$database"; @@ -188,25 +192,26 @@ try{ } sub NotConfirmed { - - my $stmS = "SELECT ID, PID, (select NAME from CAT WHERE ID_CAT == CAT.ID) as CAT, DATE, LOG from VW_LOG WHERE"; - $stmS = "SELECT rowid as ID, ID_CAT as IDCAT, DATE, LOG from LOG" if($opr == 2); - my $stmE = " ORDER BY DATE DESC, ID DESC;"; +try{ + my $stmS = "SELECT ID, PID, (select NAME from CAT WHERE ID_CAT == CAT.ID) as CAT, DATE, LOG from VW_LOG WHERE"; + my $stmE = " ORDER BY DATE DESC, ID DESC;"; + if($opr == 2){ + $stmS = "SELECT rowid as ID, ID_CAT as IDCAT, DATE, LOG, AMOUNT from LOG WHERE"; + $stmE = " ORDER BY date(DATE);"; + } #Get ids and build confirm table and check my $stm = $stmS ." "; - foreach my $id ($cgi->param('chk')){ - if($opr == 2){ - $stm = $stm . "rowid = " . $id . " OR "; - } - else{ - $stm = $stm . "PID = " . $id . " OR "; - } + foreach my $id ($cgi->param('chk')){ + if($opr == 2){ + $stm = $stm . "rowid == " . $id . " OR "; } - $stm =~ s/ OR $//; $stm .= $stmE; - - $st = $db->prepare( $stm ); - $rv = $st->execute(); + else{ + $stm = $stm . "PID == " . $id . " OR "; + } + } + $stm =~ s/ OR $//; $stm .= $stmE; + $st = Settings::selectRecords($db, $stm); if($opr == 0){ print $cgi->header(-expires=>"+6os"); print $cgi->start_html(-title => "Personal Log Record Removal", -BGCOLOR => $BGCOL, @@ -260,21 +265,27 @@ sub NotConfirmed { print '
' . $tbl .'
'; - }else{ - my $csv = Text::CSV->new ( { binary => 1, escape_char => "\\", eol => $/ } ); - print $cgi->header(-charset=>"UTF-8", -type=>"application/octet-stream", -attachment=>"$dbname.sel.csv"); - + } + elsif($opr == 2){ + + my $csv = Text::CSV-> new ( { binary => 1, escape_char => "\\", strict => 1, eol => $/ } ); + + my @columns = ("ID", "CAT", "DATE", "LOG", "AMOUNT"); + + print $cgi->header(-charset=>"UTF-8", -type=>"application/octet-stream", -attachment=>"$dbname"."_sel.csv"); + print $csv->print(*STDOUT, \@columns); while (my $row=$st->fetchrow_arrayref()){ - # $row[3] =~ s/\\\\n/\n/gs; - my $out = $csv->print(*STDOUT, $row); - print $out if(length $out>1); + # $row[3] =~ s/\\\\n/\n/gs; + my $out = $csv->print(*STDOUT, $row); + print $out if(length $out>1); } - $st->finish; - $db->disconnect(); - exit; + $st->finish; + $db->disconnect(); + exit; } - - +}catch{ + print "SERVER ERROR->exportLogToCSV:".$_; +} $st->finish; $db->disconnect(); } diff --git a/htdocs/cgi-bin/wsrc/main.js b/htdocs/cgi-bin/wsrc/main.js index ed7b656..7d0febf 100644 --- a/htdocs/cgi-bin/wsrc/main.js +++ b/htdocs/cgi-bin/wsrc/main.js @@ -931,10 +931,16 @@ function dateDiffSelected() { function exportSelected() { $("#opr").val(2); - return true; + } - +function exportToCSV(dat, view){ + var csv; + if(dat == 'cat'){ csv = view ? 4:3; } + else + if(dat == 'log'){ csv = view ? 2:1; } + window.location = "config.cgi?csv="+csv; +} function sumSelected() { var chks = document.getElementsByName("chk"); @@ -1138,13 +1144,6 @@ function deleteBackup() { }); } -function exportToCSV(dat, view){ - var csv; - if(dat == 'cat'){ csv = view ? 4:3; } - else - if(dat == 'log'){ csv = view ? 2:1; } - window.location = "config.cgi?csv="+csv; -} function setPageSessionTimer(expires) { -- 2.34.1