package ibase.webitm.ejb.wms;

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 StockAllocationPrcEJB extends ProcessEJB implements SessionBean
public class DespEditPrc extends ProcessEJB implements DespEditPrcLocal,DespEditPrcRemote //SessionBean
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//ConnDriver connDriver = new ConnDriver();
	CommonConstants commonConstants = new CommonConstants();
	String chgUser = null;
	String chgTerm = null;

	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 :StockAllocationPrcEJB :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 siteCode = null;
		String saleOrderFr = null;
		String saleOrderTo = null;
		String custCodeFr = null;
		String custCodeTo = null;
		String custCodeDlvFr = null;
		String custCodeDlvTo = null;
		String itemSerFr = null;
		String itemSerTo = null;
		String itemCodeFr = null;
		String itemCodeTo = null;
		String lotNoFr = null;
		String lotNoTo = null;
		String 	sql = null;

		HashMap itemCodeMap = new HashMap();
		ArrayList saleOrderArr = new ArrayList();
		ArrayList custCodeArr = new ArrayList();
		ArrayList dueDateArr = new ArrayList();

		InvAllocTrace invallocTrace = new InvAllocTrace();

		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
		String resultString = "";

		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement pstmt = null;
		Statement st = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		double pendQty = 0;
		double allocQty = 0;	
		boolean bappend = false ;	

		try
		{
			ConnDriver connDriver = new ConnDriver();
			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 = "";
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			} 
			saleOrderFr = genericUtility.getColumnValue("sale_order__fr",headerDom);
			if ( saleOrderFr == null || saleOrderFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}

			saleOrderTo = genericUtility.getColumnValue("sale_order__to",headerDom);         
			if ( saleOrderTo == null || saleOrderTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			custCodeFr = genericUtility.getColumnValue("cust_code__fr",headerDom);
			if ( custCodeFr == null || custCodeFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			custCodeTo = genericUtility.getColumnValue("cust_code__to",headerDom); 
			if ( custCodeTo == null || custCodeTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}  
			custCodeDlvFr = genericUtility.getColumnValue("cust_code__dlv_fr",headerDom);
			if ( custCodeDlvFr == null || custCodeDlvFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			custCodeDlvTo = genericUtility.getColumnValue("cust_code__dlv_to",headerDom); 
			if ( custCodeDlvTo == null || custCodeDlvTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}  
			itemSerFr = genericUtility.getColumnValue("item_ser__fr",headerDom);
			if ( itemSerFr == null || itemSerFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemSerTo = genericUtility.getColumnValue("item_ser__to",headerDom);
			if ( itemSerTo == null || itemSerTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeFr = genericUtility.getColumnValue("item_code__fr",headerDom);
			if ( itemCodeFr == null || itemCodeFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			itemCodeTo = genericUtility.getColumnValue("item_code__to",headerDom);
			if ( itemCodeTo == null || itemCodeTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			lotNoFr = genericUtility.getColumnValue("lot_no__fr",headerDom);
			if ( lotNoFr == null || lotNoFr.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			lotNoTo = genericUtility.getColumnValue("lot_no__to",headerDom);
			if ( lotNoTo == null || lotNoTo.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}

			getDataSql = "SELECT DISTINCT W.REF_SER, W.REF_ID, D.DESP_DATE TRAN_DATE, W.PTCN, W.WAVE_ID, S.SALE_ORDER, S.CUST_CODE,  "
							+ " S.CUST_CODE__DLV, C.CUST_NAME "
							+ " FROM DESPATCH D, WAVE_TASK_DET W, CUSTOMER C, SORDER S, WAVE_TASK WH, DESPATCHDET DD "
							+ " WHERE S.CUST_CODE__DLV = C.CUST_CODE "
							+ " AND D.SORD_NO = S.SALE_ORDER "
							+ " AND W.WAVE_ID = WH.WAVE_ID "
							+ " AND W.REF_ID = D.DESP_ID "
							+ " AND D.DESP_ID = DD.DESP_ID "
							+ " AND W.REF_SER = 'S-DSP' "
							+ " AND  CASE WHEN D.CONFIRMED IS NULL THEN 'N' ELSE D.CONFIRMED END = 'N' "
							+ " AND  CASE WHEN WH.CANCEL IS NULL THEN 'N' ELSE WH.CANCEL END = 'N' "
							+ " AND  S.SITE_CODE = ? "
							+ " AND  S.SALE_ORDER >= ? "
							+ " AND  S.SALE_ORDER <= ? "
							+ " AND  S.CUST_CODE >= ? "
							+ " AND  S.CUST_CODE <= ? "
							+ " AND  S.CUST_CODE__DLV >= ? "
							+ " AND  S.CUST_CODE__DLV <= ? "
							+ " AND  S.ITEM_SER >= ? "
							+ " AND  S.ITEM_SER <= ? "
							+ " AND  DD.ITEM_CODE >= ? "
							+ " AND  DD.ITEM_CODE <= ? "
							+ " AND  DD.LOT_NO >= ? "
							+ " AND  DD.LOT_NO <= ? " 
							+ " UNION ALL "
							+ " SELECT DISTINCT W.REF_SER, W.REF_ID, D.ORDER_DATE TRAN_DATE, W.PTCN, W.WAVE_ID, S.SALE_ORDER, S.CUST_CODE, "
							+ " S.CUST_CODE__DLV, C.CUST_NAME "
							+ " FROM PICK_ORD_HDR D, WAVE_TASK_DET W, CUSTOMER C, SORDER S, WAVE_TASK WH, PICK_ORD_DET DD "
							+ " WHERE S.CUST_CODE__DLV = C.CUST_CODE "
							+ " AND D.SALE_ORDER = S.SALE_ORDER "
							+ " AND W.WAVE_ID = WH.WAVE_ID "
							+ " AND W.REF_ID = D.PICK_ORDER "
							+ " AND D.PICK_ORDER = DD.PICK_ORDER "
							//+ " AND W.REF_SER IN( 'A-PICK','C-PICK','M-PICK') "
							+ " AND W.REF_SER IN( 'A-PICK','C-PICK','M-PICK','P-PICK') "
							+ " AND  CASE WHEN D.STATUS IS NULL THEN 'O' ELSE D.STATUS END = 'O' "
							+ " AND  CASE WHEN WH.CANCEL IS NULL THEN 'N' ELSE WH.CANCEL END = 'N' "
							+ " AND  S.SITE_CODE = ? "
							+ " AND  S.SALE_ORDER >= ? "
							+ " AND  S.SALE_ORDER <= ? "
							+ " AND  S.CUST_CODE >= ? "
							+ " AND  S.CUST_CODE <= ? "
							+ " AND  S.CUST_CODE__DLV >= ? "
							+ " AND  S.CUST_CODE__DLV <= ? "
							+ " AND  S.ITEM_SER >= ? "
							+ " AND  S.ITEM_SER <= ? "
							+ " AND  DD.ITEM_CODE >= ? "
							+ " AND  DD.ITEM_CODE <= ? "
							+ " AND  DD.LOT_NO >= ? "
							+ " AND  DD.LOT_NO <= ? " ;

			
			pstmt = conn.prepareStatement(getDataSql);

			pstmt.setString(1,siteCode);
			pstmt.setString(2,saleOrderFr);
			pstmt.setString(3,saleOrderTo);
			pstmt.setString(4,custCodeFr);
			pstmt.setString(5,custCodeTo);
			pstmt.setString(6,custCodeDlvFr);
			pstmt.setString(7,custCodeDlvTo);
			pstmt.setString(8,itemSerFr);
			pstmt.setString(9,itemSerTo);
			pstmt.setString(10,itemCodeFr);
			pstmt.setString(11,itemCodeTo);
			pstmt.setString(12,lotNoFr);
			pstmt.setString(13,lotNoTo);
			pstmt.setString(14,siteCode);
			pstmt.setString(15,saleOrderFr);
			pstmt.setString(16,saleOrderTo);
			pstmt.setString(17,custCodeFr);
			pstmt.setString(18,custCodeTo);
			pstmt.setString(19,custCodeDlvFr);
			pstmt.setString(20,custCodeDlvTo);
			pstmt.setString(21,itemSerFr);
			pstmt.setString(22,itemSerTo);
			pstmt.setString(23,itemCodeFr);
			pstmt.setString(24,itemCodeTo);
			pstmt.setString(25,lotNoFr);
			pstmt.setString(26,lotNoTo);

			rs = pstmt.executeQuery();

			while(rs.next())
			{
				retTabSepStrBuff.append(rs.getString("REF_SER")).append("\t");
				retTabSepStrBuff.append(rs.getString("REF_ID")).append("\t");
				retTabSepStrBuff.append(rs.getTimestamp("TRAN_DATE")).append("\t");
				retTabSepStrBuff.append(rs.getString("PTCN")).append("\t");
				retTabSepStrBuff.append(rs.getString("WAVE_ID")).append("\t");
				retTabSepStrBuff.append(rs.getString("SALE_ORDER")).append("\t");
				retTabSepStrBuff.append(rs.getString("CUST_CODE")).append("\t");
				retTabSepStrBuff.append(rs.getString("CUST_CODE__DLV")).append("\t");
				retTabSepStrBuff.append(rs.getString("CUST_NAME")).append("\t");

				retTabSepStrBuff.append("\n");

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

			resultString = retTabSepStrBuff.toString();
			System.out.println("resultString from DespEditPrc [" + resultString + "]");
			
			if (resultString == null || resultString.trim().length() == 0)
			{
				errCode ="VTNOREC2";
			}
			if (!errCode.equals(""))
			{
				resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
			}

			
		}
		catch (SQLException e)
		{
			System.out.println("SQLException :StockAllocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				retTabSepStrBuff = null;
				if(conn != null)
				{					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					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 :StockAllocationPrcEJB :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
	{
		String errString = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try
		{

		}//try end	
		catch(Exception e)
		{
			errString = e.getMessage();
			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;
			}
			else
			{
				errString="VTPRCERR";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				return errString;

			}
		}

	}
}