/*
    Window Name : w_sordalloc
*/
package ibase.webitm.ejb.adm;

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

import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.utility.CommonConstants;
import ibase.system.config.*;

public class ShiftProcess extends ProcessEJB implements ShiftProcessLocal,ShiftProcessRemote //SessionBean
{
	/* UPDATED BY NISAR ON 17/12/07 FOR REQUEST ID : AD7SUN0016 */
	String 	sql = null;
	String sDateTo = null;
	String sDateFr = null;
	double balStockQty = 0;

	java.sql.Timestamp dateTo =  null;
	java.sql.Timestamp dueDate =  null;
	java.sql.Timestamp dateFr =  null;
	java.sql.Date toDay = null;
	
	java.util.Date fromPrdDate = null;
	java.util.Date toPrdDate = null;

	AdmCommon admCommon =  new AdmCommon();

	//InvAllocTrace invallocTrace = new InvAllocTrace();
	GenericUtility genericUtility = GenericUtility.getInstance();  
	//GenericUtility genericUtility = new GenericUtility(); //COMMENTED BY ALKA 24/07/07 AS GENERIC UTILITY NOT INITIALIASED PROPERLY.
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

	CommonConstants commonConstants = new CommonConstants();

	String chgUser = null;
	String chgTerm = null;
	/*

  	public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("********ShiftProcessEJB ejbCreate called**********");
		}
		catch (Exception e)
		{
			System.out.println("Exception :ShiftProcessEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}

	public void ejbActivate()
	{
	}

	public void ejbPassivate()
	{
	}*/

	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		String userId = "";
		try
		{
			System.out.println("xtraParams :: " + xtraParams);
			System.out.println("xmlString2 :: " + xmlString2);
			System.out.println("xmlString :: " + xmlString);

			if(xtraParams != null )
			{
				userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				System.out.println("UserId :: " + userId);
				System.out.println("chgUser :: " + chgUser);
			}

			System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
			if( xmlString != null && xmlString.trim().length() !=0 )
			{
				System.out.println("XML String :" + xmlString);
				headerDom = genericUtility.parseString(xmlString);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String2 :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2);
			}
			rtrStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :ShiftProcessEJB : process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = itmDBAccessEJB.getErrorString("","VTPROUNSUC",userId);
			//throw new ITMException(e);
		}
		return rtrStr;
	}

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		ConnDriver connDriver = null;
		System.out.println("Process function called from process().........");
		String empCodeFr = "", empCodeTo = "" ;

		String deptCodeFr = "";
		String deptCodeTo = "";
		String siteCodeFr = "";
		String siteCodeTo = "";
		String emp = "";
		String workShift = "";
		String holTblno = "";
		String fromDate = "";
		String toDate = "";
		String dateFr = "";
		String dateTo = "";
		String lastProcDate =  null;
		String tmpDate = null;
		String tmpFr =  null;
		String tmpTo =  null;
		String pattern = "";
		String patternCode = "";
		String patternCode2 = "";
		String workSite = "";
		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
		String sql= "" ;
		String insertSql= "" ;
		String userId = "" ;

		String resultString = "";
		String dayslist1 = "";
		String dayslist2 = "";
		java.util.Date currDate = null;
		java.sql.Date procDate = null;
		java.sql.Date startDate = null;
		java.sql.Date frSqlDate2 = null;
		ArrayList dayslist = new ArrayList();
		int days ;
		int count=0;
		int upd =0;
		int counter=0;
		Connection conn = null;
		ResultSet rsn1 = null,rsn2=null,rsn3=null,rsn4=null;
		ResultSet rs1 = null;
		PreparedStatement pstmtn1=null,pstmtn2=null,pstmtn3=null,pstmtn4=null;
		Statement stn1 = null,stn2 = null;
		StringBuffer retTabSepStrBuff = new StringBuffer();
		java.sql.Timestamp fromDate1 =  null;
		java.util.Date toDate1 = null;
		java.util.Date tmpTo1 = null;
		java.util.Date tmpFr1 = null;
		
		String prdCode = "";
		String patternCodeFilt = "";
		int patternDays = 0;
		int totalDays = 0;
		int cnt = 0;
		double pendQty = 0;
		double allocQty = 0;
		boolean bappend = false ;
		boolean flagException = false;
		String holiTblNo =null;
		int selectedLineNo = 0;
		
		ArrayList empList = new ArrayList();
		HashMap empEventsMap = new HashMap();
		int shiftIndexFr = 0;//Added by jaimin on 16/06/2008 (AD89SUN014)
		int shiftIndexTo = 0;//Added by Jaimin on 16/06/2008 (AD89SUN014)
		try
		{
            connDriver = new ConnDriver();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			System.out.println("xtraParams :"+xtraParams);
			conn.setAutoCommit(false);
			if( xtraParams != null )
			{
				userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				//termId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termID");  //COMMENTED BY ALKA 25/07/07 AS VARIABLE termID CHANGED TO GLOBAL VARIABLE chgTerm
				chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termID");
			}
			
			Node hdrNode = null;
			NodeList detailList = null;
			
			detailList = headerDom.getElementsByTagName( "Header" );
			
			selectedLineNo = 0;
			//getting header data to be inserted
			hdrNode = detailList.item( selectedLineNo );

			if( chgUser == null || chgUser.trim().length() == 0 )
			{
				chgUser = (String)genericUtility.getColumnValueFromNode( "chg_user", hdrNode );
				userId = chgUser;
			}
			if( chgTerm == null || chgTerm.trim().length() == 0 )
			{
				chgTerm = (String)genericUtility.getColumnValueFromNode( "chg_term", hdrNode );
			}

			toDay = new java.sql.Date(System.currentTimeMillis());
			//System.out.println(" termId:::[" +termId+"]"); //COMMENTED BY ALKA 25/07/07 AS VARIABLE termID CHANGED TO GLOBAL VARIABLE chgTerm
			System.out.println( "chgUser:::[" + chgUser + "]" );
			System.out.println( "chgTerm:::[" + chgTerm + "]" );
			empCodeFr = genericUtility.getColumnValue( "emp_code__fr", headerDom );
			System.out.println( "empCodeFr  :" + empCodeFr );
			if ( empCodeFr == null || empCodeFr.trim().length() == 0 )
			{
				empCodeFr = "00";
			}
			empCodeTo = genericUtility.getColumnValue( "emp_code__to", headerDom );
			System.out.println( "empCodeTo  :" + empCodeTo );
			if ( empCodeTo == null || empCodeTo.trim().length() == 0 )
			{
				empCodeTo = "ZZ";
			}
			deptCodeFr = genericUtility.getColumnValue("dept_code__fr",headerDom);
			System.out.println("deptCodeFr  :"+deptCodeFr);
			if ( deptCodeFr == null || deptCodeFr.trim().length() == 0 )
			{
				deptCodeFr = "00";
			}
			deptCodeTo = genericUtility.getColumnValue("dept_code__to",headerDom);
			if ( deptCodeTo == null || deptCodeTo.trim().length() == 0 )
			{
				deptCodeTo = "ZZ";
			}
			siteCodeFr = genericUtility.getColumnValue("site_code__fr",headerDom);
			if ( siteCodeFr == null || siteCodeFr.trim().length() == 0 )
			{
				siteCodeFr = "00";
			}
			siteCodeTo = genericUtility.getColumnValue("site_code__to",headerDom);
			if ( siteCodeTo == null || siteCodeTo.trim().length() == 0 )
			{
				siteCodeTo = "ZZ";
			}
			patternCodeFilt = genericUtility.getColumnValue("pattern_code",headerDom);
			fromDate = genericUtility.getColumnValue("date__fr",headerDom);
			System.out.println(" From Date [" +fromDate+"]");
			toDate = genericUtility.getColumnValue("date__to",headerDom);
			System.out.println("ToDate:::[" +toDate+"]");
			System.out.println("empCodeFr :"+empCodeFr+" empCodeTo :"+empCodeTo+" deptCodeFr :"+deptCodeFr+" siteCodeFr  :"+siteCodeFr);

			//getting date in sql date and dbdate format
			String strFrDate = genericUtility.getValidDateString(fromDate,genericUtility.getApplDateFormat(),genericUtility.getApplDateFormat());
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			System.out.println("FromDate in DbDate Format :["+strFrDate+"]");
			String strToDate = genericUtility.getValidDateString(toDate,genericUtility.getApplDateFormat(),genericUtility.getApplDateFormat());
			System.out.println("To Date in Db Date Format:["+strToDate+"]");
			java.util.Date frDate = sdf.parse(strFrDate);
			fromPrdDate = frDate;
			System.out.println("From Date Object  :["+frDate+"]");
			java.sql.Date frSqlDate = new java.sql.Date(frDate.getTime());
			System.out.println("java.sql.Date From :["+frSqlDate+"]");
			SimpleDateFormat sdt = new SimpleDateFormat(genericUtility.getApplDateFormat());
			java.util.Date tDate = sdt.parse(strToDate);
			toPrdDate = tDate;
			System.out.println("To date Object :"+tDate);
			java.sql.Date toSqlDate = new java.sql.Date(tDate.getTime());
			System.out.println("java.sql.Date To : -["+toSqlDate+"]");
			//end getting dates in sqldate and dbdate format
			if (siteCodeFr != null )
			{		
				//query changed by msalam on 170308 as it was unnecessary to call the query repeteadly
				getDataSql = " SELECT e.EMP_CODE, e.EMP_FNAME, e.EMP_LNAME, e.WORK_SHIFT, e.SHIFT_ROT, e.HOL_TBLNO, "
							+"		e.cadre, e.grade, e.shift_Index, e.date_Join, e.PATTERN_CODE, e.WORK_SITE, ws.FROM_DT "
							+" FROM EMPLOYEE e, "
							+"		( SELECT t.emp_code, t.FROM_DT FROM WORK_SHIFT t "
							+"			WHERE t.EMP_CODE  >= '" + empCodeFr + "'" 
							+"				AND t.EMP_CODE <= '" + empCodeTo + "'" 
							+"				and ? BETWEEN FROM_DT AND TO_DT ) ws "
							+" WHERE e.emp_code = ws.emp_code( + ) "
							+"		and e.EMP_CODE  >= '" + empCodeFr + "'"  
							+"		AND e.EMP_CODE <= '" + empCodeTo + "'" 
							+"	 	AND DEPT_CODE >= '" + deptCodeFr + "'" 
							+"		AND DEPT_CODE <= '" + deptCodeTo + "'" 
							+"		AND WORK_SITE >= '" + siteCodeFr + "'" 
							+"		AND WORK_SITE <= '" + siteCodeTo + "'" 
							+"		AND SHIFT_ROT = 'Y' " 
							+"		AND nvl( status, ' ') <> 'S' ";

						/*
								"SELECT EMP_CODE, "
								+"EMP_FNAME, "
								+"EMP_LNAME, "
								+"WORK_SHIFT, "
								+"SHIFT_ROT, "
								+"HOL_TBLNO "
								+"FROM EMPLOYEE "
								+"WHERE EMP_CODE >= '"+empCodeFr+"' "
								+"AND EMP_CODE <= '"+empCodeTo+"' "
								+"AND DEPT_CODE >= '"+deptCodeFr+"' "
								+"AND DEPT_CODE <= '"+deptCodeTo+"' "
								+"AND WORK_SITE >= '"+siteCodeFr+"' "
								+"AND WORK_SITE <= '"+siteCodeTo+"' "
								+"AND SHIFT_ROT = 'Y' "
								+"AND nvl(status,' ') <> 'S' ";
						*/
				PreparedStatement pStmt = null;
				System.out.println("\n\n getDataSql  :"+getDataSql);
				pStmt = conn.prepareStatement( getDataSql );
				System.out.print("Parameters 1:: [" + frSqlDate+"]");

				pStmt.setDate( 1, frSqlDate );
				
				rsn1 = pStmt.executeQuery( );
				
				while( rsn1.next() )
				{
					EmpInfo empInfo = new EmpInfo();
					empInfo.empCode = rsn1.getString( "EMP_CODE" );
					empInfo.workShift = rsn1.getString( "WORK_SHIFT" );
					empInfo.holTblno = rsn1.getString( "HOL_TBLNO" );
					empInfo.fromDt = rsn1.getDate( "FROM_DT" );
					empInfo.cadre = rsn1.getString( "CADRE" );
					empInfo.grade = rsn1.getString( "GRADE" );
					empInfo.workSite = rsn1.getString( "WORK_SITE" );
					empInfo.shiftIndex = rsn1.getInt( "SHIFT_INDEX" );
					empInfo.dateJoin = rsn1.getDate( "DATE_JOIN" );					
					empInfo.patternCode = rsn1.getString( "PATTERN_CODE" );
					
					empList.add( empInfo );
				}
				
				rsn1.close();
				rsn1 = null;
				pStmt.close();
				pStmt = null;
				
				int noOfEmp = empList.size();
				
				//while( rsn1.next() )
				for( int noOfEmpCnt = 0; noOfEmpCnt < noOfEmp; noOfEmpCnt++ )
				{
					holiTblNo = null;
					counter++;
					System.out.println(" In Loop .........");
					/*
					emp = rsn1.getString("EMP_CODE");
					workShift = rsn1.getString("WORK_SHIFT");
					holTblno = rsn1.getString("HOL_TBLNO");
					*/
					EmpInfo empInfo = null;
					empInfo = ( EmpInfo )empList.get( noOfEmpCnt );
					emp = empInfo.empCode;
					workShift = empInfo.workShift;
					holTblno = empInfo.holTblno;
					java.sql.Date frSqlDate1 = null;
					frSqlDate1 = empInfo.fromDt;
					if( frSqlDate1 != null )
					{
						frSqlDate = frSqlDate1;
					}

					//commented by msalam on 170308 as all the information is coming in getdatasql Query
					/*
					sql = "SELECT FROM_DT FROM WORK_SHIFT WHERE EMP_CODE = ? AND ? BETWEEN FROM_DT AND TO_DT";
					System.out.println("sql :"+sql);
					pstmtn1 = conn.prepareStatement(sql);
					System.out.println("Parameter Emp Code  :["+emp+"]");
					System.out.println("Parameter frSqlDate  :["+frSqlDate+"]");
					pstmtn1.setString(1,emp);
					pstmtn1.setDate(2,frSqlDate);
					rsn2 = pstmtn1.executeQuery();//updated by Nisar 21/02/07 rs to rsn2
					frSqlDate2 = frSqlDate;
					if(rsn2.next()){
						System.out.println("Record Found in WORK_SHIFT Table");
						java.sql.Date frSqlDate1 = null;
						frSqlDate1 = rsn2.getDate("FROM_DT");
						if(frSqlDate1 != null){
						    frSqlDate = frSqlDate1;
						}
					}
					rsn2.close();
					rsn2=null;
					pstmtn1.close();
					pstmtn1=null;
					*/
					//end commented by msalam on 170308 as all the information is coming in getdatasql Query
					sql = "DELETE FROM WORK_SHIFT WHERE EMP_CODE = ? "
						  +"AND FROM_DT BETWEEN ? AND ? "
						  +"AND TO_DT BETWEEN ? AND ? ";

					System.out.println("sql :"+sql);
					pstmtn2 = conn.prepareStatement(sql);
					pstmtn2.setString(1,emp);
					pstmtn2.setDate(2,frSqlDate);
					System.out.println("frSqlDate :-"+frSqlDate);
					pstmtn2.setDate(3,toSqlDate);
					System.out.println("toSqlDate :-"+toSqlDate);
					pstmtn2.setDate(4,frSqlDate);
					pstmtn2.setDate(5,toSqlDate);
					upd  = pstmtn2.executeUpdate();
					System.out.println("No Of record deleted fromWORK_SHIFT ["+upd+"]");
					pstmtn2.close();
					pstmtn2=null;
					
					//commented by msalam on 170308 as all the information is coming in getdatasql Query
					//no need of this as it can be put in outer query
					/*
					sql =	 "SELECT  PATTERN_CODE,WORK_SITE ,HOL_TBLNO      FROM EMPLOYEE  "
							+"WHERE EMP_CODE = '"+emp+"' ";
					System.out.println("sql :"+sql);
					stn2 = conn.createStatement();
					rsn3 = stn2.executeQuery(sql);
					System.out.println("Pattern Code From Filter  ["+patternCodeFilt+"]");
					if(rsn3.next())
					{
						patternCode = rsn3.getString(1);
						workSite = rsn3.getString(2);
						holiTblNo =  rsn3.getString(3);
						System.out.println("Pattern Code Of Employee ["+patternCode+"]");
						System.out.println("workSite  Of Employee ["+workSite+"]");
						System.out.println("holiTblNo  Of Employee ["+holiTblNo+"]");
					}
					rsn3.close();
					rsn3 = null;
					stn2.close();
					stn2 = null;
					*/
					//end commented by msalam on 170308 as all the information is coming in getdatasql Query
					
					patternCode = empInfo.patternCode;
					workSite = empInfo.patternCode;
					holiTblNo =  empInfo.holTblno;

					//no need of this end
					if (patternCodeFilt != null)
					{
						patternCode = patternCodeFilt;
					}

					ArrayList frPatternCodeList = new ArrayList();
					ArrayList toPatternCodeList = new ArrayList();
					ArrayList effDateList = new ArrayList();
					java.sql.Date effDate = new java.sql.Date(System.currentTimeMillis());
					
					PreparedStatement patternPStmt = null;
					ResultSet patternRs = null;
					String lastPatternCodeStr = "";
					String lastShiftIndexStr = ""; //Added by jaimin on 16/06/2008 (AD89SUN014)

					lastShiftIndexStr =  empInfo.shiftIndex+""; //Added by jaimin on 16/06/2008 (AD89SUN014)
					/*sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"' "+
							"AND EFF_DATE BETWEEN ? AND ? ORDER BY EFF_DATE";*/ //Commented by jaimin on 16/06/2008 (AD89SUN014)
					sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE, " +
							" CASE WHEN SHIFT_INDEX__FR IS NULL THEN 0 ELSE SHIFT_INDEX__FR END AS SHIFT_INDEX__FR, "+
							" CASE WHEN SHIFT_INDEX__TO IS NULL THEN 0 ELSE SHIFT_INDEX__TO END AS SHIFT_INDEX__TO "+
							" FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"' "+
							"AND EFF_DATE BETWEEN ? AND ? ORDER BY EFF_DATE"; //Sql modify by jaimin on 16/06/2008 (AD89SUN014)
					System.out.print("EMP_EMPLOY_EVENTS[sql] :: " + sql);
					System.out.print("Parameters 1:: [" + frSqlDate+"]");
					System.out.print("Parameters 2:: [" + toSqlDate+"]");
					patternPStmt = conn.prepareStatement( sql );
					patternPStmt.setDate(1,frSqlDate);
					patternPStmt.setDate(2,toSqlDate);
					patternRs = patternPStmt.executeQuery();
					while( patternRs.next() )
					{
						System.out.print("Patten_Code_fr :: " + patternRs.getString( "PATTERN_CODE__FR" ) );
						System.out.print("Patten_Code_to :: " + patternRs.getString( "PATTERN_CODE__TO" ));
						System.out.print("Patten_Code_fr :: " + patternRs.getDate( "EFF_DATE" ));
						frPatternCodeList.add( patternRs.getString( "PATTERN_CODE__FR" ) );
						toPatternCodeList.add( patternRs.getString( "PATTERN_CODE__TO" ) );
						effDateList.add( patternRs.getDate( "EFF_DATE" ) );
						shiftIndexFr =  patternRs.getInt("SHIFT_INDEX__FR"); //Added by jaimin on 16/06/2008 (AD89SUN014)
						shiftIndexTo =  patternRs.getInt("SHIFT_INDEX__TO"); //Added by jaimin on 16/06/2008 (AD89SUN014)
					}
					patternRs.close();
					patternRs = null;
					patternPStmt.close();
					patternPStmt=null;

					procDate = frSqlDate;
					java.sql.Date stShiftDate = procDate;
					java.sql.Date edShiftDate = procDate;
					System.out.println("procDate::["+procDate+"]");
					if(effDateList.size() > 0)
					{
						System.out.println("toSqlDate : "+toSqlDate+" procDate :"+procDate);
						System.out.println("stShiftDate : "+stShiftDate+" edShiftDate :"+edShiftDate);
						while(edShiftDate.compareTo(toSqlDate)<= 0)
						{
							System.out.println(" 2 >> edShiftDate : "+edShiftDate+" toSqlDate :"+toSqlDate);
							for(int ctr = 0;ctr < effDateList.size();ctr++)
							{
							
								java.sql.Date tempEffDate = java.sql.Date.valueOf(effDateList.get(ctr).toString());
								System.out.println("tempEffDate[Employee events] :: "+tempEffDate+" stShiftDate [Process Date(fr)]:: "+stShiftDate+" edShiftDate [Process(fr)]]:"+edShiftDate);
								System.out.println("edShiftDate.compareTo(tempEffDate)[ "+edShiftDate.compareTo(tempEffDate)+"]");

								if(edShiftDate.compareTo(tempEffDate) == 0 && tempEffDate.compareTo(frSqlDate) == 0)
								{
								    continue;
								}
								if(edShiftDate.compareTo(tempEffDate) == 0)
								{
									System.out.println("stShiftDate "+stShiftDate+" edShiftDate "+edShiftDate);
									//errString = insertUpdateWorkShift(empInfo, emp,stShiftDate,edShiftDate,frPatternCodeList.get(ctr).toString(),toPatternCodeList.get(ctr).toString(),holiTblNo,conn);//Commented by jaimin on 16/06/2008 (AD89SUN014)
									errString = insertUpdateWorkShift(empInfo, emp,stShiftDate,edShiftDate,frPatternCodeList.get(ctr).toString(),toPatternCodeList.get(ctr).toString(),holiTblNo,shiftIndexFr,conn);//Added by jaimin on 16/06/2008 (AD89SUN014)
									if(errString.length() > 0){
									    conn.rollback();
									    return errString;
									}
									stShiftDate = edShiftDate;
									lastPatternCodeStr = toPatternCodeList.get(ctr).toString();
									lastShiftIndexStr = shiftIndexTo+""; //Added by jaimin on 16/06/2008 (AD89SUN014)
								}
							}
							edShiftDate = new java.sql.Date(edShiftDate.getTime()+(24*60*60*1000));
							System.out.println("After Adding 1 to edShiftDate ["+edShiftDate+"]");
						}
					}
					else if(effDateList.size() == 0)
					{
						System.out.println("No record in emp_employ_events else part execute");
						System.out.println("FrSqlDate :: " + frSqlDate);
						/*sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"' AND EFF_DATE = (SELECT MAX(EFF_DATE) FROM "+
						" EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"' AND EFF_DATE < ? ) ORDER BY CHG_DATE DESC";*/ //Commented by jaimin on 16/06/2008 (AD89SUN014)
						sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE, "
							+" CASE WHEN SHIFT_INDEX__FR IS NULL THEN 0 ELSE SHIFT_INDEX__FR END AS SHIFT_INDEX__FR, "
							+" CASE WHEN SHIFT_INDEX__TO IS NULL THEN 0 ELSE SHIFT_INDEX__TO END AS SHIFT_INDEX__TO "
							+" FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"'  "
							+" AND EFF_DATE = (SELECT MAX(EFF_DATE) FROM "
							+" EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+emp+"' AND EFF_DATE < ? ) ORDER BY CHG_DATE DESC";//Modify by jaimin on 16/06/2008 (AD89SUN014)
						pstmtn4 = conn.prepareStatement(sql);
						System.out.println("SQL :: "+sql);
						pstmtn4.setDate(1,frSqlDate);
						rs1 = pstmtn4.executeQuery();
						if(rs1.next())
						{
							lastPatternCodeStr = rs1.getString(2);
							shiftIndexFr = rs1.getInt("SHIFT_INDEX__FR"); //Added by jaimin on 16/06/2008 (AD89SUN014)
							shiftIndexTo = rs1.getInt("SHIFT_INDEX__TO"); //Added by jaimin on 16/06/2008 (AD89SUN014)
							lastShiftIndexStr = shiftIndexTo+""; //Added by jaimin on 16/06/2008 (AD89SUN014)

						}
						else
						{
							lastPatternCodeStr = "";
							lastShiftIndexStr = empInfo.shiftIndex+""; //Added by jaimin on 16/06/2008 (AD89SUN014)
						}
						rs1.close();
						rs1 = null;
						pstmtn4.close();
						pstmtn4=null;
						System.out.println("At 420 Pattern Code -lastPatternCodeStr -::"+lastPatternCodeStr);
					}
					toSqlDate = new java.sql.Date(toSqlDate.getTime()+(24*60*60*1000));
					if(lastPatternCodeStr == null || lastPatternCodeStr.trim().length() == 0)
					{
						lastPatternCodeStr = patternCode;
						System.out.println("At 425 Pattern Code -lastPatternCodeStr -::"+lastPatternCodeStr);
					}

					// Added by Jaimin - 16/06/2008 - (AD89SUN014)
					if (lastShiftIndexStr == null || lastShiftIndexStr.trim().length() == 0)
					{
						lastShiftIndexStr = "0";
					}
					//End Addition  - Jaimin - 16/06/2008 - (AD89SUN014)

					//errString = insertUpdateWorkShift(empInfo, emp, stShiftDate, toSqlDate, lastPatternCodeStr, lastPatternCodeStr,holiTblNo ,conn);//Commented by jaimin on 16/06/2008 (AD89SUN014)
					errString = insertUpdateWorkShift(empInfo, emp, stShiftDate, toSqlDate, lastPatternCodeStr, lastPatternCodeStr,holiTblNo ,Integer.parseInt(lastShiftIndexStr),conn);//Added by jaimin on 16/06/2008 (AD89SUN014)

					if(errString.length() > 0)
					{
					    conn.rollback();
						System.out.println("Roll back after insertion");
					    return errString;
					}
					//End adding -  jiten -05/10/06
					cnt = cnt + 1;
					System.out.println("cnt:---------------------"+cnt);
					effDateList.clear();
					frPatternCodeList.clear();
				}//end of while
				//commented by msalam as not requied
				/*
				rsn1.close();//Added on 21/02/07
				rsn1=null;
				stn1.close();//Added on 21/02/07
				stn1=null;
				*/
				//end commented by msalam on 170308

				if (cnt == 0)
				{
					System.out.println("Record not found for process ........");
					errString = itmDBAccessEJB.getErrorString("","VTRDNTFD","","",conn);
					return errString;
				}
				else
				{
					System.out.println("Process complete");
					conn.commit();
					//	errString = itmDBAccessEJB.getErrorString("","VTPCOMPT","","",conn);
					//	return errString;
				}
			}
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			try{
				conn.rollback();
			}catch(Exception e){}
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :ProcessNVOService :getComponent :==>\n"+e.getMessage());
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e =e1;
			}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					conn.close();
					conn = null;

					if(pstmtn1!=null)
					{
						pstmtn1.close();
						pstmtn1=null;
					}
					if(pstmtn3!=null)
					{
						pstmtn3.close();
						pstmtn3=null;
					}
				}
			}
			catch(Exception e)
			{
				errString = genericUtility.createErrorString(e);
				throw new ITMException(e);
			}
			dayslist.clear();
			dayslist = null;
			System.gc();
		}
		System.out.println("[ShiftProcessEJB]counter=>"+counter);
		System.out.println("Component Found!!!!!!!!");
		return errString ;
	}

	void cleanup()
	{

	}

	private String insertUpdateWorkShift(EmpInfo empInfo, String empCode, java.sql.Date shiftStDate, java.sql.Date shiftEndDate, String frpatternCode, String topatterenCode, String holiTblNo,Connection conn) throws Exception
	{
		String insertSql ="",updateSql = "",errString = "";
		PreparedStatement pstmtn5 = null,pstmtn6=null,pstmtInsert = null,pstmtUpdate = null;
		PreparedStatement pstmtEmp = null;
		Statement stmt = null;
		ResultSet rsin1 = null, rsin2=null, rsEmp=null;
		String shiftPattern = "";
		String shift = "";
		String cadre = "", grade="", siteCode="";
		int noOfDays = 0;
		int cnt =0, shiftIndex = 0 ;
		int t = 0 ;
		java.sql.Date stDate = null;
		java.util.Date shiftOfDate =  null ;
 		java.util.Date joiningDate =  null;

		try{
			stmt = conn.createStatement();
			if(frpatternCode == null)
			{
				errString = itmDBAccessEJB.getErrorString("","VTPATTERNU","");
				return errString ;
			}
			sql = "SELECT SHIFT_PATTERN,START_DATE,NO_OF_DAYS FROM SHIFT_PATTERN WHERE PATTERN_CODE = '"+frpatternCode+"'";
			rsin1 = stmt.executeQuery(sql);
			if(rsin1.next())
			{
				shiftPattern = rsin1.getString( "SHIFT_PATTERN" );
				stDate = rsin1.getDate( "START_DATE" );
				noOfDays = rsin1.getInt( "NO_OF_DAYS" );
			}
			rsin1.close();
			rsin1 = null;

			stmt.close();
			stmt=null;

			System.out.println("frpatternCode :"+frpatternCode);
			System.out.println("shiftPattern :"+shiftPattern+" stDate :"+stDate+" shiftStDate :"+shiftStDate);

			insertSql = "INSERT INTO WORK_SHIFT (EMP_CODE,FROM_DT,TO_DT,SHIFT,CHG_USER,CHG_TERM,CHG_DATE,REAL_SHIFT,PATTERN_CODE) "
				+" Values (?,?,?,?,?,?,?,?,?)";

			updateSql = "UPDATE WORK_SHIFT SET TO_DT = ? WHERE EMP_CODE = ? AND FROM_DT = ?";

			java.sql.Date shiftStDate2 = shiftStDate;
			System.out.println("shiftStDate2 : " + shiftStDate2 + " shiftEndDate : " + shiftEndDate);

			while( shiftStDate2.compareTo( shiftEndDate ) < 0)
			{
				shiftOfDate = null;
				//Updated by nisar on 21/02/06

				//added by msalam on 23/05/07
				//added by msalam on 25/02/08 for inserting record of Holiday type W
				String holidayType = null;
				//getHolidayTypeForDate( java.util.Date shiftDate, String holiTblNo, Connection conn)
				holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );
				//added by msalam on 25/02/08 for inserting record of Holiday type W
				//next line commented and modified line added by msalam on 25/02/08
				//while(isHolidayForType(shiftStDate2, 'H', holiTblNo,conn))
				
				while( isHolidayForType(shiftStDate2, holidayType, holiTblNo,conn) && shiftStDate2.compareTo( toPrdDate ) <= 0 )
				{
					//added by nisar on 17/12/07 for requestId : AD7SUN0016
					//next line commented and modified line added by msalam on 25/02/08
					//insertIntoWorkShiftForHoliday(empCode,shiftStDate2,shiftStDate2,"H",frpatternCode,conn);
					System.out.println( "From 720" );
					insertIntoWorkShiftForHoliday( empCode, shiftStDate, shiftStDate2, holidayType, frpatternCode, conn );
					//code ended by nisar on 17/12/07   for requestId : AD7SUN0016
					shiftStDate = shiftStDate2;
					shiftStDate2 = new java.sql.Date(shiftStDate2.getTime()+(24*60*60*1000));
					System.out.println("shiftStDate2 :: " + shiftStDate2);
					holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );
				} 

				if( ! (shiftStDate2.compareTo(shiftEndDate) < 0 ))
				{
					System.out.println("In Continue..");
					continue;
				}
				//End of msalam

				pstmtInsert = conn.prepareStatement(insertSql);
				//pstmtn5 =conn.prepareStatement(sql);
				pstmtUpdate = conn.prepareStatement(updateSql);
				//ended by nisar on 21/02/06
				// ADDED  BY DURGA SINGH THAKUR 30/03/2007
				//no need of this as all the information is coming in empInfo bean by msalam on 170308
				/*
				sql = " SELECT CADRE, GRADE, WORK_SITE, SHIFT_INDEX, DATE_JOIN FROM EMPLOYEE WHERE EMP_CODE= ? ";
				pstmtEmp =  conn.prepareStatement(sql);
				pstmtEmp.setString(1, empCode);
				rsEmp = pstmtEmp.executeQuery();
				if(rsEmp.next())
				{
					cadre = rsEmp.getString("CADRE");
					grade = rsEmp.getString("GRADE");
					siteCode = rsEmp.getString("WORK_SITE");
					shiftIndex = rsEmp.getInt("SHIFT_INDEX");
					joiningDate = rsEmp.getDate("DATE_JOIN");
				}
				System.out.println("At 589 Shift_Index :: " + shiftIndex);
				System.out.println("joiningDate :: " + joiningDate);
				System.out.println("shiftStDate2 :: " + shiftStDate2);
				
				if(rsEmp != null)
				{
					rsEmp.close();
					rsEmp = null;
					pstmtEmp.close();
					pstmtEmp=null;
				}
				*/
				//end no need of this as all the information is coming in empInfo bean by msalam on 170308
				cadre = empInfo.cadre;
				grade = empInfo.grade;
				siteCode = empInfo.workSite;
				shiftIndex = empInfo.shiftIndex;
				joiningDate = empInfo.dateJoin;

				//added tarani
				
				if(shiftStDate2.compareTo(joiningDate)< 0)
				{
					shiftStDate2 = (java.sql.Date)joiningDate;
					System.out.println("Calculation Change to Joining Date ["+shiftStDate2+"]");
				}
				
				String shiftDateNewString = admCommon.getAdmEnv(siteCode, cadre, grade,"SHIFT_OF_DATE", new java.sql.Timestamp(System.currentTimeMillis()), conn);
				/*
				COMMENTED BY ALKA 25/07/07 AND MOVED THE BLOCK IN THE IF CONDITION JUST BELOW 
				//Added by msalam on 18/06/07 as told by SUBARTA
				//if shift_of_date < Date_join take stDate = join_date
				java.util.Date tShiftOfDate = null;
				tShiftOfDate = genericUtility.getDateObject(shiftDateNewString);
				System.out.println("Shift Of Date(site_env) :::" +tShiftOfDate);		
				if(tShiftOfDate.compareTo(joiningDate) < 0)
				{
					//added tarani
					stDate = (java.sql.Date)joiningDate;
					
					while(isHolidayForType(stDate, 'H', holiTblNo,conn))
					{
						System.out.println("Date ["+stDate+"is hliday");
						stDate = new java.sql.Date(stDate.getTime()+(24*60*60*1000));
								
					}
					shiftOfDate = stDate;
				}
					//is jining holiday
					//added by tarani   isHoliday(shiftOfDate, 'H', holiTblNo,conn)
					
				//End by msalam
*/				
				System.out.println("SHIFT_OF_DATE SHIFTDATE FROM ADMCOMMON "+shiftDateNewString);
				if( shiftDateNewString.equalsIgnoreCase("NULLFOUND") || shiftIndex == 0 )
				{
					System.out.println("At 610 ");
					shift = getActualshift(shiftStDate2,frpatternCode,shiftPattern,stDate,noOfDays);
					System.out.println("At 612 Shift:: " + shift);
				} 
				else if(!shiftDateNewString.equalsIgnoreCase("NULLFOUND")&& shiftDateNewString.trim().length()!=0)
				{
					System.out.println("At 615 ");
					if( shiftOfDate == null )
					{
						shiftOfDate = genericUtility.getDateObject(shiftDateNewString);
					}
					System.out.println("Shift Of Date(site_env) :::" +shiftOfDate);	
//					ADDED BY ALKA ON 25/07/07 BLOCK SHIFTED FROM ABOVE 
					if( shiftOfDate.compareTo( joiningDate ) < 0 )
					{
						//added tarani
						stDate = (java.sql.Date)joiningDate;
						//next lines commented by msalam on 250208 for inserting Holidays of types w, h
						//and modified code added
						/*
						while( isHolidayForType( stDate, 'H', holiTblNo, conn ))
						{
							System.out.println("Date ["+stDate+"is hliday");
							stDate = new java.sql.Date( stDate.getTime() + ( 24 * 60 * 60 * 1000 ));
						}
						*/
						holidayType = getHolidayTypeForDate( stDate, holiTblNo, conn );
						java.sql.Date previousDate = stDate;
						while( isHolidayForType( stDate, holidayType, holiTblNo,conn ) && stDate.compareTo( toPrdDate ) <= 0 )
						{
							System.out.println( "From 839" );
							insertIntoWorkShiftForHoliday( empCode, previousDate, stDate, holidayType, frpatternCode, conn );
							previousDate = stDate;
							stDate = new java.sql.Date( stDate.getTime() + ( 24*60*60*1000 ) );
							System.out.println("stDate :: " + stDate);
							holidayType = getHolidayTypeForDate( stDate, holiTblNo, conn );
						} 
						//end by msalam on 250208
						
						shiftOfDate = stDate;
					}
//					ADDITION ENDED BY ALKA 25/07/07
					if( shiftOfDate.after( shiftStDate2 )|| shiftIndex == 0 )
					{
						shift = getActualshift(shiftStDate2,frpatternCode,shiftPattern,stDate,noOfDays);
						System.out.println("At 620 Shift:: " + shift);
					}

					if( ( shiftOfDate.before( shiftStDate2 )||shiftOfDate.equals( shiftStDate2) )&& shiftIndex != 0 )
					{
						java.util.Date effDate =  null ;
						String newPatternCode =  null ;
						//***************tarani if shift event occur thern consider in that
						PreparedStatement pstmtevent = null;
						ResultSet rsevent = null;
						sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+empCode+"'  ORDER BY EFF_DATE DESC ";//jaimin 3
						System.out.println( "sql*********************" + sql );
						pstmtevent = conn.prepareStatement(sql);
						rsevent = pstmtevent.executeQuery();
						if(rsevent.next())
						{
							newPatternCode = rsevent.getString(2);
							effDate  = rsevent.getDate(3);
							System.out.println("effDate["+effDate+"]");
							System.out.println("newPatternCode["+newPatternCode+"]");
						}
						rsevent.close();
						rsevent = null;
						pstmtevent.close();
						pstmtevent = null; 
						sql = "SELECT SHIFT_PATTERN,START_DATE,NO_OF_DAYS FROM SHIFT_PATTERN WHERE PATTERN_CODE = '"+newPatternCode+"'";
						pstmtevent = conn.prepareStatement(sql);
						rsevent = pstmtevent.executeQuery();
						newPatternCode = null;
						if(rsevent.next())
						{
							newPatternCode = rsevent.getString("SHIFT_PATTERN");
						}
						rsevent.close();
						rsevent = null;
						pstmtevent.close();
						pstmtevent = null;
						if(effDate!=null)
						{
							System.out.println("shiftOfDate["+shiftOfDate+"]");
							System.out.println("effDate["+effDate+"]");
							if(shiftStDate2.compareTo(effDate) >=0)
							{
								shiftOfDate= effDate;
								if(newPatternCode!=null)
								shiftPattern = newPatternCode;
							}
						}
						//***************added by tarani if the 
						java.sql.Date shiftOfDate1 = null;
						//shiftOfDate1=(java.sql.Date)shiftOfDate;
						//next lines commented by msalam on 250208 for recording holiday of type h, w
						//and modified lines added
						/*
						while(isHolidayForType((java.util.Date)shiftOfDate, 'H', holiTblNo,conn))
						{
							System.out.println("HoliDay ["+shiftOfDate1+"]");
							shiftOfDate = new java.sql.Date(shiftOfDate.getTime()+(24*60*60*1000));
						}
						*/
						holidayType = getHolidayTypeForDate( (java.util.Date)shiftOfDate, holiTblNo, conn );
						java.util.Date tempShiftOfDate = shiftOfDate;
						java.util.Date previousDate = tempShiftOfDate;
						while( isHolidayForType( tempShiftOfDate, holidayType, holiTblNo,conn ) )
						{
							System.out.println( "From 917" );
							insertIntoWorkShiftForHoliday( empCode, ( new java.sql.Date( previousDate.getTime() ) ),
										( new java.sql.Date( tempShiftOfDate.getTime() ) ), holidayType, frpatternCode, conn );
							stDate = new java.sql.Date(shiftOfDate.getTime()+(24*60*60*1000));
							previousDate = tempShiftOfDate;
							tempShiftOfDate = new java.sql.Date(tempShiftOfDate.getTime()+(24*60*60*1000));
							System.out.println( "In isHolidayForType at 912 tempShiftOfDate :: " + tempShiftOfDate );
							holidayType = getHolidayTypeForDate( tempShiftOfDate, holiTblNo, conn );
						} 
						//end by msalam on 250208

						//shiftOfDate = shiftOfDate1;
						//while(isshiftOfDate)
						shift = getNewShift(shiftStDate2,frpatternCode,shiftPattern,stDate,shiftOfDate,shiftIndex,conn);
						System.out.println("At 626 Shift:: " + shift);
					}
				}
				//added by tarani
				// ADDED  BY DURGA SINGH THAKUR 30/03/2007
				sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+shift+"' AND EMP_CODE = '"+empCode+"'";//QUERY IS COMMENTED  BY DURGA 02/04/07
				//sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+shift+"' AND EMP_CODE = '"+empCode+"' AND TO_DT = ? ";//Query is modified by Durga
				pstmtn6 = conn.prepareStatement(sql);
				pstmtn6.setDate(1, shiftStDate);
				//pstmtn6.setDate(2, shiftStDate2);//ADDED EXTRA PARAMETER LINE BY DURGA 02/04/07
				
				System.out.println("Sql At 939 :: " + sql);
				System.out.println("PARAMETER IN UPDATE QUERY  FROM_DT "+shiftStDate+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT "+shift+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  EMP_CODE "+empCode+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT START DATE "+shiftStDate2+"\n");
				rsin2 = pstmtn6.executeQuery();
				if(rsin2.next())
				{
					cnt = rsin2.getInt(1);
				}
				rsin2.close();
				rsin2 = null;
				pstmtn6.clearParameters();
				pstmtn6.close();
				pstmtn6=null;

				System.out.println("iN INSERT UPDATE Shift : "+shift+" Shift Count :"+cnt);
				if(cnt == 0)
				{
					System.out.println("In insert at 668 insertSql "+insertSql);
					shiftStDate = shiftStDate2;
					pstmtInsert.setString(1,empCode);
					pstmtInsert.setDate(2,shiftStDate);
					pstmtInsert.setDate(3,shiftStDate2);
					pstmtInsert.setString(4,shift);
					pstmtInsert.setString(5,chgUser);
					pstmtInsert.setString(6,chgTerm);
					pstmtInsert.setDate(7,toDay);
					pstmtInsert.setString(8,shift);
					pstmtInsert.setString(9,frpatternCode);
					pstmtInsert.executeUpdate();
					pstmtInsert.clearParameters();
					pstmtInsert.close();
					pstmtInsert=null;
				}
				else if( cnt > 0 )
				{
					System.out.println( "shiftStDate2.compare(shiftStDate) :: (" 
						+ shiftStDate2.toString() + ").compare(" + shiftStDate.toString() + ")=" 
						+ shiftStDate2.compareTo( shiftStDate ) );

					System.out.println("In update at 686 updateSql "+updateSql);

					pstmtUpdate.setDate(1,shiftStDate2);
					pstmtUpdate.setString(2,empCode);
					pstmtUpdate.setDate(3,shiftStDate);
					pstmtUpdate.executeUpdate();
					pstmtUpdate.clearParameters();
					pstmtUpdate.close();
					pstmtUpdate=null;
				}
				if(pstmtInsert!=null)
				{
					pstmtInsert.close();
					pstmtInsert=null;
				}
				if(pstmtUpdate!=null)
				{
					pstmtUpdate.close();
					pstmtUpdate=null;
				}

				shiftStDate2 = new java.sql.Date(shiftStDate2.getTime()+(24*60*60*1000));
				System.gc();
			}//end of while

			//pstmtn5.close();
			//	pstmtn5=null;
			if(stDate != null && stDate.compareTo(shiftStDate) > 0)
			{
				System.out.println("Pattern is not Valid for the Processing Month........");
				errString = itmDBAccessEJB.getErrorString("","VTINVPATRN","","",conn);
				conn.rollback();
				return errString;
			}
		}catch(Exception e){
			System.out.println("Exception "+e);
			e.printStackTrace();
			throw(e);
		}
		finally
		{
			try
			{
				if(rsin1!=null)
				{
					rsin1.close();
					rsin1=null;
				}
				if(rsin2!=null)
				{
					rsin2.close();
					rsin2=null;
				}
				if(stmt != null){
					stmt.close();
					stmt = null;
				}
				if(pstmtInsert != null){
					pstmtInsert.close();
					pstmtInsert = null;
				}
				if(pstmtUpdate != null){
					pstmtUpdate.close();
					pstmtUpdate = null;
				}
				if(pstmtn6 != null){
					pstmtn6.close();
					pstmtn6 = null;
				}
				if(pstmtEmp != null){
					pstmtEmp.close();
					pstmtEmp = null;
				}
				if(rsEmp != null){
					rsEmp.close();
					rsEmp = null;
				}
			}
			catch(Exception e)
			{e.printStackTrace();}
			System.gc();
		}
		return "";
	}//end of insertUpdateWorkShift


