/*******************************************************************************
 *  Copyright 2017-2018 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 cdac.medinfo.sdk.dcm30_04.services.hangingprotocol.dbhelper;

import java.io.IOException;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import cdac.medinfo.sdk.dcm30_04.baselibs.DataSet;
import cdac.medinfo.sdk.dcm30_04.baselibs.DicomV3Factory;
import cdac.medinfo.sdk.dcm30_04.baselibs.ExplicitDE;
import cdac.medinfo.sdk.dcm30_04.baselibs.ImplicitDE;
import cdac.medinfo.sdk.dcm30_04.baselibs.SQ;
import cdac.medinfo.sdk.dcm30_04.baselibs.Tag;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IDataElement;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IDataSet;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IDataTypeSQ;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IDicomValue;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IDictionaryReader;
import cdac.medinfo.sdk.dcm30_04.commoninterface.IItem;
import cdac.medinfo.sdk.dcm30_04.commoninterface.ITag;
import cdac.medinfo.sdk.dcm30_04.commoninterface.ITransferSyntax;
import cdac.medinfo.sdk.dcm30_04.exceptions.ParseException;
import cdac.medinfo.sdk.dcm30_04.services.dicomnetcommons.DataTypeConvertor;
import cdac.medinfo.sdk.dcm30_04.services.dimsecommons.DicomMessage;
import cdac.medinfo.sdk.dcm30_04.services.hangingprotocol.sourceimpl.HangingProtocol_RowDescriptor;
import cdac.medinfo.sdk.dcm30_04.utils.DicomConstants;

import cdac.medinfo.sdk.dcm30_04.utils.ICollectionIterator;
import cdac.medinfo.sdk.dcm30_04.utils.ISingleCollection;
import cdac.medinfo.sdk.dcm30_04.utils.SingleCollection;
import cdac.medinfo.sdk.dcm30_04.utils.StandardMethods;


/**
 * In this class, the queries for the database are written.
 * @author samta
 *
 */
public class HangingProtocol_DBQuery
{

	/**
	 * This is default Constructor for HangingProtocol_DBQuery.
	 */
	public HangingProtocol_DBQuery() {
		super();
	}
	private Connection objcon = null;
	//This set used in recursive method for retrieving matching HangingProtocolInfoQryDATASETUUIDs in Sequence.
	private Set<String> setIntersectedDatasetUIDList;// = new TreeSet<String>();

	Map<String,Integer> hs = new HashMap<String, Integer>();

	/**
	 * Sets Connection Object for used for querying Database.
	 * @param objcon object of Connection
	 */
	void setConnection(Connection objcon)
	{
		this.objcon = objcon;
	}
	/**
	 * Populates the SOP Instance information in Database
	 * @param objIDataSet
	 * @return short - shows status value which will be used in generation of response Refer EnumImplOpsStatus for supported response status values.
	 * @throws SQLException
	 */
	boolean storeInstanceInfo(IDataSet objIDataSet)throws SQLException
	{
		objcon.setAutoCommit(false);
		String strDatasetUUID = StandardMethods.generateUUID(); 

		String strValue = null;
		int iGroupNo,iElementNo,iVRType;
		for(IDataElement objDataElement : objIDataSet)
		{
			/*
			 * This If-Else block inserts DataElements in database.
			 * It inserts all first level data elements and for SQ element it inserts that SQ element and all item elements belonging to that SQ.  
			 */
			IDicomValue objIDicomValue = objDataElement.getDicomValue();
			iGroupNo = objDataElement.getTag().getGroupNumber();
			iElementNo = objDataElement.getTag().getElementNumber();
			iVRType = objIDicomValue.getIVRType();
			if(!(objIDicomValue instanceof SQ))
			{
				if(objIDicomValue.getValue() != null)
					strValue = DataTypeConvertor.dicomDatatoString(objIDicomValue);
				insertInstanceInfo(strDatasetUUID,iGroupNo ,iElementNo , false, null, strValue, null, null,0, iVRType);
			}
			else
			{
				String strSQId = StandardMethods.generateUUID();
				insertInstanceInfo(strDatasetUUID,iGroupNo ,iElementNo , true, null, null, strSQId, null, 0, iVRType);
				insertSQInfo(objIDicomValue,strDatasetUUID,strSQId,null,1);
			}
		}
		objcon.commit();
		objcon.setAutoCommit(true);

		return true;

	}


	/**
	 * Inserts DataElement of a sequence and all its Items.
	 */
	private boolean insertSQInfo(IDicomValue objIDicomValue,String strDatasetUID,String strSQUUid,String strParentSQID,int iDELevel)throws SQLException
	{
		SQ objSQDicomData = (SQ)objIDicomValue;
		ICollectionIterator<IItem> objSQItemIterator = objSQDicomData.iterator();
		String strValue = null;
		while(objSQItemIterator.hasNext())
		{
			/*
			 * Iterating over Items of provided sequence and inserting DataElements which are not SQ.
			 * for SQ it recursively calls itself with changed SQUUID and ParentSQID.
			 * A sequence and all 
			 */
			IItem objItem = objSQItemIterator.next();
			String strItemUUID = StandardMethods.generateUUID();
			int iGroupNo,iElementNo,iVRType;
			ICollectionIterator<IDataElement> objDEIterator = objItem.iterator();
			while(objDEIterator.hasNext())
			{
				IDataElement objDataElement = objDEIterator.next();
				IDicomValue objTempDicomData = objDataElement.getDicomValue();
				iGroupNo = objDataElement.getTag().getGroupNumber();
				iElementNo = objDataElement.getTag().getElementNumber();
				iVRType = objTempDicomData.getIVRType();
				if(!(objTempDicomData instanceof SQ))
				{
					if(objTempDicomData.getValue() != null)
						strValue = DataTypeConvertor.dicomDatatoString(objTempDicomData);
					insertInstanceInfo(strDatasetUID,iGroupNo,iElementNo, false, strParentSQID, strValue, strSQUUid, strItemUUID, iDELevel, iVRType);
				}
				else
				{
					String strSQId = StandardMethods.generateUUID();
					insertInstanceInfo(strDatasetUID,iGroupNo,iElementNo, true, strSQUUid, null, strSQId,strItemUUID, iDELevel, iVRType);
					insertSQInfo(objTempDicomData,strDatasetUID,strSQId,strSQUUid,++iDELevel);
				}
			}
		}
		return true;
	}

