asked on
need help in creating a cron process to this program to send emails daily from teusday thru saturday
I have this application that works fine just now i need to add a email cron funtion to it so it send out emails daily from teusday thru saturday to inform people any new cases added or not. if new cases are added then case id and case details need to be emailed out if no new cases are added then an email stating no cases were added will go out.
package com.comtrol.actions;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
import com.synfoserv.actions.Action;
import com.synfoserv.actions.ActionBase;
import com.synfoserv.actions.ActionRouter;
import com.synfoserv.actions.SUserAction;
import com.synfoserv.global.connections.*;
import com.synfoserv.global.security.*;
import com.synfoserv.global.util.Utils;
import com.synfoserv.beans.ContextBean;
import com.synfoserv.beans.PersonListBean;
import com.synfoserv.beans.SecurityBean;
import com.synfoserv.beans.SUserListBean;
import com.comtrol.global.IDGenerator;
import com.comtrol.beans.CaseListBean;
import com.comtrol.beans.CaseNoteListBean;
import com.comtrol.global.util.CookieManager;
public class CaseAction extends ActionBase implements Action{
private HttpServletResponse m_response = null;
private CookieManager cm = new CookieManager();
private HttpSession session = null;
private String msUsername = null;
private String msCaseId = null;
private boolean mbLimit = false;
private String msNotesCaseId = null;
private String msUserEmail = null;
private HttpServletRequest mRequest = null;
private String mOnlineCaseURL = "http://my.comtrol.com/support/case.asp?case=";
public CaseAction(){super();}
//adds WHERE or AND to the where clause
private StringBuffer addSyntax(StringBuffer sWhereClause){
if (sWhereClause.length()>0){sWhereClause.append(" AND ");}
else{sWhereClause.append(" WHERE ");}
return sWhereClause;
}
private void caseDelete(HttpServletRequest req){
try{
Utils utilObj = new Utils();
java.sql.Timestamp tsp = utilObj.currentTimestamp();
String sCaseId = (String)req.getParameter("casedelid");
cstmt = m_conDb.prepareCall("{call sp_case_delete(?,?,?)}");
cstmt.setInt(1,Integer.parseInt(sCaseId));
cstmt.setString(2,msUsername);
cstmt.setTimestamp(3,tsp);
cstmt.execute();
}
catch(Exception _e){_e.printStackTrace();}
}
private ArrayList caseEdit(HttpServletRequest req, boolean bOverride){
ArrayList alCaseList = new ArrayList();
ArrayList alCaseNoteList = new ArrayList();
HttpSession session = req.getSession();
ContextBean cBean = (ContextBean) session.getAttribute("context");
Utils utilObj = new Utils();
java.sql.Timestamp tsp = utilObj.currentTimestamp();
String sCaseId="0";
String sAssignto = "";
String sActionReq = "";
String sPriority = "";
String sOpenDate = "";
String sCloseDate = "";
String sStatus = "";
String sSummary = "";
String sNote = "";
String sPartNo = "";
String sOS = "";
String sDriverVer = "";
msNotesCaseId = null;
try{
//getting data from the request object'
if (bOverride){
if(msCaseId != null && !msCaseId.equals("0") && msCaseId.length() > 0){
sCaseId = msCaseId;
msCaseId = null;
}else if(cBean.getCaseId() != null && !cBean.getCaseId().equals("0") && cBean.getCaseId().length() > 0){
sCaseId = cBean.getCaseId();
}
}
else{
sCaseId = (String)req.getParameter("caseid");
msCaseId = null;
}
//for fetching case notes to display read only.
msNotesCaseId = sCaseId;
//IN -> CASE_ID,COMPANY_ID,DIVISION_ID,PERSON_ID
cstmt = m_conDb.prepareCall("{call sp_case_edit(?,?,?,?)}");
cstmt.setInt(1,Integer.parseInt(sCaseId));
cstmt.setInt(2,0);
cstmt.setInt(3,0);
cstmt.setInt(4,0);
cstmt.execute();
rs = cstmt.getResultSet();
if(rs.next()){
CaseListBean clbObj = getListBean(rs,true);
alCaseList.add(clbObj);
}
}
catch(Exception _e){_e.printStackTrace();}
return alCaseList;
}
private ArrayList getCaseNotesList(HttpServletRequest req){
ArrayList alCaseNoteList = new ArrayList();
HttpSession session = req.getSession();
String sCaseId = msNotesCaseId;
try{
if(sCaseId != null && !sCaseId.equals("0") && sCaseId.length() > 0){
cstmt = m_conDb.prepareCall("{call sp_case_note_edit(?,?)}");
cstmt.setInt(1,0);
cstmt.setInt(2,Integer.parseInt(sCaseId));
cstmt.execute();
rs = cstmt.getResultSet();
while(rs.next()){
CaseNoteListBean cnlbObj = new CaseNoteListBean();
cnlbObj.setCaseNoteId(Integer.toString(rs.getInt("CASE_NOTE_ID")));
cnlbObj.setCaseId(Integer.toString(rs.getInt("CASE_ID")));
cnlbObj.setSummary(utilObj.nullToEmpty(rs.getString("SUMMARY")));
cnlbObj.setNote(utilObj.nullToEmpty(rs.getString("NOTE")));
cnlbObj.setCreateDate(utilObj.formatDateTime(rs.getTimestamp("CREATE_DATE")));
cnlbObj.setCreateUser(utilObj.nullToEmpty(rs.getString("CREATE_USER")));
cnlbObj.setModifyDate(utilObj.formatDateTime(rs.getTimestamp("MODIFY_DATE")));
cnlbObj.setModifyUser(utilObj.nullToEmpty(rs.getString("MODIFY_USER")));
cnlbObj.setRespondTo(utilObj.nullToEmpty(rs.getString("RESPOND_TO")));
alCaseNoteList.add(cnlbObj);
}
}
}
catch(Exception _e){_e.printStackTrace();}
msNotesCaseId = null;
return alCaseNoteList;
}
private ArrayList caseInsert(HttpServletRequest req){
Utils ut = new Utils();
Timestamp ts = ut.currentTimestamp();
ArrayList alCaseList = new ArrayList();
CaseListBean clbObj = new CaseListBean();
clbObj.setOpenDate(ut.extractDate(ts));
clbObj.setAssignTo(msUsername);
clbObj.setActionReq("TECHSUP");
clbObj.setStatus("OPEN");
alCaseList.add(clbObj);
return alCaseList;
}
private void caseInsertSave(HttpServletRequest req){
ContextBean cBean = (ContextBean)session.getAttribute("context");
String sCompanyId = cBean.getCompanyId();
String sDivisionId = cBean.getDivisionId();
String sPersonId = cBean.getPersonId();
try{
Utils utilObj = new Utils();
java.sql.Timestamp tsp = utilObj.currentTimestamp();
//String sAssignToOriginal = req.getParameter("assigned_original"); will always be null
String sAssignto = (String)req.getParameter("assignto");
String sActionReq = (String)req.getParameter("actionrequired");
String sPriority = (String)req.getParameter("priority");
String sOpenDate = (String)req.getParameter("opendate");
String sCloseDate = (String)req.getParameter("closedate");
String sStatus = (String)req.getParameter("status");
String sSummary = (String)req.getParameter("summary");
String sNote = (String)req.getParameter("note");
String sPartNo = (String)req.getParameter("partno");
String sOS = (String)req.getParameter("opsys");
String sDriverVer = (String)req.getParameter("driverversion");
String sCaseType = req.getParameter("casetype");
StringBuffer sSQL = new StringBuffer();
sSQL.append("INSERT INTO CASE(");
sSQL.append("CASE_ID,COMPANY_ID,DIVISION_ID,PERSON_ID,ASSIGN_TO,ACTION_REQ,");
sSQL.append("PRIORITY,STATUS,PRODUCT,OS,OSDRIVERVER,OPEN_DATE,CLOSE_DATE,");
sSQL.append("SUMMARY,NOTE,CREATE_DATE,CREATE_USER,MODIFY_DATE,");
sSQL.append("MODIFY_USER,SYNC_DATE,SYNC_USER,ACTIVE,CASE_TYPE) ");
sSQL.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
pstmt = m_conDb.prepareStatement(sSQL.toString());
int iPrimaryKey = IDGenerator.getInstance().generateID(req,"case");
pstmt.setInt(1,iPrimaryKey);
pstmt.setInt(2,Integer.parseInt(sCompanyId));
if (sDivisionId==null || sDivisionId.length()==0){
pstmt.setInt(3, 0);
} else {
pstmt.setInt(3,Integer.parseInt(sDivisionId));
}
if (sPersonId==null || sPersonId.length()==0){
pstmt.setInt(4, 0);
} else {
pstmt.setInt(4,Integer.parseInt(sPersonId));
}
pstmt.setString(5,sAssignto);
pstmt.setString(6,sActionReq);
pstmt.setString(7,sPriority);
pstmt.setString(8,sStatus);
pstmt.setString(9,sPartNo.toUpperCase());
pstmt.setString(10,sOS.toUpperCase());
pstmt.setString(11,sDriverVer.toUpperCase());
pstmt.setString(12,sOpenDate);
pstmt.setString(13,sCloseDate);
pstmt.setString(14,sSummary);
pstmt.setString(15,sNote);
pstmt.setTimestamp(16,tsp);
pstmt.setString(17,msUsername);
pstmt.setTimestamp(18,tsp);
pstmt.setString(19,msUsername);
pstmt.setTimestamp(20,tsp);
pstmt.setString(21,msUsername);
pstmt.setString(22,"Y");
pstmt.setString(23,sCaseType);
pstmt.execute();
msCaseId = String.valueOf(iPrimaryKey);
if(!sAssignto.equalsIgnoreCase(msUsername)){
notifyAcctOwner("Indeo Alert: New Customer Case Created",
"New Case #: " + msCaseId + " was assigned to you.",sAssignto);
}
}
catch(Exception _e){
_e.printStackTrace();
}
}//end caseInsertSave()
private ArrayList caseSearch(HttpServletRequest req,boolean bOverride){
ArrayList alCaseList = new ArrayList();
HttpSession session = req.getSession();
ContextBean cBean = (ContextBean)session.getAttribute("context");
int iMaxRecordListing = Integer.parseInt(htConfig.get("max_record_listing").toString());
String sSQL = null;
String sCompanyId="0";
String sDivisionId="0";
String sPersonId="0";
String sCaseId="0";
String sCreatedBy = null;
String sModifiedBy = null;
String sCreatedStart = null;
String sCreatedEnd = null;
String sModifiedStart = null;
String sModifiedEnd = null;
String sTimeStamp = null;
if(bOverride & cBean != null){
sCompanyId = cBean.getCompanyId();
sDivisionId = cBean.getDivisionId();
sPersonId = cBean.getPersonId();
sCaseId = cBean.getCaseId();
if(sCompanyId == null || sCompanyId.length() <= 0){sCompanyId = "0";}
if(sDivisionId == null || sDivisionId.length() <= 0){sDivisionId = "0";}
if(sPersonId == null || sPersonId.length() <= 0){sPersonId = "0";}
if(sCaseId == null || sCaseId.length() <= 0){sCaseId = "0";}
}
String sFName = req.getParameter("fname");
String sLName = req.getParameter("lname");
String sComName = req.getParameter("company");
String sDivName = req.getParameter("division");
String sAssignto = req.getParameter("assignto");
String sActionReq = req.getParameter("actionrequired");
String sPriority = req.getParameter("priority");
String sOpenDate = req.getParameter("opendate");
String sCloseDate = req.getParameter("closedate");
String sStatus = req.getParameter("status");
String sSummary = req.getParameter("summary");
String sPartNo = req.getParameter("partno");
String sOS = req.getParameter("opsys");
String sDriverVer = req.getParameter("driverversion");
String sNote = req.getParameter("note");
sCreatedBy = req.getParameter("createdby");
sModifiedBy = req.getParameter("modifiedby");
sCreatedStart = req.getParameter("createstart");
sCreatedEnd = req.getParameter("createend");
sModifiedStart = req.getParameter("modifiedstart");
sModifiedEnd = req.getParameter("modifiedend");
try{
if(sAssignto != null && sAssignto.trim().length() > 0)
m_response.addCookie(new Cookie("caseassignto",sAssignto));
else if(cm.cookieExists(req,"caseassignto"))
m_response.addCookie(cm.deleteCookie(req,"caseassignto"));
if(sActionReq != null && sActionReq.trim().length() > 0)
m_response.addCookie(new Cookie("actionrequired",sActionReq));
else if(cm.cookieExists(req,"actionrequired"))
m_response.addCookie(cm.deleteCookie(req,"actionrequired"));
if(sPriority != null && sPriority.trim().length() > 0)
m_response.addCookie(new Cookie("priority",sPriority));
else if(cm.cookieExists(req,"priority"))
m_response.addCookie(cm.deleteCookie(req,"priority"));
if(sOpenDate != null && sOpenDate.trim().length() > 0)
m_response.addCookie(new Cookie("opendate",sOpenDate));
else if(cm.cookieExists(req,"opendate"))
m_response.addCookie(cm.deleteCookie(req,"opendate"));
if(sCloseDate != null && sCloseDate.trim().length() > 0)
m_response.addCookie(new Cookie("closedate",sCloseDate));
else if(cm.cookieExists(req,"closedate"))
m_response.addCookie(cm.deleteCookie(req,"closedate"));
if(sStatus != null && sStatus.trim().length() > 0)
m_response.addCookie(new Cookie("casestatus",sStatus));
else if(cm.cookieExists(req,"casestatus"))
m_response.addCookie(cm.deleteCookie(req,"casestatus"));
if(sSummary != null && sSummary.trim().length() > 0)
m_response.addCookie(new Cookie("casesummary",sSummary));
else if(cm.cookieExists(req,"casesummary"))
m_response.addCookie(cm.deleteCookie(req,"casesummary"));
if(sPartNo != null && sPartNo.trim().length() > 0)
m_response.addCookie(new Cookie("casepartno",sPartNo));
else if(cm.cookieExists(req,"casepartno"))
m_response.addCookie(cm.deleteCookie(req,"casepartno"));
if(sOS != null && sOS.trim().length() > 0)
m_response.addCookie(new Cookie("opsys",sOS));
else if(cm.cookieExists(req,"opsys"))
m_response.addCookie(cm.deleteCookie(req,"opsys"));
if(sDriverVer != null && sDriverVer.trim().length() > 0)
m_response.addCookie(new Cookie("driverversion",sDriverVer));
else if(cm.cookieExists(req,"driverversion"))
m_response.addCookie(cm.deleteCookie(req,"driverversion"));
if(sNote != null && sNote.trim().length() > 0)
m_response.addCookie(new Cookie("casenote",sNote));
else if(cm.cookieExists(req,"casenote"))
m_response.addCookie(cm.deleteCookie(req,"casenote"));
if(sCreatedBy != null && sCreatedBy.trim().length() > 0)
m_response.addCookie(new Cookie("casecreatedby",sCreatedBy));
else if(cm.cookieExists(req,"casecreatedby"))
m_response.addCookie(cm.deleteCookie(req,"casecreatedby"));
if(sModifiedBy != null && sModifiedBy.trim().length() > 0)
m_response.addCookie(new Cookie("casemodifiedby",sModifiedBy));
else if(cm.cookieExists(req,"casemodifiedby"))
m_response.addCookie(cm.deleteCookie(req,"casemodifiedby"));
if(sCreatedStart != null && sCreatedStart.trim().length() > 0)
m_response.addCookie(new Cookie("casecreatestart",sCreatedStart));
else if(cm.cookieExists(req,"casecreatestart"))
m_response.addCookie(cm.deleteCookie(req,"casecreatestart"));
if(sCreatedEnd != null && sCreatedEnd.trim().length() > 0)
m_response.addCookie(new Cookie("casecreateend",sCreatedEnd));
else if(cm.cookieExists(req,"casecreateend"))
m_response.addCookie(cm.deleteCookie(req,"casecreateend"));
if(sModifiedStart != null && sModifiedStart.trim().length() > 0)
m_response.addCookie(new Cookie("casemodifiedstart",sModifiedStart));
else if(cm.cookieExists(req,"casemodifiedstart"))
m_response.addCookie(cm.deleteCookie(req,"casemodifiedstart"));
if(sModifiedEnd != null && sModifiedEnd.trim().length() > 0)
m_response.addCookie(new Cookie("casemodifiedend",sModifiedEnd));
else if(cm.cookieExists(req,"casemodifiedend"))
m_response.addCookie(cm.deleteCookie(req,"casemodifiedend"));
}catch(Exception e){System.out.println(e.getMessage());}
StringBuffer sWhereClause = new StringBuffer();
/*
sWhereClause.append("SELECT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
sWhereClause.append("PERSON.FNAME,PERSON.LNAME ");
sWhereClause.append("FROM CASE C,COMPANY,DIVISION,PERSON,CASE_NOTE CN ");
sWhereClause.append("WHERE C.COMPANY_ID = COMPANY.COMPANY_ID ");
sWhereClause.append("AND C.DIVISION_ID = DIVISION.DIVISION_ID ");
sWhereClause.append("AND C.PERSON_ID = PERSON.PERSON_ID ");
sWhereClause.append("AND C.CASE_ID = CN.CASE_ID ");
*/
sWhereClause.append("SELECT DISTINCT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
sWhereClause.append("PERSON.FNAME,PERSON.LNAME FROM CASE C ");
sWhereClause.append("INNER JOIN COMPANY ON C.COMPANY_ID = COMPANY.COMPANY_ID ");
sWhereClause.append("LEFT JOIN DIVISION ON C.DIVISION_ID = DIVISION.DIVISION_ID ");
sWhereClause.append("LEFT JOIN PERSON ON C.PERSON_ID = PERSON.PERSON_ID WHERE ");
// sWhereClause.append("LEFT JOIN CASE_NOTE CN ON C.CASE_ID = CN.CASE_ID WHERE ");
sWhereClause.append(" C.CASE_ID!=0 AND C.ACTIVE = 'Y' ");
try{
if(m_conDb==null) System.out.println("ERROR: NO CONNECTION");
if(bOverride && !sCompanyId.equals("0")) {
cstmt = m_conDb.prepareCall("{call sp_case_edit(?,?,?,?)}");
cstmt.setInt(1,Integer.parseInt(sCaseId));
cstmt.setInt(2,Integer.parseInt(sCompanyId));
cstmt.setInt(3,Integer.parseInt(sDivisionId));
cstmt.setInt(4,Integer.parseInt(sPersonId));
cstmt.execute();
rs = cstmt.getResultSet();
while(rs.next()){
CaseListBean clbObj = getListBean(rs,false);
clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
alCaseList.add(clbObj);
}
}else{
boolean blnIsLimitedSearch = false;
sCaseId = (String)req.getParameter("caseid");
if(sCaseId != null && sCaseId.trim().length() > 0 && !sCaseId.trim().equalsIgnoreCase("0"))
m_response.addCookie(new Cookie("caseid",sCaseId));
else if(cm.cookieExists(req,"caseid"))
m_response.addCookie(cm.deleteCookie(req,"caseid"));
if(sCaseId != null && sCaseId.length() > 0 && !sCaseId.equals("0")) {
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.CASE_ID = " + sCaseId);
}
if(mbLimit){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.ASSIGN_TO = '" + msUsername + "'");
}else if(sAssignto != null && sAssignto.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.ASSIGN_TO = '" + sAssignto + "'");
}
if(sActionReq != null && sActionReq.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.ACTION_REQ = '" + sActionReq + "'");
}
if(sPriority != null && sPriority.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.PRIORITY = '" + sPriority + "'");
}
if(sOpenDate != null && sOpenDate.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.OPEN_DATE = '" + sOpenDate + "'");
}
if(sCloseDate != null && sCloseDate.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.CLOSE_DATE = '" + sCloseDate + "'");
}
if(sStatus != null && sStatus.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.STATUS = '" + sStatus + "'");
}
/*
if(sComName != null && sComName.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(COM_NAME) like '%"+sComName.toUpperCase()+"%'");
}
if(sDivName != null && sDivName.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(DIV_NAME) like '%" + sDivName.toUpperCase() + "%'");
}
*/
/*
if(sFName != null && sFName.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(FNAME) like '%" + sFName.toUpperCase() + "%'");
}
if(sLName != null && sLName.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(LNAME) like '%" + sLName.toUpperCase() + "%'");
}
*/
if(sPartNo != null && sPartNo.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.PRODUCT like '%" + sPartNo.toUpperCase() + "%'");
}
if(sOS != null && sOS.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.OS like '" + sOS.toUpperCase() + "%'");
}
if(sDriverVer != null && sDriverVer.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.OSDRIVERVER like '%" + sDriverVer.toUpperCase() + "%'");
}
if(sModifiedEnd != null && sModifiedEnd.length() > 0){
sModifiedEnd = utilObj.formatDateForStatement(sModifiedEnd);
sTimeStamp = sModifiedEnd.substring(1,(sModifiedEnd.length()-1)) + " 23:59:59.999";
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"') ");
// sWhereClause.append(" (C.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"')");
// sWhereClause.append(" OR CN.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"'))");
}
if(sCreatedEnd != null && sCreatedEnd.length() > 0){
sCreatedEnd = utilObj.formatDateForStatement(sCreatedEnd);
sTimeStamp = sCreatedEnd.substring(1,(sCreatedEnd.length()-1)) + " 23:59:59.999";
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"') ");
// sWhereClause.append(" (C.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"')");
// sWhereClause.append(" OR CN.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"'))");
}
if(sModifiedStart != null && sModifiedStart.length() > 0){
sModifiedStart = utilObj.formatDateForStatement(sModifiedStart);
sTimeStamp = sModifiedStart.substring(1,(sModifiedStart.length()-1)) + " 00:00:00.0";
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"') ");
// sWhereClause.append(" (C.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"')");
// sWhereClause.append(" OR CN.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"'))");
}
if(sCreatedStart != null && sCreatedStart.length() > 0){
sCreatedStart = utilObj.formatDateForStatement(sCreatedStart);
sTimeStamp = sCreatedStart.substring(1,(sCreatedStart.length()-1)) + " 00:00:00.0";
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"') ");
// sWhereClause.append(" (C.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"')");
// sWhereClause.append(" OR CN.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"'))");
}
if(sModifiedBy != null && sModifiedBy.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"' ");
// sWhereClause.append(" (C.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"'");
// sWhereClause.append(" OR CN.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"')");
}
if(sCreatedBy != null && sCreatedBy.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" C.CREATE_USER = '"+sCreatedBy.toLowerCase()+"' ");
// sWhereClause.append(" (C.CREATE_USER = '"+sCreatedBy.toLowerCase()+"'");
// sWhereClause.append(" OR CN.CREATE_USER = '"+sCreatedBy.toLowerCase()+"')");
}
if(sSummary != null && sSummary.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(C.SUMMARY) like '%" + sSummary.toUpperCase() + "%' ");
// sWhereClause.append(" (UPPER(C.SUMMARY) like '%" + sSummary.toUpperCase() + "%'");
// sWhereClause.append(" OR UPPER(CN.SUMMARY) like '%" + sSummary.toUpperCase() + "%')");
}
// query to complex to include ... need to find workaround
if(sNote != null && sNote.length() > 0){
blnIsLimitedSearch = true;
sWhereClause = addSyntax(sWhereClause);
sWhereClause.append(" UPPER(C.NOTE) like '%" + sNote.toUpperCase() + "%' ");
// sWhereClause.append(" (UPPER(C.NOTE) like '%" + sNote.toUpperCase() + "%'");
// sWhereClause.append(" OR UPPER(CN.NOTE) like '%" + sNote.toUpperCase() + "%')");
}
sWhereClause.append(" ORDER BY C.CASE_ID DESC, COM_NAME ASC, DIV_NAME ASC, FNAME ASC, LNAME ASC ");
sSQL = sWhereClause.toString();
// System.out.println(sSQL);
if(blnIsLimitedSearch){
if (stmt==null) stmt = m_conDb.createStatement();
stmt.execute(sSQL);
rs = stmt.getResultSet();
int iCounter = 0; //counts number of records
while(rs.next() & blnIsLimitedSearch){
CaseListBean clbObj = getListBean(rs,false);
clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
alCaseList.add(clbObj);
}
}
}
}
catch(Exception _e){
System.out.println(sSQL);
_e.printStackTrace();
}
return alCaseList;
}
private ArrayList caseMySearch(HttpServletRequest req){
ArrayList alCaseList = new ArrayList();
try{
StringBuffer sSQL = new StringBuffer();
sSQL.append("SELECT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
sSQL.append("PERSON.FNAME,PERSON.LNAME ");
sSQL.append("FROM CASE C,COMPANY,DIVISION,PERSON ");
sSQL.append("WHERE C.COMPANY_ID = COMPANY.COMPANY_ID ");
sSQL.append("AND C.DIVISION_ID = DIVISION.DIVISION_ID ");
sSQL.append("AND C.PERSON_ID = PERSON.PERSON_ID ");
sSQL.append("AND C.CASE_ID!=0 AND C.ACTIVE = 'Y' ");
sSQL.append("AND C.STATUS != 'CLOSED' AND C.ASSIGN_TO = '" + msUsername + "' ");
sSQL.append("ORDER BY C.CASE_ID DESC");
if(m_conDb==null) System.out.println("ERROR: NO CONNECTION");
if(stmt==null){stmt = m_conDb.createStatement();}
stmt.execute(sSQL.toString());
rs = stmt.getResultSet();
while(rs.next()){
CaseListBean clbObj = getListBean(rs,false);
clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
alCaseList.add(clbObj);
}
}
catch(Exception _e){_e.printStackTrace();}
return alCaseList;
}//caseMySearch()
private void caseUpdate(HttpServletRequest req){
ContextBean cBean = (ContextBean)session.getAttribute("context");
String sCompanyId = cBean.getCompanyId();
String sDivisionId = cBean.getDivisionId();
String sPersonId = cBean.getPersonId();
String sCaseId = cBean.getCaseId();
java.sql.Date dDate = null;
String sPPass = null;
String sPEmail = null;
boolean bValidDate = false;
try{
Utils utilObj = new Utils();
java.sql.Timestamp tsp = utilObj.currentTimestamp();
if(sCaseId == null || sCaseId.length() <= 0 || sCaseId.equals("0"))
sCaseId = (String)req.getParameter("caseid");
String sStatusOriginal = req.getParameter("status_original");
String sAssignToOriginal = req.getParameter("assigned_original");
String sAssignto = req.getParameter("assignto");
String sActionReq = req.getParameter("actionrequired");
String sPriority = req.getParameter("priority");
String sOpenDate = req.getParameter("opendate");
String sCloseDate = req.getParameter("closedate");
String sStatus = req.getParameter("status");
String sSummary = req.getParameter("summary");
String sNote = req.getParameter("note");
String sPartNo = req.getParameter("partno");
String sOS = req.getParameter("opsys");
String sDriverVer = req.getParameter("driverversion");
String sCaseType = req.getParameter("casetype");
StringBuffer sSQL = new StringBuffer();
sSQL.append("UPDATE CASE SET ");
sSQL.append("COMPANY_ID=?,DIVISION_ID=?,PERSON_ID=?,ASSIGN_TO=?,ACTION_REQ=?,");
sSQL.append("PRIORITY=?,STATUS=?,PRODUCT=?,OS=?,OSDRIVERVER=?,OPEN_DATE=?,CLOSE_DATE=?,");
sSQL.append("SUMMARY=?,NOTE=?,MODIFY_DATE=?,MODIFY_USER=?,SYNC_DATE=?,SYNC_USER=?,ACTIVE=?, ");
sSQL.append("CASE_TYPE=? WHERE CASE_ID=?");
pstmt = m_conDb.prepareStatement(sSQL.toString());
pstmt.setInt(1,Integer.parseInt(sCompanyId));
if(sDivisionId==null || sDivisionId.length()==0){pstmt.setInt(2, 0);}
else{pstmt.setInt(2,Integer.parseInt(sDivisionId));}
if(sPersonId==null || sPersonId.length()==0){pstmt.setInt(3, 0);}
else{pstmt.setInt(3,Integer.parseInt(sPersonId));}
pstmt.setString(4,sAssignto);
pstmt.setString(5,sActionReq);
pstmt.setString(6,sPriority);
pstmt.setString(7,sStatus);
pstmt.setString(8,sPartNo);
pstmt.setString(9,sOS);
pstmt.setString(10,sDriverVer);
pstmt.setString(11,sOpenDate);
pstmt.setString(12,sCloseDate);
pstmt.setString(13,sSummary);
pstmt.setString(14,sNote);
pstmt.setTimestamp(15,tsp);
pstmt.setString(16,msUsername);
pstmt.setTimestamp(17,tsp);
pstmt.setString(18,msUsername);
pstmt.setString(19,"Y");
pstmt.setString(20,sCaseType);
pstmt.setInt(21,Integer.parseInt(sCaseId));
pstmt.execute();
if(!sAssignToOriginal.equalsIgnoreCase(sAssignto)){
notifyAcctOwner("Indeo Alert: Customer Case Assignment",
"Case #: " + sCaseId + " is now assigned to you.",sAssignto);
}
if(sStatusOriginal != null && sStatusOriginal.length() > 0){
if(sStatus != null && (sStatus.equalsIgnoreCase("CLOSED") & sStatusOriginal.equals("OPEN"))){
stmt = m_conDb.createStatement();
rs = stmt.executeQuery("SELECT C.CREATE_DATE, P.EMAIL, P.PASSWORD FROM CASE C INNER JOIN " +
"PERSON P ON P.PERSON_ID = C.PERSON_ID WHERE C.CASE_ID = " +
sCaseId + " AND C.PERSON_ID = " + sPersonId);
if(rs.next()){
dDate = utilObj.getDateFromTS(rs.getTimestamp("CREATE_DATE"));
sPPass = utilObj.nullToEmpty(rs.getString("PASSWORD"));
sPEmail = utilObj.nullToEmpty(rs.getString("EMAIL"));
sPEmail = sPEmail.trim();
sPPass = sPPass.trim();
}
if(dDate != null && (dDate.compareTo(java.sql.Date.valueOf("2005-07-01")) >= 0)){
bValidDate = true;
}
if(bValidDate && sPPass != null && sPPass.length() >= 5 && sPEmail != null && sPEmail.length() > 0){
//System.out.println("Case status closed test for notifyCustomer() execute passed with values: " + sStatus + " and " + sStatusOriginal);
notifyCustomer("Case [#" + sCaseId + "]: Closed",
"Your technical support case #" + sCaseId + " is now closed.\n\n" +
"The case can be reopened at your convenience by visiting the link below. " +
"Should you decide to reopen the case please state the reason for doing so.\n\n" +
"Thank You\n\n" +
mOnlineCaseURL + sCaseId,sPersonId, msUserEmail);
}
}
}
}
catch(Exception _e){
_e.printStackTrace();
}
}
private ArrayList personCompanyList(HttpServletRequest req){
ArrayList alPerson = new ArrayList();
ArrayList alCaseList = caseEdit(req, false);
Iterator i = alCaseList.iterator();
CaseListBean clb = new CaseListBean();
String sPersonCompanyId = "";
if (i.hasNext()){
clb = (CaseListBean)i.next();
sPersonCompanyId = clb.getCompanyId();
String sSQL = "SELECT * FROM PERSON WHERE COMPANY_ID=" + sPersonCompanyId + " AND ACTIVE = 'Y' ORDER BY FNAME, LNAME";
try{
Statement stmtPeople = m_conDb.createStatement();
ResultSet _rs = stmtPeople.executeQuery(sSQL);
while (_rs.next()){
PersonListBean plbObj = new PersonListBean();
String sPersonId = _rs.getString("PERSON_ID");
plbObj.setPersonId(sPersonId);
String sCompanyId = _rs.getString("COMPANY_ID");
plbObj.setCompanyId(sCompanyId);
String sDivisionId = _rs.getString("DIVISION_ID");
plbObj.setDivisionId(sDivisionId);
String sFname = utilObj.nullToEmpty(_rs.getString("FNAME"));
plbObj.setFName(sFname);
String sLname = utilObj.nullToEmpty(_rs.getString("LNAME"));
plbObj.setLName(sLname);
plbObj.setTitle(utilObj.nullToEmpty(_rs.getString("TITLE")));
plbObj.setAddress1(utilObj.nullToEmpty(_rs.getString("ADDRESS1")));
plbObj.setAddress2(utilObj.nullToEmpty(_rs.getString("ADDRESS2")));
plbObj.setCity(utilObj.nullToEmpty(_rs.getString("CITY")));
plbObj.setState(utilObj.nullToEmpty(_rs.getString("STATE")));
plbObj.setCountry(utilObj.nullToEmpty(_rs.getString("COUNTRY")));
plbObj.setZip(utilObj.nullToEmpty(_rs.getString("ZIP")));
plbObj.setPhone(utilObj.nullToEmpty(_rs.getString("PHONE")));
plbObj.setExt(utilObj.nullToEmpty(_rs.getString("EXT")));
plbObj.setCellPhone(utilObj.nullToEmpty(_rs.getString("CELLPHONE")));
plbObj.setPager(utilObj.nullToEmpty(_rs.getString("PAGER")));
plbObj.setFax(utilObj.nullToEmpty(_rs.getString("FAX")));
plbObj.setEmail(utilObj.nullToEmpty(_rs.getString("EMAIL")));
plbObj.setNote(utilObj.nullToEmpty(_rs.getString("NOTE")));
plbObj.setUserName("");
//plbObj.setUserName(utilObj.nullToEmpty(_rs.getString("USERNAME")));
plbObj.setPassword(utilObj.nullToEmpty(_rs.getString("PASSWORD")));
plbObj.setCreateDate(utilObj.formatDateTime(_rs.getTimestamp("CREATE_DATE")));
plbObj.setCreateUser(utilObj.nullToEmpty(_rs.getString("CREATE_USER")));
plbObj.setModifyDate(utilObj.formatDateTime(_rs.getTimestamp("MODIFY_DATE")));
plbObj.setModifyUser(utilObj.nullToEmpty(_rs.getString("MODIFY_USER")));
plbObj.setFaxList(_rs.getString("FAX_LIST"));
plbObj.setCTitle(_rs.getString("PREFIX")); //Mr., Ms, etc.
String sComname = "";
plbObj.setComName(sComname);
String sDivname = "";
plbObj.setDivName(sDivname);
alPerson.add(plbObj);
}
}
catch(Exception e){
e.printStackTrace();
}
}
return alPerson;
}
private void changeContact(HttpServletRequest req){
String sCaseId = req.getParameter("caseid");
String sPersonId = req.getParameter("newperson");
String sSQL = "UPDATE CASE SET PERSON_ID = " + sPersonId + " WHERE CASE_ID = " + sCaseId;
try{
Statement stmtContact = m_conDb.createStatement();
stmtContact.execute(sSQL);
}
catch(Exception e){
e.printStackTrace();
}
}
//INDEO-1357, change email sender to Support Autoresponder (support@comtrol.com)
private void notifyCustomer(String sSubject, String sMsg, String sPersonId, String sTechEmail){
try{
EmailAction ea = new EmailAction();
String sPersonEmail = ea.getEmailFromPersonId(sPersonId,this.m_conDb);
try{
if(sPersonEmail != null && sPersonEmail.length() > 0){
boolean bOK = ea.processMailItem("support@comtrol.com", sPersonEmail, sSubject, sMsg, mRequest);
if(bOK){System.out.println("Case Closed Notification sent to " + sPersonEmail);}
else{System.out.println("Case Closed Notification to " + sPersonEmail + " failed.");}
}
}catch(Exception e){e.printStackTrace();}
ea = null;
}catch(Exception e){e.printStackTrace();}
}
private void notifyAcctOwner(String sSubject, String sMsg, String sOwner){
try{
SUserAction sua = new SUserAction();
SUserListBean sulb = sua.suserLookup(sOwner, m_conDb);
String sOwnerEmail = sulb.getEmail();
EmailAction ea = new EmailAction();
try{
boolean bOK = ea.processMailItem(msUserEmail, sOwnerEmail, sSubject, sMsg, mRequest);
if(bOK){System.out.println("Case Assignment Notification sent to " + sOwnerEmail);}
else{System.out.println("Case Assignment Notification to " + sOwnerEmail + " failed.");}
}catch(Exception e){e.printStackTrace();}
sulb = null;
ea = null;
}catch(Exception e){e.printStackTrace();}
}
private CaseListBean getListBean(ResultSet _rs, boolean bSetSession) throws Exception{
CaseListBean clbObj = new CaseListBean();
clbObj.setCaseUID(_rs.getString("CASE_UID"));
clbObj.setCaseId(Integer.toString(_rs.getInt("CASE_ID")));
String sCompanyId = Integer.toString(_rs.getInt("COMPANY_ID"));
clbObj.setCompanyId(sCompanyId);
String sDivisionId = Integer.toString(_rs.getInt("DIVISION_ID"));
clbObj.setDivisionId(sDivisionId);
String sPersonId = Integer.toString(_rs.getInt("PERSON_ID"));
clbObj.setPersonId(sPersonId);
clbObj.setAssignTo(utilObj.nullToEmpty(_rs.getString("ASSIGN_TO")));
clbObj.setActionReq(utilObj.nullToEmpty(_rs.getString("ACTION_REQ")));
clbObj.setPriority(utilObj.nullToEmpty(_rs.getString("PRIORITY")));
clbObj.setStatus(utilObj.nullToEmpty(_rs.getString("STATUS")));
clbObj.setProduct(utilObj.nullToEmpty(_rs.getString("PRODUCT")));
clbObj.setOS(utilObj.nullToEmpty(_rs.getString("OS")));
clbObj.setOSDriverVer(utilObj.nullToEmpty(_rs.getString("OSDRIVERVER")));
clbObj.setOpenDate(utilObj.nullToEmpty(_rs.getString("OPEN_DATE")));
clbObj.setCloseDate(utilObj.nullToEmpty(_rs.getString("CLOSE_DATE")));
clbObj.setSummary(utilObj.nullToEmpty(_rs.getString("SUMMARY")));
clbObj.setNote(utilObj.nullToEmpty(_rs.getString("NOTE")));
clbObj.setCreateDate(utilObj.formatDateTime(_rs.getTimestamp("CREATE_DATE")));
clbObj.setCreateUser(utilObj.nullToEmpty(_rs.getString("CREATE_USER")));
clbObj.setModifyDate(utilObj.formatDateTime(_rs.getTimestamp("MODIFY_DATE")));
clbObj.setModifyUser(utilObj.nullToEmpty(_rs.getString("MODIFY_USER")));
clbObj.setCaseType(utilObj.nullToEmpty(_rs.getString("CASE_TYPE")));
if (bSetSession){
String sComname = utilObj.nullToEmpty(_rs.getString("COM_NAME"));
clbObj.setComName(sComname);
String sDivname = utilObj.nullToEmpty(_rs.getString("DIV_NAME"));
clbObj.setDivName(sDivname);
String sFname = utilObj.nullToEmpty(_rs.getString("FNAME"));
clbObj.setFName(sFname);
String sLname = utilObj.nullToEmpty(_rs.getString("LNAME"));
clbObj.setLName(sLname);
//setting up context
ContextBean cBean = new ContextBean();
cBean.setPersonId(sPersonId);
cBean.setFName(sFname);
cBean.setLName(sLname);
cBean.setCompanyId(sCompanyId);
cBean.setComName(sComname);
cBean.setDivisionId(sDivisionId);
cBean.setDivName(sDivname);
cBean.setOpportunityId("0");
cBean.setQuoteId("0");
cBean.setOrderId("0");
cBean.setCaseId(clbObj.getCaseId());
cBean.setRmaId("0");
session.setAttribute("context",cBean);
}
return clbObj;
}
public ActionRouter perform(HttpServlet servlet, HttpServletRequest req, HttpServletResponse res) throws java.io.IOException, ServletException{
String sNextJsp = "/CaseList.jsp";
session=req.getSession();
UserBean ubObject = (UserBean)session.getAttribute("userbean"); //retrieving security user bean
msUsername = ubObject.getUserId();
msUserEmail = ubObject.getEMailAddress();
Hashtable htSecurity = ubObject.getSecurity();
mbLimit = ((SecurityBean)htSecurity.get("CASE")).getLimit();
mRequest = req;
m_response = res; //for search cookie management
try{
getConnection(servlet);
String sCmd = (String) req.getParameter("cmd");
if(sCmd.equals("case_list") || sCmd.equals("case_contextlist")){
boolean bOverride = false;
if(sCmd.equals("case_contextlist")){bOverride = true;}
req.setAttribute("listing",caseSearch(req,bOverride));
sNextJsp ="/CaseList.jsp";
}else if(sCmd.equals("case_edit") || sCmd.equals("case_contextedit")){
populateErrorFields(req);
boolean bOverride = false;
if(sCmd.equals("case_contextedit")){bOverride=true;}
req.setAttribute("listing",caseEdit(req,bOverride));
req.setAttribute("noteslisting",getCaseNotesList(req));
req.setAttribute("action","update");
sNextJsp ="/Case.jsp";
}else if(sCmd.equals("case_save")){
if(isValid(req)){
caseUpdate(req);
req.setAttribute("listing",caseEdit(req,false));
req.setAttribute("noteslisting",getCaseNotesList(req));
}
sNextJsp ="/Case.jsp";
}else if(sCmd.equals("case_insert")){
populateErrorFields(req);
req.setAttribute("listing",caseInsert(req));
sNextJsp ="/Case.jsp";
}else if(sCmd.equals("case_insertsave")){
if(isValid(req)){
caseInsertSave(req);
req.setAttribute("listing",caseEdit(req,true));
req.setAttribute("cmd","case_edit");
}else{
req.setAttribute("cmd","case_insert");
}
sNextJsp ="/Case.jsp";
}else if (sCmd.equals("case_delete")){
caseDelete(req);
req.setAttribute("listing",caseSearch(req,true));
sNextJsp ="/CaseList.jsp";
}else if(sCmd.equals("case_search")){
sNextJsp ="/CaseSearch.jsp";
}else if(sCmd.equals("case_mylist")){
boolean bOverride = false;
req.setAttribute("listing",caseMySearch(req));
sNextJsp ="/CaseList.jsp";
}else if(sCmd.equals("case_move")){
//Need current case and list of all people at company
req.setAttribute("listing", caseEdit(req, false));
req.setAttribute("people", personCompanyList(req));
sNextJsp="/CaseMove.jsp";
}else if(sCmd.equals("case_changecontact")){
changeContact(req);
req.setAttribute("listing",caseEdit(req,true));
req.setAttribute("noteslisting",getCaseNotesList(req));
req.setAttribute("action","update");
sNextJsp ="/Case.jsp";
}
}
catch(Exception _e){
_e.printStackTrace();
sNextJsp="/CaseList.jsp";
}
finally {
try{close();}
catch (Exception _e) {
_e.printStackTrace();
sNextJsp="/CaseList.jsp";
}
}
return new ActionRouter(sNextJsp);
}//end perform()
private boolean isValid(HttpServletRequest req){
boolean isValid=true;
/*
if (!validate(req,"detail_category","detailcat"))isValid=false;
if (!validate(req,"detail_data","detaildata"))isValid=false;
if (!isValid){
DetailListBean dlbObj = new DetailListBean();
dlbObj.setDetailId(req.getParameter("detailid"));
dlbObj.setCategory(req.getParameter("detailcat"));
ArrayList alDetailList = new ArrayList();
alDetailList.add(dlbObj);
req.setAttribute("listing",alDetailList);
}
*/
return isValid;
}
private void populateErrorFields(HttpServletRequest req){
/*
req.setAttribute("error"," "); //error message
req.setAttribute("detaildata_error","");
req.setAttribute("detailcat_error","");
req.setAttribute("note_error","");
*/
}//end populateErrorFields()
}
Java
Last Comment
Mick Barry