<%@ page language=”java”
import=”logic.*,com.businessobjects.samples.JRCHelperSample,
com.crystaldecisions.report.web.viewer.CrystalReportViewer,
com.crystaldecisions.reports.sdk.ReportClientDocument,
com.crystaldecisions.sdk.occa.report.application.OpenReportOptions,
com.crystaldecisions.sdk.occa.report.lib.ReportSDKExceptionBase,
com.crystaldecisions.sdk.occa.report.reportsource.IReportSource”
pageEncoding=”ISO-8859-1?
contentType=”text/html; charset=ISO-8859-1?
%>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
<html>
<head>
<title>My JSP ‘home_sql12.jsp’ starting page</title>
</head>
<body>
<%
/*java.util.Enumeration paramNames = request.getParameterNames();
while(paramNames.hasMoreElements()) {
String parm = (String)paramNames.nextElement();
out.println(parm + ” = ” + request.getParameter(parm) + “<BR/>”);
}*/
QueryHelper qHelper = new QueryHelper();
String reportName = “rpt_SR_sqlsrt.rpt”;
String repSel = “S”;
if (request.getParameter(”Report”)!=null) {
repSel = request.getParameter(”Report”);
reportName = “/rpt/rpt_”+repSel+”.rpt”;
}
//checks for null
//checks if the value is NA
//checks if the Report Selected should be passing this parameter
//adds the string to the end of the qHelper.selectionstring
if (request.getParameter(”comboboxDistrict”)!=null)
if (!request.getParameter(”comboboxDistrict”).equals(”NA”))
if ( (repSel.equals(”S”))||(repSel.equals(”PERD”))||(repSel.equals(”SPR”))||(repSel.equals(”FER”))||(repSel.equals(”SML”))||(repSel.equals(”IAR”))||(repSel.equals(”IANR”))||(repSel.equals(”TR1?)) )
qHelper.add(1,”ACV_STATIONS.STA_DISTRICT”,request.getParameter(”comboboxDistrict”));
if (request.getParameter(”comboboxCity”)!=null)
if (!request.getParameter(”comboboxCity”).equals(”NA”))
if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
qHelper.add(1,”ceds_FACILITIES.FAC_L_CITY”,request.getParameter(”comboboxCity”));
if (request.getParameter(”comboboxZip”)!=null)
if (!request.getParameter(”comboboxZip”).equals(”NA”))
if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP5?,request.getParameter(”comboboxZip”));
if (request.getParameter(”comboboxZip4?)!=null)
if (!request.getParameter(”comboboxZip4?).equals(”NA”))
if ( (repSel.equals(”S”))||(repSel.equals(”SML”)) )
qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
if (request.getParameter(”textExpDate”)!=null)
if (!request.getParameter(”textExpDate”).equals(”NA”))
if ( (repSel.equals(”PERD”))||(repSel.equals(”PERND”))||(repSel.equals(”FER”))||(repSel.equals(”IEL”))||(repSel.equals(”IEML”))||(repSel.equals(”IER”))||(repSel.equals(”TEL”))||(repSel.equals(”TEML”))||(repSel.equals(”TER”)) )
{}//qHelper.add(1,”ACV_STATIONS.STA_EXP_DATE”,request.getParameter(”textExpDate”));
if (request.getParameter(”comboboxDate1?)!=null)
if (!request.getParameter(”comboboxDate1?).equals(”NA”))
if ( (repSel.equals(”IANR”))||(repSel.equals(”TR1?))||(repSel.equals(”TR2?)) )
{}//qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
if (request.getParameter(”comboboxDate2?)!=null)
if (!request.getParameter(”comboboxZip4?).equals(”NA”))
if ( (repSel.equals(”IANR”))||(repSel.equals(”TR1?))||(repSel.equals(”TR2?)) )
{}//qHelper.add(1,”ceds_FACILITIES.FAC_L_ZIP4?,request.getParameter(”comboboxZip4?));
qHelper.removeAndOr();
//START THE CHECKBOXES
QueryHelper checkboxSCQH = new QueryHelper(); //will need an and before if greater than 3
if (request.getParameter(”checkboxSC1?)!=null)
if (request.getParameter(”checkboxSC1?).equals(”on”))
checkboxSCQH.add(1,”ACV_STATIONS.STA_CODE”,”Both”);
if (request.getParameter(”checkboxSC2?)!=null)
if (request.getParameter(”checkboxSC2?).equals(”on”))
checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”EIS”);
if (request.getParameter(”checkboxSC3?)!=null)
if (request.getParameter(”checkboxSC3?).equals(”on”))
checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”CRF”);
if (request.getParameter(”checkboxSC4?)!=null)
if (request.getParameter(”checkboxSC4?).equals(”on”))
checkboxSCQH.add(2,”ACV_STATIONS.STA_CODE”,”Neither”);
checkboxSCQH.removeAndOr();
// INSPECTION CHECK BOXES
QueryHelper checkboxQH = new QueryHelper(); //will need an and before if greater than 3
if (request.getParameter(”checkboxIS1?)!=null)
if (request.getParameter(”checkboxIS1?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Active”);
if (request.getParameter(”checkboxIS2?)!=null)
if (request.getParameter(”checkboxIS2?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Inactive”);
if (request.getParameter(”checkboxIS3?)!=null)
if (request.getParameter(”checkboxIS3?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Revoked”);
if (request.getParameter(”checkboxIS4?)!=null)
if (request.getParameter(”checkboxIS4?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_INSP_STATUS”,”Suspended”);
if (request.getParameter(”checkboxRS1?)!=null)
if (request.getParameter(”checkboxRS1?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Active”);
if (request.getParameter(”checkboxRS2?)!=null)
if (request.getParameter(”checkboxRS2?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Inactive”);
if (request.getParameter(”checkboxRS3?)!=null)
if (request.getParameter(”checkboxRS3?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Revoked”);
if (request.getParameter(”checkboxRS4?)!=null)
if (request.getParameter(”checkboxRS4?).equals(”on”))
checkboxQH.add(2,”ACV_STATIONS.STA_REPR_STATUS”,”Suspended”);
checkboxQH.removeAndOr();
String output = “”;
if (qHelper.toString().length() > 2){
output += “( ” + qHelper +” ) “;
if (checkboxSCQH.toString().length() > 2){
output += ” AND ( “+checkboxSCQH+” )”;
if (checkboxQH.toString().length() > 2)
output += ” AND ( ” +checkboxQH+” )”;
}
}
try {
// Set Report Values
ReportClientDocument clientDoc;
clientDoc = new ReportClientDocument();
clientDoc.open(reportName, OpenReportOptions._openAsReadOnly);
clientDoc.setRecordSelectionFormula(output);
//DB Info
String connectStr = “jdbc:oracle:thin:@finland:1521:DBNAME”;
String driverName = “oracle.jdbc.OracleDriver”;
String userName = “TABNAME”;
String password = “DBPASS”;
String JNDIName = “”;
JRCHelperSample.changeDataSource(clientDoc, userName, password, connectStr, driverName, JNDIName);
if (request.getParameter(”comboboxOrderBy”)!=null)
if ( (repSel.equals(”S”))||(repSel.equals(”FER”))||(repSel.equals(”SML”))||(repSel.equals(”IML”))||(repSel.equals(”TML”)) )
JRCHelperSample.addDiscreteParameterValue(clientDoc, “sort”, “”, request.getParameter(”comboboxOrderBy”));
// FORMAT SELECT/OUTPUT
String Format = “PDF”;
if (request.getParameter(”radioFormat”)!=null){
Format = request.getParameter(”radioFormat”);
}
if (Format.equals(”PDF”)) {
IReportSource reportSource = clientDoc.getReportSource();
JRCHelperSample.exportPDF(reportSource, request, response, config.getServletContext(), true);
}
else if (Format.equals(”CRView”)) {
CrystalReportViewer crystalReportPageViewer = new CrystalReportViewer();
IReportSource reportSource = clientDoc.getReportSource();
crystalReportPageViewer.setReportSource(reportSource);
crystalReportPageViewer.setOwnPage(true);
crystalReportPageViewer.setOwnForm(true);
//crystalReportPageViewer.setSelectionFormula(SelectionString);
crystalReportPageViewer.processHttpRequest(request, response, application, null);
}
else if (Format.equals(”RTF”)) {
IReportSource reportSource = clientDoc.getReportSource();
JRCHelperSample.exportRTF(reportSource, request, response, config.getServletContext(), true);
}
else if (Format.equals(”CSV”)) {
IReportSource reportSource = clientDoc.getReportSource();
JRCHelperSample.exportCSV(reportSource, request, response, config.getServletContext(), true);
}
} catch (ReportSDKExceptionBase e) {
out.println(e);
} /*catch (SQLException e) {
out.println(e);
}*/
%>