DiaryReport.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.account;
 
import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.activation.MimetypesFileTypeMap;
import org.amic.util.date.CheckDate;
import org.turro.action.IMailSender;
import org.turro.action.MailSenders;
import org.turro.auth.Authentication;
import org.turro.command.Command;
import org.turro.command.Context;
import org.turro.contacts.Contact;
import org.turro.elephant.context.ElephantContext;
import org.turro.elephant.db.WhereClause;
import org.turro.elephant.util.DateFormats;
import org.turro.elephant.util.DecimalFormats;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.Company;
import org.turro.financials.entity.Register;
import org.turro.financials.entity.RegisterView;
import org.turro.financials.model.business.CompanyWrapper;
import org.turro.financials.model.register.ViewWrapper;
import org.turro.financials.view.ViewListbox;
import org.turro.i18n.I_;
import org.turro.jpa.Dao;
import org.turro.jpa.export.ExportAsStream;
import org.turro.jpa.export.ExportQuery;
import org.turro.message.MessageQueue;
import org.turro.plugin.contacts.IContact;
import org.turro.print.Print;
import org.turro.sql.SqlClause;
import org.turro.zkoss.dialog.InputDialog;
import org.turro.zkoss.dialog.InputField;
import org.turro.zkoss.print.ReportMenuitem;
import org.turro.zul.frame.Framework;
import org.zkoss.zhtml.Filedownload;
import org.zkoss.zk.ui.HtmlBasedComponent;
 
public class DiaryReport extends ReportMenuitem {
 
  private Integer year, startPage;
  private RegisterView view;
  private Dao dao;
 
  public DiaryReport() {
  }
 
  @Override
  protected void print(final String type) {
    if(view == null) {
      view = ViewWrapper.getFormalView();
    }
    year = new CheckDate().getYear();
    startPage = 1;
    
    InputDialog.getInput(
            Framework.getCurrent().getPage(),
            I_.get("Diary book"),
            new InputField[] {
              new InputField("Year", year, null, 0),
              new InputField("Start page", startPage, null, 0),
              new InputField("View", null, null, 0) {
                @Override
                protected HtmlBasedComponent createEditor() {
                  ViewListbox vl = new ViewListbox();
                  vl.setMold("select");
                  return vl;
                }
              }
            }, new Command() {
      @Override
      public Object execute(Context context) {
        InputField[] fields = (InputField[]) context.get("fields");
        if(fields.length > 0) {
          for(InputField f : fields) {
            if("Year".equals(f.getLabel())) {
              year = (Integer) f.getValue();
            } else if("Start page".equals(f.getLabel())) {
              startPage = (Integer) f.getValue() - 1;
            } else if("View".equals(f.getLabel())) {
              view = (RegisterView) f.getValue();
            }
          }
          if(year != null) {
            IContact contact = Authentication.getIContact();
            MailSenders.getHeavy()
                    .addContact(contact)
                    .onStart(s -> MessageQueue.pushMessage(contact, I_.get("Report will be sent by email")))
                    .onCancel(s -> MessageQueue.pushMessage(contact, I_.get("Task already running")))
                    .onBuild(s -> print2execute((IMailSender) s))
                    .send(I_.get("Diary book"), I_.get("Diary book"));
          }
        }
        return null;
      }
    });           
  }
 
