phpstock/app/admin/controller/Report.php

<?php

namespace app\admin\controller;

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

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

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

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

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

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

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

        $data = [];

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

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

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

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

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

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

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

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

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

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

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

        if (empty($data)) return error_show(1005, '没有可供导出的数据');
        else excelSaveBatch($data);

    }

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

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

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

        $rs = Db::name('good_zixun')
            ->alias('gz')
            ->field('gz.id,DATE_FORMAT(gz.addtime,"%Y-%m-%d") addtime,gz.createrid,du.itemid,ci.name')
            ->leftJoin('depart_user du', 'du.uid=gz.createrid AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where('gz.is_del', 0)
            ->whereBetween('gz.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
            ->cursor();

        $data = [];
        foreach ($rs as $value) {
            $addtime_stamp = strtotime($value['addtime']);

            if (!isset($data[$addtime_stamp])) $data[$addtime_stamp] = [
                'date' => $value['addtime'],
                'list' => [],
            ];

            if (isset($data[$addtime_stamp]['list'][$value['itemid']])) {
                $data[$addtime_stamp]['list'][$value['itemid']]['total']++;
            } else {
                $data[$addtime_stamp]['list'][$value['itemid']] = [
                    'total' => 1,
                    'name' => $value['name'],
                ];
            }
        }

        //去掉下标
        foreach ($data as &$v) {
            $v['list'] = array_merge($v['list']);
        }

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

    }

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

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

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

        $rs = Db::name('purchease_order')
            ->alias('po')
            ->field('DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,SUM(po.total_fee) total_fee,SUM(po.good_num) good_num,po.status,du.itemid,ci.name,"" wait_total_fee,"" wait_good_num')
            ->leftJoin('depart_user du', 'du.uid=po.cgder_id AND du.is_del=0')
            ->leftJoin('company_item ci', 'ci.id=du.itemid AND ci.is_del=0')
            ->where('po.is_del', 0)
            ->group('addtime,du.itemid,po.status')
            ->order('addtime,itemid')
            ->whereBetween('po.addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
            ->cursor();

        $data = [];
        foreach ($rs as $value) {
            if (!isset($data[$value['addtime']][$value['itemid']])) {
                $data[$value['addtime']][$value['itemid']] = [
                    'addtime' => $value['addtime'],
                    'itemid' => $value['itemid'],
                    'name' => $value['name'],
                    'total_fee' => 0,
                    'good_num' => 0,
                    'wait_total_fee' => 0,
                    'wait_good_num' => 0,
                ];
            }

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

        }

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

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

    }

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

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

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

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

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

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

    }

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

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

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

        $rs = Db::name('purchease_order')
            ->field('DATE_FORMAT(addtime, "%Y-%m-%d") addtime,cgder_id,cgder,SUM(total_fee) total_fee,SUM(good_num) good_num,status,0 wait_total_fee,0 wait_good_num')
            ->where('is_del', 0)
            ->group('addtime,cgder_id,cgder,status')
            ->order('addtime,cgder_id')
            ->whereBetween('addtime', [$param['start_date'] . ' 00:00:00', $param['end_date'] . ' 23:59:59'])
            ->cursor();

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

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

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

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

    }

    //【二、咨询单报表】1.已采反报价信息
    public function consultInfoBidsSum()
    {
        $param = $this->request->only([
            'token',
            'zxNo' => [],
            'start_date' => date('Y-m-d'),
            'end_date' => date('Y-m-d'),
            'page' => 1,
            'size' => 15,
            'is_export' => 0,//是否导出，1导出，0不导出
        ], 'post', 'trim');

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

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

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

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

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

            if (empty($data)) return error_show(1005, '没有可供导出的数据');
            else {
                $headerArr = array_keys($data[0]);
                excelSave('咨询单报表-已采反报价信息' . date('YmdHis'), $headerArr, $data);
            }

        } else {

            $total = $rs->count('cb.id');

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

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

        }

    }

    //【二、咨询单报表】2.未采反信息
    public function consultInfoBidsSumNot()
    {
        $param = $this->request->only([
            'token',
            'companyName' => '',
            'start_date' => date('Y-m-d'),
            'end_date' => date('Y-m-d'),
            'page' => 1,
            'size' => 15,
            'is_export' => 0,//是否导出，1导出，0不导出
        ], 'post', 'trim');

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

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

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

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

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

            if (empty($data)) return error_show(1005, '没有可供导出的数据');
            else {
                $headerArr = array_keys($data[0]);
                excelSave('咨询单报表-未采反信息' . date('YmdHis'), $headerArr, $data);
            }

        } else {

            $total = $rs->count('cb.id');

            $data = $rs
                ->field('cb.zxNo,cb.good_name,ci.num,csi.companyName,ci.addtime,co.endtime')
                ->page($param['page'], $param['size'])
                ->select()
                ->toArray();

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

        }

    }

    //【三、订单明细报表】
    public function orderListDetailed()
    {
        $param = $this->request->only([
            'token',
            'start_date' => date('Y-m-d'),
            'end_date' => date('Y-m-d'),
            'page' => 1,
            'size' => 15,
            'is_export' => 0,//是否导出，1导出，0不导出
        ], 'post', 'trim');

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

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

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

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

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

            if (empty($data)) return error_show(1005, '没有可供导出的数据');
            else {
                $headerArr = array_keys($data[0]);
                excelSave('咨询单报表-未采反信息' . date('YmdHis'), $headerArr, $data);
            }

        } else {

            $total = $rs->count('po.id');

            $data = $rs
                ->field('po.,po.,po.,po.,po.,po.,po.,po.')
                ->page($param['page'], $param['size'])
                ->select()
                ->toArray();

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

        }

    }


}