//start - Overload - function added by jaimin on 16/06/2008 (AD89SUN014)
	private String insertUpdateWorkShift(EmpInfo empInfo, String empCode, java.sql.Date shiftStDate, java.sql.Date shiftEndDate, String frpatternCode, String topatterenCode, String holiTblNo,int shiftIndexNew, Connection conn) throws Exception
	{
		String insertSql ="",updateSql = "",errString = "";
		PreparedStatement pstmtn5 = null,pstmtn6=null,pstmtInsert = null,pstmtUpdate = null;
		PreparedStatement pstmtEmp = null;
		Statement stmt = null;
		ResultSet rsin1 = null, rsin2=null, rsEmp=null;
		String shiftPattern = "";
		String shift = "";
		String cadre = "", grade="", siteCode="";
		int noOfDays = 0;
		int cnt =0, shiftIndex = 0 ;
		int t = 0 ;
		java.sql.Date stDate = null;
		java.util.Date shiftOfDate =  null ;
 		java.util.Date joiningDate =  null;

		try{
			stmt = conn.createStatement();
			if(frpatternCode == null)
			{
				errString = itmDBAccessEJB.getErrorString("","VTPATTERNU","");
				return errString ;
			}
			sql = "SELECT SHIFT_PATTERN,START_DATE,NO_OF_DAYS FROM SHIFT_PATTERN WHERE PATTERN_CODE = '"+frpatternCode+"'";
			rsin1 = stmt.executeQuery(sql);
			if(rsin1.next())
			{
				shiftPattern = rsin1.getString( "SHIFT_PATTERN" );
				stDate = rsin1.getDate( "START_DATE" );
				noOfDays = rsin1.getInt( "NO_OF_DAYS" );
			}
			rsin1.close();
			rsin1 = null;

			stmt.close();
			stmt=null;

			System.out.println("frpatternCode :"+frpatternCode);
			System.out.println("shiftPattern :"+shiftPattern+" stDate :"+stDate+" shiftStDate :"+shiftStDate);

			insertSql = "INSERT INTO WORK_SHIFT (EMP_CODE,FROM_DT,TO_DT,SHIFT,CHG_USER,CHG_TERM,CHG_DATE,REAL_SHIFT,PATTERN_CODE) "
				+" Values (?,?,?,?,?,?,?,?,?)";

			updateSql = "UPDATE WORK_SHIFT SET TO_DT = ? WHERE EMP_CODE = ? AND FROM_DT = ?";

			java.sql.Date shiftStDate2 = shiftStDate;
			System.out.println("shiftStDate2 : " + shiftStDate2 + " shiftEndDate : " + shiftEndDate);

			while( shiftStDate2.compareTo( shiftEndDate ) < 0)
			{
				shiftOfDate = null;
				//Updated by nisar on 21/02/06

				//added by msalam on 23/05/07
				//added by msalam on 25/02/08 for inserting record of Holiday type W
				String holidayType = null;
				//getHolidayTypeForDate( java.util.Date shiftDate, String holiTblNo, Connection conn)
				holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );
				//added by msalam on 25/02/08 for inserting record of Holiday type W
				//next line commented and modified line added by msalam on 25/02/08
				//while(isHolidayForType(shiftStDate2, 'H', holiTblNo,conn))
				
				while( isHolidayForType(shiftStDate2, holidayType, holiTblNo,conn) && shiftStDate2.compareTo( toPrdDate ) <= 0 )
				{
					//added by nisar on 17/12/07 for requestId : AD7SUN0016
					//next line commented and modified line added by msalam on 25/02/08
					//insertIntoWorkShiftForHoliday(empCode,shiftStDate2,shiftStDate2,"H",frpatternCode,conn);
					System.out.println( "From 720" );
					insertIntoWorkShiftForHoliday( empCode, shiftStDate, shiftStDate2, holidayType, frpatternCode, conn );
					//code ended by nisar on 17/12/07   for requestId : AD7SUN0016
					shiftStDate = shiftStDate2;
					shiftStDate2 = new java.sql.Date(shiftStDate2.getTime()+(24*60*60*1000));
					System.out.println("shiftStDate2 :: " + shiftStDate2);
					holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );
				} 

				if( ! (shiftStDate2.compareTo(shiftEndDate) < 0 ))
				{
					System.out.println("In Continue..");
					continue;
				}
				//End of msalam

				pstmtInsert = conn.prepareStatement(insertSql);
				//pstmtn5 =conn.prepareStatement(sql);
				pstmtUpdate = conn.prepareStatement(updateSql);
				//ended by nisar on 21/02/06
				// ADDED  BY DURGA SINGH THAKUR 30/03/2007
				//no need of this as all the information is coming in empInfo bean by msalam on 170308
				/*
				sql = " SELECT CADRE, GRADE, WORK_SITE, SHIFT_INDEX, DATE_JOIN FROM EMPLOYEE WHERE EMP_CODE= ? ";
				pstmtEmp =  conn.prepareStatement(sql);
				pstmtEmp.setString(1, empCode);
				rsEmp = pstmtEmp.executeQuery();
				if(rsEmp.next())
				{
					cadre = rsEmp.getString("CADRE");
					grade = rsEmp.getString("GRADE");
					siteCode = rsEmp.getString("WORK_SITE");
					shiftIndex = rsEmp.getInt("SHIFT_INDEX");
					joiningDate = rsEmp.getDate("DATE_JOIN");
				}
				System.out.println("At 589 Shift_Index :: " + shiftIndex);
				System.out.println("joiningDate :: " + joiningDate);
				System.out.println("shiftStDate2 :: " + shiftStDate2);
				
				if(rsEmp != null)
				{
					rsEmp.close();
					rsEmp = null;
					pstmtEmp.close();
					pstmtEmp=null;
				}
				*/
				//end no need of this as all the information is coming in empInfo bean by msalam on 170308
				cadre = empInfo.cadre;
				grade = empInfo.grade;
				siteCode = empInfo.workSite;
				//shiftIndex = empInfo.shiftIndex; //Commented by jaimin on 16/06/2008 (AD89SUN014)
				shiftIndex = shiftIndexNew;//Added by jaimin on 16/06/2008 (AD89SUN014)
				joiningDate = empInfo.dateJoin;

				//added tarani
				
				if(shiftStDate2.compareTo(joiningDate)< 0)
				{
					shiftStDate2 = (java.sql.Date)joiningDate;
					System.out.println("Calculation Change to Joining Date ["+shiftStDate2+"]");
				}
				
				String shiftDateNewString = admCommon.getAdmEnv(siteCode, cadre, grade,"SHIFT_OF_DATE", new java.sql.Timestamp(System.currentTimeMillis()), conn);
				/*
				COMMENTED BY ALKA 25/07/07 AND MOVED THE BLOCK IN THE IF CONDITION JUST BELOW 
				//Added by msalam on 18/06/07 as told by SUBARTA
				//if shift_of_date < Date_join take stDate = join_date
				java.util.Date tShiftOfDate = null;
				tShiftOfDate = genericUtility.getDateObject(shiftDateNewString);
				System.out.println("Shift Of Date(site_env) :::" +tShiftOfDate);		
				if(tShiftOfDate.compareTo(joiningDate) < 0)
				{
					//added tarani
					stDate = (java.sql.Date)joiningDate;
					
					while(isHolidayForType(stDate, 'H', holiTblNo,conn))
					{
						System.out.println("Date ["+stDate+"is hliday");
						stDate = new java.sql.Date(stDate.getTime()+(24*60*60*1000));
								
					}
					shiftOfDate = stDate;
				}
					//is jining holiday
					//added by tarani   isHoliday(shiftOfDate, 'H', holiTblNo,conn)
					
				//End by msalam
*/				
				System.out.println("SHIFT_OF_DATE SHIFTDATE FROM ADMCOMMON "+shiftDateNewString);
				if( shiftDateNewString.equalsIgnoreCase("NULLFOUND") || shiftIndex == 0 )
				{
					System.out.println("At 610 ");
					shift = getActualshift(shiftStDate2,frpatternCode,shiftPattern,stDate,noOfDays);
					System.out.println("At 612 Shift:: " + shift);
				} 
				else if(!shiftDateNewString.equalsIgnoreCase("NULLFOUND")&& shiftDateNewString.trim().length()!=0)
				{
					System.out.println("At 615 ");
					if( shiftOfDate == null )
					{
						shiftOfDate = genericUtility.getDateObject(shiftDateNewString);
					}
					System.out.println("Shift Of Date(site_env) :::" +shiftOfDate);	
//					ADDED BY ALKA ON 25/07/07 BLOCK SHIFTED FROM ABOVE 
					if( shiftOfDate.compareTo( joiningDate ) < 0 )
					{
						//added tarani
						stDate = (java.sql.Date)joiningDate;
						//next lines commented by msalam on 250208 for inserting Holidays of types w, h
						//and modified code added
						/*
						while( isHolidayForType( stDate, 'H', holiTblNo, conn ))
						{
							System.out.println("Date ["+stDate+"is hliday");
							stDate = new java.sql.Date( stDate.getTime() + ( 24 * 60 * 60 * 1000 ));
						}
						*/
						holidayType = getHolidayTypeForDate( stDate, holiTblNo, conn );
						java.sql.Date previousDate = stDate;
						while( isHolidayForType( stDate, holidayType, holiTblNo,conn ) && stDate.compareTo( toPrdDate ) <= 0 )
						{
							System.out.println( "From 839" );
							insertIntoWorkShiftForHoliday( empCode, previousDate, stDate, holidayType, frpatternCode, conn );
							previousDate = stDate;
							stDate = new java.sql.Date( stDate.getTime() + ( 24*60*60*1000 ) );
							System.out.println("stDate :: " + stDate);
							holidayType = getHolidayTypeForDate( stDate, holiTblNo, conn );
						} 
						//end by msalam on 250208
						
						shiftOfDate = stDate;
					}
//					ADDITION ENDED BY ALKA 25/07/07
					if( shiftOfDate.after( shiftStDate2 )|| shiftIndex == 0 )
					{
						shift = getActualshift(shiftStDate2,frpatternCode,shiftPattern,stDate,noOfDays);
						System.out.println("At 620 Shift:: " + shift);
					}

					if( ( shiftOfDate.before( shiftStDate2 )||shiftOfDate.equals( shiftStDate2) )&& shiftIndex != 0 )
					{
						java.util.Date effDate =  null ;
						String newPatternCode =  null ;
						//***************tarani if shift event occur thern consider in that
						PreparedStatement pstmtevent = null;
						ResultSet rsevent = null;
						sql = "SELECT PATTERN_CODE__FR,PATTERN_CODE__TO,EFF_DATE FROM EMP_EMPLOY_EVENTS WHERE EMP_CODE = '"+empCode+"'  ORDER BY EFF_DATE DESC ";//jaimin 3
						System.out.println( "sql*********************" + sql );
						pstmtevent = conn.prepareStatement(sql);
						rsevent = pstmtevent.executeQuery();
						if(rsevent.next())
						{
							newPatternCode = rsevent.getString(2);
							effDate  = rsevent.getDate(3);
							System.out.println("effDate["+effDate+"]");
							System.out.println("newPatternCode["+newPatternCode+"]");
						}
						rsevent.close();
						rsevent = null;
						pstmtevent.close();
						pstmtevent = null; 
						sql = "SELECT SHIFT_PATTERN,START_DATE,NO_OF_DAYS FROM SHIFT_PATTERN WHERE PATTERN_CODE = '"+newPatternCode+"'";
						pstmtevent = conn.prepareStatement(sql);
						rsevent = pstmtevent.executeQuery();
						newPatternCode = null;
						if(rsevent.next())
						{
							newPatternCode = rsevent.getString("SHIFT_PATTERN");
						}
						rsevent.close();
						rsevent = null;
						pstmtevent.close();
						pstmtevent = null;
						if(effDate!=null)
						{
							System.out.println("shiftOfDate["+shiftOfDate+"]");
							System.out.println("effDate["+effDate+"]");
							if(shiftStDate2.compareTo(effDate) >=0)
							{
								shiftOfDate= effDate;
								if(newPatternCode!=null)
								shiftPattern = newPatternCode;
							}
						}
						//***************added by tarani if the 
						java.sql.Date shiftOfDate1 = null;
						//shiftOfDate1=(java.sql.Date)shiftOfDate;
						//next lines commented by msalam on 250208 for recording holiday of type h, w
						//and modified lines added
						/*
						while(isHolidayForType((java.util.Date)shiftOfDate, 'H', holiTblNo,conn))
						{
							System.out.println("HoliDay ["+shiftOfDate1+"]");
							shiftOfDate = new java.sql.Date(shiftOfDate.getTime()+(24*60*60*1000));
						}
						*/
						holidayType = getHolidayTypeForDate( (java.util.Date)shiftOfDate, holiTblNo, conn );
						java.util.Date tempShiftOfDate = shiftOfDate;
						java.util.Date previousDate = tempShiftOfDate;
						while( isHolidayForType( tempShiftOfDate, holidayType, holiTblNo,conn ) )
						{
							System.out.println( "From 917" );
							insertIntoWorkShiftForHoliday( empCode, ( new java.sql.Date( previousDate.getTime() ) ),
										( new java.sql.Date( tempShiftOfDate.getTime() ) ), holidayType, frpatternCode, conn );
							stDate = new java.sql.Date(shiftOfDate.getTime()+(24*60*60*1000));
							previousDate = tempShiftOfDate;
							tempShiftOfDate = new java.sql.Date(tempShiftOfDate.getTime()+(24*60*60*1000));
							System.out.println( "In isHolidayForType at 912 tempShiftOfDate :: " + tempShiftOfDate );
							holidayType = getHolidayTypeForDate( tempShiftOfDate, holiTblNo, conn );
						} 
						//end by msalam on 250208

						//shiftOfDate = shiftOfDate1;
						//while(isshiftOfDate)
						shift = getNewShift(shiftStDate2,frpatternCode,shiftPattern,stDate,shiftOfDate,shiftIndex,conn);
						System.out.println("At 626 Shift:: " + shift);
					}
				}
				//added by tarani
				// ADDED  BY DURGA SINGH THAKUR 30/03/2007
				sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+shift+"' AND EMP_CODE = '"+empCode+"'";//QUERY IS COMMENTED  BY DURGA 02/04/07
				//sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+shift+"' AND EMP_CODE = '"+empCode+"' AND TO_DT = ? ";//Query is modified by Durga
				pstmtn6 = conn.prepareStatement(sql);
				pstmtn6.setDate(1, shiftStDate);
				//pstmtn6.setDate(2, shiftStDate2);//ADDED EXTRA PARAMETER LINE BY DURGA 02/04/07
				
				System.out.println("Sql At 939 :: " + sql);
				System.out.println("PARAMETER IN UPDATE QUERY  FROM_DT "+shiftStDate+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT "+shift+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  EMP_CODE "+empCode+"\n");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT START DATE "+shiftStDate2+"\n");
				rsin2 = pstmtn6.executeQuery();
				if(rsin2.next())
				{
					cnt = rsin2.getInt(1);
				}
				rsin2.close();
				rsin2 = null;
				pstmtn6.clearParameters();
				pstmtn6.close();
				pstmtn6=null;

				System.out.println("iN INSERT UPDATE Shift : "+shift+" Shift Count :"+cnt);
				if(cnt == 0)
				{
					System.out.println("In insert at 668 insertSql "+insertSql);
					shiftStDate = shiftStDate2;
					pstmtInsert.setString(1,empCode);
					pstmtInsert.setDate(2,shiftStDate);
					pstmtInsert.setDate(3,shiftStDate2);
					pstmtInsert.setString(4,shift);
					pstmtInsert.setString(5,chgUser);
					pstmtInsert.setString(6,chgTerm);
					pstmtInsert.setDate(7,toDay);
					pstmtInsert.setString(8,shift);
					pstmtInsert.setString(9,frpatternCode);
					pstmtInsert.executeUpdate();
					pstmtInsert.clearParameters();
					pstmtInsert.close();
					pstmtInsert=null;
				}
				else if( cnt > 0 )
				{
					System.out.println( "shiftStDate2.compare(shiftStDate) :: (" 
						+ shiftStDate2.toString() + ").compare(" + shiftStDate.toString() + ")=" 
						+ shiftStDate2.compareTo( shiftStDate ) );

					System.out.println("In update at 686 updateSql "+updateSql);

					pstmtUpdate.setDate(1,shiftStDate2);
					pstmtUpdate.setString(2,empCode);
					pstmtUpdate.setDate(3,shiftStDate);
					pstmtUpdate.executeUpdate();
					pstmtUpdate.clearParameters();
					pstmtUpdate.close();
					pstmtUpdate=null;
				}
				if(pstmtInsert!=null)
				{
					pstmtInsert.close();
					pstmtInsert=null;
				}
				if(pstmtUpdate!=null)
				{
					pstmtUpdate.close();
					pstmtUpdate=null;
				}

				shiftStDate2 = new java.sql.Date(shiftStDate2.getTime()+(24*60*60*1000));
				System.gc();
			}//end of while

			//pstmtn5.close();
			//	pstmtn5=null;
			if(stDate != null && stDate.compareTo(shiftStDate) > 0)
			{
				System.out.println("Pattern is not Valid for the Processing Month........");
				errString = itmDBAccessEJB.getErrorString("","VTINVPATRN","","",conn);
				conn.rollback();
				return errString;
			}
		}catch(Exception e){
			System.out.println("Exception "+e);
			e.printStackTrace();
			throw(e);
		}
		finally
		{
			try
			{
				if(rsin1!=null)
				{
					rsin1.close();
					rsin1=null;
				}
				if(rsin2!=null)
				{
					rsin2.close();
					rsin2=null;
				}
				if(stmt != null){
					stmt.close();
					stmt = null;
				}
				if(pstmtInsert != null){
					pstmtInsert.close();
					pstmtInsert = null;
				}
				if(pstmtUpdate != null){
					pstmtUpdate.close();
					pstmtUpdate = null;
				}
				if(pstmtn6 != null){
					pstmtn6.close();
					pstmtn6 = null;
				}
				if(pstmtEmp != null){
					pstmtEmp.close();
					pstmtEmp = null;
				}
				if(rsEmp != null){
					rsEmp.close();
					rsEmp = null;
				}
			}
			catch(Exception e)
			{e.printStackTrace();}
			System.gc();
		}
		return "";
	}//end of insertUpdateWorkShift
