/*
    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.*;
//Changed by Dhiraj req ID: AD01SUN008 [05/10/10].start
import javax.naming.InitialContext;

import javax.ejb.Stateless; 

@Stateless
//public class ShiftProcessEJB extends ProcessEJB implements SessionBean

public class ShiftProcess extends ProcessEJB implements ShiftProcessLocal,ShiftProcessRemote
//Changed by Dhiraj req ID: AD01SUN008 [05/10/10].end
{
	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();

	GenericUtility genericUtility = GenericUtility.getInstance();  
	

	CommonConstants commonConstants = new CommonConstants();

	String chgUser = null;
	String chgTerm = null;

	//Changed by Dhiraj req ID: AD01SUN008 [05/10/10].start
	//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
  	/*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()
	{
	}
	*/
	//Changed by Dhiraj req ID: AD01SUN008 [05/10/10].end
	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() + ":");
			//changed by Dhiraj 05/10/10 [AD01SUN008] - Exception should throw
			//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)
		//Changed by Dhiraj 06/10/10 [AD01SUN008]
		ITMDBAccessLocal itmdbAccessLocal = null;
		
		try
		{
			//Changed by Dhiraj 06/10/10 [AD01SUN008].start

			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			itmdbAccessLocal = (ITMDBAccessLocal)ctx.lookup("ibase/ITMDBAccessEJB/local");
			//Changed by Dhiraj 06/10/10 [AD01SUN008].end
            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");//Gulzar - 07/10/10 termID value pass as 'BASE'
				chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");//Gulzar - 07/10/10
			}
			
			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+"]");
			//Changed by Pawan on 5/11/2011 [AD01SUN008].start
			if ( fromDate == null || fromDate.length() <= 0 )
			{
				errString = itmdbAccessLocal.getErrorString("","INVFRMDATE","","",conn);
				return errString;
			}
			//Added By Rupesh on [14-10-11] [CCF No.:-AD01SUN0018] [Reason :- Convert Date format from ITM format to ApplDateFormat] Start 
			else
			{
				if( xtraParams.indexOf("user_lang=") <= 0 )						
				{
					//fromDate = genericUtility.getValidDateString(fromDate, "yy-MM-dd" , genericUtility.getApplDateFormat());
					fromDate = genericUtility.getValidDateString(fromDate, "dd/MM/yy" , genericUtility.getApplDateFormat());
				}
			}
			//Added By Rupesh on [14-10-11] [CCF No.:-AD01SUN0018] [Reason :- Convert Date format from ITM format to ApplDateFormat] End

			//Changed by Pawan on 5/11/2011 [AD01SUN008].end
			toDate = genericUtility.getColumnValue("date__to",headerDom );
			//Changed by Pawan on 5/11/2011 [AD01SUN008].start
			if ( toDate == null || toDate.length() <= 0)
			{
				errString = itmdbAccessLocal.getErrorString("","INVDTODATE","","",conn);
				return errString;
			}
			//Added By Rupesh on [14-10-11] [CCF No.:-AD01SUN0018] [Reason :- Convert Date format from ITM format to ApplDateFormat] Start 
			else
			{
				if( xtraParams.indexOf("user_lang=") <= 0 )						
				{
					//toDate = genericUtility.getValidDateString(fromDate, "yy-MM-dd" , genericUtility.getApplDateFormat());
					toDate = genericUtility.getValidDateString(toDate, "dd/MM/yy" , genericUtility.getApplDateFormat());
				}
			}
			//Added By Rupesh on [14-10-11] [CCF No.:-AD01SUN0018] [Reason :- Convert Date format from ITM format to ApplDateFormat] End

			//Changed by Pawan on 5/11/2011 [AD01SUN008].end
			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 ,"
							//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002] [Fetching relieve date of employee][Start]
							+"e.RELIEVE_DATE "
							//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002] [Fetching relieve date of employee][End]
							+" 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' " 
							// Modified by Piyush on 31/10/2012 for not to fetch those employee whose joining date is beyound shift to date specified in argument
							//+"		AND nvl( status, ' ') <> 'S'";
							+"		AND nvl( status, ' ') <> 'S' AND e.date_Join <= ?";

				PreparedStatement pStmt = null;
				System.out.println("\n\n getDataSql  :"+getDataSql);
				pStmt = conn.prepareStatement( getDataSql );
				System.out.print("Parameters 1:: [" + frSqlDate+"]");

				pStmt.setDate( 1, frSqlDate );
				// Modified by Piyush on 31/10/2012 [for not to fetch those employee whose joining date is beyound shift to date specified in argument].Start
				System.out.print("Parameters 1-1:: [" + toSqlDate+"]");
				pStmt.setDate( 2, toSqlDate );
				// Modified by Piyush on 31/10/2012 [for not to fetch those employee whose joining date is beyound shift to date specified in argument].End
				
				rsn1 = pStmt.executeQuery( );
				
				while( rsn1.next() )
				{
					EmpInfo empInfo = new EmpInfo();
					empInfo.empCode = rsn1.getString( "EMP_CODE" );
					empInfo.workShift = checkNull(rsn1.getString( "WORK_SHIFT" )).trim();
					empInfo.holTblno = checkNull(rsn1.getString( "HOL_TBLNO" )).trim();
					empInfo.fromDt = rsn1.getDate( "FROM_DT" );
					empInfo.cadre = rsn1.getString( "CADRE" );
					empInfo.grade = rsn1.getString( "GRADE" );
					empInfo.workSite = checkNull(rsn1.getString( "WORK_SITE" )).trim();
					empInfo.shiftIndex = rsn1.getInt( "SHIFT_INDEX" );
					empInfo.dateJoin = rsn1.getDate( "DATE_JOIN" );					
					empInfo.patternCode = checkNull(rsn1.getString( "PATTERN_CODE" )).trim();
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Fetching relieve date of employee ][Start]
					empInfo.relieveDate = rsn1.getDate( "RELIEVE_DATE" );
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Fetching relieve date of employee ][End]
					empList.add( empInfo );
				}
				
				rsn1.close();
				rsn1 = null;
				pStmt.close();
				pStmt = null;
				
				int noOfEmp = empList.size();
				System.out.println("noOfEmp..........::"+noOfEmp);
				
				java.sql.Date toSqlDate1 = getNextDate( toSqlDate ); 

				for( int noOfEmpCnt = 0; noOfEmpCnt < noOfEmp; noOfEmpCnt++ )
				{
					holiTblNo = null;
					counter++;
					System.out.println(" In Loop .........");

					frSqlDate = new java.sql.Date(frDate.getTime());
					System.out.println("java.sql.Date From :["+frSqlDate+"]");
					toSqlDate = new java.sql.Date(tDate.getTime());
					System.out.println("java.sql.Date To :["+toSqlDate+"]");
					toSqlDate1 = getNextDate( toSqlDate ); 
					System.out.println("java.sql.Date Next date To Date :["+toSqlDate1+"]");
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To reset the from & to date][End]

					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;
					}

					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Not to generate shift beyond relieve date of employee][Start]
					String updateSql = "";
					PreparedStatement pstmtTemp = null;
					int updCnt = 0;
					java.sql.Date reliDate = null;
					reliDate = empInfo.relieveDate;
					if(reliDate != null && reliDate.compareTo(toSqlDate) < 0)
					{
						toSqlDate = (java.sql.Date)reliDate;
						System.out.println("Calculation Change to relieve Date ["+toSqlDate+"]");
						toSqlDate1 = getNextDate( toSqlDate ); 
						System.out.println("java.sql.Date Next date To Date :["+toSqlDate1+"]");
						updateSql = "UPDATE WORK_SHIFT SET TO_DT = ? WHERE EMP_CODE = '"+emp+"' "+
									"AND ? BETWEEN FROM_DT AND TO_DT ";
						pstmtTemp = conn.prepareStatement(updateSql);
						pstmtTemp.setDate( 1, toSqlDate );
						pstmtTemp.setDate( 2, toSqlDate );
						updCnt  = pstmtTemp.executeUpdate();
						pstmtTemp.clearParameters();
						if ( updCnt > 0 )
						{
							System.out.println("Updated Shift till relievedate Successfully updCnt=["+updCnt+"]");
						}
						pstmtTemp.close();
						pstmtTemp = null;
						updateSql = "DELETE FROM WORK_SHIFT WHERE EMP_CODE = '"+emp+"' "+
									"AND FROM_DT > ? ";
						pstmtTemp = conn.prepareStatement(updateSql);
						pstmtTemp.setDate( 1, toSqlDate );
						updCnt  = pstmtTemp.executeUpdate();
						pstmtTemp.clearParameters();
						if ( updCnt > 0 )
						{
							System.out.println("Deleted Shift beyound relievedate Successfully updCnt=["+updCnt+"]");
						}
						pstmtTemp.close();
						pstmtTemp = null;
						if(reliDate.compareTo(new java.sql.Date(frDate.getTime())) < 0)
						{
							continue;
						}
					
					}
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Not to generate shift beyond relieve date of employee][End]

					java.sql.Date stShiftDate = frSqlDate;
					java.sql.Date edShiftDate = frSqlDate;
					//Modified by Rupesh on [01/11/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Checking for joining date][Start]					

					java.sql.Date empJoinDt = (java.sql.Date)empInfo.dateJoin; 
					if (frSqlDate.compareTo(empJoinDt) < 0)
					{
						frSqlDate = (java.sql.Date)empJoinDt;
						System.out.println("Joining Date ["+frSqlDate+"]");
						stShiftDate = frSqlDate;
						edShiftDate = frSqlDate;					
					}
					//Modified by Rupesh on [01/11/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Checking for joining date][End]
		
					ResultSet rsTmp = null;
					int selCnt = 0;					
					java.sql.Date prevFrDate = null;
					java.sql.Date nextToDate = null;

					prevFrDate = getPrevDate(frSqlDate);
					System.out.println("prevFrDate::"+prevFrDate);
					updateSql = "UPDATE WORK_SHIFT SET TO_DT = ? WHERE EMP_CODE = '"+emp+"' "+
								"AND ? BETWEEN FROM_DT AND TO_DT AND FROM_DT <> ? ";
					pstmtTemp = conn.prepareStatement(updateSql);
					pstmtTemp.setDate( 1, prevFrDate );
					pstmtTemp.setDate( 2, frSqlDate );
					pstmtTemp.setDate( 3, frSqlDate );
					updCnt  = pstmtTemp.executeUpdate();
					pstmtTemp.clearParameters();
					if ( updCnt > 0 )
					{
						System.out.println("Updated Successfully updCnt=["+updCnt+"]");
					}
					pstmtTemp.close();
					pstmtTemp = null;

					nextToDate = getNextDate(toSqlDate);
					System.out.println("nextToDate::"+nextToDate);
					updateSql = "UPDATE WORK_SHIFT SET FROM_DT = ? WHERE EMP_CODE = '"+emp+"' "+
								"AND ? BETWEEN FROM_DT AND TO_DT AND TO_DT <> ? ";
					pstmtTemp = conn.prepareStatement(updateSql);
					pstmtTemp.setDate( 1, nextToDate );
					pstmtTemp.setDate( 2, toSqlDate );
					pstmtTemp.setDate( 3, toSqlDate );
					updCnt  = pstmtTemp.executeUpdate();
					pstmtTemp.clearParameters();
					if ( updCnt > 0 )
					{
						System.out.println("Updated Successfully updCnt=["+updCnt+"]");
					}
					pstmtTemp.close();
					pstmtTemp = null;

					//End added Gulzar - 03/06/10[]

					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 from WORK_SHIFT ["+upd+"]");
					pstmtn2.close();
					pstmtn2 = null;
					
					patternCode = checkNull(empInfo.patternCode).trim();
					workSite = empInfo.patternCode;
					holiTblNo =  empInfo.holTblno;

					//no need of this end
					if (patternCodeFilt != null && patternCodeFilt.trim().length() > 0)
					{
						patternCode = patternCodeFilt;
					}

					ArrayList frPatternCodeList = new ArrayList();
					ArrayList toPatternCodeList = new ArrayList();
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To Add index of pattern in ArrayList][Start]					
					ArrayList frShiftIndexList = new ArrayList();
					ArrayList toShiftIndexList = new ArrayList();					
					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To Add index of pattern in ArrayList][End]

					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 =  checkNull(empInfo.shiftIndex+"").trim(); //Added by jaimin on 16/06/2008 (AD89SUN014)
					

					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To fetch only confirmed transaction and in proper order][Start]
					/*
					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)
					*/

					frPatternCodeList.add(empInfo.patternCode);
					toPatternCodeList.add(empInfo.patternCode);
					frShiftIndexList.add(lastShiftIndexStr);
					toShiftIndexList.add(lastShiftIndexStr);
					effDateList.add(empJoinDt);
					
					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 < ? ) "+
							"AND CONFIRMED = 'Y' AND (nvl(PATTERN_CODE__FR,' ') <> nvl(PATTERN_CODE__TO, ' ') or nvl(SHIFT_INDEX__FR,'0') <> nvl(SHIFT_INDEX__TO ,'0')) "+
							"ORDER BY EFF_DATE DESC,CONF_DATE DESC,CHG_DATE DESC"; //Sql modify by jaimin on 16/06/2008 (AD89SUN014)

					//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To fetch only confirmed transaction and in proper order][End]

					System.out.print("EMP_EMPLOY_EVENTS[sql] :: " + sql);
					System.out.print("Parameters 1:: [" + frSqlDate+"]");
			
					patternPStmt = conn.prepareStatement( sql );
					patternPStmt.setDate(1,frSqlDate);					
					patternRs = patternPStmt.executeQuery();
					edShiftDate = null;
					shiftIndexTo = 0;
					lastPatternCodeStr = "";
					lastShiftIndexStr = "";
					if( patternRs.next() )
					{						
						System.out.print("Effective Date :: " + patternRs.getDate( "EFF_DATE" ));
						System.out.print("Patten_Code_to :: " + patternRs.getString( "PATTERN_CODE__TO" ));						
						System.out.print("SHIFT_INDEX__TO :: " + patternRs.getInt( "SHIFT_INDEX__TO" ));						
						
						//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Adding index of pattern in ArrayList][Start]		
						
						edShiftDate = patternRs.getDate( "EFF_DATE" );
						lastPatternCodeStr = checkNull(patternRs.getString( "PATTERN_CODE__TO" )).trim();
						shiftIndexTo =  patternRs.getInt("SHIFT_INDEX__TO");	
						lastShiftIndexStr = checkNull(shiftIndexTo+"").trim();
						if (lastShiftIndexStr.length() == 0 || lastShiftIndexStr == "0")
						{
							lastShiftIndexStr = "1";
						}
						if (frPatternCodeList.size() > 0 )
						{
							frPatternCodeList.set(0 , lastPatternCodeStr);
							toPatternCodeList.set(0 , lastPatternCodeStr);
							frShiftIndexList.set(0 , lastShiftIndexStr);
							toShiftIndexList.set(0 , lastShiftIndexStr);
							effDateList.set(0 , edShiftDate);
						}
						else
						{
							frPatternCodeList.add(lastPatternCodeStr);
							toPatternCodeList.add(lastPatternCodeStr);
							frShiftIndexList.add(lastShiftIndexStr);
							toShiftIndexList.add(lastShiftIndexStr);
							effDateList.add(edShiftDate);
						}
						
						//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][Adding index of pattern in ArrayList][End]
					}
					patternRs.close();
					patternRs = null;
					patternPStmt.close();
					patternPStmt=null;
