问题描述
我有下表来生成json数据集
Hi i have the following table to generate a json dataset
它具有以下数据，我具有脚本表
it has the following data i have script teh table
USE [GridSamples]
GO
/****** Object: Table [dbo].[SalesStats] Script Date: 12/13/2016 07:34:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesStats](
[id] [int] IDENTITY(1,1) NOT NULL,
[makes] [nchar](10) NOT NULL,
[models] [nchar](10) NOT NULL,
[fuelusagecity] [nchar](10) NOT NULL,
[fuelusagehwy] [nchar](10) NOT NULL,
[salesaboveavg] [bit] NOT NULL,
[totalnumofsales] [money] NOT NULL,
[highsalestext] [varchar](50) NULL,
[saledate] [date] NOT NULL,
CONSTRAINT [PK_SalesStats] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[SalesStats] ON
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (1, N'toyota ', N'corolla ', N'17 ', N'12 ', 0, 120000.0000, NULL, CAST(0x9A330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (2, N'toyota ', N'corolla ', N'10 ', N'14 ', 0, 100000.0000, N'HIGH', CAST(0xA8330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (3, N'toyota ', N'belta ', N'15 ', N'10 ', 1, 200000.0000, NULL, CAST(0xC2330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (4, N'toyota ', N'camry ', N'13 ', N'10 ', 0, 300000.0000, N'HIGH', CAST(0x29340B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (5, N'nissan ', N'skyline ', N'14 ', N'9 ', 1, 500000.0000, N'HIGH', CAST(0x48330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (6, N'nissan ', N'zx300 ', N'10 ', N'8 ', 0, 400000.0000, NULL, CAST(0x2B350B00 AS Date))
GO
SET IDENTITY_INSERT [dbo].[SalesStats] OFF
GO
在我的中间层ASP MVC中，控制器将其转换为以下json (更新为具有STRING SalesDate)
In my middle tier ASP MVC the controller converts this to the the following json (UPADATED TO HAVE A STRING SalesDate)
var data = [{"id":1,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":120000.0000,"highsalestext":null,"salesdate":"2010-12-01"},{"id":2,"make":"toyota","model":"corolla","fuelusagecity":"10","fuelusagehwy":"14","salesaboveavg":false,"totalnumberofsales":100000.0000,"highsalestext":"HIGH","salesdate":"2010-12-15"},{"id":3,"make":"toyota","model":"belta","fuelusagecity":"15","fuelusagehwy":"10","salesaboveavg":true,"totalnumberofsales":200000.0000,"highsalestext":null,"salesdate":"2011-01-10"},{"id":4,"make":"toyota","model":"camry","fuelusagecity":"13","fuelusagehwy":"10","salesaboveavg":false,"totalnumberofsales":300000.0000,"highsalestext":"HIGH","salesdate":"2011-04-23"},{"id":5,"make":"nissan","model":"skyline","fuelusagecity":"14","fuelusagehwy":"9","salesaboveavg":true,"totalnumberofsales":500000.0000,"highsalestext":"HIGH","salesdate":"2010-09-10"},{"id":6,"make":"nissan","model":"zx300","fuelusagecity":"10","fuelusagehwy":"8","salesaboveavg":false,"totalnumberofsales":400000.0000,"highsalestext":null,"salesdate":"2012-01-06"}];
我使用以下代码使用JqGrid自由网格生成网格.为了简便起见，我已将上述json数组添加到data数组
i have used the following code to generate the grid using JqGrid free-grid. for easiness i have added the above json array to the data array
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title></title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/south-street/jquery-ui.css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/css/ui.jqgrid.min.css" />
</head>
<body>
<table id="list483"></table>
<div id=""></div>
<!--<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.12.3/jquery.min.js"></script>-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<!--<script src="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/js/i18n/grid.locale-de.min.js"></script>-->
<script src="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/js/jquery.jqgrid.min.js"></script>
<script>
var data = [{"id":1,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":120000.0000,"highsalestext":null,"salesdate":"2010-12-01"},{"id":2,"make":"toyota","model":"corolla","fuelusagecity":"10","fuelusagehwy":"14","salesaboveavg":false,"totalnumberofsales":100000.0000,"highsalestext":"HIGH","salesdate":"2010-12-15"},{"id":3,"make":"toyota","model":"belta","fuelusagecity":"15","fuelusagehwy":"10","salesaboveavg":true,"totalnumberofsales":200000.0000,"highsalestext":null,"salesdate":"2011-01-10"},{"id":4,"make":"toyota","model":"camry","fuelusagecity":"13","fuelusagehwy":"10","salesaboveavg":false,"totalnumberofsales":300000.0000,"highsalestext":"HIGH","salesdate":"2011-04-23"},{"id":5,"make":"nissan","model":"skyline","fuelusagecity":"14","fuelusagehwy":"9","salesaboveavg":true,"totalnumberofsales":500000.0000,"highsalestext":"HIGH","salesdate":"2010-09-10"},{"id":6,"make":"nissan","model":"zx300","fuelusagecity":"10","fuelusagehwy":"8","salesaboveavg":false,"totalnumberofsales":400000.0000,"highsalestext":null,"salesdate":"2012-01-06"}];
$("#list483").jqGrid("jqPivot",
data,
{
frozenStaticCols: true,
xDimension: [
{/*x0*/ dataName: "make", width: 200, label: "Make" },
{/*x1*/ dataName: "model", skipGrouping: true },
{/*x2*/ dataName: "fuelusagecity", hidden: true, skipGrouping: true },
{/*x3*/ dataName: "fuelusagehwy", width: 80, align: "center",
label: "fuel consumption", skipGrouping: true,
formatter: function (cellvalue, options, rowObject) {
return rowObject.x2 === null || rowObject.x3 === null ?
"-" :
String(rowObject.x2) + "-" + String(cellvalue);
}
},
{/*x4*/ dataName: "salesaboveavg", hidden: true, width: 50, align: "center", skipGrouping: true },
{/*x5*/ dataName: "highsalestext", hidden: true, skipGrouping: true }
],
yDimension: [
{/*y0*/ dataName: "salesdate",
sorttype: "date",
formatter: function (cellvalue, options, rowObject) {
//var x = rawObject.y0;
console.log(rowObject);
// return rowObject;
}
}],
aggregates: [{
member: "totalnumberofsales",
aggregator: "max"
}]
},
// grid options
{
iconSet: "fontAwesome",
cmTemplate: { autoResizable: true, width: 90 },
shrinkToFit: false,
useUnformattedDataForCellAttr: false,
autoResizing: { compact: true },
groupingView: {
groupField: ["x0"],
groupColumnShow: [false],
groupText: ['<b>{0}</b>']
},
width: 450,
pager: true,
rowNum: 20,
rowList: [5, 10, 20, 100, "10000:All"],
caption: "<b>Car sales statistics</b>"
}
);
</script>
</body>
</html>
生成的网格显示在图像链接下方
the generated grid is shown below image link
我有很多与此网格有关的问题.但随着答案的进展，我将一一发布.但是现在，以下问题开始于:
i have so many questions relating to this grid. but i will post them one by one as answers progresses. But for now the following questions to start off with:
-
当我打开分组功能时，应该显示在分组名称后面的折叠图标图像发生了什么? 已解决:我已经找到了这个问题的答案，缺少令人赞叹的字体库导致了问题
如何转换日期列中的json日期并在实际日期中显示它们?已解决:一旦我在中间层进行了转换以将saledate返回为字符串值，此问题就解决了 strong>
How do i convert the json dates in teh dates columns and display them in actual dates?SOLVED:This issue was solved once i made the conversion in the middle tier to return saledate a string value
-
Grid在Toyota Corolla上有两个条目，但这应该是一个条目，并且在该条目中，应在两个匹配的日期上放置
totalnumofsales值. AKA在2010-12-01单元格中的Toyota花冠120000和2010-12-15中的100000的单行条目中燃油消耗值也必须是第一行值，即10-14
The Grid has two entries on the Toyota Corolla but this should be one entry and in that entry,
totalnumofsalesvalues should be placed on the two matching dates. AKA in a single row entry to toyota corolla120000in2010-12-01cell and100000in2010-12-15also teh fuel consuption value must be the first row value i.e. 10-14
对于没有值的单元格，显示0.00而不是显示0.00，我需要它们显示一个空单元格
for the cell that don't have values shows 0.00 instead of showing 0.00 i need them to display an empty cell
我如何关闭网格的自动排序，因为生成网格时，组名按字母顺序排序，但我不会对网格进行排序，而是保留来自后端的原始顺序
How do i turn off the automatic sorting of the grid becuase when grid is generated the groups names are sorted in the alphabetical order but i dont wont grid to sort them, instead keep their original order that comes from the backend
我如何达到上述目标?
how do i acheive the above?
推荐答案
我认为您的问题的起源是一些常见的误解，即什么是数据透视表以及如何使用它.我会在下面尝试详细说明您输入数据的示例.
I think origin of your question are some common misunderstanding what is pivot table and how one can use it. I try to explain all below in details on an example of your input data.
您使用以下输入数据:
[{
"id": 1,
"make": "toyota",
"model": "corolla",
"fuelusagecity": "17",
"fuelusagehwy": "12",
"salesaboveavg": false,
"totalnumberofsales": 120000.0000,
"highsalestext": null,
"salesdate": "2010-12-01"
}, {
"id": 2,
"make": "toyota",
"model": "corolla",
"fuelusagecity": "10",
"fuelusagehwy": "14",
"salesaboveavg": false,
"totalnumberofsales": 100000.0000,
"highsalestext": "HIGH",
"salesdate": "2010-12-15"
}, {
"id": 3,
"make": "toyota",
"model": "belta",
"fuelusagecity": "15",
"fuelusagehwy": "10",
"salesaboveavg": true,
"totalnumberofsales": 200000.0000,
"highsalestext": null,
"salesdate": "2011-01-10"
}, {
"id": 4,
"make": "toyota",
"model": "camry",
"fuelusagecity": "13",
"fuelusagehwy": "10",
"salesaboveavg": false,
"totalnumberofsales": 300000.0000,
"highsalestext": "HIGH",
"salesdate": "2011-04-23"
}, {
"id": 5,
"make": "nissan",
"model": "skyline",
"fuelusagecity": "14",
"fuelusagehwy": "9",
"salesaboveavg": true,
"totalnumberofsales": 500000.0000,
"highsalestext": "HIGH",
"salesdate": "2010-09-10"
}, {
"id": 6,
"make": "nissan",
"model": "zx300",
"fuelusagecity": "10",
"fuelusagehwy": "8",
"salesaboveavg": false,
"totalnumberofsales": 400000.0000,
"highsalestext": null,
"salesdate": "2012-01-06"
}]
每个数据项都有许多属性.一些属性包含不同(甚至唯一)的值(例如id).其他属性应分组在一起，并在组内的每个属性上对其他属性计算一些汇总功能.我以一个例子解释了上面的陈述.
Every item of data have many properties. Some properties contains different (or even unique) values (like id). Other properties should be grouped together and calculate some aggregation function on another properties on every item inside of the group. I explain the above statement on an example.
输入数据包含某些汽车型号的销售信息.例如，按日期或年份等显示每种车型的销售数量可能会很有趣.
The input data contains sale information of some car models. It could be interesting, for example, to display the number of sales of every car model by date or by year etc.
数据透视表包含三个主要参数xDimension，yDimension和aggregates.例如，请看下面的图片左侧部分(橙色标记)表示xDimension(make和model).它主要构建网格的行.右侧部分(标记为蓝色)表示yDimension(输入项目的year和month).正确尺寸的最低级别信息包含某些输入属性(在示例中为totalnumberofsales)的聚合函数(max和count)的计算结果.
Pivot tables contains three main parameters xDimension, yDimension and aggregates. For example, look at the following picture belowThe left part (markt in orange) represents xDimension (make and model). It build mostly the rows of the grid. The right part (marked in blue) represents yDimension (year and month of the input items). The lowest level of the information on the right size contains the result of calculation of aggregate functions (max and count) on some input property (it was totalnumberofsales in the example).
如果在aggregates参数内仅定义了一个聚合函数，则将不显示聚合函数的名称:
If one defined only one aggregate function inside of aggregates parameter, then the name of aggregate function will be not displayed:
现在，我必须显示jqPivot做什么来创建jqGrid.首先，它通过所有X和Y参数扫描所有输入数据.例如，如果您定义以下枢轴模型
Now I have to display what jqPivot do to create jqGrid. First of all it scan all input data by all X and by Y parameters. If you define for example the following pivot model
xDimension: [
{ dataName: "make", width: 100, label: "Make" },
{ dataName: "model", width: 100, label: "Model", align: "center" }
],
yDimension: [
{ dataName: "salesdate", sortorder: "desc" }
],
aggregates: [
{ member: "totalnumberofsales", aggregator: "max" }
]
然后将首先扫描所有数据，以查找xDimension中具有相同["make", "model"]值的项目，以及yDimension中具有["salesdate"]值的项目.输入数据具有6个元素，索引从0到5.所得的xIndexes和yIndexes包含x和y的唯一值以及源数据(从0到5)到包含数据的项的索引.可以在jqPivot调用之后添加以下几行以查看索引:
then all data will be first scanned for the items having the same ["make", "model"] values in xDimension and by ["salesdate"] in yDimension. The input data has 6 elements with the indexes from 0 till 5. The resulting xIndexes and yIndexes contains unique values by x and y and the indexes of source data (from 0 till 5) to the items, which has the data. One can add the following lines after jqPivot call to see the indexes:
var p = $("#list483").jqGrid("getGridParam");
console.log(JSON.stringify(p.pivotOptions.xIndex));
console.log(JSON.stringify(p.pivotOptions.yIndex));
结果将看到演示 https://jsfiddle.net/oadzsnov/.我在下面包括生成的xIndex和yIndex. xIndex是
As the result one will see the demo https://jsfiddle.net/oadzsnov/. I include the resulting xIndex and yIndex below. xIndex is
{
"items": [
["toyota", "corolla"],
["toyota", "belta"],
["toyota", "camry"],
["nissan", "skyline"],
["nissan", "zx300"]
],
"indexesOfSourceData": [
[0, 1],
[2],
[3],
[4],
[5]
],
"trimByCollect": true,
"caseSensitive": false,
"skipSort": true,
"fieldLength": 2,
"fieldNames": ["make", "model"],
"fieldSortDirection": [1, 1],
"fieldCompare": [null, null]
}
yIndex是
{
"items": [
["2012-01-06"],
["2011-04-23"],
["2011-01-10"],
["2010-12-15"],
["2010-12-01"],
["2010-09-10"]
],
"indexesOfSourceData": [
[5],
[3],
[2],
[1],
[0],
[4]
],
"trimByCollect": true,
"caseSensitive": false,
"skipSort": false,
"fieldLength": 1,
"fieldNames": ["salesdate"],
"fieldSortDirection": [-1],
"fieldCompare": [null]
}
xIndex.items构建生成的数据透视表的行，而yIndex构建列.您会看到这些行将是
The xIndex.items build the rows of the resulting pivot table and yIndex build the columns. You can see that the rows will be
["toyota", "corolla"],
["toyota", "belta"],
["toyota", "camry"],
["nissan", "skyline"],
["nissan", "zx300"]
和列:
["2012-01-06"],
["2011-04-23"],
["2011-01-10"],
["2010-12-15"],
["2010-12-01"],
["2010-09-10"]
还可以看到源数据的2个元素(请参见xIndex.indexesOfSourceData，它是[0, 1])xIndex.items对应于相同的x矢量["toyota", "corolla"].数据透视表的内容(请参见第一张图片上的黄色标记数据)将是执行指定的聚合函数的结果.我们用
One can see additionally that 2 elements of source data (see xIndex.indexesOfSourceData, which is [0, 1]) xIndex.items corresponds the same x-vector ["toyota", "corolla"]. The content of the pivot table (see yellow marked data on the first picture) will be the result of executing the specified aggregate function. We used
aggregates: [
{ member: "totalnumberofsales", aggregator: "max" }
]
以及索引为0和1的源元素是
and the source elements with the index 0 and 1 are
[{
...
"make": "toyota",
"model": "corolla",
...
"totalnumberofsales": 120000.0000,
...
"salesdate": "2010-12-01"
}, {
...
"make": "toyota",
"model": "corolla",
...
"totalnumberofsales": 100000.0000,
...
"salesdate": "2010-12-15"
}
这些项目具有个不同的y矢量(第一个项目为"salesdate": "2010-12-01"，第二个项目为"salesdate": "2010-12-15").因此，总计的计算将非常简单:对于所有其他日期，"2010-12-01"列中的120000，"2010-12-15"列中的100000和0，因为当天没有["toyota", "corolla"]的销售:
The items have different y-vectors ("salesdate": "2010-12-01" for the first item and "salesdate": "2010-12-15" for the second one). Thus the calculation of aggregates will be very simple: 120000 in the column "2010-12-01", 100000 in the column "2010-12-15" and 0 for all other dates, because there are no ["toyota", "corolla"] sales at the day:
如果您要基于salesdate分配单独 salesYear和salesMonth属性，则可以使用
If you would assign separate salesYear and salesMonth properties based on the salesdate, then you can use
xDimension: [
{ dataName: "make", width: 100, label: "Make" },
{ dataName: "model", width: 100, label: "Model", align: "center" }
],
yDimension: [
{ dataName: "salesYear", sorttype: "integer" },
{ dataName: "salesMonth", sorttype: "integer" }
],
aggregates: [{
member: "totalnumberofsales",
aggregator: "max"
}]
创建数据透视表.前两个源项目具有相同的salesdate("2010-12-15"和"2010-12-15").在这两个项目上的max聚合器的计算结果为120000，这将在结果网格中看到
to create the pivot table. Both first source items has the same salesdate ("2010-12-15" and "2010-12-15"). The calculation of max aggregater over the both items get 120000, which one will see in the resulting grid
请参见 https://jsfiddle.net/fa40onkz/
如果您可以跟随我直到那个地方，那么您可以从结果中了解任何效果，您可以在生成的数据透视表中看到它.
If you could follow me till the place, then you could understand any from effect, which you could see in the resulting pivot table.
例如，使用
{/*x2*/ dataName: "fuelusagecity", hidden: true, skipGrouping: true },
{/*x3*/ dataName: "fuelusagehwy", width: 80, align: "center",
label: "fuel consumption", skipGrouping: true,
formatter: function (cellvalue, options, rowObject) {
return rowObject.x2 === null || rowObject.x3 === null ?
"-" :
String(rowObject.x2) + "-" + String(cellvalue);
}
},
{/*x4*/ dataName: "salesaboveavg", hidden: true, width: 50, align: "center", skipGrouping: true },
{/*x5*/ dataName: "highsalestext", hidden: true, skipGrouping: true }
jpPivot选项中的
，因为两个["toyota", "corolla"]源项目具有不同 fuelusagecity，fuelusagehwy和highsalestext值. salesaboveavg的值是相同的，但是我想salesaboveavg的用法是相同的错误. 您应该从xDimension中删除项目.
in option of jqPivot, because two ["toyota", "corolla"] source items have different fuelusagecity, fuelusagehwy and highsalestext values. The salesaboveavg value is the same, but I suppose the usage of salesaboveavg is the same error. You should remove the items from xDimension.
要在具有0值的单元格中显示空白单元格，可以定义列模板
To display empty cell in the cells with 0 value you can define column template
var myIntTemplate = {
formatter: "currency",
align: "right", sorttype: "number",
searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"] },
formatoptions: { defaultValue: ""}};
并在aggregates中使用它:
aggregates: [{
member: "cellvalue",
template: myIntTemplate,
aggregator: "max"
}]
您将获得类似的结果
You will get the results like
就像在演示 https://jsfiddle.net/tnr2dgkv/
您的最后一个问题是关于排序.对源项目进行排序对于创建正确的数据透视表极为重要.您可以使用jqPivot的skipSortByX: true或/和skipSortByY: true选项禁止对初始数据进行排序，但是如果使用分组(groupField: ["x0"])，则将再次对结果数据进行排序.我认为保持结果项顺序的最简单方法(但在实现中仍然不是那么简单)是通过为xDimension项定义自定义排序功能.我建议您阅读 Wiki文章以了解更多信息信息.
Your last question was about sorting. Sorting of source items is extremely important to create correct pivot table. You can use skipSortByX: true or/and skipSortByY: true options of jqPivot to suppress sorting of initial data, but the resulting data will be sorted once more if you use grouping (groupField: ["x0"]). I think that the simplest way (but it's still not so simple in the implementation) to hold the order of resulting items would be by defining custom sorting functions for xDimension items. I recommend you to read the wiki article for more information.
这篇关于如何对自由网格jqgrid中的行进行分类?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！