package ee.tpu.hk.links;

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

import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import org.w3c.dom.*;

/**
 *	Klass XML-i juppide töötlemiseks.
 *	2003 Haapsalu Kolledz
 *	@author Berit Hiieväli
 */
public class  XMLfunc
{
	private db dbConn = new db();
								
	public XMLfunc()
	{		
	}
	
	/**
	 *	Plaatide tabel xml  faili.
	 *	Ainult kasutaja plaadid  
	 */		
	public void fromDatabaseToXML( int userId, String fileanme)
	{
	
			try
			{
				
				Document d=DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
			    Element juur=d.createElement("ADB");
			    d.appendChild(juur);
			    
			    String lause ="SELECT  id, artist, title ,  zanr, aasta, comm  FROM  `plaadid` WHERE  `user_id`  = '"+userId+"' ORDER  BY  `id`  ASC ";

			    ResultSet rs1 = dbConn.executeQuery(lause);
				
		        while(rs1.next())
	    		{
  
				      Element e=d.createElement("plaat");
				      e.setAttribute("id",""+rs1.getInt("id"));
				      e.setAttribute("artist",rs1.getString("artist"));
				      e.setAttribute("title",rs1.getString("title"));
				      e.setAttribute("zanr",rs1.getString("zanr"));
				      e.setAttribute("aasta",""+rs1.getInt("aasta"));
				      e.setAttribute("comm",rs1.getObject("comm").toString());
				      juur.appendChild(e);
    
				}
				 Transformer t=TransformerFactory.newInstance().newTransformer();
    			t.transform(new DOMSource(d), new StreamResult("C:/Program Files/Apache Group/Tomcat 4.1/webapps/examples/"+fileanme));	
	    	}catch (Exception e)
	    	{
	    		System.out.println(e);
	    	}
	    	
	}
	
	/**
	 *	XML fail andmebaasi.
	 *	Ainult need plaadid mida ei ole plaadid  
	 */		
	public void fromXMLToDatabase(String filename, int userId)
	{
		
		try
		{
			ResultSet rs1 =null;
			int row = 0;
			String artist, title, zanr, comm,id, aasta;
			Document d=DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(filename);
			
			Node juur, plaat, mmm;
			NamedNodeMap atributes;
			juur=(Element)d.getFirstChild();
			NodeList plaadid =juur.getChildNodes();
			
			for(int i=0; i<plaadid.getLength(); i++)
			{
				plaat = plaadid.item(i);
				
				atributes =plaat.getAttributes();
				
				
				aasta  = atributes.getNamedItem("aasta").getNodeValue();
				artist = atributes.getNamedItem("artist").getNodeValue();
				comm   = atributes.getNamedItem("comm").getNodeValue();
				id     = atributes.getNamedItem("id").getNodeValue();
				title  = atributes.getNamedItem("title").getNodeValue();
				zanr   = atributes.getNamedItem("zanr").getNodeValue();
							
				// kontrollitakse plaadi olemasolu 
				rs1 = dbConn.executeQuery("SELECT * FROM `plaadid` "+
									"WHERE  `title` LIKE '"+title.trim()+"' AND `artist` "+
									"LIKE '"+artist.trim()+"'");
				
				if (!rs1.next()) 	//kui andmebaasis ei ole selise artiti sellist plaati siis lisada
				{
					System.out.println("salvestati"+ userId);
					dbConn.updateQuery("INSERT INTO `plaadid` VALUES "+
							"('', '"+title+"', '"+artist+"', '"+aasta+"', '"+zanr+"', '"+comm+"', '"+userId+"');");
				}
			}
		}
		catch (Exception e)
		{
			System.out.println("sasa"+e);
		}
	}
}