package Opals::Report;
require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    getAverageCopyrightDate 
     _splitDeweyNumber
     _first_digit_matched
     _first_two_digits_matched    
    _first_digit_unmatched
     createRegExp

    rpt_onloanList
    rpt_onloanList_WO_userInfo

    rpt_getItemListByStatus
    rpt_getItemListByStatus_current
    rpt_getMissingItem
    rpt_preDueEmailNotice

    rpt_getFineTransList

    rpt_circTrans
    rpt_holdTrans
    rpt_itemRecord
);
use POSIX qw(
    floor
);
use Opals::Date qw(
    date_text
    date_now
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);
use Opals::Mail qw(
    mail_enqueueMail
    );
use Opals::MarcXmlParser;
use JSON;
# Version number
$VERSION   = 0.01;      
use POSIX qw(
    ceil
);

#use utf8;
use strict;
#use Opals::_Some_Module_;
#
sub _genGUID{
    my $guid=`uuidgen`;
    chomp $guid;
    return $guid;
}

################################################################################
sub rpt_getFineTransList {
    my ($dbh,$rptType,$from,$to,$pNum,$pagesize,$sort,$sortDir) = @_;
    my $mapField={unpaid        =>{fields=>['balance','date']    ,tbl=>'opl_charge'},
                  pendingRefund =>{fields=>['balance','date']    ,tbl=>'opl_charge'},
                  waived        =>{fields=>['waive'  ,'date']    ,tbl=>'opl_charge'},
                  refunds       =>{fields=>['withdrawn','ondate'],tbl=>'opl_paymentReceipt'},
                  collected     =>{fields=>['recieved','ondate'] ,tbl=>'opl_paymentReceipt'}
                  };
    my ($rTbl,$rFields) = ($mapField->{$rptType}->{'tbl'},$mapField->{$rptType}->{'fields'});       
    my ($field0, $field1) =(@$rFields[0],@$rFields[1]);   
    my $totalAmt=0;
    my $srtFeldMap={
            rptAmt=>$field0,
            onDate=>$field1
        };             
    if(! defined $sort || $sort eq ''){
        $sort =' u.lastname,u.firstname ' 
    }
    elsif( defined $srtFeldMap->{$sort}){
        $sort=  $srtFeldMap->{$sort};
    }
    $sortDir =' asc ' if(! defined $sortDir || $sortDir eq '');
    $from="2000-01-01" if(!defined $from || $from eq '');
    $to=date_now() if(!defined $to || $to eq '');


    my $sql = <<_STH_;
select u.*,sum($field0) $field0,max($field1) $field1
from $rTbl c  inner join opl_user u using (uid)
where $field1 between '$from' and '$to'
_STH_
    if($rptType eq 'pendingRefund'){
        $sql .= " group by u.uid having sum($field0) <0 ";  
    }
    else{
        $sql .= " group by u.uid having sum($field0) >0 ";
    }


    my($total)=$dbh->selectrow_array("select count(*) from  ($sql) t ",undef );
    ($totalAmt)=$dbh->selectrow_array("select sum($field0) from ($sql) t ",undef );
    $totalAmt=0 if(!$totalAmt);
    if(defined $sort && defined $sortDir){
        $sql .=" order by  $sort  $sortDir";
    }
    if(defined $pNum && defined $pagesize){
        my $pageoffset =$pagesize*($pNum-1);
        $sql .=" limit  $pageoffset, $pagesize";
    }

    
    #open debug ,">/tmp/dd"; print debug "$sql\n"; close debug;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    
    my @recordList =();
    while (my $r = $sth->fetchrow_hashref) {
       my $rec;
          $rec->{'tid'}         = $r->{'tid'};
          $rec->{'firstname'}   = $r->{'firstname'};
          $rec->{'lastname'}    = $r->{'lastname'};
          $rec->{'date'}        = $r->{'date'};
          $rec->{'description'} = $r->{'description'};
          $rec->{'rptAmt'}      = $r->{$field0};
          $rec->{'uid'}         = $r->{'uid'};
          $rec->{'sid'}         = $r->{'sid'};
          $rec->{'userbarcode'} = $r->{'userbarcode'};
          $rec->{'grade'}       = $r->{'grade'};
          $rec->{'homeroom'}    = $r->{'homeroom'};
          $rec->{'teacher'}     = $r->{'teacher'};
          $rec->{'notes'}       = $r->{'notes'};
          $rec->{'onDate'}      = $r->{$field1};
          #$totalAmt += $rec->{'rptAmt'};
          push @recordList, $rec;    
    }
    $sth->finish;
    return{total=>$total,recordList=>\@recordList,totalAmt=>$totalAmt};
}

