Skip to end of banner
Go to start of banner

Use Useful Framework Methods

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

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

What does this section cover?

 

Date Handling Methods

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

Handling Java Dates

 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.

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;
}
 Difference between two dates

getDateDiff( int calculationUnit, Date d1, Date d2 ) method returns the difference of the two dates in the given calculation unit. 

Example
public Boolean expired(Date fromDate, Date toDate, Integer daysValid){
	
	Integer numberOfDays = DateUtil.getDateDiff(Calendar.DAY_OF_YEAR, fromDate, toDate);
	
	if(numberOfDays>daysValid)
	{
		return true;
	}
		
	return false;
}
 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.

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. 

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

Handling SQL Dates

 Get SQL Date

getSelectSQLDate(String dbType, String datefield) method returns a 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 queries.

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);	
	
}
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);
}
 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.

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);	
	
}
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);
}

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.

 Retrieve results from DB

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)

 

Hibernate Query 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;
}

 

Metadata Handling Methods

 

parameters.add(itemCode);

  • No labels