/*******************************************************************************
 * This file is part of GECAMed.
 * 
 * GECAMed is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License (L-GPL) as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * GECAMed is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License (L-GPL)
 * along with GECAMed.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * GECAMed is Copyrighted by the Centre de Recherche Public Henri Tudor (http://www.tudor.lu)
 * (c) CRP Henri Tudor, Luxembourg, 2008
 *******************************************************************************/
package lu.tudor.santec.gecamed.reporting.ejb.session.beans;

import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Vector;

import javax.annotation.security.RolesAllowed;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;

import lu.tudor.santec.gecamed.core.utils.ManagerFactory;
import lu.tudor.santec.gecamed.reporting.ejb.entity.beans.ParameterQuery;
import lu.tudor.santec.gecamed.reporting.ejb.entity.beans.Report;
import lu.tudor.santec.gecamed.reporting.ejb.entity.beans.ReportParameter;
import lu.tudor.santec.gecamed.reporting.ejb.session.interfaces.ReportManagerInterface;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.query.JRJpaQueryExecuterFactory;
import net.sf.jasperreports.engine.util.JRLoader;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;


@Remote({ ReportManagerInterface.class })
@Stateless
public class ReportManagerBean implements ReportManagerInterface
{
	/* ======================================== */
	// 		MEMBERS
	/* ======================================== */
	
	/** the logger Object for this class */
	private static Logger		logger		= Logger.getLogger(ReportManagerBean.class.getName());
	
//	private static DateFormat	timeFormatter	= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
//	private static Connection	connection;
	
//	private static DataSource	dataSource;
	
	
	@PersistenceContext(unitName = "gecam")
	EntityManager	em;
	
	
	
	
	
	/* ======================================== */
	// 		CLASS BODY
	/* ======================================== */
	
