<?php
$TAmt = 0;
$TNumPay = 0;
//print_r($_POST);
//form the report query
  function ReportQuery(){
    global $searchstr;
    global $ses;
    global $Sem;
    global $datefilter;
    global $Fields;
    global $lmt;
    global $dbo;
    global $itemID;


    $lmt = (trim($lmt) != "")?"LIMIT $lmt":"";


      $semcond = (int)$Sem > 0?"AND p.Sem = ".$dbo->SqlSafe($Sem):"";

      $query = "SELECT COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums FROM payhistory_tb p WHERE (p.PayBrkDn LIKE '%~%~$itemID~_~0***%'  OR p.PayBrkDn LIKE '%~%~$itemID~_~0') $ses $datefilter $semcond";
   
      $searchcond = ($searchstr != "")?" AND (p.Info LIKE '%".$searchstr."%' OR p.TransID LIKE '%$searchstr%' OR p.Amt LIKE '%$searchstr%' OR DATE_FORMAT(p.PayDate,'%d/%m/%Y') = '$searchstr' OR p.RegNo LIKE '%$searchstr%' OR p.CollectBank LIKE '%$searchstr%' OR IF(LCASE(p.Bank) = 'manual pay','OFFLINE','ONLINE') LIKE '%$searchstr%')":"";
      
      //"select Name, IF(l.Descr='',l.Name,l.Descr) as LevelName from schoollevel_tb where Level = {$lvl} and StudyID = {$StudyID}"

      $querylim = "SELECT SQL_CALC_FOUND_ROWS p.RegNo,CONCAT(s.SurName,' ', s.FirstName,' ', s.OtherNames) as StudName, p.Info, p.TransID, p.Amt, c.Name as ClassName, p.Lvl, DATE_FORMAT(p.PayDate,'%d/%m/%Y') as PayDate, p.itemNum, p.PayID, p.CollectBank, IF(LCASE(p.Bank) = 'manual pay','OFFLINE','ONLINE') as Channel,p.PayBrkDn,p.SemPart, IF(l.Descr='',l.Name,l.Descr) as LevelName FROM payhistory_tb p LEFT JOIN studentinfo_tb s ON (s.RegNo = p.RegNo OR s.JambNo = p.RegNo) LEFT JOIN studentclass_tb c ON s.ClassID = c.ID LEFT JOIN schoollevel_tb l ON p.Lvl=l.Level AND s.StudyID = l.StudyID WHERE  (p.PayBrkDn LIKE '%~%~$itemID~_~0***%'  OR p.PayBrkDn LIKE '%~%~$itemID~_~0') $ses $datefilter $searchcond $semcond ORDER BY p.PayDate DESC,p.ID DESC"." ".$lmt;

   
       $_POST['TotalPaidStud']=0;
    return [$query,$querylim];
  }

  //Form the spreadsheet records
  function FormRecord(){
    global $searchstr;
    global $totstat;
    global $totamt;
    global $rec;
    global $BackPOST;
    global $dbo;
    global $dump;
    global $itemID;
    global $TAmt;
    global $TNumPay;
  

    //$recarr = $dumptemplate;
    $rinfo = json_decode($rec['Info']);
    //$rec['Name'] = $rinfo->Name;
    $rec['DeptName'] = $rinfo->DeptName;
    $rec['FacName'] = $rinfo->FacName;
    if(isset($rinfo->Name))$rec['StudName'] = $rinfo->Name;

    //AKSCOE ***********************
    //$rec['Bank']  =  ($rinfo->FacID == 1 || $rinfo->FacID == 6) && $rec['PayID'] == 2?"FIRST BANK":"UNION BANK";
    //AKSCOE ***********************
    //IBOM ***********************
    $rec['Bank']  =  $rec['CollectBank'];
    //IBOM ***********************

    $SemPart = "";
    //manage part
    if(isset($rec['SemPart']) && !is_null($rec['SemPart']) && (int)$rec['SemPart'] > 0){
      $SemPart = (int)$rec['SemPart'] >= 3?"FULL":((int)$rec['SemPart'] == 2?"COMPLETE":"PART");
    }

    //get the break down
    $RstHtml = "";
    $brkdwn = $rec['PayBrkDn'];
    $PAmt = 0;
    if(!is_null($brkdwn) && trim($brkdwn) != ""){ //if paid
      
      //get individual payment
      $indpaymitem = explode("***",$brkdwn);
      $subtot = 0;
      if(count($indpaymitem) > 0){
        $sen = false;
        foreach($indpaymitem as $initem){
          $pitem = explode("~",$initem);
          if(count($pitem) < 3)continue;
          $iID = (int)$pitem[2];//if not selected
          if($itemID == $iID){
            $PAmt = (float)$pitem[1];
          break;
          }
          
         
          //$succ = $sen?"successse":"dangerre";
          //$sen = !$sen;
                       //$RstHtml .= '<strong class="Labels '.$succ.'">'.$pitem[0]." (".$pitem[1].")  </strong>";
        }
      }
    }

    $TAmt += $PAmt;

    $PAmt = number_format($PAmt,2);

    $TNumPay += 1;
    


 
 foreach($rec as &$erec){
     $erec = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($erec));
 }
 $classd = "";
 if(!empty($rec['LevelName']) && !empty($rec['ClassName'])){
   $classd = $rec['LevelName']."(".$rec['ClassName'].")";
 }else if(empty($rec['LevelName']) && !empty($rec['ClassName'])){
  $classd = $rec['ClassName'];
 }else if(!empty($rec['LevelName']) && empty($rec['ClassName'])){
  $classd = $rec['LevelName'];
 }
 //$dt = new DateTime($rec[4]);
   $recarr = array($rec['RegNo'],$rec['StudName'],$classd,$rec['TransID'],$SemPart,$rec['PayDate'],$PAmt);
  //$recarr[0] = $rec[0];$recarr[1] = $rec['StudName'];$recarr[2] = $rec['PayDate'];$recarr[3] = $SemPart;
