<?php
/**
 * 系统升级处理，所有数据库更新，全部放在本文件里。
 * 
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 * 
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: songdemei<songdemei@suxuantech.cn> 2017-10-17 $
 * 
 */
namespace app\index\lib;
class Update {
    protected static $instance;
    var $tables = [];
    var $thisVersionTables = [];
    var $tableColumns = [];
    var $tableIndexes = [];
    var $sqlUpdate = [];
    var $versionTableName = 'crm_app_version';
    function __construct() {
        $this->sqlUpdate = require_once('config.php');
    }
    
   function runUpdate(){
        $dbVersion = $this->getDbVersion();
        $thisVersion = get_version('APP');

        if(version_compare($thisVersion, $dbVersion, '<=')){
            return array('code'=>101,'msg'=>'数据库版本高于当前程序版一，不需要升级。');
        }
        //申请原子操作，确保只有一个请求在处理升级
        $res  = \think\Cache::inc('update_lock');
        if($res > 1){
            return array('code'=>102,'msg'=>'已有升级正在进行，请稍后，或删除:update_lock缓存值。');
        }
        ignore_user_abort(true);
        $sqlActive = null;
        $ddlActive = null;
        foreach ($this->sqlUpdate as $k=>$sqlConfig){
            if (version_compare($k, $dbVersion, '>') && version_compare($k, $thisVersion, '<=')) {
                $sql = $this->getSql($sqlConfig);
                if (!empty($sql['ddl'])) {
                    $ddlActive = $ddlActive ? array_merge($ddlActive, $sql['ddl']) : $sql['ddl'];
                }
                if (!empty($sql['dml'])) {
                    $sqlActive = $sqlActive ? array_merge($sqlActive, $sql['dml']) : $sql['dml'];
                }
            }
        }
        $allNum = count($ddlActive) + count($sqlActive) + 1;
        cache('db_update_process_all',$allNum);
        $doNum = 0;
        $str = '';
        if(!empty($ddlActive)){
            try{
                foreach ($ddlActive as $sqlinfo) {
                    $doNum++;
                    cache('db_update_process',$doNum);
                    cache('db_update_process_sql',$sqlinfo);
                    db()->execute($sqlinfo);
                }
                session('ddl_update_sql',implode("\r\n", $ddlActive));
            }catch (\Exception $e) {
                $str .=  "===$dbVersion=>==DDL数据库升级失败==>=={$thisVersion}===<br/>\r\n";
                $str .= "最后执行语句：".$sqlinfo."<br/>\r\n";
                $str .= '<b>错误：'.$e->getMessage()."</b><br/>\r\n";;
                foreach($e->getTraceAsString() as $k=>$v){
                    $str .= $v."<br/>\r\n";
                }
                $str .= "===========本次升级SQL：============<br/>\r\n";
                // dump($sqlActive);
                foreach($sqlActive as $k=>$v){
                    $str .= $v."<br/>\r\n";
                }
                \think\Cache::rm('update_lock');
                return array('code'=>100,'msg'=>$str);
                exit();
            }
        }
        \think\Db::startTrans();
        try{
            foreach($sqlActive as $sqlinfo){
                $doNum++;
                cache('db_update_process',$doNum);
                cache('db_update_process_sql',$sqlinfo);
                \think\Db::execute($sqlinfo);
            }
            $upSql = session('ddl_update_sql').implode("\r\n", $sqlActive);
            $sqlinfo = db($this->versionTableName)->fetchSql()->strict(false)->insert(['version'=>$thisVersion,'up_time'=>date('Y-m-d H:i:s'),'up_sql'=>$upSql]).';';
            $doNum++;
            cache('db_update_process',$doNum);
            cache('db_update_process_sql',$sqlinfo);
            \think\Db::execute($sqlinfo);
            \think\Db::commit();
        }catch(\Exception $e){
            \think\Db::rollback();
            $str .=  "===$dbVersion=>==数据库升级失败==>=={$thisVersion}===<br/>\r\n";
            $str .= "最后执行语句：".$sqlinfo."<br/>\r\n";
            $str .= '<b>错误：'.$e->getMessage()."</b><br/>\r\n";;
            foreach($e->getTraceAsString() as $k=>$v){
                $str .= $v."<br/>\r\n";
            }
            $str .= "===========本次升级SQL：============<br/>\r\n";
            // dump($sqlActive);
            foreach($sqlActive as $k=>$v){
                $str .= $v."<br/>\r\n";
            }
            \think\Cache::rm('update_lock');
            return array('code'=>100,'msg'=>$str);
        }
        session('ddl_update_sql',null);
        cache('db_version_' . config('APP_ID'), null);
        \think\Cache::rm('update_lock');
        return array('code'=>200,'msg'=>'');
    }
    function getUpdateSqlList(){
        $dbVersion = $this->getDbVersion();
        $sqlActive = null;
        foreach ($this->sqlUpdate as $k=>$sqlConfig){
            if(version_compare($k, $dbVersion, '>')){
                $sql = $this->getSql($sqlConfig);
                if (!empty($sql['ddl'])) {
                    $sqlActive = $sqlActive?array_merge($sqlActive, $sql['ddl']):$sql['ddl'];
                }
                if(!empty($sql['dml'])){
                    $sqlActive = $sqlActive?array_merge($sqlActive, $sql['dml']):$sql['dml'];
                }
            }
        }
        return $sqlActive;
    }
    function getSql($config){
        $sql = [];
        foreach($config as $k=>$info){
            $thisSql = '';
            $sqlType = '';
            switch ($info['type']){
                case 'table':
                    $sqlType = 'ddl';
                    if($this->isTableExist($info['name']) === true){
                        continue;
                    }
                    $thisSql .= 'CREATE TABLE `'.trim($info['name']).'`(';
                    $thisSql .= $this->createColumn($info['fields'],$info['name']);
                    $thisSql .= ') COLLATE=\'utf8_unicode_ci\' ';
                    $thisSql .= ' ENGINE=InnoDB ';
                    if(isset($info['comment'])){
                        $thisSql .= ' COMMENT \''.addslashes($info['comment']).'\'';
                    }
                    break;
                case 'column':
                    $sqlType = 'ddl';
                    $column = $this->addColumn($info['fields'], $info['name']);
                    if($column){
                        $thisSql .= ' ALTER TABLE `'.trim($info['name']).'` '.$column;
                    }
                    
                    break;
                case 'data':
                    $sqlType = 'dml';
                    $column = $this->addData($info['fields'], $info['name'],$info['unique']);
                    if($column){
                        $thisSql .= ' INSERT INTO `'.trim($info['name']).'` '.$column;
                    }
                    break;
                case 'sql':
                    $thisSql = $info['sql'];
                    if(strpos(strtolower(trim($thisSql)),'alter') === 0){
                        $sqlType = 'ddl';
                    }else{
                        $sqlType = 'dml';
                    }
                    break;
            }
            if($thisSql){
                $sql[$sqlType][] = $thisSql;
            }      
        }
        return $sql;
    }
    /**
     * 添加字段时，组成字段语句。
     * ﻿ALTER TABLE `oa_app_version`
     * ADD COLUMN `test` varchar(50) NULL ,
     * ADD COLUMN `列 5` VARCHAR(50) NULL,
     * ADD INDEX `Index 2` (`version`, `id`);
     * @param type $columnList
     * @param type $table
     * @return string
     */
    function addColumn($columnList,$table){
        $ret = '';
        // static $allColumn;
        // if(!$allColumn){
            
        // }
        $allColumn = $this->getColumns($table);
        if($allColumn === false){
            return false;
        }
        $allIndexes = $this->getIndexes($table);
        if($allIndexes === false){
            return false;
        }
        foreach ( $columnList as $value){
            if($value['type'] == 'key'){
                if(in_array($value['name'], $allIndexes)){
                    //已经有这个索引了。
                    continue;
                }else{
                    $this->tableIndexes[$table][] = $value['name'];
                }
                $keyType = $value['keytype']?strtoupper($value['keytype']):'INDEX';
                $keys[] = ' ADD '.$keyType.' `'.$value['name'].'` (`'.implode('`,`', $value['fields']).'`) ';
                continue;
            }
            if(in_array($value['name'], $allColumn)){
                //已经有这个字段了。
                continue;
            }else{
                $this->tableColumns[$table][] = $value['name'];
            }
            $ret .= ' ADD COLUMN `'.$value['name'].'` '.$value['type'];
            $ret .= isset($value['length'])?'('.$value['length'].') ':' ';
            $ret .= $value['is_null']?' NULL ':' NOT NULL ';
            
            switch (true){
                case strtoupper(substr($value['default'],0,14))=='AUTO_INCREMENT':
                    $ret .=' '.$value['default'].' ';
                    break;
                /*
                case strtoupper(substr($value['default'],0,17))=='CURRENT_TIMESTAMP' && in_array(strtoupper($value['type']),['date','datetime'] ):
                    $ret .=' DEFAULT '.$value['default'];
                    break;
                case is_string($value['default']):
                    $ret .= ' DEFAULT '.$value['default'];
                    break;
                 * 
                 */
                case isset($value['default']):
                    $ret .= is_numeric($value['default'])?' DEFAULT '.$value['default'].' ':' DEFAULT \''.$value['default'].'\' ';
                    break;
            }
            if(in_array(strtolower($value['type']),['varchar','char','text','tinytext','mediumtext','longtext','json']) ){
                $ret .= ' COLLATE \'utf8_unicode_ci\' ';
            }
            if(isset($value['comment'])){
                $ret .= ' COMMENT \''.addslashes($value['comment']).'\'';
            }
            $ret .= ',';
        }
        unset($allColumn,$allIndexes);
        $ret .= implode(',', $keys);
        $ret = trim($ret,',');
        return $ret;
    }
    /**
     * 创建表时组成字段语句
     * @param type $config
     * @return type
     */
    function createColumn($config,$table=''){
        $ret = '';
        $primary = '';
        $keys = '';
        foreach ($config as $value){
            if($value['type'] == 'key'){
                $this->tableIndexes[$table][] = $value['name'];
                $keys[] = ' INDEX `'.$value['name'].'` (`'.implode('`,`', $value['fields']).'`) ';
                continue;
            }
            $this->tableColumns[$table][] = $value['name'];
            $ret .= '`'.$value['name'].'` '.$value['type'];
            $ret .= isset($value['length'])?'('.$value['length'].') ':' ';
            $ret .= $value['is_null']?' NULL ':' NOT NULL ';
            switch (true){
                case strtoupper(substr($value['default'],0,14))=='AUTO_INCREMENT':
                    $ret .=' '.$value['default'].' ';
                    $primary = $value['name'];
                    break;
                /*
                case strtoupper(substr($value['default'],0,17))=='CURRENT_TIMESTAMP' && in_array(strtoupper($value['type']),['date','datetime'] ):
                    $ret .=' DEFAULT '.$value['default'];
                    break;
                case is_string($value['default']):
                    $ret .= ' DEFAULT '.$value['default'];
                    break;
                 * 
                 */
                case isset($value['default']):
                    $ret .= is_numeric($value['default'])?' DEFAULT '.$value['default'].' ':' DEFAULT \''.$value['default'].'\' ';
                    break;
                
            }
            if(in_array(strtolower($value['type']),['varchar','char','text','tinytext','mediumtext','longtext','json']) ){
                $ret .= ' COLLATE \'utf8_unicode_ci\' ';
            }
            if(isset($value['comment'])){
                $ret .= ' COMMENT \''.addslashes($value['comment']).'\'';
            }
            if($value['primary']){
                $primary = $value['name'];
            }
            
            $ret .= ',';
            
        }
        
        if($primary){
            $ret .= 'PRIMARY KEY (`'.$primary.'`),';
        }
        $ret .= implode(',', $keys);
        $ret = trim($ret,',');
        return $ret;
    }

