<?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"));
}
}