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);
			//for(int i=0;i<dom.getElementsByTagName("Detail2").getLength() ; i++)
			//{
				//lineNo= GenericUtility.getInstance().getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail2").item(i));
				//rcpAmt = Double.parseDouble(GenericUtility.getInstance().getColumnValueFromNode("rcp_amt",dom.getElementsByTagName("Detail2").item(i)));
				//finChg = Double.parseDouble(GenericUtility.getInstance().getColumnValueFromNode("fin_chg",dom.getElementsByTagName("Detail2").item(i)));
				

				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 adv_amt, exch_rate,chq_amt 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);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = 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 = ? WHERE  tran_id = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,chqAmt );
					pstmt.setString(2,tranId );
					cnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				//}

				// 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 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 = ? " ;
					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");
					}
					// 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 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 = ? " ;
					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);
	}
}