<?php

namespace BRSystems\Core\Library\cExcel;

use BRSystems\Core\Library\cJSON\cJSON;
use Exception;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Reader_IReadFilter;

/** Include PHPExcel */
require_once 'PHPExcel_1.7.8/PHPExcel.php';
require_once 'PHPExcel_1.7.8/PHPExcel/IOFactory.php';

/**
 * Excel module
 *
 * @category CMS
 * @author Mareks Keišs <mareks.keiss@brsystems.lv>
 * @since Version 1.0
 *
 *
 * http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.intro.php
 */
class cExcel extends PHPExcel
{
    CONST eXs = 1;
    CONST eXb = 2;
    CONST eYs = 3;
    CONST eYb = 4;

    CONST TY_XLS = 'xls';
    CONST TY_XLSX = 'xlsx';
    CONST TY_HTML = 'html';
    CONST TY_PDF = 'pdf';

    public $mimeTypes = array(
        'doc'  => 'application/msword',
        'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        'htm'  => 'text/html',
        'html' => 'text/html',
        'pdf'  => 'application/pdf',
        'xls'  => 'application/excel',
        'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'zip'  => 'application/zip',
    );

    public function __construct() {

        parent :: __construct();

    }

    public function openDocument($file){
        if($file)
            return PHPExcel_IOFactory::load($file);
        return false;
    }
    public function createReader($file){
        if($file)
            return PHPExcel_IOFactory::createReaderForFile($file);
        return false;
    }

    /**
     * Helper Function to set document properties
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $category - export category title
     * @return array of data
     * @version 1.1
     */
    public function setPropertiesAdv($category = "") {

        // Set document properties
        $this->getProperties()->setCreator("ZUTENIS")
            ->setLastModifiedBy("ZUTENIS")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Reports")
            ->setKeywords("office 2007 openxml php")
            ->setCategory($category?$category:"Export");

    }

    /**
     * Helper Function to throw file download .xls
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $fileName - download name title
     * @return array of data
     * @version 1.1
     */
    public function throwDocument($fileName = "") {

        if($fileName){
            $ext = getFileExtension($fileName);

            // Redirect output to a client’s web browser (Excel5)
            header("Content-Type: {$this->mimeTypes[$ext]}");
            header('Content-Disposition: attachment;filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');

            $oWriter = $this->createWriter($ext);

            // Output content
            //$objWriter->save("C:/{$fileName}");
            $oWriter->save('php://output');
        }
    }

    /**
     * Helper Function to throw file download .xls
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $extension - file extension
     * @param object|bool $object - reader object
     * @return array of data
     * @version 1.1
     */
    public function createWriter($extension, $object = false) {

        if(!strlen($extension))
            $extension = 'xls';
        if(!$object)
            $object = $this;

        switch($extension){
            case $this::TY_XLSX:
                //Now we save the created document in the Excel 2007 format
                $oWriter = PHPExcel_IOFactory::createWriter($object, 'Excel2007');
                break;
            case $this::TY_HTML:
                $oWriter = PHPExcel_IOFactory::createWriter($object, 'HTML');
                break;
            case $this::TY_PDF:
                $oWriter = PHPExcel_IOFactory::createWriter($object, 'PDF');
                break;
            default:
                //Now we save the created document in the Excel 2005 format
                $oWriter = PHPExcel_IOFactory::createWriter($object, 'Excel5');
                break;
        }

        return $oWriter;
    }

    /**
     * Function to get ms coordinate indexes by ms address
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param $Item - ms address
     * @param $param - const to get item
     * @return array of data
     * @version 1.1
     */
    public function msXY($Item, $param) {

        $nValue = 0;
        $items = explode(':', $Item);

        switch($param){
            case self::eXs:
                $this->getLetter($items[0], $nValue);
                break;
            case self::eXb:
                $this->getLetter((count($items)>1?$items[1]:$items[0]), $nValue);
                break;
            case self::eYs:
                $this->getNumber($items[0], $nValue);
                break;
            case self::eYb:
                $this->getNumber((count($items)>1?$items[1]:$items[0]), $nValue);
                break;
        }

        return $nValue;
    }

    /**
     * Function to get ms coordinate index by letters AB from "AB12"
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $item - coordinate part
     * @param bool $nValue - value calculated
     * @return array of data
     * @version 1.1
     */
    private function getLetter($item, &$nValue = false) {
        if(strlen($item)){
            if(is_numeric(substr($item, 1)) || strlen($item) == 1){
                $chr = substr($item, 0, 1);
                for($i = 1;$i <= 26;$i++){
                    if($chr == chr(64+$i)){
                        $nValue = $nValue * 26 + $i;
                        break;
                    }
                }
            }else{
                $chr = substr($item, 0, 1);
                for($i = 1;$i <= 26;$i++){
                    if($chr == chr(64+$i)){
                        $nValue = $nValue * 26 + $i;
                        break;
                    }
                }
                $this->getLetter(substr($item, 1), $nValue);
            }
        }
        return;
    }

