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

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

import org.apache.commons.collections.CollectionUtils;
import org.projectforge.access.AccessChecker;
import org.projectforge.common.DateFormatType;
import org.projectforge.common.DateFormats;
import org.projectforge.common.NumberHelper;
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.registry.Registry;
import org.projectforge.task.TaskNode;
import org.projectforge.task.TaskTree;
import org.projectforge.user.PFUserContext;
import org.projectforge.user.PFUserDO;

/**
 * For excel export.
 * 
 * @author Kai Reinhard (k.reinhard@micromata.de)
 */
public class OrderExport
{
  private class MyContentProvider extends MyXlsContentProvider
  {
    public MyContentProvider(final ExportWorkbook workbook)
    {
      super(workbook);
    }

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

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

  protected AccessChecker accessChecker;

  private AuftragDao auftragDao;

  private RechnungCache rechnungCache;

  private TaskTree taskTree;

  private enum OrderCol
  {
    NUMMER, NUMBER_OF_POSITIONS, DATE, ORDER_DATE, STATUS, PROJECT, PROJECT_CUSTOMER, TITLE, NETSUM, INVOICED, TO_BE_INVOICED, COMPLETELY_INVOICED, INVOICES, CONTACT_PERSON, REFERENCE, COMMENT;
  }

  private enum PosCol
  {
    NUMBER, POS_NUMBER, DATE, PROJECT, ORDER_TITLE, TITLE, TYPE, STATUS, PERSON_DAYS, NETSUM, INVOICED, TO_BE_INVOICED, COMPLETELY_INVOICED, INVOICES, PERIOD_OF_PERFORMANCE_BEGIN, PERIOD_OF_PERFORMANCE_END, TASK, COMMENT;
  }

