<?php
class ExcelCellValues
{

    //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");
    }


    function bindValuesToExcel($cellValueArray, $activeSheet)
    {
        $this->activeSheet=$activeSheet;
        $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 == 1) {
                $this->mergedCells($rowIndex, $cellValueArray, $loopCount, false, $duplicateDateCount);
            } elseif ($tempDuplicateDateCount == $duplicateDateCount) {                                       //eg: duplicateDateCount=3  then  (duplicateDateCount--) upto duplicateDateCount= tempDuplicateDateCount if(equal){ get the duplicateDateCount}
                $this->mergedCells($rowIndex, $cellValueArray, $loopCount, true, $duplicateDateCount);
            }
            $duplicateDateCount--;


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

            $this->unMergedCells($rowIndex, $cellValueArray, $loopCount); // unmerged cell in excel
        }
    }


    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 "NOT AT CLAIMED";
                break;
            case CLAIM_PENDING: //CLAIM-PENDING
                $this->changeTextColor("AC$rowIndex", "ffc107");
                return "CLAIM 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 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

    }

    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
    }
    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
    }


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