<?php defined('BASEPATH') or exit('No direct script access allowed');

class Excel
{
    public function import($file)
    {
        if (empty($file) || !is_file($file)) {
            return array('message' => 'Import file was not found.');
        }

        $contents = file_get_contents($file);
        if ($contents === false || $contents === '') {
            return array('message' => 'Import file is empty or unreadable.');
        }

        if (strncmp($contents, "PK\x03\x04", 4) === 0) {
            return $this->import_xlsx($contents);
        }

        return $this->import_csv($file);
    }

    protected function import_csv($file)
    {
        $handle = fopen($file, 'r');
        if (!$handle) {
            return array('message' => 'Unable to open import file.');
        }

        $rows = array();
        $line = 0;
        while (($row = fgetcsv($handle, 0, ',', '"', '')) !== false) {
            $line++;
            if ($line === 1) {
                continue;
            }
            if ($this->is_empty_row($row)) {
                continue;
            }
            $rows[] = $row;
        }
        fclose($handle);

        return $rows;
    }

    protected function import_xlsx($contents)
    {
        $entries = $this->read_zip_entries($contents);
        if (isset($entries['message'])) {
            return $entries;
        }

        if (empty($entries['xl/worksheets/sheet1.xml'])) {
            return array('message' => 'The uploaded spreadsheet does not contain a first worksheet.');
        }

        $shared_strings = $this->read_shared_strings(!empty($entries['xl/sharedStrings.xml']) ? $entries['xl/sharedStrings.xml'] : null);
        $date_styles = $this->read_date_styles(!empty($entries['xl/styles.xml']) ? $entries['xl/styles.xml'] : null);
        $sheet = simplexml_load_string($entries['xl/worksheets/sheet1.xml']);
        if (!$sheet) {
            return array('message' => 'Unable to read the first worksheet.');
        }

        $rows = array();
        $row_index = 0;
        foreach ($sheet->sheetData->row as $row) {
            $row_index++;
            if ($row_index === 1) {
                continue;
            }

            $values = array();
            foreach ($row->c as $cell) {
                $attributes = $cell->attributes();
                $reference = isset($attributes['r']) ? (string)$attributes['r'] : '';
                $column_index = $reference !== '' ? $this->column_index($reference) : count($values);
                $values[$column_index] = $this->cell_value($cell, $shared_strings, $date_styles);
            }

            if (empty($values)) {
                continue;
            }
            ksort($values);
            $max = max(array_keys($values));
            $normalized = array();
            for ($i = 0; $i <= $max; $i++) {
                $normalized[] = isset($values[$i]) ? $values[$i] : '';
            }
            if (!$this->is_empty_row($normalized)) {
                $rows[] = $normalized;
            }
        }

        return $rows;
    }

    protected function read_zip_entries($contents)
    {
        $eocd = strrpos($contents, "PK\x05\x06");
        if ($eocd === false) {
            return array('message' => 'Invalid XLSX file.');
        }

        $directory_offset = $this->u32($contents, $eocd + 16);
        $entries = array();
        $offset = $directory_offset;
        $length = strlen($contents);

        while ($offset + 46 <= $length && substr($contents, $offset, 4) === "PK\x01\x02") {
            $method = $this->u16($contents, $offset + 10);
            $compressed_size = $this->u32($contents, $offset + 20);
            $name_length = $this->u16($contents, $offset + 28);
            $extra_length = $this->u16($contents, $offset + 30);
            $comment_length = $this->u16($contents, $offset + 32);
            $local_offset = $this->u32($contents, $offset + 42);
            $name = str_replace('\\', '/', substr($contents, $offset + 46, $name_length));

            if ($local_offset + 30 > $length || substr($contents, $local_offset, 4) !== "PK\x03\x04") {
                return array('message' => 'Invalid XLSX file structure.');
            }

            $local_name_length = $this->u16($contents, $local_offset + 26);
            $local_extra_length = $this->u16($contents, $local_offset + 28);
            $data_offset = $local_offset + 30 + $local_name_length + $local_extra_length;
            $data = substr($contents, $data_offset, $compressed_size);

            if ($method === 8) {
                $data = @gzinflate($data);
                if ($data === false) {
                    return array('message' => 'Unable to decompress XLSX content.');
                }
            } elseif ($method !== 0) {
                return array('message' => 'Unsupported XLSX compression method.');
            }

            $entries[$name] = $data;
            $offset += 46 + $name_length + $extra_length + $comment_length;
        }

        return $entries;
    }

