SMS handler functions: Perbedaan revisi
(Created page with "{{#apGetSQL: select format ('<h2>%s: %s</h2> <h3>Format pesan</h3> %s %s %s %s', s.start_code, s.name[1],get_sms_format(s.id,1), format('<h3>Reply SQL</h3> %s',replace(s.r...") |
(Tidak ada perbedaan)
|
Revisi per 25 Februari 2014 10.25
Daftar isi
AH: Akhir HewanFormat pesanAH [ID Pemilik] [ID Hewan] [Kondisi Hewan] Reply SQLselect format(get_string('AH_reply', sms_userid), h.identifikasi, get_species_name(h.idspesies,sms_userid), s.name[get_lang(sms_userid)], nama(get_farmer_id(params[1])), t.name[get_lang(sms_userid)]) from kejadian k join hewan h on h.id = k.idhewan and not h.del join sex s on s.id = h.idsex and not s.del join tipeakhir t on t.id = k.datakejadian::integer and not t.del where k.msgid = sms_msgid Business RulesHook: sms_postinsert; Order: 1update pemilik_hewan set tanggalakhir = now(), modifiedon = now(), modifiedby = $1 from hewan h where h.id = idhewan and idpemilik = get_farmer_id($2[1],$1) and upper(h.identifikasi) = upper($2[2]) and tanggalakhir is null and not pemilik_hewan.del and not h.del Hook: sms_postinsert; Order: 10UPDATE hewan h SET tanggal_kematian = now()::date, status_kematian = (select id from (select unnest(regexp_matches($2[3], '([a-z]+)', 'igx')) as code) as dat left outer join tipeakhir t on upper(t.code) = upper(dat.code) AND NOT t.del), modifiedon = now(), modifiedby = $1 from pemilik_hewan ph where h.id = ph.idhewan and ph.idpemilik = get_farmer_id($2[1], $1) and upper(h.identifikasi) = upper($2[2]) and h.tanggal_kematian is null and h.status_kematian is null and not ph.del and not h.del | ||
BB: Berat BadanFormat pesanBB [ID Pemilik] [ID hewan] [Berat Badan] Reply SQLselect format(get_string('BB_reply',sms_userid), get_species_name(h.idspesies,sms_userid), h.identifikasi, sex.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name, k.datakejadian) from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid where k.msgid = sms_msgid Business RulesHook: sms_validate; Order: 10Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan berat badan yang sama pada hari ini.') end from (select k.id -- get_animal_id(upper($2[1]),upper($2[2]),$1) as x from kejadian k where k.idtipekejadian=4 -- kelahiran and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal AND k.createdon::date=now()::date -- same date AND k.datakejadian = $2[3]--same calve AND k.createdby = $1 -- same inseminator ) as z | ||
BCS: Kondisi ScoreFormat pesanBCS [ID Pemilik] [ID Hewan] [score 1,2,3,4,5] Reply SQLselect format(get_string('BCS_reply',sms_userid), k.datakejadian, get_species_name(h.idspesies,sms_userid), h.identifikasi, -- sex.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name) from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id --and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid where k.msgid = sms_msgid | ||
BCSK: Kondisi Score Kelompok HewanFormat pesanBCSK [Kode Kelompok] ([Nilai Kondisi] [Jumlah hewan]...) Reply SQLselect format(get_string('BCSK_reply', sms_userid), string_agg(format ('%s (%s)', t1.nilai, t1.jumlahhewan),', ')) from (select bcs.nilai , sum(kk.jumlahhewan) as jumlahhewan from kejadiankelompok kk join bcs on bcs.id=kk.idklasifikasi and not bcs.del where kk.idtipekejadian=15 and not kk.del and kk.msgid=sms_msgid group by 1 order by 1) t1 | ||
BH: BirahiFormat pesanBH {ID Pemilik} [ID Hewan] Reply SQLwith an as ( select ph.idpemilik as idpemilik , h.id as idhewan from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.idpemilik=get_farmer_id(coalesce(params[1],sms_phone),sms_userid) where h.identifikasi=upper(params[2]) ) select format(get_string('BH_reply', sms_userid), get_species_name(idspesies,sms_userid), identifikasi, coalesce(inseminator, registrar)) from hewan h join an on true left outer join ( select format ('%s (%s)',nama(u.id), local_phone(phone)) as inseminator from kejadian k join an on true join users u on k.createdby = u.id and not u.del where k.idhewan = an.idhewan and not k.del and k.idtipekejadian in (2,3) and k.createdby <> an.idpemilik order by reportdate desc limit 1) as foo on true left outer join ( select format ('%s (%s)',nama(u.createdby), local_phone(phone)) as registrar from users u where u.id=get_farmer_id(coalesce(params[1],sms_phone))) as bar on true where h.id = an.idhewan Alert SQLwith an as ( select ph.idpemilik as idpemilik , h.id as idhewan from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.idpemilik=get_farmer_id(coalesce(params[1],sms_phone),sms_userid) where h.identifikasi=upper(params[2]) ) select u1.phone, format(get_string('BH_alert', u1.id), h.identifikasi, get_species_name(h.idspesies,u1.id), nama(u.id), local_phone(u.phone), l.name) from users u1 join an on true join users u on u.id = an.idpemilik join hewan h on h.id = an.idhewan join locations l on u.locationid = l.id where u1.id in ( select distinct uid from ( select * from (select 1 as natord, k.createdby as uid from kejadian k join users u on k.createdby = u.id and not u.del where k.idhewan in ( select idhewan from pemilik_hewan where idpemilik = get_farmer_id(coalesce(params[1],sms_phone),sms_userid) and tanggalakhir is null and not del ) and not k.del and idtipekejadian in (2,3) order by reportdate desc limit 1) as foo union select 2, createdby from users u where u.id = sms_userid /* -- ========== Training Modifications ============ -- add all local inseminators for training union select 3, u.id from users u join users u2 on u2.id = sms_userid where is_permitted('get_bh_alert',u.id) -- 19 = any(u.groupid) and ischildlocation(u.area, u2.locationid) and not u.del */ -- disable limit for training order by natord limit 1 ) as foo ) | ||
CIP: Cari Identifikasi PeternakFormat pesanCIP [Nama Peternak] [Kode Lokasi] Reply SQL/* -- old version -- doesn't work because no order clause select * from ( Select format(get_string('CIP_notfound', sms_userid)) UNION ( select * from ( select format(get_string('CIP_reply',sms_userid), lokasi, string_agg(format('%s(%s)',nama,idpeternak),',')) as ab from ( select upper(firstname) as nama, local_phone(phone) as idpeternak, get_location_rev(u.locationid) AS lokasi from users u join locations s on u.locationid=s.id and s.code= params[2] and s.validto is null and not s.del and s.level=5 where groupid =array[10] ) as x where upper(nama) LIKE upper(params[1]||'%') group by lokasi ) as y ) ) as xz limit 1 */ -- new version select msg from ( select 2 as seq, format(get_string('CIP_notfound', sms_userid)) as msg UNION select 1, format(get_string('CIP_reply',sms_userid),lokasi, string_agg(format('%s(%s)',nama,idpeternak),',')) from ( select upper(firstname) as nama, local_phone(phone) as idpeternak, get_location_rev(u.locationid) AS lokasi from users u join locations s on u.locationid=s.id and s.code= params[2] and s.validto is null and not s.del and s.level=5 where groupid =array[10] ) as x where upper(nama) LIKE upper(params[1]||'%') group by lokasi order by seq asc limit 1) y | ||
CKAB: Cari Kode Asal BibitFormat pesanCKAB [asal bibit] Reply SQLselect coalesce( case when length(msg) < 160 then msg else left(msg, 160) end, format(get_string('CKAB_notfound',sms_userid),params[1])) from ( select get_string('CKAB_reply',sms_userid)||string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from asalbibit where similarity(upper(name[1]), upper(params[1])) > 0.1 order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKH: Cari Kode HewanFormat pesanCKH [species] Reply SQLselect coalesce(msg, format(get_string('CKH_notfound',sms_userid),params[1])) from ( select string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from species where not del and level >=3 and similarity(upper(name[1]), upper(params[1])) > 0.1 and left(hiercode,2) not in ('10','11') order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKHI: Cari Kode HijauanFormat pesanCKHI [jenis hijauan] Reply SQLselect coalesce( case when length(msg) < 160 then msg else left(msg, 160) end, format(get_string('CKHI_notfound',sms_userid),params[1])) from ( select get_string('CKHI_reply',sms_userid)||string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from jenishijauan where similarity(upper(name[1]), upper(params[1])) > 0.1 order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKI: Cari kode infrastrukturFormat pesanCKI [jenis infrastruktur] [lokasi] Reply SQLselect rep from ( select 1 as ord, format( get_string('CKI_reply',sms_userid), it2.name[get_lang(sms_userid)], get_location(l.id), string_agg(format('%s: %s',i.name, i.code),'; ')) as rep from infrastructure i join infrastructure_types it1 on it1.id = i.infrastructure_typeid join infrastructure_types it2 on it2.code = upper(params[1]) join locations l on l.code = params[2] where is_child(it2.hiercode, it1.hiercode) and is_parent_area(i.locationid, l.id) and not i.del group by it2.name[get_lang(sms_userid)], get_location(l.id) UNION select 2, (get_string('CKI_notfound', 2) ) )foo order by ord limit 1 | ||
CKJS: Cari kode jenis spesimenFormat pesanCKJS [jenis specimen] Reply SQLselect coalesce(msg, format(get_string('CKJS_notfound',sms_userid),params[1])) from ( select string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from specimentypes where similarity(upper(name[1]), upper(params[1])) > 0.1 order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKL: Cari kode lokasiFormat pesanCKL [nama lokasi] Reply SQLselect trunc_sms(string_agg(msg, '; '),2) from ( select coalesce(name, format(get_string('CKL_notfound',sms_userid),params[1])) as msg from ( select format('%s: %s', name, code) as name, (3.0+ (1.0-length(code)/10.0))::float as match from locations where upper(trim(params[1])) = upper(name) and not del and validto is null UNION select format('%s: %s', name, code) as name, (2.0+ (1.0-length(code)/10.0))::float as match from locations where (name ~* ('^'||params[1]||'\M')) and not del and upper(trim(params[1])) <> upper(name) and validto is null UNION select format('%s: %s', name, code) as name, (1.0+ (1.0-length(code)/10.0))::float as match from locations where (name ~* ('.+\m'||params[1]||'\M')) and not del and upper(trim(params[1])) <> upper(name) and validto is null UNION select name, match from ( select format('%s: %s', name, code) as name, similarity(upper(name), upper(params[1])) as match from locations where not del and validto is null and (name !~* ('\m'||params[1]||'\M')) order by match desc limit 10) as foo order by match desc limit 100 ) as bar ) as too | ||
CKO: Cari kode obatFormat pesanCKO [obat] Reply SQLselect coalesce( case when length(msg) < 160 then msg else left(msg, 160) end, format(get_string('CKO_notfound',sms_userid),params[1])) from ( select get_string('CKO_reply',sms_userid)||string_agg(sgn, '; ') as msg from ( select format('%s %s', name, code) as sgn from drugs where similarity(upper(name), upper(params[1])) > 0.1 order by similarity(upper(name), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKP: Cari kode penyakitFormat pesanCKP [penyakit] Reply SQLselect coalesce(string_agg(msg,'; '), format(get_string('CKP_notfound',sms_userid), params[1])) from ( select id, format('%s%s: %s', syn, case when syn=name then else format(' (%s)', name) end, code ) as msg, similarity(syn, params[1]) as sim from ( select id, code, name[1] as name, unnest(name||synname||code) as syn from diseases where not del ) as foo where params[1] % syn order by sim desc limit 10 ) as bar | ||
CKPR: Cari Kode ProdukFormat pesanCKPR [Produk] Reply SQLselect coalesce(msg, format(get_string('CKPR_notfound',sms_userid),params[1])) from ( select string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from species where not del and level >= 3 and similarity(upper(name[1]), upper(params[1])) > 0.1 and left(hiercode,2) in ('11') order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKS: Cari Kode Sampel (lingkungan)Format pesanCKS [species] Reply SQLselect coalesce(msg, format(get_string('CKS_notfound',sms_userid),params[1])) from ( select string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from species where similarity(upper(name[1]), upper(params[1])) > 0.1 and left(hiercode,2) in ('10','11') order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar | ||
CKT: Cari kode tandaFormat pesanCKT [tanda] Reply SQL/*select coalesce(msg, format(get_string('CKT_notfound',sms_userid),params[1])) from ( select string_agg(sgn, '; ') as msg from ( select format('%s %s', name[1], code) as sgn from signs where similarity(upper(name[1]), upper(params[1])) > 0.1 or similarity(upper(synname), upper(params[1])) > 0.1 order by similarity(upper(name[1]), upper(params[1])) desc limit 20 ) foo ) as bar */ select coalesce(string_agg(msg,'; '), format(get_string('CKT_notfound',sms_userid), params[1])) from ( select id, format('%s%s: %s', syn, case when syn=name then else format(' (%s)', name) end, code ) as msg, similarity(syn, params[1]) as sim from ( select id, code, name[1] as name, unnest(name||synname||code) as syn from signs where not del ) as foo where params[1] % syn order by sim desc limit 10 ) as bar | ||
CPD: Cek Populasi DesaFormat pesanCPD [spesies] [kode lokasi] Reply SQLselect coalesce(foo, case when get_location(params[2]) is null then format(get_string('E_location', sms_userid),params[2]) end) from (Select format(' %s jumlah populasi %s '||populasi,lokasi,hewan )as foo from ( select case when (get_population(hid,lid)).total is not null then (get_population(hid,lid)).total::text else ' tidak diketahui.' end as populasi, lokasi, hewan, hid,lid from ( select 1 as xx, get_location_rev(loc.id) as "lokasi",loc.id as lid from locations loc where loc.code=params[2] ) as lo Join ( select 1 as xx,sp.id as hid, get_species_name(sp.id,sms_userid) as "hewan" from species sp where lower(sp.code)=lower(params[1]) ) as ho ON lo.xx=ho.xx ) as po ) as xxx Business RulesHook: sms_validate; Order: 10select case when length($2[2]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end | ||
CSR: Cari Status ReproduksiFormat pesanCSR [ID Peternak] [ID Hewan] Reply SQLwith bcs as (select coalesce(bcs.datakejadian,'tidak dilaporkan') as val from hewan h join pemilik_hewan ph on ph.idhewan=h.id and not ph.del and ph.tanggalakhir is null join users u on u.id=ph.idpemilik and not u.del and local_phone(u.phone) ~ (params[1]||'$') left join kejadian bcs on bcs.idtipekejadian=15 and h.id=bcs.idhewan and not bcs.del where upper(h.identifikasi) = upper(params[2]) and not h.del order by bcs.reportdate desc limit 1) select msg from (select 2 as seq , get_string('CSR_notfound', sms_userid) as msg union select * from (select 1 , format(get_string('CSR_reply', sms_userid) , add_checkdigit(k.id) , get_farmer_id(params[1]) , h.identifikasi , sr.name[get_lang(sms_userid)] , bcs.val , tanggal(k.reportdate)) from kejadian k join statusrepro sr on k.datakejadian::integer=sr.id and not sr.del join hewan h on h.id = k.idhewan and not h.del join pemilik_hewan ph on ph.idhewan=h.id and not ph.del and ph.tanggalakhir is null join users u on u.id=ph.idpemilik and not u.del and local_phone(u.phone) ~ (params[1]||'$') join bcs on true where upper(h.identifikasi) = upper(params[2]) and not k.del and k.idtipekejadian=16 order by k.reportdate desc limit 1) t1 order by seq asc limit 1) t2 | ||
CUL: PemusnahanFormat pesanCUL [ID program] ([spesies] [jumlah hewan]...) [ID lokasi] Reply SQLselect format(get_string('CUL_reply',sms_userid), res, get_disease_names(p.diseaseid,2), get_location(locationid), p.name) from ( select min(programafkirid) as programid, min(locationid) as locationid, string_agg(format('%s %s',jumlah, get_species_name(speciesid, sms_userid)), ', ') as res from afkir where msgid = sms_msgid) as foo join programafkir p on p.id = foo.programid | ||
CV: Cek cakupan vaksinasiFormat pesanCV [kode lokasi] Reply SQLselect x7 from ( select 1 as srtodr, coalesce(foo, case when get_location(params[1]) is null then format(get_string('E_location', sms_userid),params[1]) else get_location(params[1]) end) as x7 from (Select format('%s (%s %s divaksin %s) cakupan %s',lokasi,hewan,populasi,dose,round(cakupan::numeric,2)*100||'%') as foo from ( select lokasi, hewan, populasi, sum(dose) as dose,sum(cakupan) as cakupan from ( Select lokasi,hewan,jumlah as populasi, case when not dosis is null then dosis else 0 end as dose, case when not dosis/jumlah::numeric(8,3) is null then dosis/jumlah::numeric(8,3) else 0 end as cakupan from (select get_location_rev(p.locationid) as "lokasi", at.name[1] as "hewan", p.locationid, total as "jumlah", at.species, reportdate::date as "Tanggal laporan" from population p join animal_types at on at.id = p.animaltypeid join locations l on p.locationid = l.id and l.code=params[1] join ( select max(reportdate) as mdate, animaltypeid, locationid from population group by animaltypeid, locationid ) as maxdate on maxdate.animaltypeid = p.animaltypeid and maxdate.locationid = p.locationid and maxdate.mdate = p.reportdate where not p.del and at.species in(60) and ischildlocation( ARRAY[1], p.locationid ) order by l.code, at.name ) as pop Join (Select v.locationid, get_location_rev(v.locationid) as "Lokasi1", get_species_name(sp.id,sms_userid) as "hewan1", sp.id, sum(v.firstdose) as "dosis" from vaccinations v join species spc on spc.id=v.speciesid join species sp on sp.hiercode=left(spc.hiercode,5) where not v.del and left(spc.hiercode,5) in ('4.1.1') group by v.locationid,get_location(v.locationid) , spc.hiercode, sp.id order by get_species_name(sp.id,sms_userid) asc ) as vak ON pop.locationid=vak.locationid AND pop.species=vak.id ) as xxx group by lokasi,hewan,populasi ) as zzz) as aaa Union select 2, format(get_string('CV_notfound',sms_userid),params[1]) order by srtodr limit 1 ) as bar | ||
CVR: Cek cakupan vaksinasi rabiesFormat pesanCVR [kode lokasi] Reply SQLselect x7 from ( select 1 as srtodr, coalesce(foo, format(get_string('CV_data', sms_userid),get_location(params[1]))) as x7 from (Select format('%s (%s %s divaksin %s) cakupan %s',lokasi,hewan,populasi,dose,round(cakupan::numeric,2)*100||'%') as foo from ( select lokasi, hewan, populasi, sum(dose) as dose,sum(cakupan) as cakupan from ( Select lokasi,hewan,jumlah as populasi, case when not dosis is null then dosis else 0 end as dose, case when not dosis/jumlah::numeric(8,3) is null then dosis/jumlah::numeric(8,3) else 0 end as cakupan from (select get_location_rev(p.locationid) as "lokasi", at.name[1] as "hewan", p.locationid, total as "jumlah", at.species, reportdate::date as "Tanggal laporan" from population p join animal_types at on at.id = p.animaltypeid join locations l on p.locationid = l.id and l.code=params[1] join ( select max(reportdate) as mdate, animaltypeid, locationid from population group by animaltypeid, locationid ) as maxdate on maxdate.animaltypeid = p.animaltypeid and maxdate.locationid = p.locationid and maxdate.mdate = p.reportdate where not p.del and at.species in(60) and ischildlocation( ARRAY[1], p.locationid ) order by l.code, at.name ) as pop Join (Select v.locationid, get_location_rev(v.locationid) as "Lokasi1", get_species_name(sp.id,sms_userid) as "hewan1", sp.id, sum(v.firstdose) as "dosis" from vaccinations v join species spc on spc.id=v.speciesid join species sp on sp.hiercode=left(spc.hiercode,5) where not v.del and left(spc.hiercode,5) in ('4.1.1') group by v.locationid,get_location(v.locationid) , spc.hiercode, sp.id order by get_species_name(sp.id,sms_userid) asc ) as vak ON pop.locationid=vak.locationid AND pop.species=vak.id ) as xxx group by lokasi,hewan,populasi ) as zzz) as aaa Union select 2, format(get_string('CV_notfound',sms_userid),params[1]) order by srtodr limit 1 ) as bar Business RulesHook: sms_validate; Order: 10select case when length($2[1]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end | ||
D: Daftar penggunaFormat pesanD Reply SQLselect 1 | ||
DH: Daftar HewanFormat pesanDH [ID Peternak] ([Identifikasi Hewan] [Kode spesies] [Jenis Kelamin] [Umur]...) Reply SQLselect format (get_string('DH_reply',sms_userid),name, string_agg(format('%s %s (%s)',species, sex, ids),'; ')) from ( select nama(u.id) as name, get_species_name(h.idspesies,sms_userid) as species, sex.name[get_lang(sms_userid)] as sex, string_agg( h.identifikasi,', ') as ids from hewan h join pemilik_hewan p on p.idhewan = h.id join users u on u.id = p.idpemilik join sex on sex.id = h.idsex where h.msgid = sms_msgid group by u.id, idspesies, sex.name[get_lang(sms_userid)]) as foo group by name Business RulesHook: sms_validate; Order: 0drop table if exists regans Hook: sms_validate; Order: 1create temporary table regans ( re text[], ident varchar, species integer, sex integer, birth date ) Hook: sms_validate; Order: 2insert into regans (re) -- select regexp_matches($2[2], -- '(\w+)[\s,]+(\w+)[\s,]+(\w+)[\s]+(\d+[\.,]?\d*)', 'igm') as data; select array[data[1], data[2], data[3], replace(data[4],',','.')]::text[] from ( select regexp_matches($2[2], '(\w+)[\s,]+(\w+)[\s,]+(\w+)[\s]+(\d+[\.,]?\d*)', 'igm') as data ) foo Hook: sms_validate; Order: 3select case when animals is not null then (1,format(get_string('DH_duplicate',$1),animals)) else (0,) end as result from ( select string_agg(code,',') as animals from ( select upper(re[1]) as code, count(*) as num from regans group by upper(re[1]) ) foo where num>1) foo2 -- previous code /* select case when max(count) > 1 then (1,format(get_string('DH_duplicate',$1), code)) else (0,) end as result from ( select upper(re[1]) as code, count(*) from regans group by upper(re[1]) ) foo group by code */ Hook: sms_validate; Order: 4select case when bool_and(emsg is null) then (0,) else (1,string_agg(emsg,'; ')) end from ( select case when count(*) = 0 then null else format(get_string('DHM_invspec',$1), string_agg(format('%s (%s)',r.re[1], r.re[2]),', ')) end as emsg from regans r left outer join species s on s.code = upper(r.re[2]) where s.id is null UNION select case when count(*) = 0 then null else format(get_string('DHM_invsex',$1), string_agg(format('%s (%s)',r.re[1], r.re[3]),', ')) end as emsg from regans r left outer join sex s on s.code = upper(left(r.re[3],1)) where s.id is null UNION select case when count(*) = 0 then null else format(get_string('DHM_invage',$1), string_agg(format('%s (%s)',r.re[1], r.re[4]),', ')) end as emsg from regans r where r.re[4]::float > 30 UNION select case count(*) when 1 then null when 0 then format(get_string('DH_invfarmerid',$1), $2[1]) else format(get_string('DH_matches',$1), count(*), $2[1]) end as emsg from get_farmer_ids($2[1],$1) /* users u join users u2 on u2.id = $1 where local_phone(u.phone) ~ ($2[1]||'$') and not u.del and ischildlocation(u2.area,u.locationid) and 10 = any(u.groupid) */ UNION select case count(*) when 0 then null else format(get_string('DH_dupan',$1), nama(u.id), string_agg(r.re[1],', ')) end as emsg from users u join pemilik_hewan p on p.idpemilik = u.id and p.tanggalakhir is null and not p.del join hewan h on h.id = p.idhewan and not h.del join regans r on h.identifikasi = upper(r.re[1]) where u.id = get_farmer_id($2[1],$1) -- where u.phone ~ ($2[1]||'$') and not u.del group by u.id ) as foo Hook: sms_postinsert; Order: 1update regans set ident = upper(re[1]), species = s.id, sex = sex.id, birth = now()-(replace(re[4],',','.')||' years')::interval from species s, sex where s.code = upper(re[2]) and sex.code = upper(left(re[3], 1)) Hook: sms_postinsert; Order: 2select case when count(*) = 0 then (0,) else (1,format(get_string('IDHEWANBARU_error',$1),string_agg(r.ident,','))) end from hewan h join pemilik_hewan ph on ph.idhewan = h.id and tanggalakhir is null and not ph.del join users u on u.id = ph.idpemilik --and local_phone(u.phone) ~ ($2[1]||'$') and u.id = get_farmer_id($2[1], $1) and 10 = any(groupid) and not u.del join regans r on upper(r.ident) = upper(h.identifikasi) where not h.del Hook: sms_postinsert; Order: 3with andata as ( insert into hewan (identifikasi, idspesies, idsex, tanggallahir, msgid, createdby) select ident, species, sex, birth, $3, $1 from regans returning id ) insert into pemilik_hewan (idpemilik, idhewan, msgid, createdby) select get_farmer_id($2[1],$1), id, $3, $1 from andata Hook: sms_postinsert; Order: 4drop table regans Hook: sms_postinsert; Order: 10select case when send_template_report('ID hewan', u.email, h.id, case when get_param('hostname') = 'sidecar' then 'sidecar' else 'live' end) = 'True' then (0,) else (1,'Unable to send animal ID certificate by email') end from hewan h join users u on u.id = h.createdby where h.msgid = $3 and u.email is not null and trim(u.email) <> and not h.del | ||
DHK: Daftar Hewan KasusFormat pesanDHK [ID Kasus] Reply SQLselect format(get_string('DHK_reply',sms_userid),string_agg(seq,';') )as msg from (select format('%s:%s',bar.name,string_agg(ansp,',')) as seq from (select name, format ('%s(%s)',foo.ids,row_number()over (order by urutan asc))as ansp from (select nama(p.idpemilik::integer) as name, h.identifikasi as ids, ca.createdon as urutan from case_animals ca join hewan h on h.id = ca.idhewan and not h.del join pemilik_hewan p on p.idhewan = ca.idhewan and not p.del and tanggalakhir is null where add_checkdigit(ca.idcase) = params[1]::integer and not ca.del and p.tanggalakhir is null order by ca.createdon) as foo group by name,ids,urutan) as bar group by name) as bee | ||
DHP: Daftar hewan peternakFormat pesanDHP {ID Peternak} Reply SQLwith farmer as ( select get_farmer_id(params[1],sms_userid) as id ) select msg from ( select 1 as ord, format(get_string('DHP_reply',sms_userid),name, string_agg(ansp,'; ')) as msg from ( select name, format ('%s %s: %s',species, gend, string_agg(foo.ids, ',')) as ansp from ( select nama(p.idpemilik::integer) as name, sp.name[get_lang(sms_userid)] as species, s.name[get_lang(sms_userid)] as gend, identifikasi as ids from pemilik_hewan p join hewan h on h.id = p.idhewan and not h.del join sex s on s.id = h.idsex join species sp on sp.id = h.idspesies join farmer on true where p.idpemilik = farmer.id and not p.del and p.tanggalakhir is null order by identifikasi ) foo group by name, species, gend) bar group by name UNION select 2, format(get_string('DHP_noanreg',sms_userid),nama(farmer.id)) from farmer order by ord limit 1) as bar /* select msg from ( select 1 as ord, format(get_string('DHP_reply',sms_userid),name, string_agg(ansp,'; ')) as msg from ( select name, format ('%s %s: %s',species, gend, string_agg(foo.ids, ',')) as ansp from ( select nama(p.idpemilik::integer) as name, sp.name[get_lang(sms_userid)] as species, s.name[get_lang(sms_userid)] as gend, identifikasi as ids from pemilik_hewan p join hewan h on h.id = p.idhewan join sex s on s.id = h.idsex join species sp on sp.id = h.idspesies join users u on u.id = p.idpemilik and u.phone = case when params[1] ~ '^08\d{9,10}$' then -- valid mobile number '62'||ltrim(params[1],'0') when params[1] ~ '^[1-9]\d{11}$' then -- valid SIM number '1'||params[1] when params[1] ~ '^[1-9]\d{5,11}$' then -- other numeric code '2'||lpad(params[1],12,'0') end and not h.del and not p.del and p.tanggalakhir is null order by identifikasi ) foo group by name, species, gend) bar group by name UNION select 2, format(get_string('DHP_noanreg',sms_userid),nama(get_farmer_id(params[1],sms_userid))) order by ord limit 1) as bar */ -- old version /* select msg from ( select 1 as ord, format(get_string('DHP_reply',sms_userid),name, string_agg(ansp,'; ')) as msg from ( select name, format ('%s %s: %s',species, gend, string_agg(foo.ids, ',')) as ansp from ( select nama(p.idpemilik::integer) as name, sp.name[get_lang(sms_userid)] as species, s.name[get_lang(sms_userid)] as gend, identifikasi as ids from pemilik_hewan p join hewan h on h.id = p.idhewan join sex s on s.id = h.idsex join species sp on sp.id = h.idspesies where p.idpemilik = coalesce(get_farmer_id(params[1],sms_userid), sms_userid) and not h.del and not p.del and p.tanggalakhir is null order by identifikasi ) foo group by name, species, gend) bar group by name UNION select 2, format(get_string('DHP_noanreg',sms_userid),nama(get_farmer_id(params[1],sms_userid))) order by ord limit 1) as bar */ | ||
DIP: Daftar Infrastruktur PenggunaFormat pesanDIP [ID Pengguna] [Kode Infrastruktur] [Jenis Infrastruktur] Reply SQLselect format(get_string('DIP_reply',sms_userid), nama(u.id), i.name) as msg from users u join infrastructure i on i.id=u.infraid and not i.del join infrastructure_types it on it.id=i.infrastructure_typeid and not it.del and it.code=upper(params[3]) where u.id = get_farmer_id(params[1],sms_userid) and i.code = params[2] and not u.del Business RulesHook: sms_postinsert; Order: 1update users set infraid=i.id , modifiedby=$1 , modifiedon=now() from infrastructure i join infrastructure_types it on it.id=i.infrastructure_typeid and it.code=upper($2[3]) and not it.del where i.code=$2[2] and not i.del and users.id = get_farmer_id($2[1],$1) | ||
DKB: Daftar kode bangsa hewanFormat pesanDKB [kode spesies] Reply SQLselect string_agg(format('%s (%s)',s1.name[get_lang(sms_userid)], s1.code),'; ') from species s1 where is_child_species(v_species_code, s1.id) and s1.id <> v_species_code | ||
DKL: Daftar kode lokasiFormat pesanDKL [kode lokasi] Reply SQLselect rep from (select 1 as srtodr,format('%s %s: %s',n, c,string_agg(lc,'; ')) as rep from (select l2.name as n, l2.code as c, format('%s %s',l1.name,right(l1.code,2)) as lc from locations l1 join locations l2 on l1.level = l2.level+1 and left(l1.code,length(l2.code))=l2.code and l2.validto is null and not l2.del where l2.code = params[1] and l1.validto is null order by l1.code) as foo group by n,c union select 2, format(get_string('DKL_notfound',sms_userid),params[1]) order by srtodr limit 1) as bar | ||
DNC: Distribusi Nitrogen CairFormat pesanDNC [Jumlah liter] [Kode infrastruktur tujuan] Reply SQLselect format(get_string('DNC_reply', sms_userid), add_checkdigit(s.id), ns.volume, i1.shortname, get_location(i1.locationid), i2.shortname, get_location(i2.locationid)) from shipments s join infrastructure i1 on i1.id = s.origininfraid join infrastructure i2 on i2.id = s.destinfraid join nitrogenshipment ns on ns.shipmentid = s.id where s.msgid = sms_msgid Alert SQLwith lid as ( select centroid, format(get_string('DNC_alert',sms_userid), add_checkdigit(s.id),ns.volume,i.name, i2.name,s.createdon::date ) as msg from shipments s join nitrogenshipment ns on ns.shipmentid = s.id join infrastructure i on i.id = s.origininfraid join infrastructure i2 on i2.id = s.destinfraid join users u2 on u2.id = s.createdby join locations l on l.id = i2.locationid where s.msgid = sms_msgid ), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 37 and permission = 1 ) select u.phone, lid.msg from users u left join gperm on true left join user_permissions up on up.userid = u.id and up.permission_typeid =37 join locations l on array[l.id] && u.area join lid on true -- reporter location where (coalesce(up.permission=1,gperm.p && u.groupid)) and st_contains(l.geom,lid.centroid) | ||
DP: Daftar PemilikFormat pesanDP [Nama] [Kode Lokasi] {Identifikasi pemilik} Reply SQLSELECT case when phone is null then format ('%s dari %s harus mengirimkan' || ' pesan konfirmasi "KDP %s" dari nomor' || ' handphone pengguna yang didaftarkan', firstname, get_location(locationid), pin) else format ('%s dari %s telah didaftarkan dengan identifikasi %s %s', firstname, get_location(locationid), case left(phone,1) when '6' then 'nomor HP' when '1' then 'nomor SIM' when '2' then 'nomor identifikasi' end, phone) end FROM users where msgid=sms_msgid | ||
DSB: Distribusi Semen BekuFormat pesanDSB ([Bangsa hewan] [Jumlah straw]...) [Kode infrastruktur tujuan] Reply SQLselect format(get_string('DSB_reply', sms_userid), add_checkdigit(s.id), string_agg(format('%s (%s)',sp.name[1], total),', '), i1.shortname, get_location(i1.locationid), i2.shortname, get_location(i2.locationid) ) from shipments s join infrastructure i1 on i1.id = s.origininfraid join infrastructure i2 on i2.id = s.destinfraid join strawshipment ns on ns.shipmentid = s.id join species sp on sp.id = ns.breedid where s.msgid = sms_msgid group by s.id, i1.shortname, i1.locationid, i2.shortname, i2.locationid Alert SQLwith lid as ( select centroid, format(get_string('DSB_alert',sms_userid), add_checkdigit(s.id),i.name, i2.name,tanggal(s.createdon::date), string_agg(format('%s(%s unit)', sp.name[1],ss.total), ', ') ) as msg from shipments s join strawshipment ss on ss.shipmentid = s.id join species sp on sp.id = ss.breedid join infrastructure i on i.id = s.origininfraid join infrastructure i2 on i2.id = s.destinfraid join users u2 on u2.id = s.createdby join locations l on l.id = i2.locationid where s.msgid = sms_msgid group by centroid,s.id,i.name,i2.name ), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 37 and permission = 1 ) select u.phone, lid.msg from users u left join gperm on true left join user_permissions up on up.userid = u.id and up.permission_typeid =37 join locations l on array[l.id] && u.area join lid on true -- reporter location where (coalesce(up.permission=1,gperm.p && u.groupid)) and st_contains(l.geom,lid.centroid) | ||
DTB: Daftar Telpon BabinsaFormat pesanDTB [nama] [telp] Reply SQLselect 'berhasil' | ||
DX: Diagnosa definitifFormat pesanDX [ID kasus] [diagnosa] Reply SQLselect format(get_string('DX_reply', sms_userid), d.name[get_lang(sms_userid)], get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), coalesce(get_syndrome_name(sy.syndromeid,sms_userid), get_sign_names(s.signsid,sms_userid)), get_location(c.locationid), tanggal(c.reportdate)) from diagnoses dx join diseases d on dx.diseaseid = d.id join cases c on c.id = dx.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where dx.msgid = sms_msgid | ||
GI: Ganti Identifikasi hewanFormat pesanGI [ID Peternak] [ID Hewan Lama] [ID Hewan Baru] Reply SQLwith foo as ( select get_animal_id(params[1],params[3]) as id ) select format(get_string('GI_reply', sms_userid), get_species_name(h.idspesies, sms_userid), s.name[get_lang(sms_userid)], nama(get_farmer_id(params[1])), params[2], params[3]) from hewan h join sex s on h.idsex = s.id join foo on true where h.id = foo.id and not h.del /* -- old version select format(get_string('GI_reply', sms_userid), get_species_name(h.idspesies, sms_userid), s.name[get_lang(sms_userid)], nama(get_farmer_id(params[1])), params[2], params[3]) from hewan h join sex s on h.idsex = s.id where h.id = get_animal_id(params[1],params[3]) and not h.del */ Business RulesHook: sms_validate; Order: 1select case count(*) when 1 then (0, null) when 0 then (1,format(get_string('DH_invfarmerid',$1), $2[1])) else (1,format(get_string('DH_matches',$1), count(*), $2[1])) end as emsg from get_farmer_ids($2[1],$1) /*select case count(u.id) when 0 then (1,format(get_string('E_owner', $1),$2[1])) when 1 then (0,null) else (1,format(get_string('E_multiowner',$1),count(u.id))) end from users u join users u2 on u2.id = $1 where local_phone(u.phone) ~ ($2[1]||'$') --where u.phone = '62'||trim($2[1],'0') and not u.del and ischildlocation(u2.area,u.locationid) and 10 = any(u.groupid) */ Hook: sms_postinsert; Order: 1with an as ( select get_animal_id($2[1],$2[2]) as id ) update hewan set identifikasi = upper($2[3]), modifiedby = $1, modifiedon = now() from an where hewan.id = an.id /*update hewan set identifikasi = upper($2[3]), modifiedby = $1, modifiedon = now() where id = get_animal_id($2[1],$2[2]) */ | ||
H: HapusFormat pesanH [Kode SMS] Reply SQLselect 1 | ||
HK: Hewan terkait KasusFormat pesanHK [ID Kasus] [ID Pemilik] ([Identifikasi Hewan]...) Reply SQLselect format(get_string('HK_reply',sms_userid), string_agg(hewan,'; '), pemilik, ik) from ( select format ('%s (%s)', get_species_name(h.idspesies,sms_userid), string_agg(h.identifikasi,',')) as hewan, nama(p.idpemilik::integer) as pemilik, add_checkdigit(c.id) as ik from case_animals ca join hewan h on ca.idhewan=h.id and not h.del left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik join cases c on ca.idcase=c.id and not c.del where ca.msgid = sms_msgid group by p.idpemilik,add_checkdigit(c.id), h.idspesies) as foo group by pemilik,ik | ||
HPT: Statistik Pakan TernakFormat pesanHPT ([jenishijauan] [produsen] [jeniskebun] [luas] [Jumlah bibit] [Bulan untuk panen] [sumberanggaran] [tahun_fasilitasi]...) [lokasi] Reply SQLselect format(get_string('HPT_replay',sms_userid), get_location_rev(s.locationid) , i.name, string_agg(format('%s: %s %s hektar (%s stek),%s-%s, bulan perkiraan panen: %s', j.name [get_lang(sms_userid)], t.name[get_lang(sms_userid)], luas, jumlahbibit, sa.name[1], s.tahunfasilitasi, m.name[get_lang(sms_userid)]), '; ')) from hijauan s join jenishijauan t on t.id = s.idjenishijauan and not t.del join infrastructure i on s.idasalbibit = i.id and i.infratype = 9 and not i.del join jeniskebun j on j.id = s.idjeniskebun and not j.del join sumberanggaran sa on sa.id=s.idanggaran and not sa.del left join months m on m.id=s.bulanpanen and not m.del where msgid = sms_msgid group by i.name, get_location_rev(s.locationid) | ||
IB: Inseminasi BuatanFormat pesanIB [ID Peternak] [ID hewan] [ID pejantan] [Kode pembuatan] Reply SQLselect format(get_string('IB_reply', sms_userid), h.identifikasi, get_species_name(h.idspesies,sms_userid), s.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name, case when st.idpejantan is null then format(get_string('IB_unregstraw',sms_userid), params[3], params[4] ) else format(get_string('IB_regstraw',sms_userid), st.idpejantan, st.idpembuatan, get_species_name(h2.idspesies,sms_userid), i.name) end ) from kejadian k join hewan h on k.idhewan = h.id join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null join sex s on s.id = h.idsex join users u on u.id = p.idpemilik join locations l on l.id = u.locationid left outer join straws st on st.id = k.datakejadian::integer left outer join hewan h2 on h2.id = st.idhewan left outer join infrastructure i on i.id = st.idbib where k.msgid = sms_msgid Business RulesHook: sms_validate; Order: 1Select case when count(id)=0 then (0, null) else (1,'Maaf, Hewan belum memiliki eartag. Tambahkan dulu kode eartag') end from ( select h.id from hewan h join pemilik_hewan ph on ph.idhewan = h.id and not ph.del and ph.tanggalakhir is null join users u on u.id = ph.idpemilik and not u.del join species sp on sp.id = h.idspesies and not sp.del where idpemilik = get_farmer_id($2[1],$1) and upper(h.identifikasi) = upper($2[2]) and h.national_code is null and sikhnascode in (2,3) ) as z Hook: sms_validate; Order: 2Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan IB yang sama pada hari ini.') end from ( select k.id -- get_animal_id(upper($2[1]),upper($2[2]),$1) as x from kejadian k join straws s on k.datakejadian = s.id::text where k.idtipekejadian=2 -- insemination and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal AND k.createdon::date=now()::date -- same date AND k.createdby = $1 -- same inseminator AND upper(s.idpejantan) = upper($2[3]) and upper(s.idpembuatan) = (regexp_replace(upper($2[4]),'[^a-z0-9]+',,'i')) and not s.del ) as z | ||
IH: Informasi hewanFormat pesanIH [ID Pemilik] [ID hewan] Reply SQLselect string_agg(detail,'; ') from ( select detail from ( with anid as ( select coalesce ( get_animal_id(params[1], params[2]), id) as animalid from (select p.idhewan as id from pemilik_hewan p join hewan h on h.id = p.idhewan and upper(identifikasi) = upper(params[2]) where idpemilik = get_farmer_id(params[1],sms_userid) order by p.createdon desc limit 1) as fb ) select k.reportdate, format('%s %s%s', to_char(k.reportdate, 'DD/MM/YYY'), t.name[get_lang(sms_userid)], case idtipekejadian when 1 then -- lahir format(get_string('IH_lahir',sms_userid), x.name[get_lang(sms_userid)], h2.identifikasi) when 2 then -- inseminasi ': '||get_species_name(h.idspesies,sms_userid) when 3 then -- PKB ': '||datakejadian||' '||get_string('IH_months',sms_userid) when 4 then -- berat badan ': '||datakejadian||' kg' when 5 then -- akhir hewan ': '||a.name[get_lang(sms_userid)] when 6 then -- keguguran ': trimester '||datakejadian end) as detail from kejadian k join hewan h1 on k.idhewan = h1.id and k.idhewan in (select animalid from anid) join tipekejadian t on t.id = k.idtipekejadian left outer join straws s on s.id = k.datakejadian::float -- round(k.datakejadian::float) and idtipekejadian = 2 and not s.del left outer join hewan h on s.idhewan = h.id and not h.del left outer join tipeakhir a on a.id = k.datakejadian::float -- round(k.datakejadian::float) and idtipekejadian = 5 and not a.del left outer join hewan h2 on h2.id = k.datakejadian::float -- round(k.datakejadian::float) and idtipekejadian = 1 and not h2.del left outer join sex x on x.id = h2.idsex and not x.del where not k.del UNION select tanggallahir, format('%s %s',get_string('IH_born',sms_userid),to_char(tanggallahir, 'DD/MM/YYYY')) from hewan where id in (select animalid from anid) and not del UNION select * from ( select tanggalmulai, format(get_string('IH_reg',sms_userid), to_char(tanggalmulai, 'DD/MM/YYYY'), nama(idpemilik::integer)) from pemilik_hewan where idhewan in (select animalid from anid) and not del order by createdon asc limit 1 ) as foo UNION select * from ( select s.tanggalakhir, format (get_string('IH_sold',sms_userid), to_char(s.tanggalakhir,'DD/MM/YYYY'), nama(b.idpemilik::integer)) from pemilik_hewan s left outer join pemilik_hewan b on s.tanggalakhir = b.tanggalmulai where s.idhewan in (select animalid from anid) and not s.del and s.tanggalakhir is not null and b.idpemilik is not null order by s.createdon) as bar order by reportdate desc limit 10) as foobar order by reportdate asc) as foobarbar | ||
IMAGE: Upload fotoFormat pesanIMAGE [URL] Reply SQLselect case when i.caseid is null then format(get_string('IMAGE_nocase',sms_userid), add_checkdigit(i.id)) else format(get_string('IMAGE_reply',sms_userid), add_checkdigit(i.id), add_checkdigit(c.id), coalesce(s.cases, sy.cases), get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), l.name ) end from caseimages i left outer join cases c on i.caseid = c.id left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id left outer join locations l on l.id = c.locationid where i.msgid = sms_msgid | ||
IO: Informasi ObatFormat pesanIO [Kode Obat] Reply SQLselect string_agg (format ('nama obat:%s, regnumber:%s, manufacturer:%s, composition:%s',name,regnumber,manufacturer,composition),',') from drugs where upper (code) = upper (params[1]) | ||
JBH: Jual/Beli HewanFormat pesanJBH [ID penjual] [ID Pembeli] ([ID Hewan]...) [Tipe] Reply SQLselect format(get_string('JH_reply',sms_userid), nama(sellerid), nama(buyerid), string_agg( newname, ', '),ta.name[get_lang(sms_userid)]) from animaltransfers join tipeakhir ta on ta.code=upper(params[4]) group by sellerid, buyerid, ta.name Business RulesHook: sms_validate; Order: 1select case count(u.id) when 0 then (1,format(get_string('JH_invsellid', $1),$2[1])) when 1 then (0,null) else (1,format(get_string('JH_multisellid',$1),count(u.id))) end from users u join users u2 on u2.id = $1 where local_phone(u.phone) ~ ($2[1]||'$') and not u.del --and ischildlocation(u2.area,u.locationid) and 10 = any(u.groupid) Hook: sms_validate; Order: 2select case count(u.id) when 0 then (1,format(get_string('JH_invbuyid', $1),$2[2])) when 1 then (0,null) else (1,format(get_string('JH_multibuyid',$1),count(u.id))) end from users u join users u2 on u2.id = $1 where local_phone(u.phone) ~ ($2[2]||'$') and not u.del -- and ischildlocation(u2.area,u.locationid) and 10 = any(u.groupid) Hook: sms_validate; Order: 3select case count(*) when 0 then (0, null) else (1,format(get_string('JH_invanid', $1), string_agg(hid,', '))) end from ( select regexp_split_to_table($2[3],E'[^a-z|^0-9]+','i') as hid ) as hewanid where get_animal_id($2[1],hewanid.hid) is null Hook: sms_validate; Order: 4select case count(*) when 0 then (0, null) else (0,format(get_string('JH_buyhasid', $1), string_agg(hid,', '))) end from ( select regexp_split_to_table($2[3],E'[^a-z|^0-9]+','i') as hid ) as hewanid join hewan h on h.identifikasi = upper(hid) join pemilik_hewan p on h.id = p.idhewan and p.tanggalakhir is null and not p.del join users u on p.idpemilik = u.id and not u.del and u.phone ~ ($2[2]||'$') and 10 = any(u.groupid) join users u2 on u2.id = $1 --and ischildlocation(u2.area,u.locationid) where h.id is not null Hook: sms_postinsert; Order: -1create temp table animaltransfers ( sellerid integer, buyerid integer, saleanimals integer, newname varchar ) on commit drop Hook: sms_postinsert; Order: 0insert into animaltransfers select get_farmer_id($2[1]) as sellerid, get_farmer_id($2[2]) as buyerid, get_animal_id($2[1],hw.id) as saleanimals, case when get_animal_id($2[2],hw.id) is not null then get_dup_anident($2[2],hw.id,$1) --upper(hw.id)||'BARU' else upper(hw.id) end as newname from ( select upper(regexp_split_to_table($2[3],E'[^a-z|^0-9]+','i')) as id ) as hw Hook: sms_postinsert; Order: 1insert into pemilik_hewan (idpemilik, idhewan, createdby) select buyerid, saleanimals,$1 from animaltransfers Hook: sms_postinsert; Order: 2update hewan set identifikasi = newname, modifiedby = $1, modifiedon = now() from animaltransfers where hewan.id = saleanimals Hook: sms_postinsert; Order: 3update pemilik_hewan set tanggalakhir = now(), modifiedon = now(), modifiedby = $1 from animaltransfers where idpemilik = sellerid and idhewan = saleanimals Hook: sms_postinsert; Order: 4insert into kejadian (reportdate,idhewan,idtipekejadian,datakejadian,createdby,msgid) select now() , saleanimals , 5 , ta.id , $1 , $3 from animaltransfers join tipeakhir ta on true and not ta.del and ta.live=true and ta.code=upper($2[4]) | ||
KA: Pembiakan alamFormat pesanKA [ID Pemilik hewan] [ID Hewan] {ID Pemilik pejantan} [ID Pejantan] [tanggal_ka] Reply SQLselect format(get_string('KA_reply', sms_userid), h.identifikasi, get_species_name(h.idspesies,sms_userid), nama(p.idpemilik::integer), l.name, coalesce(h2.identifikasi,'tidak diketahui'), tanggal(k.reportdate) ) from kejadian k join hewan h on k.idhewan = h.id join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null join users u on u.id = p.idpemilik join locations l on l.id = u.locationid left join hewan h2 on h2.id=k.datakejadian::integer where k.msgid = sms_msgid Business RulesHook: sms_validate; Order: 1select case when count(*)=1 then (0,) else (1,format(get_string('Inval_owner_id',$1),$2[1])) end from (select get_farmer_ids($2[1],$1)) t1 Hook: sms_validate; Order: 2select case when count(*)=1 then (0,) else (1,format(get_string('Inval_hewan_id',$1),$2[2])) end from ( select h.id from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.tanggalakhir is null and not ph.del where not h.del and h.idsex=2 and ph.idpemilik=get_farmer_id($2[1],$1) and upper(h.identifikasi)=upper($2[2])) t1 Hook: sms_validate; Order: 3with owner_bull as (select case when $2[3] is NULL then $2[1] else $2[3] end as id) select msg from (select 1 as ord , case when count(*)=1 then (0,) else (1,format(get_string('Inval_owner_id',$1),owner_bull.id)) end as msg from (select get_farmer_ids(owner_bull.id,$1) from owner_bull) t1 join owner_bull on true group by owner_bull.id union all select 2 as ord, (1,format(get_string('Inval_owner_id',$1),$2[3])) as msg) t2 order by ord limit 1 Hook: sms_validate; Order: 4with owner_bull as (select case when $2[3] is NULL then $2[1] else $2[3] end as id) select case when count(*)=1 or ($2[4]='000' and $2[3] is null) then (0,) else (1,format(get_string('Inval_hewan_id',$1),$2[4])) end from ( select h.id from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.tanggalakhir is null and not ph.del join owner_bull on true where not h.del and ph.idpemilik=get_farmer_id(owner_bull.id,$1) and upper(h.identifikasi)=upper($2[4]) )t1 Hook: sms_validate; Order: 5select case when count(*)>0 then (1,format(get_string('KA_exists',$1),$2[2])) else (0,) end from kejadian k join hewan h on h.id=k.idhewan and not h.del join pemilik_hewan ph on ph.idhewan=h.id and ph.tanggalakhir is null and not ph.del where not k.del and k.idtipekejadian=10 and ph.idpemilik=get_farmer_id($2[1],$1) and upper(h.identifikasi)=upper($2[2]) and k.reportdate::date=$2[5]::date Hook: sms_postinsert; Order: 1insert into kejadian (reportdate,idhewan,idtipekejadian,datakejadian,createdby,createdon,msgid) select $2[5]::date , h.id , 10 , case when bull_id.id is not null then bull_id.id::varchar else '000' end , $1 , now() , $3 from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.tanggalakhir is null and not ph.del left join (select h.id from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.tanggalakhir is null and not ph.del join (select case when $2[3] is NULL then $2[1] else $2[3] end as id) owner_bull on true where upper(h.identifikasi)=upper($2[4]) and ph.idpemilik=get_farmer_id(owner_bull.id,$1) ) bull_id on true where ph.idpemilik=get_farmer_id($2[1],$1) and upper(h.identifikasi)=upper($2[2]) | ||
KDP: Konfirmasi daftar peternakFormat pesanKDP [PIN] Reply SQLselect format(get_string('KDP_reply', id), firstname, get_location(locationid) ) from users where phone = sms_phone Business RulesHook: sms_validate; Order: 1select case when $1 = -1 then (0,null) else (1, format(get_string('KDP_numreg',$1), nama(id), format_phone(phone))) end from users where id = $1 Hook: sms_postinsert; Order: 10 | ||
KDX: Laporan Kompartemen Diagnosa AkhirFormat pesanKDX [ID Kasus Kompartemen] [Diagnosa Akhir] Reply SQLselect 'berhasil' Business RulesHook: sms_validate; Order: 10select case -- allowed to make a diagnosis when is_permitted('can_update_compartment_dx',$1) then case when $2[2] is null then (1,get_string('E_missingdx',$1)) else case when $2[1]::numeric = ((select add_checkdigit(cdc.id)::numeric from compartment_disease_cases CDC join farms f on cdc.farmid = f.id join user_kompartemen uk on uk.farm_id = f.id where add_checkdigit(cdc.id)::numeric = $2[1]::numeric and uk.user_id = $1)) then (0,null) else (1, format(get_string('E_notpermitted_farm_kdx',$1), $2[1])) end end -- Pelsa else case when $2[2] is not null then (1,get_string('E_dxnotallowed',$1)) else case when $2[1]::numeric = ((select add_checkdigit(cdc.id)::numeric from compartment_disease_cases CDC join farms f on cdc.farmid = f.id join user_kompartemen uk on uk.farm_id = f.id where add_checkdigit(cdc.id)::numeric = $2[1]::numeric and uk.user_id = $1)) then (0,null) else (1, format(get_string('E_notpermitted_farm_kdx',$1), $2[1])) end end end from users u where u.id = $1 Hook: sms_postinsert; Order: 1UPDATE data.compartment_disease_cases cd SET diseaseid = (select CAST(id AS integer) from diseases d where upper(d.code) = upper($2[2]) AND NOT d.del), modifiedby = $1, modifiedon=NOW() where cd.id = reference.remove_checkdigit($2[1]::int) and not cd.del | ||
KGG: KeguguranFormat pesanKGG [ID Pemilik] [ID Hewan] [Trimester 1,2,3] Reply SQLselect format(get_string('KGG_reply',sms_userid), k.datakejadian, get_species_name(h.idspesies,sms_userid), h.identifikasi, -- sex.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name) from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid where k.msgid = sms_msgid | ||
KNEG: Laporan Negatif KompartemenFormat pesanKNEG {Aktif?} Reply SQLSELECT format(get_string('KNEG_reply',sms_userid), f.name, get_location(u.locationid)) from compartement_negativereports n join users u on u.id = n.userid join user_kompartemen uk on uk.user_id = n.userid join farms f on f.id = uk.farm_id where n.msgid = sms_msgid Business RulesHook: sms_validate; Order: 10SELECT CASE WHEN $2[1] IS NOT NULL THEN CASE WHEN UPPER(TRIM($2[1])) IN ('Y','A') THEN (0, NULL) ELSE (1, get_string('KNEG_checked', $1)) END ELSE (0, NULL) END FROM users u WHERE u.id = $1; Hook: sms_postinsert; Order: 10INSERT INTO compartement_negativereports(userid, msgid, createdby, checked) VALUES($1,$3,$1, (SELECT case upper(trim($2[1])) when 'Y' then true when 'A' then true else false end)) | ||
KODE: KodeFormat pesanKODE {jenis kode} Reply SQLselect case upper(trim(params[1])) when 'SP' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from species where level = 3 and not del and not lab) when 'RP' then (select case when length(l1.code) <> 4 then get_string('KODE_rphkab',sms_userid) else format(get_string('KODE_rph',sms_userid), l1.name, string_agg(i.name||' '||right(i.code,2),'; ')) end from users u join locations l1 on u.area[1] = l1.id join locations l2 on l1.code = left(l2.code,length(l1.code)) left outer join infrastructure i on i.locationid = l2.id where u.id = sms_userid and i.infrastructure_typeid in (1,2,3,4,5,6,7) group by l1.name, l1.code) when 'JHRP' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from animal_types where slaughter and not del) when 'POP' then (select get_string('KODE_pop',sms_userid)) when 'TAN' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from signs where pelsa and not del) when 'PK' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from caseresulttypes where not del) when 'JP' then (select string_agg(id||' '||name[get_lang(sms_userid)],'; ') from groups where not del) when 'PROD' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from productionsystems where not del) when 'SL' then (select string_agg(code||' '||name[get_lang(sms_userid)],'; ') from labsections where not del) when 'BS' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from specimenforms where not del) when 'AH' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from tipeakhir where not del) when 'SUM' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from sumber where not del) when 'UC' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from testtypes where rapid and not del) when 'JI' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from infrastructure_types where array_length(string_to_array(hiercode,'.'),1) = 1 and not del) when 'TL' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from tindakan where not del) When 'SIN' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from syndromes where not del) when 'PL' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from tipepenanganan where not del) when 'ABH' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from asalbibit where not del) when 'JHI' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from jenishijauan where not del) when 'JB' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from jenisbibit where not del) when 'JKB' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from jeniskebun where not del) when 'JK' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from jeniskontainer where not del) when 'KK' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from kondisikontainer where not del) when 'TP' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from alasanpenolakan where not del) when 'JBH' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from tipeakhir where not del and id in (4,6)) when 'SA' then (select string_agg(format('%s %s',name[get_lang(sms_userid)],code),'; ') from sumberanggaran where not del) else get_string('KODE_format',sms_userid) end | ||
KOM: KomentarFormat pesanKOM [ID kasus] [komentar] Reply SQLselect format(get_string('KOM_reply',sms_userid), coalesce(si.cases, sy.cases), get_species_name(coalesce(si.speciesid, sy.speciesid),sms_userid), coalesce(get_syndrome_name(sy.syndromeid, sms_userid), get_sign_names(si.signsid,sms_userid)), get_location(c.locationid), tanggal(c.reportdate)) from cases c join komentar k on k.caseid = c.id left outer join signreports si on si.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where k.msgid = sms_msgid Alert SQLselect u.phone, format('Komentar %s untuk kasus[%s]: %s ', nama(k.createdby), add_checkdigit(c.id), k.komentar ) from users u join response r on r.userid=u.id join cases c on c.id = r.caseid join komentar k on k.caseid =c.id and k.msgid=sms_msgid where not u.del group by u.phone,nama(u.id), c.id, nama(k.createdby), add_checkdigit(c.id), k.komentar, u.id | ||
LAB: Laboratory submission reportFormat pesanLAB [ID kasus] ([jenis spesimen] [bentuk spesimen] {seksi} [jumlah spesimen]...) [lab ID] Reply SQLselect format (get_string('LAB_reply',sms_userid), add_checkdigit(s.caseid), i.name, string_agg( format('%s %s %s %s', s.total, t.name[get_lang(sms_userid)], f.name[get_lang(sms_userid)], coalesce( case when get_lang(sms_userid) = 1 then 'untuk '||ls.name[1] else 'for '||ls.name[2] end, ) ), ', '), get_location(c.locationid)) from submissions s join specimentypes t on s.specimentypeid = t.id and not t.del join specimenforms f on s.specimenformid = f.id and not s.del left outer join labsections ls on s.sectionid = ls.id and not ls.del join users u on s.userid = u.id join cases c on c.id = s.caseid and not s.del join infrastructure i on s.labid = i.id where s.msgid = sms_msgid group by s.caseid, c.locationid, i.name | ||
LAPD: Laporan desaFormat pesanLAPD [kode desa] Reply SQLselect coalesce(string_agg(caselist,'; '), case when get_location(params[1]) is null then format(get_string('E_location', sms_userid),params[1]) else format(get_string('LAPD_nocases', sms_userid),get_location(params[1])) end) from ( select format('[%s] %s, %s, %s', add_checkdigit(c.id), tanggal(c.reportdate), string_agg(sp.name[get_lang(sms_userid)],', '), case when signsid is not null then get_sign_names(si.signsid,sms_userid) else sd.name[get_lang(sms_userid)] end ) as caselist from cases c join locations l on c.locationid = l.id join locations l2 on l2.code = params[1] and ischildlocation(l2.id, l.id) left outer join signreports si on si.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id left outer join species sp on sp.id = coalesce(si.speciesid, sy.speciesid) left outer join syndromes sd on sy.syndromeid = sd.id where not c.del group by c.id, signsid, sd.id order by c.id desc limit 5) as foo | ||
LAPK: Laporan kasusFormat pesanLAPK [ID kasus] Reply SQLselect string_agg(summ,'; ') from (select (rep_case).summary as summ from (select rep_case(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_signs(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_syndromes(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_response(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_treatment(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_caseresult(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_rabies(remove_checkdigit(params[1]::integer)::integer,sms_userid) union select rep_ai(remove_checkdigit(params[1]::integer)::integer,sms_userid)) as reports order by (rep_case).repdate) as reps | ||
LAPP: Laporan PenggunaFormat pesanLAPP Reply SQLselect string_agg(casesum,'; ') from ( select casesum from ( select format('%s %s %s %s %s %s', add_checkdigit(c.id), to_char(c.createdon,'DD Mon'), coalesce(si.cases, sy.cases), sp.code, coalesce(s.code,syn.code), l.name) as casesum from cases c left join signreports si on si.caseid = c.id and not si.del left join syndromereports sy on sy.caseid = c.id and not sy.del join species sp on sp.id = coalesce(si.speciesid, sy.speciesid) join locations l on l.id = c.locationid left join signs s on s.id = si.signsid[1] left join syndromes syn on syn.id = sy.syndromeid --where c.createdby = sms_userid where c.createdby = params[1] and not (si.id is null and sy.id is null) and not c.del order by c.createdon desc limit 11) foo) bar | ||
LH: LahirFormat pesanLH [ID Pemilik] [ID Hewan] [Jenis Kelamin] [Identifikasi Pedet] [tanggal_lahir] {Berat Lahir} Reply SQLselect format(get_string('LH_reply',sms_userid), initcap(get_species_name(h.idspesies,sms_userid)), h.identifikasi, nama(p.idpemilik::integer), l.name, tanggal(h2.tanggallahir), sex.name[get_lang(sms_userid)], h2.identifikasi, coalesce(k2.datakejadian,'-')) from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid left outer join hewan h2 on h2.id = k.datakejadian::integer left outer join sex on sex.id = h2.idsex left join kejadian k2 on k2.msgid = sms_msgid and k2.idtipekejadian=4 where k.msgid = sms_msgid and k.idtipekejadian=1 Business RulesHook: sms_validate; Order: 1Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan kelahiran yang sama pada hari ini.') end from ( select k.id -- get_animal_id(upper($2[1]),upper($2[2]),$1) as x from kejadian k where k.idtipekejadian=1 -- kelahiran and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal --AND k.createdon::date=now()::date -- same date AND k.datakejadian::integer = get_animal_id(upper($2[1]),upper($2[4]),$1)--same calve --AND k.createdby = $1 -- same inseminator ) as z Hook: sms_validate; Order: 2SELECT case when $2[5]::date <=now()::date then (0,null) else (1,'Maaf, tanggal tidak bisa di masa depan') end Hook: sms_postinsert; Order: 1with andata as ( insert into hewan (identifikasi, indukbetina, idspesies, idsex, tanggallahir, msgid, createdby) select upper($2[4]), h.id, h.idspesies, s.id, $2[5]::date, $3, $1 from kejadian k join hewan h on h.id = k.idhewan join sex s on s.code = upper($2[3]) where k.msgid = $3 returning id ) insert into pemilik_hewan (idpemilik, idhewan, msgid, createdby) select get_farmer_id($2[1],$1), id, $3, $1 from andata Hook: sms_postinsert; Order: 2update kejadian k set datakejadian = h.id from hewan h where k.msgid = $3 and h.msgid = $3 Hook: sms_postinsert; Order: 3insert into kejadian (reportdate, idhewan, idtipekejadian, datakejadian, msgid, createdby) select now() , h.id , 4 , $2[6] , $3 , $1 from hewan h where h.msgid = $3 and h.identifikasi=upper($2[4]) Hook: sms_postinsert; Order: 5select case when send_template_report('ID hewan', u.email, h.id, case when get_param('hostname') = 'sidecar' then 'sidecar' else 'live' end) = 'True' then (0,) else (1,'Unable to send animal ID certificate by email') end from hewan h join users u on u.id = h.createdby where h.msgid = $3 and u.email is not null and trim(u.email) <> and not h.del | ||
LINK: Link photo to caseFormat pesanLINK [ID Foto] [ID Kasus] Reply SQLselect format(get_string('LINK_reply',sms_userid), add_checkdigit(i.id), add_checkdigit(c.id), coalesce(s.cases, sy.cases), get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), coalesce(get_sign_names(s.signsid,sms_userid),get_syndrome_name(sy.syndromeid,sms_userid)), get_location(c.id), tanggal(c.reportdate)) from caseimages i join cases c on c.id = i.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where i.id = remove_checkdigit(params[1]::bigint) Business RulesHook: sms_validate; Order: 5update caseimages set caseid = remove_checkdigit(($2[2])::bigint), modifiedon = now(), modifiedby = $1 where id = remove_checkdigit(($2[1])::bigint) returning (0,) | ||
LKOM: Laporan KompartemenFormat pesanLKOM [Kode Farm] [Tanda,Tanda...] [Jumlah Hewan] [Persentase] [Diagnosa Sementara,Diagnosa Sementara...] Reply SQLselect format(get_string('lkom_reply',sms_userid), cd.id, get_sign_names(cd.signsid, sms_userid), cd.cases, get_species_name(f.idspecies, sms_userid), CONCAT(UPPER(f.name), '(', f.national_code, ')'), CONCAT('DX: ',get_disease_names(cd.diagnosisid, sms_userid)) || coalesce('. DD: ' || get_disease_name(cd.diseaseid,sms_userid),)) from data.compartment_disease_cases cd JOIN farms f ON f.id = cd.farmid where cd.msgid = sms_msgid Alert SQLwith lid as (select format(get_string('lkom_dd_alert', u.id), cd.id, nama(u.firstname, u.surname), format_phone(u.phone), get_sign_names(cd.signsid, u.id), cd.cases, CONCAT(UPPER(f.name), '(', f.national_code, ')'), coalesce('DD: ' || get_disease_names(cd.diagnosisid, sms_userid), ) ) as msg from data.compartment_disease_cases cd join users u on u.id = cd.createdby join farms f on f.id = cd.farmid where cd.msgid = sms_msgid ), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 97 ) select u.phone, lid.msg from users u left join gperm on true join lid on true where gperm.p && u.groupid Business RulesHook: sms_validate; Order: 5select case when count(f.code) = 0 then (1,get_string('E_notpermitted_farm',$1)) else case when lower($2[1]) = lower((SELECT code FROM farms f JOIN user_kompartemen uk ON uk.farm_id = f.id JOIN users u ON u.id = uk.user_id where u.id = $1 and upper(f.code) = upper($2[1]))) then (0,null) else (1,get_string('E_notpermitted_farm',$1)) end end from farms f join user_kompartemen uk on uk.farm_id = f.id join users u on u.id = uk.user_id where u.id = $1 Hook: sms_validate; Order: 10select case -- allowed to make a diagnosis when is_permitted('can_report_kompartemen_dd',$1) then case when $2[2] is null then (1,get_string('E_missingdx',$1)) else (0,null) end -- Pelsa else case when $2[2] is not null then (1,get_string('E_dxnotallowed',$1)) else (0,null) end end from users u where u.id = $1 Hook: sms_postinsert; Order: 1INSERT INTO data.compartment_disease_cases (userid, msgid, farmid, signsid, diagnosisid, cases, persentase,createdby) VALUES( $1, $3, (SELECT id FROM farms f WHERE f.code = UPPER($2[1]::varchar) and not f.del), (select array(select id from (select unnest(regexp_matches($2[2],'([a-z]+)', 'igx')) as code) as dat left outer join signs s on upper(dat.code) = upper(s.code) AND NOT s.del)), (select array(select id from (select unnest(regexp_matches($2[5],'([a-z]+)', 'igx')) as code) as dat left outer join diseases d on upper(dat.code) = upper(d.code) AND NOT d.del)), $2[3]::integer, $2[4]::integer,$1) | ||
LTL: Laporan tindak lanjutFormat pesanLTL [ID kasus] ([spesies] [jumlah sakit] [jumlah mati] [jumlah dimusnahkan/potong bersyarat] [jumlah berisiko]...) {selesai} Reply SQLselect format (get_string('LTL_reply',sms_userid),l1.name, add_checkdigit(i.caseid), string_agg(format ('%s: %s '|| get_string('LTL_sick',sms_userid)||', %s '|| get_string('LTL_dead',sms_userid)||', %s '|| get_string('LTL_culled',sms_userid)||', %s '|| get_string('LTL_atrisk',sms_userid), s.name[get_lang(sms_userid)], i.sick, i.dead, i.slaughtered, i.atrisk),'; '), case when i.resolved then get_string('LTL_resolved',3) else get_string('LTL_ongoing',3) end) from investigationanimals i join cases c on c.id = i.caseid join species s on s.id = i. speciesid join locations l1 on l1.id = c.locationid where i.msgid = sms_msgid group by l1.name, i.caseid, i.resolved | ||
NIK: NIKFormat pesanNIK [ID Pengguna] [no ktp] Reply SQLselect format(get_string('nik_reply',sms_userid), nik,nama(id),phone) from users u where u.phone = int_phone(params[1]) Business RulesHook: sms_validate; Order: 1select case when length($2[2]) = 16 then (0,null) else (1,'maaf,no ktp harus 16 digit!') end Hook: sms_validate; Order: 2select case when count(id)=0 then (0, null) else (1,'Maaf, nomor nik/ktp sudah terdaftar.') end from ( select id from users u where nik = $2[2] ) as nik Hook: sms_validate; Order: 3select case when count(id)=1 then (0, null) else (1,'Maaf, pengguna tidak ada/di luar area tanggungjawab anda.') end from ( select id from users u where u.phone = int_phone($2[1]) and u.locationid in ( select l.id from locations l join users u on u.id = $1 where not l.del and ischildlocation(u.area,l.id) ) ) as u Hook: sms_postinsert; Order: 1update users u set nik = $2[2] where u.phone=int_phone($2[1]) and not u.del | ||
OB: Laporan pengobatanFormat pesanOB [ID kasus] ([kode obat] [dosis per ekor] [jumlah hewan]...) Reply SQLselect format(get_string('OB_reply',sms_userid), dinfo, s.name[get_lang(sms_userid)], l.name) from ( select t.caseid, string_agg(format('%s (%s %s, %s ekor)', case when length(trim(d.name))<11 then d.name else substring(trim(d.name) from '(.{1,10}[^s]*)')|| case when length(trim(d.name))>length(substring(trim(d.name) from '(.{1,10}[^s]*)')) then '...' else end end, dose, doseunits, animals ), ', ') as dinfo from treatments t join treatmentanimals a on t.id = a.treatmentid join drugs d on d.id = a.drugid where t.msgid = sms_msgid group by t.caseid) as dd left join cases c on c.id = dd.caseid left join locations l on l.id = c.locationid left outer join signreports sr on sr.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id left outer join species s on s.id = case when sr.speciesid is null then sy.speciesid else sr.speciesid end Alert SQLselect u.phone, format('Telah dilakukan pengobatan kasus %s di %s oleh %s pada %s ekor %s (%s) dengan %s', add_checkdigit(c.id), get_location(c.locationid), nama(t.userid), a.animals, get_species_name(s.speciesid,u.id), get_sign_names(s.signsid,u.id), string_agg(format('%s %s %s', a.dose, d.doseunits, d.name ),' dan ') ) from users u join treatments t on t.msgid = sms_msgid join cases c on c.id = t.caseid join treatmentanimals a on a.treatmentid = t.id join drugs d on d.id = a.drugid join signreports s on s.caseid = c.id where is_permitted('get_ob_alert',u.id) and ischildlocation(area,c.locationid) and not u.del group by u.phone, c.id, c.locationid, t.userid, a.animals, s.speciesid, u.id, s.signsid Business RulesHook: sms_validate; Order: 10WITH split_string AS ( SELECT unnest(string_to_array($2[2], ' ')) AS element, generate_series(1, array_length(string_to_array($2[2], ' '), 1)) AS position ) SELECT CASE WHEN sum((select count(*) FROM drugs WHERE code IN (upper(element)) AND reg_institution NOT IN('KEMENTAN', 'BPOM') AND del IS FALSE )) > 0 THEN (1, get_string('E_drug',$1)) ELSE (0,null) END FROM split_string WHERE (position - 1) % 3 = 0; | ||
OBI: Laporan Pengobatan IndividuFormat pesanOBI [Id Kasus] ([kode obat] [dosis]...) [urutan hewan] Reply SQLselect format(get_string('OBI_reply',sms_userid), dinfo, s.name[get_lang(sms_userid)], string_agg(anid, ', '), l.name) from ( select distinct t.caseid, h.identifikasi as anid, string_agg(format('%s (%s %s)', case when length(trim(d.name))<11 then d.name else substring(trim(d.name) from '(.{1,10}[^s]*)')|| case when length(trim(d.name))>length(substring(trim(d.name) from '(.{1,10}[^s]*)')) then '...' else end end, dose, doseunits ), ', ') as dinfo from treatments t join treatmentanimals a on t.id = a.treatmentid join drugs d on d.id = a.drugid join hewan h on h.id = any(a.animalid) where t.msgid = sms_msgid group by t.caseid, h.identifikasi) as dd left join cases c on c.id = dd.caseid left join locations l on l.id = c.locationid left outer join signreports sr on sr.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id left outer join species s on s.id = case when sr.speciesid is null then sy.speciesid else sr.speciesid end group by dinfo, s.name, l.name Business RulesHook: sms_validate; Order: 10Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan OBI pada hewan yang sama hari ini.') end from ( with animal as ( select idhewan, row_number() Over(order by id asc) as r_n from case_animals a where a.idcase =remove_checkdigit($2[1]::integer)) select ta.id from treatmentanimals ta JOIN drugs d ON d.id=ta.drugid and NOT d.del JOIN hewan h on h.id = any(ta.animalid) and not h.del JOIN pemilik_hewan ph ON ph.idhewan=any(ta.animalid) and NOT ph.del JOIN users u ON u.id=ph.idpemilik and NOT u.del JOIN animal on animal.r_n in ($2[3]::integer) where upper(d.code) in (select upper(dnames[1]) from (select regexp_matches($2[2],'([a-z0-9]+)\s+(\d+[,.]{0,1}\d*)', 'igx') as dnames) t1) and animal.idhewan=any(ta.animalid) and ta.reportdate::date=now()::date and ta.createdby=$1 and not ta.del ) as z Hook: sms_validate; Order: 15WITH split_string AS ( SELECT unnest(string_to_array($2[2], ' ')) AS element, generate_series(1, array_length(string_to_array($2[2], ' '), 1)) AS position ) SELECT CASE WHEN sum((select count(*) FROM drugs WHERE code IN (upper(element)) AND reg_institution NOT IN('KEMENTAN', 'BPOM') AND del IS FALSE )) > 0 THEN (1, get_string('E_drug',$1)) ELSE (0,null) END FROM split_string WHERE (position - 1) % 2 = 0; | ||
P: Laporan prioritasFormat pesanP [sindrom] [spesies] [jumlah hewan] {lokasi} {diagnosa,diagnosa...} Reply SQLselect format(get_string('P_posrep',sms_userid), add_checkdigit(c.id) ,s.cases, sy.name[get_lang(sms_userid)], sp.name[get_lang(sms_userid)], get_location(c.locationid)) ||coalesce('. Ddx: '||get_disease_names(r.diagnosisid, sms_userid),) from cases c join syndromereports s on caseid = c.id join syndromes sy on sy.id = s.syndromeid join species sp on sp.id = s.speciesid left outer join response r on r.caseid = c.id where c.msgid = sms_msgid Alert SQLselect u.phone, format(get_string('P_alert',u.id), add_checkdigit(s.caseid), nama(u2.firstname, u2.surname), format_phone(u2.phone), s.cases, d.name[get_lang(u.id)], get_species_name(s.speciesid,u.id), get_location(c.locationid), coalesce('. DDx: '||get_disease_names(r.diagnosisid,2),) ) from users u join cases c on c.msgid = sms_msgid join syndromereports s on c.id = s.caseid join syndromes sy on sy.id = s.syndromeid join diseases d on d.id = sy.targetdiseaseid join users u2 on u2.id = c.userid left outer join response r on r.caseid = c.id join locations l on c.locationid = l.id where is_permitted('get_pelsa_alert_p',u.id) and u.id in ( select distinct uid from ( select id as uid, unnest(area) as area from users ) as foo where is_parent_area(c.locationid, foo.area) and u.id <> sms_userid ) Business RulesHook: sms_preparse; Order: 10select case when spcode is null then (0,null) else (1, case spcode::integer when 1 then format(get_string('U_speccodechange',$1),'AY',spcode) when 2 then format(get_string('U_speccodechange',$1),'SP',spcode) when 3 then format(get_string('U_speccodechange',$1),'KR',spcode) when 4 then format(get_string('U_speccodechange',$1),'AJ',spcode) when 5 then format(get_string('U_speccodechange',$1),'KB',spcode) when 6 then format(get_string('U_speccodechange',$1),'KL',spcode) when 7 then format(get_string('U_speccodechange',$1),'KD',spcode) when 9 then format(get_string('U_speccodechange',$1),'DB',spcode) when 10 then format(get_string('U_speccodechange',$1),'BB',spcode) when 11 then format(get_string('U_speccodechange',$1),'KE',spcode) when 12 then format(get_string('U_speccodechange',$1),'IT',spcode) when 13 then format(get_string('U_speccodechange',$1),'IK',spcode) when 14 then format(get_string('U_speccodechange',$1),'KC',spcode) when 16 then format(get_string('U_speccodechange',$1),'UL',spcode) else format(get_string('E_invspec',$1),spcode) end) end from ( select (regexp_matches($2[1], '^P [;#\s]+ [a-z]+ [;#\s]+ (\d+) [;#\s]* \d+ (?:(?:[;#\s]*)\d{2,8}(?:\.\d{1,})?)? [;#\s]* (?:(?:[;#\s]*)[a-z]+(?:\s*,\s*[a-z]+)*)? [;#\s]* $', 'igx'))[1] as spcode union select null order by spcode limit 1 ) as foo Hook: sms_validate; Order: 7Select case when count(caseid)=0 then (0, null) else (1,'Maaf, Sudah ada laporan sindrom yang sama untuk hewan, jumlah hewan dan desa yang sama pada hari ini.') end from ( select s.caseid from syndromereports s join reference.syndromes s2 on s.syndromeid = s2.id and not s2.del join reference.species s3 on s3.id = s.speciesid and not s3.del join cases c on c.id = s.caseid and not c.del join reference.locations l on l.id = c.locationid and l.validto is null and not l.del where upper(s2.code) = upper($2[1]) and upper(s3.code) = upper($2[2]) and s.cases = $2[3]::integer and l.code = $2[4] and c.reportdate::date = current_date and c.userid =$1 and not s.del ) as z Hook: sms_validate; Order: 10select case -- allowed to make a diagnosis when is_permitted('can_send_r',$1) then case when $2[5] is null then (1,get_string('E_missingdx',$1)) else (0,null) end -- Pelsa else case when $2[5] is not null then (1,get_string('E_dxnotallowed',$1)) else (0,null) end end from users u where u.id = $1 Hook: sms_validate; Order: 20select case when $2[4] is not null then case when length($2[4]) < 7 then (1,get_string('E_notdesacode',$1)) else (0,null) end else (0,null) end | ||
PG: Injeksi Prostaglandin (sinkronisasi)Format pesanPG [ID Pemilik] ([ID Hewan]...) Reply SQLselect format(get_string('PG_reply', sms_userid), string_agg(hewan,'; '), pemilik, lokasi) from ( select format ('%s (%s)', get_species_name(h.idspesies, sms_userid), string_agg(h.identifikasi,',')) as hewan, nama(p.idpemilik::integer) as pemilik, l.name as lokasi from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid where k.msgid = sms_msgid group by p.idpemilik, l.name, h.idspesies) as foo group by pemilik, lokasi Business RulesHook: sms_validate; Order: 10Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan injeksi prostaglandin yang sama pada hari ini.') end from ( select k.id -- get_animal_id(upper($2[1]),upper($2[2]),$1) as x from kejadian k where k.idtipekejadian=8 -- Injeksi Prostaglandin and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal AND k.createdon::date=now()::date -- same date AND k.createdby = $1 -- same inseminator ) as z | ||
PK: Perkembangan kasusFormat pesanPK [ID kasus] [kode perkembangan kasus] Reply SQLselect format(get_string('PK_response', sms_userid), add_checkdigit(r.caseid), coalesce(s.cases,sy.cases), get_species_name(coalesce(s.speciesid, sy.speciesid), sms_userid), get_location(c.locationid), t.name[get_lang(sms_userid)] ) from caseresults r join caseresulttypes t on t.id = r.result join cases c on c.id = r.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where r.msgid = sms_msgid | ||
PKB: Pemeriksaan kebuntinganFormat pesanPKB [ID Pemilik] [ID hewan] [Bulan] Reply SQLselect format(get_string('PKB_reply', sms_userid), get_species_name(h.idspesies,sms_userid), h.identifikasi, sex.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name, case when datakejadian = '0' then get_string('PKB_tidakbunting', sms_userid) else datakejadian || ' ' || get_string('PKB_bunting', sms_userid) end) from kejadian k join hewan h on k.idhewan = h.id join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null join sex on sex.id = h.idsex join users u on u.id = p.idpemilik join locations l on l.id = u.locationid --join tipekejadian t on t.id = k.datakejadian where k.msgid = sms_msgid Business RulesHook: sms_validate; Order: 1Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan PKB yang sama pada hari ini.') end from ( select k.id -- get_animal_id(upper($2[1]),upper($2[2]),$1) as x from kejadian k where k.idtipekejadian=3 -- insemination and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal AND k.createdon::date=now()::date -- same date AND k.createdby = $1 -- same inseminator ) as z | ||
PKHI: Perkembangan kasus hewan individualFormat pesanPKHI [ID kasus] [kode perkembangan kasus] [ID Pemilik] [ID Hewan] Reply SQLselect format(get_string('PKHI_response', sms_userid), add_checkdigit(r.caseid) ,string_agg(format('%s %s %s' , h.identifikasi , get_species_name(coalesce(s.speciesid, sy.speciesid), sms_userid) , t.name[get_lang(sms_userid)]),',' ) , get_location(c.locationid)) from caseresults r join caseresulttypes t on t.id = r.result join cases c on c.id = r.caseid left outer join signreports s on s.caseid = c.id join hewan h on h.id = s.animalid join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null join users u on u.id = p.idpemilik left outer join syndromereports sy on sy.caseid = c.id where r.msgid = sms_msgid group by r.caseid, c.locationid | ||
PKL: Perkembangan kasus lengkapFormat pesanPKL [ID kasus] ([kode perkembangan kasus] [jumlah hewan]...) Reply SQLselect format(get_string('PKL_response', sms_userid), add_checkdigit(r.caseid) ,string_agg(format('%s %s %s' ,r.jumlah , get_species_name(coalesce(s.speciesid, sy.speciesid), sms_userid) , t.name[get_lang(sms_userid)]),',' ) , get_location(c.locationid)) from caseresults r join caseresulttypes t on t.id = r.result join cases c on c.id = r.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where r.msgid =sms_msgid group by r.caseid, c.locationid | ||
PL: Penanganan LainFormat pesanPL [ID Pemilik] [ID Hewan] [tipepenanganan] Reply SQLselect format(get_string('PL_reply', sms_userid), h.identifikasi, get_species_name(h.idspesies,sms_userid), s.name[get_lang(sms_userid)], nama(get_farmer_id(params[1])), t.name[get_lang(sms_userid)]) from kejadian k join hewan h on h.id = k.idhewan and not h.del join sex s on s.id = h.idsex and not s.del join tipepenanganan t on t.id = k.datakejadian::integer and not t.del where k.msgid = sms_msgid Business RulesHook: sms_validate; Order: 10with penanganan as ( select id from tipepenanganan where upper(code) = upper($2[3])) Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan penanganan lain yang sama pada hari ini.') end from ( select k.id from kejadian k join penanganan on penanganan.id =k.datakejadian::integer where k.idtipekejadian=8 and not k.del AND k.idhewan=get_animal_id(upper($2[1]),upper($2[2]),$1) -- same animal AND k.createdon::date=now()::date -- same date AND k.createdby = $1 -- same inseminator ) as z | ||
PNC: Penerimaan Nitrogen CairFormat pesanPNC [ID pengiriman] [Jumlah liter] Reply SQLselect format(get_string('PNC_reply', sms_userid), ns.volume, ns2.volume, get_location(i.locationid), to_char(s.datesent, 'DD/MM/YYYY') ) from receipt r join nitrogenshipment ns on ns.receiptid = r.id join shipments s on s.id = r.shipmentid join infrastructure i on i.id = s.origininfraid join nitrogenshipment ns2 on ns2.shipmentid = s.id and ns2.receiptid is null where r.msgid = sms_msgid Business RulesHook: sms_validate; Order: 2select case when $2[2]::integer > ns.volume::integer then (1,'Volume salah. Volume yang diterima lebih besar daripada yang dikirim') else (0,) end as result from shipments s join nitrogenshipment ns on ns.shipmentid = s.id and ns.receiptid is null and s.id = remove_checkdigit($2[1]::integer) Hook: sms_postinsert; Order: 1update receipt set shipmentid = nitrogenshipment.shipmentid from nitrogenshipment where receipt.id = receiptid and receipt.msgid = $3 returning (0,) | ||
PNEG: Laporan negatifFormat pesanPNEG Reply SQLselect format(get_string('PNEG_reply', sms_userid), get_location(u.locationid), case checked when true then get_string('PNEG_active',sms_userid) when false then get_string('PNEG_passive',sms_userid) else end) from negativereports n join users u on u.id = n.userid where n.msgid = sms_msgid | ||
POP: Laporan populasiFormat pesanPOP ([jenis hewan] [jumlah hewan]...) {lokasi} Reply SQLselect format(get_string('POP_reply',sms_userid), get_location(l.id), string_agg(format('%s: %s',t.name[get_lang(sms_userid)], p.total),'; ')) from population p join animal_types t on t.id = p.animaltypeid join locations l on l.id = p.locationid where p.msgid =sms_msgid group by l.id Business RulesHook: sms_validate; Order: 10select case when ((get_user_groups($1, 251)) like '%tim vaksinasi%') then (1,get_string('E_ketuavaksin',$1)) else case when $2[2] is not null then case when length($2[2]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end else (0,null) end end | ||
PS: Produksi SusuFormat pesanPS [ID Pemilik] [ID hewan] [Liter] Reply SQLselect format(get_string('PS_reply',sms_userid), get_species_name(h.idspesies,sms_userid), h.identifikasi, sex.name[get_lang(sms_userid)], nama(p.idpemilik::integer), l.name, k.datakejadian) from kejadian k left outer join hewan h on k.idhewan = h.id left outer join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null left outer join sex on sex.id = h.idsex left outer join users u on u.id = p.idpemilik left outer join locations l on l.id = u.locationid where k.msgid = sms_msgid | ||
PSB: Penerimaan Semen BekuFormat pesanPSB [ID pengiriman] ([Bangsa hewan] [Jumlah straw]...) Reply SQLselect format (get_string('PSB_reply', sms_userid), string_agg( format ('%s (%s/%s)', sp.name[get_lang(sms_userid)], coalesce(ns.total,0), coalesce(ns2.total,0)), ', '), get_location(i.locationid), to_char(s.datesent, 'DD/MM/YYYY')) from receipt r join strawshipment ns on ns.receiptid = r.id join shipments s on s.id = r.shipmentid join infrastructure i on i.id = s.origininfraid join strawshipment ns2 on ns2.shipmentid = s.id and ns2.breedid = ns.breedid and ns2.receiptid is null left outer join species sp on sp.id = ns.breedid where r.msgid = sms_msgid group by i.locationid, s.datesent Business RulesHook: sms_validate; Order: 0select case when verify_checksum($2[1]::bigint) then case when (select count(*)=1 from shipments where id = remove_checkdigit($2[1]::bigint) and not del) then (0,null) else (1,format(get_string('PSB_shipiderror', $1), $2[1])) end else (1,format(get_string('PSB_checksumerror', $1), $2[1])) end Hook: sms_validate; Order: 1select case when count(*) = 0 then (0,null) else (1,format('Jumlah straw tidak valid.jumlah diterima lebih besar daripada jumlah dikirim: %s', string_agg( format ('%s (%s/%s)', s.name[1], coalesce(col[2],'0'), coalesce(ss.total,'0')), ', '))) end from ( select regexp_matches($2[2],'([a-z]+)[;,#\s]*(-?\d+\.?\d*)', 'igx') ) as foo(col) left join species s on s.code = upper(col[1]) left join strawshipment ss on ss.shipmentid = remove_checkdigit($2[1]::integer) and ss.breedid = s.id and ss.receiptid is null where col[2]::integer > coalesce(ss.total,0) Hook: sms_postinsert; Order: 1update receipt set shipmentid = strawshipment.shipmentid from strawshipment where receipt.id = receiptid and receipt.msgid = $3 returning (0,) | ||
Q: PertanyaanFormat pesanQ [petanyaan] Reply SQLselect get_string('Q_response',sms_userid) Alert SQL-- new version with lid as ( select l.centroid as centroid, nama(u.firstname, u.surname) as nama, local_phone(u.phone) as phone, to_char(reportdate,'HH:MM:SS') as rtime, question from questions q join users u on u.id = q.userid join locations l on l.id = u.locationid where q.msgid = sms_msgid ), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 4 and permission = 1 ) select u.phone, format(get_string('Q_alert',sms_userid), lid.nama, lid.phone, lid.rtime, lid.question ) from users u left join gperm on true left join user_permissions up on up.userid = u.id and up.permission_typeid = 4 join locations l on array[l.id] && u.area join lid on true -- reporter location where (gperm.p && u.groupid AND coalesce(up.permission,1) = 1) --where (gperm.p && u.groupid OR up.permission = 1) and st_contains(l.geom,lid.centroid) /* -- old version - Extremely slow!! select u.phone, format(get_string('Q_alert',2), nama(u2.firstname,u2.surname), local_phone(u2.phone), to_char(reportdate,'HH:MM:SS'), question ) from users u left outer join questions q on q.msgid = sms_msgid join users u2 on u2.id = q.userid where is_permitted('get_questions',u.id) and u.id in ( select distinct uid from ( select id as uid, unnest(area) as area from users ) as foo where is_parent_area(u2.locationid, foo.area) and u.id <> sms_userid ) */ | ||
R: Response reportFormat pesanR [ID Kasus] [dikunjung (K/T)] [diagnosa banding,diagnosa banding...] {diagnosa lain} Reply SQLselect format(get_string('R_conf',sms_userid), add_checkdigit(c.id), get_location(c.locationid), to_char(c.reportdate,'DD/MM/YYYY'), get_disease_names(diagnosisid,sms_userid)||coalesce(', '||otherdiagnosis, ) ) from cases c join response r on r.caseid = c.id where r.msgid = sms_msgid Alert SQLwith lid as ( select l.centroid as centroid, format(get_string('R_alert',u.id), add_checkdigit(c.id), nama(u.firstname, u.surname), format_phone(u.phone), get_location(c.locationid), coalesce(si.cases, sy.cases), get_disease_names(diagnosisid,u.id)||coalesce(', '||otherdiagnosis, ) ) as msg from response r join cases c on c.id = r.caseid join locations l on l.id = c.locationid join users u on u.id = c.createdby left join signreports si on si.caseid = c.id left join syndromereports sy on sy.caseid = c.id where r.msgid = sms_msgid ), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 5 and permission = 1 ) select u.phone, lid.msg from users u left join gperm on true left join user_permissions up on up.userid = u.id and up.permission_typeid = 5 join locations l on array[l.id] && u.area join lid on true -- reporter location where (coalesce(up.permission=1,gperm.p && u.groupid)) and st_contains(l.geom,lid.centroid) /* select u.phone, format(get_string('R_alert',u.id), add_checkdigit(c.id), nama(u2.firstname, u2.surname), format_phone(u2.phone), get_location(c.locationid), coalesce(sr.cases, sy.cases), get_disease_names(diagnosisid,u.id)||coalesce(', '||otherdiagnosis, ) ) from users u join response r on r.msgid = sms_msgid join cases c on c.id = r.caseid left outer join signreports sr on sr.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id left outer join syndromes syn on sy.syndromeid = syn.id join users u2 on u2.id = r.userid where is_permitted('get_response_conf',u.id) and u.id in ( select distinct uid from ( select id as uid, unnest(area) as area from users ) as foo where is_parent_area(c.locationid, foo.area) and u.id <> sms_userid ) */ Business RulesHook: sms_validate; Order: 10select (0, case when (select max( case when similarity(dz,d.name[1]) > similarity(dz,d.name[2]) then similarity(dz,d.name[1]) else similarity(dz,d.name[2]) end) from ( select regexp_split_to_table($2[4],',') as dz) as foo join diseases d on dz % d.name[1] or dz % d.name[2]) > 0.5 then (select format('[Code suggestions] %s',string_agg(format('%s %s',d.code,d.name[1]),'; ')) from diseases d join ( select regexp_split_to_table($2[4],',') as dz) as foo on similarity(dz, d.name[1])>0.5 or similarity(dz, d.name[2])>0.5) else null end); | ||
RP: Statistic rumah potongFormat pesanRP ([jenis hewan] [jumlah dipotong]...) {kode RP} Reply SQLselect format(get_string('RP_totalsrep',sms_userid), i.name, to_char(s.reportdate,'dd/mm/YYYY'), string_agg(format('%s: %s ekor', t.name[get_lang(sms_userid)],total), '; ')) from slaughtertotals s join animal_types t on t.id = s.animaltypeid and not t.del join infrastructure i on s.infrastructureid = i.id and not i.del where msgid = sms_msgid group by i.name,s.reportdate | ||
RVAK: Riwayat vaksinasiFormat pesanRVAK [ID kasus] ([kode penyakit] [tanggal vaksinasi]...) Reply SQLselect format(get_string('RVAK_reply',sms_userid), get_species_name(coalesce(si.speciesid, sy.speciesid),sms_userid), coalesce(case when trim(get_sign_names(si.signsid,sms_userid))= then null else get_sign_names(si.signsid,sms_userid) end, get_syndrome_name(sy.syndromeid,sms_userid)), get_location(c.locationid), foo.result ) from ( select min(caseid) as caseid, string_agg(format('%s %s', get_disease_name(diseaseid,2), to_char(tanggal, 'mm/yyyy')), ', ') as result from riwayatvaksinasi where msgid = sms_msgid) as foo join cases c on c.id = foo.caseid left outer join signreports si on si.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id | ||
SAP: SapihFormat pesanSAP [ID Pemilik] [ID Hewan] Reply SQLselect format(get_string('SAP_reply',sms_userid), initcap(get_species_name(h.idspesies,sms_userid)), h.identifikasi, nama(p.idpemilik::integer), l.name) from kejadian k join hewan h on k.idhewan = h.id and not h.del join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null and not p.del join users u on u.id = p.idpemilik and not u.del join locations l on l.id = u.locationid where k.msgid = sms_msgid | ||
SCS: Stok Cold StorageFormat pesanSCS ([Jenis Produk] [Jumlah Masuk (Ton)] [Jumlah keluar (ton)] [jumlah stok (ton)]...) [kode cold storage] Reply SQLselect format(get_string('SCS_reply', 35) ,case when cs is null or trim(cs) = then get_string('SSB_office',35) else cs end ,string_agg(format('Produk %s, Jumlah masuk %s ton, Jumlah keluar %s ton, stok %s ton', product, stockin, stockout, endstock), ', ') ) from ( select i.name as cs,sp.name[1] as product, stockin, stockout,endstock from coldstorage_stock cs join infrastructure i on i.id = cs.idcs join species sp on sp.id = cs.producttypes where cs.msgid = sms_msgid ) as aa group by cs | ||
SHP: Stok hijauan pakanFormat pesanSHP ([jenis hijauan] [jumlah] [satuan] [harga]...) [produsen] Reply SQLselect format(get_string('SBH_reply',sms_userid) ,case when infra is null or trim(infra) = then get_string('SSB_office',sms_userid) else infra end ,string_agg(format('%s(%s %s)@ %s rupiah', jh,total,jb,harga), ', ') ) from ( select i.name as infra,jh.name[1] as jh ,jb.name[1] as jb , harga, total from stokbibit s join infrastructure i on i.id = s.asalid join jenisbibit jb on jb.id = s.jenisbibitid join jenishijauan jh on jh.id = s.jenishijauanid where s.msgid = sms_msgid ) as aa group by infra | ||
SKO: Stok KontainerFormat pesanSKO ([Jenis Kontainer] [Kondisi Kontainer] [Jumlah]...) Reply SQLselect format(get_string('SKO_reply',sms_userid) ,case when infra is null or trim(infra) = then get_string('SSB_office',sms_userid) else infra end ,string_agg(format('%s-%s(%s unit)', jk,kk,total), ', ') ) from (select i.name as infra,jk.name[1] as jk ,kk.name[1] as kk , cs.total from stocktake s join containershipment cs on cs.stocktakeid = s.id join infrastructure i on i.id = s.infrastructureid join jeniskontainer jk on jk.id = cs.jeniskontainerid join kondisikontainer kk on kk.id = cs.kondisikontainerid where s.msgid = sms_msgid) as aa group by infra | ||
SKP: Stok Pakan KonsentratFormat pesanSKP ([Jenis Konsentrat] [jumlah stok konsentrat (Kg)] [sumberanggaran]...) [produsen] Reply SQLselect format(get_string('SKP_reply', sms_userid) ,case when infra is null or trim(infra) = then get_string('SSB_office',sms_userid) else infra end ,string_agg(format('%s:%s kg', jk,jumlah), ', ') ) from ( select i.name as infra,jk.name[1] as jk ,jumlah from konsentrat k join infrastructure i on i.id = k.infraid join jeniskonsentrat jk on jk.id = k.idjeniskonsentrat where k.msgid = sms_msgid ) as aa group by infra | ||
SKSR: SKSRFormat pesanSKSR [ID Pemilik] [ID Hewan] Reply SQLwith bcs as (select coalesce(bcs.datakejadian,'tidak dilaporkan') as val from hewan h join pemilik_hewan ph on ph.idhewan=h.id and not ph.del and ph.tanggalakhir is null join users u on u.id=ph.idpemilik and not u.del and local_phone(u.phone) ~ (params[1]||'$') left join kejadian bcs on bcs.idtipekejadian=15 and h.id=bcs.idhewan and not bcs.del where upper(h.identifikasi) = upper(params[2]) and not h.del order by bcs.reportdate desc limit 1) select format(get_string('SKSR_reply', sms_userid) , add_checkdigit(k.id) , local_phone(u.phone) , h.identifikasi , sr.name[get_lang(sms_userid)] , bcs.val) from kejadian k join statusrepro sr on k.datakejadian::integer=sr.id and not sr.del join hewan h on h.id = k.idhewan and not h.del join pemilik_hewan ph on ph.idhewan=h.id and not ph.del join users u on u.id=ph.idpemilik and not u.del join bcs on true where k.msgid = sms_msgid Business RulesHook: sms_postinsert; Order: 10select case when send_template_report('SKSR', u.email, add_checkdigit(k.id)::int, case when get_param('hostname') = 'dbmaster' then 'live' else 'sidecar' end) = 'True' then (0,) else (1,'Maaf, Kartu SKSR tidak dapat dikirimkan') end from kejadian k join users u on u.id = k.createdby where k.msgid = $3 and u.email is not null and trim(u.email) <> and not k.del | ||
SLAB: Surveilans aktif (laboratorium)Format pesanSLAB [ID program] ([species] [jumlah hewan]...) [lokasi] {ID laboratorium} Reply SQLselect format(get_string('SUR_reply', sms_userid), p.name, l.name, string_agg(format(get_string('SUR_totals',sms_userid), sp.name[get_lang(sms_userid)], s.specimens),'; '), coalesce(' ('||i.name||')',)) from surveillance s join surveillanceprograms p on s.programid = p.id join species sp on s.speciesid = sp.id join locations l on s.locationid = l.id left outer join infrastructure i on i.id = s.labid where msgid = sms_msgid group by p.name, l.name, i.name Business RulesHook: sms_validate; Order: 10select case when $2[3] is not null then case when length($2[3]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end else (0,null) end | ||
SLAP: Surveilans (lapangan)Format pesanSLAP [ID program] ([spesies] [jumlah positif] [jumlah negatif]...) [lokasi] Reply SQLselect format( get_string('SLAP_reply',sms_userid), foo.gresult, get_location(foo.locationid), p.name) from ( select min(locationid) as locationid, min(programid) as programid, string_agg( format('%s (%s pos, %s neg)', get_species_name(speciesid, sms_userid), pos, neg), ', ') as gresult from surveillance where msgid = sms_msgid) as foo join surveillanceprograms p on p.id = foo.programid Business RulesHook: sms_validate; Order: 10select case when $2[3] is not null then case when length($2[3]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end else (0,null) end | ||
SNC: Stok Nitrogen CairFormat pesanSNC [Jumlah liter] Reply SQLselect format(get_string('SNC_reply',sms_userid), ns.volume, case when i.shortname is null or trim(i.shortname) = then get_string('SNC_office',sms_userid) else i.shortname end, get_location(i.locationid) ) from stocktake s join nitrogenshipment ns on ns.stocktakeid = s.id join infrastructure i on i.id = s.infrastructureid where s.msgid = sms_msgid | ||
SP: Sumber penyakitFormat pesanSP [ID kasus] [kode sumber] {lokasi} Reply SQLselect format(get_string('SP_reply', sms_userid), sum.name[get_lang(sms_userid)], coalesce(format(' (dari %s)',get_location(sp.locationid)),), get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), get_location(c.locationid), tanggal(c.reportdate)) from sumberpenyakit sp join cases c on c.id = sp.caseid join sumber sum on sum.id = sp.sumberid and not sum.del left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where sp.msgid = sms_msgid | ||
SPK: Surveilans Pengawas KesmavetFormat pesanSPK [tanda,tanda...] [Produk] [uji kit] [jumlah positif] [jumlah negatif] [kode pasar] Reply SQLselect 'terimakasih' | ||
SSB: Stok Semen BekuFormat pesanSSB ([Bangsa] [Jumlah straw]...) Reply SQLselect format(get_string('SSB_reply',sms_userid), string_agg(format('%s (%s)', sp.name[get_lang(sms_userid)], ns.total), ', '), case when i.shortname is null or trim(i.shortname) = then get_string('SSB_office',sms_userid) else i.shortname end, get_location(i.locationid) ) from stocktake s join strawshipment ns on ns.stocktakeid = s.id join infrastructure i on i.id = s.infrastructureid join species sp on sp.id = ns.breedid where s.msgid = sms_msgid group by i.shortname, i.locationid | ||
ST: Pendaftaran StrawFormat pesanST [ID Pejantan] [ID Pembuatan] [Spesies] [Kode BIB] Reply SQLSelect 'Terimakasih' | ||
TK: Tanda klinisFormat pesanTK [ID kasus] [tanda,tanda...] Reply SQLselect format(get_string('TK_reply',sms_userid), get_sign_names(t.signsid, sms_userid), get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), get_location(c.locationid), tanggal(c.reportdate)) from tandaklinis t join cases c on c.id = t.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where t.msgid = sms_msgid | ||
TL: Tindakan LainFormat pesanTL [ID program] ([kode jenis tindakan] [jumlah]...) [lokasi] Reply SQLselect format(get_string('TL_reply',sms_userid), get_location(foo.locationid), foo.result, pk.name, get_disease_names(pk.diseaseid,sms_userid)) from ( select min(locationid) as locationid , min(programtindakanid) as programid, string_agg(format('%s (%s %s)',t.name[get_lang(sms_userid)], tl.jumlah, t.units[get_lang(sms_userid)]), ', ') as result from tindakanlain tl join tindakan t on t.id = tl.tindakanid where msgid = sms_msgid) as foo join programtindakan pk on pk.id = foo.programid | ||
TP: Tindakan Penolakan PemotonganFormat pesanTP ([Jenis Hewan] [Alasan Penolakan] [Jumlah ditolak]...) {Kode RP} Reply SQLselect format(get_string('TPP_totalsrep',sms_userid), i.name, to_char(p.reportdate,'dd/mm/YYYY'), string_agg(format('%s %s : %s ekor', t.name[get_lang(sms_userid)],ap.name[1],total), '; ')) from penolakan p join animal_types t on t.id = p.animaltypeid and not t.del join infrastructure i on p.infrastructureid = i.id and not i.del join alasanpenolakan ap on ap.id = p.alasanpenolakanid where msgid = sms_msgid group by i.name,p.reportdate | ||
U: Laporan tanda umumFormat pesanU [tanda,tanda...] [spesies] [jumlah hewan] {lokasi} {diagnosa banding,diagnosa banding...} Reply SQLselect format(get_string('U_posrep',sms_userid), add_checkdigit(c.id), cases, get_sign_names(signsid,c.userid), get_species_name(speciesid,c.userid), get_location(c.locationid)) || coalesce('. Ddx: ' || get_disease_names(r.diagnosisid,sms_userid),) from cases c join signreports s on s.caseid = c.id left outer join response r on r.caseid = c.id where c.msgid = sms_msgid Alert SQLselect u.phone, format(get_string('U_alert',u.id), add_checkdigit(s.caseid), nama(u2.firstname, u2.surname), format_phone(u2.phone), s.cases, get_sign_names(s.signsid, u.id), get_species_name(s.speciesid,u.id), get_location(c.locationid), coalesce('. DDx: '||get_disease_names(r.diagnosisid,2),) ) from users u join cases c on c.msgid = sms_msgid join signreports s on c.id = s.caseid join users u2 on u2.id = c.userid left outer join response r on r.caseid = c.id -- join locations l on c.locationid = l.id where is_permitted('get_pelsa_alert_u',u.id) and u.id in ( select distinct uid from ( select id as uid, unnest(area) as area from users ) as foo where is_parent_area(c.locationid, foo.area) and u.id <> sms_userid ) Business RulesHook: sms_preparse; Order: 10select case when spcode is null then (0,null) else (1, case spcode::integer when 1 then format(get_string('U_speccodechange',$1),'AY',spcode) when 2 then format(get_string('U_speccodechange',$1),'SP',spcode) when 3 then format(get_string('U_speccodechange',$1),'KR',spcode) when 4 then format(get_string('U_speccodechange',$1),'AJ',spcode) when 5 then format(get_string('U_speccodechange',$1),'KB',spcode) when 6 then format(get_string('U_speccodechange',$1),'KL',spcode) when 7 then format(get_string('U_speccodechange',$1),'KD',spcode) when 9 then format(get_string('U_speccodechange',$1),'DB',spcode) when 10 then format(get_string('U_speccodechange',$1),'BB',spcode) when 11 then format(get_string('U_speccodechange',$1),'KE',spcode) when 12 then format(get_string('U_speccodechange',$1),'IT',spcode) when 13 then format(get_string('U_speccodechange',$1),'IK',spcode) when 14 then format(get_string('U_speccodechange',$1),'KC',spcode) when 16 then format(get_string('U_speccodechange',$1),'UL',spcode) else format(get_string('E_invspec',$1),spcode) end) end from ( select (regexp_matches($2[1], '^U [;#\s]+ [a-z]+(?:\s*,\s*[a-z]+)* [;#\s]+ (\d+) [;#\s]* \d+ (?:(?:[;#\s]*)\d{2,8}(?:\.\d{1,})?)? [;#\s]* (?:(?:[;#\s]*)[a-z]+(?:\s*,\s*[a-z]+)*)? [;#\s]* $', 'igx'))[1] as spcode union select null order by spcode limit 1 ) as foo Hook: sms_validate; Order: 10select case -- allowed to make a diagnosis when is_permitted('can_send_r',$1) then case when $2[5] is null then (1,get_string('E_missingdx',$1)) else (0,null) end -- Pelsa else case when $2[5] is not null then (1,get_string('E_dxnotallowed',$1)) else (0,null) end end from users u where u.id = $1 Hook: sms_validate; Order: 10select case when species.sikhnascode != 33 then (0, null) else (1,'Tidak bisa melaporkan produk hewan') end from (SELECT s.sikhnascode FROM species s where s.code = $2[2]) as species Hook: sms_validate; Order: 20select case when $2[4] is not null then case when length($2[4]) < 7 then (1,get_string('E_notdesacode',$1)) else (0,null) end else (0,null) end | ||
UC: Uji cepatFormat pesanUC [ID kasus] [kode uji] ([kode spesies] [jumlah positif] [jumlah negatif]...) Reply SQLselect format( get_string('UC_reply',sms_userid), t.name[get_lang(sms_userid)], get_location(c.locationid), foo.res) from ( select min(u.caseid) as caseid, min(testtypeid) as testtypeid, string_agg(format(get_string('UC_test',sms_userid), get_species_name(u.speciesid, sms_userid), pos, neg), '; ') as res from ujicepat u where u.msgid = sms_msgid and not u.del ) as foo join cases c on c.id = foo.caseid and not c.del join testtypes t on t.id = foo.testtypeid | ||
UE: Perbarui EartagFormat pesanUE [ID Pengguna] [ID Hewan] [Eartag Nasional] Reply SQLselect format(get_string('eartag_reply',18), params[3], params[2], nama(get_farmer_id(params[1]))) Business RulesHook: sms_validate; Order: 1select case when count(id)=0 then (0, null) else (1,'Maaf, Kode Eartag sudah terdaftar') end from ( SELECT id FROM hewan WHERE national_code = $2[3] ) as eartag Hook: sms_validate; Order: 2select case when count(id)=1 then (0, null) else (1,'Maaf pada kode eartag digit ke 4-5 tidak ditemukan kode provinsi kemendagri yang sesuai') end from ( SELECT id FROM locations WHERE mendagricode = (SELECT SUBSTRING($2[3], 4, 2)) ) as mendagri_code Hook: sms_postinsert; Order: 1update hewan h set national_code = $2[3], modifiedon = now(), modifiedby = $1 from pemilik_hewan ph where ph.idhewan = h.id and ph.idpemilik = get_farmer_id($2[1],$1) and upper(h.identifikasi) = upper($2[2]) and tanggalakhir is null and h.national_code is null and not ph.del and not h.del | ||
UH: Ukuran hewanFormat pesanUH [ID Pemilik] [ID Hewan] [Lingkar dada sentimeter] [Tinggi gumba sentimeter] [Panjang badan sentimeter] Reply SQLselect format(get_string('UH_reply', sms_userid), k1.datakejadian, k2.datakejadian, k3.datakejadian, h.identifikasi, nama(ph.idpemilik::int), l.name, round(((k1.datakejadian::float^2)*k3.datakejadian::float/10838)::numeric,-1)) from kejadian k1 join hewan h on h.id=k1.idhewan join pemilik_hewan ph on ph.idhewan = h.id join users u on u.id = ph.idpemilik join locations l on l.id = u.locationid join kejadian k2 on k2.idhewan=k1.idhewan and k2.msgid=sms_msgid and k2.idtipekejadian=18 join kejadian k3 on k3.idhewan=k1.idhewan and k3.msgid=sms_msgid and k3.idtipekejadian=19 where k1.msgid = sms_msgid and k1.idtipekejadian=13 Business RulesHook: sms_postinsert; Order: 1insert into kejadian (reportdate,idhewan,idtipekejadian,datakejadian,createdby,msgid) select now() , ph.idhewan , 18 , $2[4] , $1 , $3 from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.idpemilik = get_farmer_id($2[1],$1) and ph.tanggalakhir is null and not ph.del where upper(h.identifikasi) = upper($2[2]) and not h.del Hook: sms_postinsert; Order: 2insert into kejadian (reportdate,idhewan,idtipekejadian,datakejadian,createdby,msgid) select now() , ph.idhewan , 19 , $2[5] , $1 , $3 from hewan h join pemilik_hewan ph on ph.idhewan=h.id and ph.idpemilik = get_farmer_id($2[1],$1) and ph.tanggalakhir is null and not ph.del where upper(h.identifikasi) = upper($2[2]) and not h.del | ||
UI: Laporan tanda umum hewan identifikasiFormat pesanUI [tanda,tanda...] [ID Pemilik] [ID Hewan] [diagnosa banding,diagnosa banding...] Reply SQLselect format(get_string('UI_posrep',sms_userid), add_checkdigit(c.id), get_sign_names(signsid,c.userid), get_species_name(speciesid,c.userid), h.identifikasi, nama(u.id), get_location(c.locationid)) |
coalesce('. Ddx: ' | get_disease_names(r.diagnosisid,sms_userid),)
from cases c join signreports s on s.caseid = c.id join hewan h on h.id = s.animalid join pemilik_hewan p on p.idhewan = h.id and p.tanggalakhir is null join users u on u.id = p.idpemilik left outer join response r on r.caseid = c.id where c.msgid = sms_msgid Alert SQLselect phone, message from ( select u.phone, format(get_string('U_alert',u.id), add_checkdigit(c.id), nama(u2.id), format_phone(u2.phone), s.cases, get_sign_names(s.signsid, u.id), get_species_name(s.speciesid,u.id), get_location(c.locationid), coalesce('. DDx: '||get_disease_names(r.diagnosisid,u.id),) ) as message, coalesce(bool_or(coalesce(up.permission,gp.permission) = 1),false) as permitted from users u join cases c on c.msgid = sms_msgid join signreports s on s.caseid = c.id left outer join response r on r.caseid = c.id join users u2 on u2.id = c.createdby join locations l on l.id = c.locationid join locations l2 on l2.id = any(u.area) and l.code ~ ('^'||l2.code) left outer JOIN group_permissions gp ON gp.permission_typeid = 48 AND gp.groupid = ANY(u.groupid) LEFT OUTER JOIN user_permissions up ON up.permission_typeid = 48 AND up.userid = u.id where not u.del and u.id <> sms_userid group by u.id, c.id, u2.id, s.cases, s.signsid, s.speciesid, r.diagnosisid ) foo where permitted /* WITH myusers AS ( SELECT DISTINCT phone, uid FROM ( SELECT id AS uid , phone , UNNEST(area) as area FROM users where not del ) as foo WHERE IS_PARENT_AREA((SELECT locationid FROM cases WHERE msgid = sms_msgid), foo.area) AND IS_PERMITTED('get_pelsa_alert_u',uid) AND uid <> sms_userid ) SELECT mu.phone, format(get_string('U_alert',mu.uid), add_checkdigit(s.caseid), nama(u2.firstname, u2.surname), format_phone(u2.phone), s.cases, get_sign_names(s.signsid, mu.uid), get_species_name(s.speciesid,mu.uid), get_location(c.locationid), coalesce('. DDx: '||get_disease_names(r.diagnosisid,mu.uid),) ) FROM myusers mu join cases c on c.msgid = sms_msgid JOIN users u2 ON (c.createdby = u2.id) join signreports s on c.id = s.caseid left outer join response r on r.caseid = c.id join locations l on c.locationid = l.id */ Business RulesHook: sms_validate; Order: 10Select case when count(id)=0 then (0, null) else (1,'Maaf, Sudah ada laporan UI pada hewan yang sama hari ini.') end from ( select c.id from cases c join case_animals ca on ca.idcase=c.id and not ca.del join hewan h on h.id=ca.idhewan and not h.del join pemilik_hewan ph on ph.idhewan=h.id and not ph.del join users u on u.id=ph.idpemilik and not u.del where u.phone=int_phone($2[2]::text) and upper(h.identifikasi) =upper($2[3]::text) and c.createdon::date=now()::date and c.createdby=$1 and not c.del ) as z |
VAK: Laporan vaksinasiFormat pesanVAK [ID program] ([spesies] [jumlah divaksinasi pertama] {jumlah booster}...) [lokasi] Reply SQL-- new version. Includes special messages for rabies vaccination /* select foo.mainreply || ' ' || case when rabies then case when target is null then get_string('VAK_rep_nopop', sms_userid) -- ' There is no previous population or vaccination data available for this village' -- ' Cakupan vaksinasi tidak diketahui karena tidak ada data populasi di desa ini' when foo.total_this_round >= foo.target then get_string('VAK_rep_enough',sms_userid) -- ' You have finished vaccination in this village' -- ' Cakupan vaksinasi cukup, silahkan ke desa lain' else get_string('VAK_rep_more',sms_userid) -- format(' Your target is %s more dogs in this village', foo.target - foo.total_this_round) -- ' Cakupan belum cukup, lakukan vaksinasi ulang' end else end as rabies_reply from ( select format (get_string('VAK_reply',sms_userid), p.name, l.name, string_agg( case when v.booster is null then format(get_string('VAK_doses', sms_userid),s.name[get_lang(sms_userid)], v.firstdose) else format(get_string('VAK_booster', sms_userid),s.name[get_lang(sms_userid)], v.firstdose, v.booster) end ,'; ')) as mainreply, 111 = any(p.diseaseid) as rabies, -- is this related to a rabies program -- end rabies report case when coalesce(sum(v3.firstdose),0) > coalesce((get_population(l.id, v.speciesid)).total,0) then 1.1 * sum(v3.firstdose) -- 10% more than the last vacc round when coalesce(sum(v3.firstdose),0) <= coalesce((get_population(l.id, v.speciesid)).total,0) and get_population(l.id, v.speciesid) is not null -- checks for both 0/null then (get_population(l.id, v.speciesid)).total -- the most recent population estimate if more than the last vacc round else null end as target, sum(v2.firstdose) as total_this_round -- current message data -- v.id, p.id, l.id, v.firstdose, v.booster, -- same village, all vaccinations this round -- count(v2.*),sum(v2.firstdose) as this_round_first, sum(v2.booster) as this_round_booster, -- same village, totals for previous round -- count(v3.*),sum(v3.firstdose) as last_round_first, sum(v3.booster) as last_round_booster, -- same village, latest population figures -- (get_population(l.id, v.speciesid)).total as pop_total from vaccinations v join vaccinationprograms p on v.programid = p.id join species s on v.speciesid = s.id join locations l on v.locationid = l.id -- join the current vaccination round and population from the same village (there may have been earlier vaccinations in this village) left join vaccinations v2 on v2.locationid = v.locationid -- same village and v2.createdon between (v.createdon-'4 months'::interval) and v.createdon -- between 4 and 10 months previously and v2.speciesid = v.speciesid and not v2.del left join vaccinationprograms p2 on p2.id = v2.programid and p2.diseaseid && p.diseaseid -- some of the same diseases and not p2.del -- join the previous vaccination round and population from the same village left join vaccinations v3 on v3.locationid = v.locationid -- same village and v3.createdon between (v.createdon-'10 months'::interval) and (v.createdon-'4 months'::interval) -- between 4 and 10 months previously and v3.speciesid = v.speciesid and not v3.del left join vaccinationprograms p3 on p3.id = v3.programid and p3.diseaseid && p.diseaseid -- some of the same diseases and not p3.del where v.msgid = sms_msgid group by v.id, p.id, l.id,v.firstdose, v.booster) foo */ -- old version select format (get_string('VAK_reply',sms_userid), p.name, l.name, string_agg( case when booster is null then format(get_string('VAK_doses', sms_userid),s.name[1], firstdose) else format(get_string('VAK_booster', sms_userid),s.name[1], firstdose, booster) end ,'; ')) from vaccinations v join vaccinationprograms p on v.programid = p.id join species s on v.speciesid = s.id join locations l on v.locationid = l.id where v.msgid = sms_msgid group by p.name, l.name Alert SQLselect u.phone, rabies_reply from (select case when rabies then bar.mainreply || ' ' || format(get_string('VAK_status',sms_userid), pop, bar.target, total_this_round, ((100*total_this_round)/pop::float)::numeric(5,2) ) |
case when target is null then get_string('VAK_rep_nopop', sms_userid) when bar.total_this_round >= bar.target then get_string('VAK_achieved',sms_userid) else format(get_string('VAK_more',sms_userid), target - total_this_round) end else end as rabies_reply, locid from ( select mainreply, case -- population estimation: two options -- 1) Population recorded in the database (aim for 70% coverage), or if not availalbe -- 2) Number vaccinated last year (increase by 15%) when coalesce(sum(v3.firstdose),0) <= coalesce((get_population(60, locid)).total,0) -- pop data availalbe and larger than vac and (get_population(60,locid)).total is not null -- checks for both 0/null then ceiling(get_param('rabies_target_coverage')::float * (get_population(60, locid)).total) when coalesce(sum(v3.firstdose),0) > coalesce((get_population(60,locid)).total,0) then ceiling(get_param('rabies_annual_pop_increase')::float * sum(v3.firstdose)) -- 15% more than the last vacc round else null end as target, sum(coalesce(foo.firstdose,0)) as total_this_round, -- only use first dose to calculate coverage (not booster), rabies, (get_population(60, locid)).total as pop, locid from( select main.*, v2.*, -- change made here in response to Albert's request -- special Rabies handling disabled, so that normal reply is supplied -- do this by marking the variable rabies as false for all cases -- this makes it easy to switch back on if we need to --- false as rabies 111 = any(did) as rabies from ( -- foo select -- main v.locationid as locid, v.createdon as co, v.programid as pid, p.diseaseid as did, format(get_string('VAK_alert',2), nama(u.id), local_phone(u.phone), add_checkdigit(p.id), get_location(l.id), string_agg( case when v.booster is null then format(get_string('VAK_doses', sms_userid),s.name[get_lang(sms_userid)], v.firstdose) else format(get_string('VAK_booster', sms_userid),s.name[get_lang(sms_userid)], v.firstdose, v.booster) end ,'; ')) as mainreply from vaccinations v join users u on u.id = v.createdby join vaccinationprograms p on v.programid = p.id join locations l on l.id = v.locationid join species s on s.id = v.speciesid where v.msgid = sms_msgid group by p.id, u.id, l.id, v.locationid, v.createdon, v.programid,p.diseaseid ) main -- join the current vaccination round and population from the same village (there may have been earlier vaccinations in this village) left join vaccinations v2 on v2.locationid = locid -- same village and v2.createdon between (co-'3 months'::interval) and co -- within the last 3 months --and v2.speciesid in (60,61,62,215,216,217,218,219,220) and not v2.del join species sp2 on sp2.id = v2.speciesid and is_child ('4.1.1',sp2.hiercode) left join vaccinationprograms p2 on p2.id = pid and p2.diseaseid && did -- some of the same diseases and not p2.del ) foo left join vaccinations v3 on v3.locationid = locid --v.locationid -- same village and v3.createdon between (co-'15 months'::interval) and (co-'9 months'::interval) -- between 9 and 15 months previously (to catch the last vaccination round and not v3.del left join species sp3 on sp3.id = v3.speciesid and is_child ('4.1.1',sp3.hiercode) left join vaccinationprograms p3 on p3.id = v3.programid and p3.diseaseid && did -- some of the same diseases and not p3.del group by locid, mainreply, rabies ) bar ) message join users u on is_permitted('get_vac_alert', u.id) -- array[36,37,38,39, 40] && u.groupid and not u.del and ischildlocation(u.area, locid) Business RulesHook: sms_validate; Order: 10select case when length($2[3]) < 8 then (1,get_string('E_notdesacode',$1)) else (0,null) end Hook: sms_validate; Order: 20with newdata as ( select remove_checkdigit($2[1]::integer) as programid, s.id as speciesid, number::integer as firstvac, get_location_id(trim($2[3])) as locid from ( select r[1] as species,r[2] as number from ( select regexp_matches($2[2] ,'([a-z]+)[;#\s]*(\d+)((?:[;#\s]*)\d+)?', 'igx') as r ) foo ) bar join species s on s.code ilike bar.species ) select result from ( select 1 as natorder, case when firstvac >= firstdose then (1,format(get_string('VAK_duplicate',$1), format('%s (%s)',get_species_name(v.speciesid,$1),v.firstdose))) else (0,null) end as result from ( select v.speciesid, v.firstdose, n.firstvac from vaccinations v join newdata n -- get the matching rows on n.speciesid = v.speciesid -- same species and n.programid = v.programid -- same programid and v.reportdate::date = now()::date -- same date and v.locationid = n.locid -- same village -- and n.firstvac >= v.firstdose -- same or higher number of animals and v.createdby = $1 -- same user and not v.del --join locations l on l.id = v.locationid and left(l.code,2) = '51' -- only Bali for the moment order by v.createdon asc limit 1 ) v union select 2, (0,null) order by natorder ) fbar limit 1 | |
VSK: Validasi SKKHFormat pesanVSK [ID SKKH] Reply SQLselect format(get_string('VSK_reply',sms_userid), add_checkdigit(m.id), case when now() - m.reportdate < (get_param('skkh_valid_period'))::interval then get_string('Valid',sms_userid) else get_string('Expired',sms_userid) end, ownerid, tanggal(m.reportdate), string_agg(format('%s %s',a.total,get_species_name(a.speciesid,sms_userid)),', '), get_location(origin), get_location(destination)) from movement m left outer join movementanimals a on a.movementid = m.id where m.id = remove_checkdigit(trim(params[1])::bigint) group by m.id | ||
Z: Suspek zoonosisFormat pesanZ [ID kasus] [jumlah manusia terpapar] [jumlah manusia sakit] [jumlah manusia mati] Reply SQLselect format(get_string('Z_reply', sms_userid), terpapar, sakit, mati, get_species_name(coalesce(s.speciesid, sy.speciesid),sms_userid), get_location(c.locationid), tanggal(c.reportdate)) from zoonoses z join cases c on c.id = z.caseid left outer join signreports s on s.caseid = c.id left outer join syndromereports sy on sy.caseid = c.id where z.msgid = sms_msgid Alert SQLwith lid as ( select l.centroid, format(get_string('Z_alert',sms_userid) , nama (z.createdby) , format_phone(u2.phone) , z.terpapar , z.sakit , z.mati , d.name , get_species_name(s.speciesid,sms_userid) , get_location(c.locationid))as msg from zoonoses z JOIN cases c on c.id = z.caseid JOIN users u2 ON (c.userid = u2.id) join syndromereports s on c.id = s.caseid join syndromes sy on sy.id = s.syndromeid join response r on r.caseid = c.id join diseases d on d.id=sy.targetdiseaseid join locations l on c.locationid = l.id where z.msgid = 1988514284778197 and d.priority = true), gperm as ( select array_agg(groupid) as p from group_permissions where permission_typeid = 73 and permission = 1 -- 47 = get_pelsa_alert_p ) select u.phone ,lid.msg from users u left join gperm on true left join user_permissions up on up.userid = u.id and up.permission_typeid = 73 -- 47 = get_pelsa_alert_p join locations l on array[l.id] && u.area join lid on true -- reporter location where (coalesce(up.permission=1,gperm.p && u.groupid)) and st_contains(l.geom,lid.centroid)
|