Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/common/element/NumerotationAutoSQLElement.java - Rev 180
Rev 156 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.** The contents of this file are subject to the terms of the GNU General Public License Version 3* only ("GPL"). You may not use this file except in compliance with the License. You can obtain a* copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific* language governing permissions and limitations under the License.** When distributing the software, include this License Header Notice in each file.*/package org.openconcerto.erp.core.common.element;import org.openconcerto.erp.config.ComptaPropsConfiguration;import org.openconcerto.erp.core.common.image.ImageIconWarning;import org.openconcerto.erp.core.customerrelationship.customer.element.CourrierClientSQLElement;import org.openconcerto.erp.core.customerrelationship.customer.element.CustomerSQLElement;import org.openconcerto.erp.core.customerrelationship.customer.element.RelanceSQLElement;import org.openconcerto.erp.core.humanresources.payroll.element.SalarieSQLElement;import org.openconcerto.erp.core.sales.credit.element.AvoirClientSQLElement;import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;import org.openconcerto.erp.core.sales.order.element.CommandeClientSQLElement;import org.openconcerto.erp.core.sales.quote.element.DevisSQLElement;import org.openconcerto.erp.core.sales.shipment.element.BonDeLivraisonSQLElement;import org.openconcerto.erp.core.supplychain.credit.element.AvoirFournisseurSQLElement;import org.openconcerto.erp.core.supplychain.order.element.CommandeSQLElement;import org.openconcerto.erp.core.supplychain.order.element.DemandePrixSQLElement;import org.openconcerto.erp.core.supplychain.receipt.element.BonReceptionSQLElement;import org.openconcerto.sql.Configuration;import org.openconcerto.sql.element.BaseSQLComponent;import org.openconcerto.sql.element.SQLComponent;import org.openconcerto.sql.element.SQLElement;import org.openconcerto.sql.model.IResultSetHandler;import org.openconcerto.sql.model.SQLDataSource;import org.openconcerto.sql.model.SQLRow;import org.openconcerto.sql.model.SQLRowAccessor;import org.openconcerto.sql.model.SQLRowListRSH;import org.openconcerto.sql.model.SQLRowValues;import org.openconcerto.sql.model.SQLSelect;import org.openconcerto.sql.model.SQLSelect.LockStrength;import org.openconcerto.sql.model.SQLTable;import org.openconcerto.sql.model.SQLTableEvent;import org.openconcerto.sql.model.SQLTableEvent.Mode;import org.openconcerto.sql.model.SQLTableModifiedListener;import org.openconcerto.sql.model.Where;import org.openconcerto.ui.DefaultGridBagConstraints;import org.openconcerto.utils.CollectionUtils;import org.openconcerto.utils.StringUtils;import org.openconcerto.utils.Tuple2;import org.openconcerto.utils.text.SimpleDocumentListener;import java.awt.GridBagConstraints;import java.awt.GridBagLayout;import java.sql.SQLException;import java.text.DateFormat;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import java.util.Map.Entry;import javax.swing.Icon;import javax.swing.JCheckBox;import javax.swing.JLabel;import javax.swing.JOptionPane;import javax.swing.JTextField;import javax.swing.SwingConstants;import javax.swing.event.DocumentEvent;import org.apache.commons.dbutils.ResultSetHandler;// FIXME bug JTextField for input bigIntpublic class NumerotationAutoSQLElement extends ComptaSQLConfElement {private static final String FORMAT = "_FORMAT";private static final String START = "_START";public NumerotationAutoSQLElement() {super("NUMEROTATION_AUTO", "une numérotation automatique", "numérotations automatiques");}protected List<String> getListFields() {final List<String> list = new ArrayList<String>(2);list.add("NOM");return list;}protected List<String> getComboFields() {final List<String> list = new ArrayList<String>(2);list.add("NOM");return list;}/** (non-Javadoc)** @see org.openconcerto.devis.SQLElement#getComponent()*/public SQLComponent createComponent() {return new BaseSQLComponent(this) {private Icon iconWarning = ImageIconWarning.getInstance();public void addViews() {this.setLayout(new GridBagLayout());final GridBagConstraints c = new DefaultGridBagConstraints();Set<Class<? extends SQLElement>> s = map.keySet();final ArrayList<Class<? extends SQLElement>> list = new ArrayList<Class<? extends SQLElement>>(s);Collections.sort(list, new Comparator<Class<? extends SQLElement>>() {public int compare(Class<? extends SQLElement> o1, Class<? extends SQLElement> o2) {return o1.getSimpleName().toString().compareTo(o2.getSimpleName().toString());};});List<String> added = new ArrayList<String>();for (Class<? extends SQLElement> class1 : list) {String prefix = map.get(class1);if (!added.contains(prefix)) {c.gridy++;c.gridx = 0;c.weightx = 0;added.add(prefix);SQLElement elt = Configuration.getInstance().getDirectory().getElement(class1);if (elt == null) {throw new IllegalArgumentException("Element null for class " + class1);}// AvoirJLabel labelAvoirFormat = new JLabel(StringUtils.firstUp(elt.getPluralName()) + " " + getLabelFor(prefix + FORMAT), SwingConstants.RIGHT);this.add(labelAvoirFormat, c);c.gridx++;c.weightx = 1;final JTextField fieldFormat = new JTextField();this.add(fieldFormat, c);final JLabel labelAvoirStart = new JLabel(getLabelFor(prefix + START));c.gridx++;c.weightx = 0;this.add(labelAvoirStart, c);c.gridx++;c.weightx = 1;final JTextField fieldStart = new JTextField();this.add(fieldStart, c);final JLabel labelResult = new JLabel();c.gridx++;c.weightx = 0;this.add(labelResult, c);if (getTable().getFieldsName().contains(prefix + AUTO_MONTH)) {final JCheckBox boxAuto = new JCheckBox(getLabelFor(prefix + AUTO_MONTH));c.gridx++;c.weightx = 0;this.add(boxAuto, c);this.addSQLObject(boxAuto, prefix + AUTO_MONTH);}// Affichage dynamique du résultatSimpleDocumentListener listener = new SimpleDocumentListener() {@Overridepublic void update(DocumentEvent e) {updateLabel(fieldStart, fieldFormat, labelResult);}};fieldFormat.getDocument().addDocumentListener(listener);fieldStart.getDocument().addDocumentListener(listener);this.addRequiredSQLObject(fieldFormat, prefix + FORMAT);this.addRequiredSQLObject(fieldStart, prefix + START);}}// JLabel labelCodeLettrage = new JLabel(getLabelFor("CODE_LETTRAGE"));// c.gridy++;// c.gridx = 0;// c.weightx = 0;// this.add(labelCodeLettrage, c);// c.gridx++;// c.weightx = 1;// this.add(this.textCodeLettrage, c);//// c.gridx++;// c.weightx = 0;// labelNextCodeLettrage = new JLabel();// this.add(labelNextCodeLettrage, c);JLabel labelExemple = new JLabel("Exemple de format : 'Fact'yyyy0000");c.gridy++;c.gridx = 0;c.gridwidth = GridBagConstraints.REMAINDER;c.weighty = 1;c.anchor = GridBagConstraints.NORTHWEST;this.add(labelExemple, c);// this.textCodeLettrage.getDocument().addDocumentListener(this.listenText);}// private void updateLabelNextCode() {// String s = getNextCodeLetrrage(this.textCodeLettrage.getText());// this.labelNextCodeLettrage.setText(donne + " " + s);// }private void updateLabel(JTextField textStart, JTextField textFormat, JLabel label) {if (textStart.getText().trim().length() > 0) {try {String numProposition = getNextNumero(textFormat.getText(), Integer.parseInt(textStart.getText()), new Date());if (numProposition != null) {label.setText(" --> " + numProposition);label.setIcon(null);} else {label.setIcon(this.iconWarning);label.setText("");}} catch (IllegalArgumentException e) {JOptionPane.showMessageDialog(null, "Le format " + textFormat.getText() + " n'est pas valide!");}} else {label.setIcon(this.iconWarning);label.setText("");}}};}public static final String getLabelFormatFor(Class<? extends SQLElement> clazz) {return map.get(clazz) + FORMAT;}public static final String getLabelNumberFor(Class<? extends SQLElement> clazz) {return map.get(clazz) + START;}public final static String AUTO_MONTH = "_AUTO_MONTH";public static final boolean isAuto(Class<? extends SQLElement> clazz, SQLRowAccessor rowNum) throws IllegalArgumentException {final String s = getName(clazz);return TABLE_NUM.getFieldsName().contains(s + AUTO_MONTH) && rowNum.getBoolean(s + AUTO_MONTH);}public static final void updateNextNumero(final Class<? extends SQLElement> clazz, final Date storedDate, final String storedNumero) throws SQLException {final SQLSelect sel = new SQLSelect();sel.addSelectStar(TABLE_NUM);sel.setWhere(new Where(TABLE_NUM.getKey(), "=", 2));sel.setLockStrength(LockStrength.UPDATE);final SQLRow row = CollectionUtils.getSole(SQLRowListRSH.execute(sel, false, false));if (isAuto(clazz, row))return;if (NumerotationAutoSQLElement.getNextNumero(clazz, storedDate, row).equalsIgnoreCase(storedNumero.trim())) {final String numberLabel = NumerotationAutoSQLElement.getLabelNumberFor(clazz);row.createEmptyUpdateRow().put(numberLabel, row.getInt(numberLabel) + 1).update();}}/**** @throws IllegalArgumentException pattern incorrect*/public static final String getNextNumero(Class<? extends SQLElement> clazz) throws IllegalArgumentException {return getNextNumero(clazz, new Date());}/** Format du type 'Fact'yyyy-MM-dd0000** @throws IllegalArgumentException pattern incorrect*/public static final String getNextNumero(Class<? extends SQLElement> clazz, Date d) throws IllegalArgumentException {return getNextNumero(clazz, d, null);}public static final String getNextNumero(Class<? extends SQLElement> clazz, Date d, SQLRowAccessor rowNum) throws IllegalArgumentException {if (rowNum == null || rowNum.isUndefined()) {rowNum = TABLE_NUM.getRow(2);}if (isAuto(clazz, rowNum)) {return getNextForMonth(clazz, d, rowNum);} else {final String numberLabel = getLabelNumberFor(clazz);if (rowNum.getObject(numberLabel) == null || rowNum.getString(getLabelFormatFor(clazz)) == null || rowNum.getString(getLabelFormatFor(clazz)).trim().length() == 0) {return "";}String format = rowNum.getString(getLabelFormatFor(clazz));SQLSelect sel = new SQLSelect();sel.addSelect(rowNum.getTable().getField(numberLabel));sel.setWhere(rowNum.getTable().getKey(), "=", rowNum.getID());final Number l = (Number) rowNum.getTable().getDBSystemRoot().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.SCALAR_HANDLER, false));int start = l.intValue();return getNextNumero(format, start, d);}}private static final Tuple2<String, String> getPrefixAndSuffix(String format, Date d) {String prefix = "";String suffix = "";int c = format.indexOf('0');if (format.trim().length() > 0) {if (c >= 0) {prefix = format.substring(0, c);suffix = format.substring(c, format.length());try {DateFormat dateFormat = new SimpleDateFormat(prefix);prefix = dateFormat.format(d);} catch (IllegalArgumentException e) {System.err.println("pattern incorrect");}} else {try {DateFormat dateFormat = new SimpleDateFormat(format);prefix = dateFormat.format(d);} catch (IllegalArgumentException e) {System.err.println("pattern incorrect");}}}return Tuple2.create(prefix, suffix);}/** @throws IllegalArgumentException pattern incorrect*/protected static final String getNextNumero(String format, Integer start, Date d) throws IllegalArgumentException {if (start != null && start < 0) {return null;}Tuple2<String, String> t = getPrefixAndSuffix(format, d);DecimalFormat decimalFormat = new DecimalFormat(t.get1());return t.get0() + decimalFormat.format(start);}public static final String getNextCodeLettrage() {SQLRow rowNum = TABLE_NUM.getRow(2);final String string = rowNum.getString("CODE_LETTRAGE");String s = (string == null) ? "" : string.trim().toUpperCase();return getNextCodeLetrrage(s);}public final String getNextCodeLettragePartiel() {SQLRow rowNum = this.getTable().getRow(2);final String string = rowNum.getString("CODE_LETTRAGE_PARTIEL");String s = (string == null) ? "" : string.trim().toLowerCase();return getNextCodeLettragePartiel(s);}public static final String getNextCodeLetrrage(String code) {code = code.trim();if (code == null || code.length() == 0) {return "AAA";} else {char[] charArray = code.toCharArray();char c = 'A';int i = charArray.length - 1;while (i >= 0 && (c = charArray[i]) == 'Z') {i--;}if (i >= 0) {c++;charArray[i] = c;for (int j = i + 1; j < charArray.length; j++) {charArray[j] = 'A';}code = String.valueOf(charArray);} else {// On ajoute une lettrefinal StringBuffer buf = new StringBuffer(code.length() + 1);final int nb = code.length() + 1;for (int j = 0; j < nb; j++) {buf.append('A');}code = buf.toString();}return code;}}public static final String getNextCodeLettragePartiel(String code) {code = code.trim();if (code == null || code.length() == 0) {return "aaa";} else {char[] charArray = code.toCharArray();char c = 'a';int i = charArray.length - 1;while (i >= 0 && (c = charArray[i]) == 'z') {i--;}if (i >= 0) {c++;charArray[i] = c;for (int j = i + 1; j < charArray.length; j++) {charArray[j] = 'a';}code = String.valueOf(charArray);} else {// On ajoute une lettrefinal StringBuffer buf = new StringBuffer(code.length() + 1);final int nb = code.length() + 1;for (int j = 0; j < nb; j++) {buf.append('a');}code = buf.toString();}return code;}}private static boolean isNumeroExist(SQLElement element, int num) {if (num < 0) {return true;}String s = map.get(element.getClass());for (Class<? extends SQLElement> e : map.keySet()) {String prefix = map.get(e);if (prefix.equals(s)) {SQLSelect sel = new SQLSelect();SQLElement elt = Configuration.getInstance().getDirectory().getElement(e);if (elt != null && elt.getTable().getFieldsName().contains("NUMERO")) {sel.addSelect(elt.getTable().getKey());sel.setWhere(new Where(elt.getTable().getField("NUMERO"), "LIKE", getPattern(elt, num)));System.err.println("NumerotationAutoSQLElement.isNumeroExist() " + sel.asString());List<SQLRow> liste = (List<SQLRow>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new SQLRowListRSH(elt.getTable(), true));if (liste.size() > 0) {return true;}}}}return false;}protected static final SQLTable TABLE_NUM = ((ComptaPropsConfiguration) Configuration.getInstance()).getSQLBaseSociete().getTable("NUMEROTATION_AUTO");public static void main(String[] args) {List<String> l = Arrays.asList("2011/05/001", "2011/05/002", "2011/05/003", "2011/05/004");DecimalFormat format = new DecimalFormat("'2011/05/'000");for (String string : l) {Number n;try {n = format.parse(string);System.err.println(n);} catch (ParseException exn) {// TODO Bloc catch auto-généréexn.printStackTrace();}}}private static String getNextForMonth(Class<? extends SQLElement> clazz, Date d, SQLRowAccessor rowNum) {List<SQLTable> tables = new ArrayList<>();SQLTable table = Configuration.getInstance().getDirectory().getElement(clazz).getTable();tables.add(table);final String s = map.get(clazz);for (Entry<Class<? extends SQLElement>, String> entry : map.entrySet()) {if (!entry.getKey().equals(clazz) && entry.getValue().equals(s)) {tables.add(Configuration.getInstance().getDirectory().getElement(entry.getKey()).getTable());}}final String pattern = rowNum.getString(s + FORMAT);final Tuple2<String, String> prefixSuffix = getPrefixAndSuffix(pattern, d);final String prefix = prefixSuffix.get0();final String suffix = prefixSuffix.get1();final String decimalPattern = "'" + prefix + "'" + suffix;final DecimalFormat format = new DecimalFormat(decimalPattern);int value = 0;for (SQLTable sqlTable : tables) {final SQLSelect sel = new SQLSelect();sel.addSelect(sqlTable.getField("NUMERO"));sel.addSelect(sqlTable.getKey());Where w = new Where(sqlTable.getField("NUMERO"), "LIKE", "%" + prefix + "%");sel.setWhere(w);SQLDataSource source = Configuration.getInstance().getBase().getDataSource();final ResultSetHandler createFromSelect = SQLRowListRSH.createFromSelect(sel);final List<SQLRow> l = (List<SQLRow>) source.execute(sel.asString(), new IResultSetHandler(createFromSelect, false));for (SQLRow sqlRow : l) {final String numero = sqlRow.getString("NUMERO");try {final Number n = format.parse(numero);value = Math.max(value, n.intValue());} catch (ParseException exn) {System.err.println("NumerotationAutoSQLElement.getNextForMonth(): warning: unable to parse " + numero + " with pattern " + decimalPattern + " row:" + sqlRow);exn.printStackTrace();}}}final String result = format.format(value + 1);System.err.println("NumerotationAutoSQLElement.getNextForMonth(): " + result);return result;}private static String getPattern(SQLElement elt, int num) {SQLRow rowNum = TABLE_NUM.getRow(2);String s = map.get(elt.getClass());String format = rowNum.getString(s + FORMAT);format = format.replaceAll("y|d|M", "_");format = format.replaceAll("'", "");int c = format.indexOf('0');String numero = "";if (format.trim().length() > 0) {if (c >= 0) {String prefix = format.substring(0, c);String suffix = format.substring(c, format.length());String d = prefix;DecimalFormat numberFormat = new DecimalFormat(suffix);String n = numberFormat.format(num);numero = d + n;} else {String d = format;numero = d + String.valueOf(num);}} else {numero = String.valueOf(num);}return numero;}/*** Vérifie et corrige la numérotation** @param elt*/public static void fixNumerotation(SQLElement elt) {SQLRow rowNum = TABLE_NUM.getRow(2);String s = map.get(elt.getClass());if (!rowNum.getTable().contains(s + AUTO_MONTH) || !rowNum.getBoolean(s + AUTO_MONTH)) {if (rowNum.getObject(s + START) != null) {int start = rowNum.getInt(s + START);// si le numero precedent n'existe pasif (!isNumeroExist(elt, start - 1)) {int i = 2;while (!isNumeroExist(elt, start - i)) {i++;}if (start - i >= 0) {SQLRowValues rowVals = rowNum.createEmptyUpdateRow();rowVals.put(s + START, start - i + 1);try {rowVals.update();} catch (SQLException e) {e.printStackTrace();}}}}}}private static Map<Class<? extends SQLElement>, String> map = new HashMap<Class<? extends SQLElement>, String>();static {map.put(AvoirClientSQLElement.class, "AVOIR");map.put(SaisieVenteFactureSQLElement.class, "FACT");map.put(AvoirClientSQLElement.class, "AVOIR");map.put(AvoirFournisseurSQLElement.class, "AVOIR_F");map.put(DevisSQLElement.class, "DEVIS");map.put(DemandePrixSQLElement.class, "DMD_PRIX");map.put(CustomerSQLElement.class, "CLIENT");map.put(BonDeLivraisonSQLElement.class, "BON_L");map.put(BonReceptionSQLElement.class, "BON_R");map.put(CommandeClientSQLElement.class, "COMMANDE_CLIENT");map.put(CommandeSQLElement.class, "COMMANDE");map.put(CourrierClientSQLElement.class, "COURRIER");map.put(RelanceSQLElement.class, "RELANCE");map.put(SalarieSQLElement.class, "SALARIE");}/*** Permet d'ajouter la gestion de la numérotation automatique. Attention à bien créer les champs* name_FORMAT et name_START dans la table NUMEROTATION_AUTO** @param elt* @param name*/public static void addClass(Class<? extends SQLElement> elt, String name) {map.put(elt, name);}public static void removeClass(Class<? extends SQLElement> elt) {map.remove(elt);}private static String getName(Class<? extends SQLElement> elt) {return map.get(elt);}public static void addListeners() {for (Class<? extends SQLElement> clazz : map.keySet()) {final SQLElement elt = Configuration.getInstance().getDirectory().getElement(clazz);if (elt != null) {elt.getTable().addTableModifiedListener(new SQLTableModifiedListener() {@Overridepublic void tableModified(SQLTableEvent evt) {if (evt.getMode() == Mode.ROW_UPDATED) {SQLRow row = evt.getRow();if (row != null && row.isArchived()) {fixNumerotation(elt);}}}});} else {System.err.println(clazz);Thread.dumpStack();}}}@Overrideprotected String createCode() {return "autonumbering";}}