Dépôt officiel du code source de l'ERP OpenConcerto

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 package org.openconcerto.erp.core.supplychain.purchase.importer;

import org.openconcerto.erp.core.finance.payment.element.ModeDeReglementSQLElement;

import org.openconcerto.sql.model.DBRoot;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowAccessor;

import org.openconcerto.sql.model.SQLRowValues;

import org.openconcerto.sql.model.SQLRowValuesListFetcher;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.model.Where;

import java.math.BigDecimal;

import java.text.DecimalFormat;

import java.text.DecimalFormatSymbols;

import java.text.SimpleDateFormat;

import java.util.Collection;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Locale;

import java.util.Map;

import org.jdom2.Document;

import org.jdom2.Element;

import org.jdom2.Namespace;

import org.jdom2.output.Format;

import org.jdom2.output.XMLOutputter;

/**

 * FacturX export (EN 16931 format)

 */

public class FacturXExporter {

    private class Tax {

        // Taux, ex pour 20% : 20.00

        final BigDecimal rate;

        // Montant de la TVA

        BigDecimal amount = BigDecimal.ZERO;

        // montant HT sur lequel s'applique la TVA

        BigDecimal basisAmount = BigDecimal.ZERO;

        public Tax(BigDecimal rate) {

            this.rate = rate;

        }

        public void add(BigDecimal taxAmount,

                BigDecimal basisAmount) {

            this.amount = this.amount.add(taxAmount);

            this.basisAmount = this.basisAmount.add(basisAmount);

        }

    }

    public static Namespace QDT_NS = Namespace.getNamespace("qdt", "urn:un:unece:uncefact:data:standard:QualifiedDataType:100");

    public static Namespace RAM_NS = Namespace.getNamespace("ram", "urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:100");

    public static Namespace RSM_NS = Namespace.getNamespace("rsm", "urn:un:unece:uncefact:data:standard:CrossIndustryInvoice:100");

    public static Namespace UDT_NS = Namespace.getNamespace("udt", "urn:un:unece:uncefact:data:standard:UnqualifiedDataType:100");

    public static Namespace XSI_NS = Namespace.getNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");

    public static void main(String[] args) {

        FacturXExporter ex = new FacturXExporter();

        System.err.println("FacturXExporter.main() " + ex.checkEAN13("5987854125989"));

        // String xml = ex.createXMLFrom(null, 1);

        // System.out.println(xml);

    }

    DecimalFormat formatAmount = new DecimalFormat("#0.00", DecimalFormatSymbols.getInstance(Locale.ENGLISH));

    private boolean useCommonClient = false;

