497 lines
24 KiB
PHP
Executable File
497 lines
24 KiB
PHP
Executable File
<?php
|
|
namespace wstmart\admin\model;
|
|
use think\Db;
|
|
use think\Loader;
|
|
/**
|
|
* ============================================================================
|
|
* 报表业务处理
|
|
*/
|
|
class Reports extends Base{
|
|
/**
|
|
* 获取商品销售统计
|
|
*/
|
|
public function topSaleGoodsByPage(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$startTime = strtotime(input('startDate'));
|
|
$endTime =strtotime(input('endDate'));
|
|
$goodsCatIdPath = input('goodsCatIdPath');
|
|
$shopName = input('post.shopName');
|
|
$goodsName = input('post.goodsName');
|
|
$where='(payType=0 or (payType=1 and isPay=1)) and o.dataFlag=1';
|
|
if($shopName)$where.=' AND shopName like "%'.$shopName.'%"';
|
|
if($goodsName)$where.=' AND g.goodsName like "%'.$goodsName.'%"';
|
|
if($goodsCatIdPath !='')$where.=' AND g.goodsCatIdPath like "%'.$goodsCatIdPath.'%"';
|
|
$rs= Db::field('og.goodsId,g.goodsName,goodsSn,s.shopId,shopName,sum(og.goodsNum) goodsNum,og.goodsImg,count(og.goodsId)orderNum,pageNum,goodsCatIdPath')
|
|
->name('order_goods')->alias('og')
|
|
->join('(select p.*,count(p.goodsId) pageNum from __PAGE_VIEW__ p group by p.goodsId ) con','con.goodsId=og.goodsId','left')
|
|
->join('__ORDERS__ o','og.orderId=o.orderId','left')
|
|
->join('__GOODS__ g','og.goodsId=g.goodsId','left')
|
|
->join('__SHOPS__ s','g.shopId=s.shopId','left')
|
|
->order('goodsNum desc')
|
|
->whereTime('o.createTime','between',[$start,$end])
|
|
->where($where)
|
|
->group('og.goodsId,g.goodsName,goodsSn,s.shopId,shopName,og.goodsImg')
|
|
->paginate(input('limit/d'))->toArray();
|
|
return $rs;
|
|
}
|
|
/**
|
|
* 获取店铺销售统计
|
|
*/
|
|
public function topShopSalesByPage(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$shopName = input('shopName');
|
|
$where='(payType=0 or (payType=1 and isPay=1)) and o.dataFlag=1';
|
|
if($shopName)$where.=' AND shopName like "%'.$shopName.'%"';
|
|
$rs = Db::field('s.shopId,s.shopImg,s.shopName,sum(o.totalMoney) totalMoney,count(o.shopId) orderNum,s.userId')
|
|
->name('shops')->alias('s')
|
|
->join('__ORDERS__ o','s.shopId=o.shopId')
|
|
->order('totalMoney desc,orderNum desc')
|
|
->whereTime('o.createTime','between',[$start,$end])
|
|
->where($where)
|
|
->group('o.shopId')
|
|
->paginate(input('limit/d'))->toArray();
|
|
foreach($rs['Rows'] as $k=>$v){
|
|
$onLineArr = Db::name('orders')
|
|
->whereTime('createTime','between',[$start,$end])
|
|
->field('sum(totalMoney) totalMoney,sum(realTotalMoney) realTotalMoney')
|
|
->where('payType=1 and isPay=1 and dataFlag=1 and orderStatus=2')
|
|
->where(['shopId'=>$v['shopId']])
|
|
->find();
|
|
$rs['Rows'][$k]['onLinePayMoney'] = (float)$onLineArr['totalMoney'];// 在线支付总金额
|
|
$rs['Rows'][$k]['onLinePayTrueMoney'] = (float)$onLineArr['realTotalMoney'];// 在线支付实际金额
|
|
$rs['Rows'][$k]['offLinePayMoney'] = (float)Db::name('orders')
|
|
->whereTime('createTime','between',[$start,$end])
|
|
->where('payType=0 and dataFlag=1 and orderStatus=2')
|
|
->where(['shopId'=>$v['shopId']])
|
|
->value('sum(totalMoney)');;// 货到付款金额
|
|
$rs['Rows'][$k]['ectNum']=db('user_ect_log')->where(['userId'=>$v['userId'],'ectType'=>12])->value('sum(ectNum)ectNum');
|
|
}
|
|
return $rs;
|
|
|
|
}
|
|
|
|
/**
|
|
* 获取销售额
|
|
*/
|
|
public function statSales(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$payType = (int)input('payType',-1);
|
|
$rs = Db::field('left(createTime,10) createTime,orderSrc,sum(totalMoney) totalMoney')->name('orders')->whereTime('createTime','between',[$start,$end])
|
|
->where('((payType=0 or (payType=1 and isPay=1)) and dataFlag=1) '.(in_array($payType,[0,1])?" and payType=".$payType:''))
|
|
->order('createTime asc')
|
|
->group('left(createTime,10),orderSrc')->select();
|
|
$rdata = [];
|
|
if(count($rs)>0){
|
|
$days = [];
|
|
$payTypes = [0,1,2,3,4];
|
|
$tmp = [];
|
|
foreach($rs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp[$v['orderSrc']."_".$v['createTime']] = $v['totalMoney'];
|
|
}
|
|
$rdata['map'] = ['p0'=>0,'p1'=>0,'p2'=>0,'p3'=>0,'p4'=>0];
|
|
foreach($days as $v){
|
|
$total = 0;
|
|
foreach($payTypes as $p){
|
|
$pv = isset($tmp[$p."_".$v])?$tmp[$p."_".$v]:0;
|
|
$rdata['p'.$p][] = (float)$pv;
|
|
$total = $total + (float)$pv;
|
|
$rdata['map']['p'.$p] = $rdata['map']['p'.$p] + (float)$pv;
|
|
}
|
|
$rdata['total'][] = $total;
|
|
}
|
|
$rdata['days'] = $days;
|
|
}
|
|
return WSTReturn('',1,$rdata);
|
|
}
|
|
|
|
/**
|
|
* 获取订单统计
|
|
*/
|
|
public function statOrders(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$payType = (int)input('payType',-1);
|
|
$rs = Db::field('left(createTime,10) createTime,orderSrc,count(orderId) orderNum')->name('orders')->whereTime('createTime','between',[$start,$end])
|
|
->where('((payType=0 or (payType=1 and isPay=1)) and dataFlag=1) '.(in_array($payType,[0,1])?" and payType=".$payType:''))
|
|
->order('createTime asc')
|
|
->group('left(createTime,10),orderSrc')->select();
|
|
$rdata = [];
|
|
if(count($rs)>0){
|
|
$days = [];
|
|
$payTypes = [0,1,2,3,4];
|
|
$tmp = [];
|
|
foreach($rs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp[$v['orderSrc']."_".$v['createTime']] = $v['orderNum'];
|
|
}
|
|
$rdata['map'] = ['p0'=>0,'p1'=>0,'p2'=>0,'p3'=>0,'p4'=>0];
|
|
foreach($days as $v){
|
|
$total = 0;
|
|
foreach($payTypes as $p){
|
|
$pv = isset($tmp[$p."_".$v])?$tmp[$p."_".$v]:0;
|
|
$rdata['p'.$p][] = (float)$pv;
|
|
$total = $total + (float)$pv;
|
|
$rdata['map']['p'.$p] = $rdata['map']['p'.$p] + (float)$pv;
|
|
}
|
|
$rdata['total'][] = $total;
|
|
}
|
|
$rdata['days'] = $days;
|
|
}
|
|
return WSTReturn('',1,$rdata);
|
|
}
|
|
/*首页获取订单数量*/
|
|
public function getOrders(){
|
|
$data = cache('orderData');
|
|
if(empty($data)){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$payType = -1;
|
|
$rs = Db::field('left(createTime,10) createTime,orderSrc,count(orderId) orderNum')->name('orders')->whereTime('createTime','between',[$start,$end])
|
|
->where('((payType=0 or (payType=1 and isPay=1)) and dataFlag=1) '.(in_array($payType,[0,1])?" and payType=".$payType:''))
|
|
->order('createTime asc')
|
|
->group('left(createTime,10),orderSrc')->select();
|
|
$rdata = [];
|
|
if(count($rs)>0){
|
|
$days = [];
|
|
$tmp = [];
|
|
$payTypes = [0,1,2,3,4];
|
|
foreach($rs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp[$v['orderSrc']."_".$v['createTime']] = $v['orderNum'];
|
|
}
|
|
foreach($days as $v){
|
|
$total = 0;
|
|
foreach($payTypes as $p){
|
|
$pv = isset($tmp[$p."_".$v])?$tmp[$p."_".$v]:0;
|
|
$total = $total + (float)$pv;
|
|
}
|
|
$rdata['total'][] = $total;
|
|
}
|
|
$rdata['days'] = $days;
|
|
cache('orderData',$rdata,7200);
|
|
}
|
|
}else{
|
|
$rdata = cache('orderData');
|
|
}
|
|
return WSTReturn('',1,$rdata);
|
|
}
|
|
/**
|
|
* 获取新增用户
|
|
*/
|
|
public function statNewUser(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$urs = Db::field('left(createTime,10) createTime,count(userId) userNum')
|
|
->name('users')
|
|
->whereTime('createTime','between',[$start,$end])
|
|
->where(['dataFlag'=>1,'userType'=>0])
|
|
->order('createTime asc')
|
|
->group('left(createTime,10)')
|
|
->select();
|
|
$srs = Db::field('left(createTime,10) createTime,count(shopId) userNum')
|
|
->name('shops')
|
|
->whereTime('createTime','between',[$start,$end])
|
|
->where(['dataFlag'=>1])
|
|
->order('createTime asc')
|
|
->group('left(createTime,10)')
|
|
->select();
|
|
$rdata = [];
|
|
$days = [];
|
|
$tmp = [];
|
|
if(count($urs)>0){
|
|
foreach($urs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp["0_".$v['createTime']] = $v['userNum'];
|
|
}
|
|
}
|
|
if(count($srs)>0){
|
|
foreach($srs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp["1_".$v['createTime']] = $v['userNum'];
|
|
}
|
|
}
|
|
sort($days);
|
|
foreach($days as $v){
|
|
$rdata['u0'][] = isset($tmp['0_'.$v])?$tmp['0_'.$v]:0;
|
|
$rdata['u1'][] = isset($tmp['1_'.$v])?$tmp['1_'.$v]:0;
|
|
}
|
|
$rdata['days'] = $days;
|
|
return WSTReturn('',1,$rdata);
|
|
}
|
|
/**
|
|
* 会员登录统计
|
|
*/
|
|
public function statUserLogin(){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$prefix = config('database.prefix');
|
|
$sql ='select createTime,userType,count(userId) userNum from (
|
|
SELECT left(loginTime,10) createTime,`userType`,u.userId
|
|
FROM `'.$prefix.'users` `u` INNER JOIN `'.$prefix.'log_user_logins` `lg` ON `u`.`userId`=`lg`.`userId`
|
|
WHERE `loginTime` BETWEEN "'.$start.'" AND "'.$end.'" AND ( dataFlag=1 )
|
|
GROUP BY left(loginTime,10),userType,lg.userId
|
|
) a GROUP BY createTime, userType ORDER BY createTime asc ';
|
|
$rs = Db::query($sql);
|
|
$rdata = [];
|
|
if(count($rs)>0){
|
|
$days = [];
|
|
$tmp = [];
|
|
foreach($rs as $key => $v){
|
|
if(!in_array($v['createTime'],$days))$days[] = $v['createTime'];
|
|
$tmp[$v['userType']."_".$v['createTime']] = $v['userNum'];
|
|
}
|
|
foreach($days as $v){
|
|
$rdata['u0'][] = isset($tmp['0_'.$v])?$tmp['0_'.$v]:0;
|
|
$rdata['u1'][] = isset($tmp['1_'.$v])?$tmp['1_'.$v]:0;
|
|
}
|
|
$rdata['days'] = $days;
|
|
}
|
|
return WSTReturn('',1,$rdata);
|
|
}
|
|
/**
|
|
* 导出商品销售订单
|
|
*/
|
|
public function toExportGoods(){
|
|
$name='商品销售统计表';
|
|
$start = input('startDate');
|
|
$end = input('endDate');
|
|
$where=[];
|
|
$shopName = input('shopName');
|
|
$goodsName = input('goodsName');
|
|
$goodsCatIdPath = input('goodsCatIdPath');
|
|
if($goodsCatIdPath !='')$where['g.goodsCatIdPath'] = ['like','%'.$goodsCatIdPath.'%'];
|
|
if($shopName!='')$where['shopName'] = ['like','%'.$shopName.'%'];
|
|
if($goodsName!='')$where['g.goodsName'] = ['like','%'.$goodsName.'%'];
|
|
if($start!='' && $end!=''){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$where['o.createTime'] = ['between',[$start.' 00:00:00',$end.' 23:59:59']];
|
|
}else if($start!=''){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$where['o.createTime'] = ['>=',$start.' 00:00:00'];
|
|
}else if($end!=''){
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$where['o.createTime'] = ['<=',$end.' 23:59:59'];
|
|
}
|
|
$page=Db::field('og.goodsId,g.goodsName,goodsSn,s.shopId,shopName,sum(og.goodsNum) goodsNum,og.goodsImg,count(og.goodsId)orderNum,pageNum')
|
|
->name('order_goods')->alias('og')
|
|
->join('(select p.*,count(p.goodsId) pageNum from __PAGE_VIEW__ p group by p.goodsId ) con','con.goodsId=og.goodsId','left')
|
|
->join('__ORDERS__ o','og.orderId=o.orderId','left')
|
|
->join('__GOODS__ g','og.goodsId=g.goodsId','left')
|
|
->join('__SHOPS__ s','g.shopId=s.shopId','left')
|
|
->order('goodsNum desc')
|
|
->where($where)
|
|
->where('(payType=0 or (payType=1 and isPay=1)) and o.dataFlag=1')
|
|
->group('og.goodsId,g.goodsName,goodsSn,s.shopId,shopName,og.goodsImg')
|
|
->select();
|
|
Loader::import('phpexcel.PHPExcel.IOFactory');
|
|
$objPHPExcel = new \PHPExcel();
|
|
// 设置excel文档的属性
|
|
$objPHPExcel->getProperties()->setCreator("heyuanhui")//创建人
|
|
->setLastModifiedBy("heyuanhui")//最后修改人
|
|
->setTitle($name)//标题
|
|
->setSubject($name)//题目
|
|
->setDescription($name)//描述
|
|
->setKeywords("订单")//关键字
|
|
->setCategory("Test result file");//种类
|
|
|
|
// 开始操作excel表
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
|
// 设置工作薄名称
|
|
$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'Sheet'));
|
|
// 设置默认字体和大小
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', ''));
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);
|
|
$styleArray = array(
|
|
'font' => array(
|
|
'bold' => true,
|
|
'color'=>array(
|
|
'argb' => 'ffffffff',
|
|
)
|
|
),
|
|
'borders' => array (
|
|
'outline' => array (
|
|
'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式
|
|
'color' => array ('argb' => 'FF000000'), //设置border颜色
|
|
)
|
|
)
|
|
);
|
|
//设置宽
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->getStartColor()->setARGB('333399');
|
|
|
|
$objPHPExcel->getActiveSheet()
|
|
->setCellValue('A1', '商品ID')
|
|
->setCellValue('B1', '商品编号')
|
|
->setCellValue('C1', '商品名称')
|
|
->setCellValue('D1', '商品销量')
|
|
->setCellValue('E1', '所属店铺')
|
|
->setCellValue('F1', '订单总数')
|
|
->setCellValue('G1', '浏览总数');
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->applyFromArray($styleArray);
|
|
|
|
for ($row = 0; $row < count($page); $row++){
|
|
$i = $row+2;
|
|
$objPHPExcel->getActiveSheet()
|
|
->setCellValue('A'.$i, $page[$row]['goodsId'])
|
|
->setCellValue('B'.$i, chunk_split($page[$row]['goodsSn']))
|
|
->setCellValue('C'.$i, $page[$row]['goodsName'])
|
|
->setCellValue('D'.$i, $page[$row]['goodsNum'])
|
|
->setCellValue('E'.$i, $page[$row]['shopName'])
|
|
->setCellValue('F'.$i, $page[$row]['orderNum'])
|
|
->setCellValue('G'.$i, $page[$row]['pageNum']);
|
|
|
|
}
|
|
//输出EXCEL格式
|
|
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
|
|
// 从浏览器直接输出$filename
|
|
header('Content-Type:application/csv;charset=UTF-8');
|
|
header("Pragma: public");
|
|
header("Expires: 0");
|
|
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
|
|
header("Content-Type:application/force-download");
|
|
header("Content-Type:application/vnd.ms-excel;");
|
|
header("Content-Type:application/octet-stream");
|
|
header("Content-Type:application/download");
|
|
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
|
|
header("Content-Transfer-Encoding:binary");
|
|
$objWriter->save('php://output');
|
|
}
|
|
/**
|
|
* 导出商品销售订单
|
|
*/
|
|
public function toExportShop(){
|
|
$name='店铺销售统计表';
|
|
$start = input('startDate');
|
|
$end = input('endDate');
|
|
$shopName = input('shopName');
|
|
$wh='(payType=0 or (payType=1 and isPay=1)) and o.dataFlag=1';
|
|
if($shopName)$wh.=' AND shopName like "%'.$shopName.'%"';
|
|
$where=[];
|
|
if($start!='' && $end!=''){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$where['o.createTime'] = ['between',[$start.' 00:00:00',$end.' 23:59:59']];
|
|
}else if($start!=''){
|
|
$start = date('Y-m-d 00:00:00',strtotime(input('startDate')));
|
|
$where['o.createTime'] = ['>=',$start.' 00:00:00'];
|
|
}else if($end!=''){
|
|
$end = date('Y-m-d 23:59:59',strtotime(input('endDate')));
|
|
$where['o.createTime'] = ['<=',$end.' 23:59:59'];
|
|
}
|
|
$page=Db::field('s.shopId,s.shopImg,s.shopName,sum(o.totalMoney) totalMoney,count(o.orderId) orderNum')
|
|
->name('shops')->alias('s')
|
|
->join('__ORDERS__ o','s.shopId=o.shopId')
|
|
->order('totalMoney desc,orderNum desc')
|
|
->where($where)
|
|
->where($wh)
|
|
->group('o.shopId')
|
|
->select();
|
|
|
|
foreach($page as $k=>$v){
|
|
$onLineArr = Db::name('orders')->alias('o')
|
|
->where($where)
|
|
->field('sum(totalMoney) totalMoney,sum(realTotalMoney) realTotalMoney')
|
|
->where('payType=1 and isPay=1 and dataFlag=1 and orderStatus=2')
|
|
->where(['shopId'=>$v['shopId']])
|
|
->find();
|
|
$page[$k]['onLinePayMoney'] = (float)$onLineArr['totalMoney'];// 在线支付总金额
|
|
$page[$k]['onLinePayTrueMoney'] = (float)$onLineArr['realTotalMoney'];// 在线支付实际金额
|
|
$page[$k]['offLinePayMoney'] = (float)Db::name('orders')->alias('o')
|
|
->where($where)
|
|
->where('payType=0 and dataFlag=1 and orderStatus=2')
|
|
->where(['shopId'=>$v['shopId']])
|
|
->value('sum(totalMoney)');;// 货到付款金额
|
|
}
|
|
Loader::import('phpexcel.PHPExcel.IOFactory');
|
|
$objPHPExcel = new \PHPExcel();
|
|
// 设置excel文档的属性
|
|
$objPHPExcel->getProperties()->setCreator("heyuanhui")//创建人
|
|
->setLastModifiedBy("heyuanhui")//最后修改人
|
|
->setTitle($name)//标题
|
|
->setSubject($name)//题目
|
|
->setDescription($name)//描述
|
|
->setKeywords("订单")//关键字
|
|
->setCategory("Test result file");//种类
|
|
|
|
// 开始操作excel表
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
|
// 设置工作薄名称
|
|
$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'Sheet'));
|
|
// 设置默认字体和大小
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', ''));
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);
|
|
$styleArray = array(
|
|
'font' => array(
|
|
'bold' => true,
|
|
'color'=>array(
|
|
'argb' => 'ffffffff',
|
|
)
|
|
),
|
|
'borders' => array (
|
|
'outline' => array (
|
|
'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式
|
|
'color' => array ('argb' => 'FF000000'), //设置border颜色
|
|
)
|
|
)
|
|
);
|
|
//设置宽
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->getStartColor()->setARGB('333399');
|
|
|
|
$objPHPExcel->getActiveSheet()
|
|
->setCellValue('A1', '店铺名称')
|
|
->setCellValue('B1', '订单总金额')
|
|
->setCellValue('C1', '订单总数')
|
|
->setCellValue('D1', '在线支付总金额')
|
|
->setCellValue('E1', '在线支付实际金额')
|
|
->setCellValue('F1', '货到付款金额');
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->applyFromArray($styleArray);
|
|
|
|
for ($row = 0; $row < count($page); $row++){
|
|
$i = $row+2;
|
|
$objPHPExcel->getActiveSheet()
|
|
->setCellValue('A'.$i, $page[$row]['shopName'])
|
|
->setCellValue('B'.$i, $page[$row]['totalMoney'])
|
|
->setCellValue('C'.$i, $page[$row]['orderNum'])
|
|
->setCellValue('D'.$i, $page[$row]['onLinePayMoney'])
|
|
->setCellValue('E'.$i, $page[$row]['onLinePayTrueMoney'])
|
|
->setCellValue('F'.$i, $page[$row]['offLinePayMoney']);
|
|
|
|
}
|
|
//输出EXCEL格式
|
|
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
|
|
// 从浏览器直接输出$filename
|
|
header('Content-Type:application/csv;charset=UTF-8');
|
|
header("Pragma: public");
|
|
header("Expires: 0");
|
|
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
|
|
header("Content-Type:application/force-download");
|
|
header("Content-Type:application/vnd.ms-excel;");
|
|
header("Content-Type:application/octet-stream");
|
|
header("Content-Type:application/download");
|
|
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
|
|
header("Content-Transfer-Encoding:binary");
|
|
$objWriter->save('php://output');
|
|
}
|
|
} |