<?php
class datatable
{
    /**
     * Header.
     *
     * @var array
     * @access public
     */
    public $header;

    /**
     * Body.
     *
     * @var array
     * @access public
     */
    public $body;

    /**
     * Max row.
     *
     * @var int
     * @access public
     */
    public static $MAX_ROW = 30000;

    public $app;

    public $name;

    /**
     * Constructor.
     *
     * @access public
     * @return void
     */
    public function __construct()
    {
        global $app;
        $this->app    = $app;
        $this->header = array();
        $this->body   = array();
    }

    /**
     * 将 SQL 语句字符串拆分成多个语句，并过滤掉空语句。
     *
     * @param string $sql 输入的 SQL 语句字符串
     * @return array 包含拆分并过滤后的 SQL 语句的数组
     */
    public function sqlToArr($sql)
    {
        $statements = explode(';', $sql);
        return array_map('trim', array_filter($statements));
    }

    /**
     * Set header.
     *
     * @param array $data
     * @access public
     * @return void
     */
    public function setHeader($data)
    {
        $headers     = array();
        $headerNames = array();
        foreach($data as $index => $header)
        {
            if(!empty($header) and in_array($header, $headerNames))
            {
                $count = 1;
                $newHeader = $header . $count;
                while(in_array($newHeader, $headerNames))
                {
                    $count += 1;
                    $newHeader = $header . $count;
                }
                $header = $newHeader;
            }
            $headers[]     = array('name' => $header, 'field' => 'col' . $index, 'deleted' => false);
            $headerNames[] = $header;
        }

        $this->header = $headers;
    }

    /**
     * Add row.
     *
     * @param array $data
     * @param int   $maxCol
     * @access public
     * @return void
     */
    public function addRow($data, $maxCol)
    {
        if(count($data) < $maxCol) $data = array_pad($data, $maxCol, '');
        $this->body[] = $data;
    }

    /**
     * Get maxium column lenth.
     *
     * @param array $data
     * @access public
     * @return int
     */
    public function getMaxCol($data)
    {
        $len = 0;
        foreach($data as $row)
        {
            $len = max($len, count($row));
        }

        return $len;
    }

    /**
     * Create table.
     *
     * @param string $name
     * @param string $connection
     * @param string $tableName
     * @access public
     * @return void
     */
    public function create($name, $connection, $tableName)
    {
        $data = $this->getData($name, $connection, $tableName);

        if(count($data) <= 1) return;
        $maxCol = $this->getMaxCol($data);
        $header = $data[0];

        if(count($header) < $maxCol) $header = array_pad($header, $maxCol, '');
        $this->setHeader($header);
        array_shift($data);
        foreach($data as $row)
        {
            $this->addRow($row, $maxCol);
        }
        $this->name = $this->app->config->db->analysis . 'analysis' . $name;
        $this->initSchema();
        if(!empty($data)) $this->flushData();
    }

    /**
     * Create analysis table.
     *
     * @param string     name
     * @param string     $connection
     * @param string|int $tableName
     * @access public
     * @return void
     *
     * 如果是excel, connection存的是当前文件的路径，并且tableName是int类型，意为第几张sheet，例如0是sheet1。
     * 如果是mysql，那么connection存的是json过的mysql配置，tableName是string类型，是当前选中的数据表名字，例如禅道数据库的zt_action表。
     *
     * If it's excel, the connection stores the path to the current file, and tableName is of type int, which means the number of sheets. For example, 0 is sheet1.
     * For mysql, connection stores the mysql configuration after json. tableName is a string and is the name of the currently selected data table, such as the now selected zt_action table for ZenTao database.
     */
    public function createAnalysis($name, $connection, $tableName, $ids)
    {
        if(!empty($connection))
        {
            $data = $this->getData($name, $connection, $tableName, $ids);
            if(empty($data)) return;
            $maxCol = $this->getMaxCol($data);
            $header = $data[0];

            if(count($header) < $maxCol) $header = array_pad($header, $maxCol, '');

            $this->setHeader($header);
            array_shift($data);
            foreach($data as $row)
            {
                $this->addRow($row, $maxCol);
            }

            $count = count($this->body);
            if($count > static::$MAX_ROW) return;
        }

        $this->name = $this->app->config->db->analysis . 'analysis' . $name;
        $this->initSchema();
        $this->flushData();
    }

