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

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

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import org.w3c.dom.CDATASection;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import javax.ejb.Stateless;

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:
		    	
		    	parentNodeList = dom.getElementsByTagName("Detail2");
				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",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;
								}
								
							}
						
						}
					}
					
					
					else if(childNodeName.equalsIgnoreCase("item_code"))
					{
						itemCode = genericUtility.getColumnValue("item_code",dom);
						
						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",dom);
						
						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",dom);
						
						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 = dom.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",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;
									}
									
								}
							
							}
						}
						else if(childNodeName.equalsIgnoreCase("no_art"))
						{
							String noArt = genericUtility.getColumnValue("no_art",dom);
							
							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",dom);

							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",dom);
							
							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="";
		
		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="";
		java.sql.Timestamp currDate = null;
		double quantity = 0,grossWeight = 0,tareWeight = 0,netWeight = 0,noOfArt=0,shippsize = 0;
		SimpleDateFormat sdf = null;
		String currAppdate = "";
		String tranid ="";
		double rate = 0,conquan,conOldRate,oldAmount,amount,amtdiff,oldRate=0,getquantity=0;
		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()+"]");
				
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					System.out.println("itm_default >>>>> 3");
					workOrder = genericUtility.getColumnValue("work_order", dom1);
					System.out.println("work order from header is"+workOrder);
					
					
					System.out.println(">>>>>>>>>>>SXD"+workOrder);
					
					
					sql = "select a.tran_id,a.work_order,a.tran_date,a.item_code,a.no_art,a.quantity,a.gross_weight,"
							+ "a.tare_weight,a.net_weight,b.descr from workorder_receipt a,item b  where "
							+ "a.item_code=b.item_code and work_order = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,workOrder);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						tranid = rs.getString(1) == null ? "":rs.getString(1);
						workOrder = rs.getString(2) == null ? "":rs.getString(2);
						currDate = rs.getTimestamp(3);
						itemCode = 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);
						itemDescr = rs.getString(10) == null ? "":rs.getString(10);
						
						simpleDateFormat=new SimpleDateFormat(genutility.getApplDateFormat());
						currAppdate = simpleDateFormat.format(currDate);
						
						valueXmlString.append("<Detail2>");
						valueXmlString.append("<tran_id>").append("<![CDATA[" + tranid + "]]>").append("</tran_id>");
						valueXmlString.append("<tran_date>").append("<![CDATA[" + currAppdate + "]]>").append("</tran_date>");
						valueXmlString.append("<work_order>").append("<![CDATA[" + workOrder + "]]>").append("</work_order>");
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						valueXmlString.append("<descr>").append("<![CDATA[" + itemDescr + "]]>").append("</descr>");
						valueXmlString.append("<no_art>").append("<![CDATA[" + noOfArt + "]]>").append("</no_art>");
						valueXmlString.append("<quantity>").append("<![CDATA[" + quantity + "]]>").append("</quantity>");
						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("</Detail2>");
						
						
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					
					
				}
				
				
				break;
			    case 3 : 
				parentNodeList = dom.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("<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;
					
					
					
				}
				else if(currentColumn.trim().equalsIgnoreCase("itm_default_add"))
				{
					

					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 StringBuffer getXMLTransData(Connection conn, String sql, String objName) throws Exception
	{
		System.out.println("In getXMLTransData");
		ConnDriver connDriver1 = new ConnDriver();
		Connection conn1=null;
		StringBuffer valueXmlString = new StringBuffer("");
		HashMap<String,Double> issueSiteMap=new HashMap<String,Double>();
		HashMap<String,Double> reseiverSiteMap=new HashMap<String,Double>();
		HashMap<String,String> issueSiteRefMap=new HashMap<String,String>();
		HashMap<String,String> reseiverSiteRefMap=new HashMap<String,String>();
		ArrayList<ArrayList> arrayListData = null;
		ArrayList traceData = null;
		Document dom = null;
		String rType="";
		TransIDGenerator generator = null;
		if(conn!=null){
			ResultSet rs = null;
			PreparedStatement pstmt = null;
			
			int columnCnt = 0;
			int columnItr = 0, detailListCnt = 1;
			ResultSetMetaData resultSetMetaData = null;
			try {
				//Added by Dadaso pawar on 26-MAy-14 [Start]
				// check reconcile type if Auto then following query will executed otherwise 
				// manually--> then only those ref id compare or reconcile whose amount is matched. 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if ( rs != null ) {
					System.out.println("In First If");
					System.out.println("In CallByItemChange");
					resultSetMetaData = rs.getMetaData();
					columnCnt = resultSetMetaData.getColumnCount();
					while(rs.next())
					{
						valueXmlString.append("<Detail2 domID='"+ detailListCnt++ +"' selected = 'N'>\r\n");
						for (columnItr = 1;columnItr <= columnCnt;columnItr++) {
							valueXmlString.append("<");
							valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr).toLowerCase());
							valueXmlString.append(">");
							valueXmlString.append("<![CDATA[");
							valueXmlString.append(rs.getString(columnItr) == null ? "" : rs.getString(columnItr).trim());
							valueXmlString.append("]]>");
							valueXmlString.append("</");
							valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr).toLowerCase());
							valueXmlString.append(">\r\n");
							
						}
						valueXmlString.append("</Detail2>\r\n");
					}
					pstmt = null;
					rs = null;
				}
			}
			catch(SQLException e) {
				System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
				e.printStackTrace();
				try{
					conn.rollback();
				}
				catch(Exception e1){
					e1.printStackTrace();
				}
			}
			catch(Exception ex) {
				System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
				try{
					conn.rollback();
				}
				catch(Exception e1){
					e1.printStackTrace();
				}
				ex.printStackTrace();
			} finally {
				try{
					if(pstmt!=null){
						pstmt.close();
						pstmt = null;
					}
					if(rs!=null){
						rs.close();
						rs = null;
					}
					if(conn1!=null){
						conn1.close();
						conn1=null;
					}
				}catch (SQLException se) {
					se.printStackTrace();
				}
				sql = null;
				
				objName = null;
				
			}
		}else{
			try {
				throw new SQLException("Connection passed to InterCompReconcileProcess.getXMLTransData() method is null");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return valueXmlString;
	}
	

	public String preSaveForm(String tranid)throws ITMException
	{
		String errString = "";
		Document dom = null;
		System.out.println("@@@@@@@@@---------preSaveForm EJB called...");
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			System.out.println("@@@@@@@xmlString1["+tranid+"]");
			if (tranid != null && tranid.trim().length() > 0)
			{
				try{
				
					writeFile(tranid,"/home/deepak/myfile.txt");
					
			}catch (Exception e) {
				e.printStackTrace();
			}
			}			
		}
		catch(SQLException e) {
			System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
			e.printStackTrace();
		}
		catch(Exception ex) {
			System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
			ex.printStackTrace();
		} finally {
			try {
				if(conn != null) {
					conn.close(); 
				}
				conn = null;
				
			} catch(Exception d) {
				d.printStackTrace(); 
			}
		}
		return "";
	}	
    
	  private String readFile(String file) throws Exception
		{
		  String errString = "";
			String sqlQry = "";
			try
			{
				System.out.println("File "+file);
				FileInputStream fin = new FileInputStream(file);
				BufferedReader br = new BufferedReader(new InputStreamReader(fin));
				String tempStr = "";
				while ((tempStr = br.readLine()) != null)
				{
					sqlQry = sqlQry + tempStr;
				}
				tempStr = null;
				br = null;
				fin.close();
			} catch (FileNotFoundException fe)
			{
				System.out.println("FileNotFoundException :InterCompReconcileProcess :readFile(String).." + fe.getMessage());
				fe.printStackTrace();
				errString = fe.getMessage();
				throw new ITMException(fe);
			}

			catch (Exception e)
			{
				System.out.println("Exception :InterCompReconcileProcess :readFile(String).." + e.getMessage());
				e.printStackTrace();
				errString = e.getMessage();
				throw new ITMException(e);
			}
			return sqlQry;
		}
	    private void writeFile(String adpQuery,String fileName) throws Exception
		{
			String errString;
			try
			{
				System.out.println("fileName "+fileName);
				FileOutputStream fout = null;
				BufferedWriter bw = null;
				fout = new FileOutputStream(fileName);
				bw = new BufferedWriter(new OutputStreamWriter(fout));
				bw.write(adpQuery);
				bw.close();
				fout.close();
				
			} catch (FileNotFoundException fe)
			{
				System.out.println("FileNotFoundException :InterCompReconcileProcess :writeFile(String,String).." + fe.getMessage());
				fe.printStackTrace();
				errString = fe.getMessage();
				throw new ITMException(fe);
			}

			catch (Exception e)
			{
				System.out.println("Exception :InterCompReconcileProcess :writeFile(String,String).." + e.getMessage());
				e.printStackTrace();
				errString = e.getMessage();
				throw new ITMException(e);
			}
		}

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