################################################################################
sub rpt_getItemListByStatus{
    my($dbh,$status,$from,$to,$pNum,$pSize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo,$filter,$rsCacheFid)=@_;
       $from =~ s/(\d{4}-\d{2}-\d{2}).*/$1/g;
       $to   =~ s/(\d{4}-\d{2}-\d{2}).*/$1 23:59:59/g;
    my $rs={};
    if($status ==0){
        $rs=getMissingItemRpt($dbh,$from,$to,$pNum,$pSize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo,$filter,$rsCacheFid);
    }
    else{
        $rs=getItemListByStatusRpt($dbh,$status,$from,$to,$pNum,$pSize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo);
    }
    return $rs;
}
############################################################
sub getMissingItemRpt{
    my($dbh,$from,$to,$pNum,$pagesize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo,$filter,$rsCacheFid)=@_;
    my $rs={};
    my $rpt=undef;
    my $data="";
    if(defined $rsCacheFid && -f "/tmp/$rsCacheFid" && 0){ # somehow, parsing cache report is slow=> disable it for now

        open IN,"</tmp/$rsCacheFid";
        while(<IN>){
            $data .=$_;
        }
        $rpt=from_json($data);
    }
    else{
        $rpt =_getMissingItem($dbh,$from,$to,$incIll,$incTmp,$incEbook,$incUserInfo);
        #disable cache
        #d$rsCacheFid =_genGUID();
        #dopen  OUT,">/tmp/$rsCacheFid";
        #dprint OUT to_json($rpt);
        #dclose OUT;
    }
    if(defined $rpt){
        my $itemList=[];
        $filter="" if(!defined $filter);
        if($filter eq 'missing'){
            $itemList=$rpt->{'itemList_missing'};
            $rs->{'found'}=$rpt->{'stillMissing'};
        }
        elsif($filter eq 'deleted'){
            $itemList=$rpt->{'itemList_del'};
            $rs->{'found'}=$rpt->{'totalDel'};
        }
        elsif($filter eq 'active'){
            $itemList=$rpt->{'itemList_active'};
            $rs->{'found'}=$rpt->{'totalActive'};
        }
        else{
            $itemList=$rpt->{'itemList'};
            $rs->{'found'}=$rpt->{'total'};
        }
        if($sort =~ m/titleSort|author|pubDateSort|isbn|callNumber|barcode|price|ondate|status/){
            if(defined $sortDir && lc $sortDir eq 'desc'){
                @$itemList = sort {lc($b->{$sort}) cmp lc($a->{$sort})} @$itemList;
            }
            else{
                @$itemList = sort {lc($a->{$sort}) cmp lc($b->{$sort})} @$itemList;

            }
        }
        elsif($sort eq  'rid'){
            if(defined $sortDir && lc $sortDir eq 'desc'){
                @$itemList= sort {$b->{$sort} <=> $a->{$sort}} @$itemList;
            }
            else{
                @$itemList = sort {$a->{$sort} <=> $b->{$sort}} @$itemList;
            }
        }

        $rs->{'total'}=$rpt->{'total'};
        $rs->{'stillMissing'}=$rpt->{'stillMissing'};
        $rs->{'totalDel'}=$rpt->{'totalDel'};
        $rs->{'totalActive'}=$rpt->{'totalActive'};
        my $rsItemList=[];
        if(defined $pNum){
            my $i=($pNum-1)*$pagesize;
            my $n=$pNum*$pagesize;
            my $itemListSize=scalar(@$itemList);
            $n= $itemListSize if($itemListSize<$n);
            while($i<$n){
                push @$rsItemList,@$itemList[$i++];
            }
        }
        else{
            $rsItemList=$itemList;
        }
        $rs->{'itemList'}=$rsItemList;
    }
    $rs->{'rsCacheFid'}=$rsCacheFid;
    return $rs;

}
############################################################
sub _getMissingItem{
    my($dbh,$from,$to,$incIll,$incTmp,$incEbook)=@_;
    $from="2000-01-01" if(!defined $from || $from eq '');
    $to=date_now() if(!defined $to || $to eq '');

    my $sql=<<_SQL_;
    select distinct s.barcode,t.status, i.rid,i.callNumber,i.price,s.ondate,
           m.title,m.titleSort,m.author, m.tempIll ,m.pubDate, m.isbn as isbn ,m.pubDateSort
    from opl_itemstatus s 
         inner join (select t1.barcode,t2.status from (select barcode,max(id) id 
                    from opl_itemstatus 
                    where ondate > '$from' group by barcode 
                    ) t1 
         inner join opl_itemstatus t2 using(id))t using(barcode) 
         inner join opl_item i using(barcode)
         inner join opl_marcRecord m using(rid)
    where s.status=0 and s.ondate between '$from' and '$to 23:59:59'
_SQL_
    
   if(!defined $incIll ||  $incIll ==0){
       $sql .= " and (m.tempIll is null || m.tempIll <>'ILL')";
   }
   if(!defined $incTmp ||   $incTmp==0){
       $sql .= " and (m.tempIll is null || m.tempIll  <> 'temporary')";
   }
   if(!defined $incEbook ||  $incEbook==0){
       $sql .= " and (m.recFormat <> 'ebook')";
   }
    my $sth =$dbh->prepare($sql);
    $sth->execute();
    my $itemList=[];
    my $itemList_del=[];
    my $itemList_active=[];
    my $itemList_missing=[];

    my $total=0;
    my $rs={total=>0,totalDel=>0,totalActive=>0,stillMissing=>0};
    while(my $r=$sth->fetchrow_hashref){
        push @$itemList,$r;
        $rs->{'total'}++;
        $total++;
        if($r->{'status'} ==5){
            push @$itemList_del,$r;
            $rs->{'totalDel'}++;
        }
        elsif($r->{'status'} !=0 && $r->{'status'} !=13 ){
            push @$itemList_active,$r;
            $rs->{'totalActive'}++;
        }
        else{
            push @$itemList_missing,$r;
            $rs->{'stillMissing'}++;
        }
    }

    $rs->{'itemList'}=$itemList;
    $rs->{'itemList_missing'}=$itemList_missing;
    $rs->{'itemList_active'}=$itemList_active;
    $rs->{'itemList_del'}=$itemList_del;

    return $rs;

}
############################################################
sub getItemListByStatusRpt{
    my($dbh,$status,$from,$to,$pNum,$pagesize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo)=@_;
    my $status_odl= ($status ==2 || $status ==3 ||$status ==11 ||$status ==12 );
    $from="2000-01-01" if(!defined $from || $from eq '');
    $to  =date_now() if(!defined $to || $to eq '');
    $to .=" 23:59:59";
    if($sort =~ m/titleSort|author|pubDateSort|isbn|rid/){
        $sort    = "m.$sort $sortDir";
    }
    elsif( $sort =~ m/firstname|lastname|userbarcode/ && $status_odl){
        $sort    = "u.$sort $sortDir";
    }
    elsif($sort =~ m/callNumber|barcode|price/){
        $sort    = "i.$sort ";
    }
    elsif($sort =~ m/ondate/){
        $sort    = "s.$sort $sortDir";
    }
    else{
        $sort    = "s.ondate DESC";
    }
    my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'};
    my @cond=();
    if(!defined $incIll ||  $incIll ==0){
      push @cond, "(m.tempIll is null || m.tempIll <>'ILL')";
   }
   if(!defined $incTmp ||   $incTmp==0){
        push @cond,  " (m.tempIll is null || m.tempIll  <> 'temporary')";
   }
   if(!defined $incEbook ||  $incEbook==0){
       push @cond,  " m.recFormat <> 'ebook'";
   }
    my $sql_count=(<<_SQL_);
    select  count(*)
    from opl_item i inner join opl_itemstatus s on i.barcode=s.barcode && s.status=? && s.ondate between ? and ?
     inner join opl_marcRecord m using(rid) 
     left outer join (select l.barcode, u.uid,u.firstname,u.lastname ,u.grade,u.userbarcode
                      from opl_loan l inner join opl_item i using(barcode) 
                            inner join opl_odl s using(idloan) 
                            inner join opl_marcRecord m using(rid) 
                            inner join opl_user u using(uid) 
                     where s.ondate between ? and ? && s.type=?) u 
     on i.barcode=u.barcode
_SQL_

   my $sql=(<<_SQL_);
    select  i.rid,i.barcode ,i.callNumber,i.price,s.ondate,
            m.title,m.author, m.tempIll ,m.pubDate,m.titleSort,m.pubDateSort , m.isbn as isbn,
            u.firstname,u.lastname,u.grade,u.userbarcode 
     from opl_item i inner join opl_itemstatus s on i.barcode=s.barcode && s.status=? && s.ondate between ? and ?
     inner join opl_marcRecord m using(rid) 
     left outer join (select l.barcode, u.uid,u.firstname,u.lastname ,u.grade,u.userbarcode
                      from opl_loan l inner join opl_item i using(barcode) 
                            inner join opl_odl s using(idloan) 
                            inner join opl_marcRecord m using(rid) 
                            inner join opl_user u using(uid) 
                     where s.ondate between ? and ? && s.type=?) u 
     on i.barcode=u.barcode
_SQL_

    if(scalar @cond>0){
        $sql       .= " where " . join(" AND ", @cond);
        $sql_count .= " where " . join(" AND ", @cond);
    }

  
   $sql .= " order by $sort ";

   my($count)=$dbh->selectrow_array($sql_count,undef ,$status,$from,$to,$from,$to,$itemStatus->{$status}); 

    my $found=0;
    my $itemList=[];
    if($count>0){
        $found=$count;

        if(defined $pNum && defined $pagesize){
            my $pageoffset =$pagesize*($pNum-1);
            $sql .=" limit  $pageoffset, $pagesize";
        }
        my $sth=$dbh->prepare($sql);
        $sth->execute($status,$from,$to,$from,$to,$itemStatus->{$status});
        while(my $rec=$sth->fetchrow_hashref){
            push @$itemList,$rec;
        }
        
    }
    
    return{total=>$found,found=>$found,itemList=>$itemList};
}
############################################################
sub rpt_getItemListByStatus_current{
    my($dbh,$status,$pNum,$pagesize,$sort,$sortDir,$incIll,$incTmp,$incEbook,$incUserInfo)=@_;
    my $status_odl= ($status ==2 || $status ==3 ||$status ==11 ||$status ==12 );
    if($sort =~ m/titleSort|author|pubDateSort|isbn|rid/){
        $sort    = "m.$sort ";
    }
    elsif( $sort =~ m/firstname|lastname|userbarcode/ && $status_odl){
        $sort    = "u.$sort ";
    }
    elsif($sort =~ m/callNumber|barcode|price/){
        $sort    = "i.$sort ";
    }
    elsif($sort =~ m/ondate/){
        $sort    = "s.$sort ";
    }
    else{
        $sort    = "s.ondate ";
        $sortDir = "DESC ";
    }
    
    my $sql="";
    my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'};
    my $coreCond = " opl_item i inner join opl_itemstatus s using(barcode)
           inner join  (select max(id) id from opl_itemstatus  group by barcode) t on s.status=? and s.id=t.id 
           inner join opl_marcRecord m on m.rid=i.rid ";
   if(!defined $incIll ||  $incIll ==0){
       $coreCond .= " and (m.tempIll is null || m.tempIll <>'ILL')";
   }
   if(!defined $incTmp ||   $incTmp==0){
       $coreCond .= " and (m.tempIll is null || m.tempIll  <> 'temporary')";
   }
   if(!defined $incEbook ||  $incEbook==0){
       $coreCond .= " and (m.recFormat <> 'ebook')";
   }
   if($status_odl ){
       my $s= $itemStatus->{$status};
        $coreCond .= " left outer join opl_loan l on  i.barcode=l.barcode  inner join opl_odl o on o.idloan=l.idloan && o.type='$s'
             left outer join opl_user u on u.uid=l.uid  " ;
   }

    my($count)=$dbh->selectrow_array("select count(*) from $coreCond ",undef ,$status); 

    my $found=0;
    my $itemList=[];
    if($count>0){
        $found=$count;
        $sql="select distinct i.rid,i.barcode ,m.titleSort,m.pubDateSort ";
        $sql .=",i.callNumber,i.price,s.ondate,m.title,m.author, m.tempIll ,m.pubDate, m.isbn as isbn";
        if($status_odl){
            $sql .=",u.firstname,u.lastname,u.grade,u.userbarcode ";
        }
        $sql .=" from $coreCond order by $sort $sortDir ";

        if(defined $pNum && defined $pagesize){
            my $pageoffset =$pagesize*($pNum-1);
            $sql .=" limit  $pageoffset, $pagesize";
        }
        my $sth=$dbh->prepare($sql);
        $sth->execute($status);
        while(my $rec=$sth->fetchrow_hashref){
            push @$itemList,$rec;
        }
        
    }
    
    return{total=>$found,found=>$found,itemList=>$itemList,rptType=>'current'};
}
############################################################
sub  getUserInfo{
    my($dbh,$status,$barcode)=@_;
    my $itemStatus={2=>'damaged' ,3=>'lost',11=>'claimReturned',12=>'claimNeverLoaned'};
    my $sql = "select u.firstname,u.lastname,u.grade,u.userbarcode   from opl_loan l inner  join opl_odl o on o.idloan=l.idloan && o.type=?  
               inner join opl_user u on u.uid=l.uid  where barcode=?";
    my $sth=$dbh->prepare($sql);            
    $sth->execute($itemStatus->{$status},$barcode);
    my ($firstname,$lastname,$grade,$userbarcode)=('','','','');
    ($firstname,$lastname,$grade,$userbarcode) = $sth->fetchrow_array;
    $sth->finish;

    return ($firstname,$lastname,$grade,$userbarcode);

   }

