Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/22/24 15:04:28 (12 months ago)

Author:

175012

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај за сите податоци за клиент, возило, осигурител, почетен датум, краен датум===
+
+{{{#!sql
+
+WITH CustomerPolicyInfo AS (
+    SELECT
+        c.c_id,
+        c.name AS customer_name,
+        c.email,
+        c.type AS customer_type,
+        p.sdate,
+        p.edate,
+        pd.d_embg,
+        pd.name AS policy_holder_name,
+        pd.surname AS policy_holder_surname,
+        pt.title AS package_title,
+        pt.total AS package_total,
+        p.p_id as policy_id
+    FROM
+        project.Customer c
+    INNER JOIN project.Pol_dog pd ON c.c_id = pd.c_id
+    INNER JOIN project.Policy p ON pd.policy = p.p_id
+    INNER JOIN project.Package pt ON p.package = pt.code
+)
+SELECT
+    cpi.c_id,
+    cpi.customer_name,
+    cpi.email,
+    cpi.customer_type,
+    cpi.sdate,
+    cpi.edate,
+    cpi.d_embg,
+    cpi.policy_holder_name,
+    cpi.policy_holder_surname,
+    cpi.package_title,
+    cpi.package_total,
+    cpi.policy_id,
+CASE
+    WHEN po.o_embg IS NOT NULL THEN 'Travel Health'
+    ELSE 'Auto registration'
+END AS policy_type,
+    po.o_embg AS osi_embg,
+    po.name AS osi_name,
+    po.surname AS osi_surname,
+    po.birthdate AS osi_birthdate,
+    po.kontakt AS osi_kontakt,
+    v."type"  AS vehicle_type,
+    v.marka  AS vehicle_marka,
+    v.model  AS vehicle_model,
+    v.license_plate  AS license_plate
+FROM
+    CustomerPolicyInfo cpi
+LEFT JOIN project.Pol_travel pt ON cpi.policy_id = pt.pol_id
+LEFT JOIN project.Pol_osi po ON pt.tr_id = po.policy
+LEFT JOIN project.Pol_auto pa ON cpi.policy_id = pa.pol_id
+LEFT JOIN project.Vehicle v ON pa.a_id = v.policy
+
+
+}}}
+
+=== Извештај за број на закажани полиси за одреден клиент ===
+
+{{{#!sql
+
+SELECT
+    c.c_id,
+    c.name AS customer_name,
+    c.email,
+    COUNT(p.p_id) AS reserved_policies_count
+FROM
+    project.Customer c
+LEFT JOIN project.Pol_dog pd ON c.c_id = pd.c_id
+LEFT JOIN project.Policy p ON pd.policy = p.p_id
+GROUP BY
+    c.c_id, c.name, c.email
+ORDER BY
+    reserved_policies_count DESC;
+}}}
+