/*******************************************************************************
 * Copyright 2014 Centre for Development of Advanced Computing(C-DAC), Pune
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package in.cdac.medinfo.csnotk.csnolib.db;

import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_DEFINITION_STATUS;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.CONCEPT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_CONCEPT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_EFFECTIVE_TIME;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_MODULE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_TERM;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.DESCRIPTION_TYPE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_ACCEPTABILITY_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_MODULE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_REFERENCED_COMPONENT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.REFSET_REFSET_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.cREFSET;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.cREFSET_ACTIVE;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.cREFSET_MODULE_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.cREFSET_REFERENCED_COMPONENT_ID;
import static in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping.cREFSET_REFSET_ID;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Set;

import in.cdac.medinfo.csnotk.csnolib.commons.DBSchemaMapping;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumAcceptabilityId;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumDescType;
import in.cdac.medinfo.csnotk.csnolib.commons.EnumRefset;



/**
 *
 * Database resource provider utility for query generation and data fetching.
 */
public class DBUtil
{

	/**
	 * Gets instance of {@link Statement} for database operation.
	 * @param connection
	 * 				 	Connection object.
	 * @return instance of {@link Statement}.
	 * @throws SQLException
	 */
	public static Statement getStatement(Connection connection) throws SQLException
	{
		Statement statement =  connection.createStatement();
		return statement;
	}

	/**
	 * Fetches all rows from specified database table associated with given instance of {@link Statement}.
	 * @param tableName
	 * 					 table in which the query has to be fired.
	 * @param statement
	 * 					 {@link Statement} instance.
	 * @return result set obtained on firing the query on DB.
	 * @throws SQLException
	 */
	public static ResultSet retrieveAll(String tableName, Statement statement) throws SQLException
	{
		return statement.executeQuery("Select * from "+tableName+";");
	}

