Building a pivot table data view

Documentation

This is a pivot table view you can build using GeneralPurposes Nodes.

Clicking the 2nd column in the first row gives you a view of the corresponding order data for that cell:

A pivot list data view working example is running at the following address using sample data contained in the Order Items Export .csv file from woo commerce:

https://www.generalpurposes.com/nodes/pivot1/

Open or create a new node and paste the code below into the landing page editor by clicking the pencils where you want the view to display. Paste and save.

<link rel="stylesheet" type="text/css" href="TerraDctl_view.css" />
[[CABLEBOX:csv_parser]]
[[READ_FILE:Order Items Export.csv|csv_data]]
[[STRING_REPLACE:csv_data|’|'|csv_data]]
[[STRING_REPLACE:csv_data|`|'|csv_data]]

[[CSV_DESERIALIZE:csv_data|,]]

[[BOX:links|float:left;font-size:70%;font-family:verdana;color:white;]]
 [[QUERY_STRING:select]]
 [[IF:$select=='flip']]
 [[PIVOT_TABLE:csv_data|pivot_data|0|2|5]]
 [[LINK:http://www.generalpurposes.com/nodes/pivot2/|FLIP]]
 [[ELSE]]
 [[PIVOT_TABLE:csv_data|pivot_data|2|0|5]]
 [[LINK:http://www.generalpurposes.com/nodes/pivot2/?select=flip|FLIP]]
 [[END_IF]]
[[END_BOX]]
[[QUERY_STRING:search]]

<form action="?select=[[=select]]#this" style="float:right;margin:0px;padding:0px;padding-bottom:4px;">
 <input id="search" name="search" value="[[=search]]" type="text" style="border:1px #000 solid;">

<input id="select" name="select" value="[[=select]]" type="hidden" style="border:1px #000 solid;">

<input style="background-color:$bbb;color:green;border:1px #000 solid;" value=" ? " type="submit">
 </form>

<!-- comment may be deleted: notice that the next required step must be done twice and is used to load all the empty cells with zeros /-->
[[STRING_REPLACE:pivot_data|,,|,0,|pivot_data]]
[[STRING_REPLACE:pivot_data|,,|,0,|pivot_data]]

<?php
$needle=",".PHP_EOL;
$rep=",0".PHP_EOL;
?>

[[STRING_REPLACE:pivot_data|$needle|$rep|pivot_data]]
[[CSV_DESERIALIZE:pivot_data|,]]

<?php 
//if no unspecified then delete column.
foreach($v_UNSPECIFIED as $v){$va=$va+$v;}
if(!$va){
unset($v_UNSPECIFIED);
array_splice($matrix_fields, 1, 1);
array_splice($matrix_fields_orig, 1, 1);
}
 ?>

[[br]]
[[VIEW_DATAFEED]]
[[INITIALIZE:perpage|20]]
[[INITIALIZE:sort_order|"{ASCENDING}"]]
[[QUERY_STRING:sort_order]]
[[INITIALIZE:sort_column|Name]]
[[QUERY_STRING:sort_column]]
[[INITIALIZE:v_page|1]]
[[QUERY_STRING:v_page]]
[[VIEW_SEARCH:search]]
[[INITIALIZE:numpages|intval(count($TerraDctl_view)/$perpage)+1]]
[[IF:$v_page>$numpages]]
 [[INITIALIZE:v_page|$numpages]]
[[END_IF]]

[[IF:$sort_order=="{DESCENDING}"]]
 [[VIEW_SORT_DESC:$sort_column]]
[[ELSE]]
 [[VIEW_SORT:$sort_column]]
[[END_IF]]
 
[[TABLE:viewtable|TerraDctl_view_table|width:100%;text-align:center]]
[[VIEW_COLUMN_SORT]]
[[VIEW_PAGINATE:$v_page|$perpage]]

[[PIVOT_VIEW_DISPLAY:http://www.generalpurposes.com/nodes/pivot_source_data/]]
[[END_TABLE]]
[[IF:$numpages>1]][[cr]][[VIEW_PAGELINKS:$numpages]][[cr]][[END_IF]]

Go to CSS File manager and create a file called TerraDctl_view.css. Paste the following CSS code and save:

.TerraDctl_view_table{font-family:arial;border:1px #444 solid;padding:0px;margin:0px;border-spacing:1px;font-size:70%;background-color:#ccc;}
.TerraDctl_view_table td{width:0%;text-align:left;padding:0px;margin-left:0px;margin-right:0px;margin-top:0px;margin-bottom:0px;padding-top:5px;padding-left:20px;padding-right:5px;}
.TerraDctl_view_table td a{text-decoration:none;}
.TerraDctl_view_table td a:hover{text-decoration:none;color:green;}
.TerraDctl_view_skin{border:1px #000 solid;padding:5px;margin:5px;background-color:#28517f;}
.TerraDctl_view_header{text-align:center;border:2px #000 solid;padding:0px;margin:0px;background-color:#99f;}
.TerraDctl_view_header td{vertical-align:text-top;width:0%;text-align:left;border-bottom:1px #000 solid;padding:5px;margin:0px;}

.TerraDctl_view_header td:hover{text-align:left;border-bottom:1px green solid;background-color:#999;}
.TerraDctl_view_header tr{border-bottom:1px #000 solid;position:sticky;top:0;}
.TerraDctl_view_header td a{font-size:100%;font-weight:normal;text-decoration:none;}
#links a{background-color:#999;padding:2px;margin:2px;border:1px #444 outset;text-decoration:none;color:blue;}
#links a:hover{background-color:#bbb;padding:2px;margin:2px;border:1px #555 inset;text-decoration:none;color:green;}
#links a:active{background-color:#ddd;padding:2px;margin:2px;border:1px #555 inset;text-decoration:none;color:black;font-weight:strong;}

.pagination_links{border:1px #444 solid;margin:2px;padding-top:0px;padding:3px;text-decoration:none;background-color:#ccf;font-family:verdana;font-size:120%;}
.pagination_page_number{border:1px #000 solid;margin-right:6px;margin-left:2px;padding-top:0px;padding:3px;color:#ccf;background-color:#444;font-family:verdana;font-size:120%;}
.odd{background-color:#bbb;border-bottom:1px #000 solid;}
.even{background-color:#aaa;}

Now you will need to create a separate node for displaying your source data views.(pivot_source_data) Use this internet address for the url in your PIVOT_VIEW_DISPLAY shortcode.

 

The code:

<link rel="stylesheet" type="text/css" href="TerraDctl_view.css" /> 
[[CABLEBOX:csv_parser]]
[[READ_FILE:Order Items Export.csv|csv_data]]
[[STRING_REPLACE:csv_data|’|'|csv_data]]
[[STRING_REPLACE:csv_data|`|'|csv_data]]
[[STRING_REPLACE:csv_data|,,|,0,|csv_data]]
[[STRING_REPLACE:csv_data|,,|,0,|csv_data]]
[[STRING_REPLACE:csv_data|, ,|,0,|csv_data]]
<?php
$needle=",".PHP_EOL;
$rep=",0".PHP_EOL;
?>
[[STRING_REPLACE:csv_data|$needle|$rep|csv_data]]
[[CSV_DESERIALIZE:csv_data|,]
<?php
//populate empty fields
foreach($matrix_fields as $field){
 $i=0; 
 foreach($$field as $cell){
 if($cell==""){$stmt='$'.$field.'['.$i.']="0";';eval($stmt);}
 $i++;
 }
}
?>
[[QUERY_STRING:search]]
<form action="?select=[[=select]]#this" style="float:right;margin:0px;padding:0px;padding-bottom:4px;">
 <input id="search" name="search" value="[[=search]]" type="text" style="border:1px #000 solid;">
 <input id="select" name="select" value="[[=select]]" type="hidden" style="border:1px #000 solid;">
 <input style="background-color:$bbb;color:green;border:1px #000 solid;" value=" ? " type="submit">
</form> 
[[br]]
[[VIEW_DATAFEED]]
[[INITIALIZE:perpage|20]]
[[INITIALIZE:sort_order|"{ASCENDING}"]]
[[QUERY_STRING:sort_order]]
[[INITIALIZE:sort_column|Name]]
[[QUERY_STRING:sort_column]]
[[INITIALIZE:v_page|1]]
[[QUERY_STRING:v_page]]
[[VIEW_SEARCH:search]]
[[INITIALIZE:numpages|intval(count($TerraDctl_view)/$perpage)+1]]
[[IF:$v_page>$numpages]]
 [[INITIALIZE:v_page|$numpages]]
[[END_IF]]
[[IF:$sort_order=="{DESCENDING}"]]
 [[VIEW_SORT_DESC:$sort_column]]
[[ELSE]]
 [[VIEW_SORT:$sort_column]]
[[END_IF]] 
[[TABLE:viewtable|TerraDctl_view_table|width:100%;text-align:center]]
[[VIEW_COLUMN_SORT]]
[[VIEW_PAGINATE:$v_page|$perpage]]
[[VIEW_DISPLAY]]
[[END_TABLE]]
[[IF:$numpages>1]][[cr]][[VIEW_PAGELINKS:$numpages]][[cr]][[END_IF]]

Create a file called TerraDctl_view.css and paste the following code:

.TerraDctl_view_table{font-family:arial;border:1px #444 solid;padding:0px;margin:0px;border-spacing:1px;font-size:70%;background-color:#ccc;}
.TerraDctl_view_table td{vertical-align:text-top;width:0%;text-align:left;padding:0px;margin-left:0px;margin-right:0px;margin-top:0px;margin-bottom:0px;padding-top:5px;padding-left:20px;padding-right:5px;}
.TerraDctl_view_table td a{text-decoration:none;}
.TerraDctl_view_table td a:hover{text-decoration:none;color:green;}
.TerraDctl_view_skin{border:1px #000 solid;padding:5px;margin:5px;background-color:#28517f;}
.TerraDctl_view_header{text-align:center;border:2px #000 solid;padding:0px;margin:0px;background-color:#99f;}
.TerraDctl_view_header td{vertical-align:text-top;width:0%;text-align:left;border-bottom:1px #000 solid;padding:5px;margin:0px;}

.TerraDctl_view_header td:hover{text-align:left;border-bottom:1px green solid;background-color:#999;}
.TerraDctl_view_header tr{border-bottom:1px #000 solid;position:sticky;top:0;}
.TerraDctl_view_header td a{font-size:100%;font-weight:normal;text-decoration:none;}
#links a{background-color:#999;padding:2px;margin:2px;border:1px #444 outset;text-decoration:none;color:blue;}
#links a:hover{background-color:#bbb;padding:2px;margin:2px;border:1px #555 inset;text-decoration:none;color:green;}
#links a:active{background-color:#ddd;padding:2px;margin:2px;border:1px #555 inset;text-decoration:none;color:black;font-weight:strong;}

.pagination_links{border:1px #444 solid;margin:2px;padding-top:0px;padding:3px;text-decoration:none;background-color:#ccf;font-family:verdana;font-size:120%;}
.pagination_page_number{border:1px #000 solid;margin-right:6px;margin-left:2px;padding-top:0px;padding:3px;color:#ccf;background-color:#444;font-family:verdana;font-size:120%;}
.odd{background-color:#bbb;border-bottom:1px #000 solid;}
.even{background-color:#aaa;}

The”flip” link swaps the x axis(columns) for the y axis(rows).

See also: CSV_DESERIALIZE, PIVOT_TABLE, PIVOT_VIEW_DISPLAY, VIEW_DISPLAY.