<?php
namespace model\bi;
/**
 * Class io
 * @package model
 */
class bi extends \model
{
    public $bi = null;
    public $saleTable = 'new_saledataall_bi';
	public function __construct()
    {
	    $this->bi = new \lib\db(\mvc::$cfg['db']['main']);
		parent::__construct();
	}

	/**
	  * 
	  */
    function test()
	{
        ;
	}

	/**
	  * 销售数据刷新
	  */
    function new_saledataall_bi()
	{
		set_time_limit(0);
		ignore_user_abort(true);
	    ini_set('memory_limit', \mvc::$cfg['upload']['bigtable_memory_limit']);
	    
	    $db = $this->db;
	    
	    //原始维度
	    $groupKeys = [
	        'custnumber',            //客户代码
            'custname',              //客户名称
            'countryen',             //国家
            'parentproducttype',     //产品线
            'producttype',           //品名
            'territorycn',           //区域中文名
            'province',              //省份
            'city',                  //城市
            'team',                  //Team
            'salesmanager',          //销售人员
        ];
        
	    $groupArr = [];
        foreach($groupKeys as $key){ $groupArr[$key] = [''];}
        $groupArr['sbi_state_main'] = ['YJ','Y0','Y1','Y2','Y3','Y4','Y5','Y6'];
        
	    $sql = sprintf("select * from `%s`",$this->saleTable);
	    $st = $db->query($sql);
	    $yearMonthData = [];
	    $data = [];
	    
	    while($row = $st->fetch()){
	        if(empty(trim($row['orderdate'])) || empty(trim($row['transfernewno']))){
	            continue; //订单日期与GSP型号必须
	        }
	        $item['sbi_outnum'] = formatNum($row['transfernewno']);
	        if($item['sbi_outnum']==''){
	            continue; //GSP型号必须
	        }
	        
	        $item['orderdate'] = $row['orderdate'];
	        list($item['sbi_year'],$item['sbi_month'],$day) = explode('-',date('Y-n-d',strtotime($item['orderdate'])));
	        $item['sbi_year'] *= 1;
	        $item['sbi_month'] *= 1;
	        $item['sbi_qty'] = !empty($row['quantity']) ? $row['quantity'] * 1 : 0;
	        $item['sbi_money'] = !empty($row['amountusd']) ? $row['amountusd'] * 1 : 0;
	        $item['sbi_money_out'] = !empty($row['fcost']) ? $row['fcost'] * 1 : 0;
	        $item['sbi_money_in'] = $item['sbi_money'] - $item['sbi_money_out'];
            $item['sbi_mk_id'] = 0;
            $item['sbi_ms_id'] = 0;
	        
            //年月
            if(!array_key_exists($item['sbi_year'],$yearMonthData)){
                $yearMonthData[$item['sbi_year']][] = $item['sbi_month']; 
            }
            if(!in_array($item['sbi_month'],$yearMonthData[$item['sbi_year']])){
                $yearMonthData[$item['sbi_year']][] = $item['sbi_month']; 
            }
	        
            //分组
	        foreach($groupKeys as $key){
	            if(trim($row[$key])!=''){
	                $idx = array_search(trim($row[$key]),$groupArr[$key]);
	                if($idx===false){
	                    array_push($groupArr[$key],trim($row[$key]));
	                    $idx = count($groupArr[$key])-1;
	                }
	                $item[$key] = $idx;
	            }else{
	                $item[$key] = 0;
	            }
	        }
	        array_push($data,$item);
	    }//while
	    
	    //年月排序    
	    arsort($yearMonthData);
		foreach($yearMonthData as &$months){  sort($months);} 
		$t = $yearMonthData;
		$yearMonthData = [];
		foreach($t as $key=>$months){
		    $yearMonthData[] = ['year'=>$key,'months'=>$months];
		} 
        unset($t);
		
		
		//分组维度排序   
		//foreach($groupKeys as $key){ sort($groupArr[$key]);}  
	    
        $proCache = [];  //产品查询结果缓存
        $modelData = []; //车型结果数据
	    foreach($data as &$item){
            if(!array_key_exists($item['sbi_outnum'],$proCache)){
                //缓存
                $proCache[$item['sbi_outnum']] = [];
                if($proRow = $db->query( sprintf("select * from pro where p_outnum_format='%s' limit 1",$item['sbi_outnum']))->fetch() ){
                    $proCache[$item['sbi_outnum']] = [
                        'sbi_pid'=>$proRow['p_id'],
                        'sbi_cate'=>$proRow['p_cate'],
                        'sbi_state_main'=>$proRow['p_state_main'],
                        'sbi_proyear'=>date('Y',strtotime($proRow['create_time'])),
                        'model'=>[]
                    ];
                    
                    //查车型
                    $sql = sprintf(
                        "SELECT mm_mk_id,mm_ms_id FROM pro_model_link pml
                        JOIN m_model mm ON pml_mm_id = mm_id
                        WHERE pml_p_id=%d
                        GROUP BY mm_ms_id"
                        ,$proRow['p_id']
                    );
                    if($mRows = $db->query( $sql )->fetchAll()){
                        $proCache[$item['sbi_outnum']]['model'] = $mRows;
                        $item['sbi_mk_id'] = $mRows[0]['mm_mk_id'];
                        $item['sbi_ms_id'] = $mRows[0]['mm_ms_id'];
                    }
                }
            }
            
            //基础数据
            foreach($proCache[$item['sbi_outnum']] as $k=>$v){
                if($k!='model'){
                    $item[$k] = $v;
                }
            }
            
            //车型数据
            if($proCache[$item['sbi_outnum']]['model']){
                foreach($proCache[$item['sbi_outnum']]['model'] as $mRow){
                    $mkey = $item['sbi_year'].$item['sbi_month'].$item['sbi_proyear'].'_'.$mRow['mm_mk_id'].'_'.$mRow['mm_ms_id'];
                    if(!array_key_exists($mkey,$modelData)){
                        $modelData[$mkey] = [
                            'sbim_year'=>   $item['sbi_year'],
                            'sbim_month'=>  $item['sbi_month'],
                            'sbim_proyear'=>$item['sbi_proyear'],
                            'sbim_mk_id'=>  $mRow['mm_mk_id'],
                            'sbim_ms_id'=>  $mRow['mm_ms_id'],
                            'sbim_qty'=>0,
                            'sbim_money'=>0,
                            'sbim_money_out'=>0,
                            'sbim_money_in'=>0,
                            'sbim_sku'=>0,
                        ];
                    }
                    $modelData[$mkey]['sbim_qty']       += $item['sbi_qty'];
                    $modelData[$mkey]['sbim_money']     += $item['sbi_money'];
                    $modelData[$mkey]['sbim_money_out'] += $item['sbi_money_out'];
                    $modelData[$mkey]['sbim_money_in']  += $item['sbi_money_in'];
                    $modelData[$mkey]['sbim_sku']++;
                }
            }
            
	    }
        
        $db->begin();
        $db->query('TRUNCATE TABLE bi_sale_base');
        $db->query('TRUNCATE TABLE bi_sale_model');
        
        //插入基础数据
        $insert = [];
        $i=0;
        foreach($data as $item){
            $i++;
            $insert[] = $item;
            if($i % 30 == 0){
                $db->insert('bi_sale_base',$insert);
                $insert=[];
            }
        }
        if(!empty($insert)){
            $db->insert('bi_sale_base',$insert);
        }
        //插入车型数据
        $insert = [];
        $i=0;
        foreach($modelData as $item){
            $i++;
            $insert[] = $item;
            if($i % 100 == 0){
                $db->insert('bi_sale_model',$insert);
                $insert=[];
            }
        }
        if(!empty($insert)){
            $db->insert('bi_sale_model',$insert);
        }
        
        //原始维度组
	    $this->setKV('bi_sale_table_yearmonth',json_encode($yearMonthData));
	    $this->setKV('bi_sale_table_group',json_encode($groupArr));
        $db->end();
	    return true;
    }
    