############################################################

sub  getAverageCopyrightDate{
   my ($dbh,$regexp,$f_Val, $searchOpt, $dateFrom, $dateTo) = @_;
   my $queryCon ="";
 
   if($regexp eq ""){
       $regexp = '(0[0-9][0-9])';
   }
   elsif($regexp eq  "outofrange"){
       $regexp = '[a-zA-Z\ ]+$';
   }
   #$queryCon = " callNumber regexp '($regexp)' ";
    
   if($searchOpt =~ m/prefix/i){
       $queryCon .= " callNumber regexp '(^| )($f_Val)( +)$regexp' ";
   }
   elsif($searchOpt  =~ m/lastyear/i){
       $queryCon ="";
   }
   else{
       $queryCon .= " callNumber regexp '^$regexp| $regexp|^\D+$regexp' ";
   }
   
   $queryCon .= " && R.pubDate is not null ";  
   if ($dateFrom ne '' ||  $dateTo ne ''){ 
    $queryCon .= " && I.dateImport >= '$dateFrom' && I.dateImport <='$dateTo' ";
   }
  
      my $sql = <<_STH_;    
select  R.pubDate, count(distinct I.rid) as cRecord, count(I.rid) as cHolding
from    opl_item as I
right join    opl_marcRecord as R on   I.rid = R.rid
where   $queryCon && I.barcode not regexp '\_\_\_' 
group by R.pubDate
_STH_
    
    my $sth = $dbh->prepare($sql);
     
    $sth->execute();
    my ($date, $cRecord, $cHolding, $sumYear, $recordCount, $holdingCount);
    $sumYear      = 0;
    $recordCount  = 0;
    $holdingCount = 0;
    my $dewey;
    while (($date, $cRecord, $cHolding) = $sth->fetchrow_array) {
        $dewey =~  m/(^| )([\d]{2})/;

        $dewey =$2;
        $date =~ s/^([\D]*)([\d]{4}).*$/$2/;
        if ($date =~ m/^[\d]{4}$/) {
            $sumYear      += $date * $cRecord;
            $recordCount += $cRecord;
            $holdingCount += $cHolding;
        }
    }
    $sth->finish;
    
    return ($recordCount) ?
        (floor($sumYear/$recordCount + 0.5), $recordCount, $holdingCount) : (0, 0, 0);
}
############################################################

