Guriddo » Topic: Custom Search Form for Pivot Grid

Custom Search Form for Pivot Grid

Guriddo
PivotGrid
Custom Search Form for Pivot Grid

Tagged: PivotGrid

This topic has 11 replies, 2 voices, and was last updated 2 months, 1 week ago by guriddo.support.

Creator

Topic
March 12, 2023 at 8:15 pm #128119

kgrogers
Participant

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
Creator

Topic

Viewing 11 replies - 1 through 11 (of 11 total)

Author

Replies

March 13, 2023 at 3:46 pm #128120

guriddo.support

Participant

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

March 13, 2023 at 7:29 pm #128121

kgrogers

Participant

I am looking forward to seeing the example!

Ken

March 15, 2023 at 12:05 pm #128122

guriddo.support

Participant

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

March 15, 2023 at 2:27 pm #128123

kgrogers

Participant

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

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

<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=”search” type=”button” value=”Search”></input> </form> <br />

<?php include(“leaderboard.php”); ?> </body> </html>

leaderboard.php

<?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);
?>

This reply was modified 2 months, 2 weeks ago by kgrogers.

March 15, 2023 at 4:19 pm #128125

guriddo.support

Participant

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.

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

March 15, 2023 at 5:49 pm #128126

kgrogers

Participant

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

March 15, 2023 at 6:29 pm #128127

guriddo.support

Participant

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

March 15, 2023 at 8:26 pm #128128

kgrogers

Participant

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

March 18, 2023 at 6:30 pm #128129

kgrogers

Participant

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

<?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>
        
            <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 />
        
        <?php include("leaderboard.php"); ?>
    </body>
</html>

leaderboard.php

<?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);
?>

March 19, 2023 at 2:37 am #128130

kgrogers

Participant

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

This reply was modified 2 months, 1 week ago by kgrogers. Reason: figured out the problem with using the search operator 'bt'

March 20, 2023 at 8:16 pm #128132

guriddo.support

Participant

Hello Ken,

Thank you for the feedback and glad to hear that it works as expected

Kind Regards,

Will

Guriddo Support Team

Author

Replies

Viewing 11 replies - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.

Stay connected with us in your favorite flavor!

Custom Search Form for Pivot Grid

Search Forum

Recent Replies

Forum Statistics

UserOnline