	/**
	 * Inserts the information of DataElement in Database
	 */
	private boolean insertInstanceInfo(String strDatasetUUID,int iGroupNumber,int iElementNumber,boolean isSQ,String strParentSQID,String strValue,
			String strSQID,String strItemID,int iDELevel,int iDEVR)throws SQLException
			{
		String strQuery = "insert into HangingProtocol_DEINFO(DEROWID,DATASETUUID,GROUPNUMBER,ELEMENTNUMBER,ISSQ,PARENTSQUID,VALUE,SQUUID,ITEMUUID,DELEVEL,DEVR) " +
				"values(?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = objcon.prepareStatement(strQuery);
		ps.setString(1,StandardMethods.generateUUID());
		ps.setString(2, strDatasetUUID);
		ps.setInt(3,iGroupNumber);
		ps.setInt(4,iElementNumber);
		if(isSQ)
			ps.setInt(5, 1);
		else
			ps.setInt(5, 0);
		ps.setString(6,strParentSQID);
		ps.setString(7, strValue);
		ps.setString(8,strSQID);
		ps.setString(9,strItemID);
		ps.setInt(10,iDELevel);
		ps.setInt(11,iDEVR);
		ps.executeUpdate();
		return true;
			}

	/**
	 * Depending on the keys in the IDataSet it finds the matching DataSets and returns status.
	 * According to the matched DataSets it populates the Dataset collection taken as out parameter.
	 * 
	 * @param objIDataSet The data set containing identifiers.
	 * @param objRstDataCollection the matched data set collection
	 * 
	 * @return	<tt>true</tt> if one or more matches are found, otherwise
	 * 			<tt>false</tt>
	 */
	boolean find(IDataSet objIDataSet, ISingleCollection<IDataSet> objRstDataCollection) throws SQLException 
	{
		//display(objIDataSet);


		ICollectionIterator<IDataElement> objIDataSetIterator = objIDataSet.iterator();
		setIntersectedDatasetUIDList = null ;
		Integer pixel = null ;
		ResultSet itemsRecordSet = null;
		Set<String> setCurrentResultDatasetUIDList = new HashSet<String>();


		while(objIDataSetIterator.hasNext())
		{
			IDataElement objDataElement = objIDataSetIterator.next();				
			int iGroupNumber = objDataElement.getTag().getGroupNumber();
			int iElementNumber = objDataElement.getTag().getElementNumber();

			if(!(objDataElement.getDicomValue() instanceof SQ))
			{
				if(iGroupNumber==114 && iElementNumber == 260)
				{
					String elementValue=objDataElement.getDicomValue().toString();
					if(elementValue!="")
					{
						pixel = Integer.parseInt(objDataElement.getDicomValue().toString());
					}
					else
						pixel=0;
					
					Statement objStmt = objcon.createStatement();

					String strQuery = 						  
								"Select DISTINCT DATASETUUID , VALUE from HangingProtocol_DEINFO where GROUPNUMBER = "+iGroupNumber +""
										+ "and ELEMENTNUMBER = "+iElementNumber ;

						// Find all HangingProtocolInfoQryDATASETUUIDs Which has this DataElement and Value
						itemsRecordSet = objStmt.executeQuery(strQuery);
						while(itemsRecordSet.next())
						{
							String uid = itemsRecordSet.getString("DATASETUUID");
							hs.put(uid, itemsRecordSet.getInt("VALUE"));
							//find(objIDataSet,uid);

						}
						itemsRecordSet.close();
						objStmt.close();
				}
			}
			else  
			{	
				//Add HangingProtocolInfoQryDATASETUUIDs for Sequences
				//get HangingProtocolInfoQryDATASETUUIDs  having matching records in Sequence Attributes.	
				//Set<String> setHangingProtocolInfoQryDatasetuuidsForSequence = 
				getHangingProtocolInfoQryDatasetUIDBySequence((SQ)objDataElement.getDicomValue(),1); //SQ,DELEVEL
			}			
		}
		if(pixel!=0)
		{
			String val = sortValue(pixel);
			//	setCurrentResultDatasetUIDList.add(itemsRecordSet.getString("DATASETUUID"));
			if(val!=null)
			{
				setCurrentResultDatasetUIDList.add(val);
			}
		}
		

		if(null == setIntersectedDatasetUIDList && setCurrentResultDatasetUIDList.size()>0)
			setIntersectedDatasetUIDList = new TreeSet<String>(setCurrentResultDatasetUIDList);
		else if(null != setIntersectedDatasetUIDList && setIntersectedDatasetUIDList.size()>0&& setCurrentResultDatasetUIDList.size()>0)
			setIntersectedDatasetUIDList.retainAll(setCurrentResultDatasetUIDList);


		// For HangingProtocolInfoQryDATASETUUID retrieve DataSet
		if(null != setIntersectedDatasetUIDList)
		{
			Iterator<String> objIntersectedDatasetUIDIterator = setIntersectedDatasetUIDList.iterator();
			while (objIntersectedDatasetUIDIterator.hasNext())
			{			
				objRstDataCollection.add(retrieveHangingProtocolInfoQryInstanceInfo(objIDataSet,objIntersectedDatasetUIDIterator.next().toString()));
			}
		}
		hs.clear();
		return true;
	}
	/**
	 * To find pixels
	 */
	boolean find(IDataSet objIDataSet,String uid) throws SQLException 
	{
		Statement objStmt = objcon.createStatement();
		
		String strQuery =	"Select VALUE from HangingProtocol_DEINFO where GROUPNUMBER = "+114 +""
				+ "and ELEMENTNUMBER = "+260+""+ "and DATASETUUID ='"+uid+"'" ;
		
		// Find all HangingProtocolInfoQryDATASETUUIDs Which has this DataElement and Value

		ResultSet itemsRecordSet = objStmt.executeQuery(strQuery);
		while(itemsRecordSet.next())
		{
			hs.put(uid, itemsRecordSet.getInt("VALUE"));

		}

		itemsRecordSet.close();
		objStmt.close();

		return false;

	}
	/**
	 * To Sort the pixel value .
	 */
	private String sortValue(Integer value)
	{
		ArrayList<Integer> arrlist = new ArrayList<>();
		Set<java.util.Map.Entry<String, Integer>> mapEntries = hs.entrySet();
		List<java.util.Map.Entry<String,Integer>> aList = new LinkedList<java.util.Map.Entry<String,Integer>>(mapEntries);
		Collections.sort(aList, new Comparator<java.util.Map.Entry<String,Integer>>() {
			@Override
			public int compare(java.util.Map.Entry<String, Integer> ele1,
					java.util.Map.Entry<String, Integer> ele2) {
				return ele1.getValue().compareTo(ele2.getValue());
			}
		});
		Map<String,Integer> aMap2 = new LinkedHashMap<String, Integer>();
		for(java.util.Map.Entry<String,Integer> entry: aList) {
			aMap2.put(entry.getKey(), entry.getValue());
		}

		// printing values after sorting of map
		String key=null;
		int flag=0;
		for(java.util.Map.Entry<String,Integer> entry : aMap2.entrySet()) 
		{
			arrlist.add(entry.getValue());
			if(value==0)
				return key ;

			if(value.equals(entry.getValue()))
			{
				flag=1;
				key=entry.getKey();
				break;
			}
		}

		if(flag==1)
			return key;
		else
		{
			Integer searchVal=search(value,arrlist);
			for(java.util.Map.Entry<String,Integer> entry : aMap2.entrySet()) 
			{
				if(searchVal.equals(entry.getValue()))
				{
					key=entry.getKey();
					break;
				}
			}
			return key;
		}

	}

