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;
}
/**
* 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()
/**
* 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 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 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;
}
/**
* 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;
}
/**
* 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 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;charset=UTF-8");
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;
_jspx_resourceInjector =
(org.glassfish.jsp.api.ResourceInjector)
application.getAttribute("com.sun.appserv.jsp.resource.injector");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("\n");
out.write("<!DOCTYPE html>\n");
out.write("<html>\n");
out.write(" <head>\n");
out.write(
" <meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n");
out.write(" <title>Fine</title>\n");
out.write(" <link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\"> \n");
out.write("\n");
out.write(" </head>\n");
out.write(" <body style = \"background-image: url(lib2.jpg)\"> \n");
out.write(" <center>\n");
out.write(" <h1>Update Fines information</h1>\n");
out.write(" <form name=\"Update\" action=\"Fines_upd.jsp\">\n");
out.write(" <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
out.write(" <thead>\n");
out.write(" <tr>\n");
out.write(" <th>Update Fines</th>\n");
out.write(" <th></th>\n");
out.write(" </tr>\n");
out.write(" </thead>\n");
out.write(" <tbody>\n");
out.write(" <tr>\n");
out.write(" <td>Update Fine table with todays Data</td>\n");
out.write(
" <td><input type=\"submit\" value=\"Update / View Fines\" name=\"SUBMIT\"/></td>\n");
out.write(" </tr>\n");
out.write(" </tbody>\n");
out.write(" </table> \n");
out.write(" </form>\n");
out.write(" <h1>Check your Fines Here</h1>\n");
out.write(" <form name=\"Fines\" action=\"Fines.jsp\">\n");
out.write(" <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
out.write(" <thead>\n");
out.write(" <tr>\n");
out.write(" <th>Get Fine Details</th>\n");
out.write(" <th></th>\n");
out.write(" </tr>\n");
out.write(" </thead>\n");
out.write(" <tbody>\n");
out.write(" <tr>\n");
out.write(" <td>Card No</td>\n");
out.write(
" <td><input type=\"text\" name=\"Card_no\" value=\"\"/></td>\n");
out.write(" </tr>\n");
out.write(" <tr>\n");
out.write(" <td></td>\n");
out.write(
" <td><input type=\"submit\" value=\"Get Fines\" name=\"SUBMIT\" /></td>\n");
out.write(" </tr>\n");
out.write(" </tbody>\n");
out.write(" </table> \n");
out.write(" ");
Connection con = null;
String[] selected_Checkboxes = request.getParameterValues("chk");
PreparedStatement pst = null;
ResultSet result = null;
ResultSet resUpd = null;
con =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/lbms_db?zeroDateTimeBehavior=convertToNull",
"root",
"admin12");
String Card_no = request.getParameter("Card_no");
String button = null;
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
String current_date = sdf.format(dt);
if (Card_no != null && selected_Checkboxes == null) {
String selSql =
"SELECT l.card_no, SUM(f.fine_amt) AS total_fine, f.paid "
+ "FROM book_loans l, fines f "
+ "WHERE l.loan_id = f.loan_id AND "
+ "l.card_no = "
+ Card_no
+ " "
+ "GROUP BY l.card_no";
pst = con.prepareStatement(selSql);
result = pst.executeQuery();
String box = null;
String paid;
String pay;
Boolean chk = false;
out.println("<table>");
pay = "<form action='Fines.jsp'>";
out.println(pay);
out.println("<tr>");
out.println("<th>Card No</th>");
out.println("<th>Fine_Amt</th>");
out.println("<th>Paid OR Not</th>");
out.println("</tr>");
while (result.next()) {
chk = true;
paid = "No";
if (result.getBoolean("f.paid")) {
paid = "Yes";
}
out.println("<tr>");
out.println(
"<td>"
+ result.getInt("l.card_no")
+ "</td><td>"
+ result.getString("total_fine")
+ "</td><td>"
+ paid
+ "</td>");
out.print("<td>");
box = "<input name='chk' value=" + result.getInt("l.card_no") + " type='checkbox'>";
out.print(box);
out.print("</td>");
out.print("</tr>");
}
if (chk == true) {
out.println("<tr>");
out.print("<td>");
button = "<input type='submit' value='Pay Fine' name='Pay'>";
out.print(button);
out.print("</td>");
out.println("</tr>");
} else {
out.write(
"<dialog open> <font color = 'green'>No Fine information. You owe nothing! Thank You</font> </dialog>");
}
out.println("</form>");
out.println("</table>");
} else if (selected_Checkboxes != null) {
String sqlLoan = null;
ResultSet resultLoan = null;
String sqlUpdFine = null;
PreparedStatement pstUpd = null;
String sqlBook = null;
ResultSet rsltBook = null;
char chkouts = 'N';
int length_chk = selected_Checkboxes.length;
for (int i = 0; i < length_chk; i++) {
// Check whether the Book is returned before paying the fine.
sqlBook =
"SELECT COUNT(loan_id) AS no_chkouts FROM book_loans WHERE card_no = "
+ selected_Checkboxes[i]
+ " AND date_in = '0000-00-00' AND due_date < "
+ current_date
+ "";
pst = con.prepareStatement(sqlBook);
rsltBook = pst.executeQuery();
while (rsltBook.next()) {
if (rsltBook.getInt("no_chkouts") > 0) {
chkouts = 'Y';
}
}
if (chkouts == 'Y') {
out.write(
"<dialog open> <font color = 'red'>You have outstanding due checkouts!. Please return the books and then Pay the fine</font> </dialog>");
}
// Get the corresponding loan_Ids for each customer from Fines table
sqlLoan =
"SELECT loan_id FROM book_loans WHERE card_no = "
+ selected_Checkboxes[i]
+ " AND date_in IS NOT NULL AND due_date < date_in";
pst = con.prepareStatement(sqlLoan);
resultLoan = pst.executeQuery();
while (resultLoan.next()) {
sqlUpdFine =
"UPDATE fines SET paid = true WHERE loan_id = " + resultLoan.getInt("loan_id") + "";
pstUpd = con.prepareStatement(sqlUpdFine);
pstUpd.executeUpdate();
out.println("Payment Updated Successfully");
}
}
}
out.write("\n");
out.write(" </form> \n");
out.write(" </center>\n");
out.write("</body>\n");
out.write("</html>\n");
} catch (Throwable t) {
if (!(t instanceof SkipPageException)) {
out = _jspx_out;
if (out != null && out.getBufferSize() != 0) out.clearBuffer();
if (_jspx_page_context != null) _jspx_page_context.handlePageException(t);
else throw new ServletException(t);
}
} finally {
_jspxFactory.releasePageContext(_jspx_page_context);
}
}