clsprocstat.cs

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

        if (sAttWhere.Trim() != "") SQL += " and " + sAttWhere;
        SQL +=  " GROUP BY " + AttCodeField + "," + AttNameField + "," + SecondGroupField;//次分组
        saveStat4(SQL);
      }
      return true;
    }

    //没有次分组，正常的统计(当主分组是地图时)
    private bool saveStat3(string SQL)
    {
      clsDbPub.SaveTextToFile(SQL);

      if (StatEnv == null || StatTable == null) return false;
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null || dt.Rows.Count == 0) return false;

      clsProcMainGroupField procField = new clsProcMainGroupField();
      procField.StatEnv = StatEnv;
      string AttCodeField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttField;
      string AttNameField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttNameField;
      for (int i = 0; i < dt.Rows.Count; i++)
      {
        DataRow dr = StatTable.workTable.NewRow();
        dr[procField.GetMainFieldCode()] = dt.Rows[i][AttCodeField];
        dr[procField.GetMainFieldName()] = dt.Rows[i][AttNameField];
        foreach (StatItem var in StatTable.StatItemList)
        {
         dr[var.fieldName] =  dt.Rows[i][var.fieldName];
        }
        StatTable.workTable.Rows.Add(dr);
      }
      return true;
    }

    //有次分组，特殊的统计
    private bool saveStat4(string SQL)
    {
      clsDbPub.SaveTextToFile(SQL);

      if (StatEnv == null || StatTable == null) return false;
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null || dt.Rows.Count == 0) return false;

      DataRow dr = null;
      clsProcMainGroupField procField = new clsProcMainGroupField();
      procField.StatEnv = StatEnv;
      string name = "";
      string OldName = "*&^%$#";
      string code = "";
      string AttCodeField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttField;
      string AttNameField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttNameField;
      string SecondGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.SecondGroupField);
      string SecondGroupCode;
      for (int i = 0; i < dt.Rows.Count; i++)
      {

        code = dt.Rows[i][AttCodeField].ToString();
        name = dt.Rows[i][AttNameField].ToString();
        if (name != OldName)
        {
          dr = StatTable.workTable.NewRow();
          StatTable.workTable.Rows.Add(dr);
        }
        SecondGroupCode = dt.Rows[i][SecondGroupField].ToString();
        dr[procField.GetMainFieldCode().ToString()] = code;
        dr[procField.GetMainFieldName().ToString()] = name;
        foreach (StatItem var in StatTable.StatItemList)
        {
          string ValueField = var.fieldName + "_" + SecondGroupCode;//code是次分组的编码
          dr[ValueField] = dt.Rows[i][var.fieldName];
        }
        OldName = name;
        
      }
      return true;
    }
    #endregion
    #region "处理情况3：主分组为属性"
    private bool Stat3()
    {
      if (StatEnv == null || StatTable == null || StatEnv.MapControl == null) return false;
      if (StatEnv.MainGroupType != 1) return false;//0表示，主分组为属性
      if (StatTable.MainGroupList == null || StatTable.StatItemList == null) return false;
      if (StatTable.MainGroupList.Count == 0 || StatTable.StatItemList.Count == 0) return false;

      clsProcAttWhere ProcWhere = new clsProcAttWhere();
      ProcWhere.StatEnv = StatEnv;
      string sAttWhere = ProcWhere.GetAttWhere(true);////????

      string view = StatEnv.ProcAttFilter.StatTemplateInfo.strVName;
      string code = "";
      foreach (myItemInfo var in StatTable.MainGroupList)
      {
        if (code == "") code = "'" + var.ItemCode + "'";
        else code = code + ",'" + var.ItemCode + "'";
      }

      string MainGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.MainGroupItem);
      string MainGroup = MainGroupField + " in (" + code + ")";

      clsProcMainGroupField MainField = new clsProcMainGroupField();
      MainField.StatEnv = StatEnv;

      //如果没有次分组
      if (StatEnv.SecondGroupField.Trim() == "" || StatTable.SecondGroupList == null || StatTable.SecondGroupList.Count == 0)
      {
        string SQL;
        SQL = "SELECT " + MainGroupField + "," + MakeStatField() + " FROM " + view + " WHERE "
                  + MainGroup;
        if (sAttWhere.Trim() != "") SQL += " and " + sAttWhere;
        SQL += " group by " + MainGroupField;
        saveStat5(SQL);//没有次分组，所有的统计记录
      }
      else//有次分组
      {
        string Second = "";
        foreach (myItemInfo var in StatTable.SecondGroupList)
        {
          if (Second == "") Second = "'" + var.ItemCode + "'";
          else Second += ",'" + var.ItemCode + "'";
        }
        string SecondGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.SecondGroupField);
        Second = SecondGroupField + " in(" + Second + ")";

        string SQL;
        SQL = "SELECT " + MainGroupField + "," + SecondGroupField + "," + MakeStatField() + " FROM " + view + " WHERE "
                  + MainGroup + " and " + Second;

        if (sAttWhere.Trim() != "") SQL += " and " + sAttWhere;
        SQL += " GROUP BY " +
                    MainGroupField + "," + SecondGroupField
                    + " order by " + MainGroupField + "," + SecondGroupField; //次分组
        saveStat6(SQL);
      }
      return true;
    }

    //没有次分组，正常的统计(当主分组是属性时)
    private bool saveStat5(string SQL)
    {
      clsDbPub.SaveTextToFile(SQL);

      if (StatEnv == null || StatTable == null) return false;
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null || dt.Rows.Count == 0) return false;

      clsProcMainGroupField procField = new clsProcMainGroupField();
      procField.StatEnv = StatEnv;
      string AttCodeField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttField;
      string AttNameField = StatEnv.ProcAttFilter.StatTemplateInfo.strAttNameField;

      string MainGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.MainGroupItem);
      for (int i = 0; i < dt.Rows.Count; i++)
      {
        DataRow dr = StatTable.workTable.NewRow();
        string code  = dt.Rows[i][MainGroupField].ToString();
        dr[procField.GetMainFieldCode()] = code;
        dr[procField.GetMainFieldName()] = GetAttMainFieldCaption(code);
        foreach (StatItem var in StatTable.StatItemList)
        {
          dr[var.fieldName] = dt.Rows[i][var.fieldName];
        }
        StatTable.workTable.Rows.Add(dr);
      }
      clsDbPub.SaveTextToFile(SQL);
      return true;
    }

    //有次分组，特殊的统计(主字段是属性)
    private bool saveStat6(string SQL)
    {
      clsDbPub.SaveTextToFile(SQL);

      if (StatEnv == null || StatTable == null) return false;
      DataTable dt = clsDbPub.GetDataTable(StatEnv.DbConn, SQL);
      if (dt == null || dt.Rows.Count == 0) return false;

      DataRow dr = null;
      clsProcMainGroupField procField = new clsProcMainGroupField();
      procField.StatEnv = StatEnv;
      string name = "";
      string OldName = "*&^%$#";
      string code = "";
      string MainGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.MainGroupItem);
      string SecondGroupField = StatEnv.ProcAttFilter.GetFieldName(StatEnv.SecondGroupField);
      string SecondGroupCode;
      for (int i = 0; i < dt.Rows.Count; i++)
      {

        code = dt.Rows[i][MainGroupField].ToString();
        name = GetAttMainFieldCaption(code);
        if (name != OldName)
        {
          dr = StatTable.workTable.NewRow();
          StatTable.workTable.Rows.Add(dr);
        }
        SecondGroupCode = dt.Rows[i][SecondGroupField].ToString();
        dr[procField.GetMainFieldCode().ToString()] = name;
        dr[procField.GetMainFieldName().ToString()] = code;
        foreach (StatItem var in StatTable.StatItemList)
        {
          string ValueField = var.fieldName + "_" + SecondGroupCode;//code是次分组的编码
          dr[ValueField] = dt.Rows[i][var.fieldName];
        }
        OldName = name;

      }
      clsDbPub.SaveTextToFile(SQL);
      return true;
    }

    private string GetAttMainFieldCaption(string code)
    {
      foreach (myItemInfo var in StatTable.MainGroupList)
      {
        if (var.ItemCode == code) return var.ItemName;
      }
      return "";
    }

      private string GetAttSecondFieldCaption(string code)
    {
      foreach (myItemInfo var in StatTable.SecondGroupList)
      {
        if (var.ItemCode == code) return var.ItemName;
      }
      return "";
    }
    #endregion


















  }
}