/*
 develop by Ritesh 
 purpose - generate confirm  sale order from receipt and despatch order
 */

package ibase.webitm.ejb.dis;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import org.apache.axis.client.Service;
import org.apache.axis.client.Call;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;
import javax.xml.rpc.ParameterMode;
import ibase.webitm.ejb.dis.DistCommon;

@Stateless
public class GenerateSaleOrderPrc extends ProcessEJB implements GenerateSaleOrderPrcLocal,GenerateSaleOrderPrcRemote 
{

	static
	{
		System.out.println("-- GenerateSaleOrderPrc called -- ");
	}
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{

		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		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 :SoHoldRefPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	
	} //end of confirm method
	public String process(Document dom, Document dom2, String windowName,String xtraParams) throws RemoteException, ITMException 
	{

		System.out.println("process called........");
		String sql = "";
		String errString = "";
		String siteCode = "";
		String  tranId = "",custCode="";
		int tranIdFound=0,cusCodeFound=0;
		Connection conn = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		System.out.println("tran id = "+tranId);
		try
		{
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			tranId = checkNull(genericUtility.getColumnValue("tran_id", dom));
			custCode = checkNull(genericUtility.getColumnValue("cust_code", dom));
			conn.setAutoCommit(false);
			if(custCode.trim() !=null && custCode.trim().length() >0)
			{
				sql = "select site_code from porcp where tran_id =  ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					siteCode = rs.getString("site_code");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				sql = "select count(*) from site_customer where cust_code = ? and channel_partner = 'Y' and dis_link = 'A' and site_code = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,custCode);
				pstmt.setString(2,siteCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cusCodeFound = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if(cusCodeFound == 0)
				{
					sql = "select count(*) from customer where cust_code = ? and channel_partner = 'Y' and dis_link = 'A' ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,custCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						cusCodeFound = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(cusCodeFound == 0)
					{
						System.out.println("--cust_code validate--");
						errString = itmDBAccessEJB.getErrorString("","VTCSCDNTFD ","","",conn);
						return errString;
					}
					
				}
			}else
			{
				errString = itmDBAccessEJB.getErrorString("","VTCSCDNTNL ","","",conn);
				return errString;
			}
			if(tranId.trim() !=null && tranId.trim().length() >0)
			{
				sql = "select count(*) from porcp where tran_id = ?";
				//sql = "select a.sale_order,b.tran_id from sorder a, porcp b where a.sale_order = ?  and  b.tran_id  = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					tranIdFound = rs.getInt(1);
					//agreementValue = rs.getDouble("max_order_value");
				} 
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if(tranIdFound > 0)
				{
					errString = generateSaleOrder(tranId,custCode, xtraParams,conn);
					System.out.println("err String from generateSaleOrder ="+errString);
					if((errString != null ) &&  errString.indexOf("Success") > -1)
					{
						System.out.println("--order despatch successfully--");
						errString = itmDBAccessEJB.getErrorString("","VTPRCSUCC","","",conn);
						return errString;
					}
					else 
					{
						conn.rollback();
						System.out.println("--order not despatch--");
						errString = itmDBAccessEJB.getErrorString("","VTPRCNTSUC ","","",conn);
						return errString;
					}
				}
				else
				{
					System.out.println("Tran id not found");
					errString = itmDBAccessEJB.getErrorString("","VTTNNTFD    ","","",conn);
					return errString;
				}
			}
			else
			{
				errString = itmDBAccessEJB.getErrorString("","VTTNNTNL    ","","",conn);
				return errString;
			}
		}
		catch( Exception e)
		{			
				try 
				{
					conn.rollback();
				} 
				catch (SQLException ex) 
				{
					Logger.getLogger(GenerateSaleOrderPrc.class.getName()).log(Level.SEVERE, null, ex);
				}
				e.printStackTrace();
		}
		finally
		{		
				try
				{
					if(errString != null && errString.trim().length() > 0)
					{
						
						if(errString.indexOf("VTPRCSUCC") > -1 )
							
						{
							conn.commit();
							System.out.println("--transaction commited--");
						}
						else
						{
							conn.rollback();
							System.out.println("--transaction rollback--");
						}
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;					
					}		
					if( conn != null && ! conn.isClosed() )
					{
						conn.close();
						conn = null;
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			}
		return errString;
	}
	private String generateSaleOrder(String tranId,String custCode, String xtraParams, Connection conn) throws RemoteException, ITMException
	{	
		String  userId = "";
		String  termId = "";
		String  orderType = ""; 
		String  remarks = "";
		String  projCode = "";
		String  sql = "";
		String  siteCode = "";	
		String  empCode = "";
		String  crTerm = "";
		String  currCodeFrt = "";
		String  currCodeIns = "";
		String	quntyStduom = "";
		String	exchRate1 = "";
		String	itemSer  = "";
		String	currencyCode = "";
		String	transMode = "";
		String	stanCode="";
		String	itemCode = "";
		String	quantity ="";
		String	rate="";
		String	unit="";
		String	conQtyStd ="";
		String	unitRate="";
		String	taxClass="";
		String	taxChap="";
		String	taxEnv = "",purcOrder = "";
		String	packCode ="",convRtuomStduom="",rateStduom="";
		String lineno = "",noArt = "",netAmt = "";
		String pin = "", stateCode = "",city = "",countCode = "",unitStd = "";
		Date orderDt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double exchRate =0,rateClg = 0;
		int lnno = 0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = null;
	
		StringBuffer xmlBuff = null;
		String xmlString = null,retString  = null;
		try   
		{
				itmDBAccessEJB = new ITMDBAccessEJB();
				System.out.println("@@@@@@@ generateSaleOrder  function called");
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
				//SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
				///today =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
				//DistCommon distCommom = new DistCommon();
				//ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
				userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
				termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); System.out.println("--term id--"+termId);
				sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, userId);
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					empCode = rs.getString("EMP_CODE")== null ?" ":rs.getString("EMP_CODE");
				}

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

