<?php
namespace model\bi;
/** 取数据
 * Class getData
 * @package model
 */
class getData2 extends \model
{
    public $dbbi;
    public $cache;
    public $cacheTime=3600; //缓存时间
    public $dircfg;
    public $cols;
    public $cfgs;
    public $captions;
	public function __construct()
    {
		$db = new \lib\db(\mvc::$cfg['db']['main']);
		parent::__construct();
		$cacheDir = \mvc::$cfg['dir']['cache'].'/bi';
		mkdirs($cacheDir);
		$cache = new \lib\cache($cacheDir);
		$this->dbbi = new \lib\db(\mvc::$cfg['db']['main']);
		$this->cache = $cache;
		$this->dircfg = \mvc::$cfg['dir']['project'].'/config/bi';
		$cols = require($this->dircfg.'/cols.php');
		$this->cols = $cols;
		$this->captions = [];
	}
	
	public  function getDimension($table,$field,$rules=[])
    {
	    $join['[>]'.$table.'_dimension'] = [$field=>'key','AND'=>"dimension='".$field."'"];
	    $where='';
	    if($rules){
	        $where = $this->dbbi->whereToSql($rules);
	    }
        $sql = "SELECT `{:field}`,`parent_key`,IFNULL(`{:name}`,`{:field}`) as name FROM 
                (SELECT {:field} FROM `{:table}` WHERE 1 {:where} GROUP BY `{:field}`) {:table}
                LEFT OUTER JOIN `{:table}_dimension` ON `{:table}`.`{:field}` = `{:table}_dimension`.`key` AND DIMENSION='{:field}'
                ORDER BY name,`{:field}`";
        $sql = qgprintf($sql,['table'=>$table,'field'=>$field,'name'=>LG_NAME,'where'=>$where]);
        $st = $this->dbbi->query($sql);
        $data = [];
        while($row = $st->fetch()){
            if(!$row['name']) $row['name'] = lg('空');
            $data[] = ['key'=>$row[$field],'value'=>$row['name'],'parent_key'=>$row['parent_key']];
        }
        return $data;
    }
    
	/**
	* 通过caption查key
	*/
    public function getDimensionKeyByCaption($table,$dimension,$caption)
	{
	    $where = [
	        'dimension'=>$dimension,
	        'OR'=>[
	            'cn_name'=>$caption,
	            'en_name'=>$caption
	        ]
	    ];
	    return $this->dbbi->get($table.'_dimension','key',$where);
    }
    
	function getCaption($table,$field,$value){
	    $table = $table.'_dimension';
	    $key = $table.'-'.$field.'-'.$value;
	    if(!isset($this->captions[$key])){
    	    $this->captions[$key] = $this->dbbi->get($table,LG_NAME,['dimension'=>$field,'key'=>$value]);
	    }
	    return $this->captions[$key];
	}

	public function put($rules)
    {
        $cache = null;
        if($rules['cache']){
            unset($rules['cache']);
            $cacheKey = md5(serialize($rules));
            $cache = make(\lib\cache::class,\mvc::$cfg['dir']['cache'].'/bi/'.substr($cacheKey,0,1).substr($cacheKey,-1));
            $data = $cache->get($cacheKey,3600 * 12);
            if($data){ 
                return $data;
            }
        }
        if(isset($rules['cmd'])) $rules = ['default'=>$rules];
        $cmds = ['select','has','count','max','min','avg','sum'];
        $defult = [
            'cmd'=>'select',
            'table'=>'',
            'join'=>[],
            'fields'=>'*',
            'where'=>[],
            'getCaptions'=>[],
        ];
        $data = [];
        foreach($rules as $key=>$item){
            $item = array_merge($defult,$item);
            if(!in_array($item['cmd'],$cmds)){
                dd('操作类型只能是: '.implode(',',$cmds));
            }
            if(isset($item['filterFields'])){
                foreach($item['where'] as $k=>$v){
                    preg_match('/(.*)(\{.*\}|\s)/',$k,$m);
                    $initK = ($m[1]) ? $m[1] : $k;
                    if(!in_array($initK,$item['filterFields'])){
                        unset($item['where'][$k]);
                    }
                }
            }
            if($item['axis']){
                $rows = $this->getRowsByAxis($item);
            }else{
                $rows = $this->getRows($item);
            }
            $data[$key] = $rows;
        }
        if($cache){
            $cache->set($cacheKey,$data);
        }
        return $data;
    }
    
