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

{

    /**
     * public function index()
     * {
     * $data = [];
     *
     * //        //1.今日销量(单数、金额)
     * //        $today_sales_volume = Db::name('sale')
     * //            ->field('count(id) orders_number,sum(total_price) money')
     * //            ->where('is_del',0)
     * //            ->whereDay('addtime','today')
     * //            ->find();
     * //
     * //        $data['today_sales_volume'] = [
     * //            'orders_number' => isset($today_sales_volume['orders_number'])?$today_sales_volume['orders_number']:0,
     * //            'money' =>  isset($today_sales_volume['money'])?$today_sales_volume['money']:0,
     * //        ];
     *
     * //2.今日销冠(部门、姓名、单数、金额)
     * $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();
     *
     * $data['today_sales_champion'] = 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();
     *
     * //3.今日采购(采购订单数量、商品数量、竞价单数、反馈商品数)
     * $data['today_purchase'] = [
     * 'orders' => '0',
     * 'goods' => '0',
     * 'bidding_orders' => '0',
     * 'feedback_goods' => '0'
     * ];
     *
     * //4.今日订单列表(按照完成率排序，前三名的部门、单数、金额)
     * $data['today_order'] = [
     * ['company' => 'xxx部', 'orders' => '30', 'money' => '300'],
     * ['company' => 'xx部', 'orders' => '20', 'money' => '200'],
     * ['company' => 'x部', 'orders' => '10', 'money' => '100'],
     * ];
     *
     * //5.本月完成率列表(按照完成率排序，前三名的部门、完成单数和完成率，区分今日、本周、本月和毛利率状态：2达标、1最低、0不达标等情况)
     * $data['month_completion_rate'] = [
     * 'today' => [
     * ['company' => 'xxx部', 'completion' => 10, 'completion_rate' => '30%', 'type' => '2'],
     * ['company' => 'xx部', 'completion' => 10, 'completion_rate' => '20%', 'type' => '1'],
     * ['company' => 'x部', 'completion' => 10, 'completion_rate' => '10%', 'type' => '0'],
     * ],
     * 'week' => [
     * ['company' => 'xxx部', 'completion' => 10, 'completion_rate' => '30%', 'type' => '2'],
     * ['company' => 'xx部', 'completion' => 10, 'completion_rate' => '20%', 'type' => '1'],
     * ['company' => 'x部', 'completion' => 10, 'completion_rate' => '10%', 'type' => '0'],
     * ],
     * 'month' => [
     * ['company' => 'xxx部', 'completion' => 10, 'completion_rate' => '30%', 'type' => '2'],
     * ['company' => 'xx部', 'completion' => 10, 'completion_rate' => '20%', 'type' => '1'],
     * ['company' => 'x部', 'completion' => 10, 'completion_rate' => '10%', 'type' => '0'],
     * ],
     * ];
     *
     * //6.待开票(单数、金额)
     * $data['wait_invoice'] = [
     * 'orders' => '10',
     * 'money' => '34.45'
     * ];
     *
     * //7.待回款(单数、金额)
     * $data['wait_return'] = [
     * 'orders' => '100',
     * 'money' => '3.45'
     * ];
     *
     * //8.待回票(单数、金额)
     * $data['wait_ticket'] = [
     * 'orders' => '8',
     * 'money' => '38.5'
     * ];
     *
     * //9.待付款(单数、金额)
     * $data['wait_payment'] = [
     * 'orders' => '34',
     * 'money' => '5338.58'
     * ];
     *
     *
     * //10.竞价(招标单数、未竞标单数)
     * $data['bidding'] = [
     * 'bidding_orders' => '10',
     * 'no_bidding_orders' => '2'
     * ];
     *
     * //11.采购单(采购单数、未下单数)
     * $data['purchase'] = [
     * 'purchase_orders' => '10',
     * 'no_purchase_orders' => '2'
     * ];
     *
     * //12.采购单(采购下单数、未生产单数)
     * $data['purchase_2'] = [
     * 'orders' => '10',
     * 'no_orders' => '2'
     * ];
     *
     * //13.未发货(单数、商品数)
     * $data['no_deliver'] = [
     * 'orders' => '10',
     * 'goods' => '2'
     * ];
     *
     * //14.转单率(部门名称、今日转单率、本月转单率，今年转单率，按照年度转单率排序)
     * $data['transfer_order_rate'] = [
     * ['company' => 'xxx部', 'today_tor' => '34%', 'month_tor' => '68%', 'year_tor' => '45%'],
     * ['company' => 'xx部', 'today_tor' => '34%', 'month_tor' => '68%', 'year_tor' => '40%'],
     * ['company' => 'x部', 'today_tor' => '34%', 'month_tor' => '68%', 'year_tor' => '6%'],
     * ];
     *
     * return app_show(0, '请求成功', $data);
     *
     * }
     **/

    //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);

    }

}