#!/usr/bin/perl

use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);

use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Tb_Record qw(
    
    tb_itemType_getList
);

use Opals::Constant;

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'txtbk/report/statReport.tmpl',
        reqPermission   => 'tb_report',
    }
);
    my $pref = tmpl_preference($dbh);
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $dateFrom    = $input->{'dateFrom'};
    my $dateTo      = $input->{'dateTo'};
    my $dateRange   = $input->{'dateRangeOpt'};

    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    my $dateFirst = $pref->{'dateFirst'};
    my $dateLast  = $pref->{'dateLast'};
    $dateFrom = $dateFirst if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateLast if ( !$dateTo );
    $dateTo .= " 23:59:59";
    if($dateRange eq 'all'){
        $template->param(
            dateFrom     => substr($dateFrom, 0, 10),
            dateTo       => substr($dateTo, 0, 10),
            rangeAll     => 1,
            dateRangeOpt => "all",
       );
    }
    elsif($dateRange eq 'rangeSel'){
        $template->param(
            dateFrom     => substr($dateFrom, 0, 10),
            dateTo       => substr($dateTo, 0, 10),
            rangeSel     => 1,
            dateRangeOpt =>"rangeSel"
       );
    }
    else{
        $template->param(
            dateFrom     => substr($dateFrom, 0, 10),
            dateTo       => substr($dateTo, 0, 10),
            rangeFYear   => 1,
            dateRangeOpt =>"fYear"
       );
    }
    getStatReport($dbh, $template, $dateFrom, $dateTo);
    getStatReportByItemType($dbh, $template, $dateFrom, $dateTo);

#------------------------------------------------------------------------------
sub getStatReport{
    
    my ($dbh, $template, $dateFirst, $dateLast) = @_;
    my @reportTotal=();
    my @statReportList = ();
    my ($totRecsDateFirst, $totItemsDateFirst)= (0,0);
    my ($totRecsAdded ,$totItemsAdded  ) = (0,0);
    my ($totRecsDeleted ,$totItemsDeleted) = (0,0);
    my $totItemsFound = 0;
    my ($totRecsDateLast, $totItemsDateLast )= (0,0);

    $totRecsDateFirst = getTotalRecordsToDate($dbh, $dateFirst);
    $totRecsDateLast  = getTotalRecordsToDate($dbh, $dateLast);
    $totRecsAdded     = getTotalRecordsAdded($dbh, $dateFirst, $dateLast);
    $totItemsAdded    = getTotalItemsAdded($dbh, $dateFirst, $dateLast);
    $totRecsDeleted   = getTotalRecordsDeleted($dbh, $dateFirst, $dateLast);
    $totItemsDeleted  = getTotalItemsLostDeleted($dbh, $dateFirst, $dateLast);
    $totItemsFound    = getTotalItemsFound($dbh, $dateFirst, $dateLast);
      
    my $sql = "select distinct rid from tb_records inner join tb_items using(rid)";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my ($rid) = $sth->fetchrow_array()){
        $totItemsDateFirst += getTotalItemsToDate($dbh, $rid, $dateFirst);
        $totItemsDateLast  += getTotalItemsToDate($dbh, $rid, $dateLast);
    }
    $sth->finish;
    
    push @reportTotal, {
        type                =>  'Total',
        totRecsDateFirst    =>  $totRecsDateFirst,
        totRecsDateLast     =>  $totRecsDateLast,
        totRecsAdded        =>  $totRecsAdded,
        totItemsAdded       =>  $totItemsAdded ,
        totRecsDeleted      =>  $totRecsDeleted,
        totItemsDeleted     =>  $totItemsDeleted ,
        totItemsFound       =>  $totItemsFound ,
        totItemsDateFirst   =>  $totItemsDateFirst,
        totItemsDateLast    =>  $totItemsDateLast, 
    };
    $template->param(
        result  => 1,
        reports_total    => \@reportTotal,
    );
}

