Adding Mail Queues

Notes

The SQL in the following steps should be committed in accordance with the Database Versioning standard.

The email address of the queue and the email address of the email account checked may not match. This is because DOS South emails (eg: @pixiehollow.com) are usually redirected to @clubpenguin.com accounts to be imported into CS Support Tools, then "sent" from the original address.

Step 1: Add a New Ticket Category

The SQL to be run on the support database:

INSERT INTO ticket_category
	(`name`, `property_id`, `hasTab`, `from_email`, `from_name`, `bcc_email`, `subject_line`, `signature`, `language`, `tab_color`, `type`)
VALUES
	('Community', 3, 1, 'community@disneyonline.com', 'Disney Online Support', NULL, 'Disney Online Support', 'Disney Online Support', 1, '#ffffff', 'email');

The values, in order of appearance, are:

• name: This is the name of the queue as it appears in Flurry.
• property_id: The integer ID of the virtual world. Refer to the Property ID list.
• hasTab: Legacy Team Tools value, set to 1.
• from_email: The email address all responses are sent from. This does not have to match the address that receives the email.
• from_name: The name of the sender in the responses.
• bcc_email: An email address to BCC on responses. Usually NULL.
• subject_line: The email subject line in the response
• signature: The line following the SR's name in the response
• language: The ID of the language the queue is for. See Language ID List.
• tab_color: The hex color code used to highlight the email entry
• type: Email queues will always have 'email' as the type

Step 2: Add the Mail Account

The SQL to be run on the mail database:

SET @password := 'PASSWORD'; -- Update with correct password
SET @category := 0;          -- SELECT ticket_category_id FROM ticket_category WHERE from_email = 'community@disneyonline.com';

INSERT INTO flurry_address
	(`local`, `domain`, `category`, `ticket_category_id`, `method`, `path`, `password`, `host`, `max_message_size`, `language`, `enable_parser` )
VALUES
	('community.disneyonline', 'clubpenguin.com', 'Community', @category, 'POP3', '/var/mail/vhosts/clubpenguin.com/dos_test', @password, 'localhost', 0, 1, 1);

The values, in order of appearance, are:

• local: The portion of the receiving email address to the left of the '@' symbol.
• domain: The portion of the receiving email address to the right of the '@' symbol.
• category: The name of the category the emails are for. TODO: verify if this needs to be the same as support.ticket_category.name
• ticket_category_id: The ID of the category created in Step 1.
• method: The method for retrieving email. Use POP3 or MailDir
• path: The MailDir path on the system. Use /var/mail/vhosts/clubpenguin.com/dos_test to if using POP3 to avoid triggering alerts (which annoy Landon).
• password: The POP3 password
• host: The POP3 host. Usually localhost
• max_message_size: The largest size, in bytes, of a message allowed to be added to the queue. Use 0 to disable the limit.
• enable_parser: Used to enable importing of messages into the queue. Typically enabled (1). Use 0 to disable.

The local, domain, and password values are supplied by TechOps.

Since we do not want to save passwords in our source code control system, the file should be committed with @password set to 'PASSWORD'. This should be updated when handing off the script to TechOps.

Since the mail database is on a separate database server, we cannot dynamically determine the ticket category ID. As such, instructions will have to be given to TechOps to update the @category value after the previous script has been run.

Step 3: Add the Queue Permissions

The SQL to be run on the authentication database:

SET @category   := (SELECT ticket_category_id FROM support.ticket_category WHERE from_email='community@disneyonline.com');
SET @permission := 'Answer ticket_category_id:';

INSERT INTO group_permission
	(group_id, permission_id)
VALUES
	('CPE_PSR',    CONCAT(@permission, @category)),
	('CPE_PSR_CA', CONCAT(@permission, @category)),
	('CPE_CSR',    CONCAT(@permission, @category)),
	('CPE_CSR_CA', CONCAT(@permission, @category)),
	('CPE_TSR',    CONCAT(@permission, @category)),
	('CPE_TSR_CA', CONCAT(@permission, @category)),
	('CPE_TSR_US', CONCAT(@permission, @category)),
	('quality_assurance_ca', CONCAT(@permission, @category)),
	('community_support_leadership_team_ca', CONCAT(@permission, @category)),
	('community_support_leadership_team_us', CONCAT(@permission, @category));

The values, in order of appearance, are:

• group_id: The LDAP group to permit.
• permission_id: The name of the permission. To enable an SR to answer emails in a queue, the permission ID needs to be 'Answer ticket_category_id:xyz', where xzy is the ticket_category_id of the queue.

Quality Assurance Steps

1. After Step 1, ensure the queue appears in the system
   • log out of CS Support Tools
   • clear Flash cache
   • clear memcache
   • login to CS Support Tools with an account that has Super privileges (wait for dictionaries to load)
   • check that the queue appears in flurry for the appropriate virtual world and language
2. After Step 2, ensure that emails can be imported to CS Support Tools
   • this test can only be performed on stage and live, and only after the email address has been setup
   • send an email to the email account (eg: local@domain with the values in Step 2)
   • ensure the email has been imported within 5 minutes of sending
3. After Step 3, log in with a test SR account to verify they have access to the queue
   • Test PSRs don't always have permissions to use all virtual worlds, so the test may not be accurate