
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.fin.GenerateReceiptPrc;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.BaseException;
import ibase.utility.CommonConstants;
import ibase.utility.UserInfoBean;
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;
import ibase.webitm.ejb.sys.UtilMethods;


public class PhysicalCountSchedule 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;
	//Change by Rohan on 2/7/12
	String loginSiteCode = "";

	
	public static void main(String args[])
	{
		PhysicalCountSchedule pcs=new PhysicalCountSchedule();
		//pcs.insertSchedule();
		System.out.println("Main Method Calling******************************************************88");
	}
	
	public String schedulePriority( String wrkflwPriority )throws Exception
	{
		return "";
	}
	public String schedule(HashMap map)throws Exception
	{
		return "";
	}
	public String schedule(String scheduleParamXML)throws Exception
	{
		ibase.utility.UserInfoBean userInfo = null;
		try
		{
			//Chnaged by Rohan on 7/2/12.start
			//insertSchedule();
			System.out.println("************ ["+scheduleParamXML+"]");
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			loginSiteCode = userInfo.getSiteCode();
			System.out.println("Site code======**************====> "+loginSiteCode);
			
			insertSchedule(loginSiteCode,userInfo.getEmpCode(),userInfo);
			//Chnaged by Rohan on 7/2/12.end
			
		}
		catch(Exception e)
		{
			throw new Exception(e);
		}
		return "";
	}
	public void insertSchedule(String loginSiteCode,String empCode,UserInfoBean userInfo)
	{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtInsert = null;
		ResultSet rs = null;
		Statement stmt = null;
		
		java.util.Date todayDate=null;
		java.sql.Date chgDate=null;
		
		String currentMonthInString="";
		String currentQuarterMonth1InString="";
		String currentQuarterMonth2InString="";
		String currentHalfYearMonth1InString="";
		String currentHalfYearMonth2InString="";
		String currentHalfYearMonth3InString="";
		String insertItemProcess="";
		String itemCode="";
		String analysisClass="";
		String siteCode="";
		
		String locCode="";
		String chgUser="";		
		String chgTerm="";
		String queryString="";
		String machineName="";
		String clientIp="";
		String tranId="";
		String lotNo="";
		String lotSl="";
		String cycleCriteria="";
		int cycleCount=0;
		
		int currentMonth=0;
		int currentYear=0;
		int numberOfItemsA=0;
		int numberOfItemsB=0;
		int numberOfItemsC=0;
		int numberOfWorkingDaysA=0;
		int numberOfWorkingDaysB=0;
		int numberOfWorkingDaysC=0;
		int numberOfItemsPerdayA=0;
		int numberOfItemsPerdayB=0;
		int numberOfItemsPerdayC=0;
		int numberOfItemsPerdayATemp=0;
		int numberOfItemsPerdayA_rem=0;
		int numberOfItemsPerdayBTemp=0;
		int numberOfItemsPerdayB_rem=0;
		int numberOfItemsPerdayCTemp=0;
		int numberOfItemsPerdayC_rem=0;	
		int numberOfItemProcessRecords=0;
		int noOfRecordsToday = 0;
		int iA=0;
		int iB=0;
		int iC=0;
		HashMap qhm=new HashMap<String,String>();
		String strKey="";
		String strValue="";
		int pctr=0;
		int qctr=0;
		int rctr=0;
		SelectItem aitem=null;
		SelectItem bitem=null;
		SelectItem citem=null;
		ArrayList<SelectItem> alist=null;
		ArrayList<SelectItem> blist=new ArrayList<SelectItem>();
		ArrayList<SelectItem> clist=new ArrayList<SelectItem>();
		ArrayList<ArrayList<SelectItem>> arrA=new ArrayList<ArrayList<SelectItem>>();
		ArrayList<ArrayList<SelectItem>> arrB=new ArrayList<ArrayList<SelectItem>>();
		ArrayList<ArrayList<SelectItem>> arrC=new ArrayList<ArrayList<SelectItem>>();
		ArrayList<SelectItem> b2list=new ArrayList<SelectItem>();
		ArrayList<SelectItem> c2list=new ArrayList<SelectItem>();
		
		
		int oneCount=0;
		int twoCount=0;
		int threeCount=0;
		ArrayList result=null;
		int rec=0;
		java.sql.Timestamp wTimeStamp=null;
		java.sql.Date wDate=null;
		int days=0;
		int totHdays=0;
		String monthName="";
		String year="";
		java.sql.Timestamp ltranDate=null;
		java.sql.Date lDate=null;
		java.sql.Date frDate=null;
		java.sql.Date toDate=null;
		java.sql.Date from1Quarter=null;
		java.sql.Date from2Quarter=null;
		java.sql.Date from3Quarter=null;
		java.sql.Date from4Quarter=null;
		
		java.sql.Date to1Quarter=null;
		java.sql.Date to2Quarter=null;
		java.sql.Date to3Quarter=null;
		java.sql.Date to4Quarter=null;
		
		java.sql.Date from1Half=null;
		java.sql.Date from2Half=null;
		
		java.sql.Date to1Half=null;
		java.sql.Date to2Half=null;
		
		
		java.sql.Date setQFromdt=null;
		java.sql.Date setQTodt=null;
		
		java.sql.Date setHFromdt=null;
		java.sql.Date setHTodt=null;
		int priority=1;
		int workDay=0;
		

		try
		{
			//java.util.Date dt=new Date();
			java.sql.Date  dt = new  java.sql.Date((new java.util.Date()).getTime());
			todayDate=new java.util.Date();
			java.sql.Timestamp ts=null;
			GenericUtility genericUtility=GenericUtility.getInstance();
			SimpleDateFormat sdf1= new SimpleDateFormat(genericUtility.getDBDateFormat());
			java.sql.Timestamp dts=java.sql.Timestamp.valueOf(sdf1.format(dt).toString() + " 00:00:00.0");
			wTimeStamp=dts;
			clientIp = InetAddress.getLocalHost().getHostAddress();
			
			//Changed by Gayatri Yadav on 01-04-2011 [To store machine name in status log][SY12SUN004]
			machineName = InetAddress.getLocalHost().getHostName();	
			//Class.forName("oracle.jdbc.driver.OracleDriver");
			//conn = DriverManager.getConnection("jdbc:oracle:thin:@//172.16.100.70:1521/orcl", "erp", "erp");
			
			conn = getConnection();
			conn.setAutoCommit(false);
			tranId=generateTranId("w_phy_item_scan",conn);	
			
			queryString="select count(*) from work_calendar where work_date=? and working='Y' ";
			pstmt=conn.prepareStatement(queryString);
			pstmt.setTimestamp(1,dts);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				workDay=rs.getInt(1);
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			
			if(workDay>0)
			{
					
				
				queryString="select fr_date,to_date from acctprd where ? between fr_date and to_date ";
				pstmt=conn.prepareStatement(queryString);
				pstmt.setTimestamp(1, dts);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					frDate=new java.sql.Date(rs.getTimestamp("fr_date").getTime());
					toDate=new java.sql.Date(rs.getTimestamp("to_date").getTime());
				}
				if(rs!=null)
				{
					rs.close();
					rs=null;
				}
				if(pstmt!=null)
				{
					pstmt.close();
					pstmt=null;
				}
				from1Quarter=frDate;
				from2Quarter=addMonths(frDate,3);
				from3Quarter=addMonths(frDate,6);
				from4Quarter=addMonths(frDate,9);
				to1Quarter=addMonths(toDate,-9);
				to2Quarter=addMonths(toDate,-6);
				to3Quarter=addMonths(toDate,-3);
				to4Quarter=toDate;
				from1Half=frDate;
				from2Half=addMonths(frDate,6);
				to1Half=addMonths(toDate,-6);
				to2Half=toDate;
				System.out.println("Quarter Period Defined");
				System.out.println(from1Quarter+"----"+to1Quarter);
				System.out.println(from2Quarter+"----"+to2Quarter);
				System.out.println(from3Quarter+"----"+to3Quarter);
				System.out.println(from4Quarter+"----"+to4Quarter);
				System.out.println("Half Year Period Defined");
				System.out.println(from1Half+"----"+to1Half);
				System.out.println(from2Half+"----"+to2Half);
				
				if((dt.after(from1Quarter) || dt.toString().equals(from1Quarter.toString()) ) && (dt.before(to1Quarter) || dt.toString().equals(to1Quarter.toString()))) //change done by kunal on 31/12/12 add equal cond. as per manoj instruction  
				{
					//subSql=" AND TRAN_DATE BETWEEN  ";
					setQFromdt=from1Quarter;
					setQTodt=to1Quarter;
					
				}
				if((dt.after(from2Quarter) || dt.toString().equals(from2Quarter.toString())) && (dt.before(to2Quarter) || dt.toString().equals(to2Quarter.toString())))
				{
					setQFromdt=from2Quarter;
					setQTodt=to2Quarter;
					
				}
				
				if((dt.after(from3Quarter)|| dt.toString().equals(from3Quarter.toString()) ) && ( dt.before(to3Quarter) || dt.toString().equals(to3Quarter.toString())))
				{
					setQFromdt=from3Quarter;
					setQTodt=to3Quarter;
					
				}
				if((dt.after(from4Quarter) || dt.toString().equals(from4Quarter.toString())) && ( dt.before(to4Quarter) || dt.toString().equals(to4Quarter.toString())))
				{
					setQFromdt=from4Quarter;
					setQTodt=to4Quarter;
					
				}
				if((dt.after(from1Half) || dt.toString().equals(from1Half.toString())) && (dt.before(to1Half) || dt.toString().equals(to1Half.toString())))
				{
					setHFromdt=from1Half;
					setHTodt=to1Half;
					
				}
				if((dt.after(from2Half) || dt.toString().equals(from2Half.toString())) && (dt.before(to2Half) || dt.toString().equals(to2Half.toString())))
				{
					setHFromdt=from2Half;
					setHTodt=to2Half;
					
				}
				
				queryString="select count(1) from PHYSCAN  where  tran_date=? and tran_type='A'";
				pstmt=conn.prepareStatement(queryString);
				pstmt.setTimestamp(1,dts);
				rs=pstmt.executeQuery();
				while(rs.next())
				{
					
					noOfRecordsToday = rs.getInt(1);
					//noOfRecordsToday++;
				}
				
				if(pstmt!=null)
				{
					pstmt.close();
					pstmt=null;
				}
				
				if(rs!=null)
				{
					rs.close();
					rs=null;
				}
				
				if(noOfRecordsToday>0)
				{
					System.out.println("Already Records Inserted");
				}
				else
				{
					
				
					chgUser="WMS";
	
					
					System.out.println("currentMonthInString="+currentMonthInString);
					queryString="insert into physcan (tran_id,tran_date,site_code,chg_date,chg_user,chg_term,tran_type) values(?,?,?,?,?,?,?)";
					pstmt=conn.prepareStatement(queryString);
					pstmt.setString(1,tranId);
					pstmt.setTimestamp(2,dts);
					pstmt.setString(3,loginSiteCode);
					pstmt.setTimestamp(4,dts);
					pstmt.setString(5,userInfo.getLoginCode());
					pstmt.setString(6,userInfo.getRemoteHost());
					pstmt.setString(7,"A");
					pstmt.executeUpdate();
					pstmt.close();
					pstmt=null;
					
					queryString = "select work_date from work_calendar where work_date < ? and working='N' order by work_date desc ";
					pstmt=conn.prepareStatement(queryString);
					pstmt.setTimestamp(1,dts);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						wTimeStamp=rs.getTimestamp(1);
						wDate=new java.sql.Date(wTimeStamp.getTime());
						System.out.println(wDate+"-----"+todayDate);
						if(wDate.before(todayDate))
						{
							System.out.println("days-----"+days);
							//Changed By 02/10/12 Manoj Sharma Location emptied on pevious day does not come in the cycle count 
							//days=(int) (todayDate.getTime() - wDate.getTime())/(1000 * 60 * 60 * 24);
							days = (int)countDaysBetween(wDate,todayDate);
							System.out.println("days-----"+days);
							if(days==1)
							{
								todayDate=wDate;
								totHdays++;
							}
							
	
						}
					
					}
					System.out.println("totHdays---"+totHdays);
					System.out.println("days-----"+days);
					if(totHdays==0)
						totHdays=1;
					wTimeStamp.setTime(dts.getTime() - (totHdays*(1000 * 60 * 60 * 24)));
					
					System.out.println("wTimeStamp---"+wTimeStamp);
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					
					queryString=" SELECT DISTINCT S.ITEM_CODE,S.LOT_NO,S.LOT_SL,S.LOC_CODE,S.SITE_CODE,CYCLE_CRITERIA,S.QUANTITY,S.UNIT FROM ITEM I, STOCK S,LOCATION L" +
							//",INVTRACE T" +
							",ITEM_LOT_PACKSIZE P WHERE ANALYSIS_CLASS='A' " +
							" AND I.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM PHYSCAN A,PHYSCANDET B WHERE A.TRAN_ID=B.TRAN_ID AND  ANALYSIS_CLASS='A' AND TO_CHAR(TRAN_DATE, 'MON/YYYY')=?  AND CASE WHEN B.STATUS IS NULL THEN 'N' ELSE B.STATUS END ='Y'  and A.tran_type='A') " +
							" AND S.ITEM_CODE = I.ITEM_CODE  AND S.LOC_CODE=L.LOC_CODE AND CYCLE_COUNT_REQD='Y' " +
							" AND S.ITEM_CODE=P.ITEM_CODE " +
							//" AND P.ITEM_CODE=T.ITEM_CODE " +
							" AND S.LOT_NO BETWEEN P.LOT_NO__FROM AND P.LOT_NO__TO "+
							//"  AND S.site_code=T.site_code and S.lot_no=T.lot_no and S.loc_code=T.loc_code and S.lot_sl=T.lot_sl " +
							//" and S.item_code=T.item_code" +
							" and ( S.QUANTITY>0 " +
							//"OR (T.qty_after=0 and T.tran_date=?)" +
							" ) AND S.site_code = ? "+ //added by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction 
							" AND (S.LAST_PHYC_DATE IS NULL OR TO_CHAR(S.LAST_PHYC_DATE,'MON/YYYY') <> ?) ORDER BY CYCLE_CRITERIA,S.ITEM_CODE,S.LOT_NO,S.LOT_SL ";
					pstmt=conn.prepareStatement(queryString);
					pstmt.setString(1,(new SimpleDateFormat("MMM").format(dt)).toUpperCase()+"/"+new SimpleDateFormat("yyyy").format(dt));
					//pstmt.setTimestamp(2,wTimeStamp);
					pstmt.setString(2, loginSiteCode);
					pstmt.setString(3,(new SimpleDateFormat("MMM").format(dt)).toUpperCase()+"/"+new SimpleDateFormat("yyyy").format(dt));
					
					rs=pstmt.executeQuery();
					alist=new ArrayList<SelectItem>();
					while(rs.next())
					{
						aitem=new SelectItem(rs.getString("ITEM_CODE"), rs.getString("LOT_NO"), rs.getString("LOT_SL"), rs.getString("LOC_CODE"),"A", rs.getString("SITE_CODE"), rs.getString("CYCLE_CRITERIA"),rs.getDouble("QUANTITY"),rs.getString("UNIT"));
						alist.add(aitem);
						//numberOfItemsA=rs.getInt(1);
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					
					
					System.out.println("alist---"+alist.size());
					result=new ArrayList();
					if(alist.size()>0)
					{
						result=countItemValue(alist);
					}
					if(result.size()==2)
					{
						numberOfItemsA=(Integer) result.get(0);
						arrA=(ArrayList<ArrayList<SelectItem>>) result.get(1);
					}
				
					
					System.out.println("arrA---"+arrA.size());
	//				
					
					queryString=" SELECT DISTINCT S.ITEM_CODE,S.LOT_NO,S.LOT_SL,S.LOC_CODE,S.SITE_CODE,CYCLE_CRITERIA,S.LTRAN_DATE,S.QUANTITY,S.UNIT FROM ITEM I, STOCK S,LOCATION L" +
							//",INVTRACE T" +
							",ITEM_LOT_PACKSIZE P WHERE ANALYSIS_CLASS='B' " +
							" AND S.ITEM_CODE = I.ITEM_CODE AND S.LOC_CODE=L.LOC_CODE AND CYCLE_COUNT_REQD='Y' " +
							" AND S.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM PHYSCAN A,PHYSCANDET B WHERE A.TRAN_ID=B.TRAN_ID AND  ANALYSIS_CLASS='B' AND TRAN_DATE BETWEEN ? AND ?  AND CASE WHEN B.STATUS IS NULL THEN 'N' ELSE B.STATUS END ='Y'  and A.tran_type='A' ) " +
							" AND S.ITEM_CODE=P.ITEM_CODE" +
							//" AND P.ITEM_CODE=T.ITEM_CODE" +
							" AND S.LOT_NO BETWEEN P.LOT_NO__FROM AND P.LOT_NO__TO "+
							//"  AND S.site_code=T.site_code and S.lot_no=T.lot_no and S.loc_code=T.loc_code and S.lot_sl=T.lot_sl " +
							//" and S.item_code=T.item_code" +
							" and  ( S.QUANTITY>0" +
							//" OR (T.qty_after=0 and T.tran_date=?)" +
							" ) and  S.site_code = ? "+ //added  by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction
							" AND (S.LAST_PHYC_DATE IS NULL OR S.LAST_PHYC_DATE NOT BETWEEN ? AND ? )  ORDER BY CYCLE_CRITERIA,S.ITEM_CODE,S.LOT_NO,S.LOT_SL";
					pstmt=conn.prepareStatement(queryString);
					pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(sdf1.format(setQFromdt).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(sdf1.format(setQTodt).toString() + " 00:00:00.0"));
					//pstmt.setTimestamp(3,wTimeStamp);
					pstmt.setString(3, loginSiteCode);
					pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(sdf1.format(setQFromdt).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(5,java.sql.Timestamp.valueOf(sdf1.format(setQTodt).toString() + " 00:00:00.0"));
					
					rs=pstmt.executeQuery();
					alist=new ArrayList<SelectItem>();
					while(rs.next())
					{
						
						System.out.println(rs.getString("ITEM_CODE")+"--"+ rs.getString("LOT_NO")+"---"+ rs.getString("LOT_SL")+"---"+ rs.getString("LOC_CODE")+"---"+"A"+"--"+ rs.getString("SITE_CODE")+"---"+ rs.getString("CYCLE_CRITERIA"));
						aitem=new SelectItem(rs.getString("ITEM_CODE"), rs.getString("LOT_NO"), rs.getString("LOT_SL"), rs.getString("LOC_CODE"),"B", rs.getString("SITE_CODE"), rs.getString("CYCLE_CRITERIA"),rs.getDouble("QUANTITY"),rs.getString("UNIT"));
						alist.add(aitem);
					}			
		
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					result=new ArrayList();
					if(alist.size()>0)
					{
						System.out.println("alist---"+alist.size());
						result=countItemValue(alist);
					}
					if(result.size()==2)
					{
						numberOfItemsB=(Integer) result.get(0);
						arrB=(ArrayList<ArrayList<SelectItem>>) result.get(1);
					}
					System.out.println("arrB---"+arrB.size());
	//				
						queryString=" SELECT DISTINCT S.ITEM_CODE,S.LOT_NO,S.LOT_SL,S.LOC_CODE,S.SITE_CODE,CYCLE_CRITERIA,S.LTRAN_DATE,S.QUANTITY,S.UNIT FROM ITEM I, STOCK S,LOCATION L" +
								//",INVTRACE T" +
								",ITEM_LOT_PACKSIZE P WHERE ANALYSIS_CLASS='C' " +
								" AND S.ITEM_CODE = I.ITEM_CODE AND S.LOC_CODE=L.LOC_CODE AND CYCLE_COUNT_REQD='Y'" +
								" AND S.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM PHYSCAN A,PHYSCANDET B WHERE A.TRAN_ID=B.TRAN_ID AND ANALYSIS_CLASS='C' AND  TRAN_DATE BETWEEN ? AND ?  AND CASE WHEN B.STATUS IS NULL THEN 'N' ELSE B.STATUS END ='Y'  and A.tran_type='A' )" +
								" AND S.ITEM_CODE=P.ITEM_CODE" +
								//" AND P.ITEM_CODE=T.ITEM_CODE" +
								" AND S.LOT_NO BETWEEN P.LOT_NO__FROM AND P.LOT_NO__TO "+
								//"  AND S.site_code=T.site_code and S.lot_no=T.lot_no and S.loc_code=T.loc_code and S.lot_sl=T.lot_sl " +
								//" and S.item_code=T.item_code" +
								" and  ( S.QUANTITY>0" +
								//" OR (T.qty_after=0 and T.tran_date=?)" +
								" ) and  S.site_code = ? "+ //added  by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction
								" AND (S.LAST_PHYC_DATE IS NULL OR S.LAST_PHYC_DATE NOT BETWEEN ? AND ? ) ";
						pstmt=conn.prepareStatement(queryString);
						pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(sdf1.format(setHFromdt).toString() + " 00:00:00.0"));
						pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(sdf1.format(setHTodt).toString() + " 00:00:00.0"));
						//pstmt.setTimestamp(3,wTimeStamp);
						pstmt.setString(3, loginSiteCode);
						pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(sdf1.format(setHFromdt).toString() + " 00:00:00.0"));
						pstmt.setTimestamp(5,java.sql.Timestamp.valueOf(sdf1.format(setHTodt).toString() + " 00:00:00.0"));
					
					rs=pstmt.executeQuery();
					alist=new ArrayList<SelectItem>();
					while(rs.next())
					{
						aitem=new SelectItem(rs.getString("ITEM_CODE"), rs.getString("LOT_NO"), rs.getString("LOT_SL"), rs.getString("LOC_CODE"),"C", rs.getString("SITE_CODE"), rs.getString("CYCLE_CRITERIA"),rs.getDouble("QUANTITY"),rs.getString("UNIT"));
						alist.add(aitem);
					}
		
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					System.out.println("alist---"+alist.size());
					result=new ArrayList();
					if(alist.size()>0)
					{
						result=countItemValue(alist);
					}
					if(result.size()==2)
					{
						numberOfItemsC=(Integer) result.get(0);
						arrC=(ArrayList<ArrayList<SelectItem>>) result.get(1);
					}
					System.out.println("arrC---"+arrC.size());
					//Change by Rohan on 7/2/12 To add site Code 
					//queryString="select count(distinct(work_date)) from work_calendar where WORKING='Y' and WORK_DATE>=to_date(sysdate,'DD-MON-YY') and WORK_DATE like '%"+currentMonthInString+"%' and to_char(WORK_DATE, 'YY')=?";
					queryString="select distinct(work_date) from work_calendar where WORKING='Y'  "//and TO_DATE(WORK_DATE,'DD-MON-YY')>=to_date(sysdate,'DD-MON-YY') "
						+" and site_code = ? and TO_CHAR(WORK_DATE, 'MON/YYYY') = ? ";
					System.out.println("1-Print queryString---"+queryString);
					pstmt=conn.prepareStatement(queryString);
					pstmt.setString(1,loginSiteCode);
					pstmt.setString(2,(new SimpleDateFormat("MMM").format(dt)).toUpperCase()+"/"+new SimpleDateFormat("yyyy").format(dt));
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						//numberOfWorkingDaysA=rs.getInt(1);
						ts=rs.getTimestamp(1);
						//System.out.println("ts---"+ts);
						//System.out.println("dts---"+dts);
						if(ts.after(dts) || ts.equals(dts))
						{
							numberOfWorkingDaysA++;
						}
						
						//numberOfWorkingDaysA++;
						System.out.println("numberOfWorkingDaysA----"+numberOfWorkingDaysA);
					}
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					//Changed by Rohan on 2/7/12
					//queryString="select count(distinct(work_date)) from work_calendar where WORKING='Y' and WORK_DATE>=to_date(sysdate,'DD-MON-YY') and (WORK_DATE like '%"+currentMonthInString+"%' or WORK_DATE like '%"+currentQuarterMonth1InString+"%' or WORK_DATE like '%"+currentQuarterMonth2InString+"%') and to_char(WORK_DATE, 'YY')=?";
					queryString="select distinct(work_date) from work_calendar where WORKING='Y' "// and WORK_DATE>=to_date(sysdate,'DD-MON-YY') "
						     +" and site_code = ? AND WORK_DATE BETWEEN ? AND ? ";
					System.out.println("2-Print queryString---"+queryString);
					pstmt=conn.prepareStatement(queryString);
					pstmt.setString(1,loginSiteCode);
					pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(sdf1.format(setQFromdt).toString() + " 00:00:00.0"));
					pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(sdf1.format(setQTodt).toString() + " 00:00:00.0"));
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						//numberOfWorkingDaysB=rs.getInt(1);
						ts=rs.getTimestamp(1);
						System.out.println("ts---"+ts);
						System.out.println("dts---"+dts);
						if(ts.after(dts) || ts.equals(dts))
						{
							numberOfWorkingDaysB++;
						}
					}
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					
						queryString="select distinct(work_date) from work_calendar where WORKING='Y' "// and WORK_DATE>=to_date(sysdate,'DD-MON-YY') "
							+" and site_code = ?  and work_date between ? and ? ";
						System.out.println("5-Print queryString---"+queryString);
						pstmt=conn.prepareStatement(queryString);
						pstmt.setString(1,loginSiteCode);
						pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(sdf1.format(setHFromdt).toString() + " 00:00:00.0"));
						pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(sdf1.format(setHTodt).toString() + " 00:00:00.0"));
						
										
					System.out.println("queryString---"+queryString);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						ts=rs.getTimestamp(1);
						System.out.println("ts---"+ts);
						System.out.println("dts---"+dts);
						if(ts.after(dts) || ts.equals(dts))
						{
							numberOfWorkingDaysC++;
						}
						//numberOfWorkingDaysC=rs.getInt(1);
					}
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
								
					System.out.println("numberOfItemsA="+numberOfItemsA);
					System.out.println("numberOfItemsB="+numberOfItemsB);
					System.out.println("numberOfItemsC="+numberOfItemsC);
					System.out.println("numberOfWorkingDaysA="+numberOfWorkingDaysA);
					System.out.println("numberOfWorkingDaysB="+numberOfWorkingDaysB);
					System.out.println("numberOfWorkingDaysC="+numberOfWorkingDaysC);
					
					
					//Changed by Rohan on 7/2/12
					//if(numberOfItemsA>0)
					if(numberOfItemsA > 0 && numberOfWorkingDaysA > 0)
					{
						numberOfItemsPerdayATemp=(numberOfItemsA/numberOfWorkingDaysA);
						numberOfItemsPerdayA_rem=(numberOfItemsA%numberOfWorkingDaysA);
						if(numberOfItemsPerdayA_rem > (numberOfWorkingDaysA/2) )
							numberOfItemsPerdayA=numberOfItemsPerdayATemp+1;
						else
							numberOfItemsPerdayA=numberOfItemsPerdayATemp;				
					}
					//Changed by Rohan on 7/2/12
					//if(numberOfItemsB>0)
					if(numberOfItemsB > 0 && numberOfWorkingDaysB > 0)
					{
						numberOfItemsPerdayBTemp=(numberOfItemsB/numberOfWorkingDaysB);
						numberOfItemsPerdayB_rem=(numberOfItemsB%numberOfWorkingDaysB);
						if(numberOfItemsPerdayB_rem > (numberOfWorkingDaysB/2) )
							numberOfItemsPerdayB=numberOfItemsPerdayBTemp+1;
						else
							numberOfItemsPerdayB=numberOfItemsPerdayBTemp;				
					}
					//Changed by Rohan on 7/2/12
					//if(numberOfItemsC>0)
					if(numberOfItemsC > 0 && numberOfWorkingDaysC > 0 )
					{
						numberOfItemsPerdayCTemp=(numberOfItemsC/numberOfWorkingDaysC);
						numberOfItemsPerdayC_rem=(numberOfItemsC%numberOfWorkingDaysC);
						if(numberOfItemsPerdayC_rem > (numberOfWorkingDaysC/2) )
							numberOfItemsPerdayC=numberOfItemsPerdayCTemp+1;
						else
							numberOfItemsPerdayC=numberOfItemsPerdayCTemp;				
					}
					
					//java.sql.Date currTrandate = new java.sql.Date( System.currentTimeMillis() );
					///*
					
		
					System.out.println("numberOfItemsPerdayA="+numberOfItemsPerdayA);
					System.out.println("numberOfItemsPerdayB="+numberOfItemsPerdayB);
					System.out.println("numberOfItemsPerdayC="+numberOfItemsPerdayC);
					
					
					numberOfItemProcessRecords=0;
					System.out.println("HELLO---"+numberOfItemsPerdayA);
					//int rec=generateRec(conn,arr,numberOfItemsPerdayA);
					
					rec=generateDetRec(empCode,rec,conn,tranId,dts,arrA,numberOfItemsPerdayA);
					System.out.println("1---rec----"+rec);
					rec=generateDetRec(empCode,rec,conn,tranId,dts,arrB,numberOfItemsPerdayB);
					System.out.println("2--rec----"+rec);
					rec=generateDetRec(empCode,rec,conn,tranId,dts,arrC,numberOfItemsPerdayC);
					System.out.println("3--rec----"+rec);
					
					
					
					queryString=" SELECT DISTINCT T.ITEM_CODE,T.LOT_NO,T.LOT_SL,T.LOC_CODE,T.SITE_CODE,CYCLE_CRITERIA,0 QUANTITY,T.UNIT,I.ANALYSIS_CLASS FROM ITEM I,LOCATION L,INVTRACE T,ITEM_LOT_PACKSIZE P WHERE " +
							//" AND I.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM PHYSCAN A,PHYSCANDET B WHERE A.TRAN_ID=B.TRAN_ID AND  ANALYSIS_CLASS='A' AND TO_CHAR(TRAN_DATE, 'MON/YYYY')=?  AND CASE WHEN B.STATUS IS NULL THEN 'N' ELSE B.STATUS END ='Y'  and A.tran_type='A') " +
							//" AND S.ITEM_CODE = I.ITEM_CODE  AND S.LOC_CODE=L.LOC_CODE AND CYCLE_COUNT_REQD='Y' " +
							//" AND S.ITEM_CODE=P.ITEM_CODE AND "+
							" T.LOC_CODE=L.LOC_CODE AND T.ITEM_CODE=I.ITEM_CODE AND "+
							"P.ITEM_CODE=T.ITEM_CODE AND T.LOT_NO BETWEEN P.LOT_NO__FROM AND P.LOT_NO__TO AND "+
							//"  AND S.site_code=T.site_code and S.lot_no=T.lot_no and S.loc_code=T.loc_code and S.lot_sl=T.lot_sl " +
							"  L.CYCLE_COUNT_REQD='Y' AND (T.qty_after=0 and T.tran_date=?) AND T.site_code = ? "+ //added by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction 
							"   ORDER BY CYCLE_CRITERIA,T.ITEM_CODE,T.LOT_NO,T.LOT_SL ";
					}
					pstmt=conn.prepareStatement(queryString);
					pstmt.setTimestamp(1,wTimeStamp);
					pstmt.setString(2, loginSiteCode);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						rec++;
						insertItemProcess="insert into PHYSCANDET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE," +
								"LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
								"ANALYSIS_CLASS,PRIORITY_SEQ,CYCLE_CRITERIA,EMP_CODE__USER,UNIT) " +
						"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
						pstmtInsert = conn.prepareStatement(insertItemProcess);
						pstmtInsert.setString(1,tranId);
						pstmtInsert.setInt(2,rec);
						pstmtInsert.setString(3,loginSiteCode);
						pstmtInsert.setString(4,rs.getString("ITEM_CODE"));
						pstmtInsert.setString(5,rs.getString("LOC_CODE"));
						pstmtInsert.setString(6,rs.getString("LOT_NO"));
						pstmtInsert.setString(7,rs.getString("LOT_SL"));
						pstmtInsert.setDouble(8,0);
						pstmtInsert.setString(9,rs.getString("ANALYSIS_CLASS"));
						pstmtInsert.setInt(10,0);
						pstmtInsert.setString(11,rs.getString("CYCLE_CRITERIA"));
						pstmtInsert.setString(12,empCode);
						pstmtInsert.setString(13,rs.getString("UNIT"));
						pstmtInsert.executeUpdate();
						pstmtInsert.close();
						pstmtInsert=null;
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					
				
					
					queryString="select * from PHYSCANDET where tran_id=? order by loc_code asc ";
					pstmt=conn.prepareStatement(queryString);
					pstmt.setString(1,tranId);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						System.out.println("LINE_NO---"+rs.getInt("LINE_NO"));
					queryString="update PHYSCANDET set priority_seq=? where tran_id=? and LINE_NO=? ";	
					pstmtInsert=conn.prepareStatement(queryString);
					pstmtInsert.setInt(1, priority);
					pstmtInsert.setString(2, tranId);
					pstmtInsert.setInt(3, rs.getInt("LINE_NO"));
					pstmtInsert.executeUpdate();
					pstmtInsert.close();
					pstmtInsert=null;
					priority++;
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					//If No Records Found in detail 
					if(priority==1)
					{
						System.out.println("If No Records Found in detail");
						conn.rollback();
					}
			}
			else
			{
				System.out.println("Work Calendar is not Defined ");
			}
		}
		catch(Exception e)
		{
			isError = true;
			rec=0;
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("Exception is "+e.getMessage());
			System.out.println("Exception is "+e);
			e.printStackTrace();
		}
		finally
		{
			
			try
			{
				if(!isError || rec>0)
				{						
					conn.commit();					
				}
				else
				{
					conn.rollback();
				}
				if(conn != null)
				{
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(pstmtInsert!=null)
					{
						pstmtInsert.close();
						pstmtInsert=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
				
					conn.commit();
					conn.close();
				}
			}
			catch(Exception e)
			{
				isError = true;
				System.out.println("Exception is "+e.getMessage());
			}
			
		}
		
	}
	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;
	}
	

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

	
	/*
	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 CYCLECNT_SCH (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
/*private int generateDetRec(String empCode,int rec,Connection conn,String tranId,java.sql.Timestamp dts,ArrayList<ArrayList<SelectItem>> arr,double numberOfItemsPerdayA) throws ITMException
{
	
	int priority=0;
	SelectItem aitem=null;
	String insertItemProcess="";
	PreparedStatement pstmtInsert=null;
	try
	{
		for(int ctr=0;ctr<arr.size();ctr++)
		{
			//if(numberOfItemsPerdayA>=arr.get(ctr).size())
			
			System.out.println("ctr---"+ctr+"---"+priority);
				if(ctr>=priority)
				{
					System.out.println("ctr----"+ctr+"---"+arr.get(ctr).isEmpty());
					for(int ctr1=0;ctr1<arr.get(ctr).size();ctr1++)
					{
						System.out.println("ctr1----"+ctr1);
						
						//System.out.println("aitem---"+aitem);
						if(numberOfItemsPerdayA>=priority)
						{
							priority++;
							aitem=new SelectItem();
							aitem=arr.get(ctr).get(ctr1);
							rec++;
							System.out.println("lineNo---"+rec);
							insertItemProcess="insert into PHYSCANDET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE," +
									"LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
									"ANALYSIS_CLASS,PRIORITY_SEQ,CYCLE_CRITERIA,EMP_CODE__USER,UNIT) " +
							"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
							pstmtInsert = conn.prepareStatement(insertItemProcess);
							pstmtInsert.setString(1,tranId);
							pstmtInsert.setInt(2,rec);
							pstmtInsert.setString(3,aitem.getSiteCode());
							pstmtInsert.setString(4,aitem.getItemCode());
							pstmtInsert.setString(5,aitem.getLocCode());
							pstmtInsert.setString(6,aitem.getLotNo());
							pstmtInsert.setString(7,aitem.getlotSl());
							pstmtInsert.setDouble(8,aitem.getQuantity());
							pstmtInsert.setString(9,aitem.getAnalysisClass());
							pstmtInsert.setInt(10,priority);
							pstmtInsert.setString(11,aitem.getCycleCriteria());
							pstmtInsert.setString(12,empCode);
							pstmtInsert.setString(13,aitem.getUnit());
							pstmtInsert.executeUpdate();
							pstmtInsert.close();
							pstmtInsert=null;
						}
						else
						{
							break;
						}
					}
				}
			
			
		}
	}catch(Exception e)
	{
		try {
			conn.rollback();
			rec=0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		e.printStackTrace();
		throw new ITMException(e);
	}
	finally
	{
		
	}
	return rec;
	
}*/
private int generateDetRec(String empCode,int rec,Connection conn,String tranId,java.sql.Timestamp dts,ArrayList<ArrayList<SelectItem>> arr,double numberOfItemsPerdayA) throws ITMException
{
	
	int priority=0;
	SelectItem aitem=null;
	String insertItemProcess="";
	PreparedStatement pstmtInsert=null;
	try
	{
		for(int ctr=0;ctr<numberOfItemsPerdayA;ctr++)
		{
			//if(numberOfItemsPerdayA>=arr.get(ctr).size())
			
					//System.out.println("ctr---"+ctr+"---"+priority);
					//System.out.println("ctr----"+ctr+"---"+arr.get(ctr).isEmpty());
					System.out.println("arr.get(ctr).size()----"+arr.get(ctr).size());
					for(int ctr1=0;ctr1<arr.get(ctr).size();ctr1++)
					{
						System.out.println("ctr1----"+ctr1);
						
							priority++;
							aitem=new SelectItem();
							aitem=arr.get(ctr).get(ctr1);
							rec++;
							System.out.println("lineNo---"+rec);
							insertItemProcess="insert into PHYSCANDET (TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE," +
									"LOC_CODE,LOT_NO,LOT_SL,QUANTITY," +
									"ANALYSIS_CLASS,PRIORITY_SEQ,CYCLE_CRITERIA,EMP_CODE__USER,UNIT) " +
							"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
							pstmtInsert = conn.prepareStatement(insertItemProcess);
							pstmtInsert.setString(1,tranId);
							pstmtInsert.setInt(2,rec);
							pstmtInsert.setString(3,aitem.getSiteCode());
							pstmtInsert.setString(4,aitem.getItemCode());
							pstmtInsert.setString(5,aitem.getLocCode());
							pstmtInsert.setString(6,aitem.getLotNo());
							pstmtInsert.setString(7,aitem.getlotSl());
							pstmtInsert.setDouble(8,aitem.getQuantity());
							pstmtInsert.setString(9,aitem.getAnalysisClass());
							pstmtInsert.setInt(10,priority);
							pstmtInsert.setString(11,aitem.getCycleCriteria());
							pstmtInsert.setString(12,empCode);
							pstmtInsert.setString(13,aitem.getUnit());
							pstmtInsert.executeUpdate();
							pstmtInsert.close();
							pstmtInsert=null;
						
						
					}
				
			
			
		}
	}catch(Exception e)
	{
		try {
			conn.rollback();
			rec=0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		e.printStackTrace();
		throw new ITMException(e);
	}
	finally
	{
		
	}
	return rec;
	
}
//Changed By 02/10/12 Manoj Sharma Location emptied on pevious day does not come in the cycle count.start
//public  long countDaysBetween(java.sql.Date startdate, java.util.Date enddate) 
public  long countDaysBetween(java.sql.Date startdate, java.util.Date enddate)
{
		   
		    //reset all hours mins and secs to zero on start date
		    Calendar startCal = GregorianCalendar.getInstance();
		    startCal.setTime(startdate);
		    startCal.set(Calendar.HOUR_OF_DAY, 0);
		    startCal.set(Calendar.MINUTE, 0);
		    startCal.set(Calendar.SECOND, 0);
		    long startTime = startCal.getTimeInMillis();

		    //reset all hours mins and secs to zero on end date
		    Calendar endCal = GregorianCalendar.getInstance();
		    endCal.setTime(enddate);
		    endCal.set(Calendar.HOUR_OF_DAY, 0);
		    endCal.set(Calendar.MINUTE, 0);
		    endCal.set(Calendar.SECOND, 0);
		    long endTime = endCal.getTimeInMillis();

		    return (endTime - startTime)/(1000 * 60 * 60 * 24) ;
}
//Changed By 02/10/12 Manoj Sharma Location emptied on pevious day does not come in the cycle count.end
private ArrayList countItemValue(ArrayList<SelectItem> alist) throws ITMException
{
	ArrayList result=new ArrayList();
	SelectItem aitem=null;
	String cycleCriteria="";
	String itemCode="";
	String lotNo="";
	String lotSl="";
	HashMap<String,ArrayList<SelectItem>> hmOne=new HashMap<String, ArrayList<SelectItem>>();
	HashMap<String,ArrayList<SelectItem>> hmTwo=new HashMap<String, ArrayList<SelectItem>>();
	HashMap<String,ArrayList<SelectItem>> hmThree=new HashMap<String, ArrayList<SelectItem>>();
	ArrayList<ArrayList<SelectItem>> arr=null;
	ArrayList<SelectItem> a2list=null;
	ArrayList<SelectItem> b2list=null;
	ArrayList<SelectItem> c2list=null;
	int numberOfItemsA=0;
	Set<String> set=new HashSet<String>();
	try
	{
	for(int ctr=0;ctr<alist.size();ctr++)
	{
		 aitem=alist.get(ctr);	
		 cycleCriteria=aitem.getCycleCriteria();
		 itemCode=aitem.getItemCode();
		 lotNo=aitem.getLotNo();
		 lotSl=aitem.getlotSl();
		 System.out.println("aitem---"+aitem);
		 System.out.println("cycleCriteria---"+cycleCriteria);
		 System.out.println("itemCode---"+itemCode);
		 System.out.println("lotNo---"+lotNo);
		 System.out.println("lotSl---"+lotSl);
		 System.out.println(hmThree.size());
		 System.out.println(hmThree.toString());
		 //a2list.clear();
		 if("1".equalsIgnoreCase(cycleCriteria))
		 {
			 System.out.println("Cycle criteria itemCode+lotNo "+itemCode+lotNo);
			 System.out.println("hmOne.containsKey(itemCode+lotNo) "+hmOne.containsKey(itemCode+lotNo));
			 //Changed By Pragyan 28/09/12 As logic has been changed by M Sharma lot sl should not be included in key.
			 //if(!hmOne.containsKey(itemCode+lotNo+lotSl))
			 if(!hmOne.containsKey(itemCode))
			 {
				 
				 a2list=new ArrayList<SelectItem>();
				 a2list.add(aitem);
				 //numberOfItemsA++;
				 //System.out.println("aitem---"+aitem);
				 System.out.println("a2list---"+a2list);
				 System.out.println("Entering for CycleCriteria 1---a2list ----"+a2list.toString());
				//Changed By Pragyan 28/09/12 As logic has been changed by M Sharma lot sl should not be included in key.
				 //hmOne.put(itemCode+lotNo+lotSl,a2list) ;
				 
				/* Set s=hmOne.keySet();
				Iterator itr=s.iterator();
				System.out.println("s.size()--"+s.size());
				String key="";
				if(!set.isEmpty())
				{
					while(itr.hasNext())
					{
						key=(String) itr.next();
						System.out.println("Printing Key--"+key);
						String[] sval=key.split("@");
						System.out.println("sval[0]----"+sval[0]);
						if(!sval[0].equalsIgnoreCase(aitem.getItemCode()))
						{
							hmOne.put(itemCode+"@"+lotNo,a2list) ;
						}
						else
						{
							break;
						}
						
					}
				}
				else
				{
					hmOne.put(itemCode+"@"+lotNo,a2list) ;
				}*/
				 hmOne.put(itemCode,a2list) ;
				// set.add(aitem.getItemCode());
			 }
			 else
			 {
				 System.out.println("Skip the line");
			 }
		 }
//		else if("2".equalsIgnoreCase(cycleCriteria))
//		 {
//			 if(!hmTwo.containsKey(itemCode+lotNo))
//			 {
//				 a2list=new ArrayList<SelectItem>();
//				 a2list.add(aitem);
//				 hmTwo.put(itemCode+lotNo,a2list) ;
//			 }
//			 else
//			 {
//				 b2list=new ArrayList<SelectItem>();
//				 b2list=hmTwo.get(itemCode+lotNo);
//				 b2list.add(aitem);
//				 hmTwo.put(itemCode+lotNo,b2list) ;
//				 
//			 }
//		 }
		else if("2".equalsIgnoreCase(cycleCriteria))
		 {
			 if(!hmTwo.containsKey(itemCode))
			 {
				 a2list=new ArrayList<SelectItem>();
				 a2list.add(aitem);
				 //numberOfItemsA++;
				 hmTwo.put(itemCode,a2list) ;
				 //set.add(aitem.getItemCode());
			 }
			 else
			 {
				 b2list=new ArrayList<SelectItem>();
				 b2list=hmTwo.get(itemCode);
				 int listSize=0;
				 listSize=b2list.size();
				 System.out.println("listSize---"+listSize);
				 for(int p=0;p<listSize;p++)
				 {
					 SelectItem sc=b2list.get(p);
					 System.out.println("getting lotNo from existing list---"+sc.getLotNo());
					 System.out.println("getting lotNo from current Record---"+aitem.getLotNo());
					 if((sc.getLotNo()).equalsIgnoreCase(aitem.getLotNo()))
					 {
						 b2list.add(aitem); 
						 //numberOfItemsA++;
						 //set.add(aitem.getItemCode());
						 break;
					 }
				 }
				 
				 hmTwo.put(itemCode,b2list) ;
				 
			 }
		 }
		 else if("3".equalsIgnoreCase(cycleCriteria))
		 {
			 //Commented by Manoj dtd 03/10/2012 not required
			 if(!hmThree.containsKey(itemCode))
			 {
				 System.out.println("1--"+aitem.getItemCode()+"---"+aitem.getLotNo()+"---"+aitem.getlotSl());
				 a2list=new ArrayList<SelectItem>();
				 a2list.add(aitem);
				// numberOfItemsA++;
				 
				 hmThree.put(itemCode,a2list) ;
				// set.add(aitem.getItemCode());
			 }
			 else
			 {
				 System.out.println("2---"+aitem.getItemCode()+"---"+aitem.getLotNo()+"---"+aitem.getlotSl());
				 c2list=new ArrayList<SelectItem>();
				 //Added by manoj dtd 03/10/2012 to append record in map
				 c2list=hmThree.get(itemCode);
				 System.out.println("before adding element to c2list.size()--"+c2list.size());
				 c2list.add(aitem);
				 
				 //numberOfItemsA++;
				 System.out.println("after adding element to c2list.size()--"+c2list.size());
				 for(int p=0;p<c2list.size();p++)
				 {
					 SelectItem sc=c2list.get(p);
					 System.out.println("Printing values---"+sc.getItemCode()+"----"+sc.getLotNo()+"---"+sc.getLocCode());
				 }
				 hmThree.put(itemCode,c2list) ; 
			 }
			 
//			 System.out.println(aitem.getItemCode()+"---"+aitem.getLotNo()+"---"+aitem.getlotSl());
//			 c2list=new ArrayList<SelectItem>();
//			 System.out.println("hmThree----"+hmThree.size());
//			 if(hmThree.size()>0)
//				 c2list=hmThree.get(itemCode);
//				 c2list.add(aitem);
//				 //c2list.add(aitem);
//				 hmThree.put(itemCode,c2list) ; 
				 //hmThree.put(itemCode,hmThree.get(itemCode).add(aitem));
				 System.out.println("hmThree----"+hmThree.size());
		 }
		 
	}
	//a2list.clear();
	//b2list.clear();
	//c2list.clear();
	hmOne.putAll(hmTwo);
	hmOne.putAll(hmThree);
	System.out.println("hmOne.size()---"+hmOne.size());
	System.out.println("hmOne---"+hmOne);
	
	Set s=hmOne.keySet();
	Iterator itr=s.iterator();
	System.out.println("s.size()--"+s.size());
	String key="";
	arr=new ArrayList<ArrayList<SelectItem>>();
	while(itr.hasNext())
	{
		key=(String) itr.next();
		System.out.println("Printing Key--"+key);
		a2list=hmOne.get(key);
		System.out.println("a2list--"+a2list);
		numberOfItemsA++;//=a2list.size();
		arr.add(a2list);
	}
	System.out.println("Number of records elligible for scanning "+numberOfItemsA);
	result.add(numberOfItemsA);
	result.add(arr);
	}
	catch(Exception e)
	{
		e.printStackTrace();
		throw new ITMException(e);
	}
	finally
	{
		
		aitem=null;
		hmOne=null;
		hmTwo=null;
		hmThree=null;
		a2list=null;
		b2list=null;
		c2list=null;
		arr=null;
		
	}
	
	return result;
}
public static java.sql.Date addMonths(java.sql.Date date, int months) throws Exception ,ITMException 
{
	java.sql.Date calculatedDate = null;

	if (date != null) {
		final GregorianCalendar calendar = new GregorianCalendar();
		calendar.setTime(date);
		calendar.add(Calendar.MONTH, months);
		calculatedDate = new java.sql.Date(calendar.getTime().getTime());
	}

	return calculatedDate;
}

}
class SelectItem
{
	private String itemCode;
	private String lotNo;
	private String lotSl;
	private String locCode;
	private String analysisClass;
	private String siteCode;
	private String cycleCriteria;
	private double quantity;
	private String unit;
	public SelectItem() 
	{
		// TODO Auto-generated constructor stub
	}
	SelectItem(String itemCode,String lotNo,String lotSl,String locCode,String analysisClass,String siteCode,String cycleCriteria,double quantity,String unit)
	{
		this.itemCode=itemCode;
		this.lotNo=lotNo;
		this.lotSl=lotSl;
		this.locCode=locCode;
		this.analysisClass=analysisClass;
		this.siteCode=siteCode;
		this.cycleCriteria=cycleCriteria;
		this.quantity=quantity;
		this.unit=unit;
		
	}
	
