Echarts与.net后端动态交互
1.效果图
2.使用技术
@1.Echarts模块化引入开发。
官方地址：http://echarts.baidu.com/echarts2/doc/doc.html#%E5%BC%95%E5%85%A5ECharts1
博主地址：https://blog.csdn.net/danielinbiti/article/details/44560075
@2 .net后端查询数据（oracle）到前端，前端解析
3.前端代码
@{
ViewBag.Title = "tradecount";
}
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
@Scripts.Render("~/Scripts/jquery-1.11.2-min.js")
@Scripts.Render("~/Common/echarts/echarts.js")
<style>
.my_table {
/*border-spacing: 0;
border: solid 10px #257ad4;*/
padding: 0;
margin: 0;
width: 94%;
height: 100%;
margin-top: 40px;
margin-left: 50px;
margin-right: 50px;
}
</style>
<title>交易量</title>
</head>
<body>
<div class="page-container">
<table border="0" class="my_table">
<colgroup style="width:50%"></colgroup>
<colgroup style="width:50%"></colgroup>
<tr>
<td style="height:50%;border:solid 10px #fff;">
<div id="et_pie" style="width:100%;height:100%;"></div>
</td>
<td style="height:50%;border:solid 10px #fff;">
<div id="et_pie1" style="width:100%;height:100%;"></div>
</td>
</tr>
<tr>
<td style="height:50%;padding-top:10px;border:solid 10px #fff;">
<div id="et_bar" style="width:100%;height:100%;"></div>
</td>
<td style="height:50%;padding-top:10px;border:solid 10px #fff;">
<div id="et_line" style="width:100%;height:100%;"></div>
</td>
</tr>
</table>
</div>
<script>
$(function () {
require.config({
paths: {
echarts: '../Common/echarts/'
}
});
require(
[
'echarts',
'echarts/theme/macarons',
'echarts/chart/bar',
'echarts/chart/line',
'echarts/chart/pie'
], function (ec) {
var pieChart = ec.init(document.getElementById('et_pie'));
$.ajax({
url: '@Url.Action("TradeCount", "PersonalContract")',
type: 'post',
data: { type: "pie"} ,
async: true,
dataType: 'json',
success: function (result) {
//console.log(result);
var res = JSON.parse(result);
pieChart.setOption({
theme: "macarons",
title: {
text: '当月交易情况',
x: 'center'
},
tooltip: {
trigger: 'item',
formatter: "{a} <br/>{b} : {c} ({d}%)"
},
legend: {
orient: 'vertical',
x: 'left',
data: ['中介交易', '个人交易']
},
calculable: true,
series: [
{
name: '访问来源',
type: 'pie',
radius: '55%',
center: ['50%', '60%'],
data: res
}
]
})
pieChart.setTheme('macarons');
}
})
var pieChart1 = ec.init(document.getElementById('et_pie1'));
$.ajax({
url: '@Url.Action("TradeCount", "PersonalContract")',
type: 'post',
data: { type: "pie1"} ,
async: true,
dataType: 'json',
success: function (result) {
//console.log(result);
var res = JSON.parse(result);
pieChart1.setOption({
theme: "macarons",
title: {
text: '当日交易情况',
x: 'center'
},
tooltip: {
trigger: 'item',
formatter: "{a} <br/>{b} : {c} ({d}%)"
},
legend: {
orient: 'vertical',
x: 'left',
data: ['中介交易', '个人交易']
},
calculable: true,
series: [
{
name: '访问来源',
type: 'pie',
radius: '55%',
center: ['50%', '60%'],
data: res
}
]
})
pieChart1.setTheme('macarons');
}
})
var bar = ec.init(document.getElementById('et_bar'));
$.ajax({
url: '@Url.Action("TradeCount", "PersonalContract")',
type: 'post',
data: { type: "bar"} ,
async: true,
dataType: 'json',
success: function (result) {
console.log(result);
var res = JSON.parse(result);
bar.setOption({
theme: "macarons",
title: {
text: '今年每月交易情况',
x: 'center'
},
tooltip: {
trigger: 'axis'
},
legend: {
y: 'bottom',
data: ['中介交易', '个人交易']
},
calculable: true,
xAxis: [
{
type: 'category',
data: ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月']
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
name: '中介交易',
type: 'bar',
data: res.agency,
markPoint: {
data: [
{ type: 'max', name: '最大值' },
{ type: 'min', name: '最小值' }
]
},
markLine: {
data: [
{ type: 'average', name: '平均值' }
]
}
},
{
name: '个人交易',
type: 'bar',
data: res.personal,
markPoint: {
data: [
{ type: 'max', name: '最大值' },
{ type: 'min', name: '最小值' }
]
},
markLine: {
data: [
{ type: 'average', name: '平均值' }
]
}
}
]
})
bar.setTheme('macarons');
}
})
var chartLine = ec.init(document.getElementById('et_line'));
$.ajax({
url: '@Url.Action("TradeCount", "PersonalContract")',
type: 'post',
data: { type: "line"} ,
async: true,
dataType: 'json',
success: function (result) {
//console.log(result);
var res = JSON.parse(result);
chartLine.setOption({
theme: "macarons",
title: {
text: '最近两周交易情况',
x: 'center'
},
tooltip: {
trigger: 'axis'
},
legend: {
y: 'bottom',
data: ['本周', '上周']
},
calculable: true,
xAxis: [
{
type: 'category',
boundaryGap: false,
data: ['周一', '周二', '周三', '周四', '周五', '周六', '周日']
}
],
yAxis: [
{
type: 'value'
}
],
series: [
{
name: '本周',
type: 'line',
stack: '总量',
data: res.ThisWeek
},
{
name: '上周',
type: 'line',
stack: '总量',
data: res.LastWeek
}
]
})
chartLine.setTheme('macarons');
}
})
}
);
});
$(function () {
$(".page-container").height($(window).height() - 80);
$(window).resize(function () {
setTimeout(function () {
$(".page-container").height($(window).height() - 80);
//$.loading($(".main"));
}, 0);
}).trigger("resize");
});
</script>
</body>
4.后端代码
业务层
using DbService; using Frame.Base; using Frame.Model; using System; using System.Collections.Generic; using System.Data; using System.Data.Odbc; using System.Linq; using System.Text; namespace Frame.Business.PersonalContract { public class Contract_TradeCount : XT_CONTRACT { public static DataTable GetDataTable(String flg) { var db = BaseOperate.CreateDatabase(); try { if (flg.Equals("1")) { var sql = new Sql("select count(*) as total,'中介交易' as name from XT_CONTRACT where MEDIATION_PERSON=1 and to_char(CREATE_TIME,'mm')=to_char(sysdate,'mm')" + " union all select count(*) as total,'个人交易'as name from XT_CONTRACT where MEDIATION_PERSON=0 and to_char(CREATE_TIME,'mm')=to_char(sysdate,'mm')"); var dbo = db.Query4DataTable(sql.SQL); return dbo; } else if(flg.Equals("2")) { var sql = new Sql("select count(*) as total,'中介交易' as name from XT_CONTRACT where MEDIATION_PERSON=1 and trunc(CREATE_TIME)=trunc(sysdate)" + " union all select count(*) as total,'个人交易'as name from XT_CONTRACT where MEDIATION_PERSON=0 and trunc(CREATE_TIME)=trunc(sysdate)"); var dbo = db.Query4DataTable(sql.SQL); return dbo; } else if (flg.Equals("3")) { var sql = new Sql("SELECT DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '01', A_AREA, 0)),0,0,count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '01', A_AREA, 0))) M1," +" DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '02', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '02', A_AREA, 0))) M2," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '03', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '03', A_AREA, 0))) M3," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '04', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '04', A_AREA, 0))) M4," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '05', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '05', A_AREA, 0))) M5," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '06', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '06', A_AREA, 0))) M6," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '07', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '07', A_AREA, 0))) M7," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '08', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '08', A_AREA, 0))) M8," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '09', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '09', A_AREA, 0))) M9," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '10', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '10', A_AREA, 0))) M10," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '11', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '11', A_AREA, 0))) M11," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '12', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '12', A_AREA, 0))) M12," + "'个人交易' as name " + " FROM XT_CONTRACT" + " WHERE MEDIATION_PERSON=0 and TO_CHAR(CREATE_TIME, 'yyyy') = TO_CHAR(sysdate, 'yyyy')" + " union all " + " SELECT DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '01', A_AREA, 0)),0,0,count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '01', A_AREA, 0))) M1," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '02', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '02', A_AREA, 0))) M2," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '03', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '03', A_AREA, 0))) M3," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '04', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '04', A_AREA, 0))) M4," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '05', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '05', A_AREA, 0))) M5," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '06', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '06', A_AREA, 0))) M6," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '07', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '07', A_AREA, 0))) M7," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '08', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '08', A_AREA, 0))) M8," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '09', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '09', A_AREA, 0))) M9," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '10', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '10', A_AREA, 0))) M10," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '11', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '11', A_AREA, 0))) M11," + " DECODE(SUM(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '12', A_AREA, 0)), 0, 0, count(DECODE(TO_CHAR(CREATE_TIME, 'MM'), '12', A_AREA, 0))) M12," + "'中介交易' as name" + " FROM XT_CONTRACT" + " WHERE MEDIATION_PERSON=1 and TO_CHAR(CREATE_TIME, 'yyyy') = TO_CHAR(sysdate, 'yyyy')"); var dbo = db.Query4DataTable(sql.SQL); return dbo; } else { var sql = new Sql("select '上周' as type,to_number(to_char(CREATE_TIME,'D')-1) as myweek,count(*) as total from XT_CONTRACT where CREATE_TIME between (trunc(sysdate,'iw') - 7) and (trunc(sysdate,'iw') - 1) group by to_number(to_char(CREATE_TIME,'D')-1)" + " union all " + "select '本周' as type, to_number(to_char(CREATE_TIME, 'D') - 1) as myweek, count(*) as total from XT_CONTRACT where to_char(CREATE_TIME, 'iw') = to_char(sysdate, 'iw') group by to_number(to_char(CREATE_TIME, 'D') - 1)"); var dbo = db.Query4DataTable(sql.SQL); return dbo; } } catch (Exception ex) { throw ex; } finally { db.Dispose(); } } } }
视图层
public ActionResult TradeCount() { var type = Params("type"); if (!type.IsNullOrEmpty()) { var result = new JsonResult(); var flg = ""; if (type.Equals("pie")) { flg = "1"; } else if (type.Equals("pie1")) { flg = "2"; } else if (type.Equals("bar")) { flg = "3"; } else if (type.Equals("line")) { flg = "4"; } DataTable dto = Contract_TradeCount.GetDataTable(flg); List<object> lists = new List<object>(); Dictionary<String, Object> week = new Dictionary<String, Object>(); Dictionary<String, Object> year = new Dictionary<String, Object>(); if (flg.Equals("4")) { int[] snum = new int[7]; int[] bnum = new int[7]; foreach (DataRow dr in dto.Rows) { int[] num = new int[] { 1, 2, 3, 4, 5, 6, 7 }; if (dr["type"].Equals("上周")) { for(int i = 0; i < num.Length; i++) { if(Convert.ToInt32(dr["myweek"]) == num[i]) { snum[i] = Convert.ToInt32(dr["total"]); } } } else { for (int i = 0; i < num.Length; i++) { if (Convert.ToInt32(dr["myweek"]) == num[i]) { bnum[i] = Convert.ToInt32(dr["total"]); } else { bnum[i] = 0; } } } } week.Add("LastWeek", snum); week.Add("ThisWeek", bnum); result.Data = JsonHelper.Serialize(week); //result.JsonRequestBehavior = JsonRequestBehavior.AllowGet; return result; } else if (flg.Equals("3")) { int[] znum = new int[12]; int[] gnum = new int[12]; foreach (DataRow dr in dto.Rows) { String[] str = new String[] { "M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12" }; if (dr["name"].Equals("个人交易")) { for (int i = 0; i < str.Length; i++) { gnum[i] = Convert.ToInt32(dr[str[i]]); } } else { for (int i = 0; i < str.Length; i++) { znum[i] = Convert.ToInt32(dr[str[i]]); } } } year.Add("personal", gnum); year.Add("agency", znum); result.Data = JsonHelper.Serialize(year); //result.JsonRequestBehavior = JsonRequestBehavior.AllowGet; return result; } else { foreach (DataRow dr in dto.Rows) { var obj = new { value = dr["total"], name = dr["name"] }; lists.Add(obj); } result.Data = JsonHelper.Serialize(lists); //result.JsonRequestBehavior = JsonRequestBehavior.AllowGet; return result; } } else { return View(); }
5.总结
@1.Echarts模块化引入为了是做配置开发，更灵活，更全面
@2.后端.net查询数据并转为前端需要的json格式，前端做解析即可。
6.难点
@1.后端查询本周和上周每天的数据量count(*),写sql发现只查出了有数据的，如果星期一没数据则不会在结果集显示，
而前端需要一周每天的数据量，所以这个工作就交给实体层在传给前端时做数据组装，如果星期几没有数据则自动为其填充默认值0，组装完之后传给前端即可
@2.后端查询今年"个人交易"和"中介交易"每个月的数据量count(*)，此sql是由网上查询"oracle查询本年每个月的数据"，从一位博主文章中的sql修改而来。
博主文章地址：https://www.cnblogs.com/lidj/p/5482389.html
本按钮的sql是由以上的博主文章的sql变更的，关键有两点：首先是做if else判断语句，sql语句中肯定不能带有逻辑判断语句，所以需要用一个函数DECODE来到达同样的效果。
最后是查询的字段（除了时间字段之外），该字段必须是有值并且能做统计计算，这样查询出的数据才正确。
7.提升
以前写前端的Echarts报表页面，都是采用公司封装好的写，优点是写代码快，但是缺点很多，样式固定、不清楚执行原理、移植性差。
经过此次用原生的开发方式，echarts报表开发，现在已经了然于胸，以后遇到此种需求，自己可以自定义化开发。
