這篇文章主要介紹了Asp.net管理資訊系統中資料統計功能的實現方法,需要的朋友可以參考下
資料統計是每個系統中必備的功能,在給領導報告統計數據,工作中所需的進度數據時非常有用。
在我看來,一個統計的模組應該實現以下功能:
#能夠將常用的查詢的統計結果顯示出來;
- ##顯示的結果可以是表格形式,也可以是圖形形式,如果是圖形的話能夠以多種形式顯示(長條圖、折線圖、圓餅圖、雷達圖、堆疊長條圖等):
- 統計查詢的結果,點選數字或百分比能夠顯示詳細的資料;
- #能夠自由組合查詢條件、篩選條件、分組條件、排序等等;
- 統計結果最好有個即時預覽;
#查詢統計能夠保存,以便下次能直接呼叫並顯示統計查詢的結果;
對於已儲存後的查詢統計,下次呼叫時也可以依照靈活的篩選手段對查詢結果進行篩選;
對於一些複雜的查詢,能夠直接在後台寫Sql或者調用Sp出數據
......
#這是學生的就業系統,學生在不同的時期會對自己畢業去向進行登記,因此依照不同時間截點統計出來的資料是不一樣的。資料表有100多個欄位(並不是所有欄位都需要統計)。
首先,我們在資料庫中建立一個表值函數,能夠按照不同的時間截斷返回出數據,表也起到視圖的作用,將參數表的值直接包含到返回結果中去。
ALTER FUNCTION [dbo].[Get.............] ( @gxsj datetime ) RETURNS TABLE AS RETURN ( select t1.*, dbo.depacode.xymc, 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, CASE WHEN LEFT(t1.sydqdm, 2) IN ('11', '12', '13', '21', '31', '32', '33', '35', '37', '44', '46', '71', '81', '82') THEN '东部' WHEN LEFT(t1.sydqdm, 2) IN ('14', '22', '23', '34', '36', '41', '42', '43') THEN '中部' WHEN LEFT(t1.sydqdm, 2) IN ('15', '45', '51', '50', '52', '53', '54', '61', '62', '65', '63', '64') THEN '西部' 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 '东部' WHEN LEFT(t1.dwdqdm, 2) IN ('14', '22', '23', '34', '36', '41', '42', '43') THEN '中部' WHEN LEFT(t1.dwdqdm, 2) IN ('15', '45', '51', '50', '52', '53', '54', '61', '62', '65', '63', '64') THEN '西部' ELSE '' END AS dwdq, dwdq.dwdqmc AS dwsf, dbo.Entcode.hyname, dbo.hydygx.hymldm, dbo.hydygx.hyml, CASE t1.xbdm WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '男' END AS xbmc, [mzdmb].[nation] AS mzmc, [EjByqxdmb].[Ejbyqxmc], dbo.byqxdygx.jybbyqx, t1.gn500 AS jybdwxzdm, 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 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) + '0000' = sydq.dwdqdm LEFT OUTER JOIN dbo.dwdq AS dwdq ON LEFT(t1.dwdqdm, 2) + '0000' = 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) )
這樣我們使用select * from [get...]('2016-8-25')
接下來是介面設計,我們使用jequery-ui中dropable\dragable的控件,欄位排列在介面上,直接拖曳到對應域裡,就能夠進行統計。
除了分組欄位外,顯示欄位也能夠依照具體的值進行統計過濾,起到多重分組統計的功能。
大家可以看到,最上面一欄是資料篩選,然後是系統已經儲存的查詢(分成表格查詢和圖形查詢),點選已儲存好的查詢直接出查詢結果,也可以刪除已儲存的查詢。在下面是自訂查詢,上面是一排條件,然後是可以拖曳的字段,當字段拖至分組列,則顯示字段名稱;拖至顯示列,還可以對顯示的資料的具體值進行分組篩選統計。下方則是一些選項,是否顯示小計、總計,以何種方式顯示圖表。
以表格形式的顯示統計,可以看到,每個數值都可以點擊彈出框顯示詳情,最下方能夠保存查詢條件,以圖形方式顯示等:
#圖形的展示:
[Serializable] [XmlInclude(typeof(BYNF_InquireField))] [XmlInclude(typeof(Count_InquireField))] [XmlInclude(typeof(XYMC_InquireField))] [XmlInclude(typeof(ZYMC_InquireField))] [XmlInclude(typeof(SZBJ_InquireField))] [XmlInclude(typeof(FDY_InquireField))] [XmlInclude(typeof(XL_InquireField))] [XmlInclude(typeof(SYDQ_InquireField))] [XmlInclude(typeof(SYSF_InquireField))] [XmlInclude(typeof(DWDQ_InquireField))] [XmlInclude(typeof(DWSF_InquireField))] [XmlInclude(typeof(HYML_InquireField))] [XmlInclude(typeof(HYDL_InquireField))] [XmlInclude(typeof(XBMC_InquireField))] [XmlInclude(typeof(MZMC_InquireField))] [XmlInclude(typeof(BYQX_InquireField))] [XmlInclude(typeof(KNSLB_InquireField))] [XmlInclude(typeof(ZYDKL_InquireField))] [XmlInclude(typeof(DWXZ_InquireField))] [XmlInclude(typeof(EJBYQXMC_InquireField))] [XmlInclude(typeof(GZ_InquireField))] [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 FieldType { get; set; } //用于highchart统计 [XmlAttribute] public bool IsNameField { get; set; } //用于统计输出数据 [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 = "毕业年份"; DbName = "BYNF"; } } [Serializable] public class XYMC_InquireField : InquireFieldBase { public XYMC_InquireField() { FieldDisplayOrder = 5; FieldName = "学院名称"; DbName = "XYMC"; } } [Serializable] public class ZYMC_InquireField : InquireFieldBase { public ZYMC_InquireField() { FieldDisplayOrder = 6; FieldName = "专业名称"; DbName = "ZYMC"; } } [Serializable] public class SZBJ_InquireField : InquireFieldBase { public SZBJ_InquireField() { FieldDisplayOrder = 7; FieldName = "所在班级"; DbName = "SZBJ"; } } [Serializable] public class FDY_InquireField : InquireFieldBase { public FDY_InquireField() { FieldDisplayOrder = 8; FieldName = "辅导员"; DbName = "FDY"; } } [Serializable] public class XL_InquireField : InquireFieldBase { public XL_InquireField() { FieldDisplayOrder = 9; FieldName = "学历"; DbName = "XLMC"; } } [Serializable] public class SYDQ_InquireField : InquireFieldBase { public SYDQ_InquireField() { FieldDisplayOrder = 10; FieldName = "生源地区"; 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 = "单位地区"; DbName = "DWDQ"; } } [Serializable] public class DWSF_InquireField : InquireFieldBase { public DWSF_InquireField() { FieldDisplayOrder = 13; FieldName = "单位省份"; DbName = "DWSF"; } }###控制類別########## #####
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> /// 创建对象(当前程序集) /// </summary> /// <param name="typeName">类型名</param> /// <returns>创建的对象,失败返回 null</returns> public static object CreateObject(string typeName) { object obj = null; try { Type objType = Type.GetType(typeName, true); obj = Activator.CreateInstance(objType); } catch (Exception ex) { } return 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 ('", string.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 List<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 orderbyCond = 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.GroupField).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] = "小计"; break; } } } } if (inquire.ShowSubSummary || inquire.ShowSummary) { dt.Rows[dt.Rows.Count - 1][0] = "合计"; } 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.Deserialize(srReader); return steplist; } public enum FieldType { DisplayField, GroupField, ConditionField, OrderByField } private static ConcurrentDictionary<InquireFieldBase, List<string>> _inquireItems = new ConcurrentDictionary<InquireFieldBase,List<string>>(); public static List<string> GetInquireItemsByInquireType(this InquireFieldBase inquireField) { List<string> inquireItems; if (_inquireItems.TryGetValue(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; case "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[] { "博士", "硕士", "双学位", "本科", "专科", "高职" }.ToList(); break; case "SYDQ_InquireField": inquireItems = new[] { "东部", "中部", "西部" }.ToList(); break; case "SYSF_InquireField": inquireItems = DbHelperSql.Query("select [Name] from [Sydqdm] where RIGHT([code], 4) = '0000' order by code").Tables[0].AsEnumerable().Select(b => b["Name"].ToString()).ToList(); break; case "DWDQ_InquireField": inquireItems = new[] { "东部", "中部", "西部" }.ToList(); break; case "DWSF_InquireField": inquireItems = DbHelperSql.Query("select [Name] from [Sydqdm] where RIGHT([code], 4) = '0000' 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(); break; 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[] { "男", "女" }.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[] { "就业困难、家庭困难和残疾", "家庭困难和残疾", "就业困难和残疾", "残疾", "就业和家庭困难", "家庭困难", "就业困难", "非困难生" }.ToList(); break; case "ZYDKL_InquireField": inquireItems = new[] { "专业对口", "专业相关", "不对口", "未填写" }.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 ChartType { get; set; } }###實際上在使用中,還是非常方便的######預計以後版本需要製作的功能:######對統計欄位進行進一步最佳化,能夠使用多個條件組合篩選同一個字段,這個比較簡單,擴充下類別並且UI調整下就可以了。 ######
以上是使用Asp.net實現資訊管理系統的資料統計功能的詳細內容。更多資訊請關注PHP中文網其他相關文章!

要開始C#.NET開發,你需要:1.了解C#的基礎知識和.NET框架的核心概念;2.掌握變量、數據類型、控制結構、函數和類的基本概念;3.學習C#的高級特性,如LINQ和異步編程;4.熟悉常見錯誤的調試技巧和性能優化方法。通過這些步驟,你可以逐步深入C#.NET的世界,並編寫高效的應用程序。

C#和.NET的關係是密不可分的,但它們不是一回事。 C#是一門編程語言,而.NET是一個開發平台。 C#用於編寫代碼,編譯成.NET的中間語言(IL),由.NET運行時(CLR)執行。

C#.NET依然重要,因為它提供了強大的工具和庫,支持多種應用開發。 1)C#結合.NET框架,使開發高效便捷。 2)C#的類型安全和垃圾回收機制增強了其優勢。 3).NET提供跨平台運行環境和豐富的API,提升了開發靈活性。

C#.NETisversatileforbothwebanddesktopdevelopment.1)Forweb,useASP.NETfordynamicapplications.2)Fordesktop,employWindowsFormsorWPFforrichinterfaces.3)UseXamarinforcross-platformdevelopment,enablingcodesharingacrossWindows,macOS,Linux,andmobiledevices.

C#和.NET通過不斷的更新和優化,適應了新興技術的需求。 1)C#9.0和.NET5引入了記錄類型和性能優化。 2).NETCore增強了雲原生和容器化支持。 3)ASP.NETCore與現代Web技術集成。 4)ML.NET支持機器學習和人工智能。 5)異步編程和最佳實踐提升了性能。

c#.netissutableforenterprise-levelapplications withemofrosoftecosystemdueToItsStrongTyping,richlibraries,androbustperraries,androbustperformance.however,itmaynotbeidealfoross-platement forment forment forment forvepentment offependment dovelopment toveloperment toveloperment whenrawspeedsportor whenrawspeedseedpolitical politionalitable,

C#在.NET中的編程過程包括以下步驟:1)編寫C#代碼,2)編譯為中間語言(IL),3)由.NET運行時(CLR)執行。 C#在.NET中的優勢在於其現代化語法、強大的類型系統和與.NET框架的緊密集成,適用於從桌面應用到Web服務的各種開發場景。

C#是一種現代、面向對象的編程語言,由微軟開發並作為.NET框架的一部分。 1.C#支持面向對象編程(OOP),包括封裝、繼承和多態。 2.C#中的異步編程通過async和await關鍵字實現,提高應用的響應性。 3.使用LINQ可以簡潔地處理數據集合。 4.常見錯誤包括空引用異常和索引超出範圍異常,調試技巧包括使用調試器和異常處理。 5.性能優化包括使用StringBuilder和避免不必要的裝箱和拆箱。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

MinGW - Minimalist GNU for Windows
這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。

記事本++7.3.1
好用且免費的程式碼編輯器

WebStorm Mac版
好用的JavaScript開發工具

Dreamweaver Mac版
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)