hlm_pl_mall/app/admin/controller/Statmember.php

<?php

/**
 * 会员统计
 *
 */

namespace app\admin\controller;
use think\facade\View;
use think\facade\Lang;
use think\facade\Db;
/**
 * ============================================================================
 * 
 * ============================================================================
 * 版权所有 2014-2028 浙江惠利玛产业互联网有限公司，并保留所有权利。
 * 网站地址: https://www.valimart.net/
 * ----------------------------------------------------------------------------
 * 
 * ============================================================================
 * 控制器
 */
class Statmember extends AdminControl {

    public function initialize() {
        parent::initialize(); // TODO: Change the autogenerated stub
        Lang::load(base_path() . 'admin/lang/' . config('lang.default_lang') . '/stat.lang.php');
        Lang::load(base_path() . 'admin/lang/' . config('lang.default_lang') . '/member.lang.php');
        include_once root_path(). 'extend/mall/statistics.php';
        include_once root_path(). 'extend/mall/datehelper.php';
        $stat_model = model('stat');
        //存储参数
        $this->search_arr = input('param.');
        //处理搜索时间
        if (in_array(request()->action(), array('newmember', 'analyze', 'scale', 'area', 'buying'))) {
            $this->search_arr = $stat_model->dealwithSearchTime($this->search_arr);
            //获得系统年份
            $year_arr = getSystemYearArr();
            //获得系统月份
            $month_arr = getSystemMonthArr();
            //获得本月的周时间段
            $week_arr = getMonthWeekArr($this->search_arr['week']['current_year'], $this->search_arr['week']['current_month']);
            View::assign('year_arr', $year_arr);
            View::assign('month_arr', $month_arr);
            View::assign('week_arr', $week_arr);
        }
        View::assign('search_arr', $this->search_arr);
    }

    /**
     * 新增会员
     */
    public function newmember() {
        if (!isset($this->search_arr['search_type'])) {
            $this->search_arr['search_type'] = 'day';
        }
        $stat_model = model('stat');
        $statlist = array(); //统计数据列表
        //新增总数数组
        $count_arr = array('up' => 0, 'curr' => 0);
        $where = array();
        $field = ' COUNT(*) as allnum ';
        if ($this->search_arr['search_type'] == 'day') {
            //构造横轴数据
            for ($i = 0; $i < 24; $i++) {
                //统计图数据
                $curr_arr[$i] = 0; //今天
                $up_arr[$i] = 0; //昨天
                //统计表数据
                $currlist_arr[$i]['timetext'] = $i;

                //方便搜索会员列表，计算开始时间和结束时间
                $currlist_arr[$i]['stime'] = $this->search_arr['day']['search_time'] + $i * 3600;
                $currlist_arr[$i]['etime'] = $currlist_arr[$i]['stime'] + 3600;

                $uplist_arr[$i]['val'] = 0;
                $currlist_arr[$i]['val'] = 0;
                //横轴
                $stat_arr['xAxis']['categories'][] = "$i";
            }
            $stime = $this->search_arr['day']['search_time'] - 86400; //昨天0点
            $etime = $this->search_arr['day']['search_time'] + 86400 - 1; //今天24点
            //总计的查询时间
            $count_arr['seartime'] = ($stime + 86400) . '|' . $etime;

            $today_day = @date('d', $this->search_arr['day']['search_time']); //今天日期
            $yesterday_day = @date('d', $stime); //昨天日期

            $where[] = array('member_addtime','between',array($stime, $etime));
            $field .= ' ,DAY(FROM_UNIXTIME(member_addtime)) as dayval,HOUR(FROM_UNIXTIME(member_addtime)) as hourval ';
            $memberlist = $stat_model->statByMember($where, $field, 0, '', 'dayval,hourval');
            if ($memberlist) {
                foreach ($memberlist as $k => $v) {
                    if ($today_day == $v['dayval']) {
                        $curr_arr[$v['hourval']] = intval($v['allnum']);
                        $currlist_arr[$v['hourval']]['val'] = intval($v['allnum']);
                        $count_arr['curr'] += intval($v['allnum']);
                    }
                    if ($yesterday_day == $v['dayval']) {
                        $up_arr[$v['hourval']] = intval($v['allnum']);
                        $uplist_arr[$v['hourval']]['val'] = intval($v['allnum']);
                        $count_arr['up'] += intval($v['allnum']);
                    }
                }
            }
            $stat_arr['series'][0]['name'] = lang('yestoday');
            $stat_arr['series'][0]['data'] = array_values($up_arr);
            $stat_arr['series'][1]['name'] = lang('today');
            $stat_arr['series'][1]['data'] = array_values($curr_arr);

            //统计数据标题
            $statlist['headertitle'] = array(lang('hour'), lang('yestoday'), lang('today'), lang('compare_to'));
            View::assign('actionurl', 'statmember/newmember?search_type=day&search_time=' . date('Y-m-d', $this->search_arr['day']['search_time']));
        }

        if ($this->search_arr['search_type'] == 'week') {
            $current_weekarr = explode('|', $this->search_arr['week']['current_week']);
            $stime = strtotime($current_weekarr[0]) - 86400 * 7;
            $etime = strtotime($current_weekarr[1]) + 86400 - 1;
            //总计的查询时间
            $count_arr['seartime'] = ($stime + 86400 * 7) . '|' . $etime;

            $up_week = @date('W', $stime); //上周
            $curr_week = @date('W', $etime); //本周
            //构造横轴数据
            for ($i = 1; $i <= 7; $i++) {
                //统计图数据
                $up_arr[$i] = 0;
                $curr_arr[$i] = 0;
                $tmp_weekarr = getSystemWeekArr();
                //统计表数据
                $currlist_arr[$i]['timetext'] = $tmp_weekarr[$i];
                //方便搜索会员列表，计算开始时间和结束时间
                $currlist_arr[$i]['stime'] = strtotime($current_weekarr[0]) + ($i - 1) * 86400;
                $currlist_arr[$i]['etime'] = $currlist_arr[$i]['stime'] + 86400 - 1;

                $uplist_arr[$i]['val'] = 0;
                $currlist_arr[$i]['val'] = 0;
                //横轴
                $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i];
                unset($tmp_weekarr);
            }
            $where[] = array('member_addtime','between',array($stime, $etime));
            $field .= ',WEEKOFYEAR(FROM_UNIXTIME(member_addtime)) as weekval,WEEKDAY(FROM_UNIXTIME(member_addtime))+1 as dayofweekval ';
            $memberlist = $stat_model->statByMember($where, $field, 0, '', 'weekval,dayofweekval');

            if ($memberlist) {
                foreach ($memberlist as $k => $v) {
                    if ($up_week == intval($v['weekval'])) {
                        $up_arr[$v['dayofweekval']] = intval($v['allnum']);
                        $uplist_arr[$v['dayofweekval']]['val'] = intval($v['allnum']);
                        $count_arr['up'] += intval($v['allnum']);
                    }
                    if ($curr_week == $v['weekval']) {
                        $curr_arr[$v['dayofweekval']] = intval($v['allnum']);
                        $currlist_arr[$v['dayofweekval']]['val'] = intval($v['allnum']);
                        $count_arr['curr'] += intval($v['allnum']);
                    }
                }
            }

            $stat_arr['series'][0]['name'] = lang('last_week');
            $stat_arr['series'][0]['data'] = array_values($up_arr);
            $stat_arr['series'][1]['name'] = lang('this_week');
            $stat_arr['series'][1]['data'] = array_values($curr_arr);
            //统计数据标题
            $statlist['headertitle'] = array(lang('week'), lang('last_week'), lang('this_week'), lang('compare_to'));
            View::assign('actionurl', 'statmember/newmember?search_type=week&searchweek_year=' . $this->search_arr['week']['current_year'] . '&searchweek_month=' . $this->search_arr['week']['current_month'] . '&searchweek_week=' . $this->search_arr['week']['current_week']);
        }

