// JavaScript Document
//alert("loading linksession");
var login_failed_message;

function login_successful()
{
	//alert('login to billing ok ('+response.getXml()+')')
	//alert('login successful');
	login_failed_message = null;
}

function login_attempt_failed()
{
	//alert('login failed');
	login_failed_message = LinkSession.lastError();
	//if (LinkSession.lastError().indexOf('verify your login name') < 0)
	//{
	//	if (LinkSession.lastError().indexOf('does not exist in current Database') < 0)
	//	{
	//	    //alert('login fault due to other error');
	//	    //soap_failed();
	//	}
	//}
	//login_succeeded = false;
}

function login_to_database(username,password,database_name)
{
	// returns NULL if ok
	// message if not ok
	var n;
	
	//alert("logging into database "+database_name+" as "+username+" sbase="+sBase);
	// have several goes at logging in.
	// some errors appear temporily when licence changes are made.
	// try this as a fix.
	for (n = 0; n < 3; n++)
	{
		LinkSession.login(username, password, database_name);
		LinkSession.executeBatch(true,login_successful,login_attempt_failed);
		if (!login_failed_message)
			return null; // success
	}
	//alert("login to database response="+response.getXml());
	return login_failed_message;
}


function login_to_billing_database()
{
	return login_to_database(billing_database_username,
			        		 billing_database_password,
					         billing_database_name);
}

function logout_from_database()
{ 
	LinkSession.logout();
	LinkSession.executeBatch(true,null,soap_failed);
}

function linksession_init()
{
	//alert('login to billing database');
	login_to_billing_database();
	//alert('init soap session');
	if (!currentuserid)
	{  //if paramters are not already loaded
	    //alert('init soap session2');
		LinkSession.initSoapSession()//load server settings (SOAP function in eLinkSOAPProxy.js)
	}		
	//alert('init soap session done');
	//request.Clear()
}

function show_licences()
{
	alert('licences are ('+response.getXml()+')')
}

function soap_failed()
{
	alert('FAILED : '+LinkSession.lastError())
}

function consult_ok()
{
}

function simple_is_email_already_registered(email)
{
	var found = "";
	var id = LinkSession.Search(targets.tgCONT,"email1",email,false,false);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	r.first();
	while(!r.EOF())
	{
		var name = r.fieldByName('NAME');
		var found_email = r.fieldByName('EMAIL1');
		if (found_email == email)
		if (found.length == 0)
			found = name;
		else
		    found = found +" "+ name;
		r.next();
	}
	return found;
}

function quote(s)
{
	// this function ensures that any single quotes in s
	// are doubled up so that they can be quoted in sql
	return s.replace(/'/g,"''")
}

function is_email_already_registered_to_account_holder(email)
{
	// the proj.ftype strings in the database have trailing characters that
	// need to be ignored. Hence the substring() function call.
	var sql = "select cont.name from cont, cont_proj, proj where UPPER(email1)= UPPER('"+quote(email)+"')"
	      + " and cont_proj.idcontact = cont.idcontact"
			+ " and cont_proj.idproject = proj.idproject"
			+ " and substring(proj.ftype from 1 for 5) ='users'"
			+ " and proj."+field_pj_database_owner+"='yes'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	return (r.recordcount() > 0);
}

function is_organisation_name_registered(name,exclude_idcompany)
{
	//alert('ior');
	var sql = "select name, idcompany from cy where name='"+quote(name)+"'";
	
	// if exclude_idcompany is defined, it should be the id of the
	// company previously assigned to the company. We exclude it from the
	// search since it is quite possible for users to update the company
	// record without changing the name of the company
	if (exclude_idcompany)
	{
		sql += " and cy.idcompany <> '"+exclude_idcompany+"'";
	}
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return (r.recordcount() > 0);
}

function update_company(organisation_name,
						organisation_type,
						org_building,
						org_street,
						org_city,
						org_state,
						org_postcode,
						org_country,
						org_phone1,
						org_phone2,
						org_fax,
						org_web_address,
						idcompany)
{
	if (!idcompany)
		idcompany = 0;
	var edit_id = LinkSession.BeginEdit(targets.tgCY, idcompany); // create the new company record
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("NAME");
	field_values.push(organisation_name);
	field_names.push("TYPECY");
	field_values.push(organisation_type);
	field_names.push("BUILDING");
	field_values.push(org_building);
	field_names.push("STREET");
	field_values.push(org_street);
	field_names.push("CITY");
	field_values.push(org_city);
	field_names.push("STATE");
	field_values.push(org_state);
	field_names.push("POSTCODE");
	field_values.push(org_postcode);
	field_names.push("COUNTRY");
	field_values.push(org_country);
	field_names.push("PHONE1");
	field_values.push(org_phone1);
	field_names.push("PHONE2");
	field_values.push(org_phone2);
	field_names.push("FAX");
	field_values.push(org_fax);
	field_names.push("WEB1");
	field_values.push(org_web_address);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	return edit_key;
}

var create_company = update_company;

function link_company_to_company(cy1, cy2,relation, is_parent)
{
	var edit_id = LinkSession.BeginEditRelation(targets.tgCY, targets.tgCY, cy1, cy2); 
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("RELATION");
	field_values.push(relation);
	field_names.push("PARENT");
	field_values.push(is_parent);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function link_company_to_project(cy1, idproject, role)
{
	var edit_id = LinkSession.BeginEditRelation(
						targets.tgCY, targets.tgPROJ, cy1, idproject); 
	if (role)
	{
		var field_names = new Array;
		var field_values = new Array;
		field_names.push("CY_ROLE");
		field_values.push(role);
		LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	}
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function update_contact(user_title,
						user_first_name,
	                    user_last_name,
	                    user_email_address,
						idcontact)
{
	if (!idcontact)
		idcontact = 0;
	var edit_id = LinkSession.BeginEdit(targets.tgCONT, idcontact);
	var field_names = new Array;
	var field_values = new Array;
	if (user_title)
	{
		field_names.push("TITLE");
		field_values.push(user_title);
	}
	field_names.push("NAME");
	field_values.push(user_last_name);
	field_names.push("FIRSTNAME");
	field_values.push(user_first_name);
	field_names.push("EMAIL1");
	field_values.push(user_email_address);
	field_names.push("CY_ADDRES");
	field_values.push("1");
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	return edit_key;
}

var create_contact = update_contact;  // same function

function link_company_to_contact(cy, cont,rank, service, kind)
{
	var edit_id = LinkSession.BeginEditRelation(targets.tgCY, targets.tgCONT, cy, cont); 
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("RANK");
	field_values.push(rank);
	field_names.push("SERVICE");
	field_values.push(service);
	field_names.push("KIND");
	field_values.push(kind);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function get_project_reference(idproject)
{
	var sql = "select proj.reference from proj where"
	      + " idproject='"+idproject+"'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	while(!r.EOF())
	{
		var reference = r.fieldByName('REFERENCE');
		return reference;
	}
	return 0;
}

function new_user_data_class(idproject,usrcode)
{
	this.idproject = idproject;
	this.usrcode = usrcode;
}

function create_user_project(usercode_prefix,is_database_owner, password)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, 0);
	var field_names = new Array;
	var field_values = new Array;
	// the name of the project is the usercode
	field_names.push("NAME");
	field_values.push(usercode_prefix);
	field_names.push("FTYPE");
	field_values.push("users");
	field_names.push(field_pj_database_owner);
	field_values.push(is_database_owner);
	field_names.push(field_pj_password);
	field_values.push(password);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.SetReference(edit_id,users_project_idreference);
 	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);
	var edit_key = LinkSession.GetEditKey(edit_id);
	
	
	// we need to
	// ensure that the usercode is unique throughout elink lite
	// we do this by getting the project reference
	// and appending it to the usercode prefix
	
	// the reason for this uniqueness is to allow us to register
	// users using the firebird database authentication system
	// rather than the native elink authentication system
	// this approach allows us to manage passwords sensibly
	
	var full_usercode = usercode_prefix;
	var ref = get_project_reference(edit_key);
	
	edit_id = LinkSession.BeginEdit(targets.tgPROJ, edit_key);
	field_names = new Array;
	field_values = new Array;
	field_names.push('NAME');
	
	
	// eLink limits user codes to 15 characters.
	// the ref should be of the form US_000000
	// so we chop off the first 3 characters.
	// leaving 12 characters for the username.
	//alert('ref='+ref);
	ref = ref.substring(3,ref.length);  
	//alert('ref='+ref);
	if (ref.length >= 15)
	{
		// should never happen - but just in case!!!
		full_usercode = ref.substring(ref.length - 15, 15);  // the rightmost 15 characters
	}
	else
	{
		full_usercode = usercode_prefix.substring(0,15 - ref.length) + ref;
	}
	field_values.push(full_usercode);
	LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);
	var result = new new_user_data_class(edit_key, full_usercode);
	return result;

}

function update_user_project_password(user_idproject, password)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, user_idproject);
	var field_names = new Array;
	var field_values = new Array;

	field_names.push(field_pj_password);
	field_values.push(password);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
 	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);
}

function link_contact_to_project(idcont, idproj)
{
	var edit_id = LinkSession.BeginEditRelation(
			targets.tgCONT, targets.tgPROJ, idcont, idproj);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function find_current_server(return_address)
{
	// the current server is a project linked to a document
	// named 'current server'
	// the current server project should be of ftype 'servers'
	// there should only be one current server linked to the document
	var sql = "select proj.idproject, proj."+field_pj_server_address+" from doc, proj_doc, proj where"
	      + " doc.name='Current Server'"
	      + " and proj_doc.iddocument = doc.iddocument"
			+ " and proj_doc.idproject = proj.idproject"
			+ " and substring(proj.ftype from 1 for 7) ='servers'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		if (return_address)
		{
			return r.fieldByName(field_pj_server_address)
		}
		var idproject = r.fieldByName('IDPROJECT');
		return idproject;
	}
	return 0;
}


function create_database_project(database_name,status)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, 0);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("NAME");
	field_values.push(database_name);
	field_names.push("FTYPE");
	field_values.push("databases");
	field_names.push(field_pj_database_status);
	field_values.push(status);
	LinkSession.SetReference(edit_id,ref_pj_database_id);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	
	return edit_key;
}

function update_database_project_name(idproject, database_name)
{
	// Having created the basic entry
	// rename it to include the reference id to ensure the name is unique
	var reference = get_project_reference(idproject);
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, idproject);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("NAME");
	new_name = database_name+"_"+reference;
	field_values.push(new_name);
	LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
    return new_name;
}

