在asp.net中通常情况下,我们实现对数据报表的查看,都会使用报表控件来完成对数据的统计操作,然而有些时候我们却不想被控件所定的规格束缚住,因此我们想了另一种方式:首先来看看其中的一种效果如图1:
其主要功能实现的是一段sql语句(这里使用了存储过程):
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.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
@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
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.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 ;
}
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