/**
* PURPOSE : Pre Save Logic implementation for SHIPMENT component.
* AUTHOR : Sumit Sarkar
*/

package ibase.webitm.ejb.wms;

import ibase.webitm.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;

import java.rmi.RemoteException;
import java.sql.*;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

import org.w3c.dom.*;

import javax.ejb.*;
//changed by sankara on 15/10/13 for clubing multiple ptcn
import ibase.webitm.utility.wms.*;
@javax.ejb.Stateless
public class ShipmentPrs extends ValidatorEJB implements ShipmentPrsLocal, ShipmentPrsRemote
{
	public String preSave() throws RemoteException,ITMException
	{
		return "";
	}

	public String preSave(String xmlString, String editFlag, String xtraParams, Connection conn)throws RemoteException,ITMException
	{
		String errString = "";
		System.out.println("ShipmentPrs EJB called");
		Document dom = null;
		try
		{
			System.out.println("xmlString in ShipmentPrs : preSaveRec \n"+xmlString);			
			if (xmlString != null && xmlString.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString);
				errString = executepreSave( dom, editFlag, xtraParams, conn );
			}			
		}
		catch(Exception e)
		{
			System.out.println("Exception :ShipmentPrs :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}

	private String executepreSave( Document dom, String editFlag, String xtraParams, Connection conn ) throws RemoteException, ITMException
	{
		System.out.println("executepreSave method called");
		String sql = "";			
		String tranId = "";
		String transMode = "";
		Set ptcn = null;		
		Set tranCodeSet = null;	
		int deleteCount = 0;
		boolean isSameWave = false;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String errString = "";
		String tempPtcn = "";
		Set refSer = new HashSet();//added by sumit on 11/02/13
		int count = 0;
		String tranCode = "";
		
		HashMap ptcnAndConsingnee = new HashMap();
		HashMap newPtcnAndNewConsingnee = new HashMap();
		
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		//chnaged by sankara on 21/10/13 for clubbing ptcn for newly created shipment
		Set keyset = new HashSet();
		//changed by sankara on 22/09/14 for sitetransporter
		String siteCodeShip = "";
		try
		{			
			conn.setAutoCommit(false); 
			GenericUtility genericUtility = GenericUtility.getInstance();
			ptcn = new HashSet();
			tranCodeSet = new HashSet();
			NodeList parentNodeList = null;
			NodeList childList = null;
			int parentNodeListLength ;
			int detLine = 0;
			Node parentNode = null;
			String updateFlag = "";
			String shipmentId = "";
			parentNodeList = dom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			for(int row = 0; row < parentNodeListLength; row++)
			{
				parentNode = parentNodeList.item(row);					
	        	updateFlag = getAttribValue(parentNode,"attribute", "updateFlag"); //(parentNode, "attribute", "status"));
	        	//System.out.println(" update flag ["+updateFlag+"]");
	        	if(!"D".equalsIgnoreCase(updateFlag))
	        	{
	        		tempPtcn = GenericUtility.getInstance().getColumnValueFromNode("ptcn", dom.getElementsByTagName("Detail2").item(row));
	        		//changed by sankara on 08/10/14 if ptcn found null start.
	        		//ptcn.add(tempPtcn);	     
	        		if( tempPtcn != null && tempPtcn.trim().length() > 0 )
	        		{
	        			ptcn.add(tempPtcn);	     
	        		}
	        		//changed by sankara on 08/10/14 if ptcn found null end.    
	        		tranCode = GenericUtility.getInstance().getColumnValueFromNode("tran_code", dom.getElementsByTagName("Detail2").item(row));
	        		tranCodeSet.add(tranCode);
	        		
	        		shipmentId = checkNull(GenericUtility.getInstance().getColumnValueFromNode("shipment_id", dom.getElementsByTagName("Detail2").item(row)));
	        		//Changed by sumit on 11/02/13 getting ref ser 
	        		refSer.add(checkNull(GenericUtility.getInstance().getColumnValueFromNode("ref_ser", dom.getElementsByTagName("Detail2").item(row))).trim());
	        		//changed by sankara on 22/09/14 for sitetransporter start.
	        		sql = " SELECT DISTINCT S.SITE_CODE__SHIP FROM SORDER S, SHIPMENT D, SHIP_DOCS SD, WAVE_TASK W, WAVE_TASK_DET WT " +
	        				" WHERE D.SHIPMENT_ID = SD.SHIPMENT_ID AND SD.PTCN  = WT.PTCN AND S.SALE_ORDER = WT.SALE_ORDER AND WT.PTCN = ? ";	     
	        		pstmt = conn.prepareStatement( sql );
	        		pstmt.setString( 1, tempPtcn );
	        		rs = pstmt.executeQuery();
	        		if( rs.next() )
	        		{
	        			siteCodeShip = checkNull(rs.getString("SITE_CODE__SHIP"));
	        			System.out.println("siteCodeShip::::::"+siteCodeShip);
	        		}
	        		rs.close();rs = null;
	        		pstmt.close();pstmt = null;	
	        		//changed by sankara on 22/09/14 for sitetransporter end.
	  				
	        		if( shipmentId == null || shipmentId.length() == 0 )
	        		{
	        			sql = "SELECT PTCN FROM SHIP_DOCS WHERE PTCN = ? ";
			        	pstmt = conn.prepareStatement(sql);			        	
			        	pstmt.setString(1, tempPtcn);
	        		}
	        		else
	        		{
	        			sql = "SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID <> ? AND PTCN = ? ";
			        	pstmt = conn.prepareStatement(sql);
			        	pstmt.setString(1, shipmentId);
			        	pstmt.setString(2, tempPtcn);
	        		}
		        	
		        	rs = pstmt.executeQuery();
		        	if( rs.next())
		        	{
		        		errString = itmDBAccessEJB.getErrorString("","VTPTCNALLX","","",conn);
						return errString;
		        	}
		        	rs.close(); rs = null;
		        	pstmt.close(); pstmt = null;    	
	        	
	        	}
			}
			
			System.out.println(" ptcn ["+ptcn+"] shipmentId ["+shipmentId+"] refser.size() ["+refSer.size()+"] refSer "+refSer);	
			
			//Changed by sumit on 11/02/13 adding ref_ser condition
			//if( ptcn != null && ptcn.size() > 0 )
			if( ptcn != null && ptcn.size() > 0 && refSer.contains("S-DSP") && refSer.size() == 1)
			{
			
				if( !isConsingneeSameOfEnteredPTCN( ptcn, conn ))  // is ship consignee are same for entered ptcn
				{
					System.out.println(" isConsingneeSameOfAllPTCN ");
					errString = itmDBAccessEJB.getErrorString("","INVSHIPCON","","",conn);
					return errString;
				}
								
				isSameWave = isSameWaveId(ptcn, conn);
				System.out.println(" isSameWave ["+isSameWave+"]");
				if( isSameWave )
				{				
					if( !isAllPtcnTaken(ptcn, conn))
					{
						System.out.println(" isAllPtcnTaken ");
						
						System.out.println(" else sumit  ");
						ptcnAndConsingnee = getConsigneAgainstPTCN(ptcn, conn);
						//changed by sankara on 22/09/14 for sitetransporter	
						//newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn,conn);	
						newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn, siteCodeShip, conn);					
						System.out.println(" ptcnAndConsingnee "+ptcnAndConsingnee);
						System.out.println(" newPtcnAndNewConsingnee "+newPtcnAndNewConsingnee);				
						//chnaged by sankara on 21/10/13 for clubbing ptcn for newly created shipment
						keyset = newPtcnAndNewConsingnee.keySet();
						System.out.println("keysetsize--->"+keyset.size());
						Iterator iterator = ptcnAndConsingnee.keySet().iterator();
						while(iterator.hasNext())
						{
							/*HashMap  tempHash = new HashMap();
							tempHash = 	(HashMap) iterator.next();*/
							String key = (String)iterator.next();
							String val = (String)ptcnAndConsingnee.get(key);
							System.out.println("key ["+key+"] and value ["+val+"]");							
							if( newPtcnAndNewConsingnee.containsValue(val))
							{
								//changed by sankara on 15/10/13 for clubing multiple ptcn start.
								/*errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
								return errString;  */
								//chnaged by sankara on 21/10/13 for clubbing ptcn for newly created shipment
								//if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0)
								if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0 && keyset.size() != 0 )
								{
									System.out.println("all ptcns in one wave");
									errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
									//changed by sankara on 25/11/13 for clubing all ptcn as message level
									errString = findError(errString,xtraParams, conn);
									//}
									if(errString != null && errString.indexOf("W") != -1)
									{
										System.out.println("all ptcns in one wave1");
										CommonWmsUtil.ALL_PTCN_EX_COUNT++;
									}
									//changed by sankara on 25/11/13 for clubing all ptcn as message level
									return errString; 
								}
								//changed by sankara on 15/10/13 for clubing multiple ptcn end.
							}
						}
						//}
					}
				}
				else
				{
					ptcnAndConsingnee.clear();
					newPtcnAndNewConsingnee.clear();
					ptcnAndConsingnee = getConsigneAgainstPTCN(ptcn, conn);
					//changed by sankara on 22/09/14 for sitetransporter	
					//newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn,conn);			
					newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn, siteCodeShip, conn);						
					System.out.println(" * ptcnAndConsingnee "+ptcnAndConsingnee);		
					System.out.println(" * ptcnAndConsingnee "+ptcnAndConsingnee);
					System.out.println(" * newPtcnAndNewConsingnee "+newPtcnAndNewConsingnee);
					
					Iterator iterator = ptcnAndConsingnee.keySet().iterator();
					while(iterator.hasNext())
					{
						/*HashMap  tempHash = new HashMap();
						tempHash = 	(HashMap) iterator.next();*/
						String key = (String)iterator.next();
						String val = (String)ptcnAndConsingnee.get(key);
						System.out.println("key ["+key+"] and value ["+val+"]");							
						if( newPtcnAndNewConsingnee.containsValue(val))
						{
						  //changed by sankara on 15/10/13 for clubing multiple ptcn start.	
						  /*errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
							return errString;  */
							//chnaged by sankara on 21/10/13 for clubbing ptcn for newly created shipment
							//if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0)
							if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0 && keyset.size() != 0 )
							{
								System.out.println("all ptcns in diff wave");
								errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
								//changed by sankara on 25/11/13 for clubing all ptcn as message level
								errString = findError(errString,xtraParams, conn);
								//}
								if(errString != null && errString.indexOf("W") != -1)
								{
									System.out.println("all ptcns in diff wave1");
									CommonWmsUtil.ALL_PTCN_EX_COUNT++;
								}
								//changed by sankara on 25/11/13 for clubing all ptcn as message level
								return errString; 
							}
							//changed by sankara on 15/10/13 for clubing multiple ptcn end.
						}
					}
				}
			}
			//changed by sankara on 15/10/13 for clubing multiple ptcn start.	
            System.out.println("***************Calling Commanwms*************");
			System.out.println("com.allPtcnCount:"+CommonWmsUtil.ALL_PTCN_EX_COUNT  );
			//changed by sankara on 15/10/13 for clubing multiple ptcn end.	
			if( tranCodeSet != null && tranCodeSet.size() > 1 && refSer.contains("S-DSP") && refSer.size() == 1)
			{
				//changed by sankara on 15/10/13 for clubing multiple ptcn start.	
				/* errString = itmDBAccessEJB.getErrorString("","VTDIFFTRAN","","",conn);
				System.out.println(" errString -->["+errString+"]");
				return errString; */
				//chnaged by sankara on 21/10/13 for clubbing ptcn for newly created shipment
				//if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0)
				if(CommonWmsUtil.ALL_PTCN_EX_COUNT == 0 && keyset.size() != 0 )
				{
					System.out.println("inside checking");
					errString = itmDBAccessEJB.getErrorString("","VTDIFFTRAN","","",conn);
				}
				if(errString != null && errString.indexOf("W") != -1)
				{
					CommonWmsUtil.ALL_PTCN_EX_COUNT++;
				}
				System.out.println("allptcnscount:"+CommonWmsUtil.ALL_PTCN_EX_COUNT);
				System.out.println(" errString -->["+errString+"]");	
				//changed by sankara on 15/10/13 for clubing multiple ptcn end.	
			}
			
		}
		catch (SQLException sqe)
		{
			System.out.println("The SQLException occurs in ShipmentPrs :"+sqe);
			sqe.printStackTrace();
			throw new ITMException(sqe);
		}
		catch(Exception e)
		{
			System.out.println("The Exception occurs in ShipmentPrs :"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :ShipmentPrs :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}		
	
	private boolean isSameWaveId (Set ptcn, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Set setWaveId = null;
		boolean isSame = false;
		int count = 0;
		StringBuffer buffer = new StringBuffer();
		String inputString = "";
		try
		{
			Iterator iterator = ptcn.iterator();
			while ( iterator.hasNext())
			{				
				buffer.append(iterator.next()).append("','");
			}
			inputString = buffer.toString();
			inputString = inputString.substring(0, inputString.length() - 3 );
			System.out.println(" input - string "+inputString);
			
			/*sql = " SELECT DISTINCT WT.WAVE_ID  FROM WAVE_TASK_DET WTD, WAVE_TASK WT " +
					" WHERE WT.WAVE_ID IN (SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN (?) ) " +
					" AND WTD.WAVE_ID = WT.WAVE_ID ";*/
			sql = " SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') ";
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1, inputString);
			rs = pstmt.executeQuery();
			while( rs.next() )
			{				
				count++;
			}
			System.out.println(" count : ["+count+"]");
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( count == 1 )
			{
				isSame = true ;
			}
			else
			{
				isSame = false;
			}
			//System.out.println(" isSame ["+isSame+"]");
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return isSame;
	}
	
	private boolean isAllPtcnTaken(Set ptcn, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Set setWaveId = null;
		boolean isPTCNTaken = false;
		int count = 0;
		StringBuffer buffer = new StringBuffer();
		String inputString = "";
		try
		{
			Iterator iterator = ptcn.iterator();
			while ( iterator.hasNext())
			{				
				buffer.append(iterator.next()).append("','");
			}
			inputString = buffer.toString();
			inputString = inputString.substring(0, inputString.length() - 3 );
			System.out.println(" input string "+inputString);
			
			sql = " SELECT WTD.PTCN  FROM WAVE_TASK_DET WTD, WAVE_TASK WT WHERE WT.WAVE_ID IN  " +
					" (SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') ) " +
					" AND WTD.WAVE_ID = WT.WAVE_ID GROUP BY WTD.PTCN ";
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1, inputString);
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				count++;
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( count == ptcn.size() )
			{
				isPTCNTaken = true ;
			}
			else
			{
				isPTCNTaken = false;
			}
			System.out.println(" isAllPtcnTaken: isPTCNTaken ["+isPTCNTaken+"]");
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return isPTCNTaken;
	}
	
	private boolean isConsingneeSameOfEnteredPTCN(Set ptcn, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Set setWaveId = null;
		boolean isAllCngSame = false;
		int count = 0;
		StringBuffer buffer = new StringBuffer();
		String inputString = "";
		Set sipConsignee = new HashSet();
		
		try
		{
			Iterator iterator = ptcn.iterator();
			while ( iterator.hasNext())
			{				
				buffer.append(iterator.next()).append("','");
			}
			inputString = buffer.toString();
			inputString = inputString.substring(0, inputString.length() - 3 );
			System.out.println(" input string "+inputString);
			////Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
			//sql = "SELECT DISTINCT SHIP_CONS_GRP FROM CUSTOMER WHERE CUST_CODE IN " +
			sql = "SELECT DISTINCT CASE WHEN SHIP_CONS_GRP IS NULL THEN CUST_CODE ELSE SHIP_CONS_GRP END AS SHIP_CONS_GRP FROM CUSTOMER WHERE CUST_CODE IN " + 
					//" ( SELECT CUST_CODE FROM DESPATCH D,WAVE_TASK_DET WTD  WHERE WTD.WAVE_ID = D.WAVE_ID" +
					" ( SELECT CUST_CODE__DLV FROM DESPATCH D,WAVE_TASK_DET WTD  WHERE WTD.WAVE_ID = D.WAVE_ID" +
					" AND WTD.REF_ID = D.DESP_ID AND WTD.PTCN IN ('"+inputString+"') AND WTD.SALE_ORDER = D.SORD_NO )";
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1, inputString);
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				count++;
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			System.out.println(" count ["+count+"]");
			if( count == 1 )
			{
				isAllCngSame = true ;
			}
			else
			{
				isAllCngSame = false;
			}
			System.out.println(" isConsingneeSameOfEnteredPTCN: isAllCngSame ["+isAllCngSame+"]");			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return isAllCngSame;
	}
	private boolean isTransporterSameForPTCN(String despID, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isAllTransporterSame = false;
		int count = 0;
		StringBuffer buffer = new StringBuffer();
		
		
		try
		{
			
			////Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
			//sql = "SELECT DISTINCT SHIP_CONS_GRP FROM CUSTOMER WHERE CUST_CODE IN " +
			sql = "SELECT DISTINCT TRAN_CODE FROM DESPACTCHDET WHERE DESP_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, despID);
			
			rs = pstmt.executeQuery();
			
			while( rs.next() )
			{
				count++;
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			System.out.println(" count ["+count+"]");
			if( count == 1 )
			{
				isAllTransporterSame = true ;
			}
			else
			{
				isAllTransporterSame = false;
			}
			System.out.println(" isisTransporterSameForPTCN ["+isAllTransporterSame+"]");			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return isAllTransporterSame;
	}
	//changed by sankara on 22/09/14 for sitetransporter 
	//private HashMap getConsigneAgainstNewPTCN(Set ptcn, Connection conn) throws ITMException
	private HashMap getConsigneAgainstNewPTCN(Set ptcn,String siteCodeShip, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		PreparedStatement pstmt = null, pstmt1 =null;
		ResultSet rs = null, rs1 = null;
		Set setWaveId = null;
		boolean isAllCngSame = false;
		int count = 0;
		StringBuffer buffer = new StringBuffer();
		String inputString = "";
		HashMap sipConsignee = new HashMap();
		String stPTCN = "";
		//Changed By Pragyan to store consignee group and customer code delivery.start
		String shiptConsiGroup = "";
		String shipCustCodeDlv = "";
		String serviceCode = "";
		String shipmentType = "";
		//changed by sankara on 22/09/14 for sitetransporter
		boolean istransporterexist = false;
		try
		{
			Iterator iterator = ptcn.iterator();
			while ( iterator.hasNext())
			{				
				buffer.append(iterator.next()).append("','");
			}
			inputString = buffer.toString();
			inputString = inputString.substring(0, inputString.length() - 3 );
			System.out.println(" input *-  string "+inputString);
			
			
			//Chnaged By Pragyan 07-OCT-13 not To validate UPS/FEDEX Shipments in 
			///sql = "SELECT DISTINCT PTCN FROM WAVE_TASK_DET WHERE WAVE_ID IN ( SELECT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN ? )";
			
			//For different ptcn
			/*sql = " SELECT DISTINCT WTD.PTCN FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE C.CUST_CODE = D.CUST_CODE " +
					" AND WTD.WAVE_ID = D.WAVE_ID AND WTD.REF_ID = D.DESP_ID AND WTD.PTCN NOT IN '"+inputString+"' " +
					" AND WTD.WAVE_ID IN ( SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN '"+inputString+"' )";*/
			//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
			//sql = " SELECT DISTINCT WTD.PTCN FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE C.CUST_CODE = D.CUST_CODE " +
			/*sql = " SELECT DISTINCT WTD.PTCN FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE C.CUST_CODE = D.CUST_CODE__DLV " +
					" AND WTD.WAVE_ID = D.WAVE_ID AND WTD.REF_ID = D.DESP_ID AND WTD.PTCN NOT IN ('"+inputString+"') " +
					" AND WTD.WAVE_ID IN ( SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') )";*/
			
			//changed by sankara on 22/09/14 for sitetransporter start.
			/*sql = " SELECT DISTINCT WTD.PTCN,T.TRAN_CODE,T.SERVICE_CODE,T.SHIPMENT_TYPE FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD," +
					" TRANSPORTER T WHERE C.CUST_CODE = D.CUST_CODE__DLV " +
					" AND T.TRAN_CODE = D.TRAN_CODE AND WTD.WAVE_ID = D.WAVE_ID AND WTD.REF_ID = D.DESP_ID AND " +
					" WTD.PTCN NOT IN ('"+inputString+"')  AND WTD.WAVE_ID IN ( SELECT DISTINCT WAVE_ID FROM " +
					//changed by sankara on 21/10/13 for getting unconfirmed ptcn
					//" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') )";
					" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') ) AND D.CONFIRMED = 'N' ";*/

			sql = " SELECT DISTINCT WTD.PTCN,ST.TRAN_CODE,ST.SERVICE_CODE,ST.SHIPMENT_TYPE FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD," +
					" SITE_TRANSPORTER ST WHERE C.CUST_CODE = D.CUST_CODE__DLV " +
					" AND ST.TRAN_CODE = D.TRAN_CODE AND WTD.WAVE_ID = D.WAVE_ID AND WTD.REF_ID = D.DESP_ID AND " +
					" WTD.PTCN NOT IN ('"+inputString+"')  AND WTD.WAVE_ID IN ( SELECT DISTINCT WAVE_ID FROM " +
					//changed by sankara on 21/10/13 for getting unconfirmed ptcn
					//" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') )";
					" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') ) AND D.CONFIRMED = 'N' AND ST.SITE_CODE = '"+siteCodeShip+"' ";
			//changed by sankara on 22/09/14 for sitetransporter end.
			pstmt = conn.prepareStatement(sql);

			//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
			/*sql1 = 	//" SELECT C.SHIP_CONS_GRP FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE WTD. PTCN = ? " +
					//" AND D.WAVE_ID = WTD.WAVE_ID  AND C.CUST_CODE = D.CUST_CODE AND WTD.REF_ID = D.DESP_ID ";
					"SELECT C.SHIP_CONS_GRP,D.CUST_CODE__DLV FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE WTD. PTCN = ? " +
					" AND D.WAVE_ID = WTD.WAVE_ID  AND C.CUST_CODE = D.CUST_CODE__DLV AND WTD.REF_ID = D.DESP_ID ";*/
			sql1  = " SELECT C.SHIP_CONS_GRP,D.CUST_CODE__DLV FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD ,"
					+" TRANSPORTER T  WHERE WTD. PTCN = ?  AND D.WAVE_ID = WTD.WAVE_ID"  
					+" AND C.CUST_CODE = D.CUST_CODE__DLV AND WTD.REF_ID = D.DESP_ID AND D.TRAN_CODE = T.TRAN_CODE" 
					+" AND CASE WHEN T.SERVICE_CODE IS NULL THEN '0' ELSE T.SERVICE_CODE END IN " 
					+" (SELECT CASE WHEN T.SERVICE_CODE IS NULL THEN '0' ELSE T.SERVICE_CODE END FROM TRANSPORTER T, DESPATCH D," 
					+" WAVE_TASK_DET WTD WHERE T.TRAN_CODE = D.TRAN_CODE  AND D.WAVE_ID = WTD.WAVE_ID AND" 
					//changed by sankara on 21/10/13 for getting unconfirmed ptcn
					//+" D.DESP_ID = WTD.REF_ID AND WTD.PTCN IN ('"+inputString+"'))";
					+" D.DESP_ID = WTD.REF_ID AND WTD.PTCN IN ('"+inputString+"')) AND D.CONFIRMED = 'N' ";
			
			pstmt1 = conn.prepareStatement(sql1);
			
			/*pstmt.setString(1, inputString);
			pstmt.setString(2, inputString);*/
			rs = pstmt.executeQuery();
			
			while( rs.next() )
			{
				//changed by sankara on 22/09/14 for sitetransporter
				istransporterexist = true;
				stPTCN = rs.getString("PTCN");
				serviceCode = checkNull(rs.getString("SERVICE_CODE"));
				shipmentType = checkNull(rs.getString("SHIPMENT_TYPE"));
				System.out.println(" serviceCode "+serviceCode+"]["+shipmentType+"]");
				//changed by sankara on 15/10/13 for clubing multiple ptcn
				//if(serviceCode.trim().length() == 0 && shipmentType.trim().length() == 0)
				if(serviceCode.trim().length() == 0 )
				{
					pstmt1.setString(1, stPTCN);
					rs1 = pstmt1.executeQuery();

					if( rs1.next())
					{
						//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
						shiptConsiGroup = checkNull(rs1.getString("SHIP_CONS_GRP"));
						shipCustCodeDlv = checkNull(rs1.getString("CUST_CODE__DLV"));


						if(shiptConsiGroup.trim().length() > 0)
						{
							sipConsignee.put(stPTCN, shiptConsiGroup);
						}
						else
						{
							sipConsignee.put(stPTCN, shipCustCodeDlv);
						}
						//}
						//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.end
						//PTCN, "SHIP_CONS_GRP";
						//sipConsignee.put(stPTCN, rs1.getString("SHIP_CONS_GRP"));
					}
					pstmt1.clearParameters();
					rs1.close(); rs1 = null;
					count++;
				}
			}
			//changed by sankara on 22/09/14 for sitetransporter start.
			if(!istransporterexist)
			{
				sql = " SELECT DISTINCT WTD.PTCN,T.TRAN_CODE,T.SERVICE_CODE,T.SHIPMENT_TYPE FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD," +
						" TRANSPORTER T WHERE C.CUST_CODE = D.CUST_CODE__DLV " +
						" AND T.TRAN_CODE = D.TRAN_CODE AND WTD.WAVE_ID = D.WAVE_ID AND WTD.REF_ID = D.DESP_ID AND " +
						" WTD.PTCN NOT IN ('"+inputString+"')  AND WTD.WAVE_ID IN ( SELECT DISTINCT WAVE_ID FROM " +
						//changed by sankara on 21/10/13 for getting unconfirmed ptcn
						//" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') )";
						" WAVE_TASK_DET WHERE PTCN IN ('"+inputString+"') ) AND D.CONFIRMED = 'N' ";

				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while( rs.next() )
				{
					stPTCN = rs.getString("PTCN");
					serviceCode = checkNull(rs.getString("SERVICE_CODE"));
					shipmentType = checkNull(rs.getString("SHIPMENT_TYPE"));
					System.out.println(" serviceCode "+serviceCode+"]["+shipmentType+"]");
					//changed by sankara on 15/10/13 for clubing multiple ptcn
					//if(serviceCode.trim().length() == 0 && shipmentType.trim().length() == 0)
					if(serviceCode.trim().length() == 0 )
					{
						pstmt1.setString(1, stPTCN);
						rs1 = pstmt1.executeQuery();

						if( rs1.next())
						{
							//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
							shiptConsiGroup = checkNull(rs1.getString("SHIP_CONS_GRP"));
							shipCustCodeDlv = checkNull(rs1.getString("CUST_CODE__DLV"));
							
							
								if(shiptConsiGroup.trim().length() > 0)
								{
									sipConsignee.put(stPTCN, shiptConsiGroup);
								}
								else
								{
									sipConsignee.put(stPTCN, shipCustCodeDlv);
								}
							//}
							//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.end
							//PTCN, "SHIP_CONS_GRP";
							//sipConsignee.put(stPTCN, rs1.getString("SHIP_CONS_GRP"));
						}
						pstmt1.clearParameters();
						rs1.close(); rs1 = null;
						count++;
					}
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( pstmt1 != null )
			{
				pstmt1.close(); pstmt1 = null;
			}
			
			System.out.println("getConsigneAgainstNewPTCN: sipConsignee "+sipConsignee);
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( pstmt1 != null)
				{
					pstmt1.close(); pstmt1 = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return sipConsignee;
	}
	
	
	private HashMap getConsigneAgainstPTCN(Set ptcn, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//Set setWaveId = null;
		//boolean isAllCngSame = false;
		//int count = 0;
		//StringBuffer buffer = new StringBuffer();
		//String inputString = "";
		HashMap sipConsignee = new HashMap();
		String stPTCN = "";
		
		try
		{
			//Changed By Pragyan 25/04/13 to join with cust_code__dlv delivery.start
			/*sql1 = " SELECT C.SHIP_CONS_GRP FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE WTD. PTCN = ? " +
					" AND D.WAVE_ID = WTD.WAVE_ID  AND C.CUST_CODE = D.CUST_CODE AND WTD.REF_ID = D.DESP_ID ";*/
			sql1 =  " SELECT CASE WHEN C.SHIP_CONS_GRP IS NULL THEN C.CUST_CODE ELSE C.SHIP_CONS_GRP END AS SHIP_CONS_GRP " +
					" FROM CUSTOMER C, DESPATCH D, WAVE_TASK_DET WTD WHERE WTD. PTCN = ? " +
					" AND D.WAVE_ID = WTD.WAVE_ID  AND C.CUST_CODE = D.CUST_CODE__DLV AND WTD.REF_ID = D.DESP_ID ";
			
			pstmt = conn.prepareStatement(sql1);
			
			Iterator iterator = ptcn.iterator();
			while ( iterator.hasNext())
			{				
				stPTCN = iterator.next().toString();		
				pstmt.setString(1, stPTCN);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					//               PTCN, "SHIP_CONS_GRP";
					sipConsignee.put(stPTCN, rs.getString("SHIP_CONS_GRP"));
				}
				pstmt.clearParameters();
				rs.close(); rs = null;
				//count++;
			}			
			if( pstmt != null )
			{
				pstmt.close(); pstmt = null;
			}
			
			System.out.println(" sipConsignee "+sipConsignee);
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return sipConsignee;
	}
	
	private String getAttribValue(Node detailNode, String nodeName, String attribStr)
	{
		String attribValue = "";
		try
		{
			String domStr = GenericUtility.getInstance().serializeDom(detailNode);
			Document dom = GenericUtility.getInstance().parseString(domStr);
			if( dom != null /*&& dom.getAttributes() != null*/)
			{
				Node attributeNode = dom.getElementsByTagName( nodeName ).item(0);
				attribValue = getAttribValue(attributeNode, attribStr);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}
	private String getAttribValue(Node detailNode, String attribStr)
	{
		String attribValue = "";
		try
		{
			if( detailNode != null && detailNode.getAttributes() != null)
			{
				Node attribNode = detailNode.getAttributes().getNamedItem( attribStr );
				if( attribNode != null )
				{
					attribValue = checkNull( attribNode.getNodeValue() );
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}
	//changed by sankara on 10/04/13 for getting service code start.
	public String getServiceCode(String key, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String servicceCode = "";
		String stPTCN = "";
		try
		{
			sql1 =  " SELECT T.SERVICE_CODE AS SERVICE_CODE FROM TRANSPORTER T, DESPATCH D, WAVE_TASK_DET WTD WHERE T.TRAN_CODE = D.TRAN_CODE" +
					" AND D.WAVE_ID = WTD.WAVE_ID AND D.DESP_ID = WTD.REF_ID AND WTD.PTCN = ? ";
			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, key);			
			rs = pstmt.executeQuery();
		    if( rs.next())
			{
			   servicceCode = rs.getString("SERVICE_CODE");
			}
			if( pstmt != null )
			{
				pstmt.close(); 
				pstmt = null;
			}
			if( rs != null )
			{
				rs.close(); 
				rs = null;
			}

			System.out.println(" SERVICCECODE:"+servicceCode);

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); 
					rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); 
					pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return servicceCode;
	}
	//changed by sankara on 10/04/13 for getting service code end.
	//changed by sankara on 25/11/13 for display allptcn handled at mesages level start.
	private String findError(String errString, String xtraParams, Connection conn) throws ITMException
	{
		Document errDomObj = null;
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String msgType = "";
		String winName = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
		String errorString = "";
		try
		{
			//changed by sankara on 05/03/14 pass profile id
			System.out.println("xtraParams::::::::::::::::"+xtraParams);
			String profileId = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "profileId" ));		
			System.out.println("Found Profile ID->"+profileId);
			
			//sql = " SELECT MSG_TYPE FROM MESSAGES_LEVEL WHERE PROFILE_ID = ? AND WIN_NAME = 'w_shipment' AND MSG_NO = 'ALLPTCNNTA' ";
			sql = " SELECT MSG_TYPE, WIN_NAME FROM MESSAGES_LEVEL WHERE PROFILE_ID = ? AND MSG_NO = 'ALLPTCNNTA' ";
			pstmt = conn.prepareStatement(sql);
			//changed by sankara on 03/05/14 passed profile id
			//pstmt.setString(1, userId);
			pstmt.setString(1, profileId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				msgType = checkNull(rs.getString("MSG_TYPE"));
				winName = checkNull(rs.getString("WIN_NAME"));
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null; 

			if(winName == null || winName.trim().length() == 0 )
			{
				winName = "X";
			}			
			System.out.println("msgType->"+msgType);
			System.out.println("winName->"+winName);
			if((winName.trim().equalsIgnoreCase("w_shipment") || winName.trim().equalsIgnoreCase("X")) &&  msgType.trim().equalsIgnoreCase("W"))
			{			
				if(errString != null && errString.trim().length()!=0)
				{
					System.out.println("errString:->"+errString);
					errDomObj = genericUtility.parseString(errString); 
					System.out.println("errDomObj:->"+errDomObj);
					NodeList  errorNodeList = errDomObj.getElementsByTagName("error");
					System.out.println("errorNodeList:->"+errorNodeList);

					errDomObj.getElementsByTagName("error").item(0).getAttributes().getNamedItem("type").setNodeValue("W");
					errDomObj.getElementsByTagName("type").item(0).getFirstChild().setNodeValue("W");
					errDomObj.getElementsByTagName("trace").item(0).getFirstChild().getNodeValue().contains("Error");
					errDomObj.getElementsByTagName("trace").item(0).getFirstChild().getNodeValue();
					String mainString = errDomObj.getElementsByTagName("trace").item(0).getFirstChild().getNodeValue();
					System.out.println("mainString:->"+mainString);
					mainString = mainString.substring(mainString.indexOf(":"), mainString.indexOf("Shipment")+8);
					System.out.println("mainString1:->"+mainString);
					errDomObj.getElementsByTagName("trace").item(0).getFirstChild().setNodeValue("Warning"+mainString);
					errorString = GenericUtility.getInstance().serializeDom(errDomObj);
					System.out.println("errString of trace:->"+errorString);
					if(errorString != null && errorString.trim().length() > 0 )
					{
						errString = errorString;
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close(); 
					rs = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); 
					pstmt = null;
				}
			}
			catch( Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return errString;
	}
	//changed by sankara on 25/11/13 for display allptcn handled at mesages level end.
}
