package ibase.webitm.ejb.sys;

import ibase.system.config.ConnDriver;
import ibase.utility.GenericUtility;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.text.DecimalFormat;
import java.util.Calendar;
import java.util.Date;

import javax.ejb.Stateless;

@Stateless
public class AcctprdGenPrd extends ActionHandlerEJB implements AcctprdGenPrdLocal,AcctprdGenPrdRemote 
{
	
	@Override
	public String actionHandler(String acctPrd, String xtraParams, String forcedFlag) throws RemoteException, ITMException 
	{
		//Connection  connectionObject = null;
		String errString = "";
		try
		{
			errString =  GenPrd(acctPrd,xtraParams,forcedFlag);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		System.out.println("Returning Result ::"+errString);
		return errString;
	}
	
	public String GenPrd(String acctPrd ,String xtraParams, String forcedFlag) throws ITMException
	{
		String sql="",errString="";
		PreparedStatement pstmt =null,pstmtUpd = null  ;
		ResultSet rs=null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		java.util.Date newDate = new Date();
		Connection conn=null;
		SimpleDateFormat sdf = null;
		
		int cnt = 0;
		String prdCode = "", termId = "", userId = "";
		Timestamp dateFrom = null, dateTo = null, lastDay = null, firstDay = null, today = null;
		
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			GenericUtility genericUtility = GenericUtility.getInstance();
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			termId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId");
			sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());	
			today = java.sql.Timestamp.valueOf(sdf.format(newDate).toString() + " 00:00:00.000");

			sql = "insert into period (acct_prd, code, descr, fr_date, to_date, chg_user, chg_date, chg_term, stat_ic, stat_fin, stat_adm, stat_pur, stat_sal, stat_mfg ) "
				+ " values ( ?, ?, ?, ?, ?, ?,  ?, ?, 'N', 'N', 'N', 'N', 'N', 'N') ";
			pstmtUpd = conn.prepareStatement(sql);
			
			
			sql = " SELECT FR_DATE, TO_DATE FROM acctprd WHERE  CODE    = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,acctPrd);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				dateFrom = rs.getTimestamp("FR_DATE");
				dateTo = rs.getTimestamp("TO_DATE");
			}
			rs.close();
			rs =null;
			pstmt.close();
			pstmt =null;
	
			// if any period exist whose dates fall between the a/c period dates, 
			// autogenerate is terminated.
			sql = "select count(*) FROM  period where ( fr_date between ? and ? ) or  ( to_date between ? and ? )" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setTimestamp(1,dateFrom);
			pstmt.setTimestamp(2,dateTo);
			pstmt.setTimestamp(3,dateFrom);
			pstmt.setTimestamp(4,dateTo);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cnt = rs.getInt(1);
			}
			rs.close();
			rs =null;
			pstmt.close();
			pstmt =null;
			System.out.println("Total count for period for acctPrd [" + cnt + "]");
			if (cnt > 0 )
			{
				errString = itmDBAccessEJB.getErrorString("","VMPRDEXT",userId);
				return errString;
			}
	
			Calendar cal = Calendar.getInstance();
			cal.setTime(dateFrom);
			cal.add(Calendar.MONTH, 1);  
			cal.set(Calendar.DAY_OF_MONTH, 1);  
			cal.add(Calendar.DATE, -1);  
			newDate = cal.getTime();
			sdf = new SimpleDateFormat("yyyy-MM-dd");
			lastDay =  java.sql.Timestamp.valueOf(sdf.format(newDate) + " 00:00:00.000");

			firstDay = dateFrom;
			if (lastDay.compareTo(dateTo) > 0)
			{	
				lastDay = dateTo; 
			}
			//sdf = new SimpleDateFormat("yyyyMM");
			prdCode = sdf.format(firstDay);
			prdCode = prdCode.replaceAll("-","");
			System.out.println("prdCode [ " + prdCode + "] [" + prdCode.substring(1,6) + "]" );
			prdCode = prdCode.substring(0,6) ;// sdf.format(firstDay);
			System.out.println("1 firstDay [" + firstDay + "] lastDay [" + lastDay + "] prdCode [" + prdCode + "]");

	
			pstmtUpd.setString(1,acctPrd);
			pstmtUpd.setString(2,prdCode);
			pstmtUpd.setString(3,prdCode);
			pstmtUpd.setTimestamp(4,firstDay);
			pstmtUpd.setTimestamp(5,lastDay);
			pstmtUpd.setString(6,userId);
			pstmtUpd.setTimestamp(7,today);
			pstmtUpd.setString(8,termId);
			pstmtUpd.addBatch();
			pstmtUpd.clearParameters();
			
			while(dateTo.compareTo(lastDay) != 0)
			{
				cal.add(Calendar.DATE, 1);  
				newDate = cal.getTime();
				sdf = new SimpleDateFormat("yyyy-MM-dd");
				firstDay =  java.sql.Timestamp.valueOf(sdf.format(newDate) + " 00:00:00.000");

				cal.add(Calendar.MONTH, 1);  
				cal.set(Calendar.DAY_OF_MONTH, 1);  
				cal.add(Calendar.DATE, -1);  
				newDate = cal.getTime();
				sdf = new SimpleDateFormat("yyyy-MM-dd");
				lastDay =  java.sql.Timestamp.valueOf(sdf.format(newDate) + " 00:00:00.000");
				if (lastDay.compareTo(dateTo) > 0)
				{	
					lastDay = dateTo; 
				}
				//sdf = new SimpleDateFormat("yyyyMM");
				prdCode = sdf.format(firstDay);
				prdCode = prdCode.replaceAll("-","");
				prdCode = prdCode.substring(0,6) ;// sdf.format(firstDay);
				System.out.println("1 firstDay [" + firstDay + "] lastDay [" + lastDay + "] prdCode [" + prdCode + "]");
			
				pstmtUpd.setString(1,acctPrd);
				pstmtUpd.setString(2,prdCode);
				pstmtUpd.setString(3,prdCode);
				pstmtUpd.setTimestamp(4,firstDay);
				pstmtUpd.setTimestamp(5,lastDay);
				pstmtUpd.setString(6,userId);
				pstmtUpd.setTimestamp(7,today);
				pstmtUpd.setString(8,termId);
				pstmtUpd.addBatch();
				pstmtUpd.clearParameters();
			}
			pstmtUpd.executeBatch();
			pstmtUpd.clearBatch();
			pstmtUpd.close();
			pstmtUpd = null;
			conn.commit();
			errString = itmDBAccessEJB.getErrorString("","VMPRDGEN",userId);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			try
			{
				conn.rollback();
				errString = itmDBAccessEJB.getErrorString("","ERROR",userId);
			}
			catch(Exception ex)
			{
				
			}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if (pstmtUpd != null )
				{
					pstmtUpd.close();
					pstmtUpd = null;
				}
				if( rs != null )
				{
					rs.close();
					rs =null;
				}
				if( conn != null ){
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				
			}
		}
		return errString;
	}
	
	
}
