Untitled

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    <%@ page import="java.sql.*" %>
    <%@page import ="java.sql.Date" %>
    <%@page import =  "java.text.*" %>
<%@ page import="java.io.*" %>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link rel="stylesheet" type="text/css" href="css/style.css">
<title>Insert title here</title>
</head>
<h4 align="center">Hotel Reservation Statistics</h4>
<body>
<ul>
  <li><a class="active" href="home.html">Home</a></li>
</ul>

<sql:setDataSource
    var="DS"
    driver="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/hotel"
    user="root" password="Thunder0!"
/>

<%
String checkIn = request.getParameter("bday");

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

java.util.Date dt = sdf.parse(checkIn);

java.sql.Date sqlD = new Date(dt.getTime());
pageContext.setAttribute("in_date", sqlD);
out.println("after sql " + sqlD); %>

<%
String checkOut = request.getParameter("eday");


SimpleDateFormat sdff = new SimpleDateFormat("MM/dd/yyyy");

java.util.Date dtt = sdff.parse(checkOut);

java.sql.Date sqlDD = new Date(dtt.getTime());
pageContext.setAttribute("out_date", sqlDD);
out.println("after sql " + sqlDD);
%>


<% String adminSearch = request.getParameter("gender");
    pageContext.setAttribute("adminStat", adminSearch);
    out.println("admin search type: " + adminSearch);%>


 <c:set var = "stats" value = "${adminStat}"/>
<c:set var = "in" value = "${in_date}"/>
<c:set var = "out" value = "${out_date}"/>

 <c:out value="${stats}" />
 <c:if test = "${stats.equals('breakfast')}">
<sql:query var="list_rooms" dataSource="${DS}">
Select bType, max(total) as final
from (SELECT bType, Avg(rating) AS total
    FROM Review Rv, Reservation Rs
    where Rs.InDate BETWEEN  ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
        and Rs.OutDate Between ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
         and Rv.Room_no=Rs.Room_no
    and Rs.HotelID=Rv.HotelID and isBreakfastReview = 1
Group by bType) as T1
group by bType
order by
    total desc
limit 1;
</sql:query>
 </c:if>


 <c:if test = "${stats.equals('breakfast')}">
 <div align="center">
        <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">

             <caption><h2>Admin Stats</h2></caption>


            <tr>
           <td>sType  </td>
				 <td>rating </td></tr>
             <c:forEach var="admin" items="${list_rooms.rowsByIndex}">
               <tr>

				<td>${admin[0]}  </td>
				 <td>${admin[1]}  </td>


               </tr>


            </c:forEach>


        </table>
    </div>
  </c:if>


 <c:if test = "${stats.equals('service')}">
<sql:query var="list_rooms" dataSource="${DS}">
Select sType, max(total) as final
from (SELECT sType, Avg(rating) AS total
    FROM Review Rv, Reservation Rs
    where Rs.InDate BETWEEN  ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
        and Rs.OutDate Between ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
         and Rv.Room_no=Rs.Room_no
    and Rs.HotelID=Rv.HotelID and isServiceReview = 1
Group by sType) as T1
group by sType
order by
    total desc
limit 1;
</sql:query>
 </c:if>


 <c:if test = "${stats.equals('service')}">
 <div align="center">
        <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">
          <tr>
           <td>sType  </td>
				 <td>rating </td></tr>
             <caption><h2>Admin Stats</h2></caption>

             <c:forEach var="admin" items="${list_rooms.rowsByIndex}">
               <tr>

				<td>${admin[0]}  </td>
				 <td>${admin[1]}  </td>


               </tr>


            </c:forEach>


        </table>
    </div>
  </c:if>

 <c:if test = "${stats.equals('rooms')}">
<sql:query var="list_rooms" dataSource="${DS}">
Select T.rType, max(total) as final
from (SELECT Avg(rating) AS total, R.rType
    FROM Review Rv, Reservation Rs, Room R
    where Rs.InDate BETWEEN  ? and ?
     <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
        and Rs.OutDate Between ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
         and Rv.Room_no=Rs.Room_no
    and Rs.HotelID=Rv.HotelID and Rv.HotelID=R.HotelID and Rv.Room_no=R.Room_no and Rv.HotelID=R.HotelID and isRoomReview = 1
Group by rType) as T
group by T.rType
order by
    total desc
limit 1;
</sql:query>
 </c:if>


 <c:if test = "${stats.equals('rooms')}">
 <div align="center">
        <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">

             <caption><h2>Admin Stats</h2></caption>
           	<tr>

				<td>rType  </td>
				 <td>rating </td>


               </tr>
             <c:forEach var="admin" items="${list_rooms.rowsByIndex}">
               <tr>

				<td>${admin[0]}  </td>
				 <td>${admin[1]}  </td>


               </tr>


            </c:forEach>


        </table>
    </div>
  </c:if>


 <c:if test = "${stats.equals('custom')}">
<sql:query var="list_rooms" dataSource="${DS}">
select C.name,
    C.CID,
    SUM(TotalAmt) as TOTAL
        from Make_Reservation as T1
        INNER JOIN Customer C
        ON T1.CID = C.CID
        where  T1.resdate BETWEEN  ? and ?
        <sql:param value = "${in}"/>
        <sql:param value = "${out}"/>
        Group by CID
        Order by TOTAL DESC limit 5;
</sql:query>
 </c:if>


 <c:if test = "${stats.equals('custom')}">
 <div align="center">
        <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">

             <caption><h2>Admin Stats</h2></caption>

             <c:forEach var="admin" items="${list_rooms.rowsByIndex}">
               <tr>

				<td>${admin[0]}  </td>
				 <td>${admin[1]}  </td>


               </tr>


            </c:forEach>


        </table>
    </div>
  </c:if>


</body>
</html>