wangdu_public_api/app/Console/Commands/ForceUserActive.php

<?php
/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2017/11/20
 * Time: 下午5:26
 */

namespace App\Console\Commands;

use App\Modules\Finance\Services\FinanceService;
use Log;
use Illuminate\Console\Command;
use DB;
use Redis;
use App\Modules\Book\Services\BookSubscribleChapterService;
use App\Modules\Sys\Services\SysConfigService;

class ForceUserActive extends Command
{
    /**
     * 执行命令   php artisan force_user_active
     *
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'force_user_active {start} {end}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日强关用户行为';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        /**
         * 更新渠道、公众号信息
         *update force_users_date_actives,distribution_channels,official_accounts,companies,channel_users set force_users_date_actives.distribution_channel_id = distribution_channels.id,force_users_date_actives.distribution_channel_name=companies.name,force_users_date_actives.gzh_name = official_accounts.nickname  where force_users_date_actives.appid = official_accounts.appid and official_accounts.distribution_channel_id = distribution_channels.id and companies.id = channel_users.company_id and distribution_channels.channel_user_id = channel_users.id
         * select date,distribution_channel_name,gzh_name,distribution_channel_id,fans_num,pay_user_num_in_one_day,unsub_user_num_in_one_day,unsub_in_one_day_read_last_free_user_num,pay_user_num_in_three_days,unsub_user_num_in_two_days,unsub_in_two_day_read_last_free_user_num,unsub_user_num_in_three_days,unsub_in_three_day_read_last_free_user_num,pay_user_num_in_one_month,order_sum_in_one_day,order_sum_in_three_day,order_sum_in_one_month from force_users_date_actives
         * 24小时内取关粉丝阅读到免费最后一章和取关时间差
         *select count(1) from force_users_date_read where is_unsub_in_one_day = 1 and  last_free_read_time is not null and unsub_time - last_free_read_time >10800 and unsub_time - last_free_read_time <=10800
         *24小时内取关粉丝关注到取关时间差
         *select count(1) from force_users_date_read where is_unsub_in_one_day = 1 and  unsub_time - sub_time >43200 and unsub_time - sub_time <=86400
         *24小时内新关粉丝阅读到免费最后一章和首充时间差
         *select count(1) from force_users_date_read where last_free_read_time is not null  and recharge_time_in_one_day is not null and  recharge_time_in_one_day - last_free_read_time >10800 and recharge_time_in_one_day - last_free_read_time <=10800
         *24小时内新关粉丝关注到首充时间差
         *select count(1) from force_users_date_read where recharge_time_in_one_day is not null and  recharge_time_in_one_day - sub_time  >21600 and recharge_time_in_one_day - sub_time <=43200
         *24小时内新关粉丝关注到订阅时间差
         *select count(1) from force_users_date_read where first_sub_time is not null and  first_sub_time - sub_time  >43200 and first_sub_time - sub_time <=86400
         *update force_users_date_read set unsub_time = null where unsub_time = 0
         */
        print_r("======每日强关用户行为生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));
        Log::info("======每日强关用户行为生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        //清空原表数据
        DB::table('force_users_date_actives')->truncate();

        $_start = new \DateTime($this->argument('start'));
        $_end = new \DateTime($this->argument('end'));

        if(!$_start || !$_end) dd('param empty!exit');
        foreach (new \DatePeriod($_start, new \DateInterval('P1D'), $_end) as $d)
        {

            $date = $d->format('Y-m-d');
            $start = $date;
            $end = date('Y-m-d', strtotime($date) + 86400);
            print_r("======执行日期：{$date}=====");
            $offset = 0;
            $limit = 13000;
            $data = [];
            $_m_data = [];

            while (true) {

                print_r("select send_order_id,uid,appid,unsubscribe_time,distribution_channel_id,created_at from force_subscribe_users where created_at > '{$start}' and created_at < '{$end}' limit $offset,$limit" . date("y-m-d H:i:s" . "\n"));
                $users = DB::select("select send_order_id,uid,appid,unsubscribe_time,distribution_channel_id,created_at from force_subscribe_users where created_at > '{$start}' and created_at < '{$end}' limit $offset,$limit");
                print_r("users_count:".count($users));
                if (count($users) == 0) break;

                foreach ($users as $user) {
                    $is_unsub_in_one_day = false;
                    $is_pay_in_one_day = false;
                    $is_pay_in_two_days = false;
                    $is_pay_in_three_days = false;
                    $is_unsub_in_two_days = false;
                    $is_unsub_in_three_days = false;

                    $order_sum_in_one_day = 0;
                    $order_sum_in_two_day = 0;
                    $order_sum_in_three_day = 0;
                    $order_sum_in_one_month = 0;
                    $order_sum_in_two_month = 0;
                    $order_sum_in_three_month = 0;
                    $order_sum_in_six_month = 0;

                    $user_orders = DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->get();
                    $sub_time = strtotime($user->created_at);//关注时间戳

                    if (count($user_orders)) {
                        $first_order = $user_orders ? $user_orders[0] : null;

                        if (strtotime($first_order->created_at) < $sub_time + 86400)//24小时内
                        {
                            @$data[$user->appid]['pay_user_num_in_one_day']++;
                            $is_pay_in_one_day = true;
                        }

                        if (strtotime($first_order->created_at) < $sub_time + 86400 * 2)//48小时内
                        {
                            $is_pay_in_two_days = true;
                        }

                        if (strtotime($first_order->created_at) < $sub_time + 86400 * 3)//72小时内
                        {
                            @$data[$user->appid]['pay_user_num_in_three_days']++;
                            $is_pay_in_three_days = true;
                        }

                        if (strtotime($first_order->created_at) < $sub_time + 86400 * 30)//30天内
                        {
                            @$data[$user->appid]['pay_user_num_in_one_month']++;
                        }

                        foreach ($user_orders as $_order)
                        {
                            $price = $_order->price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400) $order_sum_in_one_day += $price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400*3) $order_sum_in_three_day += $price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400*30) $order_sum_in_one_month += $price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400*60) $order_sum_in_two_month += $price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400*90) $order_sum_in_three_month += $price;
                            if(strtotime($_order->created_at) - $sub_time <= 86400*180) $order_sum_in_six_month += $price;
                        }

                    }
                    @$data[$user->appid]['order_sum_in_one_day'] += $order_sum_in_one_day;
                    //@$data[$user->appid]['order_sum_in_two_day'] += $order_sum_in_two_day;
                    @$data[$user->appid]['order_sum_in_three_day'] += $order_sum_in_three_day;
                    @$data[$user->appid]['order_sum_in_one_month'] += $order_sum_in_one_month;

