How to style a row using POI

/*To style a row a cellStyle instance to be used in POI API and below steps are to be performed*/

/*Create an instance of HSSFCellStyle and it can be created as below
Below wb corresponds to HSSFWorkbook() class
wb = new HSSFWorkbook();
 */

HSSFCellStyle cellStyle = wb.createCellStyle();

/*Then a setFillForegroundColor method to be called using the color index. The color index is a short variable and defined as below*/

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

/*A setFillPattern method is to be called with another short variable to be sent as a variable*/

cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

/*Finally a cellStyle variable is ready and can be used to apply style but note that this style will be over written when you try to write value in the cell*/
/*The same style should be applied when writing the cellValue as shown further in the code*/

header.setRowStyle(cellStyle);

/*This creates a cell with value as 'Sample value' not this will remove the styling applied to the row and hence the styling should be applied again*/ 

header.createCell(1).setCellValue("Sample value");

/*As explained before the styling pattern is applied again*/

header.getCell(1).setCellStyle(cellStyle);
fileOutputStream = new FileOutputStream(filePath +"\\ExcelUtilities.xls");
wb.write(fileOutputStream);
fileOutputStream.close();

How to read auto generated key in Java

Database : SQL Server 2008

insertSuccessFileObj = new ();
insertSuccessFileObj.setFILE_CODE("DASHBOARD");
insertSuccessFileObj.setFILE_TYPE("MARS_DASHBOARD_DETAILS");
insertSuccessFileObj.setFILE_NAME(fileName);
insertSuccessFileObj.setFILE_PATH(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
insertSuccessFileObj.setUPLOADED_DATE(new Date());
insertSuccessFileObj.setMONTH(MONTH);
insertSuccessFileObj.setYEAR(Double.valueOf(YEAR));
insertSuccessFileObj.setCREATED_ON(new Date());
insertSuccessFileObj.setCREATED_BY(BSF.getUser().substring(BSF.getUser().indexOf("=") + 1, BSF.getUser().indexOf(",")));
insertSuccessFileObj.insert();
double fileID = insertSuccessFileObj.getFD_ID(); //fileID returned would be 0.0

The above is a small sample of code which inserts record in a table called MARS_FILE_DETAILS in which FD_ID is a column which is auto generated in database which is of type Double. 

Requirement is, we want to insert record through program and read the FD_ID (auto generated field) and return it as response. This is not achievable using the above sample piece of code because the java layer will know about the whereabouts of primary key only after committing the transaction. Hence we need to commit through program and get the FD_ID. This is done using below line

BSF.getObjectManager()._commitTransactionDirect(true); 

Hence the above sample would be modified as below

MARS_FILE_DETAILS insertSuccessFileObj = new MARS_FILE_DETAILS();
insertSuccessFileObj.setFILE_CODE("DASHBOARD");
insertSuccessFileObj.setFILE_TYPE("MARS_DASHBOARD_DETAILS");
insertSuccessFileObj.setFILE_NAME(fileName);
insertSuccessFileObj.setFILE_PATH(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
insertSuccessFileObj.setUPLOADED_DATE(new Date());
insertSuccessFileObj.setMONTH(MONTH);
insertSuccessFileObj.setYEAR(Double.valueOf(YEAR));
insertSuccessFileObj.setCREATED_ON(new Date());
insertSuccessFileObj.setCREATED_BY(BSF.getUser().substring(BSF.getUser().indexOf("=") + 1, BSF.getUser().indexOf(",")));
insertSuccessFileObj.insert();
BSF.getObjectManager()._commitTransactionDirect(true);
double fileID = insertSuccessFileObj.getFD_ID();

Sample function on how to generate an excel

Advantages of below method is Query and the template columns are dynamic
----------------------------------------------------------------------------------------------------

public static int generateTemplateDashboard(String MONTH,String YEAR,String TEMPLATE_NAME)
{
int response=0;
String responseStr="<OperationResult><Status/><StatusMessage/><FilePath/></OperationResult>";
Document doc = new Document();
String queryText="";
QueryObject query = null;
BusObjectIterator wholsaleItems=null;
int templateData=0;
int queryTemplateConfig=0;
int temptableHeaderData=0;
String excelHeaders[]=null;
String columnNames[]=null;
String dataTypes[]=null;
String columnData[]=null;
int responseData=0;
try{
oDoc=BSF.getXMLDocument();
response = doc.parseString(responseStr);
//Loading Query Template from configuration
queryTemplateConfig = oDoc.load(installDir + "\\com\\vw\\mars\\ExportXLQueriesConfig.xml");
queryText = Node.getData(XPath.getFirstMatch(".//QUERY[@TEMPLATE_NAME='"+TEMPLATE_NAME+"']", null, queryTemplateConfig));
logger.log(Severity.WARN, "Logging the query text as "+queryText);
if(queryText==null || queryText.equalsIgnoreCase(""))
throw new BsfRuntimeException("Query Template not found from the configuration");
query = new QueryObject(queryText);
query.addParameter("MONTH", "MARS_WHOLSALE_FORECASTS.MONTH", QueryObject.PARAM_STRING, MONTH);
query.addParameter("YEAR", "MARS_WHOLSALE_FORECASTS.YEAR", QueryObject.PARAM_STRING, YEAR);
wholsaleItems = query.getObjects();
if(logger.isWarningEnabled())
logger.log(Severity.WARN, "Query got executed with data "+Node.writeToString(query.execute().getDatasetNode(), true));
//Write the header of the excel
HSSFWorkbook wb = null;
HSSFSheet marsSheet = null;
Row header = null;
String filePath = props.getProperty("mars.download.directory");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String currentTimeStamp = sdf.format(getCurrentDate());
String fileName = "Dashboard_"+YEAR + "_" + MONTH + "_" + currentTimeStamp + ".xls";
String abPth = "";
abPth = filePath + "\\"+TEMPLATE_NAME;
logger.log(Severity.WARN, "Logging absolute path as "+abPth);
//Creating the directory if it is not present
File createFolders = new File(filePath + "\\" + YEAR + "\\" + MONTH);
createFolders.mkdirs();
logger.log(Severity.WARN,"Directory created");
wb = new HSSFWorkbook(); // new HSSFWorkbook();
marsSheet = wb.createSheet(TEMPLATE_NAME);
header = marsSheet.createRow(0);

//Read the header columns from ExportConfig.xml
templateData = oDoc.load(installDir+ "\\com\\vw\\mars\\ExportXLConfig.xml");
logger.log(Severity.WARN, "Logging Export XL Config file "+Node.writeToString(templateData,true));
temptableHeaderData = XPath.getFirstMatch(".//*[local-name()='filetype'][@name='"+TEMPLATE_NAME+"']", null, templateData);
logger.log(Severity.WARN, "Logging template header data node "+Node.writeToString(temptableHeaderData,true));

int length = XPath.getMatchingNodes(".//*[local-name()='field']", null, temptableHeaderData).length;
excelHeaders = new String[length];
columnNames = new String[length];
dataTypes = new String[length];
columnData = new String[length];
for (int i = 0; i < length; i++) {
excelHeaders[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "header_name", "");
columnNames[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "name", "");
dataTypes[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "datatype", "");
}
logger.log(Severity.WARN,"Finished reading the template and stored all the headers with datatypes into string arrays ");
for (int i = 0; i < excelHeaders.length; i++)
header.createCell(i).setCellValue(excelHeaders[i]);
logger.log(Severity.WARN, "Finished writing headers of the dashboard excel");
responseData = TupleHandling.makeTupleSet(wholsaleItems, null);
int tupleRecords[] = XPath.getMatchingNodes(
".//*[local-name()='tuple']", null, responseData);
for (int i = 0; i < tupleRecords.length; i++) {
header = marsSheet.createRow(i + 1);
for (int j = 0; j < columnNames.length; j++) {
columnData[j] = Node.getData(Node.getFirstChild(XPath.getFirstMatch(".//*[local-name()='"+ columnNames[j]+ "']", null,tupleRecords[i])));
logger.log(Severity.WARN, "Reading Column value as "+columnData[j]);
}
if (tupleRecords[i] > 0)
Node.delete(tupleRecords[i]);
logger.log(Severity.WARN,"Deleted the tupleRecord[i]");
for (int j = 0; j < columnData.length; j++)
{
if(dataTypes[j].equalsIgnoreCase("NUMERIC"))
{
header.createCell(j).setCellValue(Integer.valueOf(columnData[j]));
header.getCell(j).setCellType(Cell.CELL_TYPE_NUMERIC);
}
else if(dataTypes[j].equalsIgnoreCase("STRING"))
{
header.createCell(j).setCellValue(columnData[j]);
header.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
}
}
}
for (int i = 0; i < excelHeaders.length; i++)
marsSheet.autoSizeColumn(i);
FileOutputStream fileOutputStream = new FileOutputStream(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
wb.write(fileOutputStream);
fileOutputStream.close();
logger.log(Severity.WARN, "Successfully written the excel template at "+filePath);
Node.setDataElement(response, "Status", "SUCCESS");
Node.setDataElement(response, "StatusMessage", "Application is successful in generating zonal allocation dashboard excel");
Node.setDataElement(response, "FilePath", filePath);
}
catch(Exception e)
{
Node.setDataElement(response, "Status", "FAILED");
Node.setDataElement(response, "StatusMessage", "Application has encountered exception while generating dashboard with details "+e.getMessage());
logger.log(Severity.ERROR,"Exception in generateTemplateDashboard ",e);
}
finally{
if(Node.isValidNode(templateData))
Node.delete(templateData);
if(Node.isValidNode(temptableHeaderData))
Node.delete(temptableHeaderData);
if(Node.isValidNode(queryTemplateConfig))
Node.delete(queryTemplateConfig);
if(Node.isValidNode(responseData))
Node.delete(responseData);
query = null;
wholsaleItems = null;
}
return response;
}

Publishing the content is ever lasting

Sometimes a possible situation arises when publishing takes ever longing time. This is identified to be due to BPMs in the project as every publish will have an entry for PSL (Published Source Layer). This can be solved by adding a property for cws config

 development.publish.to.psl.enabled=false

------------------------------------------------------------------------------------------
Below is the history behind this problem

Problem

Publishing business processes seems to take ever more time.

Reason

In the Process Instance Manager (PIM) and from the Inbox you have the option to graphical view the progress definition of the process instance. For every publish of a business process, the design time information is stored in a Published Source Layer (PSL). Each process instance refers to a PSL and in this way we know which version of the process definition belongs to the instance. On every publish of a business process, a new PSL is created. Due to some performance issues, this takes ever more time.

Solution

The best solution is to solve the performance issues. For the meantime there are two alternatives:

Processor settings

In case you don’t need to view all versions of the business process from the Process Instance Manager while developing, you can disable publishing to PSL. This can be achieved by:

putting the following line in cws.properties (in install dir\components\cws\config):

development.publish.to.psl.enabled=false

restart the CWS processor

Please note that this setting applies to this whole Cordys installation, not only your current workspace.

Fresh organization

In case you do need the runtime versioning of the business process from the Process Instance Manager, there is a workaround by regularly moving to another organization, as PSLs are isolated per organization.

Applies to

Cordys BOP-4 GA, and all it's CU's

Utilities

Index :

• GetDayWeek from Date (Get the day or week from the date passed)
• GetDaysDiff (Calculating the difference between two dates)
• parseCellDataToString (Reading the excel cell value irrespective of cellType)

---------------------------------------------------------------------------------------------------------------------

Gets the Day or the Week from the given date

public static String getDayWeek(String sourceDate, String opType) {
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
try {
if ("WEEK".equalsIgnoreCase(opType)) {
String week = new SimpleDateFormat("w").format(sdf
.parse(sourceDate));
return week;
} else if ("DAY".equalsIgnoreCase(opType)) {
String day = new SimpleDateFormat("dd-MMM").format(sdf
.parse(sourceDate));
return day;
} else
return "";
} catch (Exception e) {
return "";
}
}

Usage :

getDayWeek("10/10/2014","DAY")

----------------------------------------------------------------------------------------------------------

Gets the difference in days between two days

public static int getDaysDiff(java.util.Date startDate,
java.util.Date endDate) {
long dateDiff = startDate.getTime() - endDate.getTime();
return (int) (dateDiff / (1000 * 60 * 60 * 24));

}

----------------------------------------------------------------------------------------------------------

 public static String parseCellDataToString(Cell cell){
    String cellStr;
int cellType= cell.getCellType();
        if(cellType==Cell.CELL_TYPE_STRING){
            // Return as it is since it is already in string format unless its date type  
            cellStr = cell.getStringCellValue();
        }
        else if(cellType==Cell.CELL_TYPE_NUMERIC || cellType==Cell.CELL_TYPE_FORMULA){
            // Remove any fractional data by parsing to long and then parse to string and return
            cellStr = String.valueOf((long)(cell.getNumericCellValue()));
        }
        else if(cellType==Cell.CELL_TYPE_BOOLEAN){
            cellStr = String.valueOf(cell.getBooleanCellValue());
        }
        else if(cellType==Cell.CELL_TYPE_BLANK){
        cellStr = "";
        }
        else{
        cellStr = "";
        }
        cellStr = cellStr.trim();
        return cellStr;

    }

How to use STUFF keyword in Microsoft SQL Server

Usage of STUFF in Microsoft SQL Server 

General syntax of STUFF in SQL Server is 

STUFF ( character_expression , start , length , replaceWith_expression )

You can refer the details from below link
http://msdn.microsoft.com/en-IN/library/ms188043.aspx

SELECT DISTINCT COMMISION_NUMBERS = 
STUFF((Select ','+COMMISION_NUMBER from MARS_ALLOCATION_DATA where MONTH='JUN' and YEAR=2014
 FOR XML PATH('')),1,1,'') FROM MARS_ALLOCATION_DATA

Working with NULLABLE fields in SQL Queries

This post describes possible problems and resolutions on how to "Work with NULLABLE fields in SQL Queries"

Assumptions :

Retrieving column information from a table with some search criteria

Table Name : MARS_ALLOCATION_DATA
Columns : ALLOCATION_NUMBER, ZONE_CODE and ZONE_DESCRIPTION

Sample Query :

SELECT ALLOCATION_NUMBER,ZONE_CODE,ZONE_DESCRIPTION FROM MARS_ALLOCATION_DATA
WHERE MONTH='JUN' AND YEAR=2014

Retrieves ALLOCATION_NUMBER, ZONE_CODE and ZONE_DESCRIPTION from table mentioned whose MONTH is 'JUN' and YEAR is 2014

We wanted to add one more condition as to "Retrieve those records in which ZONE_CODE and ZONE_DESCRIPTION are not same hence we add one more extra condition in query as 
' AND ZONE_CODE != ZONE_DESCRIPTION' which ideally should give the response we deserve but it would give a response which is shown below

Where as ideally a record with ZONE_CODE as 'S' and ZONE_DESCRIPTION is NULL should be retrieved by adding the filter in the query as ZONE_CODE != ZONE_DESCRIPTION. 

Reason :

When adding a search filter which involves NULLABLE fields the filter won't work if one of the columns have NULL in the data as even NULL is not equal to NULL, hence resulting in the response which involves only non-null records in the two columns which are used for filter.

In Such cases it is suggested to add the filter as 
'ISNULL(ZONE_CODE,'') != ISNULL(ZONE_DESCRIPTION,'')'

Hence entire query will be changed accordingly,

SELECT ALLOCATION_NUMBER,ZONE_CODE,ZONE_DESCRIPTION FROM MARS_ALLOCATION_DATA
WHERE MONTH='JUN' AND YEAR=2014 AND ISNULL(ZONE_CODE,'') != ISNULL(ZONE_DESCRIPTION,'')

Which results in the below expected response,