plugins/CustomAlerts/Model.php

<?php

/**
 * Matomo - free/libre analytics platform
 *
 * @link    https://matomo.org
 * @license http://www.gnu.org/licenses/gpl-3.0.html Gpl v3 or later
 */

namespace Piwik\Plugins\CustomAlerts;

use Piwik\Common;
use Piwik\Date;
use Piwik\Db;
use Piwik\DbHelper;
use Piwik\Period;

/**
 *
 */
class Model
{
    public static function install()
    {
        $tableAlert = "`idalert` INT NOT NULL PRIMARY KEY ,
                       `name` VARCHAR(100) NOT NULL ,
                       `description` VARCHAR(255) NOT NULL DEFAULT '',
                       `login` VARCHAR(100) NOT NULL ,
                       `period` VARCHAR(5) NOT NULL ,
                       `report` VARCHAR(150) NOT NULL ,
                       `report_condition` VARCHAR(50) ,
                       `report_matched` VARCHAR(255) ,
                       `report_mediums` TEXT ,
                       `metric` VARCHAR(150) NOT NULL ,
                       `metric_condition` VARCHAR(50) NOT NULL ,
                       `metric_matched` FLOAT NOT NULL ,
                       `compared_to` SMALLINT (4) UNSIGNED NOT NULL DEFAULT 1 ,
                       `email_me` BOOLEAN NOT NULL DEFAULT '0',
                       `additional_emails` TEXT ,
                       `phone_numbers` TEXT ,
                       `slack_channel_id` VARCHAR(50) NULL ,
                       `ms_teams_webhook_url` VARCHAR(500) NULL ";

        DbHelper::createTable('alert', $tableAlert);

        $tableAlertSite = "`idalert` INT( 11 ) NOT NULL ,
                           `idsite` INT( 11 ) NOT NULL ,
                           PRIMARY KEY ( idalert, idsite )";

        DbHelper::createTable('alert_site', $tableAlertSite);

        $tableAlertLog = "`idtriggered` BIGINT unsigned NOT NULL AUTO_INCREMENT,
			              `idalert` INT( 11 ) NOT NULL ,
			              `idsite` INT( 11 ) NOT NULL ,
			              `ts_triggered` timestamp NOT NULL default CURRENT_TIMESTAMP,
			              `ts_last_sent` timestamp NULL DEFAULT NULL,
			              `value_old` DECIMAL (20,3) DEFAULT NULL,
			              `value_new` DECIMAL (20,3) DEFAULT NULL,
                          `name` VARCHAR(100) NOT NULL ,
                          `description` VARCHAR(255) NOT NULL DEFAULT '',
			              `login` VARCHAR(100) NOT NULL ,
			              `period` VARCHAR(5) NOT NULL ,
			              `report` VARCHAR(150) NOT NULL ,
			              `report_condition` VARCHAR(50) ,
			              `report_matched` VARCHAR(1000) ,
			              `report_mediums` TEXT ,
			              `metric` VARCHAR(150) NOT NULL ,
			              `metric_condition` VARCHAR(50) NOT NULL ,
			              `metric_matched` FLOAT NOT NULL ,
			              `compared_to` SMALLINT NOT NULL DEFAULT 1 ,
			              `email_me` BOOLEAN NOT NULL  DEFAULT '0',
			              `additional_emails` TEXT ,
			              `phone_numbers` TEXT ,
			              `slack_channel_id` VARCHAR(50) NULL ,
			              `ms_teams_webhook_url` VARCHAR(500) NULL ,
			              PRIMARY KEY (idtriggered)";

        DbHelper::createTable('alert_triggered', $tableAlertLog);
    }

    public static function uninstall()
    {
        Db::dropTables(array(
            Common::prefixTable('alert'),
            Common::prefixTable('alert_triggered'),
            Common::prefixTable('alert_site')
        ));
    }

    /**
     * Returns the Alerts that are defined on the idSites given.
     *
     * @param array       $idSites
     * @param bool|string $login false returns alerts for all users
     *
     * @return array
     */
    public function getAlerts($idSites, $login = false)
    {
        $sql    = ("SELECT * FROM " . Common::prefixTable('alert')
            . " WHERE idalert IN (" . $this->getInnerSiteQuery($idSites) . ") ");
        $values = array();

        if ($login !== false) {
            $sql      .= " AND login = ?";
            $values[] = $login;
        }

        $alerts = Db::fetchAll($sql, $values);
        $alerts = $this->completeAlerts($alerts);

        return $alerts;
    }