sub _splitDeweyNumber {
    my ($dn) = @_;
    if(length($dn)==1){
        $dn= '00' .$dn;
    }
    elsif(length($dn)==2){
        $dn= '0' .$dn;
    }

    
    if ($dn =~ m/^([\d])([\d])([\d])$/) {
        return ($1, $2, $3);
    }
    return;
}
    
############################################################

sub _first_two_digits_matched {
    my ($b1, $b2, $b3, $e3) = @_;

    my $reg_exp;
    
    if ($b3 == 0 && $e3 == 9) {
        $reg_exp = "$b1$b2\[0-9\]";
    }
    elsif ($b3 == $e3) {
        $reg_exp = "$b1$b2$b3";#$begin;
    }
    else {
        $reg_exp = "$b1$b2\[$b3-$e3\]";
    }

    return $reg_exp;
}
############################################################

sub _first_digit_matched {
    my ($b1, $b2, $b3, $e2, $e3) = @_;

    my $reg_exp;

    ########################################
    if ($b3 != 0) {
        if ($b3 == 9) {
            $reg_exp  = "$b1$b2$b3";
        }
        else {
            $reg_exp  = "$b1$b2\[$b3-9\]";
        }
    }

    ########################################
    my $r       = $e2 - $b2;
    my $r_begin = $b2;
    my $r_end   = $e2;
    if ($b3 == 0) {
        $r++;
    }
    else {
        $r_begin++;
    }
    if ($e3 != 9) {
        $r--;
        $r_end--;
    }

    if ($r > 0) {
        my $re;
        if ($r == 1) {
            $re = "$b1$r_begin\[0-9\]";
        }
        else { # m > 1
            $re = "$b1\[$r_begin-$r_end\]\[0-9\]";
        }

        if ($reg_exp) {
            $reg_exp .= '|' . $re;
        }
        else {
            $reg_exp = $re;
        }
    }

    ########################################
    if ($e3 != 9) {
        my $e1  = $b1;
        my $end = "$e1$e2$e3";
        my $re;
        if ($e3 == 0) {
            $re = $end; #"$e1$e2$e3";
        }
        else {
            $re = "$e1$e2\[0-$e3\]";
        }

        if ($reg_exp) {
            $reg_exp .= '|' . $re;
        }
        else {
            $reg_exp = $re;
        }
    }

    return $reg_exp;
}
############################################################

sub _first_digit_unmatched {
    my ($b1, $b2, $b3, $e1, $e2, $e3) = @_;

    my $reg_exp;

    ########################################
    if ("$b2$b3" ne '00') {
        $reg_exp = createRegExp("$b1$b2$b3", $b1.'99');
    }

    ########################################
    my $r       = $e1 - $b1;
    my $r_begin = $b1;
    my $r_end   = $e1;
    if ("$b2$b3" eq '00') {
        $r++;
    }
    else {
        $r_begin++;
    }
    if ("$e2$e3" ne '99') {
        $r--;
        $r_end--;
    }

    if ($r > 0) {
        my $re;
        if ($r == 1) {
            $re = "$r_begin\[0-9\]{2}";
        }
        else {
            $re = "\[$r_begin-$r_end\]\[0-9\]{2}";
        }

        if ($reg_exp) {
            $reg_exp .= '|' . $re;
        }
        else {
            $reg_exp = $re;
        }
    }
    ########################################
    if ("$e2$e3" ne '99') {
        $reg_exp .= '|' . createRegExp($e1.'00', "$e1$e2$e3");
    }

    return $reg_exp;
}
############################################################


