Untitled

<%@page language="java" import="java.sql.*,java.util.*"%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>

<%
    Driver DriverRecordset1 = (Driver) Class.forName("org.postgresql.Driver").newInstance();
    Properties props = new Properties();
    props.setProperty("user", "postgres");
    props.setProperty("password", "123456");
    String url = "jdbc:postgresql://localhost/projectdb";
    Connection Conn = DriverManager.getConnection(url, props);

    //Create a dropdown menu of all restaurants
    Statement st = Conn.createStatement();
    ResultSet rs = st.executeQuery("select res_name from Restaurant");
    HttpSession sess = request.getSession(true);
    String query = "select * from restaurant";
    sess.setAttribute("query", query);
%>


    <div align="center">
        <table border="1" cellpadding="5">
            <caption><h2>Queries</h2></caption>


              <tr>
                <td>Find restaurants that have a review with taste, delivery, and customer service ratings greater than
                    <form name="f0" action="server.jsp" >
                    <select>
                    <%  for(int i = 0; i < 5; i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>

                    <input type="submit" name="submit" value="submit">
                    </form>
                </td>
              </tr>

              <tr>
                <td>Find restaurants with more than
                <form name="f1" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select max(x.num) as y from (select count(*) as num from review group by res_id) as x");
                        rs.next();
                        for(int i = 0; i <= rs.getInt("y"); i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>
                    reviews

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find users who've spent more than
                <form name="f2" action="server.jsp" >
                    <input type="text" name="dollars" min=0 size=4>
                    dollars on a single order.

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who have eaten in at least
                <form name="f3" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select count(*) as y from restaurant");
                        rs.next();
                        for(int i = 0; i <= rs.getInt("y"); i++){ %>
                        <option><%= i%></option>
                    <% } %>
                    </select>
                    restaurants

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who have only eaten at restaurants that serve
                <form name="f4" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select distinct dish_name as y from dish");
                        rs.next();
                        while (rs.next()){ %>
                        <option><%= rs.getString("y")%></option>
                    <% } %>
                    </select>

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find dishes that cost less than
                <form name="f5" action="server.jsp" >
                        <input type="text" name="dollars" min=0 size=4>
                    dollars

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find all customers who've ordered
                <form name="f6" action="server.jsp" >
                    <select>
                    <%  rs = st.executeQuery("select distinct dish_name as y from dish");
                        rs.next();
                        while (rs.next()){ %>
                        <option><%= rs.getString("y")%></option>
                    <% } %>
                    </select>
                    in every order.

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find dishes with a wait time of less than
                <form name="f7" action="server.jsp" >
                    <input type="text" name="dollars" min=0 size=4>
                    minutes

                    <input type="submit" name = "submit" value="submit">
                </form>
                </td>
              </tr>

              <tr>
                <td>Find customers who did not get discounts in all orders made between
                <form name="f8" action="server.jsp" >

                    <input type="date" name="day1">
                    and
                    <input type="date" name="day2">

                    <input type="submit" name="submit" value="submit" onclick="myFunction()">
                </form>
                </td>
              </tr>

        </table>

    </div>
</body>
</html>