	/**
	 * Search for matching pixel value.
	 * @param value 
	 * @param arrList 
	 * @return 
	 */
	private int search(Integer value , ArrayList<Integer> arrList)
	{
		int searchVal;
		ArrayList<Integer> minValue = new ArrayList<>();
		ArrayList<Integer> maxValue = new ArrayList<>();
		for(int i=0;i!=arrList.size();i++)
		{
			searchVal=value-arrList.get(i);
			if(searchVal<0)
			{
				minValue.add(searchVal);

			}
			else
			{
				maxValue.add(searchVal);
			}
		}
		if(minValue.isEmpty())
		{
			return value-maxValue.get(0);
		}
		int result = -minValue.get(0);            
		if(maxValue.isEmpty())
		{
			return value+result;
		}
		if(maxValue.get(maxValue.size()-1)>-minValue.get(0))
			return value+result;
		else
			return value-maxValue.get(maxValue.size()-1);
			
	}

	/**
	 * Retrieves the information of a given SQ and populates the Dataset.
	 * @param objSQDataElement
	 * @param iDELevel
	 * @throws SQLException
	 */
	private void getHangingProtocolInfoQryDatasetUIDBySequence(SQ objSQDataElement,int iDELevel) throws SQLException
	{
		ICollectionIterator<IItem> objSQItemIterator = objSQDataElement.iterator();

		while(objSQItemIterator.hasNext())
		{
			IItem objItem = objSQItemIterator.next();
			ICollectionIterator<IDataElement> objDataElementIterator = objItem.iterator();
			while(objDataElementIterator.hasNext())
			{
				IDataElement objDataElement = objDataElementIterator.next();							
				int iGroupNumber = objDataElement.getTag().getGroupNumber();
				int iElementNumber = objDataElement.getTag().getElementNumber();

				if(!(objDataElement.getDicomValue() instanceof SQ))
				{
					if(null != objDataElement.getDicomValue().toString())
					{
						Statement objStmt = objcon.createStatement();
						Set<String> settmpCurrentResultDatasetUIDList = new HashSet<String>();
						String strQuery = 						  
								"Select DISTINCT DATASETUUID from HangingProtocol_DEINFO where GROUPNUMBER = "+iGroupNumber +""
										+ "and ELEMENTNUMBER = "+iElementNumber+""
										+ "and VALUE ='"+ DataTypeConvertor.dicomDatatoString(objDataElement.getDicomValue())+"'" 
										+ "and DELEVEL = "+ iDELevel;

						// Find all HangingProtocolInfoQryDATASETUUIDs Which has this DataElement and Value
						ResultSet itemsRecordSet = objStmt.executeQuery(strQuery);					

						while(itemsRecordSet.next())
						{
							settmpCurrentResultDatasetUIDList.add(itemsRecordSet.getString("DATASETUUID"));
						}

						itemsRecordSet.close();
						objStmt.close();

						if(null == setIntersectedDatasetUIDList && settmpCurrentResultDatasetUIDList.size()>0)
							setIntersectedDatasetUIDList = new TreeSet<String>(settmpCurrentResultDatasetUIDList);
						else if(null != setIntersectedDatasetUIDList && setIntersectedDatasetUIDList.size()>0 && settmpCurrentResultDatasetUIDList.size()>0)
							setIntersectedDatasetUIDList.retainAll(settmpCurrentResultDatasetUIDList);
					}
				}
				else
				{
					getHangingProtocolInfoQryDatasetUIDBySequence((SQ)objDataElement.getDicomValue(),++iDELevel);
				}				
			}
		}
	}