    /**
     * 插入数据时，组成字段语句。
     * INSERT INTO USER(name, sex, age)
     * SELECT '小红','1','1' UNION
     * SELECT '王明','2','14' UNION
     * SELECT '赵强','1','64' UNION
     * SELECT '孙俪','2,'34'；
     * @param type $dataList
     * @param type $table
     * @return string
     */
    function addData($dataList,$table,$unique=''){
        $ret = '';
        // static $allColumn;
        // if(!$allColumn){
            
        // }
        $allColumn = $this->getColumns($table);
        if($allColumn === false){
            return false;
        }

        $arr = [];
        foreach ($dataList['field'] as $key => $value) {
            $arr[$key] = '`'.$value.'`';
        }
        $ret .=' ('.implode(',',$arr).') ';
        $dataCount = 0;
        foreach($dataList['value'] as $k => $v){
            if($unique){
                $uniqueKey =  array_search($unique,$dataList['field']);
                if($unique !== false){
                    $info = db($table)->where($unique,$v[$uniqueKey])->find();
                    if($info){
                        continue;
                    }
                    unset($info);
                }
            }
            $dataList['value'][$k]=$arr = array_map(create_function('$item', 'return "\'$item\'";'), $v);
            $ret .=" SELECT ".implode(',',$dataList['value'][$k])." union ";
            $dataCount++;
        }
        $sql=substr($ret,0,strripos($ret,"union"));
        unset($ret);
        return $dataCount>0?$sql:'';
    }

