/*******************************************************************************
 * 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.billing.ejb.session.beans;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.security.RolesAllowed;
import javax.ejb.EJB;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import lu.tudor.santec.gecamed.billing.ejb.entity.beans.Act;
import lu.tudor.santec.gecamed.billing.ejb.entity.beans.KeyValue;
import lu.tudor.santec.gecamed.billing.ejb.entity.beans.Rate;
import lu.tudor.santec.gecamed.billing.ejb.entity.beans.RateIndex;
import lu.tudor.santec.gecamed.billing.ejb.entity.beans.Suffix;
import lu.tudor.santec.gecamed.billing.ejb.session.interfaces.NomenclatureInterface;
import lu.tudor.santec.gecamed.billing.utils.BillingAdminSettings;
import lu.tudor.santec.gecamed.core.ejb.session.beans.GECAMedSessionBean;
import lu.tudor.santec.gecamed.core.utils.GECAMedUtils;
import lu.tudor.santec.gecamed.core.utils.querybuilder.HibernateCondition;
import lu.tudor.santec.gecamed.core.utils.querybuilder.HibernateOperator;
import lu.tudor.santec.gecamed.core.utils.querybuilder.HibernateQueryFactory;
import lu.tudor.santec.gecamed.core.utils.querybuilder.WhereClause;
import lu.tudor.santec.gecamed.usermanagement.ejb.session.interfaces.LoginInterface;
import lu.tudor.santec.settings.SettingReader;

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

//***************************************************************************
//* Interface Definition and Members                                        *
//***************************************************************************

@Stateless
@Remote (NomenclatureInterface.class)
public class NomenclatureBean extends GECAMedSessionBean implements NomenclatureInterface
	{
	
	/** the logger Object for this class */
	private static Logger logger = Logger.getLogger(NomenclatureBean.class.getName());
 	
 	private static Pattern suffixesPattern;
	
 	private static HashMap<Integer, Vector<KeyValue>> sortedKeyVals;
 	
 	@EJB
	LoginInterface	loginBean;

	
//	@PersistenceContext (unitName="gecam")
//	EntityManager m_EntityManager;

//***************************************************************************
//* Class Primitives                                                        *
//***************************************************************************
	
//***************************************************************************
//* Class Body                                                              *
//***************************************************************************
//---------------------------------------------------------------------------
//===========================================================================
//= Suffix Related Methods
//===========================================================================
//---------------------------------------------------------------------------
/**
 * Returns all suffixes defined in the database.
 * @return A collection holding all suffixes defined in the database, <code>
 * null</code> if none can be found.
 */
//---------------------------------------------------------------------------
	
@SuppressWarnings("unchecked")
public Collection<Suffix> getAllSuffixes() throws Exception 
	{
	Collection <Suffix>	l_Suffixes;
	try	{	
		l_Suffixes = m_EntityManager.createNamedQuery (Suffix.c_AllSuffixes).getResultList();
		}
	catch (NoResultException p_Exception)
		{
		l_Suffixes = null;
		}
	
	return l_Suffixes;
	}

//---------------------------------------------------------------------------
//===========================================================================
//= Key Value Related Methods
//===========================================================================
//---------------------------------------------------------------------------
/**
 * Returns all key values defined in the database.
 * @return A collection holding all key values defined in the database, <code>
 * null</code> if none can be found.
 */
//---------------------------------------------------------------------------
	