	/**
	 * Retrieves the information of a given SOP Instance UID and populates the Dataset.
	 * @param objIDataSet instance of input Dataset
	 * @param strSOPInstanceUID value of SOPInstance UID
	 * @return objIDataSet instance of resultant Dataset
	 * @throws SQLException if SQL operation fails
	 */
	private IDataSet retrieveHangingProtocolInfoQryInstanceInfo(IDataSet objQryDataSet,String strDatasetUID)throws SQLException
	{
		IDataSet objResDataset = new DataSet(objQryDataSet.getTransferSyntax(),objQryDataSet.getDictionaryReader());
		ISingleCollection<HangingProtocol_RowDescriptor> objRowCollection = populateRows(strDatasetUID);
		generateDataSet(objResDataset,objRowCollection,objQryDataSet);
	//	display(objResDataset);
		return objResDataset;
	}

	/**
	 * Populates the collection of RowDescriptor for a given Data set UID
	 * @return ISingleCollection<HangingProtocolInfoQry_RowDescriptor>
	 */
	private ISingleCollection<HangingProtocol_RowDescriptor> populateRows(String strDatasetUID)throws SQLException
	{
		ISingleCollection<HangingProtocol_RowDescriptor> objRowCollection = new SingleCollection<HangingProtocol_RowDescriptor>();
		String query = "Select * from HangingProtocol_DEINFO where DATASETUUID = '"+ strDatasetUID + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		HangingProtocol_RowDescriptor objRowDescriptor = null;

		while(rs.next())
		{
			objRowDescriptor = new HangingProtocol_RowDescriptor();
			objRowDescriptor.setDEROWID(rs.getString("DEROWID"));
			objRowDescriptor.setDATASETUUID(rs.getString("DATASETUUID"));	
			objRowDescriptor.setGROUPNUMBER(rs.getInt("GROUPNUMBER"));
			objRowDescriptor.setELEMENTNUMBER(rs.getInt("ELEMENTNUMBER"));

			int isSQ = rs.getInt("ISSQ");
			if(1 == isSQ)				
				objRowDescriptor.setSQ(true);
			else
				objRowDescriptor.setSQ(false);			

			objRowDescriptor.setPARENTSQUID(rs.getString("PARENTSQUID"));
			objRowDescriptor.setVALUE(rs.getString("VALUE"));
			objRowDescriptor.setSQUUID(rs.getString("SQUUID"));
			objRowDescriptor.setITEMUUID(rs.getString("ITEMUUID"));
			objRowDescriptor.setDELEVEL(rs.getInt("DELEVEL"));
			objRowDescriptor.setDEVR(rs.getInt("DEVR"));

			objRowCollection.add(objRowDescriptor);
		}

		return objRowCollection;
	}

	/**
	 * Populates the Dataset from given collection of Dataelements Rows according to the queried Dataset.
	 */
	private void generateDataSet(IDataSet objIDataSet,ISingleCollection<HangingProtocol_RowDescriptor> objRowsCollection,IDataSet objQryDataSet)
	{
		IDictionaryReader objIDictionaryReader = objIDataSet.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objIDataSet.getTransferSyntax();
		IDataElement objIDataElement = null;
		int iGroupNumber,iElementNumber;

		populateFirstLevelDE(objIDataSet, objRowsCollection,objQryDataSet);

		ICollectionIterator<HangingProtocol_RowDescriptor> objRowsIterator = objRowsCollection.iterator();
		while(objRowsIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objRowsIterator.next();
			iGroupNumber = objRow.getGROUPNUMBER();
			iElementNumber = objRow.getELEMENTNUMBER();
			//	iVRType = objRow.getDEVR();
			if(objRow.isSQ() && objRow.getPARENTSQUID() == null)
			{
				IDataTypeSQ objSQ = DicomV3Factory.createInstance().createSequence(iGroupNumber, iElementNumber, objIDataSet.getTransferSyntax());
				objRowsCollection.remove(objRow);
				createSequence(objSQ,objRow.getSQUUID(),objRowsCollection);
				objRowsIterator = objRowsCollection.iterator();
				if(objQryDataSet == null || objQryDataSet.isPresent(iGroupNumber, iElementNumber))
				{
					if(objITransferSyntax.getVRTransferSyntax()==DicomConstants.DCM_VR_IMPLICIT)
						objIDataElement = new ImplicitDE(iGroupNumber, iElementNumber,objSQ,objSQ.getTransferSyntax(),objIDictionaryReader);
					else
						objIDataElement = new ExplicitDE(iGroupNumber,iElementNumber,DicomConstants.DCM_DT_SQ,objSQ);
					objIDataSet.addDataElement(objIDataElement);
					//	objIDataSet.addDataElement(StandardMethods.stringtoIDataElement(objRow.getVALUE(), iGroupNumber, iElementNumber, iVRType, objITransferSyntax, objIDictionaryReader));
				}
			}
		}
	}

