#!/usr/local/sysmansw/runtime/perl/bin/perl -w

#
## -_- $Name: CallTrack-1_3b $ % $Id: ctadmin,v 1.12 2001/09/05 17:48:04 sietse Exp $
#

my $CTDIR;

BEGIN
{
    # Default directory name is ~perfgr - changeable by defining the variable PERFDIR
    $CTDIR = $ENV{'CTDIR'};
    $CTDIR = $ENV{'HOME'} . "/calltrack" if not defined($CTDIR);

    push @INC, $CTDIR;
}

use DBI;
use dbconnect;
use strict;

my $dbh;

##########################################################################
# Make version 1.0 of the database
##########################################################################

sub mk_v1_0 ()
{
#    $dbh->{RaiseError} = 0;
#    $dbh->do("drop table ctdb_ver");
#    $dbh->do("drop table lic");
#    $dbh->do("drop table vendors");
#    $dbh->do("drop table vendoritems");
#    $dbh->do("drop table ct_users");
#    $dbh->do("drop table calls");
#    $dbh->do("drop table callhist");
#    $dbh->do("drop table callitem");
#    $dbh->do("drop table next_id");
#    $dbh->do("drop table logins");
#    $dbh->{RaiseError} = 1;

    $dbh->{AutoCommit} = 1;

    $dbh->do("create table ctdb_ver(version varchar(15))");
    $dbh->do("create table lic(lic char(32))");

    $dbh->do("create table vendors(
                 vendorid   int,
                 vendorname varchar(50),
                 telno      varchar(30),
                 faxno      varchar(30),
                 email      varchar(50),
                 address    varchar(40),
                 zipcode    varchar(10),
                 city       varchar(30),
                 state      varchar(30),
                 country    varchar(25),
                 descr      varchar(4000),
                 changed_by varchar(20),
                 changed_at int)
             ");

    $dbh->do("create table vendoritems (
                 vendorid   int,
                 itemid     int,
                 item       varchar(50),
                 contractnr varchar(50),
                 licensenr  varchar(50),
                 descr      varchar(4000),
                 changed_by varchar(20),
                 changed_at int)
             ");

    $dbh->do("create table ct_users (
                 userid     varchar(20),
                 password   varchar(20),
                 role       varchar(10))  -- either 'user' or 'admin'
             ");

    $dbh->do("create table calls (
                 callid     int,
                 priority   int,
                 status     int,
                 callnr     varchar(20),
                 problem    varchar(50),
                 logtime    int,
                 loggedby   varchar(30))
             ");

    $dbh->do("create table callhist (
                 callid      int,
                 updtime     int,
                 updby       varchar(30),
                 note        varchar(4000))
             ");

    $dbh->do("create table callitem (
                 vendorid   int,
                 itemid     int,
                 callid     int)
             ");

    $dbh->do("create table next_id (
                 vendorid   int,
                 itemid     int,
                 callid     int)
             ");

    $dbh->do("insert into ctdb_ver values('1.0')");

    $dbh->do("insert into vendors values(
                1,
                'Sysman Software',
                '',
                '',
                'support\@sysman.nl',
                '',
                '',
                '',
                '',
                '',
                'Sysman Software provides system management tools',
                'sietse',
                977860344)
             ");

    $dbh->do("insert into vendoritems values(
                1,
                1,
                'CallTrack',
                '',
                'a6b17cd',
                'CallTrack is a client side call tracking application',
                'sietse',
                977860394)
             ");

    $dbh->do("insert into ct_users values( 'admin', '', 'admin')");

    $dbh->do("insert into callitem values(1, 1, 1)");
    $dbh->do("insert into calls values(
                1,
                4,
                1,
                'ct-1223',
                'CallTrack enhancements',
                " . time . ",
                'sietse')
             ");

    $dbh->do("insert into callhist values(
              1,
              " . (time-100) . ",
              'sietse',
              '[i]New call.[/i]\nIf you have any suggestions for enhancements, please let us know!')
             ");

    $dbh->do("insert into next_id values(10, 10, 10)");

    $dbh->{AutoCommit} = 0;
}

sub mk_v1_1
{
    $dbh->{AutoCommit} = 1;
    $dbh->do("alter table vendors add (auto_inc int)");
    $dbh->do("alter table vendors add (nr_dig int)");
    $dbh->do("alter table vendors add (next_nr int)");
    $dbh->do("alter table vendors add (prefix varchar(15))");
    $dbh->do("update vendors set auto_inc=0, nr_dig=4, next_nr=1, prefix=''");
    $dbh->do("update ctdb_ver set version='1.1'");
    $dbh->{AutoCommit} = 0;
}

##########################################################################
##########################################################################
##########################################################################