				sql = "select tran_type,tran_date,item_ser,site_code,curr_code,exch_rate,tran_code," +
					  "trans_mode,curr_code__frt,curr_code__ins,purc_order from porcp where tran_id = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					orderType = rs.getString("tran_type") == null ? " " : rs.getString("tran_type");
					orderDt = rs.getDate("tran_date") ;
					siteCode = rs.getString("SITE_CODE") == null ? " " : rs.getString("SITE_CODE")  ;
					itemSer = rs.getString("item_ser")== null ? " " : rs.getString("item_ser");
					currencyCode = rs.getString("curr_code") == null ? " " : rs.getString("curr_code");
					exchRate1 = rs.getString("exch_rate") == null ? " " : rs.getString("exch_rate");
					transMode = rs.getString("trans_mode") == null ? "R " : rs.getString("trans_mode");
					currCodeFrt = rs.getString("curr_code__frt") == null ? " " : rs.getString("curr_code__frt");
					currCodeIns = rs.getString("curr_code__ins") == null ? " " : rs.getString("curr_code__ins");
					purcOrder = rs.getString("purc_order") == null ? " " : rs.getString("purc_order");

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

				sql ="select cr_term,stan_code ,city,count_code,pin,state_code from customer where cust_code = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,custCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					crTerm = rs.getString("cr_term")== null ?" ":rs.getString("cr_term");
					stanCode = rs.getString("stan_code")==null ?" ":rs.getString("stan_code");
					city = rs.getString("city")== null ?" ":rs.getString("city");
					countCode = rs.getString("count_code")== null ?" ":rs.getString("count_code");
					pin = rs.getString("pin")== null ?" ":rs.getString("pin");
					stateCode = rs.getString("state_code")== null ?" ":rs.getString("state_code");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				
			    System.out.printf("data --------"+orderType,orderDt,siteCode,projCode,currencyCode,exchRate,remarks);
				xmlBuff = new StringBuffer();
				System.out.println("--XML CREATION --");
				xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
				xmlBuff.append("<DocumentRoot>");
				xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
				xmlBuff.append("<group0>");
				xmlBuff.append("<description>").append("Group0 description").append("</description>");
				xmlBuff.append("<Header0>");
				xmlBuff.append("<objName><![CDATA[").append("sorder").append("]]></objName>");  
				xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
				xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
				xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
				xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
				xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
				xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
				xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
				xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
				xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
				xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
				xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
				xmlBuff.append("<description>").append("Header0 members").append("</description>");	
				xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"sorder\" objContext=\"1\">");  
				xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuff.append("<sale_order/>");
				xmlBuff.append("<order_type><![CDATA["+ orderType +"]]></order_type>");
				xmlBuff.append("<order_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></order_date>");
				xmlBuff.append("<item_ser><![CDATA["+ itemSer.trim()   +"]]></item_ser>");
				xmlBuff.append("<site_code><![CDATA["+ siteCode.trim() +"]]></site_code>");
				xmlBuff.append("<site_code__ship><![CDATA["+ siteCode.trim() +"]]></site_code__ship>");
				xmlBuff.append("<cust_code><![CDATA["+ custCode.trim() +"]]></cust_code>");
				xmlBuff.append("<cust_code__bil><![CDATA["+ custCode.trim() +"]]></cust_code__bil>");
				xmlBuff.append("<cust_code__dlv><![CDATA["+ custCode.trim() +"]]></cust_code__dlv>");
				xmlBuff.append("<stan_code><![CDATA["+ stanCode.trim() +"]]></stan_code>");
				xmlBuff.append("<cr_term><![CDATA["+ crTerm +"]]></cr_term>");
				xmlBuff.append("<curr_code__frt><![CDATA["+ currencyCode.trim() +"]]></curr_code__frt>");
				xmlBuff.append("<trans_mode><![CDATA["+ transMode.trim() +"]]></trans_mode>");
				xmlBuff.append("<emp_code__ord><![CDATA["+ empCode.trim() +"]]></emp_code__ord>");
				xmlBuff.append("<curr_code__frt><![CDATA["+ currCodeFrt.trim() +"]]></curr_code__frt>");
				xmlBuff.append("<curr_code__ins><![CDATA["+ currCodeIns.trim() +"]]></curr_code__ins>");
				xmlBuff.append("<curr_code><![CDATA["+ currencyCode.trim() +"]]></curr_code>");
				xmlBuff.append("<exch_rate><![CDATA["+  exchRate1 +"]]></exch_rate>");// CAN NOT BE NULL
				xmlBuff.append("<exch_rate__comm><![CDATA["+  exchRate1 +"]]></exch_rate__comm>");	
				xmlBuff.append("<exch_rate__comm_1><![CDATA["+ exchRate1 +"]]></exch_rate__comm_1>");
				xmlBuff.append("<exch_rate__comm_2><![CDATA["+ exchRate1 +"]]></exch_rate__comm_2>");
				xmlBuff.append("<tax_class><![CDATA["+taxClass.trim() +"]]></tax_class>");
				xmlBuff.append("<tax_chap><![CDATA["+taxChap.trim() +"]]></tax_chap>");
				xmlBuff.append("<tax_env><![CDATA["+ taxEnv.trim()+"]]></tax_env>");
				xmlBuff.append("<dlv_city><![CDATA["+ city +"]]></dlv_city>");
				xmlBuff.append("<dlv_pin><![CDATA["+ pin +"]]></dlv_pin>");
				xmlBuff.append("<state_code__dlv><![CDATA["+ stateCode +"]]></state_code__dlv>");
				xmlBuff.append("<count_code__dlv><![CDATA["+ countCode +"]]></count_code__dlv>");
				xmlBuff.append("<tran_id__porcp><![CDATA["+ tranId +"]]></tran_id__porcp>");
				xmlBuff.append("<status_remarks><![CDATA["+ " generate from receipt  "+tranId +"]]></status_remarks>");
				xmlBuff.append("<CHG_USER><![CDATA["+ userId +"]]></CHG_USER>");
				xmlBuff.append("<CHG_TERM><![CDATA["+ termId +"]]></CHG_TERM>");
				xmlBuff.append("<purc_order><![CDATA["+ purcOrder +"]]></purc_order>");
				xmlBuff.append("</Detail1>");
				sql = "select line_no,item_code,quantity,rate,unit,conv__qty_stduom,unit__rate,tax_class,tax_chap,tax_env,pack_code,rate__clg,quantity__stduom,unit__std," +
						"no_art,conv__rtuom_stduom,rate__stduom,NET_AMT from porcpdet where tran_id = ? order by line_no";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				rs = pstmt.executeQuery();
			    while( rs.next() )
			    {
					lineno =  rs.getString("line_no");	
					unitStd = rs.getString("unit__std") == null ? " " : rs.getString("unit__std");	
					itemCode = rs.getString("item_code") == null ? " " : rs.getString("item_code");	
					quantity = rs.getString("quantity") == null ? "0" : rs.getString("quantity")  ;
					rate = rs.getString("rate")== null ? " " : rs.getString("rate");
					unit = rs.getString("unit") == null ? " " : rs.getString("unit");
					conQtyStd = rs.getString("conv__qty_stduom") == null ? " " : rs.getString("conv__qty_stduom");
					unitRate = rs.getString("unit__rate") == null ? " " : rs.getString("unit__rate");
					taxClass = rs.getString("tax_class") == null ? " " : rs.getString("tax_class");
					taxChap = rs.getString("tax_chap") == null ? " " : rs.getString("tax_chap");
					taxEnv = rs.getString("tax_env") == null ? " " : rs.getString("tax_env");
					packCode = rs.getString("pack_code") == null ? " " : rs.getString("pack_code");
					rateClg = rs.getDouble("rate__clg");
					netAmt =  rs.getString("NET_AMT");
					noArt =  rs.getString("no_art");
					rateStduom =  rs.getString("rate__stduom");	
					convRtuomStduom =  rs.getString("conv__rtuom_stduom");	
					quntyStduom = rs.getString("quantity__stduom") == null ? "0" : rs.getString("quantity__stduom");
					
					xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"sorder\" objContext=\"2\">"); 
					xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
					xmlBuff.append("<sale_order/>");
					xmlBuff.append("<remarks><![CDATA["+ "generated from receipt "+tranId+"]]></remarks>");
					xmlBuff.append("<rate><![CDATA["+ exchRate1 +"]]></rate>");
				    xmlBuff.append("<line_no><![CDATA["+ lineno +"]]></line_no>");
					xmlBuff.append("<item_code__ord><![CDATA["+ itemCode.trim() +"]]></item_code__ord>");
					xmlBuff.append("<item_code><![CDATA["+ itemCode.trim() +"]]></item_code>");
					xmlBuff.append("<rate><![CDATA["+rate +"]]></rate>");
					xmlBuff.append("<unit><![CDATA["+ unit+"]]></unit>");
					xmlBuff.append("<conv__qty_stduom><![CDATA["+ conQtyStd +"]]></conv__qty_stduom>");
					xmlBuff.append("<unit__rate><![CDATA["+ unitRate +"]]></unit__rate>");
					xmlBuff.append("<tax_class><![CDATA["+taxClass.trim() +"]]></tax_class>");
					xmlBuff.append("<tax_chap><![CDATA["+taxChap.trim() +"]]></tax_chap>");
					xmlBuff.append("<tax_env><![CDATA["+ taxEnv.trim() +"]]></tax_env>");
					xmlBuff.append("<pack_code><![CDATA["+ packCode +"]]></pack_code>");
					xmlBuff.append("<quantity><![CDATA["+ quantity.trim() +"]]></quantity>");
					xmlBuff.append("<unit__std><![CDATA["+ unitStd +"]]></unit__std>");
					xmlBuff.append("<net_amt><![CDATA["+ netAmt +"]]></net_amt>");

					if(rateClg != 0)
					{
						xmlBuff.append("<rate__clg><![CDATA["+ rateClg +"]]></rate__clg>");
					}else
					{
						xmlBuff.append("<rate__clg><![CDATA["+ rate +"]]></rate__clg>"); // SHOULD BE GREATER THAN ZERO
					}
					xmlBuff.append("<item_flg><![CDATA["+ "I" +"]]></item_flg>"); // CAN NOT BE NULL
					xmlBuff.append("<quantity__stduom><![CDATA["+ quntyStduom+"]]></quantity__stduom>");
					xmlBuff.append("<CHG_USER><![CDATA["+ userId +"]]></CHG_USER>");
					xmlBuff.append("<CHG_TERM><![CDATA["+ termId +"]]></CHG_TERM>");
					xmlBuff.append("<no_art><![CDATA["+ noArt +"]]></no_art>");
					xmlBuff.append("<rate__stduom><![CDATA["+ rateStduom +"]]></rate__stduom>");
					xmlBuff.append("<conv__rtuom_stduom><![CDATA["+ convRtuomStduom +"]]></conv__rtuom_stduom>");
					xmlBuff.append("</Detail2>");
			    }
			    lnno = 0;
			    rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				xmlString = xmlBuff.toString();
				System.out.println("@@@@@2: xmlString:"+xmlBuff.toString());
				System.out.println("...............just before savdata()");
				siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
				System.out.println("== site code =="+siteCode);
				retString = saveData(siteCode,xmlString,conn);
				System.out.println("@@@@@2: retString:"+retString);
				System.out.println("--retString finished--");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("@@@@@@3: retString"+retString);
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");
					String tranIdForIssue = arrayForTranId[1].substring(0,endIndex);
					System.out.println("-tranIdForIssue-"+tranIdForIssue);	
					sql = " update sorder set purc_order = ? where sale_order = ? ";
					pstmt  = conn.prepareStatement(sql);
					pstmt.setString(1,purcOrder);
					pstmt.setString(2,tranIdForIssue);
					int rows = pstmt.executeUpdate();System.out.println("updated rows =="+rows);
					conn.commit();
					retString = confirmSaleOrder("sorder",tranIdForIssue,xtraParams,conn);
					System.out.println("retString from generateSaleOrder conf ::"+retString);
					if((retString != null ) &&  retString.indexOf("success") > -1)
					{
						System.out.println( "confirm sale order generate and confirm successfully ...");
						if(tranIdForIssue != null)
						{
							retString = despatchOrder(tranIdForIssue,custCode,tranId, xtraParams,conn);
							System.out.println("ret String from despatchOrder ="+retString);
							return retString;							
						}
					}				
				}
				else
				{
					System.out.println("[" + retString + "]");	
					return retString;
				}		
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result [" + retString + "]");
		return retString;
	}
	private String despatchOrder(String saleOrder,String custCode,String tranId, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		String userId = "", termId = "";
		String sql = "";
		String siteCode = "";	
		String currCodedlv  = "";
		String lnNoSord = "";
		String currencyCode = "";
		String currCodeFrt="";
		String currCodeIns ="";
		String quntyStduom="";
		String transMode = "";
		String lotSl = "";
		String lotNo = "";
		String explLev = "";
		String stanCode="";
		String exchRate1="";
		String netTotAmt = "";	
		String itemCode = "";
		String quantity ="";
		String rate="";
		String unit="";
		String conQtyStd ="";
		String unitRate="";
		String taxClass="";
		String taxChap="";
		String taxEnv = "";
		String packCode ="";
		String siteCodeDet ="",convRtuomStduom="";
		String locCode = "",rateStduom="",netAmt="";
		String varValue = "",unitStd = "",noArt = "";
		Date orderDt = null;
		PreparedStatement pstmt = null,pstmt1 =null;
		ResultSet rs = null,rs1 = null;
		double rateClg=0;
		int lnno = 0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		StringBuffer xmlBuff = null;
		String xmlString = null,retString  = null;
		ITMDBAccessEJB itmDBAccessLocal = null;
		DistCommon dComm = new DistCommon();
		try   
		{
				System.out.println("@@@@@@@ despatchOrder  function called");
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
				SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
				Date today =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
				System.out.println("==today=="+today);
				itmDBAccessLocal = new ITMDBAccessEJB();
				userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
				termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); System.out.println("--term id--"+termId);
				  
				sql = "select item_ser,site_code,curr_code,exch_rate,tran_code, trans_mode,curr_code__frt,curr_code__ins,cust_code__dlv," +
						" stan_code,order_date from sorder where sale_order = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,saleOrder);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					siteCode = rs.getString("site_code") == null ? " " : rs.getString("site_code")  ;
					currencyCode = rs.getString("curr_code") == null ? " " : rs.getString("curr_code");
					exchRate1 = rs.getString("exch_rate") == null ? " " : rs.getString("exch_rate");
					transMode = rs.getString("trans_mode") == null ? "R " : rs.getString("trans_mode");
					currCodeFrt = rs.getString("curr_code__frt") == null ? " " : rs.getString("curr_code__frt");
					currCodeIns = rs.getString("curr_code__ins") == null ? " " : rs.getString("curr_code__ins");
					currCodedlv = rs.getString("cust_code__dlv") == null ? " " : rs.getString("cust_code__dlv");
					stanCode = rs.getString("stan_code") == null ? " " : rs.getString("stan_code");
					orderDt = rs.getDate("order_date");
//					city = rs.getString("DLV_CITY");
//					stateCode = rs.getString("STATE_CODE__DLV");
//					countCode = rs.getString("COUNT_CODE__DLV");
//					pin = rs.getString("DLV_PIN");
//					dlvAdd1 = rs.getString("dlv_add1");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				
				sql ="select exp_lev  from sorditem where sale_order = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,saleOrder);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					explLev = rs.getString("exp_lev")==null ? " ":rs.getString("exp_lev");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				
				xmlBuff = new StringBuffer();
				System.out.println("--XML CREATION --");
				xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
				xmlBuff.append("<DocumentRoot>");
				xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
				xmlBuff.append("<group0>");
				xmlBuff.append("<description>").append("Group0 description").append("</description>");
				xmlBuff.append("<Header0>");
				xmlBuff.append("<objName><![CDATA[").append("despatch").append("]]></objName>");  
				xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
				xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
				xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
				xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
				xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
				xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
				xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
				xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
				xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
				xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
				xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
				xmlBuff.append("<description>").append("Header0 members").append("</description>");	
				xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"despatch\" objContext=\"1\">");  
				xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuff.append("<desp_id/>");
				xmlBuff.append("<desp_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></desp_date>");
				xmlBuff.append("<sord_date><![CDATA["+ sdf.format(orderDt).toString() +"]]></sord_date>");
				xmlBuff.append("<sord_no><![CDATA["+ saleOrder.trim() +"]]></sord_no>");
				xmlBuff.append("<cust_code><![CDATA["+ custCode.trim() +"]]></cust_code>");
				xmlBuff.append("<cust_code__dlv><![CDATA["+ custCode.trim() +"]]></cust_code__dlv>");
				xmlBuff.append("<stan_code><![CDATA["+ stanCode.trim() +"]]></stan_code>");
				xmlBuff.append("<trans_mode><![CDATA["+ transMode.trim() +"]]></trans_mode>");
				xmlBuff.append("<curr_code__frt><![CDATA["+ currCodeFrt.trim() +"]]></curr_code__frt>");
				xmlBuff.append("<curr_code__ins><![CDATA["+ currCodeIns.trim() +"]]></curr_code__ins>");
				xmlBuff.append("<curr_code><![CDATA["+ currencyCode.trim() +"]]></curr_code>");
				xmlBuff.append("<exch_rate><![CDATA["+  exchRate1 +"]]></exch_rate>");// CAN NOT BE NULL
				xmlBuff.append("<exch_rate__frt><![CDATA["+  exchRate1 +"]]></exch_rate__frt>");	
				xmlBuff.append("<exch_rate__ins><![CDATA["+ exchRate1 +"]]></exch_rate__ins>");
				xmlBuff.append("<cust_code__dlv><![CDATA["+ currCodedlv +"]]></cust_code__dlv>");
				xmlBuff.append("<site_code><![CDATA["+ siteCode.trim()+"]]></site_code>");
				xmlBuff.append("<remarks><![CDATA["+ " generate from sale order  "+ saleOrder +"]]></remarks>");				
				xmlBuff.append("<CHG_USER><![CDATA["+ userId +"]]></CHG_USER>");
				xmlBuff.append("<CHG_TERM><![CDATA["+ termId +"]]></CHG_TERM>");
				xmlBuff.append("</Detail1>");
				
				sql = "select line_no,item_code,quantity,rate,unit,conv__qty_stduom,unit__rate,tax_class,tax_chap,tax_env,pack_code,rate__clg,quantity__stduom," +
						"net_tot_amt,net_amt,site_code,unit__std,no_art,rate__stduom,conv__rtuom_stduom from sorddet where sale_order = ? order by line_no";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,saleOrder);
				rs = pstmt.executeQuery();
			   while( rs.next() )
			   {
					lnNoSord = rs.getString("line_no");	
					itemCode = rs.getString("item_code") == null ? " " : rs.getString("item_code");	
					quantity = rs.getString("quantity") == null ? "0" : rs.getString("quantity")  ;
					rate = rs.getString("rate")== null ? " " : rs.getString("rate");
					unit = rs.getString("unit") == null ? " " : rs.getString("unit");
					conQtyStd = rs.getString("conv__qty_stduom") == null ? " " : rs.getString("conv__qty_stduom");
					unitRate = rs.getString("unit__rate") == null ? " " : rs.getString("unit__rate");
					taxClass = rs.getString("tax_class") == null ? " " : rs.getString("tax_class");
					taxChap = rs.getString("tax_chap") == null ? " " : rs.getString("tax_chap");
					taxEnv = rs.getString("tax_env") == null ? " " : rs.getString("tax_env");
					packCode = rs.getString("pack_code") == null ? " " : rs.getString("pack_code");
					rateClg = rs.getDouble("rate__clg");
					quntyStduom = rs.getString("quantity__stduom") == null ? "0" : rs.getString("quantity__stduom");
					netTotAmt = rs.getString("net_tot_amt") == null ? " " : rs.getString("net_tot_amt");
					siteCodeDet = rs.getString("site_code") == null ? " " : rs.getString("site_code");	
					unitStd = rs.getString("unit__std") == null ? " " : rs.getString("unit__std");	
					noArt = rs.getString("no_art");	
					rateStduom = rs.getString("rate__stduom");	
					convRtuomStduom = rs.getString("conv__rtuom_stduom");	
					netAmt = rs.getString("net_amt");	

					sql ="select lot_sl,lot_no,loc_code  from porcpdet where tran_id = ? and line_no = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,tranId);
					pstmt1.setString(2,lnNoSord);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						lotSl = rs1.getString("lot_sl")==null ? " ":rs1.getString("lot_sl");
						lotNo = rs1.getString("lot_no")==null ? " ":rs1.getString("lot_no");
						locCode = rs1.getString("loc_code")==null ? " ":rs1.getString("loc_code");
	
					}
					rs1.close();
					rs1 =null;
					pstmt1.close();
					pstmt1 = null;
				
			    	lnno ++;
			    	System.out.println("--LINE NO--"+lnno);
					xmlBuff.append("<Detail2 dbID='' domID=\"1\" objName=\"despatch\" objContext=\"2\">"); 
					xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
					xmlBuff.append("<desp_id/>");
					xmlBuff.append("<sord_no><![CDATA["+ saleOrder.trim() +"]]></sord_no>");
					xmlBuff.append("<line_no><![CDATA["+ lnno +"]]></line_no>");					
					xmlBuff.append("<line_no__sord><![CDATA["+ lnNoSord +"]]></line_no__sord>");					
					xmlBuff.append("<lot_no><![CDATA["+ lotNo +"]]></lot_no>");					
					xmlBuff.append("<lot_sl><![CDATA["+ lotSl +"]]></lot_sl>");					
					xmlBuff.append("<loc_code><![CDATA["+ locCode +"]]></loc_code>");					
					xmlBuff.append("<exp_lev><![CDATA["+ explLev +"]]></exp_lev>");					
					xmlBuff.append("<remarks><![CDATA["+ "generated from sorder "+"]]></remarks>");					
					xmlBuff.append("<rate><![CDATA["+rate +"]]></rate>");					
					xmlBuff.append("<item_code__ord><![CDATA["+ itemCode.trim() +"]]></item_code__ord>");					
					xmlBuff.append("<item_code><![CDATA["+ itemCode +"]]></item_code>");					
					xmlBuff.append("<quantity><![CDATA["+ quantity.trim() +"]]></quantity>");					
					xmlBuff.append("<unit><![CDATA["+ unit+"]]></unit>");					
					xmlBuff.append("<conv__qty_stduom><![CDATA["+ conQtyStd +"]]></conv__qty_stduom>");					
					xmlBuff.append("<unit__rate><![CDATA["+ unitRate +"]]></unit__rate>");				
					xmlBuff.append("<tax_class><![CDATA["+taxClass.trim() +"]]></tax_class>");				
					xmlBuff.append("<tax_chap><![CDATA["+taxChap +"]]></tax_chap>");				
					xmlBuff.append("<tax_env><![CDATA["+ taxEnv+"]]></tax_env>");					
					xmlBuff.append("<rate__clg><![CDATA["+ rateClg +"]]></rate__clg>");					
					xmlBuff.append("<tot_net_amt><![CDATA["+ netTotAmt.trim()+"]]></tot_net_amt>");					
					xmlBuff.append("<site_code><![CDATA["+ siteCodeDet.trim()+"]]></site_code>");					
					xmlBuff.append("<quantity__stduom><![CDATA["+ quntyStduom.trim()+"]]></quantity__stduom>");					
					xmlBuff.append("<pack_code><![CDATA["+ packCode.trim()+"]]></pack_code>");
					xmlBuff.append("<unit__std><![CDATA["+ unitStd+"]]></unit__std>");
					xmlBuff.append("<CHG_USER><![CDATA["+ userId +"]]></CHG_USER>");
					xmlBuff.append("<CHG_TERM><![CDATA["+ termId +"]]></CHG_TERM>");
					xmlBuff.append("<no_art><![CDATA["+ noArt +"]]></no_art>");
					xmlBuff.append("<rate__stduom><![CDATA["+ rateStduom +"]]></rate__stduom>");
					xmlBuff.append("<conv__rtuom_stduom><![CDATA["+ convRtuomStduom +"]]></conv__rtuom_stduom>");
					xmlBuff.append("<tot_net_amt><![CDATA["+ netAmt +"]]></tot_net_amt>");
					xmlBuff.append("<quantity__inv><![CDATA["+ quantity +"]]></quantity__inv>");
					xmlBuff.append("<quantity__real><![CDATA["+ quantity +"]]></quantity__real>");
					xmlBuff.append("</Detail2>");
			    }
			    lnno = 0;
			    rs.close();
			    rs = null;
			    pstmt.close();
			    pstmt = null;
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				xmlString = xmlBuff.toString();
				System.out.println("@@@@@2: xmlString  for despatch:"+xmlBuff.toString());
				System.out.println("...............just before savdata() for despatch");
				siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
				System.out.println("==site code =="+siteCode);
				retString = saveData(siteCode,xmlString,conn);
				System.out.println("@@@@@2: retString from despatch:"+retString);
				System.out.println("--retString finished from despatch--");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("@@@@@@3: retString from despatch"+retString);
					conn.commit();
					varValue = dComm.getDisparams("999999","AUTO_CONF_DESP", conn); 
					System.out.println("varValue --"+varValue);
					if("Y".equalsIgnoreCase(varValue.trim()))
					{
						String[] arrayForTranId = retString.split("<TranID>");
						int endIndex = arrayForTranId[1].indexOf("</TranID>");
						String tranIdForDesp = arrayForTranId[1].substring(0,endIndex);
						System.out.println("-tranIdForDesp-"+tranIdForDesp);	
						retString = confirmSaleOrder("despatch",tranIdForDesp,xtraParams,conn);
						System.out.println("retString from conf from despatch ::"+retString);
					}
				}
				else
				{
					System.out.println("[" + retString + "]");	
					return retString;
				}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result [" + retString + "]");
		return retString;
	}
	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	private String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			System.out.println("--retString - -"+retString);
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}
	public String confirmSaleOrder(String businessObj, String tranIdFr,String xtraParams, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println("confirmSaleOrder(String businessObj, String tranIdFr,String xtraParams, String forcedFlag, Connection conn) called >>><!@#>");

		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");

			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;

			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 FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,serviceCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			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);
			aobj[1] = new String(tranIdFr);
			aobj[2] = new String(xtraParams);
			//aobj[3] = new String(forcedFlag);
			//System.out.println("@@@@@@@@@@loginEmpCode:" +genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode")+":");
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);
			retString = (String)call.invoke(aobj);
			System.out.println("Confirm Complete @@@@@@@@@@@ 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;
	}
}