Chart component / link parameter / custom handlebar

[SebastiendOrnano]

Hello,

I am still working on the COOP template an app for project management.

I am using the chart component to visualize the timeline of the workpackages of a project. I want to add a link so I can jump from the bar in the chart to the edit form of a workpackage (as in the map component, you can jump from the map to a form from the location point)). This feature is not (yet ?) included in the standard handlebar. So I tried to write a custom chart handlebar but I am not a developer, and I am stuck in how to write correctly the handlebar:

I introduced a link parameter in the sql file :

`SELECT 
    'chart'            AS component,
    'Workpackage Timeline' AS title,
    'rangeBar'         AS type,
    TRUE               AS time,
    'green'            AS color,
    'blue'             AS color,
    $date_start_mini   AS xmin,
    $date_end_max      AS xmax;

SELECT 
    w.workpackage_name                       AS series,
    w.workpackage_name  ||'    Planififié'    AS label,
   '/e_project_workpackage/workpackage_main_edit_4.sql?workpackage_id='||w.workpackage_id as link,
    w.workpackage_date_start_scheduled        AS value,
    w.workpackage_date_end_scheduled          AS value
  FROM project_workpackage AS w
  left join (SELECT project_id FROM project) AS p
  on w.project_id=p.project_id
  WHERE p.project_id=$project_id::INTEGER
  ORDER BY w.workpackage_date_start_scheduled ASC;`

Then I tried to introduce a <a href> tag in the custom handlebar in the point description :

