Asp.net管理信息系统中数据统计功能的实现

数据统计是每个系统中必备的功能，在给领导汇报统计数据，工作中需要的进展数据时非常有用。

在我看来，一个统计的模块应该实现以下功能：

• 能够将常用的查询的统计结果显示出来；
• 显示的结果可以是表格形式，也可以是图形形式，如果是图形的话能够以多种形式显示（柱状图、折线图、饼图、雷达图、堆叠柱状图等）：
• 统计查询的结果，点击数字或者百分比能够显示详细的数据；
• 能够自由组合查询条件、筛选条件、分组条件、排序等；
• 统计结果最好有个实时预览；
• 查询统计能够保存，以便下次能直接调用并显示统计查询的结果；
• 对于保存后的查询统计，下次调用时也可以按照灵活的筛选手段对查询结果进行筛选；
• 界面需要做的简洁、直观，就算是不太懂电脑的操作员也能够方便使用；
• 对于一些复杂的查询，能够直接在后台写Sql或者调用Sp出数据
• ......

好了，以下是在实际环境中的实现和应用：
这是一个学生的就业系统，学生在不同的时期会对自己毕业去向进行登记，因此按照不同时间截点统计出来的数据是不一样的。数据表有100多个字段（并不是所有字段都需要统计）。

首先，我们在数据库中构建一个表值函数，能够按照不同的时间截点返回出数据，表也起到视图的作用，将参数表的值直接包含到返回结果中去。

  View Code

这样我们使用 select * from [get...]('2016-8-25') 就可以查询出8月25日截止日期的数据。

接下来是界面设计，我们使用jequery-ui中dropable\dragable的控件，字段排列在界面上，直接拖拽到相应域里，就能够进行统计。

除了分组字段外，显示字段还能够按照具体的值进行统计过滤，起到多重分组统计的功能。

 

 大家可以看到，最上面一栏是数据筛选，然后是系统已经保存的查询（分为表格查询和图形查询），点击保存好的查询直接出查询结果，也可以删除保存的查询。在下面是自定义查询，上面是一排条件，然后是可以拖拽的字段，当字段拖至分组列，则显示字段名称；拖至显示列，还可以对显示的数据的具体值进行分组筛选统计。下方则是一些选项，是否显示小计、总计，以何种方式显示图表。

以表格形式的显示统计，可以看到，每个数值都可以点击弹出框显示详情，最下方能够保存查询条件，以图形方式显示等：

图形的展示：

 

 