@SuppressWarnings("unchecked")
public Collection<KeyValue> getAllKeyValues() throws Exception 
	{
	Collection <KeyValue>	l_KeyValues;
	try	{	
		l_KeyValues = m_EntityManager.createNamedQuery (KeyValue.c_AllKeyValues).getResultList();
		}
	catch (NoResultException p_Exception)
		{
		l_KeyValues = null;
		}
	
	return l_KeyValues;
	}


	/**
	 * Returns a hashmap with Key = keyType and value = a date-sorted collection of all 
	 * key values with that keyType 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public HashMap<Integer, Vector<KeyValue>> getAllKeyValuesSorted() throws Exception {
		
		Collection<KeyValue> l_KeyValues;
		HashMap<Integer, Vector<KeyValue>> sortedKeyVals = new HashMap<Integer, Vector<KeyValue>>();
		
		try {
			l_KeyValues = m_EntityManager.createNamedQuery(KeyValue.c_AllKeyValues).getResultList();
			for (KeyValue keyValue : l_KeyValues) {
				Integer keyType = keyValue.getKeyType();
				Vector<KeyValue> vals = sortedKeyVals.get(keyType);
				if (vals == null) {
					vals = new Vector<KeyValue>();
					sortedKeyVals.put(keyType, vals);
				}
				vals.add(keyValue);
			}
		} catch (NoResultException p_Exception) {
			l_KeyValues = null;
		}

		return sortedKeyVals;
	}
	
	/**
	 * returns the current key value for the specified key type and performed date.
	 * keyvalues are cached in the bean for faster access.
	 * @param p_Type the specified key type
	 * @param p_Date the specified performed date
	 * @return returns the current key value for the specified key type and performed date, null if none found
	 * @throws Exception
	 */
	public KeyValue getKeyValueByTypeAndPerformedDate(Integer p_Type, Date p_Date) throws Exception {
		if (sortedKeyVals == null) {
			sortedKeyVals = getAllKeyValuesSorted();
		}
		
		Vector<KeyValue> l_keyVals = sortedKeyVals.get(p_Type);
		for (KeyValue keyValue : l_keyVals) {
			if (! p_Date.before(keyValue.getApplicability())) {
				return keyValue;
			}
		}
		return null;
	}

//---------------------------------------------------------------------------
/**
* Returns the newest key value of each key_id from the Database
*/
//---------------------------------------------------------------------------
	@SuppressWarnings("unchecked")
	public Collection<KeyValue> getNewestKeyValues() throws Exception {
		Collection<KeyValue> l_KeyValues;
		HashSet<Integer> keyType = new HashSet<Integer>();
		Collection<KeyValue> l_KeyValuesNewest = new ArrayList<KeyValue>();
		try {
			l_KeyValues = m_EntityManager.createNamedQuery(KeyValue.c_AllKeyValues).getResultList();
			for (KeyValue keyValue : l_KeyValues) {
				if (!keyType.contains(keyValue.getKeyType())) {
					keyType.add(keyValue.getKeyType());
					l_KeyValuesNewest.add(keyValue);
				}
			}

		} catch (NoResultException p_Exception) {
			l_KeyValues = null;
		}

		return l_KeyValuesNewest;
	}

//---------------------------------------------------------------------------
/**
 * Returns one particular key value indentified by the specified Id.
 * @param p_ID specifies the id of the key value to fetch from database.
 * @return The key value object with the specified Id if available, <code>
 * null</code> otherwise.
 */
//---------------------------------------------------------------------------

public KeyValue getKeyValueByID (Integer p_ID) throws Exception 
	{
	KeyValue l_KeyValue;

	try	{	
		l_KeyValue = m_EntityManager.find(KeyValue.class, p_ID);
		}
	catch (NoResultException p_Exception)
		{
		l_KeyValue = null;
		}

	return l_KeyValue;
	}

//---------------------------------------------------------------------------
/**
 * Saves the specified key value into the database.
 * @param p_KeyValue specifies the key value to save.
 * @return the persisted, i.e. saved key value.
 */
//---------------------------------------------------------------------------

@RolesAllowed("gecam")
public KeyValue saveKeyValue (KeyValue p_KeyValue) throws Exception 
	{
	if (p_KeyValue == null) return p_KeyValue;
		
	// clear keyvalue Hash
	sortedKeyVals = null;
	
	return m_EntityManager.merge (p_KeyValue);
	}

//---------------------------------------------------------------------------
/**
 * Deletes the specified key value from the database.
 * @param p_KeyValue specifies the key value to be deleted.
 */
//---------------------------------------------------------------------------

@RolesAllowed("gecam")
public void deleteKeyValue (KeyValue p_KeyValue) throws Exception 
	{
	if ((p_KeyValue == null) || (!p_KeyValue.isPersistent())) return;
		
	p_KeyValue = m_EntityManager.find(KeyValue.class, p_KeyValue.getId());
	m_EntityManager.remove(p_KeyValue);
	
	// clear keyvalue Hash
	sortedKeyVals = null;
	}

