CREATE OR REPLACE FUNCTION inventory.get_expiry_drugs_by_user(asset_name text DEFAULT NULL::text, supplier_id integer DEFAULT NULL::integer, acquisition_id integer DEFAULT NULL::integer, location_id integer DEFAULT NULL::integer, cabinet_type text DEFAULT NULL::text, start_date date DEFAULT NULL::date, end_date date DEFAULT NULL::date, status_name text DEFAULT NULL::text, user_name text DEFAULT NULL::text, divisions integer[] DEFAULT NULL)
RETURNS TABLE(id integer, rfid_tag text, order_id integer, po_number text, status text, asset_id integer, proprietary_name text, asset_type text, labeler_name text, dosage text, jcode text, display text, ndc_package_code text, usage_consent_required boolean, serial_number character varying, tag_type_id integer, lot_number text, expiry_date date, vendor_name text, supplier_name text, accounting_flag text, acquisition_name text, acquisition_type_id integer, reserved_for_patient_fname text, reserved_for_patient_lname text, reserved_for_chart_number text, is_unreimbursed boolean, replaces_item bigint, replaced_by_item bigint, dw_appointment_id bigint, dw_physician_id bigint, dw_patient_id bigint, location_name text, is_rfid character varying, cabinet_name text, line_cost_each numeric)
LANGUAGE plpgsql
AS $function$
declare
conditionQuery text := ' WHERE item.deleted_at is null and order_line.dw_practice_id in (SELECT practice_id FROM practice_ids) AND item.status = $3 AND item.expiry_date <= $2 AND item.expiry_date >= $1 AND item.dw_practice_id in (SELECT practice_id FROM practice_ids) ';
OuterConditionQuery text := ' )x ';
query text := 'select * from (
WITH practice_ids AS (
select unnest(allowed_practice_list) as practice_id from get_user_acl($4)
),
recent_cab_txn AS (
SELECT
item.dw_practice_id,
ct.rfid_tag,
MAX(event_time) AS event_time,
MAX(ct.id) AS max_id
FROM item
join cabinet_rfid_transaction ct on item.dw_practice_id in (select practice_id from practice_ids) and item.dw_practice_id = ct.dw_practice_id
and item.id = ct.item_id
where ct.deleted_at is null and item.deleted_at is null and item.status = $3
AND item.expiry_date <= $2
AND item.expiry_date >= $1
GROUP BY 1,ct.rfid_tag
),
recent_cab_rfid_txn AS (
SELECT
ct.rfid_tag,
ct.direction,
c.is_rfid,
c.cabinet_name
FROM cabinet_rfid_transaction ct
JOIN recent_cab_txn co ON ct.dw_practice_id in (select practice_id from practice_ids) and ct.rfid_tag = co.rfid_tag
AND ct.event_time = co.event_time
JOIN cabinet c ON co.dw_practice_id = c.dw_practice_id and ct.cabinet_id = c.id
WHERE
c.deleted_at IS NULL
AND (c.is_rfid IS TRUE OR (c.is_rfid IS FALSE AND ct.id = co.max_id))
AND ct.dw_practice_id IN (SELECT practice_id FROM practice_ids)
AND c.dw_practice_id IN (SELECT practice_id FROM practice_ids)
)
SELECT distinct item.id,
item.rfid_tag,
item.order_id,
orders.po_number,
item.status,
asset.id as asset_id,
asset.proprietary_name,
asset.asset_type,
asset.labeler_name,
asset.dosage,
asset.jcode,
asset.display,
asset.ndc_package_code,
usage_consent_required,
item.serial_number::varchar,
item.tag_type_id::int,
item.lot_number,
item.expiry_date,
vendor_name,
supplier_name,
accounting_flag,
acquisition_type.acquisition_name,
item.acquisition_type_id,
INITCAP(pd.fname) as reserved_for_patient_fname,
INITCAP(pd.lname) as reserved_for_patient_lname,
reserved_for_chart_number,
is_unreimbursed,
replaces_item,
replaced_by_item,
item.dw_appointment_id,
item.dw_physician_id,
item.dw_patient_id,
dw_location_dim.location_name,
-- case when c.is_rfid is not null then c.is_rfid
-- else null end as is_rfid,
-- case when ocr.rfid_tag is not null then ''Out of Storage''
-- when ocr.rfid_tag is null and c.cabinet_name is null then ''Out of Storage''
-- when ocr.rfid_tag is null and c.cabinet_name is not null then c.cabinet_name
-- else null end as cabinet_name
case when ocr.rfid_tag is not null then
case when ocr.is_rfid is true then ''true''::varchar else ''false''::varchar end
else ''null''::varchar end as is_rfid,
case when ocr.rfid_tag is not null and ocr.direction = ''IN'' then ocr.cabinet_name
else ''Out of Storage'' end as cabinet_name,
order_line.line_cost_each
FROM item
JOIN asset ON item.dw_practice_id IN (SELECT practice_id FROM practice_ids) and asset.id = item.asset_id
JOIN orders ON item.dw_practice_id = orders.dw_practice_id and orders.id = item.order_id
JOIN order_line ON item.dw_practice_id = order_line.dw_practice_id and orders.id = order_line.order_id
AND item.order_line_id = order_line.id
LEFT JOIN vendor ON vendor.id = item.vendor_id
LEFT JOIN acquisition_type ON acquisition_type.id = item.acquisition_type_id
LEFT JOIN supplier ON supplier.id = item.supplier_id
LEFT JOIN ( select item.dw_practice_id, item.id, p.dw_patient_id, max(p.modified_time) as modified_time
from item
JOIN dw_patient_lookup p on item.dw_practice_id IN (SELECT practice_id FROM practice_ids)
AND p.dw_practice_id = item.dw_practice_id and p.chart_number = item.reserved_for_chart_number
and nullif(trim(item.reserved_for_chart_number),'''') is not null
where item.status = $3 AND item.expiry_date <= $2 AND
item.expiry_date >= $1
group by 1,2,3
) pt on item.id = pt.id
LEFT JOIN dw_patient_lookup pd on pd.dw_practice_id = pt.dw_practice_id and pd.dw_patient_id = pt.dw_patient_id
and coalesce(pd.modified_time, ''1900-01-01 00:00:00'') = coalesce(pt.modified_time, ''1900-01-01 00:00:00'')
LEFT JOIN dw_location_dim ON dw_location_dim.dw_location_id = item.dw_location_id
LEFT JOIN cabinet c on item.dw_practice_id = c.dw_practice_id and item.cabinet_id = c.id and c.deleted_at is null
LEFT JOIN recent_cab_rfid_txn ocr on item.rfid_tag = ocr.rfid_tag';
begin
if location_id is not null then
conditionQuery = conditionQuery || ' AND item.dw_location_id = $5';
END IF;
if asset_name is not null then
conditionQuery = conditionQuery || ' AND asset.display = $6';
END IF;
if supplier_id is not null then
conditionQuery = conditionQuery || ' AND item.supplier_id = $7';
END IF;
if acquisition_id is not null then
conditionQuery = conditionQuery || ' AND item.acquisition_type_id = $8';
END IF;
if divisions is not null and array_length(divisions, 1) > 0 then
conditionQuery = conditionQuery || ' AND item.dw_practice_id = ANY($9)';
END IF;
if lower(trim(cabinet_type)) = 'null' then
OuterConditionQuery = OuterConditionQuery || ' where is_rfid = ''null''';
elsif trim(cabinet_type) != '0' and left(lower(trim(cabinet_type)),1) in ('t','f') then
OuterConditionQuery = OuterConditionQuery || ' where is_rfid = ' || quote_literal(lower(trim(cabinet_type)));
end if;
query = query || conditionQuery || OuterConditionQuery || ' order by expiry_date';
raise notice '%', query;
RETURN QUERY EXECUTE query using start_date, end_date, status_name, user_name, location_id, asset_name, supplier_id, acquisition_id, divisions;
END;
$function$
;