package org.openswing.swing.table.profiles.database.server;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

import org.openswing.swing.message.send.java.*;
import org.openswing.swing.table.profiles.java.*;
import org.openswing.swing.util.java.Consts;


/**
 * <p>Title: OpenSwing Framework</p>
 * <p>Description: Grid profile manager: it manages grid profile storing and fetching.
 * This implementation is based on database tables: it stores and retrieves user profiles from a table filtered by "username".
 * </p>
 * <p>Copyright: Copyright (C) 2006 Mauro Carniel</p>
 *
 * <p> This file is part of OpenSwing Framework.
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the (LGPL) Lesser General Public
 * License as published by the Free Software Foundation;
 *
 *                GNU LESSER GENERAL PUBLIC LICENSE
 *                 Version 2.1, February 1999
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Library General Public License for more details.
 *
 * You should have received a copy of the GNU Library General Public
 * License along with this library; if not, write to the Free
 * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *
 *       The author may be contacted at:
 *           maurocarniel@tin.it</p>
 *
 * @author Mauro Carniel
 * @version 1.0
 */
public class DbGridProfileManager extends GridProfileManager {

  /** connection manager */
  private DbConnectionSource dbConnectionSource = null;
  private DbActiveProfileDescriptor dbActiveProfileDescriptor = null;
  private DbDigestDescriptor dbDigestDescriptor = null;
  private DbProfileDescriptor dbProfileDescriptor = null;
  private SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");


  public DbGridProfileManager(
      DbConnectionSource dbConnectionSource,
      DbActiveProfileDescriptor dbActiveProfileDescriptor,
      DbDigestDescriptor dbDigestDescriptor,
      DbProfileDescriptor dbProfileDescriptor) {
    this.dbConnectionSource = dbConnectionSource;
    this.dbActiveProfileDescriptor = dbActiveProfileDescriptor;
    this.dbDigestDescriptor = dbDigestDescriptor;
    this.dbProfileDescriptor = dbProfileDescriptor;
  }


