clsproattfilter.cs

来自「以构建的方式来实现对通用的查询统计」· CS 代码 · 共 340 行

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;
using DevExpress.XtraEditors.Repository;
using dist.hb.EnvQueryStat.Pub;

namespace dist.hb.EnvQueryStat.QuerySet
{
  class clsQueryTemplateInfo
  {
    public string strAbstractQueryName;//抽象查询名称；
    public string strAttField;//属性字段
    public string strLayerName;//图层名称
    public string strSpaceField;//空间字段
    public string strVName;//视图名称
  }

  class clsFieldInfo
  {
    public int VFieldID;
    public string FieldName;
    public string FieldAias;
    public string FieldType;
    public string DICT;
    public bool IsShow;
    public bool IsFilter;
    public bool IsOrder;
    public bool IsMainField;
    public bool IsUserVisible;

  }

  //处理属性过滤
  class clsProAttFilter
  {
    public clsQueryTemplateInfo QueryTemplateInfo;
    public clsProAttFilter()
    {
      QueryTemplateInfo = new clsQueryTemplateInfo();
      htItemLookUp = new Hashtable();

    }
    ~clsProAttFilter()
    {
    }
    public clsFieldInfo GetFieldInfo(int FieldId)
    {
      if (m_FieldInfoTable == null) return null;
      DataRow[] drs = m_FieldInfoTable.Select("VFieldID = " + FieldId.ToString());
      if (drs == null) return null;
      clsFieldInfo FieldInfo = new clsFieldInfo();

      FieldInfo.VFieldID = Convert.ToInt32(drs[0]["VFieldID"]);
      FieldInfo.FieldName = drs[0]["FieldName"].ToString();
      FieldInfo.FieldAias = drs[0]["FieldAias"].ToString();
      FieldInfo.FieldType = drs[0]["FieldType"].ToString();
      FieldInfo.DICT = drs[0]["DICT"].ToString();
      FieldInfo.IsShow = Convert.ToBoolean(drs[0]["IsShow"]);
      FieldInfo.IsFilter = Convert.ToBoolean(drs[0]["IsFilter"]);
      FieldInfo.IsOrder = Convert.ToBoolean(drs[0]["IsOrder"]);
      FieldInfo.IsMainField = Convert.ToBoolean(drs[0]["IsMainField"]);
      FieldInfo.IsUserVisible = Convert.ToBoolean(drs[0]["IsUserVisible"]);
      return FieldInfo;
    }
    public DataTable AttFilterTable
    {
      get { return m_AttFilterTable; }
    }
    private OleDbConnection m_OleDbConn;
    public OleDbConnection OleDbConn
    {
      get { return m_OleDbConn; }
      set
      {
        if (value == null) return;
        if (value == m_OleDbConn) return;
        m_OleDbConn = value;
      }
    }
    private int m_nQueryInstID;//查询ID
    public int QueryInstID
    {
      set
      {
        m_nQueryInstID = value;
      }
    }
    private int m_nAbstractQueryID;//查询模板ID
    public int AbstractQueryID
    {
      set
      {
        m_nAbstractQueryID = value;
      }
    }

    //以下函数处理属性过滤表
    System.Data.OleDb.OleDbDataAdapter m_AttFilterDbDataAdapter;
    System.Data.DataSet m_AttFilterDataSet;
    public System.Data.DataTable m_AttFilterTable;//属性过滤表
    string m_strAttFilterTbl = "FS_QAttFilter";


    //处理数据加载
    public void LoadAttFilterTable()
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT QAFid,QueryInstID,LeftBracket,Field,FieldName,relation,value1,RightBracket,logicSym,UserIsVisible "
       + "FROM " + m_strAttFilterTbl + " where QueryInstID = " + m_nQueryInstID.ToString();
      m_AttFilterDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);

      OleDbCommandBuilder custCB = new OleDbCommandBuilder(m_AttFilterDbDataAdapter);
      OleDbCommand cmd = new OleDbCommand();

      cmd.Connection = m_OleDbConn;
      //cmd.Transaction = m_tran;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = strSQL;
      m_AttFilterDbDataAdapter.SelectCommand = cmd;