//---------------------------------------------------------------------------
//===========================================================================
//= Rate Related Methods
//===========================================================================
//---------------------------------------------------------------------------
/**
 * The Method returns the number of Rates refering to the specified key value.
 * @param p_KeyValue specifies the key value to get number of refering rates
 * for.
 * @return The number of rates refering to the specified key value.
 */
//---------------------------------------------------------------------------

public Long getRateCountByKeyValue (KeyValue p_KeyValue)
	{
	Long l_RateCount = Long.valueOf (0L);
		
	if ((p_KeyValue == null) || (!p_KeyValue.isPersistent())) return l_RateCount;
		
	try	{
		l_RateCount = (Long) m_EntityManager.createNamedQuery (Rate.c_RateCountByKeyType)
									   		.setParameter("keyType", p_KeyValue.getKeyType())
									   		.setMaxResults(1)
									   		.getSingleResult();
		}
	catch (NoResultException p_Exception)
		{
		l_RateCount = Long.valueOf (0L);
		}

	return l_RateCount;	
	}


///**
// * returns the Rate for the given Code and Performed Date.
// * @param p_Code the Rates Code
// * @param p_Date The performed Date
// * @return the Rate for the given Code and Performed Date.
// * @throws Exception
// */
//public Rate getRateByCodeAndPerformedDate (String p_Code, Date p_Date) throws Exception {
//	List<?> l_Result;
//	Rate l_Rate;
//	try	{
//		l_Result = m_EntityManager.createNamedQuery (Rate.c_RateByCodeAndPerformedDate)
//				.setParameter("code", p_Code.toUpperCase())
//				.setParameter("date", p_Date)
//				.setMaxResults(1)
//				.getResultList();
//		if (l_Result != null && l_Result.size() > 0)
//			 l_Rate = (Rate) l_Result.get(0);
//		else l_Rate = null;
//	} catch (NoResultException p_Exception)	{
//		l_Rate = null;
//		p_Exception.printStackTrace();
//	}
//	return l_Rate;
//}

//---------------------------------------------------------------------------
/**
* UCM (Union des Caisses de Maladie) Rates are uniquely identified by a
* short alphanumerical code. The getRateByCode method looks up a rate for
* the specified code.
* @param p_Code specifies the code of the rate to look for.
* @param p_ExtractSuffixes specifies, if this code might contain suffixes. 
*   If <code>false</code>, the search is for the code exactly as entered.
* @return A matching rate for the specified code, <code>null</code> if none
* can be found.
*/
//---------------------------------------------------------------------------

public Rate getRateByCode (String p_Code, Date p_Date) throws Exception
{
	return getRateByCode(p_Code, p_Date, false);
}

//---------------------------------------------------------------------------
/**
 * UCM (Union des Caisses de Maladie) Rates are uniquely identified by a
 * short alphanumerical code. The getRateByCode method looks up a rate for
 * the specified code.
 * @param p_Code specifies the code of the rate to look for.
 * @param p_ExtractSuffixes specifies, if this code might contain suffixes. 
 *   If <code>false</code>, the search is for the code exactly as entered.
 * @return A matching rate for the specified code, <code>null</code> if none
 * can be found.
 */
//---------------------------------------------------------------------------

public Rate getRateByCode (String p_Code, Date p_Date, boolean p_ExtractSuffixes) throws Exception
{
	List<?> l_Result;
	Rate l_Rate;
	String l_Code;
	String l_Suffix;
	
	if (p_Code != null) 
		 l_Code = p_Code.toUpperCase().trim();
	else l_Code = null;
	
	if (p_Date == null)
		p_Date = new Date();
	
	try	{
		if (p_ExtractSuffixes)
		{
			l_Suffix	= extractSuffix(l_Code);
			l_Code		= l_Code.substring(0, l_Code.length() - l_Suffix.length());
			
			if (!GECAMedUtils.isEmpty(l_Suffix))
			{
				l_Result	= m_EntityManager.createNativeQuery("SELECT DISTINCT(code) FROM billing.rate WHERE trim(upper(code)) ~ :codeRegex")
						.setParameter("codeRegex", generateCodeSearchRegex(l_Code, l_Suffix))
						.getResultList();
				
				l_Code	= null;
				for (Object c : l_Result)
				{
					if (l_Code == null)
						l_Code	= (String) c;
					else if (((String) c).length() > l_Code.length())
						l_Code	= (String) c;
				}
			}
		}
		
		if (l_Code == null)
			return null;
		
		l_Result = m_EntityManager.createNamedQuery (Rate.c_RateByCodeAndPerformedDate)
				.setParameter("code", l_Code.toUpperCase())
				.setParameter("date", p_Date)
				.setMaxResults(1)
				.getResultList();
		
		if (l_Result != null && l_Result.size() > 0)
			 l_Rate = (Rate) l_Result.get(0);
		else l_Rate = null;
		}
	catch (NoResultException p_Exception)
		{
		l_Rate = null;
		p_Exception.printStackTrace();
		}

	return l_Rate;
}