        if ($this->search_arr['search_type'] == 'month') {
            $stime = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01 -1 month");
            $etime = getMonthLastDay($this->search_arr['month']['current_year'], $this->search_arr['month']['current_month']) + 86400 - 1;
            //总计的查询时间
            $count_arr['seartime'] = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01") . '|' . $etime;

            $up_month = date('m', $stime);
            $curr_month = date('m', $etime);
            //计算横轴的最大量（由于每个月的天数不同）
            $up_dayofmonth = date('t', $stime);
            $curr_dayofmonth = date('t', $etime);
            $x_max = $up_dayofmonth > $curr_dayofmonth ? $up_dayofmonth : $curr_dayofmonth;

            //构造横轴数据
            for ($i = 1; $i <= $x_max; $i++) {
                //统计图数据
                $up_arr[$i] = 0;
                $curr_arr[$i] = 0;
                //统计表数据
                $currlist_arr[$i]['timetext'] = $i;
                //方便搜索会员列表，计算开始时间和结束时间
                $currlist_arr[$i]['stime'] = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01") + ($i - 1) * 86400;
                $currlist_arr[$i]['etime'] = $currlist_arr[$i]['stime'] + 86400 - 1;

                $uplist_arr[$i]['val'] = 0;
                $currlist_arr[$i]['val'] = 0;
                //横轴
                $stat_arr['xAxis']['categories'][] = $i;
                unset($tmp_montharr);
            }
            $where[] = array('member_addtime','between',array($stime, $etime));
            $field .= ',MONTH(FROM_UNIXTIME(member_addtime)) as monthval,day(FROM_UNIXTIME(member_addtime)) as dayval ';
            $memberlist = $stat_model->statByMember($where, $field, 0, '', 'monthval,dayval');
            if ($memberlist) {
                foreach ($memberlist as $k => $v) {
                    if ($up_month == $v['monthval']) {
                        $up_arr[$v['dayval']] = intval($v['allnum']);
                        $uplist_arr[$v['dayval']]['val'] = intval($v['allnum']);
                        $count_arr['up'] += intval($v['allnum']);
                    }
                    if ($curr_month == $v['monthval']) {
                        $curr_arr[$v['dayval']] = intval($v['allnum']);
                        $currlist_arr[$v['dayval']]['val'] = intval($v['allnum']);
                        $count_arr['curr'] += intval($v['allnum']);
                    }
                }
            }
            $stat_arr['series'][0]['name'] = lang('last_month');
            $stat_arr['series'][0]['data'] = array_values($up_arr);
            $stat_arr['series'][1]['name'] = lang('this_month');
            $stat_arr['series'][1]['data'] = array_values($curr_arr);
            //统计数据标题
            $statlist['headertitle'] = array(lang('date'), lang('last_month'), lang('this_month'), lang('compare_to'));
            View::assign('actionurl', 'statmember/newmember?search_type=month&searchmonth_year=' . $this->search_arr['month']['current_year'] . '&searchmonth_month=' . $this->search_arr['month']['current_month']);
        }

