<?php
namespace model\bi;
/** 生成维度与汇总表
 * Class pushData
 * @package model
 */
class pushData extends \model
{
    public $dbbi;
    public $dircfg;
    public $cfg;
    public $cols;
    public $enables;
	public function __construct()
    {
		$db = new \lib\db(\mvc::$cfg['db']['main']);
		parent::__construct();
		$this->dbbi = new \lib\db(\mvc::$cfg['db']['main']);
		$this->dircfg = \mvc::$cfg['dir']['project'].'/config/bi';
		$cols = require($this->dircfg.'/cols.php');
		$this->cols = $cols;
		$this->enables = require($this->dircfg.'/enable.php');
	}

	public function execute()
    {
        $startTime = time();
        $files = scandir($this->dircfg.'/data');
        ob_end_clean();
        
        $st = $this->dbbi->query('SHOW TABLES');
        if($rows = $st->fetchAll()){
            foreach($rows as $row){
                $table = array_shift($row);
                if(substr($table,0,strlen('zmem_')) == 'zmem_'){
                    $this->dbbi->query('DROP TABLE IF EXISTS '.'zmem_'.$table);
                }
            }
        }
        foreach($files as $file){
            if($file=='.' || $file=='..') continue;
            if($this->enables && !in_array(basename($file),$this->enables)) continue;
            $cols = $this->cols;
            $options = require($this->dircfg.'/data/'.$file);
            if(!isset($options['enable']) || $options['enable']!==true){
                continue;
            }
            $table = $options['table'];
            echo $table."\r\n";
            flush();
            ob_flush();
            if($options['createTable']){
                if(isset($options['dropTable']) && $options['dropTable']){
                    $this->dbbi->query('DROP TABLE IF EXISTS '.$table);
                    $this->dbbi->query('DROP TABLE IF EXISTS '.$table.'_dimension');
                }
                $this->createTable($table,$options);
                $this->createTableDimension($table);
            }
            if(isset($options['pushData']) && !empty($options['pushData'])){
                if(isset($options['truncateTable']) && $options['truncateTable']){
                    $this->dbbi->query('TRUNCATE TABLE '.$table);
                    $this->dbbi->query('TRUNCATE TABLE '.$table.'_dimension');
                }
                $this->pushData($table,$options);
            }
        }
		echo('执行完成, 总耗时: '.(time()-$startTime) . ' 秒');
	}
    
	public function pushData($table,$options)
    {   
        $pushCfg = $options['pushData'];
        if(isset($pushCfg['not']) && !empty($pushCfg['not'])){
            if($this->dbbi->has($table,$pushCfg['not'])){
                return false;
            }
        }
        if($pushCfg['php'] && file_exists($this->dircfg.'/php/'.$table.'.php')){
            require($this->dircfg.'/php/'.$table.'.php');
        }else if($pushCfg['sql']){
            $sql = $pushCfg['sql'];//file_get_contents($this->dircfg.'/sql/'.$table.'.sql');
            
            if(isset($pushCfg['sqloutlink']) && $pushCfg['sqloutlink']){
                $newSql = $sql;
                foreach($pushCfg['sqloutlink'] as $sqloutlink){
                    $linkdb = new \lib\db(\mvc::$cfg['db'][$sqloutlink['db']]);
                    foreach($sqloutlink['tables'] as $linkTable){
                        $this->buildMemTable($linkdb,$linkTable,$sql);
                        $newSql = str_replace($this->dbbi->dbname.'.'.$linkTable, $this->dbbi->dbname.'.zmem_'.$linkTable, $newSql);
                    }
                }
                $sql = $newSql;
            }
            
            $insertSql = "INSERT IGNORE INTO ".$this->dbbi->dbname.'.'.$table." " . $sql;
            //$this->dbbi->begin();
            $this->dbbi->query($insertSql);
            
            //$this->db->useBuff(false);
            //$st = $this->db->query($sql);
            $this->dbbi->useBuff(false);
            $st = $this->dbbi->query("SELECT * FROM ".$this->dbbi->dbname.'.'.$table);
            $i=1;
            $dimensions = [];
            $insert = [];
            while($row = $st->fetch()){
                $data = [];
                if(isset($pushCfg['default']) && $pushCfg['default']){
                    foreach($pushCfg['default'] as $k=>$v){
                        //if($k=='year') $v = mt_rand(2018,2021);
                        //if($k=='month') $v = mt_rand(1,12);
                        //if($k=='yearmonth') $v = $data['year'].$data['month'];
                        $data[$k] = $v;
                    }
                }
                foreach($options['fields'] as $k=>$v){
                    if(isset($row[$k])){
                        $data[$k] = $row[$k];
                    }else{
                        $data[$k] = null;
                    }
                }
                foreach($data as $k=>$v){
                    if(isset($this->cols[$k]['dimension']) && $this->cols[$k]['dimension']){
                        $dimensions[$k][strtolower($v)] = $v;
                    }
                }
                /*
                array_push($insert,$data);
                if($i % 1000 == 0){
                    $this->dbbi->insert($table,$insert);
                    $insert = [];
                }
                */
                $i++;
            }
            /*
            if(!empty($insert)){
                $this->dbbi->insert($table,$insert);
                $insert = [];
            }*/
            if($dimensions){
                foreach($dimensions as $k => $v){
                    sort($v);
                    $dimensions[$k] = $v;
                }
                echo $table."_dimension\r\n";
                flush();
                $this->dbbi->useBuff(true);
                $this->pushDimensionData($table,$dimensions);
            }
            //$this->db->useBuff(true);
            $this->dbbi->useBuff(true);
            //$this->dbbi->end();
            
            //删除外部表
            if(isset($pushCfg['sqloutlink']) && $pushCfg['sqloutlink']){
                foreach($pushCfg['sqloutlink'] as $sqloutlink){
                    foreach($sqloutlink['tables'] as $linkTable){
                        //$this->dbbi->query('DROP TABLE IF EXISTS '.'zmem_'.$linkTable);
                    }
                }
            }
        }
    }

