mashangyoujiu/Application/Retail/Controller/ExporttableController.class.php

<?php

namespace Retail\Controller;

use Common\Business\DownFile;
use Common\Business\OperateMGoods;
use Common\Model\RetailStoreModel;
use Common\Model\SkuGoodsViewModel;
use Common\Model\SkuMGoodsViewModel;
use Common\Order\Statistics;
use Think\Controller;

//[1.8.50]
class ExporttableController extends BaseController
{
    /**
     * 门店商品档案导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function goods()
    {
        $platform_type = $this->role_info['platform_type'] ? $this->role_info['platform_type'] : 1;
        $store_id = $this->store_id;
        if (IS_POST) {
            $page = I('post.page') ? I('post.page') : 1;
            $limit = 1000;
            $directory_name = I('post.directory_name');
            $param = array(
                'store_id' => $store_id,
                'platform_type'=>$platform_type,
                'merchant_id'=>$this->merchant_id,
                'showpurch_price' => session('global_store_jurisdiction')['showpurch_price'], //进价查看权限
                'identity' => I('session.identity'), //寄售类型
                'page' => $page, //页
                'limit' => $limit, //每页数量
                'need_goods_num' => true,
                'need_img' => true,
                'fields' => array(
                    'id',
                    'goods_class_id',
                    'goods_name',
                    'goods_number',
                    'goods_code',
                    'purchase_price',
                    'retail_price',
                    'online_price',
                    'store_mem_status',
                    'store_mem_price',
                    'plus_mem_status',
                    'plus_price',
                    'online_state',
                    'shop_mem_status',
                    'shop_mem_price',
                    'vip_online_state',
                    'vip_retail_state',
                    'integral_mem_status',
                    'integral',
                    'guide_royalty_state',
                    'guide_royalty_custom_state',
                    'guide_royalty_price',
                    'guide_royalty_online_price',
                    'goods_num',
                    'more_num',
                    'less_num',
                    'sell_num',
                    'fictitious_num',
                    'company',
                    'weight_value',
                    'sku_weight_value',
                    'specifications',
                    'pricing_method',
                    'production_date',
                    'shelf_life',
                    'status',
                    'goods_img_id',
                    'third_goods_code'
                ),
                'attribute_state' => I('post.attribute_state'),    //规格  1无规格  2规格
                'keyword' => I('post.keyword'), //搜索关键词
                'keyword_type' => I('post.keyword_type'), //关键词搜索类型 1货号 2条码 3名称
                'order_type' => I('post.order_type'), //排序规则
                'condition_name' => I('post.condition_name'), //排序规则关键词
                'pricing_method' => I('post.pricing_method'),
                'online_state' => I('post.online_state'),
                'recovery_type' => I('post.recovery_type'), //2回收站
                'advance_sale_status' => I('post.advance_sale_status'),
                //  按照商品类别搜索
                'person1' => I('post.person1'),
                'person2' => I('post.person2'),
                'person3' => I('post.person3'),
            );
            $SkuGoodsViewModel = new SkuGoodsViewModel();
            $ret = $SkuGoodsViewModel->getGoodsList($param);
            if (empty($ret['data'])) {
                $this->ajaxReturn(array(
                    'status' => 0,
                    'msg' => '暂无商品可导出'
                ));
            }
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Vendor.PHPExcel.PHPExcel");
            import("Vendor.PHPExcel.Writer.Excel5");
            import("Vendor.PHPExcel.IOFactory.php");
            $filename = "storeGoods_NO.{$page}";
            $headArr = array(
                array('id', 'ID'),
                array('cate_name', '分类'),
                array('goods_name', '商品名称'),
                array('goods_number', '货号'),
                array('goods_code', '条码'),
                array('purchase_price', '成本价'),
                array('retail_price', '零售价'),
                array('online_price', '线上价'),
                array('store_mem_name', '是否开启店内会员价'),
                array('store_mem_price', '店内会员价格'),
                array('plus_mem_name', ' plus会员价开关'),
                array('plus_price', 'Plus员价'),
                array('online_state_name', '是否在商城出售'),
                array('shop_mem_name', '是否开启商城会员价'),
                array('shop_mem_price', '线上会员价'),
                array('vip_online_name', '线上折扣'),
                array('vip_retail_name', '线下折扣'),
                array('integral_mem_name', '会员购买获得积分'),
                array('integral', '会员消费送积分'),
                array('guide_royalty_name', ' 购买该商品获得提成的状态'),
                array('guide_royalty_custom_name', ' 导购员获得提成自定义状态'),
                array('guide_royalty_price', ' 导购员线下固定金提成'),
                array('guide_royalty_online_price', ' 导购员线上固定金提成'),
                array('goods_num', '商品系统库存'),
                array('more_num', '库存上限(超量值)'),
                array('less_num', '库存下限(预警值)'),
                array('sell_num', '已售数量'),
                array('fictitious_num', '虚拟销量'),
                array('company', '单位'),
                array('weight_value', '重量(单位:kg)'),
                array('specifications', '规格'),
                array('pricing_method_name', '称重'),
                array('production_date', '生产日期'),
                array('shelf_life', '到期时间'),
                array('status_name', '状态'),
                array('goods_img', '图片'),
            );
            //第三方平台
            if ($platform_type == 2) {
                $headArr[] = ['third_goods_code', '第三方平台商品编码'];
            }
            $str = $filename . ' 表格生成时间:' . date('Y-m-d H:i:s');
            if (!$directory_name) {
                $num = createKey();
                $directory_name = "./Uploads/storeGoodsExcel/{$store_id}/{$num}";
            }
            $this->setExcel($filename, $headArr, $ret['data'], $str, $directory_name);
            $res = array(
                'status' => 1,
                'type' => 1,
                'directory_name' => $directory_name,
                'msg' => '请继续生成文件'
            );
            if (count($ret['data']) < $limit) {
                $res['type'] = 2;
                $res['msg'] = '数据生成完毕';
            }
            $this->ajaxReturn($res);
        } else if (IS_GET) {
            $directory_name = I('get.directory_name');
            $online_state = I('get.online_state');
            $attribute_state = I('get.attribute_state');
            if (is_dir($directory_name)) {
                $zip = new \ZipArchive();
                $dir = ltrim($directory_name, './');
                $arr = explode('/', $dir);
                unset($arr[count($arr) - 1]);
                $zipName = implode('/', $arr);
                $name = $online_state == 1 ? '商城商品档案导出' : ($attribute_state == 1 ? '无规格商品导出' : '商品档案导出');
                $zip_url = './' . $zipName . '/' . $name . date('Y-m-d_H_i_s') . '.zip';

                if ($zip->open($zip_url, \ZipArchive::CREATE) === TRUE) {

                    $this->addFileToZip($dir, $zip, $name); //调用方法，对要打包的根目录进行操作，并将ZipArchive的对象传递给方法
                    $zip->close(); //关闭处理的zip文件

                    if (!file_exists($zip_url)) {  //判断文件是否存在
                        echo "文件不存在";
                        exit();
                    }
                    $file = fopen($zip_url, "r");
                    Header("Content-type:application/octet-stream");
                    header("Accept-Ranges: bytes");
                    header("Accept-Length: " . filesize($zip_url));
                    header("Content-Disposition: attachment; filename={$name}" . date('Y-m-d_H:i:s') . ".zip");
                    echo fread($file, filesize($zip_url));
                    fclose($file);

                    $this->delDirFile($directory_name); //删除文件夹下的所有文件
                    rmdir($directory_name); //删除当前文件夹
                    unlink($zip_url); //删除压缩包
                }
            } else {
                echo '文件不存在';
            }
        }
    }


    /**
     * 商户商品档案导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function mGoods()
    {
        $platform_type = $this->role_info['platform_type'] ? $this->role_info['platform_type'] : 1;
        $merchant_id = $this->merchant_id;
        if (IS_POST) {
            $page = I('post.page') ? I('post.page') : 1;
            $limit = 1000;
            $directory_name = I('post.directory_name');
            $param = array(
                'merchant_id' => $merchant_id,
                'page' => $page, //页
                'limit' => $limit, //每页数量
                'need_goods_num' => true,
                'need_img' => true,
                'fields' => array(
                    'id',
                    'goods_class_id',
                    'goods_name',
                    'good_number',
                    'goods_code',
                    'purch_price',
                    'retail_price',
                    'online_price',
                    'store_mem_status',
                    'store_mem_price',
                    'plus_mem_status',
                    'plus_price',
                    'online_state',
                    'shop_mem_status',
                    'shop_mem_price',
                    'vip_online_state',
                    'vip_retail_state',
                    'more_num',
                    'less_num',
                    'company',
                    'weight_value',
                    'sku_weight_value',
                    'specifications',
                    'can_exchange_weigh',
                    'status',
                    'goods_image_id',
                    'third_goods_code'
                ),
                'keyword' => I('post.keyword'),
                'keyword_type' => I('post.keyword_type'),
                'can_exchange_weigh' => I('post.can_exchange_weigh'),
                'condition_name' => I("post.condition_name"), //排序规则关键词
                'order_type' => I("post.order_type"), //排序规则
                //按照商品类别搜索
                'person1' => I('post.person1'),
                'person2' => I('post.person2'),
                'person3' => I('post.person3'),
                'issellonline' => I('post.issellonline'), //商城出售状态
                'attribute_state' => I('post.attribute'), //规格  1无规格  2规格
                'recovery_type' => I('post.recovery_type'),   //2回收站
            );
            $SkuGoodsViewModel = new OperateMGoods();
            $ret = $SkuGoodsViewModel->getMerGoodsList($param);
            if (empty($ret['data'])) {
                $this->ajaxReturn(array(
                    'status' => 0,
                    'msg' => '暂无商品可导出'
                ));
            }
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Vendor.PHPExcel.PHPExcel");
            import("Vendor.PHPExcel.Writer.Excel5");
            import("Vendor.PHPExcel.IOFactory.php");
            $filename = "storeGoods_NO.{$page}";
            $headArr = array(
                array('id', 'ID'),
                array('cate_name', '分类'),
                array('goods_name', '商品名称'),
                array('good_number', '货号'),
                array('goods_code', '条码'),
                array('purch_price', '成本价'),
                array('retail_price', '零售价'),
                array('online_price', '线上价'),
                array('store_mem_name', '是否开启店内会员价'),
                array('store_mem_price', '店内会员价格'),
                array('plus_mem_name', ' plus会员价开关'),
                array('plus_price', 'Plus员价'),
                array('online_state_name', '是否在商城出售'),
                array('shop_mem_name', '是否开启商城会员价'),
                array('shop_mem_price', '线上会员价'),
                array('vip_online_name', '线上折扣'),
                array('vip_retail_name', '线下折扣'),
                array('more_num', '库存上限(超量值)'),
                array('less_num', '库存下限(预警值)'),
                array('company', '单位'),
                array('weight_value', '重量(单位:kg)'),
                array('specifications', '规格'),
                array('can_exchange_weigh_name', '称重'),
                array('status_name', '状态'),
                array('goods_img', '图片'),
            );
            //第三方平台
            if ($platform_type == 2) {
                $headArr[] = ['third_goods_code', '第三方平台商品编码'];
            }else{
                $headArr[] = ['goods_num', '商品系统库存'];
            }
            $str = $filename . ' 表格生成时间:' . date('Y-m-d H:i:s');
            if (!$directory_name) {
                $directory_str = createKey();
                $directory_name = "./Uploads/MerGoodsExcel/{$merchant_id}/{$directory_str}";
            }
            $this->setExcel($filename, $headArr, $ret['data'], $str, $directory_name);
            $res = array(
                'status' => 1,
                'type' => 1,
                'directory_name' => $directory_name,
                'msg' => '请继续生成文件'
            );
            if (count($ret['data']) < $limit) {
                $res['type'] = 2;
                $res['msg'] = '数据生成完毕';
            }
            $this->ajaxReturn($res);
        } else if (IS_GET) {
            $directory_name = I('get.directory_name');
            $attribute = I('get.attribute');
            if (is_dir($directory_name)) {
                $zip = new \ZipArchive();
                $dir = ltrim($directory_name, './');
                $arr = explode('/', $dir);
                unset($arr[count($arr) - 1]);
                $zipName = implode('/', $arr);
                $name = $attribute == 1 ? '无规格商品导出' : '商品档案导出';
                $zip_url = './' . $zipName . '/' . $name . date('Y-m-d_H_i_s') . '.zip';

                if ($zip->open($zip_url, \ZipArchive::CREATE) === TRUE) {

                    $this->addFileToZip($dir, $zip, $name); //调用方法，对要打包的根目录进行操作，并将ZipArchive的对象传递给方法
                    $zip->close(); //关闭处理的zip文件

                    if (!file_exists($zip_url)) {  //判断文件是否存在
                        echo "文件不存在";
                        exit();
                    }
                    $file = fopen($zip_url, "r");
                    Header("Content-type:application/octet-stream");
                    header("Accept-Ranges: bytes");
                    header("Accept-Length: " . filesize($zip_url));
                    header("Content-Disposition: attachment; filename={$name}" . date('Y-m-d_H:i:s') . ".zip");
                    echo fread($file, filesize($zip_url));
                    fclose($file);

                    $this->delDirFile($directory_name); //删除文件夹下的所有文件
                    rmdir($directory_name); //删除当前文件夹
                    unlink($zip_url); //删除压缩包
                }
            } else {
                echo '文件不存在';
            }
        }
    }

    /**
     * 门店规格商品导出
     */
    public function SkuGoodsTable()
    {
        $global_store_jurisdiction = session('global_store_jurisdiction');
        $storeid = session('storeid');
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        //按照商品类别搜索
        $condition_name = I('get.condition_name');
        $order_type = I('get.order_type');
        if ($condition_name == 2) { //货号
            $order = "g.sku_goods_number";
        } else {
            $order = "g.id";
        }
        if ($order_type == 2) { //降序
            $ascending_order = 'desc';
        } else {
            $ascending_order = 'asc';
        }
        $where['g.store_id'] = $storeid;
        $where['g.state'] = 0;
        //规格商品列表
        $sku_goods = M('retail_sku_goods as g')
            ->join("ls_retail_store_sale_goods as s on g.id=s.sku_id")
            ->where($where)
            ->order("$order $ascending_order")
            ->field('g.goods_id,g.weight_value,g.purchase_price,g.online_price,g.retail_price,g.sku_num,g.shop_mem_price,g.store_mem_price,g.plus_price,g.add_time,g.status,g.sku_goods_number,g.sku_code,g.less_num,g.more_num,g.guide_royalty_price,g.guide_royalty_online_price,s.goods_num,s.goods_name,s.sku_name')
            ->select();
        foreach ($sku_goods as &$value) {
            if ($global_store_jurisdiction['showpurch_price'] == 2) {
                $value['purchase_price'] = "***";
            } else {
                $value['purchase_price'] = round($value['purchase_price'], 2);
            }
            $value['retail_price'] = round($value['retail_price'], 2);
            $value['goods_num'] = round($value['goods_num'], 3);
            $value['sku_num'] = round($value['sku_num'], 3);
            $value['shop_mem_price'] = round($value['shop_mem_price'], 2);
            $value['store_mem_price'] = round($value['store_mem_price'], 2);
            $value['plus_price'] = round($value['plus_price'], 2);
            $value['add_time'] = date('Y-m-d H:i:s', $value['add_time']);
            $value['less_num'] = round($value['less_num'], 3);
            $value['more_num'] = round($value['more_num'], 3);
            $value['guide_royalty_price'] = round($value['guide_royalty_price'], 2);
            $value['guide_royalty_online_price'] = round($value['guide_royalty_online_price'], 2);
            $value['weight_value'] = $value['weight_value'] ? round($value['weight_value'], 3) : '';
        }
        $filename = "规格商品导出";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('sku_name', '规格名称'),
            array('sku_goods_number', '货号'),
            array('sku_code', '条码'),
            array('weight_value', '重量(单位:kg)'),
            array('purchase_price', '成本价'),
            array('retail_price', '零售价'),
            array('goods_num', '商品库存'),
            array('sku_num', '规格基数'),
            array('shop_mem_price', '商城会员价格'),
            array('store_mem_price', '店内会员价格'),
            array('plus_price', 'plus会员价'),
            array('add_time', '添加时间'),
            array('less_num', '规格预警'),
            array('more_num', '规格超量'),
            array('guide_royalty_price', '导购员线下固定金提成'),
            array('guide_royalty_online_price', '导购员线上固定金提成'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $sku_goods, $str);
    }

