SQL error on validation of factory order when workstation with type not "STT" is added

[splohmer]

Description In case an workstation (where the type is not "STT") is added to an factory order and order is validated, then an SQL error var_dump is shown: Unknown column 'aex.fk_workstation' in 'where clause'.

Affected versions:

• issue found on workstation module version 2.0.2

Steps to reproduce

• create a new factory order
• add a product to be created
• add a workstation where type is not "STT" e.g. human + machine
• save and validate the factory order

Current behaviour var_dump of sql error is shown

Expected behaviour factory order is validated, no errors are shown

Additional information

• var_dump shown on screen: object(DoliDBMysqli)#31 (19) { ["db"]=> object(mysqli)#32 (18) { ["affected_rows"]=> int(-1) ["client_info"]=> string(13) "mysqlnd 7.4.3" ["client_version"]=> int(70403) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(1054) ["error"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" ["error_list"]=> array(1) { [0]=> array(3) { ["errno"]=> int(1054) ["sqlstate"]=> string(5) "42S22" ["error"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" } } ["field_count"]=> int(7) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(38) "5.5.5-10.3.34-MariaDB-0ubuntu0.20.04.1" ["server_version"]=> int(100334) ["sqlstate"]=> string(5) "42S22" ["protocol_version"]=> int(10) ["thread_id"]=> int(5462) ["warning_count"]=> int(0) } ["type"]=> string(6) "mysqli" ["_results":"DoliDBMysqli":private]=> bool(false) ["forcecharset"]=> string(4) "utf8" ["forcecollate"]=> string(15) "utf8_unicode_ci" ["connected"]=> bool(true) ["database_selected"]=> bool(true) ["database_name"]=> string(12) "dolibarrTest" ["database_user"]=> string(8) "dolibarr" ["database_host"]=> string(9) "localhost" ["database_port"]=> string(4) "3306" ["transaction_opened"]=> int(0) ["lastquery"]=> string(310) "SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)" ["lastqueryerror"]=> string(310) "SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)" ["lasterror"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" ["lasterrno"]=> string(20) "DB_ERROR_NOSUCHFIELD" ["ok"]=> bool(true) ["error"]=> NULL ["_results":"DoliDB":private]=> NULL }

• sql query tried to execute: SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)

• where does the error occurs in code: I drilled the error down to workstation.class.php, function getUsedDayCapacityAgenda in the line $sql.= ' AND (aex.fk_workstation = '.(int)$this->id.' OR aex.fk_workstation = 0) ';

Currently I am not sure how to fix it as I cannot get the intention of the sql ammendment for the capacity planning. Therefore I cannot provide an pull request. Therefore I hope this can be fixed from your side.

[splohmer]

