use strict;

my $db = "MyServer";
#my $user = MyServerStuff::odbc->{ $db } { user };
my $user = 'bob';
#my $pass = MyServerStuff::odbc->{ $db } { pass };
my $pass = 'bob';


my $dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1});

my @sql = (
[
"g_total",
<<SQL,
select distinct main.type,
       history.date_forwarded as data,
       COUNT(1) as 'PPending'
  from main,history
 where main.type='1'
       and history.date_forwarded
       between dateadd( day, -7, getdate()) and getdate()
 group by history.date_forwarded, main.type
SQL
], [
"c_g_total",
<<SQL,
select distinct main.type,
       history.date_forwarded as data,
       COUNT(1) as 'PPending'
  from main,history
 where main.type!='1'
       and history.date_forwarded
       between dateadd( day, -7, getdate()) and getdate()
 group by history.date_forwarded,main.type
SQL
], [
"total_pending",
<<SQL,
select status_now as data,
       COUNT(1) as 'Got Total'
  from main
 where status_now='submitted' and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_total_pending",
<<SQL,
select status_now, COUNT(1) as 'Got Total'
  from main
 where status_now='submitted'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"comp_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'All_Completed'
  from main
 where status_now='completed'and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_comp_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'All_Completed'
  from main
 where status_now='completed'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now";
SQL
], [
"delete_check",
<<SQL,
select delete_check as data,
       COUNT(1) as 'Total Deleted'
  from main
 where delete_check='yes'and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by delete_check";
SQL
], [
"c_delete_check",
<<SQL,
select delete_check as data,
       COUNT(1) as 'Total Deleted'
  from main
 where delete_check='yes'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by delete_check
SQL
], [
"total_pending_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'Total Pending'
  from main
 where status_now='forwarded' and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_total_pending_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'Total Pending'
  from main
 where status_now='forwarded' and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
       group by status_now
SQL
]
);

my %sth;
my $n = 0;

for my $sql (@sql) {
    $sth{$sql[0]} = $dbh->prepare($sql[1]);
}

my %data;

sub cleanup
{
    my $ret = shift;
    $ret =~ s/\s+$//g;
    return $ret;
}

for my $sthk (keys %sth) {
    my $sth = $sth{$sthk};

    eval {
        $sth->execute();
    };
    if ($@) {
        die $sth->errstr;
    }
    while (my $pointer = $sth->fetchrow_hashref) {
        my $data = cleanup($pointer->{'DATA'});
        push(@{$data{$sthk}}, $data);
    }
    $sth->finish;
}