package ibase.webitm.ejb.dis;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.fin.MiscDrCrRcpConf;
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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.ejb.Stateless;
import javax.naming.InitialContext;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;

import javax.xml.rpc.ParameterMode;


@Stateless
public class ProofOfDeliveryConf extends ActionHandlerEJB implements ProofOfDeliveryConfLocal,ProofOfDeliveryConfRemote
{	
	String debitNoteTranID="";
	boolean isRegPriceNotFound=false;	
	boolean isError = false;
	public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		System.out.println("--------------confirm method of ProofOfDeliveryConf ------------- : ");
		String retString = "";		
		try
		{
						
				retString = confirmPOD(tranId,xtraParams,forcedFlag);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception :ProofOfDeliveryConf :confirm():" + e.getMessage() + ":");
			retString = GenericUtility.getInstance().createErrorString(e);
			e.printStackTrace();
		}
		
		return retString;
	}
	
	private String confirmPOD(String tranId,String xtraParams,String forcedFlag) 
	{
		System.out.println("---------Class : ProofOfDeliveryConf-->> confirm method called-----------");
		isError=false;
		tranId=tranId==null ? "" :tranId.trim();
		ResultSet rs=null;
		Connection conn=null;
		ConnDriver ConnDriver = new ConnDriver();
		PreparedStatement pstmt=null;
		String errString="",sql="",loginSite="",wfStatus="",itemCode="",regulatedPrice="",itemActive="";
		String itmCodeDt2="",lotNo="",lotSl="", detail2xmlString="";
		double aprvRate=0.0,PODrate=0.0,PODqty=0.0,allowedAmt=0.0,stockistAmt=0.0,debitNoteAmtDetail=0.0;
		double debitNoteAmtHdr=0;
		int updCnt=0,lineNo=0,lineNotrace=0;
		ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		HashMap<Integer,Double> mapDebitNoteAmt=new HashMap<Integer,Double>();
		try
		{		
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");			
			conn.setAutoCommit(false);
			String confirmed="";
			sql = "select confirmed,wf_status from spl_sales_por_hdr WHERE tran_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed =rs.getString(1)==null ? "N":rs.getString(1);
				wfStatus=rs.getString(2)==null ? "O" : rs.getString(2);
			}	
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( wfStatus != null && wfStatus.equalsIgnoreCase("O") )
			{
				//Opened transaction can not confirm
				errString = itmdbAccess.getErrorString("", "VTOPCCONF", "", "", conn);
				return errString;
			}	
			if( wfStatus != null && wfStatus.equalsIgnoreCase("R") )
			{
				//Rejected transaction can not confirm
				errString = itmdbAccess.getErrorString("", "VTRJCCONF", "", "", conn);
				return errString;
			}	
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				//if already confirm then show error
				errString = itmdbAccess.getErrorString("", "VTALCONF", "", "", conn);
				return errString;
			}	
			System.out.println("-------wfStatus before confirm -------"+wfStatus);
			//Only submitted transaction allowed to confirm
			if(wfStatus.trim().equalsIgnoreCase("S"))
			{
				String invoiceId="",custCode="",siteCode="",currCode="",loginCode="";
				double ExchRate=0.0,actualDBamount=0.0,rateInv=0,qtyInv=0,detail3Amt=0,detail3AmtT=0;
				loginCode=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
				System.out.println("Login Code ------->> "+loginCode);			
							
					sql="";
					sql="select invoice_id,cust_code,site_code,curr_code,exch_rate from spl_sales_por_hdr where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						invoiceId=rs.getString(1);
						custCode=rs.getString(2)==null ? "" : rs.getString(2);
						siteCode=rs.getString(3)==null ? "" : rs.getString(3);
						currCode=rs.getString(4)==null ? "" : rs.getString(4);
						ExchRate=rs.getDouble(5);
					}
					if(rs != null)
					{
						rs.close();
						rs=null;						
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;						
					}
					
					sql="";
					sql="select item_code,rate__resale,quantity__resale,aprv_rate,line_no,lot_no,lot_sl,rate__inv,quantity__inv,line_no__trace " +
							"from spl_sales_por_det where tran_id = ? order by line_no";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						itemCode=rs.getString(1);						
						PODrate=rs.getDouble(2);
						PODqty=rs.getDouble(3);
						aprvRate=rs.getDouble(4);
						lineNo=rs.getInt(5);
						lotNo=rs.getString(6);
						lotSl=rs.getString(7);
						rateInv=rs.getDouble(8);
						qtyInv=rs.getDouble(9);
						lineNotrace=rs.getInt(10);
						
						itemCode=itemCode==null ? "" :itemCode.trim();
						lotNo=lotNo==null ? "" :lotNo.trim();
						lotSl=lotSl==null ? "" :lotSl.trim();		
						
						
						//debitNoteAmtDetail=getDebitNoteAmount(conn, tranId, lineNo,aprvRate,PODrate,PODqty);
						debitNoteAmtDetail=getDebitNoteAmount(rateInv,qtyInv);						
						debitNoteAmtHdr=debitNoteAmtHdr+debitNoteAmtDetail;
						detail3Amt=(PODrate - aprvRate) * PODqty;
						detail3AmtT=detail3AmtT + detail3Amt;
						mapDebitNoteAmt.put(lineNo, detail3Amt);
						//detail2xmlString=getDetailsXmlString(conn, xtraParams, invoiceId, aprvRate, debitNoteAmtDetail);										
						detail2xmlString=detail2xmlString + getDetailsXmlString(invoiceId,lineNotrace,lineNo,rateInv,qtyInv,lotNo,lotSl,itemCode, debitNoteAmtDetail);
						
					}	//end while-------------------@@@@@@@@@@---------------------------------------
					System.out.println("detail2xmlString3813---->>[["+detail2xmlString+"]]");
					System.out.println("Final debitNoteAmtHdr---------->> : "+debitNoteAmtHdr);
					System.out.println("Final detail3AmtT in debit note table---------->> : "+detail3AmtT);
					//debitNoteAmtDetail=100.10;					
						System.out.println("-----Debit note generation condition----------"+actualDBamount);
						errString=genDebitNote(tranId,invoiceId,custCode,siteCode,xtraParams,actualDBamount,currCode,ExchRate,detail2xmlString,debitNoteAmtHdr,detail3AmtT,conn);
						System.out.println("------Error string after DN save--->>["+errString+"]");
						if (errString.indexOf("Success") > -1)
						{	
							
							int cnt=updateDrCrRcpHdrAmt(debitNoteTranID,detail3AmtT,conn);
							if(cnt>0)					
							 conn.commit();
						System.out.println("Confirmation of Debit note1212112 COMMITED----------");
						//MiscDrCrRcpConf confDebitNote=new MiscDrCrRcpConf();
						//errString=confDebitNote.confirm(debitNoteTranID,xtraParams,"",conn);
						errString = confirmDebitNote("drcrrcp_dr",debitNoteTranID,xtraParams,"",conn);
						System.out.println("Error String on DB Confirmation21212112 : "+errString);
						if((errString != null ) &&  errString.indexOf("VTSUCC1") > -1)
						{
							updCnt=updatePodHeader(conn,tranId,loginCode);
							System.out.println("return count from updateDebitNoteAmounts--->["+updCnt+"]");
							if(updCnt > 0){
							conn.commit();
							errString = itmdbAccess.getErrorString("", "VTCONSUCF", "", "", conn);	
							System.out.println("------@@@Debit Note Generated successfully for amount : "+actualDBamount);
							System.out.println("Confirmation of Debit note done successfully----------");
							}else{
								errString = itmdbAccess.getErrorString("", "VTNCONFT", "", "", conn);
								conn.rollback();
								System.out.println("transaction not confirmed----------");
							}
						}
						else{
							errString = itmdbAccess.getErrorString("", "VTNCONFT", "", "", conn);
							conn.rollback();
						}
						}
						else{
							//errString = itmdbAccess.getErrorString("", "VTNCONFT", "", "", conn);
							conn.rollback();
							return errString;
							
						}
			
			   
			} // end condition for wfstatus ='S'
			else
			{	
				conn.rollback();
				errString = itmdbAccess.getErrorString("", "VTNSUBCON", "", "", conn);
			}
			
		} // end try
		catch(SQLException se)
		{
			System.out.println("SQLException : class ProofOfDeliveryConf : ");
			se.printStackTrace();
			try
			{
				isError=true;
			  conn.rollback();
			}
			catch(Exception e){
				System.out.println("Exception : Occure during rollback........");e.printStackTrace();
				}
		}
		catch(Exception e)
		{
			try
			{
				isError=true;
				conn.rollback();
			}
			catch (Exception e1)
			{
				e.printStackTrace();
				System.out.println("Exception Class [ProofOfDeliveryConf]::"+e.getMessage());
			}
			
			
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}	
				if (conn!=null)
				{
					conn.close();
					conn=null;
				}
				if(isError)
				{
					errString = itmdbAccess.getErrorString("", "VTNCONFT", "", "", conn);
				}
							
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
				
			}
		}
		System.out.println("[ProofOfDeliveryConf]errstring :"+errString);
		return errString;
	}
	
	private String genDebitNote(String tranId,String invoiceId,String custCode,String siteCode ,String xtraParams,double relAmt,String currencyCode,double exchRate,String detail2xmlString,double debitNoteAmtHdr,double detail3AmtT, Connection conn) throws ITMException
	{
	    	System.out.println("<--------Inside genDebitNote11----------->");
	    	StringBuffer xmlBuff = new StringBuffer();
			String xmlString = "", sql="", refNo="", procCode="";
			String retString="";
			GenericUtility genutility=null;
			SimpleDateFormat sdf=null;
			String currAppdate ="";
			java.sql.Timestamp currDate = null;
			String itemSer="";
			String finEntity="",acctCode="",cctrCode="",acctCodetotAR="",cctrCodetotAR="",dbTranID="";			
			String crTerm="";
			PreparedStatement pstmt=null;
			ResultSet rs=null;
			int count=0;
			try
			{
				genutility=GenericUtility.getInstance();
				sdf=new SimpleDateFormat(genutility.getApplDateFormat());
				
				currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
				currAppdate = sdf.format(currDate);
				itemSer =getColumnDescr(conn, "item_ser","invoice","invoice_id", invoiceId);
				finEntity = getColumnDescr(conn, "fin_entity","site","site_code", siteCode);
				acctCode = getColumnDescr(conn,"acct_code__ar", "customer", "cust_code", custCode);
				cctrCode = getColumnDescr(conn,"cctr_code__ar", "customer", "cust_code", custCode);
				//acctCodetotAR=getColumnDescr(conn,"ACCT_CODE__TOT_AR", "item_acct_detr", "item_ser", itemSer);
				//cctrCodetotAR=getColumnDescr(conn,"CCTR_CODE__TOT_AR", "item_acct_detr", "item_ser", itemSer);
				cctrCodetotAR = cctrCodetotAR== null ? " " :cctrCodetotAR;		
			xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
			xmlBuff.append("<DocumentRoot>\r\n");
			xmlBuff.append("<description>").append("Datawindow Root").append("</description>\r\n");
			xmlBuff.append("<group0>\r\n");
			xmlBuff.append("<description>").append("Group0 description").append("</description>\r\n");
			xmlBuff.append("<Header0>\r\n");
			xmlBuff.append("<objName><![CDATA[").append("drcrrcp_dr").append("]]></objName>\r\n");  
			xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>\r\n");
			xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>\r\n");
			xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>\r\n");
			xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>\r\n");
			xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>\r\n");
			xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>\r\n");
			xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>\r\n");
			xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>\r\n");
			xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>\r\n");
			xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>\r\n");
			xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>\r\n");
			xmlBuff.append("<description>").append("Header0 members").append("</description>\r\n");		
			xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"drcrrcp_dr\" objContext=\"1\">\r\n");  
			xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
			xmlBuff.append("<tran_id/>\r\n");
			xmlBuff.append("<tran_ser><![CDATA[DRNRCP]]></tran_ser>\r\n");
			xmlBuff.append("<drcr_flag>").append("<![CDATA[").append("D").append("]]></drcr_flag>\r\n");
			
			xmlBuff.append("<tran_date><![CDATA["+currAppdate +"]]></tran_date>\r\n");
			xmlBuff.append("<eff_date><![CDATA["+currAppdate+"]]></eff_date>\r\n");
			//xmlBuff.append("<amount><![CDATA["+ bdNoteHdr +"]]></amount>");
			xmlBuff.append("<item_ser><![CDATA["+ itemSer  +"]]></item_ser>\r\n");
			xmlBuff.append("<site_code><![CDATA["+ siteCode  +"]]></site_code>\r\n");
			xmlBuff.append("<cust_code><![CDATA["+ custCode  +"]]></cust_code>\r\n");
			xmlBuff.append("<invoice_id><![CDATA["+ invoiceId  +"]]></invoice_id>\r\n");
			crTerm=getColumnDescr(conn, "cr_term","invoice","invoice_id", invoiceId);
			System.out.println("crTerm---->>["+crTerm+"]");
			xmlBuff.append("<cr_term><![CDATA["+ crTerm  +"]]></cr_term>");
			xmlBuff.append("<remarks>").append("<![CDATA[").append("Auto Generated Debit Note for POD").append("]]></remarks>\r\n");
			//xmlBuff.append("<due_date><![CDATA["+ (dueDate==null?"":sdf.format(dueDate).toString()) +"]]></due_date>");
			xmlBuff.append("<fin_entity><![CDATA["+ finEntity  +"]]></fin_entity>\r\n");
			xmlBuff.append("<curr_code><![CDATA["+ currencyCode  +"]]></curr_code>\r\n");
			xmlBuff.append("<exch_rate><![CDATA["+ exchRate  +"]]></exch_rate>\r\n");
			//xmlBuff.append("<round_adj><![CDATA["+ roundAdj +"]]></round_adj>");
			//xmlBuff.append("<anal_code><![CDATA["+ analCode  +"]]></anal_code>");
			xmlBuff.append("<acct_code><![CDATA["+ acctCode  +"]]></acct_code>\r\n");
			xmlBuff.append("<cctr_code><![CDATA["+ cctrCode  +"]]></cctr_code>\r\n");
			//xmlBuff.append("<gp_no><![CDATA["+ gpNo  +"]]></gp_no>");
			//xmlBuff.append("<gp_date><![CDATA["+ (gpDate==null?"":sdf.format(gpDate).toString()) +"]]></gp_date>");
			xmlBuff.append("</Detail1>\r\n");
			
			xmlBuff.append(detail2xmlString);	
			
			pstmt=conn.prepareStatement("SELECT ACCT_CODE__SAL,CCTR_CODE__SAL from invoice where invoice_id=?" );
			pstmt.setString(1, invoiceId);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				acctCode=rs.getString("ACCT_CODE__SAL");
				cctrCode=rs.getString("CCTR_CODE__SAL");
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			
			String lineNo1="1";
			xmlBuff.append("<Detail3 dbID='' domID='"+lineNo1+"' objName=\"drcrrcp_dr\" objContext=\"2\">"); 
			xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
			xmlBuff.append("<tran_id/>");
			xmlBuff.append("<line_no><![CDATA["+lineNo1+"]]></line_no>\r\n");
			xmlBuff.append("<acct_code><![CDATA["+acctCode+"]]></acct_code>\r\n");
			xmlBuff.append("<cctr_code><![CDATA["+cctrCode+"]]></cctr_code>\r\n");
			xmlBuff.append("<amount><![CDATA["+debitNoteAmtHdr+"]]></amount>\r\n");	
			xmlBuff.append("</Detail3>");
			lineNo1="2";
			
			xmlBuff.append("<Detail3 dbID='' domID='"+lineNo1+"' objName=\"drcrrcp_dr\" objContext=\"2\">"); 
			xmlBuff.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
			xmlBuff.append("<tran_id/>");
			xmlBuff.append("<line_no><![CDATA["+lineNo1+"]]></line_no>\r\n");
			xmlBuff.append("<acct_code><![CDATA["+acctCode+"]]></acct_code>\r\n");
			xmlBuff.append("<cctr_code><![CDATA["+cctrCode+"]]></cctr_code>\r\n");
			xmlBuff.append("<amount><![CDATA["+(detail3AmtT - debitNoteAmtHdr)+"]]></amount>\r\n");	
			xmlBuff.append("</Detail3>");
			
		
			
			xmlBuff.append("</Header0>");
			xmlBuff.append("</group0>");
			xmlBuff.append("</DocumentRoot>");
			
			xmlString=xmlBuff.toString();
					
			 System.out.println("All XML String --->>[["+xmlBuff.toString()+"]]");   
	           
				System.out.println("------ debit Note Data saving------------------");
				retString=saveData(siteCode, xmlString, conn);
				System.out.println("debit Note Data saved successfully. retString : [["+retString+"]]");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("retString.indexOf(Success) > -1)");
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");

					dbTranID = arrayForTranId[1].substring(0,endIndex);
					//retString ="";
					System.out.println("@@@@@@3: retString"+retString);
					System.out.println("DebitNoteTranID------>>>["+dbTranID+"]");
					if(dbTranID != null && dbTranID.trim().length() > 0)
					{
						
						System.out.println("tran_id__drn generated successfully----------");	
						debitNoteTranID=dbTranID;
					}	
					
				
				}
				

			}catch(Exception e)
			{
				isError=true;
				retString  = e.getMessage();
						e.printStackTrace();
						throw new ITMException(e);
			}
	    	
	    	return retString;	
	    
	}
	private String getDetailsXmlString(String invoiceId, int lineNoTrace,int lineNo,double rateInv,double qtyInv,String lotNo,String lotSl,String itemCode, double debitNoteAmtDetail)
	{
		invoiceId=invoiceId==null ? "" : invoiceId.trim();
		System.out.println("in getDetailsXmlString -->> invoiceId--->>["+invoiceId+"]");
		StringBuffer detailsXml=new StringBuffer();	
		
		try 
		{
		
				detailsXml.append("<Detail2 dbID='' domID='"+lineNo+"' objName=\"drcrrcp_dr\" objContext=\"2\">"); 
				detailsXml.append("<attribute pkNames=\"\" selected=\"N\" updateFlag=\"A\" status=\"N\" />");
				detailsXml.append("<tran_id/>");
				detailsXml.append("<line_no><![CDATA["+lineNo+"]]></line_no>\r\n");
				detailsXml.append("<line_no__invtrace><![CDATA["+lineNoTrace+"]]></line_no__invtrace>\r\n");
				detailsXml.append("<line_no__inv><![CDATA["+lineNoTrace+"]]></line_no__inv>\r\n");
				detailsXml.append("<invoice_id><![CDATA["+invoiceId+"]]></invoice_id>\r\n");				
				
				detailsXml.append("<item_code><![CDATA["+itemCode+"]]></item_code>\r\n");

				detailsXml.append("<rate><![CDATA["+rateInv+"]]></rate>\r\n");
				detailsXml.append("<rate__std><![CDATA["+rateInv+"]]></rate__std>\r\n");
				detailsXml.append("<quantity><![CDATA["+qtyInv+"]]></quantity>\r\n");
				detailsXml.append("<lot_no><![CDATA["+lotNo+"]]></lot_no>\r\n");
				detailsXml.append("<lot_sl><![CDATA["+lotSl+"]]></lot_sl>\r\n");
				
				detailsXml.append("<rate__clg><![CDATA["+rateInv+"]]></rate__clg>\r\n");
				//String debitNoteAmt1="100.10";
				detailsXml.append("<drcr_amt><![CDATA["+debitNoteAmtDetail+"]]></drcr_amt>\r\n");
				detailsXml.append("<net_amt><![CDATA["+debitNoteAmtDetail+"]]></net_amt>\r\n");
				//detailsXml.append("<reas_code><![CDATA["+reasCode+"]]></reas_code>");
				detailsXml.append("</Detail2>");				

			
			
							
			
		}
		catch(Exception e)
		{
			System.out.println("Exception :getDetail2xmlString method  :==>");
			isError=true;
			e.printStackTrace();
		}
		
		System.out.println("@@@@@2: xmlString detail2 + detail3--->>[["+detailsXml.toString()+"]]");
		return detailsXml.toString();
	}
	private int updatePodHeader(Connection conn,String tranId,String loginCode)
	{
		String sql="";
		int lineNo=0,count=0;
		double dbAmt=0.0,debitNoteHeaderL=0;
		PreparedStatement pstmt=null;
		
		try{
		
		System.out.println("spl_sales_por_det updated successfully but not commit yet------");
		
		System.out.println("Final debitNote amt Header----->>["+debitNoteHeaderL+"]");
		
		
		sql="update spl_sales_por_hdr set tran_id__drn = ? where tran_id = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,debitNoteTranID);
		pstmt.setString(2, tranId);
		count=pstmt.executeUpdate();	
		
		sql="update spl_sales_por_hdr set confirmed = ?,conf_date = ?, emp_code__aprv = ? where tran_id = ? ";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, "Y");
		pstmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
		pstmt.setString(3,loginCode);		//loginCode //"E10808"
		pstmt.setString(4, tranId);
		count=pstmt.executeUpdate();
		System.out.println("spl_sales_por_hdr(confirmed) updated successfully but not commit yet------");
		}//end try
		catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
		System.out.println("update count@@@----->>["+count+"]");
		return count;
		
	}
	private int updateDrCrRcpHdrAmt(String tranId,double actualDebitNote,Connection conn)
	{
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="";
		int cnt=0;
		double drCrAcctAmt=0,drCrAcctAmtM=0,hdrDbAmt=0,finalHdrAmt=0,exchRate=0,amountBc=0;
		
		try{
			sql="select amount from drcr_racct where tran_id= ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				drCrAcctAmt=rs.getDouble(1);
				
				if(drCrAcctAmt < 0){
					drCrAcctAmtM=drCrAcctAmtM +	drCrAcctAmt;
				}			
					
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("Final amount drcr_racct------>>["+drCrAcctAmtM+"]");
			sql="select amount,exch_rate from DRCR_RCP where tran_id= ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				//hdrDbAmt=rs.getDouble(1);
				exchRate=rs.getDouble(2);
			}
			System.out.println("Header amount in DRCR_RCP------>>["+hdrDbAmt+"]");
			
			finalHdrAmt= hdrDbAmt + drCrAcctAmtM;
			amountBc = actualDebitNote * exchRate;
			System.out.println("Final Header amount in DRCR_RCP------>>["+finalHdrAmt+"]");
			System.out.println("Final Header amount_bc in DRCR_RCP------>>["+amountBc+"]");
			
			sql="update DRCR_RCP set amount = ?,amount__bc = ? where tran_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setDouble(1, actualDebitNote);
			pstmt.setDouble(2, amountBc);
			pstmt.setString(3, tranId);
			cnt=pstmt.executeUpdate();	
			
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
		}
		catch(Exception e)
		{
			isError=true;
			e.printStackTrace();
		}
		System.out.println("cnt in updateDrCrRcpHdrAmt---->["+cnt+"]");
		return cnt;
	}
	
	private String saveData(String siteCode, String xmlString, Connection conn) throws ITMException
	{

		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");

			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString in masterStateful :::: " + xmlString);
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString, true, conn);
			masterStateful = null;			
			
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :PosConfirm :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			isError=true;
			System.out.println("Exception :PosConfirm :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	
	private double getDebitNoteAmount (double rateInv,double qtyInv)
	{
		System.out.println("-------- in getDebitNoteAmount method -------------");
		double debitNoteAmount=0,aprvRate=0,dbResale=0,dbAmt=0;
		System.out.println("rateInv-->>["+rateInv+"]");		
		System.out.println("qtyInv-->>["+qtyInv+"]");
		//debitNoteAmount= (podRate - apprvRate) * podQty;
		//dbResale= podRate * podQty;
		
		debitNoteAmount=rateInv * qtyInv;
		//System.out.println("dbResale-->>["+podQty+"]");
		System.out.println("dbAmt-->>["+dbAmt+"]");
		/*
		if(dbResale > dbAmt){
			debitNoteAmount=dbResale - dbAmt;
		}*/		
		System.out.println("debitNoteAmount-->>["+debitNoteAmount+"]");	
		return debitNoteAmount;
	}
	
	private boolean isRegulatePriceNull(Connection conn,String tranId)
	{
		boolean isNull=false;
		PreparedStatement pstmt1 = null ;
		ResultSet rs1 = null ; 
		String sql="",itemCode="",regulatedPrice="";
		sql="select item_code from spl_sales_por_det where tran_id = ?";
		//getColumnDescr(conn,"regulated_price","item","item_code",itemCode);
		try
		{
			pstmt1=conn.prepareStatement(sql);
			pstmt1.setString(1, tranId);
			rs1=pstmt1.executeQuery();
			
			while(rs1.next())
			{
				itemCode=rs1.getString(1)==null ? "" :rs1.getString(1);
				regulatedPrice=getColumnDescr(conn,"regulated_price","item","item_code",itemCode);
				System.out.println("--regulatedPrice in isRegulatePriceNull method -->>"+regulatedPrice);
				if(regulatedPrice == null || regulatedPrice.trim().length()==0)				
					return true;				
				
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception in isRegulatePriceNull method ");
			e.printStackTrace();
			
		}
		return false;
	}
	
	
	
	public String confirmDebitNote(String businessObj, String tranIdFr,String xtraParams, String forcedFlag, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");
			System.out.println("-------in confirmDebitNote mothod----------------");			
			tranIdFr=tranIdFr==null ? "" :tranIdFr.trim();
			System.out.println("Tran id for debit note--->>"+tranIdFr+"]");
			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,businessObj);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			compName=compName==null ? "" :compName.trim();
			System.out.println("serviceCode = ["+serviceCode+"]   compName ["+compName+"]");
			sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,serviceCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			serviceURI=serviceURI==null ? "" :serviceURI.trim();
			System.out.println("serviceURI = ["+serviceURI+"]---> compName = ["+compName+"]");
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName);
			aobj[1] = new String(tranIdFr);
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);
			//System.out.println("@@@@@@@@@@loginEmpCode:" +genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode")+":");
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);

			retString = (String)call.invoke(aobj);

			System.out.println("Confirm Complete @@@@@@@@@@@ Return string from NVO is:==>["+retString+"]");

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{		
			try{
				if (pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				/*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
				System.out.println("Exception inCalling confirmed");
				e.printStackTrace();
				try{
					conn.rollback();

				}catch (Exception s)
				{
					System.out.println("Unable to rollback");
					s.printStackTrace();
				}
				throw new ITMException(e);
			}

		}
		return retString;
	}//end confirmCreditNote	
	
	public String confirmConsOrder(String businessObj, String tranIdFr,String xtraParams, Connection conn) throws ITMException
			{

			        String methodName = "";
			        String compName = "";
			        String retString = "";
			        String serviceCode = "";
			        String serviceURI = "";
			        String actionURI = "";
			        String sql = "";
			        PreparedStatement pstmt = null;
			        ResultSet rs = null;
			        System.out.println("confirmSaleOrder(String businessObj, String tranIdFr,String	xtraParams, String forcedFlag, Connection conn) called >>><!@#>");

			        try
			        {
			                //ConnDriver connDriver = new ConnDriver();
			                //conn = connDriver.getConnectDB("DriverITM");
			        System.out.println("enter in confirm method [[[[[[[[[[[]]]]]]]]]]] >>>>>");
			                methodName = "gbf_post";
			                actionURI = "http://NvoServiceurl.org/" + methodName;

			                sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND	EVENT_CODE = 'pre_confirm' ";
			                pstmt = conn.prepareStatement(sql);
			                pstmt.setString(1,businessObj);
			                rs = pstmt.executeQuery();
			                if ( rs.next() )
			                {
			                        serviceCode = rs.getString("SERVICE_CODE");
			                        compName = rs.getString("COMP_NAME");
			                }
			                System.out.println("serviceCode = "+serviceCode+" compName "+compName);
			                sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			                pstmt = conn.prepareStatement(sql);
			                pstmt.setString(1,serviceCode);
			                rs = pstmt.executeQuery();
			                if ( rs.next() )
			                {
			                        serviceURI = rs.getString("SERVICE_URI");
			                }
			                System.out.println("serviceURI = "+serviceURI+" compName = "+compName);
			                Service service = new Service();
			                Call call = (Call)service.createCall();
			                call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			                call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org",methodName ) );
			                call.setUseSOAPAction(true);
			                call.setSOAPActionURI(actionURI);
			                Object[] aobj = new Object[4];

			                call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org","component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			                call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org","tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			                call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org","xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			                call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org","forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			                aobj[0] = new String(compName);
			                aobj[1] = new String(tranIdFr);
			                aobj[2] = new String(xtraParams);
			                //aobj[3] = new String(forcedFlag);
			                //System.out.println("@@@@@@@@@@loginEmpCode:"+genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode")+":");
			                System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			                call.setReturnType(XMLType.XSD_STRING);
			                retString = (String)call.invoke(aobj);
			                System.out.println("Confirm Complete @@@@@@@@@@@ Return string from NVO	is:==>["+retString+"]");

			        }
			        catch(Exception e)
			        {
			                e.printStackTrace();
			                throw new ITMException(e);
			        }
			        finally
			        {                
			                try{
			                        
			                
			                        if (pstmt != null )
			                        {
			                                pstmt.close();
			                                pstmt = null;
			                        }
			                        /*if( conn != null ){
			                                conn.close();
			                                conn = null;
			                        }*/
			                }
			                catch(Exception e)
			                {
			                        System.out.println("Exception inCalling confirmed");
			                        e.printStackTrace();
			                        try{
			                                conn.rollback();

			                        }catch (Exception s)
			                        {
			                                System.out.println("Unable to rollback");
			                                s.printStackTrace();
			                        }
			                        throw new ITMException(e);
			                }
			        }
			        return retString;

			}
	
	
}