    /**
     * @param $idSites
     * @return string
     */
    protected function getInnerSiteQuery($idSites)
    {
        $idSites = array_map('intval', $idSites);

        $innerSiteQuery = "SELECT idalert FROM " . Common::prefixTable('alert_site')
            . " WHERE idsite IN (" . implode(",", $idSites) . ")";

        return $innerSiteQuery;
    }

    private function completeAlerts($alerts)
    {
        foreach ($alerts as &$alert) {
            $alert['additional_emails'] = json_decode($alert['additional_emails']);
            $alert['phone_numbers']     = json_decode($alert['phone_numbers']);
            $alert['report_mediums']     = json_decode($alert['report_mediums']);
            $alert['email_me']          = (int)$alert['email_me'];
            $alert['compared_to']       = (int)$alert['compared_to'];
            $alert['id_sites']          = $this->getDefinedSiteIds($alert['idalert']);
        }

        return $alerts;
    }

    private function getDefinedSiteIds($idAlert)
    {
        $sql   = "SELECT idsite FROM " . Common::prefixTable('alert_site') . " WHERE idalert = ?";
        $sites = Db::fetchAll($sql, $idAlert);

        $idSites = array();
        foreach ($sites as $site) {
            $idSites[] = $site['idsite'];
        }

        return $idSites;
    }

    public function getTriggeredAlertsForPeriod($period, $date)
    {
        $piwikDate = Date::factory($date);
        $date      = Period\Factory::build($period, $piwikDate);

        $db  = $this->getDb();
        $sql = $this->getTriggeredAlertsSelectPart()
            . " WHERE  period = ? AND ts_triggered BETWEEN ? AND ?";

        $values = array(
            $period,
            $date->getDateStart()->getDateStartUTC(),
            $date->getDateEnd()->getDateEndUTC()
        );

        $alerts = $db->fetchAll($sql, $values);
        $alerts = $this->completeAlerts($alerts);

        return $alerts;
    }

    private function getDb()
    {
        return Db::get();
    }

    private function getTriggeredAlertsSelectPart()
    {
        return "SELECT * FROM   " . Common::prefixTable('alert_triggered');
    }

    public function getTriggeredAlerts($idSites, $login)
    {
        $idSites = array_map('intval', $idSites);

        $db  = $this->getDb();
        $sql = $this->getTriggeredAlertsSelectPart()
            . " WHERE idsite IN (" . Common::getSqlStringFieldsArray($idSites) . ")"
            . " AND login = ?";

        $values   = $idSites;
        $values[] = $login;

        $alerts = $db->fetchAll($sql, $values);
        $alerts = $this->completeAlerts($alerts);

        return $alerts;
    }

    public function getTriggeredAlertsFromPastNHours(string $period, int $idSite, int $hours)
    {
        $timestamp = Date::now()->addHour(-1 * $hours)->getDatetime();

        $db  = $this->getDb();
        $sql = $this->getTriggeredAlertsSelectPart()
            . " WHERE idsite = ?"
            . " AND  period = ?"
            . " AND  ts_triggered > ?";

        $values = [$idSite, $period, $timestamp];

        $alerts = $db->fetchAll($sql, $values);

        return $this->completeAlerts($alerts);
    }

    public function getAllAlerts()
    {
        $sql = "SELECT * FROM " . Common::prefixTable('alert');

        $alerts = Db::fetchAll($sql);
        $alerts = $this->completeAlerts($alerts);

        return $alerts;
    }

    public function getAllAlertsForPeriod($period)
    {
        $sql = "SELECT * FROM " . Common::prefixTable('alert') . " WHERE period = ?";

        $alerts = Db::fetchAll($sql, array($period));
        $alerts = $this->completeAlerts($alerts);

        return $alerts;
    }

