<?php
session_start();
if(isset($_POST['paramValues'])){
    $paramObj=json_decode($_POST['paramValues']);
    $paramObj->userId=$_SESSION['userId'];
    $paramObj->department=$_SESSION['teamLeads'];
    
    $clsObj=new PendingReportSubmitions();
    $clsObj->getPendingReportSubmitionDetails($paramObj);
    
}else{
    echo "Result Not Found";
}
class PendingReportSubmitions{
    function __construct(){
        require_once dirname(__DIR__).'../../../../php/DB_Connect.php';
        $clsObj=new DB_conncect();
        $this->db=$clsObj->connectToDB();
        $this->db_Pdo=$clsObj->connectToDBVia_PDO();
        $this->query= $this->selectColumnNamesQuery();
    }
    function getPendingReportSubmitionDetails($paramObj){
        $mainArray=array();
        $tempArray=array();
        switch($paramObj->searchBy){
            case 'date':
                $stmt=$this->getPendingReportSubmitionsBySiteVisitDate($paramObj);
                break;
            case 'siteId':
                $stmt=$this->getPendingReportSubmitionsBySiteID($paramObj);
                break;
            case 'PONo':
                $stmt=$this->getPendingReportSubmitionsByPONo($paramObj);
                break;
        }
        
        try{
            $this->db_Pdo->beginTransaction();
                $executionStatus=$stmt->execute();
            $this->db_Pdo->commit();
            if(!$executionStatus){
                $mainArray["message"]="FAILURE";
            }else{
                $mainArray["message"]="SUCCESS";
                $result=$stmt->get_result();
                while($data=$result->fetch_assoc()){
                    array_push($tempArray, $data);
                }
                
            }
            $mainArray["responseDataArray"]=$tempArray;
        }
        catch(PDOException $e){
            $this->db_Pdo->rollback();
            $mainArray["message"]="FAILURE";
        }
        echo json_encode($mainArray);
    }
    function getPendingReportSubmitionsBySiteVisitDate($paramObj){
        $this->updateParamObj($paramObj);
        if($paramObj->department==FINANCE_DEPARTMENT){
            $stmt=$this->getFinanceDepartment_SiteVisitDateQueryStmt($paramObj);
        }elseif($paramObj->department==TL_DEPARTMENT){
            $stmt=$this->getTLDepertment_SiteVisitDateQueryStmt($paramObj);
        }
        return $stmt;
    }
    function getPendingReportSubmitionsBySiteID($paramObj){
        if($paramObj->department==FINANCE_DEPARTMENT){
            $stmt=$this->getFinanceDepartment_SiteIdQueryStmt($paramObj->siteId);
        }elseif($paramObj->department==TL_DEPARTMENT){
            $stmt=$this->getTLDepartment_SiteIdQueryStmt($paramObj->managerId, $paramObj->siteId);
        }
        return $stmt;
    }
    function getPendingReportSubmitionsByPONo($paramObj){
        if($paramObj->department==FINANCE_DEPARTMENT){
            $stmt=$this->getFinanceDepartment_PONoQueryStmt($paramObj->PONo);
        }elseif($paramObj->department==TL_DEPARTMENT){
            $stmt=$this->getTLDepartment_PONoQueryStmt($paramObj->managerId, $paramObj->PONo);
        }
        return $stmt;
    }
    // -----------------------------------------------------------------------------------------------------------------
    function getFinanceDepartment_SiteVisitDateQueryStmt($paramObj){
        if($paramObj->month !="Financial"){
            $completeQuery=$this->query." WHERE MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND YEAR(`tesf-2`.`SITE_VISIT_DATE`)=? AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss',$paramObj->month, $paramObj->yearValue);
        }else{
            $completeQuery=$this->query." WHERE `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('ss',$paramObj->from_year, $paramObj->to_year);
        }
        return $stmt;
    }
    function getTLDepertment_SiteVisitDateQueryStmt($paramObj){
        if($paramObj->month !="Financial"){ //Financial year
            $completeQuery=$this->query." WHERE `tesf-2`.`TL_EMP_ID`=? AND MONTHNAME(`tesf-2`.`SITE_VISIT_DATE`)=? AND YEAR(`tesf-2`.`SITE_VISIT_DATE`)=? AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$paramObj->userId, $paramObj->month, $paramObj->yearValue);
        }else{
            $completeQuery=$this->query." WHERE `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
            $stmt=$this->db->prepare($completeQuery);
            $stmt->bind_param('sss',$paramObj->userId, $paramObj->from_year, $paramObj->to_year);
        }
        return $stmt;  
    }
    function getFinanceDepartment_SiteIdQueryStmt($siteId){
        $completeQuery=$this->query." WHERE `tesf`.`SITE_ID` LIKE '%".$siteId."%'  AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
        $stmt=$this->db->prepare($completeQuery);
        // $stmt->bind_param('s',$siteId);
        return $stmt;
    }
    function getTLDepartment_SiteIdQueryStmt($managerId, $siteId){
        $completeQuery=$this->query." WHERE `tesf-2`.`TL_EMP_ID`=? AND `tesf`.`SITE_ID`  LIKE '%".$siteId."%'  AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('ss',$managerId, $siteId);
        return $stmt;        
    }
    function getFinanceDepartment_PONoQueryStmt($poNo){
        $completeQuery=$this->query." WHERE `tesf`.`PO_NUMBER` LIKE '%".$poNo."%'  AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
        $stmt=$this->db->prepare($completeQuery);
        // $stmt->bind_param('s',$poNo);
        return $stmt;
    }
    function getTLDepartment_PONoQueryStmt($managerId, $poNo){
        $completeQuery=$this->query." WHERE `tesf-2`.`TL_EMP_ID`=?  AND `tesf`.`PO_NUMBER` LIKE '%".$poNo."%'  AND (`tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON` IS NULL || `tesf`.`REPORT_SUBMITED_TO_CUSTOMER_ON`='0000-00-00')";
        $stmt=$this->db->prepare($completeQuery);
        $stmt->bind_param('s',$managerId);
        return $stmt;        
    }
    function selectColumnNamesQuery(){
        $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-2`.`TL_EMP_ID` AS `MANAGER_ID`, `tesf-2`.`TL_NAME` AS `MANAGER_NAME`
                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` AND `tesf`.`SITE_ID` NOT LIKE '%Return_journey_F%' ";
        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";
    }
}
?>