BankAccounts.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2015 Lluis Turró Cutiller <http://www.turro.org/>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
 
package org.turro.financials.account.logic;
 
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import org.turro.command.Command;
import org.turro.command.Context;
import org.turro.elephant.context.ElephantContext;
import org.turro.elephant.impl.util.FileUtil;
import org.turro.file.FileWrapper;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.BankAccount;
import org.turro.financials.entity.Contract;
import org.turro.jpa.Dao;
import org.turro.math.Round;
import org.zkoss.lang.Strings;
 
public class BankAccounts {
 
  private static final String BANK_IMPORT = "/WEB-INF/bank/n43.txt";
  
  public static Collection<StatementEntry> getMovements(String account, Date start, Date end) {
    Contract contract = null;
    if(!Strings.isBlank(account) && account.length() == 10) {
      contract = getContract(account);
    }
    ArrayList<StatementEntry> list = new ArrayList<>();
    if(contract != null) {
      Dao dao = new FinancialsPU();
      for(BankAccount ba : (List<BankAccount>) dao.getResultList(
              "select ba from BankAccount ba " +
              "where ba.contract = ? " +
              "and ba.transactionDate >= ? " +
              "and ba.transactionDate <= ?",
              new Object[] {
                contract, start, end
              })) {
        list.add(new BankEntry(ba));
      }
    }
    return list;
  }
  
  public static void importN43(final Contract contract) {
    final File n43 = new File(ElephantContext.getRealPath(BANK_IMPORT));
    FileWrapper fw = new FileWrapper(n43);    
    fw.uploadContent(new Command() {
      @Override
      public Object execute(Context context) {
        if(!importFromXls(contract, n43)) {
          try {
            importFromFile(contract, n43);
          } catch (IOException ex) {
            Logger.getLogger(BankAccounts.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
          }
        }
        return null;
      }
 
    });
  }  
 
  private static void importFromFile(Contract contract, File n43) throws FileNotFoundException, IOException {
    Dao dao = new FinancialsPU();
    try (BufferedReader br = FileUtil.getBufferedFile(n43)) {
      String line;
      double balance = 0;
      long order = 0;
      BankAccount bankAccount = null;
      while ((line = br.readLine()) != null) {
        if(line.startsWith("11")) {
          if(Strings.isBlank(contract.getGlobalId())) {
            contract.setGlobalId(line.substring(10, 20));
          }
          balance = Double.valueOf(line.substring(33, 47)) / 100;
          if(line.charAt(32) == '1') {
            balance = -balance;
          }
          deleteFromTo(contract, getDate(line.substring(20, 26)), getDate(line.substring(26, 32)));
        } else if(line.startsWith("22")) {
          if(bankAccount != null) {
            dao.saveObject(bankAccount);
          }
          bankAccount = new BankAccount();
          bankAccount.setContract(contract);
          bankAccount.setTransactionDate(getDate(line.substring(10, 16)));
          bankAccount.setValueDate(getDate(line.substring(16, 22)));
          bankAccount.setRegisterOrder(order++);
          if(line.charAt(27) == '1') {
            bankAccount.setCredit(Double.valueOf(line.substring(28, 42)) / 100);
            balance = new Round(balance - bankAccount.getCredit()).decimals(2).value();
            bankAccount.setBalance(balance);
          } else {
            bankAccount.setDebit(Double.valueOf(line.substring(28, 42)) / 100);
            balance = new Round(balance + bankAccount.getDebit()).decimals(2).value();
            bankAccount.setBalance(balance);
          }
        } else if(line.startsWith("23")) {
          if(Strings.isBlank(bankAccount.getConcept())) {
            bankAccount.setConcept(line.substring(4).replaceAll("\\s+", " "));
          }
        } else if(line.startsWith("88")) {
          if(bankAccount != null) {
            dao.saveObject(bankAccount);
          }
        }
      }
    }
  }
 
  private static boolean importFromXls(Contract contract, File n43) {
    Dao dao = new FinancialsPU();
    try {
      WorkbookSettings wsRead = new WorkbookSettings();
      wsRead.setEncoding("ISO-8859-1");
      Workbook workbook = Workbook.getWorkbook(n43, wsRead);
      Sheet sheet = workbook.getSheet(0);
      for (int row = 3; row < sheet.getRows(); row++) {
        BankAccount bankAccount = new BankAccount();
        bankAccount.setContract(contract);
        Cell cell = sheet.getCell(1, row);
        if (cell.getType().equals(CellType.DATE)) {
          bankAccount.setTransactionDate(((DateCell) cell).getDate());
        }
        cell = sheet.getCell(2, row);
        if (cell.getType().equals(CellType.DATE)) {
          bankAccount.setValueDate(((DateCell) cell).getDate());
        }
        bankAccount.setRegisterOrder(row - 3);
        cell = sheet.getCell(4, row);
        if (cell.getType().equals(CellType.NUMBER)) {
          double num = ((NumberCell) cell).getValue();
          if (num < 0) {
            bankAccount.setCredit(-num);
          } else {
            bankAccount.setDebit(num);
          }
        }
        cell = sheet.getCell(5, row);
        if (cell.getType().equals(CellType.NUMBER)) {
          double num = ((NumberCell) cell).getValue();
          bankAccount.setBalance(num);
        }
        cell = sheet.getCell(0, row);
        if (cell.getType().equals(CellType.LABEL)) {
          LabelCell label = (LabelCell) cell;
          bankAccount.setConcept(label.getString());
        }
        dao.saveObject(bankAccount);
      }
    } catch (IOException | BiffException ex) {
      Logger.getLogger(BankAccounts.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
      return false;
    }
    return true;
  }
  
  private static ThreadLocal<DateFormat> dateFormat = new ThreadLocal<DateFormat>() {
    @Override
    protected DateFormat initialValue() {
      return new SimpleDateFormat("yyMMdd");
    }
  };
 
  private static Date getDate(String date) {
    try {
      return dateFormat.get().parse(date);
    } catch (ParseException ex) {
      Logger.getLogger(BankAccounts.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(date), ex);
      return null;
    }
  }
 
  private static void deleteFromTo(Contract contract, Date start, Date end) {
    Dao dao = new FinancialsPU();
    dao.executeUpdate(
            "delete from BankAccount ba " +
            "where ba.contract = ? " +
            "and ba.transactionDate >= ? " +
            "and ba.transactionDate <= ?",
            new Object[] {
              contract, start, end
            });
  }
 
  private static Contract getContract(String account) {
    Dao dao = new FinancialsPU();
    return (Contract) dao.getSingleResultOrNull(
            "select c from Contract c where c.id = ? " +
            "and c.contractDefinition.asCash = ?",
            new Object[] { 
              Long.valueOf(account.substring(5)),
              account.substring(0, 3)
            });
  }
  
}