function dereference_database_project_name(name)
{
	// this function takes a database project name and removes the reference characters (from the trailing _DB to the end)
	// this process allows the root database name to be attached to a new reference.
	i = name.lastIndexOf("_DB");  // find the trailing underscore
	name = name.substr(0,i);
	return name;
}

function touch_project(idproject)
{
	// Having created the basic entry
	// rename it to include the reference id to ensure the name is unique
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, idproject);
	//var field_names = new Array;
	//var field_values = new Array;
	//field_names.push("MEMO");
	//field_values.push("Touched");
	//LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}


function link_project_to_project(idproject1, idproject2)
{
	var edit_id = LinkSession.BeginEditRelation(
			targets.tgPROJ, targets.tgPROJ, idproject1, idproject2);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function get_id_of_user(userCode)
{
 	sql = "select iduser from users where usrcode='"+quote(userCode)+"'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
 	//alert("request="+request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	//alert("add user response="+response.getXml());
	var r= response.getNodeDataByID(id,false);

	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if (!r.EOF())
	{
		var iduser = r.fieldByName('IDUSER');
		//alert("Found iduser="+idusr);
		return iduser;
	}
	return -1;  // not found
}

function find_contact_from_email_address(email_address)
{
	request.Clear();
	//alert('consult');
	//var id = LinkSession.Search(targets.tgCONT,"email1",email_address,false,false);
	var sql = "select cont.*, proj.idproject"
			  +",proj.ftype"
			  +",proj.name as usrcode"
			  +",proj."+field_pj_password
			  +",proj."+field_pj_database_owner
	          +" from cont,proj, cont_proj where email1='"+quote(email_address)+"'"
			  +" and cont_proj.idproject = proj.idproject"
			  +" and cont_proj.idcontact = cont.idcontact"
			  +" and proj.ftype like '%users%'";
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(response.getXml());
	return r;	
}


function lookup_database(username_idproject)
{
	request.Clear();
	//alert('consult');
	//var id = LinkSession.Search(targets.tgCONT,"email1",email_address,false,false);
	
	var sql = "select proj.name, proj.idproject"
	          +" from proj,proj_proj where proj_proj.idproject2='"+username_idproject+"'"
			  +" and proj_proj.idproject = proj.idproject"
			  +" and proj.ftype like '%databases%'";
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	//alert(request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(response.getXml());
	return r;	
}

function is_database_active(database_idproject)
{
	request.Clear();
	//alert('consult');
	//var id = LinkSession.Search(targets.tgCONT,"email1",email_address,false,false);
	
	var sql = "select doc.name from proj_doc, doc"
	           +" where proj_doc.idproject = '"+database_idproject+"'"
	           +" and proj_doc.iddocument = doc.iddocument"
			   +" and doc.name = 'Inactive Databases'";
			   
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	//alert(request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(response.getXml());
	r.first();
	return (r.EOF());
}


function lookup_server(database_idproject)
{
	request.Clear();
	//alert('consult');
	//var id = LinkSession.Search(targets.tgCONT,"email1",email_address,false,false);
	var sql = "select proj.name, proj.idproject, proj."+field_pj_server_address+","
	          + field_pj_database_path_field+","
			  + field_pj_elink_connection_path_field+","
			  + field_pj_elink_bin_path_field+","
              + field_pj_database_server_public_address
			  +" from proj,proj_proj where proj_proj.idproject2='"+database_idproject+"'"
			  +" and proj_proj.idproject = proj.idproject"
			  //+" and proj.ftype = 'servers'";
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(response.getXml());
	return r;	
}

var login_failed_details;
var login_failed_retry_callback;

function end_login_failed()
{
	Modalbox.hide();
	setTimeout(end_login_failed2,2000);
}

function end_login_failed2()
{
	//alert('end_login_failed2() called callback='+login_failed_retry_callback);
	if (login_failed_retry_callback)
		login_failed_retry_callback();
}

function expand(s)
{
	//alert(s);
	s = s.replace(/</g,"&lt;");
	s = s.replace(/>/g,"&gt;");
	s = s.replace(/"/g,"&quot;");
	s = s.replace(/&/g,"&amp;");
	alert(s);
	return s;
}

function show_login_failed_details()
{
	//var s = expand(login_failed_details);
	var s = login_failed_details;
	//alert(s);
	Modalbox.show("<h3 style='text-align:center;'>Login Failed</h3><p style='text-align:center;'>Please check email address and password and try again</p><pre>"+s+"</pre><input type='button' value='Continue' onclick='end_login_failed();' />", 
				{title: "eLink Lite Login Failed", width: 700});
}

function show_login_failed_details2()
{
	Modalbox.hide();
	setTimeout(show_login_failed_details2,2000);
}

function user_login_failed(details, failed_retry_callback)	
{
	//alert('login failed');
	// there are certain details that we do not wish to divulge as they would increase the risk of being hacked
	// so as far as the user is concerned, they have simply failed to provide correct username and password
	reason = null;
	if (details != null)
	{
		if (details.indexOf('www.elinkuk.co.uk') > 0)
		{
			// this is a special case, the account has been deactivated, so we want to tell the customer that up front.
			reason = details;
			details = null;
		}
		else if (details.indexOf('verify your login name') > 0)
		{
			details = null;
		}
		else if (details.indexOf('does not exist in current Database') > 0)
		{
			details = null;
		}
		else if (details.indexOf(" Too many active users on module") > 0)
		{
			var d = details.split('.');
			details = d[0];  // just the first sentence is enough
		}
		// otherwise, leave the details as specified - I dont know what possibilities exist
	}

	//alert('user_login_failed() called callback='+failed_retry_callback);
	login_failed_retry_callback = failed_retry_callback;
	if (reason)
	{
		Modalbox.show("<h3 style='text-align:center;'>Login Failed</h3><p style='text-align:center;'>"+reason+"</p><input type='button' value='Continue' onclick='end_login_failed();' />",
				{title: "eLink Lite Login Failed", width: 300});
	}
	else if (details)
	{
		login_failed_details = details;
		Modalbox.show("<h3 style='text-align:center;'>Login Failed</h3><p style='text-align:center;'>Please check email address and password and try again</p><input type='button' value='Details' onclick='javascript:show_login_failed_details();' /><input type='button' value='Continue' onclick='end_login_failed();' />", 
				{title: "eLink Lite Login Failed", width: 300});
	}
	else
	{
		Modalbox.show("<h3 style='text-align:center;'>Login Failed</h3><p style='text-align:center;'>Please check email address and password and try again</p><input type='button' value='Continue' onclick='end_login_failed();' />",
				{title: "eLink Lite Login Failed", width: 300});
	}
 
}

function error(s)	
{
	Modalbox.alert("<h3>Error</h3><p>"+s+"</p>", 
			{title: "eLink Lite Internal Error", width: 300});

}

// -------------------------------------------------------------------------------
//                            Cookie Management
//--------------------------------------------------------------------------------

function createCookie(name,value,mins)
{
	//alert("createCookie "+name+"value="+value+"mins="+mins);
	if (mins)
	{
		var date = new Date();
		date.setTime(date.getTime()+(mins*60*1000));
		var expires = "; expires="+date.toGMTString();
	}
	else
		var expires = "";
	document.cookie = name+"="+value+expires+"; path=/";
}

function readCookie(name)
{
	//alert("readCookie "+name);
	var nameEQ = name + "=";
	var ca = document.cookie.split(';');
	for(var i=0;i < ca.length;i++)
	{
		var c = ca[i];
		while (c.charAt(0)==' ')
			c = c.substring(1,c.length);
		if (c.indexOf(nameEQ) == 0)
			return c.substring(nameEQ.length,c.length);
	}
	return null;
}

function eraseCookie(name)
{
	createCookie(name,"",-1);
}

// -------------------------------------------------------------------------------
//                            Navigate Extract
// -------------------------------------------------------------------------------
var isMac = navigator.userAgent.indexOf("Mac") != -1
var isFirefox = navigator.userAgent.indexOf("Firefox") != -1
var isIE = document.all
var isNS4 = document.layers
var Relocate

function LocationReplace(Window, URL) {
  if (isIE || isNS4 || isFirefox)
    Window.location.replace(URL)
  else // this avoids a bug in Netscape 6
    Window.location = URL
}

function ShowWindow(WindowName, URL, Refresh, Width, Height, NoStatus, NoResize, AllowScroll)
{
	var wnd
	if (top.name == WindowName)     
    	wnd = top
	else
	{
		var TopLeft = ""
		if (isIE && !isMac)
		{
			try
			{
				// ignore IE bug
				alert('1');
       	  		document.body.style.behavior = "url(#default#userdata)"
 				alert('2');
          		document.body.load(WindowName + "Positions")
 				alert('3');
    	  		if (!NoResize)
				{
 				alert('4');
            		if (n = document.body.getAttribute("Width"))
						Width = n
 				alert('5');
       	    		if (n = document.body.getAttribute("Height"))
						Height = n
    	  		}
 				alert('6');
       	  		if (n = document.body.getAttribute("Left"))
					TopLeft += ",left=" + n
 				alert('7');
       	  		if (n = document.body.getAttribute("Top"))
					TopLeft += ",top=" + n
      		}
	  		catch(ex)
			{
	    		// ignore error
	  		}
		}
			
		var Status = ",status=" + (NoStatus ? "no" : "yes") 
    	var Resize = ",resizable=" + (NoResize ? "no" : "yes")
		var Scroll = AllowScroll ? ",scrollbars=yes" : ""
    	wnd = window.open("", WindowName, "height=" + Height + ",width=" + Width + TopLeft + ",toolbar=no,menubar=no" + Status + Resize + Scroll)
  	}	
  	if (Refresh || wnd.length == 0)
	{
    	if (Relocate)
      		LocationReplace(wnd, URL)
		else
      		wnd.location = URL 
  	}    
  	wnd.focus()
  	return wnd 
}

// -------------------------------------------------------------------------------
//                            End of Navigate Extract
// -------------------------------------------------------------------------------

// global variables holding details of the database selected
// for management
var calling_server_address;
var calling_database;
var calling_username;
var calling_password;

var elink_lite_cookie_name = "eLinkLite";

function read_elink_lite_cookie()
{
	var value = readCookie(elink_lite_cookie_name);
	if (value)
	{
		//alert('secure value='+value);
		value = doUnsecure(value);
		if (value)
		{
			//alert('cookie value = '+value);
			var v = value.split(' ');
			//alert('cookie value = '+value+" argc="+v.length);
			if (v.length >= 8)
			{
				calling_server_address = v[1];
				calling_database = v[3];
				calling_username = v[5];
				calling_password = v[7];
				return true;
			}
		}
	}
	return false;
}

function add_form_value(form,name,value)
{
	var e = document.createElement('input');
	e.name = name;
	e.type = "hidden";
	e.value = value;
	form.appendChild(e);
}

/*
	this function has been superceded by local_login_to_user_database()
	which has to be present in each calling file because it submits a form

function login_to_user_database2(server_address,database,username,password, admin_mode)
{
	var url;

	calling_server_address = server_address;
	calling_database = database;
	calling_username = username;
	calling_password = password;
	//alert("login to user database");
	if (admin_mode)
	{
		var value = "server "+server_address+" database "+ database+ " username "+username+" password "+password;
		//alert("create cookie "+value);
		alert("Development system: remember to reduce the cookie lifetime to 1 min");
		createCookie(elink_lite_cookie_name,doSecure(value),24*60);  // valid for one day in development, 1 min live
		url = 'http://'+server_address+'/billing/lite/manage.php'
        //javascript:open_window('billing/lite/manage.php');
		//return;
		//url = 'http://'+server_address+"/econfig.dll/";
		//url = url + "login?page=MainFrameSet.htm&dbpath="+database
		//		  +"&login="+username
		//		  +"&password="+password
		//		  +"&rootdir=econfig";
	}
	else
	{
		url = 'http://'+server_address+"/elink.dll/";
		url = url + "login?page=Portal.htm&dbpath="+database
				  +"&login="+username
				  +"&password="+password
				  +"&rootdir=billing";
	}
	//alert('login to user_database url='+url);
    //show_user_elink_window('Login', url, false, 800, 600, true, false, false);
    LocationReplace(this, url)
}
*/
// login details preserved while showing MOTD
var motd_server_address,motd_database,motd_username,motd_password, motd_admin_mode;

function display_message_of_the_day3()
{
	local_login_to_user_database(motd_server_address,motd_database,motd_username,motd_password, motd_admin_mode);
}

function display_message_of_the_day2()
{
	Modalbox.hide();
	setTimeout(display_message_of_the_day3,2000);
}

function display_message_of_the_day(motd,server_address,database,username,password, admin_mode)
{
	motd_server_address = server_address;
	motd_database = database;
	motd_username = username;
	motd_password = password;
	motd_admin_mode = admin_mode;
	Modalbox.show(motd,	{title: "eLink Lite Message of the Day", width: 700});
}

function login_to_user_database(server_address,database,username,password, admin_mode)
{
	login_to_billing_database();
	var motd = get_message_of_the_day(database,username);
	logout_from_database();
	if (motd.length == 0)
		local_login_to_user_database(server_address,database,username,password, admin_mode);
	else
		display_message_of_the_day(motd, server_address,database,username,password, admin_mode);
}

var phase2_user_name_list;
var phase2_is_database_owner_list;
var phase2_server_address_list;
var defined_password;

var select_database_callback;

var select_database_admin_mode;

function chosen_database() 
{
	var dspe = $('database_select');
	var index = dspe.selectedIndex;
	var chosen_database_name = dspe.options[index].text;
	//alert('i='+index+ ' ' + chosen_database_name+' 	
	//		'+server_address_list[index],user_name_list[index],defined_password);
	select_database_callback(phase2_server_address_list[index],
						 chosen_database_name,
						 phase2_user_name_list[index],
						 defined_password,
						 select_database_admin_mode);
	Modalbox.hide();
	return true;
}


function select_database(email_address,password,contact,
						 admin_mode,callback,failed_callback, failed_retry_callback)
{
	// this function takes the list of contact data, which represents all the users and databases
	// that could respond to the email/password combination.
	// it then builds a list of the usercodes/servers/databases that could respond
	// then it moves to phase 2 and attempts to login to each of these databases in turn
	// removing from the list any that do NOT succeed.
	// If the list is now empty, the login is invalid - failed callback is called.
	// If there is only one that is valid, callback is called.
	// If there are mulitiple databases, a dialog is presented to the user
	// so they can choose which database to enter.
	
	// THIS METHOD RAISES DIALOG BOXES
	//
	// Currently, there are two dialogs that can be generated from this function
	// they are alternatives, so cannot clash.
	// if this function is called from another dialog, it is important that the
	// previous dialog is CLOSED before invoking this function
	
	select_database_callback = callback;
	select_database_admin_mode = admin_mode;
		
    defined_password = password;
	var phase1_database_list;
    var phase1_user_name_list;
    var phase1_is_database_owner_list;
    var phase1_server_address_list;
    var dsp = $('database_selection_panel');
    var dspe = $('database_select');
	dspe.options.length = 0;  // clear the list
	
	contact.first();
	phase1_user_name_list = new Array();
	phase1_database_list = new Array();
	phase1_database_idproject_list = new Array();
	phase1_is_database_owner_list = new Array();
	phase1_server_address_list = new Array();
	// scan the contact list, build a list of usercodes, databases, and servers.
	while(!contact.EOF())
	{
		var user_name = contact.fieldByName('USRCODE');
		var is_database_owner = contact.fieldByName(field_pj_database_owner);
		var contact_idproject = contact.fieldByName('idproject');
		// only the database owner is allowed access to econfig
		if ( (admin_mode && is_database_owner)
			  || !admin_mode
		   )
		{
			//alert('found contact '+user_name+' owner='+is_database_owner+' idproj='+contact_idproject);
			var database_result = lookup_database(contact_idproject);
			database_result.first();
			if (database_result.EOF())
			{
				logout_from_database();
				error("Failed to find database associated with user "
					   +user_name + " user_idproject="+contact_idproject);
				return;
			}
			else
			{
				var database_idproject = database_result.fieldByName('idproject');
				var database_name = database_result.fieldByName('name');
				var server_result = lookup_server(database_idproject);
				server_result.first();
				if (server_result.EOF())
				{
					logout_from_database();
					error("Failed to find server associated with database "
						  + database_name + " database_idproject="+database_idproject);
					return;
				}
				else
				{
					var server_address = server_result.fieldByName(field_pj_server_address);
					phase1_database_list.push(database_name);
					phase1_user_name_list.push(user_name);
					phase1_database_idproject_list.push(database_idproject);
					phase1_is_database_owner_list.push(is_database_owner);
					phase1_server_address_list.push(server_address);
				}
			}
		}
		contact.next();
	}
	// Attempt a login to each database.  Strike from the list any which fail
	// copy all others to the options list submitted to users.
	phase2_user_name_list = new Array();
	phase2_database_list = new Array();
	phase2_is_database_owner_list = new Array();
	phase2_server_address_list = new Array();
	
	var login_failed_details;
	for (n = 0; n < phase1_server_address_list.length; n++)
	{
		var database_name = phase1_database_list[n];
		// look to see if the database is active, i.e. it is not marked as inactive in the billing database.
		logout_from_database();
	    login_to_billing_database();
		if (!is_database_active(phase1_database_idproject_list[n]))
		{
			login_failed_details = "Database "+database_name+" is unavailable due to a long period of inactivity.<br />"
			                       + "To reactivate, please contact:<br /><br />"
			                       + "    eLink UK<br/>"
								   + "    www.elinkuk.co.uk<br /><br />"
								   + "    01273 358000";
		    logout_from_database();
		}
		else
		{
		    logout_from_database();
			login_failed_details = login_to_database(phase1_user_name_list[n],
									  password,
									  database_name);
		}
		if (!login_failed_details)
		{
			phase2_database_list.push(phase1_database_list[n]);
			phase2_is_database_owner_list.push(phase1_is_database_owner_list[n]);
			phase2_server_address_list.push(phase1_server_address_list[n]);
			phase2_user_name_list.push(phase1_user_name_list[n]);
			var opt = document.createElement('option');
			opt.text = database_name;
			try
			{
				dspe.add(opt,null);
			}
			catch(ex)
			{
				dspe.add(opt);
			}
		}
	}
	// log back into the billing database in case of subsequent login failiure.
	login_to_billing_database();
	
	if (phase2_server_address_list.length <= 0)
	{
		//alert('failed1 - frc='+failed_retry_callback);
		logout_from_database();
		failed_callback(login_failed_details, failed_retry_callback);
	}
	else if (phase2_server_address_list.length == 1)
	{
		// only one database in list
		//alert('select database callback='+select_database_callback);
		//alert('server='+phase2_server_address_list[0]);
		//alert('database='+phase2_database_list[0]);
		//alert('user='+phase2_user_name_list[0]);
		select_database_callback(
			phase2_server_address_list[0],
			phase2_database_list[0],
			phase2_user_name_list[0],
			defined_password,
			admin_mode);
	}
	else
	{
		//alert('13');
		logout_from_database();
		Modalbox.show("<div class='warning'><p>"+dsp.innerHTML
			+"</p> <input type='button' value='Select' onclick='chosen_database();' />"
			+"<input type='button' value='Cancel' onclick='Modalbox.hide()' /></div>",
			{title: "eLink Lite Login", width: 300});
	}
}

function signin_user(email, password, admin_mode,callback, failed_callback, failed_retry_callback)
{
    //alert('signin '+email+' '+password+' admin_mode='+admin_mode+' callback='+callback
	//	  +' failed_callback='+failed_callback
	//	  +' failed_retry_callback='+failed_retry_callback);
	//alert('failed_retry_callback='+failed_retry_callback);
	if (!failed_callback)
 		failed_callback = user_login_failed;
	
	
    var contact = find_contact_from_email_address(email);
	contact.first();
	if (contact.recordcount() == 0)
	{
		logout_from_database();  // to avoid security breach
		failed_callback(null, failed_retry_callback);
	}
	else
	{
		select_database(email,password,contact,admin_mode,callback, failed_callback, failed_retry_callback);
	} 
}


function individual_licence_class(idproduct_p, name_p, memo_p, licence_code_p,
								  reference_p, category_p,
								  price_p, billing_type_p, trial_period_p,
			   					  ftype_p,fieldname_p,fieldmask_p,rolename_p,
			    				  iconpath_p,iconwidth_p,iconheight_p,
								  max_licence_users_p, max_licences_p, user_manageable_trial_p,
								  lite_module_name_p)
{
	
	this.idproduct = idproduct_p;
	this.name = name_p;
	this.memo = memo_p;
	this.licence_code = licence_code_p;
	this.reference = reference_p;
	this.category = category_p;
	this.price = price_p;
	//alert('licence '+this.name+' price='+this.price);
	this.billing_type = billing_type_p;
	this.trial_period = trial_period_p;
	this.ftype = ftype_p;
	this.fieldname = fieldname_p;
	this.fieldmask = fieldmask_p;
	this.rolename = rolename_p;
	this.iconpath = iconpath_p;
	this.iconwidth = iconwidth_p;
	this.iconheight = iconheight_p;
	this.max_licence_users = max_licence_users_p;
	this.max_licences = max_licences_p;
	this.user_manageable_trial = user_manageable_trial_p;
	this.lite_module_name = lite_module_name_p;
}

individual_licence_class.prototype.get_name
	= function()
{
	return this.name;
}

individual_licence_class.prototype.show
    = function()
{
	return this.name + ' ' + this.category +'\n' + this.licence_code+ '\n'+ this.ftype
	       +'\n'+this.iconpath+' '+this.iconwidth+' '+this.iconheight;
}

function licence_billing_data_class()
{
	var full_user_licence;
	var free_user_licence;
	var concurrent_user_licence_list;
	var elink_lite_user_manager_licence;
	var elink_lite_free_user_manager_licence;
	var std_user_licence_list;
	var std_db_licence_list;
	var lite_user_licence_list;
	var lite_db_licence_list;
	
	this.full_user_licence = null;
	this.free_user_licence = null;
	this.concurrent_user_licence_list = new Array;
	this.elink_lite_free_user_manager_licence = null;
	this.elink_lite_user_manager_licence = null;
	this.std_user_licence_list = new Array;
	this.std_db_licence_list = new Array;
	this.lite_user_licence_list = new Array;
	this.lite_db_licence_list = new Array;
}

licence_billing_data_class.prototype.add_licence
    = function(idproduct_p, name_p, memo_p, licence_code_p, reference_p, category_p,
			   price_p, billing_type_p, trial_period_p,
			   ftype_p,fieldname_p,fieldmask_p,rolename_p,
			   iconpath_p,iconwidth_p,iconheight_p,
			   max_licence_users_p, max_licences_p, user_manageable_trial_p,
			   lite_module_name_p)
{
	var new_licence = new individual_licence_class(
								idproduct_p, name_p, memo_p, licence_code_p,
								reference_p,category_p,
								price_p, billing_type_p, trial_period_p,
								ftype_p,fieldname_p,fieldmask_p,rolename_p,
								iconpath_p,iconwidth_p,iconheight_p,
								max_licence_users_p, max_licences_p, user_manageable_trial_p,
								lite_module_name_p);
	//alert(new_licence.show());
	if (ftype_p.indexOf(ftype_pd_builtin_module) >= 0)
	{
		switch(category_p)
		{
case "Free User Licence":
			if (this.free_user_licence)
				alert("Duplicate Free User Licence");
			this.free_user_licence = new_licence;
			break;
case "Full User Licence":
			if (this.full_user_licence)
				alert("Duplicate Full User Licence");
			this.full_user_licence = new_licence;
			break;
case "Concurrent User Licence":
			 // there are multiple concurrent user licences
			this.concurrent_user_licence_list.push(new_licence);
			break;
case "Lite Free Usr Man Licence":
			if (this.elink_lite_free_user_manager_licence)
				alert("Duplicate Lite Free Usr Man Licence");
			this.elink_lite_free_user_manager_licence = new_licence;
			break;
case "Lite User Man Licence":
			if (this.elink_lite_user_manager_licence)
				alert("Duplicate Lite User Man Licence");
			this.elink_lite_user_manager_licence = new_licence;
			break;
default:
			alert("Unsupported builtin Licence Category: "+ new_licence.show());
			break;
		}
	}
	else if (ftype_p.indexOf(ftype_pd_std_user_module) >= 0)
	{
		this.std_user_licence_list.push(new_licence);
	}
	else if (ftype_p.indexOf(ftype_pd_std_db_module) >= 0)
	{
		this.std_db_licence_list.push(new_licence);
	}
	else if (ftype_p.indexOf(ftype_pd_lite_user_module) >= 0)
	{
		this.lite_user_licence_list.push(new_licence);
	}
	else if (ftype_p.indexOf(ftype_pd_lite_db_module) >= 0)
	{
		this.lite_db_licence_list.push(new_licence);
	}
	else
	{
		alert("Unsupported Licence ftype: "+ new_licence.show());
	}
}

licence_billing_data_class.prototype.find_concurrent_licence =
	function(name)
{
	for(var n = 0; n < this.concurrent_user_licence_list.length; n++)
	{
		if (this.concurrent_user_licence_list[n].name == name)
			return this.concurrent_user_licence_list[n];
	}
	return null;
}

licence_billing_data_class.prototype.validate =
function()
{
	var log = "";
	if (!this.full_user_licence)
		log += "Full User Licence Data Missing\n";
	if (!this.free_user_licence)
		log += "Free User Licence Data Missing\n";
	if (this.concurrent_user_licence_list.length == 0)
		log += "Concurrent User Licence Data Missing\n";
	if (!this.elink_lite_free_user_manager_licence)
		log += "Lite Free User Management Licence Data Missing\n";
	if (!this.elink_lite_user_manager_licence)
		log += "Lite User Management Licence Data Missing\n";
	return log;
}	

licence_billing_data_class.prototype.get_total_user_licences
	= function()
{
	// the first licence is the lite user manager licence.
	// the next set are standard user licences
	// the remainder are elink lite user licences.
	return this.std_user_licence_list.length
		+ this.lite_user_licence_list.length + 1;
}

licence_billing_data_class.prototype.is_elink_lite_user_module
	= function(n)
{
	// this function is called to determine if the nth licence in the total list of user licences
	// is an elink lite user licence.
	return n >= (this.std_user_licence_list.length + 1);
}

licence_billing_data_class.prototype.is_user_manager_licence
    = function(l)
{
	// return true if the specified licence is a user manager licence
	return l == this.elink_lite_user_manager_licence;
}

licence_billing_data_class.prototype.get_free_user_manager_licence
    = function()
{
	// return true if the specified licence is a user manager licence
	return this.elink_lite_free_user_manager_licence;
}


licence_billing_data_class.prototype.get_user_licence
	= function(index)
{
	if (index == 0)
		return this.elink_lite_user_manager_licence;
	index -= 1;
	if (index < this.std_user_licence_list.length)
		return this.std_user_licence_list[index];
	index -= this.std_user_licence_list.length;
	if (index < this.lite_user_licence_list.length)
		return this.lite_user_licence_list[index];
	return null;
}

licence_billing_data_class.prototype.get_total_lite_licences
	= function()
{
	return this.lite_db_licence_list.length
		+ this.lite_user_licence_list.length;
}

licence_billing_data_class.prototype.get_lite_licence
	= function(index)
{
	if (index < this.lite_user_licence_list.length)
		return this.lite_user_licence_list[index];
	index -= this.lite_user_licence_list.length;
	
	if (index < this.lite_db_licence_list.length)
		return this.lite_db_licence_list[index];
	return null;
}

licence_billing_data_class.prototype.index_of_user_licence
    = function(idproduct)
{
	if (idproduct == this.elink_lite_free_user_manager_licence.idproduct)
		return 0;
	if (idproduct == this.elink_lite_user_manager_licence.idproduct)
		return 0;
	for(var n = 0; n < this.std_user_licence_list.length; n++)
	{
		if (idproduct == this.std_user_licence_list[n].idproduct)
			return n+1;
	}
	for(var n = 0; n < this.lite_user_licence_list.length; n++)
	{
		if (idproduct == this.lite_user_licence_list[n].idproduct)
			return n+1+this.std_user_licence_list.length;
	}
	return -1;  // not found
}

licence_billing_data_class.prototype.find_licence_by_reference
    = function(reference)
{
	if (reference == this.elink_lite_free_user_manager_licence.reference)
		return this.elink_lite_free_user_manager_licence;
	if (reference == this.elink_lite_user_manager_licence.reference)
		return this.elink_lite_user_manager_licence;
	for(var n = 0; n < this.std_user_licence_list.length; n++)
	{
		if (reference == this.std_user_licence_list[n].reference)
			return this.std_user_licence_list[n];
	}
	for(var n = 0; n < this.lite_user_licence_list.length; n++)
	{
		if (reference == this.lite_user_licence_list[n].reference)
			return this.std_user_licence_list[n];
	}
	for(var n = 0; n < this.std_db_licence_list.length; n++)
	{
		if (reference == this.std_db_licence_list[n].reference)
			return this.std_db_licence_list[n];
	}
	for(var n = 0; n < this.lite_db_licence_list.length; n++)
	{
		if (reference == this.lite_db_licence_list[n].reference)
			return this.lite_db_licence_list[n];
	}
	return null;  // not found
}

licence_billing_data_class.prototype.find_licence_by_idproduct
    = function(idproduct)
{
	if (idproduct == this.full_user_licence.idproduct)
		return this.full_user_licence;
	if (idproduct == this.free_user_licence.idproduct)
		return this.free_user_licence;
	if (idproduct == this.elink_lite_free_user_manager_licence.idproduct)
		return this.elink_lite_free_user_manager_licence;
	if (idproduct == this.elink_lite_user_manager_licence.idproduct)
		return this.elink_lite_user_manager_licence;
	for(var n = 0; n < this.std_user_licence_list.length; n++)
	{
		if (idproduct == this.std_user_licence_list[n].idproduct)
			return this.std_user_licence_list[n];
	}
	for(var n = 0; n < this.lite_user_licence_list.length; n++)
	{
		if (idproduct == this.lite_user_licence_list[n].idproduct)
			return this.lite_user_licence_list[n];
	}
	for(var n = 0; n < this.std_db_licence_list.length; n++)
	{
		//alert('compare idproduct='+idproduct
		//	  +' with std db '+this.std_db_licence_list[n].idproduct);
		if (idproduct == this.std_db_licence_list[n].idproduct)
			return this.std_db_licence_list[n];
	}
	for(var n = 0; n < this.lite_db_licence_list.length; n++)
	{
		//alert('compare idproduct='+idproduct
		//	  +' with lite db '+this.lite_db_licence_list[n].idproduct);
		if (idproduct == this.lite_db_licence_list[n].idproduct)
			return this.lite_db_licence_list[n];
	}
	return null;  // not found
}


licence_billing_data_class.prototype.get_element_id
	= function(index)
{
	if (index == 0)
		return "lite_user_manager";
	index -= 1;
	if (index < this.std_user_licence_list.length)
		return "std_user_licence";
	index -= this.std_user_licence_list.length;
	if (index < this.lite_user_licence_list.length)
		return "lite_user_licence";
	return null;

}


function read_licence_billing_data(database_idproject)
{
	// this function reads all the current licence data from the billing database.
	// licences are identified by the product category.
	// current prices are linked to the 'Current Price List' project
	// OR products that are linked to the specific database project identified by database_idproject
	// And licences all have a reference code starting with LI
	
	// we order the list by the product name so that the first concurrent licence
	// will be the single user licence.
    var licence_data = new licence_billing_data_class();
	var sql = "select products.name, products.memo, "
	        + field_pd_licence_code+", products.category, "    
	        + " products.reference, products.price1, products.idproduct"
//			+ ", "+field_pd_billing_type
			+ ", "+field_pd_trial_period
			+ ", "+field_pd_fieldname
			+ ", "+field_pd_fieldmask
			+ ", "+field_user_manageable_trial
//			+ ", "+field_pd_rolename
			+ ", "+field_pd_iconpath
			+ ", "+field_pd_iconwidth
			+ ", "+field_pd_iconheight
			+ ", "+field_max_licence_users
			+ ", "+field_max_licences
			+ ", "+field_pd_usr_module_name
			+ ", "+field_pd_db_module_name
			+ ", products.ftype"
			+ " from products, proj, proj_prod"
            + " where products.reference like 'LI%'"
            + " and proj.name='Current Price List'"
            + " and (proj.idproject = proj_prod.idproject"
		    + "      or proj_prod.idproject = '"+database_idproject+"'"
			+ "     )"
            + " and proj_prod.idproduct = products.idproduct"
			+ " order by products.name"
			
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
 	//alert("request="+request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	//alert("add user response="+response.getXml());
	var r= response.getNodeDataByID(id,false);

	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	while (!r.EOF())
	{
		var idproduct = r.fieldByName('IDPRODUCT');
		var name = r.fieldByName('Name');
		var memo = decode64(r.fieldByName('Memo'));
		var licence_code = r.fieldByName(field_pd_licence_code);
		var category = r.fieldByName('Category');
		var reference = r.fieldByName('reference');
		var price = r.fieldByName('Price1');
		var ftype = decode64(r.fieldByName('ftype'));
		var fieldname = r.fieldByName(field_pd_fieldname);
		var fieldmask = r.fieldByName(field_pd_fieldmask);
		var user_manageable_trial = r.fieldByName(field_user_manageable_trial);
//		var rolename = r.fieldByName(field_pd_rolename);
		var rolename = "";  // obsolete
		var iconpath = r.fieldByName(field_pd_iconpath);
		var iconwidth = r.fieldByName(field_pd_iconwidth);
		var iconheight = r.fieldByName(field_pd_iconheight);
		var max_licence_users = r.fieldByName(field_max_licence_users);
		var max_licences = r.fieldByName(field_max_licences);
//		var billing_type = r.fieldByName(field_pd_billing_type);
		var billing_type = null;  // reserved
		var trial_period = r.fieldByName(field_pd_trial_period);
		var lite_module_name = '';
		if (ftype.indexOf(ftype_pd_lite_user_module) >= 0)
		{
			lite_module_name = r.fieldByName(field_pd_usr_module_name);
		}
		else if (ftype.indexOf(ftype_pd_lite_db_module) >= 0)
		{
			lite_module_name = r.fieldByName(field_pd_db_module_name);
		}
		
        licence_data.add_licence(idproduct,name, memo, licence_code, reference, category,
			        	price, billing_type, trial_period,
						ftype,fieldname,fieldmask,rolename,
						iconpath,iconwidth,iconheight,
						max_licence_users, max_licences, user_manageable_trial,
						lite_module_name);
		r.next();
	}
	licence_data.validate();
	return licence_data;  // not found
}

function user_licence_data_reading_class(licence_billing_data)
{
	// this class is responsible for reading the data from a user table
	// to determine the number of users and licences needed
	
	var n;
	
	this.total_full_users = 0;
	this.total_free_users = 0;
	this.total_consult_users = 0;
	this.total_system_administrators = 0;
	this.total_concurrent_users = 0;
	
	this.total_elink_lite_free_user_managers = 0;
	this.total_elink_lite_user_managers = 0;
	this.total_elink_lite_free_trial_user_managers = 0; // should always be zero, included for consistency
	this.total_elink_lite_trial_user_managers = 0;
	
	this.trial_start_count = 0;
	this.trial_end_count = 0;
	
	this.total_std_user_licence_list = new Array;
	this.total_std_db_licence_list = new Array;
	this.total_lite_user_licence_list = new Array;
	this.total_lite_db_licence_list = new Array;

	this.total_std_trial_user_licence_list = new Array;
	this.total_std_trial_db_licence_list = new Array;
	this.total_lite_trial_user_licence_list = new Array;
	this.total_lite_trial_db_licence_list = new Array;
	
	for(n = 0; n < licence_billing_data.std_user_licence_list.length; n++)
	{
		this.total_std_user_licence_list.push(0);
		this.total_std_trial_user_licence_list.push(0);
	}
	for(n = 0; n < licence_billing_data.std_db_licence_list.length; n++)
	{
		this.total_std_db_licence_list.push(0);
		this.total_std_trial_db_licence_list.push(0);
	}
	for(n = 0; n < licence_billing_data.lite_user_licence_list.length; n++)
	{
		this.total_lite_user_licence_list.push(0);
		this.total_lite_trial_user_licence_list.push(0);
	}
	for(n = 0; n < licence_billing_data.lite_db_licence_list.length; n++)
	{
		this.total_lite_db_licence_list.push(0);
		this.total_lite_trial_db_licence_list.push(0);
	}

	this.log = "";
}

user_licence_data_reading_class.prototype.add_database_licence
	= function(index,quantity)
{
	//alert('add_user_licence('+index+')');
	
	if (index < this.total_std_db_licence_list.length)
	{
		this.total_std_db_licence_list[index] += quantity;
	}
	else
	{
		index -= this.total_std_db_licence_list.length;
		if (index < this.total_lite_db_licence_list.length)
		{
			this.total_lite_db_licence_list[index] += quantity;
		}
		else
		{
			// unknown index
			alert('add_database_licence(index). Index out of range '+index
			  +' '+ this.total_std_db_licence_list.length
			  +' '+ this.total_lite_db_licence_list.length);
		}
	}
}

user_licence_data_reading_class.prototype.add_database_trial_licence
	= function(index, quantity)
{
	//alert('add_user_licence('+index+')');
	
	if (index < this.total_std_trial_db_licence_list.length)
	{
		this.total_std_trial_db_licence_list[index] += quantity;
	}
	else
	{
		index -= this.total_std_trial_db_licence_list.length;
		if (index < this.total_lite_trial_db_licence_list.length)
		{
			this.total_lite_trial_db_licence_list[index] += quantity;
		}
		else
		{
			// unknown index
			alert('add_database_trial_licence(index). Index out of range '+index
			  +' '+ this.total_std_trial_db_licence_list.length
			  +' '+ this.total_lite_trial_db_licence_list.length);
		}
	}
}


user_licence_data_reading_class.prototype.add_user_licence
	= function(index)
{
	//alert('add_user_licence('+index+')');
	
	if (index == 0)
	{
		if (this.total_elink_lite_free_user_managers == 0)
			this.total_elink_lite_free_user_managers = 1;
		else
			this.total_elink_lite_user_managers++;
	}
	else
	{
		index -= 1;
		if (index < this.total_std_user_licence_list.length)
		{
			this.total_std_user_licence_list[index]++;
		}
		else
		{
			index -= this.total_std_user_licence_list.length;
			if (index < this.total_lite_user_licence_list.length)
			{
				this.total_lite_user_licence_list[index]++;
			}
			else
			{
				// unknown index
				alert('add_user_licence(index). Index out of range '+index
		          +' '+ this.total_std_user_licence_list.length
				  +' '+ this.total_lite_user_licence_list.length);
			}
		}
	}
}

user_licence_data_reading_class.prototype.add_user_trial_licence
	= function(index)
{
	//alert('add_user_licence('+index+')');
	
	if (index == 0)
	{
		if (this.total_elink_lite_free_trial_user_managers == 0)
			this.total_elink_lite_free_trial_user_managers = 1;
		else
			this.total_elink_lite_user_trial_managers++;
	}
	else
	{
		index -= 1;
		if (index < this.total_std_trial_user_licence_list.length)
		{
			this.total_std_trial_user_licence_list[index]++;
		}
		else
		{
			index -= this.total_std_trial_user_licence_list.length;
			if (index < this.total_lite_trial_user_licence_list.length)
			{
				this.total_lite_trial_user_licence_list[index]++;
			}
			else
			{
				// unknown index
				alert('add_user_licence(index). Index out of range '+index
		          +' '+ this.total_std_trial_user_licence_list.length
				  +' '+ this.total_lite_trial_user_licence_list.length);
			}
		}
	}
}


user_licence_data_reading_class.prototype.get_report
    = function()
{
	return "Full users = " + this.total_full_users + "\n"
	    + "Free users = " + this.total_free_users + "\n"  
	    + "Consult users = " + this.total_consult_users + '\n'
	    + "concurrent users = " + this.total_concurrent_users + '\n'
	    + "System Administrators = "+ this.total_system_administrators + '\n'
	    + "eLink Lite free user managers = " + this.total_elink_lite_free_user_managers + '\n'
	    + "eLink Lite user managers = " + this.total_elink_lite_user_managers + '\n'
        + this.log; 
}

function get_database_account_holder_data(database_name)
{
	// this function executes a query that requests all the data
	// for the eLink Lite account holder of the database
	// Assuming we are logged into the billing database,
	// this query will obtain the list of contacts
	// associated with the database, who are marked as owner.
	// results are returned as a T_DATASET for subsequent interpretation
	var sql = "select idcontact, cont.email1, cont.title, cont.firstname, cont.name, proj.name as usrcode, "
	        + " dbproj.name as Database_Name,"
			+ " proj."+field_pj_password+" as user_password,"
			+ " proj.idproject as user_idproject"
			+ " from cont, cont_proj, proj, proj_proj, proj as dbproj where "
			+ " cont_proj.idcontact = cont.idcontact"
			+ " and cont_proj.idproject = proj.idproject"
			+ " and proj.ftype like 'users%'"
			+ " and proj_proj.idproject2 = proj.idproject"
			+ " and proj_proj.idproject = dbproj.idproject"
			+ " and proj."+field_pj_database_owner+" like 'yes'"
			+ " and dbproj.name = '"+database_name+"'";
			
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
 	//alert("request="+request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	
	var r= response.getNodeDataByID(id,false);

	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_company_data_from_contact(idcontact, contact_role)
{
	// this function executes a query that requests all the data
	// for the eLink Lite account holder identified by idcontact
	// Assuming we are logged into the billing database,
	// this query will obtain the list of companies
	// if contact_role == general, it should return the main company details
	// if the contact_role == financial, it should return the billing company details
	//  (there may not be a billing company).
	// results are returned as a T_DATASET for subsequent interpretation
	var sql = "select cy.*"
			+ " from cy_cont, cy where "
			+ " cy_cont.idcontact = '"+idcontact+"'"
			+ " and cy_cont.idcompany = cy.idcompany"
			+ " and cy_cont.service = '"+contact_role+"'"
			
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
 	//alert("request="+request.getSOAPRequest());
	LinkSession.executeBatch(true,null,soap_failed);
	
	var r= response.getNodeDataByID(id,false);

	//alert(r.recordcount()+" records"+response.getXml());
	return r;

}

function lookup_email_from_usercode(database,user_code)
{
	// this function looks up the email address registered with the specified
	// user code on the given database.
	// this information is to be found in the billing database.
	request.Clear();
	//alert('consult');
	//var id = LinkSession.Search(targets.tgCONT,"email1",email_address,false,false);
	var sql = "select dbproj.name, dbproj.idproject, userproj.name as usrcode, cont.email1"
		+ " from proj as dbproj, proj as userproj, proj_proj, cont_proj, cont"
		+ " where dbproj.ftype like 'databases%'"
		+ " and dbproj.name = '"+database+"'"
		+ " and dbproj.idproject = proj_proj.idproject"
		+ " and userproj.idproject = proj_proj.idproject2"
		+ " and userproj.ftype like 'users%'"
		+ " and userproj.name = '"+user_code+"'"
		+ " and cont_proj.idproject = userproj.idproject"
		+ " and cont_proj.idcontact = cont.idcontact"
	//alert(sql);
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(response.getXml());
	if (r.EOF())
		return null;  // unknown;
	return r.fieldByName('EMAIL1');
}

function create_purchase_order_document(name, status,
										total_ex_vat, vat_rate, total_inc_vat)
{
	var edit_id = LinkSession.BeginEdit(targets.tgDOC, 0);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("NAME");
	field_values.push(name);
	field_names.push("ftype");
	field_values.push(ftype_purchase_order);
	field_names.push(field_purchase_order_document_status);
	field_values.push(status);
	field_names.push(field_purchase_order_total_ex_vat);
	field_values.push(total_ex_vat);
	field_names.push(field_purchase_order_vat_rate);
	field_values.push(vat_rate);
	field_names.push(field_purchase_order_total_inc_vat);
	field_values.push(total_inc_vat);
	//field_names.push(field_pj_database_type);
	//field_values.push(database_type);
	LinkSession.SetReference(edit_id,purchase_order_document_idreference);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	
	return edit_key;
}

function create_project(name)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, 0);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("NAME");
	field_values.push(name);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	
	return edit_key;
}
function link_project_to_document(
	    idproject,iddocument,status)
{
	//alert('link project '+idproject+' to document'+iddocument);
	var edit_id = LinkSession.BeginEditRelation(targets.tgDOC, targets.tgPROJ,
												iddocument, idproject); 
	if (status)
	{
		var field_names = new Array;
		var field_values = new Array;
		field_names.push(field_doc_proj_user_status);
		field_values.push(status);
    	LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	}
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function link_product_to_document(idproduct,iddocument, user_idproject, quantity)
{
	var edit_id = LinkSession.BeginEditRelation(
			targets.tgDOC, targets.tgPROD, iddocument, idproduct); 
	if (user_idproject || quantity)
	{
		var field_names = new Array;
		var field_values = new Array;
		if (user_idproject)
		{
			field_names.push(field_prod_doc_user_idproject);
			field_values.push(user_idproject);
		}
		if (quantity)
 		{
			field_names.push("QTITY");
			field_values.push(quantity);
		}
	   	LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	}
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function lookup_project(name)
{
	var sql = "select proj.idproject from proj where"
	      + " proj.name='"+name+"'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		var idproject = r.fieldByName('IDPROJECT');
		return idproject;
	}
	return 0;
}

function get_project_name(idproject)
{
	var sql = "select name from proj where"
	      + " idproject='"+idproject+"'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName('NAME');
	}
	return null;
}


function purchase_order_details_class()
{
		var iddocument;
		var reference;
}

function get_latest_purchase_order(calling_database, status)
{
	var answer = new purchase_order_details_class;
	var sql = "select doc.iddocument, doc.reference from proj,  doc, proj_doc where"
	      + " proj.name='"+calling_database+"'"
	if (status)
	{
		sql += " and doc."+field_purchase_order_document_status+" = '"+status+"'"
	}
	sql +=  " and proj.ftype like 'databases%'"
		  + " and proj_doc.idproject = proj.idproject"
		  + " and proj_doc.iddocument = doc.iddocument"
		  + " and doc.reference like '"+purchase_order_prefix+"%'"
		  + " order by doc.date_creat desc"
	var id = LinkSession.ExecuteSQLQuery(sql,'',1); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		answer.iddocument = r.fieldByName('IDDOCUMENT');
		answer.reference = r.fieldByName('REFERENCE');
		return answer;
	}
	return null;
}

