@SuppressWarnings("unchecked")
public void render(Map model, HttpServletRequest request, HttpServletResponse response)
throws Exception {
log.info("ENTER - GetuserInbox render()");
response.setContentType(Constants.CONTENT_TYPE);
response.setHeader("Cache-Control", Constants.CACHE_CONTROL);
userId = CommonDAO.getUSERID(request);
groupId = CommonDAO.getUSERGROUPID(request);
PrintWriter pw = response.getWriter();
if (Constants.DEBUG) pw.write(readData("/getList.do").toString());
else {
pw.write(getData().toString());
}
pw.flush();
pw.close();
log.info("EXIT - GetuserInbox render()");
}
public String deleteData(String resultSetID, SimpleJdbcTemplate template) {
int id = 0, idPar = 0;
CommonDAO.setDbmsClientInfo(template, userId);
log.info("******************ENTERED DELETE");
id =
template.update(
"DELETE FROM LAU_SAVED_QUERY_RESULTS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);
idPar =
template.update(
"DELETE FROM LAU_QUERY_RESULT_SETS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);
log.info("LauSaveResultSetDAO delete() ID Child -> " + id + " Parent -> " + idPar);
return String.valueOf(idPar);
}
@SuppressWarnings("unchecked")
public void render(Map model, HttpServletRequest request, HttpServletResponse response)
throws Exception {
String tableName = "", keyName = "", orderby = "", extraFilter = "";
long reportId = 0;
userId = CommonDAO.getUSERID(request);
response.setContentType(Constants.CONTENT_TYPE);
response.setHeader("Cache-Control", Constants.CACHE_CONTROL);
PrintWriter pw = response.getWriter();
pw.write(getData().toString());
pw.flush();
pw.close();
log.info("EXIT - GetAccessibleUsersList()");
}
public int insertDocumentStatus(ExternalDocuments externalDocs) throws Exception {
int id = 0;
String sql =
"INSERT INTO LAU_EXTERNAL_DOCUMENTS (DOCUMENT_STATUS,UPDATE_USER_ID,UPDATE_TIMESTAMP,"
+ "DOCUMENT_REJECTION_REASON,OBJECT_ID,DOCUMENT_NAME,FILE_NAME,DOCUMENT_DESCRIPTION,DOCUMENT_URL,USER_GROUP_ID,"
+ "EXTERNAL_DOCUMENT_ID,E2B_EXCHANGE_ID,E2B_PARTNER_ID,E2B_MESSAGENUMB,IS_E2B_RTF,FILE_TYPE,RECEIVED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'Y','E2B',current_timestamp)";
try {
long externalDocumentId = CommonDAO.getPrimaryKey(getJdbcTemplate());
log.info("New externalDocumentId -> " + externalDocumentId);
id =
getJdbcTemplate()
.update(
sql,
new Object[] {
externalDocs.getStatus(),
externalDocs.getUser(),
externalDocs.getDstamp(),
externalDocs.getRejectReason(),
externalDocs.getObjectID(),
externalDocs.getDocumentTitle(),
externalDocs.getDocumentName(),
externalDocs.getDocDescription(),
externalDocs.getFileURL(),
externalDocs.getGroupID(),
externalDocumentId,
externalDocs.getE2bExchangeId(),
externalDocs.getE2B_PARTNER_ID(),
externalDocs.getE2B_MESSAGENUMB()
});
} catch (Exception e) {
log.info("LAU_EXTERNAL_DOCUMENTS insert failed: " + e.getMessage());
log.error(e);
throw e;
}
return id;
}
@Override
public Object call() throws Exception {
try {
// Step 1
/*
* LOAD_AND_SPLIT(P_FILENAME IN CLOB, P_SPLIT varchar2 default 'Y',
* P_FILE varchar2 default NULL, //new P_ERR_MSG OUT varchar2, P_MSG
* OUT varchar2, P_TASK_ID_OUT OUT NUMBER, P_REF_E2B_MESSAGE_OUT OUT
* VARCHAR2, P_USER_GROUP_OUT OUT VARCHAR2, //new P_USER_ID
* varchar2, P_DISPLAY_OUTPUT VARCHAR2 DEFAULT 'N' ) return
* varchar2;
*/
String pRefE2bMsgOut = "";
String errorMsgOut = "";
String pTaskId = "";
String pMsg = "";
String userGroup = "";
String userId = "primo";
Connection con = null;
con = ds.getConnection();
log.info("Before auto commit: " + con.getAutoCommit());
con.setAutoCommit(false);
log.info("After auto commit: " + con.getAutoCommit());
String sql = "{call ? := LAU_E2B_INBOUND.LOAD_AND_SPLIT(?,?,?,?,?,?,?,?,?,?)}"; // TOTAL
// 11
// parameters
// including
// the
// return
// value
CallableStatement stmt = con.prepareCall(sql);
stmt.registerOutParameter(1, OracleTypes.VARCHAR);
log.info(
"File Name: "
+ ReadConfig.getMessage().getString("folderWatchLocation")
+ "\\"
+ fileName);
String fileContents =
getFileContents(
new File(ReadConfig.getMessage().getString("folderWatchLocation") + "\\" + fileName));
stmt.setCharacterStream(
2, new StringReader(fileContents), fileContents.length()); // P_FILENAME CLOB
stmt.setString(3, "Y"); // P_SPLIT
// stmt.setCharacterStream(4, new
// StringReader(fileName),fileName.length());
stmt.setString(4, fileName); // P_FILE FILE NAME
stmt.registerOutParameter(5, OracleTypes.VARCHAR); // P_ERR_MSG
stmt.registerOutParameter(6, OracleTypes.VARCHAR); // P_MSG
stmt.registerOutParameter(7, OracleTypes.NUMBER); // P_TASK_ID_OUT
stmt.registerOutParameter(8, OracleTypes.VARCHAR); // P_REF_E2B_MESSAGE_OUT
stmt.registerOutParameter(9, OracleTypes.VARCHAR); // P_USER_GROUP_OUT
stmt.setString(10, userId); // P_USER_ID
stmt.setString(11, "N"); // P_DISPLAY_OUTPUT
stmt.execute();
// FUNCTION output
int update = stmt.getInt(1);
errorMsgOut = stmt.getString(5);
pMsg = stmt.getString(6);
pTaskId = stmt.getString(7);
pRefE2bMsgOut = stmt.getString(8);
userGroup = stmt.getString(9);
log.info("Return UPDATE: " + update);
log.info("Return P_MSG: " + pMsg);
log.info("Return P_TASK_ID_OUT: " + pTaskId);
log.info("Return P_REF_E2B_MESSAGE_OUT: " + pRefE2bMsgOut);
log.info("Return P_USER_GROUP_OUT: " + userGroup);
LauJobScheduler lauJobScheduler = new LauJobScheduler();
lauJobScheduler.setTHREAD_ID(jobName);
if (errorMsgOut != null) lauJobScheduler.setJOB_COMMENT(errorMsgOut);
stmt.close();
// update == 2 call import for each record
// p-multi cases parameter must be
// non sanofi - N
if (update > 0) {
// Update the Job Status to completed
lauJobScheduler.setSTATUS("C");
folderWatch.updateJobStatus(lauJobScheduler);
// Step 2
// if the FUNCTION returns success, get required details from
// LAU_E2B_EXCHANGE_HISTORY
sql =
"SELECT E2B_EXCHANGE_ID,E2B_XML_FILE_NAME,E2B_MESSAGE_TYPE,XML_CONTENT_CLOB,E2B_PARTNER_ID,E2B_MESSAGENUMB "
+ "FROM LAU_E2B_EXCHANGE_HISTORY WHERE TASK_ID = "
+ pTaskId
+ " AND E2B_MESSAGE_TYPE != 'MULTI_CASES' AND REF_E2B_MESSAGENUMB = '"
+ pRefE2bMsgOut
+ "'";
List<LauJobScheduler> results =
getJdbcTemplate()
.query(
sql,
new RowMapper<LauJobScheduler>() {
public LauJobScheduler mapRow(ResultSet rs, int i) throws SQLException {
LauJobScheduler jobScheduler = new LauJobScheduler();
jobScheduler.setE2B_EXCHANGE_ID(rs.getLong("E2B_EXCHANGE_ID"));
jobScheduler.setE2B_XML_FILE_NAME(rs.getString("E2B_XML_FILE_NAME"));
jobScheduler.setE2B_MESSAGE_TYPE(rs.getString("E2B_MESSAGE_TYPE"));
jobScheduler.setXML_CONTENT_CLOB(
clobToString(rs.getClob("XML_CONTENT_CLOB")));
jobScheduler.setE2B_PARTNER_ID(rs.getString("E2B_PARTNER_ID"));
jobScheduler.setE2B_MESSAGENUMB(rs.getString("E2B_MESSAGENUMB"));
return jobScheduler;
}
});
// Iterate through results
int cntrDoc = 1;
for (LauJobScheduler jobScheduler : results) {
log.info(jobScheduler.getE2B_EXCHANGE_ID() + " ---");
log.info(jobScheduler.getE2B_XML_FILE_NAME() + " ---");
log.info(jobScheduler.getE2B_MESSAGE_TYPE() + " ---");
// log.info(jobScheduler.getXML_CONTENT_CLOB() + " ---");
if (update == 1) { // 1 - Manual Import
// Step 3
// Create RTF from XML file contents
InputStream is =
new ByteArrayInputStream(jobScheduler.getXML_CONTENT_CLOB().getBytes());
StreamSource source = new StreamSource(new InputStreamReader(is));
StreamSource transformSource =
new StreamSource(E2BFileOutput.class.getResourceAsStream("/E2Bstyle.xsl"));
ByteArrayOutputStream baos = new ByteArrayOutputStream();
Result result = new StreamResult(baos);
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer = tFactory.newTransformer(transformSource);
transformer.transform(source, result);
// Step 4
// SharePoint file upload
String sharePointTitle = "";
if (jobScheduler.getE2B_MESSAGE_TYPE().equalsIgnoreCase("FRAGMENT")) {
sharePointTitle =
jobScheduler
.getE2B_XML_FILE_NAME()
.substring(0, jobScheduler.getE2B_XML_FILE_NAME().lastIndexOf("."))
+ "-"
+ cntrDoc
+ ".rtf";
} else {
sharePointTitle =
jobScheduler
.getE2B_XML_FILE_NAME()
.substring(0, jobScheduler.getE2B_XML_FILE_NAME().lastIndexOf("."))
+ ".rtf";
}
SPResponse sPResponse =
GroupInboxService.uploadToGroupInbox(userGroup, sharePointTitle, baos);
cntrDoc++;
// If true, file uploaded to SharePoint
if (sPResponse.getSuccess().length() > 0) {
if (Boolean.valueOf(sPResponse.getSuccess())) {
log.info("Success " + Boolean.valueOf(sPResponse.getSuccess()));
log.info("sPResponse.getFileId() -> " + sPResponse.getFileId());
log.info("sPResponse.getMessage() -> " + sPResponse.getMessage());
log.info("sPResponse.getFileName() -> " + sPResponse.getFileName());
log.info("sPResponse.getFileUrl() -> " + sPResponse.getFileUrl());
// Step 5
// insert to LAU_EXTERNAL_DOCUMENTS
ExternalDocuments externalDocs = new ExternalDocuments();
externalDocs.setStatus("New");
externalDocs.setUser(ReadConfig.getMessage().getString("systemUser"));
externalDocs.setDstamp(CommonDAO.getTimestamp(getJdbcTemplate()));
externalDocs.setDocumentName(sPResponse.getFileName());
externalDocs.setDocumentTitle(sharePointTitle);
externalDocs.setRejectReason("");
externalDocs.setDocDescription(
"File Uploaded By " + ReadConfig.getMessage().getString("systemUser"));
externalDocs.setObjectID(sPResponse.getFileId());
externalDocs.setFileID(sPResponse.getFileId());
externalDocs.setFileURL(sPResponse.getFileUrl());
externalDocs.setGroupID(userGroup);
externalDocs.setE2bExchangeId(jobScheduler.getE2B_EXCHANGE_ID());
externalDocs.setE2B_PARTNER_ID(jobScheduler.getE2B_PARTNER_ID());
externalDocs.setE2B_MESSAGENUMB(jobScheduler.getE2B_MESSAGENUMB());
int rtn = insertDocumentStatus(externalDocs);
log.info("Return from insertDocumentStatus(externalDocs) -> " + rtn);
}
}
} else if (update == 2) // import each message
{
importE2bData(userId, Long.toString(jobScheduler.getE2B_EXCHANGE_ID()));
}
}
boolean fileDeleteStatus =
folderWatch.fileDelete(
new File(
ReadConfig.getMessage().getString("folderWatchLocation") + "\\" + fileName));
log.info("File delete status: " + fileDeleteStatus);
} else {
// Update the Job Status to error
lauJobScheduler.setSTATUS("E");
folderWatch.updateJobStatus(lauJobScheduler);
}
} catch (Exception e) {
log.error(e);
// Update the Job Status to error
LauJobScheduler lauJobScheduler = new LauJobScheduler();
lauJobScheduler.setSTATUS("E");
lauJobScheduler.setJOB_COMMENT(e.toString());
folderWatch.updateJobStatus(lauJobScheduler);
}
return null;
}
public String save(HttpServletRequest request, SimpleJdbcTemplate template, Timestamp dstamp) {
userId = CommonDAO.getUSERID(request);
String success = "";
try {
String resultsetname = "";
String resultdesc = "";
String reportids = "";
String repFlag = "";
String resultPermission = "";
String createUserId = "";
String resultSetID = "";
String transactionType = "";
String queryId = "";
String resultSetMode = "";
resultsetname = request.getParameter("resultName");
resultdesc = request.getParameter("resultDesc");
reportids = request.getParameter("reprtIds");
resultPermission = request.getParameter("resultPermn");
repFlag = request.getParameter("reportPickedFlag");
createUserId = request.getParameter("createUserID");
transactionType = request.getParameter("transactionType");
resultSetID = request.getParameter("resultSetID");
String TT = String.valueOf(IReportsConstants.deleteFlag);
queryId = request.getParameter("queryID");
resultSetMode = request.getParameter("resultSetMode");
String resultCharts = "charts";
log.info("queryId: " + queryId);
if (queryId == null) {
log.info("queryID set to null");
queryId = "";
}
log.info("transactiontype: " + transactionType);
if (transactionType.equals(TT)) { // DELETE
log.info("DELETE*****************");
success = deleteData(resultSetID, template);
} else { // INSERT
log.info("resultSetID------>" + resultSetID);
if (transactionType.equals(String.valueOf(1))) {
log.info("INSERT*******************");
Boolean namePrsnt = checkNameExists(template, resultsetname);
if (namePrsnt == false) {
if (resultSetMode.equals("charts")) {
success =
insertChartData(
resultsetname,
resultdesc,
resultPermission,
repFlag,
queryId,
template,
dstamp);
} else {
success =
insertData(
resultsetname,
resultdesc,
resultPermission,
reportids,
repFlag,
queryId,
template,
dstamp);
}
} else {
success = "exists";
}
} else {
log.info("UPDATE*******************");
success =
updateData(
resultSetID,
resultsetname,
resultdesc,
resultPermission,
reportids,
repFlag,
queryId,
template,
dstamp);
}
}
} catch (Exception e) {
log.info(e);
}
return success;
}
public String updateData(
String resultSetID,
String resultsetname,
String resultdesc,
String resultPermission,
String reportids,
String repFlag,
String queryID,
SimpleJdbcTemplate template,
Timestamp dstamp)
throws Exception {
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ApplicationContext ctx = AppContext.getApplicationContext();
com.nrg.lau.dao.SharedConnectionDAO shConnection =
(com.nrg.lau.dao.SharedConnectionDAO) ctx.getBean("getSharedConnection");
Connection con = shConnection.getConVerify();
String success = "";
// queryID = "";
try {
int idchild = 0;
String sqlParent =
"UPDATE LAU_QUERY_RESULT_SETS SET QUERY_ID=?,RESULT_SET_NAME=?,"
+ "RESULT_SET_DESCRIPTION=?,CREATE_USER_ID=?, CREATE_TIMESTAMP=?, UPDATE_USER_ID=?, UPDATE_TIMESTAMP=?, RESULT_SET_PERMISSION=? WHERE QUERY_RESULT_SET_ID=?";
log.info("queryRsltID------------" + resultSetID);
log.info("queryID----------------" + queryID);
log.info("resultsetname----------" + resultsetname);
log.info("resultdesc-------------" + resultdesc);
log.info("resultPermission-------------" + resultPermission);
log.info("userId-----------------" + userId);
log.info("dt---------------------" + dstamp);
ps1 = con.prepareStatement(sqlParent);
ps1.setString(1, queryID);
ps1.setString(2, resultsetname);
ps1.setString(3, resultdesc);
ps1.setString(4, userId);
ps1.setObject(5, dstamp);
ps1.setString(6, userId);
ps1.setObject(7, dstamp);
ps1.setString(8, resultPermission);
ps1.setString(9, resultSetID);
ps1.addBatch();
CommonDAO.setDbmsClientInfo(template, userId);
log.info("******************DELETING Saved ResultSet");
idchild =
template.update(
"DELETE FROM LAU_SAVED_QUERY_RESULTS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);
log.info("LauSaveResultSetDAO delete() ID Child -> " + idchild);
// INSERTING THE SavedResultSet Content
ps2 = con.prepareStatement(sqlChild);
String strReportIds = reportids;
String[] reportIdArray = strReportIds.split(",");
for (int i = 0; i < reportIdArray.length; i++) {
String rprtId = reportIdArray[i];
Long rptId = Long.valueOf(rprtId);
// log.info(" rptId-----------"+rptId);
ps2.setString(1, resultSetID);
ps2.setLong(2, rptId);
ps2.setString(3, repFlag);
ps2.setString(4, userId);
ps2.setObject(5, dstamp);
ps2.addBatch();
}
log.info(" Start Batch execute------------------------------------------------");
int[] rtn1 = ps1.executeBatch();
ps1.close();
log.info(" END executeBatch 1------------------------------------------------:");
log.info(" START executeBatch Child------------------------------------------------:");
int[] rtn2 = ps2.executeBatch();
log.info(" Update length---------" + rtn2.length);
log.info(" Update data##### " + rtn2[0]);
success = Integer.toString(rtn2[0]);
ps2.close();
log.info(" END executeBatch 2Child------------------------------------------------:");
} catch (Exception e) {
log.error("IN --" + e);
con.rollback();
log.error("IN -- Rollback---");
throw new Exception(e);
} finally {
try {
try {
if (ps2 != null) ps2.close();
} catch (Exception e) {
log.error(e, e);
}
} catch (Exception e) {
log.error("Error -- " + e);
}
}
return success;
}
public String insertData(
String resultsetname,
String resultdesc,
String resultPermission,
String reportids,
String repFlag,
String queryID,
SimpleJdbcTemplate template,
Timestamp dstamp)
throws Exception {
log.info("insert----> ");
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ApplicationContext ctx = AppContext.getApplicationContext();
com.nrg.lau.dao.SharedConnectionDAO shConnection =
(com.nrg.lau.dao.SharedConnectionDAO) ctx.getBean("getSharedConnection");
Connection con = shConnection.getConVerify();
String success = "";
// queryID = "";
try {
long queryRsltID = CommonDAO.getUniqueId(template);
log.info("Generated Primary Key for save resultset -> " + queryRsltID);
log.info("queryRsltID------------" + queryRsltID);
log.info("queryID----------------" + queryID);
log.info("resultsetname----------" + resultsetname);
log.info("resultdesc-------------" + resultdesc);
log.info("resultPermission-------------" + resultPermission);
log.info("userId-----------------" + userId);
log.info("dt---------------------" + dstamp);
log.info("queryRsltID------------" + queryRsltID);
ps1 = con.prepareStatement(sqlParent);
ps1.setLong(1, queryRsltID);
ps1.setString(2, "");
ps1.setString(3, resultsetname);
ps1.setString(4, resultdesc);
ps1.setString(5, userId);
ps1.setObject(6, dstamp);
ps1.setString(7, userId);
ps1.setObject(8, dstamp);
ps1.setString(9, resultPermission);
ps1.addBatch();
ps2 = con.prepareStatement(sqlChild);
String strReportIds = reportids;
String[] reportIdArray = strReportIds.split(",");
for (int i = 0; i < reportIdArray.length; i++) {
String rprtId = reportIdArray[i];
Long rptId = Long.valueOf(rprtId);
// log.info(" rptId-----------"+rptId);
ps2.setLong(1, queryRsltID);
ps2.setLong(2, rptId);
ps2.setString(3, repFlag);
ps2.setString(4, userId);
ps2.setObject(5, dstamp);
ps2.addBatch();
}
log.info(" Start Batch execute------------------------------------------------");
int[] rtn1 = ps1.executeBatch();
log.info(" rtn1---------" + rtn1);
ps1.close();
log.info(" END executeBatch 1------------------------------------------------:");
int[] rtn2 = ps2.executeBatch();
log.info(" rtn2 length---------" + rtn2.length);
log.info(" rtn2 data##### " + rtn2[0]);
success = Integer.toString(rtn2[0]);
ps2.close();
log.info(" END executeBatch 2------------------------------------------------:");
con.commit();
} catch (Exception e) {
log.error("IN --" + e);
con.rollback();
log.error("IN -- Rollback---");
throw new Exception(e);
} finally {
try {
try {
if (ps1 != null) ps1.close();
} catch (Exception e) {
log.error(e, e);
}
try {
if (ps2 != null) ps2.close();
} catch (Exception e) {
log.error(e, e);
}
if (con != null) con.close();
} catch (Exception e) {
log.error("Error -- " + e);
}
}
return success;
}