
package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
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.Timestamp;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;

import javax.ejb.Stateless;

import org.w3c.dom.CDATASection;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class DistIssDelWiz extends ValidatorEJB implements DistIssDelWizLocal, DistIssDelWizRemote {

	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = null;
	String chgUser = null;
	String chgTerm = null;
	NumberFormat nf = null;
	boolean isError=false;



	public DistIssDelWiz() 
	{
		System.out.println("^^^^^^^ inside Distribution Issue Wizard ^^^^^^^");
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ inside Distribution Issue Wizard >^^^^^^^");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";

		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) 
			{
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			if (objContext != null && Integer.parseInt(objContext) == 1) 
			{
				parentNodeList = dom2.getElementsByTagName("Header0");
				parentNode = parentNodeList.item(1);
				childNodeList = parentNode.getChildNodes();
				for (int x = 0; x < childNodeList.getLength(); x++) 
				{
					childNode = childNodeList.item(x);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("Detail1")) 
					{
						errString = wfValData(dom, dom1, dom2, "1", editFlag, xtraParams);
						if (errString != null && errString.trim().length() > 0)
							break;
					} else if (childNodeName.equalsIgnoreCase("Detail2")) 
					{
						errString = wfValData(dom, dom1, dom2, "2", editFlag, xtraParams);
						break;
					}
				}
			} else 
			{
				errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
			}
		} catch (Exception e) {
			System.out.println("Exception : Inside DocumentMaster wfValData Method ..> " + e.getMessage());
			throw new ITMException(e);
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ inside Distribution Issue wfValData >^^^^^^^");
		GenericUtility genericUtility;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0, currentFormNo = 0, childNodeListLength = 0, cnt = 0;
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		String userId = "";
		PreparedStatement pstmt = null ;
		ResultSet rs = null;
		String sql = "";

		String distOrderIssNo = "",confirmed="",tranId="",siteCode="",lotSl="";
		try {

			System.out.println("editFlag>>>>wf"+editFlag);
			System.out.println("xtraParams>>>wf"+xtraParams);


			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			genericUtility = GenericUtility.getInstance();
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++) {


					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();

					if(childNodeName.equalsIgnoreCase("distord_issno"))
					{

						distOrderIssNo = genericUtility.getColumnValue("distord_issno",dom);

						if (distOrderIssNo == null || distOrderIssNo.trim().length() == 0)
						{
							errCode = "DISISSNULL";
							errString = getErrorString("distord_issno",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from distord_iss where tran_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,distOrderIssNo);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "DISNOTEX";
								errString = getErrorString("distord_issno",errCode,userId);
								break;
							}else
							{
								sql = "select confirmed from distord_iss where tran_id = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,distOrderIssNo);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									confirmed = rs.getString(1) == null ? "":rs.getString(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;

								if(confirmed.equals("Y"))
								{
									errCode = "DISISSCO";
									errString = getErrorString("distord_issno",errCode,userId);
									break;
								}

							}

						}

					}
				}
				break;
			case 2:


				System.out.println("DOM>>>> Elements>>["+genericUtility.serializeDom(dom).toString()+"]");
				System.out.println("DOM1>> Elements>>["+genericUtility.serializeDom(dom1).toString()+"]");
				System.out.println("DOM2>> Elements>>["+genericUtility.serializeDom(dom2).toString()+"]");	

				parentNodeList = dom2.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				System.out.println("parentNode >>>{"+parentNode+"}");
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();


				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					
				}
				break;
			case 3:

				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength+ ====>>>>>>> "+childNodeListLength);
				System.out.println("childNodeName+ ==== "+childNodeName);
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{


					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("value of child node : "+childNode);

					if(childNodeName.equalsIgnoreCase("lot_sl"))
					{
						siteCode = genericUtility.getColumnValue("site_code",dom2);
						lotSl = genericUtility.getColumnValue("lot_sl",dom2);
						distOrderIssNo = genericUtility.getColumnValue("distord_issno",dom1);

						if (lotSl == null || lotSl.trim().length() == 0)
						{
							errCode = "DIDOLSNULL";
							errString = getErrorString("lot_sl",errCode,userId);
							break;
						}
						else
						{
							
							//add validation here for check lot sl present in detail table
							sql = "select count(1) from distord_issdet where lot_sl = ? and tran_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,lotSl);
							pstmt.setString(2,distOrderIssNo);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "VTINVLOTSL";
								errString = getErrorString("lot_sl",errCode,userId);
								break;
							}
						}

	
					}
					

					
				}
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();			
			errString = e.getMessage();
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
					conn = null;
				}
				
			} catch (Exception d) {
				d.printStackTrace();
			}
		}
		return errString;
	}

	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try {
			System.out.println("currentColumn"+currentColumn);
			System.out.println("editFlag"+editFlag);
			System.out.println("xtraParams"+xtraParams);


			System.out.println("xmlString111>>"+xmlString);
			System.out.println("xmlString222>>"+xmlString1);
			System.out.println("xmlString333>>"+xmlString2);
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		} catch (Exception e) {
			System.out.println("Exception : [itemChanged(String,String)] :==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		return valueXmlString;
	}


	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException {

		StringBuffer valueXmlString = null;
		int currentFormNo = 0;
		Connection conn = null;
		double squantity = 0.0;
		PreparedStatement pstmt = null,pstmt1=null;
		ResultSet rs = null ,rs1 = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		GenericUtility genutility = new GenericUtility();
		String locCode="",lotNo="",itmdesc="",siteCode="",locdesc="";
		System.out.println("DOM111 Elements>>["+genericUtility.serializeDom(dom).toString()+"]");
		System.out.println("DOM222 Elements>>["+genericUtility.serializeDom(dom1).toString()+"]");
		System.out.println("DOM322 Elements>>["+genericUtility.serializeDom(dom2).toString()+"]");
		String distOrder = "",itemCode = "",sql="", distOrderiss = "";
		java.sql.Timestamp currDate = null;
		double grossWeight = 0,netWeight=0,tareWeight=0,noOfArt=0;
		SimpleDateFormat sdf = null;
		String currAppdate = "";
		String tranid ="";

		String tranDate = null;
		String  lotSl ="";
		try
		{   
			sdf=new SimpleDateFormat(genutility.getApplDateFormat());
			currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
			currAppdate = sdf.format(currDate);
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");

			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println("FORM NO IS"+currentFormNo);
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			switch (currentFormNo) {

			case 1 :
				break;

			case 2 : 
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");

			

				distOrderiss = genericUtility.getColumnValue("distord_issno", dom1);
				
					if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						
						
						sql =   "SELECT SITE_A.DESCR,SITE_B.DESCR,LOCATION.DESCR,DISTORD_ISS.TRAN_ID,DISTORD_ISS.TRAN_DATE,DISTORD_ISS.EFF_DATE,DISTORD_ISS.DIST_ORDER,DISTORD_ISS.SITE_CODE,DISTORD_ISS.SITE_CODE__DLV,"
								+"DISTORD_ISS.DIST_ROUTE,DISTORD_ISS.TRAN_CODE,DISTORD_ISS.LR_NO,DISTORD_ISS.LR_DATE,DISTORD_ISS.LORRY_NO,DISTORD_ISS.GROSS_WEIGHT,"
								+"DISTORD_ISS.TARE_WEIGHT,DISTORD_ISS.NET_WEIGHT,DISTORD_ISS.FRT_AMT,DISTORD_ISS.AMOUNT,DISTORD_ISS.TAX_AMT,DISTORD_ISS.NET_AMT,DISTORD_ISS.REMARKS,"
								+"DISTORD_ISS.FRT_TYPE,DISTORD_ISS.CHG_USER,DISTORD_ISS.CHG_TERM,DISTORD_ISS.CURR_CODE,DISTORD_ISS.CHG_DATE,"
								+"TRANSPORTER.TRAN_NAME,CURRENCY_A.DESCR,DISTORD_ISS.CONFIRMED,DISTORD_ISS.LOC_CODE__GIT,DISTORD_ISS.CONF_DATE,DISTORD_ISS.NO_ART,DISTORD_ISS.TRANS_MODE,"
								+"DISTORD_ISS.GP_NO,DISTORD_ISS.GP_DATE,DISTORD_ISS.CONF_PASSWD,DISTORD_ISS.ORDER_TYPE,DISTORD_ISS.GP_SER,DISTORD_ISS.REF_NO,DISTORD_ISS.REF_DATE,DISTORD_ISS.AVAILABLE_YN,"
								+"SITE_B.ADD1,SITE_B.ADD2,SITE_B.CITY,SITE_B.PIN,SITE_B.STATE_CODE,DISTORD_ISS.EXCH_RATE,DISTORD_ISS.TRAN_TYPE,DISTORD_ISS.EMP_CODE__APRV,DISTORD_ISS.DISCOUNT,DISTORD_ISS.PERMIT_NO,"
								+"DISTORD_ISS.SHIPMENT_ID,DISTORD_ISS.CURR_CODE__FRT,DISTORD_ISS.EXCH_RATE__FRT,CURRENCY_B.DESCR,DISTORD_ISS.RD_PERMIT_NO,DISTORD_ISS.DC_NO,DISTORD_ISS.TRAN_SER,DISTORD_ISS.PART_QTY,SPACE(100) "
								+"AS SUNDRY_DETAILS,SPACE(100) AS "
								+"SUNDRY_NAME,DISTORD_ISS.PROJ_CODE,SITE_B.TELE1,SITE_B.TELE2,SITE_B.TELE3,DISTORD_ISS.SITE_CODE__BIL,SITE_C.DESCR,SITE_C.ADD1,SITE_C.ADD2,SITE_C.CITY,SITE_C.PIN,SITE_C.STATE_CODE,"
								+"DISTORD_ISS.PALLET_WT,DISTORDER.AUTO_RECEIPT,DISTORD_ISS.CR_TERM,DISTORD_ISS.DLV_TERM,DISTORD_ISS.OUTSIDE_INSPECTION,DISTORD_ISS.LABEL_TYPE,DISTORD_ISS.ADD_USER,DISTORD_ISS.ADD_TERM "
								+"FROM DISTORD_ISS  DISTORD_ISS,SITE SITE_A,SITE SITE_B,LOCATION "
								+"LOCATION,TRANSPORTER  TRANSPORTER,CURRENCY CURRENCY_A,CURRENCY" 
								+" CURRENCY_B,SITE SITE_C,DISTORDER  DISTORDER WHERE ( "
								+"DISTORD_ISS.SITE_CODE = SITE_A.SITE_CODE ) AND ( "
								+"DISTORD_ISS.SITE_CODE__DLV = SITE_B.SITE_CODE ) AND ( "
								+"DISTORD_ISS.LOC_CODE__GIT = LOCATION.LOC_CODE ) AND ( "
								+"DISTORD_ISS.CURR_CODE = CURRENCY_A.CURR_CODE  ) AND ( "
								+"DISTORD_ISS.DIST_ORDER = DISTORDER.DIST_ORDER ) AND ( "
								+"DISTORD_ISS.TRAN_CODE=TRANSPORTER.TRAN_CODE(+)) AND ( "
								+"DISTORD_ISS.CURR_CODE__FRT=CURRENCY_B.CURR_CODE(+)) AND (" 
								+"DISTORD_ISS.SITE_CODE__BIL=SITE_C.SITE_CODE(+)) AND "
								+"DISTORD_ISS.TRAN_ID    = '"+distOrderiss+"'";



						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{

							valueXmlString.append("<Detail2  domID='1' objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
							valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");

							valueXmlString.append("<tran_id><![CDATA["+(rs.getString("tran_id")==null?"":rs.getString("tran_id").trim())+"]]></tran_id>");

							simpleDateFormat=new SimpleDateFormat(genutility.getApplDateFormat());
							currDate = rs.getTimestamp("tran_date");
							if(currDate != null)
							{
								currAppdate = simpleDateFormat.format(currDate);
							}
							valueXmlString.append("<tran_date><![CDATA["+currAppdate+"]]></tran_date>");
							valueXmlString.append("<eff_date><![CDATA["+currAppdate+"]]></eff_date>");
							valueXmlString.append("<dist_order><![CDATA["+(rs.getString("dist_order")==null?"":rs.getString("dist_order").trim())+"]]></dist_order>");
							valueXmlString.append("<site_code><![CDATA["+(rs.getString("site_code")==null?"":rs.getString("site_code").trim())+"]]></site_code>");
							valueXmlString.append("<site_code__dlv><![CDATA["+(rs.getString("SITE_CODE__DLV")==null?"":rs.getString("SITE_CODE__DLV").trim())+"]]></site_code__dlv>");
							valueXmlString.append("<dist_route><![CDATA["+(rs.getString("DIST_ROUTE")==null?"":rs.getString("DIST_ROUTE"))+"]]></dist_route>");
							valueXmlString.append("<tran_code><![CDATA["+(rs.getString("tran_code")==null?"":rs.getString("tran_code"))+"]]></tran_code>");
							valueXmlString.append("<lr_no><![CDATA[]]></lr_no>");
							valueXmlString.append("<lr_date><![CDATA[]]></lr_date>");
							valueXmlString.append("<lorry_no><![CDATA[]]></lorry_no>");
							valueXmlString.append("<gross_weight><![CDATA["+(rs.getDouble("gross_weight"))+"]]></gross_weight>");
							valueXmlString.append("<tare_weight><![CDATA["+(rs.getDouble("tare_weight"))+"]]></tare_weight>");
							valueXmlString.append("<net_weight><![CDATA["+(rs.getDouble("net_weight"))+"]]></net_weight>");
							valueXmlString.append("<frt_amt><![CDATA[0]]></frt_amt>");
							valueXmlString.append("<amount><![CDATA[0]]></amount>");
							valueXmlString.append("<tax_amt><![CDATA[0]]></tax_amt>");
							valueXmlString.append("<net_amt><![CDATA[0]]></net_amt>");
							valueXmlString.append("<remarks><![CDATA["+(rs.getString("remarks")==null?"":rs.getString("remarks").trim())+"]]></remarks>");
							valueXmlString.append("<frt_type><![CDATA[T]]></frt_type>");
							valueXmlString.append("<chg_user><![CDATA["+(rs.getString("CHG_USER")==null?"":rs.getString("CHG_USER").trim())+"]]></chg_user>");
							valueXmlString.append("<chg_term><![CDATA["+(rs.getString("CHG_TERM")==null?"":rs.getString("CHG_TERM").trim())+"]]></chg_term>");
							valueXmlString.append("<curr_code><![CDATA["+(rs.getString("curr_code")==null?"":rs.getString("curr_code").trim())+"]]></curr_code>");
							valueXmlString.append("<chg_date><![CDATA["+currAppdate+"]]></chg_date>");
							valueXmlString.append("<site_descr><![CDATA["+(rs.getString(1)==null?"":rs.getString(1).trim())+"]]></site_descr>");
							valueXmlString.append("<site_to_descr><![CDATA["+(rs.getString(2)==null?"":rs.getString(2).trim())+"]]></site_to_descr>");
							valueXmlString.append("<location_descr><![CDATA["+(rs.getString(3)==null?"":rs.getString(3).trim())+"]]></location_descr>");
							valueXmlString.append("<tran_name><![CDATA["+(rs.getString("tran_name")==null?"":rs.getString("tran_name"))+"]]></tran_name>");
							valueXmlString.append("<currency_descr><![CDATA[]]></currency_descr>");
							valueXmlString.append("<confirmed><![CDATA[N]]></confirmed>");
							valueXmlString.append("<loc_code__git><![CDATA["+(rs.getString("loc_code__git")==null?"":rs.getString("loc_code__git"))+"]]></loc_code__git>");
							valueXmlString.append("<conf_date><![CDATA["+currAppdate+"]]></conf_date>");
							valueXmlString.append("<no_art><![CDATA[0]]></no_art>");
							valueXmlString.append("<trans_mode><![CDATA["+(rs.getString("TRANS_MODE")==null?"":rs.getString("TRANS_MODE").trim())+"]]></trans_mode>");
							valueXmlString.append("<gp_no><![CDATA[]]></gp_no>");
							valueXmlString.append("<gp_date/>");
							valueXmlString.append("<conf_passwd/>");
							valueXmlString.append("<order_type><![CDATA["+(rs.getString("ORDER_TYPE")==null?"":rs.getString("ORDER_TYPE").trim())+"]]></order_type>");
							valueXmlString.append("<gp_ser><![CDATA[I]]></gp_ser>");
							valueXmlString.append("<ref_no><![CDATA[]]></ref_no>");
							valueXmlString.append("<ref_date><![CDATA[]]></ref_date>");
							valueXmlString.append("<available_yn><![CDATA["+(rs.getString("available_yn")==null?"N":rs.getString("available_yn").trim())+"]]></available_yn>");
							valueXmlString.append("<site_add1><![CDATA["+(rs.getString("ADD1")==null?"":rs.getString("ADD1").trim())+"]]></site_add1>");
							valueXmlString.append("<site_add2><![CDATA["+(rs.getString("ADD2")==null?"":rs.getString("ADD2").trim())+"]]></site_add2>");
							valueXmlString.append("<site_city><![CDATA["+(rs.getString("CITY")==null?"":rs.getString("CITY").trim())+"]]></site_city>");
							valueXmlString.append("<site_pin><![CDATA["+(rs.getString("PIN")==null?"":rs.getString("PIN").trim())+"]]></site_pin>");
							valueXmlString.append("<site_state_code><![CDATA["+(rs.getString("STATE_CODE")==null?"":rs.getString("STATE_CODE").trim())+"]]></site_state_code>");
							valueXmlString.append("<exch_rate><![CDATA["+(rs.getDouble("EXCH_RATE"))+"  ]]></exch_rate>");
							valueXmlString.append("<tran_type><![CDATA["+(rs.getString("TRAN_TYPE")==null?"":rs.getString("TRAN_TYPE").trim())+"]]></tran_type>");
							valueXmlString.append("<emp_code__aprv><![CDATA[]]></emp_code__aprv>");
							valueXmlString.append("<discount><![CDATA[0]]></discount>");
							valueXmlString.append("<permit_no><![CDATA[]]></permit_no>");
							valueXmlString.append("<shipment_id><![CDATA[]]></shipment_id>");
							valueXmlString.append("<curr_code__frt><![CDATA[ ]]></curr_code__frt>");
							valueXmlString.append("<exch_rate__frt><![CDATA[]]></exch_rate__frt>");
							valueXmlString.append("<currency_descr__frt><![CDATA[]]></currency_descr__frt>");
							valueXmlString.append("<rd_permit_no><![CDATA[]]></rd_permit_no>");
							valueXmlString.append("<dc_no><![CDATA[]]></dc_no>");
							valueXmlString.append("<tran_ser><![CDATA[D-ISS ]]></tran_ser>");
							valueXmlString.append("<part_qty><![CDATA[A]]></part_qty>");
							valueXmlString.append("<sundry_details><![CDATA[]]></sundry_details>");
							valueXmlString.append("<sundry_name><![CDATA["+(rs.getString("SUNDRY_NAME")==null?"":rs.getString("SUNDRY_NAME"))+"]]></sundry_name>");
							valueXmlString.append("<proj_code><![CDATA["+(rs.getString("PROJ_CODE")==null?"":rs.getString("PROJ_CODE").trim())+"]]></proj_code>");
							valueXmlString.append("<site_tele1><![CDATA[]]></site_tele1>");
							valueXmlString.append("<site_tele2><![CDATA[]]></site_tele2>");
							valueXmlString.append("<site_tele3><![CDATA[]]></site_tele3>");
							valueXmlString.append("<site_code__bil><![CDATA[]]></site_code__bil>");
							valueXmlString.append("<site_descr_bill><![CDATA[]]></site_descr_bill>");
							valueXmlString.append("<site_add1_bill><![CDATA[]]></site_add1_bill>");
							valueXmlString.append("<site_add2_bill><![CDATA[]]></site_add2_bill>");
							valueXmlString.append("<site_city_bill><![CDATA[]]></site_city_bill>");
							valueXmlString.append("<site_pin_bill><![CDATA[]]></site_pin_bill>");
							valueXmlString.append("<site_state_code_bill><![CDATA[]]></site_state_code_bill>");
							valueXmlString.append("<pallet_wt><![CDATA[]]></pallet_wt>");
							valueXmlString.append("<auto_receipt><![CDATA[N]]></auto_receipt>");
							valueXmlString.append("<add_user><![CDATA["+(rs.getString("add_user")==null?"":rs.getString("add_user").trim())+"]]></add_user>");
							valueXmlString.append("<add_term><![CDATA["+(rs.getString("add_term")==null?"":rs.getString("add_term").trim())+"]]></add_term>");
							valueXmlString.append("<add_date>").append("<![CDATA[" + currAppdate + "]]>").append("</add_date>");
							valueXmlString.append("</Detail2>");
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
					}


				break;
			case 3 : 

				System.out.println("dom :"+genericUtility.serializeDom(dom).toString());
				System.out.println("dom1 :"+genericUtility.serializeDom(dom1).toString());
				System.out.println("dom2 :"+genericUtility.serializeDom(dom2).toString());
				
				int lineNo = 0;
/*				distOrderiss = genericUtility.getColumnValue("distord_issno", dom2);
				lotSl = genericUtility.getColumnValue("lot_sl",dom);
				siteCode = genericUtility.getColumnValue("site_code", dom2,"2");*/
				
				distOrderiss = genericUtility.getColumnValue("distord_issno", dom2);
				System.out.println("dom2 distOrderiss : "+distOrderiss);	
				
				if(distOrderiss==null){
					distOrderiss = genericUtility.getColumnValue("distord_issno", dom1);
					System.out.println("dom1 distOrderiss : "+distOrderiss);
				}
				
				if(distOrderiss==null){
					distOrderiss = genericUtility.getColumnValue("distord_issno", dom);
					System.out.println("dom distOrderiss : "+distOrderiss);
				}
				
				lotSl = genericUtility.getColumnValue("lot_sl",dom);
				siteCode = genericUtility.getColumnValue("site_code", dom2,"2");
				
				System.out.println("final => distOrderiss : "+distOrderiss+"  lotSl : " +lotSl+" siteCode : "+siteCode);

				String currDomStr = genericUtility.serializeDom(dom);
				
				if(currentColumn.trim().equalsIgnoreCase("lot_sl"))
				{
					System.out.println(">>>>START>>>>");
					if(lotSl != null && lotSl.trim().length() > 0)
					{
						//item_code,no_art,quantity,amount,net_amt,discount,tax_amt,gross_weight,tare_weight,net_weight
						sql = "select LOC_CODE,LOT_NO,ITEM_CODE,QUANTITY,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,NO_ART,DIST_ORDER from distord_issdet where LOT_SL = ? and tran_id = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,lotSl);
						pstmt1.setString(2,distOrderiss);

						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							locCode =  rs1.getString("loc_code")==null?"":rs1.getString("loc_code").trim();
							lotNo = rs1.getString("lot_no")==null?"":rs1.getString("lot_no").trim();
							itemCode = rs1.getString("item_code")==null?"":rs1.getString("item_code").trim();
							squantity = rs1.getDouble("quantity");
							grossWeight = rs1.getDouble("gross_weight");
							netWeight = rs1.getDouble("net_weight");
							tareWeight = rs1.getDouble("tare_weight");
							noOfArt = rs1.getDouble("no_art");
							distOrder = rs1.getString("DIST_ORDER");
							
                        }
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;


						valueXmlString.append("<dist_order protect=\"0\">").append("<![CDATA[" + distOrder + "]]>").append("</dist_order>");
						setNodeValue( dom, "dist_order", getAbsString(""+distOrder)); 
						

						valueXmlString.append("<loc_code protect=\"0\">").append("<![CDATA[" + locCode + "]]>").append("</loc_code>");
						setNodeValue( dom, "loc_code", getAbsString(""+locCode)); 

						sql = "select descr from location where loc_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,locCode);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							locdesc =  rs1.getString("descr")==null?"":rs1.getString("descr").trim();
						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;



						valueXmlString.append("<location_descr protect=\"0\">").append("<![CDATA[" + locdesc + "]]>").append("</location_descr>");
						setNodeValue( dom, "location_descr", getAbsString(""+locdesc)); 

						valueXmlString.append("<lot_no protect=\"0\">").append("<![CDATA[" + lotNo + "]]>").append("</lot_no>");
						setNodeValue( dom, "lot_no", getAbsString(""+lotNo)); 

						sql = "select descr from item where item_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							itmdesc =  rs1.getString("descr")==null?"":rs1.getString("descr").trim();
						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;


						valueXmlString.append("<item_descr protect=\"0\">").append("<![CDATA[" + itmdesc + "]]>").append("</item_descr>");
						setNodeValue( dom, "item_descr", getAbsString(""+itmdesc)); 

						valueXmlString.append("<item_code protect=\"0\">").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						setNodeValue( dom, "item_code", getAbsString(""+itemCode)); 

						valueXmlString.append("<quantity protect=\"0\">").append("<![CDATA[" + squantity + "]]>").append("</quantity>");
						setNodeValue( dom, "quantity", getAbsString(""+squantity)); 

						valueXmlString.append("<gross_weight protect=\"0\">").append("<![CDATA[" + grossWeight + "]]>").append("</gross_weight>");
						setNodeValue( dom, "gross_weight", getAbsString(""+grossWeight)); 
						
						valueXmlString.append("<net_weight protect=\"0\">").append("<![CDATA[" + netWeight + "]]>").append("</net_weight>");
						setNodeValue( dom, "net_weight", getAbsString(""+netWeight)); 
						
						valueXmlString.append("<tare_weight protect=\"0\">").append("<![CDATA[" + tareWeight + "]]>").append("</tare_weight>");
						setNodeValue( dom, "tare_weight", getAbsString(""+tareWeight)); 
						
						valueXmlString.append("<no_art protect=\"0\">").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");
						setNodeValue( dom, "no_art", getAbsString(""+noOfArt)); 

						
						currDomStr = currDomStr.replace("</Detail3>", valueXmlString.toString() + "</Detail3>");
						System.out.println("after currDomStr[" + currDomStr + "]");
						valueXmlString.append(currDomStr);
					}
					


					System.out.println(">>>>END>>>>>>>>>");
				}
				else if(currentColumn.trim().equalsIgnoreCase("itm_default_add"))
				{
					System.out.println("distOrderiss (tranId) : "+distOrderiss);
					
					sql = "select max(line_no)  "
							+ "from distord_issdet where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,distOrderiss);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						lineNo = rs.getInt(1);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;

					lineNo++;
					
					System.out.println(">>>>>>>>>>>>> Line no in add : "+lineNo);
					
					valueXmlString.append("<Detail3 domID='" + lineNo+ "'  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
					//valueXmlString.append("<Detail3  domID='1' objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
					valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
					
					valueXmlString.append("<tran_id>").append("<![CDATA[" + distOrderiss + "]]>").append("</tran_id>");
					setNodeValue( dom, "tran_id", getAbsString("")); 
					
					valueXmlString.append("<line_no><![CDATA["+lineNo+"]]></line_no>");
					setNodeValue( dom, "line_no", getAbsString("")); 
					
					valueXmlString.append("<dist_order protect=\"0\">").append("<![CDATA[]]>").append("</dist_order>");
					setNodeValue( dom, "dist_order", getAbsString("")); 
					
					valueXmlString.append("<loc_code protect=\"0\">").append("<![CDATA[]]>").append("</loc_code>");
					setNodeValue( dom, "loc_code", getAbsString("")); 

					valueXmlString.append("<location_descr protect=\"0\">").append("<![CDATA[]]>").append("</location_descr>");
					setNodeValue( dom, "location_descr", getAbsString("")); 

					valueXmlString.append("<lot_no protect=\"0\">").append("<![CDATA[]]>").append("</lot_no>");
					setNodeValue( dom, "lot_no", getAbsString("")); 

					valueXmlString.append("<item_descr protect=\"0\">").append("<![CDATA[]]>").append("</item_descr>");
					setNodeValue( dom, "item_descr", getAbsString("")); 

					valueXmlString.append("<item_code protect=\"0\">").append("<![CDATA[]]>").append("</item_code>");
					setNodeValue( dom, "item_code", getAbsString("")); 

					valueXmlString.append("<quantity protect=\"0\">").append("<![CDATA[0]]>").append("</quantity>");
					setNodeValue( dom, "quantity", getAbsString("0")); 

					valueXmlString.append("<gross_weight protect=\"0\">").append("<![CDATA[]]>").append("</gross_weight>");
					setNodeValue( dom, "gross_weight", getAbsString("")); 

					valueXmlString.append("<net_weight protect=\"0\">").append("<![CDATA[]]>").append("</net_weight>");
					setNodeValue( dom, "net_weight", getAbsString("")); 
					
					valueXmlString.append("<tare_weight protect=\"0\">").append("<![CDATA[]]>").append("</tare_weight>");
					setNodeValue( dom, "tare_weight", getAbsString("")); 
					
					valueXmlString.append("<no_art protect=\"0\">").append("<![CDATA[]]>").append("</no_art>");
					setNodeValue( dom, "no_art", getAbsString("")); 
					
					currDomStr = currDomStr.replace("</Detail3>", valueXmlString.toString() + "</Detail3>");
					System.out.println("after currDomStr[" + currDomStr + "]");
					valueXmlString.append(currDomStr);

				}

				break;
			}

			if(("lot_sl".equalsIgnoreCase(currentColumn)))
			{
				System.out.println("CHK VAL");
				String currDomStr = genericUtility.serializeDom(dom);
				System.out.println("currDomStr[" + currDomStr + "]");
				StringBuffer valueXmlStr = new StringBuffer(currDomStr);
				System.out.println("@@@@@@@@@@@ after serialize : valueXmlStr ["+valueXmlStr+"]");
				StringBuffer valueXmlString1 = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
				valueXmlString1.append(editFlag).append("</editFlag></header>");
				valueXmlString1.append(valueXmlStr);
				valueXmlString = valueXmlString1;
			}

			valueXmlString.append("</Root>"); 
		}
		catch(Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage()); 
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			throw new ITMException(e); 
		}
		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 d)
			{
				d.printStackTrace(); 
			}
		}
		return valueXmlString.toString();
	}


	private static String getAbsString( String str )
	{
		return ( str == null || str.trim().length() == 0 || "null".equalsIgnoreCase( str.trim() ) ? "" : str.trim() );
	}

	private static void setNodeValue( Document dom, String nodeName, String nodeVal ) throws Exception
	{
		Node tempNode = dom.getElementsByTagName( nodeName ).item(0);

		if( tempNode != null )
		{
			if( tempNode.getFirstChild() == null )
			{
				CDATASection cDataSection = dom.createCDATASection( nodeVal );
				tempNode.appendChild( cDataSection );
			}
			else
			{
				tempNode.getFirstChild().setNodeValue(nodeVal);
			}
		}
		tempNode = null;
	}


	
	public String getFormatedValue(double actVal,int prec)throws RemoteException//This method is added by nisar on 11/23/2007
	{//this method is used to return double with appropriate precison
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(prec);
		String strValue = numberFormat.format(DoubleValue);
		//System.out.println(strValue);
		strValue = strValue.replaceAll(",","");
		return strValue;
	}
	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			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");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			//System.out.println("Exception in [MPSOrder] getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}
}
