Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/accounting/report/BalanceSheet.java - Rev 182
Rev 180 | Blame | Compare with Previous | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.** The contents of this file are subject to the terms of the GNU General Public License Version 3* only ("GPL"). You may not use this file except in compliance with the License. You can obtain a* copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific* language governing permissions and limitations under the License.** When distributing the software, include this License Header Notice in each file.*/package org.openconcerto.erp.core.finance.accounting.report;import org.openconcerto.erp.config.ComptaPropsConfiguration;import org.openconcerto.erp.element.objet.Compte;import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;import org.openconcerto.erp.generationDoc.SheetInterface;import org.openconcerto.erp.preferences.PrinterNXProps;import org.openconcerto.sql.Configuration;import org.openconcerto.sql.model.SQLBase;import org.openconcerto.sql.model.SQLRow;import org.openconcerto.sql.model.SQLRowListRSH;import org.openconcerto.sql.model.SQLSelect;import org.openconcerto.sql.model.SQLTable;import org.openconcerto.sql.model.Where;import org.openconcerto.utils.GestionDevise;import java.text.DateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.handlers.ArrayListHandler;public class BalanceSheet extends SheetInterface {private static int debutFill, endFill;private final static SQLTable tableEcriture = base.getTable("ECRITURE");private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");private boolean centralClient, centralFourn, centralFournImmo;private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);public static String TEMPLATE_ID = "Balance";public static String TEMPLATE_PROPERTY_NAME = "LocationBalance";private Date dateAu, dateDu;private String compteDeb, compteEnd;public static void setSize(int debut, int fin) {debutFill = debut;endFill = fin;}static {setSize(7, 69);}@Overrideprotected String getYear() {return "";}@Overridepublic String getTemplateId() {return TEMPLATE_ID;}public BalanceSheet(Date du, Date au, String compteDeb, String compteEnd, boolean centralClient, boolean centralFourn, boolean centralFournImmo, boolean displayAll) {super();Calendar cal = Calendar.getInstance();cal.setTime(au);// Initialisation des Valeurthis.nbRowsPerPage = 72;this.printer = PrinterNXProps.getInstance().getStringProperty("BalancePrinter");this.modele = "Balance.ods";final DocumentLocalStorageManager storage = DocumentLocalStorageManager.getInstance();// this.locationOO = storage.getDocumentOutputDirectory(TEMPLATE_ID);// this.locationPDF = storage.getPDFOutputDirectory(TEMPLATE_ID);this.dateAu = au;this.dateDu = du;this.viewMode = displayAll;this.compteDeb = compteDeb;this.compteEnd = compteEnd;this.centralClient = centralClient;this.centralFourn = centralFourn;this.centralFournImmo = centralFournImmo;createMap();}private void makeEntete(int rowDeb) {SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();this.mCell.put("A" + rowDeb, rowSociete.getObject("NOM"));this.mCell.put("D" + rowDeb, "Edition du " + dateFormat.format(new Date()));// this.mCell.put("D" + (rowDeb + 2), "Grand livre");System.err.println("MAKE ENTETE");}private void makePiedPage(int row) {if (this.dateDu == null) {this.mCell.put("C" + row, "Balance au " + dateFormatEcr.format(this.dateAu));} else {this.mCell.put("C" + row, "Balance du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu));}this.mCell.put("B" + row, "Du compte " + this.compteDeb + " à " + this.compteEnd);}private void makeSousTotalClasse(int row, long debit, long credit, String classe) {this.mCell.put("A" + row, "Total classe " + classe);this.mCell.put("B" + row, "");this.mCell.put("C" + row, new Double(GestionDevise.currencyToString(debit, false)));this.mCell.put("D" + row, new Double(GestionDevise.currencyToString(credit, false)));this.mCell.put("E" + row, new Double(GestionDevise.currencyToString(debit - credit, false)));this.mapStyleRow.put(new Integer(row), "Titre 1");}private boolean viewMode = true;protected void createMap() {int posLine = 1;int firstLine = 1;this.nbPage = 0;long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit;totalDebit = 0;totalCredit = 0;sousTotalDebit = 0;sousTotalCredit = 0;long totalDebitClient = 0;long totalCreditClient = 0;long totalDebitFourn = 0;long totalCreditFourn = 0;long totalDebitFournImmo = 0;long totalCreditFournImmo = 0;String numCptClient = "411";String nomCptClient = "Clients";String numCptFourn = "401";String nomCptFourn = "Fournisseurs";String numCptFournImmo = "404";String nomCptFournImmo = "Fournisseurs d'immobilisations";boolean addedLine = false;boolean addedLineImmo = false;boolean addedLineFourn = false;int j = 0;String classe = "";if (viewMode) {getBalance();for (int i = 0; i < this.vecteurCompte.size();) {System.err.println("START NEW PAGE; POS : " + posLine);/**************************************************************************************** ENTETE**************************************************************************************/makeEntete(posLine);posLine += debutFill - 1;/**************************************************************************************** CONTENU**************************************************************************************/for (j = 0; (j < endFill - debutFill + 1) && i < this.vecteurCompte.size(); j++) {Compte compte = this.vecteurCompte.get(i);String numeroCpt = compte.getNumero();String nomCpt = compte.getNom();long deb = compte.getTotalDebit();long cred = compte.getTotalCredit();totalCredit += cred;sousTotalCredit += cred;totalDebit += deb;sousTotalDebit += deb;this.mCell.put("A" + posLine, numeroCpt);this.mCell.put("B" + posLine, nomCpt);this.mCell.put("C" + posLine, new Double(GestionDevise.currencyToString(deb, false)));this.mCell.put("D" + posLine, new Double(GestionDevise.currencyToString(cred, false)));this.mCell.put("E" + posLine, new Double(GestionDevise.currencyToString(deb - cred, false)));if (compte.getSousCompte().isEmpty()) {this.mapStyleRow.put(new Integer(posLine), "Normal");} else {this.mapStyleRow.put(new Integer(posLine), "Titre 1");}i++;posLine++;}if (i >= this.vecteurCompte.size() && j < endFill - debutFill + 1) {makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);}posLine = firstLine + endFill;/** if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {* this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }*/// Totalthis.mCell.put("C" + posLine, ((totalDebit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit, false))));this.mCell.put("D" + posLine, ((totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalCredit, false))));this.mCell.put("E" + posLine, (totalDebit - totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));posLine += 2;// bas de pagemakePiedPage(posLine);posLine++;firstLine = posLine;this.nbPage++;// if (i >= this.vecteurCompte.size() && j >= (endFill - debutFill + 1)) {//// makeEntete(posLine);// posLine += debutFill - 1;//// makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);//// this.nbPage++;// }}} else {this.mapReplace = new HashMap();this.mCell = new HashMap();this.mapStyleRow = new HashMap();SQLSelect sel = new SQLSelect();sel.addSelect(tableCompte.getField("ID"));sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");Where w = (new Where(tableEcriture.getField("DATE"), this.dateDu, this.dateAu));if (dateDu == null) {w = (new Where(tableEcriture.getField("DATE"), "<=", this.dateAu));}if (compteDeb.equals(this.compteEnd)) {w = w.and(new Where(tableCompte.getField("NUMERO"), "=", this.compteDeb));} else {w = w.and(new Where(tableCompte.getField("NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));}// FIXME use flag clotureWhere wCloture = new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%");wCloture = wCloture.and(new Where(tableEcriture.getField("DATE"), "=", this.dateAu));wCloture = wCloture.or(new Where(tableEcriture.getField("DATE"), "<", this.dateAu));w = w.and(wCloture);sel.setWhere(w);String req = sel.asString() + " AND \"ECRITURE\".\"ID_COMPTE_PCE\" = \"COMPTE_PCE\".\"ID\" GROUP BY \"COMPTE_PCE\".\"NUMERO\", \"COMPTE_PCE\".\"ID\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";System.err.println(req);List l = (List) base.getDataSource().execute(req, new ArrayListHandler());SQLSelect selCompte = new SQLSelect();selCompte.addSelectStar(tableCompte);List<SQLRow> compteRows = SQLRowListRSH.execute(selCompte);Map<Integer, SQLRow> mapCompte = new HashMap<Integer, SQLRow>();for (SQLRow sqlRow : compteRows) {mapCompte.put(sqlRow.getID(), sqlRow);}for (int i = 0; i < l.size();) {System.err.println("START NEW PAGE; POS : " + posLine);/**************************************************************************************** ENTETE**************************************************************************************/makeEntete(posLine);posLine += debutFill - 1;/**************************************************************************************** CONTENU**************************************************************************************/for (j = 0; (j < endFill - debutFill + 1) && i < l.size(); j++) {Object[] o = (Object[]) l.get(i);int idCpt = Integer.parseInt(o[0].toString());// SQLRow rowCpt = tableCompte.getRow(idCpt);SQLRow rowCpt = mapCompte.get(idCpt);String numeroCpt = rowCpt.getString("NUMERO").trim();String nomCpt = rowCpt.getString("NOM");// Changement de classe de compteif (classe.trim().length() != 0 && numeroCpt.length() > 0 && !classe.trim().equalsIgnoreCase(numeroCpt.substring(0, 1))) {makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);sousTotalCredit = 0;sousTotalDebit = 0;classe = numeroCpt.substring(0, 1);} else {if (classe.trim().length() == 0 && numeroCpt.trim().length() > 0) {classe = numeroCpt.substring(0, 1);}long deb = new Double(o[1].toString()).longValue();long cred = new Double(o[2].toString()).longValue();totalCredit += cred;sousTotalCredit += cred;totalDebit += deb;sousTotalDebit += deb;// Centralisation compte clientif (this.centralClient && (numeroCpt.equalsIgnoreCase("411") || numeroCpt.startsWith("411"))) {totalDebitClient += deb;totalCreditClient += cred;deb = totalDebitClient;cred = totalCreditClient;}// Centralisation compte fournisseur immoelse if (this.centralFournImmo && (numeroCpt.equalsIgnoreCase("404") || numeroCpt.startsWith("404"))) {totalDebitFournImmo += deb;totalCreditFournImmo += cred;deb = totalDebitFournImmo;cred = totalCreditFournImmo;}// Centralisation compte fournisseurelse if (this.centralFourn && (numeroCpt.equalsIgnoreCase("401") || numeroCpt.startsWith("401"))) {totalDebitFourn += deb;totalCreditFourn += cred;deb = totalDebitFourn;cred = totalCreditFourn;}if (this.centralClient && !numeroCpt.equalsIgnoreCase("411") && numeroCpt.startsWith("411")) {if (addedLine) {posLine--;j--;} else {addedLine = true;}this.mCell.put("A" + posLine, numCptClient);this.mCell.put("B" + posLine, nomCptClient);} else if (this.centralFourn && !numeroCpt.equalsIgnoreCase("401") && numeroCpt.startsWith("401")) {if (addedLineFourn) {posLine--;j--;} else {addedLineFourn = true;}this.mCell.put("A" + posLine, numCptFourn);this.mCell.put("B" + posLine, nomCptFourn);} else if (this.centralFournImmo && !numeroCpt.equalsIgnoreCase("404") && numeroCpt.startsWith("404")) {if (addedLineImmo) {posLine--;j--;} else {addedLineImmo = true;}this.mCell.put("A" + posLine, numCptFournImmo);this.mCell.put("B" + posLine, nomCptFournImmo);} else {this.mCell.put("A" + posLine, numeroCpt);this.mCell.put("B" + posLine, nomCpt);}this.mCell.put("C" + posLine, new Double(GestionDevise.currencyToString(deb, false)));this.mCell.put("D" + posLine, new Double(GestionDevise.currencyToString(cred, false)));this.mCell.put("E" + posLine, new Double(GestionDevise.currencyToString(deb - cred, false)));this.mapStyleRow.put(new Integer(posLine), "Normal");i++;}posLine++;}if (i >= l.size() && j < endFill - debutFill + 1) {makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);}posLine = firstLine + endFill;/** if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {* this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }*/// Totalthis.mCell.put("C" + posLine, ((totalDebit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit, false))));this.mCell.put("D" + posLine, ((totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalCredit, false))));this.mCell.put("E" + posLine, (totalDebit - totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));posLine += 2;// bas de pagemakePiedPage(posLine);posLine++;firstLine = posLine;this.nbPage++;if (i >= l.size() && j >= (endFill - debutFill + 1)) {makeEntete(posLine);posLine += debutFill - 1;makeSousTotalClasse(posLine, sousTotalDebit, sousTotalCredit, classe);this.nbPage++;}}}if (this.nbPage < 1) {this.nbPage = 1;}}private long totalDebitBalance = 0;private long totalCreditBalance = 0;private List<Compte> vecteurCompte = new ArrayList<Compte>();public void getBalance() {// Compte numero -- totalDebitMap<Number, Long> mapCompteDebit = new HashMap<Number, Long>();Map<Number, Long> mapCompteCredit = new HashMap<Number, Long>();List<Compte> comptes = new ArrayList<Compte>();this.totalDebitBalance = 0;this.totalCreditBalance = 0;SQLBase base = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete();SQLTable compteTable = base.getTable("COMPTE_PCE");SQLTable ecritureTable = base.getTable("ECRITURE");SQLSelect sel = new SQLSelect();// On recupere le solde des comptessel.addSelect(compteTable.getField("ID"));sel.addSelect(ecritureTable.getField("DEBIT"), "SUM");sel.addSelect(ecritureTable.getField("CREDIT"), "SUM");sel.addSelect(compteTable.getField("NUMERO"));sel.setDistinct(true);Where w = (new Where(tableEcriture.getField("DATE"), this.dateDu, this.dateAu));if (dateDu == null) {w = (new Where(tableEcriture.getField("DATE"), "<=", this.dateAu));}sel.setWhere(w.and(new Where(compteTable.getField("ID"), "=", ecritureTable.getField("ID_COMPTE_PCE"))));String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\",\"COMPTE_PCE\".\"NUMERO\" ORDER BY \"COMPTE_PCE\".\"NUMERO\"";System.out.println(req);Object ob = base.getDataSource().execute(req, new ArrayListHandler());List myList = (List) ob;if (myList.size() != 0) {for (int i = 0; i < myList.size(); i++) {Object[] tmp = (Object[]) myList.get(i);mapCompteDebit.put((Number) tmp[0], Long.parseLong(tmp[1].toString()));mapCompteCredit.put((Number) tmp[0], Long.parseLong(tmp[2].toString()));}}// Création du vecteur balancesel = new SQLSelect();sel.addSelect(compteTable.getKey());sel.addSelect(compteTable.getField("NUMERO"));sel.addSelect(compteTable.getField("NOM"));sel.addRawOrder("\"COMPTE_PCE\".\"NUMERO\"");String reqCompte = sel.asString();System.out.println(req);Object obCompte = base.getDataSource().execute(reqCompte, new ArrayListHandler());List myListCompte = (List) obCompte;if (myListCompte.size() != 0) {for (int i = 0; i < myListCompte.size(); i++) {Object[] tmp = (Object[]) myListCompte.get(i);System.err.println("Compte " + tmp[1].toString().trim());long totalDebit = 0;long totalCredit = 0;if (mapCompteDebit.get(tmp[0]) != null) {totalDebit = Long.parseLong(mapCompteDebit.get(tmp[0]).toString());}if (mapCompteCredit.get(tmp[0]) != null) {totalCredit = Long.parseLong(mapCompteCredit.get(tmp[0]).toString());}this.totalDebitBalance += totalDebit;this.totalCreditBalance += totalCredit;List<String> sousCompte = new ArrayList<>();for (int j = i + 1; j < (myListCompte.size() - 1); j++) {Object[] tmpNext = (Object[]) myListCompte.get(j);if (tmpNext[1].toString().trim().startsWith(tmp[1].toString().trim())) {System.err.println("Sous Compte " + tmpNext[1].toString().trim());sousCompte.add(tmpNext[1].toString().trim());if (mapCompteDebit.get(tmpNext[0]) != null) {totalDebit += Long.parseLong(mapCompteDebit.get(tmpNext[0]).toString());}if (mapCompteCredit.get(tmpNext[0]) != null) {totalCredit += Long.parseLong(mapCompteCredit.get(tmpNext[0]).toString());}} else {break;}}if ((totalDebit != 0.0) || (totalCredit != 0.0)) {Compte cpt = new Compte(((Number) tmp[0]).intValue(), tmp[1].toString(), tmp[2].toString(), "", totalDebit, totalCredit);cpt.addSousCompte(sousCompte);comptes.add(cpt);}}}this.vecteurCompte = comptes;}}