Database functions

Revisi per 25 Februari 2014 10.31 oleh Catriona (bicara | kontrib) (Created page with "{{#apGetSQL:select format($$<h3>%1$s</h3> <dt>Function call: </dt><dd>%1$s (%2$s) %3$s</dd> <dt>Schema: </dt><dd>%4$s</dd>%5$s $$,fname, string_agg(format('%s%s %s', cas...")
(beda) ← Revisi sebelumnya | Revisi terkini (beda) | Revisi selanjutnya → (beda)

Daftar isi

add_checkdigit

Function call:
add_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number, return it with a check digit appended to the end of it.

addlookuprecord

Function call:
addlookuprecord (lookuptype character varying, cde character varying, name character varying, descr character varying) integer
Schema:
metadata
Description:
Add a Lookup Type Record

animals_affected_report

Function call:
animals_affected_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (ANIMALS_AFFECTED_REPORT(casechid,usrid,1)).*;

anyerrors

Function call:
anyerrors (result inputprocessresult) boolean
Schema:
metadata
Description:
A simple utility function to simplify checking whether errors have been found

app_checkpin

Function call:
app_checkpin (userphone character varying, userpin character varying) json
Schema:
metadata
Description:
App phone number validation system. Given a user phone number and pin, return matching user details.

app_get_form

Function call:
app_get_form (smsid integer, userid integer, OUT form_def json) json
Schema:
metadata
Description:
Generate JSON form definition for mobile App based on sms metadata

app_get_project_by_phone

Function call:
app_get_project_by_phone (userphone character varying, OUT project_def json) json
Schema:
metadata
Description:
Look up project by phone

app_get_project_grouped

Function call:
app_get_project_grouped (smsuserid integer, OUT project_def json) json
Schema:
metadata
Description:
Generate project JSON for mobile app (grouped version)

app_get_project

Function call:
app_get_project (smsuserid integer, OUT project_def json) json
Schema:
metadata
Description:
Generate project JSON for mobile app, based on user phone number

app_get_static

Function call:
app_get_static (static_id integer, userid integer, OUT static_data text) text
Schema:
metadata
Description:
Download App static data

app_startup

Function call:
app_startup (userphone character varying) boolean
Schema:
metadata
Description:
App phone number validation system. Given a user phone number, generate a random pin, store it in the user's record, and send a confirmation SMS message.

businessrulehandler

Function call:
businessrulehandler (hookname hook, uid integer, callingfunc character varying, args character varying[], sms_msgid bigint) businessrulereturn
Schema:
backoffice
Description:
The function calls business rule handler functions which match the hook name and caller name input arguments. There can be 0, 1, or more business rule functions which match.

Business rule functions are assumed to take the userid as the first argument, followed by the arguments passed into this function in the args array, and to return a composite object consisting of a return code and a error string. If the business rule function succeeds, it returns a result code of zero, and an empty or null error string. If the business rule function encounters a non-fatal error, it returns a result code of zero, and a non-empty error string. In this case, further rule handlers can be called. If the business rule function has a fatal error, it returns a result code of 1, and a non-empty error string. In this case, any further business rule handlers are not called.

If an internal error occurs in a rule handler function, causing an exception to be thrown, this will be trapped and treated as if it was an error return.

This function returns 0 and an empty string to indicate success, or 1 and an error message to indicate failure. If multiple errors were detected, the error strings are concatenated with linefeed characters.

case_comments_report

Function call:
case_comments_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASE_COMMENTS_REPORT(casechid,usrid,1)).*;

caseimages_report

Function call:
caseimages_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASEIMAGES_REPORT(casechid,usrid,1)).*;

case_resolved_report

Function call:
case_resolved_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASE_RESOLVED_REPORT(casechid,usrid,1)).*;

check_d_codes

Function call:
check_d_codes (codes text[], sms_userid integer, OUT msg character varying) character varying
Schema:
sms
Description:
Check incoming codes from SMS

checklocationcontained

Function call:
checklocationcontained (parentid integer, locid integer) boolean
Schema:
reference
Description:
See if a location is the child of another location

checklookupcode

Function call:
checklookupcode (lkuptypid integer, codeval character varying) integer
Schema:
metadata
Description:
Used for tables where there are too many codes to fit into a hstore (e.g. locations with 77,000 records). Return the integer id value for the code if it a valid code, or NULL if it is not. This function should always return the same integer that GetLookupTableData would have returned for the same code value.

cleanname

Function call:
cleanname (name character varying) character varying
Schema:
metadata
Description:
Given a column/table name used for user display, convert it into a name suitable for inclusion in SQL statements, by stripping out any non-alphanumeric characters

clinical_signs_report

Function call:
clinical_signs_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CLINICAL_SIGNS_REPORT(casechid,usrid,1)).*;

columnintable

Function call:
columnintable (tblname character varying, colname character varying) boolean
Schema:
metadata
Description:
Return true if the table contains a column with the given name, false otherwise. Results are unpredictable if there are two tables with that name, in different schemas!! (which should not ever happen...)

confirm_register_user

Function call:
confirm_register_user (userid integer, sms_pin character varying, sms_msgid bigint, OUT rslt integer, OUT msg character varying) record

Schema:
backoffice
Description:
This function confirms a user as registered by:
  1. finding their phone number,
  2. adding the number to the user record with a matching pin,
  3. before setting the pin to NULL.

create_api

Function call:
create_api (tablename character varying) integer
Schema:
metadata

createbasetype

Function call:
createbasetype (ourname character varying, descr character varying, internalname character varying) integer
Schema:
metadata
Description:
Create a basetype record for each internal type which users are allowed to use as a type for fields in our user-defined projects. Returns the id of the created record.

createdatatable

Function call:
createdatatable (tblid integer) boolean
Schema:
metadata
Description:
Physically creates the table defined in the specified record of datatables, using the field definitions for that table that exist in tablefields.

createdatatablerecord

Function call:
createdatatablerecord (tblnam character varying) integer
Schema:
metadata
Description:
Create an entry in the datatable table for a project master table (but do not define the table in question, since the field definitions for the table do not yet exist).

createdatatablerecord

Function call:
createdatatablerecord (tblnam character varying, parent integer, parentlinkfield character varying) integer
Schema:
metadata
Description:
Create an entry in the datatable table (but do not define the table in question, since the field definitions for the table do not yet exist).

createdomaintype

Function call:
createdomaintype (ourname character varying, descr character varying, internalname character varying, checkcond character varying) integer
Schema:
metadata
Description:
Create a domain type, which is basically an internal type with a check condition added - checkcond is a string of the form: VALUE >= 1 AND VALUE <= 100 - returns the id of the created record.

create_lab_standards_file

Function call:
create_lab_standards_file (standard integer, labcode character varying) character varying
Schema:
reference

createlookuptable

Function call:
createlookuptable (ourname character varying, schema character varying, synonyms boolean) integer
Schema:
metadata
Description:
Create a generic lookup table file. If the optional synonyms flag is set, include a synonym column to point back to the record

createlookuptypefortable

Function call:
createlookuptypefortable (lookuptype character varying, descr character varying, tblname character varying, lkupcol character varying) integer
Schema:
metadata
Description:
Create a lookup type for a table which already exists.

createlookuptype

Function call:
createlookuptype (ourname character varying, descr character varying) integer
Schema:
metadata
Description:
Create a lookup type. Also create the corresponding table which will contain the definitions of the values we will be looking up.

create_password

Function call:
create_password (passlength integer) text
Schema:
backoffice
Description:
Creates a random password of the specified length using the following characters only:

23456789ABCDEFGHJKMNPQRSTUVWXYZabcdefghjkmnpqrstuvwxyz

create_pin

Function call:
create_pin (pinlength integer, OUT pin text) text
Schema:
backoffice
Description:
Creates a random numeric PIN number of the specified lenght (default 6 digits), for use during user registration.

createtablefield

Function call:
createtablefield (tblid integer, fname character varying[], typid integer, keyf boolean, mand boolean, repeat boolean) boolean
Schema:
metadata
Description:
Create a table field

custom_excel_import

Function call:
custom_excel_import (jid integer, mail_userid integer, mail_msgid bigint, sheet character varying) integer
Schema:
metadata
Description:
Parses custom lab spreadsheet data and inserts it into iSIKHNAS.

Called by parse_XM.

Parameters

  • JID: jobid from the import jobs list (currently only 1)
  • msgid: unique message ID assigned at receipt of the email

definitive_diagnosis_report

Function call:
definitive_diagnosis_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (DEFINITIVE DIAGNOSIS_REPORT(casechid,usrid,1)).*;

del_ah