sub empty_db
{
    $dbh->{AutoCommit} = 1;
    $dbh->do("delete from ctdb_ver");
    $dbh->do("delete from lic");
    $dbh->do("delete from vendors");
    $dbh->do("delete from vendoritems");
    $dbh->do("delete from ct_users");
    $dbh->do("delete from calls");
    $dbh->do("delete from callhist");
    $dbh->do("delete from callitem");
    $dbh->do("vacuum analyze") if dbconnect::dbsystem($dbh) eq "Pg";
    $dbh->{AutoCommit} = 0;
}

sub drop_indexes
{
    $dbh->{AutoCommit} = 1;
    $dbh->{RaiseError} = 0;
    $dbh->{PrintError} = 0;
    $dbh->do("drop index vendors_vendorid_idx");
    $dbh->do("drop index vendoritems_itemid_idx");
    $dbh->do("drop index ct_users_userid_idx");
    $dbh->do("drop index calls_callid_idx");
    $dbh->do("drop index callhist_callid_idx");
    $dbh->do("drop index callitem_vendorid_idx");
    $dbh->do("drop index callitem_itemid_idx");
    $dbh->do("drop index callitem_callid_idx");
    $dbh->{RaiseError} = 1;
    $dbh->{PrintError} = 1;
    $dbh->{AutoCommit} = 0;
}

sub create_indexes
{
    $dbh->{AutoCommit} = 1;
    $dbh->do("create index vendors_vendorid_idx on vendors(vendorid)");
    $dbh->do("create index vendoritems_itemid_idx on vendoritems(itemid)");
    $dbh->do("create index ct_users_userid_idx on ct_users(userid)");
    $dbh->do("create index calls_callid_idx on calls(callid)");
    $dbh->do("create index callhist_callid_idx on callhist(callid)");
    $dbh->do("create index callitem_vendorid_idx on callitem(vendorid)");
    $dbh->do("create index callitem_itemid_idx on callitem(itemid)");
    $dbh->do("create index callitem_callid_idx on callitem(callid)");
    $dbh->{AutoCommit} = 0;
}

##########################################################################
##########################################################################
##########################################################################

