Java tutorial
///////////////////////////////////////////////////////////////////////////// // // 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; } }