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 org.w3c.dom.Document;
@javax.ejb.Stateless

public class VoucherPostSave extends ValidatorEJB implements VoucherPostSaveLocal, VoucherPostSaveRemote
{        

	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;
		PreparedStatement pstmt2 = null;
		Document dom = null;                
		ResultSet rs =null;
		boolean isError = false;

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

		double diffAmtExch = 0, adjAmt = 0, netAmt = 0, netAmtBc = 0, vouchAmt = 0, billAmt = 0, taxAmt = 0, totAmt = 0;
		double netAmtRcp = 0, advAmt = 0, netAmtDet = 0, netAmtBcRcp = 0, taxAmtDet = 0, totAmtDet = 0, netAmtBcDet = 0 ;
		double vouchAvdTaxAmt=0;
				 
		int cnt=0;
		try
		{

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

			tranId = GenericUtility.getInstance().getColumnValue("tran_id",dom);
			// Advance Screen Cross Update
			sql="select case when sum(case when adj_amt is null then 0 else adj_amt end) is null then 0 else sum(case when adj_amt is null then 0 else adj_amt end) end,case when sum(case when diff_amt__exch is null then 0 else diff_amt__exch end) is null then 0 else sum(case when diff_amt__exch is null then 0 else diff_amt__exch end) end,case when sum(case when tax_amt is null then 0 else tax_amt end) is null then 0 else sum(case when tax_amt is null then 0 else tax_amt end) end from vouchadv WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				adjAmt = rs.getDouble(1);
				diffAmtExch = rs.getDouble(2);
				vouchAvdTaxAmt=rs.getDouble(3);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			sql="select coalesce(sum(tot_amt),0) + coalesce(sum(adv_amt),0), coalesce(sum(net_amt),0) * coalesce(sum(exch_rate),0) from voucher WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				netAmt = rs.getDouble(1);
				netAmtBc = rs.getDouble(2);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("In netAmtBc =  "+netAmtBc);
				
			sql="UPDATE voucher set adv_amt = ?, diff_amt__exch = ?, net_amt = ? , net_amt__bc = ? WHERE  tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,adjAmt );
			pstmt.setDouble(2,diffAmtExch );
			pstmt.setDouble(3,netAmt );
			pstmt.setDouble(4,netAmtBc );
			pstmt.setString(5,tranId );
			cnt = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			if( cnt > 0)
			{
				System.out.println("update of advance Screen success ["+cnt+"]");
			}
			// End Of advance Screen cross Update
			
			//Receipt Screen Cross Update
			sql="select line_no, coalesce(vouch_qty,0) * coalesce(vouch_rate,0), coalesce(((coalesce(vouch_qty,0) * coalesce(vouch_rate,0)) + tax_amt + disc_amt),0) from vouchrcp WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			//pstmt.setDouble(1,vouchAmt);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sql2="UPDATE vouchrcp set vouch_amt = ?, net_amt = ? WHERE  tran_id = ? and line_no = ?";
				pstmt2 = conn.prepareStatement(sql2);
				pstmt2.setDouble(1,rs.getDouble(2));
				pstmt2.setDouble(2,rs.getDouble(3));
				pstmt2.setString(3,tranId);
				pstmt2.setLong(4,rs.getLong(1));
				pstmt2.executeUpdate();
				
				pstmt2.close();
				pstmt2 = null;
				
				
/*				System.out.println("vouchAmt Inner ====>>>>> "+vouchAmt);
				System.out.println("netAmtRcp Inner ====>>>>> "+vouchAmt);*/
				vouchAmt = vouchAmt + rs.getDouble(2);
				netAmtRcp = netAmtRcp + rs.getDouble(3);
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			/*System.out.println("vouchAmt ====>>>>> "+vouchAmt);
			System.out.println("netAmtRcp ====>>>>> "+netAmtRcp);*/
			
			sql="select ? - coalesce(sum(disc_amt),0), coalesce(sum(tax_amt),0) from vouchrcp WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,vouchAmt );
			pstmt.setString(2,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				billAmt = rs.getDouble(1);
				taxAmt = rs.getDouble(2);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			totAmt = billAmt + taxAmt;
			
			/*System.out.println("billAmt Receipt ====>>>>> "+billAmt);
			System.out.println("taxAmt Receipt ====>>>>> "+taxAmt);
			System.out.println("totAmt Receipt ====>>>>> "+totAmt);*/
			
			sql="select ? * coalesce(sum(exch_rate),0) from voucher WHERE  tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1,netAmtRcp );
			pstmt.setString(2,tranId );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				netAmtBcRcp = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
/*			sql="UPDATE vouchrcp set vouch_amt = ?, net_amt = ?  WHERE  tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1,vouchAmt );
				pstmt.setDouble(2,netAmtRcp);
				pstmt.setString(3,tranId );
				cnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;

				if( cnt > 0)
				{
					System.out.println("update of Receipt Screen success ["+cnt+"]");
				}*/
				
				sql="UPDATE voucher set bill_amt = ?, tax_amt = ?, tot_amt = ? , net_amt = ?, net_amt__bc = ? "
					+" WHERE  tran_id = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,billAmt );
					pstmt.setDouble(2,taxAmt );
					pstmt.setDouble(3,totAmt );
					pstmt.setDouble(4,netAmtRcp );
					pstmt.setDouble(5,netAmtBcRcp );
					pstmt.setString(6,tranId );
					cnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

					if( cnt > 0)
					{
						System.out.println("update of Vouche Screen from Receipt success ["+cnt+"]");
					}	
			
			//End Of Receipt Screen Cross Update 
					
					
			// Cross Update of Detail Screen
					
				sql="select coalesce(sum(amount),0),  coalesce(sum(tax_amt),0) from vouchdet WHERE  tran_id = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranId);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						advAmt = rs.getDouble(1);
						taxAmtDet = rs.getDouble(2);
						taxAmtDet = taxAmtDet + taxAmt;
						advAmt = advAmt + adjAmt;
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				
					//System.out.println("taxAmtDet ====>>>>> "+taxAmtDet);
					//System.out.println("advAmt ====>>>>> "+advAmt);
					
				sql="select coalesce((bill_amt + ?),0),  coalesce((coalesce((bill_amt + ?),0) + ?),0), coalesce((coalesce((bill_amt + ?),0) + ?),0) * coalesce(exch_rate,0) from voucher WHERE  tran_id = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,taxAmtDet);
					pstmt.setDouble(2,taxAmtDet);
					pstmt.setDouble(3,advAmt);
					pstmt.setDouble(4,taxAmtDet);
					pstmt.setDouble(5,advAmt);
					pstmt.setString(6,tranId);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						totAmtDet = rs.getDouble(1);
						netAmtDet = rs.getDouble(2);
						netAmtBcDet = rs.getDouble(3);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					
				sql="UPDATE voucher set adv_amt = ?, tax_amt = ?, tot_amt = ?, net_amt = ?, net_amt__bc = ?  WHERE  tran_id = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,advAmt );
					pstmt.setDouble(2,taxAmtDet+vouchAvdTaxAmt);
					pstmt.setDouble(3,totAmtDet+vouchAvdTaxAmt);
					pstmt.setDouble(4,netAmtDet+vouchAvdTaxAmt);
					pstmt.setDouble(5,netAmtBcDet+vouchAvdTaxAmt);
					pstmt.setString(6,tranId);
					cnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

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