Implementation of the data statistics function in the Asp.net Management Information System, asp.net data statistics

Implementation of the data statistics function in the Asp.net Management Information System, asp.net data statistics

Statistical data is a required function in each system. It is useful when reporting statistical data to leaders and the progress data required in the work.

In my opinion, a statistical module should implement the following functions:

• Displays statistical results of common queries;
• The displayed result can be in the form of a table or a graph. If it is a graph, it can be displayed in multiple forms (bar chart, line chart, pie chart, radar chart, Stacked column chart, etc ):
• Count the query results and click a number or percentage to display detailed data;
• The ability to freely combine query conditions, filtering conditions, grouping conditions, sorting, and so on;
• It is best to have a real-time preview of the statistical results;
• Query statistics can be saved so that the query results can be directly called and displayed next time;
• For saved query statistics, you can filter the query results based on flexible filtering methods in the next call;
• The interface needs to be concise and intuitive, so that even computer-less operators can easily use it;
• For some complex queries, you can directly write SQL statements in the background or call the Sp to output data.

......

Well, the following are the implementations and applications in the actual environment:

This is a student's employment system. Students will register their graduation destinations in different periods. Therefore, the data collected by different cutoff points is different. The data table has more than 100 fields (not all fields need statistics ).

First, we construct a table Value Function in the database to return data according to different time cut points. The table also serves as a view, include the values of the parameter table directly in the returned results.

