Asp.net管理信息系統(tǒng)中數(shù)據(jù)統(tǒng)計功能的實現(xiàn)方法

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 '就業(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)
)

[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)計

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)建對象（當前程序集）
 /// </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 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[] { "就業(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; }
 }