Пример #1
1
  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;
  }
Пример #2
1
  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;
  }
Пример #3
0
  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;
  }
Пример #4
0
  /**
   * 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;
  }
Пример #5
0
 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;
 }
Пример #6
0
  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;
  }
Пример #7
0
  /**
   * 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;
  }
Пример #8
0
  /**
   * 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
Пример #9
0
  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;
  }
Пример #10
0
  /**
   * 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;
  }
Пример #11
0
  /**
   * 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;
  }
Пример #12
0
  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;
  }
Пример #13
0
  /**
   * 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;
  }
Пример #14
0
  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;
  }
Пример #15
0
  /**
   * 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;
  }
Пример #16
0
  /**
   * 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;
  }
Пример #17
0
  /**
   * 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
Пример #18
0
  /**
   * 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;
  }
Пример #19
0
  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;
  }
Пример #20
0
  /**
   * 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);
    }
  }
Пример #23
0
  /**
   * 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;
  }
Пример #24
0
  /**
   * 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;
  }
Пример #26
0
  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;
  }