#!/usr/bin/perl -w

use strict;
use DBI;

my $sec;
my $ix = 0;
my @rec;
my %list;
my %stuff;
my $memid;
##declare variables

open(INFILE, "input_file.csv") || die "Cannot open file!";
<INFILE>;
while(<INFILE>)
{
        $ix++;
        chomp;
        chop;
        my $rec = $_;
        #print "processing $rec\n";
        $sec = [split(/\;/,$_)];
        if ((scalar(@$sec) >0))
        {
                if (length($sec->[9]) >12)
                {
                        $memid = lc(substr($sec->[9],4,8));
                } else {
                        $memid = $sec->[9];
                        if ($memid eq '')
                        {
                                $memid = "FORCEMEMID";
                        }
                }
        $list{$memid}{$ix} = $sec;
        }
}


foreach my $lookup (sort keys %dbsrv)
{
        #print "Server: $dbsrv{$lookup}{'server'} Database: $dbsrv{$lookup}{'database'}\n";

        my $server = $dbsrv{$lookup}{'server'};

        my $db = $dbsrv{$lookup}{'database'};

        my $dbh = DBI->connect("dbi:Pg:dbname =$db;host=$server","username","password");

        if ($dbh)
        {
                foreach my $key (sort keys %list)
                {
                        my $hpt = $list{$key};
                #       print "$hpt\n";
                        my %rhsh = %$hpt;
                        foreach my $rpt (sort keys %rhsh)
                        {
                                #print "$rpt\n"; - Prints out the record number
                                my $ptrx = $rhsh{$rpt};
                                my @drec = @$ptrx;
                                my $phone = $drec[1];
                                #print "$phone\n";
                                my @dbrec = getRecord($phone,$dbh);
                                if (@dbrec)
                                {
                                        if (scalar(@dbrec) > 6 )
                                        {
                                                                                                for (my $ix=0; $ix < scalar(@dbrec); $ix++)
                                                {
                                                        if(! defined($dbrec[$ix])) {$dbrec[$ix] ='';}
                                                }
                                                print "$drec[0]|$drec[1]|$drec[2]|$drec[3]|$drec[4]|$drec[5]|$drec[6]|$drec[7]|$drec[8]|$drec[9]|";
                                                print "$drec[10]|$drec[11]|$drec[12]|$drec[13]|";
                                                print "$dbrec[0]|$dbrec[1]|$dbrec[2]|$dbrec[3]|$dbrec[4]|$dbrec[5]|$dbrec[6]|$dbrec[7]\n";
                                                #       delete $list{$key};
                                        }delete $list{$key};
                                }
                        }
                }
        }
}

print "Now print out records that could not be matched\n";
foreach my $key (keys %list)
{
        my $nref;
        my @nrec;
        my $apt;
        my $nphone;
        my $nptr;
        my %nlst;

        $nref = $list{$key};
        %nlst = %$nref;
        foreach my $nrecno (keys %nlst)
        {

                $nptr = $nlst{$nrecno};
                @nrec = @$nptr;
              
                print "$nrec[0]|$nrec[1]|$nrec[2]|$nrec[3]|$nrec[4]|$nrec[5]|$nrec[6]|$nrec[7]|$nrec[8]|$nrec[9]|";
                        print "$nrec[10]|$nrec[11]|$nrec[12]|$nrec[13]|$nrec[14]|Not Found\n";
                }
        }
}

close(INFILE);



sub findDbandServer()
{
                my $db;
                my $server;
                my $dbh = DBI->connect("dbi:Pg:dbname=#######;host=#######","#####","#######");
                my $pst = $dbh->prepare("Select dbname,server From table);
                $pst->execute;
                my %dbhash;
                my $ref;
                while (my @row = $pst->fetchrow_array())
                {
                   ($db, $server) = @row;
                   $dbhash{$db}{'database'} = $db;
                   $dbhash{$db}{'server'} = $server;
                }
                return %dbhash;

}
sub getRecord()
{
        my $btn = shift;
        my $dbh = shift;
#print "getRecord: Btn: $btn Dbh: $dbh\n";
        my %dbhash;
        my @row;
        my $pst = $dbh->prepare("select first,last, dest, address1, address2,day, night,round((cast(recur as numeric) / freq ),2) as revenue
                                        from table1 so join table2 cs on so.svc = cs.svc
                                                                 join table3 cp on cs.pkg = cp.pkg
                                                                 join table4 pp on pp.pkgpart = cp.pkgpart
                                                                 join table5 c on c.cust = cp.cust
                                                                 join table6 ci on ci.cust = c.cust
                                        Where so.value = '$btn'");


        $pst->execute;

        @row = $pst->fetchrow_array();
        return @row;
}