What is script connector and how to configure it ???

This post is to understand about "Script connector" and how to configure it. Understanding the necessity of script connector is best explained by explaining about the environment that I have been experienced to.

Without script connector :

I was a part of a team which works  on a framework called "Cordys" and which leverages the usage of its connector capability (able to connect to different systems which are in different platforms like .net, java etc.,) using pre-built connectors like UDDI, SAP Connector etc., As a part of the project we need to integrate with couple of systems and build a process performs a functionality by integrating with those systems. We use UDDI services, SAP Services etc., Hence the development of the process is a lot dependent on the implementation and availability of these services without which we won't be able to finish the implementation of the process.

It would be ideal if we remove this dependency and implement it. There is a way to implement it using a connector built by coe team of cordys which is "Script Connector". Advantages of script connector are 

• It mimics the response(user configured response) from different system and hence we won't be depending on the availability of the main system instead we create a dummy system which responds like the way main response does.
• Implementation would be very smooth and doesn't need to depend on any other system.
• All possible cases can be tested without even bothering the main system.

How to mimic the response  :

There are many ways to replicate the main system by dummy system, current explanation deals with mimic-ing the response by custom logic by retrieving the mimic-ed response from xml store of cordys. 

Initial configuration of script connector : 

In order to have script connector completely configured we need to install the script connector isvp in the environment. My cordys environment is "BOP4.1CU6", hence I would install an ISVP and this isvp can be found out from cordys wiki at this particular link,

https://wiki.cordys.com/display/dsc/Script+Connector

Step 1 : Install the isvp

Step 2 : Go to your organization and try creating a new container. You will be finding a new entry for script connector as shown below,

Step 3:  Go ahead and create the container normally.

Extra configuration to be made :

Here in this section this extra configuration is to be done in order to mimic the response for your service.

Step 4 : Find out what is the web service which you want to mimic. In my case it is "ConversionRate" for which I need to connect to internet and I don't have internet, hence I need to remove this dependency by using script connector.

Step 5 : Edit config.properties file located in the directory 

<<CordysInstalledDirectory>>/coe/scriptconnector/config.properties by adding the below property

*=scripts/all.js  --- This property says any service that you want to mimic can go and refer all.js file. Actual implementation is done at all.js file. I will try to attach all.js file as a comment to the blog post. One must have this file at the location 

<<CordysInstalledDirectory>>/coe/scriptconnector/scripts/.

Step 6 : Have all.js file in the location mentioned above.

Step 7 : According to the implementation in all.js, we must configure our custom response in xml store. The mandatory things we need to make sure are,

• We must have a folder in xml store as shown below
• Collection->scriptconnector->all.js
• Now create a folder with the same name as your "WebServiceOperation" in my case it is "ConversionRate".
• Now create an Item with any name but which ends with suffix "-Cur" and put any response in that item. In my case it is "success-Cur". All these steps are shown in the below figure,

Step 8 : Attach the interface to be tested to script connector and test it. You would see the same response which you configured in the xml store.

After testing with all possible cases and when we are done with the development from our side we can port these contents to whatever environment you want and connect the interface to the real system instead of script connector. Script connector should be used only in the development environment and not encouraged to be used in any other environments apart from development.     

How to implement interceptor for UDDI Service calls

This post describes how to implement interceptor for all external service calls done through UDDI Connector.

Why Interceptor ?

• End Point not static : You might change the end point for every UDDI call based on some logic. This is a general scenario where in you need to change the end point of all the services for every deployment. This can be achieved by changing every web service's end point by going to external services configuration but this is a tedious task as one needs to do it after every deployment and also to all the web services in the solution. One might think about the effort of changing 10 interfaces having 10 web services in each interface.
• Defaulting few tags : A Scenario where for every UDDI call there are many tags which are to be defaulted and this should not be taken at process level (BPM level) as it(defaulting) has to be implemented whenever a UDDI service to be called.
• Tweaking the request : A possible scenario of encrypting/decrypting some or all of the request tags.
• Tweaking the response : A possible scenario of encrypting/decrypting some or all of the response tags.
• Audit Log : Auditing every UDDI call.

