Graph view in Lizmap Records - Record Count and Record Detail

[sergiuspinheiro]

Hello, will it be possible to implement the visualization of Lizmap Records data in graphic?

I know it's a non-essential feature but it makes it easier to understand the given use of lizmap.

I made some changes to the lizmap code for testing, as well as views in postgres to provide data to the graphs

I leave some images of the result

If this resource is considered, I will send the example code of the postgres views and the example code of the graphics to lizmap, in the event that it is necessary.

thank you so much

[nboisteault]

Hi, This is some great work we'd appreciate you to share. Thank you.

[Gustry]

Which library are you using to draw plots? Is-it plotly? Because this lib is already included in Lizmap, for the dataviz.

It looks a nice feature!

[sergiuspinheiro]

Hi @nboisteault @Gustry,

Yes, I used plotly.js from lizmap/dataviz

However my approach is not very clean, I created a direct connection to postgres in php and made a dirty approach for testing purposes, a mix between php js and sql with shared parts...

Edited files:

\lizmap\modules\admin\templates\logs_view.tpl

.....
    <div class="form-actions">
      <a class="btn" href="{jurl 'admin~logs:counter'}">{@admin~admin.logs.view.button@}</a>
      <!-- INTRODUCED COD  -->
      <a class="btn" href="/set_location/estatis.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
      <!-- END -->
      <a class="btn" href="{jurl 'admin~logs:emptyCounter'}" onclick="return confirm(`{@admin~admin.logs.empty.confirm@}`)">{@admin~admin.logs.empty.button@}</a>
    </div>
.....

\lizmap\modules\admin\templates\logs_counter.tpl

.....
    <h2>{@admin~admin.logs.counter.title@}</h2>
    <!-- INTRODUCED COD -->
    <div class="form-actions">
      <a class="btn" href="/set_location/estatis.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
      <iframe style="width: 100%; height: 50vh; border: 0;" src="/set_location/estatis.php">
      </iframe>
    </div>
     <!-- END -->
    <table class="table table-bordered table-striped">
.....

\lizmap\modules\admin\templates\logs_detail.tpl

.....
    <h2>{@admin~admin.logs.counter.title@}</h2>
         <!-- INTRODUCED COD -->
     <div class="accordion" id="accordion2">
       <div class="accordion-group">
         <div class="accordion-heading">
           <a class="accordion-toggle" data-toggle="collapse" data-parent="#accordion2" href="#collapse1">{@admin~admin.logs.view.graphic.key@}</a>
         </div>
         <div id="collapse1" class="accordion-body collapse">
           <div class="accordion-inner">
                 <div class="form-actions">
                 <a class="btn" href="/ajuda/lizmap/estatis_ldkt.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
                 <iframe style="width: 100%; height: 60vh; border: 0;" src="/ajuda/lizmap/estatis_ldkt.php">
                 </iframe>
                 </div>
           </div>
         </div>
       </div>

       <div class="accordion-group">
         <div class="accordion-heading">
           <a class="accordion-toggle" data-toggle="collapse" data-parent="#accordion2" href="#collapse2">{@admin~admin.logs.view.graphic.user@}</a>
         </div>
         <div id="collapse2" class="accordion-body collapse">
           <div class="accordion-inner">
                 <div class="form-actions">
                 <a class="btn" href="/ajuda/lizmap/estatis_ldut.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
                 <iframe style="width: 100%; height: 60vh; border: 0;" src="/ajuda/lizmap/estatis_ldut.php">
                 </iframe>
                 </div>
           </div>
         </div>
       </div>

       <div class="accordion-group">
         <div class="accordion-heading">
           <a class="accordion-toggle" data-toggle="collapse" data-parent="#accordion2" href="#collapse3">{@admin~admin.logs.view.graphic.project@}</a>
         </div>
         <div id="collapse3" class="accordion-body collapse">
           <div class="accordion-inner">
                 <div class="form-actions">
                 <a class="btn" href="/ajuda/lizmap/estatis_ldpt.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
                 <iframe style="width: 100%; height: 60vh; border: 0;" src="/ajuda/lizmap/estatis_ldpt.php">
                 </iframe>
                 </div>
           </div>
         </div>
       </div>

       <div class="accordion-group">
         <div class="accordion-heading">
           <a class="accordion-toggle" data-toggle="collapse" data-parent="#accordion2" href="#collapse4">{@admin~admin.logs.view.graphic.repository@}</a>
         </div>
         <div id="collapse4" class="accordion-body collapse">
           <div class="accordion-inner">
                 <div class="form-actions">
                 <a class="btn" href="/ajuda/lizmap/estatis_ldrt.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
                 <iframe style="width: 100%; height: 60vh; border: 0;" src="/ajuda/lizmap/estatis_ldrt.php">
                 </iframe>
                 </div>
           </div>
         </div>
       </div>

       <div class="accordion-group">
         <div class="accordion-heading">
           <a class="accordion-toggle" data-toggle="collapse" data-parent="#accordion2" href="#collapse5">{@admin~admin.logs.view.graphic.project.user@}</a>
         </div>
         <div id="collapse5" class="accordion-body collapse">
           <div class="accordion-inner">
                 <div class="form-actions">
                 <a class="btn" href="/ajuda/lizmap/estatis_ldkurpt.php" target="_blank">{@admin~admin.logs.view.graphic@}</a>
                 <iframe style="width: 100%; height: 60vh; border: 0;" src="/ajuda/lizmap/estatis_ldkurpt.php">
                 </iframe>
                 </div>
           </div>
         </div>
       </div>

     </div>
     <!-- END -->
    <table class="table table-bordered table-striped">