    //多维度取值
    function getDimensValue($where,$fields,$row2col=[],$groups=[],$having=[],$orders=[],$limit=20)
    {
        
        $map = [];
        $q = [];
        $q['where'] = $this->dimensToWhere($where);
        $q['groups'] = $groups;
        $q['having'] = $this->dimensToWhere($having);
        $q['limit'] = $limit;
        foreach($fields as $field){
            if($field=='sku'){
                $q['fields'][] = "count(DISTINCT sbi_outnum) sku";
            }else{
                $q['fields'][] = qgprintf("sum({1}) sum_{1}",$field);
            }
        }
        
        foreach($q['groups'] as $field){
            $q['fields'][] = $field;
            $q['where'] .= ' AND '. $field . ' > 0 ';
        }
        
        foreach($row2col as $field=>$item){
                if($item['rule']=='sku'){
                    $rule = "count(1) sku";
                }else{
                    $rule = qgprintf("sum({1}) sum_{1}",$field);
                }
                foreach($q['groups'] as $gfield){
                    $item['where'][$gfield] = 'main.'.$gfield;
                }
                $colwhere = $this->dimensToWhere($item['where']);
                $q['fields'][] = trim(qgprintf("
                    (SELECT {1} FROM `bi_sale_base` WHERE {2} LIMIT 1) {3}"
                    ,$rule,$colwhere,$field
                ));
        }
        foreach($orders as $item){
            if(isset($item['values']) && is_array($item['values'])){
                $q['orders'][] = qgprintf("FIELD({1},{2})",$item['field'],implode(',',$item['values']));
            }else{
                $sc = (isset($item['sc']) && !empty(isset($item['sc']))) ? $item['sc'] : 'DESC';
                $q['orders'][] = $item['field'] . ' ' . strtoupper($sc);
            }
        }
        $q['fields'] = !empty($q['fields']) ? implode(',',$q['fields']) : '*';
        $q['where']  = !empty($q['where'])  ? ' WHERE '.$q['where'] : '';
        $q['groups'] = !empty($q['groups']) ? ' GROUP BY '.implode(',',$q['groups']) : '';
        $q['having'] = !empty($q['having']) ? ' HAVING '.$q['having'] : '';
        $q['orders'] = !empty($q['orders']) ? ' ORDER BY '.implode(',',$q['orders']) : '';
        $sql = trim(qgprintf("
            SELECT {:fields} FROM `bi_sale_base` main {:where}{:groups}{:having}{:orders} LIMIT {:limit}"
            ,$q
        ));
        $rows = $this->db->query($sql,$map)->fetchAll();
        return $rows;
    }

