/********************************************************
	Title 	 : 	QCApprovalWizIC[M14HFRA002]
	Date  	 : 	04/12/14
	Developer:  Sagar M.

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

import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.mfg.MfgCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import ibase.system.config.ConnDriver;
import javax.ejb.Stateless;

@Stateless 
public class QCApprovalWizIC extends ValidatorEJB implements QCApprovalWizICLocal,QCApprovalWizICRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	String curTranId = null;
	boolean isShortQtyWithinMcVar = false;
	double shortQtyActual = 0;
	boolean actStkLess = false;
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			System.out.println(">>>>>>>>>>>>wfValData start>>>>>>>>>>>");
			System.out.println(">>>>>>>>>>>>Val xmlString :: " + xmlString );
			System.out.println(">>>>>>>>>>>>Val xmlString1 :: " + xmlString1 );
			System.out.println(">>>>>>>>>>>>Val xmlString2 :: " + xmlString2 );
			System.out.println(">>>>>>>>>>>>wfValData end>>>>>>>>>>>");
			
			if(xmlString != null && xmlString.trim().length() > 0)
			{
				dom = parseString(xmlString);
			}
			if(xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom1 = parseString(xmlString1);
			}
			if(xmlString2 != null && xmlString2.trim().length() > 0)
			{
				dom2 = parseString(xmlString2);
			}

			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			throw new ITMException( e );
		}
		return (errString);
	}
	
	
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		//Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null,rs1 = null;
		String sql = "";
		String userId = "";
		String qcOrdNo="",status="";
		String itemCode = null,siteCode = null,childNodeValue = null;
		int currentFormNo=0,cnt=0,childNodeListLength;
		int ctr=0;
		String loginSite = "",columnValue="",lotNo ="",lotSl="",locCodeTo="",invStat="",sampleQtyStr="";
		double sampleQty=0.0;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println(">>>>>>>>>>>>>>>>>>>>In Validation currentFormNo:"+currentFormNo);
			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 ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					
					if( childNodeName.equalsIgnoreCase("qc_ord__no"))
					{
						System.out.println(">>>>>>>>>>>>>>>>Check qc_ord__no Validation:"+childNodeName);
						qcOrdNo = getColumnValue("qc_ord__no",dom);
						System.out.println(">>>qcOrdNo Validation:" + qcOrdNo);
						siteCode = getColumnValue("site_code",dom);
						itemCode = getColumnValue("item_code",dom);
						lotNo = getColumnValue("lot_no",dom);
						if(qcOrdNo != null && qcOrdNo.trim().length() > 0 )
						{
							sql = "select count(1) from qc_order where qorder_no = ? ";
							pstmt= conn.prepareStatement(sql);
							pstmt.setString(1, qcOrdNo);
							rs = pstmt.executeQuery(); 
							if(rs.next())
							{
							   cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							System.out.println(">>>>>>>>qorder_no cnt:"+qcOrdNo);
							if (cnt == 0)
							{
								errCode = "VMQCORM"; //error for not exist qc order no in  qc_order
								errString = getErrorString("qorder_no",errCode,userId);
								break;
							}
							else
							{
								sql = "select status from qc_order where qorder_no = ?";
								pstmt= conn.prepareStatement(sql);
								pstmt.setString(1, qcOrdNo);
								rs = pstmt.executeQuery(); 
								if(rs.next())
								{
								   status = rs.getString(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								System.out.println(">>>>>>>>qorder_no status:"+status);
								if ( status != null && status.trim().length() > 0 && status.equalsIgnoreCase("C") )
								{
									errCode = "VMQCORC"; //error for confirmed qc order no   
									errString = getErrorString("qorder_no",errCode,userId);
									break;
								}
							}//end else status = C
						} //end qorder_no ! null
						else
						{
							errCode = "VTQCNOBLNK"; //error for blank qc order no   
							errString = getErrorString("qorder_no",errCode,userId);
							break;
						}
					} 
				} //end for
			}//end case 1
			break;
			case 2 :
			{	
				System.out.println(">>>>>>>>>>>>>>>>>>QCApprovalWizIC Validation Call Case 2>>>>>>>>>>>>>>>>>>>>>>");
			
			} //end case 2
			break;
			case 3 :
			{
				System.out.println(">>>>>>>>>>>>>>>>>>QCApprovalWizIC Validation Call Case 3>>>>>>>>>>>>>>>>>>>>>>");
				NodeList parentNodeList1 = dom2.getElementsByTagName("Detail3");
				Node parentNode1 = parentNodeList1.item(0);
				System.out.println(">>>>>>>>>>parentNode1.getNodeName().length():"+parentNode1.getNodeName().length());
				String selectValue = this.getSelectValue(parentNode1);
				System.out.println(">>>>>>>>>>>selectValue:"+selectValue);
				if("N".equals(selectValue))
				{
					errCode = "VTINVSCAN"; 
					errString = getErrorString("",errCode,userId);
					break;
				}
			
			}//case 3 end
			break;
			case 4 :
			{
				System.out.println(">>>>>>>>>>>>>>>>>>QCApprovalWizIC Validation Call Case 4>>>>>>>>>>>>>>>>>>>>>>");
				parentNodeList = dom.getElementsByTagName("Detail4");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNode != null && childNode.getFirstChild() != null)
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					if (childNodeName.equalsIgnoreCase("loc_code"))
					{
						locCodeTo = genericUtility.getColumnValue("loc_code", dom);
						System.out.println(">>>Scan Input locCodeTo:"+locCodeTo);
						if (locCodeTo != null && locCodeTo.trim().length() > 0)
						{
							sql = " select count(1) cnt from location where loc_code = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, locCodeTo);
							rs = pstmt.executeQuery();
							cnt = 0;
							if (rs.next())
							{
								cnt = rs.getInt("cnt");
							}
							rs.close();
							rs = null;

							pstmt.close();
							pstmt = null;
							if (cnt == 0)
							{
								errString = getErrorString("loc_code__to","VMINVLOC",userId);
								break;
							}
						} 
						else
						{
							errString = getErrorString("","VTINVSCAN",userId);
							break;
						}
					}
					
				}
				
			  }//case 4 end
			 
			}//end catch switch
		}//end try
		catch(Exception e)
		{
			e.printStackTrace();			
			errString=e.getMessage();			
			throw new ITMException( e );
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					if(pstmt != null )
					{
						pstmt.close();
						pstmt =null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception d)
			{
				d.printStackTrace();
				throw new ITMException( d );
			}
		}
	   return errString;
	
	}
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}

	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(">>>>>>>>>>>>>>>>In QCApprovalWizIC itemChanged xmlString>>>>>>>>>>>>>>>>>>");
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}

			System.out.println("xml str1 in Detail --->["+xmlString+"]");

			System.out.println("xml str2 in Detail --->["+xmlString1+"]");

			System.out.println("xml str3 in Detail --->["+xmlString2+"]");
			valueXmlString = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
			//genWOIssue = null;
		}
		catch(Exception e)
		{
			System.out.println("Exception : [QCApprovalWizIC][itemChanged(String,String)] :==>\n"+e.getMessage());
			throw new ITMException( e );
		}
		return valueXmlString;
	}
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null , pstmt1 = null;
		ResultSet rs = null,rs1=null;
		ConnDriver connDriver = new ConnDriver();
		java.text.SimpleDateFormat sdf = null,sdf1 = null;
		java.util.Date currDate = new java.util.Date();
		//Date qcOrdDate=null;
		java.sql.Timestamp qcOrdDate = null;
		String tranId="", sql = "",qcOrdNo="",locDescr="",loginSite="",refSer="XFRX";
		String itemCode = "",siteCode = "",itemDescr = "",currDateStr=""; 
		String lotNo="",locCode="",lotSl="",locCodeFrom="",locDescrFrom="",lotNoFrom="",lotNoTo="",lotSlFrom="",lotSlTo="",locCodeTo="",locCodeIss="";
		String qcOrdType="",descr = "",unit = "",unitSample="",testStatus="",suppCode="",porcpNo="",siteDescr="",qcOrdDateStr="";
		String userId = "", termId = "",errCode="",chgTerm="",locType="",dbLocCode="",dbLocType,containerNo="";
		String rejectionCode="",rejectionDescr="";
		int lineNo = 0,dbLineNo=0;
		int currentFormNo= 0;
		int noArt=0,domId=0,lineNoMax=0;
		int parentNodeListLength = 0;
		double quantity=0.0,qtyPassed=0.0,qtyReject=0.0,qtySample=0.0;
		double sampleQty=0.0;
		NodeList parentNodeList = null;
		Node parentNode = null;
		String currAppdate ="";
		java.sql.Timestamp sysDate = null;
	    
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			sysDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
            System.out.println(">>>>>>>>>>>>> itemChanged dom currentColumn:"+currentColumn);
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			System.out.println(">>>>>>>>>>>>>In itemChanged editFlag:"+editFlag);
			valueXmlString.append(editFlag).append("</editFlag></header>");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			userId = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
			chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			if (userId == null || userId.trim().length() == 0)
			{
				userId = "SYSTEM";
			}
			if (termId == null || termId.trim().length() == 0)
			{
				termId = "SYSTEM";
			}
            System.out.println(">>>>>>currentFormNo:"+currentFormNo);
			switch(currentFormNo)
			{
			case 2 :
			{
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					System.out.println(">>>>>>>>>>>>QCApprovalWizIC itm_default ItemChange Call>>>>>>>>>>");
					siteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
					qcOrdNo = checkNull(genericUtility.getColumnValue("qc_ord__no", dom1));
					System.out.println(">>>>>>>>>>>>>>>>>>>>>>qcOrdNo dom1:"+qcOrdNo);
					
					sql = "select qorder_date,qorder_type,site_code,item_code,quantity,unit,qty_passed,qty_rejected," +
							"qty_sample,unit__sample,supp_code,porcp_no,lot_no,rejection_code,rejection_descr " +
							"from qc_order where qorder_no= ?";
					pstmt= conn.prepareStatement(sql);
					pstmt.setString(1, qcOrdNo);
					rs = pstmt.executeQuery(); 
					if(rs.next())
					{
					    qcOrdDate = rs.getTimestamp("qorder_date");
					    qcOrdType = rs.getString("qorder_type");
					    siteCode = rs.getString("site_code") == null ? "" : rs.getString("site_code");
					    itemCode = rs.getString("item_code") == null ? "" : rs.getString("item_code");
					    quantity = rs.getDouble("quantity");
					    unit = rs.getString("unit") == null ? "" : rs.getString("unit");
					    qtyPassed = rs.getDouble("qty_passed");
					    qtyReject = rs.getDouble("qty_rejected");
					    qtySample = rs.getDouble("qty_sample");
					    unitSample = rs.getString("unit__sample") == null ? "" : rs.getString("unit__sample");
					    suppCode = rs.getString("supp_code") == null ? "" : rs.getString("supp_code");
					    porcpNo = rs.getString("porcp_no") == null ? "" : rs.getString("porcp_no");
					    lotNo = rs.getString("lot_no") == null ? "" : rs.getString("lot_no");
					    rejectionCode = rs.getString("rejection_code") == null ? "" : rs.getString("rejection_code");
					    rejectionDescr = rs.getString("rejection_descr") == null ? "" : rs.getString("rejection_descr");
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					
					qcOrdDateStr = sdf.format(qcOrdDate);
					//valueXmlString.append("<Detail2 domID='1' selected=\"N\">\r\n");
					//valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
					valueXmlString.append("<Detail2 domID='1' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
					valueXmlString.append("<qorder_no><![CDATA["+qcOrdNo+"]]></qorder_no>");
					valueXmlString.append("<qorder_date><![CDATA["+ qcOrdDateStr +"]]></qorder_date>");
				    valueXmlString.append("<site_code protect =\"1\"><![CDATA["+ siteCode +"]]></site_code>");
					siteDescr = findValue(conn, "descr", "site", "site_code", siteCode);
					itemDescr = findValue(conn, "descr", "item", "item_code", itemCode);
					valueXmlString.append("<site_descr><![CDATA["+ siteDescr +"]]></site_descr>");
					valueXmlString.append("<qorder_type protect=\"1\"><![CDATA["+ qcOrdType +"]]></qorder_type>");
					valueXmlString.append("<item_code protect =\"1\"><![CDATA["+ itemCode +"]]></item_code>");
					valueXmlString.append("<item_descr><![CDATA["+ itemDescr +"]]></item_descr>");
					valueXmlString.append("<unit><![CDATA["+ unit +"]]></unit>");
					valueXmlString.append("<quantity><![CDATA["+ quantity +"]]></quantity>");
					valueXmlString.append("<qty_passed><![CDATA["+ qtyPassed +"]]></qty_passed>");
					valueXmlString.append("<qty_rejected><![CDATA["+ qtyReject +"]]></qty_rejected>");
					valueXmlString.append("<qty_sample><![CDATA["+ qtySample +"]]></qty_sample>");
					valueXmlString.append("<unit__sample><![CDATA["+ unitSample +"]]></unit__sample>");
					valueXmlString.append("<supp_code><![CDATA["+ suppCode +"]]></supp_code>");
					valueXmlString.append("<porcp_no><![CDATA["+ porcpNo +"]]></porcp_no>");
					valueXmlString.append("<lot_no><![CDATA["+ lotNo +"]]></lot_no>");
					valueXmlString.append("<chg_date><![CDATA[" + sdf.format(currDate).toString() + "]]></chg_date>");
					valueXmlString.append("<rejection_code><![CDATA[" + rejectionCode + "]]></rejection_code>");
					valueXmlString.append("<rejection_descr><![CDATA[" + rejectionDescr + "]]></rejection_descr>");
					valueXmlString.append("<chg_term><![CDATA[" + termId + "]]></chg_term>");
					valueXmlString.append("<chg_user><![CDATA[" + userId + "]]></chg_user>");
					valueXmlString.append("</Detail2>\r\n");
					
				}
			}
			break;
			case 3:
			{
				System.out.println(">>>>>>>>>>>case 3 currentColumn:"+currentColumn);
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					qcOrdNo = genericUtility.getColumnValue("qc_ord__no", dom1);
      				parentNodeList = dom2.getElementsByTagName("Detail2");
					System.out.println(">>>>>>>>parentNodeList:"+parentNodeList);
					parentNodeListLength = parentNodeList.getLength();
					System.out.println(">>>>>>>>in case 3:parentNodeListLength:"+parentNodeListLength);
					for(int ctr = 0; ctr < parentNodeListLength ; ctr++ )
					{
						parentNode = parentNodeList.item(ctr);
						siteCode = checkNull(genericUtility.getColumnValueFromNode("site_code", parentNode));
						itemCode =  checkNull(genericUtility.getColumnValueFromNode("item_code", parentNode));
						lotNo = checkNull(genericUtility.getColumnValueFromNode("lot_no", parentNode));
						locCode = checkNull(genericUtility.getColumnValueFromNode("loc_code", parentNode));
					}
														
      				System.out.println(">>>>>qcOrdNo dom1:"+qcOrdNo);
					System.out.println(">>>>>siteCode:"+siteCode);
					System.out.println(">>>>>itemCode:"+itemCode);
					System.out.println(">>>>>lotNo:"+lotNo);
					System.out.println(">>>>>locCode:"+locCode);
					
					sql = "select max (line_no) from qc_order_lots where qorder_no= ? ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1, qcOrdNo );
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						lineNoMax = Integer.parseInt( rs.getString(1)==null? "0":rs.getString(1).trim());
						System.out.println(">>>>>>>case 3 qc_order_lots lineNoMax:"+lineNoMax);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql = " SELECT stock.item_ser,stock.item_code,stock.unit,stock.loc_code,stock.lot_no, stock.lot_sl,   "
							+ " stock.site_code, stock.exp_date, stock.retest_date, stock.pack_code, stock.quantity  - case "
							+" when stock.alloc_qty is null then 0 else stock.alloc_qty end as quantity "
							+" FROM stock,invstat  "
							+" WHERE ( stock.inv_stat = invstat.inv_stat ) and  "
							+" ( stock.item_code = ? ) AND  "
							+" ( stock.site_code = ?) AND  "
							+" ( stock.lot_no = ?) AND  "
							+" ( stock.quantity  - case when stock.alloc_qty is null then 0 else stock.alloc_qty end >  0 ) order by  stock.lot_no, stock.lot_sl"; //Add location Code "QUAR"
					
					/*sql = " SELECT stock.item_ser,stock.item_code,stock.unit,stock.loc_code,stock.lot_no, stock.lot_sl,   "
							+ " stock.site_code, stock.exp_date, stock.retest_date, stock.pack_code, stock.quantity  - case "
							+" when stock.alloc_qty is null then 0 else stock.alloc_qty end as quantity "
							+" FROM stock,invstat  "
							+" WHERE ( stock.inv_stat = invstat.inv_stat ) and  "
							+" ( stock.item_code = ? ) AND  "
							+" ( stock.site_code = ?) AND  "
							+" ( stock.lot_no = ?) AND  "
							+" ( stock.inv_stat = 'TEST') AND  "
							+" ( stock.quantity  - case when stock.alloc_qty is null then 0 else stock.alloc_qty end >  0 )";*/
					pstmt= conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					pstmt.setString(2, siteCode);
					pstmt.setString(3, lotNo);
					rs= pstmt.executeQuery();
				    while(rs.next())
				    {
				    	System.out.println(">>>>>Display Stock details>>>>>");
				    	locCodeIss= rs.getString("loc_code") == null ? "" : rs.getString("loc_code");
				    	itemCode= rs.getString("item_code") == null ? "" : rs.getString("item_code");
				    	lotNo= rs.getString("lot_no") == null ? "" : rs.getString("lot_no");
				    	lotSl= rs.getString("lot_sl")== null ? "" : rs.getString("lot_sl");
				    	quantity= rs.getDouble("quantity");
				    	unit= rs.getString("unit") == null ? "" : rs.getString("unit");
				    	itemDescr = findValue(conn, "descr", "item", "item_code", itemCode);
						
				    	sql = "select line_no from qc_order_lots where qorder_no = ? and item_code= ? and lot_no= ? and lot_sl= ? and loc_code__issue= ? ";
						pstmt1= conn.prepareStatement(sql);
						pstmt1.setString(1, qcOrdNo);
						pstmt1.setString(2, itemCode);
						pstmt1.setString(3, lotNo);
						pstmt1.setString(4, lotSl);
						pstmt1.setString(5, locCodeIss);
						rs1 = pstmt1.executeQuery(); 
						if(rs1.next())
						{
						    dbLineNo= rs1.getInt("line_no");
						    System.out.println(">>>>>>In case 3 dbLineNo:"+dbLineNo);
						    valueXmlString.append("<Detail3 domID='" + dbLineNo + "' selected=\"Y\">\r\n");
							//valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
							valueXmlString.append("<qorder_no><![CDATA[").append(qcOrdNo).append("]]></qorder_no>\r\n");
							valueXmlString.append("<line_no><![CDATA[").append(domId).append("]]></line_no>\r\n");
							valueXmlString.append("<item_code><![CDATA[").append(checkNull(itemCode)).append("]]></item_code>\r\n");
							valueXmlString.append("<item_descr><![CDATA[").append(checkNull(itemDescr)).append("]]></item_descr>\r\n");
							valueXmlString.append("<loc_code__issue><![CDATA[").append(checkNull(locCodeIss)).append("]]></loc_code__issue>\r\n");
							valueXmlString.append("<loc_code><![CDATA[" ).append( "" ).append( "]]></loc_code>\r\n" );
							valueXmlString.append("<lot_no><![CDATA[").append(checkNull(lotNo)).append("]]></lot_no>\r\n");
							valueXmlString.append("<lot_sl><![CDATA[").append(checkNull(lotSl)).append("]]></lot_sl>\r\n");
							valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n");
							valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");
							valueXmlString.append("<no_art><![CDATA[").append(noArt).append("]]></no_art>\r\n");
							valueXmlString.append("<line_no_sl><![CDATA[").append(checkNull("1")).append("]]></line_no_sl>\r\n");
							System.out.println(">>>>>>>>in case3 Add qc_order:"+qcOrdNo);
							valueXmlString.append( "<qc_order><![CDATA[" ).append( qcOrdNo ).append( "]]></qc_order>\r\n");
							valueXmlString.append("</Detail3>\r\n");
						}
						else
						{
							lineNoMax=lineNoMax+1;
					    	System.out.println(">>>>>>lineNoMax:"+lineNoMax);
					    	valueXmlString.append("<Detail3 domID='" + lineNoMax + "' selected=\"N\">\r\n");
							//valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\" />\r\n");
							valueXmlString.append("<qorder_no><![CDATA[").append(qcOrdNo).append("]]></qorder_no>\r\n");
							valueXmlString.append("<line_no><![CDATA[").append(domId).append("]]></line_no>\r\n");
							valueXmlString.append("<item_code><![CDATA[").append(checkNull(itemCode)).append("]]></item_code>\r\n");
							valueXmlString.append("<item_descr><![CDATA[").append(checkNull(itemDescr)).append("]]></item_descr>\r\n");
							valueXmlString.append("<loc_code__issue><![CDATA[").append(checkNull(locCodeIss)).append("]]></loc_code__issue>\r\n");
							valueXmlString.append( "<loc_code><![CDATA[" ).append( "" ).append( "]]></loc_code>\r\n" );
							valueXmlString.append("<lot_no><![CDATA[").append(checkNull(lotNo)).append("]]></lot_no>\r\n");
							valueXmlString.append("<lot_sl><![CDATA[").append(checkNull(lotSl)).append("]]></lot_sl>\r\n");
							valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n");
							valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");
							valueXmlString.append("<no_art><![CDATA[").append(noArt).append("]]></no_art>\r\n");
							valueXmlString.append("<line_no_sl><![CDATA[").append(checkNull("1")).append("]]></line_no_sl>\r\n");
							System.out.println(">>>>>>>>in case3 Add qc_order:"+qcOrdNo);
							valueXmlString.append("<qc_order><![CDATA[" ).append( qcOrdNo ).append( "]]></qc_order>\r\n");
							valueXmlString.append("</Detail3>\r\n");
						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
				    }
				    pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
				}//end itm_default in case 3
				
			}//end case 3 in itemchange...	
			break;
			case 4: 
			{
				System.out.println(">>>>>>>>Calling case 4 IC:"+currentFormNo);
				lineNoMax = 0;
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
				{
					qcOrdNo = genericUtility.getColumnValue("qc_ord__no", dom1);
					System.out.println(">>>>>In case 4 qcOrdNo dom1:"+qcOrdNo);
					parentNodeList = dom2.getElementsByTagName("Detail3");
					System.out.println(">>>>>>>>parentNodeList:"+parentNodeList);
					parentNodeListLength = parentNodeList.getLength();
					System.out.println(">>>>>>>>in case 4:parentNodeListLength:"+parentNodeListLength);
					sql = "select max (line_no) from qc_order_lots where qorder_no= ? ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1, qcOrdNo );
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						lineNoMax = Integer.parseInt( rs.getString(1)==null? "0":rs.getString(1).trim());
						System.out.println(">>>>>>>case 4 qc_order_lots lineNoMax:"+lineNoMax);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					for(int ctr = 0; ctr < parentNodeListLength ; ctr++ )
					{
						parentNode = parentNodeList.item(ctr);
						System.out.println(">>>>>>>>>>parentNode:"+parentNode);
						qcOrdNo = genericUtility.getColumnValueFromNode("qorder_no", parentNode) ;
						lineNo = Integer.parseInt(genericUtility.getColumnValueFromNode("line_no", parentNode).trim());
						itemCode =  genericUtility.getColumnValueFromNode("item_code", parentNode);
						itemDescr = genericUtility.getColumnValueFromNode("item_descr", parentNode);
						locCodeIss = genericUtility.getColumnValueFromNode("loc_code__issue", parentNode);
						lotNo = genericUtility.getColumnValueFromNode("lot_no", parentNode);
						lotSl = genericUtility.getColumnValueFromNode("lot_sl", parentNode);
						quantity = Double.parseDouble(genericUtility.getColumnValueFromNode("quantity", parentNode));
						noArt = Integer.parseInt(genericUtility.getColumnValueFromNode("no_art", parentNode));
						containerNo = genericUtility.getColumnValueFromNode("line_no_sl", parentNode);
						unit = genericUtility.getColumnValueFromNode("unit", parentNode);//code added by sagar on 06/04/15
						System.out.println(">>>>>>Set Unit:"+unit);
						//Get Sample Qty from qc_sample_stk...
						sampleQty=0.0;
						/*sql = "SELECT qcsmp.qty_sample FROM qc_sample_stk qcsmp,qc_sample qcs WHERE qcsmp.tran_id = qcs.tran_id AND qcs.item_code= ?" 
						      + " AND qcsmp.lot_no = ? AND qcsmp.lot_sl = ? AND qcsmp.loc_code = ? AND qcs.qorder_no= ?";
						pstmt= conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);
						pstmt.setString(2, lotNo);
						pstmt.setString(3, lotSl);
						pstmt.setString(4, locCodeIss);
						pstmt.setString(5, qcOrdNo);
						rs = pstmt.executeQuery(); 
						if(rs.next())
						{
							sampleQty =rs.getDouble("qty_sample");
							System.out.println(">>>>>>>>>>QC Sample sampleQty:"+sampleQty);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						*/
				        //Prepare Selected row..
						sql = "select line_no,loc_code,loctype from qc_order_lots where qorder_no = ? and item_code= ? and lot_no= ? and lot_sl= ? and loc_code__issue= ? ";
						pstmt= conn.prepareStatement(sql);
						pstmt.setString(1, qcOrdNo);
						pstmt.setString(2, itemCode);
						pstmt.setString(3, lotNo);
						pstmt.setString(4, lotSl);
						pstmt.setString(5, locCodeIss);
						rs = pstmt.executeQuery(); 
						if(rs.next())
						{
							dbLineNo= rs.getInt("line_no");
							dbLocCode= rs.getString("loc_code") == null ? "" : rs.getString("loc_code");
							dbLocType= rs.getString("loctype") == null ? "" : rs.getString("loctype");
							
						    System.out.println(">>>>>>In case 4 dbLineNo Exist:"+dbLineNo);
						    valueXmlString.append("<Detail4 domID='" + dbLineNo + "' objContext = '"+currentFormNo+"' selected=\"Y\">\r\n");
							valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
							valueXmlString.append( "<qorder_no><![CDATA[" ).append( qcOrdNo ).append( "]]></qorder_no>\r\n"); 
							valueXmlString.append( "<line_no><![CDATA[" ).append( dbLineNo ).append( "]]></line_no>\r\n"); 
							valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode ).append( "]]></item_code>\r\n");
							valueXmlString.append( "<item_descr><![CDATA[" ).append( itemDescr ).append( "]]></item_descr>\r\n");
							valueXmlString.append( "<loc_code__issue><![CDATA[").append(checkNull(locCodeIss)).append("]]></loc_code__issue>\r\n");
							valueXmlString.append( "<loc_code><![CDATA[" ).append( dbLocCode ).append( "]]></loc_code>\r\n");
							valueXmlString.append( "<lot_no><![CDATA[" ).append( lotNo ).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[" ).append( lotSl ).append( "]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( quantity ).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<no_art><![CDATA[" ).append( noArt ).append( "]]></no_art>\r\n" );
							valueXmlString.append( "<sample_qty><![CDATA[" ).append( sampleQty ).append( "]]></sample_qty>\r\n" );
							valueXmlString.append( "<loctype><![CDATA[" ).append( dbLocType ).append( "]]></loctype>\r\n" );
							valueXmlString.append( "<line_no_sl><![CDATA[").append(checkNull(containerNo)).append("]]></line_no_sl>\r\n");
							System.out.println(">>>>>>>>in case4 Add qc_order:"+qcOrdNo);
							valueXmlString.append( "<qc_order><![CDATA[" ).append( qcOrdNo ).append( "]]></qc_order>\r\n");
							valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");//code added by sagar on 06/04/15
							valueXmlString.append("</Detail4>\r\n");
						}
						else
						{
							lineNoMax=lineNoMax+1;
							System.out.println(">>>>>>In case 4 lineNoMax:"+lineNoMax);
							valueXmlString.append("<Detail4 domID='" + lineNoMax + "' objContext = '"+currentFormNo+"' selected=\"N\">\r\n");
							valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\" />\r\n");
							valueXmlString.append( "<qorder_no><![CDATA[" ).append( qcOrdNo ).append( "]]></qorder_no>\r\n"); 
							valueXmlString.append( "<line_no><![CDATA[" ).append( lineNoMax ).append( "]]></line_no>\r\n"); 
							valueXmlString.append( "<item_code><![CDATA[" ).append( itemCode ).append( "]]></item_code>\r\n");
							valueXmlString.append( "<item_descr><![CDATA[" ).append( itemDescr ).append( "]]></item_descr>\r\n");
							valueXmlString.append( "<loc_code__issue><![CDATA[").append(checkNull(locCodeIss)).append("]]></loc_code__issue>\r\n");
							valueXmlString.append( "<loc_code><![CDATA[" ).append( "" ).append( "]]></loc_code>\r\n");
							valueXmlString.append( "<lot_no><![CDATA[" ).append( lotNo ).append( "]]></lot_no>\r\n" );
							valueXmlString.append( "<lot_sl><![CDATA[" ).append( lotSl ).append( "]]></lot_sl>\r\n" );
							valueXmlString.append( "<quantity><![CDATA[" ).append( quantity ).append( "]]></quantity>\r\n" );
							valueXmlString.append( "<no_art><![CDATA[" ).append( noArt ).append( "]]></no_art>\r\n" );
							valueXmlString.append( "<sample_qty><![CDATA[" ).append( sampleQty ).append( "]]></sample_qty>\r\n" );
							valueXmlString.append( "<loctype><![CDATA[" ).append( "" ).append( "]]></loctype>\r\n" );
							valueXmlString.append( "<line_no_sl><![CDATA[").append(checkNull(containerNo)).append("]]></line_no_sl>\r\n");
							System.out.println(">>>>>>>>in case4  Add qc_order:"+qcOrdNo);
							valueXmlString.append( "<qc_order><![CDATA[" ).append( qcOrdNo ).append( "]]></qc_order>\r\n");
							valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");//code added by sagar on 06/04/15
							valueXmlString.append("</Detail4>\r\n");
						}
					}//end for loop for get selected/...
				}//end itemdefault in case 4..
				/*if(currentColumn.trim().equalsIgnoreCase("loc_code"))
			    {
					System.out.println(">>>>In Detail4 IC loc_code:"+currentColumn);
					locCode = genericUtility.getColumnValue("loc_code", dom);
					System.out.println(">>>>>>>>>>>locCode:"+locCode);
					if(locCode!=null && locCode.trim().length() > 0)
					{
						if("REJ".equals(locCode))
						{
							locType="R";
						}
						else
						{
							locType="A";
						}
						String currDomStr = genericUtility.serializeDom(dom);
						System.out.println(">>>>> Before  currDomStr[" + currDomStr + "]");
						StringBuffer valueXmlStr = new StringBuffer();
						valueXmlStr.append("<loctype>").append("<![CDATA[" + locType + "]]>").append("</loctype>\r\n");
						currDomStr = currDomStr.replace("</Detail4>\r\n", valueXmlStr.toString() + "</Detail4>\r\n");
						System.out.println(">>>>>After currDomStr[" + currDomStr + "]");
						valueXmlString.append(currDomStr);
					}
			     }*/
			}//end case 4 in itemChange..
		 }//end switch
		 valueXmlString.append("</Root>");
		 System.out.println(">>>>>>>>>>>valueXmlString Final["+valueXmlString+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}	
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}	
					if(conn != null)
					{	
						conn.close();
						conn = null;
					}	
				}
			}
			catch(Exception d)
			{
				d.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}
	
	//Validation calling method start
    private int isValueExist(String tableName, String columnName, String columnValue,Connection conn)  throws Exception,ITMException
    {
    	PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = null;
        int cnt = 0 ;
        sql = " select count(1) as count from "+tableName+" where "+columnName+" = '" + columnValue + "' ";
        try
        {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if(rs.next())
            {
            	cnt = rs.getInt("count");
            }
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;
            System.out.println("cnt--------------"+cnt);
        }
        catch(Exception exception)
        {
            exception.printStackTrace();
            System.out.println("Exception ::" + exception.getMessage());
			throw new ITMException(exception);
        }
        return cnt;
	}
    //Validation calling method end
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}
	
	public double getRequiredDecimal(double actVal, int prec)
	{
		String fmtStr = "############0";
		String strValue = null;
		double retVal = 0;
		if (prec > 0)
		{
			fmtStr = fmtStr + "." + "000000000".substring(0, prec);
		}
		DecimalFormat decFormat = new DecimalFormat(fmtStr);
		retVal = Double.parseDouble(decFormat.format(actVal));
		if (Math.abs(retVal) == 0)
		{
			retVal = 0;
		}
		return retVal;
	}
	private String findValue(Connection conn, String columnName, String tableName, String columnName2, String value) throws ITMException, RemoteException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String findValue = "";
		try
		{
			sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 + "= ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, value);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				//findValue = rs.getString(1);
				findValue= rs.getString(1) == null ? "" : rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (findValue == null || findValue.trim().length()== 0)
			{
				findValue = "";
			}
		} catch (Exception e)
		{
			System.out.println("Exception in findValue ");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from findValue " + findValue);
		return findValue;
	}
		
	private String getAttribValue(Node detailNode, String nodeName, String attribStr)
	{
		String attribValue = "";
		try
		{
			String domStr = GenericUtility.getInstance().serializeDom(detailNode);
			Document dom = GenericUtility.getInstance().parseString(domStr);
			if( dom != null /*&& dom.getAttributes() != null*/)
			{
				Node attributeNode = dom.getElementsByTagName( nodeName ).item(0);
				attribValue = getAttribValue(attributeNode, attribStr);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}
	//
	private String getAttribValue(Node detailNode, String attribStr)
	{
		String attribValue = "";
		try
		{
			if( detailNode != null && detailNode.getAttributes() != null)
			{
				Node attribNode = detailNode.getAttributes().getNamedItem( attribStr );
				if( attribNode != null )
				{
					attribValue = checkNull( attribNode.getNodeValue() );
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : getAttribValue :" + e.getMessage());
		}
		return attribValue;
	}
	
	//private String getSelectValue(Node node) throws Exception
	private String getSelectValue(Node node) 
	{
		String selectValue = "";
		int selectLength=0;
		NamedNodeMap attrMap = node.getAttributes();
		selectLength=attrMap.getLength();
		System.out.println(">>>>>>>>>In getSelectValue attrMap.getLength():"+selectLength);
		if(selectLength == 5)
		{
			selectValue = attrMap.getNamedItem("selected").getNodeValue();
		}
		else
		{
			selectValue="N";
		}
		return selectValue;
	}
}