    protected function read_shared_strings($xml)
    {
        if (empty($xml)) {
            return array();
        }

        $shared = array();
        $strings = simplexml_load_string($xml);
        if (!$strings) {
            return $shared;
        }

        foreach ($strings->si as $string) {
            if (isset($string->t)) {
                $shared[] = (string)$string->t;
                continue;
            }

            $text = '';
            foreach ($string->r as $run) {
                $text .= (string)$run->t;
            }
            $shared[] = $text;
        }

        return $shared;
    }

    protected function read_date_styles($xml)
    {
        if (empty($xml)) {
            return array();
        }

        $styles = simplexml_load_string($xml);
        if (!$styles) {
            return array();
        }

        $custom_formats = array();
        if (!empty($styles->numFmts->numFmt)) {
            foreach ($styles->numFmts->numFmt as $format) {
                $attributes = $format->attributes();
                $custom_formats[(int)$attributes['numFmtId']] = (string)$attributes['formatCode'];
            }
        }

        $date_styles = array();
        if (!empty($styles->cellXfs->xf)) {
            foreach ($styles->cellXfs->xf as $index => $xf) {
                $attributes = $xf->attributes();
                $format_id = isset($attributes['numFmtId']) ? (int)$attributes['numFmtId'] : 0;
                if ($this->is_date_format($format_id, isset($custom_formats[$format_id]) ? $custom_formats[$format_id] : '')) {
                    $date_styles[(int)$index] = true;
                }
            }
        }

        return $date_styles;
    }

    protected function cell_value(SimpleXMLElement $cell, array $shared_strings, array $date_styles)
    {
        $attributes = $cell->attributes();
        $type = isset($attributes['t']) ? (string)$attributes['t'] : '';
        $style = isset($attributes['s']) ? (int)$attributes['s'] : null;

        if ($type === 'inlineStr') {
            return isset($cell->is->t) ? (string)$cell->is->t : '';
        }

        $value = isset($cell->v) ? (string)$cell->v : '';
        if ($type === 's') {
            return isset($shared_strings[(int)$value]) ? $shared_strings[(int)$value] : '';
        }
        if ($type === 'b') {
            return $value === '1' ? 'TRUE' : 'FALSE';
        }
        if ($value !== '' && $style !== null && !empty($date_styles[$style]) && is_numeric($value)) {
            return gmdate('Y-m-d', ((float)$value - 25569) * 86400);
        }

        return $value;
    }

    protected function is_date_format($format_id, $format_code)
    {
        if (in_array($format_id, array(14, 15, 16, 17, 18, 19, 20, 21, 22, 45, 46, 47), true)) {
            return true;
        }

        $format_code = strtolower(preg_replace('/"[^"]*"|\[[^\]]*\]/', '', (string)$format_code));
        return $format_code !== '' && (strpos($format_code, 'yy') !== false || strpos($format_code, 'dd') !== false);
    }

    protected function column_index($reference)
    {
        preg_match('/^[A-Z]+/i', $reference, $matches);
        $letters = strtoupper(isset($matches[0]) ? $matches[0] : 'A');
        $index = 0;
        for ($i = 0, $length = strlen($letters); $i < $length; $i++) {
            $index = ($index * 26) + (ord($letters[$i]) - 64);
        }

        return $index - 1;
    }

    protected function is_empty_row(array $row)
    {
        foreach ($row as $value) {
            if (trim((string)$value) !== '') {
                return false;
            }
        }

        return true;
    }

    protected function u16($contents, $offset)
    {
        $value = unpack('v', substr($contents, $offset, 2));
        return $value[1];
    }

    protected function u32($contents, $offset)
    {
        $value = unpack('V', substr($contents, $offset, 4));
        return $value[1];
    }
}
