clsattfieldproc.cs

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

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

namespace dist.hb.EnvQueryStat.Query
{
  //属性字段处理
  class clsAttFieldProc
  {
    #region "类模块初始化"
    public clsQueryHelp QueryHelp;
    public OleDbConnection DbConn;
    public int nQueryInstID;
    public clsAttFieldProc()
    {
      QueryHelp = null;
      DbConn = null;
      nQueryInstID = -1;
    }
    #endregion

    #region "处理WHERE子句"
    public string GetWhere()
    {
      string strWhere =  GetSqlUserInvisible() + " " +GetSqlFromUserInput();
      strWhere.Trim();
      strWhere = strWhere.ToLower();
      if (strWhere.Length >= 2)
      {
        string or = strWhere.Substring(strWhere.Length - 2, 2);
        if (or == "or") strWhere = strWhere.Substring(0, strWhere.Length - 2);
      }
      if(strWhere.Length >= 3)
      {
        string and = strWhere.Substring(strWhere.Length - 3, 3);
        if (and == "and") strWhere = strWhere.Substring(0, strWhere.Length - 3);
      }
      return strWhere;
    }

    //从数据库中获取用户不可见的SQL条件语句
    private string GetSqlUserInvisible()
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT QAFid,QueryInstID,LeftBracket,Field,FieldName,relation,value1,RightBracket,logicSym,UserIsVisible "
       + "FROM FS_QAttFilter where UserIsVisible = 0 and QueryInstID = " + nQueryInstID.ToString();
      OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, DbConn);

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

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

      Adapter.Fill(ds, "FS_QAttFilter");
      DataTable dt = ds.Tables["FS_QAttFilter"];
      string SQL = " ";
      foreach (DataRow var in dt.Rows)
      {
        SQL += ProcRowSql(var);
      }
      return SQL.Trim();
    }

    //处理一行SQL语句
    private string ProcRowSql(DataRow var)
    {
      if (var == null || QueryHelp == null) return "";
      int QAFid = 0;
      object obj;
      obj = var["QAFid"];
      if (obj != null && obj.ToString() != "") QAFid = Convert.ToInt32(var["QAFid"]);

      string LeftBracket = "";
      obj = var["LeftBracket"];
      if (obj != null && obj.ToString() != "") LeftBracket = Convert.ToString(var["LeftBracket"]);

      int Field = -1;
      obj = var["Field"];
      if (obj != null && obj.ToString() != "") Field = Convert.ToInt32(var["Field"]);


      string relation = "";
      obj = var["relation"];
      if (obj != null && obj.ToString() != "") relation = obj.ToString();

      string value1 = "";
      obj = var["value1"];
      if (obj != null && obj.ToString() != "") value1 = obj.ToString();

      string RightBracket = "";
      obj = var["RightBracket"];
      if (obj != null && obj.ToString() != "") RightBracket = obj.ToString();

      string logicSym = "";
      obj = var["logicSym"];
      if (obj != null && obj.ToString() != "") logicSym = obj.ToString();

      //获取查询字段代码
      clsFieldInfo FieldInfo;
      FieldInfo = QueryHelp.GetFieldInfo(Field);
      if(FieldInfo == null) return "";
      string FieldName = FieldInfo.FieldName.Trim();
      if (FieldName == "")
      {
        MessageBox.Show("字段名称为空!");
        return "";
      }

      //特殊处理字段值的字典值
      string Code;
      if(FieldInfo.DICT.Trim() != "")
        Code = GetFieldValueCode(Field, value1);
      else Code = value1;

      //对字符串特殊处理
      if (FieldInfo.FieldType == ConstDataType.CString && relation.ToLower() != "like")
      {
        Code = "'" + Code + "'";
      }
      else if (FieldInfo.FieldType == ConstDataType.CData)
      {
        char[] charSeparators = new char[] {' '};
        string[] strs= Code.Split(charSeparators);
        Code = "'"+strs[0]+"'";
      }
      //如果relation为like,要特殊处理
      if (relation.ToLower() == "like")
      {
        Code = "'%" + Code + "%'";////注：这里只是针对SQL SERVER
      }

      string SQL = " " + LeftBracket + FieldName + " " + relation + " " + Code + RightBracket + " " + logicSym;


      return SQL;
    }

    //将SQL条件语句中的字典名称代码化
    private string GetFieldValueCode(int iFieldID, string Value)
    {
      if(QueryHelp == null) return "";
      clsFieldInfo FieldInfo;
      FieldInfo = QueryHelp.GetFieldInfo(iFieldID);
      if(FieldInfo == null) return "";
      if(FieldInfo.DICT.Trim() == "" ) return "";

      DataSet ds = new DataSet();
      string SQL;
      string dict = FieldInfo.DICT.Trim().ToLower();

      SQL = "SELECT NAME, CODE FROM(" + dict + ") AS DICT" + " where name = '" + Value + "'";
      OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(SQL, DbConn);
      adapter.Fill(ds, "DICT");
      DataTable dt = ds.Tables["DICT"];
      if(dt.Rows.Count==0) return "";
      return dt.Rows[0]["code"].ToString();
    }

    //从用户的输入表中获取SQL语句
    private string GetSqlFromUserInput()
    {
      if (QueryHelp == null) return "";
      DataTable dt = QueryHelp.AttFilterTable;
      if (dt == null) return "";
      DataRow[] drs = dt.Select("UserIsVisible =1");
      if (drs == null) return "";
      string SQL = "";
      foreach (DataRow var in drs)
      {
        SQL += ProcRowSql(var);
      }
      return SQL.Trim();
    }
    #endregion

    #region "处理SELECT字段"
    //包含了主字段的选择语句
    public string GetSelectFields()
    {
      ArrayList AList = GetSelectFields1();
      if (AList == null) return "";
      ArrayList AList1 = GetMainField();
      if (AList1 != null)
      {
        for (int i = 0; i < AList1.Count; i++)
        {
          string fieldName = AList1[i].ToString();
          if (!AList.Contains(fieldName)) AList.Insert(0, fieldName);
        }
      }
      string strSELECT = "";
      for (int i = 0; i < AList.Count; i++)
      {
        if(strSELECT == "") strSELECT = AList[i].ToString();
        else strSELECT += ","+ AList[i].ToString();
      }
      return strSELECT;
    }
    public ArrayList GetSelectFields1()
    {
      if (QueryHelp == null) return null;
      ArrayList AList = new ArrayList();
      DataRow[] drs = QueryHelp.FieldInfoTable.Select("IsShow =1");
      if (drs == null) return null;
      foreach (DataRow var in drs)
      {
        AList.Add(var["FieldName"].ToString());
      }
      return AList;
    }
    public ArrayList GetMainField()
    {
      if (QueryHelp == null) return null;
      ArrayList AList = new ArrayList();
      DataRow[] drs = QueryHelp.FieldInfoTable.Select("IsMainField =1");
      if (drs == null) return null;
      foreach (DataRow var in drs)
      {
        AList.Add(var["FieldName"].ToString());
      }
      return AList;
    }
    #endregion

    #region "处理排序字段"
    public string GetOrderByFields()
    {
      if (QueryHelp == null) return "";
      DataRow[] drs = QueryHelp.FilterFieldInfoTable.Select("IsOrder =1");
      if (drs == null) return "";
      string Fields = "";
      foreach (DataRow var in drs)
      {
        if (Fields == "") Fields = var["FieldName"].ToString();
        else Fields += "," + var["FieldName"].ToString();
      }
      return Fields;
    }
    #endregion
  }
}