	public function createTableDimension($table)
    {   
        $sql = "CREATE TABLE IF NOT EXISTS `".$table."_dimension` (
          `dimension` varchar(50) COLLATE latin1_bin NOT NULL,
          `key` varchar(50) COLLATE latin1_bin NOT NULL,
          `parent_dimension` varchar(50) COLLATE latin1_bin NOT NULL DEFAULT '',
          `parent_key` varchar(50) COLLATE latin1_bin NOT NULL DEFAULT '',
          `cn_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
          `en_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
          PRIMARY KEY (`dimension`,`key`),
          KEY `idx_parent` (`parent_dimension`,`parent_key`),
          KEY `idx_cn_name` (`dimension`,`cn_name`),
          KEY `idx_en_name` (`dimension`,`en_name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin";
        $this->dbbi->query($sql);
    }
    
	public function pushDimensionData($table,$dimensions)
    { 
        $list=[];
        if($rows = $this->dbbi->select($table.'_dimension','*')){
            foreach ($rows as $row){
                $list[$row['dimension'].'-'.$row['key']] = md5($row['cn_name'].$row['en_name']);
            }
        }
        $delWhere = [];
        $data = [];
        $dimensionValues = [];
        foreach($dimensions as $dimension => $items){
            foreach($items as $v){
                $key = $v;
                $parent_dimension = '';
                $parent_key = '';
                $cn_name = $key;
                $en_name = $key;
                if(isset($this->cols[$dimension]['getValue']) && !empty($this->cols[$dimension]['getValue'])){
                    
                    if(empty($key)){
                            $cn_name = '空';
                            $en_name = 'Empty';
                    }else{
                        if(!isset($dimensionValues[$dimension][$key])){
                            $dimensionValues[$dimension][$key] = $this->cols[$dimension]['getValue']($key,$this->db,$this->cols,$dimension);
                        }
                        $cn_name = $dimensionValues[$dimension][$key]['cn_name'];
                        $en_name = $dimensionValues[$dimension][$key]['en_name'];
                        if(!empty($this->cols[$dimension]['parent']) && isset($dimensionValues[$dimension][$key]['parent'])){
                            $parent_dimension = $this->cols[$dimension]['parent'];
                            $parent_key = $dimensionValues[$dimension][$key]['parent'];
                        }
                    }
                }
                $newItem = [
                    'dimension'=>$dimension,
                    'key'=>$key,
                    'parent_dimension'=>$parent_dimension,
                    'parent_key'=>$parent_key,
                    'cn_name'=>$cn_name,
                    'en_name'=>$en_name,
                ];
                if(!array_key_exists($dimension.'-'.$key,$list)){
                    //新增
                    $data[] = $newItem;
                }else if(md5($cn_name.$en_name) != $list[$dimension.'-'.$key]){
                    //更新
                    $delWhere['dimension'][] = $dimension;
                    $delWhere['key'][] = $key;
                    $data[] = $newItem;
                }
            }
        }
        if($delWhere){
            $this->dbbi->delete($table.'_dimension',$delWhere);
        }
        if($data){
            $this->dbbi->insert($table.'_dimension',$data);
        }
    }

	public function createTable($table,$options)
    {
        $tableEnd = ["ENGINE" => "InnoDB", "DEFAULT CHARSET" =>'latin1', "COLLATE" =>'latin1_bin'];
        if(isset($options['tableEnd']) && !empty($options['tableEnd'])){
            $tableEnd = $options['tableEnd'];
        }
        $fields = $options['fields'];
        if(isset($options['keys'])){
            $fields[] = 'PRIMARY KEY ('.implode(',',$options['keys']).')';
        }
        if(isset($options['indexs'])){
            foreach($options['indexs'] as $k=>$v){
                if(is_string($v)) $v = [$v];
                $fields[] = 'KEY '.$table.'_'.$v[0].'_'.$k.'('. implode(',',$v) .')';
            }
        }
        $this->dbbi->create($table,$fields,$tableEnd);
        if($this->dbbi->hasError()){
            dd($this->dbbi->last());
        }
	}
	
	public function buildMemTable($linkdb,$linkTable,$sql)
    {
        $linkSql = str_replace($this->dbbi->dbname.'.'.$linkTable, $linkdb->dbname.'.'.$linkTable, $sql);
        $st = $linkdb->query('SHOW COLUMNS FROM '.$linkTable);
        $rows = $st->fetchAll();
        $fields = [];
        $keys = [];
        $indexs = [];
        foreach($rows as $row){
            $fields[$row['Field']][] = $row['Type'];
            $fields[$row['Field']][] = ($row['Null']=='YES') ? 'NULL' : 'NOT NULL';
            if($row['Default']!==NULL){
                $fields[$row['Field']][] = 'DEFAULT '.$row['Default'];
            }
            if($row['Extra']){
                $fields[$row['Field']][] = $row['Extra'];
            }
           if($row['Key']=='PRI'){
                $keys[] = $row['Field'];
            }
        }
        $st = $linkdb->query('SHOW INDEX FROM '.$linkTable);
        $rows = $st->fetchAll();
        foreach($rows as $row){
            if($row['Key_name']!='PRIMARY'){
                $indexs[$row['Key_name']][] = $row['Column_name'];
            }
        }
        if($keys){
            $fields[] = 'PRIMARY KEY ('.implode(',',$keys).')';
        }
        $i=1;
        if($indexs){
            foreach($indexs as $k=>$v){
                $fields[] = 'KEY '.$linkTable.'_'.$v[0].'_'.$i++.'('. implode(',',$v) .')';
            }
        }
        $tableEnd = ["ENGINE" => "MyISAM", "DEFAULT CHARSET" =>'utf8mb4', "COLLATE" =>'utf8mb4_unicode_ci'];
        //$this->dbbi->query('DROP TABLE IF EXISTS '.'zmem_'.$linkTable);
        $this->dbbi->create('zmem_'.$linkTable,$fields,$tableEnd);
        $this->buildMemTableData($linkdb,$linkTable,$sql);
    }
    
	public function buildMemTableData($linkdb,$linkTable,$sql)
    {
        $table = 'zmem_'.$linkTable;
        $i=1;
        $insert = [];
        $linkdb->useBuff(false);
        $st = $linkdb->query("SELECT * FROM `".$linkTable."`");
        $this->dbbi->begin();
        while($row = $st->fetch()){
            array_push($insert,$row);
            if($i % 1000 == 0){
                $this->dbbi->insert($table,$insert);
                $insert = [];
            }
            $i++;
        }
        if(!empty($insert)){
            $this->dbbi->insert($table,$insert);
            $insert = [];
        }
        $this->dbbi->end();
        $linkdb->useBuff(true);
    }
}