Getting data from DB to form items

Consider providing a Combo Box for the list of Debit Account No and Beneficiary Account fetched dynamically from database. This can be done using the data-support class.

In the Payment Form shown in the previous screenshot, perform the following steps:

Delete the Textbox "TXT_DBIT_AC_NO".
Insert a Combo and put the Item ID as "TXT_DBIT_AC_NO".
Create a data-support class implementing the Canvas IAdditionalData class and retrieve the data using a SQLMap.
Map the data support class path with Form.

Now, create a data-support class that implements the following Canvas class:

import com.intellectdesign.canvas.formdefinition.addinfo.IAdditionalDataSupport

Assuming the data-support class name is PaymentDataSupport.java,

package com.intellectdesign.modelhouse.dataSupport; 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; 
import com.intellectdesign.canvas.database.DatabaseConstants;
import com.intellectdesign.canvas.database.DatabaseException;
import com.intellectdesign.canvas.database.DatabaseRequest;
import com.intellectdesign.canvas.database.DatabaseResult;
import com.intellectdesign.canvas.formdefinition.FormDefinitionException;
import com.intellectdesign.canvas.formdefinition.FormItemDefinition;
import com.intellectdesign.canvas.formdefinition.addinfo.AdditionalDataCodeValue;
import com.intellectdesign.canvas.formdefinition.addinfo.IAdditionalDataSupport;
import com.intellectdesign.canvas.value.IUserValue; 

public class PaymentDataSupport implements IAdditionalDataSupport
{ 
	@Override
	public ArrayList<AdditionalDataCodeValue> getAdditionalDataFor(FormItemDefinition itemDefn, IUserValue userValue, HashMap inputParams) throws FormDefinitionException
	{
		ArrayList<AdditionalDataCodeValue> resultList = null;
		DatabaseRequest dbRequest = null;
		DatabaseResult dbResult = null;
		List tmpList = null;
		String key = null;
		
		try
		{
			//Create a DB Request to fetch the Account No from DB to the combo 
			dbRequest = new DatabaseRequest();
			dbResult = new DatabaseResult();
			tmpList = new ArrayList();
			resultList = new ArrayList<AdditionalDataCodeValue>();
			dbRequest.setDataSource(DatabaseConstants.DEFAULT_DATASOURCE); 

			//Getting the data for the Debit Account No combo
			if (itemDefn.getItemId().equals("ACC_NO"))
			{
				dbRequest.setOperation(DatabaseConstants.SELECT); 
				// Actual result map key, which contains the SQL to be executed
				dbRequest.setDataAccessMapKey("FUNDTRANS");
				dbRequest.setOperationExtension("ACC_NO");
				dbResult = dbRequest.execute(); 
				key = "ACCOUNT_NO"; 
				tmpList = dbResult.getReturnedList(); 
			} else if (itemDefn.getItemId().equals("BENE_ACC_NO"))
			{
				dbRequest.setOperation(DatabaseConstants.SELECT);
				dbRequest.setDataAccessMapKey("FUNDTRANS");
				dbRequest.setOperationExtension("BENE_NO");
				dbResult = dbRequest.execute();
				key = "BENE_ACC_NO";
				tmpList = dbResult.getReturnedList();
			}
			for (int i = 0; i < tmpList.size(); i++)
			{
				Map tmpMap = (HashMap) tmpList.get;
				resultList.add(new AdditionalDataCodeValue((String) tmpMap.get(key), (String) tmpMap.get(key)));
			}
		} catch (DatabaseException dbExcep)
		{
			throw new FormDefinitionException(dbExcep);
		}
		return resultList;
	}
}

Now, create a Select SQL result map in SQLMap file to get the data from the database for the access map key.

Assuming you want to add the SQL in the same PaymentSQLMap.xml file, add the SQL result map (FUNDTRANS) as follows:

<! Debit Account Number selection SQL Extension value as SELECT_ACC_NO----------------------------> 
	<selectid="FUNDTRANS_SELECT_ACC_NO"resultMap="FUNDTRANS_SELECT_ACC_NO_RESULT_MAP">
		select ACCOUNT_NO from ACCOUNT_SUMMARY_DATA
	</select> 
<! Debit Account Number selection SQL Extension value as SELECT_ACC_NO---> 
	<selectid="FUNDTRANS_SELECT_BENE_NO"resultMap="FUNDTRANS_SELECT_BENE_NO_RESULT_MAP">
		select BENE_ACC_NO from BENE_DATA where PAYMENT_TYPE='Domestic Fund Transfer'
	</select>

Similarly, replace the Beneficiary Account No text box with Combo and get the Beneficiary Account No for the combo in the same data-support class.

After adding, map the data-support class path with the Payment Form as com.intellectdesign.modelhouse.dataSupport.PaymentDataSupport

Build your sources and run the application. The form should appear as shown in the following screenshot:

Getting the data for other items using AJAX Request

You can use the form and form item events to get the data dynamically for those controls that cannot get data using the data-support class.

For the given example, we can display the Account Names dynamically on change of Account No combo boxes. This can be achieved through AJAX requests.

In the sample listener class payments.intertransfer.js created for the Payment Form, add the following handlers:

registerHandlers : function ()
{
	this.fm.registerHandler("cbxchange","TXT_DBIT_AC_NO", function (fm, event, fieldName, value)
	{
		if(!cbx.isEmpty (value))
		{
			var paramsOb =
			{
				"INPUT_ACTION" : "GET_ACC_DTLS",
				"INPUT_FUNCTION_CODE" : "VSBLTY",
				"INPUT_PRODUCT" : "PAYMNT",
				"INPUT_SUB_PRODUCT" : "BKSIFT",
				"PAGE_CODE_TYPE" : "ACC_CODE",
				"PRODUCT_NAME" : "PAYMNT",
				"ACCOUNT_NO" : value
			};
			cbx.ajax(
			{
				params : paramsOb,
				success : function (result, request)
				{
					fm.model.setValue('TXT_DBIT_AC_NAME', (response.ACC_NAME||
					response.ACCOUNT_NAME||""));
				},
				scope :this
			}); 
		}
	}); 
	
	/* this.fm.registerHandler("cbxchange","TXT_BEN_AC_NO", function (fm, event,fieldName, value){
	}); */ 
}

Similarly, create a change event for the TXT_BEN_AC_NO combo and get the data through Action Class > Handler Class > Instruction Class > SQL Map from the database.