ExpensesBook.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2023 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.book;
 
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.TreeSet;
import javax.activation.MimetypesFileTypeMap;
import jxl.Workbook;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.turro.time.Dates;
import org.turro.elephant.context.ElephantContext;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.Contract;
import org.turro.financials.entity.Document;
import org.turro.financials.model.document.AmountTaxable;
import org.turro.financials.model.document.DocumentAmounts;
import org.turro.jpa.Dao;
import org.turro.log.WebLoggers;
import org.turro.plugin.contacts.IContact;
import org.turro.sql.SqlClause;
import org.turro.util.Cached;
import org.zkoss.zul.Filedownload;
 
public class ExpensesBook extends TreeSet<ExpensesEntry> {
  
  public void download() {
    String name = ElephantContext.getSiteName() + "_ExpensesBook.xls";
    Filedownload.save(getExcel(name), new MimetypesFileTypeMap().getContentType(name), name);  
  }
  
  public InputStream getExcel(String sheetName) {
    try {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      WritableWorkbook ww = Workbook.createWorkbook(baos);
      WritableSheet ws = ww.createSheet(sheetName, 0);
      writeSheet(ws);
      ww.write();
      ww.close();
      baos.close();
      return new ByteArrayInputStream(baos.toByteArray());
    } catch (WriteException | IOException ex) {
      WebLoggers.severe(this).exception(ex).log();
    }
    return null;
  }
 
  private List<String> columns = List.of(
          "Register", "DocNumber", "ReceiptDate", "DocDate", "GID", "Provider", 
          "Type",   "Tax%", "Taxable", "Tax", "Total");
  
  private List<Long> docDefs = List.of(
          2L, 10L, 12L, 15L, 51L);
          
  private void writeSheet(WritableSheet ws) throws WriteException {
    int countCols = 0, countRows = 0;
    for(String s : columns) {
      ws.addCell(new Label(countCols++, countRows, s));
    }
    for(ExpensesEntry entry : this) {
      countCols = 0;
      countRows++;
      ws.addCell(new jxl.write.Number(countCols++, countRows, countRows));
      ws.addCell(new Label(countCols++, countRows, entry.getDocNumber()));
      ws.addCell(new DateTime(countCols++, countRows, Dates.fromGTM(entry.getReceiptDate()).get()));
      ws.addCell(new DateTime(countCols++, countRows, Dates.fromGTM(entry.getDocDate()).get()));
      ws.addCell(new Label(countCols++, countRows, entry.getGlobalIdentifier()));
      ws.addCell(new Label(countCols++, countRows, entry.getProvider()));
      ws.addCell(new Label(countCols++, countRows, entry.getType()));
      ws.addCell(new jxl.write.Number(countCols++, countRows, entry.getTax()));
      ws.addCell(new jxl.write.Number(countCols++, countRows, entry.getTaxable()));
      ws.addCell(new jxl.write.Number(countCols++, countRows, entry.getTaxAmount()));
      ws.addCell(new jxl.write.Number(countCols++, countRows, entry.getTotal()));
    }
  }
 
  /* Utils */
  
  private void load() {
    SqlClause.select("d").from("Document d")
            .where().equal("year(d.receiptDate)", year)
            .and().in("d.documentDefinition.id", docDefs)
            .dao(dao.get())
            .resultList(Document.class)
            .forEach(d -> loadEntry(d));
  }
  
  private void loadEntry(Document d) {
    Contract c = d.getContract();
    IContact contractor = c.getIContractor();
    DocumentAmounts da = d.getAmounts();
    for(AmountTaxable at : da.getTaxables()) {
      ExpensesEntry e = new ExpensesEntry(d.getDocumentNumber(), contractor.getGlobalId(), contractor.getName(), 
              c.getOperatingModifier().toString(), d.getReceiptDate(), d.getDocumentDate(), 
              at.getTax(), at.getTaxable(), at.getTaxAmount(), at.getTotal());
      add(e);
    }    
  }
  
  /* Dao */
  
  private final Cached<Dao> dao = Cached.instance(() -> new FinancialsPU());
  
  /* Factory */
  
  public static ExpensesBook from(int year) {
    return new ExpensesBook(year);
  }
  
  private final int year;
  
  private ExpensesBook(int year) {
    this.year = year;
    load();
  }
 
}