<?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();
        if($paramObj->searchBy=='date'){
            $stmt=$this->getPendingInvoiceReportsDetailsBySiteVisitDate($paramObj);
        }elseif($paramObj->searchBy=='siteId'){
            $stmt=$this->getPendingInvoiceReportsDetailsBySiteId($paramObj);
        }
        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 getTLDepartment_siteVisitDateQueryStmt($tlId, $monthName, $year){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                    LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE `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);
        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`.`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){
        $stmt=$this->db->prepare("SELECT * FROM `site_allocations` 
                                    LEFT JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE  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);
        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`.`SITE_ID`=? 
                                    AND (`site_allocations`.`INVOICE_NUMBER` IS NULL || `site_allocations`.`INVOICE_NUMBER`='') ");
        $stmt->bind_param('s',$siteId);
        return $stmt;
    }
}


?>