function purchase_order_value_class(total_ex_vat_p, vat_rate_p, total_inc_vat_p)
{
	this.total_ex_vat = total_ex_vat_p;
	this.vat_rate = vat_rate_p;
	this.total_inc_vat = total_inc_vat_p;
}

function get_value_of_latest_active_purchase_order(db)
{
	var answer = new purchase_order_details_class;
	var sql = "select doc.* from proj,  doc, proj_doc where"
	      + " proj.name='"+calling_database+"'"
	sql +=  " and doc."+field_purchase_order_document_status+" like '"
				+order_status_active+"%'"
	sql +=  " and proj.ftype like 'databases%'"
		  + " and proj_doc.idproject = proj.idproject"
		  + " and proj_doc.iddocument = doc.iddocument"
		  + " and doc.reference like '"+purchase_order_prefix+"%'"
		  + " order by doc.date_creat desc"
	var id = LinkSession.ExecuteSQLQuery(sql,'',1); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	if (r.EOF())
		return null;  // implies only free, basic system is active
	else
	{
		var total_ex_vat =  r.fieldByName(field_purchase_order_total_ex_vat);
		var vat_rate =  r.fieldByName(field_purchase_order_vat_rate);
		var total_inc_vat =  r.fieldByName(field_purchase_order_total_inc_vat);
		return new purchase_order_value_class(total_ex_vat, vat_rate, total_inc_vat);
	}
}

