package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.adsionline.ADSIShippingWS.ADSIShippingLocator;
import com.adsionline.ADSIShippingWS.ADSIShippingSoap;
import com.adsionline.ADSIShippingWS.StringDictionaryReturn;
//import com.lowagie.text.pdf.hyphenation.TernaryTree.Iterator;

@javax.ejb.Stateless

public class ShipmentPrc extends ProcessEJB implements ShipmentPrcLocal,ShipmentPrcRemote
{

	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("INSIDE GET DATA");
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String frdate = "";
		String fromDate = "";
		String tDate = "";
		String toDate="";
		String dateFrom = "";
		String dateTo = "";
		String transporterFrom = "";
		String transporterTo = "";
		String shipmentType = "";
		String ptcn = "";
		String errCode = "";
		String errString = "";
		PreparedStatement pstmt=null;
		String getDataSql=null;
		String resultString = "";
		Connection conn = null;
		String studId=null;
		ResultSet rs=null;
		boolean rsflag=false;
		StringBuffer test=new StringBuffer();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();		
		try
		{
			test = new StringBuffer( "<?xml version='1.0' encoding='UTF-8'?>\r\n<DocumentRoot>\r\n<description>Datawindow Root</description>\r\n<group0>\r\n" +
					"<description>Group0 description</description>\r\n<Header0>\r\n<description>Header0 members</description>\r\n");
			int count = 0;			
			NodeList parentList = null;
			NodeList childList = null;
			int noOfChilds = 0;
			String childNodeName = "";
			String sql = "";
			Node childNode = null;
			GenericUtility genericUtility = GenericUtility.getInstance();
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat sdf;
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			fromDate = checkNull(genericUtility.getColumnValue("from_date",headerDom));
			System.out.println( "*********FROMDATE="+fromDate);
			toDate = checkNull(genericUtility.getColumnValue("to_date",headerDom));
			System.out.println( "*********TODATE="+toDate);
			transporterFrom = checkNull(genericUtility.getColumnValue("tran_code_from",headerDom));
			System.out.println( "*********TRANSPORTERFROM="+transporterFrom);
			transporterTo = checkNull(genericUtility.getColumnValue("tran_code_to",headerDom));
			System.out.println( "*********TRANSPORTERTO="+transporterTo);
			shipmentType = checkNull(genericUtility.getColumnValue("shipment_type",headerDom));
			System.out.println( "*********SHIPMENTTYPE="+shipmentType);
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);

			if ( fromDate == null || fromDate.trim().length() == 0 ) //from_date
			{
				System.out.println("*************DEF="+fromDate);
				errCode = "BLKFROMDT";
				errString = itmDBAccessEJB.getErrorString("","BLKFROMDT",userId);
				return errString; 
			} //from_date
			if ( toDate == null || toDate.trim().length() == 0 ) //to_date
			{
				System.out.println("*************GHI="+toDate);
				errCode = "BLKTODT";
				errString = itmDBAccessEJB.getErrorString("","BLKTODT",userId);
				return errString; 
			} //to_date
			
			if ( transporterFrom == null || transporterFrom.trim().length() == 0 ) //tran_code_from
			{
				System.out.println("TRANSPORTERFROM="+transporterFrom);
				errCode = "BLKTRANFR";
				errString = itmDBAccessEJB.getErrorString("","BLKTRANFR",userId);
				return errString; 
			}
			else if(transporterFrom != null || transporterFrom.trim().length() != 0)
			{
				
				if(!"00".equalsIgnoreCase(transporterFrom))
				{
					sql = " SELECT COUNT(*) FROM TRANSPORTER WHERE TRAN_CODE = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,transporterFrom);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count =  rs.getInt(1);
					}
					if(count == 0) 
					{
						errCode = "INVTRANPRT";
						errString = itmDBAccessEJB.getErrorString("","INVTRANPRT",userId);
						return errString; 
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				}
			} //end of tran_code_from
			
			
			if ( transporterTo == null || transporterTo.trim().length() == 0 ) //tran_code_to
			{
				System.out.println("TRANSPORTERTO="+transporterTo);
				errCode = "BLKTRANTO";
				errString = itmDBAccessEJB.getErrorString("","BLKTRANTO",userId);
				return errString; 
			}
			else if(transporterTo != null || transporterTo.trim().length() != 0)
			{
				if(!"ZZ".equalsIgnoreCase(transporterTo))
				{
					sql = " SELECT COUNT(*) FROM TRANSPORTER WHERE TRAN_CODE = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,transporterTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count =  rs.getInt(1);
					}
					if(count == 0) 
					{
						errCode = "INVTRANPRT";
						errString = itmDBAccessEJB.getErrorString("","INVTRANPRT",userId);
						return errString; 
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				}
			} //end of supp_code_from
			
