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

<?php

namespace App\Console\Commands\Notices;

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

class FpdxCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'notice:fpdx {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_b598cb7474d8';

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

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

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


    /*
     * 关注公众号、无卡片ID/有卡片ID（无照片&无语音&无联系方式）
     * @return bool
     */
    public function card1()
    {
        $title = "老用户召回fpdx-卡片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', 'desc')
            ->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' => '9e-Aclq7-z-BpMxfbFl-PnnePPoPJvdAmAYoZv2MCHw',
                    'data' => [
                        'first' => [
                            'value' => "小遇今日为你精选了56个兴趣相投又好看的{$ta}。说不定其中就有你心动的人哦！",
                        ],
                        'keyword1' => [
                            'value' => date('Y-m-d')
                        ],
                        'keyword2' => [
                            'value' => '精选推荐',
                        ],
                        'keyword3' => [
                            'value' => '已推荐',
                        ],
                        'remark' => [
                            'value' => "他们之中离你最近的只有0.5km哦~",
                        ]
                    ]
                ],
                [
                    'template_id' => 'aWsSfsy-EalI_jzopvZhjImLQkjIK1qVZqIbhMvY-4o',
                    '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 = "老用户召回fpdx-卡片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', 'desc')
            ->limit(rand(1, 5))
            ->get();


        foreach ($users as $user) {
            $x = rand(100, 1000);
            $templates = [
                [
                    'template_id' => 'JVICUmaP38MoputCdKVn5AAIFmc7ZO9eewj2FyW58NM',
                    'data' => [
                        'first' => [
                            'value' => "你的交友卡片尚未修改或完善，已错过{$x}次推荐曝光机会",
                        ],
                        'keyword1' => [
                            'value' => '交友卡片待完善',
                        ],
                        'keyword2' => [
                            'value' => date("Y-m-d"),
                        ],
                        'remark' => [
                            'value' => "点此继续完善卡片重新提交审核，通过后将优先把你推荐给附近的人",
                        ]
                    ]
                ],
                [
                    'template_id' => 'vj03Lys62tcWH0BAx2bKdDbzxBnhwe4Fhs3GXqbxkTg',
                    'data' => [
                        'first' => [
                            'value' => "你的交友卡片尚未修改或完善，已错过{$x}次推荐曝光机会",
                        ],
                        'keyword1' => [
                            'value' => '交友卡片待完善',
                        ],
                        'keyword2' => [
                            '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 = "老用户召回-访客通知";
        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', 'desc')
            ->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' => "\n你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~\n"
                        ],
                        'keyword1' => [
                            'value' => "访客通知"
                        ],
                        'keyword2' => [
                            'value' => "已通知"
                        ],
                        'keyword3' => [
                            'value' => date('Y-m-d')
                        ],
                        'keyword4' => [
                            'value' => "分配对象个人中心"
                        ],
                        'remark' => [
                            'value' => "\n他们之中离你最近的只有0.5km哦，来看看你附近的{$ta}吧~ ",
                        ],
                    ]
                ],
                [
                    'template_id' => 'ugxHaIxl5sFf2T00QZdCc0l4qELRUfEtw1HnCE7PDww',
                    'data' => [
                        'first' => [
                            'value' => "\n你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~\n"
                        ],
                        'keyword1' => [
                            'value' => "访客通知"
                        ],
                        'keyword2' => [
                            'value' => "分配对象个人中心"
                        ],
                        'keyword3' => [
                            'value' => "访客通知"
                        ],
                        'keyword4' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "\n他们之中离你最近的只有0.5km哦，来看看你附近的{$ta}吧~ ",
                        ],
                    ]
                ],
                [
                    'template_id' => 'vj03Lys62tcWH0BAx2bKdDbzxBnhwe4Fhs3GXqbxkTg',
                    'data' => [
                        'first' => [
                            'value' => "你不在的这段时间里，已经有{$x}个{$ta}看过你的交友卡片了！快来看看都有哪些人吧~\n"
                        ],
                        'keyword1' => [
                            'value' => "访客通知"
                        ],
                        'keyword2' => [
                            'value' => date('Y-m-d')
                        ],
                        'remark' => [
                            'value' => "\n他们之中离你最近的只有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;
    }
}