      m_AttFilterDbDataAdapter.Fill(ds, m_strAttFilterTbl);
      m_AttFilterDataSet = ds;
      m_AttFilterTable = ds.Tables[m_strAttFilterTbl];
    }

    public void SaveAttFilterTable(int QueryInstID)
    {
      try
      {
        DeleteAttFilterTable(QueryInstID);//在执行前先删除。
        //获取属性过滤表的最大值
        int QAFid = clsDbPub.GetMaxValue(m_OleDbConn, m_strAttFilterTbl, "QAFid");

        //要处理主键和外键的编辑
        int i = 0;
        foreach (DataRow row in m_AttFilterTable.Rows)
        {
          if (row.RowState == DataRowState.Deleted) continue;

          i = i + 1;
          row["QueryInstID"] = QueryInstID;
          row["QAFid"] = QAFid + i;
        }
        if (m_AttFilterTable.Rows.Count <= 0) return;
        SaveAttFilterTable(m_AttFilterTable.Rows);
        //m_AttFilterDbDataAdapter.Update(m_AttFilterDataSet, m_strAttFilterTbl);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, "提示");
      }
    }
    private void SaveAttFilterTable(DataRowCollection drc)
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT QAFid,QueryInstID,LeftBracket,Field,FieldName,relation,value1,RightBracket,logicSym,UserIsVisible "
       + "FROM " + m_strAttFilterTbl + " where QueryInstID = " + m_nQueryInstID.ToString();
      OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);

      OleDbCommandBuilder custCB = new OleDbCommandBuilder(Adapter);
      OleDbCommand cmd = new OleDbCommand();

      cmd.Connection = m_OleDbConn;
      //cmd.Transaction = m_tran;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = strSQL;
      Adapter.SelectCommand = cmd;

      Adapter.Fill(ds, m_strAttFilterTbl);
      DataTable dt = ds.Tables[m_strAttFilterTbl];
      foreach (DataRow row in drc)
      {
        if (row.RowState == DataRowState.Deleted) continue;
        dt.Rows.Add(row.ItemArray);
      }
      Adapter.Update(ds, m_strAttFilterTbl);
    }

    public void DeleteAttFilterTable(int QueryInstID)
    {
      string strSQL;
      strSQL = "DELETE  FROM " + m_strAttFilterTbl + " WHERE QueryInstID =" + QueryInstID.ToString();
      OleDbCommand command = new OleDbCommand(strSQL, m_OleDbConn);
      command.ExecuteNonQuery();
      command.Dispose();
    }

    //加载查询模板信息
    public void LoadQueryTemplateInfo()
    {
      string strSQL;
      strSQL = "SELECT AbstractQueryName, AttField, LayerName, SpaceField, VName " +
                "FROM FS_AbstractQuery INNER JOIN  FS_VTable ON FS_AbstractQuery.VTID = FS_VTable.VTID " +
                "where AbstractQueryID = " + m_nAbstractQueryID.ToString();

      OleDbConnection connection = new OleDbConnection(m_OleDbConn.ConnectionString);

      connection.Open();
      OleDbCommand command = new OleDbCommand(strSQL, connection);
      OleDbDataReader reader = command.ExecuteReader();
      if (!reader.HasRows) return;
      if (reader.Read())
      {
        QueryTemplateInfo.strAbstractQueryName = tostring(reader.GetValue(0));
        QueryTemplateInfo.strAttField = tostring(reader.GetValue(1));
        QueryTemplateInfo.strLayerName = tostring(reader.GetValue(2));
        QueryTemplateInfo.strSpaceField = tostring(reader.GetValue(3));
        QueryTemplateInfo.strVName = tostring(reader.GetValue(4));
      }
      reader.Close();
    }

    private string tostring(object obj)
    {
      if (obj == null) return "";
      else return obj.ToString();
    }

    //加载查询字段信息
    public System.Data.DataTable m_FieldInfoTable;
    public void LoadFieldsInfo()
    {
      DataSet ds = new DataSet();
      string strSQL = "SELECT FS_VTFieldDes.VFieldID as VFieldID, FieldName, FieldAias,FieldType, DICT, " +
               " IsShow,IsFilter, IsOrder, IsMainField,IsUserVisible" +
               " FROM FS_QuerySet INNER JOIN FS_VTFieldDes ON FS_QuerySet.VFieldID = FS_VTFieldDes.VFieldID " +
               "where IsFilter = 1 and AbstractQueryID = " + m_nAbstractQueryID.ToString();

      OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);

      adapter.Fill(ds, "FieldsInfo");
      m_FieldInfoTable = ds.Tables["FieldsInfo"];
    }

    public DataRow GetFieldInfo1(int Fieldid)
    {
      DataRow[] rows = m_FieldInfoTable.Select("VFieldID = " + Fieldid.ToString());
      if (rows == null) return null;
      if (rows.Length == 0) return null;
      return rows[0];
    }

    //处理属性过滤环境
    public void FillrepositoryItemComboBox_field(RepositoryItemLookUpEdit repositoryItemLookUpEdit_field1)
    {
      if (repositoryItemLookUpEdit_field1 == null || m_FieldInfoTable == null) return;
      repositoryItemLookUpEdit_field1.DataSource = m_FieldInfoTable;
      repositoryItemLookUpEdit_field1.DisplayMember = "FieldAias";
      repositoryItemLookUpEdit_field1.ValueMember = "VFieldID";
    }

    //对所有的LOOKUPEDIT进行预处理
    Hashtable htItemLookUp ;
    public void InitItemLookUp(DevExpress.XtraGrid.Views.Grid.GridView gridView1)
    {
      if (gridView1 == null) return;
      DataRow[] rows = m_FieldInfoTable.Select("FieldType = '" + ConstDataType.CString + "' AND DICT <>''");
      if (rows == null) return;
      htItemLookUp.Clear();
      foreach (DataRow dr in rows)
      {
        int fieldID = Convert.ToInt32(dr["VFieldID"].ToString());
        string DataType = dr["FieldType"].ToString();
        string SQL = dr["DICT"].ToString();
        //
       // RepositoryItemLookUpEdit lookup = CreateNewLookUpEdit(gridView1, fieldID);
        //htItemLookUp.Add(fieldID, lookup);//

        if (DataType == ConstDataType.CString && SQL != "")//重复检查
        {
          DataSet ds = new DataSet();
          OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(SQL, m_OleDbConn);
          adapter.Fill(ds, "DICT");
          AddItemComboBox(gridView1, ds.Tables["DICT"], fieldID); 
        }
        //((System.ComponentModel.ISupportInitialize)(lookup)).EndInit();
      }
    }


    private RepositoryItemComboBox AddItemComboBox(DevExpress.XtraGrid.Views.Grid.GridView gridView1,
                                            DataTable dt, int fieldid)
    {
      if (dt == null) return null;
      RepositoryItemComboBox repositoryItemComboBox1 = new DevExpress.XtraEditors.Repository.RepositoryItemComboBox();

      ((System.ComponentModel.ISupportInitialize)(repositoryItemComboBox1)).BeginInit();

      gridView1.GridControl.RepositoryItems.Add(repositoryItemComboBox1);


      repositoryItemComboBox1.AutoHeight = false;
      repositoryItemComboBox1.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Combo)});
      repositoryItemComboBox1.Name = "repositoryItemComboBox1" + fieldid.ToString();
      htItemLookUp.Add(fieldid, repositoryItemComboBox1);//

      foreach (DataRow  dr in dt.Rows)
      {
        repositoryItemComboBox1.Items.Add(dr["name"].ToString());
      }
      ((System.ComponentModel.ISupportInitialize)(repositoryItemComboBox1)).EndInit();
      return repositoryItemComboBox1;
    }

    public RepositoryItemComboBox GetLookupEdit(int fieldID)
    {
      return (RepositoryItemComboBox)htItemLookUp[fieldID];
    }

    private RepositoryItemLookUpEdit CreateNewLookUpEdit(DevExpress.XtraGrid.Views.Grid.GridView gridView1, int FieldID)
    {
      RepositoryItemLookUpEdit lookup = new RepositoryItemLookUpEdit();
      ((System.ComponentModel.ISupportInitialize)(lookup)).BeginInit();
      gridView1.GridControl.RepositoryItems.Add(lookup);

      // repositoryItemLookUpEdit_value
      // 
      lookup.AutoHeight = false;
      lookup.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Combo)});
      lookup.Columns.AddRange(new DevExpress.XtraEditors.Controls.LookUpColumnInfo[] {
            new DevExpress.XtraEditors.Controls.LookUpColumnInfo("name", "名称", 90),
            new DevExpress.XtraEditors.Controls.LookUpColumnInfo("code", "编码", 90)});
      lookup.DisplayMember = "name";
      lookup.Name = "repositoryItemLookUpEdit_value" + FieldID.ToString();
      lookup.NullText = "";
      lookup.ValueMember = "name";
      return lookup;
    }

  }
}