			if ( shipmentType == null || shipmentType.trim().length() == 0 )
			{
				System.out.println("SHIPMENTTYPE="+shipmentType);
				errCode = "BLKSHIPTYP";
				errString = itmDBAccessEJB.getErrorString("","BLKSHIPTYP",userId);
				return errString; 
			}
			else if(shipmentType != null || shipmentType.trim().length() != 0)
			{
				sql = "SELECT COUNT(*) FROM GENCODES WHERE FLD_NAME = 'SHIPMENT_TYPE' AND MOD_NAME = 'W_TRANSPORTER'  AND FLD_VALUE = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,shipmentType);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =  rs.getInt(1);
				}
				if(count == 0) 
				{
					errCode = "INVSHIPTYP";
					errString = itmDBAccessEJB.getErrorString("","INVSHIPTYP",userId);
					return errString; 
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} //end of supp_code_from 

			/*	Date tempSysDate=new Date();
			int day=tempSysDate.getDate();
			int month=tempSysDate.getMonth();
			int year=tempSysDate.getYear();
			Date finalSysDate=new Date(year,month,day);  */
			int i =new java.util.Date(toDate).compareTo(new java.util.Date(fromDate));
			//	int sysDate=new java.util.Date(toDate).compareTo(finalSysDate);

			if((i==-1) )
			{	
				errCode = "INVLDATE";
				errString = itmDBAccessEJB.getErrorString("","INVLDATE",userId);
				return errString; 			
			}

			frdate =  oracleFormatDate(fromDate);
			System.out.println("FRDATE="+frdate);
			tDate =  oracleFormatDate(toDate);
			System.out.println("TDATE="+tDate);

