Changes between Version 13 and Version 14 of RelationalDesign

Timestamp:

09/13/26 22:54:15 (2 weeks ago)

Author:

223235

Comment:

--

RelationalDesign

@@ -63 +63 @@
 Потребно е да се направат три погледи (views) по член, така што тие подоцна реално ќе бидат користени од апликацијата.
 
-==== View 1
-
-// Простор за првиот view
-
-## [source,sql]
-
-## -- SQL код за View 1
-
-==== View 2
-
-// Простор за вториот view
-
-## [source,sql]
-
-## -- SQL код за View 2
-
-==== View 3
-
-// Простор за третиот view
-
-## [source,sql]
-
-## -- SQL код за View 3
-
+== Views
+
+=== View 1
+
+_Мени со јадења_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_menu_meal AS
+WITH meal_ingredients AS (
+    SELECT
+        x.meal_id,
+        string_agg(x.ingr_name, ', ' ORDER BY x.ingr_name) AS ingredients
+    FROM (
+        SELECT DISTINCT
+            mi.meal_id,
+            i.ingr_name
+        FROM kbnteam.meal_ingredient mi
+        JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id
+    ) x
+    GROUP BY x.meal_id
+),
+meal_allergens AS (
+    SELECT
+        x.meal_id,
+        string_agg(x.alergen_name, ', ' ORDER BY x.alergen_name) AS allergens
+    FROM (
+        SELECT DISTINCT
+            mi.meal_id,
+            a.alergen_name
+        FROM kbnteam.meal_ingredient mi
+        JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id
+        JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id
+    ) x
+    GROUP BY x.meal_id
+)
+SELECT
+    m.meal_id,
+    m.meal_name,
+    m.meal_description,
+    m.meal_price,
+    m.meal_weight,
+    c.cat_id,
+    c.cat_name,
+    r.rest_id,
+    r.rest_name,
+    COALESCE(mi.ingredients, '') AS ingredients,
+    COALESCE(ma.allergens, '') AS allergens
+FROM kbnteam.meal m
+JOIN kbnteam.category c ON c.cat_id = m.cat_id
+JOIN kbnteam.restaurant r ON r.rest_id = m.rest_id
+LEFT JOIN meal_ingredients mi ON mi.meal_id = m.meal_id
+LEFT JOIN meal_allergens ma ON ma.meal_id = m.meal_id;
+----
+
+=== View 2
+
+_Целосен преглед на нарачките_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_orders_full AS
+WITH order_meals AS (
+    SELECT
+        x.order_id,
+        string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
+    FROM (
+        SELECT DISTINCT
+            om.order_id,
+            m.meal_name
+        FROM kbnteam.order_meal om
+        JOIN kbnteam.meal m ON m.meal_id = om.meal_id
+    ) x
+    GROUP BY x.order_id
+),
+order_drinks AS (
+    SELECT
+        x.order_id,
+        string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
+    FROM (
+        SELECT DISTINCT
+            od.order_id,
+            d.drink_name
+        FROM kbnteam.order_drink od
+        JOIN kbnteam.drink d ON d.drink_id = od.drink_id
+    ) x
+    GROUP BY x.order_id
+)
+SELECT
+    o.order_id,
+    o.order_datetime,
+    o.order_total,
+    os.o_status_name,
+    cu.user_id AS customer_user_id,
+    cu.company_id AS customer_company_id,
+    au.user_first_name AS customer_first_name,
+    au.user_last_name AS customer_last_name,
+    au.user_email AS customer_email,
+    co.comp_order_id,
+    co.company_id AS order_company_id,
+    cmp.company_name,
+    d.delivery_id,
+    d.delivery_date,
+    ds.d_status_name,
+    drv.user_id AS driver_user_id,
+    du.user_first_name AS driver_first_name,
+    du.user_last_name AS driver_last_name,
+    du.user_phone_no AS driver_phone,
+    COALESCE(om.meals, '') AS meals,
+    COALESCE(od.drinks, '') AS drinks
+FROM kbnteam.customer_order o
+JOIN kbnteam.order_status os ON os.o_status_id = o.o_status_id
+JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
+JOIN kbnteam.api_user au ON au.user_id = cu.user_id
+JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
+JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
+LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id
+LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
+LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
+LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
+LEFT JOIN order_meals om ON om.order_id = o.order_id
+LEFT JOIN order_drinks od ON od.order_id = o.order_id;
+----
+
+=== View 3
+
+_Преглед на рецензиите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_reviews_full AS
+SELECT
+    r.review_id,
+    'order'::text AS review_type,
+    r.review_created_at,
+    r.review_rating,
+    r.review_comment,
+    orv.order_id,
+    NULL::integer AS delivery_id,
+    co.company_id,
+    cmp.company_name,
+    au.user_id AS customer_user_id,
+    au.user_first_name AS customer_first_name,
+    au.user_last_name AS customer_last_name,
+    NULL::integer AS driver_user_id,
+    NULL::varchar(255) AS driver_first_name,
+    NULL::varchar(255) AS driver_last_name,
+    orv.order_review_food_rating,
+    orv.order_review_res_rating,
+    NULL::integer AS del_review_courier_rating,
+    NULL::integer AS del_review_speed_rating
+FROM kbnteam.review r
+JOIN kbnteam.order_review orv ON orv.review_id = r.review_id
+JOIN kbnteam.customer_order o ON o.order_id = orv.order_id
+JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
+JOIN kbnteam.api_user au ON au.user_id = cu.user_id
+JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
+JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
+
+UNION ALL
+
+SELECT
+    r.review_id,
+    'delivery'::text AS review_type,
+    r.review_created_at,
+    r.review_rating,
+    r.review_comment,
+    NULL::integer AS order_id,
+    drvrev.delivery_id,
+    co.company_id,
+    cmp.company_name,
+    NULL::integer AS customer_user_id,
+    NULL::varchar(255) AS customer_first_name,
+    NULL::varchar(255) AS customer_last_name,
+    du.user_id AS driver_user_id,
+    du.user_first_name AS driver_first_name,
+    du.user_last_name AS driver_last_name,
+    NULL::integer AS order_review_food_rating,
+    NULL::integer AS order_review_res_rating,
+    drvrev.del_review_courier_rating,
+    drvrev.del_review_speed_rating
+FROM kbnteam.review r
+JOIN kbnteam.delivery_review drvrev ON drvrev.review_id = r.review_id
+JOIN kbnteam.delivery d ON d.delivery_id = drvrev.delivery_id
+LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
+LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
+LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
+LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id;
+----
+
+=== View 4
+
+_Мени со пијалоци_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_menu_drink AS
+SELECT
+    d.drink_id,
+    d.drink_name,
+    d.drink_milliliters,
+    d.drink_price,
+    r.rest_id,
+    r.rest_name
+FROM kbnteam.drink d
+JOIN kbnteam.restaurant r ON r.rest_id = d.rest_id;
+----
+
+=== View 5
+
+_Преглед на фактурирањето на компаниите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_company_billing_overview AS
+WITH company_contracts AS (
+    SELECT
+        ct.company_id,
+        COUNT(*) AS contract_count
+    FROM kbnteam.contract ct
+    GROUP BY ct.company_id
+),
+invoice_totals AS (
+    SELECT
+        i.invoice_id,
+        i.comp_order_id,
+        co.company_id,
+        co.delivery_id,
+        COUNT(o.order_id) AS customer_order_count,
+        COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS invoice_amount
+    FROM kbnteam.invoice i
+    JOIN kbnteam.company_order co ON co.comp_order_id = i.comp_order_id
+    LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
+    GROUP BY i.invoice_id, i.comp_order_id, co.company_id, co.delivery_id
+)
+SELECT
+    it.invoice_id,
+    it.comp_order_id,
+    cmp.company_id,
+    cmp.company_name,
+    cmp.company_address,
+    it.delivery_id,
+    it.customer_order_count,
+    it.invoice_amount,
+    COALESCE(cc.contract_count, 0) AS contract_count
+FROM invoice_totals it
+JOIN kbnteam.company cmp ON cmp.company_id = it.company_id
+LEFT JOIN company_contracts cc ON cc.company_id = cmp.company_id;
+----
+
+=== View 6
+
+_Приходи според договорите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
+WITH order_restaurants AS (
+    SELECT DISTINCT
+        om.order_id,
+        m.rest_id
+    FROM kbnteam.order_meal om
+    JOIN kbnteam.meal m ON m.meal_id = om.meal_id
+
+    UNION
+
+    SELECT DISTINCT
+        od.order_id,
+        d.rest_id
+    FROM kbnteam.order_drink od
+    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
+),
+contract_metrics AS (
+    SELECT
+        co.company_id,
+        orr.rest_id,
+        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
+        COUNT(DISTINCT o.order_id) AS customer_order_count,
+        COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_revenue
+    FROM kbnteam.customer_order o
+    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
+    JOIN order_restaurants orr ON orr.order_id = o.order_id
+    GROUP BY co.company_id, orr.rest_id
+)
+SELECT
+    ct.contract_id,
+    cmp.company_id,
+    cmp.company_name,
+    r.rest_id,
+    r.rest_name,
+    cs.contract_status_name,
+    ct.contract_start_date,
+    ct.contract_end_date,
+    COALESCE(cm.company_order_count, 0) AS company_order_count,
+    COALESCE(cm.customer_order_count, 0) AS customer_order_count,
+    COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue
+FROM kbnteam.contract ct
+JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
+JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
+JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id
+LEFT JOIN contract_metrics cm
+    ON cm.company_id = ct.company_id
+   AND cm.rest_id = ct.rest_id;
+----
+
+=== View 7
+
+_Преглед на лојалноста на клиентите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_customer_loyalty_full_v2 AS
+WITH order_stats AS (
+    SELECT
+        o.customer_user_id,
+        COUNT(*) AS order_count,
+        COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_spent,
+        MAX(o.order_datetime) AS last_order_at
+    FROM kbnteam.customer_order o
+    GROUP BY o.customer_user_id
+)
+SELECT
+    cl.cus_loyalty_id,
+    cl.user_id AS customer_user_id,
+    cu.company_id,
+    cmp.company_name,
+    au.user_first_name,
+    au.user_last_name,
+    au.user_email,
+    au.user_phone_no,
+    cl.cus_loyalty_curr_points,
+    cl.cus_loyalty_joined_at,
+    cls.cus_loyalty_status_id,
+    cls.cus_loyalty_status_name,
+    lt.tier_id,
+    lt.tier_name,
+    lt.tier_discount_percentage,
+    lt.tier_free_delivery_eligibility,
+    lt.tier_priority_support,
+    COALESCE(os.order_count, 0) AS order_count,
+    COALESCE(os.total_spent, 0)::numeric(14,2) AS total_spent,
+    os.last_order_at
+FROM kbnteam.customer_loyalty cl
+JOIN kbnteam.customer cu
+    ON cu.user_id = cl.user_id
+JOIN kbnteam.company cmp
+    ON cmp.company_id = cu.company_id
+JOIN kbnteam.api_user au
+    ON au.user_id = cl.user_id
+JOIN kbnteam.customer_loyalty_status cls
+    ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id
+JOIN kbnteam.loyalty_tier lt
+    ON lt.tier_id = cl.tier_id
+LEFT JOIN order_stats os
+    ON os.customer_user_id = cl.user_id;
+----
+
+=== View 8
+
+_Преглед на доставите на доставувачите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS
+WITH order_meals AS (
+    SELECT
+        x.order_id,
+        string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
+    FROM (
+        SELECT DISTINCT
+            om.order_id,
+            m.meal_name
+        FROM kbnteam.order_meal om
+        JOIN kbnteam.meal m ON m.meal_id = om.meal_id
+    ) x
+    GROUP BY x.order_id
+),
+order_drinks AS (
+    SELECT
+        x.order_id,
+        string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
+    FROM (
+        SELECT DISTINCT
+            od.order_id,
+            d.drink_name
+        FROM kbnteam.order_drink od
+        JOIN kbnteam.drink d ON d.drink_id = od.drink_id
+    ) x
+    GROUP BY x.order_id
+)
+SELECT
+    d.delivery_id,
+    d.delivery_date,
+    d.delivery_notes,
+    ds.d_status_name,
+    drv.user_id AS driver_user_id,
+    du.user_first_name AS driver_first_name,
+    du.user_last_name AS driver_last_name,
+    du.user_phone_no AS driver_phone,
+    r.rest_id,
+    r.rest_name,
+    co.comp_order_id,
+    cmp.company_id,
+    cmp.company_name,
+    o.order_id,
+    o.order_datetime,
+    o.order_total,
+    cu.user_id AS customer_user_id,
+    au.user_first_name AS customer_first_name,
+    au.user_last_name AS customer_last_name,
+    au.user_email AS customer_email,
+    COALESCE(om.meals, '') AS meals,
+    COALESCE(od.drinks, '') AS drinks
+FROM kbnteam.delivery d
+JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
+LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
+LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
+LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id
+LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
+LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
+LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
+LEFT JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
+LEFT JOIN kbnteam.api_user au ON au.user_id = cu.user_id
+LEFT JOIN order_meals om ON om.order_id = o.order_id
+LEFT JOIN order_drinks od ON od.order_id = o.order_id;
+----
+
+=== View 9
+
+_Термини за ручек на доставувачите_
+
+[source,sql]
+----
+CREATE OR REPLACE VIEW kbnteam.v_driver_lunch_timers AS
+SELECT
+    lt.lunch_time_id,
+    lt.lunch_weekday,
+    lt.lunch_start,
+    lt.lunch_end,
+    lt.lunch_preorder_offset,
+    lt.comp_order_id,
+    ct.contract_id,
+    cmp.company_id,
+    cmp.company_name,
+    r.rest_id,
+    r.rest_name,
+    drv.user_id AS driver_user_id,
+    au.user_first_name AS driver_first_name,
+    au.user_last_name AS driver_last_name,
+    au.user_phone_no AS driver_phone
+FROM kbnteam.lunch_time lt
+JOIN kbnteam.contract ct ON ct.contract_id = lt.contract_id
+JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
+JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
+JOIN kbnteam.driver drv ON drv.rest_id = r.rest_id
+JOIN kbnteam.api_user au ON au.user_id = drv.user_id;
+----
+