/********************************************************
	Title 	 : UndoRecievableAdjConf[DI3FSUN015]
	Date  	 : 03/02/15
	Developer: Priyanka

********************************************************/



package ibase.webitm.ejb.fin.adv;

import ibase.system.config.ConnDriver;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
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.Timestamp;

import ibase.utility.E12GenericUtility;
import javax.ejb.Stateless;

@Stateless
public class UndoRecievableAdjConf extends ActionHandlerEJB implements UndoRecievableAdjConfLocal,UndoRecievableAdjConfRemote 
{
	
	public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException, ITMException
	{
		
		E12GenericUtility e12GenericUtility = new E12GenericUtility();
		
		String sql = "",sql1="";
		String confirmed="";
		ResultSet rs = null,rs1=null;
		PreparedStatement pstmt = null,pstmt1=null;
		GenericUtility genericUtility = null;
	    Connection conn = null;
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		
		//ConnDriver connDriver = new ConnDriver();
		String errString = null;
		int cnt=0,updCnt=0,updCnt1=0,updCnt2=0,undoLineNo=1;
		String refNo="",refSer="",refNoHeader="",tranType="";
		double adjAmt=0,totalAmt=0,sumAdjAmt=0;
		String loginEmpCode="";
		Timestamp sysDate = null;
		try
		{
			genericUtility = GenericUtility.getInstance();
			System.out.println("In UndoRecievableAdjConf Confirmation Process >>>>>>>>>>>>>> ");
			
			String userId="";
					
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			
			userId = e12GenericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			System.out.println("--login code--"+userId);
			
		   System.out.println("Getting Tran_ID=============="+tranId);
		   if(tranId!=null && tranId.trim().length()>0)
		   {
			   System.out.println("@@@@@tranId"+tranId+"]");
			  
			   sql = "	select confirmed from  undo_rec_adj where tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				if (rs.next()) 
				{

					confirmed = rs.getString("confirmed");

				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if ("Y".equalsIgnoreCase(confirmed)) 
				{	
					System.out.println("transaction already confirmed");				
					errString = itmDBAccessLocal.getErrorString("","VTCONF8","");
				
				}
				else
				{
				
						System.out.println("transaction not confirmed");
						loginEmpCode =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");							
						Calendar currentDate = Calendar.getInstance();
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						String sysDateStr = sdf.format(currentDate.getTime());
						System.out.println("Now the date is :=>  " + sysDateStr);
						sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

						System.out.println(">>>>>>>>sysDate:"+sysDate);
						System.out.println(">>>>>>>>loginEmpCode:"+loginEmpCode);
												
						sql="select ref_no,tran_type from undo_rec_adj where tran_id= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							refNoHeader = rs.getString("ref_no");
							tranType= rs.getString("tran_type");
						}						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@@@ refNo from undo_rec_adj " + refNo);
						System.out.println("@@@@@@ tranType from undo_rec_adj " + tranType);
						//added
										
						sql="select count(1) from receivables_adj where  ref_no_adj= ? and ref_ser_adj= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, refNoHeader);
						pstmt.setString(2, tranType);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							cnt=rs.getInt(1);
						}						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(cnt==0)
						{
							System.out.println("Record not exist in receivables_adj>>>>>>>>>:"+cnt);
							errString = itmDBAccessLocal.getErrorString("","VTREF03","");
							
						}
						else
						{
							
						System.out.println("Record exist in receivables_adj>>>>>>>>> ");
						sql="select ref_ser,ref_no,adj_amt from receivables_adj where  ref_no_adj= ? and ref_ser_adj= ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, refNoHeader);
						pstmt.setString(2, tranType);
						rs = pstmt.executeQuery();
						
						while (rs.next()) 
						{
							System.out.println("@@@@@@ inside while ");
							
							refSer = rs.getString("ref_ser");
							refNo = rs.getString("ref_no");
							adjAmt = rs.getDouble("adj_amt");
														
							System.out.println("refNo==========="+refNo);
							System.out.println("refSer==========="+refSer);
							System.out.println("adjAmt==========="+adjAmt);
							 
							sql1="insert into undo_rec_adjdet(tran_id,line_no,ref_no,ref_ser,adj_amt) values(?,?,?,?,?)";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1,tranId);
							pstmt1.setInt(2,undoLineNo);
							pstmt1.setString(3,refNo);				
							pstmt1.setString(4,refSer);
							pstmt1.setDouble(5,adjAmt);																		
							pstmt1.executeUpdate();									
							pstmt1.close();
							pstmt1 = null;	
							
							sumAdjAmt=sumAdjAmt+adjAmt;
							System.out.println("SumADJAMOUNT============"+sumAdjAmt);
							
						
							sql1=" update receivables set adj_amt =adj_amt-? where ref_no= ? and tran_ser=?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setDouble(1, adjAmt);
							pstmt1.setString(2, refNo);
							pstmt1.setString(3, refSer);
							
							updCnt = pstmt1.executeUpdate();
							pstmt1.close();
							pstmt1 = null;
							System.out.println("@@@updCnt========["+updCnt+"]");
											
							
														
							sql1="delete from receivables_adj where ref_no= ? and  ref_ser_adj= ?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, refNo);
							pstmt1.setString(2, tranType);
							pstmt1.executeUpdate();
							System.out.println("Data deleted sucessfully");
							undoLineNo++;	
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
				
								
						System.out.println("SumADJAMOUNT11111111111============"+sumAdjAmt);
						
						
						if(sumAdjAmt>0)
						{
							sql="update receivables_adj set adj_amt= adj_amt - ? where ref_no= ? and ref_ser= ? ";
							pstmt = conn.prepareStatement(sql);							
							pstmt.setDouble(1, sumAdjAmt);
							pstmt.setString(2, refNoHeader);
							pstmt.setString(3, tranType);
												
							updCnt1 = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("@@@updCnt=receivables_adj=========["+updCnt1+"]");
							
							
							sql1=" update receivables set adj_amt =adj_amt-? where ref_no= ? and tran_ser=?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setDouble(1, sumAdjAmt);
							pstmt1.setString(2, refNoHeader);
							pstmt1.setString(3, tranType);
							
							updCnt2 = pstmt1.executeUpdate();
							pstmt1.close();
							pstmt1 = null;
							System.out.println("@@@updCnt for receivables========["+updCnt2+"]");
							
							sql1="update INVOICE set adj_amount= adj_amount- ? WHERE invoice_id= ?";
							pstmt = conn.prepareStatement(sql1);							
							pstmt.setDouble(1, sumAdjAmt);
							pstmt.setString(2, refNoHeader);
												
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("@@@updCnt ===INVOICE===["+updCnt+"]");
							
							
						}
						else
						{
							sql="update receivables_adj set adj_amt= adj_amt + ? where ref_no= ? and ref_ser= ? ";
							pstmt = conn.prepareStatement(sql);							
							pstmt.setDouble(1, sumAdjAmt);
							pstmt.setString(2, refNoHeader);
							pstmt.setString(3, tranType);
												
							updCnt1 = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("@@@updCnt== receivables_adj========["+updCnt1+"]");
							
							
							sql1=" update receivables set adj_amt =adj_amt + ? where ref_no= ? and tran_ser=?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setDouble(1, sumAdjAmt);
							pstmt1.setString(2, refNoHeader);
							pstmt1.setString(3, tranType);
							
							updCnt2 = pstmt1.executeUpdate();
							pstmt1.close();
							pstmt1 = null;
							System.out.println("@@@updCnt for receivables========["+updCnt2+"]");
							
							sql1="update INVOICE set adj_amount= adj_amount + ? WHERE invoice_id= ?";
							pstmt = conn.prepareStatement(sql1);							
							pstmt.setDouble(1, sumAdjAmt);
							pstmt.setString(2, refNoHeader);
											
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt= null;
							System.out.println("@@@updCnt=== INVOICE ["+updCnt+"]");
						}
					
						  if (updCnt>0 && updCnt1>0 && updCnt2>0) 
							{	
						
						        sql = " update undo_rec_adj set confirmed = 'Y',conf_date = ?, emp_code__aprv = ? where tran_id = ? ";
						        pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1, sysDate);
								pstmt.setString(2, loginEmpCode);
								pstmt.setString(3, tranId);
								cnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println(">>>>>>>>>>>>>>>>>cnt:"+cnt);
								if (cnt>0) 
								{	

									errString = itmDBAccessLocal.getErrorString("","VTCNFSUCC","");

								}
								else
								{	
									errString = itmDBAccessLocal.getErrorString("","VTUNCNFIRM","");
								}
		                      
							}
							else
							{	
								System.out.println("not updated >>>>>>>>updCnt1>>>>>>>>>>>>:"+updCnt1);
								errString = itmDBAccessLocal.getErrorString("","VTUNCNFIRM","");

							}
						
				}
		  
				}
				}
		
			
		}
		catch( Exception e )
		{	
			System.out.println("UndoRecievableAdjConf..."+e.getMessage());
		    e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
			   System.out.println("UndoRecievableAdjConf..."+e1.getMessage());
			   e1.printStackTrace();
			}
					
		}
		finally
		{
			
			try
			{
			
				if (errString != null && errString.trim().length() > 0) 
				{
					
					System.out.println("--going to commit tranaction--");
						if (errString.indexOf("VTCNFSUCC") > -1)
	
						{
							conn.commit();
							System.out.println("--transaction commited--");
						} 
						else 
						{
							conn.rollback();
							System.out.println("--transaction rollback--");
						}
				}
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			conn.close();
			}
			catch(Exception e)
			{
				System.out.println("Exception : "+e);
			e.printStackTrace();}
			
		}
		
		System.out.println("");
		return errString ;
	}
	

	
}
