package es.dalocar.software.daos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import junit.framework.TestCase;

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import es.dalocar.software.beans.Factura;
import es.dalocar.software.beans.FacturaId;
import es.dalocar.software.beans.LineaFactura;
import es.dalocar.software.beans.LineaFacturaId;

public class LineaFacturaDAOTest extends TestCase {

	
	public static Logger log = Logger.getLogger(FacturaDAOTest.class);
	
	LineaFacturaDAO dao;
	FacturaDAO daoFactura;
	ApplicationContext contexto;
	
	/* (non-Javadoc)
	 * @see junit.framework.TestCase#setUp()
	 */
	protected void setUp() throws Exception {
		super.setUp();
	
		contexto = new ClassPathXmlApplicationContext("applicationContext.xml");
		dao = (LineaFacturaDAO)contexto.getBean("lineaFacturaDAO");
		daoFactura = (FacturaDAO)contexto.getBean("facturaDAO");
	}

	

	/**
	 * Test method for {@link es.dalocar.software.daos.ClienteDAO#findById(java.lang.Integer)}.
	 */
	public void testFindById() {
		/*Cliente cliente = (Cliente) dao.findById(new Integer(1));
		System.out.println(cliente.toString());*/
		LineaFacturaId idLinea = new LineaFacturaId(new Integer(1),new Integer(1), new Integer(2009));
		LineaFactura lineaFactura= (LineaFactura) dao.findById(idLinea);
		if(lineaFactura != null) {
			log.debug(lineaFactura.toString());
			
		}
		
		assertNotNull(lineaFactura);
	}
	
	/**
	 * Test method for {@link es.dalocar.software.daos.ClienteDAO#findById(java.lang.Integer)}.
	 */
	public void testInsert() {
		
		dao.attachDirty(getLineaFactura());		

	}

	
	public LineaFactura getLineaFactura() {
		
		LineaFactura aux = new LineaFactura();		
		LineaFacturaId id = new LineaFacturaId(new Integer(1),new Integer(1), new Integer(2009));	
		
		aux.setId(id);
		aux.setCantidad(new Double(10.0));
		aux.setDescripcion("Horas de limpieza");
		aux.setIva(new Double(16.0));
		aux.setPrecio(new Double(12.0));	
		
		return aux;		
		
	}
	
	public void recalculateIvaAndTotal() {
		
		List<Factura> listaFacturas = daoFactura.findAll();
		for (Factura factura : listaFacturas) {
			double total = 0.0;
			double iva =0.0;
			List<LineaFactura> listaLineas = dao.findByFactura(factura);
			for (LineaFactura linea : listaLineas) {
				double totalLinea = linea.getCantidad().doubleValue() * linea.getPrecio().doubleValue();
				double ivaLinea = totalLinea * linea.getIva() / 100D;
				iva += ivaLinea;
				total += totalLinea + ivaLinea;
				if(factura.getId().getNumero().equals(new Integer(1)) && factura.getId().getAnyo().equals(new Integer(2009)))
					System.out.println(linea.getId().getNumerolinea()+","+totalLinea + ";" +  ivaLinea);
			}
			factura.setTotalIva(new Double(iva));
			factura.setImportefactura(new Double(total));
			daoFactura.saveOrUpdate(factura);
			if(factura.getId().getNumero().equals(new Integer(1)) && factura.getId().getAnyo().equals(new Integer(2009)))
				System.out.println(factura.getImportefactura()+"," + factura.getTotalIva());
		}
		
	}
	
	public void testPopulateFromODBC() {

		// Suggestion: Load these from a properties object.
		String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
		String URL = "jdbc:odbc:FACTURACION";

		// Register the Native JDBC driver. If we can't
		// register the driver, the test can't continue.
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			System.out.println("Driver failed to register.");
			System.out.println(e.getMessage());
			System.exit(1);
		}

		Connection c = null;
		Statement s = null;

		// 1) First, this program handles creating the table that
		// will be used by our prepared statements later.
		try {
			// Create the connection properties
			Properties properties = new Properties();

			// Connect to the local iSeries 400 database
			c = DriverManager.getConnection(URL, properties);
			PreparedStatement ps = c.prepareStatement("SELECT * FROM Linea_Factura");

			// Run an SQL query on the table.
			ResultSet rs = ps.executeQuery();
			// Display all the data in the table
			LineaFactura factura = null;
			while (rs.next()) {
				factura = new LineaFactura();
				Integer NumeroLinea=Integer.valueOf(rs.getInt(1));	
				Integer NumeroFactura=Integer.valueOf(rs.getInt(2));
				Double cantidad = Double.valueOf(rs.getDouble(3));
				String descripcion = rs.getString(4);
				Double precio  = Double.valueOf(rs.getDouble(5));
				Double IVA = Double.valueOf(rs.getDouble(6));
				Integer any_factura=Integer.valueOf(rs.getInt(7));
				
				Factura fact = daoFactura.findById(new FacturaId(NumeroFactura, any_factura));
				factura.setFactura(fact);
				factura.setId(new LineaFacturaId(NumeroLinea,NumeroFactura, any_factura));
				factura.setDescripcion(descripcion);
				factura.setCantidad(cantidad);
				factura.setPrecio(precio);
				factura.setIva(IVA);
				
				dao.persist(factura);				
			}

			
		} catch (SQLException sqle) {
			System.out.println("Database processing has failed.");
			System.out.println("Reason: " + sqle.getMessage());
		}

	}
}
