Example usage for org.apache.commons.dbutils.handlers ArrayListHandler ArrayListHandler

List of usage examples for org.apache.commons.dbutils.handlers ArrayListHandler ArrayListHandler

  1. HOME
  2. Java
  3. org.apache.commons
  4. org.apache.commons.dbutils.handlers.*
  5. ArrayListHandler
  6. ArrayListHandler

Introduction

In this page you can find the example usage for org.apache.commons.dbutils.handlers ArrayListHandler ArrayListHandler.

Prototype

public ArrayListHandler()

Source Link

Document

Creates a new instance of ArrayListHandler using a BasicRowProcessor for conversions.

Usage

From source file:org.openconcerto.erp.core.humanresources.payroll.report.FichePayeSheet.java

protected void createMap() {

    this.styleMapRow = new HashMap();
    this.mapReplace = new HashMap();

    this.mCell = new HashMap();

    // Infos societe
    SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
    this.mCell.put("B1", rowSociete.getObject("TYPE") + " " + rowSociete.getObject("NOM"));
    SQLRow rowAdrSociete = tableAdresseCommon.getRow(rowSociete.getInt("ID_ADRESSE_COMMON"));
    this.mCell.put("B2", rowAdrSociete.getObject("RUE"));
    this.mCell.put("B3", rowAdrSociete.getString("CODE_POSTAL") + " " + rowAdrSociete.getString("VILLE"));

    this.mCell.put("D5", rowSociete.getObject("NUM_SIRET"));
    this.mCell.put("D6", rowSociete.getObject("NUM_APE"));
    this.mapReplace.put("D8", rowSociete.getObject("NUMERO_URSSAF"));

    // Infos Salarie
    SQLRow rowSal = tableSalarie.getRow(this.row.getInt("ID_SALARIE"));
    SQLRow rowEtatCivil = tableEtatCivil.getRow(rowSal.getInt("ID_ETAT_CIVIL"));
    this.mCell.put("G8", rowSal.getObject("NOM") + " " + rowSal.getObject("PRENOM"));
    SQLRow rowAdrSal = tableAdresse.getRow(rowEtatCivil.getInt("ID_ADRESSE"));
    this.mCell.put("G9", rowAdrSal.getObject("RUE"));
    this.mCell.put("G11", rowAdrSal.getString("CODE_POSTAL") + " " + rowAdrSal.getString("VILLE"));

    this.mCell.put("D13", rowEtatCivil.getObject("NUMERO_SS"));

    SQLRow rowInfosPaye = tableInfosPaye.getRow(rowSal.getInt("ID_INFOS_SALARIE_PAYE"));
    SQLRow rowContrat = tableContrat.getRow(rowInfosPaye.getInt("ID_CONTRAT_SALARIE"));

    if (this.row.getString("NATURE_EMPLOI").trim().length() == 0) {
        this.mCell.put("D14", rowContrat.getObject("NATURE"));
    } else {//  ww  w. j a  v  a  2  s.c  o m
        this.mCell.put("D14", this.row.getString("NATURE_EMPLOI"));
    }

    SQLRow rowCC;
    if (this.row.getInt("ID_IDCC") > 1) {
        rowCC = tableConventionC.getRow(this.row.getInt("ID_IDCC"));
    } else {
        rowCC = tableConventionC.getRow(rowInfosPaye.getInt("ID_IDCC"));
    }
    this.mCell.put("D15", rowCC.getString("NOM"));

    // Bulletin du
    // Bulletin de paie du
    Date du = (Date) this.row.getObject("DU");
    Date au = (Date) this.row.getObject("AU");
    this.mCell.put("F1", "Bulletin de paie du " + dateFormat.format(du) + " au " + dateFormat.format(au));

    // Paiement le
    SQLRow rowRegl;
    if (this.row.getInt("ID_REGLEMENT_PAYE") <= 1) {
        rowRegl = tableReglementPaye.getRow(rowSal.getInt("ID_REGLEMENT_PAYE"));
    } else {
        rowRegl = tableReglementPaye.getRow(this.row.getInt("ID_REGLEMENT_PAYE"));
    }
    SQLRow rowModeRegl = tableModeRegl.getRow(rowRegl.getInt("ID_MODE_REGLEMENT_PAYE"));

    Calendar c = Calendar.getInstance();

    c.set(Calendar.MONTH, this.row.getInt("ID_MOIS") - 2);
    c.set(Calendar.YEAR, Integer.parseInt(this.row.getString("ANNEE")));

    if (rowRegl.getInt("LE") != 31) {

        c.set(Calendar.MONTH, c.get(Calendar.MONTH) + 1);
    }

    int max = c.getActualMaximum(Calendar.DAY_OF_MONTH);
    int day = Math.min(rowRegl.getInt("LE"), max);

    c.set(Calendar.DAY_OF_MONTH, day);

    this.mCell.put("H3", dateFormat.format(c.getTime()));
    this.mCell.put("I3", "Par " + rowModeRegl.getObject("NOM"));

    // Congs
    // "G3";
    SQLRow rowConges;
    if (this.row.getInt("ID_CUMULS_CONGES") <= 1) {
        rowConges = tableCumulsConges.getRow(rowSal.getInt("ID_CUMULS_CONGES"));
    } else {
        rowConges = tableCumulsConges.getRow(this.row.getInt("ID_CUMULS_CONGES"));
    }

    SQLRow rowVarSal;
    if (this.row.getInt("ID_VARIABLE_SALARIE") <= 1) {
        rowVarSal = tableVarPeriode.getRow(rowSal.getInt("ID_VARIABLE_SALARIE"));
    } else {
        rowVarSal = tableVarPeriode.getRow(this.row.getInt("ID_VARIABLE_SALARIE"));
    }

    float congesPris = rowVarSal.getFloat("CONGES_PRIS");
    float congesRestant = rowConges.getFloat("RESTANT") - congesPris;
    float congesAcquis = rowConges.getFloat("ACQUIS") + this.row.getFloat("CONGES_ACQUIS");
    this.mCell.put("G14", new Float(congesPris));
    this.mCell.put("H14", new Float(congesRestant));
    this.mCell.put("I14", new Float(congesAcquis));

    // Element Devis
    SQLSelect selElt = new SQLSelect(base);
    selElt.addSelect(tableFicheElt.getField("ID"));
    selElt.setWhere(tableFicheElt.getField("ID_FICHE_PAYE"), "=", this.row.getID());

    String req = selElt.asString() + " ORDER BY \"FICHE_PAYE_ELEMENT\".\"POSITION\"";
    List l = (List) base.getDataSource().execute(req, new ArrayListHandler());
    int pos = 20;
    for (Iterator i = l.iterator(); i.hasNext();) {
        Object[] o = (Object[]) i.next();
        SQLRow rowTmp = tableFicheElt.getRow(Integer.parseInt(o[0].toString()));

        if (rowTmp.getBoolean("IMPRESSION") && rowTmp.getBoolean("IN_PERIODE")) {

            Object nomTmp = rowTmp.getObject("NOM");
            this.mCell.put("B" + pos, nomTmp);

            // Base
            Object baseTmp = rowTmp.getObject("NB_BASE");

            if (baseTmp != null) {
                float base = ((Float) baseTmp).floatValue();
                if (base != 0) {
                    this.mCell.put("E" + pos, baseTmp);
                } else {
                    this.mCell.put("E" + pos, "");
                }
            } else {
                this.mCell.put("E" + pos, baseTmp);
            }

            // Taux Sal
            Object tauxSalTmp = rowTmp.getObject("TAUX_SAL");

            if (tauxSalTmp != null) {
                float tauxSal = ((Float) tauxSalTmp).floatValue();
                if (tauxSal != 0) {
                    this.mCell.put("F" + pos, tauxSalTmp);
                } else {
                    this.mCell.put("F" + pos, "");
                }
            } else {
                this.mCell.put("F" + pos, tauxSalTmp);
            }

            // Montant Sal Aj
            Object montantSalAjTmp = rowTmp.getObject("MONTANT_SAL_AJ");
            if (montantSalAjTmp != null) {
                float montantSalAj = ((Float) montantSalAjTmp).floatValue();
                if (montantSalAj != 0) {
                    this.mCell.put("G" + pos, montantSalAjTmp);
                } else {
                    this.mCell.put("G" + pos, "");
                }
            } else {
                this.mCell.put("G" + pos, montantSalAjTmp);
            }

            // Montant Sal ded
            Object montantSalDedTmp = rowTmp.getObject("MONTANT_SAL_DED");
            if (montantSalDedTmp != null) {
                float montantSalDed = ((Float) montantSalDedTmp).floatValue();
                if (montantSalDed != 0) {
                    this.mCell.put("H" + pos, montantSalDedTmp);
                } else {
                    this.mCell.put("H" + pos, "");
                }
            } else {
                this.mCell.put("H" + pos, montantSalDedTmp);
            }

            // Taux Pat
            Object tauxPatTmp = rowTmp.getObject("TAUX_PAT");
            if (tauxPatTmp != null) {
                float tauxPat = ((Float) tauxPatTmp).floatValue();
                if (tauxPat != 0) {
                    this.mCell.put("I" + pos, tauxPatTmp);
                } else {
                    this.mCell.put("I" + pos, "");
                }
            } else {
                this.mCell.put("I" + pos, tauxPatTmp);
            }

            // Montant Pat
            Object montantPatTmp = rowTmp.getObject("MONTANT_PAT");
            if (montantPatTmp != null) {
                float montantPat = ((Float) montantPatTmp).floatValue();
                if (montantPat != 0) {
                    this.mCell.put("J" + pos, montantPatTmp);
                } else {
                    this.mCell.put("J" + pos, "");
                }
            } else {
                this.mCell.put("J" + pos, montantPatTmp);
            }

            if (rowTmp.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_COMM")) {
                this.mapStyleRow.put(new Integer(pos), "Titre 1");
            } else {
                this.mapStyleRow.put(new Integer(pos), "Normal");
            }

            pos++;
        }
    }

    // Totaux
    float netApayerCumul = this.row.getFloat("NET_A_PAYER");
    float salBrutCumul = this.row.getFloat("SAL_BRUT");
    float cotSalCumul = this.row.getFloat("COT_SAL");
    float cotPatCumul = this.row.getFloat("COT_PAT");
    float netImpCumul = this.row.getFloat("NET_IMP");
    this.mCell.put("I61", this.row.getObject("NET_A_PAYER"));
    this.mCell.put("D61", this.row.getObject("SAL_BRUT"));
    this.mCell.put("E61", this.row.getObject("COT_SAL"));
    this.mCell.put("F61", this.row.getObject("COT_PAT"));
    this.mCell.put("H61", this.row.getObject("NET_IMP"));

    SQLRow rowCumulsPaye;

    if (this.row.getInt("ID_CUMULS_PAYE") == 1) {
        rowCumulsPaye = tableCumulsPaye.getRow(rowSal.getInt("ID_CUMULS_PAYE"));
    } else {
        rowCumulsPaye = tableCumulsPaye.getRow(this.row.getInt("ID_CUMULS_PAYE"));
    }

    netApayerCumul += rowCumulsPaye.getFloat("NET_A_PAYER_C");
    salBrutCumul += rowCumulsPaye.getFloat("SAL_BRUT_C");
    cotSalCumul += rowCumulsPaye.getFloat("COT_SAL_C");
    cotPatCumul += rowCumulsPaye.getFloat("COT_PAT_C");
    netImpCumul += rowCumulsPaye.getFloat("NET_IMP_C");

    this.mCell.put("D62", new Float(salBrutCumul));
    this.mCell.put("E62", new Float(cotSalCumul));
    this.mCell.put("F62", new Float(cotPatCumul));
    this.mCell.put("H62", new Float(netImpCumul));
}

