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;
}
/**
* 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;
}
/**
* Consulta as glosas do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas
* @return um array de glosas do prestador fornecido como paramentro
*/
public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
GlosaPrestador[] glosas = null;
PreparedStatement pst;
ResultSet rset;
int qtdeGlosas = 0;
try {
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeGlosas += 1;
} while (rset.next());
System.out.println("qtdeGlosas -> " + qtdeGlosas);
glosas = new GlosaPrestador[qtdeGlosas];
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
qtdeGlosas = 0;
while (rset.next()) {
glosas[qtdeGlosas] = montaGlosaPrestador(rset);
qtdeGlosas++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return glosas;
} // consultaGlosaPrest()
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to 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;
}
/**
* Consulta as fatos do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos
* @return um array de fatos do prestador fornecido como paramentro
*/
public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
ResumoFato[] fatos = null;
PreparedStatement pst;
ResultSet rset;
int qtdeFatos = 0;
try {
pst = con.prepareStatement(CONSULTA_RESUMO_FATO);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
pst.setString(3, strCdContrato);
pst.setString(4, strNumPeriodo);
pst.setString(5, strCdContrato);
pst.setString(6, strNumPeriodo);
pst.setString(7, strCdContrato);
pst.setString(8, strNumPeriodo);
pst.setString(9, strCdContrato);
pst.setString(10, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeFatos += 1;
} while (rset.next());
System.out.println("qtdeFatos -> " + qtdeFatos);
fatos = new ResumoFato[qtdeFatos];
rset = pst.executeQuery();
qtdeFatos = 0;
while (rset.next()) {
fatos[qtdeFatos] = montaResumoFato(rset);
qtdeFatos++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return fatos;
} // buscaResumoFato()
/**
* 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;
}
/**
* 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;
}
/**
* 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;
}