Напредни извештаи од базата (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;