package ibase.webitm.ejb.dis;

import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.system.config.*;
import java.rmi.RemoteException;
import ibase.system.config.AppConnectParm;
import java.text.SimpleDateFormat;
import ibase.utility.CommonConstants;

import java.util.*;
import java.sql.*;
import java.io.Serializable;
import javax.ejb.*;
import javax.naming.InitialContext;
//import javax.ejb.SessionBean;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class StockHelp implements StockHelpLocal,StockHelpRemote
{
	CommonConstants commonConstants = new CommonConstants();
	/* public void ejbCreate() throws RemoteException,CreateException
	{
		
	}

	public void ejbRemove()
	{
	}

	public void ejbActivate()
	{
	}

	public void ejbPassivate()
	{
	} */

	public void setSessionContext(SessionContext mSessionContext)
	{
	}
	// 24/08/11 manoharan
	public String getItemDescr(String itemCode) throws ITMException
    {
		String itemDescr = "";
		GenericUtility genericUtility = null;
		genericUtility = GenericUtility.getInstance();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM"); 
			conn.setAutoCommit(false);
			connDriver = null;
			pstmt = conn.prepareStatement("select descr from item where item_code = ?");	
			pstmt.setString(1,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				itemDescr = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
				
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{			
				genericUtility = null;				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close(); 
					rs = null;				
				}
				if(conn != null)
				{					
					conn.close();
					conn=null;
				}				
			}
			catch(Exception e)
			{
				System.out.println("Exception :StockHelpEJB:==>\n"+e.getMessage());
			}
		}
		return itemDescr;
	}
	// end 24/08/11 manoharan
	public ArrayList getDetails(String siteCode, String itemCode) throws ITMException
    {
		GenericUtility genericUtility = null;
		genericUtility = GenericUtility.getInstance();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		StockDtlParams thisBean = null;
		ArrayList stkHelpList = new ArrayList();
		
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM"); 
			conn.setAutoCommit(false);
			connDriver = null;
			int i = 1;
			double lotWiseQuant = 0, lotWiseAllocQuant = 0, lotWiseAvailQuant = 0;
			double lotWiseHoldQuant = 0, avl = 0;
			String quantKey = "", sql = "";
			String allcQuantKey = "", prevLocCode = "", prevLotNo = "";
			String holdQuanKey = "", prevLotSl = "";
			System.out.println("itemCode>>>>>>>>>>>>>>>>"+itemCode);
			System.out.println("siteCode>>>>>>>>>>>>>>>>"+siteCode);
			
			sql = "SELECT  stock.site_code ,site.descr ,stock.item_code ,item.descr ,stock.loc_code , "
			+" stock.lot_no ,stock.lot_sl ,location.inv_stat ,stock.retest_date ,stock.exp_date ,"
			+" sum(stock.quantity) qty,sum(stock.alloc_qty) alloc_qty,invstat.available ,stock.unit , "
			+" stock.potency_perc ,stock.batch_no ,stock.dimension ,stock.supp_code__mfg , ";
			if((CommonConstants.DB_NAME).equalsIgnoreCase("mssql"))
			{
				sql = sql + " dbo.fn_ppo_qty('"+siteCode+"','"+itemCode+"') as ppo_qty,dbo.fn_pind_qty('"+siteCode+"','"+itemCode+"') as pind_qty,stock.pack_code , ";
			}
			else
			{
				sql = sql + " fn_ppo_qty('"+siteCode+"','"+itemCode+"') as ppo_qty,fn_pind_qty('"+siteCode+"','"+itemCode+"') as pind_qty,stock.pack_code , ";
			}
			
			sql =  sql + " stock.qty_per_art ,stock.batch_size , "
			+" sum(case when stock.hold_qty is null then 0 else stock.hold_qty end)  hold_qty, "
			+" stock.mfg_date, stock.no_art, stock.rate,stock.pack_ref "
			+" FROM invstat ,item ,location ,site ,stock "
			+" WHERE ( location.inv_stat = invstat.inv_stat )  "
			+" and ( stock.loc_code = location.loc_code ) "
			+" and ( stock.item_code = item.item_code ) "
			+" and ( site.site_code = stock.site_code ) "
			+" and ( ( stock.item_code = '"+itemCode+"' ) and ( stock.site_code = '"+siteCode+"' ) ) "
			+" and ( invstat.stat_type <> 'S' ) "
			+" and ( stock.quantity <> 0 ) "
			+" GROUP BY stock.site_code ,site.descr ,stock.item_code ,item.descr ,stock.loc_code , "
			+" stock.lot_no ,stock.lot_sl ,location.inv_stat ,invstat.available ,stock.retest_date , "
			+" stock.exp_date ,stock.unit ,stock.potency_perc ,stock.batch_no ,stock.dimension , "
			+" stock.supp_code__mfg ,stock.pack_code ,stock.qty_per_art , "
			+" stock.batch_size, stock.mfg_date, stock.no_art, stock.rate,stock.pack_ref  "
			+" ORDER BY stock.site_code ASC, stock.item_code ASC, "
			+" stock.loc_code ASC, stock.lot_no ASC, stock.lot_sl ASC, location.inv_stat ASC ";
			
			pstmt = conn.prepareStatement(sql);	
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				thisBean = new StockDtlParams();
				
				thisBean.itemCode = rs.getString(3);
				thisBean.currLocCode = rs.getString(5);
				thisBean.currLotNo = rs.getString(6);
				thisBean.currLotSl = rs.getString(7);
				thisBean.invStat = rs.getString(8);
				thisBean.retestDate = rs.getString(9) == null ? "" : genericUtility.getValidDateString(rs.getString(9),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				thisBean.expDate = rs.getString(10) == null ? "" : genericUtility.getValidDateString(rs.getString(10),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				thisBean.quantity = rs.getDouble(11);
				thisBean.allocQuant = rs.getDouble(12);
				thisBean.available = rs.getString(13);
				thisBean.unit = rs.getString(14);
				thisBean.potencyPerc = rs.getString(15);
				thisBean.batchNo = rs.getString(16);
				thisBean.dimention = rs.getString(17);
				thisBean.suppCodeMfg = rs.getString(18);
				thisBean.ppoQty = rs.getString(19);
				thisBean.pindQty = rs.getString(20);
				thisBean.packCode = rs.getString(21);
				thisBean.qtyPerArt = rs.getString(22);
				thisBean.batchSize = rs.getString(23);
				thisBean.holdQuant = rs.getDouble(24);
				thisBean.mfgDate = rs.getString(25) == null ? "" : genericUtility.getValidDateString(rs.getString(25),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				thisBean.noArt = rs.getString(26); //Changed By Dipak Chattar On 15 Nov 2011
				thisBean.rate = rs.getString(27);
				thisBean.packRef = rs.getString(28);//added By Swati Bade On 09 May 2013
				
				thisBean.currDateTs = getCurrdateTimestmp();
				avl = thisBean.quantity - thisBean.allocQuant;
				thisBean.netAvailable = Double.toString(avl);//balance
				if( thisBean.currLocCode != null && thisBean.currLotNo != null )
				{
					if( thisBean.currLocCode.equals(prevLocCode) && thisBean.currLotNo.equals(prevLotNo) )
					{
						lotWiseQuant = lotWiseQuant + thisBean.quantity;
						lotWiseAllocQuant = lotWiseAllocQuant + thisBean.allocQuant;
						//lotWiseAvailQuant = lotWiseAvailQuant + thisBean.availQuant
						lotWiseHoldQuant = lotWiseHoldQuant + thisBean.holdQuant;
						quantKey = "LWQ" + i;
						allcQuantKey = "LAQ" + i;
						holdQuanKey = "LHQ" + i;
						(thisBean.lotQuantMap).put(quantKey, new Double(lotWiseQuant) );
						thisBean.lotQuantMap.put(allcQuantKey, new Double(lotWiseAllocQuant));
						thisBean.lotQuantMap.put(holdQuanKey, new Double(lotWiseHoldQuant));
					}
					else
					{
						i++;
						lotWiseQuant = 0;
						lotWiseAllocQuant = 0;
						lotWiseAvailQuant = 0;
						lotWiseHoldQuant = 0;
						lotWiseQuant = lotWiseQuant + thisBean.quantity;
						lotWiseAllocQuant = lotWiseAllocQuant + thisBean.allocQuant;
						//lotWiseAvailQuant = lotWiseAvailQuant + thisBean.availQuant;
						lotWiseHoldQuant = lotWiseHoldQuant + thisBean.holdQuant;
						quantKey = "LWQ" + i;
						allcQuantKey = "LAQ" + i;
						holdQuanKey = "LHQ" + i;
						thisBean.lotQuantMap.put(quantKey, new Double(lotWiseQuant));
						thisBean.lotQuantMap.put(allcQuantKey, new Double(lotWiseAllocQuant));
						thisBean.lotQuantMap.put(holdQuanKey, new Double(lotWiseHoldQuant));
					}
				}
				stkHelpList.add(thisBean);
				prevLocCode = thisBean.currLocCode;
				prevLotNo = thisBean.currLotNo;
				prevLotSl = thisBean.currLotSl;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{			
				genericUtility = null;				
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close(); 
					rs = null;				
				}
				if(conn != null)
				{					
					conn.close();
					conn=null;
				}				
			}
			catch(Exception e)
			{
				System.out.println("Exception :StockHelpEJB:==>\n"+e.getMessage());
			}
		}
		return stkHelpList;
	}
	private Timestamp getCurrdateTimestmp()
    {
        String s = "";	
		Timestamp timestamp = null;		
        GenericUtility genericUtility = GenericUtility.getInstance();
        try
        {
            java.util.Date date = null;
            timestamp = new Timestamp(System.currentTimeMillis());
            SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
            date = simpledateformat.parse(timestamp.toString());
            timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
		}
        catch(Exception exception)
        {
            System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
        }
        return timestamp;
    }
	class StockDtlParams implements Serializable
	{
		HashMap lotQuantMap = new HashMap();
		String currLocCode = "", currLotNo = "", currLotSl = "", invStat = "", retestDate = "";
		String expDate = "",unit = "", potencyPerc = "", batchNo = "", netAvailable = "";
		String dimention = "", suppCodeMfg = "", ppoQty = "", pindQty = "", packCode = "";
		String qtyPerArt = "", batchSize = "", mfgDate = "", rate = "", noArt = "" , packRef = "";
		String itemCode = "", available = "";
		double quantity = 0, availQuant = 0, holdQuant = 0, allocQuant = 0;	
		java.sql.Timestamp currDateTs = null;	
	}
}