List of usage examples for org.apache.commons.dbutils.handlers ArrayListHandler ArrayListHandler
public ArrayListHandler()
BasicRowProcessor for conversions. From source file:org.openconcerto.erp.core.finance.accounting.report.BalanceSheet.java
protected void createMap() { this.mapReplace = new HashMap(); this.mCell = new HashMap(); this.mapStyleRow = new HashMap(); SQLSelect sel = new SQLSelect(base); 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 (compteDeb.equals(this.compteEnd)) { w = w.and(new Where(tableCompte.getField("NUMERO"), "=", this.compteDeb)); } else {//from ww w. j a v a 2 s . co m w = w.and(new Where(tableCompte.getField("NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd)); } 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()); int posLine = 1; int firstLine = 1; System.err.println("START CREATE Grand livre, NB ecritures " + l.size()); 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; String numCptClient = "411"; String nomCptClient = "Clients"; String numCptFourn = "401"; String nomCptFourn = "Fournisseurs"; boolean addedLine = false; int j = 0; String classe = ""; 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); String numeroCpt = rowCpt.getString("NUMERO"); String nomCpt = rowCpt.getString("NOM"); // Changement de classe de compte if (classe.trim().length() != 0 && numeroCpt.trim().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 client if (this.centralClient && (numeroCpt.equalsIgnoreCase("411") || numeroCpt.startsWith("411"))) { totalDebitClient += deb; totalCreditClient += cred; deb = totalDebitClient; cred = totalCreditClient; } // Centralisation compte fournisseur 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 || !this.centralFourn) { 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")) { posLine--; j--; this.mCell.put("A" + posLine, numCptFourn); this.mCell.put("B" + posLine, nomCptFourn); } 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"); } */ // Total this.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 page makePiedPage(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++; } } // on conserve la page d'origine du model if (this.nbPage > 0) { this.nbPage--; } }
From source file:org.openconcerto.erp.core.finance.accounting.report.GrandLivreSheet.java
private List<Integer> getListeCompteSolde() { SQLSelect sel = new SQLSelect(base); sel.addSelect(tableCompte.getField("ID")); sel.addSelect(tableEcriture.getField("DEBIT"), "SUM"); sel.addSelect(tableEcriture.getField("CREDIT"), "SUM"); Where w;//from ww w .ja v a 2 s .co m if (this.compteDeb.equals(this.compteEnd)) { w = new Where(tableCompte.getField("NUMERO"), "=", this.compteDeb); } else { w = new Where(tableCompte.getField("NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd); } w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), "=", tableCompte.getField("ID"))); if (this.cumul) { w = w.and(new Where(tableEcriture.getField("DATE"), "<=", this.dateAu)); } else { w = w.and(new Where(tableEcriture.getField("DATE"), this.dateDu, this.dateAu)); } w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude)); if (this.lettrage == MODELETTREE) { Object o = null; w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o)); w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", "")); } else { if (this.lettrage == MODENONLETTREE) { Object o = null; Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o); w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", ""))); } } sel.setWhere(w); String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\""; System.err.println(req); List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler()); List<Integer> list = new ArrayList<Integer>(); for (Object[] o : l) { long credit = 0; if (o[2] != null) { credit = Long.valueOf(o[2].toString()); } long debit = 0; if (o[1] != null) { debit = Long.valueOf(o[1].toString()); } int id = Integer.valueOf(o[0].toString()); long solde = debit - credit; if (solde == 0) { list.add(id); } } return list; }
From source file:org.openconcerto.erp.core.finance.accounting.report.GrandLivreSheet.java
/** * @param d date limite des cumuls/*from w w w . j a va2s. c o m*/ * @return Map<Integer id compte, Long solde(debit-credit)> */ private Map<Integer, Long> getCumulsAnterieur(Date d, List<Integer> listCompteSolde) { SQLSelect sel = new SQLSelect(base); sel.addSelect(tableEcriture.getField("ID_COMPTE_PCE")); sel.addSelect(tableEcriture.getField("DEBIT"), "SUM"); sel.addSelect(tableEcriture.getField("CREDIT"), "SUM"); sel.addSelect(tableEcriture.getField("COMPTE_NUMERO")); // sel.addSelect(tableEcriture.getField("ID_MOUVEMENT")); Where w = (new Where(tableEcriture.getField("DATE"), "<", d)); w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getKey())); if (this.compteDeb.equals(this.compteEnd)) { w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb)); } else { w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd)); } if (this.lettrage == MODELETTREE) { Object o = null; w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o)); w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", "")); } else { if (this.lettrage == MODENONLETTREE) { Object o = null; Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o); w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", ""))); } } w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), "=", tableCompte.getField("ID"))); w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude)); if (listCompteSolde != null) { w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), listCompteSolde).not()); } sel.setWhere(w); String req = sel.asString() + " GROUP BY \"ECRITURE\".\"ID_COMPTE_PCE\", \"ECRITURE\".\"COMPTE_NUMERO\""; System.err.println(req); List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler()); Map<Integer, Long> map = new HashMap<Integer, Long>(); int idCptFourn = ComptePCESQLElement.getId("401", "Fournisseurs"); int idCptClient = ComptePCESQLElement.getId("411", "Clients"); for (Object[] o : l) { long credit = 0; if (o[2] != null) { credit = Long.valueOf(o[2].toString()); } long debit = 0; if (o[1] != null) { debit = Long.valueOf(o[1].toString()); } int id = Integer.valueOf(o[0].toString()); long solde = debit - credit; map.put(id, solde); if (o[3] != null) { String numero = o[3].toString(); if (this.centralFourn && numero.startsWith("401")) { Long lS = map.get(idCptFourn); if (lS != null) { lS += solde; } else { lS = new Long(solde); } map.put(idCptFourn, lS); } if (this.centralClient && numero.startsWith("411")) { Long lS = map.get(idCptClient); if (lS != null) { lS += solde; } else { lS = new Long(solde); } map.put(idCptClient, lS); } } } return map; }
From source file:org.openconcerto.erp.core.finance.accounting.report.JournauxMoisSheet.java
@Override protected void createMap() { this.mapReplace = new HashMap(); this.mCell = new HashMap(); this.mapStyleRow = new HashMap(); String schema = ((ComptaPropsConfiguration) Configuration.getInstance()).getSocieteBaseName(); String select = "SELECT SUM(\"DEBIT\"), SUM(\"CREDIT\"), EXTRACT(MONTH FROM \"DATE\"), EXTRACT(YEAR FROM \"DATE\"),\"JOURNAL\".\"ID\" FROM \"" + schema + "\".\"ECRITURE\" , \"" + schema + "\".\"JOURNAL\" "; String groupBy = " GROUP BY EXTRACT(YEAR FROM \"DATE\"), EXTRACT(MONTH FROM \"DATE\"),\"JOURNAL\".\"ID\""; String orderBy = " ORDER BY \"JOURNAL\".\"ID\",EXTRACT(YEAR FROM \"DATE\"), EXTRACT(MONTH FROM \"DATE\")"; if (this.idS != null && this.idS.length > 0) { select += " WHERE"; for (int i = 0; i < this.idS.length; i++) { if (i == 0) { select += "( \"" + schema + "\".\"JOURNAL\".\"ID\" = " + this.idS[i]; } else { select += " OR \"" + schema + "\".\"JOURNAL\".\"ID\" = " + this.idS[i]; }/*from ww w . j av a 2 s . c o m*/ } select += ")"; } if (this.lettrage == MODELETTREE) { if (this.idS != null && this.idS.length > 0) { select += " AND \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" <> NULL"; select += " AND \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" != ''"; } else { select += " WHERE \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" <> NULL"; select += " AND \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" != ''"; } } else { if (this.lettrage == MODENONLETTREE) { if (this.idS != null && this.idS.length > 0) { select += " AND (\"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" = NULL"; select += " OR \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" = '')"; } else { select += " WHERE \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" = NULL"; select += " OR \"" + schema + "\".\"ECRITURE\".\"LETTRAGE\" = ''"; } } } // ARCHIVE, ID 1, select += " AND \"" + schema + "\".\"JOURNAL\".\"ID\"!=1"; select += " AND \"" + schema + "\".\"ECRITURE\".\"ID\"!=1"; select += " AND \"" + schema + "\".\"JOURNAL\".\"ARCHIVE\"!=1"; select += " AND \"" + schema + "\".\"ECRITURE\".\"ARCHIVE\"!=1"; if (!UserManager.getInstance().getCurrentUser().getRights() .haveRight(ComptaUserRight.ACCES_NOT_RESCTRICTED_TO_411)) { // TODO Show Restricted acces in UI select += " AND \"" + schema + "\".\"ECRITURE\".\"COMPTE_NUMERO\" LIKE '411%'"; } // DATE select += " AND \"" + schema + "\".\"ECRITURE\".\"DATE\" BETWEEN '" + dateFormatPG.format(this.dateDu) + "' AND '" + dateFormatPG.format(this.dateAu) + "'"; select += " AND \"" + schema + "\".\"JOURNAL\".\"ID\"=\"" + schema + "\".\"ECRITURE\".\"ID_JOURNAL\"" + groupBy + orderBy; System.err.println(select); List l = (List) base.getDataSource().execute(select, new ArrayListHandler()); int posLine = 1; int firstLine = 1; System.err.println("START CREATE JOURNAUX, NB ecritures " + l.size()); this.nbPage = 0; long totalDebit, totalCredit; totalDebit = 0; totalCredit = 0; SQLRow rowFirstJournal = null; for (int i = 0; i < l.size();) { Object[] tmp = (Object[]) l.get(i); int idJrnl = Integer.valueOf(tmp[4].toString()); int year = Double.valueOf(tmp[3].toString()).intValue(); int month = Double.valueOf(tmp[2].toString()).intValue(); long credit = Integer.valueOf(tmp[1].toString()); long debit = Integer.valueOf(tmp[0].toString()); if (rowFirstJournal == null || rowFirstJournal.getID() != idJrnl) { totalDebit = 0; totalCredit = 0; } rowFirstJournal = tableJournal.getRow(idJrnl); System.err.println( "START NEW PAGE --> Journal : " + rowFirstJournal.getString("NOM") + "; POS : " + posLine); /*************************************************************************************** * ENTETE **************************************************************************************/ makeEntete(posLine, rowFirstJournal.getString("NOM")); posLine += debutFill - 1; /*************************************************************************************** * CONTENU **************************************************************************************/ Calendar cal = Calendar.getInstance(); // && (posLine % endFill !=0) for (int j = 0; (j < endFill - debutFill + 1) && i < l.size(); j++) { tmp = (Object[]) l.get(i); idJrnl = Integer.valueOf(tmp[4].toString()); year = Double.valueOf(tmp[3].toString()).intValue(); month = Double.valueOf(tmp[2].toString()).intValue(); credit = Integer.valueOf(tmp[1].toString()); debit = Integer.valueOf(tmp[0].toString()); SQLRow rowJournal = tableJournal.getRow(idJrnl); if (rowJournal.getID() == rowFirstJournal.getID()) { this.mapStyleRow.put(new Integer(posLine), "Titre 1"); cal.set(Calendar.MONTH, month - 1); cal.set(Calendar.YEAR, year); this.mCell.put("A" + posLine, dateFormatMonth.format(cal.getTime())); this.mCell.put("B" + posLine, dateFormatYear.format(cal.getTime())); this.mCell.put("C" + posLine, ""); this.mCell.put("D" + posLine, ""); long solde = debit - credit; totalCredit += credit; totalDebit += debit; this.mCell.put("E" + posLine, (debit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(debit, false))); this.mCell.put("F" + posLine, (credit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(credit, false))); this.mCell.put("G" + posLine, (solde == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(solde, false))); } else { break; } i++; posLine++; } posLine = firstLine + endFill; /* * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) { * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); } */ // Bas de page this.mCell.put("E" + posLine, (totalDebit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit, false))); this.mCell.put("F" + posLine, (totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalCredit, false))); this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? new Double(0) : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false))); posLine += 2; makeBasPage(posLine, rowFirstJournal.getString("NOM")); posLine++; firstLine = posLine; this.nbPage++; } // on conserve la page d'origine du model if (this.nbPage > 0) { this.nbPage--; } }
From source file:org.openconcerto.erp.core.finance.accounting.ui.AjouterComptePCGtoPCEFrame.java
private void ajoutCompteSelected() { SQLTable compteTable = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete() .getTable("COMPTE_PCE"); JTable tabTmp = (JTable) (this.planPanel.getTables().get(this.planPanel.getSelectedIndex())); PlanComptableGModel modelTmp = (PlanComptableGModel) tabTmp.getModel(); int[] selectRows = tabTmp.getSelectedRows(); if (selectRows.length == 0) { return;// w w w . j av a2 s . c o m } // On verifie que les compte n'existent pas deja SQLSelect selCompte = new SQLSelect(compteTable.getBase()); selCompte.addSelect(compteTable.getField("NUMERO")); Where w = new Where(compteTable.getField("NUMERO"), "=", tabTmp.getValueAt(selectRows[0], 0)); for (int i = 1; i < selectRows.length; i++) { w.or(new Where(compteTable.getField("NUMERO"), "=", tabTmp.getValueAt(selectRows[i], 0))); } String reqCompte = selCompte.asString(); Object obRep = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete().getDataSource() .execute(reqCompte, new ArrayListHandler()); List tmpCpt = (List) obRep; // Map qui contient les comptes existants Map<String, Integer> mCompte; if (tmpCpt.size() == 0) { mCompte = null; } else { mCompte = new HashMap<String, Integer>(); for (int i = 0; i < tmpCpt.size(); i++) { Object[] tmp = (Object[]) tmpCpt.get(i); mCompte.put(tmp[0].toString().trim(), new Integer(0)); } } // on ajoute les comptes si ils n'existent pas for (int i = 0; i < selectRows.length; i++) { if ((mCompte != null) && (mCompte.get(tabTmp.getValueAt(selectRows[i], 0).toString().trim()) == null)) { System.out.println("Ajout du compte" + tabTmp.getValueAt(selectRows[i], 0) + " " + tabTmp.getValueAt(selectRows[i], 1)); SQLRowValues val = new SQLRowValues(compteTable); val.put("NUMERO", tabTmp.getValueAt(selectRows[i], 0)); val.put("NOM", tabTmp.getValueAt(selectRows[i], 1)); val.put("INFOS", ((Compte) modelTmp.getComptes().get(selectRows[i])).getInfos()); try { val.insert(); } catch (SQLException sqlE) { System.err.println("Error insert row in table COMPTE_PCE"); sqlE.printStackTrace(); } } } }
From source file:org.openconcerto.erp.core.finance.accounting.ui.BilanPanel.java
public void getBilan() { long soldeClient, soldeFourn, soldeBanq; soldeClient = 0;//from w ww . j av a 2s . c o m soldeBanq = 0; soldeFourn = 0; /******************************************************************************************* * CALCUL DU BILAN CREANCES(CLIENTS BANQUE) - DETTES (FOURNISSEURS BENEF) ******************************************************************************************/ // Rcupration des ecritures du journal avec le total SQLBase base = Configuration.getInstance().getBase(); SQLTable compteTable = base.getTable("COMPTE_PCE"); SQLTable ecritureTable = base.getTable("ECRITURE"); SQLSelect selClient = new SQLSelect(base); SQLSelect selFourn = new SQLSelect(base); SQLSelect selBanq = new SQLSelect(base); // sel.addSelect(ecritureTable.getField("DATE"), "YEAR"); // sel.addSelect(ecritureTable.getField("DATE"), "MONTH"); selClient.addSelect(ecritureTable.getField("DEBIT"), "SUM"); selClient.addSelect(ecritureTable.getField("CREDIT"), "SUM"); selFourn.addSelect(ecritureTable.getField("DEBIT"), "SUM"); selFourn.addSelect(ecritureTable.getField("CREDIT"), "SUM"); selBanq.addSelect(ecritureTable.getField("DEBIT"), "SUM"); selBanq.addSelect(ecritureTable.getField("CREDIT"), "SUM"); Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getField("ID")); Where wClient = new Where(compteTable.getField("NUMERO"), "LIKE", "41%"); Where wFourn = new Where(compteTable.getField("NUMERO"), "LIKE", "40%"); Where wBanq = new Where(compteTable.getField("NUMERO"), "LIKE", "5%"); selBanq.setWhere(w.and(wBanq)); selFourn.setWhere(w.and(wFourn)); selClient.setWhere(w.and(wClient)); // sel.setDistinct(true); // sel.addRawOrder("NUMERO"); String reqClient = selClient.asString(); String reqFourn = selFourn.asString(); String reqBanq = selBanq.asString(); System.out.println(reqClient); System.out.println(reqFourn); System.out.println(reqBanq); Object obClient = base.getDataSource().execute(reqClient, new ArrayListHandler()); Object obFourn = base.getDataSource().execute(reqFourn, new ArrayListHandler()); Object obBanq = base.getDataSource().execute(reqBanq, new ArrayListHandler()); List myListClient = (List) obClient; if (myListClient.size() != 0) { for (int i = 0; i < myListClient.size(); i++) { Object[] objTmp = (Object[]) myListClient.get(i); soldeClient = ((Long) objTmp[0]).longValue() - ((Long) objTmp[1]).longValue(); } } List myListBanq = (List) obBanq; if (myListBanq.size() != 0) { for (int i = 0; i < myListBanq.size(); i++) { Object[] objTmp = (Object[]) myListBanq.get(i); soldeBanq = ((Long) objTmp[0]).longValue() - ((Long) objTmp[1]).longValue(); } } List myListFourn = (List) obFourn; if (myListFourn.size() != 0) { for (int i = 0; i < myListFourn.size(); i++) { Object[] objTmp = (Object[]) myListFourn.get(i); soldeFourn = -((Long) objTmp[0]).longValue() + ((Long) objTmp[1]).longValue(); } } this.add(new JLabel("Clients : " + soldeClient + " Banque : " + soldeBanq + " solde Fourn : " + soldeFourn + " Rsultat " + ResultatPanel.getResultatValue() + " Bilan = " + (soldeClient + soldeBanq) + " <<>> " + (soldeFourn - ResultatPanel.getResultatValue()))); }
From source file:org.openconcerto.erp.core.finance.accounting.ui.ClotureMensuellePayePanel.java
public ClotureMensuellePayePanel() { super();/*from w w w .j av a 2 s .co m*/ this.setLayout(new GridBagLayout()); GridBagConstraints c = new GridBagConstraints(); c.anchor = GridBagConstraints.WEST; c.fill = GridBagConstraints.HORIZONTAL; c.insets = new Insets(2, 2, 1, 2); c.weightx = 0; c.weighty = 0; c.gridheight = 1; c.gridwidth = 1; c.gridx = 0; c.gridy = 0; JLabel labelMois = new JLabel("Cloture du mois de "); this.add(labelMois, c); final ElementComboBox selMois = new ElementComboBox(true, 25); selMois.init(((ComptaPropsConfiguration) Configuration.getInstance()).getDirectory() .getElement(MoisSQLElement.class)); selMois.setButtonsVisible(false); c.gridx++; this.add(selMois, c); JLabel labelAnnee = new JLabel("Anne"); c.gridx++; this.add(labelAnnee, c); final JTextField textAnnee = new JTextField(5); c.gridx++; this.add(textAnnee, c); DateFormat format = new SimpleDateFormat("yyyy"); textAnnee.setText(format.format(new Date())); c.gridy++; c.gridx = 0; final JCheckBox boxValid = new JCheckBox("Valider toutes les payes du mois"); final JCheckBox boxCompta = new JCheckBox("Gnrer les critures comptables associes"); c.gridwidth = GridBagConstraints.REMAINDER; this.add(boxValid, c); boxValid.setSelected(true); c.gridy++; this.add(boxCompta, c); JButton buttonClot = new JButton("Clturer"); JButton buttonFermer = new JButton("Fermer"); JPanel panelButton = new JPanel(); panelButton.add(buttonClot); panelButton.add(buttonFermer); c.anchor = GridBagConstraints.SOUTHEAST; c.fill = GridBagConstraints.NONE; c.weighty = 1; c.gridy++; this.add(panelButton, c); buttonFermer.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { ((JFrame) SwingUtilities.getRoot(ClotureMensuellePayePanel.this)).dispose(); } }); buttonClot.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { try { // Valider les fiches non valids des salaris actifs if (boxValid.isSelected()) { SQLSelect selFiche = new SQLSelect(); SQLTable tableFiche = ClotureMensuellePayePanel.this.base.getTable("FICHE_PAYE"); SQLTable tableSalarie = ClotureMensuellePayePanel.this.base.getTable("SALARIE"); SQLTable tableInfosSal = ClotureMensuellePayePanel.this.base.getTable("INFOS_SALARIE_PAYE"); selFiche.addSelect(tableFiche.getField("ID")); selFiche.setWhere(new Where(tableFiche.getField("VALIDE"), "=", Boolean.FALSE)); selFiche.andWhere(new Where(tableFiche.getField("ID_MOIS"), "=", selMois.getSelectedId())); selFiche.andWhere( new Where(tableFiche.getField("ANNEE"), "=", new Integer(textAnnee.getText()))); selFiche.andWhere( new Where(tableSalarie.getField("ID"), "=", tableFiche.getField("ID_SALARIE"))); selFiche.andWhere(new Where(tableInfosSal.getField("ID"), "=", tableSalarie.getField("ID_INFOS_SALARIE_PAYE"))); // FIXME ne pas valider les fiches d'un employ renvoy // Where w2 = new Where(tableInfosSal.getField("DATE_SORTIE"), "IS", // "NULL"); Calendar cal = Calendar.getInstance(); cal.set(Calendar.DATE, 1); cal.set(Calendar.MONTH, selMois.getSelectedId() - 2); cal.set(Calendar.YEAR, Integer.parseInt(textAnnee.getText())); cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE)); Where w = new Where(tableInfosSal.getField("DATE_SORTIE"), "<=", cal.getTime()); w = w.or(new Where(tableInfosSal.getField("DATE_SORTIE"), "=", (Object) null)); selFiche.andWhere(w); String req = selFiche.asString(); System.err.println(req); List l = (List) ClotureMensuellePayePanel.this.base.getDataSource().execute(req, new ArrayListHandler()); for (int i = 0; i < l.size(); i++) { Object[] tmp = (Object[]) l.get(i); SQLRow rowFicheTmp = tableFiche.getRow(Integer.parseInt(tmp[0].toString())); System.err.println(rowFicheTmp); FichePayeSQLElement.validationFiche(rowFicheTmp.getID()); } } // cloture du mois et generation compta SQLSelect selFiche = new SQLSelect(ClotureMensuellePayePanel.this.base); SQLTable tableFiche = ClotureMensuellePayePanel.this.base.getTable("FICHE_PAYE"); SQLTable tableMois = ClotureMensuellePayePanel.this.base.getTable("MOIS"); selFiche.addSelect(tableFiche.getField("ID")); selFiche.setWhere(new Where(tableFiche.getField("VALIDE"), "=", Boolean.TRUE)); selFiche.andWhere(new Where(tableFiche.getField("ID_MOIS"), "=", selMois.getSelectedId())); selFiche.andWhere( new Where(tableFiche.getField("ANNEE"), "=", new Integer(textAnnee.getText()))); String req = selFiche.asString(); List l = (List) ClotureMensuellePayePanel.this.base.getDataSource().execute(req, new ArrayListHandler()); if (l != null && l.size() > 0) { int[] idS = new int[l.size()]; SQLRow rowMois = tableMois.getRow(selMois.getSelectedId()); for (int i = 0; i < l.size(); i++) { Object[] tmp = (Object[]) l.get(i); idS[i] = Integer.parseInt(tmp[0].toString()); SQLRow rowFiche = tableFiche.getRow(idS[i]); FichePayeSQLElement.clotureMensuelle(selMois.getSelectedId(), Integer.parseInt(textAnnee.getText()), rowFiche.getInt("ID_SALARIE")); } if (boxCompta.isSelected()) { new GenerationMvtFichePaye(idS, rowMois.getString("NOM"), textAnnee.getText()); } } System.err.println( "ClotureMensuellePayePanel.ClotureMensuellePayePanel().new ActionListener() {...}.actionPerformed()"); JOptionPane.showMessageDialog(null, "Clture termine"); } catch (Exception ex) { ExceptionHandler.handle("Unable to complete operation", ex); } } }); }
From source file:org.openconcerto.erp.core.finance.accounting.ui.CloturePanel.java
private void clotureExercice() throws SQLException { SQLRow rowPrefCompte = this.tablePrefCompte.getRow(2); int id_Compte_Bilan_Ouverture = rowPrefCompte.getInt("ID_COMPTE_PCE_BILAN_O"); if (id_Compte_Bilan_Ouverture <= 1) { id_Compte_Bilan_Ouverture = ComptePCESQLElement.getId("890"); }// w w w . j ava2 s .c o m int id_Compte_Bilan_Cloture = rowPrefCompte.getInt("ID_COMPTE_PCE_BILAN_F"); if (id_Compte_Bilan_Cloture <= 1) { id_Compte_Bilan_Cloture = ComptePCESQLElement.getId("891"); } /******************************************************************************************* * Validation des critures ******************************************************************************************/ EcritureSQLElement.validationEcrituresBefore((Date) this.rowExercice.getObject("DATE_FIN"), true); /******************************************************************************************* * Solde des comptes de gestion 6* et 7* (gnration du rsultat) ******************************************************************************************/ this.opEnCours.setText("En cours: solde des comptes 6 et 7"); long time = new Date().getTime(); System.err.println("Start :: " + time); soldeCompte(false); /******************************************************************************************* * Solde des autres comptes (comptes de bilan) ******************************************************************************************/ this.opEnCours.setText("En cours: solde des comptes autres que 6 et 7"); this.bar.setValue(1); soldeCompte(true); long time2 = new Date().getTime(); System.err.println("Stop :: " + time2); System.err.println("Time :: " + (time2 - time)); /******************************************************************************************* * Validation des critures de clotures ******************************************************************************************/ this.opEnCours.setText("En cours: validation des critures de l'exercice"); this.bar.setValue(2); EcritureSQLElement.validationEcrituresBefore((Date) this.rowExercice.getObject("DATE_FIN"), true); /******************************************************************************************* * Reouverture des comptes de bilan ******************************************************************************************/ this.opEnCours.setText("En cours: report des nouveaux"); this.bar.setValue(3); // transfert du compte bilan fermeture vers le compte bilan ouverture SQLTable ecritureTable = this.base.getTable("ECRITURE"); SQLTable compteTable = this.base.getTable("COMPTE_PCE"); SQLSelect sel = new SQLSelect(this.base); sel.addSelect(compteTable.getKey()); sel.addSelect(compteTable.getField("NUMERO")); sel.addSelect(compteTable.getField("NOM")); sel.addSelect(ecritureTable.getField("DEBIT"), "SUM"); sel.addSelect(ecritureTable.getField("CREDIT"), "SUM"); Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", id_Compte_Bilan_Cloture); w = w.and(new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getKey())); sel.setWhere(w); String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\", \"COMPTE_PCE\".\"NUMERO\", \"COMPTE_PCE\".\"NOM\" ORDER BY \"COMPTE_PCE\".\"NUMERO\""; System.out.println(req); Object ob = this.base.getDataSource().execute(req, new ArrayListHandler()); List myList = (List) ob; if (myList.size() != 0) { GenerationMvtVirement gen = new GenerationMvtVirement(1, id_Compte_Bilan_Cloture, 0, 0, "Fermeture du compte ", this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, "Fermeture des comptes"); for (int i = 0; i < myList.size(); i++) { Object[] objTmp = (Object[]) myList.get(i); Compte cptTmp = new Compte(((Number) objTmp[0]).intValue(), objTmp[1].toString(), objTmp[2].toString(), "", ((Number) objTmp[3]).longValue(), ((Number) objTmp[4]).longValue()); // vecteurCompte.add(cptTmp); long solde = cptTmp.getTotalDebit() - cptTmp.getTotalCredit(); if (solde != 0) { if (solde > 0) { gen.setValues(cptTmp.getId(), id_Compte_Bilan_Cloture, 0, Math.abs(solde), "Fermeture du compte " + cptTmp.getNumero(), this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, false); } else { gen.setValues(cptTmp.getId(), id_Compte_Bilan_Cloture, Math.abs(solde), 0, "Fermeture du compte " + cptTmp.getNumero(), this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, false); } gen.genereMouvement(); } } } // A nouveaux Object[] compteAnouveau = this.mRAN.keySet().toArray(); GenerationMvtVirement genAnouveaux = new GenerationMvtVirement(id_Compte_Bilan_Ouverture, 1, 0, 0, "A nouveaux", this.dateOuv.getValue(), JournalSQLElement.OD, "A nouveaux"); for (int i = 0; i < this.mRAN.keySet().size(); i++) { long solde = this.mRAN.get(compteAnouveau[i]).longValue(); // if (solde != 0) { if (solde > 0) { genAnouveaux.setValues(id_Compte_Bilan_Ouverture, Integer.parseInt(compteAnouveau[i].toString()), 0, Math.abs(solde), "A nouveaux", this.dateOuv.getValue(), JournalSQLElement.OD, false); } else { genAnouveaux.setValues(id_Compte_Bilan_Ouverture, Integer.parseInt(compteAnouveau[i].toString()), Math.abs(solde), 0, "A nouveaux", this.dateOuv.getValue(), JournalSQLElement.OD, false); } genAnouveaux.genereMouvement(); // } } // Fix la nouvel date de l'exercice SQLRowValues valsExercice = new SQLRowValues(this.exercice); valsExercice.put("CLOTURE", Boolean.TRUE); try { valsExercice.update(this.rowExercice.getID()); } catch (SQLException e) { e.printStackTrace(); } // Creation d'un nouvel exercice valsExercice.put("CLOTURE", Boolean.FALSE); valsExercice.put("DATE_DEB", new java.sql.Date(this.dateOuv.getValue().getTime())); valsExercice.put("DATE_FIN", new java.sql.Date(this.dateFerm.getValue().getTime())); valsExercice.put("ID_SOCIETE_COMMON", this.rowSociete.getID()); try { SQLRow rowNewEx = valsExercice.insert(); // mise a jour de l'exercice de la societe SQLRowValues rowValsSociete = new SQLRowValues(this.societe); rowValsSociete.put("ID_EXERCICE_COMMON", rowNewEx.getID()); rowValsSociete.update(this.rowSociete.getID()); } catch (SQLException e) { e.printStackTrace(); } this.bar.setValue(4); this.opEnCours.setText("Etat: clture termnine"); }
From source file:org.openconcerto.erp.core.finance.accounting.ui.CloturePanel.java
private void soldeCompte(boolean compteBilan) throws SQLException { String typeCompte;// www.j a v a 2s . co m int compteDest; SQLRow rowPrefCompte = this.tablePrefCompte.getRow(2); if (compteBilan) { typeCompte = "^[^6-8].*$"; compteDest = rowPrefCompte.getInt("ID_COMPTE_PCE_BILAN_F"); if (compteDest <= 1) { compteDest = ComptePCESQLElement.getId("891", "Bilan de clture"); } } else { SommeCompte s = new SommeCompte(); long solde6 = s.soldeCompte(6, 6, true, this.rowExercice.getDate("DATE_DEB").getTime(), this.rowExercice.getDate("DATE_FIN").getTime()); long solde7 = s.soldeCompte(7, 7, true, this.rowExercice.getDate("DATE_DEB").getTime(), this.rowExercice.getDate("DATE_FIN").getTime()); long resultat = -solde7 - solde6; System.err.println("Solde Rsultat :::: " + solde7 + " __ " + solde6 + "__" + (solde7 - solde6)); typeCompte = "^(6|7).*$"; if (resultat > 0) { compteDest = rowPrefCompte.getInt("ID_COMPTE_PCE_RESULTAT"); } else { compteDest = rowPrefCompte.getInt("ID_COMPTE_PCE_RESULTAT_PERTE"); } if (compteDest <= 1) { if (resultat > 0) { compteDest = ComptePCESQLElement.getId("120", "Rsultat de l'exercice (bnfice)"); } else { compteDest = ComptePCESQLElement.getId("129", "Rsultat de l'exercice (perte)"); } } } // on rcupre les comptes avec leurs totaux SQLTable ecritureTable = this.base.getTable("ECRITURE"); SQLTable compteTable = this.base.getTable("COMPTE_PCE"); SQLSelect sel = new SQLSelect(this.base); sel.addSelect(compteTable.getKey()); sel.addSelect(compteTable.getField("NUMERO")); sel.addSelect(compteTable.getField("NOM")); sel.addSelect(ecritureTable.getField("DEBIT"), "SUM"); sel.addSelect(ecritureTable.getField("CREDIT"), "SUM"); Where w = new Where(ecritureTable.getField("ID_COMPTE_PCE"), "=", compteTable.getKey()); String function = "REGEXP"; if (Configuration.getInstance().getBase().getServer().getSQLSystem() == SQLSystem.POSTGRESQL) { // function = "SIMILAR TO"; // typeCompte = typeCompte.replace(".*", "%"); function = "~"; } Where w2 = new Where(compteTable.getField("NUMERO"), function, typeCompte); Where w3 = new Where(ecritureTable.getField("DATE"), "<=", this.rowExercice.getObject("DATE_FIN")); sel.setWhere(w.and(w2).and(w3)); String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\", \"COMPTE_PCE\".\"NUMERO\", \"COMPTE_PCE\".\"NOM\" ORDER BY \"COMPTE_PCE\".\"NUMERO\""; System.err.println(req); Object ob = this.base.getDataSource().execute(req, new ArrayListHandler()); List myList = (List) ob; if (myList != null && myList.size() != 0) { GenerationMvtVirement genFerm = new GenerationMvtVirement(1, compteDest, 0, 0, "Fermeture du compte ", this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, "Fermeture des comptes"); for (int i = 0; i < myList.size(); i++) { Object[] objTmp = (Object[]) myList.get(i); Compte cptTmp = new Compte(((Number) objTmp[0]).intValue(), objTmp[1].toString(), objTmp[2].toString(), "", ((Number) objTmp[3]).longValue(), ((Number) objTmp[4]).longValue()); long solde = cptTmp.getTotalDebit() - cptTmp.getTotalCredit(); // if (solde != 0) { if (compteBilan) { this.mRAN.put(objTmp[0], Long.valueOf(solde)); } if (solde > 0) { genFerm.setValues(cptTmp.getId(), compteDest, 0, Math.abs(solde), "Fermeture du compte " + cptTmp.getNumero(), this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, false); } else { genFerm.setValues(cptTmp.getId(), compteDest, Math.abs(solde), 0, "Fermeture du compte " + cptTmp.getNumero(), this.rowExercice.getDate("DATE_FIN").getTime(), JournalSQLElement.OD, false); } genFerm.genereMouvement(); } // } } }
From source file:org.openconcerto.erp.core.finance.accounting.ui.EtatJournauxPanel.java
private JPanel initJournalPanel(final Journal jrnl) { final JPanel panelTmp = new JPanel(); long totalDebitJournal = 0; long totalCreditJournal = 0; panelTmp.setLayout(new GridBagLayout()); final GridBagConstraints c = new GridBagConstraints(); c.insets = new Insets(2, 2, 1, 2); c.fill = GridBagConstraints.HORIZONTAL; c.anchor = GridBagConstraints.NORTHWEST; c.gridx = 0;//from w w w.ja va 2 s . c om c.gridy = 0; c.gridwidth = 1; c.gridheight = 1; c.weightx = 1; c.weighty = 0; String req = "SELECT DISTINCT EXTRACT(YEAR FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\"), " + "EXTRACT(MONTH FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\")," + " SUM(\"" + baseName + "\".\"ECRITURE\".\"DEBIT\"), " + "SUM(\"" + baseName + "\".\"ECRITURE\".\"CREDIT\")" + " FROM \"" + baseName + "\".\"ECRITURE\" " + "WHERE (\"" + baseName + "\".\"ECRITURE\".\"ID\" != 1) " + "AND ((\"" + baseName + "\".\"ECRITURE\".\"ARCHIVE\" = 0) " + "AND (\"" + baseName + "\".\"ECRITURE\".\"ID_JOURNAL\" = " + jrnl.getId() + ")) " + "GROUP BY EXTRACT(YEAR FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\"), " + "EXTRACT(MONTH FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\") " + "ORDER BY EXTRACT(YEAR FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\"), " + "EXTRACT(MONTH FROM \"" + baseName + "\".\"ECRITURE\".\"DATE\")"; System.out.println(req); Object ob = base.getDataSource().execute(req, new ArrayListHandler()); List myList = (List) ob; // System.err.println("TEST DATE " + t); if (myList.size() != 0) { for (int i = 0; i < myList.size(); i++) { Object[] objTmp = (Object[]) myList.get(i); Calendar cal = Calendar.getInstance(); cal.set(Calendar.DATE, 1); int month = (new Double(objTmp[1].toString()).intValue() - 1); System.err.println(jrnl.getNom() + " SET MONTH " + month); cal.set(Calendar.MONTH, month); System.err.println(month + " = " + cal.getTime()); cal.set(Calendar.YEAR, new Double(objTmp[0].toString()).intValue()); long debitMois = ((Number) objTmp[2]).longValue(); long creditMois = ((Number) objTmp[3]).longValue(); c.gridwidth = GridBagConstraints.REMAINDER; final JPanel creerJournalMoisPanel = creerJournalMoisPanel(cal.getTime(), debitMois, creditMois, jrnl); creerJournalMoisPanel.setOpaque(false); panelTmp.add(creerJournalMoisPanel, c); c.gridy++; totalDebitJournal += debitMois; totalCreditJournal += creditMois; } } c.gridx = 0; c.weighty = 1; c.gridwidth = 1; final JLabel label = new JLabel("Journal " + jrnl.getNom()); label.setOpaque(false); panelTmp.add(label, c); c.gridx = GridBagConstraints.RELATIVE; panelTmp.add(new JLabel(" Total dbit : " + GestionDevise.currencyToString(totalDebitJournal)), c); panelTmp.add(new JLabel(" Total crdit : " + GestionDevise.currencyToString(totalCreditJournal)), c); return panelTmp; }