数据统计是每个系统中必备的功能,在给领导汇报统计数据,工作中需要的进展数据时非常有用。
在我看来,一个统计的模块应该实现以下功能:
好了,以下是在实际环境中的实现和应用:
这是一个学生的就业系统,学生在不同的时期会对自己毕业去向进行登记,因此按照不同时间截点统计出来的数据是不一样的。数据表有100多个字段(并不是所有字段都需要统计)。
首先,我们在数据库中构建一个表值函数,能够按照不同的时间截点返回出数据,表也起到视图的作用,将参数表的值直接包含到返回结果中去。
1 ALTER FUNCTION [dbo].[Get.............]View Code
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 )
这样我们使用 select * from [get...]('2016-8-25') 就可以查询出8月25日截止日期的数据。
接下来是界面设计,我们使用jequery-ui中dropable\dragable的控件,字段排列在界面上,直接拖拽到相应域里,就能够进行统计。
除了分组字段外,显示字段还能够按照具体的值进行统计过滤,起到多重分组统计的功能。
大家可以看到,最上面一栏是数据筛选,然后是系统已经保存的查询(分为表格查询和图形查询),点击保存好的查询直接出查询结果,也可以删除保存的查询。在下面是自定义查询,上面是一排条件,然后是可以拖拽的字段,当字段拖至分组列,则显示字段名称;拖至显示列,还可以对显示的数据的具体值进行分组筛选统计。下方则是一些选项,是否显示小计、总计,以何种方式显示图表。
以表格形式的显示统计,可以看到,每个数值都可以点击弹出框显示详情,最下方能够保存查询条件,以图形方式显示等:
图形的展示:
以下是核心类InquireHelper.cs:
字段实体类(部分)
1 [Serializable]View Code
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; i++)
2 {
3 public static ListGetSubInquireList()
4 {
5 var inquires = new List();
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 ///
36 /// 创建对象(当前程序集)
37 ///
38 /// 类型名
39 ///创建的对象,失败返回 null
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 ListBindCondition(this List 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 ListBindCondition(this List 71 //{conditions, string conditionName, string range1, string range2)
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; i1
实际在使用中,还是非常方便的
预计以后版本需要制作的功能:
对统计字段进行进一步优化,能够使用多个条件组合筛选同一个字段,这个比较简单,扩展下类并且UI调整下就可以了。
在这里把代码都分享给大家,希望和大家一起探讨。