List of High5 Queries

Account / User / Points

List of Terminated Users with High5 Points remaining(Frequency: yearly)

SELECT 
accounts.login AS 'Employee ID', 
users.first_name AS 'First Name', 
users.last_name AS 'Last Name', 
accounts.points AS 'High 5 Points', 
users.termination_date AS 'Termination Date'
FROM accounts
LEFT JOIN users ON users.id = accounts.user_id 
WHERE users.is_terminated = 1
AND accounts.points > 0
AND DATE(users.termination_date) < '2016-01-01'
AND DATE(users.termination_date) > '2014-12-31';

Zeroed out Terminated Users based on Logins / Employee IDs (Frequency: yearly)

#Double-checking before running the query
SELECT 
accounts.login AS 'Employee ID', 
users.first_name AS 'First Name', 
users.last_name AS 'Last Name', 
accounts.points AS 'High 5 Points', 
users.termination_date AS 'Termination Date'
FROM accounts
LEFT JOIN users ON users.id = accounts.user_id 
WHERE users.is_terminated = 1
AND accounts.points > 0
AND accounts.login IN ([Insert a list of employee ids, separated with comma])
ORDER BY users.termination_date;
#Create Point Log
INSERT INTO point_logs(user_id,`old`, `new`, `reason`, `datetime`)
SELECT accounts.user_id, accounts.points, 0, 'Zero Out Points from Terminated User', NOW()
FROM accounts
LEFT JOIN users ON users.id = accounts.user_id 
WHERE users.is_terminated = 1
AND accounts.points > 0
AND accounts.login IN ([Insert a list of employee ids, separated with comma]);
#Update Account
UPDATE accounts 
LEFT JOIN users ON users.id = accounts.user_id
SET accounts.points = 0
WHERE users.is_terminated = 1
AND accounts.points > 0
AND accounts.login IN ([Insert a list of employee ids, separated with comma]);

Create a new HR Account (Frequency: rarely)

Referenced from High 5.docx in P:\Teams\Team Technical\High5\High5 Documents

Requirement:

  • Username / Employee ID (accounts.login)
  • First Name
  • Last Name
  • Username for HR Account. Format is "[employee_id]hr"
#grab initial user (for checking)
SELECT *
FROM accounts
LEFT JOIN users ON accounts.user_id = users.id
WHERE accounts.login = username;
#create a new user
INSERT INTO users
(`business_unit_id`,`organization_level_id`, `is_terminated`, `is_store`,  `first_name`, `last_name`, `job_title`, `position_title`, `position_code`)
SELECT business_unit_id, 1, is_terminated, is_store, first_name, last_name, job_title, position_title, position_code
FROM users
LEFT JOIN accounts ON accounts.user_id = users.id

WHERE accounts.login = username;

#grab the user id from user you just created
SELECT *
FROM users 
WHERE users.last_name = last name AND users.first_name = first name
AND organization_level_id = 1;
#create a new account with that user id
INSERT INTO `accounts` 
(`user_id`, `language_id`, `role_id`, `login`, `password`, `points`, `work_email`, `home_email`, `text_email`, `phone`, 
`use_work_email`, `use_home_email`, `use_text_email`, `use_phone`, `theme_id`, `is_locked`, `is_first_login`, `is_milestone_eligible`, `birth_date`, 
`hire_date`,`pay_group`, `employee_record`, `company_code`, `province`)
SELECT HR user id, language_id, 3, HR username, `password`, 0, work_email, home_email, text_email, phone, use_work_email, use_home_email, 
use_text_email, use_phone, theme_id, is_locked, is_first_login, 0, birth_date, hire_date, pay_group, employee_record, 
company_code, province
FROM accounts
WHERE accounts.login = username;
#add users to organization levels association:
INSERT INTO `user_organizationlevel` 
(`user_id`, `organizationlevel_id`)
VALUES
(HR user id,3386),
(HR user id,5342),
(HR user id,6212),
(HR user id,6246),
(HR user id,6887);

User Point Balance Log

