package ibase.webitm.ejb.fin;
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.util.ArrayList;
import java.util.Iterator;

import org.w3c.dom.Document;
@javax.ejb.Stateless

public class MiscVoucherPostSave extends ValidatorEJB implements MiscVoucherPostSaveLocal, MiscVoucherPostSaveRemote
{        

	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 VoucherPostSave ... ");

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

		String tranId = "";
		String sql = null,sql1=null;

		double diffAmtExch = 0, adjAmt = 0, netAmt = 0, netAmtBc = 0, vouchAmt = 0, billAmt = 0, taxAmt = 0, totAmt = 0;
		double diffAmtExchLine=0.0;
		double netAmtRcp = 0, advAmt = 0, netAmtDet = 0, netAmtBcRcp = 0, taxAmtDet = 0, totAmtDet = 0, netAmtBcDet = 0 ;
		double advAmtAdj = 0, taxedAdjAmt = 0, advAmtDet = 0, taxAmtAdv = 0;
		String hdrVoucherType="",exchRate="";
				 
		int cnt=0,cnt1=0,listCnt=1;
		try
		{

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

			tranId = GenericUtility.getInstance().getColumnValue("tran_id",dom);
			// Advance Screen Cross Update
			sql="select exch_rate from misc_voucher where tran_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if(rs.next()){
				exchRate=rs.getString("exch_rate");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			//To update diff_amt__exch for every line_no
			sql="update misc_vouchadv set diff_amt__exch=((coalesce(adj_amt,0) * coalesce(exch_rate__vouch,0))-(coalesce(adj_amt,0)*?))"
					+ " where tran_id= ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,exchRate);
			pstmt.setString(2,tranId);
			cnt1 = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
			
			/**Commented by Vallabh Kadam 28/NOV/14*/
//			sql="select coalesce(((mvadv.adj_amt * mvadv.exch_rate__vouch) - (mvadv.adj_amt * mv.exch_rate)),0) " +
//				" from misc_vouchadv mvadv join misc_voucher mv on mvadv.tran_id = mv.tran_id WHERE  mvadv.tran_id = ? " ;
			/**Commented by Vallabh Kadam 28/NOV/14 END*/
			
			//SUM of all diff_amt__exch to update in header
			sql="select coalesce(sum(diff_amt__exch),0) from misc_vouchadv where tran_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				diffAmtExch = rs.getDouble(1); 
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			sql="select coalesce(sum(adj_amt),0),  coalesce(sum(taxed_adj_amt),0), coalesce(sum(tax_amt),0) from misc_vouchadv WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				advAmtAdj = rs.getDouble(1);
				taxedAdjAmt = rs.getDouble(2);
				taxAmtAdv = rs.getDouble(3);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			sql="select coalesce((adv_amt__adj + adv_amt__det),0), coalesce((coalesce((adv_amt__adj + adv_amt__det),0) + tot_amt),0), coalesce((coalesce((adv_amt__adj + adv_amt__det),0) + tot_amt),0) + coalesce(exch_rate,0) from misc_voucher WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				advAmt = rs.getDouble(1);
				netAmt = rs.getDouble(2);
				netAmtBc = rs.getDouble(3);
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

				/**Commented on 28/NOV/14 Vallabh Kadam */
//			sql="UPDATE misc_vouchadv set diff_amt__exch = ? "
//			+" WHERE  tran_id = ? ";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setDouble(1,diffAmtExch );
//			pstmt.setString(2,tranId );
//			cnt = pstmt.executeUpdate();
//			pstmt.close();
//			pstmt = null;

//			if( cnt > 0)
//			{
//				System.out.println("update of advance Screen success ["+cnt+"]");
//			}
			/**Commented on 28/NOV/14 Vallabh Kadam END */
			
			sql="UPDATE misc_voucher set adv_amt__adj = ?, adv_amt = ?, taxed_adj_amt = ?, diff_amt__exch = ?, net_amt = ?, net_amt__bc = ? "
				+" WHERE  tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1,advAmtAdj );
				pstmt.setDouble(2,advAmt );
				pstmt.setDouble(3,taxedAdjAmt );
				pstmt.setDouble(4,diffAmtExch );
				pstmt.setDouble(5,netAmt );
				pstmt.setDouble(6,netAmtBc );
				pstmt.setString(7,tranId );
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;

				if( cnt > 0)
				{
					System.out.println("update of advance Screen in misc_voucher success ["+cnt+"]");
				}
			// End Of advance Screen cross Update
			
			//Detail Screen Cross Update
			sql="select case when mv.vouch_type = 'A' "
					+ "Then coalesce(sum(mvdet.amount),0) "
					+ "ELSE 0 END, "
					+ "case when mv.vouch_type != 'A' "
					+ "Then coalesce(sum(mvdet.amount),0) "
					+ "ELSE 0 END,"
					+ " coalesce(sum(mvdet.tax_amt),0) "
					+ "from misc_vouchdet mvdet join misc_voucher mv "
					+ "on mvdet.tran_id = mv.tran_id "
					+ "WHERE  mvdet.tran_id = ? group by mv.vouch_type" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				advAmtDet = rs.getDouble(1);
				billAmt = rs.getDouble(2);
				taxAmt = rs.getDouble(3);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			

			
			sql="select coalesce(adv_amt__adj + adv_amt__det,0) from misc_voucher WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				advAmt = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			totAmt = billAmt + (taxAmt + taxAmtAdv);
			netAmt = advAmt + totAmt;
			
			
			sql="select coalesce(? * exch_rate,0) from misc_voucher WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,netAmt );
			pstmt.setString(2,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				netAmtBc = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("Taxy Amt = "+ taxAmt + " totAmt = "+ totAmt);
						
			sql="UPDATE misc_voucher set adv_amt__det = ?, bill_amt = ?, adv_amt = ?, tax_amt = ?, tot_amt = ?, net_amt = ?, net_amt__bc = ?  WHERE  tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1,advAmtDet );
				pstmt.setDouble(2,billAmt);
				pstmt.setDouble(3,advAmt);
				pstmt.setDouble(4,(taxAmt + taxAmtAdv));
				pstmt.setDouble(5,totAmt);
				pstmt.setDouble(6,netAmt);
				pstmt.setDouble(7,netAmtBc);
				pstmt.setString(8,tranId );
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;

				if( cnt > 0)
				{
					System.out.println("update of Detail Screen success ["+cnt+"]");
				}
				
			//End Of Detail Screen Cross Update 
					
		}
		catch(Exception e)
		{
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			isError = true;
			System.out.println("Exception :MiscVoucherPostSaveEJB : :==>\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 :MiscVoucherPostSaveEJB : :==>\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 MiscVoucherPostSaveEJB ... ");
		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);
	}*/
}