/********************************************************
	Title : SundryTransferPrc[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 SundryTransferPrc extends ProcessEJB implements SundryTransferPrcLocal,SundryTransferPrcRemote
{	
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 = "";
	/*Connection conn = null;	
	ConnDriver connDriver = null;
	*/
	boolean isConn= false;
	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);
		}
		 /*connDriver = new ConnDriver();
         conn = connDriver.getConnectDB("Driver");
         conn.setAutoCommit(false);*/
		retStr = process(headerDom, detailDom, windowName, xtraParams, null,isConn );
	}
	catch (Exception e)
	{			
		System.out.println("Exception :SundryTransferPrc :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 = "",contactCode = "",currCodeAc = "",acctPrdFr = "",acctPrdTo="",sundryType = "";
	String sundryCodeFr = "",sundryCodeTo = "",sundryCode = "",site = "",cctrCode="";
	double oldCrAmt=0,oldDrAmt=0,oldCrAmtBase=0,oldDrAmtBase=0,oldAdvAmt=0;
	double sumCrAmt =0,sumDrAmt=0,sumCrAmtBase=0,sumDrAmtBase=0,sumAdvAmt=0;	
	int cnt=0,count=0,updCnt=0;
	int prcCnt = 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 );
		sundryType = genericUtility.getColumnValue("sundry_type",headerDom );
		sundryCodeFr = genericUtility.getColumnValue("sundry_code__fr",headerDom );
		sundryCodeTo   = genericUtility.getColumnValue("sundry_code__to",headerDom );
		finEntity   = genericUtility.getColumnValue("fin_entity",headerDom );
		if(sundryCodeFr == null)
		{
			sundryCodeFr = "0";			
		}
		if(sundryCodeTo == null)
		{
			sundryCodeFr = "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 fin_entity,sundry_type,acct_code,curr_code__ac,sundry_code,contact_code,site_code,cctr_code from sundrybal "+ 
                       "where site_code =? and acct_prd=? and sundry_type = ?  and sundry_code >= ?  and sundry_code <= ? and prd_code = 'zzzzzz'";
				pstmt1 = conn.prepareStatement( sql1 );
				pstmt1.setString(1,siteCode);
				pstmt1.setString(2,acctPrdFr);
				pstmt1.setString(3,sundryType);
				pstmt1.setString(4,sundryCodeFr);
				pstmt1.setString(5,sundryCodeTo);				
				rs1 = pstmt1.executeQuery();
				while (rs1.next())
				{	
							
					finEntity = checkNull(rs1.getString(1));
					sundryType = checkNull(rs1.getString(2));
					acctCode = checkNull(rs1.getString(3));
					currCodeAc = checkNull(rs1.getString(4));
					sundryCode = checkNull(rs1.getString(5));
					contactCode = checkNull(rs1.getString(6));
					site = checkNull(rs1.getString(7));
					cctrCode = checkNull(rs1.getString(8));					
					
					System.out.println("acctCode:::["+acctCode+"]");
					System.out.println("sundryCode:::["+sundryCode+"]");				
				
					sql = "select sum(case when sundrybal.cr_amt is null then 0 else sundrybal.cr_amt end) as cr_amt, sum(case when sundrybal.dr_amt is null then 0 else sundrybal.dr_amt end) as dr_amt," +
						"sum(case when sundrybal.cr_amt__base is null then 0 else sundrybal.cr_amt__base end) as cr_amt__base,sum(case when sundrybal.dr_amt__base is null then 0 else sundrybal.dr_amt__base end) as dr_amt__base, " +
						"sum(case when sundrybal.adv_amt is null then 0 else sundrybal.adv_amt end) as adv_amt "+  
						"from sundrybal where sundrybal.acct_prd = ? and sundrybal.prd_code <> 'zzzzzz'  and "+
						"sundrybal.site_code = ? and	sundrybal.sundry_type = ? and	sundrybal.sundry_code = ? and "+
						"sundrybal.acct_code = ? and sundrybal.cctr_code = ?";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,acctPrdFr);				
					pstmt.setString(2,site);
					pstmt.setString(3,sundryType);
					pstmt.setString(4,sundryCode);
					pstmt.setString(5,acctCode);
					pstmt.setString(6,cctrCode);					
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						 sumCrAmt = Double.parseDouble(rs.getString(1));
						 sumDrAmt = Double.parseDouble(rs.getString(2));
						 sumCrAmtBase = Double.parseDouble(rs.getString(3));
						 sumDrAmtBase = Double.parseDouble(rs.getString(4));
						 sumAdvAmt = Double.parseDouble(rs.getString(5));							 
					}
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					
					sql = "update sundrybal  set cr_amt = ?, dr_amt = ?,cr_amt__base = ?, dr_amt__base = ?," +
						  "adv_amt = ? where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
						  "sundry_type = ? and sundry_code = ? 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.setDouble(5,sumAdvAmt);
						pstmt.setString(6,acctPrdFr);
						pstmt.setString(7,site);
						pstmt.setString(8,sundryType);
						pstmt.setString(9,sundryCode);
						pstmt.setString(10,acctCode);
						pstmt.setString(11,cctrCode);						
						updCnt = pstmt.executeUpdate();						
						if (!(updCnt==1))
						{
							errString = itmDBAccessEJB.getErrorString("","DS000NR","");	
						}
						pstmt.close();
						pstmt = null;
					
							sql = "select count(*) as count from sundrybal where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
							  "sundry_type = ? and sundry_code = ? and acct_code = ? and cctr_code = ?";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1,acctPrdTo);				
							pstmt.setString(2,site);
							pstmt.setString(3,sundryType);
							pstmt.setString(4,sundryCode);
							pstmt.setString(5,acctCode);
							pstmt.setString(6,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 sundrybal(acct_prd, prd_code, fin_entity, acct_code, sundry_type, cr_amt, dr_amt, curr_code__ac, cr_amt__base, dr_amt__base," +
								  "adv_amt, chg_date, chg_user, chg_term, sundry_code, contact_code,site_code, cctr_code )values" +
								  "( ?,'zzzzzz', ?,?, ? , ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ? )";
								pstmt = conn.prepareStatement( sql );
								pstmt.setString(1,acctPrdTo);				
								pstmt.setString(2,finEntity);
								pstmt.setString(3,acctCode);
								pstmt.setString(4,sundryType);
								pstmt.setDouble(5,sumCrAmt);
								pstmt.setDouble(6,sumDrAmt);	
								pstmt.setString(7,currCodeAc);
								pstmt.setDouble(8,sumCrAmtBase);
								pstmt.setDouble(9,sumDrAmtBase);
								pstmt.setDouble(10,sumAdvAmt);
								pstmt.setTimestamp(11, getCurrdateAppFormat() );								
								pstmt.setString(12,chgUser);
								pstmt.setString(13,chgTerm);
								pstmt.setString(14,sundryCode);
								pstmt.setString(15,contactCode);
								pstmt.setString(16,site);
								pstmt.setString(17,cctrCode);								
								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, adv_amt as oldadv_amt "+  
										"from sundrybal where acct_prd = ? and prd_code = '000000'  and "+
										"site_code = ? and	sundry_type = ? and	sundry_code = ? and "+
										"acct_code = ? and cctr_code = ?";
									pstmt = conn.prepareStatement( sql );
									pstmt.setString(1,acctPrdTo);				
									pstmt.setString(2,site);
									pstmt.setString(3,sundryType);
									pstmt.setString(4,sundryCode);
									pstmt.setString(5,acctCode);
									pstmt.setString(6,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));
										oldAdvAmt = Double.parseDouble(rs.getString(5));	
										
									}
									pstmt.close();
									pstmt = null;
									rs.close();
									rs = null;	
									

									sql = "update sundrybal  set cr_amt = cr_amt-?+?, dr_amt = dr_amt-?+?,cr_amt__base =cr_amt__base-?+?, dr_amt__base = dr_amt__base-?+?," +
											"adv_amt =adv_amt-?+? where acct_prd = ? and prd_code = 'zzzzzz' and site_code = ? and " +
											"sundry_type = ? and sundry_code = ? 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.setDouble(9,oldAdvAmt);
										pstmt.setDouble(10,sumAdvAmt);
										pstmt.setString(11,acctPrdTo);
										pstmt.setString(12,site);
										pstmt.setString(13,sundryType);
										pstmt.setString(14,sundryCode);
										pstmt.setString(15,acctCode);
										pstmt.setString(16,cctrCode);										
										updCnt = pstmt.executeUpdate();
										if (!(updCnt==1))
										{
											errString = itmDBAccessEJB.getErrorString("","DS000NR","");
										}
										pstmt.close();
										pstmt = null;
							}		
										sql = "select count(*) as count from sundrybal where acct_prd = ? and prd_code = '000000' and site_code = ? and " +
												"sundry_type = ? and sundry_code = ? and acct_code = ? and cctr_code = ?";
											pstmt = conn.prepareStatement( sql );
											pstmt.setString(1,acctPrdTo);				
											pstmt.setString(2,site);
											pstmt.setString(3,sundryType);
											pstmt.setString(4,sundryCode);
											pstmt.setString(5,acctCode);
											pstmt.setString(6,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 sundrybal(acct_prd, prd_code, fin_entity, acct_code, sundry_type, cr_amt, dr_amt, curr_code__ac, cr_amt__base, dr_amt__base," +
														"adv_amt, chg_date, chg_user, chg_term, sundry_code, contact_code,site_code, cctr_code )values" +
														" ( ? , '000000', ?,?, ? , ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ? )";
													pstmt = conn.prepareStatement( sql );
													pstmt.setString(1,acctPrdTo);				
													pstmt.setString(2,finEntity);
													pstmt.setString(3,acctCode);
													pstmt.setString(4,sundryType);
													pstmt.setDouble(5,sumCrAmt);
													pstmt.setDouble(6,sumDrAmt);	
													pstmt.setString(7,currCodeAc);
													pstmt.setDouble(8,sumCrAmtBase);
													pstmt.setDouble(9,sumDrAmtBase);
													pstmt.setDouble(10,sumAdvAmt);
													pstmt.setTimestamp(11, getCurrdateAppFormat() );								
													pstmt.setString(12,chgUser);
													pstmt.setString(13,chgTerm);
													pstmt.setString(14,sundryCode);
													pstmt.setString(15,contactCode);
													pstmt.setString(16,site);
													pstmt.setString(17,cctrCode);													
													pstmt.executeUpdate();														
													pstmt.close();
													pstmt = null;
													
												
											
											}else if(count==1)
											{	
												
												sql = "update sundrybal  set cr_amt = ?, dr_amt = ?,cr_amt__base = ?, dr_amt__base = ?," +
														"adv_amt = ? where acct_prd = ? and prd_code = '000000' and site_code = ? and " +
														"sundry_type = ? and sundry_code = ? 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.setDouble(5,sumAdvAmt);
													pstmt.setString(6,acctPrdTo);
													pstmt.setString(7,site);
													pstmt.setString(8,sundryType);
													pstmt.setString(9,sundryCode);
													pstmt.setString(10,acctCode);
													pstmt.setString(11,cctrCode);													
													updCnt = pstmt.executeUpdate();													
													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;
}
}

 