一、生成Excel，两大方向：
1后台生成Excel
查询数据库，使用NOPI生成Excel。
2前台js生成Excel三种方式
1）jquery.table2excel.js
--采用，优势：兼容IE和Chrome。
2）handsontable 0.32.0
问题：不兼容IE。
3）手写js有兼容性问题。
问题：浏览器兼容型问题等。
考虑到这里只需要将Echarts的数据视图生成Excel，采用最简单有效的方式：jquery.table2excel.js。
二、方案
1、 jquery.table2excel.js 和Echarts数据视图结合的取巧方案
第一步：应用js脚本，先jquery脚本后table2excel脚本。
<script src="~/Scripts/jquery-1.10.2.js"></script> <script src="~/Scripts/table2excel/jquery.table2excel.js"></script>
第二步：重写dataView-contentToOption方法，注意不能设为只读。
toolbox: {
right: '20px',
feature: {
dataView: {
show: true,
title: '数据视图',
//readOnly: true, //设置只读，会隐藏刷新按钮。
lang: ['数据视图', '关闭', '导出Excel'],
contentToOption: function (opts) {
$("#tableExcel_Day").table2excel({
exclude: ".noExl", //过滤位置的 css 类名
filename: productSelectName + '每日价格走势图' + ".xls", //文件名称
name: "Excel Document Name.xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true
});
},
optionToContent: function (opt) {
// console.log(opt);
var axisData = opt.xAxis[0].data; //坐标数据
var series = opt.series; //折线图数据
var tdHeads = '<td style="padding: 0 10px">时间</td>'; //表头第一列
var tdBodys = ''; //表数据
//组装表头
var nameData = new Array('田头价格', '批发价格', '零售价格', '交易量');
for (var i = 0; i < nameData.length; i++) {
tdHeads += '<td style="padding: 0 10px">' + nameData[i] + '</td>';
}
var table = '<table id="tableExcel_Day" border="1" class="table-bordered table-striped" style="width:100%;text-align:center" ><tbody><tr>' + tdHeads + ' </tr>';
//组装表数据
for (var i = 0, l = axisData.length; i < l; i++) {
for (var j = 0; j < series.length ; j++) {
var temp = series[j].data[i];
if (temp != null && temp != undefined) {
tdBodys += '<td>' + temp.toFixed(2) + '</td>';
} else {
tdBodys += '<td></td>';
}
}
table += '<tr><td style="padding: 0 10px">' + axisData[i] + '</td>' + tdBodys + '</tr>';
tdBodys = '';
}
table += '</tbody></table>';
// console.log(table);
return table;
}
},
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
解释 1、通过id即tableExcel_Day来找到表格。
2、exclude: “.noExl” ： 有class = “noExl” 的行不被导出；
第三步：效果图
点击“导出Excel”
存在的问题：点击“生成Excel”按钮后，视图会跳转，因为这是原本是刷新按钮，目前还未解决。
三、jquery.table2excel.js 脚本：
/*
* jQuery table2excel - v1.1.1
* jQuery plugin to export an .xls file in browser from an HTML table
* https://github.com/rainabba/jquery-table2excel
*
* Made by rainabba
* Under MIT License
*/
/*
* jQuery table2excel - v1.1.1
* jQuery plugin to export an .xls file in browser from an HTML table
* https://github.com/rainabba/jquery-table2excel
*
* Made by rainabba
* Under MIT License
*/
//table2excel.js
;(function ( $, window, document, undefined ) {
var pluginName = "table2excel", defaults = {
exclude: ".noExl",
name: "Table2Excel",
filename: "table2excel",
fileext: ".xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true
}; // The actual plugin constructor
function Plugin ( element, options ) {
this.element = element;
// jQuery has an extend method which merges the contents of two or
// more objects, storing the result in the first object. The first object
// is generally empty as we don't want to alter the default options for
// future instances of the plugin
//
this.settings = $.extend( {}, defaults, options );
this._defaults = defaults;
this._name = pluginName;
this.init();
} Plugin.prototype = {
init: function () {
var e = this; var utf8Heading = "<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">";
e.template = {
head: "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\">" + utf8Heading + "<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>",
sheet: {
head: "<x:ExcelWorksheet><x:Name>",
tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>"
},
mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>",
table: {
head: "<table>",
tail: "</table>"
},
foot: "</body></html>"
}; e.tableRows = []; // get contents of table except for exclude
$(e.element).each( function(i,o) {
var tempRows = "";
$(o).find("tr").not(e.settings.exclude).each(function (i,p) { tempRows += "<tr>";
$(p).find("td,th").not(e.settings.exclude).each(function (i,q) { // p did not exist, I corrected var rc = {
rows: $(this).attr("rowspan"),
cols: $(this).attr("colspan"),
flag: $(q).find(e.settings.exclude)
}; if( rc.flag.length > 0 ) {
tempRows += "<td> </td>"; // exclude it!!
} else {
if( rc.rows & rc.cols ) {
tempRows += "<td>" + $(q).html() + "</td>";
} else {
tempRows += "<td";
if( rc.rows > 0) {
tempRows += " rowspan=\'" + rc.rows + "\' ";
}
if( rc.cols > 0) {
tempRows += " colspan=\'" + rc.cols + "\' ";
}
tempRows += "/>" + $(q).html() + "</td>";
}
}
}); tempRows += "</tr>";
// console.log(tempRows); });
// exclude img tags
if(e.settings.exclude_img) {
tempRows = exclude_img(tempRows);
} // exclude link tags
if(e.settings.exclude_links) {
tempRows = exclude_links(tempRows);
} // exclude input tags
if(e.settings.exclude_inputs) {
tempRows = exclude_inputs(tempRows);
}
e.tableRows.push(tempRows);
}); e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName);
}, tableToExcel: function (table, name, sheetName) {
var e = this, fullTemplate="", i, link, a; e.format = function (s, c) {
return s.replace(/{(\w+)}/g, function (m, p) {
return c[p];
});
}; sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName; e.ctx = {
worksheet: name || "Worksheet",
table: table,
sheetName: sheetName
}; fullTemplate= e.template.head; if ( $.isArray(table) ) {
for (i in table) {
//fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail;
fullTemplate += e.template.sheet.head + sheetName + i + e.template.sheet.tail;
}
} fullTemplate += e.template.mid; if ( $.isArray(table) ) {
for (i in table) {
fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail;
}
} fullTemplate += e.template.foot; for (i in table) {
e.ctx["table" + i] = table[i];
}
delete e.ctx.table; var isIE = /*@cc_on!@*/false || !!document.documentMode; // this works with IE10 and IE11 both :)
//if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // this works ONLY with IE 11!!!
if (isIE) {
if (typeof Blob !== "undefined") {
//use blobs if we can
fullTemplate = e.format(fullTemplate, e.ctx); // with this, works with IE
fullTemplate = [fullTemplate];
//convert to array
var blob1 = new Blob(fullTemplate, { type: "text/html" });
window.navigator.msSaveBlob(blob1, getFileName(e.settings) );
} else {
//otherwise use the iframe and save
//requires a blank iframe on page called txtArea1
txtArea1.document.open("text/html", "replace");
txtArea1.document.write(e.format(fullTemplate, e.ctx));
txtArea1.document.close();
txtArea1.focus();
sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) );
} } else {
var blob = new Blob([e.format(fullTemplate, e.ctx)], {type: "application/vnd.ms-excel"});
window.URL = window.URL || window.webkitURL;
link = window.URL.createObjectURL(blob);
a = document.createElement("a");
a.download = getFileName(e.settings);
a.href = link; document.body.appendChild(a); a.click(); document.body.removeChild(a);
} return true;
}
}; function getFileName(settings) {
return ( settings.filename ? settings.filename : "table2excel" );
} // Removes all img tags
function exclude_img(string) {
var _patt = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i;
return string.replace(/<img[^>]*>/gi, function myFunction(x){
var res = _patt.exec(x);
if (res !== null && res.length >=2) {
return res[2];
} else {
return "";
}
});
} // Removes all link tags
function exclude_links(string) {
return string.replace(/<a[^>]*>|<\/a>/gi, "");
} // Removes input params
function exclude_inputs(string) {
var _patt = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i;
return string.replace(/<input[^>]*>|<\/input>/gi, function myFunction(x){
var res = _patt.exec(x);
if (res !== null && res.length >=2) {
return res[2];
} else {
return "";
}
});
} $.fn[ pluginName ] = function ( options ) {
var e = this;
e.each(function() {
if ( !$.data( e, "plugin_" + pluginName ) ) {
$.data( e, "plugin_" + pluginName, new Plugin( this, options ) );
}
}); // chain jQuery functions
return e;
}; })( jQuery, window, document );
有朋友说想看看源码，特加上源码，请适当参考：
前端：
@{
ViewBag.Title = "价格行情";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="row">
<div class="col-md-6">
<table>
<tr>
<td style="width:20px"></td>
<td><label id="lbDateStart" style="width:70px">开始日期:</label></td>
<td>
<input id="boxDateStart" class="easyui-datebox" />
</td>
<td style="width:20px"></td>
<td><label id="lbDateEnd" style="width:70px">结束日期:</label></td>
<td>
<input id="boxDateEnd" class="easyui-datebox" />
</td>
</tr>
</table>
</div>
<div class="col-md-6">
<table>
<tr>
<td style="width:90px"></td>
<td><label id="lbselectRegion">地区:</label></td>
<td>
<select class="easyui-combobox" id="selectRegion" data-options="required:false,label: $('#lbselectRegion'),width:150,labelWidth:50"></select>
</td>
<td style="width:20px"></td>
<td><label id="lbselectVegetableType">品种选择:</label></td>
<td>
<select class="easyui-combobox" id="selectVegetableType" data-options="required:false,label: $('#lbselectVegetableType'),width:200,labelWidth:80"></select>
</td>
</tr>
</table>
</div>
</div>
<div class="row">
<div id="cc" style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<div id="someTimePriceForm" style="width:100%;height:400px;">
<header>
<div class="panel-title">
<span id="SelectID_someTimePriceForm"></span>每日价格表
<span style="float:right;padding-right:15px">单位：元/千克</span>
</div>
</header>
</div>
</div>
<div class="col-md-6">
<div id="someTimePrice" style="width:100%;height:400px; "></div>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-12">
<table>
<tr>
<td style="width:20px"></td>
<td><label id="lbyearStartSelect_Week">开始年:</label></td>
<td>
<select class="easyui-combobox" id="yearStartSelect_WeekCombox" data-options="required:false,label: $('#lbyearStartSelect_Week'),width:150,labelWidth:50"></select>
</td>
<td style="width:20px"></td>
<td><label id="lbweekStartSelect">开始周:</label></td>
<td>
<select class="easyui-combobox" id="weekStartSelectCombox" data-options="required:false,label: $('#lbweekStartSelect'),width:150,labelWidth:50"></select>
</td>
<td style="width:90px"></td>
<td><label id="lbyearEndSelect_Week">结束年:</label></td>
<td>
<select class="easyui-combobox" id="yearEndSelect_WeekCombox" data-options="required:false,label: $('#lbyearEndSelect_Week'),width:150,labelWidth:50"></select>
</td>
<td style="width:20px"></td>
<td><label id="lbweekEndSelect">结束周:</label></td>
<td>
<select class="easyui-combobox" id="weekEndSelectCombox" data-options="required:false,label: $('#lbweekEndSelect'),width:150,labelWidth:50"></select>
</td>
</tr>
</table>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<div id="weekPriceListGrid" style="width:100%;height:400px;">
<header>
<div class="panel-title">
<span id="SelectID_weekPriceListGrid"></span>周均价格表
<span style="float:right;padding-right:15px">单位：元/千克</span>
</div>
</header>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div id="weekPrice" style="width:100%;height:400px;"></div>
</div>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<table>
<tr>
<td style="width:20px"></td>
<td><label id="lbYearMonthStart" style="width:70px">起始年月:</label></td>
<td>
<input id="attYearMonthStart" editable="false" name="attYearMonthStart" class="easyui-datebox" style="width: 172px" />
</td>
<td style="width:20px"></td>
<td><label id="lbYearMonthEnd" style="width:70px">终止年月:</label></td>
<td>
<input id="attYearMonthEnd" editable="false" name="attYearMonthEnd" class="easyui-datebox" style="width: 172px" />
</td>
</tr>
</table>
</div>
</div>
<div class="row" >
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<div id="monthlyPriceListGrid" style="width:100%;height:400px;">
<header>
<div class="panel-title">
<span id="SelectID_monthlyPriceListGrid"></span>月均价格表
<span style="float:right;padding-right:15px">单位：元/千克</span>
</div>
</header>
</div>
</div>
<div class="col-md-6">
<div id="monthPrice" style="width:100%;height:400px;">
</div>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<table>
<tr>
<td style="width:20px"></td>
<td><label id="lbselectDate">日期:</label></td>
<td>
<input class="easyui-datebox" id="selectDate" data-options="required:false,label: $('#lbselectDate'),width:220,labelWidth:50" />
</td>
</tr>
</table>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<div id="gridJghq" style="width:100%;height:400px;">
<header>
<div class="panel-title">
<span id="SelectID_gridJghq"></span>感兴趣品种价格表
<span style="float:right;padding-right:15px">单位：元/千克</span>
</div>
</header>
</div>
</div>
<div class="col-md-6">
<div id="regionProductsPrice" style="width:100%;height:400px;"></div>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<div class="row">
<div class="col-md-6">
<div id="identicalProductDifferentRegions" style="width:100%;height:400px;">
<header>
<div class="panel-title">
感兴趣地区<span id="SelectID_DifferentRegions"></span>价格表
<span style="float:right;padding-right:15px">单位：元/千克</span>
</div>
</header>
</div>
</div>
<div class="col-md-6">
<div id="productRegionsPrice" style="width:100%;height:400px;"></div>
</div>
</div>
<div class="row">
<div style="width:100%;height:20px; "></div>
</div>
<script type="text/javascript">
var curr_time = new Date();
var y = curr_time.getFullYear();
var m = curr_time.getMonth();
var d = curr_time.getDate();
//近12个月
var lastYear = new Date(y, m - , d);
//近一月
var lastMonth = new Date(y, m, d - );
//近一天
var lastDay = new Date(y, m, d - );
//开始日期
var startDay = myformatterDate(lastMonth);
//结束日期
var endDay = myformatterDate(curr_time);
//开始年
var yearStartSelect = lastYear.getFullYear();
//开始月
var monthStartSelect = lastYear.getMonth() + ;
//结束年
var yearEndSelect = y;
//结束月
var monthEndSelect = m + ;
// 日期
var selectDate = myformatterDate(lastDay);
//品种
var productSelect = null;
//地区
var locationSelect = null;
//品种名称
var productSelectName = null;
//地区名称
var locationSelectName = null;
//品种下拉框
var selectVegetableType = $("#selectVegetableType");
//城市下拉框
var selectRegion = $("#selectRegion");
//品种选择
var setProductValue = null;
function loadJghq() {
SomeTimePriceLineAJAXData(locationSelect, productSelect, startDay, endDay);
$("#someTimePriceForm").datagrid("load", {
"regions": locationSelect,
"products": productSelect,
"start": startDay,
"end": endDay,
});
}
//开始年_周下拉列表
var yearStartSelect_WeekCombox = $("#yearStartSelect_WeekCombox");
//开始周下拉列表
var weekStartSelectCombox = $("#weekStartSelectCombox");
//结束年_周下拉列表
var yearEndSelect_WeekCombox = $("#yearEndSelect_WeekCombox");
//结束周下拉列表
var weekEndSelectCombox = $("#weekEndSelectCombox");
//当前时间
var date1 = new Date();
var date2 = new Date();
//设置为1月
date2.setMonth();
//设置为1日
date2.setDate();
var time = date1 - date2;
//今天是今年第几天
var day = Math.ceil(time / ( * * * ));
//今天是今年第几周
var week = Math.ceil(day / );
//下拉列表最大年份
var yearFuture = date1.getFullYear();
//下拉列表最小年份
var yearPast = date1.getFullYear() - ;
//开始年_周
var yearStartSelect_Week = date1.getFullYear() - ;
//开始周
var weekStartSelect = week;
//结束年_周
var yearEndSelect_Week = date1.getFullYear();
//结束周
var weekEndSelect = week;
$(function () {
//开始年、结束年下拉列表数据
for (var j = yearPast; j <= yearFuture; j++) {
yearStartSelect_WeekCombox.append($("<option>").text(j).attr("value", j));
yearEndSelect_WeekCombox.append($("<option>").text(j).attr("value", j));
}
//开始周、结束周列表数据
for (var i = ; i <= ; i++) {
weekStartSelectCombox.append($("<option>").text(i).attr("value", i));
weekEndSelectCombox.append($("<option>").text(i).attr("value", i));
}
//开始周默认选中项
if (weekStartSelect != null) {
setTimeout('$("#weekStartSelectCombox").combobox("setValue", ' + weekStartSelect + ')', );
}
//结束周默认选中项
if (weekEndSelect != null) {
setTimeout('$("#weekEndSelectCombox").combobox("setValue", ' + weekEndSelect + ')', );
}
//开始年默认选中项
if (yearStartSelect_Week != null) {
setTimeout('$("#yearStartSelect_WeekCombox").combobox("setValue", ' + yearStartSelect_Week + ')', );
}
//结束年默认选中项
if (yearEndSelect_Week != null) {
setTimeout('$("#yearEndSelect_WeekCombox").combobox("setValue", ' + yearEndSelect_Week + ')', );
}
//开始周初始化
weekStartSelectCombox.combobox({
editable: false,
events: {
blur: function (s) {
}
},
onSelect: function (s) {
weekStartSelect = s.value;
WeekPriceCompareLineAJAXDataLoad(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect)
}
});
//结束周初始化
weekEndSelectCombox.combobox({
editable: false,
events: {
blur: function (s) {
}
},
onSelect: function (s) {
weekEndSelect = s.value;
WeekPriceCompareLineAJAXDataLoad(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect)
}
});
//开始年初始化
yearStartSelect_WeekCombox.combobox({
editable: false,
events: {
blur: function (s) {
}
},
onSelect: function (s) {
yearStartSelect_Week = s.value;
WeekPriceCompareLineAJAXDataLoad(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect)
}
});
//结束年初始化
yearEndSelect_WeekCombox.combobox({
editable: false,
events: {
blur: function (s) {
}
},
onSelect: function (s) {
yearEndSelect_Week = s.value;
WeekPriceCompareLineAJAXDataLoad(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect)
}
});
// 周比较条件判断
function WeekPriceCompareLineAJAXDataLoad(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect) {
if (locationSelect && productSelect && yearStartSelect_Week && weekStartSelect && yearEndSelect_Week && weekEndSelect) {
//周均价格行情表
$("#weekPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect_Week,
"weekStart": weekStartSelect,
"yearEnd": yearEndSelect_Week,
"weekEnd": weekEndSelect
});
return WeekPriceCompareLineAJAXData(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect);
}
}
for (var i in products) {
var canAdd = false;
if (!forecastPriceAll) {
for (var j in defaultFavorProductsValues) {
if (products[i].id == defaultFavorProductsValues[j]) {
canAdd = true;
}
}
}
else {
canAdd = true;
}
if (canAdd) {
var item = $("<option>").text(products[i].ncpmc).attr("value", products[i].id);
//if (item.length == 1) {
// setProductValue = products[i].id;
//}
selectVegetableType.append(item);
}
}
//品种默认选中第一项
setTimeout('$("#selectVegetableType").combobox("select", ' + selectVegetableType[][].value+ ' )', );
//地区
var setRegionValue = null;
for (var i in regionData) {
var canAdd = false;
for (var j in defaultFavorRegionValues) {
if (regionData[i].id == defaultFavorRegionValues[j]) {
canAdd = true;
}
}
if (regionData[i].id == defaultFavorRegionValues) {
canAdd = true;
}
if (canAdd) {
var item = $("<option>").text(regionData[i].cjdd).attr("value", regionData[i].id);
if (regionData[i].id == defaultRegion) {
setRegionValue = defaultRegion;
}
selectRegion.append(item);
}
}
//地区默认选中项
if (setRegionValue != null) {
setTimeout('$("#selectRegion").combobox("setValue", ' + setRegionValue + ')', );
}
//地区combobox 设置
selectRegion.combobox({
editable: false,
onSelect: function (s) {
locationSelect = s.value;
locationSelectName = s.text;
document.getElementById("SelectID_gridJghq").innerHTML = locationSelectName;
//同一地区不同品种价格柱状图
RegionProductsPriceBarAJAXData(locationSelect, defaultFavorProductsValues, selectDate);
//价格行情曲线
SomeTimePriceLineAJAXData(locationSelect, productSelect, startDay, endDay);
WeekPriceCompareLineAJAXData(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect);
//月度价格行情曲线
MonthPriceLineAJAXData(locationSelect, productSelect, yearStartSelect, monthStartSelect, yearEndSelect, monthEndSelect);
//价格行情表
$("#someTimePriceForm").datagrid("load", {
"regions": locationSelect,
"products": productSelect,
"start": startDay,
"end": endDay,
});
//月均价格行情表
$("#monthlyPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect,
"monthStart": monthStartSelect,
"yearEnd": yearEndSelect,
"monthEnd": monthEndSelect
});
//周均价格行情表
$("#weekPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect_Week,
"weekStart": weekStartSelect,
"yearEnd": yearEndSelect_Week,
"weekEnd": weekEndSelect
});
//同一地区不同品种价格柱状图
$("#gridJghq").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"date": selectDate
});
},
//onChange: function (s) {
// if (s == "") {
// $("#monthlyPriceListGrid").datagrid("load", {});
// }
//}
});
//品种选择combobox 设置
selectVegetableType.combobox({
editable: false,
events: {
blur: function (s) {
//selectVegetableType.combobox();
}
},
onSelect: function (s) {
productSelect = s.value;
productSelectName = s.text;
document.getElementById("SelectID_someTimePriceForm").innerHTML = productSelectName;
document.getElementById("SelectID_weekPriceListGrid").innerHTML = productSelectName;
document.getElementById("SelectID_monthlyPriceListGrid").innerHTML = productSelectName;
document.getElementById("SelectID_DifferentRegions").innerHTML = productSelectName;
//同一品种不同地区价格柱状图
ProductRegionsPriceBarAJAXData(defaultFavorRegionValues, productSelect, selectDate);
//价格行情曲线
SomeTimePriceLineAJAXData(locationSelect, productSelect, startDay, endDay);
WeekPriceCompareLineAJAXData(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect);
//价格行情表
$("#someTimePriceForm").datagrid("load", {
"regions": locationSelect,
"products": productSelect,
"start": startDay,
"end": endDay,
});
//月度价格行情曲线
MonthPriceLineAJAXData(locationSelect, productSelect, yearStartSelect, monthStartSelect, yearEndSelect, monthEndSelect);
//月度价格行情表
$("#monthlyPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect,
"monthStart": monthStartSelect,
"yearEnd": yearEndSelect,
"monthEnd": monthEndSelect
});
//周均价格行情表
$("#weekPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect_Week,
"weekStart": weekStartSelect,
"yearEnd": yearEndSelect_Week,
"weekEnd": weekEndSelect
});
//同一品种不同地区价格柱状图
$("#identicalProductDifferentRegions").datagrid("load", {
"products": productSelect,
// "regions": locationSelect,
"date": selectDate
});
},
});
//开始时间
$('#boxDateStart').datebox({
editable: false,
onSelect: function (startDate) {
startDay = $("#boxDateStart").datebox("getValue");
var startDate = new Date(startDay);
var endDate = new Date(endDay);
if (startDate > endDate) {
endDay = startDay;
$("#boxDateEnd").datebox("setValue", endDay);
}
loadJghq();
},
});
//结束时间
$('#boxDateEnd').datebox({
editable: false,
onSelect: function (endDate) {
endDay = $("#boxDateEnd").datebox("getValue");
var startDate = new Date(startDay);
var endDate = new Date(endDay);
if (startDate > endDate) {
startDay = endDay;
$("#boxDateStart").datebox("setValue", endDay);
}
loadJghq();
},
onChange:function (newValue,oldValue){
endDay = newValue;
loadJghq();
}
});
//日期选择对象
var p = $('#attYearMonthStart').datebox('panel'),
//日期选择对象中月份
tds = false,
//显示月份层的触发控件
span = p.find('span.calendar-text');
//开始年月
$('#attYearMonthStart').datebox({
//显示日趋选择对象后再触发弹出月份层的事件，初始化时没有生成月份层
onShowPanel: function () {
//触发click事件弹出月份层
span.trigger('click');
if (!tds)
//延时触发获取月份对象，因为上面的事件触发和对象生成有时间间隔
setTimeout(function () {
tds = p.find('div.calendar-menu-month-inner td');
tds.click(function (e) {
//禁止冒泡执行easyui给月份绑定的事件
e.stopPropagation();
//得到年份
var year = /\d{}/.exec(span.html())[],
//月份
month = parseInt($(this).attr('abbr'), );
//隐藏日期对象
$('#attYearMonthStart').datebox('hidePanel')
//设置日期的值
.datebox('setValue', year + '-' + month);
});
}, );
},
//配置parser，返回选择的日期
parser: function (s) {
if (!s) return new Date();
var arr = s.split('-');
return new Date(parseInt(arr[], ), parseInt(arr[], ) - , );
},
//配置formatter，只返回年月
formatter: function (d) {
var currentMonth = (d.getMonth() + );
var currentMonthStr = currentMonth < ? ('' + currentMonth) : (currentMonth + '');
// alert(d.getFullYear() + "-" + currentMonthStr);
return d.getFullYear() + '-' + currentMonthStr;
},
onChange: function (newValue, oldValue) {
yearStartSelect = parseInt(newValue.split('-')[]);
monthStartSelect = parseInt(newValue.split('-')[]);
//获取开始和结束时间
var start = yearStartSelect + '-' + monthStartSelect + '-' + ;
var end = yearEndSelect + '-' + monthEndSelect + '-' + ;
//日期比较方法
function CompareDate(d1, d2) {
return ((new Date(d1.replace(/-/g, "\/"))) <= (new Date(d2.replace(/-/g, "\/"))));
}
if (end != "null-null-1") {
//如果开始时间<=结束时间就执行以下代码
if (CompareDate(start, end)) {
//月度价格行情曲线
MonthPriceLineAJAXData(locationSelect, productSelect, yearStartSelect, monthStartSelect, yearEndSelect, monthEndSelect);
//月度价格行情表
$("#monthlyPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect,
"monthStart": monthStartSelect,
"yearEnd": yearEndSelect,
"monthEnd": monthEndSelect
});
} else {
$('#attYearMonthEnd').datebox("setValue", newValue)
}
}
},
});
//日期选择对象
var p_2 = $('#attYearMonthEnd').datebox('panel'),
//日期选择对象中月份
tds_2 = false,
//显示月份层的触发控件
span_2 = p_2.find('span.calendar-text');
//结束年月
$('#attYearMonthEnd').datebox({
//显示期选择对象后再触发弹出月份层的事件，初始化时没有生成月份层
onShowPanel: function () {
//触发click事件弹出月份层
span_2.trigger('click');
if (!tds_2){
//延时触发获取月份对象，因为上面的事件触发和对象生成有时间间隔
setTimeout(function () {
tds_2 = p_2.find('div.calendar-menu-month-inner td');
tds_2.click(function (e) {
//禁止冒泡执行easyui给月份绑定的事件
e.stopPropagation();
//得到年份
var year = /\d{}/.exec(span_2.html())[],
//月份
//之前是这样的month = parseInt($(this).attr('abbr'), 10) + 1;
month = parseInt($(this).attr('abbr'), );
//隐藏日期对象
$('#attYearMonthEnd').datebox('hidePanel')
//设置日期的值
.datebox('setValue', year + '-' + month);
});
}, );
}
},
onChange: function (newValue, oldValue) {
yearEndSelect = parseInt(newValue.split('-')[]);
monthEndSelect = parseInt(newValue.split('-')[]);
//获取开始和结束时间
var start = yearStartSelect + '-' + monthStartSelect + '-' + ;
var end = yearEndSelect + '-' + monthEndSelect + '-' + ;
//日期比较方法
function CompareDate(d1, d2) {
return ((new Date(d1.replace(/-/g, "\/"))) <= (new Date(d2.replace(/-/g, "\/"))));
}
//如果开始时间<=结束时间就执行以下代码
if (CompareDate(start, end)) {
//月度价格行情曲线
MonthPriceLineAJAXData(locationSelect, productSelect, yearStartSelect, monthStartSelect, yearEndSelect, monthEndSelect);
//月度价格行情表
$("#monthlyPriceListGrid").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"yearStart": yearStartSelect,
"monthStart": monthStartSelect,
"yearEnd": yearEndSelect,
"monthEnd": monthEndSelect
});
} else {
$('#attYearMonthStart').datebox("setValue", newValue)
}
},
//配置parser，返回选择的日期
parser: function (s) {
if (!s) return new Date();
var arr = s.split('-');
return new Date(parseInt(arr[], ), parseInt(arr[], ) - , );
},
//配置formatter，只返回年月
formatter: function (d) {
var currentMonth = (d.getMonth() + );
var currentMonthStr = currentMonth < ? ('' + currentMonth) : (currentMonth + '');
// alert(d.getFullYear() + "-" + currentMonthStr);
return d.getFullYear() + '-' + currentMonthStr;
},
});
$("#selectDate").datebox({
editable: false,
onSelect: function (s) {
selectDate = $.fn.datebox.defaults.formatter(s);
TodayloadData();
},
onChange: function (newValue, oldValue) {
selectDate = newValue;
TodayloadData();
}
});
function TodayloadData() {
//同一地区不同品种价格柱状图
$("#gridJghq").datagrid("load", {
"products": productSelect,
"regions": locationSelect,
"date": selectDate
});
//同一品种不同地区价格柱状图
$("#identicalProductDifferentRegions").datagrid("load", {
"products": productSelect,
// "regions": locationSelect,
"date": selectDate
});
//同一地区不同品种价格柱状图
RegionProductsPriceBarAJAXData(locationSelect, defaultFavorProductsValues, selectDate);
//同一品种不同地区的价格柱状图
ProductRegionsPriceBarAJAXData(defaultFavorRegionValues, productSelect, selectDate);
}
//价格行情表（）
$("#someTimePriceForm").datagrid({
// title: "每日价格表",
url: '@Url.Action("SomeTimePriceFormDataAjax")',
method: "get",
dataType: "json",
height: ,
idField: "ID",
pageSize: ,
singleSelect: true,
multiSort: true,
columns: [[
{ field: 'cjrq', title: '日期', width: '15%', sortable: true, halign: 'center', align: 'center', formatter: formatDate },
{ field: 'yjxzqname', title: '地区', width: '15%', halign: 'center', align: 'center' },
{ field: 'ncpmc', title: '品种', width: '15%', sortable: true, halign: 'center', align: 'center' },
{ field: 'ttjg', title: '田头价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'pfjg', title: '批发价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'lsjg', title: '零售价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'jyl', title: '交易量(吨)', width: '16%', halign: 'center', align: 'center', formatter: formatFloat },
]],
pagination: true,
remoteFilter: true,
rownumbers: true,
loader: function (param, success, error) {
// param.regions = defaultFavorRegionValues.toString();
// param.products = defaultFavorProductsValues.toString();
$.ajax({
url: "@Url.Action("SomeTimePriceFormDataAjax")",
data: param,
type: "get",
dataType: "json",
//contentType: "application/json",
success: function (data) {
success(data);
}
});
}
});
//周均价格行情表
$("#weekPriceListGrid").datagrid({
// title: "周均价格表",
url: '@Url.Action("weekPriceList")',
method: "get",
dataType: "json",
height: ,
idField: "ID",
pageSize: ,
singleSelect: true,
multiSort: true,
columns: [[
{ field: 'cjyear', title: '年', width: '10%', sortable: true, halign: 'center', align: 'center' },
{ field: 'cjweek', title: '周', width: '8%', sortable: true, halign: 'center', align: 'center' },
{ field: 'yjxzqname', title: '地区', width: '13%', halign: 'center', align: 'center' },
{ field: 'ncpmc', title: '品种', width: '15%', halign: 'center', align: 'center' },
{ field: 'ttjg', title: '田头价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'pfjg', title: '批发价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'lsjg', title: '零售价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'jyl', title: '交易量(吨)', width: '18%', halign: 'center', align: 'center', formatter: formatFloat },
]],
pagination: true,
remoteFilter: true,
rownumbers: true,
loader: function (param, success, error) {
// param.regions = defaultFavorRegionValues.toString();
// param.products = defaultFavorProductsValues.toString();
//param.regions = locationSelect;
//param.products=productSelect;
$.ajax({
url: "@Url.Action("WeekPriceList")",
data: param,
type: "get",
dataType: "json",
//contentType: "application/json",
success: function (data) {
success(data);
}
});
}
});
//月均价格行情表
$("#monthlyPriceListGrid").datagrid({
// title: "月均价格表",
url: '@Url.Action("MonthlyPriceList")',
method: "get",
dataType: "json",
height: ,
idField: "ID",
pageSize: ,
singleSelect: true,
multiSort: true,
columns: [[
{ field: 'cjyear', title: '年', width: '10%', sortable: true, halign: 'center', align: 'center' },
{ field: 'cjmonth', title: '月', width: '8%', sortable: true, halign: 'center', align: 'center' },
{ field: 'yjxzqname', title: '地区', width: '13%', halign: 'center', align: 'center' },
{ field: 'ncpmc', title: '品种', width: '15%', halign: 'center', align: 'center' },
{ field: 'ttjg', title: '田头价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'pfjg', title: '批发价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'lsjg', title: '零售价格', width: '12%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'jyl', title: '交易量(吨)', width: '18%', halign: 'center', align: 'center', formatter: formatFloat },
]],
pagination: true,
remoteFilter: true,
rownumbers: true,
loader: function (param, success, error) {
// param.regions = defaultFavorRegionValues.toString();
// param.products = defaultFavorProductsValues.toString();
//param.regions = locationSelect;
//param.products=productSelect;
$.ajax({
url: "@Url.Action("MonthlyPriceList")",
data: param,
type: "get",
dataType: "json",
//contentType: "application/json",
success: function (data) {
success(data);
}
});
}
});
//同一地区不同品种价格表
$("#gridJghq").datagrid({
// title: "感兴趣品种价格表",
url: '@Url.Action("yjdatealljg")',
method: "get",
dataType: "json",
height: ,
idField: "ID",
pageSize: ,
singleSelect: true,
multiSort: true,
columns: [[
{ field: 'cjrq', title: '日期', width: '15%', sortable: true, halign: 'center', align: 'center', formatter: formatDate },
{ field: 'yjxzqname', title: '地区', width: '15%', halign: 'center', align: 'center' },
{ field: 'ncpmc', title: '品种', width: '15%', sortable: true, halign: 'center', align: 'center' },
{ field: 'ttjg', title: '田头价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'pfjg', title: '批发价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'lsjg', title: '零售价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'jyl', title: '交易量(吨)', width: '16%', halign: 'center', align: 'center', formatter: formatFloat },
]],
pagination: true,
remoteFilter: true,
rownumbers: true,
loader: function (param, success, error) {
// param.regions = defaultFavorRegionValues.toString();
param.products = defaultFavorProductsValues.toString();
console.log(defaultFavorProductsValues);
console.log(param.products);
$.ajax({
url: "@Url.Action("yjdatealljg")",
data: param,
type: "get",
dataType: "json",
//contentType: "application/json",
success: function (data) {
success(data);
}
});
}
});
//同一品种不同地区价格表
$("#identicalProductDifferentRegions").datagrid({
// title: "感兴趣地区价格表",
url: '@Url.Action("IdenticalProductDifferentRegionsAjaxData")',
method: "get",
dataType: "json",
height: ,
idField: "ID",
pageSize: ,
singleSelect: true,
multiSort: true,
columns: [[
{ field: 'cjrq', title: '日期', width: '15%', sortable: true, halign: 'center', align: 'center', formatter: formatDate },
{ field: 'yjxzqname', title: '地区', width: '15%', halign: 'center', align: 'center' },
{ field: 'ncpmc', title: '品种', width: '15%', sortable: true, halign: 'center', align: 'center' },
{ field: 'ttjg', title: '田头价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'pfjg', title: '批发价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'lsjg', title: '零售价格', width: '13%', halign: 'center', align: 'center', formatter: formatFloat },
{ field: 'jyl', title: '交易量(吨)', width: '16%', halign: 'center', align: 'center', formatter: formatFloat },
]],
pagination: true,
remoteFilter: true,
rownumbers: true,
loader: function (param, success, error) {
param.regions = defaultFavorRegionValues.toString();
$.ajax({
url: "@Url.Action("IdenticalProductDifferentRegionsAjaxData")",
data: param,
type: "get",
dataType: "json",
//contentType: "application/json",
success: function (data) {
success(data);
}
});
}
});
// 设置当月
$("#attYearMonthStart").datebox("setValue", myformatterDY(lastYear));
$("#attYearMonthEnd").datebox("setValue", myformatterDY(curr_time));
//设置日期
$("#boxDateStart").datebox("setValue", myformatterDate(lastMonth));
$("#boxDateEnd").datebox("setValue", myformatterDate(curr_time));
//日期
$("#selectDate").datebox("setValue", myformatterDate(lastDay));
});
// 格式化日期
function myformatterDY(date) {
//获取年份
var y = date.getFullYear();
//获取月份
var m = date.getMonth() + ;
return y + '-' + m;
}
function myformatterDate(date) {
var y = date.getFullYear();
var m = date.getMonth() + ;
var d = date.getDate();
return y + '-' + (m < ? ('' + m) : m) + '-' + (d < ? ('' + d) : d);
}
function formatFloat(val, row) {
if (val == null) { return null; }
return val.toFixed();
}
function formatDate(val, row) {
//return new Date(val).Format("yyyy-MM-dd");
return $.fn.datebox.defaults.formatter(new Date(val))
}
//价格行情曲线（第一排右侧） 选中品种一段日期的三个价格行情曲线
var chart_someTimePrice_line = echarts.init(document.getElementById('someTimePrice'));
option = {
title: {
text: '价格走势图',
left: "center",
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['田头价格', '批发价格', '零售价格'],
top: '30px',
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true,
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis: {
type: 'category',
boundaryGap: false,
data: [],
},
yAxis: {
type: 'value',
min: function (value) {
return Math.floor(value.min - 0.1);
}
},
series: [
{
name: '田头价格',
type: 'line',
data: [],
},
{
name: '批发价格',
type: 'line',
data: [],
},
{
name: '零售价格',
type: 'line',
data: [],
},
],
tooltip: {
trigger: 'axis',
}
};
function SomeTimePriceLineAJAXData(locationSelect, productSelect, startDay, endDay) {
//异步加载数据
var url = "@Url.Action("SomeTimePriceLineAJAXData")";
if (locationSelect && productSelect && startDay && endDay) {
$.post(url, { yjxzqid: locationSelect, ncpid: productSelect, start: startDay, end: endDay }, function (result) {
chart_someTimePrice_line.setOption({
title: {
text: productSelectName+'每日价格走势图',
left: "center",
},
legend: {
data: ['田头价格', '批发价格', '零售价格', '交易量'],
top: '30px',
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true,
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis:
{
type: 'category',
boundaryGap: false,
data: result.cjrqList,
},
yAxis:[
{
name: '价格(元/千克)',
nameLocation: 'end',
type: 'value',
min: function (value) {
return Math.floor(value.min - 0.1);
},
axisLabel: {
formatter: '{value} '
}
},
{
name: '交易量(吨)',
nameLocation: 'end',
type: 'value',
inverse:false,
}
],
series: [
{
name: '田头价格',
type: 'line',
data: result.ttjgList,
connectNulls: true
},
{
name: '批发价格',
type: 'line',
data: result.pfjgList,
connectNulls: true
},
{
name: '零售价格',
type: 'line',
data: result.lsjgList,
connectNulls: true
},
{
name: '交易量',
type: 'line',
yAxisIndex:,
data: result.jylList,
connectNulls: true
},
],
tooltip: {
trigger: 'axis',
formatter: function (data) {
var seriesNames = [];
var formateStrings = [];
var formateString = "";
// console.log(typeof (data));
formateStrings.push(data[].name);
if (data.length != undefined) {
for (var i in data) {
var item = data[i];
if (item.data == null || item.data == "-") {
}
else {
if (seriesNames.indexOf(item.seriesName) < ) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
}
else {
var item = data;
if (item.data == null || item.data == "-") {
}
else {
if (!seriesNames.contains(item.seriesName)) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
formateString = formateStrings.join("<br />");
return formateString;
}
}
})
});
}
}
// 使用刚指定的配置项和数据显示图表。
if (option && typeof option === "object") {
chart_someTimePrice_line.setOption(option, true);
}
//价格预测和实际数据比较 按周
var chart_WeekPrice_line = echarts.init(document.getElementById('weekPrice'));
option = {
title: {
text: '周均价格走势图',
left: "center",
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['田头价格', '批发价格', '零售价格','交易量'],
top: '30px',
},
// color: ['#c23531', '#2f4554', '#61a0a8', '#c23531', '#2f4554', '#61a0a8'],
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis: {
type: 'category',
boundaryGap: false,
data: [],
},
yAxis: {
type: 'value',
axisLabel: {
formatter: '{value}'
}
},
series: [
{
name: '田头价格',
type: 'line',
data: [],
},
{
name: '批发价格',
type: 'line',
data: [],
},
{
name: '零售价格',
type: 'line',
data: [],
},
]
};
// 使用刚指定的配置项和数据显示图表。
if (option && typeof option === "object") {
chart_WeekPrice_line.setOption(option, true);
}
//周均价格 异步加载数据
function WeekPriceCompareLineAJAXData(locationSelect, productSelect, yearStartSelect_Week, weekStartSelect, yearEndSelect_Week, weekEndSelect) {
var url = "@Url.Action("WeekPriceLineAJAXData")";
if (locationSelect && productSelect && yearStartSelect_Week && weekStartSelect && yearEndSelect_Week && weekEndSelect) {
$.post(url, { yjxzqid: locationSelect, ncpid: productSelect, yearStart: yearStartSelect_Week, weekStart: weekStartSelect, yearEnd: yearEndSelect_Week, weekEnd: weekEndSelect }, function (result) {
chart_WeekPrice_line.setOption({
title: {
text: productSelectName + '周均价格走势图',
left: "center",
},
legend: {
data: ['田头价格', '批发价格', '零售价格','交易量'],
top: '30px',
},
// color: ['#c23531', '#2f4554', '#61a0a8','#c23531', '#2f4554', '#61a0a8'],
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis: [
{
type: 'category',
boundaryGap: false,
data: result.weekList,
},
],
yAxis: [
{
name: '价格(元/千克)',
nameLocation: 'end',
type: 'value',
axisLabel: {
formatter: '{value}'
}
},
{
name: '交易量(吨)',
nameLocation: 'end',
type: 'value',
inverse: false,
}
],
series: [
{
name: '田头价格',
type: 'line',
data: result.ttjgList,
connectNulls: true
},
{
name: '批发价格',
type: 'line',
data: result.pfjgList,
connectNulls: true
},
{
name: '零售价格',
type: 'line',
data: result.lsjgList,
connectNulls: true
},
{
name: '交易量',
type: 'line',
yAxisIndex: ,
data: result.jylList,
connectNulls: true
},
],
tooltip: {
trigger: 'axis',
formatter: function (data) {
var seriesNames = [];
var formateStrings = [];
var formateString = "";
// console.log(typeof (data));
formateStrings.push(data[].name);
if (data.length != undefined) {
for (var i in data) {
var item = data[i];
if (item.data == null || item.data == "-") {
}
else {
if (seriesNames.indexOf(item.seriesName) < ) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
}
else {
var item = data;
if (item.data == null || item.data == "-") {
}
else {
if (!seriesNames.contains(item.seriesName)) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
formateString = formateStrings.join("<br />");
return formateString;
}
}
});
});
}
}
//月均价格行情曲线（第二排右侧）
var chart_MonthPrice_line = echarts.init(document.getElementById('monthPrice'));
option = {
title: {
text: '月均价格走势图',
left: "center",
},
legend: {
data: ['田头价格', '批发价格', '零售价格'],
top: '30px',
},
tooltip: {
trigger: 'axis'
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis: {
type: 'category',
boundaryGap: false,
data: [],
},
yAxis: {
name: '价格(元/千克)',
type: 'value',
axisLabel: {
formatter: '{value} '
}
},
series: [
{
name: '田头价格',
type: 'line',
data: [],
},
{
name: '批发价格',
type: 'line',
data: [],
},
{
name: '零售价格',
type: 'line',
data: [],
},
]
};
// 异步加载数据
function MonthPriceLineAJAXData(locationSelect, productSelect, yearStartSelect, monthStartSelect, yearEndSelect, monthEndSelect) {
var url = "@Url.Action("MonthPriceLineAJAXData")";
if (locationSelect && productSelect && yearStartSelect && monthStartSelect && yearEndSelect && monthEndSelect) {
$.post(url, { yjxzqid: locationSelect, ncpid: productSelect, yearStart: yearStartSelect, monthStart: monthStartSelect, yearEnd: yearEndSelect, monthEnd: monthEndSelect }, function (result) {
chart_MonthPrice_line.setOption({
title: {
text:productSelectName+ '月均价格走势图',
left: "center",
},
legend: {
data: ['田头价格', '批发价格', '零售价格','交易量'],
top: '30px',
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
xAxis: [
{
type: 'category',
boundaryGap: false,
data: result.monthList,
},
],
yAxis: [{
name:'价格(元/千克)',
type: 'value',
axisLabel: {
formatter: '{value} '
}
},
{
name: '交易量(吨)',
nameLocation: 'end',
type: 'value',
inverse:false,
}],
series: [
{
name: '田头价格',
type: 'line',
data: result.ttjgList,
connectNulls: true
},
{
name: '批发价格',
type: 'line',
data: result.pfjgList,
connectNulls: true
},
{
name: '零售价格',
type: 'line',
data: result.lsjgList,
connectNulls: true
},
{
name: '交易量',
type: 'line',
yAxisIndex: ,
data: result.jylList,
connectNulls: true
},
],
tooltip: {
trigger: 'axis',
formatter: function (data) {
var seriesNames = [];
var formateStrings = [];
var formateString = "";
// console.log(typeof (data));
formateStrings.push(data[].name);
if (data.length != undefined) {
for (var i in data) {
var item = data[i];
if (item.data == null || item.data == "-") {
}
else {
if (seriesNames.indexOf(item.seriesName) < ) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
}
else {
var item = data;
if (item.data == null || item.data == "-") {
}
else {
if (!seriesNames.contains(item.seriesName)) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
formateString = formateStrings.join("<br />");
return formateString;
}
}
});
});
}
}
// 使用刚指定的配置项和数据显示图表。
if (option && typeof option === "object") {
chart_MonthPrice_line.setOption(option, true);
}
//同一地区不同品种柱状图
var chart_regionProductsPrice_bar = echarts.init(document.getElementById('regionProductsPrice'));
option = {
title: {
text: '同一地区不同品种柱状图',
left: "center",
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['田头价格', '批发价格', '零售价格'],
top:
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
calculable: true,
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value',
axisLabel: {
formatter: '{value} '
}
}
],
series: [
{
name: '田头价格',
type: 'bar',
data: [],
},
{
name: '批发价格',
type: 'bar',
data: [],
},
{
name: '零售价格',
type: 'bar',
data: [],
},
]
};
// 异步数据加载
function RegionProductsPriceBarAJAXData(yjxzqidSelect, ncpidSelect, selectDate) {
var url = "@Url.Action("RegionProductsPriceBarAJAXData")";
if (yjxzqidSelect && ncpidSelect && selectDate) {
$.post(url, { yjxzqid: yjxzqidSelect, ncpid: ncpidSelect, date: selectDate }, function (result) {
chart_regionProductsPrice_bar.setOption({
title: {
text:locationSelectName+ '感兴趣品种价格柱状图',
left: "center",
},
tooltip: {
trigger: 'axis',
formatter: function (data) {
var seriesNames = [];
var formateStrings = [];
var formateString = "";
// console.log(typeof (data));
formateStrings.push(data[].name);
if (data.length != undefined) {
for (var i in data) {
var item = data[i];
if (item.data == null || item.data == "-") {
}
else {
if (seriesNames.indexOf(item.seriesName) < ) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
}
else {
var item = data;
if (item.data == null || item.data == "-") {
}
else {
if (!seriesNames.contains(item.seriesName)) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
formateString = formateStrings.join("<br />");
return formateString;
}
},
legend: {
data: ['田头价格', '批发价格', '零售价格','交易量'],
top:
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
calculable: true,
xAxis: [
{
type: 'category',
data: result.ncpmcList,
}
],
yAxis: [
{
name:'价格(元/千克)',
type: 'value',
axisLabel: {
formatter: '{value} '
}
},
{
name: '交易量(吨)',
nameLocation: 'end',
type: 'value',
inverse: false,
}
],
series: [
{
name: '田头价格',
type: 'bar',
data: result.ttjgList,
},
{
name: '批发价格',
type: 'bar',
data: result.pfjgList,
},
{
name: '零售价格',
type: 'bar',
data: result.lsjgList,
},
{
name: '交易量',
type: 'bar',
yAxisIndex: ,
data: result.jylList,
connectNulls: true
},
]
});
});
}
}
// 使用刚指定的配置项和数据显示图表。
if (option && typeof option === "object") {
chart_regionProductsPrice_bar.setOption(option, true);
}
//同一品种不同地区的价格柱状图
var chart_productRegionsPrice_bar = echarts.init(document.getElementById('productRegionsPrice'));
option = {
title: {
text: '同一品种不同地区的价格柱状图',
left: "center",
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['田头价格', '批发价格', '零售价格'],
top:
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
calculable: true,
xAxis: [
{
type: 'category',
data: []
}
],
yAxis: [
{
type: 'value',
axisLabel: {
formatter: '{value} '
}
}
],
series: [
{
name: '田头价格',
type: 'bar',
data: [],
},
{
name: '批发价格',
type: 'bar',
data: [],
},
{
name: '零售价格',
type: 'bar',
data: [],
},
]
};
// 异步数据加载
function ProductRegionsPriceBarAJAXData(yjxzqidSelect, ncpidSelect, selectDate) {
var url = "@Url.Action("ProductRegionsPriceBarAJAXData")";
if (yjxzqidSelect && ncpidSelect && selectDate) {
$.post(url, { yjxzqid: yjxzqidSelect, ncpid: ncpidSelect, date: selectDate }, function (result) {
chart_productRegionsPrice_bar.setOption({
title: {
text: '感兴趣地区' + productSelectName + '价格柱状图',
left: "center",
},
tooltip: {
trigger: 'axis',
formatter: function (data) {
var seriesNames = [];
var formateStrings = [];
var formateString = "";
// console.log(typeof (data));
formateStrings.push(data[].name);
if (data.length != undefined) {
for (var i in data) {
var item = data[i];
if (item.data == null || item.data == "-") {
}
else {
if (seriesNames.indexOf(item.seriesName) < ) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
}
else {
var item = data;
if (item.data == null || item.data == "-") {
}
else {
if (!seriesNames.contains(item.seriesName)) {
seriesNames.push(item.seriesName);
formateStrings.push(item.marker + item.seriesName + ": " + item.data.toFixed());
}
}
}
formateString = formateStrings.join("<br />");
return formateString;
}
},
legend: {
data: ['田头价格', '批发价格', '零售价格','交易量'],
top:
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
right: '20px',
feature: {
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
saveAsImage: { show: true }
}
},
calculable: true,
xAxis: [
{
type: 'category',
data: result.yjxzqnameList,
}
],
yAxis: [
{
name: '价格(元/千克)',
type: 'value',
axisLabel: {
formatter: '{value} '
}
},
{
name: '交易量(吨)',
nameLocation: 'end',
type: 'value',
inverse: false,
}
],
series: [
{
name: '田头价格',
type: 'bar',
data: result.ttjgList,
},
{
name: '批发价格',
type: 'bar',
data: result.pfjgList,
},
{
name: '零售价格',
type: 'bar',
data: result.lsjgList,
},
{
name: '交易量',
type: 'bar',
yAxisIndex: ,
data: result.jylList,
connectNulls: true
},
]
});
});
}
}
// 使用刚指定的配置项和数据显示图表。
if (option && typeof option === "object") {
chart_productRegionsPrice_bar.setOption(option, true);
}
window.onresize = function () {
setTimeout("chartResize();", );
}
function chartResize() {
$("#gridJghq").datagrid("resize");
$("#identicalProductDifferentRegions").datagrid("resize");
$("#someTimePriceForm").datagrid("resize");
$("#weekPriceListGrid").datagrid("resize");
$("#monthlyPriceListGrid").datagrid("resize");
chart_someTimePrice_line.resize();
chart_MonthPrice_line.resize();
chart_WeekPrice_line.resize();
chart_regionProductsPrice_bar.resize();
chart_productRegionsPrice_bar.resize();
}
</script>
服务器端（C#）：
api类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Vegetable.DAL;
using Vegetable.DBHelper;
using Vegetable.Models; namespace Vegetable.Controllers
{
public class JghqController : JsonController
{
DAL.lyc2Entities db = new Vegetable.DAL.lyc2Entities(); #region 价格行情-需求变更 #region 蔬菜价格查询 /// <summary>
/// 本站使用
/// </summary>
/// <returns></returns>
public ActionResult SCjgcx()
{
return View();
} /// <summary>
/// 部分页 公用
/// </summary>
/// <returns></returns>
public ActionResult SCjgcxPartial()
{
//var model = "我是Model";
//return View("SCjgcxPartial",model);
return PartialView();
} /// <summary>
/// 站外使用
/// </summary>
/// <returns></returns>
public ActionResult SCjgcxPage()
{ return View();
} #endregion #region 蔬菜价格比较
public ActionResult SCjgbj()
{
return View();
} public ActionResult SCjgbjPartial()
{
return PartialView();
} public ActionResult SCjgbjPage()
{
return View();
}
#endregion #region 生猪价格查询
public ActionResult SZjgcx()
{
return View();
}
/// <summary>
/// 部分页 公用
/// </summary>
/// <returns></returns>
public ActionResult SZjgcxPartial()
{ return PartialView();
} /// <summary>
/// 站外使用
/// </summary>
/// <returns></returns>
public ActionResult SZjgcxPage()
{ return View();
} #endregion #region 生猪价格比较
public ActionResult SZjgbj()
{
return View();
} public ActionResult SZjgbjPartial()
{
return PartialView();
} public ActionResult SZjgbjPage()
{
return View();
}
#endregion #endregion // GET: Jghq
public ActionResult Index()
{
return View();
} /// <summary>
/// 同一地区不同品种价格表
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sort"></param>
/// <param name="order"></param>
/// <param name="date"></param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <returns></returns>
public JsonResult yjdatealljg(int page,int rows, string sort, string order,DateTime? date, string products, string regions)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return yjdatealljg(pageModel, date, products, regions);
} [HttpPost]
public JsonResult yjdatealljg(PageModel pageInfo, DateTime? date, string products, string regions)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjdatealljg.Select(d => new {
ncpid= d.ncpid,
ncpmc= d.ncpmc,
yjxzqid= d.yjxzqid,
yjxzqname= d.yjxzqname,
cjrq= d.cjrq,
ttjg= d.ttjg,
pfjg= d.pfjg,
lsjg= d.lsjg,
jyl=d.jyl }); if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} if (date == null || ((DateTime)date).Year < )
{
//测试用例
// var now = DateTime.Parse("2016-06-15");
var now = DateTime.Now;
date = now.AddDays(-); //近1天（昨天） }
data = data.Where(d => d.cjrq == date);
var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach(var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid));
if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjrq);
} result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 同一品种不同地区价格表
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sort"></param>
/// <param name="order"></param>
/// <param name="date"></param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <returns></returns>
public JsonResult IdenticalProductDifferentRegionsAjaxData(int page, int rows, string sort, string order, DateTime? date, string products, string regions)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return IdenticalProductDifferentRegionsAjaxData(pageModel, date, products, regions);
} [HttpPost]
public JsonResult IdenticalProductDifferentRegionsAjaxData(PageModel pageInfo, DateTime? date, string products, string regions)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjdatealljg.Select(d => new {
ncpid = d.ncpid,
ncpmc = d.ncpmc,
yjxzqid = d.yjxzqid,
yjxzqname = d.yjxzqname,
cjrq = d.cjrq,
ttjg = d.ttjg,
pfjg = d.pfjg,
lsjg = d.lsjg,
jyl = d.jyl }); if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} if (date == null || ((DateTime)date).Year < )
{
//测试用例
// var now = DateTime.Parse("2016-06-15");
var now = DateTime.Now;
date = now.AddDays(-); //近1天（昨天） }
data = data.Where(d => d.cjrq == date);
var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach (var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid));
if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjrq);
} result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 同一品种不同地区价格表
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sort"></param>
/// <param name="order"></param>
/// <param name="date"></param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <returns></returns>
public JsonResult SZIdenticalProductDifferentRegionsAjaxData(int page, int rows, string sort, string order, DateTime? date, string products, string regions)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return SZIdenticalProductDifferentRegionsAjaxData(pageModel, date, products, regions);
} [HttpPost]
public JsonResult SZIdenticalProductDifferentRegionsAjaxData(PageModel pageInfo, DateTime? date, string products, string regions)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjdatezrsjjg.Select(d => new {
ncpid = d.ncpid,
ncpmc = d.ncpmc,
yjxzqid = d.yjxzqid,
yjxzqname = d.yjxzqname,
cjrq = d.cjrq,
pfjg = d.pfjg, }); if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} if (date == null || ((DateTime)date).Year < )
{
//测试用例
// var now = DateTime.Parse("2016-06-15");
var now = DateTime.Now;
date = now.AddDays(-); //近1天（昨天） }
data = data.Where(d => d.cjrq == date);
var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach (var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid));
if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjrq);
} result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 价格行情表
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sort"></param>
/// <param name="order"></param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name=""></param>
/// <returns></returns>
public JsonResult SomeTimePriceFormDataAjax(int page, int rows, string sort, string order, string products, string regions, DateTime? start, DateTime? end)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return SomeTimePriceFormDataAjax(pageModel, products, regions, start, end);
} [HttpPost]
public JsonResult SomeTimePriceFormDataAjax(PageModel pageInfo, string products, string regions,DateTime? start, DateTime? end)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjdatealljg.Select(d => new {
ncpid = d.ncpid,
ncpmc = d.ncpmc,
yjxzqid = d.yjxzqid,
yjxzqname = d.yjxzqname,
cjrq = d.cjrq,
ttjg = d.ttjg,
pfjg = d.pfjg,
lsjg = d.lsjg,
jyl=d.jyl }); if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} if (start == null || end==null||start>end ||((DateTime)end).Year < )
{
//测试用例
//start = DateTime.Parse("2016-06-19").AddDays(-30);
//end = DateTime.Parse("2016-06-19").AddDays(-1);
start = DateTime.Now.AddDays(-);
end = DateTime.Now.AddDays(-);
} data = data.Where(d => d.cjrq >=start&&d.cjrq<=end); var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach (var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid));
if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjrq);
} result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 月度价格行情表
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">表格数据</param>
/// <param name="sort"></param>
/// <param name="order">排序</param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <param name="yearStart"></param>
/// <param name="monthStart"></param>
/// <param name="yearEnd"></param>
/// <param name="monthEnd"></param>
/// <returns></returns>
public JsonResult WeekPriceList(int page, int rows, string sort, string order, string products, string regions, int? yearStart, int? weekStart, int? yearEnd, int? weekEnd)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return WeekPriceList(pageModel, products, regions, yearStart, weekStart, yearEnd, weekEnd);
}
[HttpPost]
public JsonResult WeekPriceList(PageModel pageInfo, string products, string regions, int? yearStart, int? weekStart, int? yearEnd, int? weekEnd)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjweekalljg.Select(d => new
{
yjxzqid = d.yjxzqid,
yjxzqname = d.yjxzqname,
ncpid = d.ncpid,
ncpmc = d.ncpmc,
cjyear = d.cjyear,
cjweek = d.cjweek,
lsjg = d.lsjg,
pfjg = d.pfjg,
ttjg = d.ttjg,
jyl = d.jyl
}
);
if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} //如果年月为空，默认显示近12个月
//if (string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(weekStart.ToString()) || string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(weekEnd.ToString()))
//{ // var now = DateTime.Now;
// DateTime start1 = new DateTime();
// start1 = now.AddMonths(-12);
// yearStart = start1.Year;
// weekStart = start1.Month;
// yearEnd = now.Year;
// weekEnd = now.Month; //} if (yearStart < yearEnd)
{
data = data.Where(d => ((d.cjyear == yearStart && d.cjweek >= weekStart) || (d.cjyear == yearEnd && d.cjweek <= weekEnd) || (d.cjyear > yearStart && d.cjyear < yearEnd)));
}
if (yearStart == yearEnd)
{ data = data.Where(d => ((d.cjyear == yearStart && d.cjweek >= weekStart && d.cjweek <= weekEnd)));
} if (yearStart > yearEnd)
{
data = data.Where(d => (d.cjyear > yearStart && d.cjyear < yearEnd));
} var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach (var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid)); if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjyear).ThenBy(d => d.cjweek); } result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 月度价格行情表
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">表格数据</param>
/// <param name="sort"></param>
/// <param name="order">排序</param>
/// <param name="products"></param>
/// <param name="regions"></param>
/// <param name="yearStart"></param>
/// <param name="monthStart"></param>
/// <param name="yearEnd"></param>
/// <param name="monthEnd"></param>
/// <returns></returns>
public JsonResult MonthlyPriceList(int page, int rows, string sort, string order, string products, string regions, int? yearStart, int? monthStart, int? yearEnd, int? monthEnd)
{
PageModel pageModel = new PageModel();
pageModel.Page = page;
pageModel.Rows = rows;
pageModel.Order = order;
pageModel.Sort = sort; return MonthlyPriceList(pageModel, products, regions, yearStart, monthStart,yearEnd, monthEnd);
}
[HttpPost]
public JsonResult MonthlyPriceList(PageModel pageInfo, string products, string regions, int? yearStart, int? monthStart, int? yearEnd, int? monthEnd)
{
ResultModel result = new ResultModel(); if (pageInfo.Page <= )
{
pageInfo.Page = ;
}
if (pageInfo.Rows <= )
{
pageInfo.Rows = ;
}
var data = db.v_yjmonthalljg.Select(d => new
{
yjxzqid = d.yjxzqid,
yjxzqname = d.yjxzqname,
ncpid = d.ncpid,
ncpmc = d.ncpmc,
cjyear = d.cjyear,
cjmonth = d.cjmonth,
lsjg = d.lsjg,
pfjg = d.pfjg,
ttjg = d.ttjg,
jyl=d.jyl
}
);
if (pageInfo.FilterRuleList != null)
{
data = data.Where(pageInfo.FilterRuleList.ToArray());
} //如果年月为空，默认显示近12个月
if (string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthStart.ToString()) || string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthEnd.ToString()))
{ var now = DateTime.Now;
DateTime start1 = new DateTime();
start1 = now.AddMonths(-);
yearStart = start1.Year;
monthStart = start1.Month;
yearEnd = now.Year;
monthEnd = now.Month; } if (yearStart < yearEnd)
{
data = data.Where(d => ((d.cjyear == yearStart && d.cjmonth >= monthStart) || (d.cjyear == yearEnd && d.cjmonth <= monthEnd) || (d.cjyear > yearStart && d.cjyear < yearEnd)));
}
if (yearStart == yearEnd)
{ data = data.Where(d => ((d.cjyear == yearStart && d.cjmonth >= monthStart && d.cjmonth <= monthEnd)));
} if (yearStart > yearEnd)
{
data = data.Where(d => (d.cjyear > yearStart && d.cjyear < yearEnd));
} var pids = new List<int>();
var rids = new List<int>();
if (products == null) products = "";
if (regions == null) regions = "";
var productIds = products.Split(',');
var regionIds = regions.Split(',');
foreach (var pid in productIds)
{
if (pid == "")
{
continue;
}
pids.Add(Convert.ToInt32(pid));
}
foreach (var rid in regionIds)
{
if (rid == "")
{
continue;
}
rids.Add(Convert.ToInt32(rid));
}
data = data.Where(d => pids.Contains(d.ncpid) && rids.Contains(d.yjxzqid)); if (pageInfo.Sort != null)
{
string[] sorts = pageInfo.Sort.Split(',');
string[] orderbys = pageInfo.Order.Split(',');
List<bool> isAscs = new List<bool>();
foreach (var o in orderbys)
{
isAscs.Add(o == "asc");
}
data = data.OrderBy(sorts, isAscs.ToArray()); }
else
{
data = data.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth); } result.rows = data.Skip((pageInfo.Page - ) * pageInfo.Rows).Take(pageInfo.Rows).ToList();
result.total = data.Count(); result.success = true;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// (选中品种一段日期的三种)价格行情曲线
/// </summary>
/// <returns></returns>
public JsonResult SomeTimePriceLineAJAXData(int yjxzqid, int ncpid, DateTime start, DateTime end)
{ //测试用例
// yjxzqid = 9;
// ncpid = 35; var result = new EchartsModel();
//如果为空，赋默认值
if (start == null || end == null)
{
//测试用例
//start = DateTime.Parse("2016-06-19").AddDays(-30 );
//end = DateTime.Parse("2016-06-19").AddDays(-1);
start = DateTime.Now.AddDays(-);
end = DateTime.Now.AddDays(-); } //结束日期大于开始日期，结束日期小于今天
if (end.Date >= start.Date )
{ V_yjdatealljgDal dal = new V_yjdatealljgDal();
var data1 = dal.SelectList(yjxzqid,ncpid,start,end);
result.lsjgList = data1.OrderBy(d => d.cjrq).Select(d => d.lsjg).ToList();
result.pfjgList = data1.OrderBy(d => d.cjrq).Select(d => d.pfjg).ToList();
result.ttjgList = data1.OrderBy(d => d.cjrq).Select(d => d.ttjg).ToList();
result.cjrqList = data1.OrderBy(d => d.cjrq).Select(d => d.cjrq.ToString("yyyy-MM-dd")).ToList();
result.jylList = data1.OrderBy(d => d.cjrq).Select(d => d.jyl).ToList();
} return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// (选中品种一段日期的三种)价格行情曲线
/// </summary>
/// <returns></returns>
public JsonResult SZSomeTimePriceLineAJAXData(int yjxzqid, int ncpid, DateTime start, DateTime end)
{ //测试用例
// yjxzqid = 9;
// ncpid = 35; var result = new EchartsModel();
//如果为空，赋默认值
if (start == null || end == null)
{
//测试用例
//start = DateTime.Parse("2016-06-19").AddDays(-30 );
//end = DateTime.Parse("2016-06-19").AddDays(-1);
start = DateTime.Now.AddDays(-);
end = DateTime.Now.AddDays(-); } //结束日期大于开始日期，结束日期小于今天
if (end.Date >= start.Date)
{ var dal = new V_yjdatezrsjjgDal();
var data1 = dal.SelectList(yjxzqid, ncpid, start, end);
result.pfjgList = data1.OrderBy(d => d.cjrq).Select(d => d.pfjg).ToList();
result.cjrqList = data1.OrderBy(d => d.cjrq).Select(d => d.cjrq.ToString("yyyy-MM-dd")).ToList();
} return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 周价格行情曲线
/// </summary>
/// <param name="yjxzqid">一级行政区id</param>
/// <param name="ncpid">农产品id</param>
/// <returns></returns>
public JsonResult WeekPriceLineAJAXData(int yjxzqid, int ncpid, int yearStart, int weekStart, int yearEnd, int weekEnd)
{
V_yjweekalljgDal dal = new V_yjweekalljgDal();
var result = new EchartsModel(); //if (!string.IsNullOrEmpty(yjxzqid.ToString())&&!string.IsNullOrEmpty(ncpid.ToString())&& !string.IsNullOrEmpty(yearStart.ToString()) && !string.IsNullOrEmpty(weekStart.ToString()) && !string.IsNullOrEmpty(yearStart.ToString()) && !string.IsNullOrEmpty(weekEnd.ToString()))
//{ //var now = DateTime.Now;
//DateTime start1 = new DateTime();
//start1 = now.AddMonths(-12);
//yearStart = start1.Year;
//weekStart = start1.Month;
//yearEnd = now.Year;
//weekEnd = now.Month; //}else
//{
// result = null; //}
var data2 = dal.SelectList(yjxzqid, ncpid, yearStart, weekStart, yearEnd, weekEnd).OrderBy(d => d.cjyear).ThenBy(d => d.cjweek); //记录表数据
result.lsjgList = data2.Select(d => d.lsjg).ToList();
result.pfjgList = data2.Select(d => d.pfjg).ToList();
result.ttjgList = data2.Select(d => d.ttjg).ToList();
result.jylList = data2.Select(d => d.jyl).ToList();
result.weekList = data2.Select(d => d.cjweek).ToList();//待删除 //年周 转换为 n周(mm.dd – mm.dd)
var weekAndYear = data2.Select(d => new { year = d.cjyear, week = d.cjweek }).ToList();
var dateList = new List<string>() { };
foreach (var item in weekAndYear)
{
var weekhelp3 = new WeekHelper((int)item.year, (int)item.week);
dateList.Add("(" + weekhelp3.DateStart.ToString("MM.dd") + "-" + weekhelp3.DateEnd.ToString("MM.dd") + ") " + item.week);
} result.weekListAndYear = dateList; return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 周价格行情曲线
/// </summary>
/// <param name="yjxzqid">一级行政区id</param>
/// <param name="ncpid">农产品id</param>
/// <returns></returns>
public JsonResult SZWeekPriceLineAJAXData(int yjxzqid, int ncpid, int yearStart, int weekStart, int yearEnd, int weekEnd)
{
var dal = new V_yjweekzrsjjgDal();
var result = new EchartsModel();
var data2 = dal.SelectList(yjxzqid, ncpid, yearStart, weekStart, yearEnd, weekEnd).OrderBy(d => d.cjyear).ThenBy(d => d.cjweek);
//记录表数据 result.pfjgList = data2.Select(d => d.pfjg).ToList();
result.weekList = data2.Select(d => d.cjweek).ToList();//待删除
//年周 转换为 n周(mm.dd – mm.dd)
var weekAndYear = data2.Select(d => new { year = d.cjyear, week = d.cjweek }).ToList();
var dateList = new List<string>() { };
foreach (var item in weekAndYear)
{
var weekhelp3 = new WeekHelper((int)item.year, (int)item.week);
dateList.Add("(" + weekhelp3.DateStart.ToString("MM.dd") + "-" + weekhelp3.DateEnd.ToString("MM.dd") + ") " + item.week);
}
result.weekListAndYear = dateList;
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 月度价格行情曲线
/// </summary>
/// <param name="yjxzqid">一级行政区id</param>
/// <param name="ncpid">农产品id</param>
/// <returns></returns>
public JsonResult MonthPriceLineAJAXData(int yjxzqid, int ncpid, int yearStart, int monthStart, int yearEnd, int monthEnd)
{
V_yjmonthalljgDal dal = new V_yjmonthalljgDal();
var result = new EchartsModel(); //如果年月为空，默认显示近12个月
if (string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthStart.ToString()) || string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthEnd.ToString()))
{ var now = DateTime.Now;
DateTime start1 = new DateTime();
start1 = now.AddMonths(-);
yearStart = start1.Year;
monthStart = start1.Month;
yearEnd = now.Year;
monthEnd = now.Month; } var data2 = dal.SelectList( yjxzqid, ncpid, yearStart, monthStart, yearEnd, monthEnd); //记录表数据
result.lsjgList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.lsjg).ToList();
result.pfjgList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.pfjg).ToList();
result.ttjgList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.ttjg).ToList();
result.monthList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.cjmonth).ToList();
result.jylList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.jyl).ToList();
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 月度价格行情曲线
/// </summary>
/// <param name="yjxzqid">一级行政区id</param>
/// <param name="ncpid">农产品id</param>
/// <returns></returns>
public JsonResult SZMonthPriceLineAJAXData(int yjxzqid, int ncpid, int yearStart, int monthStart, int yearEnd, int monthEnd)
{
var dal = new V_yjmonthzrsjjgDal();
var result = new EchartsModel();
//如果年月为空，默认显示近12个月
if (string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthStart.ToString()) || string.IsNullOrEmpty(yearStart.ToString()) || string.IsNullOrEmpty(monthEnd.ToString()))
{
var now = DateTime.Now;
DateTime start1 = new DateTime();
start1 = now.AddMonths(-);
yearStart = start1.Year;
monthStart = start1.Month;
yearEnd = now.Year;
monthEnd = now.Month;
} var data2 = dal.SelectList(yjxzqid, ncpid, yearStart, monthStart, yearEnd, monthEnd);
//记录表数据
result.pfjgList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.pfjg).ToList();
result.monthList = data2.OrderBy(d => d.cjyear).ThenBy(d => d.cjmonth).Select(d => d.cjmonth).ToList();
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 同一地区不同品种价格柱状图
/// </summary>
/// <returns></returns>
public JsonResult RegionProductsPriceBarAJAXData(int yjxzqid, int[] ncpid,DateTime date)
{
//测试用例
//yjxzqid = 9;
//ncpid = new int[] { 35, 36, 38 };
var result = new EchartsModel();
if (date == null)
{
var now = DateTime.Now;
// var now = DateTime.Parse("2016-06-15");
date = now.AddDays(-); //近1天（昨天）
}
var data = db.v_yjdatealljg .Where(d => ncpid.Contains(d.ncpid) && d.yjxzqid == yjxzqid && (d.cjrq == date)).ToList();
result.lsjgList= data.OrderBy(d => d.cjrq).Select(d => d.lsjg).ToList();
result.pfjgList = data.OrderBy(d => d.cjrq).Select(d => d.pfjg).ToList();
result.ttjgList = data.OrderBy(d => d.cjrq).Select(d => d.ttjg).ToList();
result.ncpmcList = data.OrderBy(d => d.cjrq).Select(d => d.ncpmc).ToList();
result.jylList = data.OrderBy(d => d.cjrq).Select(d => d.jyl).ToList();
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 同一品种不同地区的价格柱状图
/// </summary>
/// <returns></returns>
public JsonResult ProductRegionsPriceBarAJAXData(int[] yjxzqid, int ncpid, DateTime? date)
{
//测试用例
//yjxzqid = 9;
// yjxzqid = new int[] { 9, 4, 8 };
var result = new EchartsModel();
if (date == null)
{
var now = DateTime.Now;
// var now = DateTime.Parse("2016-06-15");
date = now.AddDays(-); //近1天（昨天）
}
var data = db.v_yjdatealljg.Where(d => d.ncpid== ncpid && yjxzqid.Contains(d.yjxzqid) && (d.cjrq == date)).ToList();
result.lsjgList = data.OrderBy(d => d.cjrq).Select(d => d.lsjg).ToList();
result.pfjgList = data.OrderBy(d => d.cjrq).Select(d => d.pfjg).ToList();
result.ttjgList = data.OrderBy(d => d.cjrq).Select(d => d.ttjg).ToList();
result.yjxzqnameList = data.OrderBy(d => d.cjrq).Select(d => d.yjxzqname).ToList();
result.jylList = data.OrderBy(d => d.cjrq).Select(d => d.jyl).ToList();
return Json(result, JsonRequestBehavior.AllowGet);
} /// <summary>
/// 同一品种不同地区的价格柱状图
/// </summary>
/// <returns></returns>
public JsonResult SZProductRegionsPriceBarAJAXData(int[] yjxzqid, int ncpid, DateTime? date)
{
//测试用例
//yjxzqid = 9;
// yjxzqid = new int[] { 9, 4, 8 };
var result = new EchartsModel();
if (date == null)
{
var now = DateTime.Now;
// var now = DateTime.Parse("2016-06-15");
date = now.AddDays(-); //近1天（昨天）
}
var data = db.v_yjdatezrsjjg.Where(d => d.ncpid == ncpid && yjxzqid.Contains(d.yjxzqid) && (d.cjrq == date)).ToList(); result.pfjgList = data.OrderBy(d => d.cjrq).Select(d => d.pfjg).ToList();
result.yjxzqnameList = data.OrderBy(d => d.cjrq).Select(d => d.yjxzqname).ToList();
return Json(result, JsonRequestBehavior.AllowGet);
} }
}
linq部分方法改写 类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Web;
using Vegetable.Models; namespace Vegetable.DAL
{
public static class QueryableExtension
{ public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string[] propertyName, bool[] ascending) where T : class
{
Type type = typeof(T); for (int i = ; i < propertyName.Length; i++)
{ PropertyInfo property = type.GetProperty(propertyName[i]);
if (property == null)
throw new ArgumentException("propertyName", "Not Exist"); ParameterExpression param = Expression.Parameter(type, "p");
Expression propertyAccessExpression = Expression.MakeMemberAccess(param, property);
LambdaExpression orderByExpression = Expression.Lambda(propertyAccessExpression, param); string methodName = ascending[i] ? "OrderBy" : "OrderByDescending";
if (i != )
{
methodName = ascending[i] ? "ThenBy" : "ThenByDescending";
} MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExpression));
source = source.Provider.CreateQuery<T>(resultExp); } return source;
} public static IQueryable<T> Where<T>(this IQueryable<T> source, FilterRule[] filterRules) where T : class
{
if (filterRules == null)
{
return source;
}
Type type = typeof(T); ParameterExpression param = Expression.Parameter(type, "c"); Expression<Func<T, bool>> op = null; foreach (var rule in filterRules)
{
PropertyInfo property = type.GetProperty(rule.Field);
if (property == null)
{
continue;
}
//c.Field==Value
//c=>c.Field.Contains(Value)
Expression left = Expression.Property(param, property); Expression right = Expression.Constant(rule.Value);
Type valueType = property.PropertyType;
if (rule.Value == null || rule.Value == "") continue;
DateTime inputDateTime = DateTime.Now;
try
{ if (valueType == typeof(int) || valueType == typeof(int?))
{
right = Expression.Constant(Convert.ToInt32(rule.Value.Split('.')[]));
}
else if (valueType == typeof(short) || valueType == typeof(short?))
{
right = Expression.Constant(Convert.ToInt16(rule.Value.Split('.')[]));
}
else if (valueType == typeof(byte) || valueType == typeof(byte?))
{
right = Expression.Constant(Convert.ToByte(rule.Value.Split('.')[]));
}
else if (valueType == typeof(long) || valueType == typeof(long?))
{
right = Expression.Constant(Convert.ToInt64(rule.Value));
}
else if (valueType == typeof(float) || valueType == typeof(float?))
{
right = Expression.Constant(Convert.ToSingle(rule.Value));
}
else if (valueType == typeof(double) || valueType == typeof(double?))
{
right = Expression.Constant(Convert.ToDouble(rule.Value));
}
else if (valueType == typeof(decimal) || valueType == typeof(decimal?))
{
right = Expression.Constant(Convert.ToDecimal(rule.Value));
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
inputDateTime = Convert.ToDateTime(rule.Value);
right = Expression.Constant(Convert.ToDateTime(rule.Value));
}
else if (valueType == typeof(Guid) || valueType == typeof(Guid?))
{
right = Expression.Constant(Guid.Parse(rule.Value));
} }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
} Expression filter = Expression.Equal(left, right);
Expression filter2 = null;
MethodInfo method; switch (rule.Op)
{
case OP.contains:
//BinaryExpression
if (valueType == typeof(string))
{
method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
right = Expression.Constant(inputDateTime.Date);
filter = Expression.GreaterThanOrEqual(left, right);
right = Expression.Constant(inputDateTime.Date.AddDays());
filter2 = Expression.LessThan(left, right);
}
else
{
filter = Expression.Equal(left, right);
}
break;
case OP.equal:
filter = Expression.Equal(left, right);
break;
case OP.notequal:
filter = Expression.NotEqual(left, right);
break;
case OP.beginwith:
method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) }); filter = Expression.Call(left, method, right);
break;
case OP.endwith:
method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) }); filter = Expression.Call(left, method, right);
break;
case OP.less:
filter = Expression.LessThan(left, right);
break;
case OP.lessorequal:
filter = Expression.LessThanOrEqual(left, right);
break;
case OP.greater:
filter = Expression.GreaterThan(left, right);
break;
case OP.greaterorequal:
filter = Expression.GreaterThanOrEqual(left, right);
break;
default:
break;
} var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (op == null)
{
op = lambda;
}
else
{
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambda.Body), op.Parameters);
} if (filter2 != null)
{
var lambda2 = Expression.Lambda<Func<T, bool>>(filter2, param);
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambda2.Body), op.Parameters);
}
} if (op != null)
{
source = source.Where(op);
}
return source;
} public static IQueryable<T> Where<T>(this IQueryable<T> source, Filter[] filters) where T : class
{
if (filters == null)
{
return source;
}
Type type = typeof(T); ParameterExpression param = Expression.Parameter(type, "c"); Expression<Func<T, bool>> op = null; foreach (var rule in filters)
{
PropertyInfo property = type.GetProperty(rule.Field);
if (property == null)
{
continue;
}
//c.Field==Value
//c=>(c.Field.Contains(Value) || c.Field.Contains(Value))
Exception outExc = new Exception(); Expression left = Expression.Property(param, property);
Type valueType = property.PropertyType;
if (rule.Value == null || rule.Value.Length <= ) continue; Expression<Func<T, bool>> lambdaOut = null;
foreach (var v in rule.Value)
{
Expression right = Expression.Constant(v);
DateTime inputDateTime = DateTime.Now;
try
{ if (valueType == typeof(int) || valueType == typeof(int?))
{
right = Expression.Constant(Convert.ToInt32(v.Split('.')[]));
}
else if (valueType == typeof(short) || valueType == typeof(short?))
{
right = Expression.Constant(Convert.ToInt16(v.Split('.')[]));
}
else if (valueType == typeof(byte) || valueType == typeof(byte?))
{
right = Expression.Constant(Convert.ToByte(v.Split('.')[]));
}
else if (valueType == typeof(long) || valueType == typeof(long?))
{
right = Expression.Constant(Convert.ToInt64(v));
}
else if (valueType == typeof(float) || valueType == typeof(float?))
{
right = Expression.Constant(Convert.ToSingle(v));
}
else if (valueType == typeof(double) || valueType == typeof(double?))
{
right = Expression.Constant(Convert.ToDouble(v));
}
else if (valueType == typeof(decimal) || valueType == typeof(decimal?))
{
right = Expression.Constant(Convert.ToDecimal(v));
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
inputDateTime = Convert.ToDateTime(v);
right = Expression.Constant(Convert.ToDateTime(v));
}
else if (valueType == typeof(Guid) || valueType == typeof(Guid?))
{
right = Expression.Constant(Guid.Parse(v));
} }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
} Expression filter = Expression.Equal(left, right);
Expression filter2 = null;
MethodInfo method; switch (rule.Op)
{
case OP.contains:
//BinaryExpression
if (valueType == typeof(string))
{
method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
right = Expression.Constant(inputDateTime.Date);
filter = Expression.GreaterThanOrEqual(left, right);
right = Expression.Constant(inputDateTime.Date.AddDays());
filter2 = Expression.LessThan(left, right);
}
else
{
filter = Expression.Equal(left, right);
}
break;
case OP.equal:
filter = Expression.Equal(left, right);
break;
case OP.notequal:
filter = Expression.NotEqual(left, right);
break;
case OP.beginwith:
method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) }); filter = Expression.Call(left, method, right);
break;
case OP.endwith:
method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) }); filter = Expression.Call(left, method, right);
break;
case OP.less:
filter = Expression.LessThan(left, right);
break;
case OP.lessorequal:
filter = Expression.LessThanOrEqual(left, right);
break;
case OP.greater:
filter = Expression.GreaterThan(left, right);
break;
case OP.greaterorequal:
filter = Expression.GreaterThanOrEqual(left, right);
break;
default:
break;
} var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (lambdaOut == null)
{
lambdaOut = lambda;
}
else
{
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.Or(lambdaOut.Body, lambda.Body), lambdaOut.Parameters);
} if (filter2 != null)
{
var lambda2 = Expression.Lambda<Func<T, bool>>(filter2, param);
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.And(lambdaOut.Body, lambda2.Body), lambdaOut.Parameters);
}
}
if (op == null)
{
op = lambdaOut;
}
else
{
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambdaOut.Body), op.Parameters);
} }
if (op != null)
{
source = source.Where(op);
}
return source; } public static IQueryable<T> Where<T>(this IQueryable<T> source, string[] columnNames, string filterString)
{ Type type = typeof(T); ParameterExpression param = Expression.Parameter(type, "c"); Expression right = Expression.Constant(filterString); //1!=1
//Expression op = Expression.NotEqual(Expression.Constant(1), Expression.Constant(1));
Expression<Func<T, bool>> op = null; foreach (var column in columnNames)
{
PropertyInfo property = type.GetProperty(column);
if (property == null)
{
continue;
}
//c.Field==Value
//c=>c.Field.Contains(Value)
Expression left = Expression.Property(param, property); Type valueType = property.PropertyType;
if (valueType != typeof(string)) continue;
if (filterString == null || filterString == "") continue; MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) }); Expression filter = Expression.Call(left, method, right); var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (op == null)
{
op = lambda;
}
else
{ op = Expression.Lambda<Func<T, bool>>(Expression.Or(op.Body, lambda.Body), op.Parameters);
}
} if (op != null)
{ source = source.Where(op);
}
return source;
}
}
}
日期转年周类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web; namespace Vegetable.DBHelper
{
/// <summary>
/// 星期一作为一周的开始，第一个完整的周作为第一周
/// </summary>
public class WeekHelper
{
/// <summary>
/// 开始日期
/// </summary>
public DateTime DateStart { get; private set; }
/// <summary>
/// 结束日期
/// </summary>
public DateTime DateEnd { get; private set; } /// <summary>
/// 年
/// </summary>
public int Year { get; private set; } /// <summary>
/// 周
/// </summary>
public int Week { get; private set; } /// <summary>
/// 一年开始的日期
/// </summary>
public DateTime YearStartDate { get; private set; } /// <summary>
/// 一年最大周数
/// </summary>
public int MaxWeek { get; private set; } public override string ToString()
{
return string.Format("{0}年第{1}周", Year, Week);
} private void SetYearStartDay(int year)
{
YearStartDate = new DateTime(year, , );
var addDay = - (int)YearStartDate.DayOfWeek;
if (addDay >= )
{
addDay = addDay - ;
}
YearStartDate = YearStartDate.AddDays(addDay);
} public WeekHelper(DateTime date)
{
SetYearStartDay(date.Year);
if (date < YearStartDate)
{
SetYearStartDay(date.Year - );
}
MaxWeek = ((new DateTime(YearStartDate.Year, , ) - YearStartDate).Days / ) + ;
Year = YearStartDate.Year;
Week = ((date - YearStartDate).Days / ) + ;
DateStart = YearStartDate.AddDays((Week - ) * );
DateEnd = YearStartDate.AddDays(Week * - );
} public WeekHelper(int year, int week)
{
SetYearStartDay(year);
var date = YearStartDate.AddDays((week - ) * );
if (date < YearStartDate)
{
SetYearStartDay(year - );
} MaxWeek = ((new DateTime(YearStartDate.Year, , ) - YearStartDate).Days / ) + ;
while (week > MaxWeek)
{
year = year + ;
week = week - MaxWeek;
SetYearStartDay(year);
date = YearStartDate.AddDays((week - ) * );
MaxWeek = ((new DateTime(YearStartDate.Year, , ) - YearStartDate).Days / ) + ;
} Year = YearStartDate.Year;
Week = ((date - YearStartDate).Days / ) + ;
DateStart = YearStartDate.AddDays((Week - ) * );
DateEnd = YearStartDate.AddDays(Week * - );
}
}
}
sqlHelper类
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web; namespace Vegetable.DBHelper
{
public static class SqlHelper
{
private static readonly string conStr = ConfigurationManager.ConnectionStrings["lyc2ConnString"].ConnectionString; //insert delete update
public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
} //返回单个值
public static object ExecuteScalar(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
} //执行返回DataReader
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
//con.Open();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
} //查询多行
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
} return dt;
} /// <summary>
/// 将DbNull转换成null
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static object FromDbNull(object obj)
{
if (obj == DBNull.Value)
{
return null;
}
else
{
return obj;
}
} /// <summary>
/// 将null转换成DbNull
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static object ToDbNull(object obj)
{
if (obj == null)
{
return DBNull.Value;
}
else
{
return obj;
}
} /// <summary>
/// 数组转字符串 逗号分隔
/// </summary>
/// <param name="ncpid"></param>
/// <returns></returns>
public static String ArrayToString(int[] ncpid)
{
string temp = "";
for (int i = ; i < ncpid.Length; i++)
{
if (i < ncpid.Length - )
{
temp = temp + ncpid[i] + ",";
}
else
{
temp = temp + ncpid[i];
} }
return temp;
}
} }
参考过的文章：
https://blog.csdn.net/wwp231/article/details/51675023
http://www.cmsdx.com/wenzhang/329.html