phpstock/app/admin/controller/SaleReport.php

<?php

namespace app\admin\controller;

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

//销售报表处理类
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 $is_act = [0 => '否', 1 => '是'];//是否接受工差
    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 => '不修改销售额'];


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

        $rs_1 = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->where('ci.is_del', 0)
            ->whereBetween('ci.addtime', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59'])
            ->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['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')], 'post', 'trim');

        $rs_1 = Db::name('consult_info')
            ->alias('ci')
            ->leftJoin('consult_order co', 'co.zxNo=ci.zxNo')
            ->where('ci.is_del', 0)
            ->whereBetween('ci.addtime', [$param['date'] . ' 00:00:00', $param['date'] . ' 23:59:59'])
            ->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')
            ->where($where)
            ->order('ci.addtime', 'desc')
            ->count('ci.id');

        $data = Db::name('consult_info')
            ->alias('ci')
            ->field('co.creater,ci.addtime,ci.zxNo,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 = [];
        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'])) {
                $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['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.zxNo 咨询单号,"" 一级企业,"" 二级企业,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 = [];
        foreach ($data as $value) {
            $value['创建人所属部门'] = get_company_name_by_uid($value['createrid']);//获取创建人的所属部门
            $value['金额'] = round($value['单价'] * $value['数量'], 2);
            $value['是否采反'] = is_null($value['是否采反']) ? '否' : '是';
            $value['是否转单'] = $value['是否转单'] == 5 ? '是' : '否';//5成功转单

            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] : '';
            }

            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' => 1], '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')
            ->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')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_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] : '';
            }

            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_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']);//获取创建人的所属部门

            $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')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_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['订单类型'] == 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['订单类型']] : '';

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

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

            $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' => 1], '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']];
        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')
            ->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')
            ->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['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['creater_company'] = get_company_name_by_uid($value['apply_id']);//获取创建人的所属部门

            $list[] = $value;
        }

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

    }

    //【一、管理报表】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]];
        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')
            ->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']);//获取创建人的所属部门

            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 = [['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']];

        $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')
            ->where($where)
            ->count('oo.id');

        $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)
            ->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['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['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();

        $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;
        }

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

    }

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

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

        $where = [];
        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['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 = [];
        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['异常状态'] = 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' => 1], '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 saleDetailed()
    {
        $param = $this->request->only(['token', 'start_date' => '', 'end_date' => '', 'status' => '', 'create_company' => '', 'company' => '', 'page' => 1, 'size' => 15], 'post', 'trim');

        $where = [['s.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[] = ['s.status', '=', $param['status']];
        if ($param['create_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['create_company'] . '%'];
        if ($param['company'] != '') $where[] = ['b.company', 'like', '%' . $param['company'] . '%'];

        $count = Db::name('sale')
            ->alias('s')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('business b', 'b.companyNo=s.supplierNo')
            ->leftJoin('platform p', 'p.id=s.platform_id')
            ->leftJoin('good_basic gb', 'gb.spuCode=s.good_code')
            ->leftJoin('order_use ou', 'ou.id=s.use_order')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id')
            ->leftJoin('company_item ci', 'ci.id=u.itemid')
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->count('s.id');

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.addtime,s.orderCode,s.order_type,s.status,s.apply_name,ci.name,b.company,csi.companyName,p.platform_name,s.platform_order,s.poNo,s.paytime,ou.order_use,s.good_name,gb.tax,s.sale_price,s.good_num,s.total_price,po.cgder,gb.customized,s.arrive_time,s.remark,s.is_stock,csi.itemid,s.cat_id,s.is_activity,s.good_type')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('business b', 'b.companyNo=s.supplierNo')
            ->leftJoin('platform p', 'p.id=s.platform_id')
            ->leftJoin('good_basic gb', 'gb.spuCode=s.good_code')
            ->leftJoin('order_use ou', 'ou.id=s.use_order')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id')
            ->leftJoin('company_item ci', 'ci.id=u.itemid')
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->page($param['page'], $param['size'])
            ->cursor();

        $list = [];

        foreach ($data as $value) {

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

            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['is_stock'] = isset($this->all_stock[$value['is_stock']]) ? $this->all_stock[$value['is_stock']] : '';

            $is_activity = isset($this->is_activity[$value['is_activity']]) ? $this->is_activity[$value['is_activity']] : '';
            $good_type = isset($this->good_type[$value['good_type']]) ? $this->good_type[$value['good_type']] : '';
            $value['activity_type'] = $is_activity . '/' . $good_type;

            $list[] = $value;
        }

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

    }

    //【二、订单导出表】
    public function saleDetailedExport()
    {

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

        $where = [['s.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[] = ['s.status', '=', $param['status']];
        if ($param['create_company'] != '') $where[] = ['ci.name', 'like', '%' . $param['create_company'] . '%'];
        if ($param['company'] != '') $where[] = ['b.company', 'like', '%' . $param['company'] . '%'];

        $data = Db::name('sale')
            ->alias('s')
            ->field('s.addtime 确认单下单时间,s.orderCode 订单编号,s.order_type 订单类型,s.status 订单状态,s.apply_name 创建人,ci.name 部门,b.company 销售方公司,"" 一级组织,"" 二级组织,csi.companyName 购买方公司,p.platform_name 所属平台,s.platform_order 平台订单号,s.poNo 其他单号,s.paytime 承诺回款时间,ou.order_use 订单用途,"" 活动类型,s.good_name 产品名称,"" 一级分类,gb.tax 税率,s.sale_price 销售单价,s.good_num 下单数量,s.total_price 销售总额,po.cgder 采购人,gb.customized 工期时间,s.arrive_time 要求到货时间,s.remark 备注,s.is_stock 是否库存品,csi.itemid,s.cat_id,s.is_activity,s.good_type')
            ->leftJoin('customer_info csi', 'csi.companyNo=s.customer_code')
            ->leftJoin('business b', 'b.companyNo=s.supplierNo')
            ->leftJoin('platform p', 'p.id=s.platform_id')
            ->leftJoin('good_basic gb', 'gb.spuCode=s.good_code')
            ->leftJoin('order_use ou', 'ou.id=s.use_order')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id')
            ->leftJoin('company_item ci', 'ci.id=u.itemid')
            ->leftJoin('order_num on', 'on.orderCode=s.orderCode')
            ->leftJoin('purchease_order po', 'po.cgdNo=on.cgdNo')
            ->where($where)
            ->order('s.addtime', 'desc')
            ->cursor();

        $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['订单状态']] : '';

            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['是否库存品'] = isset($this->all_stock[$value['是否库存品']]) ? $this->all_stock[$value['是否库存品']] : '';

            $is_activity = isset($this->is_activity[$value['is_activity']]) ? $this->is_activity[$value['is_activity']] : '';
            $good_type = isset($this->good_type[$value['good_type']]) ? $this->good_type[$value['good_type']] : '';
            $value['活动类型'] = $is_activity . '/' . $good_type;
            unset($value['good_code']);
            unset($value['itemid']);
            unset($value['cat_id']);
            unset($value['is_activity']);
            unset($value['good_type']);

            $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('sale')
            ->alias('s')
            ->leftJoin('consult_bids cb', 'cb.bidNo=s.zxNo')
            ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('company_item item', 'item.id=co.depart')
            ->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('sale')
            ->alias('s')
            ->field('ci.addtime,co.salesman,item.name,cb.zxNo,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')
            ->leftJoin('consult_bids cb', 'cb.bidNo=s.zxNo')
            ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('company_item item', 'item.id=co.depart')
            ->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 = [['s.order_type', '=', 3], ['s.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', '=', $param['use_desc']];

        $data = Db::name('sale')
            ->alias('s')
            ->field('ci.addtime 咨询时间,co.salesman 业务员,item.name 业务部门,cb.zxNo 咨询单号,"" 一级组织,"" 二级组织,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')
            ->leftJoin('consult_bids cb', 'cb.bidNo=s.zxNo')
            ->leftJoin('consult_info ci', 'ci.zxNo=cb.zxNo')
            ->leftJoin('consult_order co', 'co.zxNo=cb.zxNo')
            ->leftJoin('company_item item', 'item.id=co.depart')
            ->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('business b', 'b.companyNo=po.companyNo')
            ->leftJoin('good_basic gb', 'gb.spuCode=po.spuCode')
            ->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,b.company,gb.noble_metal,pd.good_num,pd.sale_price,pd.good_weight,pd.diff_weight,pd.diff_price,sd.status')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('business b', 'b.companyNo=po.companyNo')
            ->leftJoin('good_basic gb', 'gb.spuCode=po.spuCode')
            ->where($where)
            ->page($param['page'], $param['size'])
            ->order('pd.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

            $value['apply_company'] = get_company_name_by_uid($value['apply_id']);
            $value['noble_metal'] = isset($this->noble_metal[$value['noble_metal']]) ? $this->noble_metal[$value['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 产品名称,b.company 客户名称,gb.noble_metal 贵金属种类,pd.good_num 下单数量,pd.sale_price 单价,"" 总额,pd.good_weight 商品总重量,pd.diff_weight 工差重量,pd.diff_price 销售工差总费用,sd.status 是否修改销售额')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('business b', 'b.companyNo=po.companyNo')
            ->leftJoin('good_basic gb', 'gb.spuCode=po.spuCode')
            ->where($where)
            ->order('pd.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

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

            unset($value['apply_id']);

            $list[] = $value;
        }

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

    }

    //【五、退货明细表】
    public function thDataExport()
    {
        $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('th_data')
            ->alias('pd')
            ->field('pd.addtime 退货时间,pd. 退货单号')
//            ->field('pd.addtime 发起时间,sd.diffCode 工差单号,sd.orderCode 关联确认单,pd.apply_name 创建人,"" 部门,pd.apply_id,po.addtime 确认单下单日期,pd.good_name 产品名称,b.company 客户名称,gb.noble_metal 贵金属种类,pd.good_num 下单数量,pd.sale_price 单价,"" 总额,pd.good_weight 商品总重量,pd.diff_weight 工差重量,pd.diff_price 销售工差总费用,sd.is_act 是否修改销售额')
            ->leftJoin('sale_diff sd', 'sd.cgd_diffid=pd.id')
            ->leftJoin('purchease_order po', 'po.cgdNo=pd.cgdNo')
            ->leftJoin('business b', 'b.companyNo=po.companyNo')
            ->leftJoin('good_basic gb', 'gb.spuCode=po.spuCode')
            ->where($where)
            ->order('pd.addtime', 'desc')
            ->cursor();

        $list = [];

        foreach ($data as $value) {

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

            unset($value['apply_id']);

            $list[] = $value;
        }

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

    }


}