public Vector getUsacFiles(String invoice) {
Vector fileList = new Vector();
String query = "";
query =
query
+ " SELECT distinct filename, ' Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
query = query + " FROM stage_usac_form ";
query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
query = query + " ORDER BY usac_prcs_dat ";
USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = cConn.prepareStatement(query);
pstmt.setString(1, invoice);
rs = pstmt.executeQuery();
while (rs.next()) {
fileList.addElement(rs.getString(1));
fileList.addElement(rs.getString(2));
}
// rs.close();
// stmt.close();
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
}
USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
}
/* try
{
if( rs != null)
rs.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( pstmt != null)
pstmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return fileList;
}
private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
/** Sends Emails to Customers who have not submitted their Bears */
public static void BearEmailSendMessage(String msgsubject, String msgText, String msgTo) {
try {
BearFrom = props.getProperty("BEARFROM");
// To = props.getProperty("TO");
SMTPHost = props.getProperty("SMTPHOST");
Properties mailprops = new Properties();
mailprops.put("mail.smtp.host", SMTPHost);
// create some properties and get the default Session
Session session = Session.getDefaultInstance(mailprops, null);
// create a message
Message msg = new MimeMessage(session);
// set the from
InternetAddress from = new InternetAddress(BearFrom);
msg.setFrom(from);
InternetAddress[] address = InternetAddress.parse(msgTo);
msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(msgsubject);
msg.setContent(msgText, "text/plain");
Transport.send(msg);
} // end try
catch (MessagingException mex) {
USFEnv.getLog().writeCrit("Message not sent", null, null);
} catch (Exception ex) {
USFEnv.getLog().writeCrit("Message not sent", null, null);
}
} // end BearEmailSendMessage
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public boolean validateFRN(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public String getBpid() {
String query;
String bpid = "";
Statement stmt = null;
ResultSet rs = null;
query = "select bp_id_seq.nextval from dual ";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bpid = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return bpid;
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getInvno(String invid) {
String query;
String invno = "";
Statement stmt = null;
ResultSet rs = null;
query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invno = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return invno;
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public long getInvID(String inv_no) {
String query;
long invID = 0;
Statement stmt = null;
ResultSet rs = null;
query = "select inv_id from inv where inv_no=" + inv_no;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invID = rs.getLong(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return (invID);
}
/**
* This method searches the table stage_usac_form for the USAC File Name. It returns all the
* columns in the table.
*
* @param <code>String file</code> The USAC File Name
* @return Vector containing the Result Set
*/
public Vector searchStageUsacFormByFile(String file) {
// the buffer for the query
StringBuffer sbQuery = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append(buildSelectClause());
sbQuery.append(" where upper(rtrim(ltrim(FILENAME)))=upper('" + file + "')");
sbQuery.append(" order by FRN , SDC_INV_NO ");
// write to log if in debug mode
USFEnv.getLog()
.writeDebug(
"searchStageUsacFormByFile() " + "sbQuery is " + sbQuery.toString() + "\n",
this,
null);
} catch (Exception e) {
USFEnv.getLog().writeCrit("searchStageUsacFormByFile(): " + e.getMessage(), this, e);
}
return super.selectSQLCall(sbQuery.toString());
}
/**
* This method queries the database to get the SPIN associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public String getSPINname(String invid) {
String query;
String spinnm = "";
Statement stmt = null;
ResultSet rs = null;
// query = "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
query =
"select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
+ invid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
spinnm = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return spinnm;
}
/**
* This method queries the database to check if the passed start date for the Journal Month starts
* exactly after the Previous end date.
*
* @exception SQLException, if query fails
* @author
*/
public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
if (month.length() > ((new Integer("2")).intValue())) {
month = getMonth(month);
}
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn = ?";
} else {
query = query + " and bp_rgn is null";
}
query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";
query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";
USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
pstmt.setString(2, month);
pstmt.setString(3, blgsys);
int m = 4;
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(m, rgn);
m = m + 1;
}
pstmt.setString(m, month);
pstmt.setString(m + 1, year);
pstmt.setString(m + 2, year);
pstmt.setString(m + 3, strtdat);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
/**
* This servlet init method sets up business logic EJBs. Servlet init method is only executed once
*
* @param config - default parameter
* @exception ServletException
*/
public void init(ServletConfig config) throws ServletException {
super.init(config);
USFEnv.getLog().writeDebug("initializing buslogic EJB home interfaces", this, null);
try {
workorderdetHome = (WorkOrderDetailHome) USFEnv.ic.lookup(WORKORDERDETS_INFORMATION_EJBEAN);
} catch (NamingException e) {
USFEnv.getLog().writeDebug("Failed to ic.lookup Naming Exception", this, null);
}
}
/** Required Method Implementation */
public void ejbCreate() throws CreateException, RemoteException {
try {
// Data validation EJB
rhcccustValidateHome = (RhccCustomerHome) USFEnv.ic.lookup(RHCC_VALIDATION_EJBEAN);
rhcccustValidate = rhcccustValidateHome.create();
} catch (NamingException e) {
USFEnv.getLog().writeCrit("Inside Naming exception", this, e);
} catch (CreateException e) {
USFEnv.getLog().writeCrit("Inside Create exception", this, e);
} catch (RemoteException e) {
USFEnv.getLog().writeCrit("Inside Remote exception", this, e);
}
USFEnv.getLog().writeDebug("EJB Created, ref:" + this.toString(), this, null);
}
/**
* This method updates this object into the stage_usac_form table.
*
* @return boolean, true if update successful else false.
*/
public boolean updateStageUsacForm() {
// the buffer for the query
StringBuffer sbQuery = null;
try {
// init the buffer
sbQuery = new StringBuffer();
// build the query
sbQuery.append(buildUpdateClause());
sbQuery.append(" WHERE ");
sbQuery.append(" FRN = " + this.getFrn());
sbQuery.append(" AND SDC_INV_NO = '" + this.getSdcInvno() + "' ");
sbQuery.append(" AND HDR_SPIN = " + this.getHdrSpin());
sbQuery.append(" AND DSBRSMNT_TXT = '" + this.getDsbrsmntTxt() + "' ");
// update row - return true if update successful
if (super.doSQLOneRowCall(sbQuery.toString())) {
USFEnv.getLog()
.writeDebug(
"updateStageUsacForm(): SUCCESS. sbQuery is " + sbQuery.toString() + "\n",
this,
null);
return true;
} else {
USFEnv.getLog()
.writeDebug(
"searchStageUsacFormByInv(): Failure! sbQuery is " + sbQuery.toString() + "\n",
this,
null);
return false;
}
} catch (Exception e) {
USFEnv.getLog()
.writeCrit(
"updateStageUsacForm(long lQL>, long lStageUsacForm) "
+ "Unable to update a stage_usac_form record: "
+ e.getMessage()
+ " with Query: "
+ sbQuery.toString(),
this,
e);
return false;
}
// update was not successful, return false
// return false;
}
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
RhccInvviewHome invviewhome = (RhccInvviewHome) USFEnv.ic.lookup(RHCCINVVIEW_EJBEAN);
invviewEJBean = invviewhome.create();
RhccInvgenHome invgenhome = (RhccInvgenHome) USFEnv.ic.lookup(RHCCINVGEN_EJBEAN);
invgenEJBean = invgenhome.create();
} catch (NamingException e) {
USFEnv.getLog().writeDebug("RhccInvviewServlet: Inside Naming Exception", this, null);
} catch (CreateException e) {
USFEnv.getLog().writeDebug("RhccInvviewServlet: Inside Create Exception", this, null);
} catch (RemoteException e) {
USFEnv.getLog().writeDebug("RhccInvviewServlet: Inside Remote Exception", this, null);
}
}
/**
* This method pretty print the Class Object StageUsacForm
*
* @return String sbStr - Class Object StageUsacForm
*/
public String toString() {
// init the buffer
StringBuffer sbStr = new StringBuffer();
try {
sbStr.append("\n HDR_SPIN: " + HDR_SPIN);
sbStr.append("\n SPIN_NM: " + SPIN_NM);
sbStr.append("\n RCPNT_EMAIL: " + RCPNT_EMAIL);
sbStr.append("\n USAC_EMAIL: " + USAC_EMAIL);
sbStr.append("\n RFRNC_NMBR: " + RFRNC_NMBR);
sbStr.append("\n RCRD_CNT: " + RCRD_CNT);
sbStr.append("\n TOT_PAYMENT: " + TOT_PAYMENT);
sbStr.append("\n USAC_PRCS_DAT: " + USAC_PRCS_DAT);
sbStr.append("\n RTRCT_FLAG: " + RTRCT_FLAG);
sbStr.append("\n DTL_SPIN: " + DTL_SPIN);
sbStr.append("\n FRN: " + FRN);
sbStr.append("\n SDC_INV_NO: " + SDC_INV_NO);
sbStr.append("\n AMT_PAID: " + AMT_PAID);
sbStr.append("\n DSBRSMNT_TXT: " + DSBRSMNT_TXT);
sbStr.append("\n EMAIL_DATE: " + EMAIL_DATE);
sbStr.append("\n FILENAME: " + FILENAME);
sbStr.append("\n PROCESS_DATE: " + PROCESS_DATE);
sbStr.append("\n STATUS: " + STATUS);
} catch (Exception e) {
USFEnv.getLog()
.writeCrit(
"toString(): Unable to convert stage_usac_form to a string. " + e.getMessage(),
this,
e);
}
return sbStr.toString();
}
public List retrieveAttachmentsByCustomerId(long customerId) throws RemoteException {
List attachments = new ArrayList();
AttachmentDAO attachmentDAO = new AttachmentDAO(conn);
attachments = attachmentDAO.retrieveAttachmentsByCustomerId(customerId);
USFEnv.getLog().writeDebug("attachments size is......" + attachments.size(), this, null);
return attachments;
}
/**
* Method to build a select clause for all the columns in stage_usac_form table.
*
* @return String, the built select clause
*/
protected String buildSelectClauseRaw() {
USFEnv.getLog().writeDebug("INSIDE buildSelectClauseRaw()", this, null);
// the String to return
StringBuffer sbSelect = null;
// init the buffer
sbSelect = new StringBuffer("select ");
// build the query
// IMPORTANT STEP*****ROWID IS ALSO INCLUDED IN THE SELECT STATEMENT
sbSelect.append("ROWID, ");
sbSelect.append("nvl(HDR_SPIN,0) HDR_SPIN, ");
sbSelect.append("nvl(SPIN_NM,'') SPIN_NM, ");
sbSelect.append("nvl(RCPNT_EMAIL,'') RCPNT_EMAIL, ");
sbSelect.append("nvl(USAC_EMAIL,'') USAC_EMAIL, ");
sbSelect.append("nvl(RFRNC_NMBR,'') RFRNC_NMBR, ");
sbSelect.append("nvl(RCRD_CNT,0) RCRD_CNT, ");
sbSelect.append("nvl(TOT_PAYMENT,0) TOT_PAYMENT, ");
sbSelect.append("USAC_PRCS_DAT, ");
sbSelect.append("nvl(RTRCT_FLAG,'') RTRCT_FLAG, ");
sbSelect.append("nvl(DTL_SPIN,0) DTL_SPIN, ");
sbSelect.append("nvl(FRN,0) FRN, ");
sbSelect.append("nvl(SDC_INV_NO,'') SDC_INV_NO, ");
sbSelect.append("nvl(AMT_PAID,0) AMT_PAID, ");
sbSelect.append("nvl(DSBRSMNT_TXT,'') DSBRSMNT_TXT, ");
sbSelect.append("EMAIL_DATE, ");
sbSelect.append("nvl(FILENAME,'') FILENAME, ");
sbSelect.append("PROCESS_DATE, ");
sbSelect.append("nvl(STATUS,0) STATUS ");
sbSelect.append(" from stage_usac_form ");
return sbSelect.toString();
}
/**
* This method searches the table STAGE_USAC_FORM for the data provided for the status and
* startDate given
*
* @param <code>status</code> USAC record processing status
* @param <code>startDate</code> selection start Date
* @return Vector containing the Result Set
*/
public Vector getUsacErr(int status, java.sql.Date startDate) {
// the buffer for the query
StringBuffer sbQuery = null;
Vector rsVector = new Vector();
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append(buildSelectClauseRaw());
sbQuery.append(" WHERE ");
sbQuery.append(" status ");
if (status == 0) {
USFEnv.getLog()
.writeDebug("inside the if loop for checking status is null or not", this, null);
sbQuery.append(" is null");
} else {
sbQuery.append("= ");
sbQuery.append(status);
}
if (startDate != null) {
USFEnv.getLog()
.writeDebug("inside the if loop for checking startDate is null or not", this, null);
sbQuery.append(" AND ");
sbQuery.append(" usac_prcs_dat >= '");
sbQuery.append(startDate + "' ");
}
// order the result by filename, email date applic. number and frn
// CHANGE MADE - ORDERT BY USAC REFRENCE NUMBER, TOTAL PAYMENT AND FRN IN ADDITION
sbQuery.append(" ORDER BY rfrnc_nmbr, tot_payment, filename, frn, email_date, frn ASC ");
// write to log if in debug mode
USFEnv.getLog().writeDebug("Get USAC by error is: " + sbQuery.toString() + "\n", this, null);
rsVector = super.selectSQLCall(sbQuery.toString());
} catch (Exception e) {
USFEnv.getLog()
.writeCrit("Caught Exception in get USAC by status: " + e.getMessage(), this, e);
}
return (rsVector);
}
/**
* This calls the itemselection.jsp and list all items available for a WorkOrder and a WorkOrder's
* item list
*
* @param workorderid - the WO_ID of the parent WorkOrder
* @param request - servlet request
* @param response - servlet response
*/
private void listProducts(
long workorderId,
WorkOrderDetailRemote workorderdetEJBean,
HttpSession session,
HttpServletRequest req,
HttpServletResponse resp) {
try {
// If any product object is left over in session remove it
session.removeValue("itemObj");
// Create db connection for EJB
workorderdetEJBean.connect();
// Get the 2D Array which has the List of Items for the WorkOrder
// grouped by the Billing System.
Object[][] productList = workorderdetEJBean.getWorkOrderItems(workorderId);
// Get the WorkOrder Object
WorkOrder workorderObj = workorderdetEJBean.getWorkOrderInfo(workorderId);
// Get the List of all Product Names for the WorkOrder
String[] productNameList = workorderdetEJBean.getProdList(workorderId);
for (int w = 0; w < productNameList.length; w++)
USFEnv.getLog()
.writeDebug("VALUES INSIDE productNameList is" + productNameList[w], this, null);
// Set the attributes to the itemselection JSP
req.setAttribute("productNameList", productNameList);
req.setAttribute("productList", productList);
req.setAttribute("workorderObj", workorderObj);
// Release db connection for EJB
workorderdetEJBean.release();
// Include the JSP
includeJSP(req, resp, ITEM_JSP_PATH, "itemselection");
return;
} catch (Exception e) {
String errorMsg = "Fail to list products for a WORKORDER " + workorderId;
USFEnv.getLog().writeCrit(errorMsg, this, e);
errorJSP(req, resp, errorMsg);
}
}
/** This method used to connect to the Conection pool */
public void connect() throws ConnectException, RemoteException {
try {
conn = null;
conn = dbconn.open();
USFEnv.getLog().writeDebug("created db connection(), ref: " + conn.toString(), this, null);
} catch (Exception e) {
throw new ConnectException("Database connection failure");
}
}
/**
* Business unit of work This method is used to update an existing customer after editing
*
* @param customer details
* @return true or false whether the customer is updated or not
*/
public boolean updateCustomer(RhccCustInfos updateCustomer) throws RemoteException {
USFEnv.getLog().writeWarn(" Inside updateCustomer", this, null);
boolean bTmp = false;
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
bTmp = rhcc_obj.updateCustomerInfo(updateCustomer);
USFEnv.getLog()
.writeDebug("newCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return bTmp;
}
/**
* Business unit of work This method is used to view a particular selected Customer
*
* @param customer Id
* @return customer details
*/
public RhccCustInfos viewCustomer(String customerId) throws RemoteException {
RhccCustInfos cTmp = null;
USFEnv.getLog().writeWarn(" Inside viewCustomer", this, null);
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
cTmp = rhcc_obj.searchCustomerInfo(customerId);
USFEnv.getLog()
.writeDebug("viewCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return cTmp;
}
/**
* Business unit of work This method is used to delete an existing customer
*
* @param customer Id
* @return true or false whether the customer is deleted or not
*/
public boolean delCustomer(String customerId) throws RemoteException {
USFEnv.getLog().writeWarn(" Inside delCustomer", this, null);
boolean bTmp = false;
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
bTmp = rhcc_obj.deleteCustomer(Long.parseLong(customerId));
USFEnv.getLog()
.writeDebug("delCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return bTmp;
}
/**
* Business unit of work This method show all the RHCC customers for a funding year
*
* @param year - funding year
* @return vCustnmid - Customers for a Funding Year
*/
public Vector getAllRHCCCustomers(short year) throws RemoteException, NullPointerException {
Vector vCustnmid = null;
USFEnv.getLog().writeWarn("Inside getAllRHCCCustomers", this, null);
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos();
try {
vCustnmid = rhcc_obj.getAllRHCCCustomers(year);
USFEnv.getLog()
.writeDebug("getAllRHCCCustomers DB connection, ref: " + conn.toString(), this, null);
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return vCustnmid;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDuplicate(String blgsys, String year, String month, String rgn) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from blg_prd where bs_id_fk=" + blgsys + " and bp_year=" + year;
query = query + " and bp_month=" + month;
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn='" + rgn + "'";
} else {
query = query + " and bp_rgn is null";
}
USFEnv.getLog().writeDebug("Dinvjrnl:check Duplicate- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
pstmt.setString(2, year);
pstmt.setString(3, month);
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(4, rgn);
}
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Invoice Numbers list for a particular FRN in a
* Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getFrninvnos(String frn, String year) {
String query;
Vector invnos = new Vector();
Statement stmt = null;
ResultSet rs = null;
USFEnv.getLog().writeWarn("Inside the block-->>RhccDinview Inview", this, null);
query =
" select distinct rhcc_inv_id_fk,rhcc_inv.inv_no||' - '||to_char(rhcc_inv.inv_dat,'Mon/DD/YYYY') ";
query = query + " from wrk_ordr,wrk_ordr_dets,wo_det_hsties,rhcc_inv,fung_yr ";
query = query + " where wo_id=wo_id_fk and wod_id=wod_id_fk and ";
query = query + " wdh_stat='P' and ";
query = query + " inv_stat is not null and ";
query = query + " rhcc_inv_id_fk=rhcc_inv_id and ";
query = query + " wrk_ordr_no='" + frn + "' and ";
query = query + " wrk_ordr.yr_fk=fung_yr.yr and ";
query = query + " wrk_ordr.yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number List Query:" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
invnos.addElement(rs.getString(1));
invnos.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("RhccDinvview: The Invoice Numbers List for FRN not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return invnos;
}