//End - Overload - function added by jaimin on 16/06/2008 (AD89SUN014)

	private String getActualshift(java.util.Date currDate,String patternCode,String shiftPattern,java.sql.Date shiftStDate,int noOfDays)
	{
		long timeDiff = 0;
		int noDays = 0;
		ArrayList patternList = new ArrayList();
		String patString ="",day ="",shift ="";
		System.out.println("\n\n Pase Value............");
		System.out.println("\n\n currDate[ "+currDate+"]");
		System.out.println("\n\n patternCode "+patternCode);
		System.out.println("\n\n shiftPattern "+shiftPattern);
		System.out.println("\n\n shiftStDate "+shiftStDate);

		try{
			patternList = GenericUtility.getInstance().getTokenList(shiftPattern,";");
			timeDiff = currDate.getTime() - shiftStDate.getTime();
			timeDiff = (timeDiff/(60*60*24*1000))+1;
			noDays = new Long(timeDiff).intValue();
			System.out.println("noOfDays :"+noOfDays+"noDays :"+noDays);
			while(noDays > noOfDays)
			{
				noDays = noDays - noOfDays;
			}
			System.out.println("noOfDays :"+noOfDays+"noDays :"+noDays);
			System.out.println("patternList : "+patternList);
			for(int i = 0;i < patternList.size()-1;i++){
				patString = patternList.get(i).toString();
				System.out.println("patString : "+patString);
				System.out.println("patString.indexOf('-') : "+patString.indexOf('-'));
				day = patString.substring(0,patString.indexOf('-'));
				shift = patString.substring(patString.indexOf('-')+1);
				if(noDays <= Integer.parseInt(day)){
					break;
				}
				else{
					noDays = noDays - Integer.parseInt(day);
				}
			}
			System.out.println("For currDate : "+currDate+" Shift : "+shift);
			patternList.clear();
		}catch(Exception e){
			System.out.println("Exception "+e);
			e.printStackTrace();
		}
		return shift;
	}
	// ADDED  BY DURGA SINGH THAKUR 30/03/2007
	private String getNewShift(java.sql.Date fromDate, String patternCode,String shiftPattern, java.sql.Date shiftStDate,java.util.Date shiftOfDate,int shiftIndex, Connection conn)
	{
		long timeDiff = 0;
		int noDays = 0;
		String sql = "";
		//int shiftIndex =0 ;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String cadre = "",grade = "",siteCode = "";
		ArrayList patternList = new ArrayList();
		String patString ="",day ="",shift ="";
		java.sql.Date shiftDateNew = shiftStDate;
		ArrayList shiftTime = new ArrayList();
		ArrayList  shiftTime1 =  new ArrayList();
		String shiftNew = "";
		System.out.println("\n\n PASSED Parameter...");
		System.out.println("\n\n  SHIFTINDEX ["+shiftIndex+"]");
		System.out.println("\n\n  CURRDATE "+fromDate+"]");
		System.out.println("\n\n  PATTERNCODE ["+patternCode+"]");
		System.out.println("\n\n  SHIFTPATTERN ["+shiftPattern+"]");
		System.out.println("\n\n  SHIFTSTDATE ["+shiftStDate+"]");
		long numberOfDay = 0 ;

		try
		{
			patternList = GenericUtility.getInstance().getTokenList(shiftPattern,";");
			System.out.println("\n\n\n\n PatternList(Original) "+patternList);
			for(int j = 0 ; j < patternList.size()-1 ; j++)
			{
				patString = patternList.get(j).toString();
				day = patString.substring(0,patString.indexOf('-'));
				shift = patString.substring(patString.indexOf('-')+1);
				for(int k = 0; k < Integer.parseInt(day); k++)
				{
					shiftTime.add(shift);
				}
			}

			shiftNew =(String)shiftTime.get(shiftIndex-1);
			System.out.println("\n\n NEW SHIFT IN SHIFT DATE "+shiftNew+"\n\n");

			int j = 0,i = 0;

			for( i = shiftIndex-1 ; i<shiftTime.size(); i++ )
			{
				shiftTime1.add( shiftTime.get(shiftIndex-1+j));
				j++;
			}
			for(i = 0; i < shiftIndex-1; i++)
			{
				shiftTime1.add(shiftTime.get(i));
			}
			/*for(i= 0; i< shiftTime1.size(); i++)
			{
				System.out.println("POSITION "+i+" "+shiftTime1.get(i));
			}*/

			System.out.println("\n\n change patternList: "+shiftTime1+"********");
			timeDiff = fromDate.getTime() - shiftOfDate.getTime();
			timeDiff = (timeDiff/(60*60*24*1000));
			noDays = new Long(timeDiff).intValue();
			noDays =  Math.abs(noDays);

			System.out.println("\n\n NUMBER OF DAYS BETWEEN DATE__FROM AND SHIFTOFDATE IS  ["+noDays+"]");
			j = shiftTime1.size();
			System.out.println("\n\n SIZE OF ARRAYLIST SHIFTTIME IS J :["+j+"]");
			while( noDays >= j)
			{
				noDays = noDays%j ;
			}
			System.out.println("\n\n INDEX OF FETCHING IS :["+noDays+"]");
			shiftNew = (String)shiftTime1.get(noDays).toString();
			System.out.println("\n\n SHIFT INDEX AT FROM DATE "+fromDate+" is "+shiftNew);

		}
		catch(Exception e)
		{
			System.out.println("Exception "+e);
			e.printStackTrace();
		}
		finally
		{
			shiftTime.clear();
			shiftTime1.clear();
			shiftTime = null;
			shiftTime1 = null;
			System.gc();
		}
		return shiftNew;
	} // ADDED  BY DURGA SINGH THAKUR 30/03/2007
	private String getOldShift(java.sql.Date fromDate, String patternCode,String shiftPattern, java.sql.Date shiftStDate,java.util.Date shiftOfDate,int shiftIndex, Connection conn)
	{
		long timeDiff = 0;
		int noDays = 0;
		String sql = "";
		//int shiftIndex =0 ;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String cadre = "",grade = "",siteCode = "";
		ArrayList patternList = new ArrayList();
		String patString ="",day ="",shift ="";
		//java.sql.Date shiftDateNew = shiftStDate;
		ArrayList shiftTime = new ArrayList();
		ArrayList  shiftTime1 =  new ArrayList();
		String shiftNew = "";
		System.out.println("\n\n PASSED VALUE SHIFTINDEX "+shiftIndex);
		System.out.println("\n\n PASSED VALUE CURRDATE "+fromDate);
		System.out.println("\n\n PASSED VALUE PATTERNCODE "+patternCode);
		System.out.println("\n\n PASSED VALUE SHIFTPATTERN "+shiftPattern);
		System.out.println("\n\n PASSED VALUE SHIFTSTDATE "+shiftStDate);
		long numberOfDay = 0 ;

		try
		{

			patternList = GenericUtility.getInstance().getTokenList(shiftPattern,";");

			System.out.println("\n\n\n\n******PatternList "+patternList);

			for(int j = 0 ; j < patternList.size()-1 ; j++)
			{
				patString = patternList.get(j).toString();
				day = patString.substring(0,patString.indexOf('-'));
				shift = patString.substring(patString.indexOf('-')+1);
				for(int k = 0; k < Integer.parseInt(day); k++)
				{
					shiftTime.add(shift);
				}
			}

			shiftNew =(String)shiftTime.get(shiftIndex-1);
			System.out.println("\n\n NEW SHIFT IN SHIFT DATE "+shiftNew+"\n\n");

			int j = 0,i = 0;

			for( i = shiftIndex-1 ; i<shiftTime.size(); i++ )
			{
				shiftTime1.add( shiftTime.get(shiftIndex-1+j));
				j++;
			}
			for(i = 0; i < shiftIndex-1; i++)
			{
				shiftTime1.add(shiftTime.get(i));
			}
			for(i= 0; i< shiftTime1.size(); i++)
			{
				System.out.println("POSITION "+i+" "+shiftTime1.get(i));
			}

			System.out.println("\n Pattern start from Shift index ["+shiftTime1+"]");
			timeDiff = fromDate.getTime() - shiftOfDate.getTime();
			timeDiff = (timeDiff/(60*60*24*1000));
			noDays = new Long(timeDiff).intValue();
			noDays =  Math.abs(noDays);

			System.out.println("\n\n NUMBER OF DAYS BETWEEN DATE__FROM AND SHIFTOFDATE IS  "+noDays);
			j = shiftTime1.size();
			System.out.println("\n\n SIZE OF ARRAYLIST SHIFTTIME IS J :"+j);
			while( noDays >= j)
			{
				noDays = noDays%j ;
			}
			System.out.println("\n\n INDEX OF FETCHING IS :"+noDays);
			shiftNew = (String)shiftTime1.get(noDays).toString();
			System.out.println("\n\n SHIFT INDEX AT FROM DATE "+fromDate+" is "+shiftNew);

		}
		catch(Exception e)
		{
			System.out.println("Exception "+e);
			e.printStackTrace();
		}
		finally
		{
			shiftTime.clear();
			shiftTime1.clear();
			shiftTime = null;
			shiftTime1 = null;
			System.gc();
		}
		return shiftNew;
	} // ADDED  BY DURGA SINGH THAKUR 30/03/2007

	// ADDED BY Shoiab on 23/05/07
	private boolean isHolidayForType(java.util.Date shiftDate, String holidayType,String holiTblNo,Connection conn)
	{
		Timestamp ts  = new Timestamp(shiftDate.getTime()) ;
		String sqlStr = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;		int cnt = 0;
		System.out.println("Shift date isHoliday :: " + shiftDate);
		System.out.println("Hol Tupe in isHoliday :: " + holidayType);
		try{
			sqlStr = "select count(*) from holiday where Hol_Date = ? and Hol_Type = '" + holidayType + "' and HOL_TBLNO ='"+holiTblNo+"'";
			stmt = conn.prepareStatement(sqlStr);
			//sqlStr = "select count(*) from holiday where Hol_Date = to_date('" + shiftDate + "', 'yyyy-MM-dd') and Hol_Type = '" + holidayType + "'";
			stmt.setTimestamp(1, ts);
			System.out.println("Setting Timestamp:["+ts+"]");
			System.out.println("Sql Str in isHoliday :: " + sqlStr);
			rs = stmt.executeQuery();
			if(rs.next())
				cnt = rs.getInt(1);
			System.out.println("Cnt in isHoliday :: " + cnt);
			rs.close();
			rs = null;
			stmt.close();
			stmt=null;
		}catch(Exception e){
			System.out.println("Exception " + e);
			e.printStackTrace();
		}
		finally{
			try
			{
				if(rs!=null)
					{
						rs.close();
						rs = null;
					}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			}catch(Exception e){}
		}
		return cnt >= 1;
	}
	private void insertIntoWorkShiftForHoliday(String empCode,java.sql.Date shiftStDate,
	java.sql.Date shiftStDate2,String shift,String frpatternCode,Connection conn)
	{//ADDED BY NISAR ON 17/12/07 FOR REQUERST ID : AD7SUN0016
		PreparedStatement pstmtInsert = null, pstmtn6=null, pstmtUpdate=null;
		ResultSet rsin2 = null;
		String sql = null;
		String insertSql = null;
		String updateSql = null;
		int cnt = 0;
		
		SimpleDateFormat sdf = null;

		try
		{
			sdf = new SimpleDateFormat( genericUtility.getApplDateFormat() );
			
			insertSql = "INSERT INTO WORK_SHIFT (EMP_CODE,FROM_DT,TO_DT,SHIFT,CHG_USER,CHG_TERM,CHG_DATE,REAL_SHIFT,PATTERN_CODE) "
				+" Values (?,?,?,?,?,?,?,?,?)";

			updateSql = "UPDATE WORK_SHIFT SET TO_DT = ? WHERE EMP_CODE = ? AND FROM_DT = ?";

			sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '" + shift + "' AND EMP_CODE = '"+empCode+"'";
			pstmtn6 = conn.prepareStatement(sql);
			//next line added by msalam on 28/02/07 and modified line added.
			pstmtn6.setDate( 1, shiftStDate );
			//end next line added by msalam on 28/02/07 and modified line added.
			//pstmtn6.setDate(2, shiftStDate2);//ADDED EXTRA PARAMETER LINE BY DURGA 02/04/07
			System.out.println("Sql At 1365 :: " + sql);
			System.out.println("PARAMETER IN UPDATE QUERY  FROM_DT "+shiftStDate+"\n");
			System.out.println("PARAMETER IN UPDATE QUERY  SHIFT "+shift+"\n");
			System.out.println("PARAMETER IN UPDATE QUERY  EMP_CODE "+empCode+"\n");
			System.out.println("PARAMETER IN UPDATE QUERY  SHIFT START DATE "+shiftStDate2+"\n");
			rsin2 = pstmtn6.executeQuery();
			if(rsin2.next())
			{
				cnt = rsin2.getInt(1);
			}
			rsin2.close();
			rsin2 = null;
			pstmtn6.clearParameters();
			pstmtn6.close();
			pstmtn6=null;
			
			System.out.println( "shiftStDate2.compare(shiftStDate) :: (" 
					+ shiftStDate2.toString() + ").compare(" + shiftStDate.toString() + ")=" 
					+ shiftStDate2.compareTo( shiftStDate ) );


			System.out.println("Shift : "+shift+" Shift Count :"+cnt);
			if(cnt == 0)
			{	 
				pstmtInsert = conn.prepareStatement(insertSql);
				System.out.println("In insert at 668 insertSql "+insertSql);
				shiftStDate = shiftStDate2;
				pstmtInsert.setString( 1, empCode );
				pstmtInsert.setDate( 2, shiftStDate );
				pstmtInsert.setDate( 3, shiftStDate2 );
				pstmtInsert.setString( 4, shift );
				pstmtInsert.setString( 5, chgUser );
				pstmtInsert.setString( 6, chgTerm );
				//next line changed by msalam and modified line added on 280207
				pstmtInsert.setTimestamp(  7, new java.sql.Timestamp( System.currentTimeMillis() ) );
				java.util.Date chgDate = null;
				chgDate = new java.util.Date( System.currentTimeMillis() );
				//pstmtInsert.setTimestamp(  7, new java.sql.Timestamp( (sdf.parse( chgDate.toString() )).getTime() ) );
				//end next line changed by msalam and modified line added on 280207
				pstmtInsert.setString( 8, shift);
				pstmtInsert.setString( 9, frpatternCode );
				pstmtInsert.executeUpdate();
				pstmtInsert.clearParameters();
				pstmtInsert.close();
				pstmtInsert=null;
			}
			//else if( cnt > 0 && shiftStDate2.compareTo( shiftStDate ) != 0 )
			else if( cnt > 0 )
			{
				System.out.println( "In insertIntoWorkShiftForHoliday shiftStDate2.compare(shiftStDate) :: (" 
									+ shiftStDate2.toString() + ").compare(" + shiftStDate.toString() + ")=" 
									+ shiftStDate2.compareTo( shiftStDate ) );

				System.out.println( "In update at 686 updateSql " + updateSql );
				pstmtUpdate = conn.prepareStatement(updateSql);
				pstmtUpdate.setDate(1,shiftStDate2);
				pstmtUpdate.setString(2,empCode);
				pstmtUpdate.setDate(3,shiftStDate);
				pstmtUpdate.executeUpdate();
				//pstmtUpdate.clearParameters();
				pstmtUpdate.close();
				pstmtUpdate=null;
			}
			if(pstmtInsert!=null)
			{
				pstmtInsert.close();
				pstmtInsert=null;
			}
			if(pstmtUpdate!=null)
			{
				pstmtUpdate.close();
				pstmtUpdate=null;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}//end of insertIntoWorkShiftForHoliday()//added by nisar on 17/12/07 
	//ADDED BY MSALAM ON 250208 TO GET HOLIDAY DAY TYPE
	private String getHolidayTypeForDate( java.util.Date shiftDate, String holiTblNo, Connection conn)
	{
		Timestamp ts  = new Timestamp(shiftDate.getTime()) ;
		String sqlStr = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String holType = null;
		System.out.println("Holiday tyoe for date :: " + shiftDate);
		try{
			//sqlStr = "select Hol_Type from holiday where Hol_Date = ? and HOL_TBLNO ='" + holiTblNo + "'";//Commentd by jaimin on 17/06/2008 (AD89SUN016)
			sqlStr = "select Hol_Type from holiday where Hol_Date = ? and HOL_TBLNO ='" + holiTblNo + "' and HOL_TYPE <> 'W' "; //Added by jaimin on 17/06/2008 (AD89SUN016)
			stmt = conn.prepareStatement( sqlStr );
			stmt.setTimestamp(1, ts);
			System.out.println("Holiday Type Setting Timestamp:["+ts+"]");
			System.out.println("Sql Str for Holiday Holiday Type :: " + sqlStr);
			rs = stmt.executeQuery();
			if( rs.next() )
			{
				holType = rs.getString( "Hol_Type" );
			}
			
			System.out.println( "Hol Type Holiday :: " + holType );
			rs.close();
			rs = null;
			stmt.close();
			stmt=null;
		}catch(Exception e){
			System.out.println("Exception " + e);
			e.printStackTrace();
		}
		finally{
			try
			{
				if(rs!=null)
					{
						rs.close();
						rs = null;
					}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
			}catch(Exception e){}
		}
		return ( holType == null ? "" : holType );
	}
	//END MSALAM ON 250208
	//Bean class added by msalam on 170308
	private class EmpInfo{
		public String empCode = null;
		public String empFname = null;
		public String empLname = null;
		public String workShift = null;
		public String shiftRot = null;
		public String holTblno = null;
		public String patternCode = null;
		public String workSite = null;
		public java.sql.Date fromDt = null;
		public String cadre = null;
		public String grade = null;
		public int shiftIndex = 0;
		public java.util.Date dateJoin = null;
	}
	//end bean class added by msalam on 170308
}