package mp3browser;

import java.io.*;
import java.sql.*;
import java.net.URL;
import javax.servlet.ServletException;
import javax.servlet.http.*;

/**
 * @version 	1.0
 * @author
 * teemaks on mp3 brauser, kus kasutajad saavad vaadata just neile lubatud kategooria lugusid
 * igal kasutajal on mingid kindlad kategooriad (rock, techno jne)
 */
public class ActionServlet extends HttpServlet {

    private Connection con;
    private Statement stmt;
    private String user;

    public ActionServlet() {
        dbConnect("admin", "admin");
    }

//        GET- meetodi k2ivitamine
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
		throws ServletException, IOException {

        resp.setContentType("text/html; charset=windows-1257");
        printHtmlHeader(resp);
        printHtmlEnding(resp);
        resp.getWriter().close();

	}

	public void doPost(HttpServletRequest req, HttpServletResponse resp)
		throws ServletException, IOException {

        String formType = req.getParameter("formType");

        if (formType.equalsIgnoreCase("loginForm")) {

            String login = req.getParameter("username");
            String passwd = req.getParameter("passwd");

//            kasutaja autentimine, mille j2rel suunatakse otsinguvormi
            if (userAuthorized(login, passwd)) {
                resp.sendRedirect("searchForm.jsp");
            }
            else {
                resp.sendRedirect("login_error.jsp");
            }
        }
        else if (formType.equalsIgnoreCase("searchForm")) {
            if (con == null && stmt == null) {
                dbConnect("admin", "admin");
            }
            else {
                try {
                	String artist = req.getParameter("artist");
                	String title = req.getParameter("title");
                	String album = req.getParameter("album");
                	String category = req.getParameter("category");
                	
                	String queryString = 
                	"SELECT A.artist, A.title, A.album, C.name FROM Mp3 A, "+
                	"Usercat B, Category C WHERE A.catid = B.catid AND "+
                	"B.catid = C.id AND B.userid = '"+user+"'";
                	
                	if (artist.length() != 0) {
                		queryString = queryString + " AND A.artist='"+artist+"'";
                	}
                	if (title.length() != 0) {
                		queryString = queryString + " AND A.title='"+title+"'";
                	}
                	if (album.length() != 0) {
                		queryString = queryString + " AND A.album='"+album+"'";
                	}
                	if (category.length() != 0) {
                		queryString = queryString + " AND C.name='"+category+"'";
                	}
                	
                    ResultSet rs = stmt.executeQuery(queryString);
                    
                    resp.setContentType("text/html; charset=windows-1257");
                    printHtmlHeader(resp);
                    printHtmlBody(resp, rs);
                    printHtmlEnding(resp);
                }
                catch (SQLException sqlex) {
                	printSqlEx(sqlex);
                }
            }

        }

	}

    public void printHtmlHeader(HttpServletResponse response) throws java.io.IOException{
        PrintWriter out = response.getWriter();
        out.println("<HTML><HEAD><TITLE>");
        out.println("<META http-equiv=\"Content-Type\" content=\"text/html; charset=Cp1257\">");
        out.println("<META http-equiv=\"Content-Style-Type\" content=\"text/css\">");
        out.println("<LINK href=\"theme/Master.css\" rel=\"stylesheet\" type=\"text/css\">");
        out.println("<TITLE>");
        out.println("Mp3 Otsing");
        out.println("</TITLE></HEAD><BODY>");
    }

    public void printHtmlEnding(HttpServletResponse response) throws java.io.IOException{
        PrintWriter out = response.getWriter();
        out.println("<hr>");
        out.println("</BODY></HTML>");
    }

//    otsingutulemuste v2ljastamine
    public void printHtmlBody(HttpServletResponse response, ResultSet rs)
    throws java.io.IOException, SQLException {
        PrintWriter out = response.getWriter();
        out.println("<H2> Otsingu tulemused</H2>");
        out.println("<table border=1>");
        out.println("<th>Artist</th>");
        out.println("<th>Title</th>");
        out.println("<th>Album</th>");
        out.println("<th>Category</th>");
        out.println("<tr>");
        out.println("</tr>");
        while (rs.next()){
          out.println("<tr>");
          out.println("<td>"+rs.getString(1)+"</td>");
          out.println("<td>"+rs.getString(2)+"</td>");
          out.println("<td>"+rs.getString(3)+"</td>");
          out.println("<td>"+rs.getString(4)+"</td>");
          out.println("</tr>");
        }
        out.println("</table>");
    }

    private boolean userAuthorized(String login, String passwd) {
    	ResultSet rs = null;
    	
    	try {
	    	rs = stmt.executeQuery(
    		"SELECT * FROM Mp3user WHERE id='"+login+"' AND passwd='"+passwd+"'");
    	
	    	if(rs.next()) {
	    		user = login;
		    	return true;
    		}
	    	else {
	    		return false;
	    	}
    	} catch (SQLException sqlex) {
    		printSqlEx(sqlex);
    		return false;
    	}
    }

//    andmebaasi yhendamine
    public void dbConnect(String user, String passwd) {

        try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //  pyye saada ligi draiverile

        }
        catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
        }

//        yhenduse loomine baasiga
        try {
                 con = DriverManager.getConnection("jdbc:odbc:mp3browser", user, passwd);
                 //con = DriverManager.getConnection("jdbc:odbc:mp3browser");
                 stmt = con.createStatement();
        }
        catch(SQLException sqlex) {
        	printSqlEx(sqlex);
        }
    }

//   andmehoidla sulgemine
    public void dbDisconnect(){

        try{
                stmt.close();
                con.close();
        }
        catch(SQLException sqlex) {
        	printSqlEx(sqlex);
        }
    }

//    meetod v2ljastab veateated vastavas v2ljakutses
    private void printSqlEx(SQLException sqlex) {
        System.out.println("\n--- SQLException viga ---\n");
        while (sqlex != null) {
            System.out.println("Message:   " + sqlex.getMessage ());
            System.out.println("SQLState:  " + sqlex.getSQLState ());
            System.out.println("ErrorCode: " + sqlex.getErrorCode ());
            sqlex = sqlex.getNextException();
            System.out.println("");
        }
    }

}
