zhuishuyun_quickapp/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();

    }

    /**
     * 根据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();

    }


    //根据客服消息传的各项条件检索用户
    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-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', '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('user_sign')
        				->whereRaw('user_sign.uid =temp_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 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-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;
    			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;
    	}
    }
    
    
    /**
     * 根据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;
     }
     
}