php - 在同一网格上搜索多个日期选择器

Question

我在同一网格上使用多个日期选择器，但我面临着获得正确结果的问题。

我在 1 个网格中使用了 3 个日期选择器。

只有第一个日期选择器(订单日期)能够输出正确的结果，而其他 2 个日期选择器(开始日期和结束日期)无法生成正确的结果。

查询没有问题，你能看看这里发生了什么吗？

提前致谢!

PHP 包装

<?php
ob_start();
require_once 'config.php';
// include the jqGrid Class
require_once "php/jqGrid.php";
// include the PDO driver class
require_once "php/jqGridPdo.php";
// include the datepicker
require_once "php/jqCalendar.php"; 
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new jqGridRender($conn);
// Write the SQL Query
$grid->SelectCommand = "SELECT c.CompanyID, c.CompanyCode, c.CompanyName, c.Area, o.OrderCode, o.Date, m.maID ,m.System, m.Status, m.StartDate, m.EndDate, m.Type  FROM company c, orders o, maintenance_agreement m WHERE c.CompanyID = o.CompanyID AND o.OrderID = m.OrderID ";  
// Set the table to where you update the data
$grid->table = 'maintenance_agreement'; 
// set the ouput format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setPrimaryKeyId('maID');
// Let the grid create the model
$grid->setColModel();
// Set the url from where we obtain the data
$grid->setUrl('grouping_ma_details.php');
// Set grid caption using the option caption
$grid->setGridOptions(array(
    "sortable"=>true,
    "rownumbers"=>true, 
    "caption"=>"Group by Maintenance Agreement",
    "rowNum"=>20,
    "height"=>'auto', 
    "width"=>1300,
    "sortname"=>"maID",
    "hoverrows"=>true,
    "rowList"=>array(10,20,50),     
    "footerrow"=>false,
    "userDataOnFooter"=>false,
    "grouping"=>true,
    "groupingView"=>array(
        "groupField" => array('CompanyName'),
        "groupColumnShow" => array(true), //show or hide area column
        "groupText" =>array('<b> Company Name: {0}</b>',),
        "groupDataSorted" => true,
        "groupSummary" => array(true)
    ) 
));

//Start Date
$grid->setColProperty("StartDate", array("label"=>"Start Date","width"=>120,"align"=>"center","fixed"=>true,
    "formatter"=>"date",
    "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"d M Y")
    ));
$grid->setUserTime("d M Y");
$grid->setUserDate("d M Y");
$grid->setDatepicker("StartDate",array("buttonOnly"=>false));
$grid->datearray = array('StartDate');

//End Date
$grid->setColProperty("EndDate", array("label"=>"End Date","width"=>120,"align"=>"center","fixed"=>true,
    "formatter"=>"date",
    "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"d M Y")
    ));
$grid->setUserTime("d M Y");
$grid->setUserDate("d M Y");
$grid->setDatepicker("EndDate",array("buttonOnly"=>false));
$grid->datearray = array('EndDate');

//Order Date
$grid->setColProperty("Date", array("label"=>"Order Date","width"=>100,"editable"=>false,"align"=>"center","fixed"=>true,
    "formatter"=>"date",
    "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"d M Y")
    ));
$grid->setUserTime("d M Y");
$grid->setUserDate("d M Y");
$grid->setDatepicker("Date",array("buttonOnly"=>false));
$grid->datearray = array('Date');



// Enable toolbar searching
$grid->toolbarfilter = true;
$grid->setFilterOptions(array("stringResult"=>true,"searchOnEnter"=>false,"defaultSearch"=>"cn")); 
// Enable navigator
$grid->navigator = true;


$grid->setNavOptions('navigator', array("pdf"=>true, "excel"=>true,"add"=>false,"edit"=>true,"del"=>false,"view"=>true, "search"=>true));

$grid->renderGrid('#grid','#pager',true, null, null, true,true);
$conn = null;
?>  

