SQLize Online / PHPize Online  /  SQLtest Online

with sites as ( select "id", "codeInternal", "label", "cell", "type", "sapContractCustomerNumber", spt."leftBorder", spt."rightBorder", spt."depth" from "Site" inner join "Site_Parent_Tree" spt on spt."idSite" = "Site"."id" where "id" = all(?::integer[]) and "id" <> all(?::integer[]) union all select "id", "codeInternal", "label", "cell", "type", "sapContractCustomerNumber", spt."leftBorder", spt."rightBorder", spt."depth" from "Site" inner join "Site_Parent_Tree" spt on spt."idSite" = "Site"."id" where "id" = any(?::integer[]) order by "id" ), max_production_time_lag as ( select sites."id" AS "idSiteOwner", coalesce(max("nbDaysBefore"),?) AS "maxDaysBeforeConsumption" from sites left join "ProductionTimeLagRule" on "ProductionTimeLagRule"."idSiteOwner" = sites."id" group by sites."id" ), menu_without_recipes as ( select "Menu"."id" AS "id", "Menu"."idMealType" AS "idMealType", "Menu"."dateMenu" AS "dateMenu", "Menu"."plannedQty" AS "plannedQty", "Menu"."type" AS "type", "Menu"."status" AS "status", "Menu"."idCategoryMenu" AS "idCategoryMenu", "Menu_Element"."id" AS "idMenuElement", "Menu_Element"."idElement" AS "idElementRecipe", "Menu_Element"."idDish" AS "idDish", "Menu_Element"."plannedQty" AS "plannedQtyMenuElement", "Menu_Element"."dateProduction" AS "dateProductionPotential", "Menu_Element"."displayAllergens", "Menu_Element"."displayCertifications", "CategoryMenu"."idSiteOwner" AS "idSiteOwner", "CategoryMenu"."idGuest" AS "idGuest", "CategoryMenu"."idSegment" AS "idSegment", "CategoryMenu"."idTexture" AS "idTexture", "CategoryMenu"."idDiet" AS "idDiet", "CategoryMenu"."displayAllergens" AS "displayAllergensMenu", "CategoryMenu"."displayCertifications" AS "displayCertificationsMenu", "RecipeVersion"."id" AS "idRecipeVersion" from sites inner join max_production_time_lag on max_production_time_lag."idSiteOwner" = sites.id and sites."id" = ?::int inner join "CategoryMenu" on "CategoryMenu"."idSiteOwner" = sites."id" and "CategoryMenu"."isActive" = true inner join "Menu" on "Menu"."idCategoryMenu" = "CategoryMenu"."id" and "Menu"."status" > ? and ?=? inner join "Menu_Element" on ?=? and "Menu"."id" = "Menu_Element"."idMenu" and "Menu_Element"."idElement" <> ? and ("Menu"."dateMenu" between ? and (?::date + max_production_time_lag."maxDaysBeforeConsumption") or ("Menu_Element"."dateProduction"::date between ?::date and ?::date) ) left join "RecipeVersion" on "RecipeVersion"."idTexture" = "CategoryMenu"."idTexture" and "RecipeVersion"."idSegment" = "CategoryMenu"."idSegment" and "RecipeVersion"."idDiet" = "CategoryMenu"."idDiet" left join "ClosedPeriod" on "ClosedPeriod"."idSiteOwner" = "CategoryMenu"."idSiteOwner" and "ClosedPeriod"."dateValid" @> "Menu"."dateMenu" where "ClosedPeriod"."idSiteOwner" is null ), menu_with_recipe as ( select distinct on ("idElementRecipe","idMenuElement") * from ( select "Recipe"."id" AS "idRecipeMenu" , "Recipe"."idProductionWorkshop" AS "idProductionWorkshop", "Recipe"."idRecipeFamily" AS "idRecipeFamily", "Recipe"."idProductionUnit" AS "idProductionUnit", "Recipe"."idSiteOwner" AS "idSiteOwnerRecipe", case when "Recipe"."idRecipeVersions" is not null then "rvIdRecipeVersion" else menu_without_recipes."idRecipeVersion" end AS "idPotentialRecipeVersion", menu_without_recipes.* from menu_without_recipes inner join "Recipe" on "Recipe"."idElement" = menu_without_recipes."idElementRecipe" and "Recipe"."idRecipeVersions" <> ?::int[] and "Recipe"."idRecipeVersions" @> array[menu_without_recipes."idRecipeVersion"]::int[] left join lateral unnest("Recipe"."idRecipeVersions") "rvIdRecipeVersion" on true union all select "Recipe"."id" AS "idRecipeMenu" , "Recipe"."idProductionWorkshop" AS "idProductionWorkshop", "Recipe"."idRecipeFamily" AS "idRecipeFamily", "Recipe"."idProductionUnit" AS "idProductionUnit", "Recipe"."idSiteOwner" AS "idSiteOwnerRecipe", case when "Recipe"."idRecipeVersions" is not null then "rvIdRecipeVersion" else menu_without_recipes."idRecipeVersion" end AS "idPotentialRecipeVersion", menu_without_recipes.* from menu_without_recipes inner join "Recipe" on "Recipe"."idElement" = menu_without_recipes."idElementRecipe" and ?::int[] @> "Recipe"."idRecipeVersions" left join lateral unnest("Recipe"."idRecipeVersions") "rvIdRecipeVersion" on true where menu_without_recipes."idTexture" = ? and menu_without_recipes."idDiet" = ? union all select "Recipe"."id" AS "idRecipeMenu" , "Recipe"."idProductionWorkshop" AS "idProductionWorkshop", "Recipe"."idRecipeFamily" AS "idRecipeFamily", "Recipe"."idProductionUnit" AS "idProductionUnit", "Recipe"."idSiteOwner" AS "idSiteOwnerRecipe", case when "Recipe"."idRecipeVersions" is not null then "rvIdRecipeVersion" else menu_without_recipes."idRecipeVersion" end AS "idPotentialRecipeVersion", menu_without_recipes.* from menu_without_recipes left join "Recipe" on "Recipe"."idElement" = menu_without_recipes."idElementRecipe" and "Recipe"."isPrimitive" = true left join lateral unnest("Recipe"."idRecipeVersions") "rvIdRecipeVersion" on true ) sub order by "idElementRecipe","idMenuElement","idPotentialRecipeVersion" desc ), potential_menu_with_production_time_lag as ( select menu_with_recipe.*, menu_with_recipe."dateMenu" AS "dateConsumption", case when menu_with_recipe."dateProductionPotential" = ? then coalesce(menu_with_recipe."dateMenu" - "ProductionTimeLagRule"."nbDaysBefore", menu_with_recipe."dateMenu") else menu_with_recipe."dateProductionPotential" end AS "dateProduction", coalesce("ProductionTimeLagRule"."nbDaysBefore",?) AS "nbProductionDaysBeforeConsumption", menu_with_recipe."idProductionWorkshop" AS "idProductionWorkshop" from menu_with_recipe left join "ProductionTimeLagRule" on "ProductionTimeLagRule"."idSiteOwner" = menu_with_recipe."idSiteOwner" and "ProductionTimeLagRule"."idProductionWorkshop" = menu_with_recipe."idProductionWorkshop" and "ProductionTimeLagRule"."idMealType" = menu_with_recipe."idMealType" and "ProductionTimeLagRule"."weekday" = extract(isodow from menu_with_recipe."dateMenu") where ?=? ), menu_with_production_time_lag as ( select * from potential_menu_with_production_time_lag where "dateProduction" between ? and ? ), production_and_consumption_date as ( select distinct on ("idSiteOwner") menu_with_production_time_lag."idSiteOwner" AS "idSiteOwner", ?::date AS "fromProductionDate", ?::date AS "toProductionDate", min(menu_with_production_time_lag."dateConsumption") over (partition by "idSiteOwner") AS "fromConsumptionDate", max(menu_with_production_time_lag."dateConsumption") over (partition by "idSiteOwner") AS "toConsumptionDate", menu_with_production_time_lag."displayAllergens", menu_with_production_time_lag."displayCertifications" from menu_with_production_time_lag ), site_with_parent as ( select ?::int AS "idSiteRoot", parents."idSite" AS "idSite", parents."depth" from "Site_Parent_Tree" inner join "Site_Parent_Tree" parents on "Site_Parent_Tree"."idSite" = ? and "Site_Parent_Tree"."idTree" = ? and parents."idTree" = ? and "Site_Parent_Tree"."leftBorder" >= parents."leftBorder" and "Site_Parent_Tree"."leftBorder" <= parents."rightBorder" ), smartselection_rule as ( select "id" AS "idSmartSelectionRule", "preferenceIds", "preferenceColumns", "applyToIds", "applyToColumns", "dateValid" AS "dateValidRule", "priority", site_with_parent."depth" AS "siteDepth" from production_and_consumption_date,"SmartSelectionRule" inner join site_with_parent on "SmartSelectionRule"."idSiteOwner" = site_with_parent."idSite" and (("status" = ? and site_with_parent."idSite" = ?) or ("status" = ? and site_with_parent."idSite" <> ?) or ("status" = ?) ) where "SmartSelectionRule"."dateValid" && daterange(coalesce(production_and_consumption_date."fromConsumptionDate",?), coalesce(production_and_consumption_date."toConsumptionDate",?), ?) union all select ? * (row_number() over ()) AS "idSmartSelectionRule", case when "idProduct" is null then ?::int[] else array["idProduct"] end AS "preferenceIds", coalesce(array_fill( ?, array[case when "idProduct" is null then ? else ? end]),?::int[]) AS "preferenceColumns", coalesce( case when "idCategoryMenu" = ? then array[]::int[] else array["idCategoryMenu" ]::int[] end || case when "idRecipe" = ? then array[]::int[] else array["idRecipe" ]::int[] end || case when "idGeneric" = ? then array[]::int[] else array["idGeneric" ]::int[] end ,?::int[]) AS "applyToIds", coalesce( case when "idCategoryMenu" = ? then array[]::int[] else array[?]::int[] end || case when "idRecipe" = ? then array[]::int[] else array[?]::int[] end || case when "idGeneric" = ? then array[]::int[] else array[?]::int[] end ,?::int[]) AS "applyToColumns", "dateValid" AS "dateValidRule", ?::int AS "priority", ?::int AS "siteDepth" from "SitePreference",production_and_consumption_date where "SitePreference"."idSiteOwner" = ? and "SitePreference"."dateValid" && daterange(coalesce(production_and_consumption_date."fromConsumptionDate",?), coalesce(production_and_consumption_date."toConsumptionDate",?), ?) and "SitePreference"."priority" = ? and "SitePreference"."idProduct" is not null ), product_without_origin as ( select "idProduct" ,"idSupplierProduct", "idGenerics" , "dateValid" , case when coalesce("Site"."isMSC", false) is false then array_replace( array_replace( array(select distinct unnest("SupplierProductFlat"."idCertifications")), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?) ), (select "id"::int2 from "Certification" where "code" = ? and "extId" <> ?), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?) ) else "idCertifications" end AS "idCertifications", "idCertifications" AS "idCertificationsVector", "idRange" , "idStorageType" , "idProdFamilies", "idMeatRace", "ratioCookedOverRaw", "priceBillingUnit", "nbProductionPerOrders" , "nbBillingPerOrder", "nbStoragePerOrder" , "idStorageUnit" , "packagingLevel" , "weightPerPiece" , "reference" , "SupplierProductFlat"."codeInternal" , "preOrderDelay" , "isDecimalAllowed" , "allowedSuppliers" , "idAllergens" , "isOrderable", ?::int AS "idSite", "priceBillingUnit" / "ratioCookedOverRaw" AS "priceBillingUnitCooked", array["allowedSuppliers"[?], "idProduct", ?::integer, ?::integer, ?::integer, "idRange","packagingLevel"]::int[] "productVector", production_and_consumption_date."displayAllergens", production_and_consumption_date."displayCertifications" from production_and_consumption_date,"SupplierProductAllowedFlat" inner join "Site" on "SupplierProductAllowedFlat"."allowedSites" @> array["Site"."id"]::int[] and "Site"."id" = ? inner join "SupplierProductFlat" on "SupplierProductAllowedFlat"."allowedSites" @> array[?]::int[] and "SupplierProductAllowedFlat"."isOrderable" = true and "SupplierProductAllowedFlat"."idSupplierProductFlat" = "SupplierProductFlat"."id" where "SupplierProductAllowedFlat"."dateValid" && daterange(coalesce(production_and_consumption_date."fromProductionDate",?), coalesce(production_and_consumption_date."toProductionDate",?), ?) ), product_allergens as ( select pwo."idProduct", pwo."idSupplierProduct", array_agg(ae."idAllergen") AS "idAllergens" from product_without_origin pwo inner join "Allergen_Element" ae on ae."idElement" = pwo."idProduct" group by pwo."idProduct", pwo."idSupplierProduct" ), product as ( select distinct pwo."idProduct" , pwo."idSupplierProduct", pwo."idGenerics" , pwo."dateValid" , pwo."idCertifications" , pwo."idRange" , pwo."idStorageType" , pwo."idProdFamilies" , pwo."ratioCookedOverRaw", pwo."priceBillingUnit" , pwo."nbProductionPerOrders" , pwo."nbBillingPerOrder", pwo."nbStoragePerOrder" , pwo."idStorageUnit" , pwo."packagingLevel" , pwo."weightPerPiece" , pwo."reference" , pwo."codeInternal" , pwo."preOrderDelay" , pwo."isDecimalAllowed" , pwo."allowedSuppliers" , pwo."isOrderable" , pwo."idSite" , pwo."priceBillingUnitCooked", pwo."productVector" , pwo."idCertificationsVector", case when pwo."idAllergens" @> pa."idAllergens" then pwo."idAllergens" else pa."idAllergens" end AS "idAllergens", o."id" AS "idOrigin", o."label" AS "labelOrigin", pf."id" AS "idProdFamilyMenu", pf."extId" AS "extIdProdFamily", pf."label" AS "labelProdFamily", pwo."displayAllergens", pwo."displayCertifications", p."racialType", pwo."idMeatRace", mr."label" AS "labelMeatRace", mr."code" AS "codeMeatRace" from product_without_origin pwo left join product_allergens pa on pa."idProduct" = pwo."idProduct" and pa."idSupplierProduct" = pwo."idSupplierProduct" left join "Product" p on p."idElement" = pwo."idProduct" left join "Origin" o on o."id" = p."idOrigin" left join "ProdFamily" pf on pf."id" = p."idProdFamily" left join "MeatRace" mr on mr."id" = p."idMeatRace" ), stable_production_period as ( select distinct on ("milestone") daterange( lag("milestone", ?, ?) over (order by "milestone"), "milestone", ? ) AS "dateStableProduction" from ( select lower("dateValid") AS "milestone" from product where lower("dateValid") <> ? union all select upper("dateValid") AS "milestone" from product union all select ? AS "milestone" ) sub ), stable_consumption_period as ( select distinct on ("milestone") daterange( lag("milestone", ?, ?) over (order by "milestone"), "milestone", ? ) AS "dateStableConsumption" from ( select lower("dateValidRule") AS "milestone" from smartselection_rule where lower("dateValidRule") <> ? union all select upper("dateValidRule") AS "milestone" from smartselection_rule union all select ? AS "milestone" ) sub ), recipe_only as ( select distinct on ("idRecipeMenu") "idRecipeMenu" from menu_with_production_time_lag ), recipe_basisweight as ( select distinct on ("BasisWeight"."idMealType", "BasisWeight"."idRecipe", "BasisWeight"."idGuest") recipe_only."idRecipeMenu", "BasisWeight".* from "BasisWeight" inner join site_with_parent on site_with_parent."idSite" = "BasisWeight"."idSite" inner join recipe_only on recipe_only."idRecipeMenu" = "BasisWeight"."idRecipe" order by "BasisWeight"."idMealType", "BasisWeight"."idRecipe", "BasisWeight"."idGuest", site_with_parent."depth" desc ), recipe_component_without_on_stock as ( select recipe_only."idRecipeMenu", "RecipeFlat"."qty", "RecipeFlat"."idRecipeParent", "RecipeFlat"."idRecipeMainParent", "RecipeFlat"."qtyPerPerson", "RecipeFlat"."weightPerPiece", "RecipeFlat"."isQtyFixed", "RecipeFlat"."tolerance" AS "pieceTolerance", "RecipeFlat"."idProductionUnit", "RecipeFlat"."isMainElement", "RecipeFlat"."idRecipeFamilies", "RecipeFlat"."idGenericFamilies", "RecipeFlat"."idElement" AS "idGeneric", case when s."isMSC" is false then array_replace( array_replace( array(select distinct unnest("RecipeFlat"."idCertifications")), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?) ), (select "id"::int2 from "Certification" where "code" = ? and "extId" <> ?), (select "id"::int2 from "Certification" where "code" = ? and "extId" = ?) ) else "idCertifications" end AS "idCertificationsGeneric", "RecipeFlat"."idAllergens" AS "idAllergensGeneric" from "Site" s, recipe_only inner join "RecipeFlat" on "RecipeFlat"."idRecipe" = recipe_only."idRecipeMenu" and ?=? and "RecipeFlat"."type" = ? where s."id" = ?::int ), recipe_component_on_stock as ( select distinct on (rc."idGeneric", s.depth) rc."idGeneric", coalesce(gs."isOnStock", false) AS "isOnStock", row_number() over ( partition by rc."idGeneric" order by s."depth" desc ) AS "row_num" from recipe_component_without_on_stock rc inner join "Generic" g on g."idElement" = rc."idGeneric" inner join "Generic_Site" gs on gs."idGeneric" = g."idElement" inner join site_with_parent s on s."idSite" = gs."idSite" and s."idSiteRoot" = ? ), recipe_component as ( select r.*, coalesce(rs."isOnStock", false) AS "isOnStock" from recipe_component_without_on_stock r left join recipe_component_on_stock rs on rs."idGeneric" = r."idGeneric" and rs."row_num" = ? ), menu_compressed as ( select distinct on ("dateStableConsumption","dateStableProduction","idCategoryMenu", "idMealType", "idDish", "idRecipeMenu") "dateStableConsumption", "dateStableProduction", "idSiteOwner", "idCategoryMenu", "idMealType", "idDish", "idRecipeMenu", "idGuest", "idSegment", "idTexture", "idDiet", "displayAllergens", "displayCertifications" from menu_with_production_time_lag inner join stable_production_period on stable_production_period."dateStableProduction" @> menu_with_production_time_lag."dateProduction" inner join stable_consumption_period on stable_consumption_period."dateStableConsumption" @> menu_with_production_time_lag."dateConsumption" ), menu_basisweight as ( select "qtyPerPerson" AS "qtyPerPersonModified", menu_compressed.* from menu_compressed left join recipe_basisweight on menu_compressed."idRecipeMenu" = recipe_basisweight."idRecipe" and menu_compressed."idGuest" = recipe_basisweight."idGuest" and menu_compressed."idMealType" = recipe_basisweight."idMealType" ), menu_component_without_row_num as ( select distinct on ("qtyPerPersonModified","dateStableConsumption", "dateStableProduction","idSiteOwner","idCategoryMenu","idMealType", "idDish","idRecipeMenu","idGuest","idSegment","idTexture","idDiet", "idRecipeParent","idRecipeMainParent","idGeneric","pieceTolerance", "idProductionUnit","isQtyFixed","isMainElement","isOnStock", "qtyPerPersonTarget",qty,"weightPerPieceTarget" ) menu_basisweight.*, recipe_component."idRecipeParent", recipe_component."idRecipeMainParent", recipe_component."idGeneric", recipe_component."idRecipeFamilies", recipe_component."idGenericFamilies", recipe_component."pieceTolerance", recipe_component."idProductionUnit", recipe_component."isQtyFixed", recipe_component."isMainElement", recipe_component."isOnStock", recipe_component."idCertificationsGeneric", recipe_component."idAllergensGeneric", coalesce("qtyPerPersonModified", "qtyPerPerson") AS "qtyPerPersonTarget", case when recipe_component."isQtyFixed" = false then coalesce("qtyPerPersonModified"/nullif(recipe_component."qtyPerPerson",?) * recipe_component."qty", recipe_component."qty") else recipe_component."qty" end AS "qty", case when recipe_component."isQtyFixed" = false then recipe_component."weightPerPiece" else coalesce("qtyPerPersonModified"/nullif(recipe_component."qtyPerPerson",?) * recipe_component."weightPerPiece", recipe_component."weightPerPiece") end AS "weightPerPieceTarget" from menu_basisweight inner join recipe_component on recipe_component."idRecipeMenu" = menu_basisweight."idRecipeMenu" ), menu_component as ( select m.*, row_number() over () AS "rowNumberMenuComponent" from menu_component_without_row_num m ), unnested_smartselection_rules as ( select smartselection_rule.*, "applyToId", "applyToColumn", "applyToIndex" from smartselection_rule, unnest("applyToIds", "applyToColumns") with ordinality x("applyToId", "applyToColumn", "applyToIndex") ), precomputed_smartselection as ( select unnested_smartselection_rules.*, count(*) filter (where "applyToColumn" > ?) over (partition by "idSmartSelectionRule" order by "applyToIndex") AS "andGroupIndex", count(*) filter (where "applyToColumn" > ? and "applyToId" > ?) over (partition by "idSmartSelectionRule" ) AS "nbAndGroup" from unnested_smartselection_rules ), checked_smartselection_rule as ( select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idCategoryMenu" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idDiet" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idDish" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idGeneric" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idGenericFamilies"[?] and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idGenericFamilies"[?] and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idGuest" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idMealType" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idRecipeParent" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idRecipeFamilies"[?] and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idRecipeFamilies"[?] and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idSegment" and menu_component."dateStableConsumption" && "dateValidRule" union all select menu_component.*, precomputed_smartselection.* from precomputed_smartselection inner join menu_component on abs("applyToColumn") = ? and abs("applyToId") = menu_component."idTexture" and menu_component."dateStableConsumption" && "dateValidRule" ), resolved_smartselection_rule_or as ( select distinct on ("idSmartSelectionRule", "andGroupIndex", "rowNumberMenuComponent") * from checked_smartselection_rule ), resolved_smartselection_rule_and as ( select * from ( select resolved_smartselection_rule_or.*, row_number() over (partition by "idSmartSelectionRule", "rowNumberMenuComponent", "dateStableConsumption") AS "distinctFilter", count(*) filter (where "applyToId">?) over (partition by "idSmartSelectionRule", "rowNumberMenuComponent", "dateStableConsumption") AS "nbAndGroupCounted", count(*) filter (where "applyToId" ? and menu_component_selected."dateStableProduction" @> menu_with_production_time_lag."dateProduction" and menu_component_selected."dateStableConsumption" @> menu_with_production_time_lag."dateConsumption" and menu_component_selected."idRecipeMenu" = menu_with_production_time_lag."idRecipeMenu" and menu_component_selected."idCategoryMenu" = menu_with_production_time_lag."idCategoryMenu" and menu_component_selected."idMealType" = menu_with_production_time_lag."idMealType" and menu_component_selected."idDish" = menu_with_production_time_lag."idDish" and menu_with_production_time_lag."plannedQtyMenuElement" > ? and ?=? and ( ?=? or ?=? ) ), preorder as ( select distinct on ("idElement", "idSupplierProduct", "dateProduction","dateConsumption") menu_computed."dateConsumption" AS "dateConsumption", menu_computed."dateProduction" AS "dateProduction", menu_computed."idGeneric" AS "idGeneric", menu_computed."idProduct" AS "idProduct", menu_computed."idElement" AS "idElement", menu_computed."idSupplierProduct" AS "idSupplierProduct", menu_computed."typeElement" AS "typeElement", menu_computed."priceProductionUnit" AS "priceProductionUnit", menu_computed."referenceProduct" AS "referenceProduct", menu_computed."codeInternalProduct" AS "codeInternalProduct", menu_computed."preOrderDelay" AS "preOrderDelay", menu_computed."idProductionUnit" AS "idProductionUnit", menu_computed."idProdFamilies"[?] AS "idProdFamily", menu_computed."idStorageType" AS "idStorageType", menu_computed."allowedSuppliers" AS "allowedSuppliers", menu_computed."idRange" AS "idRange", menu_computed."nbStoragePerOrder" AS "nbStoragePerOrder", menu_computed."nbProductionPerOrder" AS "nbProductionPerOrder", menu_computed."isDecimalAllowed" AS "isDecimalAllowed", round(sum("qtyProduct") over (partition by "idElement", "idSupplierProduct" ),?) AS "qtyTotalProductPerSupplierProduct", round(sum("totalPrice") over (partition by "idElement", "idSupplierProduct" ),?) AS "totalPricePerSupplierProduct", round(sum("qtyProduct") over (partition by "idElement", "idSupplierProduct", "dateProduction"),?) AS "qtyProduct", round(sum("totalPrice") over (partition by "idElement", "idSupplierProduct", "dateProduction"),?) AS "totalPrice", bool_and(menu_computed."isIdealProduct") over (partition by "idElement", "idSupplierProduct", "dateProduction") AS "isIdealProduct", bool_or(menu_computed."isForcedProduct") over (partition by "idElement", "idSupplierProduct", "dateProduction") AS "isForcedProduct" from menu_computed ), site_with_parentx as ( select ?::int AS "idSiteRoot", parents."idSite" AS "idSite", parents."depth" from "Site_Parent_Tree" spt inner join "Site_Parent_Tree" parents on parents."idTree" = ? and spt."leftBorder" >= parents."leftBorder" and spt."leftBorder" <= parents."rightBorder" where spt."idSite" = ?::int and spt."idTree" = ? ), site_certification as ( select distinct s."idSiteRoot" AS "idSite", c."id" AS "idCertification", c."label" AS "labelCertification", c."code" AS "codeCertification", cs."isEgalim", s."depth" from site_with_parent s inner join "Certification_Site" cs on cs."idSite" = s."idSite" inner join "Certification" c on c."id" = cs."idCertification" union select distinct ?::int AS "idSite", c."id" AS "idCertification", c."label" AS "labelCertification", c."code" AS "codeCertification", false AS "isEgalim", ? AS "depth" from "site_with_parent" s, "Certification" c where s."idSite" not in ( select "idSite" from "Certification_Site" cs where cs."idSite" = s."idSite" and cs."idCertification" = c."id" union select "idSite" from "Certification_Site" cs inner join "Certification" c2 on c2.id = cs."idCertification" and c2."code" = c."code" ) order by "idSite", "idCertification", "labelCertification", "codeCertification", "depth" desc, "isEgalim" ), site_certification_distinct as ( select distinct on ( sc."idSite", sc."idCertification", sc."labelCertification", sc."codeCertification" ) sc."idSite", sc."idCertification", sc."labelCertification", sc."codeCertification", sc."isEgalim" from site_certification sc left join site_certification sc2 on sc."idSite" = sc2."idSite" and sc."depth" < sc2."depth" order by sc."idSite", sc."idCertification", sc."labelCertification", sc."codeCertification", sc."isEgalim" desc ), egalim as ( select distinct ce."idCertification", coalesce(mc."idProduct", mc."idGeneric") AS "idElement", scd."labelCertification" AS "label", scd."isEgalim" from menu_computed mc inner join "Certification_Element" ce on ce."idElement" = coalesce(mc."idProduct", mc."idGeneric") inner join site_certification_distinct scd on scd."idCertification" = ce."idCertification" ) select distinct preorder."idElement" ||?|| coalesce(preorder."idSupplierProduct",?) AS "id", null::varchar AS "dateProduction", null::varchar AS "dateConsumption", preorder."idGeneric", preorder."idProduct", preorder."idElement", coalesce(preorder."idSupplierProduct",?) AS "idSupplierProduct", preorder."typeElement", preorder."priceProductionUnit", preorder."referenceProduct", preorder."codeInternalProduct", preorder."preOrderDelay", preorder."idProductionUnit", preorder."idProdFamily", pf."label" AS "labelProdFamily", subfamily."id" AS "idProdSubFamily", subfamily."label" AS "labelProdSubFamily", preorder."idStorageType", preorder."idRange", preorder."nbStoragePerOrder", null::float AS "nbProductionPerOrder", null::boolean AS "isDecimalAllowed", preorder."qtyTotalProductPerSupplierProduct", preorder."totalPricePerSupplierProduct", null::float AS "qtyProduct", preorder."totalPrice", preorder."isIdealProduct", preorder."isForcedProduct", productionunit."code" AS "codeProductionUnit", productionunit."label" AS "labelProductionUnit", orderunit."id" AS "idOrderUnit", orderunit."label" AS "labelOrderUnit", orderunit."code" AS "codeOrderUnit", generic."label" AS "labelGeneric", coalesce(product."label", generic."label") AS "labelElement", null::int AS "idCertification", null::varchar AS "egalimLabel", null::boolean AS "isEgalim", coalesce("Stock"."qtyStorageUnit", ?) AS "qtyStorageUnitInStock", "Supplier"."id" AS "idSupplier", "Supplier"."label" AS "labelSupplier", "Supplier"."codeInternal" AS "codeInternalSupplier", null::float AS "totalPriceByProductionDay" , case when "Sheets"."pathFT" <> ? then "Sheets"."pathFT" || "Sheets"."label" else null end AS "sheetsTechnical" , case when "Sheets"."pathFP" <> ? then "Sheets"."pathFP" || "Sheets"."label" else null end AS "sheetsProducer" from preorder inner join "Element" generic on generic."id" = preorder."idGeneric" inner join "Unit" productionunit on productionunit."id" = preorder."idProductionUnit" left join "Supplier" on "Supplier"."id" = preorder."allowedSuppliers"[?] left join "Element" product on product."id" = preorder."idProduct" left join "Stock" on "Stock"."idSupplierProduct" = preorder."idSupplierProduct" and "Stock"."idSiteOwner" in (select "id" from sites) left join "SupplierProduct" on preorder."idSupplierProduct" = "SupplierProduct"."id" left join "Unit" orderunit on "SupplierProduct"."idOrderUnit" = orderunit."id" left join "ProdFamily" pf on pf."id" = preorder."idProdFamily" left join "Product" p on p."idElement" = preorder."idProduct" left join "ProdFamily" subfamily on subfamily."id" = p."idProdSubFamily" left join "Sheets_Elements" on preorder."idElement" = "Sheets_Elements"."idElement" left join "Sheets" on "Sheets_Elements"."idSheet" = "Sheets"."id" and "Sheets"."isAvailable" = true where ( ?=? or ?=? ) union select distinct preorder."idElement" ||?|| coalesce(preorder."idSupplierProduct",?) AS "id", replace(cast(preorder."dateProduction" as varchar),?,?)::varchar AS "dateProduction", replace(cast(preorder."dateConsumption" as varchar),?,?)::varchar AS "dateConsumption", preorder."idGeneric", preorder."idProduct", preorder."idElement", coalesce(preorder."idSupplierProduct",?) AS "idSupplierProduct", preorder."typeElement", preorder."priceProductionUnit", preorder."referenceProduct", preorder."codeInternalProduct", preorder."preOrderDelay", preorder."idProductionUnit", preorder."idProdFamily", pf."label" AS "labelProdFamily", subfamily."id" AS "idProdSubFamily", subfamily."label" AS "labelProdSubFamily", preorder."idStorageType", preorder."idRange", preorder."nbStoragePerOrder", preorder."nbProductionPerOrder", preorder."isDecimalAllowed", preorder."qtyTotalProductPerSupplierProduct", preorder."totalPricePerSupplierProduct", preorder."qtyProduct", preorder."totalPrice", preorder."isIdealProduct", preorder."isForcedProduct", productionunit."code" AS "codeProductionUnit", productionunit."label" AS "labelProductionUnit", orderunit."id" AS "idOrderUnit", orderunit."label" AS "labelOrderUnit", orderunit."code" AS "codeOrderUnit", generic."label" AS "labelGeneric", coalesce(product."label", generic."label") AS "labelElement", null::int AS "idCertification", null::varchar AS "egalimLabel", null::boolean AS "isEgalim", coalesce("Stock"."qtyStorageUnit", ?) AS "qtyStorageUnitInStock", "Supplier"."id" AS "idSupplier", "Supplier"."label" AS "labelSupplier", "Supplier"."codeInternal" AS "codeInternalSupplier", preorder."priceProductionUnit" * preorder."qtyProduct" AS "totalPriceByProductionDay" , case when "Sheets"."pathFT" <> ? then "Sheets"."pathFT" || "Sheets"."label" else null end AS "sheetsTechnical" , case when "Sheets"."pathFP" <> ? then "Sheets"."pathFP" || "Sheets"."label" else null end AS "sheetsProducer" from preorder inner join "Element" generic on generic."id" = preorder."idGeneric" inner join "Unit" productionunit on productionunit."id" = preorder."idProductionUnit" left join "Supplier" on "Supplier"."id" = preorder."allowedSuppliers"[?] left join "Element" product on product."id" = preorder."idProduct" left join "Stock" on "Stock"."idSupplierProduct" = preorder."idSupplierProduct" and "Stock"."idSiteOwner" in (select "id" from sites) left join "SupplierProduct" on preorder."idSupplierProduct" = "SupplierProduct"."id" left join "Unit" orderunit on "SupplierProduct"."idOrderUnit" = orderunit."id" left join "ProdFamily" pf on pf."id" = preorder."idProdFamily" left join "Product" p on p."idElement" = preorder."idProduct" left join "ProdFamily" subfamily on subfamily."id" = p."idProdSubFamily" left join "Sheets_Elements" on preorder."idElement" = "Sheets_Elements"."idElement" left join "Sheets" on "Sheets_Elements"."idSheet" = "Sheets"."id" and "Sheets"."isAvailable" = true where ( ?=? or ?=? ) union select distinct preorder."idElement" ||?|| coalesce(preorder."idSupplierProduct",?) AS "id", null::varchar AS "dateProduction", null::varchar AS "dateConsumption", preorder."idGeneric", preorder."idProduct", preorder."idElement", coalesce(preorder."idSupplierProduct",?) AS "idSupplierProduct", preorder."typeElement", preorder."priceProductionUnit", preorder."referenceProduct", preorder."codeInternalProduct", preorder."preOrderDelay", preorder."idProductionUnit", preorder."idProdFamily", pf."label" AS "labelProdFamily", subfamily."id" AS "idProdSubFamily", subfamily."label" AS "labelProdSubFamily", preorder."idStorageType", preorder."idRange", preorder."nbStoragePerOrder", null::float AS "nbProductionPerOrder", null::boolean AS "isDecimalAllowed", preorder."qtyTotalProductPerSupplierProduct", preorder."totalPricePerSupplierProduct", null::float AS "qtyProduct", preorder."totalPrice", preorder."isIdealProduct", preorder."isForcedProduct", productionunit."code" AS "codeProductionUnit", productionunit."label" AS "labelProductionUnit", orderunit."id" AS "idOrderUnit", orderunit."label" AS "labelOrderUnit", orderunit."code" AS "codeOrderUnit", generic."label" AS "labelGeneric", coalesce(product."label", generic."label") AS "labelElement", egalim."idCertification" AS "idCertification", egalim."label" AS "egalimLabel", egalim."isEgalim" AS "isEgalim", coalesce("Stock"."qtyStorageUnit", ?) AS "qtyStorageUnitInStock", "Supplier"."id" AS "idSupplier", "Supplier"."label" AS "labelSupplier", "Supplier"."codeInternal" AS "codeInternalSupplier", preorder."priceProductionUnit" * preorder."qtyProduct" AS "totalPriceByProductionDay" , case when "Sheets"."pathFT" <> ? then "Sheets"."pathFT" || "Sheets"."label" else null end AS "sheetsTechnical" , case when "Sheets"."pathFP" <> ? then "Sheets"."pathFP" || "Sheets"."label" else null end AS "sheetsProducer" from preorder inner join "Element" generic on generic."id" = preorder."idGeneric" inner join "Unit" productionunit on productionunit."id" = preorder."idProductionUnit" left join egalim on egalim."idElement" = coalesce(preorder."idProduct", preorder."idGeneric") left join "Supplier" on "Supplier"."id" = preorder."allowedSuppliers"[?] left join "Element" product on product."id" = preorder."idProduct" left join "Stock" on "Stock"."idSupplierProduct" = preorder."idSupplierProduct" and "Stock"."idSiteOwner" in (select "id" from sites) left join "SupplierProduct" on preorder."idSupplierProduct" = "SupplierProduct"."id" left join "Unit" orderunit on "SupplierProduct"."idOrderUnit" = orderunit."id" left join "ProdFamily" pf on pf."id" = preorder."idProdFamily" left join "Product" p on p."idElement" = preorder."idProduct" left join "ProdFamily" subfamily on subfamily."id" = p."idProdSubFamily" left join "Sheets_Elements" on preorder."idElement" = "Sheets_Elements"."idElement" left join "Sheets" on "Sheets_Elements"."idSheet" = "Sheets"."id" and "Sheets"."isAvailable" = true where ( ?=? or ?=? ) order by "typeElement" , "isIdealProduct" , "id", "dateProduction", "dateConsumption", "idCertification";