There might be many more scenarios which might need the concept of Interceptor to be implemented.

How ?

• We need to write a class which extends Customizer class of UDDIConn.jar. This Customizer class is an abstract class, hence a class which extends this class has to implement all the abstract methods among which OnRequest and OnResponse are also the methods.
• A Sample scenario in which request and response is logged is shown below,

package better.faster.smarter.development.uddi.interceptor;

import com.eibus.applicationconnector.uddi.AuthenticationDetails;

import com.eibus.applicationconnector.uddi.Customizer;

import com.eibus.applicationconnector.uddi.IHTTPObject;

import com.eibus.applicationconnector.uddi.InterceptorException;

import com.eibus.applicationconnector.uddi.UserInfo;

import com.eibus.util.logger.CordysLogger;

import com.eibus.util.logger.Severity;

import com.eibus.xml.nom.Node;

public class CustomInterceptor extends Customizer {

private static CordysLogger logger = CordysLogger.getCordysLogger(CustomInterceptor.class);@Override

public void initialize(int arg0) {

// TODO Auto-generated method stub

}

@Override

public void onHttpRequest(IHTTPObject arg0, UserInfo arg1)

throws InterceptorException {

// TODO Auto-generated method stub

}

@Override

public void onHttpResponse(IHTTPObject arg0, UserInfo arg1)

throws InterceptorException {

// TODO Auto-generated method stub

}

@SuppressWarnings("deprecation")@Override

public void onRequest(int arg0) {

// TODO Auto-generated method stub

logger.log(Severity.WARN, "Writing the request " + Node.writeToString(arg0, true));

}

@SuppressWarnings("deprecation")@Override

public void onResponse(int arg0) {

// TODO Auto-generated method stub

logger.log(Severity.WARN, "Writing the response " + Node.writeToString(arg0, true));

}

@Override

public void onSOAPFault(int arg0) {

// TODO Auto-generated method stub

}

@Override

public AuthenticationDetails getAuthenticationDetails(int arg0, String arg1) {

// TODO Auto-generated method stub

return null;

}

@Override

public String getNewEndpointURL(int arg0, String arg1) {

// TODO Auto-generated method stub

return null;

}

}

• A Sample scenario of changing the end point is shown below

package com.schneiderelectric.bfoentwebservices.r1;

import com.eibus.applicationconnector.uddi.AuthenticationDetails;

import com.eibus.applicationconnector.uddi.Customizer;

import com.eibus.applicationconnector.uddi.IHTTPObject;

import com.eibus.applicationconnector.uddi.InterceptorException;

import com.eibus.applicationconnector.uddi.UserInfo;

import com.eibus.util.logger.CordysLogger;

import com.eibus.xml.nom.Node;

import com.eibus.xml.xpath.XPath;

public class BFOReqInterceptor extends Customizer

{

  private static final CordysLogger logger = CordysLogger.getCordysLogger(BFOReqInterceptor.class);

  public String getNewEndpointURL(int request, String origURL) {

    int methodNode = XPath.getFirstMatch("//Body/*", null, request);

    String host = Node.getAttribute(methodNode, "SessionURL", null);

    if (host == null) {

      return origURL;

    }

    if (logger.isDebugEnabled())

      logger.debug("Original URL: " + origURL);

    int dblSlashIndex = origURL.indexOf("//");

    int slashIndex = origURL.indexOf("/", dblSlashIndex + 2);

    int colonIndex = origURL.indexOf(":", dblSlashIndex + 2);

    StringBuffer buf = new StringBuffer(origURL);

    buf.replace(dblSlashIndex + 2, colonIndex != -1 ? colonIndex : slashIndex, host);

    if (logger.isDebugEnabled())

      logger.debug("Updated URL: " + buf.toString());

    return buf.toString();

  }

