Scripting

Sorry for the slow response. I think we have a working script, but for some reason it's always showing 0 rows selected when I try and test.

SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
SUBMITTER_CUSTOM.FIELD_VALUE AS SUBMITTER_CUSTOM_1, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- example of static distribution list
'bpendry@martinsnet.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* submitter custom*/ LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = SUBMITTER_CUSTOM.USER_ID AND SUBMITTER_CUSTOM.FIELD_ID = 1
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE 'TICKET CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

If I remove the "AND C.ID=<CHANGE_ID>" and run the query in MySQL Workbench, I get correct results for all the tickets in the system. But for some reason this still doesn't seem to work.

Any thoughts?

I appreciate your help.

Nick - nwilliams@martinbros.com 7 years ago

You can't effectively test rules that run on save because of the CHANGE_ID variable. The best method is to save the rule and then create a ticket to test. I have a queue setup for testing rules that only admins can see. - chucksteel 7 years ago

I'm afraid the last run log isn't showing much. Simply this: 12/06/2017 11:33:07> Starting: 12/06/2017 11:33:07 12/06/2017 11:33:07> Executing Select Query... 12/06/2017 11:33:07> selected 0 rows

as I said before, I can see the results correctly, so I'm not sure what to look at next - nwilliams@martinbros.com 7 years ago

Try adjusting the C.DESCRIPTION to LIKE '%Created%'. - chucksteel 7 years ago

Changing that doesn't seem to have made any difference at all. Is there a better way to trouble shoot this than to just look and see if the save has results? - nwilliams@martinbros.com 7 years ago

The CHANGE_ID Variable didn't change with 7.x did it? - nwilliams@martinbros.com 7 years ago

You can look at the HD_TICKET_CHANGE table for the ticket you created and see what it reports. You could also remove the C.DESCRIPTION like "%Created%" so that it should match on any change to the ticket and see if it triggers an email. - chucksteel 7 years ago

No, the variable is still the same. Another way to trouble shoot is to find the ID for the change in the HD_TICKET_CHANGE table for that ticket and then run the query using that ID in MySQL Workbench where the variable normally would be. - chucksteel 7 years ago

It seems to work when I take the WHERE
C.DESCRIPTION LIKE '%CREATED%' out. So that's a step in the right direction. - nwilliams@martinbros.com 7 years ago

We're working now! I hate to say it was mostly how I was testing that was the issue, but now the issue is resolved. Thank you very much for your time and effort to help a handicapped scripter like myself :) - nwilliams@martinbros.com 7 years ago