fhumanes author
I have done this example at the request of Danilo, who told me that he had problems using it and more specifically, how to provide JSON-type data.
This exercise has very little news compared to others that are already published, but I think it can help to refresh those different ways of doing developments. In addition, this library is very, very powerful (a whole world) and can be of help in some of your developments.
The library can be downloaded from: https://pivottable.js.org/examples/
It is totally free and has many examples and documentation, however, like everything that is done in JavaScript, at least for me, it is difficult to understand and debug, in the face of any problem.
Objectives of the example
I set the following goals for myself.
- Identify the "dimensions" and "values" fields in the data cube.
- Be able to define punctuation marks for values according to language and data type.
- Be able to use the product in different languages. Always testing in Spanish !!!
- Be able to use the graphics functionality.
- To be able to define the colors that I want to apply to one of the "dimensions".
DEMO: https://fhumanes.com/pivot
Technical Solution
The exercise is done in PHPRunner 10.4, but it could be done in any 10.X version.
I have used the database from the example I made about the Madrid Elections, because it had the basic data that I required to fulfill the objectives that I had proposed.
To make the data presentation I have used a "LIST" page where I have deleted all the presentation data. Also, I have integrated this query (with filters and searches) so that it could also affect the data of the “pivot” object, in order to add functionality that could be useful to whoever reviews this example.
The "select" that is built, according to the filters and searches, is shown in the footer, for review (this should not be done in any application in production).
The page of the pivot table is built using a "snippet" that has this code:
<?php
@ini_set("display_errors","1");
@ini_set("display_startup_errors","1");
require_once("include/dbcommon.php");
// List of colors of Candidaturas
$colores = '';
$sql="SELECT Codigo, Color FROM candidatura";
$resql=DB::Query($sql,$conn);
while($row = $resql->fetchAssoc()) {
$colores .= "'".$row['Codigo']."' : '".$row['Color']."',";
}
$html = <<<EOT
<!-- external libs from cdnjs -->
<!-- script src="https://cdn.plot.ly/plotly-basic-latest.min.js"></script -->
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.js"></script>
<!-- script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script -->
<!-- script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script -->
<!-- PivotTable.js libs from pivotTable/dist -->
<link rel="stylesheet" type="text/css" href="pivotTable/dist/pivot.css">
<script type="text/javascript" src="pivotTable/dist/pivot.js"></script>
<script type="text/javascript" src="pivotTable/dist/pivot.es.js"></script>
<!-- script type="text/javascript" src="pivotTable/dist/plotly_renderers.js"></script -->
<script type="text/javascript" src="pivotTable/dist/c3_renderers.js"></script>
<!-- optional: mobile support with jqueryui-touch-punch -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui-touch-punch/0.2.3/jquery.ui.touch-punch.min.js"></script>
<script type="text/javascript">
// This example adds Plotly chart renderers.
// This example adds C3 chart renderers.
$(function(){
// defaults = { digitsAfterDecimal: 0, scaler: 1, thousandsSep: ".", decimalSep: ",", prefix: "", suffix: "" };
function getFormat(short) {
if (short == '%') {
return $.pivotUtilities.numberFormat({ digitsAfterDecimal: 2, scaler: 100, thousandsSep: ".", decimalSep: ",", prefix: "", suffix: "%" });
}
if (short == '%%') {
return $.pivotUtilities.numberFormat({ digitsAfterDecimal: 2, scaler: 1, thousandsSep: ".", decimalSep: ",", prefix: "", suffix: "%" });
}
if (short == '$') {
return $.pivotUtilities.numberFormat({digitsAfterDecimal: 0, scaler: 1, thousandsSep: ".", decimalSep: ",", prefix:'$', suffix: ""});
}
if (short == '#') {
return $.pivotUtilities.numberFormat({digitsAfterDecimal: 0, scaler: 1, thousandsSep: ".", decimalSep: ",", prefix: "", suffix: ""});
}
if (short == '$M') {
return $.pivotUtilities.numberFormat({digitsAfterDecimal: 2, scaler: 1000, thousandsSep: ".", decimalSep: ",", prefix: '$', suffix: ""});
}
return $.pivotUtilities.numberFormat();
}
var tpl = $.pivotUtilities.aggregatorTemplates;
var derivers = $.pivotUtilities.derivers;
var renderers = $.extend($.pivotUtilities.renderers,
// $.pivotUtilities.plotly_renderers);
$.pivotUtilities.c3_renderers);
$.getJSON("elecciones_json001.php", function(mps) {
$("#output").pivotUI(mps, {
renderers: renderers,
cols: ["Convocatoria"],
rows: ["Siglas"],
rendererName: "Table",
aggregators: {
"Votos": function() { return tpl.sum(getFormat('#'))(["Votos"]) },
"Escaños": function() { return tpl.sum(getFormat('#'))(["Escaños"]) },
"% Votos": function() { return tpl.sum(getFormat('%%'))(["% Votos"]) }
},
rendererOptions: {
c3: { data: {colors: {
$colores
}}}}
},false, "es");
});
});
</script>
<div id="output" style="margin: 30px;"></div>
EOT;
echo $html;
The dynamic table is dynamically supplied with a JSON file that is built with this code:
<?php
@ini_set("display_errors","1");
@ini_set("display_startup_errors","1");
require_once("include/dbcommon.php");
header("Expires: Thu, 01 Jan 1970 00:00:01 GMT");
// Construct JSON
$all_row = [];
global $conn;
$sql = $_SESSION['SQL001']; // Bring the Select page with the filters done
if (!isset($_SESSION['SQL001'])) {
$sql="select
-- idEscanos,
-- Orden_Ana Orden,
convocatoria.Titulo Convocatoria,
Codigo Siglas,
candidatura.Titulo Candidatura,
-- Color,
Votos,
PorcVotos `% Votos`,
Escanos `Escaños`
from escanos
join convocatoria on (idConvocatoria = Convocatoria_idConvocatoria and EsAsamblea = 1)
join candidatura on (idCandidatura = Candidatura_idCandidatura)
order by Orden_Ana desc";
}
$resql=DB::Query($sql,$conn);
while($row = $resql->fetchAssoc()) {
$all_rows[] = $row;
}
echo json_encode($all_rows, JSON_UNESCAPED_UNICODE); // convert Array to Json
?>
As I have tried to explain, I am not an expert in this "pivottable.js" library, so if you ask about it, it is very likely that I will not be able to answer you, but for any problem in this example, I will gladly answer you and solve the doubts that have. Ask me the questions through my email [email="fernandohumanes@gmail.com"]fernandohumanes@gmail.com[/email]
Also, as I always do, I leave the sources of the example on my portal, so that you can reproduce it on your computers.
|
|