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%