phpstock/app/admin/controller/SaleReport.php

<?php

namespace app\admin\controller;

use app\abutment\model\SupplierRelationUser;
use app\abutment\model\SupplierUser;
use think\facade\Db;

//前端报表处理类
class SaleReport extends Base
{

    private $all_order_type = [1 => '备库', 2 => '非库存', 3 => '咨询采反', 4 => '项目采反', 5 => '平台部订单销售库存', 6 => '平台部订单销售非库存'];//订单类型
    private $all_stock = [0 => '非库存品', 1 => '库存品'];//是否库存品
    private $all_sale_status = [0 => '待发货', 1 => '待发货完成', 2 => '发货已完成', 3 => '订单已取消'];//订单状态(sale表里的status)
    private $is_addrs = [0 => '是', 1 => '否'];//是否一地(数据库中存的是'是否多地'，逻辑相反)
    private $is_custom = [0 => '否', 1 => '是'];//是否定制
    private $consult_info_status = [1 => '招标进行中', 2 => '招标已结束', 3 => '等待议价结果', 4 => '确认商品', 5 => '成功转单', 6 => '取消转单', 7 => '招标已暂停'];//状态
    private $noble_metal = [1 => '18K', 2 => '24K', 3 => '白银'];//贵金属种类
    private $all_order_return_status = [1 => '待业务审核', 2 => '待采购审核', 3 => '待设置仓库', 4 => '待客户退货', 5 => '售后已完成', 6 => '业务已驳回', 7 => '采购已驳回', 8 => '申请已取消'];
    private $is_activity = [0 => '不参与活动', 1 => '参与活动'];
    private $good_type = [1 => '正常商品', 2 => '赠品', 3 => '样品'];
    private $sale_diff_status = [1 => '修改销售额', 2 => '不修改销售额'];
    private $is_receive = [0 => '未收到', 1 => '收到'];
    private $bargain_order_status = [1 => '待主管审批', 2 => '待财务专员审批', 3 => '待财务主管审批', 4 => '审批通过', 5 => '主管驳回', 6 => '财务主管驳回', 7 => '已取消议价'];
    private $order_out_status = [0 => '待发货', 1 => '待库管发货', 2 => '已发货待收货', 3 => '已收货', 4 => '已全部退货'];
    private $except_code = [0 => [1 => '退货', 2 => '补发'], 1 => [1 => '退货', 2 => '换货'],];//期望意愿
    private $order_back_status = [0 => '待申请', 1 => '待验收', 2 => '待验收审核', 3 => '待业务审核', 4 => '完成退货'];


