import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.Object.*;
import java.sql.*;

// servleti puhul kus luua ühendus?
public class EMaterjalid extends HttpServlet{
	private int ID=0;
	private int LK=0;
	Connection cn;
	
	public EMaterjalid(){
		try{
		}catch(Exception e){
			System.out.println("v1:"+e);
		}		
	}
	private void kuvaNimekiri(PrintWriter pw, HttpServletRequest kysimus){
		String lause;
		ResultSet rs;
		Statement st;
		if(LK==0)
		{
			try{
				
				// Kõik kategooriad
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");			
				cn=DriverManager.getConnection("jdbc:odbc:baas", "", "");			
				st=cn.createStatement();
				
				lause="SELECT * FROM teema WHERE vanem="+ID;
				rs=st.executeQuery(lause);					
				String sisu="<h1><font face='System' color='White'>Pealkiri</font></h1><br/><center><table border=1 width=800>\n"+
				"<tr><td align=center><font face='System' color='White'>Teema</font></td></tr>\n";				
			
				while(rs.next()){
						sisu+="<tr>\n"+
							"<td width='700'>"+
							"<font face='System' color='White'>";						
							String nr=rs.getString("ID");
							String vanem=rs.getString("vanem");
							sisu+="<img src=/fun1.gif><a href='"+kysimus.getRequestURI()+"?id="+nr+"' target=# name=#>"+
							rs.getString("teema")+
							"</a></font>"+
							"</td><td>"+
							
							"</td>"+
						"\n</tr>\n";
				}			
				sisu+="</table><a href="+kysimus.getRequestURI()+"?id="+ID+"&lk=1>Lisa uus teema</a></center>";
				
				
				// Kuvab kõik failid antud kategoorias
				st=cn.createStatement();			
				lause="select count(teema) as nr from ebook where teema="+ID;			
				rs=st.executeQuery(lause);
				rs.next();			
				int arv=Integer.parseInt(rs.getString("nr"));
				if(arv==0){				
				}else{				
					st=cn.createStatement();			
					lause="SELECT ebook.id, ebook.teema ,ebook.pealkiri as nimi, ebook.asukoht,ebook.avaldatud, autorid.perekonnanimi as perek, autorid.eesnimi as eesn, kirjastused.kirjastus,formaadid.formaat FROM ebook, autorid,kirjastused, formaadid WHERE autorid.id=ebook.autor and kirjastused.ID=ebook.kirjastus and formaadid.ID=ebook.formaat and ebook.teema="+ID+";";
					rs=st.executeQuery(lause);
					sisu+="<center><table border=1 width=800>\n<tr>"+
					"<td align=center><font face='System' color='White'>Fail</font></td>"+
					"<td align=center><font face='System' color='White'>Autor</font></td>"+
					"<td align=center><font face='System' color='White'>Avaldatud</font></td>"+
					"<td align=center><font face='System' color='White'>Kirjastus</font></td>"+
					"<td align=center><font face='System' color='White'>Formaat</font></td>"+
					"</tr>\n";
					while(rs.next()){
							sisu+="<tr>\n"+
								"<td width='400'><font face='System' color='White'>\n";
								String asukoht=rs.getString("asukoht");
								sisu+="<a href='"+asukoht+"' target=# name=#>"+
								rs.getString("nimi")+
								"</a>\n"+
								"</font></td><td><font face='System' color='White'>\n"+
								rs.getString("eesn")+" "+rs.getString("perek")+
								"</font></td>\n<td><font face='System' color='White'>\n"+
								rs.getString("avaldatud")+
								"</font></td>\n<td><font face='System' color='White'>\n"+
								rs.getString("kirjastus")+
								"</font></td>\n<td align=center><font face='System' color='White'>\n"+
								rs.getString("formaat")+
								"</font></td>"+
							"\n</tr>\n";
					}
					sisu+="</table>"+
					//"<a href="+kysimus.getRequestURI()+"?id="+ID+"&lk=1>Lisa uus teema</a> | "+
					"<a href="+kysimus.getRequestURI()+"?id="+ID+"&lk=2>Lisa uus allikas</a></center>";			
				}
				
				
				pw.println(sisu);			
				cn.close();
			}catch(Exception e){pw.println("viga(kuvaNimekiri):"+e);}
		}else if(LK==1){
			// Lisa teema			
			pw.println("<form name='f1' action=/examples/servlet/EMaterjalidAdmin?id=lisa&vanem="+ID+" method=post><font face='System' color='White'>Uus teema: </font><input type=text name='teemaNimi'>");
			pw.println("<input type=submit name='saada' value='Lisan'></form>");
			pw.pritln("<a href='""'></a>");
		}else if(LK==2){
			// Lisa uus materjal
			pw.println("<center><table>\n");
			//pealkiri,asukoht,aeesn,aperek,avaldatud,kirjastus,formaat
				pw.println("<form name=f2 action=/examples/servlet/EMaterjalidAdmin?id=lisa&vanem="+ID+" method=post>\n");
				pw.println("<tr><td>Pealkiri :</td><td>");
				pw.println("<input type=text name=pealkiri>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("Asukoht :</td><td>");
				pw.println("<input class=formtext type='file' name='asukoht'>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("Autor</td></tr>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("Eesnimi :</td><td>");
				pw.println("<input type=text name=aeesn>\n");
			pw.println("</td><td>\n");
				pw.println("Perekonnanimi :</td><td>");
				pw.println("<input type=text name=aperek>\n");
			pw.println("</td></tr><tr><td>\n");			
				pw.println("Avaldatud</td><td>");
				pw.println("<input type=text name=avaldatud>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("Kirjastus</td><td>");
				pw.println("<input type=text name=kirjastus>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("Formaat :</td><td>");
				pw.println("<input type=text name=formaat>\n");
			pw.println("</td></tr><tr><td>\n");
				pw.println("<input type=submit value=saada>\n");
			pw.println("</td></tr><tr><td>\n");
			pw.println("</form>");
			pw.println("</table></center>\n");
		}
	}	
	//*****************************************//
	public void doGet(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException{	
		PrintWriter pw=vastus.getWriter();
		try{			
			vastus.setContentType("text/html");			
			pw.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">");
			pw.println("<HTML><HEAD>");
			pw.println("<META http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\" />");		
			pw.println("<TITLE>eMaterjalid</TITLE></HEAD>\n");
			pw.println("<BODY BGCOLOR=#535772 LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0 LINK=#FFFFFF VLINK=#FFFFFF><CENTER>");				
			
			String s1=(kysimus.getParameter("id"));
			String s2=(kysimus.getParameter("lk"));
			if(s1==null){
				ID=0;	
	        }else{
	        	ID=Integer.parseInt(s1);
			}
			if(s2==null){
				LK=0;
			}else{
				LK=Integer.parseInt(s2);
			}
			kuvaNimekiri(pw,kysimus);
			
			pw.println("</CENTER></BODY></HTML>");		
		}catch(Exception e){
			pw.println("viga(doGet):"+e);
		}
	}
	//*****************************************//
}