#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    ceil
);
use Time::localtime;
use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

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


use Opals::Locale qw(
    loc_getMsgFile
    loc_write
    
);

my @csvHeader=qw(
    SID
    USERBARCODE
    TITLE
    FIRSTNAME
    LASTNAME
    ADDRLINE1
    ADDRLINE2
    CITY
    ZIP
    STATE
    PHONE
    CELLPHONE
    FAX
    EMAIL
    BIRTHDAY
    GENDER
    NOTES

    DATE
    DESCRIPTION
    BALANCE

);

my @userFields=qw(
    sid
    userbarcode
    title
    firstname
    lastname
    addrLine1
    addrLine2
    city
    zip
    state
    phone
    cellphone
    fax
    email
    birthday
    gender
    notes

    date
    description
    balance
    );



my $libTye = Opals::Context->preference('libraryType');
if($libTye eq 'k-12'){
  splice  @userFields,15,'buildingcode','homeroom','teacher','grade','yeargraduation';
  splice  @csvHeader, 15,'BUILDINGCODE','HOMEROOM','TEACHER','GRADE','YEARGRADUATION';
}
elsif($libTye eq 'academy'){
  splice  @userFields,15,'program','studies';
  splice  @csvHeader, 15,'PROGRAM','STUDIES';
}


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/fineReport_prt.tmpl',
            reqPermission   => 'rpt_fine|fine',
        }
);
   my $tm = localtime;
    my $dateToday = sprintf("%04d-%02d-%02d", 
                            $tm->year+1900, 
                            ($tm->mon)+1, 
                            $tm->mday);


    my  $uidList = $input->{'uidList'};
    my  $prtType = $input->{'prtType'};
    my  $selCount = $input->{'selCount'};


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

    my $sort     = $input->{'sort'};
    my $sDirection = $input->{'sDirection'};
    if ( !$sort){
      $sDirection='asc';
      $sort     = 'u.lastname , u.firstname ';
    }
    if ($sort =~  m/(lastname|firstname)/g){
        $sort     = 'u.lastname ' . $sDirection .', u.firstname ';
    }
    else{
        $sort = 'u.' . $sort;
    }


if($prtType eq 'csv'){
    my $fineList = getFineList2Export($dbh,$uidList, $sort,$sDirection, $pNum, $pagesize);
    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=fineNoticeList.csv\n\n"; 
    print "\"",  join("\",\"",@csvHeader), "\"\n";
    foreach my $row(@$fineList){
        print "\"",  join("\",\"",@$row), "\"\n";
    }

}
else{
    my $recordList = getFineList2Print($dbh,$uidList, $sort,$sDirection, $pNum, $pagesize);
    if($prtType eq 'list'){
        $template->param(prtList    => 1);
    }
    else{    
        $template->param(prtNotice  => 1);
    }

    $template->param(
        recordList => $recordList,
        sort       => $sort,
        sDirection => $sDirection, 
        selCount   => $selCount,
        dateToday  => date_text($dateToday),
        pNum       => $pNum,
    );
     my $noticeMsgMap =loc_getMsgFile('report/reports.msg');
        loc_write($template,$noticeMsgMap);

    tmpl_write($dbh, $cgi, $cookie, $template);
}
#$dbh->disconnect();
################################################################################
sub getFineList2Export {
    my ($dbh,$uidList , $sort,$sDirection, $pNum, $pagesize) = @_;
    my $fineList = [];

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

    my $sql = <<_STH_;
select *   from opl_transactions t1 
   inner join (
               select   max(tid) as tid   
               from     opl_transactions  
               group by uid 
               )as r  
   ON t1.tid=r.tid 
   inner join opl_user u ON u.uid=t1.uid
 where t1.balance <> 0  
_STH_
    
    
    $uidList =~ s/,+/,/g;
    $uidList =~ s/(^,|,$)//g;
    $uidList =~ s/,$//;
    if ($uidList){
        $sql .= " && u.uid in ($uidList) ";
    }
    $sql .= " order by $sort  $sDirection  ";


    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $i=-1;
    while ( my $rec = $sth->fetchrow_hashref ){
        $i++;
        $fineList->[$i]=[];
        $rec->{'notes'}   =~ s/\s+//g;
        $rec->{'notes'}   =~ s/-+$//g;
        foreach my $f (@userFields){
            if($f eq 'gender'){
                $rec->{$f} =($rec->{$f}==0)?"F":"M";
            }

            push @{$fineList->[$i]},$rec->{$f};
        }
   }
    $sth->finish;
    return $fineList;

}
################################################################################
sub getFineList2Print {
    my ($dbh,$uidList , $sort,$sDirection, $pNum, $pagesize) = @_;

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

    my $sql = <<_STH_;
select *   from opl_transactions t1 
   inner join (
               select   max(tid) as tid   
               from     opl_transactions  
               group by uid 
               )as r  
   ON t1.tid=r.tid 
   inner join opl_user u ON u.uid=t1.uid
 where t1.balance <> 0  
_STH_
    
    
    $uidList =~ s/,+/,/g;
    $uidList =~ s/(^,|,$)//g;
    $uidList =~ s/,$//;
    if ($uidList){
        $sql .= " && u.uid in ($uidList) ";
    }
    $sql .= " order by $sort  $sDirection  ";

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    

    my $pNum = -1;
    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->{'balance'}     = $r->{'balance'};
          $rec->{'uid'}         = $r->{'uid'};
          $rec->{'sid'}         = $r->{'sid'};
          $rec->{'userbarcode'} = $r->{'userbarcode'};
          $rec->{'grade'}       = $r->{'grade'};
          $rec->{'buildingcode'}= $r->{'buildingcode'};
          $rec->{'homeroom'}    = $r->{'homeroom'};
          $rec->{'teacher'}     = $r->{'teacher'};
          $rec->{'notes'}       = $r->{'notes'};
          $pNum++;
          $rec->{'odd'}         = $pNum%2;
          $rec->{'order'}       = $pNum;
          $rec->{'orderNo'}     = $pNum+1;
          push @recordList, $rec;    
    }
    $sth->finish;
    return \@recordList;
}



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