///*  added and modify by chandrakant patil on 09/10/2013  */


package ibase.webitm.ejb.sys;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.rmi.RemoteException;
import java.util.*;

import org.w3c.dom.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.ejb.*;

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



@Stateless // added for ejb3
public class SiteRegNoPrs extends ValidatorEJB implements SiteRegNoPrsLocal, SiteRegNoPrsRemote
{
	/*
	public void ejbCreate() throws RemoteException, CreateException
	{
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate()
	{
	}
	public void ejbPassivate()
	{
	}*/
	@Override
	public String preSaveRec() throws RemoteException, ITMException
	{
		return "";
	}
	@Override
	public String preSaveRec(String xmlString1, String domId, String objContext, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		System.out.println("@@@@@@@@********* SiteRegNoPrs Called *******@@@@@@@@");
		Document dom = null;
		String errString = "";
		try
		{
			System.out.println("SiteRegNoPrs xmlString1 [" + xmlString1 + "]");
			System.out.println("SiteRegNoPrs domId [" + domId + "]");
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				errString = executepreSaveRec(dom,domId,objContext,editFlag,xtraParams,conn);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :SiteRegNoPrs :preSaveRec(): " + e.getMessage()+ ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}
	private String executepreSaveRec(Document dom, String domID, String ObjContext, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		String siteCode = "";
		String tranId = "";
		String sql="",errString="";
		java.util.Date tranDate = null;
		//DistStkUpdLocal distStkUpd = null;
		NodeList hdrDom = null;
		Node currDetail = null;
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String effStr="",custCode="",effStr2="",validstr="",retString="";
		Timestamp effFrom=null,upeffFrom=null;
		Timestamp effFrom2=null,validUpto=null;
		Timestamp effFromNext = null, validUptoNext = null, effFromNew2=null;
		int cnt=0,ctr=0,cnt1=0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int childNodeListLength,cnt2=0;
		String childNodeName = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		String finEntity="",refCode="";
		try
		{
			tranDate = new java.sql.Date(System.currentTimeMillis());
			System.out.println("\n tranDate :"+tranDate);
			hdrDom = dom.getElementsByTagName("Detail1");
			GenericUtility genericUtility = GenericUtility.getInstance();
			currDetail 	= getCurrentDetailFromDom(dom,domID);
			{
				if (conn == null)
				{
					System.out.println("<-----------Connection is null----------------------->");
				}
			
				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(childNodeName.equalsIgnoreCase("fin_entity") && ("A".equalsIgnoreCase(editFlag)))	
					{
					effStr = GenericUtility.getInstance().getColumnValue("eff_from",dom);
					validstr = GenericUtility.getInstance().getColumnValue("valid_upto",dom);
					finEntity = GenericUtility.getInstance().getColumnValue("fin_entity",dom);
					siteCode = GenericUtility.getInstance().getColumnValue("site_code",dom);
					refCode= GenericUtility.getInstance().getColumnValue("ref_code",dom);
					effFrom= Timestamp.valueOf(genericUtility.getValidDateString(effStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
					validUpto= Timestamp.valueOf(genericUtility.getValidDateString(validstr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

					System.out.println("finEntity:::[" + finEntity+"]");
					System.out.println("siteCode:::[" + siteCode+"]");
					System.out.println("refCode:::[" + refCode+"]");
					System.out.println("effFromStr:::[" + effFrom+"]");
					System.out.println("validUptoStr:::[" + validUpto+"]");
					
					System.out.println("@@@@@@@ validate querry executed ::::");
					
					sql="select count(1) from siteregno "+
							" where fin_entity = ? " +
							" and site_code = ? " +
							" and ref_code = ? "+ 
							" and ( ( eff_from <= ? and valid_upto > ? ) or (  eff_from >= ? and valid_upto < ? )" +
							" or (  eff_from >= ? and eff_from <= ? and valid_upto > ? ) " +
							" or (  eff_from >= ? and valid_upto >= ? and valid_upto = ? ) ) "; 
					 
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,finEntity);
					pstmt.setString(2,siteCode);
					pstmt.setString(3,refCode);
					pstmt.setTimestamp(4,effFrom);
					pstmt.setTimestamp(5,validUpto);
					pstmt.setTimestamp(6,effFrom);
					pstmt.setTimestamp(7,validUpto);
					pstmt.setTimestamp(8,effFrom);
					pstmt.setTimestamp(9,validUpto);
					pstmt.setTimestamp(10,validUpto);
					pstmt.setTimestamp(11,effFrom);
					pstmt.setTimestamp(12,effFrom);
					pstmt.setTimestamp(13,validUpto);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						cnt2 =  rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					System.out.println("@@@@@ cnt2:["+cnt2+"]");
					if(cnt2==0)
					{
					
					sql = " select count(1) from  siteregno "+
						  " where fin_entity = ? " +
						  " and site_code = ? " +
						  " and ref_code = ? "+ 
						  " and (eff_from <= ? and valid_upto >= ? ) "; 
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,finEntity);
					pstmt.setString(2,siteCode);
					pstmt.setString(3,refCode);
					pstmt.setTimestamp(4,validUpto);
					pstmt.setTimestamp(5,effFrom);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						cnt =  rs.getInt(1);
					}
					 rs.close();
					 rs = null;
					 pstmt.close();
					 pstmt = null;
					
					 if(cnt == 0)
					{
						System.out.println("No record found against ");
					}
					else
					{
						System.out.println("record found against ");
						
						sql = " select eff_from from siteregno " +
							  " where fin_entity = ? " +
							  " and site_code = ? " +
							  " and ref_code = ? "+ 
							  " and ( eff_from <= ? and valid_upto >= ? and valid_upto <= ? ) ";   // order by gp.eff_from desc ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,finEntity);
						pstmt.setString(2,siteCode);
						pstmt.setString(3,refCode);
						pstmt.setTimestamp(4,effFrom);
						pstmt.setTimestamp(5,effFrom);
						pstmt.setTimestamp(6,validUpto);
						
						rs = pstmt.executeQuery();
						if(rs.next())
						{   cnt1++;
							upeffFrom =  rs.getTimestamp(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						 
						System.out.println("@@@@@cnt1:["+cnt1+"]finEntity:["+finEntity+"]::up_effFrom["+upeffFrom+"]");
						System.out.println("@@@@@finEntity::["+finEntity+"]::siteCode["+siteCode+"]::refCode["+refCode+"]");
				        if(cnt1 != 0)
				    	 {
				       
						 sql=" UPDATE siteregno SET valid_upto= ? " +
							 " where fin_entity = ? " +
							 " and site_code = ? " +
							 " and ref_code = ? "+ 
							 " and eff_from = ? ";  
						 pstmt = conn.prepareStatement(sql);
						 effFrom= Timestamp.valueOf(genericUtility.getValidDateString(effStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
						 SimpleDateFormat sdf = new SimpleDateFormat(getApplDateFormat());
						 Calendar c = Calendar.getInstance();
						 c.setTime(sdf.parse(effStr));
						 c.add(Calendar.DATE, -1);
						 effStr2 = sdf.format(c.getTime());
						 effFrom2= Timestamp.valueOf(genericUtility.getValidDateString(effStr2, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
						 System.out.println("@@@@@@@@@@@@ new date into validUpto2: ["+effFrom2+"]");
						
							pstmt.setTimestamp(1,effFrom2);
							pstmt.setString(2,finEntity);
							pstmt.setString(3,siteCode);
							pstmt.setString(4,refCode);
							pstmt.setTimestamp(5,upeffFrom);
							cnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;		

							System.out.println("@@@@@@@@@@ update querry executed ....["+ cnt +"]");
							ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
                            retString = itmDBAccessLocal.getErrorString("","PRVDATUPDT","");
					         }
				         	else
				         	{
				         		System.out.println("@@@@@@@@@ invalid details @@@@@@@@@@@@");
				         		errString = itmDBAccessEJB.getErrorString("","INVDATA5","");
                            }    	
						}
			/*
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
				*/
					} 
					else
					{
						System.out.println("@@@@@@@ INVALID DATA : DATA FOUND ");
						errString = itmDBAccessEJB.getErrorString("","INVDATA5","");
					}		
				}
					 
					 else if(childNodeName.equalsIgnoreCase("fin_entity") && ("E".equalsIgnoreCase(editFlag)))	
						{
						 System.out.println("@@@@ in edit mode...........");
						
						 effStr = GenericUtility.getInstance().getColumnValue("eff_from",dom);
 						validstr = GenericUtility.getInstance().getColumnValue("valid_upto",dom);
						finEntity = GenericUtility.getInstance().getColumnValue("fin_entity",dom);
						siteCode = GenericUtility.getInstance().getColumnValue("site_code",dom);
						refCode= GenericUtility.getInstance().getColumnValue("ref_code",dom);
						effFrom= Timestamp.valueOf(genericUtility.getValidDateString(effStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
						validUpto= Timestamp.valueOf(genericUtility.getValidDateString(validstr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

						System.out.println("finEntity:::[" + finEntity+"]");
						System.out.println("siteCode:::[" + siteCode+"]");
						System.out.println("refCode:::[" + refCode+"]");
						System.out.println("effFromStr:::[" + effFrom+"]");
						System.out.println("validUptoStr:::[" + validUpto+"]");
						

						sql=" select count(1) from siteregno " +
							" where fin_entity = ? " +
							" and site_code = ? " +
							" and ref_code = ? "+ 
							" and eff_from > ? and  valid_upto <= ? "; 
			 				pstmt = conn.prepareStatement(sql);
			 				pstmt.setString(1,finEntity);
							pstmt.setString(2,siteCode);
							pstmt.setString(3,refCode);
							pstmt.setTimestamp(4,effFrom);
			 				pstmt.setTimestamp(5,validUpto);
			 				rs = pstmt.executeQuery();
			 				if(rs.next())
			 				{
			 						cnt = rs.getInt(1);
			 				}
			 				rs.close();
			 				rs = null;
			 				pstmt.close();
			 				pstmt=null;
			 				
			 				if( cnt > 0)
			 				{
			 					errString = itmDBAccessEJB.getErrorString("","VMSTRGEXIS","");
								return errString;
			 				}
			 		
			 				System.out.println("@@@@@@@ for next validity modify");
				        	 sql=" select eff_from, valid_upto from siteregno " +
				        		 " where fin_entity = ? " +
								 " and site_code = ? " +
								 " and ref_code = ? "+ 
								 "and eff_from > ?  and ( eff_from <= ? and valid_upto >= ? )"; 
				 					
				 				pstmt = conn.prepareStatement(sql);
				 				pstmt.setString(1,finEntity);
								pstmt.setString(2,siteCode);
								pstmt.setString(3,refCode);
								pstmt.setTimestamp(4,effFrom);
				 				pstmt.setTimestamp(5,validUpto);
				 				pstmt.setTimestamp(6,validUpto);
				 					
				 				rs = pstmt.executeQuery();
				 				if(rs.next())
				 				{
				 						effFromNext = rs.getTimestamp(1);
				 						validUptoNext = rs.getTimestamp(2);
				 				}
				 				
				 				rs.close();
				 				rs = null;
				 				pstmt.close();
				 				pstmt=null;
				 			
				 			System.out.println("@@@@@ effFromNext["+effFromNext+"]:::::validUptoNext["+validUptoNext+"]");	
				 			
				 			if( effFromNext != null && validUptoNext != null )	
				 			{
				 				 validUpto = Timestamp.valueOf(genericUtility.getValidDateString(validstr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
								 SimpleDateFormat sdf = new SimpleDateFormat(getApplDateFormat());
								 Calendar c = Calendar.getInstance();
								 c.setTime(sdf.parse(validstr));
								 c.add(Calendar.DATE, +1);
								 String  effFromNew = sdf.format(c.getTime());
								 effFromNew2= Timestamp.valueOf(genericUtility.getValidDateString(effFromNew, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
								 System.out.println("@@@@@@@@@@@@ new date into effFromNew2: ["+effFromNew2+"]");
								 
								 if( effFromNew2.after(validUptoNext))
								 {
									 errString = itmDBAccessEJB.getErrorString("","INVDATE9","");
									 return errString;
								 }
								 sql=" UPDATE siteregno SET eff_from= ? " +
										 " where fin_entity = ? " +
										 " and site_code = ? " +
										 " and ref_code = ? "+ 
										 " and valid_upto = ? ";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setTimestamp(1,effFromNew2);
								 pstmt.setString(2,finEntity);
								 pstmt.setString(3,siteCode);
								 pstmt.setString(4,refCode);
								 pstmt.setTimestamp(5,validUptoNext);
								 cnt = pstmt.executeUpdate();
								 pstmt.close();
								 pstmt = null;		
								 System.out.println("@@@@ for next record cnt["+cnt+"]");
				 				
				 			}
				 			
					 }
				}
			}
		}
		catch (SQLException sqe)
		{
			System.out.println("The SQLException occurs in SiteRegNoPrs :"+sqe);
			sqe.printStackTrace();
			throw new ITMException(sqe);
		}
		catch(Exception e)
		{
			System.out.println("The Exception occurs in SiteRegNoPrs :"+e);			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :SiteRegNoPrs :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		
	return errString;
	}
	private Node getCurrentDetailFromDom(Document dom,String domId)
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String currDomId = "";
		int	detailListLength = 0;

		detailList = dom.getElementsByTagName("Detail2");
		detailListLength = detailList.getLength();
		for (int ctr = 0;ctr < detailListLength;ctr++)
		{
			currDetail = detailList.item(ctr);
			currDomId = currDetail.getAttributes().getNamedItem("domID").getNodeValue();
			if (currDomId.equals(domId))
			{
				reqDetail = currDetail;
				break;
			}			
		}
		return reqDetail;
	}
	
}
