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;
}
/**
* This Method returns the 2D Array which is the list of the Items for the selected WorkOrder of
* the Customer.
*
* @param workorderId - WorkOrder Id Foreign Key of WRK_ORDR_DETS table
* @return Object[][] - 2D Array which is List of the Items for the WorkOrder
*/
public Object[][] getWorkOrderItems(long workorderId) throws RemoteException {
WrkOrdrDets wrkordrdts_obj = new WrkOrdrDets(conn);
RHCCBlgKeys blgkeys_obj = new RHCCBlgKeys(conn);
Vector workorderdetList = wrkordrdts_obj.getProductList(workorderId);
// Hashtable BSysList = (Hashtable) USFEnv.getBillSystems();
BlgSys blgsys = new BlgSys();
Hashtable BSysList = (Hashtable) blgsys.searchBlgSys();
Enumeration BSys = BSysList.keys();
// The 2-Dimensional array to hold the Items Billing System wise.
Object[][] prodList = new Object[BSysList.size()][workorderdetList.size() + 1];
// The Number of Billing Systems are assumed to be equal to the
// Static Load Billing Systems Hashtable size. The Billing Systems will
// be in order starting from 1 and incrementing by one.
for (int i = 0; i < BSysList.size(); i++) {
// Set the 2D array to store the Billing System as the first element
// of each row.
prodList[i][0] = String.valueOf(i + 1);
}
// Loop throught the WorkOrder Items List and place them in the appropriate
// positions in the 2D array.
for (int j = 0; j < workorderdetList.size(); j++) {
// Determine the Billing System of the Product
Vector tmpVector = new Vector();
WrkOrdrDets workorderObj = (WrkOrdrDets) workorderdetList.elementAt(j);
RHCCBlgKeys bkObj =
blgkeys_obj.searchRHCCBlgKeys(((WrkOrdrDets) workorderdetList.elementAt(j)).getRBKID());
int bsid = (new Long(bkObj.getBsId())).intValue();
tmpVector.addElement(bkObj);
tmpVector.addElement(workorderObj);
// Based on the Billing System Id retreived place the Product Object
// in the 2D array.
int k = 1;
while (prodList[bsid - 1][k] != null) {
k = k + 1;
}
prodList[bsid - 1][k] = tmpVector;
}
// Return the 2D array
return prodList;
}
/**
* 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 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;
}
/**
* Method to build a vector from a ResultSet
*
* @param <code>ResultSet rsResult</code> the result from a query
* @return Vector of objects from the ResultSet
* @exception Throws Exception on error
*/
protected Vector buildObjectVector(ResultSet rsResult) throws Exception {
// vector for return data
Vector vReturn = null;
try {
// init the vector
vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE);
// loop through the entire result set
while (rsResult.next()) {
// create a new one
StageUsacForm objTmp = new StageUsacForm();
// set the attributes
// ADDING A NEW FIELD "ROWID"
objTmp.ROWID = rsResult.getString("ROWID");
objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN");
objTmp.SPIN_NM = rsResult.getString("SPIN_NM");
objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL");
objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL");
objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR");
objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT");
objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT");
objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT");
objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG");
objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN");
objTmp.FRN = rsResult.getLong("FRN");
objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO");
objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID");
objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT");
objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE");
objTmp.FILENAME = rsResult.getString("FILENAME");
objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE");
objTmp.STATUS = rsResult.getLong("STATUS");
// add it to the vector
vReturn.addElement(objTmp);
}
return vReturn;
} catch (Exception e) {
throw new Exception("BuildObjectVector()\n" + e.getMessage());
}
}
/**
* 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;
}
/**
* This method queries the database to get the list of the Future Years.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getFutureyears() {
String query;
Vector years = new Vector();
Statement stmt = null;
ResultSet rs = null;
query = " select yr,yr ||'-'|| (yr+1) from fung_yr ";
query = query + "where yr >= to_number( to_char(sysdate,'YYYY') ) ";
query = query + "and yr not in ";
query = query + "( select yr from fung_yr where ";
query = query + "((sysdate between strt_dat and end_dat) or ";
query = query + "(sysdate-365 between strt_dat and end_dat) or ";
query = query + "(sysdate-730 between strt_dat and end_dat)) ) ";
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Future Years List 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);
}
}
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Vector size :" + years.size(), this, null);
return years;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBlgsys() {
String query;
Vector blgsys = new Vector();
Statement stmt = null;
ResultSet rs = null;
query =
"select distinct decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord,";
query =
query
+ " rtrim(bs_nm||' '||bp_rgn),bs_id_fk||rtrim(bp_rgn) from blg_sys,blg_prd where bs_id=bs_id_fk order by ord";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
blgsys.addElement(rs.getString(3));
blgsys.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System List 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);
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Billing System Vector size :" + blgsys.size(), this, null);
}
return blgsys;
}
/**
* 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;
}
public Vector getFiledetails(String frn, String invid, String year) {
String query;
Vector inv = new Vector();
Statement stmt = null;
ResultSet rs = null;
query = " SELECT rci_st, wrk_ordr_no, s.bs_nm, b.rbk_keys, rci_nm, wod_id, ";
query = query + " srv_sub_cat_nm, h.bill_dat, TO_CHAR (h.bill_dat, 'MM/DD/YYYY'), ";
query = query + " TO_CHAR (h.CRDT_AMT, 'FM999999990.00'), ";
query = query + " inv_stat invstat ";
query = query + " from ";
query = query + " rhcc_cust_infos, ";
query = query + " wo_det_hsties h, ";
query = query + " wrk_ordr_dets, ";
query = query + " wrk_ordr, ";
query = query + " rhcc_blg_keys b, ";
query = query + " srv_sub_cat, ";
query = query + " fung_yr, ";
query = query + " blg_sys s ";
query = query + " WHERE wo_id = wo_id_fk ";
query = query + " AND wod_id = wod_id_fk ";
query = query + " AND rci_id = rci_id_fk ";
query = query + " AND wdh_stat = 'P' ";
query = query + " AND b.bs_id_fk != 2 ";
query = query + " AND inv_stat IS NOT NULL ";
query = query + " AND ssc_id = ssc_id_fk ";
query = query + " AND wrk_ordr_dets.rbk_id_fk = b.rbk_id ";
query = query + " AND b.bs_id_fk = s.bs_id ";
query = query + " AND fung_yr.yr = wrk_ordr.yr_fk and ";
query = query + " fung_yr.yr=" + year + " and ";
if ((frn != null) && (!(frn).equals(""))) {
query = query + " wrk_ordr_no='" + frn + "' and ";
}
query = query + " rhcc_inv_id_fk=" + invid + " and ";
query = query + " wrk_ordr.sc_id_fk=5 ";
query = query + " UNION ALL ";
query = query + " select rci_st,wrk_ordr_no, s.bs_nm||'-'||substr(GETCONDITION( rm_rgn ";
query = query + " , ";
query = query + " substr(rbk_keys,1,3) ";
query = query + " , ";
query = query + " substr(rbk_keys,4,3) ";
query = query + " ) ";
query = query + " ,1,1),b.rbk_keys,rci_nm,wod_id,srv_sub_cat_nm,h.bill_dat, ";
query =
query
+ " to_char(h.bill_dat,'MM/DD/YYYY'),to_char(h.CRDT_AMT,'FM999999990.00'),inv_stat invstat ";
query = query + " from rhcc_cust_infos,wo_det_hsties h,wrk_ordr_dets,wrk_ordr, ";
query = query + " rhcc_blg_keys b,srv_sub_cat,rgn_map,fung_yr,blg_sys s ";
query = query + " where wo_id = wo_id_fk ";
query = query + " AND wod_id = wod_id_fk ";
query = query + " AND rci_id = rci_id_fk and ";
query = query + " wdh_stat='P' and ";
query = query + " b.bs_id_fk=2 and ";
query = query + " inv_stat is not null and ";
query = query + " ssc_id=ssc_id_fk and ";
query = query + " wrk_ordr_dets.RBK_ID_FK = b.RBK_ID AND ";
query = query + " b.bs_id_fk=s.bs_id and ";
query = query + " FUNG_YR.YR = wrk_ordr.YR_FK and ";
query = query + " fung_yr.yr=" + year + " and ";
if ((frn != null) && (!(frn).equals(""))) {
query = query + " wrk_ordr_no='" + frn + "' and ";
}
query = query + " rhcc_inv_id_fk=" + invid + " and ";
query = query + " wrk_ordr.sc_id_fk='5' and ";
query = query + " rm_npa = substr(b.rbk_keys,1,3) ";
USFEnv.getLog().writeDebug("rhccDinvview:Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
RhccDinvview linedet = new RhccDinvview(null);
linedet.strCuststate = rs.getString(1);
linedet.strFRN = rs.getString(2);
linedet.strBlgsys = rs.getString(3);
linedet.strBlgkey = rs.getString(4);
linedet.strCustnm = rs.getString(5);
linedet.strReconkey = rs.getString(6);
linedet.strProdnm = rs.getString(7);
linedet.strBdate = rs.getString(9);
linedet.strProdcost = rs.getString(10);
linedet.strInvstatus = rs.getString(11);
inv.addElement(linedet);
linedet = null;
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog()
.writeCrit("RhccDinvgen: The Details of the Line Item 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 inv;
}