phpstock/app/admin/controller/Data.php

<?php

namespace app\admin\controller;

use app\BaseController;
use think\App;
use think\facade\Db;
use think\facade\Validate;

//数据统计类(数据看板，获取相关统计数据)
class Data extends BaseController

{

    //1.今日销量
    public function todaySalesVolume()
    {

        $rs = Db::name('sale')
            ->field('count(id) orders_number,sum(total_price) money')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->find();

        //跨库查询网络部和客服部
        $internet = Db::connect('mysql3')
            ->table('source_all')
            ->field('SUM(sale_total) money,COUNT(id) orders_number')
            ->whereDay('ordertime')
            ->whereIn('depart', ['网络部', '客服部'])
            ->find();

        $temp = [
            'orders_number' => isset($rs['orders_number']) ? (isset($internet['orders_number']) ? $rs['orders_number'] + $internet['orders_number'] : $rs['orders_number']) : 0,
            'money' => isset($rs['money']) ? (isset($internet['money']) ? $rs['money'] + $internet['money'] : $rs['money']) : 0,
        ];

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

    }

    //2.今日销冠
    public function todaySalesChampion()
    {

        $temp = Db::name('sale')
            ->field('count(id) orders,sum(total_price) money,apply_id')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->group('apply_id')
            ->order('money', 'desc')
            ->limit(1)
            ->buildSql();

        $rs = Db::table($temp)
            ->alias('t')
            ->field('t.apply_id,t.orders,t.money,u.itemid company_id,	c.`name` company,	u.nickname')
            ->leftJoin('depart_user u', 'u.uid=t.apply_id AND u.is_del=0')
            ->leftJoin('company_item c', 'c.id=u.itemid AND c.is_del=0')
            ->select()
            ->toArray();

        return app_show(0, '请求成功', empty($rs[0]) ? [] : $rs[0]);

    }

    //3.今日采购
    public function todayPurchase()
    {

        $rs = Db::name('purchease_order')
            ->field('count(id) orders_number,sum(good_num) good_num')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->find();

        $consult_info_total = Db::name('consult_info')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->count('id');

        $consult_bids_total = Db::name('consult_bids')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->count('id');

        $data = [
            'orders_number' => isset($rs['orders_number']) ? $rs['orders_number'] : 0,
            'good_num' => isset($rs['good_num']) ? $rs['good_num'] : 0,
            'consult_info_total' => $consult_info_total,
            'consult_bids_total' => $consult_bids_total,
        ];

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

    }

    //4.票(相关，暂不做)

    //5.竞价单和采购单
    public function totalZixunPurchease()
    {

        //招标单数(咨询单数)
        $zixun_total = Db::name('consult_info')
            ->where(['is_del' => 0])
            ->count('id');

        //已反馈单数,
        $sql = Db::name('consult_info')
            ->field('zxNo')
            ->where(['is_del' => 0, 'status' => 1])
            ->buildSql();

        //正在进行中的咨询单(招标工作台上的数据)
        $zixun_num_ing = Db::name('consult_info')
            ->field('zxNo')
            ->where(['is_del' => 0, 'status' => 1])
            ->count('id');

        //已反馈的单数
        $temp_sql = Db::name('consult_bids')
            ->field('zxNo')
            ->where('zxNo IN ' . $sql)
            ->group('zxNo')
            ->buildSql();
        $not_feedback = Db::table($temp_sql)
            ->alias('t')
            ->count('t.zxNo');

        //未竞价单数(未反馈单数) = 进行中的单数-已反馈单数
        $not_feedback = $zixun_num_ing - $not_feedback;

        //采购单数
        $purchease_total = Db::name('purchease_order')
            ->where('is_del', 0)
            ->count('id');

        //采购单未下单数(状态为待与供应商确认)
        $purchease_wait_confirm = Db::name('purchease_order')
            ->where(['is_del' => 0, 'status' => 0])
            ->count('id');

        //采购单 采购下单数？？？？？？？

        return app_show(0, '请求成功', [
            'zixun_total' => $zixun_total,
            'not_feedback' => $not_feedback,
            'purchease_total' => $purchease_total,
            'purchease_wait_confirm' => $purchease_wait_confirm,
        ]);


    }

