AbstractMetricRepository.php

<?php

/*
 * This file is part of Cachet.
 *
 * (c) Alt Three Services Limited
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CachetHQ\Cachet\Repositories\Metric;

use CachetHQ\Cachet\Models\Metric;
use Illuminate\Contracts\Config\Repository;

/**
 * This is the abstract metric repository class.
 *
 * @author Jams Brooks <james@alt-three.com>
 */
abstract class AbstractMetricRepository
{
    /**
     * The illuminate config repository.
     *
     * @var \Illuminate\Contracts\Config\Repository
     */
    protected $config;

    /**
     * The table prefix.
     * 
     * @var string
     */
    protected $prefix;

    /**
     * Create a new abstract metric repository instance.
     *
     * @param \Illuminate\Contracts\Config\Repository $config
     *
     * @return void
     */
    public function __construct(Repository $config)
    {
        $this->config = $config;
    }

    /**
     * Get a fully qualified table name.
     * 
     * @param string $table
     *
     * @return string
     */
    protected function getTableName($table)
    {
        $prefix = $this->getTablePrefix();

        return "{$table}{$table}";
    }

    /**
     * Get the configured database table prefix.
     * 
     * @return string
     */
    protected function getTablePrefix()
    {
        if ($this->prefix === null) {
            $driver = $this->config->get('database.default');
            $connection = $this->config->get('database.connections.'.$driver);

            $this->prefix = $connection['prefix'];
        }

        return $this->prefix;
    }
}

MySqlRepository.php

<?php

/*
 * This file is part of Cachet.
 *
 * (c) Alt Three Services Limited
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CachetHQ\Cachet\Repositories\Metric;

use CachetHQ\Cachet\Models\Metric;
use DateInterval;
use Illuminate\Support\Facades\DB;
use Jenssegers\Date\Date;

/**
 * This is the mysql repository class.
 *
 * @author James Brooks <james@alt-three.com>
 */
class MySqlRepository extends AbstractMetricRepository implements MetricInterface
{
    /**
     * Returns metrics for the last hour.
     *
     * @param \CachetHQ\Cachet\Models\Metric $metric
     * @param int                            $hour
     * @param int                            $minute
     *
     * @return int
     */
    public function getPointsLastHour(Metric $metric, $hour, $minute)
    {
        $dateTime = (new Date())->sub(new DateInterval('PT'.$hour.'H'))->sub(new DateInterval('PT'.$minute.'M'));
        $timeInterval = $dateTime->format('YmdHi');

        if (!isset($metric->calc_type) || $metric->calc_type == Metric::CALC_SUM) {
            $queryType = 'SUM(mp.`value` * mp.`counter`) AS `value`';
        } elseif ($metric->calc_type == Metric::CALC_AVG) {
            $queryType = 'AVG(mp.`value` * mp.`counter`) AS `value`';
        }

        $value = 0;

        $points = DB::select("SELECT {$queryType} FROM {$this->getTableName('metrics_base')} m INNER JOIN {$this->getTableName('metric_points')} mp ON m.id = mp.metric_id WHERE m.id = :metricId AND DATE_FORMAT(mp.`created_at`, '%Y%m%d%H%i') = :timeInterval GROUP BY HOUR(mp.`created_at`), MINUTE(mp.`created_at`)", [
            'metricId'     => $metric->id,
            'timeInterval' => $timeInterval,
        ]);

        if (isset($points[0]) && !($value = $points[0]->value)) {
            $value = 0;
        }

        if ($value === 0 && $metric->default_value != $value) {
            return $metric->default_value;
        }

        return round($value, $metric->places);
    }

    /**
     * Returns metrics for a given hour.
     *
     * @param \CachetHQ\Cachet\Models\Metric $metric
     * @param int                            $hour
     *
     * @return int
     */
    public function getPointsByHour(Metric $metric, $hour)
    {
        $dateTime = (new Date())->sub(new DateInterval('PT'.$hour.'H'));
        $hourInterval = $dateTime->format('YmdH');

        if (!isset($metric->calc_type) || $metric->calc_type == Metric::CALC_SUM) {
            $queryType = 'SUM(mp.`value` * mp.`counter`) AS `value`';
        } elseif ($metric->calc_type == Metric::CALC_AVG) {
            $queryType = 'AVG(mp.`value` * mp.`counter`) AS `value`';
        }

        $value = 0;

        $points = DB::select("SELECT {$queryType} FROM {$this->getTableName('metrics_base')} m INNER JOIN {$this->getTableName('metric_points')} mp ON m.id = mp.metric_id WHERE m.id = :metricId AND DATE_FORMAT(mp.`created_at`, '%Y%m%d%H') = :hourInterval GROUP BY HOUR(mp.`created_at`)", [
            'metricId'     => $metric->id,
            'hourInterval' => $hourInterval,
        ]);

        if (isset($points[0]) && !($value = $points[0]->value)) {
            $value = 0;
        }

        if ($value === 0 && $metric->default_value != $value) {
            return $metric->default_value;
        }

        return round($value, $metric->places);
    }

    /**
     * Returns metrics for the week.
     *
     * @param \CachetHQ\Cachet\Models\Metric $metric
     *
     * @return int
     */
    public function getPointsForDayInWeek(Metric $metric, $day)
    {
        $dateTime = (new Date())->sub(new DateInterval('P'.$day.'D'));

        if (!isset($metric->calc_type) || $metric->calc_type == Metric::CALC_SUM) {
            $queryType = 'SUM(mp.`value` * mp.`counter`) AS `value`';
        } elseif ($metric->calc_type == Metric::CALC_AVG) {
            $queryType = 'AVG(mp.`value` * mp.`counter`) AS `value`';
        }

        $value = 0;

        $points = DB::select("SELECT {$queryType} FROM {$this->getTableName('metrics_base')} m INNER JOIN {$this->getTableName('metric_points')} mp ON m.id = mp.metric_id WHERE m.id = :metricId AND mp.`created_at` BETWEEN DATE_SUB(mp.`created_at`, INTERVAL 1 WEEK) AND DATE_ADD(NOW(), INTERVAL 1 DAY) AND DATE_FORMAT(mp.`created_at`, '%Y%m%d') = :timeInterval GROUP BY DATE_FORMAT(mp.`created_at`, '%Y%m%d')", [
            'metricId'     => $metric->id,
            'timeInterval' => $dateTime->format('Ymd'),
        ]);

        if (isset($points[0]) && !($value = $points[0]->value)) {
            $value = 0;
        }

        if ($value === 0 && $metric->default_value != $value) {
            return $metric->default_value;
        }

        return round($value, $metric->places);
    }
}