Alter function [dbo]. [Get...] (@ gxsj datetime) returns table asreturn (select t1. *, dbo. depacode. xymc, CASE t1.xldm WHEN '01 'then' Dr. 'when' 11 'then' master's 'when' 25 'then' dual degree 'when' 31 'then' undergraduate 'when' 41 'then' specialist 'when' 61 'then' Higher Vocational 'else' end as xlmc, case when left (t1.sydqdm, 2) IN ('11', '12', '13', '21', '31', '32', '33 ', '35', '37', '44', '46', '71 ', '81', '82 ') THEN 'east' when left (t1.sydqdm, 2) IN ('14', '22', '23', '34', '36', '41', '42', '43 ') THEN 'Middle 'when LEFT (t1.sydqdm, 2) IN ('15', '45', '51', '50', '52 ', '53 ', '54 ', '61', '62 ', '65', '63', '64') THEN 'West 'else' 'end AS sydq, sydq. dwdqmc AS sysf, case when left (t1.dwdqdm, 2) IN ('11', '12', '13', '21', '31', '32 ', '33', '35', '37', '44', '46', '71 ', '81', '82 ') THEN 'east' when left (t1.dwdqdm, 2) IN ('14', '22', '23', '34', '36', '41 ', '42', '43 ') middle of then' 'when LEFT (t1.dwdqdm, 2) IN ('15', '45', '51', '50 ', '52 ', '53', '54 ', '61', '62 ', '65', '63', '64 ') THEN 'west' else' end as dwdq, dwdq. dwdqmc AS dwsf, dbo. entcode. hyname, dbo. hydygx. hymldm, dbo. hydygx. hyml, CASE t1.xbdm WHEN 1 THEN 'male' WHEN 2 THEN 'female 'else' Male' end as xbmc, [mzdmb]. [nation] AS mzmc, [EjByqxdmb]. [Ejbyqxmc], dbo. byqxdygx. jybbyqx, t1.gn500 AS jybdwxzdm, CASE t1.knslbdm WHEN '7' then' employment difficulties, family difficulties and disability 'when' 6'then' family difficulties and disability 'when' 5'then' employment difficulties and disability 'when' 4 'then' disabled 'when' 3 'then' employment and family difficulties 'when' 2 'then' family difficulties 'when' 1 'then' employment difficulties 'else' non-active students' end as Knslb from [table] as t1 left outer join dbo. depacode ON t1.xydm = dbo. depacode. xydm left outer join dbo. dwdq AS sydq on left (t1.sydqdm, 2) + '000000' = sydq. dwdqdm left outer join dbo. dwdq AS dwdq on left (t1.dwdqdm, 2) + '000000' = dwdq. dwdqdm left outer join dbo. entcode ON t1.hylb = dbo. entcode. hycode left outer join dbo. hydygx ON t1.hylb = dbo. hydygx. hydldm left outer join [mzdmb] ON t1.mzdm = [mzdmb]. [mzdm] left outer join [EjByqxdmb] ON t1.byqx2 = [EjByqxdmb]. [Ejbyqxdm] left outer join dbo. byqxdygx ON t1.byqx = dbo. byqxdygx. shbyqx AND t1.dwxzdm = dbo. byqxdygx. shdwxzdm where [gxsj] <= dateadd (day, 1, @ gxsj) and HisId in (select top 1 HisId FROM [table] WHERE [gxsj] <= dateadd (day, 1, @ gxsj) and xsxh = t1.xsxh and bynf = t1.bynf and t1.byqx not in ('08 ', '05', '11') order by [gxsj] DESC ))

In this way, we useselect * from [get...]('2016-8-25')You can query the data of the end date of January 1, August 25.

Next is the interface design. We use the dropable \ dragable control in jequery-ui. The fields are arranged on the interface and dragged directly to the corresponding field for statistics.

In addition to grouping fields, the display fields can also be filtered based on specific values for grouping statistics.

As you can see, the top column is data filtering, followed by saved queries (table queries and graphic queries). Click saved queries to directly display the query results, you can also delete saved queries. The following is a custom query with a row of conditions and then a field that can be dragged. When a field is dragged to a group column, the field name is displayed. When a field is dragged to a display column, you can also perform grouping and filtering statistics on the specific values of the displayed data. Below are some options for how to display charts, such as subtotal and total.

Statistics are displayed in a table. You can see that details are displayed in the pop-up box for each value. The query conditions can be saved at the bottom and displayed graphically:

Image Display:

The following is the core class InquireHelper. cs:

Field entity class (partial)

[Serializable] [XmlInclude (typeof (BYNF_InquireField)] [XmlInclude (typeof (Count_InquireField)] [XmlInclude (typeof (random)] [XmlInclude (typeof (random)] [XmlInclude (typeof (SZBJ_InquireField)] [XmlInclude (typeof (FDY_InquireField)] [XmlInclude (typeof (XL_InquireField)] [XmlInclude (typeof (SYDQ_InquireField)] [XmlInclude (typeof (SYSF_InquireField)] [XmlInclude (typeof (DWDQ_Inqu IreField)] [XmlInclude (typeof (SORT)] [XmlInclude (typeof (HYML_InquireField)] [XmlInclude (typeof (HYDL_InquireField)] [XmlInclude (typeof (SORT)] [XmlInclude (typeof (types)] [XmlInclude (typeof (BYQX_InquireField)] [XmlInclude (typeof (KNSLB_InquireField)] [XmlInclude (typeof (ZYDKL_InquireField)]) [XmlInclude (typeof (DWXZ_InquireField)] [XmlInclude (typeof (EJBYQXM C_InquireField)] [XmlInclude (typeof (bytes)] [XmlInclude (typeof (WYJE_InquireField)] public abstract class InquireFieldBase {public InquireFieldBase () {FieldItems = this. getInquireItemsByInquireType ();} [XmlAttribute] public int FieldDisplayOrder {get; set;} [XmlAttribute] public string FieldName {get; set;} [XmlAttribute] public string DbName {get; set;} [XmlAttribute] public Bool IsAggregate {get; set;} [XmlAttribute] public InquireHelper. fieldType {get; set;} // used for highchart statistics [XmlAttribute] public bool IsNameField {get; set ;}// used for statistics of output data [XmlAttribute] public bool IsPercent {get; set;} [XmlIgnore] public List <string> FieldItems {get; set;} public List <string> FieldValue {get; set;} public bool? OrderByAsc {get; set ;}} [Serializable] public class BYNF_InquireField: InquireFieldBase {public BYNF_InquireField () {FieldDisplayOrder = 1; FieldName = "year of graduation "; dbName = "BYNF" ;}} [Serializable] public class XYMC_InquireField: InquireFieldBase {public XYMC_InquireField () {FieldDisplayOrder = 5; FieldName = "school name "; dbName = "XYMC" ;}} [Serializable] public class ZYMC_InquireField: InquireFieldBase {public ZYMC_InquireField () {FieldDisplayOrder = 6; FieldName = "professional name "; dbName = "ZYMC" ;}} [Serializable] public class SZBJ_InquireField: InquireFieldBase {public SZBJ_InquireField () {FieldDisplayOrder = 7; FieldName = "class "; dbName = "SZBJ" ;}} [Serializable] public class FDY_InquireField: InquireFieldBase {public FDY_InquireField () {FieldDisplayOrder = 8; FieldName = "Counselor"; DbName = "FDY ";}} [Serializable] public class XL_InquireField: InquireFieldBase {public XL_InquireField () {FieldDisplayOrder = 9; FieldName = ""; DbName = "XLMC" ;}} [Serializable] public class metadata: inquireFieldBase {public partition () {FieldDisplayOrder = 10; FieldName = "student source region"; DbName = "SYDQ" ;}} [Serializable] public class SYSF_InquireField: InquireFieldBase {public SYSF_InquireField () {FieldDisplayOrder = 11; FieldName = ""; DbName = "SYSF" ;}} [Serializable] public class DWDQ_InquireField: InquireFieldBase {public DWDQ_InquireField () {FieldDisplayOrder = 12; fieldName = "region"; DbName = "DWDQ" ;}} [Serializable] public class DWSF_InquireField: InquireFieldBase {public DWSF_InquireField () {FieldDisplayOrder = 13; FieldName = "Province "; dbName = "DWSF ";}}

Control class

Public static class InquireHelper {public static List <InquireFieldBase> GetSubInquireList () {var inquires = new List <InquireFieldBase> (); var subTypeQuery = from t in Assembly. getExecutingAssembly (). getTypes () where IsSubClassOf (t, typeof (InquireFieldBase) select t; foreach (var type in subTypeQuery) {InquireFieldBase obj = CreateObject (type. fullName) as InquireFieldBase; if (obj! = Null) {inquires. Add (obj) ;}return inquires;} static bool IsSubClassOf (Type type, Type baseType) {var B = type. BaseType; while (B! = Null) {if (B. equals (baseType) {return true;} B = B. baseType;} return false;} // <summary> // create an object (current Assembly) /// </summary> /// <param name = "typeName"> type name </param> /// <returns> created object, return null for failure </returns> public static object CreateObject (string typeName) {object obj = null; try {Type objType = Type. getType (typeName, true); obj = Activator. createInstance (objType);} catch (Exception ex) {} re Turn obj;} public static List <InquireFieldBase> BindCondition (this List <InquireFieldBase> conditions, string conditionName, List <string> values) {var condition = conditions. firstOrDefault (c => c. getType (). name = conditionName & c. fieldType = FieldType. conditionField); if (condition = null) {condition = CreateObject ("BLL. "+ conditionName) as InquireFieldBase; condition. fieldType = FieldType. ConditionField; conditions. add (condition);} condition. fieldValue = values; return conditions;} // public static List <InquireFieldBase> BindCondition (this List <InquireFieldBase> conditions, string conditionName, string range1, string range2) // {// var condition = conditions. firstOrDefault (c => c. getType (). name = conditionName & c. fieldType = FieldType. conditionField); // if (! String. IsNullOrEmpty (range2 )&&! String. isNullOrEmpty (range1) // {// if (condition = null) // {// condition = CreateObject ("BLL. "+ conditionName) as InquireFieldBase; // condition. fieldType = FieldType. conditionField; // conditions. add (condition); // condition. fieldValue = string. concat (condition. dbName, // "between to_date ('", range1, "', 'yyyy-mm-dd hh24: mi: ss') and to_date ('", range2, // "', 'yyyy-mm-dd hh24: mi: ss ')" ); //} // Return conditions; //} public static DataTable GetDataTable (StatisticsInquire inquire) {var inquireCond = new List <string> (); inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. groupField ). toList (). forEach (f => {if (! F. isAggregate) {inquireCond. add (string. concat (f. dbName, "AS", f. fieldName) ;}}); inquire. inquireFields. where (f => f. fieldType = FieldType. displayField ). toList (). toList (). forEach (f => {if (f. isAggregate) {inquireCond. add (string. concat (f. dbName, "AS", f. fieldName);} else {if (f. isPercent) {inquireCond. add (string. concat ("ltrim (Convert (numeric (9,2), SUM (case when", f. dbName, "IN ('", s Tring. join ("','", f. fieldValue), "') THEN 1 ELSE 0 END) * 100.0/Count (*) +' % 'AS'", f. fieldName, ":", string. join (",", f. fieldValue ). subStr (60), "(%) '");} else {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), "'") ;}}); var whereCond = new Lis T <string> (); inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. conditionField ). toList (). forEach (f => {whereCond. add (string. concat (f. dbName, "IN ('", string. join ("','", f. fieldValue), "')");}); var groupCond = new List <string> (); inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. groupField ). toList (). forEach (f => {groupCond. add (f. dbName) ;}); var or DerbyCond = new List <string> (); inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. orderByField ). toList (). forEach (f => {orderbyCond. add (string. concat (f. dbName, "", f. orderByAsc. getValueOrDefault ()? "ASC": "DESC") ;}; var sqlStr = string. concat ("SELECT", string. join (",", inquireCond), "FROM GetStudentStatusByGxsj ('", inquire. statisticsDate, "')", whereCond. any ()? "WHERE": string. Empty, string. Join ("AND", whereCond), groupCond. Any ()? "Group by": string. Empty, (inquire. ShowSubSummary | inquire. ShowSummary )? String. concat ("rollup (", string. join (",", groupCond), ")"): string. join (",", groupCond), orderbyCond. any ()? "Order by": string. empty, string. join (",", orderbyCond); var dt = DBUtility. dbHelperSql. query (sqlStr ). tables [0]; if (! Inquire. showSubSummary) {if (inquire. showSummary) {var col = inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. groupField ). count (); for (int I = dt. rows. count-2; I> = 0; I --) {if (dt. rows [I] [col-1]. toString () = "") {dt. rows. removeAt (I); // dt. rows. remove [dt. rows [I]) ;}}} else {var col = inquire. inquireFields. where (f => f. fieldType = InquireHelper. fieldType. groupF Ield ). count (); for (int I = 0; I <dt. rows. count-1; I ++) {for (int j = 1; j <col; j ++) {if (dt. rows [I] [j]. toString () = "") {dt. rows [I] [j] = "subtotal"; break ;}}} if (inquire. showSubSummary | inquire. showSummary) {dt. rows [dt. rows. count-1] [0] = "Total";} return dt;} public static string SubStr (this string str, int maxLength) {if (str. length> maxLength) {return str. substring (0, maxLength -1);} else {return str;} public static string ToSerializableXML <T> (this T t) {XmlSerializer mySerializer = new XmlSerializer (typeof (T )); stringWriter sw = new StringWriter (); mySerializer. serialize (sw, t); return sw. toString ();} public static T ToEntity <T> (this string xmlString) {var xs = new XmlSerializer (typeof (T); var srReader = new StringReader (xmlString ); var steplist = (T) xs. deseri Alize (srReader); return steplist;} public enum FieldType {DisplayField, GroupField, ConditionField, OrderByField} private static ConcurrentDictionary <strong, List <string> _ inquireItems = new ConcurrentDictionary <InquireFieldBase, list <string> (); public static List <string> GetInquireItemsByInquireType (this InquireFieldBase inquireField) {List <string> inquireItems; if (_ inquireItems. T RyGetValue (inquireField, out inquireItems) {return inquireItems;} switch (inquireField. getType (). name) {case "XYMC_InquireField": inquireItems = new BLL. depacode (). getModelList (""). orderBy (d => d. xydm ). select (d => d. xymc ). toList (); break; case "ZYMC_InquireField": inquireItems = new BLL. profcode (). getModelList (""). orderBy (d => d. xydm ). thenBy (d => d. zydm ). select (d => d. zymc ). toList (); break; ca Se "SZBJ_InquireField": inquireItems = DbHelperSql. query ("select distinct szbj from jbdate order by szbj "). tables [0]. asEnumerable (). select (B => B ["szbj"]. toString ()). toList (); break; case "FDY_InquireField": inquireItems = new BLL. depaUser (). getModelList (""). orderBy (d => d. XYDM ). thenBy (y => y. YHXM ). select (d => d. YHXM ). toList (); break; case "XL_InquireField": inquireItems = new [] {"Phd", "Master", "Double Degree "," Undergraduate "," specialist "," Higher Vocational "}. toList (); break; case "SYDQ_InquireField": inquireItems = new [] {"East", "Central", "West "}. toList (); break; case "SYSF_InquireField": inquireItems = DbHelperSql. query ("select [Name] from [Sydqdm] where RIGHT ([code], 4) = '000000' order by code "). tables [0]. asEnumerable (). select (B => B ["Name"]. toString ()). toList (); break; case "DWDQ_InquireField": inquireItems = new [] {"East", "Central", "West "}. ToList (); break; case "DWSF_InquireField": inquireItems = DbHelperSql. query ("select [Name] from [Sydqdm] where RIGHT ([code], 4) = '000000' order by code "). tables [0]. asEnumerable (). select (B => B ["Name"]. toString ()). toList (); break; case "HYML_InquireField": inquireItems = DbHelperSql. query ("select distinct hyml from [hydygx]"). tables [0]. asEnumerable (). select (B => B ["hyml"]. toString ()). toList (); B Reak; case "HYDL_InquireField": inquireItems = DbHelperSql. query ("select hydl from [hydygx] order by hydldm "). tables [0]. asEnumerable (). select (B => B ["hydl"]. toString ()). toList (); break; case "XBMC_InquireField": inquireItems = new [] {"male", "female "}. toList (); break; case "MZMC_InquireField": 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 (); break; case "BYQX_InquireField": inquireItems = new BLL. byqxdmb (). getModelList (""). orderBy (d => d. byqxdm ). select (d => d. byqxmc ). toList (); break; case "KNSLB_InquireField": inquireItems = new [] {"employment difficulties, family difficulties and disability", "family difficulties and disability ", "employment and disability", "disability", "employment and family difficulties", "family difficulties", "employment difficulties", "non-active "}. toList (); break; case "ZYDKL_InquireField ": InquireItems = new [] {"professional counterpart", "professional counterpart", "non-counterpart", "not filled "}. toList (); break; case "DWXZ_InquireField": inquireItems = new BLL. dwxz (). getModelList (""). orderBy (d => d. dwxzdm ). select (d => d. dwxzmc ). toList (); break; case "EJBYQXMC_InquireField": inquireItems = new BLL. ejByqxdmb (). getModelList (""). orderBy (d => d. ejbyqxdm ). select (d => d. ejbyqxmc ). toList (); break;} if (inquireItems! = Null) {_ inquireItems [inquireField] = inquireItems; return inquireItems;} return new List <string> ();}} [Serializable] public class StatisticsInquire {public List <InquireFieldBase> InquireFields {get; set;} [XmlAttribute] public bool ShowSummary {get; set ;} [XmlAttribute] public bool ShowSubSummary {get; set;} [XmlAttribute] public string StatisticsDate {get; set;} [XmlAttribute] public HighChart. chartType {get; set ;}}

In practice, it is very convenient

Functions to be created in future versions:

To further optimize the statistical fields, you can use multiple conditions to filter the same field. This is relatively simple. You can extend the following classes and adjust the UI.