<?php

namespace Retail\Controller;

use Think\Controller;

//[1.2.91]
class ExporttableController extends BaseController
{
    public function goods()
    {
        $storeid = session('storeid');
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        //按照商品类别搜索
        //按照商品类别搜索
        $person1 = I('get.person1');
        $person2 = I('get.person2');
        $person3 = I('get.person3');
        $flag = 0;
        if (!empty($person3)) {
            $where['g.goods_class_id'] = $person3;
        } else if (!empty($person2)) {
            $class = M('retail_goods_class as c2')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid')
                ->where(array('c2.id' => $person2, 'c3.status' => 0))
                ->field('c3.id')->select();
            $flag = 1;
        } else if (!empty($person1)) {
            $class = M('retail_goods_class as c1')
                ->join('ls_retail_goods_class as c2 on c1.id=c2.pid')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid AND c3.status=0', 'LEFT')
                ->where(array('c1.id' => $person1, 'c2.status' => 0))
                ->field('c2.id,c3.id as c3_id')->select();
            $flag = 1;
        }
        if (!empty($class)) {
            $arr = array();
            foreach ($class as &$val) {
                if (!in_array($val['id'], $arr)) {
                    $arr[] = $val['id'];
                }
                if (!empty($val['c3_id'])) {
                    if (!in_array($val['c3_id'], $arr)) {
                        $arr[] = $val['c3_id'];
                    }
                }
            }
            $str = implode(',', $arr);
            $where['g.goods_class_id'] = array('in', $str . ',' . ($person2 ? $person2 : $person1));
        } else if ($flag == 1) {
            $where['g.goods_class_id'] = $person2 ? $person2 : $person1;
        }
        $where['g.storeid'] = $storeid;
        $where['g.status'] = array('in', array(0, 1));
        //商品列表
        $goods = M('retail_goods as g')
            ->join('ls_retail_goods_class as c on c.id=g.goods_class_id')
            ->where($where)
            ->order('g.goods_class_id desc,g.status asc')
            ->field('g.*,c.class_name,c.type as c_type,c.pid as c_pid')
            ->select();

        foreach ($goods as &$value) {
            //查询出分类名称
            if ($value['c_type'] == 2) {
                $value['class_name'] = M('retail_goods_class')->where(array('id' => $value['c_pid']))->getField('class_name');
            } else if ($value['c_type'] == 3) {
                $_class = M('retail_goods_class as c2')
                    ->join('ls_retail_goods_class as c1 on c2.pid=c1.id')
                    ->where(array('c2.id' => $value['c_pid']))
                    ->field('c1.class_name,c2.class_name as c2_class_name')
                    ->find();
                $value['class_name'] = $_class['class_name'] . '/' . $_class['c2_class_name'] . '/' . $value['class_name'];
            }
            //判断出商品的状态以及是否允许修改折扣价
            if ($value['status'] == 0) {
                $value['status_name'] = '正常';
            } elseif ($value['status'] == 1) {
                $value['status_name'] = '已下架';
            } elseif ($value['status'] == 2) {
                $value['status_name'] = '已删除';
            }
            if ($value['issellonline'] == 0) {
                $value['issellonline_name'] = '否';
            } else {
                $value['issellonline_name'] = '是';
            }
            $value['goods_code'] = $value['goods_code'] ? $value['goods_code'] : '';
            $value['good_number'] = $value['good_number'] ? $value['good_number'] : '';
            if ($value['vip_online_state'] == 0) {
                $value['vip_online_state'] = '关闭';
            } else {
                $value['vip_online_state'] = '开启';
            }
            if ($value['vip_retail_state'] == 0) {
                $value['vip_retail_state'] = '关闭';
            } else {
                $value['vip_retail_state'] = '开启';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            array('purch_price', '进货价'),
            array('retail_price', '零售价'),
            array('online_price', '线上价'),
            array('issellonline_name', '是否在商城出售'),
            array('vip_online_state', '线上折扣'),
            array('vip_retail_state', '线下折扣'),
            array('goods_num', '商品系统库存'),
            array('more_num', '库存上限(超量值)'),
            array('less_num', '库存下限(预警值)'),
            array('sell_num', '已售数量'),
            array('company', '单位'),
            array('specifications', '规格'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $goods, $str);
    }

    public function goodstwo()
    {
        $storeid = session('storeid');
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        //按照商品类别搜索
        $person1 = I('get.person1');
        $person2 = I('get.person2');
        $person3 = I('get.person3');
        $flag = 0;
        if (!empty($person3)) {
            $where['g.goods_class_id'] = $person3;
        } else if (!empty($person2)) {
            $class = M('retail_goods_class as c2')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid')
                ->where(array('c2.id' => $person2, 'c3.status' => 0))
                ->field('c3.id')->select();
            $flag = 1;
        } else if (!empty($person1)) {
            $class = M('retail_goods_class as c1')
                ->join('ls_retail_goods_class as c2 on c1.id=c2.pid')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid AND c3.status=0', 'LEFT')
                ->where(array('c1.id' => $person1, 'c2.status' => 0))
                ->field('c2.id,c3.id as c3_id')->select();
            $flag = 1;
        }
        if (!empty($class)) {
            $arr = array();
            foreach ($class as &$val) {
                if (!in_array($val['id'], $arr)) {
                    $arr[] = $val['id'];
                }
                if (!empty($val['c3_id'])) {
                    if (!in_array($val['c3_id'], $arr)) {
                        $arr[] = $val['c3_id'];
                    }
                }
            }
            $str = implode(',', $arr);
            $where['g.goods_class_id'] = array('in', $str . ',' . ($person2 ? $person2 : $person1));
        } else if ($flag == 1) {
            $where['g.goods_class_id'] = $person2 ? $person2 : $person1;
        }
        $where['g.storeid'] = $storeid;
        $where['g.issellonline'] = 1;
        $where['g.status'] = array('in', array(0, 1));
        //商品列表
        $goods = M('retail_goods as g')
            ->join('ls_retail_goods_class as c on c.id=g.goods_class_id')
            ->where($where)
            ->order('g.status asc,g.good_number desc')
            ->field('g.*,c.class_name,c.type as c_type,c.pid as c_pid')
            ->select();
        foreach ($goods as &$value) {
            //查询出分类名称
            if ($value['c_type'] == 2) {
                $value['class_name'] = M('retail_goods_class')->where(array('id' => $value['c_pid']))->getField('class_name');
            } else if ($value['c_type'] == 3) {
                $_class = M('retail_goods_class as c2')
                    ->join('ls_retail_goods_class as c1 on c2.pid=c1.id')
                    ->where(array('c2.id' => $value['c_pid']))
                    ->field('c1.class_name,c2.class_name as c2_class_name')
                    ->find();
                $value['class_name'] = $_class['class_name'] . '/' . $_class['c2_class_name'] . '/' . $value['class_name'];
            }
            //判断出商品的状态以及是否允许修改折扣价
            if ($value['status'] == 0) {
                $value['status_name'] = '正常';
            } elseif ($value['status'] == 1) {
                $value['status_name'] = '已下架';
            } elseif ($value['status'] == 2) {
                $value['status_name'] = '已删除';
            }
            if ($value['issellonline'] == 0) {
                $value['issellonline_name'] = '否';
            } else {
                $value['issellonline_name'] = '是';
            }
            $value['goods_code'] = $value['goods_code'] ? $value['goods_code'] : '';
            $value['good_number'] = $value['good_number'] ? $value['good_number'] : '';
            if ($value['vip_online_state'] == 0) {
                $value['vip_online_state'] = '关闭';
            } else {
                $value['vip_online_state'] = '开启';
            }
            if ($value['vip_retail_state'] == 0) {
                $value['vip_retail_state'] = '关闭';
            } else {
                $value['vip_retail_state'] = '开启';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            array('purch_price', '进货价'),
            array('retail_price', '零售价'),
            array('online_price', '线上价'),
            array('issellonline_name', '是否在商城出售'),
            array('vip_online_state', '线上折扣'),
            array('vip_retail_state', '线下折扣'),
            array('goods_num', '商品系统库存'),
            array('more_num', '库存上限(超量值)'),
            array('less_num', '库存下限(预警值)'),
            array('sell_num', '已售数量'),
            array('company', '单位'),
            array('specifications', '规格'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $goods, $str);
    }

    // 商品分类导出
    public function goodsclass()
    {
        $storeid = session('storeid');
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        $merchantid = M('retail_store')->where(array('id' => $storeid))->getField('merchantid');
        $where['merchantid'] = $merchantid;
        $where['status'] = 0;
        $where['type'] = 1;
        $class1 = M('retail_goods_class')->where($where)->order('addtime desc')->select();
        $classAll = array();
        if (!empty($class1)) {
            foreach ($class1 as &$value) {
                $value['p_name'] = '一级分类';
                $value['p_name_path'] = $value['class_name'];
                $classAll[] = $value;
                $where['type'] = 2;
                $where['pid'] = $value['id'];
                $class2 = M('retail_goods_class')->where($where)->order('addtime desc')->select();
                if (!empty($class2)) {
                    foreach ($class2 as &$val) {
                        $val['p_name'] = '二级分类';
                        $val['p_name_path'] = $value['class_name'] . "/" . $val['class_name'];
                        $classAll[] = $val;
                        $where['type'] = 3;
                        $where['pid'] = $val['id'];
                        $class3 = M('retail_goods_class')->where($where)->order('addtime desc')->select();
                        if (!empty($class3)) {
                            foreach ($class3 as &$v) {
                                $v['p_name'] = '三级分类';
                                $v['p_name_path'] = $value['class_name'] . "/" . $val['class_name'] . "/" . $v['class_name'];
                                $classAll[] = $v;
                            }
                        }
                    }
                }
            }
        } else {
            $classAll = array();
        }
        $filename = "商品分类明细";
        $arr = array(
            array('class_name', '分类名称'),
            array('p_name', '分类级别'),
            array('p_name_path', '分类详情'),
            array('code', '编码')
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $arr, $classAll, $str);
    }

    private function getExcel($expTitle, $expCellName, $expTableData, $str = "")
    {

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle . date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $common = $str;
        vendor("PHPExcel.PHPExcel");
        ob_clean();
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $common);
//        $objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', $str);
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }

        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), ' ' . $expTableData[$i][$expCellName[$j][0]]);

            }
        }
        foreach ($cellName as $value) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);

        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit();
    }

    // 商品导出页面
    public function goodsquantity()
    {
        $this->display();
    }

    public function quantityList()
    {
        $storeid = session('storeid');
        $where['l.storeid'] = $storeid;
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $name['goods_name|goods_code|good_number'] = $keyword;
            $name['storeid'] = $storeid;
            $ids = M('retail_goods')->where($name)->field('id')->select();
            $new_id = '';
            foreach ($ids as $key => &$value) {
                $new_id .= $value['id'] . ',';
            }
            $arr_ids = explode(',', trim($new_id, ','));
            $where['l.goods_id'] = array('in', $arr_ids);
        }
        $where['l.type'] = I('get.type');
        $start = I('get.start');
        $end = I('get.end');
        $s = strtotime($start . ' 00:00:00');
        $e = strtotime($end . ' 23:59:59');
        $where['l.addtime'] = array('between', array($s, $e));
        $logs = M('retail_quantity_logs')->alias('l')
            ->join('ls_retail_goods as g on g.id=l.goods_id', 'LEFT')
            ->where($where)
            ->field('l.*,g.goods_name,g.goods_code,g.good_number')
            ->order('l.addtime desc, l.id desc')
            ->select();
        foreach ($logs as $key => &$value) {
            $logs[$key]['time'] = date('Y-m-d H:i:s', $value['addtime']);
            //商品来源
            if ($value['supplier_id'] == 0) {
                if ($value['type'] == 1) {
                    $logs[$key]['supplier_name'] = '自采';
                    if ($logs[$key]['remark'] == '盘点增加') {
                        $logs[$key]['supplier_name'] = '盘点';
                    }
                } else {
                    $logs[$key]['supplier_name'] = '出售';
                    if ($logs[$key]['remark'] == '盘点减少') {
                        $logs[$key]['supplier_name'] = '盘点';
                    }
                }
            } else if ($value['supplier_id'] == -1) {
                $logs[$key]['supplier_name'] = '报损';
            } else if ($value['supplier_id'] == -2) {
                $logs[$key]['supplier_name'] = '调出';
            } else if ($value['supplier_id'] == -3) {
                $logs[$key]['supplier_name'] = '退货出库';
            } else {
                if ($value['type'] == 1) {
                    $logs[$key]['supplier_name'] = M("retail_supplier")->where(array('id' => $value['supplier_id']))->getField("supplier_name");
                    $logs[$key]['supplier_name'] = $logs[$key]['supplier_name'] . "(供货商)";
                } else {
                    $logs[$key]['supplier_name'] = '出售';
                }
            }

            if ($value['type'] == 1) {
                $logs[$key]['type'] = '增加';
            } else {
                $logs[$key]['type'] = '减少';
            }
            $value['key'] = $key + 1;
            $logs[$key]['goods_code'] = $logs[$key]['goods_code'] ? $logs[$key]['goods_code'] : '';
            $logs[$key]['good_number'] = $logs[$key]['good_number'] ? $logs[$key]['good_number'] : '';

        }
        $rt['code'] = 0;
        $rt['msg'] = '';
        $rt['count'] = count($logs);
        $rt['data'] = $logs;
        $this->ajaxReturn($rt);
    }

    // 导出单品出入库数据
    public function exportquantityList()
    {
        $storeid = session('storeid');
        $where['l.storeid'] = $storeid;
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $name['goods_name|goods_code|good_number'] = $keyword;
            $name['storeid'] = $storeid;
            $ids = M('retail_goods')->where($name)->field('id')->select();
            $new_id = '';
            foreach ($ids as $key => &$value) {
                $new_id .= $value['id'] . ',';
            }
            $arr_ids = explode(',', trim($new_id, ','));
            $where['l.goods_id'] = array('in', $arr_ids);
        }
        $where['l.type'] = I('get.type');
        $start = I('get.start');
        $end = I('get.end');
        $s = strtotime($start . ' 00:00:00');
        $e = strtotime($end . ' 23:59:59');
        $where['l.addtime'] = array('between', array($s, $e));
        $logs = M('retail_quantity_logs')->alias('l')
            ->join('ls_retail_goods as g on g.id=l.goods_id', 'LEFT')
            ->where($where)
            ->field('l.*,g.goods_name,g.goods_code,g.good_number')
            ->order('l.addtime desc')
            ->select();
        foreach ($logs as $key => &$value) {
            $logs[$key]['time'] = date('Y-m-d H:i:s', $value['addtime']);
            //商品来源
            if ($value['supplier_id'] == 0) {
                if ($value['type'] == 1) {
                    $logs[$key]['supplier_name'] = '自采';
                    if ($logs[$key]['remark'] == '盘点增加') {
                        $logs[$key]['supplier_name'] = '盘点';
                    }
                } else {
                    $logs[$key]['supplier_name'] = '出售';
                    if ($logs[$key]['remark'] == '盘点减少') {
                        $logs[$key]['supplier_name'] = '盘点';
                    }
                }
            } else if ($value['supplier_id'] == -1) {
                $logs[$key]['supplier_name'] = '报损';
            } else if ($value['supplier_id'] == -2) {
                $logs[$key]['supplier_name'] = '调出';
            } else if ($value['supplier_id'] == -3) {
                $logs[$key]['supplier_name'] = '退货出库';
            } else {
                if ($value['type'] == 1) {
                    $logs[$key]['supplier_name'] = M("retail_supplier")->where(array('id' => $value['supplier_id']))->getField("supplier_name");
                    $logs[$key]['supplier_name'] = $logs[$key]['supplier_name'] . "(供货商)";
                } else {
                    $logs[$key]['supplier_name'] = '出售';
                }
            }

            if ($value['type'] == 1) {
                $logs[$key]['type'] = '增加';
            } else {
                $logs[$key]['type'] = '减少';
            }
            $value['key'] = $key + 1;
            $logs[$key]['goods_code'] = $logs[$key]['goods_code'] ? $logs[$key]['goods_code'] : '';
            $logs[$key]['good_number'] = $logs[$key]['good_number'] ? $logs[$key]['good_number'] : '';
        }
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        $str2 = I('get.type') == 1 ? '入库' : '出库';
        $filename = '商品(' . $logs[0]['goods_name'] . ")" . $str2 . "明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('type', '操作类型'),
            array('time', '操作时间'),
            array('supplier_name', '供货商/来源'),
            array('original_stock', '变动前库存'), array('present_stock', '变动后库存'),
            array('do_num', '操作数量'),
            array('remark', '备注'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $logs, $str);
    }

    // 导出盘点单商品
    public function exporttablecheck()
    {
        $global_store_jurisdiction = I('session.global_store_jurisdiction');        // 权限
        $showpurch_price = $global_store_jurisdiction['showpurch_price'] ? $global_store_jurisdiction['showpurch_price'] : 2;   // 查看进价权限
        $id = I('get.id');
        $storeid = session('storeid');
        $merchantid = M('retail_store')->where(array('id'=>$storeid))->getField("merchantid");
        $where['merchantid'] = $merchantid;
        $where['storeid'] = $storeid;
        $where['pd_order_id'] = $id;
        $orderlist = M('retail_goods_pd')->where($where)->select();
        foreach ($orderlist as $k => &$v) {
            $v['index'] = $k + 1;
            if($v['attribute'] == 2){
                $v['goods_code'] = $v['sku_code'] ? $v['sku_code'] : '--';
                $sku_list = M('retail_sku_goods')->where(array('id'=>$v['sku_id']))->getField("sku_id_list");
                $sku_list = explode(',',$sku_list);
                $sku_name = '';
                foreach ($sku_list as $skuk=>&$skuv){
                    $sku_name .= ','.M('retail_sku_list')->where(array('id'=>$skuv))->getField("name");
                }
                $v['goods_name'] = $v['goods_name'].$sku_name;
            }else if($v['attribute'] == 1){
                $v['goods_code'] = $v['goods_code'] ? $v['goods_code'] : '--';
                $v['sku_id'] = '';
            }
            $v['good_number'] = $v['good_number'] ? $v['good_number'] : '--';
            $v['change_num'] = round($v['pd_num'] - $v['goods_num'], 2);
            if($showpurch_price == 2){
                $v['all_money'] = '*';
                $v['purch_price'] = '*';
            }else{
                $v['all_money'] = round(($v['pd_num'] - $v['goods_num']) * $v['purch_price'], 2);
            }
        }
      /*  import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");*/
        $str2 = "日期:" . date('Y-m-d H:i:s', $order['addtime']);
        $filename = "商品盘点(" . $str2 . ")明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('purch_price', '成本价'),
            array('retail_price', '零售价'),
            array('goods_num', '商品系统库存'),
            array('pd_num', '盘点(实际)库存'),
            array('change_num', '盈亏数量'),
            array('all_money', '盈亏金额'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $orderlist, $str);
    }

    // 导出超量商品数据
    public function exporttablegoodsmore()
    {
        $storeid = session('storeid');
        //接收查询条件
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $where['g.good_number|g.goods_name'] = $keyword;
        }
        //按照商品类别搜索
        $person1 = I('get.person1');
        $person2 = I('get.person2');
        $person3 = I('get.person3');
        $flag = 0;
        if (!empty($person3)) {
            $where['g.goods_class_id'] = $person3;
        } else if (!empty($person2)) {
            $class = M('retail_goods_class as c2')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid')
                ->where(array('c2.id' => $person2, 'c3.status' => 0))
                ->field('c3.id')->select();
            $flag = 1;
        } else if (!empty($person1)) {
            $class = M('retail_goods_class as c1')
                ->join('ls_retail_goods_class as c2 on c1.id=c2.pid')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid AND c3.status=0', 'LEFT')
                ->where(array('c1.id' => $person1, 'c2.status' => 0))
                ->field('c2.id,c3.id as c3_id')->select();
            $flag = 1;
        }
        if (!empty($class)) {
            $arr = array();
            foreach ($class as &$val) {
                if (!in_array($val['id'], $arr)) {
                    $arr[] = $val['id'];
                }
                if (!empty($val['c3_id'])) {
                    if (!in_array($val['c3_id'], $arr)) {
                        $arr[] = $val['c3_id'];
                    }
                }
            }
            $str = implode(',', $arr);
            $where['g.goods_class_id'] = array('in', $str . ',' . ($person2 ? $person2 : $person1));
        } else if ($flag == 1) {
            $where['g.goods_class_id'] = $person2 ? $person2 : $person1;
        }
        $where['g.storeid'] = $storeid;
        $where['g.status'] = array('in', array(0, 1));
        //商品列表
        $goods = M('retail_goods')->alias('g')
            ->join('ls_retail_goods as rg on g.id=rg.id', 'LEFT')
            ->order('g.status asc')
            ->where($where)
            ->where('g.goods_num >rg.more_num')
            ->select();
        $goods_class = M('retail_goods_class')->where(array('storeid' => $storeid, 'status' => 0))->select();
        foreach ($goods as $key => &$value) {
            //查询出分类名称
            foreach ($goods_class as $k => $v) {
                if ($value['goods_class_id'] == $v['id']) {
                    $goods[$key]['cate_name'] = $v['class_name'];
                    break;
                }
            }
            if ($value['status'] == 0) {
                $goods[$key]['status_name'] = '正常';
            } elseif ($value['status'] == 1) {
                $goods[$key]['status_name'] = '已下架';
            } elseif ($value['status'] == 2) {
                $goods[$key]['status_name'] = '已删除';
            }
            $goods[$key]['goods_code'] = $goods[$key]['goods_code'] ? $goods[$key]['goods_code'] : '';
        }
        $filename = "门店超量商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('cate_name', '所属分类'),
            array('purch_price', '成本价'),
            array('last_purch_price', '当前进货价'),
            array('retail_price', '零售价'),
            array('online_price', '线上价'),
            array('goods_num', '商品系统库存'),
            array('more_num', '库存上限(超量值)'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $goods, $str);
    }

    // 导出不足商品数据
    public function exporttablegoodsless()
    {
        $storeid = session('storeid');
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $where['g.good_number|g.goods_name'] = $keyword;
        }
        //按照商品类别搜索
        $person1 = I('get.person1');
        $person2 = I('get.person2');
        $person3 = I('get.person3');
        $flag = 0;
        if (!empty($person3)) {
            $where['g.goods_class_id'] = $person3;
        } else if (!empty($person2)) {
            $class = M('retail_goods_class as c2')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid')
                ->where(array('c2.id' => $person2, 'c3.status' => 0))
                ->field('c3.id')->select();
            $flag = 1;
        } else if (!empty($person1)) {
            $class = M('retail_goods_class as c1')
                ->join('ls_retail_goods_class as c2 on c1.id=c2.pid')
                ->join('ls_retail_goods_class as c3 on c2.id=c3.pid AND c3.status=0', 'LEFT')
                ->where(array('c1.id' => $person1, 'c2.status' => 0))
                ->field('c2.id,c3.id as c3_id')->select();
            $flag = 1;
        }
        if (!empty($class)) {
            $arr = array();
            foreach ($class as &$val) {
                if (!in_array($val['id'], $arr)) {
                    $arr[] = $val['id'];
                }
                if (!empty($val['c3_id'])) {
                    if (!in_array($val['c3_id'], $arr)) {
                        $arr[] = $val['c3_id'];
                    }
                }
            }
            $str = implode(',', $arr);
            $where['g.goods_class_id'] = array('in', $str . ',' . ($person2 ? $person2 : $person1));
        } else if ($flag == 1) {
            $where['g.goods_class_id'] = $person2 ? $person2 : $person1;
        }
        $where['g.storeid'] = $storeid;
        $where['g.status'] = array('in', array(0, 1));
        //商品列表
        $goods = M('retail_goods')->alias('g')
            ->join('ls_retail_goods as rg on g.id=rg.id', 'LEFT')
            ->order('g.status asc')
            ->where($where)
            ->where('g.goods_num <rg.less_num')
            ->select();
        $goods_class = M('retail_goods_class')->where(array('storeid' => $storeid, 'status' => 0))->select();
        foreach ($goods as $key => &$value) {
            //查询出分类名称
            foreach ($goods_class as $k => $v) {
                if ($value['goods_class_id'] == $v['id']) {
                    $goods[$key]['cate_name'] = $v['class_name'];
                    break;
                }
            }
            //商品状态
            if ($value['status'] == 0) {
                $goods[$key]['status_name'] = '正常';
            } elseif ($value['status'] == 1) {
                $goods[$key]['status_name'] = '已下架';
            } elseif ($value['status'] == 2) {
                $goods[$key]['status_name'] = '已删除';
            }
            //是否允许修改折扣价
            if ($value['canchangeprice'] == 1) {
                $goods[$key]['canchangeprice_name'] = '是';
            } else {
                $goods[$key]['canchangeprice_name'] = '不允许';
            }
            if ($value['issellonline'] == 0) {
                $goods[$key]['issellonline_name'] = '否';
            } else {
                $goods[$key]['issellonline_name'] = '是';
            }
            $goods[$key]['goods_code'] = $goods[$key]['goods_code'] ? $goods[$key]['goods_code'] : '';
        }
        $filename = "门店不足商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('cate_name', '所属分类'),
            array('purch_price', '成本价'),
            array('last_purch_price', '当前进价'),
            array('retail_price', '零售价'),
            array('goods_num', '商品系统库存'),
            array('less_num', '库存下限(预警值)'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $goods, $str);
    }


    /**
     * 2018-10-09   会员信息导出
     */
    public function exportVipInfo()
    {
        $user = session('ls_userinfo');
        $store_id = I("get.store_id");          //门店为0时所有门店
        $vipstatus = I('get.vipstatus');        //会员状态
        $keyword = I('get.keyword');            //手机号实体卡号昵称
        $where['v.status'] = array('neq', 2);
        if ($store_id == 0) {
            //查询出该商户所有的会员信息
            $where['v.merchantid'] = $user['merchantid'];
        } else {
            //根据商户选择的门店进行会员信息查询并导出
            $where['v.storeid'] = $store_id;
            $where['v.merchantid'] = $user['merchantid'];
        }
        if ($vipstatus == 0) {
            $where['v.status'] = array('neq', 2);
        } else if ($vipstatus == 1) {
            $where['v.status'] = 1;
        } else if ($vipstatus = 3) {
            $where['v.status'] = 3;
        } else if ($vipstatus = 4) {
            $where['v.status'] = 4;
        }
        if (!empty($keyword)) {
            $where['v.nickname|v.mobile_phone|v.card_key'] = array('like', "%" . $keyword . "%");
        }
        $vipInfo = M('retail_vip as v')
            ->join('ls_retail_store as s on v.storeid=s.id')
            ->where($where)
            ->order('v.addtime desc')
            ->field('v.*,s.storetag')
            ->select();
        if ($vipInfo) {
            foreach ($vipInfo as $key => &$val) {
                $val['index'] = $key + 1;
                $val['name'] = $val['name'] ? $val['name'] : "未设置";
                $val['balance'] = $val['balance'] ? $val['balance'] : "0.00";
                $val['nickname'] = $val['nickname'] ? $val['nickname'] : "未设置";
                $one = M('retail_vip_integral')
                    ->where(array('min_integral' => array('ELT', $val['grade_integral'])))
                    ->order('min_integral desc')
                    ->find();
                $val['grade'] = $one ? $one['cardname'] : '默认会员';
                if ($val['sex'] == 1) {
                    $val['sex'] = '男';
                } elseif ($val['sex'] == 2) {
                    $val['sex'] = '女';
                } else {
                    $val['sex'] = '保密';
                }

                if ($val['is_register'] == 1) {
                    $val['is_register'] = '是';
                } else {
                    $val['is_register'] = '否';
                }
                if ($one['discount'] == 1) {
                    $val['discount'] = '不享受折扣';
                } else {
                    $val['discount'] = $one ? $one['discount'] * 10 . '折' : '不打折';
                }
                $val['addtime'] = date('Y-m-d H:i:s', $val['addtime']);
                if (empty($val['card_key'])) {
                    $val['card_key_whether'] = '否';
                } else {
                    $val['card_key_whether'] = '是';
                }
                if (empty($val['card_key'])) {
                    $val['card_key'] = '暂无';
                }
                if ($val['status'] == 1) {
                    $val['statusname'] = "正常会员";
                } else if ($val['status'] == 2) {
                    $val['statusname'] = "注销会员";
                } else if ($val['status'] == 3) {
                    $val['statusname'] = "冻结会员";
                } else if ($val['status'] == 4) {
                    $val['statusname'] = "挂失会员";
                }
            }
        }
        $filename = "会员信息统计";
        $headArr = array(
            array('index', '序号'),
            array('storetag', '门店名称'),
            array('statusname', '会员状态'),
            array('mobile_phone', '手机号码'),
            array('card_key', '实体卡'),
            array('name', '姓名'),
            array('nickname', '昵称'),
            array('sex', '性别'),
            array('balance', '会员卡余额'),
            array('grade', '会员等级'),
            array('discount', '会员折扣'),
            array('addtime', '创建时间'),
            array('province', '省份'),
            array('city', '城市'),
            array('birthday', '生日'),
            array('integral', '当前积分'),
            array('grade_integral', '等级积分'),
            array('is_register', '是否激活公众号会员'),
            array('cardnum', '会员卡号'),
            array('card_key_whether', '是否绑定实体卡')
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $vipInfo, $str);
    }

    public function exportStoreSaleData()
    {
        $user = session('ls_userinfo');
        $time = I('get.date');
        if (!empty($time)) {
            $times = explode('至', $time);
            $start_time = strtotime(date('Y-m-d 00:00:00', strtotime($times[0])));
            $end_time = strtotime(date('Y-m-d 23:59:59', strtotime($times[1])));
        } else {
            $start_time = strtotime(date('Y-m-d 00:00:00', time()));
            $end_time = $start_time + 86399;
        }
        $merchantid = $user['merchantid'];
        $where['merchantid'] = $merchantid;
        $where['store_status'] = 0;
        $where['state'] = 0;
        $store_info = M('retail_store')->where($where)->field('id,storetag')->select();                     //门店正常并且开启

        foreach ($store_info as $key => &$val) {
            $map_duct = array();
            $map_store = array();
            $map_shop = array();
            $map_store['merchantid'] = $merchantid;
            $map_store['storeid'] = $val['id'];
            $map_store['pay_state'] = 1;
            $map_store['paytype'] = array('neq', 0);
            $map_store['addtime'] = array('between', array($start_time, $end_time));
            $map_store['type'] = 2;                   //店内
            $map_store['order_type'] = 1;            //购买订单
            $val['d_total_sales'] = M('retail_bill')->where($map_store)->sum('allmoney');                     //店内总销售金额
            $val['d_total_sales'] = $val['d_total_sales'] ? $val['d_total_sales'] : 0;
            $val['d_total_sales_num'] = M('retail_bill')->where($map_store)->count();                         //店内总销售笔数

            $map_store['order_type'] = 2;
            $map_store['refund_status'] = 3;
            $val['d_refund_total_sales'] = M('retail_bill')->where($map_store)->sum('allmoney');              //店内退款总金额
            $val['d_refund_total_sales'] = $val['d_refund_total_sales'] ? $val['d_refund_total_sales'] : 0;
            $val['d_refund_total_sales_num'] = M('retail_bill')->where($map_store)->count();                 //店内退款总笔数

            $val['d_clean_total_sales'] = round($val['d_total_sales'] - $val['d_refund_total_sales'], 2);         //店内总净销售额
            $map_store['order_type'] = 1;
            $map_store['refund_status'] = array('in', '0,1,2,3');
            $map_store['paytype'] = 3;
            $val['d_wxpay'] = M('retail_bill')->where($map_store)->sum('allmoney');               //店内微信总售金额
            $val['d_wxpay'] = $val['d_wxpay'] ? $val['d_wxpay'] : 0;
            $val['d_wxpay_num'] = M('retail_bill')->where($map_store)->count();                   //店内微信总销售笔数

            $map_store['paytype'] = 4;
            $val['d_alipay'] = M('retail_bill')->where($map_store)->sum('allmoney');             //店内支付宝总金额
            $val['d_alipay'] = $val['d_alipay'] ? $val['d_alipay'] : 0;
            $val['d_alipay_num'] = M('retail_bill')->where($map_store)->count();                 //店内支付宝总笔数

            $map_store['paytype'] = 1;
            $val['d_cash'] = M('retail_bill')->where($map_store)->sum('allmoney');                //店内现金金额
            $val['d_cash'] = $val['d_cash'] ? $val['d_cash'] : 0;
            $val['d_cash_num'] = M('retail_bill')->where($map_store)->count();                     //店内现金笔数

            $map_store['paytype'] = 5;
            $val['d_vippay'] = M('retail_bill')->where($map_store)->sum('allmoney');               //店内会员金额
            $val['d_vippay'] = $val['d_vippay'] ? $val['d_vippay'] : 0;
            $val['d_vippay_num'] = M('retail_bill')->where($map_store)->count();                   //店内会员笔数

            $map_store['paytype'] = 7;
            $val['d_otherpay'] = M('retail_bill')->where($map_store)->sum('allmoney');             //店内其他金额
            $val['d_otherpay'] = $val['d_otherpay'] ? $val['d_otherpay'] : 0;
            $val['d_otherpay_num'] = M('retail_bill')->where($map_store)->count();                  //店内其他笔数

            $map_store['order_type'] = 2;
            $map_store['refund_status'] = 3;
            $map_store['paytype'] = 3;
            $val['d_refund_wxpay'] = M('retail_bill')->where($map_store)->sum('allmoney');                   //微信退款金额
            $val['d_refund_wxpay'] = $val['d_refund_wxpay'] ? $val['d_refund_wxpay'] : 0;
            $val['d_refund_wxpay_num'] = M('retail_bill')->where($map_store)->count();                       //微信退款笔数

            $map_store['paytype'] = 4;
            $val['d_refund_alipay'] = M('retail_bill')->where($map_store)->sum('allmoney');                //支付宝退款金额
            $val['d_refund_alipay'] = $val['d_refund_alipay'] ? $val['d_refund_alipay'] : 0;
            $val['d_refund_alipay_num'] = M('retail_bill')->where($map_store)->count();                     //支付宝退款笔数

            $map_store['paytype'] = 1;
            $val['d_refund_cash'] = M('retail_bill')->where($map_store)->sum('allmoney');                  //现金退款金额
            $val['d_refund_cash'] = $val['d_refund_cash'] ? $val['d_refund_cash'] : 0;
            $val['d_refund_cash_num'] = M('retail_bill')->where($map_store)->count();                       //现金退款笔数

            $map_store['paytype'] = 5;
            $val['d_refund_vippay'] = M('retail_bill')->where($map_store)->sum('allmoney');                //会员退款金额
            $val['d_refund_vippay'] = $val['d_refund_vippay'] ? $val['d_refund_vippay'] : 0;
            $val['d_refund_vippay_num'] = M('retail_bill')->where($map_store)->count();                     //会员退款笔数

            $map_store['paytype'] = 7;
            $val['d_refund_otherpay'] = M('retail_bill')->where($map_store)->sum('allmoney');              //其他退款金额
            $val['d_refund_otherpay'] = $val['d_refund_otherpay'] ? $val['d_refund_otherpay'] : 0;
            $val['d_refund_otherpay_num'] = M('retail_bill')->where($map_store)->count();                  // 其他退款笔数

            //商城
            $map_shop['addtime'] = array('between', array($start_time, $end_time));
            $map_shop['merchantid'] = $merchantid;
            $map_shop['storeid'] = $val['id'];
            $map_shop['pay_state'] = 1;
            $map_shop['paytype'] = array('in', '5,6');
            $map_shop['type'] = 1;

            $map_shop['order_type'] = 1;
            $val['s_total_sales'] = M('retail_bill')->where($map_shop)->sum('allmoney');                 //商城总销售金额
            $val['s_total_sales'] = $val['s_total_sales'] ? $val['s_total_sales'] : 0;
            $val['s_total_sales_num'] = M('retail_bill')->where($map_shop)->count();                      //商城总销售笔数

            $map_shop['order_type'] = 2;
            $map_shop['refund_status'] = 2;
            $val['s_refund_total_sales'] = M('retail_bill')->where($map_shop)->sum('allmoney');          //商城退款金额
            $val['s_refund_total_sales'] = $val['s_refund_total_sales'] ? $val['s_refund_total_sales'] : 0;
            $val['s_refund_total_sales_num'] = M('retail_bill')->where($map_shop)->count();             //商城退款笔数
            $val['s_clean_total_sales'] = round($val['s_total_sales'] - $val['s_refund_total_sales'], 2);  //商城净销售额

            $map_shop['paytype'] = 6;
            $val['s_refund_wxpay'] = M('retail_bill')->where($map_shop)->sum('allmoney');            //商城微信退款金额
            $val['s_refund_wxpay'] = $val['s_refund_wxpay'] ? $val['s_refund_wxpay'] : 0;
            $val['s_refund_wxpay_num'] = M('retail_bill')->where($map_shop)->count();                //商城退款笔数

            $map_shop['paytype'] = 5;
            $val['s_refund_vippay'] = M('retail_bill')->where($map_shop)->sum('allmoney');          //会员退款金额
            $val['s_refund_vippay'] = $val['s_refund_vippay'] ? $val['s_refund_vippay'] : 0;
            $val['s_refund_vippay_num'] = M('retail_bill')->where($map_shop)->count();              //会员退款笔数

            $map_shop['order_type'] = 1;
            $map_shop['refund_status'] = array('in', '0,1,2,3');
            $map_shop['paytype'] = 5;
            $vippay = M('retail_bill')->where($map_shop)->sum('allmoney');                    //商城会员金额
            $val['s_vippay'] = $vippay ? $vippay : 0;

            $map_shop['paytype'] = 6;
            $vipwxpay = M('retail_bill')->where($map_shop)->sum('allmoney');                   //商城微信订单金额
            $val['s_wxpay'] = $vipwxpay ? $vipwxpay : 0;

            $map_shop['paytype'] = 6;
            $val['s_wxpay_num'] = M('retail_bill')->where($map_shop)->count();                   //商城微信订单笔数

            $map_shop['paytype'] = 5;
            $val['s_vippay_num'] = M('retail_bill')->where($map_shop)->count();                  //商城会员订单笔数

            $val['z_clean_total_sales'] = round($val['d_clean_total_sales'] + $val['s_clean_total_sales'], 2);                    //小计总净销售额
            $val['z_total_sales'] = round($val['s_total_sales'] + $val['d_total_sales'], 2);                                         //小计总销售额
            $val['z_total_sales_num'] = round($val['d_total_sales_num'] + $val['s_total_sales_num'], 2);                            //小计销售笔数
            $val['z_refund_total_sales'] = round($val['d_refund_total_sales'] + $val['s_refund_total_sales'], 2);                 //小计退款金额
            $val['z_refund_total_sales_num'] = round($val['d_refund_total_sales_num'] + $val['s_refund_total_sales_num'], 2);     //小计退款总笔数


            $val['z_wxpay'] = round($val['d_wxpay'] + $val['s_wxpay'], 2);                                                               //小计微信销售金额
            $val['z_wxpay_num'] = round($val['d_wxpay_num'] + $val['s_wxpay_num'], 2);                                                 //小计微信笔数
            $val['z_refund_wxpay'] = round($val['d_refund_wxpay'] + $val['s_refund_wxpay'], 2);                                       //小计微信退款金额
            $val['z_refund_wxpay_num'] = round($val['s_refund_wxpay_num'] + $val['d_refund_wxpay_num'], 2);                          //小计微信退款笔数

            $val['z_vippay'] = round($val['d_vippay'] + $val['s_vippay'], 2);                                                           //小计会员金额
            $val['z_vippay_num'] = round($val['d_vippay_num'] + $val['s_vippay_num'], 2);                                             //小计会员笔数
            $val['z_refund_vippay'] = round($val['d_refund_vippay'] + $val['s_refund_vippay'], 2);                                   //小计会员退款金额
            $val['z_refund_vippay_num'] = round($val['d_refund_vippay_num'] + $val['s_refund_vippay_num'], 2);                       //小计会员退款笔数


            //充值记录
            $map_recharge['addtime'] = array('between', array($start_time, $end_time));
            $map_recharge['merchantid'] = $merchantid;
            $map_recharge['storeid'] = $val['id'];

            $map_recharge['type'] = 2;
            $map_recharge['paytype'] = 1;
            $val['v_st_cashpay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内现金充值
            $val['v_st_cashpay'] = $val['v_st_cashpay'] ? $val['v_st_cashpay'] : 0;

            $v_total_cashpay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');     //店内现金获得金额
            $v_total_cashpay = $v_total_cashpay ? $v_total_cashpay : 0;

            $val['v_cash_discount'] = round($v_total_cashpay - $val['v_st_cashpay'], 2);                    //店内现金优惠金额

            $map_recharge['paytype'] = 2;
            $val['v_st_wxpay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内微信充值
            $val['v_st_wxpay'] = $val['v_st_wxpay'] ? $val['v_st_wxpay'] : 0;

            $v_total_wxpay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');       //店内微信总金额
            $val['v_wxpay_discount'] = round($v_total_wxpay - $val['v_st_wxpay'], 2);                        //店内微信优惠金额

            $map_recharge['paytype'] = 3;
            $val['v_st_alipay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内支付宝充值
            $val['v_st_alipay'] = $val['v_st_alipay'] ? $val['v_st_alipay'] : 0;
            $v_total_alipay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');      //店内支付宝总金额
            $val['v_alipay_discount'] = round($v_total_alipay - $val['v_st_alipay'], 2);                   //店内支付宝优惠

            $map_recharge['type'] = 4;
            $map_recharge['usertype'] = 2;
            $map_recharge['paytype'] = 1;
            $val['v_stditpay'] = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');      //门店编辑赠送
            $val['v_stditpay'] = $val['v_stditpay'] ? $val['v_stditpay'] : 0;

            //门店扣除
            $map_duct['addtime'] = array('between', array($start_time, $end_time));
            $map_duct['merchantid'] = $merchantid;
            $map_duct['storeid'] = $val['id'];
            $map_duct['type'] = 4;
            $map_duct['usertype'] = 2;
            $val['v_st_duct'] = M('recharge_order')->where($map_duct)->sum('money');           //门店扣除
            $val['v_st_duct'] = $val['v_st_duct'] ? $val['v_st_duct'] : 0;
            $map_duct['usertype'] = 3;
            $val['v_cashier_duct'] = M('recharge_order')->where($map_duct)->sum('money');       //收银员扣除
            $val['v_cashier_duct'] = $val['v_cashier_duct'] ? $val['v_cashier_duct'] : 0;
        }
        $recharge = array();
        $map_vip['addtime'] = array('between', array($start_time, $end_time));
        $map_vip['merchantid'] = $merchantid;
        $recharge['v_total_pay'] = M('retail_viprecharge')->where($map_vip)->sum('money');
        $recharge['v_total_pay'] = $recharge['v_total_pay'] ? $recharge['v_total_pay'] : 0;           //实际总的充值金额

        $total_recharge_pay = M('retail_viprecharge')->where($map_vip)->sum('allmoney');             //总获得的充值金额
        $total_recharge_pay = $total_recharge_pay ? $total_recharge_pay : 0;

        $recharge['v_total_discount'] = round($total_recharge_pay - $recharge['v_total_pay'], 2);              // 总优惠金额

        $map_vip['type'] = 1;
        $recharge['v_wxpay'] = M('retail_viprecharge')->where($map_vip)->sum('money');          //公众号充值
        $recharge['v_wxpay'] = $recharge['v_wxpay'] ? $recharge['v_wxpay'] : 0;

        $map_vip['type'] = 4;
        $map_vip['usertype'] = 1;
        $recharge['v_merpay'] = M('retail_viprecharge')->where($map_vip)->sum('money');      //商户充值
        $recharge['v_merpay'] = $recharge['v_merpay'] ? $recharge['v_merpay'] : 0;

        $v_merpay_allmoney = M('retail_viprecharge')->where($map_vip)->sum('allmoney');      //商户赠送
        $v_merpay_allmoney = $v_merpay_allmoney?$v_merpay_allmoney:0;
        $recharge['v_merpay_discount'] =round($v_merpay_allmoney - $recharge['v_merpay'],2);

        $map_vip['type'] = 3;
        $recharge['v_xcxpay'] = M('retail_viprecharge')->where($map_vip)->sum('money');     //小程序充值
        $recharge['v_xcxpay'] = $recharge['v_xcxpay'] ? $recharge['v_xcxpay'] : 0;

        //商户扣除
        $map_de['addtime'] = array('between', array($start_time, $end_time));
        $map_de['merchantid'] = $merchantid;
        $map_de['type'] = 4;

        $recharge['v_total_duct'] = M('recharge_order')->where($map_de)->sum('money');         //总扣除
        $recharge['v_total_duct'] = $recharge['v_total_duct'] ? $recharge['v_total_duct'] : 0;

        $map_de['usertype'] = 1;
        $recharge['v_mer_duct'] = M('recharge_order')->where($map_de)->sum('money');        //商户扣除
        $recharge['v_mer_duct'] = $recharge['v_mer_duct'] ? $recharge['v_mer_duct'] : 0;

        $filename = "所有门店销售汇总";
        if (!$time) {
            $time = date('Y-m-d H:i:s', $start_time) . '至' . date('Y-m-d H:i:s', $end_time);
        }
        $common = $time . '，导出时间:' . date('Y-m-d H:i:s');
        $this->getStoreSaleDataExcel($filename, $store_info, $recharge, $common);
    }

    private function getStoreSaleDataExcel($filename, $expTableData, $recharge, $common = "")
    {
        $filename = iconv('utf-8', 'gb2312', $filename);//文件名称
        $_fileName = $filename . date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        vendor("PHPExcel.PHPExcel");
        ob_clean();
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:AO1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $common);

        //表头
        $objPHPExcel->getActiveSheet(0)->mergeCells('A2:A4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', '门店');
        $objPHPExcel->getActiveSheet(0)->mergeCells('B2:B4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '渠道');
        $objPHPExcel->getActiveSheet(0)->mergeCells('C2:C4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', '净销售额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('D2:D4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', '销售额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('E2:E4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', '销售笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('F2:F4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', '退款金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('G2:G4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('H2:K3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2', '微信支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('L2:O3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L2', '会员卡支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('P2:S3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P2', '支付宝支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('T2:W3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T2', '现金支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('X2:AA3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X2', '其他支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA4', '退款笔数');

        $objPHPExcel->getActiveSheet(0)->mergeCells('AB2:AO2');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB2', '会员充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AB3:AB4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB3', '类型');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AC3:AE3');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC3', '店内');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC4', '微信');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD4', '支付宝');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE4', '现金');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AF3:AF4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF3', '门店后台充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AG3:AG4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG3', '门店后台扣除');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AH3:AH4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AH3', '收银员扣除');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AI3:AI4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI3', '小程序');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AJ3:AJ4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AJ3', '公众号');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AK3:AK4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AK3', '商户后台充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AL3:AL4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AL3', '商户后台赠送');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AM3:AM4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AM3', '商户后台扣除');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AN3:AN4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AN3', '实际充值金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AO3:AO4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AO3', '赠送金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AP3:AP4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AP3', '扣除总金额');

        foreach ($expTableData as $key => $val) {
            $objPHPExcel->getActiveSheet(0)->mergeCells('A' . ($key * 3 + 5) . ':A' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($key * 3 + 5), $val['storetag']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 5), '小计');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 5), $val['z_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 5), $val['z_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 5), $val['z_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 5), $val['z_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 5), $val['z_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 5), $val['z_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 5), $val['z_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 5), $val['z_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 5), $val['z_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 5), $val['z_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 5), $val['z_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 5), $val['z_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 5), $val['z_refund_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 5), $val['d_alipay']);//支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 5), $val['d_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 5), $val['d_refund_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 5), $val['d_refund_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 5), $val['d_cash']);//现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 5), $val['d_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 5), $val['d_refund_cash']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 5), $val['d_refund_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . ($key * 3 + 5), $val['d_otherpay']);//其他
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . ($key * 3 + 5), $val['d_otherpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . ($key * 3 + 5), $val['d_refund_otherpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . ($key * 3 + 5), $val['d_refund_otherpay_num']);


            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 6), '店内');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 6), $val['d_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 6), $val['d_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 6), $val['d_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 6), $val['d_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 6), $val['d_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 6), $val['d_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 6), $val['d_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 6), $val['d_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 6), $val['d_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 6), $val['d_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 6), $val['d_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 6), $val['d_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 6), $val['d_refund_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 6), $val['d_alipay']);//支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 6), $val['d_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 6), $val['d_refund_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 6), $val['d_refund_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 6), $val['d_cash']);//现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 6), $val['d_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 6), $val['d_refund_cash']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 6), $val['d_refund_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . ($key * 3 + 6), $val['d_otherpay']);//其他
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . ($key * 3 + 6), $val['d_otherpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . ($key * 3 + 6), $val['d_refund_otherpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . ($key * 3 + 6), $val['d_refund_otherpay_num']);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 7), '商城');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 7), $val['s_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 7), $val['s_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 7), $val['s_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 7), $val['s_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 7), $val['s_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 7), $val['s_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 7), $val['s_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 7), $val['s_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 7), $val['s_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 7), $val['s_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 7), $val['s_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 7), $val['s_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 7), $val['s_refund_vippay_num']);
            $objPHPExcel->getActiveSheet(0)->mergeCells('P' . ($key * 3 + 7) . ':AA' . ($key * 3 + 7));//合并单元格

            $objPHPExcel->getActiveSheet(0)->mergeCells('AB' . ($key * 3 + 5) . ':AB' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . ($key * 3 + 5), '充值');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . ($key * 3 + 7), '赠送');

            $objPHPExcel->getActiveSheet(0)->mergeCells('AC' . ($key * 3 + 5) . ':AC' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells('AD' . ($key * 3 + 5) . ':AD' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells('AE' . ($key * 3 + 5) . ':AE' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . ($key * 3 + 5), $val['v_st_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . ($key * 3 + 5), $val['v_st_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . ($key * 3 + 5), $val['v_st_cashpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . ($key * 3 + 7), $val['v_wxpay_discount']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . ($key * 3 + 7), $val['v_alipay_discount']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . ($key * 3 + 7), $val['v_cash_discount']);

            $objPHPExcel->getActiveSheet(0)->mergeCells('AF' . ($key * 3 + 5) . ':AF' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells('AG' . ($key * 3 + 5) . ':AG' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells('AH' . ($key * 3 + 5) . ':AH' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . ($key * 3 + 5), $val['v_stditpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG' . ($key * 3 + 5), $val['v_st_duct']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AH' . ($key * 3 + 5), $val['v_cashier_duct']);

            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AH' . ($key * 3 + 7))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            if (($key * 3 + 3) % 6 == 0 || ($key * 3 + 4) % 6 == 0 || ($key * 3 + 5) % 6 == 0) {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AH' . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFF9FAFC');
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AH' . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFFFFFFF');
            }
        }
        $storeNum = count($expTableData);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AI5:AI' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI5', $recharge['v_xcxpay']);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AJ5:AJ' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AJ5', $recharge['v_wxpay']);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AK5:AK' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AK5', $recharge['v_merpay']);

        $objPHPExcel->getActiveSheet(0)->mergeCells('AL5:AL' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AL5', $recharge['v_merpay_discount']);

        $objPHPExcel->getActiveSheet(0)->mergeCells('AM5:AM' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AM5', $recharge['v_mer_duct']);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AN5:AN' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AN5', $recharge['v_total_pay']);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AO5:AO' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AO5', $recharge['v_total_discount']);
        $objPHPExcel->getActiveSheet(0)->mergeCells('AP5:AP' . ($storeNum * 3 + 4));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AP5', $recharge['v_total_duct']);

        $titleArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP');

        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array(                               //allborders  表示全部线框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    'color' => array('argb' => 'FFDDDDDD'),          //设置border颜色
                ),
            ),
        );
        $count = ($storeNum * 3) + 5;
        foreach ($titleArr as $key => $value) {
            for ($i = 2; $i <= 4; $i++) {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFill()->getStartColor()->setARGB('FFF3F9FF');
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFont()->getColor()->setARGB('FF333333');
                $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight('30');
                $objPHPExcel->getActiveSheet()->getStyle($value . $i)->applyFromArray($styleThinBlackBorderOutline);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFont()->setBold(true);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }
            for ($j = 5; $j <= $count; $j++) {
                if ($key < 7 && ($j - 2) % 3 == 0) {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->setBold(true);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->getColor()->setARGB('FF333333');
                } else {
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->getColor()->setARGB('FF666666');
                }
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle($value . $j)->applyFromArray($styleThinBlackBorderOutline);
                $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight('30');
            }
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);
        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $filename . '.xls"');
        header("Content-Disposition:attachment;filename=$_fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit();
    }


    /*
     * 门店
     * */
    public function exportOneStoreSaleData()
    {
        $user = session('ls_userinfo');
        $time = I('get.date');
        $storeid = session('storeid');
        if (!empty($time)) {
            $times = explode('至', $time);
            $start_time = strtotime(date('Y-m-d', strtotime($times[0])));
            $end_time = strtotime(date('Y-m-d', strtotime($times[1])));
            $days = round($end_time - $start_time) / 86400;
            if ($days > 61) {
                $res['status'] = 0;
                $res['msg'] = '单页最多只能查看60天的数据';
                $this->ajaxReturn($res);
            }
            $times = array();
            $end_time = $end_time + 86400;
            for ($i = 0; $i < $days; $i++) {
                $end_time = strtotime(date('Y-m-d 00:00:00', $end_time - 86400));
                $start_time = $end_time + 86399;
                $times[] = $end_time . ',' . $start_time;
            }
        } else {
            $times = array();
            $start_time = strtotime(date('Y-m-d 00:00:00', time()));
            $end_time = $start_time + 86399;
            $times[] = $start_time . ',' . $end_time;
            $start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-1 day")));
            $end_time = $start_time + 86399;
            $times[] = $start_time . ',' . $end_time;
            $start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-2 day")));
            $end_time = $start_time + 86399;
            $times[] = $start_time . ',' . $end_time;
            $start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-3 day")));
            $end_time = $start_time + 86399;
            $times[] = $start_time . ',' . $end_time;
            $start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-4 day")));
            $end_time = $start_time + 86399;
            $times[] = $start_time . ',' . $end_time;
        }
        $merchantid = $user['merchantid'];
        $where['id'] = $storeid;
        $where['merchantid'] = $merchantid;
        $store_info = M('retail_store')->where($where)->find();
        if ($store_info) {
            $payInfo = array();
            foreach ($times as $key => &$val) {
                $map_store = array();
                $map_shop = array();
                $map_recharge = array();
                $map_duct = array();
                $search_time = explode(',', $val);
                $start_time = $search_time[0];
                $end_time = $search_time[1];
                $payInfo[$key]['time'] = date('Y-m-d', $start_time);
                $map_store['merchantid'] = $merchantid;
                $map_store['storeid'] = $storeid;
                $map_store['pay_state'] = 1;
                $map_store['paytype'] = array('neq', 0);
                $map_store['addtime'] = array('between', array($start_time, $end_time));
                $map_store['type'] = 2;                   //店内
                $map_store['order_type'] = 1;            //购买订单
                $payInfo[$key]['d_total_sales'] = M('retail_bill')->where($map_store)->sum('allmoney');                     //店内总销售金额
                $payInfo[$key]['d_total_sales'] = $payInfo[$key]['d_total_sales'] ? $payInfo[$key]['d_total_sales'] : 0;
                $payInfo[$key]['d_total_sales_num'] = M('retail_bill')->where($map_store)->count();                         //店内总销售笔数

                $map_store['order_type'] = 2;
                $map_store['refund_status'] = 3;
                $payInfo[$key]['d_refund_total_sales'] = M('retail_bill')->where($map_store)->sum('allmoney');              //店内退款总金额
                $payInfo[$key]['d_refund_total_sales'] = $payInfo[$key]['d_refund_total_sales'] ? $payInfo[$key]['d_refund_total_sales'] : 0;
                $payInfo[$key]['d_refund_total_sales_num'] = M('retail_bill')->where($map_store)->count();                 //店内退款总笔数

                $payInfo[$key]['d_clean_total_sales'] = round(($payInfo[$key]['d_total_sales'] - $payInfo[$key]['d_refund_total_sales']), 2);
                //店内总净销售额
                $map_store['order_type'] = 1;
                $map_store['refund_status'] = array('in', '0,1,2,3');
                $map_store['paytype'] = 3;
                $payInfo[$key]['d_wxpay'] = M('retail_bill')->where($map_store)->sum('allmoney');               //店内微信总售金额
                $payInfo[$key]['d_wxpay'] = $payInfo[$key]['d_wxpay'] ? $payInfo[$key]['d_wxpay'] : 0;
                $payInfo[$key]['d_wxpay_num'] = M('retail_bill')->where($map_store)->count();                   //店内微信总销售笔数

                $map_store['paytype'] = 4;
                $payInfo[$key]['d_alipay'] = M('retail_bill')->where($map_store)->sum('allmoney');             //店内支付宝总金额
                $payInfo[$key]['d_alipay'] = $payInfo[$key]['d_alipay'] ? $payInfo[$key]['d_alipay'] : 0;
                $payInfo[$key]['d_alipay_num'] = M('retail_bill')->where($map_store)->count();                 //店内支付宝总笔数

                $map_store['paytype'] = 1;
                $payInfo[$key]['d_cash'] = M('retail_bill')->where($map_store)->sum('allmoney');                //店内现金金额
                $payInfo[$key]['d_cash'] = $payInfo[$key]['d_cash'] ? $payInfo[$key]['d_cash'] : 0;
                $payInfo[$key]['d_cash_num'] = M('retail_bill')->where($map_store)->count();                     //店内现金笔数

                $map_store['paytype'] = 5;
                $payInfo[$key]['d_vippay'] = M('retail_bill')->where($map_store)->sum('allmoney');               //店内会员金额
                $payInfo[$key]['d_vippay'] = $payInfo[$key]['d_vippay'] ? $payInfo[$key]['d_vippay'] : 0;
                $payInfo[$key]['d_vippay_num'] = M('retail_bill')->where($map_store)->count();                   //店内会员笔数

                $map_store['paytype'] = 7;
                $payInfo[$key]['d_otherpay'] = M('retail_bill')->where($map_store)->sum('allmoney');             //店内其他金额
                $payInfo[$key]['d_otherpay'] = $payInfo[$key]['d_otherpay'] ? $payInfo[$key]['d_otherpay'] : 0;
                $payInfo[$key]['d_otherpay_num'] = M('retail_bill')->where($map_store)->count();                  //店内其他笔数

                $map_store['order_type'] = 2;
                $map_store['refund_status'] = 3;
                $map_store['paytype'] = 3;
                $payInfo[$key]['d_refund_wxpay'] = M('retail_bill')->where($map_store)->sum('allmoney');                   //微信退款金额
                $payInfo[$key]['d_refund_wxpay'] = $payInfo[$key]['d_refund_wxpay'] ? $payInfo[$key]['d_refund_wxpay'] : 0;
                $payInfo[$key]['d_refund_wxpay_num'] = M('retail_bill')->where($map_store)->count();                       //微信退款笔数

                $map_store['paytype'] = 4;
                $payInfo[$key]['d_refund_alipay'] = M('retail_bill')->where($map_store)->sum('allmoney');                //支付宝退款金额
                $payInfo[$key]['d_refund_alipay'] = $payInfo[$key]['d_refund_alipay'] ? $payInfo[$key]['d_refund_alipay'] : 0;
                $payInfo[$key]['d_refund_alipay_num'] = M('retail_bill')->where($map_store)->count();                     //支付宝退款笔数

                $map_store['paytype'] = 1;
                $payInfo[$key]['d_refund_cash'] = M('retail_bill')->where($map_store)->sum('allmoney');                  //现金退款金额
                $payInfo[$key]['d_refund_cash'] = $payInfo[$key]['d_refund_cash'] ? $payInfo[$key]['d_refund_cash'] : 0;
                $payInfo[$key]['d_refund_cash_num'] = M('retail_bill')->where($map_store)->count();                       //现金退款笔数

                $map_store['paytype'] = 5;
                $payInfo[$key]['d_refund_vippay'] = M('retail_bill')->where($map_store)->sum('allmoney');
                $payInfo[$key]['d_refund_vippay'] = $payInfo[$key]['d_refund_vippay'] ? $payInfo[$key]['d_refund_vippay'] : 0;

                $payInfo[$key]['d_refund_vippay_num'] = M('retail_bill')->where($map_store)->count();                     //会员退款笔数

                $map_store['paytype'] = 7;
                $payInfo[$key]['d_refund_otherpay'] = M('retail_bill')->where($map_store)->sum('allmoney');              //其他退款金额
                $payInfo[$key]['d_refund_otherpay'] = $payInfo[$key]['d_refund_otherpay'] ? $payInfo[$key]['d_refund_otherpay'] : 0;
                $payInfo[$key]['d_refund_otherpay_num'] = M('retail_bill')->where($map_store)->count();                  // 其他退款笔数

                //商城
                $map_shop['addtime'] = array('between', array($start_time, $end_time));
                $map_shop['merchantid'] = $merchantid;
                $map_shop['storeid'] = $storeid;
                $map_shop['pay_state'] = 1;
                $map_shop['paytype'] = array('in', '5,6');
                $map_shop['type'] = 1;

                $map_shop['order_type'] = 1;
                $payInfo[$key]['s_total_sales'] = M('retail_bill')->where($map_shop)->sum('allmoney');                 //商城总销售金额
                $payInfo[$key]['s_total_sales'] = $payInfo[$key]['s_total_sales'] ? $payInfo[$key]['s_total_sales'] : 0;
                $payInfo[$key]['s_total_sales_num'] = M('retail_bill')->where($map_shop)->count();                      //商城总销售笔数

                $map_shop['order_type'] = 2;
                $map_shop['refund_status'] = 2;
                $payInfo[$key]['s_refund_total_sales'] = M('retail_bill')->where($map_shop)->sum('allmoney');          //商城退款金额
                $payInfo[$key]['s_refund_total_sales'] = $payInfo[$key]['s_refund_total_sales'] ? $payInfo[$key]['s_refund_total_sales'] : 0;
                $payInfo[$key]['s_refund_total_sales_num'] = M('retail_bill')->where($map_shop)->count();             //商城退款笔数
                $payInfo[$key]['s_clean_total_sales'] = round($payInfo[$key]['s_total_sales'] - $payInfo[$key]['s_refund_total_sales'], 2);  //商城净销售额

                $map_shop['paytype'] = 6;
                $payInfo[$key]['s_refund_wxpay'] = M('retail_bill')->where($map_shop)->sum('allmoney');            //商城微信退款金额
                $payInfo[$key]['s_refund_wxpay'] = $payInfo[$key]['s_refund_wxpay'] ? $payInfo[$key]['s_refund_wxpay'] : 0;
                $payInfo[$key]['s_refund_wxpay_num'] = M('retail_bill')->where($map_shop)->count();                //商城退款笔数

                $map_shop['paytype'] = 5;
                $payInfo[$key]['s_refund_vippay'] = M('retail_bill')->where($map_shop)->sum('allmoney');          //会员退款金额
                $payInfo[$key]['s_refund_vippay'] = $payInfo[$key]['s_refund_vippay'] ? $payInfo[$key]['s_refund_vippay'] : 0;
                $payInfo[$key]['s_refund_vippay_num'] = M('retail_bill')->where($map_shop)->count();              //会员退款笔数

                $map_shop['order_type'] = 1;
                $map_shop['refund_status'] = array('in', '0,1,2,3');
                $map_shop['paytype'] = 5;
                $vippay = M('retail_bill')->where($map_shop)->sum('allmoney');                    //商城会员金额
                $payInfo[$key]['s_vippay'] = $vippay ? $vippay : 0;

                $map_shop['paytype'] = 6;
                $vipwxpay = M('retail_bill')->where($map_shop)->sum('allmoney');                   //商城微信订单金额
                $payInfo[$key]['s_wxpay'] = $vipwxpay ? $vipwxpay : 0;

                $map_shop['paytype'] = 6;
                $payInfo[$key]['s_wxpay_num'] = M('retail_bill')->where($map_shop)->count();                   //商城微信订单笔数

                $map_shop['paytype'] = 5;
                $payInfo[$key]['s_vippay_num'] = M('retail_bill')->where($map_shop)->count();                  //商城会员订单笔数

                $payInfo[$key]['z_clean_total_sales'] = round($payInfo[$key]['d_clean_total_sales'] + $payInfo[$key]['s_clean_total_sales'], 2);                    //小计总净销售额
                $payInfo[$key]['z_total_sales'] = round($payInfo[$key]['s_total_sales'] + $payInfo[$key]['d_total_sales'], 2);                                         //小计总销售额
                $payInfo[$key]['z_total_sales_num'] = round($payInfo[$key]['d_total_sales_num'] + $payInfo[$key]['s_total_sales_num'], 2);                            //小计销售笔数
                $payInfo[$key]['z_refund_total_sales'] = round($payInfo[$key]['d_refund_total_sales'] + $payInfo[$key]['s_refund_total_sales'], 2);                 //小计退款金额
                $payInfo[$key]['z_refund_total_sales_num'] = round($payInfo[$key]['d_refund_total_sales_num'] + $payInfo[$key]['s_refund_total_sales_num'], 2);     //小计退款总笔数

                $payInfo[$key]['z_wxpay'] = round($payInfo[$key]['d_wxpay'] + $payInfo[$key]['s_wxpay'], 2);                                                               //小计微信销售金额
                $payInfo[$key]['z_wxpay_num'] = round($payInfo[$key]['d_wxpay_num'] + $payInfo[$key]['s_wxpay_num'], 2);                                                 //小计微信笔数
                $payInfo[$key]['z_refund_wxpay'] = round($payInfo[$key]['d_refund_wxpay'] + $payInfo[$key]['s_refund_wxpay'], 2);                                       //小计微信退款金额
                $payInfo[$key]['z_refund_wxpay_num'] = round($payInfo[$key]['s_refund_wxpay_num'] + $payInfo[$key]['d_refund_wxpay_num'], 2);                          //小计微信退款笔数

                $payInfo[$key]['z_vippay'] = round($payInfo[$key]['d_vippay'] + $payInfo[$key]['s_vippay'], 2);                                                           //小计会员金额
                $payInfo[$key]['z_vippay_num'] = round($payInfo[$key]['d_vippay_num'] + $payInfo[$key]['s_vippay_num'], 2);                                             //小计会员笔数
                $payInfo[$key]['z_refund_vippay'] = round($payInfo[$key]['d_refund_vippay'] + $payInfo[$key]['s_refund_vippay'], 2);                                   //小计会员退款金额
                $payInfo[$key]['z_refund_vippay_num'] = round($payInfo[$key]['d_refund_vippay_num'] + $payInfo[$key]['s_refund_vippay_num'], 2);                       //小计会员退款笔数

                //充值记录
                $map_recharge['addtime'] = array('between', array($start_time, $end_time));
                $map_recharge['merchantid'] = $merchantid;
                $map_recharge['storeid'] = $storeid;
                /*$payInfo[$key]['v_total_pay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');
                $payInfo[$key]['v_total_pay'] = $payInfo[$key]['v_total_pay'] ? $payInfo[$key]['v_total_pay'] : 0;           //实际总的充值金额

                $total_recharge_pay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');
                $payInfo[$key]['v_total_discount'] = round($total_recharge_pay - $payInfo[$key]['v_total_pay'], 2);*/

                $map_recharge['type'] = 2;
                $map_recharge['paytype'] = 1;
                $payInfo[$key]['v_st_cashpay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内现金充值
                $payInfo[$key]['v_st_cashpay'] = $payInfo[$key]['v_st_cashpay'] ? $payInfo[$key]['v_st_cashpay'] : 0;

                $v_total_cashpay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');     //店内现金获得金额
                $v_total_cashpay = $v_total_cashpay ? $v_total_cashpay : 0;

                $payInfo[$key]['v_cash_discount'] = round($v_total_cashpay - $payInfo[$key]['v_st_cashpay'], 2);                    //店内现金优惠金额

                $map_recharge['paytype'] = 2;
                $payInfo[$key]['v_st_wxpay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内微信充值
                $payInfo[$key]['v_st_wxpay'] = $payInfo[$key]['v_st_wxpay'] ? $payInfo[$key]['v_st_wxpay'] : 0;

                $v_total_wxpay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');       //店内微信总金额
                $payInfo[$key]['v_wxpay_discount'] = round($v_total_wxpay - $payInfo[$key]['v_st_wxpay'], 2);                        //店内微信优惠金额

                $map_recharge['paytype'] = 3;
                $payInfo[$key]['v_st_alipay'] = M('retail_viprecharge')->where($map_recharge)->sum('money');  //店内支付宝充值
                $payInfo[$key]['v_st_alipay'] = $payInfo[$key]['v_st_alipay'] ? $payInfo[$key]['v_st_alipay'] : 0;
                $v_total_alipay = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');      //店内支付宝总金额
                $payInfo[$key]['v_alipay_discount'] = round($v_total_alipay - $payInfo[$key]['v_st_alipay'], 2);                   //店内支付宝优惠

                $map_recharge['type'] = 4;
                $map_recharge['usertype'] = 2;
                $map_recharge['paytype'] = 1;
                $payInfo[$key]['v_stditpay'] = M('retail_viprecharge')->where($map_recharge)->sum('allmoney');       //门店后台编辑赠送
                $payInfo[$key]['v_stditpay'] = $payInfo[$key]['v_stditpay'] ? $payInfo[$key]['v_stditpay'] : 0;


                $payInfo[$key]['v_total_pay'] = round($payInfo[$key]['v_st_cashpay'] + $payInfo[$key]['v_st_wxpay'] + $payInfo[$key]['v_st_alipay'], 2);  //门店实际充值金额
                $payInfo[$key]['v_total_discount'] = round($payInfo[$key]['v_cash_discount'] + $payInfo[$key]['v_wxpay_discount'] + $payInfo[$key]['v_alipay_discount'] + $payInfo[$key]['v_stditpay']); //门店赠送金额


                $map_duct['addtime'] = array('between', array($start_time, $end_time));
                $map_duct['merchantid'] = $merchantid;
                $map_duct['storeid'] = $storeid;
                $map_duct['type'] = 4;
                $map_duct['usertype'] = 2;
                $v_st_duct = M('recharge_order')->where($map_duct)->sum('money');           //门店扣除
                $payInfo[$key]['v_st_duct'] = $v_st_duct ? $v_st_duct : 0;
                $map_duct['usertype'] = 3;
                $payInfo[$key]['v_cashier_duct'] = M('recharge_order')->where($map_duct)->sum('money');       //收银员扣除
                $payInfo[$key]['v_cashier_duct'] = $payInfo[$key]['v_cashier_duct'] ? $payInfo[$key]['v_cashier_duct'] : 0;
                $payInfo[$key]['v_total_duct'] = round($payInfo[$key]['v_cashier_duct'] + $payInfo[$key]['v_st_duct'], 2);  //总扣除
            }
        } else {
            $payInfo = array();
        }

        $filename = $store_info['storetag'] . "销售汇总";
        if (!$time) {
            $time = date('Y-m-d H:i:s', $times[0]) . '至' . date('Y-m-d H:i:s', $times[1]);
        }
        $common = $time . '，导出时间:' . date('Y-m-d H:i:s');
        $this->getOneStoreSaleDataExcel($filename, $payInfo, $common);
    }

    private function getOneStoreSaleDataExcel($filename, $expTableData, $common = "")
    {
        $filename = iconv('utf-8', 'gb2312', $filename);//文件名称
        $_fileName = $filename . date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        vendor("PHPExcel.PHPExcel");
        ob_clean();
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:Ak1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $common);

        //表头
        $objPHPExcel->getActiveSheet(0)->mergeCells('A2:A4');//日期
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', '日期');
        $objPHPExcel->getActiveSheet(0)->mergeCells('B2:B4');//渠道
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '渠道');
        $objPHPExcel->getActiveSheet(0)->mergeCells('C2:C4');//净销售额
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', '净销售额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('D2:D4');//销售额
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', '销售额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('E2:E4');//销售笔数
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', '销售笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('F2:F4');//退款金额
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', '退款金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('G2:G4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('H2:K3');//微信支付
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2', '微信支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('L2:O3');//会员卡支付
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L2', '会员卡支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('P2:S3');//支付宝支付
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P2', '支付宝支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('T2:W3');//现金支付
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T2', '现金支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W4', '退款笔数');
        $objPHPExcel->getActiveSheet(0)->mergeCells('X2:AA3');//其他支付
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X2', '其他支付');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X4', '销售金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y4', '销售笔数');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z4', '退款金额');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA4', '退款笔数');

        $objPHPExcel->getActiveSheet(0)->mergeCells('AB2:AK2');//会员充值
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB2', '会员充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AB3:AB4');//类型
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB3', '类型');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AC3:AE3');//店内
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC3', '店内');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC4', '微信');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD4', '支付宝');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE4', '现金');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AF3:AF4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF3', '门店后台赠送');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AG3:AG4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG3', '门店后台扣除');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AH3:AH4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AH3', '收银员扣除');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AI3:AI4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI3', '实际充值金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AJ3:AJ4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AJ3', '赠送金额');
        $objPHPExcel->getActiveSheet(0)->mergeCells('AK3:AK4');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AK3', '扣除总金额');

        foreach ($expTableData as $key => $val) {
            $objPHPExcel->getActiveSheet(0)->mergeCells('A' . ($key * 3 + 5) . ':A' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($key * 3 + 5), $val['time']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 5), '小计');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 5), $val['z_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 5), $val['z_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 5), $val['z_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 5), $val['z_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 5), $val['z_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 5), $val['z_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 5), $val['z_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 5), $val['z_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 5), $val['z_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 5), $val['z_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 5), $val['z_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 5), $val['z_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 5), $val['z_refund_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 5), $val['d_alipay']);//支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 5), $val['d_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 5), $val['d_refund_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 5), $val['d_refund_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 5), $val['d_cash']);//现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 5), $val['d_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 5), $val['d_refund_cash']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 5), $val['d_refund_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . ($key * 3 + 5), $val['d_otherpay']);//其他
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . ($key * 3 + 5), $val['d_otherpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . ($key * 3 + 5), $val['d_refund_otherpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . ($key * 3 + 5), $val['d_refund_otherpay_num']);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 6), '店内');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 6), $val['d_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 6), $val['d_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 6), $val['d_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 6), $val['d_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 6), $val['d_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 6), $val['d_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 6), $val['d_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 6), $val['d_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 6), $val['d_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 6), $val['d_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 6), $val['d_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 6), $val['d_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 6), $val['d_refund_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 6), $val['d_alipay']);//支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 6), $val['d_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 6), $val['d_refund_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 6), $val['d_refund_alipay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 6), $val['d_cash']);//现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 6), $val['d_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 6), $val['d_refund_cash']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 6), $val['d_refund_cash_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . ($key * 3 + 6), $val['d_otherpay']);//其他
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . ($key * 3 + 6), $val['d_otherpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . ($key * 3 + 6), $val['d_refund_otherpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . ($key * 3 + 6), $val['d_refund_otherpay_num']);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 7), '商城');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 7), $val['s_clean_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 7), $val['s_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 7), $val['s_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 7), $val['s_refund_total_sales']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 7), $val['s_refund_total_sales_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 7), $val['s_wxpay']);//微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 7), $val['s_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 7), $val['s_refund_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 7), $val['s_refund_wxpay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 7), $val['s_vippay']);//会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 7), $val['s_vippay_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 7), $val['s_refund_vippay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 7), $val['s_refund_vippay_num']);
            $objPHPExcel->getActiveSheet(0)->mergeCells('P' . ($key * 3 + 7) . ':AA' . ($key * 3 + 7));//合并单元格

            $objPHPExcel->getActiveSheet(0)->mergeCells('AB' . ($key * 3 + 5) . ':AB' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . ($key * 3 + 5), '充值');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . ($key * 3 + 7), '赠送');

            $objPHPExcel->getActiveSheet(0)->mergeCells('AC' . ($key * 3 + 5) . ':AC' . ($key * 3 + 6));//合并单元格   店内
            $objPHPExcel->getActiveSheet(0)->mergeCells('AD' . ($key * 3 + 5) . ':AD' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells('AE' . ($key * 3 + 5) . ':AE' . ($key * 3 + 6));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . ($key * 3 + 5), $val['v_st_wxpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . ($key * 3 + 5), $val['v_st_alipay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . ($key * 3 + 5), $val['v_st_cashpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . ($key * 3 + 7), $val['v_wxpay_discount']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . ($key * 3 + 7), $val['v_alipay_discount']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . ($key * 3 + 7), $val['v_cash_discount']);

            $objPHPExcel->getActiveSheet(0)->mergeCells('AF' . ($key * 3 + 5) . ':AF' . ($key * 3 + 7));//合并单元格     商户充值
            $objPHPExcel->getActiveSheet(0)->mergeCells('AG' . ($key * 3 + 5) . ':AG' . ($key * 3 + 7));//合并单元格     商户扣除
            $objPHPExcel->getActiveSheet(0)->mergeCells('AH' . ($key * 3 + 5) . ':AH' . ($key * 3 + 7));//合并单元格     收银员扣除
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . ($key * 3 + 5), $val['v_stditpay']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG' . ($key * 3 + 5), $val['v_st_duct']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AH' . ($key * 3 + 5), $val['v_cashier_duct']);

            $objPHPExcel->getActiveSheet(0)->mergeCells('AI' . ($key * 3 + 5) . ':AI' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI' . ($key * 3 + 5), $val['v_total_pay']);
            $objPHPExcel->getActiveSheet(0)->mergeCells('AJ' . ($key * 3 + 5) . ':AJ' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AJ' . ($key * 3 + 5), $val['v_total_discount']);
            $objPHPExcel->getActiveSheet(0)->mergeCells('AK' . ($key * 3 + 5) . ':AK' . ($key * 3 + 7));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AK' . ($key * 3 + 5), $val['v_total_duct']);

            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AK' . ($key * 3 + 7))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            if (($key * 3 + 3) % 6 == 0 || ($key * 3 + 4) % 6 == 0 || ($key * 3 + 5) % 6 == 0) {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AK' . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFF9FAFC');
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':AK' . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFFFFFFF');
            }
        }
        $titleArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK');
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array(                               //allborders  表示全部线框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    'color' => array('argb' => 'FFDDDDDD'),          //设置border颜色
                ),
            ),
        );
        $storeNum = count($expTableData);
        $count = ($storeNum * 3) + 5;
        foreach ($titleArr as $key => $value) {
            for ($i = 2; $i <= 4; $i++) {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFill()->getStartColor()->setARGB('FFF3F9FF');
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFont()->getColor()->setARGB('FF333333');
                $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight('30');
                $objPHPExcel->getActiveSheet()->getStyle($value . $i)->applyFromArray($styleThinBlackBorderOutline);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getFont()->setBold(true);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }
            if ($storeNum) {
                for ($j = 5; $j <= $count; $j++) {
                    if ($key < 7 && ($j - 2) % 3 == 0) {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->setBold(true);
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->getColor()->setARGB('FF333333');
                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getFont()->getColor()->setARGB('FF666666');
                    }
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle($value . $j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle($value . $j)->applyFromArray($styleThinBlackBorderOutline);
                    $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight('30');
                }
            }

            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $filename . '.xls"');
        header("Content-Disposition:attachment;filename=$_fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit();
    }
}