  /**
   * @param functionId identifier (functionId) associated to the grid
   * @return list of GridProfileDescription objects
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   */
  public ArrayList getUserProfiles(final String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      for(int i=0;i<dbProfileDescriptor.getIdFieldNames().length;i++)
        idFields += dbProfileDescriptor.getIdFieldNames()[i]+",";
      pstmt = conn.prepareStatement(
        "select "+
        idFields+
        dbProfileDescriptor.getDescriptionFieldName()+","+
        dbProfileDescriptor.getDefaultProfileFieldName()+" "+
        "from "+dbProfileDescriptor.getProfileTableName()+" "+
        "where "+dbProfileDescriptor.getFunctionIdFieldName()+"=? "+
        "and "+dbProfileDescriptor.getUsernameFieldName()+"=? "+
        "order by "+dbProfileDescriptor.getDescriptionFieldName()
      );
      pstmt.setString(1,functionId);
      pstmt.setString(2,getUsername());
      rset = pstmt.executeQuery();

      ArrayList profiles = new ArrayList();
      Object[] id = null;
      while(rset.next()) {
        id = new Object[rset.getMetaData().getColumnCount()-2];
        for(int i=0;i<id.length;i++)
          id[i] = rset.getObject(i+1);
        profiles.add(new GridProfileDescription(
          id,
          rset.getString(id.length+1),
          rset.getString(id.length+2).equals("Y")
        ));
      }
      return profiles;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @return default user profile; null if the default profile has not been yet stored
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   */
  public final GridProfile getDefaultProfile(final String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      for(int i=0;i<dbProfileDescriptor.getIdFieldNames().length;i++)
        idFields += dbProfileDescriptor.getIdFieldNames()[i]+",";
      pstmt = conn.prepareStatement(
        "select "+
        dbProfileDescriptor.getColumnsAttributeFieldName()+","+
        dbProfileDescriptor.getColumnsVisibilityFieldName()+","+
        dbProfileDescriptor.getColumnsWidthFieldName()+","+
        dbProfileDescriptor.getCurrentSortedColumnsFieldName()+","+
        dbProfileDescriptor.getCurrentSortedVersusColumnsFieldName()+","+
        dbProfileDescriptor.getDefaultProfileFieldName()+","+
        dbProfileDescriptor.getDescriptionFieldName()+","+
        dbProfileDescriptor.getFunctionIdFieldName()+","+
        idFields+
        dbProfileDescriptor.getQuickFilterValuesFieldName()+","+
        dbProfileDescriptor.getUsernameFieldName()+" "+
        "from "+dbProfileDescriptor.getProfileTableName()+" "+
        "where "+dbProfileDescriptor.getFunctionIdFieldName()+"=? "+
        "and "+dbProfileDescriptor.getUsernameFieldName()+"=? "+
        "and "+dbProfileDescriptor.getDefaultProfileFieldName()+"='Y'"
      );
      pstmt.setString(1,functionId);
      pstmt.setString(2,getUsername());
      rset = pstmt.executeQuery();

      ArrayList profiles = new ArrayList();
      Object[] ids = null;
      GridProfile profile = null;
      if(rset.next()) {
        return getUserProfileFromResultSet(rset);
      }
      return null;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @param id grid profile identifier
   * @return user profile
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   */
  public GridProfile getUserProfile(String functionId,Object id) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      String idWhereFields = "";
      Object[] idWhereFieldValues = (Object[])id;
      for(int i=0;i<dbProfileDescriptor.getIdFieldNames().length;i++) {
        idFields += dbProfileDescriptor.getIdFieldNames()[i]+",";
        idWhereFields += dbProfileDescriptor.getIdFieldNames()[i]+"=? and ";
      }
      idWhereFields = idWhereFields.substring(0,idWhereFields.length()-4);

      pstmt = conn.prepareStatement(
        "select "+
        dbProfileDescriptor.getColumnsAttributeFieldName()+","+
        dbProfileDescriptor.getColumnsVisibilityFieldName()+","+
        dbProfileDescriptor.getColumnsWidthFieldName()+","+
        dbProfileDescriptor.getCurrentSortedColumnsFieldName()+","+
        dbProfileDescriptor.getCurrentSortedVersusColumnsFieldName()+","+
        dbProfileDescriptor.getDefaultProfileFieldName()+","+
        dbProfileDescriptor.getDescriptionFieldName()+","+
        dbProfileDescriptor.getFunctionIdFieldName()+","+
        idFields+
        dbProfileDescriptor.getQuickFilterValuesFieldName()+","+
        dbProfileDescriptor.getUsernameFieldName()+" "+
        "from "+dbProfileDescriptor.getProfileTableName()+" "+
        "where "+dbProfileDescriptor.getFunctionIdFieldName()+"=? "+
        "and "+idWhereFields
      );
      pstmt.setString(1,functionId);

      for(int i=0;i<idWhereFieldValues.length;i++)
        pstmt.setObject(i+2,idWhereFieldValues[i]);
      rset = pstmt.executeQuery();

      if(rset.next()) {
        return getUserProfileFromResultSet(rset);
      }
      else
        throw new IOException("Record not found: "+id);
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }

  }
    /**
     * @param rset ResultSet having a record related to a GridProfile
     * @return GridProfile created from the record of the ResultSet
     */
  private GridProfile getUserProfileFromResultSet(ResultSet rset) throws Throwable {
    /*
      dbProfileDescriptor.getColumnsAttributeFieldName()+","+
      dbProfileDescriptor.getColumnsVisibilityFieldName()+","+
      dbProfileDescriptor.getColumnsWidthFieldName()+","+
      dbProfileDescriptor.getCurrentSortedColumnsFieldName()+","+
      dbProfileDescriptor.getCurrentSortedVersusColumnsFieldName()+","+
      dbProfileDescriptor.getDefaultProfileFieldName()+","+
      dbProfileDescriptor.getDescriptionFieldName()+","+
      dbProfileDescriptor.getFunctionIdFieldName()+","+
      idFields+
      dbProfileDescriptor.getQuickFilterValuesFieldName()+","+
      dbProfileDescriptor.getUsernameFieldName()+" "+
    */

    String description = rset.getString(7);
    String functionId = rset.getString(8);
    String username = rset.getString(rset.getMetaData().getColumnCount());

    String aux = rset.getString(4); // currentSortedColumns
    ArrayList currentSortedColumns = new ArrayList();
    StringTokenizer st = new StringTokenizer(aux,",");
    while(st.hasMoreTokens())
      currentSortedColumns.add( st.nextToken() );

    aux = rset.getString(5); // currentSortedVersusColumns
    ArrayList currentSortedVersusColumns = new ArrayList();
    st = new StringTokenizer(aux,",");
    while(st.hasMoreTokens())
      currentSortedVersusColumns.add( st.nextToken() );

    aux = rset.getString(1); // columnsAttribute
    st = new StringTokenizer(aux,",");
    int cols = st.countTokens();
    String[] columnsAttribute = new String[cols];
    int i = 0;
    while(st.hasMoreTokens())
      columnsAttribute[i++] = st.nextToken();

    aux = rset.getString(2); // columnsVisibility
    boolean[] columnsVisibility = new boolean[cols];
    st = new StringTokenizer(aux,",");
    i = 0;
    while(st.hasMoreTokens())
      columnsVisibility[i++] = st.nextToken().toLowerCase().equals("true");

    aux = rset.getString(3); // columnsWidth
    int[] columnsWidth = new int[cols];
    st = new StringTokenizer(aux,",");
    i = 0;
    while(st.hasMoreTokens())
      columnsWidth[i++] = Integer.parseInt( st.nextToken().trim() );

    aux = rset.getString(6); // defaultProfile
    boolean defaultProfile = aux.equals("Y");

    // id...
    Object[] id = new Object[rset.getMetaData().getColumnCount()-10];
    for(i=9;i<=rset.getMetaData().getColumnCount()-2;i++)
      id[i-9] = rset.getObject(i);

    // quickFilterValues
    HashMap quickFilterValues = new HashMap(); // hashtable which contains the associations: attribute name, new FilterWhereClause[2] {FilterWhereClause,FilterWhereClause})
    String attributeName = null;
    String operator1,typevalue1,operator2,typevalue2;
    Object value1,value2;
    FilterWhereClause[] filter = new FilterWhereClause[2];
    String filters = rset.getString(rset.getMetaData().getColumnCount()-1);
    StringTokenizer f = new StringTokenizer(filters,"\n");
    ArrayList values = null;
    String[] tokens = null;
    while(f.hasMoreTokens()) { // f = attribute name,operator1,typevalue1,value1a\tvalue1b\tvalue1c...[,operator2,typevalue2,value2...]
      st = new StringTokenizer(f.nextToken(),",");
      attributeName = st.nextToken();
      operator1 = st.nextToken();
      typevalue1 = st.nextToken();
      value1 = st.nextToken();

      tokens = value1.toString().split("\t");
      if (tokens.length==1 && !operator1.equals(Consts.IN)) {
        if (typevalue1.equals("D"))
          value1 = sdf.parse(value1.toString());
        else if (typevalue1.equals("N"))
          value1 = new BigDecimal(value1.toString());
      }
      else {
        values = new ArrayList();
        for(int j=0;j<tokens.length;j++)
          if (typevalue1.equals("D"))
            values.add(sdf.parse(tokens[j]));
          else if (typevalue1.equals("N"))
            values.add(new BigDecimal(tokens[j]));
          else
            values.add(tokens[j]);
        value1 = values;
      }
      filter[0] = new FilterWhereClause(attributeName,operator1,value1);

      if (st.hasMoreTokens()) {
        operator2 = st.nextToken();
        typevalue2 = st.nextToken();
        value2 = st.nextToken();

        tokens = value2.toString().split("\t");
        if (tokens.length==1 && !operator2.equals(Consts.IN)) {
          if (typevalue2.equals("D"))
            value1 = sdf.parse(value2.toString());
          else if (typevalue2.equals("N"))
            value1 = new BigDecimal(value2.toString());
        }
        else {
          values = new ArrayList();
          for(int j=0;j<tokens.length;j++)
            if (typevalue1.equals("D"))
              values.add(sdf.parse(tokens[j]));
            else if (typevalue1.equals("N"))
              values.add(new BigDecimal(tokens[j]));
            else
              values.add(tokens[j]);
          value2 = values;
        }

        filter[1] = new FilterWhereClause(attributeName,operator2,value2);
      }
      else
        filter[1] = null;

      quickFilterValues.put( attributeName,filter );
    }

    return new GridProfile(
      id,
      description, // description
      functionId, // functionId
      username, // username
      currentSortedColumns, // currentSortedColumns
      currentSortedVersusColumns, // currentSortedVersusColumns
      quickFilterValues, // quickFilterValues
      columnsAttribute, // columnsAttribute
      columnsVisibility, // columnsVisibility
      columnsWidth, // columnsWidth
      defaultProfile // defaultProfile
    );
  }


