<?php
$FieldsArr = [];
$dumptemplate = [];
$selectFieldID = [];
$selectField = [];
$selectFieldBrkdwn = [];
$StudBal = [];
//form the report query
  function ReportQuery(){
      global $searchstr;
      global $ses;
      global $datefilter;
      global $lmt;
      global $Lvl;
      global $Sem;
      global $ProgID;
      global $StartSes;
      global $ClassID;
      global $MOE;
      global $PayID;
      global $dbo;
      global $OrderBy;
      global $Fields;
      global $FieldsArr;
      global $dumptemplate;
      global $selectFieldID;
      global $selectField;
      global $ReportFilter; //all | paid | dept

      $dumptemplate = ["RN","NM","DT","PP"];

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

      if(isset($Fields)){
        $FieldsArr = $dbo->DataArray($Fields);
        //echo $FieldsArr['MaxDataRow'];
        //get all selected fields
        $maxrw = (int)$FieldsArr['MaxDataRow'];
        $selectField = [];
        $selectFieldID = [];
        if($maxrw > 0){
          $cnt = 1;
          for($ind=1;$ind<=$maxrw;$ind++){
            $itemstatus = (int)$FieldsArr[$ind.'_2'];
            if($itemstatus > 0){ //if selected
              $itemid = $FieldsArr[$ind.'_3'];
              $itemname = $FieldsArr[$ind.'_1'];
              $selectField["*F".$itemid] = strtoupper($itemname);
              $selectFieldID[$itemid] = 3+$cnt;
              $dumptemplate[] = "";
              $cnt++;
            } 
          }
          
         if($cnt > 1){
           $dumptemplate[] = "SubTotal";
           $selectFieldID["SubTotal"] = count($dumptemplate) - 1;
         }
          
        }
        /* print_r($selectField);
        print_r($selectFieldID);
        print_r($dumptemplate); */
      }

      $dumptemplate[] = "TotalPaid";
      $selectFieldID["TotalPaid"] = count($dumptemplate) - 1;
      $dumptemplate[] = "WalletBal";
      $selectFieldID["WalletBal"] = count($dumptemplate) - 1;

      


      //
      $order = "s.RegNo, p.Lvl DESC, p.Sem DESC, p.SemPart DESC";
     /*  if(isset($OrderBy) && trim($OrderBy) != ""){
        $orderarr = ["ordregno"=>"s.RegNo, s.JambNo","ordpaid"=>"p.Amt","orddate"=>"p.PayDate"];
        if(isset($orderarr[$OrderBy])){
          $order = $orderarr[$OrderBy];
        }
      } */

      
      $query = "SELECT COUNT(s.id) as Payments, SUM(p.Amt) as Sums FROM studentinfo_tb s LEFT JOIN payhistory_tb p ON (s.RegNo = p.RegNo OR s.JambNo = p.RegNo)  AND p.PayID = $PayID  AND p.Lvl=".$dbo->SqlSafe($Lvl)." AND p.Sem=".$dbo->SqlSafe($Sem)." $datefilter $ses  WHERE s.ProgID = ".$dbo->SqlSafe($ProgID)." AND s.StartSes = ".$dbo->SqlSafe($StartSes)." AND s.ClassID = ".$dbo->SqlSafe($ClassID)." AND s.ModeOfEntry = ".$dbo->SqlSafe($MOE);

            
      $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%')":"";
      /*  $querylim = "SELECT SQL_CALC_FOUND_ROWS p.RegNo, p.Info, p.TransID, p.Amt, p.PayDate, p.itemNum, p.PayID, p.CollectBank, IF(LCASE(p.Bank) = 'manual pay','OFFLINE','ONLINE') as Channel FROM payhistory_tb p WHERE p.Info  LIKE '%\"ProgID\":\"$progID\"%' $ses $datefilter $searchcond ORDER BY p.PayDate DESC,p.ID DESC"." LIMIT ".$lmt; */

      $cond = [
        "all"=>[
        "(s.RegNo = p.RegNo OR s.JambNo = p.RegNo)  AND p.PayID = $PayID  AND p.Lvl=".$dbo->SqlSafe($Lvl)." AND p.Sem=".$dbo->SqlSafe($Sem)." $datefilter $ses",
        "s.ProgID = ".$dbo->SqlSafe($ProgID)." AND s.StartSes = ".$dbo->SqlSafe($StartSes)." AND s.ClassID = ".$dbo->SqlSafe($ClassID)." AND s.ModeOfEntry = ".$dbo->SqlSafe($MOE)." $searchcond"
        ],
        "paid"=>[
          "(s.RegNo = p.RegNo OR s.JambNo = p.RegNo)",
          "s.ProgID = ".$dbo->SqlSafe($ProgID)." AND p.PayID = $PayID  AND p.Lvl=".$dbo->SqlSafe($Lvl)." AND p.Sem=".$dbo->SqlSafe($Sem)." $ses AND s.StartSes = ".$dbo->SqlSafe($StartSes)." AND s.ClassID = ".$dbo->SqlSafe($ClassID)." AND s.ModeOfEntry = ".$dbo->SqlSafe($MOE)." $datefilter $searchcond"
          ]
          ,
        "dept"=>[
          "(s.RegNo = p.RegNo OR s.JambNo = p.RegNo)  AND p.PayID = $PayID  AND p.Lvl=".$dbo->SqlSafe($Lvl)." AND p.Sem=".$dbo->SqlSafe($Sem)." $datefilter $ses",
          "s.ProgID = ".$dbo->SqlSafe($ProgID)." AND s.StartSes = ".$dbo->SqlSafe($StartSes)." AND s.ClassID = ".$dbo->SqlSafe($ClassID)." AND TransID IS NULL AND s.ModeOfEntry = ".$dbo->SqlSafe($MOE)." $searchcond"
          ]
      ];

      $nowcond = $cond[$ReportFilter];
 
       $querylim = "SELECT SQL_CALC_FOUND_ROWS IF(s.RegNo = '', s.JambNo,s.RegNo) as SRegNo, CONCAT(s.SurName,' ', s.FirstName,' ', s.OtherNames) as StudName,p.Info,p.Amt, p.TransID, p.Amt, DATE_FORMAT(p.PayDate,'%d/%m/%Y') as PayDate, p.itemNum, p.PayID, p.CollectBank, IF(LCASE(p.Bank) = 'manual pay','OFFLINE',p.Bank) as Channel, p.PayBrkDn, p.SemPart FROM studentinfo_tb s LEFT JOIN payhistory_tb p ON {$nowcond[0]}  WHERE {$nowcond[1]} ORDER BY $order $lmt" ;

      /*  $querylim = "SELECT SQL_CALC_FOUND_ROWS IF(s.RegNo = '', s.JambNo,s.RegNo) as SRegNo, CONCAT(s.SurName,'', s.FirstName,' ', s.OtherNames) as StudName,p.Info,p.Amt, p.TransID, p.Amt, 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 FROM studentinfo_tb s LEFT JOIN payhistory_tb p ON (s.RegNo = p.RegNo OR s.JambNo = p.RegNo)  WHERE s.ProgID = ".$dbo->SqlSafe($ProgID)." AND p.PayID = $PayID  AND p.Lvl=".$dbo->SqlSafe($Lvl)." AND p.Sem=".$dbo->SqlSafe($Sem)." $ses AND s.StartSes = ".$dbo->SqlSafe($StartSes)." AND s.ClassID = ".$dbo->SqlSafe($ClassID)." AND s.ModeOfEntry = ".$dbo->SqlSafe($MOE)." $searchcond ORDER BY $order LIMIT $lmt" ; */
       //echo $querylim;
       $_POST['TotalPaidStud']=0;
    return [$query,$querylim];
  }

  function FieldsSubs(){
    global $selectFieldBrkdwn;
    return $selectFieldBrkdwn;
  }

  //Form the spreadsheet records
  function FormRecord(){
    global $searchstr;
    global $totstat;
    global $totamt;
    global $rec;
    global $BackPOST;
    global $dbo;
    global $dump;
    global $dumptemplate;
    global $selectFieldID;
    global $ReportFilter; //all | paid | dept

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

    //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'];
    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;
          $optional = (int)$pitem[4];//if not selected
          if($optional > 0)continue;
          $itemID = (int)$pitem[2];
          $itemidinrec = $selectFieldID["".$itemID];
          if(isset($recarr[$itemidinrec])){
             $recarr[$itemidinrec] = number_format((float)$pitem[1],2);

           $subtot += (float)$pitem[1];
          }
         
          //$succ = $sen?"successse":"dangerre";
          //$sen = !$sen;
                       //$RstHtml .= '<strong class="Labels '.$succ.'">'.$pitem[0]." (".$pitem[1].")  </strong>";
        }
      }
    }
