
/********************************************************
	Title : WokCalendarPrcEJB
	Date  : 23/12/08
	Author: Pankaj singh

********************************************************/
package ibase.webitm.ejb.mfg;
import ibase.webitm.utility.*;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.*;
import ibase.system.config.*;
import java.rmi.RemoteException;
import java.text.*;
import java.util.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import java.io.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import java.util.GregorianCalendar;
import java.util.Calendar;
import java.util.Date;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless;

@Stateless 
public class WorkCalendarPrc extends ProcessEJB implements WorkCalendarPrcLocal, WorkCalendarPrcRemote
{
	Connection sqlConn = null;
	Connection oraConn = null;
	String errorString = null;
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	Node childNode = null;
	int ctr = 0,cnt = 0;
	String childNodeName = null;
	String errCode = "";
	int currentFormNo=0;
	int childNodeListLength;
	String selectQry = "";
	String insertQry = "";	
	String upDateQry = "";	
	String selectDtl1Qry = "";	
	String selectDtl2Qry = "";	
	String insertDtl1Qry = "",newCustCode = "";	
	String insertDtl2Qry = "";	
	String selectItem = "";	
	ResultSet rs = null;
	ResultSet detail1Rs = null;
	ResultSet detail2Rs = null;
	PreparedStatement psmt = null;
	PreparedStatement dtl1Psmt = null;
	PreparedStatement dtl2Psmt = null;
	Statement stmt = null;
	Statement dtl2Stmt = null;
	ResultSet selectRs = null;
	Statement selectStmt = null;
	ConnDriver connDriver = null;
	ResultSet dtlRs = null;
	String working = "";
	StringBuffer xmlString = null;
	int date = 0,month= 0,year= 0,hour = 0,min = 0,sec = 0;
	Date d = null;	
	//BasePreparedStatement oraPsmt = null;
	PreparedStatement oraPsmt = null;
	Statement st = null;
	SimpleDateFormat simpleDateFormat  = new SimpleDateFormat("yyyy-MM-dd");
	int upd = 0;
	
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	
	/* public void ejbCreate() throws RemoteException, CreateException 
	{
		System.out.println("Create Method Called....");
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	} */
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams)
		throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{			
			System.out.println("Exception :WokCalendarPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	}//END OF PROCESS (1)

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{		
		String retStr = "";
		PreparedStatement pstmt = null;
		PreparedStatement InsertPstmt = null;
		ResultSet rs = null;
		String sql = "" ,sqlw = "",errString = "";
        String retString = "" ,errCode = "",tranId="";
		String loginSiteCode="",empCode="",chgUser="",chgTerm="",loginCode="";
		String 	policyNo="",insCertNo="",agent_code="",invoiceId="";
		int count =0,cnt =0,cnt1 = 0;
		String curr_code="",dayNo = "",shiftOfDay = "";
	    double exch_rate=0,netAmt=0;
	    Timestamp tranDate =null;
		HashMap insUpdHMap = null;
		String siteCode = "",wokCntr = "",datefrom = "",dateto = "",holTblno = "",insertQry = "";
		connDriver = new ConnDriver();
		Map shift = null;
		shift = new HashMap();
		Calendar cal = null;
		java.sql.Timestamp ToDate =null,FromDate = null;
		
		try
		{
			System.out.println("xtraParams :::::::::::::::::::::::::::::::::: "+xtraParams);
			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			empCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userId");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId");
			loginCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("empCode..............."+empCode);
			System.out.println("Login Site Code............."+loginSiteCode);
			System.out.println("userId............."+chgUser);
			System.out.println("termId............."+chgTerm);
			oraConn = connDriver.getConnectDB("DriverITM");
			oraConn.setAutoCommit(false);
			connDriver = null;
			siteCode  = genericUtility.getColumnValue("site_code",headerDom);	
			wokCntr  = genericUtility.getColumnValue("work_cntr",headerDom);
			//wokCntr = wokCntr == null ? "" :wokCntr;
			datefrom  = genericUtility.getColumnValue("date_from",headerDom);	
			dateto  = genericUtility.getColumnValue("date_to",headerDom);	
			System.out.println("Values from dom \n sitecode " + siteCode + "\n wokCntr " + wokCntr + "\n datefrom " + datefrom + "\n dateto " + dateto );
			//sql = "Select Count(*)  from site where site_code = '"+siteCode+"' ";
			/* sqlw = "Select Count(*)  from workcntr where work_ctr = '"+wokCntr+"' "; */
			//System.out.println("select qry from site_code.." + sql);
			/*if(siteCode == null || siteCode.trim().length() == 0)
			{
				return errString = itmDBAccessEJB.getErrorString("","SPSTCODNUL",chgUser);
			}
			if(wokCntr == null || wokCntr.trim().length() == 0)
			{
				return errString = itmDBAccessEJB.getErrorString("","SPSTCODNUL",chgUser);
			}*/
			if(siteCode == null || siteCode.trim().length() == 0)
			{
				return errString = itmDBAccessEJB.getErrorString("","SPSTCODNUL",chgUser);
			}
			else
			{
				sql = "Select count(*)  from site where site_code = '"+siteCode+"' ";
				pstmt= oraConn.prepareStatement(sql);
				rs = pstmt.executeQuery(); 
				if(rs.next())
				{
				   cnt = rs.getInt(1);
				}							
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;
				if (cnt == 0)
				{
					return errString = itmDBAccessEJB.getErrorString("","VMSITE1",chgUser);
				}//VMWCTR1					
			}
			if(wokCntr != null)
			{	
				sqlw = "Select count(*)  from workcntr where work_ctr = '"+wokCntr+"' ";
				pstmt= oraConn.prepareStatement(sqlw);
				rs = pstmt.executeQuery(); 
				if(rs.next())
				{
				   cnt1 = rs.getInt(1);
				}							
				pstmt.close();
				rs.close();
				pstmt = null;
				rs = null;
				if (cnt1 == 0)
				{
					return errString = itmDBAccessEJB.getErrorString("","VMWCTR1",chgUser);
				}				
			}
			if(datefrom == null || datefrom.trim().length() == 0 || dateto == null || dateto.trim().length() == 0)
			{
				return errString = itmDBAccessEJB.getErrorString("","VEDAT2",chgUser);
			}
			else
			{
				datefrom = genericUtility.getValidDateString(datefrom,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat());
				System.out.println(" from Date in dbdate format " + datefrom );
				FromDate = java.sql.Timestamp.valueOf(datefrom + " 00:00:00.00");
				FromDate =  new java.sql.Timestamp((FromDate.getTime() - (24*60*60))); // added to change reduce date by 1 day on 2/feb/2009 by pankaj
				System.out.println(" from Date in timestamp " + FromDate );
				dateto = genericUtility.getValidDateString(dateto,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat());
				System.out.println(" to Date in dbdate format " + dateto );
				ToDate = java.sql.Timestamp.valueOf(dateto + " 00:00:00.00");
				System.out.println(" from Date in timestamp " + ToDate );
				if(FromDate.after(ToDate))
				{
					return errString = itmDBAccessEJB.getErrorString("","VMVALIDDT",chgUser);
				}
			}			
			if(wokCntr == null || wokCntr.trim().length() == 0)
			{
				sql = "select	count(*) "
						+ " from	work_calendar "
						+ " where site_code = '" + siteCode + "'"
						+ " and work_date between ? and ? ";
			}
			else
			{
				sql = "select	count(*) "
					+ " from	work_calendar "
					+ " where site_code = '" + siteCode + "'"
					+ " and work_ctr = '" + wokCntr + "'"
					+ " and work_date between ? and ? ";
			}
			System.out.println("sql for count ....... " + sql);
			pstmt = oraConn.prepareStatement(sql);
			pstmt.setTimestamp(1,FromDate);
			pstmt.setTimestamp(2,ToDate);
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("no of records found " + count);
			if( count > 0)
			{
				if(wokCntr == null || wokCntr.trim().length() == 0)
				{
					sql = " delete "
							+ " from	work_calendar "
							+ " where site_code = '" + siteCode + "'"
							+ " and work_date between ? and ? ";
				}
				else
				{
					sql = " delete "
						+ " from	work_calendar "
						+ " where site_code = '" + siteCode + "'"
						+ " and work_ctr = '" + wokCntr + "'"
						+ " and work_date between ? and ? ";
				}
				System.out.println("sql for delete " + sql);
				pstmt = oraConn.prepareStatement(sql);
				pstmt.setTimestamp(1,FromDate);
				pstmt.setTimestamp(2,ToDate);
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
				System.out.println("records deleted  " + count);
				//caled insertSchedule to insert schehedule after deleting existing records
				//errString =  insertSchedule(siteCode,wokCntr,FromDate,ToDate,chgUser,chgTerm);
			}
			errString = insertSchedule(siteCode,wokCntr,FromDate,ToDate,chgUser,chgTerm,oraConn);
				if(errString != null && errString.trim().length() == 0)
				{
					oraConn.commit();
					System.out.println("Connection commited................. ");
					errString = itmDBAccessEJB.getErrorString("","WRKCALSUCC",chgUser);
				}
				else if(errString.equals("VTNORECFND"))
				{
					System.out.println("Connection rollbacked");
					oraConn.rollback();
					errString = itmDBAccessEJB.getErrorString("","VTNORECFND",chgUser);
				}
				else
				{
					System.out.println("Connection rollbacked");
					oraConn.rollback();
				}
		}
	   	catch(Exception e)
		{
			   System.out.println("Exception in WokCalendarPrcEJB..."+e.getMessage());
			   e.printStackTrace();
			   errString = e.getMessage();
		}
		finally
		{
			try
			{
				if(rs != null)rs.close();
				rs = null;
				if(pstmt != null)pstmt.close();
				pstmt = null;
				if( InsertPstmt != null)InsertPstmt.close();
				InsertPstmt = null;
				if(oraConn != null)oraConn.close();
				oraConn = null;	
			}
			catch(SQLException sqle)
			{
				sqle.printStackTrace();
			}
		}
		System.out.println("returning from  WokCalendarPrcEJB   "+errString);
		return errString;
	}//end process
	public String insertSchedule(String siteCode,String wokCntr,Timestamp FromDate,Timestamp ToDate,String chgUser,String chgTerm,Connection oraConn )
	{
		String retStr = "";
		PreparedStatement pstmt = null;
		PreparedStatement InsertPstmt = null;
		ResultSet rs = null;
		String sql = "" ,sql1 = "",errString = "";
        String retString = "" ,errCode = "",tranId="";
		String loginSiteCode="",empCode="",loginCode="";
		String 	policyNo="",insCertNo="",agent_code="",invoiceId="";
		int count =0,cnt =0;
		String curr_code="",dayNo = "",shiftOfDay = "";
	    double exch_rate=0,netAmt=0;
	    Timestamp tranDate =null;
		HashMap insUpdHMap = null;
		String datefrom = "",dateto = "",holTblno = "",insertQry = "";
		
		Map shift = null;
		shift = new HashMap();
		int upd[] = new int[10];
		Calendar cal = null;
		//java.sql.Timestamp ToDate =null,FromDate = null;
		System.out.println("Inserting records..........");
		try
		{
			sql = "select hol_tblno from site where site_code = '" + siteCode + "'";
		System.out.println("Sql for hol_tblno ::::: " + sql);
		pstmt = oraConn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while( rs.next() )
		{
			holTblno = rs.getString(1);
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;	
		System.out.println(" hol_tblno " + holTblno);
		do
		{
			if(FromDate != null  )
			{
				cal = Calendar.getInstance();
				System.out.println("cal today :- " + cal.getTime());
				d= new Date(FromDate.getTime());
				cal.setTime(d);
				//cal.set(year,month,date);
				System.out.println("cal :- " + cal.getTime());
				cal.add(Calendar.DAY_OF_MONTH,1);
				System.out.println("cal incr :- " + cal.getTime());
				FromDate = new java.sql.Timestamp(cal.getTime().getTime() );
				System.out.println("FromDate :- " + FromDate);
				System.out.println(FromDate.toString().substring(0,11) + "00:00:00.0");
				String chan = FromDate.toString().substring(0,11) + "00:00:00.0";
				System.out.println(chan);
				FromDate = Timestamp.valueOf(chan);
				System.out.println("FromDate changed :- " + FromDate);
			}
			sql = " select	count(*) "
						+ " from holiday "
						+ " where hol_tblno = '" + holTblno + "'"
						+ " and	hol_date  = ?";
			pstmt = oraConn.prepareStatement(sql);
			pstmt.setTimestamp(1,FromDate);
			rs = pstmt.executeQuery();
			while( rs.next())
			{
				count = rs.getInt(1);
			}
			System.out.println("no of holidays " + count);
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if( count == 0) 
			{
				working = "Y"; 
			}
			else
			{
				working = "N"; 
			}
			System.out.println("working status " + working);
			insertQry = " INSERT INTO work_calendar "
					+ "("
					+ "	site_code,work_date,day_no,"
					+ " shift ,working,confirmed,"
					+ "	chg_date,chg_user,chg_term,"
					+ "	work_ctr )"
					+ " VALUES (?,?,?,"
					+ " ?,?,?,"
					+ " ?,?,?,"
					+ " ?)";
			System.out.println("Insert query " + insertQry);
			InsertPstmt = oraConn.prepareStatement(insertQry);
			java.sql.Timestamp currDate = null;
			String currAppdate = "";
			currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
			System.out.println("\n today date----" + currDate);
			currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDate).toString();
			System.out.println("\n today currDate in apl----" + currAppdate);
			currAppdate = genericUtility.getValidDateString(currAppdate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat());
			System.out.println("\n today currDate in DB----" + currAppdate);
			currDate = Timestamp.valueOf(currAppdate + " 00:00:00.00");
			System.out.println("\n today currDate in timestamp----" + currDate);
			TreeMap dayMap = new TreeMap();
			TreeMap shiftMap = new TreeMap();
			String day[] = new String[100],shiftAr[] = new String[100];
			Object name = null,dayOb = null,shiftOb = null;
			Object  shftName = null,dayNum = null;	
			int i = 0;
			if(wokCntr == null || wokCntr.trim().length() == 0)
			{
				sql = " SELECT " 
				+ " DAY_NO ,SHIFT "  
				+ " FROM WORKSHFT "  
				+ " WHERE  site_code = '" + siteCode + "'";
			}
			else
			{
				sql =  " SELECT  DAY_NO ,SHIFT FROM WORKCNTRSHFT"
							+ " WHERE  site_code = '" + siteCode + "'"
							+ " and work_ctr = '" + wokCntr + "'";
			}			
			System.out.println("select day,shift query :::: " + sql);
			pstmt = oraConn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				dayNo = rs.getString(1);
				dayMap.put("day[" + i +"]",dayNo);
				System.out.println("dayNo : " + dayNo);
				shiftOfDay = rs.getString(2);
				System.out.println("shiftOfDay : " + shiftOfDay);
				shiftMap.put("shiftAr[" + i +"]",shiftOfDay);
				i++;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(dayMap.size() > 0 && shiftMap.size() > 0)
			{
				Iterator it = dayMap.keySet().iterator();
				Iterator itshft = shiftMap.keySet().iterator();
				System.out.println("Reading data...... " );
				while( itshft.hasNext() )
				{
					while(it.hasNext() )
					{
						System.out.println("Inside map..................");
						InsertPstmt.setString(1,siteCode);
						InsertPstmt.setTimestamp(2,FromDate);
						System.out.println("primary setting work date " + FromDate);
						dayNum = it.next();	
						dayOb = dayMap.get(dayNum);
						//System.out.println(" primary Day:- " + dayNum + " value:-" + dayOb);
						InsertPstmt.setString(3,dayOb.toString());
						shftName = itshft.next();	
						shiftOb = shiftMap.get(shftName) ;
						System.out.println(" primary Day:- " + dayNum + " value:-" + dayOb +" primary Shift:- " + shftName + " value:-" + shiftOb);
						InsertPstmt.setString(4,shiftOb.toString());
						InsertPstmt.setString(5,working);
						InsertPstmt.setString(6,"N");
						InsertPstmt.setTimestamp(7,currDate);
						InsertPstmt.setString(8,chgUser);
						InsertPstmt.setString(9,chgTerm);
						//InsertPstmt.setString(10,chgTerm);
						System.out.println(" primary setting wokCntr ::::: " + wokCntr);
						if(wokCntr == null || wokCntr.trim().length() == 0)
						{
							InsertPstmt.setString(10," ");
						}
						else
						{
							InsertPstmt.setString(10,wokCntr);
						}
						InsertPstmt.addBatch();
					}
				}
				InsertPstmt.executeBatch();
			}
			else
			{
				errString = "VTNORECFND";
			}
			//oraConn.commit();
			//System.out.println("data inserted in work calendar sucesfully!" + upd);
		}
		while( FromDate.before(ToDate) );	
		}//end try
		catch (Exception exception)
		{
			exception.printStackTrace();
			errString = exception.getMessage();
		}
		//System.out.println("errString in insert schedule.........." + errString);
		return errString;
	}//end inser schedule

}//end class