    public String createXMLFrom(DBRoot dbRoot, int invoiceId, SQLRow societeRow) {

        SQLTable factureTable = dbRoot.getTable("SAISIE_VENTE_FACTURE");

        SQLRowValues rowValsToFetch = new SQLRowValues(factureTable);

        rowValsToFetch.putNulls("NUMERO", "DATE", "NOM", "T_TVA", "T_HT", "T_TTC", "NET_A_PAYER");

        rowValsToFetch.putRowValues("ID_MODE_REGLEMENT").putNulls("COMPTANT", "AJOURS", "LENJOUR", "DATE_FACTURE", "FIN_MOIS").putRowValues("ID_TYPE_REGLEMENT").putNulls("NOM");

        SQLRowValues putRowValuesClient = rowValsToFetch.putRowValues("ID_CLIENT");

        putRowValuesClient.putNulls("NOM", "RESPONSABLE", "SIRET", "NUMERO_TVA", "MAIL", "TEL");

        if (putRowValuesClient.getTable().contains("ID_CLIENT")) {

            this.useCommonClient = true;

            putRowValuesClient.putRowValues("ID_CLIENT").putNulls("NOM", "RESPONSABLE", "SIRET", "NUMERO_TVA", "MAIL", "TEL").putRowValues("ID_ADRESSE").putNulls("RUE", "VILLE", "CODE_POSTAL",

                    "PAYS");

        }

        putRowValuesClient.putRowValues("ID_ADRESSE").putNulls("RUE", "VILLE", "CODE_POSTAL", "PAYS");

        putRowValuesClient.putRowValues("ID_ADRESSE_F").putNulls("RUE", "VILLE", "CODE_POSTAL", "PAYS");

        rowValsToFetch.putRowValues("ID_ADRESSE").putNulls("RUE", "VILLE", "CODE_POSTAL", "PAYS");

        SQLTable factureItemtable = dbRoot.getTable("SAISIE_VENTE_FACTURE_ELEMENT");

        SQLRowValues rowValsItemsToFetch = new SQLRowValues(factureItemtable);

        rowValsItemsToFetch.putNulls("NIVEAU", "CODE", "NOM", "QTE", "QTE_UNITAIRE", "PV_HT", "T_PV_HT", "T_PV_TTC").putRowValues("ID_TAXE").put("TAUX", null);

        rowValsItemsToFetch.put("ID_SAISIE_VENTE_FACTURE", rowValsToFetch);

        List<SQLRowValues> factures = SQLRowValuesListFetcher.create(rowValsToFetch).fetch(new Where(factureTable.getKey(), "=", invoiceId));

        SQLRowValues facture = factures.get(0);

        Collection<? extends SQLRowAccessor> factureItems = facture.getReferentRows(factureItemtable.getField("ID_SAISIE_VENTE_FACTURE"));

        Map<Integer, Tax> taxes = new HashMap<>();

        // Tax t1 = new Tax();

        // t1.rate = new BigDecimal("20.0");

        // t1.amount = new BigDecimal("40.0");

        // t1.basisAmount = new BigDecimal("200.0");

        // taxes.add(t1);

        DecimalFormat fQty = new DecimalFormat("#0.########", DecimalFormatSymbols.getInstance(Locale.ENGLISH));

        String invoiceNumber = facture.getString("NUMERO");

        Date invoiceDate = facture.getDate("DATE").getTime();

        String regNote = societeRow.getString("TYPE") + " au capital de " + societeRow.getObject("CAPITAL") + " €";

        String legNote = societeRow.getString("RCS");

        int nbLines = factureItems.size();

        final Document doc = new Document();

        Element root = new Element("CrossIndustryInvoice", RSM_NS);

        root.addNamespaceDeclaration(QDT_NS);

        root.addNamespaceDeclaration(RAM_NS);

        root.addNamespaceDeclaration(RSM_NS);

        root.addNamespaceDeclaration(UDT_NS);

        root.addNamespaceDeclaration(XSI_NS);

        doc.setRootElement(root);

        // ExchangedDocumentContext : Le truc qui s'ecrirait en une ligne si les génies, grands

        // créateurs de cette norme, connaissaient la notion d'attributs...

        Element eDC = new Element("ExchangedDocumentContext", RSM_NS);

        Element bPSDCP = new Element("BusinessProcessSpecifiedDocumentContextParameter", RAM_NS);

        final Element bPSDCP_ID = new Element("ID", RAM_NS);

        // CHORUSPRO specifications: A1 (invoice deposit), A2 (prepaid invoice deposit)...

        bPSDCP_ID.setText("A1");

        bPSDCP.addContent(bPSDCP_ID);

        eDC.addContent(bPSDCP);

        Element bGSDCP = new Element("GuidelineSpecifiedDocumentContextParameter", RAM_NS);

        final Element bGSDCP_ID = new Element("ID", RAM_NS);

        // For Profile EN 16931 (Comfort):

        bGSDCP_ID.setText("urn:cen.eu:en16931:2017");

        bGSDCP.addContent(bGSDCP_ID);

        eDC.addContent(bGSDCP);

        root.addContent(eDC);

        // ExchangedDocument

        Element ed = new Element("ExchangedDocument", RSM_NS);

        Element edID = new Element("ID", RAM_NS);

        if (invoiceNumber.length() > 20) {

            // CHORUSPRO: the invoice number is limited to 20 characters (lol)

            invoiceNumber = invoiceNumber.substring(0, 20);

        }

        edID.setText(invoiceNumber);

        ed.addContent(edID);

        Element edTypeCode = new Element("TypeCode", RAM_NS);

        // The types of documents used are:

        // 380: Commercial Invoice

        // 381: Credit note

        // 384: Corrected invoice

        // 389: Self-billied invoice (created by the buyer on behalf of the supplier)

        // 261: Self billed credit note (not accepted by CHORUSPRO)

        // 386: Prepayment invoice

        // 751: Invoice information for accounting purposes (not accepted by CHORUSPRO)

        edTypeCode.setText("380");

        ed.addContent(edTypeCode);

        Element edIssueDateTime = new Element("IssueDateTime", RAM_NS);

        addDateTime(invoiceDate, edIssueDateTime);

        ed.addContent(edIssueDateTime);

        addIncludedNote(regNote, "REG", ed);

        addIncludedNote(legNote, "ABL", ed);

        // SupplyChainTradeTransaction

        Element eSupplyChainTradeTransaction = new Element("SupplyChainTradeTransaction", RSM_NS);

        int lineID = 1;

        for (SQLRowAccessor rowItem : factureItems) {

            String productCode = rowItem.getString("CODE");

            String productName = rowItem.getString("NOM").trim().length() == 0 ? "Ligne" : rowItem.getString("NOM");

            final BigDecimal pHT, pTTC, totalHTLigne, totalTTCLigne;

            SQLRowAccessor taxeItem = rowItem.getForeign("ID_TAXE");

            BigDecimal taxValue = new BigDecimal(taxeItem.getFloat("TAUX")).setScale(2);

            if (rowItem.getInt("NIVEAU") != 1) {

                pHT = BigDecimal.ZERO;

                pTTC = BigDecimal.ZERO;

                totalHTLigne = BigDecimal.ZERO;

                totalTTCLigne = BigDecimal.ZERO;

            } else {

                pHT = rowItem.getBigDecimal("PV_HT");

                pTTC = pHT.multiply(taxValue.add(BigDecimal.ONE));

                totalHTLigne = rowItem.getBigDecimal("T_PV_HT");

                totalTTCLigne = rowItem.getBigDecimal("T_PV_TTC");

            }

            int idTaxe = taxeItem.getID();

            if (!taxes.containsKey(idTaxe)) {

                Tax t = new Tax(taxValue);

                taxes.put(idTaxe, t);

            }

            taxes.get(idTaxe).add(totalTTCLigne.subtract(totalHTLigne), totalHTLigne);

            BigDecimal qte = new BigDecimal(rowItem.getInt("QTE")).multiply(rowItem.getBigDecimal("QTE_UNITAIRE"));

            Element eLineItem = new Element("IncludedSupplyChainTradeLineItem", RAM_NS);

            // AssociatedDocumentLineDocument

            Element eAssociatedDocumentLineDocument = new Element("AssociatedDocumentLineDocument", RAM_NS);

            Element eLineID = new Element("LineID", RAM_NS);

            eLineID.setText(String.valueOf(lineID++));

            eAssociatedDocumentLineDocument.addContent(eLineID);

            eLineItem.addContent(eAssociatedDocumentLineDocument);

            // SpecifiedTradeProduct

            Element eSpecifiedTradeProduct = new Element("SpecifiedTradeProduct", RAM_NS);

            if (!productCode.isEmpty()) {

                Element eGlobalID = new Element("GlobalID", RAM_NS);

                if (productCode.length() > 40) {

                    // CHORUSPRO: this field is limited to 40 characters

                    productCode = productCode.substring(0, 40);

                }

                if (checkEAN13(productCode)) {

                    // 0088 : EAN

                    eGlobalID.setAttribute("schemeID", "0088");

                } else {

                    // 0197 : Not Known

                    eGlobalID.setAttribute("schemeID", "0197");

                }

                eGlobalID.setText(productCode);

                eSpecifiedTradeProduct.addContent(eGlobalID);

            }

            Element eName = new Element("Name", RAM_NS);

            eName.setText(productName);

            eSpecifiedTradeProduct.addContent(eName);

            eLineItem.addContent(eSpecifiedTradeProduct);

            //

            Element eSpecifiedLineTradeAgreement = new Element("SpecifiedLineTradeAgreement", RAM_NS);

            // Prix unitaire TTC

            Element eGrossPriceProductTradePrice = new Element("GrossPriceProductTradePrice", RAM_NS);

            Element eChargeAmount = new Element("ChargeAmount", RAM_NS);

            eChargeAmount.setText(formatAmount.format(pTTC));

            eGrossPriceProductTradePrice.addContent(eChargeAmount);

            Element eAppliedTradeAllowanceCharge = new Element("AppliedTradeAllowanceCharge", RAM_NS);

            Element eChargeIndicator = new Element("ChargeIndicator", RAM_NS);

            Element eIndicator = new Element("Indicator", UDT_NS);

            // pas de remise

            eIndicator.setText("false");

            eChargeIndicator.addContent(eIndicator);

            eAppliedTradeAllowanceCharge.addContent(eChargeIndicator);

            Element eActualAmount = new Element("ActualAmount", RAM_NS);

            // Montant de TVA (unitaire)

            eActualAmount.setText(formatAmount.format(pTTC.subtract(pHT)));

            eAppliedTradeAllowanceCharge.addContent(eActualAmount);

            // eGrossPriceProductTradePrice.addContent(eAppliedTradeAllowanceCharge);

            eSpecifiedLineTradeAgreement.addContent(eGrossPriceProductTradePrice);

            // Prix unitaire HT

            Element eNetPriceProductTradePrice = new Element("NetPriceProductTradePrice", RAM_NS);

            Element eChargeAmountHT = new Element("ChargeAmount", RAM_NS);

            eChargeAmountHT.setText(formatAmount.format(pHT));

            eNetPriceProductTradePrice.addContent(eChargeAmountHT);

            eSpecifiedLineTradeAgreement.addContent(eNetPriceProductTradePrice);

            eLineItem.addContent(eSpecifiedLineTradeAgreement);

            // Quantité

            Element eSpecifiedLineTradeDelivery = new Element("SpecifiedLineTradeDelivery", RAM_NS);

            // LTR = Liter (1 dm3), MTQ = cubic meter , KGM = Kilogram , MTR = Meter , C62 = Unit ,

            // TNE = Tonne

            // TODO : gerer les unités

            Element eBilledQuantity = new Element("BilledQuantity", RAM_NS);

            eBilledQuantity.setAttribute("unitCode", "C62");

            eBilledQuantity.setText(fQty.format(qte));

            eSpecifiedLineTradeDelivery.addContent(eBilledQuantity);

            eLineItem.addContent(eSpecifiedLineTradeDelivery);

            Element eSpecifiedLineTradeSettlement = new Element("SpecifiedLineTradeSettlement", RAM_NS);

            Element eApplicableTradeTaxt = new Element("ApplicableTradeTax", RAM_NS);

            Element eTypeCode = new Element("TypeCode", RAM_NS);

            eTypeCode.setText("VAT");

            eApplicableTradeTaxt.addContent(eTypeCode);

            // S = Taux de TVA standard

            // E = Exempté de TVA

            // AE = Autoliquidation de TVA

            // K = Autoliquidation pour cause de livraison intracommunautaire

            // G = Exempté de TVA pour Export hors UE

            // O = Hors du périmètre d'application de la TVA

            // L = Iles Canaries

            // M = Ceuta et Mellila

            // TODO : TVA 0

            Element eCategoryCode = new Element("CategoryCode", RAM_NS);

            eCategoryCode.setText("S");

            eApplicableTradeTaxt.addContent(eCategoryCode);

            Element eRateApplicablePercent = new Element("RateApplicablePercent", RAM_NS);

            // 20% -> 20.0

            eRateApplicablePercent.setText(formatAmount.format(taxValue));

            eApplicableTradeTaxt.addContent(eRateApplicablePercent);

            eSpecifiedLineTradeSettlement.addContent(eApplicableTradeTaxt);

            Element eSpecifiedTradeSettlementLineMonetarySummation = new Element("SpecifiedTradeSettlementLineMonetarySummation", RAM_NS);

            // Total HT

            Element eLineTotalAmount = new Element("LineTotalAmount", RAM_NS);

            eLineTotalAmount.setText(formatAmount.format(totalHTLigne));

            eSpecifiedTradeSettlementLineMonetarySummation.addContent(eLineTotalAmount);

            eSpecifiedLineTradeSettlement.addContent(eSpecifiedTradeSettlementLineMonetarySummation);

            eLineItem.addContent(eSpecifiedLineTradeSettlement);

            //

            eSupplyChainTradeTransaction.addContent(eLineItem);

        }

        root.addContent(ed);

        addApplicableHeader(societeRow, facture, eSupplyChainTradeTransaction, taxes.values());

        root.addContent(eSupplyChainTradeTransaction);

        final XMLOutputter xmlOutput = new XMLOutputter();

        xmlOutput.setFormat(Format.getPrettyFormat());

        return xmlOutput.outputString(doc);

    }

