OERL: Instruments: Search

<%-- advInstrSearch.jsp ------------------ A complete search form for Instruments in the OERL relational database. Submitted to doInstrSearch.jsp. Javscript Includes: focusWindow.js Dynamic Includes: uncheck.js submitToURL.js inc_tblHdrBGColor.html inc_tblIndentWidth.html inc_searchIntro.html inc_buttonsForm.html dinc_searchTitle.jsp Static Includes: sinc_checkEmptyProjectParams.jsp sinc_emptyFieldMessage.jsp sinc_formVars.jsp sinc_instrConstants.jsp sinc_oerlWebViewCon.jsp sinc_printEmptyProjectFields.jsp (forms) sinc_contentAreaForm.jsp sinc_fundingSourceForm.jsp sinc_organizationForm.jsp sinc_projectTypeSubmitForm.jsp Created by zaz@sri.com. Last modified by zaz@sri.com, 3/10/03. --%> <%@ page errorPage="error.jsp" %> <%@ page import="java.sql.*" %> <%@ include file="../jspcode/sinc_instrConstants.jsp" %> <%@ include file="codefragments/sinc_formVars.jsp" %> <%-- Make connection to database using oerl_webview login --%> <%@ include file="../jspcode/sinc_oerlWebViewCon.jsp" %> <% boolean hasEmptyFields = false, hasEmptyInstrumentType = false, hasEmptyInstrumentTopic = false, hasEmptyFormat = false; // Check to see if there are any unspecified search parameters // described in the HTTP request parameters if (request.getParameter("hasEmptyFields") != null) hasEmptyFields = true; if (request.getParameter("hasEmptyInstrumentType") != null) hasEmptyInstrumentType = true; if (request.getParameter("hasEmptyInstrumentTopic") != null) hasEmptyInstrumentTopic = true; if (request.getParameter("hasEmptyFormat") != null) hasEmptyFormat = true; // Check for empty project field parameters %><%@ include file="codefragments/sinc_checkEmptyProjectParams.jsp" %> OERL: Instruments: Search


	: Instruments : Search

<% // If there are empty parameters, print a message to that effect if (hasEmptyFields) { %> <% if (hasEmptyInstrumentType) { %> <% } %> <% if (hasEmptyInstrumentTopic) { %> <% } %> <% if (hasEmptyFormat) { %> <% } %> <%@ include file="codefragments/sinc_printEmptyProjectFields.jsp" %> <% } %> <% String listAllQuery = "?ListAll=true"; listAllQuery += "&noProjectTypeField=true"; listAllQuery += "&noFundingSourceField=true"; listAllQuery += "&noContentAreaField=true"; listAllQuery += "&noOrganizationField=true"; listAllQuery += "&noInstrumentTypeField=true"; listAllQuery += "&noInstrumentTopicField=true"; listAllQuery += "&noFormatField=true"; %>

Overwhelmed by all the choices? You can go back to the simple search for instruments. You can also view a list of all instruments.

> <% } else { %>><% } %> > <% } else { %>><% } %> > <% } else { %>><% } %> > <% } else { %>><% } %> > <% } else { %>><% } %> > <% } else { %>><% } %> > <% } else { %>><% } %>

For Instruments from these Types of Projects:
<% boolean doSubmitToURL = true; %> <%@ include file="forms/sinc_projectTypeForm.jsp" %>

From Projects funded by:
<%@ include file="forms/sinc_fundingSourceForm.jsp" %>

From Projects in these Content Areas:
<%@ include file="forms/sinc_contentAreaForm.jsp" %>

From Projects contributed by these Organizations:
<%@ include file="forms/sinc_organizationForm.jsp" %>

Of these Instrument Types:
checked <% } %> onClick="deselectSelect('searchForm','InstrumentSubTypeID'),radioCheck('searchForm',this,'InstrumentTypeID'),submitToURL('searchForm','<%= request.getRequestURI() %>','#InstrumentType')" > Choose All Instrument Types:

<% final int NUM_COLS_IT = 2; int it_id = -1, ist_id = -1, js_it_id = 1; column = 1; values = request.getParameterValues("InstrumentTypeID"); subValues = request.getParameterValues("InstrumentSubTypeID"); // Make query and result set String itQuery = "SELECT it.InstrumentTypeID, ist.InstrumentSubTypeID, it.InstrumentTypeName, ist.InstrumentSubTypeName"; itQuery += " FROM InstrumentTypes AS it, InstrumentSubTypes AS ist, InstrumentSubTypesToProjectTypes AS ist2pt"; itQuery += " WHERE it.InstrumentTypeID=ist.InstrumentTypeID AND ist.InstrumentSubTypeID=ist2pt.InstrumentSubTypeID"; String [] projectTypeIDs = request.getParameterValues("ProjectTypeID"); if (projectTypeIDs != null) { itQuery += " AND ("; for (int i = 0; i < projectTypeIDs.length; i++) { if (i != 0) itQuery += " OR "; itQuery += "ist2pt.ProjectTypeID=" + projectTypeIDs[i]; } itQuery += " )"; } itQuery += " ORDER BY it.InstrumentTypeName, ist.InstrumentSubTypeName"; stmt = con.createStatement(); rs = stmt.executeQuery(itQuery); %> <% // Generate InstrumentType/InstrumentSubType select elements from database while (rs.next()) { int cur_it_id = rs.getInt("InstrumentTypeID"); // If there is a new instrument type id, make the header if (it_id != cur_it_id) { if (!rs.isFirst()) { %> <% if (column % NUM_COLS_IT == 0) { %> <% } column++; } %>

