[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>类型名</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调整下就可以了。
您可能感兴趣的文章: