{
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, MyXlsContentProvider.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();