以下是核心类InquireHelper.cs：
字段实体类（部分）

  1     [Serializable]
  2     [XmlInclude(typeof(BYNF_InquireField))]
  3     [XmlInclude(typeof(Count_InquireField))]
  4     [XmlInclude(typeof(XYMC_InquireField))]
  5     [XmlInclude(typeof(ZYMC_InquireField))]
  6     [XmlInclude(typeof(SZBJ_InquireField))]
  7     [XmlInclude(typeof(FDY_InquireField))]
  8     [XmlInclude(typeof(XL_InquireField))]
  9     [XmlInclude(typeof(SYDQ_InquireField))]
 10     [XmlInclude(typeof(SYSF_InquireField))]
 11     [XmlInclude(typeof(DWDQ_InquireField))]
 12     [XmlInclude(typeof(DWSF_InquireField))]
 13     [XmlInclude(typeof(HYML_InquireField))]
 14     [XmlInclude(typeof(HYDL_InquireField))]
 15     [XmlInclude(typeof(XBMC_InquireField))]
 16     [XmlInclude(typeof(MZMC_InquireField))]
 17     [XmlInclude(typeof(BYQX_InquireField))]
 18     [XmlInclude(typeof(KNSLB_InquireField))]
 19     [XmlInclude(typeof(ZYDKL_InquireField))]
 20     [XmlInclude(typeof(DWXZ_InquireField))]
 21     [XmlInclude(typeof(EJBYQXMC_InquireField))]
 22     [XmlInclude(typeof(GZ_InquireField))]
 23     [XmlInclude(typeof(WYJE_InquireField))]
 24     public abstract class InquireFieldBase
 25     {
 26         public InquireFieldBase()
 27         {
 28             FieldItems = this.GetInquireItemsByInquireType();
 29         }
 30 
 31         [XmlAttribute]
 32         public int FieldDisplayOrder { get; set; }
 33         [XmlAttribute]
 34         public string FieldName { get; set; }
 35         [XmlAttribute]
 36         public string DbName { get; set; }
 37         [XmlAttribute]
 38         public bool IsAggregate { get; set; }
 39         [XmlAttribute]
 40         public InquireHelper.FieldType FieldType { get; set; }
 41         
 42         //用于highchart统计
 43         [XmlAttribute]
 44         public bool IsNameField { get; set; }
 45 
 46         //用于统计输出数据
 47         [XmlAttribute]
 48         public bool IsPercent { get; set; }
 49 
 50         [XmlIgnore]
 51         public List<string> FieldItems { get; set; }
 52         public List<string> FieldValue { get; set; }
 53         public bool? OrderByAsc { get; set; }
 54     }
 55     [Serializable]
 56     public class BYNF_InquireField : InquireFieldBase
 57     {
 58         public BYNF_InquireField()
 59         {
 60             FieldDisplayOrder = 1;
 61             FieldName = "毕业年份";
 62             DbName = "BYNF";
 63         }
 64     }
 65     [Serializable]
 66     public class XYMC_InquireField : InquireFieldBase
 67     {
 68         public XYMC_InquireField()
 69         {
 70             FieldDisplayOrder = 5;
 71             FieldName = "学院名称";
 72             DbName = "XYMC";
 73         }
 74     }
 75     [Serializable]
 76     public class ZYMC_InquireField : InquireFieldBase
 77     {
 78         public ZYMC_InquireField()
 79         {
 80             FieldDisplayOrder = 6;
 81             FieldName = "专业名称";
 82             DbName = "ZYMC";
 83         }
 84     }
 85     [Serializable]
 86     public class SZBJ_InquireField : InquireFieldBase
 87     {
 88         public SZBJ_InquireField()
 89         {
 90             FieldDisplayOrder = 7;
 91             FieldName = "所在班级";
 92             DbName = "SZBJ";
 93         }
 94     }
 95     [Serializable]
 96     public class FDY_InquireField : InquireFieldBase
 97     {
 98         public FDY_InquireField()
 99         {
100             FieldDisplayOrder = 8;
101             FieldName = "辅导员";
102             DbName = "FDY";
103         }
104     }
105     [Serializable]
106     public class XL_InquireField : InquireFieldBase
107     {
108         public XL_InquireField()
109         {
110             FieldDisplayOrder = 9;
111             FieldName = "学历";
112             DbName = "XLMC";
113         }
114     }
115     [Serializable]
116     public class SYDQ_InquireField : InquireFieldBase
117     {
118         public SYDQ_InquireField()
119         {
120             FieldDisplayOrder = 10;
121             FieldName = "生源地区";
122             DbName = "SYDQ";
123         }
124     }
125     [Serializable]
126     public class SYSF_InquireField : InquireFieldBase
127     {
128         public SYSF_InquireField()
129         {
130             FieldDisplayOrder = 11;
131             FieldName = "生源省份";
132             DbName = "SYSF";
133         }
134     }
135     [Serializable]
136     public class DWDQ_InquireField : InquireFieldBase
137     {
138         public DWDQ_InquireField()
139         {
140             FieldDisplayOrder = 12;
141             FieldName = "单位地区";
142             DbName = "DWDQ";
143         }
144     }
145     [Serializable]
146     public class DWSF_InquireField : InquireFieldBase
147     {
148         public DWSF_InquireField()
149         {
150             FieldDisplayOrder = 13;
151             FieldName = "单位省份";
152             DbName = "DWSF";
153         }
154     }

