<?php
ini_set('memory_limit', '1024M');
session_start();
if (isset($_GET['fromDate'])) {

    $typeOfExcel = $_GET['typeOfExcel'];
    $empId = $_GET['empId'];
    $fromDate = $_GET['fromDate'];
    $toDate = $_GET['toDate'];
    $loggedInUserID = $_SESSION["userId"];
    $loggedInUserDepartment = $_SESSION['teamLeads'];
    // $calimstatus = $_GET['calimstatus'];
    $obj = new Excel();
    $obj->fetchData($fromDate, $toDate, $empId, $typeOfExcel,$loggedInUserID,$loggedInUserDepartment);
} else {
    echo "no reached";
}
class Excel
{

    function __construct()
    {
        require_once '../../Classes/PHPExcel/IOFactory.php';
        require_once dirname(__DIR__).'../../php/DB_Connect.php';
        $this->phpExcel = new PHPExcel();
        $loadExcel = PHPExcel_IOFactory::load("FTA.xlsx");
        $this->activeSheet = $loadExcel;

        $obj=new DB_conncect();
        $this->db=$obj->connectToDB();

    }

    //set individual cell value fucntion
    function setToRelatedSingleCell($rowIndex, $rowValue)
    {
        $this->activeSheet->getActiveSheet()->setCellValue($rowIndex, strval($rowValue));
    }

    //set text-color for claimStatus  fucntion
    function changeTextColor($rowIndex, $color)
    {
        $styleArray = array(
            'font'  => array(
                'color' => array('rgb' => "$color")

            )
        );
        $this->activeSheet->getActiveSheet()->getStyle("$rowIndex")->applyFromArray($styleArray);
    }

    //merging cell (fromcell -> Tocell)
    function mergeCellINExcel($rowIndex, $lastRowIndex, $colIndex)
    {
        $co_ordinate1 = $colIndex . $rowIndex;
        $co_ordinate2 = $colIndex . $lastRowIndex;
         //$co_ordinate2 = $colIndex . $rowIndex;
        $this->activeSheet->getActiveSheet()->mergeCells("$co_ordinate1:$co_ordinate2");
    }

