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

=== Актери:

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

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

     ==== Чекор 1.
        * Актерот навигира на **Достави** копчето од менито при што се прикажува табела со сите идни и минати достави. 
[[Image(step1.png)]]
        * Овој дел е опфатен во предходното сценарио за додавање на Достава.

     ==== Чекор 2.
        * На посакуваниот ред од табелата клика **Детали**
[[Image(step2.png)]]
        * Тука иницијално се вчитуваат основните податоци за доставата и податоците за цена.
{{{
SELECT 
    s.shipment_id,
    p.payment_status,
    CONCAT(e.employee_name, ' ', e.employee_surname) AS employee_full_name,
    CONCAT(v.make, ' ', v.model, ' (', v.registration, ')') AS vehicle_info,
    s.shipment_date,
    Count(distinct sl.customer_id) as no_of_customers,
    SUM(sl.wine_quantity) as wine_quantity_shipped,
    SUM(sl.wine_base_price * sl.wine_quantity) AS shipment_base_price,
    SUM(sl.wine_agreed_price * sl.wine_quantity) AS shipment_agreed_price,
    COALESCE(ex.shipment_expenses, 0) as shipment_expenses,
    (SUM(sl.wine_agreed_price * sl.wine_quantity) - SUM(sl.wine_base_price * sl.wine_quantity) - COALESCE(ex.shipment_expenses, 0)) as shipment_profit
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
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 = 5
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.
        * Актерот може да кликне и на копчето **Прикажи повеќе детали** при што добиваме специфично кое вино било доставено на кој клиент и нивните цени по единица.
[[Image(step3.png)]]


{{{
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
}}}