package ibase.dashboard.wms.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;
import javax.ejb.Stateless;

/**
 * Session Bean implementation class EORLocationStock
 */

@Stateless
public class EORLocationStock extends ValidatorEJB implements EORLocationStockRemote, EORLocationStockLocal
{
	/**
	 * Default constructor.
	 */
	public EORLocationStock()
	{
	}

	public String getEORLocPhyArea() throws RemoteException, ITMException
	{
		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			sql = "select distinct loc_phy_area  from  location order by loc_phy_area ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			xmlData = new StringBuffer("<?xml version='1.0'?> <Root>");
			while (rs.next())
			{
				xmlData.append("<Detail>");
				xmlData.append("<loc_phy_area>").append("<![CDATA[" + this.checkNull(rs.getString("loc_phy_area")) + "]]>").append("</loc_phy_area>");
				xmlData.append("</Detail>");
			}
			xmlData.append("</Root>");
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

		} catch (Exception e)
		{
			System.out.println("Exception :ibase.dashboard.scm.bean:EORLocationStock:getEoRLocPhyArea " + e);
			e.printStackTrace();
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
						rs.close();
					rs = null;
					if (pstmt != null)
						pstmt.close();
					pstmt = null;
					conn.close();
					conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		System.out.println("xmlData.toString() =" + xmlData.toString());
		return xmlData.toString();
	}

