Custom Notifications

[Tampa]

I'm attempting to extend the notifications system to include more information about tickets and invoices, unfortunately I have hit a brick wall. Retrieving further ticket data from the database randomly yields no data for the message field.

foreach (Capsule::table('tbltickets')->where('id', $ticketid)->get() as $ticket)
		{
			$tickettitle = $ticket->title;
			$ticketmsg = $ticket->message;
		}

For some tickets will return the message in full and for others I just get 0. I assumed this had something to do with size or perhaps a nested array, but checking the actual bit length of the return yields 0 length as well. What exactly am I missing here?

My ultimate goal for this is to show full text for all tickets, replies and the invoices as tabled boxes and pipe them via curl into the webhooks provided by Mattermost.

[brian!]

22 hours ago, Tampa said:

For some tickets will return the message in full and for others I just get 0. I assumed this had something to do with size or perhaps a nested array, but checking the actual bit length of the return yields 0 length as well. What exactly am I missing here?

only the opening ticket message will be stored in tbltickets - if there are replies, then they'll be stored in tblticketreplies, with the 'tid' value in tblticketreplies equal to the 'id' value in tbltickets.

[Tampa]

Again, id, tid, title and so on return fine, but seemingly at random message will return 0 for some tickets and proper for others. I cannot figure out why, the ticket exists and has a message set, I'm not even looking at the replies yet.

[steven99]

Do a bit of database digging and see if the message field in tbltickets is actually filled or not.  Then match the ticket that is showing no message field and see if what you expected is in the replies table instead.   Would not be surprised if WHMCS put the initial message as a reply and left the message field blank. 

Does the initial message show in the ticket when you view it in the admin? 

[Tampa]

All the tickets I tried to pull data from actually have the message field filled out, otherwise I would not be asking as to why nothing was returned.

[steven99]

Was a diagnostic question as from experience such questions provides clues. 

I did an adminareapage hook that queried 1000 tickets, tested if ticketmessage was empty, and saved that away to an array if so.  Out of those 1000 tickets 77 matched to not have an message but having an initial message.  Checking some of those tickets, they appeared to be have been merged tickets -- multiple tickets merged to one.   So see if that holds on your side. 

[Tampa]

The two tickets I tested with that one was working and the other not, were not merged or in any other way different from one another apart from the obvious contents like title, client and message. They also both have replies and all other parameters return fine, just message stubbornly refuses to return for one of them. I tried most of what I could think of to debug, even tried to make sure it was not returning a nested array, but somehow it all just seems to arbitrarily fail without anything I can make out as to why. If the way ticket data was pulled for the internal ticket system was described anywhere that would help maybe give me a lead as to why some tickets work and some won't. Heck it would be some much easier just to be able to expand the notifications system to allow full message inclusion, but unfortunately that code is not open.

[steven99]

For the one that isn't working, if you copy the message field data from one to the other in phpmyadmin, does the working one stop?

WHMCS uses a custom capsule / laravel database system, so if you want to dig further you could start there.  I wonder if you do a straight PDO query if the behavior continues. 

[Tampa]

Tried this, results inconclusive. Tested 8 tickets, 2 of which were still not returning no matter what I put in the message field. I have not tried straight pdo, though I am at verge of just going back to basic mysqli if I can't get a result for no discernible reason. 

[Tampa]

So I revisited this. I tried direct pdo method essentially hardcoded the ticket ids to test. The ticket in question has only one reply, this I tried fetching directly from the id rather than tid, the return? Array

If I var dump that I get: 1

That's it. Now I am no php dev by trade, so I am utterly confused now. I for the life of me cannot figure out how to pull the messages out of the tables, they just refuse to return anything I can actually work with.

 

$pdo = Capsule::connection()->getPdo();
$pdo->beginTransaction();
try {
	$statement = $pdo->prepare('select * from tblticketreplies where id = :replyid');
    $statement->execute(array(':replyid' => "7076"));
    $statement = $statement->fetchAll();    
} catch (\Exception $e) {
    $statement = "Not set";
}
$ticketmsg = addslashes(print_r($statement));

If I send this out as notification to Mattermost I get: WHMCS: 1

I tried everything I could think of, but so far nothing has actually yielded anything more useful. Outside of trying plain old mysqli I really don't know what else I can do to read this data properly. I am going to reopen my support ticket as well, because frankly I feel rather left to my own devices with this and the developer documentation in this regard is somewhat lacking, especially in terms of the examples it gives for retrieving data.