wangdu_public_api/app/Console/Commands/WechatCustumerPushStatistical.php

<?php

namespace App\Console\Commands;

use App\Modules\OfficialAccount\Services\CustomMsgService;
use App\Modules\Statistic\Services\WapVisitStatService;
use Illuminate\Console\Command;
use App\Modules\Subscribe\Models\Order;
use Redis;
use DB;
use App\Modules\OfficialAccount\Models\CustomSendDayStats;
use App\Modules\OfficialAccount\Models\CustomSendStats;
use Log;

class WechatCustumerPushStatistical extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'pay:wcps {--auto} {--all}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '微信推送数据统计任务';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $all = $this->option('all');
        if($all){
            $this->autoAll();
        }
        $auto = $this->option('auto');
        if($auto){
            $this->updateAutoCustomStats(date('Y-m-d',time()-1*86400));
            $this->updateAutoCustomStats(date('Y-m-d',time()-2*86400));
            $this->updateAutoCustomStats(date('Y-m-d',time()-3*86400));
            $this->totalStats();
        }
        if(!$all && !$auto){
            $this->dayStat();
        }
    }

    public function dayStat()
    {
        //Redis::sadd('push:activity:distribution_channel_id:'.$this->distribution_channel_id.'from:'.$from.':date:'.date('Y-m-d'),$this->uid);
        //Redis::hincrby('customer:push:click:activity:distribution_channel_id:'.$this->distribution_channel_id.'from:'.$from,date('Y-m-d'),1);

        //Redis::sadd('push:distribution_channel_id:'.$distribution_channel_id.'from:'.$from,date('Y-m-d'),decrypt($uid_cookie));
        //Redis::hincrby('customer:push:click:distribution_channel_id:'.$distribution_channel_id.'from:'.$from,date('Y-m-d'),1);
        $day = date('Y-m-d', time() - 86400);
        $channel = $this->getChannel();
        if (!$channel) return false;
        foreach ($channel as $key => $value) {
            if ($value->from == 'recovery_push') {
                $click_num = Redis::scard('push:activity:distribution_channel_id:' . $value->distribution_channel_id . 'from:recovery_push:date:' . $day);
            } else {
                $click_num = Redis::scard('push:distribution_channel_id:' . $value->distribution_channel_id . 'from:' . $value->from . ':date:' . $day);
            }
            $order = Order::where('distribution_channel_id', $value->distribution_channel_id)
                ->where('from_type', $value->from)
                ->where('status', 'PAID')
                ->select(DB::raw('count(*) as count'), DB::raw('sum(price) as money'))
                ->whereBetween('created_at', [$day, date('Y-m-d')])
                ->first();
            if ($value->push_user_num <= 0) {
                $success_pay_rate = 0;
                $click_rate = 0;
            } else {
                $success_pay_rate = round($order->count / $value->push_user_num, 4) * 100;
                $click_rate = round($click_num / $value->push_user_num, 4) * 100;
            }

            CustomSendDayStats::where('id', $value->id)->update([
                'click_num' => $click_num,
                'amount' => $order->money,
                'success_pay_num' => $order->count,
                'success_pay_rate' => $success_pay_rate,
                'click_rate' => $click_rate
            ]);

            $res = CustomSendStats::where('distribution_channel_id', $value->distribution_channel_id)->where('from', $value->from)->first();
            if ($res) {
                $all_user = $res->push_user_num + $value->push_user_num;
                if ($all_user == 0) {
                    $success_pay_rate = 0;
                    $click_rate = 0;
                } else {
                    $success_pay_rate = round(($res->success_pay_num + $order->count) / $all_user, 4) * 100;
                    $click_rate = round(($res->click_num + $click_num) / $all_user, 4) * 100;
                }
                $res->push_user_num = $all_user;
                $res->click_num = $res->click_num + $click_num;
                $res->amount = $res->amount + $order->money;
                $res->success_pay_num = $res->success_pay_num + $order->count;
                $res->click_rate = $click_rate;
                $res->success_pay_rate = $success_pay_rate;
                $res->save();
            } else {
                CustomSendStats::create([
                    'distribution_channel_id' => $value->distribution_channel_id,
                    'push_user_num' => $value->push_user_num,
                    'click_num' => $click_num,
                    'amount' => $order->money,
                    'success_pay_num' => $order->count,
                    'success_pay_rate' => $success_pay_rate,
                    'from' => $value->from,
                    'click_rate' => $click_rate
                ]);

            }

        }

    }

    public function getChannel()
    {
        $day = date('Y-m-d', time() - 86400);
        $channel = CustomSendDayStats::select('id', 'distribution_channel_id', 'from', 'push_user_num')->where('date', $day)->get();
        return $channel;
    }

    private function autoAll(){
        $start = '2018-04-27';
        $end = date('Y-m-d',time()-86400);
        while (strtotime($start) <= strtotime($end)){
            $this->updateAutoCustomStats($start);
            $start = date('Y-m-d',strtotime($start)+86400);
        }
        $this->totalStats(true);
    }
    private function updateAutoCustomStats($day)
    {
        $data = $this->autoCustomTrusteeship($day);
        if (empty($data))
            return false;
        foreach ($data as $info) {
            $res = null;
            $res = CustomSendStats::where('from', 'auto_custom_trusteeship')
                ->where('distribution_channel_id', $info['distribution_channel_id'])
                ->first();
            if (!$res) {
                CustomSendStats::create([
                    'distribution_channel_id' => $info['distribution_channel_id'],
                    'date' => $day,
                    'push_user_num' => $info['push_user_num'],
                    'click_num' => $info['click_num'],
                    'amount' => $info['amount'],
                    'success_pay_num' => $info['success_pay_num'],
                    'success_pay_rate' => $info['success_pay_rate'],
                    'from' => $info['from'],
                    'click_rate' => $info['click_rate']
                ]);
            }
            //custom_send_day_stats
            $channel = CustomSendDayStats::where('date', $day)
                ->where('distribution_channel_id',$info['distribution_channel_id'])
                ->where('from',$info['from'])
                ->first();
            if($channel){
                $channel->push_user_num = $info['push_user_num'];
                $channel->click_num = $info['click_num'];
                $channel->amount = $info['amount'];
                $channel->success_pay_num = $info['success_pay_num'];
                $channel->success_pay_rate = $info['success_pay_rate'];
                $channel->click_rate = $info['click_rate'];
                $channel->save();
            }else{
                CustomSendDayStats::create($info);
            }
        }
    }

    private function totalStats($is_all=false){
        //2018-04-27 16:29:45
        $res = CustomSendStats::where('from', 'auto_custom_trusteeship')
            ->get();
        if ($res) {
            foreach ($res as $val) {
                if($is_all){
                    $start = '2018-04-27 16:29:00';
                    $start_date = '2018-04-27';
                    $visit_date = ['2018-04-27',date('Y-m-d',time()-86400)];
                }else{
                    $start = $val->updated_at->format('Y-m-d H:i:s');
                    $start_date =$val->updated_at->format('Y-m-d');
                    $visit_date = $start_date;
                }
                //echo $start.'---------'.$start_date.PHP_EOL;
                $push_info = CustomMsgService::customSendMsgsByTrusteeshipByChannelId(
                    $val->distribution_channel_id,
                    '2018-04-27 16:29:00'
                );
                $val->push_user_num = $push_info->user_num;

                $from = $this->getFromFromIds($push_info->ids);
                $visit_info = $this->getAutoCustomVisitInfo(
                    $val->distribution_channel_id,
                    $from,
                    $visit_date
                );
                if($is_all){
                    $val->click_num = $visit_info['uv'];
                }else{
                    $val->click_num += $visit_info['uv'];
                }

                $order_info = $this->getOrderInfo(
                    $val->distribution_channel_id, $from,
                    [$start, date('Y-m-d H:i:s')]
                );
                //$val->amount = $val->amount+$order_info['amount'];
                if($is_all){
                    $val->amount = $order_info['amount'];
                    $val->success_pay_num = $order_info['count'];
                }else{
                    $val->amount += $order_info['amount'];
                    $val->success_pay_num += $order_info['count'];
                }
                if( $val->push_user_num){
                    $val->success_pay_rate = round($val->success_pay_num / $val->push_user_num, 4) * 100;
                }
                if($val->push_user_num){
                    $val->click_rate = round($val->click_num / $val->push_user_num, 4) * 100;
                }
                $val->save();
            }
        }
    }

    //客服消息托管数据
    private function autoCustomTrusteeship($send_day)
    {
        //distribution_channel_id  ids  user_num
        $res = CustomMsgService::customSendMsgsByTrusteeshipGroup($send_day);
        $data = [];
        if (!$res) {
            return $data;
        }
        foreach ($res as $v) {
            $temp = [];
            $temp['distribution_channel_id'] = $v->distribution_channel_id;
            $temp['push_user_num'] = $v->user_num?$v->user_num:0;
            $from = $this->getFromFromIds($v->ids);
            $visit_info = $this->getAutoCustomVisitInfo(
                $v->distribution_channel_id,
                $from,
                [$send_day, date('Y-m-d', strtotime($send_day) + 86400 * 3)]
            );
            $temp['click_num'] = $visit_info['uv']?$visit_info['uv']:0;
            $temp['date'] = $send_day;
            $order_info = $this->getOrderInfo(
                $v->distribution_channel_id, $from,
                [$send_day, date('Y-m-d 23:59:59', strtotime($send_day) + 86400 * 3)]
            );
            $temp['amount'] = $order_info['amount']?$order_info['amount']:0;
            $temp['success_pay_num'] = $order_info['count']?$order_info['count']:0;
            if ($v->user_num) {
                $temp['success_pay_rate'] = round(($order_info['count']) / $v->user_num, 4) * 100;
                $temp['click_rate'] = round($temp['click_num'] / $v->user_num, 4) * 100;
            } else {
                $temp['success_pay_rate'] = 0;
                $temp['click_rate'] = 0;
            }
            $temp['from'] = 'auto_custom_trusteeship';
            $data[] = $temp;
        }

        return $data;
    }


    private function getFromFromIds($ids)
    {
        if (!$ids)
            return [];
        $id_arr = explode(',', $ids);
        $res = [];
        foreach ($id_arr as $v) {
            for ($i = 1; $i <= 4; $i++) {
                array_push($res, sprintf('custom_%s_%s', $v, $i));
            }
        }
        return $res;
    }

    private function getAutoCustomVisitInfo($distribution_channel_id, $from, $day)
    {
        return WapVisitStatService::getUvAndPVByFromAndDay($distribution_channel_id, $from, $day);
    }

    private function getOrderInfo($distribution_channel_id, $from, $day)
    {
        $order = Order::where('distribution_channel_id', $distribution_channel_id)
            ->whereIn('from_type', $from)
            ->where('status', 'PAID')
            ->select(DB::raw('count(*) as count'), DB::raw('sum(price) as money'))
            ->where('created_at', '>=', $day[0])
            ->where('created_at', '<', $day[1])
            ->first();
        if ($order) {
            return ['count' => $order->count, 'amount' => $order->money];
        }
        return ['count' => 0, 'amount' => 0];
    }
}