    //6.未发货
    public function waitSendTotal()
    {
        $rs = Db::name('sale')
            ->field('count(id) order_num,sum(wsend_num) wsend_num')
            ->where(['is_del' => 0, 'status' => 1])//status==1 待发货
            ->find();
        return app_show(0, '请求成功', $rs);
    }

    //7.今日订单
    public function todaySale()
    {

        $temp = Db::name('sale')
            ->alias('s')
            ->field('s.id,s.apply_id,s.total_price,u.nickname,u.itemid')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id AND u.is_del=0')
            ->where('s.is_del', 0)
            ->whereDay('s.addtime', 'today')
            ->buildSql();

        $rs = Db::table($temp)
            ->alias('t')
            ->field('itemid companyId,c.`name` companyName,SUM(t.total_price) total_price,COUNT(t.id) total_order')
            ->leftJoin('company_item c', 'c.id=t.itemid')
            ->group('t.itemid')
            ->order('total_price', 'desc')
            ->column('itemid companyId,c.`name` companyName,SUM(t.total_price) total_price,COUNT(t.id) total_order', 'name');

        //跨库添加网络部数据和客服部数据
        $internet = Db::connect('mysql3')
            ->table('source_all')
            ->whereDay('ordertime')
            ->group('depart')
            ->whereIn('depart', ['网络部', '客服部'])
            ->column('depart,SUM(sale_total) total_price,COUNT(id) total_order ', 'depart');

        if (!empty($internet)) {
            if (isset($rs['网络部'])) {
                $rs['网络部']['total_price'] += $internet['网络部']['total_price'];
                $rs['网络部']['total_order'] += $internet['网络部']['total_order'];
            } else {
                $rs[] = [
                    'companyId' => '',
                    'companyName' => '网络部',
                    'total_price' => $internet['网络部']['total_price'],
                    'total_order' => $internet['网络部']['total_order'],
                ];
            }

            if (isset($rs['客服部'])) {
                $rs['客服部']['total_price'] += $internet['客服部']['total_price'];
                $rs['客服部']['total_order'] += $internet['客服部']['total_order'];
            } else {
                $rs[] = [
                    'companyId' => '',
                    'companyName' => '客服部',
                    'total_price' => $internet['客服部']['total_price'],
                    'total_order' => $internet['客服部']['total_order'],
                ];
            }


            //重新按照总金额排序
            usort($rs, function ($left, $right) {
                return ($left['total_price'] > $right['total_price']) ? -1 : 1;
            });

        }

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

    }

    //8.本月完成率(数据在结算库里,部门完成率=部门净销售额 / 部门销售指标)
    //没有销售指标的部门，不纳入统计范围
    public function monthFinishRate()
    {
        //部门净销售额
        $sales_volume = Db::name('sale')
            ->alias('s')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id AND u.is_del=0')
            ->leftJoin('company_item c', 'c.id=u.itemid AND c.is_del=0')
            ->group('u.itemid')
            ->whereMonth('s.addtime')
            ->column('(sum(s.total_price) - sum(s.th_fee)) sales_volume,u.itemid,c.name', 'c.name');

        //额外把网络部和客服部的销售数据跨数据库查询出来
        $other_network = Db::connect('mysql3')
            ->table('source_all')
            ->whereMonth('ordertime')
            ->whereIn('depart', ['网络部', '客服部'])
            ->group('depart')
            ->column('SUM(sale_total) sale_total', 'depart');

        if (isset($other_network['网络部'])) {
            if (isset($sales_volume['网络部'])) $sales_volume['网络部']['sales_volume'] += $other_network['网络部'];
            else {
                $sales_volume['网络部'] = [
                    'sales_volume' => $other_network['网络部'],
                    'itemid' => -1,
                    'name' => '网络部'
                ];
            }
        }

        if (isset($other_network['客服部'])) {
            if (isset($sales_volume['客服部'])) $sales_volume['客服部']['sales_volume'] += $other_network['客服部'];
            else {
                $sales_volume['客服部'] = [
                    'sales_volume' => $other_network['客服部'],
                    'itemid' => -2,
                    'name' => '客服部'
                ];
            }
        }
        
        //部门销售指标
        $sale_indicators = Db::name('depart_tips')
            ->field('id,total_tips,depart_item department')
            ->where(['year' => date('Y'), 'month' => date('n')])
            ->select()
            ->toArray();

        $da = [];
        //计算完成率
        foreach ($sale_indicators as $value) {
            if (isset($sales_volume[$value['department']]['sales_volume'])) {
                $value['finish'] = $sales_volume[$value['department']]['sales_volume'];
                $value['finish_rate'] = round(($value['finish'] / $value['total_tips']) * 100, 5);
                $da[] = $value;
            } else continue;
        }

        //按照完成率排序
        usort($da, function ($left, $right) {
            return ($left['finish_rate'] > $right['finish_rate']) ? -1 : 1;
        });

        //计算汇总完成率
        $total_finish_rate = round((array_sum(array_column($sales_volume, 'sales_volume')) / array_sum(array_column($da, 'total_tips'))) * 100, 2);

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

    }