        //计算同比
        foreach ((array) $currlist_arr as $k => $v) {
            $tmp = array();
            $tmp['timetext'] = $v['timetext'];
            $tmp['seartime'] = $v['stime'] . '|' . $v['etime'];
            $tmp['currentdata'] = $v['val'];
            $tmp['updata'] = $uplist_arr[$k]['val'];
            $tmp['tbrate'] = getTb($tmp['updata'], $tmp['currentdata']);
            $statlist['data'][] = $tmp;
        }
        //计算总结同比
        $count_arr['tbrate'] = getTb($count_arr['up'], $count_arr['curr']);

        //导出Excel
        if (input('param.exporttype') == 'excel') {
            //导出Excel
            $excel_obj = new \excel\Excel();
            $excel_data = array();
            //设置样式
            $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
            //header
            foreach ($statlist['headertitle'] as $v) {
                $excel_data[0][] = array('styleid' => 's_title', 'data' => $v);
            }
            //data
            foreach ($statlist['data'] as $k => $v) {
                $excel_data[$k + 1][] = array('data' => $v['timetext']);
                $excel_data[$k + 1][] = array('format' => 'Number', 'data' => $v['updata']);
                $excel_data[$k + 1][] = array('format' => 'Number', 'data' => $v['currentdata']);
                $excel_data[$k + 1][] = array('data' => $v['tbrate']);
            }
            $excel_data[count($statlist['data']) + 1][] = array('data' => lang('statstore_total'));
            $excel_data[count($statlist['data']) + 1][] = array('format' => 'Number', 'data' => $count_arr['up']);
            $excel_data[count($statlist['data']) + 1][] = array('format' => 'Number', 'data' => $count_arr['curr']);
            $excel_data[count($statlist['data']) + 1][] = array('data' => $count_arr['tbrate']);

            $excel_data = $excel_obj->charset($excel_data, CHARSET);
            $excel_obj->addArray($excel_data);
            $excel_obj->addWorksheet($excel_obj->charset(lang('add_member_statis'), CHARSET));
            $excel_obj->generateXML($excel_obj->charset(lang('add_member_statis'), CHARSET) . date('Y-m-d-H', TIMESTAMP));
            exit();
        } else {
            //得到统计图数据
            $stat_arr['title'] = lang('add_member_statis');
            $stat_arr['yAxis'] = lang('add_member_number');
            $stat_json = getStatData_LineLabels($stat_arr);
            View::assign('stat_json', $stat_json);
            View::assign('statlist', $statlist);
            View::assign('count_arr', $count_arr);
            $this->setAdminCurItem('newmember');
            return View::fetch('stat_newmember');
        }
    }

    /**
     * 会员分析
     */
    public function analyze() {
        if (!isset($this->search_arr['search_type'])) {
            $this->search_arr['search_type'] = 'day';
        }
        $stat_model = model('stat');
        //构造横轴数据
        for ($i = 1; $i <= 15; $i++) {
            //横轴
            $stat_arr['xAxis']['categories'][] = $i;
        }
        $stat_arr['title'] = lang('buyer_rank').'Top15';
        $stat_arr['legend']['enabled'] = false;

        //获得搜索的开始时间和结束时间
        $searchtime_arr = $stat_model->getStarttimeAndEndtime($this->search_arr);

        $where = array();
        $where[] = array('statm_time','between', $searchtime_arr);
        //下单量
        $where[]=array('statm_ordernum','>', 0);
        $field = ' statm_memberid, statm_membername, SUM(statm_ordernum) as ordernum ';
        $ordernum_listtop15 = $stat_model->statByStatmember($where, $field, 0, 15, 'ordernum desc,statm_memberid desc', 'statm_memberid');
        $stat_ordernum_arr = $stat_arr;
        $stat_ordernum_arr['series'][0]['name'] = lang('statstore_ordernum');
        $stat_ordernum_arr['series'][0]['data'] = array();
        if (!empty($ordernum_listtop15)) {
            for ($i = 0; $i < 15; $i++) {
                if (isset($ordernum_listtop15[$i])) {
                    $stat_ordernum_arr['series'][0]['data'][] = array(
                        'name' => strval($ordernum_listtop15[$i]['statm_membername']),
                        'y' => intval($ordernum_listtop15[$i]['ordernum'])
                    );
                }
            }
        }
        $stat_ordernum_arr['yAxis'] = lang('statstore_ordernum');
        $statordernum_json = getStatData_Column2D($stat_ordernum_arr);
        unset($stat_ordernum_arr);
        View::assign('statordernum_json', $statordernum_json);
        View::assign('ordernum_listtop15', $ordernum_listtop15);

        //下单商品件数
        $where[]=array('statm_goodsnum','>', 0);
        $field = ' statm_memberid, statm_membername, SUM(statm_goodsnum) as goodsnum ';
        $goodsnum_listtop15 = $stat_model->statByStatmember($where, $field, 0, 15, 'goodsnum desc,statm_memberid desc', 'statm_memberid');
        $stat_goodsnum_arr = $stat_arr;
        $stat_goodsnum_arr['series'][0]['name'] = lang('goodsnum');
        $stat_goodsnum_arr['series'][0]['data'] = array();
        if (!empty($goodsnum_listtop15)) {
            for ($i = 0; $i < 15; $i++) {
                if (isset($goodsnum_listtop15[$i])) {
                    $stat_goodsnum_arr['series'][0]['data'][] = array(
                        'name' => strval($goodsnum_listtop15[$i]['statm_membername']),
                        'y' => intval($goodsnum_listtop15[$i]['goodsnum'])
                    );
                }
            }
        }
        $stat_goodsnum_arr['yAxis'] = lang('goodsnum');
        $statgoodsnum_json = getStatData_Column2D($stat_goodsnum_arr);
        unset($stat_goodsnum_arr);
        View::assign('statgoodsnum_json', $statgoodsnum_json);
        View::assign('goodsnum_listtop15', $goodsnum_listtop15);

        //下单金额
        $where[]=array('statm_orderamount','>', 0);
        $field = ' statm_memberid, statm_membername, SUM(statm_orderamount) as orderamount ';
        $orderamount_listtop15 = $stat_model->statByStatmember($where, $field, 0, 15, 'orderamount desc,statm_memberid desc', 'statm_memberid');
        $stat_orderamount_arr = $stat_arr;
        $stat_orderamount_arr['series'][0]['name'] = lang('statstore_orderamount');
        $stat_orderamount_arr['series'][0]['data'] = array();
        if (!empty($orderamount_listtop15)) {
            for ($i = 0; $i < 15; $i++) {
                if (isset($orderamount_listtop15[$i])) {
                    $stat_orderamount_arr['series'][0]['data'][] = array(
                        'name' => strval($orderamount_listtop15[$i]['statm_membername']),
                        'y' => floatval($orderamount_listtop15[$i]['orderamount'])
                    );
                }
            }
        }
        $stat_orderamount_arr['yAxis'] = lang('statstore_orderamount');
        $statorderamount_json = getStatData_Column2D($stat_orderamount_arr);
        unset($stat_orderamount_arr);
        View::assign('statorderamount_json', $statorderamount_json);
        View::assign('orderamount_listtop15', $orderamount_listtop15);
        View::assign('searchtime', implode('|', $searchtime_arr));
        $this->setAdminCurItem('analyze');
        return View::fetch('stat_memberanalyze');
    }

    /**
     * 会员分析异步详细列表
     */
    public function analyzeinfo() {
        $stat_model = model('stat');
        $where = array();
        $searchtime_arr_tmp = explode('|', $this->search_arr['t']);
        foreach ((array) $searchtime_arr_tmp as $k => $v) {
            $searchtime_arr[] = intval($v);
        }
        $where[] = array('statm_time','between', $searchtime_arr);
        $memberlist = array();
        //查询统计数据
        $field = ' statm_memberid, statm_membername ';
        $type = '';
        switch (input('param.type')) {
            case 'orderamount':
                $where[]=array('statm_orderamount','>', 0);
                $field .= ' ,SUM(statm_orderamount) as orderamount ';
                $caption = lang('statstore_orderamount');
                break;
            case 'goodsnum':
                $where[]=array('statm_goodsnum','>', 0);
                $field .= ' ,SUM(statm_goodsnum) as goodsnum ';
                $caption = lang('goodsnum');
                break;
            default:
                $type = 'ordernum';
                $where[]=array('statm_ordernum','>', 0);
                $field .= ' ,SUM(statm_ordernum) as ordernum ';
                $caption = lang('statstore_ordernum');
                break;
        }
        if (input('param.exporttype') == 'excel') {
            $memberlist = $stat_model->statByStatmember($where, $field, 0, 0, input('param.type')." desc,statm_memberid desc", 'statm_memberid');
        } else {
            $memberlist = $stat_model->statByStatmember($where, $field, 10, 0, input('param.type')." desc,statm_memberid desc", 'statm_memberid');
        }
        $curpage = ($t = intval(input('param.curpage'))) ? $t : 1;
        foreach ((array) $memberlist as $k => $v) {
            $v['number'] = ($curpage - 1) * 10 + $k + 1;
            $memberlist[$k] = $v;
        }
        //导出Excel
        if (input('param.exporttype') == 'excel') {
            //导出Excel
            $excel_obj = new \excel\Excel();
            $excel_data = array();
            //设置样式
            $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
            //header
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('statstore_number'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('ds_member_name'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => $caption);
            //data
            foreach ($memberlist as $k => $v) {
                $excel_data[$k + 1][] = array('format' => 'Number', 'data' => $v['number']);
                $excel_data[$k + 1][] = array('data' => $v['statm_membername']);
                $excel_data[$k + 1][] = array('data' => $v[$type]);
            }
            $excel_data = $excel_obj->charset($excel_data, CHARSET);
            $excel_obj->addArray($excel_data);
            $excel_obj->addWorksheet($excel_obj->charset(lang('ds_member') . $caption . lang('ds_stat'), CHARSET));
            $excel_obj->generateXML($excel_obj->charset(lang('ds_member') . $caption . lang('ds_stat'), CHARSET) . date('Y-m-d-H', TIMESTAMP));
            exit();
        } else {
            View::assign('caption', $caption);
            View::assign('stat_field', $type);
            View::assign('memberlist', $memberlist);
            View::assign('show_page', $stat_model->page_info->render());
            echo View::fetch('stat_memberanalyze_info');
        }
    }

    /**
     * 查看会员列表
     */
    public function showmember() {
        $stat_model = model('stat');
        $where = array();
        $actionurl = '';
        if (in_array(input('param.type'), array('newbyday', 'newbyweek', 'newbymonth'))) {
            $actionurl = (string)url('Statmember/showmember', ['type' => 'newbyday', 't' => input('param.t')]);
            $searchtime_arr_tmp = explode('|', input('param.t'));
            foreach ((array) $searchtime_arr_tmp as $k => $v) {
                $searchtime_arr[] = intval($v);
            }
            $where[] = array('member_addtime','between',$searchtime_arr);
        }
        if (isset($this->search_arr['exporttype']) && $this->search_arr['exporttype'] == 'excel') {
            $member_list = $stat_model->getMemberList($where);
        } else {
            $member_list = $stat_model->getMemberList($where, '*', 10);
        }
        if (is_array($member_list)) {
            foreach ($member_list as $k => $v) {
                $member_list[$k]['member_addtime'] = isset($v['member_addtime']) ? date('Y-m-d H:i:s', $v['member_addtime']) : '';
                $member_list[$k]['member_logintime'] = $v['member_logintime'] ? date('Y-m-d H:i:s', $v['member_logintime']) : '';
            }
        }
        //导出Excel
        if (isset($this->search_arr['exporttype']) && $this->search_arr['exporttype'] == 'excel') {
            //导出Excel
            $excel_obj = new \excel\Excel();
            $excel_data = array();
            //设置样式
            $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
            //header
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('ds_member_name'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('member_addtime'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('member_loginnum'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('member_logintime'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('ds_member_points'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('ds_predeposit'));
            //data
            foreach ($member_list as $k => $v) {
                $excel_data[$k + 1][] = array('data' => $v['member_name'] . '(' . lang('member_truename') . lang('ds_colon') . $v['member_truename'] . ')');
                $excel_data[$k + 1][] = array('data' => $v['member_addtime']);
                $excel_data[$k + 1][] = array('format' => 'Number', 'data' => $v['member_loginnum']);
                $excel_data[$k + 1][] = array('data' => $v['member_logintime'] . '(IP:' . $v['member_login_ip'] . ')');
                $excel_data[$k + 1][] = array('data' => $v['member_points']);
                $excel_data[$k + 1][] = array('data' => lang('member_index_available') . lang('ds_colon') . $v['available_predeposit'] . lang('ds_yuan') . '(' . lang('member_index_frozen') . lang('ds_colon') . $v['freeze_predeposit'] . lang('ds_yuan') . ')');
            }
            $excel_data = $excel_obj->charset($excel_data, CHARSET);
            $excel_obj->addArray($excel_data);
            $excel_obj->addWorksheet($excel_obj->charset(lang('stat_newmember'), CHARSET));
            $excel_obj->generateXML($excel_obj->charset(lang('stat_newmember'), CHARSET) . date('Y-m-d-H', TIMESTAMP));
            exit();
        }
        View::assign('actionurl', $actionurl);
        View::assign('member_list', $member_list);
        View::assign('show_page', $stat_model->page_info->render());
        $this->setAdminCurItem('showmember');
        return View::fetch('memberlist');
    }

    /**
     * 会员规模
     */
    public function scale() {
        if (!isset($this->search_arr['search_type'])) {
            $this->search_arr['search_type'] = 'day';
        }
        $stat_model = model('stat');
        $statlist = array(); //统计数据列表
        //获得搜索的开始时间和结束时间
        $searchtime_arr = $stat_model->getStarttimeAndEndtime($this->search_arr);
        $where = array();
        $where[] = array('statm_time','between', $searchtime_arr);
        if (trim(isset($this->search_arr['membername']))) {
            $where[]=array('statm_membername','like', "%" . trim($this->search_arr['membername']) . "%");
        }
        $field = ' statm_memberid, statm_membername, statm_time, SUM(statm_orderamount) as orderamount, SUM(statm_predincrease) as predincrease, -SUM(statm_predreduce) as predreduce, SUM(statm_pointsincrease) as pointsincrease, -SUM(statm_pointsreduce) as pointsreduce ';
        //排序
        $orderby_arr = array('orderamount asc', 'orderamount desc', 'predincrease asc', 'predincrease desc', 'predreduce asc', 'predreduce desc', 'pointsincrease asc', 'pointsincrease desc', 'pointsreduce asc', 'pointsreduce desc');
        if (!in_array(trim(isset($this->search_arr['orderby'])), $orderby_arr)) {
            $this->search_arr['orderby'] = 'orderamount desc';
        }
        $orderby = trim($this->search_arr['orderby']) . ',statm_memberid desc';

        if (input('param.exporttype') == 'excel') {
            $statlist = $stat_model->statByStatmember($where, $field, 0, 0, $orderby, 'statm_memberid');
        } else {
            $statlist = $stat_model->statByStatmember($where, $field, 10, 0, $orderby, 'statm_memberid');
        }
        //导出Excel
        if (isset($this->search_arr['exporttype']) && $this->search_arr['exporttype'] == 'excel') {
            //导出Excel
            $excel_obj = new \excel\Excel();
            $excel_data = array();
            //设置样式
            $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
            //header
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('ds_member_name'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('statstore_orderamount'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('predincrease'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('predreduce'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('pointsincrease'));
            $excel_data[0][] = array('styleid' => 's_title', 'data' => lang('pointsreduce'));
            //data
            foreach ($statlist as $k => $v) {
                $excel_data[$k + 1][] = array('data' => $v['statm_membername']);
                $excel_data[$k + 1][] = array('data' => $v['orderamount']);
                $excel_data[$k + 1][] = array('data' => $v['predincrease']);
                $excel_data[$k + 1][] = array('data' => $v['predreduce']);
                $excel_data[$k + 1][] = array('data' => $v['pointsincrease']);
                $excel_data[$k + 1][] = array('data' => $v['pointsreduce']);
            }
            $excel_data = $excel_obj->charset($excel_data, CHARSET);
            $excel_obj->addArray($excel_data);
            $excel_obj->addWorksheet($excel_obj->charset(lang('stat_scaleanalyze'), CHARSET));
            $excel_obj->generateXML($excel_obj->charset(lang('stat_scaleanalyze'), CHARSET) . date('Y-m-d-H', TIMESTAMP));
            exit();
        }
        View::assign('statlist', $statlist);
        View::assign('show_page', $stat_model->page_info->render());
        View::assign('orderby', $this->search_arr['orderby']);
        $this->setAdminCurItem('scale');
        return View::fetch('stat_memberscale');
    }

    /**
     * 区域分析
     */
    public function area() {
        if (!isset($this->search_arr['search_type'])) {
            $this->search_arr['search_type'] = 'day';
        }
        $stat_model = model('stat');
        //获得搜索的开始时间和结束时间
        $searchtime_arr = $stat_model->getStarttimeAndEndtime($this->search_arr);
        View::assign('searchtime', implode('|', $searchtime_arr));
        $this->setAdminCurItem('area');
        return View::fetch('stat_memberarea');
    }

    /**
     * 区域分析之详细列表
     */
    public function area_list() {
        $stat_model = model('stat');
        $where = array();
        $where[] = array('order_isvalid','=',1); //计入统计的有效订单
        $searchtime_arr_tmp = explode('|', $this->search_arr['t']);
        foreach ((array) $searchtime_arr_tmp as $k => $v) {
            $searchtime_arr[] = intval($v);
        }
        $where[] = array('order_add_time','between',$searchtime_arr);
        $field = ' reciver_province_id, COUNT(*) as ordernum,SUM(order_amount) as orderamount, COUNT(DISTINCT buyer_id) as membernum ';

        $orderby_arr = array('membernum asc', 'membernum desc', 'orderamount asc', 'orderamount desc', 'ordernum asc', 'ordernum desc');
        if (!isset($this->search_arr['orderby']) || !in_array(trim($this->search_arr['orderby']), $orderby_arr)) {
            $this->search_arr['orderby'] = 'membernum desc';
        }
        $orderby = trim($this->search_arr['orderby']) . ',reciver_province_id';

        if (isset($this->search_arr['exporttype']) && $this->search_arr['exporttype'] == 'excel') {
            $statlist_tmp = $stat_model->statByStatorder($where, $field, 0, 0, $orderby, 'reciver_province_id');
        } else {
            $statlist_tmp = $stat_model->statByStatorder($where, $field, 10, 0, $orderby, 'reciver_province_id');
        }
        // 地区
        $province_array = model('area')->getTopLevelAreas();
        $statheader = array();
        $statheader[] = array('text' => lang('statestore_province'), 'key' => 'provincename');
        $statheader[] = array('text' => lang('membernum'), 'key' => 'membernum', 'isorder' => 1);
        $statheader[] = array('text' => lang('statstore_orderamount'), 'key' => 'orderamount', 'isorder' => 1);
        $statheader[] = array('text' => lang('statstore_ordernum'), 'key' => 'ordernum', 'isorder' => 1);
        $statlist = array();
        foreach ((array) $statlist_tmp as $k => $v) {
            $province_id = intval($v['reciver_province_id']);
            $tmp = array();
            $tmp['provincename'] = ($t = $province_array[$province_id]) ? $t : lang('other');
            $tmp['membernum'] = $v['membernum'];
            $tmp['orderamount'] = $v['orderamount'];
            $tmp['ordernum'] = $v['ordernum'];
            $statlist[] = $tmp;
        }
        //导出Excel
        if (isset($this->search_arr['exporttype']) && $this->search_arr['exporttype'] == 'excel') {
            //导出Excel
            $excel_obj = new \excel\Excel();
            $excel_data = array();
            //设置样式
            $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
            //header
            foreach ($statheader as $k => $v) {
                $excel_data[0][] = array('styleid' => 's_title', 'data' => $v['text']);
            }
            //data
            foreach ($statlist as $k => $v) {
                foreach ($statheader as $h_k => $h_v) {
                    $excel_data[$k + 1][] = array('data' => $v[$h_v['key']]);
                }
            }
            $excel_data = $excel_obj->charset($excel_data, CHARSET);
            $excel_obj->addArray($excel_data);
            $excel_obj->addWorksheet($excel_obj->charset(lang('stat_areaanalyze'), CHARSET));
            $excel_obj->generateXML($excel_obj->charset(lang('stat_areaanalyze'), CHARSET) . date('Y-m-d-H', TIMESTAMP));
            exit();
        }
        View::assign('statlist', $statlist);
        View::assign('statheader', $statheader);
        View::assign('orderby', $this->search_arr['orderby']);
        View::assign('actionurl', (string)url('Statmember/area_list', ['t' => $this->search_arr['t']]));
        View::assign('show_page', $stat_model->page_info->render());
        $this->setAdminCurItem('area');
        echo View::fetch('stat_listandorder');
    }

    /**
     * 区域分析之地图数据
     */
    public function area_map() {
        $stat_model = model('stat');
        $where = array();
        $where[] = array('order_isvalid','=',1); //计入统计的有效订单
        $searchtime_arr_tmp = explode('|', $this->search_arr['t']);
        foreach ((array) $searchtime_arr_tmp as $k => $v) {
            $searchtime_arr[] = intval($v);
        }
        $where[] = array('order_add_time','between',$searchtime_arr);
        $memberlist = array();
        //查询统计数据
        $field = ' reciver_province_id ';
        $type = input('param.type');
        switch ($type) {
            case 'orderamount':
                $field .= ' ,SUM(order_amount) as orderamount ';
                $orderby = 'orderamount desc';
                break;
            case 'ordernum':
                $field .= ' ,COUNT(*) as ordernum ';
                $orderby = 'ordernum desc';
                break;
            default:
                $type = 'membernum';
                $field .= ' ,COUNT(DISTINCT buyer_id) as membernum ';
                $orderby = 'membernum desc';
                break;
        }
        $orderby .= ',reciver_province_id';
        $statlist_tmp = $stat_model->statByStatorder($where, $field, 10, 0, $orderby, 'reciver_province_id');
        // 地区
        $province_array = model('area')->getTopLevelAreas();
        //地图显示等级数组
        $level_arr = array(array(1, 2, 3), array(4, 5, 6), array(7, 8, 9), array(10, 11, 12));
        $statlist = array();
        foreach ((array) $statlist_tmp as $k => $v) {
            $v['level'] = 4; //排名
            foreach ($level_arr as $lk => $lv) {
                if (in_array($k + 1, $lv)) {
                    $v['level'] = $lk; //排名
                }
            }
            $province_id = intval($v['reciver_province_id']);
            $statlist[$province_id] = $v;
        }
        $stat_arr = array();
        foreach ((array) $province_array as $k => $v) {
            if (!empty($statlist[$k])) {
                switch ($type) {
                    case 'orderamount':
                        $des = "，下单金额：{$statlist[$k]['orderamount']}";
                        break;
                    case 'ordernum':
                        $des = "，下单量：{$statlist[$k]['ordernum']}";
                        break;
                    default:
                        $des = "，下单会员数：{$statlist[$k]['membernum']}";
                        break;
                }
                $stat_arr[] = array('cha' => $k, 'name' => $v, 'des' => $des, 'level' => $statlist[$k]['level']);
            } else {
                $des = "，无订单数据";
                $stat_arr[] = array('cha' => $k, 'name' => $v, 'des' => $des, 'level' => 4);
            }
        }
        $stat_json = getStatData_Map($stat_arr);
        View::assign('stat_field', $type);
        View::assign('stat_json', $stat_json);
        echo View::fetch('stat_map');
    }

    /**
     * 购买分析
     */
    public function buying() {
        if (!isset($this->search_arr['search_type'])) {
            $this->search_arr['search_type'] = 'day';
        }
        $stat_model = model('stat');
        //获得搜索的开始时间和结束时间
        $searchtime_arr = $stat_model->getStarttimeAndEndtime($this->search_arr);
        /*
         * 客单价分布
         */
        $where = array();
        $where[] = array('order_isvalid','=',1); //计入统计的有效订单
        $where[] = array('order_add_time','between',$searchtime_arr);

        $field = '1';
        $pricerange_arr = ($t = trim(config('ds_config.stat_orderpricerange'))) ? unserialize($t) : '';
        if ($pricerange_arr) {
            $stat_arr['series'][0]['name'] = lang('statstore_ordernum');
            //设置价格区间最后一项，最后一项只有开始值没有结束值
            $pricerange_count = count($pricerange_arr);
            if ($pricerange_arr[$pricerange_count - 1]['e']) {
                $pricerange_arr[$pricerange_count]['s'] = $pricerange_arr[$pricerange_count - 1]['e'] + 1;
                $pricerange_arr[$pricerange_count]['e'] = '';
            }
            foreach ((array) $pricerange_arr as $k => $v) {
                $v['s'] = intval($v['s']);
                $v['e'] = intval($v['e']);
                //构造查询字段
                if ($v['e']) {
                    $field .= " ,SUM(IF(order_amount > {$v['s']} and order_amount <= {$v['e']},1,0)) as ordernum_{$k}";
                } else {
                    $field .= " ,SUM(IF(order_amount > {$v['s']},1,0)) as ordernum_{$k}";
                }
            }
            $orderlist = Db::query('SELECT ' . $field . ' FROM ' . config('database.connections.mysql.prefix') . 'statorder WHERE order_isvalid=1 AND order_add_time BETWEEN ' . $searchtime_arr[0] . ' AND ' . $searchtime_arr[1]);
            if ($orderlist) {
                $orderlist = current($orderlist);
                foreach ((array) $pricerange_arr as $k => $v) {
                    //横轴
                    if ($v['e']) {
                        $stat_arr['xAxis']['categories'][] = $v['s'] . '-' . $v['e'];
                    } else {
                        $stat_arr['xAxis']['categories'][] = $v['s'] . lang('above');
                    }
                    //统计图数据
                    if (isset($orderlist['ordernum_' . $k])) {
                        $stat_arr['series'][0]['data'][] = intval($orderlist['ordernum_' . $k]);
                    } else {
                        $stat_arr['series'][0]['data'][] = 0;
                    }
                }
            }
            //得到统计图数据
            $stat_arr['title'] = lang('statmember_per_member_price_distribution');
            $stat_arr['legend']['enabled'] = false;
            $stat_arr['yAxis'] = lang('statstore_ordernum');
            $guestprice_statjson = getStatData_LineLabels($stat_arr);
        } else {
            $guestprice_statjson = '';
        }
        unset($stat_arr);

        /*
         * 购买频次分析
         */
        //统计期间会员下单量
        $where = array();
        $where[] = array('statm_time','between', $searchtime_arr);
        $where[] = array('statm_ordernum','>', 0);
        $field = 'COUNT(*) as countnum';
        $countnum_arr = $stat_model->getOneStatmember($where, $field);
        $countnum = intval($countnum_arr['countnum']);
        $member_arr = array();
        for ($i = 0; $i < $countnum; $i += 1000) {//由于数据库底层的限制，所以每次查询1000条
            $statmember_list = Db::name('statmember')->field('statm_memberid,statm_ordernum')->where($where)->page($i,1000)->group('statm_id')->select()->toArray();
            foreach ((array) $statmember_list as $k => $v) {
                $member_arr[$v['statm_memberid']] = isset($member_arr[$v['statm_memberid']])?intval($member_arr[$v['statm_memberid']]):0 + intval($v['statm_ordernum']);
            }
        }
        $stattimes_arr = array();
        if ($member_arr) {
            //整理期间各个频次的下单客户数
            for ($i = 1; $i <= 10; $i++) {
                $stattimes_arr[$i] = array('num' => 0, 'rate' => 0.00);
                if ($i >= 10) {
                    $stattimes_arr[$i]['text'] = sprintf(lang('buy_time_info'),10);
                } else {
                    $stattimes_arr[$i]['text'] = sprintf(lang('buy_time_info'),$i);
                }
            }
            foreach ($member_arr as $k => $v) {
                if ($v >= 10) {
                    $stattimes_arr[10]['num'] = intval($stattimes_arr[10]['num']) + 1;
                } else {
                    $stattimes_arr[$v]['num'] = intval($stattimes_arr[$v]['num']) + 1;
                }
            }
            //计算期间各个频次的下单客户数占总数比例
            foreach ($stattimes_arr as $k => $v) {
                $stattimes_arr[$k]['rate'] = round(intval($v['num']) / count($member_arr) * 100, 2);
            }
        }

        //购买时段分布
        $where = array();
        $where[] = array('order_isvalid','=',1); //计入统计的有效订单
        $where[] = array('order_add_time','between',$searchtime_arr);
        $field = ' HOUR(FROM_UNIXTIME(order_add_time)) as hourval,COUNT(*) as ordernum ';
        $orderlist = $stat_model->statByStatorder($where, $field, 0, 0, '', 'hourval');
        $stat_arr = array();
        $stat_arr['series'][0]['name'] = lang('statstore_ordernum');
        //构造横轴坐标
        for ($i = 0; $i < 24; $i++) {
            //横轴
            $stat_arr['xAxis']['categories'][] = $i;
            $stat_arr['series'][0]['data'][$i] = 0;
        }
        foreach ((array) $orderlist as $k => $v) {
            //统计图数据
            $stat_arr['series'][0]['data'][$v['hourval']] = intval($v['ordernum']);
        }
        //得到统计图数据
        $stat_arr['title'] = lang('statmember_time_distribution');
        $stat_arr['legend']['enabled'] = false;
        $stat_arr['yAxis'] = lang('statstore_ordernum');
        $hour_statjson = getStatData_LineLabels($stat_arr);

        View::assign('hour_statjson', $hour_statjson);
        View::assign('stattimes_arr', $stattimes_arr);
        View::assign('guestprice_statjson', $guestprice_statjson);
        $this->setAdminCurItem('buying');
        return View::fetch('stat_buying');
    }

    protected function getAdminItemList() {
        $menu_array = array(
            array('name' => 'newmember', 'text' => lang('stat_newmember'), 'url' => (string)url('Statmember/newmember')),
            array('name' => 'analyze', 'text' => lang('stat_memberanalyze'), 'url' => (string)url('Statmember/analyze')),
            array('name' => 'scale', 'text' => lang('stat_scaleanalyze'), 'url' => (string)url('Statmember/scale')),
            array('name' => 'area', 'text' => lang('stat_areaanalyze'), 'url' => (string)url('Statmember/area')),
            array('name' => 'buying', 'text' => lang('stat_buying'), 'url' => (string)url('Statmember/buying')),
            array('name' => 'showmember', 'text' => lang('stat_memberlist'), 'url' => (string)url('Statmember/showmember'))
        );
        return $menu_array;
    }

}