			  // Changed by sankara on 16/08/13 for allow shipment despatch and all manual shipment Start..
			/*	getDataSql = " SELECT DISTINCT D.PTCN, S.SHIPMENT_ID, T.TRAN_NAME FROM SHIP_DOCS D, SHIPMENT S,TRANSPORTER T,CARTON_MASTER C WHERE  D.SHIPMENT_ID = S.SHIPMENT_ID " +
						     " AND S.TRAN_CODE = T.TRAN_CODE AND D.REF_SER = 'S-DSP' AND S.CONFIRMED = 'Y' AND S.CONF_DATE BETWEEN ? AND ? AND S.TRAN_CODE BETWEEN ? AND ? " +
						     " AND T.SHIPMENT_TYPE = ? AND D.PTCN = C.PTCN AND C.SF_IDENTITY IS NULL ";    
				System.out.println("**************getDataSql="+getDataSql);
				pstmt = conn.prepareStatement(getDataSql);
				pstmt.setString(1,frdate);
				pstmt.setString(2,tDate);
				pstmt.setString(3,transporterFrom);
				pstmt.setString(4,transporterTo);
				pstmt.setString(5,shipmentType);   */
					getDataSql = " SELECT DISTINCT D.PTCN, S.SHIPMENT_ID, T.TRAN_NAME FROM SHIP_DOCS D, SHIPMENT S, TRANSPORTER T, CARTON_MASTER C " +
							     " WHERE D.SHIPMENT_ID = S.SHIPMENT_ID " +
							     " AND S.TRAN_CODE = T.TRAN_CODE " +
							     " AND D.REF_SER = 'S-DSP' " +
							     " AND S.CONFIRMED = 'Y'  " +
							     " AND S.CONF_DATE BETWEEN  ? AND  ? " +
							     " AND S.TRAN_CODE BETWEEN  ? AND  ? " + 
							     " AND T.SHIPMENT_TYPE = ? " +
							     " AND D.PTCN = C.PTCN " + 
							     " AND C.SF_IDENTITY IS NULL " +
							     " AND T.SERVICE_CODE IS NOT NULL " +
							     " AND C.TRACKING_NO IS NOT NULL " +        
							     " UNION " +                                  // MANUAL SHIPMENT  REF SER = "" AND NON INVENTORY
							     " SELECT DISTINCT D.PTCN, S.SHIPMENT_ID, T.TRAN_NAME FROM SHIP_DOCS D, SHIPMENT_CONTENT P, "+
							     " SHIPMENT S, TRANSPORTER T, CARTON_MASTER C " +
							     " WHERE D.SHIPMENT_ID <> S.SHIPMENT_ID " + 
							     " AND S.SHIPMENT_ID = P.SHIPMENT_ID " +
							     " AND S.TRAN_CODE = T.TRAN_CODE " +
							     " AND S.CONFIRMED = 'Y' " +
							     " AND S.CONF_DATE BETWEEN  ?  AND  ? " + 
							     " AND S.TRAN_CODE BETWEEN  ? AND  ? " +
							     " AND T.SHIPMENT_TYPE = ? " +
							     " AND C.SF_IDENTITY IS NULL " + 
							     " AND T.SERVICE_CODE IS NOT NULL " +
							     " AND C.TRACKING_NO IS NOT NULL " +
							     " AND D.PTCN IS NULL " +
     						     // changed by sankara on 17/08/13 added join form carton_master for manual ship get data.  
							     " AND P.CARTON_NO = C.CARTON_NO " +
							     " UNION " +                              //MANUAL SHIPMENT  REF SER = "" AND INVENTORY 
							     " SELECT DISTINCT D.PTCN, S.SHIPMENT_ID, T.TRAN_NAME FROM SHIP_DOCS D, SHIPMENT_CONTENT P, "+
							     " SHIPMENT S, TRANSPORTER T, CARTON_MASTER C " +
							     " WHERE D.SHIPMENT_ID = S.SHIPMENT_ID " + 
							     " AND S.SHIPMENT_ID = P.SHIPMENT_ID " +
							     " AND D.SHIPMENT_ID = P.SHIPMENT_ID " +
							     " AND S.TRAN_CODE = T.TRAN_CODE " +
							     " AND S.CONFIRMED = 'Y' " +
							     " AND S.CONF_DATE BETWEEN  ?  AND  ? " +
							     " AND S.TRAN_CODE BETWEEN  ? AND  ? " +
							     " AND D.PTCN IS NULL " +
							     " AND T.SHIPMENT_TYPE = ? " +
							     " AND C.SF_IDENTITY IS NULL " + 
							     " AND T.SERVICE_CODE IS NOT NULL " +
								  // changed by sankara on 17/08/13 added join form carton_master for manual ship get data.  
							     " AND P.CARTON_NO = C.CARTON_NO " +
							     " AND C.TRACKING_NO IS NOT NULL " ; 
			  
