Changes between Version 3 and Version 4 of UseCaseModel/ShipmentDetails

Timestamp:

12/25/24 22:53:27 (3 weeks ago)

Author:

153093

Comment:

--

UseCaseModel/ShipmentDetails

@@ -11 +11 @@
         * Актерот навигира на **Достави** копчето од менито при што се прикажува табела со сите идни и минати достави. 
 [[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;
+}}}