<?php
namespace App\Modules\OfficialAccount\Models;
use Illuminate\Database\Eloquent\Model;
use App\Modules\OfficialAccount\Models\TempForceSubscribeUsers;
use DB;
use App\Modules\User\Services\UserService;
class ForceSubscribeUsers extends Model
{
protected $connection = 'api_mysql';
protected $tables = 'force_subscribe_users';
protected $fillable = ['uid', 'official_account_id', 'distribution_channel_id', 'appid', 'openid', 'is_subscribed', 'subscribe_time', 'unsubscribe_time', 'last_interactive_time', 'bid', 'send_order_id'];
/**
* 根据openid和appid获取强关用户
*/
static function forceSubscribeUsersByAppidAndOpenId($appid, $openid)
{
return self::where(['appid' => isset($appid) ? $appid : '', 'openid' => isset($openid) ? $openid : ''])->first();
}
/**
* 获取所有强关用户
*/
static function forceSubscribeUsersAllOf()
{
return self::where('is_subscribed', 1)->get();
}
/**
* 修改公众号迁移用户数据
*/
static function updateSubscribeUserTransfer($distribution_channel_id,$uid,$from_appid,$to_appid,$to_openid){
return self::where(['distribution_channel_id'=>$distribution_channel_id,'uid'=>$uid,'appid'=>$from_appid])->update(['appid'=>$to_appid,'openid'=>$to_openid,'updated_at'=>date('Y-m-d H:i:s')]);
}
/**
* 根据uid获取强关用户
*/
static function forceSubscribeUsersByUid($uid)
{
return self::where(['uid' => $uid, 'is_subscribed' => 1])->first();
}
/**
* 根据uid获取强关用户
*/
static function allForceSubscribeUsersByUid($uid)
{
return self::where(['uid' => $uid, 'is_subscribed' => 1])->get();
}
/**
* 根据distribution_channel_id获取强关用户
*/
static function forceSubscribeUsersByChannelidAndTimeset($distribution_channel_id, $timeset)
{
$lastTime = date('Y-m-d H:i:s', time() - $timeset);
return self::where(['distribution_channel_id' => $distribution_channel_id, 'is_subscribed' => 1])->where('last_interactive_time', '<', $lastTime)->get();
}
/**
* 根据distribution_channel_id获取强关用户
*/
static function forceSubscribeNewUsersByTimeset($distribution_channel_id, $timeset)
{
$lastTime = date('Y-m-d H:i:s', time() - $timeset);
return self::where(['distribution_channel_id' => $distribution_channel_id, 'is_subscribed' => 1])->where('created_at', '<', $lastTime)->where('created_at', '>', date('Y-m-d H:i:s', strtotime('-2 day')))->get();
// return self::where(['distribution_channel_id'=>$distribution_channel_id,'is_subscribed'=>1])->where('created_at','<',$lastTime)->get();
}
/**
* 根据distribution_channel_id获取未付费的强关用户
*/
static function forceSubscribeNewUnpaidUsersByTime($distribution_channel_id, $start_time,$end_time)
{
// return self::where('distribution_channel_id',$distribution_channel_id)
// ->where('is_subscribed', 1)
// ->where('created_at', '<', $end_time)
// ->where('created_at', '>=', $start_time)
// ->whereNotExists(function ($query) {
// $query->select('uid')
// ->from('orders')
// ->where('orders.uid', '=', 'force_subscribe_users.uid') // TODO用框架这里有bug
// ->where('orders.status', 'PAID')
// ->limit(1);
// })->skip(0)->take(20000)->get();
$data = DB::select(
"select force_subscribe_users.* from force_subscribe_users
where distribution_channel_id = '{$distribution_channel_id}'
and is_subscribed = 1
and created_at < '{$end_time}'
and created_at >= '{$start_time}'
and not exists (select uid from orders where orders.uid=force_subscribe_users.uid and orders.status='PAID' limit 1 )
limit 20000
"
);
return object_to_array($data);
}
/**
* 根据uid和appid获取强关用户
*/
static function forceSubscribeUsersByUidAndAppidAndChannelId($uid, $appid, $distribution_channel_id)
{
$query = self::where(['uid' => $uid, 'distribution_channel_id' => $distribution_channel_id, 'is_subscribed' => 1]);
$appidArray = explode(',', $appid);
return $query->wherein('appid', $appidArray)->get();
}
/**
* 根据uid获取强关用户包括取关的用户
*/
static function forceSubscribeUsersByUidIncludeCancel($uid)
{
return self::where(['uid' => $uid])->first();
}
/**
* 根据openid获取一个强关用户
*/
static function forceSubscribeUsersByOpenid($openid, $appid = '')
{
return self::where(['openid' => $openid, 'is_subscribed' => 1])->first();
}
/**
* openid获取强关用户
*/
static function getOneForceSubscribeUsersByOpenid($openid)
{
return self::where('openid', $openid)->first();
}
/**
* 根据openid获取所有强关用户
*/
static function forceAllSubscribeUsersByOpenid($openid)
{
return self::where(['openid' => $openid, 'is_subscribed' => 1])->get();
}
/**
* 根据openid获取强关用户
*/
static function forceSubscribeUsersByOpenidAndAC($openid, $appid = '', $distribution_channel_id = '')
{
$query = self::where('openid', $openid);
if ($appid) {
# code...
$query->where('appid', $appid);
}
if ($distribution_channel_id) {
# code...
$query->where('distribution_channel_id', $distribution_channel_id);
}
return $query->first();
}
/**
* 根据渠道号和日期获取强关用户数量
*/
static function forceSubscribeUserCountByChannelIdAndDate($distribution_channel_id, $startTime = '', $endTime = '')
{
$query = self::orderBy('id', 'desc');
$distribution_channel_id && $query->where('distribution_channel_id', $distribution_channel_id);
if ($startTime && $endTime) {
$query->where('created_at', '>=', date('Y-m-d H:i:s', strtotime($startTime)))->where('created_at', '<', date('Y-m-d H:i:s', strtotime($endTime) + 1 * 86400));
} elseif ($startTime) {
# code...
$end_time = date("Y-m-d",strtotime($startTime)+86400);
//$query->whereDate('created_at', $startTime);
$query->where('created_at', '>=',$startTime)->where('created_at', '<',$end_time);
} elseif ($endTime) {
# code...
$end_time = date("Y-m-d",strtotime($endTime)+86400);
//$query->whereDate('created_at', $endTime);
$query->where('created_at', '>=',$endTime)->where('created_at', '<',$end_time);
}
return $query->count();
}
/**
* 根据openid获取强关用户
*/
static function forceSubscribeUsersByOpenidAndChannelId($openid, $distribution_channel_id)
{
return self::where(['openid' => $openid, 'distribution_channel_id' => $distribution_channel_id])->first();
}
/**
* 根据openid获取一个取关用户
*/
static function forceUnSubscribeUsersByOpenid($openid)
{
return self::where(['openid' => $openid, 'is_subscribed' => 0])->first();
}
/**
* 根据openid获取全部取关用户
*/
static function forceAllUnSubscribeUsersByOpenid($openid)
{
return self::where(['openid' => $openid, 'is_subscribed' => 0])->get();
}
/**
* 根据appid获取强关用户
*/
static function forceSubscribeUsersByAppid($appid)
{
return self::where(['appid' => $appid, 'is_subscribed' => 1])->get();
}
/**
* 根据appid获取强关用户数量
*/
static function forceSubscribeUsersCountByAppid($appid)
{
return self::where(['appid' => $appid, 'is_subscribed' => 1])->count();
}
/**
* 根据appid获取今日新增粉丝数
*/
static function todayForceSubscribeUsers($appid)
{
// dd($appid);
// return 0;
$subscribe_time = date("Y-m-d");
$end_time = date("Y-m-d",strtotime($subscribe_time)+86400);
//return self::where(['appid' => $appid, 'is_subscribed' => 1])->whereDate('subscribe_time', $subscribe_time)->count();
return self::where(['appid' => $appid, 'is_subscribed' => 1])->where('subscribe_time', '>=',$subscribe_time)->where('subscribe_time', '<',$end_time)->count();
}
/**
* 根据appid获取粉丝总数
*/
static function allForceSubscribeUsers($appid)
{
return self::where(['appid' => $appid, 'is_subscribed' => 1])->count();
}
//获取推广带来的净关粉丝总数
static function getFansNum($send_order_id)
{
return self::where('send_order_id', $send_order_id)->where('is_subscribed', 1)->count();
}
//获取推广带来的新关粉丝总数
static function getNewFansNum($send_order_id)
{
return self::where('send_order_id', $send_order_id)->count();
}
//根据模板消息传的各项条件检索用户
static function forceUserCountByTemplatePrams($appids, $distribution_channel_id, $subscribe_time = '', $sex = '', $balance = '', $category_id = '', $order_type = '', $skip = 0)
{
$query = self::leftJoin('users', 'users.id', '=', 'force_subscribe_users.uid')
->select('force_subscribe_users.id', 'force_subscribe_users.uid', 'force_subscribe_users.official_account_id', 'force_subscribe_users.distribution_channel_id', 'force_subscribe_users.appid', 'force_subscribe_users.openid', 'force_subscribe_users.is_subscribed', 'force_subscribe_users.subscribe_time', 'force_subscribe_users.unsubscribe_time', 'force_subscribe_users.last_interactive_time', 'force_subscribe_users.bid',
'users.nickname');
$query->where('force_subscribe_users.distribution_channel_id', $distribution_channel_id);
$query->where('force_subscribe_users.is_subscribed', 1);
$appidArray = explode(',', $appids);
$query->wherein('force_subscribe_users.appid', $appidArray);
if ($subscribe_time) {
# code...
switch ($subscribe_time) {
case 'a':
# code...
$selTime = date('Y-m-d H:i:s', strtotime('-5 day'));
$query->where('subscribe_time', '>', $selTime);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-15 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'c':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-15 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'd':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
break;
case 'e':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-1 day'));
$selTimeEnd = date('Y-m-d H:i:s');
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'f':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
default:
# code...
break;
}
}
if ($sex || $balance) {
if ($sex) {
# code...
switch ($sex) {
case 'a':
# code...
$query->where('users.sex', 1);
break;
case 'b':
# code...
$query->where('users.sex', 2);
break;
default:
# code...
break;
}
}
if ($balance) {
# code...
switch ($balance) {
case 'a':
# code...
$query->where('users.balance', '<', 500);
break;
case 'b':
# code...
$query->where('users.balance', '>=', 500);
$query->where('users.balance', '<', 2000);
break;
case 'c':
# code...
$query->where('users.balance', '>=', 2000);
$query->where('users.balance', '<', 5000);
break;
default:
# code...
break;
}
}
}
if ($category_id) {
if ($category_id != 'z') {
# code...
$query->join('books', 'force_subscribe_users.bid', '=','books.id');
$categoryArray = explode(',', $category_id);
$query->wherein('books.category_id', $categoryArray);
// $query->where('books.category_id',$category_id);
}
}
if ($order_type) {
switch ($order_type) {
case 'a':
# code...
//未消费用户
$query->whereNotExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'b':
# code...
//已消费用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid=force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'c':
# code...
//包年用户
$query->whereExists(function ($query) {
$query->select('uid')
->from('year_orders')
->whereRaw('year_orders.uid = force_subscribe_users.uid')
->where('year_orders.begin_time', '<', date('Y-m-d H:i:s'))
->where('year_orders.end_time', '>', date('Y-m-d H:i:s'))
->limit(1);
});
break;
case 'd':
# code...
//昨天消费用户
$query->whereExists(function ($query) {
$query->select('uid')
->from('orders')
->whereRaw('orders.uid=force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->where('orders.created_at', '>=',date('Y-m-d',strtotime('-1 day')))
->where('orders.created_at', '<',date('Y-m-d'))
->limit(1);
});
break;
default:
# code...
break;
}
}
// dd($query->get());
return $query->skip($skip)->take(10000)->get();
}
//根据客服消息传的各项条件检索用户，精确到appid级别
static function forceUserCountByCustomPrams($appids, $distribution_channel_id, $subscribe_time = '', $sex = '', $balance = '', $category_id = '', $order_type = '', $sign_time = '', $skip = 0)
{
$canSendDate = date('Y-m-d H:i:s', strtotime('-2 day'));
# 缓存表
$query = TempForceSubscribeUsers::select('temp_force_subscribe_users.id', 'temp_force_subscribe_users.uid', 'temp_force_subscribe_users.distribution_channel_id', 'temp_force_subscribe_users.appid', 'temp_force_subscribe_users.openid',
'temp_force_subscribe_users.is_subscribed', 'temp_force_subscribe_users.subscribe_time', 'temp_force_subscribe_users.unsubscribe_time', 'temp_force_subscribe_users.last_interactive_time', 'temp_force_subscribe_users.bid');
$query->where('temp_force_subscribe_users.last_interactive_time', '>', $canSendDate);
$query->where('temp_force_subscribe_users.distribution_channel_id', $distribution_channel_id);
$query->where('temp_force_subscribe_users.is_subscribed', 1);
$appidArray = explode(',', $appids);
$query->wherein('temp_force_subscribe_users.appid', $appidArray);
if ($subscribe_time) {
# code...
switch ($subscribe_time) {
case 'a':
# code...
$selTime = date('Y-m-d H:i:s', strtotime('-5 day'));
$query->where('subscribe_time', '>', $selTime);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-15 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'c':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-15 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'd':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
break;
case 'e':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-1 day'));
$selTimeEnd = date('Y-m-d H:i:s');
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'f':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'g':
# 客服消息专用，24小时前的老用户，不发给新用户
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
break;
case 'h':
// 关注10-50分钟之间
$selTimeStart = date('Y-m-d H:i:s', strtotime('-50 min'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-10 min'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'i':
# 关注时间 36小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-36 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'j':
# 关注时间 20小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-20 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'k':
# 关注时间 42小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-42 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'l':
# 关注时间 12小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-12 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
default:
# code...
break;
}
}
if ($sex || $balance) {
# code...
$query->join('users', 'temp_force_subscribe_users.uid', '=', 'users.id');
if ($sex) {
# code...
switch ($sex) {
case 'a':
# code...
$query->where('users.sex', 1);
break;
case 'b':
# code...
$query->where('users.sex', 2);
break;
default:
# code...
break;
}
}
if ($balance) {
# code...
switch ($balance) {
case 'a':
# code...
$query->where('users.balance', '<', 500);
break;
case 'b':
# code...
$query->where('users.balance', '>=', 500);
$query->where('users.balance', '<', 2000);
break;
case 'c':
# code...
$query->where('users.balance', '>=', 2000);
$query->where('users.balance', '<', 5000);
break;
default:
# code...
break;
}
}
}
if ($category_id) {
if ($category_id != 'z') {
# code...
$query->join('books', 'temp_force_subscribe_users.bid','=', 'books.id');
$categoryArray = explode(',', $category_id);
$query->wherein('books.category_id', $categoryArray);
// $query->where('books.category_id',$category_id);
}
}
if ($order_type) {
switch ($order_type) {
case 'a':
# code...
//未消费用户
$query->whereNotExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =temp_force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'b':
# code...
//已消费用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =temp_force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'c':
# code...
//包年用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('year_orders')
->whereRaw('year_orders.uid = temp_force_subscribe_users.uid')
->where('year_orders.begin_time', '<', date('Y-m-d H:i:s'))
->where('year_orders.end_time', '>', date('Y-m-d H:i:s'))
->limit(1);
});
break;
case 'd':
# code...
//6-13小时之间有过订阅记录
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =temp_force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->where('orders.created_at','>',date('Y-m-d H:i:s', strtotime('-13 hour')))
->where('orders.created_at','<=',date('Y-m-d H:i:s', strtotime('-6 hour')))
->limit(1);
});
break;
default:
# code...
break;
}
}
if ($sign_time) {
switch ($sign_time) {
case 'a':
//签到 60-90分钟内
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('smart_push_user_sign')
->whereRaw('smart_push_user_sign.uid =temp_force_subscribe_users.uid')
->where('smart_push_user_sign.sign_time', '<', (microtime(true) - 60*60))
->where('smart_push_user_sign.sign_time', '>', (microtime(true) - 60*90))
->limit(1);
});
break;
default:
break;
}
}
// 单次获取1w，改动此数值，必须更改CustomMsgService下SmartPush方法内对应数值
return $query->skip($skip)->take(10000)->get();
}
//根据客服消息传的各项条件检索用户，精确到appid级别
static function forceUserCountByBatchWechatCustomPrams($appids, $distribution_channel_id, $subscribe_time = '', $sex = '', $balance = '', $category_id = '', $order_type = '', $sign_time = '', $interaction_time = '', $skip = 0)
{
$canSendDate = date('Y-m-d H:i:s', strtotime('-2 day'));
# 缓存表
$query = ForceSubscribeUsers::select('force_subscribe_users.id', 'force_subscribe_users.uid', 'force_subscribe_users.distribution_channel_id', 'force_subscribe_users.appid', 'force_subscribe_users.openid',
'force_subscribe_users.is_subscribed', 'force_subscribe_users.subscribe_time', 'force_subscribe_users.unsubscribe_time', 'force_subscribe_users.last_interactive_time', 'force_subscribe_users.bid');
// $query->where('force_subscribe_users.last_interactive_time', '>', $canSendDate);
$query->where('force_subscribe_users.distribution_channel_id', $distribution_channel_id);
$query->where('force_subscribe_users.is_subscribed', 1);
$appidArray = explode(',', $appids);
$query->wherein('force_subscribe_users.appid', $appidArray);
if ($subscribe_time) {
# code...
switch ($subscribe_time) {
case 'a':
# code...
$selTime = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '>', $selTime);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-1 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-3 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'c':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-3 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-5 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'd':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-7 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'e':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-7 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-14 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'f':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-14 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'g':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-30 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-60 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'h':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-60 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
break;
default:
# code...
break;
}
}
// 互动时间
if ($interaction_time) {
# code...
switch ($interaction_time) {
case 'a':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$query->where('last_interactive_time', '>', $selTimeStart);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$query->where('last_interactive_time', '<=', $selTimeStart);
break;
default:
# code...
break;
}
}
if ($sex || $balance) {
# code...
$query->join('users', 'force_subscribe_users.uid', '=', 'users.id');
if ($sex) {
# code...
switch ($sex) {
case 'a':
# code...
$query->where('users.sex', 1);
break;
case 'b':
# code...
$query->where('users.sex', 2);
break;
default:
# code...
break;
}
}
if ($balance) {
# code...
switch ($balance) {
case 'a':
# code...
$query->where('users.balance', '<', 500);
break;
case 'b':
# code...
$query->where('users.balance', '>=', 500);
$query->where('users.balance', '<', 2000);
break;
case 'c':
# code...
$query->where('users.balance', '>=', 2000);
$query->where('users.balance', '<', 5000);
break;
default:
# code...
break;
}
}
}
// 书籍分类
if ($category_id) {
if ($category_id != 'z') {
# code...
$query->join('books', 'force_subscribe_users.bid','=', 'books.id');
$categoryArray = explode(',', $category_id);
$query->wherein('books.category_id', $categoryArray);
// $query->where('books.category_id',$category_id);
}
}
if ($order_type) {
switch ($order_type) {
case 'a':
# code...
//未消费用户
$query->whereNotExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid = force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'b':
# code...
//已消费用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid = force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
default:
# code...
break;
}
}
if ($sign_time) {
switch ($sign_time) {
case 'a':
//签到 60-90分钟内
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('smart_push_user_sign')
->whereRaw('smart_push_user_sign.uid = force_subscribe_users.uid')
->where('smart_push_user_sign.sign_time', '<', (microtime(true) - 60*60))
->where('smart_push_user_sign.sign_time', '>', (microtime(true) - 60*90))
->limit(1);
});
break;
default:
break;
}
}
// 单次获取1w，改动此数值，必须更改CustomMsgService下SmartPush方法内对应数值
return $query->skip($skip)->take(10000)->get();
}
//指定强关用户获取
static function simpleForceUserCountByPrams($uids, $param=[],$skip = 0)
{
$query = ForceSubscribeUsers::leftjoin('wechat_templates','wechat_templates.appid','=','force_subscribe_users.appid');
$query->select('force_subscribe_users.id', 'force_subscribe_users.uid', 'force_subscribe_users.distribution_channel_id', 'force_subscribe_users.appid', 'force_subscribe_users.openid',
'force_subscribe_users.is_subscribed', 'force_subscribe_users.subscribe_time','force_subscribe_users.unsubscribe_time', 'force_subscribe_users.last_interactive_time',
'force_subscribe_users.bid','wechat_templates.template_id');
$query->where('force_subscribe_users.subscribe_time', '>=','2018-11-15');
$query->where('force_subscribe_users.is_subscribed', 1);
$query->wherein('force_subscribe_users.uid', $uids);
$subscribe_time = isset($param['subscribe_time'])?$param['subscribe_time']:'z';
$category_id = isset($param['category_id'])?$param['category_id']:'z';
$sex = isset($param['sex'])?$param['sex']:'';// 不区分
$balance = isset($param['balance'])?$param['balance']:'';
$order_type = isset($param['order_type'])?$param['order_type']:'z';
$sign_time = isset($param['sign_time'])?$param['sign_time']:'z';
$common_template_id = isset($param['common_template_id'])?$param['common_template_id']:'';
if($common_template_id){
$query->where('wechat_templates.common_template_id', $common_template_id);
// $query->select(DB::raw('(select template_id from wechat_templates where appid=force_subscribe_users.appid and common_template_id="'.$common_template_id.'" limit 1) as template_id'));
}
if ($subscribe_time) {
# code...
switch ($subscribe_time) {
case 'a':
# code...
$selTime = date('Y-m-d H:i:s', strtotime('-5 day'));
$query->where('subscribe_time', '>', $selTime);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-15 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'c':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-15 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'd':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
break;
case 'e':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-1 day'));
$selTimeEnd = date('Y-m-d H:i:s');
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'f':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'g':
# 客服消息专用，24小时前的老用户，不发给新用户
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
break;
case 'h':
// 关注10-50分钟之间
$selTimeStart = date('Y-m-d H:i:s', strtotime('-50 min'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-10 min'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'i':
# 关注时间 36小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-36 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'j':
# 关注时间 20小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-20 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'k':
# 关注时间 42小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-42 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'l':
# 关注时间 12小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-12 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'm':
# 关注时间 60小时-80小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-80 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-60 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
default:
# code...
break;
}
}
if ($sex || $balance) {
# code...
$query->join('users', 'force_subscribe_users.uid', '=', 'users.id');
if ($sex) {
# code...
switch ($sex) {
case 'a':
# code...
$query->where('users.sex', 1);
break;
case 'b':
# code...
$query->where('users.sex', 2);
break;
default:
# code...
break;
}
}
if ($balance) {
# code...
switch ($balance) {
case 'a':
# code...
$query->where('users.balance', '<', 500);
break;
case 'b':
# code...
$query->where('users.balance', '>=', 500);
$query->where('users.balance', '<', 2000);
break;
case 'c':
# code...
$query->where('users.balance', '>=', 2000);
$query->where('users.balance', '<', 5000);
break;
default:
# code...
break;
}
}
}
if ($category_id) {
if ($category_id != 'z') {
# code...
$query->join('books', 'force_subscribe_users.bid','=', 'books.id');
$categoryArray = explode(',', $category_id);
$query->wherein('books.category_id', $categoryArray);
// $query->where('books.category_id',$category_id);
}
}
if ($order_type) {
switch ($order_type) {
case 'a':
# code...
//未消费用户
$query->whereNotExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'b':
# code...
//已消费用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'c':
# code...
//包年用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('year_orders')
->whereRaw('year_orders.uid = force_subscribe_users.uid')
->where('year_orders.begin_time', '<', date('Y-m-d H:i:s'))
->where('year_orders.end_time', '>', date('Y-m-d H:i:s'))
->limit(1);
});
break;
default:
# code...
break;
}
}
if ($sign_time) {
switch ($sign_time) {
case 'a':
//签到 60-90分钟内
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('user_sign')
->whereRaw('user_sign.uid =force_subscribe_users.uid')
->where('user_sign.sign_time', '<', (microtime(true) - 60*60))
->where('user_sign.sign_time', '>', (microtime(true) - 60*90))
->limit(1);
});
break;
default:
break;
}
}
// 单次获取1w，改动此数值，必须更改CustomMsgService下SmartPush方法内对应数值
return $query->skip($skip)->take(10000)->get();
}
//根据客服消息传的各项条件检索用户--老版
static function forceUserCountByCustomPrams2($appids, $distribution_channel_id, $subscribe_time = '', $sex = '', $balance = '', $category_id = '', $order_type = '', $sign_time = '', $skip = 0)
{
$query = self::select('force_subscribe_users.id', 'force_subscribe_users.uid', 'force_subscribe_users.official_account_id', 'force_subscribe_users.distribution_channel_id', 'force_subscribe_users.appid', 'force_subscribe_users.openid', 'force_subscribe_users.is_subscribed', 'force_subscribe_users.subscribe_time', 'force_subscribe_users.unsubscribe_time', 'force_subscribe_users.last_interactive_time', 'force_subscribe_users.bid');
$query->where('force_subscribe_users.distribution_channel_id', $distribution_channel_id);
$query->where('force_subscribe_users.is_subscribed', 1);
$appidArray = explode(',', $appids);
$query->wherein('force_subscribe_users.appid', $appidArray);
$canSendDate = date('Y-m-d H:i:s', strtotime('-2 day'));
$query->where('force_subscribe_users.last_interactive_time', '>', $canSendDate);
if ($subscribe_time) {
# code...
switch ($subscribe_time) {
case 'a':
# code...
$selTime = date('Y-m-d H:i:s', strtotime('-5 day'));
$query->where('subscribe_time', '>', $selTime);
break;
case 'b':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-15 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'c':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-15 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
$query->where('subscribe_time', '>', $selTimeEnd);
break;
case 'd':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-30 day'));
$query->where('subscribe_time', '<=', $selTimeStart);
break;
case 'e':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-1 day'));
$selTimeEnd = date('Y-m-d H:i:s');
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'f':
# code...
$selTimeStart = date('Y-m-d H:i:s', strtotime('-2 day'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'g':
# 客服消息专用，24小时前的老用户，不发给新用户
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-1 day'));
$query->where('subscribe_time', '<=', $selTimeEnd);
break;
case 'h':
// 关注10-25分钟之间
$selTimeStart = date('Y-m-d H:i:s', strtotime('-25 min'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-10 min'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'i':
# 关注时间 36小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-36 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'j':
# 关注时间 20小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-20 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'k':
# 关注时间 42小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-42 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
case 'l':
# 关注时间 12小时-48小时
$selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
$selTimeEnd = date('Y-m-d H:i:s', strtotime('-12 hour'));
$query->where('subscribe_time', '<=', $selTimeEnd);
$query->where('subscribe_time', '>', $selTimeStart);
break;
default:
# code...
break;
}
}
if ($sex || $balance) {
# code...
$query->join('users', 'force_subscribe_users.uid', '=', 'users.id');
if ($sex) {
# code...
switch ($sex) {
case 'a':
# code...
$query->where('users.sex', 1);
break;
case 'b':
# code...
$query->where('users.sex', 2);
break;
default:
# code...
break;
}
}
if ($balance) {
# code...
switch ($balance) {
case 'a':
# code...
$query->where('users.balance', '<', 500);
break;
case 'b':
# code...
$query->where('users.balance', '>=', 500);
$query->where('users.balance', '<', 2000);
break;
case 'c':
# code...
$query->where('users.balance', '>=', 2000);
$query->where('users.balance', '<', 5000);
break;
default:
# code...
break;
}
}
}
if ($category_id) {
if ($category_id != 'z') {
# code...
$query->join('books', 'force_subscribe_users.bid', '=','books.id');
$categoryArray = explode(',', $category_id);
$query->wherein('books.category_id', $categoryArray);
// $query->where('books.category_id',$category_id);
}
}
if ($order_type) {
switch ($order_type) {
case 'a':
# code...
//未消费用户
$query->whereNotExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'b':
# code...
//已消费用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.uid =force_subscribe_users.uid')
->whereRaw('orders.status="PAID"')
->limit(1);
});
break;
case 'c':
# code...
//包年用户
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('year_orders')
->whereRaw('year_orders.uid = force_subscribe_users.uid')
->where('year_orders.begin_time', '<', date('Y-m-d H:i:s'))
->where('year_orders.end_time', '>', date('Y-m-d H:i:s'))
->limit(1);
});
break;
default:
# code...
break;
}
}
if ($sign_time) {
switch ($sign_time) {
case 'a':
//签到 60-90分钟内
$query->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('user_sign')
->whereRaw('user_sign.uid =force_subscribe_users.uid')
->where('user_sign.sign_time', '<', (microtime(true) - 60*60))
->where('user_sign.sign_time', '>', (microtime(true) - 60*90))
->limit(1);
});
break;
default:
break;
}
}
// 单次获取1w，改动此数值，必须更改CustomMsgService下SmartPush方法内对应数值
return $query->skip($skip)->take(10000)->get();
}
/**
* 通过uid获取用户关注的服务号名称
* @param $uid
* @return mixed
*/
static function getOfficalAccountNickName($uid)
{
$search_obj = self::select('official_accounts.nickname')->leftjoin('official_accounts', 'force_subscribe_users.appid', '=', 'official_accounts.appid')->where('force_subscribe_users.uid', $uid);
return $search_obj->first();
}
/**
* 根据openid获取用户的性别信息
* @param unknown_type $distribution_channel_id
* @param unknown_type $openid
*/
static function getSexByOpenid($distribution_channel_id,$openid){
$sex = self::join('books','force_subscribe_users.bid','=','books.id')
->join('book_categories', 'books.category_id','=', 'book_categories.id')
->select('book_categories.channel_name','force_subscribe_users.bid')
->where('force_subscribe_users.openid',$openid)
->where('force_subscribe_users.distribution_channel_id',$distribution_channel_id)
->first();
\Log::info('getSexByOpenid:'.$openid.' sex:'.json_encode($sex));
if(isset($sex->channel_name) && $sex->channel_name){
if($sex->channel_name == '男频'){
return 1;
}
return 2;
}else{
// 判断用户性别
$user = self::join('users','force_subscribe_users.uid','=','users.id')
->select('users.sex')
->where('force_subscribe_users.openid',$openid)
->where('force_subscribe_users.distribution_channel_id',$distribution_channel_id)
->first();
\Log::info('getSexByOpenid_user_openid:'.$openid.' sex:'.json_encode($user));
if(isset($user->sex) && $user->sex == 1){
\Log::info('getSexByOpenid_by_user_sex:'.$user->sex);
return 1;
}
return 2;
}
}
/**
* 根据uid获取用户的真实性别信息
*/
static function getUserRealSexByUid($uid){
// 判断用户性别
$user = UserService::getById($uid);
$sex = isset($user->sex) && $user->sex==1?1:2;
\Log::info('getUserRealSexByUid:'.$uid.' sex:'.$sex);
return $sex;
}
/**
* 根据openid获取用户的性别信息--无需判断性别
*/
static function getSimpleSexByOpenid($distribution_channel_id,$openid){
$sex = self::join('books','force_subscribe_users.bid','=','books.id')
->join('book_categories', 'books.category_id','=', 'book_categories.id')
->select('book_categories.channel_name','force_subscribe_users.bid')
->where('force_subscribe_users.openid',$openid)
->where('force_subscribe_users.distribution_channel_id',$distribution_channel_id)
->first();
\Log::info('getSimpleSexByOpenid:'.$openid.' sex:'.json_encode($sex));
if(isset($sex->channel_name) && $sex->channel_name){
if($sex->channel_name == '男频'){
return 1;
}
}
return 2;
}
/**
* 根据uid获取用户的性别信息--无需判断性别
*/
static function getSimpleSexByUid($uid){
if($uid == 0){
return 2;
}
$sex = self::join('books','force_subscribe_users.bid','=','books.id')
->join('book_categories', 'books.category_id','=', 'book_categories.id')
->select('book_categories.channel_name','force_subscribe_users.bid')
->where('force_subscribe_users.uid',$uid)
->first();
\Log::info('getSimpleSexByUid:'.$uid.' sex:'.json_encode($sex));
if(isset($sex->channel_name) && $sex->channel_name){
if($sex->channel_name == '男频'){
return 1;
}
}
return 2;
}
public static function getOldUserNewSubsribeNum($params){
$obj = self::whereRaw('force_subscribe_users.subscribe_time > force_subscribe_users.created_at');
if(isset($params['distribution_channel_id']) && $params['distribution_channel_id']){
$obj->where('distribution_channel_id',$params['distribution_channel_id']);
}
if(isset($params['start_time']) && $params['start_time']){
$obj->where('subscribe_time','>=',$params['start_time']);
}
if(isset($params['end_time']) && $params['end_time']){
$obj->where('subscribe_time','<=',$params['end_time']);
}
return $obj->count('id');
}
}