/* 
	Window Name : w_inv_freeze
	Button Name : (Detail,Alldue)
    Action      : Adding Records  for Detail Count,Alldue. 
*/
package ibase.webitm.ejb.dis.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Random;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;
import java.text.SimpleDateFormat;

import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class InvFreezeAct extends ActionHandlerEJB implements InvFreezeActLocal, InvFreezeActRemote
{
	/*public void ejbCreate() throws RemoteException, CreateException 
	{
	}

	public void ejbRemove()
	{
	}

	public void ejbActivate() 
	{
	}

	public void ejbPassivate() 
	{
	}*/

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

	public String actionHandler(String actionType, String xmlString, String objContext, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		String  resString = null;
		try
		{
			if (actionType.equalsIgnoreCase("AllItem"))
			{
				if(xmlString != null && xmlString.trim().length()!=0)
				{
					GenericUtility genericUtility = GenericUtility.getInstance();
					System.out.println("XML String :"+xmlString);
					dom = genericUtility.parseString(xmlString); 
				}
				resString = actionAllItem(dom,objContext,xtraParams);
			}
			if (actionType.equalsIgnoreCase("AllDue"))
			{
				if (xmlString != null && xmlString.trim().length()!=0)
				{
					GenericUtility genericUtlity= GenericUtility.getInstance();
					System.out.println("XML String :"+ xmlString);
					dom = genericUtlity.parseString(xmlString);
				}
				resString = actionAllDue(dom,objContext,xtraParams);
			}
			if (actionType.equalsIgnoreCase("Random"))
			{
				if(xmlString != null && xmlString.trim().length()!=0)
				{
					GenericUtility genericUtility = GenericUtility.getInstance(); 
					System.out.println("XML String :"+xmlString);
					dom = genericUtility.parseString(xmlString); 
				}
				resString = actionRandom(dom,objContext,xtraParams);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :InvFreeze :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from actionDetail actionHandler"+resString);
		return resString;
	}

	private String actionAllItem(Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		String siteCode = "", locCodeFrom = "", locCodeTo = "";
		String itemSerFrom = "", itemSerTo = "";
		String sql = "", sql1 = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null, rs1 = null;
		String errCode = "" ,errString = "", returnValue1 = "";
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			siteCode= genericUtility.getColumnValue("site_code",dom);
			locCodeFrom = genericUtility.getColumnValue("loc_code__from",dom);
			locCodeTo = genericUtility.getColumnValue("loc_code__to",dom);
			itemSerFrom = genericUtility.getColumnValue("item_ser__from",dom);
			itemSerTo = genericUtility.getColumnValue("item_ser__to",dom);
			System.out.println("siteCode ="+siteCode);
			System.out.println("locCodeFrom ="+locCodeFrom);
			System.out.println("locCodeTo ="+locCodeTo);
			System.out.println("ItemSerFrom ="+itemSerFrom);
			System.out.println("ItemSerTo ="+itemSerTo);
			sql = "SELECT A.ITEM_CODE,A.LOC_CODE,A.LOT_NO,A.LOT_SL,A.QUANTITY,A.UNIT FROM STOCK A,INVSTAT B "+
				  "WHERE A.SITE_CODE = '"+siteCode+ "' AND LOC_CODE >= '"+locCodeFrom + "' AND LOC_CODE <= '"+locCodeTo+ "' AND ITEM_SER >= '"+itemSerFrom+"' "+
					"AND ITEM_SER <= '"+itemSerTo+ "' AND A.INV_STAT=B.INV_STAT AND B.AVAILABLE='Y' ";
			System.out.println("sql "+sql);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(1)).append("]]>").append("</item_code>\r\n");
				sql1 = "SELECT DESCR FROM ITEM WHERE ITEM_CODE='" +rs.getString(1)+ "'";
				rs1 = stmt.executeQuery(sql1);
				if(rs1.next())
				{
					returnValue1 = rs1.getString(1);
					valueXmlString.append("<item_descr>").append(returnValue1).append("</item_descr>\r\n");
				}
				else
				{
					returnValue1="";
					valueXmlString.append("<item_descr>").append(returnValue1).append("</item_descr>\r\n");
				}
				valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(2)).append("]]>").append("</loc_code>\r\n");
				valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(3)).append("]]>").append("</lot_no>\r\n");
				valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(4)).append("]]>").append("</lot_sl>\r\n");
				valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getString(5)).append("]]>").append("</quantity>\r\n");
				valueXmlString.append("<unit>").append("<![CDATA[").append(rs.getString(6)).append("]]>").append("</unit>\r\n");
				valueXmlString.append("</Detail>\r\n");
			}
			stmt.close();				
			valueXmlString.append("</Root>\r\n");			
		}
		catch(SQLException e)
		{
			System.out.println("Exception : InvFreeze : actionAllRecords " +e.getMessage());
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			System.out.println("Exception : InvFreeze : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection........");
				conn.close();
				conn = null;
			}catch(SQLException se){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionAllDue(Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		String siteCode = "";
		String locCodeFrom = "";
		String locCodeTo = "";
		String itemSerFrom = "";
		String itemSerTo = "";
		String tranDate= "";
		String sql = "", sql1 = "", sql2 = "";
		String returnValue1 = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null, rs1 = null, rs2 = null;
		String errCode = "" ,errString = "";
		String detCnt = "0";
		java.sql.Date phycDate = null;
		java.sql.Date tempDate = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			siteCode= genericUtility.getColumnValue("site_code",dom);
			locCodeFrom = genericUtility.getColumnValue("loc_code__from",dom);
			locCodeTo = genericUtility.getColumnValue("loc_code__to",dom);
			itemSerFrom = genericUtility.getColumnValue("item_ser__from",dom);
			itemSerTo = genericUtility.getColumnValue("item_ser__to",dom);
			tranDate = genericUtility.getColumnValue("tran_date",dom);
			System.out.println("siteCode ="+siteCode);
			System.out.println("locCodeFrom ="+locCodeFrom);
			System.out.println("locCodeTo ="+locCodeTo);
			System.out.println("ItemSerFrom ="+itemSerFrom);
			System.out.println("ItemSerTo ="+itemSerTo);
			System.out.println("TranDate="+tranDate);
			
			sql = "SELECT A.ITEM_CODE,A.LOC_CODE,A.LOT_NO,A.LOT_SL,A.QUANTITY,A.UNIT,A.LAST_PHYC_DATE FROM STOCK A,INVSTAT B "+
					"WHERE A.SITE_CODE ='"+siteCode+ "' AND LOC_CODE >=' "+locCodeFrom + "' AND LOC_CODE <= '"+locCodeTo+ "'AND ITEM_SER >='"+itemSerFrom+ "'"+
					"AND ITEM_SER <='"+itemSerTo+ "'AND A.INV_STAT=B.INV_STAT AND B.AVAILABLE='Y'";
			System.out.println("sql "+sql);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				phycDate = rs.getDate("LAST_PHYC_DATE");
				sql2 = "SELECT CYCLE_COUNT FROM ITEM WHERE ITEM_CODE = '"+rs.getString("ITEM_CODE")+"'";
				rs2 = stmt.executeQuery(sql2);
				if (rs2.next())
				{
					//phycDate.setDate(rs2.getInt("CYCLE_COUNT"));
					phycDate = new java.sql.Date(phycDate.getTime()+(rs2.getInt("CYCLE_COUNT")* 60 * 60 * 1000)); 
				}
				tranDate = sdf.format(tranDate);
				tempDate = java.sql.Date.valueOf(tranDate);
				if (phycDate.before(tempDate))
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(rs.getString(1)).append("]]>").append("</item_code>\r\n");
					sql1 = "SELECT  DESCR FROM ITEM WHERE ITEM_CODE='" +rs.getString(1)+ "'";
					rs1 = stmt.executeQuery(sql1);
					if(rs1.next())
					{
						returnValue1 = rs1.getString(1);
						valueXmlString.append("<item_descr>").append(returnValue1).append("</item_descr>\r\n");
					}
					else
					{
						returnValue1="";
						valueXmlString.append("<item_descr>").append(returnValue1).append("</item_descr>\r\n");
					}
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs.getString(2)).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs.getString(3)).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs.getString(4)).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[").append(rs.getString(5)).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<unit>").append("<![CDATA[").append(rs.getString(6)).append("]]>").append("</unit>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}				
			}
			valueXmlString.append("</Root>\r\n");						
		}
		catch(SQLException e)
		{
			System.out.println("Exception : InvFreeze : actionAllRecords " +e.getMessage());
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			System.out.println("Exception : InvFreeze : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection........");
				conn.close();
				conn = null;
			}catch(SQLException se){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionRandom(Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{	
		String siteItem = "", status= "", itemCode= "";
		String siteCode = "", locCodeFrom = "", locCodeTo = "";
		String ItemSerFrom = "", ItemSerTo = "", tranId = "";
		String lineNo = "", sql1 = "", sql2= "", sql3 = "", sql4 = "", sql5 = "", sql6 = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		Connection conn = null;
		Statement stmt1 = null, stmt2 = null, stmt3 = null, stmt4 = null;
		Statement stmt5 = null, stmt6 = null;
		ResultSet rs1 = null, rs2 = null, rs3 = null, rs4 = null, rs5 = null, rs6 = null;
		String returnValue1 = "", returnValue2 = "";
		String errCode = "" ,errString = "";
		String detCnt = "0",varValue="";
		boolean bool=false;
		double retries=0;
		int xx,zz,cnt=0, rowId = 0, ItmCount = 0, maxItems=400;
		String stackOptions[]={"1","2"};
		ArrayList site= new ArrayList();
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		try
		{
			Random rnd = new Random();
			conn = connDriver.getConnectDB("DriverITM");
			stmt1= conn.createStatement();
			tranId= genericUtility.getColumnValue("tran_id",dom);
			System.out.println("tranId :"+tranId);
			lineNo= genericUtility.getColumnValue("line_no",dom);
			System.out.println("lineNo :"+lineNo);
			status= genericUtility.getColumnValue("status",dom);
			System.out.println("status :"+status);
			siteCode= genericUtility.getColumnValue("site_code",dom);
			System.out.println("siteCode :"+siteCode);
			locCodeFrom = genericUtility.getColumnValue("loc_code__from",dom);
			System.out.println("locCodeFrom :"+locCodeFrom);
			locCodeTo = genericUtility.getColumnValue("loc_code__to",dom);
			System.out.println("locCodeTo :"+locCodeTo);
			ItemSerFrom = genericUtility.getColumnValue("item_ser__from",dom);
			System.out.println("ItemSerFrom :"+ItemSerFrom);
			ItemSerTo = genericUtility.getColumnValue("item_ser__to",dom);
			System.out.println("ItemSerTo :"+ItemSerTo);
			if (status.equals("F") || status.equals("U") || status.equals("V"))
			{
				errCode = "";
				errString = itmDBAccess.getErrorString("",errCode,"","",conn);
				System.out.println("errString :"+errString);
				return errString;
			}
			sql1="SELECT VAR_VALUE,VAR_VALUE FROM DISPARM WHERE VAR_NAME= 'PHYCNTITEMS' AND PRD_CODE = '999999'";
			System.out.println(sql1);
			
			rs1 = stmt1.executeQuery(sql1);
			if(rs1.next())
			{
				maxItems = rs1.getInt(1);
				System.out.println("maxItems  :"+maxItems);
				varValue = rs1.getString(2);
				System.out.println("varValue :"+varValue);
			}
			if (varValue == null || varValue.equals("0"))
			{
				varValue = "0";
				maxItems= 10;
			}
			
			sql2="SELECT SITEITEM.SITE_CODE,SITEITEM.ITEM_CODE,ITEM.ITEM_SER FROM SITEITEM,ITEM "
				+"WHERE  SITEITEM.ITEM_CODE = ITEM.ITEM_CODE AND SITEITEM.SITE_CODE ='"+siteCode+"' "
				+"AND ITEM.ITEM_SER >='"+ItemSerFrom+"' AND ITEM.ITEM_SER <='"+ItemSerTo+"' "
				+"AND ITEM.STK_OPT IN ('1','2')"; 
			System.out.println("sql2="+sql2);
			
			stmt2= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs2=stmt2.executeQuery(sql2);
			int noOfRows = 0;
			while(rs2.next())
			{
				noOfRows++;
			}
			System.out.println("noOfRows :"+noOfRows);

			for (xx = 1; xx <= maxItems; xx++)
			{
				retries++;
				rowId = rnd.nextInt(noOfRows);
				System.out.println("rowId :"+rowId);
				System.out.println("noOfRows :"+noOfRows);
				//if(noOfRows == 1)
				if(rowId < noOfRows)// by using 21 it was ok.
				{
					System.out.println("rowId :"+rowId);
					if (rowId == 0)
					{
						System.out.println("rowId :"+rowId);
						rowId = 1;
						System.out.println("rowId :"+rowId);
					}
					System.out.println("rowId :"+rowId);
					rs2.absolute(rowId);
					//if(rs2.next())
					//{
						itemCode = rs2.getString(2);
						System.out.println("itemCode :"+itemCode);
					//}
					boolean itemSelected = false;
					System.out.println("site.size() :"+site.size());
					for (zz = 0; zz < site.size(); zz++)
					{   
						System.out.println("site.size() :"+site.size());
						System.out.println("site.get(zz)=="+site.get(zz));
						if (site.get(zz).toString().equalsIgnoreCase(itemCode))
						{
							System.out.println("ItemCode found and flag set to true");
							itemSelected = true;
							cnt=0;
						}
					}//for
					if (!itemSelected)
					{
						sql3 = "SELECT COUNT(*) FROM STOCK S,INVSTAT ISTAT "
							  +"WHERE S.ITEM_CODE= '"+itemCode+"' AND S.SITE_CODE='"+siteCode+"' "
							  +"AND S.LOC_CODE>= '"+locCodeFrom+"' AND S.LOC_CODE<='"+locCodeTo+"' "
							  +"AND S.INV_STAT = ISTAT.INV_STAT AND ISTAT.AVAILABLE ='Y'";
						System.out.println("sql3 :"+sql3);
						rs1 = stmt1.executeQuery(sql3);
						if (rs1.next())
						{
							cnt = rs1.getInt(1);
							System.out.println("cnt :"+cnt);
						}
					}
					if (cnt >= 1)
					{
						site.add(itemCode);
					}
					else
					{
						if (retries > 4)
						{
							site.add("");
						}
						else
						{
							continue;
						}
					}// else end
				}
			}//end for	
			xx = site.size();
			System.out.println("Size of ArrayList XX:"+xx);
			if (xx == 0)
			{
				errCode = "VTDETCNT";
				errString = itmDBAccess.getErrorString("",errCode,"","",conn);
				System.out.println("errString :"+errString);
				return errString;
			}
			for (xx = 1;xx <= maxItems; xx++)
			{
				System.out.println("Inside for :"+site.size());
				if (xx >= site.size())
				{
					continue;
				}
				itemCode = (String)site.get(xx);
				System.out.println("itemCode :"+itemCode);
				sql4="SELECT STOCK.SITE_CODE,STOCK.QUANTITY,STOCK.LOT_NO,STOCK.LOT_SL,STOCK.ITEM_SER, "
					+"STOCK.LOC_CODE,STOCK.ITEM_CODE,STOCK.UNIT FROM STOCK,ITEM,LOCATION,INVSTAT "
					+"WHERE ITEM.ITEM_CODE = STOCK.ITEM_CODE "
					+"AND LOCATION.LOC_CODE=STOCK.LOC_CODE "
					+"AND LOCATION.INV_STAT = INVSTAT.INV_STAT "
					+"AND INVSTAT.AVAILABLE= 'Y' "
					+"AND STOCK.SITE_CODE='"+siteCode+"' "
					+"AND STOCK.ITEM_CODE='" +itemCode+ "' "
					+"AND STOCK.LOC_CODE >='"+ locCodeFrom +"' "
					+"AND  STOCK.LOC_CODE<= '"+locCodeTo +"'";
				System.out.println("sql "+sql4);
				
				stmt4 = conn.createStatement();
				rs4 = stmt4.executeQuery(sql4);
				while (rs4.next())
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<tran_id>").append("<![CDATA[").append(tranId).append("]]>").append("</tran_id>\r\n");
					valueXmlString.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>\r\n");
					sql5 = "SELECT  DESCR FROM ITEM WHERE ITEM_CODE='" +itemCode+ "'";
					System.out.println("sql5 :"+sql5);
					rs5 = stmt1.executeQuery(sql5);
					if(rs5.next())
					{
						returnValue1 = rs5.getString(1);
						System.out.println("returnValue1 :"+returnValue1);
					}
					valueXmlString.append("<site_descr>").append(returnValue1).append("</site_descr>\r\n");
					valueXmlString.append("<quantity>").append("<![CDATA[").append(rs4.getDouble(2)).append("]]>").append("</quantity>\r\n");
					valueXmlString.append("<lot_no>").append("<![CDATA[").append(rs4.getString(3)).append("]]>").append("</lot_no>\r\n");
					valueXmlString.append("<lot_sl>").append("<![CDATA[").append(rs4.getString(4)).append("]]>").append("</lot_sl>\r\n");
					valueXmlString.append("<item_ser>").append("<![CDATA[").append(rs4.getString(5)).append("]]>").append("</item_ser>\r\n");
					valueXmlString.append("<loc_code>").append("<![CDATA[").append(rs4.getString(6)).append("]]>").append("</loc_code>\r\n");
					valueXmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
					sql6 = "SELECT  DESCR FROM ITEM WHERE SITE_CODE='" +siteCode+ "'";
					System.out.println("sql6 :"+sql6);
					rs6 = stmt1.executeQuery(sql6);
					if(rs6.next()  )
					{
						returnValue2 = rs6.getString(1);
					}
					valueXmlString.append("<item_descr>").append(returnValue2).append("</item_descr>\r\n");
					valueXmlString.append("<unit>").append("<![CDATA[").append(rs4.getString(8)).append("]]>").append("</unit>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
			}//for
			stmt4.close();
			valueXmlString.append("</Root>\r\n");
		}//try
		catch(SQLException e)
		{
			System.out.println("Exception : InvFreeze : actionAllRecords " +e.getMessage());
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			System.out.println("Exception : InvFreeze : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection........");
				conn.close();
				conn = null;
			}catch(SQLException se){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}
}