

/********************************************************
	Title : RunMRPWiz (MF3HSUP005)
	Date  : 27/NOV/13
	Developer: Kunal Mandhre

 ********************************************************/

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 java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; 
import java.sql.Timestamp;
@Stateless  

public class RunMRPWiz extends ValidatorEJB implements RunMRPWizLocal, RunMRPWizRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();

	//method for validation
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = "";
		System.out.println("wfValdata() called for RunMRPWiz");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			System.out.println("@@@@@@@@@@@@@@@xmlString["+xmlString+"]@@@@@@@@@@@@@@@@@@");
			System.out.println("@@@@@@@@@@@@@@@xmlString1["+xmlString1+"]@@@@@@@@@@@@@@@@@@");
			System.out.println("@@@@@@@@@@@@@@@xmlString2["+xmlString2+"]@@@@@@@@@@@@@@@@@@");
			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;
		String childNodeName = "";
		String  errString = "";
		int ctr,currentFormNo=0;
		int childNodeListLength;
		int count = 0;

		String siteCodeFrom = "";
		String siteCodeTo = "";
		String itemCodeFrom = "";
		String itemCodeTo = "";
		String itemSerFrom = "";
		String itemSerTo = "";
		String errCode = "";
		String userId = "";
		String sql = "";
		String itemcodeTo = "";
		//String itemSerTo = "";
		//String itemSerFrom = "";
		Connection conn = null;
		SimpleDateFormat simpleDateFormat = 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("date_to"))
					{
						if (childNode.getFirstChild() == null || genericUtility.getColumnValue("date_from",dom) == null)
						{
							errCode = "VMRPDATE1";
							errString = getErrorString("date_to",errCode,userId);
							break;
						}
						String df = genericUtility.getColumnValue("date_from",dom);
						String dt = genericUtility.getColumnValue("date_to",dom);
						System.out.println("date From -->" + df );
						System.out.println("date To   -->" + dt );
						System.out.println("Parsed " + simpleDateFormat.parse(df));

						java.util.Date fromDate = new Date(simpleDateFormat.parse(genericUtility.getColumnValue("date_from",dom)).getTime());
						java.util.Date toDate = new Date(simpleDateFormat.parse(genericUtility.getColumnValue("date_to",dom)).getTime());
						if(fromDate.after(toDate))
						{
							System.out.println("Error...");
							errCode = "VMRPDATE1";
							errString = getErrorString("date_to",errCode,userId);
							break;
						}
						else
							System.out.println("No Error...");
					}
					if (childNodeName.equals("site_code__to"))
					{
						if (childNode.getFirstChild() == null || genericUtility.getColumnValue("site_code__from",dom) == null)
						{
							errCode = "VMRPSITE1";
							errString = getErrorString("site_code__to",errCode,userId);
							break;
						}
						siteCodeFrom = genericUtility.getColumnValue("site_code__from",dom);
						siteCodeTo = genericUtility.getColumnValue("site_code__to",dom);

						if(siteCodeTo.compareTo(siteCodeFrom)<0)
						{
							errCode = "VMRPSITE1";
							errString = getErrorString("site_code__to",errCode,userId);
							break;
						}
						count = 0;
					}


				}
			}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}catch(Exception se){}
		}
		
		System.out.println("@@@@@@ errString["+errString+"]");
		return errString;
	}

	// method for item change
	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;
		System.out.println("itemChanged() called for RunMRPWiz");
		String valueXmlString = "";
		try
		{   
			System.out.println("xmlString:::"+xmlString);
			System.out.println("xmlString1:::"+xmlString1);
			System.out.println("xmlString2:::"+xmlString2);

			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);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [RunMRPWiz][itemChanged( String, String )] :==>\n" + e.getMessage());
		}
		return valueXmlString;
	}

	// method for item change
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		String childNodeName = null;
		String sql = "";
		String siteCodeTo = "" , siteCodeFr = "" ,saleOrder = "" ,itemCode = "" , siteCode = "" ;
		String mrpGetData = "", itemCodeList = "" , winName = "", saleOrderList="";
		StringBuffer valueXmlString = new StringBuffer();
		int ctr = 0 , currentFormNo = 0 ,lineNo = 0 ,domID = 0;
		java.sql.Timestamp dateTo =  null ,dateFr = null;
		Document argDom = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;


		String lineNoStr="",mrpGetDataNodeName="",qtyReqd="",referenceChildNodeName="", Detail4ChildNodeName=""; 
		NodeList mrpGetDataList=null,referenceList=null,orderList=null,Detail4List=null,suppDetailsList=null,childNodeList22=null; 
		Node mrpGetDataNode =null, referenceChildNode=null, Detail4ChildNode=null,mrpGetDataNode2=null; 
		Node referenceListNode=null;
		String lotNo="",lotSl="",locCode="",tranSer="",siteCodeSupp="",itemCodeSupp="",runningSupply=""; 
		Document mrpGetDataDom=null;
		NodeList referenceList22=null;
		ArrayList ValueList = new ArrayList(); 
		HashMap keyMap = new HashMap();
		ArrayList keyList = new ArrayList(); int i=0; 
		ArrayList trmpQty=null;
		NodeList orderListList22=null;



		//String mrpGetDataNodeName="",qtyReqd="",referenceChildNodeName="", Detail4ChildNodeName=""; 
		//NodeList mrpGetDataList=null,referenceList=null,orderList=null,Detail4List=null,suppDetailsList=null,childNodeList22=null; 
		//Node mrpGetDataNode =null, referenceChildNode=null, Detail4ChildNode=null,mrpGetDataNode2=null; 
		//Node referenceListNode=null;
		//String lotNo="",lotSl="",locCode="",tranSer="",siteCodeSupp="",itemCodeSupp="",runningSupply="",editFlag=""; 
		//Document mrpGetDataDom=null; NodeList referenceList22=null; ArrayList ValueList = new ArrayList(); 
		//HashMap keyMap = new HashMap<String, ArrayList>();
		//ArrayList keyList = null; 
		//ArrayList finalKeyList = new ArrayList();
		//int i=0; 
		//ArrayList trmpQty=null;
		//NodeList orderListList22=null;
		HashMap valueMap= null;
		ArrayList valueList = new ArrayList();
		String refLineNo="",refId="";
		ArrayList detail2keyList = null;
		Boolean detail2Flag = false, detail4Flag=false;
		
		String custCode="",custName="",quantity="",unit="",loginSiteCode="";
		String orderType="";
		
		java.sql.Date orderDueDate=null;
		
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());

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

			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"); 
			
			switch(currentFormNo)
			{

			case 1 :

				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				System.out.println("currentColumn===="+currentColumn);
				if(currentColumn.trim().equals("itm_default"))
				{
					Calendar cal = Calendar.getInstance();
					Date d = new Date();
					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);
					valueXmlString.append("<date_from>").append(fromDate).append("</date_from>");
					valueXmlString.append("<date_to>").append(toDate).append("</date_to>");
					valueXmlString.append("<site_code__from>").append(loginSiteCode).append("</site_code__from>");   // added loginsitecode as default instead of '00' on 18/06/14 cpatil
					valueXmlString.append("<site_code__to>").append(loginSiteCode).append("</site_code__to>");        // added loginsitecode as default instead of 'ZZ' on 18/06/14 cpatil
					
					// 16/06/14 added for change user n term at the time of allocation
					String chgUser="", chgTerm="" ; 
					chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgUser");
					chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
					System.out.println("@@@@@@ chgUser["+chgUser+"] chgTerm["+chgTerm+"]");
					
					valueXmlString.append("<chg_user>").append(chgUser).append("</chg_user>");
					valueXmlString.append("<chg_term>").append(chgTerm).append("</chg_term>");
					
					
					// finish

					/*
				valueXmlString.append("<item_code__from>").append("0").append("</item_code__from>");
				valueXmlString.append("<item_code__to>").append("ZZ").append("</item_code__to>");
				valueXmlString.append("<item_ser__from>").append("0").append("</item_ser__from>");
				valueXmlString.append("<item_ser__to>").append("ZZ").append("</item_ser__to>");
				valueXmlString.append("<process_cycle>").append("0").append("</process_cycle>");
				valueXmlString.append("<run_date>").append(runDate).append("</run_date>"); // 01-10-2007 manoharan

				valueXmlString.append("<dis_plan>").append("Y").append("</dis_plan>");
				valueXmlString.append("<mfg_plan>").append("Y").append("</mfg_plan>");
				valueXmlString.append("<mat_plan>").append("Y").append("</mat_plan>");
				valueXmlString.append("<made_to_stock>").append("0").append("</made_to_stock>");
				valueXmlString.append("<made_to_order>").append("0").append("</made_to_order>");
				valueXmlString.append("<balance_intersite>").append("N").append("</balance_intersite>");
				//valueXmlString.append("<cons_supp>").append("1").append("</cons_supp>");
				valueXmlString.append("<gen_ind>").append("Y").append("</gen_ind>");
					 */
				}

				valueXmlString.append("</Detail1>");
				break;
			case 2 : 
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					siteCodeTo = genericUtility.getColumnValue("site_code__to",dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code__from",dom1);
					System.out.println("siteCodeTo="+siteCodeTo+  "  siteCodeFr ="+siteCodeFr);

					//sDateTo = genericUtility.getValidDateString(genericUtility.getColumnValue("date_from",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
					dateFr = java.sql.Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("date_from",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00");
					dateTo = java.sql.Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("date_to",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00");

					/*
					sql = " SELECT  sorditem.sale_order,sorditem.line_no, sorditem.site_code, item.item_code__plan  as item_code,sorditem.due_date as due_date, "
							+"   ((case when sorditem.quantity is null then 0 else sorditem.quantity end - case when sorditem.qty_alloc is null then 0 else sorditem.qty_alloc end - case when sorditem.qty_desp is null then 0 else sorditem.qty_desp end )  * case when sorditem.conv__qty_stdqty is null then 1 else sorditem.conv__qty_stdqty end ) as demand,"
							+"    0 as supply, 0 as stock_qty, 0 as oth_supply , sorder.cust_code, customer.cust_name ,sorditem.quantity,sorditem.unit "
							+"  FROM sorder,sorditem, item ,customer"
							+"  WHERE ( sorditem.item_code = item.item_code ) and    (sorder.sale_order = sorditem.sale_order )  "
							+" and  (case when sorder.confirmed is null then 'N' else sorder.confirmed end         = 'Y' ) "
							+" and  (case when sorditem.consume_fc is null then 'Y' else sorditem.consume_fc end = 'N' )" 
							+" and  sorditem.site_code  >=  ?  AND sorditem.site_code  <= ?    "
							+" and (sorditem.due_date >= ?  ) and      (sorditem.due_date <= ?  )  "
							+" and ( case when sorder.status is null then 'P' else sorder.status end = 'P')" 
							//+" and item.mfg_type = 'O' "
							+" and (sorder.cust_code = customer.cust_code ) " 
							+" and case when sorder.alloc_flag is null then 'N' else sorder.alloc_flag end <> 'Y' ";
					*/
					sql=" SELECT  sorditem.sale_order as sale_order,sorditem.line_no as line_no," +
							"  sorditem.site_code as site_code," +
							"  item.item_code__plan  as item_code,sorditem.due_date as due_date," +
							"  sorder.cust_code as cust_code, customer.cust_name as cust_name ," +
							"  sorditem.quantity quantity,sorditem.unit unit,'SORD' as type  " +
							"  ,sorder.due_date as order_due_date " +
							"  FROM sorder,sorditem, item ,customer" +
							"  WHERE ( sorditem.item_code = item.item_code ) and" +
							"  (sorder.sale_order = sorditem.sale_order )" +
							"  and  (case when sorder.confirmed is null then 'N' else sorder.confirmed" +
							"  end         = 'Y' )" +
							"  and  (case when sorditem.consume_fc is null then 'Y' else" +
							"  sorditem.consume_fc end = 'N' )" +
							"  and  sorditem.site_code  >=  ?  AND sorditem.site_code  <= ?" +
							"  and (sorditem.due_date >= ?  ) and      (sorditem.due_date <= ?  )" +
							"  and ( case when sorder.status is null then 'P' else sorder.status end = 'P')" +
							"  and    (sorder.cust_code = customer.cust_code )" +
							"  and case when sorder.alloc_flag is null then 'N' else sorder.alloc_flag end <> 'Y' " +
							"  UNION ALL" +
							"  select mps_order.tran_id as sale_order,'1' as" +
							"  line_no,mps_order.site_code as site_code," +
							"  item.item_code__plan as item_code,mps_order.due_date due_date,'' as " +
							"  cust_code,'' as cust_name, " +
							"  mps_order.quantity as quantity,item.unit,'MPS' as type " +
							"  ,mps_order.due_date as order_due_date " +
							"  from mps_order,item where  mps_order.item_code=item.item_code " +
							"  and  ( case when mps_order.status is null then 'P' else " +
							"  mps_order.status end in ('P','T','M','N')) " +           // added N in condition on 26/06/14
							"  and mps_order.site_code  >=  ?  AND mps_order.site_code  <= ? " +
							"  and  (mps_order.due_date >= ?  ) and      (mps_order.due_date  <= ?  )" ;
							
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,siteCodeFr);
					pstmt.setString(2,siteCodeTo);
					pstmt.setTimestamp(3,dateFr);
					pstmt.setTimestamp(4,dateTo);
					pstmt.setString(5,siteCodeFr);
					pstmt.setString(6,siteCodeTo);
					pstmt.setTimestamp(7,dateFr);
					pstmt.setTimestamp(8,dateTo);
					rs = pstmt.executeQuery();
					while(rs.next())
					{					
						saleOrder = checkNull(rs.getString("sale_order"));
						lineNo = rs.getInt("line_no") ;
						itemCode = checkNull(rs.getString("item_code"));
						siteCode = checkNull(rs.getString("site_code"));
						custCode = checkNull(rs.getString("cust_code"));
						custName = checkNull(rs.getString("cust_name"));
						quantity = checkNull(rs.getString("quantity"));
						unit = checkNull(rs.getString("unit"));
						orderType = checkNull(rs.getString("type"));
						orderDueDate = rs.getDate("order_due_date");  // added on 02-07-14
						
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						String sysDate = sdf.format(orderDueDate);
						System.out.println("Now the order due date is ["+sysDate+"]");
						
						System.out.println(saleOrder+"@"+lineNo+"@"+itemCode);
						domID ++;
						valueXmlString.append( "<Detail2 domID='"+ domID +"' selected = 'N'>\r\n" );
						valueXmlString.append( "<sale_order><![CDATA[" ).append(saleOrder).append( "]]></sale_order>\r\n" );
						valueXmlString.append( "<line_no><![CDATA[" ).append(lineNo).append( "]]></line_no>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append(siteCode).append( "]]></site_code>\r\n" );
						valueXmlString.append( "<item_code><![CDATA[" ).append(itemCode).append( "]]></item_code>\r\n" );
						valueXmlString.append( "<item_code_descr><![CDATA[" ).append(setDescription("descr","item","item_code",itemCode,conn)).append("]]></item_code_descr>\r\n" );
						valueXmlString.append( "<cust_code><![CDATA[" ).append(custCode).append( "]]></cust_code>\r\n" );
						valueXmlString.append( "<cust_name><![CDATA[" ).append(custName).append("]]></cust_name>\r\n" );
						valueXmlString.append( "<quantity><![CDATA[" ).append(quantity).append("]]></quantity>\r\n" );
						valueXmlString.append( "<unit><![CDATA[" ).append(unit).append("]]></unit>\r\n" );
						valueXmlString.append( "<order_type><![CDATA[" ).append(orderType).append("]]></order_type>\r\n" );
						valueXmlString.append( "<due_date><![CDATA[" ).append(sysDate).append("]]></due_date>\r\n" );
						valueXmlString.append( "</Detail2>\r\n" );  

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

			case 3 : 
				parentNodeList = dom.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					
					
					siteCodeTo = genericUtility.getColumnValue("site_code__to",dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code__from",dom1);
					System.out.println("siteCodeTo="+siteCodeTo+  "  siteCodeFr ="+siteCodeFr);
					NodeList detail2List = dom2.getElementsByTagName("Detail2");

					System.out.println("len==="+detail2List.getLength());
					for ( int ctr1 = 0; ctr1 < detail2List.getLength(); ctr1++ )  //Loop for each node of current detail
					{
						saleOrder = checkNull(genericUtility.getColumnValueFromNode( "sale_order", detail2List.item(ctr1) ));
						itemCode = checkNull(genericUtility.getColumnValueFromNode( "item_code", detail2List.item(ctr1) ));
						System.out.println("sale_order=="+saleOrder+"itemCode=="+itemCode);
						System.out.println("ctr1=="+ctr1);
						if(ctr1 > 0)
							itemCodeList = itemCodeList + ",'"+saleOrder+itemCode+"'" ;
						else
							itemCodeList = "'"+saleOrder+itemCode+"'" ;
						System.out.println("itemCodeList=="+itemCodeList);
						
						orderType= checkNull(genericUtility.getColumnValueFromNode( "order_type", detail2List.item(ctr1) ));
						System.out.println("inside for orderType["+orderType+"]");
						
					}
/*
					for ( int ctr1 = 0; ctr1 < detail2List.getLength(); ctr1++ )  //Loop for each node of current detail
					{
						saleOrder = checkNull(genericUtility.getColumnValueFromNode( "sale_order", detail2List.item(ctr1) ));
						System.out.println("sale_order=="+saleOrder);
						System.out.println("ctr1=="+ctr1);
						if(ctr1 > 0)
							saleOrderList = saleOrderList +","+ "'"+saleOrder+"'" ;
						else
							saleOrderList = "'"+saleOrder+"'" ;
						System.out.println("saleOrderList=="+saleOrderList);
					}
*/
					//sDateTo = genericUtility.getValidDateString(genericUtility.getColumnValue("date_from",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
					dateFr = java.sql.Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("date_from",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00");
					dateTo = java.sql.Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("date_to",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00");



					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(genericUtility.getColumnValue("date_from",dom1)).append("</date_from>\r\n");
					argData.append("<date_to>").append(genericUtility.getColumnValue("date_to",dom1)).append("</date_to>\r\n");
					argData.append("<item_code_list>").append(itemCodeList).append("</item_code_list>\r\n");
					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("00").append("</item_code__from>\r\n");
					argData.append("<item_code__to>").append("ZZ").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(simpleDateFormat.format(new Date())).append("</run_date>\r\n"); // 01-10-2007
					// manoharan
					argData.append("<dis_plan>").append("Y").append("</dis_plan>\r\n");  //N
					argData.append("<mfg_plan>").append("Y").append("</mfg_plan>\r\n");
					argData.append("<mat_plan>").append("Y").append("</mat_plan>\r\n");   // change from N
					argData.append("<made_to_stock>").append("1").append("</made_to_stock>\r\n");  //0
					argData.append("<made_to_order>").append("0").append("</made_to_order>\r\n");
					argData.append("<balance_intersite>").append("N").append("</balance_intersite>\r\n");
					argData.append("<gen_ind>").append("Y").append("</gen_ind>\r\n");   //N
					argData.append("<wizard>").append("2").append("</wizard>\r\n");    //3

					argData.append("<sale_order_list>").append(saleOrderList).append("</sale_order_list>\r\n");
					argData.append("<verifier>").append("N").append("</verifier>\r\n");
					
					argData.append("<isBomReplace>").append("N").append("</isBomReplace>\r\n");   // added by cpatil on 17/05/14 for BomReplace
					
				//	orderType= genericUtility.getColumnValue("order_type",dom)==null?"":genericUtility.getColumnValue("order_type",dom);
					argData.append("<order_type>").append(orderType).append("</order_type>\r\n");   // added by cpatil on 17/05/14 for BomReplace
					System.out.println("@@@@@@@@:: order_type["+orderType+"]");
					
					argData.append("</Detail>");
					argData.append("</Root>");

					System.out.println("argData=="+argData);
					argDom = genericUtility.parseString(argData.toString());

					RunMRPPrc runMRPObj = new RunMRPPrc();

					parentNode = parentNodeList.item(0);
					winName = this.getWinName(parentNode);
					
					mrpGetData = runMRPObj.getData(argDom, dom2, winName, xtraParams);

					
					
					System.out.println("mrpGetData =[" + mrpGetData + "]");
					
					if( mrpGetData != null )
					{
						System.out.println("@@@@@@@@@ returning mrpGetData------------");
						return mrpGetData;
					}
					
					
					
					//System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ cp code----start@@@@@@@@@");
/*
					System.out.println("@@@@@@@@@@@@@@@@@@@final mrpGetData =[" + mrpGetData + "]@@@@@@@@@@@@@@@@@@@@@@");

					if (mrpGetData != null && mrpGetData.trim().length() != 0)
					{
						mrpGetDataDom = genericUtility.parseString(mrpGetData);
					}
					mrpGetDataList = mrpGetDataDom.getElementsByTagName("Detail2");
					System.out.println("len===["+mrpGetDataList.getLength()+"]");


					for (int dtlCtr = 0; dtlCtr < mrpGetDataList.getLength(); dtlCtr++)
					{
						Node detailListNode = mrpGetDataList.item(dtlCtr);
						NodeList refrenceList= detailListNode.getChildNodes(); 
						detail2Flag = false;
						detail4Flag=false;
						
						 keyList = new ArrayList();
						valueMap = new HashMap();
						ValueList.clear();
						if("Detail2".equalsIgnoreCase(detailListNode.getNodeName()))
						{
							detail2Flag = true;
							detail2keyList = new ArrayList();
							
							for (int cntr = 0; cntr < refrenceList.getLength(); cntr++) 
							{
								Node	refrenceNode = refrenceList.item(cntr); 
								orderList = refrenceNode.getChildNodes(); 
								//System.out.println("@@@@@@@@@refrenceNode node name[" + refrenceNode.getNodeName()+"]"); 

								
								
								if("reference".equalsIgnoreCase(refrenceNode.getNodeName())) 
								{
									for (int nodCtr = 0; nodCtr < orderList .getLength(); nodCtr++) 
									{
										Node orderNode=orderList.item(nodCtr);
										NodeList orderDtlList=orderNode.getChildNodes();
										//	   System.out.println("@@@@@@@@@orderNode node name[" + orderNode.getNodeName()+"]"); 

										if("order".equalsIgnoreCase(orderNode.getNodeName()))
										{
											for (int cn = 0; cn < orderDtlList .getLength(); cn++) 
											{
												String nodeName=orderDtlList.item(cn).getNodeName(); 
												if("sale_order".equalsIgnoreCase(nodeName))
												{
													saleOrder =  orderDtlList.item(cn).getFirstChild().getNodeValue();
												}
												if("line_no".equalsIgnoreCase(nodeName)) 
												{
													lineNoStr =  orderDtlList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("qty_reqd".equalsIgnoreCase(nodeName))
												{
													qtyReqd =  orderDtlList.item(cn).getFirstChild().getNodeValue(); 
												} 
											}

											saleOrder = saleOrder==null?" ":saleOrder;
											lineNoStr = lineNoStr==null?" ":lineNoStr;
											qtyReqd = qtyReqd==null?" ":qtyReqd;

											System.out.println("@@@@ctr["+dtlCtr+"]saleOrder["+saleOrder+"]-lineNo["+lineNoStr+"]-qtyReqd["+qtyReqd+"]@@@"); 
											
											keyList.add(saleOrder+"@"+lineNoStr);
											
											detail2keyList.add(saleOrder+"@"+lineNoStr);
											
											keyList = new ArrayList();
											valueMap.put("qty_reqd", qtyReqd);
											 System.out.println("test");
											if( keyMap.get(saleOrder+"@"+lineNoStr) != null)
											{
												valueList = (ArrayList) keyMap.get(saleOrder+"@"+lineNoStr);
											}
												
										valueList.add(valueMap);
											
											keyMap.put(saleOrder+"@"+lineNoStr, valueList );
										}
									}
								}
								
								if("Detail4".equalsIgnoreCase(refrenceNode.getNodeName()))
								{
									detail4Flag = true;
									
									for (int nodCtr = 0; nodCtr < orderList .getLength(); nodCtr++) 
									{
										Node detail4Node=orderList.item(nodCtr); 
										NodeList suppDetailList=detail4Node.getChildNodes();
										//System.out.println("@@@@@@@@@orderNode node name[" + detail4Node.getNodeName()+"]"); 
										if("supp_detail".equalsIgnoreCase(detail4Node.getNodeName())) 
										{
											for (int cn = 0; cn < suppDetailList .getLength(); cn++) 
											{
												String nodeName=suppDetailList.item(cn).getNodeName();
												if("site_code".equalsIgnoreCase(nodeName))
												{
													siteCodeSupp =  suppDetailList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("item_code".equalsIgnoreCase(nodeName)) 
												{
													itemCodeSupp =  suppDetailList.item(cn).getFirstChild().getNodeValue();
												}
												if("lot_no".equalsIgnoreCase(nodeName))
												{
													lotNo =  suppDetailList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("lot_sl".equalsIgnoreCase(nodeName))
												{
													lotSl =  suppDetailList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("loc_code".equalsIgnoreCase(nodeName)) 
												{
													locCode =  suppDetailList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("tran_ser".equalsIgnoreCase(nodeName))
												{
													tranSer =  suppDetailList.item(cn).getFirstChild().getNodeValue();
												}    // tran_id  line_no 
												
												if("tran_id".equalsIgnoreCase(nodeName)) 
												{
													refId =  suppDetailList.item(cn).getFirstChild().getNodeValue(); 
												}
												if("line_no".equalsIgnoreCase(nodeName))
												{
													refLineNo =  suppDetailList.item(cn).getFirstChild().getNodeValue();
												}
											}

											siteCodeSupp = siteCodeSupp==null?" ":siteCodeSupp;
											itemCodeSupp = itemCodeSupp==null?" ":itemCodeSupp;
											lotNo = lotNo==null?" ":lotNo;
											lotSl = lotSl==null?" ":lotSl;
											locCode = locCode==null?" ":locCode;
											tranSer = tranSer==null?" ":tranSer;
											refId = refId==null?" ":refId;
											refLineNo = refLineNo==null?" ":refLineNo;

										}
										if( "running_supply".equalsIgnoreCase(detail4Node.getNodeName()) ) 
										{
											runningSupply = orderList.item(nodCtr).getFirstChild().getNodeValue(); 
											runningSupply = runningSupply==null?" ":runningSupply;

										}
									}


									System.out.println("@@@@ctr["+dtlCtr+"]siteCodeSupp["+siteCodeSupp+"]-itemCodeSupp["+itemCodeSupp+"]-lotNo["+lotNo+"]-lotSl["+lotSl+"]-locCode["+locCode+"]-tranSer["+tranSer+"]@@@");
								
									HashMap tempValueMap = new HashMap(); 
									System.out.println("@@@@@@@@@@@@ for detail2keyList---["+detail2keyList.size()+"]-----"); 
									//ArrayList finalList = null;
									if( detail2Flag==false || detail4Flag == false)
									{
									//	detail2keyList.clear();
									}
									for(int j=0 ; j < detail2keyList.size() ; j++ )
									{
										String key = detail2keyList.get(j).toString(); 
										
										//HashMap tempValueMap = new HashMap();
										ArrayList tempArrayList = new ArrayList();
										
										tempArrayList = (ArrayList) keyMap.get(key);
										
										for( int tmpListCtr=0; tmpListCtr < tempArrayList.size() ; tmpListCtr ++ )
										{
											tempValueMap = (HashMap) tempArrayList.get(tmpListCtr);
											
											tempValueMap.put("site_code_supp",siteCodeSupp);
											tempValueMap.put("item_code_supp",itemCodeSupp);
											tempValueMap.put("lot_no",lotNo);
											tempValueMap.put("lot_sl",lotSl);
											tempValueMap.put("loc_code",locCode);
											tempValueMap.put("tran_ser",tranSer);
											tempValueMap.put("running_supply",runningSupply);
											tempValueMap.put("ref_id",refId);
											tempValueMap.put("ref_line_no",refLineNo);
										
										//	System.out.println("@@@@@tempValueMap["+tempValueMap+"]@@@@@@@");
											tempArrayList.set(tmpListCtr, tempValueMap);
										}
										
										keyMap.put(key, tempArrayList);
										
										// System.out.println("@@@@@keyMap["+keyMap+"]@@@@@@@");
									}
								}
								
							
							}
						}
					}

					ArrayList tempList = null; 
					 valueXmlString = new StringBuffer(); 
					 valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><Header><editFlag>");
					 valueXmlString.append(editFlag).append("</editFlag></Header>"); 
					 valueXmlString.append("<Detail3\r\n>"); 
					Set s1=keyMap.entrySet(); 
					Iterator itr=s1.iterator();
					
					System.out.println("@@@@@final---keyMap["+keyMap+"]]");
					
					while(itr.hasNext())
					{ 
						//System.out.println("@@@@@final---keyMap["+keyMap+"]@@@@@@@");
						Map.Entry m=(Map.Entry)itr.next(); 
						{ 
							//System.out.println("@@@@@@@@@@@m["+m+"]@@@@@@@@@");
							tempList = new ArrayList();	
							String keyListStr =  m.getKey().toString(); 
							//System.out.println("@@@@@keyListStr["+keyListStr+"]@@@@["+keyMap.get("076ORE0009@  1")+"]");
							tempList =   (ArrayList) keyMap.get(keyListStr);
							String[] keyStr = keyListStr.split("@"); 
							String so = keyStr[0]; 
							String soLineNo = keyStr[1];
							
							//System.out.println("@@@@@@@@@@@@tempList["+tempList.size()+"]keyListStr["+keyListStr+"]["+tempList+"]");
							
							if( tempList != null)
							{
								
								for( int tempListCtr = 0 ; tempListCtr < tempList.size() ; tempListCtr++ )
								{
									System.out.println("@@@@@@@@tempListCtr["+tempListCtr+"]");
									HashMap innerValueMap = new HashMap();
									innerValueMap = (HashMap) tempList.get(tempListCtr);
									
									valueXmlString.append("<sale_order>").append(so).append("</sale_order>\r\n");
									valueXmlString.append("<line_no>").append(soLineNo).append("</line_no>\r\n"); 
									valueXmlString.append("<site_code>").append( innerValueMap.get("site_code_supp")==null?" ":innerValueMap.get("site_code_supp").toString()).append("</site_code>\r\n");
									valueXmlString.append("<item_code>").append( innerValueMap.get("item_code_supp")==null?" ":innerValueMap.get("item_code_supp").toString()).append("</item_code>\r\n");
									valueXmlString.append("<lot_no>").append( innerValueMap.get("lot_no")==null?" ":innerValueMap.get("lot_no").toString()).append("</lot_no>\r\n"); 
									valueXmlString.append("<lot_sl>").append( innerValueMap.get("lot_sl")==null?" ":innerValueMap.get("lot_sl").toString()).append("</lot_sl>\r\n"); 
									valueXmlString.append("<loc_code>").append( innerValueMap.get("loc_code")==null?" ":innerValueMap.get("loc_code").toString()).append("</loc_code>\r\n");
									valueXmlString.append("<tran_ser>").append( innerValueMap.get("tran_ser")==null?" ":innerValueMap.get("tran_ser").toString()).append("</tran_ser>\r\n"); 
									valueXmlString.append("<running_supply>").append( innerValueMap.get("running_supply")==null?" ":innerValueMap.get("running_supply").toString()).append("</running_supply>\r\n");
									valueXmlString.append("<qty_reqd>").append( innerValueMap.get("qty_reqd")==null?" ":innerValueMap.get("qty_reqd").toString()).append("</qty_reqd>\r\n");
									valueXmlString.append("<running_supply>").append( innerValueMap.get("running_supply")==null?" ":innerValueMap.get("running_supply").toString()).append("</running_supply>\r\n");
									valueXmlString.append("<qty_reqd>").append( innerValueMap.get("qty_reqd")==null?" ":innerValueMap.get("qty_reqd").toString()).append("</qty_reqd>\r\n");
									valueXmlString.append("<ref_id>").append( innerValueMap.get("ref_id")==null?" ":innerValueMap.get("ref_id").toString()).append("</ref_id>\r\n");
									valueXmlString.append("<ref_line_no>").append( innerValueMap.get("ref_line_no")==null?" ":innerValueMap.get("ref_line_no").toString()).append("</ref_line_no>\r\n");
							
								}
								
							}
						//	tempList.clear();
						//	System.out.println("@@@@@@@@@@@@@@@@@@@@@single line ["+finalDetail3+"]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
						}
					}

					valueXmlString.append("</Detail3>"); valueXmlString.append("</Root>"); 
 

					System.out.println("@@@@@@@@@@@@@@@@@@@@@final xml ["+valueXmlString+"]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
		*/			
					System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ cp code----end@@@@@@@@@"); 
				}
				break;

			
			
			
			
			case 4 : 
				parentNodeList = dom.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("@@@@@@@@@@case4 called @@@@@@@@@@@@@");
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					//saleOrder = genericUtility.getColumnValue("sale_order",dom1);
					
					NodeList detail3List = dom2.getElementsByTagName("Detail3");

					System.out.println("len==="+detail3List.getLength());
					for ( int ctr1 = 0; ctr1 < detail3List.getLength(); ctr1++ )  //Loop for each node of current detail
					{
						saleOrder = checkNull(genericUtility.getColumnValueFromNode( "sale_order", detail3List.item(ctr1) ));
						lineNoStr = checkNull(genericUtility.getColumnValueFromNode( "line_no", detail3List.item(ctr1) ));
						saleOrder = checkNull(genericUtility.getColumnValueFromNode( "sale_order", detail3List.item(ctr1) ));
						System.out.println("saleOrder=="+saleOrder+"]--lineNoStr["+lineNoStr+"]");
						System.out.println("ctr1=="+ctr1);
						
					}

					
				}
				break;

			
			
			
			}
			valueXmlString.append("</Root>"); 
		}
		catch(Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage()); 
			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 String setDescription(String field, String table,
			String field2, String value,Connection conn) throws SQLException 
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String descr="",sql="";
		
		sql = "select "+ field +" from "+ table +" where "+ field2 +" = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString( 1, value );
		rs = pstmt.executeQuery();
		if (rs.next())
		{
			descr = checkNull(rs.getString("descr"));
		}
		rs.close();rs = null;
		pstmt.close();pstmt = null;  
		
		System.out.println("@@@@@@@@cpatil-Descr["+descr+"]");
		
		return descr;
	}

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


	private String errorType(Connection conn , String errorCode)
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try 
		{ 
			String  sql = "SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO = ?";
			pstmt = conn.prepareStatement(sql); pstmt.setString(1,errorCode); 
			rs = pstmt.executeQuery(); 
			while(rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			try 
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null) 
				{
					pstmt.close();
					pstmt = null; 
				}
			}
			catch(Exception e) { e.printStackTrace();
			}
		}
		return msgType;
	}

	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; 
	}
	
	
	public String getBomCodeList(String itemCode,String siteCode,String dbID) throws ITMException 
	{
		System.out.println("itemCode cal------------------["+itemCode+"]");
		System.out.println("siteCode cal------------------["+siteCode+"]");
		System.out.println("dbID cal------------------["+dbID+"]");
		
		String sql = "";
		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;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try 
		{	
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			
			//if(itemCode == null || itemCode.trim().length() == 0)
			//{	
				selectSql = " select bom_code,descr from bom " +
						    " where  active = 'Y' and confirmed = 'Y' and item_code =  ?  and site_code = ?  order by bom_code ";
				System.out.println("sql:::"+selectSql);
				pstmt = conn.prepareStatement(selectSql);
				pstmt.setString(1, itemCode);
				pstmt.setString(2, siteCode);
				
				rs = pstmt.executeQuery();
				int num = 1;
				while (rs.next())
				{

					valueXmlString.append("<BOM domID='" + num	+ "' selected = 'N'>\r\n");					
					valueXmlString.append("<BOM_CODE>").append("<![CDATA[" + rs.getString("bom_code") + "]]>").append("</BOM_CODE>\r\n");
					valueXmlString.append("<BOM_CODE_DESCR protect = '1'>").append("<![CDATA[" + rs.getString("descr") + "]]>").append("</BOM_CODE_DESCR>\r\n");
					valueXmlString.append("<DOMID><![CDATA[").append(dbID).append("]]></DOMID>\r\n");
					valueXmlString.append("</BOM>\r\n");
					num++;
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
			
			
				
		
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception :BOM :getBOMCodeList(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 :BOM :getBomCodeList(String,String) :==>\n"+ e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append("</Root>\r\n");
		System.out.println("\n****ValueXmlString ::" + valueXmlString.toString()	+ ":********");
		return valueXmlString.toString();
	}
	//} 
	//}
	//}
	//}
	//}
	//}
	//}
	//}
	//}
}