Revize b2108db7

Re #7530 Get 'vacation' and 'overtime' from input xlsx file

server/pom.xml

@@ -31 +31 @@
 
         <dependency>
             <groupId>org.apache.poi</groupId>
-            <artifactId>poi</artifactId>
+            <artifactId>poi-ooxml</artifactId>
             <version>4.1.0</version>
         </dependency>
 
@@ -41 +41 @@
             <version>2.0.15</version>
         </dependency>
 
+        <dependency>
+            <groupId>com.github.dhorions</groupId>
+            <artifactId>boxable</artifactId>
+            <version>1.5</version>
+        </dependency>
+
 
         <!-- DATABASE -->
         <dependency>

server/src/main/java/cz/zcu/yamanager/business/FileServiceImpl.java

@@ +1 @@
+package cz.zcu.yamanager.business;
+
+import cz.zcu.yamanager.business.file.excel.*;
+import cz.zcu.yamanager.ws.rest.RESTFullException;
+
+import java.io.ByteArrayInputStream;
+import java.io.IOException;
+import java.util.*;
+
+import static cz.zcu.yamanager.business.file.excel.ExcelParser.parseXLSX;
+
+public class FileServiceImpl implements FileService {
+
+    private class Content {
+        double overtime = 0;
+        List<Date> vacations = new ArrayList<>();
+    }
+
+    @Override
+    public FileImportResult parseXLSFile(String fileName, byte[] bytes) throws RESTFullException {
+
+        try {
+            Map<String, Content> contentMap = getContentInfo(parseXLSX(new ByteArrayInputStream(bytes)));
+
+            return new FileImportResult(fileName, (long) contentMap.size());
+
+        } catch (IOException e) {
+            throw new RESTFullException("", "");
+        }
+    }
+
+    private Map<String, Content> getContentInfo(Map<String, SheetContent[]> sheetForName) {
+
+        Map<String, Content> result = new HashMap<>();
+
+        for (Map.Entry<String, SheetContent[]> item : sheetForName.entrySet()) {
+            String name = item.getKey();
+            SheetAttendanceContent content = (SheetAttendanceContent) item.getValue()[SheetType.ATTENDANCE.ordinal()];
+
+            if (!result.containsKey(name)) {
+                result.put(name, new Content());
+            }
+
+            for (AttendanceRecord record : content.getRecords()) {
+                if (record.getType().equals(RecordType.VACATION)) {
+                    result.get(name).vacations.add(record.getDate());
+                }
+            }
+            result.get(name).overtime = content.getOvertimeHours();
+        }
+
+        return result;
+    }
+
+
+    @Override
+    public FileExportResult createPDFFile() throws RESTFullException {
+        return new FileExportResult("unknown", new byte[0]);
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/AttendanceRecord.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import java.util.Date;
+
+public class AttendanceRecord {
+
+    private final Date date;
+    private final double estimateHours;
+    private final double workHours;
+    private final LoadType load;
+    private final RecordType type;
+
+    public AttendanceRecord(Date date, double estimateHours, double workHours, LoadType load, RecordType type) {
+        this.date = date;
+        this.estimateHours = estimateHours;
+        this.workHours = workHours;
+        this.load = load;
+        this.type = type;
+    }
+
+    public Date getDate() {
+        return date;
+    }
+
+    public double getEstimateHours() {
+        return estimateHours;
+    }
+
+    public double getWorkHours() {
+        return workHours;
+    }
+
+    public LoadType getLoad() {
+        return load;
+    }
+
+    public RecordType getType() {
+        return type;
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/ExcelParser.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.Map;
+import java.util.Optional;
+
+public class ExcelParser {
+
+    private static class SheetInfo {
+        String employee;
+        SheetParser parser;
+    }
+
+    public static Map<String, SheetContent[]> parseXLSX(InputStream inputStream) throws IOException {
+
+        Map<String, SheetContent[]> sheets = new HashMap<>();
+
+        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
+
+        Iterator<Sheet> sheetIterator = wb.sheetIterator();
+        while (sheetIterator.hasNext()) {
+            Sheet sheet = sheetIterator.next();
+
+            Optional<SheetInfo> sheetTitleOptional = parseSheetTitle(sheet);
+
+            if (!sheetTitleOptional.isPresent()) {
+                continue;
+            }
+
+            SheetInfo sheetInfo = sheetTitleOptional.get();
+
+            SheetContent sheetContent = sheetInfo.parser.parseSheet(sheet);
+
+            if (sheetContent == null) continue;
+
+            if (!sheets.containsKey(sheetInfo.employee)) {
+                sheets.put(sheetInfo.employee, new SheetContent[SheetType.values().length]);
+            }
+
+            sheets.get(sheetInfo.employee)[sheetInfo.parser.getType().ordinal()] = sheetContent;
+        }
+
+        return sheets;
+    }
+
+    private static Optional<SheetInfo> parseSheetTitle(Sheet sheet) {
+
+        String[] sheetName = sheet.getSheetName().split("-");
+
+        if (sheetName.length != 2) {
+            return Optional.empty();
+        }
+
+        SheetInfo result = new SheetInfo();
+        result.employee = sheetName[1].trim();
+        switch (sheetName[0].trim().toLowerCase()) {
+            case "dochazka": result.parser = new SheetAttendanceParser(); break;
+            case "stravenky": result.parser = new SheetMealTicketParser(); break;
+        }
+        return Optional.of(result);
+
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/LoadType.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+public enum LoadType {
+    WEEKEND, WOORKING_WEEK
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/RecordType.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+public enum  RecordType {
+    VACATION, WORK, BLANK
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetAttendanceContent.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import java.util.List;
+
+public class SheetAttendanceContent implements SheetContent {
+
+    private List<AttendanceRecord> records;
+
+    private final double sumOfEstimateHours;
+    private final double sumOfWorkHours;
+    private final double hoursPerWeek;
+    private final double overtimeHours;
+
+    public SheetAttendanceContent(
+            List<AttendanceRecord> records,
+            double sumOfEstimateHours,
+            double sumOfWorkHours,
+            double hoursPerWeek,
+            double overtimeHours)
+    {
+        this.records = records;
+        this.sumOfEstimateHours = sumOfEstimateHours;
+        this.sumOfWorkHours = sumOfWorkHours;
+        this.hoursPerWeek = hoursPerWeek;
+        this.overtimeHours = overtimeHours;
+    }
+
+    public double getSumOfEstimateHours() {
+        return sumOfEstimateHours;
+    }
+
+    public double getSumOfWorkHours() {
+        return sumOfWorkHours;
+    }
+
+    public double getHoursPerWeek() {
+        return hoursPerWeek;
+    }
+
+    public double getOvertimeHours() {
+        return overtimeHours;
+    }
+
+    public List<AttendanceRecord> getRecords() {
+        return records;
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetAttendanceParser.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.Iterator;
+import java.util.List;
+
+public class SheetAttendanceParser implements SheetParser {
+
+
+    @Override
+    public SheetContent parseSheet(Sheet sheet) {
+
+        List<AttendanceRecord> records = new ArrayList<>();
+
+        double sumOfEstimateHours = 0;
+        double sumOfWorkHours = 0;
+        double hoursPerWeek = 0;
+        double overtimeHours = 0;
+
+        Iterator<Row> rowIterator = sheet.rowIterator();
+        rowIterator.next(); // skip header
+        while (rowIterator.hasNext()) {
+            Row row = rowIterator.next();
+
+           Cell firstCell = row.getCell(0);
+
+           if (firstCell == null) continue;
+
+           switch (firstCell.getCellType()) {
+               case NUMERIC: {
+                   AttendanceRecord record = parseRowOfAttendance(row);
+                   records.add(record);
+
+                   sumOfEstimateHours += record.getEstimateHours();
+                   sumOfWorkHours += record.getWorkHours();
+
+               } break;
+               case STRING: {
+                    Cell loadCell = row.getCell(1);
+                   String numString = loadCell.getStringCellValue().replaceAll("[^1-9.]", "");
+                   hoursPerWeek = Double.parseDouble(numString);
+               }
+           }
+        }
+
+        overtimeHours = sumOfEstimateHours - sumOfWorkHours;
+
+        return new SheetAttendanceContent(records, sumOfEstimateHours, sumOfWorkHours, hoursPerWeek, overtimeHours);
+    }
+
+    private AttendanceRecord parseRowOfAttendance(Row row) {
+
+        Date date = null;
+        Cell dateCell = row.getCell(0);
+        if(dateCell.getCellType().equals(CellType.NUMERIC)) {
+            date = dateCell.getDateCellValue();
+        }
+
+        LoadType load = null;
+        Cell loadCell = row.getCell(1);
+        switch (loadCell.getStringCellValue()) {
+            case "prac": load = LoadType.WOORKING_WEEK; break;
+            case "vikend": load = LoadType.WEEKEND; break;
+        }
+
+        double estimateHours = 0;
+        RecordType type = RecordType.BLANK;
+        Cell estimateHoursCell = row.getCell(2);
+        switch (estimateHoursCell.getCellType()) {
+            case NUMERIC: {
+                estimateHours = estimateHoursCell.getNumericCellValue();
+                type = RecordType.WORK;
+            } break;
+            case STRING: {
+                String cellValue = estimateHoursCell.getStringCellValue();
+                if (cellValue.equals("dovolena")) {
+                    estimateHours = 0;
+                    type = RecordType.VACATION;
+                }
+            }
+        }
+
+        double workHours = 0;
+        Cell workHoursCell = row.getCell(3);
+        switch (workHoursCell.getCellType()) {
+            case NUMERIC: {
+                workHours = workHoursCell.getNumericCellValue();
+            } break;
+            case STRING: {
+                String cellValue = workHoursCell.getStringCellValue();
+                if (cellValue.equals("dovolena")) {
+                    workHours = 0;
+                }
+            }
+        }
+
+        return new AttendanceRecord(date, estimateHours, workHours, load, type);
+    }
+
+
+    @Override
+    public SheetType getType() {
+        return SheetType.ATTENDANCE;
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetContent.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+public interface SheetContent {
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetMealTicketParser.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import org.apache.poi.ss.usermodel.Sheet;
+
+public class SheetMealTicketParser implements SheetParser {
+
+    @Override
+    public SheetContent parseSheet(Sheet sheet) {
+        return null;
+    }
+
+    @Override
+    public SheetType getType() {
+        return SheetType.MEAL_TICKET;
+    }
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetParser.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import org.apache.poi.ss.usermodel.Sheet;
+
+public interface SheetParser {
+
+    SheetContent parseSheet(Sheet sheet);
+
+    SheetType getType();
+}

server/src/main/java/cz/zcu/yamanager/business/file/excel/SheetType.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+public enum SheetType {
+    ATTENDANCE, MEAL_TICKET
+}

server/src/main/java/cz/zcu/yamanager/business/mock/FileServiceMock.java

@@ -1 +1 @@
 package cz.zcu.yamanager.business.mock;
 
+import be.quodlibet.boxable.BaseTable;
+import be.quodlibet.boxable.Cell;
+import be.quodlibet.boxable.Row;
+import be.quodlibet.boxable.datatable.DataTable;
 import cz.zcu.yamanager.business.FileExportResult;
 import cz.zcu.yamanager.business.FileImportResult;
 import cz.zcu.yamanager.business.FileService;
@@ -9 +13 @@
 import org.apache.pdfbox.pdmodel.PDPageContentStream;
 import org.apache.pdfbox.pdmodel.common.PDRectangle;
 import org.apache.pdfbox.pdmodel.font.PDType1Font;
-import org.apache.poi.hssf.usermodel.HSSFCell;
-import org.apache.poi.hssf.usermodel.HSSFRow;
-import org.apache.poi.hssf.usermodel.HSSFSheet;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFCell;
+import org.apache.poi.xssf.usermodel.XSSFRow;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.springframework.stereotype.Component;
 
 import java.io.ByteArrayInputStream;
 import java.io.ByteArrayOutputStream;
 import java.io.IOException;
 import java.util.ArrayList;
+import java.util.Arrays;
 import java.util.Iterator;
 import java.util.List;
 
@@ -30 +35 @@
     @Override
     public FileImportResult parseXLSFile(String fileName, byte[] bytes) throws RESTFullException {
         try {
-            HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(bytes));
 
-            HSSFSheet sheet=wb.getSheetAt(0);
-            HSSFRow row;
-            HSSFCell cell;
+            XSSFWorkbook wb = new XSSFWorkbook(new ByteArrayInputStream(bytes));
+
+            XSSFSheet sheet=wb.getSheetAt(0);
+            XSSFRow row;
+            XSSFCell cell;
 
             Iterator rows = sheet.rowIterator();
 
             while (rows.hasNext()) {
-                row=(HSSFRow) rows.next();
+                row=(XSSFRow) rows.next();
                 Iterator cells = row.cellIterator();
 
                 while (cells.hasNext()) {
-                    cell = (HSSFCell) cells.next();
+                    cell = (XSSFCell) cells.next();
 
-                    content.add(cell.getStringCellValue());
+                    content.add(cell.getRawValue());
                 }
             }
             return new FileImportResult(fileName, (long) bytes.length);
@@ -82 +88 @@
             document.save(output);
             document.close();
 
-            return new FileExportResult("export.pdf", output.toByteArray());
+            return new FileExportResult("export.pdf", pdfTable());
 
         } catch (IOException ioex) {
             throw new RESTFullException("", "");
         }
     }
+
+    private byte[] pdfTable() throws IOException {
+        PDPage myPage = new PDPage(PDRectangle.A4);
+        PDDocument mainDocument = new PDDocument();
+        PDPageContentStream contentStream = new PDPageContentStream(mainDocument, myPage);
+
+
+
+        //Dummy Table
+        float margin = 50;
+        // starting y position is whole page height subtracted by top and bottom margin
+        float yStartNewPage = myPage.getMediaBox().getHeight() - (2 * margin);
+        // we want table across whole page width (subtracted by left and right margin ofcourse)
+        float tableWidth = myPage.getMediaBox().getWidth() - (2 * margin);
+
+        boolean drawContent = true;
+        float yStart = yStartNewPage;
+        float bottomMargin = 70;
+        // y position is your coordinate of top left corner of the table
+        float yPosition = 550;
+
+        List<List> data = new ArrayList();
+        data.add(new ArrayList<>(
+                Arrays.asList("Column One", "Column Two", "Column Three", "Column Four", "Column Five")));
+        for (int i = 1; i <= 100; i++) {
+            data.add(new ArrayList<>(
+                    Arrays.asList("Row " + i + " Col One", "Row " + i + " Col Two", "Row " + i + " Col Three", "Row " + i + " Col Four", "Row " + i + " Col Five")));
+        }
+        BaseTable dataTable = new BaseTable(yStart, yStartNewPage, bottomMargin, tableWidth, margin, mainDocument, myPage, true, true);
+        DataTable t = new DataTable(dataTable, myPage);
+        t.addListToTable(data, DataTable.HASHEADER);
+        dataTable.draw();
+
+        contentStream.close();
+        mainDocument.addPage(myPage);
+
+        ByteArrayOutputStream output = new ByteArrayOutputStream();
+        mainDocument.save(output);
+
+        mainDocument.close();
+        contentStream.close();
+
+        return output.toByteArray();
+    }
+
+    private byte[] pdfSimple() throws IOException {
+
+        PDPage myPage = new PDPage(PDRectangle.A4);
+        PDDocument mainDocument = new PDDocument();
+        PDPageContentStream contentStream = new PDPageContentStream(mainDocument, myPage);
+
+
+
+        //Dummy Table
+        float margin = 50;
+        // starting y position is whole page height subtracted by top and bottom margin
+        float yStartNewPage = myPage.getMediaBox().getHeight() - (2 * margin);
+        // we want table across whole page width (subtracted by left and right margin ofcourse)
+        float tableWidth = myPage.getMediaBox().getWidth() - (2 * margin);
+
+        boolean drawContent = true;
+        float yStart = yStartNewPage;
+        float bottomMargin = 70;
+        // y position is your coordinate of top left corner of the table
+        float yPosition = 550;
+
+        BaseTable table = new BaseTable(yPosition, yStartNewPage, bottomMargin, tableWidth, margin, mainDocument, myPage, true, drawContent);
+
+
+        Row<PDPage> headerRow = table.createRow(15f);
+        Cell<PDPage> cell = headerRow.createCell(100, "Header");
+        table.addHeaderRow(headerRow);
+
+
+        Row<PDPage> row = table.createRow(12);
+        cell = row.createCell(30, "Data 1");
+        cell = row.createCell(70, "Some value");
+
+        table.draw();
+
+
+        contentStream.close();
+        mainDocument.addPage(myPage);
+
+        ByteArrayOutputStream output = new ByteArrayOutputStream();
+        mainDocument.save(output);
+
+        mainDocument.close();
+        contentStream.close();
+
+        return output.toByteArray();
+    }
+
+
 }

server/src/test/java/cz/zcu/yamanager/business/file/excel/ExcelParserTest.java

@@ +1 @@
+package cz.zcu.yamanager.business.file.excel;
+
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.*;
+
+import static org.junit.jupiter.api.Assertions.*;
+
+class ExcelParserTest {
+
+    @Test
+    public void parseXLSFile() {
+
+        try {
+
+            InputStream stream = getClass().getClassLoader().getResourceAsStream("dochazka_mezistupen.xlsx");
+
+            Map<String, SheetContent[]> sheets = ExcelParser.parseXLSX(stream);
+
+            Map<String, Double> nameOvertime = new HashMap<>();
+            Map<String, List<Date>> nameVacation = new HashMap<>();
+
+            for (Map.Entry<String, SheetContent[]> item : sheets.entrySet()) {
+                String name = item.getKey();
+                SheetAttendanceContent content = (SheetAttendanceContent) item.getValue()[SheetType.ATTENDANCE.ordinal()];
+                for (AttendanceRecord record : content.getRecords()) {
+                    if (record.getType().equals(RecordType.VACATION)) {
+                        if (!nameVacation.containsKey(name)) {
+                            nameVacation.put(name, new ArrayList<>());
+                        }
+                        nameVacation.get(name).add(record.getDate());
+                    }
+                }
+                nameOvertime.put(name, content.getOvertimeHours());
+            }
+
+            assertTrue(nameOvertime.containsKey("Onegin"));
+            assertEquals(0, nameOvertime.get("Onegin").intValue());
+
+            assertTrue(nameOvertime.containsKey("Novak"));
+            assertEquals(0, nameOvertime.get("Novak").intValue());
+
+            assertTrue(nameOvertime.containsKey("Pavel"));
+            assertEquals(0, nameOvertime.get("Pavel").intValue());
+
+            assertTrue(nameOvertime.containsKey("Fristensky"));
+            assertEquals(0, nameOvertime.get("Fristensky").intValue());
+
+
+            assertFalse(nameVacation.containsKey("Onegin"));
+            assertFalse(nameVacation.containsKey("Novak"));
+            assertFalse(nameVacation.containsKey("Fristensky"));
+
+            assertTrue(nameVacation.containsKey("Pavel"));
+            assertEquals(5, nameVacation.get("Pavel").size());
+
+        } catch (IOException e) {
+            assertFalse(false, "");
+        }
+    }
+
+}