如何归类在免费的电网jqGrid的行？(How to categorize rows in the

2019-09-28 00:56发布

站内文章 / 前沿技术
46 0 0

嗨，我有以下表来生成一个JSON数据集

它具有以下数据我有脚本TEH表

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（UPADATED有了一个字符串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阵列 

<!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>

所生成的网格显示如下图像链接

我有与该网格这么多问题。 但我会后他们逐一回答进展。 但现在以下问题，以开始与：

  1. 当我打开格兰分组发生了什么事应该出现的组名背后格兰折叠图标形象？ 解决：我已经找到了这个问题的答案，缺少的字体真棒库导致问题

  2. 如何转换JSON的日期在格兰日期列并在实际日期显示它们？ 解决：有一次，我在做中间层的转换返回saledate一个字符串值，这个问题得到了解决

  1. 网格对丰田花冠两个条目，但是这应该是一个条目，该条目， totalnumofsales值应放在两个匹配的日期。 AKA在单排条目到丰田花冠120000在2010-12-01细胞和100000在2010-12-15也TEH燃料消费值必须是在第一行的值，即10-14

  2. 对于不具备值单元格显示为0.00，而不是显示0.00我需要他们，以显示空单元格

  3. 我如何关闭网格时生成名称的字母顺序排序组网格监守的自动分拣，但我不不会电网对它们进行排序，而不是保持他们是来自后端原始订单

我如何acheive以上？

Answer 1:

我觉得你的问题的起源是一些常见的误解是什么透视表，以及如何可以使用它。 我尝试下面的所有详细说明您的输入数据的例子。

您可以使用以下输入数据： 

[{
    "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 ）。 其他性能要相对集中，并计算上的另一个性能有一定的聚集功能在每个项目组的内部 。 我解释上的例子上述声明。

输入数据包含一些车型的销售信息。 这可能是有趣的，例如，按日期或按年等显示销售每辆汽车模型的数量

透视表包含三个主要参数xDimension ， yDimension和aggregates 。 例如，看看下面的图片如下 左侧部分（Markt的橙色）表示xDimension （ make和model ）。 它建立网格的主要行 。 右边部分（标记为蓝色）代表yDimension （ year和month输入项）。 的右侧大小的信息的最低水平包含聚集函数（计算的结果max和count上一些输入属性（它是） totalnumberofsales中的例子）。

如果一个所定义的内只有一个集合函数aggregates参数，然后聚集函数的名称将不被显示：

现在我要显示什么jqPivot做创建的jqGrid。 首先，它通过扫描所有的X和y参数所有输入数据。 如果定义例如以下透视模型 

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" }
]

然后所有的数据将首先扫描具有的项目相同的["make", "model"]的值在xDimension和由["salesdate"]中yDimension 。 输入数据具有与索引6层的元件从0至5，将所得xIndexes和yIndexes包含由x和y和源数据的索引（从0至5）中的项目，其具有数据的唯一值。 人们可以jqPivot调用后添加以下行看指标： 

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是 

{
    "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打造列。 你可以看到，行会 

["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"] 枢轴表的内容（见第一画面黄色标记的数据）将执行指定的聚合函数的结果。 我们用了 

aggregates: [
    { member: "totalnumberofsales", aggregator: "max" }
]

通过与索引0和1是源元件

[{
    ...
    "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"为第二个）。 因此聚集体的计算将是非常简单的： 120000在列"2010-12-01" ， 100000在列"2010-12-15"和0对于所有其它的日期，因为没有["toyota", "corolla"]的销售在这一天：

如果你想分配一个单独的 salesYear和salesMonth基于性能salesdate ，那么你可以使用

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 aggregater在这两个项目得到120000 ，其中之一将在结果网格看

看到https://jsfiddle.net/fa40onkz/

如果你能跟着我，直到这个地方，那么你可以了解从任何效果，这你可以在生成的数据透视表见。

例如，这是错误的使用

{/*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 }

在jqPivot的选项，因为2 ["toyota", "corolla"]源项具有不同 fuelusagecity ， fuelusagehwy和highsalestext值。 该salesaboveavg值是一样的，但我想的使用salesaboveavg是同样的错误。 您应该删除的项目xDimension 。

若要显示在单元格空单元0值，你可以定义列模板

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"
}]

你会得到的结果类似

像演示https://jsfiddle.net/tnr2dgkv/

你的最后一个问题是有关排序。 源项目的排序是非常重要的是创造正确的数据透视表。 您可以使用skipSortByX: true或/和skipSortByY: true jqPivot的选项，以抑制初始数据的排序，但如果你使用分组所产生的数据将被整理一次（ groupField: ["x0"] 我认为，最简单的方法（但它仍然不是在执行那么简单）来保存结果条目是通过定义自定义排序功能的顺序xDimension项目。 我建议你阅读维基文章以获取更多信息。



文章来源: How to categorize rows in the free grid jqgrid?
标签： jqgrid free-jqgrid jqpivot
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~