<?php
//This script is used to generate payment report print out, the query for each report should correspond with the one in Cpoortal/Pages/Script/Payment/Report/..
ini_set("memory_limit","-1");
$baseurl = "../../../";
$configdir = "../../../../../".urldecode($_REQUEST['SubDir']);
require_once($baseurl."general/config.php");
require($baseurl."general/getinfo.php");
require_once "../../../general/TaquaLB/Elements/Elements.php";
require_once $configdir.$dbo->Config['Require']."PDF/mpdf60/mpdf.php";
require_once "../../../general/TaquaLB/Elements/Script/pdf.php";

function ToMySQLDate($date){
    $montharr = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec'];
    $dtarr = explode("/",$date);
    if(count($dtarr) == 3){ //if valid
      //$dtarr[1] = (int)array_search(strtolower($dtarr[1]),$montharr)+1;
      return $dtarr[2]."-".$dtarr[1]."-".$dtarr[0];
    }
    return "";
}

//get the school structure
$sch = GetSchool('SchStrucContr');
$schStruc = json_decode($sch['SchStrucContr'],true);

function HeaderText($FieldName){
    global $schStruc;
    $bank = ["RegNo"=>'REG ID',"LevelName"=>"Level","ClassName"=>$schStruc['ClassID']['Name'],"DeptName"=>$schStruc['ProgID']['Name'],"FacName"=>$schStruc['FacID']['Name']];
    return (isset($bank[$FieldName]))?$bank[$FieldName]:$FieldName;
}

function Displayable($field){
    global $schStruc;
    $fid = ["_DeptName"=>"ProgID","_FacName"=>"FacID"];
    if(isset($fid[$field]) && isset($schStruc[$fid[$field]])){
      if($schStruc[$fid[$field]]['SilentMode'])return false;
    }
    return true;
}

