SMS handler functions: Perbedaan revisi

(Created page with "{{#apGetSQL: select format ('<h2>%s: %s</h2> <h3>Format pesan</h3> %s %s %s %s', s.start_code, s.name[1],get_sms_format(s.id,1), format('<h3>Reply SQL</h3> %s',replace(s.r...")
(Tidak ada perbedaan)

Revisi per 25 Februari 2014 03.25

Daftar isi

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: 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 TO_DATE(REGEXP_REPLACE($2[5], '[^0-9]', '/', 'g'), 'DD/MM/YYYY') <=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, TO_DATE(REGEXP_REPLACE($2[5], '[^0-9]', '/', 'g'), 'DD/MM/YYYY'), $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
Select
case when count(caseid)=0 then (0, null)
else (1,'Maaf, Anda sudah melaporkan pengobatan untuk kasus ini, tunggu beberapa menit untuk menambahkan laporan pengobatan dengan id kasus yang sama') end
from (select t2.caseid from data.treatmentanimals t 
join data.treatments t2 on t2.id = t.treatmentid
join data.cases c on c.id = t2.caseid
where t2.caseid = remove_checkdigit($2[1]::int)
and EXTRACT(EPOCH FROM NOW() - t.reportdate) < 60
order by t.id desc
limit 10) as Z
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)


Diperoleh dari "https://wiki.isikhnas.com/index.php?title=SMS_handler_functions&oldid=819"