	public String getSelectedArea() throws RemoteException, ITMException
	{

		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		String selectedArea = "";
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			sql = " select distinct loc_phy_area  from  location  order by loc_phy_area ";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				selectedArea = this.checkNull(rs.getString("loc_phy_area"));
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		} catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :ibase.dashboard.scm.bean:EORLocationStock:getSelectedArea() " + e);
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
						rs.close();
					rs = null;
					if (pstmt != null)
						pstmt.close();
					pstmt = null;
					conn.close();
					conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		return selectedArea;
	}

	public String getEORLocDtl(String locPhyArea, String siteCode, String locationRange, String expDate, String reTestDate) throws RemoteException, ITMException
	{
		String sql = "";
		ResultSet rs = null, rs1 = null;
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt1 = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;
		String previousRow = "";
		String previousStack = "";
		String currentRow = "";
		String cuurentStack = "";
		String locationCode = " ";
		StringBuffer sqlStr = null;
		int count = 0;
		String locCode = "";
		String sysDate = " current_timestamp";
		final String yseStrng = "Yes";
		try
		{
			//sysDate = "sysdate";
			sqlStr = new StringBuffer();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			if (locationRange != null && locationRange.trim().length() > 0)
			{
				locCode = "AND loc_code like '" + locationRange + "%'";
			}
			if (expDate != null && expDate.trim().length() != 0 && expDate.equalsIgnoreCase(yseStrng))
			{
				sqlStr.append("  and  s.exp_date < = " + sysDate);
			}
			if (reTestDate != null && reTestDate.trim().length() != 0 && reTestDate.equalsIgnoreCase(yseStrng))
			{
				sqlStr.append(" and  s.retest_date  < =" + sysDate);
			}
			sql = "select  loc_code,loc_phy_row,loc_phy_col,loc_phy_stack  from location  where loc_phy_area = ? and site_code = ? " + locCode + "  order by loc_phy_row, loc_phy_stack ,loc_phy_col";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, locPhyArea);
			pstmt.setString(2, siteCode);
			rs = pstmt.executeQuery();

			xmlData = new StringBuffer("<?xml version='1.0'?> <Root>");
			xmlData.append("<loc_phy_area  area =\"" + locPhyArea + "\">");

			while (rs.next())
			{
				count++;
				locationCode = checkNull(rs.getString("loc_code"));

				currentRow = checkNull(rs.getString("loc_phy_row"));
				cuurentStack = checkNull(rs.getString("loc_phy_stack"));

				if (count > 1 && !previousStack.equals(cuurentStack))
				{
					xmlData.append("</loc_phy_stack > ");
				}
				if (count > 1 && !currentRow.equals(previousRow))
				{
					xmlData.append("</loc_phy_row > ");
				}

				if (count == 1 || !currentRow.equals(previousRow))
				{
					previousRow = checkNull(rs.getString("loc_phy_row"));
					xmlData.append("<loc_phy_row row =\"" + currentRow + "\">");
				}

				if (count == 1 || !previousStack.equals(cuurentStack))
				{
					previousStack = checkNull(rs.getString("loc_phy_stack"));
					xmlData.append("<loc_phy_stack  stack=\"" + cuurentStack + "\">");
				}
				xmlData.append("<loc_phy_col col=\"" + checkNull(rs.getString("loc_phy_col")) + "\">");

				sql = "select  s.item_code, s.site_code, s.loc_code, s.lot_no, s.lot_sl, s.exp_date, s.quantity, s.alloc_qty, s.hold_qty , s.mfg_date, s.exp_date, s.retest_date , i.descr from stock s  ,item i  where s.quantity >0 and s.exp_date <= sysdate  and s.item_code=i.item_code  and s.loc_code =? and  s.site_code =? " + sqlStr.toString();
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, locationCode);
				pstmt1.setString(2, siteCode);
				rs1 = pstmt1.executeQuery();

				xmlData.append("<location_code  lcode=\"" + locationCode + "\">");

				while (rs1.next())
				{
					xmlData.append("<stock>");
					xmlData.append("<item_code>").append("<![CDATA[" + checkNull(rs1.getString("item_code")) + "]]>").append("</item_code>");

					xmlData.append("<descr>").append("<![CDATA[" + checkNull(rs1.getString("descr")) + "]]>").append("</descr>");

					xmlData.append("<lot_no>").append("<![CDATA[" + checkNull(rs1.getString("lot_no")) + "]]>").append("</lot_no>");
					xmlData.append("<lot_sl>").append("<![CDATA[" + checkNull(rs1.getString("lot_sl")) + "]]>").append("</lot_sl>");
					xmlData.append("<quantity>").append("<![CDATA[" + rs1.getDouble("quantity") + "]]>").append("</quantity>");

					xmlData.append("<hold_qty>").append("<![CDATA[" + rs1.getDouble("hold_qty") + "]]>").append("</hold_qty>");
					xmlData.append("<alloc_qty>").append("<![CDATA[" + rs1.getDouble("alloc_qty") + "]]>").append("</alloc_qty>");

					if (rs1.getDate("mfg_date") != null)
					{
						xmlData.append("<mfg_date>").append("<![CDATA[" + rs1.getDate("mfg_date") + "]]>").append("</mfg_date>");
					} else
					{
						xmlData.append("<mfg_date>").append("<![CDATA[ ]]>").append("</mfg_date>");
					}

					if (rs1.getDate("exp_date") != null)
					{
						xmlData.append("<exp_date>").append("<![CDATA[" + rs1.getDate("exp_date") + "]]>").append("</exp_date>");
					} else
					{
						xmlData.append("<exp_date>").append("<![CDATA[ ]>").append("</exp_date>");
					}

					if (rs1.getDate("retest_date") != null)
					{
						xmlData.append("<retest_date>").append("<![CDATA[" + rs1.getDate("retest_date") + "]]>").append("</retest_date>");
					} else
					{
						xmlData.append("<retest_date>").append("<![CDATA[ ]]>").append("</retest_date>");
					}
					xmlData.append("</stock>");
				}
				xmlData.append("</location_code>");
				xmlData.append("</loc_phy_col>");
				pstmt1.close();
				rs1.close();
				pstmt1 = null;
				rs1 = null;
			}

			if (count > 0)
			{
				xmlData.append("</loc_phy_stack > ");
				xmlData.append("</loc_phy_row > ");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			xmlData.append("</loc_phy_area>");
			xmlData.append("</Root>");

		} catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :ibase.dashboard.scm.bean:EORLocationStock:getEORLocDtl() " + e);
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
						rs.close();
					rs = null;
					if (pstmt != null)
						pstmt.close();
					pstmt = null;

					if (rs1 != null)
						rs1.close();
					rs1 = null;

					if (pstmt1 != null)
						pstmt1.close();
					pstmt1 = null;

					conn.close();
					conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		System.out.println("Final XML===" + xmlData.toString());
		return xmlData.toString();
	}

	private String checkNull(String input)
	{
		if (input == null)
		{
			input = "";
		} else
		{
			input = input.trim();
		}
		return input;
	}

}
