/**
* This clause update Status and Process Date
*
* @return String format of update statement
*/
protected String buildUpdateStatus() throws Exception {
// the String to return
StringBuffer sbUpdate = null;
// init the buffer
sbUpdate = new StringBuffer("update stage_usac_form set ");
// build the query
sbUpdate.append("STATUS = " + this.STATUS);
sbUpdate.append(", PROCESS_DATE = SYSDATE ");
return sbUpdate.toString();
}
/**
* Method to build a select clause for distinct filenames for a particular usac refrence number in
* stage_usac_form
*
* @return String, the built select clause
*/
protected String buildSelectFileName() {
USFEnv.getLog().writeDebug("INSIDE buildSelectFileName()", this, null);
// the String to return
StringBuffer sbSelect = null;
// init the buffer
sbSelect = new StringBuffer("select ");
// build the query
sbSelect.append("distinct(FILENAME) ");
sbSelect.append("from stage_usac_form ");
return sbSelect.toString();
}
/**
* 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 searches the table stage_usac_form for the SDC Invoice Number. It returns all the
* columns in the table.
*
* @param <code>String invoice</code> The SDC Invoice Number
* @return Vector containing the Result Set
*/
public Vector searchStageUsacFormByInv(String invoice) {
// the buffer for the query
StringBuffer sbQuery = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append(buildSelectClause());
sbQuery.append(" where rtrim(ltrim(SDC_INV_NO))='" + invoice + "'");
// write to log if in debug mode
USFEnv.getLog()
.writeDebug(
"searchStageUsacFormByInv() " + "sbQuery is " + sbQuery.toString() + "\n",
this,
null);
} catch (Exception e) {
USFEnv.getLog().writeCrit("searchStageUsacFormByInv(): " + e.getMessage(), this, e);
}
return super.selectSQLCall(sbQuery.toString());
}
// NEW METHOD TO UPDATE PROCESS DATE AND STATUS IN STAGE_USAC_FORM TABLE
public boolean updateStatus() {
// the buffer for the query
StringBuffer sbQuery = null;
try {
// init the buffer
sbQuery = new StringBuffer();
// build the query
sbQuery.append(buildUpdateStatus());
sbQuery.append(" WHERE ");
sbQuery.append(" ROWID = '" + this.ROWID + "' ");
if (super.doSQLOneRowCall(sbQuery.toString())) {
USFEnv.getLog()
.writeDebug(
"updateStatus(): SUCCESS. sbQuery is " + sbQuery.toString() + "\n", this, null);
return true;
} else {
USFEnv.getLog()
.writeDebug(
"updateStatus(): Failure! sbQuery is " + sbQuery.toString() + "\n", this, null);
return false;
}
} catch (Exception e) {
USFEnv.getLog()
.writeCrit(
"exception in updateStatus ) "
+ "Unable to update a stage_usac_form status and process date: "
+ e.getMessage()
+ " with Query: "
+ sbQuery.toString(),
this,
e);
return false;
}
}
/**
* 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 tables FRNS, FRN_DETS, FRN_DET_HSTIES for the presence of a record in
* a specific query. It returns the invoice line number.
*
* @param <code>StageUsacForm</code> The STAGE_USAC object.
* @return long containing the invoice line number.
*/
public long searchForAmount(java.sql.Connection conn) {
// the buffer for the query
StringBuffer sbQuery = null;
long returnAmt = 0;
// Vector invline=null;
PreparedStatement psStmt = null;
ResultSet rsResult = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append("SELECT max(INV_LN_NO) AS INV_LN_NO_MAX ");
sbQuery.append("FROM (SELECT sum(CRDT_AMT) AMT, INV_LN_NO ");
sbQuery.append("FROM FRNS, FRN_DETS, FRN_DET_HSTIES, INV ");
sbQuery.append("WHERE ");
sbQuery.append("FRN_ID=FRN_ID_FK AND FD_ID=FD_ID_FK AND INV_STAT='I' AND ");
sbQuery.append("INV_ID_FK=INV_ID");
// sbQuery.append(" AND FRN = ?'");
sbQuery.append(" AND FRN = ? ");
// sbQuery.append(this.getFrn());
// sbQuery.append("' GROUP BY INV_LN_NO) " );
sbQuery.append(" GROUP BY INV_LN_NO) ");
sbQuery.append(" WHERE AMT = ?");
// sbQuery.append(this.getAmtPaid());
// write to log if in debug mode
USFEnv.getLog()
.writeDebug(
"searchForAmount(StageUsacForm) " + "sbQuery is " + sbQuery.toString() + "\n",
this,
null);
psStmt = conn.prepareStatement(sbQuery.toString());
// psStmt.setLong(1,this.getFrn());
psStmt.setString(1, String.valueOf(this.getFrn()));
psStmt.setDouble(2, this.getAmtPaid());
rsResult = psStmt.executeQuery();
if (rsResult.next()) {
returnAmt = rsResult.getLong(1);
}
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (Exception e) {
try {
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (Exception ex) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, ex);
}
USFEnv.getLog()
.writeCrit("searchForAmount(StageUsacForm) Failed Query:" + sbQuery.toString(), this, e);
}
/* try
{
if( rsResult != null)
rsResult.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( psStmt != null)
psStmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return (returnAmt);
} // searchForAmount
/**
* 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;
}
/**
* Method to build a update clause for all the columns in stage_usac_form table.
*
* @return String, the built update clause
* @exception Throws Exception on error
*/
protected String buildUpdateClause() throws Exception {
// the String to return
StringBuffer sbUpdate = null;
// init the buffer
sbUpdate = new StringBuffer("update stage_usac_form set ");
// build the query
sbUpdate.append("HDR_SPIN = " + this.HDR_SPIN);
sbUpdate.append(", SPIN_NM = '" + this.SPIN_NM + "'");
sbUpdate.append(", RCPNT_EMAIL = '" + this.RCPNT_EMAIL + "'");
sbUpdate.append(", USAC_EMAIL = '" + this.USAC_EMAIL + "'");
sbUpdate.append(", RFRNC_NMBR = '" + this.RFRNC_NMBR + "'");
sbUpdate.append(", RCRD_CNT = " + this.RCRD_CNT);
sbUpdate.append(", TOT_PAYMENT = " + this.TOT_PAYMENT);
if (this.USAC_PRCS_DAT == null) {
sbUpdate.append(", USAC_PRCS_DAT = null");
} else {
sbUpdate.append(", USAC_PRCS_DAT = '" + this.USAC_PRCS_DAT + "'");
}
sbUpdate.append(", RTRCT_FLAG = '" + this.RTRCT_FLAG + "'");
sbUpdate.append(", DTL_SPIN = " + this.DTL_SPIN);
sbUpdate.append(", FRN = " + this.FRN);
sbUpdate.append(", SDC_INV_NO = '" + this.SDC_INV_NO + "'");
sbUpdate.append(", AMT_PAID = " + this.AMT_PAID);
sbUpdate.append(", DSBRSMNT_TXT = '" + this.DSBRSMNT_TXT + "'");
if (this.EMAIL_DATE == null) {
sbUpdate.append(", EMAIL_DATE = null");
} else {
sbUpdate.append(", EMAIL_DATE = '" + this.EMAIL_DATE + "'");
}
sbUpdate.append(", FILENAME = '" + this.FILENAME + "'");
if (this.PROCESS_DATE == null) {
sbUpdate.append(", PROCESS_DATE = null");
} else {
sbUpdate.append(", PROCESS_DATE = '" + this.PROCESS_DATE + "'");
}
sbUpdate.append(", STATUS = " + this.STATUS);
return sbUpdate.toString();
}
/**
* Method to build a select clause for all the columns in stage_usac_form table.
*
* @return String, the built select clause
*/
protected String buildSelectClause() {
// the String to return
StringBuffer sbSelect = null;
// init the buffer
sbSelect = new StringBuffer("select ");
// build the query
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(replace(DSBRSMNT_TXT,';','<BR>'),'') 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 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();
}
/**
* This method searches the table STAGE_USAC_FORM for the distinctfilename provided for the usac
* refrence number given
*
* @param Stringusac refrence number
* @return Vector containing the Result Set
*/
public Vector selectFileName(String rfrnc_nmbr) {
// the buffer for the query
StringBuffer sbQuery = null;
Vector rsVector = new Vector();
PreparedStatement psStmt = null;
ResultSet rsResult = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append(buildSelectFileName());
sbQuery.append(" WHERE ");
sbQuery.append(" rfrnc_nmbr = ?");
// sbQuery.append( rfrnc_nmbr );
// sbQuery.append("'");
USFEnv.getLog()
.writeDebug(
"Get FileName by USAC Ref Number is: " + sbQuery.toString() + "\n", this, null);
// execute the query
psStmt = cConn.prepareStatement(sbQuery.toString());
psStmt.setString(1, rfrnc_nmbr);
rsResult = psStmt.executeQuery();
if (rsResult != null) {
while (rsResult.next()) {
rsVector.addElement(rsResult.getString("FILENAME"));
USFEnv.getLog()
.writeDebug(" THE FILENAME IS : " + rsResult.getString("FILENAME"), this, null);
}
}
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException e) {
try {
// close the statment and the ResultSet
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
}
USFEnv.getLog()
.writeCrit(
" SQL Exception : SQL Error message "
+ e.getMessage()
+ " with Query: \n"
+ sbQuery.toString(),
this,
e);
} catch (Exception e) {
try {
// close the statment and the ResultSet
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
}
USFEnv.getLog()
.writeCrit(
"Error Executing the Query(): Error executing query " + e.getMessage(), this, e);
}
/* try
{
// close the statment and the ResultSet
if (rsResult != null)
rsResult.close();
if (psStmt != null)
psStmt.close();
}
catch (SQLException e)
{
USFEnv.getLog().writeCrit("selectSQLCall(): Fail to close result set or prepare statement",this, e);
}*/
return rsVector;
}
/**
* 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);
}