  /**
   * Store the specified grid profile.
   * @param profile profile to store
   * @return profile id
   * @throws Throwable throwed if storing operation does not correctly accomplished
   * Note: if profile.getId() is null then this method must define id property.
   */
  public Object storeUserProfile(GridProfile profile) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();

      if (profile.getId()==null || ((Object[])profile.getId())[0]==null)
        profile.setId( dbProfileDescriptor.getNewIdFieldValues(conn,profile) );

      String idFields = "";
      String idSetFields = "";
      String idWhereFields = "";
      String pkQuestions = "";
      for(int i=0;i<dbProfileDescriptor.getIdFieldNames().length;i++) {
        idFields += dbProfileDescriptor.getIdFieldNames()[i]+",";
        idSetFields += dbProfileDescriptor.getIdFieldNames()[i]+"=?,";
        idWhereFields += dbProfileDescriptor.getIdFieldNames()[i]+"=? and ";
        pkQuestions += "?,";
      }
      idWhereFields = idWhereFields.substring(0,idWhereFields.length()-4);
      Object[] idValues = (Object[])profile.getId();

      String currentSortedColumns = "";
      for(int i=0;i<profile.getCurrentSortedColumns().size();i++)
        currentSortedColumns += profile.getCurrentSortedColumns().get(i)+",";
      if (currentSortedColumns.length()>0)
        currentSortedColumns = currentSortedColumns.substring(0,currentSortedColumns.length()-1);

