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.sys.UtilMethods;


public class GenInvoiceSchedule implements Schedule  {
	
	

	@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="";
		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="",;
		*/
		
		
		
		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();
					}
								
				}
			}
			System.out.println("site code parameter "+siteCode);
			
			invoiceIdList.clear();
			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=? ");
			pstmt.setString(1,siteCode);
			rs = pstmt.executeQuery();
			while (rs.next()) 
			{
				invoiceIdList.add(rs.getString("INVOICE_ID"));
			}
			System.out.println(">>>>>>>>For siteCode :"+siteCode+" of invoiceIdList for invoice :"+invoiceIdList);
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
			/*----------------------------------------------------------------------------------------------------------------*/
			
			for (int i = 0; i < invoiceIdList.size(); i++) 
			{
				invoiceId = invoiceIdList.get(i);
				System.out.println(invoiceId);
				retString = generateInvoiceXML(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!!!");
					}
					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 invoiceId,Connection conn)throws RemoteException, ITMException
	{
		
		String errString="",filepath="";
		ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
	    try
	    {
	    	
	    	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=filepath+"/export/facturae"+invoiceId+".xml";
			filepath=directory+"/invoice_"+invoiceId.trim()+".xml";
			System.out.println("filepath :"+filepath);
	    	DocumentBuilderFactory icFactory = DocumentBuilderFactory.newInstance();
	        DocumentBuilder icBuilder;
	        
	        icBuilder = icFactory.newDocumentBuilder();
            Document document = icBuilder.newDocument();
            Element mainRootElement = document.createElementNS("http://www.facturae.es/Facturae/2009/v3.2/Facturae", "Facture");
            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("", "VTTRINERR", "");
			return errString;

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

        }
        catch(Exception e)
        {
        	e.printStackTrace();
        	  System.out.println("Exception :"+e.getMessage());
        	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR", "");
			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="";
		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 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");
								
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
			
            sql = "select iso4217 from TARODEV.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();
			
		}
		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", "EM"));
        Element batch = doc.createElement("Batch");
        batch.appendChild(getElementValue(doc, fileHeader, "BatchIdentifier",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(invAmt, 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="",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="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{	
			/*sql = "select SITE_CODE,CUST_CODE,CUST_CODE__BIL from invoice where 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");
				custCodeBil=rs.getString("CUST_CODE__BIL");
			
				System.out.println("custCode  "+custCode);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			*/
			
			
			/*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");
				custCodeBil=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='TAXNO' " ;
			
			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;
			System.out.println("siteRegNo @@@@@@@@@@@@"+siteRegNo);
			
			sql = "select AREA_CODE,ADD1,ADD2,CITY,PIN,STATE_CODE,CONTACT_PERSON,DESCR 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");
				sitecity=rs.getString("CITY");
				sitePin=rs.getString("PIN");
				siteStateCode=rs.getString("STATE_CODE");
				siteContactPersion=rs.getString("CONTACT_PERSON");
				siteDescr=rs.getString("DESCR");
				
			
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
			
            sql = "select ADDR1,ADDR2,CITY,STATE_CODE,PIN,pan_no,cust_type,CUST_NAME,COUNT_CODE,CUST_CODE__BIL,TAX_REG_1 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");
				custCodeBill = rs.getString("CUST_CODE__BIL");
			
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			
			
			
		}
		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();
		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();
		
		
		
		
		
        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));
        Element addressInSpain = doc.createElement("AddressInSpain");
        addressInSpain.appendChild(getElementValue(doc, parties, "Address", siteAdd1));
        addressInSpain.appendChild(getElementValue(doc, parties, "PostCode", sitePin));
        addressInSpain.appendChild(getElementValue(doc, parties, "Town", sitecity));
        addressInSpain.appendChild(getElementValue(doc, parties, "Province", siteStateCode));
        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));
        buyerParty.appendChild(taxIdfbuyerParty);
        
        Element administrativeCentresparent = doc.createElement("AdministrativeCentres");
       
        Element admCustDlv = doc.createElement("AdministrativeCentres");
        admCustDlv.appendChild(getElementValue(doc, parties, "CentreCode", custCodeDlv));
        admCustDlv.appendChild(getElementValue(doc, parties, "RoleTypeCode", "01"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(custName != "")
        {
        	admCustDlv.appendChild(getElementValue(doc, parties, "Name", custName));
        }
        Element addCustDlv = doc.createElement("AddressInSpain");
        addCustDlv.appendChild(getElementValue(doc, parties, "Address", dlvAdd1));
        addCustDlv.appendChild(getElementValue(doc, parties, "PostCode", dlvPin));
        addCustDlv.appendChild(getElementValue(doc, parties, "Town", dlvCity));
        addCustDlv.appendChild(getElementValue(doc, parties, "Province", custState));
        //addCustDlv.appendChild(getElementValue(doc, parties, "CountryCode", dlvCountryCode));
        addCustDlv.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        admCustDlv.appendChild(addCustDlv);
        administrativeCentresparent.appendChild(admCustDlv);
   
        /*-------------------------------------------------------------------*/
        Element admCustBill = doc.createElement("AdministrativeCentres");
        if(custCodeBill != "")
        {
        	 admCustBill.appendChild(getElementValue(doc, parties, "CentreCode", custCodeBill));
        }
        admCustBill.appendChild(getElementValue(doc, parties, "RoleTypeCode", "02"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(custName != "")
        {
        	admCustBill.appendChild(getElementValue(doc, parties, "Name", custName));	
        }
        Element addCustBill = doc.createElement("AddressInSpain");
        addCustBill.appendChild(getElementValue(doc, parties, "Address", custAdd1));
        addCustBill.appendChild(getElementValue(doc, parties, "PostCode", custPin));
        addCustBill.appendChild(getElementValue(doc, parties, "Town", custCity));
        addCustBill.appendChild(getElementValue(doc, parties, "Province", custState));
        //addCustBill.appendChild(getElementValue(doc, parties, "CountryCode", custCountrtCode));
        addCustBill.appendChild(getElementValue(doc, parties, "CountryCode", "ESP"));
        admCustBill.appendChild(addCustBill);
        administrativeCentresparent.appendChild(admCustBill);
        
        
         
        Element administrativeCentres = doc.createElement("AdministrativeCentres");
        administrativeCentres.appendChild(getElementValue(doc, parties, "CentreCode", custCode));
        administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        if(custName != "")
        {
        	 administrativeCentres.appendChild(getElementValue(doc, parties, "Name", custName));
        }
        Element addressbuyer = doc.createElement("AddressInSpain");
        addressbuyer.appendChild(getElementValue(doc, parties, "Address", custAdd1));
        addressbuyer.appendChild(getElementValue(doc, parties, "PostCode", custPin));
        addressbuyer.appendChild(getElementValue(doc, parties, "Town", custCity));
        addressbuyer.appendChild(getElementValue(doc, parties, "Province", custState));
       // 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", siteDescr));
        //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", siteStateCode));
        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="";
		String custCode="",currencyCode="",itemCode="",itemDescr="",quantity="",unit="";
		String taxCode="",taxClass="",taxBase="",taxChap="",taxEnv="",exchRate="",discAmt="";
		String currCodeHd="",invoiceISOCode="";
		String itemCodeDet="",itemDescrDet="",quantityDet="",unitDet="",accDet="",instAmtDet="";
		String rate="",unitRate="",discountInv="",taxEffects="",siteCode="",paymentMeans="",accToBeCredited="",docStatus="";
		double taxBaseAmt=0.00,taxAmtHd=0.00 ,NetAmtHd=0.00,discAmtHd=0.00,invoiceAmtHd=0.00,rateDet=0.00;
		double taxAmtDet=0.00,netAmtDet=0.00,discAmtDet=0.00,taxAmt=0.00,taxPersc=0.00,discountDet=0.00;
		GenericUtility genericUtility = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt=0;
		int totalAmt=0,amtWitoutTax=0;
		HashMap<String, String> tempMap = new HashMap<String, String>();
		SimpleDateFormat sdf = null;
		Date invoiceDtHd=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 ,DOC_STATUS 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");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//2014-03-12
			
			System.out.println("invoiceDtHd : "+invoiceDtHd);
			
			paymentMeans=getSiteRegvalue(conn,"IBAN",siteCode);
			accToBeCredited=getSiteRegvalue(conn,"ACCTNO",siteCode);
			System.out.println("paymentMeans : "+paymentMeans);
			System.out.println("accToBeCredited : "+accToBeCredited);
			
			
          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;
			
					

			sql = " select ITEM_CODE,ITEM_DESCR,QUANTITY,UNIT,RATE,DISCOUNT,TAX_AMT,NET_AMT ," +
					"DISC_AMT,INST_AMOUNT,ACC_CODE__ITEM from invdet " +
					" where invoice_id=?" ;
							
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				cnt++;
				itemCodeDet = rs.getString("ITEM_CODE");
				itemDescrDet = rs.getString("ITEM_DESCR");
				quantityDet = rs.getString("QUANTITY");
				unitDet = rs.getString("UNIT");
				rateDet = rs.getDouble("RATE");
				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");
				
				
				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();
				
				
				
				tempMap.put("item_code" + cnt, itemCodeDet);
				tempMap.put("item_descr" + cnt, 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,2));
				tempMap.put("disc_amt" + cnt, utilMethods.getReqDecString(discAmtDet,2));
				tempMap.put("installment_amt" + cnt, instAmtDet);
				tempMap.put("acc_code" + cnt, accDet);
				
								
			}
			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= ?" ;
					
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				taxCode = rs.getString("TAX_CODE");
				taxClass = rs.getString("TAX_CLASS");
				taxBaseAmt = rs.getDouble("TAXABLE_AMT");
				taxChap = rs.getString("TAX_CHAP");
				taxEnv = rs.getString("TAX_ENV");
				taxAmt = rs.getDouble("TAX_AMT");
				exchRate = rs.getString("EXCH_RATE");
				taxPersc = rs.getDouble("TAX_PERC");
				taxEffects = rs.getString("EFFECT");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = 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)));
		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, "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(taxPersc,2)));
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxableBase",utilMethods.getReqDecString(taxBaseAmt,2)));
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxAmount",utilMethods.getReqDecString(taxAmt,2)));
		//tax.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurcharge", ""));
		
		Element equivalenceSurcharge = doc.createElement("EquivalenceSurcharge");
		equivalenceSurcharge.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurchargeAmount", "0.00"));
		tax.appendChild(equivalenceSurcharge);
		taxesOutputs.appendChild(tax);
		invoice.appendChild(taxesOutputs);
		
		Element taxesWithheld = doc.createElement("TaxesWithheld");
		Element taxHeld = doc.createElement("TaxesWithheld");
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode","01" ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxRate","0.00" ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxableBase", "0.00" ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxAmount","0.00"));
		taxesWithheld.appendChild(taxHeld);
		invoice.appendChild(taxesWithheld);
		
		Element invoiceTotals = doc.createElement("InvoiceTotals");
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGrossAmount",utilMethods.getReqDecString(NetAmtHd,2)));
		Element generalDiscounts = doc.createElement("GeneralDiscounts");
		Element discount = doc.createElement("Discount");
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Sales Promotions"));
		
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", utilMethods.getReqDecString(discAmtHd,2)));
		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(NetAmtHd,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxOutputs",utilMethods.getReqDecString(taxAmtHd,2)));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxesWithheld","0.00" ));
		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);
		for(int i = 1; i<= cnt ; i++)
		{
		
			
			
		Element items = doc.createElement("Items");
		Element invoiceLine = doc.createElement("InvoiceLine");
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "ItemDescription", (String) tempMap.get("item_descr" + cnt)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "Quantity", (String) tempMap.get("quantity" + cnt)));
		//invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitOfMeasure", (String) tempMap.get("unit" + cnt)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitOfMeasure", "01"));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "UnitPriceWithoutTax",(String) tempMap.get("rate" + cnt)));
		invoiceLine.appendChild(getElementValue(doc, invoiceParent, "TotalCost",(String) tempMap.get("net_amt" + cnt)));
		
		Element discountsAndRebates = doc.createElement("DiscountsAndRebates");
		Element discountItem = doc.createElement("Discount");
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", "Sales Promotions"));
		
		if(!(tempMap.get("disc_amt" + cnt).equalsIgnoreCase("0.0")))
		{
		
			discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountRate", (String) tempMap.get("discount" + cnt)));
		}
		
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", (String) tempMap.get("disc_amt" + cnt)));
		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("net_amt" + cnt)));
		
		
		
		
		Element taxesWithheldItem = doc.createElement("TaxesWithheld");
		Element taxHeldItem = doc.createElement("TaxesWithheld");
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", "01"));
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxRate", "0.00"));
		Element taxableTotAmt = doc.createElement("TotalAmount");
		taxableTotAmt.appendChild(getElementValue(doc, invoiceParent, "TaxableBase","0.00"));
		taxableTotAmt.appendChild(taxesWithheldItem);
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxAmount", "0.00"));
		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",utilMethods.getReqDecString(taxPersc,2)));
		Element taxableTotAmtOutputs = doc.createElement("TotalAmount");
		taxableTotAmtOutputs.appendChild(getElementValue(doc, invoiceParent, "TaxableBase",utilMethods.getReqDecString(taxBaseAmt,2)));
		taxItem.appendChild(taxableTotAmtOutputs);
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxAmount", (String) tempMap.get("tax_amt" + cnt)));
				
		taxesOutputsItem.appendChild(taxItem);
		invoiceLine.appendChild(taxesOutputsItem);
		
			
		
		//invoiceLine.appendChild(getElementValue(doc, invoiceParent, "AdditionalLineItemInformation", " "));
		
		
		items.appendChild(invoiceLine);
		invoice.appendChild(items);
		
		}
		
		Element paymentDetails = doc.createElement("PaymentDetails");
		Element installment = doc.createElement("Installment");
		installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentDueDate", sdf.format(invoiceDtHd)));
		installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentAmount", (String) tempMap.get("net_amt1")));
		installment.appendChild(getElementValue(doc, invoiceParent, "PaymentMeans", "04"));
		Element accountToBeCredited = doc.createElement("AccountToBeCredited");
		accountToBeCredited.appendChild(getElementValue(doc, invoiceParent, "IBAN",accToBeCredited));
		
		installment.appendChild(accountToBeCredited);
		paymentDetails.appendChild(installment);
		invoice.appendChild(paymentDetails);
		
		
		
		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;
    }
    
    
  

}
