KostZuweisungExport.java

/////////////////////////////////////////////////////////////////////////////
//
// Project ProjectForge Community Edition
//         www.projectforge.org
//
// Copyright (C) 2001-2014 Kai Reinhard (k.reinhard@micromata.de)
//
// ProjectForge is dual-licensed.
//
// This community edition 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; version 3 of the License.
//
// This community edition 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/.
//
/////////////////////////////////////////////////////////////////////////////

package org.projectforge.fibu.kost;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.hibernate.criterion.Order;
import org.projectforge.common.CurrencyHelper;
import org.projectforge.common.NumberHelper;
import org.projectforge.common.StringHelper;
import org.projectforge.core.QueryFilter;
import org.projectforge.excel.ContentProvider;
import org.projectforge.excel.ExportColumn;
import org.projectforge.excel.ExportSheet;
import org.projectforge.excel.ExportWorkbook;
import org.projectforge.excel.I18nExportColumn;
import org.projectforge.excel.PropertyMapping;
import org.projectforge.export.MyXlsContentProvider;
import org.projectforge.fibu.AbstractRechnungDO;
import org.projectforge.fibu.AbstractRechnungsPositionDO;
import org.projectforge.fibu.EingangsrechnungDO;
import org.projectforge.fibu.EingangsrechnungsPositionDO;
import org.projectforge.fibu.KontoCache;
import org.projectforge.fibu.KontoDO;
import org.projectforge.fibu.KontoDao;
import org.projectforge.fibu.ProjektFormatter;
import org.projectforge.fibu.RechnungDO;
import org.projectforge.fibu.RechnungsPositionDO;
import org.projectforge.registry.Registry;
import org.projectforge.user.PFUserContext;

/**
 * For excel export.
 * 
 * @author Kai Reinhard (k.reinhard@micromata.de)
 */
public class KostZuweisungExport
{
  public static final KostZuweisungExport instance = new KostZuweisungExport();

  private class MyContentProvider extends MyXlsContentProvider
  {
    public MyContentProvider(final ExportWorkbook workbook)
    {
      super(workbook);
    }

    @Override
    public ContentProvider newInstance()
    {
      return new MyContentProvider(this.workbook);
    }
  };

  private static final org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(KostZuweisungExport.class);

  private enum InvoicesCol
  {
    BRUTTO("fibu.common.brutto", MyXlsContentProvider.LENGTH_CURRENCY), //
    VAT("fibu.common.vat", MyXlsContentProvider.LENGTH_BOOLEAN), //
    KONTO("fibu.buchungssatz.konto", 14), //
    REFERENZ("fibu.common.reference", MyXlsContentProvider.LENGTH_STD), //
    DATE("date", MyXlsContentProvider.LENGTH_DATE), //
    GEGENKONTO("fibu.buchungssatz.gegenKonto", 14), //
    KOST1("fibu.kost1", MyXlsContentProvider.LENGTH_KOSTENTRAEGER), //
    KOST2("fibu.kost2", MyXlsContentProvider.LENGTH_KOSTENTRAEGER), //
    TEXT("description", MyXlsContentProvider.LENGTH_EXTRA_LONG), //
    KORREKTUR("fibu.common.fehlBetrag", MyXlsContentProvider.LENGTH_CURRENCY);

    final String theTitle;

    final int width;

    InvoicesCol(final String theTitle, final int width)
    {
      this.theTitle = theTitle;
      this.width = (short) width;
    }
  }