    function getIndexes($table){
        if($this->isTableExist($table) === false){
            return false;
        }
        if(!$this->tableIndexes[$table]){
            if(in_array($table, $this->thisVersionTables)){
                $this->tableIndexes[$table] = [];
                return $this->tableIndexes[$table];
            }
            $sql = "show index from ".$table;
            $res = db()->master()->query($sql);
            $keys = array_column($res,'Key_name');
            $this->tableIndexes[$table] = $keys;
            unset($keys);
        }
        return $this->tableIndexes[$table];
    }
    function getColumns($table){
        if($this->isTableExist($table) === false){
            return false;
        }
        if(!$this->tableColumns[$table]){
            if(in_array($table, $this->thisVersionTables)){
                $this->tableColumns[$table] = [];
                return $this->tableColumns[$table];
            }
            $sql = 'desc '.$table;
            $res = db()->master()->query($sql);
            $column = [];
            foreach($res as $value){
                $column[] = $value['Field'];
            }
            $this->tableColumns[$table] = $column;
            unset($column);
        }
        return $this->tableColumns[$table];
    }
    
    function getDbVersion(){
        static $version;
        $version = $version?$version:cache('db_version_'.  config('APP_ID'));
        if(!$version){
            if($this->isTableExist($this->versionTableName)){
                $version = db($this->versionTableName)->order('id', 'desc')->find();
                $version = $version['version']?$version['version']:'0.0.0';
                
                cache('db_version_'.  config('APP_ID'),$version);
            }else{
                $sql ='CREATE TABLE `'.$this->versionTableName.'` (
                        `id` INT(11) NOT NULL AUTO_INCREMENT,
                        `version` VARCHAR(50) NULL DEFAULT NULL COLLATE \'utf8_unicode_ci\',
                        `up_time` DATETIME NULL DEFAULT NULL,
                             `up_sql` text NULL ,
                        PRIMARY KEY (`id`)
                    )
                    COMMENT=\'应用版本管理\'
                    COLLATE=\'utf8_unicode_ci\'
                    ENGINE=InnoDB;';
                $res = db()->execute($sql);
                if($res === false){
                    return false;
                }
                $version = '0.0.0';
            }
            
            
        }
        return $version;
    }
    
    function isTableExist($table){
        if(!$this->tables){
            $sql = "show tables;";
            $res = db()->master()->query($sql);
            foreach ($res as $key => $value) {
                $this->tables = array_merge($this->tables,array_values($value));
            }
            unset($res);
        }
        if(in_array($table, $this->tables)){
            return true;
        }
        $this->tables[] = $table;//检查一个表，就把表放到里面，以防止当前有添加表和加字段的情况，在一次升级里，
        $this->thisVersionTables[] = $table;//
        return false;
    }

}