<?php

abstract class ImportExcelFileToEntity implements Importable, Validable {
    /**
     *
     * @var Uploader
     */
    private $_upload = null;
    private $_file = null;

    protected $_entity = null;
    protected $_feedbackMsg = '';
    protected $_validators = array();
    protected $_columnsException = array();

    private $_isUploaded = false;

    private $_objPHPExcel = null;

    const DEFAULT_START_ROW = 2;
    const MAX_COLUMN_INDEX = 4;
    const DEFAULT_DATE_FORMAT_TO_DB = 'Y-m-d';
     const DEFAULT_DATE_FORMAT = 'Y/m/d';
    const DEFAULT_DATETIME_FORMAT = 'Y/m/d H:i:s';

    public function __construct($upload, $file) {
        $this->_upload = $upload;
        $this->_file = $file;
    }

    public function setEntity(EntityRepository $entity) {
        $this->_entity = $entity;
    }

    public function getUploader() {
        return $this->_upload;
    }

    public function getStartRow() {
        return self::DEFAULT_START_ROW;
    }

    public function getMaxColumnIndex()
    {
        return self::MAX_COLUMN_INDEX;
    }

    public function getExcelObj() {
        if (is_null($this->_objPHPExcel)) {
            $this->upload();
            $this->_objPHPExcel = PHPExcel_IOFactory::load($this->getUploader()->getFileLocation());
        }
        return $this->_objPHPExcel;
    }

    public function isUploaded()
    {
        return $this->_isUploaded;
    }
    
    

    public function save() {
        try {

            if (!($this->_entity instanceof EntityRepository)) {
                throw new Exception('The entity is not declared, please set before save the file.');
            }

            $objPHPExcel = $this->getExcelObj();
            $objPHPExcel->setActiveSheetIndex(0);
            $sheet = $objPHPExcel->getActiveSheet();
            $maxRow = $sheet->getHighestRow();

            for (
                    $i = $this->getStartRow(),
                    $data = array(),
                    $options = $this->_entity->getOptions(),
                    $keys = array_keys($options);
                 $i <= $maxRow;
                 $i++) {
                $entity = clone $this->_entity;
                for ($j = 0, $idx = 0; $j < $this->getMaxColumnIndex(); $j++) {
                    if ($this->isColumnException($j)) {
                        continue;
                    }
                    $cell = $sheet->getCellByColumnAndRow($j, $i);
                    $entity->setOption($keys[$idx], $cell->getValue());
                    $idx++;
                }
                $data[] = $entity->getOptions();
                unset($entity);
            }

            if (!$this->isValid()) {
                throw new Exception($this->getFeedbackMsg());
            }

            $this->_entity->insert($data);

            unset($objPHPExcel);
            unset($sheet);
            unset($data);
            $this->deleteTempFile();

            $this->_feedbackMsg = "The Excel File has been imported successfully.";
            return true;
        } catch (Exception $exc) {
            $this->deleteTempFile();
            $this->_feedbackMsg = $exc->getMessage();
            return false;
        }
    }

    protected function isColumnException($columnIndex) {
        foreach ($this->_columnsException as $value) {
            if ((int)$value == (int)$columnIndex) {
                return true;
            }
        }
        return false;
    }

    public function deleteTempFile() {
        $this->getUploader()->deleteTempFile();
    }

    public function upload() {

        if (!$this->isUploaded()) {
            if ($this->_upload->uploadFile($this->_file)) {
                $this->_isUploaded = true;
                return true;
            }

            $this->_feedbackMsg = $this->_upload->getMessageError();
            return false;
        }
        return true;
    }

    public function getFeedbackMsg() {
        return $this->_feedbackMsg;
    }

    public function addValidator(Validator $validator) {
        $this->_validators[] = $validator;
    }

    public function isValid() {
        foreach ($this->_validators as $validator) {
            if (!$validator->isValid($this)) {
                $this->_feedbackMsg = $validator->getFeedbackMsg();
                return false;
            }
        }

        return true;
    }

    /**
     * @param $cellCoordinate Ex. E1, A3, etc.
     * @return DateTime
     * @throws Exception
     * @throws PHPExcel_Exception
     */
     public function getDateFromActiveSheet($cellCoordinate)
    {
        $objPHPExcel = $this->getExcelObj();
        $activeSheet = $objPHPExcel->getActiveSheet();


        $date = PHPExcel_Style_NumberFormat::toFormattedString(
            $activeSheet->getCell($cellCoordinate)->getValue(),
            'mm/dd/yyyy'
        );

        if(!$this->isValidDate($date)){
            throw new Exception("Invalid date format '{$date}' you should use Date format in excel or if is a format text date use mm/dd/yyyy  Ex. 01/28/2016");
        }
        
        /* El separador que se envia para la fecha debe ser "/" */
        $parsedDate = EatonDateTime::createFromFormat('m/d/Y H:i:s',$date);

        if (!$parsedDate) {
            // try to parse with the default format Y/m/d H:i:s
            $parsedDate = EatonDateTime::createFromFormat(self::DEFAULT_DATE_FORMAT, $date);

            if (!$parsedDate) {
                $parsedDate = EatonDateTime::createFromFormat(self::DEFAULT_DATETIME_FORMAT, $date);
            }
        }

        if (!$parsedDate) {
            throw new Exception('Invalid date format you should use Date format in excel or if is a format text date use yyyy/mm/dd H:mi:ss. Ex. 2015/12/09 19:15:23 or 2015/12/09');
        }

        return $parsedDate;
    }
    
    /**
    * Checks date if matches given format and validity of the date.
    * Examples:
    * <code>
    * is_date('22.22.2222', 'mm.dd.yyyy'); // returns false
    * is_date('11/30/2008', 'mm/dd/yyyy'); // returns true
    * is_date('30-01-2008', 'dd-mm-yyyy'); // returns true
    * is_date('2008 01 30', 'yyyy mm dd'); // returns true
    * </code>
    * @param string $value the variable being evaluated.
    * @param string $format Format of the date. Any combination of <i>mm<i>, <i>dd<i>, <i>yyyy<i>
    * with single character separator between.
    */ 
    private function isValidDate($value, $format = 'mm/dd/yyyy'){
        if(strlen($value) >= 6 && strlen($format) == 10){

            // find separator. Remove all other characters from $format
            $separator_only = str_replace(array('m','d','y'),'', $format);
            $separator = $separator_only[0]; // separator is first character
           
            if($separator && strlen($separator_only) == 2){ 
                // make regex 
                $regexp = str_replace('mm', '(0?[1-9]|1[0-2])', $format);
                $regexp = str_replace('dd', '(0?[1-9]|[1-2][0-9]|3[0-1])', $regexp);
                $regexp = str_replace('yyyy', '(19|20)?[0-9][0-9]', $regexp);
                $regexp = str_replace($separator, "\\" . $separator, $regexp);
              
                if($regexp != $value && preg_match('/'.$regexp.'\z/', $value)){
                    // check date
                    $arr=explode($separator,$value);
                    $month=$arr[0];
                    $day=$arr[1];
                    $year=$arr[2];
                 
                    if(@checkdate($month, $day, $year))
                        return true;
                }
            }
        }
        return false;
    } 
}