//---------------------------------------------------------------------------

private String extractSuffix (String code)
{
	Matcher m = getSuffixPattern().matcher(code);
	
	if (m.find() && m.start() > 0)
		return m.group();
	else
		return null;
}

//---------------------------------------------------------------------------

private Pattern getSuffixPattern ()
{
	if (suffixesPattern == null)
	{
		@SuppressWarnings("unchecked")
		List<Suffix>	suffixes	= m_EntityManager.createNamedQuery(Suffix.c_AllSuffixes).getResultList();
		StringBuilder	suffixRegex	= new StringBuilder(suffixes.size() + 4);
		
		suffixRegex.append("[");
		for (Suffix s : suffixes)
			suffixRegex.append(s.getLetter());
		suffixRegex.append("]*$");
		
		suffixesPattern = Pattern.compile(suffixRegex.toString(), Pattern.CASE_INSENSITIVE);
	}
	
	return suffixesPattern;
}

//---------------------------------------------------------------------------

private Object generateCodeSearchRegex (String p_Code, String p_Suffix)
{
	StringBuilder builder;
	
	
	p_Code	= p_Code.replaceAll("([$^()<>\\[{\\\\|.*+?])", "\\\\$1");
	builder = new StringBuilder(p_Code.length() + 2 + p_Suffix.length() * 4)
			.append("^")
			.append(p_Code);
	
	for (char s : p_Suffix.toCharArray())
		builder.append("(").append(s);
	for (int i = 0; i < p_Suffix.length(); i++)
		builder.append(")?");
	builder.append("$");
	
	return builder.toString();
}

