Tickets by Client report - Show archived clients if tickets exist in period

JosephWithCOR

Empty set (0.012 sec)

wrongecho

I recall you having issues after we moved to custom statuses. Are there any tickets with the resolved column set that don't have a status of 4 (resolved) or 5 (closed)?

JosephWithCOR

If you are referring to tickets.ticket_status

1 - Empty set

2 - 108 rows

3 - 8 rows

4 - 61 rows

5 - 5035 rows

Count at top of tickets.php: 114 open / 5098 closed. Otherwise you will need to direct me.

wrongecho

Sorry, just to confirm it should be for any tickets that have a resolved/closed time set but aren't showing that status (i.e. both of these should be empty):

select * from tickets where ticket_resolved_at is not null and ticket_status != 4 AND ticket_status != 5

select * from tickets where ticket_closed_at is not null and ticket_status != 4 AND ticket_status != 5

JosephWithCOR

select * from tickets where ticket_resolved_at is not null and ticket_status != 4 AND ticket_status != 5; returns

2 rows in set (0.012 sec)

select * from tickets where ticket_closed_at is not null and ticket_status != 4 AND ticket_status != 5; returns

Empty set (0.012 sec)

Modifying the first to show ticket_number returns 2437 (open) and 4465 (on hold)

2437 shows

4465 shows

Are they caught between status's?

wrongecho

JosephWithCOR

Apologies for the delay, I have lots of life things going on!!

So this mismatch is a minor issue, but I don't think it's the whole cause of your numbers not adding up. You will recall we had a few issues when we moved to custom ticket states (hindsight is a fine thing).

With them just having a datestamp for resolved (but not closed), you should be fine to close them in the UI using the top-right 'Close' button? If not, we can close them manually in the database.

You suggest the numbers wrong are from the report_ticket_by_client report? Do all of your tickets have clients associated with them?

SELECT * FROM tickets WHERE ticket_client_id = '0';

JosephWithCOR

TCK-2437 was last updated 11 months ago. I don't know why it would still show open. The add note box isn't available.

TCK-4465 was last updated 3 months ago BUT is Scheduled: 2025-08-01 08:00. The add note box isn't available either.

Weird…

wrongecho

Sorry, I have been busy. Let me come back to you on this, as it isn't quite right

JosephWithCOR

Dude, no worries… It's not a problem that needs to be fixed NOW!!!

Thank you for looking into this.

digibandit

bold the not bold the not lol

JosephWithCOR

@wrongecho

Life happens. In the grand scheme of things this is a minor issue. I am trying to resolve the discrepancy.

[database]> SELECT * FROM tickets WHERE ticket_client_id = '0';
Empty set (0.012 sec)

Today I am at 5299 (TCK-5289) and a SELECT COUNT(*) FROM tickets; returns 5299. Next ticket created will be 5300 (TCK-5290)

Continuing to count tickets by client I counted tickets "WHERE ticket_client_id = 'x';" for each of my thirteen clients (one of the benefits of being internal IT), two of which are archived, total 37 tickets, and the source of the discrepancy in the math.

When the client is archived, its totals are removed / hidden from the tickets by clients report generated by report-ticket-by-client.php causing the mathing error.

Thank you for pointing me in the right direction!

Tickets.php counts the total number of tickets across clients, it appears even the archived clients.

Because their tickets count, I would suggest listing the archived clients along with ticket counts up to 12/31 of the year they were archived in report-ticket-by-client.php select by year.

wrongecho

JosephWithCOR

Ah yes! We were on the right track then. Nice job figuring this out.

Because their tickets count, I would suggest listing the archived clients along with ticket counts up to 12/31 of the year they were archived in report-ticket-by-client.php select by year.

I think that's a good idea, thank you!