#!/usr/bin/perl

#use utf8;
###########################################################
#
# Wed, Jan 07, 2009 @ 12:21:50 EST
# modifiy  sub GetRecordAcquisitionList  to have the capability 
# to sort by barcode. But it also cuase the report will list all 
# the holding ndividually instead of group by record.
#
# Thu, Feb 05, 2009 @ 10:46:04 EST
# add option to create union update request
#
# Wed, Jul 15, 2009 @ 11:01:04 EDT
# Add options Include/Exclude ILL, Temporay items
###########################################################


use strict;
use CGI;

use Opals::Context;
use Date::Calc qw(Today Add_Delta_Days Day_of_Week Week_Number Day_of_Year);

use Opals::User qw(
    user_getInformation
);
use Opals::MarcXml qw(
    mxml_delete
    mxml_newItem
);

use Opals::RecordInfoHTML qw(
    rinfo_getPageNavForm_search
);

use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::UnionRequest qw(
    urq_createUnionRequest
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

my $dbh = Opals::Context->dbh();

my $sortFieldMap={
    title_asc       =>"titleSort,author,callNumber,pubDateSort,barcode,r.rid",
    title_desc      =>"titleSort DESC,author DESC,callNumber DESC,pubDateSort DESC,barcode DESC,rid DESC",
    author_asc      =>"author,titleSort,callNumber,pubDateSort,barcode,rid",
    author_desc     =>"author DESC,titleSort DESC,callNumber DESC,pubDateSort DESC,barcode DESC,rid DESC",
    callNumber_asc  =>"callNumber,titleSort,author,pubDateSort,barcode,rid",
    callNumber_desc =>"callNumber DESC,titleSort DESC,author DESC,pubDateSort DESC,barcode DESC,rid DESC",
    pubDateSort_asc =>"pubDateSort,titleSort,author,callNumber,barcode,rid",
    pubDateSort_desc=>"pubDateSort DESC,titleSort DESC,author DESC,callNumber DESC,barcode DESC,rid DESC",
    barcode_asc     =>"barcode,titleSort,author,callNumber,pubDateSort,rid",
    barcode_desc    =>"barcode DESC,titleSort DESC,author DESC,callNumber DESC,pubDateSort DESC,rid DESC",
    rid_asc         =>"rid,titleSort,author,callNumber,pubDateSort,barcode",
    rid_desc        =>"rid DESC,titleSort DESC,author DESC,callNumber DESC,pubDateSort DESC,barcode DESC"
};
END { $dbh->disconnect(); }

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

    my($year,$month,$day)=Today();
    ($year,$month,$day) = Add_Delta_Days($year,$month,$day,90);
    my $defaultNewItemExpDate=sprintf("%04d-%02d-%02d",$year,$month,$day); 


    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;

    my $acqDefaultInputs ={
        acqstFrom   =>"$dateToday 00:00:00",
        acqstTo     =>"$dateToday 23:59:59",
        newitemExp  => $defaultNewItemExpDate,
        sort2       =>'dateImport',
        sortOrder   =>'',
        incExcOpt   =>'exclusion',
        whole       =>0,
        dateRangeOpt=>'rangeSel',
        ridList     =>'',
        op          =>'view',
        pNum        =>1
    };
    foreach my $i (keys %$acqDefaultInputs){
        $input->{$i} =$acqDefaultInputs->{$i} if(!defined $input->{$i});
    }

my $ridList= $input->{'ridList'};
my $tmplName=(exists $input->{'ridList'})?'report/itemacqst_prt.tmpl':'report/itemacqst.tmpl';
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemacqst.tmpl',
            reqPermission   => 'rpt_notice|rpt_catRec|marc_edit',
        }
);


    my $incExcOpt = $input->{'incExcOpt'};
      
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    my $offset      = ($pNum - 1) * $pagesize ;

    my $dateFrom    = $input->{'acqstFrom'};
    my $dateTo      = $input->{'acqstTo'};
       $dateFrom    =~ s/(\d\d\d\d-\d\d-\d\d)(.*)/$1 00:00:00/g;
       $dateTo      =~ s/(\d\d\d\d-\d\d-\d\d)(.*)/$1 23:59:59/g;
    my $newitemExp  = $input->{'newitemExp'};
    my $sort        = $input->{'sort2'};
    my $sortOrder   = $input->{'sortOrder'};
    my $selRidOption   = $input->{'whole'}; # whole or just selected rid

    my $loginuid = $template->param('curUserId');       


    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
     
    $template->param(acqstFrom => $dateFrom);
    $template->param(acqstTo => $dateTo);
    $template->param(newitemExp => $newitemExp);
    $template->param(acqList => 1);

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemacqst'));
    my $remainList = '';
    my $op= $input->{'op'};
    if ($op && $op ne ""){
        if($op eq 'export2Union'){
            createUnionUpdateRequest($dbh,$loginuid,$selRidOption,$input->{'ridList'},$dateFrom,$dateTo,$incExcOpt);
        }
        else{
            my $ridList = $selRidOption? getAcqstRidList($dbh,$dateFrom,$dateTo,$incExcOpt):$input->{'ridList'}; 
            if ($op eq 'del' ){   
               $remainList = mxml_delete($dbh,$ridList);
            }
            elsif($op eq 'newitem' ){
                mxml_newItem($dbh, $ridList,$newitemExp);
            }
        }
    }

