ck_order_get_list_new.sql
unknown
pgsql
23 days ago
10 kB
5
Indexable
Never
create function ck_order_get_list_new(refcursor, search_text text, filter jsonb) returns refcursor
language plpgsql
as
$$
DECLARE
divisions_length int;
divisions jsonb;
-- trangThai varchar(200);
ngayDienRaStart bigint;
ngayDienRaEnd bigint;
ks_trangThai varchar;
ks_destination varchar;
begin
divisions = filter -> 'divisions';
-- trangThai = (filter ->> 'trangThai')::varchar;
ngayDienRaStart = (filter ->> 'ngayDienRaStart')::bigint;
ngayDienRaEnd = (filter ->> 'ngayDienRaEnd')::bigint;
ks_trangThai = (filter ->> 'ks_trangThai')::varchar;
ks_destination = (filter ->> 'ks_destination')::varchar;
divisions_length = jsonb_array_length(divisions);
open $1 for
-- new
WITH ck_order AS (SELECT *,
ROW_NUMBER() OVER (PARTITION BY contract_number ORDER BY ID DESC) AS "rowNum"
FROM ck_order)
select ck_order.id as "id",
ck_order.name as "name",
ck_order.amount as "amount",
ck_order.division_ma as "divisionMa",
ck_order.ghi_chu as "ghi_chu",
ck_order.ngay_dien_ra as "ngayDienRa",
ck_order.ngay_gui as "ngayGui",
ck_order.ngay_nhan as "ngayNhan",
ck_order.ngay_tao as "ngayTao",
ck_order.contract_number as "contractNumber",
ck_order.trang_thai as "trangThai",
ck_order.destination as "destination",
ck_order.meta_reject_note as "metaRejectNote",
ck_order.reject_note as "rejectNote",
ck_order.internal_accept_at as "internalAcceptAt",
ck_order.crm_code as "soCode",
fw_user.first_name || fw_user.last_name as "internalAcceptBy",
ck_order.is_custom as "isCustom",
ck_order."rowNum" as "rowNum",
fw_division.ten as "divisionTen",
fw_division.image as "divisionImage",
ck_order.can_cancel as "canCancel",
jsonb_strip_nulls(jsonb_build_object(
'id', ck_division_po.id,
'name', ck_division_po.name,
'divisionMa', ck_division_po.division_ma,
'createdAt', ck_division_po.created_at,
'isApproved', ck_division_po.is_internal_accept,
'neededAt', ck_division_po.needed_at,
'status', ck_division_po.status,
'items', po_detail.items
)) as "poItem",
coalesce(ck_division_meta_po.name, '') || coalesce(ck_division_po.name, '') as "title",
CASE
WHEN ck_division_meta_po.needed_at IS NOT NULL THEN ck_division_meta_po.needed_at
ELSE ck_division_po.needed_at END as "requestTime",
coalesce(ck_division_po.note, '') || coalesce(ck_division_meta_po.note, '') as "note",
(select jsonb_agg(jsonb_build_object('id', ck_order_item.id,
'dishId', ck_order_item.dish_id,
'orderId', ck_order_item.order_id,
'divisionMa', ck_order.division_ma,
'dishMasterId', dm_dish.master_id,
'dishGroupName', dm_dish_group.name,
'dishName', dm_dish.name,
'dishUnit', dm_dish.unit,
'dishGroupId', dm_dish.group_id,
'recipe', recipe.recipe,
'quantity', ck_order_item.quantity))
from ck_order_item
LEFT JOIN dm_dish on dm_dish.id = ck_order_item.dish_id
LEFT JOIN dm_dish_group on dm_dish.group_id = dm_dish_group.id
LEFT JOIN (SELECT dm_dish_recipe.dish_id,
jsonb_agg(jsonb_build_object('materialId', dm_dish_recipe.material_id,
'semiDishId', dm_dish_recipe.semi_dish_id,
'materialName', dm_material.name,
'semiDishName', dm_sm.name,
'unit', dm_unit.id,
'unitName', dm_unit.name,
'dataSemi', semi_data.list_material,
'value', dm_dish_recipe.value)) as recipe
from dm_dish_recipe
left join dm_material on dm_material.id = dm_dish_recipe.material_id
left join dm_semi_dish dm_sm on dm_sm.id = dm_dish_recipe.semi_dish_id
left join dm_unit on dm_unit.id = dm_sm.unit::integer
left join (select semi_dish.id as semi_dish_id,
jsonb_agg(jsonb_build_object('materialId', material.id,
'materialMasterId',
material.master_id,
'materialName',
material.name,
'unit', dm_unit.name,
'unitName', dm_unit.name,
'value',
semi_recipe.value)) as list_material
from dm_semi_dish semi_dish
left join
dm_semi_dish_material semi_recipe
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
group by semi_dish.id) semi_data
on semi_data.semi_dish_id = dm_dish_recipe.semi_dish_id
group by dm_dish_recipe.dish_id) recipe on recipe.dish_id = dm_dish.id
where (ck_order_item.order_id = ck_order.id)
GROUP BY ck_order_item.order_id) as items
from ck_order
left join ck_division_po_get_detail('{}') po_detail on po_detail.order_id = ck_order.id
LEFT JOIN fw_division on fw_division.ma = ck_order.division_ma
left join fw_user on fw_user.id = ck_order.internal_accept_by
left join ck_division_po on ck_division_po.order_id = ck_order.id
left join ck_division_meta_po on ck_division_meta_po.order_id = ck_division_meta_po.id
-- left join ck_order_crm on ck_order.crm_code = ck_order_crm.code
where (search_text is null or search_text = '' or ck_order.name ilike search_text or
fw_division.ten ilike search_text)
and (divisions_length = 0 or divisions ? ck_order.division_ma)
and ck_order.is_orderable = 1
-- and (trangThai is null or trangThai = '' or ck_order.trang_thai = trangThai)
and (ngayDienRaStart is null or ck_order.ngay_dien_ra >= ngayDienRaStart)
and (ngayDienRaEnd is null or ck_order.ngay_dien_ra <= ngayDienRaEnd)
and (ks_destination is null or ck_order.destination = ks_destination)
and (ks_trangThai is null or ck_order.trang_thai = ks_trangThai)
ORDER BY ck_order.ngay_tao DESC;
return $1;
end
$$;
alter function ck_order_get_list_new(refcursor, text, jsonb) owner to "inholdings-dev";
Leave a Comment