phpstock/app/admin/controller/Report.php

<?php

namespace app\admin\controller;

use think\facade\Db;
use think\facade\Validate;

//报表处理类
class Report extends Base
{

    //产品价格和产品导出
    public function exportGood()
    {

        $param = $this->request->only(['date', 'platform_id', 'status'], 'post', 'trim');

        $val = Validate::rule([
            'date|筛选时间' => 'require|date',
            'platform_id|筛选平台ID' => 'require|number|gt:0',
            'status|状态' => 'require|number|in:5,6,8',
        ]);

        if (!$val->check($param)) return error_show(1004, $val->getError());

        $where_ladder = [['gl.is_del', '=', 0]];
        $where_good = [['g.is_del', '=', 0]];
        if (!empty($param['date'])) {
            $where_ladder[] = ['gp.online_time', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']];
            $where_good[] = ['g.addtime', 'between', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']];
        }
        if (!empty($param['platform_id'])) {
            $where_ladder[] = ['gp.platform_code', '=', $param['platform_id']];
            $where_good[] = ['gp.platform_code', '=', $param['platform_id']];
        }
        if (!empty($param['status'])) {
            $where_ladder[] = ['gp.exam_status', '=', $param['status']];
            $where_good[] = ['gp.exam_status', '=', $param['status']];
        }

        $data = [];

        //产品价格
        $rs_ladder = Db::name('good_ladder')
            ->alias('gl')
            ->field('g.good_name 商品名称,gl.market_price 市场价,g.tax 税率,gl.min_num 起订量,gl.sale_price 售价,gl.skuCode 商品编码,gl.cost_fee 工艺费,gl.market_platform 对比平台')
            ->where($where_ladder)
            ->leftJoin('good_platform gp', 'gp.skuCode=gl.skuCode AND gp.is_del=0')
            ->leftJoin('good g', 'g.spuCode=gp.spuCode AND g.is_del=0')
            ->order('gl.id')
            ->select()
            ->toArray();

        if (!empty($rs_ladder)) {
            $data[] = [
                'head' => array_keys($rs_ladder[0]),
                'list' => $rs_ladder,
                'filename' => '产品价格' . date('YmdHis'),
            ];
        }

        //产品
        $rs_temp_good = Db::name('good')
            ->alias('g')
            ->field('"" 一级分类,"" 二级分类, g.cat_id 三级分类,g.good_name 商品名称,g.good_type 商品类型,g.brand_id 商品品牌,\'\' 型号,g.origin_place 产地,g.good_unit 计量单位,g.weight 重量g,\'\' 响应时间,g.lead_time 供货周期,g.good_size 商品尺寸,g.packing_size 装箱尺寸,g.packing_way 包装方式,g.packing_spec 装箱规格,g.packing_list 包装清单,g.delivery_place 发货地,g.delivery_day 物流时间,gp.skuCode 商品编码,g.spuCode')
            ->where($where_good)
            ->leftJoin('good_platform gp', 'gp.spuCode=g.spuCode AND gp.is_del=0')
            ->order('g.id')
            ->select()
            ->toArray();

        $all_good_type = [0 => '非定制商品', 1 => '定制商品'];
        $all_brand = Db::name('brand')->whereIn('id', array_column($rs_temp_good, '商品品牌'))->where('is_del', 0)->column('brand_name', 'id');
        $all_cat = Db::name('cat')
            ->alias('c3')
            ->whereIn('c3.id', array_column($rs_temp_good, '三级分类'))
            ->where('c3.is_del', 0)
            ->leftJoin('cat c2', 'c2.id=c3.pid AND c2.is_del=0')
            ->leftJoin('cat c1', 'c1.id=c2.pid AND c1.is_del=0')
            ->column('c3.cat_name cat_name_3,c2.cat_name cat_name_2,c1.cat_name cat_name_1', 'c3.id');

        $all_unit = Db::name('unit')->whereIn('id', array_column($rs_temp_good, '计量单位'))->where('is_del', 0)->column('unit', 'id');

        foreach ($rs_temp_good as &$value) {
            $value['商品品牌'] = isset($all_brand[$value['商品品牌']]) ? $all_brand[$value['商品品牌']] : '';
            $value['商品类型'] = isset($all_good_type[$value['商品类型']]) ? $all_good_type[$value['商品类型']] : '';
            $value['一级分类'] = isset($all_cat[$value['三级分类']]['cat_name_1']) ? $all_cat[$value['三级分类']]['cat_name_1'] : '';
            $value['二级分类'] = isset($all_cat[$value['三级分类']]['cat_name_2']) ? $all_cat[$value['三级分类']]['cat_name_2'] : '';
            $value['三级分类'] = isset($all_cat[$value['三级分类']]['cat_name_3']) ? $all_cat[$value['三级分类']]['cat_name_3'] : '';

            $temp = explode(',', $value['产地']);
            $value['产地'] = GetAddr(json_encode(['provice_code' => isset($temp[0]) ? $temp[0] : '', 'city_code' => isset($temp[1]) ? $temp[1] : '', 'area_code' => isset($temp[2]) ? $temp[2] : '']));
            $value['计量单位'] = isset($all_unit[$value['计量单位']]) ? $all_unit[$value['计量单位']] : '';
            $temp_2 = explode(',', $value['发货地']);
            $value['发货地'] = GetAddr(json_encode(['provice_code' => isset($temp_2[0]) ? $temp_2[0] : '', 'city_code' => isset($temp_2[1]) ? $temp_2[1] : '', 'area_code' => isset($temp_2[2]) ? $temp_2[2] : '']));

            $value['型号'] = Db::name('good_spec')
                ->alias('gp')
                ->field('')
                ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
                ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
                ->where([
                    'gp.spuCode' => $value['spuCode'],
                    'gp.is_del' => 0,
                    's.spec_name' => '型号',
                ])
                ->value('sv.spec_value', '');

            $value['响应时间'] = Db::name('good_spec')
                ->alias('gp')
                ->field('')
                ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
                ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
                ->where([
                    'gp.spuCode' => $value['spuCode'],
                    'gp.is_del' => 0,
                    's.spec_name' => '响应时间',
                ])
                ->value('sv.spec_value', '');

            unset($value['spuCode']);
        }

        if (!empty($rs_temp_good)) {
            $data[] = [
                'head' => array_keys($rs_temp_good[0]),
                'list' => $rs_temp_good,
                'filename' => '产品' . date('YmdHis'),
            ];
        }

        if (empty($data)) {
            $data[] = [
                'head' => ['没有可供导出的数据'],
                'list' => [],
                'filename' => date('YmdHis'),
            ];

        }

        excelSaveBatch($data);

    }

    //【一、采购日报表】1.咨询单总数
    public function zixunTotal()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['co.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['co.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $count = Db::name('consult_order')
            ->alias('co')
            ->field('DATE_FORMAT(co.addtime,"%Y-%m-%d") addtime,cii.name,count(ci.id) total')
            ->leftJoin('consult_info ci', 'ci.zxNo=co.zxNo AND ci.is_del=0 AND ci.bargain_code!="" ')
            ->leftJoin('company_item cii', 'cii.id=co.depart AND cii.is_del=0')
            ->where($where)
            ->group('addtime,co.depart')
            ->count();

        $list = Db::name('consult_order')
            ->alias('co')
            ->field('DATE_FORMAT(co.addtime,"%Y-%m-%d") addtime,cii.name,count(ci.id) total')
            ->leftJoin('consult_info ci', 'ci.zxNo=co.zxNo AND ci.is_del=0 AND ci.bargain_code!="" ')
            ->leftJoin('company_item cii', 'cii.id=co.depart AND cii.is_del=0')
            ->where($where)
            ->group('addtime,co.depart')
            ->order('addtime')
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【一、采购日报表】2.采购订单总金额
    public function purcheaseOrderSum()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['po.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['po.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];


        //统计条数的时候，不能按照status分组，否则下方列表不准
        $count = Db::name('purchease_order')
            ->alias('po')
            ->leftJoin('depart_user du', 'du.uid=po.cgder_id AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where($where)
            ->order('addtime,itemid')
            ->field('DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,SUM(po.total_fee) total_fee,COUNT(po.id) total_num,du.itemid,ci.name,"" wait_total_fee,"" wait_total_num')
            ->group('addtime,du.itemid')
            ->count();

        $list = Db::name('purchease_order')
            ->alias('po')
            ->leftJoin('depart_user du', 'du.uid=po.cgder_id AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where($where)
            ->order('addtime,itemid')
            ->field('DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,SUM(po.total_fee) total_fee,COUNT(po.id) total_num,po.status,du.itemid,ci.name,"" wait_total_fee,"" wait_total_num')
            ->page($param['page'], $param['size'])
            ->group('addtime,du.itemid,po.status')
            ->cursor();

        $data = [];
        foreach ($list as $value) {

            if (!isset($data[$value['addtime']][$value['itemid']])) {
                $data[$value['addtime']][$value['itemid']] = [
                    'addtime' => $value['addtime'],
                    'itemid' => $value['itemid'],
                    'name' => $value['name'],
                    'total_fee' => 0,
                    'total_num' => 0,
                    'wait_total_fee' => 0,
                    'wait_total_num' => 0,
                ];
            }

            if ($value['status'] == 0) {
                $data[$value['addtime']][$value['itemid']]['wait_total_fee'] += $value['total_fee'];
                $data[$value['addtime']][$value['itemid']]['wait_total_num'] += $value['total_num'];
            } else {
                $data[$value['addtime']][$value['itemid']]['total_fee'] += $value['total_fee'];
                $data[$value['addtime']][$value['itemid']]['total_num'] += $value['total_num'];
            }

        }

        //去除下标
        $da = [];
        foreach ($data as $v) {
            foreach ($v as $vvv) {
                $da[] = $vvv;
            }
        }

        return app_show(0, '请求成功', ['list' => $da, 'count' => $count]);

    }

    //【一、采购日报表】3.采购员回复咨询单数
    public function consultBidsSum()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['cb.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['cb.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $count = Db::name('consult_bids')
            ->alias('cb')
            ->field('DATE_FORMAT(cb.addtime,"%Y-%m-%d") addtime,du.itemid,cb.createrid,SUM(c.num) num,COUNT(cb.id) total,du.nickname,ci.name')
            ->leftJoin('consult_info c', 'c.zxNo=cb.zxNo AND c.is_del=0')
            ->leftJoin('depart_user du', 'du.uid=cb.createrid AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where($where)
            ->group('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
            ->order('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
            ->count();

        $list = Db::name('consult_bids')
            ->alias('cb')
            ->field('DATE_FORMAT(cb.addtime,"%Y-%m-%d") addtime,du.itemid,cb.createrid,SUM(c.num) num,COUNT(cb.id) total,du.nickname,ci.name')
            ->leftJoin('consult_info c', 'c.zxNo=cb.zxNo AND c.is_del=0')
            ->leftJoin('depart_user du', 'du.uid=cb.createrid AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where($where)
            ->group('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
            ->order('addtime,du.itemid,ci.name ,cb.createrid,du.nickname')
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        $list[] = ['addtime' => '汇总', 'itemid' => 0, 'createrid' => 0, 'num' => array_sum(array_column($list, 'num')), 'total' => array_sum(array_column($list, 'total')), 'nickname' => '', 'name' => ''];

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【一、采购日报表】4.采购员订单金额
    public function purcheaseOrderSumByUser()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        //先组织子查询
        $children_query = Db::name('purchease_order')
            ->field('DATE_FORMAT(addtime, "%Y-%m-%d") addtime,cgder_id,cgder,total_fee,status,good_num')
            ->where($where)
            ->buildSql();

        $count = Db::table($children_query)
            ->alias('a')
            ->group('addtime,cgder_id,cgder')
            ->count();

        $rs = Db::table($children_query)
            ->alias('a')
            ->field('addtime,cgder_id,cgder,SUM(total_fee) total_fee,SUM(good_num) good_num,status,0 wait_total_fee,0 wait_good_num')
            ->group('addtime,cgder_id,cgder,status')
            ->order('addtime,cgder_id')
            ->page($param['page'], $param['size'])
            ->cursor();

        $data = [];
        foreach ($rs as $value) {

            if (!isset($data[$value['addtime']][$value['cgder_id']])) {
                $data[$value['addtime']][$value['cgder_id']] = [
                    'addtime' => $value['addtime'],
                    'cgder' => $value['cgder'],
                    'total_fee' => 0.00,
                    'good_num' => 0,
                    'wait_total_fee' => 0.00,
                    'wait_good_num' => 0,
                ];
            }
            if ($value['status'] == 0) {
                $data[$value['addtime']][$value['cgder_id']]['wait_total_fee'] += $value['total_fee'];
                $data[$value['addtime']][$value['cgder_id']]['wait_good_num'] += $value['good_num'];
            } else {
                $data[$value['addtime']][$value['cgder_id']]['total_fee'] += $value['total_fee'];
                $data[$value['addtime']][$value['cgder_id']]['good_num'] += $value['good_num'];
            }
        }

        $da = [];
        //去除下标
        foreach ($data as $v) {
            foreach ($v as $vv) {
                $da[] = $vv;
            }
        }

        return app_show(0, '请求成功', ['list' => $da, 'count' => $count]);

    }

    //【二、咨询单报表】1.已采反报价信息_列表
    public function consultInfoBidsSum()
    {
        $param = $this->request->only([
            'token',
            'zxNo' => [],
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['cb.is_del', '=', 0]];

        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['cb.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if (!empty($param['zxNo'])) $where[] = ['cb.zxNo', 'in', $param['zxNo']];


        $count = Db::name('consult_bids')
            ->alias('cb')
            ->where($where)
            ->count('cb.id');

        $data = Db::name('consult_bids')
            ->alias('cb')
            ->field('cb.id,ci.addtime,cb.bidNo,cb.zxNo,cb.addtime cbaddtime,cb.good_name,s.name supplier,cb.total_fee,cb.delivery_day,cb.work_day,cb.expire_day,cb.creater,ci.num,ci.arrival_time,co.salesman,csi.companyName')
            ->leftJoin('consult_info ci', 'ci.infoNo=cb.infoNo AND ci.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
            ->leftJoin('supplier s', 's.code=cb.supplierNo AND s.is_del=0')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        //汇总
        $data[] = [
            'id' => 0,
            'addtime' => '汇总',
            'bidNo' => '',
            'zxNo' => '',
            'cbaddtime' => '',
            'good_name' => '',
            'supplier' => '',
            'total_fee' => array_sum(array_column($data, 'total_fee')),
            'delivery_day' => '',
            'work_day' => '',
            'expire_day' => '',
            'creater' => '',
            'num' => array_sum(array_column($data, 'num')),
            'arrival_time' => '',
            'salesman' => '',
            'companyName' => '',
        ];

        return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);


    }

    //【二、咨询单报表】1.已采反报价信息_导出
    public function consultInfoBidsSumExport()
    {
        $param = $this->request->only([
            'token',
            'zxNo' => [],
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $rs = Db::name('consult_bids')
            ->alias('cb')
            ->where('cb.is_del', 0);

        if ($param['start_date'] != '' && $param['end_date'] != '') $rs->whereBetween('cb.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']);
        if (!empty($param['zxNo'])) $rs->whereIn('cb.zxNo', $param['zxNo']);


        $data = $rs
            ->field('ci.addtime as 咨询时间,cb.bidNo as 采购单反馈单号,cb.zxNo as 咨询订单号,cb.addtime as 回复时间,cb.good_name as 产品名称,s.name as 供应商名称,cb.total_fee 成本合计,cb.delivery_day 物流时间,cb.work_day 产品工期,cb.expire_day 信息有效期,cb.creater 采购员,ci.num 需求数量,ci.arrival_time 要求到货日期,co.salesman 业务人员,csi.companyName 客户名称')
            ->leftJoin('consult_info ci', 'ci.infoNo=cb.infoNo AND ci.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
            ->leftJoin('supplier s', 's.code=cb.supplierNo AND s.is_del=0')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
            ->select()
            ->toArray();

        if (empty($data)) $data[] = '没有可供导出的数据';

        $headerArr = array_keys($data[0]);
        excelSave('咨询单报表-已采反报价信息' . date('YmdHis'), $headerArr, $data);

    }

    //【二、咨询单报表】2.未采反信息_列表
    public function consultInfoBidsSumNot()
    {
        $param = $this->request->only([
            'token',
            'companyName' => '',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['cb.is_del', '=', 0]];

        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['ci.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['companyName'] != '') $where[] = ['csi.companyName', 'like', '%' . $param['companyName'] . '%'];


        $count = Db::name('consult_bids')
            ->alias('cb')
            ->leftJoin('consult_info ci', 'ci.infoNo=cb.infoNo AND ci.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
            ->where($where)
            ->order('co.endtime', 'desc')
            ->count('cb.id');

        $data = Db::name('consult_bids')
            ->alias('cb')
            ->field('cb.zxNo,cb.good_name,ci.num,csi.companyName,ci.addtime,co.endtime')
            ->leftJoin('consult_info ci', 'ci.infoNo=cb.infoNo AND ci.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
            ->where($where)
            ->order('co.endtime', 'desc')
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $data, 'count' => $count]);


    }

    //【二、咨询单报表】2.未采反信息_导出
    public function consultInfoBidsSumNotExport()
    {
        $param = $this->request->only([
            'token',
            'companyName' => '',
            'start_date' => '',
            'end_date' => '',
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $rs = Db::name('consult_bids')
            ->alias('cb')
            ->leftJoin('consult_info ci', 'ci.infoNo=cb.infoNo AND ci.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo AND co.is_del=0')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo AND csi.is_del=0')
            ->where('cb.is_del', 0)
            ->order('co.endtime', 'desc');

        if ($param['start_date'] != '' && $param['end_date'] != '') $rs->whereBetween('ci.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']);
        if ($param['companyName'] != '') $rs->whereLike('csi.companyName', '%' . $param['companyName'] . '%');

        $data = $rs
            ->field('cb.zxNo 咨询订单号,cb.good_name 产品名称,ci.num 需求数量,csi.companyName 客户名称,ci.addtime 咨询时间,co.endtime 截止时间')
            ->select()
            ->toArray();

        if (empty($data)) $data[] = '没有可供导出的数据';

        $headerArr = array_keys($data[0]);
        excelSave('咨询单报表-未采反信息' . date('YmdHis'), $headerArr, $data);


    }

    //【三、订单明细报表】列表
    public function orderListDetailed()
    {
        $param = $this->request->only([
            'token',
            'addtime_start' => '',
            'addtime_end' => '',
            'status' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'addtime_start' => 'date|elt:addtime_end',
            'addtime_end' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [];

        if ($param['addtime_start'] != '' && $param['addtime_end'] != '') $where[] = ['po.addtime', 'between', [$param['addtime_start'] . ' 00:00:00', $param['addtime_end'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['po.status', '=', $param['status']];

        $all_status = ['待与供应商确认', '待入库', '部分入库', '入库完成', '已取消订单'];
        $all_send_status = [1 => '未发货', 2 => '部分发货', 3 => '已发货'];


        $count = Db::name('purchease_order')
            ->alias('po')
            ->leftJoin('business b', 'b.companyNo=po.companyNo AND b.is_del=0')
            ->where($where)
            ->count('po.id');

        $data = Db::name('purchease_order')
            ->alias('po')
            ->field('po.cgdNo,po.addtime,po.supplierNo,po.supplier_name,po.good_name,po.good_num,po.nake_fee,po.total_fee,po.status,po.send_status,po.wsend_num,"" creater,b.company,po.cgder,"" send_time,on.orderCode,po.order_type,po.bkcode,po.spuCode')
            ->leftJoin('business b', 'b.companyNo=po.companyNo AND b.is_del=0')
            ->leftJoin('order_num on', 'on.cgdNo=po.cgdNo')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->cursor();

        $list = [];
        foreach ($data as $v) {

            //单据状态
            $v['status'] = isset($all_status[$v['status']]) ? $all_status[$v['status']] : '';

            //发货状态
            $v['send_status'] = isset($all_send_status[$v['send_status']]) ? $all_send_status[$v['send_status']] : '';

            //创建人//order_type==1备库单
            if ($v['order_type'] == 1) $v['creater'] = Db::name('purchease')->where(['bk_code' => $v['bkcode'], 'spuCode' => $v['spuCode'], 'is_del' => 0])->value('apply_name', '');
            else $v['creater'] = Db::name('sale')->where(['orderCode' => $v['orderCode'], 'is_del' => 0])->value('apply_name', '');

            //创建时间
            $v['send_time'] = Db::name('order_out')
                ->where(['orderCode' => $v['orderCode']])
                ->order('addtime', 'desc')
                ->value('addtime');

            $list[] = $v;
        }

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);


    }

    //【三、订单明细报表】导出
    public function orderListDetailedExport()
    {
        $param = $this->request->only([
            'token',
            'addtime_start' => '',
            'addtime_end' => '',
            'status' => '',
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'addtime_start' => 'date|elt:addtime_end',
            'addtime_end' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $rs = Db::name('purchease_order')
            ->alias('po')
            ->leftJoin('business b', 'b.companyNo=po.companyNo AND b.is_del=0');

        if ($param['addtime_start'] != '' && $param['addtime_end'] != '') $rs->whereBetween('po.addtime', [$param['addtime_start'] . ' 00:00:00', $param['addtime_end'] . ' 23:59:59']);
        if ($param['status'] != '') $rs->where('po.status', '=', $param['status']);

        $all_status = ['待与供应商确认', '待入库', '部分入库', '入库完成', '已取消订单'];
        $all_send_status = [1 => '未发货', 2 => '部分发货', 3 => '已发货'];

        $data = Db::name('purchease_order')
            ->alias('po')
            ->field('po.cgdNo 采购单编号,po.addtime 创建时间,po.supplierNo 供应商编号,po.supplier_name 供应商名称,po.good_name 产品名称,po.good_num 购买数量,po.nake_fee 裸价,po.total_fee 成本合计,po.status 单据状态,po.send_status 发货状态,po.wsend_num 未发货数量,"" 创建人,b.company 客户名称,po.cgder 采购员,"" 发货时间,on.orderCode,po.order_type,po.bkcode,po.spuCode')
            ->leftJoin('business b', 'b.companyNo=po.companyNo AND b.is_del=0')
            ->leftJoin('order_num on', 'on.cgdNo=po.cgdNo')
            ->cursor();

        $list = [];

        foreach ($data as $v) {

            //单据状态
            $v['单据状态'] = isset($all_status[$v['单据状态']]) ? $all_status[$v['单据状态']] : '';

            //发货状态
            $v['发货状态'] = isset($all_send_status[$v['发货状态']]) ? $all_send_status[$v['发货状态']] : '';

            //创建人//order_type==1备库单
            if ($v['order_type'] == 1) $v['创建人'] = Db::name('purchease')->where(['bk_code' => $v['bkcode'], 'spuCode' => $v['spuCode'], 'is_del' => 0])->value('apply_name', '');
            else $v['创建人'] = Db::name('sale')->where(['orderCode' => $v['orderCode'], 'is_del' => 0])->value('apply_name', '');

            //创建时间
            $v['发货时间'] = Db::name('order_out')
                ->where(['orderCode' => $v['orderCode']])
                ->order('addtime', 'desc')
                ->value('addtime');

            unset($v['orderCode']);
            unset($v['order_type']);
            unset($v['bkcode']);
            unset($v['spuCode']);

            $list[] = $v;
        }

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('订单明细报表' . date('YmdHis'), $headerArr, $list);

    }

    //【四、售后报表】列表
    public function orderReturnList()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $all_status = [2 => '业务主管审核', 3 => '采购主管审核', 6 => '业务驳回', 7 => '采购驳回', 8 => '取消'];

        //总数
        $count = Db::name('order_return')
            ->alias('or')
            ->leftJoin('order_num on', 'on.orderCode=or.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->where($where)
            ->count('or.id');

        $list = Db::name('order_return')
            ->alias('or')
            ->field('or.id,or.returnCode,or.addtime,on.cgdNo,po.supplierNo,po.supplier_name,or.good_name,or.total_num, oo.updatetime receipttime ,or.error_num,or.error_remark,ri.result error_reason,or.status')
            ->leftJoin('order_num on', 'on.orderCode=or.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->leftJoin('order_out oo', 'oo.outCode=or.outCode AND oo.status=3')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where($where)
            ->withAttr('status', function ($val) use ($all_status) {
                return isset($all_status[$val]) ? $all_status[$val] : $val;
            })
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【四、售后报表】导出
    public function orderReturnListExport()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['or.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $all_status = [2 => '业务主管审核', 3 => '采购主管审核', 6 => '业务驳回', 7 => '采购驳回', 8 => '取消'];

        $list = Db::name('order_return')
            ->alias('or')
            ->field('or.returnCode 售后单编号,or.addtime 创建时间,on.cgdNo 采购单编号,po.supplier_name 供应商名称,or.good_name 产品名称,or.total_num 购买数量, oo.updatetime 签收日期,or.error_num 售后数量,or.error_remark 问题描述,ri.result 异常原因,or.status 售后状态')
            ->leftJoin('order_num on', 'on.orderCode=or.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->leftJoin('order_out oo', 'oo.outCode=or.outCode AND oo.status=3')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where($where)
            ->withAttr('售后状态', function ($val) use ($all_status) {
                return isset($all_status[$val]) ? $all_status[$val] : $val;
            })
            ->select()
            ->toArray();

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('售后明细报表' . date('YmdHis'), $headerArr, $list);

    }

    //【五、供应商报表】列表
    public function supplierList()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'update_start_date' => '',
            'update_end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
            'update_start_date' => 'date|elt:update_end_date',
            'update_end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['update_start_date'] != '' && $param['update_end_date'] != '') $where[] = ['s.updatetime', 'between', [$param['update_start_date'] . ' 00:00:00', $param['update_end_date'] . ' 23:59:59']];

        //总数
        $count = Db::name('supplier')
            ->alias('s')
            ->where($where)
            ->count('s.id');

        $list = Db::name('supplier')
            ->alias('s')
            ->field('s.id,s.addtime,s.name,s.creater,s.nature,s.source,s.pay_type,s.delivery_way,s.updatetime')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【五、供应商报表】导出
    public function supplierListExport()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'update_start_date' => '',
            'update_end_date' => '',
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date',
            'update_start_date' => 'date|elt:update_end_date',
            'update_end_date' => 'date',
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['update_start_date'] != '' && $param['update_end_date'] != '') $where[] = ['s.updatetime', 'between', [$param['update_start_date'] . ' 00:00:00', $param['update_end_date'] . ' 23:59:59']];

        $list = Db::name('supplier')
            ->alias('s')
            ->field('s.addtime 录入供应商时间,s.name 供应商名称,s.creater 采购维护人,s.nature 供应商性质,s.source 供应商来源,s.pay_type 付款方式,s.delivery_way 发货方式,s.updatetime 修改供应商时间')
            ->where($where)
            ->select()
            ->toArray();

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('供应商报表' . date('YmdHis'), $headerArr, $list);

    }

    //【六、退货流水报表】列表
    public function SaleReturnList()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $count = Db::name('sale_return')
            ->alias('s')
            ->where($where)
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->leftJoin('result_info ri', 'ri.result_code=s.error_code AND ri.is_del=0')
            ->count('s.id');

        $list = Db::name('sale_return')
            ->alias('s')
            ->field('s.returnCode,s.addtime,po.cgdNo,po.supplier_name,po.good_name,po.good_num,po.cgder,s.num,on.send_num,ri.result,s.is_th')
            ->where($where)
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->leftJoin('result_info ri', 'ri.result_code=s.error_code AND ri.is_del=0')
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【六、退货流水报表】导出
    public function SaleReturnExport()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => ''
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['s.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $all_is_th = [0 => '不可以', 1 => '可以'];
        $list = Db::name('sale_return')
            ->alias('s')
            ->field('s.returnCode 退货单号,s.addtime 退货时间,po.cgdNo 采购单编号,po.supplier_name 供应商名称,po.good_name 产品名称,po.good_num 购买数量,po.cgder 采购员,s.num 退货数量,on.send_num 已发货数量,ri.result 退货原因,s.is_th 供应商是否可以退货')
            ->where($where)
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo AND po.is_del=0')
            ->leftJoin('result_info ri', 'ri.result_code=s.error_code AND ri.is_del=0')
            ->withAttr('供应商是否可以退货', function ($val) use ($all_is_th) {
                return isset($all_is_th[$val]) ? $all_is_th[$val] : '';
            })
            ->select()
            ->toArray();

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('退货流水报表' . date('YmdHis'), $headerArr, $list);

    }

    //【七、商品报表】1.商品上线报表_列表
    public function GoodOnlineList()

    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0], ['gp.exam_status', '=', 6]]; //exam_status==6 上线成功
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['gp.online_time', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];//当上面状态确定为exam_status==6时，上线时间也可以取 updatetime
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $count = Db::name('good_platform')
            ->alias('gp')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('brand b', 'b.id=gb.brand_id AND b.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->count('gp.id');

        $list = Db::name('good_platform')
            ->alias('gp')
            ->field('gb.creater,gp.skuCode,gp.spuCode,gb.good_name,b.brand_name,"" good_model,s.name,gb.tax,gb.nake_price,gp.online_time')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('brand b', 'b.id=gb.brand_id AND b.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->withAttr('good_model', function ($val, $data) {
                return Db::name('good_spec')
                    ->alias('gp')
                    ->field('')
                    ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
                    ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
                    ->where([
                        'gp.spuCode' => $data['spuCode'],
                        'gp.is_del' => 0,
                        's.spec_name' => '型号',
                    ])
                    ->value('sv.spec_value', '');
            })
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【七、商品报表】1.商品上线报表_导出
    public function GoodOnlineExport()
    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => ''
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0], ['gp.exam_status', '=', 6]]; //exam_status==6 上线成功
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['gp.online_time', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $list = Db::name('good_platform')
            ->alias('gp')
            ->field('gb.creater 采购维护人,gp.skuCode 商品上线编号,gp.spuCode 商品成本编号,gb.good_name 商品名称,b.brand_name 品牌名称,"" 商品型号,s.name 供应商名称,gb.tax 税点,gb.nake_price 成本价合计,gp.online_time 上线时间')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('brand b', 'b.id=gb.brand_id AND b.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->withAttr('商品型号', function ($val, $data) {
                return Db::name('good_spec')
                    ->alias('gp')
                    ->field('')
                    ->leftJoin('specs s', 's.id=gp.spec_id AND s.is_del=0')
                    ->leftJoin('spec_value sv', 'sv.id=gp.spec_value_id AND s.is_del=0')
                    ->where([
                        'gp.spuCode' => $data['商品成本编号'],
                        'gp.is_del' => 0,
                        's.spec_name' => '型号',
                    ])
                    ->value('sv.spec_value', '');
            })
            ->select()
            ->toArray();
        halt($list);
        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('商品上线报表' . date('YmdHis'), $headerArr, $list);

    }

    //【七、商品报表】2.商品下线报表_列表
    public function GoodOfflineList()
    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0], ['gp.exam_status', '=', 8]]; //exam_status==8 下线成功
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['gp.updatetime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $count = Db::name('good_platform')
            ->alias('gp')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->count('gp.id');

        $list = Db::name('good_platform')
            ->alias('gp')
            ->field('gp.updatetime,gb.creater,gp.skuCode,gp.spuCode,gb.good_name,s.name')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->page($param['page'], $param['size'])
            ->select()
            ->toArray();

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【七、商品报表】2.商品下线报表_导出
    public function GoodOfflineExport()
    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => ''
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['s.is_del', '=', 0], ['gp.exam_status', '=', 8]]; //exam_status==8 下线成功
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['gp.updatetime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $list = Db::name('good_platform')
            ->alias('gp')
            ->field('gp.updatetime 商品下线时间,gb.creater 采购维护人,gp.skuCode 商品上线编号,gp.spuCode 商品成本编号,gb.good_name 商品名称,s.name 供应商名称')
            ->where($where)
            ->leftJoin('good_basic gb', 'gb.spuCode=gp.spuCode AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->select()
            ->toArray();

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('商品下线报表' . date('YmdHis'), $headerArr, $list);

    }

    //【七、商品报表】3.商品修改报表_列表
    public function ChangeLogList()
    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['cl.type', '=', 3]]; //type==3 商品成本变化记录
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['cl.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $count = Db::name('change_log')
            ->alias('cl')
            ->leftJoin('good_basic gb', 'gb.spuCode=cl.code AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->where($where)
            ->count('cl.id');

        $data = Db::name('change_log')
            ->alias('cl')
            ->field('cl.addtime,gb.creater,cl.code,gb.good_name,s.name,cl.before_info,cl.after_info')
            ->leftJoin('good_basic gb', 'gb.spuCode=cl.code AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->where($where)
            ->order('cl.addtime', 'desc')
            ->page($param['page'], $param['size'])
            ->cursor();

        $list = [];
        foreach ($data as $value) {

            $before_info = json_decode($value['before_info'], true);
            $after_info = json_decode($value['after_info'], true);

            if (isset($after_info['field_change'])) unset($after_info['field_change']);

            $value['before_info'] = [];
            foreach ($after_info as $kk => $vv) {

                //特殊字段处理
                switch ($kk) {
                    case 'brand_id':
                        $value['before_info'][$kk] = Db::name('brand')->where(['id' => $before_info[$kk], 'is_del' => 0])->value('brand_name', '');
                        $after_info[$kk] = Db::name('brand')->where(['id' => $vv, 'is_del' => 0])->value('brand_name', '');
                        break;

                    case 'cat_id':
                        $value['before_info'][$kk] = Db::name('cat')->where(['id' => $before_info[$kk], 'is_del' => 0])->value('cat_name', '');
                        $after_info[$kk] = Db::name('cat')->where(['id' => $vv, 'is_del' => 0])->value('cat_name', '');
                        break;

                    case 'delivery_place':
                        $delivery_place = explode(',', $before_info[$kk]);
                        $value['before_info'][$kk] = GetAddr(json_encode(['provice_code' => $delivery_place[0] ?? '', 'city_code' => $delivery_place[1] ?? '', 'area_code' => $delivery_place[2] ?? '']));

                        $delivery_place_2 = explode(',', $vv);
                        $after_info[$kk] = GetAddr(json_encode(['provice_code' => $delivery_place_2[0] ?? '', 'city_code' => $delivery_place_2[1] ?? '', 'area_code' => $delivery_place_2[2] ?? '']));
                        break;

                    case 'origin_place':
                        $origin_place = explode(',', $before_info[$kk]);
                        $value['before_info'][$kk] = GetAddr(json_encode(['provice_code' => $origin_place[0] ?? '', 'city_code' => $origin_place[1] ?? '', 'area_code' => $origin_place[2] ?? '']));

                        $origin_place_2 = explode(',', $vv);
                        $after_info[$kk] = GetAddr(json_encode(['provice_code' => $origin_place_2[0] ?? '', 'city_code' => $origin_place_2[1] ?? '', 'area_code' => $origin_place_2[2] ?? '']));
                        break;

                    default:
                        $value['before_info'][$kk] = $before_info[$kk];
                        break;
                }

            }

            $value['after_info'] = $after_info;

            $list[] = $value;

        }

        return app_show(0, '请求成功', ['list' => $list, 'count' => $count]);

    }

    //【七、商品报表】3.商品修改报表_导出
    public function ChangeLogExport()
    {
        $param = $this->request->only([
            'token',
            'creater' => '',
            'supplier_name' => '',
            'start_date' => '',
            'end_date' => ''
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['cl.type', '=', 3]]; //type==3 商品成本变化记录
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['cl.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];

        $data = Db::name('change_log')
            ->alias('cl')
            ->field('cl.addtime 商品操作时间,gb.creater 采购维护人,cl.code 商品成本编号,gb.good_name 商品名称,s.name 供应商名称,cl.before_info 修改前的内容,cl.after_info 修改后的内容')
            ->leftJoin('good_basic gb', 'gb.spuCode=cl.code AND gb.is_del=0')
            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
            ->where($where)
            ->cursor();

        $list = [];
        foreach ($data as $value) {

            $before_info = json_decode($value['修改前的内容'], true);
            $after_info = json_decode($value['修改后的内容'], true);
            $field_change = json_decode($after_info['field_change'], true);

            $value['修改前的内容'] = [];
            foreach ($field_change as $v) {
                $value['修改前的内容'][$v] = $before_info[$v];
            }
            unset($after_info['field_change']);
            $value['修改前的内容'] = json_encode($value['修改前的内容'], JSON_UNESCAPED_UNICODE);
            $value['修改后的内容'] = json_encode($after_info, JSON_UNESCAPED_UNICODE);

            $list[] = $value;

        }

        if (empty($list)) $list[] = '没有可供导出的数据';

        $headerArr = array_keys($list[0]);
        excelSave('商品修改报表' . date('YmdHis'), $headerArr, $list);

    }

    //【八、结算报表】_列表(暂不做)

    //【八、结算报表】_导出(暂不做)

    //【九、出入库明细报表】_列表
    public function GoodLogList()
    {
        /**
         * $param = $this->request->only([
         * 'token',
         * 'creater' => '',
         * 'supplier_name' => '',
         * 'start_date' => '',
         * 'end_date' => '',
         * 'page' => 1,
         * 'size' => 15
         * ], 'post', 'trim');
         * **/
    }

    //【九、出入库明细报表】_导出
    public function GoodLogExport()
    {
        /**
         * $param = $this->request->only([
         * 'token',
         * //            'creater' => '',
         * //            'supplier_name' => '',
         * //            'start_date' => '',
         * //            'end_date' => ''
         * ], 'post', 'trim');
         *
         * $val_params = Validate::rule([
         * 'start_date' => 'date|elt:end_date',
         * 'end_date' => 'date'
         * ]);
         *
         * if (!$val_params->check($param)) return error_show(1004, $val_params->getError());
         *
         * //        $where = [['gl.action_type', '=', 'CGD']];
         * //        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['gp.updatetime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
         * //        if ($param['creater'] != '') $where[] = ['gb.creater', 'like', '%' . $param['creater'] . '%'];
         * //        if ($param['supplier_name'] != '') $where[] = ['s.name', 'like', '%' . $param['supplier_name'] . '%'];
         *
         *
         * $list = Db::name('good_log')
         * ->alias('gl')
         * ->field('"" 创建人,"" 创建人公司,"" 创建人部门,"" 创建时间,"" 单据状态,"" 采购单号,gl.type 出入库类型,gb.good_name 商品名称,gb.spuCode 产品编码,gl.stock 出入库数量,gl.addtime 出入库日期,"" 维护人员,"" 发起人,wi.wsm_type 库房性质,s.name 库房公司,wi.name 库房名称')
         * //            ->where($where)
         * ->leftJoin('good_stock gs', 'gs.id=gl.stock_id AND gs.is_del=0')
         * ->leftJoin('good_basic gb', 'gb.spuCode=gs.spuCode AND gb.is_del=0')
         * ->leftJoin('warehouse_info wi', 'wi.wsm_code=gs.wsm_code AND wi.is_del=0')
         * ->leftJoin('supplier s', 's.code=wi.supplierNo AND s.is_del=0')
         * //            ->leftJoin('supplier s', 's.code=gb.supplierNo AND s.is_del=0')
         * ->select()
         * ->toArray();
         * halt($list);
         * if (empty($list)) $list[] = '没有可供导出的数据';
         *
         * $headerArr = array_keys($list[0]);
         * excelSave('出入库明细报表' . date('YmdHis'), $headerArr, $list);
         **/
    }

    //【十、退货台账-业务口径】
    public function ReturnExport()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => ''
        ], 'post', 'trim');

        $val_params = Validate::rule([
            'start_date' => 'date|elt:end_date',
            'end_date' => 'date'
        ]);

        if (!$val_params->check($param)) return error_show(1004, $val_params->getError());

        $where = [['sr.is_del', '=', 0]];
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['sr.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];

        $data = Db::name('sale_return')
            ->alias('sr')
            ->field('sr.returnCode 退货单号,sr.status 流程进度,sr.addtime 退货发起日期,s.supplierNo 公司名称,sr.apply_name 退货人,sr.num 退货数量,sr.total_fee 退货销售货款,sr.remark 退货备注,c.companyName 客户名称,or.customer_code 客户编码,"" 一级组织,"" 二级组织,"" 三级组织,p.platform_name 平台类型,s.poNo PO编号,sr.orderCode 确认单编号,s.order_type 确认单类型,s.addtime 确认单下单时间,s.apply_name 业务人员,sr.orderCode 确认单产品编号,sr.good_name 产品名称,sr.good_code 产品编码,s.cat_id,c.itemid')
            ->leftJoin('platform p', 'p.id = sr.platform_id AND p.is_del = 0')
            ->leftJoin('order_return or', 'or.orderCode = sr.orderCode AND or.is_del = 0')
            ->leftJoin('customer_info c', 'c.companyNo = or.customer_code AND c.is_del = 0')
            ->leftJoin('sale s', 's.orderCode = sr.orderCode AND s.is_del = 0')
            ->where($where)
            ->cursor();

        $all_sale_return_status = [1 => '待业务审批', 2 => '待专员审批', 3 => '待主管审批', 4 => '退货完成', 5 => '业务驳回', 6 => '采购驳回', 7 => '专员审批不通过'];
        $all_sale_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售',];
        $list = [];
        foreach ($data as $value) {

            $value['流程进度'] = isset($all_sale_return_status[$value['流程进度']]) ? $all_sale_return_status[$value['流程进度']] : '';
            $value['确认单类型'] = isset($all_sale_order_type[$value['确认单类型']]) ? $all_sale_order_type[$value['确认单类型']] : '';

            $top = made($value['cat_id']);
            $value['一级分类'] = isset($top[0]['name']) ? $top[0]['name'] : '';

            if (!empty($value['itemid'])) {
                $customer_org1 = get_top_customer_org($value['itemid']);

                foreach ($customer_org1 as $vv) {
                    switch ($vv['level']) {
                        case 1:
                            $value['一级组织'] = $vv['name'];
                            break;
                        case 2:
                            $value['二级组织'] = $vv['name'];
                            break;
                        case 3:
                            $value['三级组织'] = $vv['name'];
                            break;
                    }
                }
            }

            unset($value['cat_id']);
            unset($value['itemid']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = '没有可供导出的数据';
    
        $headerArr = array_keys($list[0]);
        excelSave('退货台账-业务口径' . date('YmdHis'), $headerArr, $list);
    }

    //库存预警汇总
    public function StockExport()
    {

    }

}