你的位置:首页 > ASP.net教程

[ASP.net教程]记一次项目中的查询汇总


  项目要实现查询汇总的功能,针对不同的分组实现不同的汇总。直接上图吧,直观一点。要实现的效果如下图所示。

 

  设计思路:第一,先实现电业局,变电工区,运维站,变电所相同的列名称,能够合并的功能。第二,在合适的位置插入汇总行(即有总计的行)。

  实现方法,第一,相同的列名称合并的功能,很简单,设置要合并的列的列属性AllowMerge=true,并不总的GridView的AllowMerge设为true即可。

  第二,主要难点在怎么实现汇总的功能。数据库中的获取的数据如下图所示:

  数据说明:

               PERSONID,      //人员ID              
               PERSONNAME, //人员姓名          READTICKETCOUNT, //审核步数          EXECUTETICKETCOUNT, //操作步数          LOOKUPTICKETCOUNT, //监护步数          DUTYTICKETCOUNT, //值班负责步数          TOTALTICKETCOUNT, //总计          WRITETICKETCOUNT //拟票步数

 

   既然要显示电业局,变电工区,运维站,变电所,就要在数据表中添加对应的列,添加代码如下:

 string[] columnNames =      {        DbFiledName.STATIONID.ToString() ,//电业局ID和名称        DbFiledName.STATION.ToString(),        DbFiledName.YWZ.ToString(),    //变电工区ID和名称        DbFiledName.YWZID.ToString(),                DbFiledName.BDGQ.ToString(),   //运维站ID和名称        DbFiledName.BDGQID.ToString(),        DbFiledName.DYJ.ToString(),    //变电所ID和名称        DbFiledName.DYJID.ToString()      };        _commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable);

  _commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable)函数如下所示:

 public void AddColumnToDataTable(IEnumerable<string> columnNameList, List<string> primaryKey ,ref DataTable dtTable)    {      if (ReferenceEquals(columnNameList, null))      {        return;      }      foreach (var columnName in columnNameList)      {        DataColumn dcColumn = new DataColumn(columnName, typeof(string));          dtTable.Columns.Add(dcColumn);      }      if (ReferenceEquals(primaryKey,null)||primaryKey.Count<=0)      {        return;      }      DataColumn[] columns=new DataColumn[primaryKey.Count];      for (int i = 0; i < primaryKey.Count; i++)      {        columns[i]=new DataColumn(primaryKey[i],typeof(string));      }      dtTable.PrimaryKey = columns;  //要设置主键,才能合并。    }

  既然列已经添加就要向列中添加数据,怎么获取数据呢? 在构造选择树的时候,已经为树中的节点传入了必要的数据。树的显示图和传入数据为:

 

  

 TreeListNode parentNode = tree.AppendNode(new object[] {
                     row["OrganizationId"], row["OrganizationName"], row["ParentId"], row["DeptClass"], row["Buro"],
                    "Organization", CheckState.Unchecked }, rootNode);

 

  既然知道了树的节点中有需要的信息,那就遍历树,获取电业局,变电工区,运维站,变电所。

 foreach (var personId in personIdList)        {          DataRow dr = dtTable.Rows.Find(personId);          //用户          TreeListNode childNode = this.comboxOrgANdUserTree1.treeListOrganization.FindNodeByFieldValue(DbFiledName.PARENTID.ToString(), personId);          if (childNode != null)          {            TreeListNode parentTreeListNode = childNode.ParentNode; //父节点,变电所            if (!ReferenceEquals(parentTreeListNode, null))     //3 class            {              string stationId = parentTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();              dr[DbFiledName.STATIONID.ToString()] = stationId;              dr[DbFiledName.STATION.ToString()] = parentTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];              TreeListNode parentTreeListNodeNext = parentTreeListNode.ParentNode;//运维站              if (parentTreeListNodeNext != null) //2 class              {                string ywzId = parentTreeListNodeNext[DbFiledName.ORGANIZATIONID.ToString()].ToString();                dr[DbFiledName.YWZID.ToString()] = ywzId;                dr[DbFiledName.YWZ.ToString()] = parentTreeListNodeNext[DbFiledName.ORGANIZATIONNAME.ToString()];                TreeListNode grandFatherTreeListNode = parentTreeListNodeNext.ParentNode; //父节点的父节点,工区                if (grandFatherTreeListNode != null)          //1 class                {                  //DbFiledName.BDGQ.ToString()                  string tempbdgqid =                    grandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();                  dr[DbFiledName.BDGQ.ToString()] =                    grandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];                  dr[DbFiledName.BDGQID.ToString()] = tempbdgqid;                  TreeListNode greatGrandFatherTreeListNode = grandFatherTreeListNode.ParentNode;//电业局                  //父节点的父节点的父节点                  if (greatGrandFatherTreeListNode != null) //0 class                  {                    string dyjid =                      greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();                    dr[DbFiledName.DYJ.ToString()] =                      greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];                    dr[DbFiledName.DYJID.ToString()] = dyjid;                  }                  else                  {                    dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.BDGQ.ToString()];                    dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.BDGQID.ToString()];                    dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.YWZ.ToString()];                    dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.YWZID.ToString()];                    dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                    dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                  }                }                else                {                  dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()];                  dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()];                  dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()];                  dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                  dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                  dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                }              }              else              {                dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()];                dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()];                dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()];                //dr[DbFiledName.STATIONID.ToString()] = "";                //dr[DbFiledName.STATION.ToString()] = "";              }            }          }        }

View Code

 

  数据表的电业局,变电工区,运维站,变电所已经获取了,接下来就要汇总数据,添加“总计”行。

        //求和之0,变电站        stationIdList = dtTable.AsEnumerable().Select(x => x.Field<string>(DbFiledName.STATIONID.ToString())).Distinct().ToList();        for (int i = 0; i < stationIdList.Count; i++)        {          //DataRow dr = dtTable.NewRow();          string stationId = stationIdList[i];          var exprssionOne = dtTable.AsEnumerable()            .Where(x => x.Field<string>(DbFiledName.STATIONID.ToString()) == stationId);          var oneList = exprssionOne.ToList();          if (oneList == null || oneList.Count <= 0)          {            continue;          }          DataRow dr = dtTable.NewRow();          DataRow drTemp = oneList[0];          dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()];          dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()];          dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()];          dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()];          dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()];          dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计:";          dr[DbFiledName.STATION.ToString()] = drTemp[DbFiledName.STATION.ToString()];          dr[DbFiledName.STATIONID.ToString()] = drTemp[DbFiledName.STATIONID.ToString()];          dr[DbFiledName.PERSONNAME.ToString()] = CountName;//CountName是“总计”          long m;    //PERSONID是主键,所以,获取最小的主键并减1,作为“总计”列的主键,从而避免重复。          long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1;          dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString();          personIdList.Add((newPersonId).ToString());          GetSumColumn(ref dtTable, ref dr, DbFiledName.STATIONID.ToString(), stationId, 1);          AddNewRow(ref dtTable, i, stationIdList, DbFiledName.STATIONID.ToString(), ref dr);        }

 

   GetSumColumn和AddNewRow函数代码:

     //获取列的和    private void GetSumColumn(ref DataTable dtTable, ref DataRow newRow, string id, string tempId, int div)    {      try      {        Func<List<long>, int, string> getSumFunc = (list, dicTemp) =>       (list == null || list.Count <= 0) ? "0" : (list.Sum() / dicTemp).ToString();        Action<DataTable, DataRow, string> resultAction = (dttable, dr, columnname) =>        {          var sumColumn = dttable.AsEnumerable()                       .Where(x => x.Field<string>(id) == tempId)                       .Select(x => x.Field<long>(columnname))                       .ToList();          dr[columnname] = getSumFunc(sumColumn, div);        };        resultAction(dtTable, newRow, DbFiledName.READTICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.EXECUTETICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.LOOKUPTICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.TOTALTICKETCOUNT.ToString());        resultAction(dtTable, newRow, DbFiledName.WRITETICKETCOUNT.ToString());      }      catch (Exception ex)      {        _throwExceptionAction(ex.Message);      }    }

 

    //添加新行    private void AddNewRow(ref DataTable dtTable, int i, IList<string> idList, string id, ref DataRow dr)    {      try      {        int j = i;        if (j < idList.Count - 1)        {          var exprssionNext = dtTable.AsEnumerable()            .Where(x => x.Field<string>(id) == idList[j + 1]);          if (exprssionNext.ToList() == null || exprssionNext.ToList().Count <= 0)          {            return;          }          DataRow nextRow = exprssionNext.ToList()[0];          int index = dtTable.Rows.IndexOf(nextRow);          dtTable.Rows.InsertAt(dr, index);        }        else        {          dtTable.Rows.Add(dr);        }      }      catch (Exception ex)      {        _throwExceptionAction(ex.Message);      }    }

 

  其他的求和和电业局求和差不多,不在贴代码了。现在所有的数据就已经有了,对数据的排列问题阻扰了我好久,最开始的时候,我是用LINQ的GroupBy来排序,结果不能显示我要的数据。我的实现是,先对变电工区汇总,把分组完的数据分开,在对各个分开的数据中的运维站汇总,依次类推。在汇总之前,先把表中获得数据转化成List<T>中。转换代码不再给出。最后得到了需要的数据,效果如最开始的图所示。分组代码如下:

        //工区        List<StepCountClass> resultClassList = new List<StepCountClass>();        List<string> tempbdgqIdList = stepCountList.Select(x => x.BDGQID).Distinct().ToList();        for (int i = 0; i < tempbdgqIdList.Count; i++)        {          //相同变电区的组          var temp = stepCountList.Where(x => x.BDGQID == tempbdgqIdList[i] && x.BDGQ != CountName            && !string.IsNullOrEmpty(x.BDGQ)).ToList();          //相同运维站的组          var tempywzIdList = temp.Select(x => x.YWZID).Distinct().ToList();          for (int j = 0; j < tempywzIdList.Count; j++)          {            var ywztemp = temp.Where(x => x.YWZID == tempywzIdList[j]               && x.YWZ != CountName && !string.IsNullOrEmpty(x.YWZ)).ToList();            //相同变电所的组            var stationidList = ywztemp.Select(x => x.STATIONID).Distinct().ToList();            for (int k = 0; k < stationidList.Count; k++)            {              var stationtemp =                  ywztemp.Where(x => x.STATIONID == stationidList[k]                    && x.STATION != CountName && !string.IsNullOrEmpty(x.STATION)).ToList();              //相同用户              var personidList = stationtemp.Select(x => x.PERSONID).ToList();              for (int l = 0; l < personidList.Count; l++)              {                var persontemp =                  stationtemp.Where(x => x.PERSONID == personidList[l]                    && x.PERSONNAME != CountName && !string.IsNullOrEmpty(x.PERSONNAME))                    .ToList();                resultClassList.AddRange(persontemp);                if (l == personidList.Count - 1)                {                  var personCount = stationtemp.Where(x => x.PERSONNAME == CountName).ToList();                  resultClassList.AddRange(personCount);                }              }              //resultClassList.AddRange(stationtemp);              if (k == stationidList.Count - 1)              {                var stationCount = ywztemp.Where(x => x.STATION == CountName).ToList();                resultClassList.AddRange(stationCount);              }            }            if (j == tempywzIdList.Count - 1)            {              var ywzCount = temp.Where(x => x.YWZ == CountName).ToList();              resultClassList.AddRange(ywzCount);            }          }

 

  本来以为就做完了,心想万事大吉,可是漏掉了用户权限这一点。如果用户的权限是电业局的话,上面正好符合要求,但是如果用户的权限是,变电工区,那么就查询不到其他工区的数据,只能查到他权限内的数据。现在已运维站为例,给出解决方法。先给出树结构图和最后的汇总效果就明白了。

 

  从图中可以看出仅仅显示了,用户所在运维站(金华操作站)中的树结构。那么右边区域,也要仅仅显示到运维站,电业局、变电工区这两列。如下图所示:

 

  this.comboxOrgANdUserTree1是一个用户控件,RootDeptClass是根节点的级别,0代表电业局,1代表变电工区,2代表运维站,3代表变电所。

 

 switch (this.comboxOrgANdUserTree1.RootDeptClass)          {            case "0":              break;            case "1":              this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;              var dataRowList =                _queryResultDt.AsEnumerable().Where(x => x.Field<string>("YWZ") == CountName).ToList();              foreach (var row in dataRowList)              {                _queryResultDt.Rows.Remove(row);              }              this.gridView1.Columns["DYJ"].Caption = @"变电工区";              this.gridView1.Columns["BDGQ"].Caption = @"运维站";              //_queryResultDt.Rows.RemoveAt(_queryResultDt.Rows.Count-1);              break;            case "2":              this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;              this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false;              deleteAction(DbFiledName.BDGQ.ToString());              deleteAction(DbFiledName.YWZ.ToString());              deleteAction(DbFiledName.STATION.ToString());                           List<string> personIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.PERSONID.ToString())).ToList();              List<string> ywzIdList = new List<string>();              ywzIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.DYJID.ToString())).Distinct().ToList();              for (int i = 0; i < ywzIdList.Count; i++)              {                //DataRow dr = dtTable.NewRow();                string ywzIdTemp = ywzIdList[i];                var exprssionOne = _queryResultDt.AsEnumerable()                  .Where(x => x.Field<string>(DbFiledName.YWZID.ToString()) == ywzIdTemp);                var oneList = exprssionOne.ToList();                if (oneList == null || oneList.Count <= 0)                {                  continue;                }                DataRow dr = _queryResultDt.NewRow();                DataRow drTemp = oneList[0];                //DataRow drTemp = exprssionOne.ToList()[0];                dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()];                dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()];                dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()];                dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()];                dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()];                dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计:";                dr[DbFiledName.STATION.ToString()] = CountName;                long m;                long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1;                dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString();                dr[DbFiledName.PERSONNAME.ToString()] = "";                personIdList.Add((newPersonId).ToString());                //dr[dyjName] = drTemp[dyjName];                GetSumColumn(ref _queryResultDt, ref dr, DbFiledName.YWZID.ToString(), ywzIdTemp, 2);                AddNewRow(ref _queryResultDt, i, ywzIdList, DbFiledName.YWZID.ToString(), ref dr);              }              //_queryResultDt.AsEnumerable().Where(x=>x.Field<string>("YWZ")||x.Field<string>("BDGQ"))              this.gridView1.Columns["DYJ"].Caption = @"运维站";              break;            case "3":              this.gridView1.Columns.ColumnByFieldName("DYJ").Visible = false;              this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false;              this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;              deleteAction(DbFiledName.DYJ.ToString());              deleteAction(DbFiledName.BDGQ.ToString());              deleteAction(DbFiledName.YWZ.ToString());              deleteAction(DbFiledName.STATION.ToString());              break;            default:              break;          }

 

this.comboxOrgANdUserTree1.RootDeptClass和 deleteAction的实现方法分别为:


    //获取根节点等级    public string RootDeptClass    {      get      {        for (int i = 0; i < this.treeListOrganization.AllNodesCount; i++)        {          TreeListNode node = this.treeListOrganization.FindNodeByID(i);          if (node == null)            continue;          //DeptClass= node["DEPTCLASS"]          var deptClass = node["DEPTCLASS"].ToString();          if (string.IsNullOrEmpty(deptClass)) //所有用户的DEPTCLASS被设置成“”            continue;          long deptClassLong;          //bool flag= long.TryParse(deptClass, out deptClassLong);          if (long.TryParse(deptClass, out deptClassLong))          {            DeptClass = deptClassLong < DeptClass ? deptClassLong : DeptClass;          }        }        return DeptClass.ToString();      }    }

 

 Action<string> deleteAction = fieldName =>          {            var dataRowList =              _queryResultDt.AsEnumerable().Where(x => x.Field<string>(fieldName) == CountName).ToList();            foreach (var row in dataRowList)            {              _queryResultDt.Rows.Remove(row);            }          };

 

  这样才算完成基本,代码还要好好重构一下,大致解决思路和主要代码都贴出来,时间仓促,以后好好润色。