if(isset($selectFieldID["SubTotal"])){
  $itemidinrec = $selectFieldID["SubTotal"];
  if($subtot > 0){
    $recarr[$itemidinrec] = number_format($subtot,2);
  }else{
    $recarr[$itemidinrec] = "";
  }
  
}
    

    
    //$totAmts += (float)$rec['Amt'];
    $amtstr = $rec['Amt']."";
    $disable = 'false';
    if(trim($amtstr) != ""){
        $rec['Amt'] = number_format($rec['Amt'], 2, '.', ',');
        if($searchstr == $amtstr){
        $searchstr = $rec['Amt'];
        }
        $_POST['TotalPaidStud'] += 1;
        //$totpays += 1;
 
    }else{
      if($ReportFilter == "dept"){
        $_POST['TotalPaidStud'] += 1;
      }
        $disable = "true";
    }

 
 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['StudName'],$rec['PayDate'],$RstHtml,$rec['Amt']);
  $recarr[0] = $rec[0];$recarr[1] = $rec['StudName'];$recarr[2] = $rec['PayDate'];$recarr[3] = $SemPart;
  //get the student wallet balance
  $studbal = GetWalletBalance($rec[0]);
  //$studbal = "0.00";
/*   if((int)$offline == 1 && (int)$online == 1){
    $recarr[] = $rec['Channel'];
  } */
  $recarr[$selectFieldID["TotalPaid"]] = $rec['Amt'];
  $recarr[$selectFieldID["WalletBal"]] = $studbal;
  // $BackPOST['displaytext'] = $_POST['displaytext'];
 // $BackPOST['reportType'] = '';
 //$BackPOST['back'] = false;
