...

Panel

nameid	blue0

Useful methods frequently used in the system concerning date, SQL and metadata handling.

What does this section cover?

Table of Contents

Date Handling Methods

Date handling methods can be found in com.crm.framework.util.DateUtil class.

Handling Java Dates

Expand

title	Truncate Date

truncate(Date date) method returns the date rounded to the day with a time of 00:00:00:00. setEndOfDay(Date date) method returns the date rounded to the day with a time of 23:59:59:999.

Code Block

title	Example

public DateRange getDateRange(Date fromDate, Date toDate){
	if (fromDate!=null && toDate!=null )
	{
		fromDate = DateUtil.truncate(fromDate);
		toDate = DateUtil.setEndOfDay(toDate);
		range = new DateRange(fromDate, toDate);
	}
		
	return range;
}

...

Expand

title	Add period of time to date

add(Date date, int timeValue, UnitOfTime uot) method adds the given number(time value) of units to the given date.

Code Block

title	Example

public Boolean getCompletionDate(Date startDate, Integer timeValue, UnitOfTime uot){
	
	Date completionDate = DateUtil.add(startDate, timeValue, uot);
		
	return completionDate ;
}

Alternatively, any of the following methods can be used if the unit of time is known and not given.

addYears(Date date, int numberOfYears) method adds the given number of years to the given date.
addMonths(Date date, int numberOfMonths) method adds the given number of months to the given date.
addWeeks(Date date, int numberOfWeeks) method adds the given number of weeks to the given date.
addDays(Date date, int numberOfDays) method adds the given number of days to the given date.
addHours(Date date, int numberOfHours) method adds the given number of hours to the given date.
addMinutes(Date date, int numberOfMinutes) method adds the given number of minutes to the given date.
addSeconds(Date date, int numberOfSeconds) method adds the given number of seconds to the given date.

Expand

title	Retrieve current date

1. getCurrentDate(String timeZoneID) method calculates and returns the current date based on the given time zone ID (the ID for a TimeZone, either an abbreviation such as "PST", a full name such as "America/Los_Angeles", or a custom ID such as "GMT-8:00". Note that the support of abbreviations is for JDK 1.1.x compatibility only and full names should be used).

Code Block

title	Example

...	
	Date currentDate = DateUtil.getCurrentDate(getCRMSession().getTimezone());
		
...

OR

2. getCurrentDate(TimeZone timeZone) method calculates and returns the current date based on the given time zone object (java.util.TimeZone).

Code Block

title	Example

...	
	String timeZoneID = getCRMSession().getTimezone();
	TimeZone timeZone= TimeZone.getTimeZone(timeZoneID);
	Date currentDate = DateUtil.getCurrentDate(timeZone);	
...

Expand

title	Convert time zone

convertDate(Date date, String fromTimeZone,String toTimeZone) method converts the given date from one time zone to another.

Code Block

title	Example

...	
    //The date is converted to UTC time zone
	date1 = DateUtil.convertDate(date1,getCRMSession().getTimezone(), "UTC");
		
...

Expand

title	Adjust server date based on client time zone

adjustServerDateBasedOnClientTimeZone(Date val, String organisationTimeZone,HttpServletRequest request) method adjusts the server date based on the client's time zone.

Code Block

title	Example

...	
  	SuperSession ss=WebUtil.getSuperSession(request);	//request object is of type javax.servlet.http.HttpServletRequest
	serverDate = DateUtil.adjustServerDateBasedOnClientTimeZone(serverDate,ss.getTimezone(),request);
...

Expand

title	Adjust client date based on server time zone

adjustClientDateBasedOnServerTimeZone(Date val, String organisationTimeZone,HttpServletRequest request) method adjusts the cient date based on the server's time zone.

Code Block

title	Example

...	
  	SuperSession ss=WebUtil.getSuperSession(request);	//request object is of type javax.servlet.http.HttpServletRequest
	clientDate = DateUtil.adjustClientDateBasedOnServerTimeZone(clientDate,ss.getTimezone(),request);
...

Handling SQL Dates

Difference between two dates

Expand

title

title	Get SQL Date

getCurrentSQLDate(String dbType, String datefield) method returns the date format of current date based on DB type

Code Block

title	Hibernate Query Example

/**
	 * Checks if a billing term scheme is used by a billing term.
	 * 
	 * @param billingTermScheme - the billing term scheme to check
	 * @return true if a billing term scheme is used by a billing term, false if not
	 * @throws Exception
	 */
	public Boolean isUsedByBillingTerms(CRMDOBillingTermScheme billingTermScheme) throws Exception {
		
		ArrayList<Object> parameters = new ArrayList<Object>();
		
		String sql = 
				"\n SELECT 'x' FROM BILLINGTERMS " +
				"\n WHERE BTDELETED = 0 " +
				"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEFFECTIVEDATE") + "<=" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n AND ((" + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEXPIRATIONDATE") + ">" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n		OR BTEXPIRATIONDATE IS NULL)) " +  
				"\n AND BTSCHEMECODE = ?";
	
		parameters.add(billingTermScheme.getCode());
		
		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, getCRMSession().getDbtype()))
		{
			return new Boolean(true);
		}
		else
		{
			return new Boolean(false);
		}
	}