sub getStatReportByItemType{

    my ($dbh,$template, $dateFirst, $dateLast) =@_;
    my @reportByItemType;
    my $tbItemTypeList    = tb_itemType_getList($dbh);
    my ($totRecsDateFirstByItt,$totRecsDateLastByItt, $totItemsDateFirstByItt,$totItemsDateLastByItt )= (0,0,0,0);
    my ($totRecsAddedByItt ,$totItemsAddedByItt  ) = (0,0);
    my ($totRecsDeletedByItt ,$totItemsDeletedByItt  ) = (0,0);
    my $totItemsFoundByItt  = 0;

    foreach my $itType (@$tbItemTypeList ){
        ($totRecsDateFirstByItt,$totRecsDateLastByItt, $totItemsDateFirstByItt,$totItemsDateLastByItt )= (0,0,0,0);
        ($totRecsAddedByItt ,$totItemsAddedByItt ) = (0,0);
        $totRecsDateFirstByItt  = getTotalRecordsToDateByItemType($dbh, $dateFirst , $itType->{'id'} );
        $totRecsDateLastByItt   = getTotalRecordsToDateByItemType($dbh, $dateLast , $itType->{'id'});
        $totRecsAddedByItt      = getTotalRecordsAddedByItemType($dbh, $dateFirst, $dateLast,$itType->{'id'});
        $totItemsAddedByItt     = getTotalItemsAddedByItemType($dbh, $dateFirst, $dateLast,$itType->{'id'});
        $totRecsDeletedByItt    = getTotalRecordsDeletedByItemType($dbh, $dateFirst, $dateLast, $itType->{'id'});
        $totItemsDeletedByItt   = getTotalItemsLostDeletedByItemType($dbh, $dateFirst, $dateLast,$itType->{'id'});
        $totItemsFoundByItt     = getTotalItemsFoundByItemType($dbh, $dateFirst, $dateLast,$itType->{'id'});

        my $sql = "select distinct rid from tb_records as r inner join tb_items as i using(rid)
            where i.typeId = ? ";
        my $sth = $dbh->prepare($sql);
        $sth->execute($itType->{'id'});
        while (my ($rid) = $sth->fetchrow_array()){
            $totItemsDateFirstByItt += getTotalItemsToDateByItemType($dbh, $rid, $dateFirst , $itType->{'id'});
            $totItemsDateLastByItt  += getTotalItemsToDateByItemType($dbh, $rid, $dateLast , $itType->{'id'} );
        }
        push @reportByItemType, {
            type                    =>  $itType->{'id'},
            typeDesc                =>  $itType->{'desc'},
            totRecsDateFirstByItt   =>  $totRecsDateFirstByItt,
            totRecsDateLastByItt    =>  $totRecsDateLastByItt,
            totRecsAddedByItt       =>  $totRecsAddedByItt,
            totItemsAddedByItt      =>  $totItemsAddedByItt,
            totRecsDeletedByItt     =>  $totRecsDeletedByItt,
            totItemsDeletedByItt    =>  $totItemsDeletedByItt ,
            totItemsFoundByItt      =>  $totItemsFoundByItt ,
            totItemsDateFirstByItt  =>  $totItemsDateFirstByItt,
            totItemsDateLastByItt   =>  $totItemsDateLastByItt, 
        };
    }
    $template->param(
        reports_byItemType    => \@reportByItemType,
    );
}

sub getTotalRecordsToDate{
    my ($dbh, $toDate)=@_;
    my $totCount = 0;
    my $sql = "select rid, min(i.importDate), (min(i.importDate) <= '$toDate') as count
        from    tb_items as i inner join tb_records as r using(rid)
        group by rid";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my ($rid, $minImportDate, $count ) =  $sth->fetchrow_array()){
        if ($rid && $minImportDate && $count && $count > 0) {
            $totCount++;
        }
    }   
    $sth->finish;
    my $totDelTodate = getTotalRecordsDeletedToDate($dbh,$toDate);
    my $ret = $totCount - $totDelTodate ;
    return $ret ;
}

