Gantt Chart for Multiple Projects

[WABez]

I'm testing the Gantt chart, but with multiple projects, but I have an issue which I'm battling with.
I'm using PHPMaker 2023.13 and the DB is PostgreSQL 13.

The Gantt is working just fine with a single project. So everything is working up that point.

I have added a dropdown selector, which I populate dynamically with the different projects. So this part is all good. I then have an event handler function that pick the selected value (from the dropdown) which I want to use to get the selected project data/detail. This is where I'm running into issues when I modify the query with a WHERE clause.

So the standard query for adding rows to the Gantt, as shown below is working fine:
data.addRows(<?= ExecuteJson("SELECT TaskID, TaskName, ResourceID, Start, End, Duration, PercentComplete, Dependencies FROM tasks", ["header" => false, "array" => true, "convertdate" => true]); ?>);

I have attempted the following:
SELECT TaskID, TaskName, ResourceID, Start, End, Duration, PercentComplete, Dependencies FROM tasks WHERE projectid = 'myvariable'
but then I get the following error: Undefined constant "PHPMaker2023\myproject\myvariable", however myvariable is declared and it contains the relevant projectid for the WHERE clause.

If I hard code the WHERE clause, it is working fine, i.e.:
SELECT TaskID, TaskName, ResourceID, Start, End, Duration, PercentComplete, Dependencies FROM tasks **WHERE projectid = 1**

I can change the WHERE projectid = ? to any other (valid) value, and it load that project perfectly. I just can't get the query to work with the variable.

Any ideas would be welcome.

[mobhar]

WABez wrote:

SELECT TaskID, TaskName, ResourceID, Start, End, Duration, PercentComplete, Dependencies FROM tasks WHERE projectid = 'myvariable'

Where did you put this SQL?

[arbei]

PHP variables begins with $, if you use myvariable, it is considered as a constant. If you don't use it with its namespace, i.e. NamespaceOfYourVariable\myvariable, PHP will assume it is under the current namespace (PHPMaker2023\myproject) so it is undefined. You need to check where you defined your constant and include its namespace when using it.

[WABez]

This is in Client Scripts > Table Specific > Startup Script as per the PHPMaker documentation - Startup Script (search for "ExecuteJson") for Gantt Chart). Everything is working just fine, up to the WHERE clause when I want to use the variable.

The same code is in the repository, and the code is fine (that is what I have used to test with). I have now added in a dropdown list (of projects) to select a specific project and populate the Gantt accordingly. It is really just this variable that is causing issues. If I hard code the "myvariable", it works just fine (for testing purposes, but I need it dynamically).

The variable is declared within the scope/block of the function where it is used. I can "console.log" it and use it elsewhere in the function, and it is fine. Maybe I'm missing something w.r.t. the ExecuteJson function.

[arbei]

WABez wrote:

This is in Client Scripts > Table Specific > Startup Script...
The variable is declared within the scope/block of the function where it is used. I can console.log it...

That means your variable is JavaScript variable on the client side, you cannot use it on the server side with PHP (inside <? .. ?>) unless you send the variable from client side to server side. You better do the other way round, declare it on the server side and then pass to client side, see Some Global Functions -> SetClientVar($name, $value).

[WABez]

I just realized my rookie mistake mixing client side and server side code. Exactly what you said. Apologies for being a bit late to acknowledge my rookie mistake.

[Cat]

hello Can you share how you did it? I can't find the solution to filter multiple projects with Gantt:

loadjs('https://www.gstatic.com/charts/loader.js', function() {
	google.charts.load('current', {'packages':['gantt']});
	google.charts.setOnLoadCallback(function() {
		// Add <div> for chart
		$(".report-summary").append('<div id="chart_div"></div>');
		// Create data table
		var data = new google.visualization.DataTable();
		// Add columns, see: https://developers.google.com/chart/interactive/docs/gallery/ganttchart#data-format)
		
        data.addColumn('string', 'Task ID');
		data.addColumn('string', 'Task Name');
		data.addColumn('string', 'Resource');
		data.addColumn('date', 'Start Date');
		data.addColumn('date', 'End Date');
		data.addColumn('number', 'Duration');
		data.addColumn('number', 'Percent Complete');
		data.addColumn('string', 'Dependencies');
        data.addColumn('string', 'Name Project');

		
        /**
		 * Add rows by ExecuteJson()
		 * NOTE: The fields must be in the same order as above columns. Change the table name and field names (if necessary) in the SQL.
		*/
        
		data.addRows(<?= ExecuteJson("SELECT TaskID, TaskName, ResourceID, Start, End, Duration, PercentComplete, Dependencies, NameProject FROM project_tareas", ["header" => FALSE, "array" => TRUE, "convertdate" => TRUE, "datatypes" => [3 => "date", 4 => "date", 5 => "number", 6 => "number"]]); ?>);
		// Chart options, see: https://developers.google.com/chart/interactive/docs/gallery/ganttchart#configuration-options
		var options = {
        height: 750
      };
		// Create chart
		var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
		// Draw chart
		chart.draw(data, options);
	});
});

[arbei]

You did not write any code for multiple projects. The original post at least tried to add to WHERE clause like WHERE projectid = xxx. You need to try add yours.