<?php
require_once("../../../../general/TaquaLB/Elements/Elements.php");
$configdir = "../../../../../../".$_POST['SubDir'];
require_once("../../../../general/config.php");
require("../../../../general/getinfo.php");
function json_value($Key,$Value){
  return '"'.$key.'":"'.$Value.'"';
}
//$mysqli = new mysqli()
function MessageBack($str,$exitt = true){
    $str = EscapeString($str);
Box("style=color:red;width:100%;text-align:center; margin-top:20px;");
    Box("style=font-size:2em");Icon("exclamation-triangle");_Box();
    Text("text=$str,style=color:inherit;text-transform:uppercase;font-size:1.0em");
_Box();
if($exitt){ 
    exit();
}
} 
function GetAllProg(){
    global $dbo;
    $all = array();
    $progs = $dbo->Select("programme_tb","ProgID,ProgName");
    while($prog = $progs[0]->fetch_array()){
   $all[$prog[0]] = $prog[1];
    }
    return $all;
}

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 "";
}
$allprog = GetAllProg();
extract($_POST);
//pagenation
//Set data for print operation
Box("id=rptprintdata,style=display:none;visibility:hidden");
  echo json_encode($_POST);
_Box();
if(isset($reportType) && isset($ses)){
    $from = isset($fr)?ToMySQLDate($fr):"";
    $to = isset($to)?ToMySQLDate($to):"";
    $searchstr = isset($str)?$str:"";
    //remove serach string from post
    unset($_POST['str']);
    $DPOST = $_POST;
    /*if(!isset($pg)){
    $LastDis = $_POST['LastDis'];
    unset($_POST['LastDis']);
    $BackPOST = $_POST;
    $BackPOST['displaytext'] = $LastDis;
    }*/

    //pagenation
    $lastpgindex = 0;
  $recpg = 50;
   //set limit
   if(isset($pg)){
      $pg = (int)$pg;
      if($pg < 2){
        $lmt = $recpg;
      }else{
          $lastpgindex = (($pg - 1) * $recpg);
        $lmt = $lastpgindex.",$recpg";
      }
   }else{
     $pg = 1;
     $lmt = $recpg;
   }
    $datefilter = ($from != "" && $to != "")?"AND (p.PayDate >= '$from' AND p.PayDate <= '$to')":"";
    $query = "";
    $dump = array();
    //form query based on the report type
    if($reportType == "paytypesumrpt"){ //Summary Report
    $searchcond = ($searchstr != "")?" WHERE i.ItemName LIKE '%$searchstr%'":"";
      $query = "SELECT SQL_CALC_FOUND_ROWS i.ItemName, COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums,i.ID FROM item_tb i LEFT JOIN payhistory_tb p ON i.ID = p.PayID AND p.Ses=$ses $datefilter AND CHAR_LENGTH (p.TransID) = 12";
      $querylim = $query."$searchcond GROUP BY i.ID LIMIT ".$lmt;
      
    }else if($reportType == "paytypeitem"){
        $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%')":"";
        //$itemID = 2;
      $query = "SELECT SQL_CALC_FOUND_ROWS p.RegNo, CONCAT(s.SurName,' ',s.FirstName,' ',s.OtherNames) as Name, p.TransID, p.Amt, p.PayDate, p.itemNum, COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums, s.ProgID FROM payhistory_tb p LEFT JOIN studentinfo_tb s  ON (s.RegNo = p.RegNo || s.JambNo = p.RegNo) WHERE p.PayID = $itemID  AND p.Ses=$ses $datefilter $searchcond  AND CHAR_LENGTH (p.TransID) = 12 ORDER BY p.PayDate DESC,s.ProgID ASC";
      $querylim = str_replace(", COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums","",$query);
      $query = str_replace($searchcond,"",$query);
      $querylim = $querylim." LIMIT ".$lmt;
    }else if($reportType == "paytypefac"){ //Report by Faculty
    $searchcond = ($searchstr != "")?" WHERE f.FacName LIKE '%$searchstr%'":"";
      $query = "SELECT SQL_CALC_FOUND_ROWS f.FacName, COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums,f.FacID 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=$ses $datefilter  AND CHAR_LENGTH (p.TransID) = 12 ";
    $querylim = $query."$searchcond GROUP BY f.FacID LIMIT ".$lmt;
    }else if($reportType == "paytypeprog"){// if($reportType == "paytypefac"){
       //$facID = 1;
       $searchcond = ($searchstr != "")?" AND pr.ProgName LIKE '%$searchstr%'":"";
      $query = "SELECT SQL_CALC_FOUND_ROWS pr.ProgName, COUNT(p.ID) as Payments, SUM(p.Amt) as Sums,pr.ProgID 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=$ses $datefilter WHERE d.FacID = $facID  AND CHAR_LENGTH (p.TransID) = 12 ";
      $querylim = $query."$searchcond GROUP BY pr.ProgID LIMIT ".$lmt;
    }else if($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 SQL_CALC_FOUND_ROWS p.RegNo, CONCAT(s.SurName,' ',s.FirstName,' ',s.OtherNames) as Name, p.TransID, p.Amt, p.PayDate, p.itemNum, COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums,s.ProgID FROM payhistory_tb p LEFT JOIN studentinfo_tb s ON (s.RegNo = p.RegNo || s.JambNo = p.RegNo) WHERE s.ProgID = $progID AND p.Ses=$ses $datefilter $searchcond  AND CHAR_LENGTH (p.TransID) = 12 ORDER BY p.PayDate DESC,s.ProgID ASC";
      $querylim = str_replace(", COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums","",$query);
      $query = str_replace($searchcond,"",$query);
      $querylim = $querylim." LIMIT ".$lmt;
    }
    
    $curdatastr = $dbo->DataString($_POST);
    $totstat = 0;$totamt=0;$totpays=0;$totAmts=0;
    if($query != ""){
       
       
        $rst = $dbo->RunQuery($query);
        
         $rpt = $dbo->RunQuery($querylim);
        //get the overall number of record and total expected page
        $rsttot = $dbo->RunQuery("SELECT FOUND_ROWS()");
        $rtot = $rsttot[0]->fetch_array();
        $alltot = (int)$rtot[0];
        $totpg = $alltot / $recpg; //get the total expexted page
        $totpg = $totpg < 1?1:ceil($totpg);
       // $BackPOST['displaytext'] = $_POST['displaytext'];
        if(is_array($rpt)){
            if($rpt[1] > 0){
                $rstrec = $rst[0]->fetch_array();
                $totpays = $rstrec['Payments'];
                $totAmts = $rstrec['Sums'];
                while($rec = $rpt[0]->fetch_array()){
                    if($reportType == "paytypesumrpt"){ //item group
                    $itemName = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($rec[0]));
                    $totstat += (int)$rec[1];
                    $totamt += (float)$rec[2];
                  $recarr = array($itemName,$rec[1],number_format($rec[2], 2, '.', ','));
                  $_POST['itemID'] = $rec[3];
                  $_POST['reportType'] = "paytypeitem";
                  //$BackPOST['displaytext'] = $_POST['displaytext'];
                  $_POST['displaytext'] = strtoupper($rec[0])." PAYMENT REPORT";
                  $datastr = $dbo->DataString($_POST);

                  $recarr["logo"] =  "*chevron-right";
				$recarr["info"] =  $_POST['displaytext'];
				$recarr["Action"] =  "Payment.PaymentReport.PerformLoad('$datastr')";
                $dump[] = $recarr;
                if(isset($BackPOST['back'])){unset($BackPOST['back']);}
                    }else if($reportType == "paytypefac"){  //faculty group
                    $totstat += (int)$rec[1];
                    $totamt += (float)$rec[2];
                     $facName = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($rec[0]));
                  $recarr = array($facName,$rec[1],number_format($rec[2], 2, '.', ','));
                  $_POST['facID'] = $rec[3];
                  $_POST['reportType'] = "paytypeprog";
                  //$BackPOST['CurrentDisplayText'] = $_POST['displaytext'];
                   $_POST['displaytext'] = strtoupper($rec[0])." FACULTY/SCHOOL PAYMENT REPORT";
                  $datastr = $dbo->DataString($_POST);
                  
                  $recarr["logo"] =  "*chevron-right";
				$recarr["info"] =  $_POST['displaytext'];
				$recarr["Action"] =  "Payment.PaymentReport.PerformLoad('$datastr')";
                $dump[] = $recarr;
                if(isset($BackPOST['back'])){unset($BackPOST['back']);}
                    }else if($reportType == "paytypeitem"){ //load by item individual payment
                    //print_r($rec);
                   // exit;
                     $totstat += 1;
                     $totamt += (float)$rec[3];
                     $amtstr = $rec[3]."";
                     $rec[3] = number_format($rec[3], 2, '.', ',');
                     if($searchstr == $amtstr){
                       $searchstr = $rec[3];
                     }
                     
                     foreach($rec as &$erec){
                         $erec = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($erec));
                     }
                     
                     $BackPOST['reportType'] = 'paytypesumrpt';
                     $BackPOST['back'] = true;
                     //$dt = new DateTime($rec[4]);
                     $recarr = array($rec[0],$rec[1],$allprog[$rec[6]],$rec[3],$rec[4]);
                     $recarr["logo"] =  "*print";
				$recarr["info"] =  "Print";
				$recarr["Action"] =  "Payment.PaymentReport.PrintRc('{$rec[5]}')";
                $dump[] = $recarr;
                    }else if($reportType == "paytypeprog"){ //load by programmes
                    $totstat += (int)$rec[1];
                    $totamt += (float)$rec[2];
                     $progName = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($rec[0]));
                  $recarr = array($progName,$rec[1],number_format($rec[2], 2, '.', ','));
                  $_POST['progID'] = $rec[3];
                  $_POST['reportType'] = "paytypestud";
                   //$BackPOST['displaytext'] = $_POST['displaytext'];
                  $_POST['displaytext'] = strtoupper($rec[0])." PROGRAMME PAYMENT REPORT";
                  $datastr = $dbo->DataString($_POST);
                 $BackPOST['reportType'] = 'paytypefac';
                     $BackPOST['back'] = true;
                  $recarr["logo"] =  "*chevron-right";
				$recarr["info"] =  $_POST['displaytext'];
				$recarr["Action"] =  "Payment.PaymentReport.PerformLoad('$datastr')";
                $dump[] = $recarr;
                    }else if($reportType == "paytypestud"){//individual payment for fac-prog report
                     $totstat += 1;
                     $totamt += (float)$rec[3];
                        $amtstr = $rec[3]."";
                     $rec[3] = number_format($rec[3], 2, '.', ',');
                     if($searchstr == $amtstr){
                       $searchstr = $rec[3];
                     }
                     
                     foreach($rec as &$erec){
                         $erec = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($erec));
                     }
                     //$dt = new DateTime($rec[4]);
                      $recarr = array($rec[0],$rec[1],$allprog[$rec[6]],$rec[3],$rec[4]);
                      // $BackPOST['displaytext'] = $_POST['displaytext'];
                      $BackPOST['reportType'] = 'paytypeprog';
                     $BackPOST['back'] = true;
                     $recarr["logo"] =  "*print";
				$recarr["info"] =  "Print";
				$recarr["Action"] =  "Payment.PaymentReport.PrintRc('{$rec[5]}')";
                $dump[] = $recarr;
                    }
                }
            }
        }
    }
    $totamt = number_format($totamt, 2, '.', ',');
    $totAmts = number_format($totAmts, 2, '.', ',');
    

         
       
     Table("style=width:calc(100% - 12px);font-size:0.8em;margin:auto;margin-top:10px;text-align:left,id=totalrpt,multiselect=false,data-type=table");
     $arrhed = ($from != "" && $to != "")?array("SUMMARY:","REPORT SESSION","DATE FILTER","TOTAL PAYMENT","OVERALL AMOUNT"):array("SUMMARY:","REPORT SESSION","TOTAL PAYMENT","OVERALL AMOUNT");
     $fromarr = explode("-",$from);
     $toarr = explode("-",$to);
        THeader($arrhed,"style=text-align:center,rspan=d1:2");
        $arrRec = ($from != "" && $to != "")?array("<strong>".SessionName($ses)."</strong>",date("j-M-Y",mktime(0,0,0,$fromarr[1],$fromarr[2],$fromarr[0]))." "._Icon("arrow-right")." ".date("j-M-Y",mktime(0,0,0,$toarr[1],$toarr[2],$toarr[0])),"<strong>".$totpays."</strong>","<strong>".$totAmts."</strong>"):array("<strong>".SessionName($ses)."</strong>","<strong>".$totpays."</strong>","<strong>N ".$totAmts."</strong>");
        TRecord($arrRec,"data-id=paytypesum,style=font-weight:bolder");

       _Table();
       echo "<div style=\"width:100%; height:1px;margin:10px 0px\" class=\"altBgColor2\" > </div>";
       
       $dif = 80;
       /*if(isset($BackPOST['back'])){
           
           $backdatastr = $dbo->DataString($BackPOST);
        Box("style=width:50px;display:inline-block;margin:0px 10px;vertical-align:top");
       $backdatastr = str_replace("=","\=",$backdatastr);
       SmallButton("id=payrptbkbtn,logo=chevron-left,style=margin:0px,title=Back,onclick=Payment.PaymentReport.Search('$backdatastr')"); _Box();
       $dif = 160;
       }*/
      Box("style=width:calc(100% - {$dif}px);display:inline-block;vertical-align:top;margin-bottom:5px"); TextBox("title=Search Report,style=width:100%,onchange=,id=payrptsearch,logo=search,info=Inteligent Search: Search by any information you have on the Report,onkeypress=Payment.PaymentReport.SearchPress(event),text=".str_replace(array("=",","),array("\=","\,"),$searchstr)); _Box(); 
       Box("style=width:50px;display:inline-block;margin:0px 10px;vertical-align:top");
       $refreshpostdatastr = str_replace("=","\=",$curdatastr);
       SmallButton("id=payrptsearchbtn,logo=sync,style=margin:0px,title=Search,onclick=Payment.PaymentReport.Search('$refreshpostdatastr')"); _Box();
       Box("style=clear:both");_Box();
       
       if($reportType == "paytypesumrpt"){
       $header=array("*ItemDesc"=>"ITEM DESCRIPTION",
           "*Stat"=>"PAYMENT",
            "*Amt"=>"AMOUNT(N)"
           );
       }else if($reportType == "paytypeitem"){
           $header=array("*RegID"=>"REG. ID",
           "*PName"=>"PAYEE NAME",
            "*PayRef"=>"DEPARTMENT",
            "*Amt"=>"AMOUNT(N)",
            "*PayDate"=>"DATE"
           );
       }else if($reportType == "paytypefac"){
       $header=array("*ItemDesc"=>"FACULTY/SCHOOL",
           "*Stat"=>"PAYMENT",
            "*Amt"=>"AMOUNT(N)"
           );
       }else if($reportType == "paytypeprog"){
         $header=array("*prog"=>"PROGRAMME",
           "*Stat"=>"PAYMENT",
            "*Amt"=>"AMOUNT(N)"
           );  
       }else if($reportType == "paytypestud"){
           $header=array("*RegID"=>"REG. ID",
           "*PName"=>"PAYEE NAME",
            "*PayRef"=>"DEPARTMENT",
            "*Amt"=>"AMOUNT(N)",
            "*PayDate"=>"DATE"
           );
       }
           SpreadSheet("rowselect=false,style=width:calc(100% - 12px);margin:auto;margin-top:10px;margin-bottom:6px,id=payrptsumm,multiselect=false,cellfocus=,cellblur=,cellkeypress=,dynamiccolumn=false,dynamicrow=false,minrow=-1,readonly=ItemDesc;Stat;Amt,trimtext=<strong class=\"altColor\">;</strong>;<strong class=\"altcolor\">,rownumstart=".($lastpgindex + 1),$header,$dump);
           Box();
           if($pg > 1){
               $DPOST['pg'] = $pg - 1;
               $datastr = $dbo->DataString($DPOST);
           SmallButton("id=prevBtn,logo=arrow-circle-left,style=margin:10px;margin-top:3px;margin-bottom:3px;float:left,title=Previous,onclick=Payment.PaymentReport.PerformLoad('".str_replace(array('=',','),array('\=','\,'),$datastr)."')");
           }
           if($pg != $totpg){
            echo '<div style="width:calc(100% - 125px);text-align:center;float:left;font-size:1.2em;font-weight:bold;margin-top:10px" class="altColor2">'.$pg."/".$totpg."</div>";
           }
           if($pg < $totpg){
               $DPOST['pg'] = $pg + 1;
               $datastr = $dbo->DataString($DPOST);
           SmallButton("id=nextbtn,logo=arrow-circle-right,style=margin:10px;margin-top:3px;margin-bottom:3px;float:right,title=Next,onclick=Payment.PaymentReport.PerformLoad('".str_replace(array('=',','),array('\=','\,'),$datastr)."')");
           }
           ClearFloat();
           _Box();
           
       
}

?>