sub getTotalItemsToDate{
    my ($dbh, $rid, $toDate) = @_;
    my $sql = "select count(*) 
        from tb_items 
        where available = 1  && importDate <= '$toDate'  && rid = $rid  ";
# && barcode not in (select barcode from tb_itemStatus where )
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalRecordsToDateByItemType{
    my ($dbh, $toDate, $itemType)=@_;
    my $totCount = 0;
    my $sql = "select rid, min(i.importDate), 
                (min(i.importDate) <= '$toDate') as count
        from tb_items as i inner join tb_records as r using(rid)
        where typeId = ?
        group by rid, typeId";
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType);
    while (my ($rid, $minImportDate, $count) =  $sth->fetchrow_array()){
        if ($rid && $minImportDate && $count && $count > 0 ) {
            $totCount++;
        }
    }   
    $sth->finish;
    my $totDelTodate = getTotalRecordsDeletedToDateByItemType($dbh,$toDate,$itemType);
    my $ret = $totCount - $totDelTodate ;
    return $ret;

}

sub getTotalItemsToDateByItemType{
    my ($dbh, $rid, $toDate, $itemType ) = @_;
    my $sql = "select   count(*) 
                from    tb_items as i
                where   available > 0 && rid = $rid  && importDate <= '$toDate' &&  i.typeId = ?";
# && barcode not in (select barcode from tb_itemStatus where )
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType );
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalRecordsAdded{
    my ($dbh, $dateFrom, $dateTo) = @_;
    my $ret = 0;
    my $sql = "select rid, min(i.importDate) 
        from tb_items as i inner join tb_records as r using(rid)
        group by rid";
    my $sqlCount = "select count(*) 
        from tb_items 
        where rid = ? && importDate between '$dateFrom' and '$dateTo' && importDate  <= ?";
    my $sth = $dbh->prepare($sql);
    my $sth_count = $dbh->prepare($sqlCount);
    $sth->execute();
    while (my ($rid, $importDate) =  $sth->fetchrow_array()){
        if ($rid && $importDate) {
            $sth_count->execute($rid, $importDate);
            my ($iCount) = $sth_count->fetchrow_array();
            if ($iCount){
                $ret++;
            }
        }
    }   
    return $ret;
}