SELECT 
point_logs.id AS 'Point Log ID',
accounts.login AS 'Employee ID',
CONCAT(users.first_name,' ',users.last_name) AS 'User Name',
point_logs.datetime AS 'Date',
point_logs.reason AS 'Description',
IF(point_logs.old - point_logs.new <= 0, '', point_logs.old - point_logs.new) AS 'Debit',
IF(point_logs.new - point_logs.old <= 0, '', point_logs.new - point_logs.old) AS 'Credit',
point_logs.new AS 'Balance'
FROM point_logs
LEFT JOIN users ON point_logs.user_id = users.id
LEFT JOIN accounts ON accounts.user_id = users.id

WHERE users.id = user_id;

Budget

Budget Transaction Log

SELECT
budget_logs.id AS 'Budget Log ID', 
organization_levels.name AS 'Organization Level Name',
budget_logs.datetime AS 'Date', 
budget_logs.reason AS 'Description',
IF(budget_logs.old - budget_logs.new <= 0, '', budget_logs.old - budget_logs.new) AS 'Debit',
IF(budget_logs.new - budget_logs.old <= 0, '', budget_logs.new - budget_logs.old) AS 'Credit',
budget_logs.new AS 'Balance'
FROM budget_logs
LEFT JOIN organization_levels ON budget_logs.organization_level_id = organization_levels.id
WHERE organization_level_id = 3278;

Email

Gift Card Batch Email - List of Orders in Email Content 

SELECT 
orders.id AS 'Order ID', 
orders.datetime AS 'Datetime', 
CONCAT(users.first_name,' ',users.last_name) AS 'User Name',
accounts.login AS 'Employee ID',
organization_levels.name AS 'Business Unit',
SUBSTRING_INDEX(prizes.name, '<br><br>', 1) AS 'Prize Name',
SUBSTRING_INDEX(prizes.description, '<br><br>', 1) AS 'Prize Description',
prizes.cost AS 'Amount (High5 Points)',
CONCAT(REPLACE(SUBSTRING_INDEX(orders.address,'<br />', 2),'<br />', ' '),'\n',REPLACE(REPLACE(SUBSTRING_INDEX(orders.address,'<br />', -4), '<br/>', '\n'), '<br />', '\n')) AS 'Address',
IF(accounts.language_id=1,'en','fr') AS 'Language'
FROM orders
LEFT JOIN prizes ON orders.prize_id = prizes.id
LEFT JOIN users ON orders.user_id = users.id
LEFT JOIN accounts ON accounts.user_id = users.id
LEFT JOIN organization_levels ON users.business_unit_id = organization_levels.id
WHERE prizes.send_immediately = 0
AND DATE(DATETIME) = '2016-04-05';

Gift Card Batch Email CSV Attachment

#Note that ADDRESS2 is meant for PO Box, but I've omitted it due to end-users' address formating
SELECT
'' AS 'Entered by REP ID',
'' AS 'Verified by ID',
'' AS 'GIFT CARD #',
prizes.cost AS 'AMT',
'',
'' AS 'DBA',
SUBSTRING_INDEX(prizes.name, 'Gift Card', 1) AS 'CUST COMP TYPE',
users.id AS 'CLIENT REF NUM',
IF(accounts.language_id=1,'en','fr') AS 'LANGUAGE',
SUBSTRING_INDEX(orders.address,'<br />', 1) AS 'FIRST NAME',
SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br />', 2),'<br />', -1) AS 'LAST NAME',
SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br />', 3),'<br />', -1) AS 'ADDRESS1',
'' AS 'ADDRESS2',
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br />', 4),'<br />', -1),', ',1) AS 'CITY',
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br />', 4),'<br />', -1),', ',-1),'<br/>',1) AS 'PR',
'CANADA' AS 'COUNTRY',
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br />', 4),'<br />', -1),'<br/>',-1) AS 'POSTALCODE',
SUBSTRING_INDEX(SUBSTRING_INDEX(orders.address,'<br/>', 4),'<br />', -1) AS 'PHONE',
organization_levels.name AS 'BUSINESS UNIT'
FROM orders
LEFT JOIN prizes ON orders.prize_id = prizes.id
LEFT JOIN users ON orders.user_id = users.id
LEFT JOIN accounts ON accounts.user_id = users.id
LEFT JOIN organization_levels ON users.business_unit_id = organization_levels.id
WHERE prizes.send_immediately = 0
AND DATE(DATETIME) = '2016-12-12';

