Преглед на Детали за достава

Актери:

• Магационер, Доставувач, Менаџер

Чекори во сценариото:

Чекор 1.

• Актерот навигира на Достави копчето од менито при што се прикажува табела со сите идни и минати достави.

• Овој дел е опфатен во предходното сценарио за додавање на Достава.

Чекор 2.

• На посакуваниот ред од табелата клика Детали

• Тука иницијално се вчитуваат основните податоци за доставата и податоците за цена.

  SELECT 
      s.shipment_id AS ShipmentId,
      p.payment_status AS PaymentStatus,
      CONCAT(e.employee_name, ' ', e.employee_surname) AS EmployeeFullName,
      CONCAT(v.make, ' ', v.model, ' (', v.registration, ')') AS VehicleInfo,
      TO_CHAR(s.shipment_date, 'DD-MM-YYYY') AS ShipmentDate,
      COUNT(DISTINCT sl.customer_id) AS NumberOfCustomers,
      SUM(sl.wine_quantity) AS WineQuantityShipped,
      SUM(sl.wine_base_price * sl.wine_quantity) AS ShipmentBasePrice,
      SUM(sl.wine_agreed_price * sl.wine_quantity) AS ShipmentAgreedPrice,
      COALESCE(ex.shipment_expenses, 0) AS ShipmentExpenses,
      ROUND(
          (SUM(sl.wine_agreed_price * sl.wine_quantity) - 
          SUM(sl.wine_base_price * sl.wine_quantity) - 
          COALESCE(ex.shipment_expenses, 0))::NUMERIC
      , 2) AS ShipmentProfit
  FROM shipment s
  JOIN payment p ON s.payment_id = p.payment_id
  JOIN employee e ON s.employee_id = e.employee_id
  JOIN vehicle v ON s.vehicle_id = v.vehicle_id
  JOIN shipment_load sl ON s.shipment_id = sl.shipment_id
  LEFT JOIN (SELECT shipment_id, SUM(amount) AS shipment_expenses FROM shipment_has_expense_of_expense_type GROUP BY shipment_id) ex ON s.shipment_id = ex.shipment_id
  WHERE s.shipment_id = {int.Parse(shipmentId)}
  GROUP BY 
      s.shipment_id, 
      p.payment_status, 
      e.employee_name, 
      e.employee_surname,
      v.make, 
      v.model, 
      v.registration, 
      s.shipment_date, 
      ex.shipment_expenses;
  

Чекор 3.

• Актерот може да кликне и на копчето Прикажи повеќе детали при што добиваме специфично кое вино било доставено на кој клиент и нивните цени по единица.

select 
	c.customer_name,
	CONCAT(a.city , ', ', a.street, ', ',a.building_number, ', ', a.postcode) AS customer_address,
	w.wine_name, 
	sl.wine_base_price, 
	sl.wine_agreed_price, 
	sl.wine_quantity
from shipment_load sl
join customer c on sl.customer_id = c.customer_id
join address a on c.address_id = a.address_id
join wine w on sl.wine_id = w.wine_id 
where sl.shipment_id = 5