	/**
	 * Populates the first level Data elements in Data set
	 */
	private void populateFirstLevelDE(IDataSet objRstDataSet,ISingleCollection<HangingProtocol_RowDescriptor> objHangingProtocolInfoQry_RowsList,IDataSet objQryDataSet)
	{
		IDictionaryReader objIDictionaryReader = objRstDataSet.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objRstDataSet.getTransferSyntax();
		//	IDataElement objIDataElement = null;
		int iVRType,iGroupNumber,iElementNumber;
		ICollectionIterator<HangingProtocol_RowDescriptor> objIterator = objHangingProtocolInfoQry_RowsList.iterator();
		while(objIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objIterator.next();
			iGroupNumber = objRow.getGROUPNUMBER();
			iElementNumber = objRow.getELEMENTNUMBER();
			iVRType = objRow.getDEVR(); //StandardMethods.getiVRtype(objIDictionaryReader.getVR(iGroupNumber,iElementNumber));
			if(!objRow.isSQ() && objRow.getITEMUUID() == null)
			{
				if(objQryDataSet == null || objQryDataSet.isPresent(iGroupNumber, iElementNumber))
				{
					objRstDataSet.addDataElement(DataTypeConvertor.stringtoDataElement(objRow.getVALUE(), iGroupNumber, iElementNumber, iVRType, objITransferSyntax, objIDictionaryReader));	
				}
				objHangingProtocolInfoQry_RowsList.remove(objRow);
				objIterator = objHangingProtocolInfoQry_RowsList.iterator();
			}
		}
	}

