/********************************************************
	Title : GLTransferPrc[FI3LFRA001]
	Date  : 08/04/14
	Developer: Chandrashekar

 ********************************************************/
package ibase.webitm.ejb.fin;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.system.config.*;
import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;

import org.w3c.dom.*;


import java.text.SimpleDateFormat; 
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class GLTransferPrc extends ProcessEJB implements GLTransferPrcLocal,GLTransferPrcRemote
{	
String loginSiteCode = null;
GenericUtility genericUtility = GenericUtility.getInstance();
String currDateTs = null;
String chgUser = "";
String chgTerm = ""; 
ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
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
	{	
		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,null,true);
	}
	catch (Exception e)
	{			
		System.out.println("Exception :GLTransferPrc :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 ,Connection conn, boolean connStatus) throws RemoteException,ITMException
{
	//Connection conn = null;	
	String resultString = "", errString = "";
	boolean isError = false;
	PreparedStatement pstmt = null;	
	PreparedStatement pstmt1 = null;	
	PreparedStatement pstmt2 = null;
	ResultSet rs = null;
	ResultSet rs1 = null;
	ResultSet rs2 = null;
	String sql = "",sql1="",sql2="";	
	String siteCode = "";	
	String finEntity = "",acctCode = "",currCodeAc = "",acctPrdFr = "",acctPrdTo="";
	String actCodeFr = "",actCodeTo = "",site = "",cctrCode="";
	double oldCrAmt=0,oldDrAmt=0,oldCrAmtBase=0,oldDrAmtBase=0;
	double sumCrAmt =0,sumDrAmt=0,sumCrAmtBase=0,sumDrAmtBase=0;		
	int cnt=0,count=0,updCnt=0;	
	
	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
	{
		System.out.println("conn::"+conn);
		if(conn == null)
		{
			ConnDriver connDriver = new ConnDriver();			
			conn = connDriver.getConnectDB("DriverITM");			
			connDriver = null;
			conn.setAutoCommit(false);
			connStatus = true;
		}
						
		acctPrdFr = genericUtility.getColumnValue("acct_prd__fr",headerDom );	
		acctPrdTo = genericUtility.getColumnValue("acct_prd__to",headerDom );
		actCodeFr = genericUtility.getColumnValue("acct_code__fr",headerDom );
		actCodeTo   = genericUtility.getColumnValue("acct_code__to",headerDom );
		finEntity   = genericUtility.getColumnValue("fin_entity",headerDom );
		if(actCodeFr == null)
		{
			actCodeFr = "0";			
		}
		if(actCodeTo == null)
		{
			actCodeTo = "z";			
		}
		sql2 = " select site_code from site where fin_entity = ? ";
		pstmt2 = conn.prepareStatement( sql2 );
		pstmt2.setString(1,finEntity);
		rs2 = pstmt2.executeQuery();
		while (rs2.next())
		{
			siteCode = rs2.getString( "site_code" );
			siteCode = siteCode.trim();
			System.out.println("siteCode is =====>"+siteCode);
		
				sql1="select acctbal.fin_entity,acctbal.acct_code,acctbal.curr_code__ac,acctbal.site_code,acctbal.cctr_code " +
						"from acctbal,acctgrp,acctsgrp,accounts where ( acctsgrp.group_code = acctgrp.group_code ) and " +
						"( acctbal.acct_code = accounts.acct_code ) and ( accounts.sgroup_code = acctsgrp.sgroup_code ) and ( ( acctbal.acct_prd = ? ) and " +
						"( acctbal.prd_code = 'zzzzzz' ) and ( acctbal.site_code in ( ? ) ) and ( acctbal.acct_code >= ? ) and " +
						"( acctbal.acct_code <= ? ) and ( acctgrp.acct_cat in ('A','L') ) )";				
				pstmt1 = conn.prepareStatement( sql1 );				
				pstmt1.setString(1,acctPrdFr);
				pstmt1.setString(2,siteCode);
				pstmt1.setString(3,actCodeFr);
				pstmt1.setString(4,actCodeTo);				
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{									
					finEntity = checkNull(rs1.getString(1));					
					acctCode = checkNull(rs1.getString(2));
					currCodeAc = checkNull(rs1.getString(3));					
					site = checkNull(rs1.getString(4));
					cctrCode = checkNull(rs1.getString(5));
					
					System.out.println("acctCode:::["+acctCode+"]");				
				
					sql = "select sum(case when acctbal.cr_amt is null then 0 else acctbal.cr_amt end) as cr_amt, sum(case when acctbal.dr_amt is null then 0 else acctbal.dr_amt end) as dr_amt," +
						  "sum(case when acctbal.cr_amt__base is null then 0 else acctbal.cr_amt__base end) as cr_amt__base,sum(case when acctbal.dr_amt__base is null then 0 else acctbal.dr_amt__base end) as dr_amt__base " +						
						  "from acctbal where acctbal.acct_prd = ? and acctbal.prd_code <> 'zzzzzz'  and "+
						  "acctbal.site_code = ? and	acctbal.fin_entity = ? and "+
						  "acctbal.acct_code = ? and acctbal.cctr_code = ?";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,acctPrdFr);				
					pstmt.setString(2,site);
					pstmt.setString(3,finEntity);					
					pstmt.setString(4,acctCode);
					pstmt.setString(5,cctrCode);					
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						 sumCrAmt = rs.getDouble(1);
						 sumDrAmt = rs.getDouble(2);
						 sumCrAmtBase = rs.getDouble(3);
						 sumDrAmtBase = rs.getDouble(4);					 				
					}
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					
					sql = "update acctbal  set cr_amt = ?, dr_amt = ?,cr_amt__base = ?, dr_amt__base = ? " +
						  " where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
						  "fin_entity = ? and acct_code = ? and cctr_code = ?";
						pstmt = conn.prepareStatement( sql );
						pstmt.setDouble(1,sumCrAmt);				
						pstmt.setDouble(2,sumDrAmt);
						pstmt.setDouble(3,sumCrAmtBase);
						pstmt.setDouble(4,sumDrAmtBase);						
						pstmt.setString(5,acctPrdFr);
						pstmt.setString(6,site);
						pstmt.setString(7,finEntity);
						pstmt.setString(8,acctCode);
						pstmt.setString(9,cctrCode);						
						updCnt = pstmt.executeUpdate();
						if (!(updCnt==1))
						{
							errString = itmDBAccessEJB.getErrorString("","DS000NR","");								
						}
						pstmt.close();
						pstmt = null;
					
						sql = "select count(*) as count from acctbal where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
							  "fin_entity = ?  and acct_code = ? and cctr_code = ?";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1,acctPrdTo);				
							pstmt.setString(2,site);
							pstmt.setString(3,finEntity);
							pstmt.setString(4,acctCode);
							pstmt.setString(5,cctrCode);							
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt(1);
								System.out.println("@@@cnt"+cnt);
							}
							pstmt.close();
							pstmt = null;
							rs.close();
							rs = null;
						
						if(cnt==0)
						{
							sql = "insert into acctbal(acct_prd, prd_code, fin_entity, acct_code, cctr_code, cr_amt, dr_amt, curr_code__ac, cr_amt__base, dr_amt__base," +
								  "chg_date, chg_user, chg_term,site_code )values" +
								  "( ?,?, ?,?, ? , ?, ?, ?, ?, ?, ?, ?,?, ?)";
								pstmt = conn.prepareStatement( sql );
								pstmt.setString(1,acctPrdTo);
								pstmt.setString(2,"zzzzzz");
								pstmt.setString(3,finEntity);
								pstmt.setString(4,acctCode);
								pstmt.setString(5,cctrCode);
								pstmt.setDouble(6,sumCrAmt);
								pstmt.setDouble(7,sumDrAmt);	
								pstmt.setString(8,currCodeAc);
								pstmt.setDouble(9,sumCrAmtBase);
								pstmt.setDouble(10,sumDrAmtBase);
								pstmt.setTimestamp(11, getCurrdateAppFormat() );								
								pstmt.setString(12,chgUser);
								pstmt.setString(13,chgTerm);
								pstmt.setString(14,site);
								pstmt.executeUpdate();								
								pstmt.close();
								pstmt = null;
								
							
						}else
							if(cnt==1)
							{
								sql = "select cr_amt as oldcr_amt, dr_amt as olddr_amt,cr_amt__base as oldcr_amt__base,dr_amt__base as olddr_amt__base "+  
										"from acctbal where acct_prd = ? and prd_code = '000000'  and "+
										"site_code = ? and	fin_entity = ? and "+
										"acct_code = ? and cctr_code = ?";
									pstmt = conn.prepareStatement( sql );
									pstmt.setString(1,acctPrdTo);				
									pstmt.setString(2,site);
									pstmt.setString(3,finEntity);
									pstmt.setString(4,acctCode);
									pstmt.setString(5,cctrCode);									
									rs = pstmt.executeQuery();
									if (rs.next())
									{
										oldCrAmt = Double.parseDouble(rs.getString(1));
										oldDrAmt = Double.parseDouble(rs.getString(2));
										oldCrAmtBase = Double.parseDouble(rs.getString(3));
										oldDrAmtBase = Double.parseDouble(rs.getString(4));
															
									}
									pstmt.close();
									pstmt = null;
									rs.close();
									rs = null;	
									

									sql = "update acctbal  set cr_amt = cr_amt-?+?, dr_amt = dr_amt-?+?,cr_amt__base =cr_amt__base-?+?, dr_amt__base = dr_amt__base-?+? " +
											"where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
											"fin_entity = ?  and acct_code = ? and cctr_code = ?";
										pstmt = conn.prepareStatement( sql );										
										pstmt.setDouble(1,oldCrAmt);
										pstmt.setDouble(2,sumCrAmt);
										pstmt.setDouble(3,oldDrAmt);
										pstmt.setDouble(4,sumDrAmt);
										pstmt.setDouble(5,oldCrAmtBase);
										pstmt.setDouble(6,sumCrAmtBase);
										pstmt.setDouble(7,oldDrAmtBase);
										pstmt.setDouble(8,sumDrAmtBase);
										pstmt.setString(9,acctPrdTo);
										pstmt.setString(10,site);
										pstmt.setString(11,finEntity);
										pstmt.setString(12,acctCode);
										pstmt.setString(13,cctrCode);										
										updCnt = pstmt.executeUpdate();
										if (!(updCnt==1))
										{
											errString = itmDBAccessEJB.getErrorString("","DS000NR","");													
										}
										pstmt.close();
										pstmt = null;
							      }	
										sql = "select count(*) as count from acctbal where acct_prd = ? and prd_code = '000000' and site_code = ? and " +
												"fin_entity = ? and acct_code = ? and cctr_code = ?";
											pstmt = conn.prepareStatement( sql );
											pstmt.setString(1,acctPrdTo);				
											pstmt.setString(2,site);
											pstmt.setString(3,finEntity);
											pstmt.setString(4,acctCode);
											pstmt.setString(5,cctrCode);											
											rs = pstmt.executeQuery();
											if (rs.next())
											{
												count = rs.getInt(1);
												System.out.println("count@@@"+count);
											}
											pstmt.close();
											pstmt = null;
											rs.close();
											rs = null;											

											if(count==0)
											{	
												sql = "insert into acctbal(acct_prd, prd_code, fin_entity, acct_code, cr_amt, dr_amt, curr_code__ac, cr_amt__base, dr_amt__base," +
														" chg_date, chg_user, chg_term,site_code,cctr_code )values" +
														" ( ? , ?, ?,?, ? , ?, ?, ?, ?, ?, ?, ?,?,? )";
													pstmt = conn.prepareStatement( sql );
													pstmt.setString(1,acctPrdTo);	
													pstmt.setString(2,"000000");
													pstmt.setString(3,finEntity);
													pstmt.setString(4,acctCode);
													pstmt.setDouble(5,sumCrAmt);
													pstmt.setDouble(6,sumDrAmt);	
													pstmt.setString(7,currCodeAc);
													pstmt.setDouble(8,sumCrAmtBase);
													pstmt.setDouble(9,sumDrAmtBase);
													pstmt.setTimestamp(10, getCurrdateAppFormat() );								
													pstmt.setString(11,chgUser);
													pstmt.setString(12,chgTerm);													
													pstmt.setString(13,site);
													pstmt.setString(14,cctrCode);												
													pstmt.executeUpdate();													
													pstmt.close();
													pstmt = null;
																							
											}else if(count==1)
											{
												sql = "update acctbal  set cr_amt = ?, dr_amt = ?,cr_amt__base = ?, dr_amt__base = ?" +
														" where acct_prd = ? and prd_code = '000000' and site_code = ? and " +
														"fin_entity = ? and acct_code = ? and cctr_code = ?";
													pstmt = conn.prepareStatement( sql );
													pstmt.setDouble(1,sumCrAmt);				
													pstmt.setDouble(2,sumDrAmt);
													pstmt.setDouble(3,sumCrAmtBase);
													pstmt.setDouble(4,sumDrAmtBase);													
													pstmt.setString(5,acctPrdTo);
													pstmt.setString(6,site);
													pstmt.setString(7,finEntity);
													pstmt.setString(8,acctCode);
													pstmt.setString(9,cctrCode);
													
													updCnt = pstmt.executeUpdate();
													if (!(updCnt==1))
													{
														errString = itmDBAccessEJB.getErrorString("","DS000NR","");															
													}
													pstmt.close();
													pstmt = null;
											}
							//}
				}
				pstmt1.close();
				pstmt1 = null;
				rs1.close();
				rs1 = null;				
			} 	// while 
		pstmt2.close();
		pstmt2 = null;
		rs2.close();
		rs2 = null;
		
	} // 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
				{
					if(connStatus)
					{
						conn.commit();
						System.out.println("commiting connection.............");
					}
					if(errString.equals(""))
					{
						errString = "PROCSUCC";
					}
					resultString = itmDBAccessEJB.getErrorString("",errString,userId);
					
				}
				if(conn != null &&  connStatus)
				{
				conn.close();
				conn = null;
				}
			}
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
		}
	}	
	System.out.println("returning from     "+resultString);
	return resultString;
	} //end process
private String checkNull(String input)
{
	if(input == null)
	{
		input = "";
	}
	return input;
}
private Timestamp getCurrdateAppFormat()
{
    
	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;
}
}

 