getSelectSQLDate(String dbType, String datefield) method returns a String script which is the given date or date field in the correct form for the given the database type. It works for both SQL and Hibernate andHibernate queries.

Code Block

title	Hibernate Query Example

public ArrayList<CRMDO> loadEffective(Date asOfDate){
	ArrayList<Object> values = new ArrayList<Object>();
	String dbType = getCRMSession().getDbtype();
	if(expirationDate!=null)
	{
		values.add(asOfDate);
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate")+"<=:p"+values.size();
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate")+">:p"+values.size();
	}
	
	return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
				values,
				getDefaultAssociations(),
				null,
				null);	
	
}

Code Block

title	SQL Query Example

public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
		String dbtype = getCRMSession().getDbtype();

		String sql = 
						"\n 	SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
						"\n 	WHERE RT.RENTTYPEID = ?" +
						"\n     AND PV.PRODVALIDITYDELETED = 0 " +
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) + 
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) ;
					
 
		ArrayList<Object> parameters = new ArrayList<Object>();
		parameters.add(rentalType.getId());

		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
		{
			return new Boolean(true);
		}
 
	return new Boolean(false);
}

Expand

title	Add period of time to SQL Date

addMonthsSQLDate(String dbType, String dateField, int months) method returns a script which adds the given number of months to the given date field. It works for both SQL and Hibernate queries.

Code Block

title	Hibernate Query Example

public ArrayList<CRMDO> loadEffectiveAfterAMonth(Date asOfDate){
	ArrayList<Object> values = new ArrayList<Object>();
	String dbType = getCRMSession().getDbtype();
	if(expirationDate!=null)
	{
		values.add(asOfDate);
		criteria+= " and " + DateUtil.addMonthsSQLDate(dbType, DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate"),1)+"<=:p"+values.size();
		criteria+= " and " + DateUtil.addMonthsSQLDate(dbType, DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate"),1)+">:p"+values.size();
	}
	
	return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
				values,
				getDefaultAssociations(),
				null,
				null);	
	
}

Code Block

title	SQL Query Example

public Boolean isEffectiveAfterAMonth(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
		String dbtype = getCRMSession().getDbtype();

		String sql = 
						"\n 	SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
						"\n 	WHERE RT.RENTTYPEID = ?" +
						"\n     AND PV.PRODVALIDITYDELETED = 0 " +
						"\n 	AND " + DateUtil.addMonthsSQLDate(dbType,DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE"),1) + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) + 
						"\n 	AND " + DateUtil.addMonthsSQLDate(dbType,DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE"),1) + " > " + DateUtil.getSQLDate(dbtype, asOfDate) ;
					
 
		ArrayList<Object> parameters = new ArrayList<Object>();
		parameters.add(rentalType.getId());

		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
		{
			return new Boolean(true);
		}
 
	return new Boolean(false);
}

SQL Handling Methods

Metadata Handling Methods

...

The following method works in a corresponding way:

addDaysSQLDate(String dbType, String dateField, int days) method returns a script which adds the given number of days to the given date field. It works for both SQL and Hibernate queries.

SQL Handling Methods

SQL handling methods can be found in com.crm.framework.util.SQLUtil class.

Expand

title	Retrieve rows using prepared statement

The following methods return a ResultSetUtil object containing the records returned by the given SQL prepared statement and the list of parameters run on the given DB . In the first method, the maximum number of returned rows can be defined.

executeUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType, Integer maxRows)

executeUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType)

Code Block

title	Example

public ArrayList<CUSTOMCRMDORental> loadLatest(CRMDOAccountReceivable account, Integer numberOfRentals){
	
	ArrayList<CUSTOMCRMDORental> rentals = new ArrayList<CUSTOMCRMDORental>();
	ArrayList<Object> parameters = new ArrayList<Object>();
 
	String sql ="\n  SELECT RENTALID FROM TRN_RENTALS" +
					"\n  WHERE RENTALDELETED = 0  " +
					"\n AND ACCRECID = ?"+
					"\n ORDER BY RENTALCREATEDDATE" ;
	
	parameters.add(account.getId());
 
	ResultSetUtil rsu =  SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype(), numberOfRentals);
	
	if(rsu!=null && rsu.next())
	{
		if(rsu.getString("RENTALID")!=null)
		{
			rentals .add((CUSTOMCRMDORental)rentalBean.load(rsu.getString("RENTALID")));
		}
	}
 
	return rentals;
}