	public function getRowsByAxis($item)
    {
        $order = [$item['axis'][0]=>strtoupper($item['axis'][1])];
        $where = array_merge($item['where'],['GROUP'=>$item['axis'][0],'ORDER'=>$order]);
        if($item['axisLimit']){
            $where = array_merge($where,['LIMIT'=>$item['axisLimit']]);
        }
        $axisRows = $this->dbbi->select($item['table'],$item['join'],$item['axis'][0],$where);
        $dimensions = [$item['axis'][0]];
        $firstDimen = [];
        $source = [];
        foreach ($axisRows as $axis){
            $axisCaption = $axis;
            if(in_array($item['axis'][0],$item['getCaptions'])){
                $axisCaption = $this->getCaption($item['table'],$item['axis'][0],$axis);
            }
            $order = [$item['value'][0]=>strtoupper($item['value'][1])];
            if($item['valueOrder']){
                $order = [$item['valueOrder'][0]=>strtoupper($item['valueOrder'][1])];
            }
            $where = array_merge($item['where'],[
                    $item['axis'][0]=>$axis,
                    'HAVING'=>[$item['value'][0].'[>]'=>0],
                    'GROUP'=>$item['series'][0],
                    'ORDER'=>$order,
                ]
            );
            $seriesRows = $this->dbbi->select($item['table'],$item['join'],$item['fields'],$where);
            $arr = [$item['axis'][0]=>$axisCaption];
            if($item['total']){
                $arr[$item['total']] = 0;
            }
            foreach($seriesRows as $row){
                foreach ($item['getCaptions'] as $field){
                    if(is_string($field) && isset($row[$field])){
                        $row[$field.'Caption'] = $this->getCaption($item['table'],$field,$row[$field]);
                    }
                }
                $arr[$row[$item['series'][0].'Caption']] = is_numeric($row[$item['value'][0]]) ? $row[$item['value'][0]] * 1 : $row[$item['value'][0]];
                if(isset($arr[$item['total']])){
                    $arr[$item['total']] += $row[$item['value'][0]] * 1;
                }
                if(!in_array($row[$item['series'][0].'Caption'],$dimensions)){
                    array_push($dimensions,$row[$item['series'][0].'Caption']);
                    if(!$firstDimen){
                        $firstDimen = $row;
                    }
                }
            }
            array_push($source,$arr);
        }
        if($item['total']){
            array_push($dimensions,$item['total']);
        }
        if($item['table']=='sum_pro_new'){
            //dd($item,$source);
        }
       foreach($source as $i=>&$arr){
            foreach ($dimensions as $dimension){
                if(!isset($arr[$dimension])){
                    $arr[$dimension] = 0;
                }
            }
        }
        return ['dimensions'=>$dimensions,'source'=>$source,'firstDimen'=>$firstDimen];
    }
    
	public function getRows($item)
    {
        $rows = $this->dbbi->cmd($item['cmd'], $item['table'],$item['join'],$item['fields'],$item['where']);
        if($rows && $item['getCaptions'] && is_array($rows)){
            foreach ($rows as &$row){
                foreach ($item['getCaptions'] as $field){
                    if(is_string($field) && isset($row[$field])){
                        $row[$field.'Caption'] = $this->getCaption($item['table'],$field,$row[$field]);
                    }
                }
            }
        }
        if($item['table']=='sum_sale'){
            //dd($this->dbbi->last());
        }
        return $rows;
    }
}