Function call:
del_ah (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_ai

Function call:
del_ai (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from aireports/aireportanimals

del_bb

Function call:
del_bb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_bcs

Function call:
del_bcs (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_bh

Function call:
del_bh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_cip

Function call:
del_cip (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from users

del_cul

Function call:
del_cul (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from afkir

del_dh

Function call:
del_dh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from hewan

del_dnc

Function call:
del_dnc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from shipments/nitrogenshipment

del_dp

Function call:
del_dp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from users

del_dsb

Function call:
del_dsb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from shipments/strawshipment

del_dx

Function call:
del_dx (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from diagnoses

delete_email

Function call:
delete_email (bademail text, reason text) boolean
Schema:
backoffice
Description:
Deletes the specified email address, and sends an SMS alert to the user, and an email alert to their kabupaten coordinator alerting them of the fact and the reason

deletetablerecords

Function call:
deletetablerecords (tblname character varying, condition character varying) integer
Schema:
metadata
Description:
Given a table name and a text condition, delete all records from that table which match the specified condition.

del_hk

Function call:
del_hk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from case_animals

del_hpt

Function call:
del_hpt (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from hijauan

del_ib

Function call:
del_ib (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_image

Function call:
del_image (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from caseimages

del_jh

Function call:
del_jh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from pemilik_hewan

del_kgg

Function call:
del_kgg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_kom

Function call:
del_kom (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from komentar

del_kt

Function call:
del_kt (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_lab

Function call:
del_lab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from submissions

del_lapp

Function call:
del_lapp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_lh

Function call:
del_lh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg_lh from kejadian

del_ltl

Function call:
del_ltl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from investigationanimals

del_nik

Function call:
del_nik (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ob

Function call:
del_ob (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from treatments/treatmentanimals

del_obi

Function call:
del_obi (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from treatments/treatmentanimals

del_pg

Function call:
del_pg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_p

Function call:
del_p (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from cases/syndromereports

del_pkb

Function call:
del_pkb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_pk

Function call:
del_pk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from caseresults

del_pkhi

Function call:
del_pkhi (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from caseresults

del_pkl

Function call:
del_pkl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from caseresults

del_pl

Function call:
del_pl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_pnc

Function call:
del_pnc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from receipt/nitrogenshipment

del_pneg

Function call:
del_pneg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from negativereports

del_pop

Function call:
del_pop (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from population

del_prp

Function call:
del_prp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from cases/signreports

del_ps

Function call:
del_ps (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_q

Function call:
del_q (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from questions

del_r

Function call:
del_r (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from response

del_rp

Function call:
del_rp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from slaughtertotals

del_rvak

Function call:
del_rvak (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from riwayatvaksinasi

del_sap

Function call:
del_sap (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_sbh

Function call:
del_sbh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from stokbibit

del_scs

Function call:
del_scs (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_shp

Function call:
del_shp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from stokbibit

del_sk

Function call:
del_sk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from movement/movementanimals

del_sko

Function call:
del_sko (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from stocktake/containershipment

del_skp

Function call:
del_skp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from konsentrat

del_sksr

Function call:
del_sksr (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_slab

Function call:
del_slab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from surveillance

del_slap

Function call:
del_slap (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from surveillance

del_snc

Function call:
del_snc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from stocktake/nitrogenshipment

del_sp

Function call:
del_sp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from sumberpenyakit

del_spk

Function call:
del_spk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from survpasar

del_srab

Function call:
del_srab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from rabiesreports

del_ssb

Function call:
del_ssb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from stocktake/strawshipment

del_sur

Function call:
del_sur (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from surveillance

del_tk

Function call:
del_tk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from tandaklinis

del_tl

Function call:
del_tl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from tindakanlain

del_tp

Function call:
del_tp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from penolakan

del_uc

Function call:
del_uc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from ujicepat

del_u

Function call:
del_u (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from cases/signreports

del_uh

Function call:
del_uh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from kejadian

del_ui

Function call:
del_ui (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from cases/signreports

del_vak

Function call:
del_vak (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from vaccinations

del_z

Function call:
del_z (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Delete msg from zoonoses

differential_diagnosis_report

Function call:
differential_diagnosis_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (DIFFERENTIAL_DIAGNOSIS_REPORT(casechid,usrid,1)).*;

excel_serial_to_date

Function call:
excel_serial_to_date (indate character varying) date
Schema:
backoffice
Description:
Converts an Excel serial date value (as a sting input) to a PostgreSQL date value. Detects if the string is actually a text version date format.

find_function

Function call:
find_function (functionname text) setof text
Schema:
backoffice
Description:
Find particular function and return name and namespace

format_phone

Function call:
format_phone (in_phone character varying, OUT result character varying) character varying
Schema:
reference
Description:
Given an international phone number, this function returns a local number.

SELECT FORMAT_PHONE(phone) FROM anytable;

phone is a phone number assumed to be in international format (that is, beginning with 62); however, checks are not done to ensure this is so. The first two characters of the phone number are replaced with 0.

Note: probably better to replace this function (where used) with local_phone, which DOES check whether have international format. (At this stage, identical copies of local_phone are stored in reference and backoffice.

general_disease_report

Function call:
general_disease_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (GENERAL_DISEASE_REPORT(casechid,usrid,1)).*;

generate_checkdigit

Function call:
generate_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number, return the checkdigit that should be appended to it.

generate_export_standard

Function call:
generate_export_standard (expid integer) integer
Schema:
reference
Description:
Generate an export spreadsheet standard

generatekeyselectcondition

Function call:
generatekeyselectcondition (flddata projfielddata[], keyfields integer[], outdata character varying[]) character varying
Schema:
metadata
Description:
Given a set of data values from a line, and information about the field definitions and which ones are key fields, return an SQL condition which can be used to select a record matching those key fields.

generate_lab_standards

Function call:
generate_lab_standards (standard integer, labcode character varying) integer
Schema:
reference

get_age

Function call:
get_age (birthday date, userid integer) character varying
Schema:
backoffice
Description:
Given a number, return it with a check digit appended to the end of it

get_animal_id_by_centre

Function call:
get_animal_id_by_centre (bibcode character varying, eartagcode character varying, creator integer, infratypeid integer, species integer, dob date, gender integer, OUT animalid integer) integer
Schema:
backoffice
Description:
Return Animal ID, create new record if not found

get_animal_id

Function call:
get_animal_id (ownerid integer, anident character varying, OUT animalid integer) integer
Schema:
backoffice
Description:
Returns the unique animal ID based on tag number and internal iSIKHNAS owner ID

get_animal_id

Function call:
get_animal_id (phone character varying, anident character varying, sms_userid integer, OUT animalid integer) integer
Schema:
backoffice
Description:
Look up Animal ID given Farmer Phone, SMS User ID and Anident

get_animal_types_name

Function call:
get_animal_types_name (antype_id integer, user_id integer, OUT animal_types_name character varying) character varying
Schema:
reference
Description:
This function returns the animal type in the viewer's language.

SELECT get_animal_types_name(animaltypeid, <<userid>>) FROM anytable;

anytable is any table that contains an animaltypeid field (for example, reference.animal_types (field name: id), or data.slaughtertotals (field name: animaltypeid).

getbasetypeoftype

Function call:
getbasetypeoftype (typeoid oid) character varying
Schema:
metadata
Description:
Given the oid of a type, keep returning its basetype until we get to an elemental type which does not have a further base type, then return the text name of that type. Usually this should return in only one iteration but one could, for example, have a domain type which is based on another domain type, which could require two iterations.

get_cell_ref

Function call:
get_cell_ref (ref character varying, rid integer, parentcell integer, rowcol character) character varying
Schema:
metadata
Description:
Get a cell reference

getcodelookupdata

Function call:
getcodelookupdata (lkuptypid integer) public.hstore
Schema:
metadata
Description:
Given the id of a field type, which must be a lookup type, return a hstore object which contains all the valid lookup codes, mapped to the associated numeric id which we will use internally to represent it.

getcodelookupquery

Function call:
getcodelookupquery (lkuptypid integer) character varying
Schema:
metadata
Description:
Create a query which, for a given lookup type, returns all currently valid lookup codes and their corresponding id values.

get_data_range

Function call:
get_data_range (trange character varying, rowcol character, OUT rng integer[]) integer[]
Schema:
metadata
Description:
Get the min and max non-empty row in an excel-like range

getdatatableid

Function call:
getdatatableid (tblnam character varying) integer
Schema:
metadata
Description:
Given the name of an iSikhnas data table, return its id number

getdaterange

Function call:
getdaterange (datetype integer, basedate date) daterange
Schema:
reference
Description:
Given a date range type, return a date range object

getdaterangelimits

Function call:
getdaterangelimits (datetype integer, basedate date) date[]
Schema:
reference
Description:
Return an array of two dates which are the inclusive ends of the date range specified by the date type integer.

get_differential

Function call:
get_differential (sms_caseid integer, sms_userid integer, lim integer, id integer, disease text, sx double precision) setof record
Schema:
reference
Description:
Get differentials for a particular case

get_disease_name

Function call:
get_disease_name (d_code integer, user_id integer, OUT d_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a diseaseid, and 2) the userid. The function returns the disease name, in the language of the report viewer.

SELECT  get_disease_name(diseaseid, <<userid>>) FROM anytable;

get_disease_names

Function call:
get_disease_names (d_codes integer[], user_id integer, OUT d_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. Its parameters are 1) an array of diseaseids, and 2) the userid. The function returns a comma-separated list of the corresponding disease names, in the language of the report viewer.

SELECT  get_disease_names(diseaseidarray, <<userid>>) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_disease_names(ARRAY_AGG(diseaseid)) FROM anytable GROUP BY item1;

get_drug_names

Function call:
get_drug_names (d_codes integer[], OUT d_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. Its parameters are an array of drugids (e.g. for vaccines, this is 15). The function returns a comma-separated list of the corresponding vames. SELECT get_drug_names(drugidarray) FROM drugs;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG: SELECT item1, get_drug_names(ARRAY_AGG(drugid)) FROM anytable GROUP BY item1;

get_dup_anident

Function call:
get_dup_anident (ownerid character varying, anident character varying, userid integer, OUT dup_anident character varying) character varying
Schema:
backoffice
Description:
Get duplicate Anident

get_farmer_id

Function call:
get_farmer_id (phone bigint, sms_userid integer, OUT farmerid integer) integer
Schema:
backoffice
Description:
Get Farmer ID given Farmer Phone (Big Int) and SMS User ID

get_farmer_id

Function call:
get_farmer_id (phone character varying, sms_userid integer, OUT farmerid integer) integer
Schema:
backoffice
Description:
Get Farmer ID given Farmer Phone (Character Varying) and SMS User ID

get_farmer_ids

Function call:
get_farmer_ids (phone character varying, sms_userid integer, OUT farmerid integer) setof integer
Schema:
backoffice
Description:
Get Farmer ID/s given Farmer Phone (Character Varying) and SMS User ID

get_group_name

Function call:
get_group_name (groupid integer, user_id integer, OUT group_name character varying) character varying
Schema:
reference
Description:
This functions returns the human readable name of a group, given the groupid, returned in the language of the viewer of the report.

SELECT get_group_name(groupid, <<userid>>) 
FROM ANYTABLE;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_group_names

Function call:
get_group_names (groupids integer[], user_id integer, OUT group_names character varying) character varying
Schema:
reference
Description:
Given an array of groupids, this functions returns a human readable list formatted for HTML display of group names, returned in the language of the viewer of the report.

SELECT get_group_names(groupids, <<userid>>) 
FROM ANYTABLE;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_hostname

Function call:
get_hostname (OUT hostname text) text
Schema:
metadata
Description:
This function is not as smart as it might appear. It relies on the /etc/hostname file being set during the cloud-init process (or sometime thereafter).
This function will not work on a replica as it creates a temporary table to store the data during loading. [BM]

get_infra_id

Function call:
get_infra_id (infra_type character varying, infra_code character varying, sms_userid integer) integer
Schema:
reference
Description:
Get infrastructure ID

get_infra_id

Function call:
get_infra_id (infra_type integer, infra_code character varying, sms_userid integer) integer
Schema:
reference
Description:
Get infrastructure ID

get_infrastructure_name

Function call:
get_infrastructure_name (infra_id integer, OUT i_name character varying) character varying
Schema:
reference
Description:
Given the infrastructureid, the function returns the name of the infrastructure.

SELECT get_infrastructure_name(infraid) FROM anytable;

get_infrastructure_names

Function call:
get_infrastructure_names (infra_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of infrastructureids. The function returns a semicolon-separated list of the corresponding infrastructure names.

SELECT  get_infrastructure_names(infrastructureidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_infrastructure_names(ARRAY_AGG(infrastructureid)) FROM anytable GROUP BY item1;

get_ivm

Function call:
get_ivm (start_date date, end_date date, OUT created_at timestamp with time zone, OUT district_code integer, OUT district_name character varying, OUT province_id integer, OUT epi_lab_number integer, OUT epi_lab_ref_number character varying, OUT epi_year character varying, OUT htype integer, OUT ntype integer, OUT id integer, OUT isolate_name character varying, OUT virology_lab_identifier character varying) setof record
Schema:
backoffice

get_lang

Function call:
get_lang (user_id integer) integer
Schema:
backoffice
Description:
Return a user's preferred language

get_location

Function call:
get_location (loccode character varying, OUT locationname character varying) character varying
Schema:
reference
Description:
Get a location name based on ID

get_location

Function call:
get_location (locid integer, OUT locationname character varying) character varying
Schema:
reference
Description:
Get a location name based on ID

get_location_id

Function call:
get_location_id (loccode character varying) integer
Schema:
reference
Description:
Given a location code, return the id of the location with that code, or null if it is an invalid code.

get_location_id

Function call:
get_location_id (loccode character varying, userid integer, force_village boolean) integer
Schema:
reference
Description:
Given a location code entered by a user, return the id of the location to use, or null if no valid village-level location code can be determined.

get_locationlevel_name

Function call:
get_locationlevel_name (ll_code integer, user_id integer, OUT ll_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a locationlevel code, and 2) the userid. The function returns the location level name, in the language of the report viewer.

SELECT  get_locationlevel_name(locationlevelcode, <<userid>>) FROM anytable;

get_location_names

Function call:
get_location_names (loc_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of locationids. The function returns a semicolon-separated list of the corresponding location names.

SELECT  get_location_names(locationidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_location_names(ARRAY_AGG(locationid)) FROM anytable GROUP BY item1;

get_location_names_rev

Function call:
get_location_names_rev (loc_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of locationids. The function returns a semicolon-separated list of the corresponding location names, with levels ordered from general to specific.

SELECT  get_location_names_rev(locationidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_location_names_rev(ARRAY_AGG(locationid)) FROM anytable GROUP BY item1;

get_location_rev

Function call:
get_location_rev (loccode character varying, OUT locationname character varying) character varying
Schema:
reference
Description:
Get a location name based on code

get_location_rev

Function call:
get_location_rev (locid integer, OUT locationname character varying) character varying
Schema:
reference
Description:
Get a location name based on ID

getlookuplistquery

Function call:
getlookuplistquery (lkuptypid integer, userid integer) character varying
Schema:
metadata
Description:
Given a lookup type, return a query which can be used to return a list suitable for display in places such as a filter listbox, in the users preferred language.

get_messagecode

Function call:
get_messagecode (msgid_code bigint, OUT mcode character varying) character varying
Schema:
reference
Description:
The parameter is a msgid. The function returns the messagecode.

SELECT  get_messagecode(msgid) FROM anytable;

get_messagecode_name

Function call:
get_messagecode_name (msgid_code bigint, user_id integer, OUT mt_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a msgid, and 2) the userid. The function returns the messagecode name, in the language of the report viewer.

SELECT  get_messagecode_name(msgid, <<userid>>) FROM anytable;

get_messagetypeid

Function call:
get_messagetypeid (msgid bigint, OUT mtypeid integer) integer
Schema:
reference
Description:
The parameter is a msgid. The function returns the messagetypeid.

SELECT  get_messagetypeid(msgid) FROM anytable;

get_month_name

Function call:
get_month_name (month_id integer, user_id integer, OUT month_name character varying) character varying
Schema:
reference
Description:
This function returns the name of the day of the week in the viewer's language. It can be used on a date field as shown:

SELECT get_month_name(EXTRACT(MONTH FROM reportdate)::integer, <<userid>>) FROM anytable;

get_owner_id

Function call:
get_owner_id (ownername character varying, owneraddress character varying, ownerphone phonenumber, ownerlocationid integer, creator integer, ownermsgid integer, OUT ownerid integer) integer
Schema:
backoffice
Description:
For use in the infolab spreadsheet import

get_owner_id

Function call:
get_owner_id (ownername character varying, owneraddress character varying, ownerphone phonenumber, ownerlocationid integer, ownermsgid integer, OUT ownerid integer) integer
Schema:
backoffice
Description:
Stephen Kirby Created this for the use in the infolab spreadsheet import

get_palette_code

Function call:
get_palette_code (palette_id integer, OUT palette_code character varying) character varying
Schema:
reference
Description:
This function returns the R-readable code for the selected Colorbrewer palette, for use in charts.

SELECT GET_PALETTE_CODE(<<palette>>) FROM anytable;

<<palette>> is brewerpalettes.id, or is selected as a lookupone filter through the interface.

get_param

Function call:
get_param (pname character varying) character varying
Schema:
backoffice
Description:
Returns the specified parameter value from the parameters table. These parameters are intended for general system variables.

get_pdsr_hpai_village_status

Function call:
get_pdsr_hpai_village_status (desa integer, basedate timestamp without time zone, period integer) integer
Schema:
reference
Description:
Check village HPAI status

get_pop

Function call:
get_pop (species integer, locationid integer, reportdate date, population integer) setof record
Schema:
data
Description:
Returns a table contining the most up to date population data for all villages for the specified species. Table fields are locationid, reportdate, population.

get_population

Function call:
get_population (sp integer, loc integer, OUT total integer, OUT createdon timestamp without time zone) record
Schema:
backoffice
Description:
Returns the most recent population data for the specified species and location. The function returns a record with two fields: total and createdon. Usage:

 select (get_population(22,67720)).total ;
 select * from get_population(22,67720);
 select createdon from get_population(22,6772);

getprojectfielddata

Function call:
getprojectfielddata (prjid integer) projfielddata[]
Schema:
metadata
Description:
Given a project code, return an array of records, each of which contains all information needed to process one of the input fields in TranslateInputValue.

get_purpose_name

Function call:
get_purpose_name (p_code integer, user_id integer, OUT p_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a purposeid, and 2) the userid. The function returns the purpose name, in the language of the report viewer.

SELECT  get_purpose_name(purposeid, <<userid>>) FROM anytable;

getrectodeleteid

Function call:
getrectodeleteid (tblnm character varying, flddata projfielddata[], keyfields integer[], outdata character varying[]) integer
Schema:
metadata
Description:
Given a set of data values from a line, and information about the field definitions and which ones are key fields, see if there is an existing record with the same key values, and return its id if so, or NULL otherwise.

get_related_table

Function call:
get_related_table (_table text, a text, b text, c text, d text) setof record
Schema:
lab
Description:
Get all related tables

getroottypeoftype

Function call:
getroottypeoftype (typeoid oid) oid
Schema:
metadata
Description:
Given the oid of a type, keep returning its basetype until we get to an elemental type which does not have a further base type. Usually this should return in only one iteration but one could, for example, have a domain type which is based on another domain type, which could require two iterations.

get_sign_name

Function call:
get_sign_name (s_code integer, user_id integer, OUT s_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a signid, and 2) the userid. The function returns the sign name, in the language of the report viewer.

SELECT  get_sign_name(signid, <<userid>>) FROM anytable;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
backoffice
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
reference
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
sms
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_singlecolour_code

Function call:
get_singlecolour_code (OUT singlecolour_code character varying) character varying
Schema:
reference
Description:
This function returns the 'R-readable' code for the iSIKHNAS standard colour, to be passed to R for use in charts that have a single colour. The colour is hard-wired into the function; currently, it is set as 'firebrick'.

SELECT GET_SINGLECOLOUR_CODE() FROM anytable;

get_sms_cname

Function call:
get_sms_cname (sms_id integer, user_id integer, OUT sms_cname character varying) character varying
Schema:
reference
Description:
Returns the code and name of the sms in the form code: name, based on the sms id and the user id to determine the correct language

SELECT get_sms_cname(smsid, <<userid>>) FROM anytable;

get_sms_cnames

Function call:
get_sms_cnames (sms_ids integer[], user_id integer, OUT sms_cnames character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. It returns a comma-separated list (on separate lines) of the code and name of the sms in the form code: name, based on the sms id and the user id to determine the correct language

SELECT get_sms_cnames(smsidarray, <<userid>>) FROM anytable;

get_sms_format_description

Function call:
get_sms_format_description (sms_start_code character varying, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Stub for real CUSUM to determine if the current period's level or reporting is unusual

get_sms_format

Function call:
get_sms_format (sms_msg_id integer, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Returns the sms message format in the specified language for a given message (specified by the id of the message in the metadata.sms table

Used for automated system documentation and generation of training materials.

get_sms_format

Function call:
get_sms_format (sms_start_code character varying, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Returns the sms message format in the specified language for a given message (specified by the start code of the message)

Used for automated system documentation and generation of training materials.

get_sms_format_user

Function call:
get_sms_format_user (sms_msg_id integer, uid integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Wrapper for get_sms_format. This version takes the userid and returns the format in the appropriate langage.

get_species_hier_id

Function call:
get_species_hier_id (spec_id integer, OUT species_id integer) integer
Schema:
reference
Description:
Returns the highest-level id of the species, based on the species id SELECT get_species_hier_id(speciesid, <<userid>>) FROM anytable;

get_species_hier_name

Function call:
get_species_hier_name (spec_id integer, user_id integer, OUT species_name character varying) character varying
Schema:
reference
Description:
Returns the highest-level name of the species, based on the species id and the user id to determine the correct language

SELECT get_species_hier_name(speciesid, <<userid>>) FROM anytable;

get_species_name

Function call:
get_species_name (spec_id integer, user_id integer, OUT species_name character varying) character varying
Schema:
reference
Description:
Returns the name of the species, based on the species id and the user id to determine the correct language

SELECT get_species_name(speciesid, <<userid>>) FROM anytable;

get_string

Function call:
get_string (trans_key character varying, OUT st_out character varying) character varying
Schema:
backoffice
Description:
Returns a string from the translation table (corresponding to the specified key) in the default language (Indonesian)

get_string

Function call:
get_string (trans_key character varying, user_id integer, OUT st_out character varying) character varying
Schema:
backoffice
Description:
Retuns a string from the tranlsation table (corresponding to the specified key) in the default language (Indonesian)

get_syndrome_name

Function call:
get_syndrome_name (syndromeid integer, user_id integer, OUT syndrome_name character varying) character varying
Schema:
reference
Description:
Returns the name of the syndrome in the language of the viewer (user), given the syndromeid and userid.

SELECT get_syndrome_name(syndromeid, <<userid>>) FROM anytable;

get_table_data

Function call:
get_table_data (jid integer, tid integer, rid integer, sheet character varying, parentrow integer) integer
Schema:
metadata
Description:
This function uses recursion to loop down through subsequent tables / sheets of data to allow insert of subdata from sheets

gettableinfodata

Function call:
gettableinfodata (prjid integer) tableinforecord[]
Schema:
metadata
Description:
This function collects information about project tables needed by UploadInputData

get_target_name

Function call:
get_target_name (t_code integer, user_id integer, OUT t_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a targetid, and 2) the userid. The function returns the target name, in the language of the report viewer.

SELECT  get_target_name(targetid, <<userid>>) FROM anytable;

get_tindakan_name

Function call:
get_tindakan_name (tindakan_id integer, user_id integer, OUT tindakan_name character varying) character varying
Schema:
reference
Description:
Returns the name of the tindakan, based on the tindakan id and the user id to determine the correct language

SELECT get_tindakan_name(tindakanid, <<userid>>) FROM anytable;

get_treatment_script

Function call:
get_treatment_script (user_id integer, OUT treatment_code character varying) character varying
Schema:
reference
Description:
This function returns the string that controls formatting of the dose information, according to the viewer's language.

SELECT get_treatment_script(<<userid>>) FROM anytable;

Script (English): '%s head %s @ %s %s with %s'

Script (Indonesian): '%s ekor %s @ %s %s dengan %s'

get_treatmenttype_name

Function call:
get_treatmenttype_name (d_code integer, user_id integer, OUT dt_name character varying) character varying
Schema:
reference
Description:
This functions returns the human readable name of a treatment type, given the treatmenttypeid, returned in the language of the viewer of the report. SELECT get_treatmenttype_name(treatmenttypeid, <<userid>>) FROM ANYTABLE; If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

gettypeid

Function call:
gettypeid (lookuptype character varying) integer
Schema:
metadata
Description:
Given the name of a lookup (or other) type in our fieldtypes table, return the id

get_user_groups

Function call:
get_user_groups (userid integer, user_id integer, OUT user_groups character varying) character varying
Schema:
reference
Description:
This functions returns a human readable list of the groups that a user is in, returned in the language of the viewer of the report.

SELECT id, get_user_groups(id, <<userid>>) 
FROM users;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_vaccination_order

Function call:
get_vaccination_order (animalid bigint, vaccineid bigint, OUT vorder integer) integer
Schema:
reference
Description:
Given a animal id, vaccine id, and return the vaccine order by diseases id.

get_weekday_name

Function call:
get_weekday_name (weekday_id integer, user_id integer, OUT weekday_name character varying) character varying
Schema:
reference
Description:
This function returns the name of the day of the week in the viewer's language. It can be used on a date field as shown:

SELECT get_weekday_name((CASE WHEN EXTRACT(DOW FROM sessionstart::TIMESTAMP)=0 THEN 7 ELSE EXTRACT(DOW FROM sessionstart::TIMESTAMP) END)::integer, <<userid>>) FROM anytable;

hierindent

Function call:
hierindent (name character varying, hiercode character varying, html boolean, OUT indented_name character varying) character varying
Schema:
backoffice
Description:
Given a hierarchical code returns it indented

hier_parents

Function call:
hier_parents (code character varying, toplevel integer) setof character varying
Schema:
backoffice
Description:
Given a hierachical code in the form 10.2.1.5, this function returns a set of parent codes, up to the specified level, or up to level 1 if not specified. For location codes (without '.') it returns higher level codes.

hiersort

Function call:
hiersort (hiercode character varying, OUT sortcode integer[]) integer[]
Schema:
backoffice
Description:
Given a hierarchical code returns it as an array

im_parse

Function call:
im_parse (msg character varying, user_mail character varying, msg_id bigint) void
Schema:
sms
Description:
Parse incoming SMS

im_parse_xmpp

Function call:
im_parse_xmpp (msg character varying, user_im character varying, msg_id bigint, OUT insert_id bigint) bigint
Schema:
sms
Description:
This is essentially the same as im_parse, but takes the mobile phone number instead of the email address. Note: Unlike the im_parse() version of the function (which returns void), this is returning the insert id... it was for debugging, is there any reason not to?[bm]
Example call:

SELECT im_parse_xmpp('kode', '081311335256@isikhnas.com', '2233915044836935');

infection_source_report

Function call:
infection_source_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (INFECTION_SOURCE_REPORT(casechid,usrid,1)).*;

insertprojectrecords

Function call:
insertprojectrecords (userid integer, fielddata projfielddata[], tableinfo tableinforecord[], datavaluesall character varying[], auxtbl character varying, parentline integer, tblidx integer, parentrecid integer) inputprocessresult
Schema:
metadata
Description:
An auxiliary routine used by UploadInputData to do the work of inserting the new data, using the data structures created by UploadInputData. It will also delete any existing records whose keyfields match new data being inserted.

install_rcmd

Function call:
install_rcmd ( text) text
Schema:
backoffice

int_phone

Function call:
int_phone (l_phone bigint, OUT int_phone character varying) character varying
Schema:
reference
Description:
Convert a phone number from local to international format

int_phone

Function call:
int_phone (l_phone character varying, OUT int_phone character varying) character varying
Schema:
reference
Description:
Convert a phone number from local to international format

is_child

Function call:
is_child (parent character varying, child character varying, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Determine if any hierarchical code is a child of any other hierarchical code

ischildlocation

Function call:
ischildlocation (parentid integer, locid integer[]) boolean
Schema:
reference
Description:
Return true if any loc id is a child of the parent id, or false otherwise.

ischildlocation

Function call:
ischildlocation (parentid integer, locid integer) boolean
Schema:
reference
Description:
See if a location is the child of another location. Do this by checking that the code of the potential child location begins with the code of the parent location. Or, if the parent location has a code of "0" (i.e. Indonesia), that code is a parent of all other locations.

ischildlocation

Function call:
ischildlocation (parentids integer[], locid integer) boolean
Schema:
reference
Description:
Return true if loc id is a child location of any of the parent ids (including if it is the same location), or false otherwise.

is_child_sign

Function call:
is_child_sign (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Returns a boolean (TRUE,FALSE) depending on whether the sign is a child of another sign.

SELECT is_child_sign(parentsignid, childsignid) FROM anytable;

is_child_sign

Function call:
is_child_sign (parentid integer, childids integer[], OUT is_at_least_one_child boolean) boolean
Schema:
backoffice
Description:
Returns a boolean (TRUE,FALSE) depending on whether at least one of the signs in the array is a child of another sign.

SELECT is_child_sign(parentsignid, childsignidarray) FROM anytable;

is_child_species

Function call:
is_child_species (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Return whether a species is a child of another species in the hierarchy

is_child_speciestype

Function call:
is_child_speciestype (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Sikhnas codes are no longer used. This now behaves exactly like is_child_species

is_child_speciestype

Function call:
is_child_speciestype (parentids integer[], childid integer) boolean
Schema:
backoffice
Description:
Return whether a species is a child of another species in the hierarchy

is_hpai_compatible_event

Function call:
is_hpai_compatible_event (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Determines if there has been an HPAI-compatible event in the specified village in the specified time period. Time period defaults to 14 days, base date defaults to now. The function checks a) for any response (R) report that includes HPAI as the differential diagnosis, and for any priority syndrome report (P) for mortality in poultry that does not yet have a response report.

is_hpai_control_happening

Function call:
is_hpai_control_happening (desa integer, basedate timestamp without time zone, period integer, OUT control boolean) boolean
Schema:
reference
Description:
Determines if there are currently HPAI control activities in the specified village, defined as biosecurity measures reported with Tindakan Lain.

is_hpai_lab_test_pos

Function call:
is_hpai_lab_test_pos (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns true of there has been at least one positive HPAI rapid test report from the village in the specified time period

is_hpai_rapid_test_pos

Function call:
is_hpai_rapid_test_pos (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns true of there has been at least one positive HPAI rapid test report from the village in the specified time period

is_in_user_area

Function call:
is_in_user_area (child_userid integer, parent_userid integer) boolean
Schema:
reference
Description:
See if a user is the child of another user.

is_in_user_arearev

Function call:
is_in_user_arearev (child_userid integer, parent_userid integer) boolean
Schema:
reference
Description:
See if a user is the child of another user.

isnumeric

Function call:
isnumeric ( text) boolean
Schema:
backoffice
Description:
Check if input is numeric

is_open_hpai_case

Function call:
is_open_hpai_case (desa integer, basedate timestamp without time zone, period integer, OUT control boolean) boolean
Schema:
reference
Description:
Is there an active (unresolved) case with a definitive diagnosis of HPAI in the village?

is_parent_area

Function call:
is_parent_area (child integer, parent integer, OUT is_parent boolean) boolean
Schema:
backoffice
Description:
Returns true or false, depending on whether the parent is a parent of the child i.e. the child location lies within the area of the parent location.

is_permitted2

Function call:
is_permitted2 (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice
Description:
Return true or false, depending on whether a user has permission to perform a particular task. This returns the value from the user permission record if it exists, otherwise true if any of the groups that the user belongs to has the permission granted.

is_permitted_group

Function call:
is_permitted_group (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice
Description:
Return true or false, depending on whether a user has permission to perform a particular task through one of the groups he is a member of (ignoring any user_permissions record).

is_permitted

Function call:
is_permitted (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice
Description:
Return true or false, depending on whether a user has permission to perform a particular task. This returns the value from the user permission record if it exists, otherwise true if any of the groups that the user belongs to has the permission granted.

is_permitted

Function call:
is_permitted (perm text, uid integer, OUT allowed boolean) boolean
Schema:
backoffice
Description:
Return whether a user has a particular permission whose name is specified. This is implemented in terms of another overload of this function.

is_terminal_infrastructure

Function call:
is_terminal_infrastructure (infraid integer, OUT terminal boolean) boolean
Schema:
backoffice
Description:
Identifies which records are the last levels in the hierarchy (have no children)

is_under_surveillance

Function call:
is_under_surveillance (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns the HPAI status of the specified village: 0: No surveillance in this village; 1: Apparently free; 2: Suspect; 3: Infected

list_functions

Function call:
list_functions (OUT schema character varying, OUT fname character varying, OUT foid oid, OUT returntype character varying, OUT pos integer, OUT direction character, OUT argname character varying, OUT datatype character varying, OUT description character varying) setof record
Schema:
backoffice
Description:
Generates a list of all functions in the database and returns the schema, function name, arguments, return type and description

local_phone

Function call:
local_phone (int_phone character varying, OUT l_phone character varying) character varying
Schema:
backoffice
Description:

SELECT local_phone(phone) FROM anytable;

This function checks to see if the phone number is prepended by '0'. If it is, the phone number is assumed to be already in local format. Otherwise, it is assumed to be in international format, and the first two digits are removed and replaced with '0'.

Note: this function is an improved version of reference.format_phone, but is identical to reference.local_phone; one of these should be removed.

log_error

Function call:
log_error (sms_userid integer, sms_msg character varying) integer
Schema:
sms
Description:
Add record to sms errors

log_import_error

Function call:
log_import_error (iref character varying, imsg character varying, isheet character varying) integer
Schema:
metadata
Description:
Given the name of a lookup (or other) type in our fieldtypes table, return the id

luhn_checksum

Function call:
luhn_checksum (inval bigint) integer
Schema:
reference
Description:
Calculate a Luhn checksum of the supplied number.

Functions to handle Luhn checksums: luhn_checksum, verify_checksum, generate_checkdigit, add_checkdigit, remove_checkdigit.

Code has been modified from http://wiki.postgresql.org/wiki/Luhn_algorithm (with some help from the Python implementation in the above Wikipedia page). luhn_checksum was factored out of the original code so it could be reused in both verify_checksum and verify_checksum.

Also fixed a bug in the original code: LOG(inval) had to be changed to LOG(inval+1) to correctly generate check digits for powers of ten.

Case ids, for example, are stored as integers without checksums. When passed as a reference to external users, a checksum digit is appended. When users supply a case id, it should include the checksum digit, and we verify that it has a checksum of zero. If not, it is an invalid number. This detects most common errors such as single digit error, transpositions, etc. Stripping off the checksum digit will get back the internal caseid.

Tests: the following statements should all evaluate to true.

SELECT luhn_checksum(10000) = 1; SELECT luhn_checksum(10009) = 0; SELECT verify_checksum(10000) = false; SELECT verify_checksum(10009) = true; SELECT generate_checkdigit(1000) = 9; SELECT add_checkdigit(1000) = 10009; SELECT remove_checkdigit(10009) = 1000;

SELECT luhn_checksum(5163103000504460) = 2; SELECT verify_checksum(5163103000504460) = false; SELECT verify_checksum(5163103000504468); SELECT generate_checkdigit(516310300050446) = 8; SELECT add_checkdigit(516310300050446) = 5163103000504468; SELECT remove_checkdigit(5163103000504468) = 516310300050446;

make_regex

Function call:
make_regex (sms_msg_id integer, group_regex boolean) character varying
Schema:
metadata
Description:
Generates regex to parse an SMS message.

make_sms_function

Function call:
make_sms_function (sms_msg_id integer) character varying
Schema:
metadata
Description:
Generates PL/SQL function to process an SMS message.

make_sms_get_vars

Function call:
make_sms_get_vars (sms_msg_id integer, grouped boolean, OUT declarations character varying, OUT funccode character varying, OUT fieldnames character varying[], OUT insertvars character varying[]) record
Schema:
metadata
Description:
Get vars for SMS

make_sms_info

Function call:
make_sms_info (sms_msg_id integer) character varying
Schema:
metadata
Description:
Generates PL/SQL function to process an SMS message requesting information - no data stored in database. Used for help etc

msg_ah

Function call:
msg_ah (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ai

Function call:
msg_ai (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_bb

Function call:
msg_bb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_bcs

Function call:
msg_bcs (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_bcsk

Function call:
msg_bcsk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_bh

Function call:
msg_bh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cip

Function call:
msg_cip (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckab

Function call:
msg_ckab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckh

Function call:
msg_ckh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckhi

Function call:
msg_ckhi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cki

Function call:
msg_cki (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckjs

Function call:
msg_ckjs (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckl

Function call:
msg_ckl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cko

Function call:
msg_cko (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckp

Function call:
msg_ckp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckpr

Function call:
msg_ckpr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cks

Function call:
msg_cks (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ckt

Function call:
msg_ckt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cpd

Function call:
msg_cpd (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cp

Function call:
msg_cp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_csr

Function call:
msg_csr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cul

Function call:
msg_cul (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cv

Function call:
msg_cv (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_cvr

Function call:
msg_cvr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_d

Function call:
msg_d (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dh

Function call:
msg_dh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dhk

Function call:
msg_dhk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dhm

Function call:
msg_dhm (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dhp

Function call:
msg_dhp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dip

Function call:
msg_dip (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dkb

Function call:
msg_dkb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dkl

Function call:
msg_dkl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dnc

Function call:
msg_dnc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dp

Function call:
msg_dp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dsb

Function call:
msg_dsb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_dx

Function call:
msg_dx (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_gi

Function call:
msg_gi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_h

Function call:
msg_h (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_hk

Function call:
msg_hk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_hpt

Function call:
msg_hpt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ib

Function call:
msg_ib (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ih

Function call:
msg_ih (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_image

Function call:
msg_image (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_io

Function call:
msg_io (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_jbh

Function call:
msg_jbh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_jh

Function call:
msg_jh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ka

Function call:
msg_ka (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_kdp

Function call:
msg_kdp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_kdx

Function call:
msg_kdx (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kgg

Function call:
msg_kgg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_kmt

Function call:
msg_kmt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kneg

Function call:
msg_kneg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kode

Function call:
msg_kode (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_kom

Function call:
msg_kom (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_kt

Function call:
msg_kt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lab

Function call:
msg_lab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lapd

Function call:
msg_lapd (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lapk

Function call:
msg_lapk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lapp

Function call:
msg_lapp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lapsk

Function call:
msg_lapsk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lh

Function call:
msg_lh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_link

Function call:
msg_link (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_lkom

Function call:
msg_lkom (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lok

Function call:
msg_lok (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ltl

Function call:
msg_ltl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
backoffice

msg_ltl

Function call:
msg_ltl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_n

Function call:
msg_n (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_nik

Function call:
msg_nik (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ob

Function call:
msg_ob (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_obi

Function call:
msg_obi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pg

Function call:
msg_pg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_p

Function call:
msg_p (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pkb

Function call:
msg_pkb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pk

Function call:
msg_pk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pkhi

Function call:
msg_pkhi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pkl

Function call:
msg_pkl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pl

Function call:
msg_pl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pnc

Function call:
msg_pnc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pneg

Function call:
msg_pneg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_pop

Function call:
msg_pop (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_prp

Function call:
msg_prp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_psb

Function call:
msg_psb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ps

Function call:
msg_ps (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_q

Function call:
msg_q (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_r

Function call:
msg_r (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_rp

Function call:
msg_rp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_rvak

Function call:
msg_rvak (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sap

Function call:
msg_sap (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sbh

Function call:
msg_sbh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_scs

Function call:
msg_scs (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_shp

Function call:
msg_shp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
NOTE: This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sk

Function call:
msg_sk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sko

Function call:
msg_sko (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_skp

Function call:
msg_skp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sksr

Function call:
msg_sksr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_slab

Function call:
msg_slab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_slap

Function call:
msg_slap (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_snc

Function call:
msg_snc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sp

Function call:
msg_sp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_spk

Function call:
msg_spk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_srab

Function call:
msg_srab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ssb

Function call:
msg_ssb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_sur

Function call:
msg_sur (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_tk

Function call:
msg_tk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_tl

Function call:
msg_tl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_tp

Function call:
msg_tp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_uc

Function call:
msg_uc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ue

Function call:
msg_ue (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_u

Function call:
msg_u (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_uh

Function call:
msg_uh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_ui

Function call:
msg_ui (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_vak

Function call:
msg_vak (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_vsk

Function call:
msg_vsk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

msg_z

Function call:
msg_z (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
This function was automatically generated by metadata.make_sms_function(). To make changes, please update the metadata in metadata.sms and metadata sms_fields, and regenerate this function.

nama

Function call:
nama (first character varying, sur character varying, OUT nama character varying) character varying

Schema:
reference
Description:
This function also uses regular expression test on the firstname to remove any errant characters (newline or carriage returns) that might have crept in.
trim( coalesce( initcap(trim(regexp_replace(first, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' )))

nama

Function call:
nama (usrid integer, OUT nama character varying) character varying
Schema:
reference
Description:
Get user name based on given ID)

outcome_report

Function call:
outcome_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (OUTCOME_REPORT(casechid,usrid,1)).*;

parse_xm

Function call:
parse_xm (fname character varying, sender character varying, email_msgid bigint, infname character varying, OUT functionstatus integer, OUT lab2id text) record
Schema:
backoffice

pg_stat_statements

Function call:
pg_stat_statements (showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision) setof record
Schema:
backoffice

plr_array_accum

Function call:
plr_array_accum ( double precision[], double precision) double precision[]
Schema:
backoffice

plr_array_push

Function call:
plr_array_push ( double precision[], double precision) double precision[]
Schema:
backoffice

plr_get_raw

Function call:
plr_get_raw ( bytea) bytea
Schema:
backoffice

plr_inline_handler

Function call:
plr_inline_handler ( internal) void
Schema:
backoffice

plr_set_display

Function call:
plr_set_display ( text) text
Schema:
backoffice

plr_set_rhome

Function call:
plr_set_rhome ( text) text
Schema:
backoffice

plr_singleton_array

Function call:
plr_singleton_array ( double precision) double precision[]
Schema:
backoffice

plr_validator

Function call:
plr_validator ( oid) void
Schema:
backoffice

print_custom_lab_report

Function call:
print_custom_lab_report (labsubmission integer, OUT columns integer, OUT epinumd text, OUT infrastructureid integer, OUT emailto text, OUT response json) record
Schema:
lab
Description:
Print a custom lab report

print_custom_lab_test

Function call:
print_custom_lab_test (labsubmission integer, OUT table_data json) json
Schema:
lab
Description:
Test - delete?

priority_disease_report

Function call:
priority_disease_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (PRIORITY_DISEASE_REPORT(casechid,usrid,1)).*;

put_on_queue

Function call:
put_on_queue (msg json, queue character varying) character varying
Schema:
sms

rapid_tests_report

Function call:
rapid_tests_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (RAPID_TESTS_REPORT(casechid,usrid,1)).*;

remove_checkdigit

Function call:
remove_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number which contains a check digit, return the original number minus the check digit. The caller should check that verify_checksum(inval) is true before calling this function.

rep_ai

Function call:
rep_ai (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get report from aireports, productionsystems and aireportanimals

rep_case

Function call:
rep_case (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on a specific case

rep_caseresult

Function call:
rep_caseresult (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on a specific case result

report_custom_import_errors

Function call:
report_custom_import_errors (fname character varying, email character varying) integer
Schema:
metadata

rep_rabies

Function call:
rep_rabies (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on Rabies

rep_response

Function call:
rep_response (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Report on response

rep_signs

Function call:
rep_signs (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on the signs of a particular case

rep_syndromes

Function call:
rep_syndromes (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on the syndromes of a particular case

rep_treatment

Function call:
rep_treatment (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data
Description:
Get a report on the treatment undertaken on a particular case

save_report_file_to_s3

Function call:
save_report_file_to_s3 (filename character varying) integer
Schema:
backoffice

save_report_params_to_ddb

Function call:
save_report_params_to_ddb (rid integer, uid integer, link character varying, params jsonb, srid integer, eventtime timestamp with time zone, runtime numeric) integer
Schema:
backoffice

send_client_report

Function call:
send_client_report (epinumber character varying, labuserid integer) boolean
Schema:
lab
Description:
Sending client report

send_email

Function call:
send_email (sendto character varying[], subject character varying, body character varying, files character varying[], html character varying) integer
Schema:
backoffice
Description:
Send an email with specified body text and attachments:

  • sendto: recipient's email address (only one recipient allowed currently)
  • subject: subject line
  • body: body text of the message
  • files: varchar array of file names including paths (must be readable by the db user)

The senders email is automatically set to 'iSIKHNAS <data@isikhnas.com>'

send_error_msg

Function call:
send_error_msg (sms_phone character varying, sms_msg character varying, sms_msgid bigint, sms_userid integer) integer
Schema:
backoffice
Description:
Sends an error message and logs the error

send_error_msg

Function call:
send_error_msg (sms_phone character varying, sms_msg character varying, sms_msgid bigint, sms_userid integer) integer
Schema:
sms
Description:
Sends an error message and logs the error

sendmessage

Function call:
sendmessage (labid integer, email text, OUT argv text) text
Schema:
backoffice

send_sms

Function call:
send_sms (phone character varying, msg character varying, priority boolean, modem character varying) boolean
Schema:
sms
Description:
Function to send SMS

send_sms_old

Function call:
send_sms_old (phone character varying, msg character varying, priority boolean, modem character varying) character varying
Schema:
sms

send_template_report

Function call:
send_template_report (rep_template character varying, email character varying, recid integer, database character varying) character varying
Schema:
backoffice

settableattributes

Function call:
settableattributes (tblnm character varying) void
Schema:
metadata
Description:
Set the default owner and rights for any tables or views created

sms_parse

Function call:
sms_parse (msg character varying, msg_type character varying, msg_id bigint) void
Schema:
sms
Description:
Function to parse incoming SMS

sms_process

Function call:
sms_process (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms
Description:
Process an incoming text message

spatial_aggregate_to_new_parent

Function call:
spatial_aggregate_to_new_parent (units integer[], parentname character varying, userid integer) integer
Schema:
reference
Description:
Create a new parent-level spatial unit, by aggregating a group of children units. For example, a contiguous group of desa from several neighbouring kecamatan can be selected and aggregated into a new kecamatan. Inputs: units: an array of unit IDs for the units to be aggregated parentname: the name of the new parent unit to create userid: the id of the current user

spatial_can_edit

Function call:
spatial_can_edit (userid integer) boolean
Schema:
reference
Description:
Given a user id returns true if the user has permission to edit data in the spatial data management system or false otherwise. This is a wrapper function for is_permitted('can_edit_spatial', userid)

spatial_edit

Function call:
spatial_edit (unit integer, newname character varying, newcomment text, userid integer) integer
Schema:
reference
Description:
Edit the name and commnent of spatial data. Inputs: - unit: id of spatial unit - newname: new name - newcomment: new comment - userid: user user id

spatial_explode_desa

Function call:
spatial_explode_desa (desa integer, userid integer) void
Schema:
reference
Description:
Break a multi-part village polygon into multiple seprate villages. Useful for working with islands. Automatcally recodes and names each part sequentially.

spatial_get_parent

Function call:
spatial_get_parent (childid integer) integer
Schema:
reference
Description:
Given a spatial unit id, returns the id of the parent unit.

spatial_get_siblings_without_geom

Function call:
spatial_get_siblings_without_geom (desa integer) setof record
Schema:
reference
Description:
Given a spatial unit id, returns a table consisting of the id, name and code of all sibling units in the same parent unit that do not have any defined boundary geometry

spatial_is_desa

Function call:
spatial_is_desa (desa integer) boolean
Schema:
reference
Description:
Returns true if the id provided is for a desa (unit level 5) or false otherwise

spatial_is_multipart

Function call:
spatial_is_multipart (desa integer) boolean
Schema:
reference
Description:
Returns true if the polygon has more than one parts

spatial_link_previous_record

Function call:
spatial_link_previous_record (original_desa integer, new_desa_to_link integer, new_desa_no_link integer, desa_with_no_geom integer, userid integer) integer
Schema:
reference
Description:
Links an existing manually created spatial unit created through the table interface, to a newly split desa. This function is intended as a means to update manually created spatial units with their boundary data. This function is part of the web-based spatial data management system and is not intended to be called manually. Inputs: - original_desa integer, -- the oirignal desa that has been split into two desa - new_desa_to_link integer, -- the desa split from the original, that is to be linked to a previously manually created record that has no geom - new_desa_no_link integer, -- the split desa that represents the original - no linking required - desa_with_no_geom integer, -- the manually created record with no geom that is to be linked to the new desa that was split from the original - userid integer -- the id of the current user)

spatial_merge_desa

Function call:
spatial_merge_desa (desa1 integer, desa2 integer, userid integer) integer
Schema:
reference
Description:
Given the spatial ids of two villages (desa1 and desa2), merges their boundaries into a single desa, retaining the attributes of desa1 returning the id of the merged village

spatial_parent_is_above_children

Function call:
spatial_parent_is_above_children (units integer[], destination integer) boolean
Schema:
reference
Description:
Check function for the move procedure. Given an array of child unit ids (to recode) and the id of the destination unit, returns true if the destination unit is on level above the child units, otherwise false.

spatial_recode

Function call:
spatial_recode (units integer[], destination integer, userid integer) integer
Schema:
reference
Description:
Move (recode) procedure. Moves units from one parent unit to another. Given an array of units at the same level and in the same parent (both of which should be first checked before this function is called), and the id of the destination parent, this function recodes the child units and any other lower units they contain according to the new parent unit code, and recalculates the boundaries of the parent and any higher units.

spatial_split_desa

Function call:
spatial_split_desa (desa integer, boundary public.geometry, userid integer) setof integer
Schema:
reference
Description:
Split procedure. Splits one desa into two new desa, using a line digitised by the user. Given an id for the desa, and a line geometry unit (in WGS84 coordinates (EPSG:4326)), splits the desa into two new desa, each of which will have identical attributes, and returns the new unit IDs

spatial_units_are_contiguous

Function call:
spatial_units_are_contiguous (units integer[]) boolean
Schema:
reference
Description:
Given an array of units and another unit, determines if all the units are contiguous. If at least unit does not contact any other units, returns false, otherwise true

spatial_units_are_contiguous

Function call:
spatial_units_are_contiguous (units integer[], destination integer) boolean
Schema:
reference
Description:
Given an array of units and another unit, determines if all the units are contiguous. If at least unit does not contact any other units, returns false, otherwise true

spatial_units_at_same_level

Function call:
spatial_units_at_same_level (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are at the same level, otherwise false

spatial_units_in_area

Function call:
spatial_units_in_area (units integer[], destination integer, userid integer) boolean
Schema:
reference
Description:
Given an array of units, another unit id, and a user id, returns true if all the units are in the user's area of responsibility, otherwise false

spatial_units_in_area

Function call:
spatial_units_in_area (units integer[], userid integer) boolean
Schema:
reference
Description:
Given an array of units, and a user id, returns true if all the units are in the user's area of responsibility, otherwise false

spatial_units_in_same_grandparent

Function call:
spatial_units_in_same_grandparent (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are in the same grandparent unit, otherwise false

spatial_units_in_same_parent

Function call:
spatial_units_in_same_parent (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are in the same parent unit, otherwise false

spatial_update_new_desa

Function call:
spatial_update_new_desa (newdesa integer, olddesa integer, new_name character varying) integer
Schema:
reference
Description:
Updates the name and code of a new desa created by spatial_split_desa()

submissions_report

Function call:
submissions_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (submissions_REPORT(casechid,usrid,1)).*;

summarise_data_records

Function call:
summarise_data_records (sdate date, edate date, data_schema character varying[], schema_name text, table_name text, mth date, total integer) setof record
Schema:
backoffice
Description:
Produces a table showing the monthly total newly created records for each data table in the data schema over the specified period. This can be used for monitoring total system usage

tanggal

Function call:
tanggal (repdate timestamp without time zone, OUT formatteddate character varying) character varying
Schema:
reference
Description:
Format a date

testhndlr1

Function call:
testhndlr1 (uid integer, i1 integer, str1 character varying, i2 integer, OUT result integer, OUT outmsg character varying) record
Schema:
data
Description:
Looks like a test - delete?

testhndlr2

Function call:
testhndlr2 (uid integer, i1 integer, str1 character varying, i2 integer, OUT result integer, OUT outmsg character varying) record
Schema:
data
Description:
Looks like another test - delete?

testvaliddate

Function call:
testvaliddate (instr character varying) boolean
Schema:
metadata
Description:
Check if date is valid

testvalidoremptydate

Function call:
testvalidoremptydate (instr character varying) boolean
Schema:
metadata
Description:
Check if date is valid or empty

texttodate

Function call:
texttodate (instr character varying) date
Schema:
metadata
Description:
Return a NULL value if the input string is not a valid date, otherwise return a date corresponding to the input string.

tiggetpassword

Function call:
tiggetpassword (_user_id character varying, OUT res_pw character varying) character varying
Schema:
backoffice
Description:
Given a user id, return a password

tiguserloginplainpw

Function call:
tiguserloginplainpw (_user_id character varying, _user_pw character varying) character varying
Schema:
backoffice
Description:
Login function

tiguserlogout

Function call:
tiguserlogout ( character varying) void
Schema:
backoffice
Description:
Logout function apparently no longer being used (just returns). Delete?

track_usage

Function call:
track_usage (user_id integer, mtype character) integer
Schema:
sms
Description:
Function just returning 0 currently - delete?

translateinputvalue

Function call:
translateinputvalue (flddata projfielddata, instr character varying, OUT result character varying, OUT errorstr character varying) record
Schema:
metadata
Description:
Given an input value, return the string that needs to be used in an INSERT/UPDATE sql statement. If there is an error, the result string will be NULL, and the errorstr return variable will contain information about the error that was detected.

translateinputvalues

Function call:
translateinputvalues (flddata projfielddata[], indata character varying[], tablesused integer[], OUT outdata character varying[], OUT errors cellerrorinfo[]) record
Schema:
metadata
Description:
Given an array of field data and matching input values, create an array of corresponding output values for use in SQL statements. The errors output will have a variable number of entries - it will be null if there are no errors, otherwise will return an error message for each error found.

trawl_data

Function call:
trawl_data (numdays integer, OUT reporttype text, OUT locationcode text, OUT locationid integer, OUT speciescode text, OUT speciesid integer, OUT signcode text, OUT signid integer, OUT targetcode text, OUT targetid integer, OUT dx integer) setof record
Schema:
backoffice
Description:
Identifies every unique combination of location, species and result (sign, syndrome, test target or diagnosis) as well as parent values in the corresponding hierarchies, based on data for the specified period, and tests to see if they generate an alert.

treatment_report

Function call:
treatment_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (TREATMENT_REPORT(casechid,usrid,1)).*;

trunc_sms

Function call:
trunc_sms (message character varying, maxparts integer, OUT outmessage character varying) character varying
Schema:
backoffice
Description:
Truncates a string to fit in the specified number of text messages (default is 1). This function leaves enough room for the "[iSIKHNAS]" prefix, adds "..." to indicate truncation. A single SMS maximum is 160 characters, but for spanning SMS, each part can only have 153 characters.

trunc_user_area

Function call:
trunc_user_area (userid integer, level integer, OUT area integer[]) integer[]
Schema:
backoffice
Description:
Takes a user id and (optionally) a number of digits to truncate to. Returns an array of integers, being the ids of the area of responsibility, truncated to the desired level. If truncation level is omitted, defaults to 4 (district/kabupaten). This function is used in report generation to identify the area for the report to be generated. With the default value of 4, users with multiple areas of responsibility wihtin a kabupaten will receive a single report for that kabupaten. Users with multiple areas above the kabupate will have each area included in the report.

update_ods

Function call:
update_ods (fname character varying) integer
Schema:
backoffice

update_permission

Function call:
update_permission (pid integer, gid integer, perm boolean) integer
Schema:
backoffice
Description:
Update permissions - update or insert

update_permission

Function call:
update_permission (pid integer, gid integer, permint integer) integer
Schema:
backoffice
Description:
Update permissions - update or insert

uploadinputdata

Function call:
uploadinputdata (projid integer, userid integer, instr character varying) inputprocessresult
Schema:
metadata
Description:
Given an array of field data and matching input values, create an array of corresponding output values for use in SQL statements. The errors output will have a variable number of entries - it will be null if there are no errors, otherwise will return an error message for each error found.

vaccination_history_report

Function call:
vaccination_history_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (VACCINATION_HISTORY_REPORT(casechid,usrid,1)).*;

validate_value

Function call:
validate_value (fieldid integer, indata character varying, rowdata character varying[], userid integer, msgid bigint, OUT result businessrulereturn) businessrulereturn
Schema:
metadata
Description:
Validates an input value against metadata. Inputs are:

  • fieldid: the id of the field from the metadata.importfield table
  • indata: the value to be validated, expressed as a string
  • rowdata: a string array of all the values in the row, to be used for cross-field validation
  • userid: the id of the current user
  • msgid: the unique ID of the current message

Outputs: A record with two fields:

  • result: 0 if valid, 1 if invalid
  • error message: Error message explaining why the field is not valid

validintegervalue

Function call:
validintegervalue (instr character varying) boolean
Schema:
metadata
Description:
Check if input is an integer

validnumericvalue

Function call:
validnumericvalue (instr character varying) boolean
Schema:
metadata
Description:
Check if input is numeric

verify_checksum

Function call:
verify_checksum (inval bigint) boolean
Schema:
reference
Description:
Return true if the supplied input value has a valid check digit as the last digit, false otherwise.

warn_changed_spatial

Function call:
warn_changed_spatial (loc_id integer, user_id integer) integer
Schema:
reference
Description:
Check and warn if spatial data standards have been modified

write_to_s3

Function call:
write_to_s3 (content text, bucket character varying, path text, region_name text) character varying
Schema:
backoffice

xget

Function call:
xget (ref character varying, rowid integer, rowcol character, OUT value character varying) character varying
Schema:
metadata
Description:
Used for parsing imported spreadsheets. xget access data from the xlimp temporary table using 'A1' cell referencing and returns the retrieved cell value

xmpp_parse

Function call:
xmpp_parse (msg character varying, user_phone character varying, msg_id bigint) void
Schema:
sms
Description:
Inserts incoming msg into sms inbox

zoonotic_involvement_report

Function call:
zoonotic_involvement_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (ZOONOTIC_INVOLVEMENT_REPORT(casechid,usrid,1)).*;