package ibase.webitm.ejb.fin;

import ibase.scheduler.utility.interfaces.Schedule;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.File;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.sys.UtilMethods;
import ibase.utility.CommonConstants;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;


import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;


public class GenInvoiceSchedule implements Schedule  {
	
	
	
	private String invHost = "";  
	private String invUser = "";     
	private String invPwd = "";  	
	//private String remoteFilePath = "base_out/Stock.csv"; 
	private String remoteFilePath = ""; 
	private String xmlFileNameUpd = "";
	private String invRmFilePath="";
	private String invLocFilePath="";
	private String invBackupFilePath="";
	private String invPort="";
	static String accToBeCredited="";
	

	

	@Override
	public String schedule(HashMap arg0) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public String schedulePriority(String arg0) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String schedule(String scheduleParamXML) throws Exception {
		// TODO Auto-generated method stub
		
		System.out.println("scheduleParamXML=####===!!::::"+scheduleParamXML);
		ibase.utility.UserInfoBean userInfo = null;
		String disparmLoginSiteCode = "";
		Connection conn = null;
		Connection connRtm = null;
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat sdf;
		
		conn = connDriver.getConnectDB("DriverITM");
		
		String sql = "",retString="",finParamTranType="",filePath="",updResult="",custTypeList="",custType="",invoiceSql="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String siteCodeList="",siteCode="",invoiceId="";
		ArrayList<String> invoiceIdList = new ArrayList<String>();
		ArrayList<String> invoiceIdSuccList = new ArrayList<String>();
		ArrayList<String> invoiceFailedList = new ArrayList<String>();
		/*String queryString = "",suppAssignCode="";
		String sql = "",suppCode="",retString="",tranId="",siteCode="",suppCodeList="",;
		*/
		FinCommon finCommon = new FinCommon();
		
		
		try
		{
					
			Document dom = null;
			Node currDetail = null ;
			int noOfParam=0;
			
			
			GenericUtility genericUtility=GenericUtility.getInstance();
			System.out.println("scheduleParamXML-----"+scheduleParamXML);
			dom=genericUtility.parseString(scheduleParamXML);
			NodeList paramList = dom.getElementsByTagName( "SCHEDULE" );
            noOfParam = paramList.getLength();            
            NodeList parentNodeList = null,childNodeList = null;
			Node parentNode = null,childNode = null;
			//String DisparmSites = "";
			int childNodeListLength = 0;
			String childNodeName = null;
			
		/*	loginCode=genericUtility.getColumnValue("loginCode", dom);
			System.out.println("loginCode  @@@ :"+loginCode);
		*/	
			parentNodeList = dom.getElementsByTagName("ACTUALPARAMETERS");

			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			childNodeListLength = childNodeList.getLength();
			System.out.println("childNodeListLength  !!!!!"+childNodeListLength);
			for(int ctr = 0; ctr < childNodeListLength; ctr++)
			{
				childNode = childNodeList.item(ctr);
				childNodeName = childNode.getNodeName();
							
				if(childNodeName!=null && !"#text".equalsIgnoreCase(childNodeName))
				{
					if(ctr==0)
					{
						siteCode = childNode.getFirstChild().getNodeValue();
					}
					
					if(ctr==1)
					{
						custTypeList = childNode.getFirstChild().getNodeValue();
					}
								
				}
			}
			System.out.println("site code parameter "+siteCode);
			System.out.println("custTypeList "+custTypeList);
			
			invoiceIdList.clear();
			
			
			String[] arrCustType =custTypeList.split(",");
		    System.out.println("arrCustType  "+arrCustType);
			int len =arrCustType.length;
			System.out.println("length of suppCodeList: "+len);
			
			
			for(int k =0;k<len;k++)
			{
				
				custType=arrCustType[k];
				System.out.println("custType is : "+custType);
			
			System.out.println("For Invoice.....");
			//pstmt = conn.prepareStatement("SELECT INVOICE_ID FROM invoice WHERE CONFIRMED='Y' AND DOWNLOAD_FLAG ='Y' AND SITE_CODE=?");
			//pstmt = conn.prepareStatement("SELECT INVOICE_ID FROM invoice WHERE CONFIRMED='Y' AND CASE WHEN DOWNLOAD_FLAG IS NULL THEN 'N' ELSE DOWNLOAD_FLAG END='N' AND SITE_CODE=? ");
			invoiceSql=" SELECT i.INVOICE_ID FROM invoice i,customer c WHERE i.cust_code__bil=c.cust_code and i.CONFIRMED='Y' AND " +
					     " CASE WHEN i.DOWNLOAD_FLAG IS NULL THEN 'N' ELSE i.DOWNLOAD_FLAG END='N' AND i.SITE_CODE=? and c.cust_type=? ";
			pstmt = conn.prepareStatement(invoiceSql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,custType);
			rs = pstmt.executeQuery();
			while (rs.next()) 
			{
				invoiceIdList.add(rs.getString("INVOICE_ID"));
			}
			System.out.println("invoiceIdList for invoice :"+invoiceIdList);
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		
			}//end of for loop
			
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			dom = db.parse("/IBASEHOME/ibase.xml");
			invLocFilePath = dom.getElementsByTagName("INV_EXPORT_PATH").item(0).getFirstChild().getNodeValue();
			System.out.println("invLocFilePath :"+invLocFilePath);
			
			for (int i = 0; i < invoiceIdList.size(); i++) 
			{
				invoiceId = invoiceIdList.get(i);
				System.out.println(invoiceId);
				filePath=invLocFilePath+"/invoice_"+invoiceId.trim()+".xml";
				System.out.println("filePath :"+filePath);
				retString = generateInvoiceXML(filePath,invoiceId,conn);
			
			
				
				System.out.println("retString :::"+retString);
				if (retString.indexOf("Success") > -1)
				{
					try
					{
						sdf= new SimpleDateFormat(genericUtility.getDBDateFormat());
						java.util.Date date=new Date();
		   		   		
		   		   		java.sql.Timestamp currentDt=java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
		   		   		
						System.out.println(">>>>INVOICE execute update query!!!!!!!!!");
						sql="update invoice set DOWNLOAD_FLAG='Y',INV_EXP_DATE=? where INVOICE_ID =?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1, currentDt);
						pstmt.setString(2, invoiceId);
						pstmt.executeUpdate();
						conn.commit();
						invoiceIdSuccList.add(invoiceId);
						System.out.println("invoice Id : "+invoiceId+" updated TRACE_STATUS 'Y' succefully!!!");
						/*--------------added by mahendra dated 24-DEC-2014-------------------*/
						
						finParamTranType=checkNull(finCommon.getFinparams("999999","INV_EDI_AUTO_UPLOAD",conn));
						
						System.out.println("finParamTranType :"+finParamTranType);	
						if(finParamTranType.equalsIgnoreCase("Y"))
						{
							//filePath=this is local path where invoice xml is generated.
							//invRmFilePath = this is remote path where invoice xml is uploaded
							//invUser=SFTP username
							//invHost=SFTP Host
							//invPwd=SFTP Password
							setSFTPConfig();
							invRmFilePath=invRmFilePath+"/invoice_"+invoiceId.trim()+".xml";
							invBackupFilePath=invBackupFilePath+"/invoice_"+invoiceId.trim()+".xml";
							System.out.println("invRmFilePath by appending invoice xml @@@@"+invRmFilePath);
							updResult=putFile(invUser, invHost,invPwd,invPort,invRmFilePath,filePath);
							if(updResult.equalsIgnoreCase("success"))
							{
								File fromFile = new File(filePath);
								File toFile = new File(invBackupFilePath);
								moveUploadFile(fromFile,toFile);
								System.out.println("invoice"+invoiceId.trim()+".xml uploaded sucessfully and move to backup folder!!!");
							}
							else
							{
								System.out.println("invoice"+invoiceId.trim()+".xml uploaded failed!!!");
							}
							
							
						}
						
						
						
						/*-----------------------------------------------------------------------------*/
						
					}
					catch(Exception e)
					{
						conn.rollback();
						e.printStackTrace();
						System.out.println("Exception :"+e.getMessage());
					}
					finally
					{
						try
						{
							if(pstmt != null)
							{
								pstmt.close();
							}
							
						}
						catch(Exception e)
						{
							e.printStackTrace();
						}
						
						
					}
				}
				else
				{
					invoiceFailedList.add(invoiceId);
					System.out.println("Failed invoice id transction : "+invoiceFailedList);
				} 
				
			}//end of for sitecodelist for invoice	
			
	
	
			
			/*---------------------------------------------------------------------------------------------------------------------*/
			
			
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					conn.close();
				}
				if(pstmt != null)
				{
					pstmt.close();
				}
				
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			
			
		}
		
		
		
