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.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 javax.ejb.Stateless;
import javax.naming.InitialContext;

@Stateless
public class ProofOfDeliveryConf extends ActionHandlerEJB implements ProofOfDeliveryConfLocal,ProofOfDeliveryConfRemote
{
	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-----------");		
		ResultSet rs=null;
		Connection conn=null;
		ConnDriver ConnDriver = new ConnDriver();
		PreparedStatement pstmt=null;
		String errString="",sql="",loginSite="",wfStatus="",itemCode="",regulatedPrice="";
		double invRate=0.0,invQty=0.0,PODrate=0.0,PODqty=0.0,allowedAmt=0.0,stockistAmt=0.0,debitNoteAmtAct=0.0;
		int updCnt=0,lineNo=0;
		ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		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("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;
				boolean isRegPrice=false;
				GenericUtility genericUtility=new GenericUtility();
				loginCode=genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				System.out.println("Login Code ------->> "+loginCode);
				sql="";
				
				//emp_code__aprv login code BASE not available in Employee Master
				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);		
				pstmt.setString(4, tranId);
				updCnt=pstmt.executeUpdate();
				
				if (updCnt > 0)
				{				
					sql="";
					sql="select 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() )
					{
						custCode=rs.getString(1)==null ? "" : rs.getString(1);
						siteCode=rs.getString(2)==null ? "" : rs.getString(2);
						currCode=rs.getString(3)==null ? "" : rs.getString(3);
						ExchRate=rs.getDouble(4);
					}
					if(rs != null)
					{
						rs.close();
						rs=null;						
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;						
					}
					
					sql="";
					sql="select item_code,rate__inv,quantity__inv,rate__resale,quantity__resale,line_no from spl_sales_por_det where tran_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						itemCode=rs.getString(1)==null ? "" :rs.getString(1);
						invRate=rs.getDouble(2);
						invQty=rs.getDouble(3);
						PODrate=rs.getDouble(4);
						PODqty=rs.getDouble(5);
						lineNo=rs.getInt(6);
						System.out.println("Line NO :"+lineNo+"------->> invRate : "+invRate+" invQty : "+invQty+" PODrate : "+PODrate+ " PODqty : "+PODqty);
						
						regulatedPrice=getColumnDescr(conn,"regulated_price","item","item_code",itemCode);
						//regulatedPrice if null then
						//isRegPrice=;
						if(isRegulatePriceNull(conn,tranId))
						{
							//Error : Regulated price is Null 
							errString = itmdbAccess.getErrorString("", "VTREGPRCN", "", "", conn);
							System.out.println("---------Error : Regulated price is Null----------");
							return errString;
							
						}
						else
						{
							debitNoteAmtAct=debitNoteCalculation(conn,regulatedPrice,invRate,invQty,PODrate,PODqty);
							System.out.println("Line no -->"+lineNo+" --debitNoteAmtAct ------->>"+debitNoteAmtAct);
							if (debitNoteAmtAct > 0)
							{
								System.out.println("-----Debit note generation condition----------");
								errString=genDebitNote(tranId,custCode,siteCode,xtraParams,debitNoteAmtAct,currCode,ExchRate, conn);
								conn.commit();
								errString = itmdbAccess.getErrorString("", "VTCONSUCF", "", "", conn);	
								System.out.println("------@@@Debit Note Generated successfully------------------");							
							}
							else
							{
								System.out.println("--------Debit note not generated------------- ");
								conn.commit();
							}
						 }
						
						
					}	
					
			   }
			}
			else
			{	
				conn.rollback();
				errString = itmdbAccess.getErrorString("", "VTNSUBCON", "", "", conn);
			}
			
		} // end try
		catch(SQLException se)
		{
			System.out.println("SQLException : class ProofOfDeliveryConf : ");
			se.printStackTrace();
			try
			{
			  conn.rollback();
			}
			catch(Exception e){
				System.out.println("Exception : Occure during rollback........");e.printStackTrace();
				}
		}
		catch(Exception e)
		{
			try
			{
				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;
				}
							
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
				
			}
		}
		System.out.println("[ProofOfDeliveryConf]errstring :"+errString);
		return errString;
	}
	
	private String genDebitNote(String tranId,String custCode,String siteCode ,String xtraParams,double relAmt,String currencyCode,double exchRate, Connection conn) throws ITMException
	{

	    	System.out.println("<--------Inside genDebitNote11----------->");
	    	StringBuffer xmlBuffer = new StringBuffer();
			String xmlString = "", sql="", refNo="", procCode="";
			String retString="";
			GenericUtility genutility=null;
			SimpleDateFormat sdf=null;
			String currAppdate ="";
			java.sql.Timestamp currDate = null;
			//String siteCode="";
			String finEntity="";
			String acctCode="";
			String cctrCode="";
			String acctCodetotAR="";
			String cctrCodetotAR="";
			String miscDebitNoteTranID="";
			PreparedStatement pstmt=null;
			ResultSet rs=null;
			int count=0;
	    	xmlBuffer.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
			xmlBuffer.append("<DocumentRoot>");
			xmlBuffer.append("<description>").append("Datawindow Root").append("</description>");
			xmlBuffer.append("<group0>");
			xmlBuffer.append("<description>").append("Group0 description").append("</description>");
			xmlBuffer.append("<Header0>");
			xmlBuffer.append("<objName><![CDATA[").append("misc_drcr_rcp_dr").append("]]></objName>");
			xmlBuffer.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
			xmlBuffer.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
			xmlBuffer.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
			xmlBuffer.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
			xmlBuffer.append("<action><![CDATA[").append("SAVE").append("]]></action>");
			xmlBuffer.append("<elementName><![CDATA[").append("").append("]]></elementName>");
			xmlBuffer.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
			xmlBuffer.append("<taxKeyValue><![CDATA[").append("	").append("]]></taxKeyValue>");
			xmlBuffer.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
			xmlBuffer.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
			xmlBuffer.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
			xmlBuffer.append("<description>").append("Header0 members").append("</description>");
			
			try
			{
				genutility=GenericUtility.getInstance();
				sdf=new SimpleDateFormat(genutility.getApplDateFormat());
				
				currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
				currAppdate = sdf.format(currDate);
				//siteCode =(genutility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"));
				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=findValue(conn,"ACCT_CODE__TOT_AR", "item_acct_detr", "item_ser", itemser);
				//cctrCodetotAR=findValue(conn,"CCTR_CODE__TOT_AR", "item_acct_detr", "item_ser", itemser);
				System.out.println("-------FinEntity : "+finEntity+" acctCode : "+acctCode+ " cctrCode : "+cctrCode);
				
				xmlBuffer.append("<Detail1 objContext =\"1\"").append(" objName=\"misc_drcr_rcp_dr\" domID=\"1\" dbID=\"\">");
				xmlBuffer.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuffer.append("<tran_id/>");
				xmlBuffer.append("<tran_ser>").append("<![CDATA[").append("MDRCRD").append("]]></tran_ser>\r\n");
		        xmlBuffer.append("<tran_date>").append("<![CDATA[").append(currAppdate).append("]]></tran_date>\r\n");
		        xmlBuffer.append("<eff_date>").append("<![CDATA[").append(currAppdate).append("]]></eff_date>\r\n");
				xmlBuffer.append("<fin_entity>").append("<![CDATA[").append(finEntity).append("]]></fin_entity>\r\n");
				xmlBuffer.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]></site_code>\r\n");
		        xmlBuffer.append("<cctr_code>").append("<![CDATA[").append(cctrCode).append("]]></cctr_code>\r\n");
				xmlBuffer.append("<sundry_type>").append("<![CDATA[").append("C").append("]]></sundry_type>\r\n");
				xmlBuffer.append("<sundry_code>").append("<![CDATA[").append(custCode).append("]]></sundry_code>\r\n");
				xmlBuffer.append("<acct_code>").append("<![CDATA[").append(acctCode).append("]]></acct_code>\r\n");
				xmlBuffer.append("<curr_code>").append("<![CDATA[" + currencyCode + "]]>").append("</curr_code>");
				xmlBuffer.append("<amount>").append("<![CDATA[").append(relAmt).append("]]></amount>\r\n");
	            xmlBuffer.append("<drcr_flag>").append("<![CDATA[").append("D").append("]]></drcr_flag>\r\n");
				//xmlBuffer.append("<item_ser>").append("<![CDATA[").append(itemser).append("]]></item_ser>\r\n");
				xmlBuffer.append("<exch_rate>").append("<![CDATA[").append(exchRate).append("]]></exch_rate>\r\n");
		        xmlBuffer.append("<confirmed>").append("<![CDATA[").append("N").append("]]></confirmed>\r\n");
		        //xmlBuffer.append("<acct_code__tot_ar>").append("<![CDATA[").append(acctCodetotAR).append("]]></acct_code__tot_ar>\r\n");
		        //xmlBuffer.append("<cctr_code__tot_ar>").append("<![CDATA[").append(cctrCodetotAR).append("]]></cctr_code__tot_ar>\r\n");
		        //xmlBuffer.append("<milestone_id>").append("<![CDATA[").append(mileStoneId).append("]]></milestone_id>\r\n");
		       // xmlBuffer.append("<proj_code>").append("<![CDATA[").append(projCode).append("]]></proj_code>\r\n");
		        //xmlBuffer.append("<sale_order>").append("<![CDATA[").append(saleOrder).append("]]></sale_order>\r\n");
		        xmlBuffer.append("<tran_type>").append("<![CDATA[").append("HO").append("]]></tran_type>\r\n");
		        xmlBuffer.append("<remarks>").append("<![CDATA[").append("Auto Generated Debit Note for POD").append("]]></remarks>\r\n");
		        xmlBuffer.append("<chg_user/>\r\n");
		        xmlBuffer.append("<chg_term/>\r\n");
		        xmlBuffer.append("<chg_date>").append("<![CDATA[").append(currAppdate).append("]]></chg_date>\r\n");
				xmlBuffer.append("</Detail1>\r\n");
				
				/*xmlBuffer.append("<Detail2 objContext =\"2\"").append(" objName=\"misc_drcr_rcp_dr\" domID=\"1\" dbID=\"\">");
				xmlBuffer.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuffer.append("<tran_id/>");
				xmlBuffer.append("<line_no>").append("<![CDATA[").append(1).append("]]></line_no>");
				xmlBuffer.append("<net_amt>").append("<![CDATA[").append(relAmt).append("]]></net_amt>");
				xmlBuffer.append("<amount>").append("<![CDATA[").append(relAmt).append("]]></amount>");
	            xmlBuffer.append("<acct_code>").append("<![CDATA[").append(acctCodetotAR).append("]]></acct_code>");
	            xmlBuffer.append("<cctr_code>").append("<![CDATA[").append(cctrCodetotAR).append("]]></cctr_code>");
	            xmlBuffer.append("<sundry_type>").append("<![CDATA[").append("C").append("]]></sundry_type>\r\n");
	            xmlBuffer.append("<tax_class>").append("<![CDATA[").append(taxClass).append("]]></tax_class>\r\n");
	            xmlBuffer.append("<tax_chap>").append("<![CDATA[").append(taxChap).append("]]></tax_chap>\r\n");
	            xmlBuffer.append("<tax_env>").append("<![CDATA[").append(taxEnv).append("]]></tax_env>\r\n");
	            
	            xmlBuffer.append("</Detail2>");*/
	            xmlBuffer.append("</Header0>");
				xmlBuffer.append("</group0>");
				xmlBuffer.append("</DocumentRoot>");
				System.out.println("^^^^^^After document root end^^^^^^^^");
				xmlString=xmlBuffer.toString();
				System.out.println("xmlString  of Debit Note --->>: [["+xmlString+"]]");
				System.out.println("------ debit Note Data saving------------------");
				retString=saveData(siteCode, xmlString, conn);
				System.out.println("------ debit Note Data saved successfully123------------------");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("retString.indexOf(Success) > -1)");
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");

					miscDebitNoteTranID = arrayForTranId[1].substring(0,endIndex);
					//retString ="";
					System.out.println("@@@@@@3: retString"+retString);
					System.out.println("miscDebitNoteTranID : "+miscDebitNoteTranID);
					if(miscDebitNoteTranID != null && miscDebitNoteTranID.trim().length() > 0)
					{
						sql="update spl_sales_por_hdr set tran_id__drn = ? where tran_id = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,miscDebitNoteTranID);
						pstmt.setString(2, tranId);
						pstmt.executeUpdate();	
						System.out.println("tran_id__drn updated successfully----------");	
					}					
				
				}
				

			}catch(Exception e)
			{
				retString  = e.getMessage();
						e.printStackTrace();
						throw new ITMException(e);
			}
	    	
	    	return retString;	
	    
	}
	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 :::: " + 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)
		{
			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) throws  ITMException, RemoteException
	{

			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 findValue ");
				e.printStackTrace();
				throw new ITMException(e); 
			}
			System.out.println("returning String from findValue " + findValue);
			return findValue;
		 
	}
	
	private double debitNoteCalculation(Connection conn,String regulaPrice,double rateInvoice,double qtyInvoice,double podRate,double podQty) throws  ITMException, RemoteException
	{
		System.out.println("----------in debitNoteCalculation method----------");
		double debitNoteAmount=0.0;
		PreparedStatement pstmt1 = null ;
		ResultSet rs1 = null ; 
		String sql = "";
		
		if(regulaPrice.equalsIgnoreCase("Y"))
		{
			System.out.println("Regulated price-------->> "+regulaPrice);
			double allowPercentageD=0.0,invoiceAmt=0.0,stockistAmt=0.0,actualAllowAmt=0.0,percentageAmtInv=0.0;
			
			String varName="AML_REG_PER",allowPercentage="";
			allowPercentage=getColumnDescr(conn,"100-var_value","disparm","var_name",varName);	
			
			debitNoteAmount=getDebitNoteAmount(allowPercentage,rateInvoice,qtyInvoice,podRate,podQty);
			
			/*allowPercentage=allowPercentage==null ? "0.0" :allowPercentage;
			allowPercentageD=Double.parseDouble(allowPercentage);			
			invoiceAmt= rateInvoice * qtyInvoice;//10 *100
			percentageAmtInv= invoiceAmt * allowPercentageD/100; //1000 * 10/100
			actualAllowAmt= invoiceAmt + percentageAmtInv; //1100
			
			stockistAmt= podRate * podQty; //12 * 100 = 1200
			
			if(stockistAmt > actualAllowAmt)			
				debitNoteAmount= stockistAmt - actualAllowAmt;
			else 
				debitNoteAmount=0.0;		*/
			System.out.println("Actual Debit note Amount : "+debitNoteAmount);
			return debitNoteAmount;
		}
		else if(regulaPrice.equalsIgnoreCase("N"))
		{
			System.out.println("Regulated price-------->> "+regulaPrice);
			double allowPercentageD=0.0,invoiceAmt=0.0,stockistAmt=0.0,actualAllowAmt=0.0,percentageAmtInv=0.0;
			String varName="AML_NONREG_PER",allowPercentage="";
			allowPercentage=getColumnDescr(conn,"100-var_value","disparm","var_name",varName);	
			
			debitNoteAmount=getDebitNoteAmount(allowPercentage,rateInvoice,qtyInvoice,podRate,podQty);
			
			/*allowPercentage=allowPercentage==null ? "0.0" :allowPercentage;
			allowPercentageD=Double.parseDouble(allowPercentage);			
			invoiceAmt= rateInvoice * qtyInvoice;//10 *100
			percentageAmtInv= invoiceAmt * allowPercentageD/100; //1000 * 10/100
			actualAllowAmt= invoiceAmt + percentageAmtInv; //1100
			
			stockistAmt= podRate * podQty; //12 * 100 = 1200
			
			if(stockistAmt > actualAllowAmt)			
				debitNoteAmount= stockistAmt - actualAllowAmt;
			else 
				debitNoteAmount=0.0;*/
			System.out.println("Actual Debit note Amount : "+debitNoteAmount);
			
			return debitNoteAmount;
			
		}
		else if (regulaPrice.equalsIgnoreCase("D"))
		{
			System.out.println("Regulated price-------->> "+regulaPrice);
			//double allowPercentageD=0.0,invoiceAmt=0.0,stockistAmt=0.0,actualAllowAmt=0.0,percentageAmtInv=0.0;
			String varName="AML_DPCO_PER",allowPercentage="";
			allowPercentage=getColumnDescr(conn,"100-var_value","disparm","var_name",varName);			
						
			debitNoteAmount=getDebitNoteAmount(allowPercentage,rateInvoice,qtyInvoice,podRate,podQty);
			System.out.println("Actual Debit note Amount : "+debitNoteAmount);
			/*invoiceAmt= rateInvoice * qtyInvoice;//10 *100
			percentageAmtInv= invoiceAmt * allowPercentageD/100; //1000 * 10/100
			actualAllowAmt= invoiceAmt + percentageAmtInv; //1100
			
			stockistAmt= podRate * podQty; //12 * 100 = 1200
			
			if(stockistAmt > actualAllowAmt)			
				debitNoteAmount= stockistAmt - actualAllowAmt; //1200 -1100 =100			
			else
				debitNoteAmount=0.0;*/
			
			return debitNoteAmount;
	
		}
		
		
		
		return debitNoteAmount;
	}
	
	private double getDebitNoteAmount (String allowPercentage,double rateInvoice,double qtyInvoice,double podRate,double podQty)
	{
		System.out.println("-------- in getDebitNoteAmount method -------------");
		double allowPercentageD=0.0,invoiceAmt=0.0,stockistAmt=0.0,actualAllowAmt=0.0,percentageAmtInv=0.0,debitNoteCaclAmount=0.0;
				
					
		allowPercentage=allowPercentage==null ? "0.0" :allowPercentage;			
		allowPercentageD=Double.parseDouble(allowPercentage);	
		
		invoiceAmt= rateInvoice * qtyInvoice;//10 *100
		percentageAmtInv= invoiceAmt * allowPercentageD/100; //1000 * 10/100
		actualAllowAmt= invoiceAmt + percentageAmtInv; //1100
		
		System.out.println("getDebitNoteAmount -->> actualAllowAmt---------->> "+actualAllowAmt);
		stockistAmt= podRate * podQty; //12 * 100 = 1200
		
		if(stockistAmt > actualAllowAmt)			
			debitNoteCaclAmount= stockistAmt - actualAllowAmt; //1200 -1100 =100			
		else
			debitNoteCaclAmount=0.0;
		
		System.out.println("--------debitNoteCaclAmount------ :Debit note amount "+debitNoteCaclAmount);
		return debitNoteCaclAmount;
	}
	
	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;
	}
	
}