	@RolesAllowed("gecam")
	public JasperPrint createReport(Integer reportID, HashMap<String, Object> parameters) throws Exception
	{
		parameters.put(JRJpaQueryExecuterFactory.PARAMETER_JPA_ENTITY_MANAGER, em);
		
		Report report = em.find(Report.class, reportID);
		
		
		JasperReport jasperReport = (JasperReport) JRLoader.loadObject(new ByteArrayInputStream(report.getJasper()));
		
		
		JasperPrint print = JasperFillManager.fillReport(jasperReport, parameters);
		
		return print;
	}
	
	
	@SuppressWarnings("unchecked")
	public Collection<Report> getAllReports() throws Exception
	{
		return em.createNamedQuery(Report.FIND_ALL_REPORTS).getResultList();
	}
	
	
	public Report saveReport(Report report)
	{
		report = em.merge(report);
		cleanUpReportParameters();
		return report;
	}
	
	
	public boolean deleteReport(Report report) 
	{
		report	= em.find(Report.class, report.getId());
		if (report != null)
		{
			em.remove(report);
			cleanUpReportParameters();
			return true;
		}
		return false;
	}
	
	
	public Report getReportByName(String name) throws Exception
	{
		try
		{
			return (Report) em.createNamedQuery(Report.FIND_REPORT_BY_NAME).setParameter("name", name).getSingleResult();
		}
		catch (Exception e)
		{
			return null;
		}
	}
	
	
	public Object[] getAllReportTypes()
	{
		return em.createNamedQuery(Report.FIND_ALL_REPORT_TYPES)
				.getResultList()
				.toArray();
	}
	
	
	public HashSet<String> getAllReportNames ()
	{
		List<?>			results;
		HashSet<String>	names;
		
		results	= em.createNamedQuery(Report.FIND_ALL_REPORT_NAMES)
				.getResultList();
		
		names	= new HashSet<String>();
		for (Object name : results)
			names.add(String.valueOf(name));
		
		return names;
	}
	
	
	public Report getResultList (Report report) throws Exception
	{
		if (report.isNativeQuery())
			 executeNativeQuery(report);
		else executeHQLQuery(report);
		
		return report;
	}
	
	
	private Report executeNativeQuery (Report report) throws Exception 
	{
		String[]				queries		= report.createFilledQueries();
		Vector<Vector<Object>>	data		= null;
		Vector<Object>			rowData;
		Object					columnData;
		ResultSetMetaData		meta;
		Connection				connection	= null;
		Statement				statement;
		ResultSet				results;
		int						columns;
		
		
		try
		{
			connection	= getJDBCConnection();
			statement	= connection.createStatement();
			
			for (String query : queries)
			{
				data		= new Vector<Vector<Object>>();
				// execute the query
				results		= statement.executeQuery(query);
				meta		= results.getMetaData();
				columns		= meta.getColumnCount();
				
				// set the column names as first result row
				rowData		= new Vector<Object>(columns);
				data.add(rowData);
				for (int i = 1; i <= columns; i++)
					rowData.add(meta.getColumnLabel(i));
				
				// read the results
				while (results.next()) 
				{
					rowData	= new Vector<Object>();
					data.add(rowData);
					
					for (int i = 1; i <= columns; i++)
					{
						columnData	= results.getObject(i);
						rowData.add(columnData);
					}
				}
			}
			report.setData(data);
		}
		finally
		{
			connection.close();
		}
		
		return report;
	}
	
	
	private Report executeHQLQuery (Report report)
	{
		// TODO: implement
		return null;
//		Query					query;
//		List<?>					results;
//		Vector<Vector<Object>>	data;
//		
//		queryString = queryString.replace(";", "");
//		query		= em.createQuery(queryString);
//		
//		for (QueryParameter p : parameters)
//			query.setParameter(p.getName(), p.getValue());
//		
//		results = query.getResultList();
//		
//		// TODO: transform the result list into a data Vector
//		data = null;
//		
//		return data;
	}
	
	
	public void cleanUpReportParameters ()
	{
		Query	query;
		List<?>	results;
		
		try
		{
			query	= em.createNamedQuery(ReportParameter.QUERY_DELETE_REFERENCELESS_PARAMETER);
			results	= query.getResultList();
			
			for (Object p : results)
				em.remove(p);
			
			if (results.size() > 0)
				logger.info(results.size() + " Parameters without reference to a report found and deleted.");
		}
		catch (Exception e)
		{
			logger.error(e.getMessage(), e);
		}
	}
	
	
	private static Connection getJDBCConnection () throws Exception
	{
		Context		initialContext;
		DataSource	dataSource;
		Connection	connection;
		
//		if (dataSource == null)
//		{
			initialContext	= new InitialContext();
			dataSource		= (DataSource) initialContext.lookup("java:/gecamdb");
//		}
		connection		= dataSource.getConnection();
		
		return connection;
	}
	
	
	public static BigDecimal fixFloatRounding (BigDecimal number)
	{
		return fixFloatRounding(number, 10);
	}
	
	
	public static BigDecimal fixFloatRounding (BigDecimal number, int scale)
	{
		if (number == null)
			return null;
		
		number	= number.setScale(scale, RoundingMode.HALF_UP);
		number	= new BigDecimal(String.valueOf(number)
				.replaceAll("(\\.0*\\Z)|(0*\\Z)", ""));
		
		return number;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<ParameterQuery> getAllParameterQueries ()
	{
		return (List<ParameterQuery>) em.createQuery("SELECT OBJECT(o) FROM ParameterQuery o ORDER BY name")
				.getResultList();
	}
	
	
	public ParameterQuery getParameterQuery (Integer id)
	{
		return (ParameterQuery) em.createQuery("SELECT OBJECT(o) FROM ParameterQuery o WHERE id = :id")
				.setParameter("id", id)
				.setMaxResults(1)
				.getSingleResult();
	
	}
	
	
	public List<?> getParameterOptions (String query)
	{
		if (query == null)
		{
			// no query, no result ...
			return null;
		}
		
		
		try
		{
			// try to execute the query
			return em.createNativeQuery(query)
					.getResultList();
		}
		catch (Exception e)
		{
			logger.log(Level.WARN, "The parameter options query seems to be wrong.\nQuery: \n"+query, e);
			return null;
		}
	}
	
	
	
	public static ReportManagerInterface getInstance ()
	{
		return (ReportManagerInterface) ManagerFactory.getRemote(ReportManagerBean.class);
	}
}
