import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import org.w3c.dom.*;
import java.text.DateFormat;

public class uudisedXML extends HttpServlet{

   public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException{
 	response.setContentType("application/xml");
 	PrintWriter output=response.getWriter();

	try{
	   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection connection=DriverManager.getConnection("jdbc:odbc:uudised","","");
   	   Statement st=connection.createStatement();	
  	   Statement st2=connection.createStatement();	
	   ResultSet rs=st.executeQuery("select * from messages order by id desc limit 10");
	   
	   Document d=DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
       Element juur=d.createElement("teated");
  	   d.appendChild(juur);
	   Date dAeg;
	   DateFormat df = DateFormat.getDateInstance(DateFormat.FULL);

	   while(rs.next()){
      		Element uudis=d.createElement("uudis");
      		Element pealkiri=d.createElement("pealkiri");
		    pealkiri.appendChild(d.createTextNode(rs.getString("header")));
			Element sisu=d.createElement("sisu");
		    sisu.appendChild(d.createTextNode(rs.getString("contents")));
			Element autor=d.createElement("autor");
			autor.appendChild(d.createTextNode(rs.getString("msgauthor")));
			Element aeg;

			uudis.appendChild(pealkiri);
			uudis.appendChild(sisu);
			uudis.appendChild(autor);

			dAeg=rs.getDate("time");
			if (dAeg!=null){
				aeg=d.createElement("aeg");
				aeg.appendChild(d.createTextNode(df.format(dAeg)));
				uudis.appendChild(aeg);
			} 
	
			Element tyyp=d.createElement("tyyp");
			tyyp.appendChild(d.createTextNode(""+rs.getInt("type")));
			uudis.appendChild(tyyp);
			int id=rs.getInt("id");
			ResultSet rs2=st2.executeQuery("select * from comments where msgid='"+id+"'");
	   		while(rs2.next()){
    			Element kommentaar=d.createElement("kommentaar");
				Element kommenteerija=d.createElement("nimi");
				kommenteerija.appendChild(d.createTextNode(rs2.getString("comauthor")));
				Element teade=d.createElement("sisu");
				teade.appendChild(d.createTextNode(rs2.getString("comcontents")));

				kommentaar.appendChild(kommenteerija);
				kommentaar.appendChild(teade);

				Element comaeg;
				dAeg=rs2.getDate("comtime");
				if (dAeg!=null){
					comaeg=d.createElement("aeg");
					comaeg.appendChild(d.createTextNode(df.format(dAeg)));
					kommentaar.appendChild(comaeg);
				} 

				uudis.appendChild(kommentaar);
			}
      		juur.appendChild(uudis);
       }

       Transformer t=TransformerFactory.newInstance().newTransformer();
       t.transform(new DOMSource(d), new StreamResult(output));
	   connection.close();
	}catch(Exception e ){
		output.println("<h2>VIGA</h2>"+e.getMessage());
	}
   }
}