PrintBook.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2012 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.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.activation.MimetypesFileTypeMap;
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import net.sf.jasperreports.engine.JRException;
import org.amic.util.date.CheckDate;
import org.turro.command.Command;
import org.turro.command.Context;
import org.turro.contacts.Contact;
import org.turro.elephant.context.Application;
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.book.BookListbox;
import org.turro.financials.db.FinancialsPU;
import org.turro.financials.entity.BookDefinition;
import org.turro.financials.entity.BookRegister;
import org.turro.financials.entity.Company;
import org.turro.financials.entity.Contract;
import org.turro.financials.entity.Document;
import org.turro.financials.entity.DocumentLine;
import org.turro.financials.entity.LineType;
import org.turro.financials.entity.OperatingModifier;
import org.turro.financials.entity.Register;
import org.turro.financials.entity.RegisterView;
import org.turro.financials.model.business.CompanyWrapper;
import org.turro.financials.model.document.AmountTaxable;
import org.turro.financials.model.document.DocumentAmountsByBook;
import org.turro.financials.model.register.ViewWrapper;
import org.turro.i18n.I_;
import org.turro.jpa.Dao;
import org.turro.jpa.export.ExportQuery;
import org.turro.plugin.contacts.IContact;
import org.turro.zkoss.dialog.InputDialog;
import org.turro.zkoss.dialog.InputField;
import org.turro.zkoss.print.OnDemandBeanCollection;
import org.turro.zkoss.print.ReportMenuitem;
import org.turro.zkoss.print.ReportPrint;
import org.turro.zul.frame.Framework;
import org.zkoss.zk.ui.HtmlBasedComponent;
import org.zkoss.zk.ui.Page;
import org.zkoss.zul.Filedownload;
 
public class PrintBook extends ReportMenuitem {
 
  private Integer year;
 
  public PrintBook() {
  }
 
  @Override
  protected void print(String type) {
    doPrint(getPage(), year, type);
  }
 
