From 49f0468d44468528c2fb2c8b0efd19cdaeeec43d Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 5 May 2022 14:12:57 +0200 Subject: [PATCH 1/1] Add filter by start/end date overall stats in api --- server/controllers/api/videos/stats.ts | 9 +++-- .../validators/videos/video-stats.ts | 10 ++++++ server/models/view/local-video-viewer.ts | 36 ++++++++++++++----- server/tests/api/check-params/views.ts | 24 ++++++++++++- .../api/views/video-views-overall-stats.ts | 21 +++++++++++ shared/models/videos/stats/index.ts | 1 + .../stats/video-stats-overall-query.model.ts | 4 +++ .../videos/video-stats-command.ts | 4 +++ support/doc/api/openapi.yaml | 24 +++++++++++++ 9 files changed, 121 insertions(+), 12 deletions(-) create mode 100644 shared/models/videos/stats/video-stats-overall-query.model.ts 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) }) diff --git a/shared/models/videos/stats/index.ts b/shared/models/videos/stats/index.ts index 4a6fdaa71..a9b203f58 100644 --- a/shared/models/videos/stats/index.ts +++ b/shared/models/videos/stats/index.ts @@ -1,3 +1,4 @@ +export * from './video-stats-overall-query.model' export * from './video-stats-overall.model' export * from './video-stats-retention.model' export * from './video-stats-timeserie-query.model' diff --git a/shared/models/videos/stats/video-stats-overall-query.model.ts b/shared/models/videos/stats/video-stats-overall-query.model.ts new file mode 100644 index 000000000..6b4c2164f --- /dev/null +++ b/shared/models/videos/stats/video-stats-overall-query.model.ts @@ -0,0 +1,4 @@ +export interface VideoStatsOverallQuery { + startDate?: string + endDate?: string +} diff --git a/shared/server-commands/videos/video-stats-command.ts b/shared/server-commands/videos/video-stats-command.ts index bd4808f63..b9b99bfb5 100644 --- a/shared/server-commands/videos/video-stats-command.ts +++ b/shared/server-commands/videos/video-stats-command.ts @@ -6,6 +6,8 @@ export class VideoStatsCommand extends AbstractCommand { getOverallStats (options: OverrideCommandOptions & { videoId: number | string + startDate?: string + endDate?: string }) { const path = '/api/v1/videos/' + options.videoId + '/stats/overall' @@ -13,6 +15,8 @@ export class VideoStatsCommand extends AbstractCommand { ...options, path, + query: pick(options, [ 'startDate', 'endDate' ]), + implicitToken: true, defaultExpectedStatus: HttpStatusCode.OK_200 }) diff --git a/support/doc/api/openapi.yaml b/support/doc/api/openapi.yaml index 95670925f..294aa50ab 100644 --- a/support/doc/api/openapi.yaml +++ b/support/doc/api/openapi.yaml @@ -1952,6 +1952,18 @@ paths: - OAuth2: [] parameters: - $ref: '#/components/parameters/idOrUUID' + - name: startDate + in: query + description: Filter stats by start date + schema: + type: string + format: date-time + - name: endDate + in: query + description: Filter stats by end date + schema: + type: string + format: date-time responses: '200': description: successful operation @@ -1996,6 +2008,18 @@ paths: enum: - 'viewers' - 'aggregateWatchTime' + - name: startDate + in: query + description: Filter stats by start date + schema: + type: string + format: date-time + - name: endDate + in: query + description: Filter stats by end date + schema: + type: string + format: date-time responses: '200': description: successful operation -- 2.41.0