SQLize | PHPize | SQLtest

-- FUNCTION: public.udf_n2_updt_qtr_ovc_smry(date, date) -- DROP FUNCTION IF EXISTS public.udf_n2_updt_qtr_ovc_smry(date, date); CREATE OR REPLACE FUNCTION public.udf_n2_updt_qtr_ovc_smry( pd_qb date, pd_qe date) RETURNS integer LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ declare o_rec record; i_cnt integer; d_eo_tq date; d_eo_lq date; d_eo_pq date; d_bo_tq date; d_bo_lq date; d_reg_dt date; i_next_psi_id int; i_tei_id int; i_pi_id int; i_ou_id int; s_reg_sts varchar; s_lq_sts varchar; s_rec_tq varchar; s_rec_lq varchar; s_rec_pq varchar; s_cli_type varchar; s_cli_sts varchar; i_age int; s_age_r varchar; i_srv_cnt int; s_hiv_test varchar; s_hiv_sts varchar; s_hiv_rl varchar; s_hiv_vl varchar; f_valid_for varchar; d_dt date; -- i_apr_out int; i_grc_days int; i_rec_num int; i_dqi_cnt int; i_ovcp_cnt int; i_tr_out int; s_pci_cnt varchar; s_prtnr_cnt varchar; --s_json_value jsonb; s_json_value = msp_mk_json[]; begin if (pd_qe is not null) then raise notice '> OVC Qtrly Update - Started at %', clock_timestamp()::timestamp; delete from tmp_qtr_updt_log; d_bo_tq := pd_qb; d_eo_tq := pd_qe; d_bo_lq := (select (d_bo_tq::date - interval '3 months')); d_eo_lq := (select (d_bo_tq::date - interval '1 day')); d_eo_pq := (select (d_bo_lq::date - interval '1 day')); raise notice '-> Updating Client status...'; insert into trackedentityattributevalue select ta.trackedentityinstanceid, 6914358, now(), now(), 'In Program',null,'smuthusamy' from (select trackedentityinstanceid from trackedentityinstance where trackedentitytypeid = 867 --31629 except select trackedentityinstanceid from trackedentityattributevalue where trackedentityattributeid = 6914358) ta; update trackedentityattributevalue set value = 'Duplicate' where trackedentityattributeid = 6914358 and trackedentityinstanceid in (select trackedentityinstanceid from trackedentityattributevalue where trackedentityattributeid = 6914358 and value <> 'Duplicate' and trackedentityinstanceid in (select trackedentityinstanceid from trackedentityattributevalue where trackedentityattributeid = 870 and value like '%Duplicate%')); update trackedentityinstance set inactive = true where trackedentityinstanceid in (select trackedentityinstanceid from trackedentityattributevalue where value like '%(Duplicate%') and coalesce(inactive,false) <> true; create temp table tmp_dqi_list as ---- select pi.trackedentityinstanceid as tei_id, tedv.dataelementid f_dei, tedv.value f_value ---- from trackedentitydatavalue tedv ---- join programstageinstance psi on tedv.programstageinstanceid = psi.programstageinstanceid ---- join programinstance pi on pi.programinstanceid = psi.programinstanceid ---- where psi.programstageid = any (array[5750275,2390499]) ---- and tedv.dataelementid = 2390430; -- u68Yq2EMokX DQC: Error --5750275,2390499 Data Quality Checks select pi.trackedentityinstanceid as tei_id,'u68Yq2EMokX' as uid, psi.eventdatavalues->'u68Yq2EMokX'->>'value' as f_value from programstageinstance psi inner join programinstance pi on pi.programinstanceid = psi.programinstanceid where psi.programstageid = any (array[5750275,2390499]); -- u68Yq2EMokX DQC: Error -- delete all records perform udf_del_qtr_smry(d_eo_tq); -- take all beneficiaries. i_rec_num := 0; raise notice '-> Processing clients...'; for o_rec in (select tei.trackedentityinstanceid, teav.value, pi.programinstanceid, pi.organisationunitid from trackedentityinstance tei left join trackedentityattributevalue teav on teav.trackedentityinstanceid = tei.trackedentityinstanceid inner join programinstance pi on tei.trackedentityinstanceid = pi.trackedentityinstanceid and pi.programid= 1153 and pi.deleted='false' inner join organisationunit ou on ou.organisationunitid = pi.organisationunitid and (ou.name like '%Mother%' or ou.name like '%SK%' or ou.name like '%Baylor%') where tei.deleted='false' and tei.trackedentitytypeid = 867 and teav.trackedentityattributeid = 6914358) ----6914358 Client Status loop i_rec_num := i_rec_num + 1; i_next_psi_id := (select programstageinstanceid+1 from programstageinstance order by programstageinstanceid desc limit 1); i_tei_id := o_rec.trackedentityinstanceid; i_pi_id := o_rec.programinstanceid; i_ou_id := o_rec.organisationunitid; s_cli_sts := o_rec.value; insert into programstageinstance(programstageinstanceid, created, lastupdated, createdatclient, lastupdatedatclient, programinstanceid, programstageid,attributeoptioncomboid, deleted, storedby, duedate, executiondate, organisationunitid, status, completeddate) values(i_next_psi_id, d_eo_tq, d_eo_tq, d_eo_tq, d_eo_tq, i_pi_id, 1103, 20, '0', 'smuthusamy', d_eo_tq, d_eo_tq, i_ou_id, 'COMPLETED', d_eo_tq); --1103 programstage - End of Quarter i_cnt := (select count(te.value) from trackedentityattributevalue te where te.trackedentityattributeid = 877 and te.trackedentityinstanceid = i_tei_id); ---877 Date of Birth if i_cnt = 0 then i_age := -1; else i_age := (select date_part('year',age(d_eo_tq, te.value::date))::integer from trackedentityattributevalue te where te.trackedentityattributeid = 877 and te.trackedentityinstanceid = i_tei_id); end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 233622, coalesce(i_age,-1), d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || msp_mk_json('hXo10O6eUge',coalesce(i_age,-1),d_today,'false', 'smuthusamy'); -- Age case when i_age < 0 then s_age_r := 'Unknown'; when i_age < 1 then s_age_r := '<1yr'; when i_age < 5 then s_age_r := '1-4yrs'; when i_age < 10 then s_age_r := '5-9yrs'; when i_age < 15 then s_age_r := '10-14yrs'; when i_age < 18 then s_age_r := '15-17yrs'; when i_age < 120 then s_age_r := '18+yrs'; else s_age_r := 'XXX'; end case; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 2366636, s_age_r, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('LJzuAsdIltj',s_age_r,d_today); -- Age Range ------------------------Check if a service was given in a quarter----------------------------------- --// i_srv_cnt := (select count(1) from ipap_stage_data --// where ps_id = 249228 and (exe_dt between d_bo_tq::date and d_eo_tq::date) and tei_id = i_tei_id and del_flag = 'false' --// and left(srv_type,4) in ('Grou','Indi') and dataelementid = 847 and left(value,4) <> '03.3'); -- and dt_ver = 'Yes' and dt_apr = 'Yes'); i_srv_cnt := (select count(1) from programstageinstance psi inner join programinstance pi on psi.programinstanceid = pi.programinstanceid Where programstageid = 249228 and (executiondate between d_bo_tq::date and d_eo_tq::date) and pi.trackedentityinstanceid = i_tei_id and psi.deleted ='false' and psi.eventdatavalues->'XdQ1hOT5N5d'->>'value' is not null and left(psi.eventdatavalues->'IgWAPZV27xB'->>'value',4) in ('Grou','Indi') ---852 "IgWAPZV27xB" "Service Type" and left(psi.eventdatavalues->'XdQ1hOT5N5d'->>'value',4) <> '03.3') ---847 "XdQ1hOT5N5d" "OVC_SP: Service Provided" ---249228 Service Provision (New) --03.3. Monthly school attendance monitoring if i_srv_cnt > 0 then s_rec_tq := 'true'; else s_rec_tq := 'false'; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 843, s_rec_tq, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('t8QcsoYFWof',s_rec_tq,d_today); -- Service Given In Quarter ------------------------------------------------------------------------------------------------------ --// i_srv_cnt := (select count(1) from trackedentitydatavalue --// inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid --// inner join programinstance on programinstance.programinstanceid = programstageinstance.programinstanceid --// where programinstance.trackedentityinstanceid = i_tei_id and value = 'true' --// and trackedentitydatavalue.dataelementid = 843 and programstageinstance.executiondate = d_eo_lq::date); ---834 t8QcsoYFWof Service Given In Quarter i_srv_cnt := (select count(1) from programstageinstance psi inner join programinstance pi on psi.programinstanceid = pi.programinstanceid Where pi.trackedentityinstanceid = i_tei_id and psi.executiondate =d_eo_lq::date and psi.deleted ='false' and psi.eventdatavalues->'t8QcsoYFWof'->>'value' = 'true'); if i_srv_cnt > 0 then s_rec_lq := 'true'; else s_rec_lq := 'false'; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 227337, s_rec_lq, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('x6lcKkmkJco',s_rec_lq,d_today); -- Service Given In Last Quarter --------------------------------------------------------------------------------------------------------------- --// i_srv_cnt := (select count(1) from trackedentitydatavalue --// inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid --// inner join programinstance on programinstance.programinstanceid = programstageinstance.programinstanceid --// where programinstance.trackedentityinstanceid = i_tei_id and value = 'true' --// and trackedentitydatavalue.dataelementid = 843 and programstageinstance.executiondate <= d_eo_pq::date); i_srv_cnt := (select count(1) from programstageinstance psi inner join programinstance pi on psi.programinstanceid = pi.programinstanceid Where pi.trackedentityinstanceid = i_tei_id and psi.executiondate =d_eo_lq::date and psi.deleted ='false' and psi.eventdatavalues->'t8QcsoYFWof'->>'value' = 'true'); if i_srv_cnt > 0 then s_rec_pq := 'true'; else s_rec_pq := 'false'; end if; /* i_apr_out := (select count(1) from trackedentitydatavalue inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid inner join programinstance on programinstance.programinstanceid = programstageinstance.programinstanceid where programinstance.trackedentityinstanceid = i_tei_id and value in ('UCS','NN2S','GED') and trackedentitydatavalue.dataelementid = 747 and programstageinstance.executiondate <= d_eo_lq::date); */ --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 227338, s_rec_pq, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('l1pvQZwcSIk',s_rec_pq,d_today); -- Service Given In Previous Quarter --------------------------------------------------------------------------------------------------------- -----Check Client Registration Date d_reg_dt := (select created from trackedentityinstance where trackedentityinstanceid = i_tei_id); if (d_reg_dt >= d_bo_tq and d_reg_dt <= d_eo_tq) then s_reg_sts:= 'New In Quarter'; elseif (d_reg_dt > d_eo_tq) then s_reg_sts := 'Not Yet Registered'; else s_reg_sts := 'Registered'; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 832, s_reg_sts, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('jjt0lH15t99',s_reg_sts,d_today); -- Registration Status ------------------------------------------------------------------------------- --// s_lq_sts := (select coalesce(value,'none') from trackedentitydatavalue --// inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid --// inner join programinstance on programinstance.programinstanceid = programstageinstance.programinstanceid --// where programinstance.trackedentityinstanceid = i_tei_id --// and trackedentitydatavalue.dataelementid = 747 and programstageinstance.executiondate = d_eo_lq::date order by value limit 1); ----- 747 747 "yLFVNIeUc4Z" "Client Type" s_lq_sts := (select psi.eventdatavalues->'yLFVNIeUc4Z'->>'value' from programstageinstance psi inner join programinstance pi on psi.programinstanceid = pi.programinstanceid Where (psi.trackedentityinstanceid = i_tei_id) and (psi.executiondate = d_eo_lq::date) order by value limit 1 and psi.deleted ='false' and psi.eventdatavalues->'yLFVNIeUc4Z'->>'value' is not null); s_cli_type := ''; if (s_cli_sts = 'In Program') then if (s_lq_sts in ('New','Ongoing','Re-enrolled')) then if (s_rec_tq = 'true' and s_rec_lq = 'true') then s_cli_type := 'Ongoing'; elseif (s_rec_tq = 'false' and s_rec_lq = 'true') then s_cli_type := 'Lost'; end if; else if (s_rec_tq = 'true' and s_rec_lq = 'false' and s_rec_pq = 'false') then s_cli_type := 'New'; select count(1) into i_ovcp_cnt from trackedentityinstance tei inner join "_view_beneficiary_list" tb on tb.tei_id = tei.uid and tei.trackedentityinstanceid = i_tei_id and tb.hh_code_new like 'CL%'; if (i_ovcp_cnt=1) then s_cli_type := 'OVC_PREV'; end if; /* if (msp_check_transfers(i_tei_id)='Yes') then s_cli_type := 'Transfer In'; end if; */ elseif (s_rec_tq = 'true' and s_rec_pq = 'true') then s_cli_type := 'Re-enrolled'; else s_cli_type := 'none'; end if; end if; elseif (s_lq_sts in ('Graduated','Exited WOG','none')) then s_cli_type := 'none'; elseif (s_cli_sts = 'Graduated') then s_cli_type := 'Graduated'; elseif (s_cli_sts = 'Exited W/O Graduation') then s_cli_type := 'Exited WOG'; /* select count(1) into i_tr_out from ipap_stage_data ts where ts.ps_id = 1110 and ts.value like 'o_et_trn%' and ts.tei_id = i_tei_id and (ts.exe_dt between d_bo_lq::date and d_eo_lq::date) and ts.del_flag = false; if (i_tr_out=1) then s_cli_type := 'Transfer Out'; end if; */ elseif (s_cli_sts = 'Exited Responsibly' and s_lq_sts in ('New','Ongoing','Re-enrolled')) then s_cli_type := 'Exited Responsibly'; else if (s_rec_tq = 'true') then s_cli_type := 'GED'; else s_cli_type := 'none'; end if; end if; i_grc_days := (d_bo_tq - d_eo_tq); if (now()::date<=d_eo_tq) then i_grc_days := 0; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 747, s_cli_type, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('yLFVNIeUc4Z',s_cli_type,d_today); -- Client Typen --------------------------------------------------------------------------------------------------------------- --15762538 EOQ: PCI: Count select count(1) into i_dqi_cnt from msp_cli_origins where co_pci_cnt = 'Y' and co_tei = i_tei_id; if (i_dqi_cnt=1) then s_pci_cnt := 'Yes'; else s_pci_cnt := 'No'; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 15762538, s_pci_cnt, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('xFiZxEcdHgB',s_pci_cnt,d_today); -- EOQ: OVC: PCI Count ---------------------------------------------------------------------------------------------------------------- select count(1) into i_dqi_cnt from msp_cli_origins where co_prtnr_cnt = 'Y' and co_tei = i_tei_id; if (i_dqi_cnt=1) then s_prtnr_cnt := 'Yes'; else s_prtnr_cnt := 'No'; end if; --15762540 EOQ: Partner: Count --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 15762540, s_prtnr_cnt, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('wAteU2Io7Ye',s_prtnr_cnt,d_today); -- EOQ: OVC: Partner Count if (s_cli_type not in ('GED','none')) then s_hiv_test := 'Not Tested'; s_hiv_sts := 'No status'; s_hiv_vl := ''; s_hiv_rl := 'Unknown'; i_cnt := (select count(1) from ipap_hiv_status_data th where th.tei_id = i_tei_id and th.f_testing is not null and f_hiv_sts is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false'); if (i_cnt > 0) then s_hiv_sts := (select f_hiv_sts from ipap_hiv_status_data th where th.tei_id = i_tei_id and th.f_testing is not null and f_hiv_sts is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false' order by th.exe_dt desc limit 1); if (s_hiv_sts in ('Positive on treatment','Positive not on treatment')) then s_hiv_rl := ''; s_hiv_test := 'Tested and Received test results'; s_hiv_vl := '1= Dont Know'; i_cnt := (select count(1) from ipap_viral_load_data th where th.tei_id = i_tei_id and f_vl_index is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false'); if (i_cnt > 0) then s_hiv_vl := (select f_vl_index from ipap_viral_load_data th where th.tei_id = i_tei_id and f_vl_index is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false' order by th.exe_dt desc limit 1); if (s_hiv_vl <> '1= Dont Know' and s_cli_type <> 'Graduated' and (udf_exp_days('VL',i_pi_id, d_bo_tq, d_eo_tq)<=i_grc_days)) then s_hiv_vl := '6 = Dont Know (Result expired)'; end if; end if; else if (s_hiv_sts in ('HIV negative','HIV status not disclosed')) then s_hiv_test := 'Tested and Received test results'; if (s_cli_type <> 'Graduated' and udf_exp_days('HIV',i_pi_id, d_bo_tq, d_eo_tq)<=i_grc_days) then s_hiv_sts := 'No status (Result expired)'; s_hiv_test := 'Not Tested (Result expired)'; end if; elseif (s_hiv_test='Tested, Didnt receive test results') then if (s_cli_type <> 'Graduated' and udf_exp_days('HIV',i_pi_id, d_bo_tq, d_eo_tq)<=i_grc_days) then s_hiv_sts := 'No status (Result expired)'; s_hiv_test := 'Not Tested (Result expired)'; end if; elseif (s_hiv_sts = 'No status (Result expired)') then s_hiv_test := 'Not Tested (Result expired)'; end if; i_cnt := (select count(1) from ipap_risk_level_data th where th.tei_id = i_tei_id and f_rl_data is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false'); if (i_cnt>0) then s_hiv_rl := (select f_rl_data from ipap_risk_level_data th where th.tei_id = i_tei_id and f_rl_data is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false' order by th.exe_dt desc limit 1); if (s_hiv_rl <> 'Unknown' and (s_cli_type <> 'Graduated' and udf_exp_days('RA',i_pi_id, d_bo_tq, d_eo_tq)<=i_grc_days)) then if (s_hiv_test like 'Not Tested%') then s_hiv_rl := 'Unknown (Result expired)'; end if; end if; end if; end if; else i_cnt := (select count(1) from ipap_risk_level_data th where th.tei_id = i_tei_id and f_rl_data is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false'); if (i_cnt>0) then s_hiv_rl := (select f_rl_data from ipap_risk_level_data th where th.tei_id = i_tei_id and f_rl_data is not null and th.exe_dt <= d_eo_tq::date and del_flag = 'false' order by th.exe_dt desc limit 1); if (s_hiv_rl <> 'Unknown' and (s_cli_type <> 'Graduated' and udf_exp_days('RA',i_pi_id, d_bo_tq, d_eo_tq)<=i_grc_days)) then if (s_hiv_test like 'Not Tested%') then s_hiv_rl := 'Unknown (Result expired)'; end if; end if; end if; end if; --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 803, s_hiv_test, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('yobE5rffqVg',s_hiv_test,d_today); -- HIV Testing --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 802, s_hiv_sts, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('AVVq0WaCpb5',s_hiv_sts,d_today); -- HIV Status --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 801, s_hiv_rl, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('zB6OcDdyNW8',s_hiv_rl,d_today); -- HIV Risk Level --insert into trackedentitydatavalue(programstageinstanceid, dataelementid, value, created, lastupdated, providedelsewhere, storedby) -- values(i_next_psi_id, 853, s_hiv_vl, d_eo_tq, d_eo_tq, 'false', 'smuthusamy'); s_json_value = s_json_value || ',' || msp_mk_json('QgoQMnBHFO5',s_hiv_vl,d_today); -- Viral load index end if; insert into tmp_qtr_updt_log values(i_rec_num, now(), pd_qe, d_bo_tq, d_eo_tq, d_bo_lq, d_eo_lq, d_eo_pq, i_tei_id, i_next_psi_id, i_age, s_age_r, s_rec_tq, s_rec_lq, s_rec_pq, d_reg_dt, s_reg_sts, s_cli_type || '/' || s_rec_lq, s_hiv_test, s_hiv_sts, s_hiv_rl, s_hiv_vl); end loop; raise notice '-> Updating UIDs...'; update programstageinstance set uid = generate_uid() where uid is null; raise notice '> OVC Qtrly Update - Ended at %', clock_timestamp()::timestamp; else raise notice 'Missing input param!'; end if; return 0; end; $BODY$; ALTER FUNCTION public.udf_n2_updt_qtr_ovc_smry(date, date) OWNER TO dhis;