Reports

Customized report based on existing Manager / Admin Reports. Note that the Manager Reports don't have the 'Download CSV' Feature.

Budget and Allocation Usage Manager Report

By Organization

I’m trying to pull together a report under John Salt regarding his Managers, AVPs, and VPs initial budget and remaining budget. But it doesn’t allow me to filter it to Managers and up but displays every single employee. Are you able to send me a report of that? So it would all of John Salt’s Managers, AVPs, VPs, and John as well, with how many budgeted points they initially have and what they have remaining.

Reported on Feb 7 2017 - Freshdesk

#Search for organization level id based on user's name
SELECT *
FROM users
WHERE users.first_name = 'John' AND users.last_name = 'Salt';
#Create the report based on organization level id
SELECT 
organization_level_hierarchy_lookups.organization_level_name_hierarchy AS 'Organization Level',
organization_levels.name AS 'Level Name',
IF(manager_users.id IS NULL,'',GROUP_CONCAT(CONCAT(manager_users.first_name,' ',manager_users.last_name))) AS 'Manager(s) Managing the Budget',
organization_levels.budget AS 'Budget Remaining',
IF(budget_logs.new IS NULL,0,budget_logs.new) AS 'Initial Budget',
IF(budget_logs.new IS NULL, 'N/A',CONCAT(ROUND(100*organization_levels.budget/budget_logs.new,2),'%')) AS 'Percentage of Budget Remaining'
FROM organization_levels
LEFT JOIN organization_level_closure ON organization_level_closure.child_id = organization_levels.id
LEFT JOIN organization_level_hierarchy_lookups ON organization_level_hierarchy_lookups.organization_level_id = organization_levels.id
LEFT JOIN (
SELECT organization_level_id,MAX(budget_logs.id) AS id
FROM budget_logs
WHERE reason = 'Importer'
GROUP BY budget_logs.organization_level_id) AS last_budget_log_id ON last_budget_log_id.organization_level_id = organization_levels.id
LEFT JOIN budget_logs ON budget_logs.organization_level_id = organization_levels.id AND budget_logs.id = last_budget_log_id.id
LEFT JOIN (
SELECT users.*
FROM users
LEFT JOIN accounts ON users.id = accounts.user_id
WHERE accounts.role_id = 1
AND users.is_terminated = 0) AS manager_users ON manager_users.organization_level_id = organization_levels.id
WHERE organization_level_closure.parent_id = 5200
AND organization_level_closure.hops <=3
GROUP BY organization_levels.id;

By Employee without hierarchy (including Date Last Imported, Points Distributed / Deducted in 2017)