	public String getItemCode()
	{
		return this.itemCode;
	}
	public void setItemCode(String itemCode)
	{
		this.itemCode=itemCode;
	}
	public String getLotNo()
	{
		return this.lotNo;
	}
	public void setLotNo(String lotNo)
	{
		this.lotNo=lotNo;
	}
	public String getlotSl()
	{
		return this.lotSl;
	}
	public void setLotSl(String lotSl)
	{
		this.lotSl=lotSl;
	}
	public String getLocCode()
	{
		return this.locCode;
	}
	public void setLocCode(String locCode)
	{
		this.locCode=locCode;
	}
	public String getAnalysisClass()
	{
		return this.analysisClass;
	}
	public void setAnalysisClass(String analysisClass)
	{
		this.analysisClass=analysisClass;
	}
	public String getSiteCode()
	{
		return this.siteCode;
	}
	public void setSiteCode(String siteCode)
	{
		this.siteCode=siteCode;
	}
	public String getCycleCriteria()
	{
		return this.cycleCriteria;
	}
	public void setCycleCriteria(String cycleCriteria)
	{
		this.cycleCriteria=cycleCriteria;
	}
	public double getQuantity()
	{
		return this.quantity;
	}
	public void setQuantity(double quantity)
	{
		this.quantity=quantity;
	}
	public String getUnit()
	{
		return this.unit;
	}
	public void setUnit(String unit)
	{
		this.unit=unit;
	}
}