function database_user_list_class()
{
	this.email = null;
	this.first_name = null;
	this.last_name = null;
	this.status = null;
	this.usercode = null;
	this.is_owner = null;
	this.password = null;
	this.user_can_manage_own_trials = false;
}

database_user_list_class.prototype.show
	= function()
{
	return "email="+this.email+" "+this.first_name+" "+this.last_name+" status="+this.status+" usercode="+this.usercode+" is_owner="+this.is_owner;
}

function get_purchase_order_users(iddocument)
{
	var answer = new Array;
	
	var sql = "select cont.email1, cont.firstname, cont.name, proj.name as usrcode, proj."
	      + field_pj_database_owner+","
		  +field_doc_proj_user_status+","
		  +field_pj_password+","
		  + " "+field_pj_user_can_manage_own_trials
	      + " from proj,  doc, proj_doc, cont, cont_proj where"
	      + " doc.iddocument='"+iddocument+"'"
		  + " and proj.ftype like 'users%'"
		  + " and proj_doc.idproject = proj.idproject"
		  + " and proj_doc.iddocument = doc.iddocument"
		  + " and cont_proj.idproject = proj.idproject"
		  + " and cont_proj.idcontact = cont.idcontact"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	while(!r.EOF())
	{
		var a = new database_user_list_class;
		a.email = r.fieldByName('EMAIL1');
		a.first_name = r.fieldByName('firstname');
		a.last_name = r.fieldByName('name');
		a.password = r.fieldByName(field_pj_password);
		a.status = r.fieldByName(field_doc_proj_user_status);
		a.is_owner = r.fieldByName(field_pj_database_owner);
		if (r.fieldByName(field_pj_user_can_manage_own_trials) == "yes")
			a.user_can_manage_own_trials = true;
			
		a.usercode = r.fieldByName('USRCODE');
		answer.push(a);
		r.next();
	}
	return answer;
}

