#!/usr/bin/perl

########## Pragmas ##########
use warnings;
use strict;
my($s);

$s="=" x 60;
print "$s\n";

$s="Data::Dumper";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }

$s="DBI";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }
$s="DBD::Pg";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }
$s="DBD::SQLite";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }

my ($USERNAME)=$ENV{USER};

# For time use $s=localtime();
########## Variables ##########
our(@lin);
my($DEBUGDETAIL,$MODVER);
my($filename,$SEP);
my($pcnt);
my(@a,@b,@k,@l,$d,$i,$j,$k,$lin,$t,$z);
my($page);
my($errfile,$fn,$histfn,$histtablename,$sql);
my($day,$mo,$hr,$min,$year,$sec);

# Database stuff
my($dbserver,$dbuser,$dbpw,$dbname,$dsn);
# SQLite handles
my($dbsql,$stsql,$rvsql,$ssql);

my(@errors,@row);

$SEP=chr(9); # chr(9)=tab.
$DEBUGDETAIL=0; # 1=show every record.

my $SUBVER=1; # Update this.
my $VER="ver 2014-1016.".sprintf("%03d",$SUBVER);


########## Begin main ##########

print "\n"; # print blank line to separate warning messages from our messages.
if (!(defined($ARGV[0])))
    {
    print "$VER Usage: \nperl $0 ok ";
    $k='  '; # Prefix spaces
    $s="\n";
    $s.=$k."Use 'ok' to run program.\n";
    $s.="\n";

    print "\n";
    exit;
    }

# Connect to DBD::SQLite.
$histfn='test.db';
$histtablename='histdata';
if (-e $histfn)
    {
    $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn",undef, undef, undef);
    if (! $dbsql)
        {
        $s="ERROR opening SQLite $histfn. Perhaps the file is new: $!";
        writeerr($s);
        exit 1;
        }
    }
else { # Create table
    $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn",undef, undef, undef); # Make new table
    if (! $dbsql)
        {
        $s="ERROR opening SQLite $histfn. Perhaps the file is new: $!";
        writeerr($s);
        exit 1;
        }
    $ssql = <<ENDSQL;
CREATE TABLE $histtablename (
ID serial PRIMARY KEY,
Mydate date,
Category text,
Subcat text,
Myvalue numeric DEFAULT 0,
Myformat text DEFAULT ''
)
;
ENDSQL

    $stsql= $dbsql->prepare($ssql);
    if (! $stsql)
        {
        $s="ERROR: prepping $histfn failed at dbsql->prepare(). ";
        $s.="\nSQL=$ssql";
        writeerr($s);
        exit 1;
        }
    $rvsql=$stsql->execute();
    if ($rvsql<0)
        {
        $s="ERROR: rvsql less than zero: $DBI::errstr";
        writeerr($s);
        exit 1;
        }
    elsif ($rvsql==0)
        {
        $s="SQLite: table $histtablename created in $histfn.";
        writeerr($s);
        }
    }

$s="SQLite version: ".$dbsql->{sqlite_version};
writeerr($s);

####################
# Now insert record.
doaddsqlite('2014-09-01','OPEN','SUBCAT',1,'main');
doaddsqlite('2014-09-02','OPENPR','SUBCAT',2,'main');

####################
# Now do select.
listhist('2014-09-01');

mainexit:

$stsql->finish();
$dbsql->disconnect();

