From 49f0468d44468528c2fb2c8b0efd19cdaeeec43d Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 5 May 2022 14:12:57 +0200 Subject: Add filter by start/end date overall stats in api --- server/controllers/api/videos/stats.ts | 9 ++++-- .../middlewares/validators/videos/video-stats.ts | 10 ++++++ server/models/view/local-video-viewer.ts | 36 ++++++++++++++++------ server/tests/api/check-params/views.ts | 24 ++++++++++++++- .../tests/api/views/video-views-overall-stats.ts | 21 +++++++++++++ 5 files changed, 88 insertions(+), 12 deletions(-) (limited to 'server') diff --git a/server/controllers/api/videos/stats.ts b/server/controllers/api/videos/stats.ts index 71452d9f0..30e2bb06c 100644 --- a/server/controllers/api/videos/stats.ts +++ b/server/controllers/api/videos/stats.ts @@ -1,6 +1,6 @@ import express from 'express' import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer' -import { VideoStatsTimeserieMetric, VideoStatsTimeserieQuery } from '@shared/models' +import { VideoStatsOverallQuery, VideoStatsTimeserieMetric, VideoStatsTimeserieQuery } from '@shared/models' import { asyncMiddleware, authenticate, @@ -39,8 +39,13 @@ export { async function getOverallStats (req: express.Request, res: express.Response) { const video = res.locals.videoAll + const query = req.query as VideoStatsOverallQuery - const stats = await LocalVideoViewerModel.getOverallStats(video) + const stats = await LocalVideoViewerModel.getOverallStats({ + video, + startDate: query.startDate, + endDate: query.endDate + }) return res.json(stats) } diff --git a/server/middlewares/validators/videos/video-stats.ts b/server/middlewares/validators/videos/video-stats.ts index 12509abde..f17fbcc09 100644 --- a/server/middlewares/validators/videos/video-stats.ts +++ b/server/middlewares/validators/videos/video-stats.ts @@ -10,6 +10,16 @@ import { areValidationErrors, checkUserCanManageVideo, doesVideoExist, isValidVi const videoOverallStatsValidator = [ isValidVideoIdParam('videoId'), + query('startDate') + .optional() + .custom(isDateValid) + .withMessage('Should have a valid start date'), + + query('endDate') + .optional() + .custom(isDateValid) + .withMessage('Should have a valid end date'), + async (req: express.Request, res: express.Response, next: express.NextFunction) => { logger.debug('Checking videoOverallStatsValidator parameters', { parameters: req.body }) diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts index 5928ba5f6..2862f8b96 100644 --- a/server/models/view/local-video-viewer.ts +++ b/server/models/view/local-video-viewer.ts @@ -100,10 +100,28 @@ export class LocalVideoViewerModel extends Model { - const options = { + static async getOverallStats (options: { + video: MVideo + startDate?: string + endDate?: string + }): Promise { + const { video, startDate, endDate } = options + + const queryOptions = { type: QueryTypes.SELECT as QueryTypes.SELECT, - replacements: { videoId: video.id } + replacements: { videoId: video.id } as any + } + + let dateWhere = '' + + if (startDate) { + dateWhere += ' AND "localVideoViewer"."startDate" >= :startDate' + queryOptions.replacements.startDate = startDate + } + + if (endDate) { + dateWhere += ' AND "localVideoViewer"."endDate" <= :endDate' + queryOptions.replacements.endDate = endDate } const watchTimeQuery = `SELECT ` + @@ -111,9 +129,9 @@ export class LocalVideoViewerModel extends Model(watchTimeQuery, options) + const watchTimePromise = LocalVideoViewerModel.sequelize.query(watchTimeQuery, queryOptions) const watchPeakQuery = `WITH "watchPeakValues" AS ( SELECT "startDate" AS "dateBreakpoint", 1 AS "inc" @@ -122,7 +140,7 @@ export class LocalVideoViewerModel extends Model(watchPeakQuery, options) + const watchPeakPromise = LocalVideoViewerModel.sequelize.query(watchPeakQuery, queryOptions) const countriesQuery = `SELECT country, COUNT(country) as viewers ` + `FROM "localVideoViewer" ` + - `WHERE "videoId" = :videoId AND country IS NOT NULL ` + + `WHERE "videoId" = :videoId AND country IS NOT NULL ${dateWhere} ` + `GROUP BY country ` + `ORDER BY viewers DESC` - const countriesPromise = LocalVideoViewerModel.sequelize.query(countriesQuery, options) + const countriesPromise = LocalVideoViewerModel.sequelize.query(countriesQuery, queryOptions) const [ rowsWatchTime, rowsWatchPeak, rowsCountries ] = await Promise.all([ watchTimePromise, diff --git a/server/tests/api/check-params/views.ts b/server/tests/api/check-params/views.ts index 3dba2a42e..fe037b145 100644 --- a/server/tests/api/check-params/views.ts +++ b/server/tests/api/check-params/views.ts @@ -75,8 +75,30 @@ describe('Test videos views', function () { }) }) + it('Should fail with an invalid start date', async function () { + await servers[0].videoStats.getOverallStats({ + videoId, + startDate: 'fake' as any, + endDate: new Date().toISOString(), + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + + it('Should fail with an invalid end date', async function () { + await servers[0].videoStats.getOverallStats({ + videoId, + startDate: new Date().toISOString(), + endDate: 'fake' as any, + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + it('Should succeed with the correct parameters', async function () { - await servers[0].videoStats.getOverallStats({ videoId }) + await servers[0].videoStats.getOverallStats({ + videoId, + startDate: new Date().toISOString(), + endDate: new Date().toISOString() + }) }) }) diff --git a/server/tests/api/views/video-views-overall-stats.ts b/server/tests/api/views/video-views-overall-stats.ts index 72b072c96..53b8f0d4b 100644 --- a/server/tests/api/views/video-views-overall-stats.ts +++ b/server/tests/api/views/video-views-overall-stats.ts @@ -141,6 +141,27 @@ describe('Test views overall stats', function () { } }) + it('Should filter overall stats by date', async function () { + this.timeout(60000) + + const beforeView = new Date() + + await servers[0].views.simulateViewer({ id: vodVideoId, currentTimes: [ 0, 3 ] }) + await processViewersStats(servers) + + { + const stats = await servers[0].videoStats.getOverallStats({ videoId: vodVideoId, startDate: beforeView.toISOString() }) + expect(stats.averageWatchTime).to.equal(3) + expect(stats.totalWatchTime).to.equal(3) + } + + { + const stats = await servers[0].videoStats.getOverallStats({ videoId: liveVideoId, endDate: beforeView.toISOString() }) + expect(stats.averageWatchTime).to.equal(22) + expect(stats.totalWatchTime).to.equal(88) + } + }) + after(async function () { await stopFfmpeg(command) }) -- cgit v1.2.3