//  if($disable == "false"){
   $recarr["logo"] =  "*ellipsis-h";
$recarr["info"] =  "Print";
$recarr["Action"] =  "Payment.PaymentReport.PrintOption('{$rec['TransID']}','{$rec[0]}')";
// $recarr["Action"] =  "Payment.PaymentReport.PrintRc('{$rec['TransID']}')";
//  }
 
$recarr["disable"] = $disable;
return $recarr;
  }


  //Form the spread sheet fields
  function Fileds(){
   global $selectField; global $selectFieldID;
    $ff = array("*RegID"=>"REG. ID",
    "*PName"=>"NAMES",
     "*PayDate"=>"DATE",
     "*PayPol"=>"INSTALMENT");
     $ff = array_merge($ff,$selectField);

     if(isset($selectFieldID['SubTotal'])){
      $ff['*SubTotal'] = "TOTAL";
     }
     $ff['*Amt'] = "AMOUNT PAID";
     $ff['*WalBal'] = "WALLET";
     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 $ClassID;
      global $dbo;
      global $ProgID;
      global $StartSes;
      global $StudyID;
      global $PayID;
      global $Lvl;
      global $Sem;
      global $ReportFilter; //all | paid | dept

   
       $sch = GetSchool();
     $schStruc = json_decode($sch['SchStrucContr'],true);
        $classname = $lvlname = "";
        $classdis = "LEVEL/CLASS";
        $headteacher = "";
       if($schStruc['ClassID']['SilentMode'] == false){
         $classdis = $schStruc['ClassID']['Name'];
        $getclass = $dbo->SelectFirstRow("studentclass_tb","Name,HeadID","ID=".$dbo->SqlSafe($ClassID));
        if(is_array($getclass)){
          $classname = "(".$getclass['Name'].")";
           //get the head teacher assigned
           $HeadDet = $dbo->SelectFirstRow("user_tb","UserName","UserID=".$dbo->SqlSafe($getclass['HeadID']));
           if(is_array($HeadDet))$headteacher = $HeadDet['UserName'];
        }
       
      }

      

      //get the student curent class level (Calculated)
      $lvldet = GetLevel($StartSes,$ProgID,$StudyID);
      $lvlname = $lvldet['LevelName']; 

    $arrhed = array($classdis);
    $arrRec = array("<strong>".$lvlname." ".$classname."</strong>");

    $studyName = "";
    $studyTitle = "SCHOOL";
    if($schStruc['StudyID']['SilentMode'] == false){
      $arrhed[] = strtoupper($schStruc['StudyID']['Name']);
      //get the studey name
      $Studdet = $dbo->SelectFirstRow("study_tb","Name","ID=".$dbo->SqlSafe($StudyID));
      $arrRec[] = $studyName = is_array($Studdet)?$Studdet['Name']:"";
      // $studyName = is_array($Studdet)?$Studdet['Name']:"";
      $studyTitle = $schStruc['StudyID']['Name'];
    }

    $progName = "";
    $progTitle = "PROGRAMME";
    if($schStruc['ProgID']['SilentMode'] == false){
      $arrhed[] = strtoupper($schStruc['ProgID']['Name']);
      //get the studey name
      $progdet = $dbo->SelectFirstRow("programme_tb","ProgName","ProgID=".$dbo->SqlSafe($ProgID));
      $arrRec[] = $progName =  is_array($progdet)?$progdet['ProgName']:"";
    $progTitle = $schStruc['ProgID']['Name'];
    }
    $sesName = (int)$rses == 0?"ANY":SessionName($rses);

    $fromarr = explode("-",$from);
    $toarr = explode("-",$to);

    //get the payment type
    $PayType = $dbo->SelectFirstRow("item_tb","ItemName","ID=".$dbo->SqlSafe($PayID));
    $payname = is_array($PayType)?$PayType['ItemName']:"";
    //pay level name
    $PayLevel = LevelName($Lvl, $StudyID);
    $PaySem = SemesterDescription($Sem);
    $dspt = $ReportFilter == "dept"?"DEPTORS":"PAYMENT";
    $otrearr = ($from != "" && $to != "")?array("DATE FILTER","CHANNEL","SESSION","PAYMENT","TOTAL $dspt","OVERALL AMOUNT PAID"):array("CHANNEL","SESSION","PAYMENT","TOTAL $dspt","OVERALL AMOUNT PAID");
    $arrhed = array_merge($arrhed,$otrearr);
    $otdatarr = ($from != "" && $to != "")?array(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])),$chaneldis,$sesName,"".$payname."<br/>".$PayLevel . " / ".$PaySem ,"<strong>".$_POST['TotalPaidStud']."/".$totpays."</strong>","<strong>N ".$totAmts."</strong>"):
    array($chaneldis,$sesName,"".$payname."<br/>".$PayLevel . " / ".$PaySem ,"<strong>".$_POST['TotalPaidStud']."/".$totpays."</strong>","<strong>N ".$totAmts."</strong>");
    $reportfileterdis = ["all"=>"PAID & DEBTORS", "paid"=>"PAID ONLY","dept"=>"DEBTORS"];
    $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,
      "PaymentType"=>$payname,
      "Level"=>$PayLevel,
      "Semester"=>$PaySem,
      "TotalPayment"=>$_POST['TotalPaidStud'],
      "ExpectedPayment"=>$totpays,
      "TotalAmount"=>$totAmts,
      "Class"=>$lvlname." ".$classname,
      "ClassTitle"=>$classdis,
      "Study"=>$studyName,
      "StudyTitle"=>$studyTitle,
      "Prog"=>$progName,
      "ProgTitle"=>$progTitle,
      "SemLabel"=>$sch['SemLabel'],
      "ClassHead" => $headteacher,
      "ReportFilter" => $reportfileterdis[$ReportFilter]
    ];
    $arrRec = array_merge($arrRec,$otdatarr);
   
    
      // THeader($arrhed,"style=text-align:center,rspan=d1:2");
      
       return [$arrhed,$arrRec,$usagearr];
  }


  function GetWalletBalance($RegNo=""){
    
    if(!isset($RegNo) || trim($RegNo) == "")return "0.00";
    
    global $StudBal; global $dbo;
    $FRegNo = str_replace(array("/",'\\','-',' '),"_",$RegNo);
    
    if(isset($StudBal[$FRegNo]))return $StudBal[$FRegNo];
    
    $bal = '0.00';
//get the student last balance
$lasttrans = $dbo->SelectFirstRow("wallet_tb","","RegNo='".$dbo->SqlSafe($RegNo)."' ORDER BY ID DESC LIMIT 1");
//return $FRegNo;
if(is_array($lasttrans)){ //if transaction exist
  $bal = number_format($lasttrans['Balance'],2);
}
$StudBal[$FRegNo] = $bal;
return $bal;
  }


?>