<?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){
        $this->updateParamObj($paramObj);
       if($paramObj->userDept==TL_DEPARTMENT){
           $stmt= $this->stmt_siteVisitDate_TL_query($paramObj);
       }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
           $stmt= $this->stmt_siteVisitDate_finance_query($paramObj);
       }
       return $stmt;
    }
    
    private function getPendingPaymentInvoiceDetailsBySiteId($paramObj){
        if($paramObj->userDept==TL_DEPARTMENT){
            $completeQuery=$this->siteId_query($paramObj->siteId).$this->tlCondition().$this->groupBy();
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('s',$paramObj->userId);  
        }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
            $completeQuery=$this->siteId_query($paramObj->siteId).$this->groupBy();
            $stmt=$this->db->prepare($completeQuery);
            // $stmt->bind_param('s',$paramObj->siteId);
        }
        return $stmt;        
    }
    
    private function getPendingPaymentInvoiceDetailsByPONo($paramObj){
        if($paramObj->userDept==TL_DEPARTMENT){
            $completeQuery=$this->po_query($paramObj->PONo).$this->tlCondition().$this->groupBy();
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss', $paramObj->userId);
        }elseif($paramObj->userDept==FINANCE_DEPARTMENT){
                $completeQuery=$this->po_query($paramObj->PONo).$this->groupBy();
                $stmt=$this->db->prepare($completeQuery);
                // $stmt->bind_param('s',$paramObj->PONo);
        }
        return $stmt;
    }
    
    
    
    //-------------------

    private function stmt_siteVisitDate_TL_query($paramObj){
        $query=$this->selectColumnsName()."INNER JOIN `tesf` ON `tesf`.`EMP_ID`= `tesf-2`.`ENGINEER_EMP_ID` AND `tesf`.`SITE_VISIT_DATE` = `tesf-2`.`SITE_VISIT_DATE`
                                                INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`tesf`.`INVOICE_NUMBER` AND (`invoices`.`PAYMENT_RECEIVED_ON`='0000-00-00' || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)";
         $monthWiseResults=" WHERE `tesf-2`.`TL_EMP_ID`=? AND MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND YEAR(`tesf-2`.`SITE_VISIT_DATE`)=?";
         $yearWiseResults =" WHERE `tesf-2`.`TL_EMP_ID`=? AND `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?";
         if($paramObj->month!="Financial"){// finance year
           $completeQuery=$query.$monthWiseResults.$this->groupBy();
           $stmt=$this->db->prepare($completeQuery);
           $stmt->bind_param('sss',$paramObj->userId, $paramObj->month, $paramObj->yearValue);
        }else{
            $completeQuery= $query.$yearWiseResults.$this->groupBy();
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$paramObj->userId, $paramObj->from_year, $paramObj->to_year);
        }
        return $stmt;
    }
    private function stmt_siteVisitDate_finance_query($paramObj){
        $query=$this->selectColumnsName()."INNER JOIN `tesf` ON `tesf`.`EMP_ID`= `tesf-2`.`ENGINEER_EMP_ID` AND `tesf`.`SITE_VISIT_DATE` = `tesf-2`.`SITE_VISIT_DATE`
                                                INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`tesf`.`INVOICE_NUMBER` AND (`invoices`.`PAYMENT_RECEIVED_ON`='0000-00-00' || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)";
         $monthWiseResults=" WHERE MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND YEAR(`tesf-2`.`SITE_VISIT_DATE`)=?";
         $yearWiseResults =" WHERE `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?";
        if($paramObj->month!="Financial"){// finance year
           $completeQuery=$query.$monthWiseResults.$this->groupBy();
           $stmt=$this->db->prepare($completeQuery);
           $stmt->bind_param('ss',$paramObj->month, $paramObj->yearValue);
        }else{
            $completeQuery= $query.$yearWiseResults.$this->groupBy();
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss',$paramObj->from_year, $paramObj->to_year);
        }
        return $stmt;
        
    }
    private function siteId_query($siteId){
        $query=$this->selectColumnsName()."INNER 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` LIKE '%$siteId%' 
                                        INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`tesf`.`INVOICE_NUMBER` AND (`invoices`.`PAYMENT_RECEIVED_ON`='0000-00-00' || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)";
        return $query;
    }
    private function po_query($PONo){
        $query=$this->selectColumnsName()." INNER JOIN `tesf` ON `tesf`.`EMP_ID`=`tesf-2`.`ENGINEER_EMP_ID` AND `tesf`.`SITE_VISIT_DATE`=`tesf-2`.`SITE_VISIT_DATE` and `tesf`.`PO_NUMBER` LIKE '%$PONo%' AND `tesf`.`SITE_ID` NOT LIKE '%Return_Journey_F%'
                INNER JOIN `invoices` ON `invoices`.`INVOICE_NUMBER`=`tesf`.`INVOICE_NUMBER` AND (`invoices`.`PAYMENT_RECEIVED_ON`='0000-00-00' || `invoices`.`REMAINING_BALANCE_IF_ANY`!=0)";
        return $query;
    }
    // -------------------------------------------------
    private function tlCondition(){
        return " WHERE `tesf-2`.`TL_EMP_ID`=?";
    }
    private function groupBy(){
        return " GROUP BY `invoices`.`INVOICE_NUMBER`";
    }
    private function selectColumnsName(){
        return "SELECT `tesf`.`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 `tesf-2` ";
    }
    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";
    }

    
}


?>