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

<?php

namespace App\Console\Commands\Notices;

use App\Services\NoticeService\Channels\WeChatTemplateChannel;
use Illuminate\Console\Command;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '时遇记营销通知';

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


    public const PUBLIC_ID = 'gh_c94c95866ca5';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //
        $notice = $this->argument('notice');
        \DB::connection()->enableQueryLog(); // 开启查询日志
        switch ($notice) {
            case 'a':
                dump($this->pair1());
                break;

            case 'b':
                dump($this->pair2());
                break;

            case 'c':
                dump($this->card1());
                break;

            case 'd':
                dump($this->card2());
                break;

            case 'e':
                dump($this->card3());
                break;
        }
        $queries = \DB::getQueryLog(); // 获取查询日志
        dump($queries);
    }


    // 关注公众号、有手机号，未参与过活动的
    public function pair1()
    {
        $title = '老用户召回syj-活动召回';
        if (
            \DB::connection('mysql_datalog')
                ->table("notice_logs")
                ->where('title', $title)
                ->where('date', date("Y-m-d"))
                ->count() >= 3000
        ) {
            return false;
        }

        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', $title)
            ->distinct()
            ->pluck('openid');

        $users = \DB::table('kddx_user_openid as o')
            ->select('o.openid', 'o.sex')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->where('o.public_id', self::PUBLIC_ID)
            ->where('o.subscribe', 1)
            ->whereNotNull('u.phone')
            ->whereRaw(" u.`uid` NOT IN( SELECT DISTINCT(`uid`) FROM `kdgx_partner_charge_pair`) ")
            ->whereNotIn('o.openid', $filter_users)
            ->orderBy('o.id', 'asc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            $ta = ($user->sex == 2) ? '小哥哥' : '小姐姐';
            $templates = [
                [
                    'template_id' => 'y-SOmy9b5AaN_h652VXw0zWr6E9vKiAojWN0JhJq7-E',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword2' => [
                            'value' => '约会邀请',
                        ],
                        'keyword4' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'buiSCGP-jKweNEVi7V8ZK-8Lspem1FopbD5Kvupg1Ig',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword2' => [
                            'value' => '约会待开启',
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => '22_U9jXaYpyk65P9-bPPe_INmrlY2TYooH3pe0kDkxA',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword3' => [
                            'value' => date("Y-m-d"),
                        ],
                        'keyword4' => [
                            'value' => '当前时间',
                        ],
                        'keyword5' => [
                            'value' => '约会邀请',
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ]
            ];
            $rand = rand(0, count($templates) - 1);
            $templateId = $templates[$rand]['template_id'];
            $parameters = $templates[$rand]['data'];
            $page = "pages/pu-article/pu-article?from=template";
            $weChatTemplate = new WeChatTemplateChannel();
            $weChatTemplate->setAppId(self::PUBLIC_ID)
                ->setTitle($title)
                ->toUser($user->openid)
                ->setPage($page)
                ->setTemplateId($templateId)
                ->setParameters($parameters)
                ->send();
        }
        return ($users);
    }


    /*
     * 关注公众号、有手机号，仅参与过一次活动，最后登录时间为一个月前的用户
     */
    public function pair2()
    {
        $title = '老用户召回syj-活动召回2';
        if (
            \DB::connection('mysql_datalog')
                ->table("notice_logs")
                ->where('title', $title)
                ->where('date', date("Y-m-d"))
                ->count() >= 3000
        ) {
            return false;
        }

        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', $title)
            ->distinct()
            ->pluck('openid');

        // 关注公众号、有手机号、登陆时间为一个月之前的人
        // 仅参加一次活动
        $users = \DB::table('kddx_user_openid as o')
            ->select('o.openid', 'o.sex')
            ->join('kdgx_partner_charge_user as u', 'u.uid', '=', 'o.uid')
            ->whereNotNull('u.phone')
            ->where('u.login_at', '<', strtotime("-1 month"))
            ->where('o.public_id', self::PUBLIC_ID)
            ->where('o.subscribe', 1)
            ->whereRaw(" u.`uid` IN( SELECT `uid` FROM `kdgx_partner_charge_pair` GROUP BY `uid` HAVING COUNT(*)= 1 )")
            ->whereNotIn('o.openid', $filter_users)
            ->orderBy('o.id', 'asc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            $ta = ($user->sex == 2) ? '小哥哥' : '小姐姐';
            $templates = [
                [
                    'template_id' => 'y-SOmy9b5AaN_h652VXw0zWr6E9vKiAojWN0JhJq7-E',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword2' => [
                            'value' => '约会邀请',
                        ],
                        'keyword4' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'buiSCGP-jKweNEVi7V8ZK-8Lspem1FopbD5Kvupg1Ig',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword2' => [
                            'value' => '约会待开启',
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => '22_U9jXaYpyk65P9-bPPe_INmrlY2TYooH3pe0kDkxA',
                    'data' => [
                        'first' => [
                            'value' => "你收到一条约会邀请，有{$ta}邀请你谈一场72小时的恋爱！",
                        ],
                        'keyword1' => [
                            'value' => '72小时恋爱体验',
                        ],
                        'keyword3' => [
                            'value' => date("Y-m-d"),
                        ],
                        'keyword4' => [
                            'value' => '当前时间',
                        ],
                        'keyword5' => [
                            'value' => '约会邀请',
                        ],
                        'remark' => [
                            'value' => "点此进入了解详情，{$ta}在等你哦~",
                        ]
                    ]
                ]
            ];
            $rand = rand(0, count($templates) - 1);
            $templateId = $templates[$rand]['template_id'];
            $parameters = $templates[$rand]['data'];
            $page = "pages/pu-article/pu-article?from=template";
            $weChatTemplate = new WeChatTemplateChannel();
            $weChatTemplate->setAppId(self::PUBLIC_ID)
                ->setTitle($title)
                ->toUser($user->openid)
                ->setPage($page)
                ->setTemplateId($templateId)
                ->setParameters($parameters)
                ->send();
        }

        return ($users);
    }


    /*
     * 关注公众号、无卡片ID/有卡片ID（无照片&无语音&无联系方式）
     */
    public function card1()
    {
        $title = "老用户召回syj-卡片1";
        if (
            \DB::connection('mysql_datalog')
                ->table("notice_logs")
                ->where('title', $title)
                ->where('date', date("Y-m-d"))
                ->count() >= 3000
        ) {
            return false;
        }

        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', $title)
            ->distinct()
            ->pluck('openid');


        $users = \DB::table('kddx_user_openid as o')
            ->select('o.openid', 'o.sex')
            ->leftjoin("kdgx_partner_charge_partner as p", function ($join) {
                $join->on('o.uid', '=', 'p.uid')
                    ->whereRaw(" (p.`photo_src`!= ''
                    or p.`photo_1`!= ''
                    or p.`photo_2`!= ''
                    or p.`photo_3`!= ''
                    or p.`photo_4`!= ''
                    or p.`voice`!= ''
                    or p.`weixin` is NOT null
                    or p.`qq` IS NOT NULL)");
            })
            ->whereNull('p.id')
            ->where('o.public_id', self::PUBLIC_ID)
            ->where('o.subscribe', 1)
            ->whereNotIn('o.openid', $filter_users)
            ->orderBy('o.id', 'asc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            switch ($user->sex) {
                case '1':
                    $ta = '小姐姐';
                    break;
                case '2':
                    $ta = '小哥哥';
                    break;
                default:
                    $ta = '新朋友';
                    break;
            }

            $templates = [
                [
                    'template_id' => '94ootk_SJuBQE9Y9d--Ag5lwcEs61NRV4AsimsV3F6Q',
                    'data' => [
                        'first' => [
                            'value' => "小遇今日为你精选了56个兴趣相投又好看的{$ta}。说不定其中就有你心动的人哦！",
                        ],
                        'keyword1' => [
                            'value' => '匹配通知',
                        ],
                        'keyword2' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'buiSCGP-jKweNEVi7V8ZK-8Lspem1FopbD5Kvupg1Ig',
                    'data' => [
                        'first' => [
                            'value' => "小遇今日为你精选了56个兴趣相投又好看的{$ta}。说不定其中就有你心动的人哦！",
                        ],
                        'keyword1' => [
                            'value' => '推荐提醒',
                        ],
                        'keyword2' => [
                            'value' => date("Y-m-d"),
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'dD4V6t1sbBDGit_eVq0gZ6bX3RmLPeZU0sdK-bBJRds',
                    'data' => [
                        'first' => [
                            'value' => "小遇今日为你精选了56个兴趣相投又好看的{$ta}。说不定其中就有你心动的人哦！",
                        ],
                        'keyword1' => [
                            'value' => '小遇',
                        ],
                        'keyword2' => [
                            'value' => '推荐提醒',
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦~",
                        ]
                    ]
                ]
            ];
            $rand = rand(0, count($templates) - 1);
            $templateId = $templates[$rand]['template_id'];
            $parameters = $templates[$rand]['data'];
            $page = "pages/index/index";
            $weChatTemplate = new WeChatTemplateChannel();
            $weChatTemplate->setAppId(self::PUBLIC_ID)
                ->setTitle($title)
                ->toUser($user->openid)
                ->setPage($page)
                ->setTemplateId($templateId)
                ->setParameters($parameters)
                ->send();
        }

        return ($users);
    }


    /**
     * 关注服务号，有卡片信息（照片or语音），未上架的用户
     * @return bool
     */
    public function card2()
    {
        $title = "老用户召回syj-卡片2";
        if (
            \DB::connection('mysql_datalog')
                ->table("notice_logs")
                ->where('title', $title)
                ->where('date', date("Y-m-d"))
                ->count() >= 3000
        ) {
            return false;
        }

        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', $title)
            ->distinct()
            ->pluck('openid');


        $users = \DB::table('kddx_user_openid as o')
            ->select('o.openid', 'o.nickname')
            ->join("kdgx_partner_charge_partner as p", function ($join) {
                $join->on('o.uid', '=', 'p.uid');
            })
            ->where('o.public_id', self::PUBLIC_ID)
            ->where('o.subscribe', 1)
            ->whereRaw(" (p.`photo_src`!= ''
                    or p.`photo_1`!= ''
                    or p.`photo_2`!= ''
                    or p.`photo_3`!= ''
                    or p.`photo_4`!= ''
                    or p.`voice`!= ''
                    )")
            ->where('p.is_sell', '<>', 1)
            ->whereNotIn('o.openid', $filter_users)
            ->orderBy('o.id', 'asc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            $x = rand(100, 1000);
            $templates = [
                [
                    'template_id' => 'RJNTbLGdlK7tLRgt5uoKIEWxlB7-bwCdwgE5yR11aHk',
                    'data' => [
                        'first' => [
                            'value' => "你的交友卡片尚未修改或完善，已错过{$x}次推荐曝光机会",
                        ],
                        'keyword1' => [
                            'value' => $user->nickname,
                        ],
                        'keyword2' => [
                            'value' => "交友卡片待完善",
                        ],
                        'keyword3' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "点此继续完善卡片重新提交审核，通过后将优先把你推荐给附近的人",
                        ]
                    ]
                ],
                [
                    'template_id' => 'T5AvN3tkUKywz1-hVeW2RFLNYV1Ex_EfXI0OE_JQBxM',
                    'data' => [
                        'first' => [
                            'value' => "你的交友卡片尚未修改或完善，已错过{$x}次推荐曝光机会",
                        ],
                        'keyword1' => [
                            'value' => date("Y-m-d"),
                        ],
                        'keyword2' => [
                            'value' => '交友卡片待完善',
                        ],
                        'remark' => [
                            'value' => "点此继续完善卡片重新提交审核，通过后将优先把你推荐给附近的人",
                        ]
                    ]
                ],
                [
                    'template_id' => 'nMFoJQdUqb4IB5S-rs1PruWjC1H9HvvGfLCrPkl9iMs',
                    'data' => [
                        'first' => [
                            'value' => "你的交友卡片尚未修改或完善，已错过{$x}次推荐曝光机会",
                        ],
                        'keyword1' => [
                            'value' => '交友卡片',
                        ],
                        'keyword2' => [
                            'value' => '交友卡片待完善',
                        ],
                        'keyword3' => [
                            'value' => '交友卡片',
                        ],
                        'keyword4' => [
                            'value' => date('Y-m-d'),
                        ],
                        'remark' => [
                            'value' => "点此继续完善卡片重新提交审核，通过后将优先把你推荐给附近的人",
                        ]
                    ]
                ]
            ];
            $rand = rand(0, count($templates) - 1);
            $templateId = $templates[$rand]['template_id'];
            $parameters = $templates[$rand]['data'];
            $page = "pages/my-friend-card-editor/my-friend-card-editor";
            $weChatTemplate = new WeChatTemplateChannel();
            $weChatTemplate->setAppId(self::PUBLIC_ID)
                ->setTitle($title)
                ->toUser($user->openid)
                ->setPage($page)
                ->setTemplateId($templateId)
                ->setParameters($parameters)
                ->send();
        }

        return $users;
    }


    /**
     * 关注服务号，有交友卡片，超1月未登录
     * @return bool
     */
    public function card3()
    {
        $title = "老用户召回syj-卡片3";
        if (
            \DB::connection('mysql_datalog')
                ->table("notice_logs")
                ->where('title', $title)
                ->where('date', date("Y-m-d"))
                ->count() >= 3000
        ) {
            return false;
        }

        // 过滤发送过的人
        $filter_users = \DB::connection('mysql_datalog')
            ->table("notice_logs")
            ->where('title', $title)
            ->distinct()
            ->pluck('openid');

        $users = \DB::table('kddx_user_openid as o')
            ->select('o.openid', 'o.nickname', 'o.sex')
            ->join('kdgx_partner_charge_user as u', 'o.uid', '=', 'u.uid')
            ->where('u.login_at', '<', strtotime("-1 month"))
            ->where('o.public_id', self::PUBLIC_ID)
            ->where('o.subscribe', 1)
            ->whereNotIn('o.openid', $filter_users)
            ->orderBy('o.id', 'asc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            $x = rand(100, 1000);
            switch ($user->sex) {
                case '1':
                    $ta = '小姐姐';
                    break;
                case '2':
                    $ta = '小哥哥';
                    break;
                default:
                    $ta = '小姐姐';
                    break;
            }
            $templates = [
                [
                    'template_id' => 'buiSCGP-jKweNEVi7V8ZK-8Lspem1FopbD5Kvupg1Ig',
                    'data' => [
                        'first' => [
                            'value' => "你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~",
                        ],
                        'keyword1' => [
                            'value' => "访客通知",
                        ],
                        'keyword2' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦，来看看你附近的{$ta}吧~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'HgmFhBPrOry3i647Zu9AF4Vcfs0MaIj8bE83-G5Kg6Q',
                    'data' => [
                        'first' => [
                            'value' => "你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~",
                        ],
                        'keyword1' => [
                            'value' => '访客通知',
                        ],
                        'keyword2' => [
                            'value' => '分配对象个人中心',
                        ],
                        'keyword3' => [
                            'value' => '访客通知',
                        ],
                        'keyword4' => [
                            'value' => date("Y-m-d"),
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦，来看看你附近的{$ta}吧~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'dD4V6t1sbBDGit_eVq0gZ6bX3RmLPeZU0sdK-bBJRds',
                    'data' => [
                        'first' => [
                            'value' => "你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~",
                        ],
                        'keyword1' => [
                            'value' => '小遇',
                        ],
                        'keyword2' => [
                            'value' => '访客提醒',
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦，来看看你附近的{$ta}吧~",
                        ]
                    ]
                ]
            ];
            $rand = rand(0, count($templates) - 1);
            $templateId = $templates[$rand]['template_id'];
            $parameters = $templates[$rand]['data'];
            $page = "pages/index/index";
            $weChatTemplate = new WeChatTemplateChannel();
            $weChatTemplate->setAppId(self::PUBLIC_ID)
                ->setTitle($title)
                ->toUser($user->openid)
                ->setPage($page)
                ->setTemplateId($templateId)
                ->setParameters($parameters)
                ->send();
        }

        return $users;
    }
}