    /**
     * Create table from data.
     * @param string $name
     * @param array $data
     * @access public
     * @return void
     */
    public function createFromData($name, $data, $clearCol = false)
    {
        if(count($data) == 0) return;
        $this->name = $this->app->config->db->analysis . 'analysis' . $name;

        // create table if not exist
        $sql  = "CREATE TABLE IF NOT EXISTS `{$this->name}` (\n";
        if($this->app->config->db->driver == "sqlite")
        {
            $sql .= "`id` INTEGER PRIMARY KEY AUTOINCREMENT\n";
            $sql .= ");";
        }
        else
        {
            $sql .= "`id` mediumint unsigned NOT NULL AUTO_INCREMENT, \n";
            $sql .= "PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;\n";
        }

        // create column fields if not exist
        $keys = array_keys($data);
        arsort($keys, SORT_NUMERIC);
        reset($keys);
        $colMax = current($keys);
        global $config;
        if($this->app->config->db->driver == "sqlite")
        {
            $cols = $this->app->dbh->query("PRAGMA table_info({$this->name})")->fetchAll();
        }
        else
        {
            $cols = $this->app->dbh->query("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = '{$this->name}' AND TABLE_SCHEMA = '{$config->db->name}'")->fetchAll();
        }
        $existCols = array();
        if($this->app->config->db->driver == "sqlite")
        {
            $existCols = array_column($cols, 'name');
        }
        else
        {
            foreach($cols as $col)
            {
                $existCols[] = $col->COLUMN_NAME;
            }
        }

        for($index = 0; $index < (int)$colMax; $index++)
        {
            $field = 'col' . $index;
            $exist = in_array($field, $existCols);
            if($this->app->config->db->driver == "sqlite")
            {
                if(!$exist) $sql .= "ALTER TABLE `{$this->name}` ADD COLUMN `$field` TEXT;\n";
            }
            else
            {
                if(!$exist) $sql .= "ALTER TABLE `{$this->name}` ADD COLUMN `$field` varchar(255);\n";
            }
        }

        // insert data
        foreach($data as $colIndex => $col)
        {
            $colField = 'col' . ($colIndex - 1);
            if($clearCol) $sql .= "UPDATE `{$this->name}` set `$colField` = null;\n";

            foreach($col as $index => $value)
            {
                if($value === null) continue;
                $id = $index + 1;
                if ($this->app->config->db->driver == "sqlite")
                {
                    $sql .= "INSERT INTO `{$this->name}` (`id`, `$colField`) VALUES($id, '$value') ON CONFLICT (`id`) DO UPDATE SET `$colField` = '$value';";
                }
                else
                {
                    $sql .= "INSERT INTO `{$this->name}` (`id`, `$colField`) VALUES($id, '$value') ON DUPLICATE KEY UPDATE `$colField`='$value';";
                }
            }
        }

        try
        {
            if($this->app->config->db->driver == "sqlite")
            {
                $sqls = $this->sqlToArr($sql);
                foreach($sqls as $sql)
                {
                    $this->app->dbh->query($sql);
                }
            }
            else
            {
                // https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd
                $this->app->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
                $this->app->dbh->query($sql);
            }
        }
        catch(PDOException $exception)
        {
            a($exception);
            a($sql);
            return false;
        }

        return $this->buildSchema($colMax);
    }

    /**
     * Build schema with columns setting
     * @param int $colMax
     * @param string $name
     * @access public
     * @return void
     */
    public function buildSchema($colMax, $name = '')
    {
        $columns = array();
        for($index = 0; $index < $colMax; $index++)
        {
            $column = new stdclass();
            $column->name = null;
            $column->field = 'col' . $index;
            $column->deleted = false;
            $columns[] = $column;
        }
        $schema = new stdclass();
        $schema->table = !empty($name) ? ($this->app->config->db->analysis . 'analysis' . $name) : $this->name;
        $schema->columns = $columns;
        return $schema;
    }

    /**
     * Init schema.
     *
     * @access private
     * @return bool
     */
    private function initSchema()
    {
        $sql  = "DROP TABLE IF EXISTS `{$this->name}`;\n";
        $sql .= "CREATE TABLE IF NOT EXISTS `{$this->name}` ( ";
        if($this->app->config->db->driver == "sqlite")
        {
            $sql.= "`id` INTEGER PRIMARY KEY AUTOINCREMENT, ";
        }
        else
        {
            $sql .= "`id` mediumint unsigned NOT NULL AUTO_INCREMENT, ";
        }
        if(!empty($this->header))
        {
            foreach($this->header as $header) $sql  .= "`{$header['field']}` text, ";
        }

        if($this->app->config->db->driver == "sqlite")
        {
            $sql  = rtrim($sql, ', ') . ");";
        }
        else
        {
            $sql .= "PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
        }

        try
        {
            if($this->app->config->db->driver == "sqlite")
            {
                $sqls = $this->sqlToArr($sql);
                foreach($sqls as $sql)
                {
                    $this->app->dbh->query($sql);
                }
            }
            else
            {
                // https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd
                $this->app->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
                $this->app->dbh->query($sql);
            }
        }
        catch(PDOException $exception)
        {
            a($exception);
            a($sql);
            return false;
        }

        return true;
    }

