/* 
	Developed by : TaraniseMeher
	Company : Base Information Management Pvt. Ltd
	Window Name : w_bom
	Date :18/09/2006
	Menu Reference:Master-ProductStructure-BillOfMaterial(MFG)
	Button:ViewAltBOM,Stock
	
*/



package ibase.webitm.ejb.mfg.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.io.*;
import java.util.Date;
import java.sql.*;
import org.w3c.dom.*;
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.ejb.*;
import javax.annotation.*;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class BOMAct extends ActionHandlerEJB implements  BOMActLocal , BOMActRemote //SessionBean
{
	GenericUtility genericUtility = null;
	ConnDriver connDriver = new ConnDriver();
	Connection conn = null;
	PreparedStatement pstmt = null;
	@PostConstruct
	public void ejbCreate()  //throws RemoteException//, CreateException 
	{
		System.out.println("Create Method Called[BOMActEJB].................");
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception in creating Connection");
		}
	}

	/*public void ejbRemove()
	{
	}

	public void ejbActivate() 
	{
	}

	public void ejbPassivate() 
	{
	}*/

    public String actionHandler() throws RemoteException,ITMException
	{
		System.out.println("actionHandler() Method Called....");
		return "";
	}
	public String actionHandlerTransform(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams, String selDataStr) throws RemoteException,ITMException
	{
		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); 
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
				if(selDataStr != null && selDataStr.length() > 0)
				{
					selDataDom = GenericUtility.getInstance().parseString(selDataStr);
				}
			}
			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("Stock"))
			{
				retString = stockTransform(dom, dom1, objContext, xtraParams, selDataDom);
			}
			if (actionType.equalsIgnoreCase("ViewAltBOM"))
			{
				retString = viewAltBOMTransform(dom, dom1, objContext, xtraParams, selDataDom);
			}
		
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :Dispatch :actionHandlerTransform(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from Despatch : actionHandlerTransform"+retString);
	    return retString;
	}
	private String viewAltBOMTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		System.out.println("TransFomMation Called**************************************");
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
				
			valueXmlString.append("</Root>");
		
		return valueXmlString.toString();
	}
	private String stockTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		System.out.println("TransFomMation Called**************************************");
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
				
			valueXmlString.append("</Root>");
		
		return valueXmlString.toString();
	}

