Changes between Version 14 and Version 15 of AdvancedReports

Timestamp:

09/25/25 00:55:58 (5 weeks ago)

Author:

223270

Comment:

--

AdvancedReports

@@ -237 +237 @@
 }}}
 
+
+=== Годишен извештај за резервации по пакет
+Прикажува број на резервации по пакет месечно низ годината и вкупен приход, за да се согледа кои пакети се најпопуларни и најисплатливи.
+{{{
+CREATE OR REPLACE FUNCTION GetMonthlyPackageReport(year INT)
+RETURNS TABLE(
+    Month INT,
+    PackageName varchar(100),
+    ReservationCount bigint,
+    TotalRevenue numeric
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        EXTRACT(MONTH FROM r.time_point)::INT AS Month,
+        p.package_name,
+        COUNT(pr.id_reservation) AS ReservationCount,
+        SUM(p.price) AS TotalRevenue
+    FROM travel_sage.package p
+    JOIN travel_sage.package_reservation pr ON p.id_package = pr.id_package
+    JOIN travel_sage.reservation r ON pr.id_reservation = r.id_reservation
+    WHERE EXTRACT(YEAR FROM r.time_point) = year
+    GROUP BY Month, p.package_name
+    ORDER BY Month, TotalRevenue DESC;
+END;
+$$;
+}}}
+
+=== Извештај за просечен број активности по дестинација
+Прикажува колку активности има по дестинација во просек, со можност да се сортира според најпопуларни дестинации.
+{{{
+CREATE OR REPLACE FUNCTION GetAverageActivitiesPerDestination()
+RETURNS TABLE(
+    DestinationName varchar(100),
+    TotalActivities bigint,
+    AverageActivitiesPerDestination numeric
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        d.location_name AS DestinationName,
+        COUNT(a.id_activity) AS TotalActivities,
+        ROUND(COUNT(a.id_activity)::numeric / COUNT(DISTINCT d.id_destination),2) AS AverageActivitiesPerDestination
+    FROM travel_sage.destination d
+    LEFT JOIN travel_sage.activity a ON d.id_destination = a.id_destination
+    GROUP BY d.location_name
+    ORDER BY TotalActivities DESC;
+END;
+$$;
+}}}
+
+=== Извештај за дестинации со најголем процент на евтини активности
+Прикажува процент на активности по дестинација кои се евтини (<20), агрегирано, за да се препорачаат буџетски дестинации.
+{{{
+CREATE OR REPLACE FUNCTION GetDestinationsCheapActivitiesReport()
+RETURNS TABLE(
+    DestinationName varchar(100),
+    TotalActivities bigint,
+    CheapActivities bigint,
+    PercentCheapActivities numeric
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        d.location_name AS DestinationName,
+        COUNT(a.id_activity) AS TotalActivities,
+        SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) AS CheapActivities,
+        ROUND(SUM(CASE WHEN a.amount < 20 THEN 1 ELSE 0 END) * 100.0 / COUNT(a.id_activity),2) AS PercentCheapActivities
+    FROM travel_sage.destination d
+    LEFT JOIN travel_sage.activity a ON d.id_destination = a.id_destination
+    GROUP BY d.location_name
+    HAVING COUNT(a.id_activity) > 0
+    ORDER BY PercentCheapActivities DESC;
+END;
+$$;
+}}}
+
+=== Годишен извештај за активностите по месец
+Прикажува колку активности се резервирани по месец во текот на целата година, без да се гледа по корисник или пакет, за да се согледа целокупната популарност на активностите.
+{{{
+CREATE OR REPLACE FUNCTION GetMonthlyActivityReport(year INT)
+RETURNS TABLE(
+    Month INT,
+    TotalActivityReservations bigint
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        EXTRACT(MONTH FROM r.time_point)::INT AS Month,
+        COUNT(DISTINCT ar.id_activity) AS TotalActivityReservations
+    FROM travel_sage.reservation r
+    JOIN travel_sage.activity_reservation ar ON r.id_reservation = ar.id_reservation
+    WHERE EXTRACT(YEAR FROM r.time_point) = year
+    GROUP BY Month
+    ORDER BY Month;
+END;
+$$;
+}}}
+
+=== Годишен извештај за нови корисници и резервации по месец
+Агрегира број на нови корисници и број на резервации месечно за целата година. Дава целосен увид во растот на платформата.
+{{{
+CREATE OR REPLACE FUNCTION GetMonthlyUserReservationReport(year INT)
+RETURNS TABLE(
+    Month INT,
+    NewUsers bigint,
+    TotalReservations bigint
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        m.Month,
+        COUNT(DISTINCT u.id_user) AS NewUsers,
+        COALESCE(SUM(r.ReservationCount),0) AS TotalReservations
+    FROM (
+        SELECT EXTRACT(MONTH FROM birth_date)::INT AS Month, id_user
+        FROM travel_sage.users
+        WHERE EXTRACT(YEAR FROM birth_date) = year
+    ) u
+    LEFT JOIN (
+        SELECT EXTRACT(MONTH FROM time_point)::INT AS Month, id_reservation,
+               id_user, COUNT(*) AS ReservationCount
+        FROM travel_sage.reservation
+        WHERE EXTRACT(YEAR FROM time_point) = year
+        GROUP BY Month, id_reservation, id_user
+    ) r ON u.id_user = r.id_user AND u.Month = r.Month
+    RIGHT JOIN (SELECT generate_series(1,12) AS Month) m ON m.Month = u.Month OR m.Month = r.Month
+    GROUP BY m.Month
+    ORDER BY m.Month;
+END;
+$$;
+}}}
+
 === Дестинации со најголем број на разновидни настани
 Идентификување на дестинации со најголем број на различни видови на настани (концерти, фестивали, саеми и сл.).