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

class PendingPaymentInvoiceDetails{
    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 getPendingPaymentInvoiceDetails($paramObj){
        $returnResponseArray=array();
        $tempArray=array();
        switch($paramObj->searchBy){
            case 'date':
                $stmt=$this->getPendingPaymentsInvoiceDetailsBySiteVisitDate($paramObj);
                break;
            case 'siteId':
                $stmt=$this->getPendingPaymentInvoiceDetailsBySiteId($paramObj);
                break;
            case 'PONo':
                $stmt=$this->getPendingPaymentInvoiceDetailsByPONo($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 getPendingPaymentsInvoiceDetailsBySiteVisitDate($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 getPendingPaymentInvoiceDetailsBySiteId($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 getPendingPaymentInvoiceDetailsByPONo($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){
        $query="SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`,
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations`  
                                    INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` ";
        $monthWiseResults=" WHERE `site_allocations`.`MANAGER_ID`=? AND MONTHNAME(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND 
                                    YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
                                    GROUP BY `invoices`.`INVOICE_NUMBER`";
        $AllYearResults=" WHERE `site_allocations`.`MANAGER_ID`=? AND  
                                    YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
                                    GROUP BY `invoices`.`INVOICE_NUMBER`";
                                    
        if($monthName!="All"){
            $stmt=$this->db->prepare($query.$monthWiseResults); //(`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
            $stmt->bind_param('sss',$tlId,$monthName,$year);
        }else{
            $stmt=$this->db->prepare($query.$AllYearResults); //(`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
            $stmt->bind_param('ss',$tlId,$year);  
        }
        return $stmt;
    }
    private function getTLDepartment_siteIdQueryStmt($tlId, $siteId){
        $stmt=$this->db->prepare("SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`,
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations`  
                                    INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE `site_allocations`.`MANAGER_ID`=? AND `site_allocations`.`SITE_ID`=?
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0) 
                                    GROUP BY `invoices`.`INVOICE_NUMBER`");
         $stmt->bind_param('ss',$tlId, $siteId);
         return $stmt;
    }   
    private function getFinanceDepartment_siteVisitDateQueryStmt($monthName, $year){
        $query="SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`, 
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations`  
                                    INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` ";
        $monthWiseResults=" WHERE  MONTHNAME(`site_allocations`.`SITE_ASSIGNED_DATE`)=? AND YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? 
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0) 
                                    GROUP BY `invoices`.`INVOICE_NUMBER`";
        $yearWiseResults=" WHERE  YEAR(`site_allocations`.`SITE_ASSIGNED_DATE`)=? 
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0) 
                                    GROUP BY `invoices`.`INVOICE_NUMBER`";
        if($monthName!="All"){
            $stmt=$this->db->prepare($query.$monthWiseResults);
            $stmt->bind_param('ss', $monthName, $year);    
        }else{
            $stmt=$this->db->prepare($query.$yearWiseResults);
            $stmt->bind_param('s', $year);
        }
        return $stmt;
    }
    private function getFinanceDepartment_siteIdQueryStmt($siteId){
        $stmt=$this->db->prepare("SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`,
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations`  
                                    INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER` 
                                    WHERE `site_allocations`.`SITE_ID`=? 
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL  || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0) 
                                    GROUP BY `invoices`.`INVOICE_NUMBER`");
        $stmt->bind_param('s',$siteId);
        return $stmt;
    }
    private function getTLDepartment_PONoQueryStmt($tlId, $PONo){
        $stmt=$this->db->prepare("SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`,
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations`  
                                    INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER`
                                    WHERE `site_allocations`.`MANAGER_ID`=? AND `site_allocations`.`PO_NUMBER`=?
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
                                    GROUP BY `invoices`.`INVOICE_NUMBER`");
        $stmt->bind_param('ss', $tlId, $PONo);
        return $stmt;
    }
    private function getFinanceDepartment_PONoQueryStmt($PONo){
        $stmt=$this->db->prepare("SELECT `site_allocations`.`PO_NUMBER`, 
                                `invoices`.`INVOICE_RISED_ON`, `invoices`.`INVOICE_NUMBER`, `invoices`.`INVOICE_VALUE`, `invoices`.`PAYMENT_RECEIVED_ON`,
                                `invoices`.`PAYMENT_RECEIVED_VALUE`, `invoices`.`PAYMENT_REMARKS`, `invoices`.`REMAINING_BALANCE_IF_ANY` FROM `site_allocations` 
                                 INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`site_allocations`.`INVOICE_NUMBER`
                                 WHERE  AND `site_allocations`.`PO_NUMBER`=?
                                    AND (`invoices`.`PAYMENT_RECEIVED_ON` IS NULL || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)
                                    GROUP BY `invoices`.`INVOICE_NUMBER`");
        $stmt->bind_param('s',$PONo);
        return $stmt;
    }
    

    
}


?>