Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

01/22/25 15:33:45 (16 hours ago)

Author:

153093

Comment:

--

AdvancedReports

@@ -5 +5 @@
 
    - Оваа функција вади извештај според месец и година за сите клиенти. Извештајот содржи:
+   
+{{{
+CREATE FUNCTION GetCustomerShipmentsReport(month INT, year INT)
+RETURNS TABLE(
+    CustomerName varchar(50),
+    CustomerTypeName varchar(50),
+    TotalWineQuantity bigint,
+    TotalWineProfit double precision,
+    TotalWineBasePrice double precision,
+    TotalWineAgreedPrice double precision,
+    PercentageDifference double precision
+) 
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+       c.customer_name as CustomerName,
+       ct.customer_type_name as CustomerTypeName,
+       SUM(wine_quantity) AS TotalWineQuantity,
+       SUM(wine_base_price * wine_quantity) AS TotalWineBasePrice,
+       SUM(wine_agreed_price * wine_quantity) AS TotalWineAgreedPrice,
+       SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) as TotalWineProfit,
+       (SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) / SUM(wine_base_price * wine_quantity)) * 100 as PercentageDifference
+FROM shipment_load sl
+join customer c on c.customer_id = sl.customer_id
+join customer_type ct on c.customer_type_id = ct.customer_type_id
+JOIN shipment s ON sl.shipment_id = s.shipment_id
+WHERE EXTRACT(MONTH FROM s.shipment_date) = month AND EXTRACT(YEAR FROM s.shipment_date) = year
+GROUP BY c.customer_name, ct.customer_type_name
+order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
+END;
+$$;
+}}}
+