    //main-query function
    function fetchData($fromDate, $toDate, $empId, $typeOfExcel,$loggedInUserID,$loggedInUserDepartment)
    {
        $cellValueArray = array(); //array
        $sitevistOrder = 1;
        $filename = '';
        $selectStatement = "SELECT 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`, sitesinfo.`SITE_NAME`, tesf.`SITE_ID_LATITUDE`,  tesf.`SITE_ID_LONGITUDE`, tesf.`SITE_ID_PROJECT`, tesf.`SITE_TOWN`, sitesinfo.`CIRCLE`, sitesinfo.`DISTRICT`, sitesinfo.`CUSTOMER_NAME`, `tesf-2`.`ENGINEER_HOTEL_BILLS_AMOUNT`,"
                            ."`tesf-2`.`ENGINEER_OTHER_BILLS_AMOUNT`, `tesf-2`.`CLAIM_STATUS`, `tesf-2`.`CLAIM_DATE`, `tesf-2`.`ENGINEER_DEPUTATION_ALLOWANCE`, `tesf-2`.`ENGINEER_DA_AMOUNT`, `tesf-2`.`ENGINEER_FINAL_AMOUNT`, `tesf-2`.`TL_APPROVED_FINAL_AMOUNT`, `tesf-2`.`TL_NAME`, `tesf-2`.`TL_EMP_ID`, `tesf-2`.`TL_APPROVED_DATE`,`tesf-2`.`TL_APPROVED_HOTEL_BILLS`,"
                            ."`tesf-2`.`TL_APPROVED_OTHER_BILLS`,`tesf-2`.`TL_APPROVED_DEPUTATION_ALLOWANCE`, `tesf-2`.`TL_APPROVED_DA_AMOUNT`,`tesf-2`.`TL_DEDUCTED_AMOUNT`,`tesf-2`.`TL_DEDUCTED_REASON`, `tesf-2`.`FINANCE_EMP_NAME`, `tesf-2`.`FINANCE_EMP_ID`, `tesf-2`.`FINANCE_APPROVED_DATE`,`tesf-2`.`FINANCE_APPROVED_HOTEL_BILLS`,`tesf-2`.`FINANCE_APPROVED_OTHER_BILLS`,"
                            ."`tesf-2`.`FINANCE_APPROVED_DEPUTATION_ALLOWANCE`, `tesf-2`.`FINANCE_APPROVED_DA_AMOUNT`, `tesf-2`.`FINANCE_APPROVED_FINAL_AMOUNT`, `tesf-2`.`TOTAL_DEDUCTED_AMOUNT`, `tesf-2`.`FINANCE_DEDUCTED_REASON`,`tesf-2`.`FINANCE_DEDUCTED_AMOUNT`,`tesf-2`.`REJECT_REASON`, `tesf-2`.`TL_OR_FINANCE_REJECTED_DATE` ,`tesf-2`.`PAID_DATE` ,`tesf-2`.`PAID_BY`";

        $fromTesfDBtable="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` WHERE ";
        $fromTesf2DBtable="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` LEFT JOIN sitesinfo ON sitesinfo.`SITE_ID`=tesf.`SITE_ID` WHERE  ";
        if ($typeOfExcel == BULK_EXCEL) {
            if($loggedInUserDepartment == FINANCE_DEPARTMENT){
            $query = $this->db->prepare($selectStatement . $fromTesfDBtable. "tesf.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY tesf.`SITE_VISIT_DATE` ASC,tesf.`EMP_ID` ASC,tesf.SITE_VISITED_ORDER ASC");
            $query->bind_param("ss", $fromDate, $toDate);
            }elseif($loggedInUserDepartment ==TL_DEPARTMENT){
                $query = $this->db->prepare($selectStatement . $fromTesf2DBtable. "`tesf-2`.`TL_EMP_ID`=?  AND  `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY `tesf-2`.`SITE_VISIT_DATE` ASC,`tesf-2`.`ENGINEER_EMP_ID` ASC");
                $query->bind_param("sss", $loggedInUserID, $fromDate, $toDate);
            }

            $filename = $fromDate . 'to' . $toDate;
        } elseif ($typeOfExcel == INDIVIDUAL_EXCEL) {
              if($loggedInUserDepartment == FINANCE_DEPARTMENT){
            $query = $this->db->prepare($selectStatement . $fromTesfDBtable. "tesf.`EMP_ID`=? AND tesf.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY tesf.`SITE_VISIT_DATE`,tesf.`SITE_VISITED_ORDER`ASC");
            $query->bind_param("sss", $empId, $fromDate, $toDate);
              }elseif($loggedInUserDepartment ==TL_DEPARTMENT){
            $query = $this->db->prepare($selectStatement . $fromTesf2DBtable. "`tesf-2`.`ENGINEER_EMP_ID`=? AND `tesf-2`.`TL_EMP_ID`=?  AND  `tesf-2`.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY `tesf-2`.`SITE_VISIT_DATE` ASC,`tesf-2`.`ENGINEER_EMP_ID` ASC");
                $query->bind_param("ssss", $empId, $loggedInUserID, $fromDate, $toDate);
              }
            $filename = $empId . "from" . $fromDate . 'to' . $toDate;
        }
        $query->execute();
        $query->bind_result($tesf_name, $tesf_SiteVisitedOrder, $tesf_empid, $tesf_date, $tesf_siteID,$tesf_stageOfVisitOrAudit, $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, $sitesinfo_SiteName, $tesf_latitude, $tesf_longitude, $tesf_project, $tesf_siteTown, $sitesinfo_Circle, $sitesinfo_District, $sitesinfo_CustomerName, $tesf2_engHotelBillsAmount, $tesf2_engOtherBillsAmount, $tesf2_claimstatus, $tesf2_claimdate, $tesf2_engDA, $tesf2_engDAAmount, $tesf2_engFinalAmount, $tesf2_TLApprovedFinalAmount, $tesf2_TL_Name, $tesf2_TL_ID, $tesf2_TLApproveDate, $tesf2_TLApprovedHotelBill, $tesf2_TLApprovedOtherBill, $tesf2_TLApprovedDA, $tesf2_TLApprovedDAAmount, $tesf2_TLDeductedAmount, $tesf2_TLDeductedReason, $tesf2_finance_Name, $tesf2_finance_ID, $tesf2_financeApprovedDate, $tesf2_financeApprovedHotelBill, $tesf2_financeApprovedOtherBill, $tesf2_financeApprovedDA, $tesf2_financeApprovedDAAmount, $tesf2_financeApprovedFinalAmount, $tesf2_totalDeductAmount, $tesf2_financeDeductReason, $tesf2_financeDeductAmount, $tesf2_rejectReason, $tesf2_tlOrFinanceRejectedDate, $tesf2PaidDate, $tesf2PaidBy);
        $query->store_result();

        if ($query->num_rows()) {
            while ($query->fetch()) {

                $data[0] = $tesf_empid;
                $data[1] = $tesf_name;
                $data[2] = $tesf_EmployeeImages;
                $data[3] = date('d-m-Y', strtotime($tesf_date));
                $data[4] = $tesf_user_location_address;
                $data[5] = $tesf_user_lat;
                $data[6] = $tesf_user_lng;
                $data[7] = $tesf_latitude;
                $data[8] = $tesf_longitude;
                $data[9] = $tesf_SiteVisitedOrder;
                $data[10] = $tesf_siteID;
                $data[11] = $sitesinfo_SiteName;
                $data[12] = $tesf_project;
                $data[13] = $tesf_stageOfVisitOrAudit;
                $data[14] = $sitesinfo_CustomerName;
                $data[15] = $sitesinfo_District;
                $data[16] = $sitesinfo_Circle;
                $data[17] = $tesf_siteTown;
                $data[18] = $tesf_report_submission_state;
                $data[19] = $tesf_mode_of_transport;
                $data[20] = $tesf_SupportingBillsAvaliable;
                $data[21] = $tesf_logIn;
                $data[22] = $tesf_logOut;
                $data[23] = $tesf_distance;
                $data[24] = $tesf2_TotalDistance;
                $data[25] = $tesf_FTA;
                $data[26] = $tesf_remark;//site remark
                $data[27] = $tesf2_claimstatus;
                $data[28] = date('d-m-Y', strtotime($tesf2_claimdate));
                $data[29] = $tesf2_engDA;
                $data[30] = $tesf2_engDAAmount;
                $data[31] = $tesf2_engHotelBillsAmount;
                $data[32] = $tesf2_engOtherBillsAmount;
                $data[33] = $tesf2_engFinalAmount;
                $data[34] = date('d-m-Y', strtotime($tesf2_TLApproveDate));
                $data[35] = $tesf2_TLApprovedDA;
                $data[36] = $tesf2_TLApprovedDAAmount;
                $data[37] = $tesf2_TLApprovedHotelBill;
                $data[38] = $tesf2_TLApprovedOtherBill;
                $data[39] = $tesf2_TLApprovedFinalAmount;
                $data[40] = $tesf2_TL_Name;
                $data[41] = $tesf2_TL_ID;
                $data[42] = $tesf2_TLDeductedReason;
                $data[43] = $tesf2_TLDeductedAmount;
                $data[44] = date('d-m-Y', strtotime($tesf2_financeApprovedDate));
                $data[45] = $tesf2_financeApprovedDA;
                $data[46] = $tesf2_financeApprovedDAAmount;
                $data[47] = $tesf2_financeApprovedHotelBill;
                $data[48] = $tesf2_financeApprovedOtherBill;
                $data[49] = $tesf2_financeApprovedFinalAmount;
                $data[50] = $tesf2_finance_Name;
                $data[51] = $tesf2_finance_ID;
                $data[52] = $tesf2_financeDeductReason;
                $data[53] = $tesf2_financeDeductAmount;
                $data[54] = $tesf2_rejectReason;// tl or finance reject reason
                $data[55] = $tesf2_totalDeductAmount;
                $data[56] = date('d-m-Y', strtotime($tesf2_tlOrFinanceRejectedDate));
                $data[57] = $tesf2PaidDate;
                $data[58] = $tesf2PaidBy;



                array_push($cellValueArray, $data);
            }
            $this->bindValuesToExcel($cellValueArray); //2d-array
            $this->saveExcel($filename);
        } else {
            echo "<script>
             alert('no data found');
             window.history.back();
             </script>";
          }
    }

