-- --------------------------------------------------
-- CREATE THE STATEMENTS FOR THE PLACEHOLDER INSERTS
-- --------------------------------------------------

-- THIS WILL CREATE A LINE LIKE THIS: 
--   "letter.insert_letter_placeholder('[PLACEHOLDER_NAME]', 'TYPE', 'a comment', 'the script');"

/*
SELECT 'SELECT letter.insert_letter_placeholder(' 
	|| COALESCE('E''' || REPLACE(p.name, '''', '''''') || '''', 'NULL') || ', ' 
	|| COALESCE('E''' || REPLACE(p.type, '''', '''''') || '''', 'NULL') || ', ' 
	|| COALESCE('E''' || REPLACE(p.comment, '''', '''''') || '''', 'NULL') || ', E''' 
	|| REPLACE(REPLACE(p.script, '''', ''''''), '\\', '\\\\') || ''');' AS "INSERTS"
FROM letter.placeholders p
ORDER BY p.type, p.name;
*/


-- --------------------------------------------------
-- USE THIS FUNCTION FOR THE PLACEHOLDER INSERTION
-- --------------------------------------------------

-- THE FUNCTION TO INSERT OR UPDATE A NEW PLACEHOLDER
CREATE OR REPLACE FUNCTION letter.insert_letter_placeholder (p_name VARCHAR, p_type VARCHAR, p_comment TEXT, p_script TEXT)
	RETURNS void AS
$BODY$

DECLARE
	r RECORD;
	count INTEGER;
	
BEGIN
	count = 0;
	FOR r IN SELECT p.name FROM letter.placeholders p 
	WHERE p.name = p_name 
	OR (
		p.name IS NULL AND p_name IS NULL
		AND (
			p.type = p_type
			OR (
				p.type IS NULL AND p_type IS NULL
			)
		)
	)
	LOOP
		count = count + 1;
	END LOOP;

	
	IF count = 0 THEN
		INSERT INTO letter.placeholders
		(name, type, comment, script)
		VALUES
		(p_name, p_type, p_comment, p_script );
	ELSE
		UPDATE letter.placeholders p
		SET comment = p_comment,
			script = p_script
		WHERE p.name = p_name 
		OR (
			p.name IS NULL AND p_name IS NULL
			AND (
				p.type = p_type
				OR (
					p.type IS NULL AND p_type IS NULL
				)
			)
		);
	END IF;
	
	RETURN;
END
$BODY$
LANGUAGE plpgsql;



