ASP.NET MVC4 + Highcharts生成报表
//后端
public ActionResult TighteningReport(BReportTighteningReportModel model, string rate, string weekTime, string weekEnd, string month)
{
TempData["Tmp"] = "open5";
if (!string.IsNullOrEmpty(rate))
{
using (BaseContext context = new BaseContext("ToolsTB"))
{
var transactionCounts = new List<BReportTighteningReportModel>();
#region 日
if (rate == "0")
{
var totalList = context.T_ToolsResult.Where(m => m.TighteningTime >= model.BeginTime && m.TighteningTime <= model.EndTime).ToList().Count();
var notOkList = context.T_ToolsResult.Where(m => m.TighteningTime >= model.BeginTime && m.TighteningTime <= model.EndTime && m.TighteningResult != "ok")
.GroupBy(m => m.TighteningTime).Select(g => (new { TighteningTime = g.Key, count = g.Count() }));
foreach (var item in notOkList)
{
transactionCounts.Add(new BReportTighteningReportModel() { Date = item.TighteningTime.ToString("yyyy/MM/dd"), Count = item.count });
}
}
#endregion
#region 周
if (rate == "1")
{
var weekT = Convert.ToDateTime(weekTime);
var weekE = Convert.ToDateTime(weekEnd);
var totalList = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE).ToList().Count();
var notOkList = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE && m.TighteningResult != "ok")
.GroupBy(m => m.TighteningTime).Select(g => (new { TighteningTime = g.Key, count = g.Count() }));
//统计结果值ok
IList<T_ToolsResultDefinition> list = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE && m.TighteningResult != "ok").Distinct().ToList();
string filter = string.Empty;
for (int i = 0; i < list.Count(); i++)
{
filter += "and TighteningTime !='" + list[i].TighteningTime.ToString() + "'";
}
string preString = @"SELECT * FROM T_ToolsResult WHERE 1=1 {0}";
string buildSql = string.Format(preString, filter);
var okList = context.T_ToolsResult.SqlQuery(buildSql)
.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE)
.GroupBy(m => m.TighteningTime).Select(g => (new { TighteningTime = g.Key, count = g.Count() }));
//结果值ok的加入highcharts
foreach (var item in okList)
{
transactionCounts.Add(new BReportTighteningReportModel() { Date = item.TighteningTime.ToString("yyyy/MM/dd"), Count = 0 });
}
foreach (var item in notOkList)
{
transactionCounts.Add(new BReportTighteningReportModel() { Date = item.TighteningTime.ToString("yyyy/MM/dd"), Count = item.count });
}
}
#endregion
#region 月
if (rate =="2")
{
DateTime dt = DateTime.Now;
var year = dt.Year;
var weekT = Convert.ToDateTime(dt.Year + "-" + month + "-" + 1);
var weekE = Convert.ToDateTime(dt.Year + "-" + month + "-" + 31);
var totalList = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE).ToList().Count();
var notOkList = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE && m.TighteningResult != "ok")
.GroupBy(m => m.TighteningTime).Select(g => (new { TighteningTime = g.Key, count = g.Count() }));
//统计结果值ok
IList<T_ToolsResultDefinition> list = context.T_ToolsResult.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE && m.TighteningResult != "ok").Distinct().ToList();
string filter = string.Empty;
for (int i = 0; i < list.Count(); i++)
{
filter += "and TighteningTime !='" + list[i].TighteningTime.ToString() + "'";
}
string preString = @"SELECT * FROM T_ToolsResult WHERE 1=1 {0}";
string buildSql = string.Format(preString, filter);
var okList = context.T_ToolsResult.SqlQuery(buildSql)
.Where(m => m.TighteningTime >= weekT && m.TighteningTime <= weekE)
.GroupBy(m => m.TighteningTime).Select(g => (new { TighteningTime = g.Key, count = g.Count() }));
//结果值ok的加入highcharts
foreach(var item in okList)
{
transactionCounts.Add(new BReportTighteningReportModel() { Date = item.TighteningTime.ToString("yyyy/MM/dd"), Count = 0 });
}
//结果值notok的加入highcharts
foreach (var item in notOkList)
{
transactionCounts.Add(new BReportTighteningReportModel() { Date = item.TighteningTime.ToString("yyyy/MM/dd"), Count = item.count });
}
}
#endregion
#region 数据
//var transactionCounts = new List<BReportTighteningReportModel>{
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="April",Count=35},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="April",Count=35},
// new BReportTighteningReportModel(){MonthName="January",Count=30},
// new BReportTighteningReportModel(){MonthName="February",Count=40},
// new BReportTighteningReportModel(){MonthName="March",Count=4},
// new BReportTighteningReportModel(){MonthName="January",Count=30}
//};
#endregion
#region 生成highcharts报表
var xDataMonths = transactionCounts.OrderBy(i => i.Date).Select(i => i.Date).ToArray();
var yDataCounts = transactionCounts.OrderBy(i => i.Date).Select(i => new object[] { i.Count }).ToArray();
ViewBag.chart = new Highcharts("chart")
.InitChart(new Chart
{
//DefaultSeriesType = ChartTypes.Line,//折线
//DefaultSeriesType = ChartTypes.Column,//柱状
DefaultSeriesType = ChartTypes.Spline,//曲线
//背景颜色
BackgroundColor = new BackColorOrGradient(Color.AliceBlue),
//3d
//Options3d = new ChartOptions3d
//{
// Enabled = true,
// Alpha = 15,
// Beta = 15,
// Depth = 50,
// ViewDistance = 25
//}
})
.SetTitle(new Title { Text = "统计某段时间内缺陷率" })
.SetSubtitle(new Subtitle { Text = "统计" })
.SetXAxis(new XAxis { Categories = xDataMonths })
.SetYAxis(new YAxis { Title = new YAxisTitle { Text = "缺陷个数" } })
.SetTooltip(new Tooltip
{
Enabled = true,
//Formatter = @"function(){return '<b>'+this.series.name+this.x+'</b></br>:'+this.y/'"+totalList+"';}"
Formatter = @"function(){return '缺陷个数：'+this.y;}"
})
.SetPlotOptions(new PlotOptions
{
Line = new PlotOptionsLine
{
DataLabels = new PlotOptionsLineDataLabels
{
Enabled = true
},
EnableMouseTracking = false
},
//Column = new PlotOptionsColumn //3d
//{
// Depth = 25
//}
})
.SetSeries(new[]
{
new Series{Name = "缺陷率",Data = new Data(yDataCounts),Color = Color.FromArgb(124, 181, 236)},
});
#endregion
}
}
return View();
}
//前端
@using TPSystem.WWW.Models
@using TPSystem.Business;
@model TPSystem.WWW.Models.BReportTighteningReportModel
@{
ViewBag.Title = "拧紧缺陷报表查询";
Layout = "/Views/Shared/_LayoutBFactory.cshtml";
}
<script src="~/Helpers/highCharts/jquery-1.8.3.min.js"></script>
<script src="~/Helpers/highCharts/highcharts.js"></script>
<script src="~/Helpers/highCharts/exporting.js"></script>
<script src="~/Helpers/highCharts/highcharts-3d.js"></script>
<!--Head-->
<!--Head-->
<!--Content-->
<form name="form1">
<div style="overflow:auto;width:100%;">
<div class="page-content">
<div class="row">
<div class="col-md-12">
<div class="portlet box blue">
<div class="portlet-title">
<div class="caption"><i class="fa fa-edit"></i>拧紧缺陷报表</div>
</div>
<div class="portlet-body">
<table class="tab-search" border="0" cellspacing="0" cellpadding="5">
<tbody>
<tr>
<td width="30" align="center"><img src="/Content/images/icon_seach.png"></td>
<td>
统计粒度：
<select name="rate" class="sel txt-select-small">
<option value="-1">请选择</option>
<option value="0" id="opDay">日</option>
<option value="1" id="opWeek">周</option>
<option value="2" id="opMonth">月</option>
</select>
<span class="day">起始时间：</span>@Html.TextBoxFor(m => m.BeginTime, new { Class = "day txt-small", Id = "beginTime", onFocus = "WdatePicker({isShowWeek:true})" })
<span class="day">结束时间：</span>@Html.TextBoxFor(m => m.EndTime, new { Class = "day txt-small", Id = "endTime", onFocus = "WdatePicker({ShowWeek:true})" })
<span class="week">周次：</span>@Html.TextBoxFor(m => m.Week, new { @id = "week", @class = "week txt-small" }) @Html.ValidationMessageFor(m=>m.Week)
<span class="month">月份：</span>
<select name="Month" class="month txt-select-small">
<option value="1">一月</option>
<option value="2">二月</option>
<option value="3">三月</option>
<option value="4">四月</option>
<option value="5">五月</option>
<option value="6">六月</option>
<option value="7">七月</option>
<option value="8">八月</option>
<option value="9">九月</option>
<option value="10">十月</option>
<option value="11">十一月</option>
<option value="12">十二月</option>
</select>
<input name="weekTime" type="hidden" />
<input name="weekEnd" type="hidden" />
<input class="btn-small" type="submit" id="btn" value="搜索">
<input class="btn-small" name="excel" onclick="btnExcel();" type="button" value="导出Excel">
<span class="error"></span>
</td>
</tr>
</tbody>
</table>
<div class="table-supplier" style="overflow:auto;">
<table class="table table-condensed table-striped table-hover table-bordered" id="J_table_list">
@(ViewBag.chart)
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</form>
<script type="text/javascript">
$(function () {
//切换日周月
$(".week").css("display", "none");
$(".month").css("display", "none");
$('.sel').change(function () {
var val = $("select[name='rate']").children('option:selected').val();
if (val == 0) {
$(".day").css("display", "");
$(".week").css("display", "none");
$(".month").css("display", "none");
}
else if (val == 1) {
$(".week").css("display", "");
$(".day").css("display", "none");
$(".month").css("display", "none");
}
else if (val == 2) {
$(".month").css("display", "");
$(".day").css("display", "none");
$(".week").css("display", "none");
}
});
//周换算日期
$("#btn").click(function () {
var week = $("#week").val();
if (week != "") {
var mydate = new Date();
var year = mydate.getFullYear();
var yearStart = new Date(parseInt(year), 0, 1) //设置该年1.1.
var firstDay = yearStart.getDay(); //星期
var yearEnd = new Date(parseInt(year), 11, 31) //设置该年12.31.
var endDay = yearEnd.getDay(); //星期
//-------------------------------------------------------------------------------------
//判断输入是否超过最大周次.
var maxWeek; //该年最大周次.1.1.在周一到周四,则为该年第一周,否则为上年最后一周.
if ((firstDay >= 1 && firstDay <= 4) && (endDay == 0 || endDay == 4 || endDay == 5 || endDay == 6)) {
maxWeek = 53; //1.1.和12.31.都在本年,则该年有53周,否则52周
}
else {
maxWeek = 52;
}
if (parseInt(document.form1.week.value, 10) > maxWeek) {
alert(year + " 年只有 " + maxWeek + " 周,请重新填写周次.");
document.form1.week.focus();
return false;
}
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
//对1.1.所在周的前后几天特殊处理.
if (firstDay >= 0 && firstDay <= 4) { other = firstDay - 1; }
else { other = firstDay - 8 }
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
//时间调整,得出要计算周的起/始时间.
//距离当年1.1.的总天数
days = (parseInt(document.form1.week.value, 10) - 1) * 7 - other;
//转换成Ms.......
var oneMinute = 60 * 1000;
var oneHour = oneMinute * 60;
var oneDay = oneHour * 24;
//1.1.至1/1/70的毫秒数
var dateInMs = yearStart.getTime();
//当前所选周第一天离1/1/70的毫秒数.
dateInMs += oneDay * days
//日期调整(设置1/1/70至今的毫秒数)
yearStart.setTime(dateInMs);
//当前所选周最后一天处理,同上.
var weekEnd = new Date(parseInt(year), 0, 1)
var dateInMs1 = weekEnd.getTime();
dateInMs1 += oneDay * (days + 6);
weekEnd.setTime(dateInMs1)
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
//月和日的处理,一位变两位,如:1->01.
var month = yearStart.getMonth() + 1
if (month < 10) { month = "0" + month; }
var day = yearStart.getDate();
if (day < 10) { day = "0" + day; }
var month1 = weekEnd.getMonth() + 1;
if (month1 < 10) { month1 = "0" + month1; }
var day1 = weekEnd.getDate();
if (day1 < 10) { day1 = "0" + day1; }
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
//赋值给text框显示.
document.form1.weekTime.value = yearStart.getFullYear() + "-" + month + "-" + day;
document.form1.weekEnd.value = weekEnd.getFullYear() + "-" + month1 + "-" + day1;
//-------------------------------------------------------------------------------------
}
});
});
</script>
<!--Content-->