#    GetAcquisitionReport($dbh, $template, $dateFrom, $dateTo, $sort, $pNum, $pagesize, $remainList);
     my $op= $input->{'op'};
    if(defined $op && $op eq 'csv'){
        my $recordList = GetRecordAcquisitionList($dbh, $dateFrom, $dateTo, $sort, $sortOrder,$pNum, 0, $remainList,$incExcOpt);
        getItemCIrcStats2Export($recordList);
    }
    else{ 
        my $recordList = GetRecordAcquisitionList($dbh, $dateFrom, $dateTo, $sort, $sortOrder,$pNum, $pagesize, $remainList,$incExcOpt);
        my $recRsPos =$offset;                
        foreach my $rec(@$recordList){
            $rec->{'recRsPos'}=$recRsPos++;
        }
        MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt);
          #  setPageNavForm($cgi);
        my $pageNavForm =rinfo_getPageNavForm_search($cgi);
        $template->param(
            pageNavForm=>$pageNavForm,
            recordList  => $recordList,
            newitemExp  => substr($newitemExp, 0, 10),
            sort2       => $sort,
            sortOrder   => $sortOrder,
            "sort_" .$sort   => 1,
            "sort_" .$sortOrder   => 1,
            pNum        => $pNum,
            marcEdit    => ($permission && $permission->{'marc_edit'}) ? 1 : 0,
            incExcOpt   => $incExcOpt,
        );

        if($incExcOpt eq 'exclusion'){
            $template->param(exclusion =>1);
        }
        elsif($incExcOpt eq 'inclusion'){
            $template->param(inclusion =>1);
        }


        if($dateRange eq 'all'){
            $template->param(
                 rangeAll   => 1,
                dateRangeOpt=>"all"
           );
        }
        elsif($dateRange eq 'fYear'){
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeFYear   => 1,
                dateRangeOpt=>"fYear"
           );
        }
        else{
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeSel   => 1,
                dateRangeOpt=>"rangeSel"
           );

        }

        #Tue, Jan 12, 2010 @ 10:31:41 EST
        my $msgValMap ={};
        my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
        loc_write($template,$msgMap);

        $template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemacqst') );
        tmpl_write($dbh, $cgi, $cookie, $template);
        #$dbh->disconnect();
   }

################################################################################
sub GetRecordAcquisitionList_bk20090107 {
    my ($dbh, $dateFrom, $dateTo, $sort, $pNum, $pagesize, $remainList) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

    my $recBlocked;
    $remainList =~ s/,+/,/g;
    $remainList =~ s/(^,|,$)//g;
    foreach my $rid (split /,/, $remainList) {
        $recBlocked->{$rid} = 1;
    }

    my $sql = <<_STH_;
select  i.rid, r.title, r.author,i.callNumber
from    opl_item as i inner join opl_marcRecord as r  on i.rid = r.rid 
where   substring(i.barcode, 1, 3) <> '___' &&
        i.dateImport >= '$dateFrom' &&
        i.dateImport <= '$dateTo'
group by rid
order by $sort  asc
limit   $pageoffset, $pagesize 
_STH_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my $holdingList;
    while (my $rec = $sth->fetchrow_hashref) {
        #$rec->{'odd'} = $odd ^ 1;
        $pNum++;
        $rec->{'odd'} = $pNum%2;
              $rec->{'order'} = $pNum;
        $rec->{'hasTransaction'} = $recBlocked->{$rec->{'rid'}};
        $holdingList = getRecordHolding($dbh, $rec->{'rid'});
        $rec->{'holdingList'} = $holdingList;
        $rec->{'holdingCount'} = scalar(@$holdingList);
        ($rec->{'holdingCount'} > 1) || ($rec->{'holdingCount'} = 0);
        push @recordList, $rec;
    }
    $sth->finish;

    return \@recordList;
}

