LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hiromu-kenzaki

非エンジニアがVtuberリマインダーwebアプリを作ってみた！

Posted at

はじめに：

Vtuber（ホロライブ）が好きな非エンジニアです。
公式スケジュールをチェックして、気になる配信を見るを繰り返していました。しかし段々とめんどくさいと感じました。
能動的に配信情報を得るのではなく、受動的に情報を得たい。そこでリマインダーwebアプリを作りました。
Vminder

GitHub

開発環境：

  • vercel-php@0.7.3
  • vercel
  • pgAdmin
  • Redis
  • Neon

概要：

Vtuberの配信情報を取得するためにYouTube Data APIを使用しました。
GCPでschedulerを使い、ターゲットタイプをHTTPに設定。5分毎にリクエスト。最新のactivitiesを検知することでPHPMailerにてメール送信。DKIMでドメイン認証して迷惑メールへ振られないようにする。

環境構築：

vercelではPHPのランタイムを公式でサポートしていません。そのためコミュニティーランタイムから引っ張ってきます。
vercel-php

DBはPostgreSQL。サーバーレスのNeonでDBを管理します。GUIの方が楽なのでpgAdminからqueryを投げます。
vercelで$_SESSIONを使うとエラーになります。調べるとSESSION管理はRedisを使用するみたいです。PHPからはクライアントのPredisを使います。
predis

リマインダー処理：

schedulerから届いたHTTPは、下記のスクリプトを実行します。

send.php
<?php

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require __DIR__ . '/Channel.php';
require __DIR__ . '/../dashboard/UserData.php';
require_once __DIR__ . '/../error/ErrorMail.php';
require __DIR__ . '/../../vendor/autoload.php';

$channel = new Channel();

$channelIdList = $channel->getIdList();

$activitieIdList = $channel->getActivitieId($channelIdList);

$videoLiveDetailList = $channel->getlVideoLiveDetail($activitieIdList);

try {

    $mail = new PHPMailer(true);
    $mail->isSMTP();
    $mail->Host = "smtp.gmail.com";
    $mail->SMTPAuth = true;
    $mail->SMTPKeepAlive = true;
    $mail->CharSet = PHPMailer::CHARSET_UTF8;
    $mail->Encoding = PHPMailer::ENCODING_BASE64;
    $mail->Username = $_ENV['GMAIL_USERNAME'];
    $mail->Password = $_ENV['GMAIL_PASSWORD'];
    $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
    $mail->Port = 587;
    $mail->setFrom($_ENV['GMAIL_USERNAME'], "Vminder-VverseHub-");
    $mail->isHTML(true);

    $mail->DKIM_domain = 'gmail.com';
    $mail->DKIM_private = __DIR__ . '/../../dkim_private.pem';
    $mail->DKIM_selector = 'v-minder';
    $mail->DKIM_identity = $mail->From;
    $mail->DKIM_copyHeaderFields = false;

    $mailAddressList = UserData::getEmailAddress($videoLiveDetailList);

    foreach ($mailAddressList as $mailAddress) {

        $mail->addAddress($mailAddress);

        $query = <<<query
            SELECT reminder_register.member_id FROM users
            INNER JOIN reminder_register ON users.id = reminder_register.user_id
            WHERE users.email = :mailAddress
            query;

        $statement = databaseConnection()->prepare($query);
        $statement->bindParam(':mailAddress', $mailAddress);
        $statement->execute();
        $registerMemberId = array_column($statement->fetchAll(PDO::FETCH_ASSOC), 'member_id');

        $videoList = [];
        $channelNameList = [];
        foreach ($videoLiveDetailList as $videoLiveDetai) {

            if (in_array($videoLiveDetai['member']['member_id'], $registerMemberId)) {

                $channelNameList[] = $videoLiveDetai['member']['channel_name'];

                $date = new DateTime($videoLiveDetai['liveStreamingDetails']['scheduledStartTime']);
                $date->setTimezone(new DateTimeZone("Asia/Tokyo"));
                $date = $date->format("Y-m-d H:i");

                $videoList[] = <<<TEXT
                <li><p>{$videoLiveDetai['snippet']['title']}</p></li>
                <li><p>開始時間：$date</p></li>
                <li><a href="https://www.youtube.com/watch?v={$videoLiveDetai['id']}">https://www.youtube.com/watch?v={$videoLiveDetai['id']}</a></li>
                <p class="section-line">--------------------------------------------------------------------</p>
                TEXT;
            }
        }

        $channelNameList = array_unique($channelNameList);
        $channelName = implode(" ", $channelNameList);
        $subject = "{$channelName}の配信が下記日程に始まります！";
        $mail->Subject = $subject;

        $mailTemplate = file_get_contents(__DIR__ . "/contents.html");
        $videoInfo = implode('', $videoList);
        $body = str_replace("{{videoList}}", $videoInfo, $mailTemplate);
        $mail->Body = $body;

        $mail->send();
        $mail->clearAddresses();
    }

    $mail->smtpClose();

} catch (Exception $message) {

    ErrorMail::send($message->errorMessage(), 'ReminderEmailFailure', 'リマインダーメール送信エラー');
    exit;
}

