溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇文章給大家分享的是有關(guān)怎么在Asp.net管理信息系統(tǒng)中實現(xiàn)一個數(shù)據(jù)統(tǒng)計功能，小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。
數(shù)據(jù)統(tǒng)計是每個系統(tǒng)中必備的功能，在給領(lǐng)導(dǎo)匯報統(tǒng)計數(shù)據(jù)，工作中需要的進展數(shù)據(jù)時非常有用。
在我看來，一個統(tǒng)計的模塊應(yīng)該實現(xiàn)以下功能：
......
好了，以下是在實際環(huán)境中的實現(xiàn)和應(yīng)用：
這是一個學(xué)生的就業(yè)系統(tǒng)，學(xué)生在不同的時期會對自己畢業(yè)去向進行登記，因此按照不同時間截點統(tǒng)計出來的數(shù)據(jù)是不一樣的。數(shù)據(jù)表有100多個字段（并不是所有字段都需要統(tǒng)計）。
首先，我們在數(shù)據(jù)庫中構(gòu)建一個表值函數(shù)，能夠按照不同的時間截點返回出數(shù)據(jù)，表也起到視圖的作用，將參數(shù)表的值直接包含到返回結(jié)果中去。
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 '雙學(xué)位' WHEN '31' THEN '本科' WHEN '41' THEN '?？?#39; 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 '就業(yè)困難、家庭困難和殘疾' WHEN '6' THEN '家庭困難和殘疾' WHEN '5' THEN '就業(yè)困難和殘疾' WHEN '4' THEN '殘疾' WHEN '3' THEN '就業(yè)和家庭困難' WHEN '2' THEN '家庭困難' WHEN '1' THEN '就業(yè)困難' 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') 就可以查詢出8月25日截止日期的數(shù)據(jù)。
接下來是界面設(shè)計，我們使用jequery-ui中dropable\dragable的控件，字段排列在界面上，直接拖拽到相應(yīng)域里，就能夠進行統(tǒng)計。
除了分組字段外，顯示字段還能夠按照具體的值進行統(tǒng)計過濾，起到多重分組統(tǒng)計的功能。
大家可以看到，最上面一欄是數(shù)據(jù)篩選，然后是系統(tǒng)已經(jīng)保存的查詢（分為表格查詢和圖形查詢），點擊保存好的查詢直接出查詢結(jié)果，也可以刪除保存的查詢。在下面是自定義查詢，上面是一排條件，然后是可以拖拽的字段，當(dāng)字段拖至分組列，則顯示字段名稱；拖至顯示列，還可以對顯示的數(shù)據(jù)的具體值進行分組篩選統(tǒng)計。下方則是一些選項，是否顯示小計、總計，以何種方式顯示圖表。
以表格形式的顯示統(tǒng)計，可以看到，每個數(shù)值都可以點擊彈出框顯示詳情，最下方能夠保存查詢條件，以圖形方式顯示等：
圖形的展示：
以下是核心類InquireHelper.cs：
字段實體類（部分）
[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統(tǒng)計
[XmlAttribute]
public bool IsNameField { get; set; }
//用于統(tǒng)計輸出數(shù)據(jù)
[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 = "畢業(yè)年份";
DbName = "BYNF";
}
}
[Serializable]
public class XYMC_InquireField : InquireFieldBase
{
public XYMC_InquireField()
{
FieldDisplayOrder = 5;
FieldName = "學(xué)院名稱";
DbName = "XYMC";
}
}
[Serializable]
public class ZYMC_InquireField : InquireFieldBase
{
public ZYMC_InquireField()
{
FieldDisplayOrder = 6;
FieldName = "專業(yè)名稱";
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 = "輔導(dǎo)員";
DbName = "FDY";
}
}
[Serializable]
public class XL_InquireField : InquireFieldBase
{
public XL_InquireField()
{
FieldDisplayOrder = 9;
FieldName = "學(xué)歷";
DbName = "XLMC";
}
}
[Serializable]
public class SYDQ_InquireField : InquireFieldBase
{
public SYDQ_InquireField()
{
FieldDisplayOrder = 10;
FieldName = "生源地區(qū)";
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 = "單位地區(qū)";
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>
/// 創(chuàng)建對象（當(dāng)前程序集）
/// </summary>
/// <param name="typeName">類型名</param>
/// <returns>創(chuàng)建的對象，失敗返回 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 hh34:mi:ss') and to_date('", range2,
// "', 'yyyy-mm-dd hh34: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[] { "博士", "碩士", "雙學(xué)位", "本科", "?？?quot;, "高職" }.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[] { "就業(yè)困難、家庭困難和殘疾", "家庭困難和殘疾", "就業(yè)困難和殘疾", "殘疾", "就業(yè)和家庭困難", "家庭困難", "就業(yè)困難", "非困難生" }.ToList();
break;
case "ZYDKL_InquireField":
inquireItems = new[] { "專業(yè)對口", "專業(yè)相關(guān)", "不對口", "未填寫" }.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; }
}以上就是怎么在Asp.net管理信息系統(tǒng)中實現(xiàn)一個數(shù)據(jù)統(tǒng)計功能，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