sub dump_db
{
    open OUT, "|compress > $CTDIR/dbdumps/calltrack.dump.Z"
         or die "Cannot compress / save to $CTDIR/dbdumps/calltrack.dump.Z: $!\n";

    my $dbver = $dbh->selectall_arrayref("select * from ctdb_ver");
    $dbver = $$dbver[0]; $dbver = $$dbver[0];
    print OUT "-_- ctdb_ver $dbver\n";
    
    my $lic = $dbh->selectall_arrayref("select * from lic");
    $lic = $$lic[0]; $lic = $$lic[0];
    print OUT "-_- lic $lic\n";
    
    my $next_id = $dbh->selectall_arrayref("select * from next_id");
    $next_id = $$next_id[0];
    print OUT "-_- next_id $$next_id[0] $$next_id[1] $$next_id[2]\n";
    
    my $ct_users = $dbh->selectall_arrayref("select * from ct_users");
    print OUT "-_- ct_users\n";
    foreach my $ct_user (@$ct_users)
    {
        print OUT "$$ct_user[0] $$ct_user[1] $$ct_user[2]\n";
    }
    
    my $sth_vendors = $dbh->prepare("select * from vendors");
    $sth_vendors->execute;
    while (my $vendor = $sth_vendors->fetchrow_arrayref)
    {
        print OUT "-_- vendors\n";
        for (my $i = 0; $i < scalar(@$vendor); $i++)
        {
            $$vendor[$i] = "" if not $$vendor[$i];
            print OUT "$$vendor[$i]\n";
            print OUT "-_- descr end -_-\n" if $sth_vendors->{NAME_lc}->[$i] eq "descr";
        }

        my $sth_vendoritems = $dbh->prepare("select * from vendoritems where vendorid=$$vendor[0]");
        $sth_vendoritems->execute;
        while (my $vendoritem = $sth_vendoritems->fetchrow_arrayref)
        {
            print OUT "-_- vendoritems\n";
            for (my $i = 0; $i < scalar(@$vendoritem); $i++)
            {
                $$vendoritem[$i] = "" if not $$vendoritem[$i];
                print OUT "$$vendoritem[$i]\n";
                print OUT "-_- descr end -_-\n" if $sth_vendoritems->{NAME_lc}->[$i] eq "descr";
            }
        }

        my $sth_callitem = $dbh->prepare("select * from callitem where vendorid=$$vendor[0]");
        $sth_callitem->execute;
        while (my $callitem = $sth_callitem->fetchrow_arrayref)
        {
            print OUT "-_- callitem\n";
            print OUT "$$callitem[0] $$callitem[1] $$callitem[2]\n";
        }

        my $sth_calls = $dbh->prepare("select distinct calls.callid,priority,status,callnr,problem,logtime,loggedby
                                       from calls,callitem where vendorid=$$vendor[0]");
        $sth_calls->execute;
        while (my $call = $sth_calls->fetchrow_arrayref)
        {
            print OUT "-_- calls\n";
            for (my $i = 0; $i < scalar(@$call); $i++)
            {
                $$call[$i] = "" if not $$call[$i];
                print OUT "$$call[$i]\n";
            }

            my $sth_callhist = $dbh->prepare("select * from callhist where callid=$$call[0]");
            $sth_callhist->execute;
            while (my $c_hist = $sth_callhist->fetchrow_arrayref)
            {
                print OUT "-_- callhist\n";
                for (my $i = 0; $i < scalar(@$c_hist); $i++)
                {
                    $$c_hist[$i] = "" if not $$c_hist[$i];
                    print OUT "$$c_hist[$i]\n";
                    print OUT "-_- descr end -_-\n" if $sth_callhist->{NAME_lc}->[$i] eq "note";
                }
            }
        }
    }
    close OUT;
    exit 0;
}

##########################################################################
##########################################################################
##########################################################################

sub restore_db
{
    my $line;

    open IN, "uncompress < $CTDIR/dbdumps/calltrack.dump.Z|"
         or die "Cannot compress / open $CTDIR/dbdumps/calltrack.dump.Z: $!\n";

    chomp(my $dbver = <IN>);
    if ($dbver !~ /^-_- ctdb_ver (.*)/)
    {
        die "This is not a CallTrack dump file!\n";
    }
    if ($1 ne "1.1")
    {
        die "The version of the dump file is $1; 1.1 is expected\n";
    }

    drop_indexes;
    empty_db;

    $dbh->do("insert into ctdb_ver values('$1')");

    chomp(my $lic = <IN>);
    $lic =~ /^-_- lic (.*)/;
    $dbh->do("insert into lic values('$1')");

    chomp(my $next_id = <IN>);
    $next_id =~ /^-_- next_id (.*)/;
    $next_id = $1;
    $next_id =~ s/ /,/g;
    $dbh->do("insert into next_id values($next_id)");

    chomp(my $ct_users = <IN>);
    if ($ct_users !~ /^-_- ct_users/)
    {
        die "Expected users\n";
    }
    while (($line = <IN>) !~ /^-_-/)
    {
        chomp $line;
        my ($user, $pwd, $role) = split(/ /, $line);
        $dbh->do("insert into ct_users values('$user', '$pwd', '$role')");
    }
    $dbh->commit;

    my $commit_ctr = 0;
    do
    {
        SWITCH:
        {
            if ($line =~ /^-_- vendors/)
            {
                chomp($line = <IN>);
                my $vendorid   = $line; chomp($line = <IN>);
                my $vendorname = $line; chomp($line = <IN>); $vendorname = $dbh->quote($vendorname);
                my $telno      = $line; chomp($line = <IN>); $telno = $dbh->quote($telno);
                my $faxno      = $line; chomp($line = <IN>); $faxno = $dbh->quote($faxno);
                my $email      = $line; chomp($line = <IN>); $email = $dbh->quote($email);
                my $address    = $line; chomp($line = <IN>); $address = $dbh->quote($address);
                my $zipcode    = $line; chomp($line = <IN>); $zipcode = $dbh->quote($zipcode);
                my $city       = $line; chomp($line = <IN>); $city = $dbh->quote($city);
                my $state      = $line; chomp($line = <IN>); $state = $dbh->quote($state);
                my $country    = $line; $country = $dbh->quote($country);
                my $descr      = "";
                while (($line = <IN>) !~ /^-_- descr end -_-/) { $descr .= $line }
                chomp $descr; chomp($line = <IN>); $descr = $dbh->quote($descr);
                my $changed_by = $line; chomp($line = <IN>);
                my $changed_at = $line; chomp($line = <IN>);
                my $auto_inc = $line; chomp($line = <IN>); $auto_inc = 0 if not $auto_inc;
                my $nr_dig = $line; chomp($line = <IN>);
                my $next_nr = $line; chomp($line = <IN>);
                my $prefix = $line;
                $dbh->do("insert into vendors values($vendorid,$vendorname,$telno,$faxno,$email,
                                                     $address,$zipcode,$city,$state,$country,$descr,
                                                     '$changed_by',$changed_at,$auto_inc,$nr_dig,
                                                     $next_nr,'$prefix')");
                $line = <IN>; chomp($line) if $line;
                last SWITCH;
            }
            if ($line =~ /^-_- vendoritems/)
            {
                chomp($line = <IN>);
                my $vendorid   = $line; chomp($line = <IN>);
                my $itemid     = $line; chomp($line = <IN>);
                my $item       = $line; chomp($line = <IN>); $item = $dbh->quote($item);
                my $contract   = $line; chomp($line = <IN>); $contract = $dbh->quote($contract);
                my $licensenr  = $line; $licensenr = $dbh->quote($licensenr);
                my $descr      = "";
                while (($line = <IN>) !~ /^-_- descr end -_-/) { $descr .= $line }
                chomp $descr; chomp($line = <IN>); $descr = $dbh->quote($descr);
                my $changed_by = $line; chomp($line = <IN>);
                my $changed_at = $line;
                $dbh->do("insert into vendoritems values($vendorid,$itemid,$item,$contract,$licensenr,
                                                         $descr, '$changed_by',$changed_at)");
                $line = <IN>; chomp($line) if $line;
                last SWITCH;
            }

            if ($line =~ /^-_- callitem/)
            {
                chomp($line = <IN>);
                my $callitem = $line;
                $callitem =~ s/ /,/g;
                $dbh->do("insert into callitem values($callitem)");
                $line = <IN>; chomp($line) if $line;
                last SWITCH;
            }

            if ($line =~ /^-_- calls/)
            {
                chomp($line = <IN>);
                my $callid     = $line; chomp($line = <IN>);
                my $priority   = $line; chomp($line = <IN>);
                my $status     = $line; chomp($line = <IN>);
                my $callnr     = $line; chomp($line = <IN>); $callnr = $dbh->quote($callnr);
                my $problem    = $line; chomp($line = <IN>); $problem = $dbh->quote($problem);
                my $logtime    = $line; chomp($line = <IN>);
                my $loggedby   = $line;
                $dbh->do("insert into calls values($callid,$priority,$status,$callnr,$problem,
                                                   $logtime,'$loggedby')");
                $line = <IN>; chomp($line) if $line;
                last SWITCH;
            }

            if ($line =~ /^-_- callhist/)
            {
                chomp($line = <IN>);
                my $callid     = $line; chomp($line = <IN>);
                my $updtime    = $line; chomp($line = <IN>);
                my $updby      = $line;
                my $descr      = "";
                while (($line = <IN>) !~ /^-_- descr end -_-/) { $descr .= $line }
                chomp $descr; $descr = $dbh->quote($descr);
                $dbh->do("insert into callhist values($callid,$updtime,'$updby',$descr)");
                $line = <IN>; chomp($line) if $line;
                last SWITCH;
            }

            chomp($line = <IN>);
        }
        if ($commit_ctr++ > 100)
        {
            $dbh->commit;
            $commit_ctr = 0;
        }
    } while ($line);
    $dbh->commit if $commit_ctr;

    create_indexes;

    close IN;
    exit 0;
}

##########################################################################
# Main
##########################################################################

my $dump_db = 0;
my $restore_db = 0;
my $license = 0;

if (scalar(@ARGV))
{
       if ($ARGV[0] eq "-dbdump")    { $dump_db = 1; }
    elsif ($ARGV[0] eq "-dbrestore") { $restore_db = 1; }
    elsif ($ARGV[0] eq "-license")   { $license = 1; }
     else { die "Unknown option $ARGV[0]\n"; }
}

if (not -f "$CTDIR/ctdb.conf")
{
    system("echo '#dbype,     dbname,     hostname,    userid,     password' > $CTDIR/ctdb.conf");
    system("echo 'PgLocal,    calltrac,   -,           -,          -' >> $CTDIR/ctdb.conf");

}

dbconnect::connect_ctdb(1);           # test database connection
$dbh = dbconnect::connect_ctdb(0);    # really connect

# get version of database, if any
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 0;
my $ver = $dbh->selectall_arrayref("select * from ctdb_ver");
$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 1;

$ver = $$ver[0];
if (not $ver)
{
    # First time use
    mk_v1_0;
    mk_v1_1;
    print "Database created.\n";
}
else
{
    $ver = $$ver[0];
    if ($ver eq "1.0")
    {
        print "Upgrading database... ";
        mk_v1_1;
        print "Done!\n\n";
    }
    else
    {
        if ($ver ne "1.1")
        {
            print "Illegal version of database: $ver\n";
            exit 1;
        }
    }
}

if ($license)
{
    my $lic = $ARGV[1]; $lic = "" if not $lic;
    my $ret = system("$CTDIR/ctstart -s $lic") >> 8;
    exit $ret
}

# Check license
my $ret = system("$CTDIR/ctstart -c") >> 8;
exit if $ret == 1;  # Problems with IP address
if ($ret)
{
    print "Enter license number: ";
    chomp(my $lic = <STDIN>);
    $ret = system("$CTDIR/ctstart -s $lic") >> 8;
    exit if $ret;
}

dump_db if $dump_db;
restore_db if $restore_db;
