<?php
/**
 * File Operations for Spreadsheet Files
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 * @subpackage base
 */

WBClass::load('WBFile');

/**
 * File Operations for Spreadsheet Files
 *
 *
// PHPOffice requires autoloader
WBParam::set('wb/class/autoload', true);
$local  =   WBParam::get('wb/class/local');
$local['PhpOffice\PhpSpreadsheet']  =   'PHPOffice::phpSpreadsheet/src/PhpSpreadsheet';
WBParam::set('wb/class/local', $local);
 *
 *
 * @version 1.2.2
 * @package WB
 * @subpackage base
 */
class WBFile_Spreadsheet extends WBFile
{
    /**
     * Column Map
     * @var array
     */
    private $map    =   array();

    /**
     * File Operation Mode
     * @var string
     */
    private $fileMode   =   'r';

    /**
     * PhpSpreadsheet
     *
     * @var PhpOffice\PhpSpreadsheet\Spreadsheet
     */
    private $sheet;

    /**
     * Current Sheet
     * @var int
     */
    private $sheetIndex =   0;

    /**
     * Current Row
     *
     * Row count per sheet
     * @var array
     */
    private $row    =   array(0 => 0);

    /**
     * Empty Row Counter
     * @var array
     */
    private $emptyRow  =   array();

    /**
     * Last Row
     * @var array
     */
    private $lastRow  =   array();

    /**
     * Current Row Iterator
     * @var PhpOffice\PhpSpreadsheet\Worksheet\RowIterator
     */
    private $rowI;

    /**
     * SpreadSheet File Type
     * @var string
     */
    private $spreadsheetType    =   'ods';

    /**
     * Named Columns in Excel-Style
     * @var array
     */
    private $cols   =   array();

    const INDEX_LAST    =   65535;
    const MAX_EMPTY_ROWS    =   10;

    /**
     * Set File Type of Spreadsheet
     *
     * @param string
     */
    public function setSpreadsheetType($type = 'ods')
    {
        $this->spreadsheetType  =   strtolower($type);
    }

    /**
     * Get Spreadsheet Type
     *
     * Usually used as file extension
     *
     * @return string
     */
    public function getSpreadsheetType()
    {
        return $this->spreadsheetType;
    }

    /**
     * Get Mime Type Of Current SpreadSheet
     *
     * Actually, path will be ignored
     *
     * @param string path
     * @return string
     */
    public function getMimeType($path = null)
    {
        switch ($this->spreadsheetType) {
            case 'ods':
                return 'application/vnd.oasis.opendocument.spreadsheet';
                break;

            case 'xls':
                return 'application/vnd.ms-excel';
                break;

            case 'xlsx':
                return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                break;

            default:
                break;
        }
        return '';
    }

    /**
     * Open CSV file
     *
     * Wrapper to fopen()
     *
     * @see fopen()
     * @param string $mode
     */
    public function open($mode = 'r')
    {
        // already open?
        if (!empty($this->sheet)) {
            return $this;
        }

        $this->fileMode =   $mode;
        switch ($this->fileMode) {
            case 'w':
                $this->sheet    =   WBClass::create('PhpOffice\\PhpSpreadsheet\\Spreadsheet');
                break;

            case 'r':
                $clazz          =   sprintf('PhpOffice\\PhpSpreadsheet\\Reader\\%s', ucfirst($this->spreadsheetType));
                /** @var PHPOffice\PhpSpreadsheet\Reader */
                $reader         =   new $clazz();
                $reader->setReadDataOnly(true);
                // $reader->setIgnoreRowsWithNoCells(true);
                $this->sheet    =   $reader->load($this->realpath());
                break;

            default:
                return $this;
                break;
        }

        $this->sheetIndex   =   0;
        $this->row          =   array(0 => 0);
        $this->emptyRow     =   array(0 => 0);
        $this->lastRow      =   array(0 => 0);
        $this->setActiveSheetIndex($this->sheetIndex);
        return $this;
    }

    /**
     * Close
     *
     * Close open file handle
     */
    public function close()
    {
        // already closed
        if (empty($this->sheet)) {
            return $this;
        }

        switch ($this->fileMode) {
            case 'w':
                $clazz  =   sprintf('\\PhpOffice\\PhpSpreadsheet\\Writer\\%s', ucfirst($this->spreadsheetType));
                /** @var PHPOffice\PhpSpreadsheet\Writer */
                $writer =   new $clazz($this->sheet);
                $writer->save($this->realpath());
                break;

            default:
                break;
        }
        $this->sheet    =   null;

        return $this;
    }

    /**
     * Get Properties
     * @return PhpOffice\PhpSpreadsheet\Document\Properties
     */
    public function getProperties()
    {
        return $this->sheet->getProperties();
    }

    /**
     * Set Active Sheet
     *
     * @param int
     * @return \PhpOffice\PhpSpreadsheet\Worksheet
     */
    public function setActiveSheetIndex($index)
    {
        $this->sheetIndex       =   $index;
        $this->row[$index]      =   0;
        $this->emptyRow[$index] =   0;
        $this->lastRow[$index]  =   0;

        $worksheet  =   $this->sheet->setActiveSheetIndex($index);
        $this->rowI =   $worksheet->getRowIterator();
        $this->rowI->seek(1);

        $this->lastRow[$index]  =   $worksheet->getHighestRow() - 1;
        return $worksheet;
    }

    public function getActiveSheet()
    {
        return $this->sheet->getActiveSheet();
    }

    public function getColumnNames()
    {
        return $this->cols;
    }