From source file:org.openconcerto.erp.core.humanresources.payroll.report.LivrePayeSheet.java

protected void createMap() {

    this.mapReplace = new HashMap();
    this.mCell = new HashMap();
    this.mapStyleRow = new HashMap();

    SQLSelect sel = new SQLSelect(base);
    sel.addSelect(tableFichePaye.getField("ID"));
    sel.addSelect(tableFichePayeElement.getField("ID"));
    sel.addSelect(tableSalarie.getField("ID"));

    Where w = (new Where(tableFichePayeElement.getField("ID_FICHE_PAYE"), "=", tableFichePaye.getField("ID")));
    Where w2 = (new Where(tableFichePaye.getField("ID_SALARIE"), "=", tableSalarie.getField("ID")));
    Where w3 = (new Where(tableFichePaye.getField("ID_MOIS"), new Integer(this.moisDu),
            new Integer(this.moisAu)));
    Where w4 = (new Where(tableFichePaye.getField("ANNEE"), "=", new Integer(this.annee)));
    Where w5 = (new Where(tableFichePaye.getField("VALIDE"), "=", Boolean.TRUE));

    sel.setWhere(w);//from   ww  w .  j av a 2 s . c  o m
    sel.andWhere(w2);
    sel.andWhere(w3);
    sel.andWhere(w4);
    sel.andWhere(w5);
    String req = sel.asString();

    System.err.println(req);

    // Liste des rubriques de chaque salaries
    List l = (List) base.getDataSource().execute(req, new ArrayListHandler());

    // Association idSal, map Value(idRub, val)
    Map mapSalarieBrut = new HashMap();
    Map mapSalarieNet = new HashMap();
    Map mapSalarieCot = new HashMap();

    Map mapTotalCot = new HashMap();
    Map mapTotalNet = new HashMap();
    Map mapTotalbrut = new HashMap();

    Map mapRubriqueBrut = new HashMap();
    Map mapRubriqueNet = new HashMap();
    Map mapRubriqueCot = new HashMap();
    Map mapSal = new HashMap();

    // Cumuls des rubriques par salaries
    for (int i = 0; i < l.size(); i++) {
        Object[] tmp = (Object[]) l.get(i);
        // int idFiche = new Integer(tmp[0].toString()).intValue();
        int idFicheElt = Integer.parseInt(tmp[1].toString());
        int idSal = Integer.parseInt(tmp[2].toString());

        Map mapValue = new HashMap();
        Map mapTotal = new HashMap();

        // SQLRow rowFiche = tableFichePaye.getRow(idFiche);
        SQLRow rowFicheElt = tableFichePayeElement.getRow(idFicheElt);

        mapSal.put(new Integer(idSal), "");

        if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_BRUT")) {

            mapRubriqueBrut.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
            mapTotal = mapTotalbrut;
            if (mapSalarieBrut.get(new Integer(idSal)) == null) {
                mapSalarieBrut.put(new Integer(idSal), mapValue);
            } else {
                mapValue = (Map) mapSalarieBrut.get(new Integer(idSal));
            }
        } else {
            if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_COTISATION")) {
                mapRubriqueCot.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
                mapTotal = mapTotalCot;
                if (mapSalarieCot.get(new Integer(idSal)) == null) {
                    mapSalarieCot.put(new Integer(idSal), mapValue);
                } else {
                    mapValue = (Map) mapSalarieCot.get(new Integer(idSal));
                }
            } else {
                if (rowFicheElt.getString("SOURCE").equalsIgnoreCase("RUBRIQUE_NET")) {
                    mapRubriqueNet.put(new Integer(rowFicheElt.getInt("IDSOURCE")), "");
                    mapTotal = mapTotalNet;
                    if (mapSalarieNet.get(new Integer(idSal)) == null) {
                        mapSalarieNet.put(new Integer(idSal), mapValue);
                    } else {
                        mapValue = (Map) mapSalarieNet.get(new Integer(idSal));
                    }
                }
            }
        }

        if (rowFicheElt.getObject("MONTANT_SAL_AJ") != null) {
            Object o = mapValue.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
            Object oTot = mapTotal.get(new Integer(rowFicheElt.getInt("IDSOURCE")));

            float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
            float montantTotal = (oTot == null) ? 0.0F : ((Float) oTot).floatValue();
            montant += rowFicheElt.getFloat("MONTANT_SAL_AJ");
            montantTotal += rowFicheElt.getFloat("MONTANT_SAL_AJ");

            mapValue.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montant));
            mapTotal.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montantTotal));
        }
        if (rowFicheElt.getObject("MONTANT_SAL_DED") != null) {
            Object o = mapValue.get(new Integer(rowFicheElt.getInt("IDSOURCE")));
            Object oTot = mapTotal.get(new Integer(rowFicheElt.getInt("IDSOURCE")));

            float montant = (o == null) ? 0.0F : ((Float) o).floatValue();
            float montantTot = (oTot == null) ? 0.0F : ((Float) oTot).floatValue();
            montant -= rowFicheElt.getFloat("MONTANT_SAL_DED");
            montantTot -= rowFicheElt.getFloat("MONTANT_SAL_DED");

            mapValue.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montant));
            mapTotal.put(new Integer(rowFicheElt.getInt("IDSOURCE")), new Float(montantTot));
        }
    }

    // Dump
    /*
     * for (int j = 0; j < mapSalarieBrut.keySet().size(); j++) {
     * System.err.println(mapSalarieBrut.get(mapSalarieBrut.keySet().toArray()[j])); }
     */

    // Fill
    int posLine = 1;
    int firstLine = 1;

    System.err.println("NB Sal = " + mapSal.keySet().size());
    System.err.println("NB Pages = " + Math.ceil((double) (mapSal.keySet().size() + 1) / nbCol));
    for (int n = 0; n < Math.ceil((double) (mapSal.keySet().size() + 1) / nbCol); n++) {

        // entete
        makeEntete(posLine);
        posLine += (debutFill - 1);

        int numFirstSal = (n * nbCol);

        if (numFirstSal < mapSal.keySet().size()) {
            SQLRow rowSal = tableSalarie.getRow(((Integer) mapSal.keySet().toArray()[numFirstSal]).intValue());
            this.mCell.put("B" + (posLine - 2), rowSal.getObject("NOM"));
            this.mCell.put("B" + (posLine - 1), rowSal.getObject("PRENOM"));
        } else {
            if (numFirstSal == mapSal.keySet().size()) {
                System.err.println("Cumuls B");
                this.mCell.put("B" + (posLine - 2), "Cumuls");
                this.mCell.put("B" + (posLine - 1), "");
            }
        }
        if (numFirstSal + 1 < mapSal.keySet().size()) {
            SQLRow rowSal = tableSalarie
                    .getRow(((Integer) mapSal.keySet().toArray()[numFirstSal + 1]).intValue());
            this.mCell.put("C" + (posLine - 2), rowSal.getObject("NOM"));
            this.mCell.put("C" + (posLine - 1), rowSal.getObject("PRENOM"));
        } else {
            if (numFirstSal + 1 == mapSal.keySet().size()) {
                System.err.println("Cumuls C");
                this.mCell.put("C" + (posLine - 2), "Cumuls");
                this.mCell.put("C" + (posLine - 1), "");
            }
        }
        if (numFirstSal + 2 < mapSal.keySet().size()) {
            SQLRow rowSal = tableSalarie
                    .getRow(((Integer) mapSal.keySet().toArray()[numFirstSal + 2]).intValue());
            this.mCell.put("D" + (posLine - 2), rowSal.getObject("NOM"));
            this.mCell.put("D" + (posLine - 1), rowSal.getObject("PRENOM"));
        } else {
            if (numFirstSal + 2 == mapSal.keySet().size()) {
                System.err.println("Cumuls D");
                this.mCell.put("D" + (posLine - 2), "Cumuls");
                this.mCell.put("D" + (posLine - 1), "");
            }
        }
        for (int i = 0; i < mapRubriqueBrut.keySet().size(); i++) {

            int idRub = ((Number) mapRubriqueBrut.keySet().toArray()[i]).intValue();
            SQLRow rowRub = tableRubBrut.getRow(idRub);

            this.mCell.put("A" + posLine, rowRub.getObject("NOM"));

            this.mCell.put("B" + posLine, fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal, mapTotalbrut));
            this.mCell.put("C" + posLine,
                    fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 1, mapTotalbrut));
            this.mCell.put("D" + posLine,
                    fillLine(mapSalarieBrut, idRub, mapSal, numFirstSal + 2, mapTotalbrut));

            posLine++;
        }

        for (int i = 0; i < mapRubriqueCot.keySet().size(); i++) {

            int idRub = ((Number) mapRubriqueCot.keySet().toArray()[i]).intValue();
            SQLRow rowRub = tableRubCot.getRow(idRub);

            this.mCell.put("A" + posLine, rowRub.getObject("NOM"));

            this.mCell.put("B" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal, mapTotalCot));
            this.mCell.put("C" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 1, mapTotalCot));
            this.mCell.put("D" + posLine, fillLine(mapSalarieCot, idRub, mapSal, numFirstSal + 2, mapTotalCot));

            posLine++;
        }

        for (int i = 0; i < mapRubriqueNet.keySet().size(); i++) {

            int idRub = ((Number) mapRubriqueNet.keySet().toArray()[i]).intValue();
            SQLRow rowRub = tableRubNet.getRow(idRub);

            this.mCell.put("A" + posLine, rowRub.getObject("NOM"));

            this.mCell.put("B" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal, mapTotalNet));
            this.mCell.put("C" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 1, mapTotalNet));
            this.mCell.put("D" + posLine, fillLine(mapSalarieNet, idRub, mapSal, numFirstSal + 2, mapTotalNet));

            posLine++;
        }

        // pied de page
        posLine = firstLine + endFill - 1;
        posLine += 2;
        makeBasPage(posLine);

        posLine++;
        firstLine = posLine;
    }

    this.nbPage = new Double(Math.ceil((double) (mapSal.keySet().size() + 1) / (nbCol))).intValue();

    System.err.println("Nombre de page " + this.nbPage);

    // on conserve la page d'origine du model

    if (this.nbPage > 0) {
        this.nbPage--;
    }
}

