406 lines
12 KiB
Go
406 lines
12 KiB
Go
package analyticsApi
|
|
|
|
import (
|
|
"encoding/json"
|
|
"git.stuve.uni-ulm.de/stuve-it/stuve-it-backend/ldapApi"
|
|
"git.stuve.uni-ulm.de/stuve-it/stuve-it-backend/logger"
|
|
"github.com/labstack/echo/v5"
|
|
"github.com/pocketbase/dbx"
|
|
"github.com/pocketbase/pocketbase"
|
|
"github.com/pocketbase/pocketbase/apis"
|
|
"github.com/pocketbase/pocketbase/core"
|
|
"strconv"
|
|
"time"
|
|
)
|
|
|
|
// initPageViewCount
|
|
//
|
|
// This endpoint is used to retrieve paginated page view counts for each unique path.
|
|
// It adds the following endpoint to the app:
|
|
// GET /api/analytics/pageViewCount
|
|
//
|
|
// Query parameters:
|
|
//
|
|
// ?startDate=ISO 8601 formatted date (optional)
|
|
// ?page=<page number>
|
|
// ?perPage=<results per page>
|
|
// ?sort=<ASC|DESC> (default is ASC)
|
|
//
|
|
// - `startDate`: Optional start date in ISO 8601 format (e.g., "2024-11-12T15:04:05Z").
|
|
// - `page`: Page number for pagination (default is 1).
|
|
// - `perPage`: Number of results per page (default is 10).
|
|
// - `sort`: Sorting order, either `ASC` or `DESC` (default is ASC).
|
|
//
|
|
// Response format:
|
|
//
|
|
// {
|
|
// "page": <current page number>,
|
|
// "perPage": <results per page>,
|
|
// "totalItems": <total number of items matching the filter>,
|
|
// "totalPages": <total number of pages>,
|
|
// "items": [
|
|
// {
|
|
// "id": <unique row id>,
|
|
// "path": "<path>",
|
|
// "count": <total view count since startDate>,
|
|
// "last_30_days_data": [
|
|
// {
|
|
// "date": YYYY-MM-DD,
|
|
// "count": <daily count>
|
|
// },
|
|
// ...
|
|
// ]
|
|
// },
|
|
// ...
|
|
// ]
|
|
// }
|
|
func initPageViewCount(app *pocketbase.PocketBase, e *core.ServeEvent) {
|
|
|
|
e.Router.GET("/api/analytics/pageViewCount", func(c echo.Context) error {
|
|
|
|
// Check if user is in admin group
|
|
if err := ldapApi.UserIsInAdminGroup(app, c); err != nil {
|
|
return err
|
|
}
|
|
|
|
// Get startDate from query parameter
|
|
startDateParam := c.QueryParam("startDate")
|
|
var startDate time.Time
|
|
var err error
|
|
if startDateParam != "" {
|
|
startDate, err = time.Parse(time.RFC3339, startDateParam)
|
|
if err != nil {
|
|
return apis.NewBadRequestError("Invalid start date format, expected ISO 8601", err)
|
|
}
|
|
} else {
|
|
// Default to the beginning of time if not provided
|
|
startDate = time.Time{}
|
|
}
|
|
|
|
// Pagination and sorting parameters
|
|
page, _ := strconv.Atoi(c.QueryParam("page"))
|
|
if page < 1 {
|
|
page = 1
|
|
}
|
|
|
|
perPage, _ := strconv.Atoi(c.QueryParam("perPage"))
|
|
if perPage < 1 {
|
|
perPage = 10
|
|
}
|
|
|
|
sort := c.QueryParam("sort")
|
|
if sort != "ASC" && sort != "DESC" {
|
|
sort = "DESC" // default sorting order
|
|
}
|
|
|
|
offset := (page - 1) * perPage
|
|
|
|
// Fetch total items for pagination
|
|
var totalItems int
|
|
err = app.Dao().DB().
|
|
NewQuery(`
|
|
SELECT COUNT(DISTINCT path) as total
|
|
FROM analyticsPageViews
|
|
WHERE created >= {:startDate}
|
|
`).
|
|
Bind(dbx.Params{
|
|
"startDate": startDate,
|
|
}).
|
|
Row(&totalItems)
|
|
|
|
if err != nil {
|
|
return apis.NewApiError(500, "Failed to count total items", err)
|
|
}
|
|
|
|
totalPages := (totalItems + perPage - 1) / perPage // Calculate total pages
|
|
|
|
// Define the structure for items
|
|
type Last30DaysData struct {
|
|
Date string `json:"date"`
|
|
Count int `json:"count"`
|
|
}
|
|
|
|
type Item struct {
|
|
Path string `json:"path"`
|
|
Count int `json:"count"`
|
|
Last30DaysData []Last30DaysData `json:"last_30_days_data"`
|
|
}
|
|
|
|
// Query paginated items
|
|
var rawItems []struct {
|
|
Path string `db:"path" json:"path"`
|
|
Count int `db:"count" json:"count"`
|
|
Last30DaysData string `db:"last_30_days_data" json:"last_30_days_data"` // JSON string to parse
|
|
}
|
|
|
|
err = app.Dao().DB().
|
|
NewQuery(`
|
|
SELECT
|
|
view.path,
|
|
COUNT(view.id) AS count,
|
|
(
|
|
SELECT json_group_array(
|
|
json_object(
|
|
'date', date,
|
|
'count', daily_count
|
|
)
|
|
)
|
|
FROM (
|
|
SELECT
|
|
strftime('%Y-%m-%d', created) AS date,
|
|
COUNT(id) AS daily_count
|
|
FROM analyticsPageViews
|
|
WHERE
|
|
path = view.path
|
|
AND created >= datetime('now', '-30 days')
|
|
GROUP BY date
|
|
) AS daily_data
|
|
) AS last_30_days_data
|
|
FROM analyticsPageViews AS view
|
|
WHERE view.created >= {:startDate}
|
|
GROUP BY view.path
|
|
ORDER BY count ` + sort + `
|
|
LIMIT {:perPage} OFFSET {:offset}
|
|
`).
|
|
Bind(dbx.Params{
|
|
"startDate": startDate,
|
|
"perPage": perPage,
|
|
"offset": offset,
|
|
}).
|
|
All(&rawItems)
|
|
|
|
if err != nil {
|
|
return apis.NewApiError(500, "Failed to query page view data", err)
|
|
}
|
|
|
|
// Parse last_30_days_data JSON string into structured data
|
|
var items []Item
|
|
for _, rawItem := range rawItems {
|
|
var last30DaysData []Last30DaysData
|
|
if rawItem.Last30DaysData != "" {
|
|
if err := json.Unmarshal([]byte(rawItem.Last30DaysData), &last30DaysData); err != nil {
|
|
return apis.NewApiError(500, "Failed to parse last_30_days_data", err)
|
|
}
|
|
}
|
|
|
|
items = append(items, Item{
|
|
Path: rawItem.Path,
|
|
Count: rawItem.Count,
|
|
Last30DaysData: last30DaysData,
|
|
})
|
|
}
|
|
|
|
// Final response structure
|
|
response := map[string]interface{}{
|
|
"page": page,
|
|
"perPage": perPage,
|
|
"totalItems": totalItems,
|
|
"totalPages": totalPages,
|
|
"items": items,
|
|
}
|
|
|
|
// Return the final JSON response
|
|
return c.JSON(200, response)
|
|
}, apis.ActivityLogger(app))
|
|
}
|
|
|
|
// initAggregateCount
|
|
//
|
|
// This endpoint is used to aggregate session metadata counts by various categories
|
|
// (device type, browser name, operating system, user agent, geo country code, and preferred language).
|
|
// It adds the following endpoint to the app:
|
|
// GET /api/analytics/sessionCounts
|
|
//
|
|
// The endpoint expects the following query parameter:
|
|
//
|
|
// ?startDate=ISO 8601 formatted date (optional)
|
|
//
|
|
// - `startDate`: An optional start date in ISO 8601 format (e.g., "2024-11-12T15:04:05Z").
|
|
// If provided, counts reflect sessions from this date onward.
|
|
// If not provided, counts include all sessions.
|
|
//
|
|
// Response format:
|
|
//
|
|
// {
|
|
// "device_type": [
|
|
// { "value": "<device type>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ],
|
|
// "browser_name": [
|
|
// { "value": "<browser name>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ],
|
|
// "operating_system": [
|
|
// { "value": "<operating system>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ],
|
|
// "user_agent": [
|
|
// { "value": "<user agent>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ],
|
|
// "geo_country_code": [
|
|
// { "value": "<country code>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ],
|
|
// "preferred_language": [
|
|
// { "value": "<preferred language>", "count": <session count>, "error_count": <error count>, "success_count": <success count> },
|
|
// ...
|
|
// ]
|
|
// }
|
|
//
|
|
// Each category includes an array of objects where `value` is the specific item (e.g., a device type or browser name),
|
|
// `count` is the total number of sessions matching that item since `startDate`, and `error_count` is the number
|
|
// of sessions with errors for that item (where the `error` field is not NULL) since `startDate`.
|
|
func initAggregateCount(app *pocketbase.PocketBase, e *core.ServeEvent) {
|
|
e.Router.GET("/api/analytics/aggregateCount", func(c echo.Context) error {
|
|
|
|
// Check if user is in admin group
|
|
if err := ldapApi.UserIsInAdminGroup(app, c); err != nil {
|
|
return err
|
|
}
|
|
|
|
// Parse the start date
|
|
startDate, err := time.Parse(time.RFC3339, c.QueryParam("startDate"))
|
|
if err != nil {
|
|
return apis.NewBadRequestError("Invalid start date format, expected ISO 8601", err)
|
|
}
|
|
|
|
type Data struct {
|
|
Value string `db:"value" json:"value"`
|
|
Count int `db:"count" json:"count"`
|
|
SuccessCount int `db:"success_count" json:"success_count"`
|
|
ErrorCount int `db:"error_count" json:"error_count"`
|
|
}
|
|
|
|
// Use a map to store the response data
|
|
response := make(map[string][]Data)
|
|
|
|
fields := []string{"device_type", "browser_name", "operating_system", "geo_country_code", "preferred_language"}
|
|
|
|
for _, field := range fields {
|
|
data := []Data{}
|
|
|
|
err := app.Dao().DB().
|
|
NewQuery(`
|
|
SELECT
|
|
IFNULL(` + field + `, 'N/A') as value,
|
|
COUNT(id) AS count,
|
|
SUM(CASE WHEN error IS NULL THEN 1 ELSE 0 END) AS success_count,
|
|
SUM(CASE WHEN error IS NOT NULL THEN 1 ELSE 0 END) AS error_count
|
|
FROM analyticsPageViewsWithSessionDetail
|
|
WHERE created >= {:startDate}
|
|
GROUP BY ` + field).
|
|
Bind(dbx.Params{
|
|
"startDate": startDate,
|
|
}).
|
|
All(&data)
|
|
|
|
if err != nil {
|
|
return apis.NewApiError(500, "Failed to aggregate data for "+field, err)
|
|
}
|
|
|
|
// Assign the data to the corresponding field in the map
|
|
response[field] = data
|
|
}
|
|
|
|
// Return the final JSON response
|
|
return c.JSON(200, response)
|
|
}, apis.ActivityLogger(app))
|
|
}
|
|
|
|
// initDailyAnalyticsAggregates
|
|
//
|
|
// This endpoint aggregates daily analytics data for a specified date range from `startDate` to `now`.
|
|
// It adds the following endpoint to the app:
|
|
// GET /api/analytics/dailyAggregates
|
|
//
|
|
// The endpoint expects the following query parameter:
|
|
//
|
|
// ?startDate=ISO 8601 formatted date (optional)
|
|
//
|
|
// - `startDate`: An optional start date in ISO 8601 format (e.g., "2024-11-12T15:04:05Z").
|
|
// If provided, aggregates are calculated from this date onward up to the current date.
|
|
// If not provided, all data until today is included.
|
|
//
|
|
// Response format:
|
|
//
|
|
// [
|
|
// {
|
|
// "date": "YYYY-MM-DD",
|
|
// "error_count": <error count for that day>,
|
|
// "page_view_count": <total page views for that day>,
|
|
// "unique_visitor_count": <unique visitors for that day>
|
|
// },
|
|
// ...
|
|
// ]
|
|
func initDailyAnalyticsAggregates(app *pocketbase.PocketBase, e *core.ServeEvent) {
|
|
e.Router.GET("/api/analytics/dailyAggregates", func(c echo.Context) error {
|
|
|
|
// Check if user is in admin group
|
|
if err := ldapApi.UserIsInAdminGroup(app, c); err != nil {
|
|
return err
|
|
}
|
|
|
|
// Parse the start date
|
|
startDateParam := c.QueryParam("startDate")
|
|
var startDate time.Time
|
|
var err error
|
|
if startDateParam != "" {
|
|
startDate, err = time.Parse(time.RFC3339, startDateParam)
|
|
if err != nil {
|
|
return apis.NewBadRequestError("Invalid start date format, expected ISO 8601", err)
|
|
}
|
|
} else {
|
|
// Default to the beginning of time if not provided
|
|
startDate = time.Time{}
|
|
}
|
|
|
|
// Define the structure for the daily aggregate data
|
|
type DailyAggregate struct {
|
|
Date string `db:"date" json:"date"`
|
|
ErrorCount int `db:"error_count" json:"error_count"`
|
|
PageViewCount int `db:"page_view_count" json:"page_view_count"`
|
|
UniqueVisitorCount int `db:"unique_visitor_count" json:"unique_visitor_count"`
|
|
}
|
|
|
|
var results []DailyAggregate
|
|
|
|
// Query to aggregate daily data within the specified date range
|
|
err = app.Dao().DB().
|
|
NewQuery(`
|
|
SELECT
|
|
strftime('%Y-%m-%d', created) AS date,
|
|
COUNT(id) AS page_view_count,
|
|
SUM(CASE WHEN error IS NOT NULL THEN 1 ELSE 0 END) AS error_count,
|
|
COUNT(DISTINCT visitor) AS unique_visitor_count
|
|
FROM analyticsPageViewsWithSessionDetail
|
|
WHERE created >= {:startDate}
|
|
GROUP BY date
|
|
ORDER BY date ASC
|
|
`).
|
|
Bind(dbx.Params{
|
|
"startDate": startDate,
|
|
}).
|
|
All(&results)
|
|
|
|
if err != nil {
|
|
return apis.NewApiError(500, "Failed to query daily analytics aggregates", err)
|
|
}
|
|
|
|
// Return the final JSON response
|
|
return c.JSON(200, results)
|
|
}, apis.ActivityLogger(app))
|
|
}
|
|
|
|
// InitAnalyticsApi initializes analytics api endpoints
|
|
func InitAnalyticsApi(app *pocketbase.PocketBase, e *core.ServeEvent) error {
|
|
|
|
logger.LogInfoF("Initializing analytics api")
|
|
|
|
initPageViewCount(app, e)
|
|
initAggregateCount(app, e)
|
|
initDailyAnalyticsAggregates(app, e)
|
|
|
|
return nil
|
|
}
|