    public function goods_new()
    {
        $global_store_jurisdiction = session('global_store_jurisdiction');
        $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'] = '开启';
            }
            if ($value['attribute'] == 2) {
                $value['purch_price'] = '规格进价';
                //                $value['last_purch_price'] = '规格进价';
                $value['plus_price'] = '规格plus会员价';
            }
            if ($value['plus_mem_status'] != 1) {
                $value['plus_price'] = '未开启';
            }
            if ($global_store_jurisdiction['showpurch_price'] == 2) {
                $value['purch_price'] = '***';
                $value['last_purch_price'] = '***';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            //array('purch_price', '成本价'),
            array('purch_price', '进货价'),
            array('retail_price', '零售价'),
            array('online_price', '线上价'),
            array('plus_price', 'plus会员价'),
            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 goods_new_three()
    {
        $global_store_jurisdiction = session('global_store_jurisdiction');
        $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'] = '开启';
            }
            if ($value['attribute'] == 2) {
                $value['purch_price'] = '规格成本价';
                $value['last_purch_price'] = '规格进价';
            }
            if ($global_store_jurisdiction['showpurch_price'] == 2) {
                $value['purch_price'] = '***';
                $value['last_purch_price'] = '***';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            array('purch_price', '成本价'),
            array('last_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_new()
    {
        $global_store_jurisdiction = session('global_store_jurisdiction');
        $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'] = '开启';
            }
            if ($value['attribute'] == 2) {
                $value['purch_price'] = '规格成本价';
                $value['last_purch_price'] = '规格进价';
                $value['plus_price'] = '规格plus会员价';
            }
            if ($value['plus_mem_status'] != 1) {
                $value['plus_price'] = '未开启';
            }
            if ($global_store_jurisdiction['showpurch_price'] == 2) {
                $value['purch_price'] = '***';
                $value['last_purch_price'] = '***';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            array('purch_price', '成本价'),
            array('last_purch_price', '进货价'),
            array('retail_price', '零售价'),
            array('online_price', '线上价'),
            array('plus_price', 'plus会员价'),
            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_new_three()
    {
        $global_store_jurisdiction = session('global_store_jurisdiction');
        $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'] = '开启';
            }
            if ($value['attribute'] == 2) {
                $value['purch_price'] = '规格成本价';
                $value['last_purch_price'] = '规格进价';
            }
            if ($global_store_jurisdiction['showpurch_price'] == 2) {
                $value['purch_price'] = '***';
                $value['last_purch_price'] = '***';
            }
        }
        $filename = "门店商品明细";
        $headArr = array(
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('good_number', '货号'),
            array('class_name', '所属分类'),
            array('purch_price', '成本价'),
            array('last_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()
    {
        //        header("Content-Type: text/html;charset=utf-8");
        $store_id = session('storeid');
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        $merchant_id = M('retail_store')->where(array('id' => $store_id))->getField('merchantid');

        $map = array(
            'c1.merchantid' => $merchant_id,
            'c1.status' => 0,
            'c1.type' => 1,
        );
        $fields = array(
            'c1.class_name as c1_class_name',
            'c2.class_name as c2_class_name',
            'c3.class_name as c3_class_name',
            'c1.code as c1_code',
            'c2.code as c2_code',
            'c3.code as c3_code',
            'c1.type as c1_type',
            'c2.type as c2_type',
            'c3.type as c3_type',
            'c1.id as c1_id',
            'c2.id as c2_id',
            'c3.id as c3_id',
            's1.online_state as s1_online_state',
            's2.online_state as s2_online_state',
            's3.online_state as s3_online_state',
        );
        $class_list = M('retail_goods_class as c1')
            ->join('ls_retail_goods_class as c2 on c2.pid=c1.id AND c2.type=2 AND c2.status=0', 'LEFT')
            ->join('ls_retail_goods_class as c3 on c3.pid=c2.id AND c3.type=3 AND c3.status=0', 'LEFT')
            ->join('ls_store_goods_class as s1 on s1.class_id=c1.id AND s1.store_id=' . $store_id, 'LEFT')
            ->join('ls_store_goods_class as s2 on s2.class_id=c2.id AND s2.store_id=' . $store_id, 'LEFT')
            ->join('ls_store_goods_class as s3 on s3.class_id=c3.id AND s3.store_id=' . $store_id, 'LEFT')
            ->where($map)
            ->order('c1.code,c1.id,c2.code,c2.id,c3.code,c3.id')
            ->field($fields)
            ->select();
        $export_class = array();
        $arrIdList = array();
        if (!empty($class_list)) {
            foreach ($class_list as $value) {
                if (!in_array($value['c1_id'], $arrIdList)) {
                    $temp = array(
                        'id' => $value['c1_id'],
                        'class_name' => $value['c1_class_name'],
                        'p_name' => '一级分类',
                        'p_name_path' => $value['c1_class_name'],
                        'code' => $value['c1_code'],
                        'online_state' => $value['s1_online_state'] ? '显示' : '不显示',
                    );
                    $export_class[] = $temp;
                    $arrIdList[] = $value['c1_id'];
                }
                if (!empty($value['c2_id']) && !in_array($value['c2_id'], $arrIdList)) {
                    $temp = array(
                        'id' => $value['c2_id'],
                        'class_name' => $value['c2_class_name'],
                        'p_name' => '二级分类',
                        'p_name_path' => $value['c1_class_name'] . "/" . $value['c2_class_name'],
                        'code' => $value['c2_code'],
                        'online_state' => $value['s2_online_state'] ? '显示' : '不显示',
                    );
                    $export_class[] = $temp;
                    $arrIdList[] = $value['c2_id'];
                }
                if (!empty($value['c3_type'])) {
                    $temp = array(
                        'id' => $value['c3_id'],
                        'class_name' => $value['c3_class_name'],
                        'p_name' => '三级分类',
                        'p_name_path' => $value['c1_class_name'] . "/" . $value['c2_class_name'] . "/" . $value['c3_class_name'],
                        'code' => $value['c3_code'],
                        'online_state' => $value['s3_online_state'] ? '显示' : '不显示',
                    );
                    $export_class[] = $temp;
                }
            }
        } else {
            $export_class = array();
        }
        $file_name = "商品分类明细";
        $header = array(
            array('class_name', '分类名称'),
            array('code', '编码'),
            array('p_name', '分类级别'),
            array('p_name_path', '分类详情'),
            array('online_state', '商城是否显示'),
        );
        $str = $file_name . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($file_name, $header, $export_class, $str);
    }


    /**
     * 导出表格
     * @param $expTitle
     * @param $expCellName
     * @param $expTableData
     * @param string $str
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    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();
    }

    /**
     * 写入表格 把表格保存在服务器
     * @param $expTitle
     * @param $expCellName
     * @param $expTableData
     * @param string $common
     * @param string $directory_name
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function setExcel($expTitle, $expCellName, $expTableData, $common = "", $directory_name = "./Public")
    {
        $fileName = $expTitle; //or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        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);
        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(25);
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $this->createDir($directory_name); //生成文件目录
        $path = "{$directory_name}/{$fileName}.xls";
        $objWriter->save($path);
    }

    /**
     * 生成文件目录
     * @param $directory_name 目录地址
     */
    private function createDir($directory_name)
    {
        if (!is_dir($directory_name)) {
            $temp = ltrim($directory_name, './');
            $arr = explode('/', $temp);
            $str = './';
            foreach ($arr as $key => $sv) {
                if (!is_dir("{$str}/{$sv}")) {
                    mkdir("{$str}/{$sv}", 0777);
                }
                $str = "{$str}/{$sv}";
            }
        }
    }

    /**
     * 删除目录下的所有文件
     * @param $dir  目录地址
     */
    private function delDirFile($dir)
    {
        $dh = opendir($dir);
        while ($file = readdir($dh)) {
            if ($file != "." && $file != "..") {
                $full_path = $dir . "/" . $file;
                if (!is_dir($full_path)) {
                    unlink($full_path);
                } else {
                    $this->delDirFile($full_path);
                }
            }
        }

        closedir($dh);
    }

    /**
     * 添加Excel表格到压缩包
     * @param $path
     * @param $zip
     * @param string $name
     */
    private function addFileToZip($path, $zip, $name = '商品档案导出')
    {
        $handler = opendir($path); //打开当前文件夹由$path指定。
        $index = 0;
        while (($filename = readdir($handler)) !== false) {
            if ($filename != "." && $filename != "..") { //文件夹文件名字为'.'和‘..'，不要对他们进行操作
                if (is_dir($path . "/" . $filename)) { // 如果读取的某个对象是文件夹，则递归
                    $this->addFileToZip($path . "/" . $filename, $zip, $name);
                } else { //将文件加入zip对象
                    $num = ++$index;
                    $sName = date("ymd") . $name . "-第" . $num . "页.xls";
                    $dir = iconv("utf-8", "gb2312", $sName);
                    $zip->addFile($path . "/" . $filename, $dir);
                }
            }
        }
        @closedir($handler);
    }

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

    public function new_goodsquantity()
    {
        $this->display();
    }

    public function quantityList()
    {
        $storeid = session('storeid');
        $where['l.storeid'] = $storeid;
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $where['goods_name|goods_code|goods_number'] = $keyword;
        }
        $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.goods_number,g.pricing_method')
            ->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['supplier_id'] == -9) {
                $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]['goods_number'] = $logs[$key]['goods_number'] ? $logs[$key]['goods_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);
            }
        }
        $str2 = "日期:" . date('Y-m-d H:i:s', time());
        $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');
        $merchantid = session('ls_userinfo')['merchantid'];
        //接收查询条件
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $where['g.goods_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.store_id'] = $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('merchantid' => $merchantid, '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;
                }
            }
            $goods[$key]['index'] = $key + 1;
            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('index', '序号'),
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('goods_number', '货号'),
            array('cate_name', '所属分类'),
            array('purchase_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');
        $merchantid = session('ls_userinfo')['merchantid'];
        $keyword = I('get.keyword');
        if (!empty($keyword)) {
            $where['goods_number|goods_name'] = $keyword;
        }
        //按照商品类别搜索
        $person1 = I('get.person1');
        $person2 = I('get.person2');
        $person3 = I('get.person3');
        $flag = 0;
        if (!empty($person3)) {
            $where['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['goods_class_id'] = array('in', $str . ',' . ($person2 ? $person2 : $person1));
        } else if ($flag == 1) {
            $where['goods_class_id'] = $person2 ? $person2 : $person1;
        }
        $where['store_id'] = $storeid;
        $where['status'] = array('in', array(0, 1));
        $where['attribute_state'] = 1;
        $where['_string'] = "goods_num - less_num <= 0";
        //商品列表
        $goods = M('retail_goods')
            ->order('status asc')
            ->where($where)
            ->select();
        $goods_class = M('retail_goods_class')->where(array('merchantid' => $merchantid, '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]['index'] = $key + 1;
            $goods[$key]['goods_code'] = $goods[$key]['goods_code'] ? $goods[$key]['goods_code'] : '';
        }
        $filename = "门店无规格不足商品明细";
        $headArr = array(
            array('index', '序号'),
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('goods_number', '货号'),
            array('cate_name', '所属分类'),
            array('purchase_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);
    }

    //规格导出
    public function exporttableSkuLess()
    {
        $storeid = session('storeid');
        $map['store_id'] = $storeid;
        $map['status'] = 1;
        $map['state'] = 0;
        $map['_string'] = "goods_num - less_num <= 0";
        $goods = M('retail_sku_goods')
            ->page($_GET['page'], $_GET['limit'])
            ->where($map)
            ->select();
        foreach ($goods as $key => &$val) {
            $goodsInfo = M('retail_goods')->where(array('id' => $val['goods_id']))->field("goods_name,goods_class_id,purchase_price,goods_number")->find();
            $class_name = M('retail_goods_class')->where(array('id' => $goodsInfo['goods_class_id']))->getField("class_name");
            $val['index'] = $key + 1;
            $val['goods_number'] = $goodsInfo['goods_number'];
            $val['goods_code'] = $val['sku_code'];
            $val['cate_name'] = $class_name;
            $val['status_name'] = '正常';
            $val['less_num'] = round($val['less_num']);
            $str = '';
            $sku_list = explode(",", $val['sku_id_list']);
            if ($sku_list) {
                foreach ($sku_list as $k => &$v) {
                    $str .= ',' . M('retail_sku_list')->where(array('id' => $v))->getField("name");
                }
            }
            $val['goods_name'] = $goodsInfo['goods_name'] . $str;
        }
        $filename = "门店规格不足商品明细";
        $headArr = array(
            array('index', '序号'),
            array('goods_name', '商品名称'),
            array('goods_code', '条码'),
            array('goods_number', '货号'),
            array('cate_name', '所属分类'),
            array('purchase_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-11-22   会员信息导出（会员列表接口更新后可删除）
     */
    public function exportVipInfo()
    {
        $merchant_id = session("merchant_userinfo")['id'];
        if (!$merchant_id) {
            $this->error("无权访问");
        }
        $data = I('get.');
        $keyword = $data['keyword'];
        $status = $data['status'];
        $storeid = $data['storeid'];
        $section = json_decode(htmlspecialchars_decode($data['section']), true);
        $add_time = $section['time'];
        //查询条件
        if (!empty($keyword)) {
            $where['nickname|mobile_phone|card_key'] = array('like', "%" . $keyword . "%");
        }
        if (!empty($storeid)) {
            $where['storeid'] = $storeid;
        }
        switch ($status) {
            case 0:
                $where['status'] = array('neq', 2);
                break;
            case 1:
                $where['status'] = 1;
                break;
            case 3:
                $where['status'] = 3;
                break;
            case 4:
                $where['status'] = 4;
                break;
            default:
                $where['status'] = array('neq', 2);
                break;
        }
        if (!empty($section['integral'])) {
            //积分区间
            $str = strpos($section['integral'], "-");
            if ($str) {
                $integral = explode('-', $section['integral']);
                $where['integral'] = array('between', array($integral[0], $integral[1]));
            } else {
                $where['integral'] = array('egt', $section['integral']);
            }
        }
        if (!empty($section['balance'])) {
            //余额区间
            $str = strpos($section['balance'], '-');
            if ($str) {
                $balance = explode('-', $section['balance']);
                $where['balance'] = array('between', array($balance[0], $balance[1]));
            } else {
                $where['balance'] = array('egt', $section['balance']);
            }
        }
        if (!empty($section['time'])) {
            switch ($section['type']) {
                case 1:
                    //激活时间
                    $start_time = strtotime($add_time[0]);
                    $end_time = strtotime($add_time[1]);
                    $where['addtime'] = array('between', array($start_time, $end_time));
                    break;
                case 2:
                    //生日时间
                    $where['birthday'] = array('between', array($add_time[0], $add_time[1]));
                    break;
                case 3:
                    //沉睡时间
                    $start_time = strtotime($add_time[0]);
                    $end_time = strtotime($add_time[1]);
                    $map = array(
                        'merchant_id' => $merchant_id,
                        'vid' => array('gt', 0),
                        'add_time' => array('between', array($start_time, $end_time))
                    );
                    if (!empty($storeid)) {
                        $map['store_id'] = $storeid;
                    }
                    $vidArr = M('retail_bill_order')->where($map)->group('vid')->getField("vid", true);
                    if (!empty($vidArr)) {
                        $where['id'] = array('not in', $vidArr);
                    }
                    break;
            }
        }
        if ($section['type'] == 4 && !empty($section['purchase'])) {
            //复购次数
            $tap = array(
                'merchant_id' => $merchant_id,
                'vid' => array('gt', 0)
            );
            if (!empty($section['time'])) {
                $start_time = strtotime($add_time[0]);
                $end_time = strtotime($add_time[1]);
                $tap['add_time'] = array('between', array($start_time, $end_time));
            }
            if (!empty($storeid)) {
                $tap['store_id'] = $storeid;
            }
            $vidArr = M('retail_bill_order')->where($tap)->group('vid')->having('count(id)>' . $section['purchase'])->getField("vid", true);
            if (!empty($vidArr)) {
                $where['id'] = array('in', $vidArr);
            } else {
                $where['id'] = 0;
            }
        }
        $where['plus_type'] = 3;
        $where['merchantid'] = $merchant_id;
        $vip_info = M('retail_vip')
            ->where($where)
            ->order('id desc')
            ->select();
        foreach ($vip_info as $key => &$val) {
            $val['index'] = $key + 1;
            if ($val['storeid']) {
                $val['storetag'] = M('retail_store')->where(array('id' => $val['storeid']))->getField('storetag');
            }
            $val['addtime'] = date('Y-m-d H:i:s', $val['addtime']);
            if (empty($val['birthday'])) {
                $val['birthday'] = '未设置';
            }
            $one = M('retail_vip_integral')
                ->where(array('min_integral' => array('ELT', $val['grade_integral']), 'merchantid' => $merchant_id))
                ->order('min_integral desc')
                ->find();
            $val['grade'] = $one ? $one['cardname'] : '默认会员';
            $val['discount'] = $one ? $one['discount'] * 100 . '%' : '不打折';
            $val['name'] = $val['name'] ? $val['name'] : "未设置";
            $val['balance'] = $val['balance'] ? $val['balance'] : "0.00";
            $val['nickname'] = $val['nickname'] ? $val['nickname'] : "未设置";
            $val['name'] = $val['name'] ? $val['name'] : "未设置";
            $val['name'] = $val['name'] . '/' . $val['nickname'];
            if (empty($val['card_key'])) {
                $val['card_key'] = '暂无';
            }
            switch ($val['status']) {
                case 1:
                    $val['statusname'] = "正常会员";
                    break;
                case 3:
                    $val['statusname'] = "冻结会员";
                    break;
                case 4:
                    $val['statusname'] = "挂失会员";
                    break;
            }
        }
        $filename = "会员信息统计";
        $headArr = array(
            array('index', '序号'),
            array('name', '姓名/昵称'),
            array('mobile_phone', '手机号'),
            array('card_key', '实体卡'),
            array('birthday', '生日'),
            array('grade', '会员卡类型'),
            array('discount', '折扣'),
            array('balance', '余额'),
            array('integral', '积分'),
            array('storetag', '激活门店'),
            array('addtime', '激活时间'),
            array('statusname', '会员状态'),
        );
        $str = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $vip_info, $str);
    }

    /**
     * 商户-导出销售统计
     */
    public function exportStoreSaleData()
    {
        $Statistics = new Statistics();
        $param['merchant_id'] = $this->merchant_id;
        $param['date'] = I('get.date') ? explode(',', I('get.date')) : '';
        $param['type'] = 1;
        $result = $Statistics->getStoreSaleDataList($param);
        if ($result['status'] != 1) {
            echo json_encode($result);
            exit;
        }
        $filename = "所有门店销售汇总";
        $common = date('Y-m-d H:i:s', $result['data']['search_date'][0]) . '至' . date('Y-m-d H:i:s', $result['data']['search_date'][1]) . '，导出时间:' . date('Y-m-d H:i:s');
        $this->getStoreSaleDataExcel($filename, $result['data']['store_data'], $result['data']['recharge'], $result['data']['other_pay'], $common);
    }

    private function getStoreSaleDataExcel($filename, $expTableData, $recharge, $other_pay, $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);

        $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', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ');

        //表头
        $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', '退款笔数');

        $key_index = 23;
        $other_len = count($other_pay) * 4;
        if (!empty($other_pay)) {
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '2:' . $cellName[$key_index + $other_len - 1] . '2'); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '2', '其他支付');
            foreach ($other_pay as $k => $o) {
                $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '3:' . $cellName[$key_index + 3] . '3'); //合并单元格
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '3', $o['name']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '4', '销售金额');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '4', '销售笔数');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . '4', '退款金额');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . '4', '退款笔数');
                $key_index += 4;
            }
        }

        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '2:' . $cellName[$key_index + 22] . '2'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '2', '会员充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '3:' . $cellName[$key_index] . '4'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '3', '类型');
        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 1] . '3:' . $cellName[$key_index + 3] . '3'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '3', '店内');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '4', '微信');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . '4', '支付宝');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . '4', '现金');

        $key_index += 3;
        $arr = array(
            '门店后台赠送',
            '门店小助手赠送',
            '门店后台扣除',
            '门店小助手扣除',
            '收银员扣除',
            '收银员小助手扣除',
            '门店会员余额退款',
            '收银员会员余额退款',
            '订单退款',
            '小程序',
            '公众号',
            '商户后台充值',
            '商户后台赠送',
            '商户小助手赠送',
            '商户后台扣除',
            '商户小助手扣除',
            '实际充值金额',
            '总赠送金额',
            '总扣除金额',
            '商户退款',
        );
        foreach ($arr as $nIndex => $name) {
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + $nIndex + 1] . '3:' . $cellName[$key_index + $nIndex + 1] . '4'); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + $nIndex + 1] . '3', $name);
        }
        //标题部分结束
        $all_key_index = $key_index + 9;
        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['store_name']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 5), '小计');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 5), $val['total']['money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 5), $val['total']['sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 5), $val['total']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 5), $val['total']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 5), $val['total']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 5), $val['wechat']['sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 5), $val['wechat']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 5), $val['wechat']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 5), $val['wechat']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 5), $val['vip']['sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 5), $val['vip']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 5), $val['vip']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 5), $val['vip']['refund_num']);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 5), $val['alipay']['sell_money']); //支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 5), $val['alipay']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 5), $val['alipay']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 5), $val['alipay']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 5), $val['cash']['sell_money']); //现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 5), $val['cash']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 5), $val['cash']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 5), $val['cash']['refund_num']);
            $objPHPExcel->getActiveSheet(0)->mergeCells('P' . ($key * 3 + 7) . ':' . $cellName[23 + $other_len - 1] . ($key * 3 + 7)); //合并单元格
            $key_index = 23;
            if (!empty($other_pay)) {
                foreach ($val['other'] as $k => $o) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . ($key * 3 + 5), $o['sell_money']); //其他
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 5), $o['sell_num']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 5), $o['refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 5), $o['refund_num']);
                    $key_index += 4;
                }
            }

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 6), '店内');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 6), $val['total']['line_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 6), $val['total']['line_sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 6), $val['total']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 6), $val['total']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 6), $val['total']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 6), $val['wechat']['line_sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 6), $val['wechat']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 6), $val['wechat']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 6), $val['wechat']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 6), $val['vip']['line_sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 6), $val['vip']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 6), $val['vip']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 6), $val['vip']['line_refund_num']);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 6), $val['alipay']['line_sell_money']); //支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 6), $val['alipay']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 6), $val['alipay']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 6), $val['alipay']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 6), $val['cash']['line_sell_money']); //现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 6), $val['cash']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 6), $val['cash']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 6), $val['cash']['line_refund_num']);

            $key_index = 23;
            if (!empty($other_pay)) {
                foreach ($val['other'] as $k => $o) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . ($key * 3 + 6), $o['line_sell_money']); //其他
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 6), $o['line_refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 6), $o['line_refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 6), $o['line_refund_num']);
                    $key_index += 4;
                }
            }

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 7), '商城');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 7), $val['total']['online_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 7), $val['total']['online_sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 7), $val['total']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 7), $val['total']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 7), $val['total']['online_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 7), $val['wechat']['online_sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 7), $val['wechat']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 7), $val['wechat']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 7), $val['wechat']['online_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 7), $val['vip']['online_sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 7), $val['vip']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 7), $val['vip']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 7), $val['vip']['online_refund_num']);

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

            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 1] . ($key * 3 + 5) . ':' . $cellName[$key_index + 1] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 2] . ($key * 3 + 5) . ':' . $cellName[$key_index + 2] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 3] . ($key * 3 + 5) . ':' . $cellName[$key_index + 3] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 5), $val['line_recharge']['wechat_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 5), $val['line_recharge']['alipay_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 5), $val['line_recharge']['cash_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 7), $val['line_recharge']['wechat_give']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 7), $val['line_recharge']['alipay_give']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 7), $val['line_recharge']['cash_give']);

            $z = 1;
            $key_index += 3;
            foreach ($val['update_balance'] as $update_balance) {
                $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + $z] . ($key * 3 + 5) . ':' . $cellName[$key_index + $z] . ($key * 3 + 7)); //合并单元格
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + $z] . ($key * 3 + 5), $update_balance);
                $z++;
            }

            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':' . $cellName[$key_index + $z] . ($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) . ':' . $cellName[$key_index + $z] . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFF9FAFC');
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':' . $cellName[$key_index + $z] . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFFFFFFF');
            }
        }
        $storeNum = count($expTableData);

        $zz = 1;
        foreach ($recharge as $r) {
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$all_key_index + $zz] . '5:' . $cellName[$all_key_index + $zz] . ($storeNum * 3 + 4)); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$all_key_index + $zz] . '5', $r);
            $zz++;
        }

        $all_key_index = $all_key_index + $zz;

        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array(                               //allborders  表示全部线框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    'color' => array('argb' => 'FFDDDDDD'),          //设置border颜色
                ),
            ),
        );
        $count = ($storeNum * 3) + 5;
        foreach ($cellName as $key => $value) {
            if ($key >= $all_key_index) {
                break;
            }
            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()
    {
        $Statistics = new Statistics();
        $param['merchant_id'] = $this->merchant_id;
        $param['store_id'] = $this->store_id;
        $param['date'] = I('get.date') ? explode(',', I('get.date')) : '';
        $param['type'] = 2;
        $result = $Statistics->getStoreSaleDataList($param);
        if ($result['status'] != 1) {
            echo json_encode($result);
            exit;
        }
        $filename = "销售汇总";
        $common = '';
        if (!empty($date)) {
            $common .= $date[0] . '至' . $date[1];
        }
        $common .= '导出时间:' . date('Y-m-d H:i:s');
        $this->getOneStoreSaleDataExcel($filename, $result['data']['store_data'], $result['data']['other_pay'], $common);
    }

    private function getOneStoreSaleDataExcel($filename, $expTableData, $other_pay, $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);

        $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', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ', 'EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG', 'EH', 'EI', 'EJ', 'EK', 'EL', 'EM', 'EN', 'EO', 'EP', 'EQ', 'ER', 'ES', 'ET', 'EU', 'EV', 'EW', 'EX', 'EY', 'EZ');
        //表头
        $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', '退款笔数');

        $key_index = 23;
        $other_len = count($other_pay) * 4;
        if (!empty($other_pay)) {
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '2:' . $cellName[$key_index + $other_len - 1] . '2'); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '2', '其他支付');
            foreach ($other_pay as $k => $o) {
                $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '3:' . $cellName[$key_index + 3] . '3'); //合并单元格
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '3', $o['name']);
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '4', '销售金额');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '4', '销售笔数');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . '4', '退款金额');
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . '4', '退款笔数');
                $key_index += 4;
            }
        }

        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '2:' . $cellName[$key_index + 15] . '2'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '2', '会员充值');
        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index] . '3:' . $cellName[$key_index] . '4'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . '3', '类型');
        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 1] . '3:' . $cellName[$key_index + 3] . '3'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '3', '店内');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . '4', '微信');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . '4', '支付宝');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . '4', '现金');

        $key_index += 3;
        $arr = array(
            '门店会员余额退款',
            '收银员会员余额退款',
            '门店后台赠送',
            '门店小助手赠送',
            '门店后台扣除',
            '门店小助手扣除',
            '收银员收银端扣除',
            '收银员小助手扣除',
            '商品退款',
            '实际充值金额',
            '赠送金额',
            '总扣除',
        );
        foreach ($arr as $nIndex => $name) {
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + $nIndex + 1] . '3:' . $cellName[$key_index + $nIndex + 1] . '4'); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + $nIndex + 1] . '3', $name);
        }
        //标题部分结束
        $all_key_index = $key_index + 12;
        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['date']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 5), '小计');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 5), $val['total']['money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 5), $val['total']['sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 5), $val['total']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 5), $val['total']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 5), $val['total']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 5), $val['wechat']['sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 5), $val['wechat']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 5), $val['wechat']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 5), $val['wechat']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 5), $val['vip']['sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 5), $val['vip']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 5), $val['vip']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 5), $val['vip']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 5), $val['alipay']['sell_money']); //支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 5), $val['alipay']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 5), $val['alipay']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 5), $val['alipay']['refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 5), $val['cash']['sell_money']); //现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 5), $val['cash']['sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 5), $val['cash']['refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 5), $val['cash']['refund_num']);

            $key_index = 23;
            if (!empty($other_pay)) {
                foreach ($val['other'] as $k => $o) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . ($key * 3 + 5), $o['sell_money']); //其他
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 5), $o['sell_num']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 5), $o['refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 5), $o['refund_num']);
                    $key_index += 4;
                }
            }

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 6), '店内');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 6), $val['total']['line_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 6), $val['total']['line_sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 6), $val['total']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 6), $val['total']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 6), $val['total']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 6), $val['wechat']['line_sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 6), $val['wechat']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 6), $val['wechat']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 6), $val['wechat']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 6), $val['vip']['line_sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 6), $val['vip']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 6), $val['vip']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 6), $val['vip']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . ($key * 3 + 6), $val['alipay']['line_sell_money']); //支付宝
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . ($key * 3 + 6), $val['alipay']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . ($key * 3 + 6), $val['alipay']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . ($key * 3 + 6), $val['alipay']['line_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . ($key * 3 + 6), $val['cash']['line_sell_money']); //现金
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . ($key * 3 + 6), $val['cash']['line_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . ($key * 3 + 6), $val['cash']['line_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . ($key * 3 + 6), $val['cash']['line_refund_num']);

            $objPHPExcel->getActiveSheet(0)->mergeCells('P' . ($key * 3 + 7) . ':' . $cellName[23 + $other_len - 1] . ($key * 3 + 7)); //合并单元格

            $key_index = 23;
            if (!empty($other_pay)) {
                foreach ($val['other'] as $k => $o) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index] . ($key * 3 + 6), $o['line_sell_money']); //其他
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 6), $o['line_refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 6), $o['line_refund_money']);
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 6), $o['line_refund_num']);
                    $key_index += 4;
                }
            }

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . ($key * 3 + 7), '商城');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . ($key * 3 + 7), $val['total']['online_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . ($key * 3 + 7), $val['total']['online_sell_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . ($key * 3 + 7), $val['total']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . ($key * 3 + 7), $val['total']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . ($key * 3 + 7), $val['total']['online_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . ($key * 3 + 7), $val['wechat']['online_sell_money']); //微信
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . ($key * 3 + 7), $val['wechat']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . ($key * 3 + 7), $val['wechat']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . ($key * 3 + 7), $val['wechat']['online_refund_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . ($key * 3 + 7), $val['vip']['online_sell_money']); //会员卡
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . ($key * 3 + 7), $val['vip']['online_sell_num']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . ($key * 3 + 7), $val['vip']['online_refund_money']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . ($key * 3 + 7), $val['vip']['online_refund_num']);

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

            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 1] . ($key * 3 + 5) . ':' . $cellName[$key_index + 1] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 2] . ($key * 3 + 5) . ':' . $cellName[$key_index + 2] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + 3] . ($key * 3 + 5) . ':' . $cellName[$key_index + 3] . ($key * 3 + 6)); //合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 5), $val['line_recharge']['wechat_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 5), $val['line_recharge']['alipay_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 5), $val['line_recharge']['cash_recharge']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 1] . ($key * 3 + 7), $val['line_recharge']['wechat_give']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 2] . ($key * 3 + 7), $val['line_recharge']['alipay_give']);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + 3] . ($key * 3 + 7), $val['line_recharge']['cash_give']);


            $z = 1;
            $key_index += 3;
            foreach ($val['update_balance'] as $update_balance) {
                $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$key_index + $z] . ($key * 3 + 5) . ':' . $cellName[$key_index + $z] . ($key * 3 + 7)); //合并单元格
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$key_index + $z] . ($key * 3 + 5), $update_balance);
                $z++;
            }

            $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':' . $cellName[$key_index + $z - 1] . ($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) . ':' . $cellName[$key_index + $z - 1] . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFF9FAFC');
            } else {
                $objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($key * 3 + 5) . ':' . $cellName[$key_index + $z - 1] . ($key * 3 + 7))->getFill()->getStartColor()->setARGB('FFFFFFFF');
            }
        }
        $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 ($cellName as $key => $value) {
            if ($key > $all_key_index) {
                break;
            }
            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();
    }

    public function exportTable()
    {
        $table_name = I('get.table_name');
        if (empty($table_name)) {
            exit;
        }
        $Model = new \Think\Model();
        $exist = M()->query("show tables like '" . $table_name . "'");
        if (!$exist) {
            exit;
        }
        $table = $Model->query("select * from information_schema.columns where table_name='" . $table_name . "' and table_schema='csls'");
        $tempArr = array();
        foreach ($table as $key => $value) {
            $temp = array(
                'index' => ($key + 1),
                'column_name' => $value['column_name'],                         //字段名
                'column_key' => $value['column_key'] == 'PRI' ? '是' : '',      //是否主键
                'is_nullable' => $value['is_nullable'] == 'YES' ? '是' : '否',  //是否为空
                'column_type' => $value['column_type'],                         //字段类型
                'column_comment' => $value['column_comment'],                   //注释
            );
            $tempArr[] = $temp;
        }
        $filename = $table_name . "表";
        import("Vendor.PHPExcel.PHPExcel");
        import("Vendor.PHPExcel.Writer.Excel5");
        import("Vendor.PHPExcel.IOFactory.php");
        $headArr = array(
            array('index', '序号'),
            array('column_name', '字段名'),
            array('column_key', '主键'),
            array('is_nullable', '是否为空'),
            array('column_type', '类型'),
            array('column_comment', '注释'),
        );
        $title = $filename . ',导出日期:' . date('Y-m-d H:i:s');
        $this->getExcel($filename, $headArr, $tempArr, $title);
    }

    /**
     * 文件导出封装
     * 2020/8/14
     * @param string $directory_name
     * @param string $name
     * @return array
     */
    public function fileZipAddToDel($directory_name, $name)
    {
        $zip = new \ZipArchive();
        if (!$directory_name) {
            return ['status' => 0, 'msg' => '缺少参数'];
        }
        if (is_dir($directory_name)) {
            $dir = ltrim($directory_name, './');
            $arr = explode('/', $dir);
            unset($arr[count($arr) - 1]);
            $zipName = implode('/', $arr);
            $zip_url = './' . $zipName . '/' . $name . date('Y-m-d-H-i-s') . '.zip';
            if ($zip->open($zip_url, \ZipArchive::CREATE) === TRUE) {

                $this->addFileToZip($dir, $zip, $name); //调用方法，对要打包的根目录进行操作，并将ZipArchive的对象传递给方法
                $zip->close(); //关闭处理的zip文件

                if (!file_exists($zip_url)) {  //判断文件是否存在
                    return ['status' => 0, 'msg' => '文件不存在'];
                }
                $file = fopen($zip_url, "r");
                Header("Content-type:application/octet-stream");
                header("Accept-Ranges: bytes");
                header("Accept-Length: " . filesize($zip_url));
                header("Content-Disposition: attachment; filename={$name}" . date('Y-m-d_H:i:s') . ".zip");
                echo fread($file, filesize($zip_url));
                fclose($file);

                $this->delDirFile($directory_name); //删除文件夹下的所有文件
                rmdir($directory_name); //删除当前文件夹
                unlink($zip_url); //删除压缩包

                return ['status' => 1, 'msg' => '文件导出成功'];
            }
        } else {
            return ['status' => 0, 'msg' => '文件不存在'];
        }
    }
}