phpstock/app/admin/controller/Report.php

<?php

namespace app\admin\controller;

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

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

    //【一、采购日报表】1.咨询单总数
    public function zixunTotal()
    {
        $param = $this->request->only([
            'token',
            'start_date' => date('Y-m-d H:i:s'),
            'end_date' => date('Y-m-d H:i:s'),
        ], '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'], $param['end_date']])
            ->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 H:i:s'),
            'end_date' => date('Y-m-d H:i:s'),
        ], '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('po.id,DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,po.cgder_id,po.total_fee,po.good_num,po.status,du.itemid,ci.name')
            ->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)
            ->whereBetween('po.addtime', [$param['start_date'], $param['end_date']])
            ->cursor();

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

            if (!isset($data[$addtime_stamp])) {
                $data[$addtime_stamp] = [
                    'date' => $value['addtime'],//date('Y年m月d日', $addtime_stamp),
                    'list' => [],
                ];
            }

            if (!isset($data[$addtime_stamp]['list'][$value['itemid']])) {
                $data[$addtime_stamp]['list'][$value['itemid']] = [
                    'name' => $value['name'],
                    'total_fee' => 0.00,
                    'good_num' => 0,
                    'wait_total_fee' => 0.00,
                    'wait_good_num' => 0,
                ];
            }
            if ($value['status'] == 0) {
                $data[$addtime_stamp]['list'][$value['itemid']]['wait_total_fee'] += $value['total_fee'];
                $data[$addtime_stamp]['list'][$value['itemid']]['wait_good_num'] += $value['good_num'];
            } else {
                $data[$addtime_stamp]['list'][$value['itemid']]['total_fee'] += $value['total_fee'];
                $data[$addtime_stamp]['list'][$value['itemid']]['good_num'] += $value['good_num'];
            }
        }

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

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

    }

    //【一、采购日报表】3.采购员回复咨询单数
    public function consultBidsSum()
    {
        $param = $this->request->only([
            'token',
            'start_date' => date('Y-m-d H:i:s'),
            'end_date' => date('Y-m-d H:i:s'),
        ], '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('cb.id,DATE_FORMAT(cb.addtime,"%Y-%m-%d") addtime,cb.createrid,cb.creater,c.num,du.itemid,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)
            ->whereBetween('cb.addtime', [$param['start_date'], $param['end_date']])
            ->cursor();

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

            $addtime_stamp = strtotime($value['addtime']);

            if (!isset($data[$addtime_stamp])) {
                $data[$addtime_stamp] = [
                    'date' => $value['addtime'],//date('Y年m月d日', $addtime_stamp),
                    'list' => [],
                ];
            }

            if (!isset($data[$addtime_stamp]['list'][$value['itemid']])) {
                $data[$addtime_stamp]['list'][$value['itemid']] = [
                    'name' => $value['name'],
                    'list' => [],
                ];
            }
            if (!isset($data[$addtime_stamp]['list'][$value['itemid']]['list']['createrid'])) {
                $data[$addtime_stamp]['list'][$value['itemid']]['list'][$value['createrid']] = [
                    'name' => $value['creater'],
                    'order_total' => 0,
                    'good_total' => 0,
                ];
            }

            $data[$addtime_stamp]['list'][$value['itemid']]['list'][$value['createrid']]['order_total']++;
            $data[$addtime_stamp]['list'][$value['itemid']]['list'][$value['createrid']]['good_total'] += $value['num'];

            $total++;

        }

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

        $data[] = ['date' => '汇总', 'list' => ['name' => '', 'list' => ['name' => '', 'order_total' => $total, 'good_total' => 0]]];

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

    }

    //【一、采购日报表】4.采购员订单金额
    public function purcheaseOrderSumByUser()
    {
        $param = $this->request->only([
            'token',
            'start_date' => date('Y-m-d H:i:s'),
            'end_date' => date('Y-m-d H:i:s'),
        ], '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('po.id,DATE_FORMAT(po.addtime,"%Y-%m-%d") addtime,po.cgder_id,po.cgder,po.total_fee,po.good_num,po.status')
            ->where('po.is_del', 0)
            ->whereBetween('po.addtime', [$param['start_date'], $param['end_date']])
            ->cursor();

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

            if (!isset($data[$addtime_stamp])) {
                $data[$addtime_stamp] = [
                    'date' => $value['addtime'],//date('Y年m月d日', $addtime_stamp),
                    'list' => [],
                ];
            }

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

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

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

    }

    //【二、咨询单报表】1.已采反报价信息
    public function consultInfoBidsSum()
    {
        $param = $this->request->only([
            'token',
            'zxNo' => [],
            'start_date' => date('Y-m-d H:i:s'),
            'end_date' => date('Y-m-d H:i:s'),
            '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'], $param['end_date']]);
        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]);

        }

    }


}