Java tutorial
/** * Copyright 2013 Instituto Superior Tcnico * * This file is part of FenixEdu IST GIAF Contracts. * * FenixEdu IST GIAF Contracts is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * FenixEdu IST GIAF Contracts 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 Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with FenixEdu IST GIAF Contracts. If not, see <http://www.gnu.org/licenses/>. */ package pt.ist.fenixedu.contracts.tasks.giafsync; import java.io.PrintWriter; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.HashSet; import java.util.List; import java.util.Objects; import java.util.Set; import org.apache.commons.lang.StringUtils; import org.fenixedu.academic.domain.Country; import org.fenixedu.academic.domain.Person; import org.fenixedu.bennu.core.domain.Bennu; import org.fenixedu.commons.StringNormalizer; import org.joda.time.DateTime; import org.joda.time.LocalDate; import org.joda.time.format.DateTimeFormat; import org.joda.time.format.DateTimeFormatter; import org.slf4j.Logger; import pt.ist.fenixedu.contracts.domain.personnelSection.contracts.GiafProfessionalData; import pt.ist.fenixedu.contracts.domain.personnelSection.contracts.GrantOwnerEquivalent; import pt.ist.fenixedu.contracts.domain.personnelSection.contracts.PersonGrantOwnerEquivalent; import pt.ist.fenixedu.contracts.domain.personnelSection.contracts.PersonProfessionalData; import pt.ist.fenixedu.contracts.domain.personnelSection.contracts.PersonProfessionalExemption; import pt.ist.fenixedu.contracts.persistenceTierOracle.Oracle.PersistentSuportGiaf; import pt.ist.fenixedu.contracts.tasks.giafsync.GiafSync.ImportProcessor; import pt.ist.fenixedu.contracts.tasks.giafsync.GiafSync.Modification; class ImportPersonGrantOwnerEquivalentFromGiaf extends ImportProcessor { final static DateTimeFormatter dateFormat = DateTimeFormat.forPattern("yyyy-MM-dd"); final static DateTimeFormatter dateTimeFormat = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"); public ImportPersonGrantOwnerEquivalentFromGiaf() { } @Override public List<Modification> processChanges(GiafMetadata metadata, PrintWriter log, Logger logger) throws Exception { List<Modification> modifications = new ArrayList<>(); PersistentSuportGiaf oracleConnection = PersistentSuportGiaf.getInstance(); PreparedStatement preparedStatement = oracleConnection.prepareStatement(getQuery()); ResultSet result = preparedStatement.executeQuery(); int count = 0; int news = 0; int notImported = 0; int dontExist = 0; Set<Person> importedButInvalid = new HashSet<Person>(); while (result.next()) { count++; String numberString = result.getString("emp_num"); Person person = metadata.getPerson(numberString, logger); if (person == null) { logger.debug("Invalid person with number: " + numberString); dontExist++; continue; } PersonProfessionalData personProfessionalData = person.getPersonProfessionalData(); if (personProfessionalData == null) { logger.debug("Empty personProfessionalData: " + numberString); dontExist++; continue; } final GiafProfessionalData giafProfessionalData = personProfessionalData .getGiafProfessionalDataByGiafPersonIdentification(numberString); if (giafProfessionalData == null) { logger.debug("Empty giafProfessionalData: " + numberString); dontExist++; continue; } final String grantOwnerEquivalentGiafId = result.getString("tipo_equip"); final GrantOwnerEquivalent grantOwnerEquivalent = metadata .grantOwnerEquivalent(grantOwnerEquivalentGiafId); if (grantOwnerEquivalent == null) { logger.debug("Empty grantOwnerEquivalent: " + grantOwnerEquivalentGiafId + ". Person number: " + numberString); importedButInvalid.add(person); } String beginDateString = result.getString("DATA_INICIO"); final LocalDate beginDate = StringUtils.isEmpty(beginDateString) ? null : new LocalDate(Timestamp.valueOf(beginDateString)); if (beginDate == null) { logger.debug("Empty beginDate. Person number: " + numberString + " ServiceExemption: " + grantOwnerEquivalentGiafId); importedButInvalid.add(person); } String endDateString = result.getString("DATA_FIM"); final LocalDate endDate = StringUtils.isEmpty(endDateString) ? null : new LocalDate(Timestamp.valueOf(endDateString)); if (endDate != null) { if (beginDate.isAfter(endDate)) { logger.debug("BeginDate after EndDate. Person number: " + numberString + " beginDate: " + beginDate + " endDate: " + endDate); importedButInvalid.add(person); } } final String motive = result.getString("motivo"); final String local = result.getString("local"); final String giafCountryName = result.getString("nac_dsc"); final Country country = StringUtils.isEmpty(giafCountryName) ? null : metadata.country(StringNormalizer.normalize(giafCountryName)); if (country == null) { importedButInvalid.add(person); } String creationDateString = result.getString("data_criacao"); if (StringUtils.isEmpty(creationDateString)) { logger.debug("Empty creationDate. Person number: " + numberString + " grantOwnerEquivalent: " + grantOwnerEquivalentGiafId); notImported++; continue; } final DateTime creationDate = new DateTime(Timestamp.valueOf(creationDateString)); String modifiedDateString = result.getString("data_alteracao"); final DateTime modifiedDate = StringUtils.isEmpty(modifiedDateString) ? null : new DateTime(Timestamp.valueOf(modifiedDateString)); if (!hasPersonGrantOwnerEquivalent(giafProfessionalData, beginDate, endDate, motive, local, giafCountryName, country, grantOwnerEquivalent, grantOwnerEquivalentGiafId, creationDate, modifiedDate)) { modifications.add(new Modification() { @Override public void execute() { new PersonGrantOwnerEquivalent(giafProfessionalData, beginDate, endDate, motive, local, giafCountryName, country, grantOwnerEquivalent, grantOwnerEquivalentGiafId, creationDate, modifiedDate); } }); news++; } } result.close(); preparedStatement.close(); int deleted = 0; int totalInFenix = 0; int repeted = 0; for (GiafProfessionalData giafProfessionalData : Bennu.getInstance().getGiafProfessionalDataSet()) { for (PersonProfessionalExemption pesonProfessionalExemption : giafProfessionalData .getPersonProfessionalExemptionsSet()) { if (pesonProfessionalExemption instanceof PersonGrantOwnerEquivalent && pesonProfessionalExemption.getAnulationDate() == null) { final PersonGrantOwnerEquivalent personGrantOwnerEquivalent = (PersonGrantOwnerEquivalent) pesonProfessionalExemption; int countThisPersonGrantOwnerEquivalentOnGiaf = countThisPersonGrantOwnerEquivalentOnGiaf( oracleConnection, personGrantOwnerEquivalent, logger); if (countThisPersonGrantOwnerEquivalentOnGiaf == 0) { modifications.add(new Modification() { @Override public void execute() { personGrantOwnerEquivalent.setAnulationDate(new DateTime()); } }); deleted++; } else { totalInFenix++; if (countThisPersonGrantOwnerEquivalentOnGiaf > 1) { repeted += countThisPersonGrantOwnerEquivalentOnGiaf - 1; } } } } } oracleConnection.closeConnection(); log.println("-- Grant owner equivalences --"); log.println("Total GIAF: " + count); log.println("New: " + news); log.println("Deleted: " + deleted); log.println("Not imported: " + notImported); log.println("Imported with errors: " + importedButInvalid.size()); log.println("Repeted: " + repeted); log.println("Invalid persons: " + dontExist); log.println("Total Fnix: " + totalInFenix); log.println("Total Fnix without errors: " + (totalInFenix - importedButInvalid.size())); log.println("Missing in Fnix: " + (count - totalInFenix)); return modifications; } private int countThisPersonGrantOwnerEquivalentOnGiaf(PersistentSuportGiaf oracleConnection, PersonGrantOwnerEquivalent personGrantOwnerEquivalent, Logger logger) throws SQLException { String query = getPersonGrantOwnerEquivalentQuery(personGrantOwnerEquivalent); PreparedStatement preparedStatement = null; ResultSet result = null; try { preparedStatement = oracleConnection.prepareStatement(query); result = preparedStatement.executeQuery(); if (result.next()) { int count = result.getInt("cont"); if (count > 0) { if (count > 1) { logger.debug("---> " + count + " ---> " + personGrantOwnerEquivalent.getGiafProfessionalData().getGiafPersonIdentification() + " FA: " + personGrantOwnerEquivalent.getGrantOwnerEquivalent().getGiafId()); } return count; } } return 0; } finally { if (result != null) { result.close(); } preparedStatement.close(); } } private String getPersonGrantOwnerEquivalentQuery(PersonGrantOwnerEquivalent personGrantOwnerEquivalent) { StringBuilder query = new StringBuilder(); query.append("select count(*) as cont from SLDEQUIDISP where emp_num="); query.append(personGrantOwnerEquivalent.getGiafProfessionalData().getGiafPersonIdentification()); if (personGrantOwnerEquivalent.getBeginDate() != null) { query.append(" and DATA_INICIO=to_date('"); query.append(dateFormat.print(personGrantOwnerEquivalent.getBeginDate())); query.append("','YYYY-MM-DD')"); } else { query.append(" and DATA_INICIO is null"); } if (personGrantOwnerEquivalent.getEndDate() != null) { query.append(" and DATA_FIM=to_date('"); query.append(dateFormat.print(personGrantOwnerEquivalent.getEndDate())); query.append("','YYYY-MM-DD')"); } else { query.append(" and DATA_FIM is null"); } if (personGrantOwnerEquivalent.getGrantOwnerEquivalent() != null) { query.append(" and tipo_equip='"); query.append(personGrantOwnerEquivalent.getGrantOwnerEquivalentGiafId()); query.append("'"); } else { query.append(" and tipo_equip is null"); } if (personGrantOwnerEquivalent.getMotive() != null) { query.append( " and motivo=(SELECT c.TAB_COD from SLTCODGER c where c.TAB_ID = 'FA' AND c.TAB_NUM = 85 and c.tab_cod_dsc ='"); query.append(personGrantOwnerEquivalent.getMotive()); query.append("')"); } else { query.append(" and motivo is null"); } if (personGrantOwnerEquivalent.getLocal() != null) { query.append(" and local='"); query.append(personGrantOwnerEquivalent.getLocal().replaceAll("'", "''")); query.append("'"); } else { query.append(" and local is null"); } if (!StringUtils.isEmpty(personGrantOwnerEquivalent.getGiafCountryName())) { query.append( " and cod_pais=(SELECT a.emp_nac FROM sltnac a where upper(convert(nac_dsc, 'US7ASCII', 'WE8ISO8859P1'))=upper(convert('"); query.append(personGrantOwnerEquivalent.getGiafCountryName()); query.append("', 'US7ASCII', 'WE8ISO8859P1')))"); } else { query.append(" and (cod_pais is null or cod_pais ='"); query.append(personGrantOwnerEquivalent.getGiafCountryName()); query.append("')"); } query.append(" and data_criacao=to_date('"); query.append(dateTimeFormat.print(personGrantOwnerEquivalent.getCreationDate())); query.append("','YYYY-MM-DD HH24:mi:ss')"); if (personGrantOwnerEquivalent.getModifiedDate() != null) { query.append(" and data_alteracao=to_date('"); query.append(dateTimeFormat.print(personGrantOwnerEquivalent.getModifiedDate())); query.append("','YYYY-MM-DD HH24:mi:ss')"); } else { query.append("and data_alteracao is null"); } return query.toString(); } private boolean hasPersonGrantOwnerEquivalent(GiafProfessionalData giafProfessionalData, LocalDate beginDate, LocalDate endDate, String motive, String local, String giafCountryName, Country country, GrantOwnerEquivalent grantOwnerEquivalent, String grantOwnerEquivalentGiafId, DateTime creationDate, DateTime modifiedDate) { for (PersonProfessionalExemption personProfessionalExemption : giafProfessionalData .getPersonProfessionalExemptionsSet()) { if (personProfessionalExemption instanceof PersonGrantOwnerEquivalent) { PersonGrantOwnerEquivalent personGrantOwnerEquivalent = (PersonGrantOwnerEquivalent) personProfessionalExemption; if (personGrantOwnerEquivalent.getAnulationDate() == null && Objects.equals(beginDate, personGrantOwnerEquivalent.getBeginDate()) && Objects.equals(endDate, personGrantOwnerEquivalent.getEndDate()) && Objects.equals(motive, personGrantOwnerEquivalent.getMotive()) && Objects.equals(local, personGrantOwnerEquivalent.getLocal()) && Objects.equals(giafCountryName, personGrantOwnerEquivalent.getGiafCountryName()) && Objects.equals(country, personGrantOwnerEquivalent.getCountry()) && Objects.equals(grantOwnerEquivalent, personGrantOwnerEquivalent.getGrantOwnerEquivalent()) && Objects.equals(grantOwnerEquivalentGiafId, personGrantOwnerEquivalent.getGrantOwnerEquivalentGiafId()) && Objects.equals(creationDate, personGrantOwnerEquivalent.getCreationDate()) && Objects.equals(modifiedDate, personGrantOwnerEquivalent.getModifiedDate())) { return true; } } } return false; } protected String getQuery() { StringBuilder query = new StringBuilder(); query.append( "SELECT d.DATA_FIM, d.DATA_INICIO, d.EMP_NUM, d.tipo_equip, c.tab_cod_dsc as motivo, d.local, p.nac_dsc, d.data_criacao, d.data_alteracao"); query.append( " FROM SLDEQUIDISP d, sltnac p, SLTCODGER c WHERE d.TIPO(+) = 'E' and d.cod_pais= emp_nac(+) and (d.MOTIVO = c.TAB_COD(+) and (c.TAB_COD is null or (c.TAB_ID = 'FA' AND c.TAB_NUM = 85)))"); return query.toString(); } }