代码分享

<?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>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['pc_baidu_price']=ceil($value);
				$value=ceil($value/$val['pc_baidu']);
				$val['combine_pc_baidu']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_wap_baidu'){
				$temp='<span class="red">'.$val['wap_baidu'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['wap_baidu_price']=ceil($value);
				$value=ceil($value/$val['wap_baidu']);
				$val['combine_wap_baidu']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_pc_regular'){
				$temp='<span class="red">'.$val['pc_regular'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['pc_regular_price']=ceil($value);
				$value=ceil($value/$val['pc_regular']);
				$val['combine_pc_regular']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_wap_regular'){
				$temp='<span class="red">'.$val['wap_regular'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['wap_regular_price']=ceil($value);
				$value=ceil($value/$val['wap_regular']);
				$val['combine_wap_regular']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_pc360'){
				$temp='<span class="red">'.$val['pc360'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['pc360_price']=ceil($value);
				$value=ceil($value/$val['pc360']);
				$val['combine_pc360']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_tel'){
				$temp='<span class="red">'.$val['tel'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['tel_price']=ceil($value);
				$value=ceil($value/$val['tel']);
				$val['combine_tel']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_qq'){
				$temp='<span class="red">'.$val['qq'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['qq_price']=ceil($value);
				$value=ceil($value/$val['qq']);
				$val['combine_qq']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_message'){
				$temp='<span class="red">'.$val['message'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['message_price']=ceil($value);
				$value=ceil($value/$val['message']);
				$val['combine_message']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_other'){
				$temp='<span class="red">'.$val['other'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['other_price']=ceil($value);
				$value=ceil($value/$val['other']);
				$val['combine_other']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_sougou'){
				$temp='<span class="red">'.$val['sougou'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['sougou_price']=ceil($value);
				$value=ceil($value/$val['sougou']);
				$val['combine_sougou']=$temp.'</span>&nbsp;|&nbsp;<span class="my_green">'.$value.'</span>';
			}elseif($key=='avg_cps'){
				$temp='<span class="red">'.$val['cps'].'</span>&nbsp;|&nbsp;<span class="my_gray">'.ceil($value);
				$val['cps_price']=ceil($value);
				$value=ceil($value/$val['cps']);
				$val['combine_cps']=$temp.'</span>&nbsp;|&nbsp;<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>&nbsp;|&nbsp;<span class="my_gray">'.$temp_2[$te_1].'</span>&nbsp;|&nbsp;<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');
}

转载于:https://blog.51cto.com/raoyonggang/1428485