sub createRegExp {
    my ($begin, $end) = @_;
    my $reg_exp;

    my ($b1, $b2, $b3) = _splitDeweyNumber($begin);
    my ($e1, $e2, $e3) = _splitDeweyNumber($end);

    if ($b1 == $e1 && $b2 == $e2) { # ddx, ddy
        $reg_exp = _first_two_digits_matched($b1, $b2, $b3, $e3);
    }
    elsif ($b1 == $e1) { # dxy, duv
        $reg_exp = _first_digit_matched($b1, $b2, $b3, $e2, $e3);
    }
    else { # xyz, uvw
        $reg_exp = _first_digit_unmatched($b1, $b2, $b3, $e1, $e2, $e3);
    }
      return $reg_exp;
}
############################################################
#Thu, Jun 03, 2010 @ 15:42:59 EDT
# rpFilter = {
#                groupBy=>'', orderBy=>'', dateFrom=>'',dateTo=>'',incExcTmpIll=>'',sortWithinGroup=>''
#            }
sub rpt_onloanList{
    my ($dbh,$rpFilter,$grpList)=@_;
    my $retList=[];
    my $totalLoan=0;
    if(defined $rpFilter && defined $rpFilter->{'groupBy'} 
      && $rpFilter->{'groupBy'} =~ m/^grade$|^homeroom$|^teacher$|^username$|^idloan$/g )
    {
        $rpFilter->{'dateField'}='dateLoan' if(!defined $rpFilter->{'dateField'} || $rpFilter->{'dateField'} !~ m/^dateLoan$|^dateDue$/g);
 
        if(!$grpList){
            if($rpFilter->{'groupBy'} eq 'grade'){
                $grpList=getGroupList_grade($dbh,$rpFilter);
            }
            elsif($rpFilter->{'groupBy'} eq 'teacher'){
                $grpList=getGroupList_teacher($dbh,$rpFilter);
            }
            elsif($rpFilter->{'groupBy'} eq 'homeroom'){
                $grpList=getGroupList_homeroom($dbh,$rpFilter);
            }
            elsif($rpFilter->{'groupBy'} eq 'username'){
               $grpList=getGroupList_user($dbh,$rpFilter);
           }
       }
       ($totalLoan,$retList) =_onloanList($dbh,$rpFilter,$grpList);
     }
    return ($totalLoan,$retList);
    
}
#=========================================================
sub _onloanList{
    my ($dbh,$filter,$grpList)=@_;

    my $filtermap={
            grade=>     ['grade'],
            username=>  ['uid'],
            teacher=>   ['homeroom','buildingcode','teacher'],
            homeroom=>  ['homeroom','buildingcode','teacher']
     };

    my @retList=();
    my $totalLoan=0;
    my $loanCount=0;
    my $grpBy=$filter->{'groupBy'};
    foreach my $rec(@$grpList){
        foreach my $f(@{$filtermap->{$grpBy}}){
            $filter->{$f}=$rec->{$f};
        }
        $rec->{'loanList'}  =getOnloanItemList($dbh,$filter);
        $loanCount =scalar(@{$rec->{'loanList'}});
        $totalLoan +=$loanCount;
    }
    return ($totalLoan,$grpList);

}