控制类

  1     public static class InquireHelper
  2     {
  3         public static List<InquireFieldBase> GetSubInquireList()
  4         {
  5             var inquires = new List<InquireFieldBase>();
  6             var subTypeQuery = from t in Assembly.GetExecutingAssembly().GetTypes()
  7                                where IsSubClassOf(t, typeof(InquireFieldBase))
  8                                select t;
  9 
 10             foreach (var type in subTypeQuery)
 11             {
 12                 InquireFieldBase obj = CreateObject(type.FullName) as InquireFieldBase;
 13                 if (obj != null)
 14                 {
 15                     inquires.Add(obj);
 16                 }
 17             }
 18             return inquires;
 19 
 20         }
 21 
 22         static bool IsSubClassOf(Type type, Type baseType)
 23         {
 24             var b = type.BaseType;
 25             while (b != null)
 26             {
 27                 if (b.Equals(baseType))
 28                 {
 29                     return true;
 30                 }
 31                 b = b.BaseType;
 32             }
 33             return false;
 34         }
 35         /// <summary>
 36         /// 创建对象（当前程序集）
 37         /// </summary>
 38         /// <param name="typeName">类型名</param>
 39         /// <returns>创建的对象，失败返回 null</returns>
 40         public static object CreateObject(string typeName)
 41         {
 42             object obj = null;
 43             try
 44             {
 45                 Type objType = Type.GetType(typeName, true);
 46                 obj = Activator.CreateInstance(objType);
 47             }
 48             catch (Exception ex)
 49             {
 50                 
 51             }
 52             return obj;
 53         }
 54 
 55         public static List<InquireFieldBase> BindCondition(this List<InquireFieldBase> conditions, string conditionName, List<string> values)
 56         {
 57             var condition = conditions.FirstOrDefault(c => c.GetType().Name == conditionName && c.FieldType == FieldType.ConditionField);
 58 
 59             if (condition == null)
 60             {
 61                 condition = CreateObject("BLL." + conditionName) as InquireFieldBase;
 62                 condition.FieldType = FieldType.ConditionField;
 63                 conditions.Add(condition);
 64             }
 65 
 66             condition.FieldValue = values;
 67 
 68             return conditions;
 69         }
 70         //public static List<InquireFieldBase> BindCondition(this List<InquireFieldBase> conditions, string conditionName, string range1, string range2)
 71         //{
 72         //    var condition = conditions.FirstOrDefault(c => c.GetType().Name == conditionName && c.FieldType == FieldType.ConditionField);
 73 
 74 
 75         //    if (!string.IsNullOrEmpty(range2)&&!string.IsNullOrEmpty(range1))
 76         //    {
 77         //        if (condition == null)
 78         //        {
 79         //            condition = CreateObject("BLL." + conditionName) as InquireFieldBase;
 80         //            condition.FieldType = FieldType.ConditionField;
 81         //            conditions.Add(condition);
 82         //        }
 83 
 84         //        condition.FieldValue = string.Concat(condition.DbName,
 85         //            " between to_date('", range1, "', 'yyyy-mm-dd hh24:mi:ss') and to_date('", range2,
 86         //            "', 'yyyy-mm-dd hh24:mi:ss')");
 87         //    }
 88         //    return conditions;
 89         //}
 90 
 91         public static DataTable GetDataTable(StatisticsInquire inquire)
 92         {
 93             var inquireCond = new List<string>();
 94             inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.GroupField).ToList()
 95                 .ForEach(f =>
 96                 {
 97                     if (!f.IsAggregate)
 98                     {
 99                         inquireCond.Add(string.Concat(f.DbName, " AS ", f.FieldName));
100                     }
101                 });
102             inquire.InquireFields.Where(f => f.FieldType == FieldType.DisplayField).ToList().ToList()
103                 .ForEach(f => {
104                     if (f.IsAggregate)
105                     {
106                         inquireCond.Add(string.Concat(f.DbName, " AS ", f.FieldName));
107                     }
108                     else
109                     {
110                         if (f.IsPercent)
111                         {
112                             inquireCond.Add(string.Concat("ltrim(Convert(numeric(9,2), SUM(CASE WHEN ", f.DbName, " IN ('", string.Join("', '", f.FieldValue), "') THEN 1 ELSE 0 END)*100.0/Count(*))) + '%'  AS '", f.FieldName, ":", string.Join(",", f.FieldValue).SubStr(60), "(%)'"));
113                         }
114                         else
115                         {
116                             inquireCond.Add(string.Concat("SUM(CASE WHEN ", f.DbName, " IN ('", string.Join("', '", f.FieldValue) , "') THEN 1 ELSE 0 END) AS '", f.FieldName, ":", string.Join(",", f.FieldValue).SubStr(60), "'"));
117                         }
118                     }
119                 });
120 
121 
122             var whereCond = new List<string>();
123             inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.ConditionField).ToList()
124                 .ForEach(f =>
125                 {
126                     whereCond.Add(string.Concat(f.DbName, " IN ('", string.Join("','", f.FieldValue), "')"));
127                 });
128 
129             var groupCond = new List<string>();
130             inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.GroupField).ToList()
131                 .ForEach(f =>
132                 {
133                     groupCond.Add(f.DbName);
134                 });
135             var orderbyCond = new List<string>();
136             inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.OrderByField).ToList()
137                 .ForEach(f =>
138                 {
139                     orderbyCond.Add(string.Concat(f.DbName, " ", f.OrderByAsc.GetValueOrDefault() ? "ASC" : "DESC"));
140                 });
141 
142             var sqlStr = string.Concat("SELECT ",
143                 string.Join(", ", inquireCond),
144                 " FROM GetStudentStatusByGxsj('", inquire.StatisticsDate , "')",
145                 whereCond.Any() ? " WHERE " : string.Empty,
146                 string.Join(" AND ", whereCond),
147                 groupCond.Any() ? " GROUP BY " : string.Empty,
148                 (inquire.ShowSubSummary || inquire.ShowSummary)
149                     ? string.Concat("rollup(", string.Join(", ", groupCond), ")")
150                     : string.Join(", ", groupCond),
151                 orderbyCond.Any() ? " ORDER BY " : string.Empty,
152                 string.Join(", ", orderbyCond));
153 
154             var dt = DBUtility.DbHelperSql.Query(sqlStr).Tables[0];
155             if (!inquire.ShowSubSummary)
156             {
157                 if (inquire.ShowSummary)
158                 {
159                     var col = inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.GroupField).Count();
160                     for(int i = dt.Rows.Count - 2; i >=0 ; i -- ){
161                         if (dt.Rows[i][col - 1].ToString() == "")
162                         {
163                             dt.Rows.RemoveAt(i);
164                             //dt.Rows.Remove[dt.Rows[i]);
165                         }
166                     }
167                 }
168             }
169             else
170             {
171                 var col = inquire.InquireFields.Where(f => f.FieldType == InquireHelper.FieldType.GroupField).Count();
172                 for (int i = 0; i < dt.Rows.Count - 1; i++)
173                 {
174                     for (int j = 1; j < col; j++)
175                     {
176                         if (dt.Rows[i][j].ToString() == "")
177                         {
178                             dt.Rows[i][j] = "小计";
179                             break;
180                         }
181                     }
182 
183                 }
184 
185             }
186 
187             if (inquire.ShowSubSummary || inquire.ShowSummary)
188             {
189                 dt.Rows[dt.Rows.Count - 1][0] = "合计";
190             }
191 
192             return dt;
193         }
194         public static string SubStr(this string str, int maxLength)
195         {
196             if (str.Length > maxLength)
197             {
198                 return str.Substring(0, maxLength - 1);
199             }
200             else
201             {
202                 return str;
203             }
204         }
205 
206         public static string ToSerializableXML<T>(this T t)
207         {
208             XmlSerializer mySerializer = new XmlSerializer(typeof(T));
209             StringWriter sw = new StringWriter();
210             mySerializer.Serialize(sw, t);
211             return sw.ToString();
212         }
213 
214         public static T ToEntity<T>(this string xmlString)
215         {
216             var xs = new XmlSerializer(typeof(T));
217             var srReader = new StringReader(xmlString);
218             var steplist = (T)xs.Deserialize(srReader);
219             return steplist;
220         }
221 
222         public enum FieldType
223         {
224             DisplayField, GroupField, ConditionField, OrderByField
225         }
226 
227         private static ConcurrentDictionary<InquireFieldBase, List<string>> _inquireItems = new ConcurrentDictionary<InquireFieldBase,List<string>>();
228         public static List<string> GetInquireItemsByInquireType(this InquireFieldBase inquireField)
229         {
230             List<string> inquireItems;
231             if (_inquireItems.TryGetValue(inquireField, out inquireItems))
232             {
233                 return inquireItems;
234             }
235             switch (inquireField.GetType().Name)
236             {
237                 case "XYMC_InquireField":
238                     inquireItems = new BLL.depacode().GetModelList("").OrderBy(d => d.xydm).Select(d => d.xymc).ToList();
239                     break;
240                 case "ZYMC_InquireField":
241                     inquireItems = new BLL.profcode().GetModelList("").OrderBy(d => d.xydm).ThenBy(d => d.zydm).Select(d => d.zymc).ToList();
242                     break;
243                 case "SZBJ_InquireField":
244                     inquireItems = DbHelperSql.Query("select distinct szbj from jbdate order by szbj").Tables[0].AsEnumerable().Select(b => b["szbj"].ToString()).ToList();
245                     break;
246                 case "FDY_InquireField":
247                     inquireItems = new BLL.DepaUser().GetModelList("").OrderBy(d => d.XYDM).ThenBy(y => y.YHXM).Select(d => d.YHXM).ToList();
248                     break;
249                 case "XL_InquireField":
250                     inquireItems = new[] { "博士", "硕士", "双学位", "本科", "专科", "高职" }.ToList();
251                     break;
252                 case "SYDQ_InquireField":
253                     inquireItems = new[] { "东部", "中部", "西部" }.ToList();
254                     break;
255                 case "SYSF_InquireField":
256                     inquireItems = DbHelperSql.Query("select [Name] from [Sydqdm] where RIGHT([code], 4) = '0000' order by code").Tables[0].AsEnumerable().Select(b => b["Name"].ToString()).ToList();
257                     break;
258                 case "DWDQ_InquireField":
259                     inquireItems = new[] { "东部", "中部", "西部" }.ToList();
260                     break; 
261                 case "DWSF_InquireField":
262                     inquireItems = DbHelperSql.Query("select [Name] from [Sydqdm] where RIGHT([code], 4) = '0000' order by code").Tables[0].AsEnumerable().Select(b => b["Name"].ToString()).ToList();
263                     break;
264                 case "HYML_InquireField":
265                     inquireItems = DbHelperSql.Query("select distinct hyml from [hydygx]").Tables[0].AsEnumerable().Select(b => b["hyml"].ToString()).ToList();
266                     break;
267                 case "HYDL_InquireField":
268                     inquireItems = DbHelperSql.Query("select hydl from [hydygx] order by hydldm").Tables[0].AsEnumerable().Select(b => b["hydl"].ToString()).ToList();
269                     break;
270                 case "XBMC_InquireField":
271                     inquireItems = new[] { "男", "女" }.ToList();
272                     break;
273                 case "MZMC_InquireField":
274                     inquireItems = DbHelperSql.Query("select nation from [mzdmb] where nation in (select nation from jbdate) order by mzdm").Tables[0].AsEnumerable().Select(b => b["nation"].ToString()).ToList();
275                     break;
276                 case "BYQX_InquireField":
277                     inquireItems = new BLL.Byqxdmb().GetModelList("").OrderBy(d => d.Byqxdm).Select(d => d.Byqxmc).ToList();
278                     break;
279                 case "KNSLB_InquireField":
280                     inquireItems = new[] { "就业困难、家庭困难和残疾", "家庭困难和残疾", "就业困难和残疾", "残疾", "就业和家庭困难", "家庭困难", "就业困难", "非困难生" }.ToList();
281                     break;
282                 case "ZYDKL_InquireField":
283                     inquireItems = new[] { "专业对口", "专业相关", "不对口", "未填写" }.ToList();
284                     break;
285                 case "DWXZ_InquireField":
286                     inquireItems = new BLL.Dwxz().GetModelList("").OrderBy(d => d.dwxzdm).Select(d => d.dwxzmc).ToList();
287                     break;
288                 case "EJBYQXMC_InquireField":
289                     inquireItems = new BLL.EjByqxdmb().GetModelList("").OrderBy(d => d.Ejbyqxdm).Select(d => d.Ejbyqxmc).ToList();
290                     break;
291             }
292             if (inquireItems != null)
293             {
294                 _inquireItems[inquireField] = inquireItems;
295                 return inquireItems;
296             }
297             return new List<string>();
298         }
299     }
300     [Serializable]
301     public class StatisticsInquire
302     {
303         public List<InquireFieldBase> InquireFields { get; set; } 
304         [XmlAttribute]
305         public bool ShowSummary { get; set; }
306         [XmlAttribute]
307         public bool ShowSubSummary { get; set; }
308         [XmlAttribute]
309         public string StatisticsDate { get; set; }
310         [XmlAttribute]
311         public HighChart.ChartType ChartType { get; set; }
312     }