#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);

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

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);
use Opals::Date qw(
    date_f005
);
use  Opals::ILL qw(ill_getOpenIllReqCount);

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }
use JSON;
my $cgi      = CGI->new;
my $input    = $cgi->Vars();

my $rmdNotePermssionMap={
    ratingToApprove=>{permissions=>['rating'],
                      url        =>'/bin/rating/reviewUserComment'
                     },
    odNotice       =>{permissions=>['circ_loan','circ_return'],
                              url=>'/bin/circ/odnotice'
                     },
    bookingReserve =>{permissions=>['circ_rsrv_self','circ_rsrv'],
                              url=>'/bin/search/requestBookList'
                     },
    illRequest     =>{permissions=>['circ_ill_filter'],
                              url=>'/bin/ill/list?ill_filter=pending'
                     },                 
    illOpenReqCount =>{permissions=>['circ_ill_filter'],
                              url=>'/bin/ill/illList?pageId=illResponses',
                              target=>'unionILL'
                     },      
#    noticeEmail    =>{permissions=>['notice'],
#                              url=>'/bin/report/emailList'
#                     },  
    marcImport       =>{permissions=>['marc_edit'],
                              url=>'/bin/record/import'
                     }, 
    requestBook      =>{permissions=>[],
                              url=>'url_report_reqReserveList'
                     },                  
    fiscalYear       =>{permissions=>['pref_edit'],
                              url=>'/bin/util/cdate'
                     }                 
};



my ($status,$errorCode,$errorMsg)    ;
my ($permission, $cookie, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'ajax/portlet/getRemindNotes.tmpl',
        reqPermission   => 'circ_loan|circ_return|rating|circ_rsrv_self|circ_rsrv|marc_edit|circ_ill_filter|notice|pref_edit',
    }
);
if ($permission && ($permission->{'circ_loan'}       || $permission->{'circ_return'}      ||
                    $permission->{'rating'}          || $permission->{'circ_rsrv_self'}   ||
                    $permission->{'circ_rsrv'}       || $permission->{'marc_edit'}        ||
                    $permission->{'circ_ill_filter'} || $permission->{'notice'}           || 
                    $permission->{'pref_edit'})) {
    my $syspref = tmpl_preference($dbh);
    my $remindNotes =getRemindNotes($dbh,$permission,$cgi,$input);
    $template->param(remindNotes => $remindNotes);
    
  
}
 
tmpl_write($dbh, $cgi, $cookie, $template);

#------------------------------------------------------------------------------
sub getRemindNotes{
    my($dbh,$permission,$cgi,$input)=@_;
    my $notes =[];
    my $rmd2get={};
    my $msgValMap={};
    my $showRem=1;
    while ( my ($rmd, $val) = each %$rmdNotePermssionMap )
    {   my $hasPerm=0;
        my $c=0;   
        foreach my $p(@{$val->{'permissions'}}){
            if($permission->{$p}){
                $hasPerm=1;
                last;
            }
        }
        if($hasPerm){
            if($rmd eq 'ratingToApprove'){
                $c =_getRatingToApprove($dbh);
                $msgValMap->{$rmd} ={numCmmt2Aprrove=> $c};
            }
            elsif($rmd eq 'odNotice'){
                $c =_getOdNotice($dbh);
                $msgValMap->{$rmd} ={numOD          => $c};
            }
            elsif($rmd eq 'bookingReserve'){
                $c =_getBookingReserve($dbh);
                $msgValMap->{$rmd} ={numRsvBooking  => $c};
            }
            elsif($rmd eq 'illRequest'){
                $c =_getILLRequest($dbh,$cgi,$input);
                $msgValMap->{$rmd} ={numILLRequest  => $c};
            }
            elsif($rmd eq 'illOpenReqCount'){
                $c =ill_getOpenIllReqCount($dbh);
                $msgValMap->{$rmd} ={numIllReqCount  => $c};
            }
              elsif($rmd eq 'noticeEmail'){
                my ($numODEmailSent,$numODEmailWaiting, $numODEmailRejected) = (0,0,0);
                ($numODEmailSent,$numODEmailWaiting,$numODEmailRejected) =_getNoticeEmail($dbh);
                $c = 1 if($numODEmailSent < $numODEmailSent + $numODEmailWaiting + $numODEmailRejected);
                $msgValMap->{$rmd} ={numODEmailSent      => $numODEmailSent,
                                     numODEmailWaiting   => $numODEmailWaiting, 
                                     numODEmailRejected  => $numODEmailRejected
                                     };
            }
            elsif($rmd eq 'marcImport'){
                my $countTotal=0;my $impDate='';
                ($c,$countTotal,$impDate) =_getPendingImport($dbh);

                $msgValMap->{$rmd} ={ numPending    => $c, 
                                      countTotal    => $countTotal,
                                      importDate    => substr($impDate,0,10)
                                      };
            }
            elsif($rmd eq 'fiscalYear'){
                $c =_getFiscalYear($dbh);
                $msgValMap->{$rmd} ={needChangeFYear  => $c};
            }

            $showRem =0 if($c <1);
            if($c>0){
                my $note ={name=>$rmd, url=>$val->{'url'} ,showRem =>$showRem};
                $note->{'target'}=$val->{'target'} if($val->{'target'});            
                push @$notes,$note;
            }
        }
    }
   my $rmdMsgMap  =loc_getMsgFile('util/remider.msg',$msgValMap);
   foreach my $n(@$notes){
        $n->{'note'} =$rmdMsgMap->{$n->{'name'}};
   }
   return $notes;
}
#------------------------------------------------------------------------------
sub _getFiscalYear{
    my($dbh)=@_;
    my $queryStr = "select count(distinct var) from opl_preference where var='dateLast' && val  <= now()";
    my ($count) = $dbh->selectrow_array($queryStr);
    return $count;
}#------------------------------------------------------------------------------
sub _getRatingToApprove{
    my($dbh)=@_;
    my $queryStr = "select count(distinct r.rid) from opl_cmntRating r  inner join opl_item i on i.rid=r.rid where  i.barcode not regexp '^\_\_\_' && reviewed=0 && deleted=0";
    my ($count) = $dbh->selectrow_array($queryStr);
    return $count;
}
#------------------------------------------------------------------------------
sub _getOdNotice{
    my($dbh)=@_;
    my $queryStr = "select count(idloan) from opl_loan l inner join opl_user u using(uid) where dateReturn is  null && dateDue<now()";
    my ($count) = $dbh->selectrow_array($queryStr);
    return $count;
}

