<?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();
    }
    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){
       if($paramObj->userDept==TL_DEPARTMENT){
           $stmt=$this->getTLDepartment_siteVisitDateQueryStmt($paramObj->userId, $paramObj->monthName, $paramObj->year);
       }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
           $stmt=$this->getFinanceDepartment_siteVisitDateQueryStmt($paramObj->monthName, $paramObj->year);
       }
       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($tlId, $monthName, $year){
        if($monthName!="All"){
            $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                        LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                        WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`MANAGER_ID`=? AND MONTHNAME(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND 
                                        YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='')");
            $stmt->bind_param('sss',$tlId,$monthName,$year);// reason behind using left join invoice table for upload excel option  
            return $stmt;
        }else{ //for year
            $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                        LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                        WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`MANAGER_ID`=? AND  
                                        YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='')");
            $stmt->bind_param('ss',$tlId,$year);// reason behind using left join invoice table for upload excel option  
            return $stmt;    
        }
        
    }
    private function getTLDepartment_siteIdQueryStmt($tlId, $siteId){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                    LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`MANAGER_ID`=? AND `site_allocations`.`SITE_ID`=?
                                    AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='')");
         $stmt->bind_param('ss',$tlId, $siteId);
         return $stmt;
    }   
    private function getFinanceDepartment_siteVisitDateQueryStmt($monthName, $year){
        if($monthName!="All"){
            $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                        LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                        WHERE  (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  MONTHNAME(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? 
                                        AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='')");
            $stmt->bind_param('ss', $monthName, $year);
        }else{
            $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                        LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                        WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? 
                                        AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='')");
            $stmt->bind_param('s', $year);
        }
        return $stmt;
    }
    private function getFinanceDepartment_siteIdQueryStmt($siteId){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                    LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`SITE_ID`=? 
                                    AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='') ");
        $stmt->bind_param('s',$siteId);
        return $stmt;
    }
    private function getTLDepartment_PONoQueryStmt($tlId, $PONo){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                  LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`= `site_allocations`.`INVOICE_NUMBER`
                                  WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`MANAGER_ID`=?  AND `site_allocations`.`PO_NUMBER`=?
                                  AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='') ");
        $stmt->bind_param('ss',$tlId, $PONo);
        return $stmt;
    }
    private function getFinanceDepartment_PONoQueryStmt($PONo){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations`
                                  LEFT JOIN `invoices` ON  `invoices`.`INVOICE_NUMBER`= `site_allocations`.`INVOICE_NUMBER`
                                  WHERE (`site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NOT NULL AND `site_allocations`.`REPORT_SUBMITED_TO_CUSTOMER_ON`!='0000-00-00') AND  `site_allocations`.`PO_NUMBER`=?
                                  AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='') ");
        $stmt->bind_param('s',$PONo);
        return $stmt;
    }
}


?>