-- ADD THE INSERTS HERE ...
SELECT letter.insert_letter_placeholder(E'[PAT_LAST_INVOICE_DATE]', E'BILLING', E'The date of the last invoice of this patient', E'/**
 * [PAT_LAST_INVOICE_DATE]
 */
var invoice = getLastInvoices(PATIENT, PHYSICIAN, 1, null);


if (invoice == null || invoice.getInvoiceDate() == null)
	"";
else
	FORMATTER.formatDate(invoice.getInvoiceDate());');
SELECT letter.insert_letter_placeholder(E'[PAT_LAST_INVOICE_NUMBER]', E'BILLING', E'The number of the last invoice of this patient', E'/**
 * [PAT_LAST_INVOICE_NUMBER]
 */
var invoice = getLastInvoices(PATIENT, PHYSICIAN, 1, null);


if (invoice == null || invoice.getInvoiceNumber() == null)
	"";
else
	invoice.getInvoiceNumber();');
SELECT letter.insert_letter_placeholder(NULL, E'BILLING', E'Functions available to all BILLING placeholders', E'/**
 * BILLING FUNCTIONS
 */

/**
 * Patient patient: The patient to search the invoice for
 * Physician physician: The physician, that created the invoice or NULL
 * 	if the the physician should not be taken into consideration.
 * int count: The number of invoices to fetch or null or -1, to fetch all
 * List<Integer> states: The states of the invoices to fetch or null or empty list,
 * 	to fetch all states.
 * return: A list of the last <<count>> invoices for this patient and this physician
 *	or the last invoice, if the count == 1.
 */
function getLastInvoices (patient, physician, count, states)
{
	var query = new java.lang.StringBuilder();
	var iter;
	var parameters = new java.util.LinkedList();
	
	
	if (patient == null)
		return null;
	
	// define the query start
	query.append("SELECT OBJECT(o) FROM Invoice o")
			.append("\\nWHERE o.patient = :patient");
	parameters.add(DATABASE_MANAGER.createParameter("patient", patient));
	
	// set the physician to query for
	if (physician != null)
	{
		query.append("\\nAND o.physician = :physician");
		parameters.add(DATABASE_MANAGER.createParameter("physician", physician));
	}
	
	// set the states to query for
	if (states != null && !states.isEmpty())
	{
		query.append("\\nAND o.state IN (");
		for (iter = states.iterator(); iter.hasNext(); )
		{
			query.append(iter.next());
			if (iter.hasNext())
				query.append(", ");
		}
		query.append(")");
	}
	
	// order the result
	query.append("\\nORDER BY o.invoiceDate, o.id")
	
	// define the no of results
	if (count != null && count > 0)
	{
		if (count == 1)
			return DATABASE_MANAGER.getHqlQueryObject(query.toString(), parameters);
		else
			return DATABASE_MANAGER.doHqlQuery(query.toString(), 0, count, parameters);
	}
	else
		return DATABASE_MANAGER.doHqlQuery(query.toString(), parameters);
}');
SELECT letter.insert_letter_placeholder(E'[CONT_CNS_CODE]', E'CONTACT', E'The CNS code of this contact', E'/**
 * [CONT_CNS_CODE]
 */
toString(MAIN_CONTACT.getUcmCode());');
SELECT letter.insert_letter_placeholder(E'[CONT_COMMENT]', E'CONTACT', E'The comment to this contact', E'/**
 * [CONT_COMMENT]
 */
toString(MAIN_CONTACT.getComment());');
SELECT letter.insert_letter_placeholder(E'[CONT_EMAIL]', E'CONTACT', E'The email of this contact', E'/**
 * [CONT_EMAIL]
 */
toString(MAIN_CONTACT.getEmail());');
SELECT letter.insert_letter_placeholder(E'[CONT_FAX]', E'CONTACT', E'The fax number of this contact', E'/**
 * [CONT_FAX]
 */
toString(MAIN_CONTACT.getFax());');
SELECT letter.insert_letter_placeholder(E'[CONT_FIRST_NAMES]', E'CONTACT', E'The contact person''s first name', E'/**
 * [CONT_FIRST_NAMES]
 */
toString(MAIN_CONTACT.getNameFirst());');
SELECT letter.insert_letter_placeholder(E'[CONT_FULL_ADDRESS]', E'CONTACT', E'The full address of this contact in several lines', E'/**
 * [CONT_FULL_ADDRESS]
 */
var addresses = MAIN_CONTACT.getAddress();
var	address;
var formattedAddress = "";


if (addresses != null && addresses.size() > 0)
{
	address = addresses.iterator().next();
	formattedAddress = FORMATTER.formatAddressBlock(address);
}

formattedAddress;');
SELECT letter.insert_letter_placeholder(E'[CONT_FULL_ADDRESS_SINGLE_LINE]', E'CONTACT', E'The full address of this contact in one line', E'/**
 * [CONT_FULL_ADDRESS_SINGLE_LINE]
 */
var addresses = MAIN_CONTACT.getAddress();
var	address;
var formattedAddress = "";


if (addresses != null && addresses.size() > 0)
{
	address = addresses.iterator().next();
	formattedAddress = FORMATTER.formatAddressLine(address);
}

formattedAddress;');
SELECT letter.insert_letter_placeholder(E'[CONT_FULL_NAME]', E'CONTACT', E'The full name of this contact', E'/**
 * [CONT_FULL_NAME]
 */
var name	= new java.lang.StringBuilder();
var part;


part = toString(MAIN_CONTACT.getTitle());
name.append(part);
if (part.length() > 0)
	name.append(" ");

part = toString(MAIN_CONTACT.getNameFirst());
name.append(part);
if (part.length() > 0)
	name.append(" ");

name.append(toString(MAIN_CONTACT.getName()));

name.toString().trim();');


SELECT letter.insert_letter_placeholder(E'[CONT_LAST_NAME]', E'CONTACT', E'The contact person''s last name', E'/**
 * [CONT_LAST_NAME]
 */
toString(MAIN_CONTACT.getName());');
SELECT letter.insert_letter_placeholder(E'[CONT_MOBILE]', E'CONTACT', E'The mobile number of this contact', E'/**
 * [CONT_MOBILE]
 */
toString(MAIN_CONTACT.getPhoneMobile());');
SELECT letter.insert_letter_placeholder(E'[CONT_TEL_PRIVATE]', E'CONTACT', E'The private phone number of this contact', E'/**
 * [CONT_TEL_PRIVATE]
 */
toString(MAIN_CONTACT.getPhonePrivate());');
SELECT letter.insert_letter_placeholder(E'[CONT_TEL_WORK]', E'CONTACT', E'The working phone number of this contact', E'/**
 * [CONT_TEL_WORK]
 */
toString(MAIN_CONTACT.getPhoneWork());');
SELECT letter.insert_letter_placeholder(E'[CONT_TITLE]', E'CONTACT', E'The title of this contact', E'/**
 * [CONT_TITLE]
 */
toString(MAIN_CONTACT.getTitle());');
SELECT letter.insert_letter_placeholder(E'[GEN_DATE]', E'GENERAL', E'The current date', E'/**
 * [GEN_DATE]
 */
FORMATTER.formatDate(new java.util.Date());');
SELECT letter.insert_letter_placeholder(E'[GEN_TIME]', E'GENERAL', E'The current time', E'/**
 * [GEN_TIME]
 */
FORMATTER.formatDate(new java.util.Date(), "hh:mm");');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ACCOUNT_LIST]', E'OFFICE', E'<html>The list of the office''s bank accounts.</html>', E'/**
 * [OFFICE_ACCOUNT_LIST]
 */

// use the same formating as in the print template
FORMATTER.formatAccountList(PHYSICIAN);');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ALL_PHONE_AND_EMAIL]', E'OFFICE', E'The offices''s phone numbers and email, on seperate lines', E'/**
 * [OFFICE_ALL_PHONE_AND_EMAIL]
 */

var result = "";

var phone = OFFICE.getOfficePhoneByType("private");
phone = phone == null ? "" : phone.getNumber();

var fax = OFFICE.getOfficePhoneByType("fax");
fax = fax == null ? "" : fax.getNumber();

var email = OFFICE.getEmail();
email = email == null ? "" : email;

if (phone != null && phone.length != 0)
	result = "Tel: " + phone;

if (fax != null && fax.lengt != 0)
	result = result + (result.length == 0 ? "" : "\\n")
		+ "FAX: " + fax;

if (email != null && email.lengt != 0)
	result = result + (result.length == 0 ? "" : "\\n")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ALL_PHONE_AND_EMAIL_SINGLE_LINE]', E'OFFICE', E'The offices''s phone numbers and email, on one single line', E'/**
 * [OFFICE_ALL_PHONE_AND_EMAIL_SINGLE_LINE]
 */

var result = "";


var phone = OFFICE.getOfficePhoneByType("private");
phone = phone == null ? "" : phone.getNumber();

var fax = OFFICE.getOfficePhoneByType("fax");
fax = fax == null ? "" : fax.getNumber();

var email = OFFICE.getEmail();
email = email == null ? "" : email;

if (phone != null && phone.length != 0)
	result = "Tel: " + phone;

if (fax != null && fax.lengt != 0)
	result = result + (result.length == 0 ? "" : "; ")
		+ "FAX: " + fax;

if (email != null && email.lengt != 0)
	result = result + (result.length == 0 ? "" : "; ")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_CNS_CODE]', E'OFFICE', E'The CNS code of the medical office / practice', E'/**
 * [OFFICE_CNS_CODE]
 */
var code = OFFICE.getUcmCode();

code == null ? "" : code;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_EMAIL]', E'OFFICE', E'The email address of the medical office / practice', E'/**
 * [OFFICE_EMAIL]
 */
var email = OFFICE.getEmail();

email == null ? "" : email;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_FAX]', E'OFFICE', E'The fax number of the medical office / practice', E'/**
 * [OFFICE_FAX]
 */
var fax = OFFICE.getOfficePhoneByType("fax");

fax == null ? "" : fax.getNumber();');
SELECT letter.insert_letter_placeholder(E'[OFFICE_FULL_SITE_ADDRESS_SINGLE_LINE]', E'OFFICE', E'<html>The office''s site''s full address. <br>Formatted in a single line, <br>suited e.g. as the return address</html>', E'/**
 * [OFFICE_FULL_SITE_ADDRESS_SINGLE_LINE]
 */

var adr = SITE.getSiteAddress();
 
// use the same formating as in the print template
FORMATTER.formatAddressLine(adr);');
SELECT letter.insert_letter_placeholder(E'[OFFICE_INFORMATIONS]', E'OFFICE', E'Informations about the medical office / practice', E'/**
 * [OFFICE_INFORMATIONS]
 */
var info = OFFICE.getInformation();

info == null ? "" : info;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_NAME]', E'OFFICE', E'The name of the medical office / pactice', E'/**
 * [OFFICE_NAME]
 */

OFFICE.getName() == null ? "" : OFFICE.getName();');
SELECT letter.insert_letter_placeholder(E'[OFFICE_PHONE]', E'OFFICE', E'The phone number of the medical office / proctice', E'/**
 * [OFFICE_PHONE]
 */
var phone = OFFICE.getOfficePhoneByType("private")
phone == null ? "" : phone.getNumber();');
SELECT letter.insert_letter_placeholder(E'[OFFICE_WEBSIITE]', E'OFFICE', E'The Web site of the medical office / practice', E'/**
 * [OFFICE_WEBSIITE]
 */
var website = OFFICE.getWebsite();

website == null ? "" : website;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ADDRESS_COUNTRY]', E'OFFICE_ADDRESS', E'The country of the office''s address', E'/**
 * [OFFICE_ADDRESS_COUNTRY]
 */

var country;
 
try {
    // An office can, in theory, have more than one address (DB schema).
    // However, in practice there is only one, and hence the first is the only one.
    country = OFFICE.getOfficeAddress().iterator().next().getCountry();
    }
catch (error) {
    country = null;
    }

country == null ? "" : FORMATTER.translate("Country." + country);');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ADDRESS_LOCALITY]', E'OFFICE_ADDRESS', E'Locality  of the office''s address', E'/**
 * [OFFICE_ADDRESS_LOCALITY]
 */

var locality;
 
try {
    // An office can, in theory, have more than one address (DB schema).
    // However, in practice there is only one, and hence the first is the only one.
    locality = OFFICE.getOfficeAddress().iterator().next().getLocality();
    }
catch (error) {
    locality = null;
    }

locality == null ? "" : locality;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ADDRESS_NB]', E'OFFICE_ADDRESS', E'The number of the office''s address', E'/**
 * [OFFICE_ADDRESS_NB]
 */

var number;
 
try {
    // An office can, in theory, have more than one address (DB schema).
    // However, in practice there is only one, and hence the first is the only one.
    number = OFFICE.getOfficeAddress().iterator().next().getStreetNumber();
    }
catch (error) {
    number = null;
    }

number == null ? "" : number;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ADDRESS_STREET]', E'OFFICE_ADDRESS', E'The street of the office''s address', E'/**
 * [OFFICE_ADDRESS_STREET]
 */

var street;
 
try {
    // An office can, in theory, have more than one address (DB schema).
    // However, in practice there is only one, and hence the first is the only one.
    street = OFFICE.getOfficeAddress().iterator().next().getStreetName();
    }
catch (error) {
    street = null;
    }

street == null ? "" : street;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_ADDRESS_ZIP]', E'OFFICE_ADDRESS', E'ZIP code of the office''s address', E'/**
 * [OFFICE_ADDRESS_ZIP]
 */

var zip;
 
try {
    // an office can, in theory, have more than one address (DB schema).
    // However, in practice there is only one and hence the first is the only one.
    zip = OFFICE.getOfficeAddress().iterator().next().getZip();
    }
catch (error) {
    zip = null;
    }

zip == null ? "" : zip;');
SELECT letter.insert_letter_placeholder(E'[OFFICE_FULL_ADDRESS]', E'OFFICE_ADDRESS', E'<html>The office''s full address. <br>Formatted in an address block <br>suited for the adress window of letters</html>', E'/**
 * [OFFICE_FULL_ADDRESS]
 */

// use the same formating as in the print template
FORMATTER.formatOfficeAddress();');
SELECT letter.insert_letter_placeholder(E'[OFFICE_FULL_ADDRESS_SINGLE_LINE]', E'OFFICE_ADDRESS', E'<html>The office''s full address. <br>Formatted in a single line<br>, suited e.g. as the return addresses </html>', E'/**
 * [OFFICE_FULL_ADDRESS_SINGLE_LINE]
 */

// use the same formating as in the print template
FORMATTER.formatOfficeLine();');
SELECT letter.insert_letter_placeholder(E'[OFFICE_FULL_SITE_ADDRESS]', E'OFFICE_ADDRESS', E'<html>The office''s site''s full address. <br>Formatted in an address block <br>suited for the adress window of letters</html>', E'/**
 * [OFFICE_FULL_SITE_ADDRESS]
 */
var adr = SITE.getSiteAddress();
 
// use the same formating as in the print template
FORMATTER.formatAddressBlock(adr);');
SELECT letter.insert_letter_placeholder(E'[PAT_ADDRESS_COUNTRY]', E'PATIENT_ADDRESS', E'<html>The country of the patient''s address.<br>Translated if possible.<html>', E'/**
 * [PAT_ADDRESS_COUNTRY]
 */

var adr = PATIENT.getHomeAddress();
 
tryTranslate("Country.", adr == null ? null : adr.getCountry());');
SELECT letter.insert_letter_placeholder(E'[PAT_ADDRESS_LOCALITY]', E'PATIENT_ADDRESS', E'The locality of the patient''s address', E'/**
 * [PAT_HOME_ADDRESS_LOCALITY]
 */

var adr = PATIENT.getHomeAddress()
 
adr == null ? "" : adr.getLocality();');
SELECT letter.insert_letter_placeholder(E'[PAT_ADDRESS_NB]', E'PATIENT_ADDRESS', E'The street numberof the patient''s address', E'/**
 * [PAT_HOME_ADDRESS_NB]
 */

var adr = PATIENT.getHomeAddress()
 
adr == null ? "" : adr.getStreetNumber();');
SELECT letter.insert_letter_placeholder(E'[PAT_ADDRESS_STREET]', E'PATIENT_ADDRESS', E'The street of the patient''s address', E'/**
 * [PAT_HOME_ADDRESS_STREET]
 */

var adr = PATIENT.getHomeAddress()
 
adr == null ? "" : adr.getStreetName();');
SELECT letter.insert_letter_placeholder(E'[PAT_ADDRESS_ZIP]', E'PATIENT_ADDRESS', E'The ZIP code of the patient''s home address', E'/**
 * [PAT_HOME_ADDRESS_ZIP]
 */

var adr = PATIENT.getHomeAddress()
 
adr == null ? "" : adr.getZip();');
SELECT letter.insert_letter_placeholder(E'[PAT_FULL_ADDRESS]', E'PATIENT_ADDRESS', E'<html>The patient''s full address. <br>Formatted in an address block <br>suited for the adress window of letters</html>', E'/**
 * [PAT_FULL_ADDRESS]
 */

// use the same formating as in the print template
FORMATTER.formatPatientHomeAddress();');
SELECT letter.insert_letter_placeholder(E'[PAT_FULL_NAME_AND_ADDRESS]', E'PATIENT_ADDRESS', E'The name and the address of the patient', E'/**
 * [PAT_FULL_NAME_AND_ADDRESS]
 */
FORMATTER.formatPatientName(PATIENT, true, false) + "\\n" + 
		FORMATTER.formatPatientHomeAddress();');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_ALL_PHONE_AND_EMAIL]', E'PATIENT_CONTACT', E'<html>The billing address for the PATIENT. <br>This can be different from the patient''s own address. <br> Therefore it''s called the "guarantor''s" address.</html>', E'/**
 * [PAT_CONTACT_ALL_PHONE_AND_EMAIL]
 */

var result = "";

var tel = PATIENT.getPhone("private");
var mobile = PATIENT.getPhone("mobile");
var work = PATIENT.getPhone("office");
var fax = PATIENT.getPhone("fax");
var email = PATIENT.getEmail();

if (tel != null && !tel.getNumber().length == 0)
	result = "Tel: " + tel.getNumber();

if (mobile != null && !mobile.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "\\n")
		+ "Mobile: " + mobile.getNumber();

if (work != null && !work.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "\\n")
		+ "Work: " + work.getNumber();

if (fax != null && !fax.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "\\n")
		+ "FAX: " + fax.getNumber();

if (email != null && !email.isEmpty())
	result = result + (result.length == 0 ? "" : "\\n")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_ALL_PHONE_AND_EMAIL_SINGLE_LINE]', E'PATIENT_CONTACT', E'All the patient''s phone numbers and email addresses, in one single line', E'/**
 * [PAT_CONTACT_ALL_PHONE_AND_EMAIL_SINGLE_LINE]
 */

var result = "";

var tel = PATIENT.getPhone("private");
var mobile = PATIENT.getPhone("mobile");
var work = PATIENT.getPhone("office");
var fax = PATIENT.getPhone("fax");
var email = PATIENT.getEmail();

if (tel != null && !tel.getNumber().length == 0)
	result = "Tel: " + tel.getNumber();

if (mobile != null && !mobile.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "; ")
		+ "Mobile: " + mobile.getNumber();

if (work != null && !work.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "; ")
		+ "Work: " + work.getNumber();

if (fax != null && !fax.getNumber().length == 0)
	result = result + (result.length == 0 ? "" : "; ")
		+ "FAX: " + fax.getNumber();

if (email != null && !email.isEmpty())
	result = result + (result.length == 0 ? "" : "; ")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_EMAIL]', E'PATIENT_CONTACT', E'The patient''s eMail address', E'/**
 * [PAT_CONTACT_EMAIL]
 */

PATIENT.getEmail() == null ? "" : PATIENT.getEmail();');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_FAX]', E'PATIENT_CONTACT', E'The patient''s FAX number', E'/**
 * [PAT_CONTACT_FAX]
 */

PATIENT.getPhone("fax") == null ? "" : PATIENT.getPhone("fax").getNumber();');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_MOBILE]', E'PATIENT_CONTACT', E'The patient''s mobile phone number', E'/**
 * [PAT_CONTACT_MOBILE]
 */

PATIENT.getPhone("mobile") == null ? "" : PATIENT.getPhone("mobile").getNumber();');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_PHONE_PRIVATE]', E'PATIENT_CONTACT', E'The patient''s private phone number', E'/**
 * [PAT_CONTACT_PHONE_PRIVATE]
 */

PATIENT.getPhone("private") == null ? "" : PATIENT.getPhone("private").getNumber();');
SELECT letter.insert_letter_placeholder(E'[PAT_CONTACT_PHONE_WORK]', E'PATIENT_CONTACT', E'The patient''s phone number at work', E'/**
 * [PAT_CONTACT_PHONE_WORK]
 */

PATIENT.getPhone("office") == null ? "" : PATIENT.getPhone("office").getNumber();');
SELECT letter.insert_letter_placeholder(E'[PAT_GENDER]', E'PATIENT_GENDER', E'The patient''s gender', E'/**
 * [PAT_GENDER]
 */
 
var gender = PATIENT.getGender();
gender == null ? "" : FORMATTER.translate("Gender." + gender);');
SELECT letter.insert_letter_placeholder(E'[PAT_GENDER_ABBREV]', E'PATIENT_GENDER', E'The patient''s gender, as a one letter abbreviation', E'/**
 * [PAT_GENDER_ABBREV]
 */
PATIENT.getGender() == null ? "" : PATIENT.getGender().substring(0,1).toUpperCase();');
SELECT letter.insert_letter_placeholder(E'[PAT_GENDER_FEMALE]', E'PATIENT_GENDER', E'"x" if the patient''s gender is "female"', E'/**
 * [PAT_GENDER_FEMALE]
 */
PATIENT.getGender() != null && PATIENT.getGender() == "female" ? "x" : "";');
SELECT letter.insert_letter_placeholder(E'[PAT_GENDER_MALE]', E'PATIENT_GENDER', E'"x" if the patient''s gender is "male"', E'/**
 * [PAT_GENDER_MALE]
 */
PATIENT.getGender() != null && PATIENT.getGender() == "male" ? "x" : "";');
SELECT letter.insert_letter_placeholder(E'[PAT_GENDER_OTHER]', E'PATIENT_GENDER', E'"x" if the patient''s gender is other than "male" or "female"', E'/**
 * [PAT_GENDER_OTHER]
 */
PATIENT.getGender() != null && PATIENT.getGender() != "male" && PATIENT.getGender() != "female" ? "x" : "";');
SELECT letter.insert_letter_placeholder(E'[PAT_FULL_GUARANTOR_ADDRESS]', E'PATIENT_GUARANTOR', E'<html>The billing address for the patient. <br>This can be different from the patient''s own address. <br> Therefore it''s called the "guarantor''s" address.</html>', E'/**
 * [PAT_FULL_GUARANTOR_ADDRESS]
 */

 // use the same formating as in the invoice print template
FORMATTER.formatGuarantorAddress(PATIENT);');
SELECT letter.insert_letter_placeholder(E'[PAT_FULL_GUARANTOR_NAME]', E'PATIENT_GUARANTOR', E'<html>The name of the person who''s goint to pay the bills. <br>This can be different from the patient''s himself. <br> Therefore he''s called the "guarantor".</html>', E'/**
 * [PAT_FULL_GUARANTOR_NAME]
 */

 // use the same formating as in the invoice print template
FORMATTER.formatGuarantorName(PATIENT, true, false);');
SELECT letter.insert_letter_placeholder(E'[PAT_GUARANTOR_COUNTRY]', E'PATIENT_GUARANTOR', E'The country of the patient''s guarantor', E'/**
 * [PAT_GUARANTOR_COUNTRY]
 */

var adr = PATIENT.getPatientContact()
 
adr == null ? "" : FORMATTER.translate("Country." + adr.getCountry());');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_30_DAYS]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 30 days', E'/**
 * [PAT_HISTORY_30_DAYS]
 */
getPatientHistoryXDays(30);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_3_DAYS]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 3 days', E'/**
 * [PAT_HISTORY_3_DAYS]
 */
getPatientHistoryXDays(3);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_7_DAYS]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 7 days', E'/**
 * [PAT_HISTORY_7_DAYS]
 */
getPatientHistoryXDays(7);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_ALL]', E'PATIENT_HISTORY', E'All history entries of the patient as a table', E'/**
 * [PAT_HISTORY_ALL]
 */
var html = getPatientHistory(PATIENT, null, null, null);
html;');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_ALL_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient as text instead of a table', E'/**
 * [PAT_HISTORY_ALL_TEXT]
 */
var html = getPatientHistory(PATIENT, null, null, null, false);
html;');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_30_DAYS_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 30 days as text (not as table)', E'/**
 * [PAT_HISTORY_30_DAYS_TEXT]
 */
var html = getPatientHistoryXDays(30, false);
html;');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_3_DAYS_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 3 days as text (not as table)', E'/**
 * [PAT_HISTORY_3_DAYS_TEXT]
 */
var html = getPatientHistoryXDays(3, false);
html;');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_7_DAYS_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during the last 7 days as text (not as table)', E'/**
 * [PAT_HISTORY_7_DAYS_TEXT]
 */
var html = getPatientHistoryXDays(7, false);
html;');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_CONSULTATION_TEXT]', E'PATIENT_HISTORY', E'The last incident of the PATIENT, that contains an SOAP entry or a measurement as text (not as a table)', 
E'/**
 * [PAT_HISTORY_LAST_CONSULTATION_TEXT]
 */
var types;
var incident;
var html;


types     = getConsultationTypes();

incident = getPatientIncidents(PATIENT, types, null, null, 1);

if (incident != null)
{
	html  = new java.lang.StringBuilder(
			"<html><head><meta http-equiv=\\"Content-Type\\" content=\\"text/html; charset=utf-8\\" /><title>history</title></head>\\n<body>");
	html  = appendIncidentRows(incident, html, false);
	
	html.append("\\n</body></html>");
}
else
	html  = "";

html.toString();');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_MONTH_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during last month as text (not as a table).', E'/**
 * [PAT_HISTORY_LAST_MONTH_TEXT]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_MONTH, 1);
from.add(java.util.Calendar.MONTH, -1);
to.set(java.util.Calendar.DAY_OF_MONTH, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_WEEK_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during the last week as text (not as a table).', E'/**
 * [PAT_HISTORY_LAST_WEEK_TEXT]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_WEEK, from.getFirstDayOfWeek());
from.add(java.util.Calendar.WEEK_OF_YEAR, -1);
to.set(java.util.Calendar.DAY_OF_WEEK, to.getFirstDayOfWeek());

getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_THIS_MONTH_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during this month as text (not as a table).', E'/**
 * [PAT_HISTORY_THIS_MONTH_TEXT]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_MONTH, 1);
to.set(java.util.Calendar.DAY_OF_MONTH, 1);
to.add(java.util.Calendar.MONTH, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_THIS_WEEK_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient during this week as text (not as a table).', E'/**
 * [PAT_HISTORY_THIS_WEEK_TEXT]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_WEEK, from.getFirstDayOfWeek());
to.set(java.util.Calendar.DAY_OF_WEEK, to.getFirstDayOfWeek());
to.add(java.util.Calendar.WEEK_OF_YEAR, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_TODAY_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient of today as text (not as a table).', E'/**
 * [PAT_HISTORY_TODAY_TEXT]
 */
getPatientHistoryXDays(1, false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_YESTERDAY_TEXT]', E'PATIENT_HISTORY', E'All history entries of the patient of yesterday as text (not as a table).', E'/**
 * [PAT_HISTORY_YESTERDAY_TEXT]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.add(java.util.Calendar.DAY_OF_MONTH, -1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), false);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_CONSULTATION]', E'PATIENT_HISTORY', E'The last incident of the PATIENT, that contains an SOAP entry or a measurement', 
E'/**
 * [PAT_HISTORY_LAST_CONSULTATION]
 */
var types;
var incident;
var html;


types     = getConsultationTypes();

incident  = getPatientIncidents(PATIENT, types, null, null, 1);

if (incident != null)
{
	html  = new java.lang.StringBuilder(
			"<html><head><meta http-equiv=\\"Content-Type\\" content=\\"text/html; charset=utf-8\\" /><title>history</title></head>\\n<body>" + 
			"<body><table border=\\"1\\" cellspacing=\\"0\\"><tbody>");
	html  = appendIncidentRows(incident, html);
	
	html.append("\\n</tbody></table></body></html>");
}
else
	html  = "";

html.toString();');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_MONTH]', E'PATIENT_HISTORY', E'All history entries of the patient during last month', E'/**
 * [PAT_HISTORY_LAST_MONTH]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_MONTH, 1);
from.add(java.util.Calendar.MONTH, -1);
to.set(java.util.Calendar.DAY_OF_MONTH, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime());');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_LAST_WEEK]', E'PATIENT_HISTORY', E'All history entries of the patient during the last week', E'/**
 * [PAT_HISTORY_LAST_WEEK]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_WEEK, from.getFirstDayOfWeek());
from.add(java.util.Calendar.WEEK_OF_YEAR, -1);
to.set(java.util.Calendar.DAY_OF_WEEK, to.getFirstDayOfWeek());

getPatientHistory(PATIENT, null, from.getTime(), to.getTime());');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_THIS_MONTH]', E'PATIENT_HISTORY', E'All history entries of the patient during this month', E'/**
 * [PAT_HISTORY_THIS_MONTH]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_MONTH, 1);
to.set(java.util.Calendar.DAY_OF_MONTH, 1);
to.add(java.util.Calendar.MONTH, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime());');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_THIS_WEEK]', E'PATIENT_HISTORY', E'All history entries of the patient during this week', E'/**
 * [PAT_HISTORY_THIS_WEEK]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.set(java.util.Calendar.DAY_OF_WEEK, from.getFirstDayOfWeek());
to.set(java.util.Calendar.DAY_OF_WEEK, to.getFirstDayOfWeek());
to.add(java.util.Calendar.WEEK_OF_YEAR, 1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime());');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_TODAY]', E'PATIENT_HISTORY', E'All history entries of the patient of today', E'/**
 * [PAT_HISTORY_TODAY]
 */
getPatientHistoryXDays(1);');
SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_YESTERDAY]', E'PATIENT_HISTORY', E'All history entries of the patient of yesterday', E'/**
 * [PAT_HISTORY_YESTERDAY]
 */
var from = clearCalendar(new java.util.GregorianCalendar());
var to = clearCalendar(new java.util.GregorianCalendar());


from.add(java.util.Calendar.DAY_OF_MONTH, -1);

getPatientHistory(PATIENT, null, from.getTime(), to.getTime());');
SELECT letter.insert_letter_placeholder('[PAT_HISTORY_ALL_SICKLEAVES]', 'PATIENT_HISTORY', E'All sickleave entries for this patient as a table',
E'/**
 * [PAT_HISTORY_ALL_SICKLEAVES]
 */
var types;
var incidents;
var html;

types     = new Array();
types[0]  = "sick_leave";

incidents  = getPatientIncidents(PATIENT, types);

if (incidents != null && !incidents.isEmpty())
{
	html  = new java.lang.StringBuilder(
			"<html><head><meta http-equiv=\\"Content-Type\\" content=\\"text/html; charset=utf-8\\" /><title>history</title></head>\\n<body>" + 
			"<body><table border=\\"1\\" cellspacing=\\"0\\"><tbody>");
	for (var iter = incidents.iterator(); iter.hasNext(); )
	{
		html  = appendIncidentRows(iter.next(), html, true, types);
	}
	
	html.append("\\n</tbody></table></body></html>");
}
else
	html  = "";

html.toString();');
SELECT letter.insert_letter_placeholder('[PAT_HISTORY_ALL_SICKLEAVES_TEXT]', 'PATIENT_HISTORY', E'All sickleave entries for this patient as a text',
E'/**
 * [PAT_HISTORY_ALL_SICKLEAVES_TEXT]
 */
var types;
var incidents;
var html;

types     = new Array();
types[0]  = "sick_leave";

incidents  = getPatientIncidents(PATIENT, types);

if (incidents != null && !incidents.isEmpty())
{
	html  = new java.lang.StringBuilder(
			"<html><head><meta http-equiv=\\"Content-Type\\" content=\\"text/html; charset=utf-8\\" /><title>history</title></head>\\n<body>" + 
			"<body><p>");
	for (var iter = incidents.iterator(); iter.hasNext(); )
	{
		html  = appendIncidentRows(iter.next(), html, false, types);
	}
	
	html.append("\\n</p></body></html>");
}
else
	html  = "";

html.toString();');
SELECT letter.insert_letter_placeholder(NULL, E'PATIENT_HISTORY', E'Functions available to all PATIENT_HISTORY placeholders', E'/**
 * PATIENT HISTORY FUNCTIONS
 */

/**
 * return: An array with the names of all consulation types.
 */
function getConsultationTypes ()
{
	types     = new Array();
	types[0]  = "soap.s";
	types[1]  = "soap.o";
	types[2]  = "soap.a";
	types[3]  = "soap.p";
	types[4]  = "cons.1";
	types[5]  = "cons.2";
	types[6]  = "cons.3";
	types[7]  = "measurement";
	
	return types;
}


/**
 * int days: The last x days to get the history.
 * boolean asTable: Whether or not the text shall be formatted as HTML. DEFAULT: true
 */
function getPatientHistoryXDays (days, asTable)
{
	if (typeof asTable == "undefined")
		asTable = true;
	
	var from = clearCalendar();
	var to = clearCalendar();
	
	
	from.add(java.util.Calendar.DAY_OF_MONTH, -(days-1));
	to.add(java.util.Calendar.DAY_OF_MONTH, 1);
	
	return getPatientHistory(PATIENT, null, from.getTime(), to.getTime(), asTable);
}


/**
 * Patient patient: The patient to get the history from
 * String[] types: The incident entry types to fetch
 * Date fromDate: The earliest date to get the history 
 * Date toDate: The latest date to get the history
 * boolean asTable: Whether or not the text shall be formatted as HTML. DEFAULT: true
 * return: The history of the patient as HTML or text (depending on the asTable flag).
 */
function getPatientHistory (patient, types, fromDate, toDate, asTable)
{
	if (patient == null || !patient.isPersistent())
		return "";
	
	if (typeof asTable == "undefined")
		asTable = true;
	
	var incidents = getPatientIncidents(patient, types, fromDate, toDate, null);
	var text = new java.lang.StringBuilder(
			"<html><head><meta http-equiv=\\"Content-Type\\" content=\\"text/html; charset=utf-8\\" /><title>history</title></head><body>\\n");
	
	if (asTable)
		text = text.append("<table border=\\"1\\" cellspacing=\\"0\\"><tbody>");
	
	if (incidents == null || incidents.size() == 0)
		return "";
	
	for (var i = 0; i < incidents.size(); i++)
	{
		text = appendIncidentRows(incidents.get(i), text, asTable);
	}
	
	if (asTable)
		text.append("\\n</tbody></table>");
	text.append("</body></html>");
	
	return text.toString();
}


/**
 * Patient patient: The patient to get the history from
 * String[] types: The incident entry types to fetch. Leave it null, to select all types.
 * Date fromDate: The earliest date to get the history. Leave it null, to set no start date.
 * Date toDate: The latest date to get the history. Leave it null, to set no end date.
 * Integer max: The maximum number of results. Leave it null, to set no maximum.
 * return: The incidents of the patient.
 */
function getPatientIncidents (patient, entryTypes, fromDate, toDate, max)
{
	var query = new java.lang.StringBuilder();
	
	
	if (patient == null || !patient.isPersistent().booleanValue())
		return null;
	
	// define the query
	query.append("SELECT OBJECT(i)")
			.append("\\nFROM Incident i")
			.append(entryTypes == null ? "" : ", IncidentEntry e, IncidentEntryType t")
			.append("\\nWHERE i.patientId = ").append(patient.getId());
	if (entryTypes != null)
	{
		query.append("\\nAND i.id = e.incidentId")
			.append("\\nAND t.id = e.entryTypeId")
			.append("\\nAND t.name IN (");
		for (var i = 0; i < entryTypes.length; i++)
			query.append(i == 0 ? "''" : ", ''").append(entryTypes[i]).append("''");
		query.append(")\\n");
	}
	if (fromDate != null)
		query.append("\\nAND i.incidentDate > ").append("''"+FORMATTER.formatDate(fromDate, "yyyy-MM-dd")+"''");
	if (toDate != null)
		query.append("\\nAND i.incidentDate < ").append("''"+FORMATTER.formatDate(toDate, "yyyy-MM-dd")+"''");
	query.append("\\nORDER BY i.incidentDate DESC");
	
	if (max == null || max < 1)
		return DATABASE_MANAGER.doHqlQuery(query.toString());
	else if (max == 1)
		return DATABASE_MANAGER.getHqlQueryObject(query.toString());
	else
		return DATABASE_MANAGER.doHqlQuery(query.toString(), null, max);
}


/**
 * IncidentEntry entry: The incident entry to format
 * StringBuilder text: The 
 * boolean asTable: Whether or not the text shall be formatted as HTML. DEFAULT: true
 * String[] filterTypes: The incident entry types to append or null to include all types.
 * return: The data of the incident entry
 */
function appendIncidentRows (incident, text, asTable, filterTypes)
{
	if (typeof asTable == "undefined")
		asTable = true;
	
	var type;
	var entries;
	var rows;
	var incidentDate;
	var physician;
	var iter;
	var entry;
	var content;
	var isAccident;
	var list;
	
	
	// check
	entries      = incident.getIncidentEntries();
	if (entries == null || entries.isEmpty())
		return text;
	
	isAccident   = incident.getIsAccident().booleanValue()
	
	if (typeof filterTypes != "undefined" 
			&& filterTypes != null 
			&& filterTypes.length > 0)
	{
		var filteredEntries = new java.util.LinkedList();
		for (iter = entries.iterator(); iter.hasNext(); )
		{
			// get the current entry and its type
			entry = iter.next();
			type  = entry.getEntryType().getName();
		
			if (stringArrayContains(filterTypes, type))
			{
				// add entry to filteredEntries
				filteredEntries.add(entry);
			} 
			// else: filter this entry -> just don''t put it into the filteredEntries list
		}
		entries = filteredEntries;
		
		// set the filter for accidents
		isAccident = isAccident && stringArrayContains(filterTypes, "accident");
	}
	
	rows         = entries.size() + (isAccident ? 1 : 0);
	incidentDate = incident.getIncidentDate();
	physician    = getPhysician(incident.getPhysicianId());
	
	// define the row and the date / physician cell
	if (asTable)
	{
		text.append("\\n<tr valign=\\"top\\">\\n  <td rowspan=\\"")
				.append(rows)
				.append("\\" nowrap>");
	}
	text.append("<b>")
			.append(FORMATTER.formatDate(incidentDate))
			.append(asTable ? "</b><br>" : "</b>&nbsp;")
			.append("<span style=\\"font-size:10px\\">")
			.append(FORMATTER.formatDate(incidentDate, "HH:mm"));
	if (physician != null) 
		text.append(" - ")
				.append(physician.getMnemonic());
	text.append("</span>");
	
	if (asTable)
		text.append("</td>\\n");
	else
		text.append("<br>");
	
	if (isAccident)
	{
		var accidentDate = incident.getAccidentDate();
		var accidentNr   = incident.getAccidentNr();
		
		if (asTable)
			text.append("\\n  <td nowrap>");
		text.append("<u>")
				.append(tryTranslate("pm.", "accident"));
		
		if (accidentDate != null || accidentNr != null)
		{
			text.append("</u>");
			if (asTable)
				text.append("</td>\\n  <td>");
			
			if (accidentNr != null)
				text.append(accidentNr);
			if (accidentDate != null && accidentNr != null)
				text.append(" - ");
			if (accidentDate != null)
				text.append(FORMATTER.formatDate(accidentDate))
			
			if (asTable)
				text.append("</td>\\n</tr>\\n<tr valign=\\"top\\">\\n");
			else
				text.append("<br>");
		}
	}
	
	var Pattern           = java.util.regex.Pattern;
	var htmlStartPattern  = Pattern.compile("<(?i)html(?-i)\\\\>");
	var htmlHeaderPattern = Pattern.compile("<(?i)head(?-i)\\\\>");
	var newlinePattern    = Pattern.compile("$(?=.)", Pattern.MULTILINE | Pattern.DOTALL);
	
	// run through the entries and add a row for each
	for (iter = entries.iterator(); iter.hasNext(); )
	{
		// get the current entry and its type
		entry = iter.next();
		type  = entry.getEntryType().getName();
		
		if (type == null
				|| type.startsWith("soap.")
				|| type.startsWith("cons.")
				|| type.equals("letter")
				|| type.equals("form")
				|| type.equals("dicom"))
		{
			// this is the default way to get the content of an incident entry ... 
			// ... get the text content ...
			content = entry.getTextContent();
			if (content == null)
			{
				content = new java.lang.String("");
			}
			else if (htmlStartPattern.matcher(content).find())
			{
				if (htmlHeaderPattern.matcher(content).find())
					content = content.substring(content.indexOf("</head>") + 7);
				
				content = content
						.replace("<html>",  "")
						.replace("</html>", "")
						.replace("<body",   "<span")
						.replace("</body>", "</span>");
			}
			else
			{
				content = newlinePattern.matcher(content).replaceAll("<br>");
				// content.replace("\\n", "<br>");
			}
			
			// ... abd the code value ...
			var code = entry.getCode();
			
			if (code != null && !code.isEmpty())

			{
				var label = new java.lang.String("patient.incident.code." + type);
				var codeLabel = tryTranslate(label);
				
				if (codeLabel == null || codeLabel.equals(label))
				{
					codeLabel = tryTranslate("patient.incident.code");
					if (codeLabel == null)
						codeLabel = new java.lang.String("Code");
				}
				
				content = new java.lang.StringBuilder(content)
						.append(content.isEmpty() ? "<p><i>" : "<br><p><i>")
						.append(codeLabel)
						.append(":</i> ")
						.append(code)
						.append("</p>")
						.toString();
			}
			
			// ... try to translate the type ...
			if (type == null || type.trim().length == 0)
				type = new java.lang.String("");
			else
				type = tryTranslate("patient.incident.", type);
		}
		else if (type.equals("file"))
		{
			// get the content for an attached file
			type    = tryTranslate("core.", type);
			content = entry.getTextContent();
			if (content == null || content.trim().length == 0)
				content = entry.getOriginalFilename();
			else 
				content = new java.lang.String(content + "<br><span style=\\"font-size:12px\\"><i>" + entry.getOriginalFilename() + "</i></span>");
		}
		else if (type.equals("prescription"))
		{
			// get the content for a presciption
			type    = tryTranslate("pm.", type);
			list    = DATABASE_MANAGER.doHqlQuery(
					"SELECT OBJECT(p) FROM Prescription p WHERE p.incidentEntryId = " + 
					entry.getId());
			
			if (list == null || list.isEmpty())
				// no prescription found
				content = new java.lang.String("");
			else
				// get the text of the prescription
				content = list.get(0).getTextContent().replace("\\n", "<br>");
		}
		else if (type.equals("measurement"))
		{
			// get the content for a measurement
			type    = tryTranslate("patient.incident.", type);
			content = getMeasurementContent(entry, asTable);
		}
		else if (type.equals("labo.result"))
		{
			// get the content for a labo result
			type    = tryTranslate("patient.history.", "labo");
			content = entry.getTextContent();
		}
		else if (type.equals("sick_leave"))
		{
			// print the sickleave
			var code  = entry.getCode();
			var start = entry.getSickLeaveStartDate();
			var end   = entry.getSickLeaveEndDate();
			
			type = tryTranslate("patient.incident." + type);
			if (type == null || type.trim().isEmpty())
				type = new java.lang.String("Sickleave");
			
			content = code != null ? code : new java.lang.String("");
			// no sense to print only start or end date, there for it is only printen, if both are set
			if (start != null && end != null)
			{
				content = new java.lang.StringBuilder(content)
						.append(code == null ? "" : " (")
						.append(FORMATTER.formatDate(start))
						.append(" - ")
						.append(FORMATTER.formatDate(end))
						.append(code == null ? "" : ")")
						.toString();
			}
		}
		else
		{
			// show, that there is an error
			// NEED TO ADD A ROW, otherwise the table will be messed up
			content = entry.getTextContent();
			print("UNSUPPORTED TYPE: " + type + " (" + content + ")" + "\\n");
		}
		
		// replace the HTML start tag, as this may mess up the HTML table
		//content = content.replace("<html>", "");
		
		if (asTable)
			text.append("\\n  <td nowrap><u>");
		else
			text.append("\\n<div  style=\\"margin-top: 5px, margin-bottom: 0px\\"><u>");
		
		if (type != null)
			text.append(type);
		
		if (asTable)
			text.append("</u></td>\\n  <td>");
		else
			text.append(":</u></div>\\n");
		
		if (content != null)
			text.append("<p>").append(content).append("</p>");
		
		if (asTable)
		{
			text.append("</td>\\n</tr>\\n");
			if (iter.hasNext())
				text.append("<tr valign=\\"top\\">");
		}
	}
	if (!asTable)
		text.append("<br>\\n");
	return text;
}


/**
 * IncidentEntry entry: The entry to fetch the measurements from
 * boolean asTable: Whether or not the text shall be formatted as HTML. DEFAULT: true

 * return: An HTML formatted string, representing the measurements
 */
function getMeasurementContent (entry, asTable)
{
	var Double = java.lang.Double;
	var Boolean = java.lang.Boolean;
	var Math = java.lang.Math;
	var Array = java.lang.reflect.Array;
	var StringBuilder = java.lang.StringBuilder;
	var LinkedList = java.util.LinkedList;
	
	var values;
	var iter;
	var value;
	var type;
	var content;
	var tabbed = false;
	
	
	if (typeof asTable == "undefined")
		asTable = true;
	
	content = new StringBuilder();
	
	// get the measurement values of the given incident entry
	values    = DATABASE_MANAGER.doHqlQuery(
			"SELECT OBJECT(v) FROM MeasurementValue v WHERE incidentEntryId = " + entry.getId());
	
	var orderedValues    = Array.newInstance(values.iterator().next().getClass(), 9);
	for (iter = values.iterator(); iter.hasNext(); )
	{
		value = iter.next();
		type  = value.getMeasurementType();
		
		if (type.getName().equals("SYS"))
			orderedValues[0] = value;
		else if (type.getName().equals("DIA"))
			orderedValues[1] = value;
		else if (type.getName().equals("POULSE"))
			orderedValues[2] = value;
		else if (type.getName().equals("HEIGHT"))
			orderedValues[3] = value;
		else if (type.getName().equals("WEIGHT"))
			orderedValues[4] = value;
		// else: here should stand the BMI, but it must be calculated first
		else if (type.getName().equals("measurement_1"))
			orderedValues[6] = value;
		else if (type.getName().equals("measurement_2"))
			orderedValues[7] = value;
		else if (type.getName().equals("measurement_3"))
			orderedValues[8] = value;
	}
	
	if (       orderedValues[3] != null 
			&& orderedValues[3].getValueNumeric() != null
			&& orderedValues[4] != null 
			&& orderedValues[4].getValueNumeric() != null)
	{
		var height = orderedValues[3].getValueNumeric().doubleValue();
		var weight = orderedValues[4].getValueNumeric().doubleValue();
		var bmi    = Double.parseDouble(Math.round(weight / (height * height) * 10) / 10);
		var mv     = orderedValues[4].clone();
		var mt     = mv.getMeasurementType().clone();
		
		mv.setMeasurementType(mt);
		mt.setAlias("bmi");
		mt.setName("bmi");
		mt.setUnit("kg/m2");
		mt.setDefault(Boolean.TRUE);
		mt.setNumericType(Boolean.TRUE);
		mv.setValueNumeric(Double.valueOf(bmi));
		mv.setId(null);
		orderedValues[5] = mv;
	}
	
	values = new LinkedList();
	for (var index = 0; index < orderedValues.length; index++)
	{
		value = orderedValues[index];
		if (value != null)
			values.add(value);
	}
	
	for (iter = values.iterator(); iter.hasNext(); )
	{
		value = iter.next();
		type  = value.getMeasurementType();
		value = type.isNumericType() ? value.getValueNumeric() : value.getValueString();
		
		if (value == null)
			// leave this value
			continue;
		
		if (type.getAlias() != null && type.getAlias().trim().length() > 0)
			content.append("")
					.append(tryTranslate("patient.incident.measurement.", type.getAlias()))
					.append(": ");
		content.append("")
				.append(value)
				.append(" ")
				.append(type.getUnit());
		if (iter.hasNext())
			content.append("<br>");
	}
	
	return content.toString();
}
');

SELECT letter.insert_letter_placeholder(E'[PAT_CALENDAR_FUTURE_APPOINTMENTS]', E'PATIENT_CALENDAR', E'The up to 10 next appointments of this patient. Every appointment in its own line.', E'/**
 * [PAT_CALENDAR_FUTURE_APPOINTMENTS]
 */
var cal = clearCalendar();
cal.add(java.util.Calendar.DAY_OF_YEAR, 1);
printAppointments (PATIENT, cal, null, 10, false);');
SELECT letter.insert_letter_placeholder(E'[PAT_CALENDAR_FUTURE_APPOINTMENTS_SINGLE_LINE]', E'PATIENT_CALENDAR', E'The up to 10 next appointments of this patient in one line, separated by a comma.', E'/**
 * [PAT_CALENDAR_FUTURE_APPOINTMENTS_SINGLE_LINE]
 */
var cal = clearCalendar();
cal.add(java.util.Calendar.DAY_OF_YEAR, 1);
printAppointments (PATIENT, cal, null, 10, true);');
SELECT letter.insert_letter_placeholder(E'[PAT_CALENDAR_LAST_APPOINTMENT]', E'PATIENT_CALENDAR', E'The last appointment of the patient.', E'/**
 * [PAT_CALENDAR_LAST_APPOINTMENT]
 */
var cal = new java.util.GregorianCalendar();
printAppointments (PATIENT, null, cal, 1, true, false);');
SELECT letter.insert_letter_placeholder(NULL, E'PATIENT_CALENDAR', E'Functions available to all PATIENT_CALENDAR placeholders', E'/**
 * PATIENT CALENDAR FUNCTIONS
 */

/**
 * Patient patient: The patient to get the appointments from. DEFAULT: PATIENT
 * Date / Calendar startDate: Start date to fetch the appointments or null, to have no start date. DEFAULT: null
 * Date / Calendar endDate: The end date to stop fetching the appontments or null, to have no end date. DEFAULT: null
 * Integer / int maxAppointments: The maximal number of appointments or null, to have no limit. DEFAULT: null
 * boolean searchForward: Needed together with maxAppointments, to define where the counting should start. DEFAULT: true
 * boolean ascendingOrder: Defines in which order the appointments are returned.
 * reutrn: An ArrayList of appointments of this patient.
 */
function getAppointments (patient, startDate, endDate, maxAppointments, searchForward, ascendingOrder)
{
	var Calendar		= java.util.Calendar;
	var singleAppParams = new java.util.ArrayList(3);
	var recurrAppParams = new java.util.ArrayList(2);
	var singleAppQuery  = new java.lang.StringBuilder();
	var recurrAppQuery  = new java.lang.StringBuilder();
	var Integer         = java.lang.Integer;
	var result;
	var appointments;
	
	
	if (typeof patient == "undefined")
		patient = PATIENT;
	
	// transform from Calendar to Date
	if (startDate != null && startDate instanceof Calendar)
		startDate = startDate.getTime();
	if (endDate != null && endDate instanceof Calendar)
		endDate = endDate.getTime();
	
	// check values and init defaults
	if (typeof searchForward == "undefined")
		searchForward = (startDate != null && endDate != null)
				? startDate.before(endDate)
				: endDate != null && maxAppointments != null
				? false
				: true;
	
	if (endDate != null 
			&& startDate != null 
			&& endDate.before(startDate))
	{
		// switch the dates
		var tmp = endDate;
		endDate = startDate;
		startDate = tmp;
	}
	
	if (typeof ascendingOrder == "undefined")
		ascendingOrder = true;
	
	if (typeof maxAppointments == "undefined")
		maxAppointments = null;
	
	
	// add the patient ID to the parameter
	singleAppQuery.append("SELECT Object(a) FROM Appointment a\\nWHERE a.patientId = ?\\n");
	singleAppParams.add(patient.getId());
	recurrAppQuery.append("SELECT Object(a) FROM Appointment a\\nWHERE a.patientId = ?\\n");
	recurrAppParams.add(patient.getId());
	if (startDate != null)
	{
		singleAppQuery.append("AND (a.startDate >= ? AND a.frequency = 0)\\n");
		singleAppParams.add(startDate);
		recurrAppQuery.append("AND a.frequency > 0\\n");
	}
	if (endDate != null)
	{
		singleAppQuery.append("AND a.startDate <= ?\\n");
		singleAppParams.add(endDate);
		recurrAppQuery.append("AND a.startDate <= ?\\n");
		recurrAppParams.add(endDate);
	}
	// don''t check the order here, as it will be put into a TreeSet anyway
	singleAppQuery.append("ORDER BY a.startDate ")
			.append(searchForward ? "ASC" : "DESC");
	
	
	// query the appointments (without recurring appointments)
	appointments = new java.util.TreeSet();
	result = DATABASE_MANAGER.doHqlQuery(singleAppQuery.toString(), null, maxAppointments, singleAppParams);
	if (result != null && !result.isEmpty())
		appointments.addAll(result);
	
	// query the recurring appointments
	result = DATABASE_MANAGER.doHqlQuery(recurrAppQuery.toString(), null, maxAppointments, recurrAppParams);
	
	// take the recurring appointment entries and produce fix appointments
	if (endDate == null)
	{
		var cal = new java.util.GregorianCalendar();
		cal.add(Calendar.YEAR, 1);
		endDate = cal.getTime();
	}
	
	var recurredEvents;
	var singleApp;
	var app;
	var event;
	var count;
	for (var appIter = result.iterator(); appIter.hasNext(); )
	{
		app = appIter.next();
		if (app.getFrequency() == 0)
			continue;
		
		// create the recursive appointments
		recurredEvents = CLASS_LOADER.execute("lu.tudor.santec.gecamed.agenda.gui.DefaultNamedCalendar", 
				"createRecurrEvents", app, endDate, java.util.Locale.getDefault());
		
		if (recurredEvents == null)
			continue;
		
		count = 0;
		for (var eventIter = recurredEvents.iterator(); eventIter.hasNext(); )
		{
			if (searchForward && maxAppointments != null && count > maxAppointments)
				break;
			
			event = eventIter.next();
			
			if ((searchForward && event.getStart().after(startDate))
					|| !searchForward && event.getStart().after(endDate))
			{
				singleApp = CLASS_LOADER.create("lu.tudor.santec.gecamed.agenda.ejb.entity.beans.Appointment");
				
				// transfer the data from the event to the appointment
				singleApp.setDescription(event.getDescription());
				singleApp.setEndDate(event.getEnd());
				singleApp.setStartDate(event.getStart());
				singleApp.setSummary(event.getSummary());
				
				// transfer the data from the recurring appointment to the single appointment
				singleApp.setCalendarId(app.getCalendarId());
				singleApp.setCreated(app.getCreated());
				singleApp.setCreatedBy(app.getCreatedBy());
				singleApp.setFrequency(0);
				singleApp.setIsBackground(app.getIsBackground());
				singleApp.setPatientId(app.getPatientId());
				singleApp.setPrivate(app.isPrivate());
				singleApp.setRRule(app.getRRule());
				singleApp.setStatus(app.getStatus());
				singleApp.setTypeId(app.getTypeId());
				singleApp.setUntil(app.getUntil());
				
				// add this of the recurring appointment created appointments
				appointments.add(singleApp);
			}
			count++;
		}
	}
	
	if (maxAppointments != null)
	{
		if (searchForward)
			while (appointments.size() > maxAppointments)
				appointments.pollLast();
		else // if (!searchForward)
			while (appointments.size() > maxAppointments)
				appointments.pollFirst();
	}
	
	var resultList = new java.util.ArrayList(appointments.size());
	if (ascendingOrder)
	{
		resultList.addAll(appointments);
	}
	else
	{
		while (!appointments.isEmpty())
			resultList.add(appointments.pollLast());
	}
	
	return resultList;
}


/**
 * Loads the physician belonging to a calendar from the database 
 * and stores it in the script context or, if it was already loaded 
 * into the script context, takes it directly from there.
 * 
 * Integer calendarId: The id of the calendar, to get the physician from
 * return: The physician, that belongs to that calendar or null, if there 
 *   is not physician for that calendar
 */
function getPhysicianOfCalendar (calendarId)
{
	if (calendarId == null)
		return null;
	
	// create the key to search for the physician in the script context
	var key       = "physicianOfCalendarId_" + java.lang.String.valueOf(calendarId);
	/* This is null, if it wasn''t searched for this calendar ID, 
	 * a physician if it was already searched for this calendar ID and a physician was found
	 * or Boolean.FALSE, if it was searched for this calendar ID and no physician was found.
	 */
	var physician = SCRIPT_CONTEXT.get(key);
	
	if (physician == null)
	{
		// physician is not yet stored in the script context
		// load it from the DB
		var parameters = new java.util.ArrayList(1);
		parameters.add(calendarId);
		physician = DATABASE_MANAGER.getHqlQueryObject(
				"SELECT Object(p) FROM Physician p, AgendaCalendar c " + 
				"WHERE c.id = ? AND c.physicianId = p.id", 
				parameters);
		
		if (physician == null)
		{
			SCRIPT_CONTEXT.put(key, java.lang.Boolean.FALSE);
		}
		else
		{
			// store the physician in the script context
			SCRIPT_CONTEXT.put(key, physician);
		}
	}
	
	if (java.lang.Boolean.FALSE.equals(physician))
		return null;
	else
		return physician;
}


/**
 * Patient patient: The patient to get the appointments from. DEFAULT: PATIENT
 * Date / Calendar startDate: Start date to fetch the appointments or null, to have no start date. DEFAULT: null
 * Date / Calendar endDate: The end date to stop fetching the appontments or null, to have no end date. DEFAULT: null
 * Integer / int maxAppointments: The maximal number of appointments or null, to have no limit. DEFAULT: null
 * boolean asSingleLine: Defines wether the appointments shall be printed in one line, separated by a ",", or if each 
 *   appointment shall have its own line. DEFAULT: false
 * boolean searchForward: Needed together with maxAppointments, to define where the counting should start. DEFAULT: true
 * boolean ascendingOrder: Defines in which order the appointments are returned.
 * return: A String, representing the appointments of the patient
 */
function printAppointments (patient, startDate, endDate, maxAppointments, asSingleLine, searchForward, ascendingOrder)
{
	// check values and init defaults
	if (typeof asSingleLine == "undefined")
		asSingleLine = false;
	
	var appointments    = getAppointments(patient, startDate, endDate, maxAppointments, searchForward, ascendingOrder);
	var text            = new java.lang.StringBuilder();
	var appendSeparator = false;
	var start;
	var physician;
	var appointment
	
	
	for (var iter = appointments.iterator(); iter.hasNext(); )
	{
		appointment = iter.next();
		
		if (appendSeparator)
		{
			if (asSingleLine)
				text.append(", ");
			else 
				text.append("\\n");
		}
		else
		{
			appendSeparator = true;
		}
		
		start     = appointment.getStartDate();
		physician = getPhysicianOfCalendar(appointment.getCalendarId());
		text.append(FORMATTER.formatDate(start))
				.append(FORMATTER.formatDate(start, " HH:mm"));
		if (physician != null)
			text.append(" (")
					.append(FORMATTER.formatPhysicianName(physician, true))
					.append(")");
	}
	
	return text.toString();
}');

SELECT letter.insert_letter_placeholder(E'[PAT_ID_EUROPE]', E'PATIENT_ID', E'The patient''s European ID', E'/**
 * [PAT_ID_EUROPE]
 */

PATIENT.getIdEuropean() == null ? "" : PATIENT.getIdEuropean()');
SELECT letter.insert_letter_placeholder(E'[PAT_ID_FUTURE_NATIONAL]', E'PATIENT_ID', E'The patient''s future national ID', E'/**
 * [PAT_ID_FUTURE_NATIONAL]
 */

PATIENT.getIdFutureNational() == null ? "" : PATIENT.getIdFutureNational()');
SELECT letter.insert_letter_placeholder(E'[PAT_ID_GECAMED]', E'PATIENT_ID', E'The patient''s internal GECAMed ID', E'/**
 * [PAT_ID_GECAMED]
 */

PATIENT.getId() + ""');
SELECT letter.insert_letter_placeholder(E'[PAT_ID_HIS_RIS]', E'PATIENT_ID', E'The patient''s HIS/RIS ID', E'/**
 * [PAT_ID_HIS_RIS]
 */

PATIENT.getIdRIS() == null ? "" : PATIENT.getIdRIS()');
SELECT letter.insert_letter_placeholder(E'[PAT_ID_LUX]', E'PATIENT_ID', E'The patient''s Lux ID', E'/**
 * [PAT_ID_LUX]
 */

PATIENT.getIdLuxembourg() == null ? "" : PATIENT.getIdLuxembourg()');
SELECT letter.insert_letter_placeholder(E'[PAT_ID_MR]', E'PATIENT_ID', E'The patient''s MR identification number (MR = Medecin Referent)', E'/**
 * [PAT_ID_MR]
 */

 var id = PATIENT.getMrDeclarationNo();
 
 id == null ? "" : id;');
SELECT letter.insert_letter_placeholder(E'[PAT_SSN]', E'PATIENT_ID', E'The patient''s SocialSecurity Number', E'/**
 * [PAT_SSN]
 */

// PATIENT.getSocialSecurityNumber() == null ? "" : PATIENT.getSocialSecurityNumber();

// use the same formating as in the print templates
var ssn = PATIENT.getSocialSecurityNumber();

FORMATTER.formatSSN(ssn);');
SELECT letter.insert_letter_placeholder(E'[PAT_SSN_SUFFIX]', E'PATIENT_ID', E'The suffix of the patient''s SocialSecurity Number. i.e. everything beyond the 8th digit', E'/**
 * [PAT_SSN_SUFFIX]
 */

PATIENT.getSocialSecurityNumber() == null ? "" : 
    PATIENT.getSocialSecurityNumber().substring(8).trim();');
SELECT letter.insert_letter_placeholder(E'[PAT_BIRTHDATE]', E'PATIENT_INFO', E'The patient''s birthdate', E'/**
 * [PAT_BIRTHDATE]
 */

PATIENT.getBirthDate() == null ? "" : 
    FORMATTER.formatDate(PATIENT.getBirthDate());');
SELECT letter.insert_letter_placeholder(E'[PAT_BIRTHPLACE]', E'PATIENT_INFO', E'The patient''s birthplace', E'/**
 * [PAT_BIRTHPLACE]
 */

PATIENT.getBirthLocality() == null ? "" : PATIENT.getBirthLocality();');
SELECT letter.insert_letter_placeholder(E'[PAT_INSURANCE_COMPLEMENTARY]', E'PATIENT_INFO', E'The patient''s complementary insurance policy', E'/**
 * [PAT_INSURANCE_COMPLEMENTARY]
 */
var insurance = PATIENT.getComplementary();
 
insurance == null ? "" : insurance.getName();');
SELECT letter.insert_letter_placeholder(E'[PAT_INSURANCE_PRIMARY]', E'PATIENT_INFO', E'The patient''s primary insurance company, full name', E'/**
 * [PAT_INSURANCE_PRIMARY]
 */
var insurance = PATIENT.getInsurance();
 
insurance == null ? "" : insurance.getName();');
SELECT letter.insert_letter_placeholder(E'[PAT_INSURANCE_PRIMARY_SHORT]', E'PATIENT_INFO', E'The patient''s primary insurance company', E'/**
 * [PAT_INSURANCE_PRIMARY_SHORT]
 */
var insurance = PATIENT.getInsurance();
 
insurance == null ? "" : insurance.getAcronym();');
SELECT letter.insert_letter_placeholder(E'[PAT_MARITAL_STATUS]', E'PATIENT_INFO', E'The patient''s marital status', E'/**
 * [PAT_MARITAL_STATUS]
 */
 
tryTranslate("MaritalStatus.", PATIENT.getMaritalStatus());');
SELECT letter.insert_letter_placeholder(E'[PAT_ACTIVE_PROBLEMS]', E'PATIENT_MEDICAL', E'The active problems of the patient as text as entered in GECAMed', E'/**
 * [PAT_ACTIVE_PROBLEMS]
 */
var text = "";
var patientDatas = getPatientDatas(PATIENT.getId());
if (patientDatas != null)
{
	text = patientDatas.getActiveProblem();
	if (text == null)
		text = "";
}
text;');
SELECT letter.insert_letter_placeholder(E'[PAT_ALLERGIES]', E'PATIENT_MEDICAL', E'The allergies of the patient', E'/**
 * [PAT_ALLERGIES]
 */
var allergies = getPatientAllergies(PATIENT.getId());
var iter;
var element;
var text = new java.lang.StringBuilder();


if (allergies != null)
{
	iter = allergies.iterator();
	while (iter.hasNext())
	{
		element = iter.next();
		text.append(element.getAllergenName());
		if (element.getComment() != null && !("".equals(element.getComment()))) 
		{
		    text.append(" (").append(element.getComment()).append(")");
		}
		if (iter.hasNext())
			text.append("\\n");
	}
}
text.toString();');
SELECT letter.insert_letter_placeholder(E'[PAT_ANTECEDENTS]', E'PATIENT_MEDICAL', E'The antecedents of the patient', E'/**
 * [PAT_ANTECEDENTS]
 */
var text = new java.lang.StringBuffer("");
var antecedents = getPatientAntecedents(PATIENT.getId());
var iter;
var desc;
var element;


if (antecedents != null)
{
	iter = antecedents.iterator();
    while (iter.hasNext()) 
    {
		element = iter.next();
		text.append("<b>").append(element.getShortcut()).append("</b>");
		desc = element.getDescription();
		if (desc != null && desc.trim().length() > 0) 
			text.append(desc);
		else
			text.append("<br>");
    }
}
text = text.toString()
		.replaceAll("\\\\</?(html|head|body)\\\\>", "");
"<html>" + text + "</html>";');
SELECT letter.insert_letter_placeholder(E'[PAT_CHRONIC_TREATMENTS]', E'PATIENT_MEDICAL', E'The chronic treatments of the patient as text as entered in GECAMed', E'/**
 * [PAT_CHRONICLE_TREATMENTS]
 */
var text = "";
var patientDatas = getPatientDatas(PATIENT.getId());
if (patientDatas != null)
{
	text = patientDatas.getChronicalTreatments();
	if (text == null)
		text = "";
}
text;
');
SELECT letter.insert_letter_placeholder(NULL, E'PATIENT_MEDICAL', E'Functions available to all PATIENT_MEDICAL placeholders', E'/**
 * PATIENT_MEDICAL FUNCTIONS
 */

/**
 * The bean PatientDatas contains the chronicle treatments (getChronicalTreatments()) 
 * and the active problems (getActiveProblem()) of a patient as string
 * 
 * int patientId: The ID of the patient to get the data from
 * return: The PatientDatas bean of the specified patient
 */
function getPatientDatas (patientId)
{
	var query = "SELECT OBJECT(o) FROM PatientDatas o WHERE o.patientId = " + patientId;
	
	return DATABASE_MANAGER.getHqlQueryObject(query);
}



/**
 * int patientId: The ID of the patient to get the allergies from
 * return: A list of allergies, entered for the patient with the specified ID
 */
function getPatientAllergies (patientId)
{
	var query = "SELECT OBJECT(o) FROM Allergies o" +
		"\\nWHERE o.patientId = " + patientId + 
		"\\nAND o.deletionDate IS NULL" +
		"\\nORDER BY o.created DESC";
	return DATABASE_MANAGER.doHqlQuery(query);
}



/**
 * int patientId: The ID of the patient to get the antecedents from
 * return: A list of antecedents, entered for the patient with the specified ID
 */
function getPatientAntecedents (patientId)
{
	var userId = USER.getId();
	var query = "SELECT OBJECT(o) FROM Antecedents o" +
			"\\nWHERE o.patientId = " + patientId + 
			"\\nAND o.deletedDate IS NULL" +
			"\\nAND" +
			"\\n((o.lockedBy IS NOT NULL" +
			"\\n  AND o.lockedBy = " + userId + ")" +
			"\\n  OR o.lockedBy IS NULL)" +
			"\\nORDER BY o.created DESC";
	return DATABASE_MANAGER.doHqlQuery(query);
}');
SELECT letter.insert_letter_placeholder(E'[PAT_FIRST_NAMES]', E'PATIENT_NAME', E'The patient''s first name', E'/**
 * [PAT_FIRST_NAMES]
 */
toString(PATIENT.getFirstName());');
SELECT letter.insert_letter_placeholder(E'[PAT_FULL_NAME]', E'PATIENT_NAME', E'The patient''s full name, with title', E'/**
 * [PAT_FULL_NAME]
 */
toString(FORMATTER.formatPatientName(true, false));');
SELECT letter.insert_letter_placeholder(E'[PAT_LAST_NAME]', E'PATIENT_NAME', E'The patient''s name', E'/**
 * [PAT_LAST_NAME]
 */
toString(PATIENT.getSurName());');
SELECT letter.insert_letter_placeholder(E'[PAT_MAIDEN_NAME]', E'PATIENT_NAME', E'The patient''s given name', E'/**
 * [PAT_MAIDEN_NAME]
 */
toString(PATIENT.getMaidenName());');
SELECT letter.insert_letter_placeholder(E'[PAT_TITLE_LONG]', E'PATIENT_NAME', E'The patient''s long title: M., Mme, Mlle (FR) - Herr, Frau, Fräulein (DE) - Mr., Mrs. Ms. (EN)"Monsieur, Madame, Mademoiselle (FR) - Herr, Frau, Fräulein (DE) - Sir, Madam, Miss (EN)', E'/**
 * [PAT_TITLE_LONG]
 */
toString(FORMATTER.translate("Title.Long." + PATIENT.getTitle()));');
SELECT letter.insert_letter_placeholder(E'[PAT_TITLE_SHORT]', E'PATIENT_NAME', E'The patient''s short title: M., Mme, Mlle (FR) - Herr, Frau, Fräulein (DE) - Mr., Mrs. Ms. (EN)', E'/**
 * [PAT_TITLE_SHORT]
 */
toString(FORMATTER.translate("Title." + PATIENT.getTitle()));');
SELECT letter.insert_letter_placeholder(E'[PHYS_ALL_PHONE_AND_EMAIL]', E'PHYSICIAN', E'The physician''s phone numbers and email, on seperate lines', E'/**
 * [PHYS_ALL_PHONE_AND_EMAIL]
 */

var result = "";

var phone = PHYSICIAN.getPhoneExtension();
var mobile = PHYSICIAN.getGsm();
var fax = PHYSICIAN.getFax();
var email = PHYSICIAN.getEmail();

if (phone != null && !phone.isEmpty())
	result = "Tel: " + phone;

if (mobile != null && !mobile.isEmpty())
	result = result + (result.length == 0 ? "" : "\\n")
		+ "Mobile: " + mobile;

if (fax != null && !fax.isEmpty())
	result = result + (result.length == 0 ? "" : "\\n")
		+ "FAX: " + fax;

if (email != null && !email.isEmpty())
	result = result + (result.length == 0 ? "" : "\\n")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[PHYS_ALL_PHONE_AND_EMAIL_SINGLE_LINE]', E'PHYSICIAN', E'The physician''s phone numbers and email, in one single line', E'/**
 * [PHYS_ALL_PHONE_AND_EMAIL_SINGLE_LINE]
 */

var result = "";
var phone = PHYSICIAN.getPhoneExtension();
var mobile = PHYSICIAN.getGsm();
var fax = PHYSICIAN.getFax();
var email = PHYSICIAN.getEmail();

if (phone != null && !phone.isEmpty())
	result = "Tel: " + phone;

if (mobile != null && !mobile.isEmpty())
	result = result + (result.length == 0 ? "" : "; ")
		+ "Mobile: " + mobile;

if (fax != null && !fax.isEmpty())
	result = result + (result.length == 0 ? "" : "; ")
		+ "FAX: " + fax;

if (email != null && !email.isEmpty())
	result = result + (result.length == 0 ? "" : "; ")
		+ "eMail: " + email;

result;');
SELECT letter.insert_letter_placeholder(E'[PHYS_CNS_CODE]', E'PHYSICIAN', E'The physician''s CNS code', E'/**
 * [PHYS_CNS_CODE]
 */

PHYSICIAN.getUcmCode() == null ? "" : PHYSICIAN.getUcmCode();');
SELECT letter.insert_letter_placeholder(E'[PHYS_EMAIL]', E'PHYSICIAN', E'The physician''s eMail address', E'/**
 * [PHYS_EMAIL]
 */

PHYSICIAN.getEmail() == null ? "" : PHYSICIAN.getEmail();');
SELECT letter.insert_letter_placeholder(E'[PHYS_FAX]', E'PHYSICIAN', E'The physician''s fax number', E'/**
 * [PHYS_FAX]
 */

PHYSICIAN.getFax() == null ? "" : PHYSICIAN.getFax();');
SELECT letter.insert_letter_placeholder(E'[PHYS_FIRST_NAMES]', E'PHYSICIAN', E'The physician''s first names', E'/**
 * [PHYS_FIRST_NAMES]
 */

PHYSICIAN.getFirstName() == null  
   ? "" :PHYSICIAN.getFirstName();');
SELECT letter.insert_letter_placeholder(E'[PHYS_FULL_NAME]', E'PHYSICIAN', E'The physician''s full name, with title', E'/**
 * [PHYS_FULL_NAME]
 */

// use the same formating as in the print template
FORMATTER.formatPhysicianName(true) ;');
SELECT letter.insert_letter_placeholder(E'[PHYS_LAST_NAME]', E'PHYSICIAN', E'The physician''s last name', E'/**
 * [PHYS_LAST_NAME]
 */

PHYSICIAN.getName() == null  
   ? "" :PHYSICIAN.getName();');
SELECT letter.insert_letter_placeholder(E'[PHYS_MOBILE]', E'PHYSICIAN', E'The physician''s cell phone number', E'/**
 * [PHYS_MOBILE]
 */

PHYSICIAN.getGsm() == null ? "" : PHYSICIAN.getGsm();');
SELECT letter.insert_letter_placeholder(E'[PHYS_PHONE]', E'PHYSICIAN', E'The physician''s phone number', E'/**
 * [PHYS_PHONE]
 */

PHYSICIAN.getPhoneExtension() == null ? "" : PHYSICIAN.getPhoneExtension();');
SELECT letter.insert_letter_placeholder(E'[PHYS_SPECIALTY]', E'PHYSICIAN', E'The physician''s medical specialty', E'/**
 * [PHYS_SPECIALTY]
 */

PHYSICIAN.getSpeciality() == null ? "" : PHYSICIAN.getSpeciality();');
SELECT letter.insert_letter_placeholder(E'[PHYS_TITLE]', E'PHYSICIAN', E'The physician''s title', E'/**
 * [PHYS_TITLE]
 */

PHYSICIAN.getTitle() == null  
   ? "" :PHYSICIAN.getTitle();');
SELECT letter.insert_letter_placeholder(E'[PHYS_ADDRESS_COUNTRY]', E'PHYSICIAN_ADDRESS', E'The country of the physician''s address', E'/**
 * [PHYS_ADDRESS_COUNTRY]
 */
var adr = PHYSICIAN.getPhysicianAddress();
 
tryTranslate("Country.", adr == null ? null : adr.getCountry());');
SELECT letter.insert_letter_placeholder(E'[PHYS_ADDRESS_LOCALITY]', E'PHYSICIAN_ADDRESS', E'The locality of the physician''s address', E'/**
 * [PHYS_ADDRESS_LOCALITY]
 */

var adr = PHYSICIAN.getPhysicianAddress();

adr == null ? "" : (adr.getStreetNumber() == null ? "" : adr.getLocality());');
SELECT letter.insert_letter_placeholder(E'[PHYS_ADDRESS_NB]', E'PHYSICIAN_ADDRESS', E'The street number of the physician''s address', E'/**
 * [PHYS_ADDRESS_NB]
 */

var adr = PHYSICIAN.getPhysicianAddress();

adr == null ? "" : (adr.getStreetNumber() == null ? "" : adr.getStreetNumber());');
SELECT letter.insert_letter_placeholder(E'[PHYS_ADDRESS_STREET]', E'PHYSICIAN_ADDRESS', E'The street name of the physician''s address', E'/**
 * [PHYS_ADDRESS_STREET]
 */

var adr = PHYSICIAN.getPhysicianAddress();

adr == null ? "" : (adr.getStreetNumber() == null ? "" : adr.getStreetName());');
SELECT letter.insert_letter_placeholder(E'[PHYS_ADDRESS_ZIP]', E'PHYSICIAN_ADDRESS', E'The ZIP code of the physician''s address', E'/**
 * [PHYS_ADDRESS_ZIP]
 */

var adr = PHYSICIAN.getPhysicianAddress();

adr == null ? "" : (adr.getStreetNumber() == null ? "" : adr.getZip());');
SELECT letter.insert_letter_placeholder(E'[PHYS_FULL_ADDRESS]', E'PHYSICIAN_ADDRESS', E'<html>The physician''s full address. <br>Formatted in an address block <br>suited for the adress window of letters</html>', E'/**
 * [PHYS_FULL_ADDRESS]
 */

// use the same formating as in the print template
FORMATTER.formatPhysicianAddress();');
SELECT letter.insert_letter_placeholder(E'[PHYS_FULL_ADDRESS_SINGLE_LINE]', E'PHYSICIAN_ADDRESS', E'<html>The physician''s full address. <br>Formatted on a single line</html>', E'/**
 * [PHYS_FULL_ADDRESS_SINGLE_LINE]
 */

// use the same formating as in the print template
FORMATTER.formatPhysicianLine();');
SELECT letter.insert_letter_placeholder(NULL, NULL, E'Global functions, always charged', E'/**
 * GLOBAL FUNCTIONS
 */


/**
 * Takes the object and returns emtpy string, if it is null,
 * otherwise it creates a string out of it.
 */
function toString (o)
{
	return o == null ? "" : o.toString().trim();
}


/**
 * Tries to translate ''label'' into the user''s chosen language.
 * This is done by concatenating ''prefix'' to the ''label'' 
 * and sending it to the GECAMed translatlor. If it can be translated,
 * than the translation is returned, If not the ''label'' is returned.
 */
function tryTranslate (prefix, label)
{
	var translation;
	
	
	if (label == null)
		label = new java.lang.String("");
	if (prefix == null)
		prefix = "";
	
    // Concats the label onto the prefix string and tries to translate
    // them by calling FORMATTER.translate().
	translation = FORMATTER.translate(prefix+label);
	
    // If this translation returns null, the label is returned.
	if (translation == null)
		return label;
	else
		return translation;
}


/**
 * Calendar cal: The calendar to clear. DEFAULT: new GregorianCalendar()
 * return: The given calendar, with unchanged date, but
 * time set to 00:00:00.000
 */
function clearCalendar(cal)
{
	if (typeof cal == "undefined")
	{
		cal = new java.util.GregorianCalendar();
	}
	else if (cal instanceof java.util.Date)
	{
		var c = new java.util.GregorianCalendar();
		c.setTime(cal);
		cal = c;
	}
	
	cal.set(java.util.Calendar.HOUR_OF_DAY, 0);
	cal.set(java.util.Calendar.MINUTE, 0);
	cal.set(java.util.Calendar.SECOND, 0);
	cal.set(java.util.Calendar.MILLISECOND, 0);
	
	return cal;
}


/**
 * int id: the physician ID
 * return: the physician with the given ID
 */
function getPhysician (id)
{
	var iter = PHYSICIAN_LIST.iterator();
	var p;
	
	
	while (iter.hasNext())
	{
		p = iter.next();
		if (p.getId().equals(id))
			// physician found!
			return p;
	}
	
	// no physician found, with the specified ID
	return null;
}


/**
 * String[] a: 
 * String value: 
 * returns: Whether or not array a contains value v. The comparing is done case insensitive. 
 * 	Use the function arrayContains for a case sensitive search.
 */
function stringArrayContains (a, v)
{
	// checks
	if (typeof a == "undefined"
			|| a == null
			|| typeof v == "undefined"
			|| v == null)
		return false;
	
	for (var i = 0; i < a.length; i++) 
	{
		if (v.equalsIgnoreCase(a[i]))
			return true;
	}
	
	return false;
}


/**
 * Object[] a: 
 * Object value: 
 * returns: Whether or not a contains value (using equals method to check).
 */
function arrayContains (a, v)
{
	// checks
	if (typeof a == "undefined"
			|| a == null
			|| typeof v == "undefined"
			|| v == null)
		return false;
	
	for (var i = 0; i < a.length; i++)
	{
		if (v.equals(a[i]))
			return true;
	}
	
	return false;
}');


SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_ACCIDENT_NB]', E'PATIENT_HISTORY', E'Accident Number of the last Accident', E'
	var query = new java.lang.StringBuilder();
	
	// define the query
	query.append("SELECT OBJECT(i) ")
			.append("FROM Incident i ")
			.append(" WHERE i.patientId = ").append(PATIENT.getId())
			.append(" AND i.isAccident = true ");
	query.append(" ORDER BY i.incidentDate DESC LIMIT 1");

	var incident = DATABASE_MANAGER.getHqlQueryObject(query.toString());
	
	if (incident != null) {
		incident.getAccidentNr();
	} else {
		"";
	}
');

SELECT letter.insert_letter_placeholder(E'[PAT_HISTORY_ACCIDENT_DATE]', E'PATIENT_HISTORY', E'Accident Date of the last Accident', E'
	var query = new java.lang.StringBuilder();
	
	// define the query
	query.append("SELECT OBJECT(i) ")
			.append("FROM Incident i ")
			.append(" WHERE i.patientId = ").append(PATIENT.getId())
			.append(" AND i.isAccident = true ");
	query.append(" ORDER BY i.incidentDate DESC LIMIT 1");

	var incident = DATABASE_MANAGER.getHqlQueryObject(query.toString());
	
	if (incident != null) {
		FORMATTER.formatDate(incident.getAccidentDate());
	} else {
		"";
	}
');


SELECT letter.insert_letter_placeholder(E'[PAT_OTHER_PHYSICIANS]', E'PATIENT_HISTORY', E'Other Treating Physicians of the Patient - String Only.', E'
	toString(PATIENT.getOtherPhysicians());
');

SELECT letter.insert_letter_placeholder(E'[PAT_JOB]', E'PATIENT_INFO', E'Patients Job', E'
	PATIENT.getJob() == null? "" : toString(PATIENT.getJob());
');
