Debug Query Reports

In order to help me to debug, fill free to open an issue with following queries result. All of those queries provide non sensitive information only (no api keys will be revealed, no password, no username and no mail address)

For all queries, you need to replace variables value according to this :

• SEAT_USERNAME : The SeAT user ID you want to test (you can find it in your SeAT user list into Configuration > Users.
• SLACK_PRIVATE : Either TRUE or FALSE according to which channel list you want (respectively private or public)

Provide information about Slack channels

select *
from slack_channels;

Give api key number linked to a specific SeAT user

set @SEAT_USERNAME = 'Warlof Tutsimo';

select count(*) as linked_keys
from eve_api_keys eak
join users u on eak.user_id = u.id
where u.`name` = @SEAT_USERNAME;

Give enabled api key number linked to a specific SeAT user

set @SEAT_USERNAME = 'Warlof Tutsimo';

select COUNT(*) as enabled_keys
from eve_api_keys eak
join users u on eak.user_id = u.id
where u.`name` = @SEAT_USERNAME
and enabled = 1;

Give api key number related to a paid account and linked to a specific SeAT user

set @SEAT_USERNAME = 'Warlof Tutsimo';

select COUNT(*) as paid_account
from eve_api_keys eak
join users u on eak.user_id = u.id
join account_account_statuses aas on eak.key_id = aas.keyID
where u.`name` = @SEAT_USERNAME
and paidUntil >= NOW();

Give slack relation with a specific SeAT user

set @SEAT_USERNAME = 'Warlof Tutsimo';

select *
from slack_users su
join users u on su.user_id = u.id
where u.`name` = @SEAT_USERNAME;

Give slack granted access for a specific SeAT user (channels on which the user should be invited)

set @SEAT_USERNAME = 'Warlof Tutsimo';
set @SLACK_PRIVATE = TRUE;

(select channel_id, sc.`name`
from users u
join slack_channel_users scu on u.id = scu.user_id
join slack_channels sc on scu.channel_id = sc.id
where u.`name` = @SEAT_USERNAME
and sc.is_group = @SLACK_PRIVATE)
union
(select channel_id, sc.`name`
from role_user ru
join slack_channel_roles scr on ru.role_id = scr.role_id
join slack_channels sc on scr.channel_id = sc.id
join users u on ru.user_id = u.id
where u.`name` = @SEAT_USERNAME
and sc.is_group = @SLACK_PRIVATE)
UNION
(select channel_id, sc.`name`
from eve_api_keys eak
join account_api_key_info_characters aakic on aakic.keyID = eak.key_id
join slack_channel_corporations scc on aakic.corporationID = scc.corporation_id
join slack_channels sc on scc.channel_id = sc.id
join users u on eak.user_id = u.id
where u.`name` = @SEAT_USERNAME
and sc.is_group = @SLACK_PRIVATE)
UNION
(select channel_id, sc.`name`
from eve_api_keys eak
join account_api_key_info_characters aakic on aakic.keyID = eak.key_id
join character_character_sheet_corporation_titles ccsct on aakic.characterID = ccsct.characterID
join slack_channel_titles sct on aakic.corporationID = sct.corporation_id
join slack_channels sc on sct.channel_id = sc.id
join users u on eak.user_id = u.id
where u.`name` = @SEAT_USERNAME
and sc.is_group = @SLACK_PRIVATE)
UNION
(select channel_id, sc.`name`
from character_character_sheets ccs
join slack_channel_alliances sca on ccs.allianceID = sca.alliance_id
join account_api_key_info_characters aakic on aakic.characterID = ccs.characterID
join eve_api_keys eak on aakic.keyID = eak.key_id
join slack_channels sc on sca.channel_id = sc.id
join users u on eak.user_id = u.id
where u.`name` = @SEAT_USERNAME
and sc.is_group = @SLACK_PRIVATE)
UNION
(select channel_id, sc.`name`
from slack_channel_public scp
join slack_channels sc on scp.channel_id = sc.id
where sc.is_group = @SLACK_PRIVATE)