/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


package data;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.sql.*;




/**
 *
 * @author Administrator
 */
public class GetAvailableTickets extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, SQLException{
        response.setContentType("text/xml;charset=UTF-8");
        PrintWriter out = response.getWriter();


        String selectedCity = request.getParameter("city");
        String selectedCinema = request.getParameter("cinema");
        String selectedDate = request.getParameter("date");
        String selectedFilm = request.getParameter("film");
        String selectedTime = request.getParameter("time");
        String sqlStatement = "";
        String extraCondition = "";
        String result = "";

        try
        {
            ResourceBundle config = ResourceBundle.getBundle("config");
            // get a DB connection
            String dbURL = config.getString("dbURL");
            String username = config.getString("username");
            String password = config.getString("password");

            Connection connection = DriverManager.getConnection(dbURL, username, password);
            Statement statement = connection.createStatement();

            StringBuffer jsonSB = new StringBuffer();

            ResourceBundle dataSQLQueries = ResourceBundle.getBundle("data.SQLQueries");

            sqlStatement = dataSQLQueries.getString("Query.availale.tickets");

            extraCondition = dataSQLQueries.getString("Condition.city") + SQLUtil.addQuotes(selectedCity) + dataSQLQueries.getString("Condition.cinema") + SQLUtil.addQuotes(selectedCinema) + dataSQLQueries.getString("Condition2.date") + SQLUtil.addQuotes(selectedDate) + dataSQLQueries.getString("Condition.film") + SQLUtil.addQuotes(selectedFilm) +  dataSQLQueries.getString("Condition.time") + SQLUtil.addQuotes(selectedTime) ;
            ResultSet resultSet = statement.executeQuery(sqlStatement + extraCondition);





            
           
            jsonSB.append("\"status\" : \"success\", ");
            while (resultSet.next())
            {
                 jsonSB.append("\"Show_id\" : " + resultSet.getString(1) + ", ");
                 jsonSB.append("\"Show_date\" : \"" + resultSet.getString(2) + "\", ");
                 jsonSB.append("\"Show_time\" : \"" + resultSet.getString(3) + "\", ");
                 jsonSB.append("\"Show_price\" : " + resultSet.getString(4) + ", ");
                 jsonSB.append("\"CinemaName\" : \"" + resultSet.getString(5) + "\", ");
                 jsonSB.append("\"Capacity\" : " + resultSet.getString(6) + ", ");
                 jsonSB.append("\"AvailableTickets\" : " + resultSet.getString(7) + ", ");
                 jsonSB.append("\"Film\" : \"" + resultSet.getString(8) + "\"");
                 
            }
            result = XMLUtil.getFormat(jsonSB.toString());
            out.println(result);
            resultSet.close();
            statement.close();
            connection.close();
            out.close();
        }
        catch(SQLException e)
        {
            out.println(XMLUtil.getFormat(XMLUtil.errorHandler(e.getMessage())));

        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            //Logger.getLogger(GetFilmsList.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException  {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            //Logger.getLogger(GetFilmsList.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 }