################################################################################
# Wed, Jan 07, 2009 @ 10:25:02 EST
sub GetRecordAcquisitionList {
    my ($dbh, $dateFrom, $dateTo, $sort,$sortOrder, $pNum, $pagesize, $remainList,$incExcOpt) = @_;
    my $pageoffset =0; 
    if($pNum && $pagesize){
        $pageoffset = $pNum;
        if ( !$pageoffset ) {
            $pageoffset = 0;
        }
        else {
            $pageoffset = ($pNum - 1) * $pagesize;
        }
    }    

    my $recBlocked;
    $remainList =~ s/,+/,/g;
    $remainList =~ s/(^,|,$)//g;
    foreach my $rid (split /,/, $remainList) {
        $recBlocked->{$rid} = 1;
    }
   $sortOrder =(!$sortOrder || $sortOrder eq '')?"_asc" : "_$sortOrder";
   $sortOrder=lc($sortOrder);
   $sort ="rid" if(!$sort || !defined $sortFieldMap->{"$sort$sortOrder"});
   my $sortCond = $sortFieldMap->{"$sort$sortOrder"};

    my $sql = " select  r.rid, r.title, r.author,r.pubDate, r.pubDateSort,
                        r.pubPlace,r.pubName,
                        i.callNumber, i.barcode, i.dateImport as ondate
from    opl_item as i inner join opl_marcRecord as r  on i.rid = r.rid 
where   substring(i.barcode, 1, 3) <> '___' &&
        i.dateImport >= '$dateFrom' &&
        i.dateImport <= '$dateTo' ";
 
 if($incExcOpt eq 'exclusion'){
       $sql .= " && (r.tempIll is NULL ||  r.tempIll = '') ";
  }
        
    $sql .= " order by $sortCond";
    if($pagesize && $pagesize >0){
          $sql .= " limit   $pageoffset, $pagesize ";
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    #my $odd = 0;
    my $pNum = -1;
    my @recordList;
    my $holdingList;
    while (my $rec = $sth->fetchrow_hashref) {
        #$rec->{'odd'} = $odd ^ 1;
        $pNum++;
        $rec->{'odd'} = $pNum%2;
              $rec->{'order'} = $pNum;
        $rec->{'hasTransaction'} = $recBlocked->{$rec->{'rid'}};

        #$holdingList = getRecordHolding($dbh, $rec->{'rid'});
        my ($barcode,$callNumber,$ondate)= ($rec->{'barcode'},$rec->{'callNumber'},$rec->{'ondate'});
        my @hl =({barcode=>$barcode, callNumber=>$callNumber,ondate=>$ondate});
        #$rec->{'holdingList'} = $holdingList;
        $rec->{'holdingList'} = \@hl;
        $rec->{'holdingCount'} =1 ;# scalar(@$holdingList);
        ($rec->{'holdingCount'} > 1) || ($rec->{'holdingCount'} = 0);
        push @recordList, $rec;
    }
    $sth->finish;

    return \@recordList;
}

################################################################################
sub getRecordHolding {
    my ($dbh, $rid) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  barcode, callNumber, dateImport as ondate
from    opl_item
where   rid = ? &&
        substring(barcode, 1, 3) <> '___'
order by callNumber,barcode asc
_STH_
    $sth->execute($rid);
    my @holdingList;
    while (my $holding = $sth->fetchrow_hashref) {
       if($holding->{'ondate'} lt $dateFrom || $holding->{'ondate'} gt $dateTo){
            $holding->{'outOfRange'}=1;
        }
        push @holdingList, $holding;
    }
    $sth->finish;

    return \@holdingList;

}

=item

sub GetAcquisitionReport
{
    my ($dbh, $template, $dateFrom, $dateTo, $sort, $pNum, $pagesize, $remainList) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }3389
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
        
    my $szSQL = "select i.rid, dateImport, callNumber, barcode, title, author from opl_item as i, opl_marcRecord as r where i.rid=r.rid && dateImport >= '$dateFrom' && dateImport <= '$dateTo'";
    $szSQL .= " order by " . $sort . " asc";
    $szSQL .= " limit $pageoffset, $pagesize";

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my @itemList = ();
    my $bZebraServerDown = 0;
    my $Num = 0;
    
    my @IdArray = split(/,/, $remainList);
    my %IdHash;
    for (my $i=0; $i < @IdArray; $i++)
    {
        $IdHash{$IdArray[$i]} = 1;
    }
    $template->param(NumNotDel => scalar(@IdArray));
    
    while ( my $rec = $query->fetchrow_hashref() )
    {
        $rec->{'odd'} = $Num%2;
        $rec->{'order'} = $Num;
        if ( $IdHash{$rec->{'rid'}} == 1 )
        {
            $rec->{'hasTransaction'} = 1;
        }
        $Num++;
        push @itemList, $rec
    }
    $query->finish;
    $template->param(itemList => \@itemList);
}


