ReportSheet.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2011 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.sheets;
 
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.CellType;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.amic.util.date.CheckDate;
import org.turro.string.Strings;
import org.turro.elephant.context.ElephantContext;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.MajorAccountType;
import org.turro.financials.entity.Register;
import org.turro.financials.entity.RegisterView;
import org.turro.jpa.Dao;
 
public class ReportSheet {
 
  private File sheetFile;
  private Date date;
  private int year;
  private RegisterView view;
  private List<WritableCell> clearCells;
  private Dao dao;
  private MajorAccountType currentType = MajorAccountType.MAJOR_NONE;
 
  public ReportSheet(File sheetFile, Date date, RegisterView view) {
    this.sheetFile = sheetFile;
    this.date = date;
    this.view = view;
    this.year = new CheckDate(date).getYear();
    dao = new FinancialsPU();
  }
 
  public InputStream getExcel() throws IOException, BiffException, WriteException {
    clearCells = new ArrayList<WritableCell>();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    WorkbookSettings wsWrite = new WorkbookSettings();
    wsWrite.setEncoding("UTF-8");
    WorkbookSettings wsRead = new WorkbookSettings();
    wsRead.setEncoding("ISO-8859-1");
    WritableWorkbook workbook = Workbook.createWorkbook(baos, Workbook.getWorkbook(sheetFile, wsRead), wsWrite);
    for(WritableSheet sheet : workbook.getSheets()) {
      for(int row = 0; row < sheet.getRows(); row++) {
        for(int col = 0; col < sheet.getColumns(); col++) {
          processCell(sheet, row, col);
        }
      }
      for(WritableCell cell : clearCells) {
        sheet.addCell(new Label(cell.getColumn(), cell.getRow(), ""));
      }
      clearCells.clear();
    }
    workbook.write();
    workbook.close();
    baos.close();
    return new ByteArrayInputStream(baos.toByteArray());
  }
 
  private void processCell(WritableSheet sheet, int row, int col) {
    WritableCell cell = sheet.getWritableCell(col, row);
    if(cell.getType() == CellType.LABEL) {
      Label l = (Label) cell;
      String value = l.getString().trim();
      if("ACTIVO".equalsIgnoreCase(value)) {
        currentType = MajorAccountType.MAJOR_ACTIVE;
      } else if("PATRIMONIO NETO Y PASIVO".equalsIgnoreCase(value)) {
        currentType = MajorAccountType.MAJOR_PASSIVE;
      } else if("CUENTA PERDIDAS Y GANACIAS %year".equalsIgnoreCase(value)) {
        currentType = MajorAccountType.MAJOR_EXPLOITATION;
      }
      processValue(value, sheet, row, col);
    }
  }
 
