/*
    Window Name : w_sord_dealloc
    Dharmendra Debata
*/

package ibase.webitm.ejb.dis;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.sql.*;
import java.io.*;
//import webitm4.*;
//import SessionManager.*;
import org.omg.CORBA.ORB;
import org.w3c.dom.*;
import java.util.Properties;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.CommonConstants;
import ibase.ejb.*;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.ejb.Stateless; // added for ejb3
@Stateless // added for ejb3

//public class StockDeallocationPrcEJB extends ProcessEJB implements SessionBean
public class StockDeallocationPrc extends ProcessEJB implements StockDeallocationPrcLocal,StockDeallocationPrcRemote //SessionBean
{
	String siteCode = null;
	String custCodeFr = null;
	String custCodeTo = null;

	String saleOrderFr = null;
	String saleOrderTo = null;
	String chgUser = null;
	String chgTerm = null;

	InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//changed by rajendra on 02/11/07 to remove global connection
	//ConnDriver connDriver = new ConnDriver();
	CommonConstants commonConstants = new CommonConstants();
	HashMap invallocTraceMap = new HashMap();


  /*	public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("StockDeallocationPrcEJB ejbCreate called.........");

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate()
	{
	}
	public void ejbPassivate()
	{
	}
	*/
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	//getData Method
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{

			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2);
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}

		return rtrStr;
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{

		String errCode = "" ;
		String errString = "";
		String getDataSql= "";
		String resultString = "";
		String sqlDealloc ="";
		//added by Kunal on 22/10/12 start
		String itemSerFr = "";
		String itemSerTo = "";
		String DefaultQtyFlag= "";
		String itemCodeFr = "";
		String itemCodeTo = "";
		String postOrderFg = "";
		String sDateTo = "";
		String sDateFr = "";
		String sql = "";
		double balStockQty = 0;
		double holdQty = 0;
		java.sql.Timestamp dateTo =  null;
		java.sql.Timestamp dueDate =  null;
		java.sql.Timestamp dateFr =  null;
		//added by Kunal on 22/10/12 end
		
		//Changed by sumit on 27/11/12 start
		String waveFlagRet = "";
		String wavesql = "";
		//Changed by sumit on 27/11/12 end
		
		Connection conn = null;
		ResultSet rs=null,rsDealloc= null,rs1=null;
		PreparedStatement pstmt = null ,pstmtDeAlloc=null;
		Statement st = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		ConnDriver connDriver = new ConnDriver();	 //added by rajendra on 02/11/07
		try
		{
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			siteCode = genericUtility.getColumnValue("site_code",headerDom);
			if ( siteCode == null || siteCode.trim().length() == 0 )
			{
				siteCode = "";
				System.out.println("Site Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("SITE CODE "+ siteCode);

	    	custCodeFr = genericUtility.getColumnValue("cust_code__from",headerDom);
	    	if ( custCodeFr == null || custCodeFr.trim().length() == 0 )
			{
				custCodeFr = "";
				System.out.println("Cust Code From is Null...");
			}
			System.out.println("custCodeFr"+ custCodeFr);
			custCodeTo = genericUtility.getColumnValue("cust_code__to",headerDom);
			if ( custCodeTo == null || custCodeTo.trim().length() == 0 )
			{
				custCodeTo = "";
				System.out.println("Cust Code To is Null...");

			}
			System.out.println("custCodeTo"+ custCodeTo);
            saleOrderFr = genericUtility.getColumnValue("sale_order__from",headerDom);
            if ( saleOrderFr == null || saleOrderFr.trim().length() == 0 )
			{
				saleOrderFr = "";
				System.out.println("Sale Order From is Null...");

			}

			saleOrderTo = genericUtility.getColumnValue("sale_order__to",headerDom);
			if ( saleOrderTo == null || saleOrderTo.trim().length() == 0 )
			{
				saleOrderTo = "";
				System.out.println("Sale Order To is Null...");

			}
         	if(saleOrderFr.trim().length()==0 && saleOrderTo.trim().length()==0 && custCodeFr.trim().length()==0 && custCodeTo.trim().length()==0)
         	{
         		errCode ="VTMRPPARM";
         		resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
				return resultString;

         	}
         	//added by Kunal on 22/10/12
         	postOrderFg = genericUtility.getColumnValue("post_order_flag",headerDom);
			if(postOrderFg == null || postOrderFg.trim().length() == 0)
			{
				postOrderFg = "";
				System.out.println("Site Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemSerFr = genericUtility.getColumnValue("item_ser__from",headerDom);
			if ( itemSerFr == null || itemSerFr.trim().length() == 0 )
			{
				itemSerFr = "";
				System.out.println("Item Series From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Ser FR "+ itemSerFr);
			itemSerTo = genericUtility.getColumnValue("item_ser__to",headerDom);
			if ( itemSerTo == null || itemSerTo.trim().length() == 0 )
			{
				itemSerTo = "";
				System.out.println("Item Series To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeFr = genericUtility.getColumnValue("item_code__from",headerDom);
			if ( itemCodeFr == null || itemCodeFr.trim().length() == 0 )
			{
				itemCodeFr = "";
				System.out.println("Item Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Code FR "+ itemCodeFr);
			itemCodeTo = genericUtility.getColumnValue("item_code__to",headerDom);
			if ( itemCodeTo == null || itemCodeTo.trim().length() == 0 )
			{
				itemCodeTo = "";
				System.out.println("Item Code To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Ser To"+itemSerTo);
			
			sDateFr = genericUtility.getColumnValue("due_date__from",headerDom);
			if ( sDateFr == null || sDateFr.trim().length() == 0 )
			{
				sDateFr = "";
				System.out.println("Date From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("due_date__from"+sDateFr);
			sDateFr = genericUtility.getValidDateString(sDateFr, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dateFr = java.sql.Timestamp.valueOf(sDateFr + " 00:00:00");

			sDateTo = genericUtility.getColumnValue("due_date__to",headerDom);            
			if ( sDateTo == null || sDateTo.trim().length() == 0 )
			{
				sDateTo = "";
				System.out.println("Date To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("due_date__to"+sDateTo);
			
			sDateTo = genericUtility.getValidDateString(sDateTo, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dateTo = java.sql.Timestamp.valueOf(sDateTo + " 00:00:00");

			DefaultQtyFlag = genericUtility.getColumnValue("default_qty_flag",headerDom);
			if(DefaultQtyFlag == null || DefaultQtyFlag.trim().length() == 0)
			{
				DefaultQtyFlag = "";
				System.out.println("Default Qty flag is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			//Changed by sumit on 27/11/12 getting value of wave_flag start
			waveFlagRet = genericUtility.getColumnValue("wave_flag",headerDom);
			System.out.println(" sumit - waveFlagRet ["+waveFlagRet+"]");
			if( !"B".equalsIgnoreCase(waveFlagRet) || "Y".equalsIgnoreCase(waveFlagRet) || "N".equalsIgnoreCase(waveFlagRet) )
			{
				System.out.println(" in side 'B' ");
				wavesql = " AND CASE WHEN SORDALLOC.WAVE_FLAG IS NULL THEN 'N' ELSE SORDALLOC.WAVE_FLAG END = ? ";
			}
		   else
		   {
			   wavesql = " ";
		   }
			System.out.println(" wavesql ["+wavesql+"]");
			//Changed by sumit on 27/11/12 getting value of wave_flag end			
			
			//added by Kunal on 22/10/12 
			
			if(	saleOrderFr.trim().length()>0 && saleOrderTo.trim().length()>0)
			{
				getDataSql = "SELECT SORDALLOC.SALE_ORDER,SORDDET.LINE_NO,SORDITEM.EXP_LEV ,"
						   +" SORDITEM.ITEM_CODE,ITEM.DESCR,SORDITEM.QUANTITY,"
					       +" SORDALLOC.QTY_ALLOC   PENDING_QUANTITY," //SORDITEM.QTY_ALLOC - SORDITEM.QTY_DESP  PENDING_QUANTITY,"
					       +" SORDALLOC.LOT_NO,SORDALLOC.LOT_SL,SORDALLOC.LOC_CODE, "
					       +" ITEM.GTIN_CASE,SORDITEM.ITEM_CODE,SORDER.SITE_CODE__SHIP ,ITEM.GTIN_UNIT "   //added by Kunal on 22/10/12
					       //Changed by sumit on 26/11/12 retrieve wave flag
					       +", CASE WHEN SORDALLOC.WAVE_FLAG IS NULL THEN 'N' ELSE SORDALLOC.WAVE_FLAG END AS WAVE_FLAG "
					       +" FROM SORDDET,SORDALLOC,SORDITEM,CUSTOMER,ITEM, SORDER  "
						   +" WHERE SORDER.SALE_ORDER = SORDDET.SALE_ORDER AND "
						   +"(SORDALLOC.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
						   +"( SORDALLOC.LINE_NO = SORDDET.LINE_NO ) AND "
						   +"( SORDALLOC.EXP_LEV = SORDITEM.EXP_LEV ) AND "
						   +"( SORDITEM.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
						   +"( SORDITEM.LINE_NO = SORDDET.LINE_NO ) AND "
						   +"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
						   +"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE ) AND "
						   //Commented by Manoj dtd 07/06/2013 to check site code from sorder table
						   //+" SORDDET.SITE_CODE = ? AND "
						   +" SORDER.SITE_CODE = ? AND "
						   +" SORDER.SALE_ORDER >= ? AND "
						   +" SORDER.SALE_ORDER <= ? AND "
						   +" ITEM.ITEM_SER >=? AND "
							+" ITEM.ITEM_SER <=? AND "  
							+" CUSTOMER.CUST_CODE >=? AND "
							+" CUSTOMER.CUST_CODE <=? AND "  
							+" SORDER.DUE_DATE >=  ?  AND" 
							+" SORDER.DUE_DATE <= ?  AND "
							+" SORDITEM.ITEM_CODE >= ? AND "
							+" SORDITEM.ITEM_CODE <= ?  "
						   +"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end = 'P' "
						   +"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
						   +"AND CASE WHEN SORDALLOC.STATUS IS NULL THEN 'P' ELSE SORDALLOC.STATUS end = 'P' "
						   +"AND SORDITEM.QTY_ALLOC - SORDITEM.QTY_DESP > 0 "
						   +"AND SORDITEM.LINE_TYPE = 'I'"	   
						   //Changed by sumit on 27/11/12 adding wave flag condition in case of both wave						   
						   +wavesql+
						   "ORDER BY SORDER.SALE_ORDER,SORDITEM.EXP_LEV,SORDITEM.ITEM_CODE__ORD";
							
				pstmt = conn.prepareStatement(getDataSql);
				pstmt.setString(1,siteCode);
				pstmt.setString(2,saleOrderFr);
				pstmt.setString(3,saleOrderTo);
				pstmt.setString(4,itemSerFr);
				pstmt.setString(5,itemSerTo);
				pstmt.setString(6,custCodeFr);
				pstmt.setString(7,custCodeTo);
				pstmt.setTimestamp(8,dateFr);
				pstmt.setTimestamp(9,dateTo);
				pstmt.setString(10,itemCodeFr);
				pstmt.setString(11,itemCodeTo);
				//Changed by sumit on 27/11/12 adding parameter start
				if( !"B".equalsIgnoreCase(waveFlagRet) || "Y".equalsIgnoreCase(waveFlagRet) || "N".equalsIgnoreCase(waveFlagRet) )
				{
					pstmt.setString(12,waveFlagRet);
				}
				//Changed by sumit on 27/11/12 adding parameter end
				st = conn.createStatement();
				rs = pstmt.executeQuery();

				if(rs.next())
				{
					do
					{
						sql ="SELECT SUM(STOCK.HOLD_QTY ) " 
								+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
								+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) "
								+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) "
								+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) "
								+"AND INVSTAT.AVAILABLE = 'Y' "
								+"AND STOCK.ITEM_CODE = '"+ rs.getString(12) + "'"
								+"AND STOCK.SITE_CODE = '"+ rs.getString(13) +"'" 
								+"AND (STOCK.QUANTITY - STOCK.ALLOC_QTY) > 0  ";
							rs1 = st.executeQuery(sql);
							if (rs1.next())
							{
								
								//balStockQty = rs1.getDouble(1);
								holdQty = rs1.getDouble(1);  
								
								System.out.println("  holdQty"+holdQty);
								
								
							}
							rs1.close();

						//SALE_ORDER
						retTabSepStrBuff.append(rs.getString(1)).append("\t");
						//LINE_NO
						retTabSepStrBuff.append(rs.getString(2)).append("\t");
						//Changed by sumit on 27/11/12 adding wave_flag
						//WAVE_FLAG
						retTabSepStrBuff.append(rs.getString("WAVE_FLAG")).append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(rs.getString(3)).append("\t");
						//CUST_CODE
						retTabSepStrBuff.append(" ").append("\t");
						//ITEM_CODE
						retTabSepStrBuff.append(rs.getString(4)).append("\t");
						//ITEM_DESCR
						retTabSepStrBuff.append(rs.getString(5)).append("\t");
						//QUANTITY
						retTabSepStrBuff.append(rs.getDouble(6)).append("\t");
						//PENDING_QTY
						retTabSepStrBuff.append(rs.getDouble(7)).append("\t");
						//LOT_NO
						retTabSepStrBuff.append(rs.getString(8)).append("\t");
						//LOT_SL
						retTabSepStrBuff.append(rs.getString(9)).append("\t");
						//LOC_CODE
						retTabSepStrBuff.append(rs.getString(10)).append("\t");
						//ALLOC_TRANID
						retTabSepStrBuff.append(" ").append("\t");
						//ALLOC_LINENO
						retTabSepStrBuff.append(" ").append("\t");

						retTabSepStrBuff.append("  ").append("\t");
						//hold qty
						retTabSepStrBuff.append(holdQty).append("\t");
						//gtin_case
						retTabSepStrBuff.append(rs.getString(11)== null ?"": rs.getString(11)).append("\t");
						//gtin_unit
						retTabSepStrBuff.append(rs.getString(14) == null ?" ":rs.getString(14) ).append("\n"); //added by Kunal on 25/10/12

						resultString = retTabSepStrBuff.toString();
						pstmt.clearParameters();

					}while(rs.next());

				}


			}
		 	else if(custCodeFr.trim().length()>0 && custCodeTo.trim().length()>0 )
			{

				sqlDealloc= "SELECT SORD_ALLOC.CUST_CODE,SORD_ALLOC_DET.ITEM_CODE, ITEM.DESCR, SORD_ALLOC_DET.QUANTITY,"
						   +"SORD_ALLOC_DET.QUANTITY - SORD_ALLOC_DET.DEALLOC_QTY PENDING_QUANTY ,"
						   +"SORD_ALLOC_DET.LOT_NO,SORD_ALLOC_DET.LOT_SL,SORD_ALLOC_DET.LOC_CODE , "
						   +"SORD_ALLOC_DET.TRAN_ID,SORD_ALLOC_DET.LINE_NO ,"
						   +"  ITEM.GTIN_CASE , SORDITEM.ITEM_CODE,SORDER.SITE_CODE__SHIP,ITEM.GTIN_UNIT "   //added by Kunal on 22/10/12
						   +" FROM SORD_ALLOC,SORD_ALLOC_DET, ITEM  "
						   +" WHERE SORD_ALLOC.TRAN_ID = SORD_ALLOC_DET.TRAN_ID "
						   +" AND SORD_ALLOC_DET.ITEM_CODE = ITEM.ITEM_CODE "
						   +"AND SORD_ALLOC.SALE_ORDER IS NULL "
						   +"AND SORD_ALLOC.SITE_CODE= ? "
						   +"AND SORD_ALLOC.CUST_CODE >=? AND SORD_ALLOC.CUST_CODE <=?  "
						   +"AND SORDER.SALE_ORDER >= ? AND "
						   +" SORDER.SALE_ORDER <= ? AND "
						   +" ITEM.ITEM_SER >=? AND "
							+" ITEM.ITEM_SER <=? AND "  
							+" CUSTOMER.CUST_CODE >=? AND "
							+" CUSTOMER.CUST_CODE <=? AND "  
							+" SORDER.DUE_DATE >=  ?  AND" 
							+" SORDER.DUE_DATE <= ?  AND "
							+" SORDITEM.ITEM_CODE >= ? AND "
							+" SORDITEM.ITEM_CODE <= ?  "
		   				   +"AND SORD_ALLOC_DET.QUANTITY -(CASE WHEN SORD_ALLOC_DET.DEALLOC_QTY IS NULL THEN 0  ELSE SORD_ALLOC_DET.DEALLOC_QTY END) > 0 ";

		  		pstmtDeAlloc = conn.prepareStatement(sqlDealloc);
		  		pstmtDeAlloc.setString(1,siteCode);
				pstmtDeAlloc.setString(2,custCodeFr);
				pstmtDeAlloc.setString(3,custCodeTo);
				pstmtDeAlloc.setString(4,saleOrderFr);
				pstmtDeAlloc.setString(5,saleOrderTo);
				pstmtDeAlloc.setString(6,itemSerFr);
				pstmtDeAlloc.setString(7,itemSerTo);
				
				pstmtDeAlloc.setTimestamp(8,dateFr);
				pstmtDeAlloc.setTimestamp(9,dateTo);
				pstmtDeAlloc.setString(10,itemCodeFr);
				pstmtDeAlloc.setString(11,itemCodeTo);
				
				st = conn.createStatement();
				rsDealloc = pstmtDeAlloc.executeQuery();
				if(rsDealloc.next())
				{

					do
					{
						sql ="SELECT SUM(STOCK.HOLD_QTY ) " 
								+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
								+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) "
								+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) "
								+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) "
								+"AND INVSTAT.AVAILABLE = 'Y' "
								+"AND STOCK.ITEM_CODE = '"+ rsDealloc.getString(12) + "'"
								+"AND STOCK.SITE_CODE = '"+ rsDealloc.getString(13) +"'" 
								+"AND (STOCK.QUANTITY - STOCK.ALLOC_QTY) > 0  ";
							rs1 = st.executeQuery(sql);
							if (rs1.next())
							{
								
								//balStockQty = rs1.getDouble(1);
								holdQty = rs1.getDouble(1);  
								
								System.out.println("  holdQty"+holdQty);
								
								
							}
							rs1.close();

						//SALE_ORDER
						retTabSepStrBuff.append(" ").append("\t");
						//LINE_NO
						retTabSepStrBuff.append(" ").append("\t");
						//EXP_LEV
						retTabSepStrBuff.append(" ").append("\t");
						//CUST_CODE
						retTabSepStrBuff.append(rsDealloc.getString(1)).append("\t");
						//ITEM_CODE
						retTabSepStrBuff.append(rsDealloc.getString(2)).append("\t");
						//ITEM_DESCR
						retTabSepStrBuff.append(rsDealloc.getString(3)).append("\t");
						//QUANTITY
						retTabSepStrBuff.append(rsDealloc.getDouble(4)).append("\t");
						//PENDING_QTY
						retTabSepStrBuff.append(rsDealloc.getDouble(5)).append("\t");
						//LOT_NO
						retTabSepStrBuff.append(rsDealloc.getString(6)).append("\t");
						//LOT_SL
						retTabSepStrBuff.append(rsDealloc.getString(7)).append("\t");
						//LOC_CODE
						retTabSepStrBuff.append(rsDealloc.getString(8).trim()).append("\t");
						//ALLOC_TRANID
						retTabSepStrBuff.append(rsDealloc.getString(9).trim()).append("\t");
						//ALLOC_LINENO
						retTabSepStrBuff.append(rsDealloc.getString(10)).append("\t");

						retTabSepStrBuff.append("  ").append("\t");
						
						//hold qty
						retTabSepStrBuff.append(holdQty).append("\t");
						//gtin_case
						retTabSepStrBuff.append(rsDealloc.getString(11) == null ?" ":rsDealloc.getString(11)).append("\t"); 
						//gtin_unit
						retTabSepStrBuff.append(rsDealloc.getString(14) == null ?" ":rsDealloc.getString(14)).append("\n"); //added by Kunal on 25/10/12

						resultString = retTabSepStrBuff.toString();


						pstmtDeAlloc.clearParameters();

					}while(rsDealloc.next());
				}


				pstmtDeAlloc.close();
				rsDealloc.close();
			}


		}
		catch (SQLException e)
		{
			System.out.println("SQLException :StockDeallocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{

			try
			{
				cleanup();
				retTabSepStrBuff = null;

				if(conn != null)
				{
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs != null)
					{
						rs.close();
					}
					if(pstmtDeAlloc !=null)
					{
						pstmtDeAlloc.close();
						pstmtDeAlloc = null;
					}
					if(rsDealloc !=null)
					{
						rsDealloc.close();
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return resultString;
	}

	//Process Method

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString);
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2);
				System.out.println("detailDom" + detailDom);
			}

		    retStr = process(headerDom, detailDom, windowName, xtraParams);

		}
		catch (Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
		}
		return retStr;

	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("detailDom------------------->"+detailDom);
		String childNodeName = "";
		String errCode = "";
		String siteCode = this.siteCode;
		String saleOrder = " ";
		String custCode=" ";
		String expLev = " " ;
		String itemCode = " ";
		String lineNo = " ";
		String locCode = " ";
		String lotSl= " ";
		String lotNo = " " ;
		String tranId=" ";
		String allocLineno=" ";
		double DeallocQty = 0;
		double quantity = 0;
		String errString = " ";
		//Changed by sumit on 27/11/12 
		String waveFlag = "";
		Connection conn = null;

		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;

		PreparedStatement pstmt = null;
		Statement st = null;
		ResultSet rs=null;
		String siteCodeShip="";

	    ConnDriver connDriver = new ConnDriver();	//added by rajendra on 02/11/07
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");

			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			System.out.println("parentNodeListLength------------------->"+parentNodeListLength);
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				parentNode = parentNodeList.item(selectedRow);

				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
 					System.out.println("childNodeName---->>> "+ childNodeName);
					if (childNodeName.equals("quantity"))
					{
						quantity = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.print("quantity::::"+ quantity );
					}

					if (childNodeName.equals("sale_order"))
					{
						saleOrder = childNode.getFirstChild().getNodeValue();
						System.out.print("saleOrder::::"+ saleOrder );

					}

					if (childNodeName.equals("line_no"))
					{
						lineNo = childNode.getFirstChild().getNodeValue();
						System.out.print("lineNo::::"+ lineNo );
					}

					if (childNodeName.equals("item_code"))
					{
						itemCode = childNode.getFirstChild().getNodeValue();
						System.out.print("itemCode::::"+ itemCode );
					}

					if (childNodeName.equals("dealloc_qty"))
					{
						DeallocQty = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.print("DeallocQty::::"+ DeallocQty );
					}
					if (childNodeName.equals("exp_lev"))
					{
						expLev = childNode.getFirstChild().getNodeValue();
						System.out.print("expLev::::"+ expLev );
					}
					if (childNodeName.equals("cust_code"))
					{
						custCode = childNode.getFirstChild().getNodeValue();
						System.out.print("custCode::::"+ custCode );
					}
					if (childNodeName.equals("lot_sl"))
					{
						lotSl = childNode.getFirstChild().getNodeValue();
						System.out.print("lineNo::::"+ lineNo );
					}
					if (childNodeName.equals("lot_no"))
					{
						lotNo = childNode.getFirstChild().getNodeValue();
						System.out.print("lotSl::::"+ lotSl );
					}
					if (childNodeName.equals("loc_code"))
					{
						locCode = childNode.getFirstChild().getNodeValue();
						System.out.print("locCode::::"+ locCode );
					}
					if (childNodeName.equals("tranid"))
					{
						tranId = childNode.getFirstChild().getNodeValue();
						System.out.print("alloc_tranid::::"+ tranId );
					}
					if (childNodeName.equals("alloc_lineno"))
					{
						allocLineno = childNode.getFirstChild().getNodeValue();
						System.out.print("allocLineno::::"+ allocLineno );
					}
					if (childNodeName.equals("wave_flag"))
					{
						waveFlag = childNode.getFirstChild().getNodeValue();
						System.out.print("waveFlag :*::"+ allocLineno );
					}

				}//inner for loop
				System.out.println(" sumit wave flag - >["+waveFlag+"]");
				//Changed by sumit on 27/11/12 error msg if wave done condition start.
				//if(DeallocQty > 0 )
				if( "Y".equalsIgnoreCase(waveFlag))
				{					
					errString="VTWAPROALL";
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					System.out.println(" error ["+errString+"]");
					return errString;
				}				
				else if(DeallocQty > 0 && "N".equalsIgnoreCase(waveFlag))
				{
				//Changed by sumit on 27/11/12 error msg if wave done condition end.					
					System.out.println("if calling...*....");
					//Added by manoj dtd 07/06/2013 to Stock deallocate for Shit Code Ship
					String getDataSql = "SELECT SITE_CODE__SHIP FROM SORDER WHERE SALE_ORDER = ? ";
					pstmt = conn.prepareStatement(getDataSql);

					pstmt.setString(1,saleOrder);

					rs = pstmt.executeQuery();

					System.out.println("siteCode------"+ siteCode);
					if(rs.next())
					{
						siteCodeShip = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					errString = sorderDeAllocate(saleOrder,siteCodeShip, lineNo, itemCode, DeallocQty, expLev,custCode,lotSl,lotNo,locCode,tranId,allocLineno);
					if (errString != null && errString.trim().length() > 0)
					{
						System.out.println("errString :"+errString);
						return errString;
					}
				}				

			}// out for loop


		}//try end
		catch(Exception e)
		{
			System.out.println("Exception :StockDeallocationPrcEJB :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			errString = e.getMessage();
			e.printStackTrace();
			return errString ;
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				if(conn != null)
				{
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					conn.close();
					conn = null;
				}

			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;

			}
			if (errString == null || errString.trim().length() == 0)
			{
				errString="VTCOMPL";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;
			}
			//Changed by sumit on 27/11/12 showing wave messages start.
			else if (errString.contains("VTWAPROALL"))
			{				
				return errString;
			}
			//Changed by sumit on 27/11/12 showing wave messages end.
			else
			{
				errString="VTPRCERR";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;

			}
		}	

	}

	private String sorderDeAllocate(String saleOrder,String siteCodeShip, String lineNo, String itemCode, double DeallocQty, String expLev,String custCode,String lotSl,String lotNo,String locCode,String tranId,String allocLineno ) throws ITMException
	{
		System.out.println("updateSOrder calling ..............");
		String updateSorditem = null,updateSordAllocDet=null;
		String updateSql= null,DelQtyAlloc= null,SqlQtyAlloc= null ;
		String errString = "";
		String error = "";
		String errCode = "";
		Connection conn = null;
		PreparedStatement pstmtDeAlloc=null;
		Statement st = null;
		ResultSet rsQtyAlloc=null;
		ConnDriver connDriver = new ConnDriver();	 //added by manoharan
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			st = conn.createStatement();
			invallocTraceMap.put("ref_ser","D-ALOC");
			invallocTraceMap.put("site_code",siteCodeShip);
			invallocTraceMap.put("item_code",itemCode);
			invallocTraceMap.put("loc_code",locCode);
			invallocTraceMap.put("lot_no",lotNo);
			invallocTraceMap.put("lot_sl",lotSl);

			invallocTraceMap.put("alloc_qty",new Double(-1 * DeallocQty));

			System.out.print("DeallocQty = " + (-1*DeallocQty));
			invallocTraceMap.put("chg_user",chgUser);
			invallocTraceMap.put("chg_term",chgTerm);
			invallocTraceMap.put("chg_win","W_SORD_DEALLOC");

			if (DeallocQty > 0 && saleOrder.trim().length()>0)
			{
				invallocTraceMap.put("ref_id",saleOrder);
				invallocTraceMap.put("ref_line",lineNo);
				errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);

				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :::"+ errString );
					return errString;
				}

				updateSorditem ="UPDATE SORDITEM  SET QTY_ALLOC = QTY_ALLOC - " + new Double(DeallocQty).toString()
							   +" WHERE SALE_ORDER = '" + saleOrder + "' "
							   +" AND LINE_NO = '" + lineNo + "' "
							   +" AND EXP_LEV = '" + expLev + "' ";

				System.out.println("updateSql------->"+updateSorditem);
				st.executeUpdate(updateSorditem);
				System.out.println("UPDATE  SUCCESS FOR SORDITEM....>>>>>>>>");

				updateSql = " UPDATE SORDALLOC SET QTY_ALLOC =  QTY_ALLOC - " + new Double(DeallocQty).toString()
							  + " WHERE SALE_ORDER = '" + saleOrder + " ' "
							  + " AND LINE_NO = '" + lineNo + "' "
							  + " AND EXP_LEV = '" + expLev + "' "
							  + " AND ITEM_CODE = '" + itemCode + "' "
							  + " AND LOT_NO = '" + lotNo + "' "
							  + " AND LOT_SL = '" + lotSl + "' "
							  + " AND LOC_CODE = '" + locCode + "' " ;
				System.out.println("updateSql:::>>>>"+ updateSql);
				st.executeUpdate(updateSql);
				System.out.println("UPDATE  SUCCESS FOR SORDALLOC....");
				SqlQtyAlloc = "SELECT QTY_ALLOC FROM SORDALLOC "
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
				rsQtyAlloc  = st.executeQuery(SqlQtyAlloc);
				if (rsQtyAlloc.next())
				{
					System.out.println("Updated Allocated Qty :::"+rsQtyAlloc.getDouble(1));
					if(rsQtyAlloc.getDouble(1)<=0)
					{
						DelQtyAlloc = "DELETE FROM SORDALLOC"
									+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
									+ " AND LINE_NO = '" + lineNo + "' "
									+ " AND EXP_LEV = '" + expLev + "' "
									+ " AND ITEM_CODE = '" + itemCode + "' "
									+ " AND LOT_NO = '" + lotNo + "' "
									+ " AND LOT_SL = '" + lotSl + "' "
									+ " AND LOC_CODE = '" + locCode + "' " ;
						System.out.println("DelQtyAlloc:::"+DelQtyAlloc);
						st.executeUpdate(DelQtyAlloc);
						System.out.println("Delete completed ");
					}
				}
				rsQtyAlloc.close();
			}
			//For saleOrder is null and you have cust_code
			else if(DeallocQty > 0 && tranId.trim().length()>0)
			{
				invallocTraceMap.put("ref_id",tranId);
				System.out.println("alloc_tranid::----"+tranId);
				invallocTraceMap.put("ref_line",allocLineno);
				System.out.println("siteCode::"+siteCode+"itemCode:"+itemCode+"locCode::"+locCode +"lotNo:"+lotNo+"lotSl:"+"QtyToBeDeallocated::"+(-1*DeallocQty)+"alloc_tranid::"+tranId+"allocLineno::"+allocLineno);
				errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :::"+ errString );
					return errString;
				}
				updateSordAllocDet= " UPDATE SORD_ALLOC_DET SET DEALLOC_QTY = DEALLOC_QTY + "+  new Double(DeallocQty).toString()
									+" WHERE SORD_ALLOC_DET.TRAN_ID='"+ tranId +"'"
									+" AND SORD_ALLOC_DET.LINE_NO ="+ allocLineno ;
				System.out.println("updateSordAllocDet::"+ updateSordAllocDet);
				st.executeUpdate(updateSordAllocDet);
				System.out.println("updateSordAllocDet completed successfully");
			}
			st.close();

			conn.commit();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			return errString;
		}

		catch(Exception e)
		{
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString ;
		}
		finally
		{
			try
			{
				if(conn != null)
				{

					if(st != null)
					{
						st.close();
						st = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;

			}
			return errString;
		}

	}
	void cleanup()
	{
		invallocTraceMap.clear();
	}

}