  private void print2execute(IMailSender sender) {
    Collection<Long> lines = getRegisters();
    if(lines != null && lines.size() > 0) {
      Map parameters = new HashMap();
      int fractionDigits = CompanyWrapper.getCompanyCurrency().getDefaultFractionDigits();
      Company company = CompanyWrapper.getDefaultCompany();
      Contact companyContact = (Contact) company.getIContact().getContact();
      parameters.put("company", company);
      parameters.put("companyContact", companyContact);
      parameters.put("dateFormat", DateFormats.getDefaultFormat());
      parameters.put("currencyFormat", DecimalFormats.getStringFormat(fractionDigits));
      parameters.put("exportFormat", "pdf");
      parameters.put("year", year);
      parameters.put("startPage", startPage);
      parameters.put("SUBREPORT_DIR", "../_reports/financials/accounting/");
      String file = "/WEB-INF/_reports/financials/accounting/DiaryBook.jasper";
      try {
        dao = new FinancialsPU();
        File result = File.createTempFile("report", "_Diary.pdf");
        Print.getInstance()
                .setTemplate(ElephantContext.getRealPath(file))
                .setCollection(lines)
                .setParameters(parameters)
                .onRead(bean -> dao.find(Register.class, (Long) bean))
                .toFile(result);
        sender.addAttachment(result);
        sender.onFinish(x -> sender.removeAttachments());
      } catch (IOException ex) {
        Logger.getLogger(DiaryReport.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
      }
    }
  }
 
  public Collection<Long> getRegisters() {
    WhereClause wc = new WhereClause();
    wc.addClause("select reg.id");
    wc.addClause("from Register reg");
    wc.addClause("where reg.view = :view");
    wc.addNamedValue("view", view);
    wc.addClause("and year(reg.registerDate) = :year");
    wc.addNamedValue("year", year);
    wc.addClause("order by reg.idRegister");
    
    Dao dao = new FinancialsPU();
    return dao.getResultList(wc);
  }
 
  public WhereClause getSQLRegisters() {
    WhereClause wc = new WhereClause();
    wc.addClause("select reg");
    wc.addClause("from Register reg");
    wc.addClause("where reg.view = :view");
    wc.addNamedValue("view", view);
    wc.addClause("and year(reg.registerDate) = :year");
    wc.addNamedValue("year", year);
    wc.addClause("order by reg.idRegister");
    
    return wc;
  }
 
  public static void exportDiary() {
    IContact contact = Authentication.getIContact();
    MailSenders.getHeavy()
            .addContact(contact)
            .onStart(s -> MessageQueue.pushMessage(contact, I_.get("Report will be sent by email")))
            .onCancel(s -> MessageQueue.pushMessage(contact, I_.get("Task already running")))
            .onBuild(s -> diaryToExcel((IMailSender) s))
            .send(I_.get("Export diary"), I_.get("Export diary"));
  }
 
  private static void diaryToExcel(IMailSender sender) {
    WhereClause wc = new WhereClause();
    wc.addClause("select r.view.name, year(r.registerDate), month(r.registerDate),day(r.registerDate), ");
    wc.addClause("substring(re.account.id, 1, 1), (select ma1.description from MajorAccount ma1 where ma1.account = substring(re.account.id, 1, 1)),");
    wc.addClause("substring(re.account.id, 1, 2), (select ma2.description from MajorAccount ma2 where ma2.account = substring(re.account.id, 1, 2)),");
    wc.addClause("substring(re.account.id, 1, 3), (select ma3.description from MajorAccount ma3 where ma3.account = substring(re.account.id, 1, 3)),");
    wc.addClause("ma.account, ma.description, re.account.id,");
    wc.addClause("re.account.description, re.debit, re.credit");
    wc.addClause("from RegisterEntry re join re.register r, MajorAccount ma");
    wc.addClause("where ma.account = (select max(m.account) from MajorAccount as m");
    wc.addClause("where re.account.id like concat(m.account, '%'))");
    wc.addClause("order by r.view.name, r.registerDate");
 
    List<String> l = Arrays.asList(new String[] {
      I_.get("View"),
      I_.get("Year"),
      I_.get("Month"),
      I_.get("Day"),
      "M1", "M1Desc", "M2", "M2Desc", "M3", "M3Desc",
      I_.get("Major account"),
      I_.get("Name"),
      I_.get("Account"),
      I_.get("Description"),
      I_.get("Debit"),
      I_.get("Credit")
    });
    try {
      Dao dao = new FinancialsPU();
      ExportAsStream eq = new ExportAsStream(dao, l, wc);
      File result = File.createTempFile("report", "_Diary.xls");
      eq.generateExcel(result);
      sender.addAttachment(result);
      sender.onFinish(x -> sender.removeAttachments());
    } catch (IOException ex) {
      Logger.getLogger(DiaryReport.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  
  public static void exportAccountBalance(int year, int month, boolean carry, boolean breakdown) {
    WhereClause wc = new WhereClause();
    wc.addClause("select r.view.name,");
    if(breakdown) {
      wc.addClause("year(r.registerDate),");
      wc.addClause("month(r.registerDate),");
    }
    wc.addClause("substring(re.account.id, 1, 1), (select ma1.description from MajorAccount ma1 where ma1.account = substring(re.account.id, 1, 1)),");
    wc.addClause("substring(re.account.id, 1, 2), (select ma2.description from MajorAccount ma2 where ma2.account = substring(re.account.id, 1, 2)),");
    wc.addClause("substring(re.account.id, 1, 3), (select ma3.description from MajorAccount ma3 where ma3.account = substring(re.account.id, 1, 3)),");
    wc.addClause("ma.account, ma.description, re.account.id,");
    wc.addClause("re.account.description, sum(re.debit), sum(re.credit)");
    wc.addClause("from RegisterEntry re join re.register r, MajorAccount ma");
    wc.addClause("where ma.account = (select max(m.account) from MajorAccount as m");
    wc.addClause("where re.account.id like concat(m.account, '%'))");
    wc.addClause("and r.registerDate < :date");
    wc.addNamedValue("date", new CheckDate(year, month, 1, 0, 0, 0).addMonths(1).getDate());
    if(carry) {
      wc.addClause("and year(r.registerDate) >= :carry");
      wc.addNamedValue("carry", getLastInitialSince(year));
      wc.addClause("and not (r.exclude = TRUE and year(r.registerDate) = :year)");
      wc.addNamedValue("year", year);
    } else {
      wc.addClause("and year(r.registerDate) = :carry");
      wc.addNamedValue("carry", year);
      wc.addClause("and not (r.exclude = TRUE or r.closing = TRUE)");
    }
    if(breakdown) {
      wc.addClause("group by r.view.name, year(r.registerDate), month(r.registerDate), re.account.id, ma.account, ma.description");
    } else {
      wc.addClause("group by r.view.name, re.account.id, ma.account, ma.description");
    }
    wc.addClause("order by re.account.id");
 
    List<String> l;
    if(breakdown) {
      l = Arrays.asList(new String[] {
        I_.get("View"),
        I_.get("Year"),
        I_.get("Month"),
        "M1", "M1Desc", "M2", "M2Desc", "M3", "M3Desc",
        I_.get("Major account"),
        I_.get("Name"),
        I_.get("Account"),
        I_.get("Description"),
        I_.get("Debit"),
        I_.get("Credit")
      });
    } else {
      l = Arrays.asList(new String[] {
        I_.get("View"),
        "M1", "M1Desc", "M2", "M2Desc", "M3", "M3Desc",
        I_.get("Major account"),
        I_.get("Name"),
        I_.get("Account"),
        I_.get("Description"),
        I_.get("Debit"),
        I_.get("Credit")
      });
    }
    Dao dao = new FinancialsPU();
    String name = ElephantContext.getSiteName() + "_AccountBalance.xls";
    ExportQuery eq = new ExportQuery(l, dao.getResultList(wc));
    Filedownload.save(eq.getExcel(name), new MimetypesFileTypeMap().getContentType(name), name);
  }
 
  public static Integer getLastInitialSince(int year) {
    return SqlClause.select("year(max(registerDate))").from("Register")
            .where().equal("closing", true)
            .and().equal("exclude", false)
            .and().smallerOrEqual("year(registerDate)", year)
            .and().equal("view.id", 1L)
            .dao(new FinancialsPU())
            .singleResult(Integer.class);
  }
  
}