final_cost

WITH packs AS (

select
packages."_id"
, packages."Date" AS pack_date
, "packages"."DeliveryDate" AS delivery_date
, "SubscriptionSettings.ProductCode" as product_code
, COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id") as "dish_id"
-- , count(packages."_id") over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes_in_packs"
-- , count(COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id")) over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes"
, "SubscriptionSettings.Count" as "packs_count"
, packages_menu."Menu.Items.OriginalDishCode" as "original_dish_id"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then true
       else false
       end as "replaced"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then "packages"."DeliveryReportGuid"
       else null
       end as "delivery_report_id"
from packages
left join packages_menu on (packages_menu."_id" = packages."_id"
                        and packages."IsReplaced" is true
                        )
left join menus ON (packages."Date"= menus."Date" + '3 hour'
                aND packages."SubscriptionSettings.ProductCode" = menus."Program"
                and packages."IsReplaced" is false
                and menus."IsDeleted" is false
                )
where packages."State" = 3
and packages."IsDeleted" is false
and packages."Date" >= '2022-01-01'
-- and packages."_id" = '00039e24-67cb-4dbd-b35c-634997077458'
order by packages."_id"
)

, prices AS (SELECT DISTINCT
  m."Date" + interval '3 hour' as menu_date
, m."Meals.Containers._id" as dish_id
, m."Meals.Containers.Price"/10000 as dish_price
FROM menus m
WHERE m."Date">='01-11-2021'::timestamp

UNION



SELECT DISTINCT

 packages."Date" as menu_date
,"public"."packages_menu"."Menu.Items.DishCode" as dish_id
,  menus."Meals.Containers.Price"/10000 as dish_price

FROM packages_menu
LEFT JOIN "public"."deliveryreports"
ON "public"."packages_menu"."Menu.Items.CookingDeliveryReportId"="public"."deliveryreports"."_id"
LEFT JOIN packages
ON packages_menu."_id"=packages."_id"
INNER JOIN menus ON "PackageGroups.FeedDate"= menus."Date" + '3 hour'
                AND "packages_menu"."Menu.Items.DishCode"=menus."Meals.Containers._id"
WHERE packages."IsDeleted" is false
and packages."Date">='01-01-2022'::timestamp
and packages."IsReplaced" is true
and packages."State"=3
and NOT "public"."packages_menu"."Menu.Items.DishCode" LIKE "public"."packages_menu"."Menu.Items.OriginalDishCode"

UNION

SELECT DISTINCT

 packages."Date" as menu_date
,"public"."packages_menu"."Menu.Items.DishCode" as dish_id
,  retailmenus."Dishes.Price"/10000 as dish_price

FROM packages_menu
LEFT JOIN "public"."deliveryreports"
ON "public"."packages_menu"."Menu.Items.CookingDeliveryReportId"="public"."deliveryreports"."_id"
LEFT JOIN packages
ON packages_menu."_id"=packages."_id"
INNER JOIN retailmenus ON "PackageGroups.FeedDate"= retailmenus."Date" + '3 hour'
                AND "packages_menu"."Menu.Items.DishCode"=retailmenus."Dishes._id"
WHERE packages."IsDeleted" is false
and packages."Date">='01-01-2022'::timestamp
and packages."IsReplaced" is true
and packages."State"=3
and NOT "public"."packages_menu"."Menu.Items.DishCode" LIKE "public"."packages_menu"."Menu.Items.OriginalDishCode")

SELECT DISTINCT packs.*,
       prices_1."dish_price" as original_price,
       prices_2."dish_price" as new_price
FROM packs
LEFT JOIN prices as prices_1
ON packs."pack_date"=prices_1."menu_date"
AND packs."original_dish_id"=prices_1."dish_id"
LEFT JOIN prices as prices_2
ON packs."pack_date"=prices_2."menu_date"
AND packs."dish_id"=prices_2."dish_id"