Changes between Version 14 and Version 15 of RelationalDesign


Ignore:
Timestamp:
09/13/26 23:40:32 (13 days ago)
Author:
223235
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v14 v15  
    6565== Views
    6666
    67 === View 1
    68 
    69 _Мени со јадења_
    70 
    71 [source,sql]
    72 ----
     67== View 1: Мени со пијалоци ==
     68
     69=== SQL Дефиниција ===
     70
     71{{{
     72#!sql
     73CREATE OR REPLACE VIEW kbnteam.v_menu_drink AS
     74SELECT
     75    d.drink_id,
     76    d.drink_name,
     77    d.drink_milliliters,
     78    d.drink_price,
     79    r.rest_id,
     80    r.rest_name
     81FROM kbnteam.drink d
     82JOIN kbnteam.restaurant r ON r.rest_id = d.rest_id;
     83}}}
     84
     85== View 2: Мени со јадења ==
     86
     87=== SQL Дефиниција ===
     88
     89{{{
     90#!sql
    7391CREATE OR REPLACE VIEW kbnteam.v_menu_meal AS
    7492WITH meal_ingredients AS (
    7593    SELECT
    76         x.meal_id,
    77         string_agg(x.ingr_name, ', ' ORDER BY x.ingr_name) AS ingredients
    78     FROM (
    79         SELECT DISTINCT
    80             mi.meal_id,
    81             i.ingr_name
    82         FROM kbnteam.meal_ingredient mi
    83         JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id
    84     ) x
    85     GROUP BY x.meal_id
     94        mi.meal_id,
     95        string_agg(DISTINCT i.ingr_name, ', ' ORDER BY i.ingr_name) AS ingredients
     96    FROM kbnteam.meal_ingredient mi
     97    JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id
     98    GROUP BY mi.meal_id
    8699),
    87100meal_allergens AS (
    88101    SELECT
    89         x.meal_id,
    90         string_agg(x.alergen_name, ', ' ORDER BY x.alergen_name) AS allergens
    91     FROM (
    92         SELECT DISTINCT
    93             mi.meal_id,
    94             a.alergen_name
    95         FROM kbnteam.meal_ingredient mi
    96         JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id
    97         JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id
    98     ) x
    99     GROUP BY x.meal_id
     102        mi.meal_id,
     103        string_agg(DISTINCT a.alergen_name, ', ' ORDER BY a.alergen_name) AS allergens
     104    FROM kbnteam.meal_ingredient mi
     105    JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id
     106    JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id
     107    GROUP BY mi.meal_id
    100108)
    101109SELECT
    … …  
    116124LEFT JOIN meal_ingredients mi ON mi.meal_id = m.meal_id
    117125LEFT JOIN meal_allergens ma ON ma.meal_id = m.meal_id;
    118 ----
    119 
    120 === View 2
    121 
    122 _Целосен преглед на нарачките_
    123 
    124 [source,sql]
    125 ----
     126}}}
     127
     128== View 3: Целосен преглед на нарачките ==
     129
     130=== SQL Дефиниција ===
     131
     132{{{
     133#!sql
    126134CREATE OR REPLACE VIEW kbnteam.v_orders_full AS
    127135WITH order_meals AS (
    128136    SELECT
    129         x.order_id,
    130         string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
    131     FROM (
    132         SELECT DISTINCT
    133             om.order_id,
    134             m.meal_name
    135         FROM kbnteam.order_meal om
    136         JOIN kbnteam.meal m ON m.meal_id = om.meal_id
    137     ) x
    138     GROUP BY x.order_id
     137        om.order_id,
     138        string_agg(DISTINCT m.meal_name, ', ' ORDER BY m.meal_name) AS meals
     139    FROM kbnteam.order_meal om
     140    JOIN kbnteam.meal m ON m.meal_id = om.meal_id
     141    GROUP BY om.order_id
    139142),
    140143order_drinks AS (
    141144    SELECT
    142         x.order_id,
    143         string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
    144     FROM (
    145         SELECT DISTINCT
    146             od.order_id,
    147             d.drink_name
    148         FROM kbnteam.order_drink od
    149         JOIN kbnteam.drink d ON d.drink_id = od.drink_id
    150     ) x
    151     GROUP BY x.order_id
     145        od.order_id,
     146        string_agg(DISTINCT d.drink_name, ', ' ORDER BY d.drink_name) AS drinks
     147    FROM kbnteam.order_drink od
     148    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
     149    GROUP BY od.order_id
    152150)
    153151SELECT
    … …  
    167165    d.delivery_date,
    168166    ds.d_status_name,
    169     drv.user_id AS driver_user_id,
     167    d.driver_user_id,
    170168    du.user_first_name AS driver_first_name,
    171169    du.user_last_name AS driver_last_name,
    … …  
    181179LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id
    182180LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
    183 LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
    184 LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
     181LEFT JOIN kbnteam.api_user du ON du.user_id = d.driver_user_id
    185182LEFT JOIN order_meals om ON om.order_id = o.order_id
    186183LEFT JOIN order_drinks od ON od.order_id = o.order_id;
    187 ----
    188 
    189 === View 3
    190 
    191 _Преглед на рецензиите_
    192 
    193 [source,sql]
    194 ----
     184}}}
     185
     186== View 4: Преглед на рецензиите ==
     187
     188=== SQL Дефиниција ===
     189
     190{{{
     191#!sql
    195192CREATE OR REPLACE VIEW kbnteam.v_reviews_full AS
    196193SELECT
    … …  
    217214JOIN kbnteam.order_review orv ON orv.review_id = r.review_id
    218215JOIN kbnteam.customer_order o ON o.order_id = orv.order_id
    219 JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
    220 JOIN kbnteam.api_user au ON au.user_id = cu.user_id
     216JOIN kbnteam.api_user au ON au.user_id = o.customer_user_id
    221217JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    222218JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
    … …  
    247243JOIN kbnteam.delivery_review drvrev ON drvrev.review_id = r.review_id
    248244JOIN kbnteam.delivery d ON d.delivery_id = drvrev.delivery_id
     245LEFT JOIN kbnteam.api_user du ON du.user_id = d.driver_user_id
     246LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
     247LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id;
     248}}}
     249
     250== View 5: Преглед на доставите ==
     251
     252=== SQL Дефиниција ===
     253
     254{{{
     255#!sql
     256CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS
     257SELECT
     258    d.delivery_id,
     259    d.delivery_date,
     260    d.delivery_notes,
     261    ds.d_status_name,
     262    drv.user_id AS driver_user_id,
     263    du.user_first_name AS driver_first_name,
     264    du.user_last_name AS driver_last_name,
     265    du.user_phone_no AS driver_phone,
     266    r.rest_id,
     267    r.rest_name,
     268    co.comp_order_id,
     269    cmp.company_id,
     270    cmp.company_name,
     271    o.order_id,
     272    o.order_datetime,
     273    o.order_total,
     274    o.customer_user_id,
     275    o.customer_first_name,
     276    o.customer_last_name,
     277    o.customer_email,
     278    COALESCE(o.meals, '') AS meals,
     279    COALESCE(o.drinks, '') AS drinks
     280FROM kbnteam.delivery d
     281JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
    249282LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
    250283LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
     284LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id
    251285LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
    252 LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id;
    253 ----
    254 
    255 === View 4
    256 
    257 _Мени со пијалоци_
    258 
    259 [source,sql]
    260 ----
    261 CREATE OR REPLACE VIEW kbnteam.v_menu_drink AS
    262 SELECT
    263     d.drink_id,
    264     d.drink_name,
    265     d.drink_milliliters,
    266     d.drink_price,
    267     r.rest_id,
    268     r.rest_name
    269 FROM kbnteam.drink d
    270 JOIN kbnteam.restaurant r ON r.rest_id = d.rest_id;
    271 ----
    272 
    273 === View 5
    274 
    275 _Преглед на фактурирањето на компаниите_
    276 
    277 [source,sql]
    278 ----
    279 CREATE OR REPLACE VIEW kbnteam.v_company_billing_overview AS
    280 WITH company_contracts AS (
    281     SELECT
    282         ct.company_id,
    283         COUNT(*) AS contract_count
    284     FROM kbnteam.contract ct
    285     GROUP BY ct.company_id
    286 ),
    287 invoice_totals AS (
    288     SELECT
    289         i.invoice_id,
    290         i.comp_order_id,
    291         co.company_id,
    292         co.delivery_id,
    293         COUNT(o.order_id) AS customer_order_count,
    294         COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS invoice_amount
    295     FROM kbnteam.invoice i
    296     JOIN kbnteam.company_order co ON co.comp_order_id = i.comp_order_id
    297     LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
    298     GROUP BY i.invoice_id, i.comp_order_id, co.company_id, co.delivery_id
    299 )
    300 SELECT
    301     it.invoice_id,
    302     it.comp_order_id,
    303     cmp.company_id,
    304     cmp.company_name,
    305     cmp.company_address,
    306     it.delivery_id,
    307     it.customer_order_count,
    308     it.invoice_amount,
    309     COALESCE(cc.contract_count, 0) AS contract_count
    310 FROM invoice_totals it
    311 JOIN kbnteam.company cmp ON cmp.company_id = it.company_id
    312 LEFT JOIN company_contracts cc ON cc.company_id = cmp.company_id;
    313 ----
    314 
    315 === View 6
    316 
    317 _Приходи според договорите_
    318 
    319 [source,sql]
    320 ----
     286LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
     287LEFT JOIN kbnteam.v_orders_full o ON o.comp_order_id = co.comp_order_id;
     288}}}
     289
     290== View 6: Приходи според договорите ==
     291
     292=== SQL Дефиниција ===
     293
     294{{{
     295#!sql
    321296CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
    322297WITH order_restaurants AS (
    323     SELECT DISTINCT
     298    SELECT
    324299        om.order_id,
    325300        m.rest_id
    … …  
    329304    UNION
    330305
    331     SELECT DISTINCT
     306    SELECT
    332307        od.order_id,
    333308        d.rest_id
    … …  
    340315        orr.rest_id,
    341316        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
    342         COUNT(DISTINCT o.order_id) AS customer_order_count,
    343         COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_revenue
     317        COUNT(*) AS customer_order_count,
     318        SUM(o.order_total)::numeric(14,2) AS total_revenue
    344319    FROM kbnteam.customer_order o
    345320    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    … …  
    366341    ON cm.company_id = ct.company_id
    367342   AND cm.rest_id = ct.rest_id;
    368 ----
    369 
    370 === View 7
    371 
    372 _Преглед на лојалноста на клиентите_
    373 
    374 [source,sql]
    375 ----
     343}}}
     344
     345== View 7: Лојалност на клиентите ==
     346
     347=== SQL Дефиниција ===
     348
     349{{{
     350#!sql
    376351CREATE OR REPLACE VIEW kbnteam.v_customer_loyalty_full_v2 AS
    377352WITH order_stats AS (
    … …  
    379354        o.customer_user_id,
    380355        COUNT(*) AS order_count,
    381         COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_spent,
     356        SUM(o.order_total)::numeric(14,2) AS total_spent,
    382357        MAX(o.order_datetime) AS last_order_at
    383358    FROM kbnteam.customer_order o
    … …  
    406381    os.last_order_at
    407382FROM kbnteam.customer_loyalty cl
    408 JOIN kbnteam.customer cu
    409     ON cu.user_id = cl.user_id
    410 JOIN kbnteam.company cmp
    411     ON cmp.company_id = cu.company_id
    412 JOIN kbnteam.api_user au
    413     ON au.user_id = cl.user_id
    414 JOIN kbnteam.customer_loyalty_status cls
    415     ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id
    416 JOIN kbnteam.loyalty_tier lt
    417     ON lt.tier_id = cl.tier_id
    418 LEFT JOIN order_stats os
    419     ON os.customer_user_id = cl.user_id;
    420 ----
    421 
    422 === View 8
    423 
    424 _Преглед на доставите на доставувачите_
    425 
    426 [source,sql]
    427 ----
    428 CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS
    429 WITH order_meals AS (
    430     SELECT
    431         x.order_id,
    432         string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
    433     FROM (
    434         SELECT DISTINCT
    435             om.order_id,
    436             m.meal_name
    437         FROM kbnteam.order_meal om
    438         JOIN kbnteam.meal m ON m.meal_id = om.meal_id
    439     ) x
    440     GROUP BY x.order_id
    441 ),
    442 order_drinks AS (
    443     SELECT
    444         x.order_id,
    445         string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
    446     FROM (
    447         SELECT DISTINCT
    448             od.order_id,
    449             d.drink_name
    450         FROM kbnteam.order_drink od
    451         JOIN kbnteam.drink d ON d.drink_id = od.drink_id
    452     ) x
    453     GROUP BY x.order_id
    454 )
    455 SELECT
    456     d.delivery_id,
    457     d.delivery_date,
    458     d.delivery_notes,
    459     ds.d_status_name,
    460     drv.user_id AS driver_user_id,
    461     du.user_first_name AS driver_first_name,
    462     du.user_last_name AS driver_last_name,
    463     du.user_phone_no AS driver_phone,
    464     r.rest_id,
    465     r.rest_name,
    466     co.comp_order_id,
    467     cmp.company_id,
    468     cmp.company_name,
    469     o.order_id,
    470     o.order_datetime,
    471     o.order_total,
    472     cu.user_id AS customer_user_id,
    473     au.user_first_name AS customer_first_name,
    474     au.user_last_name AS customer_last_name,
    475     au.user_email AS customer_email,
    476     COALESCE(om.meals, '') AS meals,
    477     COALESCE(od.drinks, '') AS drinks
    478 FROM kbnteam.delivery d
    479 JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
    480 LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
    481 LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
    482 LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id
    483 LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
    484 LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
    485 LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
    486 LEFT JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
    487 LEFT JOIN kbnteam.api_user au ON au.user_id = cu.user_id
    488 LEFT JOIN order_meals om ON om.order_id = o.order_id
    489 LEFT JOIN order_drinks od ON od.order_id = o.order_id;
    490 ----
    491 
    492 === View 9
    493 
    494 _Термини за ручек на доставувачите_
    495 
    496 [source,sql]
    497 ----
     383JOIN kbnteam.customer cu ON cu.user_id = cl.user_id
     384JOIN kbnteam.company cmp ON cmp.company_id = cu.company_id
     385JOIN kbnteam.api_user au ON au.user_id = cl.user_id
     386JOIN kbnteam.customer_loyalty_status cls ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id
     387JOIN kbnteam.loyalty_tier lt ON lt.tier_id = cl.tier_id
     388LEFT JOIN order_stats os ON os.customer_user_id = cl.user_id;
     389}}}
     390
     391== View 8: Термини за ручек на доставувачите ==
     392
     393=== SQL Дефиниција ===
     394
     395{{{
     396#!sql
    498397CREATE OR REPLACE VIEW kbnteam.v_driver_lunch_timers AS
    499398SELECT
    … …  
    519418JOIN kbnteam.driver drv ON drv.rest_id = r.rest_id
    520419JOIN kbnteam.api_user au ON au.user_id = drv.user_id;
    521 ----
    522 
     420}}}
     421
     422== View 9: Фактурирање на компаниите ==
     423
     424=== SQL Дефиниција ===
     425
     426{{{
     427#!sql
     428CREATE OR REPLACE VIEW kbnteam.v_company_billing_overview AS
     429WITH company_contracts AS (
     430    SELECT company_id, COUNT(*) AS contract_count
     431    FROM kbnteam.contract
     432    GROUP BY company_id
     433)
     434SELECT
     435    i.invoice_id,
     436    i.comp_order_id,
     437    cmp.company_id,
     438    cmp.company_name,
     439    cmp.company_address,
     440    co.delivery_id,
     441    COUNT(o.order_id) AS customer_order_count,
     442    COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS invoice_amount,
     443    COALESCE(cc.contract_count, 0) AS contract_count
     444FROM kbnteam.invoice i
     445JOIN kbnteam.company_order co ON co.comp_order_id = i.comp_order_id
     446JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
     447LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
     448LEFT JOIN company_contracts cc ON cc.company_id = cmp.company_id
     449GROUP BY i.invoice_id, i.comp_order_id, cmp.company_id,
     450         cmp.company_name, cmp.company_address, co.delivery_id, cc.contract_count;
     451}}}