  private ExportColumn[] createOrderColumns()
  {
    return new ExportColumn[] { //
        new I18nExportColumn(OrderCol.NUMMER, "fibu.auftrag.nummer.short", MyXlsContentProvider.LENGTH_ID),
        new I18nExportColumn(OrderCol.NUMBER_OF_POSITIONS, "fibu.auftrag.positions", MyXlsContentProvider.LENGTH_ID),
        new I18nExportColumn(OrderCol.DATE, "fibu.auftrag.datum", MyXlsContentProvider.LENGTH_DATE),
        new I18nExportColumn(OrderCol.ORDER_DATE, "fibu.auftrag.beauftragungsdatum", MyXlsContentProvider.LENGTH_DATE),
        new I18nExportColumn(OrderCol.STATUS, "status", 10),
        new I18nExportColumn(OrderCol.PROJECT, "fibu.projekt", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.PROJECT_CUSTOMER, "fibu.kunde", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.TITLE, "fibu.auftrag.titel", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.NETSUM, "fibu.auftrag.nettoSumme", MyXlsContentProvider.LENGTH_CURRENCY), //
        new I18nExportColumn(OrderCol.INVOICED, "fibu.fakturiert", MyXlsContentProvider.LENGTH_CURRENCY), //
        new I18nExportColumn(OrderCol.TO_BE_INVOICED, "fibu.tobeinvoiced", MyXlsContentProvider.LENGTH_CURRENCY),
        new I18nExportColumn(OrderCol.COMPLETELY_INVOICED, "fibu.auftrag.vollstaendigFakturiert", MyXlsContentProvider.LENGTH_BOOLEAN),
        new I18nExportColumn(OrderCol.INVOICES, "fibu.rechnungen", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.CONTACT_PERSON, "contactPerson", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.REFERENCE, "fibu.common.reference", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(OrderCol.COMMENT, "comment", MyXlsContentProvider.LENGTH_COMMENT)};
  }

  private void addOrderMapping(final PropertyMapping mapping, final AuftragDO order, final Object... params)
  {
    auftragDao.calculateInvoicedSum(order);
    mapping.add(OrderCol.NUMMER, order.getNummer());
    mapping.add(OrderCol.NUMBER_OF_POSITIONS, "#" + (order.getPositionen() != null ? order.getPositionen().size() : "0"));
    mapping.add(OrderCol.DATE, order.getAngebotsDatum());
    mapping.add(OrderCol.ORDER_DATE, order.getBeauftragungsDatum());
    mapping.add(OrderCol.STATUS,
        order.getAuftragsStatus() != null ? PFUserContext.getLocalizedString(order.getAuftragsStatus().getI18nKey()) : "");
    mapping.add(OrderCol.PROJECT, order.getProjektAsString());
    final ProjektDO project = order.getProjekt();
    final String projectCustomer = KundeFormatter.formatKundeAsString(project != null ? project.getKunde() : null, order.getKundeText());
    mapping.add(OrderCol.PROJECT_CUSTOMER, projectCustomer);
    mapping.add(OrderCol.TITLE, order.getTitel());
    final BigDecimal netSum = order.getNettoSumme() != null ? order.getNettoSumme() : BigDecimal.ZERO;
    final BigDecimal invoicedSum = order.getFakturiertSum() != null ? order.getFakturiertSum() : BigDecimal.ZERO;
    final BigDecimal toBeInvoicedSum = netSum.subtract(invoicedSum);
    mapping.add(OrderCol.NETSUM, netSum);
    addCurrency(mapping, OrderCol.INVOICED, invoicedSum);
    addCurrency(mapping, OrderCol.TO_BE_INVOICED, toBeInvoicedSum);
    mapping.add(OrderCol.COMPLETELY_INVOICED, order.isVollstaendigFakturiert() == true ? "x" : "");
    final Set<RechnungsPositionVO> invoicePositions = rechnungCache.getRechnungsPositionVOSetByAuftragId(order.getId());
    mapping.add(OrderCol.INVOICES, getInvoices(invoicePositions));
    final PFUserDO contactPerson = Registry.instance().getUserGroupCache().getUser(order.getContactPersonId());
    mapping.add(OrderCol.CONTACT_PERSON, contactPerson != null ? contactPerson.getFullname() : "");
    mapping.add(OrderCol.REFERENCE, order.getReferenz());
    mapping.add(OrderCol.COMMENT, order.getBemerkung());
  }

  private ExportColumn[] createPosColumns()
  {
    return new ExportColumn[] { //
        new I18nExportColumn(PosCol.NUMBER, "fibu.auftrag.nummer.short", MyXlsContentProvider.LENGTH_ID),
        new I18nExportColumn(PosCol.POS_NUMBER, "fibu.auftrag.position", 5),
        new I18nExportColumn(PosCol.DATE, "fibu.auftrag.datum", MyXlsContentProvider.LENGTH_DATE),
        new I18nExportColumn(PosCol.PROJECT, "fibu.projekt", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(PosCol.ORDER_TITLE, "fibu.auftrag.titel", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(PosCol.TITLE, "fibu.auftrag.titel", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(PosCol.TYPE, "fibu.auftrag.position.art", 10),
        new I18nExportColumn(PosCol.STATUS, "status", 10),
        new I18nExportColumn(PosCol.PERSON_DAYS, "projectmanagement.personDays.short", 8),
        new I18nExportColumn(PosCol.NETSUM, "fibu.auftrag.nettoSumme", MyXlsContentProvider.LENGTH_CURRENCY), //
        new I18nExportColumn(PosCol.INVOICED, "fibu.fakturiert", MyXlsContentProvider.LENGTH_CURRENCY), //
        new I18nExportColumn(PosCol.TO_BE_INVOICED, "fibu.tobeinvoiced", MyXlsContentProvider.LENGTH_CURRENCY),
        new I18nExportColumn(PosCol.COMPLETELY_INVOICED, "fibu.auftrag.vollstaendigFakturiert", MyXlsContentProvider.LENGTH_BOOLEAN),
        new I18nExportColumn(PosCol.INVOICES, "fibu.rechnungen", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(PosCol.PERIOD_OF_PERFORMANCE_BEGIN, null, MyXlsContentProvider.LENGTH_DATE),
        new I18nExportColumn(PosCol.PERIOD_OF_PERFORMANCE_END, null, MyXlsContentProvider.LENGTH_DATE),
        new I18nExportColumn(PosCol.TASK, "task", MyXlsContentProvider.LENGTH_STD),
        new I18nExportColumn(PosCol.COMMENT, "comment", MyXlsContentProvider.LENGTH_COMMENT)};
  }

  private void addPosMapping(final PropertyMapping mapping, final AuftragDO order, final AuftragsPositionDO pos, final Object... params)
  {
    mapping.add(PosCol.NUMBER, order.getNummer());
    mapping.add(PosCol.POS_NUMBER, "#" + pos.getNumber());
    mapping.add(PosCol.DATE, order.getAngebotsDatum());
    mapping.add(PosCol.PROJECT, order.getProjektAsString());
    mapping.add(PosCol.ORDER_TITLE, order.getTitel());
    mapping.add(PosCol.TITLE, pos.getTitel());
    mapping.add(PosCol.TYPE, pos.getArt() != null ? PFUserContext.getLocalizedString(pos.getArt().getI18nKey()) : "");
    mapping.add(PosCol.STATUS, pos.getStatus() != null ? PFUserContext.getLocalizedString(pos.getStatus().getI18nKey()) : "");
    mapping.add(PosCol.PERSON_DAYS, pos.getPersonDays());
    final BigDecimal netSum = pos.getNettoSumme() != null ? pos.getNettoSumme() : BigDecimal.ZERO;
    final BigDecimal invoicedSum = pos.getFakturiertSum() != null ? pos.getFakturiertSum() : BigDecimal.ZERO;
    final BigDecimal toBeInvoicedSum = netSum.subtract(invoicedSum);
    mapping.add(PosCol.NETSUM, netSum);
    addCurrency(mapping, PosCol.INVOICED, invoicedSum);
    addCurrency(mapping, PosCol.TO_BE_INVOICED, toBeInvoicedSum);
    mapping.add(PosCol.COMPLETELY_INVOICED, pos.isVollstaendigFakturiert() == true ? "x" : "");
    final Set<RechnungsPositionVO> invoicePositions = rechnungCache.getRechnungsPositionVOSetByAuftragsPositionId(pos.getId());
    mapping.add(PosCol.INVOICES, getInvoices(invoicePositions));
    mapping.add(PosCol.PERIOD_OF_PERFORMANCE_BEGIN, pos.getPeriodOfPerformanceBegin());
    mapping.add(PosCol.PERIOD_OF_PERFORMANCE_END, pos.getPeriodOfPerformanceEnd());
    final TaskNode node = taskTree.getTaskNodeById(pos.getTaskId());
    mapping.add(PosCol.TASK, node != null ? node.getTask().getTitle() : "");
    mapping.add(PosCol.COMMENT, pos.getBemerkung());
  }

  private String getInvoices(final Set<RechnungsPositionVO> invoicePositions)
  {
    final StringBuilder sb = new StringBuilder();
    if (invoicePositions != null) {
      String delimiter = "";
      for (final RechnungsPositionVO invoicePos : invoicePositions) {
        sb.append(delimiter).append(invoicePos.getRechnungNummer());
        delimiter = ", ";
      }
    }
    return sb.toString();
  }

  private void addCurrency(final PropertyMapping mapping, final Enum< ? > col, final BigDecimal value)
  {
    if (NumberHelper.isNotZero(value) == true) {
      mapping.add(col, value);
    } else {
      mapping.add(col, "");
    }
  }

  /**
   * Exports the filtered list as table with almost all fields. For members of group FINANCE_GROUP (PF_Finance) and MARKETING_GROUP
   * (PF_Marketing) all addresses are exported, for others only those which are marked as personal favorites.
   * @param list by sub classes such as AddressCampaignValueExport.
   * @param params
   */
  public byte[] export(final List<AuftragDO> list, final Object... params)
  {
    if (CollectionUtils.isEmpty(list) == true) {
      return null;
    }
    log.info("Exporting order list.");
    final ExportWorkbook xls = new ExportWorkbook();
    final ContentProvider contentProvider = new MyContentProvider(xls);
    // create a default Date format and currency column
    xls.setContentProvider(contentProvider);

    ExportColumn[] columns = createOrderColumns();
    String sheetTitle = PFUserContext.getLocalizedString("fibu.auftrag.auftraege");
    ExportSheet sheet = xls.addSheet(sheetTitle);
    ContentProvider sheetProvider = sheet.getContentProvider();
    sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, OrderCol.NETSUM, OrderCol.INVOICED, OrderCol.TO_BE_INVOICED);
    sheetProvider.putFormat(DateFormats.getExcelFormatString(DateFormatType.DATE), OrderCol.DATE, OrderCol.ORDER_DATE);
    sheet.createFreezePane(1, 1);
    sheet.setColumns(columns);
    for (final AuftragDO order : list) {
      final PropertyMapping mapping = new PropertyMapping();
      addOrderMapping(mapping, order, params);
      sheet.addRow(mapping.getMapping(), 0);
    }
    sheet.setAutoFilter();
    columns = createPosColumns();
    sheetTitle = PFUserContext.getLocalizedString("fibu.auftrag.positions");
    sheet = xls.addSheet(sheetTitle);
    sheetProvider = sheet.getContentProvider();
    sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, PosCol.NETSUM, PosCol.INVOICED, PosCol.TO_BE_INVOICED);
    sheetProvider.putFormat(DateFormats.getExcelFormatString(DateFormatType.DATE), PosCol.DATE, PosCol.PERIOD_OF_PERFORMANCE_BEGIN,
        PosCol.PERIOD_OF_PERFORMANCE_END);
    sheet.createFreezePane(1, 1);
    sheet.setColumns(columns);
    sheet.setMergedRegion(0, 0, PosCol.PERIOD_OF_PERFORMANCE_BEGIN.ordinal(), PosCol.PERIOD_OF_PERFORMANCE_END.ordinal(),
        PFUserContext.getLocalizedString("fibu.periodOfPerformance"));
    for (final AuftragDO order : list) {
      if (order.getPositionen() == null) {
        continue;
      }
      for (final AuftragsPositionDO pos : order.getPositionen()) {
        final PropertyMapping mapping = new PropertyMapping();
        addPosMapping(mapping, order, pos, params);
        sheet.addRow(mapping.getMapping(), 0);
      }
    }
    sheet.setAutoFilter();
    return xls.getAsByteArray();
  }

  public void setAccessChecker(final AccessChecker accessChecker)
  {
    this.accessChecker = accessChecker;
  }

  public void setAuftragDao(final AuftragDao auftragDao)
  {
    this.auftragDao = auftragDao;
  }

  public void setRechnungCache(final RechnungCache rechnungCache)
  {
    this.rechnungCache = rechnungCache;
  }

  public void setTaskTree(final TaskTree taskTree)
  {
    this.taskTree = taskTree;
  }
}