List of usage examples for org.apache.commons.dbutils.handlers ArrayListHandler ArrayListHandler
public ArrayListHandler()
BasicRowProcessor for conversions. From source file:org.openconcerto.erp.core.supplychain.receipt.component.BonReceptionSQLComponent.java
@Override public void update() { if (!this.textNumeroUnique.checkValidation()) { ExceptionHandler.handle("Impossible d'ajouter, numro de bon de livraison existant."); Object root = SwingUtilities.getRoot(this); if (root instanceof EditFrame) { EditFrame frame = (EditFrame) root; frame.getPanel().setAlwaysVisible(true); }//w w w . ja v a 2s.co m return; } else { // Mise jour de l'lment super.update(); this.tableBonItem.updateField("ID_BON_RECEPTION", getSelectedID()); this.tableBonItem.createArticle(getSelectedID(), this.getElement()); final int id = getSelectedID(); ComptaPropsConfiguration.getInstanceCompta().getNonInteractiveSQLExecutor().execute(new Runnable() { @Override public void run() { try { // On efface les anciens mouvements de stocks SQLRow row = getTable().getRow(id); SQLElement eltMvtStock = Configuration.getInstance().getDirectory() .getElement("MOUVEMENT_STOCK"); SQLSelect sel = new SQLSelect(); sel.addSelect(eltMvtStock.getTable().getField("ID")); Where w = new Where(eltMvtStock.getTable().getField("IDSOURCE"), "=", row.getID()); Where w2 = new Where(eltMvtStock.getTable().getField("SOURCE"), "=", getTable().getName()); sel.setWhere(w.and(w2)); List l = (List) eltMvtStock.getTable().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler()); if (l != null) { for (int i = 0; i < l.size(); i++) { Object[] tmp = (Object[]) l.get(i); eltMvtStock.archive(((Number) tmp[0]).intValue()); } } // Mise jour du stock updateStock(id); } catch (Exception e) { ExceptionHandler.handle("Update error", e); } } }); } }
From source file:org.openconcerto.erp.core.supplychain.receipt.element.BonReceptionSQLElement.java
@Override protected void archive(SQLRow row, boolean cutLinks) throws SQLException { super.archive(row, cutLinks); // Mise jour des stocks SQLElement eltMvtStock = Configuration.getInstance().getDirectory().getElement("MOUVEMENT_STOCK"); SQLSelect sel = new SQLSelect(eltMvtStock.getTable().getBase()); sel.addSelect(eltMvtStock.getTable().getField("ID")); Where w = new Where(eltMvtStock.getTable().getField("IDSOURCE"), "=", row.getID()); Where w2 = new Where(eltMvtStock.getTable().getField("SOURCE"), "=", getTable().getName()); sel.setWhere(w.and(w2));/*www .j a v a2 s . com*/ List l = (List) eltMvtStock.getTable().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler()); if (l != null) { for (int i = 0; i < l.size(); i++) { Object[] tmp = (Object[]) l.get(i); eltMvtStock.archive(((Number) tmp[0]).intValue()); } } }
From source file:org.openconcerto.erp.core.supplychain.supplier.ui.HistoriqueFournBilanPanel.java
private void updateChequeData(int idFournisseur) { SQLBase base = ((ComptaPropsConfiguration) ComptaPropsConfiguration.getInstance()).getSQLBaseSociete(); SQLTable tableC = base.getTable("CHEQUE_FOURNISSEUR"); final long valueTotal = getSumForField(tableC.getField("MONTANT"), idFournisseur); SQLSelect selNb = new SQLSelect(base); selNb.addSelectStar(tableC);//from w w w.ja v a 2 s .c om if (idFournisseur > 1) { selNb.setWhere(tableC.getField("ID_FOURNISSEUR"), "=", idFournisseur); } List lnb = (List) base.getDataSource().execute(selNb.asString(), new ArrayListHandler()); final int nombreCheque = (lnb == null) ? 0 : lnb.size(); SQLSelect sel = new SQLSelect(base); sel.addSelectStar(tableC); Where w = new Where(tableC.getField("DECAISSE"), "=", Boolean.FALSE); if (idFournisseur > 1) { w = w.and(new Where(tableC.getField("ID_FOURNISSEUR"), "=", idFournisseur)); } sel.setWhere(w); List l = (List) base.getDataSource().execute(sel.asString(), new ArrayListHandler()); final int valueNonEncaisse = (l == null) ? 0 : l.size(); SwingUtilities.invokeLater(new Runnable() { public void run() { String labelCheques = String.valueOf(nombreCheque); if (nombreCheque > 1) { labelCheques += CHEQUES; } else { labelCheques += CHEQUE; } labelCheques += GestionDevise.currencyToString(valueTotal, true) + " TTC"; if (valueNonEncaisse > 1) { labelCheques += " dont " + String.valueOf(valueNonEncaisse) + NON_DECAISSES; } else { labelCheques += " dont " + String.valueOf(valueNonEncaisse) + NON_DECAISSE; } HistoriqueFournBilanPanel.this.labelCheque.setText(labelCheques); } }); }
From source file:org.openconcerto.erp.core.supplychain.supplier.ui.HistoriqueFournBilanPanel.java
private void updateAchatData(int idFournisseur) { SQLBase base = ((ComptaPropsConfiguration) ComptaPropsConfiguration.getInstance()).getSQLBaseSociete(); // Total/*from w ww .ja v a 2 s . c o m*/ SQLTable tableC = base.getTable("SAISIE_ACHAT"); final long valueTotal = getSumForField(tableC.getField("MONTANT_TTC"), idFournisseur); // Nombre d'achats SQLSelect selNb = new SQLSelect(base); selNb.addSelectStar(tableC); if (idFournisseur > 1) { selNb.setWhere(tableC.getField("ID_FOURNISSEUR"), "=", idFournisseur); } List lnb = (List) base.getDataSource().execute(selNb.asString(), new ArrayListHandler()); final int nombreAchat = (lnb == null) ? 0 : lnb.size(); SwingUtilities.invokeLater(new Runnable() { public void run() { String labelAchats = String.valueOf(nombreAchat); if (nombreAchat > 1) { labelAchats += ACHATS; } else { labelAchats += ACHAT; } labelAchats += GestionDevise.currencyToString(valueTotal, true) + " TTC"; HistoriqueFournBilanPanel.this.labelAchat.setText(labelAchats); } }); }
From source file:org.openconcerto.erp.core.supplychain.supplier.ui.HistoriqueFournBilanPanel.java
/** * Calcul la somme des valeurs du champs f pour le fournisseur d'id idFourn. f est un champ * contenant une devise/*from w w w . ja va2 s. c o m*/ * * @param f * @param idFourn * @return la somme total en long */ private long getSumForField(SQLField f, int idFourn) { SQLSelect sel = new SQLSelect(f.getTable().getBase()); sel.addSelect(f, "SUM"); if (idFourn > 1) { sel.setWhere(f.getTable().getField("ID_FOURNISSEUR"), "=", idFourn); } List l = (List) f.getTable().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler()); for (int i = 0; i < l.size(); i++) { Object[] tmp = (Object[]) l.get(i); if (tmp != null && tmp[0] != null) { return new Double(tmp[0].toString()).longValue(); } } return 0; }
From source file:org.openconcerto.erp.generationDoc.gestcomm.EtatVentesXmlSheet.java
protected void createListeValues() { final SQLElementDirectory directory = Configuration.getInstance().getDirectory(); final SQLElement eltVenteFacutreElement = directory.getElement("SAISIE_VENTE_FACTURE_ELEMENT"); final SQLElement eltVenteFacture = directory.getElement("SAISIE_VENTE_FACTURE"); final SQLElement eltEncaissement = directory.getElement("ENCAISSER_MONTANT"); final SQLElement eltTicketCaisse = directory.getElement("TICKET_CAISSE"); final SQLElement eltModeReglement = directory.getElement("MODE_REGLEMENT"); final SQLTable tableModeReglement = eltModeReglement.getTable(); final SQLTable tableFactureElement = eltVenteFacutreElement.getTable(); final SQLTable tableFacture = eltVenteFacture.getTable(); final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1); final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2); final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket"); // Requete Pour obtenir les quantits pour chaque type de rglement SQLSelect sel = new SQLSelect(Configuration.getInstance().getBase()); sel.addSelect(tableFactureElement.getField("CODE")); sel.addSelect(tableFactureElement.getField("NOM")); // Elements assoscis une facture Where w = new Where(tableFactureElement.getField("ID_TICKET_CAISSE"), "=", tableTicket.getTable().getUndefinedID()); sel.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w); // Elements associs un ticket de caisse Where w2 = new Where(tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE"), "=", 1); sel.addJoin("LEFT", tableFacture.getField("ID_MODE_REGLEMENT"), MODE1); sel.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2); sel.addBackwardJoin("LEFT", "enc", eltEncaissement.getTable().getField("ID_TICKET_CAISSE"), "ticket"); sel.addJoin("LEFT", new AliasedField(eltEncaissement.getTable().getField("ID_MODE_REGLEMENT"), "enc"), MODE2);/* w ww .j a v a 2s. c o m*/ final String idTypeReglement1 = tableModeReglement1.getField("ID_TYPE_REGLEMENT").getFieldRef(); final String idTypeReglement2 = tableModeReglement2.getField("ID_TYPE_REGLEMENT").getFieldRef(); final String qte = sel.getAlias(tableFactureElement.getField("QTE")).getFieldRef(); sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=2 OR " + idTypeReglement2 + "=2 THEN " + qte + " ELSE 0 END)", "Cheque"); sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=3 OR " + idTypeReglement2 + "=3 THEN " + qte + " ELSE 0 END)", "CB"); sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=4 OR " + idTypeReglement2 + "=4 THEN " + qte + " ELSE 0 END)", "Especes"); Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au); Where w4 = new Where(tableFacture.getField("DATE"), this.du, this.au); if (this.du != null && this.au != null) { sel.setWhere(w3.or(w4)); } // FIXME traiter le cas du!=null et au==null et vice versa sel.addGroupBy(tableFactureElement.getField("NOM")); sel.addGroupBy(tableFactureElement.getField("CODE")); System.err.println(sel.asString()); // Requete pour obtenir les quantits vendus SQLSelect selQte = new SQLSelect(Configuration.getInstance().getBase()); selQte.addSelect(tableFactureElement.getField("CODE")); selQte.addSelect(tableFactureElement.getField("NOM")); selQte.addSelect(tableFactureElement.getField("QTE"), "SUM"); selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM"); selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM"); selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM"); selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w); selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2); if (this.du != null && this.au != null) { selQte.setWhere(w3.or(w4)); // FIXME traiter le cas du!=null et au==null et vice versa } selQte.addGroupBy(tableFactureElement.getField("NOM")); selQte.addGroupBy(tableFactureElement.getField("CODE")); List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource() .execute(selQte.asString(), new ArrayListHandler()); // Rcupration des quantits et des montant totaux pour chaque article Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>(); for (Object[] sqlRow : listeQte) { String code = (String) sqlRow[0]; String nom = (String) sqlRow[1]; Number qteVendu = (Number) sqlRow[2]; Number ha = (Number) sqlRow[3]; Number ht = (Number) sqlRow[4]; Number ttc = (Number) sqlRow[5]; ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, (BigDecimal) ttc); map.put(code + "##" + nom, a); } List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource() .execute(sel.asString(), new ArrayListHandler()); if (listeIds == null) { return; } // Liste des valeurs de la feuille OO ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>(listeIds.size()); BigDecimal totalTPA = BigDecimal.ZERO; BigDecimal totalTPVTTC = BigDecimal.ZERO; for (Object[] obj : listeIds) { Map<String, Object> mValues = new HashMap<String, Object>(); String code = (String) obj[0]; String nom = (String) obj[1]; ArticleVendu a = map.get(code + "##" + nom); mValues.put("CODE", code); mValues.put("NOM", nom); mValues.put("QTE", a.qte); mValues.put("T_PA", a.ha); mValues.put("T_PV_HT", a.ht); mValues.put("T_PV_TTC", a.ttc); mValues.put("NB_CHEQUE", obj[2]); mValues.put("NB_CB", obj[3]); mValues.put("NB_ESPECES", obj[4]); totalTPA = totalTPA.add(a.ha); totalTPVTTC = totalTPVTTC.add(a.ttc); listValues.add(mValues); System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues); } // Liste des ventes comptoirs final SQLTable venteComptoirT = directory.getElement("SAISIE_VENTE_COMPTOIR").getTable(); SQLSelect selVC = new SQLSelect(venteComptoirT.getBase()); selVC.addSelect(venteComptoirT.getField("NOM")); selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM"); selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM"); selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT"); if (this.du != null && this.au != null) { Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au); wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey())); selVC.setWhere(wVC); } else { selVC.setWhere(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey())); } // FIXME traiter le cas du!=null et au==null et vice versa selVC.addGroupBy(venteComptoirT.getField("NOM")); List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource() .execute(selVC.asString(), new ArrayListHandler()); long totalVCInCents = 0; if (listVC.size() > 0) { Map<String, Object> mValues = new HashMap<String, Object>(); mValues.put("NOM", " "); listValues.add(mValues); Map<String, Object> mValues2 = new HashMap<String, Object>(); if (listVC.size() > 1) { mValues2.put("NOM", "VENTES COMPTOIR"); } else { mValues2.put("NOM", "VENTE COMPTOIR"); } Map<Integer, String> style = styleAllSheetValues.get(0); if (style == null) { style = new HashMap<Integer, String>(); } style.put(listValues.size(), "Titre 1"); styleAllSheetValues.put(0, style); listValues.add(mValues2); } for (Object[] rowVenteComptoir : listVC) { final Map<String, Object> mValues = new HashMap<String, Object>(); // Nom mValues.put("NOM", rowVenteComptoir[0]); // HT mValues.put("T_PV_HT", ((Number) rowVenteComptoir[1]).longValue() / 100.0D); // TTC final long ttcInCents = ((Number) rowVenteComptoir[2]).longValue(); mValues.put("T_PV_TTC", ttcInCents / 100.0D); totalVCInCents += ttcInCents; // Quantit mValues.put("QTE", rowVenteComptoir[3]); listValues.add(mValues); } // Liste des Achats final ArrayList<Map<String, Object>> listValuesAchat = new ArrayList<Map<String, Object>>(listeIds.size()); Map<String, Object> valuesAchat = this.mapAllSheetValues.get(1); if (valuesAchat == null) { valuesAchat = new HashMap<String, Object>(); } final SQLElement eltAchat = directory.getElement("SAISIE_ACHAT"); final SQLTable tableAchat = eltAchat.getTable(); final SQLSelect selAchat = new SQLSelect(Configuration.getInstance().getBase()); selAchat.addSelect(tableAchat.getField("NOM")); selAchat.addSelect(tableAchat.getField("MONTANT_HT"), "SUM"); selAchat.addSelect(tableAchat.getField("MONTANT_TTC"), "SUM"); final Where wHA = new Where(tableAchat.getField("DATE"), this.du, this.au); selAchat.setWhere(wHA); selAchat.addGroupBy(tableAchat.getField("NOM")); List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource() .execute(selAchat.asString(), new ArrayListHandler()); long totalAchatInCents = 0; for (Object[] row : listAchat) { Map<String, Object> mValues = new HashMap<String, Object>(); mValues.put("NOM", row[0]); long ht = ((Number) row[1]).longValue(); long pA = ((Number) row[2]).longValue(); mValues.put("T_PV_HT", -ht / 100.0D); mValues.put("T_PV_TTC", -pA / 100.0D); totalAchatInCents -= pA; listValuesAchat.add(mValues); } totalTPVTTC = totalTPVTTC.add(new BigDecimal(totalVCInCents).movePointLeft(2)); // Rcapitulatif Map<String, Object> valuesE = this.mapAllSheetValues.get(2); if (valuesE == null) { valuesE = new HashMap<String, Object>(); } SQLElement eltE = directory.getElement("ENCAISSER_MONTANT"); SQLElement eltM = directory.getElement("MODE_REGLEMENT"); SQLElement eltT = directory.getElement("TYPE_REGLEMENT"); SQLSelect selE = new SQLSelect(Configuration.getInstance().getBase()); selE.addSelect(eltT.getTable().getField("NOM")); selE.addSelect(eltT.getTable().getField("NOM"), "COUNT"); selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM"); Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au); wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey())); wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey())); selE.setWhere(wE); selE.addGroupBy(eltT.getTable().getField("NOM")); selE.addFieldOrder(eltT.getTable().getField("NOM")); List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource() .execute(selE.asString(), new ArrayListHandler()); ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>(listeIds.size()); long totalEInCents = 0; for (Object[] o : listE) { Map<String, Object> mValues = new HashMap<String, Object>(); mValues.put("NOM", o[0]); final long pA = ((Number) o[2]).longValue(); mValues.put("QTE", o[1]); mValues.put("TOTAL", pA / 100.0D); totalEInCents += pA; listValuesE.add(mValues); } Map<String, Object> values = this.mapAllSheetValues.get(0); if (values == null) { values = new HashMap<String, Object>(); } valuesAchat.put("TOTAL", totalAchatInCents / 100f); valuesE.put("TOTAL_HA", totalAchatInCents / 100f); valuesE.put("TOTAL", totalEInCents / 100f); valuesE.put("TOTAL_VT", totalTPVTTC); values.put("TOTAL", totalVCInCents / 100f); values.put("TOTAL_MARGE", totalTPVTTC.subtract(totalTPA)); valuesE.put("TOTAL_GLOBAL", totalTPVTTC.add(new BigDecimal(totalAchatInCents).movePointLeft(2))); values.put("TOTAL_PA", totalTPA); values.put("TOTAL_PV_TTC", totalTPVTTC); String periode = ""; if (this.du != null && this.au != null) { periode = "Priode du " + DATE_FORMAT.format(this.du) + " au " + DATE_FORMAT.format(this.au); } else if (du == null && au != null) { periode = "Priode jusqu'au " + DATE_FORMAT.format(this.au); } else if (du != null && du != null) { periode = "Priode depuis le " + DATE_FORMAT.format(this.du); } values.put("DATE", periode); valuesAchat.put("DATE", periode); valuesE.put("DATE", periode); System.err.println(this.du); System.err.println(this.au); this.listAllSheetValues.put(0, listValues); this.mapAllSheetValues.put(0, values); this.listAllSheetValues.put(1, listValuesAchat); this.mapAllSheetValues.put(1, valuesAchat); this.listAllSheetValues.put(2, listValuesE); this.mapAllSheetValues.put(2, valuesE); }
From source file:org.openconcerto.erp.generationEcritures.GenerationEcritures.java
/** * Cre un nouveau mouvement associ la piece d'id idPiece * //from w w w . j a v a2s .c o m * @param source * @param idSource * @param idPere * @param idPiece * @return id d'un nouveau mouvement */ synchronized public int getNewMouvement(String source, int idSource, int idPere, int idPiece) { SQLTable mouvementTable = base.getTable("MOUVEMENT"); // on calcule le nouveau numero de mouvement SQLSelect selNumMvt = new SQLSelect(base); selNumMvt.addSelect(mouvementTable.getField("NUMERO")); String reqNumMvt = selNumMvt.asString(); Object obNumMvt = base.getDataSource().execute(reqNumMvt, new ArrayListHandler()); List myListNumMvt = (List) obNumMvt; int numMvt = 1; if (myListNumMvt.size() != 0) { for (int i = 0; i < myListNumMvt.size(); i++) { Object[] objTmp = (Object[]) myListNumMvt.get(i); int tmp = Integer.parseInt(objTmp[0].toString()); if (numMvt < tmp) { numMvt = tmp; } } } numMvt++; // Creation du mouvement Map<String, Object> m = new HashMap<String, Object>(); m.put("SOURCE", source); m.put("IDSOURCE", Integer.valueOf(idSource)); m.put("ID_MOUVEMENT_PERE", Integer.valueOf(idPere)); this.idPiece = idPiece; m.put("ID_PIECE", Integer.valueOf(idPiece)); m.put("NUMERO", Integer.valueOf(numMvt)); SQLRowValues val = new SQLRowValues(mouvementTable, m); try { if (val.getInvalid() == null) { SQLRow row = val.insert(); this.idMvt = row.getID(); this.mEcritures.put("ID_MOUVEMENT", Integer.valueOf(this.idMvt)); } else { System.err.println( "Error in values for insert in table " + val.getTable().getName() + " : " + val.toString()); } } catch (SQLException e) { System.err.println("Error insert row in " + val.getTable().getName()); e.printStackTrace(); } System.err.println("Numero de mouvement gnr : " + numMvt); return this.idMvt; }
From source file:org.openconcerto.erp.generationEcritures.GenerationMvtFichePaye.java
private void genereComptaFichePaye() throws Exception { System.out.println("Gnration des ecritures reglement du mouvement " + this.idMvt); // SQLRow rowFiche = // Configuration.getInstance().getBase().getTable("FICHE_PAYE").getRow(this.idFichePaye); // iniatilisation des valeurs de la map this.date = new Date(); // SQLRow rowMois = tableMois.getRow(rowFiche.getInt("ID_MOIS")); // SQLRow rowSal = tableSalarie.getRow(rowFiche.getInt("ID_SALARIE")); this.nom = "Paye " + this.mois + " " + this.annee; this.mEcritures.put("DATE", new java.sql.Date(this.date.getTime())); this.mEcritures.put("NOM", this.nom); this.mEcritures.put("ID_JOURNAL", journalOD); this.mEcritures.put("ID_MOUVEMENT", Integer.valueOf(this.idMvt)); // Salaire Brut Debit // float totalSalaireBrut = 0.0F; for (int i = 0; i < this.idFichePaye.length; i++) { SQLRow rowFiche = tableFichePaye.getRow(this.idFichePaye[i]); SQLRow rowSal = tableSalarie.getRow(rowFiche.getInt("ID_SALARIE")); int idComptePaye = rowPrefsCompte.getInt("ID_COMPTE_PCE_PAYE"); if (idComptePaye <= 1) { idComptePaye = ComptePCESQLElement.getIdComptePceDefault("PayeRemunerationPersonnel"); }/*from w w w . ja va2 s . c o m*/ this.mEcritures.put("ID_COMPTE_PCE", Integer.valueOf(idComptePaye)); this.mEcritures.put("NOM", rowSal.getString("NOM") + " " + this.nom); float sal = rowFiche.getFloat("SAL_BRUT"); // totalSalaireBrut += sal; this.mEcritures.put("DEBIT", Long.valueOf(GestionDevise.parseLongCurrency(String.valueOf(sal)))); this.mEcritures.put("CREDIT", Long.valueOf(0)); ajoutEcriture(); } // Salaire Brut Credit for (int i = 0; i < this.idFichePaye.length; i++) { SQLRow rowFiche = tableFichePaye.getRow(this.idFichePaye[i]); SQLRow rowSal = tableSalarie.getRow(rowFiche.getInt("ID_SALARIE")); SQLRow rowRegl = tableReglementPaye.getRow(rowSal.getInt("ID_REGLEMENT_PAYE")); int idComptePayeRegl = rowRegl.getInt("ID_COMPTE_PCE"); if (idComptePayeRegl <= 1) { idComptePayeRegl = ComptePCESQLElement.getIdComptePceDefault("PayeReglement"); } this.mEcritures.put("ID_COMPTE_PCE", Integer.valueOf(idComptePayeRegl)); this.mEcritures.put("NOM", rowSal.getString("NOM") + " " + this.nom); float sal = rowFiche.getFloat("SAL_BRUT"); this.mEcritures.put("DEBIT", Long.valueOf(0)); this.mEcritures.put("CREDIT", Long.valueOf(GestionDevise.parseLongCurrency(String.valueOf(sal)))); ajoutEcriture(); } /* * this.mEcritures.put("ID_COMPTE_PCE", new Integer(ComptePCESQLElement.getId("421"))); * this.mEcritures.put("NOM", this.nom); this.mEcritures.put("DEBIT", new Float(0)); * this.mEcritures.put("CREDIT", new Float(totalSalaireBrut)); ajoutEcriture(); */ // Acomptes for (int i = 0; i < this.idFichePaye.length; i++) { SQLRow rowFiche = tableFichePaye.getRow(this.idFichePaye[i]); SQLRow rowSal = tableSalarie.getRow(rowFiche.getInt("ID_SALARIE")); long acompte = GestionDevise.parseLongCurrency(String.valueOf(rowFiche.getFloat("ACOMPTE"))); if (acompte != 0) { int idCompteAcompte = rowPrefsCompte.getInt("ID_COMPTE_PCE_ACOMPTE"); if (idCompteAcompte <= 1) { idCompteAcompte = ComptePCESQLElement.getIdComptePceDefault("PayeAcompte"); } this.mEcritures.put("ID_COMPTE_PCE", Integer.valueOf(idCompteAcompte)); this.mEcritures.put("NOM", rowSal.getString("NOM") + " Acompte sur " + this.nom); this.mEcritures.put("DEBIT", Long.valueOf(0)); this.mEcritures.put("CREDIT", Long.valueOf(acompte)); ajoutEcriture(); SQLRow rowRegl = tableReglementPaye.getRow(rowSal.getInt("ID_REGLEMENT_PAYE")); int idComptePayeRegl = rowRegl.getInt("ID_COMPTE_PCE"); if (idComptePayeRegl <= 1) { idComptePayeRegl = ComptePCESQLElement.getIdComptePceDefault("PayeReglement"); } this.mEcritures.put("ID_COMPTE_PCE", Integer.valueOf(idComptePayeRegl)); this.mEcritures.put("NOM", rowSal.getString("NOM") + " Acompte sur " + this.nom); this.mEcritures.put("DEBIT", Long.valueOf(acompte)); this.mEcritures.put("CREDIT", Long.valueOf(0)); ajoutEcriture(); } } // on recupere les lements de la fiche // ensemble des cotisations SQLSelect selAllIDFicheElt = new SQLSelect(base); selAllIDFicheElt.addSelect(tableFichePayeElt.getField("ID")); Where w = null; for (int i = 0; i < this.idFichePaye.length; i++) { if (w == null) { w = new Where(tableFichePayeElt.getField("ID_FICHE_PAYE"), "=", this.idFichePaye[i]); } else { w.and(new Where(tableFichePayeElt.getField("ID_FICHE_PAYE"), "=", this.idFichePaye[i])); } } selAllIDFicheElt.setWhere(w); selAllIDFicheElt.setDistinct(true); String reqAllIDFichelElt = selAllIDFicheElt.asString(); System.err.println("Request " + reqAllIDFichelElt); Object[] objIDFicheElt = ((List) base.getDataSource().execute(reqAllIDFichelElt, new ArrayListHandler())) .toArray(); System.err.println(objIDFicheElt.length + " elements to load"); Map<Integer, Long> mapCompteDebSal = new HashMap<Integer, Long>(); Map<Integer, Long> mapCompteDebPat = new HashMap<Integer, Long>(); Map<Integer, Long> mapCompteCredSal = new HashMap<Integer, Long>(); Map<Integer, Long> mapCompteCredPat = new HashMap<Integer, Long>(); for (int i = 0; i < objIDFicheElt.length; i++) { SQLRow row = tableFichePayeElt.getRow(Integer.parseInt(((Object[]) objIDFicheElt[i])[0].toString())); String source = row.getString("SOURCE"); int idSource = row.getInt("IDSOURCE"); if (source.trim().length() != 0) { System.err.println("Source != null"); if (this.mapTableSource.get(source) != null) { SQLRow rowSource = this.mapTableSource.get(source).getRow(idSource); if (rowSource.getTable().getName().equalsIgnoreCase("RUBRIQUE_COTISATION")) { // on recupere les comptes tiers et charge de la caisse associe int idCompteCharge = ComptePCESQLElement.getId("645"); // } // int idCompteTiers = rowCaisse.getInt("ID_COMPTE_PCE_TIERS"); // if (idCompteTiers <= 1) { int idCompteTiers = ComptePCESQLElement.getId("437"); // } // Cotisations sal. if (row.getFloat("MONTANT_SAL_DED") != 0) { Object montantCredObj = mapCompteCredSal.get(Integer.valueOf(idCompteTiers)); long montantCred = (montantCredObj == null) ? 0 : ((Long) montantCredObj).longValue(); montantCred += GestionDevise .parseLongCurrency(row.getObject("MONTANT_SAL_DED").toString()); mapCompteCredSal.put(Integer.valueOf(idCompteTiers), Long.valueOf(montantCred)); Object montantDebObj = mapCompteDebSal .get(Integer.valueOf(ComptePCESQLElement.getId("421"))); long montantDeb = (montantDebObj == null) ? 0 : ((Long) montantDebObj).longValue(); montantDeb += GestionDevise .parseLongCurrency(row.getObject("MONTANT_SAL_DED").toString()); mapCompteDebSal.put(Integer.valueOf(ComptePCESQLElement.getId("421")), Long.valueOf(montantDeb)); } // Cotisation pat. if (row.getFloat("MONTANT_PAT") != 0) { Object montantDebObj = mapCompteDebPat.get(Integer.valueOf(idCompteCharge)); long montantDeb = (montantDebObj == null) ? 0 : ((Long) montantDebObj).longValue(); montantDeb += GestionDevise.parseLongCurrency(row.getObject("MONTANT_PAT").toString()); mapCompteDebPat.put(Integer.valueOf(idCompteCharge), Long.valueOf(montantDeb)); Object montantCredObj = mapCompteCredPat.get(Integer.valueOf(idCompteTiers)); long montantCred = (montantCredObj == null) ? 0 : ((Long) montantCredObj).longValue(); montantCred += GestionDevise.parseLongCurrency(row.getObject("MONTANT_PAT").toString()); mapCompteCredPat.put(Integer.valueOf(idCompteTiers), Long.valueOf(montantCred)); } } } else { System.err.println("Table " + source + " non rfrence"); } } } // enregistrement des ecritures pour les cotisations salariales et patronales for (Entry<Integer, Long> entry : mapCompteCredSal.entrySet()) { Integer idCompte = entry.getKey(); this.mEcritures.put("ID_COMPTE_PCE", idCompte); this.mEcritures.put("NOM", "Cotisations salariales, " + this.nom); this.mEcritures.put("DEBIT", Long.valueOf(0)); this.mEcritures.put("CREDIT", entry.getValue()); ajoutEcriture(); } for (Entry<Integer, Long> entry : mapCompteDebSal.entrySet()) { Integer idCompte = entry.getKey(); this.mEcritures.put("ID_COMPTE_PCE", idCompte); this.mEcritures.put("NOM", "Cotisations salariales, " + this.nom); this.mEcritures.put("CREDIT", Long.valueOf(0)); this.mEcritures.put("DEBIT", entry.getValue()); ajoutEcriture(); } for (Entry<Integer, Long> entry : mapCompteCredPat.entrySet()) { Integer idCompte = entry.getKey(); this.mEcritures.put("ID_COMPTE_PCE", idCompte); this.mEcritures.put("NOM", "Cotisations patronales, " + this.nom); this.mEcritures.put("DEBIT", Long.valueOf(0)); this.mEcritures.put("CREDIT", entry.getValue()); ajoutEcriture(); } for (Entry<Integer, Long> entry : mapCompteDebPat.entrySet()) { Integer idCompte = entry.getKey(); this.mEcritures.put("ID_COMPTE_PCE", idCompte); this.mEcritures.put("NOM", "Cotisations patronales, " + this.nom); this.mEcritures.put("CREDIT", Long.valueOf(0)); this.mEcritures.put("DEBIT", entry.getValue()); ajoutEcriture(); } // MAYBE Reglement de la paie }
From source file:org.openconcerto.erp.graph.GraphArticleMargePanel.java
@Override protected void updateDataset(List<String> labels, List<Number> values) { final SQLTable tableVFElement = Configuration.getInstance().getDirectory() .getElement("SAISIE_VENTE_FACTURE_ELEMENT").getTable(); final SQLSelect sel = new SQLSelect(tableVFElement.getBase()); final String field = "NOM"; sel.addSelect(tableVFElement.getField(field)); sel.addSelect(tableVFElement.getField("PA_HT")); sel.addSelect(tableVFElement.getField("PV_HT")); sel.addSelect(tableVFElement.getField("QTE"), "SUM"); final List<Object[]> rowsArticle = (List<Object[]>) Configuration.getInstance().getBase().getDataSource() .execute(sel.asString() + " GROUP BY \"SAISIE_VENTE_FACTURE_ELEMENT\".\"" + field + "\"" + ",\"SAISIE_VENTE_FACTURE_ELEMENT\".\"PA_HT\"" + ",\"SAISIE_VENTE_FACTURE_ELEMENT\".\"PV_HT\"", new ArrayListHandler()); Collections.sort(rowsArticle, new Comparator<Object[]>() { @Override//from w ww . java 2s . c om public int compare(Object[] o1, Object[] o2) { BigDecimal pa1 = (BigDecimal) o1[1]; BigDecimal pv1 = (BigDecimal) o1[2]; BigDecimal qte1 = new BigDecimal(o1[3].toString()); BigDecimal pa2 = (BigDecimal) o2[1]; BigDecimal pv2 = (BigDecimal) o2[2]; BigDecimal qte2 = new BigDecimal(o2[3].toString()); BigDecimal marge1 = pv1.subtract(pa1).multiply(qte1, MathContext.DECIMAL128); BigDecimal marge2 = pv2.subtract(pa2).multiply(qte2, MathContext.DECIMAL128); return marge1.compareTo(marge2); } }); for (int i = 0; i < 10 && i < rowsArticle.size(); i++) { Object[] o = rowsArticle.get(i); BigDecimal pa2 = (BigDecimal) o[1]; BigDecimal pv2 = (BigDecimal) o[2]; BigDecimal qte2 = new BigDecimal(o[3].toString()); BigDecimal marge2 = pv2.subtract(pa2).multiply(qte2, MathContext.DECIMAL128); final String string = o[0].toString(); values.add(marge2); labels.add(string); } }
From source file:org.openconcerto.erp.graph.GraphArticleVentePanel.java
protected void updateDataset(List<String> labels, List<Number> values) { SQLTable tableVFElement = Configuration.getInstance().getDirectory() .getElement("SAISIE_VENTE_FACTURE_ELEMENT").getTable(); SQLSelect sel = new SQLSelect(tableVFElement.getBase()); String field = "NOM"; sel.addSelect(tableVFElement.getField(field)); sel.addSelectFunctionStar("COUNT"); final SQLDataSource dataSource = Configuration.getInstance().getBase().getDataSource(); @SuppressWarnings("unchecked") List<Object[]> rowsArticle = (List<Object[]>) dataSource.execute( sel.asString() + " GROUP BY \"SAISIE_VENTE_FACTURE_ELEMENT\".\"" + field + "\"", new ArrayListHandler()); Collections.sort(rowsArticle, new Comparator<Object[]>() { @Override//w w w .ja v a 2 s . c o m public int compare(Object[] o1, Object[] o2) { return Integer.parseInt(o2[1].toString()) - Integer.parseInt(o1[1].toString()); } }); int rowCount = 0; for (Object[] objects : rowsArticle) { int value = Integer.parseInt(objects[1].toString()); rowCount += value; } for (int i = 0; i < 10 && i < rowsArticle.size(); i++) { Object[] o = rowsArticle.get(i); int value = Integer.parseInt(o[1].toString()); final String string = o[0].toString() + " (" + Math.round(((double) value / (double) rowCount) * 10000.0) / 100.0 + "%)"; values.add(value); labels.add(string); } }