		return "";
		
	
	}

	
	
	
	
	
	public String generateInvoiceXML(String filePath,String invoiceId,Connection conn)throws RemoteException, ITMException
	{
		
		String errString="";
		ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
	    try
	    {
	    	System.out.println("------generateInvoiceXM---------L ");
	    	
	    /*	DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document dom = db.parse("/IBASEHOME/ibase.xml");
			filepath = dom.getElementsByTagName("JBOSSHOME").item(0).getFirstChild().getNodeValue();
			filepath=filepath +"/export";
			System.out.println("filepath  :::: "+filepath);
		
			File directory = new File(filepath);
			if (directory.exists()) 
			{
		   
				System.out.println("Directory already exists ...");

		    }
			else
			{
				
				boolean succcess = directory.mkdir();
				System.out.println("succcess :"+succcess);
		        System.out.println("directory :"+directory);
			}
			
			
			
			
			
			filepath=directory+"/invoice_"+invoiceId.trim()+".xml";*/
			System.out.println("filepath :"+filePath);
			
			
	    	DocumentBuilderFactory icFactory = DocumentBuilderFactory.newInstance();
	        DocumentBuilder icBuilder;
	        
	        icBuilder = icFactory.newDocumentBuilder();
            Document document = icBuilder.newDocument();
         /* String xlmn="xmlns:gva=\"http://www.facturae.es/Facturae/2009/v3.2/Facturae\"\n"+"xmlns:xs="+"\"http://www.w3.org/2001/XMLSchema\"\n";
   		       xlmn = xlmn + "xmlns:fac=\"http://www.facturae.es/Facturae/2009/v3.2/Facturae\"\nxmlns:ds=\"http://www.w3.org/2000/09/xmldsig#\"";
   		   System.out.println(" "+xlmn);*/
            //Element mainRootElement = document.createElementNS("http://www.facturae.es/Facturae/2009/v3.2/Facturae", "fac:Facturae");

            Element mainRootElement = document.createElementNS("", "fac:Facturae");
            mainRootElement.setAttribute("xmlns:fac", "http://www.facturae.es/Facturae/2009/v3.2/Facturae");
            mainRootElement.setAttribute("xmlns:gva", "http://www.edicomgroup.com/schemas/gva/");
            mainRootElement.setAttribute("xmlns:xs", "http://www.w3.org/2001/XMLSchema");
            mainRootElement.setAttribute("xmlns:ds", "http://www.w3.org/2000/09/xmldsig#"); 
            
           
           
            
            document.appendChild(mainRootElement);
 
            // append child elements to root element
            mainRootElement.appendChild(getFileHeader(document,invoiceId,conn));
            mainRootElement.appendChild(getPartiesElement(document,invoiceId,conn));
            mainRootElement.appendChild(getInvoiceElement(document,invoiceId,conn));
            
            // create the xml file
			 //transform the DOM Object to an XML File
			 TransformerFactory transformerFactory = TransformerFactory.newInstance();
			 Transformer transformer = transformerFactory.newTransformer();
			 DOMSource domSource = new DOMSource(document);
			// StreamResult streamResult = new StreamResult(new File("//home//basedv-2//mahendra//work//xmlFile//Sample_Facturae11.xml"));
			 StreamResult streamResult = new StreamResult(new File(filePath));
			  
	         	 
			 transformer.transform(domSource, streamResult);
			 	 
			 System.out.println("Done creating XML File@@@!!");
	    	
			 return "Success";
	    	
	    }
	    catch (ParserConfigurationException pce) 
	    {
               pce.printStackTrace();
               System.out.println("ParserConfigurationException :"+pce.getMessage());
           	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR1", "");
			return errString;

        }
	    catch (TransformerException tfe) 
	    {
             tfe.printStackTrace();
             System.out.println("TransformerException :"+tfe.getMessage());
         	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR2", "");
			return errString;

        }
        catch(Exception e)
        {
        	e.printStackTrace();
        	  System.out.println("Exception :"+e.getMessage());
        	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR3", "");
			return errString;

        }
		
		
		
	   // return "Success";
	}

	
	
	
	
	
	
	
	
	
	private static Node getFileHeader(Document doc,String invoiceId ,Connection conn) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="",invType="",currencyCode="",invoiceISOCode="",finEntity="",siteCode="",siteRegNo="";
		double invAmt=0.0,netAmt=0.0;
		DecimalFormat twoDf = new DecimalFormat("#.00");
		DecimalFormat fourDf = new DecimalFormat("#.0000");
		DecimalFormat sixDf = new DecimalFormat("#.000000");
		UtilMethods utilMethods= new UtilMethods();
		try
		{
			sql = "select INV_AMT,INV_TYPE ,NET_AMT,CURR_CODE,site_code,FIN_ENTITY,SITE_CODE from invoice where invoice_id=? " ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				invAmt = rs.getDouble("INV_AMT");
				invType = rs.getString("INV_TYPE");
				netAmt=rs.getDouble("NET_AMT");
				currencyCode=rs.getString("CURR_CODE");
				finEntity=rs.getString("FIN_ENTITY");
				siteCode=rs.getString("SITE_CODE");
								
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			siteRegNo=getSiteRegData(conn,"TAXNO1",siteCode,finEntity);
			
            sql = "select iso4217 from currency where curr_code=? " ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, currencyCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				invoiceISOCode = rs.getString("iso4217");
												
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			//invAmt = invAmt == null ? " " : invAmt.trim();
			invType = invType == null ? " " : invType.trim();
			currencyCode = currencyCode == null ? " " : currencyCode.trim();
			invoiceISOCode = invoiceISOCode == null ? " " : invoiceISOCode.trim();
			finEntity = finEntity == null ? " " : finEntity.trim();
			siteRegNo = siteRegNo == null ? " " : siteRegNo.trim();
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
        Element fileHeader = doc.createElement("FileHeader");
       // fileHeader.setAttribute("id", id);
        fileHeader.appendChild(getElementValue(doc, fileHeader, "SchemaVersion", "3.2"));
        fileHeader.appendChild(getElementValue(doc, fileHeader, "Modality", "I"));
        fileHeader.appendChild(getElementValue(doc, fileHeader, "InvoiceIssuerType", "TE"));
        
        Element thirdParty = doc.createElement("ThirdParty");
        Element thirdPartyTaxId = doc.createElement("TaxIdentification");
        thirdPartyTaxId.appendChild(getElementValue(doc, fileHeader, "PersonTypeCode","J"));
        thirdPartyTaxId.appendChild(getElementValue(doc, fileHeader, "ResidenceTypeCode","R"));
        thirdPartyTaxId.appendChild(getElementValue(doc, fileHeader, "TaxIdentificationNumber","ESB96490867"));
        thirdParty.appendChild(thirdPartyTaxId);
        
        Element thirdPartyEntity = doc.createElement("LegalEntity");
        thirdPartyEntity.appendChild(getElementValue(doc, fileHeader, "CorporateName","Intercambio Electrónico de Datos,SL"));
        Element thirdPartyAddInSpain = doc.createElement("AddressInSpain");
        thirdPartyAddInSpain.appendChild(getElementValue(doc, fileHeader, "Address","Ronda de Auguste y Louis Lumiere 12"));
        thirdPartyAddInSpain.appendChild(getElementValue(doc, fileHeader, "PostCode","46980"));
        thirdPartyAddInSpain.appendChild(getElementValue(doc, fileHeader, "Town","Paterna"));
        thirdPartyAddInSpain.appendChild(getElementValue(doc, fileHeader, "Province","Valencia"));
        thirdPartyAddInSpain.appendChild(getElementValue(doc, fileHeader, "CountryCode","ESP"));
        thirdPartyEntity.appendChild(thirdPartyAddInSpain);
        thirdParty.appendChild(thirdPartyEntity);
        
        fileHeader.appendChild(thirdParty);
        
        Element batch = doc.createElement("Batch");
        batch.appendChild(getElementValue(doc, fileHeader, "BatchIdentifier",siteRegNo+invoiceId));
        batch.appendChild(getElementValue(doc, fileHeader, "InvoicesCount","1"));
        
        Element totalInvoicesAmount = doc.createElement("TotalInvoicesAmount");
      //  totalInvoicesAmount.appendChild(getElementValue(doc, fileHeader, "TotalAmount",String.valueOf(twoDf.format(invAmt))));
        totalInvoicesAmount.appendChild(getElementValue(doc, fileHeader, "TotalAmount",utilMethods.getReqDecString(netAmt, 2)));
        batch.appendChild(totalInvoicesAmount);
        
        Element totalOutstandingAmount = doc.createElement("TotalOutstandingAmount");
        totalOutstandingAmount.appendChild(getElementValue(doc, fileHeader, "TotalAmount",utilMethods.getReqDecString(netAmt, 2)));
        batch.appendChild(totalOutstandingAmount);
        
        Element totalExecutableAmount = doc.createElement("TotalExecutableAmount");
        totalExecutableAmount.appendChild(getElementValue(doc, fileHeader, "TotalAmount",utilMethods.getReqDecString(netAmt, 2)));
        batch.appendChild(totalExecutableAmount);
        
        batch.appendChild(getElementValue(doc, fileHeader, "InvoiceCurrencyCode",invoiceISOCode));
        //batch.appendChild(getElementValue(doc, fileHeader, "InvoiceCurrencyCode","EUR"));
        fileHeader.appendChild(batch);
        return fileHeader;
    }
	
	
	
	private static Node getPartiesElement(Document doc,String invoiceId ,Connection conn ) {
		
		String sql="";
		String custCode="",siteCode="",custCodeBil="";
		String siteAreaCode="",siteAdd1="",siteAdd2="",siteAdd3="",sitecity="",sitePin="",siteStateCode="",siteRegNo="",siteContactPersion="";
		String custAdd1="",custAdd2="",custCity="",custState="",custPin="",custTaxreg="",custType="",custName="",custCountrtCode="";
		String rate="",unitRate="",discountInv="",siteDescr="",custCodeBill="",custCodeDlv="";
		String dlvAdd1="",dlvAdd2="",dlvCity="",dlvPin="",dlvCountryCode="",siteStatedescr="",custStatedescr="",custNameBill="",custNameDlv="";
		String custBillAdd1="",custBillAdd2="",custBillAdd3="",custBillCity="",custBillStateCode="",custBillPin="",custBillCounty="";
		String custDlvAdd1="",custDlvAdd2="",custDlvAdd3="",custDlvCity="",custDlvStateCode="",custDlvPin="",custDlvCounty="";
		String custBillStatedescr="",custDlvStatedescr="";
		String custDlvreg_1="",custDlvreg_2="",custDlvreg_3="",custBillreg_1="",custBillreg_2="",custBillreg_3="",custCodereg_1="",custCodereg_2="",custCodereg_3="";
		String nameReg1="",add1Reg1="",add2Reg1="",add3Reg1="",cityReg1="",pinReg1="",countyReg1="",stateCodeReg1="";
		String nameReg2="",add1Reg2="",add2Reg2="",add3Reg2="",cityReg2="",pinReg2="",countyReg2="",stateCodeReg2="";
		String nameReg3="",add1Reg3="",add2Reg3="",add3Reg3="",cityReg3="",pinReg3="",countyReg3="",stateCodeReg3="";
		String compLocation="",sheet="",folio="",volume="",addRegData="",finEntity="";
		String stateDescrReg3="",stateDescrReg2="",stateDescrReg1="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{	
			
			
			
			/*sql = " select i.SITE_CODE,i.CUST_CODE,i.CUST_CODE__BIL,d.cust_code__dlv ,d.DLV_ADD1 " +
					",d.DLV_ADD2 , d.DLV_CITY , d.DLV_PIN ,d.COUNT_CODE__DLV " +
					"from invoice i, DESPATCH d , " +
					"despatchdet det where  i.invoice_id=det.invoice_id " +
					"and d.desp_id=det.desp_id  and det.invoice_id= ? " ;*/
			
			
			sql = " select i.SITE_CODE,i.CUST_CODE,i.CUST_CODE__BIL,d.cust_code__dlv ,d.DLV_ADD1, "+
	               " d.DLV_ADD2 , d.DLV_CITY , d.DLV_PIN ,d.COUNT_CODE__DLV "+
	               " from invoice i, DESPATCH d "+
	               " where i.desp_id=d.desp_id and i.invoice_id=? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				custCode = rs.getString("cust_code");
				siteCode=rs.getString("SITE_CODE");
				custCodeBill=rs.getString("CUST_CODE__BIL");
				custCodeDlv=rs.getString("cust_code__dlv");
				dlvAdd1=rs.getString("DLV_ADD1");
				dlvAdd2=rs.getString("DLV_ADD2");
				dlvCity=rs.getString("DLV_CITY");
				dlvPin=rs.getString("DLV_PIN");
				dlvCountryCode=rs.getString("COUNT_CODE__DLV");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			
			
		/*	sql = "select reg_no from siteregno where site_code=? and REF_CODE='TAXNO1' " ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				siteRegNo = rs.getString("reg_no");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;*/
			
			
			
			sql = "select AREA_CODE,ADD1,ADD2,ADD3,CITY,PIN,STATE_CODE,CONTACT_PERSON,DESCR,FIN_ENTITY from site where SITE_CODE =?" ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				siteAreaCode=rs.getString("AREA_CODE");
				siteAdd1=rs.getString("ADD1");
				siteAdd2=rs.getString("ADD2");
				siteAdd3=rs.getString("ADD3");
				sitecity=rs.getString("CITY");
				sitePin=rs.getString("PIN");
				siteStateCode=rs.getString("STATE_CODE");
				siteContactPersion=rs.getString("CONTACT_PERSON");
				siteDescr=rs.getString("DESCR");
				finEntity=rs.getString("FIN_ENTITY");
				
			
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			finEntity = finEntity == null ? " " : finEntity.trim();
			
			siteRegNo=getSiteRegData(conn,"TAXNO1",siteCode,finEntity);
			System.out.println("siteRegNo @@@@@@@@@@@@"+siteRegNo);
			
			compLocation=getSiteRegData(conn,"REG1",siteCode,finEntity);
			sheet=getSiteRegData(conn,"REG2",siteCode,finEntity);
			folio=getSiteRegData(conn,"REG3",siteCode,finEntity);
			volume=getSiteRegData(conn,"REG4",siteCode,finEntity);
			addRegData=getSiteRegData(conn,"REG5",siteCode,finEntity);
			
			accToBeCredited=getSiteRegData(conn,"ACCTNO",siteCode,finEntity);
			//System.out.println("paymentMeans : "+paymentMeans);
			System.out.println("accToBeCredited : "+accToBeCredited);
			
			accToBeCredited = accToBeCredited == null ? " " : accToBeCredited.trim();
			compLocation = compLocation == null ? " " : compLocation.trim();
			sheet = sheet == null ? " " : sheet.trim();
			folio = folio == null ? " " : folio.trim();
			volume = volume == null ? " " : volume.trim();
			addRegData = addRegData == null ? " " : addRegData.trim();
			
			
			
            sql = "select ADDR1,ADDR2,CITY,STATE_CODE,PIN,cust_type,CUST_NAME,COUNT_CODE,CUST_CODE__BIL,REGISTR_1,REGISTR_2,REGISTR_3 from customer where CUST_CODE=?" ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, custCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				custAdd1 = rs.getString("ADDR1");
				custAdd2 = rs.getString("ADDR2");
				custCity = rs.getString("CITY");
				custState = rs.getString("STATE_CODE");
				custPin = rs.getString("PIN");
				//custTaxreg = rs.getString("TAX_REG_1");
				custType = rs.getString("cust_type");
				custName = rs.getString("CUST_NAME");
				custCountrtCode = rs.getString("COUNT_CODE");
				
				//custCodereg_1 = rs.getString("REGISTR_1");
				//custCodereg_2 = rs.getString("REGISTR_2");
				//custCodereg_3 = rs.getString("REGISTR_3");
			
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			custCodeBill = custCodeBill == null ? " " : custCodeBill.trim();
			custCodeDlv = custCodeDlv == null ? " " : custCodeDlv.trim();
			/*custCodereg_1 = custCodereg_1 == null ? " " : custCodereg_1.trim();
			custCodereg_2 = custCodereg_2 == null ? " " : custCodereg_2.trim();
			custCodereg_3 = custCodereg_3 == null ? " " : custCodereg_3.trim();
			*/
			
		
			//select cust_name from tarodev.customer where cust_code=?
			  sql = "select cust_name,ADDR1,ADDR2,ADDR3,CITY,STATE_CODE,PIN,COUNT_CODE,REGISTR_1,REGISTR_2,REGISTR_3,TAX_REG_1 from customer where CUST_CODE=?" ;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, custCodeBill);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					custNameBill = rs.getString("CUST_NAME");
					custBillAdd1 = rs.getString("ADDR1");
					custBillAdd2 = rs.getString("ADDR2");
					custBillAdd3 = rs.getString("ADDR3");
					custBillCity = rs.getString("CITY");
					custBillStateCode = rs.getString("STATE_CODE");
					custBillPin = rs.getString("PIN");
					custBillCounty = rs.getString("COUNT_CODE");
					custBillreg_1 = rs.getString("REGISTR_1");
					custBillreg_2 = rs.getString("REGISTR_2");
					custBillreg_3 = rs.getString("REGISTR_3");
					custTaxreg = rs.getString("TAX_REG_1");
					
					
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				custBillAdd1 = custBillAdd1 == null ? " " : custBillAdd1.trim();
				custBillAdd2 = custBillAdd2 == null ? " " : custBillAdd2.trim();
				custBillAdd3 = custBillAdd3 == null ? " " : custBillAdd3.trim();
				custBillCity = custBillCity == null ? " " : custBillCity.trim();
				custBillStateCode = custBillStateCode == null ? " " : custBillStateCode.trim();
				custBillPin = custBillPin == null ? " " : custBillPin.trim();
				custBillCounty = custBillCounty == null ? " " : custBillCounty.trim();
				custBillreg_1 = custBillreg_1 == null ? " " : custBillreg_1.trim();
				custBillreg_2 = custBillreg_2 == null ? " " : custBillreg_2.trim();
				custBillreg_3 = custBillreg_3 == null ? " " : custBillreg_3.trim();
				
				
				/*-----------------changed done dated 29-01-2015-------------------------*/
				
				
				
               sql = "select cust_name,ADDR1,ADDR2,ADDR3,CITY,STATE_CODE,PIN,COUNT_CODE from customer where CUST_CODE=?" ;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, custBillreg_1);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					nameReg1 = rs.getString("CUST_NAME");
					add1Reg1 = rs.getString("ADDR1");
					add2Reg1 = rs.getString("ADDR2");
					add3Reg1 = rs.getString("ADDR3");
					cityReg1 = rs.getString("CITY");
					stateCodeReg1 = rs.getString("STATE_CODE");
					pinReg1 = rs.getString("PIN");
					countyReg1 = rs.getString("COUNT_CODE");
					
					
					
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				nameReg1 = nameReg1 == null ? " " : nameReg1.trim();
				add1Reg1 = add1Reg1 == null ? " " : add1Reg1.trim();
				add2Reg1 = add2Reg1 == null ? " " : add2Reg1.trim();
				add3Reg1 = add3Reg1 == null ? " " : add3Reg1.trim();
				cityReg1 = cityReg1 == null ? " " : cityReg1.trim();
				stateCodeReg1 = stateCodeReg1 == null ? " " : stateCodeReg1.trim();
				pinReg1 = pinReg1 == null ? " " : pinReg1.trim();
				countyReg1 = countyReg1 == null ? " " : countyReg1.trim();
				
				
				
				 sql = "select cust_name,ADDR1,ADDR2,ADDR3,CITY,STATE_CODE,PIN,COUNT_CODE from customer where CUST_CODE=?" ;
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, custBillreg_2);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						nameReg2 = rs.getString("CUST_NAME");
						add1Reg2 = rs.getString("ADDR1");
						add2Reg2 = rs.getString("ADDR2");
						add3Reg2 = rs.getString("ADDR3");
						cityReg2 = rs.getString("CITY");
						stateCodeReg2 = rs.getString("STATE_CODE");
						pinReg2 = rs.getString("PIN");
						countyReg2 = rs.getString("COUNT_CODE");
						
						
						
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					
					nameReg2 = nameReg2 == null ? " " : nameReg2.trim();
					add1Reg2 = add1Reg2 == null ? " " : add1Reg2.trim();
					add2Reg2 = add2Reg2 == null ? " " : add2Reg2.trim();
					add3Reg2 = add3Reg2 == null ? " " : add3Reg2.trim();
					cityReg2 = cityReg2 == null ? " " : cityReg2.trim();
					stateCodeReg2 = stateCodeReg2 == null ? " " : stateCodeReg2.trim();
					pinReg2 = pinReg2 == null ? " " : pinReg2.trim();
					countyReg2 = countyReg2 == null ? " " : countyReg2.trim();
					
					
					
					sql = "select cust_name,ADDR1,ADDR2,ADDR3,CITY,STATE_CODE,PIN,COUNT_CODE from customer where CUST_CODE=?" ;
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, custBillreg_3);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						nameReg3 = rs.getString("CUST_NAME");
						add1Reg3 = rs.getString("ADDR1");
						add2Reg3 = rs.getString("ADDR2");
						add3Reg3 = rs.getString("ADDR3");
						cityReg3 = rs.getString("CITY");
						stateCodeReg3 = rs.getString("STATE_CODE");
						pinReg3 = rs.getString("PIN");
						countyReg3 = rs.getString("COUNT_CODE");
						
						
						
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				

					nameReg3 = nameReg3 == null ? " " : nameReg3.trim();
					add1Reg3 = add1Reg3 == null ? " " : add1Reg3.trim();
					add2Reg3 = add2Reg3 == null ? " " : add2Reg3.trim();
					add3Reg3 = add3Reg3 == null ? " " : add3Reg3.trim();
					cityReg3 = cityReg3 == null ? " " : cityReg3.trim();
					stateCodeReg3 = stateCodeReg3 == null ? " " : stateCodeReg3.trim();
					pinReg3 = pinReg3 == null ? " " : pinReg3.trim();
					countyReg3 = countyReg3 == null ? " " : countyReg3.trim();
					
					
				/*-------------------------------------------------------------------------------------*/
            /*    sql = "select cust_name,ADDR1,ADDR2,ADDR3,CITY,STATE_CODE,PIN,COUNT_CODE,REGISTR_1,REGISTR_2,REGISTR_3 from customer where CUST_CODE=?" ;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, custCodeDlv);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					custNameDlv = rs.getString("CUST_NAME");
					custDlvAdd1 = rs.getString("ADDR1");
					custDlvAdd2 = rs.getString("ADDR2");
					custDlvAdd3 = rs.getString("ADDR3");
					custDlvCity = rs.getString("CITY");
					custDlvStateCode = rs.getString("STATE_CODE");
					custDlvPin = rs.getString("PIN");
					custDlvCounty = rs.getString("COUNT_CODE");
					//custDlvreg_1 = rs.getString("REGISTR_1");
					//custDlvreg_2 = rs.getString("REGISTR_2");
					//custDlvreg_3 = rs.getString("REGISTR_3");
					
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			*/	
				custDlvAdd1 = custDlvAdd1 == null ? " " : custDlvAdd1.trim();
				custDlvAdd2 = custDlvAdd2 == null ? " " : custDlvAdd2.trim();
				custDlvAdd3 = custDlvAdd3 == null ? " " : custDlvAdd3.trim();
				custDlvCity = custDlvCity == null ? " " : custDlvCity.trim();
				custDlvStateCode = custDlvStateCode == null ? " " : custDlvStateCode.trim();
				custDlvPin = custDlvPin == null ? " " : custDlvPin.trim();
				custDlvCounty = custDlvCounty == null ? " " : custDlvCounty.trim();
				/*custDlvreg_1 = custDlvreg_1 == null ? " " : custDlvreg_1.trim();
				custDlvreg_2 = custDlvreg_2 == null ? " " : custDlvreg_2.trim();
				custDlvreg_3 = custDlvreg_3 == null ? " " : custDlvreg_3.trim();
			*/	
				

			System.out.println("custNameBill :"+custNameBill);
			System.out.println("custNameDlv :"+custNameDlv);
			
			
			
			
			
			
			
			siteStatedescr=getStateDescr(conn,siteStateCode);
			System.out.println("siteStatedescr "+siteStatedescr);
			stateDescrReg3=getStateDescr(conn,stateCodeReg3);
			System.out.println("stateDescrReg3 "+stateDescrReg3);
			stateDescrReg2=getStateDescr(conn,stateCodeReg2);
			System.out.println("stateDescrReg2 "+stateDescrReg2);
			stateDescrReg1=getStateDescr(conn,stateCodeReg1);
			System.out.println("stateDescrReg1 "+stateDescrReg1);
			custBillStatedescr=getStateDescr(conn,custBillStateCode);
			System.out.println("custBillStatedescr "+custBillStatedescr);
			
			
			
			
			
			custBillStatedescr = custBillStatedescr == null ? " " : custBillStatedescr.trim();
			custDlvStatedescr = custDlvStatedescr == null ? " " : custDlvStatedescr.trim();
			stateDescrReg3 = stateDescrReg3 == null ? " " : stateDescrReg3.trim();
			stateDescrReg2 = stateDescrReg2 == null ? " " : stateDescrReg2.trim();
			stateDescrReg1 = stateDescrReg1 == null ? " " : stateDescrReg1.trim();
		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
		custCode = custCode == null ? " " : custCode.trim();
		siteCode = siteCode == null ? " " : siteCode.trim();
		custCodeBil = custCodeBil == null ? " " : custCodeBil.trim();
		custCodeDlv = custCodeDlv == null ? " " : custCodeDlv.trim();
		dlvAdd1 = dlvAdd1 == null ? " " : dlvAdd1.trim();
		dlvAdd2 = dlvAdd2 == null ? " " : dlvAdd2.trim();
		dlvCity = dlvCity == null ? " " : dlvCity.trim();
		dlvPin = dlvPin == null ? " " : dlvPin.trim();
		dlvCountryCode = dlvCountryCode == null ? " " : dlvCountryCode.trim();
		siteRegNo = siteRegNo == null ? " " : siteRegNo.trim();
		siteAreaCode = siteAreaCode == null ? " " : siteAreaCode.trim();
		siteAdd1 = siteAdd1 == null ? " " : siteAdd1.trim();
		siteAdd2 = siteAdd2 == null ? " " : siteAdd2.trim();
		siteAdd3 = siteAdd3 == null ? " " : siteAdd3.trim();
		sitecity = sitecity == null ? " " : sitecity.trim();
		sitePin = sitePin == null ? " " : sitePin.trim();
		siteStateCode = siteStateCode == null ? " " : siteStateCode.trim();
		siteContactPersion= siteContactPersion == null ? " " : siteContactPersion.trim();
		siteDescr= siteDescr == null ? " " : siteDescr.trim();
		custCountrtCode = custCountrtCode == null ? " " : custCountrtCode.trim();
		custAdd1 = custAdd1 == null ? " " : custAdd1.trim();
		custAdd2 = custAdd2 == null ? " " : custAdd2.trim();
		custCity = custCity == null ? " " : custCity.trim();
		custState = custState == null ? " " : custState.trim();
		custPin = custPin == null ? " " : custPin.trim();
		custTaxreg = custTaxreg == null ? " " : custTaxreg.trim();
		custType = custType == null ? " " : custType.trim();
		custName = custName == null ? " " : custName.trim();
		custCountrtCode = custCountrtCode == null ? " " : custCountrtCode.trim();
		custCodeBill = custCodeBill == null ? " " : custCodeBill.trim();
		siteStatedescr = siteStatedescr == null ? " " : siteStatedescr.trim();
		custStatedescr = custStatedescr == null ? " " : custStatedescr.trim();
		custNameBill = custNameBill == null ? " " : custNameBill.trim();
		custNameDlv = custNameDlv == null ? " " : custNameDlv.trim();
		
		
		System.out.println("custDlvreg_1:"+custDlvreg_1+"@custDlvreg_2:"+custDlvreg_2+"@custDlvreg_3:"+custDlvreg_3);
		System.out.println("custBillreg_1:"+custBillreg_1+"@custBillreg_2:"+custBillreg_2+"@custBillreg_3:"+custBillreg_3);
		System.out.println("custCodereg_1:"+custCodereg_1+"@custCodereg_2:"+custCodereg_2+"@custCodereg_3:"+custCodereg_3);
		
		
        Element parties = doc.createElement("Parties");
       /*-----------------------Element for seller party,start-----------------------------*/
        Element sellerParty = doc.createElement("SellerParty");
        Element taxIdentification = doc.createElement("TaxIdentification");
        taxIdentification.appendChild(getElementValue(doc, parties, "PersonTypeCode", "J"));
        taxIdentification.appendChild(getElementValue(doc, parties, "ResidenceTypeCode", "R"));
        taxIdentification.appendChild(getElementValue(doc, parties, "TaxIdentificationNumber", siteRegNo));
        sellerParty.appendChild(taxIdentification);
        
        Element individual = doc.createElement("LegalEntity");
        individual.appendChild(getElementValue(doc, parties, "CorporateName", siteDescr));
        
        /*----------------------changed done dated 29-jan-2015-----------------------------------------*/
        
        Element registrationData = doc.createElement("RegistrationData");
        registrationData.appendChild(getElementValue(doc, parties, "RegisterOfCompaniesLocation",compLocation));
        registrationData.appendChild(getElementValue(doc, parties, "Sheet",sheet));
        registrationData.appendChild(getElementValue(doc, parties, "Folio",folio));
        registrationData.appendChild(getElementValue(doc, parties, "Volume",volume));
        registrationData.appendChild(getElementValue(doc, parties, "AdditionalRegistrationData",addRegData));
        individual.appendChild(registrationData);
        
        
        /*------------------------------------------------------------------------------------------------*/
        
        Element addressInSpain = doc.createElement("AddressInSpain");
        addressInSpain.appendChild(getElementValue(doc, parties, "Address", siteAdd1+" "+siteAdd2 +" "+siteAdd3));
        addressInSpain.appendChild(getElementValue(doc, parties, "PostCode", sitePin));
        addressInSpain.appendChild(getElementValue(doc, parties, "Town", sitecity));
        addressInSpain.appendChild(getElementValue(doc, parties, "Province", siteStatedescr));
        addressInSpain.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        individual.appendChild(addressInSpain);
        sellerParty.appendChild(individual);
        parties.appendChild(sellerParty);
        /*-----------------------Element for seller party,start-----------------------------*/
        
        
        /*-----------------------Element for buyer party,start-----------------------------*/
        Element buyerParty = doc.createElement("BuyerParty");
        Element taxIdfbuyerParty = doc.createElement("TaxIdentification");
        taxIdfbuyerParty.appendChild(getElementValue(doc, parties, "PersonTypeCode", "J"));
        taxIdfbuyerParty.appendChild(getElementValue(doc, parties, "ResidenceTypeCode", "R"));
        taxIdfbuyerParty.appendChild(getElementValue(doc, parties, "TaxIdentificationNumber", custTaxreg.replaceAll("-","")));
        buyerParty.appendChild(taxIdfbuyerParty);
        
        Element administrativeCentresparent = doc.createElement("AdministrativeCentres");
       
        Element admCustDlv = doc.createElement("AdministrativeCentre");
        admCustDlv.appendChild(getElementValue(doc, parties, "CentreCode", custBillreg_1));
        admCustDlv.appendChild(getElementValue(doc, parties, "RoleTypeCode", "01"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(nameReg1 != "")
        {
        	admCustDlv.appendChild(getElementValue(doc, parties, "Name", nameReg1));
        }
        Element addCustDlv = doc.createElement("AddressInSpain");
        addCustDlv.appendChild(getElementValue(doc, parties, "Address", add1Reg1 + " "+add2Reg1 + " "+add3Reg1));
        addCustDlv.appendChild(getElementValue(doc, parties, "PostCode", pinReg1));
        addCustDlv.appendChild(getElementValue(doc, parties, "Town", cityReg1));
        addCustDlv.appendChild(getElementValue(doc, parties, "Province", stateDescrReg1));
        //addCustDlv.appendChild(getElementValue(doc, parties, "CountryCode", dlvCountryCode));
        addCustDlv.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        admCustDlv.appendChild(addCustDlv);
        administrativeCentresparent.appendChild(admCustDlv);
   
        /*-------------------------------------------------------------------*/
        Element admCustBill = doc.createElement("AdministrativeCentre");
       // if(custCodeBill != "")
      //  {
        	 admCustBill.appendChild(getElementValue(doc, parties, "CentreCode", custBillreg_2));
      //  }
        admCustBill.appendChild(getElementValue(doc, parties, "RoleTypeCode", "02"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(nameReg2 != "")
        {
        	admCustBill.appendChild(getElementValue(doc, parties, "Name", nameReg2));	
        }
        Element addCustBill = doc.createElement("AddressInSpain");
        addCustBill.appendChild(getElementValue(doc, parties, "Address", add1Reg2 + " " + add2Reg2 + " "+add3Reg2));
        addCustBill.appendChild(getElementValue(doc, parties, "PostCode", pinReg2));
        addCustBill.appendChild(getElementValue(doc, parties, "Town", cityReg2));
        addCustBill.appendChild(getElementValue(doc, parties, "Province", stateDescrReg2));
        //addCustBill.appendChild(getElementValue(doc, parties, "CountryCode", custCountrtCode));
        addCustBill.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        admCustBill.appendChild(addCustBill);
        administrativeCentresparent.appendChild(admCustBill);
        
        
         
        Element administrativeCentres = doc.createElement("AdministrativeCentre");
        administrativeCentres.appendChild(getElementValue(doc, parties, "CentreCode", custBillreg_3));
        administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(nameReg3 != "")
        {
        	 administrativeCentres.appendChild(getElementValue(doc, parties, "Name", nameReg3));
        }
        Element addressbuyer = doc.createElement("AddressInSpain");
        addressbuyer.appendChild(getElementValue(doc, parties, "Address", add1Reg3 + " "+add2Reg3 + " "+add3Reg3));
        addressbuyer.appendChild(getElementValue(doc, parties, "PostCode", pinReg3));
        addressbuyer.appendChild(getElementValue(doc, parties, "Town", cityReg3));
        addressbuyer.appendChild(getElementValue(doc, parties, "Province", stateDescrReg3));
       // addressbuyer.appendChild(getElementValue(doc, parties, "CountryCode", custCountrtCode));
        addressbuyer.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        administrativeCentres.appendChild(addressbuyer);
        administrativeCentresparent.appendChild(administrativeCentres);
        
        
        
        buyerParty.appendChild(administrativeCentresparent);
        
        /*------------------------------------------------------------------*/
        Element legalEntity = doc.createElement("LegalEntity");
        legalEntity.appendChild(getElementValue(doc, parties, "CorporateName", custNameBill));
        //legalEntity.appendChild(getElementValue(doc, parties, "TradeName", ""));
        Element addlegalEntity = doc.createElement("AddressInSpain");
        /*addlegalEntity.appendChild(getElementValue(doc, parties, "Address",siteAdd1));
        addlegalEntity.appendChild(getElementValue(doc, parties, "PostCode", sitePin));
        addlegalEntity.appendChild(getElementValue(doc, parties, "Town", sitecity));
        addlegalEntity.appendChild(getElementValue(doc, parties, "Province", siteStatedescr));*/
        addlegalEntity.appendChild(getElementValue(doc, parties, "Address",custBillAdd2));
        addlegalEntity.appendChild(getElementValue(doc, parties, "PostCode", custBillPin));
        addlegalEntity.appendChild(getElementValue(doc, parties, "Town", custBillCity));
        addlegalEntity.appendChild(getElementValue(doc, parties, "Province", custBillStatedescr));
        addlegalEntity.appendChild(getElementValue(doc, parties, "CountryCode","ESP"));
        legalEntity.appendChild(addlegalEntity);
        buyerParty.appendChild(legalEntity);
        
        parties.appendChild(buyerParty);
        /*-----------------------Element for buyer party,end-----------------------------*/
        
        return parties;
    }
	
	
	
	
	
	
	private static Node getInvoiceElement(Document doc,String invoiceId,Connection conn) {
       
		String sql="",purchaseOrd="",saleOrder="",despId="",phyAttrib_10="";
		String custCode="",currencyCode="",itemCode="",itemDescr="",quantity="",unit="";
		String taxCode="",taxClass="",taxBase="",taxChap="",taxEnv="",exchRate="",discAmt="",msg_specs="";
		String currCodeHd="",invoiceISOCode="";
		String itemCodeDet="",itemDescrDet="",quantityDet="",unitDet="",accDet="",instAmtDet="";
		String rate="",unitRate="",discountInv="",taxEffects="",siteCode="",paymentMeans="",docStatus="",custCodeBillHD="";
		double taxBaseAmt=0.00,taxAmtHd=0.00 ,NetAmtHd=0.00,discAmtHd=0.00,invoiceAmtHd=0.00,rateDet=0.00;
		double netAmtDet=0.00,discAmtDet=0.00,taxAmt=0.00,taxPersc=0.00,discountDet=0.00,totalCost=0.0,grossAmount=0.0,totalGrossAmount=0.0;
		double totTaxBaseAmt=0.0,totTaxAmt=0.0;
		double  taxBaseAmtHD =0.0,taxAmtHD =0.0,taxPerscHD=0.0,totTaxBaseAmtHD=0.0,totTaxAmtHD=0.0;
		double taxBaseAmtDet=0.0,taxPerscDet=0.0,taxAmtDet=0.0,totTaxBaseAmtDet=0.0,totTaxAmtDet=0.0;
		int lineNo=0;
		GenericUtility genericUtility = null;
		PreparedStatement pstmt = null,pstmt1=null;
		ResultSet rs = null,rs1=null;
		int cnt=0,taxCnt=0;
		int totalAmt=0,amtWitoutTax=0;
		HashMap<String, String> tempMap = new HashMap<String, String>();
		SimpleDateFormat sdf = null;
		Date invoiceDtHd=null,dueDateHd=null;
		DecimalFormat twoDf = new DecimalFormat("#.00");
		DecimalFormat fourDf = new DecimalFormat("#.0000");
		DecimalFormat sixDf = new DecimalFormat("#.000000");
		UtilMethods utilMethods= new UtilMethods();
		
		try
		{
			System.out.println("getInvoiceElement@@@@@@@@@@@@");
			genericUtility = new GenericUtility();
			//sdf=new SimpleDateFormat("yyyy-mm-dd");
			sdf=new SimpleDateFormat("yyyy-MM-dd");
			
			
			sql = " select INV_AMT,TAX_AMT,DISC_AMT,NET_AMT,CURR_CODE,TRAN_DATE ,SITE_CODE ,SALE_ORDER,DESP_ID,DUE_DATE,CUST_CODE__BIL from invoice " +
					"where invoice_id=?" ;
							
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				invoiceDtHd = rs.getDate("TRAN_DATE");
				invoiceAmtHd = rs.getDouble("INV_AMT");
				taxAmtHd = rs.getDouble("TAX_AMT");
				discAmtHd = rs.getDouble("DISC_AMT");
				NetAmtHd = rs.getDouble("NET_AMT");
				currCodeHd = rs.getString("CURR_CODE");
				siteCode= rs.getString("SITE_CODE");
				//docStatus= rs.getString("DOC_STATUS");
				saleOrder= rs.getString("SALE_ORDER");
				despId= rs.getString("DESP_ID");
				dueDateHd = rs.getDate("DUE_DATE");
				custCodeBillHD = rs.getString("CUST_CODE__BIL");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//2014-03-12
			
			saleOrder = saleOrder == null ? " " : saleOrder.trim();
			despId = despId == null ? " " : despId.trim();
			custCodeBillHD = custCodeBillHD == null ? " " : custCodeBillHD.trim();
			
			
			
			System.out.println("invoiceDtHd : "+invoiceDtHd);
			System.out.println("dueDateHd : "+dueDateHd);
			
			//paymentMeans=getSiteRegvalue(conn,"IBAN",siteCode);
			
			
		
			
			System.out.println("currencyCode : "+currCodeHd);
          sql = "select iso4217 from currency where curr_code=? " ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, currCodeHd);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				invoiceISOCode = rs.getString("iso4217");
												
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("invoiceISOCode : "+invoiceISOCode);
					

			sql = " select ITEM_CODE,ITEM_DESCR,QUANTITY,UNIT,RATE,DISCOUNT,TAX_AMT,NET_AMT ," +
					"DISC_AMT,INST_AMOUNT,ACC_CODE__ITEM,QUANTITY__STDUOM,RATE__STDUOM ,LINE_NO,(select cust_pord from sorder where sale_order=?) as cust_prod from invdet " +
					" where invoice_id=?" ;
							
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, saleOrder);
			pstmt.setString(2, invoiceId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				cnt++;
				totTaxBaseAmtDet=0.0;
				totTaxAmtDet=0.0;
				
				System.out.println("cnt "+cnt);
				itemCodeDet = rs.getString("ITEM_CODE");
				itemDescrDet = rs.getString("ITEM_DESCR");
				//quantityDet = rs.getString("QUANTITY");
				quantityDet = rs.getString("QUANTITY__STDUOM");
				unitDet = rs.getString("UNIT");
				//rateDet = rs.getDouble("RATE");
				rateDet = rs.getDouble("RATE__STDUOM");
				discountDet = rs.getDouble("DISCOUNT");
				//taxAmtDet = rs.getDouble("TAX_AMT");
				netAmtDet = rs.getDouble("NET_AMT");
				discAmtDet = rs.getDouble("DISC_AMT");
				instAmtDet = rs.getString("INST_AMOUNT");
				accDet = rs.getString("ACC_CODE__ITEM");
				purchaseOrd=rs.getString("cust_prod");
				lineNo=rs.getInt("LINE_NO");
				
				totalCost=rateDet * Integer.parseInt(quantityDet);
				System.out.println("totalCost :"+totalCost);
				System.out.println("discAmtDet :"+discAmtDet);
				grossAmount=totalCost - discAmtDet;
				
				totalGrossAmount=totalGrossAmount + grossAmount;
				
				itemCodeDet = itemCodeDet == null ? " " : itemCodeDet.trim();
				itemDescrDet = itemDescrDet == null ? " " : itemDescrDet.trim();
				quantityDet = quantityDet == null ? " " : quantityDet.trim();
				unitDet = unitDet == null ? " " : unitDet.trim();
			//	rateDet = rateDet == null ? " " : rateDet.trim();
				//discountDet = discountDet == null ? " " : discountDet.trim();
				//taxAmtDet = taxAmtDet == null ? " " : taxAmtDet.trim();
				//netAmtDet = netAmtDet == null ? " " : netAmtDet.trim();
			//	discAmtDet = discAmtDet == null ? " " : discAmtDet.trim();
				instAmtDet = instAmtDet == null ? " " : instAmtDet.trim();
				accDet = accDet == null ? " " : accDet.trim();
				purchaseOrd = purchaseOrd == null ? " " : purchaseOrd.trim();
				
				
				/*----------------------------------------------------------------------------*/
				
				/*sql=" select MSG_SPECS from CUSTOMERITEM ci, invoice i, invdet idet" +
				" where ci.cust_code=i.cust_code__bil " +
				" and idet.item_code = ci.item_code and idet.item_code = ? and i.cust_code__bil= ? ";*/
	
				sql = "select replace(MSG_SPECS,'|',' ' ) as MSG_SPECS from CUSTOMERITEM ci,customer c , item i where " +
						" c.cust_code = ci.cust_code and i.item_code = ci.item_code  " +
						" and ci.item_code=? and ci.cust_code=? " ;
								
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, itemCodeDet);
				pstmt1.setString(2, custCodeBillHD);
				rs1 = pstmt1.executeQuery();
				if (rs1.next())
				{
					msg_specs = rs1.getString("MSG_SPECS");
					msg_specs = msg_specs == null ? " " : msg_specs.trim();
					tempMap.put("msg_specs" + cnt, msg_specs);
				}
				else
				{
					tempMap.put("msg_specs" + cnt, "");
				}
				
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
			
				System.out.println("msg_specs :"+msg_specs);
				
				
				
				/*------------------------------------------------------------------------------*/
				
				
				
				
				
				
				
				    sql = "select PHY_ATTRIB_10 from item where item_code= ? " ;
					
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCodeDet);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						phyAttrib_10 = rs1.getString("PHY_ATTRIB_10");
														
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				
					phyAttrib_10 = phyAttrib_10 == null ? " " : phyAttrib_10.trim();
				
				tempMap.put("item_code" + cnt, itemCodeDet);
				tempMap.put("item_descr" + cnt, phyAttrib_10+"-"+itemDescrDet);
				tempMap.put("quantity" + cnt, quantityDet);
				tempMap.put("unit" + cnt, unitDet);
	
				tempMap.put("rate" + cnt, utilMethods.getReqDecString(rateDet, 6));
				tempMap.put("discount" + cnt, utilMethods.getReqDecString(discountDet, 4));
				//tempMap.put("tax_amt" + cnt, utilMethods.getReqDecString(taxAmtDet,2));
				tempMap.put("net_amt" + cnt, utilMethods.getReqDecString(netAmtDet,6));
				tempMap.put("disc_amt" + cnt, utilMethods.getReqDecString(discAmtDet,6));
				tempMap.put("installment_amt" + cnt, instAmtDet);
				tempMap.put("acc_code" + cnt, accDet);
				tempMap.put("total_cost" + cnt, utilMethods.getReqDecString(totalCost,6));
				tempMap.put("gross_amt" + cnt, utilMethods.getReqDecString(grossAmount,6));
				//tempMap.put("tot_gross_amt" + cnt, utilMethods.getReqDecString(totalGrossAmount,2));
				
				
				/*-----------------changes done on 03-02-2014-----------------------------------------------*/
				
				
				
				sql = " select TAX_CODE,TAX_CLASS,TAXABLE_AMT ,TAX_CHAP,TAX_ENV,TAX_AMT,EXCH_RATE ,TAX_PERC,EFFECT " +
						" from taxtran where tran_id= ? and tran_code='S-INV' and tax_amt <> '0' and tax_perc <>'0'  and LINE_NO = ? " ;
						
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, invoiceId);
				pstmt1.setInt(2, lineNo);
				rs1 = pstmt1.executeQuery();
				while(rs1.next())
				{
					taxCnt++;
					//System.out.println("taxCnt :"+taxCnt);
					//taxCode = rs1.getString("TAX_CODE");
					//taxClass = rs1.getString("TAX_CLASS");
					taxBaseAmtDet = rs1.getDouble("TAXABLE_AMT");
					//taxChap = rs1.getString("TAX_CHAP");
					//taxEnv = rs1.getString("TAX_ENV");
					taxAmtDet = rs1.getDouble("TAX_AMT");
					//exchRate = rs1.getString("EXCH_RATE");
					taxPerscDet = rs1.getDouble("TAX_PERC");
					//taxEffects = rs1.getString("EFFECT");
					
					
					System.out.println("taxBaseAmtDet :"+taxBaseAmtDet);
					System.out.println("taxAmtDet :"+taxAmtDet);
					System.out.println("taxPerscDet :"+taxPerscDet);
					
					totTaxBaseAmtDet = totTaxBaseAmtDet + taxBaseAmtDet;
					totTaxAmtDet = totTaxAmtDet + taxAmtDet; 
					
					
					/*tempMap.put("taxBase_amt_det" + cnt, utilMethods.getReqDecString(taxBaseAmt,2));
					tempMap.put("tax_amt_det" + cnt, utilMethods.getReqDecString(taxAmt,2));
					tempMap.put("tax_persc_det" + cnt, utilMethods.getReqDecString(taxPersc,2));*/
					tempMap.put("tax_persc_det" + cnt, utilMethods.getReqDecString(taxPerscDet,2));
					tempMap.put("totTaxBaseAmtDet" + cnt, utilMethods.getReqDecString(totTaxBaseAmtDet,2));
					tempMap.put("totTaxAmtDet" + cnt, utilMethods.getReqDecString(totTaxAmtDet,2));
					
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
				
				
				
				/*------------------------------------------------------------------------------------------------------*/
				
				
								
				
								
			}//end of while loop
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			

			
			
			sql = " select TAX_CODE,TAX_CLASS,TAXABLE_AMT ,TAX_CHAP,TAX_ENV,TAX_AMT,EXCH_RATE ,TAX_PERC,EFFECT " +
					" from taxtran where tran_id= ? and tran_code='S-INV' and tax_amt <> '0' and tax_perc <>'0' " ;
					
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, invoiceId);
			rs1 = pstmt1.executeQuery();
			while(rs1.next())
			{
				//taxCnt++;
				System.out.println("taxCnt :"+taxCnt);
				//taxCode = rs1.getString("TAX_CODE");
				//taxClass = rs1.getString("TAX_CLASS");
				taxBaseAmtHD = rs1.getDouble("TAXABLE_AMT");
				//taxChap = rs1.getString("TAX_CHAP");
				//taxEnv = rs1.getString("TAX_ENV");
				taxAmtHD = rs1.getDouble("TAX_AMT");
				//exchRate = rs1.getString("EXCH_RATE");
				taxPerscHD = rs1.getDouble("TAX_PERC");
				//taxEffects = rs1.getString("EFFECT");
				
				
				System.out.println("taxBaseAmtHD :"+taxBaseAmtHD);
				System.out.println("taxAmtHD :"+taxAmtHD);
				System.out.println("taxPerscHD :"+taxPerscHD);
				
				totTaxBaseAmtHD = totTaxBaseAmtHD + taxBaseAmtHD;
				totTaxAmtHD = totTaxAmtHD + taxAmtHD; 
				
				/*
				tempMap.put("taxBase_amt" + taxCnt, utilMethods.getReqDecString(taxBaseAmt,2));
				tempMap.put("tax_amt" + taxCnt, utilMethods.getReqDecString(taxAmt,2));
				tempMap.put("tax_persc" + taxCnt, utilMethods.getReqDecString(taxPersc,2));
				*/
				
			}
			rs1.close();
			rs1 = null;
			pstmt1.close();
			pstmt1 = null;
			
			currCodeHd = currCodeHd == null ? " " : currCodeHd.trim();
			invoiceISOCode = invoiceISOCode == null ? " " : invoiceISOCode.trim();
			paymentMeans = paymentMeans == null ? " " : paymentMeans.trim();
			accToBeCredited = accToBeCredited == null ? " " : accToBeCredited.trim();
			taxCode = taxCode == null ? " " : taxCode.trim();
			taxClass = taxClass == null ? " " : taxClass.trim();
			//taxBaseAmt = taxBaseAmt == null ? " " : taxBaseAmt.trim();
			taxChap = taxChap == null ? " " : taxChap.trim();
			taxEnv = taxEnv == null ? " " : taxEnv.trim();
			//taxAmt = taxAmt == null ? " " : taxAmt.trim();
			exchRate = exchRate == null ? " " : exchRate.trim();
			//taxPersc = taxPersc == null ? " " : taxPersc.trim();
			taxEffects= taxEffects == null ? " " : taxEffects.trim();
			docStatus= docStatus == null ? " " : docStatus.trim();
			
			
		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
		
		System.out.println("***************InvoiceHeader***********************");
		Element invoiceParent = doc.createElement("Invoices");
		Element invoice = doc.createElement("Invoice");
		
		Element invoiceHeader = doc.createElement("InvoiceHeader");
		invoiceHeader.appendChild(getElementValue(doc, invoiceParent, "InvoiceNumber", invoiceId));
		invoiceHeader.appendChild(getElementValue(doc, invoiceParent, "InvoiceDocumentType","FC" ));
		invoiceHeader.appendChild(getElementValue(doc, invoiceParent, "InvoiceClass", "OO"));
		invoice.appendChild(invoiceHeader);
		
		Element invoiceIssueData = doc.createElement("InvoiceIssueData");
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "IssueDate",sdf.format(invoiceDtHd)));
	/*	
		//removed on dated 29-jan-2015
		Element invoicingPeriod = doc.createElement("InvoicingPeriod");
		invoicingPeriod.appendChild(getElementValue(doc, invoiceParent, "StartDate",sdf.format(invoiceDtHd)));
		invoicingPeriod.appendChild(getElementValue(doc, invoiceParent, "EndDate",sdf.format(invoiceDtHd)));
		invoiceIssueData.appendChild(invoicingPeriod);
		
	*/	
		
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "InvoiceCurrencyCode", invoiceISOCode));
		//invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "InvoiceCurrencyCode", "EUR"));
		//invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "TaxCurrencyCode", "EUR"));
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "TaxCurrencyCode", invoiceISOCode));
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "LanguageName", "es"));
		invoice.appendChild(invoiceIssueData);
		
		System.out.println("***************TaxesOutputs***(((((****************");
		Element taxesOutputs = doc.createElement("TaxesOutputs");
		Element tax = doc.createElement("Tax");
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));
		System.out.println("test  000");
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate", utilMethods.getReqDecString(taxPerscHD,2)));
		Element taxableTotAmtHD = doc.createElement("TaxableBase");
		taxableTotAmtHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",utilMethods.getReqDecString(totTaxBaseAmtHD,2)));
		tax.appendChild(taxableTotAmtHD);
		Element taxAmounttHd = doc.createElement("TaxAmount");		
		taxAmounttHd.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",utilMethods.getReqDecString(totTaxAmtHD,2)));
		tax.appendChild(taxAmounttHd);
		//tax.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurcharge", ""));
		
		/*Element equivalenceSurcharge = doc.createElement("EquivalenceSurchargeAmount");
		equivalenceSurcharge.appendChild(getElementValue(doc, invoiceParent, "TotalAmount", "0.00"));
		tax.appendChild(equivalenceSurcharge);
		*/
		
		taxesOutputs.appendChild(tax);
		invoice.appendChild(taxesOutputs);
		
		