.....

\lizmap\app\locales\pt_PT\admin\locales\admin.UTF-8.properties
.....
logs.view.button=Ver
;; INTRODUCED COD 
logs.view.graphic=Abrir gráfico em página inteira
logs.view.graphic.key=Gráfico | Total por Chave
logs.view.graphic.user=Gráfico | Total por Utilizador
logs.view.graphic.project=Gráfico | Total por Projeto
logs.view.graphic.repository=Gráfico | Total por Repositório
logs.view.graphic.project.user=Gráfico | Total por Projeto/Utilizador
;; END
logs.export.button=Exportar
.....

New files

\lizmap\set_location\i.php
\lizmap\set_location\sql_lc.php
\lizmap\set_location\sql_ldkt.php
\lizmap\set_location\sql_ldkurpt.php
\lizmap\set_location\sql_ldpt.php
\lizmap\set_location\sql_ldrt.php
\lizmap\set_location\sql_ldut.php
\lizmap\set_location\estatis.php
\lizmap\set_location\estatis_ldkt.php
\lizmap\set_location\estatis_ldkurpt.php
\lizmap\set_location\estatis_ldpt.php
\lizmap\set_location\estatis_ldrt.php
\lizmap\set_location\estatis_ldut.php

file list

\lizmap\set_location\i.php

<?php
// Connecting, selecting database
$dbconn = pg_connect("host=host dbname=dbname user=user password=password")
    or die('Could not connect: ' . pg_last_error());
?>

\lizmap\set_location\sql_lc.php

<?php
// info conexão
include 'i.php';

// Performing SQL query
$query = 'SELECT data
    FROM public.v_log_counter_legenda';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

// Printing results in HTML
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    foreach ($line as $col_value) {
        echo $col_value;
    }
}

// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);
?>

\lizmap\set_location\sql_ldkt.php the same code as the sql_lc.php file change line 7

<?php
...
    FROM public.v_log_detail_log_key_total_legenda';
...
?>

\lizmap\set_location\sql_ldkurpt.php the same code as the sql_lc.php file change line 7

<?php
...
    FROM public.v_log_detail_log_key_user_repository_project_total_legenda';
...
?>

\lizmap\set_location\sql_ldpt.php the same code as the sql_lc.php file change line 7

<?php
...
    FROM public.v_log_detail_log_project_total_legenda';
...
?>

\lizmap\set_location\sql_ldrt.php the same code as the sql_lc.php file change line 7

<?php
...
    FROM public.v_log_detail_log_repository_total_legenda';
...
?>

\lizmap\set_location\sql_ldut.php the same code as the sql_lc.php file change line 7

<?php
...
    FROM public.v_log_detail_log_user_total_legenda';
...
?>

\lizmap\set_location\estatis.php

