WebSVN - OpenConcerto - Rev 177 - /trunk/OpenConcerto/src/org/openconcerto/erp/panel/compta/ExportSageEtendu.java

/*
 * 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.panel.compta;

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.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.GestionDevise;
import org.openconcerto.utils.StringUtils;
import org.openconcerto.utils.cc.ITransformer;

import java.io.IOException;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;

public class ExportSageEtendu extends AbstractExport {

    private List<SQLRowValues> data;
    private Map<Integer, SQLRowValues> mapFacture = new HashMap<>();
    private Map<Integer, SQLRowValues> mapAvoir = new HashMap<>();

    public ExportSageEtendu(DBRoot rootSociete) {
        super(rootSociete, "Sage Etendu", ".xls");
    }

    @Override
    protected int fetchData(Date from, Date to, SQLRow selectedJournal, boolean onlyNew) {

        final SQLTable tableEcriture = getEcritureT();

        // Fetch facture
        final SQLTable tableVF = tableEcriture.getTable("SAISIE_VENTE_FACTURE");
        SQLRowValues rowValsVF = new SQLRowValues(tableVF);
        rowValsVF.put("ID", null);
        rowValsVF.put("DATE", null);
        rowValsVF.put("NOM", null);
        rowValsVF.put("NUMERO", null);

        SQLRowValues rowValsMdr = rowValsVF.putRowValues("ID_MODE_REGLEMENT");

        rowValsMdr.putNulls(rowValsMdr.getTable().getFieldsName());
        rowValsMdr.putRowValues("ID_TYPE_REGLEMENT").putNulls("NOM");

        if (tableVF.contains("ID_ABONNEMENT")) {
            rowValsVF.putRowValues("ID_ABONNEMENT").putNulls("DATE_DEBUT_FACTURE", "DATE_FIN_FACTURE");
        }
        SQLRowValuesListFetcher fetcherVF = SQLRowValuesListFetcher.create(rowValsVF);
        List<SQLRowValues> resultVF = fetcherVF.fetch(new Where(tableVF.getField("DATE"), from, to));

        for (SQLRowValues sqlRowValues : resultVF) {
            this.mapFacture.put(sqlRowValues.getID(), sqlRowValues);
        }

        // Fetch avoirs
        final SQLTable tableAvoir = tableEcriture.getTable("AVOIR_CLIENT");
        SQLRowValues rowValsAvoir = new SQLRowValues(tableAvoir);
        rowValsAvoir.put("ID", null);
        rowValsAvoir.put("DATE", null);
        rowValsAvoir.put("NOM", null);
        rowValsAvoir.put("NUMERO", null);
        SQLRowValues rowValsMdrAvoir = rowValsAvoir.putRowValues("ID_MODE_REGLEMENT");

        rowValsMdrAvoir.putNulls(rowValsMdrAvoir.getTable().getFieldsName());
        rowValsMdrAvoir.putRowValues("ID_TYPE_REGLEMENT").putNulls("NOM");

        SQLRowValuesListFetcher fetcherAvoir = SQLRowValuesListFetcher.create(rowValsAvoir);
        List<SQLRowValues> resultAvoir = fetcherAvoir.fetch(new Where(tableAvoir.getField("DATE"), from, to));

        for (SQLRowValues sqlRowValues : resultAvoir) {
            this.mapAvoir.put(sqlRowValues.getID(), sqlRowValues);
        }

        final SQLTable tableCompte = tableEcriture.getForeignTable("ID_COMPTE_PCE");

        SQLRowValues rowValsFetch = new SQLRowValues(tableEcriture);
        rowValsFetch.putNulls("NOM", "DATE", "DEBIT", "CREDIT");
        if (tableEcriture.contains("CODE_CLIENT")) {
            rowValsFetch.put("CODE_CLIENT", null);
        }

        rowValsFetch.putRowValues("ID_MOUVEMENT").putNulls("NUMERO", "SOURCE", "IDSOURCE").putRowValues("ID_PIECE").put("NOM", null);

        rowValsFetch.putRowValues("ID_COMPTE_PCE").putNulls("NUMERO", "NOM");
        rowValsFetch.putRowValues("ID_JOURNAL").putNulls("CODE", "NOM");

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowValsFetch);
        fetcher.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                input.setWhere(getWhere(from, to, selectedJournal, onlyNew));
                input.addFieldOrder(input.getAlias(tableEcriture).getField("ID_MOUVEMENT"));
                input.addFieldOrder(input.getAlias(tableCompte).getField("NUMERO"));
                return input;
            }
        }, 0);

        final List<SQLRowValues> l = fetcher.fetch();
        this.data = l;
        return l == null ? 0 : l.size();
    }

    @Override
    protected void export(OutputStream bufOut) throws IOException {
        final List<SQLRowValues> l = this.data;
        final boolean containsCodeClient = getEcritureT().contains("CODE_CLIENT");
        final int size = l.size();

        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet("Ecritures");
        Row rowEntete = sheet.createRow(0);
        List<String> colName = Arrays.asList("Date d'écriture", "Code Journal", "Numéro de facture", "Référence", "Compte Général", "Compte Client", "Libellé de l'écriture", "Date d'échéance",
                "Mode de règlement", "Montant débiteur", "Montant Créditeur", "Date de début d'abonnement", "Date de fin d'abonnement");
        int colIndex = 0;
        for (String string : colName) {
            Cell cell = rowEntete.createCell(colIndex, CellType.STRING);
            cell.setCellValue(string);
            colIndex++;
        }

        CellStyle cellStyle = workBook.createCellStyle();
        CreationHelper createHelper = workBook.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));

        for (int i = 0; i < size; i++) {

            Row row = sheet.createRow(i + 1);

            final SQLRowValues rowLine = l.get(i);

            // Date
            final Date d = rowLine.getDate("DATE").getTime();
            Cell cellDate = row.createCell(0);
            cellDate.setCellStyle(cellStyle);
            cellDate.setCellValue(d);
            // Jrnl
            SQLRowAccessor rowJrnl = rowLine.getForeign("ID_JOURNAL");
            Cell cellJrnl = row.createCell(1);
            cellJrnl.setCellValue(StringUtils.toAsciiString(rowJrnl.getString("CODE").trim()));

            SQLRowAccessor rowValsSource = null;
            SQLRowAccessor rowMvt = rowLine.getForeign("ID_MOUVEMENT");
            if (rowMvt.getString("SOURCE").equals("AVOIR_CLIENT")) {
                rowValsSource = this.mapAvoir.get(rowMvt.getInt("IDSOURCE"));
            } else if (rowMvt.getString("SOURCE").equals("SAISIE_VENTE_FACTURE")) {
                rowValsSource = this.mapFacture.get(rowMvt.getInt("IDSOURCE"));
            }

            // Numero Piece
            Cell cellPiece = row.createCell(2);
            if (rowValsSource != null) {
                cellPiece.setCellValue(StringUtils.toAsciiString(rowValsSource.getString("NUMERO")));
            }

            // Ref vide
            Cell cellRef = row.createCell(3);
            cellRef.setCellValue("");

            // N° Compte
            final String cpt = rowLine.getForeign("ID_COMPTE_PCE").getString("NUMERO").trim();
            Cell cellCpt = row.createCell(4, CellType.STRING);
            cellCpt.setCellValue(getFormattedCompte(cpt));

            Cell cellCodeClient = row.createCell(5);
            if (containsCodeClient) {
                // Code Client
                String codeClient = "";
                if (cpt.startsWith("41")) {
                    codeClient = rowLine.getString("CODE_CLIENT");
                }
                cellCodeClient.setCellValue(StringUtils.toAsciiString(codeClient));
            }

            // Libellé
            Cell cellLib = row.createCell(6);
            cellLib.setCellValue(StringUtils.toAsciiString(rowLine.getString("NOM").trim()));

            Cell cellDateEch = row.createCell(7);
            Cell cellTypeRegl = row.createCell(8);
            if (rowValsSource != null && cpt.startsWith("41")) {
                final SQLRowAccessor foreignMdr = rowValsSource.getForeign("ID_MODE_REGLEMENT");
                Date ech = ModeDeReglementSQLElement.calculDate(foreignMdr, rowValsSource.getDate("DATE").getTime());
                cellDateEch.setCellValue(ech);
                cellDateEch.setCellStyle(cellStyle);
                cellTypeRegl.setCellValue(foreignMdr.getForeign("ID_TYPE_REGLEMENT").getString("NOM"));
            }

            // Debit
            Cell cellDebit = row.createCell(9);
            final Long debit = rowLine.getLong("DEBIT");
            cellDebit.setCellValue(debit / 100.0D);
            // Credit
            final Long credit = rowLine.getLong("CREDIT");
            Cell cellCredit = row.createCell(10);
            cellCredit.setCellValue(credit / 100.0D);

            Cell cellDebAbo = row.createCell(11);
            Cell cellFinAbo = row.createCell(12);

            if (rowValsSource != null && rowValsSource.contains("ID_ABONNEMENT") && rowValsSource.getTable().getName().equals("SAISIE_VENTE_FACTURE") && cpt.startsWith("41")) {
                SQLRowAccessor rowValsAbo = rowValsSource.getForeign("ID_ABONNEMENT");
                if (rowValsAbo != null && !rowValsAbo.isUndefined()) {
                    final Calendar calDeb = rowValsAbo.getDate("DATE_DEBUT_FACTURE");
                    if (calDeb != null) {
                        cellDebAbo.setCellValue(calDeb.getTime());
                        cellDebAbo.setCellStyle(cellStyle);
                    }

                    final Calendar calFin = rowValsAbo.getDate("DATE_FIN_FACTURE");
                    if (calFin != null) {
                        cellFinAbo.setCellValue(calFin.getTime());
                        cellFinAbo.setCellStyle(cellStyle);
                    }
                }
            }

        }
        workBook.write(bufOut);
        workBook.close();
    }

    private void exportTAB(OutputStream bufOut) throws IOException {
        final List<SQLRowValues> l = this.data;
        final boolean containsCodeClient = getEcritureT().contains("CODE_CLIENT");
        final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        final int size = l.size();
        for (int i = 0; i < size; i++) {

            // Ligne à insérer dans le fichier
            final StringBuilder line = new StringBuilder();

            final SQLRowValues rowLine = l.get(i);

            // Date
            final Date d = rowLine.getDate("DATE").getTime();
            line.append(dateFormat.format(d));
            line.append('\t');
            // Jrnl
            SQLRowAccessor rowJrnl = rowLine.getForeign("ID_JOURNAL");
            line.append(StringUtils.toAsciiString(rowJrnl.getString("CODE").trim()));
            line.append('\t');

            SQLRowAccessor rowValsSource = null;
            SQLRowAccessor rowMvt = rowLine.getForeign("ID_MOUVEMENT");
            if (rowMvt.getString("SOURCE").equals("AVOIR_CLIENT")) {
                rowValsSource = this.mapAvoir.get(rowMvt.getInt("IDSOURCE"));
            } else if (rowMvt.getString("SOURCE").equals("SAISIE_VENTE_FACTURE")) {
                rowValsSource = this.mapFacture.get(rowMvt.getInt("IDSOURCE"));
            }

            // Numero Piece
            if (rowValsSource != null) {
                line.append(StringUtils.toAsciiString(rowValsSource.getString("NUMERO")));
            }
            line.append('\t');

            // Référence
            // if (rowValsSource != null) {
            // line.append(StringUtils.toAsciiString(rowValsSource.getString("NOM")));
            // }
            line.append('\t');

            // N° Compte
            final String cpt = rowLine.getForeign("ID_COMPTE_PCE").getString("NUMERO").trim();
            line.append(getFormattedCompte(cpt));
            line.append('\t');

            if (containsCodeClient) {
                // Code Client
                String codeClient = "";
                if (cpt.startsWith("41")) {
                    codeClient = rowLine.getString("CODE_CLIENT");
                }
                line.append(StringUtils.toAsciiString(codeClient));
                line.append('\t');
            }

            // Libellé
            line.append(StringUtils.toAsciiString(rowLine.getString("NOM").trim()));
            line.append('\t');

            if (rowValsSource != null && cpt.startsWith("41")) {
                final SQLRowAccessor foreignMdr = rowValsSource.getForeign("ID_MODE_REGLEMENT");
                Date ech = ModeDeReglementSQLElement.calculDate(foreignMdr, rowValsSource.getDate("DATE").getTime());
                line.append(dateFormat.format(ech));
                line.append('\t');
                line.append(foreignMdr.getForeign("ID_TYPE_REGLEMENT").getString("NOM"));
                line.append('\t');
            } else {
                line.append('\t');
                line.append('\t');
            }

            // Debit
            final Long debit = rowLine.getLong("DEBIT");
            line.append(GestionDevise.currencyToString(debit.longValue(), false));
            line.append('\t');
            // Credit
            final Long credit = rowLine.getLong("CREDIT");
            line.append(GestionDevise.currencyToString(credit.longValue(), false));
            line.append('\t');

            if (rowValsSource != null && rowValsSource.contains("ID_ABONNEMENT") && rowValsSource.getTable().getName().equals("ID_SAISIE_VENTE_FACTURE") && cpt.startsWith("41")) {
                SQLRowAccessor rowValsAbo = rowValsSource.getForeign("ID_ABONNEMENT");
                if (rowValsAbo != null && !rowValsAbo.isUndefined()) {
                    final Calendar calDeb = rowValsAbo.getDate("DATE_DEBUT_FACTURE");
                    if (calDeb != null) {
                        line.append(dateFormat.format(calDeb.getTime()));
                    }
                    line.append('\t');
                    final Calendar calFin = rowValsAbo.getDate("DATE_FIN_FACTURE");
                    if (calFin != null) {
                        line.append(dateFormat.format(calFin.getTime()));
                    }
                    line.append('\t');
                }
            } else {
                line.append('\t');
                line.append('\t');
            }

            line.append('\r');
            line.append('\n');
            bufOut.write(line.toString().getBytes(StringUtils.Cp1252));
        }
    }
}
Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/panel/compta/ExportSageEtendu.java - Rev 177