ChannelからはVtuberのチャンネル情報を取得します。

Channel.php
<?php

require __DIR__ . '/../databaseConfig.php';
require __DIR__ . '/../error/ErrorMail.php';

/**
 * Vtuberのチャンネル情報を取得するクラス
 */
class Channel
{
    private $reminRegisterList;

    /**
     * リマインダーに登録されたVtuberのIDを初期化
     */
    public function __construct()
    {
        try {

            $stmt = databaseConnection()->prepare('SELECT * FROM reminder_register');
            $stmt->execute();
            $this->reminRegisterList = $stmt->fetchAll(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {

            ErrorMail::send($e->getTraceAsString(), 'connectionFailure', 'データーベース接続エラー');
            exit;
        }

        empty($this->reminRegisterList) ? exit : '';
    }

    /**
     * メンバーIDからチャンネルIDを取得
     */
    public function getIdList(): array
    {
        try {
            $memberIdList = array_values(array_unique(array_column($this->reminRegisterList, 'member_id')));
            $placeholders = implode(',', array_fill(0, count($memberIdList), '?'));
            $query = "SELECT * FROM (
	            SELECT id, channel_id FROM hololive_member
	            UNION ALL
	            SELECT id, channel_id FROM nizisanzi_member
	            UNION ALL
	            SELECT id, channel_id FROM vspo_member
            ) AS merged WHERE id IN($placeholders) ORDER BY id ASC";

            $stmt = databaseConnection()->prepare($query);
            $stmt->execute($memberIdList);
            $channelIdList = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'channel_id');

        } catch (PDOException $e) {

            ErrorMail::send($e->getTraceAsString(), 'connectionFailure', 'データーベース接続エラー');
            exit;
        } catch (Exception $e) {

            ErrorMail::send($e->getMessage(), 'getIdListFailure', 'getIdListFailure');
            exit;
        }

        return $channelIdList;
    }

    /**
     * チャンネルの最新アクティビティIDを取得
     */
    public function getActivitieId(array $channelIdList): array
    {

        $date = new DateTimeImmutable('now', new DateTimeZone('UTC'));
        $publishedAfter = $date->modify('-5 minute');
        $publishedAfter = $publishedAfter->format('Y-m-d\TH:i:s\Z');
        $publishedBefore = $date->format('Y-m-d\TH:i:s\Z');

        $videoIdList = [];
        $multiHandler = curl_multi_init();
        $handles = [];

        foreach ($channelIdList as $channelId) {

            $parameter = [
                "part" => "snippet,contentDetails",
                "channelId" => $channelId,
                "publishedAfter" => $publishedAfter,
                "publishedBefore" => $publishedBefore,
                "fields" => "items(snippet(type),contentDetails(upload(videoId)))",
                "key" => $_ENV['APIKEY']
            ];

            $url = "https://www.googleapis.com/youtube/v3/activities" . "?" . http_build_query($parameter);
            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_multi_add_handle($multiHandler, $ch);

            $handles[] = $ch;
        }


        $active = null;
        do {
            $mrc = curl_multi_exec($multiHandler, $active);
        } while ($mrc == CURLM_CALL_MULTI_PERFORM);

        while ($active && $mrc == CURLM_OK) {
            if (curl_multi_select($multiHandler) != -1) {
                do {

                    $mrc = curl_multi_exec($multiHandler, $active);

                } while ($mrc == CURLM_CALL_MULTI_PERFORM);
            }
        }

        foreach ($handles as $ch) {

            try {
                $response = curl_multi_getcontent($ch);
                $httpcode = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);

                if (curl_errno($ch)) {
                    $error = curl_error($ch);
                    throw new Exception("{$error}");
                }

                if ($httpcode != 200) {
                    $responseData = json_decode($response, true);
                    throw new Exception("code:{$httpcode} message:{$responseData['error']['message']}");
                }

                curl_multi_remove_handle($multiHandler, $ch);
                curl_close($ch);

            } catch (Exception $e) {

                ErrorMail::send($e->getMessage(), 'getActivitieIdFailure', 'getActivitieIdFailure');
                exit;
            }

            $memberActivity = json_decode($response, true);

            if (empty($memberActivity['items'])) {
                continue;
            }

            foreach ($memberActivity['items'] as $activity) {

                if ($activity['snippet']['type'] === 'upload') {
                    $videoIdList[] = $activity['contentDetails']['upload']['videoId'];
                }
            }
        }

