game-sdk/simplewind/huo/logic/abs/AbsLogic.php

<?php
/**
 * AbsLogic.php  UTF-8
 * huosdk_v8
 *
 * @date    : 2018/7/31 23:05
 *
 * @license 这不是一个自由软件，未经授权不许任何使用和传播。
 * @author  : guxiannong<gxn@huosdk.com>
 * @version : HUSDK 8.0
 */

namespace huo\logic\abs;

use huo\model\common\CommonModel;
use think\Log;

class AbsLogic extends CommonModel {
    public function getSubAgentList(
       $where, $is_today, $platform_id, $start, $end, $app_id, $agent_id, $plan_id, $landing_page_id, $son_agent_id, $page,
        $list_rows
    ) {

    }

    public function subAgent() {
        $this->genUsers();
        $this->_game();
        if (3 == $this->role_type) {
            $this->where = " pp.owner_id".$this->agentwhere;
        } else {
//            $this->where = " 1 ";
            $_agent_ids = implode(',', $this->buy_agents);
            $this->where = " pp.owner_id in (".$_agent_ids.") ";
        }
        $this->_getSubAgent();
        $this->display();
    }

    public function _getSubAgent() {
        $_is_today = I('is_today', '今日');
        if ('今日' == $_is_today) {
            $this->_initTodayData();
            $_day_model = M('tmp_day_promotion_game');
            $_table = 'c_tmp_day_promotion_game';
        } else {
            $_day_model = M('day_promotion_game');
            $_table = 'c_day_promotion_game';
        }
        $platform_id = I('platform_id');
        $start = I('start_time');
        $end = I('end_time');
        $app_id = I('app_id');
        $agent_id = I('agent_id/d', 0);
        $son_agent_id = I('son_agent_id/d', 0);
        $plan_id = I('plan_id/d', '');
        $material_id = I('material_id/d', '');
        $landing_page_id = I('landing_page_id/d', '');
        /* 导出参数 */
        $_csv_do = I('csv_do', 1);
        $_csv_page = I('csv_page', 1);
        $_csv_offset = I('csv_offset', 1000);
        $_csv_file = I('csv_file', '');
        $_explore_csv_class = new \Huosdk\Data\ExportCsv();
        $where = $this->where;
        $sum_where = [];
        if (isset($platform_id) && !empty($platform_id)) {
            $where .= " AND pp.platform_id = '".$platform_id."'";
        }
        if (isset($start) && !empty($start)) {
            $where .= " AND d.date >= '".$start."'";
        }
        if (isset($end) && !empty($end)) {
            $where .= " AND d.date <= '".$end."'";
        }
        if (isset($agent_id) && !empty($agent_id)) {
            $where .= " AND pp.owner_id=".$agent_id;
        }
        if (isset($son_agent_id) && !empty($son_agent_id)) {
            $where .= " AND pd.agent_id=".$son_agent_id;
        }
        if (isset($plan_id) && !empty($plan_id)) {
            $where .= " AND pd.promotion_plan_id=".$plan_id;
        }
        if (isset($material_id) && !empty($material_id)) {
            $landing_page_ids = M('game_landing_page')->where(['material_id' => $material_id])->getField('id', true);
            $idstr = implode(',', $landing_page_ids);
            $where .= " AND glp.id in ('".$idstr."') ";
        }
        if (isset($app_id) && !empty($app_id)) {
            $where .= " AND d.app_id=".$app_id;
        }
        if (isset($landing_page_id) && !empty($landing_page_id)) {
            $where .= " AND glp.id = '".$landing_page_id."'";
        }
        $count_sql
            = "SELECT count(*) as tp_count  FROM (SELECT d.id FROM {$_table} d 
                      LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id and pd.app_id=d.app_id
                      LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id
                      LEFT JOIN ".C('DB_PREFIX')."game_landing_page glp ON glp.id=pd.landing_page_id
                      WHERE {$where} GROUP BY d.date,d.app_id,pp.owner_id,pp.platform_id) taba";
        $count_info = M()->query($count_sql);
        $count = isset($count_info[0]['tp_count']) ? $count_info[0]['tp_count'] : 0;
//        $count = $_day_model
//            ->alias('d')
//            ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id")
//            ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
//            ->join("LEFT JOIN ".C('DB_PREFIX')."game_landing_page glp ON glp.id=pd.landing_page_id")
//            ->where($where)
//            ->count();
        if (2 == $_csv_do) {
//            $_fields = 'd.*,pd.promotion_plan_id,pd.landing_page_id,pp.platform_id,pp.owner_id';
            $_fields
                = "d.date,d.app_id,pp.platform_id,d.agent_id,sum(d.reg_cnt) reg_cnt,sum(d.reg_device) reg_device,sum(d.reg_pay_cnt) reg_pay_cnt,";
            $_fields .= "sum(d.sum_reg_money) sum_reg_money,sum(d.user_cnt) user_cnt,sum(d.pay_user_cnt) pay_user_cnt,";
            $_fields .= "sum(d.sum_money) sum_money,pp.owner_id,";
            $_fields .= 'sum(d.day2) day2,sum(d.day7) day7,sum(d.day15) day15';
            $items = $_day_model
                ->alias('d')
                ->field($_fields)
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id  and pd.app_id=d.app_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."game_landing_page glp ON glp.id=pd.landing_page_id")
                ->where($where)
                ->order('d.id DESC')
                ->page($_csv_page, $_csv_offset)
                ->group('d.date,d.app_id,pp.owner_id,pp.platform_id')
                ->select();
            if (!empty($items) && is_array($items)) {
                $_title = '投放数据';
                $_expCellName = array('日期', '买量专员', '投放渠道', '游戏名称', '点击数量', '下载数量', '注册', '新增设备', '新增付费数', '新增付费率',
                                      '新增付费金额', '活跃', '付费人数', '付费金额', '活跃付费率', '活跃arpu', '付费arpu', 'ltv 1日', 'ltv 7日',
                                      'ltv 15日', '留存 1日', '留存 7日', '留存 15日'
                );
                $_export_data = [];
                $agents = M('users')->where(
                    array('id' => array('in', implode(',', array_unique(array_column($items, 'agent_id')))))
                )->getField("id,user_login agentname", true);
                $games = M('game')->where(
                    array('id' => array('in', implode(',', array_unique(array_column($items, 'app_id')))))
                )->getField('id,name', true);
                $platforms = M('platform')->getField("id,name platform", true);
                foreach ($items as $k => $v) {
                    $_where = " `date`='".$v['date']."' AND `agent_id`='".$v['agent_id']."' AND `app_id`='".$v['app_id']
                              ."'";
                    $_ltv = $this->getLtvData($_where);
                    $_day_map = [
                        'agent_id' => $v['agent_id'],
                        'date'     => $v['date'],
                        'app_id'   => $v['app_id']
                    ];
                    //$_day_log = $this->getPromotionDayLog($_day_map);
                    $_day_log = $this->getPromotionByowner($v['date'],$v['owner_id'],$v['app_id'],$v['platform_id']);
                    $_v = [];
                    $_v['date'] = $v['date'];
                    $_v['agent_id'] = (isset($agents[$v['owner_id']]) ? $agents[$v['owner_id']] : '官包')."\t";
                    $_v['platform'] = $platforms[$v['platform_id']];
                    $_v['game'] = $games[$v['app_id']];
                    $_v['check_cnt'] = $_day_log['distinct_visit_cnt'];
                    $_v['down_cnt'] = $_day_log['distinct_down_cnt'];
                    $_v['reg_cnt'] = $v['reg_cnt'];
                    $_v['reg_device'] = $v['reg_device'];
                    $_v['reg_pay_cnt'] = $v['reg_pay_cnt'];
                    $_v['reg_pay_rate'] = (number_format($v['reg_pay_cnt'] / $v['reg_cnt'], 2, '.', '') * 100).'%';
                    $_v['sum_reg_money'] = $v['sum_reg_money'];
                    $_v['user_cnt'] = $v['user_cnt'];
                    $_v['pay_user_cnt'] = $v['pay_user_cnt'];
                    $_v['sum_money'] = $v['sum_money'];
                    $_v['active_pay_rate'] = (number_format($v['pay_user_cnt'] / $v['user_cnt'], 2, '.', '') * 100).'%';
                    $_v['active_arpu'] = number_format($v['sum_money'] / $v['user_cnt'], 2, '.', '');
                    $_v['pay_arpu'] = number_format($v['sum_money'] / $v['pay_user_cnt'], 2, '.', '');
                    $_v['ltv1'] = isset($_ltv[0]['day1']) ? $_ltv[0]['day1'] : 0;
                    $_v['ltv7'] = isset($_ltv[0]['day7']) ? $_ltv[0]['day7'] : 0;
                    $_v['ltv15'] = isset($_ltv[0]['day15']) ? $_ltv[0]['day15'] : 0;
                    $_v['day2'] = $this->_showRegPoint($v['day2'], $v['reg_cnt']);
                    $_v['day7'] = $this->_showRegPoint($v['day7'], $v['reg_cnt']);
                    $_v['day15'] = $this->_showRegPoint($v['day15'], $v['reg_cnt']);
                    $_export_data[] = $_v;
                }
                $_explore_csv_class->exportCsv(
                    $_title, $_expCellName, $_export_data, $_csv_file, $count, $_csv_page, $_csv_offset
                );
            } else {
                $_arr['error'] = 0;
                $_arr['msg'] = '';
                $_arr['data'] = '';
                $this->ajaxReturn($_arr);
            }
        } else {
            $rows = isset($_POST['rows']) ? intval($_POST['rows']) : $this->abs_row;
            $page = $this->page($count, $rows);
//            $_fields = 'd.*,pd.promotion_plan_id,pd.landing_page_id,pp.platform_id,pp.owner_id';
            $_fields
                = "d.date,d.app_id,pp.platform_id,d.agent_id,sum(d.reg_cnt) reg_cnt,sum(d.reg_device) reg_device,sum(d.reg_pay_cnt) reg_pay_cnt,";
            $_fields .= "sum(d.sum_reg_money) sum_reg_money,sum(d.user_cnt) user_cnt,sum(d.pay_user_cnt) pay_user_cnt,";
            $_fields .= "sum(d.sum_money) sum_money,pp.owner_id,";
            $_fields .= 'sum(d.day2) day2,sum(d.day7) day7,sum(d.day15) day15';
            $items = $_day_model
                ->alias('d')
                ->field($_fields)
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id and pd.app_id=d.app_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."game_landing_page glp ON glp.id=pd.landing_page_id")
                ->where($where)
                ->order('d.id DESC')
                ->limit($page->firstRow.','.$page->listRows)
                ->group('d.date,d.app_id,pp.owner_id,pp.platform_id')
                ->select();
            $_check_cnt = $_down_cnt = 0;
            $_ltv_agent_ids = [];
            foreach ($items as $_k => $_v) {
                $_ltv_agent_ids[] = $_v['agent_id'];
                $_where = " `date`='".$_v['date']."' AND `agent_id`='".$_v['agent_id']."' AND `app_id`='".$_v['app_id']
                          ."'";
                $_ltv = $this->getLtvData($_where);
                $items[$_k]['ltv'] = $_ltv[0];
                $_day_map = [
                    'agent_id' => $_v['agent_id'],
                    'date'     => $_v['date'],
                    'app_id'   => $_v['app_id']
                ];
                //$_day_log = $this->getPromotionDayLog($_day_map);
                $_day_log = $this->getPromotionByowner($_v['date'],$_v['owner_id'],$_v['app_id'],$_v['platform_id']);
                $items[$_k]['check_cnt'] = $_day_log['distinct_visit_cnt'];
                $items[$_k]['down_cnt'] = $_day_log['distinct_down_cnt'];
                $_check_cnt += $_day_log['distinct_visit_cnt'];
                $_down_cnt += $_day_log['distinct_down_cnt'];
                $items[$_k]['day2'] = $this->_showRegPoint($_v['day2'], $_v['reg_cnt']);
                $items[$_k]['day7'] = $this->_showRegPoint($_v['day7'], $_v['reg_cnt']);
                $items[$_k]['day15'] = $this->_showRegPoint($_v['day15'], $_v['reg_cnt']);
            }
            /* 汇总数据 */
            $_sum_field
                = "sum(d.reg_cnt) reg_cnt,sum(d.reg_device) reg_device,sum(d.reg_pay_cnt) reg_pay_cnt,
                   sum(d.sum_reg_money) sum_reg_money,sum(d.user_cnt) user_cnt,sum(d.pay_user_cnt) pay_user_cnt,
                   sum(d.sum_money) sum_money,sum(d.day2) day2,sum(d.day7) day7,sum(d.day15) day15,sum(d.check_cnt) check_cnt,
                   sum(d.down_cnt) down_cnt,sum(reg_device) reg_device
                   ";
            $_sum_items = $_day_model
                ->alias('d')
                ->field($_sum_field)
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id and pd.app_id=d.app_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."game_landing_page glp ON glp.id=pd.landing_page_id")
                ->where($where)
                ->select();
            $_prom_data = M("promotion_day")
                ->alias('d')
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id and pd.app_id=d.app_id")
                ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
                ->where($where)
                ->field("sum(`distinct_visit_cnt`) distinct_visit_cnt,sum(`distinct_down_cnt`) distinct_down_cnt")
                ->select();
            $_sum_items[0]['check_cnt'] = $_prom_data[0]['distinct_visit_cnt'];
            $_sum_items[0]['down_cnt'] = $_prom_data[0]['distinct_down_cnt'];
            //$_sum_items[0]['check_cnt'] = $_check_cnt;
            //$_sum_items[0]['down_cnt'] = $_down_cnt;
            if (3 == $this->role_type) {
                $_where = " agent_id".$this->agentwhere;
            } else {
                if (!empty($_ltv_agent_ids)) {
                    $_agent_ids = implode(',', $_ltv_agent_ids);
                    $_where = " agent_id in (".$_agent_ids.") ";
                } else {
                    $_where = " 1 ";
                }
            }
            /*if ('今日' == $_is_today) {
                $_where .= " and `date`='".date('Y-m-d')."'";
            }*/
            if ('今日' == $_is_today) {
                $_where .= " and `date`='".date('Y-m-d')."'";
                $where .= " and d.`date`='".date('Y-m-d')."'";
                $_prom_data = M("promotion_day")
                    ->alias('d')
                    ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_detail pd ON pd.agent_id=d.agent_id and pd.app_id=d.app_id")
                    ->join("LEFT JOIN ".C('DB_PREFIX')."promotion_plan pp ON pp.id=pd.promotion_plan_id")
                    ->where($where)
                    ->field("sum(`distinct_visit_cnt`) distinct_visit_cnt,sum(`distinct_down_cnt`) distinct_down_cnt")
                    ->select();
                $_sum_items[0]['check_cnt'] = !empty($_prom_data[0]['distinct_visit_cnt']) ? $_prom_data[0]['distinct_visit_cnt'] : 0;
                $_sum_items[0]['down_cnt'] = !empty($_prom_data[0]['distinct_down_cnt']) ? $_prom_data[0]['distinct_down_cnt'] : 0;
            }
            $_ltv = $this->getLtvData($_where);
            $_sum_items[0]['ltv'] = $_ltv[0];
            $_sum_items[0]['day2'] = $this->_showRegPoint($_sum_items[0]['day2'], $_sum_items[0]['reg_cnt']);
            $_sum_items[0]['day7'] = $this->_showRegPoint($_sum_items[0]['day7'], $_sum_items[0]['reg_cnt']);
            $_sum_items[0]['day15'] = $this->_showRegPoint($_sum_items[0]['day15'], $_sum_items[0]['reg_cnt']);
            $_export_button = $_explore_csv_class->exportButton('数据导出', 'input', 1000, 'btn btn-primary');
            $this->assign("export_button", $_export_button);
            $this->assign("pays", $items);
            $this->assign($_GET);
            $this->assign("sum_items", $_sum_items);
            $this->assign("Page", $page->show('Admin'));
        }
    }


    /**
     * 初始化实时数据
     */
    public function _initTodayData() {
        $this->today_date = date('Y-m-d');
        $this->today_start_time = strtotime($this->today_date);
        $this->today_end_time = time();
        $_agents = Db::name('user')->where(['role_id' => ['in', [24, 241]]])->column('id', 'id');
        if (!empty($_agents)) {
            $_agent_ids = implode(',', $_agents);
            $_sql
                = "
            TRUNCATE h_tmp_promotion_logindata;
            ALTER TABLE h_tmp_promotion_logindata DISABLE KEYS;
            INSERT INTO h_tmp_promotion_logindata
            (
                `mem_id`,
                `app_id`,
                `login_date`,
                `reg_date`,
                `login_cnt`,
                `agent_id`,
                `imei`,
                `imei_cnt`,
                `regdays`
            )
            SELECT
                `ll`.`mem_id` AS `mem_id`,
                `ll`.`app_id` AS `app_id`,
                DATE_FORMAT(FROM_UNIXTIME(`ll`.`login_time`),'%Y-%m-%d') AS `login_date`,
                DATE_FORMAT(FROM_UNIXTIME(IFNULL(`m`.`reg_time`,0)),'%Y-%m-%d') AS `reg_date`,
                COUNT(`ll`.`id`) AS `login_cnt`,
                IFNULL(`m`.`agent_id` , '0') AS `agent_id`, 
                `ll`.`imei` AS `imei`,
                COUNT(DISTINCT(`ll`.`imei`)) AS `imei_cnt`,
                (TO_DAYS(FROM_UNIXTIME(`ll`.`login_time`)) - TO_DAYS(FROM_UNIXTIME(IFNULL(`m`.`reg_time`,0)))) AS `regdays`
            FROM
                (`c_login_log` `ll`)
                LEFT JOIN `c_members` m ON ll.mem_id = m.id
            WHERE
                (`ll`.`login_time` >= ".$this->today_start_time." AND `ll`.`login_time` < ".$this->today_end_time
                  ." AND `ll`.`agent_id` in (".$_agent_ids.") AND (`ll`.`app_id` > 0)) AND (`ll`.`app_id` = `m`.`app_id`)
            GROUP BY `ll`.`mem_id` , `ll`.`app_id`;
            ALTER TABLE c_tmp_promotion_logindata ENABLE KEYS;
            
            
            TRUNCATE c_tmp_promotion_paydata;
            ALTER TABLE c_tmp_promotion_paydata DISABLE KEYS;
            INSERT INTO c_tmp_promotion_paydata
            (
                `mem_id`,
                `app_id`,
                `agent_id`,
                `login_date`,
                `reg_date`,
                `order_cnt`,
                `sum_money`,
                `imei`,
                `imei_cnt`,
                `regdays`
            )
            SELECT
                `p`.`mem_id` AS `mem_id`,
                `p`.`app_id` AS `app_id`,
                IFNULL(`m`.`agent_id` , '0') AS `agent_id`, 
                DATE_FORMAT(FROM_UNIXTIME(`p`.`create_time`),'%Y-%m-%d') AS `login_date`,
                DATE_FORMAT(FROM_UNIXTIME(IFNULL(`m`.`reg_time`,0)),'%Y-%m-%d') AS `reg_date`,
                COUNT(`p`.`id`) AS `order_cnt`,
                SUM(`p`.`amount`) AS `sum_money`,
                `m`.`imei` AS `imei`,
                COUNT(DISTINCT(`m`.`imei`)) AS `imei_cnt`,
                (TO_DAYS(FROM_UNIXTIME(`p`.`create_time`)) - TO_DAYS(FROM_UNIXTIME(IFNULL(`m`.`reg_time`,0)))) AS `regdays`
            FROM
                `c_pay` p
                LEFT JOIN `c_members` m ON p.mem_id = m.id
            WHERE
                ((`p`.`status` = 2)
                AND `p`.`create_time` >= ".$this->today_start_time."
                AND `p`.`create_time` < ".$this->today_end_time."
                AND `m`.`agent_id` in (".$_agent_ids.")
                )
            GROUP BY `p`.`mem_id` , `p`.`app_id`;
            ALTER TABLE c_tmp_promotion_paydata ENABLE KEYS;
            
            
            TRUNCATE c_tmp_promotion_daypayuser;
            ALTER TABLE c_tmp_promotion_daypayuser DISABLE KEYS;
            INSERT INTO c_tmp_promotion_daypayuser
            (
                `login_date`,
                `reg_date`,
                `mem_id`,
                `app_id`,
                `agent_id`,
                `sum_money`,
                `order_cnt`,
                `login_cnt`,
                `imei`,
                `imei_cnt`,
                `regdays`
            )
            SELECT
                `a`.`login_date` AS `login_date`,
                IFNULL(`a`.`reg_date`, '1970-01-01') AS `reg_date`,
                `a`.`mem_id` AS `mem_id`,
                `a`.`app_id` AS `app_id`,
                IFNULL(`a`.`agent_id`, 0) AS `agent_id`,
                IFNULL(`b`.`sum_money`, 0) AS `sum_money`,
                IFNULL(`b`.`order_cnt`, 0) AS `order_cnt`,
                `a`.`login_cnt` AS `login_cnt`,
                `a`.`imei` AS `imei`,
                `a`.`imei_cnt` AS `imei_cnt`,
                `a`.`regdays` AS `regdays`
            FROM
                (`c_tmp_promotion_logindata` `a`
                LEFT JOIN `c_tmp_promotion_paydata` `b` ON (((`a`.`login_date` = `b`.`login_date`)
                    AND (`a`.`mem_id` = `b`.`mem_id`)
                    AND (`a`.`app_id` = `b`.`app_id`))))
            WHERE
                (`a`.`login_date` = '".$this->today_date."')
            UNION SELECT
                `b`.`login_date` AS `login_date`,
                IFNULL(`b`.`reg_date`, '1970-01-01') AS `reg_date`,
                `b`.`mem_id` AS `mem_id`,
                `b`.`app_id` AS `app_id`,
                IFNULL(`b`.`agent_id`, 0) AS `agent_id`,
                `b`.`sum_money` AS `sum_money`,
                `b`.`order_cnt` AS `order_cnt`,
                IFNULL(`a`.`login_cnt`, 0) AS `login_cnt`,
                IFNULL(`b`.`imei`, '000000000000000') AS `imei`,
                IFNULL(`b`.`imei_cnt`, 0) AS `imei_cnt`,
                IFNULL(`b`.`regdays`, 100000) AS `regdays`
            FROM
                (`c_tmp_promotion_paydata` `b`
                LEFT JOIN `c_tmp_promotion_logindata` `a` ON (((`a`.`login_date` = `b`.`login_date`)
                    AND (`a`.`mem_id` = `b`.`mem_id`)
                    AND (`a`.`app_id` = `b`.`app_id`))))
            WHERE
                `b`.`login_date` = '".$this->today_date."';
            ALTER TABLE c_tmp_promotion_daypayuser ENABLE KEYS;
            
            
            TRUNCATE c_tmp_promotion_dayagentgame;
            ALTER TABLE c_tmp_promotion_dayagentgame DISABLE KEYS;
            INSERT INTO c_tmp_promotion_dayagentgame
            (
                `date`,
                `reg_date`,
                `regdays`,
                `agent_id`,
                `app_id`,
                `user_cnt`,
                `sum_money`,
                `pay_user_cnt`,
                `order_cnt`,
                `reg_device`
            )
            SELECT
                '".$this->today_date."' AS `date`,
                `reg_date` AS `reg_date`,
                `regdays` AS `regdays`,
                `agent_id` AS `agent_id`,
                `app_id` AS `app_id`,
                COUNT(DISTINCT `mem_id`) AS `user_cnt`,
                SUM(`sum_money`) AS `sum_money`,
                COUNT(DISTINCT(CASE  WHEN `sum_money`>0 THEN `mem_id` END)) AS `pay_user_cnt`,
                SUM(`order_cnt`) AS `order_cnt`,
                (SELECT COUNT(DISTINCT t1.`imei`) FROM `c_tmp_promotion_daypayuser` t1 WHERE t1.`regdays`='0' AND t1.`agent_id`=c_tmp_promotion_daypayuser.`agent_id` AND t1.`app_id`=c_tmp_promotion_daypayuser.`app_id` AND t1.`reg_date`=c_tmp_promotion_daypayuser.`reg_date`) AS imei_cnt
            FROM `c_tmp_promotion_daypayuser`
            GROUP BY `agent_id`,`app_id`,`reg_date`;
            ALTER TABLE c_tmp_promotion_dayagentgame ENABLE KEYS;
            
            
            TRUNCATE c_tmp_day_promotion;
            ALTER TABLE c_tmp_day_promotion DISABLE KEYS;
            REPLACE INTO `c_tmp_day_promotion`
            (
                `date`,
                `agent_id`,
                `user_cnt`, 
                `sum_money`, 
                `order_cnt`,
                `reg_order_cnt`,
                `pay_user_cnt`,
                `reg_pay_cnt`,
                `sum_reg_money`,
                `reg_cnt`,
                `reg_device`
            )
            (SELECT
                 '".$this->today_date."' AS `date`,
                `agent_id`,
                COUNT(DISTINCT `mem_id`) AS `user_cnt`,
                SUM(`sum_money`) AS `sum_money`,
                SUM(`order_cnt`) AS `order_cnt`,
                SUM(CASE  WHEN `regdays`=0 THEN `order_cnt` ELSE 0 END) AS `reg_order_cnt`,	
                COUNT(DISTINCT(CASE  WHEN `sum_money`>0 THEN `mem_id` END)) AS `pay_user_cnt`,
                COUNT(DISTINCT(CASE  WHEN `regdays`=0 AND  `sum_money`>0 THEN `mem_id` END)) AS `reg_pay_cnt`,
                SUM(CASE  WHEN `regdays`=0 THEN `sum_money` ELSE 0 END) AS `sum_reg_money`,
                COUNT(DISTINCT(CASE WHEN `regdays`=0 THEN `mem_id` END)) AS `reg_cnt`,
                COUNT(DISTINCT(CASE  WHEN `regdays`=0 THEN `imei` END)) AS imei_cnt
            FROM `c_tmp_promotion_daypayuser`
            GROUP BY agent_id)
			UNION 
			(SELECT
                '".$this->today_date."' AS `date`,
                `agent_id`,
                0 AS `user_cnt`,
                0 AS `sum_money`,
                0 AS `order_cnt`,
                0 AS `reg_order_cnt`,	
                0 AS `pay_user_cnt`,
                0 AS `reg_pay_cnt`,
                0 AS `sum_reg_money`,
                0 AS `reg_cnt`,
                0 AS `imei_cnt`
            FROM
                `c_promotion_day` WHERE `agent_id` NOT IN (SELECT agent_id FROM c_tmp_promotion_daypayuser WHERE agent_id IS NOT NULL )
				AND
                `date` = '".$this->today_date."');
            ALTER TABLE c_tmp_day_promotion ENABLE KEYS;
            
            TRUNCATE c_tmp_day_promotion_game;
            ALTER TABLE c_tmp_day_promotion_game DISABLE KEYS;
            REPLACE INTO `c_tmp_day_promotion_game`
            (
                `date`,
                `agent_id`,
                `app_id`,
                `user_cnt`,
                `sum_money`,
                `order_cnt`,	
                `reg_order_cnt`,
                `pay_user_cnt`,
                `reg_pay_cnt`,
                `sum_reg_money`,
                `reg_cnt`,
                `reg_device`
            )
            (SELECT
                `date`,
                `agent_id`,
                `app_id`,
                SUM(user_cnt) AS `user_cnt`,
                SUM(sum_money) AS `sum_money`,
                SUM(order_cnt) AS `order_cnt`,
                SUM(CASE  WHEN `regdays`=0 THEN `order_cnt` ELSE 0 END) AS `reg_order_cnt`,
                SUM(pay_user_cnt) AS `pay_user_cnt`,
                SUM(CASE  WHEN `regdays`=0 THEN `pay_user_cnt` ELSE 0 END) AS `reg_pay_cnt`,
                SUM(CASE  WHEN `regdays`=0 THEN `sum_money` ELSE 0 END) AS `sum_reg_money`,
                SUM(CASE  WHEN `regdays`=0 THEN `user_cnt` ELSE 0 END) AS `reg_cnt`,
                SUM(`reg_device`) AS `reg_device`
            FROM c_tmp_promotion_dayagentgame
            GROUP BY agent_id, app_id)
						UNION 
			(SELECT
                '".$this->today_date."' AS `date`,
                `agent_id`,
                `app_id`,
                0 AS `user_cnt`,
                0 AS `sum_money`,
                0 AS `order_cnt`,
                0 AS `reg_order_cnt`,	
                0 AS `pay_user_cnt`,
                0 AS `reg_pay_cnt`,
                0 AS `sum_reg_money`,
                0 AS `reg_cnt`,
                0 AS `reg_device`
            FROM
                `c_promotion_day` WHERE `agent_id` NOT IN (SELECT agent_id FROM c_tmp_promotion_dayagentgame WHERE agent_id IS NOT NULL )
				AND
                `date` = '".$this->today_date."');
            ALTER TABLE c_tmp_day_promotion_game ENABLE KEYS;
            ";
            M()->execute($_sql);
            $this->doDayPromotionGame();
            $this->doDayPromotion();
        }
    }

    /**
     * 操作tmp_day_promotion_game留存数据
     */
    public function doDayPromotionGame() {
        $_fields = "reg_date,app_id,agent_id,regdays,COUNT(DISTINCT `mem_id`) AS `user_cnt`";
        $_map['regdays'] = [
            'in' => [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 20, 29, 59]
        ];
        $_list = M('tmp_promotion_daypayuser')->field($_fields)->where($_map)->group(
            'app_id,agent_id,regdays'
        )->select();
        if (!empty($_list)) {
            foreach ($_list as $_k => $_v) {
                $_field = "";
                switch ($_v['regdays']) {
                    CASE 1:
                        $_field = "day2";
                        break;
                    CASE 2:
                        $_field = "day3";
                        break;
                    CASE 3:
                        $_field = "day4";
                        break;
                    CASE 4:
                        $_field = "day5";
                        break;
                    CASE 5:
                        $_field = "day6";
                        break;
                    CASE 7:
                        $_field = "day8";
                        break;
                    CASE 8:
                        $_field = "day9";
                        break;
                    CASE 9:
                        $_field = "day10";
                        break;
                    CASE 10:
                        $_field = "day11";
                        break;
                    CASE 11:
                        $_field = "day12";
                        break;
                    CASE 12:
                        $_field = "day13";
                        break;
                    CASE 13:
                        $_field = "day14";
                        break;
                    CASE 14:
                        $_field = "day15";
                        break;
                    CASE 20:
                        $_field = "day21";
                        break;
                    CASE 29:
                        $_field = "day30";
                        break;
                    CASE 59:
                        $_field = "day60";
                        break;
                }
                if (!empty($_field)) {
                    $_map = [
                        'date'     => $_v['reg_date'],
                        'agent_id' => $_v['agent_id'],
                        'app_id'   => $_v['app_id']
                    ];
                    M('tmp_day_promotion_game')->where($_map)->setField($_field, $_v['user_cnt']);
                }
            }
        }
    }

    /**
     * 操作tmp_day_promotion表留存数据
     */
    public function doDayPromotion() {
        $_map['regdays'] = [
            'in' => [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 20, 29, 59]
        ];
        $_fields = "reg_date,agent_id,regdays,COUNT(DISTINCT `mem_id`) AS `user_cnt`";
        $_list = M('tmp_promotion_daypayuser')->field($_fields)->where($_map)->group('agent_id,regdays')
                                              ->select();
        if (!empty($_list)) {
            foreach ($_list as $_k => $_v) {
                $_field = "";
                switch ($_v['regdays']) {
                    CASE 1:
                        $_field = "day2";
                        break;
                    CASE 2:
                        $_field = "day3";
                        break;
                    CASE 3:
                        $_field = "day4";
                        break;
                    CASE 4:
                        $_field = "day5";
                        break;
                    CASE 5:
                        $_field = "day6";
                        break;
                    CASE 7:
                        $_field = "day8";
                        break;
                    CASE 8:
                        $_field = "day9";
                        break;
                    CASE 9:
                        $_field = "day10";
                        break;
                    CASE 10:
                        $_field = "day11";
                        break;
                    CASE 11:
                        $_field = "day12";
                        break;
                    CASE 12:
                        $_field = "day13";
                        break;
                    CASE 13:
                        $_field = "day14";
                        break;
                    CASE 14:
                        $_field = "day15";
                        break;
                    CASE 20:
                        $_field = "day21";
                        break;
                    CASE 29:
                        $_field = "day30";
                        break;
                    CASE 59:
                        $_field = "day60";
                        break;
                }
                if (!empty($_field)) {
                    $_map = [
                        'date'     => $_v['reg_date'],
                        'agent_id' => $_v['agent_id']
                    ];
                    M('tmp_day_promotion')->where($_map)->setField($_field, $_v['user_cnt']);
                }
            }
        }
    }

    function _showRegPoint($cnt, $reg) {
        if ($reg) {
            return $cnt.'('.number_format($cnt * 100 / $reg, 2, '.', '').'%)';
        } else {
            return $cnt.'(0.00%)';
        }
    }
}