/* 
	Developed by : Alka Shah 
	Company : Base Information Management Pvt. Ltd
	Version : 1.0
	Date :31/03/2007
*/
package ibase.webitm.ejb.mfg.adv;
 
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.mfg.ExplodeBom;
import ibase.webitm.ejb.mfg.MfgCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.util.*;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.text.DecimalFormat;

import javax.ejb.CreateException;
//import javax.ejb.SessionBean;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
 
public class RcptBackflushAct extends ActionHandlerEJB implements  RcptBackflushActLocal , RcptBackflushActRemote //SessionBean
{
	/**
	 * 
	 */
	
	/*public void ejbCreate() throws RemoteException, CreateException {
	}

	public void ejbRemove() {
	}

	public void ejbActivate() {
	}

	public void ejbPassivate() {
	}*/
	DecimalFormat df = new DecimalFormat( "##.000" );
	public String actionHandler() throws RemoteException, ITMException {
		return "";
	}

	public String actionHandler(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams)
			throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;

		String retString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				System.out.println("XML String :" + xmlString);
				dom = genericUtility.parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length() != 0) {
				System.out.println("XML String1 :" + xmlString1);
				dom1 = genericUtility.parseString(xmlString1);
			}

			System.out.println("actionType:" + actionType + ":");

			if (actionType.equalsIgnoreCase("BOM")) {
				retString = actionBOM(dom, dom1, xtraParams);
			}
			if (actionType.equalsIgnoreCase("Stock")) {
				retString = actionStock(dom, dom1, xtraParams);
			}
		} catch (Exception e) {
			System.out
					.println("Exception :RcptBackflushActEJB :actionHandler(String xmlString):"
							+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out
				.println("returning String from RcptBackflushActEJB : actionHandler"
						+ retString);
		return retString;
	}

	public String actionHandlerTransform(String actionType, String xmlString,
			String xmlString1, String objContext, String xtraParams,
			String selDataStr) throws RemoteException, ITMException {
		System.out.println("actionHandlerTransform is calling.............");
		Document dom = null;
		Document dom1 = null;
		Document selDataDom = null;

		String retString = null;
		try {
			if (xmlString != null && xmlString.trim().length() != 0) {
				dom = GenericUtility.getInstance().parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length() != 0) 
			{
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
			}
			if (selDataStr != null && selDataStr.length() > 0) {
				selDataDom = GenericUtility.getInstance().parseString(selDataStr);
			}
			System.out.println("selDataStr:" + selDataStr + ":");
			System.out.println("actionType:" + actionType + ":");

			if (actionType.equalsIgnoreCase("Stock")) {
				retString = stockTransform(dom, dom1, objContext, xtraParams,
						selDataDom);
			}
		} catch (Exception e) {
			System.out.println("Exception :RcptBackflushActEJB :actionHandlerTransform(String xmlString):"
							+ e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from RcptBackflushActEJB : actionHandlerTransform"
						+ retString);
		return retString;
	}

	private String actionBOM(Document dom, Document dom1, String xtraParams) throws RemoteException, ITMException {
		String  siteCode = null, itemCode = null, bomCode = null; 
		String mfgLeadTime = null, qcLeadTime = null,partQty="";
		double quantity = 0;
		
		String sql = "", varValue = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		MfgCommon mfgCommon = new MfgCommon();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ExplodeBom explodeBomObj = new ExplodeBom();	
		ConnDriver connDriver = new ConnDriver();
		String returnString = "";
		try 
		{
			conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			itemCode = genericUtility.getColumnValue("item_code", dom1);
			quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom1)); 
			bomCode = genericUtility.getColumnValue("bom_code", dom1);
			partQty = genericUtility.getColumnValue("part_qty", dom1);
			varValue = mfgCommon.getEnvMfg("999999", "SITE_SPECIFIC", conn);
			if (varValue.equalsIgnoreCase("Y"))
			{
				sql = " SELECT MFG_LEAD_TIME, QC_LEAD_TIME FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCode);
				pstmt.setString(2, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					mfgLeadTime = rs.getString(1);
					qcLeadTime = rs.getString(2);
				}
				rs.close();
				pstmt.close();
			}
			else
			{
				sql = "SELECT MFG_LEAD, QC_LEAD_TIME FROM ITEM WHERE ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					mfgLeadTime = rs.getString(1);
					qcLeadTime = rs.getString(2);
				}	
				rs.close();
				pstmt.close();
			}
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
            StringBuffer xmlString = new StringBuffer("<Root>");							
            String retXMLStr = null;
            xmlString.append("<Detail>");
            xmlString.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>");
            xmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>");
            xmlString.append("<line_type>").append("<![CDATA[").append("B").append("]]>").append("</line_type>");
            xmlString.append("<quantity>").append("<![CDATA[").append(quantity).append("]]>").append("</quantity>");
            xmlString.append("<bom_code>").append("<![CDATA[").append(bomCode).append("]]>").append("</bom_code>");
            xmlString.append("<due_date>").append("<![CDATA[").append(sdf.format(new java.util.Date())).append("]]>").append("</due_date>");
            xmlString.append("<mfg_lead_time>").append("<![CDATA[").append(mfgLeadTime).append("]]>").append("</mfg_lead_time>");
            xmlString.append("<qc_lead_time>").append("<![CDATA[").append(qcLeadTime).append("]]>").append("</qc_lead_time>");
            xmlString.append("</Detail>");
            xmlString.append("</Root>");	

            retXMLStr = explodeBomObj.explodeBom(xmlString.toString()).toString(); 
            if (xmlString != null || xmlString.toString().trim().length() != 0)
            {
            	returnString = updateDetail(dom1, retXMLStr,partQty, xtraParams);
            }
			
		} catch (SQLException sqx) {
			System.out.println("The sqlException occure in RcptBackflushActEJB :" + sqx);
			throw new ITMException(sqx);
		} catch (Exception e) {
			System.out.println("The Exception occure in RcptBackflushActEJB :" + e);
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
//		System.out.println("returnString from RcptBackflushActEJB :" + returnString);
		return returnString;
	}
	
	//private String updateDetail(Document dom1, String expXmlString, String xtraParams)
	private String updateDetail(Document dom1, String expXmlString, String partQty,String xtraParams)
	{
        Document expDom;
        String siteCode = null, itemCode = null;
        String allocateStr = null,locCodeWip=null;
        double quantity = 0;
        Node currDetail = null;
        int ctr = 0;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String errString = "";
        try
        {
        	expDom = genericUtility.parseString(expXmlString);
        	NodeList detailList = expDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			System.out.println("noOfDetails :: " + noOfDetails);
		
			if (noOfDetails > 0)
			{
				for(ctr = 0; ctr < noOfDetails; ctr++)
				{
					currDetail = detailList.item(ctr);	    		
					siteCode = genericUtility.getColumnValueFromNode("site_code", currDetail);
					itemCode = genericUtility.getColumnValueFromNode("item_code", currDetail);
					
					locCodeWip = genericUtility.getColumnValueFromNode("loc_code__wip", currDetail);
					
					quantity = Double.parseDouble(genericUtility.getColumnValueFromNode("quantity", currDetail));
					quantity = Double.parseDouble(df.format(quantity));
					System.out.println(" ****quantity after df.format**** "+quantity);
					// location Code wip added as a parameter in the method below--- 26/03/09 by mukesh for MF89BHU001
					//commented by rajendra on 03/11/09
					//allocateStr = allocateItem(dom1,  locCodeWip , siteCode, itemCode, quantity, xtraParams);
					allocateStr = allocateItem(dom1,  locCodeWip , siteCode, itemCode, quantity,partQty, xtraParams);
					System.out.println("allocateStr ::: " + allocateStr);
					//added by rajendra on 27/10/09
					if (allocateStr.indexOf("<VSTKNOSUFF>") > 0) 
					{
						errString = itmDBAccessEJB.getErrorString("", "VSTKNOSUFF", "");
						errString = updateMessage(errString, "Stock for Item_code :::: " + itemCode + " is not sufficient");
						return errString;
					}
					
					//ended byrajendra on 27/10/09
					if (allocateStr.indexOf("<Errors>") > 0) 
					{
						return allocateStr;
					}
//					ADDED BY ALKA ON 20/07/07 FOR VALIDATING THAT 
					if (allocateStr.indexOf("<Detail>") == -1)
					{
						errString = itmDBAccessEJB.getErrorString("", "VTSTKBOM", "");
						errString = updateMessage(errString, "Stock for Item_code :::: " + itemCode + " is not available");
						return errString;
					}
//					ADDITION ENDED BY ALKA 20/07/07
					valueXmlString.append(allocateStr.substring(allocateStr.indexOf("<Detail>"), allocateStr.indexOf("</Root>")));
	    		}
			}
			valueXmlString.append("</Root>\r\n");
        }
        catch(Exception e)
        {
               System.out.println("Exception :RcptBackflushActEJB : updateDetail.." +e.getMessage());
               e.printStackTrace();
        }
		System.out.println("valueXmlString return from RcptBackflushActEJB :" + valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionStock(Document dom, Document dom1, String xtraParams)
			throws RemoteException, ITMException {
/*		 
 * ALLOWS THE USER TO SELECT STOCK FROM DIFF. LOC_CODE, LOT_NO, LOT_SL
		 GETS ALL STOCK FROM STOCK TABLE FOR FOLLOWING CONDITIONS:
		 	SITE_CODE = HEADER SITE_CODE ---- ITEM_CODE = ITEM CODE ENTERED
		 	INVSTAT.AVAILABLE = 'Y' ---	EXPIRY DATE IS NULL OR EXPIRY DATE IS > HEADER EFF_DATE 
		 	RETEST_DATE > HEADER EFF_DATE ---- 	QUANTITY - ALLOC_QTY > 0
		 IF QC_REQD = 'Y' THEN ONLY EXPIRY DATE AND	RETEST DATE TO BE CONSIDERED
*/
		String available = "", backflushType = "", trackShelfLife = "";
		String siteCode = "", itemCode = "", qcReqd = "";
		String sql = "";
		String expDate1 = "", retestDate1 = "", retestDt = "";
		double totalQty = 0, allocQty = 0;
		Timestamp expDate = null, retestDate = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");

		if (dom == null) {
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}

		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();

		try {
			conn = connDriver.getConnectDB("DriverITM");
			available = "('Y')";
			siteCode = genericUtility.getColumnValue("site_code", dom);
			itemCode = genericUtility.getColumnValue("item_code", dom);
			backflushType = genericUtility.getColumnValue("backflush_type", dom1);
			sql = "SELECT (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END), "
				+ " (CASE WHEN TRACK_SHELF_LIFE IS NULL THEN 'N' ELSE TRACK_SHELF_LIFE END) "
				+ " FROM ITEM WHERE ITEM_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				qcReqd =  rs.getString(1);
				trackShelfLife = rs.getString(2);
			}
			rs.close();
			
			pstmt.close();
			
			if (trackShelfLife != null && trackShelfLife.equalsIgnoreCase("N") && qcReqd != null && qcReqd.equalsIgnoreCase("N"))
			{
				retestDt = genericUtility.getValidDateString("01/01/1910","dd/mm/yyyy", genericUtility.getApplDateFormat());
			}
			else
			{
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
//				ADDED BY ALKA ON 14/07/07 AS ERROR OCCURING FOR PARSING DATE
				java.util.Date date = sdf.parse(new Timestamp(System.currentTimeMillis()).toString());
	            retestDate = Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
	            retestDt = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(retestDate).toString();
//	            COMMENTED BY ALKA ON 14/07/07 AS ERROR OCCURING FOR PARSING DATE
//				retestDt = sdf.format(new Timestamp(System.currentTimeMillis()));  
			}
			
			if (backflushType.equalsIgnoreCase("R"))
			{
				available = "('Y','N')";
			}
			
//			sql = " SELECT  A.ITEM_SER, A.ITEM_CODE, A.UNIT, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.QUANTITY, "
			sql = " SELECT  A.ITEM_SER, A.ITEM_CODE, A.UNIT, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.QUANTITY - A.ALLOC_QTY AS QUANTITY, "
			+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END) ALLOC_QTY, A.SITE_CODE, "
			+ " A.EXP_DATE , A.RETEST_DATE, A.RATE, A.MFG_DATE, A.SITE_CODE__MFG, A.PACK_CODE, A.GRADE, "
			+ " A.DIMENSION, A.GROSS_WEIGHT, A.TARE_WEIGHT, A.NET_WEIGHT FROM STOCK A, INVSTAT B "
			+ " WHERE (A.INV_STAT = B.INV_STAT) "
			+ " AND (( A.ITEM_CODE = '" + itemCode + "' ) AND (A.SITE_CODE = '" + siteCode + "' ) AND (A.QUANTITY > 0) AND (A.QUANTITY - " 
			+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END) > 0 ) "
			+ " AND B.AVAILABLE IN " + available + " AND (A.EXP_DATE IS NULL OR (A.EXP_DATE > ? )) "
			+ " AND ( A.LOT_NO NOT IN ( SELECT LOT_NO FROM QC_ORDER WHERE SITE_CODE = '" + siteCode + "' AND ITEM_CODE = '" + itemCode + "' "
			+ " AND STATUS = 'U' )) AND ( B.STAT_TYPE <> 'S' ))";

			System.out.println("sql :: "+ sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setTimestamp(1, Timestamp.valueOf(genericUtility.getValidDateString(retestDt, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat())+ " 00:00:00"));
			rs = pstmt.executeQuery();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			while (rs.next())
			{
				expDate = rs.getTimestamp(10);
				if (expDate != null)
				{
					expDate1 = sdf.format(expDate);
					System.out.println("expDate1 :"+expDate1);
				}
				retestDate = rs.getTimestamp(11);
				if (retestDate != null)
				{
					retestDate1 = sdf.format(retestDate); 
					System.out.println("retestDate1 :"+retestDate1);
				}
				
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<item_ser>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</item_ser>\r\n");			
				valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</item_code>\r\n");			
				valueXmlString.append("<unit>").append("<![CDATA[").append(rs.getString(3)).append("]]>").append("</unit>\r\n");			
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(4).trim()).append("]]>").append("</loc_code>\r\n");			
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(5).trim()).append("]]>").append("</lot_no>\r\n");			
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(6).trim()).append("]]>").append("</lot_sl>\r\n");			
 				valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getDouble(7)).append("]]>").append("</quantity>\r\n");
                //valueXmlString.append("<alloc_qty>").append("<![CDATA[").append(rs.getDouble(8)).append("]]>").append("</alloc_qty>\r\n");*/
				valueXmlString.append("<site_code>").append("<![CDATA[").append(rs.getString(9).trim()).append("]]>").append("</site_code>\r\n");
				if (expDate != null)
				{
					valueXmlString.append("<exp_date>").append("<![CDATA[").append(expDate1).append("]]>").append("</exp_date>\r\n");
				}
				if (retestDate != null)
				{
					valueXmlString.append("<retest_date>").append("<![CDATA[").append(retestDate1).append("]]>").append("</retest_date>\r\n");	
				}
				valueXmlString.append("<dimension>").append("<![CDATA[").append(rs.getString(11) == null?"":rs.getString(11).trim()).append("]]>").append("</dimension>\r\n");			
                // added below by Mukesh 31/03 for MF89BHU001
				valueXmlString.append("<rate>").append("<![CDATA[").append(rs.getString("rate") == null?"":rs.getString("rate").trim()).append("]]>").append("</rate>\r\n");			
				
				valueXmlString.append("</Detail>\r\n");

			}
			pstmt.close();
			rs.close();
			valueXmlString.append("</Root>\r\n");
		}//try end
		catch (SQLException sqx) {
			System.out.println("The SQLException occurs in RcptBackflushActEJB for Stock button :" + sqx);
			throw new ITMException(sqx);
		} catch (Exception e) {
			System.out.println("The Exception occure in RcptBackflushActEJB  for Stock button:" + e);
			throw new ITMException(e);
		} finally {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
		System.out.println("valueXmlString return from actionStock :"	+ valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String stockTransform(Document dom, Document dom1, String objContext, String xtraParams, Document selDataDom)
			throws ITMException {
		System.out.println("stockTransform is calling.............");
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		
		String sql = null, errCode = "", errString = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String tranId = null, siteDescr = null, itemCode = null, itemDescr = null,  unit = null;
		String stkLocCode = null,stkLotNo = null, stkLotSl = null, locDescr = null,rate=null;;
		String siteCode = null;
		double quantity = 0, remainingQty = 0, inputQty = 0, stkQty = 0;
		Node currDetail = null, currDetail1 = null;

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

			tranId = genericUtility.getColumnValue("tran_id", dom1);
			siteCode = genericUtility.getColumnValue("site_code", dom1);
			siteDescr = genericUtility.getColumnValue("site_descr", dom1);
			itemCode = genericUtility.getColumnValue("item_code", dom);
			itemDescr = genericUtility.getColumnValue("item_descr", dom);
			quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom));
			
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			for (int ctr = 0; ctr < noOfDetails; ctr++) {
				currDetail = detailList.item(ctr);
				stkQty = stkQty + Double.parseDouble((genericUtility.getColumnValueFromNode("quantity",	currDetail)));
			}
			System.out.println("stkQty  :" + stkQty);
			System.out.println("remainingQty  :" + remainingQty);
			
//			if (stkQty < quantity)
			if (quantity != 0 && stkQty < quantity) //CHANGED BY ALKA 19/07/07 FOR HANDLING NO QUANTITY ENTERED.
			{
				errCode = "VTSTOCK1";
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				conn.close();
				conn = null;
				return errString;
			}
//			remainingQty = quantity;  //COMMENTED BY ALKA AS THE FOLLOWING LOGIC IS ADDED
//			ADDED BY ALKA ON 19/07/07 FOR SELECTED STOCK TO BE ENTERED IN THE DETAIL IF THE QUANTITY IS 0
			if (quantity == 0)
			{
				remainingQty = stkQty;
			}
			else
			{
				remainingQty = quantity;
			}
			System.out.println("quantity at the start of the forloop :::: " + quantity);
			System.out.println("remainingQty at the start of the forloop :::: " + remainingQty);
//			ADDITION ENDED BY ALKA 19/07/07
			if (noOfDetails > 0) 
			{
				for (int ctr = 0; ctr < noOfDetails && remainingQty > 0; ctr++) 
				{
					valueXmlString.append("<Detail>\r\n");
					currDetail1 = detailList.item(ctr);
					stkLocCode = genericUtility.getColumnValueFromNode("loc_code", currDetail1);
					stkLotNo = genericUtility.getColumnValueFromNode("lot_no", currDetail1);
					stkLotSl = genericUtility.getColumnValueFromNode("lot_sl", currDetail1);
					unit = genericUtility.getColumnValueFromNode("unit", currDetail1);
					stkQty = Double.parseDouble(genericUtility.getColumnValueFromNode("quantity", currDetail1));
					rate= genericUtility.getColumnValueFromNode("rate", currDetail1);// added by Mukesh -31/03/09
					//System.out.println("\n The rate is =="+rate);
						
					System.out.println("stkQty ==>  :" + stkQty);
					
					sql = "SELECT DESCR FROM LOCATION WHERE LOC_CODE = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, stkLocCode);
					System.out.println("sql ::" + sql + " with stkLocCode :: " + stkLocCode);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						locDescr = rs.getString(1);
						System.out.println("locDescr : " + locDescr);
					}
					rs.close();
					pstmt.close();

					if (stkQty >= remainingQty)
					{
						inputQty = remainingQty;
						remainingQty = 0;
					}
					else if (stkQty < remainingQty)
					{
						inputQty = stkQty;
						remainingQty = remainingQty - stkQty;
					}
					System.out.println("inputQty :: " + inputQty + " remainingQty :: " + remainingQty);
					valueXmlString.append("<tran_id isSrvCallOnChg='0'>").append("<![CDATA[").append(tranId).append("]]>").append("</tran_id>\r\n");
					valueXmlString.append("<site_code isSrvCallOnChg='0'>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>\r\n");
					valueXmlString.append("<site_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(siteDescr).append("]]>").append("</site_descr>\r\n");
					valueXmlString.append("<item_code isSrvCallOnChg='0'>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<item_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
					valueXmlString.append("<unit isSrvCallOnChg='0'>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
					valueXmlString.append("<loc_code isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLocCode).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<location_descr isSrvCallOnChg='0'>").append("<![CDATA[").append(locDescr).append("]]>").append("</location_descr>\r\n");
					valueXmlString.append("<unit__doc isSrvCallOnChg='0'>").append("<![CDATA[").append(unit).append("]]>").append("</unit__doc>\r\n");
					valueXmlString.append("<lot_no isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLotNo).append("]]>").append("</lot_no>\r\n");
					//changed by ALKA on 12/09/07 for no_art column added in the receipt_backflush_set and itemChange enabled for lot_sl for request id "MF78GIN014"
//					valueXmlString.append("<lot_sl isSrvCallOnChg='0'>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<quantity isSrvCallOnChg='0'>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<rate isSrvCallOnChg='0'>").append("<![CDATA[").append(rate).append("]]>").append("</rate>\r\n");
					valueXmlString.append("</Detail>");	
					System.out.println("remainingQty at the end of the forloop :::: " + remainingQty);  //SOP ADDED BY ALKA TO CHECK THE REMAINING STOCK 19/07/07
					if (remainingQty == 0)
					{
						break;
					}
				}// for end
			}// end if
			valueXmlString.append("</Root>");
		}// end try
		catch (ITMException itme) {
			itme.printStackTrace();
			throw itme;
		} catch (Exception e) {
			e.printStackTrace();
			throw new ITMException(e);
		} finally {
			try {
				System.out.println("Closing Connection...");
				conn.close();
				conn = null;
			} catch (Exception e) {
			}
		}
		System.out.println("valueXmlString from :" + valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String allocateItem(Document dom1, String locCodeWip, String siteCode, String itemCode, double quantity, String partQty,String xtraParams)throws RemoteException, ITMException
	{
		String locDescr = null, itemDescr = null, sql = "", siteDescr = null, tranId = null;
		String stkLocCode = null, stkLotNo = null, stkLotSl = null, unit = null;
		String convReqd = null, bkflshType = null, convQty = null, convRate = null;
		double remainingQty = 0d, inputQty = 0d, stkQtyLot = 0d, rate = 0;

		PreparedStatement pstmt = null;
		StringBuffer sqlBuf= new StringBuffer();
		
		Connection conn = null;
		ResultSet rs = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		MfgCommon mfgCommon = new MfgCommon();
		DistCommon distCommon = new DistCommon();

		try
		{
			conn = connDriver.getConnectDB("DriverITM");

			System.out.println("[Allocating .....]");

			sql = "SELECT DESCR FROM ITEM WHERE ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				itemDescr = rs.getString(1);
			}
			
			remainingQty = quantity;  //added by rajendra
			 
			
			
			sql  = " SELECT (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - " 
			+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END), A.UNIT, A.LOC_CODE, A.LOT_NO, "
			+ " A.LOT_SL, A.RATE ,B.DESCR FROM STOCK A, LOCATION B, INVSTAT C WHERE A.LOC_CODE = B.LOC_CODE "
			+ " AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - " 
			+ " (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 AND B.INV_STAT = C.INV_STAT "
			+ " AND  A.ITEM_CODE = ?  AND A.SITE_CODE = ? ";
			if(locCodeWip !=null  && locCodeWip.trim().length()>0 )
			{  
				sql = sql + " AND A.LOC_CODE= ? "; // - added by Mukesh -26/03/09
			}
			
        	sql= sql  +  " AND (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END) >= 0 AND C.AVAILABLE = 'Y' "
			+ " ORDER BY (CASE WHEN A.EXP_DATE IS NULL THEN A.CREA_DATE ELSE A.EXP_DATE END), A.CREA_DATE, " 
			+ " A.LOT_NO , A.LOT_SL";
			
			System.out.println("site code=="+siteCode+" itemCode code=="+itemCode+"  \tlocCodeWip=== "+ locCodeWip + " \n sql= "+sql);
					
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			pstmt.setString(2, siteCode);
			if(locCodeWip !=null  && locCodeWip.trim().length()>0 )
			{
			    pstmt.setString(3, locCodeWip);
			}
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				stkQtyLot = rs.getDouble(1);
				unit = rs.getString(2);
				stkLocCode = rs.getString(3);
				stkLotNo = rs.getString(4);
				stkLotSl = rs.getString(5);
				rate = rs.getDouble(6);
				locDescr = rs.getString(7);	 //added by rajendra on 19/05/08
				
			//	remainingQty = quantity;
				if (stkQtyLot >=  remainingQty)
				{
					inputQty = remainingQty;
					remainingQty = 0;
				}
				else 
				{
					inputQty = stkQtyLot;
					remainingQty = remainingQty - inputQty;
				}
					
				//commented by rajendra on 19/05/08
				//				sql = "SELECT DESCR FROM LOCATION WHERE LOC_CODE = ?";
				//				pstmt = conn.prepareStatement(sql);
				//				pstmt.setString(1, stkLocCode);
				//				rs = pstmt.executeQuery();
				//				if (rs.next())
				//				{
				//					locDescr = rs.getString(1);
				//				}
				//				rs.close();
				//				pstmt.close();
				
				siteDescr = genericUtility.getColumnValue("site_descr", dom1);
				tranId = genericUtility.getColumnValue("tran_id", dom1);
				bkflshType = genericUtility.getColumnValue("backflush_type", dom1);
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<tran_id>").append("<![CDATA[").append(tranId).append("]]>").append("</tran_id>\r\n");
				valueXmlString.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>\r\n");
				valueXmlString.append("<site_descr>").append("<![CDATA[").append(siteDescr).append("]]>").append("</site_descr>\r\n");
				valueXmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
				valueXmlString.append("<item_descr>").append("<![CDATA[").append(itemDescr).append("]]>").append("</item_descr>\r\n");
				valueXmlString.append("<unit>").append("<![CDATA[").append(unit).append("]]>").append("</unit>\r\n");
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(stkLocCode).append("]]>").append("</loc_code>\r\n");
				valueXmlString.append("<location_descr>").append("<![CDATA[").append(locDescr).append("]]>").append("</location_descr>\r\n");
				valueXmlString.append("<unit__doc>").append("<![CDATA[").append(unit).append("]]>").append("</unit__doc>\r\n");
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(stkLotNo).append("]]>").append("</lot_no>\r\n");
				//changed by ALKA on 12/09/07 for no_art column added in the receipt_backflush_set and itemChange enabled for lot_sl for request id "MF78GIN014"
				//valueXmlString.append("<lot_sl>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
				valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(stkLotSl).append("]]>").append("</lot_sl>\r\n");
				valueXmlString.append("<quantity>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<rate>").append("<![CDATA[").append(rate).append("]]>").append("</rate>\r\n");
				
				convReqd = mfgCommon.getEnvMfg("999999", "RBKFSH_CONV_REQD", conn);
				if (convReqd.equalsIgnoreCase("NULLFOUND"))
				{
					convReqd = "Y";
				}
				if (convReqd.equalsIgnoreCase("Y") && bkflshType.equalsIgnoreCase("J"))
				{
					ArrayList convQtyList = new ArrayList();
					ArrayList convRateList = new ArrayList();
					convQtyList = distCommon.getConvQuantityFact(unit, "", itemCode, inputQty, 0, conn);
					convQty = (String)convQtyList.get(1);
					convRateList = distCommon.getConvQuantityFact(unit, "", itemCode, rate, 0, conn);
					convRate = (String)convRateList.get(1);
					valueXmlString.append("<conv_qty_doc>").append("<![CDATA[").append("0").append("]]>").append("</conv_qty_doc>\r\n");
					valueXmlString.append("<qty_doc>").append("<![CDATA[").append(convQty).append("]]>").append("</qty_doc>\r\n");
					valueXmlString.append("<rate_doc>").append("<![CDATA[").append(convRate).append("]]>").append("</rate_doc>\r\n");
				}
				valueXmlString.append("</Detail>");	
				if (remainingQty == 0)
				{
					break;
				}
			}
			//added by rajendra on 27/10/09
			if("X".equalsIgnoreCase(partQty) || partQty==null || partQty.trim().length()==0  )
			{
				if (remainingQty != 0)
				{
					valueXmlString.append("<VSTKNOSUFF>");
				}
			}
