/********************************************************
	Title 	 : UndoRecievableAdjIC[DI3FSUN015]
	Date  	 : 03/02/15
	Developer: Priyanka

********************************************************/


package ibase.webitm.ejb.fin;

import java.rmi.RemoteException;
import java.text.*;
import java.util.ArrayList;
import java.util.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
import org.w3c.dom.*;

import javax.ejb.*;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;

@Stateless // added for ejb3
public class UndoRecievableAdjIC extends ValidatorEJB implements UndoRecievableAdjICLocal,UndoRecievableAdjICRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance(); 

	public String wfValData(String xmlString, String xmlString1,String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String  errString = null;
		System.out.println("Validation Start..........");
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1); 
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString(xmlString2);
			}
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : UndoRecievableAdjIC : wfValData(String xmlString) : ==>\n"+e.getMessage());
		}
		return (errString);
	}
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{

		int currentFormNo = 0;
		int childNodeListLength;
		int ctr = 0;
		int cnt=0,cntv=0;
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		String userId = "";
		String sql = "";
		String errorType = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ConnDriver connDriver = new ConnDriver();
		String keyFlag = "";
		int periodCnt=0;
		String custCode="",siteCode="",statusSales="",tranType="",refNo="",refSer="",tranId="";
		Timestamp tranDate=null;
		String prdCode="";
		System.out.println("Priyanka testing : Inside wfValData 1 ");
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
		System.out.println("Priyanka testing : editFlag :" + editFlag);

		try
		{
	
			conn = connDriver.getConnectDB("DriverITM");
			if (objContext != null && objContext.trim().length() > 0)
			{
				currentFormNo = Integer.parseInt(objContext);
				System.out.println("Priyanka testing : currentFormNo :"+currentFormNo);
			}
	
			switch (currentFormNo)
			{
			 case 1:
				System.out.println("Priyanka testing case 1 for validation ");
	
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("Priyanka testing :parentNode : "+parentNode);
				System.out.println("Priyanka testing :childNodeListLength : "+childNodeListLength);
				for (ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("Priyanka testing :childNodeName : "+childNodeName);
						
					
					
					if(childNodeName.equalsIgnoreCase("tran_id"))
					{
						
						    sql = "select key_flag from transetup where tran_window = 'w_undo_rec_adj'";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								keyFlag = rs.getString(1)==null ? "M" : rs.getString(1).trim();
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							tranId = genericUtility.getColumnValue("tran_id", dom);
							  
							
							if(keyFlag.equalsIgnoreCase("M"))
							{
							
								if(tranId == null || tranId.trim().length() == 0 )
								{
									
									errCode="VMTRNIDNUL ";						
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
									break;
								}
								else
								{
									//if(keyFlag.equalsIgnoreCase("M") || keyFlag.equalsIgnoreCase("A"))
									if(editFlag.equals("A"))
									{
										sql = "select count(*) from UNDO_REC_ADJ where tran_id = ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setString(1, tranId);
										rs = pstmt.executeQuery();
										if(rs.next())
										{
											cntv = rs.getInt(1);
										}
										rs.close();
										rs = null;
										pstmt.close();
										pstmt = null;
								    	
										 if(cntv > 0)
										 {
											
											    errCode = "VMTRNIDINV";
												errList.add(errCode);
												errFields.add(childNodeName.toLowerCase());
												break;
										 }
								  }
							 }
						}

					}
					
					
					
					else if(childNodeName.equalsIgnoreCase("cust_code"))
					{
						custCode = checkNull(genericUtility.getColumnValue("cust_code",dom));
						System.out.println(" Customer_code =" + custCode);
	
						if (custCode == null || custCode.trim().length() == 0)
						{
							errCode = "VMCUSTCD1 ";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						} 
						else
						{						
							if(!isExist(conn, "customer", "cust_code", custCode))
	  						 {
								    errCode = "VTCUSTCD1 ";
					    			errList.add(errCode);
					    			errFields.add(childNodeName.toLowerCase());				    			
	  						 }
						}
	
					}
					else if(childNodeName.equalsIgnoreCase("site_code"))
					{
							
						siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
						System.out.println(" siteCode_code =" + siteCode);
	
						if (siteCode == null || siteCode.trim().length() == 0)
						{
							errCode = "NULLSITECD";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						} 
						else
						{
							if(!isExist(conn, "site", "site_code", siteCode))
	  						 {
								     errCode = "VTSITECD1";
					    			errList.add(errCode);
					    			errFields.add(childNodeName.toLowerCase());				    			
	  						 }
						}
						
				    }
					else if(childNodeName.equalsIgnoreCase("tran_date"))
					{
						if (genericUtility.getColumnValue("tran_date", dom) == null)
						{
							tranDate = null;
						} 
						else
						{
							tranDate = Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("tran_date", dom), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
						}
						
						siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
						System.out.println(" siteCode_code =" + siteCode);
						if(tranDate == null)
						{
							System.out.println("tranDate is nulllllllllll");
							errCode = "VTTRNDTNUL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());						
						}
						else
						{
							
							sql = "select code from period where ?  between fr_date and to_date";
					    	
							pstmt = conn.prepareStatement(sql);
							pstmt.setTimestamp(1, tranDate);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								prdCode = rs.getString(1);

							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							

							System.out.println("prdCode=============="+prdCode);
							
							sql = "select stat_sal from period_stat where site_code = ? and prd_code = ? ";
							System.out.println("sql1 :"+sql);
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCode);
							pstmt.setString(2, prdCode);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								statusSales = rs.getString("stat_sal");

							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							System.out.println("statusSales==========="+statusSales);
							if((statusSales!=null && statusSales.trim().length()>0 && statusSales.equalsIgnoreCase("N")))
							{
								errCode = "VMSTATSNVL";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
								System.out.println("Stat sal is not valid for defined period.");
							}
														
					
					}	
				  }
					
					else if(childNodeName.equalsIgnoreCase("ref_no"))
					{
						refNo=genericUtility.getColumnValue("ref_no", dom);
						tranType=genericUtility.getColumnValue("tran_type", dom);
						System.out.println("refNo============="+refNo);
						System.out.println("tranType============="+tranType);
						if(refNo==null||refNo.trim().length()==0)
						{
							errCode = "VTREF02";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						
						}
						else
						{						
							sql="select count(1) from receivables  where tran_ser=? and ref_no= ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranType);
							pstmt.setString(2, refNo);
							
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt(1);					

							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							System.out.println("count frm ref _no==="+cnt);
							if(cnt==0)
							{
								errCode = "VTREF01";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
								
							}
						}
					}
					
								
				}//end of else  if loop
					//end of for 	
			     break;//end of switch
					//case2
				
				} 
	
				int errListSize = errList.size();
				int count = 0;
				String errFldName = null;
				if (errList != null && errListSize > 0)
				{
					for (count = 0; count < errListSize; count++)
					{
						errCode = errList.get(count);
						errFldName = errFields.get(count);
						System.out.println(" testing :errCode .:" + errCode);
						errString = getErrorString(errFldName, errCode, userId);
						errorType = errorType(conn, errCode);
						if (errString.length() > 0)
						{
							String bifurErrString = errString.substring(errString.indexOf("<Errors>") + 8, errString.indexOf("<trace>"));
							bifurErrString = bifurErrString + errString.substring(errString.indexOf("</trace>") + 8, errString.indexOf("</Errors>"));
							errStringXml.append(bifurErrString);
							errString = "";
						}
						if (errorType.equalsIgnoreCase("E"))
						{
							break;
						}
					}
					errList.clear();
					errList = null;
					errFields.clear();
					errFields = null;
					errStringXml.append("</Errors> </Root> \r\n");
				} else
				{
					errStringXml = new StringBuffer("");
				}
	
		}//end of try
		catch (Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			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;
			} 
			catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		errString = errStringXml.toString();
		System.out.println("testing : final errString : "+errString);
		return errString;

	}

	
	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 = "";
		System.out.println("hELLO PRINT");
		try
		{
			System.out.println("xmlString@@@@@@@"+xmlString);

			if(xmlString != null && xmlString.trim().length() > 0)
			{
				dom = parseString(xmlString);
			}
			System.out.println("dom@@@@@@@"+dom);

			System.out.println("xmlString1@@@@@@@"+xmlString1);

			if(xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom1 = parseString(xmlString1);
			}

			System.out.println("xmlString2@@@@@@@"+xmlString2);

			if(xmlString2 != null && xmlString2.trim().length() > 0)
			{
				dom2 = parseString(xmlString2);
			}
			System.out.println("HELLO1 PRINT");
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
			System.out.println("VALUE HELLO PRINT["+valueXmlString+"]");
		}
		catch (Exception e)
		{
			System.out.println("Exception : [UndoRecievableAdjIC][itemChanged( String, String )] :==>\n" + 
					e.getMessage());
			throw new ITMException(e);
		}
		System.out.println("VALUE HELLO PRINTA["+valueXmlString+"]");
		return valueXmlString;
  }
	
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = null;
		int currentFormNo = 0;
		StringBuffer valueXmlString = new StringBuffer();
		String columnValue = null;
		NodeList parentNodeList = null;
		Node parentNode = null; 
		Node childNode = null;
		NodeList childNodeList = null;
		String childNodeName = null;
		int childNodeListLength = 0;
		int ctr = 0;
		int cnt = 0;
		String  loginSite = null;
		String  chguser = null;
		String  chgterm = null;
	    String 	addUser="",addTerm="";
		SimpleDateFormat sdf = null;
		String custCode="",custName="";
		
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		try
		{
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
				
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			chguser = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgterm = getValueFromXTRA_PARAMS(xtraParams, "chgTerm");
		
			addUser = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			addTerm = getValueFromXTRA_PARAMS(xtraParams, "chgTerm");
			
			
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			
			System.out.println("Current Form No ["+currentFormNo+"]");							
			switch (currentFormNo)
			{
				case 1:
					valueXmlString.append("<Detail1>");	
					//SEARCHING THE DOM FOR THE INCOMING COLUMN VALUE START
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0;
					childNodeListLength = childNodeList.getLength();
			
					System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
					if (currentColumn.trim().equals( "itm_default" ))
					{
						String loginSiteCode = null;
						String siteDescr = null;
						loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
						if( loginSiteCode != null && loginSiteCode.trim().length()>0 )
						{
							sql = "select descr from site where site_code= ? ";

							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,loginSiteCode);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								siteDescr = rs.getString( "descr" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;

							valueXmlString.append("<tran_date>").append("<![CDATA[" + getCurrdateAppFormat() + "]]>").append("</tran_date>");
							valueXmlString.append("<site_code>").append("<![CDATA[" + ( loginSiteCode != null ? loginSiteCode.trim() : ""  )+ "]]>").append("</site_code>");
							valueXmlString.append("<site_descr>").append("<![CDATA[" + ( siteDescr != null ? siteDescr.trim() : ""  )+ "]]>").append("</site_descr>");	
							valueXmlString.append("<add_date>").append("<![CDATA["+ getCurrdateAppFormat() + "]]>").append("</add_date>");
							valueXmlString.append("<add_user>").append("<![CDATA["+ chguser + "]]>").append("</add_user>");
							valueXmlString.append("<add_term>").append("<![CDATA["+ chgterm + "]]>").append("</add_term>");
						}
						
					}
					if(currentColumn.trim().equalsIgnoreCase("itm_defaultedit"))
					{
						
						valueXmlString.append("<chg_date>").append("<![CDATA["+ getCurrdateAppFormat() + "]]>").append("</chg_date>");
						valueXmlString.append("<chg_user>").append("<![CDATA["+ chguser + "]]>").append("</chg_user>");
						valueXmlString.append("<chg_term>").append("<![CDATA["+ chgterm + "]]>").append("</chg_term>");
												
					}	
					//site_code
					if (currentColumn.trim().equals( "site_code" ))
					{
						String siteDescr = null;
						String sitecode =checkNull( genericUtility.getColumnValue( "site_code", dom ));
						if(sitecode!=null && sitecode.trim().length()>0)
						{
							sql = "select descr from site where site_code= ? ";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1,sitecode);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								siteDescr = rs.getString( "descr" );
							}	
							
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr+ "]]>").append("</site_descr>");
						}
						else
						{
							valueXmlString.append("<site_descr>").append("<![CDATA[" + "" + "]]>").append("</site_descr>");
						}
										
					}
					//cust_code
					if (currentColumn.trim().equals( "cust_code" ))
					{
						
						custCode = checkNull(genericUtility.getColumnValue( "cust_code", dom));
						System.out.println("Customer Code====="+custCode);
						if(custCode!=null && custCode.trim().length()>0)
						{
							sql = "select cust_name from customer where cust_code= ? ";
							
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,custCode);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								custName = rs.getString("cust_name");
							}							
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							valueXmlString.append("<cust_name>").append("<![CDATA[" + custName+ "]]>").append("</cust_name>");
						}
						else
						{
							valueXmlString.append("<cust_name>").append("<![CDATA[" + ""+ "]]>").append("</cust_name>");
						}
								  
					}
					
					valueXmlString.append("</Detail1>");
					valueXmlString.append("</Root>");	
					break;
					
			}//END OF TRY
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+ e.getMessage());
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pStmt != null )
				{
					pStmt.close();
					pStmt = null;					
				}
				if ( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("Exception ::" + e);
				e.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}//END OF ITEMCHANGE	
	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			Timestamp 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");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}

	
	private boolean isExist(Connection conn, String tableName, String columnName, String value) throws  ITMException, RemoteException
	{
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = "";
		boolean status = false;
		try
		{
			sql = "SELECT count(*) from " + tableName + " where " + columnName +"  = ?";
	    	pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,value);
	    	rs = pstmt.executeQuery();

	    	if(rs.next())
	    	{
				if(rs.getBoolean(1))
				{
					status = true;
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}
		catch(Exception e)
		{
			System.out.println("Exception in isExist ");
			e.printStackTrace();
		    throw new ITMException(e);
		}
		System.out.println("returning String from isExist ");
		return status;
	}
	
	
	
	private String errorType(Connection conn, String errorCode) throws ITMException
	{
		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();
			if (rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		} 
		catch (Exception ex)
		{
			ex.printStackTrace();
			throw new ITMException(ex);
		} 
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return msgType;
	}
	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		
	
}
