From 744431c5d76fdcb6b10e3091a7c69c21ee0b5fc4 Mon Sep 17 00:00:00 2001 From: Will Budicm Date: Mon, 26 Oct 2020 05:51:50 +1100 Subject: [PATCH] Finished PostgresSQL integration. --- Installation_ProgresSQL.txt | 2 +- htdocs/cgi-bin/config.cgi | 25 +++++++------ htdocs/cgi-bin/stats.cgi | 45 +++++++++++++---------- htdocs/cgi-bin/system/modules/Settings.pm | 10 +++++ 4 files changed, 50 insertions(+), 32 deletions(-) diff --git a/Installation_ProgresSQL.txt b/Installation_ProgresSQL.txt index 9fabbdc..6f30ad5 100644 --- a/Installation_ProgresSQL.txt +++ b/Installation_ProgresSQL.txt @@ -13,7 +13,7 @@ sudo apt install libpq-dev sudo cpan DBD::Pg; -# To assign default postgres user and db +# To assign default postgresSQL user and db sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';" sudo -u postgres psql -c "CREATE DATABASE testdb;" diff --git a/htdocs/cgi-bin/config.cgi b/htdocs/cgi-bin/config.cgi index 3d3c552..fa9c39c 100755 --- a/htdocs/cgi-bin/config.cgi +++ b/htdocs/cgi-bin/config.cgi @@ -30,12 +30,12 @@ require Settings; #15mg data post limit $CGI::POST_MAX = 1024 * 15000; my ($LOGOUT,$ERROR) = (0,""); -my $cgi = CGI->new; -my $session = new CGI::Session("driver:File", $cgi, {Directory=>&Settings::logPath}); -my $sid=$session->id(); -my $dbname =$session->param('database'); -my $userid =$session->param('alias'); -my $pass =$session->param('passw'); +my $cgi = CGI->new; +my $sss = new CGI::Session("driver:File", $cgi, {Directory=>&Settings::logPath}); +my $sid = $sss->id(); +my $dbname = $sss->param('database'); +my $userid = $sss->param('alias'); +my $pass = $sss->param('passw'); my $sys = `uname -n`; #my $acumululator=""; @@ -44,9 +44,10 @@ if(!$userid||!$dbname){ exit; } -my $db = Settings::connectDB($userid, $pass); - +Settings::dbSrc( $sss->param('db_source')); +Settings::dbFile($sss->param('database')); ### Fetch settings + my $db = Settings::connectDB($userid, $pass); Settings::getConfiguration($db); Settings::getTheme(); ### @@ -78,8 +79,8 @@ my %hshCats = {}; &processSubmit; ############### Settings::getTheme(); -$session->param("theme",&Settings::css); -$session->param("bgcolor",&Settings::bgcol); +$sss->param("theme", &Settings::css); +$sss->param("bgcolor", &Settings::bgcol); &getHeader; @@ -974,8 +975,8 @@ try{ } sub logout { - $session->delete(); - $session->flush(); + $sss->delete(); + $sss->flush(); print $cgi->redirect("login_ctr.cgi"); exit; } diff --git a/htdocs/cgi-bin/stats.cgi b/htdocs/cgi-bin/stats.cgi index ab611b7..5ea154e 100755 --- a/htdocs/cgi-bin/stats.cgi +++ b/htdocs/cgi-bin/stats.cgi @@ -22,28 +22,35 @@ use lib "system/modules"; use lib $ENV{'PWD'}.'/htdocs/cgi-bin/system/modules'; require Settings; -my $cgi = CGI->new(); -my $session = new CGI::Session("driver:File",$cgi, {Directory=>&Settings::logPath}); -my $sid=$session->id(); -my $dbname =$session->param('database'); -my $userid =$session->param('alias'); -my $password=$session->param('passw'); +my $cgi = CGI->new(); +my $sss = new CGI::Session("driver:File",$cgi, {Directory=>&Settings::logPath}); +my $sid = $sss->id(); +my $dbname = $sss->param('database'); +my $userid = $sss->param('alias'); +my $pass = $sss->param('passw'); +my $db; if(!$userid||!$dbname){ if (Settings::debug()){ $userid ="admin"; $dbname = "data_admin_log.db"; - $password = "admin"; + $pass = "admin"; } else{ - print $cgi->redirect("login_ctr.cgi?CGISESSID=$sid"); - exit; + print $cgi->redirect("login_ctr.cgi?CGISESSID=$sid"); + exit; } } -my $db = ""; - try{ -$db = Settings::connectDB($userid, $password); + +Settings::dbSrc( $sss->param('db_source')); +Settings::dbFile($sss->param('database')); +### Fetch settings + $db = Settings::connectDB($userid, $pass); + Settings::getConfiguration($db); + Settings::getTheme(); +### + my @stat = stat Settings::dbFile(); Settings::getConfiguration($db); Settings::getTheme(); @@ -90,20 +97,20 @@ print $cgi->start_html(-title => "Log Data Stats", -BGCOLOR=>Settings::bgcol(), -onload => "onBodyLoadGeneric()" ); -my $log_rc = selectSQL('select count(rowid) from LOG;'); -my ($stm1,$stm2) = "SELECT count(date) from LOG where date>=date('now','start of year');"; +my $log_rc = selectSQL(Settings::selLogIDCount()); +my ($stm1,$stm2) = "SELECT count(date) from LOG where ".Settings::selStartOfYear(); my $log_this_year_rc = selectSQL($stm1); my $notes_rc = selectSQL('select count(LID) from NOTES where DOC is not null;'); my $id; #INCOME -$stm1 = 'SELECT sum(AMOUNT) from LOG where date>=date("now","start of year") AND AFLAG = 1;'; +$stm1 = 'SELECT sum(AMOUNT) from LOG where '.Settings::selStartOfYear().' AND AFLAG = 1;'; #EXPENSE -$stm2 = 'SELECT sum(AMOUNT) from LOG where date>=date("now","start of year") AND AFLAG = 2;'; +$stm2 = 'SELECT sum(AMOUNT) from LOG where '.Settings::selStartOfYear().' AND AFLAG = 2;'; -my $income = selectSQL($stm1); -my $expense= selectSQL($stm2); +my $income = selectSQL($stm1); +my $expense = selectSQL($stm2); my $gross = big_money($income - $expense); $expense = big_money(sprintf("%.2f",$expense)); $income = big_money(sprintf("%.2f",$income)); @@ -111,7 +118,7 @@ $income = big_money(sprintf("%.2f",$income)); #Under perlbrew, sometimes STDOUT is not piped back to our cgi, #utility inxi could be a perl written version on newer systems. #So I use the inter processing module here for inxi. -- @wbudic -my $buff = ""; +my $buff = ""; my @cmd = ("/usr/bin/inxi", "-b", "-c0"); my $uptime = `uptime -p`; my @ht = split(m/\s/,`hostname -I`); diff --git a/htdocs/cgi-bin/system/modules/Settings.pm b/htdocs/cgi-bin/system/modules/Settings.pm index fd4dd48..9ecae9d 100644 --- a/htdocs/cgi-bin/system/modules/Settings.pm +++ b/htdocs/cgi-bin/system/modules/Settings.pm @@ -147,6 +147,16 @@ if($IS_PG_DB){ ); )} +sub selLogIDCount { + if($IS_PG_DB){return 'select count(ID) from LOG;'} + return 'select count(rowid) from LOG;' +} + +sub selStartOfYear { + if($IS_PG_DB){return "date>= date_trunc('year', now());"} + return "date>=date('now','start of year')" +} + sub createVW_LOGStmt { if($IS_PG_DB){ return qq( -- 2.34.1