#=========================================================
sub getGroupList_homeroom{
    my ($dbh,$filter)=@_;
    my @retList=();
    my $sqlCond="";
    my $sql=(<<_SQL_);
    select distinct homeroom, teacher,buildingcode
    from opl_user u inner join opl_loan l on u.uid=l.uid && l.dateReturn is null
_SQL_

    if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= "  " .$filter->{'dateField'} . "  >='" . $filter->{'dateFrom'} ."'";
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= " && " if($sqlCond ne '');
        $sqlCond .= "  " .$filter->{'dateField'} . "  <='" . $filter->{'dateTo'} ." 23:59:59'";
    }
    if(defined $filter->{'userType'} && $filter->{'userType'} ne ''){
         $sql .= " &&  u.categorycode =" . $filter->{'userType'}
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'}==1){
         $sqlCond .= " && l.dateDue < now() "
    }
    if($sqlCond ne ''){
        $sql .= " where $sqlCond ";
    }
    $sql .= " order by concat(homeroom,'zzzzzz'),concat(buildingcode,'zzzzzz'),concat(teacher,'zzzzzz')";

    my $sth= $dbh->prepare($sql);
    $sth->execute();
    while(my $rec=$sth->fetchrow_hashref){
        push @retList,$rec;
    }
    $sth->finish;
    return \@retList;
}
#=========================================================
sub getGroupList_teacher{
    my ($dbh,$filter)=@_;
    my @retList=();
    my $sqlCond="";
    my $sql=(<<_SQL_);

    select distinct homeroom, teacher,buildingcode
    from opl_user u inner join opl_loan l on u.uid=l.uid && l.dateReturn is null

_SQL_

    if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= "  " .$filter->{'dateField'} . "  >='" . $filter->{'dateFrom'} ."'";
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= " && " if($sqlCond ne '');
        $sqlCond .= "  " .$filter->{'dateField'} . "  <='" . $filter->{'dateTo'} ." 23:59:59'";
    }
    if(defined $filter->{'userType'} && $filter->{'userType'} ne ''){
         $sql .= " &&  u.categorycode =" . $filter->{'userType'}
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'}==1){
         $sqlCond .= " && l.dateDue < now() "
    }
    if($sqlCond ne ''){
        $sql .= " where $sqlCond ";
    }
    $sql .= " order by concat(teacher,'zzzzzz'),concat(buildingcode,'zzzzzz'),concat(homeroom,'zzzzzz')";

    my $sth= $dbh->prepare($sql);
    $sth->execute();
    while(my $rec=$sth->fetchrow_hashref){
        push @retList,$rec;
    }
    $sth->finish;
    return \@retList;
}
#=========================================================
sub getGroupList_grade{
    my ($dbh,$filter)=@_;
    my @retList=();
    my $sqlCond="";
    my $sql=(<<_SQL_);
    select distinct grade 
    from opl_user u inner join opl_loan l on u.uid=l.uid && l.dateReturn is null
    
_SQL_
  if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= " " . $filter->{'dateField'} . " >='" . $filter->{'dateFrom'} ."'";
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sqlCond .= " && " if($sqlCond ne '');
        $sqlCond .= "  " .$filter->{'dateField'} . "  <='" . $filter->{'dateTo'} ." 23:59:59'";
    }
    if(defined $filter->{'userType'} && $filter->{'userType'} ne ''){
         $sql .= " &&  u.categorycode =" . $filter->{'userType'}
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'}==1){
         $sqlCond .= " && l.dateDue < now() "
    }
    if($sqlCond ne ''){
        $sql .= " where $sqlCond ";
    }
    $sql .= " order by grade ,concat(buildingcode,'zzzzzz'),concat(homeroom,'zzzzzz')";

    my $sth= $dbh->prepare($sql);
    $sth->execute();
    while(my $rec=$sth->fetchrow_hashref){
        push @retList,$rec;
    }
    $sth->finish;
    return \@retList;
}
#=========================================================
#sub getAllItemOnLoanNoGrp{
sub rpt_onloanList_WO_userInfo{
    my ($dbh,$filter)=@_;
    my @retList=();
    my @sqlCondVals=();
    my $sql=(<<_SQL_);
    select i.price,i.callNumber ,
           l.idloan,l.barcode,l.dateLoan,l.dateDue,l.dateDue<now() as overDue,
           m.title,m.author,m.pubDate, m.pubName,m.tempIll
    from   opl_loan l 
           inner join opl_item as i on l.barcode=i.barcode
           inner join opl_marcRecord as m on m.rid=i.rid
    where  l.dateReturn is null      
_SQL_

    $filter->{'dateField'}='dateLoan' if(!defined $filter->{'dateField'} || $filter->{'dateField'} !~ m/^dateLoan$|^dateDue$/g);
    if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  >=? ";
        push  @sqlCondVals,$filter->{'dateFrom'} ;
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  <=? " ;
        push  @sqlCondVals,$filter->{'dateTo'} ." 23:59:59 " ;
    }
    
    if(defined $filter->{'incExcTmpIll'} && $filter->{'incExcTmpIll'} eq 'exclusion'){
        $sql .= " &&  (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'}==1){
         $sql .= " && l.dateDue < now() "
    }

    if($filter->{'sortBy'} =~ m/^teacher$|^homeroom$|^buildingcode$|^grade$|^title$|^author$|^pubDate$|^pubName$|^barcode$|^callNumber$|^dateLoan$|^dateDue$|^deltaDueDate$/g){
        $sql .= " order by $filter->{'sortBy'} ,m.author, m.titleSort";
    }
    elsif($filter->{'sortBy'} eq 'title'){
        $sql .= " order by m.titleSort ,m.author, m.titleSort";
    }
    else {
        $sql .= " order by callNumber, m.author, m.titleSort ";
    }
   
    my $sth= $dbh->prepare($sql);
    $sth->execute(@sqlCondVals);
    while(my $rec=$sth->fetchrow_hashref){
        push @retList,$rec;
    }
    $sth->finish;
    return (scalar(@retList),\@retList);
}
#=========================================================
sub getGroupList_user{
    my ($dbh,$filter)=@_;
    my @retList=();
    my $sql=(<<_SQL_);
    select distinct u.sid,u.uid,lastname,firstname,homeroom, teacher,buildingcode,grade,email ,yeargraduation
    from opl_user u inner join opl_loan l on u.uid=l.uid && l.dateReturn is null
    where l.uid is not null
_SQL_

    if(defined $filter->{'idlist'} && $filter->{'idlist'} ne ''){
        $sql .= " &&   u.uid in (" . $filter->{'idlist'} .") ";
    }
    if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  >='" . $filter->{'dateFrom'} ."'";
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  <='" . $filter->{'dateTo'} ." 23:59:59'";
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'}==1){
         $sql .= " && l.dateDue < now() "
    }
    if(defined $filter->{'userType'} && $filter->{'userType'} ne ''){
         $sql .= " &&  u.categorycode =" . $filter->{'userType'}
    }
  
  
    if(defined $filter->{'groupOrderBy'} && $filter->{'groupOrderBy'} eq 'room'){
        $sql .= " order by concat(homeroom,'zzzzzz'), concat(lastname,firstname,'zzzzzz')";
    }
    elsif(defined $filter->{'userSortBy'} && $filter->{'userSortBy'} ne ''){
        $sql .=  " order by $filter->{'userSortBy'}, concat(lastname,firstname,'zzzzzz'),concat(homeroom,'zzzzzz')";
    }
    else{
        $sql .= " order by concat(lastname,firstname,'zzzzzz'),concat(homeroom,'zzzzzz')";
    }

    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        if($rec->{'email'} eq ''){
            my $email=getGuardianEmail($dbh,$rec->{'uid'});
            $rec->{'gemail'} = $email->{'gemail'};
        }

        if(defined $filter->{'sendEmail'} && $filter->{'sendEmail'}){
                       
            if($rec->{'email'} ne '' || $rec->{'gemail'} ne ''){
                $rec->{'count'} = $count++;
                push @retList,$rec ;
            }
        }
        else{
            $rec->{'count'} = $count++;
            push @retList,$rec;
        }
    }
    $sth->finish;
    return \@retList;
}
####################################################
sub getGuardianEmail{
    my ($dbh,$uid)=@_;
    my $email={};
    my $sth=$dbh->prepare("select gemail gemail from opl_guardian where uid=$uid limit 1");
    $sth->execute();
    $email =$sth->fetchrow_hashref;
    $sth->finish;
    return $email;	
    
}