    public void addValue(Element parent, Element element, String value) {

        element.setText(value);

        parent.addContent(element);

    }

    private void addApplicableHeader(SQLRowAccessor rowSociete, SQLRowAccessor rowFacture, Element eSupplyChainTradeTransaction, Collection<Tax> taxes) {

        //

        // ApplicableHeaderTradeAgreement

        //

        final Element eApplicableHeaderTradeAgreement = new Element("ApplicableHeaderTradeAgreement", RAM_NS);

        addSupplierInfo(eApplicableHeaderTradeAgreement, rowSociete);

        addBuyerInfo(eApplicableHeaderTradeAgreement, rowFacture);

        String orderRef = "";

        String contractRef = "";

        // Date de livraison, facultative

        Date effectiveDeliveryDate = rowFacture.getDate("DATE").getTime();

        String invoiceNumber = rowFacture.getString("NUMERO");

        String currencyCode = "EUR";

        SQLRowAccessor foreignMdr = rowFacture.getForeign("ID_MODE_REGLEMENT");

        int ajours = foreignMdr.getInt("AJOURS");

        int lenjour = foreignMdr.getInt("LENJOUR");

        String dueDescription = (ajours > 0 ? "A " + ajours : "") + (lenjour > 0 ? " le " + lenjour : "");

        Date dueDate = ModeDeReglementSQLElement.calculDate(foreignMdr, rowFacture.getDate("DATE").getTime());

        final Element eBuyerOrderReferencedDocument = new Element("BuyerOrderReferencedDocument", RAM_NS);

        addValue(eBuyerOrderReferencedDocument, new Element("IssuerAssignedID", RAM_NS), orderRef);

        eApplicableHeaderTradeAgreement.addContent(eBuyerOrderReferencedDocument);

        final Element eContractReferencedDocument = new Element("ContractReferencedDocument", RAM_NS);

        addValue(eContractReferencedDocument, new Element("IssuerAssignedID", RAM_NS), contractRef);

        eApplicableHeaderTradeAgreement.addContent(eContractReferencedDocument);

        eSupplyChainTradeTransaction.addContent(eApplicableHeaderTradeAgreement);

        //

        // ApplicableHeaderTradeDelivery

        //

        final Element eApplicableHeaderTradeDelivery = new Element("ApplicableHeaderTradeDelivery", RAM_NS);

        // <ram:ShipToTradeParty>

        // <ram:PostalTradeAddress>

        // <ram:PostcodeCode>69001</ram:PostcodeCode>

        // <ram:LineOne>35 rue de la République</ram:LineOne>

        // <ram:CityName>Lyon</ram:CityName>

        // <ram:CountryID>FR</ram:CountryID>

        // </ram:PostalTradeAddress>

        // </ram:ShipToTradeParty>

        if (effectiveDeliveryDate != null) {

            // Date de livraison effective (facultative)

            final Element eActualDeliverySupplyChainEvent = new Element("ActualDeliverySupplyChainEvent", RAM_NS);

            final Element eOccurrenceDateTime = new Element("OccurrenceDateTime", RAM_NS);

            addDateTime(effectiveDeliveryDate, eOccurrenceDateTime);

            eActualDeliverySupplyChainEvent.addContent(eOccurrenceDateTime);

            eApplicableHeaderTradeDelivery.addContent(eActualDeliverySupplyChainEvent);

        }

        eSupplyChainTradeTransaction.addContent(eApplicableHeaderTradeDelivery);

        //

        // ApplicableHeaderTradeSettlement

        //

        final Element eApplicableHeaderTradeSettlement = new Element("ApplicableHeaderTradeSettlement", RAM_NS);

        addValue(eApplicableHeaderTradeSettlement, new Element("PaymentReference", RAM_NS), invoiceNumber);

        addValue(eApplicableHeaderTradeSettlement, new Element("InvoiceCurrencyCode", RAM_NS), currencyCode);

        final Element aSpecifiedTradeSettlementPaymentMeans = new Element("SpecifiedTradeSettlementPaymentMeans", RAM_NS);

        addValue(aSpecifiedTradeSettlementPaymentMeans, new Element("TypeCode", RAM_NS), "57");

        // <ram:TypeCode>30</ram:TypeCode>

        // <ram:Information>Virement sur compte Banque Fiducial</ram:Information>

        // <ram:PayeePartyCreditorFinancialAccount>

        // <ram:IBANID>FR2012421242124212421242124</ram:IBANID>

        // </ram:PayeePartyCreditorFinancialAccount>

        // <ram:PayeeSpecifiedCreditorFinancialInstitution>

        // <ram:BICID>FIDCFR21XXX</ram:BICID>

        // </ram:PayeeSpecifiedCreditorFinancialInstitution>

        eApplicableHeaderTradeSettlement.addContent(aSpecifiedTradeSettlementPaymentMeans);

        DecimalFormat format = new DecimalFormat("#0.00", DecimalFormatSymbols.getInstance(Locale.ENGLISH));

        for (Tax t : taxes) {

            final Element aApplicableTradeTax = new Element("ApplicableTradeTax", RAM_NS);

            // Montant de la TVA

            addValue(aApplicableTradeTax, new Element("CalculatedAmount", RAM_NS), format.format(t.amount));

            // LOL

            addValue(aApplicableTradeTax, new Element("TypeCode", RAM_NS), "VAT");

            // montant HT sur lequel s'applique la TVA

            addValue(aApplicableTradeTax, new Element("BasisAmount", RAM_NS), format.format(t.basisAmount));

            // S = Taux de TVA standard

            // E = Exempté de TVA

            // AE = Autoliquidation de TVA

            // K = Autoliquidation pour cause de livraison intracommunautaire

            // G = Exempté de TVA pour Export hors UE

            // O = Hors du périmètre d'application de la TVA

            // L = Iles Canaries

            // M = Ceuta et Mellila

            // TODO : TVA 0

            addValue(aApplicableTradeTax, new Element("CategoryCode", RAM_NS), "S");

            // TODO : type de TVA :

            // 5 : Date de la facture (TVA sur DEBITS)

            // 29 : Date de livraison (TVA sur DEBITS)

            // 72 : Date de paiement (TVA sur ENCAISSEMENTS)

            addValue(aApplicableTradeTax, new Element("DueDateTypeCode", RAM_NS), "5");

            addValue(aApplicableTradeTax, new Element("RateApplicablePercent", RAM_NS), format.format(t.rate));

            eApplicableHeaderTradeSettlement.addContent(aApplicableTradeTax);

        }

        final Element eSpecifiedTradePaymentTerms = new Element("SpecifiedTradePaymentTerms", RAM_NS);

        addValue(eSpecifiedTradePaymentTerms, new Element("Description", RAM_NS), dueDescription);

        final Element eDueDateDateTime = new Element("DueDateDateTime", RAM_NS);

        addDateTime(dueDate, eDueDateDateTime);

        eSpecifiedTradePaymentTerms.addContent(eDueDateDateTime);

        eApplicableHeaderTradeSettlement.addContent(eSpecifiedTradePaymentTerms);

        final Element eSpecifiedTradeSettlementHeaderMonetarySummation = new Element("SpecifiedTradeSettlementHeaderMonetarySummation", RAM_NS);

        final Element eTotalHT = new Element("LineTotalAmount", RAM_NS);

        eTotalHT.setText(this.formatAmount.format(new BigDecimal(rowFacture.getLong("T_HT")).movePointLeft(2)));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalHT);

