package ibase.webitm.ejb.adm;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import javax.ejb.Stateless; 
import javax.naming.InitialContext;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;


@Stateless 
public class ShiftPatternProcIC extends ValidatorEJB implements ShiftPatternProcICLocal,ShiftPatternProcICRemote 

{	
	
	public String itemChanged() throws RemoteException,ITMException
	{
		return "";
	}
	public String itemChanged(String xmlString, String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException 
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString);
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1);
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return valueXmlString.toString(); 
	}
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String columnValue = "";
		String sql = "";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String loginSite = "";
		String empFname = "";
		String empLname = "";
		String deptDescr = "";
		String siteDescr = "";
		String shiftDescr = "";
		
		StringBuffer valueXmlString = new StringBuffer();
	    ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			columnValue = genericUtility.getColumnValue(currentColumn,dom);
			System.out.println("columnValue :"+columnValue);
			valueXmlString.append("<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></Header>\r\n");
			valueXmlString.append("<Detail1>\r\n");
			
			if (currentColumn.trim().equals("itm_default"))
			{
				
				String shiftPattern = getColumnValue("shift_pattern",dom);
				String shiftTo = getColumnValue("shift_to",dom);
				valueXmlString.append("<shift_pattern protect = '1'> ").append(shiftPattern == null ? "N":shiftPattern).append("</shift_pattern>\r\n");
				valueXmlString.append("<shift_to protect = '1'> ").append(shiftTo == null ? "" : shiftTo).append("</shift_to>\r\n");
					
				loginSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSite");
				valueXmlString.append("<site_code__fr>").append(loginSite).append("</site_code__fr>\r\n");
				valueXmlString.append("<site_code__to>").append(loginSite).append("</site_code__to>\r\n");
					
				sql = "SELECT DESCR  FROM SITE WHERE SITE_CODE = '" + loginSite + "'";
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					siteDescr = rs.getString("DESCR");
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<site_code_fr_descr>").append(siteDescr).append("</site_code_fr_descr>\r\n");
				valueXmlString.append("<site_code_to_descr>").append(siteDescr).append("</site_code_to_descr>\r\n");
				valueXmlString.append("<dept_code__fr>").append("00").append("</dept_code__fr>\r\n");
				valueXmlString.append("<dept_code__to>").append("ZZ").append("</dept_code__to>\r\n");
				valueXmlString.append("<emp_code__fr>").append("00").append("</emp_code__fr>\r\n");
				valueXmlString.append("<emp_code__to>").append("ZZ").append("</emp_code__to>");
			}
			else if (currentColumn.trim().equals("emp_code__fr"))
			{
				String empCode = getColumnValue("emp_code__fr", dom);
				sql = "SELECT EMP_FNAME, EMP_LNAME FROM EMPLOYEE WHERE EMP_CODE = '"+empCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					empFname = rs.getString("EMP_FNAME");					
					empLname = rs.getString("EMP_LNAME");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<emp_code__fr>").append(empCode == null ? "" : empCode).append("</emp_code__fr>\r\n");		
				valueXmlString.append("<fr_emp_name>").append(empFname == null ? "" : empFname).append(" ").append(empLname == null ? "" : empLname).append("</fr_emp_name>\r\n");		
			}
			else if (currentColumn.trim().equals("emp_code__to"))
			{
				String empCode = getColumnValue("emp_code__to", dom);
				sql = "SELECT EMP_FNAME, EMP_LNAME FROM EMPLOYEE WHERE EMP_CODE = '"+empCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					empFname = rs.getString("EMP_FNAME");					
					empLname = rs.getString("EMP_LNAME");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<emp_code__to>").append(empCode == null ? "" : empCode).append("</emp_code__to>\r\n");		
				valueXmlString.append("<to_emp_name>").append(empFname == null ? "" : empFname).append(" ").append(empLname == null ? "" : empLname).append("</to_emp_name>\r\n");		
			}
			else if (currentColumn.trim().equals("dept_code__fr"))
			{
				String deptCode = getColumnValue("dept_code__fr", dom);
				sql = "select descr  from department where dept_code = '"+deptCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					deptDescr = rs.getString("descr");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<dept_code__fr>").append(deptCode == null ? "" : deptCode).append("</dept_code__fr>\r\n");
				valueXmlString.append("<dept_code_fr_descr>").append(deptDescr == null ? "" : deptDescr).append("</dept_code_fr_descr>\r\n");
			}
			else if (currentColumn.trim().equals("dept_code__to"))
			{
				String deptCode = getColumnValue("dept_code__to", dom);
				sql = "select descr  from department where dept_code = '"+deptCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					deptDescr = rs.getString("descr");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<dept_code__to>").append(deptCode == null ? "" : deptCode).append("</dept_code__to>\r\n");
				valueXmlString.append("<dept_code_to_descr>").append(deptDescr == null ? "" : deptDescr).append("</dept_code_to_descr>\r\n");
			}
			else if (currentColumn.trim().equals("site_code__fr"))
			{
				String siteCode = getColumnValue("site_code__fr", dom);
				sql = "select descr from site where site_code = '"+siteCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					siteDescr = rs.getString("descr");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<site_code__fr>").append(siteCode == null ? "" : siteCode).append("</site_code__fr>\r\n");				
				valueXmlString.append("<site_code_fr_descr>").append(siteDescr == null ? "" : siteDescr).append("</site_code_fr_descr>\r\n");				
				
			}
			else if (currentColumn.trim().equals("site_code__to"))
			{
				
				String siteCode = getColumnValue("site_code__to", dom);
				sql = "select descr from site where site_code = '"+siteCode+"' ";		
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					siteDescr = rs.getString("descr");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<site_code__to>").append(siteCode == null ? "" : siteCode).append("</site_code__to>\r\n");				
				valueXmlString.append("<site_code_to_descr>").append(siteDescr == null ? "" : siteDescr).append("</site_code_to_descr>\r\n");				
				
			}
			else if(currentColumn.trim().equals("shift_to"))
			{
				String shift = getColumnValue("shift_to", dom);			
				sql = "select a.descr from workshft a where a.shift = '" + shift + "' and a.day_no = (select min(b.day_no) from workshft b where b.shift = a.shift)";
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next())
				{
					 shiftDescr = rs.getString("descr");					
				}
				rs.close();
				stmt.close();
				valueXmlString.append("<shift_to>").append(shift == null ? "" : shift).append("</shift_to>\r\n");	
				valueXmlString.append("<shift_to_descr>").append(shiftDescr == null ? "" : shiftDescr).append("</shift_to_descr>\r\n");	
			}			
			valueXmlString.append("</Detail1>\r\n");
			valueXmlString.append("</Root>\r\n");			
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.......");
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception se)
			{
				throw new ITMException(se);
			}
		}
		
		return valueXmlString.toString(); 

	}
}



