msy-prod/app/Console/Commands/Notices/OldUserNoticeCommand.php

<?php

namespace App\Console\Commands\Notices;

use App\Models\Fpdx\PairModel;
use App\Models\PartnerModel;
use App\Services\Partner\NoticeService;
use Illuminate\Console\Command;

class OldUserNoticeCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'notice:old_user {notice}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '老用户-通知';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $notice = $this->argument('notice');

        \DB::connection()->enableQueryLog(); // 开启查询日志
        switch ($notice) {
            case "vip":
                dump($this->vip());
                break;
            case "once_pair":
                dump($this->oncePair());
                break;
            case "phone_not_card":
                dump($this->phoneNotCard());
                break;
            case "partner":
                dump($this->partner());
                break;
            case "visitor":
                dump($this->visitorCallBack());
                break;
            case 'cardRecommendCancel':
                dump($this->cardRecommendCancel());
                break;
            case "syj_pair1":
                dump($this->syj_pair1());
                break;
            case 'syj_pair2':
                dump($this->syj_pair2());
                break;
            case 'syj_visitor':
                dump($this->syj_visitor());
                break;
            case 'syj_like':
                dump($this->syj_like());
                break;
            case 'syj_like2':
                dump($this->syj_like2());
                break;
        }
        $queries = \DB::getQueryLog(); // 获取查询日志
        print_r($queries);
    }


    /**
     * 老用户召回-激活会员通知
     */
    public function vip()
    {
        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '老用户召回-激活会员通知')
            ->pluck('uid')->toArray();

        // 关注公众号、未开通会员
        $users = \DB::table('kddx_user_openid as o')
            ->select('u.uid')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->where('u.be_vip_at', 0)
            ->where('o.public_id', 'gh_b598cb7474d8')
            ->where('o.subscribe', 1)
            ->whereNotIn('u.uid', $filter_users)
            ->orderBy('u.uid')
            ->limit(180)
            ->get();

        foreach ($users as $user) {
            $uid = $user->uid;
            $notification = new \App\Services\User\Notifications\OldUserToVipNotification($uid);
            $notification->send();
        }
        return $users;
    }


    /**
     * 有手机号但无卡片者通知
     * @return \Illuminate\Support\Collection
     */
    public function phoneNotCard()
    {
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '老用户召回-有手机号但无卡片者通知')
            ->pluck('uid')
            ->toArray();

        $users = \DB::table('kddx_user_openid as o')
            ->select('u.uid')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->where('u.partner_id', 0)
            ->whereNotNull('phone')
            ->where('o.public_id', 'gh_b598cb7474d8')
            ->where('o.subscribe', 1)
            ->whereNotIn('u.uid', $filter_users)
            ->orderBy('u.uid', 'desc')
            ->limit(60)
            ->get();

        foreach ($users as $user) {
            // 模版消息
            $notification = new \App\Services\User\Notifications\OldUserToNotCardNotification($user->uid);
            $notification->send();
        }
        return $users;
    }


    /**
     *
     */
    public function oncePair()
    {
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '老用户召回-仅参与过一次活动的用户')
            ->pluck('uid')
            ->toArray();

        $users = \DB::table('kddx_user_openid as o')
            ->select('u.uid')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->where('o.public_id', 'gh_b598cb7474d8')
            ->where('o.subscribe', 1)
            ->whereNull('u.phone')
            ->whereNotIn("u.uid", $filter_users)
            ->whereRaw("u.uid in (SELECT `uid`
                  FROM `koudai`.`kdgx_partner_charge_pair`
                 WHERE `create_time`> 1541001600
                 GROUP BY `uid`
                HAVING COUNT(*)= 1)")
            ->limit(120)
            ->get();

        foreach ($users as $user) {
            // 模版消息
            $notification = new \App\Services\User\Notifications\OldUserToOncePairNotification($user->uid);
            $notification->send();
        }

        return $users;
    }


    public function partner()
    {
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '老用户召回-有卡片的用户')
            ->pluck('uid')
            ->toArray();

        $users = \DB::table('kddx_user_openid as o')
            ->select('u.uid')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->where('u.partner_id', '>', 0)
            ->where('o.public_id', 'gh_b598cb7474d8')
            ->where('o.subscribe', 1)
            ->whereBetween('u.login_at', [time() - 6 * 86400, time() - 5 * 86400])
            ->whereNotIn("u.uid", $filter_users)
            ->limit(600)
            ->get();

        foreach ($users as $user) {
            // 模版消息
            $notification = new \App\Services\User\Notifications\OldUserToPartnerNotification($user->uid);
            $notification->send();
        }

        return $users;
    }


    // 服务号召回通知-通过访客召回
    public function visitorCallBack()
    {
        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '老用户召回-访客通知')
            ->pluck('uid');

        $users = \DB::table('kddx_user_openid as o')
            ->select('p.uid')
            ->join('kdgx_partner_charge_partner as p', 'p.uid', '=', 'o.uid')
            ->where('p.id', '>', 0)
            ->where('p.is_sell', 1)
            ->where('o.public_id', 'gh_b598cb7474d8')
            ->where('o.subscribe', 1)
            ->where('p.login_at', '<', strtotime("-1 month"))
            ->whereNotIn('p.uid', $filter_users)
            ->limit(600)
            ->get();

        foreach ($users as $user) {
            // 模版消息
            $notification = new \App\Services\User\Notifications\OldUserToVisitorNotification($user->uid);
            $notification->send();
        }

        return $users;
    }


    /**
     * 卡片取消推荐未换卡片，每日都推
     */
    public function cardRecommendCancel()
    {
        // 过滤一周内发送过的人
        $begin = mktime(0, 0, 0) - 7 * 86400;
        $end = time();
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', '交友卡片-卡片取消推荐未更换通知')
            ->whereBetween('created_at', [$begin, $end])
            ->pluck('uid');
        $cards = PartnerModel::select('uid')
            ->where('is_push_feed', 0)
            ->whereNotIn('uid', $filter_users)
            ->limit(1000)
            ->pluck('uid');
        foreach ($cards as $uid) {
            (new NoticeService())->recommendCancelNotChange($uid);
        }
    }
}