//---------------------------------------------------------------------------
/**
 * Returns all the rates whose code begin with the specified pattern.
 * @param p_Pattern specifies the beginning of the code to look for. Please note that
 * method automatically adds necessary wildcard at end.
 * @return a collection holding all the matching rates or <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<Rate> getRatesStartingWith (String p_Pattern) throws Exception 
	{
	Collection 	l_Rates;
	
	p_Pattern = p_Pattern + "%";
	
	try	{
		l_Rates = m_EntityManager.createNamedQuery (Rate.c_RatesByCodeFragment)
				 .setParameter("pattern", p_Pattern)
				 .getResultList();
		
		l_Rates = newestRateOnly(l_Rates);
		
		}
	catch (NoResultException p_Exception)
		{
		l_Rates = null;
		}
	
	return (l_Rates);
	}

//---------------------------------------------------------------------------
/**
 * Returns all the rates having the specified phrase in their label.
 * @param p_Phrase specifies the exact phrase to look for.
 * @return a collection holding all the matching rates or <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<Rate> getRatesWithExactPhrase (String p_Phrase) throws Exception 
	{
	Collection 			l_Rates;	
	HibernateCondition	l_Condition;
	WhereClause			l_WhereClause;
	
	l_WhereClause = new WhereClause ();
	l_Condition   = new HibernateCondition ("label",
					  					    HibernateOperator.c_LikeOperator,
									        "%" + p_Phrase + "%");
	
	l_WhereClause.addCondition(l_Condition);
	l_Rates = this.getRatesByWhereClause(l_WhereClause);
	
	l_Rates = newestRateOnly(l_Rates);

	return (l_Rates);
	}

//---------------------------------------------------------------------------
/**
 * Returns all rates having ALL the specified keywords in their label.
 * @param p_Keywords specifies the keywords to look for.
 * @return a collection holding all the matching rates or <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<Rate> getRatesWithAllKeywords (String[] p_Keywords) throws Exception 
	{
	Collection 			l_Rates;
	HibernateCondition	l_Condition;
	WhereClause			l_WhereClause;
	int					l_Index;
	
	l_WhereClause = new WhereClause ();
	l_WhereClause.setOperator(HibernateOperator.c_AndOperator);

	for (l_Index = 0; l_Index < p_Keywords.length; l_Index++)
		{
		l_Condition   = new HibernateCondition ("label",
					  					        HibernateOperator.c_LikeOperator,
									            "%" + p_Keywords[l_Index] + "%");
		l_Condition.setCaseSensitive(false);
		l_WhereClause.addCondition(l_Condition);
		}

	l_Rates = this.getRatesByWhereClause (l_WhereClause);

	l_Rates = newestRateOnly(l_Rates);
	
	return (l_Rates);
	}

//---------------------------------------------------------------------------
/**
 * Returns all rates having ANY of the specified keywords in their label.
 * @param p_Keywords specifies the keywords to look for.
 * @return a collection holding all the matching rates or <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<Rate> getRatesWithAnyKeywords (String[] p_Keywords) throws Exception 
	{
	Collection 			l_Rates;
	HibernateCondition	l_Condition;
	WhereClause			l_WhereClause;
	int					l_Index;
	
	l_WhereClause = new WhereClause ();
	l_WhereClause.setOperator(HibernateOperator.c_OrOperator);

	for (l_Index = 0; l_Index < p_Keywords.length; l_Index++)
		{
		l_Condition   = new HibernateCondition ("label",
					  					        HibernateOperator.c_LikeOperator,
									            "%" + p_Keywords[l_Index] + "%");
		l_Condition.setCaseSensitive(false);
		l_WhereClause.addCondition(l_Condition);
		}

	l_Rates = this.getRatesByWhereClause (l_WhereClause);

	l_Rates = newestRateOnly(l_Rates);
	
	return (l_Rates);
	}

//---------------------------------------------------------------------------
/**
 * Returns all rates having NONE of the specified keywords in their label.
 * @param p_Keywords specifies the keywords to look for.
 * @return a collection holding all the matching rates or <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<Rate> getRatesWithNoneKeywords (String[] p_Keywords) throws Exception 
	{
	Collection 			l_Rates;
	HibernateCondition	l_Condition;
	WhereClause			l_WhereClause;
	int					l_Index;
	
	l_WhereClause = new WhereClause ();
	l_WhereClause.setOperator(HibernateOperator.c_AndOperator);

	for (l_Index = 0; l_Index < p_Keywords.length; l_Index++)
		{
		l_Condition   = new HibernateCondition ("label",
					  					        HibernateOperator.c_NotLikeOperator,
									            "%" + p_Keywords[l_Index] + "%");
		l_Condition.setCaseSensitive(false);
		l_WhereClause.addCondition(l_Condition);
		}

	l_Rates = this.getRatesByWhereClause (l_WhereClause);
	
	l_Rates = newestRateOnly(l_Rates);

	return (l_Rates);
	}


//---------------------------------------------------------------------------
/**
 * Returns all the rates matching the specified where clause.
 * @param p_Clause specifies the where clause which defines the search criteria.
 * @return A collection of rates matching the specified criteria.
 */
//---------------------------------------------------------------------------

//@RolesAllowed("gecam")
@SuppressWarnings("unchecked")
public Collection <Rate> getRatesByWhereClause (WhereClause p_Clause) throws Exception
	{
	Query					l_Query;
	String					l_QueryString;

	Collection <Rate>		l_Rates;

	l_Rates = new LinkedHashSet <Rate> ();
	
	if (p_Clause == null) return l_Rates;

	l_QueryString = "SELECT OBJECT(o) FROM Rate o ";

	try	{
		l_Query = HibernateQueryFactory.buildQueryFromWhereClause (m_EntityManager,l_QueryString, p_Clause, " ORDER BY o.code, o.applicability DESC");
		l_Rates = l_Query.getResultList();
		
		l_Rates = newestRateOnly(l_Rates);
		
		}
	catch (NoResultException p_Exception)
		{
		// Simply Return empty Collection
		}
	
	
	
	return l_Rates;
	}

