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;
}
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;
}
public String updateStatus(String new_status) {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq = "update monitors set status=? where id = ? ";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, new_status);
pstmt.setString(2, id);
pstmt.executeUpdate();
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
return back;
}
/**
* 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;
}
public String updateImport(PreparedStatement pstmt) {
String msg = "";
try {
int jj = 1;
if (device_id.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, device_id);
if (name.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, name);
if (serial_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, serial_num);
if (model.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, model);
if (type.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, type);
if (vertical_resolution.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, vertical_resolution);
if (horizontal_resolution.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, horizontal_resolution);
if (manufacturer.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, manufacturer);
pstmt.setString(jj++, external_id);
pstmt.executeUpdate();
} catch (Exception ex) {
msg += "update " + ex;
logger.error(external_id + " " + ex);
// System.err.println(id+" "+ex);
}
return msg;
}
public String doPartialUpdate() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq =
"update monitors set "
+ "asset_num=?, screen_size=?,received=?, expected_age=?, "
+ "notes=? "
+ "where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
int jj = 1;
if (asset_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, asset_num);
if (screen_size.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, screen_size);
if (received.equals("")) pstmt.setNull(jj++, Types.DATE);
else pstmt.setDate(jj++, new java.sql.Date(dateFormat.parse(received).getTime()));
pstmt.setString(jj++, "" + expected_age);
if (notes.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, notes);
pstmt.setString(6, id);
pstmt.executeUpdate();
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
if (back.equals("")) {
back = doSelect();
}
return back;
}
/**
* 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;
}
/**
* Ejecuta una sentencia SQL de insercion La sentencia requiere de parametros en tiempo de
* ejecuci�n.
*
* @param consultaSQL Cadena que contiene una sentencia de insercion SQL: INSERT into table values
* (valores)
* @param parametros Un Iterador de Parametros con los parametros de la insercion.
* @return falso o verdadero si pudo insertar
*/
protected boolean ejecutarActualizacionSQL(String comandoSQL, Iterator parametros)
throws Exception {
boolean ok;
if (this.conectar()) {
// La consulta es preparada porque requiere de parametros
// por ejemplo:
// delete from producto where precio=?
// insert into producto values (?,?,?,?,?)
// los ? indican parametros ordenados por posicion
PreparedStatement sql = this.conexión.prepareStatement(comandoSQL);
// System.err.println(comandoSQL+"-->");
for (int i = 1; parametros.hasNext(); i++) {
String parametro = parametros.next().toString();
sql.setString(i, parametro);
// System.err.println("param["+i+"]="+parametro);
}
ok = sql.executeUpdate() != 0;
// importante cerrar la conexión
sql.close();
sql = null;
this.desconectar();
return ok;
} else return false;
} // Fin ejecutarSQL
public String doDelete() {
String back = "", qq = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
qq = "delete from monitors where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, id);
pstmt.executeUpdate();
message = "Deleted Successfully";
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
/**
* 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;
}
public String doSelect() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String qq =
"select external_id,device_id,name,asset_num,"
+ " serial_num,screen_size,model,type,"
+ "vertical_resolution,horizontal_resolution,"
+ "manufacturer,"
+ "date_format(received,'%m/%d/%Y'),"
+ "expected_age,status,notes,editable "
+ " from monitors where id=?";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
} else {
try {
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
setExternal_id(rs.getString(1));
setDevice_id(rs.getString(2));
setName(rs.getString(3));
setAsset_num(rs.getString(4));
setSerial_num(rs.getString(5));
setScreen_size(rs.getString(6));
setModel(rs.getString(7));
setType(rs.getString(8));
setVertical_resolution(rs.getString(9));
setHorizontal_resolution(rs.getString(10));
setManufacturer(rs.getString(11));
setReceived(rs.getString(12));
setExpected_age(rs.getString(13));
setStatus(rs.getString(14));
setNotes(rs.getString(15));
setEditable(rs.getString(16));
} else {
return "Record " + id + " Not found";
}
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
}
return back;
}
/**
* 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;
}
public String saveImport(PreparedStatement pstmt) {
String msg = "";
try {
int jj = 1;
pstmt.setString(jj++, external_id);
if (device_id.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, device_id);
if (name.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, name);
// asset_num = null
if (serial_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, serial_num);
// screen_size = null
if (model.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, model);
//
if (type.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, type);
if (vertical_resolution.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, vertical_resolution);
if (horizontal_resolution.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, horizontal_resolution);
if (manufacturer.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, manufacturer);
//
if (received.equals("")) pstmt.setNull(jj++, Types.DATE);
else pstmt.setString(jj++, received); // as text
// expected_age = null
// status = 'Active'
// notes = null
// editable = null
pstmt.executeUpdate();
} catch (Exception ex) {
msg += " Save " + ex;
// System.err.println(id+" "+ex);
logger.error(external_id + " " + ex);
}
return msg;
}
/**
* This method updates the BLG_PRD table.
*
* @exception SQLException, if query fails
* @author
*/
public boolean updateJrnldts(String bpid, String strtdt, String enddt) {
String query;
boolean update_flag = false;
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"update blg_prd set bp_strt_dat=to_date(?,'MM/DD/YYYY') , bp_end_dat=to_date(?,'MM/DD/YYYY') where bp_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl:Updation- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, strtdt);
pstmt.setString(2, enddt);
pstmt.setString(3, bpid);
update_flag = (pstmt.executeUpdate() != 0) ? true : false;
if (pstmt != null) pstmt.close();
return update_flag;
} catch (SQLException ex) {
try {
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close statement", this, e);
}
USFEnv.getLog().writeCrit("Dinvjrnl: The Updation of Journal Dates failed", this, ex);
} catch (Exception e) {
try {
if (pstmt != null) pstmt.close();
} catch (Exception ex) {
USFEnv.getLog().writeCrit("Unable to close statement", this, ex);
}
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
return false;
}
/**
* 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;
}
/**
* Ejecuta una sentencia SQL y regresa como resultado un objeto ResultSet. La Consulta requiere de
* parametros en tiempo de ejecuci�n.
*
* @param consultaSQL Cadena que contiene una sentencia de consulta SQL: SELECT listaCampos FROM
* listaTablas WHERE listaCondiciones
* @param parametros Un Iterador de Parametros con los parametros de la consulta.
* @return Regresa un objeto ResulSet con el resultado de la consulta
*/
protected ResultSet ejecutarSQL(String consultaSQL, Iterator parametros) throws Exception {
if (this.conectar()) {
PreparedStatement sql = this.conexión.prepareStatement(consultaSQL);
// System.err.println(consultaSQL+"-->");
for (int i = 1; parametros.hasNext(); i++) {
String parametro = parametros.next().toString();
sql.setString(i, parametro);
// System.err.println("param["+i+"]="+parametro);
}
return sql.executeQuery();
} else return null;
} // Fin ejecutarSQL
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDates(String strtdt, String enddt, String year) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from fung_yr where to_date(?,'MM/DD/YYYY') < to_date(?,'MM/DD/YYYY') ";
query = query + "and yr = ? and strt_dat-10 <= to_date(?,'MM/DD/YYYY') ";
query = query + "and end_dat >= to_date(?,'MM/DD/YYYY') ";
USFEnv.getLog().writeDebug("Dinvjrnl: Check Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, strtdt);
pstmt.setString(2, enddt);
pstmt.setString(3, year);
pstmt.setString(4, strtdt);
pstmt.setString(5, enddt);
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;
}
public String doUpdate() {
String back = "";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String str = "";
String qq = "";
con = Helper.getConnection();
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
qq =
"update monitors set external_id=?,device_id=?,"
+ "name=?,asset_num=?,serial_num=?,screen_size=?,model=?,type=?,"
+ "vertical_resolution=?,horizontal_resolution=?,manufacturer=?,"
+ "received=?,expected_age=?, "
+ "notes=? "
+ // we do not change status here
"where id=?";
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
back = fillPStatement(pstmt);
if (back.equals("")) {
pstmt.setString(15, id); // 17 - 2 (editable)
pstmt.executeUpdate();
}
} catch (Exception ex) {
back += ex + ":" + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
if (back.equals("")) {
back = doSelect(); // to get status
}
return back;
}
/**
* This method Inserts Journal Dates in BLG_PRD table.
*
* @exception SQLException, if Insertion fails
* @author
*/
public boolean insertJrnldts(
String bpid,
String blgsys,
String rgn,
String strtdt,
String enddt,
String month,
String year) {
String query;
boolean insert_flag = false;
PreparedStatement pstmt = null;
query =
"Insert into blg_prd (bp_id,bs_id_fk,bp_year,bp_month,bp_strt_dat,bp_end_dat,bp_rgn) values ";
query = query + "(?,?,?,?,to_date(?,'MM/DD/YYYY') ,to_date(?,'MM/DD/YYYY') ,?) ";
USFEnv.getLog().writeDebug("Dinvjrnl:Insertion - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
pstmt.setString(2, blgsys);
pstmt.setString(3, year);
pstmt.setString(4, month);
pstmt.setString(5, strtdt);
pstmt.setString(6, enddt);
pstmt.setString(7, rgn);
insert_flag = (pstmt.executeUpdate() != 0) ? true : false;
if (pstmt != null) pstmt.close();
return insert_flag;
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: The Insertion of Journal Dates failed", this, ex);
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
//
// Find all the methods for java.sql objects in the Connection which raise
// SQLFeatureNotSupportedException.
//
private void connectionWorkhorse(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
vetSavepoint(conn, unsupportedList, notUnderstoodList);
vetLargeObjects(conn, unsupportedList, notUnderstoodList);
DatabaseMetaData dbmd = conn.getMetaData();
PreparedStatement ps = conn.prepareStatement("select * from sys.systables where tablename = ?");
ps.setString(1, "foo");
ParameterMetaData parameterMetaData = ps.getParameterMetaData();
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Statement stmt = conn.createStatement();
CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)");
ParameterMetaData csmd = cs.getParameterMetaData();
//
// The vetObject() method calls all of the methods in these objects
// in a deterministic order, calling the close() method last.
// Inspect these objects in an order which respects the fact that
// the objects are closed as a result of calling vetObject().
//
vetObject(dbmd, unsupportedList, notUnderstoodList);
vetObject(stmt, unsupportedList, notUnderstoodList);
vetObject(csmd, unsupportedList, notUnderstoodList);
vetObject(cs, unsupportedList, notUnderstoodList);
vetObject(rsmd, unsupportedList, notUnderstoodList);
vetObject(rs, unsupportedList, notUnderstoodList);
vetObject(parameterMetaData, unsupportedList, notUnderstoodList);
vetObject(ps, unsupportedList, notUnderstoodList);
vetObject(conn, unsupportedList, notUnderstoodList);
// No need to close the objects. They were closed by vetObject().
}
public void _jspService(HttpServletRequest request, HttpServletResponse response)
throws java.io.IOException, ServletException {
PageContext pageContext = null;
HttpSession session = null;
ServletContext application = null;
ServletConfig config = null;
JspWriter out = null;
Object page = this;
JspWriter _jspx_out = null;
PageContext _jspx_page_context = null;
try {
response.setContentType("text/html");
pageContext = _jspxFactory.getPageContext(this, request, response, null, true, 8192, true);
_jspx_page_context = pageContext;
application = pageContext.getServletContext();
config = pageContext.getServletConfig();
session = pageContext.getSession();
out = pageContext.getOut();
_jspx_out = out;
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("<!--%@ page errorPage=\"/error.jsp\" %-->\n");
response.setHeader("Pragma", "no-cache"); // HTTP 1.0
response.setDateHeader("Expires", 0);
response.setHeader("Cache-Control", "no-cache"); // HTTP 1.1
String _adminid = "";
String _adminname = "";
String _admintype = "";
String _admingroup = "";
String _approval = "";
String _adminclass = "";
String _adminmail = "";
try {
_adminid = (String) session.getAttribute("adminid");
if (_adminid == null || _adminid.length() == 0 || _adminid.equals("null")) {
response.sendRedirect("/admin/login_first.html");
return;
}
_adminname = (String) session.getAttribute("adminname");
_admintype = (String) session.getAttribute("admintype");
_admingroup = (String) session.getAttribute("admingroup");
_approval = (String) session.getAttribute("approval");
_adminclass = (String) session.getAttribute("adminclass");
_adminmail = (String) session.getAttribute("admin_email");
// session.setMaxInactiveInterval(60*60);
} catch (Exception e) {
response.sendRedirect("/admin/login_first.html");
return;
}
out.write('\n');
out.write('\n');
out.write('\n');
String password = request.getParameter("password");
String fromURL = request.getParameter("fromURL");
String oldPassword = "";
String sql = "";
int iCnt = 0;
boolean isSucceeded = false;
String strMsg = "";
Connection conn = null;
MatrixDataSet matrix = null;
DataProcess dataProcess = null;
PreparedStatement pstmt = null;
String targetUrl = "";
try {
if (password.equals("1111")) {
throw new UserDefinedException(
"The new password is not acceptable. Change your password.");
}
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/scm");
conn = ds.getConnection();
matrix = new dbconn.MatrixDataSet();
dataProcess = new DataProcess();
sql =
" select password " + " from admin_01t " + " where adminid = '" + _adminid + "' ";
iCnt = dataProcess.RetrieveData(sql, matrix, conn);
if (iCnt > 0) {
oldPassword = matrix.getRowData(0).getData(0);
} else {
throw new UserDefinedException("Can't find User Information.");
}
if (password.equals(oldPassword)) {
throw new UserDefinedException(
"The new password is not acceptable. Change your password.");
}
// update ó¸®...
int idx = 0;
conn.setAutoCommit(false);
sql =
" update admin_01t "
+ " set password = ?, pw_date = sysdate() "
+ " where adminid = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(++idx, password);
pstmt.setString(++idx, _adminid);
iCnt = pstmt.executeUpdate();
if (iCnt != 1) {
throw new UserDefinedException("Password update failed.");
}
conn.commit();
isSucceeded = true;
} catch (UserDefinedException ue) {
try {
conn.rollback();
} catch (Exception ex) {
}
strMsg = ue.getMessage();
} catch (Exception e) {
try {
conn.rollback();
} catch (Exception ex) {
}
System.out.println("Exception /admin/resetAdminPasswd : " + e.getMessage());
throw e;
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {
}
}
if (conn != null) {
try {
conn.setAutoCommit(true);
} catch (Exception e) {
}
conn.close();
}
}
// °á°ú ¸Þ½ÃÁö ó¸®
if (isSucceeded) {
// where to go?
if (fromURL.equals("menu")) {
targetUrl = "";
} else {
targetUrl = "/admin/index2.jsp";
}
strMsg = "The data are successfully processed.";
} else {
strMsg = "The operation failed.\\n" + strMsg;
targetUrl = "/admin/resetAdminPasswdForm.jsp";
}
out.write("\n");
out.write("<html>\n");
out.write("<head>\n");
out.write("<title></title>\n");
out.write("<link href=\"/common/css/style.css\" rel=\"stylesheet\" type=\"text/css\">\n");
out.write("</head>\n");
out.write("<body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>\n");
out.write("<form name=\"form1\" method=\"post\" action=\"");
out.print(targetUrl);
out.write("\">\n");
out.write("<input type='hidden' name='fromURL' value='");
out.print(fromURL);
out.write("'>\n");
out.write("</form>\n");
out.write("<script language=\"javascript\">\n");
if (targetUrl.length() > 0) {
out.write("\n");
out.write(" alert('");
out.print(strMsg);
out.write("');\n");
out.write(" document.form1.submit();\n");
}
out.write("\n");
out.write("</script>\n");
out.write("<table width='840' border='0' cellspacing='0' cellpadding='0'><tr><td>\n");
out.write("\n");
out.write("<table width='99%' border='0' cellspacing='0' cellpadding='0'>\n");
out.write("<tr>\n");
out.write(" <td height='15' colspan='2'></td>\n");
out.write("</tr>\n");
out.write("<tr>\n");
out.write(" <td width='3%'><img src='/img/title_icon.gif'></td>\n");
out.write(" <td width='*' class='left_title'>Password Change</td>\n");
out.write("</tr>\n");
out.write("<tr>\n");
out.write(" <td width='100%' height='2' colspan='2'><hr width='100%'></td>\n");
out.write("</tr>\n");
out.write("<tr>\n");
out.write(" <td height='10' colspan='2'></td>\n");
out.write("</tr>\n");
out.write("</table>\n");
out.write("\n");
out.write("<table width='90%' border='0' cellspacing='0' cellpadding='0' align='center'>\n");
out.write("<tr>\n");
out.write(" <td width='100%' align='center'><img border=\"0\" src=\"/img/pass.jpg\">\n");
out.write(" <br><br>\n");
out.write(" <b>The Password has been changed successfully.</b></td>\n");
out.write("</tr>\n");
out.write("</table>\n");
out.println(CopyRightLogo());
out.write("\n");
out.write("</tr></td></table>\n");
out.write("</body>\n");
out.write("</html>");
} catch (Throwable t) {
if (!(t instanceof SkipPageException)) {
out = _jspx_out;
if (out != null && out.getBufferSize() != 0)
try {
out.clearBuffer();
} catch (java.io.IOException e) {
}
if (_jspx_page_context != null) _jspx_page_context.handlePageException(t);
}
} finally {
_jspxFactory.releasePageContext(_jspx_page_context);
}
}
/**
* 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 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
public String find() {
String back = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = Helper.getConnection();
String qq =
"select r.id,r.asset_id,r.asset_num,r.type, "
+ " date_format(date,'%m/%d/%Y'),r.location_id,r.weight,r.description "
+ " from recycled_items r ";
String qw = "";
if (con == null) {
back = "Could not connect to DB";
addError(back);
return back;
}
try {
if (!location_id.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.location_id = ? ";
}
if (!asset_id.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.asset_id = ? ";
}
if (!type.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.type = ? ";
}
if (!date_from.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.date >= str_to_date('" + date_from + "','%m/%d/%Y')";
}
if (!date_to.equals("")) {
if (!qw.equals("")) qw += " and ";
qw += " r.date <= str_to_date('" + date_to + "','%m/%d/%Y')";
}
if (!qw.equals("")) {
qq = qq + " where " + qw;
}
qq = qq + " order by r.date DESC " + limit;
if (debug) {
logger.debug(qq);
}
pstmt = con.prepareStatement(qq);
int jj = 1;
if (!location_id.equals("")) {
pstmt.setString(jj++, location_id);
}
if (!asset_id.equals("")) {
pstmt.setString(jj++, asset_id);
}
if (!type.equals("")) {
pstmt.setString(jj++, type);
}
rs = pstmt.executeQuery();
while (rs.next()) {
if (recycledItems == null) recycledItems = new ArrayList<RecycledItem>();
RecycledItem one =
new RecycledItem(
debug,
rs.getString(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
rs.getString(6),
rs.getString(7),
rs.getString(8));
recycledItems.add(one);
}
} catch (Exception ex) {
back += ex + " : " + qq;
logger.error(back);
addError(back);
} finally {
Helper.databaseDisconnect(con, pstmt, rs);
}
return back;
}
String fillPStatement(PreparedStatement pstmt) {
String back = "";
int jj = 1;
try {
if (external_id.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, external_id);
if (device_id.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, device_id);
if (name.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, name);
if (asset_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, asset_num);
if (serial_num.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, serial_num);
if (screen_size.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, screen_size);
if (model.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, model);
if (type.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, type);
if (vertical_resolution.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, vertical_resolution);
if (horizontal_resolution.equals("")) pstmt.setNull(jj++, Types.INTEGER);
else pstmt.setString(jj++, horizontal_resolution);
if (manufacturer.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, manufacturer);
if (received.equals("")) pstmt.setNull(jj++, Types.DATE);
else pstmt.setDate(jj++, new java.sql.Date(dateFormat.parse(received).getTime()));
pstmt.setString(jj++, "" + expected_age);
if (id.equals("")) {
status = "Active";
}
// otherwise we skip status
if (notes.equals("")) pstmt.setNull(jj++, Types.VARCHAR);
else pstmt.setString(jj++, notes);
editable = "y";
} catch (Exception ex) {
logger.error(ex);
back += ex;
}
return back;
}