From f1f06214cde296d6a241fcb2cf87cb8ce9260943 Mon Sep 17 00:00:00 2001 From: wbudic Date: Wed, 11 Aug 2021 09:22:54 +1000 Subject: [PATCH] RTF renumeration fixed for PG. --- htdocs/cgi-bin/data.cgi | 10 +- htdocs/cgi-bin/main.cgi | 329 +++++++++++----------- htdocs/cgi-bin/system/modules/Settings.pm | 56 ++-- 3 files changed, 206 insertions(+), 189 deletions(-) diff --git a/htdocs/cgi-bin/data.cgi b/htdocs/cgi-bin/data.cgi index a594e67..648b53e 100755 --- a/htdocs/cgi-bin/data.cgi +++ b/htdocs/cgi-bin/data.cgi @@ -115,15 +115,17 @@ return "".$d->ymd()." ".$d->hms; sub DeletionConfirmed { try{ - my $SQLID = 'rowid'; $SQLID = 'ID' if (Settings::isProgressDB()); + my $SQLID = 'rowid'; $SQLID = 'ID' if Settings::isProgressDB(); my $st1 = $db->prepare("DELETE FROM LOG WHERE $SQLID = ?;"); my $st2 = $db->prepare("DELETE FROM NOTES WHERE LID = ?;"); #print $cgi->header(-expires=>"+6os"); foreach my $id ($cgi->param('chk')){ - #print $cgi->p("###[deleting:$id]") if(Settings::debug()); - $st1->execute($id) or die "

Error->"& $_ &"

"; - $st2->execute($id); + my $st = Settings::selectRecords($db, 'select RTF from LOG where '.$SQLID.'='.$id); + my @ra = $st->fetchrow_array(); + $st1->execute($id) or die "

Error->"& $_ &"

"; + $st2->execute($id) if $ra[0]; } + Settings::renumerate($db);#<- 2021-08-11 Added just in case renumeration. Above also chack now if log was flagged an RTF before deleting note entry. print $cgi->redirect('main.cgi'); }catch{ diff --git a/htdocs/cgi-bin/main.cgi b/htdocs/cgi-bin/main.cgi index 9bec9d6..fd78f38 100755 --- a/htdocs/cgi-bin/main.cgi +++ b/htdocs/cgi-bin/main.cgi @@ -9,6 +9,8 @@ use strict; use Exception::Class ('LifeLogException'); use Syntax::Keyword::Try; use DBI; +use DBD::Pg; +use DBD::Pg qw(:pg_types); use Date::Language; use Date::Parse; use Time::localtime; @@ -128,10 +130,10 @@ if($cgi->param('srch_reset') == 1){ if($prm_vc &&$prm_vc ne ""){ #TODO (2020-11-05) This is a subrotine candidate. It gets too complicated. should not have both $prm_vc and $prm_vc_lst; - $prm_xc =~ s/^0*//g;$prm_xc_lst=~ s/^\,$//g; - if(!$prm_vc_lst||$prm_vc_lst==0){#} && index($prm_xc, ',') > 0){ + $prm_xc =~ s/^0*//g;$prm_xc_lst=~ s/^\,$//g; + if(!$prm_vc_lst||$prm_vc_lst==0){#} && index($prm_xc, ',') > 0){ $prm_vc_lst = $prm_vc; - }else{ + }else{ my $f; my @vc_lst = split /\,/, $prm_vc_lst; @vc_lst = uniq(sort { $a <=> $b } @vc_lst); foreach my $n(@vc_lst){ @@ -141,7 +143,7 @@ if($prm_vc &&$prm_vc ne ""){ $prm_vc_lst .= ",$prm_vc"; } $prm_vc_lst=~ s/\,$//g;$prm_vc_lst=~ s/\,\,/\,/g; - } + } if ($cgi->param('sss_vc') eq 'on'){ @@ -149,8 +151,8 @@ if($prm_vc &&$prm_vc ne ""){ $sss->param('sss_vc_lst', $prm_xc_lst); } else{ - $sss->clear('sss_vc'); - $sss->clear('sss_vc_lst'); + $sss->clear('sss_vc'); + $sss->clear('sss_vc_lst'); } }else{ @@ -160,30 +162,28 @@ if($prm_vc &&$prm_vc ne ""){ if($prm_xc &&$prm_xc ne ""){ #TODO (2020-02-23) It gets too complicated. should not have both $prm_xc and $prm_xc_lst; - $prm_xc =~ s/^0*//g;$prm_xc_lst=~ s/^\,$//g; - if(!$prm_xc_lst||$prm_xc_lst==0){#} && index($prm_xc, ',') > 0){ - $prm_xc_lst = $prm_xc; - }else{ - my $f; - my @xc_lst = split /\,/, $prm_xc_lst; @xc_lst = uniq(sort { $a <=> $b } @xc_lst); - foreach my $n(@xc_lst){ - if($n == $prm_xc){ $f=1; last; } - } - if(!$f){#not found view was clicked changing category but not adding it to ex list. Let's add it to the list. - $prm_xc_lst .= ",$prm_xc"; - } - $prm_xc_lst=~ s/\,$//g;$prm_xc_lst=~ s/\,\,/\,/g; - } - - - if ($cgi->param('sss_xc') eq 'on'){ - $sss->param('sss_xc', $prm_xc); - $sss->param('sss_xc_lst', $prm_xc_lst); - } - else{ - $sss->clear('sss_xc'); - $sss->clear('sss_xc_lst'); - } + $prm_xc =~ s/^0*//g;$prm_xc_lst=~ s/^\,$//g; + if(!$prm_xc_lst||$prm_xc_lst==0){#} && index($prm_xc, ',') > 0){ + $prm_xc_lst = $prm_xc; + }else{ + my $f; + my @xc_lst = split /\,/, $prm_xc_lst; @xc_lst = uniq(sort { $a <=> $b } @xc_lst); + foreach my $n(@xc_lst){ + if($n == $prm_xc){ $f=1; last; } + } + if(!$f){#not found view was clicked changing category but not adding it to ex list. Let's add it to the list. + $prm_xc_lst .= ",$prm_xc"; + } + $prm_xc_lst=~ s/\,$//g;$prm_xc_lst=~ s/\,\,/\,/g; + } + if ($cgi->param('sss_xc') eq 'on'){ + $sss->param('sss_xc', $prm_xc); + $sss->param('sss_xc_lst', $prm_xc_lst); + } + else{ + $sss->clear('sss_xc'); + $sss->clear('sss_xc_lst'); + } }else{ $prm_xc = $sss->param('sss_xc'); @@ -198,7 +198,6 @@ if($cgi->param('sss_ord_cat') eq 'on'){ my @vc_lst = split /\,/, $prm_vc_lst; @vc_lst = uniq(sort { $a <=> $b } @vc_lst); my @xc_lst = split /\,/, $prm_xc_lst; @xc_lst = uniq(sort { $a <=> $b } @xc_lst); - $sss->flush(); #tag related framed sizing. @@ -319,54 +318,49 @@ qq(
$sqlVWL = $stmS . $stmE; } } - elsif ($prm_vc) { - - if(@vc_lst){ - foreach (@vc_lst){ - $stmS .= " ID_CAT=$_ OR"; - } - $sqlVWL = $stmS . $prm_aa; $sqlVWL =~ s/OR$//g; - $sqlVWL .= $stmE; - } - elsif ($stmD) { - $sqlVWL = $stmS . $prm_aa . $stmD . " AND ID_CAT=" . $prm_vc . $stmE; - } - else { - $sqlVWL = $stmS . $prm_aa . " ID_CAT=" . $prm_vc . $stmE; - } - - + elsif ($prm_vc) { + if(@vc_lst){ + foreach (@vc_lst){ + $stmS .= " ID_CAT=$_ OR"; + } + $sqlVWL = $stmS . $prm_aa; $sqlVWL =~ s/OR$//g; + $sqlVWL .= $stmE; + } + elsif ($stmD) { + $sqlVWL = $stmS . $prm_aa . $stmD . " AND ID_CAT=" . $prm_vc . $stmE; + } + else { + $sqlVWL = $stmS . $prm_aa . " ID_CAT=" . $prm_vc . $stmE; + } } else { - if($prm_xc>0){ - if(@xc_lst){ - my $ands = ""; - foreach (@xc_lst){ - $ands .= " ID_CAT!=$_ AND"; - } - - $ands =~ s/AND$//g; - $sqlVWL = $stmS .$prm_aa. $ands . $stmE; - } - else{ - $sqlVWL = $stmS . $prm_aa." ID_CAT!=$prm_xc;" . $stmE; + if($prm_xc>0){ + if(@xc_lst){ + my $ands = ""; + foreach (@xc_lst){ + $ands .= " ID_CAT!=$_ AND"; } + + $ands =~ s/AND$//g; + $sqlVWL = $stmS .$prm_aa. $ands . $stmE; } - if ($stmD) { - $sqlVWL = $stmS . $prm_aa.' '. $stmD . $stmE; - } - elsif($prm_aa){ - $prm_aa =~ s/AND$//g; - $sqlVWL = $stmS .$prm_aa.' '.$stmE; - } - elsif($prm_rtf){ - $stmS =~ s/AND$//g; - $sqlVWL = $stmS.$stmE; + else{ + $sqlVWL = $stmS . $prm_aa." ID_CAT!=$prm_xc;" . $stmE; } + } + if ($stmD) { + $sqlVWL = $stmS . $prm_aa.' '. $stmD . $stmE; + } + elsif($prm_aa){ + $prm_aa =~ s/AND$//g; + $sqlVWL = $stmS .$prm_aa.' '.$stmE; + } + elsif($prm_rtf){ + $stmS =~ s/AND$//g; + $sqlVWL = $stmS.$stmE; + } } - - ################### &processSubmit; ################### @@ -580,7 +574,6 @@ sub buildLog { $log =~ s/<+/$sub/o; $tagged = 1; } - while ( $log =~ /<', $idx ) - 7; @@ -661,9 +654,9 @@ sub buildLog { my $ssymb = "Edit"; my $ssid = $tfId; - if ($sticky){ - $ssymb = "Edit ✵"; - $ssid = $tfId + 2; + if($sticky){ + $ssymb = "Edit ✵"; + $ssid = $tfId + 2; } $log_output .= qq( @@ -833,8 +826,8 @@ $log_output .= qq(Income  - RTF Document - Sticky + RTF Attach + Sticky
  @@ -1037,8 +1030,8 @@ if($isPUBViewMode){$sideMenu=$frm=$srh=$tail=""}else{ toBuf (qq( $sideMenu
$frm
-
$srh
$quill +
$srh
$help
\n$log_output\n

$tail @@ -1065,95 +1058,95 @@ $db->disconnect(); undef($sss); exit; +sub castToBool { + my $v=shift; + if($v eq"1"||$v eq"on"){return 'true'}else{return 'false'} +} sub processSubmit { - my $date = $cgi->param('date'); - my $log = $cgi->param('log'); - my $cat = $cgi->param('ec'); - my $cnt =""; - my $am = $cgi->param('am'); - my $af = $cgi->param('amf'); - - my $edit_mode = $cgi->param('submit_is_edit'); - my $view_mode = $cgi->param('submit_is_view'); - my $view_all = $cgi->param('rs_all'); - my $rtf = $cgi->param('rtf'); - my $sticky = $cgi->param('sticky'); - my $stm; - my $SQLID = 'rowid'; - - ##TODO - if($rtf eq 'on'){$rtf = 1} else {$rtf = 0} - if($sticky eq 'on'){$sticky = 1} else {$sticky = 0} - if(!$am){$am=0} - if(Settings::isProgressDB()){$SQLID = 'ID'; $sticky = castToBool($sticky);} + my $date = $cgi->param('date'); + my $log = $cgi->param('log'); + my $cat = $cgi->param('ec'); + my $cnt =""; + my $am = $cgi->param('am'); + my $af = $cgi->param('amf'); + + my $edit_mode = $cgi->param('submit_is_edit'); + my $view_mode = $cgi->param('submit_is_view'); + my $view_all = $cgi->param('rs_all'); + my $rtf = $cgi->param('rtf'); + my $sticky = $cgi->param('sticky'); + my $stm; + my $SQLID = 'rowid'; + + if($rtf eq 'on'){$rtf = 1} else {$rtf = 0} + if($sticky eq 'on'){$sticky = 1} else {$sticky = 0} + if(!$am){$am=0} + if(Settings::isProgressDB()){$SQLID = 'ID'; $sticky = castToBool($sticky);} try { #Apostroph's need to be replaced with doubles and white space to be fixed for the SQL. - $log =~ s/'/''/g; - - if ( $edit_mode && $edit_mode != "0" ) { - $date = DateTime::Format::SQLite->parse_datetime($date); $date =~ s/T/ /g; - $stm = qq(UPDATE LOG SET ID_CAT='$cat', RTF='$rtf', - DATE='$date', - LOG='$log', - AMOUNT=$am, - AFLAG = $af, - STICKY=$sticky WHERE $SQLID=$edit_mode;); - # - toBuf $stm if $DEBUG; - # - - traceDBExe($stm); - return; - } - - if ( $view_mode == "1" ) { - - if ($rs_cur) { - my $sand = ""; - if ( $rs_cur == $rs_prev ) - { #Mid page back button if id ordinal. - $rs_cur += $rec_limit; - $rs_prev = $rs_cur; - $rs_page--; - } - else { - $rs_page++; - } + $log =~ s/'/''/g; + + if ( $edit_mode && $edit_mode != "0" ) { + $date = DateTime::Format::SQLite->parse_datetime($date); $date =~ s/T/ /g; + $stm = qq(UPDATE LOG SET ID_CAT='$cat', RTF='$rtf', + DATE='$date', + LOG='$log', + AMOUNT=$am, + AFLAG = $af, + STICKY=$sticky WHERE $SQLID=$edit_mode;); + # + toBuf $stm if $DEBUG; + # + + traceDBExe($stm); + return; + } - if($prm_vc){ - $sand = "and ID_CAT == $prm_vc"; - } - elsif($prm_xc){ + if ( $view_mode == "1" ) { - if(@xc_lst){ - foreach (@xc_lst){ - $sand .= "and ID_CAT!=$_ "; - } - } - else{ $sand = "and ID_CAT != $prm_xc"; } + if ($rs_cur) { + my $sand = ""; + if($rs_cur == $rs_prev) + { #Mid page back button if id ordinal. + $rs_cur += $rec_limit; + $rs_prev = $rs_cur; + $rs_page--; + } + else { + $rs_page++; + } + if($prm_vc){ + $sand = "and ID_CAT == $prm_vc"; + } + elsif($prm_xc){ + if(@xc_lst){ + foreach (@xc_lst){ + $sand .= "and ID_CAT!=$_ "; + } } + else{ $sand = "and ID_CAT != $prm_xc"; } - $sqlVWL = qq($stmS PID<=$rs_cur and STICKY=false $sand $stmE); - return; } + $sqlVWL = qq($stmS PID<=$rs_cur and STICKY=false $sand $stmE); + return; } + } if ( $log && $date && $cat ) { - - # - # After Insert renumeration check # + # After Insert renumeration check + # my $dt = DateTime::Format::SQLite->parse_datetime($date); my $dtCur = DateTime->now(); - $dtCur->set_time_zone(&Settings::timezone); - $dtCur = $dtCur - DateTime::Duration->new( days => 1 ); - - #check for double entry + $dtCur->set_time_zone(&Settings::timezone); + $dtCur = $dtCur - DateTime::Duration->new( days => 1 ); + # + # check and prevent double entry # - $date = DateTime::Format::SQLite->parse_datetime($date); + $date = $dt; $stm = qq(SELECT DATE,LOG FROM LOG where DATE='$date' AND LOG='$log';); my $st = traceDBExe($stm); if ($st->fetchrow_array() ) { @@ -1163,28 +1156,45 @@ try { $sticky=castToBool($sticky); $stm = qq(INSERT INTO LOG (ID_CAT, DATE, LOG, RTF, AMOUNT, AFLAG, STICKY) VALUES ($cat,'$date','$log',$rtf, $am,$af,$sticky);); $st = traceDBExe($stm); + # my $rv = $db->last_insert_id(undef, undef, "log", undef); + # toBuf "\n[".$rv."]"; + $st->finish(); if($sssCDB){ #Allow further new database creation, it is not an login infinite db creation attack. $sss->param("cdb", 0); } if($rtf){ #Update 0 ground NOTES entry to the just inserted log. - - $st = traceDBExe('SELECT ID FROM '.Settings->VW_LOG.' LIMIT 1;'); - my @lid = $st->fetchrow_array(); + if ($dtCur > $dt){#New entry is set in the past. And wtf; has RTF attached. + if(Settings::isProgressDB()){$stm = "SELECT ID FROM LOG WHERE date = timestamp '$date';"} + else{$stm = 'SELECT ID FROM '.Settings->VW_LOG." WHERE datetime(date) = datetime('$date');"} + $st = traceDBExe($stm); + }else{ + $st = traceDBExe('SELECT ID FROM '.Settings->VW_LOG.' LIMIT 1;'); + } + my @lids = $st->fetchrow_array(); $st = traceDBExe('SELECT DOC FROM NOTES WHERE LID = 0;'); my @gzero = $st->fetchrow_array(); - if(scalar @lid > 0){ + if(scalar @lids > 0){ #By Notes.LID constraint, there should NOT be an already existing log rowid entry just submitted in the Notes table! #What happened? We must check and delete, regardles. As data is renumerated and shuffled from perl in database. :( - $st = traceDBExe("SELECT LID FROM NOTES WHERE LID=".$lid[0].";"); + $st = traceDBExe("SELECT LID FROM NOTES WHERE LID=".$lids[0].";"); if($st->fetchrow_array()){ - $st = $db->do("DELETE FROM NOTES WHERE LID=".$lid[0].";"); - toBuf qq(

Warning deleted (possible old) NOTES.LID[$lid[0]] -> lid:@lid

); + $st = $db->do("DELETE FROM NOTES WHERE LID=".$lids[0].";"); + # NOTICE - There will be disparities here if renumeration failed, to update, run. + # These are expected after upgrades. And if switching DB engine and SQL compatibilities. + toBuf qq(

Warning deleted (possible old) NOTES.LID[$lids[0]] -> lid:@lids

); } + toBuf("\nINSERT INTO NOTES($lids[0], {DOC[$date]})") if ($DEBUG); $st = $db->prepare("INSERT INTO NOTES(LID, DOC) VALUES (?, ?);"); - $st->execute($lid[0], $gzero[0]); + if(Settings::isProgressDB()){ + $st->bind_param(1, $lids[0]); + $st->bind_param(2, $gzero[0],{ pg_type => DBD::Pg::PG_BYTEA }); + $st->execute(); + }else{ + $st->execute($lids[0], $gzero[0]); + } #Flatten ground zero - $st = $db->prepare("UPDATE NOTES SET DOC='' WHERE LID=0;"); + $st = $db->prepare("UPDATE NOTES SET DOC=null WHERE LID=0;"); $st->execute(); } } @@ -1551,6 +1561,5 @@ sub outputPage { print $cgi->end_html; } -sub castToBool {if(shift){return 'true'}else{return 'false'}} 1; \ No newline at end of file diff --git a/htdocs/cgi-bin/system/modules/Settings.pm b/htdocs/cgi-bin/system/modules/Settings.pm index 1d1e703..88b312a 100644 --- a/htdocs/cgi-bin/system/modules/Settings.pm +++ b/htdocs/cgi-bin/system/modules/Settings.pm @@ -498,41 +498,47 @@ sub getTheme { #From v.1.8 Changed sub renumerate { my $db = shift; + my $CI = 'rowid'; $CI = 'ID' if $IS_PG_DB; #Renumerate Log! Copy into temp. table. my $sql; - selectRecords($db,'CREATE TABLE life_log_temp_table AS SELECT * FROM LOG;'); - my $CI = 'rowid'; $CI = 'ID' if $IS_PG_DB; - #update notes table with new log id only for reference sake. - my $st = selectRecords($db, "SELECT $CI, DATE FROM LOG WHERE RTF > 0 ORDER BY DATE;"); - while(my @row =$st->fetchrow_array()) { - my $sql_date = $row[1]; - #$sql_date =~ s/T/ /; - $sql_date = DateTime::Format::SQLite->parse_datetime($sql_date); - $sql = "SELECT $CI, DATE FROM life_log_temp_table WHERE RTF > 0 AND DATE = '".$sql_date."';"; + + $db->do("CREATE TABLE life_log_temp_table AS SELECT * FROM LOG order by $CI;"); + # Delete any possible orphaned Notes records. + my $st = selectRecords($db, "SELECT LID, LOG.$CI from NOTES LEFT JOIN LOG ON NOTES.LID = LOG.$CI WHERE LOG.$CI is NULL;"); + while(my @row=$st->fetchrow_array()) { + $db->do("DELETE FROM NOTES WHERE LID=".$row[0].";") + } + $st->finish(); + + if($IS_PG_DB){$db->do('DROP TABLE LOG CASCADE;')}else{$db->do('DROP TABLE LOG;')} + + $db->do(&createLOGStmt); + $db->do('INSERT INTO LOG (ID_CAT, DATE, LOG, RTF ,AMOUNT, AFLAG, STICKY) + SELECT ID_CAT, DATE, LOG, RTF, AMOUNT, AFLAG, STICKY FROM life_log_temp_table ORDER by DATE;'); + + #Update notes table with date ordered log id for reference sake. + $st = selectRecords($db, "SELECT $CI, DATE FROM life_log_temp_table WHERE RTF > 0 ORDER BY DATE;"); + while(my @row=$st->fetchrow_array()) { + my $ID_OLD = $row[0]; + my $sql_date = $row[1]; #$sql_date =~ s/T/ /; + # if(!$IS_PG_DB){ + # $sql_date = DateTime::Format::SQLite->parse_datetime($sql_date); + # } + $sql = "SELECT $CI DATE FROM LOG WHERE RTF > 0 AND DATE = '".$sql_date."';"; my @new = selectRecords($db, $sql)->fetchrow_array(); - if(scalar @new > 0){ + if(scalar @new > 0 && $new[0] ne $ID_OLD){ try{#can fail here, for various reasons. - $sql="UPDATE NOTES SET LID =". $new[0]." WHERE LID=".$row[0].";"; + $sql="UPDATE NOTES SET LID =". $new[0]." WHERE LID=". $ID_OLD .";"; $db->do($sql); } catch{ SettingsException->throw(error=>"\@Settings::renumerate Database error encountered. sql->$sql", show_trace=>$DEBUG); }; - } - } - - # Delete any possible orphaned Notes records. - $st->finish(); - $st = selectRecords($db, "SELECT LID, LOG.$CI from NOTES LEFT JOIN LOG ON NOTES.LID = LOG.$CI WHERE LOG.$CI is NULL;"); - while($st->fetchrow_array()) { - $db->do("DELETE FROM NOTES WHERE LID=".$_[0].";") - } + } + } $st->finish(); - if($IS_PG_DB){$db->do('DROP TABLE LOG CASCADE;');}else{$db->do('DROP TABLE LOG;');} - - $db->do(&createLOGStmt); - $db->do('INSERT INTO LOG (ID_CAT, DATE, LOG, RTF ,AMOUNT, AFLAG, STICKY) - SELECT ID_CAT, DATE, LOG, RTF, AMOUNT, AFLAG, STICKY FROM life_log_temp_table ORDER by DATE;'); + + $db->do('DROP TABLE life_log_temp_table;'); } -- 2.34.1