/********************************************************
	Title : ShipmentConf
	Date  : 01/06/2012
	Developer: Mahesh Patidar
 ********************************************************/

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;
import javax.xml.rpc.ParameterMode;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.apache.poi.hssf.util.HSSFColor.RED;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
//Changed by sumit on 06/10/12
import org.w3c.dom.*;

import com.adsionline.ADSIShippingWS.ADSIShippingLocator;
import com.adsionline.ADSIShippingWS.ADSIShippingSoap;
import com.adsionline.ADSIShippingWS.StringDictionaryReturn;


@Stateless
public class ShipmentConf extends ActionHandlerEJB implements ShipmentConfLocal, ShipmentConfRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	public String confirm(String tranId, String xtraParam, String forcedFlag) throws RemoteException,ITMException
	{
		int cnt = 0;
		String errString = "";
		String sql = "";
		String refId = "",proNo = "",tranProNo = "";
		String lorryNo = "";
		String tranCode = "";
		String lrNo = "";
		//Changed by sumit on 28/09/12 
		String confirmed = "";
		String newErro = "";
		//Changed by sumit on 05/10/12 created ref_ser variable 
		//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] adding variable for CHK_DIGIT_YN
		String chDigitYn = "";
		String refSer = "", refSerNew = "";
		int noOfPallet = 0, count = 0;
		ArrayList<String> errList = new ArrayList<String>();
		Timestamp lrDate = null;
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		PreparedStatement pstmt1 = null;
		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			String userID = genericUtility.getValueFromXTRA_PARAMS( xtraParam, "LoginCode" );
			//Changed by sumit on 28/09/12 checking whether shipment is already confirmed or not start.
			System.out.println(" forced flag ["+forcedFlag+"]");
			ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
			sql = "SELECT SHIPMENT.CONFIRMED, SHIPMENT.NO_PALLET, SHIP_DOCS.REF_SER " +
					" FROM SHIPMENT, SHIP_DOCS WHERE SHIPMENT.SHIPMENT_ID = ? " +
					" AND SHIP_DOCS.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				confirmed = rs.getString("CONFIRMED");
				noOfPallet = rs.getInt("NO_PALLET");
				refSer = checkNull(rs.getString("REF_SER"));
			}
			System.out.println("-- confirmed ["+confirmed+"] noOfPallet ["+noOfPallet+"] refSer ["+refSer.trim()+"]");
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			if( "Y".equalsIgnoreCase(confirmed))
			{
				errString = itmDBAccessLocal.getErrorString("","SHIPALLCON","");
			}
			else
			{
				if( "S-DSP".equalsIgnoreCase(refSer.trim()) && "N".equalsIgnoreCase(forcedFlag))
				{
					errString = itmDBAccessLocal.getErrorString("","SDSPCONFIR","");
				}
				else
				{
					sql = "SELECT COUNT(*) as COUNT FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						count = rs.getInt("COUNT");
					}
					rs.close();rs = null;
					pstmt.close();pstmt = null;
					if( noOfPallet != count && !("S-DSP".equalsIgnoreCase(refSer.trim())))
					{
						errString = itmDBAccessLocal.getErrorString("","NOOFPALLET","");
					}
					else
					{
						//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment start.
						if( updateShipment(tranId , conn, xtraParam) > 0)
						{
							System.out.println(" update sucessfully ");
						}
						//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment end.
						
						//Changed by sumit on 28/09/12 checking whether shipment is already confirmed or not end.					
						sql = "SELECT LORRY_NO,TRAN_CODE,LR_NO,LR_DATE, pro_no FROM SHIPMENT WHERE SHIPMENT_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							lorryNo = rs.getString(1);
							tranCode = rs.getString(2);
							lrNo = rs.getString(3);
							lrDate = rs.getTimestamp(4);
							proNo = rs.getString(5);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
			
						//Changed by sumit on 28/09/12 wrong sql
						//sql = "SELECT PRONO_LASTe FROM transporter WHERE tran_code = ? ";
						//sql = "SELECT PRONO_LAST FROM transporter WHERE tran_code = ? ";
						//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] adding columns
						sql = "SELECT PRONO_LAST,CHK_DIGIT_YN FROM transporter WHERE tran_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							tranProNo = rs.getString(1);
							//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] getting CHK_DIGIT_YN
							chDigitYn = rs.getString("CHK_DIGIT_YN");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						//Changed by sumit on 05/10/12 added condition where ref_ser = S-DSP
						//sql = "SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ?";
						sql = "SELECT REF_ID, REF_SER FROM SHIP_DOCS WHERE SHIPMENT_ID = ? AND DESP_CONFIRMED = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);						
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							refId = rs.getString(1);
							//Changed by sumit on 05/10/12 getting value of refser
							refSerNew = checkNull(rs.getString("REF_SER"));
							
							System.out.println("ref_id " +refId+" refSer: ["+refSerNew.trim()+"]");
							//Changed by sumit on 05/10/12 add condition do not call despatch confirmed other than despatch
							//if(refId != null && refId.trim().length() > 0 )
							if(refId != null && refId.trim().length() > 0 && "S-DSP".equalsIgnoreCase(refSerNew.trim()))
							{
								
								
								System.out.println("after commit in ShipmentConf for id ["+ refId +"]");
								errString = confirmShipment("despatch", refId.trim(), xtraParam, forcedFlag, conn);
								
								//Changed by sumit on 05/10/12 updating ship_docs  start.
								System.out.println(" errString ---------------- > ["+errString);		
								
								//Changed by sumit on 05/10/12 updating desp_confirmed in ship docs table
								/*if ( newErro == null || newErro.trim().length() == 0)
								{*/
									updateShipDocs(refId, findError(errString));
								//}
								
								//errList.add(errString);
								// check digit to be removed from pro_no before update
								//if(proNo.length() > tranProNo.length())
								//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] commenting this, as not required for despatch start
								/*if((tranProNo != null && proNo != null) && proNo.length() > tranProNo.length())
								{
									proNo = proNo.substring(0,tranProNo.length());
								}*/
								//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] commenting this, as not required for despatch end
									
								sql = "UPDATE DESPATCH SET LORRY_NO = ?, SHIPMENT_ID = ?, TRAN_CODE = ?, LR_NO  = ?, LR_DATE = ?, pro_no = ? WHERE DESP_ID = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lorryNo);
								pstmt1.setString(2, tranId);
								pstmt1.setString(3, tranCode);
								pstmt1.setString(4, lrNo);
								pstmt1.setTimestamp(5, lrDate);
								pstmt1.setString(6, proNo);
								pstmt1.setString(7, refId);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								//conn.commit();
								
								//changed by sumit on 12/04/13 of Req>ID [WM3ASUN007] updating lr number in invoice against desp_id start.
								sql = "UPDATE INVOICE SET LR_NO  = ? WHERE DESP_ID = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lrNo);
								pstmt1.setString(2, refId);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								
								//Removing check digit number from pro_no and then updating in PRONO_LAST of TRANSPORTER.
								
								if((tranProNo != null && proNo != null) && proNo.length() > tranProNo.length())
								{
									proNo = proNo.substring(0,tranProNo.length());
								}
								else if( tranProNo == null && "Y".equalsIgnoreCase(chDigitYn))
								{
									proNo = proNo.substring(0,proNo.length() - 1);
								}
								System.out.println(" pro_no ["+proNo+"]");
								//changed by sumit on 12/04/13 of Req>ID [WM3ASUN007] updating lr number in invoice against desp_id end.
								
								sql = "UPDATE TRANSPORTER SET PRONO_LAST  = ? WHERE TRAN_CODE = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, proNo);
								pstmt1.setString(2, tranCode);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								//conn.commit();								
									
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						//Changed by sumit on 05/10/12 adding condition to check whether all despatch are confirmed or not 
						if( isCountSame(tranId, conn) || !("S-DSP".equalsIgnoreCase(refSer.trim())))
						{
							sql = "UPDATE SHIPMENT SET CONFIRMED = ? , CONF_DATE = ? WHERE SHIPMENT_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, "Y");
							pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
							pstmt.setString(3, tranId);
							pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							conn.commit();
							
							//For Adsi start
							DistCommon discommon = new DistCommon();
							ITMDBAccessEJB 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);
							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 DISTINCT S.PTCN,G.CARRIER_FRIENDLYNAME FROM SHIP_DOCS S,WAVE_STATUS_ORG G WHERE S.SHIPMENT_ID = ? AND  G.PTCN = S.PTCN";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							while( rs.next())
							{
								PTCN = rs.getString("PTCN");
								carrierFriendlyName = rs.getString("CARRIER_FRIENDLYNAME");
								System.out.println("PTCN["+PTCN+"]Carrier FriendlyName["+carrierFriendlyName+"]");
								StringDictionaryReturn testStr = soap.closeOutEOD("Taro Commercial", carrierFriendlyName,strSmple, false, "");
					    		
								System.out.println("Message for Close Out["+testStr.getMessage()+"]");
								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;	
				    		
							
							//For Adsi end
							
							
							
							//Changed by sumit on 28/09/12 commented and written above.
							//ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
							errString = itmDBAccessLocal.getErrorString("","VTCONFIRM","");
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			try{
				conn.rollback();
			}
			catch(SQLException es)
			{
				System.out.println("Rollback exception : " + es.getMessage() + ":");
			}
			System.out.println("Exception : Shipment Confirm():" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("!! Closing Connection in Shipment Confirm() !!");
			try
			{
				conn.close();
				conn = null;
			}
			catch(Exception se){
				System.out.println(se.getMessage());
				throw new ITMException(se);
			}
		}
		return errString;
	}

	
	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;
    }
	
	private String confirmShipment(String businessObj, String tranIdFr,String xtraParams, String forcedFlag , Connection conn) throws ITMException
	{
		String methodName = "gbf_post";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		//Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		System.out.println("confirmShipment");
		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");

			//methodName = "";
			actionURI = "http://NvoServiceurl.org/" + methodName;
                           System.out.println(businessObj+"---"+tranIdFr+"---"+xtraParams+"---"+forcedFlag);

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,businessObj);
			rs = pStmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			System.out.println("serviceCode = "+serviceCode+" compName "+compName);
			sql = "SELECT SERVICE_URI,METHOD_NAME FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,serviceCode);
			rs = pStmt.executeQuery();
			if ( rs.next() )//serviceURI = http://192.168.0.124/BaseE12DotNetService6-3-0-003/E12BusinessLogic6-3-0-003.asmx compName = nvo_business_object_dist_despatch   
			{
				serviceURI = rs.getString("SERVICE_URI");
                                methodName=rs.getString("METHOD_NAME");
			}
			System.out.println("serviceURI = "+serviceURI+" compName = "+compName);
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName.trim());
			aobj[1] = new String(tranIdFr.trim());
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);

			call.setReturnType(XMLType.XSD_STRING);

			retString = (String)call.invoke(aobj);

			System.out.println("Return string from NVO is:==>["+retString+"]");

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pStmt != null )
				{
					pStmt.close();
					pStmt = null;
				}
				 /*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
                            System.out.println("Exception inCalling confirmed");
                            e.printStackTrace();
                            try{
                            conn.rollback();

                            }catch (Exception s)
                            {
                                System.out.println("Unable to rollback");
                                s.printStackTrace();
                            }
            				throw new ITMException(e);
                        }
		}
		return retString;
	}
	//Changed by sumit on 02/12/12 for checking null start.
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	//Changed by sumit on 02/12/12 for checking null end.
	
	//Changed by sumit on 05/10/12 retrieving error string start.
	private HashMap findError(String errorString) throws ITMException
	{
		Document errorDom = null;
		NodeList parentNodeList = null;
		NodeList childList = null;
		int parentNodeListLength ;
		String description = "";
		Element elementAttr = null;
		Map error =  new HashMap();
		String columnValue ="",nodeName= "",errType = "";
		try
		{
			if(errorString != null && errorString.trim().length()!=0)
			{
				errorDom = genericUtility.parseString(errorString); 
			}
			
			parentNodeList = errorDom.getElementsByTagName("error");
			parentNodeListLength = parentNodeList.getLength();	
			
			for(int row = 0; row < parentNodeListLength; row++)
			{
				Node childNode = parentNodeList.item(row);
				nodeName = childNode.getNodeName();
				//elementAttr = (Element)parentNode;
				errType = childNode.getAttributes().getNamedItem("type").getNodeValue();
				System.out.println(" error type ->["+errType+"]");
				if( "E".equalsIgnoreCase(errType))
				{
					description = checkNull(GenericUtility.getInstance().getColumnValueFromNode("description", errorDom.getElementsByTagName("error").item(row)));
				}				
			}	
			description = description.replace("\"", " ");
			description = description.replace("'", "");
			error.put("type", errType);
			error.put("description", description);
			System.out.println(" error description - > ["+error+"]");				
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return (HashMap) error;
	}
	//Changed by sumit on 05/10/12 retrieving error string end.
	//Changed by sumit on 05/10/12 counting ship detail start.
	private boolean isCountSame(String shipId, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0, count1 = 0;
		boolean isSame = false;
		try
		{
			
			sql = " SELECT COUNT(*) AS COUNT FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count = rs.getInt("COUNT");
			}
			rs.close();rs = null;			
			pstmt.close();pstmt = null;	
			
			sql = " SELECT COUNT(*) AS COUNT FROM SHIP_DOCS WHERE SHIPMENT_ID = ? AND DESP_CONFIRMED = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipId);
			pstmt.setString(2, "Y");		
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count1 = rs.getInt("COUNT");
			}
			rs.close();rs = null;			
			pstmt.close();pstmt = null;	
			if (count == count1)
			{
				isSame = true;
			}
			else
			{
				isSame = false;
			}
			System.out.println(" isSame ->["+isSame+"]");
		}		
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try {
				
				if (rs != null) 
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		return isSame;
	}
	//Changed by sumit on 05/10/12 counting ship detail end.
	
		
	
	//Changed by sumit on 05/10/12 update desp_confirmed in ship_docs ship detail start.
	private String updateShipDocs ( String refId, HashMap error) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt1 = null, pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = null;
		try
		{
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			sql = " UPDATE SHIP_DOCS SET DESP_CONFIRMED = ?, ERROR_MSG = ? WHERE REF_ID = ? ";
			pstmt1 = conn.prepareStatement(sql);
			 if( "E".equals(error.get("type").toString()))
			 {
				pstmt1.setString(1, "N");
				pstmt1.setString(2, error.get("description").toString());
				pstmt1.setString(3, refId);
			 }
			 else
			 {
				//Changed by sumit on 20/12/12 updating wave_task_det after despatch get confirmed start.
				sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' WHERE REF_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refId);
				if(pstmt.executeUpdate() > 0 )
				{
					System.out.println(" wave_status of wave_task_det updated successfully");
				}
				if( pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
				//Changed by sumit on 20/12/12 updating wave_task_det after despatch get confirmed end.
				pstmt1.setString(1, "Y");
				pstmt1.setString(2, "");
				pstmt1.setString(3, refId);
			 }
			pstmt1.executeUpdate();
			pstmt1.close();
			pstmt1 = null;
			conn.commit();
		}
		catch( Exception e)
		{
			try
			{
                conn.rollback();                
			}
			catch (Exception s)
            {
                System.out.println("Unable to rollback");
                s.printStackTrace();
            }
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if ( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if( conn != null)
				{
					conn.close();
					conn = null;
				}
			} catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		
		return null;
	}
	//Changed by sumit on 05/10/12 update desp_confirmed in ship_docs ship detail end.
	
	//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment start
	private int updateShipment(String shipmentId, Connection conn, String xtraParams) throws ITMException
	{
		String sql = "";
		String descr = "";
		String descr1 = "";		
		String prefix = "";
		String sProno = "";
		String lrNo = "";
		String tranCode = "";
		long pronoLast = 0;
		int update = 0;		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println(" sumit inside update shipment ");
		try
		{
			
			sql = "SELECT TRAN_CODE FROM SHIPMENT WHERE SHIPMENT_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipmentId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				tranCode = rs.getString("TRAN_CODE");				
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}				
			
			sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX FROM transporter WHERE tran_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranCode);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				descr = rs.getString(1);
				descr1 = rs.getString(2);
				prefix = rs.getString(4);
				if(descr1 == null || descr1.trim().length() == 0)
				{
					descr1 = checkNull(rs.getString(3));
				}
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}			
		
			if(descr1.trim().length() > 0)
			{
				pronoLast = Long.parseLong(descr1) + 1;				
			}
			if(pronoLast != 0)
			{
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( pronoLast ).append( "]]></lr_no>\r\n" ); // commented to generate SSCC18 complaint number
		
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></lr_no>\r\n" ); // added to generate SSCC18 complaint number //commented as per suggestion   
					sProno = ("" + pronoLast).trim();
					System.out.println("manohar before prefix["+prefix+"]::::sProno["+sProno+"]");
					sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranCode);
					pstmt.setString(2, sProno);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						sProno = checkNull(rs.getString(1));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				System.out.println("manohar after prefix["+prefix+"]::::sProno["+sProno+"]");
				//valueXmlString.append( "<pro_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></pro_no>\r\n" );
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" ); 
				//valueXmlString.append( "<pro_no><![CDATA[" ).append( sProno ).append( "]]></pro_no>\r\n" );
				
				lrNo = getLrNo(xtraParams, conn);
				System.out.println(" --sumit-- lrno - >["+lrNo+"]");
				if (lrNo == null || lrNo.trim().length() ==0 )
				{
					lrNo = getLrNo(xtraParams,conn);
					System.out.println("manohar before lr_no ["+lrNo+"]");
					sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranCode);
					pstmt.setString(2, lrNo);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						lrNo = checkNull(rs.getString(1));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				
					System.out.println("manohar after lr_no ["+lrNo+"]");
					
					//valueXmlString.append( "<lr_no><![CDATA[" ).append( sProno  ).append( "]]></lr_no>\r\n" ); 
				}
			}
			sql = "UPDATE SHIPMENT SET LR_NO = ? , PRO_NO = ? WHERE SHIPMENT_ID = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, lrNo);
			pstmt.setString(2, sProno);
			pstmt.setString(3, shipmentId);
			update = pstmt.executeUpdate();
			if( update > 0)
			{
				System.out.println(" shipment updated sucessfully");
			}
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
						
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} 
			catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		
		return update;
	}
	//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment end
	//Changed by sumit on 10/11/12 generating LrNo ( Copied from shipmentIC, as per manoharan sir) start.
	private String getLrNo( String xtraParams,Connection conn) throws Exception
	{

		boolean isOdd = false;
		int sumOdd = 0, sumEven = 0;
		String inputStr = "", lrNo = "",  runNumberStr =""; //mfgCode = "0351672",
		//ibase.utility.GenericUtility genUtility = ibase.utility.GenericUtility.getInstance();
		int ctr, curDigit = 0, chkDigit = 0, padLen = 0; // runNumber = 4967235
		long runNumber=0;
		//TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
		String windowName="";
		PreparedStatement pSel = null;
		PreparedStatement pIns = null;	
		ResultSet rsSel = null;
	//	Connection conn = null;
		String mfgCode = null;
		String sSel = "";
		String sIns = "";
		String siteCode = "";
		String labelType = "";
		String labelID = "";
		String keyString = "";
		String tranIDCol = "";
		String refSer = "";
		String retString = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		try
		{
			if (mfgCode == null) 
			{
				mfgCode = "04";
			}
			System.out.println("@@@@@ getLrNo called..........");
			windowName = "w_bol_gen";
			sSel = "SELECT KEY_STRING, TRAN_ID_COL,REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pSel = conn.prepareStatement(sSel);
			pSel.setString(1, windowName);
			rsSel = pSel.executeQuery();
			if( rsSel.next() )
			{
				keyString = rsSel.getString( "KEY_STRING" );
				tranIDCol = rsSel.getString("TRAN_ID_COL");
				refSer = rsSel.getString("REF_SER");
			}
			//refSer="BOL-NO";
			String loginCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"site_code");
			String chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String currDate = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			StringBuffer xmlValues = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>");
			xmlValues = xmlValues.append("<Header></Header>");
			xmlValues = xmlValues.append("<Detail1>");
			xmlValues.append( "<dummy>").append("<![CDATA[]]>").append("</dummy>\r\n" );
			xmlValues.append( "<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>\r\n" );
			xmlValues.append( "<tran_date>").append("<![CDATA[" + currDate + "]]>").append("</tran_date>\r\n");
			xmlValues.append( "<loginCode><![CDATA[" ).append(loginCode).append( "]]></loginCode>\r\n" );
			xmlValues.append( "<lr_no></lr_no>\r\n");
			xmlValues.append( "<bol_no></bol_no>\r\n");
			xmlValues = xmlValues.append( "</Detail1></Root>");
			
			System.out.println("refSer:["+refSer+"]:::tranIDCol:["+tranIDCol+"]::::keyString:["+keyString+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
			
			runNumber = Long.parseLong(tg.generateTranSeqID(refSer, tranIDCol, keyString, conn));
			//Changed by sumit on 12/04/13 of Req.ID [WM3ASUN007] commenting commit from here.
			//conn.commit();
			System.out.println("runNumber:["+runNumber+"]");
	
			padLen = 16 - mfgCode.length(); // total length should be 16 excluding extension digit and check digit
			
			runNumberStr = "0000000000000000" + String.valueOf(runNumber);
			runNumberStr = runNumberStr.substring(runNumberStr.length() - padLen ,runNumberStr.length()); 
			lrNo = mfgCode + runNumberStr;
			System.out.println(  " lrNo [" + lrNo + "] lrNo.length() [" +  lrNo.length() + "]" );
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return lrNo;
	
	}
		
}