#------------------------------------------------------------------------------
sub _getBookingReserve_bk{
    my($dbh)=@_;
    my $prevDays = 1;
    my $queryStr = "select count(bookingId) from opl_bookingRequest  where status = 'processing' &&   DATE_ADD(reserveTo ,INTERVAL $prevDays DAY)>=now()";
    my ($count) = $dbh->selectrow_array($queryStr);

    return $count;
}
#------------------------------------------------------------------------------
sub _getBookingReserve{
    my($dbh)=@_;
    my $prevDays = 1;
    my $queryStr = "select count(id) from opl_requestReserve r inner join opl_marcRecord m using(rid)  
                     where status = 'waiting' &&  dateExpiry>now()  order by id";
    my ($count) = $dbh->selectrow_array($queryStr);

    return $count;
}


#------------------------------------------------------------------------------
sub _getILLRequest{
    my($dbh,$cgi,$input)=@_;
    my $sql = <<_SQL_;
select  count(*)
from    opl_studentILL
where    dApproval is null && 
         dRejection is null &&
         dExpiry>=now()
_SQL_

    my ($count) = $dbh->selectrow_array($sql);

    return $count;
}

#------------------------------------------------------------------------------
sub _getNoticeEmail{
    my ($dbh)=@_;
    my ($numODEmailSent,$numODEmailWaiting,$numODEmailRejected) = (0,0,0);
   my $sth = $dbh->prepare(<<_SQL_);
select  r.status status,r.eid 
from    opl_emailRequest r 
            inner join opl_emailDetails d using(eid)          
            inner join opl_user u on u.uid=r.uid
_SQL_

    $sth->execute();

    while (my $odEmail = $sth->fetchrow_hashref) {
        $numODEmailSent      += 1 if($odEmail->{'status'} eq 'sent') ;
        $numODEmailWaiting   += 1 if($odEmail->{'status'} eq 'waiting') ;
        $numODEmailRejected  += 1 if($odEmail->{'status'} eq 'rejected') ;
    }

    $sth->finish;
    return ($numODEmailSent,$numODEmailWaiting,$numODEmailRejected);
}


#------------------------------------------------------------------------------
sub _getPendingImport{
    my($dbh)=@_;
    my ($mesgDigest, $iid, $fileDuplicated);
    my $catType = Opals::Context->config('type');

    my $sth = $dbh->prepare(<<_STH_);
select  m.*, 
        u.uid, u.firstname, u.lastname, 
        (countTotal - countProcessed) as countImporting
from    opl_marcImport as m left outer join opl_user as u on m.uid = u.uid
order by iid desc limit 1
_STH_
$sth->execute();


my $sth_mergeCount = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_marcDuplicate
where   iid = ?
_STH_
my ($count,$countTotal, $dateUpload) = (0,0,'');
    while (my $iInfo = $sth->fetchrow_hashref) {
         
        $sth_mergeCount->execute($iInfo->{'iid'});
        ($count) = $sth_mergeCount->fetchrow_array;
        if ($iInfo->{'countTotal'} == $iInfo->{'countProcessed'}) {
            $iInfo->{'countIgnored'} =
                $iInfo->{'countTotal'}
              - $iInfo->{'countImported'}
              - $iInfo->{'countMerged'}
              - $iInfo->{'countPending'};
        }
        $countTotal = $iInfo->{'countTotal'};
        $dateUpload = $iInfo->{'dateUpload'};       
    }


        $sth->finish;
      #return (2,7,'2011-07-14');
      return ($count,$countTotal,$dateUpload);
}