package utiliServlet;

import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import m05bClasses.m05bFileLog;
import n05bModelosVistas.m05bAsignacion;
import n05bModelosVistas.m05bImprimible;
import n05bModelosVistas.m05bPersonaEstadisticas;
import n05bModelosVistas.m05bVehiculoV;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class CopyOfm05bExcel {
	   m05bFileLog miLog = new m05bFileLog();
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
//	public void createFileAltas2(String template, ArrayList<m05bVehiculoV> datos,int anio,
//			OutputStream salida) throws Exception {
//		short rownum;
//
//		// create a destination file
//		try {
//
//			FileInputStream in = new FileInputStream(template);
//
//			// create a new workbook object; note that the workbook
//			// and the file are two separate things until the very
//			// end, when the workbook is written to the file.
//			HSSFWorkbook wb = new HSSFWorkbook(in);
//			// create a new worksheet
//
//			// create a row object reference for later use
//			HSSFRow r = null;
//			// create a cell object reference
//			HSSFCell c = null;
//
//			HSSFCellStyle grisa = wb.createCellStyle();
//			grisa.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//			grisa.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			HSSFCellStyle zuria = wb.createCellStyle();
//			zuria.setFillForegroundColor(HSSFColor.WHITE.index);
//			zuria.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			HSSFFont font = wb.createFont();
//			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//
//			font.setColor(HSSFColor.WHITE.index);
//
//			HSSFCellStyle bold = wb.createCellStyle();
//			bold.setFillForegroundColor(HSSFColor.BLACK.index);
//			bold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			bold.setFont(font);
//
//			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
//			DocumentBuilder db = dbf.newDocumentBuilder();
//			int rowc = 1, columnc = 0;
//
//			Iterator iter = datos.iterator();
//			HSSFSheet ws;
//			int counts = 4;
//			HSSFSheet indice = wb.getSheetAt(0);
//			r = indice.getRow(1);
//			c = r.getCell(0);
//			c.setCellValue(c.getStringCellValue()+" "+anio);
//			m05bVehiculoV elemento;
//			while (iter.hasNext()) {
//
//				elemento = (m05bVehiculoV) iter.next();
//
//				// indice.addMergedRegion(new
//				// CellRangeAddress(counts,2,counts,9));
//				r = indice.createRow(counts);
//				c = r.createCell(0);
//				c.setCellValue(elemento.getDepartamento());
//				c = r.createCell(1);
//				c.setCellValue(elemento.getMatricula());
//				c = r.createCell(2);
//				c.setCellValue(elemento.getMarca());
//				c = r.createCell(3);
//				c.setCellValue(elemento.getModelo());
//				c = r.createCell(4);
//				c.setCellValue(elemento.getFechaMatricula());
//				c = r.createCell(5);
//				c.setCellValue(elemento.getFechaAlta());
//				c = r.createCell(6);
//				c.setCellValue(elemento.getRangoVehiculo());
//				c = r.createCell(7);
//				c.setCellValue(elemento.getPrecio());
//				counts++;
//
//			}
//			wb.write(salida);
//			salida.close();
//		} catch (Exception e) {
//			miLog.addTraza(e);
//			throw (e);
//		}
//	}
//	
	public void createFileAltas(String template, ArrayList<m05bVehiculoV> datos,int anio,
			OutputStream salida) throws Exception{
		createFile(template,(short)4,0,(ArrayList)datos,m05bVehiculoV.ORDENEXCELALTAS,anio+"",salida);
	}
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
	public void createFileBajas(String template, ArrayList<m05bVehiculoV> datos,int anio,
			OutputStream salida) throws Exception {
		
		createFile(template,(short)4,0,(ArrayList)datos,m05bVehiculoV.ORDENEXCELBAJAS,anio+"",salida);
//		short rownum;
//
//		// create a destination file
//		try {
//
//			FileInputStream in = new FileInputStream(template);
//
//			// create a new workbook object; note that the workbook
//			// and the file are two separate things until the very
//			// end, when the workbook is written to the file.
//			HSSFWorkbook wb = new HSSFWorkbook(in);
//			// create a new worksheet
//
//			// create a row object reference for later use
//			HSSFRow r = null;
//			// create a cell object reference
//			HSSFCell c = null;
//
//			HSSFCellStyle grisa = wb.createCellStyle();
//			grisa.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//			grisa.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			HSSFCellStyle zuria = wb.createCellStyle();
//			zuria.setFillForegroundColor(HSSFColor.WHITE.index);
//			zuria.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			HSSFFont font = wb.createFont();
//			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//
//			font.setColor(HSSFColor.WHITE.index);
//
//			HSSFCellStyle bold = wb.createCellStyle();
//			bold.setFillForegroundColor(HSSFColor.BLACK.index);
//			bold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//			bold.setFont(font);
//
//			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
//			DocumentBuilder db = dbf.newDocumentBuilder();
//			int rowc = 1, columnc = 0;
//
//			Iterator iter = datos.iterator();
//			HSSFSheet ws;
//			int counts = 4;
//			HSSFSheet indice = wb.getSheetAt(0);
//			r = indice.getRow(1);
//			c = r.getCell(0);
//			c.setCellValue(c.getStringCellValue()+" "+anio);
//			m05bVehiculoV elemento;
//			while (iter.hasNext()) {
//
//				elemento = (m05bVehiculoV) iter.next();
//
//				// indice.addMergedRegion(new
//				// CellRangeAddress(counts,2,counts,9));
//				r = indice.createRow(counts);
//				c = r.createCell(0);
//				c.setCellValue(elemento.getDepartamento());
//				c = r.createCell(1);
//				c.setCellValue(elemento.getMatricula());
//				c = r.createCell(2);
//				c.setCellValue(elemento.getMarca());
//				c = r.createCell(3);
//				c.setCellValue(elemento.getModelo());
//				c = r.createCell(4);
//				c.setCellValue(elemento.getFechaMatricula());
//				c = r.createCell(5);
//				c.setCellValue(elemento.getFechaBaja());
//				c = r.createCell(6);
//				c.setCellValue(elemento.getRangoVehiculo());
//				c = r.createCell(7);
//				c.setCellValue(elemento.getMotivoBaja());
//				counts++;
//
//			}
//			wb.write(salida);
//			salida.close();
//		} catch (Exception e) {
//			miLog.addTraza(e);
//			throw (e);
//		}
	}
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
	public void createFileAsignacionesPorVeh(String template, ArrayList<m05bAsignacion> datos,
			OutputStream salida) throws Exception {
		short rownum;
		int anio=2010;
		// create a destination file
		try {

			FileInputStream in = new FileInputStream(template);

			// create a new workbook object; note that the workbook
			// and the file are two separate things until the very
			// end, when the workbook is written to the file.
			HSSFWorkbook wb = new HSSFWorkbook(in);
			// create a new worksheet

			// create a row object reference for later use
			HSSFRow r = null;
			// create a cell object reference
			HSSFCell c = null;

			HSSFCellStyle grisa = wb.createCellStyle();
			grisa.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			grisa.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle zuria = wb.createCellStyle();
			zuria.setFillForegroundColor(HSSFColor.WHITE.index);
			zuria.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFFont font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			font.setColor(HSSFColor.WHITE.index);

			HSSFCellStyle bold = wb.createCellStyle();
			bold.setFillForegroundColor(HSSFColor.BLACK.index);
			bold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			bold.setFont(font);

			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			int rowc = 1, columnc = 0;

			Iterator iter = datos.iterator();
			HSSFSheet ws;
			int counts = 4;
			HSSFSheet indice = wb.getSheetAt(0);
			r = indice.getRow(1);
			c = r.getCell(0);
			//c.setCellValue(c.getStringCellValue()+" "+anio);
			m05bAsignacion elemento;
			while (iter.hasNext()) {

				elemento = (m05bAsignacion) iter.next();

				// indice.addMergedRegion(new
				// CellRangeAddress(counts,2,counts,9));
				r = indice.createRow(counts);
				c = r.createCell(0);
				c.setCellValue(elemento.getDepartamento());
				c = r.createCell(1);
				c.setCellValue(elemento.getCargo());
				c = r.createCell(2);
				c.setCellValue(elemento.getNombre());
				c = r.createCell(3);
				c.setCellValue(elemento.getMarca());
				c = r.createCell(4);
				c.setCellValue(elemento.getModelo());
				c = r.createCell(5);
				c.setCellValue(elemento.getMatricula());
				c = r.createCell(6);
				c.setCellValue(elemento.getFechaMatricula());
				c = r.createCell(7);
				c.setCellValue(elemento.getKilometraje());
				if(elemento.getFechaDecreto()!=null){
					c = r.createCell(8);
					c.setCellValue(elemento.getFechaDecreto());
				}
				
				if(elemento.getFechaDevolucion()!=null){
					c = r.createCell(9);
					c.setCellValue(elemento.getFechaDevolucion());
				}
				counts++;

			}
			wb.write(salida);
			salida.close();
		} catch (Exception e) {
			miLog.addTraza(e);
			throw (e);
		}
	}
	
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
	public void createFileAsignacionesFechas(String template, ArrayList<m05bAsignacion> datos,String fechaDesde,String fechaHasta,
			OutputStream salida) throws Exception {
		short rownum;
		int anio=2010;
		// create a destination file
		try {

			FileInputStream in = new FileInputStream(template);

			// create a new workbook object; note that the workbook
			// and the file are two separate things until the very
			// end, when the workbook is written to the file.
			HSSFWorkbook wb = new HSSFWorkbook(in);
			// create a new worksheet

			// create a row object reference for later use
			HSSFRow r = null;
			// create a cell object reference
			HSSFCell c = null;

			HSSFCellStyle grisa = wb.createCellStyle();
			grisa.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			grisa.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle zuria = wb.createCellStyle();
			zuria.setFillForegroundColor(HSSFColor.WHITE.index);
			zuria.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFFont font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			font.setColor(HSSFColor.WHITE.index);

			HSSFCellStyle bold = wb.createCellStyle();
			bold.setFillForegroundColor(HSSFColor.BLACK.index);
			bold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			bold.setFont(font);

		
			int rowc = 1, columnc = 0;

			Iterator iter = datos.iterator();
			HSSFSheet ws;
			int counts = 4;
			HSSFSheet indice = wb.getSheetAt(0);
			r = indice.getRow(1);
			c = r.getCell(0);
			c.setCellValue(c.getStringCellValue()+" "+fechaDesde+"-"+fechaHasta);
			m05bAsignacion elemento;
			while (iter.hasNext()) {

				elemento = (m05bAsignacion) iter.next();

				// indice.addMergedRegion(new
				// CellRangeAddress(counts,2,counts,9));
				r = indice.createRow(counts);
				c = r.createCell(0);
				c.setCellValue(elemento.getDepartamento());
				c = r.createCell(1);
				c.setCellValue(elemento.getNombre());
				c = r.createCell(2);
				c.setCellValue(elemento.getMatricula());
				
				if(elemento.getFechaDecreto()!=null){
					c = r.createCell(3);
					c.setCellValue(elemento.getFechaDecreto());
				}
				if(elemento.getFechaDevolucion()!=null){
					c = r.createCell(4);
					c.setCellValue(elemento.getFechaDevolucion());
				}
				
				c = r.createCell(5);
				c.setCellValue(elemento.getKilometraje());
				c = r.createCell(6);
				c.setCellValue(elemento.getAsigpermanente());
				c = r.createCell(7);
				c.setCellValue(elemento.getRangoVehiculo());
				c = r.createCell(8);
				c.setCellValue(elemento.getCodrangovehiculo());
				counts++;

			}
			wb.write(salida);
			salida.close();
		} catch (Exception e) {
			miLog.addTraza(e);
			throw (e);
		}
	}
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
	public void createFileAsignacionesPersonas(String template, ArrayList<m05bPersonaEstadisticas> datos,String anio,
			OutputStream salida) throws Exception {
		createFile(template,(short)4,0,(ArrayList)datos,m05bPersonaEstadisticas.ORDENINFORMEASIGNACIONPERSONAS,anio,salida);
	}
	/**
		 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida 
	 * @param template
	 * @param datos
	 * @param salida
	 * @throws Exception
	 */
	public void createFileAsignacionesVivas(String template, ArrayList<m05bAsignacion> datos,
			OutputStream salida) throws Exception {
		createFile(template,(short)4,0,(ArrayList)datos,m05bAsignacion.ORDENINFORMEASIGNACIONESVIVAS,"",salida);
	}
	/**
	 * Crear un EXCEL utilizando el XSL de Template y el XML datos enel
	 * OutputStream salida
	 * 
	 * @param template
	 * @param datos
	 * @param salida
	 * @param lang
	 * @throws Exception
	 */
	private void createFile(String template, short rownum,int colnum,ArrayList<m05bImprimible> datos,int [] orden,String anio,
			OutputStream salida) throws Exception {
		

		// create a destination file
		try {

			FileInputStream in = new FileInputStream(template);

			// create a new workbook object; note that the workbook
			// and the file are two separate things until the very
			// end, when the workbook is written to the file.
			HSSFWorkbook wb = new HSSFWorkbook(in);
			// create a new worksheet

			// create a row object reference for later use
			HSSFRow r = null;
			// create a cell object reference
			HSSFCell c = null;

			HSSFCellStyle grisa = wb.createCellStyle();
			grisa.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			grisa.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle zuria = wb.createCellStyle();
			zuria.setFillForegroundColor(HSSFColor.WHITE.index);
			zuria.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFFont font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			font.setColor(HSSFColor.WHITE.index);

			HSSFCellStyle bold = wb.createCellStyle();
			bold.setFillForegroundColor(HSSFColor.BLACK.index);
			bold.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			bold.setFont(font);

		
			int rowc = 1, columnc = 0;

			Iterator iter = datos.iterator();
			HSSFSheet ws;
			int counts = rownum;
			HSSFSheet indice = wb.getSheetAt(0);
			r = indice.getRow(1);
			c = r.getCell(0);
			c.setCellValue(c.getStringCellValue()+" "+anio);
			m05bImprimible elemento;
			Object campo;
			while (iter.hasNext()) {
				elemento = (m05bImprimible) iter.next();
				r = indice.createRow(counts);
				for(int i=0; i<orden.length;i++){					
					c = r.createCell(i);
					campo=elemento.get(orden[i]);
					if (campo instanceof Integer) 
						c.setCellValue((Integer)campo);
					else if (campo instanceof Double) 
						c.setCellValue((Double)campo);
					else if (campo instanceof Date) 
							c.setCellValue((Date)campo);
					else if (campo instanceof String) 
						c.setCellValue((String)campo);				
				}
				counts++;
			}
			wb.write(salida);
			salida.close();
		} catch (Exception e) {
			miLog.addTraza(e);
			throw (e);
		}
	}
}
