<?php
session_start();
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)
    $paramObj->department=$_SESSION["teamLeads"];
    $paramObj->userId=$_SESSION["userId"];
    $obj= new invoiceAndReportsDashBoard();
    $obj->getDetails($paramObj);
}else{
    echo "No Result found";
}
class invoiceAndReportsDashBoard{
    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();
        $queryTitleArray=array("invoiceReportValues", "invoiceReportCount");
        $query1=$this->getInvoiceReportsValueQuery($paramObj);
        $query2=$this->getInvoiceReportsCountQuery($paramObj);
        $stmt=$this->db_Pdo->query($query1." ".$query2);
        $index=0;
        do{
            $data=$stmt->fetch(PDO::FETCH_ASSOC);
            $subArray[$queryTitleArray[$index]]=$data;
            $index++;
        }while($stmt->nextRowset());
        $mainArray["error"]=false;
        $mainArray["responseData"]=$subArray;
        echo json_encode($mainArray);
    }
    
    private function getInvoiceReportsValueQuery($paramObj){
        if($paramObj->department== FINANCE_DEPARTMENT){
            // $query="SELECT SUM(INVOICE_VALUE)AS OverallInvoicesAmount,
            //             SUM(INVOICE_VALUE-PAYMENT_RECEIVED_VALUE)AS PendingInvoicesAmount,
            //             SUM(PAYMENT_RECEIVED_VALUE)AS PaymentReceivedInvoicesAmount 
            //     FROM `invoices` 
            //     WHERE INVOICE_RISED_ON BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."'; ";
            $query="SELECT SUM(INVOICE_VALUE)AS OverallInvoicesAmount,
                        SUM(INVOICE_VALUE-PAYMENT_RECEIVED_VALUE)AS PendingInvoicesAmount,
                        SUM(PAYMENT_RECEIVED_VALUE)AS PaymentReceivedInvoicesAmount 
                FROM `invoices` 
                INNER JOIN `tesf` ON `tesf`.`INVOICE_NUMBER`=`invoices`.`INVOICE_NUMBER`
                LEFT JOIN `tesf-2` ON `tesf-2`.`ENGINEER_EMP_ID`=`tesf`.`EMP_ID` AND `tesf-2`.`SITE_VISIT_DATE`=`tesf`.`SITE_VISIT_DATE`
                WHERE `tesf-2`.`SITE_VISIT_DATE` BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."';";
        }elseif($paramObj->department== TL_DEPARTMENT){
            $query="SELECT SUM(INVOICE_VALUE)AS OverallInvoicesAmount,
                        SUM(INVOICE_VALUE-PAYMENT_RECEIVED_VALUE)AS PendingInvoicesAmount,
                        SUM(PAYMENT_RECEIVED_VALUE)AS PaymentReceivedInvoicesAmount 
                FROM `invoices` 
                INNER JOIN `tesf` ON `tesf`.`INVOICE_NUMBER`=`invoices`.`INVOICE_NUMBER`
                LEFT JOIN `tesf-2` ON `tesf-2`.`ENGINEER_EMP_ID`=`tesf`.`EMP_ID` AND `tesf-2`.`SITE_VISIT_DATE`=`tesf`.`SITE_VISIT_DATE`
                WHERE `tesf-2`.`SITE_VISIT_DATE` BETWEEN '".$paramObj->fromDate."' AND '".$paramObj->toDate."' AND `tesf-2`.`TL_EMP_ID`='".$paramObj->userId."'; ";
        }
        
        return $query;
    }
    private function getInvoiceReportsCountQuery($paramObj){
        $query="SELECT
                COUNT( CASE WHEN tesf.INVOICE_NUMBER IS NOT NULL AND invoices.PAYMENT_RECEIVED_VALUE IS NULL THEN 'paymentPending' END) AS TotalPendingInvoicesPaymentNeedToReceive,
                COUNT( CASE WHEN tesf.INVOICE_NUMBER IS NOT NULL AND invoices.PAYMENT_RECEIVED_VALUE IS NOT NULL THEN 'paymentReceived' END) AS TotalInvoicesPaymentReceived,
                COUNT(CASE WHEN tesf.REPORT_SUBMITED_TO_CUSTOMER_ON IS NOT NULL AND tesf.INVOICE_NUMBER IS NOT NULL THEN 'invoiceRaised' END) AS TotalInvoicesRaised,
                COUNT(CASE WHEN tesf.REPORT_SUBMITED_TO_CUSTOMER_ON IS NOT NULL AND tesf.INVOICE_NUMBER IS NULL THEN 'invoicePending' END) AS TotalPendingInvoicesNeedToRaise,
                COUNT(CASE WHEN tesf.REPORT_SUBMITED_TO_CUSTOMER_ON IS NOT NULL THEN 'reportSubmitted' END )AS TotalSubmittedReportsFromTLs,
                COUNT(CASE WHEN tesf.REPORT_SUBMITED_TO_CUSTOMER_ON IS NULL THEN 'reportPending' END )AS TotalPendingReportsAtTLs
                FROM `tesf-2` 
                INNER JOIN tesf ON tesf.EMP_ID= `tesf-2`.`ENGINEER_EMP_ID` AND `tesf`.`SITE_VISIT_DATE`=`tesf-2`.`SITE_VISIT_DATE` AND `tesf`.`SITE_ID` NOT LIKE '%Return_Journey_F%'
                LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`tesf`.`INVOICE_NUMBER`
                WHERE `tesf-2`.`SITE_VISIT_DATE` BETWEEN '".$paramObj->fromDate."' And '".$paramObj->toDate."'; ";
        return $query;
    }
}
?>