<?php

$tamt = 0;
$tpay = 0;
$max_grp_concat = 100000000;
$approseen = false;
//form the report query
  function ReportQuery(){
      global $searchstr;
      global $ses;
      global $Sem;
      global $datefilter;
      global $Fields;
      global $lmt;
      global $dbo;
      global $max_grp_concat;
      $itemcond = "";

      $lmt = (trim($lmt) != "")?"LIMIT $lmt":"";
      if(isset($Fields)){

        $FieldsArr = $dbo->DataArray($Fields);
        //echo $FieldsArr['MaxDataRow'];
        //get all selected fields
        $maxrw = (int)$FieldsArr['MaxDataRow'];
        if($maxrw > 0){
          $itemsarr = [];
          $cnt = 1; $seen=false;
          for($ind=1;$ind<=$maxrw;$ind++){
            $itemstatus = (int)$FieldsArr[$ind.'_2'];
            if($itemstatus > 0){
            if(isset($FieldsArr[$ind.'_3'])){
              $itemsarr[] = $FieldsArr[$ind.'_3'];
              $seen = true;
            }
          }
            
          }
          if(!$seen){
            MessageBack("No Payment Item Selected");
              // exit("#No Payment Item Selected");
          }else{
            if(count($itemsarr) != $maxrw){
              $itemcond = "AND i.ID IN (".implode(",",$itemsarr).")";
            }
            
          }

        }
      }

      $semcond = (int)$Sem > 0?"AND p.Sem = ".$dbo->SqlSafe($Sem):"";
      /* Tuition Fee~25000~1~0~0***Library Fee~2500~13~0~0***Laboratory  */
    $searchcond = ($searchstr != "")?" WHERE i.ItemName LIKE '%$searchstr%'":"";
    $searchcond .= ($searchstr != "")?" $itemcond":"WHERE 1=1 $itemcond";
    //not using the query to get total
    $query = "SELECT  COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums FROM  payhistory_tb p WHERE 1=1 $ses $datefilter";

   // $query = "";
    $q=$dbo->RunQuery("SET group_concat_max_len=".$max_grp_concat);
    $querylim = "SELECT SQL_CALC_FOUND_ROWS i.ItemName, COUNT(p.PayID) as Payments, SUM(p.Amt) as Sums,i.ID as ItemID, GROUP_CONCAT(PayBrkDn SEPARATOR '`') as BRKDWN FROM payitem_tb i LEFT JOIN payhistory_tb p ON (p.PayBrkDn LIKE CONCAT('%~%~',i.ID,'~_~0***%')  OR p.PayBrkDn LIKE CONCAT('%~%~',i.ID,'~_~0')) $semcond $ses $datefilter "."$searchcond GROUP BY i.ID"." ".$lmt;
    //echo $querylim;
    return [$query,$querylim];
  }

  //Form the spreadsheet records
  function FormRecord(){
    global $searchstr;
    global $totstat;
    global $totamt;
    global $rec;
    global $BackPOST;
    global $dbo;
    global $dump;
    global $tamt;
    global $tpay;
    global $max_grp_concat;
    global $approseen;
    $itemName = str_replace(strtolower($searchstr),"<strong class=\"altColor\">$searchstr</strong>",strtolower($rec[0]));
    $totstat += (int)$rec[1];

    //get the breakdown
    $allbrkdown = $rec['BRKDWN'];
    $ItemID = (int)$rec['ItemID'];
    $totalamt = 0;
    $aproximate = ""; 
    if(strlen($allbrkdown) >= $max_grp_concat){
      $aproximate = "&gt;";
      $approseen = true;
    }
    
    $indbrkdwn = explode('`',$allbrkdown);
    foreach($indbrkdwn as $bd){
      //break the pay breakdown
      $paybd = explode("***",$bd);
      foreach($paybd as $inpaybd){
        $idpayitem = explode("~",$inpaybd);
        if(!isset($idpayitem[2]))continue;
        if($ItemID == (int)$idpayitem[2]){
          $totalamt += (float)$idpayitem[1];
        break;
        }
      }
    }
    $rec[2] = $totalamt;
    $tamt += $rec[2];
    $tpay += (int)$rec[1];
  $recarr = array($itemName,$rec[1],$aproximate.number_format($rec[2], 2, '.', ','));
  $_POST['itemID'] = $rec[3];
  $_POST['itemName'] = strtoupper($itemName);
  $_POST['reportType'] = "payitemdet";
  $_POST['pg'] = 1;
  //$BackPOST['displaytext'] = $_POST['displaytext'];
  $_POST['displaytext'] = strtoupper($itemName)." REPORT";
  $datastr = $dbo->DataString($_POST);

  $recarr["logo"] =  "*chevron-right";
$recarr["info"] =  $_POST['displaytext'];
$recarr["Action"] =  "Payment.PaymentReport.PerformLoad('$datastr')";

if(isset($BackPOST['back'])){unset($BackPOST['back']);}
return $recarr;
  }


  //Form the spread sheet fields
  function Fileds(){
    return array("*ItemDesc"=>"PAYMENT ITEM",
    "*Stat"=>"PAYMENT",
     "*Amt"=>"AMOUNT(N)"
    );
  }

  //form the sumary fields and data
  function Summary(){
      global $from;
      global $to;
      global $rses;
      global $chaneldis;
      global $totpays;
      global $totAmts;
      global $tamt;
      global $tpay;
      global $approseen;
      global $Sem;
      $sch = GetSchool();
      $approstr = $approseen?"&gt;":"";
    $arrhed = ($from != "" && $to != "")?array("REPORT SESSION",strtoupper($sch["SemLabel"]),"CHANNEL","DATE FILTER","TOTAL PAYMENT","OVERALL AMOUNT"):array("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("<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>".$tpay."</strong>","<strong>$approstr ".number_format($tamt,2)."</strong>"):array("<strong>".$sesName."</strong>",$PaySem,$chaneldis,"<strong>".$tpay."</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"=>$tpay,
        "TotalAmount"=>"$approstr ".number_format($tamt,2),
        "SemLabel"=>$sch['SemLabel']
      ];
       return [$arrhed,$arrRec,$usagearr];
  }


?>