    /**
     * Flush data
     *
     * @access private
     * @return bool
     */
    private function flushData()
    {
        if(empty($this->body)) return true;

        $fields = array();
        foreach($this->header as $header)
        {
            $fields[] = "`{$header['field']}`";
        }
        $sql = "INSERT INTO `{$this->name}` (" . implode(', ', $fields) . ") VALUES ";

        $count = 0;

        foreach($this->body as $row)
        {
            $rowData = array();
            foreach($row as $cell)
            {
                if(strpos($cell, "'") !== false)
                {
                    $cell = str_replace("'", "\'", $cell);
                    $cell = str_replace("\\\'", "\'", $cell);
                }
                $cell = preg_replace("#([^\\\])[\\\]$#", '$1\\\\\\', $cell);
                $rowData[] =  "'" . $cell . "'";
            }
            $sql .= ($count == 0 ? '' : ', ') . "(" . implode(', ', $rowData) . ")";

            $count += 1;
            if($count > 1000)
            {
                try
                {
                    $this->app->dbh->query($sql);
                }
                catch(PDOException $exception)
                {
                    a($exception);
                    a($sql);
                    return false;
                }

                $sql = "INSERT INTO `{$this->name}` (" . implode(', ', $fields) . ") VALUES ";
                $count = 0;
            }
        }

        if(!empty($sql))
        {
            try
            {
                $this->app->dbh->query($sql);
            }
            catch(PDOException $exception)
            {
                a($exception);
                a($sql);
                return false;
            }
        }

        return true;
    }

    /**
     * Delete data
     *
     * @access private
     * @param  string $table
     * @param  int    $row
     * @param  int    $col
     * @return void
     */
    public function delete($table, $row, $col)
    {
        $sql = "UPDATE {$table} SET col{$col} = null WHERE id = {$row}";
        $this->app->dbh->query($sql);
    }

    /**
     * Get datatable data.
     *
     * @param  string $name
     * @param  string $connection
     * @param  string $tableName
     * @access public
     * @return array
     */
    public function getData($name, $connection, $tableName, $ids)
    {
        if(strpos($connection, '/data/upload/') !== false)
        {
            $this->app->loadClass('perfexcel', true);
            $excel = perfexcel::read($connection);
            $sheetList = $excel->sheetList();
            $sheetName = $sheetList[$tableName];
            $data  = $excel->openSheet($sheetName)->getSheetData();
        }
        else
        {
            $connection = json_decode($connection);

            $dbConfig = new stdclass();
            $dbConfig->driver       = 'mysql';
            $dbConfig->host         = $connection->ip;
            $dbConfig->port         = $connection->port;
            $dbConfig->name         = $connection->database;
            $dbConfig->user         = $connection->account;
            $dbConfig->password     = $connection->password;
            $dbConfig->persistant   = false;
            $dbConfig->encoding     = 'UTF8';
            $dbConfig->strictMode   = false;
            $dbConfig->triggerError = false;
            $dbConfig->timeout      = 5;

            global $dao;
            $connectDao = clone $dao;
            $connectDao->autoLang = false;

            $connectDao->dbh = $this->app->connectByPDO($dbConfig);
            if(is_array($connectDao->dbh) and isset($connectDao->dbh['result']) and $connectDao->dbh['result'] == 'fail') return array();

            $columns = $this->app->dao->getColumns("select * from `$tableName`");
            $fields = array();
            foreach($columns as $column)
            {
                $item = new stdclass();
                $item->name = $column['name'];
                $item->type = $column['native_type'];
                $fields[] = $item;
            }

            $notAllowedTypes = array('VAR_STRING', 'STRING', 'BLOB', 'LONG_BLOB', 'MEDIUMBLOB', 'VARCHAR', 'CHAR', 'BPCHAR', 'TEXT', 'TINYBLOB', 'GEOMETRY', 'XML', 'BIT', 'UUID', 'JSON');
            $allowedNames    = array('name', 'title', 'status', 'statge', 'pri', 'mailto', 'openedBy', 'assignedTo', 'resolvedBy', 'closedBy', 'lastEditedBy', 'canceledBy', 'PO', 'PM', 'QM', 'team', 'type', 'found', 'feedbackBy', 'openedBuild', 'resolution', 'resolvedBuild', 'relatedBug', 'mode', 'finishedBy', 'stage', 'changedBy', 'reviewedBy', 'category', 'closedReason', 'attribute', 'QD', 'RD');
            $tablefield      = array();
            $allowedFields   = array();
            foreach($fields as $field)
            {
                if(!in_array($field->type, $notAllowedTypes) || in_array($field->name, $allowedNames))
                {
                    if(in_array($field->name, $allowedNames)) $field->name = str_replace('"', '', htmlspecialchars_decode($field->name));
                    $tablefield[]    = $field->name;
                    $allowedFields[] = "`$field->name`";
                }
            }
            $tablefield    = array_unique($tablefield);
            $allowedFields = array_unique($allowedFields);
            $tabledata = $connectDao->select(implode(',', $allowedFields))
                ->from($tableName)
                ->where('id')->in($ids)
                ->limit(5000)
                ->fetchAll();
            $tabledata = json_decode(json_encode($tabledata), true);

            $connectDao->dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
            $connectDao->dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
            unset($connectDao);

            $data = array();
            foreach($tablefield as $field) $data[0][] = $field;
            foreach($tabledata as $row)    $data[] = array_values($row);
        }

        return $data;
    }
}