//

					if (patternCodeFilt != null && patternCodeFilt.trim().length() > 0)
					{
						patternCode = patternCodeFilt;
						sql = "SELECT PATTERN_CODE__TO,EFF_DATE,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 = ? and nvl(PATTERN_CODE__TO, ' ') = '" + patternCodeFilt + "' AND NVL(CONFIRMED , 'N') = 'N'";
						patternPStmt = conn.prepareStatement( sql );
						patternPStmt.setDate(1,frSqlDate);					
						patternRs = patternPStmt.executeQuery();
						edShiftDate = null;
						shiftIndexTo = 0;
						lastPatternCodeStr = "";
						lastShiftIndexStr = "";
						if( patternRs.next() )
						{						
							System.out.print("Pattern Change - Effective Date :: " + patternRs.getDate( "EFF_DATE" ));
							System.out.print("Pattern Change - Patten_Code_to :: " + patternRs.getString( "PATTERN_CODE__TO" ));						
							System.out.print("Pattern Change - SHIFT_INDEX__TO :: " + patternRs.getInt( "SHIFT_INDEX__TO" ));						
							
						
							edShiftDate = patternRs.getDate( "EFF_DATE" );
							lastPatternCodeStr = checkNull(patternRs.getString( "PATTERN_CODE__TO" )).trim();
							shiftIndexTo =  patternRs.getInt("SHIFT_INDEX__TO");	
							lastShiftIndexStr = checkNull(shiftIndexTo+"").trim();
							if (lastShiftIndexStr.length() == 0 || lastShiftIndexStr == "0")
							{
								lastShiftIndexStr = "1";
							}
							if (frPatternCodeList.size() > 0 )
							{
								frPatternCodeList.set(0 , lastPatternCodeStr);
								toPatternCodeList.set(0 , lastPatternCodeStr);
								frShiftIndexList.set(0 , lastShiftIndexStr);
								toShiftIndexList.set(0 , lastShiftIndexStr);
								effDateList.set(0 , edShiftDate);
							}
							else
							{
								frPatternCodeList.add(lastPatternCodeStr);
								toPatternCodeList.add(lastPatternCodeStr);
								frShiftIndexList.add(lastShiftIndexStr);
								toShiftIndexList.add(lastShiftIndexStr);
								effDateList.add(edShiftDate);
							}
							
						}
						patternRs.close();
						patternRs = null;
						patternPStmt.close();
						patternPStmt=null;
					}

