function printDate($year, $mon)
{
//20130520
$montharr = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
$stand = "{$year}-{$mon}-";
//取得每个月的天数
foreach ($montharr as $key => $monthv) {
$daysarr[$key + 1] = getMonthLastDay($monthv, $year);
}
$tmp_mon = substr($mon, 0, 1);
if ($tmp_mon == "0") {
$tmp_mon = substr($mon, 1, 1);
}
for ($i = 1; $i <= $daysarr[$tmp_mon]; $i++) {
$time = strtotime($stand . $i);
$date[] = date("Ymd", $time);
}
return $date;
}
public function exportExcel()
{
$type = I('get.params');
$m = I('get.month');
$y = date("Y");
$startMonth = mktime(0, 0, 0, $m, 1, $y);
$endMonth = getMonthLastDay($y, $m);
//首行数据
$firstrow = array("id", "用户名", "真实姓名", "地区", "学校", "邮箱", "手机号码", "创建时间", "状态", "qq", "学历", "省份", "城市", "区县");
switch ($type) {
case 'all':
if (empty($m)) {
$data = M('user_seeker')->field('id,username,realname,area,school,email,phone,
createtime,state,qq,education,province,city,county')->order('createtime desc')->limit('5000')->select();
export($data, '注册用户数据表', 'user_seeker', $firstrow);
} else {
$data = M('user_seeker')->field('id,username,realname,area,school,email,phone,
createtime,state,qq,education,province,city,county')->order('createtime desc')->where("UNIX_TIMESTAMP(createtime)>={$startMonth} AND UNIX_TIMESTAMP(createtime)<{$endMonth}")->select();
export($data, $m . '月份注册用户数据表', 'user_seeker', $firstrow);
}
break;
case 'check':
if (empty($m)) {
$data = M('user_seeker')->field('id,username,realname,area,school,email,phone,
createtime,state,qq,education,province,city,county')->order('createtime desc')->where('state=1')->limit('5000')->select();
export($data, '认证用户表数据', 'user_seeker', $firstrow);
} else {
$data = M('user_seeker')->field('id,username,realname,area,school,email,phone,
createtime,state,qq,education,province,city,county')->where("UNIX_TIMESTAMP(createtime)>={$startMonth} AND UNIX_TIMESTAMP(createtime)<{$endMonth} AND state=1")->select();
export($data, $m . '月份认证用户表数据', 'user_seeker', $firstrow);
}
break;
case 'company':
$firstrow = array('id', '用户名', '公司名', '地址', '营业执照号码', '公司邮箱', '电话', '联系人', '联系人手机号', '公司类型', '注册时间');
$data = M('user_company')->field('id,username,companyname,address,buslicense,
companyemail,phone,contacter,contacterphone,companytype,createtime')->select();
export($data, '萝卜兼职企业表数据', 'user_company', $firstrow);
break;
}
}
/**
* 获得查询的开始和结束时间
*/
public function getStarttimeAndEndtime($search_arr)
{
if ($search_arr['search_type'] == 'day') {
$stime = $search_arr['day']['search_time'];
//今天0点
$etime = $search_arr['day']['search_time'] + 86400 - 1;
//今天24点
}
if ($search_arr['search_type'] == 'week') {
$current_weekarr = explode('|', $search_arr['week']['current_week']);
$stime = strtotime($current_weekarr[0]);
$etime = strtotime($current_weekarr[1]) + 86400 - 1;
}
if ($search_arr['search_type'] == 'month') {
$stime = strtotime($search_arr['month']['current_year'] . '-' . $search_arr['month']['current_month'] . "-01 0 month");
$etime = getMonthLastDay($search_arr['month']['current_year'], $search_arr['month']['current_month']) + 86400 - 1;
}
return array($stime, $etime);
}
/**
* 新增会员
*/
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 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 saleOp()
{
$model = Model('stat');
//存储参数
$this->search_arr = $_REQUEST;
//处理搜索时间
$this->search_arr = $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']);
Tpl::output('year_arr', $year_arr);
Tpl::output('month_arr', $month_arr);
Tpl::output('week_arr', $week_arr);
Tpl::output('search_arr', $this->search_arr);
//默认统计当前数据
if (!$this->search_arr['search_type']) {
$this->search_arr['search_type'] = 'day';
}
//计算昨天和今天时间
if ($this->search_arr['search_type'] == 'day') {
$stime = $this->search_arr['day']['search_time'] - 86400;
//昨天0点
$etime = $this->search_arr['day']['search_time'] + 86400 - 1;
//今天24点
$curr_stime = $this->search_arr['day']['search_time'];
//今天0点
} elseif ($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;
$curr_stime = strtotime($current_weekarr[0]);
//本周0点
} elseif ($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;
$curr_stime = strtotime($this->search_arr['month']['current_year'] . '-' . $this->search_arr['month']['current_month'] . "-01");
//本月0点
}
$where = array();
$where['order_add_time'] = array('between', array($curr_stime, $etime));
if (trim($_GET['order_type']) != '') {
$where['order_state'] = trim($_GET['order_type']);
}
if (trim($_GET['store_name']) != '') {
$where['store_name'] = array('like', '%' . trim($_GET['store_name']) . '%');
}
if ($_GET['exporttype'] == 'excel') {
$order_list = $model->statByStatorder($where, '', 0, 0, 'order_id desc', '');
} else {
$order_list = $model->statByStatorder($where, '', 10, 0, 'order_id desc', '');
}
//统计数据标题
$statlist = array();
$statlist['headertitle'] = array('订单号', '买家', '店铺名称', '下单时间', '订单总额', '订单状态');
foreach ((array) $order_list as $k => $v) {
switch ($v['order_state']) {
case ORDER_STATE_CANCEL:
$v['order_statetext'] = '已取消';
break;
case ORDER_STATE_NEW:
$v['order_statetext'] = '待付款';
break;
case ORDER_STATE_PAY:
$v['order_statetext'] = '待发货';
break;
case ORDER_STATE_SEND:
$v['order_statetext'] = '待收货';
break;
case ORDER_STATE_SUCCESS:
$v['order_statetext'] = '交易完成';
break;
}
$statlist['data'][$k] = $v;
}
//导出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 ((array) $statlist['data'] 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['order_add_time']));
$excel_data[$k + 1][] = array('data' => number_format($v['order_amount'], 2));
$excel_data[$k + 1][] = array('data' => $v['order_statetext']);
}
$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 {
//总数统计
$statcount_arr = $model->getoneByStatorder($where, ' COUNT(*) as ordernum, SUM(order_amount) as orderamount');
Tpl::output('statcount_arr', $statcount_arr);
Tpl::output('searchtime', implode('|', array($stime, $etime)));
Tpl::output('statlist', $statlist);
Tpl::output('show_page', $model->showpage());
Tpl::output('top_link', $this->sublink($this->links, 'sale'));
Tpl::showpage('stat.sale');
}
}
/**
* 店铺排行
*/
public function rankOp()
{
$where = array();
if (trim($_GET['order_type']) != '') {
$where['order_state'] = trim($_GET['order_type']);
}
if (!$_REQUEST['search_type']) {
$_REQUEST['search_type'] = 'day';
}
if (trim($_GET['store_name']) != '') {
$where['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');
if ($_REQUEST['search_type'] == 'day') {
$stime = $search_time;
//昨天0点
$etime = $search_time + 86400 - 1;
//今天24点
Tpl::output('actionurl', 'index.php?act=stat_store&op=rank&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;
Tpl::output('actionurl', 'index.php?act=stat_store&op=rank&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 0 month");
$etime = getMonthLastDay($current_year, $current_month) + 86400 - 1;
Tpl::output('actionurl', 'index.php?act=stat_store&op=rank&search_type=month&search_time_year=' . $current_year . '&search_time_month=' . $current_month);
}
$where['add_time'] = array('between', array($stime, $etime));
$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', "!(payment_code='offline' and order_state <> '" . ORDER_STATE_SUCCESS . "')");
//货到付款订单，订单成功之后才计入统计
//得到统计图数据
if (trim($_GET['stat_type']) == 'sale') {
$store_list = $model->getStoreSaleRank($where, 'sale_amount');
$statlist['headertitle'] = array('排名', '店铺名称', '销售额');
$stat_arr['title'] = '店铺销售额排行Top15';
$stat_arr['yAxis'] = '销售额';
$stat_arr['series'][0]['name'] = '销售额';
} else {
$store_list = $model->getStoreSaleRank($where, 'sale_num');
$statlist['headertitle'] = array('排名', '店铺名称', '订单量');
$stat_arr['title'] = '店铺订单量排行Top15';
$stat_arr['yAxis'] = '订单量';
$stat_arr['series'][0]['name'] = '订单量';
}
//导出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 ($store_list as $k => $v) {
$excel_data[$k + 1][] = array('data' => $k + 1);
$excel_data[$k + 1][] = array('data' => $v['store_name']);
$excel_data[$k + 1][] = array('data' => $v['allnum']);
}
$excel_data = $excel_obj->charset($excel_data, CHARSET);
$excel_obj->addArray($excel_data);
if (trim($_GET['stat_type']) == 'sale') {
$excel_obj->addWorksheet($excel_obj->charset('店铺销售额排行Top15', CHARSET));
$excel_obj->generateXML($excel_obj->charset('店铺销售额排行Top15', CHARSET) . date('Y-m-d-H', time()));
} else {
$excel_obj->addWorksheet($excel_obj->charset('店铺订单量排行Top15', CHARSET));
$excel_obj->generateXML($excel_obj->charset('店铺订单量排行Top15', CHARSET) . date('Y-m-d-H', time()));
}
exit;
} else {
$stat_arr['series'][0]['data'] = array();
for ($i = 0; $i < 15; $i++) {
$stat_arr['series'][0]['data'][] = array('name' => strval($store_list[$i]['store_name']), 'y' => floatval($store_list[$i]['allnum']));
}
//构造横轴数据
for ($i = 1; $i <= 15; $i++) {
//横轴
$stat_arr['xAxis']['categories'][] = $i;
}
$stat_arr['legend']['enabled'] = false;
$stat_json = getStatData_Column2D($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('store_list', $store_list);
Tpl::output('top_link', $this->sublink($this->links, 'rank'));
Tpl::showpage('stat.storerank');
}
}
/**
* 销售统计
*/
public function saleOp(){
$model = Model('stat');
//默认统计当前数据
if(!$this->search_arr['search_type']){
$this->search_arr['search_type'] = 'day';
}
//计算昨天和今天时间
if($this->search_arr['search_type'] == 'day'){
$stime = $this->search_arr['day']['search_time'] - 86400;//昨天0点
$etime = $this->search_arr['day']['search_time'] + 86400 - 1;//今天24点
$curr_stime = $this->search_arr['day']['search_time'];//今天0点
} elseif ($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;
$curr_stime = strtotime($current_weekarr[0]);//本周0点
} elseif ($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;
$curr_stime = strtotime($this->search_arr['month']['current_year'].'-'.$this->search_arr['month']['current_month']."-01");;//本月0点
}
$where = array();
$where['store_id'] = $_SESSION['store_id'];
$where['order_add_time'] = array('between',array($stime,$etime));
if(trim($_GET['order_type']) != ''){
$where['order_state'] = trim($_GET['order_type']);
}
//走势图
$field = ' COUNT(*) as ordernum,SUM(order_amount) as orderamount ';
$stat_arr = array();
//$searchtime_arr = array($stime,$etime);
if($this->search_arr['search_type'] == 'day'){
//构造横轴数据
for($i=0; $i<24; $i++){
//统计图数据
$curr_arr['orderamount'][$i] = 0;//今天
$up_arr['orderamount'][$i] = 0;//昨天
$curr_arr['ordernum'][$i] = 0;//今天
$up_arr['ordernum'][$i] = 0;//昨天
//统计表数据
$currlist_arr[$i]['timetext'] = $i;
$uplist_arr[$i]['val'] = 0;
$currlist_arr[$i]['val'] = 0;
//横轴
$stat_arr['orderamount']['xAxis']['categories'][] = "$i";
$stat_arr['ordernum']['xAxis']['categories'][] = "$i";
}
$today_day = @date('d', $etime);//今天日期
$yesterday_day = @date('d', $stime);//昨天日期
$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['ordernum'][$v['hourval']] = intval($v['ordernum']);
$curr_arr['orderamount'][$v['hourval']] = floatval($v['orderamount']);
$currlist_arr[$v['hourval']]['val'] = $v[$search_type];
}
if($yesterday_day == $v['dayval']){
$up_arr['ordernum'][$v['hourval']] = intval($v['ordernum']);
$up_arr['orderamount'][$v['hourval']] = floatval($v['orderamount']);
$uplist_arr[$v['hourval']]['val'] = $v[$search_type];
}
}
$stat_arr['ordernum']['series'][0]['name'] = '昨天';
$stat_arr['ordernum']['series'][0]['data'] = array_values($up_arr['ordernum']);
$stat_arr['ordernum']['series'][1]['name'] = '今天';
$stat_arr['ordernum']['series'][1]['data'] = array_values($curr_arr['ordernum']);
$stat_arr['orderamount']['series'][0]['name'] = '昨天';
$stat_arr['orderamount']['series'][0]['data'] = array_values($up_arr['orderamount']);
$stat_arr['orderamount']['series'][1]['name'] = '今天';
$stat_arr['orderamount']['series'][1]['data'] = array_values($curr_arr['orderamount']);
}
if($this->search_arr['search_type'] == 'week'){
$up_week = @date('W', $stime);//上周
$curr_week = @date('W', $etime);//本周
//构造横轴数据
for($i=1; $i<=7; $i++){
$tmp_weekarr = getSystemWeekArr();
//统计图数据
$up_arr['ordernum'][$i] = 0;
$curr_arr['ordernum'][$i] = 0;
$up_arr['orderamount'][$i] = 0;
$curr_arr['orderamount'][$i] = 0;
//横轴
$stat_arr['ordernum']['xAxis']['categories'][] = $tmp_weekarr[$i];
$stat_arr['orderamount']['xAxis']['categories'][] = $tmp_weekarr[$i];
//统计表数据
$uplist_arr[$i]['timetext'] = $tmp_weekarr[$i];
$currlist_arr[$i]['timetext'] = $tmp_weekarr[$i];
$uplist_arr[$i]['val'] = 0;
$currlist_arr[$i]['val'] = 0;
unset($tmp_weekarr);
}
$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['ordernum'][$v['dayofweekval']] = intval($v['ordernum']);
$up_arr['orderamount'][$v['dayofweekval']] = intval($v['orderamount']);
$uplist_arr[$v['dayofweekval']]['val'] = intval($v[$search_type]);
}
if ($curr_week == $v['weekval']){
$curr_arr['ordernum'][$v['dayofweekval']] = intval($v['ordernum']);
$curr_arr['orderamount'][$v['dayofweekval']] = intval($v['orderamount']);
$currlist_arr[$v['dayofweekval']]['val'] = intval($v[$search_type]);
}
}
$stat_arr['ordernum']['series'][0]['name'] = '上周';
$stat_arr['ordernum']['series'][0]['data'] = array_values($up_arr['ordernum']);
$stat_arr['ordernum']['series'][1]['name'] = '本周';
$stat_arr['ordernum']['series'][1]['data'] = array_values($curr_arr['ordernum']);
$stat_arr['orderamount']['series'][0]['name'] = '上周';
$stat_arr['orderamount']['series'][0]['data'] = array_values($up_arr['orderamount']);
$stat_arr['orderamount']['series'][1]['name'] = '本周';
$stat_arr['orderamount']['series'][1]['data'] = array_values($curr_arr['orderamount']);
}
if($this->search_arr['search_type'] == 'month'){
$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['ordernum'][$i] = 0;
$curr_arr['ordernum'][$i] = 0;
$up_arr['orderamount'][$i] = 0;
$curr_arr['orderamount'][$i] = 0;
//横轴
$stat_arr['ordernum']['xAxis']['categories'][] = $i;
$stat_arr['orderamount']['xAxis']['categories'][] = $i;
//统计表数据
$currlist_arr[$i]['timetext'] = $i;
$uplist_arr[$i]['val'] = 0;
$currlist_arr[$i]['val'] = 0;
}
$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['ordernum'][$v['dayval']] = intval($v['ordernum']);
$up_arr['orderamount'][$v['dayval']] = floatval($v['orderamount']);
$uplist_arr[$v['dayval']]['val'] = intval($v[$search_type]);
}
if ($curr_month == $v['monthval']){
$curr_arr['ordernum'][$v['dayval']] = intval($v['ordernum']);
$curr_arr['orderamount'][$v['dayval']] = intval($v['orderamount']);
$currlist_arr[$v['dayval']]['val'] = intval($v[$search_type]);
}
}
$stat_arr['ordernum']['series'][0]['name'] = '上月';
$stat_arr['ordernum']['series'][0]['data'] = array_values($up_arr['ordernum']);
$stat_arr['ordernum']['series'][1]['name'] = '本月';
$stat_arr['ordernum']['series'][1]['data'] = array_values($curr_arr['ordernum']);
$stat_arr['orderamount']['series'][0]['name'] = '上月';
$stat_arr['orderamount']['series'][0]['data'] = array_values($up_arr['orderamount']);
$stat_arr['orderamount']['series'][1]['name'] = '本月';
$stat_arr['orderamount']['series'][1]['data'] = array_values($curr_arr['orderamount']);
}
$stat_arr['ordernum']['title'] = '订单量统计';
$stat_arr['ordernum']['yAxis'] = '订单量';
$stat_arr['orderamount']['title'] = '下单金额统计';
$stat_arr['orderamount']['yAxis'] = '下单金额';
$stat_json['ordernum'] = getStatData_LineLabels($stat_arr['ordernum']);
$stat_json['orderamount'] = getStatData_LineLabels($stat_arr['orderamount']);
Tpl::output('stat_json',$stat_json);
Tpl::output('stattype',$search_type);
//总数统计
$where['order_add_time'] = array('between',array($curr_stime,$etime));
$statcount_arr = $model->getoneByStatorder($where,' COUNT(*) as ordernum, SUM(order_amount) as orderamount');
$statcount_arr['ordernum'] = ($t = intval($statcount_arr['ordernum'])) > 0?$t:0;
$statcount_arr['orderamount'] = ncPriceFormat(($t = floatval($statcount_arr['orderamount'])) > 0?$t:0);
Tpl::output('statcount_arr',$statcount_arr);
Tpl::output('searchtime',implode('|',array($curr_stime,$etime)));
Tpl::output('show_page',$model->showpage(2));
self::profile_menu('sale');
Tpl::showpage('stat.sale.index');
}