<html lang="pt" >
    <head>
    <meta content="text/html; charset=UTF-8" http-equiv="content-type">
    <title>Projetos - Arqvector - Estatísticas</title>
    <meta name="description" content="">
    <meta name="keywords" content="">
    <link rel="shortcut icon" href="/assets/favicon/favicon.ico">
   <!-- <script src="./DataPlotly/jsscripts/polyfill.min.js"></script>
    <script src="./DataPlotly/jsscripts/plotly-1.52.2.min.js"></script> -->
    <script type="text/javascript" src="/assets/js/dataviz/plotly-latest.min.js"></script>
    <style id="plotly.js-style-global"></style>
    <script type="text/javascript" src="/assets/js/dataviz/dataviz.js"></script>
<style>
body {
    background: #EFEFEF;
}
</style>
    </head>
    <div>
    <div id="da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe" class="plotly-graph-div" style="height:100%; width:100%;"></div>
    <script type="text/javascript">
        var dados = <?php pg_free_result($result); include ('sql_lc.php');?>
                    window.PLOTLYENV=window.PLOTLYENV || {};

                if (document.getElementById("da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe")) {
                    dados
                };

    </script>
    </div>
    <script>
        // additional js function to select and click on the data
        // returns the ids of the selected/clicked feature

        var plotly_div = document.getElementById('da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe')
        var plotly_data = plotly_div.data

        // selecting function
        plotly_div.on('plotly_selected', function(data){
        var dds = {};
        dds["mode"] = 'selection'
        dds["type"] = data.points[0].data.type

        featureIds = [];
        featureIdsTernary = [];

        data.points.forEach(function(pt){
        featureIds.push(parseInt(pt.id))
        featureIdsTernary.push(parseInt(pt.pointNumber))
        dds["id"] = featureIds
        dds["tid"] = featureIdsTernary
            })
        //console.log(dds)
        window.status = JSON.stringify(dds)
        })

        // clicking function
        plotly_div.on('plotly_click', function(data){
        var featureIds = [];
        var dd = {};
        dd["fidd"] = data.points[0].id
        dd["mode"] = 'clicking'

        // loop and create dictionary depending on plot type
        for(var i=0; i < data.points.length; i++){

        // scatter plot
        if(data.points[i].data.type == 'scatter'){
            dd["uid"] = data.points[i].data.uid
            dd["type"] = data.points[i].data.type

            data.points.forEach(function(pt){
            dd["fid"] = pt.id
            })
        }

        // pie

        else if(data.points[i].data.type == 'pie'){
          dd["type"] = data.points[i].data.type
          dd["label"] = data.points[i].label
          dd["field"] = data.points[i].data.name
          console.log(data.points[i].label)
          console.log(data.points[i])
        }

        // histogram
        else if(data.points[i].data.type == 'histogram'){
            dd["type"] = data.points[i].data.type
            dd["uid"] = data.points[i].data.uid
            dd["field"] = data.points[i].data.name

            // correct axis orientation
            if(data.points[i].data.orientation == 'v'){
                dd["id"] = data.points[i].x
                dd["bin_step"] = data.points[i].fullData.xbins.size
            }
            else {
                dd["id"] = data.points[i].y
                dd["bin_step"] = data.points[i].fullData.ybins.size
            }
        }

        // box plot
        else if(data.points[i].data.type == 'box'){
            dd["uid"] = data.points[i].data.uid
            dd["type"] = data.points[i].data.type
            dd["field"] = data.points[i].data.customdata[0]

                // correct axis orientation
                if(data.points[i].data.orientation == 'v'){
                    dd["id"] = data.points[i].x
                }
                else {
                    dd["id"] = data.points[i].y
                }
            }

        // violin plot
        else if(data.points[i].data.type == 'violin'){
            dd["uid"] = data.points[i].data.uid
            dd["type"] = data.points[i].data.type
            dd["field"] = data.points[i].data.customdata[0]

                // correct axis orientation (for violin is viceversa)
                if(data.points[i].data.orientation == 'v'){
                    dd["id"] = data.points[i].x
                }
                else {
                    dd["id"] = data.points[i].y
                }
            }

        // bar plot
        else if(data.points[i].data.type == 'bar'){
            dd["uid"] = data.points[i].data.uid
            dd["type"] = data.points[i].data.type
            dd["field"] = data.points[i].data.customdata[0]

                // correct axis orientation
                if(data.points[i].data.orientation == 'v'){
                    dd["id"] = data.points[i].x
                }
                else {
                    dd["id"] = data.points[i].y
                }
            }

        // ternary
        else if(data.points[i].data.type == 'scatterternary'){
            dd["uid"] = data.points[i].data.uid
            dd["type"] = data.points[i].data.type
            dd["field"] = data.points[i].data.customdata
            dd["fid"] = data.points[i].pointNumber
            }

            }
        window.status = JSON.stringify(dd)
        });
    </script>