//					
					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 >= ? "+
							"AND CONFIRMED = 'Y' AND (nvl(PATTERN_CODE__FR,' ') <> nvl(PATTERN_CODE__TO, ' ') or nvl(SHIFT_INDEX__FR,'0') <> nvl(SHIFT_INDEX__TO ,'0')) "+
							"ORDER BY EFF_DATE ASC,CONF_DATE DESC,CHG_DATE DESC";

					System.out.print("EMP_EMPLOY_EVENTS[sql] :: " + sql);
					System.out.print("Parameters 1:: [" + frSqlDate+"]");
					
					patternPStmt = conn.prepareStatement( sql );
					patternPStmt.setDate(1,frSqlDate);					
					patternRs = patternPStmt.executeQuery();
					edShiftDate = null;
					shiftIndexTo = 0;
					lastPatternCodeStr = "";
					lastShiftIndexStr = "";
					while( patternRs.next() )
					{
						edShiftDate = null;
						shiftIndexTo = 0;
						lastPatternCodeStr = "";
						lastShiftIndexStr = "";
						edShiftDate = patternRs.getDate( "EFF_DATE" );
						lastPatternCodeStr = checkNull(patternRs.getString( "PATTERN_CODE__TO" )).trim();
						shiftIndexTo =  patternRs.getInt("SHIFT_INDEX__TO");	
						lastShiftIndexStr = checkNull(shiftIndexTo+"").trim();
						if (lastShiftIndexStr.length() == 0 || lastShiftIndexStr == "0")
						{
							lastShiftIndexStr = "1";
						}
						//if(shiftStDate2.compareTo(joiningDate)< 0)
						if (edShiftDate.compareTo( frSqlDate) == 0)
						{
							if (frPatternCodeList.size() > 0)
							{
								frPatternCodeList.set(0 , lastPatternCodeStr);
								toPatternCodeList.set(0 , lastPatternCodeStr);
								frShiftIndexList.set(0 , lastShiftIndexStr);
								toShiftIndexList.set(0 , lastShiftIndexStr);
								effDateList.set(0 , edShiftDate);
							}
							else
							{
								frPatternCodeList.add(lastPatternCodeStr);
								toPatternCodeList.add(lastPatternCodeStr);
								frShiftIndexList.add(lastShiftIndexStr);
								toShiftIndexList.add(lastShiftIndexStr);
								effDateList.add(edShiftDate);
							}
						}
						else if (edShiftDate.compareTo( toSqlDate ) <= 0)
						{
							frPatternCodeList.add(lastPatternCodeStr);
							toPatternCodeList.add(lastPatternCodeStr);
							frShiftIndexList.add(lastShiftIndexStr);
							toShiftIndexList.add(lastShiftIndexStr);
							effDateList.add(edShiftDate);
						}
						else
						{
							break;
						}
					}				
					
					patternRs.close();
					patternRs = null;
					patternPStmt.close();
					patternPStmt=null;

					edShiftDate = frSqlDate;
					
					System.out.print("edShiftDate 1::::: [" + edShiftDate+"]");
					System.out.print("effDateList.size() 1::::: [" + effDateList.size()+"]");

					//NEED TO TAKE TILL LAST DATE SO = MUST [(edShiftDate.compareTo(toSqlDate) <= 0)]
					java.sql.Date patternEffDate = null;
					boolean isDataGenerated = false;
					if(effDateList.size() > 0)
					{						
						for(int ctr = 0;ctr < effDateList.size();ctr++)
						{
							edShiftDate = null;
							shiftIndexTo = 0;
							lastPatternCodeStr = "";
							lastShiftIndexStr = "";
							java.sql.Date tempEffDate = java.sql.Date.valueOf(effDateList.get(ctr).toString());
							System.out.print("tempEffDate 1::::: [" + tempEffDate+"]");
							patternEffDate = java.sql.Date.valueOf(effDateList.get(ctr).toString());

							if (ctr == 0 && tempEffDate.compareTo(frSqlDate) < 0)
							{
								tempEffDate = frSqlDate;
							}

							stShiftDate = tempEffDate;

							lastPatternCodeStr = checkNull(frPatternCodeList.get(ctr).toString());
							
							lastShiftIndexStr = checkNull(frShiftIndexList.get(ctr).toString());
							if (lastPatternCodeStr == null || lastPatternCodeStr.trim().length() == 0)
							{
								continue;
							}
							if (lastShiftIndexStr.length() == 0 || lastShiftIndexStr == "0")
							{
								lastShiftIndexStr = "1";
							}
							if ((ctr + 1) < effDateList.size())
							{
								edShiftDate = java.sql.Date.valueOf(effDateList.get(ctr+1).toString());
							}
							else
							{
								edShiftDate = getNextDate(toSqlDate);
							}

							errString = insertUpdateWorkShift(empInfo, emp, stShiftDate, edShiftDate, lastPatternCodeStr, patternEffDate, holiTblNo, Integer.parseInt(lastShiftIndexStr), conn, itmdbAccessLocal); 

							if(errString.length() > 0)
							{
								break;
							}
							isDataGenerated = true;
						}
					}
					//Rupesh End [21/11/2012]
					if (isDataGenerated)
					{
						cnt = cnt + 1;
					}
					
				}//end of while

				if (errString != null && errString.trim().length() > 0)
				{
					conn.rollback();
					return errString;
				}
				else if (cnt == 0)
				{
					conn.rollback();
					System.out.println("Record not found for process ........");
					errString = itmdbAccessLocal.getErrorString("","VTRDNTFD","","",conn);
					return errString;
				}
				else
				{
					System.out.println("Process complete");
					conn.commit();
					//Added by Santosh on [19/12/11][to solve the issue when called on Employee Event Confirmation][Start] -->
					if(patternCodeFilt != null && patternCodeFilt.length() > 0)
					{
						errString =  "";
					}
					else
					{
						// Modified by Piyush on 31/12/2012 to avoid error in ITM
						//errString = itmdbAccessLocal.getErrorString("","VTPCOMPT","","",conn);
						if ( xtraParams.indexOf("user_lang=") <= 0 )
						{
							errString = "";
						}
						else
						{
							errString = itmdbAccessLocal.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);
			}
			// Remarked by Piyush on 11/01/2013 [As the object is nullified in bellow statement so no need to clear]
			//dayslist.clear();
			dayslist = null;
			// Remarked by Piyush on 11/01/2013 [As it was taking to much time in processing]
			//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, java.sql.Date patternEffDate, String holiTblNo,int shiftIndexNew, Connection conn, ITMDBAccessLocal itmdbAccessLocal ) throws Exception
	{
		System.out.println("insertUpdateWorkShift is called...............");
		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;

		String tempShift = "XXX";
		//Added by satya on 07/06/10 [AD90SUN028].start
		String genShiftHol = null;
		//Added by satya on 07/06/10 [AD90SUN028].end

		try
		{

			stmt = conn.createStatement();
			sql = "SELECT SHIFT_PATTERN, START_DATE, NO_OF_DAYS, GEN_SHIFT_HOL FROM SHIFT_PATTERN WHERE PATTERN_CODE = '"+frpatternCode+"'";
			//changed by satya on 07/06/10 [AD90SUN028].start
			rsin1 = stmt.executeQuery(sql);
			if(rsin1.next())
			{
				shiftPattern = rsin1.getString( "SHIFT_PATTERN" );
				stDate = rsin1.getDate( "START_DATE" );
				noOfDays = rsin1.getInt( "NO_OF_DAYS" );
				//added by satya on 07/06/10 [AD90SUN028].start
				genShiftHol = rsin1.getString("GEN_SHIFT_HOL");
				//added by satya on 07/06/10 [AD90SUN028].end
			}
			rsin1.close();
			rsin1 = null;

			stmt.close();
			stmt=null;

			stDate = patternEffDate;

			System.out.println("frpatternCode :"+frpatternCode);			

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

			if (shiftIndexNew > noOfDays )
			{
				System.out.println("\n\n shiftIndexNew > noOfDays: "+shiftIndexNew+ " > " + noOfDays);
				shiftIndexNew = 1;
			}

			insertSql = "INSERT INTO WORK_SHIFT (EMP_CODE,FROM_DT,TO_DT,SHIFT,CHG_USER,CHG_TERM,CHG_DATE,REAL_SHIFT,PATTERN_CODE) "
			//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][Start]
			//	+" Values (?,?,?,?,?,?,?,?,?)";
				+" Values (?,?,?,?,?,?,FN_SYSDATE(),?,?)";
			//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][End]

			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);

			if (shiftIndexNew == 0)
			{
				shiftIndexNew = 1;
			}			
			while( shiftStDate2.compareTo( shiftEndDate ) < 0 )
			{
				shiftOfDate = null;
				String holidayType = null;
				if ( genShiftHol != null && genShiftHol.equalsIgnoreCase("Y") )
				{			
					holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );				
					boolean isShiftGeneratedForHoliday = false;
					java.sql.Date shiftNewStDate = shiftStDate2;
					while( isHolidayForType(shiftStDate2, holidayType, holiTblNo,conn) && shiftStDate2.compareTo( shiftEndDate ) < 0 )					
					{						
						System.out.println( "From 720" );
						insertIntoWorkShiftForHoliday( empCode, shiftStDate, shiftStDate2, holidayType, frpatternCode, conn );
						shiftNewStDate = shiftStDate2;
						shiftStDate2 = getNextDate( shiftStDate2 );
						
						isShiftGeneratedForHoliday = true;
						if (shiftStDate2.compareTo( shiftEndDate ) >= 0 )
						{
							break;
						}
						System.out.println("shiftStDate2 :: " + shiftStDate2);
						holidayType = getHolidayTypeForDate( shiftStDate2, holiTblNo, conn );
					} 
					if (isShiftGeneratedForHoliday)
					{
						shiftStDate = shiftStDate2;
					}
				}
				
				
				//uncommented and changed by satya on 07/06/10 [AD90SUN028].end
				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
				
				cadre = empInfo.cadre;
				grade = empInfo.grade;
				siteCode = empInfo.workSite;
				shiftIndex = shiftIndexNew;//Added by jaimin on 16/06/2008 (AD89SUN014)
				
				/*
				shift = getActualshift(shiftStDate2,frpatternCode,shiftPattern,stDate,noOfDays); 
				*/
				shift = getActualshift(shiftStDate2,frpatternCode,shiftIndexNew,shiftPattern,stDate,noOfDays,conn); 

				System.out.println("shift [" + shift + "] tempShift [" + tempShift + "] shiftStDate [" + shiftStDate + "] shiftStDate2 [" + shiftStDate2 + "]");

				if ( shift != null && !shift.trim().equalsIgnoreCase(tempShift) )
				{
					shiftStDate = shiftStDate2;
					tempShift = shift;
				}
				//sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+shift+"' AND EMP_CODE = '"+empCode+"'";				
				sql = "SELECT COUNT(*) FROM WORK_SHIFT WHERE FROM_DT = ? AND SHIFT = '"+tempShift+"' AND EMP_CODE = '"+empCode+"'";				

				pstmtn6 = conn.prepareStatement(sql);
				pstmtn6.setDate(1, shiftStDate);
				
				System.out.println("Sql At 1444 :: " + sql);
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT START DATE(FROM_DT) =["+shiftStDate+"]");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT =["+tempShift+"]");
				System.out.println("PARAMETER IN UPDATE QUERY  EMP_CODE ["+empCode+"]");
				System.out.println("PARAMETER IN UPDATE QUERY  SHIFT TO DATE(TO_DT) =["+shiftStDate2+"]");
				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);
				System.out.println("iN INSERT UPDATE Shift : "+tempShift+" 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.setString(7,shift);
					pstmtInsert.setString(7,tempShift);
					pstmtInsert.setString(8,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;
				}
				
				{
					System.out.println("Before Incrementing shiftStDate2 by one day.........::"+shiftStDate2);
					shiftStDate2 = getNextDate( shiftStDate2 ); //Gulzar - 17/09/09
					System.out.println("After incremented shiftStDate2 by one day....::"+shiftStDate2);
				}
			}//end of while

			/*
			if(stDate != null && stDate.compareTo(shiftStDate) > 0)
			{
				System.out.println("Pattern is not Valid for the Processing Month........");
				errString = itmdbAccessLocal.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();}
			// Remarked by Piyush on 11/01/2013 [As it was taking to much time in processing]
			// System.gc();
		}
		return "";
	}//end of insertUpdateWorkShift
//End - Overload - function added by jaimin on 16/06/2008 (AD89SUN014)


	private String getActualshift(java.sql.Date currDate,String patternCode,int shiftIndex,String shiftPattern,java.sql.Date shiftStDate,int noOfDays, Connection conn)
	{
		long timeDiff = 0;
		int noDays = 0;
		ArrayList patternList = new ArrayList();
		String patString ="",day ="",shift ="";
		ArrayList shiftTime = new ArrayList();
		ArrayList  shiftTime1 =  new ArrayList();
		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);
		System.out.println("\n\n shiftIndex "+shiftIndex);

		try{
			patternList = GenericUtility.getInstance().getTokenList(shiftPattern,";");
			System.out.println("noOfDays :"+noOfDays+"noDays :"+noDays);
			if (shiftIndex > noOfDays )
			{
				System.out.println("\n\n shiftIndex > noOfDays: "+shiftIndex+ " > " + noOfDays);
				shiftIndex = 1;
			}

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

			int n1 = 0;

			for( int i = shiftIndex-1 ; i<shiftTime.size(); i++ )
			{
				shiftTime1.add( shiftTime.get(shiftIndex-1+n1));
				n1++;
			}
			for(int i = 0; i < shiftIndex-1; i++)
			{
				shiftTime1.add(shiftTime.get(i));
			}

			noDays = daysDiff( shiftStDate, currDate, conn );
			noDays = noDays;
			while(noDays >= noOfDays)
			{
				noDays = noDays%noOfDays ;
			}
			shift = (String)shiftTime1.get(noDays).toString();
		}catch(Exception e){
			System.out.println("Exception "+e);
			e.printStackTrace();
		}
		return shift;
	}


	// 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 Type 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) "
			//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][Start]
				//+" Values (?,?,?,?,?,?,?,?,?)";
				+" Values (?,?,?,?,?,?,FN_SYSDATE(),?,?)";
			//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][End]
			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
				
				//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][Start]
				//pstmtInsert.setTimestamp(  7, new java.sql.Timestamp( System.currentTimeMillis() ) );
				//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][End]

				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
				//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][Start]
				pstmtInsert.setString( 7, shift);
				pstmtInsert.setString( 8, frpatternCode );
				//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To insert CHG_DATE with time][End]
				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 type 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;
		//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To store relieve date of employee][Start]
		public java.sql.Date relieveDate = null;
		//Modified by Rupesh on [26/10/2012][CCF No.:COR10/C252 -AD01SUN037 & COR11/C025-AD1ASUN002][To store relieve date of employee][End]
	}
	//end bean class added by msalam on 170308
	//Function added - Gulzar - 17/09/09
	private java.sql.Date getNextDate( java.sql.Date currShiftDate ) 
	{
		java.util.Date uDate1 = null;
		java.sql.Date nextShiftDate = null;
		try
		{
			Calendar calObject = Calendar.getInstance();
			calObject.setTime( currShiftDate );
			calObject.add( calObject.DATE, 1 );
			uDate1 = calObject.getTime();
			nextShiftDate = new java.sql.Date( uDate1.getTime() );
			calObject = null;
		}
		catch( Exception e )
		{
			e.printStackTrace();
		}
		return nextShiftDate;
	}
	//End Added - Gulzar - 17/09/09
	//Start added gulzar - 03/06/10[]
	private java.sql.Date getPrevDate( java.sql.Date currShiftDate ) 
	{
		java.util.Date uDate1 = null;
		java.sql.Date nextShiftDate = null;
		try
		{
			Calendar calObject = Calendar.getInstance();
			calObject.setTime( currShiftDate );
			calObject.add( calObject.DATE, -1 );
			uDate1 = calObject.getTime();
			nextShiftDate = new java.sql.Date( uDate1.getTime() );
			calObject = null;
		}
		catch( Exception e )
		{
			e.printStackTrace();
		}
		return nextShiftDate;
	}
	//End added gulzar - 03/06/10[]
	//Start added gulzar - 06/07/10[]


	//Modified by Santosh on [19/07/2012][Defined overloaded method To solve the issue of date difference coming as 0][Start]

	public int daysDiff( java.util.Date dateFrom, java.util.Date dateTo , Connection conn)
	{
		String dbDateFormat = "";
		SimpleDateFormat simpledateformat = null;
		String fromDate = "";
		String toDate = "";
		PreparedStatement pStmt = null;
		ResultSet rSet = null;
		int diffBtwDate = 0;

		try
		{
			dbDateFormat = genericUtility.getDBDateFormat();
			simpledateformat = new SimpleDateFormat(dbDateFormat);
			fromDate = simpledateformat.format(dateFrom);
			toDate = simpledateformat.format(dateTo);
			String sql = "SELECT TO_DATE(?,?) - TO_DATE(?,?) FROM DUAL";

			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, toDate);
			pStmt.setString(2, dbDateFormat);
			pStmt.setString(3, fromDate);
			pStmt.setString(4, dbDateFormat);
			System.out.println("todate = [ "+toDate+" ], dbDateFormat = [ "+dbDateFormat+" ], fromDate = [ "+fromDate+" ]");		
			System.out.println("daysDiffsql===>"+sql);		
			rSet = pStmt.executeQuery();
            pStmt.clearParameters();

			while(rSet.next())
			{
				diffBtwDate = rSet.getInt(1);
			}
			System.out.println("Total No Of Days between [" + fromDate + "] and [" + toDate + "] =[" + diffBtwDate+ "]");

			if(rSet != null)
			{
				rSet.close();
				rSet = null;
			}
			if(pStmt != null)
			{
				pStmt.close();
				pStmt = null;
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
		}
		finally
		{
			System.out.println("Inside Finally daysDiff");
			try
			{
				if(rSet != null)
				{
					rSet.close();
					rSet = null;
				}
				if(pStmt != null)
				{
					pStmt.close();
					pStmt = null;
				}
			}
			catch(Exception exp)
			{
				exp.printStackTrace();
			}
		}
		return diffBtwDate;
	}
	//Modified by Santosh on [19/07/2012][Defined overloaded method To solve the issue of date difference coming as 0][End]

	/*
	public int daysDiff( java.util.Date dateTo, java.util.Date dateFrom )
	{
		int noOfDays = 0;
		try
		{
			java.util.Calendar cal = java.util.Calendar.getInstance();
			cal.setTime( dateFrom );
			int fromDayNo = 0;
			int toDayNo = 0;
			fromDayNo = cal.get(cal.DAY_OF_YEAR);
			cal.setTime( dateTo );
			toDayNo = cal.get(cal.DAY_OF_YEAR);
			noOfDays = (toDayNo - fromDayNo);
			//System.out.println("Total No Of Days between ["+dateFrom+"] and ["+dateTo+"] is ["+noOfDays+"]");
		}
		catch ( Exception e )
		{
			e.printStackTrace();
		}
		return noOfDays;
	}
	//End added gulzar - 06/07/10[]
*/
	private String checkNull(String input)
	{
		if (input==null)
		{
			input="";
		}
		return input;
	}

}