    /**
     * Creates an Alert for given website(s).
     *
     * @param string $name
     * @param int[]  $idSites
     * @param string $login
     * @param string $period
     * @param bool   $emailMe
     * @param array  $additionalEmails
     * @param array  $phoneNumbers
     * @param string $metric (nb_uniq_visits, sum_visit_length, ..)
     * @param string $metricCondition
     * @param float  $metricValue
     * @param int    $comparedTo
     * @param string $reportUniqueId
     * @param string $reportCondition
     * @param string $reportValue
     * @param array $reportMediums
     * @param string $slackChannelID
     * @param string $msTeamsWebhookUrl
     *
     * @return int ID of new Alert
     * @throws \Exception
     */
    public function createAlert($name, $idSites, $login, $period, $emailMe, $additionalEmails, $phoneNumbers, $metric, $metricCondition, $metricValue, $comparedTo, $reportUniqueId, $reportCondition, $reportValue, $reportMediums, $slackChannelID, $msTeamsWebhookUrl, $description = '')
    {
        $idAlert = $this->getNextAlertId();

        $newAlert = array(
            'idalert'           => $idAlert,
            'name'              => $name,
            'description'       => $description,
            'period'            => $period,
            'login'             => $login,
            'email_me'          => $emailMe ? 1 : 0,
            'additional_emails' => json_encode($additionalEmails),
            'phone_numbers'     => json_encode($phoneNumbers),
            'metric'            => $metric,
            'metric_condition'  => $metricCondition,
            'metric_matched'    => $metricValue,
            'report'            => $reportUniqueId,
            'compared_to'       => $comparedTo,
            'report_condition'  => $reportCondition,
            'report_matched'    => $reportValue,
            'report_mediums'    => json_encode($reportMediums),
            'slack_channel_id'  => $slackChannelID,
            'ms_teams_webhook_url'  => $msTeamsWebhookUrl,
        );

        $db = $this->getDb();
        $db->insert(Common::prefixTable('alert'), $newAlert);

        $this->setSiteIds($idAlert, $idSites);

        return $idAlert;
    }

    private function getNextAlertId()
    {
        $idAlert = Db::fetchOne("SELECT max(idalert) + 1 FROM " . Common::prefixTable('alert'));

        if (empty($idAlert)) {
            $idAlert = 1;
        }

        return $idAlert;
    }

    public function setSiteIds($idAlert, $idSites)
    {
        $this->removeAllSites($idAlert);

        $db = $this->getDb();
        foreach ($idSites as $idSite) {
            $db->insert(Common::prefixTable('alert_site'), array(
                'idalert' => intval($idAlert),
                'idsite'  => intval($idSite)
            ));
        }
    }

    private function removeAllSites($idAlert)
    {
        $this->getDb()->query("DELETE FROM " . Common::prefixTable("alert_site") . " WHERE idalert = ?", $idAlert);
    }

    /**
     * Edits an Alert for given website(s).
     *
     * @param int    $idAlert
     * @param string $name    Name of Alert
     * @param int[]  $idSites array of ints of idSites.
     * @param string $period  Period the alert is defined on.
     * @param bool   $emailMe
     * @param array  $additionalEmails
     * @param array  $phoneNumbers
     * @param string $metric  (nb_uniq_visits, sum_visit_length, ..)
     * @param string $metricCondition
     * @param float  $metricValue
     * @param int    $comparedTo
     * @param string $reportUniqueId
     * @param string $reportCondition
     * @param string $reportValue
     * @param array $reportMediums
     * @param string $slackChannelID
     * @param string $msTeamsWebhookUrl
     *
     * @return int
     * @throws \Exception
     */
    public function updateAlert($idAlert, $name, $idSites, $period, $emailMe, $additionalEmails, $phoneNumbers, $metric, $metricCondition, $metricValue, $comparedTo, $reportUniqueId, $reportCondition, $reportValue, $reportMediums, $slackChannelID, $msTeamsWebhookUrl, $description = '')
    {
        $alert = array(
            'name'              => $name,
            'description'       => $description,
            'period'            => $period,
            'email_me'          => $emailMe ? 1 : 0,
            'additional_emails' => json_encode($additionalEmails),
            'phone_numbers'     => json_encode($phoneNumbers),
            'metric'            => $metric,
            'metric_condition'  => $metricCondition,
            'metric_matched'    => $metricValue,
            'report'            => $reportUniqueId,
            'compared_to'       => $comparedTo,
            'report_condition'  => $reportCondition,
            'report_matched'    => $reportValue,
            'report_mediums'    => json_encode($reportMediums),
            'slack_channel_id'  => $slackChannelID,
            'ms_teams_webhook_url'  => $msTeamsWebhookUrl,
        );

        $db = $this->getDb();
        $db->update(Common::prefixTable('alert'), $alert, "idalert = " . intval($idAlert));

        $this->setSiteIds($idAlert, $idSites);

        return $idAlert;
    }