From source file:org.openconcerto.erp.core.humanresources.payroll.ui.ProfilPayeModel.java

private void doSelectID(final int idProfil) {
    this.idProfil = idProfil;
    this.vectRowElt.clear();

    final SQLSelect selAllIDProfilElt = new SQLSelect(Configuration.getInstance().getBase());
    selAllIDProfilElt.addSelect(TABLE_PROFIL.getField("ID"));
    selAllIDProfilElt.addSelect(TABLE_PROFIL.getField("POSITION"));
    selAllIDProfilElt.setWhere(new Where(TABLE_PROFIL.getField("ID_PROFIL_PAYE"), "=", this.idProfil));
    selAllIDProfilElt.addRawOrder("\"PROFIL_PAYE_ELEMENT\".\"POSITION\"");

    final String reqAllIDProfilElt = selAllIDProfilElt.asString();
    final Object[] objIDProfilElt = ((List) Configuration.getInstance().getBase().getDataSource()
            .execute(reqAllIDProfilElt, new ArrayListHandler())).toArray();

    for (int i = 0; i < objIDProfilElt.length; i++) {
        final SQLRow rowTmp = TABLE_PROFIL
                .getRow(Integer.parseInt((((Object[]) objIDProfilElt[i])[0].toString())));
        final SQLRowValues rowValsTmp = new SQLRowValues(TABLE_PROFIL);
        rowValsTmp.loadAbsolutelyAll(rowTmp);
        this.vectRowElt.add(rowValsTmp);
    }//from   w w  w  . j  a v a2 s. c om

}

