2

I'm using multiple datepicker on same grid and I face the problem to get a proper result.

I used 3 datepicker in 1 grid.

Only the first datepicker (Order Date)is able to output proper result while the other 2 datepicker (Start Date & End Date) are not able to generate proper result.

There is no problem with the query, so could you find out what's going on here?

Thanks in advance!

php wrapper

<?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 code

    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"
        });
    });
DHF
  • 35
  • 1
  • 7
  • 1
    the PHP code which you posted generate some HTML page with JavaScript. [jqGrid](http://stackoverflow.com/tags/jqgrid/info) is JavaScript product. So you should post the JavaScript code here. You should consider to use another tag of the question like [jqgrid-php](http://stackoverflow.com/questions/tagged/jqgrid-php) if you use the wrapper. – Oleg Dec 05 '12 at 09:46
  • sorry I'm not aware of that. Can you help me look into this issue? I've included javascript code as well. – DHF Dec 05 '12 at 10:12
  • Please don't post the code without formatting so that human (not full crazy) could read it. You write "Only the first datepicker (Order Date) is able to output proper result while the other 2 datepicker (Start Date & End Date) are not able to generate proper result". You should describe the problem more clear. What you mean under "is able to output proper result" and "are not able to generate proper result"? Are datepicker will be displayed? Could you select the date? What is the problem which you have? – Oleg Dec 05 '12 at 11:06
  • Sorry I thought full coding will be helpful to find out the error, ok I will format it nicely. There is no problem with the first datepicker (Order Date). FYI, I use searching tools at the navigation bar. When I tried to search for a result for the other 2 datepicker, it won't produce the correct result. If my data have '5 Dec' and '6 Dec', when I search the date smaller than '7 Dec', there is no matching results produced, but it works totally fine for first datepicker. – DHF Dec 05 '12 at 14:48
  • I made the most part of reformatting of the code for you. Do you really search for the part of the date like `5 Dec` instead of `5 Dec 2012`? – Oleg Dec 05 '12 at 15:29
  • Sorry, I search `5 Dec 2012`. I use datepicker to search, so it will generate full string of date format. – DHF Dec 06 '12 at 02:19

1 Answers1

1

If one uses searching toolbar (filterToolbar) it can be specified only one operation used during searching. You used

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

So the operation "Contains" ("cn") will be apply on all columns during filtering where sopt of searchoptions is not specified. It's extremely important to include sopt of searchoptions for all columns having stype: "select".

If you don't use Searching Dialog then you can include sopt: ["eq"] in searchoptions for all columns having stype: "select" and formatter: "date". If you use Searching Dialog additionally to Searching Toolbar you should in sopt with some array where "eq" is the first element of the array. In the case the operation "Equal" will be used during filtering of the grid.

Because you use Advanced Searching Dialog (with multipleSearch: true) you can verify the filter generated by the Searching Toolbar very easy. You need just set any filter (or filters) and then open Searching Dialog after that. If you don't opened the searching dialog before you will see the filter generated by searching filter. I recommend you to use recreateForm: true option together with multipleSearch: true (or probably with multipleGroup: true). In the case you will always see the current used filter in the searching dialog instead of the last searching dialog (it will be hide instead of destroying).

Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Hmm, actually I just wanted to use searching dialog to filter the date instead of searching toolbar. I don't want to fix a searchoptions because I want to be flexible, to be able to use all options of `sopt`. I find out the problem is somewhat related to the position of the code. I put setColProperty in the order of 'End Date' -> 'Start Date' -> 'Order Date', the last setColProperty will be effective, if i rearrange 'Order Date' to 1st position, then 'End Date' will able to generate proper result. This is the [link](http://smartouch-cdms.com/datebug.php) here. It will be clearer. – DHF Dec 06 '12 at 01:37
  • 1
    @DHF: I could not see any difference between any from columns having dates. One problem is that you don't set `sopt: ["eq"]` for columns with dates so `"op":"cn"` will be used. **Your server code** can't filter for example `{"groupOp":"AND","rules":[{"field":"System","op":"eq","data":"TMS"},{"field":"StartDate","op":"cn","data":"01 Oct 2012"}]}`. It seems to me that **the server part of your code** apply incorrect such filter. Additionally you can improve usage of datepicker in the searching toolbar. See [the answer](http://stackoverflow.com/a/6876951/315935) for details. – Oleg Dec 06 '12 at 07:18