	
/********************************************************
	Title : JvYearEndClosingDtlPrcEJB
********************************************************/
package ibase.webitm.ejb.fin;
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.lang.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;
import java.text.SimpleDateFormat; 
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class JvYearEndClosingDtlPrc extends ProcessEJB implements JvYearEndClosingDtlPrcLocal,JvYearEndClosingDtlPrcRemote
{	
	String loginSiteCode = null;
	GenericUtility genericUtility = GenericUtility.getInstance();
	String currDateTs = null;
	String chgUser = "";
	String chgTerm = ""; 
	/* public void ejbCreate() throws RemoteException, CreateException 
	{
		System.out.println("Create Method Called....");
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	} */
	public void remove()
	{
	}
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	
	JournalDetBean dtlItems = null;
	
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams)
		throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		String retStr = "";
		System.out.println("Process method called......");
		try
		{				
			
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
			 throw new ITMException(e);
		}
		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 :RecpExcisePrc :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
	}//END OF PROCESS (1)

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;	
		String resultString = "", errString = "";
		boolean isError = false;
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		String sql = "";
		ArrayList jourList = null;
		String tranId = "";
		String acctCodeFrom = "";
		String acctCodeTo = "";
		String prdCodeFrom = "";
		String prdCodeTo = "";
		String siteCode = "";
		String genTranId = "";
		String effDate = "";
		java.sql.Timestamp effDateTs = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();	
		String userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userId");
		loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"termId");
		try
		{	
			ConnDriver connDriver = new ConnDriver();			
			conn = connDriver.getConnectDB("DriverITM");			
			connDriver = null;
			conn.setAutoCommit(false);
			//failMsg = new StringBuffer( "" );
			SimpleDateFormat sdf=null;
			
			acctCodeFrom = genericUtility.getColumnValue("acct_code_from",headerDom );
			acctCodeTo = genericUtility.getColumnValue("acct_code_to",headerDom );	
			prdCodeFrom = genericUtility.getColumnValue("prd_code_from",headerDom );
			prdCodeTo = genericUtility.getColumnValue("prd_code_to",headerDom );
			siteCode   = genericUtility.getColumnValue("site_code",headerDom );
			
			System.out.println("siteCode :"+siteCode); 
			double drAmtHdr = 0;
			double crAmtHdr = 0;
			String finEntity = "";
			String firstSite = "";
			
			if(siteCode != null)				
			{
				StringTokenizer st = new StringTokenizer(siteCode,",");
				StringBuffer finalStr = new StringBuffer("");
				while (st.hasMoreTokens()) 
				{
					//finalStr = finalStr.append(st.nextToken()).append("'").append(",").append("'"); // 23/03/11 manoharan commented
					siteCode = st.nextToken(); //""; // 23/03/11 manoharan each token assigned to sitecode
					siteCode = siteCode.trim(); //finalStr.substring(0,finalStr.length()-3);
					System.out.println("siteCode is =====>"+siteCode);
					/*if( siteCode.indexOf("'") == -1)
					{
						firstSite = siteCode;
					}
					else
					{
						firstSite = siteCode.substring(0,siteCode.indexOf("'"));
					}*/
					System.out.println("firstSite is =====>"+firstSite);
					//sql = "SELECT distinct fin_entity from site where site_code = '"+firstSite+"' ";
					sql = "SELECT fin_entity from site where site_code = ? ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,siteCode);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						finEntity = rs.getString( 1 );
					}
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					jourList = null;
					jourList = new ArrayList();

					sql = "SELECT A.ACCT_CODE, SUM(CASE WHEN A.DR_AMT__BASE IS NULL THEN 0 ELSE A.DR_AMT__BASE END) AMT_DR,"  
							  +"SUM(CASE WHEN A.CR_AMT__BASE IS NULL THEN 0 ELSE A.CR_AMT__BASE END) AMT_CR "//A.CCTR_CODE, "
							//  +" A.FIN_ENTITY "
							  +" FROM ACCTBAL A, "
							  +"ACCOUNTS B, ACCTSGRP C, ACCTGRP D "
							  +"WHERE A.ACCT_CODE = B.ACCT_CODE "
							  +"AND B.SGROUP_CODE = C.SGROUP_CODE "
							  +"AND C.GROUP_CODE = D.GROUP_CODE "
							  +"AND D.ACCT_CAT IN ('I','E') "
							//  +"AND A.SITE_CODE IN ( ? ) "
							//  +"AND A.SITE_CODE IN ('"+siteCode+"') "
							  +"AND A.SITE_CODE = ? "
							  +"AND A.PRD_CODE >= ? "
							  +"AND A.PRD_CODE <=  ? "
							  +"AND A.ACCT_CODE >= ? "
							  +"AND A.ACCT_CODE <= ? "
							//  +"GROUP BY A.PRD_CODE, A.ACCT_CODE, A.CCTR_CODE, A.FIN_ENTITY "
							  +"GROUP BY A.ACCT_CODE "
							  +"HAVING SUM(CASE WHEN A.DR_AMT__BASE IS NULL THEN 0 ELSE A.DR_AMT__BASE END) <> SUM(CASE WHEN A.CR_AMT__BASE " +"IS NULL THEN 0 ELSE A.CR_AMT__BASE END)";
					
					//System.out.println("sql ::"+sql);
					pstmt = conn.prepareStatement( sql );
					
					pstmt.setString( 1, siteCode );
					pstmt.setString( 2, prdCodeFrom );
					pstmt.setString( 3, prdCodeTo );
					pstmt.setString( 4, acctCodeFrom );
					pstmt.setString( 5, acctCodeTo );
					//System.out.println("After setting data");
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						dtlItems = new JournalDetBean();
						
						dtlItems.acct_code = rs.getString(1);
					//	dtlItems.cctr_code = rs.getString(4);
						double amtDr = Double.parseDouble(rs.getString(2));
						double amtCr = Double.parseDouble(rs.getString(3));
					//	finEntity = rs.getString(5);
						
						if (amtDr >= amtCr)
						{
							dtlItems.drcrFlag = "C";
							dtlItems.amount = amtDr - amtCr;
							crAmtHdr = crAmtHdr + dtlItems.amount;
						}
						else
						{
							dtlItems.drcrFlag = "D";
							dtlItems.amount = amtCr - amtDr;
							drAmtHdr = drAmtHdr + dtlItems.amount;
						}
						jourList.add( dtlItems );
					}
					pstmt.close();
					pstmt = null;
					System.out.println("Out of while loop");
					if( jourList.size() > 0 )
					{
						sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						Timestamp currDate = new Timestamp(System.currentTimeMillis());
						currDateTs = sdf.format(currDate).toString();
						//genTranId = generateTranId("W_CLOSINGDTL_PROC", conn);
						genTranId = generateTranId("W_CLOSINGDTL_PROC",siteCode, conn);
						drAmtHdr = Math.abs(drAmtHdr);
						crAmtHdr = Math.abs(crAmtHdr);
						effDate   = genericUtility.getColumnValue("eff_date",headerDom );
						effDateTs = Timestamp.valueOf(genericUtility.getValidDateString(effDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
						//errString = insertJournal( jourList, genTranId, drAmtHdr,crAmtHdr, finEntity, firstSite, effDateTs, conn );
						errString = insertJournal( jourList, genTranId, drAmtHdr,crAmtHdr, finEntity, siteCode, effDateTs, conn );
					}
					else
					{
						System.out.println("No record found for site [" + siteCode + "]");
						//errString = "VTNORECFND";
					}
				} 	// while tokan // 23/03/11 manoharan
			}//end if
		} // end of try code 
	   	catch(Exception e)
		{
			isError = true;
			e.printStackTrace();
			errString = e.getMessage();
			throw  new ITMException(e);
		}		
		finally
		{
			try
			{
				if(rs != null)rs.close();
				rs = null;
				if(pstmt != null)pstmt.close();
				pstmt = null;				
				if(conn != null)
				{
					if(isError)
					{
						conn.rollback();
						System.out.println("connection rollback.............");
						resultString = itmDBAccessEJB.getErrorString("","PROCFAILED",userId);
					}	
					else
					{
						conn.commit();
						System.out.println("commiting connection.............");
						if(errString.equals(""))
						{
							errString = "PROCSUCC";
						}
						resultString = itmDBAccessEJB.getErrorString("",errString,userId);
						
						/* if( failMsg != null && failMsg.toString().trim().length() > 0 )
						{	
							String begPart = resultString.substring( 0, resultString.indexOf("<trace>") + 7 );
							String endPart = resultString.substring( resultString.indexOf("</trace>"));
							String mainStr = begPart + "Following Employee Codes can not be processded due to \n invalid adjustment amount entered :\n" + failMsg.toString() + endPart;
							resultString = mainStr;
							begPart =null;
							endPart =null;
							mainStr =null;
						} */
					}	
					conn.close();
					conn = null;	
				}
			}
			catch(SQLException sqle)
			{
				sqle.printStackTrace();
			}
		}	
		System.out.println("returning from     "+resultString);
		return resultString;
	} //end process
	
	private String insertJournal( ArrayList dtlList, String tranId, double drAmtHdr, double crAmtHdr, String finEntity, String firstSite, Timestamp effDateTs, Connection conn) throws RemoteException,ITMException
	{
		PreparedStatement pstmt = null;	
		String hdrInsertSql = "";
		String dtlInsertSql = "";
		String cctrCode = null;
		String currCode = "";
		ResultSet rs = null;
		int lineNo = 0;
		try
		{
			String getCurr = "SELECT curr_code from finent where fin_entity = ?";
			pstmt = conn.prepareStatement( getCurr );
			pstmt.setString( 1,finEntity);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				currCode = rs.getString("curr_code");
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			
			hdrInsertSql = "insert into journal( " 
					+" TRAN_ID, TRAN_DATE, EFF_DATE, FIN_ENTITY, CURR_CODE,  "
					+" EXCH_RATE, DR_AMT, CR_AMT, REMARKS, REVERSIBLE, SITE_CODE, "
					+" CHG_DATE, CHG_USER, CHG_TERM, SITE_CODE__REV, SITE_CODE__FOR, "
					+" AUTO_REV, CONFIRMED, STATUS )"
					+" VALUES( ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ?, ?, ?, ?, ?, "
						+" ?, ?, ?, ?, ? ) ";
								
			System.out.println( "Hdr Insert Sql :: " + hdrInsertSql );
			pstmt = conn.prepareStatement( hdrInsertSql );
			pstmt.setString( 1, tranId );
			//pstmt.setTimestamp( 2, getCurrdateAppFormat() );
			//pstmt.setTimestamp( 3, getCurrdateAppFormat() );
			pstmt.setTimestamp( 2, effDateTs );
			pstmt.setTimestamp( 3, effDateTs );
			pstmt.setString( 4, finEntity );
			pstmt.setString( 5, currCode );
			pstmt.setDouble( 6, 1 );
			pstmt.setDouble( 7, drAmtHdr );
			pstmt.setDouble( 8, crAmtHdr );
			pstmt.setString( 9, "Autogenerated" );
			pstmt.setString( 10, "N" );
			pstmt.setString( 11, firstSite );
			pstmt.setTimestamp( 12, getCurrdateAppFormat() );
			pstmt.setString( 13, chgUser );
			pstmt.setString( 14, chgTerm );
			pstmt.setString( 15, firstSite );
			pstmt.setString( 16, firstSite );
			pstmt.setString( 17, "N" );
			pstmt.setString( 18, "N" );
			pstmt.setString( 19, "A" );
			
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
			
			String getCctr = "SELECT var_value from finparm "
			                +" where prd_code ='999999' AND var_name = ? ";
			pstmt = conn.prepareStatement( getCctr );
			pstmt.setString( 1, "YEAR_END_CCTR" );
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cctrCode = rs.getString("var_value");
			}
			if( cctrCode == null )
			{
				cctrCode = "    ";
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			System.out.println("CCTR_CODE is==>"+cctrCode+"<==");
			int dtlListLen = dtlList.size();
			for( int dtlListIdx = 0; dtlListIdx < dtlListLen ; dtlListIdx++)
			{
				dtlItems = ( JournalDetBean ) dtlList.get( dtlListIdx );
				lineNo = lineNo + 1;
				dtlInsertSql = "insert into jourdet( " 
						+" TRAN_ID, LINE_NO, ACCT_CODE, CCTR_CODE, DRCR_FLAG,  "
						+" AMOUNT, REMARKS )"
						+" VALUES( ?, ?, ?,'"+cctrCode+"', ?, ?, ?) ";
						
				System.out.println( "Hdr Insert Sql :: " + dtlInsertSql );
				pstmt = conn.prepareStatement( dtlInsertSql );
				pstmt.setString( 1, tranId );
				pstmt.setInt( 2, lineNo );
				pstmt.setString( 3, dtlItems.acct_code );
				//pstmt.setString( 4,"    ");
				pstmt.setString( 4, dtlItems.drcrFlag );
				pstmt.setDouble( 5, dtlItems.amount );
				pstmt.setString( 6, "Autogenerated" );
				
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}
	
	private Timestamp getCurrdateAppFormat()
    {
        String s = "";	
		Timestamp timestamp = null;		
        GenericUtility genericUtility = GenericUtility.getInstance();
        try
        {
            java.util.Date date = null;
            timestamp = new Timestamp(System.currentTimeMillis());
            
			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
            date = simpledateformat.parse(timestamp.toString());
            timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
		}
        catch(Exception exception)
        {
            System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
        }
        return timestamp;
    }
	
	private String generateTranId(String windowName,String siteCode, Connection conn) throws Exception 
	{
			ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql = "",errCode ="",errString ="";
			String tranId = null;
			String newKeystring = "";
			boolean found =false;
			String xmlValues = "";
			 try
			 {
				sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)=UPPER('"+windowName+"')";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				System.out.println("keyString :"+rs.toString());
				String tranSer1 = "";
				String keyString = "";
				String keyCol = "";
				if (rs.next())
				{	
					found =true;
					keyString = rs.getString(1);
					keyCol = rs.getString(2);
					tranSer1 = rs.getString(3);				
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if(!found)
				{	  
					  sql ="SELECT key_string,TRAN_ID_COL, REF_SER from transetup where tran_window = 'GENERAL' ";
					  pstmt	= conn.prepareStatement(sql);
					  rs = pstmt.executeQuery();
					  if (rs.next())
					  {	
						keyString = rs.getString(1);
						keyCol = rs.getString(2);
						tranSer1 = rs.getString(3);	
					  }
					  rs.close();
					  rs = null;
					  pstmt.close();
					  pstmt = null ;
				}
				if(keyString ==null || keyString.trim().length() ==0)
				{
					errCode = "VTSEQ";
					System.out.println("errcode......"+errCode);
					errString = itmDBAccessEJB.getErrorString("","VTSEQ","BASE","",conn);

				}
				
				xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<tran_id></tran_id>";
				xmlValues = xmlValues + "<site_code>"+siteCode.trim()+"</site_code>" ;
				xmlValues = xmlValues + "<tran_date>"+currDateTs+"</tran_date>" ;
				xmlValues = xmlValues + "</Detail1></Root>";
				
				System.out.println("keyString=>"+keyString);
				System.out.println("keyCol=>"+keyCol);
				System.out.println("tranSer1"+tranSer1);
				
				System.out.println("xmlValues  :["+xmlValues+"]");
				
				TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
				tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			
				System.out.println(" new tranId :"+tranId);
				if(rs!=null)
				 {
					rs.close();
				 }
				 if(pstmt!=null)
				 {	
					pstmt.close();
				 }
			}
			catch(SQLException ex)
			{
				System.out.println("Exception ::" +sql+ ex.getMessage() + ":");			
				ex.printStackTrace();
				tranId=null;
				throw new  Exception(ex);
				
			}
			catch(Exception e)
			{
				System.out.println("Exception ::" + e.getMessage() + ":");
				e.printStackTrace();
				tranId=null;
				throw new  Exception(e);
			}
			return tranId;
	}//generateTranTd()
	
	private class JournalDetBean
	{
		public String acct_code = null;
		public String cctr_code = null;
		public String drcrFlag = null;
		public double amount = 0;
	}
}//end class