List of High5 Queries

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';

#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]);

#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

#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);

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

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;

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';

#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';

#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;

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);

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;

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;