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

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

• Оваа функција вади извештај според одреден месец и година за сите клиенти. Извештајот содржи:
  • Име на клиент
  • Тип на клиент
  • Вкупна нарачана количина на вино од сите испораки
  • Вкупна основна цена на вино од сите испораки
  • Вкупна договорена цена на вино од сите испораки
  • Вкупен профит од сите испораки
  • Вкупен процент преметан од основната цена и договорената цена од сите испораки

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;
$$;

Извештај за сите вина според месец и година

• Оваа функција вади извештај според одреден месец и година за сите вина. Извештајот содржи:
  • Име на вино
  • Тип на вино
  • Вкупна нарачана количина на вино од сите испораки
  • Вкупна основна цена на вино од сите испораки
  • Вкупна договорена цена на вино од сите испораки
  • Вкупен профит од сите испораки
  • Вкупен процент преметан од основната цена и договорената цена од сите испораки

CREATE FUNCTION GetShippedWinesReport(month INT, year INT)
RETURNS TABLE(
    WineName varchar(50),
    WineTypeName varchar(50),
    WineRegion varchar(50),
    YearProduced date,
    TotalWineBasePrice double precision,
    TotalWineAgreedPrice double precision,
    TotalWineQuantity bigint,
    TotalWineProfit double precision,
    PercentageDifference double precision
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        w.wine_name as WineName,
        wt.wine_type_name AS WineTypeName, 
        wt.wine_type_region AS WineRegion, 
        w.year_produced AS YearProduced,
        SUM(sl.wine_base_price * sl.wine_quantity) AS TotalWineBasePrice, 
        SUM(sl.wine_agreed_price * sl.wine_quantity) AS TotalWineAgreedPrice,
        SUM(sl.wine_quantity) AS TotalWineQuantity,
        SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) AS TotalWineProfit,
		(SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) / SUM(sl.wine_base_price * sl.wine_quantity)) * 100 as PercentageDifference
    FROM shipment_load sl
    JOIN wine w ON w.wine_id = sl.wine_id
    JOIN wine_type wt ON wt.wine_type_id = w.wine_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
        w.wine_id,
        w.wine_name,
        wt.wine_type_name,
        wt.wine_type_region,
        w.year_produced
	order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
END;
$$;