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

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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.dicomparserserializers.DicomParser;
import cdac.medinfo.sdk.dcm30_04.dicomstreams.DicomFileStream;
import cdac.medinfo.sdk.dcm30_04.enums.EnumSOPClassUIDs;
import cdac.medinfo.sdk.dcm30_04.services.dicomnetcommons.DataTypeConvertor;
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;

/*
 * Print_DBHelper interacts with database and performs queries fired.
 * It performs the queries fired for creation,updation and retrieval of SOP Instance UID
 */
class Print_DBHelper 
{
	private Connection objcon = null;

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

	/*
	 * Creates the SOPInstance.
	 * It inserts the information of all DataElements of provided Dataset  
	 * @param objIDataSet instance of Dataset
	 * @param strSOPInstanceUID value of SOPInstance UID
	 * @return boolean status as true or false
	 * @throws SQLException if SQL Operation fails
	 */
	boolean createSOPInstance(EnumSOPClassUIDs enumSOPClassUIDs, IDataSet objIDataSet,String strSOPInstanceUID)throws SQLException,IOException
	{
		/*
		 * This call removes the rows with same SOP Instance UID to achieve the overridden scenario.
		 */
		removeDuplicateSOPInstance(enumSOPClassUIDs,strSOPInstanceUID);


		for(IDataElement objDataElement : objIDataSet)
		{
			String strValue = null;
			IDicomValue objIDicomValue = objDataElement.getDicomValue();

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

			if(!(objIDicomValue instanceof SQ))
			{
				if(objIDicomValue.getValue() != null)
				{
					int iGroupNo = objDataElement.getTag().getGroupNumber();
					int iElementNo = objDataElement.getTag().getElementNumber();
					if(iGroupNo == 0x7FE0 && iElementNo == 0x0010)
					{
						strValue = strPath;
					}
					else
					{
						strValue = DataTypeConvertor.dicomDatatoString(objIDicomValue);
					}
				}
				insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objIDicomValue.getIVRType(), objDataElement.getTag().getGroupNumber(),objDataElement.getTag().getElementNumber(), false, null, strValue, null,null);
			}
			else
			{
				String strSQId = StandardMethods.generateUUID();
				insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objIDicomValue.getIVRType(), objDataElement.getTag().getGroupNumber(),objDataElement.getTag().getElementNumber(), true, null, null, strSQId,null);
				insertSQInfo(enumSOPClassUIDs,objIDicomValue,strSOPInstanceUID,strSQId,null);
			}
		}
		return true;
	}

	/*
	 * Checks whether SOP Instance already exists or not 
	 */
	private void removeDuplicateSOPInstance(EnumSOPClassUIDs enumSOPClassUIDs, String strSOPInstanceUID)throws SQLException
	{
		String query  = null;
		String strTableName = getTablename(enumSOPClassUIDs);
		query = "delete from "+strTableName+" where SOPInstanceUID = '"+ strSOPInstanceUID + "'";

		PreparedStatement ps = objcon.prepareStatement(query);
		ps = objcon.prepareStatement(query);
		ps.executeUpdate();
	}

	/*
	 * Inserts DataElement of a sequence and all its Items.
	 */
	private boolean insertSQInfo(EnumSOPClassUIDs enumSOPClassUIDs,IDicomValue objIDicomValue,String strSOPInstanceUID,String strSQUUid,String strParentSQID)throws SQLException, IOException
	{
		SQ objSQDicomValue = (SQ)objIDicomValue;
		ICollectionIterator<IItem> objSQItemIterator = objSQDicomValue.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();
			ICollectionIterator<IDataElement> objDEIterator = objItem.iterator();
			while(objDEIterator.hasNext())
			{
				IDataElement objDataElement = objDEIterator.next();
				IDicomValue objTempDicomValue = objDataElement.getDicomValue();
				if(objDataElement.getTag().getGroupNumber() == 0x7FE0 && objDataElement.getTag().getElementNumber() == 0x0010)
				{
					File objFile = new File(DicomConstants.DICOM_CSTORE_STORAGE_PATH + "\\Print");
					objFile.mkdirs();

					strValue = DicomConstants.DICOM_CSTORE_STORAGE_PATH + "\\Print" +"\\"+strSOPInstanceUID;
					FileOutputStream objFileInputStream = new FileOutputStream(strValue);
					DicomFileStream objDicomFileStream = new DicomFileStream();
					objDicomFileStream.setEndianNess(objItem.getTransferSyntax().getEndianNess());
					objDicomFileStream.setOutputStream(objFileInputStream);
					objDataElement.serialize(objDicomFileStream);

					insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objTempDicomValue.getIVRType(), objDataElement.getTag().getGroupNumber(),objDataElement.getTag().getElementNumber(), false, strParentSQID, strValue, strSQUUid,strItemUUID);
				}
				else if(!(objTempDicomValue instanceof SQ))
				{
					if(objTempDicomValue.getValue() != null)
						strValue = DataTypeConvertor.dicomDatatoString(objTempDicomValue);
					insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objTempDicomValue.getIVRType(), objDataElement.getTag().getGroupNumber(),objDataElement.getTag().getElementNumber(), false, strParentSQID, strValue, strSQUUid,strItemUUID);
				}
				else
				{
					String strSQId = StandardMethods.generateUUID();
					insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objTempDicomValue.getIVRType(), objDataElement.getTag().getGroupNumber(),objDataElement.getTag().getElementNumber(), true, strSQUUid, null, strSQId,strItemUUID);
					insertSQInfo(enumSOPClassUIDs,objTempDicomValue, strSOPInstanceUID,strSQId,strSQUUid);
				}
			}
		}
		return true;
	}

	/*
	 * Inserts the information of DataElement in Database
	 */
	private boolean insertInstanceDEInfo(EnumSOPClassUIDs enumSOPClassUIDs, String strSOPInstanceUID,int iVRType,int iGroupNumber,int iElementNumber,boolean isSQ,String strParentSQID,String strValue,String strSQID,String strItemID)throws SQLException
	{
		String strQuery  = null;
		String strTableName = getTablename(enumSOPClassUIDs);

		strQuery = "insert into "+strTableName+" (DERowID,SOPInstanceUID,VRType,GroupNumber,ElementNumber,IsSQ,ParentSQUID,Value,SQUUID,ItemUUID) values (?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = objcon.prepareStatement(strQuery);
		ps.setString(1,StandardMethods.generateUUID());
		ps.setString(2,strSOPInstanceUID);
		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;
	}

	boolean insertIntoRelationShipTable(String strBasicFilmSessionSOPInstanceUID, String strBasicFilmBoxSOPInstanceUID, String strImageBoxSOPInstanceUID, String strBasicAnnotationSOPInstanceUID, String strPresentationLUTSOPInstanceUID, String strImageOverlaySOPInstanceUID)throws SQLException
	{
		String strQuery = "insert into MAPPING_TABLE (BasicFilmSessionSOPInstanceUID,BasicFilmBoxSOPInstanceUID,ImageBoxSOPInstanceUID,BasicAnnotationSOPInstanceUID,PresentationLUTSOPInstanceUID,ImageOverlaySOPInstanceUID) values (?,?,?,?,?,?)";
		PreparedStatement ps = objcon.prepareStatement(strQuery);
		ps.setString(1,strBasicFilmSessionSOPInstanceUID);
		ps.setString(2,strBasicFilmBoxSOPInstanceUID);
		ps.setString(3,strImageBoxSOPInstanceUID);
		ps.setString(4,strBasicAnnotationSOPInstanceUID);
		ps.setString(5,strPresentationLUTSOPInstanceUID);
		ps.setString(6,strImageOverlaySOPInstanceUID);
		ps.executeUpdate();

		return true;
	}

	/*
	 * Updates the SOPInstance information.
	 * It updates the information of all DataElements of provided Dataset  
	 * @param objIDataSet instance of Dataset
	 * @param strSOPInstanceUID value of SOPInstance UID
	 * @return boolean status as true or false
	 * @throws SQLException if SQL Operation fails
	 */
	boolean updateInstanceInfo(EnumSOPClassUIDs enumSOPClassUIDs,IDataSet objIDataSet,String strSOPInstanceUID)throws SQLException, IOException
	{
		ISingleCollection<Print_RowDescriptor> objRowCollection = populateRows(enumSOPClassUIDs,strSOPInstanceUID);
		ICollectionIterator<IDataElement> objDEIterator = objIDataSet.iterator();
		String strValue = null;
		int iGroupNo,iElementNo;
		IDataElement objDataElement = null;
		IDicomValue objIDicomValue = null;
		while(objDEIterator.hasNext())
		{
			objDataElement = objDEIterator.next();
			objIDicomValue = objDataElement.getDicomValue();
			iGroupNo = objDataElement.getTag().getGroupNumber();
			iElementNo = objDataElement.getTag().getElementNumber();
			String strElementID = null;
			if(!(objIDicomValue instanceof SQ))
			{
				if(objIDicomValue.getValue() != null)
					strValue = DataTypeConvertor.dicomDatatoString(objIDicomValue);
				strElementID = getElementID(iGroupNo,iElementNo,objRowCollection);
				if(strElementID != null)
					updateRow(enumSOPClassUIDs,strValue,strElementID);
			}
			else
			{
				String strDelSQUID = getSQUID(iGroupNo,iElementNo,objRowCollection);
				deleteSequence(enumSOPClassUIDs,strDelSQUID);
				String strSQId = StandardMethods.generateUUID();
				insertInstanceDEInfo(enumSOPClassUIDs,strSOPInstanceUID,objIDicomValue.getIVRType(), iGroupNo,iElementNo, true, null, null, strSQId,null);
				insertSQInfo(enumSOPClassUIDs,objIDicomValue,strSOPInstanceUID,strSQId,null);
			}
		}
		return true;
	}

	/*
	 * Retrives the SQUID for given Tag
	 */
	private String getSQUID(int iGroupNumber,int iElementNumber,ISingleCollection<Print_RowDescriptor> objRowCollection)
	{
		ICollectionIterator<Print_RowDescriptor> objRowIterator = objRowCollection.iterator();
		Print_RowDescriptor objNServiceRow = null;
		while(objRowIterator.hasNext())
		{
			objNServiceRow = objRowIterator.next();
			if(objNServiceRow.getGroupNumber() == iGroupNumber && objNServiceRow.getElementNumber() == iElementNumber && objNServiceRow.getParentSQUID() == null)
			{
				return objNServiceRow.getSQUID();
			}
		}
		return null;
	}

	/*
	 * Delete information of a Sequence form Database for given SQ UID.
	 */
	private void deleteSequence(EnumSOPClassUIDs enumSOPClassUIDs,String strSQUID)throws SQLException
	{
		String query = null;

		String strTableName = getTablename(enumSOPClassUIDs);
		query = "Select * from "+strTableName+" where SQUUID = '"+ strSQUID + "'";

		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		String isSQ = null;
		//String strItemID = null;
		while(rs.next())
		{
			isSQ = rs.getString("IsSQ");
			if(isSQ.equalsIgnoreCase("Y"))
			{
				String strQry = "delete from "+strTableName+" where DERowID = '" + rs.getString("DERowID") + "'";
				PreparedStatement objPreparedStatement = objcon.prepareStatement(strQry);
				objPreparedStatement.executeUpdate();
			}
			else
			{
				deleteItemRows(enumSOPClassUIDs,rs.getString("ItemUUID"));
			}
		}
		String strQuery = "delete from "+strTableName+" where SQUUID = '" + strSQUID +"'";

		ps = objcon.prepareStatement(strQuery);
		ps.executeUpdate();
	}

	/*
	 * Delete information of a Item form Database for given Item UID.
	 */
	private void deleteItemRows(EnumSOPClassUIDs enumSOPClassUIDs,String strItemUID)throws SQLException
	{
		String strTableName = getTablename(enumSOPClassUIDs);

		String query = "Select * from "+strTableName+" where ItemUUID = '"+ strItemUID + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		String isSQ = null;
		while(rs.next())
		{
			isSQ = rs.getString("IsSQ");
			if(isSQ.equalsIgnoreCase("Y"))
				deleteSequence(enumSOPClassUIDs,rs.getString("SQUUID"));
			else
			{
				String strQry = "delete from "+strTableName+" where DERowID = '" + rs.getString("DERowID") + "'";
				PreparedStatement objPreparedStatement = objcon.prepareStatement(strQry);
				objPreparedStatement.executeUpdate();
			}
		}
	}

	/*
	 * Populates the collection of RowDescriptor for a given SOP Instance UID
	 */
	private ISingleCollection<Print_RowDescriptor> populateRows(EnumSOPClassUIDs enumSOPClassUIDs,String strSOPInstanceUID)throws SQLException
	{
		ISingleCollection<Print_RowDescriptor> objRowCollection = new SingleCollection<Print_RowDescriptor>();
		String query = null;
		String strTableName = getTablename(enumSOPClassUIDs);

		query = "Select * from "+strTableName+" where SOPInstanceUID = '"+ strSOPInstanceUID + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		Print_RowDescriptor objRowDescriptor = null;
		while(rs.next())
		{
			objRowDescriptor = new Print_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);
		}
		rs.close();
		ps.close();
		return objRowCollection;
	}

	/*
	 * Updates the value for a given unique DataElement ID
	 */
	private boolean updateRow(EnumSOPClassUIDs enumSOPClassUIDs, String strValue,String strDERowID)throws SQLException
	{
		String query = null;
		String strTableName = getTablename(enumSOPClassUIDs);
		query = "update "+strTableName+" SET Value=?  where DERowID = '" + strDERowID + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ps.setString(1,strValue);
		ps.executeUpdate();

		return true;
	}

	/*
	 * This function gets the Table name corresponding to the SOP class UID provided.
	 */
	private String getTablename(EnumSOPClassUIDs enumSOPClassUIDs)
	{
		String strTableName = null;
		switch(enumSOPClassUIDs)
		{
		case BASICFILMSESSIONIOD:
			strTableName = "BASIC_FILM_SESSION";
			break;
		case BASICFILMBOXIOD:
			strTableName = "BASIC_FILM_BOX";
			break;
		case BASICCOLORIMAGEBOX:
			strTableName = "IMAGE_BOX";
			break;
		case BASICGRAYSCALEIMAGEBOX:
			strTableName = "IMAGE_BOX";
			break;
		case PULLPRINTREQUESTIOD:
			strTableName = "PULL_PRINT_CREATE";
			break;
		case BASICPRINTIMAGEOVERLAYBOXIOD :
			strTableName = "BASIC_PRINT_IMAGE_OVERLAY_BOX";
			break;
		case PRESENTATIONLUTIOD:
			strTableName = "PRESENTATION_LUT";
			break;
		case BASICANNOTATIONBOX:
			strTableName = "BASIC_ANNOTATION_BOX";
			break;
		case PRINTQUEUEMANAGEMENTIOD:
			strTableName = "PRINT_QUEUE_MANAGEMENT";
			break;
		case STOREDPRINTSTORAGE:
			strTableName = "STORED_PRINT_STORAGE";
			break;
		}
		return strTableName;
	}

	/*
	 * This function gets the column name corresponding to the SOP Class UID provided.
	 */
	private String getCoumnNameOfMappingTable(EnumSOPClassUIDs enumSOPClassUIDs)
	{
		String strTableName = null;
		switch(enumSOPClassUIDs)
		{
		case BASICFILMSESSIONIOD:
			strTableName = "BasicFilmSessionSOPInstanceUID";
			break;
		case BASICFILMBOXIOD:
			strTableName = "BasicFilmBoxSOPInstanceUID";
			break;
		case BASICCOLORIMAGEBOX:
			strTableName = "ImageBoxSOPInstanceUID";
			break;
		case BASICGRAYSCALEIMAGEBOX:
			strTableName = "ImageBoxSOPInstanceUID";
			break;
		case BASICPRINTIMAGEOVERLAYBOXIOD :
			strTableName = "ImageOverlaySOPInstanceUID";
			break;
		case PRESENTATIONLUTIOD:
			strTableName = "PresentationLUTSOPInstanceUID";
			break;
		case BASICANNOTATIONBOX:
			strTableName = "BasicAnnotationSOPInstanceUID";
			break;
		}
		return strTableName;
	}

	/*
	 * Retrieves the unique DataElement Row ID for given Tag.
	 */
	private String getElementID(int iGroupNumber,int iElementNumber,ISingleCollection<Print_RowDescriptor> objRowCollection)
	{
		ICollectionIterator<Print_RowDescriptor> objRowIterator = objRowCollection.iterator();
		Print_RowDescriptor objPrintRow = null;
		while(objRowIterator.hasNext())
		{
			objPrintRow = objRowIterator.next();
			if(!objPrintRow.isSQ() && objPrintRow.getItemUID() == null && objPrintRow.getGroupNumber() == iGroupNumber && objPrintRow.getElementNumber() == iElementNumber)
			{
				objRowCollection.remove(objPrintRow);
				return objPrintRow.getDERowID();
			}
		}
		return null;
	}

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

		return false;
	}

	/*
	 * This function updates the relationship table with the values provided.
	 */
	boolean updateRelationShipTable(EnumSOPClassUIDs enumSOPClassUIDs,EnumSOPClassUIDs enumSOPClassUIDOFColumName,String strSOPInstance,String strUpdateSOPInstanceUID) throws SQLException
	{
		String query = null;
		String strTableName = "MAPPING_TABLE";
		String strUpdateParameterColumName = getCoumnNameOfMappingTable(enumSOPClassUIDOFColumName);
		String strSearchParameterColumName = getCoumnNameOfMappingTable(enumSOPClassUIDs);
		query = "update "+strTableName+" SET "+strUpdateParameterColumName+"=?  where "+strSearchParameterColumName+" = '" + strSOPInstance + "'";
		PreparedStatement ps = objcon.prepareStatement(query);
		ps.setString(1,strUpdateSOPInstanceUID);
		ps.executeUpdate();
		return true;
	}

	/*
	 * This function deltes the SOP instance of enumSOPClassUID.
	 */
	boolean deleteSOPInstance(EnumSOPClassUIDs enumSOPClassUIDs,String strSOPInstance) throws SQLException
	{
		String query = null;
		PreparedStatement objPreparedStatement = null;
		ResultSet rs = null;
		if(enumSOPClassUIDs.equals(EnumSOPClassUIDs.BASICPRINTIMAGEOVERLAYBOXIOD) || enumSOPClassUIDs.equals(EnumSOPClassUIDs.PRESENTATIONLUTIOD))
		{
			query = "Select * from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			rs = objPreparedStatement.executeQuery();
			if(rs.next())
				return false;
			query = "delete from "+getTablename(enumSOPClassUIDs)+" where SOPInstanceUID = '" + strSOPInstance + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
			return true;
		}
		else if(enumSOPClassUIDs.equals(EnumSOPClassUIDs.PULLPRINTREQUESTIOD))
		{
			query = "delete from "+getTablename(enumSOPClassUIDs)+" where SOPInstanceUID = '" + strSOPInstance + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
			return true;
		}
		else if(enumSOPClassUIDs.equals(EnumSOPClassUIDs.BASICFILMSESSIONIOD))
		{
			deleteBasicImageBox(enumSOPClassUIDs,strSOPInstance);
			query = "Select Distinct BasicFilmSessionSOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			rs = objPreparedStatement.executeQuery();
			if(rs.next())
			{
				String strSOPInstanceUIDBasicFilmSession = rs.getString(1);
				query = "delete from "+getTablename(EnumSOPClassUIDs.BASICFILMSESSIONIOD)+" where SOPInstanceUID = '" + strSOPInstanceUIDBasicFilmSession + "'";
				objPreparedStatement = objcon.prepareStatement(query);
				objPreparedStatement.executeUpdate();
			}
			else
			{
				if(isSOPInstanceExist(EnumSOPClassUIDs.BASICFILMSESSIONIOD, strSOPInstance))
				{
					query = "delete from " + getTablename(EnumSOPClassUIDs.BASICFILMSESSIONIOD) + " where SOPInstanceUID = '" + strSOPInstance + "'";
					objPreparedStatement = objcon.prepareStatement(query);
					objPreparedStatement.executeUpdate();
				}
			}
		}
		else if(enumSOPClassUIDs.equals(EnumSOPClassUIDs.BASICFILMBOXIOD))
		{
			deleteBasicImageBox(enumSOPClassUIDs,strSOPInstance);
		}
		return true;
	}

	/*
	 * This function deletes the Basic Finm Box SOP instance with all other SOP instance in its hierarcy.
	 */
	private void deleteBasicImageBox( EnumSOPClassUIDs enumSOPClassUIDs, String strSOPInstance) throws SQLException
	{
		String query = "Select Distinct ImageOverlaySOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
		PreparedStatement objPreparedStatement = objcon.prepareStatement(query);
		ResultSet rs = objPreparedStatement.executeQuery();
		if(rs.next())
		{
			String strSOPInstanceUIDImageOverlay = rs.getString(1);
			query = "delete from "+getTablename(EnumSOPClassUIDs.BASICPRINTIMAGEOVERLAYBOXIOD)+" where SOPInstanceUID = '" + strSOPInstanceUIDImageOverlay + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
		}
		query = "Select Distinct PresentationLUTSOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
		objPreparedStatement = objcon.prepareStatement(query);
		rs = objPreparedStatement.executeQuery();
		if(rs.next())
		{
			String strSOPInstanceUIDPresentationLUT = rs.getString(1);
			query = "delete from "+getTablename(EnumSOPClassUIDs.PRESENTATIONLUTIOD)+" where SOPInstanceUID = '" + strSOPInstanceUIDPresentationLUT + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
		}
		query = "Select Distinct BasicAnnotationSOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
		objPreparedStatement = objcon.prepareStatement(query);
		rs = objPreparedStatement.executeQuery();
		if(rs.next())
		{
			String strBasicAnnotationSOPInstanceUID = rs.getString(1);
			query = "delete from "+getTablename(EnumSOPClassUIDs.BASICANNOTATIONBOX)+" where SOPInstanceUID = '" + strBasicAnnotationSOPInstanceUID + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
		}
		query = "Select Distinct ImageBoxSOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
		objPreparedStatement = objcon.prepareStatement(query);
		rs = objPreparedStatement.executeQuery();
		if(rs.next())
		{
			String strImageBoxSOPInstanceUID = rs.getString(1);
			query = "delete from "+getTablename(EnumSOPClassUIDs.BASICGRAYSCALEIMAGEBOX)+" where SOPInstanceUID = '" + strImageBoxSOPInstanceUID + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
		}
		query = "Select Distinct BasicFilmBoxSOPInstanceUID from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strSOPInstance + "'";
		objPreparedStatement = objcon.prepareStatement(query);
		rs = objPreparedStatement.executeQuery();
		if(rs.next())
		{
			String strBasicFilmBoxSOPInstanceUID = rs.getString(1);
			query = "delete from "+getTablename(EnumSOPClassUIDs.BASICFILMBOXIOD)+" where SOPInstanceUID = '" + strBasicFilmBoxSOPInstanceUID + "'";
			objPreparedStatement = objcon.prepareStatement(query);
			objPreparedStatement.executeUpdate();
		}
	}

	/*
	 * This function retrieves the SOP instance information of enumSOPClassUID.
	 */
	IDataSet retrieveSOPInstanceInfo(EnumSOPClassUIDs enumSOPClassUIDs,ISingleCollection<ITag> objITagCollection,String strSOPInstanceUID) throws SQLException, IOException
	{
		ISingleCollection<Print_RowDescriptor> objRowCollection = populateRows(enumSOPClassUIDs,strSOPInstanceUID);
		int iGroupNumber,iElementNumber;
		if(enumSOPClassUIDs.equals(EnumSOPClassUIDs.STOREDPRINTSTORAGE))
		{
			ICollectionIterator<Print_RowDescriptor> objRowsIterator = objRowCollection.iterator();
			while(objRowsIterator.hasNext())
			{
				Print_RowDescriptor objRow = objRowsIterator.next();
				iGroupNumber = objRow.getGroupNumber();
				iElementNumber = objRow.getElementNumber();
				if(iGroupNumber == 0x7FE0 && iElementNumber == 0x0010)
				{
					String strValue = objRow.getValue();
					DicomParser objDicomParser = new DicomParser();
					IDataSet objIDataSet = objDicomParser.parseAsPart10File(strValue, true);
					return objIDataSet;
				}
			}
		}

		IDataSet objResDataset = new DataSet();
		if(objRowCollection.size() == 0)
			return null;
		generateDataSet(objResDataset,objRowCollection,objITagCollection);
		return objResDataset;

	}

	/*
	 * Populates the Dataset from given collection of Dataelements Rows according to the queried Dataset.
	 */
	private void generateDataSet(IDataSet objIDataSet,ISingleCollection<Print_RowDescriptor> objRowsCollection,ISingleCollection<ITag> objITagCollection) throws IOException
	{

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

		ICollectionIterator<Print_RowDescriptor> objRowsIterator = objRowsCollection.iterator();
		while(objRowsIterator.hasNext())
		{
			Print_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.getSQUID(),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 Dataset with a Sequence and all its Items information 
	 */
	private void createSequence(IDataTypeSQ objSQ,String strSQUID,ISingleCollection<Print_RowDescriptor> objPrint_RowsList) throws IOException
	{
		ICollectionIterator<Print_RowDescriptor> objRowsIterator = objPrint_RowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			Print_RowDescriptor objRow = objRowsIterator.next();
			if(!objRow.isSQ() && objRow.getSQUID() != null && objRow.getSQUID().equals(strSQUID))
			{
				IItem objItem = objSQ.getNewItem();
				createSQItems(objItem, objRow.getItemUID(),objPrint_RowsList);
				objRowsIterator = objPrint_RowsList.iterator();
				objSQ.addItem(objItem);
			}
		}
	}

	/*
	 * Populates the Item with information of all its DataElements 
	 */
	private void createSQItems(IItem objItem , String strItemUid,ISingleCollection<Print_RowDescriptor> objPrint_RowsList) throws IOException
	{
		int iVRType,iGroupNumber,iElementNumber;
		IDictionaryReader objIDictionaryReader = objItem.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objItem.getTransferSyntax();
		IDataElement objIDataElement = null;
		ICollectionIterator<Print_RowDescriptor> objRowsIterator = objPrint_RowsList.iterator();
		while(objRowsIterator.hasNext())
		{
			Print_RowDescriptor objRow = objRowsIterator.next();
			iGroupNumber = objRow.getGroupNumber();
			iElementNumber = objRow.getElementNumber();
			iVRType = objRow.getVRType();
			if(objRow.getItemUID() != null && objRow.getItemUID().equals(strItemUid))
			{
				if(!objRow.isSQ())
				{
					if(iGroupNumber == 0x7FE0 && iElementNumber == 0x0010)
					{
						objIDataElement = new ImplicitDE(objITransferSyntax,objIDictionaryReader);
						String strPath = objRow.getValue();
						DicomFileStream objDicomStream = new DicomFileStream();
						FileInputStream objFileInputStream = new FileInputStream(strPath);
						objDicomStream.setInputStream(objFileInputStream);
						objDicomStream.setEndianNess(objItem.getTransferSyntax().getEndianNess());
						objIDataElement.parse(objDicomStream);
						objItem.addDataElement(objIDataElement);
					}
					else
					{
						objIDataElement = DataTypeConvertor.stringtoDataElement(objRow.getValue(),iGroupNumber,iElementNumber,iVRType,objITransferSyntax,objIDictionaryReader);
						objItem.addDataElement(objIDataElement);
					}

					objPrint_RowsList.remove(objRow);
					objRowsIterator = objPrint_RowsList.iterator();
				}
				else if(objRow.isSQ())
				{
					IDataTypeSQ objSQ = DicomV3Factory.createInstance().createSequence(iGroupNumber, iElementNumber, objItem.getTransferSyntax());
					objPrint_RowsList.remove(objRow);
					//Populates the all Items of this sequence 
					createSequence(objSQ,objRow.getSQUID(),objPrint_RowsList);
					objRowsIterator = objPrint_RowsList.iterator();
					//Adding the SQ DicomValue in Item
					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);
				}
			}
		}
	}

	/*
	 * Populates the first level Dataelements in Dataset
	 */
	private void populateFirstLevelDE(IDataSet objRstDataSet,ISingleCollection<Print_RowDescriptor> objobjPrint_RowsList,ISingleCollection<ITag> objITagCollection) throws IOException
	{
		IDictionaryReader objIDictionaryReader = objRstDataSet.getDictionaryReader();
		ITransferSyntax objITransferSyntax = objRstDataSet.getTransferSyntax();
		IDataElement objIDataElement = null;
		int iVRType,iGroupNumber,iElementNumber;
		ICollectionIterator<Print_RowDescriptor> objIterator = objobjPrint_RowsList.iterator();
		while(objIterator.hasNext())
		{
			Print_RowDescriptor objRow = objIterator.next();
			iGroupNumber = objRow.getGroupNumber();
			iElementNumber = objRow.getElementNumber();


			iVRType = objRow.getVRType();
			if(!objRow.isSQ() && objRow.getItemUID() == null)
			{
				if(objITagCollection == null || objITagCollection.contains(new Tag(iGroupNumber, iElementNumber)))
				{
					objIDataElement = DataTypeConvertor.stringtoDataElement(objRow.getValue(),iGroupNumber,iElementNumber,iVRType,objITransferSyntax,objIDictionaryReader);
					if(objIDataElement != null)
						objRstDataSet.addDataElement(objIDataElement);
				}
				objobjPrint_RowsList.remove(objRow);
				objIterator = objobjPrint_RowsList.iterator();
			}

		}
	}

	/*
	 * This function gets all the SOP instance UID of enumSOPClassUIDsToFind corresponding to enumSOPClassUIDs.
	 */
	String[] getAllSOPInstance(EnumSOPClassUIDs enumSOPClassUIDsToFind, EnumSOPClassUIDs enumSOPClassUIDs,String strValue) throws SQLException
	{
		ISingleCollection<String> objStringColl = new SingleCollection<String>();
		String query = "Select Distinct "+getCoumnNameOfMappingTable(enumSOPClassUIDsToFind)+" from MAPPING_TABLE where "+getCoumnNameOfMappingTable(enumSOPClassUIDs)+"  = '"+ strValue + "'";
		PreparedStatement objPreparedStatement = objcon.prepareStatement(query);
		ResultSet rs = objPreparedStatement.executeQuery();
		while(rs.next())
		{
			objStringColl.add(rs.getString(1));
		}
		String[] strArr = new String[objStringColl.size()];
		int i=0;
		for(String strSOPInstance : objStringColl)
		{
			strArr[i] = strSOPInstance;
			i++;
		}
		return strArr;
	}

	/*
	 * This function deletes the Print JOB ID given in the data set provided.
	 */
	boolean deletePrintJOB(EnumSOPClassUIDs enumSOPClassUIDs, String strSOPInstanceUID, IDataSet objIDataSet) throws IOException, SQLException
	{
		ISingleCollection<Print_RowDescriptor> objRowCollection = populateRows(enumSOPClassUIDs,strSOPInstanceUID);
		IDataElement objPrintJObIDDE = objIDataSet.getDataForTag(0x2100,0x0010);
		String strPrintJobID = objPrintJObIDDE.getValue().toString();
		if(objRowCollection.size() == 0)
			return false;

		PreparedStatement pstmt = objcon.prepareStatement("Select ItemUUID from PRINT_QUEUE_MANAGEMENT where SQUUID = ? AND ElementNumber = ? AND GroupNumber = ? AND Value = ?");
		String strItemUID = null;
		String strDelSQID = null;
		for (Print_RowDescriptor objPrint_RowDescriptor : objRowCollection)
		{
			if(true == objPrint_RowDescriptor.isSQ() && objPrint_RowDescriptor.getGroupNumber()== 0x2120 && objPrint_RowDescriptor.getElementNumber()== 0x0050)
			{
				strDelSQID = objPrint_RowDescriptor.getSQUID();
				pstmt.clearParameters();
				pstmt.setString(1, strDelSQID);
				pstmt.setInt(2,objPrintJObIDDE.getTag().getElementNumber());
				pstmt.setInt(3,objPrintJObIDDE.getTag().getGroupNumber());
				pstmt.setString(4, strPrintJobID);
				ResultSet rsForPrintJOBID = pstmt.executeQuery();
				if(rsForPrintJOBID.next())
				{
					strItemUID = rsForPrintJOBID.getString("ItemUUID");
				}
				else
				{
					rsForPrintJOBID.close();
					pstmt.close();
					return false;
				}
				rsForPrintJOBID.close();
				break;
			}
		}
		if(strItemUID ==  null || strDelSQID == null)
			return false;

		boolean flag = false;
		IDataElement objOwnerIDDE = objIDataSet.getDataForTag(0x2100,0x0160);
		String strOwnerID = objOwnerIDDE.getValue().toString();

		pstmt.clearParameters();
		pstmt.setString(1, strDelSQID);
		pstmt.setInt(2,objOwnerIDDE.getTag().getElementNumber());
		pstmt.setInt(3,objOwnerIDDE.getTag().getGroupNumber());
		pstmt.setString(4, strOwnerID);

		ResultSet rsForOwnerID = pstmt.executeQuery();
		if(rsForOwnerID.next())
		{
			do
			{
				String strItemUIDFromOwnerID = rsForOwnerID.getString("ItemUUID");
				if(strItemUID.equals(strItemUIDFromOwnerID))
				{
					deleteItemRows(enumSOPClassUIDs, strItemUID);
					flag = true;
				}
			}while(rsForOwnerID.next());
		}
		else
		{
			rsForOwnerID.close();
			pstmt.close();
			return false;
		}
		return flag;
	}

	/*
	 * This function updates the priority of Print JOB ID given in the data set provided.
	 */
	boolean updatePrintJob(EnumSOPClassUIDs enumSOPClassUIDs,String strSOPInstanceUID, IDataSet objIDataSet) throws SQLException, IOException
	{
		IDataSet objTempIDataSet = retrieveSOPInstanceInfo(enumSOPClassUIDs, null, strSOPInstanceUID);
		ISingleCollection<Print_RowDescriptor> objRowCollection = populateRows(enumSOPClassUIDs,strSOPInstanceUID);
		ICollectionIterator<IDataElement> objDEIterator = objTempIDataSet.iterator();
		int iGroupNo,iElementNo;
		IDataElement objDataElement = null;
		IDicomValue objIDicomValue = null;
		if(objTempIDataSet == null)
		{
			return false;
		}
		else
		{
			while(objDEIterator.hasNext())
			{
				objDataElement = objDEIterator.next();
				objIDicomValue = objDataElement.getDicomValue();
				iGroupNo = objDataElement.getTag().getGroupNumber();
				iElementNo = objDataElement.getTag().getElementNumber();
				if(objIDicomValue instanceof SQ)
				{
					String strDelSQUID = getSQUID(iGroupNo,iElementNo,objRowCollection);

					String strTableName = getTablename(enumSOPClassUIDs);
					IDataElement objPrintJObIDDE = objIDataSet.getDataForTag(0x2100,0x0010);
					String strPrintJobID = objPrintJObIDDE.getValue().toString();
					String query = "Select ItemUUID from "+strTableName+" where SQUUID = '"+ strDelSQUID + "'"+" AND ElementNumber = "+ objPrintJObIDDE.getTag().getElementNumber() +" AND GroupNumber = "+ objPrintJObIDDE.getTag().getGroupNumber() + " AND Value = '"+ strPrintJobID + "'";
					PreparedStatement ps = objcon.prepareStatement(query);
					ResultSet rsForPrintJOBID = ps.executeQuery();

					IDataElement objOwnerIDDE = objIDataSet.getDataForTag(0x2100,0x0160);
					String strOwnerID = objOwnerIDDE.getValue().toString();
					query = "Select ItemUUID from "+strTableName+" where SQUUID = '"+ strDelSQUID + "' AND ElementNumber = "+ objOwnerIDDE.getTag().getElementNumber() + " AND GroupNumber = "+ objOwnerIDDE.getTag().getGroupNumber() +" AND Value = '"+ strOwnerID + "'";
					ps = objcon.prepareStatement(query);
					ResultSet rsForOwnerID = ps.executeQuery();
					while(rsForPrintJOBID.next())
					{
						String strItemUID = rsForPrintJOBID.getString("ItemUUID");
						while(rsForOwnerID.next())
						{
							String strItemUIDFromOwnerID = rsForOwnerID.getString("ItemUUID");
							if(strItemUID.equals(strItemUIDFromOwnerID))
							{
								IDataElement objPrintPriorityDE = objIDataSet.getDataForTag(0x2000,0x0020);
								String strPrintPriority = objPrintPriorityDE.getValue().toString();
								query = "update "+strTableName+" SET Value=?  where ItemUUID = '" + strItemUID + "'" +" AND ElementNumber = "+ objPrintPriorityDE.getTag().getElementNumber() + " AND GroupNumber = "+ objPrintPriorityDE.getTag().getGroupNumber() ;
								ps = objcon.prepareStatement(query);
								ps.setString(1,strPrintPriority);

								ps.executeUpdate();
							}
						}
					}
				}
			}
		}
		return true;
	}

	/*
	 * Store Store print Storage SOP Instance .
	 */
	boolean storeInstanceInfo(IDataSet objIDataSet,String strSOPInstanceUID)throws SQLException, IOException
	{
		createSOPInstance(EnumSOPClassUIDs.STOREDPRINTSTORAGE, objIDataSet, strSOPInstanceUID);
		return true;
	}

	/*
	 * Store Image SOP Instance.
	 */
	boolean storeImageInstanceInfo(IDataSet objIDataSet,String strSOPInstanceUID, String strPath)throws SQLException, IOException
	{
		this.strPath = strPath;
		return createSOPInstance(EnumSOPClassUIDs.STOREDPRINTSTORAGE, objIDataSet, strSOPInstanceUID);
	}
}
