Database functions

- test mode -

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: finding their phone number, adding the number to the user record with a matching pin, 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)).*;