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