      String currentSortedVersusColumns = "";
      for(int i=0;i<profile.getCurrentSortedVersusColumns().size();i++)
        currentSortedVersusColumns += profile.getCurrentSortedVersusColumns().get(i)+",";
      if (currentSortedVersusColumns.length()>0)
        currentSortedVersusColumns = currentSortedVersusColumns.substring(0,currentSortedVersusColumns.length()-1);

      String columnsAttribute = "";
      for(int i=0;i<profile.getColumnsAttribute().length;i++)
        columnsAttribute += profile.getColumnsAttribute()[i]+",";
      if (columnsAttribute.length()>0)
        columnsAttribute = columnsAttribute.substring(0,columnsAttribute.length()-1);

      String columnsVisibility = "";
      for(int i=0;i<profile.getColumnsVisibility().length;i++)
        columnsVisibility += profile.getColumnsVisibility()[i]+",";
      if (columnsVisibility.length()>0)
        columnsVisibility = columnsVisibility.substring(0,columnsVisibility.length()-1);

      String columnsWidth = "";
      for(int i=0;i<profile.getColumnsWidth().length;i++)
        columnsWidth += profile.getColumnsWidth()[i]+",";
      if (columnsWidth.length()>0)
        columnsWidth = columnsWidth.substring(0,columnsWidth.length()-1);

