#!/usr/bin/perl

#use utf8;
use strict;
use CGI;


use Opals::Context;
use Opals::Constant;

use Date::Calc::Object qw(
    :all
);
use POSIX qw(
    floor
);
use Time::localtime;

use Opals::Fines qw(
    fine_getFineList
    fine_getFineRate
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);

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



use Opals::Circulation qw(
    circ_userListLoanStat
    circ_userListLoan
    circ_userListReserve
    circ_GetLostNumber
    circ_GetDamagedNumber
    circ_lostDeclarating
    circ_record_ODL

);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);
use JSON;

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

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/userCircStatsList.tmpl',
            reqPermission   => 'circ_loan|payment|rpt_circ|rpt_user',
        }
);
my $syspref = tmpl_preference($dbh);
my $chargeFine   = $syspref->{'charge_overdue'};
my $chargeDamage = $syspref->{'charge_damage'};
my $chargeLost   = $syspref->{'charge_lost'};
my $loginuid     = $template->param('curUserId');
my $syspref      = tmpl_preference($dbh);
#my $patronItemPrivacy  = $syspref->{'patronItemPrivacy'};
my $patronItemPrivacy  = ($syspref->{'patronItemPrivacy'})? $syspref->{'patronItemPrivacy'} : 1;

my @cookieList = (@$cookie);
my $debugStr;
#ha
my $dateDue;
my @idloanArr;
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
my $dFirst = Opals::Context->preference('dateFirst');
my $dLast  = Opals::Context->preference('dateLast');
#ha
$template->param(
        hlpUrl     => Opals::Constant->getHlpUrl("userCircStatsList"),
        libType    =>$syspref->{'libraryType'}
   );

    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    my $dateFrom= $input->{'statFrom'};
    my $dateTo  = $input->{'statTo'};
    my $sort    = $input->{'sort'};
   
    my $field    = $input->{'fieldOpt'};

    my $dir     = $input->{'dir'};
    $dir       = '' if ( !$dir );

    if ( !$sort ){
        $sort = 'totalCirc' ;
        $dir  ="desc";
    }
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    
    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

    $template->param(statFrom => $dateFrom);
    $template->param(statTo   => $dateTo);
    $template->param(field    => $dateTo);
    $template->param(userStat => 1);
    
  
    my $op= $input->{'op'};
    if(defined $op && $op eq 'csv'){
        my $userList = GetUserStatList($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum,0);
        getUserStatList2Export($userList);
    }
    else{ 
        my $userList = GetUserStatList($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize);
        my $reportDataJson =to_json($userList);
        $template->param(reportData=>$reportDataJson);
        MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo);
        $template->param(
            userList => $userList,
            sort    => $sort,
            dir    => $dir,
            pNum    => $pNum
        );

        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"
           );
        }

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

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

        #tmpl_write($dbh, $cgi, \@cookieList, $template);
    }
     

#---------------------------------------------------------
sub GetUserStatList {
    my ($dbh, $dateFrom, $dateTo, $sort, $dir, $pNum, $pagesize) = @_;

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


    my $sql ="select  u.uid,u.sid,u.userbarcode,concat(lastname,', ', firstname) fullname, u.firstname,u.lastname,u.middlename,u.title,u.email,
           u.homeroom,u.teacher,u.grade,u.yeargraduation,u.program,u.studies,u.cellPhone,u.workPhone,u.phone,
          l.idloan, l.barcode,i.rid,
          l.dateLoan, count(i.rid) as totalCirc
from      opl_user u                    
          inner  join opl_loan l on u.uid=l.uid 
          inner join opl_item i on i.barcode = l.barcode    
where     l.dateLoan between '$dateFrom' and  '$dateTo'    
group by  u.uid
order by  $sort $dir";

if($pagesize && $pagesize >0){
   $sql .= " limit   $pageoffset, $pagesize";
}

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    
    my $pNum = -1;
    my @userList =();
 
    while (my $rec = $sth->fetchrow_hashref) {
        $rec->{'odd'} = $pNum%2;
        $pNum++;        
        push @userList,$rec;
    }
    $sth->finish;
    return \@userList;

}
#----------------------------------------------------------
sub MakePages
{
    my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo) = @_;
     my $sql_count = <<_STH_;
select    count(distinct u.uid) as count
from      opl_user u                    
          inner  join opl_loan l on u.uid=l.uid 
          inner join opl_item i on i.barcode = l.barcode    
where     l.dateLoan between '$dateFrom' and  '$dateTo'  
_STH_
    my $sth = $dbh->prepare($sql_count);
    $sth->execute();
    my ($countRecord) = $sth->fetchrow_array;
    ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

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


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

sub getUserStatList2Export{
    my ($userList)=@_;
    my $msgValMap ={};
    my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
    my $userFields_map={ 
            "k-12"=>
                        [
                        {name=>'userbarcode',   header=>uc($msgMap->{'userIdTxt'})},
                        {name=>'firstname',     header=>uc($msgMap->{'firstNameTxt'})},
                        {name=>'lastname',      header=>uc($msgMap->{'lastNameTxt'})},
                        {name=>'teacher',       header=>uc($msgMap->{'teacherTxt'})},
                        {name=>'homeroom',      header=>uc($msgMap->{'homeroomTxt'})},
                        {name=>'grade',         header=>uc($msgMap->{'gradeTxt'})},
                        {name=>'totalCirc',     header=>'NUM OF LOANS'}],
            "academy"=>
                        [
                        {name=>'userbarcode',   header=>uc($msgMap->{'userIdTxt'})},
                        {name=>'firstname',     header=>uc($msgMap->{'firstNameTxt'})},
                        {name=>'lastname',      header=>uc($msgMap->{'lastNameTxt'})},
                        {name=>'email',         header=>uc("email")},
                        {name=>'program',       header=>uc("program")},
                        {name=>'studies',       header=>uc("studies")},
                        {name=>'totalCirc',     header=>'NUM OF LOANS'}],
            "public"=>
                        [
                        {name=>'userbarcode',   header=>uc($msgMap->{'userIdTxt'})},
                        {name=>'firstname',     header=>uc($msgMap->{'firstNameTxt'})},
                        {name=>'lastname',      header=>uc($msgMap->{'lastNameTxt'})},
                        {name=>'email',         header=>uc("email")},
                        {name=>'phone',         header=>uc("phone")},
                        {name=>'cellphone',     header=>uc("cell phone")},
                        {name=>'totalCirc',     header=>'NUM OF LOANS'}]

    };
   my $libType =$syspref->{"libraryType"};
   my $userFields =$userFields_map->{$libType};


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


}