芝麻web文件管理V1.00

编辑当前文件:/home/mgatv524/cms.mgaplay.com.br/lib/Storage/MySqlTimeSeriesStore.php

<?php
/*
 * Copyright (C) 2022 Xibo Signage Ltd
 *
 * Xibo - Digital Signage - http://www.xibo.org.uk
 *
 * This file is part of Xibo.
 *
 * Xibo is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * any later version.
 *
 * Xibo is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with Xibo.  If not, see <http://www.gnu.org/licenses/>.
 */

namespace Xibo\Storage;

use Carbon\Carbon;
use Xibo\Factory\CampaignFactory;
use Xibo\Factory\LayoutFactory;
use Xibo\Service\LogServiceInterface;
use Xibo\Support\Exception\GeneralException;
use Xibo\Support\Exception\InvalidArgumentException;

/**
 * Class MySqlTimeSeriesStore
 * @package Xibo\Storage
 */
class MySqlTimeSeriesStore implements TimeSeriesStoreInterface
{
    // Keep all stats in this array after processing
    private $stats = [];
    private $layoutCampaignIds = [];
    private $layoutIdsNotFound = [];

/** @var StorageServiceInterface */
    private $store;

/** @var LogServiceInterface */
    private $log;

/** @var  LayoutFactory */
    protected $layoutFactory;

/** @var  CampaignFactory */
    protected $campaignFactory;

/**
     * @inheritdoc
     */
    public function __construct($config = null)
    {

}

/**
     * @inheritdoc
     */
    public function setDependencies($log, $layoutFactory, $campaignFactory, $mediaFactory, $widgetFactory, $displayFactory, $displayGroupFactory)
    {
        $this->log = $log;
        $this->layoutFactory = $layoutFactory;
        $this->campaignFactory = $campaignFactory;
        return $this;
    }

/** @inheritdoc */
    public function addStat($statData)
    {

// For a type "event" we have layoutid 0 so is campaignId
        // otherwise we should try and resolve the campaignId
        $campaignId = 0;
        if ($statData['type'] != 'event') {

if (array_key_exists($statData['layoutId'], $this->layoutCampaignIds)) {
                $campaignId = $this->layoutCampaignIds[$statData['layoutId']];
            } else {

try {

// Get the layout campaignId
                    $campaignId = $this->layoutFactory->getCampaignIdFromLayoutHistory($statData['layoutId']);

// Put layout campaignId to memory
                    $this->layoutCampaignIds[$statData['layoutId']] = $campaignId;

} catch (GeneralException $error) {

if (!in_array($statData['layoutId'], $this->layoutIdsNotFound)) {
                        $this->layoutIdsNotFound[] = $statData['layoutId'];
                        $this->log->error('Layout not found. Layout Id: '. $statData['layoutId']);
                    }
                    return;
                }
            }
        }

// Set to Unix Timestamp
        $statData['statDate'] = $statData['statDate']->format('U');
        $statData['fromDt'] = $statData['fromDt']->format('U');
        $statData['toDt'] = $statData['toDt']->format('U');
        $statData['campaignId'] = $campaignId;
        $statData['displayId'] = $statData['display']->displayId;
        $statData['engagements'] = json_encode($statData['engagements']);
        unset($statData['display']);

$this->stats[] = $statData;

}

/** @inheritdoc */
    public function addStatFinalize()
    {
        if (count($this->stats) > 0) {
            $sql = '
                INSERT INTO `stat` (
                    `type`,
                    `statDate`,
                    `start`,
                    `end`,
                    `scheduleID`,
                    `displayID`,
                    `campaignID`,
                    `layoutID`,
                    `mediaID`,
                    `tag`,
                    `widgetId`,
                    `duration`,
                    `count`,
                    `engagements`,
                    `parentCampaignId`
                ) 
                VALUES ';

$placeHolders = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

$sql = $sql . implode(', ', array_fill(1, count($this->stats), $placeHolders));

// Flatten the array
            $data = [];
            foreach ($this->stats as $stat) {
                // Be explicit about the order of the keys
                $ordered = [
                    'type' => $stat['type'],
                    'statDate' => $stat['statDate'],
                    'fromDt' => $stat['fromDt'],
                    'toDt' => $stat['toDt'],
                    'scheduleId' => $stat['scheduleId'],
                    'displayId' => $stat['displayId'],
                    'campaignId' => $stat['campaignId'],
                    'layoutId' => $stat['layoutId'],
                    'mediaId' => $stat['mediaId'],
                    'tag' => $stat['tag'],
                    'widgetId' => $stat['widgetId'],
                    'duration' => $stat['duration'],
                    'count' => $stat['count'],
                    'engagements' => $stat['engagements'],
                    'parentCampaignId' => $stat['parentCampaignId'],
                ];

// Add each value to another array in order
                foreach ($ordered as $field) {
                    $data[] = $field;
                }
            }

$this->store->update($sql, $data);
        }
    }

/** @inheritdoc */
    public function getEarliestDate()
    {
        $result = $this->store->select('SELECT MIN(start) AS minDate FROM `stat`', []);
        $earliestDate = $result[0]['minDate'];

return ($earliestDate === null)
            ? null
            : Carbon::createFromFormat('U', $result[0]['minDate']);
    }

/** @inheritdoc */
    public function getStats($filterBy = [], $isBufferedQuery = false)
    {
        $fromDt = $filterBy['fromDt'] ?? null;
        $toDt = $filterBy['toDt'] ?? null;
        $statDate = $filterBy['statDate'] ?? null;
        $statDateLessThan = $filterBy['statDateLessThan'] ?? null;

// In the case of user switches from  mongo to mysql - laststatId were saved as Mongo ObjectId string
        if (isset($filterBy['statId'])) {
            if (!is_numeric($filterBy['statId'])) {
                throw new InvalidArgumentException(__('Invalid statId provided'), 'statId');
            } else {
                $statId = $filterBy['statId'];
            }
        } else {
            $statId = null;
        }

$type = $filterBy['type'] ?? null;
        $displayIds = $filterBy['displayIds'] ?? [];
        $layoutIds = $filterBy['layoutIds'] ?? [];
        $mediaIds = $filterBy['mediaIds'] ?? [];
        $campaignId = $filterBy['campaignId'] ?? null;
        $parentCampaignId = $filterBy['parentCampaignId'] ?? null;
        $mustHaveParentCampaign = $filterBy['mustHaveParentCampaign'] ?? false;
        $eventTag = $filterBy['eventTag'] ?? null;

// Tag embedding
        $embedDisplayTags = $filterBy['displayTags'] ?? false;
        $embedLayoutTags = $filterBy['layoutTags'] ?? false;
        $embedMediaTags = $filterBy['mediaTags'] ?? false;

// Limit
        $start = $filterBy['start'] ?? null;
        $length = $filterBy['length'] ?? null;

$params = [];
        $select = 'SELECT stat.statId, 
            stat.statDate, 
            stat.type, 
            stat.displayId, 
            stat.widgetId, 
            stat.layoutId, 
            stat.mediaId, 
            stat.campaignId, 
            stat.parentCampaignId, 
            stat.start as start, 
            stat.end as end, 
            stat.tag, 
            stat.duration, 
            stat.count, 
            stat.engagements, 
            display.Display as display, 
            layout.Layout as layout, 
            campaign.campaign as parentCampaign, 
            media.Name AS media ';

if ($embedDisplayTags) {
            $select .= ', 
                (
                  SELECT GROUP_CONCAT(DISTINCT CONCAT(tag, \'|\', IFNULL(value, \'null\'))) 
                    FROM tag 
                      INNER JOIN lktagdisplaygroup 
                      ON lktagdisplaygroup.tagId = tag.tagId 
                      INNER JOIN `displaygroup`
                      ON lktagdisplaygroup.displayGroupId = displaygroup.displayGroupId
                        AND `displaygroup`.isDisplaySpecific = 1 
                      INNER JOIN `lkdisplaydg`
                      ON lkdisplaydg.displayGroupId = displaygroup.displayGroupId
                   WHERE lkdisplaydg.displayId = stat.displayId 
                  GROUP BY lktagdisplaygroup.displayGroupId
                ) AS displayTags
            ';
        }

if ($embedMediaTags) {
            $select .= ', 
                (
                  SELECT GROUP_CONCAT(DISTINCT CONCAT(tag, \'|\', IFNULL(value, \'null\'))) 
                    FROM tag 
                      INNER JOIN lktagmedia 
                      ON lktagmedia.tagId = tag.tagId 
                   WHERE lktagmedia.mediaId = media.mediaId 
                  GROUP BY lktagmedia.mediaId
                ) AS mediaTags
            ';
        }

if ($embedLayoutTags) {
            $select .= ', 
                (
                  SELECT GROUP_CONCAT(DISTINCT CONCAT(tag, \'|\', IFNULL(value, \'null\'))) 
                    FROM tag 
                      INNER JOIN lktaglayout 
                      ON lktaglayout.tagId = tag.tagId 
                   WHERE lktaglayout.layoutId = layout.layoutId 
                  GROUP BY lktaglayout.layoutId
                ) AS layoutTags
            ';
        }

$body = '
        FROM stat
            LEFT OUTER JOIN display
            ON stat.DisplayID = display.DisplayID
            LEFT OUTER JOIN layout
            ON layout.LayoutID = stat.LayoutID
            LEFT OUTER JOIN campaign
            ON campaign.campaignID = stat.parentCampaignID
            LEFT OUTER JOIN media
            ON media.mediaID = stat.mediaID
            LEFT OUTER JOIN widget
            ON widget.widgetId = stat.widgetId
         WHERE 1 = 1 ';

// fromDt/toDt Filter
        if (($fromDt != null) && ($toDt != null)) {
            $body .= ' AND stat.end > '. $fromDt->format('U') . ' AND stat.start <= '. $toDt->format('U');
        } else if (($fromDt != null) && empty($toDt)) {
            $body .= ' AND stat.start >= '. $fromDt->format('U');
        }

// statDate Filter
        // get the next stats from the given date
        if ($statDate != null) {
            $body .= ' AND stat.statDate >= ' . $statDate->format('U');
        }

if ($statDateLessThan != null) {
            $body .= ' AND stat.statDate < ' . $statDateLessThan->format('U');
        }

if ($statId != null) {
            $body .= ' AND stat.statId > '. $statId;
        }

if (count($displayIds) > 0) {
            $body .= ' AND stat.displayID IN (' . implode(',', $displayIds) . ')';
        }

// Type filter
        if ($type == 'layout') {
            $body .= ' AND `stat`.type = \'layout\' ';
        } else if ($type == 'media') {
            $body .= ' AND `stat`.type = \'media\' AND IFNULL(`media`.mediaId, 0) <> 0 ';
        } else if ($type == 'widget') {
            $body .= ' AND `stat`.type = \'widget\' AND IFNULL(`widget`.widgetId, 0) <> 0 ';
        } else if ($type == 'event') {
            $body .= ' AND `stat`.type = \'event\' ';
        }

// Event Tag Filter
        if ($eventTag) {
            $body .= ' AND `stat`.tag = :eventTag';
            $params['eventTag'] = $eventTag;
        }

// Layout Filter
        if (count($layoutIds) != 0) {

$layoutSql = '';
            $i = 0;
            foreach ($layoutIds as $layoutId) {
                $i++;
                $layoutSql .= ':layoutId_' . $i . ',';
                $params['layoutId_' . $i] = $layoutId;
            }

$body .= '  AND `stat`.campaignId IN (SELECT campaignId FROM `layouthistory` WHERE layoutId IN (' . trim($layoutSql, ',') . ')) ';
        }

// Media Filter
        if (count($mediaIds) != 0) {

$mediaSql = '';
            $i = 0;
            foreach ($mediaIds as $mediaId) {
                $i++;
                $mediaSql .= ':mediaId_' . $i . ',';
                $params['mediaId_' . $i] = $mediaId;
            }

$body .= ' AND `media`.mediaId IN (' . trim($mediaSql, ',') . ')';
        }

// Parent Campaign Filter
        if ($parentCampaignId != null) {
            $body .= ' AND `stat`.parentCampaignId = :parentCampaignId ';
            $params['parentCampaignId'] = $parentCampaignId;
        }

// Has Parent Campaign Filter
        if ($mustHaveParentCampaign) {
            $body .= ' AND IFNULL(`stat`.parentCampaignId, 0) != 0 ';
        }

// Campaign
        // --------
        // Filter on Layouts linked to a Campaign
        if ($campaignId != null) {
            $body .= ' AND stat.campaignId IN (
                    SELECT lkcampaignlayout.campaignId 
                      FROM `lkcampaignlayout`
                        INNER JOIN `campaign`
                        ON `lkcampaignlayout`.campaignId = `campaign`.campaignId
                            AND `campaign`.isLayoutSpecific = 1
                        INNER JOIN `lkcampaignlayout` lkcl 
                        ON lkcl.layoutid = lkcampaignlayout.layoutId
                     WHERE lkcl.campaignId = :campaignId 
                ) ';
            $params['campaignId'] = $campaignId;
        }

// Sorting
        $body .= ' ORDER BY stat.statId ';

$limit = '';
        if ($start !== null && $length !== null) {
            $limit = ' LIMIT ' . $start . ', ' . $length;
        }

// Total count if paging is enabled.
        $totalNumberOfRecords = 0;
        if ($start !== null && $length !== null) {
            $totalNumberOfRecords = $this->store->select('
              SELECT COUNT(*) AS total FROM (   ' . $select . $body . ') total
            ', $params)[0]['total'];
        }

// Join our SQL statement together
        $sql = $select . $body. $limit;

// Write this to our log
        $this->log->sql($sql, $params);

// Run our query using a connection object (to save memory)
        $connection = $this->store->getConnection();
        $connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $isBufferedQuery);

// Prepare the statement
        $statement = $connection->prepare($sql);

// Execute
        $statement->execute($params);

// Create a results object and set the total number of records on it.
        $results = new TimeSeriesMySQLResults($statement);
        $results->totalCount = $totalNumberOfRecords;
        return $results;
    }

/** @inheritdoc */
    public function getExportStatsCount($filterBy = [])
    {

$fromDt = isset($filterBy['fromDt']) ? $filterBy['fromDt'] : null;
        $toDt = isset($filterBy['toDt']) ? $filterBy['toDt'] : null;
        $displayIds = isset($filterBy['displayIds']) ? $filterBy['displayIds'] : [];

$params = [];
        $sql = ' SELECT COUNT(*) AS total FROM `stat`  WHERE 1 = 1 ';

// fromDt/toDt Filter
        if (($fromDt != null) && ($toDt != null)) {
            $sql .= ' AND stat.end > '. $fromDt->format('U') . ' AND stat.start <= '. $toDt->format('U');
        }

if (count($displayIds) > 0) {
            $sql .= ' AND stat.displayID IN (' . implode(',', $displayIds) . ')';
        }

// Total count
        $resTotal = $this->store->select($sql, $params);

// Total
        return isset($resTotal[0]['total']) ? $resTotal[0]['total'] : 0;
    }

/** @inheritdoc */
    public function deleteStats($maxage, $fromDt = null, $options = [])
    {
        // Set some default options
        $options = array_merge([
            'maxAttempts' => 10,
            'statsDeleteSleep' => 3,
            'limit' => 10000,
        ], $options);

// Convert to a simple type so that we can pass by reference to bindParam.
        $maxage = $maxage->format('U');

try {
            $i = 0;
            $rows = 1;

if ($fromDt !== null) {
                // Convert to a simple type so that we can pass by reference to bindParam.
                $fromDt = $fromDt->format('U');

// Prepare a delete statement which we will use multiple times
                $delete = $this->store->getConnection()
                    ->prepare('DELETE FROM `stat` WHERE stat.start <= :toDt AND stat.end > :fromDt ORDER BY statId LIMIT :limit');

$delete->bindParam(':fromDt', $fromDt, \PDO::PARAM_STR);
                $delete->bindParam(':toDt', $maxage, \PDO::PARAM_STR);
                $delete->bindParam(':limit', $options['limit'], \PDO::PARAM_INT);
            } else {
                $delete = $this->store->getConnection()
                    ->prepare('DELETE FROM `stat` WHERE stat.start <= :maxage LIMIT :limit');
                $delete->bindParam(':maxage', $maxage, \PDO::PARAM_STR);
                $delete->bindParam(':limit', $options['limit'], \PDO::PARAM_INT);
            }

$count = 0;
            while ($rows > 0) {

$i++;

// Run the delete
                $delete->execute();

// Find out how many rows we've deleted
                $rows = $delete->rowCount();
                $count += $rows;

// We shouldn't be in a transaction, but commit anyway just in case
                $this->store->commitIfNecessary();

// Give SQL time to recover
                if ($rows > 0) {
                    $this->log->debug('Stats delete effected ' . $rows . ' rows, sleeping.');
                    sleep($options['statsDeleteSleep']);
                }

// Break if we've exceeded the maximum attempts, assuming that has been provided
                if ($options['maxAttempts'] > -1 && $i >= $options['maxAttempts']) {
                    break;
                }
            }

$this->log->debug('Deleted Stats back to ' . $maxage . ' in ' . $i . ' attempts');

return $count;
        }
        catch (\PDOException $e) {
            $this->log->error($e->getMessage());
            throw new GeneralException('Stats cannot be deleted.');
        }
    }

/** @inheritdoc */
    public function executeQuery($options = [])
    {
        $this->log->debug('Execute MySQL query.');

$query = $options['query'];
        $params = $options['params'];

$dbh = $this->store->getConnection();

$sth = $dbh->prepare($query);
        $sth->execute($params);

// Get the results
        $results = $sth->fetchAll();

return $results;
    }

/**
     * @param StorageServiceInterface $store
     * @return $this
     */
    public function setStore($store)
    {
        $this->store = $store;
        return $this;
    }

/** @inheritdoc */
    public function getEngine()
    {
        return 'mysql';
    }

}