clsqueryhelp.cs

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

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.Query
{
  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 clsQueryInstInfo
  {
    public int QueryInstID;
    public int QueryClassID;
    public int AbstractQueryID;
    public string QueryInstName;
    public int AttWinVisible;
    public int SpWinVisible;

  }//查询实例信息

  //处理属性过滤
  class clsQueryHelp
  {
    #region "类初始化"
    private OleDbConnection m_OleDbConn;
    private int m_nQueryInstID;//查询ID
    private clsQueryTemplateInfo m_QueryTemplateInfo;//查询模板信息
    private clsQueryInstInfo m_QueryInstInfo;//查询信息
    private DataTable m_AttFilterTable;//属性过滤表
    private DataTable m_FilterFieldInfoTable;//过滤查询字段信息表
    private DataTable m_FieldInfoTable;//字段信息表
    private string m_strAttFilterTbl = "FS_QAttFilter";


    public clsQueryHelp()
    {
      m_nQueryInstID = -1;
      m_OleDbConn = null;
      m_FieldInfoTable = null;
      m_QueryTemplateInfo = new clsQueryTemplateInfo();
      m_QueryInstInfo = new clsQueryInstInfo();
      htItemLookUp = new Hashtable();
    }
    ~clsQueryHelp()
    {
    }
    #endregion

    #region "对外暴露的全局对象"
    public void Initialize(OleDbConnection OleDbConn, int QueryInstID)//在使用该类前要首先调用该函数
    {
      m_OleDbConn = OleDbConn;
      m_nQueryInstID = QueryInstID;
      LoadQueryInstInfo();
      LoadQueryTemplateInfo();
      LoadAttFilterTable();
      LoadFilterFieldsInfo();
      LoadFieldsInfo();
    }
    public DataTable AttFilterTable//获取属性过滤表
    {
      get { return m_AttFilterTable; }
    }
    public DataTable FilterFieldInfoTable//获取过滤字段信息表
    {
      get { return m_FilterFieldInfoTable; }
    }
    public DataTable FieldInfoTable//获取字段信息表
    {
      get { return m_FieldInfoTable; }
    }
    public clsQueryTemplateInfo QueryTemplateInfo//查询模板信息
    {
      get { return m_QueryTemplateInfo; }
    }
    public clsQueryInstInfo QueryInstInfo//查询信息
    {
      get { return m_QueryInstInfo; }
    }
    #endregion


    #region "相关信息加载函数"
    //获取查询实例的信息
    private void LoadQueryInstInfo()
    {
      string strSQL;
      strSQL = "SELECT QueryInstID,QueryClassID,AbstractQueryID,QueryInstName,AttWinVisible,SpWinVisible " +
                "FROM FS_QueryInst  where QueryInstID = " +m_nQueryInstID.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())
      {
        m_QueryInstInfo.QueryInstID = reader.GetInt32(0);
        m_QueryInstInfo.QueryClassID = reader.GetInt32(1);
        m_QueryInstInfo.AbstractQueryID = reader.GetInt32(2);
        m_QueryInstInfo.QueryInstName = reader.GetString(3);
        m_QueryInstInfo.AttWinVisible = reader.GetInt32(4);
        m_QueryInstInfo.SpWinVisible = reader.GetInt32(5);
      }
      reader.Close();
    }
    //加载查询模板信息
    private 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_QueryInstInfo.AbstractQueryID.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())
      {
        m_QueryTemplateInfo.strAbstractQueryName = tostring(reader.GetValue(0));
        m_QueryTemplateInfo.strAttField = tostring(reader.GetValue(1));
        m_QueryTemplateInfo.strLayerName = tostring(reader.GetValue(2));
        m_QueryTemplateInfo.strSpaceField = tostring(reader.GetValue(3));
        m_QueryTemplateInfo.strVName = tostring(reader.GetValue(4));
      }
      reader.Close();
    }

    private string tostring(object obj)
    {
      if (obj == null) return "";
      else return obj.ToString();
    }
    //处理属性过滤表
    private void LoadAttFilterTable()
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT QAFid,QueryInstID,LeftBracket,Field,FieldName,relation,value1,RightBracket,logicSym,UserIsVisible "
       + "FROM " + m_strAttFilterTbl + " where UserIsVisible = 1 and 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);
      m_AttFilterTable = ds.Tables[m_strAttFilterTbl];
      for (int i = 0; i < m_AttFilterTable.Rows.Count; i++)
			{
        DataRow dr = m_AttFilterTable.Rows[i];
        dr["UserIsVisible"] = true;
			}
    }
    //加载过滤查询字段信息
    private void LoadFilterFieldsInfo()
    {
      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_QueryInstInfo.AbstractQueryID.ToString();

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

      adapter.Fill(ds, "FilterFieldsInfo");
      m_FilterFieldInfoTable = ds.Tables["FilterFieldsInfo"];
    }
    //加载查询字段信息
    private 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 AbstractQueryID = " + m_QueryInstInfo.AbstractQueryID.ToString();

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

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


    #region "获取字段信息函数"
    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"]);
      object obj = drs[0]["IsUserVisible"];
      if (obj.ToString() == "") FieldInfo.IsUserVisible = false;
      else FieldInfo.IsUserVisible = Convert.ToBoolean(obj);
      return FieldInfo;
    }
    public clsFieldInfo GetFieldInfo(string FieldName)
    {
      if (m_FieldInfoTable == null) return null;
      DataRow[] drs = m_FieldInfoTable.Select("FieldName = '" + FieldName.ToString() + "'");
      if (drs == null) return null;
      if(drs.Length == 0) 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"]);
      object obj = drs[0]["IsUserVisible"];
      if (obj.ToString() == "") FieldInfo.IsUserVisible = false;
      else FieldInfo.IsUserVisible = Convert.ToBoolean(obj);
      return FieldInfo;
    }
    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 bool IsStringType(string fieldName)
    {
      if (m_FieldInfoTable == null) return false;
      DataRow[] drs = m_FieldInfoTable.Select("FieldName = '" + fieldName.ToString() + "'");
      if (drs == null) return false;
      if (drs.Length == 0) return false;
      string FieldType = drs[0]["FieldType"].ToString();
      if (FieldType == ConstDataType.CString) return true;
      else return false;
    }
    #endregion

    #region "处理grid下拉列表框的函数"
    //处理属性过滤环境
    public void FillrepositoryItemComboBox_field(RepositoryItemLookUpEdit repositoryItemLookUpEdit_field1)
    {
      if (repositoryItemLookUpEdit_field1 == null || m_FilterFieldInfoTable == null) return;
      repositoryItemLookUpEdit_field1.DataSource = m_FilterFieldInfoTable;
      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_FilterFieldInfoTable.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];
    }
    #endregion

  }
}