    function bindValuesToExcel($cellValueArray)
    {

        $allEmpIdAndDateArray = array();
        for ($i = 0; $i < count($cellValueArray); $i++) {
            array_push($allEmpIdAndDateArray, $cellValueArray[$i][0] . "+" . $cellValueArray[$i][3]); //merging-cell-based-on ("$cellValueArray[$i][0]"--> empid) and (" $cellValueArray[$i][3])"-->date)
        }
        $this->noOfDuplicateDateCountArray = array_count_values($allEmpIdAndDateArray); // contains no fo duplicate (emp+date) count eg:{ (180291+2020-11-20)=>2 }    ie: 2 is duplicate count.


        $sno = 0;
        $rowIndex = 2;
        $duplicateDateCount = 0;
        $tempDuplicateDateCount = 0;
        for ($loopCount = 0; $loopCount < count($cellValueArray); $loopCount++) {

            $sno++;
            $rowIndex++;
            if ($duplicateDateCount == 0) {
                $duplicateDateCount = $this->getThisDateDuplicateCount($cellValueArray[$loopCount][0] . "+" . $cellValueArray[$loopCount][3]); //function call to get perticular(emp + date) count
                $tempDuplicateDateCount = $duplicateDateCount; //temp
            }

            if ($tempDuplicateDateCount == $duplicateDateCount) {
                $this->commonRows_actualValues($rowIndex, $cellValueArray, $loopCount);
            } else{                                       //eg: duplicateDateCount=3  then  (duplicateDateCount--) upto duplicateDateCount= tempDuplicateDateCount if(equal){ get the duplicateDateCount}
                $this->commonRows_Duplicate_defaultValues($rowIndex, $cellValueArray, $loopCount);
            }
            $duplicateDateCount--;


            $this->setToRelatedSingleCell("A$rowIndex", $sno); //s.no in execl

            $this->unMergedCells($rowIndex, $cellValueArray, $loopCount); // unmerged cell in excel
        }
    }
    function commonRows_actualValues($rowIndex, $rowValueArray, $loopCount)
    {
         // DIFFERENT FIELDS
         $claimStatus = $this->getClaimStatus($rowValueArray[$loopCount][27], $rowIndex, $rowValueArray, $loopCount);//$rowValueArray[$loopCount][27] Claim status
        $this->setToRelatedSingleCell("AC$rowIndex", $claimStatus);


      //MERGED CELLS COMMON FILEDS
      $this->setToRelatedSingleCell("B$rowIndex", $rowValueArray[$loopCount][0]);//emp-id
      $this->setToRelatedSingleCell("C$rowIndex", $rowValueArray[$loopCount][1]);//emp-name
      $this->setToRelatedSingleCell("E$rowIndex", $rowValueArray[$loopCount][3]);//site visited date
      $this->setToRelatedSingleCell("Z$rowIndex", $rowValueArray[$loopCount][24]);//total distance
      $this->setToRelatedSingleCell("AD$rowIndex", $rowValueArray[$loopCount][28]);//claimdate
      $this->setToRelatedSingleCell("AE$rowIndex", $rowValueArray[$loopCount][29]);//engineer DA
      $this->setToRelatedSingleCell("AF$rowIndex", $rowValueArray[$loopCount][30]);//enginner Da- Amount
      $this->setToRelatedSingleCell("AG$rowIndex", $rowValueArray[$loopCount][31]);//engineer hotel-amount
      $this->setToRelatedSingleCell("AH$rowIndex", $rowValueArray[$loopCount][32]);//enginner other-amount
      $this->setToRelatedSingleCell("AI$rowIndex", $rowValueArray[$loopCount][33]);//engineer final amount



    }
    