sub getTotalItemsAdded{
    my ($dbh, $dateFrom, $dateTo) = @_;
     my $sql = "select  count(*) 
                from    tb_items 
                where   importDate between '$dateFrom' and '$dateTo' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalRecordsDeleted{
    my ($dbh, $dateFrom, $dateTo) = @_;
    my $sql = " select count(distinct rid) 
                from tb_records 
                where fId = '005' && fVal between '$dateFrom' and '$dateTo' && deleted = '1' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}
sub getTotalRecordsDeletedToDate{
    my ($dbh, $toDate) = @_;
    my $sql = " select count(distinct rid) 
                from tb_records 
                where fId = '005' && fVal <= '$toDate' && deleted = '1' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalItemsLostDeleted{
    my ($dbh, $dateFrom, $dateTo) = @_;
    my $sql = "select count(*) 
            from    tb_items 
            where  importDate between '$dateFrom' and '$dateTo' && deleted = '1' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $nDeleted = $sth->fetchrow_array;

    $sql = "select count(*) 
            from    tb_items as it inner join tb_itemStatus as its using (barcode) 
            where   its.status = ? && its.ondate between '$dateFrom' and '$dateTo' 
            order by its.ondate desc limit 0,1 ";
    $sth = $dbh->prepare($sql);
    $sth->execute(ITEM_LOST);
    my $nLost = $sth->fetchrow_array;

    $sth->finish;
    return ($nDeleted + $nLost);
}
sub getTotalItemsFound{

    my $sql = "select count(*) 
            from    tb_items as it inner join tb_itemStatus as its using (barcode) 
            where   its.status = ? && its.ondate between '$dateFrom' and '$dateTo' 
            order by its.ondate desc limit 0,1 ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(ITEM_ACTIVE);
    my $nFound = $sth->fetchrow_array;
    $sth->finish;
    return $nFound;
}


sub getTotalRecordsAddedByItemType{
    my ($dbh, $dateFrom, $dateTo, $itemType ) = @_;
    my $ret = 0;
    my $sql = "select rid, min(i.importDate) 
        from tb_items as i inner join tb_records as r using(rid)
        group by rid";
    my $sqlCount = "select count(*) 
        from tb_items 
        where rid = ? && importDate between '$dateFrom' and '$dateTo'
            && importDate  <= ?  &&  typeId = '$itemType' ";
    my $sth = $dbh->prepare($sql);
    my $sth_count = $dbh->prepare($sqlCount);
    $sth->execute();
    while (my ($rid, $minImportDate) =  $sth->fetchrow_array()){
        if ($rid && $minImportDate) {
            $sth_count->execute($rid, $minImportDate);
            my ($iCount ) = $sth_count->fetchrow_array();
            if ($iCount && $iCount > 0){
                $ret++;
            }
        }
    }   
    return $ret;
}

sub getTotalItemsAddedByItemType{
    my ($dbh, $dateFrom, $dateTo, $itemType ) = @_;
    my $sql = "select count(*) 
            from    tb_items as i
            where   importDate  between '$dateFrom' and '$dateTo'  &&  i.typeId = ?";
# && barcode not in (select barcode from tb_itemStatus where )
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType );
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalRecordsDeletedToDateByItemType{
    my ($dbh, $toDate, $itemType) = @_;
    my $sql = " select count(distinct rid) 
                from tb_records as r inner join tb_items as i using(rid)
                where fId = '005' && fVal <= '$toDate' && r.deleted = '1' && i.typeId = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType);
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalRecordsDeletedByItemType{
    my ($dbh, $dateFrom, $dateTo, $itemType ) = @_;
    my $sql = " select  count(distinct rid) 
                from    tb_records as r inner join tb_items as i using (rid)
                where   fId = '005' && fVal between '$dateFrom' and '$dateTo' 
                        && i.typeId = ?  && r.deleted = '1'";
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType );
    my $ret = $sth->fetchrow_array;
    $sth->finish;
    return $ret;
}

sub getTotalItemsLostDeletedByItemType{
    my ($dbh, $dateFrom, $dateTo, $itemType ) = @_;
    my $sql = " select count(*) 
                from    tb_items as i
                where   available = 0 && importDate  between '$dateFrom' and '$dateTo'  &&  i.typeId = ?  && i.deleted = '1'";
# && barcode not in (select barcode from tb_itemStatus where )
    my $sth = $dbh->prepare($sql);
    $sth->execute($itemType );
    my $nDeleted = $sth->fetchrow_array;
    
    $sql = "select  count(*) 
            from    tb_items as it inner join tb_itemStatus as its using (barcode) 
            where   its.status = ? && it.typeId = ? && its.ondate between '$dateFrom' and '$dateTo' 
            order by its.ondate desc limit 0,1 ";
    $sth = $dbh->prepare($sql);
    $sth->execute(ITEM_LOST, $itemType);
    my $nLost = $sth->fetchrow_array;
    
    $sth->finish;
    return ($nDeleted + $nLost);

}

sub getTotalItemsFoundByItemType{
    my ($dbh, $dateFrom, $dateTo, $itemType ) = @_;
    my $sql = "select  count(*) 
            from    tb_items as it inner join tb_itemStatus as its using (barcode) 
            where   its.status = ? && it.typeId = ? && its.ondate between '$dateFrom' and '$dateTo' 
            order by its.ondate desc limit 0,1 ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(ITEM_ACTIVE, $itemType);
    my $nFound = $sth->fetchrow_array;
    $sth->finish;
    return ($nFound);
}

tmpl_write($dbh, $cgi, $cookieList, $template);