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

@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;		
		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;
		
		HashMap ptcnAndConsingnee = new HashMap();
		HashMap newPtcnAndNewConsingnee = new HashMap();
		
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{			
			conn.setAutoCommit(false); 
			GenericUtility genericUtility = GenericUtility.getInstance();
			ptcn = 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));
	        		ptcn.add(tempPtcn);	        	
	        		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());
	        	
	        		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);
						newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn, conn);						
						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))
							{
								errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
								return errString;
							}
						}
						//}
					}
				}
				else
				{
					ptcnAndConsingnee.clear();
					newPtcnAndNewConsingnee.clear();
					ptcnAndConsingnee = getConsigneAgainstPTCN(ptcn, conn);
					newPtcnAndNewConsingnee = getConsigneAgainstNewPTCN(ptcn, conn);						
					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))
						{
							errString = itmDBAccessEJB.getErrorString("","ALLPTCNNTA","","",conn);
							return errString;
						}
					}
				}
			}
			//errString = getErrorString("PTCN","ALLPTCNNTA","");
			System.out.println(" errString -->["+errString+"]");
			
		}
		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 HashMap getConsigneAgainstNewPTCN(Set ptcn, 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 = "";
		
		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 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+"') )";
			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 ";
			pstmt1 = conn.prepareStatement(sql1);
			
			/*pstmt.setString(1, inputString);
			pstmt.setString(2, inputString);*/
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				stPTCN = rs.getString("PTCN");
				
				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;
				}
			}
			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;
	}
	
	
}
