db.pj.util.excel.Phase4GradeImporter.java Source code

Introduction

Here is the source code for db.pj.util.excel.Phase4GradeImporter.java

Source

package db.pj.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
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 db.pj.dao.DaoFactory;
import db.pj.dao.ExaminationDao;
import db.pj.dao.LicenseDao;
import db.pj.dao.ScheduleDao;
import db.pj.dao.StaffDao;
import db.pj.dao.UserDao;
import db.pj.dao.WarningDao;
import db.pj.entity.Examination;
import db.pj.entity.License;
import db.pj.entity.Schedule;
import db.pj.entity.User;
import db.pj.entity.Warning;
import db.pj.util.IDGenerator;
import db.pj.util.StringUtil;

public class Phase4GradeImporter implements ExcelImporter {
    private File file;

    public Phase4GradeImporter(File file) {
        this.file = file;
    }

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    @Override
    public List<String> importExcel() {
        // get workbook
        FileInputStream inputStream = null;
        XSSFWorkbook workbook = null;
        List<String> result = new ArrayList<String>();
        result.add("");
        try {
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            result.set(0, "");
            return result;
        }

        int success = 0, fail = 0; // number of tuples
        UserDao userDao = DaoFactory.getUserDao();
        StaffDao staffDao = DaoFactory.getStaffDao();
        ScheduleDao scheduleDao = DaoFactory.getScheduleDao();
        ExaminationDao examinationDao = DaoFactory.getExaminationDao();
        LicenseDao licenseDao = DaoFactory.getLicenseDao();
        WarningDao warningDao = DaoFactory.getWarningDao();

        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;

        int rows = sheet.getPhysicalNumberOfRows();
        int cells = sheet.getRow(0).getPhysicalNumberOfCells();
        for (int i = 1; i < rows; i++) {
            row = sheet.getRow(i);
            if (row == null)
                continue;

            String[] values = new String[cells];

            // read data to an array of strings
            for (short j = 0; j < cells; j++) {
                cell = row.getCell(j);
                if (cell == null)
                    values[j] = null;
                else {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                        break;
                    default:
                        values[j] = cell.getStringCellValue();
                        break;
                    }
                }
            }

            User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]);
            Schedule schedule = scheduleDao.queryScheduleByID(values[3]);
            Examination examination = null;

            if (user == null) {
                fail++;
                String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + "";
                result.add("" + ic + "" + values[3]
                        + "");
            } else if (schedule == null) {
                fail++;
                String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + "";
                result.add(
                        "" + ic + "" + values[3] + "");
            } else {
                examination = examinationDao.queryExaminationByUserAndSchedule(user, schedule);
                if (examination == null) {
                    fail++;
                    String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                            + "";
                    result.add("" + ic + "" + values[3]
                            + "");
                    Warning warning = new Warning();
                    warning.setWarnID(IDGenerator.generateUUID());
                    warning.setUser(user);
                    warning.setSchedule(schedule);
                    warning.setWarnCont(StringUtil.buildPhase14Warning(values));
                    warningDao.addWarning(warning);
                } else {
                    examination.setStaff(staffDao.queryStaffByStaffID(values[2]));
                    examination.setExamScore1(Integer.parseInt(values[4]));
                    examination.setExamPass(examination.getExamScore1() >= 60 ? 1 : 0);
                    examination.setExamFinfo(StringUtil.buildGradeFinfo(values[5]));

                    if (examination.getExamPass() == 1 && user.getUserState() == 4) {
                        boolean re = examinationDao.updateExamination(examination);
                        if (re)
                            success++;
                        else {
                            fail++;
                            String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                                    + "";
                            result.add("" + ic + "" + values[3]
                                    + "");
                        }

                        user.setUserState(5);

                        License license = licenseDao.queryLicenseByUserID(user.getUserID());
                        if (license == null) {
                            License newLicn = new License();
                            newLicn.setLicnID(IDGenerator.generateLicenseID(user.getUserID()));
                            newLicn.setLicnType("C1");
                            newLicn.setLicnGdate(schedule.getSchdDate());
                            newLicn.setLicnVdlen(10);
                            newLicn.setLicnValid(1);
                            newLicn.setLicnVinfo(0);
                            newLicn.setAdministration(schedule.getAdministration());
                            newLicn.setLicnLimit(null);
                            newLicn.setUser(user);

                            user.setLicense(license);

                            userDao.updateUser(user);
                            licenseDao.addLicense(newLicn);

                        } else {
                            license.setLicnValid(1);
                            license.setLicnGdate(schedule.getSchdDate());
                            license.setLicnLimit(null);
                            license.setLicnVinfo(0);
                            licenseDao.updateLicense(license);
                        }
                    } else if (user.getUserState() < 4) {
                        fail++;
                        String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                                + "";
                        result.add("" + ic + "" + values[3]
                                + "");
                    }

                }
            }

        }

        result.set(0, "" + success + "" + fail + "");
        return result;
    }

}