exit; # Main program
##########################################################################
###########################################################################
###########################################################################
# Add record to sqlite db. First check if record exists. If it does,
# show err msg and do not add data.
# These fields required: $indate, $cat, $subcat, $myvalue.
# Optional parameters: $estmtrid, $dept.
# In: $indate: report date
#     $cat: category
#     $subcat: subcategory
#     $myvalue: could be a count, dollar amt, or a number.
#     $calledby: subroutine that called doaddsqlite().
# Out:
sub doaddsqlite
{my($indate,$cat,$subcat,$myvalue,$calledby)=@_;
my(@a,@b,$i,$j,$procname,$s,$t,$ssql);

$procname="doaddsqlite";

if (($indate) and (len($indate)==0))
    {
    $s="$procname ERROR: Date was not provided. Cannot update database.";
    writeerr($s);
    exit 1;
    }
if (($cat) and (len($cat)==0))
    {
    $s="$procname ERROR: Category was not provided. Cannot update database.";
    writeerr($s);
    exit 1;
    }
if (($subcat) and (len($subcat)==0))
    {
    $s="$procname ERROR: Subcategory was not provided. Cannot update database.";
    writeerr($s);
    exit 1;
    }
if (($myvalue) and (len($myvalue)==0))
    {
    $s="$procname ERROR: A value was not provided. Cannot update database.";
    writeerr($s);
    exit 1;
    }
$cat=uc($cat);
$subcat=uc($subcat);

##########################################
# First make sure this data record does not exist.
$ssql='';
$ssql="SELECT Mydate, Category, Subcat";
$ssql.=" FROM $histtablename";
$ssql.=" WHERE";
$ssql.=" (Mydate = '$indate')";
$ssql.=" AND (Category = '$cat')";
$ssql.=" AND (Subcat = '$subcat')";
$ssql.=";";

$stsql= $dbsql->prepare($ssql);
if (! $stsql)
    {
    $s="$procname: Checking for existing record, dbsql->prepare failed. SQL=$ssql";
    $s.=" Called by: $calledby";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute();
if ($rvsql<0)
    {
    $s="$procname ERROR: rvsql less than zero: $DBI::errstr";
    if (len($calledby)>0)
        {
        $s.=" Called by: $calledby";
        }
    writeerr($s);
    exit 1;
    }
elsif (($rvsql>=1) )
    {
    $s="$procname ERROR: This record exists. Could not overwrite data.";
    if (len($calledby)>0)
        {
        $s.=" Called by: $calledby";
        }
    writeerr($s);
    $stsql->finish();
    return;
    }

##########################################
# Now insert data.
$ssql="INSERT INTO $histtablename (Mydate, Category, Subcat ";
$ssql.=", Myvalue) VALUES ";
$ssql.="('$indate', '$cat', '$subcat'";
$ssql.=", $myvalue)";
$ssql.=";";

$stsql= $dbsql->prepare($ssql);
if (! $stsql)
    {
    $s="$procname: INSERT data, dbsql->prepare failed. SQL=$ssql";
    $s.=" Called by: $calledby";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute();
if ($rvsql<0)
    {
    $s="$procname ERROR: rvsql less than zero: $DBI::errstr";
    writeerr($s);
    exit 1;
    }
else
    {
    $s="$procname: Data added to history.  $indate, $cat, $subcat, $myvalue";
    writeerr($s);
    }


return; # doaddsqlite
}
###########################################################################
# In: indate (not used)
# Out:
# List all records in estimatehist.db. Run with -listhist
sub listhist
{my($indate)=@_;
my(@a,@b,$i,$j,$procname,$s,$t);
my($colspace,$rvsql,$thdr,@fields,@fmtarr,@row);

$procname="listhist";
$colspace='  ';

##########################################
# Select data and show all records.

@fields=('Mydate','Category','Subcat','Myvalue');
@fmtarr=('%-10s','%-10s','%-10s','%8s');
$thdr="\n";
for ($j=0; $j<=$#fields; $j++)
    {
    $thdr.=sprintf($fmtarr[$j],$fields[$j]).$colspace;
    } # for j

# ORDER BY Mydate, Category, Subcat

$sql = <<ENDSQL;
SELECT *
FROM $histtablename
;
ENDSQL

$stsql=$dbsql->prepare($sql);
if (! $stsql)
    {
    $s="$procname ERROR: Could not prepare sql. SQL=$sql";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute(); # Always returns 0E0.
$DEBUGDETAIL=0;
if ($DEBUGDETAIL==1)
    {
    writeerr($thdr);
    }
if ($rvsql<0)
    {
    $s="$procname ERROR: $DBI::errstr";
    writeerr($s);
    }
elsif ($rvsql==0)
    {
    $s="$procname: No rows returned.";
    writeerr($s);
    }
else {
    $s="$procname: $rvsql rows found.";
    writeerr($s);
    while(my @row = $stsql->fetchrow_array())
        {
        @row=convundefarr(\@row); # Convert undefs to blank.
        for ($j=0; $j<=$#fields; $j++)
            {
            $s.=sprintf($fmtarr[$j],trim($row[$j])).$colspace;
            } # for j
        writeerr($s);
        } # while
    }


return; # listhist
}
###########################################################################
# writeerr() normally writes errors to a file but for this example it
# prints to a screen.
sub writeerr
{my ($s)=@_;

print "$s\n";

return;
}
###########################################################################
sub len
{my($l)=@_;

return length($l);
}
###########################################################################
###########################################################################
###########################################################################