Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

05/17/25 13:05:24 (5 months ago)

Author:

213231

Comment:

--

AdvancedReports

@@ -1 +1 @@
 
 == **Напредни извештаи од базата (SQL и складирани процедури)**
+
+== 1.Вкупен приход по месец од купување и изнајмување
+
+
+
+{{{
+SELECT 
+    DATE_FORMAT(p.ProcurementDate, '%Y-%m') AS Mesec,
+    SUM(COALESCE(t.TotalPrice, t.MonthlyPay * t.Duration)) AS VkupenPrihod
+FROM 
+    Procurement p
+JOIN 
+    T_Type t ON p.TransactionID = t.TransactionID
+GROUP BY 
+    Mesec
+ORDER BY 
+    Mesec DESC;
+}}}
+
+== 2.Најактивни клиенти според број на трансакции
+
+
+
+
+{{{
+SELECT 
+    c.CustomerName, c.CustomerSurName,
+    COUNT(p.TransactionID) AS VkupnoTransakcii
+FROM 
+    Customer c
+JOIN 
+    Procurement p ON c.CustomerID = p.CustomerID
+GROUP BY 
+    c.CustomerID
+ORDER BY 
+    VkupnoTransakcii DESC;
+}}}
+
+
+== 3.Најчесто сервисирани производи (камиони/приколки)
+
+
+
+
+{{{
+SELECT 
+    pr.Model,
+    COUNT(m.MainID) AS BrojNaServisi
+FROM 
+    Maintenance m
+JOIN 
+    Product pr ON m.ProductID = pr.ProductID
+GROUP BY 
+    pr.ProductID
+ORDER BY 
+    BrojNaServisi DESC;
+}}}
+
+
+== 4.Приход по тип на трансакција
+
+
+
+
+{{{
+SELECT 
+    t.Type,
+    SUM(CASE 
+            WHEN t.Type = 'Buy' THEN t.TotalPrice
+            WHEN t.Type = 'Rent' THEN t.MonthlyPay * t.Duration
+         END) AS VkupenPrihod
+FROM 
+    T_Type t
+GROUP BY 
+    t.Type;
+}}}
+
+
+== 5.Производи со најдобри оценки од корисници
+
+
+
+
+{{{
+SELECT 
+    pr.Model,
+    AVG(f.Rating) AS ProsecnaOcena,
+    COUNT(f.FeedbackID) AS BrojNaOceni
+FROM 
+    CustomerFeedback f
+JOIN 
+    Product pr ON f.ProductID = pr.ProductID
+GROUP BY 
+    f.ProductID
+ORDER BY 
+    ProsecnaOcena DESC, BrojNaOceni DESC;
+}}}
+
+
+== 6. Највредни производи (цени)
+
+
+
+
+{{{
+SELECT 
+    Model, Price, Status
+FROM 
+    Product
+ORDER BY 
+    Price DESC;
+}}}
+
+
+== 7.Месечен извештај по типови трансакции и вкупен приход
+
+
+
+
+
+{{{
+SELECT 
+    DATE_FORMAT(p.ProcurementDate, '%Y-%m') AS Month_Year,
+    COUNT(p.TransactionID) AS Total_Transactions,
+    COUNT(CASE WHEN t.Type = 'Rent' THEN 1 END) AS Rent_Transactions,
+    COUNT(CASE WHEN t.Type = 'Buy' THEN 1 END) AS Buy_Transactions,
+    SUM(CASE WHEN t.Type = 'Rent' THEN t.MonthlyPay * t.Duration ELSE 0 END) AS Rent_Revenue,
+    SUM(CASE WHEN t.Type = 'Buy' THEN t.TotalPrice ELSE 0 END) AS Buy_Revenue,
+    COUNT(DISTINCT m.MainID) AS Maintenance_Count,
+    ROUND(AVG(f.Rating), 2) AS Avg_Feedback_Rating
+FROM 
+    Procurement p
+LEFT JOIN 
+    T_Type t ON p.TransactionID = t.TransactionID
+LEFT JOIN 
+    Maintenance m ON p.ProductID = m.ProductID 
+                 AND DATE_FORMAT(p.ProcurementDate, '%Y-%m') = DATE_FORMAT(m.MainDate, '%Y-%m')
+LEFT JOIN 
+    CustomerFeedback f ON p.TransactionID = f.TransactionID
+GROUP BY 
+    Month_Year
+ORDER BY 
+    Month_Year DESC;
+}}}