package org.internna.ossmoney.services.impl;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Scanner;
import java.util.TreeSet;
import java.util.Calendar;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.SortedSet;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Collection;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.internna.ossmoney.model.Payee;
import org.internna.ossmoney.model.Account;
import org.internna.ossmoney.model.Category;
import org.internna.ossmoney.util.DateUtils;
import org.internna.ossmoney.model.Investment;
import org.internna.ossmoney.model.AccountType;
import org.internna.ossmoney.model.qif.Message;
import org.internna.ossmoney.model.Subcategory;
import org.internna.ossmoney.model.qif.Register;
import org.internna.ossmoney.model.InvestmentPrice;
import org.internna.ossmoney.model.AccountTransaction;
import org.internna.ossmoney.model.FinancialInstitution;
import org.internna.ossmoney.model.security.UserDetails;
import org.internna.ossmoney.model.support.NameValuePair;
import org.internna.ossmoney.model.InvestmentTransaction;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.transaction.annotation.Transactional;
import static org.springframework.util.StringUtils.hasText;
@Component
@Transactional
public final class QIFImporterService implements org.internna.ossmoney.services.QIFImporterService {
private static final Log logger = LogFactory.getLog(QIFImporterService.class);
private static final String MESSAGE_PARSE_LINE = "qif.parse.line";
private static final String MESSAGE_ACCOUNT_CREATED = "qif.account.create";
private static final String MESSAGE_PARSE_LINE_ERROR = "qif.parse.line.error";
private static final String MESSAGE_TRANSACTION_CREATED = "qif.account.transaction.create";
private static final SimpleDateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd");
private final NumberFormat nFormat;
public QIFImporterService() {
nFormat = NumberFormat.getInstance(Locale.ENGLISH);
nFormat.setParseIntegerOnly(false);
nFormat.setMaximumFractionDigits(2);
}
@Override public List<Message> importQIF(Locale locale, FinancialInstitution institution, InputStream qif, InputStream investment) {
List<Message> messages = new ArrayList<Message>();
Collection<NameValuePair<Register, List<Message>>> loaded = collectInvestments(investment, parse(qif));
Iterator<NameValuePair<Register, List<Message>>> parsed = loaded.iterator();
NameValuePair<Register, List<Message>> accountRegister = parsed.next();
Account account = createAccount(locale, institution, accountRegister, messages);
while (parsed.hasNext()) {
process(parsed.next(), account, messages);
}
return messages;
}
protected Collection<NameValuePair<Register, List<Message>>> collectInvestments(InputStream investment, Collection<NameValuePair<Register, List<Message>>> qif) {
if ((qif != null) & (investment != null)) {
qif.addAll(parse(investment));
qif.iterator().next().getKey().setType(Register.AccountType.INVST);
}
return qif;
}
protected Collection<NameValuePair<Register, List<Message>>> parse(InputStream qif) {
SortedSet<NameValuePair<Register, List<Message>>> registers = new TreeSet<NameValuePair<Register, List<Message>>>();
if (qif != null) {
Scanner scanner = new Scanner(qif, "CP1252");
while (scanner.hasNext()) {
NameValuePair<Register, List<Message>> register = readRegister(scanner);
if ((register != null) && (register.getKey() != null)) {
registers.add(register);
}
}
scanner.close();
}
return registers;
}
protected NameValuePair<Register, List<Message>> readRegister(Scanner scanner) {
List<String> lines = new ArrayList<String>();
try {
do {
lines.add(scanner.nextLine());
} while (!lines.get(lines.size() - 1).equals("^"));
} catch (Exception ex) {
return null;
}
return parse(lines);
}
protected NameValuePair<Register, List<Message>> parse(List<String> contents) {
NameValuePair<Register, List<Message>> result = null;
if (!CollectionUtils.isEmpty(contents)) {
logger.debug("Processing register(" + contents.size() + "): " + contents);
result = new NameValuePair<Register, List<Message>>(new Register(), new ArrayList<Message>());
for (String line : contents) {
result.getValue().add(parse(result.getKey(), line));
}
}
return result;
}
protected Message parse(Register register, String line) {
Message message = new Message(register, MESSAGE_PARSE_LINE, line);
try {
char delimiter = line.charAt(0);
switch (delimiter) {
case '!': register.setOperation(Register.Operation.OPEN_BALANCE);
register.setType(Register.AccountType.valueOf(line.substring(6).trim().toUpperCase(Locale.UK)));
break;
case 'D': Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.DAY_OF_MONTH, Integer.parseInt(line.substring(1, 3)));
calendar.set(Calendar.MONTH, Integer.parseInt(line.substring(4, 6)) - 1);
calendar.set(Calendar.YEAR, Integer.parseInt(line.substring(7, 11)));
register.setDate(calendar.getTime());
break;
case 'L': if ('[' == line.charAt(1)) {
if (!Register.Operation.OPEN_BALANCE.equals(register.getOperation())) {
register.setOperation(Register.Operation.TRANSFER);
}
register.setTargetAccount(stripCash(line));
} else {
int separator = line.indexOf(':');
if (separator < 0) {
register.setCategory(line.substring(1).trim());
register.setSubcategory(register.getCategory());
} else {
register.setCategory(line.substring(1, separator).trim());
register.setSubcategory(line.substring(separator + 1).trim());
}
}
break;
case 'C': register.setReconciled(Boolean.TRUE);
break;
case 'P': register.setPayee(stripCash(line));
break;
case 'Y': register.setInvestment(line.substring(1).trim());
break;
case 'M': register.setDescription(line.substring(1).trim());
break;
case 'T': register.setAmount(getDouble(line.substring(1).trim()));
break;
case 'N': String operation = line.substring(1).trim();
register.setOperation(Register.Operation.fromValue(operation));
break;
case 'Q': if (line.length() > 1) {
register.setQuantity(getDouble(line.substring(1).trim()));
}
break;
case 'I': if (line.length() > 1) {
register.setPrice(getDouble(line.substring(1).trim()));
}
break;
case 'S': register.setSkipped(Boolean.TRUE);
break;
}
} catch (Exception ex) {
message.setResult(false);
message.setMessageKey(MESSAGE_PARSE_LINE_ERROR);
}
return message;
}
protected String stripCash(String line) {
String stripped = line;
if (hasText(line) && line.length() > 1) {
stripped = stripped.substring(1).replace("(Cash)", "");
if ('[' == line.charAt(1)) {
stripped = stripped.substring(1, stripped.length() - 1);
}
stripped = stripped.trim();
}
return stripped;
}
protected Account createAccount(Locale locale, FinancialInstitution institution, NameValuePair<Register, List<Message>> register, List<Message> messages) {
messages.addAll(register.getValue());
Account account = createAccount(locale, institution, register.getKey());
messages.add(new Message(register.getKey(), MESSAGE_ACCOUNT_CREATED, account.getName() + "[" + account.getId() + "]"));
return account;
}
protected Account createAccount(Locale locale, FinancialInstitution institution, Register register) {
Account account = new Account();
account.setLocale(locale);
account.setHeldAt(institution);
account.setClosed(Boolean.FALSE);
account.setFavorite(Boolean.TRUE);
account.setLastModified(new Date());
account.setName(register.getTargetAccount());
account.setCreated(account.getLastModified());
account.setOwner(UserDetails.findCurrentUser());
account.setAccountType(getAccountType(register));
account.setOpened(DateUtils.getMidnight(register.getDate()));
account.setInitialBalance(new BigDecimal(register.getAmount()));
account.persist();
register.setPayee(account.getName());
getOrCreatePayee(register, account);
return account;
}
protected void process(NameValuePair<Register, List<Message>> register, Account account, List<Message> messages) {
messages.addAll(register.getValue());
messages.add(process(register.getKey(), account));
}
protected Message process(Register register, Account account) {
Message message = new Message(register, MESSAGE_TRANSACTION_CREATED);
if ((register != null) && (!register.isSkipped())) {
AccountTransaction transaction = new AccountTransaction();
transaction.setAccount(account);
transaction.setMemo(register.getDescription());
transaction.setOperationDate(register.getDate());
transaction.setAmount(new BigDecimal(register.getAmount()));
if (Register.Operation.DEPOSIT_OR_WITHDRAWAL.equals(register.getOperation())) {
transaction.setReconciled(register.getReconciled());
transaction.setPayee(getOrCreatePayee(register, account));
transaction.setSubcategory(getOrCreateSubcategory(account.getOwner(), register));
} else if (register.isInvestment()) {
transaction.setReconciled(Boolean.FALSE);
Investment investment = getOrCreateInvestment(register, account);
transaction.setPayee(investment);
InvestmentTransaction investmentTransaction = new InvestmentTransaction();
investment.addInvestment(investmentTransaction);
investmentTransaction.setAccountTransaction(transaction);
if (register.getPrice() != null) {
investmentTransaction.setPrice(new InvestmentPrice());
investmentTransaction.getPrice().setInvestment(investment);
investmentTransaction.getPrice().setPrice(register.getPrice());
investmentTransaction.getPrice().setUpdateTime(transaction.getOperationDate());
investmentTransaction.setQuantity(register.getQuantity());
} else {
investmentTransaction.setPrice(null);
investmentTransaction.setQuantity(new Double(0D));
}
transaction.setInvestment(investmentTransaction);
String subcat = Register.Operation.BUY.equals(register.getOperation()) ? "category.investment.buy" : Register.Operation.SELL.equals(register.getOperation()) ? "category.investment.sell" : "category.investment.interest";
if (Register.Operation.BUY.equals(register.getOperation())) {
transaction.setAmount(transaction.getAmount().negate());
}
transaction.setSubcategory(Subcategory.findBySubcategory(subcat, account.getOwner()));
} else {
transaction.setReconciled(Boolean.FALSE);
boolean withdrawal = register.getAmount() < 0;
String subcat = withdrawal ? "category.transfer.out" : "category.transfer.in";
if (account.isCreditCard() & !withdrawal) {
subcat = "category.cc.payment.in";
} else {
Account targetAccount = Account.findAccount(register.getTargetAccount(), account.getOwner());
if ((targetAccount != null) && (targetAccount.isCreditCard())) {
subcat = "category.cc.payment.out";
}
}
if (subcat.endsWith("in")) {
transaction.setPayee(Payee.findMySelf(account.getOwner()));
transaction.setOriginOfTheFunds(register.getTargetAccount());
} else {
register.setPayee(register.getTargetAccount());
transaction.setPayee(getOrCreatePayee(register, account));
}
transaction.setSubcategory(Subcategory.findBySubcategory(subcat, account.getOwner()));
}
transaction.persist();
message.setConfiguration(dFormat.format(transaction.getOperationDate()) + " " + transaction.getPayee().getName() + " " + nFormat.format(transaction.getAmount()));
}
return message;
}
protected Double getDouble(String line) throws ParseException {
return nFormat.parse(line).doubleValue();
}
protected Payee getOrCreatePayee(Register register, Account account) {
Payee payee = Payee.findByName(register.getPayee());
if (payee == null) {
payee = new Payee();
payee.setName(register.getPayee());
payee.setOwner(account.getOwner());
payee.persist();
payee.flush();
}
return payee;
}
protected Investment getOrCreateInvestment(Register register, Account account) {
Investment investment = Investment.findByName(register.getInvestment(), account.getOwner());
if (investment == null) {
investment = new Investment();
investment.setOwner(account.getOwner());
investment.setName(register.getInvestment());
investment.setProductType("investment.unknown");
investment.persist();
investment.flush();
}
return investment;
}
protected AccountType getAccountType(Register register) {
String type = register.getType().toString();
return AccountType.findAccountTypeByKey(type);
}
protected Subcategory getOrCreateSubcategory(UserDetails user, Register register) {
Subcategory subcategory = Subcategory.findBySubcategory(register.getSubcategory(), user);
if (subcategory == null) {
Category category = Category.findByCategory(register.getCategory());
if (category == null) {
category = new Category();
category.setCategory(register.getCategory());
category.setIncome(register.getAmount() > 0 ? Boolean.TRUE : Boolean.FALSE);
category.persist();
}
subcategory = new Subcategory();
subcategory.setOwner(user);
subcategory.setParentCategory(category);
subcategory.setCategory(register.getSubcategory());
subcategory.persist();
}
return subcategory;
}
}