AH: Akhir Hewan
Format pesan
AH [ID Pemilik] [ID Hewan] [Kondisi Hewan]
Reply SQL
select 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 Rules
Hook: sms_postinsert; Order: 1
update 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: 10
UPDATE 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 Badan
Format pesan
BB [ID Pemilik] [ID hewan] [Berat Badan]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 10
Select
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 Score
Format pesan
BCS [ID Pemilik] [ID Hewan] [score 1,2,3,4,5]
Reply SQL
select 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 Hewan
Format pesan
BCSK [Kode Kelompok] ([Nilai Kondisi] [Jumlah hewan]...)
Reply SQL
select
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: Birahi
Format pesan
BH {ID Pemilik} [ID Hewan]
Reply SQL
with 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 SQL
with 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 Peternak
Format pesan
CIP [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 Bibit
Format pesan
CKAB [asal bibit]
Reply SQL
select
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 Hewan
Format pesan
CKH [species]
Reply SQL
select
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 Hijauan
Format pesan
CKHI [jenis hijauan]
Reply SQL
select
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 infrastruktur
Format pesan
CKI [jenis infrastruktur] [lokasi]
Reply SQL
select 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 spesimen
Format pesan
CKJS [jenis specimen]
Reply SQL
select
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 lokasi
Format pesan
CKL [nama lokasi]
Reply SQL
select 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 obat
Format pesan
CKO [obat]
Reply SQL
select
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 penyakit
Format pesan
CKP [penyakit]
Reply SQL
select
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 Produk
Format pesan
CKPR [Produk]
Reply SQL
select 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 pesan
CKS [species]
Reply SQL
select
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 tanda
Format pesan
CKT [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 Desa
Format pesan
CPD [spesies] [kode lokasi]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 10
select
case
when length($2[2]) < 8 then
(1,get_string('E_notdesacode',$1))
else
(0,null)
end
|
CSR: Cari Status Reproduksi
Format pesan
CSR [ID Peternak] [ID Hewan]
Reply SQL
with 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: Pemusnahan
Format pesan
CUL [ID program] ([spesies] [jumlah hewan]...) [ID lokasi]
Reply SQL
select 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 vaksinasi
Format pesan
CV [kode lokasi]
Reply SQL
select
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 rabies
Format pesan
CVR [kode lokasi]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 10
select
case
when length($2[1]) < 8 then
(1,get_string('E_notdesacode',$1))
else
(0,null)
end
|
D: Daftar pengguna
Format pesan
D
Reply SQL
select 1
|
DH: Daftar Hewan
Format pesan
DH [ID Peternak] ([Identifikasi Hewan] [Kode spesies] [Jenis Kelamin] [Umur]...)
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 0
drop table if exists regans
Hook: sms_validate; Order: 1
create temporary table regans (
re text[],
ident varchar,
species integer,
sex integer,
birth date
)
Hook: sms_validate; Order: 2
insert 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: 3
select
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: 4
select
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: 1
update 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: 2
select
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: 3
with 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: 4
drop table regans
Hook: sms_postinsert; Order: 10
select
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 Kasus
Format pesan
DHK [ID Kasus]
Reply SQL
select 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 peternak
Format pesan
DHP {ID Peternak}
Reply SQL
with 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 Pengguna
Format pesan
DIP [ID Pengguna] [Kode Infrastruktur] [Jenis Infrastruktur]
Reply SQL
select
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 Rules
Hook: sms_postinsert; Order: 1
update 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 hewan
Format pesan
DKB [kode spesies]
Reply SQL
select 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 lokasi
Format pesan
DKL [kode lokasi]
Reply SQL
select 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 Cair
Format pesan
DNC [Jumlah liter] [Kode infrastruktur tujuan]
Reply SQL
select 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 SQL
with 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 Pemilik
Format pesan
DP [Nama] [Kode Lokasi] {Identifikasi pemilik}
Reply SQL
SELECT
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 Beku
Format pesan
DSB ([Bangsa hewan] [Jumlah straw]...) [Kode infrastruktur tujuan]
Reply SQL
select 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 SQL
with 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 Babinsa
Format pesan
DTB [nama] [telp]
Reply SQL
select 'berhasil'
|
DX: Diagnosa definitif
Format pesan
DX [ID kasus] [diagnosa]
Reply SQL
select
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 hewan
Format pesan
GI [ID Peternak] [ID Hewan Lama] [ID Hewan Baru]
Reply SQL
with 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 Rules
Hook: sms_validate; Order: 1
select
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: 1
with 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: Hapus
Format pesan
H [Kode SMS]
Reply SQL
select 1
|
HK: Hewan terkait Kasus
Format pesan
HK [ID Kasus] [ID Pemilik] ([Identifikasi Hewan]...)
Reply SQL
select 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 Ternak
Format pesan
HPT ([jenishijauan] [produsen] [jeniskebun] [luas] [Jumlah bibit] [Bulan untuk panen] [sumberanggaran] [tahun_fasilitasi]...) [lokasi]
Reply SQL
select
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 Buatan
Format pesan
IB [ID Peternak] [ID hewan] [ID pejantan] [Kode pembuatan]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 1
Select
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: 2
Select
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 hewan
Format pesan
IH [ID Pemilik] [ID hewan]
Reply SQL
select 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 foto
Format pesan
IMAGE [URL]
Reply SQL
select
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 Obat
Format pesan
IO [Kode Obat]
Reply SQL
select 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 Hewan
Format pesan
JBH [ID penjual] [ID Pembeli] ([ID Hewan]...) [Tipe]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 1
select
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: 2
select
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: 3
select
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: 4
select
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: -1
create temp table animaltransfers (
sellerid integer,
buyerid integer,
saleanimals integer,
newname varchar
)
on commit drop
Hook: sms_postinsert; Order: 0
insert 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: 1
insert into pemilik_hewan (idpemilik, idhewan, createdby)
select buyerid, saleanimals,$1 from animaltransfers
Hook: sms_postinsert; Order: 2
update hewan
set identifikasi = newname,
modifiedby = $1,
modifiedon = now()
from animaltransfers
where hewan.id = saleanimals
Hook: sms_postinsert; Order: 3
update pemilik_hewan
set tanggalakhir = now(),
modifiedon = now(),
modifiedby = $1
from animaltransfers
where idpemilik = sellerid
and idhewan = saleanimals
Hook: sms_postinsert; Order: 4
insert 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 alam
Format pesan
KA [ID Pemilik hewan] [ID Hewan] {ID Pemilik pejantan} [ID Pejantan] [tanggal_ka]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 1
select
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: 2
select
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: 3
with 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: 4
with 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: 5
select
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: 1
insert 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 peternak
Format pesan
KDP [PIN]
Reply SQL
select
format(get_string('KDP_reply', id),
firstname,
get_location(locationid)
)
from users
where phone = sms_phone
Business Rules
Hook: sms_validate; Order: 1
select
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 Akhir
Format pesan
KDX [ID Kasus Kompartemen] [Diagnosa Akhir]
Reply SQL
select 'berhasil'
Business Rules
Hook: sms_validate; Order: 10
select
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: 1
UPDATE 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: Keguguran
Format pesan
KGG [ID Pemilik] [ID Hewan] [Trimester 1,2,3]
Reply SQL
select 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 Kompartemen
Format pesan
KNEG {Aktif?}
Reply SQL
SELECT 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 Rules
Hook: sms_validate; Order: 10
SELECT
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: 10
INSERT 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: Kode
Format pesan
KODE {jenis kode}
Reply SQL
select 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: Komentar
Format pesan
KOM [ID kasus] [komentar]
Reply SQL
select 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 SQL
select
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 report
Format pesan
LAB [ID kasus] ([jenis spesimen] [bentuk spesimen] {seksi} [jumlah spesimen]...) [lab ID]
Reply SQL
select
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 desa
Format pesan
LAPD [kode desa]
Reply SQL
select 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 kasus
Format pesan
LAPK [ID kasus]
Reply SQL
select 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 Pengguna
Format pesan
LAPP
Reply SQL
select 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: Lahir
Format pesan
LH [ID Pemilik] [ID Hewan] [Jenis Kelamin] [Identifikasi Pedet] [tanggal_lahir] {Berat Lahir}
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 1
Select
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: 2
SELECT
case
when $2[5]::date <=now()::date
then (0,null)
else (1,'Maaf, tanggal tidak bisa di masa depan')
end
Hook: sms_postinsert; Order: 1
with 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: 2
update kejadian k
set datakejadian = h.id
from hewan h
where k.msgid = $3
and h.msgid = $3
Hook: sms_postinsert; Order: 3
insert 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: 5
select
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 case
Format pesan
LINK [ID Foto] [ID Kasus]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 5
update caseimages
set caseid = remove_checkdigit(($2[2])::bigint),
modifiedon = now(),
modifiedby = $1
where id = remove_checkdigit(($2[1])::bigint)
returning (0,)
|
LKOM: Laporan Kompartemen
Format pesan
LKOM [Kode Farm] [Tanda,Tanda...] [Jumlah Hewan] [Persentase] [Diagnosa Sementara,Diagnosa Sementara...]
Reply SQL
select 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 SQL
with 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 Rules
Hook: sms_validate; Order: 5
select
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: 10
select
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: 1
INSERT 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 lanjut
Format pesan
LTL [ID kasus] ([spesies] [jumlah sakit] [jumlah mati] [jumlah dimusnahkan/potong bersyarat] [jumlah berisiko]...) {selesai}
Reply SQL
select 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: NIK
Format pesan
NIK [ID Pengguna] [no ktp]
Reply SQL
select format(get_string('nik_reply',sms_userid),
nik,nama(id),phone)
from users u
where u.phone = int_phone(params[1])
Business Rules
Hook: sms_validate; Order: 1
select
case
when length($2[2]) = 16 then (0,null)
else (1,'maaf,no ktp harus 16 digit!')
end
Hook: sms_validate; Order: 2
select
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: 3
select
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: 1
update users u
set nik = $2[2]
where u.phone=int_phone($2[1])
and not u.del
|
OB: Laporan pengobatan
Format pesan
OB [ID kasus] ([kode obat] [dosis per ekor] [jumlah hewan]...)
Reply SQL
select 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 SQL
select
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 Rules
Hook: sms_validate; Order: 10
WITH 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 Individu
Format pesan
OBI [Id Kasus] ([kode obat] [dosis]...) [urutan hewan]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 10
Select
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: 15
WITH 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 prioritas
Format pesan
P [sindrom] [spesies] [jumlah hewan] {lokasi} {diagnosa,diagnosa...}
Reply SQL
select
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 SQL
select 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 Rules
Hook: sms_preparse; Order: 10
select
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: 7
Select
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: 10
select
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: 20
select
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 pesan
PG [ID Pemilik] ([ID Hewan]...)
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 10
Select
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 kasus
Format pesan
PK [ID kasus] [kode perkembangan kasus]
Reply SQL
select
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 kebuntingan
Format pesan
PKB [ID Pemilik] [ID hewan] [Bulan]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 1
Select
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 individual
Format pesan
PKHI [ID kasus] [kode perkembangan kasus] [ID Pemilik] [ID Hewan]
Reply SQL
select 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 lengkap
Format pesan
PKL [ID kasus] ([kode perkembangan kasus] [jumlah hewan]...)
Reply SQL
select 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 Lain
Format pesan
PL [ID Pemilik] [ID Hewan] [tipepenanganan]
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 10
with 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 Cair
Format pesan
PNC [ID pengiriman] [Jumlah liter]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 2
select
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: 1
update receipt set shipmentid = nitrogenshipment.shipmentid from nitrogenshipment where receipt.id = receiptid and receipt.msgid = $3 returning (0,)
|
PNEG: Laporan negatif
Format pesan
PNEG
Reply SQL
select 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 populasi
Format pesan
POP ([jenis hewan] [jumlah hewan]...) {lokasi}
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 10
select
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 Susu
Format pesan
PS [ID Pemilik] [ID hewan] [Liter]
Reply SQL
select 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 Beku
Format pesan
PSB [ID pengiriman] ([Bangsa hewan] [Jumlah straw]...)
Reply SQL
select 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 Rules
Hook: sms_validate; Order: 0
select
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: 1
select
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: 1
update receipt set shipmentid = strawshipment.shipmentid from strawshipment where receipt.id = receiptid and receipt.msgid = $3 returning (0,)
|
Q: Pertanyaan
Format pesan
Q [petanyaan]
Reply SQL
select 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 report
Format pesan
R [ID Kasus] [dikunjung (K/T)] [diagnosa banding,diagnosa banding...] {diagnosa lain}
Reply SQL
select
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 SQL
with 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 Rules
Hook: sms_validate; Order: 10
select (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 potong
Format pesan
RP ([jenis hewan] [jumlah dipotong]...) {kode RP}
Reply SQL
select
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 vaksinasi
Format pesan
RVAK [ID kasus] ([kode penyakit] [tanggal vaksinasi]...)
Reply SQL
select
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: Sapih
Format pesan
SAP [ID Pemilik] [ID Hewan]
Reply SQL
select 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 Storage
Format pesan
SCS ([Jenis Produk] [Jumlah Masuk (Ton)] [Jumlah keluar (ton)] [jumlah stok (ton)]...) [kode cold storage]
Reply SQL
select
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 pakan
Format pesan
SHP ([jenis hijauan] [jumlah] [satuan] [harga]...) [produsen]
Reply SQL
select
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 Kontainer
Format pesan
SKO ([Jenis Kontainer] [Kondisi Kontainer] [Jumlah]...)
Reply SQL
select
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 Konsentrat
Format pesan
SKP ([Jenis Konsentrat] [jumlah stok konsentrat (Kg)] [sumberanggaran]...) [produsen]
Reply SQL
select
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: SKSR
Format pesan
SKSR [ID Pemilik] [ID Hewan]
Reply SQL
with 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 Rules
Hook: sms_postinsert; Order: 10
select
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 pesan
SLAB [ID program] ([species] [jumlah hewan]...) [lokasi] {ID laboratorium}
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 10
select
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 pesan
SLAP [ID program] ([spesies] [jumlah positif] [jumlah negatif]...) [lokasi]
Reply SQL
select
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 Rules
Hook: sms_validate; Order: 10
select
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 Cair
Format pesan
SNC [Jumlah liter]
Reply SQL
select 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 penyakit
Format pesan
SP [ID kasus] [kode sumber] {lokasi}
Reply SQL
select
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 Kesmavet
Format pesan
SPK [tanda,tanda...] [Produk] [uji kit] [jumlah positif] [jumlah negatif] [kode pasar]
Reply SQL
select 'terimakasih'
|
SSB: Stok Semen Beku
Format pesan
SSB ([Bangsa] [Jumlah straw]...)
Reply SQL
select 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 Straw
Format pesan
ST [ID Pejantan] [ID Pembuatan] [Spesies] [Kode BIB]
Reply SQL
Select 'Terimakasih'
|
TK: Tanda klinis
Format pesan
TK [ID kasus] [tanda,tanda...]
Reply SQL
select
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 Lain
Format pesan
TL [ID program] ([kode jenis tindakan] [jumlah]...) [lokasi]
Reply SQL
select 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 Pemotongan
Format pesan
TP ([Jenis Hewan] [Alasan Penolakan] [Jumlah ditolak]...) {Kode RP}
Reply SQL
select
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 umum
Format pesan
U [tanda,tanda...] [spesies] [jumlah hewan] {lokasi} {diagnosa banding,diagnosa banding...}
Reply SQL
select 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 SQL
select 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 Rules
Hook: sms_preparse; Order: 10
select
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: 10
select
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: 10
select
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: 20
select
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 cepat
Format pesan
UC [ID kasus] [kode uji] ([kode spesies] [jumlah positif] [jumlah negatif]...)
Reply SQL
select 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 Eartag
Format pesan
UE [ID Pengguna] [ID Hewan] [Eartag Nasional]
Reply SQL
select format(get_string('eartag_reply',18),
params[3], params[2], nama(get_farmer_id(params[1])))
Business Rules
Hook: sms_validate; Order: 1
select
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: 2
select
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: 1
update 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 hewan
Format pesan
UH [ID Pemilik] [ID Hewan] [Lingkar dada sentimeter] [Tinggi gumba sentimeter] [Panjang badan sentimeter]
Reply SQL
select
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 Rules
Hook: sms_postinsert; Order: 1
insert 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: 2
insert 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 identifikasi
Format pesan
UI [tanda,tanda...] [ID Pemilik] [ID Hewan] [diagnosa banding,diagnosa banding...]
Reply SQL
select 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 SQL
select 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 Rules
Hook: sms_validate; Order: 10
Select
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 vaksinasi
Format pesan
VAK [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 SQL
select 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 Rules
Hook: sms_validate; Order: 10
select
case
when length($2[3]) < 8 then
(1,get_string('E_notdesacode',$1))
else
(0,null)
end
Hook: sms_validate; Order: 20
with 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 SKKH
Format pesan
VSK [ID SKKH]
Reply SQL
select
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 zoonosis
Format pesan
Z [ID kasus] [jumlah manusia terpapar] [jumlah manusia sakit] [jumlah manusia mati]
Reply SQL
select
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 SQL
with 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)
|