	/**
	 * Fetches specified columns data using offset and returnLimit values.
	 * @param tableName
	 * 					 table in which the query has to be fired.
	 * @param columnArray
	 * 					 List of columns.
	 * @param statement
	 * 					 {@link Statement} instance.
	 * @param offset
	 * 					  the offset.
	 * @param returnLimit
	 * 					  the maximum number of results to be obtained.
	 * @return result set obtained on the execution of the query.
	 * @throws SQLException
	 */
	public static ResultSet retrieveColumns(String tableName, String columnArray[], Statement statement, long offset,long returnLimit,boolean isExtensionSelected) throws SQLException
	{
		StringBuilder query=new StringBuilder("select");
		for(String column: columnArray)
			query.append(" "+column+",");
		query.replace(0, query.length(),query.substring(0, query.length()-1));
		if(isExtensionSelected) {
			query.append(" from "+tableName +" Limit "+offset+", "+returnLimit+";");
		}else {
			query.append(" from "+tableName +" Limit "+returnLimit+";");
		}
		return statement.executeQuery(query.toString());
	}
	/**
	 * Retrieves Term and Type ID of description id which is FSN.
	 * @param conceptId
	 * 					 concept identifier.
	 * @param statement
	 * 					 {@link Statement} class instance.
	 * @return {@link ResultSet} obtained on DB query execution.
	 * @throws SQLException
	 */
	public static ResultSet retrieveFSNforHierachy(String conceptId , Statement statement, String languageCode) throws SQLException{
		return statement.executeQuery(new StringBuilder("select "+DESCRIPTION_TERM+","+DESCRIPTION_TYPE_ID+" from "+DESCRIPTION+" where " +DESCRIPTION_CONCEPT_ID+"="+conceptId+" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+"='"+languageCode+"' and "+DESCRIPTION_ACTIVE+" =1;").toString());
	}
	/**
	 * RetrievesConcept details.
	 * @param conceptId
	 * 					 concept id.
	 * @param languageCode
	 * 					 Description Language Code
	 * @param statement
	 * 					 {@link Statement} class instance.
	 * @throws SQLException
	 */
	public static ResultSet retrieveConceptDetails(String conceptId, String languageCode, Statement statement) throws SQLException{
		return statement.executeQuery(new StringBuilder("select "+DESCRIPTION_TERM+","+CONCEPT_ACTIVE+","+CONCEPT_DEFINITION_STATUS+","+DESCRIPTION_MODULE_ID+" from "+DESCRIPTION+","+CONCEPT+"  where "+ DESCRIPTION_CONCEPT_ID +"="+ CONCEPT_ID+" and " +DESCRIPTION_CONCEPT_ID+"="+conceptId+" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+"='"+languageCode+"' and "+DESCRIPTION_ACTIVE+"=1;").toString());
	}
	/**
	 * RetrievesConcept details.
	 * @param conceptId
	 * 					 concept id.
	 * @param statement
	 * 					 {@link Statement} class instance.
	 * @throws SQLException
	 */
	public static ResultSet retrieveConceptDetails(String conceptId, Statement statement) throws SQLException{
		return statement.executeQuery(new StringBuilder("select "+DESCRIPTION_TERM+","+CONCEPT_ACTIVE+","+CONCEPT_DEFINITION_STATUS+","+DESCRIPTION_MODULE_ID+" from "+DESCRIPTION+","+CONCEPT+"  where "+ DESCRIPTION_CONCEPT_ID +"="+ CONCEPT_ID+" and " +DESCRIPTION_CONCEPT_ID+"="+conceptId+" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DESCRIPTION_ACTIVE+"=1;").toString());
	}

	/**
	 * Retrieves Acceptability id of description which indicates preferred term for that description.
	 * @param descriptionId
	 * 						 description id.
	 * @return acceptability id
	 * 						 the refset acceptability id corresponding to that description.
	 * @throws SQLException
	 */
	public static ResultSet retrieveAcceptabilityId( String descriptionId,  Statement statement) throws SQLException{
		return statement.executeQuery("select " + REFSETLANG_ACCEPTABILITY_ID +" from "+REFSETLANG +" WHERE "+REFSETLANG_REFERENCED_COMPONENT_ID + " = "+descriptionId  +" AND "
				+REFSETLANG_ACCEPTABILITY_ID + " = " + EnumAcceptabilityId.PREFERRED.getValue()+";");
	}

	/**
	 * Retrieves Concept state of Specified Concept ID.
	 * @param conceptId
	 * 					 concept id.
	 * @param statement
	 * 					 {@link Statement} class instance.
	 * @return {@link ResultSet} that is actually the Concept state for that particular concept id.
	 * @throws SQLException
	 */
	public static ResultSet retrieveConceptState( String conceptId,  Statement statement) throws SQLException{
		return statement.executeQuery("select " + CONCEPT_ACTIVE +" from "+CONCEPT +" WHERE "+CONCEPT_ID + " = "+conceptId +";");
	}
	/**
	 * Closes given instance of {@link Connection}.
	 * @param connection
	 * 					{@link Connection} class object.
	 * @throws SQLException
	 */
	public static void closeConnection(Connection connection) throws SQLException
	{
		if(null!=connection)
		connection.close();
	}

	/**
	 * Closes given instance of {@link Statement}.
	 * @param statement
	 * 					{@link Statement} object.
	 * @throws SQLException
	 */
	public static void closeStatement(Statement statement) throws SQLException
	{
		if(null!=statement)
		statement.close();
	}

	/**
	 * Closes given instance of {@link ResultSet}.
	 * @param resultSet
	 * 					instance of {@link ResultSet}.
	 * @throws SQLException
	 */
	public static void closeResultSet(ResultSet resultSet) throws SQLException
	{
		if(null!=resultSet)
		resultSet.close();
	}

	/**
	 * Gets count of total number of rows in specified table.
	 * @param tableName
	 * 					Name of the table to be retrieved.
	 * @param statement
	 * 					{@link Statement} instance.
	 * @param moduleId
	 * 					ModuleId concept for extension and refset, pass this if require constrain search. Default value is null
	 * @return totalRows
	 * 					the total number of rows obtained on executing the query.
	 * @throws SQLException
	 */
	public static long getTotalRows(String tableName, Statement statement,String moduleId) throws SQLException{
		ResultSet resultSet=null;
		long numberOfRows=0;
		try{
		if(moduleId!=null)
			if(moduleId.isEmpty())
				moduleId=null;
		if(tableName!=null && DBSchemaMapping.DESCRIPTION.equals(tableName)){
			if(moduleId!=null)
				tableName=DBSchemaMapping.DESCRIPTION+","+DBSchemaMapping.REFSETLANG+" where "+DBSchemaMapping.DESCRIPTION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+" AND "+DBSchemaMapping.DESCRIPTION_MODULE_ID+"="+moduleId;
			else
				tableName=DBSchemaMapping.DESCRIPTION+","+DBSchemaMapping.REFSETLANG+" where "+DBSchemaMapping.DESCRIPTION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID;
			resultSet = statement.executeQuery("select count(*) from "+tableName+";");
		}else if(tableName!=null && DBSchemaMapping.TEXTDEFINITION.equals(tableName)){
			tableName=DBSchemaMapping.TEXTDEFINITION+","+DBSchemaMapping.REFSETLANG+" where "+DBSchemaMapping.TEXTDEFINITION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID;
			 resultSet = statement.executeQuery("select count(*) from "+tableName+";");
		}else if(tableName!=null && moduleId!=null && DBSchemaMapping.TRANSCLOSURE.equals(tableName)){
			resultSet=statement.executeQuery("select count(*) from "+DBSchemaMapping.TRANSCLOSURE+" tc where exists (select tc.parentId, tc.childId from "+DBSchemaMapping.CONCEPT+" co where tc.childid =co.id and co.moduleid ="+moduleId+")");
		}else
			resultSet = statement.executeQuery("select count(*) from "+tableName+";");
		if(resultSet.next())
			numberOfRows= resultSet.getLong(1);
		}finally{
			DBUtil.closeResultSet(resultSet);
		}
		return numberOfRows;
	}
		/**
	 * Fetches Description data for the specified Refset Id using offset and returnLimit values.
	 * @param refsetId
	 * 					 identifier of Refset for which data is to be retrieved.

	 * @param statement
	 * 					 {@link Statement} instance.
	 * @param offset
	 * 					  the offset.
	 * @param returnLimit
	 * 					  the maximum number of results to be obtained.
	 * @return result set obtained on the execution of the query.
	 * @throws SQLException
	 */
	public static ResultSet retrieveRefsetColumns(String refsetId,Statement statement, long offset,long returnLimit) throws SQLException
	{
		String query="select "+DESCRIPTION_ID+", "+DESCRIPTION_ACTIVE+", "+DESCRIPTION_CONCEPT_ID+", "+DESCRIPTION_TYPE_ID+", "+DESCRIPTION_TERM+", "+DESCRIPTION_MODULE_ID +
				 ", "+DESCRIPTION_EFFECTIVE_TIME+", "+DBSchemaMapping.DESCRIPTION_CASE_SIGNIFICANCE_ID+", "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+", "+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+", "+DBSchemaMapping.REFSETLANG_REFSET_ID+", "+DBSchemaMapping.REFSETLANG_ACTIVE+", "+DBSchemaMapping.REFSETLANG_ACCEPTABILITY_ID
				 + ", "+DBSchemaMapping.REFSET_REFSET_ID+" ,"+DBSchemaMapping.REFSET_MODULE_ID+" , "+DBSchemaMapping.REFSET_ACTIVE+" from "+DESCRIPTION+","+REFSET+","+DBSchemaMapping.REFSETLANG+"  where "+ REFSET_REFSET_ID +"='"+refsetId+ "' AND "+ DESCRIPTION_CONCEPT_ID +"="+ REFSET_REFERENCED_COMPONENT_ID+
				 " and "+DBSchemaMapping.DESCRIPTION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+" and " +DESCRIPTION_ACTIVE+"=1 and "+ REFSET_ACTIVE +"=1"+";";
		return statement.executeQuery(query);
	}
	/**
	 * Fetches Description data for the specified Refset Id using offset and returnLimit values.
	 * @param refsetId
	 * 					 identifier of Refset for which data is to be retrieved.

	 * @param statement
	 * 					 {@link Statement} instance.
	 * @param offset
	 * 					  the offset.
	 * @param returnLimit
	 * 					  the maximum number of results to be obtained.
	 * @param moduleIdSet
	 * @return result set obtained on the execution of the query.
	 * @throws SQLException
	 */
	static StringBuilder br=new StringBuilder();
	public static ResultSet retrievecRefsetColumns(String refsetId,Statement statement, long offset,long returnLimit, Set<String> moduleIdSet,boolean flag) throws SQLException
	{
		String query="";

		if(flag) {
			EnumRefset [] r=EnumRefset.values();
			for(int i=0;i<r.length;i++) {
				if(r[i].getValue().equals("association type") || r[i].getValue().equals("attribute type"))
				query=query+"select "+DESCRIPTION_ID+", "+DESCRIPTION_ACTIVE+", "+DESCRIPTION_CONCEPT_ID+", "+DESCRIPTION_TYPE_ID+", "+DESCRIPTION_TERM+", "+DESCRIPTION_MODULE_ID +
					 ", "+DESCRIPTION_EFFECTIVE_TIME+", "+DBSchemaMapping.DESCRIPTION_CASE_SIGNIFICANCE_ID+", "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+", "+DBSchemaMapping.cREFSET_REFSET_ID+" , "+DBSchemaMapping.cREFSET_MODULE_ID+" , "+DBSchemaMapping.cREFSET_TARGETID+" , "+DBSchemaMapping.cREFSET_ACTIVE+" from "+DESCRIPTION+","+cREFSET+"  where "+ DBSchemaMapping.DESCRIPTION_ID +"="+ cREFSET_REFERENCED_COMPONENT_ID+ " and "+DBSchemaMapping.cREFSET_REFSET_ID+"='"+r[i].getId()+"'";
				if(i!=r.length-1) query=query+(" union ");

				br.append(" and "+DBSchemaMapping.cREFSET_REFSET_ID+"!='"+r[i].getId()+"'");
			}
			query=query+";";
		}
		for(String m:moduleIdSet){
			if(refsetId.equalsIgnoreCase("cRefset")){
				if(!flag) {
					query="select "+DESCRIPTION_ID+", "+DESCRIPTION_ACTIVE+", "+DESCRIPTION_CONCEPT_ID+", "+DESCRIPTION_TYPE_ID+", "+DESCRIPTION_TERM+", "+DESCRIPTION_MODULE_ID +
			 ", "+DESCRIPTION_EFFECTIVE_TIME+", "+DBSchemaMapping.DESCRIPTION_CASE_SIGNIFICANCE_ID+", "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+", "+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+", "+DBSchemaMapping.REFSETLANG_REFSET_ID+", "+DBSchemaMapping.REFSETLANG_ACTIVE+", "+DBSchemaMapping.REFSETLANG_ACCEPTABILITY_ID
			 + ", "+DBSchemaMapping.cREFSET_REFSET_ID+" , "+DBSchemaMapping.cREFSET_MODULE_ID+" , "+DBSchemaMapping.cREFSET_TARGETID+" , "+DBSchemaMapping.cREFSET_ACTIVE+" from "+DESCRIPTION+","+cREFSET+","+DBSchemaMapping.REFSETLANG+"  where "+  cREFSET_MODULE_ID +"='"+m+ "' AND "+ DESCRIPTION_CONCEPT_ID +"="+ cREFSET_REFERENCED_COMPONENT_ID+
			 " and "+DBSchemaMapping.DESCRIPTION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+br.toString()+" Limit "+offset+", "+returnLimit+";";
				}
			}
		if(refsetId.equalsIgnoreCase("Refset")){
						query="select "+DESCRIPTION_ID+", "+DESCRIPTION_ACTIVE+", "+DESCRIPTION_CONCEPT_ID+", "+DESCRIPTION_TYPE_ID+", "+DESCRIPTION_TERM+", "+DESCRIPTION_MODULE_ID +
					 ", "+DESCRIPTION_EFFECTIVE_TIME+", "+DBSchemaMapping.DESCRIPTION_CASE_SIGNIFICANCE_ID+", "+DBSchemaMapping.DESCRIPTION_LANGUAGE_CODE+", "+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+", "+DBSchemaMapping.REFSETLANG_REFSET_ID+", "+DBSchemaMapping.REFSETLANG_ACTIVE+", "+DBSchemaMapping.REFSETLANG_ACCEPTABILITY_ID
					 + ", "+DBSchemaMapping.REFSET_REFSET_ID+" , "+DBSchemaMapping.REFSET_MODULE_ID+" , "+DBSchemaMapping.REFSET_ACTIVE+" from "+DESCRIPTION+","+REFSET+","+DBSchemaMapping.REFSETLANG+"  where "+ REFSET_MODULE_ID +"='"+m+ "' AND "+ DESCRIPTION_CONCEPT_ID +"="+ REFSET_REFERENCED_COMPONENT_ID+
					 " and "+DBSchemaMapping.DESCRIPTION_ID+"="+DBSchemaMapping.REFSETLANG_REFERENCED_COMPONENT_ID+" Limit "+offset+", "+returnLimit+";";
		}
	}
		return statement.executeQuery(query);
	}


			/**
			 * Returns count of total number of rows of Description Table which are required for a particular Refset .
			 * @param moduleId
			 * 					module Identifier .
			 * @param statement
			 * 					{@link Statement} instance.
			 * @return refsetId_list
			 * 					distinct refsetId obtained on executing the query.
			 * @throws SQLException
			 */
			@SuppressWarnings("resource")
			public static HashMap<String, String> getDistinctRefsetId(String moduleId,String refsetId,Statement statement) throws SQLException{
				ResultSet resultSet=null;
				HashMap<String,String>refsetId_list=new HashMap<>();
				String query=null;
				try{
					if(refsetId==null){
					query="select distinct("+REFSET_REFSET_ID+"),"+DESCRIPTION_TERM+" from "+REFSET+","+DESCRIPTION
							+" where "+REFSET_MODULE_ID+"="+moduleId+" and "+REFSET_REFSET_ID+" = "+DESCRIPTION_CONCEPT_ID+" and "+DESCRIPTION_ACTIVE+"=1 and "
							+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+";";
					}
					else{
					query="select "+REFSET_REFSET_ID+" , "+DESCRIPTION_TERM+" from "+REFSET+" , "+DESCRIPTION
							+" where "+REFSET_REFSET_ID+"="+refsetId+" and "+DESCRIPTION_CONCEPT_ID+"="+REFSET_REFSET_ID+
							" and "+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DESCRIPTION_ACTIVE+"=1 ;";
					}
					resultSet=statement.executeQuery(query);
					if (!resultSet.next() ) {
						query="select "+REFSET_REFSET_ID+" from "+REFSET
								+" where "+REFSET_REFSET_ID+"="+refsetId+" limit 1;";
						resultSet=statement.executeQuery(query);
						while(resultSet.next()){
						refsetId_list.put(resultSet.getString(1),"Beta version of the reference set");
						}
					}
					else{
						do{
							refsetId_list.put(resultSet.getString(1),resultSet.getString(2));
						}
					while(resultSet.next());
					}

				}finally{
					DBUtil.closeResultSet(resultSet);
				}
				return refsetId_list;
			}
			/**
			 * Returns count of total number of rows of Description Table which are required for a particular Refset .
			 * @param moduleId
			 * 					module Identifier .
			 * @param statement
			 * 					{@link Statement} instance.
			 * @return refsetId_list
			 * 					distinct refsetId obtained on executing the query.
			 * @throws SQLException
			 */
			public static HashMap<String, String> getDistinctcRefsetId(String moduleId,Statement statement) throws SQLException{
				ResultSet resultSet=null;
				HashMap<String,String>refsetId_list=new HashMap<>();
				try{
					String query="select distinct("+cREFSET_REFSET_ID+"),"+DESCRIPTION_TERM+" from "+cREFSET+","+DESCRIPTION
							+" where "+cREFSET_MODULE_ID+"="+moduleId+" and "+cREFSET_REFSET_ID+" = "+DESCRIPTION_CONCEPT_ID+" and "+DESCRIPTION_ACTIVE+"=1 and "
							+DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+";";
					resultSet=statement.executeQuery(query);
					while(resultSet.next()){
						refsetId_list.put(resultSet.getString(1),resultSet.getString(2));
						}
				}finally{
					DBUtil.closeResultSet(resultSet);
				}
				return refsetId_list;
			}


	/**
	 * Returns count of total number of rows of Description Table which are required for a particular Refset .
	 * @param refsetId
	 * 					Refset Identifier .
	 * @param statement
	 * 					{@link Statement} instance.
	 * @return totalRows
	 * 					the total number of rows obtained on executing the query.
	 * @throws SQLException
	 */
	public static long getTotalRefsetRows(String refsetId, Statement statement ) throws SQLException{
		long numberOfRows=0;
		ResultSet resultSet=null;
		String query=null;
		try{
			if(refsetId.equalsIgnoreCase(DBSchemaMapping.REFSET))
				query="select count(*) from "+DESCRIPTION+","+REFSET+","+REFSETLANG+"  where "+ DESCRIPTION_CONCEPT_ID +"="+ REFSET_REFERENCED_COMPONENT_ID+" and " +DESCRIPTION_ID+"="+REFSETLANG_REFERENCED_COMPONENT_ID+";";
			else if(refsetId.equalsIgnoreCase(DBSchemaMapping.cREFSET))
				query="select count(*) from "+DESCRIPTION+","+cREFSET+","+REFSETLANG+"  where "+ DESCRIPTION_CONCEPT_ID +"="+ cREFSET_REFERENCED_COMPONENT_ID+" and " +DESCRIPTION_ACTIVE+"=1 and "+DESCRIPTION_ID+"="+REFSETLANG_REFERENCED_COMPONENT_ID+";";
			else
				query="select count(*) from "+DESCRIPTION+","+REFSET+"  where "+ REFSET_REFSET_ID +"='"+refsetId+ "' AND "+ DESCRIPTION_CONCEPT_ID +"="+ REFSET_REFERENCED_COMPONENT_ID+" and " +DESCRIPTION_ACTIVE+"=1 and "+ REFSET_ACTIVE +"=1"+";";
			resultSet= statement.executeQuery(query);
		if(resultSet.next())
			numberOfRows= resultSet.getLong(1);
		}finally{
		DBUtil.closeResultSet(resultSet);
		}
		return numberOfRows;
	}
	/**
	 * Returns count of total number of rows of Refset and cRefset Table.
	 * @param refsetId
	 * 					Refset Identifier .
	 * @param statement
	 * 					{@link Statement} instance.
	 * @return totalRows
	 * 					the total number of rows obtained on executing the query.
	 * @throws SQLException
	 */
	@SuppressWarnings("resource")
	public static long getTotalRefsetCount(String refsetId, Statement statement ) throws SQLException{
		long numberOfRows=0;
		ResultSet resultSet=null;
		String query=null;
		try{
			query="select count(*) from "+DBSchemaMapping.cREFSET+"  where "+cREFSET_REFSET_ID+"="+refsetId+" and "+cREFSET_ACTIVE+"=1 ;";
			resultSet= statement.executeQuery(query);
			if(resultSet.next()){
				numberOfRows= resultSet.getLong(1);
			}
			if(numberOfRows==0){
				query="select count(*) from "+DBSchemaMapping.REFSET+"  where "+ REFSET_ACTIVE +"=1 and "+REFSET_REFSET_ID+"="+refsetId+" ;";
				resultSet= statement.executeQuery(query);
				if(resultSet.next())
				numberOfRows= resultSet.getLong(1);
			}



		}finally{
		DBUtil.closeResultSet(resultSet);
		}
		return numberOfRows;
	}

	/**
	 * Returns count of total number of rows of an Extension in Concept Table.
	 * @param moduleId
	 * 					Module Identifier .
	 * @return totalRows
	 * 					the total number of rows obtained on executing the query.
	 * @throws SQLException
	 */
    public static long getTotalExtensionCount(String moduleId, Statement statement) throws SQLException{
        long numberOfRows=0;
        ResultSet resultSet=null;
        String query=null;
        try{
            query="select count(*) from "+DBSchemaMapping.CONCEPT+" where "+DBSchemaMapping.CONCEPT_MODULE_ID+"="+moduleId+" ;";
            resultSet= statement.executeQuery(query);
            if(resultSet.next()){
                numberOfRows= resultSet.getLong(1);
            }
		} finally {
            DBUtil.closeResultSet(resultSet);
        }
        return numberOfRows;
    }

	/**
	 * Returns Fully Specified Name (FSN) of a given concept ID.
	 * @param moduleId
	 * 					Module Identifier .
	 * @return conceptFSN
	 * 					Fully Specified Name of given concept ID
	 * @throws SQLException
	 */
    public static String getConceptFSN(String moduleId, Statement statement) throws SQLException{
        String conceptFSN = null;
        ResultSet resultSet=null;
        String query=null;
        try{
            query="select term from "+DBSchemaMapping.DESCRIPTION+" where "+DBSchemaMapping.DESCRIPTION_TYPE_ID+"="+EnumDescType.FULLY_SPECIFIED_NAME.getValue()+" and "+DBSchemaMapping.DESCRIPTION_CONCEPT_ID+"="+moduleId+" ;";
            resultSet= statement.executeQuery(query);
            if(resultSet.next()){
                conceptFSN= resultSet.getString(1);
            }
		} finally {
            DBUtil.closeResultSet(resultSet);
        }
        return conceptFSN;
    }
}
