/*******************************************************************************
 *  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.dicomjavadb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cdac.medinfo.sdk.dcm30_04.commoninterface.IDictionaryReader;
import cdac.medinfo.sdk.dcm30_04.dictionaryreaderwriters.DictionaryReader;
import cdac.medinfo.sdk.dcm30_04.services.dicomnetcommons.DataTypeConvertor;
import cdac.medinfo.sdk.dcm30_04.services.dimsecommons.IQueryModel;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.CServices_QueryModel;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.DicomImage;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.ItemModel;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.Patient;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.SQModel;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.Series;
import cdac.medinfo.sdk.dcm30_04.services.javadbhelper.Study;
import cdac.medinfo.sdk.dcm30_04.utils.ICollectionIterator;
import cdac.medinfo.sdk.dcm30_04.utils.IPairedCollection;
import cdac.medinfo.sdk.dcm30_04.utils.ISingleCollection;
import cdac.medinfo.sdk.dcm30_04.utils.SingleCollection;
import cdac.medinfo.sdk.dcm30_04.utils.StandardMethods;

/**
 * This class interacts with database and performs queries fired.
 */
/**
 * @author samta
 *
 */

public class DBQuery  
{

	private Connection objcon = null;

	/**
	 * Sets connection
	 * @param objcon object of Connection
	 */
	void setConnection(Connection objcon)
	{
		this.objcon = objcon;
	}

	/**
	 * Inserts a record of Patient
	 * @param objPatient object of Patient.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	void insertPatientInfo(Patient objPatient) throws SQLException
	{
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from Patient where PatientID = '" + objPatient.getPatientID() +"'" );
		String strQuery = null;
		if(rs.next())
		{
			strQuery = "update Patient SET PatientName=?, PatientID=?, PatientBirthDate=?,PatientBirthTime=?, PatientSex=?,  OtherPatientID=?, OtherPatientNames=?,EthnicGroup=?,  PatientComments=?, NoOfPatientRelatedStudies=?,NoOfPatientRelatedSeries=?,NoOfPatientRelatedInstances=?  where PatientID = '"+ objPatient.getPatientID()+ "'";
		}
		else
		{
			strQuery = "insert into Patient (PatientName, PatientID,PatientBirthDate,PatientBirthTime, PatientSex, OtherPatientID, OtherPatientNames,EthnicGroup , PatientComments,NoOfPatientRelatedStudies ,NoOfPatientRelatedSeries,NoOfPatientRelatedInstances  ) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		}

		PreparedStatement ps = objcon.prepareStatement	(strQuery);
		ps.setString(1,objPatient.getPatientName());
		ps.setString(2,objPatient.getPatientID());
		ps.setString(3,objPatient.getPatientBirthDate());
		ps.setString(4,objPatient.getPatientBirthTime());
		ps.setString(5,objPatient.getPatientSex());
		ps.setString(6,DataTypeConvertor.arrayToString(objPatient.getOtherPatientIDs()));
		ps.setString(7,DataTypeConvertor.arrayToString(objPatient.getOtherPatientNames()));
		ps.setString(8,objPatient.getEthnicGroup());
		ps.setString(9,objPatient.getPatientComments());
		ps.setString(10,objPatient.getNumberOfPatientRelatedStudies());
		ps.setString(11,objPatient.getNumberOfPatientRelatedSeries());
		ps.setString(12,objPatient.getNumberOfPatientRelatedInstances());
		ps.executeUpdate();
		insertPatientSQInfo(objPatient);
	}

	/**
	 * Insert the sequence information related to patient.
	 */
	private void insertPatientSQInfo(Patient objPatient) throws SQLException
	{
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from PatientSQInfo where PatientID = '" + objPatient.getPatientID() +"'" );
		while(rs.next())
		{
			String strSQID = rs.getString("SequenceID");
			objStmt = objcon.createStatement();
			objStmt.executeUpdate("delete from SQInfo where SequenceID = '"+ strSQID + "'");
			objStmt.close();

			objStmt = objcon.createStatement();
			objStmt.executeUpdate("delete from SQDEINFO where SequenceID = '"+ strSQID + "'");
			objStmt.close();
		}
		objStmt = objcon.createStatement();
		objStmt.executeUpdate("delete from PatientSQInfo where PatientID = '"+ objPatient.getPatientID() + "'");
		objStmt.close();
		String strQuery = null;
		ISingleCollection<SQModel> objSequenceModelCol = objPatient.getSequenceModel();
		if(objSequenceModelCol.size() >0)
		{
			for(SQModel objSqModel : objSequenceModelCol)
			{
				strQuery = "insert into PatientSQInfo (PatientID,SequenceID ) values (?,?)";
				String strSequenceID = StandardMethods.generateUUID();
				PreparedStatement ps = objcon.prepareStatement	(strQuery);
				ps.setString(1,objPatient.getPatientID());
				ps.setString(2,strSequenceID);
				ps.executeUpdate();
				ps.close();

				strQuery = "insert into SQInfo (SequenceName,SequenceID) values (?,?)";
				ps = objcon.prepareStatement(strQuery);
				ps.setString(1,objSqModel.getSQName());
				ps.setString(2,strSequenceID);
				ps.executeUpdate();
				ps.close();

				insertSQModel(objSqModel, strSequenceID);
			}
		}
	}

	/**
	 * Insert the Sequence Model.
	 */
	private boolean insertSQModel(SQModel objSqModel,String strSequenceID)throws SQLException
	{
		IDictionaryReader objDictionaryReader = DictionaryReader.createInstance();
		String strSequenceName = objSqModel.getSQName();
		short[] shTag = objDictionaryReader.getTag(strSequenceName);
		String strVR = objDictionaryReader.getVR(shTag[0], shTag[1]);
		insertInstanceDEInfo(strSequenceID,StandardMethods.getiVRtype(strVR),shTag[0],shTag[1], true, null, null, strSequenceID,null);
		insertSQInfo(objSqModel,strSequenceID,strSequenceID,null);
		return true;
	}

	/**
	 * Insert the Sequence Model.
	 */
	private boolean insertInstanceDEInfo(String strSequenceID,int iVRType,int iGroupNumber,int iElementNumber,boolean isSQ,String strParentSQID,String strValue,String strSQID,String strItemID)throws SQLException
	{
		String strQuery = "insert into SQDEINFO (DERowID,SequenceID,VRType,GroupNumber,ElementNumber,IsSQ,ParentSQUID,Value,SQUUID,ItemUUID) values (?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = objcon.prepareStatement(strQuery);
		ps.setString(1,StandardMethods.generateUUID());
		ps.setString(2,strSequenceID);
		ps.setInt(3,iVRType);
		ps.setInt(4,iGroupNumber);
		ps.setInt(5,iElementNumber);
		if(isSQ)
			ps.setString(6,"Y");
		else
			ps.setString(6,"N");
		ps.setString(7,strParentSQID);
		ps.setString(8,strValue);
		ps.setString(9,strSQID);
		ps.setString(10,strItemID);
		ps.executeUpdate();
		return true;
	}

	/**
	 * Insert the Sequence Model.
	 */
	private boolean insertSQInfo(SQModel objSQModel,String strSequenceID,String strSQUUid,String strParentSQID)throws SQLException
	{
		ISingleCollection<ItemModel> objItemModelCollection = objSQModel.getItemModelCollection();
		IDictionaryReader objDictionaryReader = DictionaryReader.createInstance();
		for(ItemModel objItemModel : objItemModelCollection)
		{
			IPairedCollection<String, Object> objPairedCollection = objItemModel.getValue();
			Object[] arrObjValue = objPairedCollection.getKeys();
			String strItemUUID = StandardMethods.generateUUID();
			for(Object objValue : arrObjValue)
			{
				String strAttributeName = (String)objValue;
				Object objValueValue = null;
				String strValue = null;
				if(objPairedCollection.get(strAttributeName) != null)
				{
					objValueValue = objPairedCollection.get(strAttributeName);
					strValue = DataTypeConvertor.objectToString(objValueValue);
				}
				short[] shTag = objDictionaryReader.getTag(strAttributeName);
				String iVR = objDictionaryReader.getVR(shTag[0], shTag[1]);
				insertInstanceDEInfo(strSequenceID,StandardMethods.getiVRtype(iVR), shTag[0],shTag[1], false, strParentSQID, strValue, strSQUUid,strItemUUID);
			}
			ISingleCollection<SQModel> objSQModelFromItem = objItemModel.getSQModel();
			{
				for(SQModel objSQModelItem : objSQModelFromItem)
				{
					String strSQID = StandardMethods.generateUUID();
					insertSQInfo(objSQModelItem,strSequenceID,strSQID, strSequenceID);
				}
			}
		}
		return true;
	}