>
>	<%= rs.getString("InstrumentTypeName") %>: Choose All: checked <% } %> onClick="deselectSelect('searchForm','InstrumentSubTypeID',<%= js_it_id %>),radioCheck('searchForm',this,'InstrumentTypesAll'),submitToURL('searchForm','<%= request.getRequestURI() %>','#InstrumentType')" >

On these Instrument Topics:
checked <% } %> onClick="deselectSelect('searchForm','InstrumentTopicID')" > Choose All Instrument Topics:

<% // Generate Instrument Topic checkboxes from database values = request.getParameterValues("InstrumentTopicID"); String itpQuery = "SELECT itp.InstrumentTopicID, itp.InstrumentTopicName"; itpQuery += " FROM InstrumentTopics AS itp, InstrumentSubTypesToInstrumentTopics AS ist2itp, InstrumentSubTypesToProjectTypes AS ist2pt, InstrumentSubTypes AS ist"; itpQuery += " WHERE ist2itp.InstrumentTopicID=itp.InstrumentTopicID AND ist2pt.InstrumentSubTypeID=ist2itp.InstrumentSubTypeID AND ist2pt.InstrumentSubTypeID=ist.InstrumentSubTypeID"; // Add ProjectType restrictions StringBuffer projTypeRestrictions = new StringBuffer(); if (projectTypeIDs != null) { projTypeRestrictions.append(" ("); for (int i = 0; i < projectTypeIDs.length; i++) { if (i != 0) projTypeRestrictions.append(" OR "); projTypeRestrictions.append("ist2pt.ProjectTypeID=" + projectTypeIDs[i]); } projTypeRestrictions.append(") "); itpQuery += " AND " + projTypeRestrictions; } // Add InstrumentType and InstrumentSubType restrictions if (request.getParameter("InstrumentTypesAll") == null) { // Get InstrumentSubType restrictions StringBuffer istRestrictions = new StringBuffer(); String [] instrSubTypeIDs = request.getParameterValues("InstrumentSubTypeID"); if (instrSubTypeIDs != null) { istRestrictions.append(" ("); for (int i = 0; i < instrSubTypeIDs.length; i++) { if (i != 0) istRestrictions.append(" OR "); istRestrictions.append("ist.InstrumentSubTypeID=" + instrSubTypeIDs[i]); } istRestrictions.append(") "); } // Get InstrumentType restrictions StringBuffer itRestrictions = new StringBuffer(); String [] instrTypeIDs = request.getParameterValues("InstrumentTypeID"); if (instrTypeIDs != null) { itRestrictions.append(" ("); for (int i = 0; i < instrTypeIDs.length; i++) { if (i != 0) itRestrictions.append(" OR "); itRestrictions.append("ist.InstrumentTypeID=" + instrTypeIDs[i]); } itRestrictions.append(") "); } // Check if Instrument Type/Instrument SubTypes intersect with ProjectTypes if (projectTypeIDs != null) { // Check if InstrumentSubTypes intersect if (instrSubTypeIDs != null) { String ptQuery = "SELECT LinkID"; ptQuery += " FROM InstrumentSubTypesToProjectTypes AS ist2pt, InstrumentSubTypes AS ist"; ptQuery += " WHERE ist2pt.InstrumentSubTypeID=ist.InstrumentSubTypeID AND " + projTypeRestrictions + " AND " + istRestrictions; rs = con.createStatement().executeQuery(ptQuery); if (!rs.next()) instrSubTypeIDs = null; } // Check if InstrumentTypes intersect if (instrTypeIDs != null) { String ptQuery = "SELECT LinkID"; ptQuery += " FROM InstrumentSubTypesToProjectTypes AS ist2pt, InstrumentSubTypes AS ist"; ptQuery += " WHERE ist2pt.InstrumentSubTypeID=ist.InstrumentSubTypeID AND " + projTypeRestrictions + " AND " + itRestrictions; rs = con.createStatement().executeQuery(ptQuery); if (!rs.next()) instrTypeIDs = null; } } if (instrTypeIDs != null || instrSubTypeIDs != null) itpQuery += " AND ("; if (instrSubTypeIDs != null) itpQuery += istRestrictions; if (instrTypeIDs != null && instrSubTypeIDs != null) itpQuery += " OR "; if (instrTypeIDs != null) itpQuery += itRestrictions; if (instrTypeIDs != null || instrSubTypeIDs != null) itpQuery += ")"; } // Finish query and make ResultSet itpQuery += " ORDER BY itp.InstrumentTopicName"; stmt = con.createStatement(); rs = stmt.executeQuery(itpQuery); %>

In these Formats:

<% // Generate Format checkboxes from database String searchParam = request.getParameter("Search"); values = request.getParameterValues("FormatID"); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT FormatID,FormatName FROM Formats"); while (rs.next()) { int formatID = rs.getInt("FormatID"); %> <% } rs.close(); stmt.close(); %>

<% String checked = ""; if (searchParam == null) checked = "checked "; if (values != null) for (int i = 0; i < values.length; i++) if (values[i] != null && Integer.parseInt(values[i]) == formatID) { checked = "checked "; } %> value="<%= formatID %>"> <%= rs.getString("FormatName") %>

<% con.close(); %>