clscreatestattable.cs

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

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

namespace dist.hb.EnvQueryStat.Stat
{
  class myItemInfo
  {
    public string ItemName;
    public string ItemCode;
  }

  class StatItem
  {
    public string fieldName;
    public string fieldCapion;
    public string statMethod;
    /// <summary>
    /// 统计字段类型，作为统计字段必须是数值型，0--整形,1--浮点
    /// </summary>
    public int FieldType;
    public bool isRenField;
  }

  /// <summary>
  /// 根据统计环境创建统计结果表
  /// </summary>
  class clsCreateStatTable
  {
    #region "类对象的初始化"
    ///统计表包括以下内容：
    ///1) 主分组项的编码;
    ///2) 主分组项的名称;
    ///3) 根据次分组项的项目数*统计字段，其字段名称为统计字段+次分组项编码
    public clsStatEnv StatEnv;
    public DataTable workTable;
    public clsCreateStatTable()
    {
      StatEnv = null;
      workTable = new DataTable();
      MainGroupList = new ArrayList();
      SecondGroupList = new ArrayList();
      StatItemList = new ArrayList();
    }
    /// <summary>
    /// 在使用该类前请调用该函数
    /// </summary>
    /// <returns></returns>
    public bool Initialize()
    {
      FillMainGroupList();
      FillStatAllItem();
      FillSecondGroupList();
      if (!CreateMainGroupField()) return false;
      return true;
    }
    #endregion

    #region "处理分组字段"
    //处理主分组(字段名称,编码名称)
    private bool CreateMainGroupField()
    {
      if (StatEnv == null || workTable == null) return false;
      if (MainGroupList == null || MainGroupList.Count == 0) return false;
      if(StatItemList == null || StatItemList.Count == 0) return false;
      workTable.Rows.Clear();
      workTable.Columns.Clear();

      System.Type stringType;
      stringType = System.Type.GetType("System.String");
      clsProcMainGroupField procField = new clsProcMainGroupField();
      procField.StatEnv = StatEnv;
      workTable.Columns.Clear();
      AddColumn(procField.GetMainFieldCode(), "编码", stringType);//增加编码字段
      AddColumn(procField.GetMainFieldName(), "名称", stringType);//增加名称字段

      System.Type intType = System.Type.GetType("System.Int32");
      System.Type doubleType = System.Type.GetType("System.Double");

      if (SecondGroupList == null || SecondGroupList.Count == 0)//没有次分组
      {
        foreach (StatItem var in StatItemList)
        {
          if (var.FieldType == 0) AddColumn(var.fieldName, var.fieldCapion, intType);
          else AddColumn(var.fieldName, var.fieldCapion, doubleType);
        }
      }
      else//根据次分组项的项目数*统计字段，其字段名称为统计字段+次分组项编码。
      {
        foreach (myItemInfo var1 in SecondGroupList)
        {
          foreach (StatItem var in StatItemList)
          {
            string fieldName = var.fieldName + "_" + var1.ItemCode;
            if (var.FieldType == 0) AddColumn(fieldName, var1.ItemName, intType);
            else AddColumn(fieldName, var1.ItemName, doubleType);
          }
        }
      }
      return true;
    }

    //获取主分组信息
    public ArrayList MainGroupList;
    private bool FillMainGroupList()
    {
      if (MainGroupList == null) return false;
      MainGroupList.Clear();
      string code = "";
      foreach (DictItem var in StatEnv.MainGrouList)
      {
        if (code == "") code = "'" + var.code + "'";
        else code = code + "," + "'" + var.code + "'";
      }

      string SQL;
      if (StatEnv.MainGroupType == 0)//地图分组
      {
        SQL = "select name,code from FS_XXQH_CODE where code in(" + code + ")";
      }
      else//StatEnv.MainGroupItem;//是字段，必须含有字典表
      {
        if (StatEnv.ProcAttFilter == null) return false;
        string dict = StatEnv.ProcAttFilter.GetFieldDICT(StatEnv.MainGroupItem);
        if (dict.Trim() == "") return false;
        SQL = "select name, code from (" + dict + ") as dict where code in(" + code + ")";////dict的格式必须如下：SELECT XX1 AS NAME,XX2 AS CODE FROM XX3
      }
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null) return false;
      MainGroupList.Clear();
      foreach (DataRow var in dt.Rows)
      {
        myItemInfo ItemInfo = new myItemInfo();
        ItemInfo.ItemName = var["name"].ToString();
        ItemInfo.ItemCode = var["code"].ToString();
        MainGroupList.Add(ItemInfo);
      }
      return true;
    }
    //获取次分组信息
    public ArrayList SecondGroupList;
    private bool FillSecondGroupList()
    {
      if (SecondGroupList == null) return false;
      if (StatEnv.SecondGroupField == null || StatEnv.SecondGroupField.Trim() == "") return false;
      if (StatEnv.SecondGroupList == null) return false;
      if (StatEnv.SecondGroupList.Count == 0) return false;
      SecondGroupList.Clear();
      string code = "";
      foreach (DictItem var in StatEnv.SecondGroupList)
      {
        if (code == "") code = "'" + var.code + "'";
        else code = code + "," + "'" + var.code + "'";
      }

      string SQL;

      if (StatEnv.ProcAttFilter == null) return false;
      string dict = StatEnv.ProcAttFilter.GetFieldDICT(StatEnv.SecondGroupField);
      if (dict.Trim() == "") return false;
      SQL = "select name, code from (" + dict + ") as dict where code in(" + code + ")";////dict的格式必须如下：SELECT XX1 AS NAME,XX2 AS CODE FROM XX3
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null) return false;
      SecondGroupList.Clear();
      foreach (DataRow var in dt.Rows)
      {
        myItemInfo ItemInfo = new myItemInfo();
        ItemInfo.ItemName = var["name"].ToString();
        ItemInfo.ItemCode = var["code"].ToString();
        SecondGroupList.Add(ItemInfo);
      }
      return true;
    }
    #endregion


    #region "处理统计项目"
    public ArrayList StatItemList;
    private bool FillStatAllItem()
    {
      if (StatItemList == null || StatEnv == null || StatEnv.ProcAttFilter == null) return false;
      if (StatEnv.StatItemTable == null || StatEnv.StatItemTable.Rows.Count == 0) return false;
      StatItemList.Clear();
      foreach (DataRow var in StatEnv.StatItemTable.Rows)
      {
        StatItem Item = new StatItem(); //StatField,StatMethod,IsRenField,其中StatField保存的是字段名称
        Item.statMethod = var["StatMethod"].ToString();
        if (var["IsRenField"].ToString() == "") Item.isRenField = false;
        else Item.isRenField = Convert.ToBoolean(var["IsRenField"]);

        string StatField = var["StatField"].ToString();
        clsFieldInfo FieldInfo = StatEnv.ProcAttFilter.GetFieldInfoFromAlias(StatField);
        if (FieldInfo == null) continue;
        Item.fieldCapion = FieldInfo.FieldAias;
        Item.fieldName = FieldInfo.FieldName;
        if (Item.statMethod == "count") Item.FieldType = 0;
        else if(FieldInfo.FieldType == ConstDataType.CInt) Item.FieldType = 0;
        else Item.FieldType = 1;
        StatItemList.Add(Item);
      }
      return true;
    }
    #endregion

    #region "辅助处理函数"
    bool AddColumn(string columnName,string columnCaption, Type dataType)
    {
      if (workTable == null) return false;
      DataColumn column = new DataColumn(columnName, dataType);
      column.Caption = columnCaption;
      workTable.Columns.Add(column);
      return true;
    }

    #endregion
  }
}