  /**
   * Export all cost assignements of the given invoices as excel list.
   * @param list
   * @return
   */
  public byte[] exportRechnungen(final List< ? extends AbstractRechnungDO< ? extends AbstractRechnungsPositionDO>> list,
      final String sheetTitle, final KontoCache kontoCache)
  {
    final List<KostZuweisungDO> zuweisungen = new ArrayList<KostZuweisungDO>();
    for (final AbstractRechnungDO< ? > rechnung : list) {
      if (rechnung.getPositionen() != null) {
        for (final AbstractRechnungsPositionDO position : rechnung.getPositionen()) {
          if (CollectionUtils.isNotEmpty(position.getKostZuweisungen()) == true) {
            for (final KostZuweisungDO zuweisung : position.getKostZuweisungen()) {
              if (NumberHelper.isZeroOrNull(zuweisung.getBrutto()) == true) {
                // Skip entries with zero amounts.
                continue;
              }
              zuweisungen.add(zuweisung);
            }
          } else {
            final KostZuweisungDO zuweisung = new KostZuweisungDO();
            if (position instanceof RechnungsPositionDO) {
              zuweisung.setRechnungsPosition((RechnungsPositionDO) position);
            } else {
              zuweisung.setEingangsrechnungsPosition((EingangsrechnungsPositionDO) position);
            }
            zuweisungen.add(zuweisung);
          }
        }
      }
    }
    return export(zuweisungen, sheetTitle, kontoCache);
  }

  /**
   * Exports the filtered list as table.
   */
  public byte[] export(final List<KostZuweisungDO> list, final String sheetTitle, final KontoCache kontoCache)
  {
    log.info("Exporting kost zuweisung list.");
    final ExportWorkbook xls = new ExportWorkbook();
    final ContentProvider contentProvider = new MyContentProvider(xls);
    // create a default Date format and currency column
    xls.setContentProvider(contentProvider);

    final ExportSheet sheet = xls.addSheet(sheetTitle);
    sheet.createFreezePane(0, 1);

    final ExportColumn[] cols = new ExportColumn[InvoicesCol.values().length];
    int i = 0;
    for (final InvoicesCol col : InvoicesCol.values()) {
      cols[i++] = new I18nExportColumn(col, col.theTitle, col.width);
    }

    // column property names
    sheet.setColumns(cols);

    final ContentProvider sheetProvider = sheet.getContentProvider();
    sheetProvider.putFormat(InvoicesCol.BRUTTO, "#,##0.00;[Red]-#,##0.00");
    sheetProvider.putFormat(InvoicesCol.KORREKTUR, "#,##0.00;[Red]-#,##0.00");
    sheetProvider.putFormat(InvoicesCol.KOST1, "#");
    sheetProvider.putFormat(InvoicesCol.KOST2, "#");
    sheetProvider.putFormat(InvoicesCol.DATE, "dd.MM.yyyy");

    final PropertyMapping mapping = new PropertyMapping();
    for (final KostZuweisungDO zuweisung : list) {
      final AbstractRechnungsPositionDO position;
      final AbstractRechnungDO< ? > rechnung;
      final String referenz;
      final String text;
      if (zuweisung.getRechnungsPosition() != null) {
        position = zuweisung.getRechnungsPosition();
        rechnung = ((RechnungsPositionDO) position).getRechnung();
        final RechnungDO r = (RechnungDO) rechnung;
        referenz = String.valueOf(r.getNummer());
        text = ProjektFormatter.formatProjektKundeAsString(r.getProjekt(), r.getKunde(), r.getKundeText());
      } else {
        position = zuweisung.getEingangsrechnungsPosition();
        rechnung = ((EingangsrechnungsPositionDO) position).getEingangsrechnung();
        final EingangsrechnungDO r = (EingangsrechnungDO) rechnung;
        referenz = r.getReferenz();
        text = r.getKreditor();
      }
      final BigDecimal grossSum = position.getBruttoSum();

      BigDecimal korrektur = null;
      if (grossSum.compareTo(position.getKostZuweisungGrossSum()) != 0) {
        korrektur = CurrencyHelper.getGrossAmount(position.getKostZuweisungNetFehlbetrag(), position.getVat());
        if (NumberHelper.isZeroOrNull(korrektur) == true) {
          korrektur = null;
        }
      }
      mapping.add(InvoicesCol.BRUTTO, zuweisung.getBrutto());
      mapping.add(InvoicesCol.VAT, NumberHelper.isNotZero(position.getVat()));
      Integer kontoNummer = null;
      if (rechnung instanceof RechnungDO) {
        final KontoDO konto = kontoCache.getKonto(((RechnungDO) rechnung));
        if (konto != null) {
          kontoNummer = konto.getNummer();
        }
      } else if (rechnung instanceof EingangsrechnungDO) {
        final Integer kontoId = ((EingangsrechnungDO) rechnung).getKontoId();
        if (kontoId != null) {
          final KontoDO konto = kontoCache.getKonto(kontoId);
          if (konto != null) {
            kontoNummer = konto.getNummer();
          }
        }
      }
      mapping.add(InvoicesCol.KONTO, kontoNummer != null ? kontoNummer : "");
      mapping.add(InvoicesCol.REFERENZ, StringHelper.removeNonDigitsAndNonASCIILetters(referenz));
      mapping.add(InvoicesCol.DATE, rechnung.getDatum());
      mapping.add(InvoicesCol.GEGENKONTO, "");
      mapping.add(InvoicesCol.KOST1, zuweisung.getKost1() != null ? zuweisung.getKost1().getNummer() : "");
      mapping.add(InvoicesCol.KOST2, zuweisung.getKost2() != null ? zuweisung.getKost2().getNummer() : "");
      mapping.add(InvoicesCol.TEXT, text);
      mapping.add(InvoicesCol.KORREKTUR, korrektur);
      sheet.addRow(mapping.getMapping(), 0);
    }
    addAccounts(xls, contentProvider);
    return xls.getAsByteArray();
  }

