use strict;
use warnings;
use DBD::Excel;
use DBI;
use Modern::Perl;
use Data::Dumper;
my %clients;
my $preMonthDB = DBI->connect("DBI:Excel:file=March_15.xls")
        or die "Cannot connect: " . $DBI::errstr;
my @preSheets = $preMonthDB->func('list_tables');
foreach my $preSheet (@preSheets)
{
    say "$preSheet";
    my $gender = $preMonthDB->prepare("SELECT sex,count(*) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY sex")
                or die "Couldn't prepare statement: " . $preMonthDB->errstr;
    $gender->execute()             # Execute the query
            or die "Couldn't execute statement: " . $gender->errstr;

    while (my @genders = $gender->fetchrow_array) {
        $clients{$preSheet}{"previous"}{$genders[0]} = $genders[1];
      }
    
    my $tests = $preMonthDB->prepare("SELECT type_of_test, count(*) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY type_of_test")
                or die "Couldn't prepare statement: " . $preMonthDB->errstr;
    $tests->execute()             # Execute the query
            or die "Couldn't execute statement: " . $tests->errstr;
    my $testRes =  $tests->rows;
    while (my @test = $tests->fetchrow_array) {
       $clients{$preSheet}{"previous"}{"Tests"}{$test[0]} = $test[1];
      }
    my $location = $preMonthDB->prepare("SELECT Location_Name, count(*) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY Location_Name")
                or die "Couldn't prepare statement: " . $preMonthDB->errstr;
    $location->execute()             # Execute the query
            or die "Couldn't execute statement: " . $location->errstr;
    my $locationRes =  $location->rows;
    say "No Of Test Types are " . $locationRes;
    while (my @locations = $location->fetchrow_array) {
        $clients{$preSheet}{"previous"}{"Locations"}{$locations[0]} = $locations[1];
      }
    my $newPatient = $preMonthDB->prepare("SELECT count(*) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' AND New_patient = 'YES'")
                or die "Couldn't prepare statement: " . $preMonthDB->errstr;
    $newPatient->execute()             # Execute the query
            or die "Couldn't execute statement: " . $newPatient->errstr;
    my ($newPatientRes) = $newPatient->fetchrow_array;
    $clients{$preSheet}{"previous"}{"new"} = $newPatientRes;
    
    my $totalPatients = $preMonthDB->prepare("SELECT count(*) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber'")
                or die "Couldn't prepare statement: " . $preMonthDB->errstr;
    $totalPatients->execute()             # Execute the query
            or die "Couldn't execute statement: " . $totalPatients->errstr;
    my ($totalPatientsRes) = $totalPatients->fetchrow_array;
    $clients{$preSheet}{"previous"}{"total"} = $totalPatientsRes;
    $clients{$preSheet}{"previous"}{"existing"} = $totalPatientsRes - $newPatientRes;
}
$preMonthDB->disconnect();
my $curMonthDB = DBI->connect("DBI:Excel:file=Appointments_April_15.xls")
        or die "Cannot connect: " . $DBI::errstr;
my @curSheets = $curMonthDB->func('list_tables');
foreach my $curSheet (@curSheets)
{
    my $gender = $curMonthDB->prepare("SELECT sex,count(*) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY sex")
                or die "Couldn't prepare statement: " . $curMonthDB->errstr;
    $gender->execute()             # Execute the query
            or die "Couldn't execute statement: " . $gender->errstr;

    while (my @genders = $gender->fetchrow_array) {
        $clients{$curSheet}{"current"}{$genders[0]} = $genders[1];
      }
    
    my $tests = $curMonthDB->prepare("SELECT type_of_test, count(*) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY type_of_test")
                or die "Couldn't prepare statement: " . $curMonthDB->errstr;
    $tests->execute()             # Execute the query
            or die "Couldn't execute statement: " . $tests->errstr;
    my $testRes =  $tests->rows;
    while (my @test = $tests->fetchrow_array) {
       $clients{$curSheet}{"current"}{"Tests"}{$test[0]} = $test[1];
      }
    my $location = $curMonthDB->prepare("SELECT Location_Name, count(*) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY Location_Name")
                or die "Couldn't prepare statement: " . $curMonthDB->errstr;
    $location->execute()             # Execute the query
            or die "Couldn't execute statement: " . $location->errstr;
    my $locationRes =  $location->rows;
    say "No Of Test Types are " . $locationRes;
    while (my @locations = $location->fetchrow_array) {
        $clients{$curSheet}{"current"}{"Locations"}{$locations[0]} = $locations[1];
      }
    my $newPatient = $curMonthDB->prepare("SELECT count(*) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' AND New_patient = 'YES'")
                or die "Couldn't prepare statement: " . $curMonthDB->errstr;
    $newPatient->execute()             # Execute the query
            or die "Couldn't execute statement: " . $newPatient->errstr;
    my ($newPatientRes) = $newPatient->fetchrow_array;
    $clients{$curSheet}{"current"}{"new"} = $newPatientRes;
    
    my $totalPatients = $curMonthDB->prepare("SELECT count(*) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber'")
                or die "Couldn't prepare statement: " . $curMonthDB->errstr;
    $totalPatients->execute()             # Execute the query
            or die "Couldn't execute statement: " . $totalPatients->errstr;
    my ($totalPatientsRes) = $totalPatients->fetchrow_array;
    $clients{$curSheet}{"current"}{"total"} = $totalPatientsRes;
    $clients{$curSheet}{"current"}{"existing"} = $totalPatientsRes - $newPatientRes;
}
$curMonthDB->disconnect();
print Dumper(%clients);