<?php
session_start();

if(isset($_POST["paramValues"])){
    $paramValues=json_decode($_POST["paramValues"]);
    $paramValues->loggedInUserID = $_SESSION['userId'];
    $paramValues->loggedInUserName = $_SESSION['userName'];
    $paramValues->loggedInUserDepartment = $_SESSION['teamLeads'];
    
    $classObj=new FetchClaimDetails();
    if($paramValues->loggedInUserDepartment==FINANCE_DEPARTMENT){
        // sleep(10000);
        $classObj->getFinance_SiteVisitClaimsDetails($paramValues);
    }elseif($paramValues->loggedInUserDepartment==TL_DEPARTMENT){
        // sleep(10000);
        $classObj->getTL_SiteVisitClaimsDetails($paramValues);
    }
    
}else {
  header('location: ../../login.php');
}
class FetchClaimDetails{
    
    function __construct(){
        require_once dirname(__DIR__).'../../php/DB_Connect.php';
        $DBclassObject= new  DB_conncect();
        $this->db=$DBclassObject->connectToDB();
        $this->db_Pdo =$DBclassObject->connectToDBVia_PDO();
    }
    // //----------------------------Finance----------------------------
    function getFinance_SiteVisitClaimsDetails($paramValues){
        $tabStatus=$paramValues->getClaimStatus;
        $queryRequestType =$paramValues->queryRequestType;
        $selectQuery =$this->getSelectStatement();
        $this->queryPrepareStmnt; //global var
        switch($tabStatus){
            case TAB_FINANCE_ALL_CLAIMS:
                $this->getEmployeeDetailsOfAllClaimsQuery($selectQuery,$paramValues);
                break;
            case TAB_FINANCE_APPROVED_CLAIMS:
                $this->getEmployeeDetailsOf_Finance_department_Approved_OR_Paid_Tab($selectQuery,$paramValues);
                break;
            case TAB_FINANCE_REJECTED_CLAIMS:
                $this->getEmployeeDetailsOf_Finance_department_RejectTab($selectQuery,$paramValues);
                break;
            case TAB_FINANCE_PAID_CLAIMS:
                $this->getEmployeeDetailsOf_Finance_department_Approved_OR_Paid_Tab($selectQuery,$paramValues);
                break;
            default:
            break;
        }
        $this->queryPrepareStmnt->execute();
        $this->bindResults_fetchEmployeeDetails();
    }
    