Expand

title	Check if query returned rows

The following method returns true if the given prepared statement and the list of parameters run on the given DB return at least one row. If not the method returns false.

queryReturnedRows(String sqlStatement, String jndi, ArrayList<Object> parameters, String dbType)

Code Block

title	Example

public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
		String dbtype = getCRMSession().getDbtype();

		String sql = 
						"\n 	SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
						"\n 	WHERE RT.RENTTYPEID = ?" +
						"\n     AND PV.PRODVALIDITYDELETED = 0 " +
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) + 
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) ;
					
 
		ArrayList<Object> parameters = new ArrayList<Object>();
		parameters.add(rentalType.getId());

		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
		{
			return new Boolean(true);
		}
 
	return new Boolean(false);
}

Expand

title	Update rows using prepared statement

The following method runs an update SQL prepared statement on the given DB .

executeUpdateUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType)

Code Block

title	Example

public void deleteNonEffective(Date asOfDate, CUSTOMCRMDORentalType type){
	
	ArrayList<Object> parameters = new ArrayList<Object>();
 
	String sql = 
						"\n 	UPDATE TRN_RENTALTS " +
						"\n 	SET RENTALDELETED = 1" +
						"\n 	WHERE RENTALTYPEID = ?" +
						"\n 	AND "+ DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " < " + DateUtil.getSQLDate(dbtype, asOfDate);
	
	parameters.add(type.getId());
 
	ResultSetUtil rsu =  SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype(), numberOfRentals);
	
	SQLUtil.executeUpdateUsingPreparedStatement(
				sql, 
				getOrganisationID(), 
				parameters,
				getCRMSession().getDbtype());
}

Metadata Handling Methods

Metadata handling methods can be found in com.crm.framework.metadata.MetadataUtil class.

Expand

title	Get entity by class, id, type entity id and table

The following methods return an Entity object which holds information about the entity as defined in entities metadata file.

getEntityByClass(String className, String context, String mpID)

getEntity(String entityID, String context, String mpID)

Code Block

title	Example

...
Entity rentalEntity = MetadataUtil.getEntityByClass(CUSTOMCRMDORental.getClass().getName(), getCRMSession().getRealPath(), getOrganisationID());
String entityId = rentalEntity.getId();
String moduleId = rentalEntity.getModuleid();
String typeEntityId = rentalEntity.getTypeentityid();	
Entity typeEntity = MetadataUtil.getEntity(typeEntityId, getCRMSession().getRealPath(), getOrganisationID());		
...

getEntityByTable(String tableName, String context, String mpID)

getEntityByTypeEntity(String typeEntityID, String context, String mpID)

Code Block

title	Example

...
Entity rentalTypeEntity = MetadataUtil.getEntityByTable("TRN_RENTALTYPES", getCRMSession().getRealPath(), getOrganisationID());
Entity rentalEntity = MetadataUtil.getEntityByTypeEntity(rentalTypeEntity.getId() , getCRMSession().getRealPath(), getOrganisationID());
...

Expand

title	Get module by id

The following method returns a Module object which holds information about the module as defined in modules metadata file.

getModuleByID(String moduleID, String context, String mpid)

Code Block

title	Example

...
String moduleId = rentalEntity.getModuleid();
Module module = MetadataUtil.getModuleByID(moduleId, getCRMSession().getRealPath(), getOrganisationID());		
...

Expand

title	Get module additional process

The following method returns a Process object which holds information about the process as defined in modules metadata file.

getModuleAdditionalProcess(Module module, String processID)

Code Block

title	Example

...
Process process = MetadataUtil.getModuleAdditionalProcess(rentalsModule, "CUSTOM_SET_RENTAL_AS_EFFECTIVE");	
ArrayList<Method> methods = process.getMethods();
...

Expand

title	Get Variable

The following method returns a global variable value as defined in variables metadata file.

getVariable(String variableName, String context, String mpID)

Code Block

title	Example

...
String testVariableValue= MetadataUtil.getVariable("myvariable", getCRMSession().getRealPath(), getOrganisationID());
...

Versions Compared

Old Version 7

New Version Current

Key

Date Handling Methods

Handling Java Dates

Handling SQL Dates

SQL Handling Methods

Metadata Handling Methods

SQL Handling Methods

Metadata Handling Methods

Page Comparison

Versions Compared

Old Version 7

New Version Current

Key

Date Handling Methods

Handling Java Dates

Handling SQL Dates

SQL Handling Methods

Metadata Handling Methods

SQL Handling Methods

Metadata Handling Methods