Changes between Initial Version and Version 1 of advancedreports

Timestamp:

01/30/26 14:30:38 (5 hours ago)

Author:

231175

Comment:

--

advancedreports

@@ +1 @@
+= Напредни извештаи од базата =
+
+== 1. Промет по курс ==
+
+'''Опис:''' Извештајот го прикажува вкупниот промет генериран од сите плаќања за секоја верзија на секој курс, филтриран по корисник и јазик на превод.
+
+'''SQL:'''
+{{{
+SELECT
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    cv.version_number AS course_version,
+    cv.active AS is_active,
+    SUM(p.amount) AS total_amount
+FROM course c
+         JOIN course_translate ct ON c.id = ct.course_id
+         JOIN course_version cv ON c.id = cv.course_id
+         JOIN enrollment e ON e.course_version_id = cv.id
+         JOIN payment p ON p.enrollment_id = e.id
+WHERE e.user_id = :user_id
+  AND ct.language = :language
+GROUP BY
+    c.id,
+    ct.title_short,
+    cv.version_number;
+}}}
+
+'''Параметри:'''
+* user_id: ИД на корисникот
+* language: јазик за превод
+
+'''Релациона алгебра:'''
+{{{
+π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active, total_amount←SUM(p.amount) (
+  γ c.id, ct.title_short, cv.version_number, cv.active; SUM(p.amount) (
+    σ e.user_id = :user_id ∧ ct.language = :language (
+      course ⋈ c.id = ct.course_id course_translate 
+             ⋈ c.id = cv.course_id course_version 
+             ⋈ cv.id = e.course_version_id enrollment 
+             ⋈ e.id = p.enrollment_id payment
+    )
+  )
+)
+}}}
+
+----
+
+== 2. Промет по месец ==
+
+'''Опис:''' Извештајот го прикажува вкупниот промет групиран по година и месец, сортиран од најнов кон најстар период.
+
+'''SQL:'''
+{{{
+SELECT
+    EXTRACT(YEAR FROM p.payment_date) AS year,
+    EXTRACT(MONTH FROM p.payment_date) AS month,
+    SUM(p.amount) AS total_revenue
+FROM payment p
+GROUP BY
+    EXTRACT(YEAR FROM p.payment_date),
+    EXTRACT(MONTH FROM p.payment_date)
+ORDER BY
+    year DESC,
+    month DESC;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+τ year DESC, month DESC (
+  π year←EXTRACT(YEAR FROM payment_date), month←EXTRACT(MONTH FROM payment_date), total_revenue←SUM(amount) (
+    γ EXTRACT(YEAR FROM payment_date), EXTRACT(MONTH FROM payment_date); SUM(amount) (
+      payment
+    )
+  )
+)
+}}}
+
+----
+
+== 3. Најпопуларни курсеви според оценка ==
+
+'''Опис:''' Извештајот ги прикажува сите курсеви со нивните верзии, вкупен број на запишувања, просечна оценка и вкупен број на рецензии, сортирани по најдобра просечна оценка.
+
+'''SQL:'''
+{{{
+SELECT 
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    cv.version_number AS course_version,
+    cv.active AS is_active,
+    COUNT(DISTINCT e.id) AS total_enrollments,
+    SUM(r.rating) / COUNT(r.id) AS average_rating,
+    COUNT(r.id) AS total_reviews
+FROM course c
+JOIN course_translate ct ON c.id = ct.course_id
+JOIN course_version cv ON c.id = cv.course_id
+JOIN enrollment e ON cv.id = e.course_version_id
+JOIN review r ON e.id = r.enrollment_id
+WHERE ct.language = :language
+GROUP BY c.id, ct.id, cv.id
+ORDER BY SUM(r.rating) / COUNT(r.id) DESC
+}}}
+
+'''Параметри:'''
+* language: јазик за превод
+
+'''Релациона алгебра:'''
+{{{
+τ average_rating DESC (
+  π course_id←c.id, course_title←ct.title_short, course_version←cv.version_number, is_active←cv.active, 
+     total_enrollments←COUNT(DISTINCT e.id), average_rating←SUM(r.rating)/COUNT(r.id), total_reviews←COUNT(r.id) (
+    γ c.id, ct.id, cv.id; COUNT(DISTINCT e.id), SUM(r.rating), COUNT(r.id) (
+      σ ct.language = :language (
+        course ⋈ c.id = ct.course_id course_translate 
+               ⋈ c.id = cv.course_id course_version 
+               ⋈ cv.id = e.course_version_id enrollment 
+               ⋈ e.id = r.enrollment_id review
+      )
+    )
+  )
+)
+}}}
+
+----