    //【一、管理报表】1.日报_列表
    public function consultDay()
    {
        $param = $this->request->only(['token', 'date' => date('Y-m-d'),'relaComNo'=>''], 'post', 'trim');

        $rs_1_where=[
            ['ci.is_del','=',0],
            ['ci.addtime','between',[$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
        ];
        if($param['relaComNo'] !== '') $rs_1_where[]=['co.companyNo','=',$param['relaComNo']];
        $rs_1 = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->where($rs_1_where)
            ->group('co.createrid,co.creater')
            ->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');

        $rs_2_where=[
            ['is_del','=',0],
            ['addtime','between',[$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
        ];
        if($param['relaComNo'] !== '') $rs_2_where[]=['customer_code|supplierNo','=',$param['relaComNo']];
        $rs_2 = Db::name('sale')
            ->where($rs_2_where)
            ->group('apply_id,apply_name')
            ->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');

        $rs_3_where=[
            ['oo.status','=',3],
            ['oo.addtime','between',[$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
        ];
        if($param['relaComNo'] !== '') $rs_3_where[]=['s.customer_code|s.supplierNo','=',$param['relaComNo']];
        $rs_3 = Db::name('order_out')
            ->alias('oo')
            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
            ->where($rs_3_where)
            ->group('oo.apply_id,oo.apply_name')
            ->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');

        $rs_4_where=[
            ['or.is_del','=',0],
            ['or.addtime','between',[$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
        ];
        if($param['relaComNo'] !== '') $rs_4_where[]=['s.customer_code|s.supplierNo|or.customer_code','=',$param['relaComNo']];
        $rs_4 = Db::name('order_return')
            ->alias('or')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->where($rs_4_where)
            ->group('or.apply_id,or.apply_name')
            ->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');

        $all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));

        $list = [];
        foreach ($all_ids as $id) {

            $val = [];
            if (isset($rs_1[$id]['创建人'])) $val['creater'] = $rs_1[$id]['创建人'];
            elseif (isset($rs_2[$id]['创建人'])) $val['creater'] = $rs_2[$id]['创建人'];
            elseif (isset($rs_3[$id]['创建人'])) $val['creater'] = $rs_3[$id]['创建人'];
            elseif (isset($rs_4[$id]['创建人'])) $val['creater'] = $rs_4[$id]['创建人'];
            else $val['creater'] = '';

            $val['creater_company'] = get_company_name_by_uid($id);//获取创建人的所属部门

            $val['consult_num'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
            $val['consult_money'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';

            $val['sale_num'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
            $val['sale_money'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';


            $val['receipt_num'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
            $val['receipt_money'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';

            $val['invoice_num'] = '';
            $val['invoice_money'] = '';

            $val['after_sales_num'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
            $val['after_sales_money'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';

            $val['payment_num'] = '';
            $val['payment_money'] = '';

            $list[] = $val;
        }

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

    }

    //【一、管理报表】1.日报_导出
    public function consultDayExport()
    {
        $param = $this->request->only(['token', 'date' => date('Y-m-d'),'relaComNo'=>''], 'post', 'trim');

        $rs_1_where=[
            ['ci.is_del','=',0],
            ['ci.addtime','between',[$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59']],
        ];
        if($param['relaComNo'] !== '') $rs_1_where[]=['co.companyNo','=',$param['relaComNo']];
        $rs_1 = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->where($rs_1_where)
            ->group('co.createrid,co.creater')
            ->column('COUNT(ci.id) 当日咨询单数量,SUM(ci.budget_price*ci.num) 金额,co.creater 创建人', 'co.createrid');

        $rs_2 = Db::name('sale')
            ->where('is_del', 0)
            ->whereBetween('addtime', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59'])
            ->group('apply_id,apply_name')
            ->column('COUNT(id) 当日下单数量,SUM(total_price) 金额,apply_name 创建人', 'apply_id');

        $rs_3 = Db::name('order_out')
            ->alias('oo')
            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
            ->where(['oo.status' => 3])
            ->whereBetween('oo.addtime', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59'])
            ->group('oo.apply_id,oo.apply_name')
            ->column('COUNT(oo.id) 当日签收数量,SUM(s.total_price) 金额,oo.apply_name 创建人', 'oo.apply_id');

        $rs_4 = Db::name('order_return')
            ->alias('or')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->where(['or.is_del' => 0])
            ->whereBetween('or.addtime', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59'])
            ->group('or.apply_id,or.apply_name')
            ->column('COUNT(or.id) 单日售后单数,SUM(s.total_price) 金额,or.apply_name 创建人', 'or.apply_id');

        $all_ids = array_unique(array_merge(array_keys($rs_1), array_keys($rs_2), array_keys($rs_3), array_keys($rs_4)));

        $list = [];
        foreach ($all_ids as $id) {

            $val = [];
            if (isset($rs_1[$id]['创建人'])) $val['创建人'] = $rs_1[$id]['创建人'];
            elseif (isset($rs_2[$id]['创建人'])) $val['创建人'] = $rs_2[$id]['创建人'];
            elseif (isset($rs_3[$id]['创建人'])) $val['创建人'] = $rs_3[$id]['创建人'];
            elseif (isset($rs_4[$id]['创建人'])) $val['创建人'] = $rs_4[$id]['创建人'];
            else $val['创建人'] = '';

            $val['创建人部门'] = get_company_name_by_uid($id);//获取创建人的所属部门

            $val['当日咨询单数量'] = isset($rs_1[$id]['当日咨询单数量']) ? $rs_1[$id]['当日咨询单数量'] : '';
            $val['当日咨询单金额'] = isset($rs_1[$id]['金额']) ? $rs_1[$id]['金额'] : '';

            $val['当日下单数量'] = isset($rs_2[$id]['当日下单数量']) ? $rs_2[$id]['当日下单数量'] : '';
            $val['当日下单金额'] = isset($rs_2[$id]['金额']) ? $rs_2[$id]['金额'] : '';


            $val['当日签收数量'] = isset($rs_3[$id]['当日签收数量']) ? $rs_3[$id]['当日签收数量'] : '';
            $val['当日签收金额'] = isset($rs_3[$id]['金额']) ? $rs_3[$id]['金额'] : '';

            $val['当日申报发票数量'] = '';
            $val['当日申报发票金额'] = '';

            $val['单日售后单数'] = isset($rs_4[$id]['单日售后单数']) ? $rs_4[$id]['单日售后单数'] : '';
            $val['单日售后金额'] = isset($rs_4[$id]['金额']) ? $rs_4[$id]['金额'] : '';

            $val['当日回款单数'] = '';
            $val['当日回款金额'] = '';

            $list[] = $val;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('咨询单报表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【一、管理报表】2.竞价反馈报表
    public function consultInfo()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $where = [['ci.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']];

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

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('co.creater,ci.addtime,ci.infoNo,cb.bidNo,csi.companyName,ci.good_name,ci.num,u.unit,ci.budget_price,cb.id bid_id,ci.status,csi.itemid,co.createrid')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = $customer_tmp = $company_tmp = [];
        foreach ($data as $value) {
            $value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
            $value['is_bids'] = is_null($value['bid_id']) ? '否' : '是';
            $value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
            if (!empty($value['itemid'])) {
                if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
                $value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
            }

            if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
            $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
            $list[] = $value;
        }

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

    }

    //【一、管理报表】2.竞价反馈报表_导出
    public function consultInfoExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

        $where = [['ci.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']];

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,cb.bidNo 反馈编号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,cb.id 是否采反,ci.status 是否转单,csi.itemid,co.createrid')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = $customer_tmp = $company_tmp = [];
        foreach ($data as $value) {
            if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
            $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
            $value['金额'] = round($value['单价'] * $value['数量'], 2);
            $value['是否采反'] = is_null($value['是否采反']) ? '否' : '是';
            $value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单

            if (!empty($value['itemid'])) {
                if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
                $value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
            }

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

            $list[] = $value;
        }

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

        excelSave('竞价反馈报表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【一、管理报表】2.1.竞价单报表
    public function consultInfoNew()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
            'page' => 1,
            'size' => 15,
        ], 'post', 'trim');

        $where = [['ci.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']];

        $count = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->count('ci.id');

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('co.creater,ci.addtime,ci.infoNo,csi.companyName,ci.good_name,ci.num,u.unit,ci.budget_price,ci.status,csi.itemid,co.createrid')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = $customer_tmp = $company_tmp = [];
        foreach ($data as $value) {
            $value['total_fee'] = round($value['budget_price'] * $value['num'], 2);
            $value['is_zhuandan'] = $value['status'] == 5 ? '是' : '否';//5成功转单
            if (!empty($value['itemid'])) {
                if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
                $value['second_customer_org'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
            }

            if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
            $value['creater_company'] = $company_tmp[$value['createrid']] ?? '';
            $list[] = $value;
        }

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

    }

    //【一、管理报表】2.1.竞价单报表_导出
    public function consultInfoNewExport()
    {
        $param = $this->request->only([
            'token',
            'start_date' => '',
            'end_date' => '',
        ], 'post', 'trim');

        $where = [['ci.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']];

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('co.creater 创建人,"" 创建人部门,ci.addtime 竞价时间,ci.infoNo 竞价单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,ci.good_name 产品名称,ci.num 数量,u.unit 单位,ci.budget_price 单价, "" 金额,ci.status 是否转单,csi.itemid,co.createrid')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = $customer_tmp = $company_tmp = [];
        foreach ($data as $value) {
            if (!isset($company_tmp[$value['createrid']])) $company_tmp[$value['createrid']] = get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
            $value['创建人部门'] = $company_tmp[$value['createrid']] ?? '';
            $value['金额'] = round($value['单价'] * $value['数量'], 2);
            $value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单

            if (!empty($value['itemid'])) {
                if (!isset($customer_tmp[$value['itemid']])) $customer_tmp[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_tmp[$value['itemid']][1]) ? $customer_tmp[$value['itemid']][1] : '';
                $value['二级企业'] = isset($customer_tmp[$value['itemid']][2]) ? $customer_tmp[$value['itemid']][2] : '';
            }

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

            $list[] = $value;
        }

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

        excelSave('竞价单报表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【一、管理报表】3.订单报表
    public function saleList()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

        $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')
            ->alias('s')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->count('s.id');

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.apply_name,s.addtime,s.orderCode,csi.companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.is_stock,s.order_type,csi.itemid,s.good_code,s.apply_id,s.cat_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('s.addtime', 'desc')
            ->cursor();

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

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else  $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['is_stock'] = isset($this->all_stock[$value['is_stock']]) ? $this->all_stock[$value['is_stock']] : '';
            $value['order_type'] = isset($this->all_order_type[$value['order_type']]) ? $this->all_order_type[$value['order_type']] : '';
            $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
            if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));

            $value['cat_name'] = $cat_tmp[$value['cat_id']] ?? '';

            $list[] = $value;
        }

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

    }

    //【一、管理报表】3.订单报表_导出
    public function saleExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

        $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']];

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.is_stock 是否库存,s.order_type 订单类型,csi.itemid,s.good_code,s.apply_id,s.cat_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->cursor();

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

            if (!empty($value['itemid'])) {

                if (!isset($tmp_customer[$value['itemid']])) $tmp_customer[$value['itemid']] = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
//                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($tmp_customer[$value['itemid']][1]) ? $tmp_customer[$value['itemid']][1] : '';
                $value['二级企业'] = isset($tmp_customer[$value['itemid']][2]) ? $tmp_customer[$value['itemid']][2] : '';
            }

            if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
                $value['单位'] = Db::name("good_zixun")
                    ->alias('gz')
                    ->leftJoin('unit u', 'u.id=gz.good_unit')
                    ->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
                    ->value('u.unit');
            } else {
                $value['单位'] = Db::name('good_basic')
                    ->alias('gb')
                    ->leftJoin('unit u', 'u.id=gb.good_unit')
                    ->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
                    ->value('u.unit');
            }

            $value['是否库存'] = isset($this->all_stock[$value['是否库存']]) ? $this->all_stock[$value['是否库存']] : '';
            $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';

            if (!isset($tmp_item[$value['apply_id']])) $tmp_item[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
            $value['创建人所属部门'] = $tmp_item[$value['apply_id']];//获取创建人的所属部门

            if (!isset($cat_tmp[$value['cat_id']])) $cat_tmp[$value['cat_id']] = implode('/', array_column(made($value['cat_id']), 'name'));
            $value['分类'] = $cat_tmp[$value['cat_id']] ?? '';

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

            $list[] = $value;
        }

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

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

    }

    //【一、管理报表】4.未发货报表_列表
    public function saleNotOut()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

        $where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
        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['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];

        $count = Db::name('sale')
            ->alias('s')
            ->field('s.apply_name,s.addtime,s.orderCode,csi.companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.arrive_time,csi.itemid,s.good_code,s.order_type,s.apply_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->count('s.id');

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.apply_name,s.addtime,s.orderCode,csi.companyName,s.good_name,s.good_num,s.sale_price,s.total_price,s.arrive_time,csi.itemid,s.good_code,s.order_type,s.apply_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('s.addtime', 'desc')
            ->cursor();

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

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3) {
                $value['unit'] = Db::name("good_zixun")
                    ->alias('gz')
                    ->leftJoin('unit u', 'u.id=gz.good_unit')
                    ->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])
                    ->value('u.unit');
            } else {
                $value['unit'] = Db::name('good_basic')
                    ->alias('gb')
                    ->leftJoin('unit u', 'u.id=gb.good_unit')
                    ->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])
                    ->value('u.unit');
            }

            $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            $list[] = $value;
        }

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

    }

    //【一、管理报表】4.未发货报表_导出
    public function saleNotOutExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_arrive_date' => '', 'end_arrive_date' => ''], 'post', 'trim');

        $where = [['s.is_del', '=', 0], ['out.status', '=', 1]];
        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['start_arrive_date'] != '' && $param['end_arrive_date'] != '') $where[] = ['s.arrive_time', 'between', [$param['start_arrive_date'] . ' 00:00:00', $param['end_arrive_date'] . ' 23:59:59']];

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,s.arrive_time 承诺发货时间,csi.itemid,s.good_code,s.order_type,s.apply_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->cursor();

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

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            unset($value['itemid']);
            unset($value['good_code']);
            unset($value['order_type']);
            unset($value['apply_id']);

            $list[] = $value;
        }

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

        excelSave('未发货报表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【一、管理报表】5.未签收报表
    public function saleNotSigned()
    {

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

        $where = [['oo.is_del', '=', 0], ['oo.status', '=', 2], ['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('order_out')
            ->alias('oo')
            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
//            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->count('oo.id');

        $data = Db::name('order_out')
            ->alias('oo')
            ->field('oo.id,s.apply_name,s.addtime,s.orderCode,csi.companyName,s.good_name,s.good_num,s.sale_price,s.total_price,oo.sendtime,csi.itemid,s.good_code,s.order_type,s.apply_id')
            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
//            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('s.addtime', 'desc')
            ->cursor();

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

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            $list[] = $value;
        }

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

    }

    //【一、管理报表】5.未签收报表_导出
    public function saleNotSignedExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

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

//        $data = Db::name('order_out')
//            ->alias('oo')
//            ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,csi.itemid,s.good_code,s.order_type,s.apply_id')
//            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
//            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
//            ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
//            ->where($where)
//            ->order('s.addtime', 'desc')
//            ->cursor('oo.id');

//        $list = [];
//        foreach ($data as $value) {
//
//            if (!empty($value['itemid'])) {
//                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
//                $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
//                $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
//            }
//
//            if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
//            else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');
//
//            $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门
//
//            unset($value['itemid']);
//            unset($value['good_code']);
//            unset($value['order_type']);
//            unset($value['apply_id']);
//
//            $list[] = $value;
//        }

        $list = $this->saleNotSignedExportTemp($where);
        excelSave('未签收报表' . date('YmdHis'), ['创建人', '创建人所属部门', '下单时间', '订单号', '一级企业', '二级企业', '客户名称', '商品名称', '数量', '单位', '单价', '金额', '发货时间'], $list);

    }


    //针对"【一、管理报表】5.未签收报表_导出"数据量太大
    private function saleNotSignedExportTemp(array $where = [])
    {
        $data = Db::name('order_out')
            ->alias('oo')
            ->field('s.apply_name 创建人,"" 创建人所属部门,s.addtime 下单时间,s.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,s.good_name 商品名称,s.good_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,oo.sendtime 发货时间,csi.itemid,s.good_code,s.order_type,s.apply_id')
            ->leftJoin('sale s', 's.orderCode=oo.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode AND out.is_del=0')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->cursor();

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['创建人所属部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            unset($value['itemid']);
            unset($value['good_code']);
            unset($value['order_type']);
            unset($value['apply_id']);

            yield $value;
        }

    }

    //【一、管理报表】6.售后报表
    public function orderReturn()
    {

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

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

        $count = Db::name('order_return')
            ->alias('or')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
            ->leftJoin('result_info ri', 'ri.id=or.error_code')
            ->where($where)
            ->count('or.id');

        $data = Db::name('order_return')
            ->alias('or')
            ->field('or.addtime,or.apply_name,or.orderCode,csi.companyName,or.good_name,or.total_num,s.sale_price,s.total_price,or.updatetime,ri.result,or.status,csi.itemid,or.good_code,or.order_type,or.apply_id')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
            ->leftJoin('result_info ri', 'ri.id=or.error_code')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('s.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }
            $value['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['unit'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else $value['unit'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['is_finish'] = $value['status'] == 5 ? '已完结' : '未完结';
            $value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';

            $list[] = $value;
        }

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

    //【一、管理报表】6.售后报表_导出
    public function orderReturnExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');

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

        $data = Db::name('order_return')
            ->alias('or')
            ->field('or.addtime 售后时间,or.apply_name 创建人,"" 创建人部门,or.orderCode 订单号,"" 一级企业,"" 二级企业,csi.companyName 客户名称,or.good_name 商品名称,or.total_num 数量,"" 单位,s.sale_price 单价,s.total_price 金额,or.updatetime 签收时间,ri.result 异常原因,or.status 异常状态,"" 是否完结,csi.itemid,or.good_code,or.order_type,or.apply_id')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('customer_info csi', 'csi.companyNo=or.customer_code')
            ->leftJoin('order_out out', 'out.orderCode=s.orderCode')
            ->leftJoin('result_info ri', 'ri.id=or.error_code')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            if ($value['order_type'] == 3 || $value['order_type'] == 4) $value['单位'] = Db::name("good_zixun")->alias('gz')->leftJoin('unit u', 'u.id=gz.good_unit')->where(["gz.spuCode" => $value['good_code'], "gz.is_del" => 0])->value('u.unit');
            else $value['单位'] = Db::name('good_basic')->alias('gb')->leftJoin('unit u', 'u.id=gb.good_unit')->where(["gb.spuCode" => $value['good_code'], "gb.is_del" => 0])->value('u.unit');

            $value['创建人部门'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            $value['是否完结'] = $value['异常状态'] == 5 ? '已完结' : '未完结';
            $value['异常状态'] = isset($this->all_order_return_status[$value['异常状态']]) ? $this->all_order_return_status[$value['异常状态']] : '';

            unset($value['itemid']);
            unset($value['good_code']);
            unset($value['order_type']);
            unset($value['apply_id']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('售后报表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【一、管理报表】7.产品热销排行
    public function goodRankingList()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

        $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')
            ->alias('s')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->group('s.good_code,s.good_name,csi.itemid,s.cat_id')
            ->count('s.id');

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.good_code,s.good_name,SUM(s.good_num) total_num,SUM(s.total_price) total_price,csi.itemid,s.cat_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->group('s.good_code,s.good_name,csi.itemid,s.cat_id')
            ->page($param['page'], $param['size'])
            ->order('total_num', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            $value['cat'] = implode('/', array_column(made($value['cat_id']), 'name'));

            $list[] = $value;
        }

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

    }

    //【一、管理报表】7.产品热销排行_导出
    public function goodRankingListExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

        $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']];


        $data = Db::name('sale')
            ->alias('s')
            ->field('s.good_code,"" 产品品类,s.good_name 产品名称,SUM(s.good_num) total_num,"" 数量,SUM(s.total_price) 金额,csi.itemid,s.cat_id')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->where($where)
            ->group('s.good_code,s.good_name,csi.itemid,s.cat_id')
            ->order('total_num', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级企业'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级企业'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            $value['产品品类'] = implode('/', array_column(made($value['cat_id']), 'name'));

            $value['数量'] = $value['total_num'];

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

            $list[] = $value;
        }


        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('产品热销排行' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【三、咨询单导出明细】
    public function consultDetailed()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

        $where = [['ci.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['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];

        $count = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
            ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('depart_user du', 'du.uid=co.saleid AND du.is_del=0')
            ->leftJoin('company_item item', 'item.id=du.itemid')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('brand b', 'b.id=ci.brand_id')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->count('ci.id');

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('ci.addtime,co.salesman,item.name,cb.infoNo,csi.companyName,ci.use_desc,ci.good_name,b.brand_name,ci.model,s.good_code,ci.cat_id,ci.budget_price,u.unit,ci.num,s.sale_price,ci.arrival_time,ci.is_addrs,ci.is_custom,ci.remark,ci.status,ci.bargain_code,csi.itemid,s.orderCode')
            ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
            ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('depart_user du', 'du.uid=co.saleid AND du.is_del=0')
            ->leftJoin('company_item item', 'item.id=du.itemid')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('brand b', 'b.id=ci.brand_id')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            $cat = made($value['cat_id']);
            $value['first_cat_name'] = isset($cat[0]['name']) ? $cat[0]['name'] : '';

            $value['budget_total_price'] = round($value['budget_price'] * $value['num'], 2);
            $value['is_addrs'] = isset($this->is_addrs[$value['is_addrs']]) ? $this->is_addrs[$value['is_addrs']] : '';
            $value['is_custom'] = isset($this->is_custom[$value['is_custom']]) ? $this->is_custom[$value['is_custom']] : '';
            $value['status'] = isset($this->consult_info_status[$value['status']]) ? $this->consult_info_status[$value['status']] : '';

            $list[] = $value;
        }

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

    }

    //【三、咨询单导出明细】
    public function consultDetailedExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'use_desc' => ''], 'post', 'trim');

        $where = [['ci.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['use_desc'] != '') $where[] = ['ci.use_desc', 'like', '%' . $param['use_desc'] . '%'];

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('ci.addtime 咨询时间,co.salesman 业务员,item.name 业务部门,cb.infoNo 咨询单号,"" 一级组织,"" 二级组织,csi.companyName 客户名称,ci.use_desc 产品用途,ci.good_name 产品名称,b.brand_name 品牌,ci.model 型号,s.good_code 产品编码,"" 一级分类,ci.cat_id,ci.budget_price 预算单价,u.unit 单位,ci.num 需求数量,"" 预算总价,s.sale_price 单价,ci.arrival_time 要求到货时间,ci.is_addrs 是否一地,ci.is_custom 定制,ci.remark 咨询备注,ci.status 竞单状态,ci.bargain_code 上次咨询单单号,s.orderCode 销售订单编号,csi.itemid')
            ->leftJoin('consult_bids cb', 'cb.infoNo=ci.infoNo AND cb.is_del=0')
            ->leftJoin('sale s', 's.zxNo=cb.bidNo AND s.is_del=0')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('depart_user du', 'du.uid=co.saleid AND du.is_del=0')
            ->leftJoin('company_item item', 'item.id=du.itemid')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('brand b', 'b.id=ci.brand_id')
            ->leftJoin('unit u', 'u.id=ci.unit')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

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

            $value['预算总价'] = round($value['预算单价'] * $value['需求数量'], 2);
            $value['是否一地'] = isset($this->is_addrs[$value['是否一地']]) ? $this->is_addrs[$value['是否一地']] : '';
            $value['定制'] = isset($this->is_custom[$value['定制']]) ? $this->is_custom[$value['定制']] : '';
            $value['竞单状态'] = isset($this->consult_info_status[$value['竞单状态']]) ? $this->consult_info_status[$value['竞单状态']] : '';

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

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('咨询单导出明细' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【四、工差明细表】
    public function saleDiff()
    {

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

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

        $count = Db::name('purchease_diff')
            ->alias('pd')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
            ->leftJoin('sale s', 's.orderCode=on.orderCode')
            ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
            ->where($where)
            ->order('pd.addtime', 'desc')
            ->count('pd.id');

        $data = Db::name('purchease_diff')
            ->alias('pd')
            ->field('pd.addtime,sd.diffCode,sd.orderCode,pd.apply_name,pd.apply_id,po.addtime poaddtime,pd.good_name,ci.companyName,pd.good_num,pd.sale_price,pd.good_weight,pd.diff_weight,pd.diff_price,sd.status,po.order_type,po.spuCode,s.apply_name sale_apply_name,s.apply_id sale_apply_id')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
            ->leftJoin('sale s', 's.orderCode=on.orderCode')
            ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('pd.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if ($value['order_type'] == 3) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
            else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');


            $value['apply_company'] = get_company_name_by_uid($value['apply_id']);
            $value['sale_apply_company'] = get_company_name_by_uid($value['sale_apply_id']);
            $value['noble_metal'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
            $value['total_price'] = round($value['sale_price'], $value['good_num'], 2);
            $value['is_update_sale'] = isset($this->sale_diff_status[$value['status']]) ? $this->sale_diff_status[$value['status']] : '';

            $list[] = $value;
        }

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

    }

    //【四、工差明细表】
    public function saleDiffExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

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

        $data = Db::name('purchease_diff')
            ->alias('pd')
            ->field('pd.addtime 发起时间,sd.diffCode 工差单号,sd.orderCode 关联确认单,pd.apply_name 创建人,"" 部门,pd.apply_id,po.addtime 确认单下单日期,pd.good_name 产品名称,ci.companyName 客户名称,"" 贵金属种类,pd.good_num 下单数量,pd.sale_price 单价,"" 总额,pd.good_weight 商品总重量,pd.diff_weight 工差重量,pd.diff_price 销售工差总费用,sd.status 是否修改销售额,s.apply_name 销售人员,s.apply_id sale_apply_id,"" 销售部门,po.order_type,po.spuCode')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('order_num on', 'on.cgdNo=pd.cgdNo')
            ->leftJoin('sale s', 's.orderCode=on.orderCode')
            ->leftJoin('customer_info ci', 'ci.companyNo=s.customer_code')
            ->where($where)
            ->order('pd.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            if ($value['order_type'] == 3) $noble_metal = Db::name("good_zixun")->where(["spuCode" => $value['spuCode'], "is_del" => 0])->value('noble_metal');
            else $noble_metal = Db::name('good_basic')->where(['spuCode' => $value['spuCode'], 'is_del' => 0])->value('noble_metal');

            $value['部门'] = get_company_name_by_uid($value['apply_id']);
            $value['销售部门'] = get_company_name_by_uid($value['sale_apply_id']);
            $value['贵金属种类'] = isset($this->noble_metal[$noble_metal]) ? $this->noble_metal[$noble_metal] : '';
            $value['总额'] = round($value['单价'], $value['下单数量'], 2);
            $value['是否修改销售额'] = isset($this->sale_diff_status[$value['是否修改销售额']]) ? $this->sale_diff_status[$value['是否修改销售额']] : '';

            unset($value['apply_id']);
            unset($value['order_type']);
            unset($value['spuCode']);
            unset($value['sale_apply_id']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('工差明细表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【五、退货明细表】
    public function thData()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

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

        $count = Db::name('th_data')
            ->alias('thd')
            ->leftJoin('sale s', 's.orderCode=thd.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo=s.customer_code')
            ->where($where)
            ->order('thd.addtime', 'desc')
            ->count('thd.id');

        $data = Db::name('th_data')
            ->alias('thd')
            ->field('thd.id,thd.th_type,thd.addtime return_time,thd.thCode,"" outCode,"" status,"" apply_name,"" apply_company,thd.orderCode,s.addtime,c.companyName,s.good_name,s.sale_price,s.wsend_num,s.th_num,s.th_fee,"" contactor,"" mobile,"" addr,"" result')
            ->leftJoin('sale s', 's.orderCode=thd.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo=s.customer_code')
            ->where($where)
            ->order('thd.addtime', 'desc')
            ->select()
            ->toArray();

        $th_types = [];
        foreach ($data as $value) {
            $th_types[$value['th_type']][] = $value['thCode'];
        }

        $all_order_return = Db::name('order_return')
            ->alias('or')
            ->withAttr('status', function ($val) {
                return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
            })
            ->withAttr('apply_company', function ($val, $da) {
                return get_company_name_by_uid($da['apply_id']);
            })
            ->leftJoin('order_out oo', 'oo.outCode=or.outCode')
            ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where('or.is_del', 0)
            ->whereIn('or.returnCode', $th_types[2])
            ->column('or.id,or.outCode,or.status,or.apply_id,or.apply_name,"" apply_company,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'or.returnCode');


        $all_order_back = Db::name('order_back')
            ->alias('ob')
            ->where('ob.is_del', 0)
            ->whereIn('ob.thNo', $th_types[3])
            ->withAttr('status', function ($val) {
                return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
            })
            ->withAttr('apply_company', function ($val, $da) {
                return get_company_name_by_uid($da['apply_id']);
            })
            ->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
            ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
            ->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
            ->column('ob.id,ob.outCode,ob.status,ob.apply_id,ob.apply_name,"" apply_company,oa.contactor,oa.mobile,oa.addr,ri.result,oa.addr_code', 'thNo');

        $list = [];

        foreach ($data as $value) {
            if ($value['th_type'] == 2) {
                $value['outCode'] = isset($all_order_return[$value['thCode']]['outCode']) ? $all_order_return[$value['thCode']]['outCode'] : '';
                $value['status'] = isset($all_order_return[$value['thCode']]['status']) ? $all_order_return[$value['thCode']]['status'] : '';
                $value['apply_name'] = isset($all_order_return[$value['thCode']]['apply_name']) ? $all_order_return[$value['thCode']]['apply_name'] : '';
                $value['apply_company'] = isset($all_order_return[$value['thCode']]['apply_company']) ? $all_order_return[$value['thCode']]['apply_company'] : '';
                $value['contactor'] = isset($all_order_return[$value['thCode']]['contactor']) ? $all_order_return[$value['thCode']]['contactor'] : '';
                $value['mobile'] = isset($all_order_return[$value['thCode']]['mobile']) ? $all_order_return[$value['thCode']]['mobile'] : '';
                $value['addr'] = isset($all_order_return[$value['thCode']]['addr']) ? $all_order_return[$value['thCode']]['addr'] : '';
                $value['result'] = isset($all_order_return[$value['thCode']]['result']) ? $all_order_return[$value['thCode']]['result'] : '';
            } elseif ($value['th_type'] == 3) {
                $value['outCode'] = isset($all_order_back[$value['thCode']]['outCode']) ? $all_order_back[$value['thCode']]['outCode'] : '';
                $value['status'] = isset($all_order_back[$value['thCode']]['status']) ? $all_order_back[$value['thCode']]['status'] : '';
                $value['apply_name'] = isset($all_order_back[$value['thCode']]['apply_name']) ? $all_order_back[$value['thCode']]['apply_name'] : '';
                $value['apply_company'] = isset($all_order_back[$value['thCode']]['apply_company']) ? $all_order_back[$value['thCode']]['apply_company'] : '';
                $value['contactor'] = isset($all_order_back[$value['thCode']]['contactor']) ? $all_order_back[$value['thCode']]['contactor'] : '';
                $value['mobile'] = isset($all_order_back[$value['thCode']]['mobile']) ? $all_order_back[$value['thCode']]['mobile'] : '';
                $value['addr'] = isset($all_order_back[$value['thCode']]['addr']) ? $all_order_back[$value['thCode']]['addr'] : '';
                $value['result'] = isset($all_order_back[$value['thCode']]['result']) ? $all_order_back[$value['thCode']]['result'] : '';
            }

            $list[] = $value;
        }

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

    }

    //【五、退货明细表】
    public function thDataExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => ''], 'post', 'trim');

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

        $data = Db::name('th_data')
            ->alias('thd')
            ->field('thd.id,thd.th_type,thd.addtime 退货时间,thd.thCode 退货单号,"" 发货申请单编码,"" 退货单状态,"" 创建人,"" 部门,thd.orderCode 确认单编号,s.addtime 下单日期,c.companyName 客户名称,s.good_name 产品名称,s.sale_price 单价,s.wsend_num 未发货数量,s.th_num 退货数量,s.th_fee 退货销售货款,"" 发货单联系人,"" 发货单联系人电话,"" 发货单地址,"" 退货原因')
            ->leftJoin('sale s', 's.orderCode=thd.orderCode')
            ->leftJoin('customer_info c', 'c.companyNo=s.customer_code')
            ->where($where)
            ->order('thd.addtime', 'desc')
            ->select()
            ->toArray();

        $th_types = [];
        foreach ($data as $value) {
            $th_types[$value['th_type']][] = $value['退货单号'];
        }

        $all_order_return = Db::name('order_return')
            ->alias('or')
            ->withAttr('退货单状态', function ($val) {
                return isset($this->all_order_return_status[$val]) ? $this->all_order_return_status[$val] : '';
            })
            ->withAttr('部门', function ($val, $da) {
                return get_company_name_by_uid($da['apply_id']);
            })
            ->leftJoin('order_out oo', 'oo.outCode=or.outCode')
            ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where('or.is_del', 0)
            ->whereIn('or.returnCode', $th_types[2])
            ->column('or.id,or.outCode 发货申请单编码,or.status 退货单状态,or.apply_id,or.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'or.returnCode');


        $all_order_back = Db::name('order_back')
            ->alias('ob')
            ->where('ob.is_del', 0)
            ->whereIn('ob.thNo', $th_types[3])
            ->withAttr('退货单状态', function ($val) {
                return isset($this->order_back_status[$val]) ? $this->order_back_status[$val] : '';
            })
            ->withAttr('部门', function ($val, $da) {
                return get_company_name_by_uid($da['apply_id']);
            })
            ->leftJoin('order_out oo', 'oo.outCode=ob.outCode')
            ->leftJoin('order_addr oa', 'oa.id=oo.addrid')
            ->leftJoin('result_info ri', 'ri.result_code=ob.return_code')
            ->column('ob.id,ob.outCode 发货申请单编码,ob.status 退货单状态,ob.apply_id,ob.apply_name 创建人,"" 部门,oa.contactor 发货单联系人,oa.mobile 发货单联系人电话,oa.addr 发货单地址,ri.result 退货原因,oa.addr_code', 'thNo');

        $list = [];

        foreach ($data as $value) {
            if ($value['th_type'] == 2) {
                $value['发货申请单编码'] = isset($all_order_return[$value['退货单号']]['发货申请单编码']) ? $all_order_return[$value['退货单号']]['发货申请单编码'] : '';
                $value['退货单状态'] = isset($all_order_return[$value['退货单号']]['退货单状态']) ? $all_order_return[$value['退货单号']]['退货单状态'] : '';
                $value['创建人'] = isset($all_order_return[$value['退货单号']]['创建人']) ? $all_order_return[$value['退货单号']]['创建人'] : '';
                $value['部门'] = isset($all_order_return[$value['退货单号']]['部门']) ? $all_order_return[$value['退货单号']]['部门'] : '';
                $value['发货单联系人'] = isset($all_order_return[$value['退货单号']]['发货单联系人']) ? $all_order_return[$value['退货单号']]['发货单联系人'] : '';
                $value['发货单联系人电话'] = isset($all_order_return[$value['退货单号']]['发货单联系人电话']) ? $all_order_return[$value['退货单号']]['发货单联系人电话'] : '';
                $value['发货单地址'] = isset($all_order_return[$value['退货单号']]['发货单地址']) ? $all_order_return[$value['退货单号']]['发货单地址'] : '';
                $value['退货原因'] = isset($all_order_return[$value['退货单号']]['退货原因']) ? $all_order_return[$value['退货单号']]['退货原因'] : '';
            } elseif ($value['th_type'] == 3) {
                $value['发货申请单编码'] = isset($all_order_back[$value['退货单号']]['发货申请单编码']) ? $all_order_back[$value['退货单号']]['发货申请单编码'] : '';
                $value['退货单状态'] = isset($all_order_back[$value['退货单号']]['退货单状态']) ? $all_order_back[$value['退货单号']]['退货单状态'] : '';
                $value['创建人'] = isset($all_order_back[$value['退货单号']]['创建人']) ? $all_order_back[$value['退货单号']]['创建人'] : '';
                $value['部门'] = isset($all_order_back[$value['退货单号']]['部门']) ? $all_order_back[$value['退货单号']]['部门'] : '';
                $value['发货单联系人'] = isset($all_order_back[$value['退货单号']]['发货单联系人']) ? $all_order_back[$value['退货单号']]['发货单联系人'] : '';
                $value['发货单联系人电话'] = isset($all_order_back[$value['退货单号']]['发货单联系人电话']) ? $all_order_back[$value['退货单号']]['发货单联系人电话'] : '';
                $value['发货单地址'] = isset($all_order_back[$value['退货单号']]['发货单地址']) ? $all_order_back[$value['退货单号']]['发货单地址'] : '';
                $value['退货原因'] = isset($all_order_back[$value['退货单号']]['退货原因']) ? $all_order_back[$value['退货单号']]['退货原因'] : '';
            }


            unset($value['id']);
            unset($value['th_type']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('退货明细表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【六、售后明细表】
    public function orderReturnDetailed()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

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

        $count = Db::name('order_return')
            ->alias('or')
            ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where($where)
            ->order('or.addtime', 'desc')
            ->count('or.id');

        $data = Db::name('order_return')
            ->alias('or')
            ->field('or.addtime,or.returnCode,or.apply_name,or.apply_id,or.outCode,or.total_num,or.status,or.orderCode,c.companyName,or.good_name,s.sale_price,or.is_receive,or.error_num,ri.result result_desc,or.error_remark,or.except_code,c.itemid')
            ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('or.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            $value['status'] = isset($this->all_order_return_status[$value['status']]) ? $this->all_order_return_status[$value['status']] : '';
            $value['except_code'] = isset($this->except_code[$value['is_receive']][$value['except_code']]) ? $this->except_code[$value['is_receive']][$value['except_code']] : '';
            $value['is_receive'] = isset($this->is_receive[$value['is_receive']]) ? $this->is_receive[$value['is_receive']] : '';
            $value['apply_company'] = get_company_name_by_uid($value['apply_id']);
            $value['error_total_price'] = round($value['error_num'] * $value['sale_price'], 2);
            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['first_customer_org'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['second_customer_org'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }

            $list[] = $value;
        }

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


    }

    //【六、售后明细表】
    public function orderReturnDetailedExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');

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

        $data = Db::name('order_return')
            ->alias('or')
            ->field('or.addtime 售后时间,or.returnCode 售后申请编号,or.apply_name 申请人,or.apply_id,"" 部门,or.outCode 发货申请编号,or.total_num 发货数量,or.status 处理状态,or.orderCode 订单号,"" 一级组织,"" 二级组织,c.companyName 客户名称,or.good_name 商品名称,s.sale_price 销售单价,"" 异常金额,or.is_receive 收货情况,or.error_num 异常数量,ri.result 异常原因,or.error_remark 异常备注,or.except_code 期望意愿,c.itemid')
            ->leftJoin('customer_info c', 'c.companyNo=or.customer_code')
            ->leftJoin('sale s', 's.orderCode=or.orderCode')
            ->leftJoin('result_info ri', 'ri.result_code=or.error_code')
            ->where($where)
            ->order('or.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            $value['处理状态'] = isset($this->all_order_return_status[$value['处理状态']]) ? $this->all_order_return_status[$value['处理状态']] : '';
            $value['部门'] = get_company_name_by_uid($value['apply_id']);
            $value['异常金额'] = round($value['异常数量'] * $value['销售单价'], 2);
            if (!empty($value['itemid'])) {
                $customer_org1 = array_column(get_top_customer_org($value['itemid']), 'name', 'level');
                $value['一级组织'] = isset($customer_org1[1]) ? $customer_org1[1] : '';
                $value['二级组织'] = isset($customer_org1[2]) ? $customer_org1[2] : '';
            }
            $value['期望意愿'] = isset($this->except_code[$value['收货情况']][$value['期望意愿']]) ? $this->except_code[$value['收货情况']][$value['期望意愿']] : '';

            $value['收货情况'] = isset($this->is_receive[$value['收货情况']]) ? $this->is_receive[$value['收货情况']] : '';

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

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('售后明细表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【七、议价明细表】
    public function bargainOrder()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

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

        $count = Db::name('bargain_order')
            ->alias('bo')
            ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
            ->where($where)
            ->order('bo.addtime', 'desc')
            ->count('bo.id');

        $data = Db::name('bargain_order')
            ->alias('bo')
            ->field('bo.addtime,bo.bargainNo,bo.infoNo,bo.status,bo.bidsNo,csi.companyName,ci.good_name,bo.creater,bo.createrid,bo.sale_price,bo.after_price,ri.result result_desc,bo.bargain_remark')
            ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('bo.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            $value['status'] = isset($this->bargain_order_status[$value['status']]) ? $this->bargain_order_status[$value['status']] : '';
            $value['creater_company'] = get_company_name_by_uid($value['createrid']);

            $list[] = $value;
        }

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

    }

    //【七、议价明细表】
    public function bargainOrderExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => ''], 'post', 'trim');

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

        $data = Db::name('bargain_order')
            ->alias('bo')
            ->field('bo.addtime 议价时间,bo.bargainNo 议价编号,bo.infoNo 咨询单编号,bo.status 议价单状态,bo.bidsNo 采返商品编号,csi.companyName 客户名称,ci.good_name 商品名称,bo.creater 申请人,"" 部门,bo.createrid,bo.sale_price 议价前售价,bo.after_price 期望售价,ri.result 议价原因,bo.bargain_remark 议价备注')
            ->leftJoin('consult_info ci', 'ci.infoNo=bo.infoNo')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->leftJoin('customer_info csi', 'csi.companyNo=co.khNo')
            ->leftJoin('result_info ri', 'ri.result_code=bo.result_info_id')
            ->where($where)
            ->order('bo.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            $value['议价单状态'] = isset($this->bargain_order_status[$value['议价单状态']]) ? $this->bargain_order_status[$value['议价单状态']] : '';
            $value['部门'] = get_company_name_by_uid($value['createrid']);

            unset($value['createrid']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('议价明细表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【八、延时发货申请单统计】
    public function orderOutNotAddr()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];

        $count = Db::name('order_out')
            ->alias('a')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("customer_info v", "v.companyNo=b.customer_code")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("business bus", "bus.companyNo=b.supplierNo")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
            ->leftJoin("supplier e", "e.code=d.supplierNo")->where($where)
            ->order("a.addtime desc")
            ->count('a.id');

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.addtime,a.orderCode,os.cgdNo,bus.company,a.apply_name,a.apply_id,a.status,a.send_num,b.good_code,b.good_name,v.companyName,b.wsend_num,b.skuCode,b.order_type,e.person')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("customer_info v", "v.companyNo=b.customer_code")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("business bus", "bus.companyNo=b.supplierNo")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
            ->leftJoin("supplier e", "e.code=d.supplierNo")->where($where)
            ->page($param['page'], $param['size'])
            ->order("a.addtime desc")
            ->cursor();

        $list = $tmp_apply = $tmp_cat = [];
        foreach ($data as $value) {
            if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
            $value['apply_company'] = $tmp_apply[$value['apply_id']];

            if ($value['order_type'] == 3 || $value['order_type'] == 4) {
                $goon = Db::name("good_zixun")
                    ->field('id,cat_id,customized')
                    ->where(["spuCode" => $value['good_code'], "is_del" => 0])
                    ->find();
            } else {
                $goon = Db::name('good_platform')
                    ->alias('a')
                    ->field('a.id,b.cat_id,b.customized')
                    ->join('good b', 'b.spuCode=a.spuCode', 'left')
                    ->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
                    ->find();
            }

            if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
            $value['cat'] = $tmp_cat[$goon['cat_id']];

            $value['send_date'] = date('Y-m-d H:i:s', strtotime($value['addtime']) + $goon['customized'] * 24 * 3600);

            $value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';

            $list[] = $value;
        }

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

    //【八、延时发货申请单统计】
    public function orderOutNotAddrExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.send_type', '=', 2], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==2 延迟发货,a.status==4 已全部退货
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.addtime 下单时间,a.orderCode 订单编号,os.cgdNo 采购单编号,bus.company 公司名称,a.apply_name 申请人,"" 部门,a.apply_id,a.status 状态,a.send_num 出库总数,"" 商品分类,b.good_code 商品编码,b.good_name 商品名称,v.companyName 客户名称,b.wsend_num 未发货数量,"" 应发货日期,b.skuCode,b.order_type 订单类型,e.person 采购员')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("customer_info v", "v.companyNo=b.customer_code")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("business bus", "bus.companyNo=b.supplierNo")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("purchease_order d", "d.cgdNo=os.cgdNo")
            ->leftJoin("supplier e", "e.code=d.supplierNo")
            ->where($where)
            ->order("a.addtime desc")
            ->cursor();

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

            if (!isset($tmp_apply[$value['apply_id']])) $tmp_apply[$value['apply_id']] = get_company_name_by_uid($value['apply_id']);
            $value['部门'] = $tmp_apply[$value['apply_id']];

            if ($value['订单类型'] == 3 || $value['订单类型'] == 4) {
                $goon = Db::name("good_zixun")
                    ->field('id,cat_id,customized')
                    ->where(["spuCode" => $value['商品编码'], "is_del" => 0])
                    ->find();

            } else {
                $goon = Db::name('good_platform')
                    ->alias('a')
                    ->field('a.id,b.cat_id,b.customized')
                    ->join('good b', 'b.spuCode=a.spuCode', 'left')
                    ->where(['a.is_del' => 0, 'a.skuCode' => $value['skuCode']])
                    ->find();
            }

            if (!isset($tmp_cat[$goon['cat_id']])) $tmp_cat[$goon['cat_id']] = implode('/', array_column(made($goon['cat_id']), 'name'));
            $value['商品分类'] = $tmp_cat[$goon['cat_id']];

            $value['应发货日期'] = date('Y-m-d H:i:s', strtotime($value['下单时间']) + $goon['customized'] * 24 * 3600);

            $value['状态'] = isset($this->order_out_status[$value['状态']]) ? $this->order_out_status[$value['状态']] : '';
            $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';

            unset($value['apply_id']);
            unset($value['skuCode']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('延时发货申请单' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【九、直接发货申请单统计 】
    public function orderOutAddr()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'page' => 1, 'size' => 15, 'order_type' => ''], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货

        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];

        $count = Db::name('order_out')
            ->alias('a')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
            ->leftJoin("order_addr oa", "oa.id=a.addrid")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("supplier c", "c.code=po.supplierNo")
            ->where($where)
            ->order("a.addtime desc")
            ->count('a.id');

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.addtime,a.orderCode,a.sendtime,a.outCode,a.status,b.good_name,b.sale_price,a.send_num,a.apply_name,ci.name apply_company,c.person cgder,a.post_name,a.post_code,oa.contactor,oa.mobile,oa.addr,oa.addr_code,a.remark,b.order_type')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
            ->leftJoin("order_addr oa", "oa.id=a.addrid")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("supplier c", "c.code=po.supplierNo")
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order("a.addtime desc")
            ->cursor();

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

            $value['send_total_price'] = round($value['send_num'] * $value['sale_price'], 2);

            $value['status'] = isset($this->order_out_status[$value['status']]) ? $this->order_out_status[$value['status']] : '';

            //拼接省市区
            if (!json_decode($value['addr_code'])) {
                $temp = explode(',', $value['addr_code']);
                $value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
            }

            $value['addr'] = GetAddr($value['addr_code']) . $value['addr'];

            $list[] = $value;
        }

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

    }

    //【九、直接发货申请单统计-导出】
    public function orderOutAddrExport()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'start_sendtime' => '', 'end_sendtime' => '', 'status' => '', 'apply_company' => '', 'order_type' => ''], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.send_type', '=', 1], ['a.send_num', '>', 0], ['a.status', '<>', 4]];//send_type==1 直接发货

        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['start_sendtime'] != '' && $param['end_sendtime'] != '') $where[] = ['a.sendtime', 'between', [$param['start_sendtime'] . ' 00:00:00', $param['end_sendtime'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['b.order_type', '=', $param['order_type']];

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.addtime 下单日期,a.orderCode 订单编号,a.sendtime 发货时间,a.outCode 发货申请单号,a.status 发货申请单状态,b.good_name 商品名称,b.sale_price 产品单价,a.send_num 发货数量,"" 发货申请单总价,a.apply_name 申请人,ci.name 部门,c.person 采购员,a.post_name 快递公司,a.post_code 快递单号,oa.contactor 收货人,oa.mobile 收货电话,oa.addr 收货地址,oa.addr_code,a.remark 备注,b.order_type 订单类型')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin("order_send os", "os.outCode=a.outCode")
            ->leftJoin("purchease_order po", "po.cgdNo=os.cgdNo")
            ->leftJoin("order_addr oa", "oa.id=a.addrid")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->leftJoin("supplier c", "c.code=po.supplierNo")
            ->where($where)
            ->order("a.addtime desc")
            ->cursor();

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

            $value['发货申请单总价'] = round(bcmul($value['发货数量'], $value['产品单价'], 3), 2);

            $value['发货申请单状态'] = isset($this->order_out_status[$value['发货申请单状态']]) ? $this->order_out_status[$value['发货申请单状态']] : '';
            $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';

            //拼接省市区
            if (!json_decode($value['addr_code'])) {
                $temp = explode(',', $value['addr_code']);
                $value['addr_code'] = json_encode(['provice_code' => $temp[0] ?? '', 'city_code' => $temp[1] ?? '', 'area_code' => $temp[2] ?? '']);
            }

            $value['收货地址'] = GetAddr($value['addr_code']) . $value['收货地址'];

            unset($value['addr_code']);

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('直接发货申请单统计' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【十、无地址销售订单】
    public function saleNotAddr()
    {

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

        $where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3]];//send_type==2 延迟发货
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];

        $count = Db::name('sale')
            ->alias('a')
            ->leftJoin("order_addr b", "b.orderCode=a.orderCode AND b.is_del=0")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->group('a.id,a.orderCode,a.order_type,a.good_code,a.skuCode,good_name,a.good_num,a.send_num,a.wsend_num,a.status,u.nickname,ci.name,b.orderCode')
            ->having('a.good_num > SUM(IFNULL(b.receipt_quantity,0)) ')
            ->count('a.id');

        $cgd_sql = Db::name('sale')
            ->alias('a')
            ->field('a.orderCode')
            ->leftJoin("order_addr b", "b.orderCode=a.orderCode AND b.is_del=0")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0 AND u.status=1")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->order("a.addtime desc")
            ->buildSql();

        $cgd = Db::name('order_num')
            ->alias('a')
            ->leftJoin("purchease_order d", "d.cgdNo=a.cgdNo")
            ->leftJoin("supplier e", "e.code=d.supplierNo")
            ->where('a.orderCode IN ' . $cgd_sql)
            ->column('e.person', 'a.orderCode');

        $data = Db::name('sale')
            ->alias('a')
            ->field('a.id,a.orderCode,a.order_type,a.good_code,a.skuCode,good_name,a.good_num,a.send_num,a.wsend_num,a.status,u.nickname,ci.name,(a.good_num - SUM(IFNULL(b.receipt_quantity,0)) ) as  not_addr_address_good_num,"" person')
            ->leftJoin("order_addr b", "b.orderCode=a.orderCode AND b.is_del=0")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->withAttr('person', function ($val, $da) use ($cgd) {
                return $cgd[$da['orderCode']] ?? '';
            })
            ->where($where)
            ->group('a.id,a.orderCode,a.order_type,a.good_code,a.skuCode,good_name,a.good_num,a.send_num,a.wsend_num,a.status,u.nickname,ci.name,b.orderCode')
            ->having('not_addr_address_good_num > 0 ')
            ->page($param['page'], $param['size'])
            ->order("a.addtime desc")
            ->select()
            ->toArray();

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

    }

    //【十、无地址销售订单-导出】
    public function saleNotAddrExport()
    {

        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'apply_company' => '', 'order_type' => ''], 'post', 'trim');

        $where = [['a.send_type', '=', 2], ['a.is_del', '=', 0], ['a.status', '<>', 3]];//send_type==2 延迟发货
        if ($param['start_date'] != '' && $param['end_date'] != '') $where[] = ['a.addtime', 'between', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['order_type'] != '') $where[] = ['a.order_type', '=', $param['order_type']];

        $data = Db::name('sale')
            ->alias('a')
            ->field('a.orderCode 订单编号,a.order_type 订单类型,a.good_code 商品成本编码,a.skuCode 商品上线编码,a.good_name 商品名称,a.good_num 购买数量,a.send_num 已发货数量,a.wsend_num 未发货数量,a.status 订单状态,u.nickname 申请人名称,ci.name 申请人所属部门,(a.good_num - SUM(IFNULL(b.receipt_quantity,0)) ) as  无地址数量,"" 采购员')
            ->leftJoin("order_addr b", "b.orderCode=a.orderCode AND b.is_del=0")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0 AND u.status=1")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->group('a.id,a.orderCode,a.order_type,a.good_code,a.skuCode,a.good_name,a.good_num,a.send_num,a.wsend_num,a.status,u.nickname,ci.name,b.orderCode')
            ->having('无地址数量 > 0')
            ->order("a.addtime desc")
            ->cursor();

        $cgd_sql = Db::name('sale')
            ->alias('a')
            ->field('a.orderCode')
            ->leftJoin("order_addr b", "b.orderCode=a.orderCode AND b.is_del=0")
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0 AND u.status=1")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->order("a.addtime desc")
            ->buildSql();

        $cgd = Db::name('order_num')
            ->alias('a')
            ->leftJoin("purchease_order d", "d.cgdNo=a.cgdNo")
            ->leftJoin("supplier e", "e.code=d.supplierNo")
            ->where('a.orderCode IN ' . $cgd_sql)
            ->column('e.person', 'a.orderCode');

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

            $value['订单类型'] = isset($this->all_order_type[$value['订单类型']]) ? $this->all_order_type[$value['订单类型']] : '';
            $value['订单状态'] = isset($this->all_sale_status[$value['订单状态']]) ? $this->all_sale_status[$value['订单状态']] : '';
            $value['采购员'] = $cgd[$value['订单编号']] ?? '';

            $list[] = $value;
        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('无地址销售订单列表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【十一、销售订单商品类型】
    public function saleMonth()
    {

        $param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

        $where = [['a.is_del', '=', 0]];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('sale')
            ->alias('a')
            ->field('a.id,a.order_type,u.itemid,ci.name')
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->cursor();
        $list = [];
        $total = ['name' => '总计', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_total' => 0];
        foreach ($data as $val) {
            if (!isset($list[$val['itemid']])) $list[$val['itemid']] = [
                'name' => $val['name'],
                'tag_1' => 0,
                'tag_2' => 0,
                'tag_3' => 0,
                'tag_total' => 0,
            ];
            $list[$val['itemid']]['tag_' . $val['order_type']]++;
            $list[$val['itemid']]['tag_total']++;
            $total['tag_' . $val['order_type']]++;
            $total['tag_total']++;

        }
        $list = array_merge($list);//重置下标
        $list[] = $total;
        return app_show(0, '请求成功', $list);

    }

    //【十一、销售订单商品类型-导出】
    public function saleMonthExport()
    {

        $param = $this->request->only(['token', 'apply_company' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

        $where = [['a.is_del', '=', 0]];
        if ($param['apply_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['apply_company'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('sale')
            ->alias('a')
            ->field('a.id,a.order_type,u.itemid,ci.name')
            ->leftJoin("depart_user u", "u.uid=a.apply_id AND u.is_del=0")
            ->leftJoin("company_item ci", "ci.id=u.itemid")
            ->where($where)
            ->cursor();
        $list = [];
        $total = ['部门' => '总计', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
        foreach ($data as $k => $val) {
            if (!isset($list[$val['itemid']])) $list[$val['itemid']] = ['部门' => $val['name'], '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];

            switch ($val['order_type']) {
                case 1:
                    $list[$val['itemid']]['库存品']++;
                    $total['库存品']++;
                    break;
                case 2:
                    $list[$val['itemid']]['非库存品']++;
                    $total['非库存品']++;
                    break;
                case 3:
                    $list[$val['itemid']]['咨询采反']++;
                    $total['咨询采反']++;
                    break;
            }

            $list[$val['itemid']]['总计']++;
            $total['总计']++;

        }
        $list = array_merge($list);//重置下标
        $list[] = $total;

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('销售订单商品类型' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【十二、采购订单发货维度表】
    public function orderOutMonth()
    {

        $param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.id,b.order_type,b.cat_id,d.supplierNo')
            ->leftJoin('sale b', 'b.orderCode=a.orderCode')
            ->leftJoin('order_send c', 'c.outCode=a.outCode')
            ->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
            ->where($where)
            ->cursor();
        $list = [];
//        $total = ['cat_1' => '总计', 'cat_2' => '', 'cat_3' => '', 'tag_1' => 0, 'tag_2' => 0, 'tag_3' => 0, 'tag_supplier' => 0, 'tag_person' => 0, 'tag_logistics' => 0, 'tag_total' => 0];

        $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
            ->group('supplierNo')
            ->column('supplierNo');

        foreach ($data as $val) {

            if (!isset($list[$val['cat_id']])) {
                $cat = made($val['cat_id']);
                $list[$val['cat_id']] = [
                    'cat_1' => $cat[0]['name'] ?? '',
                    'cat_2' => $cat[1]['name'] ?? '',
                    'cat_3' => $cat[2]['name'] ?? '',
                    'tag_1' => 0,
                    'tag_2' => 0,
                    'tag_3' => 0,
                    'tag_supplier' => 0,
                    'tag_person' => 0,
                    'tag_logistics' => 0,
                ];
            }

            switch ($val['order_type']) {
                case 1:
                    //库存品,物流岗发
                    $list[$val['cat_id']]['tag_logistics']++;
                    break;
                case 2: //非库存品
                case 3: //咨询采反
                    if (in_array($val['supplierNo'], $supplierNos)) $list[$val['cat_id']]['tag_supplier']++;
                    else $list[$val['cat_id']]['tag_person']++;
                    break;

            }

            $list[$val['cat_id']]['tag_' . $val['order_type']]++;
//            $list[$val['cat_id']]['tag_total']++;
//            $total['tag_' . $val['order_type']]++;
//            $total['tag_total']++;

        }
        $list = array_merge($list);//重置下标
//        $list[] = $total;
        return app_show(0, '请求成功', $list);

    }

    //【十二、采购订单发货维度表-导出】
    public function orderOutMonthExport()
    {
        $param = $this->request->only(['token', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('order_out')
            ->alias('a')
            ->field('a.id,b.order_type,b.cat_id,d.supplierNo')
            ->leftJoin("sale b", "b.orderCode=a.orderCode")
            ->leftJoin('order_send c', 'c.outCode=a.outCode')
            ->leftJoin('purchease_order d', 'd.cgdNo=c.cgdNo')
            ->where($where)
            ->cursor();

        $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
            ->group('supplierNo')
            ->column('supplierNo');

        $list = [];

//        $total = ['一级分类' => '总计', '二级分类' => '', '三级分类' => '', '库存品' => 0, '非库存品' => 0, '咨询采反' => 0, '总计' => 0];
        foreach ($data as $val) {

            if (!isset($list[$val['cat_id']])) {
                $cat = made($val['cat_id']);
                $list[$val['cat_id']] = [
                    '一级分类' => $cat[0]['name'] ?? '',
                    '二级分类' => $cat[1]['name'] ?? '',
                    '三级分类' => $cat[2]['name'] ?? '',
                    '库存品' => 0,
                    '非库存品' => 0,
                    '咨询采反' => 0,
                    '供应商发货' => 0,
                    '负责人发货' => 0,
                    '物流岗发货' => 0,
//                    '总计' => 0,
                ];
            }


            switch ($val['order_type']) {
                case 1:
                    $list[$val['cat_id']]['库存品']++;
//                    $total['库存品']++;
                    //物流岗发
                    $list[$val['cat_id']]['物流岗发货']++;
                    break;
                case 2:
                    $list[$val['cat_id']]['非库存品']++;
//                    $total['非库存品']++;
                    if (in_array($val['supplierNo'], $supplierNos)) $list[$val['cat_id']]['供应商发货']++;
                    else $list[$val['cat_id']]['负责人发货']++;
                    break;
                case 3:
                    $list[$val['cat_id']]['咨询采反']++;
                    if (in_array($val['supplierNo'], $supplierNos)) $list[$val['cat_id']]['供应商发货']++;
                    else $list[$val['cat_id']]['负责人发货']++;
//                    $total['咨询采反']++;
                    break;
            }

//            $list[$val['cat_id']]['总计']++;
//            $total['总计']++;

        }
        $list = array_merge($list);//重置下标
//        $list[] = $total;

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('采购订单发货维度表' . date('YmdHis'), array_keys($list[0]), $list);

    }

    //【十三、供应商采购订单情况】
    public function purcheaseOrderMonth()
    {
        $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

        $where = [['a.is_del', '=', 0]];
        if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('purchease_order')
            ->alias('a')
            ->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type')
            ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
            ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
            ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
            ->where($where)
            ->cursor();
        $list = $tmp = [];
        $total = ['supplier_name' => '总计', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0];
        foreach ($data as $val) {

            if (!isset($list[$val['supplierNo']])) {
                $list[$val['supplierNo']] = [
                    'supplier_name' => $val['supplier_name'],
                    'total_cgd' => 0,//订单总量
                    'tag_1' => 0,//库存品
                    'tag_2' => 0,//非库存品
                    'tag_c' => 0,//to C
                    'tag_b' => 0,//to B
                    'tag_zx' => 0,//咨询采反总数
                ];
            }

            switch ($val['order_type']) {
                case 1:
                    //库存品
                    if ($val['order_source'] == 0) {
                        $list[$val['supplierNo']]['tag_1']++;
                        $total['tag_1']++;
                    }
                    break;

                case 2:
                    //非库存品
                    $list[$val['supplierNo']]['tag_2']++;
                    $total['tag_2']++;

                case 3:
                    //咨询采反
                    if ($val['order_source'] == 2) {
                        $list[$val['supplierNo']]['tag_zx']++;
                        $total['tag_zx']++;
                    }
                    break;
            }

            switch ($val['use_type']) {
                case 1:
                    //to B
                    $list[$val['supplierNo']]['tag_b']++;
                    $total['tag_b']++;
                    break;

                case 2:
                    //to C
                    $list[$val['supplierNo']]['tag_c']++;
                    $total['tag_c']++;
                    break;
            }

            $list[$val['supplierNo']]['total_cgd']++;
            $total['total_cgd']++;

        }

//        $list = array_merge($list);//重置下标，关联数组，下标是字符，无法通过array_merge方法重置下标

        foreach ($list as $value) {
            $tmp[] = $value;
        }
        $tmp[] = $total;

        return app_show(0, '请求成功', $tmp);

    }

    //【十三、供应商采购订单情况-导出】
    public function purcheaseOrderMonthExport()
    {
        $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

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

        if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        $data = Db::name('purchease_order')
            ->alias('a')
            ->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type')
            ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
            ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
            ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
            ->where($where)
            ->cursor();
        $list = $tmp = [];
        $total = ['采购方' => '总计', '订单总量' => 0, '库存品' => 0, '非库存品' => 0, 'toC' => 0, 'toB' => 0, '反馈单数合计' => 0];
        foreach ($data as $val) {

            if (!isset($list[$val['supplierNo']])) {
                $list[$val['supplierNo']] = [
                    '采购方' => $val['supplier_name'],
                    '订单总量' => 0,//订单总量
                    '库存品' => 0,//库存品
                    '非库存品' => 0,//非库存品
                    'toC' => 0,//to C
                    'toB' => 0,//to B
                    '反馈单数合计' => 0,//咨询采反总数
                ];
            }

            switch ($val['order_type']) {
                case 1:
                    //库存品
                    if ($val['order_source'] == 0) {
                        $list[$val['supplierNo']]['库存品']++;
                        $total['库存品']++;
                    }
                    break;

                case 2:
                    //非库存品
                    $list[$val['supplierNo']]['非库存品']++;
                    $total['非库存品']++;
                    break;

                case 3:
                    //咨询采反
                    if ($val['order_source'] == 2) {
                        $list[$val['supplierNo']]['反馈单数合计']++;
                        $total['反馈单数合计']++;
                    }
                    break;
            }

            switch ($val['use_type']) {
                case 1:
                    //to B
                    $list[$val['supplierNo']]['toB']++;
                    $total['toB']++;
                    break;

                case 2:
                    //to C
                    $list[$val['supplierNo']]['toC']++;
                    $total['toC']++;
                    break;
            }

            $list[$val['supplierNo']]['订单总量']++;
            $total['订单总量']++;

        }

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('供应商采购订单情况' . date('YmdHis'), array_keys($total), $list);

    }

    //【十四、供应商负责人采购订单情况】
    public function purcheaseOrderPersonMonth()
    {
        $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

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

        if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        if ($param['flag'] != '') {
            $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
                ->group('supplierNo')
                ->column('supplierNo');
            if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', $supplierNos];//1.开通账号的
            else $where[] = ['a.supplierNo', 'not in', $supplierNos];//2.未开通账号的
        }

        $data = Db::name('purchease_order')
            ->alias('a')
            ->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type,c.personid,c.person')
            ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
            ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
            ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
            ->leftJoin("supplier c", "c.code=a.supplierNo AND c.is_del=0")
            ->where($where)
            ->cursor();

        $list = [];
        $total = ['person' => '总计', 'total_cgd' => 0, 'tag_1' => 0, 'tag_2' => 0, 'tag_c' => 0, 'tag_b' => 0, 'tag_zx' => 0];
        foreach ($data as $val) {

            if (!isset($list[$val['personid']])) {
                $list[$val['personid']] = [
                    'person' => $val['person'],
                    'total_cgd' => 0,//订单总量
                    'tag_1' => 0,//库存品
                    'tag_2' => 0,//非库存品
                    'tag_c' => 0,//to C
                    'tag_b' => 0,//to B
                    'tag_zx' => 0,//咨询采反总数
                ];
            }

            switch ($val['order_type']) {
                case 1:
                    //库存品
                    if ($val['order_source'] == 0) {
                        $list[$val['personid']]['tag_1']++;
                        $total['tag_1']++;
                    }
                    break;
                case 2:
                    //非库存品
                    $list[$val['personid']]['tag_2']++;
                    $total['tag_2']++;
                    break;

                case 3:
                    //咨询采反
                    if ($val['order_source'] == 2) {
                        $list[$val['personid']]['tag_zx']++;
                        $total['tag_zx']++;
                    }
                    break;
            }

            switch ($val['use_type']) {
                case 1:
                    //to B
                    $list[$val['personid']]['tag_b']++;
                    $total['tag_b']++;
                    break;

                case 2:
                    //to C
                    $list[$val['personid']]['tag_c']++;
                    $total['tag_c']++;
                    break;
            }

            $list[$val['personid']]['total_cgd']++;
            $total['total_cgd']++;

        }

        $list = array_merge($list);//重置下标，关联数组，下标是字符，无法通过array_merge方法重置下标

        $list[] = $total;

        return app_show(0, '请求成功', $list);

    }

    //【十四、供应商负责人采购订单情况-导出】
    public function purcheaseOrderPersonMonthExport()
    {

        $param = $this->request->only(['token', 'supplierNo' => '', 'supplier_name' => '', 'flag' => '', 'start_day' => date('Y-m-01'), 'end_day' => date('Y-m-t')], 'post', 'trim');

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

        if ($param['supplierNo'] != '') $where[] = ['a.supplierNo', 'like', '%' . $param['supplierNo'] . '%'];
        if ($param['supplier_name'] != '') $where[] = ['a.supplier_name', 'like', '%' . $param['supplier_name'] . '%'];
        if ($param['start_day'] != '' && $param['end_day'] != '') $where[] = ['a.addtime', 'between', [$param['start_day'] . ' 00:00:00', $param['end_day'] . ' 23:59:59']];

        if ($param['flag'] != '') {
            $supplierNos = SupplierRelationUser::where(['is_del' => SupplierUser::$is_del_normal, 'status' => SupplierUser::$status_normal])
                ->group('supplierNo')
                ->column('supplierNo');
            if ($param['flag'] == 1) $where[] = ['a.supplierNo', 'in', $supplierNos];//1.开通账号的
            else $where[] = ['a.supplierNo', 'not in', $supplierNos];//2.未开通账号的
        }

        $data = Db::name('purchease_order')
            ->alias('a')
            ->field('a.id,a.supplierNo,a.supplier_name,a.order_type,a.order_source,p.use_type,c.personid,c.person')
            ->leftJoin("order_num b", "b.cgdNo=a.cgdNo")
            ->leftJoin("sale s", "s.orderCode=b.orderCode AND s.is_del=0")
            ->leftJoin("platform p", "p.id=s.platform_id AND p.is_del=0")
            ->leftJoin("supplier c", "c.code=a.supplierNo AND c.is_del=0")
            ->where($where)
            ->cursor();
        $list = [];
        $total = ['采购方' => '总计', '订单总量' => 0, '库存品' => 0, '非库存品' => 0, 'toC' => 0, 'toB' => 0, '反馈单数合计' => 0];
        foreach ($data as $val) {

            if (!isset($list[$val['personid']])) {
                $list[$val['personid']] = [
                    '采购方' => $val['person'],
                    '订单总量' => 0,//订单总量
                    '库存品' => 0,//库存品
                    '非库存品' => 0,//非库存品
                    'toC' => 0,//to C
                    'toB' => 0,//to B
                    '反馈单数合计' => 0,//咨询采反总数
                ];
            }

            switch ($val['order_type']) {
                case 1:
                    //库存品
                    if ($val['order_source'] == 0) {
                        $list[$val['personid']]['库存品']++;
                        $total['库存品']++;
                    }

                    break;

                case 2:
                    //非库存品
                    $list[$val['personid']]['非库存品']++;
                    $total['非库存品']++;
                    break;

                case 3:
                    //咨询采反
                    if ($val['order_source'] == 2) {
                        $list[$val['personid']]['反馈单数合计']++;
                        $total['反馈单数合计']++;
                    }

                    break;
            }

            switch ($val['use_type']) {
                case 1:
                    //to B
                    $list[$val['personid']]['toB']++;
                    $total['toB']++;
                    break;

                case 2:
                    //to C
                    $list[$val['personid']]['toC']++;
                    $total['toC']++;
                    break;
            }

            $list[$val['personid']]['订单总量']++;
            $total['订单总量']++;

        }

        $list[] = $total;

        if (empty($list)) $list[] = ['没有相关可导出的数据'];
        excelSave('供应商负责人采购订单情况' . date('YmdHis'), array_keys($total), $list);

    }


}