    /**
     * Delete alert by id.
     *
     * @param int $idAlert
     *
     * @throws \Exception In case alert does not exist or not enough permission
     */
    public function deleteAlert($idAlert)
    {
        $db = $this->getDb();
        $db->query("DELETE FROM " . Common::prefixTable("alert") . " WHERE idalert = ?", array($idAlert));
        $db->query("DELETE FROM " . Common::prefixTable("alert_triggered") . " WHERE idalert = ?", array($idAlert));
        $this->removeAllSites($idAlert);
    }

    public function triggerAlert($idAlert, $idSite, $valueNew, $valueOld, $datetime)
    {
        $alert = $this->getAlert($idAlert);

        $keysToKeep = array('idalert', 'name', 'description', 'login', 'period', 'metric', 'metric_condition', 'metric_matched', 'report', 'report_condition', 'report_matched', 'report_mediums', 'compared_to', 'email_me', 'additional_emails', 'phone_numbers', 'slack_channel_id', 'ms_teams_webhook_url');

        $triggeredAlert = array();
        foreach ($keysToKeep as $key) {
            $triggeredAlert[$key] = $alert[$key];
        }

        $triggeredAlert['metric_matched']    = Common::forceDotAsSeparatorForDecimalPoint($triggeredAlert['metric_matched']);
        $triggeredAlert['ts_triggered']      = $datetime;
        $triggeredAlert['ts_last_sent']      = null;
        $triggeredAlert['value_new']         = $valueNew;
        $triggeredAlert['value_old']         = $valueOld;
        $triggeredAlert['idsite']            = $idSite;
        $triggeredAlert['additional_emails'] = json_encode($triggeredAlert['additional_emails']);
        $triggeredAlert['phone_numbers']     = json_encode($triggeredAlert['phone_numbers']);
        $triggeredAlert['report_mediums']    = json_encode($triggeredAlert['report_mediums']);

        $db = $this->getDb();
        $db->insert(
            Common::prefixTable('alert_triggered'),
            $triggeredAlert
        );
    }

    /**
     * Returns a single Alert
     *
     * @param int $idAlert
     *
     * @return array|null
     */
    public function getAlert($idAlert)
    {
        $query  = sprintf('SELECT * FROM %s WHERE idalert = ? LIMIT 1', Common::prefixTable('alert'));
        $alerts = Db::fetchAll($query, array(intval($idAlert)));

        if (empty($alerts)) {
            return null;
        }

        $alerts = $this->completeAlerts($alerts);
        return array_shift($alerts);
    }

    public function markTriggeredAlertAsSent($idTriggered, $timestamp)
    {
        $log = array(
            'ts_last_sent' => Date::factory($timestamp)->getDatetime()
        );

        $where = sprintf("idtriggered = %d", $idTriggered);

        $db = $this->getDb();
        $db->update(Common::prefixTable('alert_triggered'), $log, $where);
    }

    public function deleteTriggeredAlertsForSite($idSite)
    {
        $this->getDb()->query("DELETE FROM " . Common::prefixTable("alert_triggered") . " WHERE idsite = ?", $idSite);
    }

    public function deleteTriggeredAlertsForUserAndSites($idAlert, $idSites, $login)
    {
        $db = $this->getDb();
        $placeholders = Common::getSqlStringFieldsArray($idSites);
        $bind = array_merge(array($idAlert, $login), $idSites);
        $db->query(
            "DELETE FROM " . Common::prefixTable("alert_triggered") . " WHERE idalert = ? AND login = ? AND idsite in (" . $placeholders . " )",
            $bind
        );
    }

    public function deleteAlertSitesForSites($idAlert, $idSites)
    {
        $db = $this->getDb();
        $placeholders = Common::getSqlStringFieldsArray($idSites);
        $bind = array_merge(array($idAlert), $idSites);
        $db->query(
            "DELETE FROM " . Common::prefixTable("alert_site") . " WHERE idalert = ? AND idsite in (" . $placeholders . " )",
            $bind
        );
    }
}