CI : Etat du Stock - Filters

[mark-prins]

Request from the Feb 2020 dashboard review.

On the Etat du Stock board, add filters, which should be in the following order

• Region
• District
• Depot
• Programme
• Article

Filter are based on the selection of those earlier in the hierarchy:

• Region filters districts, which filters stores
• Programme filters Article

Programme filter should not have a default value. Programme should also be using the department as its source, which will remove the '-cs' -'district' suffixes and reduce the number of items in the list.

[mark-prins]

Variables

region

select distinct region.description
from name_category1_level2 region
join name_category1 district on region.id = district.parent_id
join "name" n on district.id = n.category1_id
join store s on n.id = s.name_id
where store_mode in ('store', 'dispensary')

district

select distinct cat.description
from name_category1 cat 
join "name" n on cat.id = n.category1_id
join store s on n.id = s.name_id
where store_mode in ('store', 'dispensary')
  and parent_id in (select id from name_category1_level2 where description in ($region));

store

select s.name from store s 
join "name" n on s.name_id = n.id 
join name_category1 cat on n.category1_id = cat.id 
where store_mode in ('store', 'dispensary') and cat.description in ($district)

program

select lm.description 
from list_master lm
 join list_master_name_join lmnj on lm.id = lmnj.list_master_id 
 join "name" n on lmnj.name_id  = n.id
 join store s on n.id = s.name_id 
where lm.isProgram=true
  and n."type" = 'store'
  and s."name" in ($store);

item

select item_name from item  
where item.id in (
  select lml.item_id 
  from list_master lm 
    join list_master_line lml on lm.id = lml.item_master_id 
    join item_department d on lm.description like (concat(d.department, '%'))
  where d.department  IN ($programme)
);