				System.out.println("**************getDataSql="+getDataSql);
				pstmt = conn.prepareStatement(getDataSql);
				pstmt.setString(1,frdate);
				pstmt.setString(2,tDate);
				pstmt.setString(3,transporterFrom);
				pstmt.setString(4,transporterTo);
				pstmt.setString(5,shipmentType);
				pstmt.setString(6,frdate);
				pstmt.setString(7,tDate);
				pstmt.setString(8,transporterFrom);
				pstmt.setString(9,transporterTo);
				pstmt.setString(10,shipmentType);
				pstmt.setString(11,frdate);
				pstmt.setString(12,tDate);
				pstmt.setString(13,transporterFrom);
				pstmt.setString(14,transporterTo);
				pstmt.setString(15,shipmentType);
			// Changed by sankara on 16/08/13 for allow shipment despatch and all manual shipment.end	
			rs = pstmt.executeQuery();
			rsflag=false;
			while(rs.next())
			{
				rsflag=true;
				test.append( "<Detail2>\r\n" );  
				ptcn = checkNull(rs.getString(1));
				test.append( "<ptcn><![CDATA[" ).append(ptcn).append( "]]></ptcn>\r\n" );
				test.append( "<shipment_id><![CDATA[" ).append(rs.getString(2)).append( "]]></shipment_id>\r\n" );
				test.append( "<tran_name><![CDATA[" ).append(rs.getString(3)).append( "]]></tran_name>\r\n" );
				test.append("\n");
				test.append( "</Detail2>\r\n" );
			}
			test.append( "</Header0>\r\n</group0>\r\n</DocumentRoot>\r\n" );
			resultString=test.toString();

