数据统计是每个系统中必备的功能，在给领导汇报统计数据，工作中需要的进展数据时非常有用。
在我看来，一个统计的模块应该实现以下功能：
好了，以下是在实际环境中的实现和应用：
这是一个学生的就业系统，学生在不同的时期会对自己毕业去向进行登记，因此按照不同时间截点统计出来的数据是不一样的。数据表有100多个字段（并不是所有字段都需要统计）。
首先，我们在数据库中构建一个表值函数，能够按照不同的时间截点返回出数据，表也起到视图的作用，将参数表的值直接包含到返回结果中去。
1 ALTER FUNCTION [dbo].[Get.............] 2 ( 3 @gxsj datetime 4 ) 5 RETURNS TABLE 6 AS 7 RETURN 8 ( 9 select t1.*, 10 dbo.depacode.xymc, 11 CASE t1.xldm WHEN '01' THEN '博士' WHEN '11' THEN '硕士' WHEN '25' THEN '双学位' WHEN '31' THEN '本科' WHEN '41' THEN '专科' WHEN '61' THEN '高职' ELSE '' END AS xlmc, 12 CASE WHEN LEFT(t1.sydqdm, 2) IN ('11', '12', '13', '21', '31', '32', '33', '35', '37', '44', '46', '71', '81', '82') THEN '东部' 13 WHEN LEFT(t1.sydqdm, 2) IN ('14', '22', '23', '34', '36', '41', '42', '43') THEN '中部' 14 WHEN LEFT(t1.sydqdm, 2) IN ('15', '45', '51', '50', '52', '53', '54', '61', '62', '65', '63', '64') THEN '西部' ELSE '' END AS sydq, 15 sydq.dwdqmc AS sysf, 16 CASE WHEN LEFT(t1.dwdqdm, 2) IN ('11', '12', '13', '21', '31', '32', '33', '35', '37', '44', '46', '71', '81', '82') THEN '东部' 17 WHEN LEFT(t1.dwdqdm, 2) IN ('14', '22', '23', '34', '36', '41', '42', '43') THEN '中部' 18 WHEN LEFT(t1.dwdqdm, 2) IN ('15', '45', '51', '50', '52', '53', '54', '61', '62', '65', '63', '64') THEN '西部' ELSE '' END AS dwdq, 19 dwdq.dwdqmc AS dwsf, dbo.Entcode.hyname, 20 dbo.hydygx.hymldm, dbo.hydygx.hyml, 21 CASE t1.xbdm WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '男' END AS xbmc, 22 [mzdmb].[nation] AS mzmc, 23 [EjByqxdmb].[Ejbyqxmc], dbo.byqxdygx.jybbyqx, t1.gn500 AS jybdwxzdm, 24 CASE t1.knslbdm WHEN '7' THEN '就业困难、家庭困难和残疾' WHEN '6' THEN '家庭困难和残疾' WHEN '5' THEN '就业困难和残疾' WHEN '4' THEN '残疾' WHEN '3' THEN '就业和家庭困难' WHEN '2' THEN '家庭困难' WHEN '1' THEN '就业困难' ELSE '非困难生' END AS Knslb 25 from [table] as t1 26 LEFT OUTER JOIN 27 dbo.depacode ON t1.xydm = dbo.depacode.xydm LEFT OUTER JOIN 28 dbo.dwdq AS sydq ON LEFT(t1.sydqdm, 2) + '0000' = sydq.dwdqdm LEFT OUTER JOIN 29 dbo.dwdq AS dwdq ON LEFT(t1.dwdqdm, 2) + '0000' = dwdq.dwdqdm LEFT OUTER JOIN 30 dbo.Entcode ON t1.hylb = dbo.Entcode.hycode LEFT OUTER JOIN 31 dbo.hydygx ON t1.hylb = dbo.hydygx.hydldm LEFT OUTER JOIN 32 [mzdmb] ON t1.mzdm = [mzdmb].[mzdm] LEFT OUTER JOIN 33 [EjByqxdmb] ON t1.byqx2 = [EjByqxdmb].[Ejbyqxdm] LEFT OUTER JOIN 34 dbo.byqxdygx ON t1.byqx = dbo.byqxdygx.shbyqx AND 35 t1.dwxzdm = dbo.byqxdygx.shdwxzdm 36 where [gxsj] <= dateadd(day,1,@gxsj) and HisId in 37 (SELECT TOP 1 HisId FROM [table] 38 WHERE [gxsj] <= dateadd(day,1,@gxsj) and xsxh = t1.xsxh 39 and bynf = t1.bynf and t1.byqx not in ('08','05','11') 40 ORDER BY [gxsj] DESC) 41 )
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 }
View Code
控制类
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 }
View Code
实际在使用中，还是非常方便的
预计以后版本需要制作的功能：
对统计字段进行进一步优化，能够使用多个条件组合筛选同一个字段，这个比较简单，扩展下类并且UI调整下就可以了。
在这里把代码都分享给大家，希望和大家一起探讨。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。