BalanceFilter.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.register;
 
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import org.amic.util.date.CheckDate;
import org.turro.contacts.Contact;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.*;
import org.turro.financials.view.ViewSets;
import org.turro.i18n.I_;
 
public class BalanceFilter {
 
  private ViewSets viewSets;
  private Date fromDate, toDate;
  private Contract contract;
  private Contact contact;
  private Department department;
  private Service service;
  private Headquarters headquarters;
  private Company company;
  private List<Long> contractListIds;
  private int breakdown = 4;
 
  public BalanceFilter() {
    fromDate = new CheckDate().setDay(1).setMonth(1).getDate();
    toDate = new Date();
    viewSets = new ViewSets();
  }
 
  public int getBreakdown() {
    return breakdown;
  }
 
  public void setBreakdown(int breakdown) {
    this.breakdown = breakdown;
  }
 
  public boolean hasOneGroup() {
    return !(viewSets.getGroupA().isEmpty());
  }
 
  public boolean hasTwoGroup() {
    return !(viewSets.getGroupA().isEmpty()) &&
           !(viewSets.getGroupB().isEmpty());
  }
 
  public Date getFromDate() {
    return fromDate;
  }
 
  public void setFromDate(Date fromDate) {
    this.fromDate = fromDate;
  }
 
  public Date getToDate() {
    return toDate;
  }
 
  public void setToDate(Date toDate) {
    this.toDate = toDate;
  }
 
  public ViewSets getViewSets() {
    return viewSets;
  }
 
  public void setViewSets(ViewSets viewSets) {
    this.viewSets = viewSets;
  }
 
  public Company getCompany() {
    return company;
  }
 
  public void setCompany(Company company) {
    clearValues();
    this.company = company;
  }
 
  public Contact getContact() {
    return contact;
  }
 
  public void setContact(Contact contact) {
    clearValues();
    this.contact = contact;
  }
 
  public Contract getContract() {
    return contract;
  }
 
  public void setContract(Contract contract) {
    clearValues();
    this.contract = contract;
  }
 
  public Department getDepartment() {
    return department;
  }
 
  public void setDepartment(Department department) {
    clearValues();
    this.department = department;
  }
 
  public Headquarters getHeadquarters() {
    return headquarters;
  }
 
  public void setHeadquarters(Headquarters headquarters) {
    clearValues();
    this.headquarters = headquarters;
  }
 
  public Service getService() {
    return service;
  }
 
  public void setService(Service service) {
    clearValues();
    this.service = service;
  }
 
  public String getFilterString() {
    if(company != null) {
      return I_.get("Company") + ": " + company.getName();
    } else if(contact != null) {
      return I_.get("Contact") + ": " + contact.getName();
    } else if(contract != null) {
      return I_.get("Contract") + ": " + contract.getFullDescription();
    } else if(department != null) {
      return I_.get("Department") + ": " + department.getFullName();
    } else if(headquarters != null) {
      return I_.get("Headquarters") + ": " + headquarters.getName();
    } else if(service != null) {
      return I_.get("Service") + ": " + service.getFullName();
    }
    return "";
  }
 
  public String getFullFilterString() {
    return viewSets.getStringFilter() + " - " + getFilterString();
  }
 
  public Double[] getAmountsA(String account) {
    return getAmounts(account, viewSets.getSqlWhereA("entry.register.view"));
  }
 
  public Double[] getAmountsB(String account) {
    return getAmounts(account, viewSets.getSqlWhereB("entry.register.view"));
  }
 
  public Double[] getAmounts(String account, String viewFilter) {
    Object[] pars = new Object[] {
              account + "%",
              fromDate,
              toDate
            };
    Object[] o = (Object[]) new FinancialsPU().getSingleResult(
            "select sum(round(entry.debit, 4) + 0.0), sum(round(entry.credit, 4) + 0.0) " +
            "from RegisterEntry as entry " +
            "where entry.account.id like ? " +
          getInClause("entry.account.id") +
            "and entry.register.registerDate >= ? " +
            "and entry.register.registerDate <= ? " +
            "and entry.register.exclude = FALSE " +
            viewFilter,
            pars);
    return new Double[] {
      (Double) (o[0] == null ? 0.0 : o[0]),
      (Double) (o[1] == null ? 0.0 : o[1])
    };
  }
 
