ck_division_po_get_detail.sql

create function ck_division_po_get_detail(filter jsonb)
    returns TABLE(id integer, order_id integer, division_ma character varying, status character varying, created_at bigint, is_internal_accept character varying, needed_at bigint, items jsonb)
    language plpgsql
as
$$
DECLARE
    division     varchar = (filter ->> 'division')::varchar;
    filter_po_id integer = (filter ->> 'poId')::integer;
begin
    RETURN QUERY
        with po_item as (select item.id,
                                coalesce(item.semi_dish_id, item.material_id)             item_id,
                                case
                                    when item.semi_dish_id is not null then 'semi'
                                    when item.material_id is not null then 'material' end item_type
                         from ck_division_po_item item)

        SELECT po.id,
               po.order_id,
               po.division_ma,
               po.status,
               po.created_at,
               po.is_internal_accept,
               po.needed_at,
               po_item.items

        FROM ck_division_po po
                 left join (select cdi.po_id,
                                   jsonb_agg(jsonb_build_object(
                                           'id', cdi.id,
                                           'semiDishId', cdi.semi_dish_id,
                                           'semiDishMasterId', dm_semi_dish.master_id,
                                           'semiDishName', dm_semi_dish.name,
                                           'semiDishUnit', semi_dish_unit.name,
                                           'semiDishUnitId', dm_semi_dish.unit,
                                           'materialId', cdi.material_id,
                                           'materialMasterId', dm_material.master_id,
                                           'materialName', dm_material.name,
                                           'materialMoq', dm_material.minimum_order_quantity,
                                           'materialUnit', material_unit.name,
                                           'materialUnitId', dm_material.unit,
                                           'moq', dm_material.minimum_order_quantity,
                                           'quantity', cdi.quantity,
                                           'neededAt', cdi.needed_time,
                                           'checkListQuantity', cdi.check_list_quantity,
                                           'checkListAt', cdi.check_list_at,
                                           'recipe', (select jsonb_agg(jsonb_build_object(
                                                   'materialId', material.id,
                                                   'materialMasterId', material.master_id,
                                                   'materialName', material.name,
                                                   'moq', material.minimum_order_quantity,
                                                   'unit', dm_unit.name,
                                                   'quantity', semi_recipe.value))
                                                      from dm_semi_dish_material semi_recipe
                                                               left join dm_semi_dish semi_dish
                                                                         on semi_dish.id = semi_recipe.semi_dish_id
                                                               left join dm_material material
                                                                         on semi_recipe.material_id = material.id
                                                               left join dm_unit on dm_unit.id = material.unit
                                                      where semi_recipe.semi_dish_id = dm_semi_dish.id
                                                      group by semi_dish.id)
                                             )) as items
                            from ck_division_po_item cdi

                                     left join po_item on po_item.id = cdi.id
                                     left join dm_material on dm_material.id = cdi.material_id
                                     left join dm_unit material_unit on material_unit.id = dm_material.unit
                                     left join dm_semi_dish on dm_semi_dish.id = cdi.semi_dish_id
                                     left join dm_unit semi_dish_unit on semi_dish_unit.id::varchar = dm_semi_dish.unit
                            where cdi.quantity != 0
                              and cdi.dish_id is null
                            group by cdi.po_id) po_item on po_item.po_id = po.id
        WHERE (division IS NULL OR po.division_ma = division)
          AND (filter_po_id IS NULL OR po.id = filter_po_id);

end
$$;

alter function ck_division_po_get_detail(jsonb) owner to "inholdings-release";