芝麻web文件管理V1.00

编辑当前文件:/home/mgatv524/cms.mgaplay.com.br/lib/Report/DistributionReport.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\Report;

use Carbon\Carbon;
use MongoDB\BSON\UTCDateTime;
use Psr\Container\ContainerInterface;
use Xibo\Entity\ReportForm;
use Xibo\Entity\ReportResult;
use Xibo\Entity\ReportSchedule;
use Xibo\Factory\DisplayFactory;
use Xibo\Factory\DisplayGroupFactory;
use Xibo\Factory\LayoutFactory;
use Xibo\Factory\MediaFactory;
use Xibo\Factory\SavedReportFactory;
use Xibo\Helper\DateFormatHelper;
use Xibo\Helper\SanitizerService;
use Xibo\Helper\Translate;
use Xibo\Service\ReportServiceInterface;
use Xibo\Support\Exception\InvalidArgumentException;
use Xibo\Support\Exception\NotFoundException;
use Xibo\Support\Sanitizer\SanitizerInterface;

/**
 * Class DistributionReport
 * @package Xibo\Report
 */
class DistributionReport implements ReportInterface
{

use ReportDefaultTrait;
    use SummaryDistributionCommonTrait;

/**
     * @var DisplayFactory
     */
    private $displayFactory;

/**
     * @var MediaFactory
     */
    private $mediaFactory;

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

/**
     * @var SavedReportFactory
     */
    private $savedReportFactory;

/**
     * @var DisplayGroupFactory
     */
    private $displayGroupFactory;

/**
     * @var ReportServiceInterface
     */
    private $reportService;

/**
     * @var SanitizerService
     */
    private $sanitizer;

private $table = 'stat';

private $periodTable = 'period';

/** @inheritdoc */
    public function setFactories(ContainerInterface $container)
    {
        $this->displayFactory = $container->get('displayFactory');
        $this->mediaFactory = $container->get('mediaFactory');
        $this->layoutFactory = $container->get('layoutFactory');
        $this->savedReportFactory = $container->get('savedReportFactory');
        $this->displayGroupFactory = $container->get('displayGroupFactory');
        $this->reportService = $container->get('reportService');
        $this->sanitizer = $container->get('sanitizerService');

return $this;
    }

/** @inheritdoc */
    public function getReportChartScript($results)
    {
        return json_encode($results->chart);
    }

/** @inheritdoc */
    public function getReportEmailTemplate()
    {
        return 'distribution-email-template.twig';
    }

/** @inheritdoc */
    public function getSavedReportTemplate()
    {
        return 'distribution-report-preview';
    }

/** @inheritdoc */
    public function getReportForm()
    {
        return new ReportForm(
            'distribution-report-form',
            'distributionReport',
            'Proof of Play',
            [
                'fromDateOneDay' => Carbon::now()->subSeconds(86400)->format(DateFormatHelper::getSystemFormat()),
                'toDate' => Carbon::now()->format(DateFormatHelper::getSystemFormat()),
            ],
            __('Select a type and an item (i.e., layout/media/tag)')
        );
    }

/** @inheritdoc */
    public function getReportScheduleFormData(SanitizerInterface $sanitizedParams)
    {
        $type = $sanitizedParams->getString('type');

$formParams = $this->getReportScheduleFormTitle($sanitizedParams);

$data = [];
        $data['formTitle'] = $formParams['title'];
        $data['hiddenFields'] = json_encode([
            'type' => $type,
            'selectedId' => $formParams['selectedId'],
            'eventTag' => $eventTag ?? null
        ]);
        $data['reportName'] = 'distributionReport';

return [
            'template' => 'distribution-schedule-form-add',
            'data' => $data
        ];
    }

/** @inheritdoc */
    public function setReportScheduleFormData(SanitizerInterface $sanitizedParams)
    {
        $filter = $sanitizedParams->getString('filter');
        $groupByFilter = $sanitizedParams->getString('groupByFilter');
        $displayId = $sanitizedParams->getInt('displayId');
        $displayGroupIds = $sanitizedParams->getIntArray('displayGroupId', ['default' => []]);
        $hiddenFields = json_decode($sanitizedParams->getString('hiddenFields'), true);

$type = $hiddenFields['type'];
        $selectedId = $hiddenFields['selectedId'];
        $eventTag = $hiddenFields['eventTag'];

// If a display is selected we ignore the display group selection
        $filterCriteria['displayId'] = $displayId;
        if (empty($displayId) && count($displayGroupIds) > 0) {
            $filterCriteria['displayGroupId'] = $displayGroupIds;
        }

$filterCriteria['type'] = $type;
        if ($type == 'layout') {
            $filterCriteria['layoutId'] = $selectedId;
        } elseif ($type == 'media') {
            $filterCriteria['mediaId'] = $selectedId;
        } elseif ($type == 'event') {
            $filterCriteria['eventTag'] = $eventTag;
        }

$filterCriteria['filter'] = $filter;

$schedule = '';
        if ($filter == 'daily') {
            $schedule = ReportSchedule::$SCHEDULE_DAILY;
            $filterCriteria['reportFilter'] = 'yesterday';
            $filterCriteria['groupByFilter'] = $groupByFilter;
        } elseif ($filter == 'weekly') {
            $schedule = ReportSchedule::$SCHEDULE_WEEKLY;
            $filterCriteria['reportFilter'] = 'lastweek';
            $filterCriteria['groupByFilter'] = $groupByFilter;
        } elseif ($filter == 'monthly') {
            $schedule = ReportSchedule::$SCHEDULE_MONTHLY;
            $filterCriteria['reportFilter'] = 'lastmonth';
            $filterCriteria['groupByFilter'] = $groupByFilter;
        } elseif ($filter == 'yearly') {
            $schedule = ReportSchedule::$SCHEDULE_YEARLY;
            $filterCriteria['reportFilter'] = 'lastyear';
            $filterCriteria['groupByFilter'] = $groupByFilter;
        }

$filterCriteria['sendEmail'] = $sanitizedParams->getCheckbox('sendEmail');
        $filterCriteria['nonusers'] = $sanitizedParams->getString('nonusers');

// Return
        return [
            'filterCriteria' => json_encode($filterCriteria),
            'schedule' => $schedule
        ];
    }

/** @inheritdoc */
    public function generateSavedReportName(SanitizerInterface $sanitizedParams)
    {
        $type = $sanitizedParams->getString('type');
        $filter = $sanitizedParams->getString('filter');

if ($type == 'layout') {
            try {
                $layout = $this->layoutFactory->getById($sanitizedParams->getInt('layoutId'));
            } catch (NotFoundException $error) {
                // Get the campaign ID
                $campaignId = $this->layoutFactory->getCampaignIdFromLayoutHistory($sanitizedParams->getInt('layoutId'));
                $layoutId = $this->layoutFactory->getLatestLayoutIdFromLayoutHistory($campaignId);
                $layout = $this->layoutFactory->getById($layoutId);
            }

$saveAs = sprintf(__('%s report for Layout %s', ucfirst($filter), $layout->layout));
        } elseif ($type == 'media') {
            try {
                $media = $this->mediaFactory->getById($sanitizedParams->getInt('mediaId'));
                $saveAs = sprintf(__('%s report for Media %s', ucfirst($filter), $media->name));
            } catch (NotFoundException $error) {
                $saveAs = __('Media not found');
            }
        } elseif ($type == 'event') {
            $saveAs = sprintf(__('%s report for Event %s', ucfirst($filter), $sanitizedParams->getString('eventTag')));
        }

// todo: ???
        if (!empty($filterCriteria['displayId'])) {
            // Get display
            try {
                $displayName = $this->displayFactory->getById($filterCriteria['displayId'])->display;
                $saveAs .= ' ('. __('Display') . ': '. $displayName . ')';
            } catch (NotFoundException $error) {
                $saveAs .= ' '.__('(DisplayId: Not Found)');
            }
        }

return $saveAs;
    }

/** @inheritdoc */
    public function getSavedReportResults($json, $savedReport)
    {
        $metadata = [ 'periodStart' => $json['metadata']['periodStart'],
            'periodEnd' => $json['metadata']['periodEnd'],
            'generatedOn' => Carbon::createFromTimestamp($savedReport->generatedOn)
                ->format(DateFormatHelper::getSystemFormat()),
            'title' => $savedReport->saveAs,
        ];

// Report result object
        return new ReportResult(
            $metadata,
            $json['table'],
            $json['recordsTotal'],
            $json['chart']
        );
    }

/** @inheritdoc */
    public function getResults(SanitizerInterface $sanitizedParams)
    {
        $type = strtolower($sanitizedParams->getString('type'));
        $layoutId = $sanitizedParams->getInt('layoutId');
        $mediaId = $sanitizedParams->getInt('mediaId');
        $eventTag = $sanitizedParams->getString('eventTag');

// Filter by displayId?
        $displayIds = $this->getDisplayIdFilter($sanitizedParams);

// Get an array of display groups this user has access to
        $displayGroupIds = [];

foreach ($this->displayGroupFactory->query(null, [
            'isDisplaySpecific' => -1,
            'userCheckUserId' => $this->getUser()->userId
        ]) as $displayGroup) {
            $displayGroupIds[] = $displayGroup->displayGroupId;
        }

if (count($displayGroupIds) <= 0) {
            throw new InvalidArgumentException(__('No display groups with View permissions'), 'displayGroup');
        }

// From and To Date Selection
        // --------------------------
        // Our report has a range filter which determins whether the user has to enter their own from / to dates
        // check the range filter first and set from/to dates accordingly.
        $reportFilter = $sanitizedParams->getString('reportFilter');
        // Use the current date as a helper
        $now = Carbon::now();

switch ($reportFilter) {
            case 'today':
                $fromDt = $now->copy()->startOfDay();
                $toDt = $fromDt->copy()->addDay();
                break;

case 'yesterday':
                $fromDt = $now->copy()->startOfDay()->subDay();
                $toDt = $now->copy()->startOfDay();
                break;

case 'thisweek':
                $fromDt = $now->copy()->locale(Translate::GetLocale())->startOfWeek();
                $toDt = $fromDt->copy()->addWeek();
                break;

case 'thismonth':
                $fromDt = $now->copy()->startOfMonth();
                $toDt = $fromDt->copy()->addMonth();
                break;

case 'thisyear':
                $fromDt = $now->copy()->startOfYear();
                $toDt = $fromDt->copy()->addYear();
                break;

case 'lastweek':
                $fromDt = $now->copy()->locale(Translate::GetLocale())->startOfWeek()->subWeek();
                $toDt = $fromDt->copy()->addWeek();
                break;

case 'lastmonth':
                $fromDt = $now->copy()->startOfMonth()->subMonth();
                $toDt = $fromDt->copy()->addMonth();
                break;

case 'lastyear':
                $fromDt = $now->copy()->startOfYear()->subYear();
                $toDt = $fromDt->copy()->addYear();
                break;

case '':
            default:
                // Expect dates to be provided.
                $fromDt = $sanitizedParams->getDate('statsFromDt', ['default' => Carbon::now()->subDay()]);
                $fromDt->startOfDay();

$toDt = $sanitizedParams->getDate('statsToDt', ['default' =>  Carbon::now()]);
                $toDt->addDay()->startOfDay();

// What if the fromdt and todt are exactly the same?
                // in this case assume an entire day from midnight on the fromdt to midnight on the todt (i.e. add a day to the todt)
                if ($fromDt == $toDt) {
                    $toDt->addDay();
                }

break;
        }

// Use the group by filter provided
        // NB: this differs from the Summary Report where we set the group by according to the range selected
        $groupByFilter = $sanitizedParams->getString('groupByFilter');

//
        // Get Results!
        // -------------
        $timeSeriesStore = $this->getTimeSeriesStore()->getEngine();
        if ($timeSeriesStore == 'mongodb') {
            $result = $this->getDistributionReportMongoDb(
                $fromDt,
                $toDt,
                $groupByFilter,
                $displayIds,
                $displayGroupIds,
                $type,
                $layoutId,
                $mediaId,
                $eventTag
            );
        } else {
            $result = $this->getDistributionReportMySql(
                $fromDt,
                $toDt,
                $groupByFilter,
                $displayIds,
                $displayGroupIds,
                $type,
                $layoutId,
                $mediaId,
                $eventTag
            );
        }

//
        // Output Results
        // --------------
        $labels = [];
        $countData = [];
        $durationData = [];
        $backgroundColor = [];
        $borderColor = [];

// Sanitize results for chart and table
        $rows = [];
        if (count($result) > 0) {
            foreach ($result['result'] as $row) {
                $sanitizedRow = $this->sanitizer->getSanitizer($row);

// ----
                // Build Chart data
                // Chart labels in xaxis
                $labels[] = $row['label'];

$backgroundColor[] = 'rgb(95, 186, 218, 0.6)';
                $borderColor[] = 'rgb(240,93,41, 0.8)';

$count = ($row['NumberPlays'] == '') ? 0 : $row['NumberPlays'];
                $countData[] = $count;

$duration = ($row['Duration'] == '') ? 0 : $row['Duration'];
                $durationData[] = $duration;

// ----
                // Build Tabular data
                $entry = [];
                $entry['label'] = $sanitizedRow->getString('label');
                $entry['duration'] = $duration;
                $entry['count'] = $count;
                $rows[] = $entry;
            }
        }

// Build Chart to pass in twig file chart.js
        $chart = [
            'type' => 'bar',
            'data' => [
                'labels' => $labels,
                'datasets' => [
                    [
                        'label' => __('Total duration'),
                        'yAxisID' => 'Duration',
                        'backgroundColor' => $backgroundColor,
                        'data' => $durationData
                    ],
                    [
                        'label' => __('Total count'),
                        'yAxisID' => 'Count',
                        'borderColor' => $borderColor,
                        'type' => 'line',
                        'fill' => false,
                        'data' =>  $countData
                    ]
                ]
            ],
            'options' => [
                'scales' => [
                    'yAxes' => [
                        [
                            'id' => 'Duration',
                            'type' => 'linear',
                            'position' =>  'left',
                            'display' =>  true,
                            'scaleLabel' =>  [
                                'display' =>  true,
                                'labelString' => __('Duration(s)')
                            ],
                            'ticks' =>  [
                                'beginAtZero' => true
                            ]
                        ], [
                            'id' => 'Count',
                            'type' => 'linear',
                            'position' =>  'right',
                            'display' =>  true,
                            'scaleLabel' =>  [
                                'display' =>  true,
                                'labelString' => __('Count')
                            ],
                            'ticks' =>  [
                                'beginAtZero' => true
                            ]
                        ]
                    ]
                ]
            ]
        ];

$metadata =   [
            'periodStart' => $fromDt->format(DateFormatHelper::getSystemFormat()),
            'periodEnd' => $toDt->format(DateFormatHelper::getSystemFormat()),
        ];

// Total records
        $recordsTotal = count($rows);

// ----
        // Return data to build chart/table
        // This will get saved to a json file when schedule runs
        return new ReportResult(
            $metadata,
            $rows,
            $recordsTotal,
            $chart
        );
    }

/**
     * MySQL distribution report
     * @param Carbon $fromDt The filter range from date
     * @param Carbon $toDt The filter range to date
     * @param string $groupByFilter Grouping, byhour, bydayofweek and bydayofmonth
     * @param $displayIds
     * @param $displayGroupIds
     * @param $type
     * @param $layoutId
     * @param $mediaId
     * @param $eventTag
     * @return array
     */
    private function getDistributionReportMySql(
        $fromDt,
        $toDt,
        $groupByFilter,
        $displayIds,
        $displayGroupIds,
        $type,
        $layoutId,
        $mediaId,
        $eventTag
    ) {
        // Only return something if we have the necessary options selected.
        if (($type == 'media' && $mediaId != '')
            || ($type == 'layout' && $layoutId != '')
            || ($type == 'event' && $eventTag != '')
        ) {
            // Create periods covering the from/to dates
            // -----------------------------------------
            try {
                $periods = $this->getTemporaryPeriodsTable($fromDt, $toDt, $groupByFilter);
            } catch (InvalidArgumentException $invalidArgumentException) {
                return [];
            }

// Join in stats
            // -------------
            $select = '                      
            SELECT periodsWithStats.id,
                MIN(periodsWithStats.start) AS start,
                MAX(periodsWithStats.end) AS end, 
                MAX(periodsWithStats.label) AS label,
                SUM(numberOfPlays) as NumberPlays, 
                CONVERT(SUM(periodsWithStats.actualDiff), SIGNED INTEGER) as Duration
             FROM (
                SELECT
                    periods.id,
                    periods.label,
                    periods.start,
                    periods.end,
                    stat.count AS numberOfPlays,
                    LEAST(stat.duration, LEAST(periods.end, statEnd, :toDt) 
                                             - GREATEST(periods.start, statStart, :fromDt)) AS actualDiff
                 FROM `' . $periods . '` AS periods
                    LEFT OUTER JOIN (
                        SELECT 
                            layout.Layout,
                            IFNULL(`media`.name, IFNULL(`widgetoption`.value, `widget`.type)) AS Media,
                            stat.mediaId,
                            stat.`start` as statStart,
                            stat.`end` as statEnd,
                            stat.duration,
                            stat.`count`
                          FROM stat
                            LEFT OUTER JOIN layout
                            ON layout.layoutID = stat.layoutID
                            LEFT OUTER JOIN `widget`
                            ON `widget`.widgetId = stat.widgetId
                            LEFT OUTER JOIN `widgetoption`
                            ON `widgetoption`.widgetId = `widget`.widgetId
                                AND `widgetoption`.type = \'attrib\'
                                AND `widgetoption`.option = \'name\'
                            LEFT OUTER JOIN `media`
                            ON `media`.mediaId = `stat`.mediaId
                         WHERE stat.type <> \'displaydown\' 
                            AND stat.start < :toDt
                            AND stat.end >= :fromDt
            ';

$params = [
                'fromDt' => $fromDt->format('U'),
                'toDt' => $toDt->format('U')
            ];

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

// Type filter
            if ($type == 'layout' && $layoutId != '') {
                // Filter by Layout
                $select .= ' 
                    AND `stat`.type = \'layout\' 
                    AND `stat`.campaignId = (SELECT campaignId FROM layouthistory WHERE layoutId = :layoutId) 
                ';
                $params['layoutId'] = $layoutId;
            } elseif ($type == 'media' && $mediaId != '') {
                // Filter by Media
                $select .= '
                    AND `stat`.type = \'media\' AND IFNULL(`media`.mediaId, 0) <> 0 
                    AND `stat`.mediaId = :mediaId ';
                $params['mediaId'] = $mediaId;
            } elseif ($type == 'event' && $eventTag != '') {
                // Filter by Event
                $select .= '
                    AND `stat`.type = \'event\'  
                    AND `stat`.tag = :tag ';
                $params['tag'] = $eventTag;
            }

$select .= ' 
                        ) stat               
                        ON statStart < periods.`end`
                            AND statEnd > periods.`start`
            ';

// Periods and Stats tables are joined, we should only have periods we're interested in, but it
            // won't hurt to restrict them
            $select .= ' 
             WHERE periods.`start` >= :fromDt
                AND periods.`end` <= :toDt ';

// Close out our containing view and group things together
            $select .= '
                ) periodsWithStats 
            GROUP BY periodsWithStats.id, periodsWithStats.label
            ORDER BY periodsWithStats.id
            ';

return [
                'result' => $this->getStore()->select($select, $params),
                'periodStart' => $fromDt->format(DateFormatHelper::getSystemFormat()),
                'periodEnd' => $toDt->format(DateFormatHelper::getSystemFormat())
            ];
        } else {
            return [];
        }
    }

private function getDistributionReportMongoDb($fromDt, $toDt, $groupByFilter, $displayIds, $displayGroupIds, $type, $layoutId, $mediaId, $eventTag)
    {
        if ((($type == 'media') && ($mediaId != '')) ||
            (($type == 'layout') && ($layoutId != '')) ||
            (($type == 'event') && ($eventTag != ''))) {
            // Get the timezone
            $timezone = Carbon::parse()->getTimezone()->getName();
            $filterRangeStart = new UTCDateTime($fromDt->format('U') * 1000);
            $filterRangeEnd = new UTCDateTime($toDt->format('U') * 1000);

$diffInDays = $toDt->diffInDays($fromDt);
            if ($groupByFilter == 'byhour') {
                $hour = 1;
                $input = range(0, 24 * $diffInDays - 1); // subtract 1 as we start from 0
                $id = '$hour';
            } elseif ($groupByFilter == 'bydayofweek') {
                $hour = 24;
                $input = range(0, $diffInDays - 1);
                $id = '$isoDayOfWeek';
            } elseif ($groupByFilter == 'bydayofmonth') {
                $hour = 24;
                $input = range(0, $diffInDays - 1);
                $id = '$dayOfMonth';
            } else {
                $this->getLog()->error('Unknown Grouping Selected ' . $groupByFilter);
                throw new InvalidArgumentException(__('Unknown Grouping ') . $groupByFilter, 'groupByFilter');
            }

// Dateparts for period generation
            $dateFromParts['month'] = $fromDt->month;
            $dateFromParts['year'] = $fromDt->year;
            $dateFromParts['day'] = $fromDt->day;
            $dateFromParts['hour'] = 0;

// PERIODS QUERY
            $cursorPeriodQuery = [

[
                    '$addFields' => [

'period_start' => [
                            '$dateFromParts' =>  [
                                'year' => $dateFromParts['year'],
                                'month' => $dateFromParts['month'],
                                'day' =>  $dateFromParts['day'],
                                'hour' =>  $dateFromParts['hour'],
                                'timezone' =>  $timezone,
                            ]
                        ]
                    ]
                ],

[
                    '$project' => [

'periods' => [
                            '$map' => [
                                'input' => $input,
                                'as' => 'number',
                                'in' => [
                                    'start' => [
                                        '$add' => [
                                            '$period_start',
                                            [
                                                '$multiply' => [
                                                    $hour * 3600000,
                                                    '$$number'
                                                ]
                                            ]
                                        ]
                                    ],
                                    'end' => [
                                        '$add' => [
                                            [
                                                '$add' => [
                                                    '$period_start',
                                                    [
                                                        '$multiply' => [
                                                            $hour * 3600000,
                                                            '$$number'
                                                        ]
                                                    ]
                                                ]
                                            ]
                                            , $hour * 3600000
                                        ]
                                    ],
                                ]
                            ]
                        ]
                    ]
                ],

// periods needs to be unwind to merge next
                [
                    '$unwind' => '$periods'
                ],

// replace the root to eliminate _id and get only periods
                [
                    '$replaceRoot' => [
                        'newRoot' => '$periods'
                    ]
                ],

[
                    '$project' => [
                        'start' => 1,
                        'end' => 1,
                        'id' => [
                            $id => [
                                'date' => '$start',
                                'timezone'=> $timezone
                            ]
                        ],
                    ]
                ],

[
                    '$group' => [
                        '_id' => [
                            'id' => '$id'
                        ],
                        'start' => ['$first' => '$start'],
                        'end' => ['$first' => '$end'],
                        'id' => ['$first' => '$id'],
                    ]
                ],

[
                    '$match' => [
                        'start' => [
                            '$lt' => $filterRangeEnd
                        ],
                        'end' => [
                            '$gt' => $filterRangeStart
                        ]
                    ]
                ],

[
                    '$sort' => ['id' => 1]
                ]

];

// Periods result
            $periods = $this->getTimeSeriesStore()->executeQuery(['collection' => $this->periodTable, 'query' => $cursorPeriodQuery]);

// We extend the stat start and stat end so that we can push required periods for them
            if (($groupByFilter == 'bydayofweek') || ($groupByFilter == 'bydayofmonth')) {
                $datePartStart = [
                    '$dateFromParts' =>  [
                        'year' => [
                            '$year' => '$start'
                        ],
                        'month' => [
                            '$month' => '$start'
                        ],
                        'day' => [
                            '$dayOfMonth' => '$start'
                        ],
                    ]
                ];

$datePartEnd = [
                    '$dateFromParts' =>  [
                        'year' => [
                            '$year' => '$end'
                        ],
                        'month' => [
                            '$month' => '$end'
                        ],
                        'day' => [
                            '$dayOfMonth' => '$end'
                        ],
                    ]
                ];
            } else { // by hour
                $datePartStart = [
                    '$dateFromParts' =>  [
                        'year' => [
                            '$year' => '$start'
                        ],
                        'month' => [
                            '$month' => '$start'
                        ],
                        'day' => [
                            '$dayOfMonth' => '$start'
                        ],
                        'hour' => [
                            '$hour' => '$start'
                        ],
                    ]
                ];

$datePartEnd = [
                    '$dateFromParts' =>  [
                        'year' => [
                            '$year' => '$end'
                        ],
                        'month' => [
                            '$month' => '$end'
                        ],
                        'day' => [
                            '$dayOfMonth' => '$end'
                        ],
                        'hour' => [
                            '$hour' => '$end'
                        ],
                    ]
                ];
            }

$match = [
                '$match' => [
                    'start' => [
                        '$lt' => $filterRangeEnd
                    ],
                    'end' => [
                        '$gt' => $filterRangeStart
                    ],
                    'type' => $type,
                ]
            ];

if (count($displayIds) > 0) {
                $match['$match']['displayId'] = [
                    '$in' => $displayIds
                ];
            }

// Type filter
            if (($type == 'layout') && ($layoutId != '')) {
                // Get the campaign ID
                $campaignId = $this->layoutFactory->getCampaignIdFromLayoutHistory($layoutId);
                $match['$match']['campaignId'] = $campaignId;
            } elseif (($type == 'media') && ($mediaId != '')) {
                $match['$match']['mediaId'] = $mediaId;
            } elseif (($type == 'event') && ($eventTag != '')) {
                $match['$match']['eventName'] = $eventTag;
            }

// STAT AGGREGATION QUERY
            $statQuery = [

$match,
                [
                    '$project' => [
                        'count' => 1,
                        'duration' => 1,
                        'start' => [
                            '$dateFromParts' =>  [
                                'year' => [
                                    '$year' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                                'month' => [
                                    '$month' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                                'day' => [
                                    '$dayOfMonth' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                                'hour' => [
                                    '$hour' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                                'minute' => [
                                    '$minute' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                                'second' => [
                                    '$second' => [
                                        'date' => '$start',
                                        'timezone' => $timezone,

]
                                ],
                            ]
                        ],
                        'end' => [
                            '$dateFromParts' =>  [
                                'year' => [
                                    '$year' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                                'month' => [
                                    '$month' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                                'day' => [
                                    '$dayOfMonth' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                                'hour' => [
                                    '$hour' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                                'minute' => [
                                    '$minute' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                                'second' => [
                                    '$second' => [
                                        'date' => '$end',
                                        'timezone' => $timezone,

]
                                ],
                            ]
                        ]

]
                ],

[
                    '$addFields' => [
                        'period_start_backward' => $datePartStart,
                        'period_end_forward' => [
                            '$add' => [
                                $datePartEnd,
                                $hour * 3600000
                            ]
                        ]
                    ]
                ],

[
                    '$project' => [
                        'start' => 1,
                        'end' => 1,
                        'count' => 1,
                        'duration' => 1,
                        'period_start_backward' => 1,
                        'period_end_forward' => 1,
                        'range' => [
                            '$range' => [
                                0,
                                [
                                    '$ceil' => [
                                        '$divide' => [
                                            [
                                                '$subtract' => [
                                                    '$period_end_forward',
                                                    '$period_start_backward'
                                                ]
                                            ],
                                            $hour * 3600000
                                        ]
                                    ]
                                ]
                            ]
                        ],

'period_start' => [
                            '$dateFromParts' =>  [
                                'year' => [
                                    '$year' => '$period_start_backward'
                                ],
                                'month' => [
                                    '$month' => '$period_start_backward'
                                ],
                                'day' => [
                                    '$dayOfMonth' => '$period_start_backward'
                                ],
                                'hour' => [
                                    '$hour' => '$period_start_backward'
                                ],
                            ]
                        ]
                    ]
                ],

[
                    '$project' => [
                        'start' => 1,
                        'end' => 1,
                        'count' => 1,
                        'duration' => 1,
                        'periods' => [
                            '$map' => [
                                'input' => '$range',
                                'as' => 'number',
                                'in' => [
                                    'start' => [
                                        '$add' => [
                                            '$period_start',
                                            [
                                                '$multiply' => [
                                                    $hour * 3600000,
                                                    '$$number'
                                                ]
                                            ]
                                        ]
                                    ],
                                    'end' => [
                                        '$add' => [
                                            [
                                                '$add' => [
                                                    '$period_start',
                                                    [
                                                        '$multiply' => [
                                                            $hour * 3600000,
                                                            '$$number'
                                                        ]
                                                    ]
                                                ]
                                            ]
                                            , $hour * 3600000
                                        ]
                                    ],
                                ]
                            ]
                        ]
                    ]
                ],

[
                    '$unwind' => '$periods'
                ],
                [
                    '$match' => [
                        'periods.start' => ['$lt' => $filterRangeEnd ],
                        'periods.end' => ['$gt' => $filterRangeStart ],
                    ]
                ],
                [
                    '$project' => [
                        'start' => 1,
                        'end' => 1,
                        'count' => 1,
                        'duration' => 1,
                        'period_start' => '$periods.start',
                        'period_end' => '$periods.end',
                        'id' => [
                            $id => [
                                'date' => '$periods.start',
                                'timezone'=> 'UTC'
                            ]
                        ],

'actualStart' => [
                            '$max' => ['$start', '$periods.start', $filterRangeStart]
                        ],
                        'actualEnd' => [
                            '$min' => ['$end', '$periods.end', $filterRangeEnd]
                        ],
                        'actualDiff' => [
                            '$min' => [
                                '$duration',
                                [
                                    '$divide' => [
                                        [
                                            '$subtract' => [
                                                ['$min' => ['$end', '$periods.end', $filterRangeEnd]],
                                                ['$max' => ['$start', '$periods.start', $filterRangeStart]]
                                            ]
                                        ], 1000
                                    ]
                                ]
                            ]
                        ],

]

],
                [
                    '$match' => [
                        '$expr' => [
                            '$lt' => ['$actualStart' , '$actualEnd' ],
                        ]

]
                ],

[
                    '$group' => [
                        '_id' => [
                            'id' => '$id'
                        ],
                        'period_start' => ['$first' => '$period_start'],
                        'period_end' => ['$first' => '$period_end'],
                        'NumberPlays' => ['$sum' => '$count'],
                        'Duration' => ['$sum' => '$actualDiff'],
                        'start' => ['$first' => '$start'],
                        'end' => ['$first' => '$end'],
                        'id' => ['$first' => '$id'],

]
                ],

[
                    '$project' => [
                        'start' => '$start',
                        'end' => '$end',
                        'period_start' => 1,
                        'period_end' => 1,
                        'NumberPlays' => ['$toInt' => '$NumberPlays'],
                        'Duration' => ['$toInt' => '$Duration'],
                        'id' => 1,

]
                ],

[
                    '$sort' => ['id' => 1]
                ]

];

// Stats result
            $results = $this->getTimeSeriesStore()->executeQuery(['collection' => $this->table, 'query' => $statQuery]);

// Run period loop and map the stat result for each period
            $resultArray = [];
            $day = [ 1 => 'Mon', 2 => 'Tues', 3 => 'Wed', 4 => 'Thu', 5 => 'Fri', 6 => 'Sat', 7 => 'Sun'];

foreach ($periods as $key => $period) {
                $id = $period['id'];

if ($groupByFilter == 'byhour') {
                    $label = Carbon::createFromTimestamp($period['start']->toDateTime()->format('U'))->format('g:i A');
                } elseif ($groupByFilter == 'bydayofweek') {
                    $label = $day[$id];
                } elseif ($groupByFilter == 'bydayofmonth') {
                    $label = Carbon::createFromTimestamp($period['start']->toDateTime()->format('U'))->format('d');
                }

$matched = false;
                foreach ($results as $k => $result) {
                    if ($result['id'] == $period['id']) {
                        $NumberPlays = $result['NumberPlays'];
                        $Duration = $result['Duration'];

$matched = true;
                        break;
                    }
                }
                
                $resultArray[$key]['id'] = $id;
                $resultArray[$key]['label'] = $label;

if ($matched == true) {
                    $resultArray[$key]['NumberPlays'] = $NumberPlays;
                    $resultArray[$key]['Duration'] = $Duration;
                } else {
                    $resultArray[$key]['NumberPlays'] = 0;
                    $resultArray[$key]['Duration'] = 0;
                }
            }

$this->getLog()->debug('Period start: ' . $fromDt->format(DateFormatHelper::getSystemFormat()) . ' Period end: ' . $toDt->format(DateFormatHelper::getSystemFormat()));

return [
                'result' => $resultArray,
                'periodStart' => $fromDt->format(DateFormatHelper::getSystemFormat()),
                'periodEnd' => $toDt->format(DateFormatHelper::getSystemFormat())
            ];
        } else {
            return [];
        }
    }
}