      Iterator it = profile.getQuickFilterValues().keySet().iterator();
      String aux = "";
      FilterWhereClause[] filter = null;
      String attributeName = null;
      String filters = "";
      while(it.hasNext()) {
        attributeName = it.next().toString();
        filter = (FilterWhereClause[])profile.getQuickFilterValues().get(attributeName);
        aux = attributeName+","+filter[0].getOperator()+",";
        if (filter[0].getValue()!=null) {

          if (filter[0].getOperator().equals(Consts.IN) || filter[0].getValue() instanceof ArrayList) {
            ArrayList values = (ArrayList)filter[0].getValue();
            for(int j=0;j<values.size();j++)
              if (values.get(j) instanceof Date)
                aux += "D,"+sdf.format( values.get(j) )+"\t";
              else if (filter[0].getValue() instanceof Number)
                aux += "N,"+values.get(j)+"\t";
              else
                aux += "T,"+values.get(j)+"\t";
          }
          else {
            if (filter[0].getValue() instanceof Date)
              aux += "D,"+sdf.format( filter[0].getValue() );
            else if (filter[0].getValue() instanceof Number)
              aux += "N,"+filter[0].getValue();
            else
              aux += "T,"+filter[0].getValue();
          }
        }

        if (filter[1]!=null) {
          aux += ","+filter[1].getOperator()+",";
          if (filter[1].getValue()!=null) {
            if (filter[1].getOperator().equals(Consts.IN) || filter[1].getValue() instanceof ArrayList) {
              ArrayList values = (ArrayList)filter[1].getValue();
              for(int j=0;j<values.size();j++)
                if (values.get(j) instanceof Date)
                  aux += "D,"+sdf.format( values.get(j) )+"\t";
                else if (filter[1].getValue() instanceof Number)
                  aux += "N,"+values.get(j)+"\t";
                else
                  aux += "T,"+values.get(j)+"\t";
            }
            else {
              if (filter[1].getValue() instanceof Date)
                aux += "D,"+sdf.format( filter[1].getValue() );
              else if (filter[1].getValue() instanceof Number)
                aux += "N,"+filter[1].getValue();
              else
                aux += "T,"+filter[1].getValue();
            }
          }
        }
        filters += aux+"\n";
      }