#----------------------------------------------------------
sub MakePages_bk
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo) = @_;
    
    my $szSQL = "select count(*) from opl_historyItem where substring(barcode, 1, 3) <> '___' && ondate>='$dateFrom' && ondate<='$dateTo'";
    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($countHolding) = $query->fetchrow_array;
    $query->finish;
    
    $szSQL = "select count(distinct rid) from opl_item inner join opl_historyItem on opl_item.barcode=opl_historyItem.barcode where opl_historyItem.status='imported' && ondate>='$dateFrom' && ondate<='$dateTo' ";
    $query = $dbh->prepare($szSQL);
    $query->execute();
    my $countRecord = 0;
    my ($countRecord) = $query->fetchrow_array;
    $query->finish;

    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countRecord, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countHolding    => $countHolding,
        countRecord     => $countRecord,
        rangedPageList  => \@rangedPageList,
    );
}

=cut
#----------------------------------------------------------

sub getAcqstRidList{
    my ($dbh,$dateFrom,$dateTo)=@_;
    my $retval="";
    my $query =$dbh->prepare("select distinct rid from opl_item 
                             where substring(barcode, 1, 3) <> '___' &&
                             dateImport >= '$dateFrom' && dateImport <='$dateTo' ");
    $query->execute();
    while( my ($rid) = $query->fetchrow_array){
        $retval .= $rid .",";
    }

    $retval =~ s/,+/,/g;
    $retval =~ s/(^,|,$)//g;
    return $retval;
    
}

#----------------------------------------------------------

sub createUnionUpdateRequest{
    my ($dbh,$uid ,$selRidOption,$ridList,$dateFrom,$dateTo,$incExcOpt)=@_;
    my $ridBcList;
    if($selRidOption){
        $ridBcList = getUnionReqRidBcList_whole($dbh,$dateFrom,$dateTo,$incExcOpt);
    }
    else{
        $ridBcList = getUnionReqRidBcList_sel($dbh,$ridList,$dateFrom,$dateTo,$incExcOpt);
    }
    foreach my $rid (keys %{$ridBcList}){
       # print debug "$rid\n";
    }
    if($ridBcList){
        urq_createUnionRequest($dbh,$uid,"import",$ridBcList);
    }
       
}

#----------------------------------------------------------
# get the whole rid,bc list for the whole acquisition between $dateFrom and $dateTo 

sub getUnionReqRidBcList_whole{
    my ($dbh,$dateFrom,$dateTo,$incExcOpt)=@_;
    my $retval;
    my $sql = " select distinct i.rid,barcode from opl_item i
                                inner join opl_marcRecord as r  on i.rid = r.rid 
                     where  substring(barcode, 1, 3) <> '___' &&
                            dateImport >= '$dateFrom' && dateImport <='$dateTo' ";
 
    if($incExcOpt eq 'exclusion'){
       $sql .= " && (r.tempIll is NULL ||  r.tempIll = '') ";
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while( my ($rid,$bc) = $sth->fetchrow_array){
        push @{$retval->{$rid}},$bc;
    }
    return $retval;
}

#----------------------------------------------------------
# get rid,bc list for the selected acquisition between $dateFrom and $dateTo 

sub getUnionReqRidBcList_sel{
    my ($dbh,$ridList,$dateFrom,$dateTo,$incExcOpt)=@_;
    my $retval;
    my @ridArr = split /,/, $ridList; 
    my $sql = "select barcode from opl_item i
                                inner join opl_marcRecord as r  on i.rid = r.rid 
                      where substring(barcode, 1, 3) <> '___' 
                            && dateImport >= '$dateFrom' && dateImport <='$dateTo'  && r.rid =? ";
    if($incExcOpt eq 'exclusion'){
        $sql .= " && (r.tempIll is NULL ||  r.tempIll = '') ";
    }
    my $sth = $dbh->prepare($sql);
    foreach my $rid(@ridArr){
        if($rid ne ''){
            $sth->execute((int $rid));
            while(my ($bc)=$sth->fetchrow_array){
                push @{$retval->{$rid}},$bc;
            }
       }
    }
    return $retval;
}

#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$incExcOpt) = @_;
    
    my $szSQL = " select count(*) from opl_item i inner join opl_marcRecord m on m.rid=i.rid 
                  where substring(barcode, 1, 3) <> '___' && dateImport>='$dateFrom' && dateImport<='$dateTo' ";
      
    if($incExcOpt eq 'exclusion'){
         $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    my $query = $dbh->prepare($szSQL);
    $query->execute();
    my ($countHolding) = $query->fetchrow_array;
    $query->finish;
    
    $szSQL = " select count(*) from opl_item i inner join opl_marcRecord m on m.rid=i.rid
               where substring(barcode, 1, 3) <> '___' && dateImport>='$dateFrom' && dateImport<='$dateTo' ";
    if($incExcOpt eq 'exclusion'){
         $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }               
    $szSQL .= " group by i.rid ";
    $query = $dbh->prepare($szSQL);
    $query->execute();
    my $countRecord = 0;
    while ($query->fetchrow_array) {
        $countRecord++;
    }
    $query->finish;

    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    #my @rangedPageList = tmpl_rangedPageList($countRecord, $input->{'pNum'}, $pagesize, 10);
    my @rangedPageList = tmpl_rangedPageList($countHolding, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countHolding    => $countHolding,
        countRecord     => $countRecord,
        rangedPageList  => \@rangedPageList,
    );
}
#=======================================================
sub setPageNavForm{
    my ($cgi,)=@_;
    my $input=$cgi->Vars();
    my @recType     = $cgi->param('recType');
    my $navFormInput=[];
    foreach my $iName(keys %$input){
        push @$navFormInput,{name=>$iName, value=>$input->{$iName}};
    }
    my @recType     = $cgi->param('recType');
    foreach my $rType(@recType){
        push @$navFormInput,{name=>'recType', value=>$rType};
    }
    my @fieldArr =qw(languageFilter formatFilter authorFilter subjectFilter deweyFilter eraFilter genreFilter);
    foreach my $f(@fieldArr){
        if(!defined $input->{$f}){
            push @$navFormInput,{name=>$f, value=>''};
        }
    }
    if(!defined $input->{'pNum'}){
        push @$navFormInput,{name=>'pNum', value=>1 };
    }
    $template->param(navFormInput => $navFormInput);
}


#----------------------------------------------------------
# Mon, Nov 26, 2012 @ 11:40:09 EST
#

sub getItemCIrcStats2Export{
    my ($recordList)=@_;
    
    my $msgValMap ={};
    my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);

    my $itemFields=[
        {name=>'rid',       header=>uc($msgMap->{'idTxt'})},
        {name=>'title',     header=>uc($msgMap->{'titleTxt'})},
        {name=>'author',    header=>uc($msgMap->{'authorTxt'})},
        {name=>'pubName',   header=>uc($msgMap->{'pubNameTxt'})},
        {name=>'pubPlace',  header=>uc($msgMap->{'pubPlaceTxt'})},
        {name=>'pubDate',   header=>uc($msgMap->{'pubDateTxt'})},
        {name=>'callNumber',header=>uc($msgMap->{'callNumTxt'})},
        {name=>'barcode',   header=>uc($msgMap->{'barcodeTxt'})},
        {name=>'ondate',    header=>uc($msgMap->{'impDateTxt'})}] ;

    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=ItemAcq.csv\n\n"; 
    my $headers=[];
    foreach  my $r (@$itemFields){
        push @$headers,"\"" . $r->{'header'} . "\"";
    }
    print  join(",",@$headers), "\n";
    foreach  my $row (@$recordList){
       my $arr=[];
       foreach my $f(@$itemFields){  
          $row->{$f->{'name'}} =~ s/"/""/g;
          push @$arr,"\"" . $row->{$f->{'name'}} ."\"";
       }
       print join(",",@$arr) ,"\n";
    }


}