JavaScript代码

    jQuery(document).ready(function ($) {
        jQuery('#grid').jqGrid({
            "width": 1300,
            "hoverrows": true,
            "viewrecords": true,
            "jsonReader": {
                "repeatitems": false,
                "subgrid": {
                    "repeatitems": false
                }
            },
            "xmlReader": {
                "repeatitems": false,
                "subgrid": {
                    "repeatitems": false
                }
            },
            "gridview": true,
            "url": "grouping_ma_details.php",
            "editurl": "grouping_ma_details.php",
            "cellurl": "grouping_ma_details.php",
            "sortable": true,
            "rownumbers": true,
            "caption": "Group by Maintenance Agreement",
            "rowNum": 20,
            "height": "auto",
            "sortname": "maID",
            "rowList": [10, 20, 50],
            "footerrow": false,
            "userDataOnFooter": false,
            "grouping": true,
            "groupingView": {
                "groupField": ["CompanyName"],
                "groupColumnShow": [true],
                "groupText": ["<b> Company Name: {0}</b>"],
                "groupDataSorted": true,
                "groupSummary": [true]
            },
            "datatype": "json",
            "colModel": [{
                "name": "CompanyID",
                "index": "CompanyID",
                "sorttype": "int",
                "editable": true
            }, {
                "name": "CompanyCode",
                "index": "CompanyCode",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "CompanyName",
                "index": "CompanyName",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "Area",
                "index": "Area",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "OrderCode",
                "index": "OrderCode",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "Date",
                "index": "Date",
                "sorttype": "date",
                "label": "Order Date",
                "width": 100,
                "editable": false,
                "align": "center",
                "fixed": true,
                "formatter": "date",
                "formatoptions": {
                    "srcformat": "Y-m-d H:i:s",
                    "newformat": "d M Y"
                },
                "editoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                },
                "searchoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                }
            }, {
                "name": "maID",
                "index": "maID",
                "sorttype": "int",
                "key": true,
                "editable": true
            }, {
                "name": "System",
                "index": "System",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "Status",
                "index": "Status",
                "sorttype": "string",
                "editable": true
            }, {
                "name": "StartDate",
                "index": "StartDate",
                "sorttype": "date",
                "label": "Start Date",
                "width": 120,
                "align": "center",
                "fixed": true,
                "formatter": "date",
                "formatoptions": {
                    "srcformat": "Y-m-d H:i:s",
                    "newformat": "d M Y"
                },
                "editoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                },
                "searchoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                },
                "editable": true
            }, {
                "name": "EndDate",
                "index": "EndDate",
                "sorttype": "date",
                "label": "End Date",
                "width": 120,
                "align": "center",
                "fixed": true,
                "formatter": "date",
                "formatoptions": {
                    "srcformat": "Y-m-d H:i:s",
                    "newformat": "d M Y"
                },
                "editoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                },
                "searchoptions": {
                    "dataInit": function (el) {
                        setTimeout(function () {
                            if (jQuery.ui) {
                                if (jQuery.ui.datepicker) {
                                    jQuery(el).datepicker({
                                        "disabled": false,
                                        "dateFormat": "dd M yy"
                                    });
                                    jQuery('.ui-datepicker').css({
                                        'font-size': '75%'
                                    });
                                }
                            }
                        }, 100);
                    }
                },
                "editable": true
            }, {
                "name": "Type",
                "index": "Type",
                "sorttype": "string",
                "editable": true
            }],
            "postData": {
                "oper": "grid"
            },
            "prmNames": {
                "page": "page",
                "rows": "rows",
                "sort": "sidx",
                "order": "sord",
                "search": "_search",
                "nd": "nd",
                "id": "maID",
                "filter": "filters",
                "searchField": "searchField",
                "searchOper": "searchOper",
                "searchString": "searchString",
                "oper": "oper",
                "query": "grid",
                "addoper": "add",
                "editoper": "edit",
                "deloper": "del",
                "excel": "excel",
                "subgrid": "subgrid",
                "totalrows": "totalrows",
                "autocomplete": "autocmpl"
            },
            "loadError": function (xhr, status, err) {
                try {
                    jQuery.jgrid.info_dialog(jQuery.jgrid.errors.errcap, '<div class="ui-state-error">' + xhr.responseText + '</div>', jQuery.jgrid.edit.bClose, {
                        buttonalign: 'right'
                    });
                } catch (e) {
                    alert(xhr.responseText);
                }
            },
            "pager": "#pager"
        });
        jQuery('#grid').jqGrid('navGrid', '#pager', {
            "edit": true,
            "add": false,
            "del": false,
            "search": true,
            "refresh": true,
            "view": true,
            "excel": true,
            "pdf": true,
            "csv": false,
            "columns": false
        }, {
            "drag": true,
            "resize": true,
            "closeOnEscape": true,
            "dataheight": 150,
            "errorTextFormat": function (r) {
                return r.responseText;
            }
        }, {
            "drag": true,
            "resize": true,
            "closeOnEscape": true,
            "dataheight": 150,
            "errorTextFormat": function (r) {
                return r.responseText;
            }
        }, {
            "errorTextFormat": function (r) {
                return r.responseText;
            }
        }, {
            "drag": true,
            "closeAfterSearch": true,
            "multipleSearch": true
        }, {
            "drag": true,
            "resize": true,
            "closeOnEscape": true,
            "dataheight": 150
        });
        jQuery('#grid').jqGrid('navButtonAdd', '#pager', {
            id: 'pager_excel',
            caption: '',
            title: 'Export To Excel',
            onClickButton: function (e) {
                try {
                    jQuery("#grid").jqGrid('excelExport', {
                        tag: 'excel',
                        url: 'grouping_ma_details.php'
                    });
                } catch (e) {
                    window.location = 'grouping_ma_details.php?oper=excel';
                }
            },
            buttonicon: 'ui-icon-newwin'
        });
        jQuery('#grid').jqGrid('navButtonAdd', '#pager', {
            id: 'pager_pdf',
            caption: '',
            title: 'Export To Pdf',
            onClickButton: function (e) {
                try {
                    jQuery("#grid").jqGrid('excelExport', {
                        tag: 'pdf',
                        url: 'grouping_ma_details.php'
                    });
                } catch (e) {
                    window.location = 'grouping_ma_details.php?oper=pdf';
                }
            },
            buttonicon: 'ui-icon-print'
        });
        jQuery('#grid').jqGrid('filterToolbar', {
            "stringResult": true,
            "searchOnEnter": false,
            "defaultSearch": "cn"
        });
    });

