clsprocattwhere.cs.svn-base

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

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


namespace dist.hb.EnvQueryStat.Stat
{
  /// <summary>
  /// 处理属性过滤的WHERE子句
  /// </summary>
  class clsProcAttWhere
  {
    public clsStatEnv StatEnv;//查询环境
    public clsProcAttWhere()
    {
      StatEnv = null;
    }
    public string GetAttWhere(bool bAll)
    {
      string strWhere;
      if (bAll) strWhere = GetSqlUserInvisible() + GetSqlFromUserInput();
      else strWhere = GetSqlUserInvisible();
      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()
    {
      if (StatEnv == null || StatEnv.DbConn == null) return "";
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT SAFid,StatInstID,LeftBracket,Field,FieldName,relation,value1,RightBracket,logicSym,UserIsVisible "
       + "FROM FS_SAttFilter where UserIsVisible = 0 and StatInstID = " + StatEnv.StatID.ToString();
      OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, StatEnv.DbConn);

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

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

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

    //处理一行SQL语句
    private string ProcRowSql(DataRow var)
    {
      if (var == null || StatEnv.ProcAttFilter == null) return "";
      int SAFid = Convert.ToInt32(var["SAFid"]);
      string LeftBracket = var["LeftBracket"].ToString();
      int Field = Convert.ToInt32(var["Field"]);
      string relation = var["relation"].ToString();
      string value1 = var["value1"].ToString();
      string RightBracket = var["RightBracket"].ToString();
      string logicSym = var["logicSym"].ToString();
      //获取查询字段代码
      clsFieldInfo FieldInfo;
      FieldInfo = StatEnv.ProcAttFilter.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 (StatEnv ==null ||StatEnv.ProcAttFilter == null) return "";
      clsFieldInfo FieldInfo;
      FieldInfo = StatEnv.ProcAttFilter.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, StatEnv.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 (StatEnv == null || StatEnv.ProcAttFilter == null || StatEnv.ProcAttFilter.m_AttFilterTable == null ) return "";
      DataTable dt = StatEnv.ProcAttFilter.m_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();
    }
  }
}