zhuishuyun_quickapp/app/Console/Commands/ForceUserActive.php.bak

<?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;

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

    /**
     * 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 set force_users_date_actives.distribution_channel_id = distribution_channels.id,force_users_date_actives.distribution_channel_name=distribution_channels.nickname,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
         * 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"));

        $_start = new \DateTime('2018-01-05');
        $_end = new \DateTime('2018-03-05');

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


            $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 = DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->first();
                    if ($order) {
                        if (strtotime($order->created_at) < strtotime($user->created_at) + 86400)//24小时内
                        {
                            @$data[$user->appid]['pay_user_num_in_one_day']++;
                            $is_pay_in_one_day = true;
                        }

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

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

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

                        $order_sum_in_one_day = (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400))->sum('price');
                        //$order_sum_in_two_day = (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*2))->sum('price');
                        $order_sum_in_three_day = (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*3))->sum('price');
                        $order_sum_in_one_month = (float)DB::table('orders')->where('uid', $user->uid)->where('status', 'PAID')->where('created_at','<=',date('Y-m-d H:i:s',strtotime($user->created_at)+86400*30))->sum('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();

                                //判断付费章节
                                $vip_chapter = DB::table('chapters')->select(['sequence'])->where('bid', $send_order->book_id)->where('is_vip',1)->orderby('sequence')->first();

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


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


        }
    }
}