/*
 * Created on 2006-01-11
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package mrPack.web;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import mrPack.model.Oferta;

/**
 * @author 112
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class WyswietlBaze extends HttpServlet {

    DataSource dataSource = null;

    private static final long serialVersionUID = 1L;

    Connection con = null;

    ArrayList lista = new ArrayList();

    public void init() throws ServletException {
         try {
            InitialContext ctx = new InitialContext();
            Context envContext = (Context) ctx.lookup("java:comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/myDerby");
        } catch (Exception exc) {
            throw new ServletException("Nie ustanowiono połaczenia z bazą", exc);
        }
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

         try {
         con = dataSource.getConnection();
         } catch (SQLException e2) {
         // TODO Auto-generated catch block
         e2.printStackTrace();
         }
        String nameMiasto = request.getParameter("nameMiasto");
        String namePanstwo = request.getParameter("namePanstwo");
        String nameRodzaj = request.getParameter("nameRodzaj");

        String query = "select distinct biuro.oferta.idOferta, biuro.panstwo.namePanstwo, biuro.miasto.nameMiasto, "
                + "biuro.rodzaj.nameRodzaj, biuro.oferta.cena, biuro.oferta.dwyjazd, biuro.oferta.opis, "
                + "biuro.oferta.dpowrot from biuro.oferta, biuro.miasto, biuro.panstwo, biuro.rodzaj where "
                + "biuro.oferta.miasto = biuro.miasto.idMiasto and biuro.oferta.panstwo ="
                + " biuro.panstwo.idPanstwo and biuro.oferta.rodzaj = biuro.rodzaj.idRodzaj";
        if (!nameMiasto.equals("-dowolne-") || nameMiasto == null)
            query += " and biuro.miasto.nameMiasto like '" + nameMiasto + "'";
        if (!namePanstwo.equals("-dowolne-") || namePanstwo == null)
            query += " and biuro.panstwo.namePanstwo LIKE '" + namePanstwo + "'";
        if (!nameRodzaj.equals("-dowolne-") || nameRodzaj == null)
            query += " and biuro.rodzaj.nameRodzaj LIKE '" + nameRodzaj + "'";

//      query += ";";
//      System.out.println(query);
        Statement st = null;
        ResultSet rs = null;

        try {
            st = con.createStatement();
            rs = st.executeQuery(query);
            System.out.println(rs.wasNull());
            String wyjazd = "";
            String powrot = "";
            
            while (rs.next()) {
                wyjazd = (rs.getDate("dwyjazd")).toString();
                powrot = rs.getDate("dpowrot").toString();
                Oferta o = new Oferta(rs.getInt("idOferta"), rs
                        .getString("namePanstwo"), rs.getString("nameMiasto"),
                        rs.getString("nameRodzaj"), rs.getInt("cena"), wyjazd,
                        powrot, rs.getString("opis"));
                System.out.println("data " + wyjazd);
                lista.add(o);
            }

            rs.close();
            st.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            System.out.println("jest elementów" + lista.size());
            if (lista.size() == 0) {
                RequestDispatcher view = request
                        .getRequestDispatcher("brak.jsp");
                view.forward(request, response);
            } else {
                request.setAttribute("styles", lista);
                request.setAttribute("query", query);
                RequestDispatcher view = request
                        .getRequestDispatcher("wynik.jsp");
                view.forward(request, response);
            }
            lista.clear();
        }
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws IOException, ServletException {

        doPost(request, response);

    }

}