    //9.转单率-今日
    public function orderTransferRateToday()
    {
        $consulting = Db::name('sale')
            ->alias('s')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id AND u.is_del=0')
            ->where(['s.order_type' => 3, 's.is_del' => 0])//order_type==3 咨询采反
            ->whereDay('s.addtime')
            ->group('u.itemid')
            ->column('count(s.id) consulting', 'u.itemid');

        $rs = Db::name('consult_order')
            ->alias('c')
            ->field('count(c.id) total,c.depart companyId,i.name companyName')
            ->leftJoin('company_item i', 'i.id=c.depart AND i.is_del=0')
            ->where(['c.is_del' => 0])
            ->whereDay('c.addtime')
            ->group('c.depart')
            ->append(['transfer_rate'])
            ->withAttr('transfer_rate', function ($val, $data) use ($consulting) {
                $consult = isset($consulting[$data['companyId']]) ? $consulting[$data['companyId']] : 0;
                return round(($consult / $data['total']) * 100, 2);
            })
            ->select()
            ->toArray();

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

    }

    //9.转单率-本月
    public function orderTransferRateMonth()
    {
        $consulting = Db::name('sale')
            ->alias('s')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id AND u.is_del=0')
            ->where(['s.order_type' => 3, 's.is_del' => 0])//order_type==3 咨询采反
            ->whereMonth('s.addtime')
            ->group('u.itemid')
            ->column('count(s.id) consulting', 'u.itemid');

        $rs = Db::name('consult_order')
            ->alias('c')
            ->field('count(c.id) total,c.depart companyId,i.name companyName')
            ->leftJoin('company_item i', 'i.id=c.depart AND i.is_del=0')
            ->where(['c.is_del' => 0])
            ->whereMonth('c.addtime')
            ->group('c.depart')
            ->append(['transfer_rate'])
            ->withAttr('transfer_rate', function ($val, $data) use ($consulting) {
                $consult = isset($consulting[$data['companyId']]) ? $consulting[$data['companyId']] : 0;
                return round(($consult / $data['total']) * 100, 2);
            })
            ->select()
            ->toArray();

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

    }

    //9.转单率-今年
    public function orderTransferRateYear()
    {

        $consulting = Db::name('sale')
            ->alias('s')
            ->leftJoin('depart_user u', 'u.uid=s.apply_id AND u.is_del=0')
            ->where(['s.order_type' => 3, 's.is_del' => 0])//order_type==3 咨询采反
            ->whereYear('s.addtime', date('Y'))
            ->group('u.itemid')
            ->column('count(s.id) consulting', 'u.itemid');

        $rs = Db::name('consult_order')
            ->alias('c')
            ->field('count(c.id) total,c.depart companyId,i.name companyName')
            ->leftJoin('company_item i', 'i.id=c.depart AND i.is_del=0')
            ->where(['c.is_del' => 0])
            ->whereYear('c.addtime', date('Y'))
            ->group('c.depart')
            ->append(['transfer_rate'])
            ->withAttr('transfer_rate', function ($val, $data) use ($consulting) {
                $consult = isset($consulting[$data['companyId']]) ? $consulting[$data['companyId']] : 0;
                return round(($consult / $data['total']) * 100, 2);
            })
            ->select()
            ->toArray();

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

    }


    //******* 以下是新版数据大屏 的内容***********************************


