Changes between Initial Version and Version 1 of Views

Timestamp:

09/11/25 03:50:57 (7 weeks ago)

Author:

141515

Comment:

--

Views

@@ +1 @@
+== Погледи
+
+Овој поглед е за сите активни договори кои постојат.
+
+{{{
+CREATE OR REPLACE VIEW active_agreements_view AS
+SELECT
+    a.A_Id, 
+    a.Price AS agreement_price, 
+    a.Status AS agreement_status, 
+    a.Datum AS agreement_date, 
+    a.Tax_Nr AS dealership_tax_nr, 
+    a.VIN AS car_vin,
+    a.EMBG AS client_embg,
+    
+    d.D_Name AS dealership_name, 
+    d.Email AS dealership_email, 
+
+    (SELECT string_agg(phone_number, ', ') 
+     FROM dealership_telephones dt 
+     WHERE dt.dealership_tax_nr = d.Tax_Nr) AS dealership_telephones,
+
+    (SELECT string_agg(brand_name, ', ') 
+     FROM dealership_brands db 
+     WHERE db.dealership_tax_nr = d.Tax_Nr) AS dealership_brands,
+
+    d.Addres AS dealership_address, 
+    
+    v.Make AS car_make, 
+    v.Model AS car_model, 
+    v.P_Year AS car_year, 
+    v.Capacity AS car_capacity, 
+    v.Power AS car_power, 
+    v.Color AS car_color, 
+    v.Price AS car_price, 
+
+    c.C_Name AS client_name, 
+    c.Email AS client_email, 
+    c.Telephone AS client_phone, 
+    p.Amount AS payment_amount
+    
+
+FROM
+    agreement a
+JOIN dealership d ON a.Tax_Nr = d.Tax_Nr
+JOIN vehicle v ON a.VIN = v.VIN
+JOIN client c ON a.EMBG = c.EMBG
+LEFT JOIN payment p ON a.A_Id = p.A_Id
+WHERE 
+    a.Status = true;
+
+}}}
+
+
+Овој поглед е за клиенти кои имаат извршено уплата за 2 или повеќе возила, и би бил корисен како маркетин алатка, да се испраќаат новости за нови огласи на тие корисници
+{{{
+CREATE OR REPLACE VIEW clients_with_2_or_more_vehicles AS
+SELECT 
+    c.EMBG AS client_embg,
+    c.C_Name AS client_name,
+    c.Email AS client_email,
+    c.Telephone AS client_phone,
+    COUNT(DISTINCT v.VIN) AS number_of_vehicles,
+    SUM(p.Amount) AS total_paid_amount
+    
+FROM
+    client c
+JOIN agreement a ON c.EMBG = a.EMBG
+JOIN vehicle v ON a.VIN = v.VIN
+LEFT JOIN payment p ON a.A_Id = p.A_Id AND p.Amount > 0
+GROUP BY 
+    c.EMBG, c.C_Name, c.Email, c.Telephone
+HAVING 
+    COUNT(DISTINCT v.VIN) >= 2;
+
+}}}