4.7.3 按照年份查询年度报表
1.把之前改写好的单用户报表的"报支打印.cshtml"和"feiyong.cshtml"各复制一份,这里分别起名为"报支打印2.cshtml"和"feiyong2.cshtml"
2.在"报支打印2.cshtml"中改写SQL语句,并且做一个去重的处理避免一个人被查询多次,这里传了一个参数"year",在之后的"feiyong2.cshtml"所传过来的,代码如下:
@{
var gisDB = Database.OpenConnectionString("GIS");//连接OrcleDB
List<dynamic> BaoZhiList = gisDB.Query(@"SELECT SNAME,STIME,COUNT(SCOUNT) AS TOTAL,SUM(SCOUNT) AS MONEY FROM (SELECT TO_CHAR(STIME,'YYYYMM') AS STIME,SNAME,SCOUNT FROM SUBDEL_SECOND WHERE STIME BETWEEN '{0}/01/01' AND '{0}/12/31 23:59:59' ) GROUP BY SNAME,STIME ORDER BY SNAME,STIME ", Params.year);
List<string> users = new List<string>();
foreach (var d in BaoZhiList)
{
if (!users.Contains(d.SNAME))
{
users.Add(d.SNAME);
}
}
}
用Tord for oracle时可能会引起时间格式问题,解决办法是点击Database中的Administer中的NLS Parameters ,将其中的的NLS_TIMESTEMP_FORMAT改为你所使用的是其格式,如图
3.<table>表中,我们用了两次次循环,先对所有用户做一次循环,再在这单个用户的循环中,再对12个月做一次循环,将满足条件的数据在单元格中显示出来,这样子每个用户的每个月的数据就都显示出来了.然后在用户的循环外,定义两个参数"totalMoney"和"totalCount",在用户的循环之内同时满足在12月循环外再定义两个参数"thisMoney"和"thisCount".在单元格输出数据的同时,让这些参数去"+="上他们,在"totalMoney"和"totalCount"会得到所有单元格中数据的和即"年度所有员工数据","thisMoney"和"thisCount"会记录单次用户的所有数据即"年度该员工的数据".一下是我的<table>表的代码,仅供参考
<table style="width:90%;border-bottom:#4d4d4d 2px solid;border-right:#4d4d4d 1px solid;border-top: #4d4d4d 1px solid;" border="0" cellpadding="0" cellspacing="0" class="TableSTyle">
<tbody>
<tr>
<td></td>
@for (int i = 1; i <= 12; i++)
{
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">@(i)月</th>
}
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;" colspan="2">总计</th>
</tr>
@{
var totalMoney = 0;
var totalCount = 0;
var JanMoney = 0;
var JanCount = 0;
var FebMoney = 0;
var FebCount = 0;
var MarMoney = 0;
var MarCount = 0;
var AprMoney = 0;
var AprCount = 0;
var MayMoney = 0;
var MayCount = 0;
var JunMoney = 0;
var JunCount = 0;
var JulMoney = 0;
var JulCount = 0;
var AugMoney = 0;
var AugCount = 0;
var SepMoney = 0;
var SepCount = 0;
var OctMoney = 0;
var OctCount = 0;
var NovMoney = 0;
var NovCount = 0;
var DecMoney = 0;
var DecCount = 0;
}
@foreach (var u in users)
{
<tr>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">@u</th>
@{
var thisMoney = 0;
var thisCount = 0;
}
@for (int i = 1; i <= 13; i++)
{
@if (i >= 1 && i <= 12)
{
<td class="TdStyle_Left" style=" text-align: center; background-color: #FFFFFF;">
@foreach (var d in BaoZhiList)
{
if (d.SNAME == u && int.Parse(d.STIME.Substring(4)) == i)
{
@(d.TOTAL+"笔报销,"+d.MONEY+"元")
@{
thisMoney += d.MONEY;
thisCount += d.TOTAL;
if (i == 1)
{
JanMoney += d.MONEY;
JanCount += d.TOTAL;
}
else if (i == 2)
{
FebMoney += d.MONEY;
FebCount += d.TOTAL;
}
else if (i == 3)
{
MarMoney += d.MONEY;
MarCount += d.TOTAL;
}
else if (i == 4)
{
AprMoney += d.MONEY;
AprCount += d.TOTAL;
}
else if (i == 5)
{
MayMoney += d.MONEY;
MayCount += d.TOTAL;
}
else if (i == 6)
{
JunMoney += d.MONEY;
JunCount += d.TOTAL;
}
else if (i == 7)
{
JulMoney += d.MONEY;
JulCount += d.TOTAL;
}
else if (i == 8)
{
AugMoney += d.MONEY;
AugCount += d.TOTAL;
}
else if (i == 9)
{
SepMoney += d.MONEY;
SepCount += d.TOTAL;
}
else if (i == 10)
{
OctMoney += d.MONEY;
OctCount += d.TOTAL;
}
else if (i == 11)
{
NovMoney += d.MONEY;
NovCount += d.TOTAL;
}
else if (i == 12)
{
DecMoney += d.MONEY;
DecCount += d.TOTAL;
}
}
}
}
</td>
}
else
{
<td class="TdStyle_Left" style=" text-align: center; background-color: #ccc;" colspan="2">
年共计报销:@thisCount,共计金额:@thisMoney
@{
totalMoney += thisMoney;
totalCount += thisCount;
}
</td>
}
}
</tr>
}
<tr>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;" >统计</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@JanCount,月金额:@JanMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@FebCount,月金额:@FebMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@MarCount,月金额:@MarMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@AprCount,月金额:@AprMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@MayCount,月金额:@MayMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@JunCount,月金额:@JunMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@JulCount,月金额:@JulMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@AugCount,月金额:@AugMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@SepCount,月金额:@SepMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@OctCount,月金额:@OctMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@NovCount,月金额:@NovMoney</th>
<th class="TdStyle_Left" style=" text-align: center; background-color: #ccc;">月共计次数:@DecCount,月金额:@DecMoney</th>
</tr>
</tbody>
</table>
4.打开"feiyong2.cshtml",在<body>中添加一个下拉框,功能是从今年开始的一个降序的年份,之前提到的参数"year",就是在这里从页面上收到的
<select id="yearId">
@{
<option>@DateTime.Now.Year</option>
for (var i = @DateTime.Now.Year; i >= @DateTime.Now.Year - 10; i--)
{
<option value="@(i-1)">@(i-1)</option>
}
}
</select>
5.添加一个"查询"的按钮,绑定单击事件"checkList()"
<input type="button" id="checkList" value="查询" onclick="checkList()" />
6.改写<script>中的方法,这里我们将"yearId"类的值以"year"这个参数传了下去,如下
<script type="text/javascript" language="javascript" charset="utf-8">
$(document).ready(function () {
InitFrameLayout();
});
function checkList() {
AjaxLabel2({ name: "报支打印2", IsShow: "1", year: $("#yearId").val() }, function (result) {
$("#SearchCondition .content").html(result.data);
});
}
</script>
7.运行程序,给之前添加的流程更换一个URL地址为"Feiyong2"(这里新增一个流程也是可以的),回到表单数据,选中一个数据,点击刚刚修改好(新增的)的流程按钮,选择一个查询的年份(这里的数据要添加好,提前多添加几个),获得结果
