<?php
session_start();
if(isset($_POST['paramValues'])){
    $paramObj=json_decode($_POST['paramValues']);
    $paramObj->userId=$_SESSION['userId'];
    $paramObj->userDept=$_SESSION['teamLeads'];
    $obj=new InvoicesReports();
    $obj->getInvoiceReportDetails($paramObj);
}else{
    echo "No Data Found";
}

class InvoicesReports{
    function __construct(){
        require_once dirname(__DIR__).'../../../../php/DB_Connect.php';
        $obj=new DB_conncect();
        $this->db=$obj->connectToDB();
        $this->db_Pdo=$obj->connectToDBVia_PDO();
        $this->query=$this->selectedColumnNamesInQuery();
    }
    function getInvoiceReportDetails($paramObj){
        $returnResponseArray=array();
        $tempArray=array();
        switch($paramObj->searchBy){
            case 'date':
                $stmt=$this->getInvoiceReportsDetailsBySiteVisitDate($paramObj);
                break;
            case 'siteId':
                $stmt=$this->getInvoiceReportsDetailsBySiteId($paramObj);
                break;
            case 'PONo':
                $stmt=$this->getInvoiceReportDetailsByPONo($paramObj);
                break;
        }

        try{
            $this->db_Pdo->beginTransaction();
                $queryExecutionStatus=$stmt->execute();
            $this->db_Pdo->commit();
            if(!$queryExecutionStatus){
                $returnResponseArray['message']='FAILURE';
            }else{
                $result=$stmt->get_result();
                while($data=$result->fetch_assoc()){
                    array_push($tempArray,$data);
                }
                $returnResponseArray['message']='SUCCESS';
                $returnResponseArray['responseDataArray']=$tempArray;
            }
        }catch(PDOException $e){
            $this->db_Pdo->rollback();
            $returnResponseArray['message']='FAILURE';
            
        }
        echo json_encode($returnResponseArray);
    }
    //---------
    function getInvoiceReportsDetailsBySiteVisitDate($paramObj){
        $this->updateParamObj($paramObj);
       if($paramObj->userDept==TL_DEPARTMENT){
           $stmt=$this->getTLDepartment_siteVisitDateQueryStmt($paramObj->userId, $paramObj->month, $paramObj->yearValue, $paramObj->from_year, $paramObj->to_year);
       }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
           $stmt=$this->getFinanceDepartment_siteVisitDateQueryStmt($paramObj->month, $paramObj->yearValue, $paramObj->from_year, $paramObj->to_year);
       }
       return $stmt;
    }
    function getInvoiceReportsDetailsBySiteId($paramObj){
        if($paramObj->userDept==TL_DEPARTMENT){
            $stmt=$this->getTLDepartment_siteIdQueryStmt($paramObj->userId, $paramObj->siteId);  
        }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
            $stmt=$this->getFinanceDepartment_siteIdQueryStmt($paramObj->siteId);
        }
        return $stmt;
    }
    function getInvoiceReportDetailsByPONo($paramObj){
        if($paramObj->userDept==TL_DEPARTMENT){
            $stmt=$this->getTLDepartment_PONoQueryStmt($paramObj->userId, $paramObj->PONo);
        }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
            $stmt=$this->getFinanceDepartment_PONoQueryStmt($paramObj->PONo);
        }
        return $stmt;
    }
    function getTLDepartment_siteVisitDateQueryStmt($tlId, $monthName, $year, $from_year, $to_year){
        if($monthName!="Financial"){ //Financial Year
            $completeQuery=$this->query."WHERE `tesf-2`.`TL_EMP_ID`=? AND MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND YEAR(`tesf-2`.`SITE_VISIT_DATE`)=?";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$tlId,$monthName,$year);
        }else{
            $completeQuery=$this->query."WHERE `tesf-2`.`TL_EMP_ID`=? AND `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$tlId, $from_year, $to_year);
        }
        return $stmt;
    }
    function getTLDepartment_siteIdQueryStmt($tlId, $siteId){
        $completeQuery=$this->query."WHERE  `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`SITE_ID` LIKE '%".$siteId."%'";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('s',$tlId);
        return $stmt;
    }
     
    function getFinanceDepartment_siteVisitDateQueryStmt($monthName, $year, $from_year, $to_year){
       if($monthName!="Financial"){
            $completeQuery=$this->query."WHERE   MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND  YEAR(`tesf-2`.`SITE_VISIT_DATE`)=?";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss',$monthName, $year);
       }else{
            $completeQuery=$this->query." WHERE  `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss', $from_year, $to_year);
       }
        return $stmt;
    }
    
    function getFinanceDepartment_siteIdQueryStmt($siteId){
        $completeQuery=$this->query."WHERE  `tesf`.`SITE_ID` LIKE '%".$siteId."%'";
        $stmt=$this->db->prepare($completeQuery);
        return $stmt;
    }
    function getTLDepartment_PONoQueryStmt($tlId, $PONo){
        $completeQuery=$this->query."WHERE  `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`PO_NUMBER` LIKE '%".$PONo."%'";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('s',$tlId);
        return $stmt;
    }
    function getFinanceDepartment_PONoQueryStmt($PONo){
        $completeQuery=$this->query."WHERE  `tesf`.`PO_NUMBER` LIKE '%".$PONo."%'";
        $stmt=$this->db->prepare($completeQuery);
        return $stmt;
    }
    function selectedColumnNamesInQuery(){
        $query="SELECT `tesf`.`SITE_ID`, `tesf`.`SITE_ID_PROJECT` AS `PROJECT`, `tesf`.`STAGE_OF_VISIT_OR_AUDIT` AS `STAGE_OF_AUDIT`, `tesf`.`EMP_ID` AS `SITE_ASSIGNED_TO_EMPID`, `tesf`.`NAME` AS `SITE_ASSIGNED_TO_EMP_NAME`, `tesf`.`SITE_VISIT_DATE` AS `SITE_ASSIGNED_DATE`, `tesf`.`PO_NUMBER` , `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`,`tesf`.`REPORT_REMARK`, `tesf`.`INVOICE_NUMBER`, `tesf`.`INVOICE_REMARK`,
                `tesf-2`.`TL_EMP_ID` AS `MANAGER_ID`, `tesf-2`.`TL_NAME` AS `MANAGER_NAME`,`invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`, `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`,`invoices`.`REMAINING_BALANCE_IF_ANY`
                FROM `tesf-2` 
                LEFT 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`";
        return $query;
    }
    function updateParamObj($paramObj){
        $paramObj->month = explode(" ",$paramObj->monthName)[0];         //eg: $monthName = "January 2021"
        $paramObj->yearValue = explode(" ", $paramObj->monthName)[1];
        $paramObj->from_year = explode("-",$paramObj->year)[0]."-04-01"; // eg 2021-2022
        $paramObj->to_year = explode("-", $paramObj->year)[1]."-03-31";
    }
}

?>