package ibase.webitm.ejb.mfg.adv;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.mfg.WorkOrderRouteAlt;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import javax.ejb.Stateless;


@Stateless
public class WorkOrderRouteAltConf  extends ActionHandlerEJB implements WorkOrderRouteAltConfLocal,WorkOrderRouteAltConfRemote
{
	WorkOrderRouteAlt workOrderAltObj=WorkOrderRouteAlt.getInstance();
	String loginSiteCode="";
	int ll_ctr=0; 
	Map <Integer,String> as_tool=new TreeMap<Integer,String>();
	Map <Integer,Integer> al_toolsr=new TreeMap<Integer,Integer>();
	boolean isError=false;
	public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		
		System.out.println("--------------confirm method of WorkOrderRouteAltConf ------------- : ");
		String retString = "";		
		try
		{
						
				retString = worderRouteAltConfirm(tranId,xtraParams,forcedFlag);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception :WorkOrderRouteAltConf :confirm():" + e.getMessage() + ":");
			retString = GenericUtility.getInstance().createErrorString(e);
			e.printStackTrace();
		}
		
		return retString;
	}
	private String worderRouteAltConfirm(String tranId,String xtraParams,String forcedFlag) 
	{
		isError=false;
		tranId=tranId==null ? "" :tranId.trim();
		ResultSet rs=null;
		Connection conn=null;
		ConnDriver ConnDriver = new ConnDriver();
		PreparedStatement pstmt=null;
		ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		String sql="",errString="",returnString="";
		try{
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");			
			conn.setAutoCommit(false);
			String confirmed="";
			sql = "select confirmed from worder_route_alt WHERE amd_no = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed =rs.getString(1)==null ? "N":rs.getString(1);				
			}	
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				//if already confirm then show error 
				errString = itmdbAccess.getErrorString("", "VTALCONF", "", "", conn);
				return errString;
			}
			returnString=gbfRetrieveWoRouteAlt(tranId,xtraParams,conn);
			System.out.println("Return String from gbfRetrieveWoRouteAlt---->>["+returnString+"]");
			if(isError){
				conn.rollback();
				errString = itmdbAccess.getErrorString("", "VTWRANC", "", "", conn);
				return errString;				
			}
			if("VTMCLHDR".equalsIgnoreCase(returnString)){
				errString = itmdbAccess.getErrorString("", "VTMCLHDR", "", "", conn);
				conn.rollback();
				return errString;
			}
			if("VTMCDET".equalsIgnoreCase(returnString)){
				errString = itmdbAccess.getErrorString("", "VTMCDET", "", "", conn);
				conn.rollback();
				return errString;
			}
			
			if(returnString.length()== 0){
				conn.commit();	
				errString = itmdbAccess.getErrorString("", "VTWRACS", "", "", conn);
				return errString;
			}
			
			
		} //end try
		catch(SQLException se)
		{
			System.out.println("SQLException : class WorkOrderRouteAltConf : ");
			se.printStackTrace();
			try
			{				
			  conn.rollback();
			}
			catch(Exception e){
				System.out.println("Exception : Occure during rollback........");e.printStackTrace();
				}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			try
			{				
				conn.rollback();
			}
			catch (Exception e1)
			{
				e.printStackTrace();
				System.out.println("Exception Class [WorkOrderRouteAltConf]::"+e.getMessage());
			}
			
			
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}	
				if (conn!=null)
				{
					conn.close();
					conn=null;
				}		
							
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
				
			}
		}
		
		return errString;
	}
	
	private String gbfRetrieveWoRouteAlt(String amdNo,String xtraParams,Connection conn)
	{
		String sql="",errcode = "", conf="", mcCode="" , worder="",dbName="",itemCode="",procCode="";
		String loginEmpCode="",loginSite="",workCtr="",workCtrOld="",mcCodeOld="",deptCode="";
		int operation=0,cnt=0;
		double waitT=0,setT=0,resetT=0,runT=0;
		ArrayList <String> updateParamList =new ArrayList();
		Map <String,String> updateParamMap=new HashMap<String,String>();
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		GenericUtility genericUtility=GenericUtility.getInstance();
		
		try{
		loginEmpCode =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
		loginSite=genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode");
		loginSiteCode=loginSite;
		
		errcode = gbfConfirmWoRouteAlt(amdNo,conn);
		System.out.println("Return errCode from gbfConfirmWoRouteAlt------>>["+errcode+"]");
		
		
		if(errcode.length() == 0){	
			
			sql="select work_order,operation,item_code,proc_code,wait_time,set_time,run_time,reset_time,work_ctr,"
					+ "mc_code,work_ctr__o,mc_code__o,dept_code from worder_route_alt where amd_no= ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, amdNo);
			rs=pstmt.executeQuery();
			if(rs.next()){
				worder=checkNull(rs.getString(1));
				operation=rs.getInt(2);
				itemCode=checkNull(rs.getString(3));
				procCode=checkNull(rs.getString(4));
				waitT=rs.getDouble(5);
				setT=rs.getDouble(6);
				runT=rs.getDouble(7);
				resetT=rs.getDouble(8);
				workCtr=checkNull(rs.getString(9));
				mcCode=checkNull(rs.getString(10));
				workCtrOld=checkNull(rs.getString(11));
				mcCodeOld=checkNull(rs.getString(12));
				deptCode=checkNull(rs.getString(13));
			}
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
			System.out.println("worder---->"+worder);
			System.out.println("operation---->"+operation);
			System.out.println("mccode---->"+mcCode);
			System.out.println("workCtr---->"+workCtr);
			System.out.println("deptCode---->"+deptCode);
			if(workCtr.length() == 0){
				workCtr=workCtrOld;
			}
			if(mcCode.length() == 0){
				mcCode=mcCodeOld;
			}			
			
			updateParamMap.put("mcCode", mcCode);
			updateParamMap.put("worder", worder);
			updateParamMap.put("operation", Integer.toString(operation));
			updateParamMap.put("confirmed", "Y");
			updateParamMap.put("loginEmpCode", loginEmpCode);
			updateParamMap.put("amdNo", amdNo);
			updateParamMap.put("workCtr", workCtr);
			
			updateParamMap.put("itemCode", itemCode);
			updateParamMap.put("procCode", procCode);
			updateParamMap.put("waitT", Double.toString(waitT));
			updateParamMap.put("setT", Double.toString(setT));
			updateParamMap.put("runT", Double.toString(runT));
			updateParamMap.put("resetT", Double.toString(resetT));
			updateParamMap.put("deptCode", deptCode);
			
			
			// update worder_route and worder_route_alt table 
			cnt =updateOnConfirmation(conn,updateParamMap);
			System.out.println("Return int from updateOnConfirmation--->>["+cnt+"]");
			/*if (cnt  > 0){
				conn.commit();
			}
			else{
				conn.rollback();
			}*/
			
			
		}
		
			
		}
		catch(Exception e){
			System.out.println("Exception in gbfRetrieveWoRouteAlt-----");
			e.printStackTrace();
			isError=true;
		}
		
		
		
		return errcode;
		
	}
	private String gbfConfirmWoRouteAlt(String amdNo, Connection conn)
	{
		PreparedStatement pstmt=null,pstmt1=null;
		ResultSet rs=null,rs1=null,rsTemp=null;
		String errorString="",workorder="",workctr="",mcCodeOld="",mcCode="",siteCode="",item="",endTime="";
		String sql="",sql1="",bomCode="",itemCode="",route_code="",chgUser="",chgTerm="",route="",proc_code="";
		String chgStat="1",operStat="1",qcType="N",shift="",startTime="",procCode="",current="",inTime="";
		String detSiteCode="",detWorkCtr="",detMcCode="",detDayNo="",detShift="" ,routeCode="",dayNo=""; 
		java.sql.Timestamp admDate=null;
		int operation=0,seqNo=0,cnt=0,seqNo1=0,rows=0,startRow=0,error=0,ParentOpert=0;
		double workOrderQty=0,waitTime=0,setTime=0,runTime=0,resetTime=0,bomBatchQty=0,mcCapacityOri=0,wcBatchQty=0;
		double processSizeOriginal=0,operLevel=0,decPrSize=0,noOfShiftsOriginal=0,noOfShiftsActual=0,machineCapacity=0;
		ArrayList<String> condParam=new ArrayList();
		java.sql.Timestamp chgDate=null,loadDate=null,detLoadDate=null; 
		HashMap machToolMap=new HashMap();
		HashMap machineLoadMap=new HashMap();
		HashMap  mcLoadMap=new HashMap();
		HashMap mcLoadDetMap=new HashMap();
		try{
			sql="select amd_date,work_order,operation,work_ctr,mc_code,mc_code__o,operation_parent"
					+ " from worder_route_alt where amd_no = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, amdNo);
			rs=pstmt.executeQuery();
			if(rs.next()){
				admDate=rs.getTimestamp(1);
				workorder=checkNull(rs.getString(2)); 
				operation=rs.getInt(3);
				workctr=checkNull(rs.getString(4));
				mcCode=checkNull(rs.getString(5));
				mcCodeOld=checkNull(rs.getString(6));
				ParentOpert=rs.getInt(7);
			}			
			System.out.println("mcCode------->>["+mcCode+"]");
			System.out.println("mcCodeOld------->>["+mcCodeOld+"]");
			System.out.println("Parent operation------->>["+ParentOpert+"]");
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			// beFore deallocating machines get the necessary details required		
			sql="Select site_code,quantity,bom_code,route_code,item_code from workorder where work_order = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workorder);
			rs=pstmt.executeQuery();
			if(rs.next()){
				siteCode=checkNull(rs.getString(1));
				workOrderQty=rs.getDouble(2);
				bomCode=checkNull(rs.getString(3));
				route=checkNull(rs.getString(4));
				item=checkNull(rs.getString(5));				
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			condParam.add("operation");
			condParam.add("work_order");
			condParam.add(Integer.toString(operation));
			condParam.add(workorder);
			cnt=workOrderAltObj.getDBRowCount(conn, "worder_route", condParam);
			System.out.println("count return from getDBRowCount--->["+cnt+"]");
			if(cnt == 0){
				
				sql="select worder_route_alt.chg_date,worder_route_alt.chg_user,worder_route_alt.chg_term,"+
				 "worder_route.seq_no,worder_route.route_code,worder_route_alt.item_code,"+
				"worder_route_alt.wait_time,worder_route_alt.set_time,worder_route_alt.run_time,"+
				 "worder_route_alt.reset_time,worder_route_alt.proc_code "+  
				"from worder_route_alt,worder_route,item,machines,process,employee,workorder "+ 
				"where ( worder_route_alt.emp_code__aprv = employee.emp_code (+)) and "+
				"( worder_route_alt.work_order = worder_route.work_order (+)) and "+ 
				"( worder_route_alt.mc_code = machines.mc_code (+)) and "+
				"( worder_route_alt.work_order = workorder.work_order (+)) and "+  
				"( worder_route_alt.item_code = item.item_code ) and "+ 
				"( worder_route_alt.proc_code = process.proc_code (+)) and "+  
				"( ( worder_route_alt.amd_no = ? ) )";
				
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, amdNo);
				rs=pstmt.executeQuery();
				if(rs.next()){
					chgDate=rs.getTimestamp(1);
					chgUser=checkNull(rs.getString(2));
					chgTerm=checkNull(rs.getString(3));
					seqNo=rs.getInt(4);
					route_code=checkNull(rs.getString(5));
					itemCode=checkNull(rs.getString(6));
					waitTime=rs.getDouble(7);
					setTime=rs.getDouble(8);
					runTime=rs.getDouble(9);
					resetTime=rs.getDouble(10);
					proc_code=checkNull(rs.getString(11));					
				}
				rs.close();			
				rs=null;
				pstmt.close();
				pstmt=null;
				
				System.out.println("itemCode----->>["+itemCode+"]");
				System.out.println("proc_code----->>["+proc_code+"]");
				
				sql="insert into worder_route (work_order,operation,proc_code,seq_no,route_code,item_code,"
						+ "work_ctr,mc_code ,chg_stat ,oper_stat ,quantity ,wait_time,set_time,run_time,"
						+ "reset_time ,chg_date ,chg_user ,chg_term ,qc_type,operation_level ) "
						+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, workorder);
				pstmt.setInt(2, operation);
				pstmt.setString(3, proc_code);
				pstmt.setInt(4, seqNo);				
				pstmt.setString(5, route_code);
				pstmt.setString(6, itemCode);				
				pstmt.setString(7, workctr); 
				pstmt.setString(8, mcCode);
				pstmt.setString(9, chgStat);
				pstmt.setString(10, operStat);
				pstmt.setDouble(11, workOrderQty);				
				pstmt.setDouble(12, waitTime);
				pstmt.setDouble(13, setTime);
				pstmt.setDouble(14, runTime);
				pstmt.setDouble(15, resetTime);				
				pstmt.setTimestamp(16, chgDate);
				pstmt.setString(17, chgUser); 
				pstmt.setString(18, chgTerm); 
				pstmt.setString(19, qcType);
				pstmt.setInt(20, operation);
				
				cnt=pstmt.executeUpdate();				
				System.out.println("inser query rows worder_route---->>["+cnt+"]");	
				pstmt.close();
				pstmt=null;
			}
			
			sql="Select Min(a.load_date),Max(a.shift) From machine_load a,machine_load_det b "
					+ "Where a.site_code = b.site_code and a.load_date 	= b.load_date "
					+ "and a.work_ctr = b.work_ctr and a.mc_code = b.mc_code "
					+ "and a.day_no = b.day_no and a.shift = b.shift and a.work_ctr = ? "
					+ "and a.mc_code = ?  and b.work_order = ? "
					+ "and b.operation 	= ?";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			pstmt.setString(3, workorder);
			pstmt.setInt(4, operation);
			rs=pstmt.executeQuery();
			if(rs.next()){
				loadDate=rs.getTimestamp(1);
				shift=checkNull(rs.getString(2));
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			if(loadDate != null){
				
				sql="Select min(a.shift_start_time) From machine_load a,machine_load_det b "
						+ "Where a.site_code = b.site_code and a.load_date = b.load_date "
						+ "and a.work_ctr = b.work_ctr and a.mc_code = b.mc_code and "
						+ "a.day_no = b.day_no and a.shift = b.shift and a.work_ctr = ? "
						+ "and a.mc_code = ? and b.work_order = ? and b.operation = ? "
						+ "and a.load_date = ?"; //@#@###$
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			pstmt.setString(3, workorder);
			pstmt.setInt(4, operation);
			pstmt.setTimestamp(5, loadDate); 
			rs=pstmt.executeQuery();
			if(rs.next()){
				startTime=checkNull(rs.getString(1));
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
				
			}
			// first deallocate the old machine
			// Then allocate the new machine
			// deallocating the old machine
			
			sql="select distinct site_code,load_date, work_ctr,mc_code,day_no,shift "
					+ "From machine_load_det Where  work_ctr = ? and mc_code = ? "
					+ "and work_order = ? and operation  = ?";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			pstmt.setString(3, workorder);
			pstmt.setInt(4, operation);
			rs=pstmt.executeQuery();
			while(rs.next()){
				detSiteCode=checkNull(rs.getString(1));
				detLoadDate=rs.getTimestamp(2);
				detWorkCtr=checkNull(rs.getString(3));
				detMcCode=checkNull(rs.getString(4));
				detDayNo=checkNull(rs.getString(5));
				detShift=checkNull(rs.getString(6));
				
				sql1="Delete From machine_load Where site_code = ? and load_date = ? "
						+ "and work_ctr = ?	and mc_code = ?	and day_no = ? and shift = ?";
				pstmt1=conn.prepareStatement(sql1);
				pstmt1.setString(1, detSiteCode);
				pstmt1.setTimestamp(2, detLoadDate);
				pstmt1.setString(3, detWorkCtr);
				pstmt1.setString(4, detMcCode);
				pstmt1.setString(5, detDayNo);
				pstmt1.setString(1, detShift);
				cnt=0;
				cnt=pstmt.executeUpdate();
				System.out.println("No. of rows are deleted machine_load---->>["+cnt+"]");			
				pstmt1.close();
				pstmt1=null;
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			sql="Delete From machine_load_det Where work_ctr = ? and mc_code = ? "
					+ "and work_order = ? and operation = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			pstmt.setString(3, workorder);
			pstmt.setInt(4, operation);
			cnt=0;
			cnt=pstmt.executeUpdate();
			System.out.println("No. of rows are deleted machine_load_det---->>["+cnt+"]");
			pstmt.close();
			pstmt=null;
			
			sql="Delete From Toolplan Where work_ctr = ? and mc_code = ? and work_order = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			pstmt.setString(3, workorder);			
			cnt=0;
			cnt=pstmt.executeUpdate();
			System.out.println("No. of rows are deleted machine_load_det---->>["+cnt+"]");
			
			// now allocate the new machine 
			
			sql="Select batch_qty from bom where bom_code = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, bomCode);
			rs=pstmt.executeQuery();
			if(rs.next()){
				bomBatchQty=rs.getDouble(1);
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			sql="Select capacity,batch_qty from  workcntr Where work_ctr = ? and"
					+ "	mc_code	= ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workctr);
			pstmt.setString(2, mcCodeOld);
			rs=pstmt.executeQuery();
			if(rs.next()){
				mcCapacityOri=rs.getDouble(1);
				wcBatchQty=rs.getDouble(2);
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			sql="Select process_size,route_code,proc_code,seq_no,operation_level from "
					+ "procroute Where route_code = ? and operation = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, route);
			pstmt.setInt(2, operation);
			rs=pstmt.executeQuery();
			if(rs.next()){
				processSizeOriginal=rs.getDouble(1);
				routeCode=checkNull(rs.getString(2));
				procCode=checkNull(rs.getString(3));
				seqNo1=rs.getInt(4);
				operLevel=rs.getDouble(5);
			}
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
					
			decPrSize=processSizeOriginal;
			System.out.println("decPrSize-------->>["+decPrSize+"]");
			System.out.println("workOrderQty-------->>["+workOrderQty+"]");
			System.out.println("max-------->>["+Math.max(bomBatchQty,wcBatchQty)+"]");
			decPrSize = (decPrSize / Math.max(bomBatchQty,wcBatchQty)) * workOrderQty;
			System.out.println("Total decPrSize-------->>["+decPrSize+"]");
			noOfShiftsOriginal= decPrSize/processSizeOriginal;
			noOfShiftsActual = noOfShiftsOriginal;
			//
			sql="Select capacity from  workcntr where work_ctr = ? and mc_code = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,workctr);
			pstmt.setString(2, mcCode);
			rs=pstmt.executeQuery();
			if(rs.next()){
				machineCapacity=rs.getDouble(1);
			}
			System.out.println("machine Capacity-------->>["+machineCapacity+"]");
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			//recalculate no.of shifts required.
			// 29-07-03 Formula is wrong
			// let original capacity = 2000
			// let current machine capacity = 1000
			// let original no. of shifts = 4
			// Then the current machine should complete the same in 8 shifts
			// i.e 2000 / 1000 * 4 = 8
			
			noOfShiftsActual=(mcCapacityOri / machineCapacity) * noOfShiftsOriginal;
			System.out.println("noOfShiftsActual-------->>["+noOfShiftsActual+"]");
			
			// now allocate the new machine For this we require the work calEndar - get it...
			sql="select count(*) from work_calendar,workshft"
					+ " where ( work_calendar.site_code = workshft.site_code ) and"
					+ "( work_calendar.day_no = workshft.day_no ) and ( work_calendar.shift = workshft.shift ) and"
					+ "( ( work_calendar.working = 'Y' ) and ( work_calendar.confirmed = 'Y' ) and"
					+ " ( work_calendar.site_code = ? ) and ( work_calendar.work_date >= ? )) "
					+ "order by work_calendar.site_code asc,work_calendar.work_date asc,workshft.in_time asc";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setTimestamp(2, loadDate);
			
			rs=pstmt.executeQuery(); 
			if(rs.next()){
				rows=rs.getInt(1);
			}
			System.out.println("rows count------>>["+rows+"]");
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			
			sql="select count(*) from work_calendar,workshft"
					+ " where ( work_calendar.site_code = workshft.site_code ) and"
					+ "( work_calendar.day_no = workshft.day_no ) and ( work_calendar.shift = workshft.shift ) and"
					+ "( ( work_calendar.working = 'Y' ) and ( work_calendar.confirmed = 'Y' ) and"
					+ " ( work_calendar.site_code = ? ) and ( work_calendar.work_date >= ? ))  and "
					+ "work_calendar.work_date= ? and work_calendar.shift= ? "
					+ "order by work_calendar.site_code asc,work_calendar.work_date asc,workshft.in_time asc";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setTimestamp(2, loadDate);
			pstmt.setTimestamp(3, loadDate);
			pstmt.setString(4, shift);	
			
			rs=pstmt.executeQuery();
			if(rs.next()){
				startRow=rs.getInt(1);
			}
			System.out.println("startRow------>>["+startRow+"]");
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;
			if(startRow > 1){
				for(int i=startRow; i > 1;i--){
				sql="select work_calendar.shift,work_calendar.day_no,work_calendar.work_date,"
						+ "workshft.in_time,workshft.out_time from work_calendar,workshft"
						+ " where ( work_calendar.site_code = workshft.site_code ) and"
						+ "( work_calendar.day_no = workshft.day_no ) and ( work_calendar.shift = workshft.shift ) and"
						+ "( ( work_calendar.working = 'Y' ) and ( work_calendar.confirmed = 'Y' ) and"
						+ " ( work_calendar.site_code = ? ) and ( work_calendar.work_date >= ? )) "
						+ "order by work_calendar.site_code asc,work_calendar.work_date asc,workshft.in_time asc";
				
				
				pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				pstmt.setString(1, siteCode);
				pstmt.setTimestamp(2, loadDate);
				/*pstmt.setTimestamp(3, loadDate);
				pstmt.setString(4, shift);*/
				rs=pstmt.executeQuery();
				while(rs.next()){
					current=checkNull(rs.getString(1));
					
					if(current.compareTo(shift) < 0){
						rs.deleteRow();
					}
				}
				
			}
			} //end startRow
			if(rs!=null)
			rs.beforeFirst();
			else{
				sql="select work_calendar.shift,work_calendar.day_no,work_calendar.work_date,"
						+ "workshft.in_time,workshft.out_time from work_calendar,workshft"
						+ " where ( work_calendar.site_code = workshft.site_code ) and"
						+ "( work_calendar.day_no = workshft.day_no ) and ( work_calendar.shift = workshft.shift ) and"
						+ "( ( work_calendar.working = 'Y' ) and ( work_calendar.confirmed = 'Y' ) and"
						+ " ( work_calendar.site_code = ? ) and ( work_calendar.work_date >= ? )) "
						+ "order by work_calendar.site_code asc,work_calendar.work_date asc,workshft.in_time asc";
				
				
				pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				pstmt.setString(1, siteCode);
				pstmt.setTimestamp(2, loadDate);
				/*pstmt.setTimestamp(3, loadDate);
				pstmt.setString(4, shift);*/
				rs=pstmt.executeQuery();
			}
			// now we have the calEndar in order just allocate For the no. of shifts
			for(int i=1; i<=noOfShiftsActual;i++){
				
				if(rs.next()){
					shift=checkNull(rs.getString(1));
					dayNo=checkNull(rs.getString(2));
					loadDate=rs.getTimestamp(3);
					inTime=checkNull(rs.getString(4));
					endTime=checkNull(rs.getString(5));					
				}
				machToolMap.put("mc_code", mcCode);
				machToolMap.put("item", item);				
				machToolMap.put("proc_code", procCode);
				machToolMap.put("schedule", loadDate);				
				machToolMap.put("workcentre", workctr);
				machToolMap.put("work_order", workorder);  
				
				machineLoadMap.put("site_code",siteCode);
				machineLoadMap.put("scheduled_date",loadDate);
				machineLoadMap.put("mc_code", mcCode);
				machineLoadMap.put("workcentre", workctr);
				machineLoadMap.put("day_no", dayNo);
				machineLoadMap.put("shift", shift);
				
				machineLoadMap.put("capacity_inqty", workOrderQty / noOfShiftsActual);
				machineLoadMap.put("loaded_inqty",  workOrderQty / noOfShiftsActual);
				
				machineLoadMap.put("shift_start_time",inTime);
				machineLoadMap.put("shift_end_time",endTime);
				machineLoadMap.put("loaded_intime",0);
				
				machineLoadMap.put("work_order", workorder);   
				machineLoadMap.put("route_code", routeCode);  
				machineLoadMap.put("operation", operation);
				machineLoadMap.put("proc_code", procCode);
				
				machineLoadMap.put("seq_no", seqNo);
				machineLoadMap.put("operation_level", operLevel);
				
				machineLoadMap.putAll(machToolMap);
				
				
				error=gbfToolAlloc(conn, machineLoadMap,1);
				System.out.println("Return string from gbfToolAlloc----->>["+error+"]");
				if(error > 0){
					conn.rollback();
					return "";
				}else if(error == 2){
					continue;
				}			
				
				//error in finding record in 'machine_load'.
				if(gbfMachineLoadUpdate(conn,machineLoadMap,1) == -1){					
					return "VTMCLHDR";
				}else if(gbfMachineLoadDetUpdate(conn,mcLoadMap,machineLoadMap,1) == -1){					
					return "VTMCDET";
				}
				
			}//end for loop		
			rs.close();			
			rs=null;
			pstmt.close();
			pstmt=null;	
				
			
			
			
		}catch(Exception e){
			System.out.println("Exception in gbfConfirmWoRouteAlt----- ");
			e.printStackTrace();
			errorString="Error";
			isError=true;
		}	
		return errorString;
		
		
	}
	/* Long(Right('00' + String((astruct.al_available_time - Mod(astruct.al_available_time,60))/60),2) + &
			   Right('00' + String(mod(astruct.al_available_time,60)),2))*/
	private int gbfMachineLoadUpdate(Connection conn,Map machineLoadMapL,int flag)
	{
		ResultSet rs1=null;
		PreparedStatement pstmt=null;
		int availableTime=0,cnt=0;
		String sql="",availTime="",availTime1="";
		
		//cnt=mcLoadMapL.size();
		
		try{
			//The below formula not understand still migrated(values always zero).
			availTime=	"00"+((availableTime - (availableTime % 60))/60);
			availTime=availTime.substring(availTime.length()-2,availTime.length());			
			availTime1="00"+availableTime % 60;
			availableTime=Integer.parseInt(availTime+availTime1);
		
			sql="insert into machine_load (site_code,load_date,work_ctr,mc_code,day_no,shift,capacity_inqty,"
					+ "loaded_inqty,shift_start_time,shift_end_time,available_time,loaded__intime,work_order)"
					+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, machineLoadMapL.get("site_code").toString());
			pstmt.setTimestamp(2, (Timestamp) machineLoadMapL.get("scheduled_date"));
			pstmt.setString(3, machineLoadMapL.get("workcentre").toString());
			pstmt.setString(4, machineLoadMapL.get("mc_code").toString());
			pstmt.setString(5, machineLoadMapL.get("day_no").toString());
			pstmt.setString(6, machineLoadMapL.get("shift").toString());
			pstmt.setDouble(7, Double.parseDouble(machineLoadMapL.get("capacity_inqty").toString()));
			pstmt.setDouble(8, Integer.parseInt(machineLoadMapL.get("loaded_inqty").toString()));
			pstmt.setString(9, machineLoadMapL.get("shift_start_time").toString());
			pstmt.setString(10, machineLoadMapL.get("shift_end_time").toString());
			pstmt.setInt(11, availableTime);
			pstmt.setInt(12, Integer.parseInt(machineLoadMapL.get("loaded_intime").toString()));
			pstmt.setString(13, machineLoadMapL.get("work_order").toString());
			
			pstmt.executeUpdate();
			pstmt.close();
			pstmt=null;
			
			System.out.println("In gbfMachineLoadUpdate flag---->>["+flag+"]");
			if(flag==2){
				sql="Insert Into machine_load_crp(site_code,load_date,work_ctr,mc_code,day_no,shift,"
						+ "work_order,route_code,operation,proc_code,seq_no,operation_level,capacity_inqty,"
						+ "loaded_inqty,shift_start_time,shift_end_time,available_time,loaded__intime) "
						+ "Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)";
				
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, machineLoadMapL.get("site_code").toString());
				pstmt.setTimestamp(2, (Timestamp) machineLoadMapL.get("scheduled_date"));
				pstmt.setString(3, machineLoadMapL.get("workcentre").toString());
				pstmt.setString(4, machineLoadMapL.get("mc_code").toString());
				pstmt.setString(5, machineLoadMapL.get("day_no").toString());
				pstmt.setString(6, machineLoadMapL.get("shift").toString());
				
				pstmt.setString(7, machineLoadMapL.get("work_order").toString());
				pstmt.setString(8, machineLoadMapL.get("route_code").toString());
				pstmt.setInt(9, Integer.parseInt(machineLoadMapL.get("operation").toString()));
				pstmt.setString(10, machineLoadMapL.get("proc_code").toString());
				pstmt.setInt(11, Integer.parseInt(machineLoadMapL.get("seq_no").toString()));
				pstmt.setInt(12, Integer.parseInt(machineLoadMapL.get("operation_level").toString()));
				pstmt.setDouble(13, Double.parseDouble(machineLoadMapL.get("capacity_inqty").toString()));
				pstmt.setDouble(14, Integer.parseInt(machineLoadMapL.get("loaded_inqty").toString()));
				
				pstmt.setString(15, machineLoadMapL.get("shift_start_time").toString());
				pstmt.setString(16, machineLoadMapL.get("shift_end_time").toString());
				pstmt.setInt(17, availableTime);
				pstmt.setInt(18, Integer.parseInt(machineLoadMapL.get("loaded_intime").toString()));	
				
				cnt=pstmt.executeUpdate();
				pstmt.close();
				pstmt=null;
				
			} //end flag cond.
			
		if(as_tool.size() > 0){	
		for(int j=0;j<as_tool.size();j++)
		{
			sql="insert into toolplan(load_date,shift,time_fr,time_to,tool_code,tool_sr,mc_code,work_ctr,"
					+ "work_order,proc_code,available_time,loaded__intime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setTimestamp(1, (Timestamp) machineLoadMapL.get("scheduled_date"));
			pstmt.setString(2, machineLoadMapL.get("shift").toString());
			pstmt.setString(3, machineLoadMapL.get("shift_start_time").toString());//time_fr 
			pstmt.setString(4, machineLoadMapL.get("shift_end_time").toString());//time_to  
			pstmt.setString(5, as_tool.get(j));
			pstmt.setInt(6, al_toolsr.get(j));
			pstmt.setString(7, machineLoadMapL.get("mc_code").toString());
			pstmt.setString(8, machineLoadMapL.get("workcentre").toString());
			pstmt.setString(9, machineLoadMapL.get("work_order").toString());
			pstmt.setString(10, machineLoadMapL.get("proc_code").toString());
			pstmt.setInt(11, availableTime);
			pstmt.setInt(12, Integer.parseInt(machineLoadMapL.get("loaded_intime").toString()));
			
			pstmt.executeUpdate();
			pstmt.close();
			pstmt=null;
			sql="Insert Into toolplan_crp(load_date,shift,mc_code,proc_code,work_ctr,tool_code,"
					+ "tool_sr,time_fr,time_to,work_order) "
					+ "Values(?,?,?,?,?,?,?,?,?,?,)";			
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setTimestamp(1, (Timestamp) machineLoadMapL.get("scheduled_date"));
			pstmt.setString(2, machineLoadMapL.get("shift").toString());
			pstmt.setString(3, machineLoadMapL.get("mc_code").toString());
			pstmt.setString(4, machineLoadMapL.get("proc_code").toString());
			pstmt.setString(5, machineLoadMapL.get("workcentre").toString());
			pstmt.setString(6, as_tool.get(j));
			pstmt.setInt(7, al_toolsr.get(j));
			pstmt.setString(8, machineLoadMapL.get("shift_start_time").toString());//time_fr 
			pstmt.setString(9, machineLoadMapL.get("shift_end_time").toString());//time_to 
			pstmt.setString(10, machineLoadMapL.get("work_order").toString());
			
			pstmt.executeUpdate();
			pstmt=null;
			
			
		}
		}	
		
	   }catch(Exception e){
		   System.out.println("Exception in gbfMachineLoadUpdate method......");			
			e.printStackTrace();	
			return -1;
			
		}
		return 1;
	}
	private int gbfMachineLoadDetUpdate(Connection conn,Map mcLoadMapL,Map machineLoadMapL,int flag)
	{
		PreparedStatement pstmt=null;
		int cnt=0;
		String sql="";
		try{
			sql="insert into machine_load_det (site_code,work_ctr,mc_code,day_no,shift,work_order,"
					+ "route_code,operation,proc_code,seq_no,operation_level,load_date,"
					+ "shift_start_time,shift_end_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, machineLoadMapL.get("site_code").toString());
			pstmt.setString(2, machineLoadMapL.get("workcentre").toString());
			pstmt.setString(3, machineLoadMapL.get("mc_code").toString());
			pstmt.setString(4, machineLoadMapL.get("day_no").toString());
			pstmt.setString(5, machineLoadMapL.get("shift").toString());
			
			pstmt.setString(6, machineLoadMapL.get("work_order").toString());
			pstmt.setString(7, machineLoadMapL.get("route_code").toString());
			pstmt.setInt(8, Integer.parseInt(machineLoadMapL.get("operation").toString()));
			pstmt.setString(9, machineLoadMapL.get("proc_code").toString());
			pstmt.setInt(10, Integer.parseInt(machineLoadMapL.get("seq_no").toString()));
			pstmt.setInt(11, Integer.parseInt(machineLoadMapL.get("operation_level").toString()));
			pstmt.setTimestamp(12, (Timestamp) machineLoadMapL.get("scheduled_date"));
			pstmt.setString(13, machineLoadMapL.get("shift_start_time").toString());
			pstmt.setString(14, machineLoadMapL.get("shift_end_time").toString());
			
			cnt=pstmt.executeUpdate();
			pstmt.close();
			pstmt=null;
			
		
		}
		catch(Exception e){
			   System.out.println("Exception in gbfMachineLoadUpdate method......");			
				e.printStackTrace();				
		}
		return cnt;
		
	}
	
	// This method allocates the Tools if available for the specified  Machine and Item
	// Returns  1 if successful
	// Returns -1 if unsuccessful
	// Returns  0 if tool is not allocated and it is not necessary
	// Returns  2 if tool cannot be allocated but it is necessary
	private int gbfToolAlloc(Connection conn,HashMap machineLoadMap,int flag)
	{
		as_tool.clear();
		ResultSet rs=null,rs1=null;
		PreparedStatement pstmt=null,pstmt1=null;
		double capacity=0;
		String sql="",sql1="",errorString="",toolCode="",oldToolCode="",option="";
		int availUnits=0,reqUnits=0,bookedUnits=0,units=0,cnt=0,countSrNo=0;
		boolean isRecordFound=false; 		
		Map <Integer,String> ls_tool=new TreeMap<Integer,String>();
		Map<Integer,Integer> ll_cnt=new TreeMap<Integer,Integer>();
		Map<Integer,Integer> ai_toolsr=new TreeMap<Integer,Integer>();
		
		int ll_oldctr=0,ll_ctr=0,ll_count=0;
		
		try{
			sql="Select A.tool_code,B.req_units,A.capacity From MachineTools A,Toolitem B,Tool C"
					+ " Where A.tool_code = B.tool_code And B.tool_code = C.tool_code "
					+ "And C.alloc_tool	= '1' And A.mc_code = ? And B.item_code  = ? "
					+ "Order by A.pref_seq";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, machineLoadMap.get("mc_code").toString());
			pstmt.setString(2, machineLoadMap.get("item").toString());
			
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				isRecordFound=true;				
				reqUnits=rs.getInt(2);
				capacity=rs.getDouble(3);
				//check the nos of available units.
				sql1="Select avail_units From Tool Where tool_code = ?";
				pstmt1=conn.prepareStatement(sql1);
				pstmt1.setString(1, ls_tool.get(ll_ctr));
				rs1=pstmt1.executeQuery();
				if(rs1.next()){
					availUnits=rs1.getInt(1);
				}
				
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				
				//Checking if the tool is required for this process
				sql1="Select req_type From ProcessTool Where proc_code =  ? and "
						+ "tool_code = ? and alloc = '1'";
				pstmt1=conn.prepareStatement(sql1);
				pstmt1.setString(1, machineLoadMap.get("proc_code").toString());
				pstmt1.setString(2, ls_tool.get(ll_ctr));
				rs1=pstmt1.executeQuery();
				int temp=0;
				if(rs1.next()){
					temp++;
					option=checkNull(rs1.getString(1));
				}
				if(temp == 0){
					as_tool.put(ll_ctr,"");
					return 0;					
				}
				System.out.println("option for check length------->>["+option+"]");		
				
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				//For Loop is for allocating units according to the units required.
				ll_oldctr = ll_ctr;
				
				for(ll_count=1;ll_count<=reqUnits;ll_count++){
					//check the number of units that have been booked already.
					sql1="Select count(1) From Toolplan Where tool_code = ? and load_date = ? and "
							+ "shift = ? and available_time = ?";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setString(1, ls_tool.get(ll_oldctr));
					pstmt1.setTimestamp(2, (Timestamp)machineLoadMap.get("schedule"));
					pstmt1.setString(3, machineLoadMap.get("shift").toString());
					pstmt.setInt(4, 0);
					rs1=pstmt1.executeQuery();
					if(rs1.next()){
						bookedUnits=rs.getInt(1);
					}
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;
					//if the nos of units used are more than available then do not allocate tool
					units = availUnits - bookedUnits;
					if(units <= 0){
						as_tool.put(ll_oldctr,"");
						//if option is 'C' then the tool is critical and must be allocated
						//else skip that tool and select the next tool
						if("C".equalsIgnoreCase(option)){
							sql1="Delete From Toolplan Where load_date = ? and shift = ? and mc_code = ? "
									+ "and proc_code = ? and work_ctr = ? and work_order = ?";
							pstmt1=conn.prepareStatement(sql1);
							pstmt1.setTimestamp(1,(Timestamp)machineLoadMap.get("schedule"));
							pstmt1.setString(2, machineLoadMap.get("shift").toString());
							pstmt.setString(3, machineLoadMap.get("mc_code").toString());
							pstmt1.setString(4, machineLoadMap.get("proc_code").toString());
							pstmt1.setString(5, machineLoadMap.get("workcentre").toString());
							pstmt1.setString(6, machineLoadMap.get("work_order").toString());
							
							cnt=pstmt1.executeUpdate();
							System.out.println("No of rows delete from toolplan from C---->>["+cnt+"]");							
							pstmt1.close();
							pstmt1=null;
							System.out.println("return from C cond.----------->>2");
							return 2;
							
						}else{  //end C/option cond.
							ll_ctr = ll_ctr - 1;
							continue;
						}
					}//end unit cond.
					ll_cnt.put(ll_count,0);
					//ll_cnt[ll_count] = 0 ------>>>>>>> not created
					//get the first available tool, i.e. which is not allocated in Toolplan
					sql1="Select Case when Min(A.sr_no) is null then 1 else Min(A.sr_no) end from Tooldet A "
							+ "Where  A.tool_code = ? and Not Exists (Select 1 from ToolPlan B "
							+ "Where  B.tool_code = A.tool_code and B.tool_sr = A.sr_no and "
							+ "B.load_date 	=  ? and B.shift = ? and B.tool_code = ?";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setTimestamp(1,(Timestamp)machineLoadMap.get("schedule"));
					pstmt1.setString(2, machineLoadMap.get("shift").toString());
					pstmt1.setString(3, ls_tool.get(ll_oldctr)); 
					rs1=pstmt1.executeQuery();
					if(rs1.next()){
						countSrNo=rs.getInt(1);
						as_tool.put(ll_ctr,ls_tool.get(ll_oldctr));//as_tool[ll_ctr] 	= ls_tool[ll_oldctr]
						al_toolsr.put(ll_ctr,ll_cnt.get(ll_count));
						
						//ai_toolsr[ll_ctr] = ll_cnt[ll_count]
						
					}
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;
					
					//Inserting the record temporarily so that tool is allocated properly
					//the same is deleted below
					sql1="Insert Into Toolplan(load_date,shift,mc_code,proc_code,work_ctr,tool_code,"
							+ "tool_sr,work_order,available_time) Values(?,?,?,?,?,?,?,?,?)";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setTimestamp(1,(Timestamp)machineLoadMap.get("schedule"));
					pstmt1.setString(2, machineLoadMap.get("shift").toString());
					pstmt.setString(3, machineLoadMap.get("mc_code").toString());
					pstmt1.setString(4, machineLoadMap.get("proc_code").toString());
					pstmt1.setString(5, machineLoadMap.get("workcentre").toString());					
					pstmt1.setString(6, toolCode);
					pstmt1.setInt(7, countSrNo);
					pstmt1.setString(8, machineLoadMap.get("work_order").toString());
					pstmt1.setInt(9, 0);
					
					cnt=pstmt1.executeUpdate();
					System.out.println("---------row inserted in toolplane table------"+cnt);
					pstmt1.close();
					pstmt1=null;
					
				} //end for loop
				
			} //end while
			//when no record found then return 0;
			if(!(isRecordFound)){
				as_tool.put(ll_ctr,"");
				return 0;
			}
			
			//delete the row inserted above.only the row inserted for work order is deleted 
			//and for mps order the row is deleted is the window.
			if(flag ==1){
				sql1="Delete From Toolplan Where load_date = ? and shift = ? and mc_code = ? "
						+ "and proc_code = ? and work_ctr = ? and work_order = ?";
				pstmt1=conn.prepareStatement(sql1);
				pstmt1.setTimestamp(1,(Timestamp)machineLoadMap.get("schedule"));
				pstmt1.setString(2, machineLoadMap.get("shift").toString());
				pstmt.setString(3, machineLoadMap.get("mc_code").toString());
				pstmt1.setString(4, machineLoadMap.get("proc_code").toString());
				pstmt1.setString(5, machineLoadMap.get("workcentre").toString());
				pstmt1.setString(6, machineLoadMap.get("work_order").toString());
				
				cnt=pstmt1.executeUpdate();
				System.out.println("No of rows delete from toolplan from flag---->>["+cnt+"]");
			}
			
		}
		catch(Exception e){
			e.printStackTrace();
			try{
			conn.rollback();
			}
			catch(Exception e1){
				System.out.println("Exception while rollbacking........");
				e1.printStackTrace();
			}
			return -1;
		}
		return 1;
	}
	private int updateOnConfirmation(Connection conn,Map<String,String>updateParamMapL)
	{
		System.out.println("updateOnConfirmation--->>["+updateParamMapL+"]");		
		PreparedStatement pstmt=null;
		int count=0;
		String sql = "",sql1="";
		try{
			//sql="update worder_route set mc_code = ? where work_order = ? and operation = ?";
		sql="update worder_route set mc_code = ?,item_code = ?,proc_code = ?,wait_time = ?,set_time = ?,"
				+ "run_time = ?,reset_time = ?,work_ctr = ?,dept_code = ? where work_order = ? and operation = ?";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, updateParamMapL.get("mcCode"));
		pstmt.setString(2, updateParamMapL.get("itemCode"));
		pstmt.setString(3, updateParamMapL.get("procCode"));
		pstmt.setDouble(4, Double.parseDouble(updateParamMapL.get("waitT")));
		pstmt.setDouble(5, Double.parseDouble(updateParamMapL.get("setT")));
		pstmt.setDouble(6, Double.parseDouble(updateParamMapL.get("runT")));
		pstmt.setDouble(7, Double.parseDouble(updateParamMapL.get("resetT")));
		pstmt.setString(8, updateParamMapL.get("workCtr"));
		
		pstmt.setString(9, updateParamMapL.get("deptCode"));
		
		pstmt.setString(10, updateParamMapL.get("worder"));
		pstmt.setInt(11, Integer.parseInt(updateParamMapL.get("operation")));
		
		
		count=pstmt.executeUpdate();
		if(pstmt!=null){
		pstmt.close();
		pstmt=null;
		}
		System.out.println("First count in updateOnConfirmation11-->>["+count+"]");
		
		sql1="update worder_route_alt set confirmed = ?,conf_date = sysdate,emp_code__aprv = ?"
				+ " where amd_no = ?";
		pstmt=conn.prepareStatement(sql1);
		pstmt.setString(1, updateParamMapL.get("confirmed"));
		pstmt.setString(2, updateParamMapL.get("loginEmpCode"));
		pstmt.setString(3, updateParamMapL.get("amdNo"));
	
		count=pstmt.executeUpdate();
		System.out.println("Second count in updateOnConfirmation-->>["+count+"]");
		
		}
		catch(Exception e)
		{		
			System.out.println("Exception in updateOnConfirmation----");
			e.printStackTrace();
			isError=true;
			return -1;			
		}
		return count;
		
	}
	private String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input.trim();
	}
	
}