\lizmap\set_location\estatis_ldkt.php the same code as the estatis.php file update the corresponding file sql_ldkt.php

line 22

\lizmap\set_location\estatis_ldkurpt.php the same code as the estatis.php file update the corresponding file sql_ldkurpt.php

line 22

\lizmap\set_location\estatis_ldpt.php the same code as the estatis.php file update the corresponding file sql_ldpt.php

line 22

\lizmap\set_location\estatis_ldrt.php the same code as the estatis.php file update the corresponding file sql_ldrt.php

line 22

\lizmap\set_location\estatis_ldut.php the same code as the estatis.php file update the corresponding file sql_ldut.php

line 22

Includes part of the code for plotly in SQL/CTE, I know it's dirty but it was for testing

v_log_counter_legenda

CREATE OR REPLACE VIEW public.v_log_counter_legenda AS
 WITH log_counter AS (
         SELECT row_number() OVER (ORDER BY l.repository, l.project) AS aid,
            l.id,
            l.key,
            l.counter,
            l.repository,
            l.project,
                CASE
                    WHEN l.key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN l.key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN l.key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN l.key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN l.key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN l.key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS marker,
                CASE
                    WHEN l.key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN l.key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN l.key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN l.key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN l.key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN l.key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS line,
            concat(l.project, ' - ', l.key) AS text
           FROM public.log_counter l
          ORDER BY (
                CASE
                    WHEN l.key::text = 'viewmap'::text THEN 1
                    WHEN l.key::text = 'popup'::text THEN 2
                    WHEN l.key::text = 'editionSaveFeature'::text THEN 3
                    WHEN l.key::text = 'print'::text THEN 4
                    WHEN l.key::text = 'editionDeleteFeature'::text THEN 5
                    ELSE 0
                END), l.repository
        ), conta_login AS (
         SELECT log_counter.id,
            log_counter.key,
            log_counter.counter AS conta_login,
            log_counter.repository,
            log_counter.project
           FROM public.log_counter
          WHERE log_counter.key::text = 'login'::text
        ), agra_viewmap AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.id::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Visualização de mapa", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.project::text, '", "'::text), '"], "y": [', string_agg(l.counter::text, ', '::text), ']}') AS valores
           FROM log_counter l
          WHERE l.key::text = 'viewmap'::text
        ), agra_popup AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.id::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Visualização de atributos", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.project::text, '", "'::text), '"], "y": [', string_agg(l.counter::text, ', '::text), ']}') AS valores
           FROM log_counter l
          WHERE l.key::text = 'popup'::text
        ), agra_editionsavefeature AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.id::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Edição de registos", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.project::text, '", "'::text), '"], "y": [', string_agg(l.counter::text, ', '::text), ']}') AS valores
           FROM log_counter l
          WHERE l.key::text = 'editionSaveFeature'::text
        ), agra_print AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.id::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Impressão", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.project::text, '", "'::text), '"], "y": [', string_agg(l.counter::text, ', '::text), ']}') AS valores
           FROM log_counter l
          WHERE l.key::text = 'print'::text
        ), agra_editiondeletefeature AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.id::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Registos apagados", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.project::text, '", "'::text), '"], "y": [', string_agg(l.counter::text, ', '::text), ']}') AS valores
           FROM log_counter l
          WHERE l.key::text = 'editionDeleteFeature'::text
        ), junta AS (
         SELECT av.valores,
            ap.valores,
            aes.valores,
            apr.valores,
            aed.valores,
            c.id,
            c.key,
            c.conta_login,
            c.repository,
            c.project
           FROM agra_viewmap av,
            agra_popup ap,
            agra_editionsavefeature aes,
            agra_print apr,
            agra_editiondeletefeature aed,
            conta_login c
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', av.valores, ', ', ap.valores, ', ', aes.valores, ', ', apr.valores, ', ', aed.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Total login: ', c.conta_login, '"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Projecto"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_viewmap av,
            agra_popup ap,
            agra_editionsavefeature aes,
            agra_print apr,
            agra_editiondeletefeature aed,
            conta_login c
        )
 SELECT fina.data
   FROM fina;

