Напредни извештаи од базата (SQL и складирани процедури)

Извештај за месечен приход и ДДВ наплата (последни 6 месеци)

Прикажува вкупен приход, трошоци за делови, труд и ДДВ наплата по месец

SELECT 
    TO_CHAR(s.DateOfService, 'YYYY-MM') AS Month,
    SUM(s.TotalPrice) AS TotalRevenue,
    SUM(si.PricePerUnit * sa.UnitsUsed) AS PartsCost,
    SUM(s.LaborCost) AS LaborCost,
    SUM(si.PricePerUnit * sa.UnitsUsed * si.Tax / 100) AS TaxCollected
FROM Services s
JOIN ServiceAssignments sa ON s.Id = sa.ServiceId
JOIN ServiceItems si ON sa.ServiceItemId = si.Id
WHERE s.DateOfService >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY TO_CHAR(s.DateOfService, 'YYYY-MM')
ORDER BY Month DESC;

Извештај за продуктивност на механичари

Прикажува број на сервиси, вкупно користени делови и вкупна вредност на делови по механичар

SELECT 
    m.EMBG,
    m.FirstName || ' ' || m.LastName AS Mechanic,
    COUNT(DISTINCT sa.ServiceId) AS TotalServices,
    SUM(sa.UnitsUsed) AS TotalUnitsUsed,
    SUM(si.PricePerUnit * sa.UnitsUsed) AS TotalPartsValue
FROM Mechanics m
JOIN ServiceAssignments sa ON m.EMBG = sa.MechanicEMBG
JOIN ServiceItems si ON sa.ServiceItemId = si.Id
GROUP BY m.EMBG, Mechanic
ORDER BY TotalPartsValue DESC;

Извештај за лојални клиенти и нивни трошоци

Листа на клиенти со повеќе од 3 сервиси, вкупен потрошено и детали за нивните мотоцикли

SELECT 
    c.Id AS ClientId,
    c.FirstName || ' ' || c.LastName AS Client,
    COUNT(DISTINCT s.Id) AS TotalServices,
    SUM(s.TotalPrice) AS TotalSpent,
    STRING_AGG(m.Model || ' (' || m.Registration || ')', ', ') AS Motorcycles
FROM Clients c
JOIN Motorcycles m ON c.Id = m.ClientId
JOIN Services s ON m.Id = s.MotorcycleId
GROUP BY c.Id, Client
HAVING COUNT(DISTINCT s.Id) > 3
ORDER BY TotalSpent DESC;

Извештај за употреба на делови во сервиси (топ 10 делови)

Прикажува најчесто користени делови и просечна количина по сервис

SELECT 
    si.Id AS PartId,
    si.Description,
    COUNT(sa.ServiceId) AS TimesUsed,
    SUM(sa.UnitsUsed) AS TotalUnits,
    ROUND(AVG(sa.UnitsUsed), 2) AS AvgPerService
FROM ServiceItems si
JOIN ServiceAssignments sa ON si.Id = sa.ServiceItemId
GROUP BY si.Id, si.Description
ORDER BY TotalUnits DESC
LIMIT 10;

Извештај за фискални обврски по месец

Вкупен ДДВ и износ на фискални сметки по месец

SELECT 
    TO_CHAR(fb.IssueDateTime, 'YYYY-MM') AS Month,
    COUNT(fb.Id) AS FiscalBillsCount,
    SUM(fb.DDV) AS TotalTax,
    SUM(s.TotalPrice) AS TotalInvoiced
FROM FiscalBills fb
JOIN ServiceInvoices si ON fb.ServiceInvoiceId = si.Id
JOIN Services s ON si.ServiceId = s.Id
GROUP BY TO_CHAR(fb.IssueDateTime, 'YYYY-MM')
ORDER BY Month DESC;

Извештај за резервни делови со ниско залишно ниво

Делови со помалку од 10 единици во магацин и просечна месечна потрошувачка

WITH MonthlyUsage AS (
    SELECT 
        si.Id AS PartId,
        AVG(sa.UnitsUsed) AS AvgMonthlyUsage
    FROM ServiceItems si
    JOIN ServiceAssignments sa ON si.Id = sa.ServiceItemId
    JOIN Services s ON sa.ServiceId = s.Id
    WHERE s.DateOfService >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY si.Id
)
SELECT 
    si.Id,
    si.Description,
    COALESCE(mu.AvgMonthlyUsage, 0) AS AvgMonthlyUsage,
    CASE 
        WHEN COALESCE(mu.AvgMonthlyUsage, 0) > 0 THEN 'Order ' || CEIL(mu.AvgMonthlyUsage * 2) || ' units'
        ELSE 'No usage data'
    END AS Recommendation
FROM ServiceItems si
LEFT JOIN MonthlyUsage mu ON si.Id = mu.PartId
WHERE si.Id NOT IN (
    SELECT DISTINCT ServiceItemId 
    FROM ServiceAssignments 
    JOIN Services ON ServiceAssignments.ServiceId = Services.Id 
    WHERE Services.DateOfService >= CURRENT_DATE - INTERVAL '30 days'
)
ORDER BY AvgMonthlyUsage DESC;