        final Element eTotalBaseTVA = new Element("TaxBasisTotalAmount", RAM_NS);

        BigDecimal totalBaseTVA = BigDecimal.ZERO;

        for (Tax tax : taxes) {

            totalBaseTVA = totalBaseTVA.add(tax.basisAmount);

        }

        eTotalBaseTVA.setText(this.formatAmount.format(totalBaseTVA));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalBaseTVA);

        final Element eTotalTVA = new Element("TaxTotalAmount", RAM_NS);

        eTotalTVA.setAttribute("currencyID", "EUR");

        eTotalTVA.setText(this.formatAmount.format(new BigDecimal(rowFacture.getLong("T_TVA")).movePointLeft(2)));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalTVA);

        final Element eTotalTTC = new Element("GrandTotalAmount", RAM_NS);

        BigDecimal totalTTC = new BigDecimal(rowFacture.getLong("T_TTC")).movePointLeft(2);

        eTotalTTC.setText(this.formatAmount.format(totalTTC));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalTTC);

        final Element eTotalPrepaid = new Element("TotalPrepaidAmount", RAM_NS);

        BigDecimal netApayer = new BigDecimal(rowFacture.getLong("NET_A_PAYER")).movePointLeft(2);

        eTotalPrepaid.setText(this.formatAmount.format(totalTTC.subtract(netApayer)));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalPrepaid);

        final Element eTotalDue = new Element("DuePayableAmount", RAM_NS);

        eTotalDue.setText(this.formatAmount.format(netApayer));

        eSpecifiedTradeSettlementHeaderMonetarySummation.addContent(eTotalDue);

        // Total HT

        // <ram:LineTotalAmount>624.90</ram:LineTotalAmount>

        // Total HT sur leque est appliqué la TVA

        // <ram:TaxBasisTotalAmount>624.90</ram:TaxBasisTotalAmount>

        // Total des TVA

        // <ram:TaxTotalAmount currencyID="EUR">46.25</ram:TaxTotalAmount>

        // Total TTC

        // <ram:GrandTotalAmount>671.15</ram:GrandTotalAmount>

        // Montant déjà payé

        // <ram:TotalPrepaidAmount>201.00</ram:TotalPrepaidAmount>

        // Reste à payer

        // <ram:DuePayableAmount>470.15</ram:DuePayableAmount>

        eApplicableHeaderTradeSettlement.addContent(eSpecifiedTradeSettlementHeaderMonetarySummation);

        eSupplyChainTradeTransaction.addContent(eApplicableHeaderTradeSettlement);

    }

    private void addBuyerInfo(final Element eApplicableHeaderTradeAgreement, final SQLRowAccessor rowFacture) {

        SQLRowAccessor client = rowFacture.getForeign("ID_CLIENT");

        SQLRowAccessor clientGestion = client;

        if (this.useCommonClient) {

            client = client.getForeign("ID_CLIENT");

        }

        final SQLRowAccessor adr;

        if (!rowFacture.isForeignEmpty("ID_ADRESSE")) {

            adr = rowFacture.getForeign("ID_ADRESSE");

        } else if (!clientGestion.isForeignEmpty("ID_ADRESSE_F")) {

            adr = clientGestion.getForeign("ID_ADRESSE_F");

        } else {

            adr = client.getForeign("ID_ADRESSE");

        }

        // Acheteur

        String buyerName = client.getString("NOM");

        String buyerSIRET = client.getString("SIRET");

        String buyerVAT = client.getString("NUMERO_TVA");

        String buyerContactName = client.getString("RESPONSABLE");

        String buyerPhone = client.getString("TEL");

        String buyerEmail = client.getString("MAIL");

        String buyerAddrL1 = adr.getString("RUE");

        String buyerAddrL2 = "";

        String buyerAddrL3 = "";

        String buyerPostalCode = adr.getString("CODE_POSTAL");

        String buyerCity = adr.getString("VILLE");

        String buyerCountryCode = resolveCountryCode(adr.getString("PAYS"));

        final Element eBuyerTradeParty = new Element("BuyerTradeParty", RAM_NS);

        this.addValue(eBuyerTradeParty, new Element("Name", RAM_NS), buyerName);

        final Element eSpecifiedLegalOrganization = new Element("SpecifiedLegalOrganization", RAM_NS);

        Element eSpecifiedLegalOrganizationId = new Element("ID", RAM_NS);

        eSpecifiedLegalOrganizationId.setAttribute("schemeID", "0002");

        eSpecifiedLegalOrganizationId.setText(buyerSIRET);

        eSpecifiedLegalOrganization.addContent(eSpecifiedLegalOrganizationId);

        eBuyerTradeParty.addContent(eSpecifiedLegalOrganization);

        // Contact

        final Element eDefinedTradeContact = new Element("DefinedTradeContact", RAM_NS);

        addValue(eDefinedTradeContact, new Element("PersonName", RAM_NS), buyerContactName);

        final Element eTelephoneUniversalCommunication = new Element("TelephoneUniversalCommunication", RAM_NS);

        addValue(eTelephoneUniversalCommunication, new Element("CompleteNumber", RAM_NS), buyerPhone);

        eDefinedTradeContact.addContent(eTelephoneUniversalCommunication);

        final Element eEmailURIUniversalCommunication = new Element("EmailURIUniversalCommunication", RAM_NS);

        final Element eURIID = new Element("URIID", RAM_NS);

        eURIID.setAttribute("schemeID", "SMTP");

        eURIID.setText(buyerEmail);

        eEmailURIUniversalCommunication.addContent(eURIID);

        eDefinedTradeContact.addContent(eEmailURIUniversalCommunication);

        eBuyerTradeParty.addContent(eDefinedTradeContact);

        // Adresse postale

        final Element ePostalTradeAddress = new Element("PostalTradeAddress", RAM_NS);

        addValue(ePostalTradeAddress, new Element("PostcodeCode", RAM_NS), buyerPostalCode);

        addValue(ePostalTradeAddress, new Element("LineOne", RAM_NS), buyerAddrL1);

        if (buyerAddrL2 != null && !buyerAddrL2.trim().isEmpty()) {

            addValue(ePostalTradeAddress, new Element("LineTwo", RAM_NS), buyerAddrL2);

        }

        if (buyerAddrL3 != null && !buyerAddrL3.trim().isEmpty()) {

            addValue(ePostalTradeAddress, new Element("LineThree", RAM_NS), buyerAddrL3);

        }

        addValue(ePostalTradeAddress, new Element("CityName", RAM_NS), buyerCity);

        addValue(ePostalTradeAddress, new Element("CountryID", RAM_NS), buyerCountryCode);

        eBuyerTradeParty.addContent(ePostalTradeAddress);

        if (buyerVAT != null && buyerVAT.trim().length() > 0) {

            // TVA

            final Element eSpecifiedTaxRegistration = new Element("SpecifiedTaxRegistration", RAM_NS);

            final Element eSpecifiedTaxRegistrationId = new Element("ID", RAM_NS);

            eSpecifiedTaxRegistrationId.setAttribute("schemeID", "VA");

            eSpecifiedTaxRegistrationId.setText(buyerVAT);

            eSpecifiedTaxRegistration.addContent(eSpecifiedTaxRegistrationId);

            eBuyerTradeParty.addContent(eSpecifiedTaxRegistration);

        }

        eApplicableHeaderTradeAgreement.addContent(eBuyerTradeParty);

    }

    private void addSupplierInfo(final Element eApplicableHeaderTradeAgreement, SQLRowAccessor rowSociete) {

        String supplierName = rowSociete.getString("NOM");

        String supplierSIRET = rowSociete.getString("NUM_SIRET");

        String supplierContactName = "";

        String supplierContactPhone = rowSociete.getString("NUM_TEL");

        String supplierContactEmail = rowSociete.getString("MAIL");

        SQLRowAccessor adr = rowSociete.getForeign("ID_ADRESSE_COMMON");

        String supplierAddrL1 = adr.getString("RUE");

        String supplierAddrL2 = "";

        String supplierAddrL3 = "";

        String supplierAddrPostalCode = adr.getString("CODE_POSTAL");

        String supplierAddrCityName = adr.getString("VILLE");

        String supplierAddrCountryID = resolveCountryCode(adr.getString("PAYS"));

        String supplierNumTVA = rowSociete.getString("NUM_NII");

        // Vendeur

        final Element eSellerTradeParty = new Element("SellerTradeParty", RAM_NS);

        addValue(eSellerTradeParty, new Element("Name", RAM_NS), supplierName);

        final Element eSpecifiedLegalOrganization = new Element("SpecifiedLegalOrganization", RAM_NS);

        final Element eID = new Element("ID", RAM_NS);

        eID.setAttribute("schemeID", "0002");

        eID.setText(supplierSIRET);

        eSpecifiedLegalOrganization.addContent(eID);

        eSellerTradeParty.addContent(eSpecifiedLegalOrganization);

        final Element eDefinedTradeContact = new Element("DefinedTradeContact", RAM_NS);

        final Element ePersonName = new Element("PersonName", RAM_NS);

        ePersonName.setText(supplierContactName);

        eDefinedTradeContact.addContent(ePersonName);

        final Element eTelephoneUniversalCommunication = new Element("TelephoneUniversalCommunication", RAM_NS);

        final Element eCompleteNumber = new Element("CompleteNumber", RAM_NS);

        eCompleteNumber.setText(supplierContactPhone);

        eTelephoneUniversalCommunication.addContent(eCompleteNumber);

        eDefinedTradeContact.addContent(eTelephoneUniversalCommunication);

        final Element eEmailURIUniversalCommunication = new Element("EmailURIUniversalCommunication", RAM_NS);

        final Element eURIID = new Element("URIID", RAM_NS);

        eURIID.setAttribute("schemeID", "SMTP");

        eURIID.setText(supplierContactEmail);

        eEmailURIUniversalCommunication.addContent(eURIID);

        eDefinedTradeContact.addContent(eEmailURIUniversalCommunication);

        eSellerTradeParty.addContent(eDefinedTradeContact);

        final Element ePostalTradeAddress = new Element("PostalTradeAddress", RAM_NS);

        addValue(ePostalTradeAddress, new Element("PostcodeCode", RAM_NS), supplierAddrPostalCode);

        addValue(ePostalTradeAddress, new Element("LineOne", RAM_NS), supplierAddrL1);

        if (supplierAddrL2 != null && !supplierAddrL2.trim().isEmpty()) {

            addValue(ePostalTradeAddress, new Element("LineTwo", RAM_NS), supplierAddrL2);

        }

        if (supplierAddrL3 != null && !supplierAddrL3.trim().isEmpty()) {

            addValue(ePostalTradeAddress, new Element("LineThree", RAM_NS), supplierAddrL3);

        }

        addValue(ePostalTradeAddress, new Element("CityName", RAM_NS), supplierAddrCityName);

        addValue(ePostalTradeAddress, new Element("CountryID", RAM_NS), supplierAddrCountryID);

        eSellerTradeParty.addContent(ePostalTradeAddress);

        final Element eSpecifiedTaxRegistration = new Element("SpecifiedTaxRegistration", RAM_NS);

        final Element eSpecifiedTaxRegistrationID = new Element("ID", RAM_NS);

        eSpecifiedTaxRegistrationID.setAttribute("schemeID", "VA");

        eSpecifiedTaxRegistrationID.setText(supplierNumTVA);

        eSpecifiedTaxRegistration.addContent(eSpecifiedTaxRegistrationID);

        eSellerTradeParty.addContent(eSpecifiedTaxRegistration);

        eApplicableHeaderTradeAgreement.addContent(eSellerTradeParty);

    }

    private void addIncludedNote(String content, String code, Element ed) {

        final Element e = new Element("IncludedNote", RAM_NS);

        final Element eContent = new Element("Content", RAM_NS);

        eContent.setText(content);

        e.addContent(eContent);

        final Element eCode = new Element("SubjectCode", RAM_NS);

        eCode.setText(code);

        e.addContent(eCode);

        ed.addContent(e);

    }

    private void addDateTime(Date date, Element eTime) {

        final Element e = new Element("DateTimeString", UDT_NS);

        // From the doc : Only value "102" ...

        e.setAttribute("format", "102");

        SimpleDateFormat s = new SimpleDateFormat("yyyyMMdd");

        e.setText(s.format(date));

        eTime.addContent(e);

    }

    boolean checkEAN13(String code) {

        if (code == null || code.length() != 13) {

            return false;

        }

        int checkdigit = 0;

        for (int i = 1; i < 12; i += 2) {

            checkdigit += Integer.valueOf(code.substring(i, i + 1));

        }

        checkdigit *= 3;

        for (int i = 0; i < 11; i += 2) {

            checkdigit += Integer.valueOf(code.substring(i, i + 1));

        }

        checkdigit %= 10;

        if (checkdigit != 0) {

            checkdigit = 10 - checkdigit;

        }

        return Integer.valueOf(code.substring(12, 13)).intValue() == checkdigit;

    }

    private String resolveCountryCode(String pays) {

        if (pays == null || pays.trim().length() == 0) {

            return "FR";

        } else if (pays.toLowerCase().equals("france")) {

            return "FR";

        } else if (pays.toLowerCase().equals("fr")) {

            return "FR";

        } else if (pays.toLowerCase().equals("polska")) {

            return "PL";

        } else if (pays.toLowerCase().equals("pologne")) {

            return "PL";

        } else if (pays.toLowerCase().equals("pl")) {

            return "PL";

        } else if (pays.toLowerCase().equals("allemagne")) {

            return "DE";

        } else if (pays.toLowerCase().equals("de")) {

            return "DE";

        } else if (pays.toLowerCase().equals("deutschland")) {

            return "DE";

        } else if (pays.toLowerCase().equals("italie")) {

            return "IT";

        } else if (pays.toLowerCase().equals("italy")) {

            return "IT";

        } else if (pays.toLowerCase().equals("italia")) {

            return "IT";

        } else if (pays.toLowerCase().equals("it")) {

            return "IT";

        } else if (pays.toLowerCase().equals("espagne")) {

            return "SP";

        } else if (pays.toLowerCase().equals("spain")) {

            return "SP";

        } else if (pays.toLowerCase().equals("sp")) {

            return "SP";

        } else if (pays.toLowerCase().equals("luxembourg")) {

            return "LU";

        } else if (pays.toLowerCase().equals("lu")) {

            return "LU";

        } else if (pays.toLowerCase().equals("portugal")) {

            return "PT";

        } else if (pays.toLowerCase().equals("pt")) {

            return "PT";

        } else if (pays.toLowerCase().equals("belgique")) {

            return "BE";

        } else if (pays.toLowerCase().equals("belgium")) {

            return "BE";

        } else if (pays.toLowerCase().equals("be")) {

            return "BE";

        } else {

            return "";

        }

    }

}