function get_purchase_order_user_manager_licences(purchase_order_iddocument, usercode)
{
	// query to get all user manager licences for all user
	var sql = "select proj.name as usrcode, products.*"
	      + " from proj,  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and products.ftype like '%"+ftype_pd_builtin_module+"%'"
		  + " and (products.category = 'Lite Free Usr Man Licence'"
		  + "     or products.category = 'Lite User Man Licence'"
		  +      ")"
		  + " and proj.name <> ''"
		  + " and proj.ftype like 'users%'"
		  + " and proj.idproject = "+field_prod_doc_user_idproject;
	if (usercode)
	{
		// we can add this code to limit the list to the specified user
		sql += " and proj.name = '"+usercode+"'";
	}
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_purchase_order_concurrent_licences(purchase_order_iddocument)
{
	var sql = "select proj.name as usrcode, products.*"
	      + " from proj,  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and products.ftype like '%"+ftype_pd_builtin_module+"%'"
		  + " and products.category = 'Concurrent User Licence'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function get_purchase_order_user_licences(purchase_order_iddocument)
{
	var sql = "select proj.name as usrcode, products.*"
	      + " from proj,  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and (products.ftype like '%"+ftype_pd_std_user_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_user_module+"%'"
		  +      ")"
		  + " and proj.name <> ''"
		  + " and proj.ftype like 'users%'"
		  + " and proj.idproject = "+field_prod_doc_user_idproject
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_all_current_database_licences(database_idproject)
{
	var sql = "select products.* "
	      + " from  products, proj_prod, proj where"
		  + " proj.name = 'Current Price List'"
          + " and (proj.idproject = proj_prod.idproject"
	      + "      or proj_prod.idproject = '"+database_idproject+"'"
		  + "     )"
		  + " and proj_prod.idproduct = products.idproduct"
		  + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		  +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_all_database_licences_from_purchase_order(iddocument)
{
	var sql = "select products.*, doc_prod.*"
	      + " from  doc, doc_prod, products where"
	      + " doc.iddocument='"+iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		  +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function get_product_from_purchase_order(iddocument,idproduct)
{
	var sql = "select products.*, doc_prod.*"
		  + " from  doc, doc_prod, products where"
		  + " doc.iddocument='"+iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and products.idproduct = '"+idproduct+"'"
		 // + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		 // + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		 // +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function get_purchase_order_database_licences(purchase_order_iddocument)
{
	var sql = "select products.*, doc_prod.*"
	      + " from  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		  +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function get_purchase_order_licences(purchase_order_iddocument)
{
	var sql = "select proj.name as usrcode, products.*"
	      + " from proj,  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and proj.name <> ''"
		  + " and proj.ftype like 'users%'"
		  + " and proj.idproject = "+field_prod_doc_user_idproject
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_purchase_order_full_user_licences(purchase_order_iddocument)
{
	var sql = "select products.*, doc_prod.*"
	      + " from  doc, doc_prod, products where"
	      + " doc.iddocument='"+purchase_order_iddocument+"'"
		  + " and doc_prod.iddocument = doc.iddocument"
		  + " and doc_prod.idproduct = products.idproduct"
		  + " and products.ftype like '%"+ftype_pd_builtin_module+"%'"
		  + " and products.category = 'Full User Licence'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function get_company_name(idcompany)
{
	var sql = "select name "
	      + " from cy where"
	      + " idcompany='"+idcompany+"'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	if (r.EOF())
		return "";  // unknown
	else
		return r.fieldByName('name');
}

function get_company_from_database_project(database_name)
{
	var sql = "select idcompany from proj,  cy, cy_proj where"
	      + " proj.name='"+database_name+"'"
		  + " and proj.ftype like 'databases%'"
		  + " and cy_proj.idproject = proj.idproject"
		  + " and cy_proj.idcompany = cy.idcompany"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName('IDCOMPANY');
	}
	return 0;
}

function get_idproject_for_database_project(database_name)
{
	var sql = "select idproject from proj where"
	      + " proj.name='"+database_name+"'"
		  + " and proj.ftype like 'databases%'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName('IDPROJECT');
	}
	return 0;
}

function get_payment_day_for_database_project(database_name)
{
	var sql = "select "+field_pj_order_day+" from proj where"
	      + " proj.name='"+database_name+"'"
		  + " and proj.ftype like 'databases%'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName(field_pj_order_day);
	}
	return 0;
}

function set_payment_day_for_database_project(database_name,day)
{
	var database_idproject = get_idproject_for_database_project(database_name);
	if (database_idproject)
	{
		var edit_id = LinkSession.BeginEdit(targets.tgPROJ, database_idproject);
		var field_names = new Array;
		var field_values = new Array;
		field_names.push(field_pj_order_day);
		field_values.push(day);
		
		LinkSession.UpdateFields(edit_id,0, field_names, field_values);
		LinkSession.CommitChanges(edit_id);
		LinkSession.EndEdit(edit_id);
		LinkSession.executeBatch(true,null,soap_failed);	
	}
}

function get_user_project(calling_database,usrcode)
{
	var sql = "select userproj.idproject"
	      + " from proj as userproj, proj_proj, proj as dbproj where"
	      + " dbproj.name='"+calling_database+"'"
		  + " and dbproj.ftype like 'databases%'"
		  + " and proj_proj.idproject = dbproj.idproject"
		  + " and proj_proj.idproject2 = userproj.idproject"
		  + " and userproj.name = '"+usrcode+"'"
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName('IDPROJECT');
	}
	return 0;
}

function is_user_allowed_to_manage_own_trials(calling_database, usrcode)
{
	var sql = "select userproj."+field_pj_user_can_manage_own_trials
	      + " from proj as userproj, proj_proj, proj as dbproj where"
	      + " dbproj.name='"+calling_database+"'"
		  + " and dbproj.ftype like 'databases%'"
		  + " and proj_proj.idproject = dbproj.idproject"
		  + " and proj_proj.idproject2 = userproj.idproject"
		  + " and userproj.name = '"+usrcode+"'"
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return (r.fieldByName(field_pj_user_can_manage_own_trials) == "yes");
	}
	return 0;

}

function get_database_management_lock(calling_database)
{
	var sql = "select "+field_pj_locked_by
	      + " from proj as dbproj where"
	      + " dbproj.name='"+calling_database+"'"
		  + " and dbproj.ftype like 'databases%'"
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName(field_pj_locked_by);
	}
	return 0;

}

function get_users_with_trial_management_in_progress(calling_database, usrcode)
{
	var sql = "select cont.*, userproj.idproject"
	      + " from proj as userproj, proj_proj, proj as dbproj, cont, cont_proj where"
	      + " dbproj.name='"+calling_database+"'"
		  + " and dbproj.ftype like 'databases%'"
		  + " and proj_proj.idproject = dbproj.idproject"
		  + " and proj_proj.idproject2 = userproj.idproject"
		  + " and cont_proj.idproject = userproj.idproject"
		  + " and cont_proj.idcontact = cont.idcontact"
		  + " and userproj.ftype like 'users%'"
		  + " and userproj."+field_pj_management_lock+" <> ''";
	if (usrcode)
	{
		// restrict the results to the specified user
		sql += " and userproj.name = '"+usrcode+"'";
	}
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function lock_database_for_management(database_idproject,usrcode)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, database_idproject);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push(field_pj_locked_by);
	field_values.push(usrcode);
	
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function unlock_database_for_management(database_idproject)
{
	lock_database_for_management(database_idproject,"");
}

function apply_lock_user_for_trial_management(user_idproject, lock_value)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, user_idproject);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push(field_pj_management_lock);
	field_values.push(lock_value);
	
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function unlock_user_for_trial_management(user_idproject)
{
	apply_lock_user_for_trial_management(user_idproject, "")
}

function lock_user_for_trial_management(user_idproject)
{
	apply_lock_user_for_trial_management(user_idproject, "locked")
}

function release_all_trial_management_locks(calling_database)
{
	var ul = get_users_with_trial_management_in_progress(calling_database);
	while(!ul.EOF())
	{
		var user_idproject = ul.fieldByName('IDPROJECT');
		unlock_user_for_trial_management(user_idproject);
		ul.next();
	}
}


function get_contact_from_user_project(user_idproject)
{
	var sql = "select idcontact"
	      + " from proj, cont_proj where"
		  + " proj.idproject = '"+user_idproject+"'"
		  + " and cont_proj.idproject = proj.idproject"
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',1); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName('IDCONTACT');
	}
	return 0;
}

function find_updated_licence(idproduct)
{
	// the specified idproduct is no longer in the current price list
	// it has been updated.  Scan down the list of child products for this
	// product to find the current one
	// this is a repeated search as the specified product could be
	// several generations out of date
	while(true)
	{
		var sql = "select idproduct2"
			  + " from prod_prod where"
			  + " idproduct='"+idproduct+"'"
			  
		var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
		LinkSession.executeBatch(true,null,soap_failed);
		var r= response.getNodeDataByID(id,false);
		//alert(r.recordcount()+" records"+response.getXml());
		r.first();
		if(!r.EOF())
		{
			idproduct = r.fieldByName('IDPRODUCT2');
		}
		else
		{
			// there is no child to the given idproduct, so this must be
			// the latest equivalent
			// we assume the very latest one is in the current price list!!
			return idproduct;
		}
	}
}

function get_product(idproduct)
{
	var sql = "select products.*"
	      + " from  products where"
		  + " products.idproduct = '"+idproduct+"'"
		 // + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		 // + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		 // +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_country_vat_list()
{
	var sql = "select *"
	      + " from  "+vat_rate_table
		 // + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		 // + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		 // +      ")"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); 
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_vat_rate_for_database(database_name)
{
	var sql = "select "+country_vat_rate
	      + " from proj, cy, cy_proj, "+vat_rate_table+" where"
	      + " proj.name='"+calling_database+"'"
		  + " and proj.ftype like 'databases%'"
		  + " and cy_proj.idproject = proj.idproject"
		  + " and cy_proj.idcompany = cy.idcompany"
		  + " and cy_proj.cy_role like '%Billing%'"  // consider the billing company
		  + " and cy.country = "+vat_rate_table+"."+country_vat_country
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return r.fieldByName(country_vat_rate);
	}
	return 0;
	
}

function country_data_class(country_p,code)
{
	this.country = country_p;
	this.iso3166_country_code = code;
}

function get_country_data_for_database(database_name)
{
	var sql = "select "+country_vat_country+", "+iso3166_country_code
	      + " from proj, cy, cy_proj, "+vat_rate_table+" where"
	      + " proj.name='"+calling_database+"'"
		  + " and proj.ftype like 'databases%'"
		  + " and cy_proj.idproject = proj.idproject"
		  + " and cy_proj.idcompany = cy.idcompany"
		  + " and cy_proj.cy_role like '%Billing%'"  // consider the billing company
		  + " and cy.country = "+vat_rate_table+"."+country_vat_country
		  
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		return new country_data_class(r.fieldByName(country_vat_country), r.fieldByName(iso3166_country_code));
	}
	return null;  // unknown
	
}


function is_user_an_elink_lite_manager(calling_database, usercode)
{
	var po_data = get_latest_purchase_order(calling_database, order_status_active);
	if (!po_data)
	{
		// we have no information, so allow the user to manage elink lite
		return true;
	}
	
	var licences = get_purchase_order_user_manager_licences(po_data.iddocument,usercode);
	return !licences.EOF();
	
}

function pad_digits(n, totalDigits) 
{ 
	n = n.toString(); 
	var pd = ''; 
	if (totalDigits > n.length) 
	{ 
		for (i=0; i < (totalDigits-n.length); i++) 
		{ 
			pd += '0'; 
		} 
	} 
	return pd + n.toString(); 
} 


function create_action(subject, action_mode, action_nature, duration)
{
	var edit_id = LinkSession.BeginEdit(targets.tgACT, 0); // create the new record
	var field_names = new Array;
	var field_values = new Array;
	field_names.push("SUBJECT");
	field_values.push(subject);
	field_names.push("KIND");
	field_values.push("0");
	//var date_string = start_date.getFullYear()+'-'+pad_digits(start_date.getMonth()+1,2)+"-"+pad_digits(start_date.getDate(),2);
	//date_string += " "+pad_digits(start_date.getHours(),2)+":"+pad_digits(start_date.getMinutes(),2)+":"+pad_digits(start_date.getSeconds(),2);
	//alert('date='+date_string);
	//field_names.push("DATEBEGIN");
	//field_values.push(date_string);
	field_names.push("MODE");
	field_values.push(action_mode);
	field_names.push("NATURE");
	field_values.push(action_nature);
	field_names.push("DURATION");
	field_values.push(duration);
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
	var edit_key = LinkSession.GetEditKey(edit_id);
	return edit_key;
}

function link_action_to_product(idaction,idproduct)
{
	var edit_id = LinkSession.BeginEditRelation(
			targets.tgACT, targets.tgPROD, idaction, idproduct);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function link_action_to_project(idaction,idproject)
{
	var edit_id = LinkSession.BeginEditRelation(
			targets.tgACT, targets.tgPROJ, idaction, idproject);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function get_trial_list(mode,database,usrcode)
{
	// this function returns the list of licence trial actions for the specified
	// database, where those actions have the specified mode.
	// usrcode may be null for all users
	// or restricting results to one user
	var sql = "select user_proj.name as usrcode, products.*, act.*"
	      + " from proj as db_proj, proj as user_proj, proj_proj, act, products, proj_act, prod_act where"
	      + " db_proj.name='"+calling_database+"'"
		  + " and db_proj.ftype like 'databases%'"
		  + " and proj_proj.idproject = db_proj.idproject"
		  + " and proj_proj.idproject2 = user_proj.idproject"
		  + " and (products.ftype like '%"+ftype_pd_std_user_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_user_module+"%'"
		  +      ")"
		  + " and user_proj.idproject = proj_act.idproject"
		  + " and proj_act.idaction = act.idaction"
		  + " and act.mode = '"+mode+"'"
		  + " and prod_act.idaction = act.idaction"
		  + " and prod_act.idproduct = products.idproduct"
		  ;
	if (usrcode)
		sql += " and user_proj.name = '"+usrcode+"'";
		
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}

function get_database_module_trial_data(mode,database,idproduct)
{
	// this function returns the list of licence trial actions for the specified
	// database, where those actions have the specified mode.
	// usrcode may be null for all users
	// or restricting results to one user
	var sql = "select products.*, act.*"
	      + " from proj as db_proj, act, products, proj_act, prod_act where"
	      + " db_proj.name='"+calling_database+"'"
		  + " and db_proj.ftype like 'databases%'"
		  + " and db_proj.idproject = proj_act.idproject"
		  + " and act.idaction = proj_act.idaction"
		  + " and (products.ftype like '%"+ftype_pd_std_db_module+"%'"
		  + "     or products.ftype like '%"+ftype_pd_lite_db_module+"%'"
		  +      ")"
		  + " and act.mode = '"+mode+"'"
		  + " and prod_act.idaction = act.idaction"
		  + " and prod_act.idproduct = products.idproduct"
		  + " and products.idproduct = '"+idproduct+"'"
		  ;
		
	var id = LinkSession.ExecuteSQLQuery(sql,'',0); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r;
}


function is_user_in_users_table(usercode)
{
	var sql = "select usrcode from users where usrcode='"+usercode+"'";
	
	var id = LinkSession.ExecuteSQLQuery(sql,'',1); // only get the first result
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	return r.recordcount() > 0;
	
}

function get_user_idusr(usrcode)
{
	var sql = "select iduser from users where usrcode='"+usrcode+"'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	if (!r.EOF())
	{
		return r.fieldByName('IDUSER');
	}
	return 0;
}

function set_user_trial_management_permission(user_idproject,permitted)
{
	//alert('sutmp '+user_idproject+' '+permitted);
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, user_idproject);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push(field_pj_user_can_manage_own_trials);
	if (permitted)
		field_values.push("yes");
	else
		field_values.push("no");
	
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function do_update_user_project(user_idproject,password)
{
	var edit_id = LinkSession.BeginEdit(targets.tgPROJ, user_idproject);
	var field_names = new Array;
	var field_values = new Array;
	field_names.push(field_pj_password);
	field_values.push(password);
	
    LinkSession.UpdateFields(edit_id,0, field_names, field_values);
	LinkSession.CommitChanges(edit_id);
	LinkSession.EndEdit(edit_id);
	LinkSession.executeBatch(true,null,soap_failed);	
}

function refresh_elink_user_data(database)
{
	// this function is designed to cause the eLink user data cache to synchronize with
	// the database.
	// It should be called after modifying the users table directly (in PHP)
	// if this is not done, new user logins are not recognised.
	
	// Method
	// the SOAP api allows us to rename users.
	// so we choose the rename the manager, and then restore his original name
	// This seems to have the desired effect.
	logout_from_database();
	var msg = login_to_billing_database();
	if (msg)
		alert(msg);
	var owner_data = get_database_account_holder_data(database);
	if (!owner_data.EOF())
	{
		var owner_usercode = owner_data.fieldByName('USRCODE');
		if (owner_usercode > 0)
		{
			logout_from_database();
			msg = login_to_database(billing_database_username,billing_database_password,database);
			if (msg)
				alert(msg);
			var iduser = get_user_idusr(owner_usercode);
			//alert('manager='+iduser);
			logout_from_database();
			switch_to_econfig();
			login_to_database(billing_database_username,billing_database_password,database);
			refresh_elink_user_database(iduser,owner_usercode);
			//econfig_add_user_account(0, firstname+" "+lastname, "dummy", 0, 0, 1, "", "");
			logout_from_database();
			end_econfig_mode();
		}
	}
}

function get_all_databases()
{	
	// it is not sufficient to location all projects whose ftype is databases.
	// it also needs to be linked to a parent project whose ftype is servers
	// because old database projects may be linked to 'obsolete'
	
	var sql = "select dbproj.* from proj as dbproj, proj_proj, proj as sproj"
	           +" where dbproj.ftype like '%databases%'"
	           +" and proj_proj.idproject2 = dbproj.idproject"
	           +" and proj_proj.idproject = sproj.idproject"
	           +" and sproj.ftype like '%servers%'"
			   +" order by date_creat desc";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	return r;
}

function get_server_message_of_the_day(database)
{
	// this function is called as part of get_message_of_the_day()
	// it should not be invoked directly.
	// It is responsible for getting any server-wide MOTDs
	motd = '';
	var sql = "select motd_proj.memo from proj as db_proj, proj as server_proj, proj as motd_proj,"
	         +"proj_proj as server_db_proj_proj, proj_proj as server_motd_proj_proj"
	         +" where db_proj.name ='"+database+"'"
			 +" and db_proj.ftype like '%databases%'"  // identify the db project
			 +" and server_db_proj_proj.idproject = server_proj.idproject"
			 +" and server_db_proj_proj.idproject2 = db_proj.idproject"   // find the server project above it
			 +" and server_motd_proj_proj.idproject = server_proj.idproject"
			 +" and server_motd_proj_proj.idproject2 = motd_proj.idproject"  // find the motd below the server project
			 +" and motd_proj.name = '"+motd_project_name+"'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	r.first();
	while(!r.EOF())
	{
		if (motd.length == 0)
			motd += "<h3>Message of the Day</h3>";
		motd += '<p>';
		var memo = decode64(r.fieldByName('Memo'));
		motd += memo;
		motd += '</p>';
		r.next();
	}
	return motd;
}

function get_message_of_the_day(database,usrcode)
{
	// there may be a number of MOTD logged against the database
	// and even more logged against the user.
	// The database-wide ones should be shown first.
	// returns an empty string if there are no MOTDs
	motd = get_server_message_of_the_day(database);
	var sql = "select motd_proj.memo from proj as db_proj, proj as motd_proj, proj_proj"
	         +" where db_proj.name ='"+database+"'"
			 +" and db_proj.ftype like '%databases%'"
			 +" and proj_proj.idproject = db_proj.idproject"
			 +" and proj_proj.idproject2 = motd_proj.idproject"
			 +" and motd_proj.name = '"+motd_project_name+"'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	r.first();
	while(!r.EOF())
	{
		if (motd.length == 0)
			motd += "<h3>Message of the Day</h3>";
		motd += '<p>';
		var memo = decode64(r.fieldByName('Memo'));
		motd += memo;
		motd += '</p>';
		r.next();
	}
	if (motd.length > 0)
	{
		motd += '<input type="button" value="Ok" onclick="javascript:display_message_of_the_day2();" />';
	}
	return motd;
}

function remove_motd(database)
{
	var sql = "select motd_proj.idproject from proj as db_proj, proj as motd_proj, proj_proj"
		 +" where db_proj.name ='"+database+"'"
		 +" and db_proj.ftype like '%databases%'"
		 +" and proj_proj.idproject = db_proj.idproject"
		 +" and proj_proj.idproject2 = motd_proj.idproject"
		 +" and motd_proj.name = '"+motd_project_name+"'";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	r.first();
	while(!r.EOF())
	{
		var idproject = r.fieldByName('IDPROJECT');
		LinkSession.Delete(targets.tgPROJ,idproject);
		r.next();
	}
}

function get_email_signature()
{
	// this returns the e-advantage email signature that
	// is appended to all emails issued through elink Lite
	var sql = "select proj.memo from proj where"
	      + " proj.name='"+email_signature_project_name+"'"
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	r.first();
	if(!r.EOF())
	{
		var memo = decode64(r.fieldByName('Memo'));
		return memo;
	}
	return "";

}

function setup_organisation_type(e)
{
	var sql= "select * from cykind_lk";
	var id = LinkSession.ExecuteSQLQuery(sql,'',0);
	LinkSession.executeBatch(true,null,soap_failed);
	var r= response.getNodeDataByID(id,false);
	//alert(r.recordcount()+" records"+response.getXml());
	// The first entry is blank
	var opt = document.createElement('option');
	opt.text = "";
	try
	{
		e.add(opt,null);
	}
	catch(ex)
	{
		e.add(opt);
	}
	
	r.first();
	while(!r.EOF())
	{
		var value = r.fieldByName('CYKINDDESC');
		opt = document.createElement('option');
		opt.text = value;
		try
		{
			e.add(opt,null);
		}
		catch(ex)
		{
			e.add(opt);
		}
		r.next();
	}
	
}

//alert("loaded linksession");