<?php

session_start();
if (isset($_GET['fromDate'])) {
    // $requestFrom=$_GET['requestedFrom_Web_OR_Mobile'];
    $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;
        $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 `normal_site_visit`.`ENGINEER_EMP_NAME`,`normal_site_visit`.`SITE_VISIT_ORDER`,`normal_site_visit`.`ENGINEER_EMP_ID`,`normal_site_visit`.`SITE_VISIT_DATE`, `normal_site_visit`.`DISTANCE_(KMS)`,`normal_site_visit`.`STARTING_LOCATION`, `normal_site_visit`.`DESTINATION_LOCATION`,"
                            ."`normal_site_visit_2`.`TOTAL_DISTANCE`, `normal_site_visit_2`.`ENGINEER_OTHER_BILLS_AMOUNT`, `normal_site_visit_2`.`TL_APPROVED_OTHER_BILLS_AMOUNT`,`normal_site_visit_2`.`FINANCE_APPROVED_OTHER_BILLS_AMOUNT`, `normal_site_visit_2`.`ENGINEER_HOTEL_BILLS_AMOUNT`,  `normal_site_visit_2`.`TL_APPROVED_HOTEL_BILLS_AMOUNT` ,"
                            ."`normal_site_visit_2`.`FINANCE_APPROVED_HOTEL_BILLS_AMOUNT`, `normal_site_visit_2`.`CLAIM_STATUS`, `normal_site_visit_2`.`CLAIM_DATE`, `normal_site_visit_2`.`ENGINEER_DEPUTATION_ALLOWANCE`, `normal_site_visit_2`.`TL_APPROVED_DEPUTATION_ALLOWANCE`,`normal_site_visit_2`.`FINANCE_APPROVED_DEPUTATION_ALLOWANCE`,"
                            ."`normal_site_visit_2`.`ENGINEER_DA_AMOUNT`, `normal_site_visit_2`.`TL_APPROED_DA_AMOUNT`,  `normal_site_visit_2`.`FINANCE_APPROVED_DA_AMOUNT`, `normal_site_visit_2`.`ENGINEER_FINAL_AMOUNT`, `normal_site_visit_2`.`TL_APPROVED_FINAL_AMOUNT`, `normal_site_visit_2`.`FINANCE_APPROVED_FINAL_AMOUNT`,"
                            ."`normal_site_visit_2`.`REJECT_REASON`, `normal_site_visit_2`.`CLAIM_STATUS_UPDATED_BY`, `normal_site_visit_2`.`TL_DEDUCTED_AMOUNT`, `normal_site_visit_2`.`FINANCE_DEDUCTED_AMOUNT`, `normal_site_visit_2`.`TL_APPROVED_DATE`, `normal_site_visit_2`.`FINANCE_APPROVED_DATE`, `normal_site_visit_2`.`FINANCE_NAME`,"
                            ."`normal_site_visit_2`.`FINANCE_EMP_ID`, `normal_site_visit_2`.`TL_NAME`, `normal_site_visit_2`.`TL_EMP_ID`, `normal_site_visit_2`.`TL_DEDUCTED_REASON`, `normal_site_visit_2`.`FINANCE_DEDUCTED_REASON`, `normal_site_visit_2`.`TL_OR_FINANCE_REJECTED_DATE`, `normal_site_visit_2`.`TOTAL_DEDUCTED_AMOUNT`, `normal_site_visit_2`.`PAID_DATE`, `normal_site_visit_2`.`PAID_BY`";

        $normalVisit_fromStatement='FROM `normal_site_visit` LEFT JOIN `normal_site_visit_2` ON `normal_site_visit_2`.`SITE_VISIT_DATE`=`normal_site_visit`.`SITE_VISIT_DATE` AND `normal_site_visit_2`.`ENGINEER_EMP_ID`=`normal_site_visit`.`ENGINEER_EMP_ID` WHERE';
        $formSnormalVisit_2_fromStatementtatement='FROM `normal_site_visit_2` LEFT JOIN `normal_site_visit` ON `normal_site_visit`.`ENGINEER_EMP_ID`=`normal_site_visit_2`.`ENGINEER_EMP_ID` AND `normal_site_visit`.`SITE_VISIT_DATE`=`normal_site_visit_2`.`SITE_VISIT_DATE` WHERE';
        if ($typeOfExcel == BULK_EXCEL) {
            if($loggedInUserDepartment==FINANCE_DEPARTMENT){
                $query = $this->db->prepare($selectStatement . $normalVisit_fromStatement. "`normal_site_visit`.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY `normal_site_visit`.`SITE_VISIT_DATE` ASC,`normal_site_visit`.`ENGINEER_EMP_ID` ASC,`normal_site_visit`.`SITE_VISIT_ORDER` ASC");
                $query->bind_param("ss", $fromDate, $toDate);
            }elseif($loggedInUserDepartment==TL_DEPARTMENT){
                $query = $this->db->prepare($selectStatement . $formSnormalVisit_2_fromStatementtatement. "`normal_site_visit_2`.`TL_EMP_ID`=? AND `normal_site_visit_2`.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY `normal_site_visit_2`.`SITE_VISIT_DATE` ASC,`normal_site_visit_2`.`ENGINEER_EMP_ID` ASC");
                $query->bind_param("sss", $loggedInUserID, $fromDate, $toDate);
            }
            $filename = $fromDate . '_TO_' . $toDate;
        } elseif ($typeOfExcel == INDIVIDUAL_EXCEL) {
            $query = $this->db->prepare($selectStatement . $normalVisit_fromStatement. "`normal_site_visit`.`ENGINEER_EMP_ID`=? AND `normal_site_visit`.`SITE_VISIT_DATE` BETWEEN ? AND ?  ORDER BY `normal_site_visit`.`SITE_VISIT_DATE`,`normal_site_visit`.`SITE_VISIT_ORDER` ASC");
            $query->bind_param("sss", $empId, $fromDate, $toDate);
            $filename = $empId . "_FROM_" . $fromDate . '_TO_' . $toDate;
        }
        $query->execute();
        $query->bind_result($normalSiteVisit_name, $normalSiteVisit_SiteVisitedOrder, $normalSiteVisit_empid, $normalSiteVisit_date, $normalSiteVisit_distance, $normalSiteVisit_Starting_location, $normalSiteVisit_Destination_location,
                            $normalSiteVisit2_TotalDistance, $normalSiteVisit2_engineerOtherBillsAmount, $normalSiteVisit2_TLApprovedOtherBill, $normalSiteVisit2_financeApprovedOtherBill, $normalSiteVisit2_engineerHotelBillsAmount, $normalSiteVisit2_TLApprovedHotelBill,
                            $normalSiteVisit2_financeApprovedHotelBill, $normalSiteVisit2_claimstatus, $normalSiteVisit2_claimdate, $normalSiteVisit2_engineerDA, $normalSiteVisit2_TLApprovedDA, $normalSiteVisit2_financeApprovedDA, $normalSiteVisit2_engineerDAAmount, $normalSiteVisit2_TLApprovedDAAmount,
                            $normalSiteVisit2_financeApprovedDAAmount, $normalSiteVisit2_engineerFinalamount, $normalSiteVisit2_TLApprovedFinalAmount, $normalSiteVisit2_financeApprovedFinalAmount, $normalSiteVisit2_rejectReason, $normalSiteVisit2_claimStatusUpdatedBy, $normalSiteVisit2_TLDeductedAmount, $normalSiteVisit2_financeDeductedAmount, $normalSiteVisit2_TLApproveDate, $normalSiteVisit2_financeApprovedDate, $normalSiteVisit2_financeEmpName, $normalSiteVisit2_financeEmpId, $normalSiteVisit2_TLName, $normalSiteVisit2_TLEmpId, $normalSiteVisit2_TLDeductedReason, $normalSiteVisit2_financeDeductedReason, $normalSiteVisit2_tlOrFinanceRejectedDate, $normalSiteVisit2_TotalDeductedAmount, $normalSiteVisit2_PaidDate, $normalSiteVisit2_PaidBy);
        $query->store_result();

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

                $data[0] = $normalSiteVisit_empid;
                $data[1] = $normalSiteVisit_name;
                $data[2] = $normalSiteVisit_date;
                $data[3] = $normalSiteVisit_Starting_location;
                $data[4] = $normalSiteVisit_Destination_location;
                $data[5] = $normalSiteVisit_SiteVisitedOrder;
                $data[6] = $normalSiteVisit_distance;
                $data[7] = $normalSiteVisit2_TotalDistance;
                $data[8] = $normalSiteVisit2_claimstatus;
                $data[9] = $normalSiteVisit2_claimdate;
                $data[10] = $normalSiteVisit2_engineerDA;
                $data[11] = $normalSiteVisit2_engineerDAAmount;
                $data[12] = $normalSiteVisit2_engineerHotelBillsAmount;
                $data[13] = $normalSiteVisit2_engineerOtherBillsAmount;
                $data[14] = $normalSiteVisit2_engineerFinalamount;
                $data[15] = $normalSiteVisit2_TLApproveDate;
                $data[16] = $normalSiteVisit2_TLApprovedDA;
                $data[17] = $normalSiteVisit2_TLApprovedDAAmount;
                $data[18] = $normalSiteVisit2_TLApprovedHotelBill;
                $data[19] = $normalSiteVisit2_TLApprovedOtherBill;
                $data[20] = $normalSiteVisit2_TLApprovedFinalAmount;
                $data[21] = $normalSiteVisit2_TLName;
                $data[22] = $normalSiteVisit2_TLEmpId;
                $data[23] = $normalSiteVisit2_TLDeductedReason;
                $data[24] = $normalSiteVisit2_TLDeductedAmount;
                $data[25] = $normalSiteVisit2_financeApprovedDate;
                $data[26] = $normalSiteVisit2_financeApprovedDA;
                $data[27] = $normalSiteVisit2_financeApprovedDAAmount;
                $data[28] = $normalSiteVisit2_financeApprovedHotelBill;
                $data[29] = $normalSiteVisit2_financeApprovedOtherBill;
                $data[30] = $normalSiteVisit2_financeApprovedFinalAmount;
                $data[31] = $normalSiteVisit2_financeEmpName;
                $data[32] = $normalSiteVisit2_financeEmpId;
                $data[33] = $normalSiteVisit2_financeDeductedReason;
                $data[34] = $normalSiteVisit2_financeDeductedAmount;
                $data[35] = $normalSiteVisit2_rejectReason;// tl or finance reject reason
                $data[36] = $normalSiteVisit2_TotalDeductedAmount;
                $data[37] = $normalSiteVisit2_tlOrFinanceRejectedDate;
                $data[38] = $normalSiteVisit2_PaidDate;
                $data[39] = $normalSiteVisit2_PaidBy;



                array_push($cellValueArray, $data);
            }
            $this->bindValuesToExcel($cellValueArray); //2d-array
           // $this->saveOrDownloadExcel($requestFrom, $filename);
           $this->downloadExcel($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][2]); //merging-cell-based-on ("$cellValueArray[$i][0]"--> empid) and (" $cellValueArray[$i][2])"-->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][2]); //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)
    {
         // MERGED CELLS DIFFERENT FIELDS
        $claimStatus = $this->getClaimStatus($rowValueArray[$loopCount][8], $rowIndex, $rowValueArray, $loopCount);//$rowValueArray[$loopCount][27] Claim status
        $this->setToRelatedSingleCell("J$rowIndex", $claimStatus);//claim status

        //MERGED CELLS COMMON FILEDS
        $this->setToRelatedSingleCell("B$rowIndex", $rowValueArray[$loopCount][0]);//emp-id
        $this->setToRelatedSingleCell("C$rowIndex", $rowValueArray[$loopCount][1]);//emp-name
        $this->setToRelatedSingleCell("D$rowIndex", $rowValueArray[$loopCount][2]);//site visited date
        $this->setToRelatedSingleCell("I$rowIndex", $rowValueArray[$loopCount][7]);//total distance
        //$this->setToRelatedSingleCell("J$rowIndex", $rowValueArray[$loopCount][8]);//claim Status
        $this->setToRelatedSingleCell("K$rowIndex", $rowValueArray[$loopCount][9]);//Claim Date
        $this->setToRelatedSingleCell("L$rowIndex", $rowValueArray[$loopCount][10]);//enginner Da
        $this->setToRelatedSingleCell("M$rowIndex", $rowValueArray[$loopCount][11]);//engineer Da- Amount
        $this->setToRelatedSingleCell("N$rowIndex", $rowValueArray[$loopCount][12]);//enginner hotel-amount
        $this->setToRelatedSingleCell("O$rowIndex", $rowValueArray[$loopCount][13]);//engineer other-amount
        $this->setToRelatedSingleCell("P$rowIndex", $rowValueArray[$loopCount][14]); //engineer final amount



    }
     function commonRows_Duplicate_defaultValues($rowIndex, $rowValueArray, $loopCount)
    {
         // DIFFERENT FIELDS
          $claimStatus = $this->getClaimStatus_duplicateDefaultValues($rowValueArray[$loopCount][8], $rowIndex);//$rowValueArray[$loopCount][27] Claim status
         $this->setToRelatedSingleCell("J$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("D$rowIndex", $rowValueArray[$loopCount][2]);//site visited date


    }
    
    function unMergedCells($rowIndex, $rowValueArray, $loopCount)
    {
        $this->setToRelatedSingleCell("E$rowIndex", $rowValueArray[$loopCount][3]);//From User location
        $this->setToRelatedSingleCell("F$rowIndex", $rowValueArray[$loopCount][4]);//To Location
        $this->setToRelatedSingleCell("G$rowIndex", $rowValueArray[$loopCount][5]);//Site On Of the Day
        $this->setToRelatedSingleCell("H$rowIndex", $rowValueArray[$loopCount][6]);//Distance
    }

    function getClaimStatus($claimValue, $rowIndex, $rowValueArray, $loopCount)
    {
        //echo $claimValue;
        switch ($claimValue) {
            case null: //NOT AT CLAIMED
                return "NOT AT CLAIMED";
                break;
            case CLAIM_PENDING: //CLAIM-PENDING
                $this->changeTextColor("J$rowIndex", "ffc107");
                return "CLAIM PENDING";
                break;
            case CLAIM_TL_APPROVED: //TL-APPROVED
                $this->changeTextColor("J$rowIndex", "007bff");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "TL APPROVED";
                break;
            case CLAIM_TL_REJECTED: //TL-REJECT
                $this->changeTextColor("J$rowIndex", "ff0000");
                $this->setToTLRejectedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "REJECTED";
                break;
            case CLAIM_FINANCE_APPROVED: //FINANCE-APPROVED
                $this->changeTextColor("J$rowIndex", "4b0082");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setToFinanceApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "FINANCE APPROVED";
                break;
            case CLAIM_FINANCE_REJECTED: //FINANCE-REJECTED
                $this->changeTextColor("J$rowIndex", "ff0000");
                $this->setToTLApprovedCellValues($rowIndex, $rowValueArray, $loopCount);
                $this->setFinanceRejectedCellValues($rowIndex, $rowValueArray, $loopCount);
                return "REJECTED";
            case CLAIM_PAID: //FINANCE-REJECTED
                $this->changeTextColor("J$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 "NOT AT CLAIMED";
                break;
            case CLAIM_PENDING: //CLAIM-PENDING
                $this->changeTextColor("J$rowIndex", "ffc107");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                return "CLAIM PENDING";
                break;
            case CLAIM_TL_APPROVED: //TL-APPROVED
                $this->changeTextColor("J$rowIndex", "007bff");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                return "TL APPROVED";
                break;
            case CLAIM_TL_REJECTED: //TL-REJECT
                $this->changeTextColor("J$rowIndex", "ff0000");
                $this->setEngineerValueCell_defaultValues($rowIndex);
               $this->setToTLRejectedCell_defaultValues($rowIndex);
                return "REJECTED";
                break;
            case CLAIM_FINANCE_APPROVED: //FINANCE-APPROVED
                $this->changeTextColor("J$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("J$rowIndex", "ff0000");
                $this->setEngineerValueCell_defaultValues($rowIndex);
                $this->setToTLApprovedCell_defaultValues($rowIndex);
                $this->setFinanceRejectedCell_defaultValues($rowIndex);
                return "REJECTED";
            case CLAIM_PAID: //FINANCE-REJECTED
                $this->changeTextColor("J$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("Q$rowIndex", $rowValueArray[$loopCount][15]);//TL APPROVED DATE
        $this->setToRelatedSingleCell("R$rowIndex", $rowValueArray[$loopCount][16]);//TL APPROVED DA
        $this->setToRelatedSingleCell("S$rowIndex", $rowValueArray[$loopCount][17]);//TL APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("T$rowIndex", $rowValueArray[$loopCount][18]);//TL HOTEL BILLS
        $this->setToRelatedSingleCell("U$rowIndex", $rowValueArray[$loopCount][19]);//TL OTHER BILLS
        $this->setToRelatedSingleCell("V$rowIndex", $rowValueArray[$loopCount][20]);//TL FINAL AMOUNT
        $this->setToRelatedSingleCell("W$rowIndex", $rowValueArray[$loopCount][21] . " " . $rowValueArray[$loopCount][22]);// APPROVED TL NAME AND ID
        $this->setToRelatedSingleCell("X$rowIndex", $rowValueArray[$loopCount][23]);//TL deduct reason
        $this->setToRelatedSingleCell("Y$rowIndex", $rowValueArray[$loopCount][24]); //TL deduct Amount

    }

    function setToTLRejectedCellValues($rowIndex, $rowValueArray, $loopCount)
    {
        
        $this->setToRelatedSingleCell("AP$rowIndex", $rowValueArray[$loopCount][37]);//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("AQ$rowIndex", $rowValueArray[$loopCount][35]);//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("AR$rowIndex", $rowValueArray[$loopCount][21] . " " . $rowValueArray[$loopCount][22]);//REJECTED BY TL NAME AND ID
    }

    function setToFinanceApprovedCellValues($rowIndex, $rowValueArray, $loopCount)
    {   
        
        $this->setToRelatedSingleCell("Z$rowIndex", $rowValueArray[$loopCount][25]);//FINANCE APPROVED DATE
        $this->setToRelatedSingleCell("AA$rowIndex", $rowValueArray[$loopCount][26]);//FINANCE APPROVED DA
        $this->setToRelatedSingleCell("AB$rowIndex", $rowValueArray[$loopCount][27]);//FINANCE APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AC$rowIndex", $rowValueArray[$loopCount][28]);//FINANCE HOTEL BILLS
        $this->setToRelatedSingleCell("AD$rowIndex", $rowValueArray[$loopCount][29]);//FINANCE OTHER BILLS
        $this->setToRelatedSingleCell("AE$rowIndex", $rowValueArray[$loopCount][30]);//FINANCE FINAL AMOUNT
        $this->setToRelatedSingleCell("AF$rowIndex", $rowValueArray[$loopCount][31] . " " . $rowValueArray[$loopCount][32]);// APPROVED FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AG$rowIndex", $rowValueArray[$loopCount][33]);//Finance deduct reason
        $this->setToRelatedSingleCell("AH$rowIndex", $rowValueArray[$loopCount][34]); //Finance deduct Amount
    }
    function setFinanceRejectedCellValues($rowIndex, $rowValueArray, $loopCount)
    {
        
        $this->setToRelatedSingleCell("AP$rowIndex", $rowValueArray[$loopCount][37]);//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("AQ$rowIndex", $rowValueArray[$loopCount][54]);//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("AR$rowIndex", $rowValueArray[$loopCount][31] . " " . $rowValueArray[$loopCount][32]);// REJECTED BY FINANCE NAME AND ID

    }
    function setToPaidCellValues($rowIndex, $rowValueArray, $loopCount){
        $this->setToRelatedSingleCell("AI$rowIndex", $rowValueArray[$loopCount][38]);//PAID  DATE
        $this->setToRelatedSingleCell("AJ$rowIndex", $rowValueArray[$loopCount][26]);//PAID APPROVED DA
        $this->setToRelatedSingleCell("AK$rowIndex", $rowValueArray[$loopCount][27]);//PAID APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AL$rowIndex", $rowValueArray[$loopCount][28]);//PAID HOTEL BILLS
        $this->setToRelatedSingleCell("AM$rowIndex", $rowValueArray[$loopCount][29]);//PAID OTHER BILLS
        $this->setToRelatedSingleCell("AN$rowIndex", $rowValueArray[$loopCount][30]);//PAID FINAL AMOUNT
        $this->setToRelatedSingleCell("AO$rowIndex", $rowValueArray[$loopCount][39]);// PAID FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AS$rowIndex", $rowValueArray[$loopCount][36]);// PAID FINANCE NAME AND ID
    }


// default static duplicate values
    function setEngineerValueCell_defaultValues($rowIndex){
        $this->setToRelatedSingleCell("I$rowIndex", 0);//total distance
        $this->setToRelatedSingleCell("K$rowIndex", "NA");//claimdate
        $this->setToRelatedSingleCell("L$rowIndex", "NA");//engineer DA
        $this->setToRelatedSingleCell("M$rowIndex", 0);//enginner Da- Amount
        $this->setToRelatedSingleCell("N$rowIndex", 0);//engineer hotel-amount
        $this->setToRelatedSingleCell("O$rowIndex", 0);//enginner other-amount
        $this->setToRelatedSingleCell("P$rowIndex", 0);//engineer final amount
    }

     function setToTLApprovedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("Q$rowIndex", "NA");//TL APPROVED DATE
        $this->setToRelatedSingleCell("R$rowIndex", "NA");//TL APPROVED DA
        $this->setToRelatedSingleCell("S$rowIndex", 0);//TL APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("T$rowIndex", 0);//TL HOTEL BILLS
        $this->setToRelatedSingleCell("U$rowIndex", 0);//TL OTHER BILLS
        $this->setToRelatedSingleCell("V$rowIndex", 0);//TL FINAL AMOUNT
        $this->setToRelatedSingleCell("W$rowIndex", "NA");// APPROVED TL NAME AND ID
        $this->setToRelatedSingleCell("X$rowIndex", "NA"); // TLdeducted reason,
        $this->setToRelatedSingleCell("Y$rowIndex", 0); // TL deducted amount

    }

    function setToTLRejectedCell_defaultValues($rowIndex)
    {

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

    function setToFinanceApprovedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("Z$rowIndex", "NA");//FINANCE APPROVED DATE
        $this->setToRelatedSingleCell("AA$rowIndex", "NA");//FINANCE APPROVED DA
        $this->setToRelatedSingleCell("AB$rowIndex", 0);//FINANCE APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AC$rowIndex", 0);//FINANCE HOTEL BILLS
        $this->setToRelatedSingleCell("AD$rowIndex", 0);//FINANCE OTHER BILLS
        $this->setToRelatedSingleCell("AE$rowIndex", 0);//FINANCE FINAL AMOUNT
        $this->setToRelatedSingleCell("AF$rowIndex", "NA");// APPROVED FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AG$rowIndex", "NA"); // Finance deducted reason,
        $this->setToRelatedSingleCell("AH$rowIndex", 0); // Finance deducted amount
   }
    function setFinanceRejectedCell_defaultValues($rowIndex)
    {
        $this->setToRelatedSingleCell("AP$rowIndex", "NA");//TL or finance REJECTED DATE
        $this->setToRelatedSingleCell("AQ$rowIndex", "NA");//TL or finance REJECTED REASON
        $this->setToRelatedSingleCell("AR$rowIndex", "NA");//REJECTED BY TL NAME AND ID

    }
    function setToPaidCell_defaultValues($rowIndex){
        $this->setToRelatedSingleCell("AI$rowIndex", "NA");//PAID  DATE
        $this->setToRelatedSingleCell("AJ$rowIndex", "NA");//PAID APPROVED DA
        $this->setToRelatedSingleCell("AK$rowIndex", 0);//PAID APPROVED DA-AMOUNT
        $this->setToRelatedSingleCell("AL$rowIndex", 0);//PAID HOTEL BILLS
        $this->setToRelatedSingleCell("AM$rowIndex", 0);//PAID OTHER BILLS
        $this->setToRelatedSingleCell("AN$rowIndex", 0);//PAID FINAL AMOUNT
        $this->setToRelatedSingleCell("AO$rowIndex", "NA");// PAID FINANCE NAME AND ID
        $this->setToRelatedSingleCell("AS$rowIndex", 0);// TOTAL DEDUCTED AMOUNT
    }
    

    function getThisDateDuplicateCount($EmployeAndDate)
    {
        return $this->noOfDuplicateDateCountArray[$EmployeAndDate]; // returns count (no.of duplicate this date contains)
    }


function saveOrDownloadExcel($requestFrom,$filename){
if($requestFrom==REQUESTED_FROM_WEB_PORTAL){
$this->downloadExcel($filename);

}elseif($requestFrom==REQUESTED_FROM_MOBILE){
$this->saveExcel($filename);

}


}

    function downloadExcel($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();
    }

    function saveExcel($filename){

        $file= PHPExcel_IoFactory::createWriter($this->activeSheet,'Excel2007');
        $file->save($filename.'.xlsx');


    }
}

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