v_log_detail_log_key_total_legenda

CREATE OR REPLACE VIEW public.v_log_detail_log_key_total_legenda AS
 WITH v_log_detail_log_key_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
            ld.log_key,
            'key'::text AS tipo
           FROM public.log_detail ld
          GROUP BY ld.log_key
        ), log_detail AS (
         SELECT t.aid,
            t.total,
            t.log_key,
                CASE
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS marker,
                CASE
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS line,
                CASE
                    WHEN t.log_key::text IS NULL THEN 'Anónimo'::text
                    ELSE t.log_key::text
                END AS text
           FROM v_log_detail_log_key_total t
          ORDER BY (
                CASE
                    WHEN t.log_key::text = 'viewmap'::text THEN 1
                    WHEN t.log_key::text = 'popup'::text THEN 2
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN 3
                    WHEN t.log_key::text = 'print'::text THEN 4
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN 5
                    ELSE 0
                END)
        ), agra_log_key AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Chave", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_key::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', alk.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Chave"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Chave"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_log_key alk
        )
 SELECT fina.data
   FROM fina;

v_log_detail_log_key_user_repository_project_total_legenda

CREATE OR REPLACE VIEW public.v_log_detail_log_key_user_repository_project_total_legenda AS
 WITH v_log_detail_log_key_user_repository_project_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
            ld.log_key,
                CASE
                    WHEN ld.log_user::text = ''::text THEN 'Anónimo'::character varying(100)
                    ELSE ld.log_user
                END AS log_user,
            ld.log_repository,
            ld.log_project
           FROM public.log_detail ld
          WHERE ld.log_key::text <> 'login'::text
          GROUP BY ld.log_key, ld.log_repository, ld.log_project, ld.log_user
        ), v_log_detail_log_key_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
            lak.log_key,
            'key'::text AS tipo
           FROM public.log_detail lak
          GROUP BY lak.log_key
        ), log_detail AS (
         SELECT t.aid,
            t.log_key,
            t.total,
            t.log_repository,
            t.log_project,
                CASE
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS marker,
                CASE
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_key::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_key::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_key::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_key::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#ff3377"'::text
                END AS line,
            concat(t.log_user, ' - ', t.log_key) AS text
           FROM v_log_detail_log_key_user_repository_project_total t
          ORDER BY (
                CASE
                    WHEN t.log_key::text = 'viewmap'::text THEN 1
                    WHEN t.log_key::text = 'popup'::text THEN 2
                    WHEN t.log_key::text = 'editionSaveFeature'::text THEN 3
                    WHEN t.log_key::text = 'print'::text THEN 4
                    WHEN t.log_key::text = 'editionDeleteFeature'::text THEN 5
                    ELSE 0
                END), t.log_repository
        ), conta_login AS (
         SELECT t.aid,
            t.log_key,
            t.total AS conta_login
           FROM v_log_detail_log_key_total t
          WHERE t.log_key::text = 'login'::text
        ), agra_viewmap AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Visualização de mapa", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
          WHERE l.log_key::text = 'viewmap'::text
        ), agra_popup AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Visualização de atributos", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
          WHERE l.log_key::text = 'popup'::text
        ), agra_editionsavefeature AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Edição de registos", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
          WHERE l.log_key::text = 'editionSaveFeature'::text
        ), agra_print AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Impressão", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
          WHERE l.log_key::text = 'print'::text
        ), agra_editiondeletefeature AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Registos apagados", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project::text, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
          WHERE l.log_key::text = 'editionDeleteFeature'::text
        ), junta AS (
         SELECT av.valores,
            ap.valores,
            aes.valores,
            apr.valores,
            aed.valores,
            c.aid,
            c.log_key,
            c.conta_login
           FROM agra_viewmap av,
            agra_popup ap,
            agra_editionsavefeature aes,
            agra_print apr,
            agra_editiondeletefeature aed,
            conta_login c
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', av.valores, ', ', ap.valores, ', ', aes.valores, ', ', apr.valores, ', ', aed.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Total login: ', c.conta_login, '"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Projecto"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_viewmap av,
            agra_popup ap,
            agra_editionsavefeature aes,
            agra_print apr,
            agra_editiondeletefeature aed,
            conta_login c
        )
 SELECT fina.data
   FROM fina;

