<?php
/*
* 订单来源统计
* edit by raoyonggang 2014.3.18
*/
define('IN_ECS',true);
require(dirname(__FILE__) .'/includes/init.php');
require(dirname(__FILE__) .'/includes/ERP/lib_erp_base.php');
require_once(ROOT_PATH . 'includes/lib_order.php');
require_once(ROOT_PATH . 'includes/lib_goods.php');
require_once("PHPExcel.php");
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Writer/Excel5.php';
require(dirname(__FILE__) .'/includes/ERP/cls/cls_date.php');
$request_year=isset($_REQUEST['year']) ? intval($_REQUEST['year']) : '2014';
if (empty($_REQUEST['act'])) {
$_REQUEST['act'] = 'list';
}
if($_REQUEST['act'] == 'month'){
$start_time=strtotime($request_year.'-'.$_REQUEST['s_d'].'-01')-3600*8;
$end_time=strtotime($request_year.'-'.$_REQUEST['e_d'].'-01')-3600*8;
if($_REQUEST['s_d'] == 12){
$year=$request_year+1;
$end_time=strtotime($year.'-01-01')-3600*8;
}
$se_time = $request_year.'-'.$_REQUEST['s_d'].'(总)';
$where = " AND add_time >='{$start_time}' AND add_time < '{$end_time}'";
$result = count_order_from($where,$se_time);
$smarty->assign('start_date',$_REQUEST['s_d']);
$smarty->assign('year',$request_year);
$smarty->assign('end_date',$_REQUEST['e_d']);
$smarty->assign('order_from',$result);
$smarty->display('order_refer.htm');
}elseif($_REQUEST['act'] == 'export'){
$request_year=isset($_REQUEST['year']) ? intval($_REQUEST['year']) : '2014';
$start_time=strtotime($request_year.'-'.$_REQUEST['s_d'].'-01')-3600*8;
$end_time=strtotime($request_year.'-'.$_REQUEST['e_d'].'-01')-3600*8;
if($_REQUEST['s_d'] == 12){
$year=$request_year+1;
$end_time=strtotime($year.'-01-01')-3600*8;
}
$se_time = $request_year.'-'.$_REQUEST['s_d'].'(总)';
$where = " AND add_time >= '{$start_time}' AND add_time < '{$end_time}'";
$result = count_order_from($where,$se_time);
ksort($result);
//导出订单
export_order_from($result,$request_year);
exit;
}
/*分析订单来源 */
function anaylse_refer($row,$order_refer){
if(strpos($row['referer'], '109')===0){
$order_refer['pc360']+=$row['sum'];
$order_refer['avg_pc360']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='114'){
$order_refer['pc_baidu']+=$row['sum'];
$order_refer['avg_pc_baidu']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='wap3-cps-114'){
$order_refer['wap_baidu']+=$row['sum'];
$order_refer['avg_wap_baidu']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif (empty($row['referer'])||$row['referer']=='本站'){
$order_refer['pc_regular']+=$row['sum'];
$order_refer['avg_pc_regular']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='wap3'){
$order_refer['wap_regular']+=$row['sum'];
$order_refer['avg_wap_regular']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='管理员添加(电话订单)'){
$order_refer['tel']+=$row['sum'];
$order_refer['avg_tel']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='管理员添加(短信订单)'){
$order_refer['message']+=$row['sum'];
$order_refer['avg_message']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='管理员添加(在线QQ订单)'){
$order_refer['qq']+=$row['sum'];
$order_refer['avg_qq']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif (strpos($row['referer'], '107')===0||$row['referer']=='wap3-cps-107'){
$order_refer['sougou']+=$row['sum'];
$order_refer['avg_sougou']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}elseif ($row['referer']=='wap3-cps-119'){
$order_refer['cps']+=$row['sum'];
$order_refer['avg_cps']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}else{
$order_refer['other']+=$row['sum'];
$order_refer['avg_other']+=$row['sum_price']+$row['sum_price2'];
return $order_refer;
}
}
/*
* 统计订单来源
* $where条件
* $flag统计订单来源的区别标识
* */
function count_order_from($where,$se_time=null){
$sql="select referer, DATE_FORMAT(FROM_UNIXTIME(add_time+28800),'%Y-%m-%d') as date, sum(order_amount) as sum_price,sum(money_paid) as sum_price2,count(1) as sum
from chun_order_info
where order_status in(1,5,6) $where
group by date,referer
order by order_id DESC";
$ress = $GLOBALS['db']->query($sql);
$result=array();
while ($row = $GLOBALS['db']->fetchRow($ress)) {
//统计整个月的订单来源
$result[$se_time]['sum_price']+=$row['sum_price']+$row['sum_price2'];
$result[$se_time]['sum']+=$row['sum'];
$result[$se_time]=anaylse_refer($row,$result[$se_time]);
//统计每个月份当中的每天的订单来源
$result[$row['date']]['sum_price']+=$row['sum_price']+$row['sum_price2'];
$result[$row['date']]['sum']+=$row['sum'];
$result[$row['date']]=anaylse_refer($row,$result[$row['date']]);
}
krsort($result);
//处理均单价
foreach ($result as $ke=>&$val){
$val['sum_price']=ceil($val['sum_price']);
foreach ($val as $key=>&$value){
if($key=='avg_pc_baidu'){
$temp='<span class="red">'.$val['pc_baidu'].'</span> | <span class="my_gray">'.ceil($value);
$val['pc_baidu_price']=ceil($value);
$value=ceil($value/$val['pc_baidu']);
$val['combine_pc_baidu']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_wap_baidu'){
$temp='<span class="red">'.$val['wap_baidu'].'</span> | <span class="my_gray">'.ceil($value);
$val['wap_baidu_price']=ceil($value);
$value=ceil($value/$val['wap_baidu']);
$val['combine_wap_baidu']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_pc_regular'){
$temp='<span class="red">'.$val['pc_regular'].'</span> | <span class="my_gray">'.ceil($value);
$val['pc_regular_price']=ceil($value);
$value=ceil($value/$val['pc_regular']);
$val['combine_pc_regular']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_wap_regular'){
$temp='<span class="red">'.$val['wap_regular'].'</span> | <span class="my_gray">'.ceil($value);
$val['wap_regular_price']=ceil($value);
$value=ceil($value/$val['wap_regular']);
$val['combine_wap_regular']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_pc360'){
$temp='<span class="red">'.$val['pc360'].'</span> | <span class="my_gray">'.ceil($value);
$val['pc360_price']=ceil($value);
$value=ceil($value/$val['pc360']);
$val['combine_pc360']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_tel'){
$temp='<span class="red">'.$val['tel'].'</span> | <span class="my_gray">'.ceil($value);
$val['tel_price']=ceil($value);
$value=ceil($value/$val['tel']);
$val['combine_tel']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_qq'){
$temp='<span class="red">'.$val['qq'].'</span> | <span class="my_gray">'.ceil($value);
$val['qq_price']=ceil($value);
$value=ceil($value/$val['qq']);
$val['combine_qq']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_message'){
$temp='<span class="red">'.$val['message'].'</span> | <span class="my_gray">'.ceil($value);
$val['message_price']=ceil($value);
$value=ceil($value/$val['message']);
$val['combine_message']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_other'){
$temp='<span class="red">'.$val['other'].'</span> | <span class="my_gray">'.ceil($value);
$val['other_price']=ceil($value);
$value=ceil($value/$val['other']);
$val['combine_other']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_sougou'){
$temp='<span class="red">'.$val['sougou'].'</span> | <span class="my_gray">'.ceil($value);
$val['sougou_price']=ceil($value);
$value=ceil($value/$val['sougou']);
$val['combine_sougou']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}elseif($key=='avg_cps'){
$temp='<span class="red">'.$val['cps'].'</span> | <span class="my_gray">'.ceil($value);
$val['cps_price']=ceil($value);
$value=ceil($value/$val['cps']);
$val['combine_cps']=$temp.'</span> | <span class="my_green">'.$value.'</span>';
}
}
}
array_unshift($result,array_pop($result));
//处理所有天数的各项指数的综合平均值
if(!empty($temp)){
$n=count($result)-1;
foreach ($result[0] as $key=>$valu){
$str=substr($key,0, 3);
if($str!='avg'&&$str!='com'){
$temp_2[$key]=ceil($valu/$n);
}
if($str=='avg'){
$temp_2[$key]=$valu;
}
if($str=='com'){
$str_2=substr($key, 8);
$te_1=$str_2.'_price';
$te_2='avg_'.$str_2;
$temp_2[$key]='<span class="red">'.$temp_2[$str_2].'</span> | <span class="my_gray">'.$temp_2[$te_1].'</span> | <span class="my_green">'.$temp_2[$te_2].'</span>';
}
}
array_unshift($result, $temp_2);
}
return $result;
}
/*统计订单总额 */
function get_selling_static($time){
$sql="SELECT shipping_fee,sales,update_time from chun_selling_statistics
where update_time ='{$time}'
order by update_time asc";
return $GLOBALS['db']->getRow($sql);
}
/*订单导出 */
function export_order_from($result,$request_year){
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objActSheet = $objPHPExcel->getActiveSheet();
// 设置sheet名称
$objActSheet->setTitle($_REQUEST['s_d']);
$objActSheet->getColumnDimension('A')->setAutoSize(true);
$objActSheet->getStyle(1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//根据要求初始化所需要的数组
$temp_1=array('A'=>10,'B'=>10,'C'=>10,'D'=>12,'E'=>20,'F'=>19,'G'=>14,'H'=>22,'I'=>21,'J'=>16,'K'=>23,'L'=>22,'M'=>16,'N'=>24,'O'=>23,'P'=>11,'Q'=>20,'R'=>19,'S'=>11,'T'=>20,'U'=>19,'V'=>8,'W'=>17,'X'=>16,'Y'=>11,'Z'=>19,'AA'=>18,'AB'=>14,'AC'=>23,'AD'=>22,'AE'=>20,'AF'=>26,'AG'=>30);
$temp_2=array('A'=>'日期','B'=>'总售额','C'=>'总订单量','D'=>'pc自然流量','E'=>'pc自然流量总销售额','F'=>'pc自然流量/均单价','G'=>'wap自然流量','H'=>'wap自然流量总销售额','I'=>'wap自然流量/均单价','J'=>'pc百度推广量','K'=>'pc百度推广量总销售额','L'=>'pc百度推广量/均单价','M'=>'wap百度推广量','N'=>'wap百度推广量总销售额','O'=>'wap百度推广量/均单价','P'=>'360推广量','Q'=>'360推广量总销售额','R'=>'360推广量/均单价','S'=>'电话订单','T'=>'电话订单总销售额','U'=>'电话订单/均单价','V'=>'qq订单','W'=>'qq订单总销售额','X'=>'qq订单/均单价','Y'=>'短信订单','Z'=>'短信订单总销售额','AA'=>'短信订单/均单价','AB'=>'其它订单来源','AC'=>'其它订单来源总销售额','AD'=>'其它订单来源/均单价','AE'=>'搜狗订单来源','AF'=>'搜狗订单来源总销售额','AG'=>'搜狗订单来源总销售额/均单价');
$temp_3=array('B'=>'sum_price','C'=>'sum','D'=>'pc_regular','E'=>'pc_regular_price','F'=>'avg_pc_regular','G'=>'wap_regular','H'=>'wap_regular_price','I'=>'avg_wap_regular','J'=>'pc_baidu','K'=>'pc_baidu_price','L'=>'avg_pc_baidu','M'=>'wap_baidu','N'=>'wap_baidu_price','O'=>'avg_wap_baidu','P'=>'pc360','Q'=>'pc360_price','R'=>'avg_pc360','S'=>'tel','T'=>'tel_price','U'=>'avg_tel','V'=>'qq','W'=>'qq_price','X'=>'avg_qq','Y'=>'message','Z'=>'message_price','AA'=>'avg_message','AB'=>'other','AC'=>'other_price','AD'=>'avg_other','AE'=>'sougou','AF'=>'sougou_price','AG'=>'avg_sougou');
//根据初始化的数组设置每一列的名称及宽度
foreach ($temp_1 as $key=>$value){
$objActSheet->setCellValue($key.'1', iconv('gbk', 'utf-8', $temp_2["$key"]));
$objActSheet->getColumnDimension($key)->setWidth($value);
}
$i=2;
foreach ($result as $key=>$value){
if($key==0){
$objActSheet->setCellValue('A'.$i, iconv('gbk', 'utf-8', $request_year.'-'.$_REQUEST['s_d'].'(均)'));
}else{
$objActSheet->setCellValue('A'.$i, iconv('gbk', 'utf-8', $key));
}
$objFontstyle=$objActSheet->getStyle($i);
//设置表格中字体的对齐方式
$objFontstyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置表格背景色隔行变色
if($i%2==0){
$objFontstyle->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFontstyle->getFill()->getStartColor()->setRGB('DFDFDF');
}
//设置excel表格中的每一单元格的具体值
foreach ($temp_3 as $ke=>$val){
$objActSheet->setCellValue("$ke".$i, iconv('gbk', 'utf-8', $value["$val"]));
}
$i++;
}
header('Content-Type: application/vnd.ms-excel');
//设置导出的文件名称
header('Content-Disposition: p_w_upload;filename="' .$_REQUEST['s_d']. '月份订单来源统计信息.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objPHPExcel->getProperties()->setCreator($_SESSION['admin_name']);
$objWriter->save('php://output');
}代码分享
原创
©著作权归作者所有：来自51CTO博客作者staose_yunwei的原创作品，请联系作者获取转载授权，否则将追究法律责任
上一篇：PHP-猴子选大王
下一篇：php获取每天指定日期时间
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
unity代码加密分享untiy代码加密 资源加密 unity3d 3d 反编译
