package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import javax.ejb.SessionContext;
import javax.ejb.Stateless;
//import javax.ejb.SessionBean;
// 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 loadData(String lotno,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);
				System.out.println("LOTNO>>>>>>>>>>>>>>>>"+lotno);
		    sql= "select f.TRAN_ID,f.TRAN_DATE,f.REF_SER,f.REF_ID,f.REF_LINE,f.ORDER_ID,f.ITEM_CODE,"
		    		+ "f.SITE_CODE,f.LOC_CODE,f.UNIT,f.LOT_NO,f.LOT_SL,f.QUANTITY,f.EFF_QTY,f.EFF_DATE,"
		    		+ "f.CHG_WIN,f.QTY_BEFORE,f.QTY_AFTER,f.REAS_CODE,f.SUNDRY_TYPE,f.SUNDRY_CODE,"
		    		+ "f.REF_DATE,f.REF_SER__FOR,f.REF_ID__FOR,f.ACCT_CODE__DR,f.CCTR_CODE__DR,"
		    		+ "f.ACCT_CODE__CR,f.CCTR_CODE__CR,f.GRADE,RATE,f.GROSS_RATE,f.NO_ART,f.DIMENSION,"
		    		+ "f.ACTUAL_QTY,f.CONV__QTY_STDUOM,f.AMOUNT,f.INV_STAT,f.RATE_INSU from invtrace f , Invstat d where "
				+ " f.inv_stat = d.inv_stat and d.stat_type <> 'S' and site_code = '"+sitecode+"' AND ITEM_CODE = '"+itemcode+"' AND LOT_NO = '"+lotno+"' "
				+ "and ref_ser in ('P-RCP','D-RCP','ADJRCP')";
		pstmt = conn.prepareStatement(sql);	
		rs = pstmt.executeQuery();
		while(rs.next())
		{
			thisBean = new StockDtlParams();
			thisBean.tranid = rs.getString(1);
			thisBean.trandate = rs.getString(2) == null ? "" : genericUtility.getValidDateString(rs.getString(2),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
			thisBean.refser = rs.getString(3);
			thisBean.refid = rs.getString(4);
			thisBean.refline = rs.getString(5);
			thisBean.orderid = rs.getString(6);
			thisBean.itemCode1 = rs.getString(7);
			thisBean.siteCode1 = rs.getString(8);
			thisBean.currLocCode = rs.getString(9);
			thisBean.unit = rs.getString(10);
			thisBean.currLotNo = rs.getString(11);
			thisBean.currLotSl = rs.getString(12);
			thisBean.quantity = rs.getDouble(13);
			thisBean.effqty = rs.getDouble(14);
			thisBean.efedate = rs.getString(15) == null ? "" : genericUtility.getValidDateString(rs.getString(15),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
			thisBean.chgwin = rs.getString(16);
			thisBean.qtybefore = rs.getDouble(17);
			thisBean.qtyaft = rs.getDouble(18);
			thisBean.reascode = rs.getString(19);
			thisBean.sundrytype = rs.getString(20);
			thisBean.sundrycode = rs.getString(21);
			thisBean.refdate = rs.getString(22) == null ? "" : genericUtility.getValidDateString(rs.getString(22),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
			thisBean.refserfor = rs.getString(23);
			thisBean.refidfor = rs.getString(24);
			thisBean.acctcddr = rs.getString(25);
			thisBean.cctrcddr = rs.getString(26);
			thisBean.acctcdcr = rs.getString(27);
			thisBean.cctrcdcr = rs.getString(28);
			thisBean.grade = rs.getString(29);
			thisBean.rateinv = rs.getDouble(30);
			thisBean.grossrate = rs.getDouble(31);
			thisBean.noartinv = rs.getDouble(32);
			thisBean.dimention = rs.getString(33);
			thisBean.actqty = rs.getDouble(34);
			thisBean.convqty = rs.getDouble(35);
			thisBean.amount = rs.getDouble(36);
			thisBean.invStat1 = rs.getString(37);
			thisBean.rateinsu = rs.getDouble(38);
			
			stkHelpList.add(thisBean);
		}
		
	    }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;
	}
	
	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.partial_used, stock.consider_allocate, 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.partial_used ,stock.consider_allocate ,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);
				
//				sql = "";
//				sql= "SELECT inv_stat FROM LOCATION WHERE LOC_CODE = ?";
//				
//			    pstmt = conn.prepareStatement(sql);
//				pstmt.setString(1, thisBean.currLocCode);
//				rs = pstmt.executeQuery();
//				if(rs.next())
//				{
//					thisBean.invstst = rs.getString("inv_stat") == null ? "":rs.getString("inv_stat");
//				
//				}
//				
//				pstmt.close();
//				pstmt = null;
//				sql = null;
//				rs.close();
//				rs = null;
//				
//				sql = "";
//				sql= "select available from invstat where inv_stat = ?";
//				
//			    pstmt = conn.prepareStatement(sql);
//				pstmt.setString(1, thisBean.invstst);
//				rs = pstmt.executeQuery();
//				if(rs.next())
//				{
//					thisBean.availableinv = rs.getString("available") == null ? "":rs.getString("available");
//				
//				}
//				
//				pstmt.close();
//				pstmt = null;
//				sql = null;
//				rs.close();
//				rs = null;
				
				
				
				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);
//				if(thisBean.allocQuant == 0 || thisBean.allocQuant == 0.0)
//				{
//					thisBean.allocQuantLable = "N";
//				}
//				else
//				{
//					thisBean.allocQuantLable = "Y";
//				}
				thisBean.available = rs.getString(13);
				thisBean.unit = rs.getString(14);
				thisBean.potencyPerc = rs.getString(15);  
				thisBean.partialused = rs.getString(16);           // added by deepak sawant
				if(thisBean.partialused == null)
				{
					thisBean.partialused = "Intact";
				}
				else
				{
					thisBean.partialused  = "Yes";
				}
				thisBean.allocQuantLable = rs.getString(17);
				
				thisBean.batchNo = rs.getString(18);
				thisBean.dimention = rs.getString(19);
				thisBean.suppCodeMfg = rs.getString(20);
				thisBean.ppoQty = rs.getString(21);
				thisBean.pindQty = rs.getString(22);
				thisBean.packCode = rs.getString(23);
				thisBean.qtyPerArt = rs.getString(24);
				thisBean.batchSize = rs.getString(25);
				thisBean.holdQuant = rs.getDouble(26);
				thisBean.mfgDate = rs.getString(27) == null ? "" : genericUtility.getValidDateString(rs.getString(27),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				thisBean.noArt = rs.getString(28); //Changed By Dipak Chattar On 15 Nov 2011
				thisBean.rate = rs.getString(29);
				thisBean.packRef = rs.getString(30);//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 = "", availableinv = "", invstst = "";
		String expDate = "",unit = "", potencyPerc = "", partialused = "", batchNo = "", netAvailable = "",allocQuantLable = "";
		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,effqty = 0,qtybefore = 0,qtyaft = 0,rateinv = 0,noartinv = 0,grossrate = 0,actqty = 0,convqty = 0,amount = 0,rateinsu = 0;	
		
		String trandate = "",tranid ="",refser = "",refid = "",refline = "",orderid = "",siteCode = "",efedate = "",chgwin = "",reascode = "",itemCode1 = "",siteCode1 = "";
		String sundrytype = "",sundrycode = "",refdate = "",refserfor = "",refidfor = "",acctcddr = "",cctrcddr = "",acctcdcr = "",cctrcdcr = "",grade = "",invStat1 = "";
		
		
		java.sql.Timestamp currDateTs = null;	
	}
}