package ibase.webitm.ejb.fin;
import ibase.system.config.AppConnectParm;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
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.util.Calendar;

import javax.naming.InitialContext;
import javax.xml.rpc.ParameterMode;
import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;
@javax.ejb.Stateless

public class ReceiptAdvPostSave extends ValidatorEJB implements ReceiptAdvPostSaveLocal, ReceiptAdvPostSaveRemote
{        

	FinCommon finCommon = new FinCommon();
	DistCommon distCommon = new DistCommon();

	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}

	public String postSave( String domString, String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{

		System.out.println("In ReceiptAdvPostSave ... ");

		PreparedStatement pstmt = null;
		Document dom = null;                
		ResultSet rs =null;
		boolean isError = false;

		String tranIdRcp = "";
		String tranId = "",lineNo="";
		String errString ="";
		String xmlString =null;
		String sql = null;
		String sqlC=null;
		StringBuffer xmlBuff = new StringBuffer();
		//PreparedStatement pstmt=null;
		String bankCode = null;
		String finEntity = null;
		String currCode = null;
		double exchRate = 0;
		double chqAmt = 0, chqAmtActual = 0 ;
		String tranDate =null;
		String custCode =null;
		String siteCode = null;
		String acctCodeBal = null;
		String acctCodeAr = null;
		String  cctrCodeBal  = null;
		String  cctrCodeAr  = null;
		String acctCodeAdv = null;
		String cctrCodeAdv = null;
		String refNo = null;
		String itemSer = null;
		String varValue = "";
		String loginEmpCode = "";
		String cctrFin = "";
		String cctrFinS = "";
		String acctFin = "";
		String acctFinS = "";
		double rcpAmt=0,finChg=0, diffAmtExchDet = 0, diffAmtExchAdv = 0, diffAmtExchEpc = 0, newAdvAmt = 0;
		double billAmtDet = 0, advAmtHdr = 0, adjAmtAdv = 0, bdAmt =0, advAmtRact = 0, othAmt =0,exchRateHdr = 0, taxAmt = 0;
		int cnt=0;
		try
		{

			GenericUtility genericUtility = GenericUtility.getInstance();
			dom = genericUtility.parseString(domString);

			tranId = GenericUtility.getInstance().getColumnValue("tran_id",dom);
			currCode = GenericUtility.getInstance().getColumnValue("curr_code",dom);

			sql="select adv_amt, exch_rate,chq_amt,curr_Code from receipt WHERE  tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				advAmtHdr = rs.getDouble(1);
				exchRateHdr = rs.getDouble(2);
				chqAmtActual = rs.getDouble(3);
				currCode = rs.getString(4);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			sql="UPDATE rcpdet set exch_rate =  ? "
			+" WHERE  tran_id = ? "
			+ " and (curr_code = ?  or exch_rate is null) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,exchRateHdr );
			pstmt.setString(2,tranId );
			pstmt.setString(3,currCode );
			cnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
				
			sql="UPDATE rcpdet set diff_amt__exch =  (rcp_amt * EXCH_RATE__RCV) - (rcp_amt * exch_rate) "
			+" WHERE  tran_id = ? "
			+ " and (curr_code = ?  or exch_rate is null) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			pstmt.setString(2,currCode );
			cnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
			
			sql="select sum(diff_amt__exch), sum(tax_amt) from rcpdet WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				diffAmtExchDet = rs.getDouble(1);
				taxAmt = rs.getDouble(2);
			}
			rs.close();
			rs = null;
			sql="select sum(diff_amt__exch) from rcpadv WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				diffAmtExchAdv = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			sql="select sum(diff_amt__exch) from rcpepc_adj WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				diffAmtExchEpc = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			
			
			sql="select sum( (case when eff_bank <> 'N' then amount else 0 end) * exch_rate / ? ) oth_amt from rcpacct WHERE  tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,exchRateHdr);
			pstmt.setString(2,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				othAmt = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql="UPDATE receipt SET oth_amt = ?, tax_amt = ? WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);

			pstmt.setDouble(1,othAmt );
			pstmt.setDouble(2,taxAmt );
			pstmt.setString(3,tranId );
			cnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			
			
			sql="select sum(adj_amt) from rcpadv WHERE  tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				adjAmtAdv = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql="select sum(d.rcp_amt * d.exch_rate / h.exch_rate), sum(case when r.bill_disc = 'Y' then d.BILL_DISC_AMT  + d. FIN_CHG else 0 end ) "
				+ " from receipt h, rcpdet d, receivables r "
				+ " WHERE  h.tran_id = d.tran_id "
				+ " and r.tran_ser = d.ref_ser "
				+ " and r.ref_no = d.ref_no "
				+ " and r.line_no__ref = d.line_no__ref "
				+ " and h.tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				billAmtDet = rs.getDouble(1);
				bdAmt = rs.getDouble(2);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			//chqAmt = billAmtDet + bdAmt  + adjAmtAdv; // 21/01/14 bd amount removed from bill_amt
			chqAmt = billAmtDet ;//+ adjAmtAdv;
			// 25/02/14 manoharan if all details are dropped t6he bill_amt should be 0
			//if (chqAmt != 0)
			//{
				sql="UPDATE receipt SET bill_amt = ?, ADVADJ_AMT = ? WHERE  tran_id = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1,chqAmt );
				pstmt.setDouble(2,adjAmtAdv);
				pstmt.setString(3,tranId );
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			//}
			System.out.println("05/07/14 bill_amt ["+ chqAmt +"] billAmtDet [" + billAmtDet + "] advadj_amt [" + adjAmtAdv +"] oth_amt [" + othAmt + "] taxAmt [" + taxAmt + "] diffAmtExchDet [" + diffAmtExchDet + "] diffAmtExchAdv [" + diffAmtExchAdv + "] diffAmtExchEpc [" + diffAmtExchEpc + "]" );
			// 21/03/14 manoharan update advance 
			if (chqAmtActual != 0)
			{
				// 29/05/14 manoharan
				// 30/06/14 manoharan not to deduct dbamt
				//sql="select  chq_amt - ((case when bill_amt is null then 0 else bill_amt  end)+ (case when advadj_amt is null then 0 else advadj_amt end) - ?  +  (case when oth_amt is null then 0 else oth_amt end)  + (case when fin_chg is null then 0 else fin_chg end) + tax_amt)  from receipt WHERE  tran_id = ? " ;
				sql="select  chq_amt - ((case when bill_amt is null then 0 else bill_amt  end) +  (case when oth_amt is null then 0 else oth_amt end)  + (case when fin_chg is null then 0 else fin_chg end) + tax_amt)  from receipt WHERE  tran_id = ? " ;
				pstmt = conn.prepareStatement(sql);

				//pstmt.setDouble(1,bdAmt );
				pstmt.setString(1,tranId );
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					newAdvAmt = rs.getDouble(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if (newAdvAmt < 0 )
				{
					throw new Exception("Advance amount calculated is becoming negative [" + newAdvAmt + "]" );
				}
				// end 29/05/14 manoharan
			
			
				// 30/06/14 manoharan not to deduct dbamt
				//sql="UPDATE receipt SET adv_amt = chq_amt - ((case when bill_amt is null then 0 else bill_amt  end)+ (case when advadj_amt is null then 0 else advadj_amt end) - ?  +  (case when oth_amt is null then 0 else oth_amt end)  + (case when fin_chg is null then 0 else fin_chg end) + tax_amt) WHERE  tran_id 					= ? " ;
				sql="UPDATE receipt SET adv_amt = chq_amt - ((case when bill_amt is null then 0 else bill_amt  end) +  (case when oth_amt is null then 0 else oth_amt end)  + (case when fin_chg is null then 0 else fin_chg end) + tax_amt) WHERE  tran_id = ? " ;
				pstmt = conn.prepareStatement(sql);

				//pstmt.setDouble(1,bdAmt );
				pstmt.setString(1,tranId );
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;

				if( cnt > 0)
				{
					System.out.println("update successfully ["+cnt+"]");
				}
			}

			// -  (case when bill_disc_amt is null then 0 else bill_disc_amt end)  removed on 21/01/14
			//change done by kunal on 01/feb/14 minus bill_disc_amt
			sql="UPDATE receipt SET DIFF_AMT__EXCH = ? + ? + ?, net_amt = ((case when adv_amt is null then 0 else  adv_amt end)+  (case when bill_amt is null then 0 else bill_amt  end)+ (case when advadj_amt is null then 0 else advadj_amt end) - ?  +  (case when oth_amt is null then 0 else oth_amt end)  + (case when fin_chg is null then 0 else fin_chg end) + tax_amt) , "
			+" net_amt__bc  = ((case when adv_amt is null then 0 else  adv_amt end)+  (case when bill_amt is null then 0 else bill_amt  end)+ (case when advadj_amt is null then 0 else advadj_amt end)  -  ?  +  (case when oth_amt is null then 0 else oth_amt end)   + (case when fin_chg is null then 0 else fin_chg end) + tax_amt)  * exch_rate, "
			+" bill_disc_amt = ? WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);

			pstmt.setDouble(1,diffAmtExchDet );
			pstmt.setDouble(2,diffAmtExchAdv );
			pstmt.setDouble(3,diffAmtExchEpc );
			pstmt.setDouble(4,bdAmt );//added by kunal on 03/feb/14
			pstmt.setDouble(5,bdAmt ); //added by kunal on 03/feb/14
			pstmt.setDouble(6,bdAmt );
			pstmt.setString(7,tranId );
			cnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			if( cnt > 0)
			{
				System.out.println("update successfully ["+cnt+"]");
			}
		}
		catch(Exception e)
		{
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			isError = true;
			System.out.println("Exception :DcdetPostSaveEJB : :==>\n"+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("isError ["+isError+"]");
				if( conn != null )
				{

					/*if( isError )
					{
						conn.rollback();
					}
					else
					{
						conn.commit();
					}*/
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch(Exception e)
			{
				System.out.println("Exception :SavexPreSaveEJB : :==>\n"+e.getMessage());
				try
				{
					System.out.println("Before rollback");
					conn.rollback();
				}

				catch(SQLException sqle)
				{
					System.out.println(sqle);
				}
				throw new ITMException(e);
			}
		}
		System.out.println("Out DcdetPostSaveEJB ... ");
		return "";                
			}
	private java.math.BigDecimal round(int scale ,double round)
	{
		return new java.math.BigDecimal(round).setScale(scale, java.math.BigDecimal.ROUND_UP);
	}

	private double roundVal(double round,int scale)
	{
		return Math.round(round*Math.pow(10, scale))/Math.pow(10, scale);
	}
}