playlet/app/Console/Commands/CustomerHour...

<?php

namespace App\Console\Commands;

use App\Log;
use App\Models\Customer;
use App\Models\CustomerDetails;
use App\Models\CustomerHourRecord;
use App\Models\AuthorizeCorp;
use App\Models\WxOfficialAccountFanRecord;
use App\Models\DjOrder;
use App\Models\OfficialAccount;
use App\RedisModel;
use App\Support\EmailQueue;
use Illuminate\Console\Command;

class CustomerHourStat extends Command
{
    protected $signature = 'CustomerHourStat';
    protected $description = '客户数据每日分小时统计';

    public function handle()
    {
        \DB::connection()->disableQueryLog();

        $this->info(date('m-d H:i:s') . ' 开始整理');

        $this->stat();

        $this->info(date('m-d H:i:s') . ' 整理结束');
    }

    public static function stat()
    {
        try {
            //查企微账号
            $accounts = AuthorizeCorp::select('corpid')->where('enable', 1)->get();

            if($accounts->isEmpty()){
                return;
            }

            $start = date('Y-m-d');
            $hour = (int)date('H');

            //汇总
            $data_total = array();

            foreach($accounts as $account){
                $corpid = $account->corpid;

                # 查询企微未流失的总客户数
                $cust_total_info = CustomerDetails::suffix($corpid)
                    ->where('corpid', $corpid)
                    ->where('loss_status', 1)
                    ->selectRaw('count(distinct(external_userid)) as cust_total')
                    ->first();
                $cust_total_uc = $cust_total_info->cust_total ?? 0;

                # 查询当日新添加的客户信息
                $cust_add_uc = CustomerDetails::suffix($corpid)
                    ->selectRaw("external_userid, min(createtime) as add_time")
                    ->where('corpid', $corpid)
                    ->where('loss_status', 1)
                    ->groupBy(['external_userid'])
                    ->having("add_time", '>=', strtotime($start))
                    ->get()
                    ->count();

                # 当日流失用户信息
                $cust_tloss = CustomerDetails::suffix($corpid)
                    ->where('loss_time', '>=', $start)
                    ->where('loss_status', 0)
                    ->where('corpid', $corpid)
                    ->select('external_userid')
                    ->distinct()
                    ->pluck('external_userid')
                    ->all();
                #过滤真正流失的
                if( empty($cust_tloss) ){
                    $cust_loss_uc = 0;
                } else {
                    $cust_noloss = CustomerDetails::suffix($corpid)
                        ->where('loss_status', 1)
                        ->where('corpid', $corpid)
                        ->whereIn('external_userid', $cust_tloss)
                        ->select('external_userid')
                        ->distinct()
                        ->pluck('external_userid')
                        ->all();
                    # 当日流失客户数 = 当日流失总数 - 还关注了其它客服的流失客户
                    $cust_loss_uc = count($cust_tloss) - count($cust_noloss);
                }

                //先查关联公众号
                $official_account_uc = null;

                $appids = OfficialAccount::where('corp_id', $corpid)->pluck('mp_app_id')->all();

                if(!empty($appids)){
                    $official_info = WxOfficialAccountFanRecord::whereIn('app_id', $appids)
                        ->where('record_at', date('Y-m-d H:00:00', strtotime('-1 hour') ))
                        ->selectRaw('sum(follow_uv) as follow_uv')
                        ->first();
                    $official_account_uc = $official_info->follow_uv ?? null;// 公众号关注数
                }

                $cust_pay_uc = null;// 付费客户数
                $cust_pay_amount = null; //付费金额
                $cust_pay = DjOrder::where('system_corpid', $corpid)
                    ->where('pay_status', 1)
                    ->where('order_pay_time', '>=', strtotime($start) * 1000)
                    ->where('enable', 1)
                    ->selectRaw('count(distinct(external_userid)) as cust_uc, sum(pay_money) as pay_amount')
                    ->first();

                if( isset($cust_pay->cust_uc) ){
                    $cust_pay_uc = $cust_pay->cust_uc;
                    $cust_pay_amount = round($cust_pay->pay_amount/10000, 2);
                }

                $cust_pay_uc_total = null;// 总付费客户数
                $cust_pay_total = DjOrder::where('system_corpid', $corpid)
                    ->where('pay_status', 1)
                    ->where('enable', 1)
                    ->selectRaw('count(distinct(external_userid)) as cust_uc, sum(pay_money) as pay_amount')
                    ->first();

                if( isset($cust_pay_total->cust_uc) ){
                    $cust_pay_uc_total = $cust_pay_total->cust_uc;
                    $cust_pay_amount_total = round($cust_pay_total->pay_amount/10000, 2);
                }

                # 总客户数
                $cust_total_info = CustomerDetails::suffix($corpid)->where('corpid', $corpid)
                    ->selectRaw("count(distinct(external_userid)) as total")
                    ->first();
                $cust_total = $cust_total_info->total ?? 0;
                # 所有流失客户信息
                $cust_loss_uc_total = $cust_total - $cust_total_uc;

                $data_total[] = [
                    'idate' => $start,
                    'corpid' => $corpid,
                    'hour' => $hour,
                    'cust_total_uc' => $cust_total_uc,      // 未流失总客户数
                    'cust_add_uc' => $cust_add_uc,          // 当日新增客户数
                    'cust_loss_uc' => $cust_loss_uc,        // 当日客户流失
                    'official_account_uc' => $official_account_uc,      // 今日公众号关注数
                    'cust_pay_uc' => $cust_pay_uc,          // 付费客户数
                    'cust_pay_amount' => $cust_pay_amount,      // 付费金额
                    'cust_pay_uc_total' => $cust_pay_uc_total,  // 总付费客户数
                    'cust_loss_uc_total' => $cust_loss_uc_total,      // 总流失客户数
                    'cust_pay_amount_total' => $cust_pay_amount_total   // 总付费金额
                ];
            }

            CustomerHourRecord::insert($data_total);
        } catch (\Exception $e) {
            EmailQueue::rPush('统计分日用户数据发生异常', $e->getTraceAsString(), ['xiaohua.hou@kuxuan-inc.com'], '统计分日用户数据发生异常');
            Log::logInfo('统计分日用户数据发生异常：', ['line' => $e->getLine(), 'msg' => $e->getMessage()], 'CustomerHourStat');
        }

    }
}