        curl_multi_close($multiHandler);

        if (empty($videoIdList)) {
            exit;
        }

        return $videoIdList;
    }

    /**
     * ビデオライブの詳細を取得
     */
    public function getlVideoLiveDetail(array $videoIdList): array
    {
        $video = implode(",", $videoIdList);
        $parameter = [
            "part" => "snippet,liveStreamingDetails",
            "id" => $video,
            "fields" => "items(id,snippet(title,channelId,liveBroadcastContent),liveStreamingDetails(scheduledStartTime))",
            "key" => $_ENV['APIKEY']
        ];

        try {

            $url = "https://www.googleapis.com/youtube/v3/videos" . "?" . http_build_query($parameter);
            $initialize = curl_init();

            curl_setopt($initialize, CURLOPT_URL, $url);
            curl_setopt($initialize, CURLOPT_RETURNTRANSFER, true);

            $response = curl_exec($initialize);
            $httpcode = curl_getinfo($initialize, CURLINFO_RESPONSE_CODE);

            if (curl_errno($initialize)) {
                $error = curl_error($initialize);
                throw new Exception("{$error}");
            }

            if ($httpcode != 200) {
                $responseData = json_decode($response, true);
                throw new Exception("code:{$httpcode} message:{$responseData['error']['message']}");
            }

            curl_close($initialize);
            $responseData = json_decode($response, true);

        } catch (Exception $e) {

            ErrorMail::send($e->getMessage(), 'getlVideoLiveDetailFailure', 'getlVideoLiveDetailFailure');
            exit;
        }

        $videoDetailList = [];
        $member = [];
        $query = "SELECT id AS member_id, channel_name FROM (
            SELECT id, channel_name, channel_id FROM hololive_member
            UNION ALL
            SELECT id, channel_name, channel_id FROM nizisanzi_member
            UNION ALL
            SELECT id, channel_name, channel_id FROM vspo_member
        ) AS merged WHERE channel_id = ? ORDER BY id ASC";

        foreach ($responseData['items'] as $detail) {
            if ($detail['snippet']['liveBroadcastContent'] === 'upcoming' && !empty($detail['liveStreamingDetails']['scheduledStartTime'])) {
                try {

                    $stmt = databaseConnection()->prepare($query);
                    $stmt->execute([$detail['snippet']['channelId']]);
                    $member['member'] = $stmt->fetch(PDO::FETCH_ASSOC);
                    $detail = array_merge($detail, $member);
                    $videoDetailList[] = $detail;

                } catch (PDOException $e) {

                    ErrorMail::send($e->getTraceAsString(), 'connectionFailure', 'データーベース接続エラー');
                    exit;
                }
            }
        }

        if (empty($videoDetailList)) {
            exit;
        }

        return $videoDetailList;
    }
}

まとめ：

非エンジニアですが、5年前にプログラミングを始めて2年前はSlerとして従事していました。
退職してからは、プログラミングに触れていませんでした。しかし今回アプリを作ろうと考えて、いざ始めてみるとやっぱりプログラミングは楽しいですね。
Vtuber関連のアプリを次も作りたいなと考えています。もしくはいまのアプリにもう少し手を加えるか。

X(Twitter)

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?