	/**
	  * 维度数组转where条件
	  */
    function dimensToWhere($dimens){
        $arr = [];
        foreach($dimens as $k=>$v){
            $le = '=';
            if(strpos($k,'[')>0){
                $t = explode('[',$k);
                $k = ($t[0]);
                $le = rtrim($t[1],']');
            }
            if($k!=='limit'){
                if(is_array($v)){
                    $arr[] = sprintf("`%s` in (%s)",$k,implode(',',$v));
                }else{
                    $arr[] = sprintf("%s %s %s",$k,$le,$v);
                }
            }
        }
        if($arr){
            return implode(' AND ',$arr);
        }else{
            return '';
        }
    }
	/**
	  * 取值
	  */
    function getValue($tag,$dimens)
	{
        if(empty($dimens)) return false;
	    return call_user_func_array([$this,'get'.ucfirst($tag)],['dimens'=>$dimens]);
    }
    
    //SKU
    function getSku($dimens)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        $sql = "select count(DISTINCT sbi_outnum) ct from bi_sale_base where ".$where;
        return $this->db->query($sql,$map)->fetch()['ct'];
    }
    
    //销售额
    function getMoney($dimens)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        $sql = "select sum(sbi_money) sum from bi_sale_base where ".$where;
        return decimal($this->db->query($sql,$map)->fetch()['sum']);
    }
    
    //成本
    function getMoneyOut($dimens)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        $sql = "select sum(sbi_money_out) sum from bi_sale_base where ".$where;
        return decimal($this->db->query($sql,$map)->fetch()['sum']);
    }
    
    //利润
    function getMoneyIn($dimens)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        $sql = "select sum(sbi_money_in) sum from bi_sale_base where ".$where;
        return decimal($this->db->query($sql,$map)->fetch()['sum']);
    }
    
    //利润率
    function getMoneyInScale($dimens)
    {
        $money = $this->getMoney($dimens);
        $moneyIn = $this->getMoneyIn($dimens);
        if($money==0 || $moneyIn==0){
            return '';
        }
        return decimal($moneyIn / $money * 100) . '%';
    }
    
    //产品线 Sku TOP
    function getCateTopSku($dimens,$limit=5)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        if(isset($dimens['limit'])) $limit = $dimens['limit'];
        $sql = "SELECT  COUNT(DISTINCT sbi_outnum) ct,parentproducttype FROM `bi_sale_base` WHERE ".$where." GROUP BY parentproducttype ORDER BY ct DESC LIMIT ".$limit;
        $rows = $this->db->query($sql,$map)->fetchAll();
        $topCount = 0;
        foreach($rows as &$row){
            $row['ct'] *= 1;
            $topCount += $row['ct'];
        }
        usort($rows,function($a,$b){
            return $a['ct']-$b['ct'];
        });        
        $allCount = $this->getSku($dimens);
        $rows[] = ['ct'=> ($allCount- $topCount), 'parentproducttype'=>'-1'];
        return $rows;
    }
    
    //产品线 销售额 TOP
    function getCateTopMoney($dimens,$limit=5)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        if(isset($dimens['limit'])) $limit = $dimens['limit'];
        $sql = "SELECT  sum(sbi_money) ct,parentproducttype FROM `bi_sale_base` WHERE ".$where." GROUP BY parentproducttype ORDER BY ct DESC LIMIT ".$limit;
        $rows = $this->db->query($sql,$map)->fetchAll();
        $topCount = 0;
        foreach($rows as &$row){
            $row['ct'] *= 1;
            $topCount += $row['ct'];
            $row['ct'] = decimal($row['ct']);
        }
        usort($rows,function($a,$b){
            return $a['ct']-$b['ct'];
        });        
        $allCount = $this->getMoney($dimens);
        $rows[] = ['ct'=> decimal($allCount- $topCount), 'parentproducttype'=>'-1'];
        return $rows;
    }
    
    //产品线 利润占比 TOP
    function getCateTopMoneyInScaleInAll($dimens,$limit=5)
    {
        $map = [];
        $where = $this->dimensToWhere($dimens);
        if(isset($dimens['limit'])) $limit = $dimens['limit'];
        $sql = "SELECT  sum(sbi_money_in) ct,parentproducttype FROM `bi_sale_base` WHERE ".$where." GROUP BY parentproducttype ORDER BY ct DESC LIMIT ".$limit;
        $rows = $this->db->query($sql,$map)->fetchAll();
        $topCount = 0;
        $allCount = $this->getMoneyIn($dimens);
        foreach($rows as &$row){
            $row['ct'] *= 1;
            $row['ct'] = decimal($row['ct'] / $allCount * 100);
            $topCount += $row['ct'];
        }
        usort($rows,function($a,$b){
            return $a['ct']-$b['ct'];
        });        
        $rows[] = ['ct'=> decimal(100 - $topCount), 'parentproducttype'=>'-1'];
        return $rows;
    }
    
    //国家
    function getCountry($dimens,$limit=20)
    {
        
        $map = [];
        $where = $this->dimensToWhere($dimens);
        if(isset($dimens['limit'])) $limit = $dimens['limit'];
        $sql = "SELECT  count(DISTINCT sbi_outnum) ct,sum(sbi_money) money,sum(sbi_money_in) money_in,countryen FROM `bi_sale_base` WHERE countryen>0 AND ".$where." GROUP BY countryen ORDER BY ct DESC LIMIT ".$limit;
        $rows = $this->db->query($sql,$map)->fetchAll();
        $allMoneyIn = $this->getMoneyIn($dimens);
        foreach($rows as &$row){
            $row['money_in_scale'] = decimal($row['money_in'] / $allMoneyIn * 100);
            $row['money'] = decimal($row['money']); 
            $row['money_in'] = decimal($row['money_in']); 
       }
        
        usort($rows,function($a,$b){
            return $a['money']-$b['money'];
        });
        
        return $rows;
    }
    
    //客户
    function getCust($dimens,$limit=20)
    {
        
        $map = [];
        $where = $this->dimensToWhere($dimens);
        if(isset($dimens['limit'])) $limit = $dimens['limit'];
        $sql = "SELECT  count(DISTINCT sbi_outnum) ct,sum(sbi_money) money,sum(sbi_money_in) money_in,custnumber FROM `bi_sale_base` WHERE custnumber>0 AND ".$where." GROUP BY custnumber ORDER BY ct DESC LIMIT ".$limit;
        $rows = $this->db->query($sql,$map)->fetchAll();
        $allMoneyIn = $this->getMoneyIn($dimens);
        foreach($rows as &$row){
            $row['money_in_scale'] = decimal($row['money_in'] / $allMoneyIn * 100);
            $row['money'] = decimal($row['money']); 
            $row['money_in'] = decimal($row['money_in']); 
       }
        
        usort($rows,function($a,$b){
            return $a['money']-$b['money'];
        });
        
        return $rows;
    }
    
    //订单 rules=wordtype:outnum;word:634171
    function getOrder($rules, $where='',$page=0,$pagesize=0,$sidx='',$sord='asc',$orderby='deliverydate desc')
    {
        $table = $this->saleTable.'(e)';
        $this->fields = ['e.*'];
        $this->join = [];
		$this->joinWhere = [];
		$this->group = [];
		$where = $this->filter2medoo($where,function(&$field,&$op,&$data){
		});
		if($where==null) $where = [];
		if(!empty($this->joinWhere)){
			$where = array_merge($where, $this->joinWhere);
		}
		if(!empty($this->group)){
			$where = array_merge($where, $this->group);
		}
		if(isset($rules['innum']) && $rules['innum']!=''){
            $rules['wordtype'] = 'innum';
            $rules['word'] = $rules['innum'];
		}
		if(empty($rules['word'])) $where['RAW'] = raw('1=0');
		$word = trim($rules['word']);
		switch($rules['wordtype'])
		{
			case 'outnum': $where['transfernewno']= $word; break;
			case 'innum': $where['productshortnumber']= $word; break;
			case 'mapnumber': $where['mapnumber']= $word; break;
			case 'and': 
				$where['OR'] = [
					'transfernewno'=>trim($rules['word']),
					'productshortnumber'=>trim($rules['word2']),
					//'mapnumber'=>trim($rules['word2']),
				];
				break;
			case 'any': 
				$where['OR'] = [
					'transfernewno'=>$word,
					'productshortnumber'=>$word,
					'mapnumber'=>$word,
				];
			break;
			default: 
				$where['RAW'] = raw('1=0');
			break;
		}
        $where = $this->listWhere($where,$page,$pagesize,$sidx,$sord,$orderby);

		if($page>0 && $pagesize>0){
            if(empty($this->join)){
                $pages = $this->paging($table,$where,$page,$pagesize);
            }else{
                $pages = $this->paging($table,$this->join,'*',$where,$page,$pagesize);
            }
			$where["LIMIT"] = [$pages['limit'],$pagesize];
		}
        if(empty($this->join)){
            $rows = $this->db->select($table,$this->fields,$where);
        }else{
            $rows = $this->db->select($table,$this->join,$this->fields,$where);
        }

		//dd($this->db->last());
		
        if(!empty($rows)) foreach($rows as $k=>&$row){
            $row['captions'] = [];
            $row['quantity'] = round($row['quantity']);
        }

		$data = [
			'page'=> $page,
			'total'=> $pages['total'],
			'records' => $pages['records'],
			'rows' => $rows
		];
		return $data;

    }


	/**
	  * @method 获取选择器的数据
	  * @param  $table  string  [必填] factory | make
	  * @param  $req  []
	  * @author soul
	  * @return id 或者 null
	  */
	public function getSelect($table, $req)
	{
		$cofArr = [
			'table'=>$table,
			'key'=>'key',
			'fileds'=>[LG_NAME.'(name)','key(id)'],
			'order'=>[LG_NAME]
		];
		$where['dimension'] = $req['dimension'];
		if($ids){
			$where = array_merge($where,[$cofAllArr[$table]['key']=>$ids]);
		}
		if(isset($cofAllArr[$table]['where'])){
			$where = array_merge($where,$cofAllArr[$table]['where']);
		}
		$where['ORDER'] = $cofArr['order'];
		if(isset($cofArr['group']) && !empty($cofArr['group'])){
    		$where['GROUP'] = $cofArr['group'];
		}
		return $this->bi->select($cofArr['table'], $cofArr['fileds'], $where);
	}


	/**
	  * @method
	  * @author soul
	  */
	public function getMsTopList($params)
	{
		$this->db = $this->bi;
		$filters = $params['filters'];
		$page = $params['page'];
		$pagesize = $params['rows'];
		$sidx = empty($params['sidx'])? '': $params['sidx'];
		$sord = empty($params['sord'])? 'asc': $params['sord'];
		$orderby = $params['orderby'];

		$where = sprintf(" and vio_fac = '%s'", $params['vio_fac']);
		$comsql = "SELECT %s FROM (
			SELECT vio_make,vio_model_range, SUM(vio_qty) vio_qty FROM (
				SELECT vio_make,vio_model_range, vio_qty FROM sum_vio WHERE 1 %s GROUP BY vio_type,vio_typeid,vio_make,vio_model_range
			) AS a GROUP BY vio_make,vio_model_range ORDER BY SUM(vio_qty) DESC
		) AS b,(SELECT @rowNum:=0) c";

		$sql = sprintf($comsql, 'count(*) count', $where);
		$row = $this->db->query($sql)->fetch();
		$total = $row['count'];
		
		$sql = sprintf("select * from (".$comsql.") as d limit %d, %d", ' @rowNum:=@rowNum+1 vio_top, b.*', $where, ($page-1)*$pagesize, $pagesize);
		$rows = $this->db->query($sql)->fetchAll();
		$data = [
			'page'=> $page,
			'total'=> ceil($total/$pagesize),
			'records' => $total,
			'rows' => $rows
		];
		return $data;
	}
}