Guriddo » Topic: jqPivot

jqPivot

Guriddo
pivot
jqPivot

Tagged: pivot

This topic has 10 replies, 2 voices, and was last updated 7 years, 10 months ago by guriddo.support.

Creator

Topic

June 13, 2018 at 6:11 pm #126848

sagana

Participant

My first attempt at a pivot grid which I need as a grid called from another grid. The first grid has

// on select row we should post the member id to second table and trigger it to reload the data
$selecttype = &lt;&lt;&lt;CAND
function(rowid, selected)
{
    if(rowid != null) {
        jQuery(&quot;#ProjectCandidateList&quot;).jqGrid(&#039;setGridParam&#039;,{postData:{nproj:rowid}});
        jQuery(&quot;#ProjectCandidateList&quot;).trigger(&quot;reloadGrid&quot;);                 
    }
}
CAND;
$grid-&gt;setGridEvent(&#039;onSelectRow&#039;, $selecttype);

// We should clear the grid data on second grid on sorting, paging, etc.
$cleargrid = &lt;&lt;&lt;CLEAR
function(rowid, selected)
{
   // clear the grid data and footer data
   jQuery(&quot;#ProjectCandidateList&quot;).jqGrid(&#039;clearGridData&#039;,true);
}
CLEAR;

$grid-&gt;setGridEvent(&#039;onPaging&#039;, $cleargrid);
$grid-&gt;setGridEvent(&#039;onSortCol&#039;, $cleargrid);

// on select row we should post the member id to second table and trigger it to reload the data

$selecttype = <<<CAND

function(rowid, selected)

{

if(rowid != null) {

jQuery("#ProjectCandidateList").jqGrid('setGridParam',{postData:{nproj:rowid}});

jQuery("#ProjectCandidateList").trigger("reloadGrid");

}

CAND;

$grid->setGridEvent('onSelectRow', $selecttype);

// We should clear the grid data on second grid on sorting, paging, etc.

$cleargrid = <<<CLEAR

function(rowid, selected)

{

// clear the grid data and footer data

jQuery("#ProjectCandidateList").jqGrid('clearGridData',true);

}

CLEAR;

$grid->setGridEvent('onPaging', $cleargrid);

$grid->setGridEvent('onSortCol', $cleargrid);

and the called ProjectCandidateList code is as follows. Something is going wrong while forming the grid, It is doing a first pass in which id is 0 but never does the second pass with the id passed from the first grid. Is there something wrong with the creation of the second grid which is the pivot grid? I use my own pdo object $g_pdoHandle to get the data in many grids without a problem.

&lt;?php

// Our grids ID
$sGridID=&quot;ProjectCandidateList&quot;;
$sGridObjectID=&quot;#{$sGridID}&quot;;

if(isset($_REQUEST[&#039;nproj&#039;]))
{
    $id = jqGridUtils::Strip($_REQUEST[&quot;nproj&quot;]);
}
else
{
    $id = 0;
} 

// Create the jqGrid instance
$pivot = new jqPivotGrid($g_pdoHandle);

// the actual query for the grid data
$sSQL = &quot;SELECT
  projectcandidate.ProjectCandidateID,
  concat(candidate.Forename,&#039; &#039;,candidate.Surname) as CandidateName,
  test.Name as TestName,
  testtaken.HeadlineScoreCalcPC,
  testtaken.ScoreCalcSpeed,
  projectcandidate.OrganisationProjectID
FROM
  projectcandidate
  LEFT JOIN candidate ON projectcandidate.CandidateID = candidate.CandidateID
  LEFT JOIN testassignment ON projectcandidate.CandidateID = testassignment.CandidateID
  LEFT JOIN test ON testassignment.TestID = test.TestID
  LEFT JOIN testtaken ON testassignment.TestAssignmentID = testtaken.TestAssignmentID
WHERE
  projectcandidate.OrganisationProjectID = {$id}&quot;;
  
  send_to_log($sSQL);
  
  
$pivot-&gt;SelectCommand = $sSQL;  
  

// Set the url from where we obtain the data
$pivot-&gt;setUrl(current_path(&quot;jqgridGetProjectCandidates&quot;));  

// Set some grid options
$pivot-&gt;setGridOptions(array(
        &quot;rowNum&quot;=&gt;25,
        &quot;rowList&quot;=&gt;array(25,100,200,$sShowAll),
        &quot;height&quot;=&gt;&quot;100%&quot;,
        &quot;altRows&quot;=&gt;true,
        &quot;autowidth&quot;=&gt; true,
        &quot;responsive&quot;=&gt; true,    
        &quot;forceFit&quot;=&gt;true,
        &quot;caption&quot;=&gt;&quot;Project Candidates&quot;,
        &quot;hidegrid&quot;=&gt;false,

        &quot;loadtext&quot;=&gt;&quot;&lt;p&gt;&quot;.translator_get_text(&quot;Loading, please wait...&quot;,&quot;SharedUI&quot;).&quot;&lt;/p&gt;&lt;p&gt;&lt;img src=&#039;&quot;.get_resource_path(&quot;images/processing-32x32.gif&quot;).&quot;&#039;&gt;&lt;/p&gt;&quot;,    
        &quot;recordtext&quot; =&gt; translator_get_text(&quot;Viewing {0} - {1} of {2}&quot;,&quot;SharedUI&quot;),
        &quot;emptyrecords&quot;=&gt; translator_get_text(&quot;No records to view&quot;,&quot;SharedUI&quot;),     
        &quot;savetext&quot;=&gt; translator_get_text(&quot;Saving...&quot;,&quot;SharedUI&quot;),
        &quot;pgtext&quot; =&gt; translator_get_text(&quot;Page {0} of {1}&quot;,&quot;SharedUI&quot;),
        &quot;pgfirst&quot; =&gt; translator_get_text(&quot;First Page&quot;,&quot;SharedUI&quot;),
        &quot;pglast&quot; =&gt; translator_get_text(&quot;Last Page&quot;,&quot;SharedUI&quot;),
        &quot;pgnext&quot; =&gt; translator_get_text(&quot;Next Page&quot;,&quot;SharedUI&quot;),
        &quot;pgprev&quot; =&gt; translator_get_text(&quot;Previous Page&quot;,&quot;SharedUI&quot;),
        &quot;pgrecs&quot; =&gt; translator_get_text(&quot;Records per Page&quot;,&quot;SharedUI&quot;),
        &quot;showhide&quot;=&gt; translator_get_text(&quot;Toggle Expand Collapse Grid&quot;,&quot;SharedUI&quot;)
    )
);

// Grid xDimension settings
$pivot-&gt;setxDimension(array(
    array(&quot;dataName&quot;=&gt;&quot;CandidateName&quot;, &quot;width&quot;=&gt;100)
));

// Grid yDimension settings
$pivot-&gt;setyDimension(array(
    array(&quot;dataName&quot; =&gt; &quot;TestName&quot;)
));

// Members
$pivot-&gt;setaggregates(array(
    array(
        &quot;member&quot;=&gt;&#039;HeadlineScoreCalcPC&#039;,
        &quot;aggregator&quot;=&gt;&#039;avg&#039;,
        &quot;width&quot;=&gt;80,
        &quot;label&quot;=&gt;&#039;Score&#039;,
        &quot;formatter&quot;=&gt;&#039;number&#039;,
        &quot;align&quot;=&gt;&#039;right&#039;
    ),
    array(
        &quot;member&quot;=&gt;&#039;ScoreCalcSpeed&#039;,
        &quot;aggregator&quot;=&gt;&#039;avg&#039;,
        &quot;width&quot;=&gt;80,
        &quot;label&quot;=&gt;&#039;Speed&#039;,
        &quot;formatter&quot;=&gt;&#039;number&#039;,
        &quot;align&quot;=&gt;&#039;right&#039;       
    )
)); 

// Set the pivot options
$pivot-&gt;setPivotOptions(array(
    &quot;rowTotals&quot; =&gt; true, 
    &quot;colTotals&quot; =&gt; true
));

$pivot-&gt;renderPivot($sGridObjectID,&quot;{$sGridObjectID}Pager&quot;,true, null,true,true);
?&gt;

100

101

102

103

104

105

106

107

108

109

<?php

// Our grids ID

$sGridID="ProjectCandidateList";

$sGridObjectID="#{$sGridID}";

if(isset($_REQUEST['nproj']))

{

$id = jqGridUtils::Strip($_REQUEST["nproj"]);

}

else

{

$id = 0;

}

// Create the jqGrid instance

$pivot = new jqPivotGrid($g_pdoHandle);

// the actual query for the grid data

$sSQL = "SELECT

projectcandidate.ProjectCandidateID,

concat(candidate.Forename,' ',candidate.Surname) as CandidateName,

test.Name as TestName,

testtaken.HeadlineScoreCalcPC,

testtaken.ScoreCalcSpeed,

projectcandidate.OrganisationProjectID

FROM

projectcandidate

LEFT JOIN candidate ON projectcandidate.CandidateID = candidate.CandidateID

LEFT JOIN testassignment ON projectcandidate.CandidateID = testassignment.CandidateID

LEFT JOIN test ON testassignment.TestID = test.TestID

LEFT JOIN testtaken ON testassignment.TestAssignmentID = testtaken.TestAssignmentID

WHERE

projectcandidate.OrganisationProjectID = {$id}";

send_to_log($sSQL);

$pivot->SelectCommand = $sSQL;

// Set the url from where we obtain the data

$pivot->setUrl(current_path("jqgridGetProjectCandidates"));

// Set some grid options

$pivot->setGridOptions(array(

"rowNum"=>25,

"rowList"=>array(25,100,200,$sShowAll),

"height"=>"100%",

"altRows"=>true,

"autowidth"=> true,

"responsive"=> true,

"forceFit"=>true,

"caption"=>"Project Candidates",

"hidegrid"=>false,

"loadtext"=>"<p>".translator_get_text("Loading, please wait...","SharedUI")."</p><p><img src='".get_resource_path("images/processing-32x32.gif")."'></p>",

"recordtext" => translator_get_text("Viewing {0} - {1} of {2}","SharedUI"),

"emptyrecords"=> translator_get_text("No records to view","SharedUI"),

"savetext"=> translator_get_text("Saving...","SharedUI"),

"pgtext" => translator_get_text("Page {0} of {1}","SharedUI"),

"pgfirst" => translator_get_text("First Page","SharedUI"),

"pglast" => translator_get_text("Last Page","SharedUI"),

"pgnext" => translator_get_text("Next Page","SharedUI"),

"pgprev" => translator_get_text("Previous Page","SharedUI"),

"pgrecs" => translator_get_text("Records per Page","SharedUI"),

"showhide"=> translator_get_text("Toggle Expand Collapse Grid","SharedUI")

)

);

// Grid xDimension settings

$pivot->setxDimension(array(

array("dataName"=>"CandidateName", "width"=>100)

));

// Grid yDimension settings

$pivot->setyDimension(array(

array("dataName" => "TestName")

));

// Members

$pivot->setaggregates(array(

array(

"member"=>'HeadlineScoreCalcPC',

"aggregator"=>'avg',

"width"=>80,

"label"=>'Score',

"formatter"=>'number',

"align"=>'right'

array(

"member"=>'ScoreCalcSpeed',

"aggregator"=>'avg',

"width"=>80,

"label"=>'Speed',

"formatter"=>'number',

"align"=>'right'

)

));

// Set the pivot options

$pivot->setPivotOptions(array(

"rowTotals" => true,

"colTotals" => true

));

$pivot->renderPivot($sGridObjectID,"{$sGridObjectID}Pager",true, null,true,true);

Creator

Topic

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

Author

Replies

June 14, 2018 at 12:42 pm #126862

sagana

Participant

I have sorted out a number of issues in the above and have data in the pivot grid provided I hard code the $id in the select.

$selectproj = &lt;&lt;&lt;PROJID
function(rowid, selected)
{
    if(rowid != null) {
        jQuery(&quot;#ProjectCandidateList&quot;).jqGrid(&#039;setGridParam&#039;,{postData:{projid:rowid}});
        jQuery(&quot;#ProjectCandidateList&quot;).trigger(&quot;reloadGrid&quot;);                 
    }
}
PROJID;
$grid-&gt;setGridEvent(&#039;onSelectRow&#039;, $selectproj);

$selectproj = <<<PROJID

function(rowid, selected)

{

if(rowid != null) {

jQuery("#ProjectCandidateList").jqGrid('setGridParam',{postData:{projid:rowid}});

jQuery("#ProjectCandidateList").trigger("reloadGrid");

}

PROJID;

$grid->setGridEvent('onSelectRow', $selectproj);

in the first grid is correctly populating rowid and therefore projid, however this is not being picked up in the pivot grid. $_REQUEST is empty

if(isset($_REQUEST[&#039;projid&#039;]))
{
    $id = jqGridUtils::Strip($_REQUEST[&quot;projid&quot;]);
}
else
{
    $id = 0;
}

if(isset($_REQUEST['projid']))

{

$id = jqGridUtils::Strip($_REQUEST["projid"]);

}

else

{

$id = 0;

}

is jQuery(“#ProjectCandidateList”).jqGrid(‘setGridParam’,{postData:{projid:rowid}}); the correct way to pass the id to a pivot grid?

This reply was modified 7 years, 10 months ago by sagana.

June 14, 2018 at 2:07 pm #126864

sagana

Participant

Passing parameter to pivot grid fixed by using
jQuery("#ProjectCandidateList").jqGrid('setGridParam',{datatype: 'json',postData:{ProjectID:rowid}});
Select statement in pivot grid now correctly formed but still no data in pivot grid. It appears the pivot grid does not include the relevant parameters on the second pass when using $pivot->setData. Using $pivot->setUrl passes the parameters but has other issues. Can anyone please offer any clarification.

This reply was modified 7 years, 10 months ago by sagana.

June 14, 2018 at 2:43 pm #126866

guriddo.support

Participant

Hello,

First of all thank you for posting a solution.

The clarification of the solution is that after the pivot grid is created and data is loaded the datatype is set internally to local – i.e if you need to load new data from server you will need to set it again to json.

It is a good idea before to load the new data to clear the pivot (grid data)

Kind Regards,

Will

Guriddo Support Team

June 15, 2018 at 12:35 pm #126870

sagana

Participant

Many thanks for the reply. I have added $pivot->dataType=”json”; to the construction of the pivot grid but it makes no difference. Is this how it is supposed to be done?

// We should clear the grid data on second grid on sorting, paging, etc.
$cleargrid = &lt;&lt;&lt;CLEAR
function(rowid, selected)
{
   // clear the grid data and footer data
   jQuery(&quot;#ProjectCandidateList&quot;).jqGrid(&#039;clearGridData&#039;,true);
}
CLEAR;

$grid-&gt;setGridEvent(&#039;onPaging&#039;, $cleargrid);
$grid-&gt;setGridEvent(&#039;onSortCol&#039;, $cleargrid);

// We should clear the grid data on second grid on sorting, paging, etc.

$cleargrid = <<<CLEAR

function(rowid, selected)

{

// clear the grid data and footer data

jQuery("#ProjectCandidateList").jqGrid('clearGridData',true);

}

CLEAR;

$grid->setGridEvent('onPaging', $cleargrid);

$grid->setGridEvent('onSortCol', $cleargrid);

is included in the calling grid.

Once this is working can you please confirm I can add columns as per normal grid into which I can place icons that will fire actions for the row.

Regards
Murray

June 18, 2018 at 9:21 am #126880

guriddo.support

Participant

Hello,

I’m really confused, but I can’t understand the problem.

To summarize: You have a master detail solution like this here where your detail is a pivot grid. Is this the case?

Guriddo Support Team

June 18, 2018 at 9:48 am #126881

sagana

Participant

Thank you for the reply and example. I will study that and hopefully see where I am going wrong. As to the second part of my question, can you please confirm I can add columns to the pivot grid and place icons there that would activate actions relative to the row on which the icon is?

Many thanks
Murray

June 18, 2018 at 10:41 am #126882

guriddo.support

Participant

Hello,

PivotGrid columns are builded dynamically depending on the source data. After the data is processed new columns are build-ed and new data is created. All this is done automatically.

After the pivot is created you can’t add columns (in the grid you allways can’t add column dynamically).

As far as I understand you want to add a actions column (like edit add delete) after the pivot is created?

Usually the pivot is used as report result and not as editing data. Editing a pivot is a little bit difficult, but not impossible

I suggest you if you want to edit the certain data to overcome using a pivot in this case.

Kind Regards

Will

Guriddo Support Team

June 18, 2018 at 10:50 am #126883

sagana

Participant

Hi,

Yes I am trying to create a Master/Detail scene where the detail is a pivot grid.

You mentioned “if you need to load new data from server you will need to set it again to json.” So I adjusted the call to reset it as below.

// set the ouput format to json
$pivot->dataType=”json”;

// Set the url from where we obtain the data
$pivot->setData(index.php?Action=getData);

However when the pivot grid makes a request to get the data again it drops the

“?Action=getData”

from the call it makes. It actually seems to drop all but jqGrid params. Am I setting the data back to json in the right place / way?

Here is an example of the call the pivot grid makes on refresh:

index.php?_search=false&nd=1529311477741&rows=10&page=1&sidx=CategoryName&sord=asc

You can see it is missing the ?Action= param.

Thanks.

June 18, 2018 at 12:39 pm #126884

sagana

Participant

Hi
Yes, I want to add an action column. Not to edit data but to launch an additional report on the subject of the row. ie. pass the rowid to a javascript function that will initiate the appropriate action. This column would be created as part of the pivot grid as the grid is first created not dynamically added. Is this possible?
Thank you.

June 18, 2018 at 1:48 pm #126886

guriddo.support

Participant

Hello,

I recommend you to read the http://www.guriddo.net/documentation/guriddo/javascript/user-guide/pivotgrid/”>docs here

$pivot->dataType=”json”;

set the initial format to json when the grid get the data. After that the data is local and you will need to set this in the javascript when the row is selected.

When you sort or paging the master you actually need to unload the pivot grid and create it again when you select the row on master.

The same should apply if select new row on master – i.e unload the existing pivot ($.jgrid.gridUnload(grid_id) ) and create new one. This is a little bit difficult and not the same as the regular example of master detail.

The main that should be considered is that after selecting every new row or sort and paging the data at master the pivot should be destroyed

In order this to work you will need load the new pivot via ajax in order to get the correct data. Example how to load a grid via ajax is here.

Not sure, but if you can omit the pivot in this situation it will make you life easier.

jqPivot

Search Forum

Recent Replies

Forum Statistics

UserOnline