    /**
     * Add an new Spreadsheet
     *
     * Add Sheet at the end
     */
    public function addSheet($title = '')
    {
        if ('w' != $this->fileMode) {
            $ex =   array(
                            'class' =>  __CLASS__,
                            'code'  =>  2,
                            'msg'   =>  'Cannot add sheet in read mode!'
                        );
            throw WBClass::create('WBException_Call', $ex);
        }

        $cnt    =   $this->sheet->getSheetCount();
        $this->row[$cnt]        =   0;
        $this->emptyRow[$cnt]   =   0;
        $this->lastRow[$cnt]    =   0;
        if (empty($title)) {
            $title  =   'sheet ' . $cnt;
        }
        $ws =   new \PhpOffice\PhpSpreadsheet\Worksheet($this->sheet, $title);
        $this->sheet->addSheet($ws);
    }

    /**
     * Read row
     *
     * Get row from spreadsheet
     *
     * @todo not implemented yet
     * @return array
     */
    public function read()
    {
        $data       =   array();
        if (self::INDEX_LAST < $this->row[$this->sheetIndex]) {
            return $data;
        }

        // last row exceeded
        if ($this->lastRow[$this->sheetIndex] < $this->row[$this->sheetIndex]) {
            return $data;
        }
        $this->rowI->seek($this->row[$this->sheetIndex] + 1);

        $row    =   array();
        if ($this->rowI->valid()) {
            $row    =   $this->rowI->current();
        }
        ++$this->row[$this->sheetIndex];

        if (empty($row)) {
            return $data;
        }

        $cellI  =   $row->getCellIterator();

        $filled =   false;
        if(empty($this->map)) {
            foreach ($cellI as $cell) {
                $val        =   trim($cell->getValue());
                if (!empty($val)) {
                    $filled =   true;
                }
                $data[] =   $val;
            }
        }
        else {
            foreach ($this->map as $k => $c) {
                $cellI->seek($this->cols[$c]);
                $cell       =   $cellI->current();
                $val        =   trim($cell->getValue());
                $data[$k]   =   '';
                if (!empty($val)) {
                    $data[$k]   =   $val;
                    $filled =   true;
                }
            }
        }

        if ($filled) {
            $this->emptyRow[$this->sheetIndex] =   0;
        }
        else {
            ++$this->emptyRow[$this->sheetIndex];
        }
        return $data;
    }

    /**
     * Fseek wrapper
     *
     * Jump to position in file, actually row
     *
     * @todo parameter whence not implemented
     * @see fseek()
     * @param int $offset
     * @param int $whence
     */
    public function seek($offset, $whence = SEEK_SET)
    {
        $this->row[$this->sheetIndex]   =   $offset;
    }

    /**
     * Ftell wrapper
     *
     * Get current offset in file
     *
     * @see ftell()
     * @return int
     */
    public function tell()
    {
        return $this->row[$this->sheetIndex];
    }

    /**
     * Feof wrapper
     *
     * @see feof()
     * @return bool
     */
    public function eof()
    {
        if (self::INDEX_LAST < $this->row[$this->sheetIndex]) {
            return true;
        }
        if ($this->lastRow[$this->sheetIndex] < $this->row[$this->sheetIndex]) {
            return true;
        }
        if (self::MAX_EMPTY_ROWS < $this->emptyRow[$this->sheetIndex]) {
            return true;
        }
        return (!$this->rowI->valid());
    }

    /**
     * Write row
     *
     * Put row to spreadsheet
     *
     *
     * @todo extend column limit
     * @param array $data
     */
    public function write($data)
    {
        if ('w' != $this->fileMode) {
            $ex =   array(
                            'class' =>  __CLASS__,
                            'code'  =>  3,
                            'msg'   =>  'Cannot write data in read mode!'
                        );
            throw WBClass::create('WBException_Call', $ex);
        }
        ++$this->row[$this->sheetIndex];

        if (empty($this->map)) {
            $row        =   array_values($data);
            if (empty($this->cols)) {
                $this->initColumnNames(count($row));
            }
        } else {
            $row    =   array();
            foreach ($this->map as $k => $i) {
                $row[$i]    =   '';
                if (isset($data[$k])) {
                    $row[$i]    =   $data[$k];
                }
            }
        }

        $sheet  =   $this->sheet->setActiveSheetIndex($this->sheetIndex);

        foreach ($row as $i => $v) {
            if (!isset($this->cols[$i])) {
                $ex =   array(
                                'class' =>  __CLASS__,
                                'code'  =>  1,
                                'msg'   =>  'Maximum number of columns exceeded!'
                            );
                throw WBClass::create('WBException_Argument', $ex);
            }
            $sheet->setCellValue($this->cols[$i] . $this->row[$this->sheetIndex], $v);
        }
    }

    /**
     * Set map to support associative arrays
     *
     * Optional map allow to support associative arrays
     *
     * @param array $map
     */
    public function setMap($map = array())
    {
        $this->map  =   $map;
        $this->initColumnNames();
    }

    /**
     * Columns Names In SpreadSheet Style
     *
     * Columns A-Z, AA-AZ, BA-BZ, ...
     * @param int number of culouns to set names
     */
    private function initColumnNames($count = 0)
    {
        if (!empty($this->map)) {
            foreach ($this->map as $k => $i) {
                $count  =   max($count, $i);
            }
        }
        ++$count;

        $alpha      =   range('A', 'Z');
        $this->cols =   $alpha;
        if (count($this->cols) > $count) {
            return;
        }

        foreach ($alpha as $a1) {
            foreach ($alpha as $a2) {
                $this->cols[]   =   $a1 . $a2;
                if (count($this->cols) > $count) {
                    return;
                }
            }
        }
    }
}