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

class PendingInvoicesReports{
    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 getPendingInvoiceReportDetails($paramObj){
        $returnResponseArray=array();
        $tempArray=array();
        switch($paramObj->searchBy){
            case 'date':
                $stmt=$this->getPendingInvoiceReportsDetailsBySiteVisitDate($paramObj);
                break;
            case 'siteId':
                $stmt=$this->getPendingInvoiceReportsDetailsBySiteId($paramObj);
                break;
            case 'PONo':
                $stmt=$this->getPendingInvoiceReportsDetailsByPONo($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);
    }
    //----------------------------
    private function getPendingInvoiceReportsDetailsBySiteVisitDate($paramObj){
        $this->updateParamObj($paramObj);
       if($paramObj->userDept==TL_DEPARTMENT){
           $stmt=$this->getTLDepartment_siteVisitDateQueryStmt($paramObj);
       }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
           $stmt=$this->getFinanceDepartment_siteVisitDateQueryStmt($paramObj);
       }
       return $stmt;
    }
    
    private function getPendingInvoiceReportsDetailsBySiteId($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;        
    }
    private function getPendingInvoiceReportsDetailsByPONo($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;
    }
    
    // -----------------------
    private function getTLDepartment_siteVisitDateQueryStmt($paramObj){
         if($paramObj->month!="Financial"){ //Financial Year
            $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `tesf-2`.`TL_EMP_ID`=? AND MONTHNAME(`tesf`.`SITE_VISIT_DATE`)=? AND 
                                        YEAR(`tesf`.`SITE_VISIT_DATE`)=? AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$paramObj->userId,$paramObj->month,$paramObj->yearValue);// reason behind using left join invoice table for upload excel option  
            return $stmt;
        }else{ //for year
            $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `tesf-2`.`TL_EMP_ID`=? AND 
                                        `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ?  AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$paramObj->userId,$paramObj->from_year, $paramObj->to_year);// reason behind using left join invoice table for upload excel option  
            return $stmt;    
        }
    }
    private function getTLDepartment_siteIdQueryStmt($tlId, $siteId){
        $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`SITE_ID` LIKE '%$siteId%' 
                                        AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('s',$tlId);
        return $stmt;
    }   
    private function getFinanceDepartment_siteVisitDateQueryStmt($paramObj){
        if($paramObj->month!="Financial"){ //Financial Year
            $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND MONTHNAME(`tesf`.`SITE_VISIT_DATE`)=? AND 
                                        YEAR(`tesf`.`SITE_VISIT_DATE`)=? AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss', $paramObj->month, $paramObj->yearValue);
        }else{
            $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  
                                        `tesf`.`SITE_VISIT_DATE` BETWEEN ?  AND ? AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss', $paramObj->from_year, $paramObj->to_year);
        }
        return $stmt;
    }
    private function getFinanceDepartment_siteIdQueryStmt($siteId){
        $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND `tesf`.`SITE_ID` LIKE '%$siteId%' 
                                        AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
        $stmt=$this->db->prepare($completeQuery);
        // $stmt->bind_param('s',$siteId);
        return $stmt;
    }
    private function getTLDepartment_PONoQueryStmt($tlId, $PONo){
        $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`PO_NUMBER` LIKE '%$PONo%' 
                                        AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('s',$tlId);
        return $stmt;
    }
    private function getFinanceDepartment_PONoQueryStmt($PONo){
        $completeQuery= $this->query."WHERE (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND `tesf`.`PO_NUMBER`LIKE '%$PONo%' 
                                        AND (`tesf`.`INVOICE_NUMBER` IS NULL || `tesf`.`INVOICE_NUMBER`='')";
        $stmt=$this->db->prepare($completeQuery);
        // $stmt->bind_param('s',$PONo);
        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`
                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";
    }
}


?>