/********************************************************
	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 
{
	String status="";
	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,upCnt=0,upCount=0;
		String refNo="",refSer="",refNoHeader="",tranType="";
		double adjAmt=0,totalAmt=0,sumAdjAmt=0,adjAmt1=0,totalAmt1=0,adjAmt2=0,totalAmt2=0;
		String loginEmpCode="",statusBef="";
		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+"]");
							
							
							System.out.println("Status Checking*********");
							sql1="select tot_amt,adj_amt,status from receivables  where ref_no= ? and tran_ser=?";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, refNo);
							pstmt1.setString(2, refSer);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								totalAmt1=Math.abs(rs1.getDouble("tot_amt"));
								adjAmt1=Math.abs(rs1.getDouble("adj_amt"));
								statusBef=rs1.getString("status");
							}						
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
							System.out.println("totalAmt1============"+totalAmt1);
							System.out.println("adjAmt1============"+adjAmt1);
							System.out.println("statusBef============"+statusBef);
							
							if(adjAmt1==0)
							{
								status="U";
								System.out.println("adjAmt1==0*********");
								/*//sql1=" update receivables set status ='U' where ref_no= ? and tran_ser=?";
								pstmt1 = conn.prepareStatement(sql1);						
								pstmt1.setString(1, refNo);
								pstmt1.setString(2, refSer);
								updCnt = pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1= null;*/
							}
							else if(adjAmt1==totalAmt1)
							{
								System.out.println("adjAmt1==totalAmt1*********");
								status="A";
								/*sql1=" update receivables set status ='A' where ref_no= ? and tran_ser=?";
								pstmt1 = conn.prepareStatement(sql1);						
								pstmt1.setString(1, refNo);
								pstmt1.setString(2, refSer);
								updCnt = pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1= null;*/
							}
							else if(adjAmt1!=0 && totalAmt1>adjAmt1)
							{
								System.out.println("totalAmt1>adjAmt1*********");
								status="P";
								/*sql1=" update receivables set status ='P' where ref_no= ? and tran_ser=?";
								pstmt1 = conn.prepareStatement(sql1);						
								pstmt1.setString(1, refNo);
								pstmt1.setString(2, refSer);
								updCnt = pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1= null;*/
							}
							
							System.out.println("status=****-========="+status);
							System.out.println("refNo=-========="+refNo);
							System.out.println("refSer=-========="+refSer);
							sql1=" update receivables set status = ? where ref_no= ? and tran_ser=?";
							pstmt1 = conn.prepareStatement(sql1);	
							pstmt1.setString(1, status);
							pstmt1.setString(2, refNo);
							pstmt1.setString(3, refSer);
							upCount = pstmt1.executeUpdate();
							pstmt1.close();
							pstmt1= null;
							
							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+"]");
						}
					
						System.out.println("refNoHeader@@@@@@@@============"+refNoHeader);
						System.out.println("tranType@@@@@@@@@@@@============"+tranType);
						sql1="select tot_amt,adj_amt,status from receivables  where ref_no= ? and tran_ser=?";
						pstmt= conn.prepareStatement(sql1);
						pstmt.setString(1, refNoHeader);
						pstmt.setString(2, tranType);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totalAmt2=Math.abs(rs.getDouble("tot_amt"));
							adjAmt2=Math.abs(rs.getDouble("adj_amt"));
							statusBef=rs.getString("status");
						}						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						System.out.println("totalAmt1============"+totalAmt2);
						System.out.println("adjAmt1============"+adjAmt2);
						System.out.println("statusBef============"+statusBef);
	
						if(adjAmt2==0)
						{
							status="U";
							System.out.println("adjAmt2=0*********");
							
						}
						else if(adjAmt2==totalAmt2)
						{
							System.out.println("adjAmt2==totalAmt2********");
							status="A";						
						}
						else if(adjAmt2!=0 && totalAmt2>adjAmt2)
						{
							System.out.println("adjAmt2!=0 && totalAmt2>adjAmt2*********");
							status="P";
						
						}
						
						System.out.println("status=****-========="+status);
						System.out.println("refNoHeader=-========="+refNoHeader);
						System.out.println("tranType=-========="+tranType);
						sql1=" update receivables set status = ? where ref_no= ? and tran_ser=?";
						pstmt1 = conn.prepareStatement(sql1);	
						pstmt1.setString(1, status);
						pstmt1.setString(2, refNoHeader);
						pstmt1.setString(3, tranType);
						upCnt = pstmt1.executeUpdate();
						pstmt1.close();
						pstmt1= null;
																
						
					  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 ;
	}
	
	


	
}