#=========================================================
sub getOnloanItemList{
    my($dbh,$filter)=@_;
    my $sortBy= $filter->{'sortWithinGroup'};
    my @loanList=();
    my @sqlCondVals=();
    my $sql=(<<_SQL_);
    select u.uid,u.lastname,u.firstname,trim(u.teacher) as teacher ,trim(u.homeroom) as homeroom ,
           u.grade,trim(u.buildingcode) as buildingcode,u.username, u.*,
           i.price,i.callNumber ,
           l.barcode,l.dateLoan,l.dateDue,l.dateDue<now() as overDue, 
           if(l.dateDue <now(),to_days(now()) - to_days(dateDue),0) as deltaDueDate,
           m.title,m.author,m.pubDate, m.pubName,m.tempIll
    from   opl_user u inner join  opl_loan l using(uid) 
           inner join opl_item as i on l.barcode=i.barcode
           inner join opl_marcRecord as m on m.rid=i.rid
    where  l.dateReturn is null      
_SQL_

    if(defined $filter->{'dateFrom'} && $filter->{'dateFrom'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  >=? ";
        push  @sqlCondVals,$filter->{'dateFrom'} ;
    }
    if(defined $filter->{'dateTo'} && $filter->{'dateTo'} =~ m/^\d{4}-\d{1,2}-\d{1,2}/){
        $sql .= " &&  " .$filter->{'dateField'} . "  <=? " ;
        push  @sqlCondVals,$filter->{'dateTo'} ." 23:59:59 " ;
}
    
    if(defined $filter->{'incExcTmpIll'} && $filter->{'incExcTmpIll'} eq 'exclusion'){
        $sql .= " &&  (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    if(defined $filter->{'overdue'} && $filter->{'overdue'} ==1){
        $sql .= " &&   l.dateDue < now() ";
    }

    if(defined $filter->{'uid'} && $filter->{'uid'} ne ''){
        $sql .= " &&  u.uid= ?  " ;
        push  @sqlCondVals,$filter->{'uid'} ;
    }
    if(defined $filter->{'userType'} && $filter->{'userType'} ne ''){
         $sql .= " &&  u.categorycode =? " ;
         push @sqlCondVals,$filter->{'userType'};
    }
    
    if(defined $filter->{'teacher'}){
        $sql .= " &&  u.teacher=?  ";
        push  @sqlCondVals,$filter->{'teacher'} ;
    }
    if(defined $filter->{'grade'} ){
        $sql .= " &&  u.grade=? ";
        push  @sqlCondVals,$filter->{'grade'} ;
    }
    if(defined $filter->{'homeroom'} ){
        $sql .= " &&  homeroom =? ";
        push  @sqlCondVals,$filter->{'homeroom'} ;
    }
    if(defined $filter->{'buildingcode'}){
        $sql .= " &&  buildingcode=?  ";
        push  @sqlCondVals,$filter->{'buildingcode'} ;
    }
    if($sortBy =~ m/^teacher$|^homeroom$|^buildingcode$|^grade$|^title$|^author$|^pubDate$|^pubName$|^barcode$|^callNumber$|^dateLoan$|^dateDue$|^deltaDueDate$/g){
        $sql .= " order by $sortBy ";
    }
    elsif($sortBy eq 'dewey'){
        $sql .= " order by callNumber ";
    }
    elsif($sortBy eq 'title'){
        $sql .= " order by m.titleSort ";
    }    else{
        $sql .= " order by u.lastname,u.firstname, homeroom";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute(@sqlCondVals);
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
        $rec->{'dateDue'} = date_text($rec->{'dateDue'}, 0);
        push @loanList,$rec;
    }
    $sth->finish;

    return \@loanList;

}
################################################################################
sub rpt_preDueEmailNotice{
    my($dbh,$nDays)=@_;
    my ($dateDue)=$dbh->selectrow_array("select DATE_ADD(CURRENT_DATE, INTERVAL ? DAY)",undef,$nDays);
    my $tmpl=_getTemplate("/htdocs/theme/opals/circ/","email_preDueNotice.inc");
    my $msgMap=loc_getMsgFile('/circ/notice.msg',{predue_subject=>{dateDue=>$dateDue},preDue_msgTop=>{dateDue=>$dateDue,nDays=>$nDays}});
    loc_write($tmpl,$msgMap);
    my $libType = Opals::Context->preference('libraryType');
    if($libType eq "k-12"){
        $tmpl->param(isK_12=>1);
    }
    $tmpl->param(incTitle  =>1,incPrice=>1);
    my $email={subject=>$msgMap->{'predue_subject'},reqType=>'preDueNotice'};
    my $sth=$dbh->prepare("select distinct l.uid 
                          from opl_loan l  inner join opl_user u on u.uid=l.uid
                          left outer join  opl_emailRequest e on e.uid=l.uid && DATE(e.requestDate)=CURRENT_DATE && e.reqType='preDueNotice'
                          where dateReturn is null && date(dateDue)=DATE_ADD(CURRENT_DATE, INTERVAL ? DAY) && e.uid is null && u.email is not null") ;
    $sth->execute($nDays);
    while(my ($uid) =$sth->fetchrow_array){
         my $noticeData=_getNoticeData($dbh,$uid,$nDays);
         while (my ($key, $value) = each %$noticeData){
             $tmpl->param($key=>$value);
         }
         $email->{'email'} =$noticeData->{'email'};
         $email->{'content'} =$tmpl->output;
         mail_enqueueMail($dbh,$uid,$email);
    }
}
################################################################################
sub rpt_circTrans {
    my($dbh,$from,$to,$offset,$size)=@_;
    my $statusMap={
        0=>'missing',
        1=>'on shelf',
        3=>'lost'
    };
    my $transList=[];
    my ($libCode)=$dbh->selectrow_array("select val from opl_preference where var='libCode'");
    my ($countLoan)   =$dbh->selectrow_array("select count(*) from opl_loan where dateLoan between ? and ?",undef,$from,$to);
    my ($countRenewal)=$dbh->selectrow_array("select count(*) from opl_loan where dateRenewal between ? and ?",undef,$from,$to);
    my $total=$countLoan +$countRenewal; 
    my $sql_status="select status from opl_itemstatus where barcode=? && date_add(ondate,interval 10 second) <? order by id desc limit 1";

    my $sql=<<_SQL_;
    select i.rid,l.barcode,l.dateLoan as transDate,if(uid=0 && type is null,'internal use','check-out') as transType
    from opl_loan l inner join opl_item i using(barcode)
    where dateLoan between ? and ?
    union
    select i.rid,l.barcode,l.dateRenewal as transDate,'renewal' transType
    from opl_loan l inner join opl_item i using(barcode)
    where dateRenewal between ? and ?
    order by transDate
_SQL_
    if(defined $offset && $offset>=0 && defined $size && $size >0){
        $sql .= " limit $offset,$size ";
    }
    my $sth=$dbh->prepare($sql);
    $sth->execute($from,$to,$from,$to);
    while( my $t =$sth->fetchrow_hashref){
        my ($s)=$dbh->selectrow_array($sql_status,undef,$t->{'barcode'},$t->{'transDate'});
        if($t->{"transType"} eq 'renewal'){
            $t->{"itemStatus"}="checked out";
        }
        elsif($s && defined $statusMap->{$s}){
            $t->{"itemStatus"} =$statusMap->{$s};
        }
        else{
            $t->{"itemStatus"}="on shelf";
        }
        $t->{"libCode"}=$libCode;
        push @$transList,$t;
    }
    return {totalTrans=>$total,transList=>$transList};

}
################################################################################
sub rpt_holdTrans {
    my($dbh,$from,$to,$offset,$size)=@_;
    my $transList;
    my ($libCode)=$dbh->selectrow_array("select val from opl_preference where var='libCode'");
    my ($total)=$dbh->selectrow_array(<<_SQL_);
       select count(distinct rid) from opl_hold inner join opl_reserve r using(idReserve)
       where dateHold between '$from' and '$to'
_SQL_
           
    my $sql=<<_SQL_;
    select r.rid,count(rid) currentHolds,m.title,m.author,m.pubDate,m.isbn
    from  opl_hold h inner join opl_reserve r using(idreserve)
          inner join opl_marcRecord m using(rid)
    where dateHold between ? and ? group by r.rid
_SQL_

    if(defined $offset && $offset>=0 && defined $size && $size >0){
        $sql .= " limit $offset,$size ";
    }
    my $sth=$dbh->prepare($sql);
    $sth->execute($from,$to);

    my $marcXmlParser=Opals::MarcXmlParser->new();
    while(my $h=$sth->fetchrow_hashref){
        $h->{"libCode"}=$libCode;
        $h->{"otherLibCode"}="";
        my $rec = $marcXmlParser->getRecInfoGeneral(getRecordXml($h->{'rid'}));
        if($rec){
            $h->{'title'}=$rec->{'title'};
            $h->{'author'}=$rec->{'author'};
            $h->{'pubDate'}=$rec->{'datePublication'};
            $h->{'pubPlace'}=$rec->{'placePublication'};
            $h->{'pubName'}=$rec->{'namePublisher'};
            my @isbnList=();
            foreach my $e(@{$rec->{'isbn'}}){
                push @isbnList,$e->{'item'};
            }
            $h->{'isbn'}=join(",", @isbnList);

        }
        push @$transList,$h;
    }
    return {totalTrans=>$total,transList=>$transList}; 
}
#####################################################
sub rpt_itemRecord{
    my ($dbh,$itemTypeList,$offset,$size)=@_;
    my $statusMap={
        0=>'missing',
        1=>'on shelf',
        3=>'lost'
    };
    my @typeCondArr=();
    foreach my $t(@$itemTypeList){
        push @typeCondArr," typeId=?";
    }
    my $typeCond= join " || ", @typeCondArr;
    my ($libCode)=$dbh->selectrow_array("select val from opl_preference where var='libCode'");
    my $sql_status="select status from opl_itemstatus where barcode=? order by id desc limit 1";

    my $sql=<<_SQL_;
    select i.rid, i.barcode, i.price,i.callNumber,i.dateImport, i.available , i.location, m.recFormat,
           max(l.dateLoan) lastCircDate ,max(dateRenewal) lastRenewalDate ,count(barcode) circCount,sum(renewalCount) renewalCount,
           max(dateReturn) lastCheckin
    from opl_item i left outer join  opl_loan l using(barcode) inner join opl_marcRecord m using(rid)
    where barcode not regexp '^___' && ($typeCond) group by barcode
_SQL_
 if(defined $offset && $offset>=0 && defined $size && $size >0){
        $sql .= " limit $offset,$size ";
    }

   my $sth =$dbh->prepare($sql);
   $sth->execute(@$itemTypeList);
   my $rs=[];
   my ($total)=$dbh->selectrow_array("select count(*) from opl_item where barcode not regexp '^___' && ($typeCond)",undef,@$itemTypeList); 
   while(my $r=$sth->fetchrow_hashref){
       $r->{"libCode"} =$libCode;
       $r->{"circCount"}=0 if(!defined $r->{"lastCircDate"});
       $r->{"renewalCount"}=0 if(!defined $r->{"lastRenewalDate"});
       if($r->{'available'}){
           $r->{'itemStatus'}=($r->{'lastCircDate'} >$r->{'lastCheckin'})?'checked out':'on shelf';
       }
       else{
            my ($s)=$dbh->selectrow_array($sql_status,undef,$r->{'barcode'});
            if($s && defined $statusMap->{$s}){
                $r->{"itemStatus"} =$statusMap->{$s};
            }
       }

       push @$rs,$r;
   }
   return {totalTrans=>$total,transList=>$rs};
 }
#####################################################
sub getRecordXml {
    my ($rid) = @_;
    
    my $zRoot   = Opals::Context->config('zRoot');
    my $zPort   = Opals::Context->config('zPort');
    my $zDatabase = Opals::Context->config('zDatabase');
    my $fname     = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000) ."/$rid.xml";
    my $xml="";
    if(-f $fname){
        open FILE,"<$fname";
        while(<FILE>){
            $xml .=$_;
        }
        close FILE;
    }
    return $xml;

}


################################################################################
sub _getNoticeData{
    my ($dbh,$uid,$nDays)=@_;
    my $data=$dbh->selectrow_hashref("select lastname,firstname,homeroom,teacher,grade,email from opl_user where uid=?",undef,$uid);
    my $sth=$dbh->prepare("select  i.barcode,i.callNumber,i.price,m.title,date(l.dateLoan) dateLoan,date(l.dateDue) dateDue 
                           from opl_loan l inner join opl_item i using(barcode) 
                           inner join opl_marcRecord m using(rid) 
                           where l.uid=? && l.dateReturn is null && date(l.dateDue)=DATE_ADD(CURRENT_DATE, INTERVAL ? DAY)"); 
    if(!defined $data->{'email'} ||  $data->{'email'} eq '' ){
       $data->{'email'} = getGuardianEmail($dbh,$uid);
    }
    $sth->execute($uid,$nDays);
    $data->{"loanList"}=[];
    while(my $l=$sth->fetchrow_hashref){
        push @{ $data->{"loanList"}},$l;
    }
    return $data;
}
################################################################################
sub _getTemplate{
    my($path,$fname)=@_;
    my $rootDir   = Opals::Context->config('rootDir');
    my $template = HTML::Template->new(    
                filename            => $fname ,
                path                => "$rootDir/$path",
                global_vars         => 1,
                die_on_bad_params   => 0,
                cache               => 1,
                shared_cache        => 0,
                loop_context_vars   => 1,);
    $template->param(http_host=>$ENV{'HTTP_HOST'});
    return $template;    
}

################################################################################