package negocio;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.xml.XMLConstants;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Source;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.DatabaseSequenceFilter;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.FilteredDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlWriter;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;


public class Xml {
	
	public static void generateXML(String driverName,String urlDB,String userDB,String passwordDB,String schemaBD,String nameXML, Plantilla p) throws SQLException {

		Connection conn=null;
	
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver)Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passwordDB);
			IDatabaseConnection connection = new DatabaseConnection(conn, schemaBD);
	
			DatabaseSequenceFilter filter = new DatabaseSequenceFilter(connection);
			IDataSet datasetAll = new FilteredDataSet(filter, connection.createDataSet());
			QueryDataSet partialDataSet = new QueryDataSet(connection);
	
			String[] listTableNames = filter.getTableNames(datasetAll);
			for (int i = 0; i < listTableNames.length; i++) {
				final String tableName = listTableNames[i];
				// Specify the SQL to run to retrieve the data
				if (tableName.equals("PLANTILLA"))
					partialDataSet.addTable(tableName,"select * from Plantilla where id_plantilla = '" + p.getIdPlantilla() + "'");
				else if (tableName.equals("PROYECTO"))
					partialDataSet.addTable(tableName,"select * from Proyecto where id_plantilla = '" + p.getIdPlantilla() + "'");
				else if (tableName.equals("BLOQUE_CONSTRUCTIVO"))
					partialDataSet.addTable(tableName,"select * from Bloque_Constructivo where id_plantilla = '" + p.getIdPlantilla() + "'");
				else
					partialDataSet.addTable(tableName);
			}
		            
			//IDataSet dataset = connection.createDataSet();
            if (nameXML.endsWith(".xml"))
            	XmlDataSet.write(partialDataSet, new FileOutputStream(nameXML));
			else
				XmlDataSet.write(partialDataSet, new FileOutputStream(nameXML+".xml"));
            conn.close();        
		
		} catch (Exception exc) {
			exc.printStackTrace();
		
		} finally{
			conn.close();
		}
	}
	
	public static void generateXML2(String driverName,String urlDB,String userDB,String passwordDB,String schemaBD,String nameXML) throws SQLException {

		Connection conn=null;
	
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver)Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passwordDB);
			IDatabaseConnection connection = new DatabaseConnection(conn, schemaBD);
	
			DatabaseSequenceFilter filter = new DatabaseSequenceFilter(connection);
			IDataSet datasetAll = new FilteredDataSet(filter, connection.createDataSet());
			QueryDataSet partialDataSet = new QueryDataSet(connection);
	
			String[] listTableNames = filter.getTableNames(datasetAll);
			for (int i = 0; i < listTableNames.length; i++) {
				final String tableName = listTableNames[i];
				// Specify the SQL to run to retrieve the data
				partialDataSet.addTable(tableName);
			}
		
			FlatXmlWriter datasetWriter;
			// Specify the location of the flat file(XML)
			if (nameXML.endsWith(".xml"))
				datasetWriter = new FlatXmlWriter(new FileOutputStream(nameXML));
			else
				datasetWriter = new FlatXmlWriter(new FileOutputStream(nameXML+".xml"));
			
			// Export the data
			datasetWriter.write(partialDataSet);
		
		} catch (Exception exc) {
			exc.printStackTrace();
		
		} finally{
			conn.close();
		}
	}
	
	public static boolean createData(String driverName,String urlDB,String userDB,String passworDB,String nameXML) throws SQLException {
		
		Connection conn = null;
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver)Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passworDB);
			IDatabaseConnection connection = new DatabaseConnection(conn);
			
			DatabaseOperation.INSERT.execute(connection,new XmlDataSet(new FileInputStream(nameXML)));
			
			return false;
	
		} catch (Exception exc) {
			//exc.printStackTrace();
			return true;
		} finally{
			conn.close();
		}
	}
	
	public static void createData2(String driverName,String urlDB,String userDB,String passworDB,String nameXML) throws SQLException {
	
		Connection conn = null;
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver)Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passworDB);
			IDatabaseConnection connection = new DatabaseConnection(conn);
			
			DatabaseOperation.INSERT.execute(connection,new FlatXmlDataSet(new FileInputStream(nameXML)));
	
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally{
			conn.close();
		}
	}
	
	public static void deleteData(String driverName,String urlDB,String userDB,String passworDB,String nameXML) throws SQLException {
		Connection conn = null;
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver)Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passworDB);
			IDatabaseConnection connection = new DatabaseConnection(conn);
			DatabaseOperation.DELETE.execute(connection, new FlatXmlDataSet(new FileInputStream("D:\\" + nameXML + ".xml")));
	
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally{
			conn.close();
		}
	}

	public static void ValidaXMLcontraXSD(String xml, String xsd) throws ParserConfigurationException, SAXException, IOException {
	//	 parse an XML document into a DOM tree
	    DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
	    Document document = parser.parse(new File(xml));
	
	    // create a SchemaFactory capable of understanding WXS schemas
	    SchemaFactory factory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
	
	    // load a WXS schema, represented by a Schema instance
	    Source schemaFile = new StreamSource(new File(xsd));
	    Schema schema = factory.newSchema(schemaFile);
	
	    // create a Validator instance, which can be used to validate an instance document
	    Validator validator = schema.newValidator();
	
	    // validate the DOM tree
	    try {
	        validator.validate(new DOMSource(document));
	    } catch (SAXException e) {
	        // instance document is invalid!
	    }
	}
}
