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


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 success 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();
           	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR", "");
			return errString;

        }
	    catch (TransformerException tfe) 
	    {
             tfe.printStackTrace();
         	errString = itmDBAccessLocal.getErrorString("", "VTTRINERR", "");
			return errString;

        }
        catch(Exception e)
        {
        	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="",invAmt="",invType="",netAmt="",currencyCode="";
		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.getString("INV_AMT");
				invType = rs.getString("INV_TYPE");
				netAmt=rs.getString("NET_AMT");
				currencyCode=rs.getString("CURR_CODE");
								
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			invAmt = invAmt == null ? " " : invAmt.trim();
			invType = invType == null ? " " : invType.trim();
			currencyCode = currencyCode == null ? " " : currencyCode.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", invType));
        Element batch = doc.createElement("Batch");
        batch.appendChild(getElementValue(doc, fileHeader, "BatchIdentifier"," "));
        batch.appendChild(getElementValue(doc, fileHeader, "InvoicesCount"," "));
        batch.appendChild(getElementValue(doc, fileHeader, "TotalInvoicesAmount",invAmt));
        batch.appendChild(getElementValue(doc, fileHeader, "TotalOutstandingAmount",netAmt));
        batch.appendChild(getElementValue(doc, fileHeader, "TotalExecutableAmount",netAmt));
        batch.appendChild(getElementValue(doc, fileHeader, "InvoiceCurrencyCode",currencyCode));
        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="",custPanNo="",custType="",custName="",custCountrtCode="";
		String rate="",unitRate="",discountInv="",siteDescr="",custCodeBill="";
		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 reg_no from siteregno where site_code=?" ;
			
			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 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");
				custPanNo = rs.getString("pan_no");
				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();
		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();
		custPanNo = custPanNo == null ? " " : custPanNo.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", "F"));
        taxIdentification.appendChild(getElementValue(doc, parties, "ResidenceTypeCode", "R"));
        taxIdentification.appendChild(getElementValue(doc, parties, "TaxIdentificationNumber", siteRegNo));
        sellerParty.appendChild(taxIdentification);
        
        Element individual = doc.createElement("Individual");
        individual.appendChild(getElementValue(doc, parties, "Name", siteContactPersion));
        individual.appendChild(getElementValue(doc, parties, "FirstSurname", " "));
        individual.appendChild(getElementValue(doc, parties, "SecondSurname", siteContactPersion));
        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", " "));
        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", custPanNo));
        buyerParty.appendChild(taxIdfbuyerParty);
        
        Element administrativeCentresparent = doc.createElement("AdministrativeCentres");
        Element administrativeCentres = doc.createElement("AdministrativeCentres");
        administrativeCentres.appendChild(getElementValue(doc, parties, "CentreCode", custCode));
        administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", custType));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        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));
        administrativeCentres.appendChild(addressbuyer);
        administrativeCentresparent.appendChild(administrativeCentres);
        /*-------------------------------------------------------------------*/
        Element admCustBill = doc.createElement("AdministrativeCentres");
        admCustBill.appendChild(getElementValue(doc, parties, "CentreCode", custCodeBill));
        admCustBill.appendChild(getElementValue(doc, parties, "RoleTypeCode", custType));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        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));
        admCustBill.appendChild(addCustBill);
        administrativeCentresparent.appendChild(admCustBill);
        
        
        Element admCustDlv = doc.createElement("AdministrativeCentres");
        admCustDlv.appendChild(getElementValue(doc, parties, "CentreCode", custCode));
        admCustDlv.appendChild(getElementValue(doc, parties, "RoleTypeCode", custType));
       // administrativeCentres.appendChild(getElementValue(doc, parties, "RoleTypeCode", "03"));
        admCustDlv.appendChild(getElementValue(doc, parties, "Name", custName));
        Element addCustDlv = doc.createElement("AddressInSpain");
        addCustDlv.appendChild(getElementValue(doc, parties, "Address", custAdd1));
        addCustDlv.appendChild(getElementValue(doc, parties, "PostCode", custPin));
        addCustDlv.appendChild(getElementValue(doc, parties, "Town", custCity));
        addCustDlv.appendChild(getElementValue(doc, parties, "Province", custState));
        addCustDlv.appendChild(getElementValue(doc, parties, "CountryCode", custCountrtCode));
        admCustDlv.appendChild(addCustDlv);
        administrativeCentresparent.appendChild(admCustDlv);
        
        
        /*------------------------------------------------------------------*/
        
        buyerParty.appendChild(administrativeCentresparent);
        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="",taxAmt="",exchRate="",discAmt="",taxBaseAmt="",taxPersc="";
		String invoiceAmtHd="",taxAmtHd="",discAmtHd="",NetAmtHd="",currCodeHd="",invoiceDtHd="";
		String itemCodeDet="",itemDescrDet="",quantityDet="",unitDet="",rateDet="",discountDet="",taxAmtDet="",discAmtDet="",accDet="",instAmtDet="",netAmtDet="";
		String rate="",unitRate="",discountInv="",taxEffects="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt=0;
		int totalAmt=0,amtWitoutTax=0;
		HashMap<String, String> tempMap = new HashMap<String, String>();
		
		try
		{
			System.out.println("getInvoiceElement@@@@@@@@@@@@");
		
			
			
			
			sql = " select INV_AMT,TAX_AMT,DISC_AMT,NET_AMT,CURR_CODE,TRAN_DATE from invoice " +
					"where invoice_id=?" ;
							
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, invoiceId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				invoiceDtHd = rs.getString("TRAN_DATE");
				invoiceAmtHd = rs.getString("INV_AMT");
				taxAmtHd = rs.getString("TAX_AMT");
				discAmtHd = rs.getString("DISC_AMT");
				NetAmtHd = rs.getString("NET_AMT");
				currCodeHd = rs.getString("CURR_CODE");
				
				
			}
			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.getString("RATE");
				discountDet = rs.getString("DISCOUNT");
				taxAmtDet = rs.getString("TAX_AMT");
				netAmtDet = rs.getString("NET_AMT");
				discAmtDet = rs.getString("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, rateDet);
				tempMap.put("discount" + cnt, discountDet);
				tempMap.put("tax_amt" + cnt, taxAmtDet);
				tempMap.put("net_amt" + cnt, netAmtDet);
				tempMap.put("disc_amt" + cnt, discAmtDet);
				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.getString("TAXABLE_AMT");
				taxChap = rs.getString("TAX_CHAP");
				taxEnv = rs.getString("TAX_ENV");
				taxAmt = rs.getString("TAX_AMT");
				exchRate = rs.getString("EXCH_RATE");
				taxPersc = rs.getString("TAX_PERC");
				taxEffects = rs.getString("EFFECT");
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if(taxEffects.equalsIgnoreCase("+"))
			{
				taxEffects="Chargeable Tax";
			}
			if(taxEffects.equalsIgnoreCase("-"))
			{
				taxEffects="Not Liable For Tax";
			}
			if(taxEffects.equalsIgnoreCase("N"))
			{
				taxEffects="Exempt From Tax";
			}
			invoiceDtHd = invoiceDtHd == null ? " " : invoiceDtHd.trim();
			invoiceAmtHd = invoiceAmtHd == null ? " " : invoiceAmtHd.trim();
			taxAmtHd = taxAmtHd == null ? " " : taxAmtHd.trim();
			discAmtHd = discAmtHd == null ? " " : discAmtHd.trim();
			NetAmtHd = NetAmtHd == null ? " " : NetAmtHd.trim();
			currCodeHd = currCodeHd == null ? " " : currCodeHd.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();
			
			
		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
		
		
		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, "InvoiceSeriesCode", " "));
		invoiceHeader.appendChild(getElementValue(doc, invoiceParent, "InvoiceDocumentType","" ));
		invoiceHeader.appendChild(getElementValue(doc, invoiceParent, "InvoiceClass", ""));
		invoice.appendChild(invoiceHeader);
		
		Element invoiceIssueData = doc.createElement("InvoiceIssueData");
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "IssueDate", invoiceDtHd));
		Element invoicingPeriod = doc.createElement("InvoicingPeriod");
		invoicingPeriod.appendChild(getElementValue(doc, invoiceParent, "StartDate", invoiceDtHd));
		invoicingPeriod.appendChild(getElementValue(doc, invoiceParent, "EndDate", invoiceDtHd));
		invoiceIssueData.appendChild(invoicingPeriod);
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "InvoiceCurrencyCode", currCodeHd));
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "TaxCurrencyCode", currCodeHd));
		invoiceIssueData.appendChild(getElementValue(doc, invoiceParent, "LanguageName", " "));
		invoice.appendChild(invoiceIssueData);
		
		
		Element taxesOutputs = doc.createElement("TaxesOutputs");
		Element tax = doc.createElement("Tax");
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", taxEffects));
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxRate", taxPersc));
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxableBase", taxBaseAmt));
		tax.appendChild(getElementValue(doc, invoiceParent, "TaxAmount",taxAmt));
		tax.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurcharge", ""));
		tax.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurchargeAmount", ""));
		taxesOutputs.appendChild(tax);
		invoice.appendChild(taxesOutputs);
		
		Element taxesWithheld = doc.createElement("TaxesWithheld");
		Element taxHeld = doc.createElement("TaxesWithheld");
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode",taxEffects ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxRate",taxPersc ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxableBase",taxBaseAmt ));
		taxHeld.appendChild(getElementValue(doc, invoiceParent, "TaxAmount",taxAmt));
		taxesWithheld.appendChild(taxHeld);
		invoice.appendChild(taxesWithheld);
		
		Element invoiceTotals = doc.createElement("InvoiceTotals");
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGrossAmount", NetAmtHd));
		Element generalDiscounts = doc.createElement("GeneralDiscounts");
		Element discount = doc.createElement("Discount");
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountReason", " "));
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountRate", " "));
		discount.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", discAmtHd));
		generalDiscounts.appendChild(discount);
		invoiceTotals.appendChild(generalDiscounts);
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGeneralDiscounts", discAmtHd));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGeneralSurcharges", "0.00"));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalGrossAmountBeforeTaxes", NetAmtHd));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxOutputs", taxAmtHd ));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalTaxesWithheld", taxAmtHd ));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalOutstandingAmount", NetAmtHd));
		invoiceTotals.appendChild(getElementValue(doc, invoiceParent, "TotalExecutableAmount", NetAmtHd));
		invoice.appendChild(invoiceTotals);
		
		for(int i = 1; i<= cnt ; i++)
		{
		
			/*if(tempMap.get("tax_amt" + cnt) != "")
			{
				totalAmt=Integer.parseInt(tempMap.get("tax_amt" + cnt))+Integer.parseInt(tempMap.get("net_amt" + cnt));
				amtWitoutTax=totalAmt - Integer.parseInt(tempMap.get("tax_amt" + cnt));
				System.out.println("totalAmt ! "+totalAmt);
			}
			else
			{
				totalAmt=Integer.parseInt(tempMap.get("net_amt" + cnt));
				amtWitoutTax=totalAmt;
				System.out.println("totalAmt "+totalAmt);
			}*/
			
			
			
		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, "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", "descuento"));
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountRate", " "));
		discountItem.appendChild(getElementValue(doc, invoiceParent, "DiscountAmount", discAmt));
		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", " "));
		charge.appendChild(getElementValue(doc, invoiceParent, "ChargeAmount", " "));
		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", taxEffects));
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxRate", taxPersc));
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxableBase",taxBaseAmt));
		taxHeldItem.appendChild(getElementValue(doc, invoiceParent, "TaxAmount", (String) tempMap.get("tax_amt" + cnt)));
		taxesWithheldItem.appendChild(taxHeldItem);
		invoiceLine.appendChild(taxesWithheldItem);
		
		
		
		
		Element taxesOutputsItem = doc.createElement("TaxesOutputs");
		Element taxItem = doc.createElement("Tax");
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxTypeCode", taxEffects));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxRate", taxPersc));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxableBase", taxBaseAmt));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "TaxAmount", (String) tempMap.get("tax_amt" + cnt)));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurcharge", " "));
		taxItem.appendChild(getElementValue(doc, invoiceParent, "EquivalenceSurchargeAmount", " "));
		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", invoiceDtHd));
		installment.appendChild(getElementValue(doc, invoiceParent, "InstallmentAmount", (String) tempMap.get("net_amt1")));
		installment.appendChild(getElementValue(doc, invoiceParent, "PaymentMeans", " "));
		Element accountToBeCredited = doc.createElement("AccountToBeCredited");
		accountToBeCredited.appendChild(getElementValue(doc, invoiceParent, "IBAN", " "));
		
		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;
    }
    
    
  

}
