/********************************************************
	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 = "",locCode ="";


		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();

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

						}
					}
					

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

						}
					}
					else if(childNodeName.equalsIgnoreCase("loc_code"))
					{
						locCode = genericUtility.getColumnValue("loc_code",dom2);

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

							sql = "select count(1) from location where loc_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,locCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "WORDWLCNE";
								errString = getErrorString("loc_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, lineNo = 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="",unit="",packCode="",qcReqd="",locGrp="",tranType ="",confirmed="",createStab="",unitStd="",locCodeAprv="";
		double totDetQuan = 0.0,totNoArt=0.0,potencyPerc=0.0,convQtyStduom=0.0,operation=0.0,stdQty=0.0;
		java.sql.Timestamp currDate = null,mfgDateStart = null,startDate=null,expDate=null,tdate = null,datCompl=null,confDate=null;
		double quantity = 0,grossWeight = 0,tareWeight = 0,netWeight = 0,noOfArt=0,shippsize = 0;
		SimpleDateFormat sdf = null;
		String currAppdate = "",woSiteCode="",sStartDate="",sExpDate="",sMfgDateStart="",stdate="",sDatCompl="",sConfDate="";
		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 = "";
		String noOfArts = "";
		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 CASE WHEN confirmed IS NULL THEN 'N' ELSE CONFIRMED  END = 'N' and 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;
				int count = 0;
				
					if(cnt > 0)
					{
						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.lot_no,a.gross_weight,a.net_weight," 
									+" a.qc_reqd,a.tran_type, a.operation, a.date__compl,a.exp_date,a.site_code__own,a.pack_code,a.confirmed,a.mfg_date,"
									+ "a.conv__qty_stduom,a.unit__std,a.std_qty,a.create_stab,a.loc_code__aprv,a.potency_perc,a.lock_group,a.conf_date "
									+ "FROM workorder_receipt a,item b WHERE a.item_code = b.item_code  and CASE WHEN confirmed IS NULL THEN 'N' ELSE CONFIRMED  END = 'N' and work_order = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							
							rs = pstmt.executeQuery();
							if(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);
								lotNo = rs.getString(13) == null ? "":rs.getString(13);
								grossWeight = rs.getDouble(14);
								netWeight = rs.getDouble(15);
								
								qcReqd = rs.getString("qc_reqd") == null ? "":rs.getString("qc_reqd");
								tranType = rs.getString("tran_type") == null ? "":rs.getString("tran_type");
								operation = rs.getDouble("operation");
								datCompl = rs.getTimestamp("date__compl");
								expDate = rs.getTimestamp("exp_date");
								woSiteCode = rs.getString("site_code__own") == null ? "":rs.getString("site_code__own");
								packCode = rs.getString("pack_code") == null ? "":rs.getString("pack_code");
								confirmed = rs.getString("confirmed") == null ? "":rs.getString("confirmed");
								mfgDateStart = rs.getTimestamp("mfg_date");
								
								convQtyStduom = rs.getDouble("conv__qty_stduom");
								unitStd = rs.getString("unit__std") == null ? "":rs.getString("unit__std");
								stdQty  = rs.getDouble("std_qty");
								createStab = rs.getString("create_stab") == null ? "":rs.getString("create_stab");
								locCodeAprv = rs.getString("loc_code__aprv") == null ? "":rs.getString("loc_code__aprv");
								potencyPerc = rs.getDouble("potency_perc");
								locGrp = rs.getString("lock_group") == null ? "":rs.getString("lock_group");
								confDate = rs.getTimestamp("conf_date");
								tareWeight = grossWeight - netWeight;
								
								simpleDateFormat=new SimpleDateFormat(genutility.getApplDateFormat());
								currAppdate = simpleDateFormat.format(currDate);

								sdf=new SimpleDateFormat(genutility.getApplDateFormat());

								if(startDate != null)
								{
									sStartDate = simpleDateFormat.format(startDate);
								}
								if(datCompl != null)
								{
									sDatCompl = simpleDateFormat.format(datCompl);
								}
								if(expDate != null)
								{
									sExpDate = simpleDateFormat.format(expDate);
								}
								if(mfgDateStart != null)
								{
									sMfgDateStart = simpleDateFormat.format(mfgDateStart);
								}
								if(confDate != null)
								{
									sConfDate = simpleDateFormat.format(confDate);
								}
								
								sdf=new SimpleDateFormat(genutility.getApplDateFormat());
								tdate = new java.sql.Timestamp(System.currentTimeMillis()) ;
								stdate = simpleDateFormat.format(tdate);

								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>").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("<lot_no>").append("<![CDATA["+ lotNo +"]]>").append("</lot_no>");
								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>");
								
								//-- Added 13-Aug-2014
								
								valueXmlString.append("<qc_reqd>").append("<![CDATA[" + qcReqd + "]]>").append("</qc_reqd>");
								valueXmlString.append("<tran_type>").append("<![CDATA[F]]>").append("</tran_type>");
								valueXmlString.append("<operation>").append("<![CDATA[0]]>").append("</operation>");
								valueXmlString.append("<date__compl>").append("<![CDATA[" + sDatCompl + "]]>").append("</date__compl>");
								valueXmlString.append("<exp_date>").append("<![CDATA[" + sExpDate + "]]>").append("</exp_date>");
								valueXmlString.append("<site_code__own>").append("<![CDATA[" + woSiteCode + "]]>").append("</site_code__own>");	
								valueXmlString.append("<pack_code>").append("<![CDATA[" + packCode + "]]>").append("</pack_code>");
								valueXmlString.append("<date__start>").append("<![CDATA[" + sStartDate + "]]>").append("</date__start>");
								valueXmlString.append("<confirmed>").append("<![CDATA[" + confirmed + "]]>").append("</confirmed>");
								valueXmlString.append("<conf_date>").append("<![CDATA[" + sConfDate + "]]>").append("</conf_date>");
								valueXmlString.append("<mfg_date>").append("<![CDATA[" + sMfgDateStart + "]]>").append("</mfg_date>");		
								valueXmlString.append("<conv__qty_stduom>").append("<![CDATA[" + convQtyStduom + "]]>").append("</conv__qty_stduom>");
								valueXmlString.append("<unit__std>").append("<![CDATA[" + unitStd + "]]>").append("</unit__std>");
								valueXmlString.append("<std_qty>").append("<![CDATA[" + stdQty + "]]>").append("</std_qty>");
								valueXmlString.append("<create_stab>").append("<![CDATA[" + createStab + "]]>").append("</create_stab>");
								
								/*valueXmlString.append("<yield_with_reco>").append("<![CDATA[0]]>").append("</yield_with_reco>");
								valueXmlString.append("<yield_without_reco>").append("<![CDATA[0]]>").append("</yield_without_reco>");*/
								valueXmlString.append("<loc_code__aprv>").append("<![CDATA[" + locCodeAprv + "]]>").append("</loc_code__aprv>");
								//valueXmlString.append("<canc_bo>").append("<![CDATA[N]]>").append("</canc_bo>");
								//valueXmlString.append("<process_hours>").append("<![CDATA[0]]>").append("</process_hours>");
								valueXmlString.append("<potency_perc>").append("<![CDATA[" + potencyPerc + "]]>").append("</potency_perc>");
								/*valueXmlString.append("<emp_code__aprv>").append("<![CDATA[" + userId + "]]>").append("</emp_code__aprv>");
								valueXmlString.append("<add_user>").append("<![CDATA[" + chgUser + "]]>").append("</add_user>"); //changes done by deepak
								valueXmlString.append("<add_term>").append("<![CDATA[" + chgTerm + "]]>").append("</add_term>");
								valueXmlString.append("<add_date>").append("<![CDATA[" + currAppdate + "]]>").append("</add_date>");*/
								valueXmlString.append("<lock_group>").append("<![CDATA[" + locGrp + "]]>").append("</lock_group>");
								valueXmlString.append("</Detail2>");


							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
						}
					}
					else    //commented on 11/aug/14
					{
						

						System.out.println("in add >>>>>");
						//if(currentColumn.trim().equalsIgnoreCase("itm_default_add"))
						//{		
							sdf=new SimpleDateFormat(genutility.getApplDateFormat());
							currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
							currAppdate = sdf.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>" + currAppdate + "</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);
							*/
							
							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);
							}
							else
							{
								sStartDate = currAppdate;
							}
							if(expDate != null)
							{
								sExpDate = simpleDateFormat.format(expDate);
							}
							if(mfgDateStart != null)
							{
								sMfgDateStart = simpleDateFormat.format(mfgDateStart);
							}

							count = 1;
							valueXmlString.append("<Detail2 domID='" + count + "'  objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
							valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\"/>\r\n");
							//valueXmlString.append("<tran_id/>\r\n");
							valueXmlString.append("<tran_id>").append("<![CDATA[]]>").append("</tran_id>");
							valueXmlString.append("<tran_date>").append("<![CDATA[" + currAppdate + "]]>").append("</tran_date>");
							valueXmlString.append("<site_code>").append("<![CDATA[" + woSiteCode + "]]>").append("</site_code>");
							valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
							valueXmlString.append("<tran_type>").append("<![CDATA[F]]>").append("</tran_type>");
							valueXmlString.append("<operation>").append("<![CDATA[0]]>").append("</operation>");
							valueXmlString.append("<date__start>").append("<![CDATA[" + sStartDate + "]]>").append("</date__start>");
							valueXmlString.append("<date__compl>").append("<![CDATA[" + currAppdate + "]]>").append("</date__compl>");
							valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
							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 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>");
							sql = "select unit,pack_code from item where item_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								unit = rs.getString("unit") == null ? "":rs.getString("unit");
								packCode = rs.getString("pack_code") == null ? "":rs.getString("pack_code");
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;

							valueXmlString.append("<unit>").append("<![CDATA[" + unit + "]]>").append("</unit>");
							valueXmlString.append("<pack_code>").append("<![CDATA[" + packCode + "]]>").append("</pack_code>");
							valueXmlString.append("<confirmed>").append("<![CDATA[N]]>").append("</confirmed>");
							valueXmlString.append("<conf_date>").append("<![CDATA[" + currAppdate + "]]>").append("</conf_date>");
							valueXmlString.append("<mfg_date>").append("<![CDATA[" + sMfgDateStart + "]]>").append("</mfg_date>");
							sql = "select qc_reqd from siteitem where site_code = ? and item_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,woSiteCode);
							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>");
							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>");

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

							/*sql = "select sum(quantity) from workorder_rcp_det where work_order = ? and confirmed = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							pstmt.setString(2, "N");
							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 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("<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 sum(no_art) from workorder_rcp_det where work_order = ? and confirmed = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							pstmt.setString(2, "N");
							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("<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>");
							*/
						//	valueXmlString.append("<yield_with_reco>").append("<![CDATA[0]]>").append("</yield_with_reco>");
						//	valueXmlString.append("<yield_without_reco>").append("<![CDATA[0]]>").append("</yield_without_reco>");
							valueXmlString.append("<loc_code__aprv>").append("<![CDATA[ ]]>").append("</loc_code__aprv>");
							
							
						//	valueXmlString.append("<canc_bo>").append("<![CDATA[N]]>").append("</canc_bo>");
						//	valueXmlString.append("<process_hours>").append("<![CDATA[0]]>").append("</process_hours>");
							valueXmlString.append("<potency_perc>").append("<![CDATA[0]]>").append("</potency_perc>");
						//	valueXmlString.append("<emp_code__aprv>").append("<![CDATA[" + userId + "]]>").append("</emp_code__aprv>");
							
						//	valueXmlString.append("<add_user>").append("<![CDATA[" + chgUser + "]]>").append("</add_user>"); //changes done by deepak
						//	valueXmlString.append("<add_term>").append("<![CDATA[" + chgTerm + "]]>").append("</add_term>");
						//	valueXmlString.append("<add_date>").append("<![CDATA[" + currAppdate + "]]>").append("</add_date>");

							
							
							valueXmlString.append("<chg_user>").append("<![CDATA[" + chgUser + "]]>").append("</chg_user>"); //changes done by deepak
							valueXmlString.append("<chg_term>").append("<![CDATA[" + chgTerm + "]]>").append("</chg_term>");
							valueXmlString.append("<chg_date>").append("<![CDATA[" + currAppdate + "]]>").append("</chg_date>");

							valueXmlString.append("<quantity>").append("<![CDATA[0]]>").append("</quantity>");
							valueXmlString.append("</Detail2>");
						}
					
						
					//}
					
					

				break;
			    case 3 : 
				parentNodeList = dom2.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				//tranid = genericUtility.getColumnValue("tran_id", dom2, "2");
				tranid = genericUtility.getColumnValue("tran_id", dom1);
				workOrder = genericUtility.getColumnValue("work_order", dom1);
				//childNodeListLength = childNodeList.getLength();
				System.out.println("tran val"+tranid+"workOrder>>>"+workOrder);
				
				sql = "select tran_id from workorder_receipt where CASE WHEN confirmed IS NULL THEN 'N' ELSE CONFIRMED  END = 'N' and work_order = ?";
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1,workOrder);
		        rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{

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

				}
				pstmt1.close();
				rs1.close();
				pstmt1 = null;
				rs1 = null;
				
				System.out.println("tranid FROM QUERY"+tranid);
				
				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,line_no,loc_code,gross_weight, tare_weight,net_weight "
							+ "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);
					    lineNo = rs.getInt(7);
					    locCode = rs.getString(8) == null ? "":rs.getString(8);
						grossWeight = rs.getDouble("gross_weight");
						netWeight = rs.getDouble("net_weight");
						tareWeight = rs.getDouble("tare_weight");
						
					    
						
						/*sql = "select gross_weight,net_weight from item where item_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
				        rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{

							grossWeight = rs1.getDouble(1);
							netWeight = rs1.getDouble(2);

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						
						tareWeight = grossWeight - netWeight;
						*/

						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");
					//workOrder = genericUtility.getColumnValue("work_order", dom2, "2");
					//itemCode = genericUtility.getColumnValue("item_code", dom2, "2");
					//tranid = genericUtility.getColumnValue("tranid", dom1);
					workOrder = genericUtility.getColumnValue("work_order", dom1);
					itemCode = genericUtility.getColumnValue("item_code", dom1);

					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 max(line_no)  "
							+ "from workorder_rcp_det where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,tranid);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						lineNo = rs.getInt(1);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					
					
					sql = "select work_order,item_code,lot_sl,lot_no,no_art,quantity "
							+ "from workorder_receipt where tran_id = ? and CASE WHEN confirmed IS NULL THEN 'N' ELSE CONFIRMED  END = 'N'";
					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);
						
						lineNo++;
						System.out.println(">>>>>>>>>>>>>..dom"+lineNo);
						valueXmlString.append("<Detail3 domID='" + lineNo+ "'  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[" + lineNo + "]]></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[]]>").append("</lot_sl>");
						valueXmlString.append("<no_art>").append("<![CDATA[0]]>").append("</no_art>");
						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>");
						valueXmlString.append("<quantity>").append("<![CDATA[0]]>").append("</quantity>");	

						
						/*sql = "select gross_weight,net_weight from item where item_code = ?";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
				        rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{

							grossWeight = rs1.getDouble(1);
							netWeight = rs1.getDouble(2);

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						
						tareWeight = grossWeight - netWeight;
						
						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>");
						
						sql = "select shipper_size from item_lot_packsize where item_code = ? and lot_no = ? and gross_weight = ? and net_weight = ?"; //changes (add lot_no,gross_weight,net_weight) done as per 11/AUG/14
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,lotNo);
						pstmt1.setDouble(3,grossWeight);
						pstmt1.setDouble(4,netWeight);
						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("</Detail3>");
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
				}

				else if(currentColumn.trim().equalsIgnoreCase("no_art"))
				{
					System.out.println(">>>>START>>>>");
					noOfArts = genericUtility.getColumnValue("no_art",dom);
					lotNo = genericUtility.getColumnValue("lot_no",dom);
					itemCode = genericUtility.getColumnValue("item_code",dom);
					String currDomStr = genericUtility.serializeDom(dom);
					System.out.println("currDomStr[" + currDomStr + "]");
					
					if (noOfArts == null || noOfArts.trim().length() == 0)
					{
						noOfArt = 0;
					}
					else
					{
						noOfArt = Double.parseDouble(noOfArts);
					}

					if(noOfArt > 0)
					{
						System.out.println(">>>>START>>>>>>>>>");
						sql = "select shipper_size,gross_weight,net_weight  from item_lot_packsize where item_code = ?  and lot_no__from <= ? and lot_no__to >= ? ";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,lotNo);
						pstmt1.setString(3,lotNo);
				        rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							shippsize =  rs1.getDouble("shipper_size");
							grossWeight = rs1.getDouble("gross_weight");
							netWeight = rs1.getDouble("net_weight");

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						
						/*sql = "select shipper_size from item_lot_packsize where item_code = ? and lot_no = ? and gross_weight = ? and net_weight = ?"; //changes (add lot_no,gross_weight,net_weight) done as per 11/AUG/14
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,lotNo);
						pstmt1.setDouble(3,grossWeight);
						pstmt1.setDouble(4,netWeight);
						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));  
						}
						valueXmlString.append("<gross_weight protect=\"0\">").append("<![CDATA[" + (noOfArt * grossWeight) + "]]>").append("</gross_weight>");
						setNodeValue( dom, "gross_weight", getAbsString(""+ (noOfArt * grossWeight)));  
						valueXmlString.append("<net_weight protect=\"0\">").append("<![CDATA[" + (noOfArt * netWeight) + "]]>").append("</net_weight>");
						setNodeValue( dom, "net_weight", getAbsString(""+(noOfArt * netWeight)));  
						valueXmlString.append("<tare_weight protect=\"0\">").append("<![CDATA[" + ( (noOfArt * grossWeight) - (noOfArt * netWeight)) + "]]>").append("</tare_weight>");
						setNodeValue( dom, "tare_weight", getAbsString(""+( (noOfArt * grossWeight) - (noOfArt * netWeight))));  
						
						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("quantity"))
				{
					String currDomStr = genericUtility.serializeDom(dom);
					quantity = Double.parseDouble(genericUtility.getColumnValue("quantity",dom));
					lotNo = genericUtility.getColumnValue("lot_no",dom);
					itemCode = genericUtility.getColumnValue("item_code",dom);
					
						sql = "select gross_weight,net_weight  from item where item_code = ? ";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
				        rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							grossWeight = rs1.getDouble("gross_weight");
							netWeight = rs1.getDouble("net_weight");

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						
						/*sql = "select shipper_size from item_lot_packsize where item_code = ? and lot_no = ? and gross_weight = ? and net_weight = ?"; //changes (add lot_no,gross_weight,net_weight) done as per 11/AUG/14
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,lotNo);
						pstmt1.setDouble(3,grossWeight);
						pstmt1.setDouble(4,netWeight);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{

							shippsize = rs1.getDouble(1);

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

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

				break;

			}
			
			if(("no_art".equalsIgnoreCase(currentColumn)) || ("quantity".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;
	}

}