//---------------------------------------------------------------------------
/**
 * Saves the specified rate into the database.
 * @param p_Rate specifies the rate to save.
 * @return the persisted, i.e. saved rate.
 */
//---------------------------------------------------------------------------

@RolesAllowed("gecam")
public Rate saveRate (Rate p_Rate) throws Exception 
	{
	if (p_Rate == null) return p_Rate;
		
	return m_EntityManager.merge (p_Rate);
	}

//---------------------------------------------------------------------------
/**
 * Deletes the specified rate from the database.
 * @param p_Rate specifies the rate to be deleted.
 */
//---------------------------------------------------------------------------

@RolesAllowed("gecam")
public void deleteRate (Rate p_Rate) throws Exception 
	{
	if ((p_Rate == null) || (!p_Rate.isPersistent())) return;
		
	p_Rate = m_EntityManager.find(Rate.class, p_Rate.getId());
	m_EntityManager.remove(p_Rate);
	}

//---------------------------------------------------------------------------
//===========================================================================
//= Rate Index Related Methods
//===========================================================================
//---------------------------------------------------------------------------
/**
 * Returns all the rate indices who are roots.
 * @return a collection holding all rate indices who are roots, <code>null</code> if
 * none can be found.
 */
//---------------------------------------------------------------------------

@SuppressWarnings("unchecked")
public Collection<RateIndex> getRateIndexRoots () throws Exception 
	{
	Collection<RateIndex> 	l_IndexRoots;
	
	try	{
		l_IndexRoots = m_EntityManager.createNamedQuery (RateIndex.c_RateIndexRoots)
				 					   .getResultList();
		}
	catch (NoResultException p_Exception)
		{
		l_IndexRoots = null;
		}
	
	return l_IndexRoots;
	}

//---------------------------------------------------------------------------
/**
 * Returns one particular RateIndex indentified by the specified Id.
 * @param p_RateIndexID specifies the id of the rate index to fetch from database.
 * @return The RateIndex object with the specified Id if available, <code>
 * null</code> otherwise.
 */
//---------------------------------------------------------------------------

public RateIndex getRateIndexByID (Integer p_RateIndexID) throws Exception
	{
	RateIndex l_RateIndex;

	try	{
		l_RateIndex = m_EntityManager.find(RateIndex.class, p_RateIndexID);
		}
	catch (NoResultException p_Exception)
		{
		l_RateIndex = null;
		}

	return l_RateIndex;
	}

//---------------------------------------------------------------------------
/**
 * Returns one particular RateIndex indentified by the specified label.
 * @param p_Label specifies the label of the rate index to fetch from database.
 * @return The RateIndex object with the specified label if available, <code>
 * null</code> otherwise.
 */
//---------------------------------------------------------------------------

public RateIndex getRateIndexByLabel (String p_Label) throws Exception
	{
	RateIndex l_RateIndex;

	try	{
		l_RateIndex = (RateIndex) m_EntityManager.createNamedQuery (RateIndex.c_RateIndexByLabel)
				   							 	 .setParameter("label", p_Label)
				   							 	 .getSingleResult();
		}
	catch (NoResultException p_Exception)
		{
		l_RateIndex = null;
		}

	return l_RateIndex;
	}

//---------------------------------------------------------------------------
/**
 * fetches the lazy dependencies. i.e. the rates for the specifed rate index.
 * Please note that rate indices are a hierarchical structure which means
 * that fetching lazy dependencies of a specific rate index entails fetching
 * the lazy dependencies of the indices directly attached to it. This is done
 * in depth first recursion.
 * @param p_RateIndex specifies the rate index object to fetch lazy dependencies
 * , i.e. rates for.
 * @return specified rate index object having its rates properly initialized.
 */
//---------------------------------------------------------------------------