SELECT
accounts.login AS 'Employee ID',
CONCAT(users.first_name, ' ', users.last_name) AS 'Employee Name',
users.position_title AS 'Position Title',
organization_levels.id AS 'Organization Level ID',
budget_logs.datetime AS 'Date Last Imported',
budget_logs.new AS 'Last Imported Budget',
organization_levels.budget AS 'Budget Remaining',
IF(budget_added_2017.budget IS NULL,0,budget_added_2017.budget) AS 'Points Distributed by Importer in 2017',
IF(budget_deducted_2017.budget IS NULL,0,budget_deducted_2017.budget) AS 'Points Deducted by Importer in 2017'
FROM accounts
LEFT JOIN users ON accounts.user_id = users.id
LEFT JOIN organization_levels ON users.organization_level_id = organization_levels.id
LEFT JOIN (
SELECT organization_level_id,MAX(budget_logs.id) AS id
FROM budget_logs
WHERE reason = 'Importer'
GROUP BY budget_logs.organization_level_id) AS last_budget_log_id ON last_budget_log_id.organization_level_id = organization_levels.id
LEFT JOIN budget_logs ON budget_logs.organization_level_id = organization_levels.id AND budget_logs.id = last_budget_log_id.id
LEFT JOIN (
SELECT organization_level_id, SUM(budget_logs.new-budget_logs.old) AS budget
FROM budget_logs
WHERE reason = 'Importer' 
AND YEAR(budget_logs.datetime) = 2017
AND budget_logs.new > budget_logs.old
GROUP BY budget_logs.organization_level_id) AS budget_added_2017 ON budget_added_2017.organization_level_id = organization_levels.id
LEFT JOIN (
SELECT organization_level_id, SUM(budget_logs.old-budget_logs.new) AS budget
FROM budget_logs
WHERE reason = 'Importer' 
AND YEAR(budget_logs.datetime) = 2017
AND budget_logs.old > budget_logs.new
GROUP BY budget_logs.organization_level_id) AS budget_deducted_2017 ON budget_deducted_2017.organization_level_id = organization_levels.id
WHERE accounts.login IN (224964,235693,221746,180664,228679,1028622,222278);

Basic Manager Report (including organization levels, managers and current budget)

SELECT

ol.id AS 'Organization Level ID',

olhl.organization_level_name_hierarchy AS 'Organization Level Hierarchy Name',

GROUP_CONCAT(CONCAT(managers.name,'(',managers.login,')')) AS 'Manager(s) Managing the Budget',

ol.budget AS 'Current Budget'

FROM organization_levels ol

INNER JOIN

(

SELECT users.id, users.organization_level_id, accounts.login, CONCAT(users.first_name,' ',users.last_name) AS NAME

FROM users

INNER JOIN accounts ON users.id = accounts.user_id AND role_id = 1

WHERE is_terminated = 0

AND users.id <>7849

) managers ON managers.organization_level_id = ol.id

LEFT JOIN

organization_level_hierarchy_lookups olhl ON olhl.organization_level_id = ol.id

GROUP BY ol.id

ORDER BY olhl.organization_level_name_hierarchy;

Prizes

Prize List (frequency: once in a while / when new prizes are going to be added)

SELECT 
prizes.id AS 'Prize ID', 
prizes.category_id AS 'Category ID', 
prize_categories.name AS 'Category Name', 
prizes.name AS 'Prize Name', 
prizes.description AS 'Prize Description', 
prizes.cost AS 'Cost', 
prizes.fulfillment_email AS 'Fulfillment Email',
prizes.tag AS 'Tags',
IF(prizes.send_immediately, 'Yes','No') AS 'Send to Fulfillment Immediately',   
IF((ISNULL(organization_levels.name)),'',GROUP_CONCAT(organization_levels.name)) AS 'Business Units'
FROM prizes
LEFT JOIN prize_categories ON prizes.category_id = prize_categories.id
LEFT JOIN prize_organizationlevel ON prizes.id = prize_organizationlevel.prize_id
LEFT JOIN organization_levels ON prize_organizationlevel.organizationlevel_id = organization_levels.id
GROUP BY prizes.id;

Rules

Easier way to view rules, paths and conditions

SELECT
roles.name AS 'role name',
rules.verb,
rules.object,
conditions.name AS 'conditon name',
paths.name AS 'path name',
GROUP_CONCAT(joins.key_name) joins, 
paths.column_name,
conditions.param
FROM roles
LEFT JOIN rules ON rules.role_id = roles.id
LEFT JOIN conditions ON conditions.rule_id = rules.id
LEFT JOIN paths ON paths.id = conditions.path_id
LEFT JOIN path_joins ON path_joins.path_id = paths.id
LEFT JOIN joins ON path_joins.join_id = joins.id
GROUP BY roles.name, rules.verb, rules.object, conditions.name, paths.name
ORDER BY roles.name, rules.verb, rules.object, conditions.name, paths.name, path_joins.order_number;