wangdu_public_api/app/Modules/OfficialAccount/Models/ForceSubscribeUsers.php

<?php

namespace App\Modules\OfficialAccount\Models;

use Illuminate\Database\Eloquent\Model;
use App\Modules\OfficialAccount\Models\TempForceSubscribeUsers;
use DB;

class ForceSubscribeUsers extends Model
{
    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)
    {

    	$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,$subscribe_time_attach='')
    {
    	$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);

        //自定义关注时间
        $subscribe_time_list = self::subscribeTimeDeal($subscribe_time,$subscribe_time_attach);
        if($subscribe_time_list){
            $query->where(function ($q) use ($subscribe_time_list){
                foreach ($subscribe_time_list as $item){
                    $q->orWhere(function ($qq) use ($item){
                        if($item[0] != 0) $qq->where('subscribe_time','>',$item[0]);
                        if($item[1] != -1)$qq->where('subscribe_time','<',$item[1]);
                    });
                }
            });
        }
        if ($sex || $balance) {
            # code...
            $query->leftjoin('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->whereIn('users.sex', [0,2]);
                        break;
                    default:
                        # code...
                        break;
                }
            }
            if ($balance) {
                # code...
                $balance_list = explode(',',$balance);
                if(count($balance_list) >1){
                    $true_balance_list = [];
                    foreach ($balance_list as $balance_item){
                        $temp_item = self::userbalance($balance_item);
                        if($temp_item) $true_balance_list[] = $temp_item;
                    }
                    $query->where(function ($q) use ($true_balance_list){
                        foreach ($true_balance_list as $true_balance_item){
                            $q->orWhere(function ($qq) use ($true_balance_item){
                                $qq->where('users.balance', '>=', $true_balance_item[0])
                                    ->where('users.balance','<',$true_balance_item[1]);
                            });
                        }
                    });
                    $balance = 'z';
                }
                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->whereNotNull('uid')->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();

    }

    private static function subscribeTimeDeal($subscribe_time,$subscribe_time_attach){
        $result  = [];
        if($subscribe_time){
            $subscribe_time_list = explode(',',$subscribe_time);
            $data = self::subscribeTime();
            foreach ($subscribe_time_list as $subscribe_time_list_item){
                $temp = isset($data[$subscribe_time_list_item])?$data[$subscribe_time_list_item]:'';
                if($temp) $result[] = $temp;
            }
        }
        if($subscribe_time_attach){
            $subscribe_time_attach_list = explode(',',$subscribe_time_attach);
            foreach ($subscribe_time_attach_list as $subscribe_time_attach_item){
                list($min_item,$max_item) = explode('-',$subscribe_time_attach_item);
                if(!$min_item && !$max_item) continue;
                $min_item_time = -1;
                $max_item_time = 0;
                if($min_item){
                    $min_item_time =  date('Y-m-d H:i:s', strtotime(-$min_item.' hour'));
                }
                if($max_item){
                    $max_item_time =  date('Y-m-d H:i:s', strtotime(-$max_item.' hour'));
                }
                $result[] = [$max_item_time,$min_item_time];
            }
        }
        return $result;
    }

    private static function subscribeTime($key = ''){
        $now = date('Y-m-d H:i:s');
        $data = [
            'a'=>[date('Y-m-d H:i:s', strtotime('-5 day')),-1],
            'b'=>[date('Y-m-d H:i:s', strtotime('-15 day')),date('Y-m-d H:i:s', strtotime('-5 day'))],
            'c'=>[date('Y-m-d H:i:s', strtotime('-30 day')),date('Y-m-d H:i:s', strtotime('-15 day'))],
            'd'=>[0,date('Y-m-d H:i:s', strtotime('-30 day'))],
            'e'=>[date('Y-m-d H:i:s', strtotime('-1 day')),$now],
            'f'=>[date('Y-m-d H:i:s', strtotime('-2 day')),date('Y-m-d H:i:s', strtotime('-1 day'))],
            'g'=>[0,date('Y-m-d H:i:s', strtotime('-1 day'))],
            'h'=>[date('Y-m-d H:i:s', strtotime('-25 min')),date('Y-m-d H:i:s', strtotime('-10 min'))],
            'i'=>[date('Y-m-d H:i:s', strtotime('-48 hour')),date('Y-m-d H:i:s', strtotime('-36 hour'))],
            'j'=>[date('Y-m-d H:i:s', strtotime('-48 hour')),date('Y-m-d H:i:s', strtotime('-20 hour'))],
            'k'=>[date('Y-m-d H:i:s', strtotime('-48 hour')),date('Y-m-d H:i:s', strtotime('-42 hour'))],
            'l'=>[date('Y-m-d H:i:s', strtotime('-48 hour')),date('Y-m-d H:i:s', strtotime('-12 hour'))],
            'n'=>[date('Y-m-d H:i:s', strtotime('-24 hour')),date('Y-m-d H:i:s', strtotime('-12 hour'))],
            'o'=>[date('Y-m-d H:i:s', strtotime('-48 hour')),date('Y-m-d H:i:s', strtotime('-24 hour'))],
            'p'=>[date('Y-m-d H:i:s', strtotime('-5 day')),date('Y-m-d H:i:s', strtotime('-48 hour'))]
        ];
        if($key){
            if(isset($data[$key])) return $data[$key];
            return [];
        }
        return $data;
    }

    private static function userbalance($key = ''){
        $data = [
            'a'=>[0,500],
            'b'=>[500,2000],
            'c'=>[2000,5000]
        ];
        if($key){
            if(isset($data[$key])) return $data[$key];
            return [];
        }
        return $data;
    }


    //指定强关用户获取
    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;
                case 'n':
                    # 关注时间 12小时-24小时
                    $selTimeStart = date('Y-m-d H:i:s', strtotime('-24 hour'));
                    $selTimeEnd = date('Y-m-d H:i:s', strtotime('-12 hour'));
                    $query->where('subscribe_time', '<=', $selTimeEnd);
                    $query->where('subscribe_time', '>', $selTimeStart);
                    break;
                case 'o':
                    # 关注时间 24小时-48小时
                    $selTimeStart = date('Y-m-d H:i:s', strtotime('-48 hour'));
                    $selTimeEnd = date('Y-m-d H:i:s', strtotime('-24 hour'));
                    $query->where('subscribe_time', '<=', $selTimeEnd);
                    $query->where('subscribe_time', '>', $selTimeStart);
                    break;
                case 'p':
                    # 关注时间 48小时-5天
                    $selTimeStart = date('Y-m-d H:i:s', strtotime('-5 day'));
                    $selTimeEnd = date('Y-m-d H:i:s', strtotime('-48 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;
    	}
    }
    
    
    /**
     * 根据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 = DB::table('official_accounts')->orderBy('id');
        //\Log::info('getOldUser:'.$obj->toSql().json_encode($params));
        if(isset($params['distribution_channel_id']) && $params['distribution_channel_id']){
            $obj->where('distribution_channel_id',$params['distribution_channel_id']);
        }
        $res = 0;
        $official_accounts = $obj->get();
        $date = date('Y-m-d');
        if(isset($params['start_time']) && $params['start_time']) {
            $date = date('Y-m-d',strtotime($params['start_time']));
        }
        foreach ($official_accounts as $official_account) {
            $count = \Redis::scard('gzh_old_sub:'.($official_account->appid).':'.$date);
            \Log::info('gzh_old_sub:'.($official_account->appid).':'.$date.$count);
            $res += $count;
        }
        return $res;
    }

    public static function getOldUserNewSubsribeNumV1($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']);
        }
        //\Log::info('getOldUser:'.$obj->toSql().json_encode($params));
        return $obj->count('id');
    }

}