nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java Source code

Java tutorial

Introduction

Here is the source code for nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

Source

package nc.noumea.mairie.appock.services.impl;

/*-
 * #%L
 * Logiciel de Gestion des approvisionnements et des stocks des fournitures administratives de la Mairie de Nouma
 * %%
 * Copyright (C) 2017 Mairie de Nouma, Nouvelle-Caldonie
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.context.annotation.ScopedProxyMode;
import org.zkoss.zul.Messagebox;

import nc.noumea.mairie.appock.comparator.ArticleCatalogueComparator;
import nc.noumea.mairie.appock.core.entity.AbstractEntity;
import nc.noumea.mairie.appock.core.security.AppUser;
import nc.noumea.mairie.appock.core.utility.AppockUtil;
import nc.noumea.mairie.appock.entity.*;
import nc.noumea.mairie.appock.repositories.AppUserRepository;
import nc.noumea.mairie.appock.repositories.MouvementStockRepository;
import nc.noumea.mairie.appock.services.*;
import nc.noumea.mairie.appock.services.CommandeService;

@org.springframework.stereotype.Service("exportExcelService")
@Scope(value = "singleton", proxyMode = ScopedProxyMode.TARGET_CLASS)
public class ExportExcelServiceImpl implements ExportExcelService {

    private static final int LIGNE_NUMERO_MARCHE_ET_SOLDE_COMMANDE = 5;

    @Autowired
    CommandeService commandeService;

    @Autowired
    CatalogueService catalogueService;

    @Autowired
    DownloadService downloadService;

    @Autowired
    AppUserRepository appUserRepository;

    @Autowired
    ConfigService configService;

    @Autowired
    MouvementStockRepository mouvementStockRepository;

    @Override
    public void genereExcelFournisseur(Commande commande) throws IOException {
        List<String> listeMessageErreurAdresseService = construitMessageErreurAdresseService(commande);
        if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(listeMessageErreurAdresseService)) {
            Messagebox.show(StringUtils.join(listeMessageErreurAdresseService, "\n"), "Erreur d'adresse",
                    Messagebox.OK, Messagebox.ERROR);
            return;
        }

        Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map = commandeService
                .construitMapFournisseurServiceListeArticleDemande(commande);
        List<AbstractEntity> listeAbstractEntity = new ArrayList(map.keySet());
        Comparator abstractEntityComparator = Comparator
                .comparing((AbstractEntity abstractEntity) -> abstractEntity.getLibelleCourt());
        Collections.sort(listeAbstractEntity, abstractEntityComparator);

        File result = File.createTempFile(UUID.randomUUID().toString(), ".zip");
        FileOutputStream fos = new FileOutputStream(result);
        ZipOutputStream zos = new ZipOutputStream(fos);

        for (AbstractEntity abstractEntity : listeAbstractEntity) {
            String nomFichierSansExtension = construitNomFichier(abstractEntity);
            File tempFile = createExcelTempFile(map, abstractEntity, nomFichierSansExtension);
            addToZipFile(tempFile, nomFichierSansExtension + ".xlsx", zos);
            tempFile.delete();
        }

        zos.close();
        fos.close();

        downloadService.downloadToUser(result, "Fichiers fournisseurs.zip");
        result.delete();
    }

    private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map,
            AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException {
        Workbook wb = new XSSFWorkbook();
        Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity);

        for (Service service : mapServiceListeArticleDemande.keySet()) {
            Sheet sheet = wb
                    .createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt());
            sheet.protectSheet(configService.getMotDePasseVerrouExcel());

            construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet);
            remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet);

            // Taille automatique des colonnes selon le contenu
            for (int i = 0; i < 20; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768);
            }
        }
        // Ecriture dans le fichier
        File result = File.createTempFile(nomFichierSansExtension, ".xlsx");
        FileOutputStream fileStream = new FileOutputStream(result);
        wb.write(fileStream);
        return result;
    }

    private String construitNomFichier(AbstractEntity abstractEntity) {
        String nomFichier;
        if (abstractEntity instanceof SousMarche) {
            nomFichier = ((SousMarche) abstractEntity).getLibelle();
        } else if (abstractEntity instanceof Fournisseur) {
            nomFichier = ((Fournisseur) abstractEntity).getNom();
        } else {
            nomFichier = "SANS MARCHE NI FOURNISSEUR";
        }
        return AppockUtil.replaceAllSpecialCharacter(nomFichier);
    }

    private List<String> construitMessageErreurAdresseService(Commande commande) throws IOException {
        Map<Service, List<Demande>> map = commandeService.construitMapServiceListeDemande(commande);
        List<String> result = new ArrayList<>();

        for (Service service : map.keySet()) {
            if (service.getAdresse().isVide()) {
                result.add("Vous devez renseigner l'adresse du service " + service.getLibelleCourt());
            }
        }

        return result;
    }

    private void construitEntete(Workbook wb, Map<Service, List<ArticleDemande>> map, AbstractEntity abstractEntity,
            Service service, Sheet sheet) {
        construitEnteteService(wb, service, sheet);
        construitEnteteContactLivraison(wb, service, sheet);
        construitEnteteAdresseLivraison(wb, service, sheet);
        construitEnteteSoldeCommande(wb, sheet);
        construitEnteteNumeroMarcheEtSoldeCommande(wb, map, abstractEntity, service, sheet);
        construitEnteteMarche(wb, abstractEntity, sheet);
        construitEnteteArticle(wb, sheet);
    }

    private void construitEnteteService(Workbook wb, Service service, Sheet sheet) {
        Row row = createRowGeneric(sheet, 1, 500);
        Cell cell = row.createCell(1);
        cell.setCellValue(service.getLibelle());
        cell.setCellStyle(
                createCellWithBorderAndColor(wb, BorderStyle.MEDIUM, IndexedColors.LIGHT_TURQUOISE, true));
    }

    private void construitEnteteContactLivraison(Workbook wb, Service service, Sheet sheet) {
        AppUser appUser = appUserRepository.findFirstByServiceAndTitulaire(service, true);
        if (appUser == null) {
            return;
        }

        Row row = createRowGeneric(sheet, 2, 500);
        Cell cell = row.createCell(1);
        List<String> listeInfoContact = new ArrayList<>();
        listeInfoContact.add(appUser.getNomComplet());
        if (StringUtils.isNotBlank(service.getTelephone())) {
            listeInfoContact.add(service.getTelephone()); // potentiellement null
        }
        if (StringUtils.isNotBlank(appUser.getPoste())) {
            listeInfoContact.add("Poste " + appUser.getPoste());
        }

        cell.setCellValue(AppockUtil.joinListeStringNotBlank(listeInfoContact, " - "));
        cell.setCellStyle(createCellWithBorderAndColor(wb, null, null, true));
    }

    private void construitEnteteAdresseLivraison(Workbook wb, Service service, Sheet sheet) {
        Row row = createRowGeneric(sheet, 3, 500);
        Cell cell = row.createCell(1);
        cell.setCellValue(service.getAdresse().getReprMonoLigne());
        cell.setCellStyle(createCellWithBorderAndColor(wb, null, null, true));
    }

    private void construitEnteteSoldeCommande(Workbook wb, Sheet sheet) {
        Row row = createRowGeneric(sheet, 4, 500);
        Cell cell = row.createCell(6);
        cell.setCellValue("Montant de la commande");
        cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_TURQUOISE, false));
    }

    private void construitEnteteNumeroMarcheEtSoldeCommande(Workbook wb, Map<Service, List<ArticleDemande>> map,
            AbstractEntity abstractEntity, Service service, Sheet sheet) {
        Row row = createRowGeneric(sheet, LIGNE_NUMERO_MARCHE_ET_SOLDE_COMMANDE, 500);
        Cell cell = row.createCell(0);
        if (abstractEntity instanceof SousMarche) {
            cell.setCellValue("March Nouma n: " + ((SousMarche) abstractEntity).getNumero());
            cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.TAN, false));
        }

        // Cell Montant de la commande
        cell = row.createCell(6);

        int prixTotalService = 0;
        for (ArticleDemande articleDemande : map.get(service)) {
            prixTotalService += articleDemande.getTotalPrixCommande();
        }
        cell.setCellValue(prixTotalService);
        CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, null, false);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        cell.setCellStyle(style);
    }

    private void construitEnteteMarche(Workbook wb, AbstractEntity abstractEntity, Sheet sheet) {
        Row row = createRowGeneric(sheet, 6, 500);
        Cell cell = row.createCell(0);
        cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.YELLOW, false));

        if (abstractEntity instanceof SousMarche) {
            cell.setCellValue(((SousMarche) abstractEntity).getLibelle());
        }

        cell = row.createCell(1);
        cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.YELLOW, false));

        if (abstractEntity instanceof SousMarche) {
            cell.setCellValue(((SousMarche) abstractEntity).getFournisseur().getNom());
        } else if (abstractEntity instanceof Fournisseur) {
            cell.setCellValue(((Fournisseur) abstractEntity).getNom());
        } else {
            cell.setCellValue("SANS MARCHE NI FOURNISSEUR");
        }
    }

    private Row createRowGeneric(Sheet sheet, int ligne, Integer taille) {
        Row row = sheet.createRow(ligne);
        if (taille != null) {
            row.setHeight((short) taille.intValue());
        }

        return row;
    }

    private void construitEnteteArticle(Workbook wb, Sheet sheet) {
        Row row = createRowGeneric(sheet, 8, 500);
        row.createCell(0).setCellValue("Rfrence");
        row.createCell(1).setCellValue("Dsignation");
        row.createCell(2).setCellValue("Prix");
        row.createCell(3).setCellValue("Colisage");
        row.createCell(4).setCellValue("Commande");
        row.createCell(5).setCellValue("Total");

        CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.TAN, false);

        row.getCell(0).setCellStyle(style);
        row.getCell(1).setCellStyle(style);
        row.getCell(2).setCellStyle(style);
        row.getCell(3).setCellStyle(style);
        row.getCell(4).setCellStyle(style);
        row.getCell(5).setCellStyle(style);
    }

    private void remplitLigneArticle(Workbook wb, List<ArticleDemande> listeArticleDemande, Sheet sheet) {
        int numRow = 10;
        Map<ArticleCatalogue, Integer> mapArticleQuantite = new HashMap<>();

        for (ArticleDemande articleDemande : listeArticleDemande) {
            ArticleCatalogue articleCatalogue = articleDemande.getArticleCatalogue();
            Integer quantite = mapArticleQuantite.get(articleCatalogue);
            if (quantite != null) {
                mapArticleQuantite.replace(articleCatalogue, quantite + articleDemande.getQuantiteCommande());
            } else {
                mapArticleQuantite.put(articleCatalogue, articleDemande.getQuantiteCommande());
            }
        }

        List<ArticleCatalogue> listeArticleCatalogue = new ArrayList(mapArticleQuantite.keySet());
        Collections.sort(listeArticleCatalogue, new ArticleCatalogueComparator());

        for (ArticleCatalogue articleCatalogue : listeArticleCatalogue) {
            Row row = createRowGeneric(sheet, numRow, 500);
            row.createCell(0).setCellValue(articleCatalogue.getReference());
            row.createCell(1).setCellValue(articleCatalogue.getLibelle());
            row.createCell(2).setCellValue(articleCatalogue.getPrix());
            row.createCell(3).setCellValue(articleCatalogue.getLibelleColisage());
            row.createCell(4).setCellValue(mapArticleQuantite.get(articleCatalogue));

            String strFormula = "C" + (numRow + 1) + "*E" + (numRow + 1) + "";
            Cell cell = row.createCell(5);
            cell.setCellType(CellType.FORMULA);
            cell.setCellFormula(strFormula);

            CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW, false);
            CellStyle stylePrix = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW,
                    false);
            stylePrix.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));

            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            row.getCell(2).setCellStyle(stylePrix);
            row.getCell(3).setCellStyle(style);
            row.getCell(4).setCellStyle(style);
            row.getCell(5).setCellStyle(stylePrix);
            numRow++;
        }
    }

    private CellStyle createCellWithBorderAndColor(Workbook wb, BorderStyle borderStyle,
            IndexedColors indexedColors, boolean bold) {
        CellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        if (borderStyle != null) {
            style.setBorderBottom(borderStyle);
            style.setBorderTop(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
        }

        if (indexedColors != null) {
            style.setFillForegroundColor(indexedColors.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        if (bold) {
            Font font = wb.createFont();
            font.setBold(true);
            style.setFont(font);
        }

        return style;
    }

    private void addToZipFile(File file, String nomFichier, ZipOutputStream zos) throws IOException {
        FileInputStream fis = new FileInputStream(file);
        ZipEntry zipEntry = new ZipEntry(nomFichier);
        zos.putNextEntry(zipEntry);

        byte[] bytes = new byte[1024];
        int length;
        while ((length = fis.read(bytes)) >= 0) {
            zos.write(bytes, 0, length);
        }

        zos.closeEntry();
        fis.close();
    }

    @Override
    public void exportCatalogue(Catalogue catalogue) throws IOException {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Feuil1");

        construitEnteteExportCatalogue(wb, sheet);
        int i = 1;
        for (Famille famille : catalogue.getListeFamille()) {
            for (SousFamille sousFamille : famille.getListeSousFamille()) {
                for (ArticleCatalogue articleCatalogue : sousFamille.getListeArticleCatalogue()) {
                    remplitLigneArticleExportCatalogue(wb, sheet, articleCatalogue, i++);
                }
            }
        }

        // Taille automatique des colonnes selon le contenu
        for (int colonne = 0; colonne <= 12; colonne++) {
            sheet.autoSizeColumn(colonne);
            sheet.setColumnWidth(colonne, sheet.getColumnWidth(colonne) + 768);
        }

        // Ecriture dans le fichier
        File result = File.createTempFile("Export Catalogue", ".xlsx");
        FileOutputStream fileStream = new FileOutputStream(result);
        wb.write(fileStream);

        downloadService.downloadToUser(result,
                "Export Catalogue " + AppockUtil.replaceAllSpecialCharacter(catalogue.getLibelle()) + ".xlsx");
        result.delete();
    }

    private void construitEnteteExportCatalogue(Workbook wb, Sheet sheet) {
        Row row = createRowGeneric(sheet, 0, 1000);
        construitEnteteExportCatalogueGeneric(wb, row, "Famille", 0);
        construitEnteteExportCatalogueGeneric(wb, row, "Sous-Famille", 1);
        construitEnteteExportCatalogueGeneric(wb, row, "Rfrence", 2);
        construitEnteteExportCatalogueGeneric(wb, row, "Dsignation", 3);
        construitEnteteExportCatalogueGeneric(wb, row, "Prix unitaire", 4);
        construitEnteteExportCatalogueGeneric(wb, row, "Quantit", 5);
        construitEnteteExportCatalogueGeneric(wb, row, "Colisage", 6);
        construitEnteteExportCatalogueGeneric(wb, row, "March", 7);
        construitEnteteExportCatalogueGeneric(wb, row, "Sous-March", 8);
        construitEnteteExportCatalogueGeneric(wb, row, "Fournisseur", 9);
        construitEnteteExportCatalogueGeneric(wb, row, "Photo", 10);
        construitEnteteExportCatalogueGeneric(wb, row, "Lien vers site fournisseur", 11);
    }

    private void construitEnteteExportCatalogueGeneric(Workbook wb, Row row, String libelle, int colonne) {
        Cell cell = row.createCell(colonne);
        cell.setCellValue(libelle);
        cell.setCellStyle(
                createCellWithBorderAndColor(wb, BorderStyle.MEDIUM, IndexedColors.LIGHT_TURQUOISE, true));
    }

    private void remplitLigneArticleExportCatalogue(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue,
            int ligne) throws IOException {
        Row row = createRowGeneric(sheet, ligne, 1000);
        boolean mouvementStock = CollectionUtils
                .isNotEmpty(mouvementStockRepository.findAllByReference(articleCatalogue.getReference()));

        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousFamille().getFamille().getLibelle(),
                0, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getSousFamille().getLibelle(), 1,
                mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getReference(), 2, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getLibelle(), 3, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getPrix().toString(), 4, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row,
                articleCatalogue.getQuantiteColisage() != null ? articleCatalogue.getQuantiteColisage().toString()
                        : null,
                5, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row,
                articleCatalogue.getTypeColisage() != null ? articleCatalogue.getTypeColisage().toString() : null,
                6, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row,
                articleCatalogue.getSousMarche() != null ? articleCatalogue.getSousMarche().getMarche().getLibelle()
                        : null,
                7, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row,
                articleCatalogue.getSousMarche() != null ? articleCatalogue.getSousMarche().getLibelleCourt()
                        : null,
                8, mouvementStock);
        construitLigneExportCatalogueGeneric(wb, row,
                (articleCatalogue.getSousMarche() == null && articleCatalogue.getFournisseur() != null)
                        ? articleCatalogue.getFournisseur().getNom()
                        : null,
                9, mouvementStock);

        if (articleCatalogue.getPhotoArticleCatalogue() != null) {
            insertPhotoArticleCatalogueInCell(wb, sheet, articleCatalogue, ligne, row, mouvementStock);
        }

        construitLigneExportCatalogueGeneric(wb, row, articleCatalogue.getLienFournisseur(), 11, mouvementStock);
    }

    private void insertPhotoArticleCatalogueInCell(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue,
            int ligne, Row row, boolean mouvementStock) throws IOException {
        File fichierPhotoArticleCatalogue = catalogueService
                .getFilePieceJointe(articleCatalogue.getPhotoArticleCatalogue());
        byte[] bytes = Files.readAllBytes(fichierPhotoArticleCatalogue.toPath());
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        Drawing drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = wb.getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(10);
        anchor.setRow1(ligne);
        anchor.setCol2(11);
        anchor.setRow2(ligne + 1);
        drawing.createPicture(anchor, pictureIdx);
        construitLigneExportCatalogueGeneric(wb, row, null, 10, mouvementStock);
    }

    private void construitLigneExportCatalogueGeneric(Workbook wb, Row row, String libelle, int colonne,
            boolean mouvementStock) {
        Cell cell = row.createCell(colonne);
        cell.setCellValue(libelle);
        cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.MEDIUM,
                mouvementStock ? IndexedColors.LIGHT_GREEN : null, false));
    }
}