clsprocattfilter.cs

以构建的方式来实现对通用的查询统计

      DataSet ds = new DataSet();
      string strSQL = "SELECT FS_VTFieldDes.VFieldID as VFieldID, FieldName, FieldAias,FieldType, DICT, " +
               " IsStat,IsFilter, IsOrder, IsMainField,IsUserVisible, IsGroup " +
               " FROM FS_StatSet INNER JOIN FS_VTFieldDes ON FS_StatSet.VFieldID = FS_VTFieldDes.VFieldID " +
               "where IsFilter = 1 and AbstractStatID = " + m_nAbstractStatID.ToString();

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

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

    #region "处理字段信息"
    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, " +
               " IsStat,IsFilter, IsOrder, IsMainField,IsUserVisible, IsGroup " +
               " FROM FS_StatSet INNER JOIN FS_VTFieldDes ON FS_StatSet.VFieldID = FS_VTFieldDes.VFieldID " +
               "where AbstractStatID = " + m_nAbstractStatID.ToString();

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

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

    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.IsStat = Convert.ToBoolean(drs[0]["IsStat"]);
      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"]);
      if (drs[0]["IsGroup"] == null || drs[0]["IsGroup"].ToString() == "") FieldInfo.IsGroup = false;
      else  FieldInfo.IsGroup = Convert.ToBoolean(drs[0]["IsGroup"]);
      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.IsStat = Convert.ToBoolean(drs[0]["IsStat"]);
      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"]);
      FieldInfo.IsGroup = Convert.ToBoolean(drs[0]["IsGroup"]);
      return FieldInfo;
    }
    public clsFieldInfo GetFieldInfoFromAlias(string FieldAlias)
    {
      if (m_FieldInfoTable == null) return null;
      DataRow[] drs = m_FieldInfoTable.Select("FieldAias = '" + FieldAlias.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.IsStat = Convert.ToBoolean(drs[0]["IsStat"]);
      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"]);
      if (drs[0]["IsGroup"].ToString() == "") FieldInfo.IsGroup = false;
      else FieldInfo.IsGroup = Convert.ToBoolean(drs[0]["IsGroup"]);
      return FieldInfo;
    }

    //获取统计字段
    public DataRow[] GetStatField()
    {
      if (m_FieldInfoTable == null) return null;
      DataRow[] rows = m_FieldInfoTable.Select("IsStat = 1");
      if (rows == null) return null;
      if (rows.Length == 0) return null;
      return rows;
    }

    //获取一条字段信息
    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 DataTable GetFieldInfoTable()
    {
      return m_FieldInfoTable;
    }

    //根据字段别名获取字段名
    public string GetFieldName(string FieldAias)
    {
      DataRow[] rows = m_FieldInfoTable.Select("FieldAias = '" + FieldAias.ToString() + "'");
      if (rows == null) return "";
      if (rows.Length == 0) return "";
      return rows[0]["FieldName"].ToString();
    }

    //根据字段名获取字段别名
    public string GetFieldAias(string FieldName)
    {
      DataRow[] rows = m_FieldInfoTable.Select("FieldName = '" + FieldName.ToString() + "'");
      if (rows == null) return "";
      if (rows.Length == 0) return "";
      return rows[0]["FieldAias"].ToString();
    }
    public string GetFieldDICT(string FieldAias)
    {
      DataRow[] rows = m_FieldInfoTable.Select("FieldAias = '" + FieldAias.ToString() + "'");
      if (rows == null) return "";
      if (rows.Length == 0) return "";
      return rows[0]["DICT"].ToString();
    }
    #endregion

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