	/** 
	 * Populates the Data set with a Sequence and all its Items information 
	 */
	private void createSequence(IDataTypeSQ objSQ,String strSQUID,ISingleCollection<HangingProtocol_RowDescriptor> objHangingProtocolInfoQry_RowsList)
	{
		ICollectionIterator<HangingProtocol_RowDescriptor> objRowsIterator = objHangingProtocolInfoQry_RowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objRowsIterator.next();
			if(!objRow.isSQ() && objRow.getSQUUID() != null && objRow.getSQUUID().equals(strSQUID))
			{
				IItem objItem = objSQ.getNewItem();
				createSQItems(objItem, objRow.getITEMUUID(),objHangingProtocolInfoQry_RowsList);
				objRowsIterator = objHangingProtocolInfoQry_RowsList.iterator();
				objSQ.addItem(objItem);
			}
		}
	}

	/** 
	 * Populates the Item with information of all its DataElements 
	 */
	private void createSQItems(IItem objItem , String strItemUid,ISingleCollection<HangingProtocol_RowDescriptor> objHangingProtocolInfoQry_RowsList)
	{
		int iVRType,iGroupNumber,iElementNumber;
		IDictionaryReader objIDictionaryReader = objItem.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objItem.getTransferSyntax();
		IDataElement objIDataElement = null;
		ICollectionIterator<HangingProtocol_RowDescriptor> objRowsIterator = objHangingProtocolInfoQry_RowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objRowsIterator.next();
			iGroupNumber = objRow.getGROUPNUMBER();
			iElementNumber = objRow.getELEMENTNUMBER();
			iVRType = 	objRow.getDEVR();		//StandardMethods.getiVRtype(objIDictionaryReader.getVR(iGroupNumber,iElementNumber));
			if(objRow.getITEMUUID()!= null && objRow.getITEMUUID().equals(strItemUid))
			{
				if(!objRow.isSQ())
				{
					objItem.addDataElement(DataTypeConvertor.stringtoDataElement(objRow.getVALUE(), iGroupNumber, iElementNumber, iVRType, objITransferSyntax, objIDictionaryReader));

					objHangingProtocolInfoQry_RowsList.remove(objRow);
					objRowsIterator = objHangingProtocolInfoQry_RowsList.iterator();
				}
				else if(objRow.isSQ())
				{
					IDataTypeSQ objSQ = DicomV3Factory.createInstance().createSequence(iGroupNumber, iElementNumber, objItem.getTransferSyntax());
					objHangingProtocolInfoQry_RowsList.remove(objRow);
					createSequence(objSQ,objRow.getSQUUID(),objHangingProtocolInfoQry_RowsList);
					objRowsIterator = objHangingProtocolInfoQry_RowsList.iterator();
					if(objITransferSyntax.getVRTransferSyntax()==DicomConstants.DCM_VR_IMPLICIT)
						objIDataElement = new ImplicitDE(iGroupNumber, iElementNumber,objSQ,objSQ.getTransferSyntax(),objIDictionaryReader);
					else
						objIDataElement = new ExplicitDE(iGroupNumber,iElementNumber,DicomConstants.DCM_DT_SQ,objSQ);
					objItem.addDataElement(objIDataElement);
				}
			}
		}
	}

	/**
	 * Displays the DataElements of Dataset
	 */
	/*
	private void display(IDataSet objIDataSet)
	{
		ICollectionIterator<IDataElement> objIDataSetIterator = objIDataSet.iterator();

		System.out.println("Tag     Value");
		while(objIDataSetIterator.hasNext())
		{
			IDataElement objDataElement = objIDataSetIterator.next();				

			if(!(objDataElement.getDicomValue() instanceof SQ))
			{

				System.out.println(objDataElement.getTag().getGroupNumber()+"    "+
						objDataElement.getTag().getElementNumber()+"    "+
						objDataElement.getDicomValue().toString());
			}
			else  
			{
				displaySequence((SQ)objDataElement.getDicomValue(),"> ");					
			}
		}

	}

	*//**
	 * Displays the Dicom Data SQ.
	 *//*
	private void displaySequence(SQ objSQDataElement,String ss)
	{
		ICollectionIterator<IItem> objSQItemIterator = objSQDataElement.iterator();
		int i = 1;
		while(objSQItemIterator.hasNext())
		{
			System.out.println("ITEM :"+ i++ );
			IItem objItem = objSQItemIterator.next();
			ICollectionIterator<IDataElement> objDataElementIterator = objItem.iterator();
			while(objDataElementIterator.hasNext())
			{
				IDataElement objDataElement = objDataElementIterator.next();

				if(!(objDataElement.getDicomValue() instanceof SQ))
				{
					System.out.println(ss +objDataElement.getTag().getGroupNumber()+"    "+
							objDataElement.getTag().getElementNumber()+"    "+
							objDataElement.getDicomValue().toString());						 
				}
				else
				{
					displaySequence((SQ)objDataElement.getDicomValue(),ss+ss);
				}	 
			}
		}
	}
*/
	/**
	 * Retrieves the information of a given SOP Instance UID and populates the Dataset.
	 * @param objIDataSet instance of input Dataset
	 * @param strSOPInstanceUID value of SOPInstance UID
	 * @return objIDataSet instance of resultant Dataset
	 * @throws SQLException if SQL operation fails
	 */
	IDataSet retrieveHangingProtocolInstanceInfo(DicomMessage objDicomMessage ,ISingleCollection<ITag> objITagCollection,String strSOPInstanceUID)throws SQLException
	{

		IDataSet objResDataset = new DataSet();
		ISingleCollection<HangingProtocol_RowDescriptor> objRowCollection = populateRowsForGet( objDicomMessage,strSOPInstanceUID);
		if(objRowCollection.size() == 0)
			return null;
		generateDataSetForGet(objResDataset,objRowCollection,objITagCollection);
	//	display(objResDataset);
		return objResDataset;
	}
	/**
	 * Populates the Dataset from given collection of Data elements Rows according to the queried Dataset.
	 */
	private ISingleCollection<HangingProtocol_RowDescriptor> populateRowsForGet( DicomMessage objDicomMessage ,String strSOPInstanceUID) throws SQLException 
	{

		String strDatasetUID;
		try {
			strDatasetUID = getuid(objDicomMessage,strSOPInstanceUID );

			ISingleCollection<HangingProtocol_RowDescriptor> objRowCollection = new SingleCollection<HangingProtocol_RowDescriptor>();
			String query = "Select * from HangingProtocol_DEINFO where DATASETUUID = '"+ strDatasetUID + "'";

			PreparedStatement ps = objcon.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			HangingProtocol_RowDescriptor objRowDescriptor = null;

			while(rs.next())
			{

				objRowDescriptor = new HangingProtocol_RowDescriptor();
				objRowDescriptor.setDEROWID(rs.getString("DEROWID"));
				objRowDescriptor.setDATASETUUID(rs.getString("DATASETUUID"));	
				objRowDescriptor.setGROUPNUMBER(rs.getInt("GROUPNUMBER"));
				objRowDescriptor.setELEMENTNUMBER(rs.getInt("ELEMENTNUMBER"));

				int isSQ = rs.getInt("ISSQ");
				if(1 == isSQ)				
					objRowDescriptor.setSQ(true);
				else
					objRowDescriptor.setSQ(false);			

				objRowDescriptor.setPARENTSQUID(rs.getString("PARENTSQUID"));
				objRowDescriptor.setVALUE(rs.getString("VALUE"));
				objRowDescriptor.setSQUUID(rs.getString("SQUUID"));
				objRowDescriptor.setITEMUUID(rs.getString("ITEMUUID"));
				objRowDescriptor.setDELEVEL(rs.getInt("DELEVEL"));
				objRowDescriptor.setDEVR(rs.getInt("DEVR"));

				objRowCollection.add(objRowDescriptor);
			}

			return objRowCollection;

		} catch (ParseException | IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Populates the Dataset from given collection of Data elements Rows according to the queried Dataset.
	 */
	private void generateDataSetForGet(IDataSet objIDataSet,ISingleCollection<HangingProtocol_RowDescriptor> objRowsCollection,ISingleCollection<ITag> objITagCollection) 
	{

		IDictionaryReader objIDictionaryReader = objIDataSet.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objIDataSet.getTransferSyntax();
		IDataElement objIDataElement = null;
		int iGroupNumber,iElementNumber;

		populateFirstLevelDECollection(objIDataSet, objRowsCollection,objITagCollection);

		ICollectionIterator<HangingProtocol_RowDescriptor> objRowsIterator = objRowsCollection.iterator();
		while(objRowsIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objRowsIterator.next();
			iGroupNumber = objRow.getGROUPNUMBER();
			iElementNumber = objRow.getELEMENTNUMBER();
			if(objRow.isSQ() && objRow.getPARENTSQUID() == null)
			{
				IDataTypeSQ objSQ = DicomV3Factory.createInstance().createSequence(iGroupNumber, iElementNumber, objIDataSet.getTransferSyntax());

				objRowsCollection.remove(objRow);
				//Populates the all Items of this sequence 
				createSequence(objSQ,objRow.getSQUUID(),objRowsCollection);
				//Adding the SQ Item in Dataset
				objRowsIterator = objRowsCollection.iterator();
				if(objITagCollection == null || objITagCollection.contains(new Tag(iGroupNumber, iElementNumber)))
				{
					//Adding the SQ Item in Dataset
					if(objITransferSyntax.getVRTransferSyntax()==DicomConstants.DCM_VR_IMPLICIT)
						objIDataElement = new ImplicitDE(iGroupNumber, iElementNumber,objSQ,objSQ.getTransferSyntax(),objIDictionaryReader);
					else
						objIDataElement = new ExplicitDE(iGroupNumber,iElementNumber,DicomConstants.DCM_DT_SQ,objSQ);
					objIDataSet.addDataElement(objIDataElement);
				}
			}
		}	
	}
	/**
	 * Populates the first level Data elements in Dataset
	 */
	private void populateFirstLevelDECollection(IDataSet objRstDataSet,ISingleCollection<HangingProtocol_RowDescriptor> objRowsCollection,ISingleCollection<ITag> objITagCollection) {

		IDictionaryReader objIDictionaryReader = objRstDataSet.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objRstDataSet.getTransferSyntax();
		IDataElement objIDataElement = null;
		int iVRType,iGroupNumber,iElementNumber;
		ICollectionIterator<HangingProtocol_RowDescriptor> objIterator = objRowsCollection.iterator();
		while(objIterator.hasNext())
		{
			HangingProtocol_RowDescriptor objRow = objIterator.next();
			iGroupNumber = objRow.getGROUPNUMBER();
			iElementNumber = objRow.getELEMENTNUMBER();
			iVRType = objRow.getDEVR();
			if(!objRow.isSQ() && objRow.getITEMUUID() == null)
			{
				if(objITagCollection == null || objITagCollection.contains(new Tag(iGroupNumber, iElementNumber)))
				{
					objIDataElement = DataTypeConvertor.stringtoDataElement(objRow.getVALUE(),iGroupNumber,iElementNumber,iVRType,objITransferSyntax,objIDictionaryReader);

					objRstDataSet.addDataElement(objIDataElement);
				}
				objRowsCollection.remove(objRow);
				objIterator = objRowsCollection.iterator();
			}
		}
	}

	/**
	 * Checks whether provided SOP Instance is created or not.
	 * @param strSOPInstance value of SOP Instance UID 
	 * @return boolean shows status as success or failure
	 * @throws SQLException if sql operation fails
	 */
	boolean isSOPInstanceExist(String strSOPInstance)throws SQLException
	{
		String query = "Select Value from HangingProtocol_DEINFO where VALUE = '"+ strSOPInstance + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		if(rs.next())
			return true;

		return false;
	}
	/**
	 * Checks whether provided DATASETUUID is present in Database or not.
	 * @param strSOPInstance value of SOP Instance UID 
	 * @param objDicomMessage
	 * @return DATASETUUID for given SOP instance UID.
	 * @throws SQLException
	 * @throws ParseException
	 * @throws IOException
	 */
	String getuid(DicomMessage objDicomMessage,String strSOPInstanceUID) throws SQLException, ParseException, IOException
	{
		//display(objIDataSet);

		IDataSet objIDataSet = objDicomMessage.getDataSet();
	//	display(objIDataSet);
		ICollectionIterator<IDataElement> objIDataSetIterator = objDicomMessage.getDataSet().iterator();
		setIntersectedDatasetUIDList = null ;
		ResultSet itemsRecordSet = null;
		String uid = null ;
		Set<String> setCurrentResultDatasetUIDList = new HashSet<String>();
		while(objIDataSetIterator.hasNext())
		{
			IDataElement objDataElement = objIDataSetIterator.next();				
			if(!(objDataElement.getDicomValue() instanceof SQ))
			{

				if(!strSOPInstanceUID.isEmpty())
				{
					Statement objStmt = objcon.createStatement();


					String strQuery = 						  
							"Select DISTINCT DATASETUUID  from HangingProtocol_DEINFO where VALUE ='"+strSOPInstanceUID+"'" ;

					// Find all HangingProtocolInfoQryDATASETUUIDs Which has this DataElement and Value
					itemsRecordSet = objStmt.executeQuery(strQuery);
					while(itemsRecordSet.next())
					{
						uid= itemsRecordSet.getString("DATASETUUID");

					}
					itemsRecordSet.close();
					objStmt.close();


				}
			}
			else  
			{	
				//Add HangingProtocolInfoQryDATASETUUIDs for Sequences
				//get HangingProtocolInfoQryDATASETUUIDs  having matching records in Sequence Attributes.	
				//Set<String> setHangingProtocolInfoQryDatasetuuidsForSequence = 
				getHangingProtocolInfoQryDatasetUIDBySequence((SQ)objDataElement.getDicomValue(),1); //SQ,DELEVEL
			}			
		}

		return uid;
	}
	/**
	 * Depending on the keys in the IDataSet it finds the matching DataSets and returns status.
	 * According to the matched DataSets it populates the Dataset collection taken as out parameter.
	 * 
	 * @param objIDataSet The data set containing identifiers.
	 * @param objRstDataCollection the matched data set collection
	 * 
	 * @return	<tt>true</tt> if one or more matches are found, otherwise
	 * 			<tt>false</tt>
	 * @throws SQLException 
	 */
	public boolean findQuery(IDataSet objIDataSet,ISingleCollection<IDataSet> objRstDataCollection) throws SQLException 
	{
		//display(objIDataSet);
		ICollectionIterator<IDataElement> objIDataSetIterator = objIDataSet.iterator();
		setIntersectedDatasetUIDList = null;

		while(objIDataSetIterator.hasNext())
		{
			IDataElement objDataElement = objIDataSetIterator.next();				
			int iGroupNumber = objDataElement.getTag().getGroupNumber();
			int iElementNumber = objDataElement.getTag().getElementNumber();

			if(!(objDataElement.getDicomValue() instanceof SQ))
			{
				if(null != objDataElement.getDicomValue().toString())
				{
					Statement objStmt = objcon.createStatement();
					Set<String> setCurrentResultDatasetUIDList = new HashSet<String>();
					String strQuery = 						  
							"Select DISTINCT DATASETUUID from HangingProtocol_DEINFO where GROUPNUMBER = "+iGroupNumber +""
									+ "and ELEMENTNUMBER = "+iElementNumber+""
									+ "and VALUE ='"+DataTypeConvertor.dicomDatatoString(objDataElement.getDicomValue())+"'" ;
					// Find all HangingProtocolDATASETUUIDs Which has this DataElement and Value
					ResultSet itemsRecordSet = objStmt.executeQuery(strQuery);
					while(itemsRecordSet.next())
					{
						setCurrentResultDatasetUIDList.add(itemsRecordSet.getString("DATASETUUID"));
					}

					itemsRecordSet.close();
					objStmt.close();

					if(null == setIntersectedDatasetUIDList && setCurrentResultDatasetUIDList.size()>0)
						setIntersectedDatasetUIDList = new TreeSet<String>(setCurrentResultDatasetUIDList);
					else if(null != setIntersectedDatasetUIDList && setIntersectedDatasetUIDList.size()>0&& setCurrentResultDatasetUIDList.size()>0)
						setIntersectedDatasetUIDList.retainAll(setCurrentResultDatasetUIDList);

				}
			}
			else  
			{	
				//Add HANGINGPROTOCOLDATASETUUIDs for Sequences
				//get HANGINGPROTOCOLDATASETUUIDs  having matching records in Sequence Attributes.	
				//Set<String> setHangingProtocolDatasetuuidsForSequence  
				getHangingProtocolInfoQryDatasetUIDBySequence((SQ)objDataElement.getDicomValue(),1); //SQ,DELEVEL
			}			
		}

		// For HANGINGPROTOCOLDATASETUUIDs retrieve DataSet
		if(null != setIntersectedDatasetUIDList)
		{
			Iterator<String> objIntersectedDatasetUIDIterator = setIntersectedDatasetUIDList.iterator();
			while (objIntersectedDatasetUIDIterator.hasNext())
			{			
				objRstDataCollection.add(retrieveHangingProtocolInfoQryInstanceInfo(objIDataSet,objIntersectedDatasetUIDIterator.next().toString()));
			}
		}
		return true;
	}
	/**
	 * Depending SOP Instance UID  finds related DataSets and creates Instance . 
	 * @param objIDataSet The data set contains identifiers.
	 * @param objRstDataSetCollection the obj result data set collection
	 * @return short - shows status value which will be used in generation of response
	 * 					Refer EnumImplOpsStatus for supported response status values.
	 */
	public short retrieve(IDataSet objIDataSet,ISingleCollection<IDataSet> objRstDataCollection) throws SQLException 
	{
		ICollectionIterator<IDataElement> objIDataSetIterator = objIDataSet.iterator();
		setIntersectedDatasetUIDList = null;

		while(objIDataSetIterator.hasNext())
		{
			IDataElement objDataElement = objIDataSetIterator.next();				
			int iGroupNumber = objDataElement.getTag().getGroupNumber();
			int iElementNumber = objDataElement.getTag().getElementNumber();

			if(!(objDataElement.getDicomValue() instanceof SQ))
			{
				if(null != objDataElement.getDicomValue().toString())
				{
					Statement objStmt = objcon.createStatement();
					Set<String> setCurrentResultDatasetUIDList = new HashSet<String>();
					String strQuery = 						  
							"Select DISTINCT DATASETUUID from HangingProtocol_DEINFO where GROUPNUMBER = "+iGroupNumber +""
									+ "and ELEMENTNUMBER = "+iElementNumber+""
									+ "and VALUE ='"+DataTypeConvertor.dicomDatatoString(objDataElement.getDicomValue())+"'" 
									+ "and ITEMUUID is NULL and SQUUID is NULL and ISSQ = 0 and PARENTSQUID is NULL and DELEVEL=0";
					// Find all HangingProtocolDATASETUUIDs Which has this DataElement and Value
					ResultSet itemsRecordSet = objStmt.executeQuery(strQuery);
					while(itemsRecordSet.next())
					{
						setCurrentResultDatasetUIDList.add(itemsRecordSet.getString("DATASETUUID"));
					}

					itemsRecordSet.close();
					objStmt.close();

					if(null == setIntersectedDatasetUIDList && setCurrentResultDatasetUIDList.size()>0)
						setIntersectedDatasetUIDList = new TreeSet<String>(setCurrentResultDatasetUIDList);
					else if(null != setIntersectedDatasetUIDList && setIntersectedDatasetUIDList.size()>0&& setCurrentResultDatasetUIDList.size()>0)
						setIntersectedDatasetUIDList.retainAll(setCurrentResultDatasetUIDList);

				}
			}
			else  
			{	
				//Add HANGINGPROTOCOLDATASETUUIDs for Sequences
				//get HANGINGPROTOCOLDATASETUUIDs  having matching records in Sequence Attributes.	
				//Set<String> setHANGINGPROTOCOLDATASETUUIDsForSequence  
				getHangingProtocolInfoQryDatasetUIDBySequence((SQ)objDataElement.getDicomValue(),1); //SQ,DELEVEL
			}			
		}

		// For HANGINGPROTOCOLDATASETUUIDs retrieve DataSet
		if(null != setIntersectedDatasetUIDList)
		{
			Iterator<String> objIntersectedDatasetUIDIterator = setIntersectedDatasetUIDList.iterator();
			while (objIntersectedDatasetUIDIterator.hasNext())
			{			
				objRstDataCollection.add(retrieveHangingProtocolInfoQryInstanceInfo(objIDataSet,objIntersectedDatasetUIDIterator.next().toString()));
			}
		}
		return 0;
	}

}