/*		Element taxesOutputs = doc.createElement("TaxesOutputs");
		System.out.println("taxCnt#########@@@@@"+taxCnt);
		for(int j = 1; j <= taxCnt ;j++)
		{
			
			System.out.println("fo header !!!!!"+taxCnt);
			Element tax = doc.createElement("Tax");
			tax.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));
			System.out.println("test  000");
			//tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate", utilMethods.getReqDecString(taxPersc,2)));
			tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate",(String) tempMap.get("tax_persc" + j)));
			Element taxableTotAmtHD = doc.createElement("TaxableBase");
			//taxableTotAmtHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",utilMethods.getReqDecString(taxBaseAmt,2)));
			taxableTotAmtHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",(String) tempMap.get("taxBase_amt" + j)));
			tax.appendChild(taxableTotAmtHD);
		
			Element taxAmounttHd = doc.createElement("TaxAmount");
			taxAmounttHd.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",(String) tempMap.get("tax_amt" + j)));
			tax.appendChild(taxAmounttHd);
			
			Element equivalenceSurcharge = doc.createElement("EquivalenceSurchargeAmount");
			equivalenceSurcharge.appendChild(getElementValue(doc, invoiceParent, "TotalAmount", "0.00"));
			tax.appendChild(equivalenceSurcharge);
			taxesOutputs.appendChild(tax);
		}
		invoice.appendChild(taxesOutputs);*/
		
		/*Element taxesWithheld = doc.createElement("TaxesWithheld");
		Element taxHeld = doc.createElement("Tax");
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode","01" ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxRate","0.00" ));
		Element TaxableBaseHD = doc.createElement("TaxableBase");
		TaxableBaseHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount","0.00"));
		taxHeld.appendChild(TaxableBaseHD);
		
		Element TaxAmountWithheld = doc.createElement("TaxAmount");
		TaxAmountWithheld.appendChild(getElementValue(doc, invoiceParent, "TotalAmount","0.00"));
		taxHeld.appendChild(TaxAmountWithheld);
		
		taxesWithheld.appendChild(taxHeld);
		invoice.appendChild(taxesWithheld);
		*/
		
		System.out.println("totalGrossAmount :"+totalGrossAmount);
		
		Element invoiceTotals = doc.createElement("InvoiceTotals");
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGrossAmount",utilMethods.getReqDecString(totalGrossAmount,2)));
		Element generalDiscounts = doc.createElement("GeneralDiscounts");
		Element discount = doc.createElement("Discount");
		//discount.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Sales Promotions"));
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Descuentos Varios"));
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", utilMethods.getReqDecString(discAmtHd,6)));
		generalDiscounts.appendChild(discount);
		invoiceTotals.appendChild(generalDiscounts);
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGeneralDiscounts", utilMethods.getReqDecString(discAmtHd,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGeneralSurcharges", "0.00"));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGrossAmountBeforeTaxes", utilMethods.getReqDecString(totalGrossAmount,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxOutputs",utilMethods.getReqDecString(taxAmtHd,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxesWithheld","0.00" ));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "InvoiceTotal",utilMethods.getReqDecString(NetAmtHd,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalOutstandingAmount",utilMethods.getReqDecString(NetAmtHd,2)));
		
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalExecutableAmount",utilMethods.getReqDecString(NetAmtHd,2)));
		invoice.appendChild(invoiceTotals);
		System.out.println("cnt !!!!!! "+cnt);
		
		
		Element items = doc.createElement("Items");
		System.out.println("cnt@@@@@"+cnt);
		for(int i = 1; i<= cnt ; i++)
		{
		
		//
			System.out.println("inside for loop for details!!!");
			System.out.println("ItemDescription :"+i+": "+(String) tempMap.get("item_descr" + i));
			System.out.println("quantity :"+i+": "+(String) tempMap.get("quantity" + i));
			System.out.println("rate :"+i+": "+(String) tempMap.get("rate" + i));
			System.out.println("net_amt :"+i+": "+(String) tempMap.get("net_amt" + i));
			System.out.println("discount :"+i+": "+(String) tempMap.get("discount" + i));
			System.out.println("disc_amt :"+i+": "+(String) tempMap.get("disc_amt" + i));
			System.out.println("TaxRate :"+i+": "+utilMethods.getReqDecString(taxPersc,2));
			System.out.println("TaxableBase :"+i+": "+utilMethods.getReqDecString(taxBaseAmt,2));
			System.out.println("TaxAmount :"+i+": "+(String) tempMap.get("tax_amt" + i));
			System.out.println("total cost :"+i+": "+(String) tempMap.get("total_cost" + i));
			System.out.println("msg_specs :"+i+": "+(String) tempMap.get("msg_specs" + i));
			
			
			
		Element invoiceLine = doc.createElement("InvoiceLine");
		/*---------------------------------------------------------------------------------------------------------------------*/
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "ReceiverTransactionReference",purchaseOrd));
		Element deliveryNotesReferences = doc.createElement("DeliveryNotesReferences");
		Element deliveryNote = doc.createElement("DeliveryNote");
		deliveryNote.appendChild(getElementValue(doc, invoiceParent, "DeliveryNoteNumber", despId));
		deliveryNotesReferences.appendChild(deliveryNote);
		invoiceLine.appendChild(deliveryNotesReferences);
		/*----------------------------------------------------------------------------------------------------------------------*/
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "ItemDescription", (String) tempMap.get("item_descr" + i)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "Quantity", (String) tempMap.get("quantity" + i)));
		//invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitOfMeasure", (String) tempMap.get("unit" + i)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitOfMeasure", "01"));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitPriceWithoutTax",(String) tempMap.get("rate" + i)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "TotalCost",(String) tempMap.get("total_cost" + i)));
		
		Element discountsAndRebates = doc.createElement("DiscountsAndRebates");
		Element discountItem = doc.createElement("Discount");
		//discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Sales Promotions"));
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Descuentos varios"));
		if(!(tempMap.get("disc_amt" + cnt).equalsIgnoreCase("0.0")))
		{
		
			discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountRate", (String) tempMap.get("discount" + i)));
		}
		
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", (String) tempMap.get("disc_amt" + i)));
		discountsAndRebates.appendChild(discountItem);
		invoiceLine.appendChild(discountsAndRebates);
		
		/*Element charges = doc.createElement("Charges");
		Element charge = doc.createElement("Charge");
		charge.appendChild(getElementValue(doc, invoiceParent, "ChargeReason", ""));
		charge.appendChild(getElementValue(doc, invoiceParent, "ChargeRate", "0.00"));
		charge.appendChild(getElementValue(doc, invoiceParent, "ChargeAmount", "0.00"));
		charges.appendChild(charge);
		invoiceLine.appendChild(charges);*/
		
		
		
		
		
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "GrossAmount", (String) tempMap.get("gross_amt" + i)));
		
		
		
		
	/*	Element taxesWithheldItem = doc.createElement("TaxesWithheld");
		Element taxHeldItem = doc.createElement("Tax");
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));saleOrder+"/"+
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxRate", "0.00"));
		Element taxableTotAmt = doc.createElement("TaxableBase");
		taxableTotAmt.appendChild(getElementValue(doc, invoiceParent, "TotalAmount","0.00"));
		taxHeldItem.appendChild(taxableTotAmt);
		
		Element taxAmount1 = doc.createElement("TaxAmount");
		taxAmount1.appendChild(getElementValue(doc, invoiceParent, "TotalAmount", "0.00"));
		taxHeldItem.appendChild(taxAmount1);
		saleOrder+"/"+
		taxesWithheldItem.appendChild(taxHeldItem);
		invoiceLine.appendChild(taxesWithheldItem);*/
		
		
	
		Element taxesOutputsItem = doc.createElement("TaxesOutputs");
		Element taxItem = doc.createElement("Tax");
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxRate",(String) tempMap.get("tax_persc_det" + i)));
		Element taxableTotAmtOutputs = doc.createElement("TaxableBase");
		taxableTotAmtOutputs.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",(String) tempMap.get("totTaxBaseAmtDet" + i)));
		taxItem.appendChild(taxableTotAmtOutputs);
		Element taxAmount2 = doc.createElement("TaxAmount");
		taxAmount2.appendChild(getElementValue(doc, invoiceParent, "TotalAmount", (String) tempMap.get("totTaxAmtDet" + i)));
		taxItem.appendChild(taxAmount2);		
		taxesOutputsItem.appendChild(taxItem);
		invoiceLine.appendChild(taxesOutputsItem);
		
	/*	System.out.println("taxCnt!!!!!@@@@@"+taxCnt);
		Element taxesOutputs1 = doc.createElement("TaxesOutputs");
		for(int j = 1; j <= taxCnt ;j++)
		{
			System.out.println("inside for loop!!!!!");
			System.out.println("inside for loop taxCnt!!!!!"+taxCnt);
			Element tax = doc.createElement("Tax");
			tax.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));
			System.out.println("test  000");
			//tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate", utilMethods.getReqDecString(taxPersc,2)));
			tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate",(String) tempMap.get("tax_persc" + j)));
			Element taxableTotAmtHD = doc.createElement("TaxableBase");
			//taxableTotAmtHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",utilMetsaleOrder+"/"+hods.getReqDecString(taxBaseAmt,2)));
			taxableTotAmtHD.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",(String) tempMap.get("taxBase_amt" + j)));
			tax.appendChild(taxableTotAmtHD);
		
			Element taxAmounttHd = doc.createElement("TaxAmount");
			taxAmounttHd.appendChild(getElementValue(doc, invoiceParent, "TotalAmount",(String) tempMap.get("tax_amt" + j)));
			tax.appendChild(taxAmounttHd);
			
			Element equivalenceSurcharge = doc.createElement("EquivalenceSurchargeAmount");
			equivalenceSurcharge.appendChild(getElementValue(doc, invoiceParent, "TotalAmount", "0.00"));
			tax.appendChild(equivalenceSurcharge);
			taxesOutputs1.appendChild(tax);
		}
		invoiceLine.appendChild(taxesOutputs1);*/
		
		
		
			
		
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "AdditionalLineItemInformation", "Nº Pedido:"+purchaseOrd+"- Nº Albarán:"+despId+"- Nº Expediente:"+(String) tempMap.get("msg_specs" + i)));
		
		
		items.appendChild(invoiceLine);
	//	
		
		}//end of for loop
		invoice.appendChild(items);
		
		System.out.println("===PaymentDetails@@@@@@====");
		Element paymentDetails = doc.createElement("PaymentDetails");
		Element installment = doc.createElement("Installment");
		if(dueDateHd != null)
		{
			installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentDueDate", sdf.format(dueDateHd)));
		}
		else
		{
			installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentDueDate", ""));
		}
		
		installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentAmount",utilMethods.getReqDecString(NetAmtHd,2)));
		installment.appendChild(getElementValue(doc, invoiceParent, "PaymentMeans", "04"));
		Element accountToBeCredited = doc.createElement("AccountToBeCredited");
		accountToBeCredited.appendChild(getElementValue(doc, invoiceParent, "IBAN",accToBeCredited.replaceAll("-","")));
		
		installment.appendChild(accountToBeCredited);
		paymentDetails.appendChild(installment);
		invoice.appendChild(paymentDetails);
		
		
		/*-----------------added on dated 29-jan-2015--------------------------*/
		Element additionalData = doc.createElement("AdditionalData");
		
		additionalData.appendChild(getElementValue(doc, invoiceParent, "InvoiceAdditionalInformation", "Nº Pedido:"+purchaseOrd+"- Nº Albarán:"+despId+"- Nº Expediente:"+(String) tempMap.get("msg_specs1")));
		invoice.appendChild(additionalData);
		/*--------------------------------------------------------------------------------------------*/
		
		invoiceParent.appendChild(invoice);
		
        return invoiceParent;
    }
	
	
	
	
	
	
	// utility method to create text node
    private static Node getElementValue(Document doc, Element element, String name, String value) {
        Element node = doc.createElement(name);
        node.appendChild(doc.createTextNode(value));
        return node;
    }
    
    
    /*public static String getSiteRegvalue(Connection conn,String col1,String siteCode)
    {
    	String result="",sql="";
    	PreparedStatement pstmt = null;
		ResultSet rs = null;
    	try
    	{
            sql = "select reg_no from siteregno where site_code=? and REF_CODE=?" ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, col1);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				result = rs.getString("reg_no");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
    	}
    	catch(Exception e)
    	{
    		System.out.println(e.getMessage());
    	}
    	
    	return result;
    }*/
    
    
    public static String getStateDescr(Connection conn,String col1)
    {
    	String result="",sql="";
    	PreparedStatement pstmt = null;
		ResultSet rs = null;
    	try
    	{
    		sql = "select descr from state where state_code=? " ;
  			
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, col1);
  			rs = pstmt.executeQuery();
  			if (rs.next())
  			{
  				result = rs.getString("descr");
  				
  			}
  			rs.close();
  			rs = null;
  			pstmt.close();
  			pstmt = null;
  			
    	}
    	catch(Exception e)
    	{
    		System.out.println(e.getMessage());
    	}
    	
    	return result;
    }
    
    
    
    /*------------added by mahendra dated 24-DEC-2014 for file upload-------------------------*/
   
    
    private  String putFile(String username, String host, String password,String port, String remotefile, String localfile)
	{
		JSch jsch = new JSch();
		Session session = null;
		try {
			System.out.println("inside putFile ...........");
			System.out.println("username :"+username);
			System.out.println("host :"+host);
			System.out.println("password :"+password);
			System.out.println("port :"+port);
			System.out.println("remotefile :"+remotefile);
			System.out.println("localfile :"+localfile);
			//getSession(String username, String host, int port) 
			session = jsch.getSession(username, host, Integer.parseInt(port));
			session.setConfig("StrictHostKeyChecking", "no");
			session.setPassword(password);
			session.connect();			
			Channel channel = session.openChannel("sftp");
			channel.connect();
			System.out.println("channel success...........");
			ChannelSftp sftpChannel = (ChannelSftp) channel;
			System.out.println("process starts for upload localfile to remote path.......");
			sftpChannel.put(localfile, remotefile);
			System.out.println("The first file is uploaded successfully.");
			sftpChannel.exit();
			session.disconnect();
			return "success";
		} catch (JSchException e) {
			e.printStackTrace();
			return "false";
		} catch (SftpException e) {
			e.printStackTrace();
			return "false";
		}
	}
    
    
	private void setSFTPConfig()
	{
		CommonConstants objCommon =new CommonConstants();
		GenericUtility genericUtility=GenericUtility.getInstance();
		String fileName = null,xmlString = "";
		Document dom=null;
		String sCurrentLine = "";
		//String xmlFile="DDSalesConfig";
		System.out.println("  in setSFTPConfig -------------------------");
		//String xmlFileName = CommonConstants.APPLICATION_CONTEXT + "setting" + File.separator + "DDSalesConfig.xml";
	//	String xmlFileName = CommonConstants.APPLICATION_CONTEXT + "setting" + File.separator + "DDSalesConfig.xml";
		//fileName = ".." + File.separator + "setting" + File.separator + xmlFile + ".xml";
		StringBuilder sb = new StringBuilder();
	//	System.out.println("xmlFileName-->>["+xmlFileName+"]");          
		try 
		{ 
			
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			dom = db.parse("/IBASEHOME/ibase.xml");
			this.invRmFilePath = dom.getElementsByTagName("INV_SFTP_PATH").item(0).getFirstChild().getNodeValue();
			System.out.println("invRmFilePath  :::: "+invRmFilePath);
			this.invLocFilePath = dom.getElementsByTagName("INV_EXPORT_PATH").item(0).getFirstChild().getNodeValue();
			System.out.println("invLocFilePath  :::: "+invLocFilePath);
			this.invBackupFilePath = dom.getElementsByTagName("INV_BACK_PATH").item(0).getFirstChild().getNodeValue();
			System.out.println("invBackupFilePath  :::: "+invBackupFilePath);
			this.invHost = dom.getElementsByTagName("INV_SFTP_HOST").item(0).getFirstChild().getNodeValue();
			System.out.println("invHost  :::: "+invHost);
			this.invUser = dom.getElementsByTagName("INV_SFTP_USER").item(0).getFirstChild().getNodeValue();
			System.out.println("invUser  :::: "+invUser);
			this.invPwd = dom.getElementsByTagName("INV_SFTP_PWD").item(0).getFirstChild().getNodeValue();
			System.out.println("invPwd  :::: "+invPwd);
			this.invPort = dom.getElementsByTagName("INV_SFTP_PORT").item(0).getFirstChild().getNodeValue();
			System.out.println("invPort  :::: "+invPort);
			 
			
			/*
			BufferedReader br = new BufferedReader(new FileReader(xmlFileName));
			while (( sCurrentLine = br.readLine()) != null)
			{
				sb.append(sCurrentLine);
			}
			xmlString =sb.toString();
			System.out.println("DDSalesConfig xmlString---->>["+xmlString+"]");            	 
			dom = genericUtility.parseString(xmlString);                 
			if(dom != null)
			{                	
				this.xmlFileNameUpd = genericUtility.getColumnValue("STOCK_TEMP_UPD_PATH",dom);               	 
				this.hostName = genericUtility.getColumnValue("SFTP_HOST",dom);
				this.username = genericUtility.getColumnValue("SFTP_USER",dom);
				this.password = genericUtility.getColumnValue("SFTP_PWD",dom);					
			}*/                 
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}      
	}

	 public void moveUploadFile(File src, File dst) throws IOException {
		    InputStream in = new FileInputStream(src);
		    OutputStream out = new FileOutputStream(dst);

		    // Transfer bytes from in to out
		    byte[] buf = new byte[1024];
		    int len;
		    while ((len = in.read(buf)) > 0) {
		        out.write(buf, 0, len);
		    }
		    in.close();
		    out.close();
		    System.out.println("File is copied successful!");
		    src.delete();
		    
 	    System.out.println("original file delete successful !");
		}

	 
	 
	 
	 public static String getSiteRegData(Connection conn,String col1,String siteCode,String col2)
	    {
	    	String result="",sql="";
	    	PreparedStatement pstmt = null;
			ResultSet rs = null;
	    	try
	    	{
	            sql = "select reg_no from siteregno where site_code=? and REF_CODE=? and fin_entity=? " ;
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCode);
				pstmt.setString(2, col1);
				pstmt.setString(3, col2);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					result = rs.getString("reg_no");
					
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
	    	}
	    	catch(Exception e)
	    	{
	    		System.out.println(e.getMessage());
	    	}
	    	
	    	return result;
	    }
    
    
    /*----------------------------------------------------------------------------------------------*/
    
    
    
	private String checkNull(String input)
	{
		if (input == null)
		{
			input = "";
		}
		return input;
	}
    
    
    
    
    
  

}
