/**
 * Developed by : Ritesh on 19/FEB/2014
 * Purpose : Define volatile loss , scrap items (req: MF3HSUP001)
 */
package ibase.webitm.ejb.mfg.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;

import org.w3c.dom.*;

import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.mfg.MfgCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;

import javax.annotation.*;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class WoVolatileLossAct extends ActionHandlerEJB implements WoVolatileLossActLocal, WoVolatileLossActRemote
{

    public String actionHandler() throws RemoteException,ITMException
	{
		return "";
	}

    public String actionHandler(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams) throws RemoteException,ITMException
	{
    	System.out.println(" actionHandler CALLED");
		Document dom = null;
		Document dom1 = null;
		String  retString = null;
		String fdType="";
		Connection conn = null;
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");	
			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("Default"))
			{
				fdType = genericUtility.getColumnValue( "feedback_type", dom1 ) == null ?"":genericUtility.getColumnValue( "feedback_type", dom1 );
				System.out.println("fdType from actionHandler :-"+fdType);
				if(fdType.trim().length() > 0 && (!"C".equalsIgnoreCase(fdType)))
				{
					retString = itmDBAccess.getErrorString("","VTFDTYPE","","",conn);
				    return retString;
				}
				retString = getInvRcpAct(dom, dom1, xtraParams, actionType);
				dom = genericUtility.parseString(retString); 
				System.out.println(" retString  returning DETAIL :"+retString);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :WoVolatileLossAct :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from WoVolatileLossAct : actionHandler"+retString);
	    return retString;
	}
	
	private String getInvRcpAct(Document dom, Document dom1, String xtraParams, String actionType) throws RemoteException,ITMException //Added - - Gulzar 25/04/07
	{
		String sql="";
		String worder = "",itemCode= "",qtyFdStr="",itemCodeBom="";
		String VolatileItem= " ",siteCode="",locCode="",VolatileItemDescr="";
		String scrapItem=" ",scrapItemDescr="";
		ResultSet rs = null;
		Connection conn = null;
	    StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		double qtyFd =0d,billQty = 0d,procLossPerc = 0d,scrapPerc=0d,batchQty=0d,requiredItemQty=0d;
		double volatileQty = 0d,scrapQty = 0d ;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
	//	ITMDBAccessEJB itmDBAccess =null;
		MfgCommon mfgComm  = new MfgCommon();
		GenericUtility genericUtility = GenericUtility.getInstance();
		DecimalFormat df = new DecimalFormat("0.000");
		try
		{
		//	itmDBAccess = new ITMDBAccessEJB();
			conn = connDriver.getConnectDB("DriverITM");
			worder = genericUtility.getColumnValue("work_order",dom1) == null ? "" :genericUtility.getColumnValue("work_order",dom1);
			itemCode = genericUtility.getColumnValue( "item_code", dom1 ) == null ?"":genericUtility.getColumnValue( "item_code", dom1 );
			qtyFdStr = genericUtility.getColumnValue( "qty_feedback", dom1 ) == null ?"":genericUtility.getColumnValue( "qty_feedback", dom1 );
			if(qtyFdStr.trim().length() > 0)
				qtyFd = Double.parseDouble(qtyFdStr);
			siteCode = genericUtility.getColumnValue( "site_code", dom1 ) == null ?"":genericUtility.getColumnValue( "site_code", dom1 );

			System.out.println(" worder :-"+worder);
			System.out.println(" itemCode :-"+itemCode);
			System.out.println(" qtyFdStr and qtyFd :-"+qtyFdStr+":"+qtyFd);
			
			String phyAttVolItem = mfgComm.getEnvMfg("999999","VOLATILE_ITEM_COL", conn); 
			String phyattScrabPerc = mfgComm.getEnvMfg("999999","ITEM_PERC_SCRAP", conn); 
			String phyattScrabItem = mfgComm.getEnvMfg("999999","SCRAP_ITEM_COL", conn); 

			sql = " select  quantity  from workorder_bill where work_order  = ? and item_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				billQty = rs.getDouble(1);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
//			
			sql = " select batch_qty from bom where bom_code in (select bom_code from workorder where work_order = ?) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				batchQty = rs.getDouble(1);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
			
			sql = " select proc_loss_perc,item_code from bomdet where bom_code  in (select bom_code from workorder where work_order = ? ) "+
					" and (proc_loss_perc is not null or length(trim(proc_loss_perc)) > 0) ";
					
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				procLossPerc = rs.getDouble(1);
				itemCodeBom = rs.getString(2) == null ?"":rs.getString(2);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
			
			sql = " select  quantity  from workorder_bill where work_order  = ? and item_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			pstmt.setString(2,itemCodeBom);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				requiredItemQty = rs.getDouble(1);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
			
			
			if( !("NULLFOUND").equalsIgnoreCase(phyAttVolItem))
			{
				sql = " select "+phyAttVolItem +" from item where item_code = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCodeBom);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					VolatileItem = rs.getString(1)== null ? " ":rs.getString(1);
				}
				pstmt.close();pstmt = null;
				rs.close();rs = null;
				sql = " select descr from item where item_code = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,VolatileItem);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					VolatileItemDescr = rs.getString(1) == null ?"":rs.getString(1);
				}
				pstmt.close();pstmt = null;
				rs.close();rs = null;
			}
			sql = "select LOC_CODE__REJ from siteitem where site_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				locCode = rs.getString(1);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
		//	volatileQty = ((billQty * qtyFd)/batchQty)*(procLossPerc/100);
			volatileQty = ((requiredItemQty * qtyFd)/batchQty)*(procLossPerc/100);
			if(volatileQty > 0 && VolatileItem.trim().length() > 0)
			{
				volatileQty = ((requiredItemQty * qtyFd)/batchQty)*(procLossPerc/100);
				
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<quantity>").append("<![CDATA[").append(df.format(volatileQty)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<item_code>").append("<![CDATA[").append(VolatileItem).append("]]>").append("</item_code>\r\n");						
				valueXmlString.append("<item_descr>").append("<![CDATA[").append(VolatileItemDescr).append("]]>").append("</item_descr>\r\n");						
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n");						
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(worder).append("]]>").append("</lot_no>\r\n");						
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append("1S").append("]]>").append("</lot_sl>\r\n");						
				valueXmlString.append("<inv_rcp_act>").append("<![CDATA[").append("V").append("]]>").append("</inv_rcp_act>\r\n");						
				valueXmlString.append("</Detail>\r\n");				
			}
			if( !("NULLFOUND").equalsIgnoreCase(phyattScrabPerc) &&  !("NULLFOUND").equalsIgnoreCase(phyattScrabItem))
			{
				sql  = " select "+phyattScrabPerc +"," +phyattScrabItem +" from item where item_code = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
				    String scrapPercstr = rs.getString(1) == null ?"0.0":rs.getString(1);
					scrapItem = rs.getString(2)== null ?" ":rs.getString(2);
					scrapPerc = Double.parseDouble(scrapPercstr);
				}
				pstmt.close();pstmt = null;
				rs.close();rs = null;
			}
			sql = " select descr from item where item_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,scrapItem);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				scrapItemDescr = rs.getString(1) == null ?"":rs.getString(1);
			}
			pstmt.close();pstmt = null;
			rs.close();rs = null;
			
		//scrapQty = ((billQty * qtyFd)/batchQty)*(scrapPerc/100);
			scrapQty = ((batchQty * qtyFd)/billQty)*(scrapPerc/100);
			if(scrapQty > 0 && scrapItem.trim().length() > 0)
			{
			
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<quantity>").append("<![CDATA[").append(df.format(scrapQty)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<item_code>").append("<![CDATA[").append(scrapItem).append("]]>").append("</item_code>\r\n");						
				valueXmlString.append("<item_descr>").append("<![CDATA[").append(scrapItemDescr).append("]]>").append("</item_descr>\r\n");						
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n");						
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(worder).append("]]>").append("</lot_no>\r\n");						
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append("1S").append("]]>").append("</lot_sl>\r\n");						
				valueXmlString.append("<inv_rcp_act>").append("<![CDATA[").append("S").append("]]>").append("</inv_rcp_act>\r\n");						
				valueXmlString.append("</Detail>\r\n");				
				
			}
			valueXmlString.append("</Root>\r\n");		
		}
		catch (SQLException sqx)
		{
			System.out.println("SQLException : getInvRcpAct : : " +sqx.getMessage());
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("Exception : getInvRcpAct :  :(Document dom) : " +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try{
				conn.close();
				conn = null;
			}catch(Exception e){}
		}
		System.out.println("valueXmlString.toString() "+valueXmlString.toString());
		return valueXmlString.toString();			
	}
}