...
...
...
...
...
...
...
...
| Panel |
|---|
|
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
| 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 | 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 |
|---|
| ...
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 |
|---|
| ...
String timeZoneID = getCRMSession().getTimezone();
TimeZone timeZone= TimeZone.getTimeZone(timeZoneID);
Date currentDate = DateUtil.getCurrentDate(timeZone);
... |
|
...
| 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 |
|---|
| ...
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 |
|---|
| ...
SuperSession ss=WebUtil.getSuperSession(request); //request object is of type javax.servlet.http.HttpServletRequest
clientDate = DateUtil.adjustClientDateBasedOnServerTimeZone(clientDate,ss.getTimezone(),request);
... |
|
|
Handling SQL Dates
| Expand |
|---|
|
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 script which is the given date or date field in the correct form for the given the database type. It works for both SQL 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 |
|---|
| 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 |
|---|
| 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 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 |
|---|
| 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 | 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 |
|---|
| 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 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 |
|---|
| ...
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 |
|---|
| ...
Entity rentalTypeEntity = MetadataUtil.getEntityByTable("TRN_RENTALTYPES", getCRMSession().getRealPath(), getOrganisationID());
Entity rentalEntity = MetadataUtil.getEntityByTypeEntity(rentalTypeEntity.getId() , getCRMSession().getRealPath(), getOrganisationID());
... |
|
...