<?php
/**
 * 产品型号汇总
 */
$sql = <<<EOFSQL
SELECT
parent_id bigcate,
p_cate cate,
p_state_main state,
IFNULL(region_s,0) region_s,
IFNULL(mm_mk_id,0) make,
IFNULL(mms_source,0) mms_source,
IFNULL(oe.ckresult2,0) oeresult,
IFNULL(ppic_type,'0') ppic_type,
IF(p_lineimg='' OR p_lineimg IS NULL,0,1) lineimg,
IF(ppv_p_id IS NULL,0,1) param,
IF(pp_id IS NULL,0,1) pack,
p_id sku
FROM gspcatalog.pro
JOIN gspcatalog.pro_cate cate ON cate.id = p_cate AND cate.is_moreline = 0
LEFT OUTER JOIN gspcatalog.pro_model_link ON pml_p_id = p_id
LEFT OUTER JOIN gspcatalog.m_model mm ON mm_id = pml_mm_id
LEFT OUTER JOIN gspcatalog.m_make ON mk_id = mm_mk_id
LEFT OUTER JOIN gspcatalog.m_model_source ON mms_mm_id = mm_id
LEFT OUTER JOIN gspcatalog.pro_numidx oe ON oe.pn_pf_id = 1 AND oe.pn_p_id = p_id
LEFT OUTER JOIN gspcatalog.pro_pic ON ppic_p_id = p_id AND ppic_type IN ('init','beautify')
LEFT OUTER JOIN gspcatalog.pro_pack ON p_id=pp_id
LEFT OUTER JOIN (SELECT DISTINCT ppv_p_id FROM gspcatalog.gsp_pro_param_val) param_val ON p_id=ppv_p_id
EOFSQL;
//GROUP BY cate,state,make,mms_source,oeresult,ppic_type,sku

return [
    'enable'=> true, //是否启用
    'table'=>'sum_pro',//表名
    'createTable'=>true,//是否创建表
    'dropTable'=>true,//创建表时是否删除表
    //'truncateTable'=>true,//写入数据是是否清空表
    'fields'=>[
        'bigcate'=> $cols['bigcate']['DDL'],
        'cate'=> $cols['cate']['DDL'],
        'state'=> $cols['state']['DDL'],
        'region_s'=> $cols['region_s']['DDL'],
        'make'=> $cols['make']['DDL'],
        'mms_source'=> $cols['mms_source']['DDL'],
        'oeresult'=> $cols['oeresult']['DDL'],
        'ppic_type'=> $cols['ppic_type']['DDL'],
        'lineimg'=> $cols['lineimg']['DDL'],
        'param'=> $cols['param']['DDL'],
        'pack'=> $cols['pack']['DDL'],
        'sku'=> $cols['sku']['DDL'],
    ],
    'keys'=>['bigcate','cate','state','region_s','make','mms_source','oeresult','ppic_type','sku'], //主键
    'indexs'=>[], //索引, ['aa',['b','c']]
    //指定表引擎与字符集,默认 InnoDB latin1 latin1_bin
    //'tableEnd'=>["ENGINE" => "InnoDB","DEFAULT CHARSET" => 'utf8mb4',"COLLATE" => 'utf8mb4_unicode_ci']
    //压入数据
    'pushData'=>[
        //默认值
        /*
        'default'=>[
            'year'=>date('Y'),
            'month'=>date('m'),
            'yearmonth'=>date('Ym')
        ],*/
         //插入数据不能有此处指定列的值,如果有整体操作放弃
        //'not'=>['yearmonth'=>date('Ym')],
         //数据使用执行文件 /config/bi/php/{$key}.php
        'php'=>false,
         //数据使用SQL 优先 php
        'sql'=>$sql,
    ]
];