Java tutorial
/*$Id: FinanceSchemaExcelParser.java 9210 2008-03-20 00:45:05Z 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.finances.utils; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import net.sf.hibernate.Criteria; import net.sf.hibernate.HibernateException; import net.sf.hibernate.Session; import net.sf.hibernate.SessionFactory; import net.sf.hibernate.Transaction; import org.springframework.beans.factory.xml.XmlBeanDefinitionReader; import org.springframework.context.ApplicationContext; import org.springframework.context.support.GenericApplicationContext; import org.springframework.core.io.ClassPathResource; import no.abmu.finances.domain.Account; import no.abmu.finances.domain.AccountOrgUnitMapping; import no.abmu.finances.domain.MainReportSchema; import no.abmu.finances.domain.Post; import no.abmu.finances.domain.PostPeriod; import no.abmu.finances.domain.PostType; import no.abmu.finances.domain.PostValidator; import no.abmu.finances.domain.ReportSchema; import no.abmu.finances.domain.SubReportSchema; import no.abmu.finances.finders.AccountFinderSpecification; import no.abmu.finances.finders.PostFinderSpecification; import no.abmu.finances.finders.PostPeriodFinderSpecification; import no.abmu.finances.finders.PostTypeFinderSpecification; import no.abmu.organisationregister.domain.OrganisationUnit; import no.abmu.organisationregister.finders.OrgUnitFinderSpecification; /** * FinanceImporter. * * @author Erik Romson, erik@zenior.no * @author $Author:$ * @version $Rev:$ * @date $Date:$ * @copyright ABM-Utvikling */ public class FinanceImporter { private static final Log logger = (Log) LogFactory.getLog(FinanceImporter.class); private GenericApplicationContext applicationContext; private FinanceSchemaExcelParser excelParser = new FinanceSchemaExcelParser(); private SimpleDateFormat yearMonthDayFormatter = new SimpleDateFormat("yyyyMMdd"); private Map<PostPeriodKey, PostPeriod> postPeriodMap = new HashMap<PostPeriodKey, PostPeriod>(); private Map<String, PostType> postTypeMap = new HashMap<String, PostType>(); private Map<String, Account> accountMap = new HashMap<String, Account>(); private Map<SchemaKey, ReportSchema> reportSchemaMap = new HashMap<SchemaKey, ReportSchema>(); public FinanceImporter() { } protected ApplicationContext getApplicationContext() { if (applicationContext == null) { applicationContext = new GenericApplicationContext(); XmlBeanDefinitionReader xmlReader = new XmlBeanDefinitionReader(applicationContext); String[] configLocations = getConfigLocations(); for (int i = 0; i < configLocations.length; i++) { String configLocation = configLocations[i]; xmlReader.loadBeanDefinitions(new ClassPathResource(configLocation)); } applicationContext.refresh(); } return applicationContext; } protected String[] getConfigLocations() { return new String[] { "conf/common/spring/hibernate2/appContext-configuration.xml", "conf/common/spring/hibernate2/appContext-dataLayer-general.xml", "conf/common/spring/appContext-dataLayer-standalone.xml", "conf/organisationregister/spring/hibernate2/appContext-service-orgRegister.xml", "conf/finances/spring/appContext-mappings-finances.xml", "conf/organisationregister/spring/appContext-mappings-orgreg.xml", "conf/common/spring/hibernate2/appContext-util-postgres.xml", "conf/common/spring/appContext-dummyAudit.xml" }; } public ReportSchema run(String fileName, String sheetName) throws HibernateException { SessionFactory sessionFactory = (SessionFactory) getApplicationContext().getBean("abmSessionFactory"); Session session = sessionFactory.openSession(); return run(session, fileName, sheetName); } public ReportSchema runInTransaction(String fileName, String sheetName, boolean doRollback) throws HibernateException { SessionFactory sessionFactory = (SessionFactory) getApplicationContext().getBean("abmSessionFactory"); Session session = sessionFactory.openSession(); return runInTransaction(session, fileName, sheetName, doRollback); } public ReportSchema runInTransaction(String fileName, String sheetName) throws HibernateException { return runInTransaction(fileName, sheetName, false); } public ReportSchema runInTransaction(Session session, String fileName, String sheetName) throws HibernateException { return runInTransaction(session, fileName, sheetName, false); } public boolean runInTransaction(ApplicationContext context, String fileName, String sheetName, boolean doRollback) { SessionFactory sessionFactory = (SessionFactory) context.getBean("abmSessionFactory"); ReportSchema reportSchema; boolean returnValue = false; try { Session session = sessionFactory.openSession(); reportSchema = runInTransaction(session, fileName, sheetName, doRollback); } catch (HibernateException e) { logger.error("Import failed ... "); logger.error(e.toString()); return false; } if (reportSchema == null) { logger.info("Import failed ...."); returnValue = false; } else { logger.info("Import went OK"); returnValue = true; } return returnValue; } public ReportSchema runInTransaction(Session session, String fileName, String sheetName, boolean doRollback) throws HibernateException { Transaction transaction = session.beginTransaction(); ReportSchema reportSchema = null; boolean rollback = doRollback; try { reportSchema = run(session, fileName, sheetName); if (reportSchema == null) { rollback = true; } if (rollback) { logger.info("doing rollback"); transaction.rollback(); } } catch (Throwable throwable) { throwable.printStackTrace(); logger.error("doing rollback ", throwable); transaction.rollback(); } finally { if (!transaction.wasRolledBack() && (!rollback)) { transaction.commit(); } session.close(); } return reportSchema; } public ReportSchema run(Session session, String fileName, String sheetName) throws HibernateException { MainReportSchema mainReportSchema = null; ReportSchema reportSchema = null; logger.info("opening file '" + fileName + "' using sheet '" + sheetName + "'"); excelParser.setExcelFileName(fileName); excelParser.setSheetName(sheetName); String errorMessage = excelParser.checkInputValues(); if (errorMessage != null) { logger.error(errorMessage); return null; } List<AccountOrgUnitMapping> mapping = new ArrayList<AccountOrgUnitMapping>(); excelParser.load(); for (; excelParser.hasNext(); excelParser.next()) { reportSchema = getReportSchema(); //the main is always first if (mainReportSchema == null) { mainReportSchema = (MainReportSchema) reportSchema; logger.info("Importing schemaName='" + mainReportSchema.getName() + "' version='" + mainReportSchema.getVersion() + "'"); } Account account = getAccount(session); logger.info(""); logger.info("XXXXXXXXXXXXXXXX NEW ROW ROW Row='" + excelParser.getCurrentRowIdx() + "' POST_CODE='" + excelParser.getPostCode() + "' POST_VALUE_TYPE='" + excelParser.getPostValueType() + "' Account id='" + account.getId() + "' name='" + account.getName() + "' "); if (!reportSchema.getAccounts().contains(account)) { reportSchema.addAccount(account); } OrganisationUnit organisationUnit = getOrganisationUnit(session); if (organisationUnit != null) { AccountOrgUnitMapping orgUnitMapping = new AccountOrgUnitMapping(organisationUnit, account); mapping.add(orgUnitMapping); } // // handle posts // Post post = getNewPost(session); //Post post = getPost(baseExcelParser, session); account.addPost(post); Set<Post> posts = account.getPosts(); for (Post tmpPost : posts) { logger.info("Account id='" + account.getId() + "' ==> " + tmpPost.toString()); } /* Set validators = post.getValidators(); logger.info("Number of validators befor clearing existing validators: '" + validators.size() + "'"); validators.clear(); int validatorCounter=1; while (null != helper.getExcelCellValue( baseExcelParser, FinanceImporterHelper.VALIDATOR + validatorCounter)) { PostValidator postValidator = getPostValidator(baseExcelParser,validatorCounter); if (postValidator.getClass().equals(StringLengthValidator.class)) { StringLengthValidator stringValidator = (StringLengthValidator) postValidator; logger.info(" ValidatorType: " + postValidator.toString() + " with StringLength='" + stringValidator.getStringLength() + "'"); } else { logger.info(" ValidatorType: " + postValidator.toString()); } post.addValidator(postValidator); validatorCounter++; } logger.info(" Total number of validators: " + validators.size()); */ /* Iterator iterator = validators.iterator(); for (Iterator iterator = validators.iterator(); iterator.hasNext();) { } while (validators.) */ /* PostType postType = getPostType(session, baseExcelParser); post.setPostType(postType); PostPeriod period = getPeriod(session, baseExcelParser); post.setPeriod(period); */ } session.save(mainReportSchema); for (AccountOrgUnitMapping orgUnitMapping : mapping) { session.save(orgUnitMapping); } return mainReportSchema; } private Post getNewPost(Session session) throws HibernateException { String code = excelParser.getPostCode(); PostType postType = getPostType(session); PostPeriod period = getPeriod(session); PostFinderSpecification finderSpecification = new PostFinderSpecification(code); Criteria criteria = finderSpecification.createCriteria(session); List<Post> list = criteria.list(); Post retPost = null; if (list.size() == 0) { retPost = createNewPost(); retPost.setPostType(postType); retPost.setPeriod(period); session.save(retPost); logger.info("NEW POST: " + retPost); validatorLogging(retPost); } else if (list.size() == 1) { retPost = list.get(0); String postValueType = excelParser.getPostValueType(); if (!postValueType.equals(retPost.getClass().getName())) { logger.error(""); logger.error( " XXXXXXXXXXXXXXXXXX OLD NOT SAME AS DEFINITION IN NEW SCHEMA XXXXXXXXXXXXXXXXXXXX"); logger.error(""); } Set validators = retPost.getValidators(); logger.info("Number of validators befor clearing existing validators: '" + validators.size() + "'"); validators.clear(); addNewValidators(retPost); retPost.setPeriod(period); session.save(retPost); logger.info("OLD POST: " + retPost); validatorLogging(retPost); } else { retPost = list.get(0); logger.warn(""); logger.warn(" ZZZZZZZ More than on post number of posts: " + list.size() + "'"); logger.warn(""); for (Post tmpPost : list) { logger.info("OLD POSTS: " + tmpPost.toString()); validatorLogging(tmpPost); } } return retPost; } private Post createNewPost() { logger.info("createNewPost POST_VALUE_TYPE='" + excelParser.getPostValueType() + "' POST_CODE='" + excelParser.getPostCode() + "'"); Post retPost = Post.Factory.newInstance(excelParser.getPostValueType(), excelParser.getPostCode(), excelParser.getPostCode()); addNewValidators(retPost); return retPost; } private void addNewValidators(Post post) { int validatorCounter = 1; while (null != excelParser.getValidator(validatorCounter)) { PostValidator postValidator = getPostValidator(validatorCounter); post.addValidator(postValidator); validatorCounter++; } } private void validatorLogging(Post post) { Set<PostValidator> validators = post.getValidators(); int validatorCounter = 1; for (PostValidator postValidator : validators) { logger.info("Validator number: " + validatorCounter + " are of type: " + postValidator.toString()); validatorCounter++; } } private OrganisationUnit getOrganisationUnit(Session session) throws HibernateException { String orgUnitId = excelParser.getAcountOrgUnitReference(); if (orgUnitId == null) { return null; } OrgUnitFinderSpecification finderSpecification = new OrgUnitFinderSpecification(orgUnitId); List list = finderSpecification.createCriteria(session).list(); if (list.size() == 0) { System.out.println("warn, the column " + FinanceImporterHelper.ACCOUNT_URG_UNIT_REFERENCE + " and row " + excelParser.getCurrentRowIdx() + " had value \"" + orgUnitId + "\". There is no org unit with this id in the " + "database"); return null; } return (OrganisationUnit) list.get(0); } private PostPeriod getPeriod(Session session) throws HibernateException { String valueFrom = excelParser.getPostPeriodFrom(); String valueTo = excelParser.getPostPeriodTo(); Date valueFromDate = null; Date valueToDate = null; try { valueFromDate = yearMonthDayFormatter.parse(valueFrom); if (valueTo != null) { valueToDate = yearMonthDayFormatter.parse(valueTo); } } catch (ParseException e) { throw new IllegalArgumentException("Could not parse the value " + valueFrom + " or " + valueTo + ". The date has to have the format yyyyMMdd."); } PostPeriodKey key = new PostPeriodKey(valueFrom, valueTo); PostPeriod postPeriod = (PostPeriod) postPeriodMap.get(key); if (null == postPeriod) { Criteria criteria = (new PostPeriodFinderSpecification(valueFromDate, valueToDate)) .createCriteria(session); List list = criteria.list(); if (list.size() == 0) { postPeriod = new PostPeriod(valueFromDate, valueToDate); session.save(postPeriod); } else { postPeriod = (PostPeriod) list.get(0); } postPeriodMap.put(key, postPeriod); } return postPeriod; } /** * dd. * * */ class PostPeriodKey { private String from; private String to; public PostPeriodKey(String from, String to) { this.from = from; this.to = to; } public boolean equals(Object o) { if (this == o) { return true; } if (o == null || getClass() != o.getClass()) { return false; } final PostPeriodKey that = (PostPeriodKey) o; if (!from.equals(that.from)) { return false; } if (to != null ? !to.equals(that.to) : that.to != null) { return false; } return true; } public int hashCode() { int result; result = from.hashCode(); result = 29 * result + (to != null ? to.hashCode() : 0); return result; } } private PostType getPostType(Session session) throws HibernateException { String postTypeName = excelParser.getPostTypeName(); String postTypeDesc = excelParser.getPostTypeDescription(); PostType postType = postTypeMap.get(postTypeName); if (null == postType) { Criteria criteria = (new PostTypeFinderSpecification(postTypeName)).createCriteria(session); List list = criteria.list(); if (list.size() == 0) { postType = new PostType(postTypeName); postType.setDescription(postTypeDesc); session.save(postType); } else { postType = (PostType) list.get(0); } } return postType; } private PostValidator getPostValidator(int validatorCounter) { try { return PostValidator.Factory.newInstance(excelParser.getValidator(validatorCounter)); } catch (Exception e) { throw new RuntimeException("in row " + excelParser.getCurrentRowIdx(), e); } } private Post getPost(Session session) throws HibernateException { String code = excelParser.getPostCode(); Criteria criteria = (new PostFinderSpecification(code)).createCriteria(session); List<Post> list = criteria.list(); logger.info( "Row='" + excelParser.getCurrentRowIdx() + "' code='" + code + "' list size='" + list.size() + "'"); for (Post tmpPost : list) { logger.info("Post: " + tmpPost.toString()); } Post retPost = null; if (list.size() == 0) { retPost = Post.Factory.newInstance(excelParser.getPostValueType(), excelParser.getPostCode(), excelParser.getPostCode()); } else { retPost = list.get(0); } return retPost; } private Account getAccount(Session session) throws HibernateException { String accountName = excelParser.getAccountName(); Account retAccount = accountMap.get(accountName); if (retAccount == null) { Criteria criteria = (new AccountFinderSpecification(accountName)).createCriteria(session); List list = criteria.list(); if (list.size() > 0) { retAccount = (Account) list.get(0); } } if (null == retAccount) { retAccount = new Account(); retAccount.setName(accountName); retAccount.setDescription(excelParser.getAccountDescription()); accountMap.put(accountName, retAccount); } return retAccount; } private ReportSchema getReportSchema() { String schemaName = excelParser.getSchemaName(); String subSchemaName = excelParser.getSubSchemaName(); ReportSchema retSchema = null; SchemaKey schemaKey = new SchemaKey(schemaName, subSchemaName); retSchema = (ReportSchema) reportSchemaMap.get(schemaKey); if (null == retSchema) { if (subSchemaName == null) { retSchema = new MainReportSchema(schemaName); } else { retSchema = new SubReportSchema(subSchemaName); MainReportSchema mainReportSchema = (MainReportSchema) reportSchemaMap .get(new SchemaKey(schemaName, null)); if (mainReportSchema == null) { throw new IllegalArgumentException("Tried to add a subschema " + subSchemaName + " to a main schema that did not exist " + schemaName); } mainReportSchema.addSubReportSchema((SubReportSchema) retSchema); } retSchema.setDescription(excelParser.getSchemaDescription()); retSchema.setVersion(excelParser.getSchemaVersion()); reportSchemaMap.put(schemaKey, retSchema); } return retSchema; } public static void main(String[] args) throws HibernateException { boolean doRollback = false; if ((args.length == 3) && (args[2].equalsIgnoreCase("dorollback"))) { doRollback = true; System.out.println("will do rollback"); } //input // 1: path to file // 2: the name on the excel sheet, usually Ark1 (new FinanceImporter()).runInTransaction(args[0], args[1], doRollback); } /** * dd. * * */ class SchemaKey { private String schemaName; private String subSchemaName; public SchemaKey(String schemaName, String subSchemaName) { this.schemaName = schemaName; this.subSchemaName = subSchemaName; } public boolean equals(Object o) { if (this == o) { return true; } if (o == null || getClass() != o.getClass()) { return false; } final SchemaKey schemaKey = (SchemaKey) o; if (!schemaName.equals(schemaKey.schemaName)) { return false; } if (subSchemaName != null ? !subSchemaName.equals(schemaKey.subSchemaName) : schemaKey.subSchemaName != null) { return false; } return true; } public int hashCode() { int result; result = schemaName.hashCode(); result = 29 * result + (subSchemaName != null ? subSchemaName.hashCode() : 0); return result; } } }