<?php
if(isset($_POST["paramValues"])){
    $paramObj=json_decode($_POST["paramValues"]);
    $paramObj->fromDate=(explode("-",$paramObj->financialYear)[0])."-04-01"; // 2021=>>>         (2021-2022)
    $paramObj->toDate=(explode("-",$paramObj->financialYear)[1])."-03-31"; // 2022=>>>          (2021-2022)
    $clsObj= new getDashBoardDetails();
    $clsObj->getDetails($paramObj);
}else{
    echo "Result not found";
}
class getDashBoardDetails{
    function __construct(){
        require_once dirname(__DIR__).'../../../php/DB_Connect.php';
        $clsObj = new DB_conncect();
        $this->db=$clsObj->connectToDB();
        $this->db_Pdo=$clsObj->connectToDBVia_PDO();
    }
    function getDetails($paramObj){
        $mainArray=array();
        $subArray=array();
        $tempArray=array();
        $queryTitleArray=["advanceAndAdjustmentValues", "totalPendingAdjustments", "totalYearWisePendingAdjustments"];
        $query1="SELECT SUM(`FINANCE_FN_FINAL_AMOUNT`) AS Overall, SUM(`TOTAL_ADVANCE_GIVEN`) AS TotalAdvance, SUM(`FTA_ADDITIONAL_ADJUSTMENT`) AS TotalAdjustments, (SUM(`FINANCE_FN_FINAL_AMOUNT`-(`TOTAL_ADVANCE_GIVEN`+`FTA_ADDITIONAL_ADJUSTMENT`))) AS TotalPendingAdjustment FROM `fort_night_claims` WHERE `FROM_DATE` BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."' ORDER BY `FROM_DATE` DESC; ";
        $query2=" SELECT COUNT(CASE WHEN `FINAL_PAYABLE_VALUE` >0 THEN 'PENDING' END) AS PendingAdjustment, CONCAT_WS('-', YEAR(`FROM_DATE`), MONTHNAME(`FROM_DATE`)) AS MonthName FROM `fort_night_claims` WHERE `FROM_DATE` BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."' GROUP BY MONTH(`FROM_DATE`);";
        $query3=" SELECT customTable.ADJUSTMENT_STATUS, COUNT(*) AS COUNT
                    FROM (SELECT 
                              CASE      	  
                          	  WHEN fn_advances.ADVANCE =0 || fn_advances.ADVANCE IS NULL THEN 'NeedToAdjust'
                              WHEN fn_advances.ADVANCE=-(SUM(`fort_night_claims`.FINANCE_FN_FINAL_AMOUNT)-(SUM(`fort_night_claims`.TOTAL_ADVANCE_GIVEN)+SUM(`fort_night_claims`.FTA_ADDITIONAL_ADJUSTMENT))) THEN 'SuccesfullyAdusted'
                          	  WHEN fn_advances.ADVANCE !=-(SUM(`fort_night_claims`.FINANCE_FN_FINAL_AMOUNT)-(SUM(`fort_night_claims`.TOTAL_ADVANCE_GIVEN)+SUM(`fort_night_claims`.FTA_ADDITIONAL_ADJUSTMENT))) THEN 'MismatchedAdjustment'
                              END AS ADJUSTMENT_STATUS,
                            (SUM(`fort_night_claims`.FINANCE_FN_FINAL_AMOUNT)-(SUM(`fort_night_claims`.TOTAL_ADVANCE_GIVEN)+SUM(`fort_night_claims`.FTA_ADDITIONAL_ADJUSTMENT))) AS REMAINIG_AMOUNT
                          FROM fort_night_claims 
                          LEFT JOIN fn_advances ON fn_advances.TRANSACTION_ID=CONCAT_WS('_', fort_night_claims.EMP_ID, '".$paramObj->fromDate."', '".$paramObj->toDate."', 'yearAdjustment') 
                          WHERE fort_night_claims.FROM_DATE BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."'  GROUP BY fort_night_claims.EMP_ID  HAVING REMAINIG_AMOUNT<0
                         )AS customTable
            GROUP BY customTable.ADJUSTMENT_STATUS ;";
        $stmt=$this->db_Pdo->query($query1.$query2.$query3);
            $index=0;
            do{
                $tempArray=array();
                while($data=$stmt->fetch(PDO::FETCH_ASSOC)){
                        array_push($tempArray, $data);
                }
                $subArray[$queryTitleArray[$index]]=$tempArray;
                $index++;
            }while($stmt->nextRowset());
            $mainArray["error"]=false;
            $mainArray["responseArray"]=$subArray;
        echo json_encode($mainArray);
    }
    
}

?>
