在asp.net中通常情况下,我们实现对数据报表的查看,都会使用报表控件来完成对数据的统计操作,然而有些时候我们却不想被控件所定的规格束缚住,因此我们想了另一种方式:首先来看看其中的一种效果如图1:
其主要功能实现的是一段sql语句(这里使用了存储过程):
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE Proc [dbo].[Compact_MonthCrossoverFormByEmp]
@bTime datetime,
@eTime dateTime
As
Select OrgId,OrgName,EmployeId, EmployeName, [1] as Day1,[2] as Day2,[3] as Day3,[4] as Day4,[5] as Day5,[6] as Day6,[7] as Day7,[8] as Day8,[9] as Day9,[10] as Day10,[11] as Day11,[12] as Day12,[13] as Day13,[14] as Day14,[15] as Day15,[16] as Day16,[17] as Day17,[18] as Day18,[19] as Day19,[20] as Day20,[21] as Day21,[22] as Day22,[23] as Day23,[24] as Day24,[25] as Day25,[26] as Day26,[27] as Day27,[28] as Day28,[29] as Day29,[30] as Day30,[31] as Day31
From(
Select O.OrgId,O.OrgName, E.EmployeId, E.Name As EmployeName, DatePart(day, C.CreateTime) as TimePoint,C.MoneyAmount as Amount
From Employe E Inner join Organ O On O.OrgId=E.OrgId Inner join Compact C On C.EmployeId=E.EmployeId
Where C.Status=2 And C.AuthType=1 And C.MoneyAmount>10 And C.CreateTime>=@bTime And C.CreateTime<@eTime
) as ds
Pivot(
Count(amount)
FOR TimePoint In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) as Pvt
Order By EmployeId
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
System.Globalization.Calendar calendar = new TaiwanCalendar();
var daysInMonth= calendar.GetDaysInMonth(Year, Month);
var hideColumns=gridViewEx.Columns.Count - 4 - daysInMonth; //4:序号,编号,名称,汇总四列
while (hideColumns-- > 0)
{
gridViewEx.Columns[gridViewEx.Columns.Count - hideColumns-1].Visible = false;
}
(1)与ODS绑定方法:
/// <summary>
/// 按员工分组,的月统计
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public List<CompactMonthCrossoverFormRow> QueryByEmp(int year, int month, string sortExpress)
{
Calendar calendar = new TaiwanCalendar();
var daysInMonth = calendar.GetDaysInMonth(year, month);
var bTime = new DateTime(year, month, 1);
var eTime = bTime.AddMonths(1);
var ds = OB.R<CompactManager>().MonthCrossoverFormByEmp(bTime, eTime);//调用其上介绍过的存储过程
var list = DBH.SetModelProperty<CompactMonthCrossoverFormRow>("*", ds.Tables[0]);
list.ForEach(ent =>
{
ent.EffectiveDay = daysInMonth;
ent.SumAndSetTotal(0, (total, v) => total += v);
});//根据daysInMonth来设置显示的列数
#region 排序处理(将设置的列排序)
if (!string.IsNullOrEmpty(sortExpress))
{
bool isAsc = true;
if (sortExpress.ToUpper().IndexOf("DESC") > 0) isAsc = false;
var field = sortExpress.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)[0];
var rtyInfo = typeof(CompactMonthCrossoverFormRow).GetProperty(field);
if (isAsc)
{
list = list.OrderBy(ent => rtyInfo.GetValue(ent, null)).ToList();
}
else
{
list = list.OrderByDescending(ent => rtyInfo.GetValue(ent, null)).ToList();
}
}
#endregion
var sumRow = CrossoverForm.CreateMonthSumRow<CompactMonthCrossoverFormRow, int, int>(list, (a, b) => a += b);
sumRow.EmployeName = "汇总";
sumRow.SumAndSetTotal(0, (a, b) => a + b);
list.Add(sumRow);
return list;
}
附带主要的几个文件:SalesPerformance.rar