      Hashtable h1 = dbProfileDescriptor.storeUserProfileOnSetUpdate();
      Hashtable h2 = dbProfileDescriptor.storeUserProfileOnWhereUpdate();
      String where = " and ";
      Enumeration en = h2.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h2.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      String set = ",";
      en = h1.keys();
      field = null;
      ArrayList setValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        set += field+"=?,";
        setValues.add(h1.get(field));
      }
      if (set.length()>1)
        set = set.substring(0,set.length()-1);
      else
        set = "";

      pstmt = conn.prepareStatement(
        "update "+dbProfileDescriptor.getProfileTableName()+" "+
        "set "+
        dbProfileDescriptor.getColumnsAttributeFieldName()+"=?,"+
        dbProfileDescriptor.getColumnsVisibilityFieldName()+"=?,"+
        dbProfileDescriptor.getColumnsWidthFieldName()+"=?,"+
        dbProfileDescriptor.getCurrentSortedColumnsFieldName()+"=?,"+
        dbProfileDescriptor.getCurrentSortedVersusColumnsFieldName()+"=?,"+
        dbProfileDescriptor.getDefaultProfileFieldName()+"=?,"+
        dbProfileDescriptor.getDescriptionFieldName()+"=?,"+
        dbProfileDescriptor.getFunctionIdFieldName()+"=?,"+
        idSetFields+
        dbProfileDescriptor.getQuickFilterValuesFieldName()+"=?,"+
        dbProfileDescriptor.getUsernameFieldName()+"=? "+
        set+" "+
        "where "+idWhereFields+where
      );
      int len1 = 8+idValues.length;
      int len2 = 8+idValues.length+2+setValues.size();

      pstmt.setString(7,profile.getDescription());
      pstmt.setString(8,profile.getFunctionId());
      pstmt.setString(len1+2,profile.getUsername());
      for(int i=0;i<idValues.length;i++)
        pstmt.setObject(i+9,idValues[i]);
      pstmt.setString(4,currentSortedColumns);
      pstmt.setString(5,currentSortedVersusColumns);
      pstmt.setString(1,columnsAttribute);
      pstmt.setString(2,columnsVisibility);
      pstmt.setString(3,columnsWidth);
      pstmt.setString(6,profile.isDefaultProfile()?"Y":"N");
      pstmt.setString(len1+1,filters);
      for(int i=0;i<setValues.size();i++)
        pstmt.setObject(i+len1+2,setValues.get(i));
      for(int i=0;i<idValues.length;i++)
        pstmt.setObject(i+len2+1,idValues[i]);
      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+len2+idValues.length+1,whereValues.get(i));

      int num = pstmt.executeUpdate();

      if (num==0) {

        Hashtable h = dbProfileDescriptor.storeUserProfileOnInsert();
        String ins = "";
        en = h.keys();
        field = null;
        ArrayList insValues = new ArrayList();
        while(en.hasMoreElements()) {
          field = en.nextElement().toString();
          pkQuestions += "?,";
          ins += ","+field;
          insValues.add(h.get(field));
        }

        pstmt = conn.prepareStatement(
          "insert into "+dbProfileDescriptor.getProfileTableName()+"("+
          dbProfileDescriptor.getColumnsAttributeFieldName()+","+
          dbProfileDescriptor.getColumnsVisibilityFieldName()+","+
          dbProfileDescriptor.getColumnsWidthFieldName()+","+
          dbProfileDescriptor.getCurrentSortedColumnsFieldName()+","+
          dbProfileDescriptor.getCurrentSortedVersusColumnsFieldName()+","+
          dbProfileDescriptor.getDefaultProfileFieldName()+","+
          dbProfileDescriptor.getDescriptionFieldName()+","+
          dbProfileDescriptor.getFunctionIdFieldName()+","+
          idFields+
          dbProfileDescriptor.getQuickFilterValuesFieldName()+","+
          dbProfileDescriptor.getUsernameFieldName()+
          ins+") "+
          "values("+pkQuestions+"?,?,?,?,?,?,?,?,?,?)"
        );
        for(int i=0;i<idValues.length;i++)
          pstmt.setObject(i+9,idValues[i]);
        pstmt.setString(7,profile.getDescription());
        pstmt.setString(8,profile.getFunctionId());
        pstmt.setString(8+idValues.length+2,profile.getUsername());
        pstmt.setString(4,currentSortedColumns);
        pstmt.setString(5,currentSortedVersusColumns);
        pstmt.setString(1,columnsAttribute);
        pstmt.setString(2,columnsVisibility);
        pstmt.setString(3,columnsWidth);
        pstmt.setString(6,profile.isDefaultProfile()?"Y":"N");
        pstmt.setString(8+idValues.length+1,filters);

        for(int i=0;i<insValues.size();i++)
          pstmt.setObject(i+8+idValues.length+3,insValues.get(i));

        pstmt.execute();
      }

      conn.commit();
      return profile.getId();
    }
    catch (Throwable t) {
      throw new IOException(t.getMessage());
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }

  }


  /**
   * Delete the specified grid profile.
   * @param id grid profile identifier
   * @throws Throwable throwed if deleting operation does not correctly accomplished
   */
  public void deleteUserProfile(String functionId,Object id) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      for(int i=0;i<dbProfileDescriptor.getIdFieldNames().length;i++)
        idFields += dbProfileDescriptor.getIdFieldNames()[i]+"=? and ";
      idFields = idFields.substring(0,idFields.length()-4);

      Hashtable h = dbProfileDescriptor.deleteUserProfile(functionId,id);
      String where = " and ";
      Enumeration en = h.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      pstmt = conn.prepareStatement(
        "delete from "+dbProfileDescriptor.getProfileTableName()+" "+
        "where "+idFields+where
      );
      Object[] idValues = (Object[])id;
      for(int i=0;i<idValues.length;i++)
        pstmt.setObject(i+1,idValues[i]);

      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+idValues.length+1,whereValues.get(i));

      int num = pstmt.executeUpdate();

      if (num==0)
        throw new IOException("Record not found: "+id);
      else
        conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Delete all grid profiles, independently from the current user.
   * This method is automatically invoked if "grid digest" comparison lead to discover a grid change: in this case all grid profiles must be removed.
   * @param functionId identifier (functionId) associated to the grid
   * @throws Throwable throwed if deleting operation does not correctly accomplished
   */
  public void deleteAllGridProfiles(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();

      Hashtable h = dbProfileDescriptor.deleteAllGridProfiles(functionId);
      String where = " and ";
      Enumeration en = h.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      pstmt = conn.prepareStatement(
        "delete from "+dbProfileDescriptor.getProfileTableName()+" "+
        "where "+dbProfileDescriptor.getFunctionIdFieldName()+"=? "+where
      );
      pstmt.setString(1,functionId);

      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+2,whereValues.get(i));

      pstmt.execute();
      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Store the "grid digest", i.e. a value that globally identify the current grid configuration.
   * @throws Throwable throwed if storing operation does not correctly accomplished
   */
  public void storeGridDigest(String functionId,String gridDigest) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();

      Hashtable h1 = dbDigestDescriptor.storeGridDigestOnSetUpdate();
      Hashtable h2 = dbDigestDescriptor.storeGridDigestOnWhereUpdate();
      String where = " and ";
      Enumeration en = h2.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h2.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      String set = ",";
      en = h1.keys();
      field = null;
      ArrayList setValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        set += field+"=?,";
        setValues.add(h1.get(field));
      }
      if (set.length()>1)
        set = set.substring(0,set.length()-1);
      else
        set = "";

      pstmt = conn.prepareStatement(
        "update "+dbDigestDescriptor.getDigestTableName()+" "+
        "set "+dbDigestDescriptor.getDigestFieldName()+"=? "+set+" "+
        "where "+dbDigestDescriptor.getFunctionIdFieldName()+"=? "+where
      );
      pstmt.setString(1,gridDigest);
      pstmt.setString(2+setValues.size(),functionId);

      for(int i=0;i<setValues.size();i++)
        pstmt.setObject(i+2,setValues.get(i));
      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+setValues.size()+3,whereValues.get(i));

      int num = pstmt.executeUpdate();

      if (num==0) {

        Hashtable h = dbDigestDescriptor.storeGridDigestOnInsert();
        String ins = "";
        en = h.keys();
        field = null;
        String pkQuestions = "";
        ArrayList insValues = new ArrayList();
        while(en.hasMoreElements()) {
          field = en.nextElement().toString();
          pkQuestions += "?,";
          ins += ","+field;
          insValues.add(h.get(field));
        }

        pstmt = conn.prepareStatement(
          "insert into "+dbDigestDescriptor.getDigestTableName()+"("+
          dbDigestDescriptor.getDigestFieldName()+","+
          dbDigestDescriptor.getFunctionIdFieldName()+ins+") "+
          "values("+pkQuestions+"?,?)"
        );
        pstmt.setString(1,gridDigest);
        pstmt.setString(2,functionId);

        for(int i=0;i<insValues.size();i++)
          pstmt.setObject(i+3,insValues.get(i));
        pstmt.execute();
      }
      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @return retrieve the "grid digest", i.e. a value that globally identify the current grid configuration; this digest is used to check if grid columns have been changed from last grid execution: in this case all grid profiles will be deleted
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   * Note: this method returns null if no digest has been yet stored (i.e. this is the first time the grid is being viewed)
   */
  public String getLastGridDigest(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      pstmt = conn.prepareStatement(
        "select "+
        dbDigestDescriptor.getDigestFieldName()+" "+
        "from "+dbDigestDescriptor.getDigestTableName()+" "+
        "where "+dbDigestDescriptor.getFunctionIdFieldName()+"=? "
      );
      pstmt.setString(1,functionId);
      rset = pstmt.executeQuery();

      if(rset.next()) {
        return rset.getString(1);
      }
      return null;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @return retrieve the last profile identifier in action
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   * Note: this method returns null if no profile identifier has been yet stored (i.e. this is the first time the grid is being viewed)
   */
  public Object getLastGridProfileId(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      for(int i=0;i<dbActiveProfileDescriptor.getIdFieldNames().length;i++)
        idFields += dbActiveProfileDescriptor.getIdFieldNames()[i]+",";
      idFields = idFields.substring(0,idFields.length()-1);

      pstmt = conn.prepareStatement(
        "select "+idFields+" "+
        "from "+dbActiveProfileDescriptor.getActiveProfileTableName()+" "+
        "where "+dbActiveProfileDescriptor.getFunctionIdFieldName()+"=? "+
        "and "+dbActiveProfileDescriptor.getUsernameFieldName()+"=? "
      );
      pstmt.setString(1,functionId);
      pstmt.setString(2,getUsername());
      rset = pstmt.executeQuery();

      if(rset.next()) {
        Object[] id = new Object[dbActiveProfileDescriptor.getIdFieldNames().length];
        for(int i=0;i<id.length;i++)
          id[i] = rset.getObject(i+1);
        return id;
      }
      return null;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Store the current profile identifier in action.
   * @throws Throwable throwed if storing operation does not correctly accomplished
   */
  public void storeGridProfileId(String functionId,Object id) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();
      String idFields = "";
      String idSetFields = "";
      String idQuestions = "";
      for(int i=0;i<dbActiveProfileDescriptor.getIdFieldNames().length;i++) {
        idFields += dbActiveProfileDescriptor.getIdFieldNames()[i]+",";
        idSetFields += dbActiveProfileDescriptor.getIdFieldNames()[i]+"=?,";
        idQuestions += "?,";
      }
      idSetFields = idSetFields.substring(0,idSetFields.length()-1);

      Hashtable h1 = dbActiveProfileDescriptor.storeGridProfileIdOnSetUpdate();
      Hashtable h2 = dbActiveProfileDescriptor.storeGridProfileIdOnWhereUpdate();
      String where = " and ";
      Enumeration en = h2.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h2.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      String set = ",";
      en = h1.keys();
      field = null;
      ArrayList setValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        set += field+"=?,";
        setValues.add(h1.get(field));
      }
      if (set.length()>1)
        set = set.substring(0,set.length()-1);
      else
        set = "";

      pstmt = conn.prepareStatement(
        "update "+dbActiveProfileDescriptor.getActiveProfileTableName()+" "+
        "set "+idSetFields+" "+set+" "+
        "where "+dbActiveProfileDescriptor.getFunctionIdFieldName()+"=? "+
        "and "+dbActiveProfileDescriptor.getUsernameFieldName()+"=? "+where
      );

      Object[] idValues = (Object[])id;
      for(int i=0;i<idValues.length;i++)
        pstmt.setObject(i+1,idValues[i]);

      for(int i=0;i<setValues.size();i++)
        pstmt.setObject(i+idValues.length+1,setValues.get(i));

      pstmt.setString(idValues.length+1+setValues.size(),functionId);
      pstmt.setString(idValues.length+2+setValues.size(),getUsername());

      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+setValues.size()+idValues.length+3,whereValues.get(i));

      int num = pstmt.executeUpdate();

      if (num==0) {

        Hashtable h = dbActiveProfileDescriptor.storeGridProfileIdOnInsert();
        String ins = "";
        en = h.keys();
        field = null;
        ArrayList insValues = new ArrayList();
        while(en.hasMoreElements()) {
          field = en.nextElement().toString();
          idQuestions += "?,";
          ins += ","+field;
          insValues.add(h.get(field));
        }

        pstmt = conn.prepareStatement(
          "insert into "+dbActiveProfileDescriptor.getActiveProfileTableName()+"("+
          idFields+
          dbActiveProfileDescriptor.getFunctionIdFieldName()+","+
          dbActiveProfileDescriptor.getUsernameFieldName()+ins+") "+
          "values("+idQuestions+"?,?)"
        );
        for(int i=0;i<idValues.length;i++)
          pstmt.setObject(i+1,idValues[i]);
        pstmt.setString(idValues.length+1,functionId);
        pstmt.setString(idValues.length+2,getUsername());

        for(int i=0;i<insValues.size();i++)
          pstmt.setObject(i+idValues.length+3,insValues.get(i));

        pstmt.execute();
      }
      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Delete all grid profiles identifiers for the current user.
   * This method is automatically invoked if "grid digest" comparison lead to discover a grid change.
   * @throws Throwable throwed if storing operation does not correctly accomplished
   */
  public void deleteAllGridProfileIds(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();

      Hashtable h = dbActiveProfileDescriptor.deleteAllGridProfileIds(functionId);
      String where = " and ";
      Enumeration en = h.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      pstmt = conn.prepareStatement(
        "delete from "+dbActiveProfileDescriptor.getActiveProfileTableName()+" "+
        "where "+dbActiveProfileDescriptor.getFunctionIdFieldName()+"=? "+where
      );
      pstmt.setString(1,functionId);

      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+2,whereValues.get(i));

      pstmt.execute();
      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }



}