  private void processValue(String value, WritableSheet sheet, int row, int col) {
    if(value.contains("%year")) {
      value = value.replaceAll("\\%year", year + "");
      WritableCell cell = sheet.getWritableCell(col, row);
      if(cell.getType() == CellType.LABEL) {
        Label l = (Label) cell;
        l.setString(value);
      }
    }
    if(value.contains("%date")) {
      value = value.replaceAll("\\%date", SimpleDateFormat.getDateInstance().format(date));
      WritableCell cell = sheet.getWritableCell(col, row);
      if(cell.getType() == CellType.LABEL) {
        Label l = (Label) cell;
        l.setString(value);
      }
    }
    if(value.contains("%")) {
      try {
        int yearOp = Integer.valueOf(Strings.extract(value, "\\%y([\\-0-9]+)"));
        int cellExpr = Integer.valueOf(Strings.extract(value, "v([\\-0-9]+)"));
        WritableCell cell = sheet.getWritableCell(cellExpr, row);
        clearCells.add(cell);
        double amount = getAmount(year + yearOp, cell.getContents());
        sheet.addCell(new jxl.write.Number(col, row, amount));
      } catch (WriteException ex) {
        Logger.getLogger(ReportSheet.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
      }
    }
  }
 
  private double getAmount(int year, String accounts) {
    if(accounts == null) return 0;
    double amount = 0;
    String accs[] = accounts.split(",");
    for(String acc : accs) {
      amount += getValue(year, acc.trim());
    }
    return amount;
  }
 
  private double getValue(int year, String account) {
    boolean negative = false, anysign = false, asSign = false;
    if(account.endsWith("+")) {
      account = account.substring(0, account.length() - 1);
      asSign = true;
    }
    if(account.endsWith("*")) {
      account = account.substring(0, account.length() - 1);
      anysign = true;
    }
    if(account.startsWith("(")) {
      account = account.substring(1, account.length() - 1);
      negative = true;
    }
//    if(currentType.equals(MajorAccountType.MAJOR_PASSIVE)) {
//      dao.executeUpdate(
//              "update MajorAccount " +
//              "set type = ? " +
//              "where type is null and account like '" + account + "%'",
//              new Object[] {
//                currentType
//              });
//      dao.executeUpdate(
//              "update MajorAccount " +
//              "set type = ? " +
//              "where type = ? and account like '" + account + "%'",
//              new Object[] {
//                MajorAccountType.MAJOR_BOTH, MajorAccountType.MAJOR_ACTIVE
//              });
//    } else {
//      dao.executeUpdate(
//              "update MajorAccount " +
//              "set type = ? " +
//              "where type is null and account like '" + account + "%'",
//              new Object[] {
//                currentType
//              });
//    }
    if(account.equals("129")) {
      return results(year);
    } else {
      CheckDate used = new CheckDate(date);
      used.setYear(year);
      Object obj = dao.getSingleResult(
              "select sum(re.debit)-sum(re.credit) from RegisterEntry re " +
              "join re.register r " +
              "where year(r.registerDate) = ? " +
              "and r.registerDate <= ? " +
              "and r.view = ? " +
              "and re.account.id like '" + account + "%' " +
              "and r.exclude = false",
              new Object[] {
                year, used.getDate(), view
              });
      if(obj instanceof Double) {
        Double d = (Double) obj;
        if(asSign) {
          if((d < 0 && currentType.equals(MajorAccountType.MAJOR_PASSIVE)) ||
                  (d > 0 && currentType.equals(MajorAccountType.MAJOR_ACTIVE))) {
            return d;
          }
        } else {
          return d;
        }
      }
      return 0;
    }
  }
 
  private boolean existClosing(int exercise) {
    Dao dao = new FinancialsPU();
    List<Register> list = dao.getResultList(
            "select reg from Register reg " +
            "where year(reg.registerDate) = ? " +
            "and month(reg.registerDate) = 12 " +
            "and reg.exclude = TRUE and reg.closing = TRUE " +
            "and reg.document is null",
            new Object[] {
              Integer.valueOf(exercise)
            });
    return !list.isEmpty();
  }
 
  private double results(int exercise) {
    CheckDate used = new CheckDate(date);
    used.setYear(year);
    Object obj;
    if(!existClosing(exercise)) {
      obj = dao.getSingleResult(
              "select sum(re.debit)-sum(re.credit) from RegisterEntry re " +
              "join re.register r " +
              "where year(r.registerDate) = ? " +
              "and r.registerDate <= ? " +
              "and r.view = ? " +
              "and (re.account.id like '6%' or re.account.id like '7%')" +
              "and r.exclude = false",
              new Object[] {
                Integer.valueOf(exercise), used.getDate(), view
              });
      if(obj instanceof Double) {
        return ((Double) obj) + pending(exercise);
      }
    } else {
      return result129(exercise);
    }
    return 0;
  }
 
  private double pending(int exercise) {
    Object obj = dao.getSingleResult(
            "select sum(re.debit)-sum(re.credit) from RegisterEntry re " +
            "join re.register r " +
            "where year(r.registerDate) = ? " +
            "and r.view = ? " +
            "and re.account.id like '129%' " +
            "and r.exclude = false",
            new Object[] {
              Integer.valueOf(exercise), view
            });
    if(obj instanceof Double) {
      return (Double) obj;
    }
    return 0;
  }
 
  private double result129(int exercise) {
    Object obj = dao.getSingleResult(
            "select -sum(re.credit) from RegisterEntry re " +
            "join re.register r " +
            "where year(r.registerDate) = ? " +
            "and r.view = ? " +
            "and re.account.id like '129%' " +
            "and r.exclude = true",
            new Object[] {
              Integer.valueOf(exercise), view
            });
    if(obj instanceof Double) {
      return (Double) obj;
    }
    return 0;
  }
 
}