...
We used the following query to generate a materialized view because it was too heavy of a load for Superset to run on the fly. This view will refresh nightly as requisition data is refreshed.
CREATE MATERIALIZED VIEW requsition_stock_and_consumption AS
SELECT DISTINCT ON (li.requisition_line_item_id) li.requisition_line_item_id, r.id, r.created_date as req_created_date, r.modified_date, r.emergency_status, r.supplying_facility, r.supervisory_node, r.facility_id, r.facility_code, r.facility_name, r.facilty_active_status, r.district_id, r.district_code, r.district_name, r.region_id, r.region_code, r.region_name, r.country_id, r.country_code, r.country_name, r.facility_type_id, r.facility_type_code, r.facility_type_name, r.facility_operator_id, r.facility_operator_code, r.facility_operator_name, r.program_id, r.program_code, r.program_name, r.program_active_status, r.processing_period_id, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_id, r.processing_schedule_code, r.processing_schedule_name,
li.requisition_id as li_req_id, li.orderable_id, li.product_code, li.full_product_name, li.trade_item_id, li.beginning_balance, li.total_consumed_quantity, li.average_consumption, li.adjusted_consumption, li.average_consumption, li.total_losses_and_adjustments, li.stock_on_hand, li.total_stockout_days, li.max_periods_of_stock, li.calculated_order_quantity, li.requested_quantity, li.approved_quantity, li.packs_to_ship, li.price_per_pack, li.total_cost, li.total_received_quantity,
sh.requisition_id as status_req_id, sh.status as req_status, sh.author_id, sh.created_date as status_date,
saf.test_facility_name, saf.test_username, saf.test_program_id,
SUM(li.stock_on_hand) as closing_balance,
SUM(li.average_consumption) as AMC,
SUM(li.total_consumed_quantity) as Consumption,
SUM(li.adjusted_consumption) as adjusted_consumption,
SUM(li.approved_quantity) as order_quantity,
CASE WHEN (SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 1 ELSE 0 END as combined_stockout,
CASE
WHEN SUM(li.max_periods_of_stock) > 6 THEN 'Overstocked'
WHEN SUM(li.max_periods_of_stock) < 3 AND (SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Stocked Out'
WHEN SUM(li.max_periods_of_stock) < 3 AND SUM(li.max_periods_of_stock) > 0 AND NOT(SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Understocked'
WHEN SUM(li.max_periods_of_stock) = 0 AND NOT(SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Unknown'
ELSE 'Adequately stocked' END as stock_status
FROM requisitions r JOIN requisition_line_item li ON r.id::VARCHAR = li_req_id
JOIN requisitions_status_history sh ON r.id::VARCHAR = status_req_id
JOIN sample_allowed_facilities saf ON saf.test_facility_name = r.facility_name AND saf.test_program_id = r.program_id
WHERE saf.test_username = '{{ current_username() }}' AND saf.test_username IS NOT 'admin' AND saf.test_username IS NOT 'administrator'
GROUP BY r.id, r.created_date, r.modified_date, r.emergency_status, r.supplying_facility, r.supervisory_node, r.facility_id, r.facility_code, r.facility_name, r.facilty_active_status, r.district_id, r.district_code, r.district_name, r.region_id, r.region_code, r.region_name, r.country_id, r.country_code, r.country_name, r.facility_type_id, r.facility_type_code, r.facility_type_name, r.facility_operator_id, r.facility_operator_code, r.facility_operator_name, r.program_id, r.program_code, r.program_name, r.program_active_status, r.processing_period_id, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_id, r.processing_schedule_code, r.processing_schedule_name,
li.requisition_line_item_id, li_req_id, li.orderable_id, li.product_code, li.full_product_name, li.trade_item_id, li.beginning_balance, li.total_consumed_quantity, li.average_consumption, li.adjusted_consumption, li.average_consumption, li.total_losses_and_adjustments, li.stock_on_hand, li.total_stockout_days, li.max_periods_of_stock, li.calculated_order_quantity, li.requested_quantity, li.approved_quantity, li.packs_to_ship, li.price_per_pack, li.total_cost, li.total_received_quantity,
status_req_id, req_status, sh.author_id, status_date,
saf.test_facility_name, saf.test_username, saf.test_program_id
ORDER BY li.requisition_line_item_id, r.modified_date DESC NULLS LAST
WITH DATA;
Reporting Rate and Timeliness
...
SELECT DISTINCT ON (li.requisition_line_item_id) li.requisition_line_item_id, r.id AS requisition_id, r.created_date, r.modified_date, r.emergency_status, f.name AS supplying_facility_name, r.supervisory_node, r.facility_name, r.facility_type_name, r.facility_operator_name, r.facilty_active_status, r.district_name, r.region_name, r.country_name, r.program_name, r.program_active_status, r.processing_period_name, li.orderable_id, li.product_code, li.full_product_name, li.trade_item_id, li.total_losses_and_adjustments, sh.status, sh.author_id, sh.created_date AS status_history_created_date,
al.id AS adjustment_lines_id, sar.name AS stock_adjustment_reason, al.quantity,
saf.test_facility_name, saf.test_username, saf.test_program_id
FROM requisitions r JOIN requisition_line_item li ON r.id::VARCHAR = li.requisition_id
LEFT JOIN requisitions_status_history sh ON r.id::VARCHAR = sh.requisition_id
LEFT JOIN requisitions_adjustment_lines al ON li.requisition_line_item_id::VARCHAR = al.requisition_line_item_id
LEFT JOIN facilities f ON r.supplying_facility = f.id::VARCHAR
LEFT JOIN stock_adjustment_reasons sar ON sar.id = al.reasonid
JOIN sample_allowed_facilities saf ON saf.test_facility_name = r.facility_name AND saf.test_program_id = r.program_id
WHERE saf.test_username = '{{ current_username() }}' AND saf.test_username IS NOT 'admin' AND saf.test_username IS NOT 'administrator' AND sh.status IS NOT NULL AND sh.status NOT IN ('SKIPPED', 'INITIATED')
ORDER BY li.requisition_line_item_id, r.modified_date DESC NULLS LAST
;
Charts
The charts can be found at this URL: https://superset.ona.io/superset/dashboard/59/ . Using the following credentials:
...