數(shù)據(jù)統(tǒng)計(jì)是每個(gè)系統(tǒng)中必備的功能，在給領(lǐng)導(dǎo)匯報(bào)統(tǒng)計(jì)數(shù)據(jù)，工作中需要的進(jìn)展數(shù)據(jù)時(shí)非常有用。
在我看來，一個(gè)統(tǒng)計(jì)的模塊應(yīng)該實(shí)現(xiàn)以下功能：
......
好了，以下是在實(shí)際環(huán)境中的實(shí)現(xiàn)和應(yīng)用：
這是一個(gè)學(xué)生的就業(yè)系統(tǒng)，學(xué)生在不同的時(shí)期會(huì)對(duì)自己畢業(yè)去向進(jìn)行登記，因此按照不同時(shí)間截點(diǎn)統(tǒng)計(jì)出來的數(shù)據(jù)是不一樣的。數(shù)據(jù)表有100多個(gè)字段（并不是所有字段都需要統(tǒng)計(jì)）。
首先，我們?cè)跀?shù)據(jù)庫(kù)中構(gòu)建一個(gè)表值函數(shù)，能夠按照不同的時(shí)間截點(diǎn)返回出數(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 '專科' 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è)計(jì)，我們使用jequery-ui中dropable\dragable的控件，字段排列在界面上，直接拖拽到相應(yīng)域里，就能夠進(jìn)行統(tǒng)計(jì)。
除了分組字段外，顯示字段還能夠按照具體的值進(jìn)行統(tǒng)計(jì)過濾，起到多重分組統(tǒng)計(jì)的功能。
大家可以看到，最上面一欄是數(shù)據(jù)篩選，然后是系統(tǒng)已經(jīng)保存的查詢（分為表格查詢和圖形查詢），點(diǎn)擊保存好的查詢直接出查詢結(jié)果，也可以刪除保存的查詢。在下面是自定義查詢，上面是一排條件，然后是可以拖拽的字段，當(dāng)字段拖至分組列，則顯示字段名稱；拖至顯示列，還可以對(duì)顯示的數(shù)據(jù)的具體值進(jìn)行分組篩選統(tǒng)計(jì)。下方則是一些選項(xiàng)，是否顯示小計(jì)、總計(jì)，以何種方式顯示圖表。
以表格形式的顯示統(tǒng)計(jì)，可以看到，每個(gè)數(shù)值都可以點(diǎn)擊彈出框顯示詳情，最下方能夠保存查詢條件，以圖形方式顯示等：
圖形的展示：
以下是核心類InquireHelper.cs：
字段實(shí)體類（部分）
[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)計(jì)
[XmlAttribute]
public bool IsNameField { get; set; }
//用于統(tǒng)計(jì)控制類
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)建對(duì)象（當(dāng)前程序集）
/// </summary>
/// <param name="typeName">類型名</param>
/// <returns>創(chuàng)建的對(duì)象，失敗返回 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] = "小計(jì)";
break;
}
}
}
}
if (inquire.ShowSubSummary || inquire.ShowSummary)
{
dt.Rows[dt.Rows.Count - 1][0] = "合計(jì)";
}
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é)位", "本科", "專科", "高職" }.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è)對(duì)口", "專業(yè)相關(guān)", "不對(duì)口", "未填寫" }.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; }
}實(shí)際在使用中，還是非常方便的
預(yù)計(jì)以后版本需要制作的功能：
對(duì)統(tǒng)計(jì)字段進(jìn)行進(jìn)一步優(yōu)化，能夠使用多個(gè)條件組合篩選同一個(gè)字段，這個(gè)比較簡(jiǎn)單，擴(kuò)展下類并且UI調(diào)整下就可以了。
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 m.alabama37th.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)
