Java tutorial
/*$Id: OrganisationUnitImportExcelParser.java 9588 2008-04-17 10:21:06Z jens $*/ /* **************************************************************************** * * * (c) Copyright 2008 ABM-utvikling * * * * This program is free software; you can redistribute it and/or modify it * * under the terms of the GNU General Public License as published by the * * Free Software Foundation; either version 2 of the License, or (at your * * option) any later version. * * * * This program 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 General * * Public License for more details. http://www.gnu.org/licenses/gpl.html * * * **************************************************************************** */ package no.abmu.lise.util; import java.math.BigDecimal; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Currency; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.Map; import java.util.Set; import no.abmu.common.excel.ExcelParserImpl; import no.abmu.lise.domain.AcceptedCurreny; import no.abmu.lise.domain.Consortium; import no.abmu.lise.domain.CustomerInvoiceType; import no.abmu.lise.domain.InvoiceLevel; import no.abmu.lise.domain.InvoiceToCustomer; import no.abmu.lise.domain.LiseComment; import no.abmu.lise.domain.PaymentToSupplier; import no.abmu.lise.domain.Product; import no.abmu.organisationregister.domain.OrganisationUnit; import no.abmu.organisationregister.service.OrganisationRegisterBaseService; import no.abmu.organisationregister.util.DataLoaderUtils; import no.abmu.util.string.StringUtil; import no.abmu.util.test.Assert; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.util.StopWatch; /** * ExcelParser for importing new Lise information. * * @author Jens.Vindvad, Jens.Vindvad@abm-utvikling.no * @author Erlend verby; erlend.overby@hypatia.no * @author $Author: jens $ * @version $Rev: 9588 $ * @date $Date: 2008-04-17 12:21:06 +0200 (Thu, 17 Apr 2008) $ * @copyright ABM-Utvikling * */ public class LiseImportExcelParser extends ExcelParserImpl { public static final DateFormat LISE_INPUT_DATEFORMAT = new SimpleDateFormat("dd.MM.yyyy"); /* * The following column names are the only ones that would be read */ /* SHEET 1 */ private static final String SUPPLIER_NAME = "SUPPLIER_NAME"; // required private static final String CONSORTIUM_NAME = "CONSORTIUM_NAME"; // required private static final String C_DESCRIPTION = "C_DESCRIPTION"; private static final String C_AGREEMENT_START_DATE = "C_AGREEMENT_START_DATE"; // required private static final String C_AGREEMENT_END_DATE = "C_AGREEMENT_END_DATE"; private static final String C_URL = "C_URL"; private static final String C_INVOICE_LEVEL = "C_INVOICE_LEVEL"; private static final String C_CURRENCY = "C_CURRENCY"; // required /* * private static final String C_CURRENCY_BUFFER="C_CURRENCY_BUFFER"; Agreed * to remove at meeting on 2008-08-25 */ private static final String C_ABMU_CONTACT_PERSON = "C_ABMU_CONTACT_PERSON"; private static final String C_SUPPLIER_CONTACT_PERSON = "C_SUPPLIER_CONTACT_PERSON"; private static final String PRODUCT_NAME = "PRODUCT_NAME"; // required private static final String P_DESCRIPTION = "P_DESCRIPTION"; // required private static final String P_PRICE_MODEL = "P_PRICE_MODEL"; private static final String RelationCustomerProduct_NAME = "RelationCustomerProduct_NAME"; // required private static final String RCP_USERNAME = "RCP_USERNAME"; private static final String RCP_PASSWORD = "RCP_PASSWORD"; private static final String RCP_PRICE_CATEGORY = "RCP_PRICE_CATEGORY"; private static final String RCP_CONCURRENT_USERS = "RCP_CONCURRENT_USERS"; private static final String RCP_CONTACT_PERSON = "RCP_CONTACT_PERSON"; // required private static final String RCP_COMMENT = "RCP_COMMENT"; private static final String RCP_REFNAME = "RCP_REFNAME"; private static final String RCP_ORG_ID = "RCP_ORG_ID"; /* SHEET 2 */ // private static final String CONSORTIUM_NAME="CONSORTIUM_NAME"; // // required - Defined in SHEET 1 // private static final String // C_AGREEMENT_START_DATE="C_AGREEMENT_START_DATE"; // required - Defined in // SHEET 1 private static final String SP_PAYMENT_YEAR = "SP_PAYMENT_YEAR"; // required private static final String SP_PAYMENT_DATE = "SP_PAYMENT_DATE"; private static final String SP_INVOICE_NUMBER = "SP_INVOICE_NUMBER"; private static final String SP_PAYMENT_CURRENCY = "SP_PAYMENT_CURRENCY"; // required private static final String SP_PAYMENT_AMOUNT = "SP_PAYMENT_AMOUNT"; // required private static final String SP_PAYMENT_COMMENT = "SP_PAYMENT_COMMENT"; /* SHEET 3 */ // private static final String PRODUCT_NAME="PRODUCT_NAME"; // required - // Defined in SHEET 1 private static final String CUSTOMER_NAME = "CUSTOMER_NAME"; // required private static final String CUSTOMERPAYMENT_INVOICE_YEAR = "CUSTOMERPAYMENT_INVOICE_YEAR"; // required private static final String CUSTOMERPAYMENT_INVOICE_TYPE = "CP_INVOICE_TYPE"; // required private static final String CP_INVOICE_DATE = "CP_INVOICE_DATE"; private static final String CP_ORIGINAL_AMOUNT = "CP_ORIGINAL_AMOUNT"; private static final String CP_PROD_INVOICE_AMOUNT = "CP_PROD_INVOICE_AMOUNT"; // required private static final String CP_INVOICE_NUM = "CP_INVOICE_NUM"; private static final String CP_INVOICE_COMMENT = "CP_INVOICE_COMMENT"; private static final Log logger = (Log) LogFactory.getLog(LiseImportExcelParser.class); private OrganisationRegisterBaseService baseService; public LiseImportExcelParser() { } /* * Initialising of values for more efficient processing */ public void init() { // do nothing for the moment } public OrganisationRegisterBaseService getOrganisationRegisterBaseService() { return baseService; } public void setOrganisationRegisterBaseService(OrganisationRegisterBaseService baseService) { this.baseService = baseService; } /** * Check for necessary input values. * * Returns null if OK, else error message with description of missing * input values. * * @return - errorMessage equals Null if OK, else description of * missing input values. */ public String checkInputValues() { logger.info("Start testing for nessesary input values ..."); StopWatch stopWatch = new StopWatch(); stopWatch.start(); String[] notNullNamesSheet1 = { SUPPLIER_NAME, CONSORTIUM_NAME, C_AGREEMENT_START_DATE, PRODUCT_NAME, // P_DESCRIPTION, C_CURRENCY, RelationCustomerProduct_NAME }; String[] notNullNamesSheet2 = { CONSORTIUM_NAME, C_AGREEMENT_START_DATE, SP_PAYMENT_YEAR, SP_PAYMENT_CURRENCY, SP_PAYMENT_AMOUNT }; String[] notNullNamesSheet3 = { PRODUCT_NAME, CUSTOMER_NAME, CUSTOMERPAYMENT_INVOICE_YEAR }; StringBuffer resBuffer = new StringBuffer(); checkSheetForNotNullNames("Sheet1", notNullNamesSheet1, resBuffer); checkSheetForNotNullNames("Sheet2", notNullNamesSheet2, resBuffer); checkSheetForNotNullNames("Sheet3", notNullNamesSheet3, resBuffer); String errorMessage = resBuffer.toString(); if (StringUtil.notEmpty(errorMessage)) { logger.error(errorMessage); } else { logger.info("All nessesary input values were present ..."); } stopWatch.stop(); logger.info("Testing nessesary input values took " + stopWatch.getTotalTimeMillis() + " ms."); return errorMessage; } public Consortium createConsortium() { Consortium consortium = new Consortium(); consortium.setConsortiumName(getConsortiumName()); consortium.setConsortiumAgreementStartDate(getConsortiumAgreementStartDate()); consortium.setConsortiumAgreementEndDate(getConsortiumAgreementEndDate()); consortium.setDescription(getConsortiumDescription()); consortium.setUrl(getConsortiumUrl()); consortium.setInvoiceLevel(getConsortiumInvoiceLevel()); consortium.setCurrency(getConsortiumCurrency()); return consortium; } public Product createProduct() { Product product = new Product(); product.setProductName(getProductName()); product.setDescription(getProductDescription()); return product; } public PaymentToSupplier createPaymentToSupplier() { PaymentToSupplier paymentToSupplier = new PaymentToSupplier(); paymentToSupplier.setSubscriptionYear(getSupplierPaymentSubscriptionYear()); paymentToSupplier.setDueDate(getSpPaymentDate()); paymentToSupplier.setInvoiceNumber(getSpInvoiceNumber()); paymentToSupplier.setAmount(getSpPaymentAmount()); paymentToSupplier.setCurrency(getSupplierPaymentCurrency()); paymentToSupplier.setLiseComment(getPaymentToSupplierComment()); return paymentToSupplier; } public InvoiceToCustomer createInvoiceToCustomer() { InvoiceToCustomer invoiceToCustomer = new InvoiceToCustomer(); invoiceToCustomer.setCustomerInvoiceType(getCustomerPaymentInvoiceType()); invoiceToCustomer.setSubscriptionPeriod(getCustomerPaymentInvoiceYear()); invoiceToCustomer.setInvoiceGenerated(new Date()); invoiceToCustomer.setOriginalAmount(getCustomerPaymentOriginalAmount()); invoiceToCustomer.setSpecialChage(getCpProdInvoiceAmount()); // currency // precentageCurrencyBuffer invoiceToCustomer.setInvoiceNumber(getCustomerPaymentInvoiceNum()); invoiceToCustomer.setInvoiceDate(getCustomerPaymentInvoiceDate()); invoiceToCustomer.setLiseComment(getInvoiceToCustomerComment()); return invoiceToCustomer; } public LiseComment getCustomerProductComment() { String commentText = getRcpComment(); if (StringUtil.isEmpty(commentText)) { return null; } return createLiseComment(commentText); } public LiseComment getPaymentToSupplierComment() { return createLiseComment(getSpPaymentComment()); } public LiseComment getInvoiceToCustomerComment() { return createLiseComment(getCpInvoiceComment()); } public String getSupplierName() { String res = getCellValueAsString(SUPPLIER_NAME); if (StringUtil.isEmpty(res)) { Assert.assertTrue("Supplier name has to exists", false); } return res.trim(); } public String getConsortiumName() { String res = getCellValueAsString(CONSORTIUM_NAME); if (StringUtil.isEmpty(res)) { Assert.assertTrue("Consortium name has to exists", false); } return res.trim(); } public Date getConsortiumAgreementStartDate() { return getCellValueAsDate(C_AGREEMENT_START_DATE, LISE_INPUT_DATEFORMAT); } public Date getConsortiumAgreementEndDate() { return getCellValueAsDate(C_AGREEMENT_END_DATE, LISE_INPUT_DATEFORMAT); } private String getConsortiumDescription() { String res = getCellValueAsString(C_DESCRIPTION); return (StringUtil.isEmpty(res) ? "" : res); } private String getConsortiumUrl() { String res = getCellValueAsString(C_URL); return (StringUtil.isEmpty(res) ? "" : res); } private InvoiceLevel getConsortiumInvoiceLevel() { String res = getCellValueAsString(C_INVOICE_LEVEL); if (res == null) { res = null; } return InvoiceLevel.getInstance(res); } private Currency getConsortiumCurrency() { String res = getCellValueAsString(C_CURRENCY); return checkAndGetCurrencyFromCurrencyCode(res); } public String getConsortiumAbmuContactPerson() { String res = getCellValueAsString(C_ABMU_CONTACT_PERSON); return (StringUtil.isEmpty(res) ? "" : res); } public String getConsortiumSupplierContactPerson() { String res = getCellValueAsString(C_SUPPLIER_CONTACT_PERSON); return (StringUtil.isEmpty(res) ? "" : res); } public String getProductName() { String res = getCellValueAsString(PRODUCT_NAME); return (StringUtil.isEmpty(res) ? "" : res); } private String getProductDescription() { String res = getCellValueAsString(P_DESCRIPTION); return (StringUtil.isEmpty(res) ? "" : res); } private String getPPriceModel() { String res = getCellValueAsString(P_PRICE_MODEL); return (StringUtil.isEmpty(res) ? "" : res); } private String getRelationCustomerProductname() { String res = getCellValueAsString(RelationCustomerProduct_NAME); return (StringUtil.isEmpty(res) ? "" : res); } private String getRcpUsername() { String res = getCellValueAsString(RCP_USERNAME); return (StringUtil.isEmpty(res) ? "" : res); } private String getRcpPassword() { String res = getCellValueAsString(RCP_PASSWORD); return (StringUtil.isEmpty(res) ? "" : res); } private String getRcpPriceCategory() { String res = getCellValueAsString(RCP_PRICE_CATEGORY); return (StringUtil.isEmpty(res) ? "" : res); } private String getRcpConcurrentUsers() { String res = getCellValueAsString(RCP_CONCURRENT_USERS); return (StringUtil.isEmpty(res) ? "" : res); } public String getRcpContactPerson() { String res = getCellValueAsString(RCP_CONTACT_PERSON); return (StringUtil.isEmpty(res) ? "" : res); } private String getRcpComment() { String res = getCellValueAsString(RCP_COMMENT); return (StringUtil.isEmpty(res) ? "" : res); } /* Sheet2 */ private String getSupplierPaymentSubscriptionYear() { String res = getCellValueAsString(SP_PAYMENT_YEAR); return (StringUtil.isEmpty(res) ? "" : res); } private Date getSpPaymentDate() { return getCellValueAsDate(SP_PAYMENT_DATE, LISE_INPUT_DATEFORMAT); } private String getSpInvoiceNumber() { String res = getCellValueAsString(SP_INVOICE_NUMBER); return (StringUtil.isEmpty(res) ? "" : res); } private Currency getSupplierPaymentCurrency() { String res = getCellValueAsString(SP_PAYMENT_CURRENCY); return checkAndGetCurrencyFromCurrencyCode(res); } private BigDecimal getSpPaymentAmount() { return bigDecimalFromCell(SP_PAYMENT_AMOUNT); } private String getSpPaymentComment() { String res = getCellValueAsString(SP_PAYMENT_COMMENT); return (StringUtil.isEmpty(res) ? "" : res); } /* Sheet3 */ public String getCustomerName() { String res = getCellValueAsString(CUSTOMER_NAME); return (StringUtil.isEmpty(res) ? "" : res); } private String getCustomerPaymentInvoiceYear() { String res = getCellValueAsString(CUSTOMERPAYMENT_INVOICE_YEAR); return (StringUtil.isEmpty(res) ? "" : res); } private CustomerInvoiceType getCustomerPaymentInvoiceType() { String res = getCellValueAsString(CUSTOMERPAYMENT_INVOICE_TYPE); if (StringUtil.isEmpty(res)) { logger.error("CUSTOMER PAYMENT INVOICE TYPE HAS NO VALUE in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")"); } return CustomerInvoiceType.getInstance(res); } private Date getCustomerPaymentInvoiceDate() { return getCellValueAsDate(CP_INVOICE_DATE, LISE_INPUT_DATEFORMAT); } private BigDecimal getCustomerPaymentOriginalAmount() { return bigDecimalFromCell(CP_ORIGINAL_AMOUNT); } private BigDecimal getCpProdInvoiceAmount() { return bigDecimalFromCell(CP_PROD_INVOICE_AMOUNT); } private String getCustomerPaymentInvoiceNum() { String res = getCellValueAsString(CP_INVOICE_NUM); return (StringUtil.isEmpty(res) ? "" : res); } private String getCpInvoiceComment() { String res = getCellValueAsString(CP_INVOICE_COMMENT); return (StringUtil.isEmpty(res) ? "" : res); } public boolean checkExistenceOfOrganisations() { logger.info("Executing checkExistenceOfOrganisations"); boolean missingOrganisations = false; Set<String> organisationNames = new HashSet<String>(); StopWatch stopWatch = new StopWatch(); stopWatch.start(); load(); for (; hasNext(); next()) { String name = getRelationCustomerProductname(); if (!organisationNames.contains(name)) { organisationNames.add(name); } String supplierName = getSupplierName(); if (!organisationNames.contains(supplierName)) { organisationNames.add(supplierName); } } setSheetName("Sheet3"); load(); for (; hasNext(); next()) { String name = getCustomerName(); if (!organisationNames.contains(name)) { organisationNames.add(name); } } logger.info("Found a total of (" + organisationNames.size() + ") organisationunits in the excel file ..."); StopWatch stopWatchGetOrgunits = new StopWatch(); stopWatchGetOrgunits.start(); Object[] allOrganisations = baseService.findAll(no.abmu.organisationregister.domain.OrganisationUnit.class); stopWatchGetOrgunits.stop(); logger.info("Getting '" + allOrganisations.length + "' OrganisationUnits from db took '" + stopWatchGetOrgunits.getTotalTimeMillis() + "' ms."); for (int i = 0; i < allOrganisations.length; i++) { OrganisationUnit organisationUnit = (OrganisationUnit) allOrganisations[i]; String name = organisationUnit.getName().getReference(); if (!organisationNames.contains(name)) { logger.error("NO OrganisationUnit is registered for (" + name + ") this should be registered !"); missingOrganisations = true; } } stopWatch.stop(); logger.info("Testing for existence of organisations took " + stopWatch.getTotalTimeMillis() + " ms."); return missingOrganisations; } public Map<String, String> getRelationCustomerProductOrgUnitColumns() { Map<String, String> excelColumnNames = new HashMap<String, String>(); excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_NAME_EXCEL_COLUMN, RelationCustomerProduct_NAME); excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_REF_NAME_EXCEL_COLUMN, RCP_REFNAME); excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_ID_EXCEL_COLUMN, RCP_ORG_ID); return excelColumnNames; } public Map<String, String> getConsortiumSupplierOrgUnitColumns() { Map<String, String> excelColumnNames = new HashMap<String, String>(); excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_NAME_EXCEL_COLUMN, SUPPLIER_NAME); /* @TODO Add these when excel sheet is updated excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_REF_NAME_EXCEL_COLUMN, RCP_REFNAME); excelColumnNames.put(DataLoaderUtils.KEY_ORGANISATIONUNIT_ID_EXCEL_COLUMN, RCP_ORG_ID); */ return excelColumnNames; } private LiseComment createLiseComment(String commentString) { String newCommentString = (StringUtil.isEmpty(commentString) ? "" : commentString); LiseComment liseComment = new LiseComment(); liseComment.setCommentText(newCommentString); liseComment.setCreatedBy("EXCEL IMPORT"); liseComment.setLastUpdatedBy("EXCEL IMPORT"); return liseComment; } private void checkSheetForNotNullNames(String sheetName, String[] notNullNamesInSheet, StringBuffer errMessages) { setSheetName(sheetName); String resSheet = checkInputValues(notNullNamesInSheet); if (StringUtil.notEmpty(resSheet)) { errMessages.append("Missing required values in ("); errMessages.append(getSheetName()); errMessages.append(") file ("); errMessages.append(getExcelFileName()); errMessages.append(")"); errMessages.append("\n"); errMessages.append(resSheet); } } private Currency checkAndGetCurrencyFromCurrencyCode(String currencyCode) { checkCurrency(currencyCode); return getCurrencyFromCurrencyCode(currencyCode); } private void checkCurrency(String currencyCode) { if (StringUtil.isEmpty(currencyCode)) { // no currency code we report and do nothing. logger.error( "CURRENCY HAS NO VALUE in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")"); return; } Currency currency = getCurrencyFromCurrencyCode(currencyCode); if (currency == null) { String errorMessage = "'" + currencyCode + "' is not a valid currencyCode."; logger.error(errorMessage); throw new IllegalArgumentException(errorMessage); } if (!AcceptedCurreny.isCurrencyAccepted(currency)) { String errorMessage = "'" + currency.getCurrencyCode() + "' is not an accepted currency."; logger.error(errorMessage); throw new IllegalArgumentException(errorMessage); } } private BigDecimal bigDecimalFromCell(String columnName) { Double doubleValue = getCellValueAsDouble(columnName); if (doubleValue == null) { return null; } return BigDecimal.valueOf(doubleValue); } /* private BigDecimal stringToBigDecimal(String string) { if (StringUtil.isEmpty(string)) { return null; } Double double1 = null; BigDecimal bigDecimal = null; try { double1 = Double.valueOf(string); } catch (NumberFormatException nfe) { logger.error("[stringToBigDecimal] (Double) NumberFormatException in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")" + " with value ='" + string + "'"); return null; } catch (Exception e) { logger.error("[stringToBigDecimal] (Double) in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")" + " with value ='" + string + "'"); return null; } try { bigDecimal = new BigDecimal(double1); } catch (NumberFormatException nfe) { logger.error("[stringToBigDecimal] (BigDecimal) NumberFormatException in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")" + " with value ='" + string + "'"); return null; } catch (Exception e) { logger.error("[stringToBigDecimal] (BigDecimal) in sheet(" + getSheetName() + ") row(" + getCurrentRowNumber() + ")" + " with value ='" + string + "'"); return null; } return bigDecimal; } */ /* private Calendar getCalendarFromString(String res) { if (res.length() != 10) { logger.debug("DATE has wrong number of characters, should be 10: dd.mm.yyyy ! Date value was (" + res + ")"); return null; } String sYear = res.substring(6, 10); String sMonth = res.substring(3, 5); String sDay = res.substring(0, 2); int year = Integer.valueOf(sYear); int month = Integer.valueOf(sMonth); int day = Integer.valueOf(sDay); Calendar calendar = Calendar.getInstance(); calendar.set(year, month, day); calendar.set(Calendar.HOUR_OF_DAY, 12); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); return calendar; } */ /* private Date getDateFromColumn(String columnName) { String dateAsString = getCellValueAsString(columnName); if (dateAsString == null) { return null; } try { return DateUtil.parseDateString(LISE_INPUT_DATEFORMAT, dateAsString); } catch (IllegalArgumentException e) { String warnMessage = "Column with name ='" + columnName + "' in Row: [" + getCurrentRowNumber() + "] in sheet " + getSheetName() + " value = '" + dateAsString + "' is not valid date - exptcted format (dd.MM.yyyy)"; logger.warn(warnMessage); } return null; } */ private Currency getCurrencyFromCurrencyCode(String string) { String currencyCode = string.trim(); Currency currency = null; try { currency = Currency.getInstance(currencyCode); } catch (IllegalArgumentException e) { if (logger.isDebugEnabled()) { logger.debug("'" + currencyCode + "' is not a valid currencyCode."); } } return currency; } }