Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

05/24/26 16:38:26 (36 hours ago)

Author:

202033

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)
+
+== 1. Месечни приходи и перформанси на плаќања
+  По месец прикажува колку плаќања има, колку се успешни/неуспешни, колку е приходот од термини наспроти приходи од купување пакет, и месечна
+  промена на приход.
+=== SQL
+{{{
+WITH bounds AS (
+  SELECT
+    date_trunc('month', MIN(p."timestamp"))::date AS min_month,
+    date_trunc('month', MAX(p."timestamp"))::date AS max_month
+  FROM payment p
+),
+months AS (
+  SELECT generate_series(min_month, max_month, interval '1 month')::date AS month_start
+  FROM bounds
+),
+pay AS (
+  SELECT
+    date_trunc('month', p."timestamp")::date AS month_start,
+    p.status,
+    p.amount,
+    p.appointment_id,
+    p.package_purchase_id
+  FROM payment p
+),
+agg AS (
+  SELECT
+    m.month_start,
+    COUNT(pay.*) AS payment_cnt,
+    COUNT(*) FILTER (WHERE pay.status = 'PAID') AS paid_cnt,
+    COUNT(*) FILTER (WHERE pay.status = 'PENDING') AS pending_cnt,
+    COUNT(*) FILTER (WHERE pay.status = 'FAILED') AS failed_cnt,
+    COUNT(*) FILTER (WHERE pay.status = 'REFUNDED') AS refunded_cnt,
+    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total,
+    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'REFUNDED'), 0)::numeric(12,2) AS refunded_amount_total,
+    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID' AND pay.appointment_id IS NOT NULL), 0)::numeric(12,2) AS paid_appointment_amount,
+    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID' AND pay.appointment_id IS NULL AND pay.package_purchase_id IS NOT NULL), 0)::numeric(12,2) AS paid_package_amount
+  FROM months m
+  LEFT JOIN pay ON pay.month_start = m.month_start
+  GROUP BY m.month_start
+)
+SELECT
+  a.month_start,
+  a.payment_cnt,
+  a.paid_cnt,
+  a.pending_cnt,
+  a.failed_cnt,
+  a.refunded_cnt,
+  a.paid_amount_total,
+  a.refunded_amount_total,
+  a.paid_appointment_amount,
+  a.paid_package_amount,
+  ROUND(100.0 * a.paid_cnt / NULLIF(a.payment_cnt, 0), 2) AS paid_rate_pct,
+  ROUND(100.0 * a.failed_cnt / NULLIF(a.payment_cnt, 0), 2) AS failed_rate_pct,
+  ROUND(
+    a.paid_amount_total
+    - COALESCE(LAG(a.paid_amount_total) OVER (ORDER BY a.month_start), 0),
+    2
+  ) AS mom_paid_amount_change
+FROM agg a
+ORDER BY a.month_start;
+
+}}}
+
+=== Релациона алгебра
+
+
+{{{
+B ← γ_{min_m:=min(month(ts)), max_m:=max(month(ts))}(Payment)
+M ← generate_series(B.min_m, B.max_m, 1 month)
+P ← π_{month:=month(ts), status, amount, appointment_id, package_purchase_id}(Payment)
+R ← γ_{month; cnt:=count(*), paid:=count_{status='PAID'}(*), pending:=count_{status='PENDING'}(*), failed:=count_{status='FAILED'}(*), refunded:=count_{status='REFUNDED'}(*), paid_sum:=sum_{status='PAID'}(amount), paid_appt:=sum_{status='PAID'∧appointment_id≠NULL}(amount), paid_pkg:=sum_{status='PAID'∧appointment_id=NULL∧package_purchase_id≠NULL}(amount)}(M ⟕_{M.month=P.month} P)
+}}}
+
+Овој извештај веднаш покажува дали растот доаѓа од класични термини или од продажба на пакети и дали има пад на success rate по месеци.
+
+
+
+== 2. Најпопуларни услуги според закажувања, приход и оценки
+
+ За секоја услуга покажува број на закажувања по статус, алоциран платен приход, просечна оценка и број рецензии, со ранг на популарност.
+
+=== SQL
+
+{{{
+WITH appointment_base AS (
+  SELECT
+    a.appointment_id,
+    UPPER(s.name) AS status_name
+  FROM appointment a
+  JOIN status s ON s.status_id = a.status_id
+),
+service_lines AS (
+  SELECT
+    aps.appointment_id,
+    aps.service_id,
+    sv.name AS service_name,
+    c.name AS category_name,
+    sv.price AS service_list_price
+  FROM appointmentservice aps
+  JOIN service sv ON sv.service_id = aps.service_id
+  LEFT JOIN category c ON c.category_id = sv.category_id
+),
+appointment_list_totals AS (
+  SELECT
+    sl.appointment_id,
+    SUM(sl.service_list_price) AS list_total_price
+  FROM service_lines sl
+  GROUP BY sl.appointment_id
+),
+paid_amount_per_appt AS (
+  SELECT
+    p.appointment_id,
+    MAX(p.amount) AS paid_amount
+  FROM payment p
+  WHERE p.status = 'PAID'
+    AND p.appointment_id IS NOT NULL
+  GROUP BY p.appointment_id
+),
+revenue_alloc AS (
+  SELECT
+    sl.appointment_id,
+    sl.service_id,
+    (pa.paid_amount * sl.service_list_price / NULLIF(alt.list_total_price, 0))::numeric(12,2) AS allocated_amount
+  FROM service_lines sl
+  JOIN appointment_list_totals alt ON alt.appointment_id = sl.appointment_id
+  JOIN paid_amount_per_appt pa ON pa.appointment_id = sl.appointment_id
+),
+reviews_by_service AS (
+  SELECT
+    aps.service_id,
+    ROUND(AVG(r.rating)::numeric, 2) AS avg_rating,
+    COUNT(r.review_id) AS review_count
+  FROM appointmentservice aps
+  JOIN payment p
+    ON p.appointment_id = aps.appointment_id
+   AND p.status = 'PAID'
+  JOIN review r ON r.payment_id = p.payment_id
+  GROUP BY aps.service_id
+),
+service_stats AS (
+  SELECT
+    sl.service_id,
+    sl.service_name,
+    sl.category_name,
+    COUNT(*) AS bookings_total,
+    COUNT(*) FILTER (WHERE ab.status_name = 'COMPLETED') AS completed_bookings,
+    COUNT(*) FILTER (WHERE ab.status_name = 'SCHEDULED') AS scheduled_bookings,
+    COUNT(*) FILTER (WHERE ab.status_name = 'CANCELLED') AS cancelled_bookings,
+    COUNT(*) FILTER (WHERE ab.status_name = 'NO_SHOW') AS no_show_bookings,
+    COALESCE(SUM(ra.allocated_amount), 0)::numeric(12,2) AS allocated_paid_revenue
+  FROM service_lines sl
+  JOIN appointment_base ab ON ab.appointment_id = sl.appointment_id
+  LEFT JOIN revenue_alloc ra
+    ON ra.appointment_id = sl.appointment_id
+   AND ra.service_id = sl.service_id
+  GROUP BY sl.service_id, sl.service_name, sl.category_name
+)
+SELECT
+  ss.service_id,
+  ss.service_name,
+  ss.category_name,
+  ss.bookings_total,
+  ss.completed_bookings,
+  ss.scheduled_bookings,
+  ss.cancelled_bookings,
+  ss.no_show_bookings,
+  ss.allocated_paid_revenue,
+  COALESCE(rv.avg_rating, 0)::numeric(4,2) AS avg_rating,
+  COALESCE(rv.review_count, 0) AS review_count,
+  DENSE_RANK() OVER (
+    ORDER BY ss.completed_bookings DESC, ss.allocated_paid_revenue DESC
+  ) AS popularity_rank
+FROM service_stats ss
+LEFT JOIN reviews_by_service rv ON rv.service_id = ss.service_id
+ORDER BY popularity_rank, ss.service_id;
+
+}}}
+
+
+=== Релациона алгебра
+
+{{{
+SL ← AppointmentService ⨝ Service ⨝ Category
+AB ← Appointment ⨝ Status
+T ← γ_{appointment_id; list_total:=Σ(service_price)}(SL)
+PP ← σ_{status='PAID' ∧ appointment_id≠NULL}(Payment)
+RA ← γ_{appointment_id, service_id; alloc:=paid_amount*service_price/list_total}(SL ⨝ T ⨝ PP)
+RV ← γ_{service_id; avg_rating:=AVG(rating), review_count:=COUNT(*)}(AppointmentService ⨝ PP ⨝ Review)
+S ← γ_{service_id, service_name, category_name; bookings:=COUNT(*), completed:=COUNT_{status='COMPLETED'}(*), revenue:=Σ(alloc)}(SL ⨝ AB ⟕ RA)
+Result ← S ⟕ RV
+}}}
+
+Ова е најкорисен top services извештај за одлуки, кои услуги се чести, кои носат реален приход и како се оценети од клиенти.
+
+
+== 3. Лојалност и активност на клиенти
+
+По корисник дава извештај за активност со термини, платени/неуспешни плаќања, купени пакети, искористеност и моментални loyalty поени.
+
+=== SQL
+
+{{{
+WITH appt_agg AS (
+  SELECT
+    a.user_id,
+    COUNT(*) AS appointments_total,
+    COUNT(*) FILTER (WHERE UPPER(s.name) = 'SCHEDULED') AS scheduled_cnt,
+    COUNT(*) FILTER (WHERE UPPER(s.name) = 'COMPLETED') AS completed_cnt,
+    COUNT(*) FILTER (WHERE UPPER(s.name) = 'CANCELLED') AS cancelled_cnt,
+    COUNT(*) FILTER (WHERE UPPER(s.name) = 'NO_SHOW') AS no_show_cnt,
+    MAX(a.appointment_time) AS last_appointment_at
+  FROM appointment a
+  JOIN status s ON s.status_id = a.status_id
+  GROUP BY a.user_id
+),
+pay_user_map AS (
+  SELECT
+    p.payment_id,
+    COALESCE(a.user_id, upp.user_id) AS user_id,
+    p.status,
+    p.amount,
+    p.points_used,
+    p.appointment_id,
+    p.package_purchase_id,
+    p."timestamp"
+  FROM payment p
+  LEFT JOIN appointment a ON a.appointment_id = p.appointment_id
+  LEFT JOIN userpackagepurchase upp ON upp.purchase_id = p.package_purchase_id
+),
+pay_agg AS (
+  SELECT
+    pum.user_id,
+    COUNT(*) AS payments_total,
+    COUNT(*) FILTER (WHERE pum.status = 'PAID') AS paid_payments_cnt,
+    COUNT(*) FILTER (WHERE pum.status = 'FAILED') AS failed_payments_cnt,
+    COUNT(*) FILTER (WHERE pum.status = 'REFUNDED') AS refunded_payments_cnt,
+    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total,
+    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID' AND pum.appointment_id IS NOT NULL), 0)::numeric(12,2) AS paid_appointment_amount,
+    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID' AND pum.appointment_id IS NULL AND pum.package_purchase_id IS NOT NULL), 0)::numeric(12,2) AS paid_package_amount,
+    COALESCE(SUM(pum.points_used) FILTER (WHERE pum.status = 'PAID'), 0) AS points_used_total,
+    MAX(pum."timestamp") AS last_payment_at
+  FROM pay_user_map pum
+  WHERE pum.user_id IS NOT NULL
+  GROUP BY pum.user_id
+),
+pkg_agg AS (
+  SELECT
+    upp.user_id,
+    COUNT(*) AS package_purchases_total,
+    COUNT(*) FILTER (WHERE upp.status = 'ACTIVE') AS active_packages_cnt,
+    COALESCE(SUM(upp.total_uses), 0) AS total_package_uses_bought,
+    COALESCE(SUM(upp.remaining_uses), 0) AS total_package_uses_remaining,
+    COALESCE(SUM(upp.total_uses - upp.remaining_uses), 0) AS total_package_uses_used,
+    MAX(upp.purchased_at) AS last_package_purchase_at
+  FROM userpackagepurchase upp
+  GROUP BY upp.user_id
+)
+SELECT
+  u.user_id,
+  u.full_name,
+  u.email,
+  COALESCE(lc.points, 0) AS loyalty_points_balance,
+  COALESCE(a.appointments_total, 0) AS appointments_total,
+  COALESCE(a.completed_cnt, 0) AS completed_cnt,
+  COALESCE(a.scheduled_cnt, 0) AS scheduled_cnt,
+  COALESCE(a.cancelled_cnt, 0) AS cancelled_cnt,
+  COALESCE(a.no_show_cnt, 0) AS no_show_cnt,
+  ROUND(100.0 * COALESCE(a.completed_cnt, 0) / NULLIF(COALESCE(a.appointments_total, 0), 0), 2) AS completion_rate_pct,
+  COALESCE(p.payments_total, 0) AS payments_total,
+  COALESCE(p.paid_payments_cnt, 0) AS paid_payments_cnt,
+  COALESCE(p.failed_payments_cnt, 0) AS failed_payments_cnt,
+  COALESCE(p.refunded_payments_cnt, 0) AS refunded_payments_cnt,
+  COALESCE(p.paid_amount_total, 0)::numeric(12,2) AS paid_amount_total,
+  COALESCE(p.paid_appointment_amount, 0)::numeric(12,2) AS paid_appointment_amount,
+  COALESCE(p.paid_package_amount, 0)::numeric(12,2) AS paid_package_amount,
+  COALESCE(p.points_used_total, 0) AS points_used_total,
+  COALESCE(pk.package_purchases_total, 0) AS package_purchases_total,
+  COALESCE(pk.active_packages_cnt, 0) AS active_packages_cnt,
+  COALESCE(pk.total_package_uses_bought, 0) AS total_package_uses_bought,
+  COALESCE(pk.total_package_uses_used, 0) AS total_package_uses_used,
+  COALESCE(pk.total_package_uses_remaining, 0) AS total_package_uses_remaining,
+  ROW_NUMBER() OVER (
+    ORDER BY COALESCE(p.paid_amount_total, 0) DESC, COALESCE(lc.points, 0) DESC, u.user_id
+  ) AS value_rank
+FROM "User" u
+LEFT JOIN loyaltycard lc ON lc.user_id = u.user_id
+LEFT JOIN appt_agg a ON a.user_id = u.user_id
+LEFT JOIN pay_agg p ON p.user_id = u.user_id
+LEFT JOIN pkg_agg pk ON pk.user_id = u.user_id
+ORDER BY value_rank;
+
+}}}
+
+=== Релациона алгебра
+
+{{{
+A ← γ_{user_id; appt_total:=COUNT(*), completed:=COUNT_{status='COMPLETED'}(*), ...}(Appointment ⨝ Status)
+P0 ← Payment ⟕ Appointment ⟕ UserPackagePurchase
+P ← γ_{user_id; paid_sum:=Σ_{status='PAID'}(amount), paid_cnt:=COUNT_{status='PAID'}(*), ...}(P0)
+K ← γ_{user_id; pkg_cnt:=COUNT(*), active_pkg:=COUNT_{status='ACTIVE'}(*), uses_bought:=Σ(total_uses), uses_rem:=Σ(remaining_uses)}(UserPackagePurchase)
+Result ← User ⟕ LoyaltyCard ⟕ A ⟕ P ⟕ K
+}}}
+
+Извештајот е добар за CRM сегментација: брзо се гледа кои клиенти носат најмногу приход, колку се активни и каков им е loyalty балансот.
+
+
+== 4. Продажба и искористеност на пакети
+За секој пакет покажува колку е продаден, колку е исплатен, колку користења се потрошени/останати и колку е „популарен“ во реална употреба.
+
+=== SQL
+
+{{{
+WITH package_base AS (
+  SELECT
+    p.package_id,
+    p.name AS package_name,
+    p.max_usage,
+    p.total_price,
+    COUNT(ps.service_id) AS services_in_package,
+    COALESCE(SUM(ps.discounted_price), 0)::numeric(12,2) AS computed_services_price_sum
+  FROM package p
+  LEFT JOIN packageservice ps ON ps.package_id = p.package_id
+  GROUP BY p.package_id, p.name, p.max_usage, p.total_price
+),
+purchase_stats AS (
+  SELECT
+    upp.package_id,
+    COUNT(*) AS purchases_cnt,
+    COUNT(*) FILTER (WHERE upp.status = 'ACTIVE') AS active_purchases_cnt,
+    COUNT(*) FILTER (WHERE upp.status = 'EXPIRED') AS expired_purchases_cnt,
+    COUNT(*) FILTER (WHERE upp.status = 'CANCELLED') AS cancelled_purchases_cnt,
+    COALESCE(SUM(upp.total_uses), 0) AS total_uses_sold,
+    COALESCE(SUM(upp.remaining_uses), 0) AS total_uses_remaining,
+    COALESCE(SUM(upp.total_uses - upp.remaining_uses), 0) AS total_uses_used
+  FROM userpackagepurchase upp
+  GROUP BY upp.package_id
+),
+package_payment_stats AS (
+  SELECT
+    upp.package_id,
+    COUNT(*) FILTER (WHERE p.status = 'PAID') AS paid_payments_cnt,
+    COUNT(*) FILTER (WHERE p.status = 'PENDING') AS pending_payments_cnt,
+    COUNT(*) FILTER (WHERE p.status = 'FAILED') AS failed_payments_cnt,
+    COUNT(*) FILTER (WHERE p.status = 'REFUNDED') AS refunded_payments_cnt,
+    COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total
+  FROM payment p
+  JOIN userpackagepurchase upp ON upp.purchase_id = p.package_purchase_id
+  WHERE p.appointment_id IS NULL
+  GROUP BY upp.package_id
+),
+usage_ops AS (
+  SELECT
+    upp.package_id,
+    COUNT(apu.*) AS linked_appointment_usages,
+    COUNT(*) FILTER (WHERE apu.finalized_at IS NOT NULL) AS finalized_usages
+  FROM userpackagepurchase upp
+  LEFT JOIN appointmentpackageusage apu ON apu.purchase_id = upp.purchase_id
+  GROUP BY upp.package_id
+)
+SELECT
+  pb.package_id,
+  pb.package_name,
+  pb.max_usage,
+  pb.total_price,
+  pb.services_in_package,
+  pb.computed_services_price_sum,
+  COALESCE(ps.purchases_cnt, 0) AS purchases_cnt,
+  COALESCE(ps.active_purchases_cnt, 0) AS active_purchases_cnt,
+  COALESCE(ps.expired_purchases_cnt, 0) AS expired_purchases_cnt,
+  COALESCE(ps.cancelled_purchases_cnt, 0) AS cancelled_purchases_cnt,
+  COALESCE(ps.total_uses_sold, 0) AS total_uses_sold,
+  COALESCE(ps.total_uses_used, 0) AS total_uses_used,
+  COALESCE(ps.total_uses_remaining, 0) AS total_uses_remaining,
+  ROUND(100.0 * COALESCE(ps.total_uses_used, 0) / NULLIF(COALESCE(ps.total_uses_sold, 0), 0), 2) AS usage_rate_pct,
+  COALESCE(pps.paid_payments_cnt, 0) AS paid_payments_cnt,
+  COALESCE(pps.pending_payments_cnt, 0) AS pending_payments_cnt,
+  COALESCE(pps.failed_payments_cnt, 0) AS failed_payments_cnt,
+  COALESCE(pps.refunded_payments_cnt, 0) AS refunded_payments_cnt,
+  COALESCE(pps.paid_amount_total, 0)::numeric(12,2) AS paid_amount_total,
+  ROUND(COALESCE(pps.paid_amount_total, 0) / NULLIF(COALESCE(ps.purchases_cnt, 0), 0), 2) AS avg_paid_per_purchase,
+  COALESCE(uo.linked_appointment_usages, 0) AS linked_appointment_usages,
+  COALESCE(uo.finalized_usages, 0) AS finalized_usages,
+  DENSE_RANK() OVER (
+    ORDER BY COALESCE(ps.purchases_cnt, 0) DESC, COALESCE(pps.paid_amount_total, 0) DESC
+  ) AS package_popularity_rank
+FROM package_base pb
+LEFT JOIN purchase_stats ps ON ps.package_id = pb.package_id
+LEFT JOIN package_payment_stats pps ON pps.package_id = pb.package_id
+LEFT JOIN usage_ops uo ON uo.package_id = pb.package_id
+ORDER BY package_popularity_rank, pb.package_id;
+
+}}}
+
+=== Релациона алгебра
+
+{{{
+PB ← γ_{package_id, name; svc_cnt:=COUNT(service_id), svc_sum:=Σ(discounted_price)}(Package ⟕ PackageService)
+PS ← γ_{package_id; purchases:=COUNT(*), active:=COUNT_{status='ACTIVE'}(*), uses_sold:=Σ(total_uses), uses_used:=Σ(total_uses-remaining_uses)}(UserPackagePurchase)
+PP ← γ_{package_id; paid_cnt:=COUNT_{status='PAID'}(*), paid_sum:=Σ_{status='PAID'}(amount)}(σ_{appointment_id=NULL}(Payment ⨝ UserPackagePurchase))
+UO ← γ_{package_id; linked:=COUNT(*), finalized:=COUNT_{finalized_at≠NULL}(*)}(UserPackagePurchase ⟕ AppointmentPackageUsage)
+Result ← PB ⟕ PS ⟕ PP ⟕ UO
+}}}
+
+Се гледа кои пакети навистина се продаваат и трошат, дали остануваат неискористени, и дали цената на пакет носи очекуван приход.