	/**
	 * Inserts a record of Series
	 * @param objSeries object of Series.
	 * @param strPatientID String value of Patient ID.
	 * @param strStudyInsUID String value of Study Instance UID.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	void insertSeriesInfo(Series objSeries,String strPatientID,String strStudyInsUID)throws SQLException
	{
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from Series where SeriesInstanceUID = '" + objSeries.getSeriesInstanceUID()+"'" + "AND StudyInstanceUID = '" + strStudyInsUID + "'" + "AND PatientID = '" + strPatientID + "'");
		String strQuery = null;
		if(rs.next())
		{
			strQuery = "update Series SET Modality=?, SeriesInstanceUID=?, SeriesNumber=?, SeriesDate=?, SeriesTime=?, SeriesDescription=?, PatientID=?, StudyInstanceUID=?, NoOfSeriesRelatedInstances=? where SeriesInstanceUID = '"+ objSeries.getSeriesInstanceUID()+"'"+"AND StudyInstanceUID = '" + strStudyInsUID + "'" + "AND PatientID = '" + strPatientID + "'";
		}
		else
		{
			strQuery = "insert into Series (Modality, SeriesInstanceUID, SeriesNumber, SeriesDate, SeriesTime, SeriesDescription, PatientID, StudyInstanceUID,NoOfSeriesRelatedInstances) values (?,?,?,?,?,?,?,?,?)";
		}
		PreparedStatement ps = objcon.prepareStatement	(strQuery);
		ps.setString(1,objSeries.getModality());
		ps.setString(2,objSeries.getSeriesInstanceUID());
		ps.setString(3,objSeries.getSeriesNumber());
		ps.setString(4,objSeries.getSeriesDate());
		ps.setString(5,objSeries.getSeriesTime());
		ps.setString(6,objSeries.getSeriesDescription());
		ps.setString(7,objSeries.getPatientID());
		ps.setString(8,objSeries.getStudyInstanceUID());
		ps.setString(9,objSeries.getNumberOfSeriesRelatedInstances());
		ps.executeUpdate();
	}

	/**
	 * Inserts a record of Study
	 * @param objStudy object of Study.
	 * @param strPatientID String value of Patient ID.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	void insertStudyInfo(Study objStudy,String strPatientID)throws SQLException
	{
		//PatientID ,StudyInstanceUID,StudyDate ,StudyTime ,AccessionNumber,StudyID,Modality,SOPClasses,ReferringPhysiciansName,StudyDescription,CodeValue,CodingSchemeDesignator,CodingSchemeVersion,CodeMeaning,NameOfPhysiciansReadingStudy,AdmittingDiagnosesDescription,ReferencedSopClassUID,ReferencedSopInstanceUID,IssuerOfPatientId ,PatientAge,PatientSize,PatientWeight,Occupation,AdditionalPatientHistory,OtherStudyNumbers,NoOfStudyRelatedSeries,NoOfStudyRelatedInstances,InterpretationAuthor
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from Study where StudyInstanceUID = '" + objStudy.getStudyInstanceUID()+"'" + "AND PatientID = '" + strPatientID + "'");
		String strQuery = null;
		if(rs.next())
		{
			strQuery = "update Study SET PatientID=?, StudyInstanceUID=?, StudyDate=?, StudyTime=?,AccessionNumber=?,StudyID=?,Modality=?, SOPClasses=?, ReferringPhysiciansName=?,StudyDescription=?,NameOfPhysiciansReadingStudy=?,AdmittingDiagnosesDescription=?,IssuerOfPatientId=?,PatientAge=?,PatientSize=?,PatientWeight=?,Occupation=?,AdditionalPatientHistory=?,OtherStudyNumbers=?,NoOfStudyRelatedSeries=?,NoOfStudyRelatedInstances=?,InterpretationAuthor=?  where StudyInstanceUID = '"+ objStudy.getStudyInstanceUID()+"'" + "AND PatientID = '" + strPatientID + "'";
		}
		else
		{
			strQuery = "insert into Study (PatientID ,StudyInstanceUID,StudyDate ,StudyTime ,AccessionNumber,StudyID,Modality,SOPClasses,ReferringPhysiciansName,StudyDescription,NameOfPhysiciansReadingStudy,AdmittingDiagnosesDescription,IssuerOfPatientId ,PatientAge,PatientSize,PatientWeight,Occupation,AdditionalPatientHistory,OtherStudyNumbers,NoOfStudyRelatedSeries,NoOfStudyRelatedInstances,InterpretationAuthor) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		}
		PreparedStatement ps = objcon.prepareStatement	(strQuery);

		ps.setString(1,objStudy.getPatientID());
		ps.setString(2,objStudy.getStudyInstanceUID());
		ps.setString(3,objStudy.getStudyDate());
		ps.setString(4,objStudy.getStudyTime());
		ps.setString(5,objStudy.getAccessionNumber());
		ps.setString(6,objStudy.getStudyID());
		ps.setString(7,DataTypeConvertor.arrayToString(objStudy.getModalitiesInStudy()));
		ps.setString(8,DataTypeConvertor.arrayToString(objStudy.getSOPClasses()));
		ps.setString(9,objStudy.getReferringPhysiansName());
		ps.setString(10,objStudy.getStudyDescription());		
		ps.setString(11,DataTypeConvertor.arrayToString(objStudy.getNameOfPhysicianReadingStudy()));
		ps.setString(12,DataTypeConvertor.arrayToString(objStudy.getAdmittingDiagnosesDescription()));		
		ps.setString(13,objStudy.getIssuerOfPatientID());
		ps.setString(14,objStudy.getPatientAge());
		ps.setString(15,objStudy.getPatientSize());
		ps.setString(16,objStudy.getPatientWeight());
		ps.setString(17,objStudy.getOccupation());
		ps.setString(18,objStudy.getAdditionalPatientHistory());
		ps.setString(19,DataTypeConvertor.arrayToString(objStudy.getOtherStudyNumbers()));
		ps.setString(20,objStudy.getNumberOfStudyRelatedSeries());
		ps.setString(21,objStudy.getNumberOfStudyRelatedInstances());
		ps.setString(22,objStudy.getInterpretationAuthor());

		ps.executeUpdate();
		insertStudySQInfo(objStudy);
	}

	/**
	 * Insert the Sequence Model.
	 */
	private void insertStudySQInfo(Study objStudy) throws SQLException
	{
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from StudySQInfo where StudyInstanceUID = '" + objStudy.getStudyInstanceUID() +"'" );
		while(rs.next())
		{
			String strSQID = rs.getString("SequenceID");
			objStmt = objcon.createStatement();
			objStmt.executeUpdate("delete from SQInfo where SequenceID = '"+ strSQID + "'");
			objStmt.close();

			objStmt = objcon.createStatement();
			objStmt.executeUpdate("delete from SQDEINFO where SequenceID = '"+ strSQID + "'");
			objStmt.close();
		}
		objStmt = objcon.createStatement();
		objStmt.executeUpdate("delete from StudySQInfo where StudyInstanceUID = '"+ objStudy.getStudyInstanceUID() + "'");
		objStmt.close();
		String strQuery = null;
		ISingleCollection<SQModel> objSequenceModelCol = objStudy.getSequenceModel();
		if(objSequenceModelCol.size() >0)
		{
			for(SQModel objSqModel : objSequenceModelCol)
			{
				strQuery = "insert into StudySQInfo (StudyInstanceUID,SequenceID ) values (?,?)";
				String strSequenceID = StandardMethods.generateUUID();
				PreparedStatement ps = objcon.prepareStatement	(strQuery);
				ps.setString(1,objStudy.getStudyInstanceUID());
				ps.setString(2,strSequenceID);
				ps.executeUpdate();
				ps.close();

				strQuery = "insert into SQInfo (SequenceName,SequenceID) values (?,?)";
				ps = objcon.prepareStatement(strQuery);
				ps.setString(1,objSqModel.getSQName());
				ps.setString(2,strSequenceID);
				ps.executeUpdate();
				ps.close();

				insertSQModel(objSqModel, strSequenceID);
			}
		}
	}