if(!isset($_REQUEST['data'])){
    $html = "INVALID PARAMETER";
}else{
    $dataobj = json_decode($_REQUEST['data']);
    //exit($dataobj->reportType);
    //get user details
    $UserDet = $dbo->SelectFirstRow("user_tb","","UserID=".$dataobj->UID);
    $PrintDate = date("d/M/Y");
   $PrintTime = date("h:i A");
   $totalrst = 0;
   $html = "NO RECORD FOUND";
   $query = "";
   $rses = $dataobj->ses;
   $dataobj->ses = (int)$dataobj->ses == 0?"":"AND p.Ses=".$dataobj->ses;
   if(isset($dataobj->reportType)){
    $from = isset($dataobj->fr)?ToMySQLDate($dataobj->fr):"";
    $to = isset($dataobj->to)?ToMySQLDate($dataobj->to):"";
    $searchstr = isset($dataobj->str)?$dataobj->str:"";
    $datefilter = ($from != "" && $to != "")?"AND (p.PayDate >= '$from' AND p.PayDate <= '$to')":"";
    //Genearl Condition
     //Online Payments
      if((int)$dataobj->offline == 0){
        $datefilter .= " AND LCASE(p.Bank) != 'manual pay' ";
    } 

    //Offline Payment
    if((int)$dataobj->online == 0){
        $datefilter .= " AND LCASE(p.Bank) = 'manual pay' ";
    }

    $vialwalletTxt = " (Wallet Inclusive)";

    $loadwallet = (int)$dataobj->loadwallet;
      //$cond = "";
      if($loadwallet == 0){//exclude
        $datefilter .= " AND p.FromWallet=0";
        $vialwalletTxt = " (Wallet Exclusive)";
      }else if($loadwallet == 2){ //wallet only
        $datefilter .= " AND p.FromWallet=1";
        $vialwalletTxt = " (Wallet Only)";
      }

    //$datefilter .= " AND p.FromWallet = 0 ";

$chnfcond = ""; 
    //include the channel field
    if((int)$dataobj->offline == 1 && (int)$dataobj->online == 1){
        $chnfcond =", IF(LCASE(p.Bank) = 'manual pay','OFFLINE','ONLINE') as CHANNEL";
    }
    //AKSCOE **************************
   // $datefilter .= " AND CHAR_LENGTH(p.TransID) = 12 AND p.Bank = '' ";
    //AKSCOE *****************************
    //reformat date filter
    $fromarr = explode("-",$from);
     $toarr = explode("-",$to);
    //$from = $from != ""?date("j/M/Y",mktime(0,0,0,$fromarr[2],$fromarr[1],$fromarr[0])):"";
//$to = $to != ""?date("j-M-Y",mktime(0,0,0,$toarr[2],$toarr[1],$toarr[0])):""; 

     $sesName = $rses == 0?"":SessionName($rses);

     if($dataobj->reportType == "paytypesumrpt"){ //Summary Report
       // $searchcond = ($searchstr != "")?" WHERE i.ItemName LIKE '%$searchstr%'":"";
      $query = "SELECT i.ItemName as 'ITEM DESCRIPTION', COUNT(p.PayID) as PAYMENT, SUM(p.Amt) as AMOUNT FROM item_tb i LEFT JOIN payhistory_tb p ON i.ID = p.PayID $dataobj->ses $datefilter GROUP BY i.ID";

      //$querylim = $query."$searchcond GROUP BY i.ID LIMIT ".$lmt;
     }else if($dataobj->reportType == "paytypeitem"){
      
       // $searchcond = ($searchstr != "")?" AND (p.Info LIKE '%".$searchstr."%' OR p.TransID LIKE '%$searchstr%' OR p.Amt LIKE '%$searchstr%' OR p.PayDate = '$searchstr' OR p.RegNo LIKE '%$searchstr%')":"";
        //$itemID = 2;
        /*"*RegID"=>"REG. ID",
           "*PName"=>"PAYEE NAME",
            "*Amt"=>"AMOUNT(N)",
            "*PayDate"=>"DATE",
            "*DeptName"=>"DEPT.",
            "*FacName"=>"SCHOOL",
            "*RBank"=>"RECEIVING BANK"*/
            //if alieas start with _ (Json value Name)
            
      $query = "SELECT p.RegNo, p.Info as '_Name', p.Info as '_LevelName' , p.Info as '_ClassName' , p.PayDate as 'DATE', p.Info as '_DeptName', p.Info as '_FacName', p.Info as '_FacID', p.PayID, p.CollectBank as 'RECIEVING BANK' $chnfcond , p.Amt AS AMOUNT, p.Lvl FROM payhistory_tb p WHERE p.PayID = $dataobj->itemID $dataobj->ses $datefilter ORDER BY p.PayDate DESC,p.ID DESC";
      
      //"SELECT  p.RegNo, p.Info, p.Amt, p.PayDate,p.Bank FROM payhistory_tb p WHERE p.PayID = $itemID  AND p.Ses=$ses $datefilter $searchcond ORDER BY p.PayDate DESC,p.ID DESC"
     }else if($dataobj->reportType == "paytypefac"){ //Report by Faculty
   // $searchcond = ($searchstr != "")?" WHERE f.FacName LIKE '%$searchstr%'":"";
     // $query = "SELECT f.FacName as 'FACULTY/SCHOOL', COUNT(p.PayID) as PAYMENT, SUM(p.Amt) as AMOUNT FROM fac_tb f LEFT JOIN dept_tb d ON f.FacID = d.FacID LEFT JOIN programme_tb pr ON d.DeptID = pr.DeptID LEFT JOIN studentinfo_tb s ON pr.ProgID = s.ProgID LEFT JOIN payhistory_tb p ON (p.RegNo = s.RegNo OR p.RegNo = s.JambNo) AND p.Ses=$dataobj->ses $datefilter "."GROUP BY f.FacID";
    // $query = "SELECT f.FacName as 'FACULTY/SCHOOL', COUNT(p.PayID) as PAYMENT, SUM(p.Amt) as AMOUNT FROM fac_tb f LEFT JOIN payhistory_tb p ON p.Info LIKE CONCAT('%\"FacID\":\"',f.FacID,'\"%') $dataobj->ses $datefilter "."GROUP BY f.FacID";
     $query = "SELECT CONCAT(f.FacName,' (',s.Name,')') as 'FACULTY/SCHOOL', COUNT(p.PayID) as PAYMENT, SUM(p.Amt) as AMOUNT FROM fac_tb f LEFT JOIN study_tb s ON f.StudyID = s.ID LEFT JOIN payhistory_tb p ON p.Info LIKE CONCAT('%\"FacID\":\"',f.FacID,'\"%') $dataobj->ses $datefilter "."GROUP BY f.FacID";
    
    }else if($dataobj->reportType == "paytypeprog"){// if($reportType == "paytypefac"){
       //$facID = 1;
      // $searchcond = ($searchstr != "")?" AND pr.ProgName LIKE '%$searchstr%'":"";
     // $query = "SELECT pr.ProgName as PROGRAMME, COUNT(p.ID) as PAYMENT, SUM(p.Amt) as AMOUNT FROM programme_tb pr LEFT JOIN dept_tb d ON pr.DeptID = d.DeptID LEFT JOIN studentinfo_tb s ON s.ProgID = pr.ProgID LEFT JOIN payhistory_tb p ON (p.RegNo = s.RegNo OR p.RegNo = s.JambNo) AND p.Ses=$dataobj->ses $datefilter WHERE d.FacID = $dataobj->facID GROUP BY pr.ProgID";
     $query = "SELECT pr.ProgName as PROGRAMME, COUNT(p.ID)  as PAYMENT, SUM(p.Amt) as AMOUNT FROM programme_tb pr LEFT JOIN  payhistory_tb p ON p.Info LIKE CONCAT('%\"ProgID\":\"',pr.ProgID,'\"%') $dataobj->ses $datefilter WHERE p.Info LIKE '%\"FacID\":\"$dataobj->facID\"%' GROUP BY pr.ProgID ";
      //$querylim = $query."$searchcond GROUP BY pr.ProgID LIMIT ".$lmt;
    }else if($dataobj->reportType == "paytypestud"){
        //$itemID = 2;
       // $searchcond = ($searchstr != "")?" AND (s.SurName LIKE '%$searchstr%' OR s.FirstName LIKE '%$searchstr%' OR s.OtherNames LIKE '%$searchstr%' OR p.TransID LIKE '%$searchstr%' OR p.Amt LIKE '%$searchstr%' OR p.PayDate = '$searchstr' OR p.RegNo LIKE '%$searchstr%')":"";
      //$query = "SELECT p.PayDate as 'DATE', p.RegNo as 'REG ID', CONCAT(s.SurName,' ',s.FirstName,' ',s.OtherNames) as 'PAYEE NAME', p.TransID as 'PAYMENT REFERENCE', p.Amt as AMOUNT FROM payhistory_tb p LEFT JOIN studentinfo_tb s ON (s.RegNo = p.RegNo || s.JambNo = p.RegNo) WHERE s.ProgID = $dataobj->progID AND p.Ses=$dataobj->ses $datefilter ORDER BY p.PayDate DESC,p.ID DESC";
      $query = "SELECT  p.RegNo, p.Info as '_Name', p.Info as '_LevelName' , p.Info as '_ClassName' , p.PayDate as 'DATE', p.Info as '_DeptName', p.Info as '_FacName', p.Info as '_FacID', p.PayID, p.CollectBank as 'RECIEVING BANK' $chnfcond , p.Amt AS AMOUNT, p.Lvl FROM payhistory_tb p WHERE p.Info LIKE '%\"ProgID\":\"$dataobj->progID\"%' $dataobj->ses $datefilter ORDER BY p.PayDate DESC,p.ID DESC";
    
    }
     $html = $query;
     $totpay = 0;
               $totamt = 0;
               $cnt =1;
               $sumary = true;
               //$query = "";
    if($query != ""){
       $qr =  $dbo->RunQuery($query);
       if(is_array($qr)){
           if($qr[1] > 0){
               if($qr[1] <= 1000){
               $thead = "";
               $tbody = "<tbody>";
               $tfoot = "";
              $totfields = 0;
             // $cntfileds = 0;
               while($rst = $qr[0]->fetch_assoc()){
                $tr = "<tr><td>$cnt</td>";
                //if($totfields == 0){$totfields = count($rst);}
                
                 foreach($rst as $fName=>$val){
                     if($fName == 'Lvl')continue;
                     //check structure field
                     if(!Displayable($fName))continue;
                     $fnameinit = substr($fName,0,1);
                     
                     $fName = $fnameinit == "_"?ltrim($fName,'_'):$fName;
                     if($fnameinit == "_"){ //if from json string (Info)
                        $val = json_decode($val,true);
                        if(isset($val[$fName])){
                           $val = $val[$fName]; 
                        }else{
                            //if LevelName is not set
                            if($fName == "LevelName"){
                                if(isset($val['StudyID'])){
                                    $StudyID = $val['StudyID'];
                                  }else{
                                    //get the student study
                                    $StudyID = GetStudStudyID($rst['RegNo']);
                                  }
                                  //get student payment level
                                   $val = LevelName($rst['Lvl'], $StudyID);
                                 // $val = $StudyID;
                            }else if($fName == "ClassName"){
                                //get student className
                              $classdet = StudClass($rst['RegNo']);
                              $val = $classdet['Name']; 
                             //$val = $rst['RegNo']; 
                            }
                        }
                        
                    }
                    
                     //AKSCOE RECIEVING BANK
                     //***********************************************
                     if($fName == 'PayID' || $fName == 'FacID'){
                        continue;
                    }
                     /* if($fName == 'FacID'){
                         $fName = 'RECEIVING BANK';
                         $val = ((int)$val == 1 || (int)$val == 6) && (int)$rst['PayID'] == 2?'FIRST BANK':'UNION BANK';
                     } */
                     //***********************************************
                    if($cnt == 1){
                        $thead .= "<th>".strtoupper(HeaderText($fName))."</th>"; 
                        $totfields++;
                    }
                    
                    $val = $fName == "AMOUNT"?number_format($val,2,".",","):$val;
                    
                    $tr .=  "<td style=\"text-transform:uppercase\">".$val."</td>"; 
                 }
                 //calculate total payment
                if(isset($rst['PAYMENT'])){
                    $totpay += (int)$rst['PAYMENT'];
                    $sumary = true;
                }else{
                    $totpay = $cnt;
                    $sumary = false;
                }
                //calculate total amount
                $totamt += (float)$rst['AMOUNT'];
                 if($cnt == 0){
                   $thead .= "</tr></thead>";
                 }
                 $tbody .= $tr."</tr>";
                 $cnt++;
               }
               $colspan = $totfields-1;
               //$totalpayshtml = $sumary?"<th style=\"text-align:left\">$totpay</th>":'<th></th>';
               $totalpayshtml = "<th style=\"text-align:left\">$totpay</th>";
               $datefil = $from == ""?"":"($from to $to)";
               $header = isset($dataobj->export)?"<tr><th colspan=\"".($totfields + 1)."\">".$sesName." ".$dataobj->displaytext." $datefil</th></tr>":"";
               $thead = "<thead>$header<tr><th>S/N</th>".$thead;
               $tbody .= "</tbody><tfoot><tr><th colspan=\"".$colspan."\" style=\"text-align:right\">Total</th>".$totalpayshtml."<th style=\"text-align:left\">".number_format($totamt,2,".",",")."</th><tr></tfoot>";
               $html = "<table id=\"allreporttb\"> $thead $tbody </table>";
           }else{ //too many record
            $html = "<div style='text-align:center;margin-top:50px;font-weight:bold;color:red;font-size:1.5em'>Maximum Printable Record Exceeded (1000), use the Date Filter to Filter Report</div>";
           }
        }
       }
    }


   }
   
}