  private enum AccountsCol
  {
    NUMBER("fibu.konto.nummer", 16), //
    NAME("fibu.konto.bezeichnung", MyXlsContentProvider.LENGTH_STD), //
    STATUS("status", 14), //
    DATE_OF_LAST_MODIFICATION("lastUpdate", MyXlsContentProvider.LENGTH_TIMESTAMP), //
    DATE_OF_CREATION("created", MyXlsContentProvider.LENGTH_TIMESTAMP), //
    DESCRIPTION("comment", MyXlsContentProvider.LENGTH_EXTRA_LONG);

    final String theTitle;

    final int width;

    AccountsCol(final String theTitle, final int width)
    {
      this.theTitle = theTitle;
      this.width = (short) width;
    }
  }

  private void addAccounts(final ExportWorkbook xls, final ContentProvider contentProvider)
  {
    final ExportSheet sheet = xls.addSheet(PFUserContext.getLocalizedString("fibu.konto.konten"));
    sheet.createFreezePane(0, 1);

    final ExportColumn[] cols = new ExportColumn[AccountsCol.values().length];
    int i = 0;
    for (final AccountsCol col : AccountsCol.values()) {
      cols[i++] = new I18nExportColumn(col, col.theTitle, col.width);
    }

    // column property names
    sheet.setColumns(cols);

    final ContentProvider sheetProvider = sheet.getContentProvider();
    sheetProvider.putFormat(AccountsCol.DATE_OF_LAST_MODIFICATION, "dd.MM.yyyy HH:mm");
    sheetProvider.putFormat(AccountsCol.DATE_OF_CREATION, "dd.MM.yyyy HH:mm");
    sheetProvider.putFormat(AccountsCol.NUMBER, "#");

    final KontoDao kontoDao = Registry.instance().getDao(KontoDao.class);
    final QueryFilter filter = new QueryFilter();
    filter.addOrder(Order.desc("lastUpdate"));
    final List<KontoDO> list = kontoDao.getList(filter);

    final PropertyMapping mapping = new PropertyMapping();
    for (final KontoDO konto : list) {
      mapping.add(AccountsCol.NUMBER, konto.getNummer());
      mapping.add(AccountsCol.NAME, konto.getBezeichnung());
      mapping.add(AccountsCol.DATE_OF_LAST_MODIFICATION, konto.getLastUpdate());
      mapping.add(AccountsCol.DATE_OF_CREATION, konto.getCreated());
      String status = "";
      if (konto.isDeleted() == true) {
        status = PFUserContext.getLocalizedString("deleted");
      } else if (konto.getStatus() != null) {
        status = PFUserContext.getLocalizedString(konto.getStatus().getI18nKey());
      }
      mapping.add(AccountsCol.STATUS, status);
      mapping.add(AccountsCol.DESCRIPTION, konto.getDescription());
      sheet.addRow(mapping.getMapping(), 0);
    }
  }
}