[GLPI 10.0.2] [FormCreator 2.13.0-rc] Dashboard validation

[LuizRicarte]

Hello,

I have some business rules that, according to the information collected in the form, send approval to up to 4 different approvers.

In GLPI 9.5.6 with the formcreator 2.12.4 plugin, in the validate dashboard all the so-called validation pending normally appeared.

I upgraded to GLPI 10.0.2 and with formcreator 2.13.0-rc, but it is not showing all pending validation calls when I click on the validation option.

I noticed that in GLPI 10.0.2, when the ticket is sent for manual approval, it has a status of awaiting approval, when it is sent by business rules, this status is not assigned.

Does anyone have an idea what it could be?

Thanks

[btry]

Hi

An issue has been fixed last week in GLPI, related to ticket status: You should apply this patch https://github.com/glpi-project/glpi/pull/12170 .

Once the fix applied, use your form to create a new ticket with pending validation and see if it is taken into account in the counters.

[LuizRicarte]

Hello,

I made the change in the CommonITILObject.php file, opened a new ticket, but it continues to appear in Assigned, after I approve this ticket, it appears in Validate. I couldn't find where is the search that the dashboard performs to check if it's an error in Select.

[btry]

I think you should first check if the status is correct in the database first.

A ticket is automatically assigned to you when you have right to be assigned to a ticket and your preferences have auto assignment to yourself enabled. This is a recurrent cause of false bug report. Check and tell if you are in such case.

Please tell how many tickets are generated by your form answer (0, 1 or several). Then

• show the status of all generated tickets in the table glpi_tickets.
• show the status of the issue in glpi_plugin_formcreator_issues.

If the form answer generated one ticket, you will find the matching issue by filtering with itemtype='Ticket' and items_id=42 (where 42 is the id of your ticket, to adapt to your case).

If the form answer generated 0 or several tickets you have to search the issue's row by filtering with itemtype='PluginFormcreatorFormAnswer' and items_id=84 (84 is the ID of the form answer. You can find its ID in the list of formanswers in the administration part of the form, tab "Form answers").

[LuizRicarte]

@btry Can you check if I understand correctly?

[btry]

It looks good : both statuses are the same : 2 and I see that global validation is 2 as well.

Acordying to these statuses, the issue should be included in the "waiting" counter. When you enter the service catalog, the counters filters the issues to ignore any row where the current logged in user is not involved. On other words you must be the author, requester observer or assigned actor of the generated ticket. Please show the login and ID of all users who appears as actors of the generated ticket (do not forget the author). Show also the ID of the current user when you display the counters.

[LuizRicarte]

@btry,

I opened the ticket for test. My ID: 51 Validator ID: 59 Neither for me nor for the validator the ticker appears on the validating dashboard.

Users:

TicketsValidations

[btry]

• Enable debug mode in your session
• switch to the profile from which you access the service catalog; click on the "waiting counter". You should see the list of waiing issues. It is empty in your case.
• Click the red bug in the top right corner, a panel will appear on the bottom of the page
• click the SQL requests tab to view all SQL requests used for this page

Use Ctrl+F to find the following string SELECT DISTINCT `glpi_plugin_formcreator_issues`.`id` AS id You should find only one result, which is the SQL query to find the issues to show (it is nearly the same for the counter itself).

Share here the query you have.

When I'm authenticated as GLPI, the request looks like this in my instance. Notice the WHERE clause containing many filters on user with the ID 2 (aka glpi). I expect you have 51 and 59 in your own query.

