
package ibase.webitm.utility.wms;
import ibase.scheduler.utility.interfaces.Schedule;
import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.sql.*;
import java.io.*;

import org.omg.CORBA.ORB;
import org.w3c.dom.*;

import java.util.Properties;

import javax.swing.text.NumberFormatter;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.InvHoldGen;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.BaseException;
import ibase.utility.CommonConstants;
import ibase.ejb.*;
import ibase.system.config.*;

import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.math.*;
import java.net.InetAddress;


public class NearExpirySchedule implements Schedule
{
	
	//GenericUtility genericUtility = GenericUtility.getInstance();
	//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//ConnDriver connDriver = new ConnDriver();
	//CommonConstants commonConstants = new CommonConstants();
	String chgUser = null;
	String chgTerm = null;
	static long count_records=0;
	boolean isError = false;

	public String schedulePriority( String wrkflwPriority )throws Exception
	{
		return "";
	}
	public String schedule(HashMap map)throws Exception
	{
		return "";
	}
	public String schedule(String scheduleParamXML)throws Exception
	{
		String siteCode = "";
		ibase.utility.UserInfoBean userInfo = null;
		try
		{
			System.out.println("************ ["+scheduleParamXML+"]");
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			siteCode = userInfo.getSiteCode();
			System.out.println("Site code = "+siteCode);
			insNearExpProducts(siteCode);			
			//insNearExpProducts("TA821");
		}
		catch(Exception e)
		{
			throw new Exception(e);
		}
		return "";
	}
	public String insNearExpProducts(String siteCode) throws RemoteException,ITMException
	{
		String salesPerson = "";
		String itemCode = "";
		String retString = "";
		String selectSql = "";
		String locCode = "";
		//String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		String dataVal1 = "";
		String dataVal2 = "";
		String reservLoc = "";
		String lockORTransfer = "";
		String nexpLoc = "";
		String expLoc = "";
		String tranID = "";
		String loginSiteCode = "";
		String userId = "";
		String chgTerm = "";
		String acctCodeInv = "";
		String cctrCodeInv = "";
		String insertSql = "";
		String defReasCode = "";
		//Changed by sumit sarkar
		String dStoreInvstat = "";

		int count = 0;
		int updCnt = 0;
		int lineNo = 0;

		double quantity = 0d;
		double allocQty = 0d;
		double noArt = 0d;
		double minSelfLife = 0d;

		boolean isError = false;
		
		Connection conn = null;
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtItem = null;

		HashMap strAllocate = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();

		try
		{
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			
			DistCommon distComm = new DistCommon();
			
			//Changed By Pragyan as the veriable name RESERV_LOC  has been changed to RESERV_LOCATION
			//reservLoc = distComm.getDisparams("999999","RESERV_LOC",conn);
			reservLoc = distComm.getDisparams("999999","RESERV_LOCATION",conn);
			lockORTransfer = distComm.getDisparams("999999","LOCK_OR_TRANSFER",conn);
			//Changed by sumit sarkar on 24/06/12 uncomment below code and change var_name in parameter. start.
			//nexpLoc = distComm.getDisparams("999999","NEAREXP_LOC",conn);
			//expLoc = distComm.getDisparams("999999","EXP_LOC",conn);
			nexpLoc = distComm.getDisparams("999999","NEAR_EXP_LOCK",conn);
			expLoc = distComm.getDisparams("999999","EXP_LOCK",conn);
			dStoreInvstat = distComm.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
			System.out.println("nexpLoc ->["+nexpLoc+"] expLoc ->["+expLoc+"]");
			//Changed by sumit sarkar on 24/06/12 uncomment below code and change var_name in parameter. start.

			/*dataVal1 = genericUtility.getColumnValue("lock_code_exp",hdrDom);
			dataVal2 = genericUtility.getColumnValue("lock_code_nexp",hdrDom);
			siteCode = genericUtility.getColumnValue("site_code",hdrDom);
			*/
		
			if ( "T".equalsIgnoreCase(lockORTransfer.trim()) )//For Transger
			{
				dataVal1 = distComm.getDisparams("999999","NEAREXP_LOC",conn);
				dataVal2 = distComm.getDisparams("999999","EXP_LOC",conn);
			}
			else if ( "L".equalsIgnoreCase(lockORTransfer.trim()) )//For Locking
			{
				dataVal1 = distComm.getDisparams("999999","NEAR_EXP_LOCK",conn);
				dataVal2 = distComm.getDisparams("999999","EXP_LOCK",conn);
			}

			//Changed by sumit sarkar on 21/06/12 insert CHECK_PHYSCAN_VALUE function in sql start.
			/*selectSql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.NO_ART, " +
						"C.ITEM_SER, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
						"A.REMARKS, D.INV_STAT, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE," +
						"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
						"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV "+
						"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
						"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
						"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
						"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
						//"AND (A.QUANTITY - A.ALLOC_QTY - CASE WHEN A.HOLD_QTY IS NULL THEN 0 ELSE A.HOLD_QTY END) > 0 "+ //Commented by gulzar on 1/12/2012
						"AND (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) > 0 "+//Changed by gulzar on 1/12/2012
						"AND D.INV_STAT = ? AND A.SITE_CODE = ? AND A.EXP_DATE IS NOT NULL ";*/
			
			
			selectSql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.NO_ART, " +
			"C.ITEM_SER, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
			"A.REMARKS, D.INV_STAT, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE," +
			"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
			"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV "+
			"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
			"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
			"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
			"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
			//"AND (A.QUANTITY - A.ALLOC_QTY - CASE WHEN A.HOLD_QTY IS NULL THEN 0 ELSE A.HOLD_QTY END) > 0 "+ //Commented by gulzar on 1/12/2012
			"AND (CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) > 0 "+//Changed by gulzar on 1/12/2012
			//Changed by sumit sarkar on 24/06/12 putting condition of dStoreInvstat (DSO)
			//"AND D.INV_STAT = ? AND A.EXP_DATE IS NOT NULL "+
			"AND D.INV_STAT IN (?, ?) AND A.EXP_DATE IS NOT NULL "+
			" AND nvl(CHECK_PHYSCAN_VALUE(A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL, ? , ?),0) <> 1";
			
			//Changed by sumit sarkar on 21/06/12 insert CHECK_PHYSCAN_VALUE function in sql end.
			
			pstmtStock = conn.prepareStatement(selectSql);
			pstmtStock.setString(1, reservLoc);
			//Changed by sumit sarkar on 22/06/12 to comment bellow parameter and add nexpLoc, expLoc parameter start.
			//pstmtStock.setString(2, siteCode);
			pstmtStock.setString(2, dStoreInvstat);
			pstmtStock.setString(3, expLoc);
			pstmtStock.setString(4, nexpLoc);			
			//Changed by sumit sarkar on 22/06/12 to comment bellow parameter and add nexpLoc, expLoc parameter end.
			rsStock = pstmtStock.executeQuery();

			InvHoldGen holdGen = new InvHoldGen();
			ArrayList stockExpList = new ArrayList();
			ArrayList stockNexpList = new ArrayList();
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.sql.Timestamp expDateCalc = null;
			java.sql.Timestamp expDateStock = null;

			selectSql = "SELECT MIN_SHELF_LIFE FROM ITEM WHERE ITEM_CODE = ? ";
			pstmtItem = conn.prepareStatement(selectSql);

			while (rsStock.next())
			{
				HashMap dataMap = new HashMap();
				itemCode = rsStock.getString("ITEM_CODE");
				siteCode = rsStock.getString("SITE_CODE");
				locCode = rsStock.getString("LOC_CODE");
				lotNo = rsStock.getString("LOT_NO");
				lotSl = rsStock.getString("LOT_SL");
				expDateStock = rsStock.getTimestamp("EXP_DATE");
				quantity = rsStock.getDouble("QUANTITY");
				allocQty = rsStock.getDouble("ALLOC_QTY");
				noArt = rsStock.getDouble("NO_ART");
				acctCodeInv = rsStock.getString("ACCT_CODE__INV");
				cctrCodeInv = rsStock.getString("CCTR_CODE__INV");
				
				if ( currDate.compareTo(expDateStock) >= 0 )//Check for Expired stock
				{
					count++;
					dataMap.put( "item_code", itemCode );
					dataMap.put( "site_code", siteCode );
					dataMap.put( "loc_code", locCode );
					dataMap.put( "lot_no", lotNo );
					dataMap.put( "lot_sl", lotSl );
					dataMap.put( "loc_code__to", dataVal1 );//expired location to which stock will be transfer
					dataMap.put( "quantity", ""+quantity );
					dataMap.put( "alloc_qty", ""+allocQty );
					dataMap.put( "no_art", ""+noArt );
					dataMap.put( "acct_code__inv", acctCodeInv );
					dataMap.put( "cctr_code__inv", cctrCodeInv );
					stockExpList.add(dataMap);
				}
				else//Check for Near Expiry stock
				{
					pstmtItem.setString( 1, itemCode );
					rsItem = pstmtItem.executeQuery();
					if ( rsItem.next() )
					{
						minSelfLife = rsItem.getDouble("MIN_SHELF_LIFE");
					}
					pstmtItem.clearParameters();
					if ( minSelfLife == 0 )
					{
						continue;
					}
					expDateCalc = distComm.CalcExpiry( currDate, minSelfLife );
					if ( expDateCalc.compareTo(expDateStock) >= 0 )
					{
						count++;
						dataMap.put( "item_code", itemCode );
						dataMap.put( "site_code", siteCode );
						dataMap.put( "loc_code", locCode );
						dataMap.put( "lot_no", lotNo );
						dataMap.put( "lot_sl", lotSl );
						dataMap.put( "loc_code__to", dataVal2 );//near expiry location to which stock will be transfer
						dataMap.put( "quantity", ""+quantity );
						dataMap.put( "alloc_qty", ""+allocQty );
						dataMap.put( "no_art", ""+noArt );
						dataMap.put( "acct_code__inv", acctCodeInv );
						dataMap.put( "cctr_code__inv", cctrCodeInv );
						stockNexpList.add(dataMap);
					}
				}
			}
			System.out.println("count = ["+count+"]");
			if ( !isError && count > 0 )
			{
				//siteCode = genericUtility.getColumnValue("site_code",hdrDom);
				if ( "L".equalsIgnoreCase(lockORTransfer.trim()) ) //For Locking
				{
					//calling for Expiry lock
					if ( stockExpList.size() > 0 )
					{
						retString = holdGen.generateHoldTrans( dataVal1, "EXPIRED","EXP", siteCode, stockExpList, "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
					}
					else 
					{
						//calling for Near Expiry lock
						if ( stockNexpList.size() > 0 )
						{
							retString = holdGen.generateHoldTrans( dataVal2, "NEAREXPIRY","NEXP", siteCode, stockNexpList, "", conn );
						}
						if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
						{
							isError = true;
						}
					}
				}
				else if ( "T".equalsIgnoreCase(lockORTransfer.trim()) ) //For Transfer
				{
					if ( stockExpList.size() > 0 )
					{
						retString = inventoryTransfer( stockExpList, "w_stock_transfer", "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
						return retString;
					}
					if ( stockNexpList.size() > 0 )
					{
						retString = inventoryTransfer( stockNexpList, "w_stock_transfer", "", conn );
					}
					if ( retString != null && retString.trim().length() > 0 && retString.trim().equalsIgnoreCase("ERROR") )
					{
						isError = true;
						//return retString;
					}
				}
			}
		}
		catch(SQLException se)
		{
			isError = true;
			System.out.println("Exception : NearExpiryPrc : process()" +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :NearExpiryPrc :process():"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				invAllocTrace = null;
				if ( count == 0 )
				{
					retString = itmDBAccess.getErrorString("","NODATAERR","");
				}
				else if ( !isError )
				{
					System.out.println("COMMITING the changes......");
					conn.commit();
					retString = itmDBAccess.getErrorString("","PRCSUCCESS","");
				}
				else
				{
					System.out.println("ROLLBACK the changes......");
					conn.rollback();
					retString = itmDBAccess.getErrorString("","PRCFAILED","");
				}
				System.out.println("Closing Database Connection......");
				if (rsItem != null)
				{
					rsItem.close();
					rsItem = null;
				}
				if (pstmtItem != null)
				{
					pstmtItem.close();
					pstmtItem = null;
				}
				if (rsStock != null)
				{
					rsStock.close();
					rsStock = null;
				}
				if (pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if ( conn != null && !conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	public String inventoryTransfer( ArrayList stockList, String windowName,String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String salesPerson = "";
		String itemCode = "";
		String retString = "";
		String selectSql = "";
		String locCode = "";
		String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		String locCodeTo = "";
		String reservLoc = "";
		String nexpLoc = "";
		String expLoc = "";
		String tranID = "";
		String loginSiteCode = "";
		String userId = "";
		String chgTerm = "";
		String acctCodeInv = "";
		String cctrCodeInv = "";
		String insertSql = "";
		String defReasCode = "";

		int count = 0;
		int updCnt = 0;
		int lineNo = 0;

		double quantity = 0d;
		double allocQty = 0d;
		double noArt = 0d;
		double minSelfLife = 0d;

		boolean isError = false;
		
		ResultSet rsStock = null;
		ResultSet rsItem = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtItem = null;
		PreparedStatement pstmtHdr = null;
		PreparedStatement pstmtDet = null;

		HashMap strAllocate = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();

		try
		{
			DistCommon distComm = new DistCommon();
			defReasCode = distComm.getDisparams("999999","DEFAULT_REAS_CODE",conn);

			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.sql.Timestamp expDateCalc = null;
			java.sql.Timestamp expDateStock = null;

			insertSql = "INSERT INTO STOCK_TRANSFER(TRAN_ID, TRAN_DATE, SITE_CODE, REF_SER__FOR, CONFIRMED, CHG_DATE, CHG_TERM, CHG_USER, SITE_CODE__FR, SITE_CODE__TO, REAS_CODE) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
			pstmtHdr = conn.prepareStatement(insertSql);

			insertSql = "INSERT INTO STOCK_TRANSFER_DET(TRAN_ID, LINE_NO, ITEM_CODE, QUANTITY, LOC_CODE__FR, LOC_CODE__TO, LOT_NO__FR, LOT_NO__TO, LOT_SL__FR, LOT_SL__TO, ACCT_CODE__CR, ACCT_CODE__DR, CCTR_CODE__DR, CCTR_CODE__CR, NO_ART) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmtDet = conn.prepareStatement(insertSql);
			
			//generate the tran id for w_stock_transfer
			tranID = generateTranId( "w_stock_transfer", conn );
			
			//Insert into header table i.e stock_transfer
			pstmtHdr.setString( 1, tranID );
			pstmtHdr.setTimestamp( 2, currDate );
			pstmtHdr.setString( 3, loginSiteCode );
			pstmtHdr.setString( 4, "XFRX" );
			pstmtHdr.setString( 5, "N" );
			pstmtHdr.setTimestamp( 6, currDate );
			pstmtHdr.setString( 7, chgTerm );
			pstmtHdr.setString( 8, userId );
			pstmtHdr.setString( 9, siteCode );
			pstmtHdr.setString( 10, siteCode );
			pstmtHdr.setString( 11, defReasCode );
			updCnt = pstmtHdr.executeUpdate();
			pstmtHdr.close();
			pstmtHdr = null;

			for ( int ctr = 0; ctr < stockList.size(); ctr++ )
			{
				lineNo++;
				HashMap dataMap = null;
				dataMap = (HashMap)stockList.get(ctr);
				
				itemCode = "";
				siteCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				acctCodeInv = "";
				cctrCodeInv = "";
				locCodeTo = "";

				quantity = 0d;
				allocQty = 0d;
				noArt = 0d;
				
				if ( dataMap.get("item_code") != null )
				{
					itemCode = (String)dataMap.get("item_code");
				}
				if ( dataMap.get("site_code") != null )
				{
					siteCode = (String)dataMap.get("site_code");
				}
				if ( dataMap.get("loc_code") != null )
				{
					locCode = (String)dataMap.get("loc_code");
				}
				if ( dataMap.get("loc_code__to") != null )
				{
					locCodeTo = (String)dataMap.get("loc_code__to");
				}
				if ( dataMap.get("lot_no") != null )
				{
					lotNo = (String)dataMap.get("lot_no");
				}
				if ( dataMap.get("lot_sl") != null )
				{
					lotSl = (String)dataMap.get("lot_sl");
				}
				if ( dataMap.get("quantity") != null )
				{
					quantity = Double.parseDouble(dataMap.get("quantity").toString());
				}
				if ( dataMap.get("alloc_qty") != null )
				{
					allocQty = Double.parseDouble(dataMap.get("alloc_qty").toString());
				}
				if ( dataMap.get("no_art") != null )
				{
					noArt = Double.parseDouble(dataMap.get("no_art").toString());
				}
				if ( dataMap.get("acct_code__inv") != null )
				{
					acctCodeInv = (String)dataMap.get("acct_code__inv");
				}
				if ( dataMap.get("cctr_code__inv") != null )
				{
					cctrCodeInv = (String)dataMap.get("cctr_code__inv");
				}
				pstmtDet.setString( 1, tranID );
				pstmtDet.setInt( 2, lineNo );
				pstmtDet.setString( 3, itemCode );
				pstmtDet.setDouble( 4, (quantity - allocQty) );
				pstmtDet.setString( 5, locCode );
				pstmtDet.setString( 6, locCodeTo );//expired / Near Expiry location to which stock will be transfer
				pstmtDet.setString( 7, lotNo );
				pstmtDet.setString( 8, lotNo );
				pstmtDet.setString( 9, lotSl );
				pstmtDet.setString( 10, lotSl );
				pstmtDet.setString( 11, acctCodeInv );
				pstmtDet.setString( 12, acctCodeInv );
				pstmtDet.setString( 13, cctrCodeInv );
				pstmtDet.setString( 14, cctrCodeInv );
				pstmtDet.setDouble( 15, noArt );

				pstmtDet.addBatch();
				pstmtDet.clearParameters();
				dataMap.clear();

				strAllocate = new HashMap();

				strAllocate.put("tran_date",currDate);
				strAllocate.put("ref_ser","XFRX");
				strAllocate.put("ref_id",tranID);
				strAllocate.put("ref_line", "" + lineNo);
				strAllocate.put("site_code",siteCode);
				strAllocate.put("item_code",itemCode);
				strAllocate.put("loc_code",locCode);
				strAllocate.put("lot_no",lotNo);
				strAllocate.put("lot_sl",lotSl);
				strAllocate.put("alloc_qty",new Double(quantity));
				strAllocate.put("chg_user",userId);
				strAllocate.put("chg_term",chgTerm);
				strAllocate.put("chg_win","w_stock_transfer");

				retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
				strAllocate = null;
				System.out.println("retString ::: " + retString);
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					retString = "ERROR";
					return retString;
				}
			}
			pstmtDet.executeBatch();
			pstmtDet.clearBatch();
		}
		catch(BatchUpdateException buex)
        {
			isError = true;
			retString = "ERROR";
			int [] updateCounts = buex.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++)
			{
				System.err.println("  Statement " + i + ":" + updateCounts[i]);
			}
			System.err.println(" Message: " + buex.getMessage());
			System.err.println(" SQLSTATE: " + buex.getSQLState());
			System.err.println(" Error code: " + buex.getErrorCode());
			SQLException ex = buex.getNextException();
			while (ex != null)
			{ 
				System.err.println("SQL exception:");
				System.err.println(" Message: " + ex.getMessage());
				System.err.println(" SQLSTATE: " + ex.getSQLState());
				System.err.println(" Error code: " + ex.getErrorCode());
				ex = ex.getNextException();
			}
        }
		catch(SQLException se)
		{
			isError = true;
			retString = "ERROR";
			System.out.println("Exception : NearExpiryPrc : process()" +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			//throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			retString = "ERROR";
			System.out.println("Exception :NearExpiryPrc :process():"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			//throw new ITMException(e);
		}
		finally
		{
			try
			{
				invAllocTrace = null;
				strAllocate = null;
				if ( isError )
				{
					System.out.println("ROLLBACK the changes......");
					conn.rollback();
					retString = "ERROR";
				}
				System.out.println("Closing Database Connection......");
				if (rsItem != null)
				{
					rsItem.close();
					rsItem = null;
				}
				if (pstmtItem != null)
				{
					pstmtItem.close();
					pstmtItem = null;
				}
				if (rsStock != null)
				{
					rsStock.close();
					rsStock = null;
				}
				if (pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if (pstmtHdr != null)
				{
					pstmtHdr.close();
					pstmtHdr = null;
				}
				if (pstmtDet != null)
				{
					pstmtDet.close();
					pstmtDet = null;
				}

			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	private String generateTranId( String windowName, Connection conn )throws ITMException
    {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";

		java.sql.Date effDate = null;

		 try
         {
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
				//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
					keyString = rs.getString("KEY_STRING");
					keyCol = rs.getString("TRAN_ID_COL");
					tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
         }
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e){}
		}
        return tranId;
     }//generateTranTd()	
	private Connection getConnection()throws BaseException
	{
		Connection con = null;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			con = connDriver.getConnectDB(CommonConstants.WRKFLW_DB);
		}
		catch(BaseException be)
		{
			throw be;
		}
		catch (Exception e)
		{
			throw new BaseException(e);
		}
		return con;
	}

	
	/*
	public String insertSchedule1() throws RemoteException,ITMException
	{
		String errString = "";
		PreparedStatement pstmt = null;
	    Connection conn = null;
	    String tranId = "";
	    String sSQL = "";
	    String siteCode = "";
	    String dateFrom = "",dateTo = "", invStat = "";
	    String confirmed = "N";
	    String itemCode = "",locCode = "",lotNo = "",lotSl = "", prioritySeq = "", priorityDate = "" ;
	    String childNodeName = "";
	    String analysisClass = ""; 

		boolean isError = false;

	    int parentNodeListLength = 0;
	    int lineNo = 0;

	    java.sql.Date currDate = new java.sql.Date( System.currentTimeMillis() );

	    NodeList parentNodeList = null, childNodeList  = null;
	    Node parentNode = null, childNode = null;

	    ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

		try
		{
			/*chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");			
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			
			siteCode = genericUtility.getColumnValue("site_code", headerDom);
			sSQL =  "INSERT INTO ITEM_PROCESS (TRAN_DATE, ITEM_CODE, " +
					"analysis_class, LOC_CODE,SITE_CODE,CHG_USER, CHG_TERM, CHG_DATE,priority_seq) VALUES (?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sSQL);
			for(int row = 0; row < parentNodeList.getLength(); row++)
			{
				parentNode = parentNodeList.item(row);
				childNodeList = parentNode.getChildNodes();
				
				itemCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				itemCode = "";
				locCode = "";
				lotNo = "";
				lotSl = "";
				
				
				System.out.println("************* first For loop *************["+parentNode.getNodeName()+"]");
				System.out.println("************* first For loop *************["+childNodeList.toString()+"]");
				System.out.println("childNodeList.getLength()=="+childNodeList.getLength());
				for(int col = 0; col < childNodeList.getLength(); col++)
				{
					childNode = childNodeList.item(col);
					
					if(childNode != null && childNode.getNodeType() == childNode.ELEMENT_NODE  )
					{
						childNodeName = childNode.getNodeName();
						
						if("priority_seq".equalsIgnoreCase(childNodeName))
						{
							prioritySeq = childNode.getFirstChild().getNodeValue();
							prioritySeq = prioritySeq != null ? prioritySeq : "";
							System.out.println("prioritySeq-->"+prioritySeq);							
						}
						
						else if("analysis_class".equalsIgnoreCase(childNodeName))
						{
							analysisClass = childNode.getFirstChild().getNodeValue();
							
							analysisClass = analysisClass != null ? analysisClass : "";
							System.out.println("*********analysisClass-->"+analysisClass);
						}
						else if("item_code".equalsIgnoreCase(childNodeName))
						{
							
							itemCode = childNode.getFirstChild().getNodeValue();
							itemCode = itemCode != null ? itemCode : "";
							
							System.out.println("itemCode-->["+itemCode+"]");
						}
						
						else if("site_code".equalsIgnoreCase(childNodeName))
						{
							siteCode ="";
							if (childNode.getFirstChild() != null)
							{
								siteCode = childNode.getFirstChild().getNodeValue();
							}
							System.out.println("siteCode-->["+siteCode+"]");
						}
						
						else if("loc_code".equalsIgnoreCase(childNodeName))
						{
							System.out.println("ENTER IN LOC CODE");
							locCode = "";
							if (childNode.getFirstChild() != null)
							{
								locCode = childNode.getFirstChild().getNodeValue();
							}
							
							System.out.println("locCode-->["+locCode+"]");
						}
						
						
					}
										
				}
				pstmt.setDate(1,currDate);
				pstmt.setString(2,itemCode);
				pstmt.setString(3,analysisClass);
				pstmt.setString(4,locCode);
				pstmt.setString(5,siteCode);
				pstmt.setString(6,chgUser);
				pstmt.setString(7,chgTerm);
				pstmt.setDate(8,currDate);
				pstmt.setString(9,prioritySeq);
				pstmt.executeUpdate();
				pstmt.clearParameters();
				
				System.out.println("Insertion Successfull in table PHY_CNT_SCH_DET table -->");
				
			 }
			
		}
		//Change by Rohan 05/04/12 [WM1FSUN005] To remove feilds on header window.end
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException :" + se);
			se.printStackTrace();
			errString = se.getMessage();
			return errString;
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			return errString ;
			
			
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if ( !isError )
					{
						System.out.println("COMMITING the changes......");
						conn.commit();
						errString = itmDBAccessEJB.getErrorString("","PRCSUCCESS","");
					}
					else
					{
						System.out.println("ROLLBACK the changes......");
						conn.rollback();
						errString = itmDBAccessEJB.getErrorString("","PRCFAILED","");
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;
				
			}
			
		}
		return errString;
		
	}
	/*	
	//Changed by Rohan 04/04/12 To get list of holiday.start
	private java.util.ArrayList getHolidayList( Connection conn, String employeeCode,java.sql.Date dateFrom, int daysInMonth ) 
	{
		System.out.println("Getting Holidays Information ..... ");
		
		java.sql.PreparedStatement pstmtHolday = null;
		java.sql.ResultSet rsetHolday = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
	
		java.util.ArrayList holidayArray = new java.util.ArrayList();
		java.util.Calendar calendar = java.util.Calendar.getInstance();		
		
		try
		{
			String periodCode = "";
			String formatWorkDate = "";
			String holidayDate = "";
			String prdCodeSql = "SELECT CODE FROM PERIOD WHERE FR_DATE = ?";

			pstmtHolday = conn.prepareStatement( prdCodeSql );
			pstmtHolday.setDate(1, dateFrom );
			rsetHolday = pstmtHolday.executeQuery();
			if ( rsetHolday.next() )
			{
				periodCode = rsetHolday.getString("CODE");
			}

			java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( genericUtility.getApplDateFormat() );
			calendar.setTimeInMillis( dateFrom.getTime() );

			for (int count=0; count!=daysInMonth; count++ )
			{
				//System.out.println("ENTER");
				calendar.setTimeInMillis( calendar.getTimeInMillis() );
				formatWorkDate = sdf.format( calendar.getTime() );
				java.sql.Timestamp  workDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(formatWorkDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()));
                System.out.println("WORK DATES iN TIMESTAMP FORMA"+workDate);
				String getHolidaySQL = "SELECT DDF_GET_EMP_HOLIDAY( ?, ?, ?, ? )AS HOL_DATE FROM DUAL";
				pstmtHolday = conn.prepareStatement( getHolidaySQL );
				pstmtHolday.setString(1, employeeCode );
				pstmtHolday.setString(2, periodCode );
				pstmtHolday.setString(3, periodCode );
				pstmtHolday.setTimestamp(4, workDate );

				rsetHolday = pstmtHolday.executeQuery();

				if( rsetHolday.next() )
				{
					holidayDate = checkNull(rsetHolday.getString("HOL_DATE"));
					if( holidayDate.trim().length() > 0 )
					{
						System.out.println("ENTER IN  if( holidayDate.trim().length() > 0 )");
						//Changed By Sumit Sakore On 24/02/2009 [ This DateFormat will be there As per Nilesh Parikh ]
						//holidayArray.add( genericUtility.getValidDateString(holidayDate, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat()).toUpperCase() ) ;
						holidayArray.add( genericUtility.getValidDateString(holidayDate, "dd-MMM-yyyy", genericUtility.getApplDateFormat()).toUpperCase() ) ;
					}					
				}
				calendar.add( calendar.DATE, 1 );
			}
			     if(pstmtHolday != null)//added by darshan for ejb 3
				{
					pstmtHolday.close();
				}
				if (rsetHolday !=null )//added by darshan for ejb 3
				{
					rsetHolday.close();
				}
			System.out.println("PhysicalCountPrcEJB :: getHolidayList :: holidayArray ==> ["+holidayArray+"]");
		}
		catch ( Exception eHolday )
		{
			System.out.println("ExpenseProcessEJB : getHolidayList => ");
			eHolday.printStackTrace();
		}
		finally
		{
			try
			{
				if(pstmtHolday != null)
				{
					pstmtHolday.close();
				}
				if (rsetHolday !=null )
				{
					rsetHolday.close();
				}
			}
			catch(Exception closeRsc )
			{
				closeRsc.printStackTrace();
			}
		}
		return holidayArray;
	}
	//Changed by Rohan 04/04/12 To get list of holiday.end
//Changed By Rohan Sakore On 04/04/12 to check null value.start
private String checkNull(String input)
{
	if (input==null)
	{
		input="";
	}
	return input;
}*/
//Changed By Rohan Sakore On 04/04/12 to check null value.end

	

}