From source file:org.openconcerto.erp.core.reports.history.ui.HistoriqueClientBilanPanel.java

public synchronized void updateVFData(final List<Integer> listId, final int idClient) {
    ComptaPropsConfiguration.getInstanceCompta().getNonInteractiveSQLExecutor().execute(new Runnable() {

        @Override//from  w  w  w.  j  ava2 s.  c o  m
        public void run() {
            final SQLBase base = ((ComptaPropsConfiguration) ComptaPropsConfiguration.getInstance())
                    .getSQLBaseSociete();

            long valueTotal = 0;
            if (listId != null && listId.size() > 0) {
                final SQLSelect select = new SQLSelect();
                final SQLTable table = base.getTable("SAISIE_VENTE_FACTURE");
                select.addSelect(table.getField("T_HT"), "SUM");
                select.setWhere(new Where(table.getKey(), listId));
                final Number n = (Number) base.getDBSystemRoot().getDataSource()
                        .executeScalar(select.asString());
                if (n != null) {
                    valueTotal = n.longValue();
                }
            }

            final Map<Object, Date> mapDateFact = new HashMap<Object, Date>();
            // On recupere les dates de facturations VF
            final SQLSelect selDateFacture = new SQLSelect();
            final SQLTable tableFacture = base.getTable("SAISIE_VENTE_FACTURE");
            final SQLTable tableEncaisse = base.getTable("ENCAISSER_MONTANT");
            final SQLTable tableEcheance = base.getTable("ECHEANCE_CLIENT");
            final SQLTable tableMvt = base.getTable("MOUVEMENT");
            selDateFacture.addSelect(tableFacture.getField("DATE"));
            selDateFacture.addSelect(tableMvt.getField("ID_PIECE"));
            Where w = new Where(tableFacture.getField("ID_MOUVEMENT"), "=", tableMvt.getKey());
            if (idClient > 1) {
                w = w.and(new Where(tableFacture.getField("ID_CLIENT"), "=", idClient));
            }
            selDateFacture.setWhere(w);

            addDatesToMap(base, selDateFacture, mapDateFact);

            // On recupere les dates de facturations
            final SQLSelect selDateFactureC = new SQLSelect();
            final SQLTable tableComptoir = base.getTable("SAISIE_VENTE_COMPTOIR");
            selDateFactureC.addSelect(tableComptoir.getField("DATE"));
            selDateFactureC.addSelect(tableMvt.getField("ID_PIECE"));
            Where wC = new Where(tableComptoir.getField("ID_MOUVEMENT"), "=", tableMvt.getKey());
            if (idClient > 1) {
                wC = wC.and(new Where(tableComptoir.getField("ID_CLIENT"), "=", idClient));
            }
            selDateFactureC.setWhere(wC);
            addDatesToMap(base, selDateFactureC, mapDateFact);

            // On recupere les dates d'encaissement
            final SQLSelect selDateEncaisse = new SQLSelect();
            selDateEncaisse.addSelect(tableEncaisse.getField("DATE"));
            selDateEncaisse.addSelect(tableMvt.getField("ID_PIECE"));
            selDateEncaisse.addSelect(tableEcheance.getField("ID"));
            Where wEncaisse = new Where(tableEcheance.getField("ID"), "=",
                    tableEncaisse.getField("ID_ECHEANCE_CLIENT"));
            wEncaisse = wEncaisse
                    .and(new Where(tableEcheance.getField("ID_MOUVEMENT"), "=", tableMvt.getField("ID")));
            wEncaisse = wEncaisse.and(new Where(tableEcheance.getArchiveField(), "=", 1));

            if (idClient > 1) {
                wEncaisse = wEncaisse.and(new Where(tableEcheance.getField("ID_CLIENT"), "=", idClient));
            }

            selDateEncaisse.setWhere(wEncaisse);
            selDateEncaisse.setArchivedPolicy(SQLSelect.BOTH);

            final List<Object[]> lDateEncaisse = (List<Object[]>) base.getDataSource()
                    .execute(selDateEncaisse.asString(), new ArrayListHandler());
            final Map<Object, Date> mapDateEncaisse = new HashMap<Object, Date>();
            for (int i = 0; i < lDateEncaisse.size(); i++) {
                final Object[] tmp = lDateEncaisse.get(i);
                final Date d2 = (Date) tmp[0];
                final Object d = mapDateEncaisse.get(tmp[1]);
                if (d != null) {
                    final Date d1 = (Date) d;
                    if (d1.before(d2)) {
                        mapDateEncaisse.put(tmp[1], d2);
                    }
                } else {
                    mapDateEncaisse.put(tmp[1], d2);
                }
            }

            // Calcul moyenne
            int cpt = 0;
            int day = 0;
            final Calendar cal1 = Calendar.getInstance();
            final Calendar cal2 = Calendar.getInstance();
            for (final Iterator i = mapDateFact.keySet().iterator(); i.hasNext();) {
                final Object key = i.next();
                final Date dFact = mapDateFact.get(key);
                final Date dEncaisse = mapDateEncaisse.get(key);

                if (dFact != null && dEncaisse != null) {
                    cpt++;
                    cal1.setTime(dFact);
                    cal2.setTime(dEncaisse);
                    cal1.set(Calendar.HOUR, 0);
                    cal1.set(Calendar.MINUTE, 0);
                    cal1.set(Calendar.SECOND, 0);
                    cal1.set(Calendar.MILLISECOND, 0);
                    cal2.set(Calendar.HOUR, 0);
                    cal2.set(Calendar.MINUTE, 0);
                    cal2.set(Calendar.SECOND, 0);
                    cal2.set(Calendar.MILLISECOND, 0);
                    day += (cal2.getTime().getTime() - cal1.getTime().getTime()) / 86400000;
                }
            }

            setPoucentageVentes(cpt == 0 ? 0 : day / cpt);
            setTotalVentesFacture(valueTotal);
            setNbVentesFacture(listId == null ? 0 : listId.size());
            updateLabels();
        }
    });
}

