<?php


class exelphp
{

    protected $_object, $_reader, $_sheets, $_supplier, $_fields, $file, $maxRows, $name;
    private $_errors = array();
    const CHUNK_SIZE = 5000;
    /**
     * Переменные статистики
     */

    /**
     * @var fvMemCache
     */
    protected $messenger;
    protected $rate;
    protected $result;

    public    $total,    /* Всего рядов в таблице */
        $good = 0,     /*уже есть, обновляем*/
        $bad = 0,      /*еще нет, создали, сохранили*/
        $ugly = 0;     /*ошибка сохранения */

    /**
     * @param $file - Эксель-файл
     * @param $supplierId - ИД поставщика
     * @param $maxRows
     * @param $id
     */

    public function convert ($file, $data=array ()) {

//Открываем файл-шаблон
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);


//Заполняем таблицу
        for($i=1, $c=count($data); $i<$c; ++$i)
        {
            if($i == 1){
                $objPHPExcel->getActiveSheet()
                    ->SetCellValue('A'.$i, 'id')
                    ->SetCellValue('B'.$i, 'item_id')
                    ->SetCellValue('C'.$i, 'old_price')
                    ->SetCellValue('D'.$i, 'name')
                    ->SetCellValue('E'.$i, 'description')
                    ->SetCellValue('F'.$i, 'real_price')
                    ->SetCellValue('G'.$i, 'day')
                    ->SetCellValue('H'.$i, 'lower_price')
                    ->SetCellValue('J'.$i, 'price-er');
            } else {
                $objPHPExcel->getActiveSheet()
                    ->SetCellValue('A'.$i, $data[$i]['id'])
                    ->SetCellValue('B'.$i, $data[$i]['item_id'])
                    ->SetCellValue('C'.$i, $data[$i]['old_price'])
                    ->SetCellValue('D'.$i, $data[$i]['name'])
                    ->SetCellValue('E'.$i, $data[$i]['description'])
                    ->SetCellValue('F'.$i, $data[$i]['real_price'])
                    ->SetCellValue('G'.$i, $data[$i]['day'])
                    ->SetCellValue('H'.$i, $data[$i]['lower_price']);
                $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
                $cena=str_replace(",",'.',$data[$i]['old_price']);
                $data[$i]['old_price']=preg_replace("/[^x\d|*\.]/","",$cena);
                $cena=str_replace(",",'.',$data[$i]['real_price']);
                $data[$i]['real_price']=preg_replace("/[^x\d|*\.]/","",$cena);
                if($data[$i]['old_price'] && $data[$i]['real_price']){


                    print('price = '.$data[$i]['old_price']);
                    print('price = '.$data[$i]['real_price']);
                    $num = ($data[$i]['old_price']*25)/$data[$i]['real_price'] ;
                    if( $num >= 0.95 ||$num <= 0.4 ){
                        $objPHPExcel->getActiveSheet()->SetCellValue('J'.$i, $num);
                    }
                }


            }


        }


//Отдаем на скачивание

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save(dirname($file).'/file.xlsx');
    }


    function addFile($file)
    {
        $inputFileType = PHPExcel_IOFactory::identify($file);
        $this->_reader = PHPExcel_IOFactory::createReader($inputFileType);
        if (method_exists($this->_reader, "setReadDataOnly"))
            $this->_reader->setReadDataOnly(true);
        $this->file = $file;
        $getWay = explode('/', $this->file);
        $this->name = end( $getWay );

    }


    /**
     *
     * @return array
     */

    function getRows()
    {
        $this->_object = $this->_reader->load($this->file);

        foreach ($this->_object->getSheetNames() as $index => $sheetName)
            $this->_sheets[$index] = $sheetName;

        foreach ($this->_sheets as $idx => $sheet) {
            /**
             * @var PHPExcel_Worksheet $activeSheet
             */
            $objWorksheet = $this->_object->getSheet($idx);

            $highestRow = $objWorksheet->getHighestRow();
            $highestColumn = $objWorksheet->getHighestColumn();

            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            for ($row = 2; $row <= $highestRow; ++$row) {
                for ($col = 0; $col <= $highestColumnIndex; ++$col) {

                    $val = $objWorksheet->getCellByColumnAndRow($col, $row);
                    if(substr($val,0,1) === '=' ) {
                        $this->result[$row][$col] = ceil($val->getCalculatedValue());
                    } else {
                        if($col == 1){
                            $this->result[$row][$col] = preg_replace('/\s+/', '', $val->getValue());
                        } else {
                            $this->result[$row][$col] = $val->getValue();
                        }

                    }
                }
            }
        }

        unset($this->_object);


        return $this->result;


    }

    function getErrors()
    {
        return $this->_errors;
    }
}