public RateIndex	fetchLazyDependencies (RateIndex p_RateIndex) throws Exception
	{
	Iterator <RateIndex>	l_SubChapterIterator;
	RateIndex				l_SubChapter;
	
	if ((p_RateIndex == null) || (p_RateIndex.getId() == null)) return null;

	p_RateIndex = m_EntityManager.find(RateIndex.class, p_RateIndex.getId());
	
	if (p_RateIndex.getSubChapters() != null)
		{
		l_SubChapterIterator = p_RateIndex.getSubChapters().iterator();
		while (l_SubChapterIterator.hasNext())
			{
			l_SubChapter = l_SubChapterIterator.next();
			l_SubChapter = this.fetchLazyDependencies(l_SubChapter);
			}
		}
	
	if ((p_RateIndex.getRates() != null) && (!Hibernate.isInitialized(p_RateIndex.getRates())) )
					Hibernate.initialize (p_RateIndex.getRates());

	return p_RateIndex;
	}

//---------------------------------------------------------------------------
/**
 * Find those rates, hat cannot be shown, but still are in the database.
 * @return A list of object arrays containing the ID and the Code of the rate
 */
//---------------------------------------------------------------------------

public List<Object[]> getRatesWithoutKeyValue()
	{
	@SuppressWarnings("unchecked")
	List<Object[]> 	invalidRates 	= m_EntityManager.createNativeQuery(
			"SELECT id, code FROM billing.rate " +
			"WHERE key_value_id NOT IN ( " +
			"  SELECT id FROM billing.key_value )")
			.getResultList();
	
	return invalidRates;
	}

//---------------------------------------------------------------------------
/**
 * Delete those rates, that cannot be shown, but still are in the database.
 * @param p_RateIds The IDs that will be deleted
 */
//---------------------------------------------------------------------------

public void removeRatesWithoutKeyValue 	(Integer[] p_RateIds)
	{
	Rate 	rate;
	int 	count = 0;
	
	for (Integer l_RateId : p_RateIds)
		{
		try 
			{
			rate = m_EntityManager.find(Rate.class, l_RateId);
			m_EntityManager.remove(rate);
			count++;
			}
		catch (Exception e)
			{
			log(Level.ERROR, "Couldn't delete rate with ID "+l_RateId, e);
			}
//		update = m_EntityManager.createNativeQuery(
//				"DELETE FROM billing.rate WHERE id = :id")
//				.setParameter("id", l_RateId)
//				.executeUpdate();
		
//		m_Logger.log(Level.INFO, update + " row(s) affected");
		}
	log(Level.INFO, count+" rates deleted.");
	}


public HashSet<String> getAllPrivateCodes ()
	{
	HashSet<String> l_NoneCnsCodes	= new HashSet<String>();
	List<?>			l_NoneCnsRateIndexes;
	
	
	l_NoneCnsRateIndexes = m_EntityManager.createNamedQuery(
			RateIndex.c_AllPrivateRateIndexes).getResultList();
	
	for (Object index : l_NoneCnsRateIndexes)
		{
		addCodes((RateIndex) index, l_NoneCnsCodes);
		}
	
	return l_NoneCnsCodes;
	}


public List<String> getCodesOfRateIndex (RateIndex index)
{
	@SuppressWarnings("unchecked")
	List<String> codes = m_EntityManager.createQuery(
					"SELECT r.code FROM RateIndex i, Rate r " +
					"WHERE r.indexId = :indexId " +
					"ORDER BY r.code")
			.setParameter("indexId", index.getId())
			.getResultList();
	
	return codes;
}


