package ibase.webitm.ejb.adm;

import ibase.webitm.utility.*;
//import ibase.utility.CommonConstants;
import ibase.system.config.*;
//import java.text.*;
import java.util.*;
//import javax.xml.parsers.*;
import javax.ejb.*;
//import javax.naming.InitialContext;
import java.io.*;

import java.util.Calendar;
import java.util.Date;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.rmi.RemoteException;
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


//public class SReturnPrsEJB extends ValidatorEJB implements SessionBean
@Stateless // added for ejb3
public class CopyGradePerkParamPrs extends ValidatorEJB implements CopyGradePerkParamPrsLocal, CopyGradePerkParamPrsRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();		
	ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
	public String preSaveRec() throws RemoteException, ITMException
	{
		return "";
	}

	public String preSaveRec(String xmlString1, String domId, String objContext, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException
	{
		System.out.println("@@@@@@@@********* CopyGradePerkParamPrs Called *******@@@@@@@@");
		Document dom = null;
		String errString = "";
		//ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		try
		{
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				errString = executepreSaveRec(dom,domId,objContext,editFlag,xtraParams,conn);
				if (errString == null || errString.trim().length() == 0)
				{

					//errString = itmDBAccessLocal.getErrorString("","VTMCONF2","");
				}
				else
				{
					conn.rollback();
					//errString = itmDBAccessLocal.getErrorString("","VMPROCUSG","");
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :CopyPayParamPrs :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
	{
		//Connection conn = null;
		PreparedStatement pstmt = null,pstmt1 = null,pstmtInser = null;
		ResultSet rs = null ,rs1 = null;
		String pay_table = "",new_pay_table="";
		String sql = "",sql1 = "",sqlInser;
		String chgUser = "",chgTerm = "";
		String grade_code="",new_grade_code="";
		String ad_code="",ad_code_main="",type="";
		double cycle_time=0.0,cycle_start=0.0,cycle_end=0.0,validity_period=0.0;
		java.sql.Timestamp exp_date=null,eff_date=null;

		java.sql.Timestamp last_proc_for=null,next_proc_date=null;

		double amount=0.0;

		int cnt = 0,updCnt = 0;

		String gradeCode = "";

		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();		
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		String errString = "";
		//		boolean insertFlag=false;
		try
		{	
			ConnDriver connDriver = new ConnDriver();			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			pay_table = genericUtility.getColumnValue("pay_table",dom);
			grade_code = genericUtility.getColumnValue("grade_code",dom);
			new_pay_table = genericUtility.getColumnValue("new_pay_table",dom);
			new_grade_code = genericUtility.getColumnValue("new_grade_code",dom);
			gradeCode = genericUtility.getColumnValue("grade_code",dom);
			String currAppdate ="";
			java.sql.Timestamp currDate = null;
			Object date = null;
			currDate =new java.sql.Timestamp(System.currentTimeMillis()) ;
			System.out.println(genericUtility.getDBDateFormat());
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = sdf.parse(currDate.toString());
			currDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
			currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDate).toString();

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chg_user");

			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chg_term");


			if( pay_table == null || pay_table.trim().length() ==0 )
			{
				//errString = itmDBAccessEJB.getErrorString("","PRDCODNULL","");ME
				//return errString;ME
			}
			else if( new_pay_table == null || new_pay_table.trim().length() == 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","NEWPAYTBL","");
				return errString;
			}
			else if( new_pay_table != null && new_pay_table.trim().length() > 0 )
			{
				/*	if( new_pay_table.trim().length() > 5 )
				{

					errString = itmDBAccessEJB.getErrorString("","NEWSITEINV","");
            		return errString;

				}*/
				/*else if(checkValidity(new_pay_table, "pay_table", "site", conn)<=0){
					insertFlag=true;
					errString = itmDBAccessEJB.getErrorString("","NEWSITNEXI","");
	        		return errString;
				}*/

			}

			if(new_pay_table!=null && checkValidity(new_pay_table, "pay_table", "PAYTABLE", conn)<=0){
				//insertFlag=true;
				errString = itmDBAccessEJB.getErrorString("","NEWPAYNO","");
				return errString;
			}

			if (new_grade_code == null || new_grade_code.trim().length() == 0)
			{
				errString = itmDBAccessEJB.getErrorString("","VTCPGPRK01","");
        		return errString;
			}
			if( new_grade_code!=null && checkValidity(new_grade_code, "GRADE_CODE", "GRADE", conn)<=0){
				//insertFlag=true;
				errString = itmDBAccessEJB.getErrorString("","GCNOEXIST","");
				return errString;
			}





			/*ME if( pay_table != null && pay_table != null && new_pay_table.trim().length() > 0 && pay_table.trim().length() > 0 )
			{	
			//sql = " SELECT var_name,var_type,var_value,descr,var_subs FROM payrparm WHERE prd_code = ? ";   // and var_name = ?";

			sql="SELECT * FROM PAYTABLE WHERE PAY_TABLE=?";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1,new_pay_table);
			rs = pstmt.executeQuery();
			if(rs.next()){

			}else{
				errString = itmDBAccessEJB.getErrorString("","NEWPAYNO","");
				return errString;
			}*/

			System.out.println("BEFORE PAYTABLE GRADE CHECK ............");
			System.out.println("----------- pay_table : "+pay_table+" grade_code : "+grade_code);	
			// sql =	"SELECT * FROM GRADE_PERK WHERE PAY_TABLE=? "; 
			sql =	"SELECT * FROM GRADE_PERK WHERE PAY_TABLE=? AND GRADE_CODE = ?"; 
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1,pay_table);
			pstmt.setString(2,gradeCode);
			//	pstmt.setString(2,grade_code);
			rs = pstmt.executeQuery();
			while(rs.next())
			{

				grade_code = rs.getString("grade_code");
				ad_code =rs.getString("ad_code");
				ad_code_main =rs.getString("ad_code__main");
				eff_date= rs.getTimestamp("eff_date");
				exp_date=rs.getTimestamp("exp_date");
				java.sql.Timestamp expDateChg = null;
				if(exp_date != null)
				{
					expDateChg=getTimestamp(exp_date);
				}
				type=rs.getString("type");
				amount=rs.getDouble("amount");
				cycle_time=rs.getDouble("cycle_time");
				cycle_start=rs.getDouble("cycle_start");
				cycle_end=rs.getDouble("cycle_end");
				last_proc_for=rs.getTimestamp("last_proc_for");
				java.sql.Timestamp last_proc_forChg = null;
				if(last_proc_for != null)
				{
					last_proc_forChg=getTimestamp(last_proc_for);
				}
				next_proc_date=rs.getTimestamp("next_proc_date");
				java.sql.Timestamp next_proc_dateChg = null;
				System.out.println("next_proc_date value :"+next_proc_date);
				if(next_proc_date != null)
				{
					next_proc_dateChg=getTimestamp(next_proc_date);
				}
				validity_period=rs.getDouble("validity_period");

				System.out.println("IN LOOP");

				java.sql.Timestamp effDateChg = null;

				//sql1 = "SELECT count(*) FROM payrparm WHERE prd_code = ? and var_name = ? ";
				sql1 ="SELECT count(*) FROM GRADE_PERK WHERE PAY_TABLE = ? and GRADE_CODE=? and AD_CODE=? and AD_CODE__MAIN=? and EFF_DATE=?";
				pstmt1 =  conn.prepareStatement(sql1);
				if(new_pay_table != null && new_pay_table.trim().length() > 0 && new_grade_code==null){
					//if only new pay table enters then  copy data from paytable and against grade code into new paytable
					System.out.println("COUNT CONDTION 1");
					pstmt1.setString(1, new_pay_table);
					pstmt1.setString(2, grade_code);


				}else if(new_pay_table == null   && new_grade_code!=null && new_grade_code.trim().length() > 0){
					//if only new gradecode enters then  copy data from paytable and against grade code into exiting paytable with new gradecode
					System.out.println("COUNT CONDTION 2");
					pstmt1.setString(1, pay_table);
					pstmt1.setString(2, new_grade_code);


				} else if(new_pay_table != null && new_pay_table.trim().length() > 0 && new_grade_code!=null && new_grade_code.trim().length() > 0){
					//if both new gradecode and gradecode enters then  copy data from paytable and against grade code into new paytable and new gradecode
					System.out.println("COUNT CONDTION 3");
					pstmt1.setString(1, new_pay_table);
					pstmt1.setString(2, new_grade_code);

				} else if(new_pay_table == null   && new_grade_code==null ){
					// validation
					System.out.println("NEW GRADE CODE AND PAYTABLE BOTH ARE EMPTY.........");
					errString = itmDBAccessEJB.getErrorString("","PAYGRTBL","");
					return errString;

				}

				pstmt1.setString(3, ad_code);
				pstmt1.setString(4, ad_code_main);
				if(eff_date != null)
				{
					effDateChg=getTimestamp(eff_date);
				}
				if(effDateChg != null)
				{
					pstmt1.setTimestamp(5, effDateChg);
				}

				System.out.println("EXECUTE");
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					cnt =  rs1.getInt(1);

				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
				System.out.println("@@@@@:::cnt:["+cnt+"]");
				if(cnt == 0)
				{
					System.out.println("IN INSERT");
					sqlInser = "INSERT INTO GRADE_PERK (pay_table, grade_code, ad_code,ad_code__main, eff_date, exp_date, type, amount, cycle_time, cycle_start, cycle_end, last_proc_for,next_proc_date,chg_date,chg_user,chg_term,validity_period) "
							+" VALUES (?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?,?,?,?)  ";
					pstmtInser	= conn.prepareStatement(sqlInser);


					if(new_pay_table != null && new_pay_table.trim().length() > 0 && new_grade_code==null  ){
						//if only new pay table enters then  copy data from paytable and against grade code into new paytable
						System.out.println("INSERT CONDTION 1");
						pstmtInser.setString(1, new_pay_table);
						pstmtInser.setString(2, grade_code);

					}else if(new_pay_table == null   && new_grade_code!=null && new_grade_code.trim().length() > 0){
						//if only new gradecode enters then  copy data from paytable and against grade code into exiting paytable with new gradecode
						System.out.println("INSERT CONDTION 2");
						pstmtInser.setString(1, pay_table);
						pstmtInser.setString(2, new_grade_code);

					} else if(new_pay_table != null && new_pay_table.trim().length() > 0 && new_grade_code!=null && new_grade_code.trim().length() > 0){
						//if both new gradecode and gradecode enters then  copy data from paytable and against grade code into new paytable and new gradecode
						System.out.println("INSERT CONDTION 3");
						pstmtInser.setString(1, new_pay_table);
						pstmtInser.setString(2, new_grade_code);

					}

					//pstmtInser.setString(1,new_pay_table);ME
					//pstmtInser.setString(2, grade_code);ME
					pstmtInser.setString(3, ad_code);
					pstmtInser.setString(4, ad_code_main);
					pstmtInser.setTimestamp(5,effDateChg);
					pstmtInser.setTimestamp(6,expDateChg);
					pstmtInser.setString(7, type);   
					pstmtInser.setDouble(8,amount);
					pstmtInser.setDouble(9,cycle_time);
					pstmtInser.setDouble(10,cycle_start);
					pstmtInser.setDouble(11,cycle_end);
					pstmtInser.setTimestamp(12,last_proc_forChg);
					pstmtInser.setTimestamp(13, next_proc_dateChg);
					pstmtInser.setTimestamp( 14, currDate);
					pstmtInser.setString(15, chgUser);
					pstmtInser.setString(16, chgTerm);
					pstmtInser.setDouble(17,validity_period);
					updCnt = pstmtInser.executeUpdate();
					pstmtInser.close();
					pstmtInser=null;
					if(updCnt > 0 )
					{
						conn.commit();
						System.out.println("@@@@@ Record inserted for table no["+new_pay_table +"]:::::::: @@@@@");
					}

				}
				else
				{
					System.out.println("@@@@@ :::::Record already exist for PeriodCode["+new_pay_table +"]::varName["+ grade_code +"]  :::::: @@@@@");
					//errString = itmDBAccessLocal.getErrorString("","VMDUPREC1","");
					// errString = itmDBAccessEJB.getErrorString("","NEWPAYAA","");ME
				}

			}
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}

			//valueXmlString.append("</Root>\r\n");
		} // end of try 
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch(Exception ex)
			{
				throw new ITMException(ex);

			}
			throw new ITMException(e);

		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
			}
			catch(Exception e)
			{
				throw new ITMException(e);
			}
		}
		return errString ;
	}

	public String getDD_MM_YYYY_format(String str) throws Exception{

		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		Date date;
		String dd_mm_yyyy="";

		date = (Date)formatter.parse(str);
		System.out.println("DDMMYYYY="+date);
		SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy");   
		dd_mm_yyyy=format.format(date);

		//String orcleFormat= oracleFormatDate(dd_mm_yyyy); 
		System.out.println("orcleFormat dd-mm-yyyy=="+dd_mm_yyyy);
		return dd_mm_yyyy;
	}
	java.sql.Timestamp timestamp = null;

	public Timestamp getTimestamp(Timestamp str) throws Exception{
		//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
		/*System.out.println("=====time"+genericUtility.getDBDateFormat());
	SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
		java.util.Date date = sdf.parse(str.toString());
		 timestamp =  java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");

		System.out.println("=====time"+timestamp);*/
		java.sql.Timestamp currDate = null;
		Object date = null;
		currDate =new java.sql.Timestamp(System.currentTimeMillis()) ;
		System.out.println("date of generic utility"+genericUtility.getDBDateFormat()+"STR VALUE:"+str);
		SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
		date = sdf.parse(str.toString());
		currDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
		return currDate;

	}



	public int checkValidity(String retrival, String column_name, String table_name,  Connection from_site_conn) throws SQLException{
		boolean flag=false;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String Query="";
		Connection conn=null;
		int count=0;
		Connection localConnection =null;

		try{
			conn = from_site_conn;
			Query="select count("+column_name+")  from "+table_name+" where "+column_name+"='"+retrival+"'";
			System.out.println("====QUERY=="+Query);
			pstmt=conn.prepareStatement(Query);
			rs=pstmt.executeQuery(Query);
			if(rs.next()){
				count=rs.getInt(1);
			}

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
			if(rs!=null){
				rs.close();
				rs=null;
			}
		}
		System.out.println("count="+count);
		return count;
	}

}