"points": [ {{~#each_row~}} {{~#if (gt @row_index 0)}},{{/if~}} [ {{~#if link~}} <h3><a href="{{link}}"> {{~ stringify (default series (default ../title "")) ~}}</a></h3> {{~else~}} {{~ stringify (default series (default ../title "")) ~}} {{~/if~}}, {{~ stringify (default x label) ~}}, {{~ stringify (default y value) ~}} {{~#if z}}, {{~ stringify z ~}} {{~/if~}} ] {{~/each_row~}} ]

The link is created but no active 👍
"points": [[<h3><a href="[/e_project_workpackage/workpackage_main_edit_4.sql?workpackage_id&#x3D;21](view-source:http://localhost:8080/e_project_workpackage/workpackage_main_edit_4.sql?workpackage_id=21)">&quot;001_SerreGeode&quot;</a></h3>,&quot;001_SerreGeode Planififié&quot;,[&quot;2021-12-30&quot;,&quot;2022-02-28&quot;]]]

So, where is my mistake ?

Thanks in advance for any answer !

[SebastiendOrnano]

I answer to myself since I found a solution. As I am not a developer and I am not able to code a script, I organized a battle between perplexity and copilot. Perplexity was the first to produce a beta script but there was many comments and versions before a final version which was not yet working !

As most of errors are coming from wrong data formats I prompted on the fact that I was using apexchart.js to produce a rangeBar of dates for a timeline visualization and not a classic range of numbers. So my new friends G & C provide me with a patch to convert date to numbers and it works !

I don't yet know if the following handlebars template is working for every type of chart. If not the script should be adjust to the "type" of chart. But it is far above my competences. So following the sql file and the final chart.handlebars template (an idea : a repository to share custom handlebars !) and a screenshot

SELECT 'dynamic' AS component, sqlpage.run_sql('a_shells/shell_4.sql') AS properties;

SET project_name = SELECT project_name FROM project WHERE project_id =$project_id::INTEGER 

SELECT 
    'breadcrumb' AS component;
SELECT 
    'Home' AS title,
    '/'    AS link;
SELECT 
    'Gestion Superviseur'         AS title,
    '/a_panels/panel_supervisor_4.sql' AS link;
 SELECT 
    'Retour à la liste des projets'            AS title,
    '/e_project/project_main_display_4.sql'     AS link;
 SELECT 
    'Retour au hub du projet'            AS title,
    '/e_project/project_hub_display_4.sql?project_id='||$project_id     AS link;   

SELECT 
    'form'   AS component,
    '' AS validate;
SELECT
    'project_id' AS name,
    TRUE AS readonly,
    'Id proj.' AS label,
    1 AS width,
   $project_id AS value;
SELECT
    'project_name' AS name,
    TRUE AS readonly,
    'Nom du projet' AS label,
    5 AS width,
    $project_name AS value;

SET date_start_mini =
SELECT MIN(w.workpackage_date_start_scheduled) FROM project_workpackage AS w
LEFT JOIN (SELECT project_id FROM project) AS p 
on p.project_id=w.project_id
WHERE p.project_id=$project_id::INTEGER;

SET date_end_max =
SELECT 
    CASE
        WHEN MAX(w.workpackage_date_end_actual) IS NOT NULL AND MAX(w.workpackage_date_end_actual) > MAX(w.workpackage_date_end_scheduled)
        THEN MAX(w.workpackage_date_end_actual) 
        ELSE MAX(w.workpackage_date_end_scheduled) 
    END
FROM project_workpackage AS w
LEFT JOIN (SELECT project_id FROM project) AS p 
ON p.project_id = w.project_id
WHERE p.project_id = $project_id::INTEGER;

SELECT 
    'chart'            AS component,
    'Workpackage Timeline' AS title,
    'rangeBar'         AS type,
    TRUE               AS time,
    TRUE               as horizontal,
    'green'            AS color,
    'blue'             AS color,
    $date_start_mini   AS xmin,
    $date_end_max      AS xmax;

SELECT 
    w.workpackage_name                       AS series,
    w.workpackage_name  ||'    Planififié'    AS label,
   'http://localhost:8080/e_project_workpackage/workpackage_main_edit_4.sql?workpackage_id='||w.workpackage_id as link,
    w.workpackage_date_start_scheduled        AS value,
    w.workpackage_date_end_scheduled          AS value
  FROM project_workpackage AS w
  left join (SELECT project_id FROM project) AS p
  on w.project_id=p.project_id
  WHERE p.project_id=$project_id::INTEGER
  ORDER BY w.workpackage_date_start_scheduled ASC;

SELECT 
    w.workpackage_name                       AS series,
    w.workpackage_name  ||'    Réel'         AS label,
   'http://localhost:8080/e_project_workpackage/workpackage_main_edit_4.sql?workpackage_id='||w.workpackage_id as link,
    w.workpackage_date_start_actual      AS value,
    w.workpackage_date_end_actual         AS value
FROM project_workpackage AS w
left join (SELECT project_id FROM project) AS p
on w.project_id=p.project_id
WHERE p.project_id=$project_id::INTEGER AND w.workpackage_date_start_actual IS NOT NULL AND w.workpackage_date_end_actual IS NOT NULL;

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>Minimal ApexCharts rangeBar with clickable points</title>
  <meta name="viewport" content="width=device-width,initial-scale=1" />
  <!-- ApexCharts from CDN -->
  <script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
  <style>
    body { font-family: system-ui, -apple-system, "Segoe UI", Roboto, "Helvetica Neue", Arial; padding: 20px; }
    .card { border: 1px solid #ddd; border-radius: 6px; padding: 12px; max-width: 900px; }
    .chart { height: 360px; }
    .spinner-wrapper { position: absolute; left: 50%; top: 50%; transform: translate(-50%, -50%); }
  </style>
</head>
<body>
  <div id="rangebar-1" class="card" data-pre-init="chart">
    <h3>Project schedule (click a bar to open its link)</h3>

    <div class="chart">
      <div id="chart-canvas"></div>
      <div class="spinner-wrapper" id="spinner">
        Loading...
      </div>

      <!-- Inline JSON data. The last item in each point row is the link (or null). -->
<data hidden>



{
  "type": {{stringify type}},
  "time": {{stringify time}},
  "labels": {{stringify labels}},
  "marker": {{stringify marker}},
  "xtitle": {{stringify xtitle}},
  "ytitle": {{stringify ytitle}},
  "ztitle": {{stringify ztitle}},
  "xticks": {{stringify xticks}},
  "yticks": {{stringify yticks}},
  "ystep": {{stringify ystep}},
  "xmin": {{stringify xmin}},
  "ymin": {{stringify ymin}},
  "xmax": {{stringify xmax}},
  "ymax": {{stringify ymax}},
  "toolbar": {{stringify toolbar}},
  "logarithmic": {{stringify logarithmic}},
  "horizontal": {{stringify horizontal}},
  "stacked": {{stringify stacked}},
  "height": {{stringify (default height 250)}},
  "colors": {{stringify (to_array color)}},
  "points":
     [
      {{~#each_row~}}
        {{~#if (gt @row_index 0)}},{{/if~}}
        [
            {{~ stringify (default series (default ../title "")) ~}},
            {{~ stringify (default x label) ~}},
            {{~ stringify (default y value) ~}}
            {{~#if z}}, {{~ stringify z ~}} {{~/if~}}
            {{~#if link}}, {{~ stringify link ~}} {{~/if~}}
        ]
    {{~/each_row~}}
    ]
        }
      </data>
    </div>
  </div>

<script>
(function(){
  const container = document.querySelector('[data-pre-init="chart"]');
  if (!container) return;

  const dataEl = container.querySelector("data");
  if (!dataEl) return;

  // decode HTML entities if present
  let raw = dataEl.textContent || "";
  if (raw.includes("&")) {
    const txt = document.createElement("textarea");
    txt.innerHTML = raw;
    raw = txt.value;
  }

  let chartData;
  try {
    chartData = JSON.parse(raw);
  } catch (e) {
    console.error("Failed to parse chart data JSON:", e, raw);
    return;
  }

  const rawPoints = Array.isArray(chartData.points) ? chartData.points : [];
  const looksLikeLink = (val) => typeof val === "string" && (/(^https?:\/\/)/i.test(val.trim()) || /^\//.test(val.trim()) || /^mailto:/i.test(val.trim()));

  const seriesNames = [];
  const seriesMap = {}; // { seriesName: { dataByX: {}, linksByX: {} } }
  const categories = [];

  const toMs = (d) => {
    if (typeof d === "number") return d;
    if (!d) return null;
    if (/^\d+$/.test(String(d).trim())) return Number(d);
    const s = String(d).trim();
    const dt = new Date(s.includes("T") ? s : `${s}T00:00:00`);
    if (isNaN(dt)) return null;
    return dt.getTime();
  };

  // Accept rows: [seriesName, x, y, (maybe other), linkIsLast]
  rawPoints.forEach(pt => {
    if (!Array.isArray(pt) || pt.length < 3) return;
    const seriesName = pt[0];
    const x = pt[1];
    const yRaw = pt[2];
    const linkRaw = pt[pt.length - 1];

    let y;
    if (Array.isArray(yRaw)) {
      const conv = yRaw.map(item => toMs(item));
      y = conv.every(v => v !== null) ? conv : null;
    } else {
      const m = toMs(yRaw);
      y = m !== null ? m : null;
    }

    const link = looksLikeLink(linkRaw) ? String(linkRaw).trim() : null;

    if (!categories.includes(x)) categories.push(x);
    if (!seriesNames.includes(seriesName)) seriesNames.push(seriesName);

    if (!seriesMap[seriesName]) seriesMap[seriesName] = { dataByX: {}, linksByX: {} };
    seriesMap[seriesName].dataByX[x] = y;
    if (link) seriesMap[seriesName].linksByX[x] = link;
  });

  // Build ApexCharts series format
  const series = seriesNames.map(name => ({
    name,
    data: categories.map(cat => {
      const y = seriesMap[name].dataByX?.[cat] ?? null;
      return { x: cat, y: y };
    })
  }));

  // Link lookup by "seriesIndex:dataPointIndex"
  const linksLookup = {};
  series.forEach((s, sIdx) => {
    s.data.forEach((dObj, i) => {
      const linkForPoint = seriesMap[s.name].linksByX?.[dObj.x];
      if (linkForPoint) linksLookup[`${sIdx}:${i}`] = linkForPoint;
    });
  });

  const options = {
    chart: {
      type: chartData.type || 'rangeBar',
      height: chartData.height || 360,
      toolbar: { show: false },
      events: {
        dataPointSelection: function(event, chartContext, config) {
          const key = `${config.seriesIndex}:${config.dataPointIndex}`;
          const link = linksLookup[key];
          if (!link) return;
          try {
            window.open(link, "_blank", "noopener");
          } catch (err) {
            window.location.href = link;
          }
        }
      }
    },
    plotOptions: {
  bar: {
    horizontal: chartData.horizontal || false,
    dataLabels: { position: "top" }
  }
}
    ,
    series: series,
xaxis: {
    type: "datetime",
    labels: {
      format: "dd MMM yyyy"
    }
  },
  tooltip: {
    x: {
      show: true,
      formatter: function(value) {
        const d = new Date(value);
        return d.toLocaleDateString(undefined, {
          year: "numeric",
          month: "short",
          day: "numeric"
        });
      }
    }
  },
    colors: chartData.colors || undefined
  };

  const chartContainer = document.getElementById("chart-canvas");
  const spinner = document.getElementById("spinner");
  const chart = new ApexCharts(chartContainer, options);
  chart.render()
    .then(() => { if (spinner) spinner.remove(); })
    .catch(err => console.error("ApexCharts render error:", err));
})();
</script>
</body>
</html>