    /**
     * Function to get ms y coordinate index by number 12 from "AB12"
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $item - coordinate part
     * @param bool $nValue - value calculated
     * @return array of data
     * @version 1.1
     */
    private function getNumber($item, &$nValue = false) {
        if(strlen($item)){
            if(is_numeric($item)){
                $nValue = intval($item);
            }else{
                $this->getNumber(substr($item,1), $nValue);
            }
        }
        return;
    }

    /**
     * Function to create ms address by coordinates
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param int $x - x coordinate
     * @param int $y - y coordinate
     * @return array of data
     * @version 1.1
     */
    public static function xY($x, $y) {

        $x = $x <= 0 ? 1 : $x;
        $y = $y <= 0 ? 1 : $y;

        $sk = 0;
        $iX = $x;
        $addr = '';

        while(1){
            if($iX>26){
                while($iX > 26){
                    $iX -= 26;
                    $sk++;
                }

                $addr = Chr(64 + $iX) . $addr;
                $iX = $sk;
                $sk = 0;
            }else{
                return chr(64 + $iX) . $addr . $y;
            }
        }

    }

    /**
     * Function to get ms letter from x coordinate
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param int $x - x coordinate
     * @return array of data
     * @version 1.1
     */
    public static function X($x) {

        $x = $x<=0?1:$x;

        $sk = 0;
        $iX = $x;
        $addr = '';

        while(1){
            if($iX>26){
                while($iX > 26){
                    $iX -= 26;
                    $sk++;
                }

                $addr = Chr(64 + $iX) . $addr;
                $iX = $sk;
                $sk = 0;
            }else{
                return chr(64 + $iX) . $addr;
            }
        }

    }

    /**
     * Function to quietly check if sheet name exists
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $sSheetTitle - sheet title
     * @return bool - true/false
     * @version 1.1
     */
    public function chkSheetNameExists($sSheetTitle) {
        $bExists = false;
        try{
            $bExists = $this->sheetNameExists($sSheetTitle);
        }catch(Exception $e){
            $bExists = false;
        }
        return !!$bExists;
    }

    /**
     * Function to quietly check if sheet name exists
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param string $sSheetTitle - sheet title
     * @return bool - true/false
     * @version 1.1
     */
    public function chkSheetNameExistsAdv($sSheetTitle, $oWorkbook) {
        $bExists = false;
        try{
            $bExists = $oWorkbook->setActiveSheetIndexByName($sSheetTitle);
        }catch(Exception $e){
            $bExists = false;
        }
        return !!$bExists;
    }

    /**
     * Function to find last filled row in long excel file
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category cmsMailSpamHandler Controller
     * @param object $chunkFilter - Chunk filter for excel reader
     * @param object $oReader - excel reader
     * @param object $file - file to check
     * @param int $emptyDefault - allowed empty row count
     * @param array $progress - progress object
     * @return array of data
     * @version 1.1
     */
    public static function getLongFileLastRow(&$chunkFilter, &$oReader, $file, $emptyDefault = 40, $progress = array()) {

        $cRow = 100;
        $chunkSize = 100;
        $emptyRows = $emptyDefault;
        while($emptyRows > 0){


            $jump = false;
            $chunkFilter->setRows($cRow, $emptyDefault);
            $oWorkbook = $oReader->load($file);

            $oSheet = $oWorkbook->getActiveSheet();
            $tCols = $oSheet->getHighestDataColumn();

            $array = $oSheet->rangeToArray("A{$cRow}:{$tCols}" . ($cRow+$emptyDefault));


            //---------------------------------------------------------------------------
            // ANALYSE EACH CHUNK SECTION
            //---------------------------------------------------------------------------
            if(!!$array && is_array($array)){
                foreach($array as $row){

                    foreach($row as $cell){
                        if(trim($cell)){
                            $jump = true;
                            break;
                        }
                    }

                    if($jump){
                        $emptyRows = $emptyDefault;
                        break;
                    }else{
                        --$emptyRows;
                    }

                }
            }

            // Progress message & percentage
            if(!!$progress && is_array($progress)){
                if($progress['apc']->check("{$progress['uKey']}_c", true)){

                    $oWorkbook->disconnectWorksheets();
                    unset($array, $oSheet, $oWorkbook, $oReader, $progress);

                    cJSON::sendError(array('msg' => '<b>File import failed!</b><br/>User terminated table data import!'));
                }
                $progress['apc']->store($progress['uKey'], array('t' => str_replace("%rows%", $cRow, $progress['text'])));
            }
            if($cRow > 3000)
                $chunkSize = 1200;
            else if($cRow > 2000)
                $chunkSize = 800;
            else if($cRow > 1000)
                $chunkSize = 500;
            elseif($cRow > 500)
                $chunkSize = 300;

            $cRow += $chunkSize;

            $oWorkbook->disconnectWorksheets();
            unset($array, $oSheet, $oWorkbook);


        }

        return $cRow;
    }

}

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{

    private $_startRow = 0;
    private $_endRow = 0;
    /**  Set the list of rows that we want to read  */
    public function setRows($startRow, $chunkSize) {
        $this->_startRow    = $startRow;
        $this->_endRow      = $startRow + $chunkSize;
    }
    public function readCell($column, $row, $worksheetName = '') {
        //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow
        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
            return true;
        }
        return false;
    }
}