Tagged: PivotGrid
Hello:
I am trying to add some search capabilities to a pivot grid much like the demo you have for jqGrid. Is this even possible?
Ken
Hello Ken,
Yes this is possible.
You need to know that after the pivot grid is build, it stay in local mode and no request to server is possible.
In order to achieve this it is needed to build the filters string manually and pass it to postData which should do the job.
In the next days we will post a example how to do this. We think it will be useful to have such method.
Kind Regards,
Will
Guriddo Support Team
I am looking forward to seeing the example!
Ken
Hello Ken,
Please check this demo example
If you have questions or something is not clear, please let us know.
Kind Regards,
Will
Guriddo Support Team
Hi Will:
I guess I should have been more specific – and perhaps I posted in the wrong forum too.
I have a PHP pivotGrid that I want to add filtering to, if that’s possible.
Here is my code:
index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
<?php include "sqlPDO.php"; $db = opendb('MySQL','tinicum'); $sql = " select LpcID, concat(LPC,' - ',LpcDescription) as LPC from tblLpcType order by LpcID"; $LPChtml = array(); foreach ($db->query($sql) as $row) { $LPChtml[strval($row['LpcID'])] = $row['LPC']; } $sql = " select distinct year(ContactDate) year from tblContactNotes where year(ContactDate) > 0 order by 1"; $Yearhtml = array(); foreach ($db->query($sql) as $row) { $Yearhtml[strval($row['year'])] = $row['year']; } $sql = " select distinct month(ContactDate) monthnum, date_format(ContactDate,'%M') monthname from tblContactNotes where month(ContactDate) > 0 order by 1"; $Monthhtml = array(); foreach ($db->query($sql) as $row) { $Monthhtml[strval($row['monthnum'])] = $row['monthname']; } ?> <!DOCTYPE html> <html> <head> <title>Contact Notes</title> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/jquery-ui.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/trirand/ui.jqgrid.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/ui.multiselect.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/css/mtabs.css" /> <style type="text"> html, body { margin: 0; /* Remove body margin/padding */ padding: 0; overflow: hidden; /* Remove scroll bars on browser window */ font-size: 100%; } .ui-jqgrid { font-size: 14px; } .ui-jqgrid .ui-jqgrid-btable tbody tr.jqgrow td { white-space: normal !important; height: auto; } .mylongdata { height: 190px; overflow-y: auto; } </style> <script src="/jqSuite/js/jquery.min.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <!-- Files for the export ----------------------> <script type="text/javascript" language="javascript" src="/js/pivotExport/pdfmake.min.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/vfs_fonts.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/jszip.min.js"></script> <script type="text/javascript"> $.jgrid.no_legacy_api = true; $.jgrid.useJSON = true; $.jgrid.defaults.width = "700"; </script> <script src="/jqSuite/js/jquery-ui.min.js" type="text/javascript"></script> <script> var LPChtml = <?php print(json_encode($LPChtml)); ?>; var Yearhtml = <?php print(json_encode($Yearhtml)); ?>; var Monthhtml = <?php print(json_encode($Monthhtml)); ?>; $(document).ready(function(){ $.each(LPChtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#LPC').append($("<option></option>").attr("value",val).text(text)); }); $.each(Yearhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#year').append($("<option></option>").attr("value",val).text(text)); }); $.each(Monthhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#month').append($("<option></option>").attr("value",val).text(text)); }); }); </script> </head> <body> |
<?php include(“leaderboard.php”); ?> </body> </html>
leaderboard.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
<?php @session_start(); require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php'; require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/php/PHPSuito/jqPivotGrid.php'; require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $db = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $db->query("SET NAMES utf8"); $pivot = new jqPivotGrid($db); $pivot->SelectCommand = " SELECT concat(lm.LastName,', ',lm.FirstName) as ContactedBy, lo.LandOwner, cn.ContactDate, case cn.ContactMode when 1 then 'Phone' when 2 then 'Other' when 3 then 'eMail' when 4 then 'Face to Face' when 5 then 'Mail' when 6 then 'Background' when 7 then 'no contact info' when 8 then 'N/A' end as ContactMode, count(*) as total FROM tblContactNotes cn, tblLpcMembers lm, tblLandOwners lo where cn.ContactedBy = lm.LpcMemberID and cn.LandOwnerID = lo.LandOwnerID GROUP BY cn.ContactedBy, cn.LandOwnerID, cn.ContactDate, cn.ContactMode"; $pivot->setData('leaderboard.php'); $pivot->setGridOptions(array( "rowNum" => 1000, "width" => "auto", "height" => "auto", "sortname" => "ContactDate", "sortorder" => "desc", "rowList" => array(20,40,60,80,1000), "altRows" => true, "caption" => "Leader Board", "groupingView" => array("groupCollapse" => true) )); $pivot->setxDimension(array( array( "dataName" => "ContactedBy", "width" => 240), array( "dataName" => "LandOwner", "width" => 115), array( "dataName" => "ContactDate", "width" => 115) )); $pivot->setyDimension(array( array( "dataName" => "ContactMode") )); $pivot->setaggregates(array( array( "member" => "total", "aggregator" => "sum", "width" => 80, "label" => "Total", "formatter" => "integer", "align" => "right", "summaryType" => "sum") )); $pivot->setPivotOptions(array( "rowTotals" => true, "colTotals" => true )); $pivot->navigator = true; $pivot->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"view"=>false,"search"=>false,"reload"=>false)); $buttonoptions = array( "#pager12",array( "caption"=>"Csv", "title"=>"Local Export to CSV", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToCsv'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,1000); $buttonoptions = array( "#pager12",array( "caption"=>"Excel", "title"=>"Local Export to Escel", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToExcel'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $buttonoptions = array( "#pager12", array( "caption"=>"Pdf", "title"=>"Local Export to PDF", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToPdf'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $pivot->renderPivot("#grid12","#pager12", true, null, true, true); ?> |
Hello,
As far as I understand you missed to bind a click to search button and use the function which we provide in our demo.
Include our function from the example in your index.php and bind a click as we do in our code. You will need just to configure the input data to meet your requirements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
/** * Build custom search string from array of objects * @param {Array} rule_arr the array where the data and rules are * @param {String} group group logical operator of the fields * @returns {String} */ function buildCustomSearch( rule_arr, group ){ if(group === undefined) { group = "AND"; } var ruleGroup = ""; if(Array.isArray(rule_arr) && rule_arr.length) { ruleGroup = "{\"groupOp\":\"" + group + "\",\"rules\":["; var gi=0; $.each(rule_arr,function(i,n){ if (gi > 0) {ruleGroup += ",";} ruleGroup += "{\"field\":\"" + n.name + "\","; ruleGroup += "\"op\":\"" + n.oper + "\","; ruleGroup += "\"data\":\"" + n.val.replace(/\\/g,'\\\\').replace(/\"/g,'\\"') + "\"}"; gi++; }); ruleGroup += "]}"; } return ruleGroup; } var grid = $("#jqGrid"); // rplace with your id $("#do_search").on('click',function(){ var my_fld=[]; /* *opts : ['eq','ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni','bw', 'bn', 'ew', 'en', 'cn', 'nc'], */ /* my_fld.push({ name: "OrderDate", val : $("#from_date").val(), oper:"ge" }); my_fld.push({ name: "OrderDate", val : $("#to_date").val(), oper:"le" }); */ // configure my_fld in a way above to meet your needs var rule = buildCustomSearch( my_fld, "AND"); grid.setGridParam({postData:{filters:rule}, search:true}).trigger("reloadGrid"); }); |
Regards,
Will
Guriddo Support Team
Hi Will:
I must be missing something here. The example you provided is not using PHP, nor is it a pivot table. It looks to me like a straight up javascript only clone of the example on your website for custom search with a PHP grid.
I understand about adding a click event in the index.php file, but I don’t see how that ever would change anything in the leaderboard.php file since it’s not referenced.
Ken
Hello Ken,
I think you missed my first post that when once created the pivot grid does not make any request to the php file.
I will explain it in detail.
In your case, when you include for the first time the file leaderboard.php in the index file it creates the pivot definitions and the grid is constructed without data. After it is created it send request ONLY ONCE to get all the needed data.
When data comes to grid it is transformed and there are NO MORE REQUESTS to leaderboard.php file. The grid is now in local mode. This can be easy seen if you try to sort, do a paging and etc – no requests to the server.
Because of this in order to do search, local build should be done like in our demo. No PHP code – only JavaScript code.
Kind Regards,
Will
Guriddo Support Team
I did see that in your original reply.
I did some testing and it turns out that all of the things I want to filter on aren’t actually in the pivot data – when I initially wrote this I didn’t realize that the initial data was a one-shot thing.
Thanks for the help – I need to go back to the drawing board and come up with a different method to accomplish what I have in mind.
Ken
Based on your demo and comments I modified my scripts and incorporated the function in your demo. That is almost working 100% – I just have one part that doesn’t seem to be working.
I added a ‘between’ search for the ContactDate comlum and it doesn’t seem to be working.
Here is my updated code. Any suggestions are appreciated.
index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
<?php include "sqlPDO.php"; $db = opendb('MySQL','tinicum'); $sql = " select LpcDescription from tblLpcType order by LpcID"; $LPChtml = array(); foreach ($db->query($sql) as $row) { $LPChtml[strval($row['LpcDescription'])] = $row['LpcDescription']; } $sql = " select distinct year(ContactDate) year from tblContactNotes where year(ContactDate) > 0 order by 1"; $Yearhtml = array(); foreach ($db->query($sql) as $row) { $Yearhtml[strval($row['year'])] = $row['year']; } $sql = " select distinct month(ContactDate) monthnum, date_format(ContactDate,'%M') monthname from tblContactNotes where month(ContactDate) > 0 order by 1"; $Monthhtml = array(); foreach ($db->query($sql) as $row) { $Monthhtml[strval($row['monthnum'])] = $row['monthname']; } ?> <!DOCTYPE html> <html> <head> <title>Contact Notes</title> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/jquery-ui.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/trirand/ui.jqgrid.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/ui.multiselect.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/css/mtabs.css" /> <style type="text"> html, body { margin: 0; /* Remove body margin/padding */ padding: 0; overflow: hidden; /* Remove scroll bars on browser window */ font-size: 100%; } .ui-jqgrid { font-size: 14px; } .ui-jqgrid .ui-jqgrid-btable tbody tr.jqgrow td { white-space: normal !important; height: auto; } .mylongdata { height: 190px; overflow-y: auto; } </style> <script src="/jqSuite/js/jquery.min.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <!-- Files for the export ----------------------> <script type="text/javascript" language="javascript" src="/js/pivotExport/pdfmake.min.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/vfs_fonts.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/jszip.min.js"></script> <script type="text/javascript"> $.jgrid.no_legacy_api = true; $.jgrid.useJSON = true; $.jgrid.defaults.width = "700"; </script> <script src="/jqSuite/js/jquery-ui.min.js" type="text/javascript"></script> <script> var LPChtml = <?php print(json_encode($LPChtml)); ?>; var Yearhtml = <?php print(json_encode($Yearhtml)); ?>; var Monthhtml = <?php print(json_encode($Monthhtml)); ?>; var postData = ''; $(document).ready(function(){ $.each(LPChtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#LPC').append($("<option></option>").attr("value",val).text(text)); }); $.each(Yearhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#year').append($("<option></option>").attr("value",val).text(text)); }); $.each(Monthhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#month').append($("<option></option>").attr("value",val).text(text)); }); /***************************************************************************************************************************************/ function buildCustomSearch( rule_arr, group ){ if(group === undefined) { group = "AND"; } var ruleGroup = ""; if(Array.isArray(rule_arr) && rule_arr.length) { ruleGroup = "{\"groupOp\":\"" + group + "\",\"rules\":["; var gi=0; $.each(rule_arr,function(i,n){ if (gi > 0) {ruleGroup += ",";} ruleGroup += "{\"field\":\"" + n.name + "\","; ruleGroup += "\"op\":\"" + n.oper + "\","; ruleGroup += "\"data\":\"" + n.val.replace(/\\/g,'\\\\').replace(/\"/g,'\\"') + "\"}"; gi++; }); ruleGroup += "]}"; console.log(ruleGroup); } return ruleGroup; } var grid = $("#grid12"); $("#do_search").on('click',function(){ // console.log("Search was clicked - val is "+$("#year").val()); if ($('#Year') != '%') { ys = $('#year').val()+"-01-01"; ye = $('#year').val()+"-12-31"; } // console.log(ys+"..."+ye); var my_fld=[]; /* *opts : ['eq','ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni','bw', 'bn', 'ew', 'en', 'cn', 'nc'], */ my_fld.push({ name: "LpcDescription", val : $("#LPC").val(), oper:"eq" }); my_fld.push({ name: "ContactDate", val: ys+"..."+ye, oper: "bt" }); // console.log(my_fld); var rule = buildCustomSearch( my_fld, "AND"); grid.setGridParam({postData:{filters:rule}, search:true}).trigger("reloadGrid"); }); /***************************************************************************************************************************************/ }); </script> </head> <body> <div> <form action="#"> <label for="LPC">LPC:</label> <select id="LPC" name="LPC"> <option value='%'>All</option> </select> <label for="year">Year:</label> <select id="year" name="year"> <option value='%'>All</option> </select> <label for="year">Month:</label> <select id="month" name="month"> <option value='%'>All</option> </select> <input id="do_search" type="button" value="Search"></input> </form> <br /> </div> <?php include("leaderboard.php"); ?> </body> </html> |
leaderboard.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
<?php @session_start(); require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php'; require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/php/PHPSuito/jqPivotGrid.php'; require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $db = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $db->query("SET NAMES utf8"); $pivot = new jqPivotGrid($db); $pivot->SelectCommand = " SELECT concat(lm.LastName,', ',lm.FirstName) as ContactedBy, lt.LpcDescription, lo.LandOwner, cn.ContactDate, case cn.ContactMode when 1 then 'Phone' when 2 then 'Other' when 3 then 'eMail' when 4 then 'Face to Face' when 5 then 'Mail' when 6 then 'Background' when 7 then 'no contact info' when 8 then 'N/A' end as ContactMode, count(*) as total FROM tblContactNotes cn, tblLpcMembers lm, tblLandOwners lo, tblLpcType lt where cn.ContactedBy = lm.LpcMemberID and cn.LandOwnerID = lo.LandOwnerID and lm.LPC = lt.LpcID GROUP BY cn.ContactedBy, cn.LandOwnerID, cn.ContactDate, cn.ContactMode"; $pivot->setData('leaderboard.php'); $pivot->setGridOptions(array( "rowNum" => 1000, "width" => "auto", "height" => "auto", "sortname" => "ContactDate", "sortorder" => "desc", "rowList" => array(20,40,60,80,1000), "altRows" => true, "caption" => "Leader Board", "groupingView" => array("groupCollapse" => true) )); $pivot->setxDimension(array( array( "dataName" => "LpcDescription", "width" => 240), array( "dataName" => "ContactedBy", "width" => 80), array( "dataName" => "LandOwner", "width" => 115), array( "dataName" => "ContactDate", "width" => 115) )); $pivot->setyDimension(array( array( "dataName" => "ContactMode") )); $pivot->setaggregates(array( array( "member" => "total", "aggregator" => "sum", "width" => 80, "label" => "Total", "formatter" => "integer", "align" => "right", "summaryType" => "sum") )); $pivot->setPivotOptions(array( "rowTotals" => true, "colTotals" => true )); $pivot->navigator = true; $pivot->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"view"=>false,"search"=>true,"reload"=>false)); $buttonoptions = array( "#pager12",array( "caption"=>"Csv", "title"=>"Local Export to CSV", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToCsv'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,1000); $buttonoptions = array( "#pager12",array( "caption"=>"Excel", "title"=>"Local Export to Escel", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToExcel'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $buttonoptions = array( "#pager12", array( "caption"=>"Pdf", "title"=>"Local Export to PDF", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToPdf'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $pivot->renderPivot("#grid12","#pager12", true, null, true, true); ?> |
Oh, I see – The ContactDate field in the pivot table is just text, so that’s why the ‘between’ oper didn’t work. I changed the oper to ‘bw’ (begins with) and pass the year from the drop-down and it works perfectly.
All done! Thanks again for your help.
Ken
Hello Ken,
Thank you for the feedback and glad to hear that it works as expected
Kind Regards,
Will
Guriddo Support Team
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top