/********************************************************
	Title : UserEJB
	Development Date: 03/3/08 
	Name of Developer : Nisar S. Khatib		 
********************************************************/
package ibase.webitm.ejb.sys; 

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.sys.*;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;
import java.sql.*;
import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
@Stateless  
public class User extends ValidatorEJB implements UserLocal,UserRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance(); 

	java.sql.PreparedStatement pstmt=null;
	java.sql.ResultSet rs = null;

	String sql = null;
	String sundryType = null;
	String  errString = "";
	String loginSite = "";

	
	/*public void ejbCreate() throws RemoteException, CreateException 
	{
		System.out.println("-------------UserEJB is in Process..........");
	}
	public void ejbRemove()
	{
		System.out.println("-------------UserEJB is Removed..................");
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	} */
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}

	public String itemChanged() throws RemoteException,ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		System.out.println("Validation Start..........");

		try
		{
			System.out.println("xmlString:::"+xmlString);
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);			
			errString = wfValData(dom,dom1,objContext,editFlag,xtraParams);			
		}
		catch(Exception e)
		{
			System.out.println("Exception : UserEJB : wfValData(String xmlString) : ==>\n"+e.getMessage());
		}
		return (errString);
	}
	public String wfValData(Document dom, Document dom1,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		
		String columnValue = null;
		String childNodeName = null;
		String errString = "";
		String errCode = "";		
		String sql = "";
		String userId = ""; 		
		String custCode=null;
		String itemType=null,sampleDate=null,designDate=null;	

		String passFeqS = null,name = null;
		String lsValidUpto = null, lsAccessType = null;
		String code = null, entityCode = "", userType = "";
		int passFeq=0,cnt = 0;

		
		int ctr=0;
		int currentFormNo=0;
		int childNodeListLength;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>"); 
		
		try
		{
			ConnDriver connDriver = new ConnDriver();	
			conn = connDriver.getConnectDB("DriverITM");
			System.out.println("[UserEJB]connection 1 is opened......");
			
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		
			GenericUtility genericUtility = GenericUtility.getInstance(); 
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
					
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						
						if ("A".equalsIgnoreCase(editFlag) && childNodeName.equalsIgnoreCase("code"))
						{
							code = getColumnValue("code",dom);
							code = code==null?"":code.trim();
							if(code.length() == 0)
							{
								errCode = "VECODE2";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							else
							{
								sql="SELECT COUNT(1) AS CNT FROM USERS WHERE CODE = '"+code+"'";
								System.out.println("[UserEJB] SQL=>"+sql);
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt("CNT");
								}
								rs.close();
								pstmt.close();

								if(cnt > 0)
								{
									errCode = "VECDDLP";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}	
						else if (childNodeName.equalsIgnoreCase("name"))
						{
							name = getColumnValue("name",dom);
							name = name==null?"":name.trim();
							if(name.length() == 0)
							{
								errCode = "VEUSREMPT";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}	  
						}
						else if (childNodeName.equalsIgnoreCase("passwd_freq"))
						{
							passFeqS = getColumnValue("passwd_freq",dom);
							passFeq = Integer.parseInt(passFeqS==null?"0":passFeqS.trim());
							
							if(passFeq > 60 || passFeq==0)
							{
								errCode = "VTPASS7";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}	  
						}
						else if (childNodeName.equalsIgnoreCase("valid_upto"))
						{
							lsValidUpto = getColumnValue("valid_upto",dom);
							lsAccessType = getColumnValue("access_type",dom);
							lsAccessType = lsAccessType==null?"":lsAccessType.trim();
							lsValidUpto = lsValidUpto==null?"":lsValidUpto.trim();
																
							if(lsAccessType.equalsIgnoreCase("T"))
							{
								if(lsValidUpto.length() == 0)
								{
									errCode = "VTDATEUPTO";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}//end of if(lsValidUpto.length() == 0)
								else
								{			   
									SimpleDateFormat sdf=new SimpleDateFormat(genericUtility.getApplDateFormat());
									SimpleDateFormat sdf1=new SimpleDateFormat(genericUtility.getDBDateFormat());
									Object dateFrom = sdf.parse(lsValidUpto);
						
									Timestamp s1 = Timestamp.valueOf(sdf1.format(dateFrom)+" 00:00:00.0");
									Timestamp s2 = new Timestamp(System.currentTimeMillis());
									
									if(s1.before(s2))
									{	
										errCode = "VVDTERR";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}//end of else 
							}//end of if(lsAccessType.equalsIgnoreCase("T"))
						}//end of else of  if (childNodeName.equalsIgnoreCase("valid_upto"))					
						else if (childNodeName.equalsIgnoreCase("entity_code"))
						{
							//Case Added by Rutuja on 20/05/13
							entityCode = getColumnValue("entity_code",dom);
							userType = getColumnValue("user_type",dom);
							entityCode= checkNull(entityCode);
							userType= checkNull(userType);
							
							if (entityCode != null && entityCode.trim().length() > 0 )
							{
								if ("E".equals(userType))
								{
									sql="SELECT COUNT(1) AS CNT FROM employee WHERE emp_code = ?";
								}
								else if ("C".equals(userType))
								{
									sql="SELECT COUNT(1) AS CNT FROM customer WHERE cust_code = ?";
								}
								else if ("S".equals(userType))
								{
									sql="SELECT COUNT(1) AS CNT FROM supplier WHERE supp_code = ?";
								}
								else if ("P".equals(userType))
								{
									sql="SELECT COUNT(1) AS CNT FROM sales_pers WHERE sales_pers = ?";
								}
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,entityCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;

								if (cnt == 0 )
								{
									errCode = "VTENTITYCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
							else if (userType != null && userType.trim().length() > 0 ) // 27/05/13 manoharan if user_type is specified the entiry code also should be specified
							{
								errCode = "VTENTITYCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						
					}
					break;//END FOR	
			}//END SWITCH
			int errListSize = errList.size();
			int count = 0;
			String errorType = "";
			String errFldName = null;
			ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
			if(errList != null && errListSize > 0)
			{
				for(count = 0; count < errListSize; count++)
				{
					errCode = errList.get(count);
					errFldName = errFields.get(count);
					System.out.println("errCode .........." + errCode);
				//	errString = getErrorString(errFldName, errCode, userId);
					errString = itmDBAccessEJB.getErrorString(errFldName, errCode, userId);
					System.out.println("errString........"+errString);
					
					errString = itmDBAccessEJB.getErrorString("", errCode, userId);
					System.out.println("errString........"+errString);
					
					errorType = errorType(conn , errCode);
					return errString;
					
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				errStringXml.append("</Errors> </Root> \r\n");
			}
			else
			{
				errStringXml = new StringBuffer("");
			}
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
			errString=e.getMessage();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
					System.out.println("[UserEJB]connection 1 is closed......");
				}
			}catch(Exception d){d.printStackTrace();}			
			System.out.println("[UserEJB] Connection is Closed");
		}
		errString = errStringXml.toString();
		System.out.println("ErrString ::"+errString);
		return errString;
	}//END OF VALIDATION 

	
	public String itemChanged(String xmlString, String xmlString1,String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";

		try
		{
			dom = parseString(xmlString); 
			System.out.println("xmlString" + xmlString);
			System.out.println("[UserEJB]itemChanged Called...........");
			dom1 = parseString(xmlString1);
			valueXmlString = itemChanged(dom,dom1,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [UserEJB][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
        return valueXmlString; 
	}

	public String itemChanged(Document dom, Document dom1,String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn=null;
		Statement stmt=null;

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;

		StringBuffer valueXmlString = new StringBuffer();
		String custCode=null;
		String custName=null;
		String itemCode=null;
		String itemDesc=null;
		String salesPerson=null;
		String salesPersonName=null;		
		String columnValue = null;
		String childNodeName = null;
		String siteCodeDescr=null;
		String invoiceNo=null;	
		String tranDate=null;
		String spName=null;
		String itemType=null;		
		String currCodeTo="";	
		String empName=null;
		String empDetails=null;
		String catCodeDescr=null;
		String marketCodeDescr=null;
		String projectCodeDescr=null;
		String productType=null;
		String productTypeDescr=null;

		boolean isFound=false;
		double invAmount=0.0;
		double exchRate=0;
		int ctr=0,graceLogin = 0;
		int currentFormNo=0;	
		String entityCode = "", userType = "", sql ="", emailId = "";

		try
		{
			ConnDriver connDriver = new ConnDriver();	
			conn = connDriver.getConnectDB("DriverITM");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println("FORM NO:::"+currentFormNo);	
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			valueXmlString.append("<Detail>");	

			switch(currentFormNo)
			{
				case 1 :
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0;
					int childNodeListLength = childNodeList.getLength();
					do
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if(childNodeName.equals(currentColumn))
						{
							if (childNode.getFirstChild() != null)
							{
								columnValue=childNode.getFirstChild().getNodeValue().trim();
							}
						}
						ctr++;
					}while(ctr < childNodeListLength  && !childNodeName.equals(currentColumn));
					System.out.println("[UserEJB]Column Value=>"+columnValue);
					System.out.println("[UserEJB]currentColumn=>"+currentColumn);

					if (currentColumn.trim().equals("itm_default"))
					{			
						sql = "select grace_login from parameter";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							graceLogin = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append("<grace_login_cnt>").append("<![CDATA[" + graceLogin + "]]>").append("</grace_login_cnt>");
					}
					else if (currentColumn.trim().equals("emp_code"))
					{
						empDetails=getEmpDetails(columnValue,"emp_code",conn);
						valueXmlString.append(empDetails);
					}
					else if (currentColumn.trim().equals("entity_code"))
					{
						//Case Added by Rutuja on 13/04/12
						entityCode =genericUtility.getColumnValue("entity_code", dom);
						userType =genericUtility.getColumnValue("user_type", dom);
						if (entityCode != null &&  entityCode.trim().length()  > 0 )
						{
							
							if ("E".equals(userType))
							{
								sql = "select email_id from employee where emp_code = ?";
							}
							else if ("C".equals(userType))
							{
								sql = "select email_addr from customer where cust_code = ?";
							}
							else if ("S".equals(userType))
							{
								sql = "select email_addr from supplier where supp_code = ?";
							}
							else if ("P".equals(userType))
							{
								sql = "select email_addr from sales_pers where sales_pers = ?";
							}
							if (sql.trim().length() > 0 )
							{
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,entityCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									emailId = rs.getString(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
						  
								valueXmlString.append("<email_id>").append("<![CDATA[" + checkNull(emailId) + "]]>").append("</email_id>");
							}
						}
					
					}
					valueXmlString.append("</Detail>");
				break;
			}//end of switch
			valueXmlString.append("</Root>");
		}//END OF TRY
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();					
					conn = null;
					System.out.println("[UserEJB]connection 2 is closed......");
				}
			}catch(Exception d){d.printStackTrace();}			
			System.out.println("[UserEJB] Connection is Closed");
		}
		System.out.println("valueXmlString:::::"+valueXmlString.toString());
		return valueXmlString.toString();
	}//END OF ITEMCHNGE		

	private String getEmpDetails(String value,String fieldname,Connection conn)
	{
		String fname = ""; 
		String lname = "";
		String emailIdOff="";
		String emailIdPer="";
		String sql=null;
		StringBuffer strBuf = new StringBuffer(); 
		sql ="SELECT CASE WHEN EMP_FNAME IS NULL THEN '' ELSE EMP_FNAME END AS EMP_FNAME , "
		+" CASE WHEN EMP_LNAME IS NULL THEN '' ELSE EMP_LNAME END AS EMP_LNAME, "
		+" CASE WHEN EMAIL_ID_OFF IS NULL THEN '' ELSE EMAIL_ID_OFF END AS EMAIL_ID_OFF ,"
		+" CASE WHEN EMAIL_ID_PER IS NULL THEN '' ELSE EMAIL_ID_PER END AS EMAIL_ID_PER "
		+" FROM EMPLOYEE "
		+" WHERE EMP_CODE = '"+value+"'";

		try
		{
			System.out.println("[UserEJB] sql---->"+sql);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				fname = rs.getString("EMP_FNAME")==null?"":rs.getString("EMP_FNAME");
				lname = rs.getString("EMP_LNAME")==null?"":rs.getString("EMP_LNAME");
				emailIdOff = rs.getString("EMAIL_ID_OFF")==null?"":rs.getString("EMAIL_ID_OFF");
				emailIdPer = rs.getString("EMAIL_ID_PER")==null?"":rs.getString("EMAIL_ID_PER");
			}
			rs.close();
			pstmt.close();

			strBuf.append("<fname><![CDATA["+fname+"]]></fname>");
			strBuf.append("<lname><![CDATA["+lname+"]]></lname>");
			strBuf.append("<email_id_off><![CDATA["+emailIdOff+"]]></email_id_off>");
			strBuf.append("<email_id_per><![CDATA["+emailIdPer+"]]></email_id_per>");
			strBuf.append("<email_id><![CDATA["+emailIdOff+"]]></email_id>");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());	
			errString=e.getMessage();
		}
		System.out.println("[UserEJB] strBuf =>"+strBuf);
		return strBuf.toString();
	}
	private String errorType(Connection conn , String errorCode)
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = "SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO = ?";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setString(1,errorCode);			
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	private String checkNull(String input)
	{
		if (input == null)
		{
			input = "";
		}
		return input;
	}
	
}//ended by nisar