Due date Notify to Mattermost by php program

[sgkohata]

There is no built-in notification feature for deadlines, so I created one myself using PHP. Feel free to use it as you like.

need alter table

  alter table card add notify_before_30_sended boolean not null default false;
  alter table card add notify_before_10_sended boolean not null default false;
  alter table card add notify_before_0_sended boolean not null default false;

run by cron

* * * * * /usr/bin/php notify.php >>notify.log 2>&1

notify.php

<?php
$url    = 'https://your.host.url';
$dbhost = 'host';
$dbname = 'name';
$dbuser = 'user';
$dbpass = 'pass';
$mm_url = 'https://your.mattermost.url';

function _post($url, $param)
{
    $curl=curl_init($url);
    curl_setopt($curl,CURLOPT_POST,           TRUE);
    curl_setopt($curl,CURLOPT_POSTFIELDS,     json_encode($param));
    curl_setopt($curl,CURLOPT_SSL_VERIFYPEER, FALSE);
    curl_setopt($curl,CURLOPT_SSL_VERIFYHOST, FALSE);
    curl_setopt($curl,CURLOPT_RETURNTRANSFER, TRUE);
    curl_setopt($curl,CURLOPT_COOKIEJAR,      'cookie');
    curl_setopt($curl,CURLOPT_COOKIEFILE,     'tmp');
    curl_setopt($curl,CURLOPT_FOLLOWLOCATION, TRUE);
    curl_setopt($curl,CURLOPT_REFERER,        "REFERER");
    curl_setopt($curl,CURLOPT_USERAGENT,      "USER_AGENT");
    curl_setopt($curl,CURLOPT_TIMEOUT,        5);
    curl_setopt($curl,CURLOPT_HTTPHEADER,     [
        'Content-Type:application/json',
    ]);
    $response = curl_exec($curl);

    if ($response === false) {
        curl_close($curl);
        return false;
    }

    $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);
    if ($httpCode >= 200 && $httpCode < 300) {
        return true;
    }
    return false;
}

function getCardMembers($pdo, $id)
{
    $query = "SELECT
                user_account.*
              FROM
                card_membership
                join user_account
                  on card_membership.user_id=user_account.id
              WHERE
                card_membership.card_id=:id
    ";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
    $members = [];
    foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $v) {
        $members[] = $v['name'];
    }
    return implode(',', $members);
}

function toJst($date)
{
    $utcTimezone = new DateTimeZone('UTC');
    $date        = new DateTime($date, $utcTimezone);
    $jstTimezone = new DateTimeZone('Asia/Tokyo');
    $date->setTimezone($jstTimezone);
    return $date->format('Y-m-d H:i:s');
}

function recoverMinutes($min)
{
    global $url;
    global $dbhost;
    global $dbname;
    global $dbuser;
    global $dbpass;
    global $mm_url;

    if ($min != 0  &&
        $min != 10 &&
        $min != 30)
        return;

    try {
        $pdo = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $query = "SELECT
                    *
                  FROM
                    card
                  WHERE
                    is_due_date_completed=false
                    and due_date is not null";

        if ($min == 0) {
            $query .= " and notify_before_0_sended=true"
                   .  " and due_date > NOW() + INTERVAL '0 minutes'";
        }
        else if ($min == 10) {
            $query .= " and notify_before_10_sended=true"
                   .  " and due_date > NOW() + INTERVAL '10 minutes'";
        }
        else if ($min == 30) {
            $query .= " and notify_before_30_sended=true"
                   .  " and due_date > NOW() + INTERVAL '30 minutes'";
        }

        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            foreach ($results as $row) {
                if ($min == 0) {
                    $sql = "UPDATE card SET notify_before_0_sended=false WHERE id=:id";
                }
                else if ($min == 10) {
                    $sql = "UPDATE card SET notify_before_10_sended=false WHERE id=:id";
                }
                else if ($min == 30) {
                    $sql = "UPDATE card SET notify_before_30_sended=false WHERE id=:id";
                }
                $stmt = $pdo->prepare($sql);
                $stmt->bindParam(':id', $row['id'], PDO::PARAM_INT);
                $stmt->execute();
            }
        }
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
}

function processMinutesAgo($min)
{
    global $url;
    global $dbhost;
    global $dbname;
    global $dbuser;
    global $dbpass;
    global $mm_url;

    if ($min != 0  &&
        $min != 10 &&
        $min != 30)
        return;

    try {
        $pdo = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $query = "SELECT
                    *
                  FROM
                    card
                  WHERE
                    is_due_date_completed=false
                    and due_date is not null";

        if ($min == 0) {
            $query .= " and notify_before_0_sended=false"
                   .  " and due_date <= NOW() + INTERVAL '0 minutes'";
        }
        else if ($min == 10) {
            $query .= " and notify_before_10_sended=false"
                   .  " and due_date <= NOW() + INTERVAL '10 minutes'";
        }
        else if ($min == 30) {
            $query .= " and notify_before_30_sended=false"
                   .  " and due_date <= NOW() + INTERVAL '30 minutes'";
        }

        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            foreach ($results as $row) {
                $members = getCardMembers($pdo, $row['id']);

                // 通知
                if ($min == 0)
                    $header = "Expired\n";
                else if ($min == 10)
                    $header = "Deadline approaching soon (10 minutes left)\n";
                else if ($min == 30)
                    $header = "Deadline approaching soon (30 minutes left)\n";

                $text = $header
                      . "Task:".$row['name']."\n"
                      . "DeadLine:".toJst($row['due_date'])."\n"
                      . "Person:".$members."\n"
                      . $url."/cards/".$row['id'];
                if (_post($mm_url, ['username'=>'Planka', 'text'=>$text]) == true) {
                    if ($min == 0)
                        $sql  = "UPDATE card SET notify_before_0_sended=true WHERE id=:id";
                    else if ($min == 10)
                        $sql  = "UPDATE card SET notify_before_10_sended=true WHERE id=:id";
                    else if ($min == 30)
                        $sql  = "UPDATE card SET notify_before_30_sended=true WHERE id=:id";
                    $stmt = $pdo->prepare($sql);
                    $stmt->bindParam(':id', $row['id'], PDO::PARAM_INT);
                    $stmt->execute();
        }
            }
        }
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
}

recoverMinutes(30);
recoverMinutes(10);
recoverMinutes(0);
processMinutesAgo(30);
processMinutesAgo(10);
processMinutesAgo(0);

[seals187]

Hello sgkohata

Wow that's really cool! Thank you for sharing this with us =)

best regards
Julien