数据统计是每个系统中必备的功能,在给领导汇报统计数据,工作中需要的进展数据时非常有用。
在我看来,一个统计的模块应该实现以下功能:
- 能够将常用的查询的统计结果显示出来;
- 显示的结果可以是表格形式,也可以是图形形式,如果是图形的话能够以多种形式显示(柱状图、折线图、饼图、雷达图、堆叠柱状图等):
- 统计查询的结果,点击数字或者百分比能够显示详细的数据;
- 能够自由组合查询条件、筛选条件、分组条件、排序等;
- 统计结果最好有个实时预览;
- 查询统计能够保存,以便下次能直接调用并显示统计查询的结果;
- 对于保存后的查询统计,下次调用时也可以按照灵活的筛选手段对查询结果进行筛选;
- 界面需要做的简洁、直观,就算是不太懂电脑的操作员也能够方便使用;
- 对于一些复杂的查询,能够直接在后台写Sql或者调用Sp出数据
- ......
好了,以下是在实际环境中的实现和应用:
这是一个学生的就业系统,学生在不同的时期会对自己毕业去向进行登记,因此按照不同时间截点统计出来的数据是不一样的。数据表有100多个字段(并不是所有字段都需要统计)。
首先,我们在数据库中构建一个表值函数,能够按照不同的时间截点返回出数据,表也起到视图的作用,将参数表的值直接包含到返回结果中去。
这样我们使用 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 }