Answer 1

如果使用搜索工具栏(filterToolbar)，则只能指定搜索期间使用的一项操作。您使用过

$('#grid').jqGrid('filterToolbar',
    { stringResult: true, searchOnEnter: false, defaultSearch: "cn" });

因此，在过滤期间未指定 searchoptions 的 sopt 的情况下，操作“Contains”(“cn”)将应用于所有列。对于具有 stype: "select" 的所有列，包含 searchoptions 的 sopt 非常重要。

如果您不使用搜索对话框，则可以在 searchoptions 中包含 sopt: ["eq"]，以获取所有具有 stype: "select"的列 和格式化程序:“日期”。如果除了搜索工具栏之外还使用搜索对话框，您应该在 sopt 中使用一些数组，其中 "eq" 是数组的第一个元素。在这种情况下，在网格过滤期间将使用“等于”操作。

因为您使用高级搜索对话框(使用multipleSearch: true)，您可以非常轻松地验证搜索工具栏生成的过滤器。您只需设置任何过滤器(或多个过滤器)，然后打开搜索对话框。如果您之前没有打开搜索对话框，您将看到搜索过滤器生成的过滤器。我建议您将 recreateForm: true 选项与 multipleSearch: true 一起使用(或者可能与 multipleGroup: true 一起使用)。在这种情况下，您将始终在搜索对话框中看到当前使用的过滤器，而不是最后一个搜索对话框(它将被隐藏而不是销毁)。