v_log_detail_log_project_total_legenda

CREATE OR REPLACE VIEW public.v_log_detail_log_project_total_legenda AS
 WITH v_log_detail_log_project_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
            lap.log_project,
            'project'::text AS tipo
           FROM public.log_detail lap
          GROUP BY lap.log_project
        ), log_detail AS (
         SELECT t.aid,
            t.total,
                CASE
                    WHEN t.log_project::text IS NULL THEN 'Anónimo'::text
                    ELSE t.log_project::text
                END AS log_project,
                CASE
                    WHEN t.log_project::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_project::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_project::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_project::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_project::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_project::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS marker,
                CASE
                    WHEN t.log_project::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_project::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_project::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_project::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_project::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_project::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS line,
                CASE
                    WHEN t.log_project::text IS NULL THEN 'Anónimo'::text
                    ELSE t.log_project::text
                END AS text
           FROM v_log_detail_log_project_total t
          ORDER BY (
                CASE
                    WHEN t.log_project::text = 'viewmap'::text THEN 1
                    WHEN t.log_project::text = 'popup'::text THEN 2
                    WHEN t.log_project::text = 'editionSaveFeature'::text THEN 3
                    WHEN t.log_project::text = 'print'::text THEN 4
                    WHEN t.log_project::text = 'editionDeleteFeature'::text THEN 5
                    ELSE 0
                END)
        ), agra_log_project AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Projecto", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_project, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', alk.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Projecto"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Projecto"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_log_project alk
        )
 SELECT fina.data
   FROM fina;

v_log_detail_log_repository_total_legenda