//			else if(partQty.equalsIgnoreCase("W"))
//			{
//				if (remainingQty != 0)
//				{
//					remQtyList.put("item_code", new Double(remainingQty)); 
//					//valueXmlString.append("<STKNOTSUFF>");
//				}
//			}
			valueXmlString.append("</Root>\r\n");
		}//try end
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in RcptBackflushActEJB :(Allocate) Button :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch (Exception e)
		{
			System.out.println("The Exception occurs in RcptBackflushActEJB : (Allocate) Button :"+e);
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
			}
			catch (Exception e){}
		}
		System.out.println("valueXmlString return from RcptBackflushActEJB :"+valueXmlString.toString());
		return valueXmlString.toString();
	}	
	private String updateMessage(String resultString,String message)
    {
        StringBuffer stbf = new StringBuffer();
        try{
            System.out.println("resultString : "+resultString);
            stbf.append(resultString.substring(0,resultString.indexOf("<trace>")));
            if(message != null && message.trim().length() > 0){
                stbf.append("<trace>"+message+"</trace>");
                stbf.append(resultString.substring(resultString.indexOf("</trace>")+8));
            }else{
                stbf.append(resultString.substring(resultString.indexOf("<trace>")));
            }
            System.out.println("Resulting String : "+stbf.toString());
            
        }catch(Exception e){
            System.out.println("Exception in updateMessage : "+e);
            e.printStackTrace();
        }
     return stbf.toString();   
    }
}