From source file:org.openconcerto.erp.core.reports.history.ui.HistoriqueClientBilanPanel.java

private void addDatesToMap(final SQLBase base, final SQLSelect selDateFacture, final Map mapDateFact) {
    final List<Object[]> lDateFact = (List<Object[]>) base.getDataSource().execute(selDateFacture.asString(),
            new ArrayListHandler());

    final int size = lDateFact.size();
    for (int i = 0; i < size; i++) {
        final Object[] tmp = lDateFact.get(i);
        mapDateFact.put(tmp[1], tmp[0]);
    }//  w  w  w.  j a  va2  s. c o m
}

From source file:org.openconcerto.erp.core.sales.credit.component.AvoirClientSQLComponent.java

@Override
public void update() {
    if (this.textNumero.checkValidation()) {
        super.update();
        try {//  w w  w .  j a  v  a2 s  .  c  o  m
            this.table.updateField("ID_AVOIR_CLIENT", getSelectedID());

            // On efface les anciens mouvements de stocks
            SQLRow row = getTable().getRow(getSelectedID());
            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));

            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());
                }
            }

            SQLRowValues rowVals2 = getTable().getRow(getSelectedID()).createUpdateRow();
            Long l2 = rowVals2.getLong("MONTANT_SOLDE");
            Long l3 = rowVals2.getLong("MONTANT_TTC");

            rowVals2.put("MONTANT_RESTANT", l3 - l2);
            rowVals2.update();

            // On met  jour le stock
            // updateStock(getSelectedID());

            int idMvt = row.getInt("ID_MOUVEMENT");

            // on supprime tout ce qui est li  la facture d'avoir
            System.err.println("Archivage des fils");
            EcritureSQLElement eltEcr = (EcritureSQLElement) Configuration.getInstance().getDirectory()
                    .getElement("ECRITURE");
            eltEcr.archiveMouvementProfondeur(idMvt, false);

            GenerationMvtAvoirClient gen = new GenerationMvtAvoirClient(getSelectedID(), idMvt);
            gen.genereMouvement();

            createAvoirClient(row);
        } catch (Exception e) {
            ExceptionHandler.handle("Erreur de mise  jour de l'avoir", e);
        }
    } else {
        ExceptionHandler.handle("Impossible de modifier, numro d'avoir existant.");
        Object root = SwingUtilities.getRoot(this);
        if (root instanceof EditFrame) {
            EditFrame frame = (EditFrame) root;
            frame.getPanel().setAlwaysVisible(true);
        }
        return;
    }
}