    //1.今日销售
    public function dnTodaySale()
    {
        $rs = Db::name('sale')
            ->field('count(id) orders_number,sum(total_price)-sum(th_fee) money,sum(good_num)-sum(th_num) good_num ')
            ->where('is_del', 0)
            ->whereDay('addtime', 'today')
            ->find();

        //跨库查询网络部和客服部
        $internet = Db::connect('mysql3')
            ->table('source_all')
            ->field('SUM(sale_total) money,SUM(order_num) good_num,COUNT(id) orders_number')
            ->whereDay('ordertime')
            ->whereIn('depart', ['网络部', '客服部'])
            ->find();

        //组织数据
        $data = [
            'orders_number' => bcadd(isset($rs['orders_number']) ? $rs['orders_number'] : '0', isset($internet['orders_number']) ? $internet['orders_number'] : '0'),
            'money' => bcadd(isset($rs['money']) ? $rs['money'] : '0', isset($internet['money']) ? $internet['money'] : '0', 2),
            'good_num' => bcadd(isset($rs['good_num']) ? $rs['good_num'] : '0', isset($internet['good_num']) ? $internet['good_num'] : '0', 2),
        ];

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

    }

    //2.今日采购
    public function dnTodayPurcheaseOrder()
    {

        $rs = Db::name('purchease_order')
            ->field('count(id) orders_number,sum(good_num) good_num')
            ->where('is_del', 0)
            ->whereDay('addtime')
            ->find();

        $consult_info_total = Db::name('consult_info')
            ->where(['is_del' => 0, 'status' => 5])//status==5成功转单
            ->whereDay('updatetime')
            ->count('id');

        $consult_bids_total = Db::name('consult_bids')
            ->where('is_del', 0)
            ->whereDay('addtime')
            ->count('id');

        $data = [
            'consult_info_total' => $consult_info_total,//竞价中标数
            'consult_bids_total' => $consult_bids_total,//反馈商品数
            'orders_number' => isset($rs['orders_number']) ? $rs['orders_number'] : 0,//采购订单
            'good_num' => isset($rs['good_num']) ? $rs['good_num'] : 0,//商品数量
        ];

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

    //3.今日结算

    //4.销售转单数
    public function dnTodayTransferOrder()
    {

        $param = $this->request->filter('trim')->only(['itemid' => '0', 'type' => '3', 'date' => date('Y-m-d')], 'post');

        $val = Validate::rule([
            'itemid|部门id' => 'require|integer',
            'type|日期类型' => 'require|number|in:1,2,3',
            'date|筛选日期' => 'require|date',
        ]);

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

        $rs = Db::name('consult_info')
            ->alias('a')
            ->leftJoin('consult_order b', 'b.zxNo=a.zxNo AND b.is_del=0')
            ->leftJoin('depart_user u', 'u.uid=b.saleid AND u.is_del=0')
            ->where(['a.is_del' => 0, 'bargain_num' => 1]);

        //查询符合条件的竞价单
        if ($param['itemid'] == '0') {
            //以部门为维度
            $rs = $rs
                ->field('a.id,a.status,u.itemid total_id,c.name')
                ->leftJoin('company_item c', 'c.id=u.itemid AND c.is_del=0');
        } else {
            //以部门下的人为维度
            $rs = $rs
                ->field('a.id,a.status,b.saleid total_id,b.salesman name')
                ->where('u.itemid', $param['itemid']);
        }

        //时间段判断
        switch ($param['type']) {
            case '1':
                $rs->whereYear('a.addtime', $param['date']);
                break;
            case '2':
                $rs->whereMonth('a.addtime', $param['date']);
                break;
            default:
                $rs->whereDay('a.addtime', $param['date']);
                break;
        }

        $data = $rs->cursor();

        $da = [];

        //组织数据
        foreach ($data as $value) {
            if (!isset($da[$value['total_id']])) {
                $da[$value['total_id']] = [
                    'finish_total' => 0,//销售单(中标单数)
                    'total' => 0,//竞价单(总单数)
                    'name' => $value['name']//名称
                ];
            }

            $da[$value['total_id']]['total']++;
            if ($value['status'] == '5') $da[$value['total_id']]['finish_total']++;
        }

        //计算转单率
        foreach ($da as &$val) {
            $val['finish_rate'] = bcmul(round(bcdiv($val['finish_total'], $val['total'], 5), 4), '100', 2) . '%';
        }

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

    }

}