    function commonRows_Duplicate_defaultValues($rowIndex, $rowValueArray, $loopCount)
    {
         // DIFFERENT FIELDS
          $claimStatus = $this->getClaimStatus_duplicateDefaultValues($rowValueArray[$loopCount][27], $rowIndex);//$rowValueArray[$loopCount][27] Claim status
         $this->setToRelatedSingleCell("AC$rowIndex",$claimStatus);


         //MERGED CELLS COMMON FILEDS
         $this->setToRelatedSingleCell("B$rowIndex",  $rowValueArray[$loopCount][0]);//emp-id
         $this->setToRelatedSingleCell("C$rowIndex", $rowValueArray[$loopCount][1]);//emp-name
         $this->setToRelatedSingleCell("E$rowIndex", $rowValueArray[$loopCount][3]);//site visited date

    //     $this->setToRelatedSingleCell("AC$rowIndex", $claimStatus);


    //   //MERGED CELLS COMMON FILEDS
    //   $this->setToRelatedSingleCell("B$rowIndex", $rowValueArray[$loopCount][0]);//emp-id
    //   $this->setToRelatedSingleCell("C$rowIndex", $rowValueArray[$loopCount][1]);//emp-name
    //   $this->setToRelatedSingleCell("E$rowIndex", $rowValueArray[$loopCount][3]);//site visited date
    //   $this->setToRelatedSingleCell("Z$rowIndex", $rowValueArray[$loopCount][24]);//total distance
    //   $this->setToRelatedSingleCell("AD$rowIndex", $rowValueArray[$loopCount][28]);//claimdate
    //   $this->setToRelatedSingleCell("AE$rowIndex", $rowValueArray[$loopCount][29]);//engineer DA
    //   $this->setToRelatedSingleCell("AF$rowIndex", $rowValueArray[$loopCount][30]);//enginner Da- Amount
    //   $this->setToRelatedSingleCell("AG$rowIndex", $rowValueArray[$loopCount][31]);//engineer hotel-amount
    //   $this->setToRelatedSingleCell("AH$rowIndex", $rowValueArray[$loopCount][32]);//enginner other-amount
    //   $this->setToRelatedSingleCell("AI$rowIndex", $rowValueArray[$loopCount][33]);//engineer final amount
    //   $this->setToRelatedSingleCell("AQ$rowIndex", $rowValueArray[$loopCount][42]); // TLdeducted reason,
    //   $this->setToRelatedSingleCell("AR$rowIndex", $rowValueArray[$loopCount][43]); // TL deducted amount
    //   $this->setToRelatedSingleCell("AZ$rowIndex", $rowValueArray[$loopCount][52]); // Finance deducted reason,
    //   $this->setToRelatedSingleCell("BA$rowIndex", $rowValueArray[$loopCount][53]); // Finance deducted amount
    //   $this->setToRelatedSingleCell("BL$rowIndex", $rowValueArray[$loopCount][55]);// TOTAL DEDUCTED AMOUNT



    }
    // function mergedCells($rowIndex, $rowValueArray, $loopCount, $merge, $duplicateDateCount)
    // {
    //     if ($merge) {
    //         $lastRowIndex = $rowIndex + ($duplicateDateCount - 1);
    //          //MERGING CELL
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "B");//EMPID
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "C");//EMP NAME
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "E");//SITE-VISITED- DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "Z");//TOTAL DISTANCE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AC");//CLAIM STATUS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AD");//CLAIM DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AE");//DEPUTATION ALL0WANCES
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AF");//DA-AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AG");//HOTEL BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AH");//OTHER AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AI");//FINAL BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AJ");//TL APPROVED DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AK");//TL DEPUTATION ALLOWANCES
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AL");//TL DA-AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AM");//APPROVED HOTEL BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AN");//APPROVED OTHER BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AO");//APPROVED FINAL  AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AP");//APPROVED TL
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AQ");//TL REASON
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AR");//TL-DEDUCTED REASON
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AS");//FINANCE APPROVED DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AT");//FINANCE  APPROVED DA
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AU");//FINANCE APPROVED DA-AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AV");//FINANCE APPROVED HOTEL BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AW");//FINANCE APPROVED OTHER BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AX");//FINANCE FINAL AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AY");//APPROVED FINANCE NAME AND ID
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "AZ");//FINANCE REASON
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BA");//FINANCE DEDUCTED AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BB");//PAID DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BC");//PAID DA
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BD");//PAID DA-AMOUNT
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BE");//PAID HOTEL BILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BF");//PAID OTHER BIILLS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BG");//PAID FINAL AMOUNTS
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BH");//PAID BY
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BI");//REJECTED DATE
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BJ");//REJECTED REASON
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BK");//REJECTED BY
    //          $this->mergeCellINExcel($rowIndex, $lastRowIndex, "BL");//TOTAL DEDUCTED AMOUNT
    //     }
    //      // DIFFERENT FIELDS
    //      $claimStatus = $this->getClaimStatus($rowValueArray[$loopCount][27], $rowIndex, $rowValueArray, $loopCount);//$rowValueArray[$loopCount][27] Claim status
    //     $this->setToRelatedSingleCell("AC$rowIndex", $claimStatus);


    //   //MERGED CELLS COMMON FILEDS
    //   $this->setToRelatedSingleCell("B$rowIndex", $rowValueArray[$loopCount][0]);//emp-id
    //   $this->setToRelatedSingleCell("C$rowIndex", $rowValueArray[$loopCount][1]);//emp-name
    //   $this->setToRelatedSingleCell("E$rowIndex", $rowValueArray[$loopCount][3]);//site visited date
    //   $this->setToRelatedSingleCell("Z$rowIndex", $rowValueArray[$loopCount][24]);//total distance
    //   $this->setToRelatedSingleCell("AD$rowIndex", $rowValueArray[$loopCount][28]);//claimdate
    //   $this->setToRelatedSingleCell("AE$rowIndex", $rowValueArray[$loopCount][29]);//engineer DA
    //   $this->setToRelatedSingleCell("AF$rowIndex", $rowValueArray[$loopCount][30]);//enginner Da- Amount
    //   $this->setToRelatedSingleCell("AG$rowIndex", $rowValueArray[$loopCount][31]);//engineer hotel-amount
    //   $this->setToRelatedSingleCell("AH$rowIndex", $rowValueArray[$loopCount][32]);//enginner other-amount
    //   $this->setToRelatedSingleCell("AI$rowIndex", $rowValueArray[$loopCount][33]);//engineer final amount
    //   $this->setToRelatedSingleCell("AQ$rowIndex", $rowValueArray[$loopCount][42]); // TLdeducted reason,
    //   $this->setToRelatedSingleCell("AR$rowIndex", $rowValueArray[$loopCount][43]); // TL deducted amount
    //   $this->setToRelatedSingleCell("AZ$rowIndex", $rowValueArray[$loopCount][52]); // Finance deducted reason,
    //   $this->setToRelatedSingleCell("BA$rowIndex", $rowValueArray[$loopCount][53]); // Finance deducted amount
    //   $this->setToRelatedSingleCell("BL$rowIndex", $rowValueArray[$loopCount][55]);// TOTAL DEDUCTED AMOUNT



    // }
    function unMergedCells($rowIndex, $rowValueArray, $loopCount)
    {
        $selfyproof = "Yes";
        if (empty($rowValueArray[$loopCount][2])) {
            $selfyproof = "No";
        }
        $this->setToRelatedSingleCell("D$rowIndex", $selfyproof);//self image
        $this->setToRelatedSingleCell("F$rowIndex", $rowValueArray[$loopCount][4]);//user location
        $this->setToRelatedSingleCell("G$rowIndex", $rowValueArray[$loopCount][5]);//user-lat
        $this->setToRelatedSingleCell("H$rowIndex", $rowValueArray[$loopCount][6]);//user-long
        $this->setToRelatedSingleCell("I$rowIndex", $rowValueArray[$loopCount][7]);//site-lat
        $this->setToRelatedSingleCell("J$rowIndex", $rowValueArray[$loopCount][8]);//site-long
        $this->setToRelatedSingleCell("K$rowIndex", $rowValueArray[$loopCount][9]);//site no of the day
        $this->setToRelatedSingleCell("L$rowIndex", $rowValueArray[$loopCount][10]);//siteId
        $this->setToRelatedSingleCell("M$rowIndex", $rowValueArray[$loopCount][11]);//siteName
        $this->setToRelatedSingleCell("N$rowIndex", $rowValueArray[$loopCount][12]);//project
        $this->setToRelatedSingleCell("O$rowIndex", $rowValueArray[$loopCount][13]);//stage of audit
        $this->setToRelatedSingleCell("P$rowIndex", $rowValueArray[$loopCount][14]);//customer
        $this->setToRelatedSingleCell("Q$rowIndex", $rowValueArray[$loopCount][15]);//district
        $this->setToRelatedSingleCell("R$rowIndex", $rowValueArray[$loopCount][16]);//circle
        $this->setToRelatedSingleCell("S$rowIndex", $rowValueArray[$loopCount][17]);//site-town
        $this->setToRelatedSingleCell("T$rowIndex", $rowValueArray[$loopCount][18]);//report submission state
        $this->setToRelatedSingleCell("U$rowIndex", $rowValueArray[$loopCount][19]);//mode of transport
        $this->setToRelatedSingleCell("V$rowIndex", $rowValueArray[$loopCount][20]);//supporting bills
        $this->setToRelatedSingleCell("W$rowIndex", $rowValueArray[$loopCount][21]);//site login
        $this->setToRelatedSingleCell("X$rowIndex", $rowValueArray[$loopCount][22]);//site logout
        $this->setToRelatedSingleCell("Y$rowIndex", $rowValueArray[$loopCount][23]);//distance
        $this->setToRelatedSingleCell("AA$rowIndex", $rowValueArray[$loopCount][25]);//fta
        $this->setToRelatedSingleCell("AB$rowIndex", $rowValueArray[$loopCount][26]);//site remarks
    }

    function getClaimStatus($claimValue, $rowIndex, $rowValueArray, $loopCount)
    {
          //echo $claimValue;
          switch ($claimValue) {
            case null: //NOT AT CLAIMED
                return "SUBMISSION PENDING";
                break;
            case CLAIM_PENDING: //CLAIM-PENDING
                $this->changeTextColor("AC$rowIndex", "ffc107");
                return "TL APPROVAL PENDING";
                break;
            case CLAIM_TL_APPROVED: //TL-APPROVED
                $this->changeTextColor("AC$rowIndex", "007bff");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "TL APPROVED";
                break;
            case CLAIM_TL_REJECTED: //TL-REJECT
                $this->changeTextColor("AC$rowIndex", "ff0000");
                $this->setToTLRejectedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "REJECTED";
                break;
            case CLAIM_FINANCE_APPROVED: //FINANCE-APPROVED
                $this->changeTextColor("AC$rowIndex", "4b0082");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setToFinanceApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "FINANCE APPROVED";
                break;
            case CLAIM_FINANCE_REJECTED: //FINANCE-REJECTED
                $this->changeTextColor("AC$rowIndex", "ff0000");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setFinanceRejectedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "REJECTED";
            case CLAIM_PAID: //FINANCE-REJECTED
                $this->changeTextColor("AC$rowIndex", "28a745");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setToFinanceApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setToPaidCellValues($rowIndex, $rowValueArray, $loopCount);

                return "PAID";
                break;
        }
    }

    function getClaimStatus_duplicateDefaultValues($claimValue, $rowIndex)
    {
          switch ($claimValue) {
            case null: //NOT AT CLAIMED
                return "SUBMISSION PENDING";
                break;
            case CLAIM_PENDING: //CLAIM-PENDING
                $this->changeTextColor("AC$rowIndex", "ffc107");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                return "TL APPROVAL PENDING";
                break;
            case CLAIM_TL_APPROVED: //TL-APPROVED
                $this->changeTextColor("AC$rowIndex", "007bff");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                return "TL APPROVED";
                break;
            case CLAIM_TL_REJECTED: //TL-REJECT
                $this->changeTextColor("AC$rowIndex", "ff0000");
                $this->setEngineerValueCell_defaultValues($rowIndex);
               $this->setToTLRejectedCell_defaultValues($rowIndex);
                return "REJECTED";
                break;
            case CLAIM_FINANCE_APPROVED: //FINANCE-APPROVED
                $this->changeTextColor("AC$rowIndex", "4b0082");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                $this->setToFinanceApprovedCell_defaultValues($rowIndex);
                return "FINANCE APPROVED";
                break;
            case CLAIM_FINANCE_REJECTED: //FINANCE-REJECTED
                $this->changeTextColor("AC$rowIndex", "ff0000");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                $this->setFinanceRejectedCell_defaultValues($rowIndex);
                return "REJECTED";
            case CLAIM_PAID: //FINANCE-REJECTED
                $this->changeTextColor("AC$rowIndex", "28a745");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                $this->setToFinanceApprovedCell_defaultValues($rowIndex);
                $this->setToPaidCell_defaultValues($rowIndex);
                return "PAID";
                break;
        }
    }


    function setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount)
    {
        $this->setToRelatedSingleCell("AJ$rowIndex", $rowValueArray[$loopCount][34]);//TL APPROVED DATE
        $this->setToRelatedSingleCell("AK$rowIndex", $rowValueArray[$loopCount][35]);//TL APPROVED DA
        $this->setToRelatedSingleCell("AL$rowIndex", $rowValueArray[$loopCount][36]);//TL APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AM$rowIndex", $rowValueArray[$loopCount][37]);//TL HOTEL BILLS
        $this->setToRelatedSingleCell("AN$rowIndex", $rowValueArray[$loopCount][38]);//TL OTHER BILLS
        $this->setToRelatedSingleCell("AO$rowIndex", $rowValueArray[$loopCount][39]);//TL FINAL AMOUNT
        $this->setToRelatedSingleCell("AP$rowIndex", $rowValueArray[$loopCount][40] . " " . $rowValueArray[$loopCount][41]);// APPROVED TL NAME AND ID
        $this->setToRelatedSingleCell("AQ$rowIndex", $rowValueArray[$loopCount][42]); // TLdeducted reason,
        $this->setToRelatedSingleCell("AR$rowIndex", $rowValueArray[$loopCount][43]); // TL deducted amount

    }

    function setToTLRejectedCellValues($rowIndex, $rowValueArray, $loopCount)
    {

        $this->setToRelatedSingleCell("BI$rowIndex", $rowValueArray[$loopCount][56]);//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("BJ$rowIndex", $rowValueArray[$loopCount][54]);//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("BK$rowIndex", $rowValueArray[$loopCount][40] . " " . $rowValueArray[$loopCount][41]);//REJECTED BY TL NAME AND ID
    }

    function setToFinanceApprovedCellValues($rowIndex, $rowValueArray, $loopCount)
    {
        $this->setToRelatedSingleCell("AS$rowIndex", $rowValueArray[$loopCount][44]);//FINANCE APPROVED DATE
        $this->setToRelatedSingleCell("AT$rowIndex", $rowValueArray[$loopCount][45]);//FINANCE APPROVED DA
        $this->setToRelatedSingleCell("AU$rowIndex", $rowValueArray[$loopCount][46]);//FINANCE APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AV$rowIndex", $rowValueArray[$loopCount][47]);//FINANCE HOTEL BILLS
        $this->setToRelatedSingleCell("AW$rowIndex", $rowValueArray[$loopCount][48]);//FINANCE OTHER BILLS
        $this->setToRelatedSingleCell("AX$rowIndex", $rowValueArray[$loopCount][49]);//FINANCE FINAL AMOUNT
        $this->setToRelatedSingleCell("AY$rowIndex", $rowValueArray[$loopCount][50] . " " . $rowValueArray[$loopCount][51]);// APPROVED FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AZ$rowIndex", $rowValueArray[$loopCount][52]); // Finance deducted reason,
        $this->setToRelatedSingleCell("BA$rowIndex", $rowValueArray[$loopCount][53]); // Finance deducted amount
   }
    function setFinanceRejectedCellValues($rowIndex, $rowValueArray, $loopCount)
    {
        $this->setToRelatedSingleCell("BI$rowIndex", $rowValueArray[$loopCount][56]);//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("BJ$rowIndex", $rowValueArray[$loopCount][54]);//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("BK$rowIndex", $rowValueArray[$loopCount][50] . " " . $rowValueArray[$loopCount][51]);// REJECTED BY FINANCE NAME AND ID

    }
    function setToPaidCellValues($rowIndex, $rowValueArray, $loopCount){
        $this->setToRelatedSingleCell("BB$rowIndex", $rowValueArray[$loopCount][57]);//PAID  DATE
        $this->setToRelatedSingleCell("BC$rowIndex", $rowValueArray[$loopCount][45]);//PAID APPROVED DA
        $this->setToRelatedSingleCell("BD$rowIndex", $rowValueArray[$loopCount][46]);//PAID APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("BE$rowIndex", $rowValueArray[$loopCount][47]);//PAID HOTEL BILLS
        $this->setToRelatedSingleCell("BF$rowIndex", $rowValueArray[$loopCount][48]);//PAID OTHER BILLS
        $this->setToRelatedSingleCell("BG$rowIndex", $rowValueArray[$loopCount][49]);//PAID FINAL AMOUNT
        $this->setToRelatedSingleCell("BH$rowIndex", $rowValueArray[$loopCount][58]);// PAID FINANCE NAME AND ID
        $this->setToRelatedSingleCell("BL$rowIndex", $rowValueArray[$loopCount][55]);// TOTAL DEDUCTED AMOUNT
    }

 // default static duplicate values
    function setEngineerValueCell_defaultValues($rowIndex){
        $this->setToRelatedSingleCell("Z$rowIndex", 0);//total distance
        $this->setToRelatedSingleCell("AD$rowIndex", "NA");//claimdate
        $this->setToRelatedSingleCell("AE$rowIndex", "NA");//engineer DA
        $this->setToRelatedSingleCell("AF$rowIndex", 0);//enginner Da- Amount
        $this->setToRelatedSingleCell("AG$rowIndex", 0);//engineer hotel-amount
        $this->setToRelatedSingleCell("AH$rowIndex", 0);//enginner other-amount
        $this->setToRelatedSingleCell("AI$rowIndex", 0);//engineer final amount
    }

     function setToTLApprovedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("AJ$rowIndex", "NA");//TL APPROVED DATE
        $this->setToRelatedSingleCell("AK$rowIndex", "NA");//TL APPROVED DA
        $this->setToRelatedSingleCell("AL$rowIndex", 0);//TL APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AM$rowIndex", 0);//TL HOTEL BILLS
        $this->setToRelatedSingleCell("AN$rowIndex", 0);//TL OTHER BILLS
        $this->setToRelatedSingleCell("AO$rowIndex", 0);//TL FINAL AMOUNT
        $this->setToRelatedSingleCell("AP$rowIndex", "NA");// APPROVED TL NAME AND ID
        $this->setToRelatedSingleCell("AQ$rowIndex", "NA"); // TLdeducted reason,
        $this->setToRelatedSingleCell("AR$rowIndex", 0); // TL deducted amount

    }

    function setToTLRejectedCell_defaultValues($rowIndex)
    {

        $this->setToRelatedSingleCell("BI$rowIndex", "NA");//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("BJ$rowIndex", "NA");//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("BK$rowIndex", "NA");//REJECTED BY TL NAME AND ID
    }

    function setToFinanceApprovedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("AS$rowIndex", "NA");//FINANCE APPROVED DATE
        $this->setToRelatedSingleCell("AT$rowIndex", "NA");//FINANCE APPROVED DA
        $this->setToRelatedSingleCell("AU$rowIndex", 0);//FINANCE APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AV$rowIndex", 0);//FINANCE HOTEL BILLS
        $this->setToRelatedSingleCell("AW$rowIndex", 0);//FINANCE OTHER BILLS
        $this->setToRelatedSingleCell("AX$rowIndex", 0);//FINANCE FINAL AMOUNT
        $this->setToRelatedSingleCell("AY$rowIndex", "NA");// APPROVED FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AZ$rowIndex", "NA"); // Finance deducted reason,
        $this->setToRelatedSingleCell("BA$rowIndex", 0); // Finance deducted amount
   }
    function setFinanceRejectedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("BI$rowIndex", "NA");//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("BJ$rowIndex", "NA");//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("BK$rowIndex", "NA");// REJECTED BY FINANCE NAME AND ID

    }
    function setToPaidCell_defaultValues($rowIndex){
        $this->setToRelatedSingleCell("BB$rowIndex", "NA");//PAID  DATE
        $this->setToRelatedSingleCell("BC$rowIndex", "NA");//PAID APPROVED DA
        $this->setToRelatedSingleCell("BD$rowIndex", 0);//PAID APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("BE$rowIndex", 0);//PAID HOTEL BILLS
        $this->setToRelatedSingleCell("BF$rowIndex", 0);//PAID OTHER BILLS
        $this->setToRelatedSingleCell("BG$rowIndex", 0);//PAID FINAL AMOUNT
        $this->setToRelatedSingleCell("BH$rowIndex", "NA");// PAID FINANCE NAME AND ID
        $this->setToRelatedSingleCell("BL$rowIndex", 0);// TOTAL DEDUCTED AMOUNT
    }

    function getThisDateDuplicateCount($EmployeAndDate)
    {
        return $this->noOfDuplicateDateCountArray[$EmployeAndDate]; // returns count (no.of duplicate this date contains)
    }
    function saveExcel($filename)
    {
        $file = PHPExcel_IOFactory::createWriter($this->activeSheet, 'Excel2007');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //mime type
        header('Content-Disposition: attachment;filename=' . $filename . '.xlsx'); //tell browser what's the file name
        header('Cache-Control: max-age=0'); //no cache
        ob_end_clean();
        $file->save('php://output');
        exit();
    }
}
?>

// $obj = new Excel();
// $obj->fetchData("2020-01-01", "2020-09-22");
