package ibase.webitm.ejb.mfg;

import java.rmi.RemoteException;
import ibase.webitm.ejb.mfg.RunMRPPrc;
import java.util.*;
import java.util.Date;
import java.sql.*;

import org.w3c.dom.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import java.text.SimpleDateFormat;
import javax.naming.InitialContext;
import javax.ejb.Stateless;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;

@Stateless
public class RequirementIC extends ValidatorEJB implements RequirementICLocal, RequirementICRemote // SessionBean
{

	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	SimpleDateFormat simpleDateFormat = null;

	public String wfValData() throws RemoteException, ITMException
	{
		System.out.println("wfValData()...");
		return "";
	}

	// Item Change
	public String itemChanged() throws RemoteException, ITMException
	{
		System.out.println("itemChanged Called...");
		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 errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			System.out.println("xmlString.........::" + xmlString);
			System.out.println("xmlString1.........::" + xmlString1);
			System.out.println("xmlString2.........::" + xmlString2);
			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);
			}
			errString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		} catch (Exception e)
		{
			System.out.println("Exception :ProcessControlEJB :itemChanged(String,String):" + e.getMessage() + ":");
			errString = genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
		System.out.println("returning from ProcessControlEJB itemChanged");
		return errString;
	}

	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		String loginSiteCode = "";
		String sql = "";
		String columnValue = "";
		String userId = "";
		String errString = "";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		int lineNo = 0;
		StringBuffer valueXmlString = new StringBuffer();
		int currentFormNo = 0;

		boolean isError = false;

		PreparedStatement pstmtIns = null;
		PreparedStatement pstmtSel = null;
		PreparedStatement pstmtSel1 = null;
		PreparedStatement pstmt = null;

		NodeList parentNodeList = null;
		NodeList parentNodeList1 = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";
		String noOfBatchStr = "";
		String itemCode = "";
		String siteCode = "";
		String orderType = "MRP";
		String tranId = "MPSID";
		String status = "M";
		String unit = "";
		String insertSql = "";
		String selectSql = "";
		String itemCodeFr = "";
		String itemCodeTo = "";
		String deleteSql = "";
		String dueDate1 = "";
		String siteCodeFr = "";
		String siteCodeTo = "";
		String siteItemList="";
		String siteList="";
		String tranidIncrement="";
		String bomCode="",mpsOrder="";

		Document argDom = null;

		java.sql.Timestamp sysDate = new java.sql.Timestamp(System.currentTimeMillis());
		

		java.sql.Date dueDate = null;
		Timestamp newsysdate=null;

		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		int noOfBatch = 0;
		int delCnt = 0;
		int noOfItem = 0;
		int tranIdSuffix = 0;

		double batchQty = 0d;
		double quantity = 0d;
		double yieldPerc = 0d;

		String winName = ""; // Added by sachin on 26/6/13
		double batchReq = 0d;

		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;

			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");

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

			columnValue = genericUtility.getColumnValue(currentColumn, dom);
			if (objContext != null && objContext.trim().length() > 0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><Header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></Header>");
			switch (currentFormNo)
			{
			case 1:
				valueXmlString.append("<Detail1>");
				if (currentColumn.trim().equals("itm_default"))
				{
					valueXmlString.append("<site_code__fr>").append(loginSiteCode).append("</site_code__fr>");
					valueXmlString.append("<site_code__to>").append(loginSiteCode).append("</site_code__to>");
				}
				valueXmlString.append("</Detail1>");
				break;
			case 2:
				if (currentColumn.trim().equals("itm_default"))
				{
					int detCnt = 0;
					System.out.println("case2 dom1-----------"+dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code__fr", dom1);
					siteCodeTo = genericUtility.getColumnValue("site_code__to", dom1);

					/*
					 * "SELECT A.TRAN_ID, A.SITE_CODE, A.ITEM_CODE, B.DESCR, A.QUANTITY, A.DUE_DATE, A.UNIT, A.YIELD_PERC "
					 * + "FROM MPS_ORDER A, ITEM B  "+
					 * "WHERE ( A.ITEM_CODE = B.ITEM_CODE (+)) " +
					 * "AND A.SITE_CODE >= ? AND A.SITE_CODE <= ? "+
					 * "AND A.STATUS IN ('P','T','M')";
					 */

					/* Change By Sachin on 26/6/2013 */

					selectSql = "SELECT  A.SITE_CODE, A.ITEM_CODE, B.DESCR, A.UNIT, C.BATCH_QTY, SUM(A.QUANTITY) AS QUANTITY, C.BOM_CODE ,'MPS' AS MPSORDER " + "FROM MPS_ORDER A, ITEM B ,BOM C " + "WHERE ( A.ITEM_CODE = B.ITEM_CODE (+)) " + "AND A.SITE_CODE >= ? AND A.SITE_CODE <=? " + "AND A.STATUS IN ('P','T','M') " + "AND A.BOM_CODE=C.BOM_CODE " + "GROUP BY A.SITE_CODE, A.ITEM_CODE, B.DESCR, A.UNIT, C.BATCH_QTY, C.BOM_CODE";

					pstmt = conn.prepareStatement(selectSql);
					pstmt.setString(1, siteCodeFr);
					pstmt.setString(2, siteCodeTo);
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						detCnt++;
						lineNo++;
						quantity = rs.getDouble("QUANTITY");
						batchQty = rs.getDouble("BATCH_QTY");
						batchReq = (quantity / batchQty);
						System.out.println(" aFTER CALUCLATION Quantity is =" + quantity + " batch quantity is =" + batchQty + "batch Requirement is =" + batchReq);

						/*
						 * dueDate = rs.getDate("DUE_DATE"); if (dueDate !=
						 * null) { dueDate1 = sdf.format(dueDate); } else {
						 * dueDate1 = ""; }
						 */
						valueXmlString.append("<Detail2 domID='" + lineNo + "'  objContext = '" + currentFormNo + "' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<site_code>").append("<![CDATA[" + rs.getString("SITE_CODE") + "]]>").append("</site_code>\r\n");
						valueXmlString.append("<item_code>").append("<![CDATA[" + rs.getString("ITEM_CODE").trim() + "]]>").append("</item_code>\r\n");
						valueXmlString.append("<item_descr protect = '1'>").append("<![CDATA[" + rs.getString("DESCR") + "]]>").append("</item_descr>\r\n");
						valueXmlString.append("<unit protect = '1'>").append("<![CDATA[" + rs.getString("UNIT") + "]]>").append("</unit>\r\n");
						valueXmlString.append("<no_of_batch protect = '1'>").append("<![CDATA[" + batchReq + "]]>").append("</no_of_batch>\r\n");
						valueXmlString.append("<quantity>").append("<![CDATA[" + rs.getDouble("QUANTITY") + "]]>").append("</quantity>\r\n");
						valueXmlString.append("<bom_code>").append("<![CDATA[" + rs.getString("BOM_CODE") + "]]>").append("</bom_code>\r\n");
						valueXmlString.append("<mpsorder>").append("<![CDATA[" + rs.getString("MPSORDER") + "]]>").append("</mpsorder>\r\n");
						valueXmlString.append("</Detail2>");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if (detCnt == 0)
					{	lineNo++;//Added by chandrashekar on 06/05/2014
						// valueXmlString.append("<Detail2>\r\n");
						valueXmlString.append("<Detail2 domID='" + lineNo + "'  objContext = '" + currentFormNo + "' selected=\"N\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<site_code>").append("<![CDATA[" + siteCodeFr + "]]>").append("</site_code>\r\n");
						//valueXmlString.append("<site_code>").append("").append("</site_code>\r\n");
						valueXmlString.append("<item_code>").append("").append("</item_code>\r\n");
						valueXmlString.append("<item_descr protect = '1'>").append("").append("</item_descr>\r\n");
						valueXmlString.append("<quantity>").append("0").append("</quantity>\r\n");
						valueXmlString.append("<no_of_batch protect = '1'>").append("0").append("</no_of_batch>\r\n");
						valueXmlString.append("<unit protect = '1'>").append("").append("</unit>\r\n");
						valueXmlString.append("<bom_code>").append("").append("</bom_code>\r\n");
						valueXmlString.append("<mpsorder>").append("").append("</mpsorder>\r\n");
						valueXmlString.append("</Detail2>");
					}

					/* End Change By Sachin on 26/6/2013 */
				}
				
				break;

			// added by akhilesh
			case 3:
				if (currentColumn.trim().equals("itm_default"))
				{
					TreeSet itemTreeSet = new TreeSet();
					TreeSet siteTreeSet = new TreeSet();
					ArrayList tranIdList = new ArrayList();

					HashMap mpsDataMap = new HashMap();

					int newDataCnt = 0;
					int noOfSite = 0;
					String tranIdDom = "";
					String qtyStr = "";
					siteCodeFr = genericUtility.getColumnValue("site_code__fr", dom1);
					siteCodeTo = genericUtility.getColumnValue("site_code__to", dom1);
					mpsOrder = genericUtility.getColumnValue("mpsorder", dom2);
					System.out.println("mpsOrder["+mpsOrder+"]");
					
					java.util.Date dt = new java.util.Date();
					SimpleDateFormat sdf1= new SimpleDateFormat(genericUtility.getDBDateFormat());
					newsysdate = java.sql.Timestamp.valueOf(sdf1.format(dt)+" 00:00:00.0");
					System.out.println("newsysdate["+newsysdate+"]");
					
					if(!"MPS".equalsIgnoreCase(mpsOrder))
					{	
						insertSql = "INSERT INTO MPS_ORDER(TRAN_ID, ORDER_TYPE, ITEM_CODE, SITE_CODE, QUANTITY, ORD_DATE, DUE_DATE, STATUS, STATUS_DATE, YIELD_PERC, UNIT , BOM_CODE ) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
						pstmtIns = conn.prepareStatement(insertSql);
					}

					selectSql = "SELECT BATCH_QTY,BOM_CODE FROM BOM WHERE BOM_CODE = (SELECT BOM_CODE FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ? )";
					pstmtSel = conn.prepareStatement(selectSql);

					selectSql = "SELECT UNIT,YIELD_PERC FROM ITEM WHERE ITEM_CODE = ? ";
					pstmtSel1 = conn.prepareStatement(selectSql);
					parentNodeList1 = dom.getElementsByTagName("Detail3");
					System.out.println("parentNodeList1   ["+parentNodeList1+"]");
					parentNodeList = dom2.getElementsByTagName("Detail2");
					
					parentNodeListLength = parentNodeList.getLength();
					System.out.println("parentNodeList["+parentNodeList+"]");
					for (int p = 0; p < parentNodeListLength; p++)
					{
						tranIdDom = "";
						itemCode = "";
						siteCode = "";
						qtyStr = "";
						quantity = 0d;
						parentNode = parentNodeList.item(p);
						childNodeList = parentNode.getChildNodes();

						childNodeListLength = childNodeList.getLength();
						for (int c = 0; c < childNodeListLength; c++)
						{
							childNode = childNodeList.item(c);
							childNodeName = childNode.getNodeName();

							System.out.println("childNodeName :: " + childNodeName);
							if (childNodeName.equals("tran_id"))
							{
								if (childNode.getFirstChild() != null)
								{
									tranIdDom = checkNull(childNode.getFirstChild().getNodeValue());
								}
							} else if (childNodeName.equals("item_code"))
							{
								if (childNode.getFirstChild() != null)
								{
									itemCode = checkNull(childNode.getFirstChild().getNodeValue());
								}
							} else if (childNodeName.equals("site_code"))
							{
								if (childNode.getFirstChild() != null)
								{
									siteCode = checkNull(childNode.getFirstChild().getNodeValue());
								}
							} else if (childNodeName.equals("quantity"))
							{
								if (childNode.getFirstChild() != null)
								{
									qtyStr = childNode.getFirstChild().getNodeValue();
									if (qtyStr != null && qtyStr.trim().length() > 0)
									{
										try
										{
											quantity = Double.parseDouble(qtyStr);
										} catch (NumberFormatException n)
										{
											quantity = 0;
										}
									}
								}
							}
						}// End of Inner for loop
						if (itemCode != null && itemCode.trim().length() > 0)
						{
							itemTreeSet.add(itemCode);
							System.out.println("itemTreeSet1["+itemTreeSet+"]");
						}
						if (siteCode != null && siteCode.trim().length() > 0)
						{
							siteTreeSet.add(siteCode);
							System.out.println("siteTreeSet1["+siteTreeSet+"]");
						}
						if (tranIdDom == null || tranIdDom.trim().length() == 0)
						{	
							
							newDataCnt++;
							ArrayList newDataList = new ArrayList();
							newDataList.add(siteCode);
							newDataList.add(itemCode);
							newDataList.add(quantity);
							mpsDataMap.put("" + newDataCnt, newDataList);
							
							pstmtSel1.setString(1, itemCode);
							rs = pstmtSel1.executeQuery();
							if (rs.next())
							{
								unit = rs.getString("UNIT");
								yieldPerc = rs.getDouble("YIELD_PERC");
							}
							pstmtSel1.clearParameters();
							rs.close();
							rs = null;

							pstmtSel.setString(1, siteCode);
							pstmtSel.setString(2, itemCode);
							rs = pstmtSel.executeQuery();
							if (rs.next())
							{
								batchQty = rs.getDouble("BATCH_QTY");
								bomCode = rs.getString("BOM_CODE");
							}
							pstmtSel.clearParameters();
							rs.close();
							rs = null;

							// quantity = batchQty * noOfBatch;

							//tranIdSuffix++;
							if(!"MPS".equalsIgnoreCase(mpsOrder))
							{
							tranidIncrement=generateTranId("w_mps_order",loginSiteCode,orderType,conn);	//ADDED BY CHANDRASHEKAR ON 04/06/2014
							String newTranId = "";
							//newTranId = tranId;
							//System.out.println("newTranId@@@["+newTranId+"]");
							//newTranId = newTranId + tranIdSuffix;
							newTranId = newTranId + tranidIncrement;
							System.out.println("newTranId["+newTranId+"]");
							
							tranIdList.add(newTranId);
							
							pstmtIns.setString(1, newTranId);
							pstmtIns.setString(2, orderType);
							pstmtIns.setString(3, itemCode);
							pstmtIns.setString(4, siteCode);
							pstmtIns.setDouble(5, quantity);
							pstmtIns.setTimestamp(6, sysDate);
							pstmtIns.setTimestamp(7, newsysdate);
							pstmtIns.setString(8, status);
							pstmtIns.setTimestamp(9, sysDate);
							pstmtIns.setDouble(10, yieldPerc);
							pstmtIns.setString(11, unit);
							pstmtIns.setString(12, bomCode);
							pstmtIns.addBatch();
							pstmtIns.clearParameters();
							}
						}
					}
					noOfItem = itemTreeSet.size();
					System.out.println("itemTreeSet:["+itemTreeSet+"]");
					if (noOfItem > 0)
					{
						itemCodeFr = (String) itemTreeSet.first();
						itemCodeTo = (String) itemTreeSet.last();
						System.out.println("itemCodeFr["+itemCodeFr+"]");
						System.out.println("itemCodeTo["+itemCodeTo+"]");
					}

					noOfSite = siteTreeSet.size();
					System.out.println("siteTreeSet:["+siteTreeSet+"]");
					if (noOfSite > 0)
					{
						siteCodeFr = (String) siteTreeSet.first();
						siteCodeTo = (String) siteTreeSet.last();
						
						System.out.println("siteCodeFr["+siteCodeFr+"]");
						System.out.println("siteCodeTo["+siteCodeTo+"]");
					}
					if(!"MPS".equalsIgnoreCase(mpsOrder))
					{
					int insCnt[] = null;
					insCnt = pstmtIns.executeBatch();
					if (insCnt.length > 0)
					{
						System.out.println("Record inserted into mps_order successfully for tran id =[" + insCnt.length + "]");
						conn.commit();
					}
					}
					Calendar cal = Calendar.getInstance();
					Date d = new Date();
					String runDate = simpleDateFormat.format(d);
					cal.setTime(d);
					cal.set(Calendar.DATE, 1);
					d = cal.getTime();
					String fromDate = simpleDateFormat.format(d);
					cal.setTime(d);
					cal.add(Calendar.MONTH, 3);
					cal.set(Calendar.DATE, 0);
					d = cal.getTime();
					String toDate = simpleDateFormat.format(d);
					System.out.println("From Date :" + fromDate + " To Date :" + toDate);
					System.out.println("siteCodeFr:"+siteCodeFr+"siteCodeTo"+siteCodeTo);
					System.out.println("itemCodeFr:"+itemCodeFr+"itemCodeTo"+itemCodeTo);
					
					System.out.println("Detail len==="+parentNodeList.getLength());
					for ( int ctr1 = 0; ctr1 < parentNodeList.getLength(); ctr1++ )  //Loop for each node of current detail
					{
						siteCode = checkNull(genericUtility.getColumnValueFromNode( "site_code", parentNodeList.item(ctr1) ));
						itemCode = checkNull(genericUtility.getColumnValueFromNode( "item_code", parentNodeList.item(ctr1) ));
						System.out.println("siteCode=="+siteCode+"itemCode=="+itemCode);
						System.out.println("ctr1=="+ctr1);
						if(ctr1 > 0)
						{
							siteItemList = siteItemList + ",'"+siteCode.trim()+itemCode.trim()+"'" ;
							siteList = siteList + ",'"+siteCode.trim()+"'" ;
						}
						else
						{
							siteItemList = "'"+siteCode.trim()+itemCode.trim()+"'" ;
							siteList = "'"+siteCode.trim()+"'" ;
						}
							
						System.out.println("siteItemList=="+siteItemList);
						System.out.println("siteList=="+siteList);
					}

					StringBuffer argData = new StringBuffer();
					argData = new StringBuffer("<?xml version=\"1.0\"?><Root><Header><editFlag>");
					argData.append(editFlag).append("</editFlag></Header>");
					argData.append("<Detail\r\n>");
					argData.append("<date_from>").append(fromDate).append("</date_from>\r\n");
					argData.append("<date_to>").append(toDate).append("</date_to>\r\n");
					argData.append("<site_item_list>").append(siteItemList).append("</site_item_list>\r\n");//chandrashekar 0n 19-03-2014
					argData.append("<site_list>").append(siteList).append("</site_list>\r\n");//chandrashekar on 10-07-2014
					argData.append("<site_code__from>").append(siteCodeFr).append("</site_code__from>\r\n");
					argData.append("<site_code__to>").append(siteCodeTo).append("</site_code__to\r\n>");
					argData.append("<item_code__from>").append(itemCodeFr).append("</item_code__from>\r\n");
					argData.append("<item_code__to>").append(itemCodeTo).append("</item_code__to>\r\n");
					argData.append("<item_ser__from>").append("0").append("</item_ser__from>\r\n");
					argData.append("<item_ser__to>").append("ZZ").append("</item_ser__to>\r\n");
					argData.append("<process_cycle>").append("3").append("</process_cycle>\r\n");
					argData.append("<run_date>").append(runDate).append("</run_date>\r\n"); // 01-10-2007
					                                                                        // manoharan
					argData.append("<dis_plan>").append("N").append("</dis_plan>\r\n");
					argData.append("<mfg_plan>").append("N").append("</mfg_plan>\r\n");
					argData.append("<mat_plan>").append("Y").append("</mat_plan>\r\n");  
					argData.append("<made_to_stock>").append("0").append("</made_to_stock>\r\n");
					argData.append("<made_to_order>").append("0").append("</made_to_order>\r\n");//0
					argData.append("<balance_intersite>").append("N").append("</balance_intersite>\r\n");
					argData.append("<gen_ind>").append("N").append("</gen_ind>\r\n");
					argData.append("<wizard>").append("3").append("</wizard>\r\n");  
					argData.append("</Detail>");
					argData.append("</Root>");

					argDom = genericUtility.parseString(argData.toString());

					String mrpGetData = "";
					RunMRPPrc runMRPObj = new RunMRPPrc();

					/* Added by sachin */

					//parentNode = parentNodeList.item(0);
					parentNode = parentNodeList1.item(0);
					
					System.out.println("parentNode["+parentNode+"]");
					winName = this.getWinName(parentNode);
					System.out.println("winName["+winName+"]");
					mrpGetData = runMRPObj.getData(argDom, dom2, winName, xtraParams);

					/* End Added by sachin */
					System.out.println("mrpGetData =[" + mrpGetData + "]");

					String tranIdStr = "'";

					if (tranIdList != null && tranIdList.size() > 0)
					{
						for (int i = 0; i < tranIdList.size(); i++)
						{
							tranIdStr = tranIdStr + tranIdList.get(i) + "','";
						}
					}
					if (tranIdStr.length() >= 2)
					{
						tranIdStr = tranIdStr.substring(0, tranIdStr.length() - 2);
					} else
					{
						tranIdStr = "";
					}

					/*if (tranIdStr != null && tranIdStr.trim().length() > 0)
					{
						deleteSql = "DELETE FROM MPS_ORDER WHERE TRAN_ID IN(" + tranIdStr + ")";
						pstmt = conn.prepareStatement(deleteSql);
						delCnt = pstmt.executeUpdate();
						if (delCnt > 0)
						{
							System.out.println("Record deleted from mps_order successfully for tran id =[" + tranIdStr + "]");
						}
						pstmt.close();
						pstmt = null;
					}*/
					System.out.println("mrpGetData>>>>>"+mrpGetData);
					return mrpGetData;

				}
				break;
			// ended by akhilesh

			}
			valueXmlString.append("</Root>");
		} catch (Exception e)
		{
			isError = true;
			System.out.println("Exception :[RequirementIC][itemChanged::case 1:] :==>\n" + e.getMessage());
			e.printStackTrace();
			return genericUtility.createErrorString(e);
			// throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (!isError)
					{
						conn.commit();
					} else
					{
						conn.rollback();
					}
					if (rs != null)
					{
						rs.close();
						rs = null;
					}
					if (pstmtIns != null)
					{
						pstmtIns.close();
						pstmtIns = null;
					}
					if (pstmtSel != null)
					{
						pstmtSel.close();
						pstmtSel = null;
					}
					if (pstmtSel1 != null)
					{
						pstmtSel1.close();
						pstmtSel1 = null;
					}
					conn.close();
					conn = null;
				}
			} catch (Exception e)
			{
			}
		}
		return valueXmlString.toString();
		
	}
	

	// Validation
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";
		System.out.println("wfValData(String)>>>");
		try
		{
			dom = parseString(xmlString);
			System.out.println("RequirementIC : wfValData(String xmlString) : ==>\n" + xmlString);
			System.out.println("RequirementIC : wfValData(String xmlString1) : ==>\n" + xmlString1);
			System.out.println("RequirementIC : wfValData(String xmlString2) : ==>\n" + xmlString2);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0)
			{
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
		} catch (Exception e)
		{
			System.out.println("Exception : RequirementIC : wfValData(String xmlString) : ==>\n" + e.getMessage());
			e.printStackTrace();
			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;
		String childNodeName = "";
		String errString = "";
		int ctr, currentFormNo = 0;
		int childNodeListLength;
		int count = 0;
		int count1 = 0;

		String siteCodeFr = "";
		String siteCodeTo = "";
		String errCode = "";
		String userId = "";
		String sql = "";
		String itemcodeTo = "";
		String siteCode = "";
		String itemCode = "";
		String quantity = "";
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			conn = getConnection();
			userId = getValueFromXTRA_PARAMS(xtraParams, "userId");
			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();

					int cnt;
					System.out.println("childNodeName 0. : " + childNodeName);
					if (childNodeName.equals("site_code__fr") || childNodeName.equals("site_code__to"))
					{
						siteCodeFr = genericUtility.getColumnValue("site_code__fr", dom);
						siteCodeTo = genericUtility.getColumnValue("site_code__to", dom);
						if (siteCodeFr == null || siteCodeFr.trim().length() == 0)
						{
							errCode = "VMSITE11";
							errString = getErrorString("site_code__fr", errCode, userId);
							break;
						}else
						{  sql = "SELECT COUNT(*) COUNT FROM SITE WHERE SITE_CODE = ? ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCodeFr);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								count = rs.getInt("COUNT");
							}
							rs.close();
							rs = null;
							if (count == 0)
							{
								errCode = "VMSITEFR";
								errString = getErrorString(childNodeName, errCode, userId);
								break;
							}
							
						}
						if (siteCodeTo == null || siteCodeTo.trim().length() == 0)
						{
							errCode = "VMSITE12";
							errString = getErrorString("site_code__to", errCode, userId);
							break;
						}else
						{	sql = "SELECT COUNT(*) COUNT FROM SITE WHERE SITE_CODE = ? ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCodeTo);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								count = rs.getInt("COUNT");
							}
							rs.close();
							rs = null;
							if (count == 0)
							{
								errCode = "VMSITETO";
								errString = getErrorString(childNodeName, errCode, userId);
								break;
							}
							
						}
					
						

					}
				}
			}// End of Case 1
				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("childNodeName["+childNodeName+"]");
					int cnt;
					System.out.println("childNodeName 0. : " + childNodeName);
					if (childNodeName.equals("site_code"))
					{
						siteCode = genericUtility.getColumnValue("site_code", dom);
						if (siteCode == null || siteCode.trim().length() == 0)
						{
							errCode = "VMSITECD";
							errString = getErrorString("site_code", errCode, userId);
							break;
						}

						sql = "SELECT COUNT(*) COUNT FROM SITE WHERE SITE_CODE = ? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, siteCode);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							count = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						if (count == 0)
						{
							errCode = "VMSITE";
							errString = getErrorString(childNodeName, errCode, userId);
							break;
						}
					} else if (childNodeName.equals("item_code"))
					{
						itemCode = genericUtility.getColumnValue("item_code", dom);
						if (itemCode == null || itemCode.trim().length() == 0)
						{
							errCode = "VMITEMCD";
							errString = getErrorString("item_code", errCode, userId);
							break;
						}

						sql = "SELECT COUNT(*) COUNT FROM ITEM WHERE ITEM_CODE = ? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							count = rs.getInt("COUNT");
						}
						rs.close();
						rs = null;
						if (count == 0)
						{
							errCode = "VMITEM1";
							errString = getErrorString(childNodeName, errCode, userId);
							break;
						}
					} else if (childNodeName.equals("quantity"))
					{
						quantity = genericUtility.getColumnValue("quantity", dom);
						if (quantity == null || quantity.trim().length() == 0 || quantity.equals("0"))
						{
							errCode = "VMWEQTY";
							errString = getErrorString("quantity", errCode, userId);
							break;
						}
					}
				}
			}// End of Case 1
				break;
			}// End of Switch block
		} catch (Exception e)
		{
			System.out.println("Exception ::" + e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		} finally
		{
			try
			{
				// System.out.println("Closing Connection.....");
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				conn.close();
				conn = null;
			} catch (Exception se)
			{
			}
		}
		return errString;
	}

	private String checkNull(String value)
	{
		if (value == null)
		{
			value = "";
		}
		return value;
	}

	/* Added by sachin on 26/6/13 */
	private String getWinName(Node node) throws Exception
	{
		String objName = "";
		NamedNodeMap attrMap = node.getAttributes();
		objName = attrMap.getNamedItem("objName").getNodeValue();
		System.out.println(" Object Name is-->" + objName);
		return "w_" + objName;
	}
	/* End Added by sachin on 26/6/13 */
	//Added by chandrashekar on 12/03/2014
	//for item code pop help 
	public String getItemCodeList(String itemCode,String search,String dbID,String siteCode) throws ITMException 
	{
		System.out.println("itemCode cal------------------["+itemCode+"]");
		System.out.println("search cal------------------["+search+"]");
		System.out.println("dbID cal------------------["+dbID+"]");
		System.out.println("siteCode cal------------------["+siteCode+"]");
		String sql = "",sql1="";
		String selectSql = "";
		int detCnt=0;
		
		double quantity=0,batchQty=0,batchReq=0;
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try 
		{	
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			if(search != null && search.trim().length() > 0)
			{	
				//sql = " SELECT ITEM_CODE,DESCR,UNIT,BOM_CODE FROM ITEM  WHERE ITEM_CODE LIKE  ? ";
				sql = "SELECT S.ITEM_CODE,I.DESCR,I.UNIT,S.BOM_CODE FROM SITEITEM S,ITEM I,BOM B " +
						"WHERE S.ITEM_CODE=I.ITEM_CODE AND B.ITEM_CODE=I.ITEM_CODE AND " +
						"S.BOM_CODE = B.BOM_CODE AND S.SITE_CODE = ? AND S.ITEM_CODE LIKE ? ";
				System.out.println("sql:::"+sql);
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCode);
				pstmt.setString(2, search+"%");
				rs = pstmt.executeQuery();
				int num = 1;
				while (rs.next())
				{
					detCnt++;
					System.out.println("bomcode["+rs.getString("BOM_CODE"));
					if(rs.getString("BOM_CODE") != null)
					{	
						sql1 = "  SELECT BATCH_QTY FROM BOM WHERE BOM_CODE = ? ";
						pstmt1 =  conn.prepareStatement(sql1);
						pstmt1.setString(1,rs.getString("BOM_CODE"));
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							batchQty =  rs1.getDouble("BATCH_QTY");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					
					valueXmlString.append("<ITEM domID='" + num	+ "' selected = 'N'>\r\n");					
					valueXmlString.append("<ITEM_CODE>").append("<![CDATA[" + rs.getString("ITEM_CODE") + "]]>").append("</ITEM_CODE>\r\n");
					valueXmlString.append("<ITEM_DESCR protect = '1'>").append("<![CDATA[" + rs.getString("DESCR") + "]]>").append("</ITEM_DESCR>\r\n");
					valueXmlString.append("<UNIT protect = '1'>").append("<![CDATA[" + rs.getString("UNIT") + "]]>").append("</UNIT>\r\n");
					valueXmlString.append("<DOMID><![CDATA[").append(dbID).append("]]></DOMID>\r\n");
					valueXmlString.append("<site_code><![CDATA[").append(siteCode).append("]]></site_code>\r\n");
					valueXmlString.append("<BOM_CODE protect = '1'>").append("<![CDATA[" + rs.getString("BOM_CODE") + "]]>").append("</BOM_CODE>\r\n");
					valueXmlString.append("<BATCH_QTY><![CDATA[").append(batchQty).append("]]></BATCH_QTY>\r\n");
					valueXmlString.append("</ITEM>\r\n");
					num++;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
			}
			
			else if(itemCode == null || itemCode.trim().length() == 0)
			{	
				//selectSql = " SELECT ITEM_CODE,DESCR,UNIT,BOM_CODE FROM ITEM WHERE SITE_CODE = ? ORDER BY ITEM_CODE  ";
				selectSql = "SELECT S.ITEM_CODE,I.DESCR,I.UNIT,S.BOM_CODE FROM SITEITEM S,ITEM I,BOM B " +
							"WHERE S.ITEM_CODE=I.ITEM_CODE AND B.ITEM_CODE=I.ITEM_CODE AND " +
							"S.BOM_CODE = B.BOM_CODE AND S.SITE_CODE = ? ORDER BY S.ITEM_CODE";
				pstmt = conn.prepareStatement(selectSql);
				pstmt.setString(1, siteCode);
				rs = pstmt.executeQuery();
				
				int num = 1;
				while (rs.next())
				{
					detCnt++;
					System.out.println("bomcode["+rs.getString("BOM_CODE"));
					if(rs.getString("BOM_CODE") != null)
					{	
						sql1 = "  SELECT BATCH_QTY FROM BOM WHERE BOM_CODE = ? ";
						pstmt1 =  conn.prepareStatement(sql1);
						pstmt1.setString(1,rs.getString("BOM_CODE"));
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							batchQty =  rs1.getDouble("BATCH_QTY");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					valueXmlString.append("<ITEM domID='" + num	+ "' selected = 'N'>\r\n");					
					valueXmlString.append("<ITEM_CODE>").append("<![CDATA[" + rs.getString("ITEM_CODE") + "]]>").append("</ITEM_CODE>\r\n");
					valueXmlString.append("<ITEM_DESCR protect = '1'>").append("<![CDATA[" + rs.getString("DESCR") + "]]>").append("</ITEM_DESCR>\r\n");
					valueXmlString.append("<UNIT protect = '1'>").append("<![CDATA[" + rs.getString("UNIT") + "]]>").append("</UNIT>\r\n");
					valueXmlString.append("<DOMID><![CDATA[").append(dbID).append("]]></DOMID>\r\n");
					valueXmlString.append("<site_code><![CDATA[").append(siteCode).append("]]></site_code>\r\n");
					valueXmlString.append("<BOM_CODE protect = '1'>").append("<![CDATA[" + rs.getString("BOM_CODE") + "]]>").append("</BOM_CODE>\r\n");
					valueXmlString.append("<BATCH_QTY><![CDATA[").append(batchQty).append("]]></BATCH_QTY>\r\n");
					valueXmlString.append("</ITEM>\r\n");
					num++;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				
				
			}
			else{
				//selectSql = " SELECT ITEM_CODE,DESCR,UNIT,BOM_CODE FROM ITEM  WHERE ITEM_CODE LIKE  ? ";
				selectSql = "SELECT S.ITEM_CODE,I.DESCR,I.UNIT,S.BOM_CODE FROM SITEITEM S,ITEM I,BOM B " +
							"WHERE S.ITEM_CODE=I.ITEM_CODE AND B.ITEM_CODE=I.ITEM_CODE AND " +
							"S.BOM_CODE = B.BOM_CODE AND S.SITE_CODE = ? AND S.ITEM_CODE LIKE ? ";
				pstmt = conn.prepareStatement(selectSql);
				pstmt.setString(1, siteCode);
				pstmt.setString(2, itemCode+"%");
				rs = pstmt.executeQuery();
				int num = 1;
				while (rs.next())
				{
					detCnt++;
					System.out.println("bomcode["+rs.getString("BOM_CODE"));
					if(rs.getString("BOM_CODE") != null)
					{	
						sql1 = "  SELECT BATCH_QTY FROM BOM WHERE BOM_CODE = ? ";
						pstmt1 =  conn.prepareStatement(sql1);
						pstmt1.setString(1,rs.getString("BOM_CODE"));
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							batchQty =  rs1.getDouble("BATCH_QTY");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					valueXmlString.append("<ITEM domID='" + num	+ "' selected = 'N'>\r\n");					
					valueXmlString.append("<ITEM_CODE>").append("<![CDATA[" + rs.getString("ITEM_CODE") + "]]>").append("</ITEM_CODE>\r\n");
					valueXmlString.append("<ITEM_DESCR protect = '1'>").append("<![CDATA[" + rs.getString("DESCR") + "]]>").append("</ITEM_DESCR>\r\n");
					valueXmlString.append("<UNIT protect = '1'>").append("<![CDATA[" + rs.getString("UNIT") + "]]>").append("</UNIT>\r\n");
					valueXmlString.append("<BOM_CODE protect = '1'>").append("<![CDATA[" + rs.getString("BOM_CODE") + "]]>").append("</BOM_CODE>\r\n");
					valueXmlString.append("<DOMID><![CDATA[").append(dbID).append("]]></DOMID>\r\n");
					valueXmlString.append("<site_code><![CDATA[").append(siteCode).append("]]></site_code>\r\n");
					valueXmlString.append("<BATCH_QTY><![CDATA[").append(batchQty).append("]]></BATCH_QTY>\r\n");
					valueXmlString.append("</ITEM>\r\n");
					num++;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}	
				
		
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception :RequirementIc :getItemCodeList(String,String):"	+ e.getMessage() + ":");
			valueXmlString = valueXmlString.append(genericUtility.createErrorString(e));
		} 
		finally 
		{
			try 
			{
				if (conn != null && !conn.isClosed()) 
				{					
					conn.close();
					conn = null;
				}
			} 
			catch (Exception e) 
			{
				System.out.println("Exception :RequirementIc :getItemCodeList(String,String) :==>\n"+ e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append("</Root>\r\n");
		System.out.println("\n****ValueXmlString ::" + valueXmlString.toString()	+ ":********");
		return valueXmlString.toString();
	}
	private String generateTranId( String windowName, String siteCode,String orderType, Connection conn )throws ITMException
    {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";
		java.sql.Timestamp currDate = null;
		java.sql.Date effDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		 try
         {

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
				//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
					keyString = rs.getString("KEY_STRING");
					keyCol = rs.getString("TRAN_ID_COL");
					tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			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>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<order_type>" + orderType + "</order_type>";
			xmlValues = xmlValues +        "<ord_date>" + currDateStr + "</ord_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
         }
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
						rs.close();
						rs = null;
				}
				if (pstmt != null)
				{
						pstmt.close();
						pstmt = null;
				}
			}
			catch(Exception e){}
		}
        return tranId;
     }//generateTranTd()	
	

}


