/********************************************************
	Title : WorkOrdRcpDet EJB
	Date  : 28 - July - 2014
	Author: Deepak Sawant.

 ********************************************************/
package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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 WorkOrdRcpDet extends ValidatorEJB implements WorkOrdRcpDetLocal, WorkOrdRcpDetRemote {

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



	public WorkOrdRcpDet() {
		System.out.println("^^^^^^^ inside Work Order Receipt Detail ^^^^^^^");
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ inside Work Order Receipt Detail 12^^^^^^^");
		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 Work Order Receipt Detail 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 workOrder = "",squantity="",wotatus="",itemCode="";
		double quantity = 0,noOfArt=0;



		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("work_order"))
					{
						workOrder = genericUtility.getColumnValue("work_order",dom);

						if (workOrder == null || workOrder.trim().length() == 0)
						{
							errCode = "WORDWIWONU";
							errString = getErrorString("work_order",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from workorder where work_order = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "WORDWIWONE";
								errString = getErrorString("work_order",errCode,userId);
								break;
							}else
							{
								sql = "select status from workorder where work_order = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,workOrder);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									wotatus = rs.getString(1) == null ? "":rs.getString(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;

								if(!wotatus.equals("R"))
								{
									errCode = "WORDWIWOSR";
									errString = getErrorString("work_order",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();

				/*	boolean flag = false;
				parentNodeList = dom.getElementsByTagName("Detail2");
				if(parentNodeList != null)
				{
					System.out.println("for dom >>>>");
					parentNode = parentNodeList.item(0);
					if(parentNode != null)
					{
						try{
							childNodeList = parentNode.getChildNodes();
							childNodeListLength = childNodeList.getLength();
						}catch(Exception e)
						{
							e.printStackTrace();
							flag = true;
						}
					}
				}
				if(flag)
				{
					flag = false;
					parentNodeList = dom1.getElementsByTagName("Detail2");
					if(parentNodeList != null)
					{
						System.out.println("for dom 1");
						parentNode = parentNodeList.item(0);
						if(parentNode != null)
						{
							try{
								childNodeList = parentNode.getChildNodes();
								childNodeListLength = childNodeList.getLength();
							}catch(Exception e)
							{
								e.printStackTrace();
								flag = true;
							}
						}
					}
					if(flag)
					{
						System.out.println("for dom 2");
						parentNodeList = dom2.getElementsByTagName("Detail2");
						parentNode = parentNodeList.item(0);
						if(parentNode != null)
						{
							try{
								childNodeList = parentNode.getChildNodes();
								childNodeListLength = childNodeList.getLength();
							}catch(Exception e)
							{
								e.printStackTrace();
								flag = true;
							}
						}
					}
				}
				 */


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

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

					if(childNodeName.equalsIgnoreCase("work_order"))
					{
						workOrder = genericUtility.getColumnValue("work_order",dom2);

						if (workOrder == null || workOrder.trim().length() == 0)
						{
							errCode = "WORDWIWONU";
							errString = getErrorString("work_order",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from workorder where work_order = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "WORDWIWONE";
								errString = getErrorString("work_order",errCode,userId);
								break;
							}else
							{
								sql = "select status from workorder where work_order = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,workOrder);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									wotatus = rs.getString(1) == null ? "":rs.getString(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;

								if(!wotatus.equals("R"))
								{
									errCode = "WORDWIWOSR";
									errString = getErrorString("work_order",errCode,userId);
									break;
								}

							}

						}
					}


					else if(childNodeName.equalsIgnoreCase("item_code"))
					{
						itemCode = genericUtility.getColumnValue("item_code",dom2);

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

							sql = "select count(1) from item where item_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "";
								errString = getErrorString("item_code",errCode,userId);
								break;
							}

						}
					}

					else if(childNodeName.equalsIgnoreCase("no_art"))
					{
						String noArt = genericUtility.getColumnValue("no_art",dom2);

						if (noArt == null || noArt.trim().length() == 0)
						{
							noOfArt = 0;
						}
						else
						{
							noOfArt = Double.parseDouble(noArt);
						}

						if(noOfArt == 0)
						{
							errCode = "WORDWINANU";
							errString = getErrorString("no_art",errCode,userId);
							break;
						}
					}

					else if(childNodeName.equalsIgnoreCase("quantity"))
					{
						squantity = genericUtility.getColumnValue("quantity",dom2);

						if (squantity == null || squantity.trim().length() == 0)
						{
							quantity = 0;
						}
						else
						{
							quantity = Double.parseDouble(squantity);
						}

						if(quantity == 0)
						{
							errCode = "WORDWIQANU";
							errString = getErrorString("quantity",errCode,userId);
							break;
						}
					}


				}
				break;
			case 3:

				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

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


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

					if(childNodeName.equalsIgnoreCase("work_order"))
					{
						workOrder = genericUtility.getColumnValue("work_order",dom2);

						if (workOrder == null || workOrder.trim().length() == 0)
						{
							errCode = "WORDWIWONU";
							errString = getErrorString("work_order",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from workorder where work_order = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "WORDWIWONE";
								errString = getErrorString("work_order",errCode,userId);
								break;
							}else
							{
								sql = "select status from workorder where work_order = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,workOrder);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									wotatus = rs.getString(1) == null ? "":rs.getString(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;

								if(!wotatus.equals("R"))
								{
									errCode = "WORDWIWOSR";
									errString = getErrorString("work_order",errCode,userId);
									break;
								}

							}

						}
					}
					else if(childNodeName.equalsIgnoreCase("no_art"))
					{
						String noArt = genericUtility.getColumnValue("no_art",dom2);

						if (noArt == null || noArt.trim().length() == 0)
						{
							noOfArt = 0;
						}
						else
						{
							noOfArt = Double.parseDouble(noArt);
						}

						if(noOfArt == 0)
						{
							errCode = "WORDWINANU";
							errString = getErrorString("no_art",errCode,userId);
							break;
						}
					}

					else if(childNodeName.equalsIgnoreCase("quantity"))
					{
						squantity = genericUtility.getColumnValue("quantity",dom2);

						if (squantity == null || squantity.trim().length() == 0)
						{
							quantity = 0;
						}
						else
						{
							quantity = Double.parseDouble(squantity);
						}

						if(quantity == 0)
						{
							errCode = "WORDWIQANU";
							errString = getErrorString("quantity",errCode,userId);
							break;
						}
					}
					else if(childNodeName.equalsIgnoreCase("item_code"))
					{

						itemCode = genericUtility.getColumnValue("item_code",dom2);

						if (itemCode == null || itemCode.trim().length() == 0)
						{
							errCode = "WORDWIICCB";
							errString = getErrorString("item_code",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from item where item_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "WORDWIICNE";
								errString = getErrorString("item_code",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 {

		String siteCodeTo = "", siteCodeFr = "", fromTranDate = "", toTranDate = "", insertQuery = "", path = "", objName = "intercomp_reconcile";
		String sundryTypeFr = "", sundryTypeTo = "", sundryCodeFr = "", sundryCodeTo = "",siteCodeFrDescr="",siteCodeToDescr="";
		StringBuffer valueXmlString = null;
		int currentFormNo = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Connection conn = null;
		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="",lotsl="",itmdesc="",loginSite="",siteCode="",atranid="";

		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 workOrder = "",itemCode = "",itemDescr="",sql="",lineNo="",unit="",packCode="",qcReqd="",locGrp="";
		double totDetQuan = 0.0,totNoArt=0.0;
		java.sql.Timestamp currDate = null,mfgDateStart = null,startDate=null,expDate=null,tdate = null;
		double quantity = 0,grossWeight = 0,tareWeight = 0,netWeight = 0,noOfArt=0,shippsize = 0;
		SimpleDateFormat sdf = null;
		String currAppdate = "",woSiteCode="",sStartDate="",sExpDate="",sMfgDateStart="",stdate="";
		String tranid ="";
		double rate = 0,conquan,conOldRate,oldAmount,amount,amtdiff,oldRate=0,getquantity=0;
		int cnt = 0;
		TransIDGenerator generator = null;
		String autoTranid = "";
		String scrFlag = "";
		String xmlString = "";
		String todayDate = "";
		try
		{   

			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>");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			switch (currentFormNo) {
			case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();

				break;
			case 2 : 
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");

				System.out.println("itm_default >>>>> 2>>");
				workOrder = genericUtility.getColumnValue("work_order", dom1);
				sql = "select count(1) from workorder_receipt where work_order = ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;
				if(cnt == 0)
				{
					System.out.println("for new tran id");
					sql = "update obj_forms set scr_flag = ? where win_name = 'w_worder_rcp_det_wiz' and form_no = '2'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, "F");
					cnt = pstmt.executeUpdate();
					System.out.println(">>>>>>>successfully updated record bom post save cnt1:" + cnt);
					conn.commit();
					pstmt.close();
					pstmt = null;

					sdf=new SimpleDateFormat(genutility.getApplDateFormat());
					currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
					todayDate = simpleDateFormat.format(currDate);


					String xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues +        "<tran_id></tran_id>";
					xmlValues = xmlValues +        "<site_code>" + loginSite + "</site_code>";
					xmlValues = xmlValues +        "<tran_date>" + todayDate + "</tran_date>";
					xmlValues = xmlValues +        "<tran_type>F</tran_type>";
					xmlValues = xmlValues + "</Detail1></Root>";
					System.out.println("xmlValues  :["+xmlValues+"]");
					TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
					atranid = tg.generateTranSeqID("WORDWI","tran_id","_spsite_code,T,tran_type,_pptran_date,seq04",conn);
					System.out.println(">>>>>NEW id"+atranid);

				}else
				{
					System.out.println("for existing tran id");
					sql = "update obj_forms set scr_flag = ? where win_name = 'w_worder_rcp_det_wiz' and form_no = '2'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, "T");
					cnt = pstmt.executeUpdate();
					System.out.println(">>>>>>>successfully updated record bom post save cnt1:" + cnt);
					conn.commit();
					pstmt.close();
					pstmt = null;
				}

				sql = "select scr_flag from obj_forms where win_name = 'w_worder_rcp_det_wiz' and form_no = '2'";
				pstmt=conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					scrFlag = rs.getString(1) == null ? "":rs.getString(1);
				}
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;

				if(scrFlag.equals("T"))
				{
					System.out.println("non add record");
					if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						System.out.println("work order from header is"+workOrder);


						System.out.println(">>>>>>>>>>>SXD"+workOrder);

						sql = "select a.tran_id,a.tran_date,a.site_code,a.work_order,a.date__start,"
								+ "a.item_code,b.descr,a.loc_code,a.lot_sl,a.quantity,a.no_art,a.unit,a.gross_weight,a.tare_weight,a.net_weight "
							    + "FROM workorder_receipt a,item b WHERE a.item_code = b.item_code  and a.work_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							tranid = rs.getString(1) == null ? "":rs.getString(1);
							currDate = rs.getTimestamp(2);
							siteCode = rs.getString(3) == null ? "":rs.getString(3);
							workOrder = rs.getString(4) == null ? "":rs.getString(4);
							startDate = rs.getTimestamp(5);
							itemCode = rs.getString(6) == null ? "":rs.getString(6);
							itemDescr  = rs.getString(7) == null ? "":rs.getString(7);
							locCode = rs.getString(8) == null ? "":rs.getString(8);
							lotsl = rs.getString(9) == null ? "":rs.getString(9);
							quantity = rs.getDouble(10);
							noOfArt = rs.getDouble(11);
							unit = rs.getString(12) == null ? "":rs.getString(12);
							grossWeight = rs.getDouble(13);
							tareWeight = rs.getDouble(14);
							netWeight = rs.getDouble(15);
							
							simpleDateFormat=new SimpleDateFormat(genutility.getApplDateFormat());
							currAppdate = simpleDateFormat.format(currDate);

							sdf=new SimpleDateFormat(genutility.getApplDateFormat());
							
							if(startDate != null)
							{
								sStartDate = simpleDateFormat.format(startDate);
							}
							
							sdf=new SimpleDateFormat(genutility.getApplDateFormat());
							tdate = new java.sql.Timestamp(System.currentTimeMillis()) ;
							stdate = simpleDateFormat.format(tdate);
							
							
							valueXmlString.append("<Detail2 objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
							valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
							valueXmlString.append("<tran_id>").append("<![CDATA[" + tranid + "]]>").append("</tran_id>");
							valueXmlString.append("<tran_date>").append("<![CDATA[" + currAppdate + "]]>").append("</tran_date>");
							valueXmlString.append("<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>");
							valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
							valueXmlString.append("<date__start>").append("<![CDATA[" + sStartDate + "]]>").append("</date__start>");
							valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
							valueXmlString.append("<descr>").append("<![CDATA[" + itemDescr + "]]>").append("</descr>");
							valueXmlString.append("<loc_code>").append("<![CDATA["+ locCode +"]]>").append("</loc_code>");
							valueXmlString.append("<lot_sl>").append("<![CDATA["+ lotsl +"]]>").append("</lot_sl>");
							valueXmlString.append("<quantity>").append("<![CDATA[" + quantity + "]]>").append("</quantity>");
							valueXmlString.append("<no_art>").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");
							valueXmlString.append("<unit>").append("<![CDATA[" + unit + "]]>").append("</unit>");
							valueXmlString.append("<gross_weight>").append("<![CDATA[" + grossWeight + "]]>").append("</gross_weight>");
							valueXmlString.append("<tare_weight>").append("<![CDATA[" + tareWeight + "]]>").append("</tare_weight>");
							valueXmlString.append("<net_weight>").append("<![CDATA[" + netWeight + "]]>").append("</net_weight>");
							valueXmlString.append("<chg_user>").append("<![CDATA[" + chgUser + "]]>").append("</chg_user>");
							valueXmlString.append("<chg_term>").append("<![CDATA[" + chgTerm + "]]>").append("</chg_term>");
							valueXmlString.append("<chg_date>").append("<![CDATA[" + stdate + "]]>").append("</chg_date>");
                            valueXmlString.append("</Detail2>");


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

				}else
				{
					System.out.println("add record");
					if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{					

						sql = "select site_code,rel_date,item_code,lot_no,exp_date,mfg_date__start from workorder where work_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							woSiteCode = rs.getString(1) == null ? "":rs.getString(1);
							startDate = rs.getTimestamp(2);
							itemCode = rs.getString(3) == null ? "":rs.getString(3);
							lotNo =  rs.getString(4) == null ? "":rs.getString(4);
							expDate = rs.getTimestamp(5);
							mfgDateStart = rs.getTimestamp(6);

						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						sdf=new SimpleDateFormat(genutility.getApplDateFormat());
						if(startDate != null)
						{
							sStartDate = simpleDateFormat.format(startDate);
						}
						if(expDate != null)
						{
							sExpDate = simpleDateFormat.format(expDate);
						}
						if(mfgDateStart != null)
						{
							sMfgDateStart = simpleDateFormat.format(mfgDateStart);
						}
						


						valueXmlString.append("<Detail2  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"O\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<tran_id>").append("<![CDATA[" + atranid + "]]>").append("</tran_id>");
						valueXmlString.append("<tran_date>").append("<![CDATA[" + todayDate + "]]>").append("</tran_date>");
						valueXmlString.append("<site_code>").append("<![CDATA[" + loginSite + "]]>").append("</site_code>");
						valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
						valueXmlString.append("<date__start>").append("<![CDATA[" + sStartDate + "]]>").append("</date__start>");
						valueXmlString.append("<date__compl>").append("<![CDATA[" + todayDate + "]]>").append("</date__compl>");
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						
						sql = "select descr from item where item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							itemDescr = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						valueXmlString.append("<descr>").append("<![CDATA[" + itemDescr + "]]>").append("</descr>");
						
						valueXmlString.append("<loc_code>").append("<![CDATA[ ]]>").append("</loc_code>");
						valueXmlString.append("<lot_no>").append("<![CDATA[" + lotNo + "]]>").append("</lot_no>");
						valueXmlString.append("<lot_sl>").append("<![CDATA[ ]]>").append("</lot_sl>");
						valueXmlString.append("<exp_date>").append("<![CDATA[" + sExpDate + "]]>").append("</exp_date>");
						valueXmlString.append("<site_code__own>").append("<![CDATA[" + woSiteCode + "]]>").append("</site_code__own>");

						sql = "select sum(quantity) from workorder_rcp_det where work_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totDetQuan = rs.getDouble(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;

						valueXmlString.append("<quantity>").append("<![CDATA[" + totDetQuan + "]]>").append("</quantity>");

						sql = "select unit from item where item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							unit = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;

						valueXmlString.append("<unit>").append("<![CDATA[" + unit + "]]>").append("</unit>");

						sql = "select pack_code from item where item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							packCode = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						valueXmlString.append("<pack_code>").append("<![CDATA[" + packCode + "]]>").append("</pack_code>");
						valueXmlString.append("<potency_perc>").append("<![CDATA[0]]>").append("</potency_perc>");

						valueXmlString.append("<chg_user>").append("<![CDATA[" + chgUser + "]]>").append("</chg_user>");
						valueXmlString.append("<chg_term>").append("<![CDATA[" + chgTerm + "]]>").append("</chg_term>");
						valueXmlString.append("<chg_date>").append("<![CDATA[" + todayDate + "]]>").append("</chg_date>");

						valueXmlString.append("<confirmed>").append("<![CDATA[N]]>").append("</confirmed>");
						valueXmlString.append("<mfg_date>").append("<![CDATA[" + sMfgDateStart + "]]>").append("</mfg_date>");
						valueXmlString.append("<tran_type>").append("<![CDATA[F]]>").append("</tran_type>");
						valueXmlString.append("<operation>").append("<![CDATA[0]]>").append("</operation>");



						sql = "select qc_reqd from siteitem where site_code = ? and item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,loginSite);
						pstmt.setString(2,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							qcReqd = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;

						valueXmlString.append("<qc_reqd>").append("<![CDATA[" + qcReqd + "]]>").append("</qc_reqd>");
						valueXmlString.append("<conv__qty_stduom>").append("<![CDATA[1]]>").append("</conv__qty_stduom>");


						sql = "select sum(no_art) from workorder_rcp_det where work_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totNoArt = rs.getDouble(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						valueXmlString.append("<no_art>").append("<![CDATA[" + totNoArt + "]]>").append("</no_art>");
						valueXmlString.append("<unit__std>").append("<![CDATA[" + unit + "]]>").append("</unit__std>");
						valueXmlString.append("<std_qty>").append("<![CDATA[" + totDetQuan + "]]>").append("</std_qty>");
						valueXmlString.append("<create_stab>").append("<![CDATA[N]]>").append("</create_stab>");
						valueXmlString.append("<gross_weight>").append("<![CDATA[0]]>").append("</gross_weight>");
						valueXmlString.append("<tare_weight>").append("<![CDATA[0]]>").append("</tare_weight>");
						valueXmlString.append("<net_weight>").append("<![CDATA[0]]>").append("</net_weight>");

						if(qcReqd.equalsIgnoreCase("Y"))
						{

							sql = "select var_value from mfgparm where var_name = 'WORCP_LOCK_GROUP'";
							pstmt=conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								locGrp = rs.getString(1) == null ? "":rs.getString(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;

							if(locGrp != null && locGrp.trim().length() > 0)
							{
								valueXmlString.append("<lock_group>").append("<![CDATA[" + locGrp + "]]>").append("</lock_group>");
							}
						}
						valueXmlString.append("</Detail2>");
					}


				}


				break;
			case 3 : 
				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				tranid = genericUtility.getColumnValue("tran_id", dom2, "2").trim();
				//childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{

					System.out.println("tran id is"+tranid);

					sql = "select work_order,item_code,lot_sl,lot_no,no_art,quantity,gross_weight,tare_weight,net_weight,line_no "
							+ "from workorder_rcp_det where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,tranid);
					rs = pstmt.executeQuery();
					while(rs.next())
					{

						workOrder = rs.getString(1) == null ? "":rs.getString(1);
						itemCode = rs.getString(2) == null ? "":rs.getString(2);
						lotsl = rs.getString(3) == null ? "":rs.getString(3);
						lotNo = rs.getString(4) == null ? "":rs.getString(4);
						noOfArt = rs.getDouble(5);
						quantity = rs.getDouble(6);
						grossWeight = rs.getDouble(7);
						tareWeight = rs.getDouble(8);
						netWeight = rs.getDouble(9);
						lineNo = rs.getString(10) == null ? "":rs.getString(10);


						valueXmlString.append("<Detail3 domID='" + lineNo+ "'  objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<tran_id>").append("<![CDATA[" + tranid + "]]>").append("</tran_id>");
						valueXmlString.append("<line_no>").append("<![CDATA[" + lineNo + "]]>").append("</line_no>");
						valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						valueXmlString.append("<quantity>").append("<![CDATA[" + quantity + "]]>").append("</quantity>");
						valueXmlString.append("<lot_no>").append("<![CDATA[" + lotNo + "]]>").append("</lot_no>");
						valueXmlString.append("<lot_sl>").append("<![CDATA[" + lotsl + "]]>").append("</lot_sl>");
						valueXmlString.append("<no_art>").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");
						valueXmlString.append("<loc_code>").append("<![CDATA[" + locCode + "]]>").append("</loc_code>");
						valueXmlString.append("<gross_weight>").append("<![CDATA[" + grossWeight + "]]>").append("</gross_weight>");
						valueXmlString.append("<tare_weight>").append("<![CDATA[" + tareWeight + "]]>").append("</tare_weight>");
						valueXmlString.append("<net_weight>").append("<![CDATA[" + netWeight + "]]>").append("</net_weight>");
						valueXmlString.append("</Detail3>");



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



				}
				else if(currentColumn.trim().equalsIgnoreCase("itm_default_add"))
				{
					tranid = genericUtility.getColumnValue("tran_id", dom2, "2").trim();
					workOrder = genericUtility.getColumnValue("work_order", dom2, "2").trim();
					itemCode = genericUtility.getColumnValue("item_code", dom2, "2").trim();

					System.out.println("TRANID IS>>>"+tranid);
					NodeList nodeList = dom2.getElementsByTagName("Detail3");
					System.out.println("sds"+nodeList.item(0).getNodeName());
					String domId =  nodeList.item(0).getAttributes().getNamedItem("domID").getNodeValue();
					System.out.println("domId=="+domId);

					System.out.println("tran id is"+tranid);

							sql = "select work_order,item_code,lot_sl,lot_no,no_art,quantity,gross_weight,tare_weight,net_weight "
							+ "from workorder_receipt where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,tranid);
					rs = pstmt.executeQuery();
					if(rs.next())
					{

						workOrder = rs.getString(1) == null ? "":rs.getString(1);
						itemCode = rs.getString(2) == null ? "":rs.getString(2);
						lotsl = rs.getString(3) == null ? "":rs.getString(3);
						lotNo = rs.getString(4) == null ? "":rs.getString(4);
						noOfArt = rs.getDouble(5);
						quantity = rs.getDouble(6);
						grossWeight = rs.getDouble(7);
						tareWeight = rs.getDouble(8);
						netWeight = rs.getDouble(9);



						valueXmlString.append("<Detail3 domID='" + domId+ "'  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
						valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<tran_id><![CDATA[" + tranid + "]]></tran_id>");
						valueXmlString.append("<line_no><![CDATA[" + domId + "]]></line_no>");
						valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						valueXmlString.append("<lot_no>").append("<![CDATA[" + lotNo + "]]>").append("</lot_no>");
						valueXmlString.append("<lot_sl>").append("<![CDATA[" + lotsl + "]]>").append("</lot_sl>");
						valueXmlString.append("<no_art>").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");

						//select shipper_size from item_lot_packsize where item_code = 'EF0050';
						sql = "select shipper_size from item_lot_packsize where item_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{

							shippsize = rs1.getDouble(1);

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;

						quantity = shippsize * noOfArt;

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

						}else
						{
							valueXmlString.append("<quantity protect=\"1\">").append("<![CDATA[" + quantity + "]]>").append("</quantity>");	
							setNodeValue( dom, "quantity", getAbsString(""+quantity));  

						}

						sql = "select loc_code__insp from siteitem where item_code = ? and site_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,loginSite);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{

							locCode = rs1.getString(1) == null ? "":rs1.getString(1);

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;


						valueXmlString.append("<loc_code>").append("<![CDATA[" + locCode + "]]>").append("</loc_code>");
						valueXmlString.append("<gross_weight>").append("<![CDATA[" + grossWeight + "]]>").append("</gross_weight>");
						valueXmlString.append("<tare_weight>").append("<![CDATA[" + tareWeight + "]]>").append("</tare_weight>");
						valueXmlString.append("<net_weight>").append("<![CDATA[" + netWeight + "]]>").append("</net_weight>");
						valueXmlString.append("</Detail3>");



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


				}


				break;

			}
			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;
	}

}