nutrition_actual_menu_descriptions1.sql

SELECT
  wo_amount.*,
  CASE
    WHEN product_unit = 'mg'    THEN round(((product_amount * portions / 1000000)::NUMERIC), 2)
    WHEN product_unit = 'g'     THEN round(((product_amount * portions / 1000)::NUMERIC), 2)
    WHEN product_unit = 'kg'    THEN round(((product_amount * portions)::NUMERIC), 2)
    WHEN product_unit = 'ml'    THEN round(((product_amount * portions / 1000)::NUMERIC), 2)
    WHEN product_unit = 'l'     THEN round(((product_amount * portions)::NUMERIC), 2)
    WHEN product_unit = 'mm'    THEN round(((product_amount * portions / 1000000)::NUMERIC), 2)
    WHEN product_unit = 'sm'    THEN round(((product_amount * portions / 1000)::NUMERIC), 2)
    WHEN product_unit = 'm'     THEN round(((product_amount * portions)::NUMERIC), 2)
    WHEN product_unit = 'piece' THEN round(((product_amount * portions)::NUMERIC), 2)
  END AS amount
FROM (
  SELECT
    actual_meal.actual_menu_id AS actual_menu_id,
    actual_meal.id AS actual_meal_id,
    COALESCE(actual_meals_residents.resident_portions, 0) AS resident_portions,
    COALESCE(actual_meals_residents.residents_ids, '{ }') AS residents_ids,
    COALESCE(actual_meals_employees.employee_portions, 0) AS employee_portions,
    COALESCE(actual_meals_employees.employees_ids, '{ }') AS employees_ids,
    COALESCE(additional_food_entries.sum, 0) AS additional_food_portions,
    actual_meal.extra_portions,
    dish_product.product_type_id AS product_type_id,
    dish_product.amount AS product_amount,
    dish_product.unit AS product_unit,
    (COALESCE(actual_meals_residents.resident_portions, 0)
      + COALESCE(actual_meals_employees.employee_portions, 0)
      + COALESCE(additional_food_entries.sum, 0)
      + actual_meal.extra_portions) AS portions
  FROM nutrition_actual_meals AS actual_meal
  INNER JOIN nutrition_dishes AS dish ON actual_meal.dish_id = dish.id
  INNER JOIN nutrition_dish_products AS dish_product ON dish.id = dish_product.dish_id
  LEFT JOIN nutrition_additional_food_entries AS additional_food_entry ON additional_food_entry.actual_meal_id = actual_meal.id
  LEFT JOIN (
    SELECT
      actual_meal.id AS actual_meal_id,
      SUM(amount) AS sum
    FROM nutrition_additional_food_entries AS additional_food_entry
    INNER JOIN nutrition_actual_meals AS actual_meal ON actual_meal.id = additional_food_entry.actual_meal_id
    GROUP BY actual_meal.id
  ) AS additional_food_entries ON actual_meal.id = additional_food_entries.actual_meal_id
  LEFT JOIN (
    SELECT
      actual_meal.id AS actual_meal_id,
      (COUNT(DISTINCT resident.id) * actual_meal.resident_percentage / 100) AS resident_portions
      ARRAY_AGG(DISTINCT resident.id) AS residents_ids
    FROM residents AS resident
    INNER JOIN nutrition_dining_types AS dining_type ON resident.dining_type_id = dining_type.id
    LEFT JOIN resident_stay_periods AS stay_period ON stay_period.resident_id = resident.id
    LEFT JOIN contract_periods AS contract_period ON contract_period.resident_id = resident.id
    INNER JOIN nutrition_actual_menus AS actual_menu
      ON (stay_period.checkin_date <= actual_menu.date
        AND (stay_period.checkout_date > actual_menu.date OR stay_period.checkout_date IS NULL)
        AND (stay_period.pansion_id = actual_menu.pansion_id))
      OR (contract_period.first_enter_date <= actual_menu.date
        AND (contract_period.last_leave_date >= actual_menu.date OR contract_period.last_leave_date IS NULL)
        AND (contract_period.pansion_id = actual_menu.pansion_id))
    INNER JOIN nutrition_actual_meals AS actual_meal ON actual_meal.actual_menu_id = actual_menu.id
    WHERE resident.id NOT IN (
      SELECT
        resident.id
      FROM resident_leaves AS leave
      WHERE leave.resident_id = resident.id
      AND leave.start_date <= actual_menu.date AND (leave.end_date > actual_menu.date OR leave.end_date IS NULL)
    )
    AND actual_meal.dining_type_id = resident.dining_type_id
    AND actual_menu.date > current_date - 1
    GROUP BY actual_meal.id
  ) AS actual_meals_residents ON actual_meal.id = actual_meals_residents.actual_meal_id
  LEFT JOIN(
    SELECT
      actual_meal.id AS actual_meal_id,
      (COUNT(DISTINCT employee.id) * actual_meal.employee_percentage / 100) AS employee_portions
      ARRAY_AGG(DISTINCT employee.id) AS employees_ids
    FROM hrm_employees AS employee
    INNER JOIN hrm_employee_shifts AS shift ON shift.employee_id = employee.id
    INNER JOIN hrm_employee_contracts AS contract ON contract.employee_id = employee.id
    INNER JOIN pansion_workspaces AS workspace ON workspace.id = shift.workspace_id
    INNER JOIN hrm_employee_contract_meal_types AS contract_meal_type ON contract_meal_type.contract_id = contract.id
    INNER JOIN nutrition_meal_types AS meal_type ON meal_type.id = contract_meal_type.meal_type_id
    INNER JOIN nutrition_actual_menus AS actual_menu
      ON (shift.start_time + interval '1h' * EXTRACT(TIMEZONE FROM now()) / 3600.0) <= (actual_menu.date + meal_type.end_time)
      AND (shift.end_time + interval '1h' * EXTRACT(TIMEZONE FROM now()) / 3600.0) >= (actual_menu.date + meal_type.start_time)
    INNER JOIN nutrition_actual_meals AS actual_meal ON actual_meal.actual_menu_id = actual_menu.id
    WHERE shift.eating = TRUE
      AND workspace.pansion_id = actual_menu.pansion_id
      AND contract.pansion_id = actual_menu.pansion_id
      AND contract.dining_type_id = actual_meal.dining_type_id
      AND meal_type.id = actual_meal.meal_type_id
      AND actual_menu.date > current_date - 1
    GROUP BY actual_meal.id
  ) AS actual_meals_employees ON actual_meal.id = actual_meals_employees.actual_meal_id
) AS wo_amount