  public List<MajorAccount> getMajorAccounts(String parent) {
    Object[] pars = new Object[] {
              fromDate,
              toDate
            };
    if(parent == null) {
      parent = "";
    }
    parent += "_";
    List<MajorAccount> l = new FinancialsPU().getResultList(
            "select m from MajorAccount as m " +
            "where m.account like '" + parent + "' " +
            "and exists ( " +
            "  select re from RegisterEntry as re " +
            "  where re.account.id like concat(m.account, '%') " +
            getInClause("re.account.id") +
            "  and re.register.registerDate >= ? " +
            "  and re.register.registerDate <= ? " +
              viewSets.getSqlWhereAB("re.register.view") +
            ") " +
            "order by m.account",
            pars);
    return l;
  }
 
  public List<Account> getAccounts(String parent) {
    Object[] pars = new Object[] {
              fromDate,
              toDate
            };
    return new FinancialsPU().getResultList(
      "select a from Account as a " +
      "where exists ( " +
      "  select ma from MajorAccount as ma " +
      "  where ma.account = '" + parent + "' " +
      "  and a.id like concat(ma.account, '_%') " +
      "  and not exists ( " +
      "    select ma2 from MajorAccount as ma2 " +
      "    where ma2.account like concat(substring(a.id, 1, length(ma.account) + 1), '%') " +
      "  ) " +
      ") " +
      "and exists ( " +
      "  select re from RegisterEntry as re " +
      "  where re.account.id = a.id " +
      getInClause("re.account.id") +
      "  and re.register.registerDate >= ? " +
      "  and re.register.registerDate <= ? " +
         viewSets.getSqlWhereAB("re.register.view") +
      ") " +
      "order by a.id",
      pars);
  }
 
  public Collection<AccountRI> getAccountRIs() {
    Object[] pars = new Object[] {
              fromDate,
              toDate
            };
    return new AccountRIAdapter(new FinancialsPU().getResultList(
      "select a from Account as a " +
      "where exists ( " +
      "  select re from RegisterEntry as re " +
      "  where re.account.id = a.id " +
      getInClause("re.account.id") +
      "  and re.register.registerDate >= ? " +
      "  and re.register.registerDate <= ? " +
        viewSets.getSqlWhereAB("re.register.view") +
      ") " +
      "order by a.id",
      pars)).getRIs(this);
  }
 
  private void clearValues() {
    contract = null;
    contact = null;
    department = null;
    service = null;
    headquarters = null;
    company = null;
  }
 
  private String getInClause(String field) {
    contractListIds = new ArrayList<Long>();
    boolean done = false;
    if(company != null) {
      done = true;
      fillCompanyContracts(company);
    } else if(contact != null) {
      done = true;
      fillContactContracts(contact);
    } else if(contract != null) {
      done = true;
      contractListIds.add(contract.getId());
    } else if(department != null) {
      done = true;
      fillDepartmentContracts(department);
    } else if(headquarters != null) {
      done = true;
      fillHeadquartersContracts(headquarters);
    } else if(service != null) {
      done = true;
      fillServiceContracts(service);
    }
    StringBuilder sb = new StringBuilder("");
    if(contractListIds.size() > 0) {
      sb.append(" and substring(" + field + ",5,10)");
      sb.append(" in (");
      String sep = "";
      for(Long l : contractListIds) {
        sb.append(sep);
        sb.append(l);
        sep = ",";
      }
      sb.append(") ");
      sb.append(" and " + field + " not like '477%'");
      sb.append(" and " + field + " not like '472%'");
      sb.append(" and " + field + " not like '475%'");
    } else if(done) {
      sb.append(" and " + field + " = 'zzzzzzzzzzzz'");
    }
    return sb.toString();
  }
 
  private void fillContactContracts(Contact contact) {
    for(Contract ctc : getContactContracts(contact)) {
      contractListIds.add(ctc.getId());
    }
  }
 
  private void fillCompanyContracts(Company company) {
    for(Service serv : company.getServices()) {
      fillServiceContracts(serv);
    }
    for(Headquarters hq : company.getHeadquarters()) {
      fillHeadquartersContracts(hq);
    }
  }
 
  private void fillServiceContracts(Service service) {
    for(Contract ctc : service.getStores()) {
      contractListIds.add(ctc.getId());
    }
  }
 
  private void fillHeadquartersContracts(Headquarters headquarters) {
    for(Department dept : headquarters.getDepartments()) {
      fillDepartmentContracts(dept);
    }
  }
 
  private void fillDepartmentContracts(Department department) {
    for(Contract ctc : department.getStores()) {
      contractListIds.add(ctc.getId());
    }
  }
 
  private Iterable<Contract> getContactContracts(Contact contact) {
    return new FinancialsPU().getResultList(
            "select contract from Contract as contract " +
            "where contract.contractor = ?",
            new Object[] { contact.getId() }
            );
  }
}