CREATE OR REPLACE VIEW public.v_log_detail_log_repository_total_legenda AS
 WITH v_log_detail_log_repository_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
            lgr.log_repository,
            'repository'::text AS tipo
           FROM public.log_detail lgr
          GROUP BY lgr.log_repository
        ), log_detail AS (
         SELECT t.aid,
            t.total,
                CASE
                    WHEN t.log_repository::text IS NULL THEN 'Anónimo'::text
                    ELSE t.log_repository::text
                END AS log_repository,
                CASE
                    WHEN t.log_repository::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_repository::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_repository::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_repository::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_repository::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_repository::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS marker,
                CASE
                    WHEN t.log_repository::text = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_repository::text = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_repository::text = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_repository::text = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_repository::text = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_repository::text = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS line,
                CASE
                    WHEN t.log_repository::text IS NULL THEN 'Anónimo'::text
                    ELSE t.log_repository::text
                END AS text
           FROM v_log_detail_log_repository_total t
          ORDER BY (
                CASE
                    WHEN t.log_repository::text = 'viewmap'::text THEN 1
                    WHEN t.log_repository::text = 'popup'::text THEN 2
                    WHEN t.log_repository::text = 'editionSaveFeature'::text THEN 3
                    WHEN t.log_repository::text = 'print'::text THEN 4
                    WHEN t.log_repository::text = 'editionDeleteFeature'::text THEN 5
                    ELSE 0
                END)
        ), agra_log_repository AS (
         SELECT concat('{"customdata": ["nome_repositoryo"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Repositório", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_repository, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', alk.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Repositório"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Repositório"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_log_repository alk
        )
 SELECT fina.data
   FROM fina;

v_log_detail_log_user_total_legenda

CREATE OR REPLACE VIEW public.v_log_detail_log_user_total_legenda AS
 WITH v_log_detail_log_user_total AS (
         SELECT row_number() OVER () AS aid,
            count(*) AS total,
                CASE
                    WHEN ldu.log_user::text = ''::text THEN 'Anónimo'::text
                    ELSE ldu.log_user::text
                END AS log_user,
            'user'::text AS tipo
           FROM public.log_detail ldu
          GROUP BY ldu.log_user
        ), log_detail AS (
         SELECT t.aid,
            t.total,
                CASE
                    WHEN t.log_user IS NULL THEN 'Anónimo'::text
                    ELSE t.log_user
                END AS log_user,
                CASE
                    WHEN t.log_user = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_user = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_user = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_user = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_user = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_user = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS marker,
                CASE
                    WHEN t.log_user = 'editionDeleteFeature'::text THEN '"#cc0000"'::text
                    WHEN t.log_user = 'editionSaveFeature'::text THEN '"#1a75ff"'::text
                    WHEN t.log_user = 'popup'::text THEN '"#009900"'::text
                    WHEN t.log_user = 'print'::text THEN '"#e68a00"'::text
                    WHEN t.log_user = 'viewmap'::text THEN '"#cccc00"'::text
                    WHEN t.log_user = 'login'::text THEN '"#99994d"'::text
                    ELSE '"#a5d915"'::text
                END AS line,
                CASE
                    WHEN t.log_user IS NULL THEN 'Anónimo'::text
                    ELSE t.log_user
                END AS text
           FROM v_log_detail_log_user_total t
          WHERE t.total > 1
          ORDER BY t.log_user
        ), agra_log_user AS (
         SELECT concat('{"customdata": ["nome_projecto"], "ids": [', string_agg(l.aid::text, ', '::text), '], "marker": {"color": [', string_agg(l.marker, ', '::text), '], "colorbar": {"len": 0.8}, "colorscale": [[0.0, "rgb(255,255,255)"], [0.125, "rgb(240,240,240)"], [0.25, "rgb(217,217,217)"], [0.375, "rgb(189,189,189)"], [0.5, "rgb(150,150,150)"], [0.625, "rgb(115,115,115)"], [0.75, "rgb(82,82,82)"], [0.875, "rgb(37,37,37)"], [1.0, "rgb(0,0,0)"]], "line": {"color": [', string_agg(l.line, ', '::text), '], "width": 1.0}, "reversescale": false, "showscale": false}, "name": "Utilizador", "opacity": 1.0, "orientation": "v", "text": ["', string_agg(l.text, '", "'::text), '"], "textposition": "auto", "type": "bar", "x": ["', string_agg(l.log_user, '", "'::text), '"], "y": [', string_agg(l.total::text, ', '::text), ']}') AS valores
           FROM log_detail l
        ), fina AS (
         SELECT concat('Plotly.newPlot(''da5d5f96-85e6-4cbe-8251-7bfa4beb5ebe'', [', alk.valores, '], {"barmode": "stack", "legend": {"orientation": "h"}, "paper_bgcolor": "rgb(255,255,255)", "plot_bgcolor": "rgb(255,255,255)", "showlegend": true, "template": {"data": {"bar": [{"error_x": {"color": "#2a3f5f"}, "error_y": {"color": "#2a3f5f"}, "marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "bar"}], "barpolar": [{"marker": {"line": {"color": "#E5ECF6", "width": 0.5}}, "type": "barpolar"}], "carpet": [{"aaxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "baxis": {"endlinecolor": "#2a3f5f", "gridcolor": "white", "linecolor": "white", "minorgridcolor": "white", "startlinecolor": "#2a3f5f"}, "type": "carpet"}], "choropleth": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "choropleth"}], "contour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "contour"}], "contourcarpet": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "contourcarpet"}], "heatmap": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmap"}], "heatmapgl": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "heatmapgl"}], "histogram": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "histogram"}], "histogram2d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2d"}], "histogram2dcontour": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "histogram2dcontour"}], "mesh3d": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "type": "mesh3d"}], "parcoords": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "parcoords"}], "pie": [{"automargin": true, "type": "pie"}], "scatter": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter"}], "scatter3d": [{"line": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatter3d"}], "scattercarpet": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattercarpet"}], "scattergeo": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergeo"}], "scattergl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattergl"}], "scattermapbox": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scattermapbox"}], "scatterpolar": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolar"}], "scatterpolargl": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterpolargl"}], "scatterternary": [{"marker": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "type": "scatterternary"}], "surface": [{"colorbar": {"outlinewidth": 0, "ticks": ""}, "colorscale": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "type": "surface"}], "table": [{"cells": {"fill": {"color": "#EBF0F8"}, "line": {"color": "white"}}, "header": {"fill": {"color": "#C8D4E3"}, "line": {"color": "white"}}, "type": "table"}]}, "layout": {"annotationdefaults": {"arrowcolor": "#2a3f5f", "arrowhead": 0, "arrowwidth": 1}, "coloraxis": {"colorbar": {"outlinewidth": 0, "ticks": ""}}, "colorscale": {"diverging": [[0, "#8e0152"], [0.1, "#c51b7d"], [0.2, "#de77ae"], [0.3, "#f1b6da"], [0.4, "#fde0ef"], [0.5, "#f7f7f7"], [0.6, "#e6f5d0"], [0.7, "#b8e186"], [0.8, "#7fbc41"], [0.9, "#4d9221"], [1, "#276419"]], "sequential": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]], "sequentialminus": [[0.0, "#0d0887"], [0.1111111111111111, "#46039f"], [0.2222222222222222, "#7201a8"], [0.3333333333333333, "#9c179e"], [0.4444444444444444, "#bd3786"], [0.5555555555555556, "#d8576b"], [0.6666666666666666, "#ed7953"], [0.7777777777777778, "#fb9f3a"], [0.8888888888888888, "#fdca26"], [1.0, "#f0f921"]]}, "colorway": ["#636efa", "#EF553B", "#00cc96", "#ab63fa", "#FFA15A", "#19d3f3", "#FF6692", "#B6E880", "#FF97FF", "#FECB52"], "font": {"color": "#2a3f5f"}, "geo": {"bgcolor": "white", "lakecolor": "white", "landcolor": "#E5ECF6", "showlakes": true, "showland": true, "subunitcolor": "white"}, "hoverlabel": {"align": "left"}, "hovermode": "closest", "mapbox": {"style": "light"}, "paper_bgcolor": "white", "plot_bgcolor": "#E5ECF6", "polar": {"angularaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "radialaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "scene": {"xaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "yaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}, "zaxis": {"backgroundcolor": "#E5ECF6", "gridcolor": "white", "gridwidth": 2, "linecolor": "white", "showbackground": true, "ticks": "", "zerolinecolor": "white"}}, "shapedefaults": {"line": {"color": "#2a3f5f"}}, "ternary": {"aaxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "baxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}, "bgcolor": "#E5ECF6", "caxis": {"gridcolor": "white", "linecolor": "white", "ticks": ""}}, "title": {"x": 0.05}, "xaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}, "yaxis": {"automargin": true, "gridcolor": "white", "linecolor": "white", "ticks": "", "title": {"standoff": 15}, "zerolinecolor": "white", "zerolinewidth": 2}}}, "title": {"text": "Estatísticas Geo Portal - ', now()::date, ' | Utilizador"}, "xaxis": {"gridcolor": "#bdbfc0","title": {"text": "Utilizador"}}, "yaxis": {"gridcolor": "#bdbfc0","title": {"text": "Contagem"}, "type": "linear"}}, {"scrollZoom": true, "editable": true, "responsive": true})') AS data
           FROM agra_log_user alk
        )
 SELECT fina.data
   FROM fina;

It would be interesting to do a configuration approach similar to the one configured in the .cfg file of lizmap when we have data for dataviz, but I don't know how to do it.

I apologize for the long answer.

I hope it helps in something thanks.

[gioman]

I hope it helps in something

@sergiuspinheiro really great work! it would be even better if you could make you code re-usable in a general way and submit it as a pull request!

[sergiuspinheiro]

@gioman I hope I made the pull request correctly. https://github.com/3liz/lizmap-web-client/pull/2713/commits/d08536f87bb106051be80bc1e88c03372accf458

thanks

[mdouchin]

Thanks a lot @sergiuspinheiro for your contribution. Let's continue the discussion inside your PR.

I think we will need some time and work to integrate your work inside LWC code base, and help you to better use the MVC (model view controller) logic available in the Jelix PHP framework. Please be patient in the meantime, the code review will be a bit slow and we will probably ask you for some major changes in the organization of your PR code.

[sergiuspinheiro]

Thanks @mdouchin, I'll read the Jelix documentation, to get an understanding of the working process.