SELECT DISTINCT `glpi_plugin_formcreator_issues`.`id` AS id, 'glpi' AS currentuser, `glpi_plugin_formcreator_issues`.`itemtype`, `glpi_plugin_formcreator_issues`.`entities_id`, `glpi_plugin_formcreator_issues`.`is_recursive`, GROUP_CONCAT(DISTINCT CONCAT(`glpi_plugin_formcreator_issues`.`name`, '$#$' , `glpi_plugin_formcreator_issues`.`id`)
ORDER BY `glpi_plugin_formcreator_issues`.`id` SEPARATOR '$$##$$') AS `ITEM_PluginFormcreatorIssue_1`, IFNULL(GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_plugin_formcreator_issues`.`display_id`, '__NULL__'), '$#$', `glpi_plugin_formcreator_issues`.`id`)
ORDER BY `glpi_plugin_formcreator_issues`.`id` SEPARATOR '$$##$$'), '__NULL__$#$') AS `ITEM_PluginFormcreatorIssue_1_display_id`, `glpi_plugin_formcreator_issues`.`display_id` AS `ITEM_PluginFormcreatorIssue_2`, `glpi_plugin_formcreator_issues`.`status` AS `ITEM_PluginFormcreatorIssue_4`, `glpi_plugin_formcreator_issues`.`date_creation` AS `ITEM_PluginFormcreatorIssue_5`, `glpi_plugin_formcreator_issues`.`date_mod` AS `ITEM_PluginFormcreatorIssue_6`, `glpi_entities`.`completename` AS `ITEM_PluginFormcreatorIssue_7`, `glpi_users_requester_id`.`name` AS `ITEM_PluginFormcreatorIssue_8`, `glpi_users_requester_id`.`realname` AS `ITEM_PluginFormcreatorIssue_8_realname`, `glpi_users_requester_id`.`id` AS `ITEM_PluginFormcreatorIssue_8_id`, `glpi_users_requester_id`.`firstname` AS `ITEM_PluginFormcreatorIssue_8_firstname`
FROM `glpi_plugin_formcreator_issues`
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id` AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id` AND `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3' )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id` AND `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3' )
LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id` AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )
LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`tickets_id` AND `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' )
LEFT JOIN `glpi_ticketvalidations` ON (`glpi_plugin_formcreator_issues`.`itemtype` = "Ticket" AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_ticketvalidations`.`tickets_id` )
LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_validator` ON (`glpi_plugin_formcreator_issues`.`groups_id_validator` = `glpi_groups_groups_id_validator`.`id` )
LEFT JOIN `glpi_tickets` ON (`glpi_tickets`.`id` = `glpi_plugin_formcreator_issues`.`items_id` AND `glpi_plugin_formcreator_issues`.`itemtype` = 'Ticket' )
LEFT JOIN `glpi_ticketvalidations` AS `glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca` ON (`glpi_tickets`.`id` = `glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca`.`tickets_id` )
LEFT JOIN `glpi_users` AS `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4` ON (`glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca`.`users_id_validate` = `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id` )
LEFT JOIN `glpi_entities` ON (`glpi_plugin_formcreator_issues`.`entities_id` = `glpi_entities`.`id` )
LEFT JOIN `glpi_users` AS `glpi_users_requester_id` ON (`glpi_plugin_formcreator_issues`.`requester_id` = `glpi_users_requester_id`.`id` )
WHERE (( `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.users_id = '2' OR `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.users_id = '2' OR `glpi_plugin_formcreator_issues`.`requester_id` = '2' OR `glpi_ticketvalidations`.`users_id_validate` = '2') OR `glpi_plugin_formcreator_issues`.`users_id_validator` = '2' OR `glpi_plugin_formcreator_issues`.`groups_id_validator` IN ('1') OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id` = '2' OR `glpi_plugin_formcreator_issues`.`users_id_recipient` = 2 ) AND ( `glpi_plugin_formcreator_issues`.`status` IN ('4','101') ) GROUP BY `glpi_plugin_formcreator_issues`.`id`
ORDER BY `ITEM_PluginFormcreatorIssue_6` DESC

[LuizRicarte]

@btry

I don't know if I understood much, I'm sorry.

I switched to the SuperAdmin profile, activated Debug.

I changed it again to the SelfService profile, where it presents the Dashboard, but with the Debug active it does not load.

If I disable the debug it loads normally.

[btry]

humm, interesting. Debug mode breaks the coutners ? Checl sql-errors.log and php-errors.log to see if some entries are related to this web page. We need to fix this to get the SQL query.

[LuizRicarte]

@btry

In the SQL file I didn't find anything related to the plugin

SQL-Errors.log

In the PHP file I found this error several times, I don't know exactly what to look for, I'm sorry if it's not that, I'm still starting in this GLPI world, if not and can you guide me what I should look for I'd appreciate it.

php-errors.log

[2022-07-12 21:17:30] glpiphplog.NOTICE: *** PHP Deprecated function (8192): str_replace(): Passing null to parameter #2 ($replace) of type array|string is deprecated in /var/www/html/glpi/plugins/formcreator/inc/formanswer.class.php at line 1215 Backtrace : plugins/formcreator/inc/formanswer.class.php:1215 str_replace() plugins/formcreator/inc/issue.class.php:830 PluginFormcreatorFormAnswer->parseTags() plugins/formcreator/hook.php:317 PluginFormcreatorIssue::giveItem() src/Plugin.php:1550 plugin_formcreator_giveItem() src/Search.php:6298 Plugin::doOneHook() src/Search.php:1687 Search::giveItem() src/Search.php:447 Search::constructData() plugins/formcreator/inc/issue.class.php:1072 Search::getDatas() : PluginFormcreatorIssue::nbIssues() src/Dashboard/Grid.php:955 call_user_func_array() ajax/dashboard.php:139 Glpi\Dashboard\Grid->getCardHtml()

[btry]

The error you found is not related to this issue, but requires a small fix, provided in the new issue #2874 .

Some other users reported here a problem with the dashboard. I assume your problem is the same, but unfoertunately I could not find the fix yet. I expect to find it from an instance I can investigate myself. I will provide to you a temporary patch to write the SQL query in logs.

[btry]

Here is the patch to log the SLQ query we need to analyze

diff --git a/inc/issue.class.php b/inc/issue.class.php
index 8805a822..1034d74b 100644
--- a/inc/issue.class.php
+++ b/inc/issue.class.php
@@ -1068,6 +1068,9 @@ class PluginFormcreatorIssue extends CommonDBTM {
          $searchCriteria
       );
       $count = 0;
+      if ($params['status'] == 'waiting') {
+         Toolbox::logSqlError($searchWaiting['sql']['search']);
+      }
       if (isset($searchWaiting['data']['totalcount'])) {
          $count = $searchWaiting['data']['totalcount'];
       }

The log entry will be written in sql-errors.log

[LuizRicarte]

@btry

Apologies for the delay.

As I understand it, I have to add the snippet indicated in the issue.class.php file. That's it?

[LuizRicarte]

@btry

I think it worked.

sql-errors.log

sqlerro.txt

[btry]

Hi

It worked. When you need to apply a patch you should use the patch tool. It will prevent human errors, espacially for complex modifications.

The query looks good.

From what I see the issue ID=1939 is filtered out because the status is 2. If you remove the last assertion in the WHERE clause the issue should be in the results. Here is the resulting query. Run it and check if the issue ID=1939 is returned (among others).

SELECT
    DISTINCT `glpi_plugin_formcreator_issues`.`id` AS id,
    'luiz.oliveira' AS currentuser,
    `glpi_plugin_formcreator_issues`.`itemtype`,
    `glpi_plugin_formcreator_issues`.`entities_id`,
    `glpi_plugin_formcreator_issues`.`is_recursive`,
    GROUP_CONCAT(
        DISTINCT CONCAT(
            `glpi_plugin_formcreator_issues`.`name`,
            '$#$',
            `glpi_plugin_formcreator_issues`.`id`
        )
        ORDER BY
            `glpi_plugin_formcreator_issues`.`id` SEPARATOR '$$##$$'
    ) AS `ITEM_PluginFormcreatorIssue_1`,
    IFNULL(
        GROUP_CONCAT(
            DISTINCT CONCAT(
                IFNULL(
                    `glpi_plugin_formcreator_issues`.`display_id`,
                    '__NULL__'
                ),
                '$#$',
                `glpi_plugin_formcreator_issues`.`id`
            )
            ORDER BY
                `glpi_plugin_formcreator_issues`.`id` SEPARATOR '$$##$$'
        ),
        '__NULL__$#$'
    ) AS `ITEM_PluginFormcreatorIssue_1_display_id`,
    `glpi_plugin_formcreator_issues`.`display_id` AS `ITEM_PluginFormcreatorIssue_2`,
    `glpi_plugin_formcreator_issues`.`status` AS `ITEM_PluginFormcreatorIssue_4`,
    `glpi_plugin_formcreator_issues`.`date_creation` AS `ITEM_PluginFormcreatorIssue_5`,
    `glpi_plugin_formcreator_issues`.`date_mod` AS `ITEM_PluginFormcreatorIssue_6`,
    `glpi_entities`.`completename` AS `ITEM_PluginFormcreatorIssue_7`,
    `glpi_users_requester_id`.`name` AS `ITEM_PluginFormcreatorIssue_8`,
    `glpi_users_requester_id`.`realname` AS `ITEM_PluginFormcreatorIssue_8_realname`,
    `glpi_users_requester_id`.`id` AS `ITEM_PluginFormcreatorIssue_8_id`,
    `glpi_users_requester_id`.`firstname` AS `ITEM_PluginFormcreatorIssue_8_firstname`
FROM
    `glpi_plugin_formcreator_issues`
    LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id`
        AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1'
    )
    LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id`
        AND `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3'
    )
    LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id`
        AND `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3'
    )
    LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
        AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2'
    )
    LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
        AND `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`type` = '2'
    )
    LEFT JOIN `glpi_ticketvalidations` ON (
        `glpi_plugin_formcreator_issues`.`itemtype` = "Ticket"
        AND `glpi_plugin_formcreator_issues`.`items_id` = `glpi_ticketvalidations`.`tickets_id`
    )
    LEFT JOIN `glpi_groups` AS `glpi_groups_groups_id_validator` ON (
        `glpi_plugin_formcreator_issues`.`groups_id_validator` = `glpi_groups_groups_id_validator`.`id`
    )
    LEFT JOIN `glpi_tickets` ON (
        `glpi_tickets`.`id` = `glpi_plugin_formcreator_issues`.`items_id`
        AND `glpi_plugin_formcreator_issues`.`itemtype` = 'Ticket'
    )
    LEFT JOIN `glpi_ticketvalidations` AS `glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca` ON (
        `glpi_tickets`.`id` = `glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca`.`tickets_id`
    )
    LEFT JOIN `glpi_users` AS `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4` ON (
        `glpi_ticketvalidations_efead4a1e4d0f1413d363b6502c29dca`.`users_id_validate` = `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id`
    )
    LEFT JOIN `glpi_entities` ON (
        `glpi_plugin_formcreator_issues`.`entities_id` = `glpi_entities`.`id`
    )
    LEFT JOIN `glpi_users` AS `glpi_users_requester_id` ON (
        `glpi_plugin_formcreator_issues`.`requester_id` = `glpi_users_requester_id`.`id`
    )
WHERE
    (
        (
            `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.users_id = '51'
            OR `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.users_id = '51'
            OR `glpi_plugin_formcreator_issues`.`requester_id` = '51'
            OR `glpi_ticketvalidations`.`users_id_validate` = '51'
        )
        OR `glpi_plugin_formcreator_issues`.`users_id_validator` = '51'
        OR `glpi_plugin_formcreator_issues`.`groups_id_validator` IN ('14')
        OR `glpi_users_users_id_validate_57751ba960bd8511d2ad8a01bd8487f4`.`id` = '51'
        OR `glpi_plugin_formcreator_issues`.`users_id_recipient` = 51
    )
GROUP BY
    `glpi_plugin_formcreator_issues`.`id`
ORDER BY
    `ITEM_PluginFormcreatorIssue_1` ASC

[btry]

Also, please validate if #2874 solves the errors you showed here

[LuizRicarte]

@btry

Sorry, as I said I don't have much knowledge, where can I read about the patch tool to apply it correctly?

I performed the requested query and the ID 1939 appears as shown in the print below.

Thank you very much for your patience

[btry]

Hi

This should be a good resource for you to learn about patch

As I expected the status filters out the entry. I need to analyze further the status workflow against your samples.

[dalmeidac]

I have the same problem! managed to come up with a solution ?

[LuizRicarte]

I have the same problem! managed to come up with a solution ?

No, I keep having the problem