From fe19f600dab0f6b00a7aa146ba4bd4bb96536155 Mon Sep 17 00:00:00 2001
From: Rigel Kent <sendmemail@rigelk.eu>
Date: Mon, 12 Apr 2021 11:19:07 +0200
Subject: add channel and playlist stats to server stats endpoint (#3747)

* add channel and playlist stats to nodeinfo

* add tests for active video channels stats

* fix tests for active channel stats
---
 server/models/video/video-channel.ts | 43 +++++++++++++++++++++++++++++++++++-
 1 file changed, 42 insertions(+), 1 deletion(-)

(limited to 'server/models/video/video-channel.ts')

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index d2a055f5b..b7ffbd3b1 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -1,4 +1,4 @@
-import { FindOptions, Includeable, literal, Op, ScopeOptions } from 'sequelize'
+import { FindOptions, Includeable, literal, Op, QueryTypes, ScopeOptions } from 'sequelize'
 import {
   AllowNull,
   BeforeDestroy,
@@ -338,6 +338,47 @@ export class VideoChannelModel extends Model {
     return VideoChannelModel.count(query)
   }
 
+  static async getStats () {
+
+    function getActiveVideoChannels (days: number) {
+      const options = {
+        type: QueryTypes.SELECT as QueryTypes.SELECT,
+        raw: true
+      }
+
+      const query = `
+SELECT          COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
+FROM            "videoChannel"                            AS "VideoChannelModel"
+INNER JOIN      "video"                                   AS "Videos"
+ON              "VideoChannelModel"."id" = "Videos"."channelId"
+AND             ("Videos"."publishedAt" > Now() - interval '${days}d')
+INNER JOIN      "account" AS "Account"
+ON              "VideoChannelModel"."accountId" = "Account"."id"
+INNER JOIN      "actor" AS "Account->Actor"
+ON              "Account"."actorId" = "Account->Actor"."id"
+AND             "Account->Actor"."serverId" IS NULL
+LEFT OUTER JOIN "server" AS "Account->Actor->Server"
+ON              "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
+
+      return VideoChannelModel.sequelize.query<{ count: string }>(query, options)
+                              .then(r => parseInt(r[0].count, 10))
+    }
+
+    const totalLocalVideoChannels = await VideoChannelModel.count()
+    const totalLocalDailyActiveVideoChannels = await getActiveVideoChannels(1)
+    const totalLocalWeeklyActiveVideoChannels = await getActiveVideoChannels(7)
+    const totalLocalMonthlyActiveVideoChannels = await getActiveVideoChannels(30)
+    const totalHalfYearActiveVideoChannels = await getActiveVideoChannels(180)
+
+    return {
+      totalLocalVideoChannels,
+      totalLocalDailyActiveVideoChannels,
+      totalLocalWeeklyActiveVideoChannels,
+      totalLocalMonthlyActiveVideoChannels,
+      totalHalfYearActiveVideoChannels
+    }
+  }
+
   static listForApi (parameters: {
     actorId: number
     start: number
-- 
cgit v1.2.3