Description Dans le cas où un poste de travail (dont le type n'est pas "STT") est ajouté à une commande d'usine et que la commande est validée, une erreur SQL var_dump est affichée : Unknown column 'aex.fk_workstation' in 'where clause'.

Versions affectées :

• problème trouvé sur le module workstation version 2.0.2

Étapes à suivre pour reproduire le problème

• créer une nouvelle commande d'usine
• ajouter un produit à créer
• ajouter un poste de travail dont le type n'est pas "STT" ex : humain + machine
• sauvegarder et valider l'ordre de fabrication

Comportement actuel var_dump de l'erreur sql est affiché

Comportement attendu l'ordre d'usine est validé, aucune erreur n'est affichée

Informations complémentaires

• var_dump affiché à l'écran: object(DoliDBMysqli)#31 (19) { ["db"]=> object(mysqli)#32 (18) { ["affected_rows"]=> int(-1) ["client_info"]=> string(13) "mysqlnd 7.4.3" ["client_version"]=> int(70403) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(1054) ["error"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" ["error_list"]=> array(1) { [0]=> array(3) { ["errno"]=> int(1054) ["sqlstate"]=> string(5) "42S22" ["error"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" } } ["field_count"]=> int(7) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(38) "5.5.5-10.3.34-MariaDB-0ubuntu0.20.04.1" ["server_version"]=> int(100334) ["sqlstate"]=> string(5) "42S22" ["protocol_version"]=> int(10) ["thread_id"]=> int(5462) ["warning_count"]=> int(0) } ["type"]=> string(6) "mysqli" ["_results":"DoliDBMysqli":private]=> bool(false) ["forcecharset"]=> string(4) "utf8" ["forcecollate"]=> string(15) "utf8_unicode_ci" ["connected"]=> bool(true) ["database_selected"]=> bool(true) ["database_name"]=> string(12) "dolibarrTest" ["database_user"]=> string(8) "dolibarr" ["database_host"]=> string(9) "localhost" ["database_port"]=> string(4) "3306" ["transaction_opened"]=> int(0) ["lastquery"]=> string(310) "SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)" ["lastqueryerror"]=> string(310) "SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)" ["lasterror"]=> string(53) "Unknown column 'aex.fk_workstation' in 'where clause'" ["lasterrno"]=> string(20) "DB_ERROR_NOSUCHFIELD" ["ok"]=> bool(true) ["error"]=> NULL ["_results":"DoliDB":private]=> NULL }

• La requête sql a essayé de s'exécuter : SELECT a.id, aex.needed_ressource, a.datep AS dateo , a.datep2 AS datee FROM llx_actioncomm a LEFT JOIN llx_actioncomm_extrafields aex ON (aex.fk_object=a.id) WHERE a.entity IN (1) AND a.fk_action=1053012 AND '2022-09-22' BETWEEN a.datep AND a.datep2 AND (aex.fk_workstation = 1 OR aex.fk_workstation = 0)

• où se trouve l'erreur dans le code : J'ai trouvé l'erreur dans workstation.class.php, fonction getUsedDayCapacityAgenda dans la ligne $sql.= ' AND (aex.fk_workstation = '.(int)$this->id.'. OR aex.fk_workstation = 0) ' ;

Actuellement, je ne sais pas comment le résoudre car je ne peux pas obtenir l'intention de l'amendement sql pour la planification de la capacité. Par conséquent, je ne peux pas fournir une demande d'extraction. Par conséquent, j'espère que cela peut être corrigé de votre côté.

[splohmer]

Hi,

I checked it again and it looks like I oversaw the comment in the code: needed_ressource is created by gantt module. Currently I see 2 solutions depending if the workstation module is intended to be used also without gantt module or not:

1. If the module should be usable without gantt module: The extrafield could be added also to the workflow module. The activated module which is first creates it. In case it is already created, then the addExtraField function will return "not ok", but the result is not used in the gantt module.
2. If the module should be usable only with gantt module: In this case I think it should be defined as dependency in the module description. Then it will be automatically activated if the workstation module is activated.

Personally I prefer the first solution as it keeps the modules independently. I will create a pull request for that. Let me know if you prefer the other solution.

[splohmer]

Pull request created: https://github.com/ATM-Consulting/dolibarr_module_workstation/pull/38

[atm-florianm]

As answered on your PR (I summarize it here to make it easier for other contributors to follow), I think more detail is required to reproduce the bug and to fix it appropriately: currently, workstationatm is designed to work with or without Gantt (which we don't sell or recommend anymore). The SQL query in your report is run within an if block causing it to be executed only if Gantt is enabled, which is why we couldn't reproduce the problem. I think the error might stem from Gantt not being completely disabled as far as the database goes. If the issue reoccurs, we will improve our response time to ensure we get all the details needed to troubleshoot / fix it.

[splohmer]

Hi @atm-florianm, sorry to contradict here, but the if statement is executed only if gantt is disabled. See code line from the PR: if(empty($conf->gantt->enabled)) This checks if the variable is empty and this is the case if the module is disabled.

[atm-florianm]

@splohmer There is a misunderstanding here: I was not referring to the line from the PR but to this line in the Workstation class, which conditions the execution of the SQL query. The query that caused the error from your issue runs only if Gantt is enabled.