                    @$data[$user->appid]['fans_num']++;
                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400)//24小时内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_one_day']++;
                        $is_unsub_in_one_day = true;
                    }

                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400*2)//48小时内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_two_days']++;
                        $is_unsub_in_two_days = true;
                    }

                    if ($user->unsubscribe_time && strtotime($user->unsubscribe_time) < strtotime($user->created_at) + 86400*3)//72小时内取注
                    {
                        @$data[$user->appid]['unsub_user_num_in_three_days']++;
                        $is_unsub_in_three_days = true;
                    }

//                    if ($user->send_order_id && ($is_unsub_in_one_day || $is_unsub_in_two_days || $is_unsub_in_three_days)) {
//                        $send_order = DB::table('send_orders')->where('id', $user->send_order_id)->first();
//                        if ($send_order->book_id) {
//                            $book = Redis::hget('book_read:' . $user->uid, $send_order->book_id);
//
//                            if ($book) {
//                                $book_info = explode('_', $book);
//
//                                $cid = $book_info[0];
//                                $book_config = DB::table('book_configs')->select(['force_subscribe_chapter_seq','charge_type'])->where('bid', $send_order->book_id)->first();
//
//                                $chapter = DB::table('chapters')->select(['sequence'])->where('id', $cid)->first();
//
//                                if($book_config && $chapter)
//                                {
//                                    //判断付费章节
//                                    $vip_chapter = DB::table('chapters')->select(['sequence'])->where('bid', $send_order->book_id)->where('is_vip',1)->orderby('sequence')->first();
//                                    if($vip_chapter)
//                                    {
//                                        if ($book_config->charge_type == 'BOOK' && $chapter->sequence >= $vip_chapter->sequence - 1)//按本、看书超过付费前一章
//                                        {
//                                            $is_unsub_in_one_day && @$data[$user->appid]['unsub_in_one_day_read_last_free_user_num']++;
//                                            $is_unsub_in_two_days && @$data[$user->appid]['unsub_in_two_day_read_last_free_user_num']++;
//                                            $is_unsub_in_three_days && @$data[$user->appid]['unsub_in_three_day_read_last_free_user_num']++;
//                                        }
//
//                                        if ($book_config->charge_type == 'CHAPTER')//按章节 有订阅无充值
//                                        {
//                                            if($is_unsub_in_one_day  && !$is_pay_in_one_day)
//                                            {
//                                                $_day = 1;
//                                                $sub_num = DB::connection('chapter_order_mysql')->table('chapter_orders'.$user->uid%512)->where('uid',$user->uid)->where('bid',$send_order->book_id)->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at) + 86400*$_day))->count();
//                                                $sub_num && @$data[$user->appid]['unsub_in_one_day_read_last_free_user_num']++;
//
//                                            }elseif($is_unsub_in_two_days && !$is_pay_in_two_days)
//                                            {
//                                                $_day = 2;
//                                                $sub_num = DB::connection('chapter_order_mysql')->table('chapter_orders'.$user->uid%512)->where('uid',$user->uid)->where('bid',$send_order->book_id)->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at) + 86400*$_day))->count();
//                                                $sub_num && @$data[$user->appid]['unsub_in_two_day_read_last_free_user_num']++;
//                                            }elseif($is_unsub_in_three_days && !$is_pay_in_three_days)
//                                            {
//                                                $_day = 3;
//                                                $sub_num = DB::connection('chapter_order_mysql')->table('chapter_orders'.$user->uid%512)->where('uid',$user->uid)->where('bid',$send_order->book_id)->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at) + 86400*$_day))->count();
//                                                $sub_num && @$data[$user->appid]['unsub_in_three_day_read_last_free_user_num']++;
//                                            }
//
//                                        }
//                                    }
//                                }
//
//                            }
//
//                        }
//
//                    }

                }

                $offset = $offset + $limit;
            }

            foreach ($data as $appid => $item) {
                $_data = [];
                $_data['appid'] = $appid;
                $_data['date'] = $date;
                $_data['fans_num'] = isset($item['fans_num']) ? $item['fans_num'] : 0;
                $_data['pay_user_num_in_one_day'] = isset($item['pay_user_num_in_one_day']) ? $item['pay_user_num_in_one_day'] : 0;
                $_data['read_last_free_user_num'] = isset($item['read_last_free_user_num']) ? $item['read_last_free_user_num'] : 0;
                $_data['unsub_user_num_in_one_day'] = isset($item['unsub_user_num_in_one_day']) ? $item['unsub_user_num_in_one_day'] : 0;
                $_data['unsub_in_one_day_read_last_free_user_num'] = isset($item['unsub_in_one_day_read_last_free_user_num']) ? $item['unsub_in_one_day_read_last_free_user_num'] : 0;
                $_data['unsub_user_num_in_two_days'] = isset($item['unsub_user_num_in_two_days']) ? $item['unsub_user_num_in_two_days'] : 0;
                $_data['unsub_in_two_day_read_last_free_user_num'] = isset($item['unsub_in_two_day_read_last_free_user_num']) ? $item['unsub_in_two_day_read_last_free_user_num'] : 0;
                $_data['unsub_user_num_in_three_days'] = isset($item['unsub_user_num_in_three_days']) ? $item['unsub_user_num_in_three_days'] : 0;
                $_data['unsub_in_three_day_read_last_free_user_num'] = isset($item['unsub_in_three_day_read_last_free_user_num']) ? $item['unsub_in_three_day_read_last_free_user_num'] : 0;
                $_data['pay_user_num_in_three_days'] = isset($item['pay_user_num_in_three_days']) ? $item['pay_user_num_in_three_days'] : 0;
                $_data['pay_user_num_in_one_month'] = isset($item['pay_user_num_in_one_month']) ? $item['pay_user_num_in_one_month'] : 0;
                $_data['order_sum_in_one_day'] = $item['order_sum_in_one_day'];
                $_data['order_sum_in_three_day'] = $item['order_sum_in_three_day'];
                $_data['order_sum_in_one_month'] = $item['order_sum_in_one_month'];
                $_data['created_at'] = $_data['updated_at'] = date('Y-m-d H:i:s');
                $_m_data[] = $_data;
            }
            DB::table('force_users_date_actives')->insert($_m_data);

        }
        //更新渠道名称
        DB::update("update force_users_date_actives,distribution_channels,official_accounts,companies,channel_users set force_users_date_actives.distribution_channel_id = distribution_channels.id,force_users_date_actives.distribution_channel_name=companies.name,force_users_date_actives.gzh_name = official_accounts.nickname  where force_users_date_actives.appid = official_accounts.appid and official_accounts.distribution_channel_id = distribution_channels.id and companies.id = channel_users.company_id and distribution_channels.channel_user_id = channel_users.id");

        //修改config表开关
        SysConfigService::closeForceActiveSwitch();

        Log::info("======每日强关用户行为生成 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
        print_r("======每日强关用户行为生成 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
    }
}