			if(rsflag == false)
			{
				errCode = "INVLDATA";
				errString = itmDBAccessEJB.getErrorString("","INVLDATA",userId);
				return errString;
			}

			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}	

		catch(Exception e)
		{
			System.out.println("Exception occured while getData()");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if(rs != null) 
					{ 
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
				conn = null;	
			}
			catch(Exception ex)
			{
				System.out.println("Exception occured while getData()");
				ex.printStackTrace();
			}
		}
		return resultString;	
	} //get data method closed
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("*******************Testing Process Method**********************");
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{  	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}	
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		ResultSet rs = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;				
		PreparedStatement pstmt = null,pstmt1 = null;	
		String updateSql=null;
		String sql=null;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		String childNodeName = null;
		String ptcn = "";
		String shipmentList []={};
		String shipmentId = "";
		String transporterName = "";
		String tranId = "";
		// changed by sankara on 17/08/13 define variables for manual ship start..	
		ResultSet rs1 = null;
		String refser = "";
		// changed by sankara on 17/08/13 define variables for manual ship end..		
		HashMap carrMap = new HashMap();
		//	Set shipmentSet =  new java.util.TreeSet();
		Set shipmentSet =  new java.util.HashSet();
		int count = 0;
		int tcnt= 0;
		String errCode = "";
		String errString = "";
		boolean isError = false;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	

		try 
		{
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);

			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{				
				parentNode = parentNodeList.item(selectedRow);				
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);

				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName---->>> "+ childNodeName);
					
					if("ptcn".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{	
							ptcn = checkNull(childNode.getFirstChild().getNodeValue());
						}	
					}
					else if("shipment_id".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{	
							shipmentId = checkNull(childNode.getFirstChild().getNodeValue());
						}	
					}
					else if("tran_name".equalsIgnoreCase(childNodeName))
					{
						if ( childNode != null && childNode.getFirstChild() != null )
						{	
							transporterName = checkNull(childNode.getFirstChild().getNodeValue());
						}	
					}			

					shipmentSet.add(shipmentId);
				}
				
			}

			System.out.println("INSIDE IF");  
			DistCommon discommon = new DistCommon();
			itmDBAccessEJB = new ITMDBAccessEJB();
			String adsiShippingURL = "",imagePath = "";
			ADSIShippingLocator locator = null;
			ADSIShippingSoap  soap = null;
			String PTCN = "";
			String carrierFriendlyName = "";
			String shipperName = "";
			java.util.Date date = new Date();
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(date);
			SimpleDateFormat format = new SimpleDateFormat("M/d/yyyy");
			String strSmple = format.format(calendar.getTime());
			adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
			System.out.println("ADSISHIPPINGURL="+adsiShippingURL);
			long timeBefore = System.currentTimeMillis();
			locator = new ADSIShippingLocator();
			System.out.println(" Connecting ADSI server ................");		
			
			if(shipmentSet != null && shipmentSet.size() >0)
			{
				soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
			}
			long timeAfter = System.currentTimeMillis();
			

			if(soap == null || !soap.testADSIServer())
			{
				errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
				return errString;
			}
			
			System.out.println(" is server active ["+soap.testADSIServer()+"]"); 
			
			Iterator it = shipmentSet.iterator();
			ArrayList shipList = new ArrayList();

			while(it.hasNext())
			{
				shipmentId = (String)it.next();
				if(shipmentId != null && shipmentId.trim().length() > 0 )
				{
					// changed by sankara on 29-06-13 added sql for getting shipment type
				
				

					// changed by sankara on 29-06-13 add join and sf_idenity	
					//sql = " SELECT CARRIER_FRIENDLYNAME FROM CARTON_MASTER WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?) ORDER BY CARRIER_FRIENDLYNAME  "; 
					//sql = " SELECT CARRIER_FRIENDLYNAME FROM CARTON_MASTER WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?) AND SF_IDENTITY IS NULL ORDER BY CARRIER_FRIENDLYNAME  ";
					/*sql = "	SELECT DISTINCT C.CARRIER_FRIENDLYNAME,T.SHIPMENT_TYPE FROM CARTON_MASTER C,SHIP_DOCS S,SHIPMENT P,TRANSPORTER T WHERE" +
						//  Changed by sankara on 16-08-13 ptcn is not available in manual shipment case...
						//	" S.PTCN =C.PTCN AND S.SHIPMENT_ID = ? AND S.SHIPMENT_ID = P.SHIPMENT_ID 	AND P.TRAN_CODE = T.TRAN_CODE AND C.CARRIER_FRIENDLYNAME" +
						 "  S.SHIPMENT_ID = ? AND S.SHIPMENT_ID = P.SHIPMENT_ID AND P.TRAN_CODE = T.TRAN_CODE AND C.CARRIER_FRIENDLYNAME " +
							  " IS NOT NULL ORDER BY C.CARRIER_FRIENDLYNAME ";*/
					sql = " SELECT DISTINCT C.CARRIER_FRIENDLYNAME, T.SHIPMENT_TYPE FROM CARTON_MASTER C, SHIPMENT_CONTENT N, SHIPMENT P, TRANSPORTER T " +
					  " WHERE P.SHIPMENT_ID = ? AND P.SHIPMENT_ID = N.SHIPMENT_ID AND P.TRAN_CODE = T.TRAN_CODE AND C.CARRIER_FRIENDLYNAME IS NOT NULL " +
				      " UNION " +
					  " SELECT DISTINCT C.CARRIER_FRIENDLYNAME, T.SHIPMENT_TYPE FROM CARTON_MASTER C, SHIP_DOCS S, SHIPMENT P, TRANSPORTER T WHERE S.PTCN = C.PTCN " + 
					  " AND S.SHIPMENT_ID = ? AND S.SHIPMENT_ID = P.SHIPMENT_ID AND S.REF_SER = 'S-DSP' AND P.TRAN_CODE = T.TRAN_CODE AND C.CARRIER_FRIENDLYNAME IS NOT NULL " ;
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,shipmentId);
					pstmt.setString(2,shipmentId);
					rs = pstmt.executeQuery();  
					if( rs.next())
					{
						carrierFriendlyName = rs.getString("CARRIER_FRIENDLYNAME");	   	
						shipperName = rs.getString("SHIPMENT_TYPE");
						
						if(!carrMap.containsKey(carrierFriendlyName + ":" + shipperName))
						{
							shipList = new ArrayList();
												
						}
						else
						{
							shipList = (ArrayList)carrMap.get(carrierFriendlyName + ":" + shipperName);
						}
						shipList.add(shipmentId);
						carrMap.put(carrierFriendlyName + ":" + shipperName, shipList);
					}
				}
			
				
			}
		
			
			// changed by sankara on 29-06-13 add join and sf_idenity		
			//	updateSql= " UPDATE CARTON_MASTER SET SF_IDENTITY = ? WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?)";
			// changed by sankara on 17/08/13  for updation of sf_idenity in manual ship case start..
			/*	// changed by sankara on 29-06-13 add join and sf_idenity		
				// updateSql= " UPDATE CARTON_MASTER SET SF_IDENTITY = ? WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?)";
				   updateSql= " UPDATE CARTON_MASTER SET SF_IDENTITY = ? WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?) AND SF_IDENTITY IS NULL";
				   pstmt = conn.prepareStatement(updateSql);   */
				sql = " SELECT PTCN, REF_SER FROM SHIP_DOCS D, SHIPMENT S WHERE S.SHIPMENT_ID = D.SHIPMENT_ID AND D.SHIPMENT_ID = ? " ;		
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,shipmentId);
				rs1 = pstmt1.executeQuery();  
				if( rs1.next())
				{
				     ptcn = rs1.getString("PTCN");
				     refser = rs1.getString("REF_SER");
				}
				if(pstmt1 != null) 
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if(rs1 != null) 
				{
					rs1.close();
					rs1 = null;
				}
				if( ptcn !=null && refser.trim().equalsIgnoreCase("S-DSP") )
				{
					updateSql= " UPDATE CARTON_MASTER SET SF_IDENTITY = ? WHERE PTCN IN (SELECT PTCN FROM SHIP_DOCS WHERE SHIPMENT_ID = ?) AND SF_IDENTITY IS NULL " ;
					pstmt = conn.prepareStatement(updateSql);
				}
				else
				{
					updateSql = " UPDATE CARTON_MASTER SET SF_IDENTITY = ? WHERE CARTON_NO IN ( SELECT CARTON_NO FROM SHIPMENT_CONTENT C, SHIP_DOCS D, SHIPMENT S " +
							    " WHERE S.SHIPMENT_ID = D.SHIPMENT_ID OR D.SHIPMENT_ID IS NULL AND S.SHIPMENT_ID = C.SHIPMENT_ID " +
							    " AND S.SHIPMENT_ID = ? ) AND SF_IDENTITY IS NULL " ;
					pstmt = conn.prepareStatement(updateSql);
				}
			// changed by sankara on 17//08/13  for updation of sf_idenity in manual ship case end..	
						
			if(carrMap != null && carrMap.size() > 0)
			{
				
				Set keySet = carrMap.keySet();
				
				Iterator itCarrFnd = keySet.iterator();
				String carrFndName = "";
				String closeShipperName = "";
				ArrayList tempList = null;
				String shipmentIDs = "";
				String key = "";
				int tmpListLen = 0;
				String  sfIdentity = "";
				
				while(itCarrFnd.hasNext())
				{
					key = (String)itCarrFnd.next();
					tempList = (ArrayList)carrMap.get(key);
					tmpListLen = tempList.size();
					
					carrFndName = key.substring(0, key.indexOf(":"));
					closeShipperName = key.substring(key.indexOf(":") + 1,key.length());
					
					System.out.println("Getting Sfidentity For Carriers services with ----["+carrFndName+"]--Shiiper Name-["+closeShipperName+"] Close Out Date["+strSmple+"]");
				
					//StringDictionaryReturn testStr = soap.closeOutEOD("Taro Commercial", carrFndName,strSmple, true, "Shipments");
					StringDictionaryReturn testStr = soap.closeOutEOD(closeShipperName, carrFndName,strSmple, true, "Shipments");
					
					System.out.println("isSuccess["+testStr.isSuccess()+"]");  
					System.out.println("Any Message["+testStr.getMessage()+"]");
					
										
					if(testStr.isSuccess())
					{		
						System.out.println("testStr.getResponseDictionary().get_any()"+testStr.getResponseDictionary().get_any());
						
						if(testStr.getResponseDictionary().get_any() != null)
						{
							int len1 = testStr.getResponseDictionary().get_any().length;
							
                       
							System.out.println("Len Response recived from ADSI"+len1);
							
							for ( int cCTR =0 ; cCTR <len1; cCTR++ )
							{

								System.out.println("Counter ["+cCTR+"]");
								System.out.println("Name["+testStr.getResponseDictionary().get_any()[cCTR]);

								if(testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(0).getFirstChild()!= null)
								{
									System.out.println("ColumnName["+testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(0).getFirstChild().getNodeValue()+"]");
								}
								if(testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(1).getFirstChild()!= null)
								{

									System.out.println("Value["+testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(1).getFirstChild().getNodeValue()+"]");

								}  
								
								if(testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(0).getFirstChild()!= null && testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(0).getFirstChild().getNodeValue().equalsIgnoreCase("SF_Identity"))
								{

									if(testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(1).getFirstChild() != null)
									{
										sfIdentity = testStr.getResponseDictionary().get_any()[cCTR].getElementsByTagName("string").item(1).getFirstChild().getNodeValue();
									}
								
								} 

							} 
						} 
						else
						{
							System.out.println("Need To Generate Erro as The Close OUT EOD Nothing to  return for carrier ["+carrFndName+"]");
						}
					}
					else
					{
						isError = false;
						errString =getError(testStr.getMessage(),"ADSCLOSEOD", conn);
						return errString;
					}
					
					System.out.println("Carrier Name ----["+carrFndName+"]----sfIdentity["+sfIdentity+"]");
					
					for(int tmpCtr = 0;tmpCtr < tmpListLen; tmpCtr++)
					{
						shipmentIDs = (String)tempList.get(tmpCtr);
						
						pstmt.setString(1,sfIdentity);
						pstmt.setString(2,shipmentIDs);
				
						count = pstmt.executeUpdate();	
						
						if(count > 0)
						{
							System.out.println("Update count ["+count+"]");
	                    }  
						pstmt.clearParameters();
					}
					
					sfIdentity = "";
				}
				
			}	
			
			if(pstmt!=null)
			{
				pstmt.close();
				pstmt = null;   
			}  	
			
			if(!isError)
			{
				System.out.println("******UPDATEDCOUNT="+count);
				errCode = "PRCESSFUL";
				errString = itmDBAccessEJB.getErrorString("","PRCESSFUL",userId);
			} 
			
		}

		catch (Exception e)
		{
			isError = true;
			try 
			{
				conn.rollback();
			}
			catch (Exception e1) 
			{
				System.out.println("Gem Message as "+e.getCause());
				System.out.println("Complete Messag by Sankara  "+e.getMessage());
				e1.printStackTrace();
			}
			e.printStackTrace();
			System.out.println("******Exception"+e.getMessage());
		}
		finally
		{
			try
			{
				if(!isError)
				{						
					conn.commit();					
				}
				else
				{
					conn.rollback();
				}
				if(conn != null)
				{
					if(rs != null) 
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					if(pstmt1 != null) 
					{
						pstmt1.close();
						pstmt1 = null;
					}
					//changed by sankara on 17/08/13 for closing result set start..
					if(rs1 != null) 
					{
						rs1.close();
						rs1 = null;
					}
					//changed by sankara on 17/08/13 for closing result set end..

					conn.close();
				}
				conn = null;	
			}
			catch (Exception ex)
			{
				isError = true;
				System.out.println("Exception is "+ex.getMessage());
			}
		}
		return errString;
	} //process method
	/*	public String getAdsiConnect(String ptcn, Connection conn) throws ITMException, Exception
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String errString = "";
		StringDictionaryReturn testStr =null;
    	HashMap hm = new HashMap();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{		 
			//For Adsi start
			DistCommon discommon = new DistCommon();
			itmDBAccessEJB = new ITMDBAccessEJB();
			String adsiShippingURL = "",imagePath = "";
			ADSIShippingLocator locator = null;
			ADSIShippingSoap  soap = null;
			String PTCN = "";
			String carrierFriendlyName = "";
			java.util.Date date = new Date();
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(date);
			SimpleDateFormat format = new SimpleDateFormat("M/d/yyyy");
			String strSmple = format.format(calendar.getTime());
			adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
			System.out.println("ADSISHIPPINGURL="+adsiShippingURL);
			long timeBefore = System.currentTimeMillis();
			locator = new ADSIShippingLocator();
			System.out.println(" Connecting ADSI server ................");		
			soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
			long timeAfter = System.currentTimeMillis();
			System.out.println(" is server active ["+soap.testADSIServer()+"]"); 

			if(!soap.testADSIServer())
			{

				errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
				return errString;
			}   		

			sql = " SELECT CARRIER_FRIENDLY_NAME FROM CARTON_MASTER WHERE PTCN = ? "; 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			rs = pstmt.executeQuery();    
			while( rs.next())
			{
				carrierFriendlyName = rs.getString("CARRIER_FRIENDLYNAME");
				System.out.println("Carrier FriendlyName["+carrierFriendlyName+"]");   
				 testStr = soap.closeOutEOD("Taro Commercial", carrierFriendlyName,strSmple, false, "");
				System.out.println("Message for Close Out["+testStr.getMessage()+"]");
				System.out.println("GET RESPONSE FROM ADSI["+testStr.getResponseDictionary() );
				System.out.println("isSuccess["+testStr.isSuccess()+"]");

				if(testStr.isSuccess())
				{

				}
				else
				{
					errString =getError(testStr.getMessage(),"ADSCLOSEOD", conn);
					return errString;
				}
				rs.close();
				rs = null;			
				pstmt.close();
				pstmt = null;	

			}
		}	
		catch(Exception ex)
		{
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;

				}
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}

			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}	
		return testStr.toString(); 	 	 
	}   */

	private  String getError(String message,String Code,Connection conn)  throws ITMException, Exception
	{
		String mainStr ="";

		try
		{
			String errString = "";
			errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Invalid Data"+"</message><description>";
			mainStr= mainStr+"ADSI ="+message+""+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return mainStr;
	}	
	public String oracleFormatDate(String dateStr) throws Exception
	{
		System.out.println("CONVERTED ORACLE DATE");
		String strMonth = "";
		String convertedDate = "";
		strMonth = dateStr.substring(0, 2);    
		//System.out.println("strMonth = " + strMonth);
		if (strMonth.equalsIgnoreCase("01")) 
		{
			strMonth = "JAN";
		}
		else if (strMonth.equalsIgnoreCase("02"))
		{
			strMonth = "FEB";
		}
		else if (strMonth.equalsIgnoreCase("03"))
		{
			strMonth = "MAR";
		}
		else if (strMonth.equalsIgnoreCase("04"))
		{
			strMonth = "APR";
		} 
		else if (strMonth.equalsIgnoreCase("05"))
		{
			strMonth = "MAY";
		} 
		else if (strMonth.equalsIgnoreCase("06"))
		{
			strMonth = "JUN";
		}
		else if (strMonth.equalsIgnoreCase("07"))
		{
			strMonth = "JUL";
		}
		else if (strMonth.equalsIgnoreCase("08"))
		{
			strMonth = "AUG";
		}
		else if (strMonth.equalsIgnoreCase("09"))
		{
			strMonth = "SEP";
		} 
		else if (strMonth.equalsIgnoreCase("10"))
		{
			strMonth = "OCT";
		}
		else if (strMonth.equalsIgnoreCase("11"))
		{
			strMonth = "NOV";
		}
		else if (strMonth.equalsIgnoreCase("12"))
		{
			strMonth = "DEC";
		}
		convertedDate = dateStr.substring(3, 5) + "-" + strMonth + "-"+ dateStr.substring(6);
		System.out.println("Given Date Into ORACLE formate = " + convertedDate);		
		return convertedDate;
	} // oracleFormatDate()
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}

} // class closed