	/**
	 * Inserts a record of Info related to a DicomImage
	 * @param objDicomImage object of DicomImage.
	 * @param strPatientID String value of Patient ID.
	 * @param strStudyInsUID String value of Study Instance UID.
	 * @param strSeriesInsUID String value of Series Instance UID.
	 * @param strStoragePath path where file dump is stored.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	void insertDicomImageInfo(DicomImage objDicomImage,String strPatientID,String strStudyInsUID,String strSeriesInsUID,String strStoragePath)throws SQLException
	{
		Statement objStmt = objcon.createStatement();
		ResultSet rs = objStmt.executeQuery("Select * from Image where SOPInstanceUID = '" + objDicomImage.getSOPInstanceUID()+"'" + "AND SeriesInstanceUID = '" + strSeriesInsUID + "'" + "AND StudyInstanceUID = '" + strStudyInsUID + "'" + "AND PatientID = '" + strPatientID + "'");
		String strQuery = null;
		//If ImageInfo requested to insert is already present in the table, update the image info
		if(rs.next())
		{
			strQuery = "update Image SET PatientName=?, PatientID=?, PatientBirthDate=?, PatientSex=?, StudyInstanceUID=?, StudyDate=?, StudyTime=?, StudyID=?, AccessionNumber=?, PatientAge=?, Modality=?, " +
			"SeriesInstanceUID=?, SeriesNumber=?, ImageNumber=?, ImageComments=?, SOPClassUID=?, SOPInstanceUID=?, ImageType=?, FilePath=?,OverlayNumber=?,CurveNumber=?,LUTNumber=?,RelatedGeneralSOPClassUID=? where SOPInstanceUID = '"+ objDicomImage.getSOPInstanceUID()+"'" + "AND SeriesInstanceUID = '" + strSeriesInsUID + "'" + "AND StudyInstanceUID = '" + strStudyInsUID + "'" + "AND PatientID = '" + strPatientID + "'";
		}
		//If ImageInfo requested to insert is not present in the table, insert the image info
		else
		{
			strQuery = "insert into Image(PatientName, PatientID, PatientBirthDate, PatientSex, StudyInstanceUID, StudyDate, StudyTime, StudyID, AccessionNumber, PatientAge, Modality, " +
			"SeriesInstanceUID, SeriesNumber, ImageNumber, ImageComments, SOPClassUID, SOPInstanceUID, ImageType, FilePath,OverlayNumber,CurveNumber,LUTNumber,RelatedGeneralSOPClassUID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			{
				// System.out.println("Image Info inserted");
			}
		}

		PreparedStatement ps = objcon.prepareStatement	(strQuery);
		ps.setString(1,objDicomImage.getPatientName());
		ps.setString(2,objDicomImage.getPatientID());
		ps.setString(3,objDicomImage.getPatientBirthDate());
		ps.setString(4,objDicomImage.getPatientSex());
		ps.setString(5,objDicomImage.getStudyInstanceUID());
		ps.setString(6,objDicomImage.getStudyDate());
		ps.setString(7,objDicomImage.getStudyTime());
		ps.setString(8,objDicomImage.getStudyID());
		ps.setString(9,objDicomImage.getAccessionNumber());
		ps.setString(10,objDicomImage.getPatientAge());
		ps.setString(11,objDicomImage.getModality());
		ps.setString(12,objDicomImage.getSeriesInstanceUID());
		ps.setString(13,objDicomImage.getSeriesNumber());
		ps.setString(14,objDicomImage.getImageNumber());
		ps.setString(15,objDicomImage.getImageComments());
		ps.setString(16,objDicomImage.getSOPClassUID());
		ps.setString(17,objDicomImage.getSOPInstanceUID());
		ps.setString(18,objDicomImage.getImageType());
		ps.setString(19,strStoragePath +".dcm");
		ps.setString(20,objDicomImage.getOverlayNumber());
		ps.setString(21,objDicomImage.getCurveNumber());
		ps.setString(22,objDicomImage.getLUTNumber());
		ps.setString(23,DataTypeConvertor.arrayToString(objDicomImage.getRelatedGeneralSOPClassUID()));
		ps.executeUpdate();
	}

	/**
	 * Retrieves collection of PatientInfo.
	 * @return ISingleCollection<Patient> Collection of Patient.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	ISingleCollection<Patient> getPatientInfo()throws SQLException
	{
		ISingleCollection<Patient> objSingleCollection = new SingleCollection<Patient>();
		String query = "Select * from Patient";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			Patient objPatient = new Patient();
			objPatient.setPatientName(rs.getString("PatientName"));
			objPatient.setPatientID(rs.getString("PatientID"));
			objPatient.setPatientBirthDate(rs.getString("PatientBirthDate"));
			objPatient.setPatientBirthTime(rs.getString("PatientBirthTime"));
			objPatient.setPatientSex(rs.getString("PatientSex"));
			objPatient.setOtherPatientIDs(DataTypeConvertor.stringToArray(rs.getString("OtherPatientID")));
			objPatient.setOtherPatientNames(DataTypeConvertor.stringToArray(rs.getString("OtherPatientNames")));
			objPatient.setEthnicGroup(rs.getString("EthnicGroup"));
			objPatient.setPatientComments(rs.getString("PatientComments"));
			objPatient.setNumberOfPatientRelatedStudies(rs.getString("NoOfPatientRelatedStudies"));
			objPatient.setNumberOfPatientRelatedSeries(rs.getString("NoOfPatientRelatedSeries"));
			objPatient.setNumberOfPatientRelatedInstances(rs.getString("NoOfPatientRelatedInstances"));
			objSingleCollection.add(objPatient);
		}
		return objSingleCollection;
	}

	/**
	 * Retrieves collection of SeriesInfo.
	 * @return ISingleCollection<Series> Collection of Series.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	ISingleCollection<Series> getSeriesInfo()throws SQLException
	{
		ISingleCollection<Series> objSingleCollection = new SingleCollection<Series>();

		String query = "Select * from Series";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			Series objSeries = new Series();
			objSeries.setModality(rs.getString("Modality"));
			objSeries.setSeriesInstanceUID(rs.getString("SeriesInstanceUID"));
			objSeries.setSeriesNumber(rs.getString("SeriesNumber"));
			objSeries.setSeriesDate(rs.getString("SeriesDate"));
			objSeries.setSeriesTime(rs.getString("SeriesTime"));
			objSeries.setSeriesDescription(rs.getString("SeriesDescription"));
			objSeries.setPatientID(rs.getString("PatientID"));
			objSeries.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			objSeries.setNumberOfSeriesRelatedInstances(rs.getString("NoOfSeriesRelatedInstances"));
			objSingleCollection.add(objSeries);
		}
		return objSingleCollection;
	}

	/**
	 * Retrieves collection of StudyInfo.
	 * @return ISingleCollection<Study> Collection of Study.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	ISingleCollection<Study> getStudyInfo()throws SQLException
	{
		ISingleCollection<Study> objSingleCollection = new SingleCollection<Study>();
		String query = "Select * from Study";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			Study objStudy = new Study();
			objStudy.setPatientID(rs.getString("PatientID"));
			objStudy.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			objStudy.setStudyDate(rs.getString("StudyDate"));
			objStudy.setStudyTime(rs.getString("StudyTime"));
			objStudy.setAccessionNumber(rs.getString("AccessionNumber"));
			objStudy.setStudyID(rs.getString("StudyID"));
			objStudy.setModalitiesInStudy(DataTypeConvertor.stringToArray(rs.getString("Modality")));
			objStudy.setSOPClasses(DataTypeConvertor.stringToArray(rs.getString("SOPClasses")));
			objStudy.setReferringPhysiansName(rs.getString("ReferringPhysiciansName"));
			objStudy.setStudyDescription(rs.getString("StudyDescription"));
			objStudy.setNameOfPhysicianReadingStudy(DataTypeConvertor.stringToArray(rs.getString("NameOfPhysiciansReadingStudy")));
			objStudy.setAdmittingDiagnosesDescription(DataTypeConvertor.stringToArray(rs.getString("AdmittingDiagnosesDescription")));
			objStudy.setStudyDescription(rs.getString("IssuerOfPatientId"));
			objStudy.setPatientAge(rs.getString("PatientAge"));
			objStudy.setPatientSize(rs.getString("PatientSize"));
			objStudy.setPatientWeight(rs.getString("PatientWeight"));
			objStudy.setOccupation(rs.getString("Occupation"));
			objStudy.setAdditionalPatientHistory(rs.getString("AdditionalPatientHistory"));
			objStudy.setOtherStudyNumbers(DataTypeConvertor.stringToArray(rs.getString("OtherStudyNumbers")));
			objStudy.setNumberOfStudyRelatedSeries(rs.getString("NoOfStudyRelatedSeries"));
			objStudy.setNumberOfStudyRelatedInstances(rs.getString("NoOfStudyRelatedInstances"));
			objStudy.setInterpretationAuthor(rs.getString("InterpretationAuthor"));
			objSingleCollection.add(objStudy);
		}
		return objSingleCollection;
	}

	/**
	 * Retrieves collection of DicomImage.
	 * @return ISingleCollection<DicomImage> Collection of DicomImage.
	 * @throws SQLException Signals if SQL Exception occurs.
	 */
	ISingleCollection<DicomImage> getDicomImageInfo()throws SQLException
	{
		ISingleCollection<DicomImage> objSingleCollection = new SingleCollection<DicomImage>();

		String query = "Select * from Image";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			DicomImage objDicomImage = new DicomImage();
			objDicomImage.setPatientName(rs.getString("PatientName"));
			objDicomImage.setPatientID(rs.getString("PatientID"));
			objDicomImage.setPatientBirthDate(rs.getString("PatientBirthDate"));
			objDicomImage.setPatientSex(rs.getString("PatientSex"));
			objDicomImage.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			objDicomImage.setStudyDate(rs.getString("StudyDate"));
			objDicomImage.setStudyTime(rs.getString("StudyTime"));
			objDicomImage.setStudyID(rs.getString("StudyID"));
			objDicomImage.setAccessionNumber(rs.getString("AccessionNumber"));
			objDicomImage.setPatientAge(rs.getString("PatientAge"));
			objDicomImage.setModality(rs.getString("Modality"));
			objDicomImage.setSeriesInstanceUID(rs.getString("SeriesInstanceUID"));
			objDicomImage.setSeriesNumber(rs.getString("SeriesNumber"));
			objDicomImage.setImageNumber(rs.getString("ImageNumber"));
			objDicomImage.setImageComments(rs.getString("ImageComments"));
			objDicomImage.setSOPClassUID(rs.getString("SOPClassUID"));
			objDicomImage.setSOPInstanceUID(rs.getString("SOPInstanceUID"));
			objDicomImage.setImageType(rs.getString("ImageType"));
			objDicomImage.setOverlayNumber(rs.getString("OverlayNumber"));
			objDicomImage.setCurveNumber(rs.getString("CurveNumber"));
			objDicomImage.setLUTNumber(rs.getString("LUTNumber"));
			objDicomImage.setRelatedGeneralSOPClassUID(DataTypeConvertor.stringToArray(rs.getString("RelatedGeneralSOPClassUID")));
			objSingleCollection.add(objDicomImage);
		}
		return objSingleCollection;
	}

	/**
	 * This method generates the SQL query base on attributes present in the query model.
	 * @param objQueryModel Object of Query Model from which SQL query is generated.
	 * @return String - SQL Query generated.
	 */
	String generateSQLQuery(IQueryModel objQueryModel)
	{
		if(objQueryModel instanceof CServices_QueryModel)
		{
			CServices_QueryModel objCServices_QueryModel = (CServices_QueryModel)objQueryModel;
			String strQueryString = null;
			ISingleCollection<String> CollectionOfFieldList = new SingleCollection<String>();
			ISingleCollection<String> CollectionOfWhereClauseList = new SingleCollection<String>();

			ISingleCollection<Patient> objPatientList = objCServices_QueryModel.getPatientList();
			if(objPatientList != null && objPatientList.size() != 0)
			{
				ICollectionIterator<Patient> objPatListIterator = objPatientList.iterator();
				Patient objPatient = null;
				while(objPatListIterator.hasNext())
				{
					objPatient = objPatListIterator.next();

					if(objPatient.isPatientID())
					{
						if((objPatient.getPatientID()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientID",objPatient.getPatientID()));
					}

					if(objPatient.isOtherPatientID())
					{
						CollectionOfFieldList.add("Patient.OtherPatientID");
						if((objPatient.getOtherPatientIDs()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.OtherPatientID",DataTypeConvertor.arrayToString(objPatient.getOtherPatientIDs())));
					}
					if(objPatient.isOtherPatientNames())
					{
						CollectionOfFieldList.add("Patient.OtherPatientNames");
						if((objPatient.getOtherPatientNames()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.OtherPatientNames",DataTypeConvertor.arrayToString(objPatient.getOtherPatientNames())));
					}
					if(objPatient.isPatientBirthDate())
					{
						CollectionOfFieldList.add("Patient.PatientBirthDate");
						if((objPatient.getPatientBirthDate()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientBirthDate",objPatient.getPatientBirthDate()));
					}
					if(objPatient.isPatientBirthTime())
					{
						CollectionOfFieldList.add("Patient.PatientBirthTime");
						if((objPatient.getPatientBirthTime()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientBirthTime",objPatient.getPatientBirthTime()));
					}
					if(objPatient.isPatientComments())
					{
						CollectionOfFieldList.add("Patient.PatientComments");
						if((objPatient.getPatientComments()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientComments",objPatient.getPatientComments()));
					}
					if(objPatient.isPatientName())
					{
						CollectionOfFieldList.add("Patient.PatientName");
						if((objPatient.getPatientName()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientName",objPatient.getPatientName()));
					}
					if(objPatient.isPatientSex())
					{
						CollectionOfFieldList.add("Patient.PatientSex");
						if((objPatient.getPatientSex()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.PatientSex",objPatient.getPatientSex()));
					}
					if(objPatient.isEthnicGroup())
					{
						CollectionOfFieldList.add("Patient.EthnicGroup");
						if((objPatient.getEthnicGroup()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.EthnicGroup",objPatient.getEthnicGroup()));
					}
					if(objPatient.isNumberOfPatientRelatedStudies())
					{
						CollectionOfFieldList.add("Patient.NoOfPatientRelatedStudies");
						if((objPatient.getNumberOfPatientRelatedStudies()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.NoOfPatientRelatedStudies",objPatient.getNumberOfPatientRelatedStudies()));
					}
					if(objPatient.isNumberOfPatientRelatedSeries())
					{
						CollectionOfFieldList.add("Patient.NoOfPatientRelatedSeries");
						if((objPatient.getNumberOfPatientRelatedSeries()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.NoOfPatientRelatedSeries",objPatient.getNumberOfPatientRelatedSeries()));
					}
					if(objPatient.isNumberOfPatientRelatedInstances())
					{
						CollectionOfFieldList.add("Patient.NoOfPatientRelatedInstances");
						if((objPatient.getNumberOfPatientRelatedInstances()) != null)
							CollectionOfWhereClauseList.add (getWhereClauseString("Patient.NoOfPatientRelatedInstances",objPatient.getNumberOfPatientRelatedInstances()));
					}

					ISingleCollection<Study> objStudyList = objPatient.getStudyList();
					if(objStudyList != null && objStudyList.size() != 0)
					{
						ICollectionIterator<Study> objStudyListIterator = objStudyList.iterator();
						Study objStudy = null;
						while(objStudyListIterator.hasNext())
						{
							objStudy = objStudyListIterator.next();

							if(objStudy.isStudyInstanceUID())
							{
								if(objStudy.getStudyInstanceUID() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.StudyInstanceUID",objStudy.getStudyInstanceUID()));
							}

							if(objStudy.isPatientAge())
							{
								CollectionOfFieldList.add("Study.PatientAge");
								if(objStudy.getPatientAge() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.PatientAge",objStudy.getPatientAge()));
							}

							if(objStudy.isPatientWeight())
							{
								CollectionOfFieldList.add("Study.PatientWeight");
								if(objStudy.getPatientWeight() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.PatientWeight",objStudy.getPatientWeight()));
							}

							if(objStudy.isStudyID())
							{
								CollectionOfFieldList.add("Study.StudyID");
								if(objStudy.getStudyID() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.StudyID",objStudy.getStudyID()));
							}

							if(objStudy.isModalitesInStudy())
							{
								CollectionOfFieldList.add("Study.Modality");
								if(objStudy.getModalitiesInStudy() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.Modality",DataTypeConvertor.arrayToString(objStudy.getModalitiesInStudy())));
							}

							if(objStudy.isStudyDate())
							{
								CollectionOfFieldList.add("Study.StudyDate");
								if(objStudy.getStudyDate() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.StudyDate",objStudy.getStudyDate()));
							}

							if(objStudy.isStudyTime())
							{
								CollectionOfFieldList.add("Study.StudyTime");
								if(objStudy.getStudyTime() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.StudyTime",objStudy.getStudyTime()));
							}

							if(objStudy.isAccessionNumber())
							{
								CollectionOfFieldList.add("Study.AccessionNumber");
								if(objStudy.getAccessionNumber() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.AccessionNumber",objStudy.getAccessionNumber()));
							}	

							if(objStudy.isStudyDescription())
							{
								CollectionOfFieldList.add("Study.StudyDescription");
								if(objStudy.getStudyDescription() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.StudyDescription",objStudy.getStudyDescription()));
							}

							if(objStudy.isSOPClasses())
							{
								CollectionOfFieldList.add("Study.SOPClasses");
								if(objStudy.getSOPClasses() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.SOPClasses",DataTypeConvertor.arrayToString(objStudy.getSOPClasses())));
							}

							if(objStudy.isReferringPhysiansName())
							{
								CollectionOfFieldList.add("Study.ReferringPhysiciansName");
								if(objStudy.getReferringPhysiansName() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.ReferringPhysiciansName",objStudy.getReferringPhysiansName()));
							}

							if(objStudy.isNameOfPhysicianReadingStudy())
							{
								CollectionOfFieldList.add("Study.NameOfPhysiciansReadingStudy");
								if(objStudy.getNameOfPhysicianReadingStudy() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.NameOfPhysiciansReadingStudy",DataTypeConvertor.arrayToString(objStudy.getNameOfPhysicianReadingStudy())));
							}

							if(objStudy.isAdmittingDiagnosesDescription())
							{
								CollectionOfFieldList.add("Study.AdmittingDiagnosesDescription");
								if(objStudy.getAdmittingDiagnosesDescription() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.AdmittingDiagnosesDescription",DataTypeConvertor.arrayToString(objStudy.getAdmittingDiagnosesDescription())));
							}

							if(objStudy.isIssuerOfPatientID())
							{
								CollectionOfFieldList.add("Study.IssuerOfPatientId");
								if(objStudy.getIssuerOfPatientID() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.IssuerOfPatientId",objStudy.getIssuerOfPatientID()));
							}

							if(objStudy.isPatientSize())
							{
								CollectionOfFieldList.add("Study.PatientSize");
								if(objStudy.getPatientSize() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.PatientSize",objStudy.getPatientSize()));
							}

							if(objStudy.isOccupation())
							{
								CollectionOfFieldList.add("Study.Occupation");
								if(objStudy.getOccupation() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.Occupation",objStudy.getOccupation()));
							}

							if(objStudy.isAdditionalPatientHistory())
							{
								CollectionOfFieldList.add("Study.AdditionalPatientHistory");
								if(objStudy.getAdditionalPatientHistory() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.AdditionalPatientHistory",objStudy.getAdditionalPatientHistory()));
							}

							if(objStudy.isOtherStudyNumbers())
							{
								CollectionOfFieldList.add("Study.OtherStudyNumbers");
								if(objStudy.getOtherStudyNumbers() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.OtherStudyNumbers",DataTypeConvertor.arrayToString(objStudy.getOtherStudyNumbers())));
							}

							if(objStudy.isNumberOfStudyRelatedSeries())
							{
								CollectionOfFieldList.add("Study.NoOfStudyRelatedSeries");
								if(objStudy.getNumberOfStudyRelatedSeries() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.NoOfStudyRelatedSeries",objStudy.getNumberOfStudyRelatedSeries()));
							}

							if(objStudy.isNumberOfStudyRelatedInstances())
							{
								CollectionOfFieldList.add("Study.NoOfStudyRelatedInstances");
								if(objStudy.getNumberOfStudyRelatedInstances() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.NoOfStudyRelatedInstances",objStudy.getNumberOfStudyRelatedInstances()));
							}

							if(objStudy.isInterpretationAuthor())
							{
								CollectionOfFieldList.add("Study.InterpretationAuthor");
								if(objStudy.getInterpretationAuthor() != null)
									CollectionOfWhereClauseList.add (getWhereClauseString("Study.InterpretationAuthor",objStudy.getInterpretationAuthor()));
							}

							ISingleCollection<Series> objSeriesList = objStudy.getSeriesList();
							if(objSeriesList != null && objSeriesList.size() != 0)
							{
								ICollectionIterator<Series> objSeriesListIterator = objSeriesList.iterator();
								Series objSeries = null;
								while(objSeriesListIterator.hasNext())
								{
									objSeries = objSeriesListIterator.next();

									if(objSeries.isSeriesInstanceUID())
									{
										if(objSeries.getSeriesInstanceUID() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.SeriesInstanceUID",objSeries.getSeriesInstanceUID()));
									}

									if(objSeries.isSeriesNumber())
									{
										CollectionOfFieldList.add("Series.SeriesNumber");
										if(objSeries.getSeriesNumber() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.SeriesNumber",objSeries.getSeriesNumber()));
									}

									if(objSeries.isSeriesDate())
									{
										CollectionOfFieldList.add("Series.SeriesDate");
										if(objSeries.getSeriesDate() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.SeriesDate",objSeries.getSeriesDate()));
									}

									if(objSeries.isSeriesTime())
									{
										CollectionOfFieldList.add("Series.SeriesTime");
										if(objSeries.getSeriesTime() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.SeriesTime",objSeries.getSeriesTime()));
									}

									if(objSeries.isSeriesDescription())
									{
										CollectionOfFieldList.add("Series.SeriesDescription");
										if(objSeries.getSeriesDescription() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.SeriesDescription",objSeries.getSeriesDescription()));
									}

									if(objSeries.isModality())
									{
										CollectionOfFieldList.add("Series.Modality");
										if(objSeries.getModality() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.Modality",objSeries.getModality()));
									}

									if(objSeries.isNumberOfSeriesRelatedInstances())
									{
										CollectionOfFieldList.add("Series.NoOfSeriesRelatedInstances");
										if(objSeries.getNumberOfSeriesRelatedInstances() != null)
											CollectionOfWhereClauseList.add (getWhereClauseString("Series.NoOfSeriesRelatedInstances",objSeries.getNumberOfSeriesRelatedInstances()));
									}

									ISingleCollection<DicomImage> objImageList = objSeries.getDicomImageList();
									if(objImageList != null && objImageList.size() != 0)
									{
										ICollectionIterator<DicomImage> objImageIterator = objImageList.iterator();
										DicomImage objDicomImage = null;
										while(objImageIterator.hasNext())
										{
											objDicomImage = objImageIterator.next();

											if(objDicomImage.isSOPInstanceUID())
											{
												if(objDicomImage.getSOPInstanceUID() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.SOPInstanceUID",objDicomImage.getSOPInstanceUID()));
											}

											if(objDicomImage.isAccessionNumber())
											{
												CollectionOfFieldList.add("Image.AccessionNumber");
												if(objDicomImage.getAccessionNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.AccessionNumber",objDicomImage.getAccessionNumber()));
											}

											if(objDicomImage.isImageType())
											{
												CollectionOfFieldList.add("Image.ImageType");
												if(objDicomImage.getImageType() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.ImageType",objDicomImage.getImageType()));
											}

											if(objDicomImage.isModality())
											{
												CollectionOfFieldList.add("Image.Modality");
												if(objDicomImage.getModality() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.Modality",objDicomImage.getModality()));
											}

											if(objDicomImage.isPatientBirthDate())
											{
												CollectionOfFieldList.add("Image.PatientBirthDate");
												if(objDicomImage.getPatientBirthDate() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.PatientBirthDate",objDicomImage.getPatientBirthDate()));
											}

											if(objDicomImage.isPatientAge())
											{
												CollectionOfFieldList.add("Image.PatientAge");
												if(objDicomImage.getPatientAge() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.PatientAge",objDicomImage.getPatientAge()));
											}

											if(objDicomImage.isPatientSex())
											{
												CollectionOfFieldList.add("Image.PatientSex");
												if(objDicomImage.getPatientSex() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.PatientSex",objDicomImage.getPatientSex()));
											}

											if(objDicomImage.isPatientName())
											{
												CollectionOfFieldList.add("Image.PatientName");
												if(objDicomImage.getPatientName() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.PatientName",objDicomImage.getPatientName()));
											}

											if(objDicomImage.isImageNumber())
											{
												CollectionOfFieldList.add("Image.ImageNumber");
												if(objDicomImage.getImageNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.ImageNumber",objDicomImage.getImageNumber()));
											}

											if(objDicomImage.isSeriesNumber())
											{
												CollectionOfFieldList.add("Image.SeriesNumber");
												if(objDicomImage.getSeriesNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.SeriesNumber",objDicomImage.getSeriesNumber()));
											}

											if(objDicomImage.isImageComments())
											{
												CollectionOfFieldList.add("Image.ImageComments");
												if(objDicomImage.getImageComments() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.ImageComments",objDicomImage.getImageComments()));
											}

											if(objDicomImage.isSOPClassUID())
											{
												CollectionOfFieldList.add("Image.SOPClassUID");
												if(objDicomImage.getSOPClassUID() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.SOPClassUID",objDicomImage.getSOPClassUID()));
											}

											if(objDicomImage.isStudyDate())
											{
												CollectionOfFieldList.add("Image.StudyDate");
												if(objDicomImage.getStudyDate() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.StudyDate",objDicomImage.getStudyDate()));
											}

											if(objDicomImage.isStudyID())
											{
												CollectionOfFieldList.add("Image.StudyID");
												if(objDicomImage.getStudyID() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.StudyID",objDicomImage.getStudyID()));
											}

											if(objDicomImage.isStudyTime())
											{
												CollectionOfFieldList.add("Image.StudyTime");
												if(objDicomImage.getStudyTime() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.StudyTime",objDicomImage.getStudyTime()));
											}

											if(objDicomImage.isOverlayNumber())
											{
												CollectionOfFieldList.add("Image.OverlayNumber");
												if(objDicomImage.getOverlayNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.OverlayNumber",objDicomImage.getOverlayNumber()));
											}

											if(objDicomImage.isCurveNumber())
											{
												CollectionOfFieldList.add("Image.CurveNumber");
												if(objDicomImage.getCurveNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.CurveNumber",objDicomImage.getCurveNumber()));
											}

											if(objDicomImage.isLUTNumber())
											{
												CollectionOfFieldList.add("Image.LUTNumber");
												if(objDicomImage.getLUTNumber() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.LUTNumber",objDicomImage.getLUTNumber()));
											}

											if(objDicomImage.isRelatedGeneralSOPClassUID())
											{
												CollectionOfFieldList.add("Image.RelatedGeneralSOPClassUID");
												if(objDicomImage.getRelatedGeneralSOPClassUID() != null)
													CollectionOfWhereClauseList.add (getWhereClauseString("Image.RelatedGeneralSOPClassUID",DataTypeConvertor.arrayToString(objDicomImage.getRelatedGeneralSOPClassUID())));
											}
										}
									}
								}
							}
						}
					}
				}

				CollectionOfWhereClauseList.add ("Patient.PatientID= Study.PatientID");

				CollectionOfWhereClauseList.add ("Study.StudyInstanceUID= Series.StudyInstanceUID AND Study.PatientID = Series.PatientID");

				CollectionOfWhereClauseList.add ("Series.SeriesInstanceUID=Image.SeriesInstanceUID And Series.PatientID = Image.PatientID AND Series.StudyInstanceUID = Image.StudyInstanceUID");

				String strTableStatement = null,strWhereStatement = null,strSelectStatement = null;
				strSelectStatement = "SELECT Distinct Patient.PatientID,Study.StudyInstanceUID,Series.SeriesInstanceUID,Image.SOPInstanceUID,Image.FilePath";
				int nFieldListSize=CollectionOfFieldList.size();
				if(0!=nFieldListSize)
				{
					for(int nFieldIndex=0;nFieldIndex<nFieldListSize;nFieldIndex++)
					{
						strSelectStatement+=","+CollectionOfFieldList.get(nFieldIndex);
					}
				}

				strTableStatement = "FROM Patient,Study,Series,Image";
				int nWhereClauseListSize=CollectionOfWhereClauseList.size();
				if(0!=nWhereClauseListSize)
				{
					strWhereStatement= "WHERE (";
					for(int nWhereClauseIndex=0;nWhereClauseIndex<nWhereClauseListSize;nWhereClauseIndex++)
					{
						strWhereStatement=strWhereStatement+" " + CollectionOfWhereClauseList.get(nWhereClauseIndex);
						if(nWhereClauseIndex !=nWhereClauseListSize-1)
							strWhereStatement=strWhereStatement+" AND";
						else
							strWhereStatement=strWhereStatement+")";
					}
				}

				if(strSelectStatement !=null  || strTableStatement != null)
					strQueryString = strSelectStatement + " " + strTableStatement + " "+strWhereStatement;

				return strQueryString;
			}
			else
				return null;
		}
		else
			return null;
	}

	/**
	 * This method populates resultant CServices_QueryModel according to the given find parameter in CServices_QueryModel.
	 * @param objRqQueryModel object of CServices_QueryModel.
	 * @param objRstQueryModel object of resultant CServices_QueryModel .
	 * @return boolean true if matches are found.
	 * 				   false if no match is found.
	 */
	boolean find(IQueryModel objRqQueryModel,IQueryModel objRstQueryModel) throws SQLException
	{
		String strQuery = generateSQLQuery(objRqQueryModel);
		PreparedStatement ps =  objcon.prepareStatement(strQuery);
		ResultSet rs = ps.executeQuery();
		return generateQueryResult(rs, objRqQueryModel, objRstQueryModel);
	}

	/**
	 * Retrieves  collection of dicom files path according to the given find parameter in CServices_QueryModel.
	 * @param objQueryModel object of CServices_QueryModel.
	 * @return ISingleCollection<String>  collection of dicom files path.
	 */
	ISingleCollection<String> retrieve(IQueryModel objQueryModel) throws SQLException
	{
		ISingleCollection<String> objFilePathCollection = new SingleCollection<String>();
		String strQuery = generateSQLQuery(objQueryModel);
		PreparedStatement ps = objcon.prepareStatement(strQuery);
		ResultSet rsQuery = ps.executeQuery();
		while(rsQuery.next())
		{
			objFilePathCollection.add(rsQuery.getString("FilePath"));
		}
		return objFilePathCollection;
	}

	/**
	 * This function generate the Query Result.
	 * @param rs the result set from which resultant Query model is populated.
	 * @param objRqQueryModel  object of resultant CServices_QueryModel .
	 * @param objQueryModel object of CServices_QueryModel.
	 * @return boolean true if resultant CServices_QueryModel is populated successfully 
	 * 				   false if no matches are found..
	 * @throws SQLException Signals SQLException if SQL operation fails.
	 */
	public boolean generateQueryResult(ResultSet rs, IQueryModel objRqQueryModel,IQueryModel objQueryModel)throws SQLException
	{
		if((objQueryModel instanceof CServices_QueryModel) && objRqQueryModel instanceof CServices_QueryModel)
		{
			CServices_QueryModel objRqCServQueryModel = (CServices_QueryModel)objRqQueryModel;
			CServices_QueryModel objCServQueryModel = (CServices_QueryModel)objQueryModel;
			ISingleCollection<Patient> objTempPatientList = null;
			if(objRqCServQueryModel.getPatientList() != null)
			{
				objTempPatientList = new SingleCollection<Patient>();
				objCServQueryModel.setPatientList(objTempPatientList);

				Patient objPatient = null;
				Study objStudy = null;
				Series objSeries = null;

				Patient objQryPatient = objRqCServQueryModel.getPatientList().elementAt(0);
				Study objQryStudy = objQryPatient.getStudyList().elementAt(0);
				Series objQrySeries = objQryStudy.getSeriesList().elementAt(0);
				DicomImage objQryDicomImage = objQrySeries.getDicomImageList().elementAt(0);

				if(rs != null)
				{
					while(rs.next())
					{
						String strPatientID = rs.getString("PatientID");
						objPatient = objCServQueryModel.isPIDPresent(strPatientID);
						if(objPatient != null)
						{
							String strStudyInsUID = rs.getString("StudyInstanceUID");
							ISingleCollection<Study> objStudyList = objPatient.getStudyList();

							objStudy = objPatient.isStudyInsUIDPresent(strStudyInsUID);
							if(objStudy != null)
							{
								String strSeriesInstanceUID = rs.getString("SeriesInstanceUID");
								ISingleCollection<Series> objSeriesList = objStudy.getSeriesList();

								objSeries = objStudy.isSeriesInsUIDPresent(strSeriesInstanceUID);
								if(objSeries != null)
								{
									DicomImage objTempDicomImage = getDicomImagePopulated(rs,objQryDicomImage);
									ISingleCollection<DicomImage> objImageList = objSeries.getDicomImageList();
									objImageList.add(objTempDicomImage);
									objSeries.setDicomImageList(objImageList);
								}
								else
								{
									objSeries = getSeriesPopulated(rs,objQrySeries);
								}
								objSeriesList.add(objSeries);
								objStudy.setSeriesList(objSeriesList);
							}
							else
							{
								objStudy = getStudyPopulated(rs,objQryStudy);
							}
							objStudyList.add(objStudy);
							objPatient.setStudyList(objStudyList);
						}
						else
						{
							objPatient = getPatientPopulated(rs,objQryPatient);
							if(objPatient != null)
								objTempPatientList.add(objPatient);
						}
					}
					return true;
				}
				//else if(retrieveSQ(strUID, strTableName, strUIDName))
			}
			return false;
		}
		else
			return false;
	}

	/**
	 * populates Dicom Image from the result set.
	 */
	private DicomImage getDicomImagePopulated(ResultSet rs,DicomImage objQryDicomImage)throws SQLException
	{
		DicomImage objDicomImage = new DicomImage();

		if(objQryDicomImage.isImageNumber())
		{
			if(rs.getString("ImageNumber") != null)
			{
				objDicomImage.setImageNumber(rs.getString("ImageNumber"));
			}
		}
		if(objQryDicomImage.isSeriesNumber())
		{
			if(rs.getString("SeriesNumber") != null)
			{
				objDicomImage.setSeriesNumber(rs.getString("SeriesNumber"));
			}
		}
		if(objQryDicomImage.isImageComments())
		{
			if(rs.getString("ImageComments") != null)
			{
				objDicomImage.setImageComments(rs.getString("ImageComments"));
			}
		}
		if(objQryDicomImage.isSOPClassUID())
		{
			if(rs.getString("SOPClassUID") != null)
			{
				objDicomImage.setSOPClassUID(rs.getString("SOPClassUID"));
			}
		}
		if(rs.getString("SOPInstanceUID") != null)
		{
			if(objQryDicomImage.isSOPInstanceUID())
				objDicomImage.setSOPInstanceUID(rs.getString("SOPInstanceUID"));
			else
			{
				objDicomImage.setSOPInstanceUID(rs.getString("SOPInstanceUID"));
				objDicomImage.setBSOPInstanceUID(false);
			}
		}

		if(objQryDicomImage.isOverlayNumber())
		{
			if(rs.getString("OverlayNumber") != null)
			{
				objDicomImage.setOverlayNumber(rs.getString("OverlayNumber"));
			}
		}
		if(objQryDicomImage.isCurveNumber())
		{
			if(rs.getString("CurveNumber") != null)
			{
				objDicomImage.setCurveNumber(rs.getString("CurveNumber"));
			}
		}
		if(objQryDicomImage.isLUTNumber())
		{
			if(rs.getString("LUTNumber") != null)
			{
				objDicomImage.setLUTNumber(rs.getString("LUTNumber"));
			}
		}
		if(objQryDicomImage.isRelatedGeneralSOPClassUID())
		{
			if(rs.getString("RelatedGeneralSOPClassUID") != null)
			{
				objDicomImage.setRelatedGeneralSOPClassUID(DataTypeConvertor.stringToArray(rs.getString("RelatedGeneralSOPClassUID")));
			}
		}
		if(objQryDicomImage.isPatientName())
		{
			if(rs.getString("PatientName") != null)
			{
				objDicomImage.setPatientName(rs.getString("PatientName"));
			}
		}
		if(objQryDicomImage.isPatientBirthDate())
		{
			if(rs.getString("PatientBirthDate") != null)
			{
				objDicomImage.setPatientBirthDate(rs.getString("PatientBirthDate"));
			}
		}
		if(objQryDicomImage.isPatientSex())
		{
			if(rs.getString("PatientSex") != null)
			{
				objDicomImage.setPatientSex(rs.getString("PatientSex"));
			}
		}
		if(objQryDicomImage.isStudyInstanceUID())
		{
			if(rs.getString("StudyInstanceUID") != null)
			{
				objDicomImage.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			}
		}
		if(objQryDicomImage.isStudyDate())
		{
			if(rs.getString("StudyDate") != null)
			{
				objDicomImage.setStudyDate(rs.getString("StudyDate"));
			}
		}
		if(objQryDicomImage.isStudyTime())
		{
			if(rs.getString("StudyTime") != null)
			{
				objDicomImage.setStudyTime(rs.getString("StudyTime"));
			}
		}
		if(objQryDicomImage.isStudyID())
		{
			if(rs.getString("StudyID") != null)
			{
				objDicomImage.setStudyID(rs.getString("StudyID"));
			}
		}
		if(objQryDicomImage.isAccessionNumber())
		{
			if(rs.getString("AccessionNumber") != null)
			{
				objDicomImage.setAccessionNumber(rs.getString("AccessionNumber"));
			}
		}
		if(objQryDicomImage.isPatientAge())
		{
			if(rs.getString("PatientAge") != null)
			{
				objDicomImage.setPatientAge(rs.getString("PatientAge"));
			}
		}
		if(objQryDicomImage.isModality())
		{
			if(rs.getString("Modality") != null)
			{
				objDicomImage.setModality(rs.getString("Modality"));
			}
		}
		if(objQryDicomImage.isSeriesInstanceUID())
		{
			if(rs.getString("SeriesInstanceUID") != null)
			{
				objDicomImage.setSeriesInstanceUID(rs.getString("SeriesInstanceUID"));
			}
		}
		if(objQryDicomImage.isImageType())
		{
			if(rs.getString("ImageType") != null)
			{
				objDicomImage.setImageType(rs.getString("ImageType"));
			}
		}
		return objDicomImage;
	}

	/**
	 * populates series from the result set.
	 * @param rs result set.
	 * @param objQrySeries.
	 * @return Series.
	 * @throws SQLException.
	 */
	private Series getSeriesPopulated(ResultSet rs,Series objQrySeries)throws SQLException
	{
		Series objSeries = new Series();

		if(objQrySeries.isSeriesNumber())
		{
			if(rs.getString("SeriesNumber") != null)
			{
				objSeries.setSeriesNumber(rs.getString("SeriesNumber"));
			}
		}
		if(rs.getString("SeriesInstanceUID") != null)
		{
			if(objQrySeries.isSeriesInstanceUID())
				objSeries.setSeriesInstanceUID(rs.getString("SeriesInstanceUID"));
			else
			{
				objSeries.setSeriesInstanceUID(rs.getString("SeriesInstanceUID"));
				objSeries.setBSeriesInstanceUID(false);
			}
		}
		if(objQrySeries.isSeriesDate())
		{
			if(rs.getString("SeriesDate") != null)
			{
				objSeries.setSeriesDate(rs.getString("SeriesDate"));
			}
		}
		if(objQrySeries.isSeriesTime())
		{
			if(rs.getString("SeriesTime") != null)
			{
				objSeries.setSeriesTime(rs.getString("SeriesTime"));
			}
		}
		if(objQrySeries.isSeriesDescription())
		{
			if(rs.getString("SeriesDescription") != null)
			{
				objSeries.setSeriesDescription(rs.getString("SeriesDescription"));
			}
		}
		if(objQrySeries.isNumberOfSeriesRelatedInstances())
		{
			if(rs.getString("NoOfSeriesRelatedInstances") != null)
			{
				objSeries.setNumberOfSeriesRelatedInstances(rs.getString("NoOfSeriesRelatedInstances"));
			}
		}
		if(objQrySeries.isModality())
		{
			if(rs.getString("Modality") != null)
			{
				objSeries.setModality(rs.getString("Modality"));
			}
		}
		if(objQrySeries.isStudyInstanceUID())
		{
			if(rs.getString("StudyInstanceUID") != null)
			{
				objSeries.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			}
		}

		ISingleCollection<DicomImage> objDicomImageList = new SingleCollection<DicomImage>();

		DicomImage objDicomImage = getDicomImagePopulated(rs,objQrySeries.getDicomImageList().elementAt(0));
		objDicomImageList.add(objDicomImage);
		objSeries.setDicomImageList(objDicomImageList);
		return objSeries;
	}

	/**
	 * populates study from the result set.
	 * @param rs ResultSet.
	 * @param objQryStudy study object.
	 * @return instance of study.
	 * @throws SQLException.
	 */
	private Study getStudyPopulated(ResultSet rs, Study objQryStudy)throws SQLException
	{
		Study objStudy = new Study();

		if(objQryStudy.isStudyID())
		{
			if(rs.getString("StudyID") != null)
			{
				objStudy.setStudyID(rs.getString("StudyID"));
			}
		}
		if(rs.getString("StudyInstanceUID") != null)
		{
			if(objQryStudy.isStudyInstanceUID())
				objStudy.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
			else
			{
				objStudy.setStudyInstanceUID(rs.getString("StudyInstanceUID"));
				objStudy.setBStudyInstanceUID(false);
			}
		}
		if(objQryStudy.isModalitesInStudy())
		{
			if(rs.getString("Modality") != null)
			{
				objStudy.setModalitiesInStudy(DataTypeConvertor.stringToArray(rs.getString("Modality")));
			}
		}
		if(objQryStudy.isStudyDate())
		{
			if(rs.getString("StudyDate") != null)
			{
				objStudy.setStudyDate(rs.getString("StudyDate"));
			}
		}
		if(objQryStudy.isStudyTime())
		{
			if(rs.getString("StudyTime") != null)
			{
				objStudy.setStudyTime(rs.getString("StudyTime"));
			}
		}
		if(objQryStudy.isAccessionNumber())
		{
			if(rs.getString("AccessionNumber") != null)
			{
				objStudy.setAccessionNumber(rs.getString("AccessionNumber"));
			}
		}
		if(objQryStudy.isStudyDescription())
		{
			if(rs.getString("StudyDescription") != null)
			{
				objStudy.setStudyDescription(rs.getString("StudyDescription"));
			}
		}

		if(objQryStudy.isSOPClasses())
		{
			if(rs.getString("SOPClasses") != null)
			{
				objStudy.setSOPClasses(DataTypeConvertor.stringToArray(rs.getString("SOPClasses")));
			}
		}
		if(objQryStudy.isNameOfPhysicianReadingStudy())
		{
			if(rs.getString("NameOfPhysiciansReadingStudy") != null)
			{
				objStudy.setNameOfPhysicianReadingStudy(DataTypeConvertor.stringToArray(rs.getString("NameOfPhysiciansReadingStudy")));
			}
		}
		if(objQryStudy.isAdmittingDiagnosesDescription())
		{
			if(rs.getString("AdmittingDiagnosesDescription") != null)
			{
				objStudy.setAdmittingDiagnosesDescription(DataTypeConvertor.stringToArray(rs.getString("AdmittingDiagnosesDescription")));
			}
		}
		if(objQryStudy.isOtherStudyNumbers())
		{
			if(rs.getString("OtherStudyNumbers") != null)
			{
				objStudy.setOtherStudyNumbers(DataTypeConvertor.stringToArray(rs.getString("OtherStudyNumbers")));
			}
		}
		if(objQryStudy.isIssuerOfPatientID())
		{
			if(rs.getString("IssuerOfPatientId") != null)
			{
				objStudy.setIssuerOfPatientID(rs.getString("IssuerOfPatientId"));
			}
		}
		if(objQryStudy.isPatientSize())
		{
			if(rs.getString("PatientSize") != null)
			{
				objStudy.setPatientSize(rs.getString("PatientSize"));
			}
		}

		if(objQryStudy.isOccupation())
		{
			if(rs.getString("Occupation") != null)
			{
				objStudy.setOccupation(rs.getString("Occupation"));
			}
		}
		if(objQryStudy.isAdditionalPatientHistory())
		{
			if(rs.getString("AdditionalPatientHistory") != null)
			{
				objStudy.setAdditionalPatientHistory(rs.getString("AdditionalPatientHistory"));
			}
		}
		if(objQryStudy.isNumberOfStudyRelatedSeries())
		{
			if(rs.getString("NoOfStudyRelatedSeries") != null)
			{
				objStudy.setNumberOfStudyRelatedSeries(rs.getString("NoOfStudyRelatedSeries"));
			}
		}
		if(objQryStudy.isNumberOfStudyRelatedInstances())
		{
			if(rs.getString("NoOfStudyRelatedInstances") != null)
			{
				objStudy.setNumberOfStudyRelatedInstances(rs.getString("NoOfStudyRelatedInstances"));
			}
		}
		if(objQryStudy.isInterpretationAuthor())
		{
			if(rs.getString("InterpretationAuthor") != null)
			{
				objStudy.setInterpretationAuthor(rs.getString("InterpretationAuthor"));
			}
		}
		if(objQryStudy.isReferringPhysiansName())
		{
			if(rs.getString("ReferringPhysiciansName") != null)
			{
				objStudy.setReferringPhysiansName(rs.getString("ReferringPhysiciansName"));
			}
		}
		if(objQryStudy.isPatientAge())
		{
			if(rs.getString("PatientAge") != null)
			{
				objStudy.setPatientAge(rs.getString("PatientAge"));
			}
		}
		if(objQryStudy.isPatientWeight())
		{
			if(rs.getString("PatientWeight") != null)
			{
				objStudy.setPatientWeight(rs.getString("PatientWeight"));
			}
		}

		//populate the sequnce according to the Query Model
		
		if(objQryStudy.getSequenceModel().size() >0)
		{
			ISingleCollection<SQModel>  objSQModelCollectioninDB = retrieveSQ( rs.getString("StudyInstanceUID"), "StudySQInfo", "StudyInstanceUID");
			ISingleCollection<SQModel> objSQModelCollectioninRQ = objQryStudy.getSequenceModel();
			ISingleCollection<SQModel> objResultSQModel = getMatchedSequenceModel(objSQModelCollectioninRQ,objSQModelCollectioninDB);
			for(SQModel objSqModel : objResultSQModel)
			{
				objStudy.addSequenceModel(objSqModel);
			}
		}

		ISingleCollection<Series> objSeriesList = new SingleCollection<Series>();
		Series objSeries = getSeriesPopulated(rs, objQryStudy.getSeriesList().elementAt(0));
		objSeriesList.add(objSeries);
		objStudy.setSeriesList(objSeriesList);
		return objStudy;
	}

	/**
	 * This will return the SQModel which is to be added in the Query Model.
	 */
	private ISingleCollection<SQModel> getMatchedSequenceModel(ISingleCollection<SQModel> objSQModelCollectioninRQ, ISingleCollection<SQModel>  objSQModelCollectioninDB)
	{
		ISingleCollection<SQModel> objSQModelCollection = new SingleCollection<SQModel>();
		for(SQModel objSQModelRQ : objSQModelCollectioninRQ)
		{
			SQModel objSQModel = new SQModel(objSQModelRQ.getSQName());
			for(SQModel objSQModelDB : objSQModelCollectioninDB)
			{
				if(objSQModelRQ.getSQName().equalsIgnoreCase(objSQModelDB.getSQName()))
				{
					if(objSQModelRQ.getItemModelCollection().size() != 0)
					{
						ISingleCollection<ItemModel> objItemModelCollectioninRQ = objSQModelRQ.getItemModelCollection();
						ISingleCollection<ItemModel> objItemModelCollectioninDB = objSQModelDB.getItemModelCollection();
						for(ItemModel objItemModelinRQ : objItemModelCollectioninRQ)
						{
							for(ItemModel objItemModelinDB : objItemModelCollectioninDB)
							{
								if(compareItemModel(objItemModelinRQ, objItemModelinDB))
								{
									ItemModel objItemModelResultant = populateItemModel(objItemModelinRQ, objItemModelinDB);
									if(objItemModelResultant != null)
										objSQModel.addItemModel(objItemModelResultant);
								}
							}
						}
					}
					else
					{
						objSQModelCollection.add(objSQModelDB);
					}
				}
			}
			if(!objSQModel.isEmpty())
				objSQModelCollection.add(objSQModel);
		}
		return objSQModelCollection;
	}
	/**
	 * populates patient from the result set.
	 * @param rs  ResultSet.
	 * @param objQryPatient object of Patient.
	 * @return populated Patient.
	 * @throws SQLException.
	 */
	private Patient getPatientPopulated(ResultSet rs, Patient objQryPatient)throws SQLException
	{
		Patient objPatient = new Patient();


		if(rs.getString("PatientID") != null)
		{
			if(objQryPatient.isPatientID())
				objPatient.setPatientID(rs.getString("PatientID"));
			else
			{
				objPatient.setPatientID(rs.getString("PatientID"));
				objPatient.setBPatientID(false);
			}
		}
		if(objQryPatient.isPatientName())
		{
			if(rs.getString("PatientName") != null)
			{
				objPatient.setPatientName(rs.getString("PatientName"));
			}
		}
		if(objQryPatient.isPatientBirthDate())
		{
			if(rs.getString("PatientBirthDate") != null)
			{
				objPatient.setPatientBirthDate(rs.getString("PatientBirthDate"));
			}
		}
		if(objQryPatient.isPatientSex())
		{
			if(rs.getString("PatientSex") != null)
			{
				objPatient.setPatientSex(rs.getString("PatientSex"));
			}
		}
		if(objQryPatient.isPatientBirthTime())
		{
			if(rs.getString("PatientBirthTime") != null)
			{
				objPatient.setPatientBirthTime(rs.getString("PatientBirthTime"));
			}
		}
		if(objQryPatient.isOtherPatientID())
		{
			if(rs.getString("OtherPatientID") != null)
			{
				objPatient.setOtherPatientIDs(DataTypeConvertor.stringToArray(rs.getString("OtherPatientID")));
			}
		}
		if(objQryPatient.isOtherPatientNames())
		{
			if(rs.getString("OtherPatientNames") != null)
			{
				objPatient.setOtherPatientNames(DataTypeConvertor.stringToArray(rs.getString("OtherPatientNames")));
			}
		}
		if(objQryPatient.isPatientComments())
		{
			if(rs.getString("PatientComments") != null)
			{
				objPatient.setPatientComments(rs.getString("PatientComments"));
			}
		}

		if(objQryPatient.isEthnicGroup())
		{
			if(rs.getString("EthnicGroup") != null)
			{
				objPatient.setEthnicGroup(rs.getString("EthnicGroup"));
			}
		}
		if(objQryPatient.isNumberOfPatientRelatedStudies())
		{
			if(rs.getString("NoOfPatientRelatedStudies") != null)
			{
				objPatient.setNumberOfPatientRelatedStudies(rs.getString("NoOfPatientRelatedStudies"));
			}
		}
		if(objQryPatient.isNumberOfPatientRelatedSeries())
		{
			if(rs.getString("NoOfPatientRelatedSeries") != null)
			{
				objPatient.setNumberOfPatientRelatedSeries(rs.getString("NoOfPatientRelatedSeries"));
			}
		}
		if(objQryPatient.isNumberOfPatientRelatedInstances())
		{
			if(rs.getString("NoOfPatientRelatedInstances") != null)
			{
				objPatient.setNumberOfPatientRelatedInstances(rs.getString("NoOfPatientRelatedInstances"));
			}
		}

		//This will populate the Sequence related to the patient and add it to the patient Model
		if(objQryPatient.getSequenceModel().size() >0 )
		{
			ISingleCollection<SQModel>  objSQModelCollectioninDB = retrieveSQ(objPatient.getPatientID(),"PatientSQInfo","PatientID");
			ISingleCollection<SQModel> objSQModelCollectioninRQ = objQryPatient.getSequenceModel();
			ISingleCollection<SQModel> objResultSQModel = getMatchedSequenceModel(objSQModelCollectioninRQ,objSQModelCollectioninDB);

			for(SQModel objSqModel : objResultSQModel)
			{
				objPatient.addSequenceModel(objSqModel);
			}
		}

		ISingleCollection<Study> objStudyList = new SingleCollection<Study>();
		Study objStudy = getStudyPopulated(rs,objQryPatient.getStudyList().elementAt(0));
		objStudyList.add(objStudy);
		objPatient.setStudyList(objStudyList);

		//This is to know whether the key attribute is present in the model itself.
		//If the key attribute is present in the model then it should be matched.
		//If the key attribute is not matched then null will be returned.
		boolean isPatientKeyAttributeSequencePresent = false;
		boolean isKeyAttributeInPatient = false;
		for(SQModel objSqModelRQ : objQryPatient.getSequenceModel())
		{
			if(isKeyAttributeInSQPresent(objSqModelRQ))
			{
				isKeyAttributeInPatient = true;
				for(SQModel objSqModelRSP : objPatient.getSequenceModel())
				{
					if(objSqModelRQ.getSQName().equals(objSqModelRSP.getSQName()))
					{
						isPatientKeyAttributeSequencePresent = true;
						break;
					}
				}
				if(isPatientKeyAttributeSequencePresent != true)
				{
					break;
				}
			}
		}

		boolean isStudyKeyAttributeSequencePresent = false;
		boolean isKeyAttributeInStudy = false;
		for(SQModel objSqModelRQ : objQryPatient.getStudyList().elementAt(0).getSequenceModel())
		{
			if(isKeyAttributeInSQPresent(objSqModelRQ))
			{
				isKeyAttributeInStudy=true;
				for(SQModel objSqModelRSP : objStudy.getSequenceModel())
				{
					if(objSqModelRQ.getSQName().equals(objSqModelRSP.getSQName()))
					{
						isStudyKeyAttributeSequencePresent = true;
						break;
					}
				}
				if(isStudyKeyAttributeSequencePresent != true)
				{
					break;
				}
			}
		}
		if((isKeyAttributeInPatient && !isPatientKeyAttributeSequencePresent) || (isKeyAttributeInStudy && !isStudyKeyAttributeSequencePresent))
			return null;

		return objPatient;
	}
	/**
	 * To check whether the sequence model contains the key attribute.
	 * 
	 */
	private boolean isKeyAttributeInSQPresent(SQModel objSqModel)
	{
		ISingleCollection<ItemModel> objITemModelCollection = objSqModel.getItemModelCollection();
		for(ItemModel objItemModel : objITemModelCollection)
		{
			if(isKeyAttributeInItemPresent(objItemModel))
				return true;
		}
		return false;
	}

	/**
	 * To check whether the item model the key attribute.
	 * 
	 */
	private boolean isKeyAttributeInItemPresent(ItemModel objItemModel)
	{
		if(objItemModel.getValue().size() == 0)
			return false;
		else
		{
			for(Object object : objItemModel.getValue())
			{
				if(object != null)
					return true;
			}
		}
		ISingleCollection<SQModel> objSQModelCollection = objItemModel.getSQModel();
		if(objSQModelCollection.size() == 0)
			return false;
		for(SQModel objSqModel : objSQModelCollection)
		{
			if(isKeyAttributeInSQPresent(objSqModel))
				return true;
		}
		return false;
	}

	/**
	 * Compare the two item model.
	 * @param objItemModelRQ .
	 * @param objItemModelDB.
	 * @return ItemModel.
	 */
	private boolean compareItemModel(ItemModel objItemModelRQ, ItemModel objItemModelDB)
	{
		boolean isEqual = true;
		IPairedCollection< String, Object> objAttributeValueCollectionRQ = objItemModelRQ.getValue();
		IPairedCollection< String, Object> objAttributeValueCollectionDB = objItemModelDB.getValue();

		if(objAttributeValueCollectionRQ.size() == 0)
			return isEqual;
		Object[] arrObjValueRQ = objAttributeValueCollectionRQ.getKeys();
		Object[] arrObjValueDB = objAttributeValueCollectionDB.getKeys();

		for(Object objValueRQ : arrObjValueRQ)
		{
			for(Object objValueDB : arrObjValueDB)
			{
				String strValueRQ = (String)objValueRQ;
				String strValueDB = (String)objValueDB;
				if(strValueRQ.equals(strValueDB))
				{
					Object objVRQ = objAttributeValueCollectionRQ.get(strValueRQ);
					if(objVRQ != null)
					{
						Object objVDB = objAttributeValueCollectionDB.get(strValueDB);
						String strVRQ = DataTypeConvertor.objectToString(objVRQ);
						String strVDB = DataTypeConvertor.objectToString(objVDB);
						if(!strVRQ.equals(strVDB))
						{
							isEqual = false;
							break;
						}
					}
				}
			}
		}
		if(isEqual)
		{
			ISingleCollection<SQModel>  objSQModelCollectioninDB = objItemModelRQ.getSQModel();
			ISingleCollection<SQModel> objSQModelCollectioninRQ = objItemModelDB.getSQModel();
			for(SQModel objSQModelRQ : objSQModelCollectioninRQ)
			{
				for(SQModel objSQModelDB : objSQModelCollectioninDB)
				{
					if(objSQModelRQ.getSQName().equals(objSQModelDB.getSQName()))
					{
						ISingleCollection<ItemModel> objItemModelCollectioninRQ = objSQModelRQ.getItemModelCollection();
						ISingleCollection<ItemModel> objItemModelCollectioninDB = objSQModelDB.getItemModelCollection();
						for(ItemModel objItemModelinRQ : objItemModelCollectioninRQ)
						{
							for(ItemModel objItemModelinDB : objItemModelCollectioninDB)
							{
								compareItemModel(objItemModelinRQ, objItemModelinDB);
							}
						}
					}
				}
			}
		}
		return isEqual;
	}

	/**
	 * populate item model according to request item model.
	 * @param objItemModelRQ.
	 * @param objItemModelDB.
	 * @return ItemModel.
	 */
	private ItemModel populateItemModel(ItemModel objItemModelRQ, ItemModel objItemModelDB)
	{
		IPairedCollection< String, Object> objAttributeValueCollectionRQ = objItemModelRQ.getValue();
		IPairedCollection< String, Object> objAttributeValueCollectionDB = objItemModelDB.getValue();
		ItemModel objItemModel = new ItemModel();
		if(objAttributeValueCollectionRQ.size() == 0)
			return null;
		Object[] arrObjValueRQ = objAttributeValueCollectionRQ.getKeys();
		Object[] arrObjValueDB = objAttributeValueCollectionDB.getKeys();

		for(Object objValueRQ : arrObjValueRQ)
		{
			for(Object objValueDB : arrObjValueDB)
			{
				String strAttributeNameRQ = (String)objValueRQ;
				String strAttributeNameDB = (String)objValueDB;
				if(strAttributeNameRQ.equals(strAttributeNameDB))
				{
					objItemModel.addValue(strAttributeNameRQ, objAttributeValueCollectionDB.get(strAttributeNameDB));
				}
			}
		}
		ISingleCollection<SQModel>  objSQModelCollectioninDB = objItemModelRQ.getSQModel();
		ISingleCollection<SQModel> objSQModelCollectioninRQ = objItemModelDB.getSQModel();
		for(SQModel objSQModelRQ : objSQModelCollectioninRQ)
		{
			SQModel objInnerSQModel = new SQModel(objSQModelRQ.getSQName());
			for(SQModel objSQModelDB : objSQModelCollectioninDB)
			{
				if(objSQModelRQ.getSQName().equals(objSQModelDB.getSQName()))
				{
					ISingleCollection<ItemModel> objItemModelCollectioninRQ = objSQModelRQ.getItemModelCollection();
					ISingleCollection<ItemModel> objItemModelCollectioninDB = objSQModelDB.getItemModelCollection();
					for(ItemModel objItemModelinRQ : objItemModelCollectioninRQ)
					{
						for(ItemModel objItemModelinDB : objItemModelCollectioninDB)
						{
							ItemModel objInnerItemModel = populateItemModel(objItemModelinRQ, objItemModelinDB);
							objInnerSQModel.addItemModel(objInnerItemModel);
						}
					}
				}
			}
			objItemModel.addSQModel(objInnerSQModel);

		}
		return objItemModel;
	}

	/**
	 * Retrieve the Sequence Model from the data base.
	 * @param strUID.
	 * @param strTableName.
	 * @param strUIDName.
	 * @return ISingleCollection of SQModel.
	 * @throws SQLException.
	 */
	private ISingleCollection<SQModel> retrieveSQ(String strUID, String strTableName, String strUIDName) throws SQLException
	{
		ISingleCollection<SQModel>  objSQModelCollection  = new SingleCollection<SQModel>();
		Statement objStmt = objcon.createStatement();
		String strQuery = "Select * from " +strTableName+ " where " + strUIDName+ " ='" + strUID+ "'";
		ResultSet rs = objStmt.executeQuery(strQuery);
		while(rs.next())
		{
			String strSQID = rs.getString("SequenceID");
			objStmt = objcon.createStatement();
			ResultSet rsForSQInfo = objStmt.executeQuery("Select * from SQInfo where SequenceID ='"+ strSQID + "'");
			if(rsForSQInfo.next())
			{
				SQModel objSQModel = new SQModel(rsForSQInfo.getString("SequenceName"));
				populateSQModel(objSQModel,strSQID);
				objSQModelCollection.add(objSQModel);
			}
		}
		return objSQModelCollection;
	}

	/**
	 * Retrieve the Sequence Model from the data base.
	 * @param objSQModel.
	 * @param strSequenceID.
	 * @throws SQLException.
	 */
	private void populateSQModel(SQModel objSQModel, String strSequenceID) throws SQLException
	{
		ISingleCollection<RowDescriptor> objRowCollection = populateRows(strSequenceID);
		if(objRowCollection.size() == 0)
			return ;
		generateSQModel(objSQModel,objRowCollection);
	}

	/**
	 * Populates the collection of RowDescriptor for a given SOP Instance UID.
	 */
	private ISingleCollection<RowDescriptor> populateRows(String strSequenceID)throws SQLException
	{
		ISingleCollection<RowDescriptor> objRowCollection = new SingleCollection<RowDescriptor>();
		String query = "Select * from SQDEINFO where SequenceID = '"+ strSequenceID + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		RowDescriptor objRowDescriptor = null;
		while(rs.next())
		{
			objRowDescriptor = new RowDescriptor();
			objRowDescriptor.setDERowID(rs.getString("DERowID"));
			objRowDescriptor.setVRType(rs.getInt("VRType"));
			objRowDescriptor.setGroupNumber(rs.getInt("GroupNumber"));
			objRowDescriptor.setElementNumber(rs.getInt("ElementNumber"));
			String isSQ = rs.getString("IsSQ");
			if(isSQ.equalsIgnoreCase("Y"))
				objRowDescriptor.setSQ(true);
			else
				objRowDescriptor.setSQ(false);
			objRowDescriptor.setParentSQUID(rs.getString("ParentSQUID"));
			objRowDescriptor.setValue(rs.getString("Value"));
			objRowDescriptor.setSQUID(rs.getString("SQUUID"));
			objRowDescriptor.setItemUID(rs.getString("ItemUUID"));
			objRowCollection.add(objRowDescriptor);
		}
		return objRowCollection;
	}

	/**
	 * Retrieve the Sequence Model from the data base.
	 * @param objSQModel.
	 * @param objRowsCollection.
	 * @throws SQLException.
	 */
	private void generateSQModel(SQModel objSQModel,ISingleCollection<RowDescriptor> objRowsCollection) throws SQLException
	{
		ICollectionIterator<RowDescriptor> objRowsIterator = objRowsCollection.iterator();
		while(objRowsIterator.hasNext())
		{
			RowDescriptor objRow = objRowsIterator.next();
			if(objRow.isSQ() && objRow.getParentSQUID() == null)
			{
				objRowsCollection.remove(objRow);
				//Populates the all Items of this sequence 
				createSequence(objSQModel,objRow.getSQUID(),objRowsCollection);
				//Adding the SQ Item in Dataset
				objRowsIterator = objRowsCollection.iterator();
			}
		}
	}

	/**
	 * Retrieve the Sequence Model from the data base.
	 * @param objSQModel.
	 * @param strSQUID.
	 * @param objRowsList.
	 * @throws SQLException.
	 */
	private void createSequence(SQModel objSQModel,String strSQUID,ISingleCollection<RowDescriptor> objRowsList) throws SQLException
	{
		ICollectionIterator<RowDescriptor> objRowsIterator = objRowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			RowDescriptor objRow = objRowsIterator.next();
			if(!objRow.isSQ() && objRow.getSQUID() != null && objRow.getSQUID().equals(strSQUID))
			{
				ItemModel objItemModel = new ItemModel();
				createSQItems(objItemModel, objRow.getItemUID(),objRowsList);
				objRowsIterator = objRowsList.iterator();
				objSQModel.addItemModel(objItemModel);
			}
		}
	}

	/**
	 * Retrieve the Sequence Model from the data base.
	 * @param objItemModel.
	 * @param strItemUid.
	 * @param objRowsList.
	 * @throws SQLException.
	 */
	private void createSQItems(ItemModel objItemModel , String strItemUid,ISingleCollection<RowDescriptor> objRowsList) throws SQLException
	{
		int iVRType,iGroupNumber,iElementNumber;
		IDictionaryReader objIDictionaryReader = DictionaryReader.createInstance();
		ICollectionIterator<RowDescriptor> objRowsIterator = objRowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			RowDescriptor objRow = objRowsIterator.next();
			iGroupNumber = objRow.getGroupNumber();
			iElementNumber = objRow.getElementNumber();
			iVRType = objRow.getVRType();
			String strAttributeName = objIDictionaryReader.getName(iGroupNumber, iElementNumber);
			if(objRow.getItemUID() != null && objRow.getItemUID().equals(strItemUid))
			{
				if(!objRow.isSQ())
				{
					Object objValue = DataTypeConvertor.stringToObject(objRow.getValue(),iVRType);
					objItemModel.addValue(strAttributeName, objValue);
					objRowsList.remove(objRow);
					objRowsIterator = objRowsList.iterator();
				}
				else if(objRow.isSQ())
				{
					String query = "Select * from SQDEINFO where SequenceID = '"+ objRow.getSQUID() + "'";
					PreparedStatement ps = objcon.prepareStatement(query);
					ResultSet rs = ps.executeQuery();

					if(rs.next())
					{
						String strSequnceName = rs.getString("SequenceName");
						SQModel objSqModel = new SQModel(strSequnceName);
						objRowsList.remove(objRow);
						//Populates the all Items of this sequence 
						createSequence(objSqModel,objRow.getSQUID(),objRowsList);
						objRowsIterator = objRowsList.iterator();
						//Adding the SQ DicomValue in Item
						objItemModel.addSQModel(objSqModel);
					}
				}
			}
		}
	}


	/**
	 * Generates string form for where clause.
	 */
	private String getWhereClauseString(String str1, String str2)
	{
		String str = str2.trim();
		if(str != null)
		{
			if(str.contains("*"))
				str = str.replace('*','%');
			if(str.contains("?"))
				str = str.replace('?','_');
		}
		String strWhereClause = str1 + " LIKE " + "'" +str +"'";
		return strWhereClause;
	}
}