  public AuthenticationDetails getAuthenticationDetails(int paramInt, String paramString) {

    return null;

  }

  public void initialize(int methodImplementation)

  {

  }

  public void onRequest(int request)

  {

  }

  public void onResponse(int response)

  {

  }

  public void onSOAPFault(int soapFault)

  {

  }

  public void onHttpRequest(IHTTPObject arg0, UserInfo arg1)

    throws InterceptorException

  {

  }

  public void onHttpResponse(IHTTPObject arg0, UserInfo arg1)

    throws InterceptorException

  {

  }

}

• Once the class is developed, we have to make a jar for this package and give this jar as JRE property for UDDI Container.

• Everything will be useless if you don't change the implementation of the web service for which you need this interceptor to work. Since this is a one time activity, this can be covered in development phase. The changes will go with every deployment.

You need to add the below XML Node to the existing UDDI Service

  <interceptor>

    <class>better.faster.smarter.development.uddi.interceptor.CustomInterceptor</class>

  </interceptor>

How to get rid of extensions in chrome browser

Problem Statement : 


I have faced a weird problem with chrome browser. My default search engine of chrome was and is "Google" as 98% of population who surf does (Don't know how many people will be jobless if google starts charging :P). On every search I was getting adds from unknown sources which google is not supposed to show unless there is some external extension attached to the browser. I could not get rid of this extension from the browser permanently. 

Solution Approach :

Things which I have tried :

• There was an extension which was attached to the chrome browser which is "GoSavvea".
• Go to settings of chrome and extensions and disabling "GoSavvea" extension which was enabled.
• Now this step helped me solve the issue temporarily but on a new browser of chrome the issue again pops up with option "Enabled" for this extension.
• Then I have checked by enabling the developer mode and could find out the path from where the extension is being applied and manually deleted the contents from that path.
• The below picture shows the summary of above explanation

Steps to configure - Custom Error Pages in IIS

IIS Version shown in the below post : 

Before setting up the custom error message in the IIS, the below message is shown.

Follow the below steps to suppress error messages:

• Goto Cordys Instance in the IIS.
• Expand the Instance, there you will find the cordys folder inside.
• Select the cordys folder & 
• Double click the Configuration Editor (as shown in the picture below.)

Now, change the default selected mode to "Application Host as show in the below picture" and make sure Section selected is "System.webServer/httpErrors".
This helps us to change the properties shown below.

Now change the properties as shown in the below picture.
Follow the steps below :

Change the properties as suggested below

• allowAbsolutePathWhenDelegated = True
• errormode = Custom
• Click Apply which will be on the right side of the configuration page under Actions

Now change the mode back to "CORDBOP4312/cordys web.config" and properties as shown in the below.

• From  as CORDBOP4312/cordys web.config (CORDBOP4312 is the instance name here)
• errormode as Custom
• Click Apply

After setting all the properties, now set the path of the HTM Page in the error Pages as shown in the below picture:

• Double click the Error Pages of the cordys folder

• Double click 404 row item (Select any error status code which you want to configure, I have configured custom 404 page for my cordys site)
• Unchcek the check box
• Set the path of the HTM page
• Click OK (Refer screenshot for above steps)

Restart the Cordys Site

Now Test the same throw browser and it shows like below

You can find the same explanation in a document which is shared here

--------------------------------------------
Credits : Kacham Naresh

How to trigger a web service from stand alone java

/*
Class EndPoint
*/

package better.faster.smarter.development;

public class EndPoint {
private String hostName="";
public void setHostName(String hostName){
this.hostName=hostName;
}
public String getHostName(){
return this.hostName;
}
}

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

/*

Class ExecuteWebService

*/

package better.faster.smarter.development;

import java.io.DataInputStream;

import java.io.DataOutputStream;

import java.net.URL;

import java.net.URLConnection;

public class ExecuteWebService {

//soapRequest is a string which is used for executing the web service

@SuppressWarnings("deprecation")

protected static String executeWebService(String soapRequest,EndPoint endPt,String signature)

{

String responseString="";

String hostName="";

URL url=null;

URLConnection urlC=null;

DataOutputStream out =null;

DataInputStream in = null;

String str="";

try{

//Validate the endPoint

if(null==endPt)

throw new RuntimeException("Cannot proceed with executing the web service as the end point is null");

//Get the hostName

hostName=endPt.getHostName();

//Validate the endPoint here as well

if("".equalsIgnoreCase(hostName))

throw new RuntimeException("Cannot proceed with executing the web service as the end point is empty");

//Validate the soap Request

if("".equalsIgnoreCase(soapRequest))

throw new RuntimeException("Cannot proceed with executing the web service as the soap request is invalid");

//Get the header value if header is present then add SOAPHeader else trigger direct soap request

if(signature==null)

signature="";

if(!"".equalsIgnoreCase(signature))

{

soapRequest = "<SOAP:Envelope xmlns:SOAP=\"http://schemas.xmlsoap.org/soap/envelope/\">" +

"<SOAP:Header><wsse:Security xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\">" +

                   signature + "</wsse:Security></SOAP:Header><SOAP:Body>" + soapRequest + "</SOAP:Body></SOAP:Envelope>";

}

//Both soap request and end point are valid if the control comes here

//Initialize url and also urlConnection and all the related parameters. Hard coded few of them as 

//they are rarely used in our application

url = new URL(hostName);

urlC = url.openConnection();

urlC.setRequestProperty("Content-Type", "text/xml");

urlC.setDoInput(true);

urlC.setDoOutput(true);

urlC.setUseCaches(false);

out = new DataOutputStream(urlC.getOutputStream());

out.writeBytes(soapRequest);

in = new DataInputStream(urlC.getInputStream());

responseString="";

while(null!=((str=in.readLine())))

responseString+= str+"\n";

}

catch(Exception e)

{

CustomLogger.logEntry("Exception while executing the soap request with details "+e.getMessage());

}

finally{

hostName=null;

url=null;

urlC=null;

}

return responseString;

}

}

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

/*

Class TriggerAssertion

*/

package better.faster.smarter.development;

public class TriggerAssertion {

public static void main(String[] args) {

// TODO Auto-generated method stub

EndPoint endPt = new EndPoint();

endPt.setHostName("http://192.168.1.10/cordys/com.eibus.web.soap.Gateway.wcp?");

String userName="icici";

String userPass="icici";

String requestString =

               "<SOAP:Envelope xmlns:SOAP=\"http://schemas.xmlsoap.org/soap/envelope/\">" +

               "<SOAP:Header>" +

               "<wsse:Security xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\">" +

               "<wsse:UsernameToken xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\">" +

               "<wsse:Username>" + userName + "</wsse:Username>" +

               "<wsse:Password>" + userPass + "</wsse:Password>" +

               "</wsse:UsernameToken>" +

               "</wsse:Security>" +

               "</SOAP:Header>" +

               "<SOAP:Body>" +

               "<samlp:Request xmlns:samlp=\"urn:oasis:names:tc:SAML:1.0:protocol\" MajorVersion=\"1\" MinorVersion=\"1\">" +

               "<samlp:AuthenticationQuery>" +

               "<saml:Subject xmlns:saml=\"urn:oasis:names:tc:SAML:1.0:assertion\">" +

               "<saml:NameIdentifier Format=\"urn:oasis:names:tc:SAML:1.1:nameid-format:unspecified\">" + userName + "</saml:NameIdentifier>" +

               "</saml:Subject>" +

               "</samlp:AuthenticationQuery>" +

               "</samlp:Request>" +

               "</SOAP:Body>" +

               "</SOAP:Envelope>";

String responseString = ExecuteWebService.executeWebService(requestString,endPt,"");

String getUserDetails="<GetUserDetails xmlns=\"http://schemas.cordys.com/notification/workflow/1.0\"></GetUserDetails>";

String sos= "<SOAP:Envelope xmlns:SOAP=\"http://schemas.xmlsoap.org/soap/envelope/\">" +

                "<SOAP:Body>" + getUserDetails + "</SOAP:Body></SOAP:Envelope>";

int ssss = responseString.indexOf("<Signature");

        int k = responseString.indexOf("</samlp:AssertionArtifact>");

        String sigresponse = responseString.substring(ssss, k+26);

responseString=ExecuteWebService.executeWebService(getUserDetails,endPt,sigresponse);

System.out.println(responseString);

}

}

How to get the current working directory from Java

package better.faster.smarter.development;

public class HowToFireWebServiceFromJava {

public static void main(String[] args) {
try{
String curWorkingDir = HowToFireWebServiceFromJava.class.getProtectionDomain().getCodeSource().getLocation().getPath();
System.out.println(curWorkingDir);
}
catch(Exception e)
{

}
}
}

Custom Logging from stand alone Java

This post shows how to log manually in a file system from Java without involving cordys.

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

package better.faster.smarter.development;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

public class CustomLogger {
private static Logger logger = Logger.getLogger("IsureTransUtil");
private static FileHandler fh=null;
protected static void logEntry(String message)
{
try{
DateFormat dateFormat = new SimpleDateFormat("yyyy_MM_dd");
       Calendar cal = Calendar.getInstance();          
    String logFile="IsureTransUtil_"+dateFormat.format(cal.getTime())+".log"; 
//FileHandler takes two parameter 1st one being the fileName and the second one being append mode. If set to true then it will append to the existing log file else will create new one.
    fh = new FileHandler(logFile,true);        
    logger.addHandler(fh);
    SimpleFormatter formatter = new SimpleFormatter();  
       fh.setFormatter(formatter); 
       logger.log(Level.SEVERE, message);        
}
catch(Exception e)
{

}
finally{
//Until you close the file handler the bytes which were written will not get reflected.
fh.close();
}
}
}

-----------------------------------------------------------------------------------------------
Usage of the above class
----------------------------------------------------------

package better.faster.smarter.development;

public class HowToLog{

public static void main(String[] args) {
try{
CustomLogger.logEntry("Started custom Logging");
}
catch(Exception e)
{

}
}
}

How to enable hyperlink to a column of table

    var data = new Object();
    data = eventObject.businessObject;
    eventObject.srcElement.style.color = "blue";
    eventObject.srcElement.style.cursor = "hand";
    eventObject.srcElement.style.textDecoration = "underline";
    eventObject.srcElement.onclick = function()
    {
        if(cordys.getNodeText(data,".//*[local-name()='GDD_BENE_CODE']","") !="")
        //beneDetails is supposed to have a UI attached to it
        application.showDialog(beneDetails.XMLDocument.documentElement , data);
    }

Javascript to get the current language of the browser

function getLanguageCode() {
    var lang;
    if (getQueryVariable("language")) {
        lang = getQueryVariable("language");
    } else if (navigator) {
        if (navigator.userLanguage) {
            lang = navigator.userLanguage;
        } else if (navigator.browserLanguage) {
            lang = navigator.browserLanguage;
        } else if (navigator.systemLanguage) {
            lang = navigator.systemLanguage;
        } else if (navigator.Language) {
            lang = navigator.Language;
        } else if (navigator.language) {
            lang = navigator.language;
        }
    }
    alert(lang);
}

function getQueryVariable(variable) {
    var query = window.location.search.substring(1);
    var vars = query.split("&");
    for (var i = 0; i < vars.length; i++) {
        var pair = vars[i].split("=");
        if (pair[0] == variable) {
            return pair[1];
        }
    }
    return false;
}

Credits : Amit Kumar 
Email : amitagl27@gmail.com

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