<?php

Yii::import('zii.widgets.grid.CGridView');

/**
 * @author Nikola Kostadinov
 * @license GPL
 * @version 0.2
 */
class EExcelView extends CGridView
    {

    // PHP Excel Path
    public static $phpExcelPathAlias = 'ext.phpexcel.Classes.PHPExcel';
    //the PHPExcel object
    public static $objPHPExcel = null;
    public static $activeSheet = null;
    //Document properties
    public $creator = 'Nikola Kostadinov';
    public $title = null;
    public $subject = 'Subject';
    public $description = '';
    public $category = '';
    //config
    public $autoWidth = true;
    public $exportType = 'Excel5';
    public $disablePaging = true;
    public $filename = null; //export FileName
    public $stream = true; //stream to browser
    //Custoize
    public $local_file = false; //save file locally
    //data
    // For performance reason, it's good to have it static and populate it once in all the execution
    public static $data = null;
    //mime types used for streaming
    public $mimeTypes = array(
        'Excel5' => array(
            'Content-type' => 'application/vnd.ms-excel',
            'extension' => 'xls',
        ),
        'Excel2007' => array(
            'Content-type' => 'application/vnd.ms-excel',
            'extension' => 'xlsx',
        ),
        'PDF' => array(
            'Content-type' => 'application/pdf',
            'extension' => 'pdf',
        ),
        'HTML' => array(
            'Content-type' => 'text/html',
            'extension' => 'html',
        ),
        'CSV' => array(
            'Content-type' => 'application/csv',
            'extension' => 'csv',
        )
    );

    public function init()
    {
        if (!isset($this->title))
            $this->title = Yii::app()->getController()->getPageTitle();

        if (!$this->local_file)
            parent::init();
        else
            $this->initColumns();

        //Autoload fix
        spl_autoload_unregister(array('YiiBase', 'autoload'));
        Yii::import(self::$phpExcelPathAlias, true);
        self::$objPHPExcel = new PHPExcel();
        self::$activeSheet = self::$objPHPExcel->getActiveSheet();
        spl_autoload_register(array('YiiBase', 'autoload'));

        // Creating a workbook
        $properties = self::$objPHPExcel->getProperties();
        $properties
                ->setTitle($this->title)
                ->setCreator($this->creator)
                ->setSubject($this->subject)
                ->setDescription($this->description)
                ->setCategory($this->category);

        //$this->initColumns();
    }

    public function renderHeader()
    {
        $i = 0;
        foreach ($this->columns as $n => $column)
        {
            ++$i;

            if ($column->name !== null && $column->header === null)
            {
                if ($column->grid->dataProvider instanceof CActiveDataProvider)
                    $head = $column->grid->dataProvider->model->getAttributeLabel($column->name);
                else
                    $head = $column->name;
            } else
            {
                $head = trim($column->header) !== '' ? $column->header : $column->grid->blankDisplay;
            }
            self::$activeSheet->setCellValue($this->columnName($i) . "1", $head);
        }
    }

    public function renderFooter()//footer created by francis
    {

        $i = 0;
        $data = $this->dataProvider->getData();
        $row = count($data);
        foreach ($this->columns as $n => $column)
        {
            $i = $i + 1;

            $footer = trim(is_null($column->footer)?'':$column->footer) !== '' ? $column->footer : "";

            self::$activeSheet->setCellValue($this->columnName($i) . ($row + 2), $footer);
        }
    }

    // Main consuming function, apply every optimization you could think of
    public function renderBody()
    {
        if ($this->disablePaging) //if needed disable paging to export all data
            $this->enablePagination = false;

        self::$data = $this->dataProvider->getData();
        
        $n = count(self::$data);

        if ($n > 0)
            for ($row = 0; $row < $n; ++$row)
                $this->renderRow($row);
    }

    public function renderRow( $row )
    {
        $i = 0;
        foreach ($this->columns as $n => $column):
            if ($column->value !== null)
                $value = $this->evaluateExpression($column->value, array('row' => $row, 'data' => self::$data));

            else if ($column->name !== null)
            {
                //edited by francis to support relational dB tables
                $condition = explode(";", $column->name);
                $value = $column->name;

                // I don't understand this piece of code (the conditions and all that stuff), when these conditions will meet?
                // Francis, if you see this code ever again, please comment
                $countCondition = count($condition);

                if ($countCondition == 6 || $countCondition == 5):
                    switch ($countCondition):
                        case 6:
                            $cond1 = $this->dataProcess($condition[0], $row);
                            if ($condition[3] == 'true')
                                $cond2 = $condition[2];
                            else
                                $cond2=$this->dataProcess($condition[2], $row);

                            $cond3 = $this->dataProcess($condition[4], $row);
                            $cond4 = $this->dataProcess($condition[5], $row);
                            break;
                        case 5:
                            $cond1 = $this->dataProcess($condition[0], $row);
                            $cond2 = $this->dataProcess($condition[2], $row);
                            $cond3 = $this->dataProcess($condition[3], $row);
                            $cond4 = $this->dataProcess($condition[4], $row);
                            break;
                        default:
                            break;
                    endswitch;

                    switch ($condition[1]):
                        case '==':
                            $value = ($cond1 == $cond2) ? $cond3 : $cond4;
                            break;
                        case '!=':
                            $value = ($cond1 != $cond2) ? $cond3 : $cond4;
                            break;
                        case '<=':
                            $value = ($cond1 <= $cond2) ? $cond3 : $cond4;
                        case '>=':
                            $value = ($cond1 >= $cond2) ? $cond3 : $cond4;
                            break;
                        case '<':
                            $value = ($cond1 < $cond2) ? $cond3 : $cond4;
                        case '>':
                            $value = ($cond1 > $cond2) ? $cond3 : $cond4;
                        default:
                            break;
                    endswitch;

                elseif ($countCondition != 1):
                    $value = '';
                else:
                    $value = $this->dataProcess($column->name, $row);
                endif;
            }

            //date edited francis
            $dateF = explode("-", is_null($value)?false:$value);
            $c1 = count($dateF);

//				if($c1==3 && $dateF[0]<9000 && $dateF[1]<13 && $dateF[2]<32)//{}
//					$value=$dateF[2].'/'.$dateF[1].'/'.$dateF[0];
            //end of date

            $value = $value === null ? "" : $column->grid->getFormatter()->format($value, $column->type);

            // Write to the cell (and advance to the next)
            // Flex Edit:  We will first format the cell as text (to avoid applying the default formatting for the cells, e.g. the badge number may contain zero at the beginning)
            self::$activeSheet
                    ->getStyle($this->columnName($i + 1) . ($row + 2))
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

            //  Flex Edit: Use setCellValueExplicit instead of setCellValue and pass the value data type as String
            self::$activeSheet->setCellValueExplicit($this->columnName(++$i) . ($row + 2), $value, PHPExcel_Cell_DataType::TYPE_STRING);
        endforeach;

        // As we are done with this row we DONT need this specific record
        unset(self::$data[$row]);
    }

    public function dataProcess( $name, $row )
    {
        // Separate name (eg person.code into array('person', 'code'))
        $separated_name = explode(".", $name);

        // Count
        $n = count($separated_name);

        // Create a copy of  the data row. Now we can "dive" trough the array until we reach the desired value
        // (because is nested)
        $aux = self::$data[$row]; //if n is greater than zero, we will loop, if not, $aux actually holds the desired value

        for ($i = 0; $i < $n; ++$i)
            $aux = $aux[$separated_name[$i]]; // We keep a deeper reference each time

            return $aux;
    }

    public function run()
    {
        $this->renderHeader();
        $this->renderBody();
        $this->renderFooter();

        //set auto width
        if ($this->autoWidth)
            foreach ($this->columns as $n => $column)
                self::$objPHPExcel->getActiveSheet()->getColumnDimension($this->columnName($n + 1))->setAutoSize(true);

        //create writer for saving
        $objWriter = PHPExcel_IOFactory::createWriter(self::$objPHPExcel, $this->exportType);
        if (!$this->stream)
            $objWriter->save($this->filename);
        else //output to browser
        {
            if (!$this->filename)
                $this->filename = $this->title;
            if (!$this->local_file)//Customize 
            {
                ob_end_clean();
                header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                header('Pragma: public');
                header('Content-type: ' . $this->mimeTypes[$this->exportType]['Content-type']);
                header('Content-Disposition: attachment; filename="' . $this->filename . '.' . $this->mimeTypes[$this->exportType]['extension'] . '"');
                header('Cache-Control: max-age=0');
                $objWriter->save('php://output');
                Yii::app()->end();
            }
            else
            {
                $objWriter->save(dirname(__FILE__) . '/../../' . 'upload/reports/' . $this->filename . '.xls');
            }
        }
    }

    /**
     * Returns the coresponding excel column.(Abdul Rehman from yii forum)
     *
     * @param int $index
     * @return string
     */
    public function columnName( $index )
    {
        --$index;
        if ($index >= 0 && $index < 26)
            return chr(ord('A') + $index);
        else if ($index > 25)
            return ($this->columnName($index / 26)) . ($this->columnName($index % 26 + 1));
        else
            throw new Exception("Invalid Column # " . ($index + 1));
    }

    }