Java tutorial
///////////////////////////////////////////////////////////////////////////// // // Project ProjectForge Community Edition // www.projectforge.org // // Copyright (C) 2001-2013 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.datev; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.math.BigDecimal; import java.math.RoundingMode; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.EnumSet; import java.util.List; import java.util.Map; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.Validate; import org.apache.poi.hssf.util.HSSFColor; import org.projectforge.calendar.DayHolder; import org.projectforge.calendar.MonthHolder; import org.projectforge.common.CurrencyHelper; import org.projectforge.common.DateHelper; import org.projectforge.common.NumberHelper; import org.projectforge.export.CellFormat; import org.projectforge.export.ContentProvider; import org.projectforge.export.ExportCell; import org.projectforge.export.ExportRow; import org.projectforge.export.ExportSheet; import org.projectforge.export.ExportWorkbook; import org.projectforge.export.PropertyMapping; import org.projectforge.export.XlsContentProvider; import org.projectforge.fibu.EmployeeDO; import org.projectforge.fibu.EmployeeDao; import org.projectforge.fibu.EmployeeFilter; import org.projectforge.fibu.EmployeeSalaryDO; import org.projectforge.fibu.MonthlyEmployeeReport; import org.projectforge.fibu.MonthlyEmployeeReport.Kost2Row; import org.projectforge.fibu.MonthlyEmployeeReportDao; import org.projectforge.fibu.MonthlyEmployeeReportEntry; import org.projectforge.fibu.kost.Kost1DO; import org.projectforge.fibu.kost.Kost2DO; import org.projectforge.user.PFUserContext; import org.projectforge.user.PFUserDO; import org.projectforge.user.UserGroupCache; import org.springframework.orm.hibernate3.support.HibernateDaoSupport; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; /** * For excel export of employee salaries for import in Datev. * * @author Kai Reinhard (k.reinhard@micromata.de) */ @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW) public class EmployeeSalaryExportDao extends HibernateDaoSupport { public static final int KONTO = 6000; public static final int GEGENKONTO = 3791; private class MyContentProvider extends XlsContentProvider { public MyContentProvider(final ExportWorkbook workbook) { super(workbook); } @Override public void updateRowStyle(final ExportRow row) { for (final ExportCell cell : row.getCells()) { final CellFormat format = cell.ensureAndGetCellFormat(); format.setFillForegroundColor(HSSFColor.WHITE.index); switch (row.getRowNum()) { case 0: format.setFont(FONT_NORMAL_BOLD); // alignment = CellStyle.ALIGN_CENTER; break; default: format.setFont(FONT_NORMAL); if (row.getRowNum() % 2 == 0) { format.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } break; } } } @Override public ContentProvider newInstance() { return new MyContentProvider(this.workbook); } }; private static final org.apache.log4j.Logger log = org.apache.log4j.Logger .getLogger(EmployeeSalaryExportDao.class); private MonthlyEmployeeReportDao monthlyEmployeeReportDao; private UserGroupCache userGroupCache; private EmployeeDao employeeDao; private enum ExcelColumn { KOST1("fibu.kost1", XlsContentProvider.LENGTH_KOSTENTRAEGER), MITARBEITER("fibu.employee", XlsContentProvider.LENGTH_USER), STUNDEN("hours", XlsContentProvider.LENGTH_DURATION), KOST2( "fibu.kost2", XlsContentProvider.LENGTH_KOSTENTRAEGER), BRUTTO_MIT_AG( "fibu.employee.salary.bruttoMitAgAnteil", XlsContentProvider.LENGTH_CURRENCY), KORREKTUR("fibu.common.korrekturWert", XlsContentProvider.LENGTH_CURRENCY), SUMME("sum", XlsContentProvider.LENGTH_CURRENCY), BEZEICHNUNG("description", XlsContentProvider.LENGTH_EXTRA_LONG), DATUM("date", XlsContentProvider.LENGTH_DATE), KONTO( "fibu.buchungssatz.konto", 14), GEGENKONTO( "fibu.buchungssatz.gegenKonto", 14); final String theTitle; final int width; final static ExcelColumn START = KOST1; final static ExcelColumn END = GEGENKONTO; ExcelColumn(final String theTitle, final int width) { this.theTitle = theTitle; this.width = (short) width; } } /** * Exports the filtered list as table with almost all fields. */ @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW) public byte[] export(final List<EmployeeSalaryDO> list) { log.info("Exporting employee salary list."); Validate.notEmpty(list); Collections.sort(list, new Comparator<EmployeeSalaryDO>() { public int compare(final EmployeeSalaryDO o1, final EmployeeSalaryDO o2) { return (o1.getEmployee().getUser().getFullname()) .compareTo(o2.getEmployee().getUser().getFullname()); } }); final EmployeeFilter filter = new EmployeeFilter(); filter.setShowOnlyActiveEntries(true); filter.setDeleted(false); final List<EmployeeDO> employees = employeeDao.getList(filter); final List<EmployeeDO> missedEmployees = new ArrayList<EmployeeDO>(); for (final EmployeeDO employee : employees) { boolean found = false; for (final EmployeeSalaryDO salary : list) { if (salary.getEmployeeId().equals(employee.getId()) == true) { found = true; break; } } if (found == false) { missedEmployees.add(employee); } } if (CollectionUtils.isNotEmpty(missedEmployees) == true) { Collections.sort(missedEmployees, new Comparator<EmployeeDO>() { public int compare(final EmployeeDO o1, final EmployeeDO o2) { return (o1.getUser().getFullname()).compareTo(o2.getUser().getFullname()); } }); } final ExportWorkbook xls = new ExportWorkbook(); final ContentProvider contentProvider = new MyContentProvider(xls); // create a default Date format and currency column xls.setContentProvider(contentProvider); final EmployeeSalaryDO first = list.get(0); final int year = first.getYear(); final int month = first.getMonth(); final DayHolder buchungsdatum = new DayHolder(); buchungsdatum.setDate(year, month, 1); final MonthHolder monthHolder = new MonthHolder(buchungsdatum.getDate()); final BigDecimal numberOfWorkingDays = monthHolder.getNumberOfWorkingDays(); buchungsdatum.setEndOfMonth(); final String sheetTitle = DateHelper.formatMonth(year, month); final ExportSheet sheet = xls.addSheet(sheetTitle); sheet.createFreezePane(0, 1); final ExportSheet employeeSheet = xls.addSheet(PFUserContext.getLocalizedString("fibu.employee")); employeeSheet.setColumnWidth(0, XlsContentProvider.LENGTH_USER * 256); employeeSheet.setColumnWidth(1, 14 * 256); employeeSheet.setColumnWidth(2, 12 * 256); employeeSheet.setColumnWidth(3, 12 * 256); employeeSheet.setColumnWidth(4, 12 * 256); final ContentProvider provider = employeeSheet.getContentProvider(); provider.putFormat("STUNDEN", "0.00;[Red]-0.00"); final ExportRow employeeRow = employeeSheet.addRow(); employeeRow.addCell(0, PFUserContext.getLocalizedString("fibu.employee")); employeeRow.addCell(1, PFUserContext.getLocalizedString("fibu.employee.wochenstunden")); employeeRow.addCell(2, PFUserContext.getLocalizedString("fibu.employee.sollstunden")); employeeRow.addCell(3, PFUserContext.getLocalizedString("fibu.employee.iststunden")); employeeRow.addCell(4, PFUserContext.getLocalizedString("fibu.common.difference")); // build all column names, title, widths from fixed and variable columns final int numCols = ExcelColumn.values().length; final String[] colNames = new String[numCols]; final String[] colTitles = new String[numCols]; final int[] colWidths = new int[numCols]; int idx = 0; for (final ExcelColumn col : EnumSet.range(ExcelColumn.START, ExcelColumn.END)) { colNames[idx] = col.name(); colTitles[idx] = PFUserContext.getLocalizedString(col.theTitle); colWidths[idx] = col.width; ++idx; } // column property names sheet.setPropertyNames(colNames); final ContentProvider sheetProvider = sheet.getContentProvider(); sheetProvider.putFormat("STUNDEN", "0.00"); sheetProvider.putFormat("BRUTTO_MIT_AG", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("KORREKTUR", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("SUMME", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("KOST1", "#"); sheetProvider.putFormat("KOST2", "#"); sheetProvider.putFormat("KONTO", "#"); sheetProvider.putFormat("GEGENKONTO", "#"); sheetProvider.putFormat("DATUM", "dd.MM.yyyy"); // inform provider of column widths for (int ci = 0; ci < colWidths.length; ++ci) { sheetProvider.putColWidth(ci, colWidths[ci]); } final ExportRow headRow = sheet.addRow(); int i = 0; for (final String title : colTitles) { headRow.addCell(i++, title); } for (final EmployeeSalaryDO salary : list) { final PropertyMapping mapping = new PropertyMapping(); final PFUserDO user = userGroupCache.getUser(salary.getEmployee().getUserId()); Validate.isTrue(year == salary.getYear()); Validate.isTrue(month == salary.getMonth()); final MonthlyEmployeeReport report = monthlyEmployeeReportDao.getReport(year, month, user); mapping.add(ExcelColumn.MITARBEITER, user.getFullname()); final Kost1DO kost1 = salary.getEmployee().getKost1(); final BigDecimal bruttoMitAGAnteil = salary.getBruttoMitAgAnteil(); final BigDecimal netDuration = new BigDecimal(report.getTotalNetDuration()); final Map<String, Kost2Row> rows = report.getKost2Rows(); BigDecimal sum = BigDecimal.ZERO; int j = rows.size(); for (final Kost2Row row : rows.values()) { final Kost2DO kost2 = row.getKost2(); final MonthlyEmployeeReportEntry entry = report.getKost2Durations().get(kost2.getId()); mapping.add(ExcelColumn.KOST1, kost1.getNummer()); mapping.add(ExcelColumn.MITARBEITER, user.getFullname()); mapping.add(ExcelColumn.KOST2, kost2.getNummer()); final BigDecimal duration = new BigDecimal(entry.getMillis() / 1000); // Seconds // duration = duration.divide(new BigDecimal(60 * 60 * 24), 8, RoundingMode.HALF_UP); // Fraction of day (24 hours) // mapping.add(ExcelColumn.STUNDEN, duration); mapping.add(ExcelColumn.STUNDEN, duration.divide(new BigDecimal(3600), 2, RoundingMode.HALF_UP)); mapping.add(ExcelColumn.BEZEICHNUNG, kost2.getToolTip()); final BigDecimal betrag = CurrencyHelper.multiply(bruttoMitAGAnteil, new BigDecimal(entry.getMillis()).divide(netDuration, 8, RoundingMode.HALF_UP)); sum = sum.add(betrag); if (--j == 0) { final BigDecimal korrektur = bruttoMitAGAnteil.subtract(sum); mapping.add(ExcelColumn.BRUTTO_MIT_AG, betrag.add(korrektur)); mapping.add(ExcelColumn.KORREKTUR, korrektur); if (NumberHelper.isEqual(sum.add(korrektur), bruttoMitAGAnteil) == true) { mapping.add(ExcelColumn.SUMME, bruttoMitAGAnteil); } else { mapping.add(ExcelColumn.SUMME, "*** " + sum + " != " + bruttoMitAGAnteil); } } else { mapping.add(ExcelColumn.BRUTTO_MIT_AG, betrag); mapping.add(ExcelColumn.KORREKTUR, ""); mapping.add(ExcelColumn.SUMME, ""); } mapping.add(ExcelColumn.DATUM, buchungsdatum.getCalendar()); // Last day of month mapping.add(ExcelColumn.KONTO, KONTO); // constant. mapping.add(ExcelColumn.GEGENKONTO, GEGENKONTO); // constant. sheet.addRow(mapping.getMapping(), 0); } addEmployeeRow(employeeSheet, salary.getEmployee(), numberOfWorkingDays, netDuration); } for (final EmployeeDO employee : missedEmployees) { final PFUserDO user = userGroupCache.getUser(employee.getUserId()); final PropertyMapping mapping = new PropertyMapping(); mapping.add(ExcelColumn.MITARBEITER, user.getFullname()); mapping.add(ExcelColumn.SUMME, "***"); mapping.add(ExcelColumn.BEZEICHNUNG, "*** FEHLT! ***"); sheet.addRow(mapping.getMapping(), 0); final MonthlyEmployeeReport report = monthlyEmployeeReportDao.getReport(year, month, user); final BigDecimal netDuration = new BigDecimal(report.getTotalNetDuration()); addEmployeeRow(employeeSheet, employee, numberOfWorkingDays, netDuration); } // sheet.setZoom(3, 4); // 75% final ByteArrayOutputStream baos = new ByteArrayOutputStream(); try { xls.write(baos); } catch (final IOException ex) { log.fatal("Exception encountered " + ex, ex); throw new RuntimeException(ex); } return baos.toByteArray(); } private void addEmployeeRow(final ExportSheet sheet, final EmployeeDO employee, final BigDecimal numberOfWorkingDays, final BigDecimal totalDuration) { final PFUserDO user = userGroupCache.getUser(employee.getUserId()); final ExportRow row = sheet.addRow(); row.addCell(0, user.getFullname()); // Wochenstunden row.addCell(1, employee.getWochenstunden(), "STUNDEN"); // Sollstunden: Wochenstunden * Arbeitstage / 5 Arbeitstage pro Woche Integer wochenstunden = employee.getWochenstunden(); if (wochenstunden == null) { wochenstunden = 0; } final BigDecimal soll = new BigDecimal(wochenstunden).multiply(numberOfWorkingDays) .divide(new BigDecimal(5), 2, RoundingMode.HALF_UP); row.addCell(2, soll, "STUNDEN"); // Iststunden final BigDecimal total = totalDuration.divide(new BigDecimal(3600000), 2, RoundingMode.HALF_UP); row.addCell(3, total, "STUNDEN"); // Differenz final BigDecimal differenz = total.subtract(soll); row.addCell(4, differenz, "STUNDEN"); } public void setMonthlyEmployeeReportDao(final MonthlyEmployeeReportDao monthlyEmployeeReportDao) { this.monthlyEmployeeReportDao = monthlyEmployeeReportDao; } public void setUserGroupCache(final UserGroupCache userGroupCache) { this.userGroupCache = userGroupCache; } public void setEmployeeDao(final EmployeeDao employeeDao) { this.employeeDao = employeeDao; } }