phpstock/app/admin/controller/Data.php

<?php

namespace app\admin\controller;

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

//数据统计类(数据看板，获取相关统计数据)
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')
            ->where('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')
            ->field('SUM(sale_total) total_price,COUNT(id) total_order ')
            ->whereDay('ordertime')
            ->where('depart', '网络部')
            ->find();

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

            //重新按照总金额排序
            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')
            ->where('depart', '网络部')
            ->sum('sale_total');
        if (isset($sales_volume['网络部'])) $sales_volume['网络部']['sales_volume'] += $other_network;
        else {
            $sales_volume['网络部'] = [
                'sales_volume' => $other_network,
                'itemid' => -1,
                '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);

    }


    //******* 以下是新版数据大屏 的内容***********************************
    public function dnTodaySale()
    {
        $rs = Db::name('sale')
            ->field('id,good_num,total_price')
            ->where(['is_del' => 0])
            ->where('status', '<>', 3)
            ->whereDay('addtime', 'today')
//            ->fetchSql()->select();
            ->cursor();

        $data = ['total_sale' => 0, 'total_num' => 0, 'total_price' => 0];

        foreach ($rs as $value) {
            $data['total_sale']++;
            $data['total_num'] += $value['good_num'];
            $data['total_price'] += $value['total_price'];
        }

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

    }

}