#!/usr/bin/perl

use strict;

use CGI;
use JSON;

use Opals::Context;



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

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

my $trans = decode_json($input->{'transListJson'});
my $dateFrom    = $input->{'dateFrom'} || '2015-06-01 ';
my $dateTo      = $input->{'dateTo'} || '2016-06-01 ';
   $dateFrom .= " 00:00:00";
   $dateTo   .= " 23:59:59";

my @fields=(
     {h=> 'TEACHER'        ,f=>"u.teacher"}, 
     {h=> 'GRADE'          ,f=>"u.grade" }, 
     {h=> 'HOMEROOM'       ,f=>"u.homeroom" }, 
     {h=> 'BUILDINGCODE'   ,f=>"u.buildingcode" }, 
     {h=> 'LASTNAME'       ,f=>"u.lastname" }, 
     {h=> 'FIRSTNAME'      ,f=>"u.firstname" }, 
     {h=> 'USERBARCODE'    ,f=>"u.userbarcode" }, 
     {h=> 'ADDRLINE1'      ,f=>"u.addrLine1" }, 
     {h=> 'ADDRLINE2'      ,f=>"u.addrLine2" }, 
     {h=> 'CITY'           ,f=>"u.city" }, 
     {h=> 'ZIP'            ,f=>"u.zip" }, 
     {h=> 'STATE'          ,f=>"u.state" }, 
     {h=> 'EMAIL'          ,f=>"u.email" }, 
     {h=> 'YEARGRADUATION' ,f=>"u.yeargraduation" }, 
     {h=> 'COURSE'         ,f=>"cl.coursename" }, 
     {h=> 'COURSETEACHER'  ,f=>"concat_ws(',',t.lastname,t.firstname) as c_teachername" }, 
     {h=> 'TITLE'          ,f=>"r.fVal as title" }, 
     {h=> 'BARCODE',       ,f=>"i.barcode" }, 
     {h=> 'PRICE'          ,f=>"i.price" }, 
     {h=> 'DATELOAN'       ,f=>"l.dateLoan" }, 
     {h=> 'DATEDUE'        ,f=>"l.dateDue" }, 
     {h=> 'OD_DAYS'        ,f=>"to_days(now()) - to_days(dateDue) as deltaDueDate"}
); 

my $fArr=[];
    my $hArr=[];
    foreach my $f(@fields){
        push @$hArr,$f->{'h'};
        push @$fArr,$f->{'f'};
    }
my $sql = sprintf "select %s
                  from tb_loan as l 
                  inner join opl_user as u on l.uid = u.uid
                  inner join tb_items as i using(barcode)
                  inner join tb_records r on r.rid=i.rid && r.fId='245_a'
                left join  
                    (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
                inner join tb_courseList as cl using(courseCode))  on ts.id = l.teacherScheduleId
                where dateReturn is null && dateDue <now() && l.uid= ? && to_days(l.dateDue) >= to_days('$dateFrom') && to_days(l.dateDue) <= to_days('$dateTo')
                group by i.barcode order by deltaDueDate  ", join(', ',@$fArr); 

    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=odTextbooks.csv\n\n"; 
    print  join(",",@$hArr) . "\n";
    my $sth = $dbh->prepare($sql);
    foreach my $t (@$trans){
        $sth->execute($t->{'uid'});
        while( my @aa=$sth->fetchrow_array){
            foreach my $c(@aa){
                $c=~ s/"/""/g;
                $c="\"$c\"";
            }
            print  join(",",@aa)  ."\n";

        }
    }