  public static void doPrint(Page page, Integer year, final String type) {
    final RegisterView formalView = ViewWrapper.getFormalView();
    Integer startPage = 1;
    year = new CheckDate().getYear();
    
    InputDialog.getInput(
            Framework.getCurrent().getPage(),
            I_.get("Books"),
            new InputField[] {
              new InputField("From", new CheckDate().setMonth(1).setDay(1).getDate(), null, 0),
              new InputField("To", new CheckDate().setMonth(12).setDay(31).getDate(), null, 0),
              new InputField("Start page", startPage, null, 0),
              new InputField("Book", null, null, 0) {
                @Override
                protected HtmlBasedComponent createEditor() {
                  BookListbox bl = new BookListbox();
                  bl.setMold("select");
                  return bl;
                }
              },
              new InputField("Export", Boolean.valueOf(false), null, 0)              
            }, new Command() {
      @Override
      public Object execute(Context context) {
        BookDefinition bookDefinition = null;
        Integer startPage = 1;
        Date from = null, to = null;
        Boolean export = false;
        InputField[] fields = (InputField[]) context.get("fields");
        if(fields.length > 0) {
          for(InputField f : fields) {
            if("From".equals(f.getLabel())) {
              from = (Date) f.getValue();
            } else if("To".equals(f.getLabel())) {
              to = (Date) f.getValue();
            } else if("Start page".equals(f.getLabel())) {
              startPage = (Integer) f.getValue() - 1;
            } else if("Book".equals(f.getLabel())) {
              bookDefinition = (BookDefinition) f.getValue();
            } else if("Export".equals(f.getLabel())) {
              export = (Boolean) f.getValue();
            }
          }
          if(from != null && to != null) {
            if(bookDefinition.getId() == 4) {
              Collection<Long> lines = getDocuments(formalView, from, to);
              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("labels", Application.getStringMap());
                parameters.put("i_", I_.api());
                parameters.put("i", I_.map());
                parameters.put("k", I_.byKeyMap());
                parameters.put("bookDef", bookDefinition);
                parameters.put("majors", getMajorBook(bookDefinition));
                parameters.put("title", I_.get("Book") + ": " + bookDefinition.getDescription());
                parameters.put("company", company);
                parameters.put("companyContact", companyContact);
                parameters.put("dateFormat", DateFormats.getDefaultFormat());
                parameters.put("currencyFormat", DecimalFormats.getStringFormat(fractionDigits));
                parameters.put("exportFormat", type);
                parameters.put("from", from);
                parameters.put("to", to);
                if(export) {
                  String name = ElephantContext.getSiteName() + "_Book" + bookDefinition.getDescription() + ".xls";
                  Filedownload.save(getExcelForDocuments(name, lines, parameters), new MimetypesFileTypeMap().getContentType(name), name);
                } else {
//                  parameters.put("startPage", startPage);
//                  parameters.put("SUBREPORT_DIR", "../_reports/financials/accounting/");
//                  String file = "/WEB-INF/_reports/financials/accounting/PrintBookDocuments.jasper";
//                  OnDemandBeanCollection collection = new OnDemandBeanCollection(lines) {
//                    @Override
//                    protected Object getRealBean(Object bean) {
//                      return new FinancialsPU().find(BookRegister.class, (Long) bean);
//                    }
//                  };
//                  try {
//                    ReportPrint.print(I_.get("Books"),
//                            collection, parameters, file, null,
//                            type, null);
//                  } catch (JRException ex) {
//                    Logger.getLogger(PrintBook.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
//                  }
                }
              }
              
              
            } else {
              Collection<Long> lines = getBookRegisters(formalView, from, to, bookDefinition);
              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("labels", Application.getStringMap());
                parameters.put("i_", I_.api());
                parameters.put("i", I_.map());
                parameters.put("k", I_.byKeyMap());
                parameters.put("bookDef", bookDefinition);
                parameters.put("majors", getMajorBook(bookDefinition));
                parameters.put("title", I_.get("Book") + ": " + bookDefinition.getDescription());
                parameters.put("company", company);
                parameters.put("companyContact", companyContact);
                parameters.put("dateFormat", DateFormats.getDefaultFormat());
                parameters.put("currencyFormat", DecimalFormats.getStringFormat(fractionDigits));
                parameters.put("exportFormat", type);
                parameters.put("from", from);
                parameters.put("to", to);
                if(export) {
                  String name = ElephantContext.getSiteName() + "_Book" + bookDefinition.getDescription() + ".xls";
                  Filedownload.save(getExcel(name, lines, parameters), new MimetypesFileTypeMap().getContentType(name), name);
                } else {
                  parameters.put("startPage", startPage);
                  parameters.put("SUBREPORT_DIR", "../_reports/financials/accounting/");
                  String file = "/WEB-INF/_reports/financials/accounting/PrintBookDocuments.jasper";
                  OnDemandBeanCollection collection = new OnDemandBeanCollection(lines) {
                    @Override
                    protected Object getRealBean(Object bean) {
                      return new FinancialsPU().find(BookRegister.class, (Long) bean);
                    }
                  };
                  try {
                    ReportPrint.print(I_.get("Books"),
                            collection, parameters, file, null,
                            type, null);
                  } catch (JRException ex) {
                    Logger.getLogger(PrintBook.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
                  }
                }
              }
            }
          }
        }
        return null;
      }
    });
  }
 
  private static Collection<Long> getBookRegisters(RegisterView formalView, Date from, Date to, BookDefinition bookDefinition) {
    WhereClause wc = new WhereClause();
    wc.addClause("select breg.id");
    wc.addClause("from BookRegister breg");
    wc.addClause("where breg.bookDefinition = :bookDef");
    wc.addNamedValue("bookDef", bookDefinition);
    wc.addClause("and breg.register.view = :view");
    wc.addNamedValue("view", formalView);
    wc.addClause("and breg.register.registerDate >= :from");
    wc.addNamedValue("from", from);
    wc.addClause("and breg.register.registerDate <= :to");
    wc.addNamedValue("to", to);
    wc.addClause("order by breg.bookOrder");
    
    Dao dao = new FinancialsPU();
    return dao.getResultList(wc);
  }
 
  private static Collection<String> getMajorBook(BookDefinition bookDefinition) {
    WhereClause wc = new WhereClause();
    wc.addClause("select ma.account");
    wc.addClause("from MajorAccount ma");
    wc.addClause("where ma.bookDefinition = :bookDef");
    wc.addNamedValue("bookDef", bookDefinition);
    wc.addClause("order by ma.account desc");
    
    Dao dao = new FinancialsPU();
    return dao.getResultList(wc);
  }
 
  private static Collection<Long> getDocuments(RegisterView formalView, Date from, Date to) {
    WhereClause wc = new WhereClause();
    wc.addClause("select doc.id");
    wc.addClause("from Document doc");
    wc.addClause("where doc.contract.operatingModifier = :opMod");
    wc.addNamedValue("opMod", OperatingModifier.OPMOD_INTRACOMMUNITY);
    wc.addClause("and doc.documentDefinition.id = 1");
    wc.addClause("and doc.forcedView is null");
    wc.addClause("and doc.receiptDate >= :from");
    wc.addNamedValue("from", from);
    wc.addClause("and doc.receiptDate <= :to");
    wc.addNamedValue("to", to);
    wc.addClause("order by doc.receiptDate");
    
    Dao dao = new FinancialsPU();
    return dao.getResultList(wc);
  }
 
  public static InputStream getExcel(String sheetName, Collection<Long> bookRegisters, Map parameters) {
    try {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      WritableWorkbook ww = Workbook.createWorkbook(baos);
      WritableSheet ws = ww.createSheet(sheetName, 0);
      writeSheet(ws, bookRegisters, parameters);
      ww.write();
      ww.close();
      baos.close();
      return new ByteArrayInputStream(baos.toByteArray());
    } catch (WriteException | IOException ex) {
      Logger.getLogger(ExportQuery.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
    }
    return null;
  }
 
  private static void writeSheet(WritableSheet ws, Collection<Long> bookRegisters, Map parameters) throws WriteException {
    int countCols = 0, countRows = 0;
    BookDefinition bookDefinition = (BookDefinition) parameters.get("bookDef");
    String[] columns = new String[] {
      "No.Registre","No.Factura","Data recepció","Data","NIF / CIF","Expedidor","Tipus","IVA%","Base imposable","IVA","IRPF","Quota","Import"
    };
    for(String s : columns) {
      ws.addCell(new Label(countCols++, countRows, s));
    }
    Dao dao = new FinancialsPU();
    DateFormat df = new DateFormat("dd/MM/yyyy");
    df.getDateFormat().setTimeZone(TimeZone.getTimeZone("GMT"));
    WritableCellFormat dateFmt = new WritableCellFormat(df);
    for(Long bookRegisterId : bookRegisters) {
      BookRegister bookRegister = dao.find(BookRegister.class, bookRegisterId);
      if(bookRegister != null) {
        Register register = bookRegister.getRegister();
        Document document = register.getDocument();
        if(document != null) {
          Contract contract = document.getContract();
          IContact contractor = contract.getIContractor();
          DocumentAmountsByBook da = document.getAmountsByBook(bookDefinition);
          for(AmountTaxable at : da.getTaxables()) {
            if(at.getTax() == 0) {
              for(DocumentLine dl : document.getDocumentLines()) {
                LineType lt = dl.getLineType();
                if(lt != null && lt.getId() == 183) {
                  countRows++;
                  ws.addCell(new jxl.write.Number(0, countRows, bookRegister.getBookOrder()));
                  ws.addCell(new jxl.write.Label(1, countRows, document.getDocumentNumber()));
                  ws.addCell(new jxl.write.DateTime(2, countRows, register.getRegisterDate(), dateFmt));
                  ws.addCell(new jxl.write.DateTime(3, countRows, document.getDocumentDate(), dateFmt));
                  ws.addCell(new jxl.write.Label(4, countRows, contractor.getGlobalId()));
                  ws.addCell(new jxl.write.Label(5, countRows, contractor.getName()));
                  ws.addCell(new jxl.write.Label(6, countRows, I_.byKey(OperatingModifier.OPMOD_EXTRACOMMUNITY.toString())));
                  ws.addCell(new jxl.write.Number(9, countRows, dl.getPrice()));
                }/* else if(dl.getPrice() != 0.0) {
                  countRows++;
                  ws.addCell(new jxl.write.Number(0, countRows, bookRegister.getBookOrder()));
                  ws.addCell(new jxl.write.Label(1, countRows, document.getDocumentNumber()));
                  ws.addCell(new jxl.write.DateTime(2, countRows, register.getRegisterDate(), dateFmt));
                  ws.addCell(new jxl.write.DateTime(3, countRows, document.getDocumentDate(), dateFmt));
                  ws.addCell(new jxl.write.Label(4, countRows, contractor.getGlobalId()));
                  ws.addCell(new jxl.write.Label(5, countRows, contractor.getName()));
                  ws.addCell(new jxl.write.Label(6, countRows, Application.getString(contract.getOperatingModifier().toString())));
                  ws.addCell(new jxl.write.Number(8, countRows, dl.getPrice()));
                  ws.addCell(new jxl.write.Number(11, countRows, dl.getPrice()));
                }*/
              }
            } else {
              countRows++;
              ws.addCell(new jxl.write.Number(0, countRows, bookRegister.getBookOrder()));
              ws.addCell(new jxl.write.Label(1, countRows, document.getDocumentNumber()));
              ws.addCell(new jxl.write.DateTime(2, countRows, register.getRegisterDate(), dateFmt));
              ws.addCell(new jxl.write.DateTime(3, countRows, document.getDocumentDate(), dateFmt));
              ws.addCell(new jxl.write.Label(4, countRows, contractor.getGlobalId()));
              ws.addCell(new jxl.write.Label(5, countRows, contractor.getName()));
              ws.addCell(new jxl.write.Label(6, countRows, I_.byKey(contract.getOperatingModifier().toString())));
              ws.addCell(new jxl.write.Number(7, countRows, at.getTax()));
              ws.addCell(new jxl.write.Number(8, countRows, at.getTaxable()));
              ws.addCell(new jxl.write.Number(9, countRows, at.getTaxAmount()));
              ws.addCell(new jxl.write.Number(10, countRows, da.getRetentions().getRetained()));
              ws.addCell(new jxl.write.Number(11, countRows, at.getTaxRealAmount()-at.getTaxAmount()));
              ws.addCell(new jxl.write.Number(12, countRows, at.getTotal()));
            }
          }
        }
      }
    }
  }
  
  public static InputStream getExcelForDocuments(String sheetName, Collection<Long> documentIds, Map parameters) {
    try {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      WritableWorkbook ww = Workbook.createWorkbook(baos);
      WritableSheet ws = ww.createSheet(sheetName, 0);
      writeSheetForDocuments(ws, documentIds, parameters);
      ww.write();
      ww.close();
      baos.close();
      return new ByteArrayInputStream(baos.toByteArray());
    } catch (WriteException | IOException ex) {
      Logger.getLogger(ExportQuery.class.getName()).log(Level.SEVERE, ElephantContext.logMsg(null), ex);
    }
    return null;
  }
 
  private static void writeSheetForDocuments(WritableSheet ws, Collection<Long> documentIds, Map parameters) throws WriteException {
    int countCols = 0, countRows = 0;
    BookDefinition bookDefinition = (BookDefinition) parameters.get("bookDef");
    String[] columns = new String[] {
      "No.Registre","No.Factura","Data recepció","Data","NIF / CIF","Expedidor","Tipus","IVA%","Base imposable","IVA","Quota","Import"
    };
    for(String s : columns) {
      ws.addCell(new Label(countCols++, countRows, s));
    }
    Dao dao = new FinancialsPU();
    DateFormat df = new DateFormat("dd/MM/yyyy");
    df.getDateFormat().setTimeZone(TimeZone.getTimeZone("GMT"));
    WritableCellFormat dateFmt = new WritableCellFormat(df);
    for(Long documentId : documentIds) {
      Document document = dao.find(Document.class, documentId);
      if(document != null) {
        Contract contract = document.getContract();
        IContact contractor = contract.getIContractor();
        DocumentAmountsByBook da = document.getAmountsByBook(bookDefinition);
        for(AmountTaxable at : da.getTaxables()) {
          countRows++;
          ws.addCell(new jxl.write.Label(1, countRows, document.getDocumentNumber()));
          ws.addCell(new jxl.write.DateTime(2, countRows, document.getReceiptDate(), dateFmt));
          ws.addCell(new jxl.write.DateTime(3, countRows, document.getDocumentDate(), dateFmt));
          ws.addCell(new jxl.write.Label(4, countRows, contractor.getGlobalId()));
          ws.addCell(new jxl.write.Label(5, countRows, contractor.getName()));
          ws.addCell(new jxl.write.Label(6, countRows, I_.byKey(contract.getOperatingModifier().toString())));
          ws.addCell(new jxl.write.Number(8, countRows, at.getTaxable()));
          ws.addCell(new jxl.write.Number(11, countRows, at.getTotal()));
        }
      }
    }
  }
  
}