/*   if((int)$offline == 1 && (int)$online == 1){
    $recarr[] = $rec['Channel'];
  } */
 // $recarr[$selectFieldID["TotalPaid"]] = $rec['Amt'];
  // $BackPOST['displaytext'] = $_POST['displaytext'];
 // $BackPOST['reportType'] = '';
 //$BackPOST['back'] = false;
 //if($disable == "false"){
   $recarr["logo"] =  "*print";
$recarr["info"] =  "Print";
$recarr["Action"] =  "Payment.PaymentReport.PrintRc('{$rec['TransID']}')";
 //}
 
//$recarr["disable"] = $disable;
return $recarr;
  }


  //Form the spread sheet fields
  function Fileds(){
   //array($rec['RegNo'],$rec['StudName'],$rec['LevelName']."(".$rec['ClassName'].")",$rec['TransID'],$SemPart,$rec['PayDate'],$PAmt);
    $ff = array("*RegID"=>"REG. ID",
    "*PName"=>"NAMES",
    "*PClass"=>"CLASS",
    "*RefNo"=>"REF.",
     "*PayPol"=>"INSTALMENT",
     "*PayDate"=>"DATE",
    "*Amt"=>"AMOUNT");
    
     return $ff;
     /* "*RBank"=>"PAYMENT",
    "*Amt"=>"TOTAL(N)" */
    
  }

  //form the sumary fields and data
  function Summary(){
      global $from;
      global $to;
      global $rses;
      global $chaneldis;
      global $totpays;
      global $totAmts;

      global $dbo;
      global $TAmt;
      global $approseen;
      global $Sem;
      global $itemName;
      global $TNumPay;
      $sch = GetSchool();
      $approstr = $approseen?"&gt;":"";
    $arrhed = ($from != "" && $to != "")?array("PAYMENT ITEM","REPORT SESSION",strtoupper($sch["SemLabel"]),"CHANNEL","DATE FILTER","TOTAL PAYMENT","OVERALL AMOUNT"):array("PAY. ITEM","REPORT SESSION",strtoupper($sch["SemLabel"]),"CHANNEL","TOTAL PAYMENT","OVERALL AMOUNT");
    $fromarr = explode("-",$from);
    $toarr = explode("-",$to);
    $sesName = (int)$rses == 0?"ANY":SessionName($rses);
    if($Sem > 0){
      $PaySem = strtoupper(SemesterDescription($Sem));
    }else{
      $PaySem = "ALL ".strtoupper($sch["SemLabel"]);
    }
    
      // THeader($arrhed,"style=text-align:center,rspan=d1:2");
       $arrRec = ($from != "" && $to != "")?array($itemName,"<strong>".$sesName."</strong>",$PaySem,$chaneldis,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>".$TNumPay."</strong>","<strong>$approstr ".number_format($TAmt,2)."</strong>"):array($itemName,"<strong>".$sesName."</strong>",$PaySem,$chaneldis,"<strong>".$TNumPay."</strong>","<strong>$approstr ".number_format($TAmt,2)."</strong>");
       $usagearr = [
        "DateFilter"=>($from != "" && $to != "")?date("j-M-Y",mktime(0,0,0,$fromarr[1],$fromarr[2],$fromarr[0]))." "._Icon("arrow-right altColor2")." ".date("j-M-Y",mktime(0,0,0,$toarr[1],$toarr[2],$toarr[0])):"",
        "Channel"=>$chaneldis,
        "Session"=>$sesName,
        "Semester"=>$PaySem,
        "TotalPayment"=>$TNumPay,
        "TotalAmount"=>"$approstr ".number_format($TAmt,2),
        "SemLabel"=>$sch['SemLabel'],
        "ItemName"=>$itemName
      ];
       return [$arrhed,$arrRec,$usagearr];
  }


?>