    function getEmployeeDetailsOf_Finance_department_Approved_OR_Paid_Tab($selectQuery,$paramValues){
        $empID=$paramValues->searchEmpId;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND `tesf-2`.`CLAIM_STATUS`=? AND `tesf-2`.`FINANCE_EMP_ID`=?");
        $this->queryPrepareStmnt->bind_param("sssis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $paramValues->loggedInUserID);
    }
    function getEmployeeDetailsOf_Finance_department_RejectTab($selectQuery,$paramValues){//tab contains ClaimStatus(5,7)
        $empID=$paramValues->searchEmpId;
        $permanentFinanceRejectedClaim=CLAIM_PERMANENT_FINANCE_REJECT;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf-2`.`CLAIM_STATUS`=? OR `tesf-2`.`CLAIM_STATUS`=?) AND `tesf-2`.`FINANCE_EMP_ID`=?");
        $this->queryPrepareStmnt->bind_param("sssiis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $permanentFinanceRejectedClaim, $paramValues->loggedInUserID);
        
        // $empID=$paramValues->searchEmpId;
        // $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND `tesf-2`.`CLAIM_STATUS`=? AND `tesf-2`.`FINANCE_EMP_ID`=?");
        // $this->queryPrepareStmnt->bind_param("sssis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $paramValues->loggedInUserID);
    }
    
    
    //------------------------TL queries---------------------------------------- 
    function getTL_SiteVisitClaimsDetails($paramValues){
        $tabStatus=$paramValues->getClaimStatus;
        $queryRequestType =$paramValues->queryRequestType;
        $selectQuery =$this->getSelectStatement();
        $this->queryPrepareStmnt; //global var
        switch($tabStatus){
            case TAB_TL_ALL_CLAIMS:
                if($queryRequestType==REQUEST_FROM_FORTNIGHT_CLAIMS){
                    $this->getEmployeeDetailsOfAllClaimsQuery($selectQuery,$paramValues);
                }elseif($queryRequestType==REQUEST_FROM_INDIVIDUAL_SEARCH_BAR){
                    $this->getEmployeeDetailsOfAllClaimsRespectToTLQuery($selectQuery,$paramValues);
                }
                break;
            case TAB_TL_APPROVED_CLAIMS:
                    $this->getEmployeeDetailsOf_TL_department_TLApprovedTab($selectQuery,$paramValues);
                break;
            case TAB_TL_REJECTED_CLAIMS:
                    $this->getEmployeeDetailsOf_TL_department_TLRejectTab($selectQuery,$paramValues);
                break;
            case TAB_TL_FINANCE_APPROVED_CLAIMS:
                    $this->getEmployeeDetailsOf_TL_department_financeApprovedTab($selectQuery,$paramValues);
                break;
            default:
            break;
        }
        $this->queryPrepareStmnt->execute();
        $this->bindResults_fetchEmployeeDetails();
    }
    

    function getEmployeeDetailsOfAllClaimsRespectToTLQuery($selectQuery,$paramValues){
        $empID=$paramValues->searchEmpId;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE tesf.`EMP_ID`=?  AND tesf.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf-2`.`CLAIM_STATUS` IS NULL OR `tesf-2`.`TL_EMP_ID`=?)");
        $this->queryPrepareStmnt->bind_param("ssss",  $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->loggedInUserID);
    }
    function getEmployeeDetailsOf_TL_department_TLApprovedTab($selectQuery,$paramValues){
        $empID=$paramValues->searchEmpId;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND `tesf-2`.`CLAIM_STATUS`=?  AND `tesf-2`.`TL_EMP_ID`=?");
        $this->queryPrepareStmnt->bind_param("sssis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $paramValues->loggedInUserID);
    }
    function getEmployeeDetailsOf_TL_department_TLRejectTab ($selectQuery,$paramValues){//tl->reject tab(3,5,7)
        $empID=$paramValues->searchEmpId;
        $permanentTLRejectedClaim=CLAIM_PERMANENT_TL_REJECT;
        $permanentFinanceRejectedClaim=CLAIM_PERMANENT_FINANCE_REJECT;
        $financeRejectedClaim=CLAIM_FINANCE_REJECTED;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf-2`.`CLAIM_STATUS`=? OR `tesf-2`.`CLAIM_STATUS`=? OR `tesf-2`.`CLAIM_STATUS`=? OR `tesf-2`.`CLAIM_STATUS`=?)  AND `tesf-2`.`TL_EMP_ID`=?");
        $this->queryPrepareStmnt->bind_param("sssiiiis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $financeRejectedClaim, $permanentTLRejectedClaim, $permanentFinanceRejectedClaim, $paramValues->loggedInUserID);
    }
    function getEmployeeDetailsOf_TL_department_financeApprovedTab ($selectQuery,$paramValues){//tl->financeapproved tab (4,6)
        $empID=$paramValues->searchEmpId;
        $paidClaim=CLAIM_PAID;
        $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE `tesf`.`EMP_ID`=? AND `tesf`.`SITE_VISIT_DATE` BETWEEN ? AND ? AND (`tesf-2`.`CLAIM_STATUS`=? OR `tesf-2`.`CLAIM_STATUS`=?)  AND `tesf-2`.`TL_EMP_ID`=?");
        $this->queryPrepareStmnt->bind_param("sssiis", $empID, $paramValues->Fromdate, $paramValues->Todate, $paramValues->getClaimStatus, $paidClaim, $paramValues->loggedInUserID);
    }
    //---------------------------------------------------------------------
    
    //------------------------common queries-------------------------------
    function getEmployeeDetailsOfAllClaimsQuery($selectQuery,$paramValues){
          $empID=$paramValues->searchEmpId;
          $this->queryPrepareStmnt = $this->db->prepare($selectQuery . " WHERE tesf.`EMP_ID`=?  AND tesf.`SITE_VISIT_DATE` BETWEEN ? AND ?");
          $this->queryPrepareStmnt->bind_param("sss",  $empID, $paramValues->Fromdate, $paramValues->Todate);
    }
    function getSelectStatement(){
         $selectQuery = "SELECT tesf.ID,tesf.NAME,tesf.`SITE_VISITED_ORDER`,tesf.`EMP_ID`,tesf.`SITE_VISIT_DATE`,tesf.`SITE_ID`, tesf.`STAGE_OF_VISIT_OR_AUDIT`, tesf.`DISTANCE_(KMS)`,tesf.`USER_LAT`,tesf.`USER_LNG`,tesf.`USER_LOCATION_ADDRESS`,`tesf-2`.`TOTAL_DISTANCE`,tesf.FTA,tesf.`REPORT_SUBMISSION_STATE`,tesf.`SUPPORTING_BILLS_AVAILABLE_(Y/N)`,"
       ."tesf.`MODE_OF_TRANSPORT`,tesf.REMARK,tesf.`EMPLOYEE_IMAGES`,tesf.`SITE_LOG_IN`,tesf.`SITE_LOG_OUT`,tesf.`FAKE_GPS`, sitesinfo.`SITE_NAME`, tesf.`SITE_ID_LATITUDE`, tesf.`SITE_ID_LONGITUDE`, tesf.`SITE_ID_PROJECT`, sitesinfo.`CIRCLE`, sitesinfo.`DISTRICT`, sitesinfo.`CUSTOMER_NAME`,  `tesf-2`.`ENGINEER_OTHER_BILLS_AMOUNT`, `tesf-2`.`TL_APPROVED_OTHER_BILLS`,"
       ."`tesf-2`.`FINANCE_APPROVED_OTHER_BILLS`, `tesf-2`.`ENGINEER_HOTEL_BILLS_AMOUNT`,  `tesf-2`.`TL_APPROVED_HOTEL_BILLS` , `tesf-2`.`FINANCE_APPROVED_HOTEL_BILLS`, COALESCE(`tesf-2`.`CLAIM_STATUS`,0), `tesf-2`.`CLAIM_DATE`, `tesf-2`.`ENGINEER_DEPUTATION_ALLOWANCE`, `tesf-2`.`TL_APPROVED_DEPUTATION_ALLOWANCE`,`tesf-2`.`FINANCE_APPROVED_DEPUTATION_ALLOWANCE`,"
       ."`tesf-2`.`ENGINEER_DA_AMOUNT`, `tesf-2`.`TL_APPROVED_DA_AMOUNT`,  `tesf-2`.`FINANCE_APPROVED_DA_AMOUNT`, `tesf-2`.`ENGINEER_FINAL_AMOUNT`, `tesf-2`.`TL_APPROVED_FINAL_AMOUNT`, `tesf-2`.`FINANCE_APPROVED_FINAL_AMOUNT`, `tesf-2`.`REJECT_REASON`, `tesf-2`.`CLAIM_STATUS_UPDATED_BY`, `tesf-2`.`TL_DEDUCTED_AMOUNT`, `tesf-2`.`FINANCE_DEDUCTED_AMOUNT`,"
       ."`tesf-2`.`TL_APPROVED_DATE`, `tesf-2`.`FINANCE_APPROVED_DATE`, `tesf-2`.`TL_NAME`, `tesf-2`.`TL_EMP_ID`, `tesf-2`.`TL_DEDUCTED_REASON`, `tesf-2`.`FINANCE_DEDUCTED_REASON`, `tesf-2`.`TL_OR_FINANCE_REJECTED_DATE`, `tesf-2`.`TOTAL_DEDUCTED_AMOUNT`, `tesf-2`.`PAID_DATE`, `tesf-2`.`PAID_BY` ,`emp_on_of`.DAY_VISITS_COUNT "
       ."FROM `tesf` LEFT JOIN `tesf-2` ON `tesf-2`.`UNIQUE_ID`=CONCAT(tesf.`EMP_ID`,'_',tesf.`SITE_VISIT_DATE`) LEFT JOIN sitesinfo ON sitesinfo.`SITE_ID`=tesf.`SITE_ID` "
       ." LEFT JOIN (SELECT EMP_ID,SITE_VISIT_DATE,COUNT(*) AS DAY_VISITS_COUNT FROM tesf GROUP BY tesf.EMP_ID,tesf.SITE_VISIT_DATE) AS `emp_on_of` ON tesf.EMP_ID=`emp_on_of`.EMP_ID AND tesf.SITE_VISIT_DATE=`emp_on_of`.SITE_VISIT_DATE";
        
        return $selectQuery;
    }
    function bindResults_fetchEmployeeDetails(){
        $this->queryPrepareStmnt->bind_result($tesf_id,$tesf_name, $tesf_SiteVisitedOrder, $tesf_empid, $tesf_date, $tesf_siteID, $tesf_stageOfvisit, $tesf_distance, $tesf_user_lat, $tesf_user_lng, $tesf_user_location_address, $tesf2_TotalDistance, $tesf_FTA, $tesf_report_submission_state, $tesf_SupportingBillsAvaliable,
                             $tesf_mode_of_transport, $tesf_remark, $tesf_EmployeeImages, $tesf_logIn, $tesf_logOut, $tesf_fakeGps, $sitesinfo_SiteName, $sitesinfo_latitude, $sitesinfo_longitude, $sitesinfo_project, $sitesinfo_Circle, $sitesinfo_District, $sitesinfo_CustomerName, $tesf2_engineerOtherBillsAmount, $tesf2_TLApprovedOtherBillsAmount,
                             $tesf2_FinanceApprovedOtherBillsAmount, $tesf2_engineerHotelBillsAmount, $tesf2_TLApprovedHotelBillsAmount, $tesf2_FinanceApprovedHotelBillsAmount, $tesf2_claimstatus, $tesf2_claimdate, $tesf2_engineerDA, $tesf2_TLApprovedDA, $tesf2_financeApprovedDA, $tesf2_engineerDAAmount, $tesf2_TLApprovedDAAmount, $tesf2_financeApprovedDAAmount,
                             $tesf2_engineerFinalamount, $tesf2_TLApprovedFinalAmount, $tesf2_FinanceApprovedFinalAmount, $tesf2_rejectReason, $tesf2_claimStatusUpdatedBy, $tesf2_TLDeductedAmount, $tesf2_financeDeductedAmount, $tesf2_tlApproved_date, $tesf2_financeApproved_date, $tesf2_TLName, $tesf2_TLEmpId, $tesf2_TLDeductedReason, $tesf2_financeDeductedReason, $tesf2_tlOrFinanceRejectedDate, $tesf2TotalDeductedAmount, $tesf2PaidDate, $tesf2PaidBy, $empOnOfDayVisitCount);
        $this->queryPrepareStmnt->store_result();
        $mainArray = array();
        if ($this->queryPrepareStmnt->num_rows() > 0) {
          while ($this->queryPrepareStmnt->fetch()) {
    
            $return_arr["tesfId"] = $tesf_id;
            $return_arr["date"] = $tesf_date;
            $return_arr["employeeImages"] = $tesf_EmployeeImages;
            $return_arr["claimstatus"] = $tesf2_claimstatus;
            $return_arr["claimdate"] = $tesf2_claimdate;
            $return_arr["userLocationAddress"] = $tesf_user_location_address;
            $return_arr["userLat"] = $tesf_user_lat;
            $return_arr["userLng"] = $tesf_user_lng;
            $return_arr["siteVisitedOrder"] = $tesf_SiteVisitedOrder;
            $return_arr["reportSubmissionState"] = $tesf_report_submission_state;
            $return_arr["modeOfTransport"] = $tesf_mode_of_transport;
            $return_arr["supportingBillsAvailable"] = $tesf_SupportingBillsAvaliable;
            $return_arr["from"] = $tesf_logIn;
            $return_arr["to"] = $tesf_logOut;
            $return_arr["distance"] = $tesf_distance;
            $return_arr["fta"] = $tesf_FTA;
            $return_arr["remark"] = $tesf_remark;
            $return_arr["sitelat"] = $sitesinfo_latitude;
            $return_arr["siteLong"] = $sitesinfo_longitude;
            $return_arr["siteID"] = $tesf_siteID;
            $return_arr["siteCircle"] = $sitesinfo_Circle;
            $return_arr["siteDistrict"] = $sitesinfo_District;
            $return_arr["stageOfvisit"] = $tesf_stageOfvisit;
            $return_arr["siteName"] = $sitesinfo_SiteName;
            $return_arr["project"] = $sitesinfo_project;
            $return_arr["customer"] = $sitesinfo_CustomerName;
            $return_arr["totalDistance"] = $tesf2_TotalDistance;
            $return_arr["fakeGps"] = $tesf_fakeGps;
    
            $return_arr["engDeputation"] = $tesf2_engineerDA;
            $return_arr["TLDeputation"] = $tesf2_TLApprovedDA;
            $return_arr["financeDeputation"] = $tesf2_financeApprovedDA;
    
            $return_arr["engDAAmount"] = $tesf2_engineerDAAmount;
            $return_arr["TLDAAmount"] = $tesf2_TLApprovedDAAmount;
            $return_arr["financeDAAmount"] = $tesf2_financeApprovedDAAmount;
    
            $return_arr["engOtherBill"] = $tesf2_engineerOtherBillsAmount;
            $return_arr["TLOtherBill"] = $tesf2_TLApprovedOtherBillsAmount;
            $return_arr["financeOtherBill"] = $tesf2_FinanceApprovedOtherBillsAmount;
    
            $return_arr["engHotelBill"] = $tesf2_engineerHotelBillsAmount;
            $return_arr["TLHotelBill"] = $tesf2_TLApprovedHotelBillsAmount;
            $return_arr["financeHotelBill"] = $tesf2_FinanceApprovedHotelBillsAmount;
    
            $return_arr["engTotalAmount"] =  $tesf2_engineerFinalamount;
            $return_arr["TLTotalAmount"] =  $tesf2_TLApprovedFinalAmount;
            $return_arr["financeTotalAmount"] =  $tesf2_FinanceApprovedFinalAmount;
    
            $return_arr["empName"] = $tesf_name;
            $return_arr["empId"] = $tesf_empid;
            $return_arr["claimStatusUpdatedBy"] = $tesf2_claimStatusUpdatedBy;
            $return_arr["rejectReason"] = $tesf2_rejectReason;
            $return_arr["tlApprovedDate"] = $tesf2_tlApproved_date;
            $return_arr["financeApprovedDate"] = $tesf2_financeApproved_date;
            $return_arr["tlDeductedAmount"] = $tesf2_TLDeductedAmount;
            $return_arr["financeDeductedAmount"] = $tesf2_financeDeductedAmount;
            $return_arr["totalDeductedAmount"] = $tesf2TotalDeductedAmount;
            $return_arr["tlName"]= $tesf2_TLName;
            $return_arr["tlId"]= $tesf2_TLEmpId;
            $return_arr["approvedTL"]= $tesf2_TLName.",".$tesf2_TLEmpId;
            $return_arr["TLDeductedReason"]= $tesf2_TLDeductedReason;
            $return_arr["financeDeductedReason"]=$tesf2_financeDeductedReason;
            $return_arr["tlOrFinanceRejectedDate"] = $tesf2_tlOrFinanceRejectedDate;
            $return_arr["paidDate"]=$tesf2PaidDate;
            $return_arr["paidBy"]=$tesf2PaidBy;
            $return_arr["siteOnOfTheDayVisitCount"]=$empOnOfDayVisitCount;
    
            array_push($mainArray, $return_arr);
          }
    
    
          echo  json_encode($mainArray);
        } else {
          echo json_encode($mainArray);
        }
    //--------------------------------------------------------------------
    }
    
    
}



?>