if(isset($dataobj->export)){
    exit($html);
}

$chaneldis = "";
if((int)$dataobj->offline == 1 && (int)$dataobj->online == 1){
    $chaneldis = "ONLINE &amp; OFFLINE";
}else if((int)$dataobj->offline == 1){
    $chaneldis = "OFFLINE";
}else{
    $chaneldis = "ONLINE"; 
}

$pdf = new PDFPrinter();
$pdf->SetBasePath($configdir."Files/");
//$pdf->WaterMarkText("AKSU",0.1);
$pdf->Banner($dataobj->displaytext,array("LogoSize"=>"80*80","WaterMark"=>"Abbr"));
$pdf->FooterNote($dataobj->displaytext,$pdf->Signataries(array("STAFF","BURSAR")));
$pdf->Panel();
$pdf->InfoBox(1.33,"margin-left:1.5%");
    $pdf->InfoTitle("STATICTICS");
        $pdf->Info("PAYMENT:",$totpay.$vialwalletTxt);
        $pdf->Info("AMOUNT:","<strong>".number_format($totamt,2,".",",")."</strong>");
        $pdf->Info("RECORD:",($cnt - 1));
$pdf->_InfoBox();
$pdf->InfoBox(1.33,"margin-left:1.5%");
    $pdf->InfoTitle("FILTER");
    
    // $arrRec = ($from != "" && $to != "")?date("j-M-Y",mktime(0,0,0,$fromarr[1],$fromarr[2],$fromarr[0]))." - ".date("j-M-Y",mktime(0,0,0,$toarr[1],$toarr
   // [2],$toarr[0])):"";

       // $pdf->Info("Query String:",$quryva);
        $pdf->Info("SESSION:",trim($sesName) == ""?"ALL":$sesName);
        $pdf->Info("PERIOD:",$dataobj->fr." to ". $dataobj->to);
        $pdf->Info("CHANNEL:",$chaneldis);
$pdf->_InfoBox();
$pdf->InfoBox(1.33,"margin-left:1.5%");
    $pdf->InfoTitle("DETAILS");
       // $pdf->Info("Query String:",$quryva);
        $pdf->Info("REPORT BY:",$UserDet['UserName']);
        $pdf->Info("DATE:",date("d-M-Y"));
        $pdf->Info("Time:",date("h:i:s A"));
$pdf->_InfoBox();
$pdf->_Panel();
$pdf->Dump($html);
$pdf->HTML();
/*echo '
<div style="width:95%; margin:auto;margin-top:100px;">
<div style="width:200px;float:left;border-top:#000 solid thin;padding-top:5px;text-align:center;font-style:italic;font-size:1.1em">HOD</div>
<div style="width:200px;float:right;border-top:#000 solid thin;padding-top:5px;text-align:center;font-style:italic;font-size:1.1em">Staff/User</div>
</div>';*/

$pdf->_HTML();
$pdf->Finish();

?>