/** * 店铺流量统计 */ public function storeflowOp() { $store_id = intval($_SESSION['store_id']); //确定统计分表名称 $last_num = $store_id % 10; //获取店铺ID的末位数字 $tablenum = ($t = intval(C('flowstat_tablenum'))) > 1 ? $t : 1; //处理流量统计记录表数量 $flow_tablename = ($t = ($last_num % $tablenum)) > 0 ? "flowstat_$t" : 'flowstat'; if(!$this->search_arr['search_type']){ $this->search_arr['search_type'] = 'week'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); $where = array(); $where['store_id'] = $store_id; $where['stattime'] = array('between',$searchtime_arr); $where['type'] = 'sum'; $field = ' SUM(clicknum) as amount'; if($this->search_arr['search_type'] == 'week'){ //构造横轴数据 for($i=1; $i<=7; $i++){ $tmp_weekarr = getSystemWeekArr(); //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); $statlist[$i] = 0; } $field .= ' ,WEEKDAY(FROM_UNIXTIME(stattime))+1 as timeval '; } if($this->search_arr['search_type'] == 'month'){ //计算横轴的最大量(由于每个月的天数不同) $dayofmonth = date('t',$searchtime_arr[0]); //构造横轴数据 for($i=1; $i<=$dayofmonth; $i++){ //横轴 $stat_arr['xAxis']['categories'][] = $i; $statlist[$i] = 0; } $field .= ' ,day(FROM_UNIXTIME(stattime)) as timeval '; } $statlist_tmp = $model->statByFlowstat($flow_tablename, $where, $field, 0, 0, 'timeval asc', 'timeval'); if ($statlist_tmp){ foreach((array)$statlist_tmp as $k=>$v){ $statlist[$v['timeval']] = floatval($v['amount']); } } //得到统计图数据 $stat_arr['legend']['enabled'] = false; $stat_arr['series'][0]['name'] = '访问量'; $stat_arr['series'][0]['data'] = array_values($statlist); $stat_arr['title'] = '店铺访问量统计'; $stat_arr['yAxis'] = '访问次数'; $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json',$stat_json); self::profile_menu('storeflow'); Tpl::showpage('stat.flow.store'); }
/** * 价格分布 */ public function priceOp() { if (!$this->search_arr['search_type']) { $this->search_arr['search_type'] = 'day'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', $searchtime_arr); $gc_id_depth = $this->gc_arr[$this->choose_gcid]['depth']; if ($this->choose_gcid > 0) { $where['gc_parentid_' . $gc_id_depth] = $this->choose_gcid; } $field = '1'; $pricerange_arr = ($t = trim(C('stat_pricerange'))) ? unserialize($t) : ''; if ($pricerange_arr) { $goodsnum_stat_arr['series'][0]['name'] = '下单商品数'; $orderamount_stat_arr['series'][0]['name'] = '下单金额'; //设置价格区间最后一项,最后一项只有开始值没有结束值 $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(goods_pay_price/goods_num > {$v['s']} and goods_pay_price/goods_num <= {$v['e']},goods_num,0)) as goodsnum_{$k}"; $field .= " ,SUM(IF(goods_pay_price/goods_num > {$v['s']} and goods_pay_price/goods_num <= {$v['e']},goods_pay_price,0)) as orderamount_{$k}"; } else { //由于最后一个区间没有结束值,所以需要单独构造sql $field .= " ,SUM(IF(goods_pay_price/goods_num > {$v['s']},goods_num,0)) as goodsnum_{$k}"; $field .= " ,SUM(IF(goods_pay_price/goods_num > {$v['s']},goods_pay_price,0)) as orderamount_{$k}"; } } $ordergooods_list = $model->getoneByStatordergoods($where, $field); if ($ordergooods_list) { foreach ((array) $pricerange_arr as $k => $v) { //横轴 if ($v['e']) { $goodsnum_stat_arr['xAxis']['categories'][] = $v['s'] . '-' . $v['e']; $orderamount_stat_arr['xAxis']['categories'][] = $v['s'] . '-' . $v['e']; } else { $goodsnum_stat_arr['xAxis']['categories'][] = $v['s'] . '以上'; $orderamount_stat_arr['xAxis']['categories'][] = $v['s'] . '以上'; } //统计图数据 $goodsnum_stat_arr['series'][0]['data'][$k] = 0; $orderamount_stat_arr['series'][0]['data'][$k] = 0; if ($ordergooods_list['goodsnum_' . $k]) { $goodsnum_stat_arr['series'][0]['data'][$k] = intval($ordergooods_list['goodsnum_' . $k]); } if ($ordergooods_list['orderamount_' . $k]) { $orderamount_stat_arr['series'][0]['data'][$k] = intval($ordergooods_list['orderamount_' . $k]); } } } //得到统计图数据 $goodsnum_stat_arr['legend']['enabled'] = false; $goodsnum_stat_arr['title'] = '行业价格下单商品数'; $goodsnum_stat_arr['yAxis'] = ''; $orderamount_stat_arr['legend']['enabled'] = false; $orderamount_stat_arr['title'] = '行业价格下单金额'; $orderamount_stat_arr['yAxis'] = ''; $goodsnum_stat_json = getStatData_LineLabels($goodsnum_stat_arr); $orderamount_stat_json = getStatData_LineLabels($orderamount_stat_arr); } else { $goodsnum_stat_json = ''; $orderamount_stat_json = ''; } Tpl::output('goodsnum_stat_json', $goodsnum_stat_json); Tpl::output('orderamount_stat_json', $orderamount_stat_json); Tpl::output('top_link', $this->sublink($this->links, 'price')); Tpl::showpage('stat.industry.price'); }
/** * 价格销量统计 */ public function priceOp(){ if(!$this->search_arr['search_type']){ $this->search_arr['search_type'] = 'day'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); $where = array(); $where['store_id'] = $_SESSION['store_id']; $where['order_isvalid'] = 1;//计入统计的有效订单 $where['order_add_time'] = array('between',$searchtime_arr); //商品分类 if ($this->choose_gcid > 0){ //获得分类深度 $depth = $this->gc_arr[$this->choose_gcid]['depth']; $where['gc_parentid_'.$depth] = $this->choose_gcid; } $field = '1'; $pricerange = Model('store_extend')->getfby_store_id($_SESSION['store_id'],'pricerange'); $pricerange_arr = $pricerange?unserialize($pricerange):array(); if ($pricerange_arr){ $stat_arr['series'][0]['name'] = '下单量'; //设置价格区间最后一项,最后一项只有开始值没有结束值 $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(goods_pay_price/goods_num > {$v['s']} and goods_pay_price/goods_num <= {$v['e']},goods_num,0)) as goodsnum_{$k}"; } else { $field .= " ,SUM(IF(goods_pay_price/goods_num > {$v['s']},goods_num,0)) as goodsnum_{$k}"; } } $ordergooods_list = $model->getoneByStatordergoods($where, $field); if($ordergooods_list){ 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'].'以上'; } //统计图数据 if ($ordergooods_list['goodsnum_'.$k]){ $stat_arr['series'][0]['data'][] = intval($ordergooods_list['goodsnum_'.$k]); } else { $stat_arr['series'][0]['data'][] = 0; } } } //得到统计图数据 $stat_arr['title'] = '价格销量分布'; $stat_arr['legend']['enabled'] = false; $stat_arr['yAxis'] = '销量'; $pricerange_statjson = getStatData_LineLabels($stat_arr); } else { $pricerange_statjson = ''; } Tpl::output('statjson',$pricerange_statjson); self::profile_menu('price'); Tpl::showpage('stat.goods.price'); }
/** * 促销销售趋势分析 */ public function promotiontrendOp() { //优惠类型数组 $goodstype_arr = array(2 => '团购', 3 => '限时折扣', 4 => '优惠套装'); $model = Model('stat'); $where = array(); $searchtime_arr = explode('|', $_GET['t']); $where['add_time'] = array('between', $searchtime_arr); //$where['order_state'] = array(array('neq',ORDER_STATE_CANCEL),array('neq',ORDER_STATE_NEW),'and'); $where['order_state'] = array('neq', ORDER_STATE_NEW); //去除未支付订单 $where['refund_state'] = array('exp', "!(order_state = '" . ORDER_STATE_CANCEL . "' and refund_state = 0)"); //没有参与退款的取消订单,不记录到统计中 $where['payment_code'] = array('exp', "!(order.payment_code='offline' and order_state <> '" . ORDER_STATE_SUCCESS . "')"); //货到付款订单,订单成功之后才计入统计 $where['goods_type'] = array('in', array(2, 3, 4)); $field = ' goods_type'; switch ($this->search_arr['stattype']) { case 'orderamount': $field .= " ,SUM(goods_pay_price) as orderamount"; $caption = '下单金额'; break; case 'goodsnum': $field .= " ,SUM(goods_num) as goodsnum"; $caption = '下单商品数'; break; default: $field .= " ,count(DISTINCT order.order_id) as ordernum"; $caption = '下单量'; break; } if ($this->search_arr['search_type'] == 'day') { //构造横轴数据 for ($i = 0; $i < 24; $i++) { //横轴 $stat_arr['xAxis']['categories'][] = "{$i}"; foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,HOUR(FROM_UNIXTIME(add_time)) as timeval '; } if ($this->search_arr['search_type'] == 'week') { //构造横轴数据 for ($i = 1; $i <= 7; $i++) { $tmp_weekarr = getSystemWeekArr(); //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,WEEKDAY(FROM_UNIXTIME(add_time))+1 as timeval '; } if ($this->search_arr['search_type'] == 'month') { //计算横轴的最大量(由于每个月的天数不同) $dayofmonth = date('t', $searchtime_arr[0]); //构造横轴数据 for ($i = 1; $i <= $dayofmonth; $i++) { //横轴 $stat_arr['xAxis']['categories'][] = $i; foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,day(FROM_UNIXTIME(add_time)) as timeval '; } //查询数据 $statlist_tmp = $model->statByOrderGoods($where, $field, 0, '', 'timeval', 'goods_type,timeval'); //整理统计数组 if ($statlist_tmp) { foreach ($statlist_tmp as $k => $v) { //将数据按照不同的促销方式分组 foreach ($goodstype_arr as $t_k => $t_v) { if ($t_k == $v['goods_type']) { switch ($this->search_arr['stattype']) { case 'orderamount': $statlist[$t_k][$v['timeval']] = round($v[$this->search_arr['stattype']], 2); break; case 'goodsnum': $statlist[$t_k][$v['timeval']] = intval($v[$this->search_arr['stattype']]); break; default: $statlist[$t_k][$v['timeval']] = intval($v[$this->search_arr['stattype']]); break; } } } } } foreach ($goodstype_arr as $k => $v) { $tmp = array(); $tmp['name'] = $v; $tmp['data'] = array_values($statlist[$k]); $stat_arr['series'][] = $tmp; } //得到统计图数据 $stat_arr['title'] = $caption . '统计'; $stat_arr['yAxis'] = $caption; $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json', $stat_json); Tpl::showpage('stat.linelabels', 'null_layout'); }
/** * 促销分析 */ public function generalOp() { $model = Model('stat'); //统计的日期0点 $stat_time = strtotime(date('Y-m-d', time())) - 86400; /* * 近30天 */ $stime = $stat_time - 86400 * 29; //30天前 $etime = $stat_time + 86400 - 1; //昨天23:59 $statnew_arr = array(); //查询订单表下单量、下单金额、下单客户数 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['store_id'] = $_SESSION['store_id']; $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' COUNT(*) as ordernum, SUM(order_amount) as orderamount, COUNT(DISTINCT buyer_id) as ordermembernum, AVG(order_amount) as avgorderamount '; $stat_order = $model->getoneByStatorder($where, $field); $statnew_arr['ordernum'] = ($t = $stat_order['ordernum']) ? $t : 0; $statnew_arr['orderamount'] = ncPriceFormat(($t = $stat_order['orderamount']) ? $t : 0); $statnew_arr['ordermembernum'] = ($t = $stat_order['ordermembernum']) > 0 ? $t : 0; $statnew_arr['avgorderamount'] = ncPriceFormat(($t = $stat_order['avgorderamount']) ? $t : 0); unset($stat_order); //下单高峰期 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['store_id'] = $_SESSION['store_id']; $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' HOUR(FROM_UNIXTIME(order_add_time)) as hourval,COUNT(*) as ordernum '; $orderlist = $model->statByStatorder($where, $field, 0, 0, 'ordernum desc,hourval asc', 'hourval'); foreach ((array) $orderlist as $k => $v) { if ($k < 2) { //取前两个订单量高的时间段 if (!$statnew_arr['hothour']) { $statnew_arr['hothour'] = $v['hourval'] . ":00~" . ($v['hourval'] + 1) . ":00"; } else { $statnew_arr['hothour'] .= "," . ($v['hourval'] . ":00~" . ($v['hourval'] + 1) . ":00"); } } } unset($orderlist); //查询订单商品表下单商品数 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['store_id'] = $_SESSION['store_id']; $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' SUM(goods_num) as ordergoodsnum, AVG(goods_pay_price/goods_num) as avggoodsprice '; $stat_ordergoods = $model->getoneByStatordergoods($where, $field); $statnew_arr['ordergoodsnum'] = ($t = $stat_ordergoods['ordergoodsnum']) ? $t : 0; $statnew_arr['avggoodsprice'] = ncPriceFormat(($t = $stat_ordergoods['avggoodsprice']) ? $t : 0); unset($stat_ordergoods); //商品总数、收藏量 $goods_list = $model->statByGoods(array('store_id' => $_SESSION['store_id'], 'is_virtual' => 0), 'COUNT(*) as goodsnum, SUM(goods_collect) as gcollectnum'); $statnew_arr['goodsnum'] = ($t = $goods_list[0]['goodsnum']) > 0 ? $t : 0; $statnew_arr['gcollectnum'] = ($t = $goods_list[0]['gcollectnum']) > 0 ? $t : 0; //店铺收藏量 $store_list = $model->getoneByStore(array('store_id' => $_SESSION['store_id']), 'store_collect'); $statnew_arr['store_collect'] = ($t = $store_list['store_collect']) > 0 ? $t : 0; /* * 销售走势 */ //构造横轴数据 for ($i = $stime; $i < $etime; $i += 86400) { //当前数据的时间 $timetext = date('n', $i) . '-' . date('j', $i); //统计图数据 $stat_list[$timetext] = 0; //横轴 $stat_arr['xAxis']['categories'][] = $timetext; } $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['store_id'] = $_SESSION['store_id']; $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' order_add_time,SUM(order_amount) as orderamount,MONTH(FROM_UNIXTIME(order_add_time)) as monthval,DAY(FROM_UNIXTIME(order_add_time)) as dayval '; $stat_order = $model->statByStatorder($where, $field, 0, 0, '', 'monthval,dayval'); if ($stat_order) { foreach ($stat_order as $k => $v) { $stat_list[$v['monthval'] . '-' . $v['dayval']] = floatval($v['orderamount']); } } $stat_arr['legend']['enabled'] = false; $stat_arr['series'][0]['name'] = '下单金额'; $stat_arr['series'][0]['data'] = array_values($stat_list); //得到统计图数据 $stat_arr['title'] = '最近30天销售走势'; $stat_arr['yAxis'] = '下单金额'; $stattoday_json = getStatData_LineLabels($stat_arr); unset($stat_arr); /* * 7日内商品销售TOP30 */ $stime = $stat_time - 86400 * 6; //7天前0点 $etime = $stat_time + 86400 - 1; //今天24点 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['store_id'] = $_SESSION['store_id']; $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' sum(goods_num) as ordergoodsnum, goods_id, goods_name '; $goodstop30_arr = $model->statByStatordergoods($where, $field, 0, 30, 'ordergoodsnum desc', 'goods_id'); /** * 7日内同行热卖商品 */ $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $where['store_id'] = array('neq', $_SESSION['store_id']); if (!checkPlatformStore()) { //如果不是平台店铺,则查询店铺经营类目的同行数据 //查询店铺经营类目 $store_bindclass = Model('store_bind_class')->getStoreBindClassList(array('store_id' => $_SESSION['store_id'])); $goodsclassid_arr = array(); foreach ((array) $store_bindclass as $k => $v) { if (intval($v['class_3']) > 0) { $goodsclassid_arr[3][] = intval($v['class_3']); } elseif (intval($v['class_2']) > 0) { $goodsclassid_arr[2][] = intval($v['class_2']); } elseif (intval($v['class_1']) > 0) { $goodsclassid_arr[1][] = intval($v['class_1']); } } //拼接商品分类条件 if ($goodsclassid_arr) { ksort($goodsclassid_arr); $gc_parentidwhere_keyarr = array(); $gc_parentidwhere_arr = array(); foreach ((array) $goodsclassid_arr as $k => $v) { $gc_parentidwhere_keyarr[] = 'gc_parentid_' . $k; $gc_parentidwhere_arr[] = array('in', $goodsclassid_arr[$k]); } if (count($gc_parentidwhere_keyarr) == 1) { $where[$gc_parentidwhere_keyarr[0]] = $gc_parentidwhere_arr[0]; } else { $gc_parentidwhere_arr['_multi'] = '1'; $where[implode('|', $gc_parentidwhere_keyarr)] = $gc_parentidwhere_arr; } } } $field = ' sum(goods_num) as ordergoodsnum, goods_id, goods_name '; $othergoodstop30_arr = $model->statByStatordergoods($where, $field, 0, 30, 'ordergoodsnum desc', 'goods_id'); Tpl::output('goodstop30_arr', $goodstop30_arr); Tpl::output('othergoodstop30_arr', $othergoodstop30_arr); Tpl::output('stattoday_json', $stattoday_json); Tpl::output('statnew_arr', $statnew_arr); Tpl::output('stat_time', $stat_time); Tpl::showpage('stat.general.index'); }
/** * 新增会员 */ public function newmemberOp() { if (!$this->search_arr['search_type']) { $this->search_arr['search_type'] = 'day'; } $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['member_time'] = array('between', array($stime, $etime)); $field .= ' ,DAY(FROM_UNIXTIME(member_time)) as dayval,HOUR(FROM_UNIXTIME(member_time)) as hourval '; $memberlist = $model->statByMember($where, $field, 0, '', 'DAY(FROM_UNIXTIME(member_time)),HOUR(FROM_UNIXTIME(member_time))'); 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'] = '昨天'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '今天'; $stat_arr['series'][1]['data'] = array_values($curr_arr); //统计数据标题 $statlist['headertitle'] = array('小时', '昨天', '今天', '同比'); Tpl::output('actionurl', 'index.php?act=stat_member&op=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['member_time'] = array('between', array($stime, $etime)); $field .= ',WEEKOFYEAR(FROM_UNIXTIME(member_time)) as weekval,WEEKDAY(FROM_UNIXTIME(member_time))+1 as dayofweekval '; $memberlist = $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'] = '上周'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本周'; $stat_arr['series'][1]['data'] = array_values($curr_arr); //统计数据标题 $statlist['headertitle'] = array('星期', '上周', '本周', '同比'); Tpl::output('actionurl', 'index.php?act=stat_member&op=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['member_time'] = array('between', array($stime, $etime)); $field .= ',MONTH(FROM_UNIXTIME(member_time)) as monthval,day(FROM_UNIXTIME(member_time)) as dayval '; $memberlist = $model->statByMember($where, $field, 0, '', 'MONTH(FROM_UNIXTIME(member_time)),day(FROM_UNIXTIME(member_time))'); 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'] = '上月'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本月'; $stat_arr['series'][1]['data'] = array_values($curr_arr); //统计数据标题 $statlist['headertitle'] = array('日期', '上月', '本月', '同比'); Tpl::output('actionurl', 'index.php?act=stat_member&op=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 ($_GET['exporttype'] == 'excel') { //导出Excel import('libraries.excel'); $excel_obj = new 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' => '总计'); $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('新增会员统计', CHARSET)); $excel_obj->generateXML($excel_obj->charset('新增会员统计', CHARSET) . date('Y-m-d-H', time())); exit; } else { //得到统计图数据 $stat_arr['title'] = '新增会员统计'; $stat_arr['yAxis'] = '新增会员数'; $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json', $stat_json); Tpl::output('statlist', $statlist); Tpl::output('count_arr', $count_arr); Tpl::output('top_link', $this->sublink($this->links, 'newmember')); Tpl::showpage('stat.newmember'); } }
/** * 退款统计 */ public function refundOp() { $where = array(); if (!$this->search_arr['search_type']) { $this->search_arr['search_type'] = 'day'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); $field = ' SUM(refund_amount) as amount '; if ($this->search_arr['search_type'] == 'day') { //构造横轴数据 for ($i = 0; $i < 24; $i++) { $stat_arr['xAxis']['categories'][] = "{$i}"; $statlist[$i] = 0; } $field .= ' ,HOUR(FROM_UNIXTIME(add_time)) as timeval '; } if ($this->search_arr['search_type'] == 'week') { //构造横轴数据 for ($i = 1; $i <= 7; $i++) { $tmp_weekarr = getSystemWeekArr(); //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); $statlist[$i] = 0; } $field .= ' ,WEEKDAY(FROM_UNIXTIME(add_time))+1 as timeval '; } if ($this->search_arr['search_type'] == 'month') { //计算横轴的最大量(由于每个月的天数不同) $dayofmonth = date('t', $searchtime_arr[0]); //构造横轴数据 for ($i = 1; $i <= $dayofmonth; $i++) { //横轴 $stat_arr['xAxis']['categories'][] = $i; $statlist[$i] = 0; } $field .= ' ,day(FROM_UNIXTIME(add_time)) as timeval '; } $where = array(); $where['add_time'] = array('between', $searchtime_arr); $statlist_tmp = $model->statByRefundreturn($where, $field, 0, 0, 'timeval asc', 'timeval'); if ($statlist_tmp) { foreach ((array) $statlist_tmp as $k => $v) { $statlist[$v['timeval']] = floatval($v['amount']); } } //得到统计图数据 $stat_arr['legend']['enabled'] = false; $stat_arr['series'][0]['name'] = '退款金额'; $stat_arr['series'][0]['data'] = array_values($statlist); $stat_arr['title'] = '退款金额统计'; $stat_arr['yAxis'] = '金额'; $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json', $stat_json); Tpl::output('searchtime', implode('|', $searchtime_arr)); Tpl::output('top_link', $this->sublink($this->links, 'refund')); Tpl::showpage('stat.aftersale.refund'); }
/** * 促销分析 */ public function generalOp() { $model = Model('stat'); //统计的日期0点 $stat_time = strtotime(date('Y-m-d', time())) - 86400; /* * 昨日最新情报 */ $stime = $stat_time; $etime = $stat_time + 86400 - 1; $statnew_arr = array(); //查询订单表下单量、下单金额、下单客户数、平均客单价 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' COUNT(*) as ordernum, SUM(order_amount) as orderamount, COUNT(DISTINCT buyer_id) as ordermembernum, AVG(order_amount) as orderavg '; $stat_order = $model->getoneByStatorder($where, $field); $statnew_arr['ordernum'] = ($t = $stat_order['ordernum']) ? $t : 0; $statnew_arr['orderamount'] = ncPriceFormat(($t = $stat_order['orderamount']) ? $t : 0); $statnew_arr['ordermembernum'] = ($t = $stat_order['ordermembernum']) ? $t : 0; $statnew_arr['orderavg'] = ncPriceFormat(($t = $stat_order['orderavg']) ? $t : 0); unset($stat_order); //查询订单商品表下单商品数 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' SUM(goods_num) as ordergoodsnum,AVG(goods_pay_price/goods_num) as priceavg '; $stat_ordergoods = $model->getoneByStatordergoods($where, $field); $statnew_arr['ordergoodsnum'] = ($t = $stat_ordergoods['ordergoodsnum']) ? $t : 0; $statnew_arr['priceavg'] = ncPriceFormat(($t = $stat_ordergoods['priceavg']) ? $t : 0); unset($stat_ordergoods); //新增会员数 $where = array(); $where['member_time'] = array('between', array($stime, $etime)); $field = ' COUNT(*) as newmember '; $stat_member = $model->getoneByMember($where, $field); $statnew_arr['newmember'] = ($t = $stat_member['newmember']) ? $t : 0; unset($stat_member); //会员总数 $where = array(); $field = ' COUNT(*) as membernum '; $stat_member = $model->getoneByMember($where, $field); $statnew_arr['membernum'] = ($t = $stat_member['membernum']) ? $t : 0; unset($stat_member); //新增店铺 $where = array(); $where['store_time'] = array('between', array($stime, $etime)); $field = ' COUNT(*) as newstore '; $stat_store = $model->getoneByStore($where, $field); $statnew_arr['newstore'] = ($t = $stat_store['newstore']) ? $t : 0; unset($stat_store); //店铺总数 $where = array(); $field = ' COUNT(*) as storenum '; $stat_store = $model->getoneByStore($where, $field); $statnew_arr['storenum'] = ($t = $stat_store['storenum']) ? $t : 0; unset($stat_store); //新增商品,商品总数 $goods_list = $model->statByGoods(array('is_virtual' => 0), "COUNT(*) as goodsnum, SUM(IF(goods_addtime>={$stime} and goods_addtime<={$etime},1,0)) as newgoods"); $statnew_arr['goodsnum'] = ($t = $goods_list[0]['goodsnum']) > 0 ? $t : 0; $statnew_arr['newgoods'] = ($t = $goods_list[0]['newgoods']) > 0 ? $t : 0; /* * 昨日销售走势 */ //构造横轴数据 for ($i = 0; $i < 24; $i++) { //统计图数据 $curr_arr[$i] = 0; //今天 $up_arr[$i] = 0; //昨天 //横轴 $stat_arr['xAxis']['categories'][] = "{$i}"; } $stime = $stat_time - 86400; //昨天0点 $etime = $stat_time + 86400 - 1; //今天24点 $yesterday_day = @date('d', $stime); //昨天日期 $today_day = @date('d', $etime); //今天日期 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' SUM(order_amount) as orderamount,DAY(FROM_UNIXTIME(order_add_time)) as dayval,HOUR(FROM_UNIXTIME(order_add_time)) as hourval '; $stat_order = $model->statByStatorder($where, $field, 0, 0, '', 'dayval,hourval'); if ($stat_order) { foreach ($stat_order as $k => $v) { if ($today_day == $v['dayval']) { $curr_arr[$v['hourval']] = intval($v['orderamount']); } if ($yesterday_day == $v['dayval']) { $up_arr[$v['hourval']] = intval($v['orderamount']); } } } $stat_arr['series'][0]['name'] = '昨天'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '今天'; $stat_arr['series'][1]['data'] = array_values($curr_arr); //得到统计图数据 $stat_arr['title'] = date('Y-m-d', $stat_time) . '销售走势'; $stat_arr['yAxis'] = '销售额'; $stattoday_json = getStatData_LineLabels($stat_arr); unset($stat_arr); /* * 7日内店铺销售TOP30 */ $stime = $stat_time - 86400 * 6; //7天前0点 $etime = $stat_time + 86400 - 1; //今天24点 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' SUM(order_amount) as orderamount, store_id, store_name '; $storetop30_arr = $model->statByStatorder($where, $field, 0, 0, 'orderamount desc', 'store_id'); /* * 7日内商品销售TOP30 */ $stime = $stat_time - 86400 * 6; //7天前0点 $etime = $stat_time + 86400 - 1; //今天24点 $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', array($stime, $etime)); $field = ' sum(goods_num) as ordergoodsnum, goods_id, goods_name '; $goodstop30_arr = $model->statByStatordergoods($where, $field, 0, 30, 'ordergoodsnum desc', 'goods_id'); Tpl::output('goodstop30_arr', $goodstop30_arr); Tpl::output('storetop30_arr', $storetop30_arr); Tpl::output('stattoday_json', $stattoday_json); Tpl::output('statnew_arr', $statnew_arr); Tpl::output('stat_time', $stat_time); Tpl::output('top_link', $this->sublink($this->links, 'general')); Tpl::showpage('stat.general.index'); }
/** * 促销销售趋势分析 */ public function promotiontrendOp() { //优惠类型数组 $goodstype_arr = array(2 => '抢购', 3 => '限时折扣', 4 => '优惠套装'); $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['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', $searchtime_arr); $where['goods_type'] = array('in', array(2, 3, 4)); $field = ' goods_type'; switch ($this->search_arr['stattype']) { case 'orderamount': $field .= " ,SUM(goods_pay_price) as orderamount"; $caption = '下单金额'; break; case 'goodsnum': $field .= " ,SUM(goods_num) as goodsnum"; $caption = '下单商品数'; break; default: $field .= " ,count(DISTINCT order_id) as ordernum"; $caption = '下单量'; break; } if ($this->search_arr['search_type'] == 'day') { //构造横轴数据 for ($i = 0; $i < 24; $i++) { //横轴 $stat_arr['xAxis']['categories'][] = "{$i}"; foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,HOUR(FROM_UNIXTIME(order_add_time)) as timeval '; } if ($this->search_arr['search_type'] == 'week') { //构造横轴数据 for ($i = 1; $i <= 7; $i++) { $tmp_weekarr = getSystemWeekArr(); //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,WEEKDAY(FROM_UNIXTIME(order_add_time))+1 as timeval '; } if ($this->search_arr['search_type'] == 'month') { //计算横轴的最大量(由于每个月的天数不同) $dayofmonth = date('t', $searchtime_arr[0]); //构造横轴数据 for ($i = 1; $i <= $dayofmonth; $i++) { //横轴 $stat_arr['xAxis']['categories'][] = $i; foreach ($goodstype_arr as $k => $v) { $statlist[$k][$i] = 0; } } $field .= ' ,day(FROM_UNIXTIME(order_add_time)) as timeval '; } //查询数据 $statlist_tmp = $model->statByStatordergoods($where, $field, 0, 0, 'timeval', 'goods_type,timeval'); //整理统计数组 if ($statlist_tmp) { foreach ($statlist_tmp as $k => $v) { //将数据按照不同的促销方式分组 foreach ($goodstype_arr as $t_k => $t_v) { if ($t_k == $v['goods_type']) { switch ($this->search_arr['stattype']) { case 'orderamount': $statlist[$t_k][$v['timeval']] = round($v[$this->search_arr['stattype']], 2); break; case 'goodsnum': $statlist[$t_k][$v['timeval']] = intval($v[$this->search_arr['stattype']]); break; default: $statlist[$t_k][$v['timeval']] = intval($v[$this->search_arr['stattype']]); break; } } } } } foreach ($goodstype_arr as $k => $v) { $tmp = array(); $tmp['name'] = $v; $tmp['data'] = array_values($statlist[$k]); $stat_arr['series'][] = $tmp; } //得到统计图数据 $stat_arr['title'] = $caption . '统计'; $stat_arr['yAxis'] = $caption; $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json', $stat_json); Tpl::output('stattype', $_GET['stattype']); Tpl::showpage('stat.linelabels', 'null_layout'); }
/** * 订单统计 */ public function saleOp() { $where = array(); if (trim($_GET['order_type']) != '') { $where['order_state'] = trim($_GET['order_type']); } if (trim($_GET['stat_type']) == 'sale') { $field = ' sum(order_amount) as allnum '; } else { $field = ' count(*) as allnum '; } if (!$_REQUEST['search_type']) { $_REQUEST['search_type'] = 'day'; } if (trim($_GET['store_name']) != '') { $where['store_name'] = trim($_GET['store_name']); $store_name = trim($_GET['store_name']); } //初始化时间 //天 if (!$_REQUEST['search_time']) { $_REQUEST['search_time'] = date('Y-m-d', time()); } $search_time = strtotime($_REQUEST['search_time']); //搜索的时间 Tpl::output('search_time', $_REQUEST['search_time']); //周 if (!$_REQUEST['search_time_year']) { $_REQUEST['search_time_year'] = date('Y', time()); } if (!$_REQUEST['search_time_month']) { $_REQUEST['search_time_month'] = date('m', time()); } if (!$_REQUEST['search_time_week']) { $_REQUEST['search_time_week'] = implode('|', getWeek_SdateAndEdate(time())); } $current_year = $_REQUEST['search_time_year']; $current_month = $_REQUEST['search_time_month']; $current_week = $_REQUEST['search_time_week']; $year_arr = getSystemYearArr(); $month_arr = getSystemMonthArr(); $week_arr = getMonthWeekArr($current_year, $current_month); Tpl::output('current_year', $current_year); Tpl::output('current_month', $current_month); Tpl::output('current_week', $current_week); Tpl::output('year_arr', $year_arr); Tpl::output('month_arr', $month_arr); Tpl::output('week_arr', $week_arr); $model = Model('stat'); $statlist = array(); //统计数据列表 $sum_num = 0; //总数统计 if ($_REQUEST['search_type'] == 'day') { //构造横轴数据 for ($i = 0; $i < 24; $i++) { //统计图数据 $curr_arr[$i] = 0; //今天 $up_arr[$i] = 0; //昨天 //统计表数据 $uplist_arr[$i]['timetext'] = $i; $currlist_arr[$i]['timetext'] = $i; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = "{$i}"; } $stime = $search_time - 86400; //昨天0点 $etime = $search_time + 86400 - 1; //今天24点 $today_day = @date('d', $search_time); //今天日期 $yesterday_day = @date('d', $stime); //昨天日期 $where['add_time'] = array('between', array($stime, $etime)); $field .= ' ,DAY(FROM_UNIXTIME(add_time)) as dayval,HOUR(FROM_UNIXTIME(add_time)) as hourval '; $memberlist = $model->getStoreSaleStatList($where, $field, 0, '', 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']); } if ($yesterday_day == $v['dayval']) { $up_arr[$v['hourval']] = intval($v['allnum']); $uplist_arr[$v['hourval']]['val'] = intval($v['allnum']); } } } elseif (trim($_GET['store_name']) != '') { Tpl::output('data_null', 'yes'); } $stat_arr['series'][0]['name'] = '昨天'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '今天'; $stat_arr['series'][1]['data'] = array_values($curr_arr); Tpl::output('actionurl', 'index.php?act=stat_trade&op=sale&search_type=day&search_time=' . date('Y-m-d', $search_time)); } if ($_REQUEST['search_type'] == 'week') { $current_weekarr = explode('|', $current_week); $stime = strtotime($current_weekarr[0]) - 86400 * 7; $etime = strtotime($current_weekarr[1]) + 86400 - 1; $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(); //统计表数据 $uplist_arr[$i]['timetext'] = $tmp_weekarr[$i]; $currlist_arr[$i]['timetext'] = $tmp_weekarr[$i]; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); } $where['add_time'] = array('between', array($stime, $etime)); $field .= ',WEEKOFYEAR(FROM_UNIXTIME(add_time)) as weekval,DAYOFWEEK(FROM_UNIXTIME(add_time)) as dayofweekval '; $memberlist = $model->getStoreSaleStatList($where, $field, 0, '', 0, 'weekval,dayofweekval'); if ($memberlist) { foreach ($memberlist as $k => $v) { if ($up_week == $v['weekval']) { $up_arr[$v['dayofweekval']] = intval($v['allnum']); $uplist_arr[$v['dayofweekval']]['val'] = intval($v['allnum']); } if ($curr_week == $v['weekval']) { $curr_arr[$v['dayofweekval']] = intval($v['allnum']); $currlist_arr[$v['dayofweekval']]['val'] = intval($v['allnum']); } } } elseif (trim($_GET['store_name']) != '') { Tpl::output('data_null', 'yes'); } $stat_arr['series'][0]['name'] = '上周'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本周'; $stat_arr['series'][1]['data'] = array_values($curr_arr); Tpl::output('actionurl', 'index.php?act=stat_trade&op=sale&search_type=week&search_time_year=' . $current_year . '&search_time_month=' . $current_month . '&search_time_week=' . $current_week); } if ($_REQUEST['search_type'] == 'month') { $stime = strtotime($current_year . '-' . $current_month . "-01 -1 month"); $etime = getMonthLastDay($current_year, $current_month) + 86400 - 1; $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; //统计表数据 $uplist_arr[$i]['timetext'] = $i; $currlist_arr[$i]['timetext'] = $i; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = $i; } $where['add_time'] = array('between', array($stime, $etime)); $field .= ',MONTH(FROM_UNIXTIME(add_time)) as monthval,day(FROM_UNIXTIME(add_time)) as dayval '; $memberlist = $model->getStoreSaleStatList($where, $field, 0, '', 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']); } if ($curr_month == $v['monthval']) { $curr_arr[$v['dayval']] = intval($v['allnum']); $currlist_arr[$v['dayval']]['val'] = intval($v['allnum']); } } } elseif (trim($_GET['store_name']) != '') { Tpl::output('data_null', 'yes'); } $stat_arr['series'][0]['name'] = '上月'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本月'; $stat_arr['series'][1]['data'] = array_values($curr_arr); Tpl::output('actionurl', 'index.php?act=stat_trade&op=sale&search_type=month&search_time_year=' . $current_year . '&search_time_month=' . $current_month); } //统计数据标题 $statlist['headertitle'] = array('订单号', '买家', '店铺名称', '下单时间', '订单总额', '订单状态'); //导出Excel if ($_GET['exporttype'] == 'excel') { //导出Excel import('libraries.excel'); $excel_obj = new 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); } $order_all_list = $model->getStoreOrderList($where, false); //data foreach ($order_all_list as $k => $v) { $excel_data[$k + 1][] = array('data' => $v['order_sn']); $excel_data[$k + 1][] = array('data' => $v['buyer_name']); $excel_data[$k + 1][] = array('data' => $v['store_name']); $excel_data[$k + 1][] = array('data' => date('Y-m-d H:i:s', $v['add_time'])); $excel_data[$k + 1][] = array('data' => number_format($v['order_amount'], 2)); switch ($v['order_state']) { case ORDER_STATE_CANCEL: $excel_data[$k + 1][] = array('data' => '已取消'); break; case ORDER_STATE_NEW: $excel_data[$k + 1][] = array('data' => '待付款'); break; case ORDER_STATE_PAY: $excel_data[$k + 1][] = array('data' => '待发货'); break; case ORDER_STATE_SEND: $excel_data[$k + 1][] = array('data' => '待收货'); break; case ORDER_STATE_SUCCESS: $excel_data[$k + 1][] = array('data' => '交易完成'); break; } } $excel_data = $excel_obj->charset($excel_data, CHARSET); $excel_obj->addArray($excel_data); $excel_obj->addWorksheet($excel_obj->charset('订单统计', CHARSET)); $excel_obj->generateXML($excel_obj->charset('订单统计', CHARSET) . date('Y-m-d-H', time())); exit; } else { $order_list = $model->getStoreOrderList($where); //得到统计图数据 if (trim($_GET['stat_type']) == 'sale') { $stat_arr['title'] = '订单销售额统计'; $stat_arr['yAxis'] = '订单销售额'; } else { $stat_arr['title'] = '订单量统计'; $stat_arr['yAxis'] = '订单量'; } $stat_json = getStatData_LineLabels($stat_arr); //总数统计 $amount = $model->getStoreSaleStatList($where, ' count(*) as allnum '); $sale = $model->getStoreSaleStatList($where, ' sum(order_amount) as allnum '); Tpl::output('sum_data', array($amount[0]['allnum'], $sale[0]['allnum'])); Tpl::output('stat_json', $stat_json); Tpl::output('statlist', $statlist); Tpl::output('order_list', $order_list); Tpl::output('show_page', $model->showpage()); Tpl::output('top_link', $this->sublink($this->links, 'sale')); Tpl::showpage('stat.sale'); } }
/** * 购买分析 */ public function buyingOp() { if (!$this->search_arr['search_type']) { $this->search_arr['search_type'] = 'day'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); /* * 客单价分布 */ $where = array(); $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', $searchtime_arr); $field = '1'; $pricerange_arr = ($t = trim(C('stat_orderpricerange'))) ? unserialize($t) : ''; if ($pricerange_arr) { $stat_arr['series'][0]['name'] = '下单量'; //设置价格区间最后一项,最后一项只有开始值没有结束值 $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 = $model->getoneByStatorder($where, $field); if ($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'] . '以上'; } //统计图数据 if ($orderlist['ordernum_' . $k]) { $stat_arr['series'][0]['data'][] = intval($orderlist['ordernum_' . $k]); } else { $stat_arr['series'][0]['data'][] = 0; } } } //得到统计图数据 $stat_arr['title'] = '客单价分布'; $stat_arr['legend']['enabled'] = false; $stat_arr['yAxis'] = '下单量'; $guestprice_statjson = getStatData_LineLabels($stat_arr); } else { $guestprice_statjson = ''; } unset($stat_arr); /* * 购买频次分析 */ //统计期间会员下单量 $where = array(); $where['statm_time'] = array('between', $searchtime_arr); $where['statm_ordernum'] = array('gt', 0); $field = 'COUNT(*) as countnum'; $countnum_arr = $model->getOneStatmember($where, $field); $countnum = intval($countnum_arr['countnum']); $member_arr = array(); for ($i = 0; $i < $countnum; $i += 1000) { //由于数据库底层的限制,所以每次查询1000条 $statmember_list = array(); $statmember_list = $model->statByStatmember($where, 'statm_memberid,statm_ordernum', 0, $i . ',1000', 'statm_id'); foreach ((array) $statmember_list as $k => $v) { $member_arr[$v['statm_memberid']] = intval($member_arr[$v['statm_memberid']]) + intval($v['statm_ordernum']); } } if ($member_arr) { //整理期间各个频次的下单客户数 $stattimes_arr = array(); for ($i = 1; $i <= 10; $i++) { $stattimes_arr[$i] = array('num' => 0, 'rate' => 0.0); if ($i >= 10) { $stattimes_arr[$i]['text'] = '期间购买10次以上'; } else { $stattimes_arr[$i]['text'] = "期间购买{$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['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', $searchtime_arr); $field = ' HOUR(FROM_UNIXTIME(order_add_time)) as hourval,COUNT(*) as ordernum '; $orderlist = $model->statByStatorder($where, $field, 0, 0, 'hourval asc', 'hourval'); $stat_arr = array(); $stat_arr['series'][0]['name'] = '下单量'; //构造横轴坐标 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'] = '购买时段分布'; $stat_arr['legend']['enabled'] = false; $stat_arr['yAxis'] = '下单量'; $hour_statjson = getStatData_LineLabels($stat_arr); Tpl::output('hour_statjson', $hour_statjson); Tpl::output('stattimes_arr', $stattimes_arr); Tpl::output('guestprice_statjson', $guestprice_statjson); Tpl::output('top_link', $this->sublink($this->links, 'buying')); Tpl::showpage('stat.buying'); }
/** * 销售走势 */ public function storesales_trendsOp() { $storeid = intval($_GET['storeid']); if ($storeid <= 0) { Tpl::output('stat_error', '走势图加载错误'); Tpl::showpage('stat.store.salestrends'); exit; } if (!$_GET['search_type']) { $_GET['search_type'] = 'day'; } $model = Model('stat'); $where = array(); $where['store_id'] = $storeid; $searchtime_arr_tmp = explode('|', $this->search_arr['t']); foreach ((array) $searchtime_arr_tmp as $k => $v) { $searchtime_arr[] = intval($v); } $where['order_isvalid'] = 1; //计入统计的有效订单 $where['order_add_time'] = array('between', $searchtime_arr); $field = " store_id,store_name,SUM(order_amount) as orderamount, COUNT(*) as ordernum, COUNT(DISTINCT buyer_id) as membernum"; $stat_arr = array('orderamount' => array(), 'ordernum' => array(), 'membernum' => array()); $statlist = array(); if ($_GET['search_type'] == 'day') { //构造横轴数据 for ($i = 0; $i < 24; $i++) { //横轴 foreach ($stat_arr as $k => $v) { $stat_arr[$k]['xAxis']['categories'][] = "{$i}"; $statlist[$k][$i] = 0; } } $field .= ' ,HOUR(FROM_UNIXTIME(order_add_time)) as timeval '; } if ($_GET['search_type'] == 'week') { //构造横轴数据 for ($i = 1; $i <= 7; $i++) { $tmp_weekarr = getSystemWeekArr(); //横轴 foreach ($stat_arr as $k => $v) { $stat_arr[$k]['xAxis']['categories'][] = $tmp_weekarr[$i]; $statlist[$k][$i] = 0; } unset($tmp_weekarr); } $field .= ' ,WEEKDAY(FROM_UNIXTIME(order_add_time))+1 as timeval '; } if ($_GET['search_type'] == 'month') { //计算横轴的最大量(由于每个月的天数不同) $dayofmonth = date('t', $searchtime_arr[0]); //构造横轴数据 for ($i = 1; $i <= $dayofmonth; $i++) { //横轴 foreach ($stat_arr as $k => $v) { $stat_arr[$k]['xAxis']['categories'][] = $i; $statlist[$k][$i] = 0; } } $field .= ' ,day(FROM_UNIXTIME(order_add_time)) as timeval '; } //查询数据 $statlist_tmp = $model->statByStatorder($where, $field, 0, '', 'timeval', 'timeval'); //整理统计数组 $storename = ''; if ($statlist_tmp) { foreach ($statlist_tmp as $k => $v) { $storename = $v['store_name']; foreach ($stat_arr as $t_k => $t_v) { if ($k == 'orderamount') { $statlist[$t_k][$v['timeval']] = round($v[$t_k], 2); } else { $statlist[$t_k][$v['timeval']] = intval($v[$t_k]); } } } } foreach ($stat_arr as $k => $v) { $stat_arr[$k]['legend']['enabled'] = false; switch ($k) { case 'orderamount': $caption = '下单金额'; break; case 'ordernum': $caption = '下单量'; break; default: $caption = '下单会员数'; break; } $stat_arr[$k]['series'][0]['name'] = $caption; $stat_arr[$k]['series'][0]['data'] = array_values($statlist[$k]); $stat_arr[$k]['title'] = $caption . '走势'; $stat_arr[$k]['yAxis'] = $caption; //得到统计图数据 $stat_json[$k] = getStatData_LineLabels($stat_arr[$k]); } Tpl::output('storename', $storename); Tpl::output('stat_json', $stat_json); Tpl::showpage('stat.store.salestrends', 'null_layout'); }
/** * 订单走势 */ public function sale_trendOp() { $model = Model('stat'); //存储参数 $this->search_arr = $_REQUEST; //默认统计当前数据 if (!$this->search_arr['search_type']) { $this->search_arr['search_type'] = 'day'; } $where = array(); if (trim($_GET['order_state']) != '') { $where['order_state'] = trim($_GET['order_state']); } if (trim($_GET['store_name']) != '') { $where['store_name'] = array('like', '%' . trim($_GET['store_name']) . '%'); } $stattype = trim($_GET['type']); if ($stattype == 'ordernum') { $field = ' COUNT(*) as ordernum '; $stat_arr['title'] = '订单量统计'; $stat_arr['yAxis'] = '订单量'; } else { $stattype = 'orderamount'; $field = ' SUM(order_amount) as orderamount '; $stat_arr['title'] = '订单销售额统计'; $stat_arr['yAxis'] = '订单销售额'; } $searchtime_arr_tmp = explode('|', $this->search_arr['t']); foreach ((array) $searchtime_arr_tmp as $k => $v) { $searchtime_arr[] = intval($v); } 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; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = "{$i}"; } $today_day = @date('d', $searchtime_arr[1]); //今天日期 $yesterday_day = @date('d', $searchtime_arr[0]); //昨天日期 $where['order_add_time'] = array('between', $searchtime_arr); $field .= ' ,DAY(FROM_UNIXTIME(order_add_time)) as dayval,HOUR(FROM_UNIXTIME(order_add_time)) as hourval '; $orderlist = $model->statByStatorder($where, $field, 0, 0, '', 'dayval,hourval'); foreach ((array) $orderlist as $k => $v) { if ($today_day == $v['dayval']) { $curr_arr[$v['hourval']] = intval($v[$stattype]); $currlist_arr[$v['hourval']]['val'] = $v[$stattype]; } if ($yesterday_day == $v['dayval']) { $up_arr[$v['hourval']] = intval($v[$stattype]); $uplist_arr[$v['hourval']]['val'] = $v[$stattype]; } } $stat_arr['series'][0]['name'] = '昨天'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '今天'; $stat_arr['series'][1]['data'] = array_values($curr_arr); } if ($this->search_arr['search_type'] == 'week') { $up_week = @date('W', $searchtime_arr[0]); //上周 $curr_week = @date('W', $searchtime_arr[1]); //本周 //构造横轴数据 for ($i = 1; $i <= 7; $i++) { //统计图数据 $up_arr[$i] = 0; $curr_arr[$i] = 0; $tmp_weekarr = getSystemWeekArr(); //统计表数据 $uplist_arr[$i]['timetext'] = $tmp_weekarr[$i]; $currlist_arr[$i]['timetext'] = $tmp_weekarr[$i]; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = $tmp_weekarr[$i]; unset($tmp_weekarr); } $where['order_add_time'] = array('between', $searchtime_arr); $field .= ',WEEKOFYEAR(FROM_UNIXTIME(order_add_time)) as weekval,WEEKDAY(FROM_UNIXTIME(order_add_time))+1 as dayofweekval '; $orderlist = $model->statByStatorder($where, $field, 0, 0, '', 'weekval,dayofweekval'); foreach ((array) $orderlist as $k => $v) { if ($up_week == $v['weekval']) { $up_arr[$v['dayofweekval']] = intval($v[$stattype]); $uplist_arr[$v['dayofweekval']]['val'] = intval($v[$stattype]); } if ($curr_week == $v['weekval']) { $curr_arr[$v['dayofweekval']] = intval($v[$stattype]); $currlist_arr[$v['dayofweekval']]['val'] = intval($v[$stattype]); } } $stat_arr['series'][0]['name'] = '上周'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本周'; $stat_arr['series'][1]['data'] = array_values($curr_arr); } if ($this->search_arr['search_type'] == 'month') { $up_month = date('m', $searchtime_arr[0]); $curr_month = date('m', $searchtime_arr[1]); //计算横轴的最大量(由于每个月的天数不同) $up_dayofmonth = date('t', $searchtime_arr[0]); $curr_dayofmonth = date('t', $searchtime_arr[1]); $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; $uplist_arr[$i]['val'] = 0; $currlist_arr[$i]['val'] = 0; //横轴 $stat_arr['xAxis']['categories'][] = $i; } $where['order_add_time'] = array('between', array($searchtime_arr[0], $searchtime_arr[1])); $field .= ',MONTH(FROM_UNIXTIME(order_add_time)) as monthval,day(FROM_UNIXTIME(order_add_time)) as dayval '; $orderlist = $model->statByStatorder($where, $field, 0, 0, '', 'monthval,dayval'); foreach ($orderlist as $k => $v) { if ($up_month == $v['monthval']) { $up_arr[$v['dayval']] = intval($v[$stattype]); $uplist_arr[$v['dayval']]['val'] = intval($v[$stattype]); } if ($curr_month == $v['monthval']) { $curr_arr[$v['dayval']] = intval($v[$stattype]); $currlist_arr[$v['dayval']]['val'] = intval($v[$stattype]); } } $stat_arr['series'][0]['name'] = '上月'; $stat_arr['series'][0]['data'] = array_values($up_arr); $stat_arr['series'][1]['name'] = '本月'; $stat_arr['series'][1]['data'] = array_values($curr_arr); } $stat_json = getStatData_LineLabels($stat_arr); Tpl::output('stat_json', $stat_json); Tpl::output('stattype', $stattype); Tpl::showpage('stat.linelabels', 'null_layout'); }
/** * 购买分析 */ public function buyingOp(){ if(!$this->search_arr['search_type']){ $this->search_arr['search_type'] = 'day'; } $model = Model('stat'); //获得搜索的开始时间和结束时间 $searchtime_arr = $model->getStarttimeAndEndtime($this->search_arr); /* * 客单价分布 */ $where = array(); $where['store_id'] = $_SESSION['store_id']; $where['order_isvalid'] = 1;//计入统计的有效订单 $where['order_add_time'] = array('between',$searchtime_arr); $field = '1'; $pricerange = Model('store_extend')->getfby_store_id($_SESSION['store_id'],'orderpricerange'); $pricerange_arr = $pricerange?unserialize($pricerange):array(); if ($pricerange_arr){ $stat_arr['series'][0]['name'] = '下单量'; //设置价格区间最后一项,最后一项只有开始值没有结束值 $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 = $model->getoneByStatorder($where, $field); if($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'].'以上'; } //统计图数据 if ($orderlist['ordernum_'.$k]){ $stat_arr['series'][0]['data'][] = intval($orderlist['ordernum_'.$k]); } else { $stat_arr['series'][0]['data'][] = 0; } } } //得到统计图数据 $stat_arr['title'] = '客单价分布'; $stat_arr['legend']['enabled'] = false; $stat_arr['yAxis'] = '下单量'; $guestprice_statjson = getStatData_LineLabels($stat_arr); } else { $guestprice_statjson = ''; } unset($stat_arr); //购买时段分布 $where = array(); $where['store_id'] = $_SESSION['store_id']; $where['order_isvalid'] = 1;//计入统计的有效订单 $where['order_add_time'] = array('between',$searchtime_arr); $field = ' HOUR(FROM_UNIXTIME(order_add_time)) as hourval,COUNT(*) as ordernum '; $orderlist = $model->statByStatorder($where, $field, 0, 0, 'hourval asc', 'hourval'); $stat_arr = array(); $stat_arr['series'][0]['name'] = '下单量'; //构造横轴坐标 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'] = '购买时段分布'; $stat_arr['legend']['enabled'] = false; $stat_arr['yAxis'] = '下单量'; $hour_statjson = getStatData_LineLabels($stat_arr); Tpl::output('hour_statjson',$hour_statjson); Tpl::output('guestprice_statjson',$guestprice_statjson); self::profile_menu('buying'); Tpl::showpage('stat.sale.buying'); }