From source file:org.openconcerto.erp.core.sales.credit.element.AvoirClientSQLElement.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));/*from ww w. ja va2s.  c  om*/

    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.sales.invoice.component.SaisieVenteFactureSQLComponent.java

public int commit(SQLRow order) {

    int idSaisieVF = -1;
    long lFactureOld = 0;
    SQLRow rowFactureOld = null;/*from   w  w  w  .  j  a v a 2s . co  m*/
    SQLRow rowFacture = null;
    SQLElement eltMvtStock = Configuration.getInstance().getDirectory().getElement("MOUVEMENT_STOCK");
    if (this.textNumeroUnique.checkValidation()) {
        try {
            if (getMode() == Mode.INSERTION) {
                idSaisieVF = super.insert(order);
                rowFacture = getTable().getRow(idSaisieVF);
                // incrmentation du numro auto
                if (NumerotationAutoSQLElement
                        .getNextNumero(SaisieVenteFactureSQLElement.class, rowFacture.getDate("DATE").getTime())
                        .equalsIgnoreCase(this.textNumeroUnique.getText().trim())) {
                    SQLRowValues rowVals = new SQLRowValues(this.tableNum);

                    String labelNumberFor = NumerotationAutoSQLElement
                            .getLabelNumberFor(SaisieVenteFactureSQLElement.class);
                    int val = this.tableNum.getRow(2).getInt(labelNumberFor);
                    val++;
                    rowVals.put(labelNumberFor, Integer.valueOf(val));
                    rowVals.update(2);
                }
            } else {
                if (JOptionPane.showConfirmDialog(this,
                        "Attention en modifiant cette facture, vous supprimerez les chques et les chances associs. Continuer?",
                        "Modification de facture", JOptionPane.YES_NO_OPTION) == JOptionPane.YES_OPTION) {
                    SQLPreferences prefs = SQLPreferences.getMemCached(getTable().getDBRoot());
                    if (prefs.getBoolean(GestionArticleGlobalPreferencePanel.STOCK_FACT, true)) {
                        // On efface les anciens mouvements de stocks
                        SQLSelect sel = new SQLSelect();
                        sel.addSelect(eltMvtStock.getTable().getField("ID"));
                        Where w = new Where(eltMvtStock.getTable().getField("IDSOURCE"), "=", getSelectedID());
                        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);
                                try {
                                    eltMvtStock.archive(((Number) tmp[0]).intValue());
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                    // On recupere l'ancien total HT
                    rowFactureOld = this.getTable().getRow(getSelectedID());
                    lFactureOld = ((Number) rowFactureOld.getObject("T_HT")).longValue();

                    super.update();

                    idSaisieVF = getSelectedID();
                } else {
                    // Annulation par l'utilisateur
                    return idSaisieVF;
                }
            }

            rowFacture = getTable().getRow(idSaisieVF);
            final ComptaPropsConfiguration comptaPropsConfiguration = ((ComptaPropsConfiguration) Configuration
                    .getInstance());

            // Mise  jour des tables lies
            this.tableFacture.updateField("ID_SAISIE_VENTE_FACTURE", idSaisieVF);

            createDocument(rowFacture);

            int idMvt = -1;
            if (!this.checkPrevisionnelle.isSelected()) {
                if (getMode() == Mode.MODIFICATION) {
                    idMvt = rowFacture.getInt("ID_MOUVEMENT");
                    // on supprime tout ce qui est li  la facture
                    System.err.println("Archivage des fils");
                    EcritureSQLElement eltEcr = (EcritureSQLElement) Configuration.getInstance().getDirectory()
                            .getElement("ECRITURE");
                    eltEcr.archiveMouvementProfondeur(idMvt, false);
                }

                System.err.println("Regeneration des ecritures");
                if (idMvt > 1) {
                    new GenerationMvtSaisieVenteFacture(idSaisieVF, idMvt);
                } else {
                    new GenerationMvtSaisieVenteFacture(idSaisieVF);
                }
                System.err.println("Fin regeneration");

                // Mise  jour des stocks

                updateStock(idSaisieVF);

                // On retire l'avoir
                if (rowFactureOld != null && rowFactureOld.getInt("ID_AVOIR_CLIENT") > 1) {

                    SQLRow rowAvoir = rowFactureOld.getForeignRow("ID_AVOIR_CLIENT");

                    Long montantSolde = (Long) rowAvoir.getObject("MONTANT_SOLDE");
                    Long avoirTTC = (Long) rowFactureOld.getObject("T_AVOIR_TTC");

                    long montant = montantSolde - avoirTTC;
                    if (montant < 0) {
                        montant = 0;
                    }

                    SQLRowValues rowVals = rowAvoir.createEmptyUpdateRow();

                    // Sold
                    rowVals.put("SOLDE", Boolean.FALSE);
                    rowVals.put("MONTANT_SOLDE", montant);
                    Long restant = (Long) rowAvoir.getObject("MONTANT_TTC") - montantSolde;
                    rowVals.put("MONTANT_RESTANT", restant);

                    rowVals.update();

                }

                final int idAvoir = rowFacture.getInt("ID_AVOIR_CLIENT");
                // on solde l'avoir
                if (idAvoir > 1) {

                    SQLRow rowAvoir = rowFacture.getForeignRow("ID_AVOIR_CLIENT");

                    Long montantTTC = (Long) rowAvoir.getObject("MONTANT_TTC");
                    Long montantSolde = (Long) rowAvoir.getObject("MONTANT_SOLDE");
                    Long factTTC = (Long) rowFacture.getObject("T_TTC");

                    long restant = montantTTC - montantSolde;

                    SQLRowValues rowVals = rowAvoir.createEmptyUpdateRow();
                    final long l2 = factTTC - restant;
                    // Sold
                    if (l2 >= 0) {
                        rowVals.put("SOLDE", Boolean.TRUE);
                        rowVals.put("MONTANT_SOLDE", montantTTC);
                        rowVals.put("MONTANT_RESTANT", 0);
                    } else {
                        // Il reste encore de l'argent pour l'avoir
                        final long m = montantSolde + factTTC;
                        rowVals.put("MONTANT_SOLDE", m);
                        rowVals.put("MONTANT_RESTANT", montantTTC - m);
                    }

                    rowVals.update();

                }

            }
        } catch (Exception e) {
            ExceptionHandler.handle("", e);
        }
    } else {
        ExceptionHandler.handle("Impossible d'ajouter, numro de facture existant.");
        Object root = SwingUtilities.getRoot(this);
        if (root instanceof EditFrame) {
            EditFrame frame = (EditFrame) root;
            frame.getPanel().setAlwaysVisible(true);
        }
    }
    return idSaisieVF;
}

From source file:org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement.java

@Override
protected void archive(SQLRow row, boolean cutLinks) throws SQLException {

    // On retire l'avoir
    if (row.getInt("ID_AVOIR_CLIENT") > 1) {
        SQLElement eltAvoir = Configuration.getInstance().getDirectory().getElement("AVOIR_CLIENT");
        SQLRow rowAvoir = eltAvoir.getTable().getRow(row.getInt("ID_AVOIR_CLIENT"));

        Long montantSolde = (Long) rowAvoir.getObject("MONTANT_SOLDE");

        Long avoirTTC = (Long) row.getObject("T_AVOIR_TTC");

        long montant = montantSolde - avoirTTC;
        if (montant < 0) {
            montant = 0;/*w w w  .  ja  v  a2s  . c om*/
        }

        SQLRowValues rowVals = rowAvoir.createEmptyUpdateRow();

        // Sold
        rowVals.put("SOLDE", Boolean.FALSE);
        rowVals.put("MONTANT_SOLDE", montant);
        Long restant = (Long) rowAvoir.getObject("MONTANT_TTC") - montantSolde;
        rowVals.put("MONTANT_RESTANT", restant);
        try {
            rowVals.update();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    super.archive(row, cutLinks);

    SQLPreferences prefs = new SQLPreferences(getTable().getDBRoot());
    if (prefs.getBoolean(GestionArticleGlobalPreferencePanel.STOCK_FACT, true)) {

        // Mise  jour des stocks
        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));

        @SuppressWarnings("rawtypes")
        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.sales.order.element.CommandeClientSQLElement.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();
    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));//from w  w w.j a v  a2 s .  co  m

    @SuppressWarnings("rawtypes")
    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());
        }
    }
}