List of usage examples for java.sql ResultSet beforeFirst
void beforeFirst() throws SQLException;
ResultSet object, just before the first row. From source file:rems.Global.java
public static boolean sendEmail(String toEml, String ccEml, String bccEml, String attchmnt, String sbjct, String bdyTxt, String[] errMsgs) { try {// w w w . j a va 2 s. c om String selSql = "SELECT smtp_client, mail_user_name, mail_password, smtp_port FROM sec.sec_email_servers WHERE (is_default = 't')"; ResultSet selDtSt = Global.selectDataNoParams(selSql); selDtSt.last(); int m = selDtSt.getRow(); String smtpClnt = ""; String fromEmlNm = ""; String fromPswd = ""; errMsgs[0] = ""; int portNo = 0; if (m > 0) { selDtSt.beforeFirst(); selDtSt.next(); smtpClnt = selDtSt.getString(1); fromEmlNm = selDtSt.getString(2); fromPswd = selDtSt.getString(3); portNo = selDtSt.getInt(4); } selDtSt.close(); String fromPassword = Global.decrypt(fromPswd, Global.AppKey); // load your HTML email template if (bdyTxt.contains("<body") == false || bdyTxt.contains("</body>") == false) { bdyTxt = "<body>" + bdyTxt + "</body>"; } if (bdyTxt.contains("<html") == false || bdyTxt.contains("</html>") == false) { bdyTxt = "<!DOCTYPE html><html lang=\"en\">" + bdyTxt + "</html>"; } String htmlEmailTemplate = bdyTxt; // define you base URL to resolve relative resource locations URL url = new URL(Global.AppUrl); // create the email message ImageHtmlEmail email = new ImageHtmlEmail(); email.setDataSourceResolver(new DataSourceUrlResolver(url)); email.setHostName(smtpClnt); email.setSmtpPort(portNo); email.setAuthentication(fromEmlNm, fromPassword); email.setDebug(true); email.setStartTLSEnabled(true); email.setStartTLSRequired(true); String spltChars = "\\s*;\\s*"; String[] toEmails = removeDplctChars(toEml).trim().split(spltChars); String[] ccEmails = removeDplctChars(ccEml).trim().split(spltChars); String[] bccEmails = removeDplctChars(bccEml).trim().split(spltChars); String[] attchMnts = removeDplctChars(attchmnt).trim().split(spltChars); for (int i = 0; i < attchMnts.length; i++) { if (attchMnts[i].equals("")) { continue; } EmailAttachment attachment = new EmailAttachment(); if (attchMnts[i].startsWith("http://") || attchMnts[i].startsWith("https://")) { attachment.setURL(new URL(attchMnts[i].replaceAll(" ", "%20"))); //"http://www.apache.org/images/asf_logo_wide.gif" } else { attachment.setPath(attchMnts[i].replaceAll(" ", "%20")); } attachment.setDisposition(EmailAttachment.ATTACHMENT); //attachment.setDescription("Picture of John"); //attachment.setName("John"); // add the attachment email.attach(attachment); } int lovID = Global.getLovID("Email Addresses to Ignore"); int toMailsAdded = 0; for (int i = 0; i < toEmails.length; i++) { if (Global.isEmailValid(toEmails[i], lovID)) { if (Global.getEnbldPssblValID(toEmails[i], lovID) <= 0) { //DO Nothing toMailsAdded++; } else { toEmails[i] = "ToBeRemoved"; errMsgs[0] += "Address:" + toEmails[i] + " blacklisted by you!\r\n"; } } else { errMsgs[0] += "Address:" + toEmails[i] + " is Invalid!\r\n"; } } if (toMailsAdded <= 0) { return false; } for (int i = 0; i < toEmails.length; i++) { if (toEmails[i].equals("ToBeRemoved")) { toEmails = (String[]) ArrayUtils.remove(toEmails, i); } } if (toEmails.length > 0) { if (toEmails[0].equals("") == false) { email.addTo(toEmails); } } for (int i = 0; i < ccEmails.length; i++) { if (Global.isEmailValid(ccEmails[i], lovID)) { if (Global.getEnbldPssblValID(ccEmails[i], lovID) <= 0) { //DO Nothing } else { ccEmails[i] = "ToBeRemoved"; errMsgs[0] += "Address:" + ccEmails[i] + " blacklisted by you!\r\n"; } } else { errMsgs[0] += "Address:" + ccEmails[i] + " is Invalid!\r\n"; } } for (int i = 0; i < ccEmails.length; i++) { if (ccEmails[i].equals("ToBeRemoved")) { ccEmails = (String[]) ArrayUtils.remove(ccEmails, i); } } if (ccEmails.length > 0) { if (ccEmails[0].equals("") == false) { email.addCc(ccEmails); } } for (int i = 0; i < bccEmails.length; i++) { if (Global.isEmailValid(bccEmails[i], lovID)) { if (Global.getEnbldPssblValID(bccEmails[i], lovID) <= 0) { //DO Nothing } else { bccEmails[i] = "ToBeRemoved"; errMsgs[0] += "Address:" + bccEmails[i] + " blacklisted by you!\r\n"; } } else { errMsgs[0] += "Address:" + bccEmails[i] + " is Invalid!\r\n"; } } for (int i = 0; i < bccEmails.length; i++) { if (bccEmails[i].equals("ToBeRemoved")) { bccEmails = (String[]) ArrayUtils.remove(bccEmails, i); } } if (bccEmails.length > 0) { if (bccEmails[0].equals("") == false) { email.addBcc(bccEmails); } } email.setFrom(fromEmlNm.trim()); email.setSubject(sbjct); // set the html message email.setHtmlMsg(htmlEmailTemplate); // set the alternative message email.setTextMsg("Your email client does not support HTML messages"); // send the email if (Global.CheckForInternetConnection()) { email.send(); return true; } errMsgs[0] += "No Internet Connection"; return false; } catch (SQLException ex) { Global.errorLog = "\r\nFailed to send Email!\r\n" + ex.getMessage(); Global.writeToLog(); errMsgs[0] += "Failed to send Email!\r\n" + ex.getMessage(); return false; } catch (MalformedURLException ex) { Global.errorLog = "\r\nFailed to send Email!\r\n" + ex.getMessage(); Global.writeToLog(); errMsgs[0] += "Failed to send Email!\r\n" + ex.getMessage(); return false; } catch (EmailException ex) { Global.errorLog = "\r\nFailed to send Email!\r\n" + ex.getMessage(); Global.writeToLog(); errMsgs[0] += "Failed to send Email!\r\n" + ex.getMessage(); return false; } catch (Exception ex) { Global.errorLog = "\r\nFailed to send Email!\r\n" + ex.getMessage(); Global.writeToLog(); errMsgs[0] += "Failed to send Email!\r\n" + ex.getMessage(); return false; } }
From source file:rems.Global.java
public static void exprtToHTMLTblr(ResultSet dtst, String fileNm, String rptTitle, String[] colsToGrp, String[] colsToCnt, String[] colsToSum, String[] colsToAvrg, String[] colsToFrmt, boolean isfirst, boolean islast, boolean shdAppnd) { try {/*from www .j av a 2 s. c o m*/ System.out.println(fileNm); DecimalFormat myFormatter = new DecimalFormat("###,##0.00"); DecimalFormat myFormatter2 = new DecimalFormat("###,##0"); dtst.last(); int totlRows = dtst.getRow(); dtst.beforeFirst(); ResultSetMetaData dtstmd = dtst.getMetaData(); int colCnt = dtstmd.getColumnCount(); long totlLen = 0; for (int d = 0; d < colCnt; d++) { totlLen += dtstmd.getColumnName(d + 1).length(); } long[] colcntVals = new long[colCnt]; double[] colsumVals = new double[colCnt]; double[] colavrgVals = new double[colCnt]; String cption = ""; if (isfirst) { cption = "<caption align=\"top\">" + rptTitle + "</caption>"; Global.strSB.append("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" " + "\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\"[]><html xmlns=\"http://www.w3.org/1999/xhtml\" dir=\"ltr\" lang=\"en-US\" xml:lang=\"en\"><head><meta http-equiv=\"Content-Type\" " + "content=\"text/html; charset=utf-8\">" + System.getProperty("line.separator") + "<title>" + rptTitle + "</title>" + System.getProperty("line.separator") + "<link rel=\"stylesheet\" href=\"../amcharts/rpt.css\" type=\"text/css\"></head><body>"); Files.copy( new File(Global.getOrgImgsDrctry() + "/" + String.valueOf(Global.UsrsOrg_ID) + ".png") .toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png").toPath(), StandardCopyOption.REPLACE_EXISTING); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png"); } //Org Name String orgNm = Global.getOrgName(Global.UsrsOrg_ID); String pstl = Global.getOrgPstlAddrs(Global.UsrsOrg_ID); //Contacts Nos String cntcts = Global.getOrgContactNos(Global.UsrsOrg_ID); //Email Address String email = Global.getOrgEmailAddrs(Global.UsrsOrg_ID); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.UsrsOrg_ID) + ".png\">" + orgNm + "<br/>" + pstl + "<br/>" + cntcts + "<br/>" + email + "<br/>" + "</p>") .append(System.getProperty("line.separator")); } Global.strSB.append("<table style=\"margin-top:5px;\">" + cption + "<thead>") .append(System.getProperty("line.separator")); int wdth = 0; String finalStr = " "; for (int d = 0; d < colCnt; d++) { String algn = "left"; int colLen = dtstmd.getColumnName(d + 1).length(); wdth = (int) Math.round(((double) colLen / (double) totlLen) * 100); if (colLen >= 3) { if (Global.mustColBeFrmtd(String.valueOf(d), colsToFrmt) == true) { algn = "right"; finalStr = StringUtils.leftPad(dtstmd.getColumnName(d + 1).trim(), colLen, ' '); } else { finalStr = dtstmd.getColumnName(d + 1).trim() + " "; } Global.strSB .append("<th align=\"" + algn + "\" width=\"" + wdth + "%\">" + finalStr.replace(" ", " ") + "</th>") .append(System.getProperty("line.separator")); } } Global.strSB.append("</thead><tbody>").append(System.getProperty("line.separator")); String[][] prevRowVal = new String[totlRows][colCnt]; dtst.beforeFirst(); System.out.println(Global.strSB.toString()); for (int a = 0; a < totlRows; a++) { dtst.next(); Global.strSB.append("<tr>").append(System.getProperty("line.separator")); for (int d = 0; d < colCnt; d++) { String algn = "left"; double nwval = 0; boolean mstgrp = Global.mustColBeGrpd(String.valueOf(d), colsToGrp); if (Global.mustColBeCntd(String.valueOf(d), colsToCnt) == true) { if ((a > 0) && (mstgrp == true)) { if ((prevRowVal[a - 1][d].equals(dtst.getString(d + 1)))) { } else { colcntVals[d] += 1; } } else { colcntVals[d] += 1; } } else if (Global.mustColBeSumd(String.valueOf(d), colsToSum) == true) { nwval = Double.parseDouble(dtst.getString(d + 1)); if ((a > 0) && (mstgrp == true)) { if ((prevRowVal[a - 1][d].equals(dtst.getString(d + 1)))) { } else { colsumVals[d] += nwval; } } else { colsumVals[d] += nwval; } } else if (Global.mustColBeAvrgd(String.valueOf(d), colsToAvrg) == true) { nwval = Double.parseDouble(dtst.getString(d + 1)); if ((a > 0) && (mstgrp == true)) { if (prevRowVal[a - 1][d].equals(dtst.getString(d + 1))) { } else { colcntVals[d] += 1; colsumVals[d] += nwval; } } else { colcntVals[d] += 1; colsumVals[d] += nwval; } } int colLen = dtstmd.getColumnName(d + 1).length(); if (colLen >= 3) { if ((a > 0) && (Global.mustColBeGrpd(String.valueOf(d), colsToGrp) == true)) { if (prevRowVal[a - 1][d].equals(dtst.getString(d + 1))) { wdth = (int) Math.round(((double) colLen / (double) totlLen) * 100); Global.strSB .append("<td align=\"" + algn + "\" width=\"" + wdth + "%\">" + " ".replace(" ", " ") + "</td>") .append(System.getProperty("line.separator")); } else { wdth = (int) Math.round(((double) colLen / (double) totlLen) * 100); String frsh = " "; if (Global.mustColBeFrmtd(String.valueOf(d), colsToFrmt) == true) { algn = "right"; double num = Double.parseDouble(dtst.getString(d + 1).trim()); if (!dtst.getString(d + 1).equals("")) { frsh = myFormatter.format(num);//.Trim().PadRight(60, ' ') } else { frsh = dtst.getString(d + 1) + " "; } } else { frsh = dtst.getString(d + 1) + " "; } Global.strSB.append("<td align=\"" + algn + "\" width=\"" + wdth + "%\">" + Global.breakTxtDownHTML(frsh, dtstmd.getColumnName(d + 1).length()) .replace(" ", " ") + "</td>").append(System.getProperty("line.separator"));//.replace(" ", " ") } } else { wdth = (int) Math.round(((double) colLen / (double) totlLen) * 100); String frsh = " "; if (Global.mustColBeFrmtd(String.valueOf(d), colsToFrmt) == true) { algn = "right"; double num = Double.parseDouble(dtst.getString(d + 1).trim()); if (!dtst.getString(d + 1).equals("")) { frsh = myFormatter.format(num);//.Trim().PadRight(60, ' ') } else { frsh = dtst.getString(d + 1) + " "; } } else { frsh = dtst.getString(d + 1) + " "; } Global.strSB .append("<td align=\"" + algn + "\" width=\"" + wdth + "%\">" + Global.breakTxtDownHTML(frsh, dtstmd.getColumnName(d + 1).length()) .replace(" ", " ") + "</td>") .append(System.getProperty("line.separator"));//.replace(" ", " ") } } } Global.strSB.append("</tr>").append(System.getProperty("line.separator")); } //Populate Counts/Sums/Averages Global.strSB.append("<tr>").append(System.getProperty("line.separator")); for (int f = 0; f < colCnt; f++) { String algn = "left"; int colLen = dtstmd.getColumnName(f + 1).length(); finalStr = " "; if (colLen >= 3) { if (Global.mustColBeCntd(String.valueOf(f), colsToCnt) == true) { if (Global.mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { algn = "right"; finalStr = ("Count = " + myFormatter2.format(colcntVals[f])); } else { finalStr = ("Count = " + String.valueOf(colcntVals[f])); } } else if (Global.mustColBeSumd(String.valueOf(f), colsToSum) == true) { if (Global.mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { algn = "right"; finalStr = ("Sum = " + myFormatter.format(colsumVals[f])); } else { finalStr = ("Sum = " + String.valueOf(colcntVals[f])); } } else if (Global.mustColBeAvrgd(String.valueOf(f), colsToAvrg) == true) { if (Global.mustColBeFrmtd(String.valueOf(f), colsToFrmt) == true) { algn = "right"; finalStr = ("Average = " + myFormatter.format(colsumVals[f] / colcntVals[f])); } else { finalStr = ("Average = " + String.valueOf(colsumVals[f] / colcntVals[f])); } } else { finalStr = " "; } Global.strSB .append("<td align=\"" + algn + "\" width=\"" + wdth + "%\">" + Global.breakTxtDownHTML(finalStr, dtstmd.getColumnName(f + 1).length()) .replace(" ", " ") + "</td>") .append(System.getProperty("line.separator"));//.replace(" ", " ") } } Global.strSB.append("</tr>").append(System.getProperty("line.separator")); Global.strSB.append("</tbody></table>").append(System.getProperty("line.separator")); if (islast) { Global.strSB.append("</body></html>"); File file = new File(fileNm); // if file doesnt exists, then create it if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile(), true); BufferedWriter bw = new BufferedWriter(fw); bw.write(Global.strSB.toString()); bw.close(); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/samples/" + String.valueOf(Global.runID) + ".html"); } } } catch (Exception ex) { System.out.println(ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"); Global.errorLog += ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"; Global.writeToLog(); } }
From source file:ro.nextreports.engine.queryexec.QueryExecutor.java
public void run() { ResultSet resultSet = null; SQLException sqlException = null; int count = 0; while (!closeRequest) { long executeTime = 0; synchronized (inputWrapper) { try { // wait for query parameters while (!inputWrapper.pending) { inputWrapper.wait(); }//w w w. j a v a 2 s . com inputWrapper.pending = false; if (closeRequest) { return; } } catch (InterruptedException e) { if (closeRequest) { return; } } // execute query try { executeTime = System.currentTimeMillis(); Dialect dialect = null; try { dialect = DialectUtil.getDialect(conn); } catch (DialectException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); } if (QueryUtil.isProcedureCall(query.getText())) { resultSet = inputWrapper.statement.executeQuery(); if (dialect instanceof OracleDialect) { resultSet = (ResultSet) ((CallableStatement) inputWrapper.statement) .getObject(outputParameterPosition); } // do not know how to get the number of rows // last() and beforeFirst() do not work for an oracle stored procedure result set // see also testForData() from ResultExporter count = -1; } else { // try to get the count with a "select *" // if that fails try to get the count with rs.last() (which is time & memory expensive) // IMPORTANT : execute the count statement first (before the statement) ,otherwise there are // drivers that will close the connection (Firebird), and an error of "result set is closed" // will arise count = -1; boolean useLast = false; if (inputWrapper.countStatement != null) { ResultSet countResultSet = null; try { countResultSet = inputWrapper.countStatement.executeQuery(); countResultSet.next(); count = countResultSet.getInt(1); } catch (SQLException e) { LOG.info("Cannot execute count statement : " + e.getMessage() + " .Will use rs.last()"); useLast = true; } finally { ConnectionUtil.closeStatement(inputWrapper.countStatement); ConnectionUtil.closeResultSet(countResultSet); inputWrapper.countStatement = null; } } else { if (!cancelRequest) { useLast = true; } } if (!cancelRequest) { resultSet = inputWrapper.statement.executeQuery(); if (useLast && !cancelRequest && computeCount) { if ((dialect instanceof SQLiteDialect) || (dialect instanceof CSVDialect)) { // resultSet is forward only count = -1; } else { resultSet.last(); count = resultSet.getRow(); resultSet.beforeFirst(); } } } } executeTime = System.currentTimeMillis() - executeTime; logSql(executeTime); statementParameters.clear(); } catch (SQLException e) { if (!cancelRequest) { sqlException = e; } } catch (Throwable t) { // catch any driver exception and log it LOG.error(t.getMessage(), t); if (!cancelRequest) { sqlException = new SQLException("Execute query. See log for details"); } } } // set query resultWrapper synchronized (resultWrapper) { resultWrapper.resultSet = resultSet; resultWrapper.count = count; resultWrapper.exception = (sqlException == null) ? null : new QueryException(sqlException); resultWrapper.serviced = true; resultWrapper.executeTime = executeTime; resultWrapper.notify(); } } }
From source file:rems.Global.java
public static void exprtToHTMLPC(ResultSet dtst, String fileNm, String rptTitle, String[] colsToGrp, String[] colsToUse, boolean isfirst, boolean islast, boolean shdAppnd) { //Pie Chart// w ww . j a v a 2s . c o m //int colCnt = dtst.Tables[0].Columns.Count; //for (int p = 0; p < colsToGrp.length; p++) //{ // Global.errorLog = "colsToGrp[" + p + "] = " + colsToGrp[p]; //} //for (int p = 0; p < colsToUse.length; p++) //{ // Global.errorLog = "colsToUse[" + p + "] = " + colsToUse[p]; //} //Global.writeToLog(); try { dtst.last(); int totlRows = dtst.getRow(); dtst.beforeFirst(); ResultSetMetaData dtstmd = dtst.getMetaData(); int colCnt = dtstmd.getColumnCount(); String cption = ""; if (isfirst) { cption = "<caption align=\"top\">" + rptTitle + "</caption>"; Global.strSB.append("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" " + "\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\"[]><html xmlns=\"http://www.w3.org/1999/xhtml\" dir=\"ltr\" lang=\"en-US\" xml:lang=\"en\"><head><meta http-equiv=\"Content-Type\" " + "content=\"text/html; charset=utf-8\"><title>" + rptTitle + "</title>") .append(System.getProperty("line.separator")) .append("<link rel=\"stylesheet\" href=\"../amcharts/rpt.css\" type=\"text/css\">") .append(System.getProperty("line.separator")); Global.strSB.append("<link rel=\"stylesheet\" href=\"style.css\" type=\"text/css\">") .append(System.getProperty("line.separator")) .append("<script src = \"../amcharts / amcharts.js\" type = \"text / javascript\"></script > ") .append(System.getProperty("line.separator")); Global.strSB.append("</head><body>").append(System.getProperty("line.separator")); Files.copy( new File(Global.getOrgImgsDrctry() + "/" + String.valueOf(Global.UsrsOrg_ID) + ".png") .toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png").toPath(), StandardCopyOption.REPLACE_EXISTING); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png"); } //Org Name String orgNm = Global.getOrgName(Global.UsrsOrg_ID); String pstl = Global.getOrgPstlAddrs(Global.UsrsOrg_ID); //Contacts Nos String cntcts = Global.getOrgContactNos(Global.UsrsOrg_ID); //Email Address String email = Global.getOrgEmailAddrs(Global.UsrsOrg_ID); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.UsrsOrg_ID) + ".png\">" + orgNm + "<br/>" + pstl + "<br/>" + cntcts + "<br/>" + email + "<br/>" + "</p>") .append(System.getProperty("line.separator")); } Global.strSB.append("<script type=\"text/javascript\">").append(System.getProperty("line.separator")) .append("var chart;").append(System.getProperty("line.separator")).append("var chartData = ["); for (int a = 0; a < totlRows; a++) { dtst.next(); if (a < totlRows - 1) { Global.strSB .append("{ctgry:\"" + dtst.getString(Integer.parseInt(colsToUse[0]) + 1) + "\", " + "vals:" + dtst.getString(Integer.parseInt(colsToUse[1]) + 1) + "},") .append(System.getProperty("line.separator")); } else { Global.strSB .append("{ctgry:\"" + dtst.getString(Integer.parseInt(colsToUse[0]) + 1) + "\", " + "vals:" + dtst.getString(Integer.parseInt(colsToUse[1]) + 1) + "}];") .append(System.getProperty("line.separator")); } } Global.strSB.append("AmCharts.ready(function () {").append(System.getProperty("line.separator")) .append("// PIE CHART").append(System.getProperty("line.separator")) .append("chart = new AmCharts.AmPieChart();").append(System.getProperty("line.separator")) .append("chart.dataProvider = chartData;").append(System.getProperty("line.separator")) .append("chart.titleField = \"ctgry\";").append(System.getProperty("line.separator")) .append("chart.valueField = \"vals\";").append(System.getProperty("line.separator")) .append("chart.outlineColor = \"#FFFFFF\";").append(System.getProperty("line.separator")) .append("chart.outlineAlpha = 0.8;").append(System.getProperty("line.separator")) .append("chart.outlineThickness = 2;").append(System.getProperty("line.separator")) .append("// this makes the chart 3D").append(System.getProperty("line.separator")) .append("chart.depth3D = 15;").append(System.getProperty("line.separator")) .append("chart.angle = 30;").append(System.getProperty("line.separator")) .append("chart.write(\"chartdiv\");").append(System.getProperty("line.separator")).append("});") .append(System.getProperty("line.separator")).append("</script>") .append(System.getProperty("line.separator")); Global.strSB.append("<h2>" + rptTitle + "</h2>").append(System.getProperty("line.separator")); Global.strSB.append("<div id=\"chartdiv\" style=\"width: " + colsToGrp[0] + "px; height: " + colsToGrp[1] + "px;\"></div>").append(System.getProperty("line.separator")); if (islast) { Global.strSB.append("</body></html>"); File file = new File(fileNm); // if file doesnt exists, then create it if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile(), true); BufferedWriter bw = new BufferedWriter(fw); bw.write(Global.strSB.toString()); bw.close(); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/samples/" + String.valueOf(Global.runID) + ".html"); } } } catch (SQLException ex) { } catch (IOException ex) { } catch (NumberFormatException ex) { } }
From source file:rems.Global.java
public static void exprtToHTMLDet(ResultSet recsdtst, ResultSet grpsdtst, String fileNm, String rptTitle, boolean isfirst, boolean islast, boolean shdAppnd, String orntnUsd, String imgCols) { try {/*from w w w. j ava 2 s . c o m*/ imgCols = "," + StringUtils.strip(imgCols, ",") + ","; String cption = ""; if (isfirst) { cption = "<caption align=\"top\">" + rptTitle + "</caption>"; Global.strSB.append("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" " + "\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\"[]><html xmlns=\"http://www.w3.org/1999/xhtml\" dir=\"ltr\" lang=\"en-US\" xml:lang=\"en\"><head><meta http-equiv=\"Content-Type\" " + "content=\"text/html; charset=utf-8\">" + System.getProperty("line.separator") + "<title>" + rptTitle + "</title>" + System.getProperty("line.separator") + "<link rel=\"stylesheet\" href=\"../amcharts/rpt.css\" type=\"text/css\"></head><body>"); Files.copy( new File(Global.getOrgImgsDrctry() + "/" + String.valueOf(Global.UsrsOrg_ID) + ".png") .toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png").toPath(), StandardCopyOption.REPLACE_EXISTING); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/images/" + String.valueOf(Global.UsrsOrg_ID) + ".png"); } //Org Name String orgNm = Global.getOrgName(Global.UsrsOrg_ID); String pstl = Global.getOrgPstlAddrs(Global.UsrsOrg_ID); //Contacts Nos String cntcts = Global.getOrgContactNos(Global.UsrsOrg_ID); //Email Address String email = Global.getOrgEmailAddrs(Global.UsrsOrg_ID); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.UsrsOrg_ID) + ".png\">" + orgNm + "<br/>" + pstl + "<br/>" + cntcts + "<br/>" + email + "<br/>" + "</p>") .append(System.getProperty("line.separator")); } int fullPgWdthVal = 800; if (orntnUsd.equals("Portrait")) { fullPgWdthVal = 700; } int wdth = 0; String finalStr = " "; String algn = "left"; String[] rptGrpVals = { "Group Title", "Group Page Width Type", "Group Min-Height", "Show Group Border", "Group Display Type", "No of Vertical Divs In Group", "Comma Separated Col Nos", "Data Label Max Width%", "Comma Separated Hdr Nms", "Column Delimiter", "Row Delimiter" }; String grpTitle = ""; String grpPgWdth = ""; int grpMinHght = 0; String shwBrdr = "Show"; String grpDsplyTyp = "Details"; int grpColDvsns = 4;//Use 1 for Images others 2 or 4 String colnums = ""; String lblmaxwdthprcnt = "35"; String tblrHdrs = ""; String clmDlmtrs = ""; String rwDlmtrs = ""; int divwdth = 0; /* 1. For each detail group create a div and fieldset with legend & border based on group settings * 2a. if detail display then create required no of td in tr1 of a table, create new tr if no of columns is not exhausted * i.e if no of vertical divs=4 no rows=math.ceil(no cols*0.5)/ * else no rows=no cols * for each col display label and data if vrtcl divs is 2 or 4 else display only data * 2b. if tabular create table with headers according to defined headers * split data according to rows and cols and display them in this table * 2. Get all column nos within the group and create their labels and data using settings * 3. if col nos is image then use full defined page width else create no of defined columns count * 4. if * */ grpsdtst.last(); recsdtst.last(); int grpdtcnt = grpsdtst.getRow(); int rowsdtcnt = recsdtst.getRow(); grpsdtst.beforeFirst(); recsdtst.beforeFirst(); ResultSetMetaData recsdtstmd = recsdtst.getMetaData(); ResultSetMetaData grpsdtstmd = grpsdtst.getMetaData(); for (int a = 0; a < rowsdtcnt; a++) { recsdtst.next(); Global.strSB.append("<table style=\"margin-top:5px;min-width:" + String.valueOf(fullPgWdthVal + 50) + "px;\">" + cption + "<tbody>").append(System.getProperty("line.separator")); Global.strSB.append("<tr><td>").append(System.getProperty("line.separator")); for (int d = 0; d < grpdtcnt; d++) { grpsdtst.next(); wdth = 35; grpTitle = grpsdtst.getString(1); grpPgWdth = grpsdtst.getString(2); grpMinHght = Integer.parseInt(grpsdtst.getString(3)); shwBrdr = grpsdtst.getString(4); grpDsplyTyp = grpsdtst.getString(5); grpColDvsns = Integer.parseInt(grpsdtst.getString(6));//Use 1 for Images others 2 or 4 colnums = grpsdtst.getString(7); lblmaxwdthprcnt = grpsdtst.getString(8); tblrHdrs = grpsdtst.getString(9); clmDlmtrs = grpsdtst.getString(10); rwDlmtrs = grpsdtst.getString(11); wdth = Integer.parseInt(lblmaxwdthprcnt); if (grpPgWdth.equals("Half Page Width")) { divwdth = (int) (fullPgWdthVal / 2); } else { divwdth = (int) (fullPgWdthVal / 1); } Global.strSB.append("<div style=\"float:left;min-width:" + String.valueOf(divwdth - 50) + "px;padding:10px;\">").append(System.getProperty("line.separator"));//min-height:" + (grpMinHght + 20).ToString() + "px; if (shwBrdr.equals("Show")) { Global.strSB .append("<fieldset style=\"min-width:" + String.valueOf(divwdth - 80) + "px;\">") .append(System.getProperty("line.separator"));//min-height:" + (grpMinHght).ToString() + "px; Global.strSB.append("<legend>" + grpTitle + "</legend>") .append(System.getProperty("line.separator")); } String w = "\\,"; String[] colNumbers = colnums.split(w); int noofRws = 1; wdth = ((divwdth - 90) * wdth) / 100; if (grpDsplyTyp.equals("DETAIL")) { if (grpColDvsns == 4) { noofRws = (int) Math.ceil((double) colNumbers.length / (double) 2); } else { noofRws = colNumbers.length; } Global.strSB .append("<table style=\"min-width:" + String.valueOf(divwdth - 90) + "px;margin-top:5px;border:none;\" border=\"0\"><tbody>") .append(System.getProperty("line.separator")); if (grpColDvsns == 4) { for (int h = 0; h < colNumbers.length; h++) { if ((h % 2) == 0) { Global.strSB.append("<tr>").append(System.getProperty("line.separator")); } int clnm = -1; clnm = Integer.parseInt(colNumbers[h]); if (clnm >= 0) { String frsh = ""; Global.strSB.append( "<td style=\"border-bottom:none;border-left:none;font-weight:bolder;\" align=\"" + algn + "\" width=\"" + wdth + "px\">") .append(System.getProperty("line.separator")); frsh = recsdtstmd.getColumnName(clnm + 1).trim() + ": "; Global.strSB.append( Global.breakTxtDownHTML(frsh, (wdth / 7)).replace(" ", " ")); Global.strSB.append("</td>").append(System.getProperty("line.separator")); Global.strSB .append("<td style=\"border-bottom:none;border-left:none;\" align=\"" + algn + "\" width=\"" + (divwdth - 90 - wdth) + "px\">") .append(System.getProperty("line.separator")); if (imgCols.contains("," + clnm + ",")) { frsh = recsdtst.getString(clnm + 1).trim(); File file = new File(Global.dataBasDir + frsh); // if file doesnt exists, then create it if (!file.exists()) { String extnsn = FilenameUtils.getExtension(Global.dataBasDir + frsh); Files.copy(new File(Global.dataBasDir + frsh).toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn).toPath(), StandardCopyOption.REPLACE_EXISTING); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn + "\" style=\"width:auto;height::" + grpMinHght + "px;\">" + "</p>") .append(System.getProperty("line.separator")); } } else { frsh = recsdtst.getString(clnm + 1).trim() + " "; Global.strSB .append(Global.breakTxtDownHTML(frsh, ((divwdth - 90 - wdth) / 7)) .replace(" ", " ")); } Global.strSB.append("</td>").append(System.getProperty("line.separator")); } if ((h % 2) == 1) { Global.strSB.append("</tr>").append(System.getProperty("line.separator")); } } } else if (grpColDvsns == 2) { for (int h = 0; h < colNumbers.length; h++) { Global.strSB.append("<tr>").append(System.getProperty("line.separator")); int clnm = -1; clnm = Integer.parseInt(colNumbers[h]); if (clnm >= 0) { String frsh = ""; Global.strSB.append( "<td style=\"border-bottom:none;border-left:none;font-weight:bold;\" align=\"" + algn + "\" width=\"" + wdth + "px\">") .append(System.getProperty("line.separator")); frsh = recsdtstmd.getColumnName(clnm + 1).trim() + ": "; Global.strSB.append( Global.breakTxtDownHTML(frsh, ((wdth) / 7)).replace(" ", " ")); Global.strSB.append("</td>").append(System.getProperty("line.separator")); Global.strSB .append("<td style=\"border-bottom:none;border-left:none;\" align=\"" + algn + "\" width=\"" + (divwdth - 90 - wdth) + "px\">") .append(System.getProperty("line.separator")); if (imgCols.contains("," + clnm + ",")) { frsh = recsdtst.getString(clnm + 1).trim(); File file = new File(Global.dataBasDir + frsh); // if file doesnt exists, then create it if (!file.exists()) { String extnsn = FilenameUtils.getExtension(Global.dataBasDir + frsh); Files.copy(new File(Global.dataBasDir + frsh).toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn).toPath(), StandardCopyOption.REPLACE_EXISTING); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn + "\" style=\"width:auto;height:" + grpMinHght + "px;\">" + "</p>") .append(System.getProperty("line.separator")); } } else { frsh = recsdtst.getString(clnm + 1).trim() + " "; Global.strSB .append(Global.breakTxtDownHTML(frsh, ((divwdth - 90 - wdth) / 7)) .replace(" ", " ")); } Global.strSB.append("</td>"); } Global.strSB.append("</tr>"); } } else if (grpColDvsns == 1) { for (int h = 0; h < colNumbers.length; h++) { Global.strSB.append("<tr>"); int clnm = -1; clnm = Integer.parseInt(colNumbers[h]); if (clnm >= 0) { String frsh = ""; Global.strSB .append("<td style=\"border-bottom:none;border-left:none;\" align=\"" + algn + "\" width=\"" + (divwdth - 90) + "px\">") .append(System.getProperty("line.separator")); if (imgCols.contains("," + clnm + ",")) { frsh = recsdtst.getString(clnm + 1).trim(); File file = new File(Global.dataBasDir + frsh); // if file doesnt exists, then create it if (!file.exists()) { String extnsn = FilenameUtils.getExtension(Global.dataBasDir + frsh); Files.copy(new File(Global.dataBasDir + frsh).toPath(), new File(Global.getRptDrctry() + "/amcharts_2100/images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn).toPath(), StandardCopyOption.REPLACE_EXISTING); Global.strSB .append("<p><img src=\"../images/" + String.valueOf(Global.runID) + "_" + String.valueOf(a) + String.valueOf(clnm) + extnsn + "\" style=\"width:auto;height:" + grpMinHght + "px;\">" + "</p>") .append(System.getProperty("line.separator")); } } else { frsh = recsdtst.getString(clnm + 1).trim() + " "; Global.strSB.append(Global.breakTxtDownHTML(frsh, ((divwdth - 90) / 7)) .replace(" ", " ")); } Global.strSB.append("</td>"); } Global.strSB.append("</tr>"); } } Global.strSB.append("</tbody></table>"); } else { } if (shwBrdr.equals("Show")) { Global.strSB.append("</fieldset>"); } Global.strSB.append("</div>"); } Global.strSB.append("</td></tr>"); Global.strSB.append("</tbody></table><br/><br/>").append(System.getProperty("line.separator")); } if (islast) { Global.strSB.append("</body></html>"); File file = new File(fileNm); // if file doesnt exists, then create it if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile(), true); BufferedWriter bw = new BufferedWriter(fw); bw.write(Global.strSB.toString()); bw.close(); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), "/amcharts_2100/samples/" + String.valueOf(Global.runID) + ".html"); } } } catch (IOException ex) { } catch (SQLException ex) { } catch (NumberFormatException ex) { } }
From source file:pagecode.RPTs.OffenceUtil.java
private void processOffenceInfoOld() { logger.info("In OffenceUtil::processOffenceInfoOld"); String selectSql = null;//from www. j a va 2 s . com Connection iicfConnection = ReportsConnectionManager.getIICFConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; if (null != iicfConnection && true == isOffenceNumberValid()) { try { String dbLetterPrefix; //If Offence is Red Light Camera or parking tickets then use //the "P" database [The photo radar DB] in IMS i.e. "P" tables in IICF if ("997".equals(jurisdictionNumber) || "901".equals(jurisdictionNumber)) { dbLetterPrefix = "P"; } else { dbLetterPrefix = "O"; } selectSql = "SELECT " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC AS OFCS_CRT_OFC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM AS OFCS_JRSDCTN_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY AS OFCS_INFO_YY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX AS OFCS_INFO_PREFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX AS OFCS_INFO_SUFFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_TRL_LANG_CD AS OFCS_TRL_LANG_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INTERP_LANG_CD AS OFCS_INTERP_LANG_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CNT_NUM AS OCNT_CNT_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_OFNC_DT AS OCNT_OFNC_DT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_OFCR_BDG_NUM AS OFCS_OFCR_BDG_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_LIC_PLATE_NUM AS OFCS_LIC_PLATE_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_1 AS OFCS_DRVR_LIC_PART_1, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_2 AS OFCS_DRVR_LIC_PART_2, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_DRVR_LIC_PART_3 AS OFCS_DRVR_LIC_PART_3, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ENF_AGCY AS OFCS_ENF_AGCY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_SEX_CD AS OFCS_SEX_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_BRTH_DT AS OFCS_BRTH_DT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CVOR_NUM AS OCNT_CVOR_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ENF_AGCY_DIV_CD AS OFCS_ENF_AGCY_DIV_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_GVN_NM AS OFCS_GVN_NM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INIT AS OFCS_INIT, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_SRNM AS OFCS_SRNM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_ST AS OFCS_ADDR_ST, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_CTY AS OFCS_ADDR_CTY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_PROV AS OFCS_ADDR_PROV, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_ADDR_PSTL_CD AS OFCS_ADDR_PSTL_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_TM AS OCNT_TM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_LOCN_DESC AS OCNT_LOCN_DESC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_STATU_CD AS OCNT_STATU_CD, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_SCTN_ID AS OCNT_SCTN_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_SUB_SCTN_ID AS OCNT_SUB_SCTN_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_PARA_ID AS OCNT_PARA_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_OFNC_CLAS_ID AS OCNT_OFNC_CLAS_ID, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_BYLAW_CD AS OCNT_BYLAW_CD " + "FROM " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT " + "WHERE " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "CNT_CNT_NUM = '0001' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' " + " " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "CNT." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' "; selectSql += " "; logger.info("+++++SQL: " + selectSql); //TODO remove this if (true) { //return; } preparedStatement = iicfConnection.prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preparedStatement.executeQuery(); resultSet.last(); int resultSetCount = resultSet.getRow(); logger.info("resultSetCount: " + resultSetCount); resultSet.beforeFirst(); if (resultSetCount != 1) { throw new Exception("Offence NOT found or MORE than one found!!!"); } else { //move to first record resultSet.next(); paramOffenceNumber = ""; if (null != resultSet.getString("OFCS_CRT_OFC")) { paramOffenceNumber = resultSet.getString("OFCS_CRT_OFC").trim() + " "; } if (null != resultSet.getString("OFCS_JRSDCTN_NUM")) { paramOffenceNumber += resultSet.getString("OFCS_JRSDCTN_NUM").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_YY")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_YY").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_PREFIX")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_PREFIX").trim() + " "; } if (null != resultSet.getString("OFCS_INFO_SUFFIX")) { paramOffenceNumber += resultSet.getString("OFCS_INFO_SUFFIX").trim(); } if (null != resultSet.getString("OFCS_TRL_LANG_CD")) { String langChar = resultSet.getString("OFCS_TRL_LANG_CD").trim(); isEnglish = (true == langChar.equalsIgnoreCase("F") ? false : true); } //NOTE: if runAsStandAlone == true then assume it is always French if (true == runAsStandAlone) { this.isEnglish = false; } paramInterpreterLanguage = ""; if (null != resultSet.getString("OFCS_INTERP_LANG_CD") && !resultSet.getString("OFCS_INTERP_LANG_CD").trim().equals("")) { paramInterpreterLanguage = resultSet.getString("OFCS_INTERP_LANG_CD").trim(); InterpreterLanguageUtil interpreterLanguageUtil = new InterpreterLanguageUtil(); interpreterLanguageUtil.setIsEnglish(this.isEnglish()); interpreterLanguageUtil.setInterpreterLanguageUtil(paramInterpreterLanguage); paramInterpreterLanguage = interpreterLanguageUtil.getInterpreterLanguageDesc(); } IconDateUtil offenceDateUtil = new IconDateUtil(); offenceDateUtil.setIsEnglish(this.isEnglish); paramOffenceDate = ""; if (null != resultSet.getString("OCNT_OFNC_DT")) { offenceDateUtil.setIconDate(resultSet.getString("OCNT_OFNC_DT").trim()); paramOffenceDate = offenceDateUtil.getDateWithSlashes(); } paramOfficerNumber = ""; if (null != resultSet.getString("OFCS_OFCR_BDG_NUM")) { paramOfficerNumber = resultSet.getString("OFCS_OFCR_BDG_NUM").trim(); } paramDriversLicenseNumber = ""; if (null != resultSet.getString("OFCS_DRVR_LIC_PART_1") && null != resultSet.getString("OFCS_DRVR_LIC_PART_2") && null != resultSet.getString("OFCS_DRVR_LIC_PART_1")) { paramDriversLicenseNumber = resultSet.getString("OFCS_DRVR_LIC_PART_1").trim() + "-" + resultSet.getString("OFCS_DRVR_LIC_PART_2").trim() + "-" + resultSet.getString("OFCS_DRVR_LIC_PART_3").trim(); } paramPlateNumber = ""; if (null != resultSet.getString("OFCS_LIC_PLATE_NUM")) { paramPlateNumber = resultSet.getString("OFCS_LIC_PLATE_NUM").trim(); } paramEnforcementAgency = ""; if (null != resultSet.getString("OFCS_ENF_AGCY")) { paramEnforcementAgency = resultSet.getString("OFCS_ENF_AGCY").trim(); } paramSex = ""; if (null != resultSet.getString("OFCS_SEX_CD")) { paramSex = resultSet.getString("OFCS_SEX_CD").trim(); } paramDateOfBirth = ""; IconDateUtil dateOfBirthUtil = new IconDateUtil(); if (null != resultSet.getString("OFCS_BRTH_DT")) { dateOfBirthUtil.setIconDate(resultSet.getString("OFCS_BRTH_DT").trim()); paramDateOfBirth = dateOfBirthUtil.getDateWithSlashes(); } paramCVOR = ""; if (null != resultSet.getString("OCNT_CVOR_NUM")) { paramCVOR = resultSet.getString("OCNT_CVOR_NUM").trim(); } paramUnit = ""; if (null != resultSet.getString("OFCS_ENF_AGCY_DIV_CD")) { paramUnit = resultSet.getString("OFCS_ENF_AGCY_DIV_CD").trim(); } paramOffenderFullName = ""; if (null != resultSet.getString("OFCS_GVN_NM")) { paramOffenderFullName = resultSet.getString("OFCS_GVN_NM").trim() + " "; } if (null != resultSet.getString("OFCS_INIT")) { paramOffenderFullName += resultSet.getString("OFCS_INIT").trim() + " "; } if (null != resultSet.getString("OFCS_SRNM")) { paramOffenderFullName += resultSet.getString("OFCS_SRNM").trim(); } paramAddressLine1 = ""; if (null != resultSet.getString("OFCS_ADDR_ST")) { paramAddressLine1 = resultSet.getString("OFCS_ADDR_ST").trim(); } paramAddressLine2 = ""; if (null != resultSet.getString("OFCS_ADDR_CTY")) { paramAddressLine2 = resultSet.getString("OFCS_ADDR_CTY").trim(); } if (null != resultSet.getString("OFCS_ADDR_PROV")) { paramAddressLine2 += ", " + resultSet.getString("OFCS_ADDR_PROV").trim(); } if (null != resultSet.getString("OFCS_ADDR_PSTL_CD")) { paramAddressLine3 = resultSet.getString("OFCS_ADDR_PSTL_CD").trim(); } paramOffenceDay = paramOffenceMonth = paramOffenceYear = ""; if (null != resultSet.getString("OCNT_OFNC_DT")) { offenceDateUtil.setIconDate(resultSet.getString("OCNT_OFNC_DT").trim()); paramOffenceDay = offenceDateUtil.getStrDay(); paramOffenceMonth = offenceDateUtil.getStrMonth(); paramOffenceYear = offenceDateUtil.getStrYear(); } //offenceDateUtil = iconDateUtil; paramOffenceTime = ""; if (null != resultSet.getString("OCNT_TM")) { offenceDateUtil.setIconTime(resultSet.getString("OCNT_TM").trim()); paramOffenceTime = offenceDateUtil.getFormatedTime(); } paramOffenceLocation = ""; if (null != resultSet.getString("OCNT_LOCN_DESC")) { paramOffenceLocation = resultSet.getString("OCNT_LOCN_DESC").trim(); } String tempOCNT_STATU_CD, tempOCNT_SCTN_ID, tempOCNT_SUB_SCTN_ID, tempOCNT_PARA_ID, tempOCNT_OFNC_CLAS_ID, tempOCNT_BYLAW_CD; tempOCNT_STATU_CD = ""; if (null != resultSet.getString("OCNT_STATU_CD")) { tempOCNT_STATU_CD = resultSet.getString("OCNT_STATU_CD").trim(); } tempOCNT_SCTN_ID = ""; if (null != resultSet.getString("OCNT_SCTN_ID")) { tempOCNT_SCTN_ID = resultSet.getString("OCNT_SCTN_ID").trim(); } tempOCNT_SUB_SCTN_ID = null; if (null != resultSet.getString("OCNT_SUB_SCTN_ID")) { tempOCNT_SUB_SCTN_ID = resultSet.getString("OCNT_SUB_SCTN_ID").trim(); } tempOCNT_PARA_ID = null; if (null != resultSet.getString("OCNT_PARA_ID")) { tempOCNT_PARA_ID = resultSet.getString("OCNT_PARA_ID").trim(); } tempOCNT_OFNC_CLAS_ID = null; if (null != resultSet.getString("OCNT_OFNC_CLAS_ID")) { tempOCNT_OFNC_CLAS_ID = resultSet.getString("OCNT_OFNC_CLAS_ID").trim(); } tempOCNT_BYLAW_CD = null; if (null != resultSet.getString("OCNT_BYLAW_CD")) { tempOCNT_BYLAW_CD = resultSet.getString("OCNT_BYLAW_CD").trim(); } OffenceStatuteDescriptionUtil offenceStatuteDescriptionUtil = new OffenceStatuteDescriptionUtil(); offenceStatuteDescriptionUtil.setIsEnglish(this.isEnglish); offenceStatuteDescriptionUtil.setOffenceStatuteDescriptionUtil(tempOCNT_STATU_CD, tempOCNT_SCTN_ID, tempOCNT_SUB_SCTN_ID, tempOCNT_PARA_ID, tempOCNT_OFNC_CLAS_ID, tempOCNT_BYLAW_CD, offenceDateUtil); paramOffenceDesc = offenceStatuteDescriptionUtil.getOffenceDescription(); paramOffenceStatue = offenceStatuteDescriptionUtil.getStatuteDescription(); paramOffenceSection = tempOCNT_SCTN_ID; if (null != tempOCNT_SUB_SCTN_ID && !tempOCNT_SUB_SCTN_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_SUB_SCTN_ID + ")"; } if (null != tempOCNT_PARA_ID && !tempOCNT_PARA_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_PARA_ID + ")"; } if (null != tempOCNT_OFNC_CLAS_ID && !tempOCNT_OFNC_CLAS_ID.equals("")) { paramOffenceSection += "(" + tempOCNT_OFNC_CLAS_ID + ")"; } //clean up for reuse if (null != preparedStatement) { preparedStatement.close(); preparedStatement = null; } if (null != resultSet) { resultSet.close(); resultSet = null; } selectSql = "SELECT " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC AS OFCS_CRT_OFC, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM AS OFCS_JRSDCTN_NUM, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY AS OFCS_INFO_YY, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX AS OFCS_INFO_PREFIX, " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX AS OFCS_INFO_SUFFIX, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_TTL_FINE_AMT, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_PRE_PD_FINE_TTL_AMT, " + " " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_PRE_PD_COST_TTL_AMT " + "FROM " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS " + "INNER JOIN " + " " + ICON_SCHEMA_NAME + ".ICOSARRT " + " ON " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_CRT_OFC" + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_JRSDCTN_NUM " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_YY " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_PREFIX " + " ) " + " AND " + " ( " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = " + ICON_SCHEMA_NAME + ".ICOSARRT.ARRT_INFO_SUFFIX " + " ) " + "WHERE " + " " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_CRT_OFC = '" + courtOfficeId + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_JRSDCTN_NUM = '" + jurisdictionNumber + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_YY = '" + infoYear + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_PREFIX = '" + infoPrefix + "' " + " AND " + ICON_SCHEMA_NAME + ".ICOS" + dbLetterPrefix + "FCS." + dbLetterPrefix + "FCS_INFO_SUFFIX = '" + infoSuffix + "' "; selectSql += " "; logger.info("+++++SQL: " + selectSql); preparedStatement = iicfConnection.prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preparedStatement.executeQuery(); resultSet.last(); resultSetCount = resultSet.getRow(); logger.info("resultSetCount: " + resultSetCount); resultSet.beforeFirst(); paramOffenceTotalPayable = ""; if (resultSetCount == 1) { resultSet.next(); // if(null != resultSet.getString("ARRT_TTL_FINE_AMT")){ // NumberFormat formatUS = NumberFormat.getCurrencyInstance(Locale.US); // //logger.info("formatUS: " + formatUS.format(resultSet.getDouble("ARRT_TTL_FINE_AMT")) ); // paramOffenceTotalPayable = formatUS.format(resultSet.getDouble("ARRT_TTL_FINE_AMT")); // } //ARRT_TTL_FINE_AMT, ARRT_PRE_PD_FINE_TTL_AMT, ARRT_PRE_PD_COST_TTL_AMT BigDecimal totalPayable; BigDecimal totalFineAmount = resultSet.getBigDecimal("ARRT_TTL_FINE_AMT"); BigDecimal totalPrePDFineAmount = resultSet.getBigDecimal("ARRT_PRE_PD_FINE_TTL_AMT"); BigDecimal totalPrePDCostAmount = resultSet.getBigDecimal("ARRT_PRE_PD_COST_TTL_AMT"); //these will never be null so NO need to check for null totalPayable = totalPrePDFineAmount.add(totalPrePDCostAmount); //IF(ARRT_PRE_PD_FINE_TTL_AMT + ARRT_PRE_PD_COST_TTL_AMT < 0) THEN if (totalPayable.compareTo(new BigDecimal("0.0")) < 0) { totalPayable = totalFineAmount; } NumberFormat formatUS = NumberFormat.getCurrencyInstance(Locale.US); paramOffenceTotalPayable = formatUS.format(totalPayable.doubleValue()); } } // if(resultSetCount != 1) } catch (SQLException sqlException) { sqlException.printStackTrace(); } catch (Exception e) { paramOffenceNumber = ""; paramOffenceDate = ""; paramOfficerNumber = ""; paramDriversLicenseNumber = ""; paramPlateNumber = ""; paramEnforcementAgency = ""; paramSex = ""; paramDateOfBirth = ""; paramCVOR = ""; paramUnit = ""; paramOffenderFullName = ""; paramAddressLine1 = ""; paramAddressLine2 = ""; paramAddressLine3 = ""; paramOffenceDay = ""; paramOffenceMonth = ""; paramOffenceYear = ""; paramOffenceTime = ""; paramOffenceLocation = ""; paramOffenceDesc = ""; paramOffenceStatue = ""; paramOffenceSection = ""; paramOffenceTotalPayable = ""; paramInterpreterLanguage = ""; e.printStackTrace(); } finally { // setDiscreteParameterValue(this, "paramOffenceNumber", "", paramOffenceNumber); // setDiscreteParameterValue(this, "paramOffenceDate", "", paramOffenceDate); // setDiscreteParameterValue(this, "paramOfficerNumber", "", paramOfficerNumber); // setDiscreteParameterValue(this, "paramDriversLicenseNumber", "", paramDriversLicenseNumber); // setDiscreteParameterValue(this, "paramPlateNumber", "", paramPlateNumber); // setDiscreteParameterValue(this, "paramEnforcementAgency", "", paramEnforcementAgency); // setDiscreteParameterValue(this, "paramSex", "", paramSex); // setDiscreteParameterValue(this, "paramDateOfBirth", "", paramDateOfBirth); // setDiscreteParameterValue(this, "paramCVOR", "", paramCVOR); // setDiscreteParameterValue(this, "paramUnit", "", paramUnit); // setDiscreteParameterValue(this, "paramOffenderFullName", "", paramOffenderFullName); // setDiscreteParameterValue(this, "paramAddressLine1", "", paramAddressLine1); // setDiscreteParameterValue(this, "paramAddressLine2", "", paramAddressLine2); // setDiscreteParameterValue(this, "paramAddressLine3", "", paramAddressLine3); // setDiscreteParameterValue(this, "paramOffenceDay", "", paramOffenceDay); // setDiscreteParameterValue(this, "paramOffenceMonth", "", paramOffenceMonth); // setDiscreteParameterValue(this, "paramOffenceYear", "", paramOffenceYear); // setDiscreteParameterValue(this, "paramOffenceTime", "", paramOffenceTime); // setDiscreteParameterValue(this, "paramOffenceLocation", "", paramOffenceLocation); // setDiscreteParameterValue(this, "paramOffenceDesc", "", paramOffenceDesc); // setDiscreteParameterValue(this, "paramOffenceStatue", "", paramOffenceStatue); // setDiscreteParameterValue(this, "paramOffenceSection", "", paramOffenceSection); // setDiscreteParameterValue(this, "paramOffenceTotalPayable", "", paramOffenceTotalPayable); // setDiscreteParameterValue(this, "paramTrialDay", "", paramTrialDay); // setDiscreteParameterValue(this, "parmaTrialMonth", "", parmaTrialMonth); // setDiscreteParameterValue(this, "paramTrailYear", "", paramTrailYear); // setDiscreteParameterValue(this, "paramTrailTimeHour", "", paramTrailTimeHour); // setDiscreteParameterValue(this, "paramTrailLocation", "", paramTrailLocationCourtOfficeId); // setDiscreteParameterValue(this, "paramIssuedAt", "", paramIssuedAt); // setDiscreteParameterValue(this, "paramIssuedDay", "", paramIssuedDay); // setDiscreteParameterValue(this, "paramIssuedMonth", "", paramIssuedMonth); // setDiscreteParameterValue(this, "paramIssuedYear", "", paramIssuedYear); // //Modified Oct 17, 2007 // setDiscreteParameterValue(this, "paramFormNumberLabel", "", paramFormNumberLabel); try { if (null != preparedStatement) { preparedStatement.close(); preparedStatement = null; } if (null != iicfConnection) { iicfConnection.close(); iicfConnection = null; } } catch (SQLException e1) { } } //try } //if(null != iicfConnection && true == isOffenceNumberValid()) }