#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;
use Encode;
use PDF::Create;
use POSIX qw(
    floor
);

use Opals::Context;

use Time::localtime;

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

);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);

use Opals::Mail qw(
    mail_send
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $pref = tmpl_preference($dbh);
my $libName = decode('utf8', $pref->{'libname'});
my $dateToday = date_f005();
#----------------------------------------------------------------------------------------------------
my $defLang= Opals::Context->preference('lang');
#my $request=getRquest();
my $request = decode_json($cgi->param('POSTDATA'));
my $defLang= Opals::Context->preference('lang');

my $incPrice = $request->{'price'};
my $incTitle = $request->{'title'};
my $msgTop  = $request->{'msgTop'};
my $msgBot = $request->{'msgBot'};

my $to = qw(khanhleq@bibliofiche.com);
my $subject = "TEST - Overdue Textbook Reminder";
my $ret = {
    selUsers => 0,
    hasEmail    => 0,
    noEmail => 0
};

foreach my $t(@{$request->{'transList'}}){
   my $ret = getNoticeData($dbh,$t->{'uid'},$t->{'loanIdList'});
   my $content = createContentOD($ret);
   my $barcodes = join(',',@{$t->{'loanIdList'}});
   my $eid = enqueueMail($dbh,$t->{'uid'},$ret->{'userInfo'}->{'email'},$subject,$barcodes,$content,"");
   $ret->{'selUsers'}++;
   if (!$ret->{'userInfo'}->{'email'} || $ret->{'userInfo'}->{'email'} eq ""){
       $ret->{'noEmail'}++;
   }
   else{
       $ret->{'hasEmail'}++;
   }
}
    print "Content-type: text/plain\n\n";
    
    
    print "Content-type: text/plain\n\n";
    my $retJSON = to_json($ret,{pretty=>1});
    print  $retJSON; ;


sub enqueueMail {
   my ($dbh,$uid,$email,$subject,$eBarcodes,$cont,$txtCont) = @_; 
   my $sqlRequest = "
insert into tb_emailRequest set 
    email   = ?,
    uid     = ?,
    requestDate = now(); ";
   my $sqlDetail = "
insert into tb_emailDetails set
    eid = ?,
    subject = ?,
    eContent = ?,
    eBarcodes = ?,
    eTxtContent = ? ";

    my $sth = $dbh->prepare($sqlRequest);
    $sth->execute($email,$uid);
    my $eid= $dbh->{'mysql_insertid'};
    $sth = $dbh->prepare($sqlDetail);
    $sth->execute($eid,$subject,$cont,$eBarcodes,$txtCont);
    $sth->finish;
    return $eid;
}

sub getNoticeData {
    my ($dbh,$uid,$loanIdList) = @_;
    my $odList;
    my $lIdList = join(',',@{$loanIdList});
    my $sql = "select i.barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn,
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,u.fullname, u.email,u.yeargraduation,
        l.id as idloan,l.dateDue < now() as overdue,to_days(now()) - to_days(l.dateDue) as od 
    from tb_loan l inner join opl_user u using(uid) 
        inner join tb_items i using(barcode) 
    where  u.uid=$uid && find_in_set(l.id,'$lIdList')";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'}    = $itemInfo->{'title'};
        $rec->{'price'}    = $itemInfo->{'price'};
        $rec->{'barcode'}  = $itemInfo->{'barcode'};
        $rec->{'dateLoan_text'}    = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'}     = date_text ($rec->{'dateDue'},0,$defLang);
        if (!defined $odList->{'userInfo'}){
            $odList->{'userInfo'} = {
                uid         => $rec->{'uid'},
                userbarcode => $rec->{'userbarcode'},
                sid         => $rec->{'sid'},
                lastname    => $rec->{'lastname'}, 
                firstname   => $rec->{'firstname'},
                fullname    => $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'},
                email       => $rec->{'email'},
                homeroom    => $rec->{'homeroom'},
                teacher     => $rec->{'teacher'},
                grade       => $rec->{'grade'},
                buildingcode=> $rec->{'buildingcode'} 
            }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$odList->{'list'}}, $rec ;
    }
    $sth->finish;
    return $odList;
}

sub createContentOD {
    my ($data) = @_;
    my $ret="";
    $ret .= "<p>This is an overdue notice from $libName </p>";
    $ret .= "<p> <b>$data->{'userInfo'}->{'fullname'} </b><br>";
    $ret .= "   <b>Teacher</b>:$data->{'userInfo'}->{'teacher'}<br> ";
    $ret .= "   <b>Grade</b>:$data->{'userInfo'}->{'grade'} </p>";
    $ret .= "<br>$msgTop</br>";
    $ret .= "<h3>OVERDUE ITEM LIST</h3>";
    $ret .= "<table cellpadding='5'><tr>";
    my $header="";
    if ($incTitle == 1){
        $header .= "<th>Title</th>";
    }
    $header .= "<th>Barcode</th>";
    if ($incPrice == 1){
        $header .= "<th>Price</th>";
    }
    $header .= "<th>Loan Date</th>";
    $header .= "<th>Due Date</th>";
    $header .= "<th>Overdue days</th>";
    $header .= "</tr>";
    $ret .= $header;
    foreach my $i(@{$data->{'list'}}){
        $ret .= "<tr>";
        if ($incTitle == 1){
            $ret .= "<td>$i->{'title'}</td>";
        }
            $ret .= "<td>$i->{'barcode'}</td>";
        if ($incPrice == 1){
            $ret .= "<td>$i->{'price'}</td>";
        }
        $ret .= "<td>$i->{'dateLoan'}</td>";
        $ret .= "<td>$i->{'dateDue'}</td>";
        $ret .= "<td>$i->{'od'}</td>";
        $ret .= "</tr>";
    }
    $ret .= "</table>";
    $ret .= "<br>$msgBot</br>";

    return $ret;
}