private void addCodes (RateIndex index, Collection<String> codes)
	{
	// add the codes of this chapter to the code collection
	for (Rate rate : index.getRates())
		codes.add(rate.getCode());
	
	if (index.getSubChapters() != null)
		{
		// call this method for all RateIndex children
		for (RateIndex childIndex : index.getSubChapters())
			addCodes(childIndex, codes);
		}
	}
	
	
	/* (non-Javadoc)
	 * @see lu.tudor.santec.gecamed.billing.ejb.session.interfaces.NomenclatureInterface#readBillingSetting(java.lang.String, java.lang.Object)
	 */
	public Object readBillingSetting (String key, Object defaultValue)
	{
		try
		{
			SettingReader	settingReader	= loginBean.getAdminSettingsReader();
			Object			setting			= settingReader.getValue(BillingAdminSettings.c_ModuleName, key);
			
			if (setting == null)
				return defaultValue;
			else
				return setting;
		}
		catch (Exception e)
		{
			logger.error("Error while trying to receive the global setting \"" + (BillingAdminSettings.c_ModuleName + "." + key) + "\"", e);
			return defaultValue;
		}
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getRateIndexLabelsForCodes (Collection<String> codes)
	{
		return m_EntityManager.createNamedQuery(RateIndex.c_MapCodeToRateLabel)
				.setParameter("codes", codes)
				.getResultList();
	}
	
	
	public String getRateIndexLabelForCode (String code)
	{
		List<?> resultList = m_EntityManager.createNamedQuery(RateIndex.c_getRateLabelOfCode)
				.setParameter("code", code)
				.setMaxResults(1)
				.getResultList();
		
		if (resultList == null || resultList.isEmpty())
			return null;
		else 
			return (String) resultList.get(0);
	}
	
	
public Act initializeAct (String p_Code, Act p_Act) throws Exception {
	
	if (p_Code == null || p_Code.trim().length() == 0) 
		return p_Act;
	
	
	if (p_Act.getPerformedDate() == null) {
		p_Act.setPerformedDate(new Date());
	}
	
	Date 	l_Date		= p_Act.getPerformedDate();
	
	// get Rate for Code and Date
	Rate 	l_Rate 		= getRateByCode(p_Code, l_Date, true);
	
	if (l_Rate == null) {
		throw new Exception("No Rate Found for Code: " + p_Code + " valid on " + l_Date);
	}
	
	// get Keyvalue for Rate and Date
	Integer 	l_KeyType	= l_Rate.getKeyType();
	KeyValue 	l_KeyValue	= getKeyValueByTypeAndPerformedDate(l_KeyType, l_Date);
	
	if (l_KeyValue == null) {
		throw new Exception("No Keyvalue Found for KeyType: " + l_KeyType + " valid on " + l_Date);
	}
	
	p_Act.setCode 		(l_Rate.getCode().toUpperCase());
	addSuffixes			(p_Act, p_Code.substring(l_Rate.getCode().length()).toUpperCase());
	p_Act.setLabel		(l_Rate.getLabel());
	p_Act.setCAT        (l_Rate.getCAT());
	p_Act.setCAC        (l_Rate.getCAC());
	p_Act.setAPCM		(l_Rate.getAPCM());
	p_Act.setACM		(l_Rate.getACM());
	p_Act.setQuantity	(Integer.valueOf (1));
	p_Act.setKeyValue	(l_KeyValue.getValue(), l_KeyValue.getFractionDigits());
	p_Act.setCoefficient(l_Rate.getCoefficient());
	p_Act.setOrgCoefficient(null);
	p_Act.setAdjustment	(l_Rate.getDefaultAdjustment());
	p_Act.setChanges	(Act.CHANGED_USER);
	
	return p_Act;
}


private void addSuffixes (Act p_Act, String p_Suffixes)
{
	StringBuilder	suffixes = new StringBuilder(p_Suffixes.toUpperCase());
	
	
	// keep 'A' and 'P' suffixes
	if (p_Act.getSuffixes().contains("A"))
		suffixes.append('A');
	if (p_Act.getSuffixes().contains("P"))
		suffixes.append('P');
	
	// add suffixes, without duplicates
	for (int index = 0; index < suffixes.length(); index++)
	{
		p_Act.setSuffix(suffixes.charAt(index));
	}
}

/**
 * Filters the list of rates (must be sorted by applicability date) and returns only the newest rate for each code. 
 * 
 * @param rates
 * @return
 */
private Collection<Rate> newestRateOnly(Collection<Rate> rates) {
	if (rates == null) return null;
	
	LinkedHashMap<String, Rate> rateHash = new LinkedHashMap<String, Rate>();
	
	for (Rate rate : rates) {
		if (! rateHash.containsKey(rate.getCode())) {
			rateHash.put(rate.getCode(), rate);
		}
	}
	
	return new ArrayList<Rate>(rateHash.values());
	
	
}

public Collection<Rate> getRatesWithoutChapters() throws Exception {
	WhereClause c = new WhereClause();
	c.addCondition(new HibernateCondition("indexId", HibernateOperator.c_IsOperator, null));
	return getRatesByWhereClause(c);
}
	
//***************************************************************************
//* End of Class															*
//***************************************************************************
	}