public String actionHandler(String actionType, String xmlString,String xmlString1, String objContext, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("AdjIssEJB called");
		Document dom = null;
		Document dom1 = null;
		String  resString = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance(); 
			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("Stock"))
			{
				resString = actionStock(dom,dom1,objContext,xtraParams);
			}
			if (actionType.equalsIgnoreCase("ViewAltBOM")) 
			{
				resString = actionViewAltBOM(dom,dom1,objContext,xtraParams);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :AdjIssEJB :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from action[Stock] actionHandler"+resString);
	    return (resString);
	}
	private String actionViewAltBOM(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		
		ResultSet rs = null;
		String sql = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		String bomCode = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
	
		try
		{
			if(conn==null)
			{
				conn = connDriver.getConnectDB("DriverITM");
			}
		
			bomCode = genericUtility.getColumnValue("bom_code",dom1);
			if(bomCode!=null)
			{
			
				sql = "SELECT ITEM_CODE, ITEM_REF, ITEM_CODE__ALT, QUANTITY, ITEM_CODE_ALT_SEQ " 
				    + "FROM BOM_ALT  "
				   	+ "WHERE BOM_CODE = ?"
					+ "ORDER BY ITEM_CODE_ALT_SEQ";
					System.out.println("Sql Query::::::"+sql);
					System.out.println("Setting Parameter:::"+bomCode);
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,bomCode);
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<item_ref>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</item_ref>\r\n");
					valueXmlString.append("<item_code__alt>").append("<![CDATA[").append(rs.getString(3).trim()).append("]]>").append("</item_code__alt>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getString(4).trim()).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<item_code_alt_seq>").append("<![CDATA[").append(rs.getString(5).trim()).append("]]>").append("</item_code_alt_seq>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
			}
			valueXmlString.append("</Root>\r\n");
		}
		catch (SQLException se)
		{
			System.out.println("Exception : BOMActEJB : actionViewAltBOM " +se.getMessage());
			se.printStackTrace();
			System.out.println("SQL : " + sql);
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception : BOMActEJB : actionViewAltBOM :(Document dom)" +e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try{
				conn.close();
				conn = null;
				pstmt.close();
			}catch(Exception e){}
		}
		//stem.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();			
	}
	private String actionStock(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		//atement stmt = null;
		ResultSet rs = null; 
		
		String sql = null;
		String bomCode=null;
		String trackShelfLife = null;
		String itemCode = null;
		String 	reqType = null;
				
		Timestamp currDate =null;
		
		double batchQtyD=0.0;
		double batchQty = 0.0;
		double qtyPar = 0.0;
		double minQty = 0.0;
		double reqQty =0.0;
		
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		try
		{ 
			if(conn==null)
			{
				conn = connDriver.getConnectDB("DriverITM");
			}
			bomCode = genericUtility.getColumnValue("bom_code",dom);
			System.out.println("bomCode From Dom::::::"+bomCode);
			
			batchQtyD= Double.parseDouble(genericUtility.getColumnValue("batch_qty",dom1));
			System.out.println("batchQtyD From Dom::::::"+batchQtyD);
				
			sql = "SELECT FN_SYSDATE()  FROM DUAL";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				currDate = rs.getTimestamp(1);
				
			}
			System.out.println("Current Date::::::"+currDate);
			rs.close();
			pstmt.close();
			
			if(bomCode!=null)
			{
				 sql = "SELECT BOM.ITEM_CODE, BOM.BOM_CODE,BOMDET.ITEM_CODE,ITEM_A.DESCR,BOMDET.REQ_TYPE,"
						+"ITEM_B.DESCR,SUM(BOMDET.MIN_QTY) AS MIN_QTY,SUM(BOMDET.QTY_PER) AS QTY_PER, MAX(BOM.BATCH_QTY) AS BATCH_QTY,"
						+"ITEM_B.DESCR,SUM(BOMDET.MIN_QTY) AS MIN_QTY,SUM(BOMDET.QTY_PER) AS QTY_PER, MAX(BOM.BATCH_QTY) BATCH_QTY,"
						+"0.0 AS STK_QTY,0.0 AS NON_STK_QTY,0.0 AS REQ_QTY,0.0 AS ALLOC_QTY  FROM BOM,BOMDET,ITEM ITEM_A,"
						+"ITEM ITEM_B "
						+" WHERE ( BOM.BOM_CODE = BOMDET.BOM_CODE ) AND"
						+"( BOM.ITEM_CODE = ITEM_A.ITEM_CODE ) AND"
						+"( BOMDET.ITEM_CODE= ITEM_B.ITEM_CODE ) AND"
						+"( ( BOM.BOM_CODE = ?) )"  
						+"GROUP BY BOM.BOM_CODE,BOM.ITEM_CODE,BOMDET.ITEM_CODE,BOMDET.REQ_TYPE,ITEM_A.DESCR,ITEM_B.DESCR"  ; 
						System.out.println("MainQuery:::"+sql);
						pstmt = conn.prepareStatement(sql);
						System.out.println("Setting BomCode as "+bomCode);
						pstmt.setString(1,bomCode);
						rs = pstmt.executeQuery();  
				
			
				 
				while (rs.next()) 
				{
					valueXmlString.append("<Detail>\r\n"); 
					itemCode = rs.getString(3);//BOM.ITEM_CODE
					reqType = rs.getString(5);//BOMDET.REQ_TYPE
					minQty   = rs.getDouble(7);//MIN_QTY
					qtyPar   = rs.getDouble(8);//QTY_PER;
					batchQty = rs.getDouble(9);//BATCH_QTY;
					valueXmlString.append("<bom_code>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</bom_code>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(3).trim()).append("]]>").append("</item_code>\r\n");
					valueXmlString.append("<descr>").append("<![CDATA[").append(rs.getString(6).trim()).append("]]>").append("</descr>\r\n");
					
				
					if(reqType.equalsIgnoreCase("s"))
					{
						reqQty = (batchQtyD / batchQty) * qtyPar;
					}
					else if(reqType.equalsIgnoreCase("P"))
					{
						reqQty = (qtyPar / batchQty) * batchQtyD;
					}
					else//f
					{
						
						reqQty = qtyPar;
					}
					if (reqQty < minQty)
					{
						 reqQty = minQty;
					}
					
					valueXmlString.append("<quantity>").append("<![CDATA[").append(reqQty).append("]]>").append("</quantity>\r\n");
					pstmt = null;
					
					pstmt = conn.prepareStatement("select nvl(track_shelf_life,'N') from item where item_code = ?") ;
					pstmt.setString(1,itemCode);
					ResultSet rs1 = pstmt.executeQuery();
					if(rs1.next())
					{
						trackShelfLife = rs1.getString(1);
						
					}
					rs1.close();
					StringBuffer sb = new StringBuffer();
					sb.append(" select nvl(sum(a.quantity - a.alloc_qty),0), nvl(sum(a.alloc_qty),0) from stock a, invstat b "
					+" where a.inv_stat = b.inv_stat  and a.item_code = ? and b.available = 'Y'  and b.stat_type <> 'S'  and (a.quantity - nvl(a.alloc_qty,0))  > 0 " );
					if(trackShelfLife.equalsIgnoreCase("Y"))
					{
						sb.append("and ( a.exp_date is null or  a.exp_date >?)");
					}
					pstmt = conn.prepareStatement(sb.toString());
					pstmt.setString(1,itemCode);
					if(trackShelfLife.equalsIgnoreCase("Y"))
					{
					
						pstmt.setTimestamp(2,currDate);
					}	
					rs1 = pstmt.executeQuery();
					while(rs1.next())  
					{
						valueXmlString.append("<stock>").append("<![CDATA[").append(rs1.getDouble(1)).append("]]>").append("</stock>\r\n");
						valueXmlString.append("<allock_qty>").append("<![CDATA[").append(rs1.getString(2).trim()).append("]]>").append("</allock_qty>\r\n");
					}
				
			
					valueXmlString.append("</Detail>\r\n");
				}
			}
			valueXmlString.append("</Root>\r\n");
			
				
		}
		catch (SQLException se)
		{
			System.out.println("Exception : BOMActEJB : actionStock " +se.getMessage());
			se.printStackTrace();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception : BOMActEJB : actionStock :(Document dom)" +e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try{
				conn.close();
				conn = null;
				pstmt.close();
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();			
	}
}