| Panel |
|---|
|
Useful methods frequently used in the system concerning date, SQL and metadata handling. What does this section cover? |
...
| Expand |
|---|
|
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 |
|---|
| 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 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 |
|---|
| 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.
| Expand |
|---|
| title | 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) | Code Block |
|---|
| title | 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);