DROP VIEW IF EXISTS v_goals_and_objectives_report;

CREATE VIEW `v_goals_and_objectives_report` AS
SELECT
`g`.`id` AS `goal_id`,
`g`.`emp_number` AS `goal_emp_number`,
CASE WHEN `ohrm_goal_category`.`name` = 'goal' THEN `g`.`name` ELSE null END AS `goal_name`,
CASE WHEN `ohrm_goal_category`.`name` = 'objective' THEN `g`.`name` ELSE null END AS `objective_name`,
CASE WHEN `ohrm_goal_category`.`name` = 'goal' OR `ohrm_goal_category`.`name` = 'objective' THEN `g`.`name` ELSE null END AS `goal_or_objective_name`,
REPLACE(ExtractValue(`g`.`description`, '//text()'),"&nbsp;", "") AS `goal_description`,
`g`.`due_date` AS `goal_due_date`,
`ohrm_goal_category`.`id` AS `goal_category_id`,
`ohrm_goal_category`.`display_name` AS `goal_category_name`,
`ohrm_goal_priority`.`id` AS `goal_priority_id`,
`ohrm_goal_priority`.`name` AS `goal_priority_name`,
`ohrm_goal_purpose`.`id` AS `goal_purpose_id`,
`ohrm_goal_purpose`.`name` AS `goal_purpose_name`,
`ohrm_goal_status`.`id` AS `goal_status_id`,
`ohrm_goal_status`.`name` AS `goal_status_name`,
`ohrm_goal_level`.`id` AS `goal_level_id`,
`ohrm_goal_level`.`name` AS `goal_level_name`,
`g`.`weight` AS `goal_weight`,
`parent`.`id` AS `goal_parent_goal_id`,
`parent`.`name` AS `goal_parent_goal_name`,
`g`.`percentage_completion`  AS `goal_percentage_completion_value`,
IF(`g`.`percentage_completion` IS NOT NULL, CONCAT(`g`.`percentage_completion`,"%"), `g`.`percentage_completion`) AS `goal_percentage_completion`
FROM `ohrm_performance_goal` `g`
LEFT JOIN `ohrm_goal_priority` ON `ohrm_goal_priority`.`id` = `g`.`goal_priority_id`
LEFT JOIN `ohrm_goal_category` ON `ohrm_goal_category`.`id` = `g`.`goal_category_id`
LEFT JOIN `ohrm_goal_purpose` ON `ohrm_goal_purpose`.`id` = `g`.`goal_purpose_id`
LEFT JOIN `ohrm_goal_status` ON `ohrm_goal_status`.`state_id` = `g`.`status_id`
LEFT JOIN `ohrm_goal_level` ON `ohrm_goal_level`.`id` = `g`.`goal_level_id`
LEFT JOIN `ohrm_goal_workflow_status` ON `ohrm_goal_workflow_status`.`state_id` = `g`.`workflow_status_id`
LEFT JOIN `ohrm_performance_goal` `parent` ON `g`.`root_id` = `parent`.`root_id` AND `parent`.`lft` < `g`.`lft` AND `parent`.`rgt` > `g`.`rgt` AND `parent`.`level` = `g`.`level`-1
WHERE `ohrm_goal_category`.`name` IN ('goal', 'objective') AND `ohrm_goal_purpose`.`name` = 'Performance' AND `ohrm_goal_workflow_status`.`consider_as_approved` = 1;

DROP VIEW IF EXISTS v_key_results_report;
CREATE VIEW `v_key_results_report` AS
SELECT
`g`.`id` AS `kr_id`,
`g`.`name` AS `kr_name`,
`g`.`emp_number` AS `kr_emp_number`,
CONCAT_WS(" ", `hs_hr_employee`.`emp_firstname`, `hs_hr_employee`.`emp_lastname`, IF(`hs_hr_employee`.`termination_id` IS NULL, NULL, '(Past Employee)')) AS `kr_owner`,
`g`.`due_date` AS `kr_due_date`,
`ohrm_goal_status`.`id` AS `kr_status_id`,
`ohrm_goal_status`.`name` AS `kr_status_name`,
`g`.`weight` AS `kr_weight`,
`parent`.`id` AS `objective_id`,
`ohrm_performance_key_result_metric`.`id` AS `kr_metric_id`,
CASE
	WHEN `ohrm_performance_key_result_metric`.`current_value` IS NULL THEN `ohrm_performance_key_result_metric`.`current_value`
	WHEN (`ohrm_performance_metric_number_format`.`strategy` = 'decimalNumber') THEN ROUND(`ohrm_performance_key_result_metric`.`current_value`, 2)
	WHEN (`ohrm_performance_metric_number_format`.`strategy` = 'integerNumber') THEN ROUND(`ohrm_performance_key_result_metric`.`current_value`, 0)
	WHEN (`ohrm_performance_metric_number_format`.`strategy` = 'percentageNumber') THEN CONCAT( ROUND(`ohrm_performance_key_result_metric`.`current_value`,2),'%')
	ELSE `ohrm_performance_key_result_metric`.`current_value`
 END AS `kr_metric_value`,
`ohrm_performance_key_result_metric`.`unit_of_measurement` AS `kr_metric_unit_of_measurement`,
`ohrm_performance_metric_number_format`.`format` AS `kr_number_format_id`,
`ohrm_performance_metric_number_format`.`strategy` AS `kr_number_format_strategy`,
`ohrm_performance_metric_number_format`.`format` AS `kr_number_format`,
`g`.`percentage_completion`  AS `kr_percentage_completion_value`,
IF(`g`.`percentage_completion` IS NOT NULL, CONCAT(`g`.`percentage_completion`,"%"), `g`.`percentage_completion`) AS `kr_percentage_completion`
FROM `ohrm_performance_goal` `g`
LEFT JOIN `ohrm_performance_key_result_metric` ON `ohrm_performance_key_result_metric`.id = `g`.`metric_id`
LEFT JOIN `ohrm_performance_metric_number_format` ON `ohrm_performance_metric_number_format`.id = `ohrm_performance_key_result_metric`.`number_format_id`
LEFT JOIN `ohrm_goal_category` ON `ohrm_goal_category`.`id` = `g`.`goal_category_id`
LEFT JOIN `ohrm_goal_status` ON `ohrm_goal_status`.`state_id` = `g`.`status_id`
LEFT JOIN `ohrm_goal_workflow_status` ON `ohrm_goal_workflow_status`.`state_id` = `g`.`workflow_status_id`
LEFT JOIN `hs_hr_employee` ON `hs_hr_employee`.`emp_number` = `g`.`emp_number`
LEFT JOIN `ohrm_performance_goal` `parent` ON `g`.`root_id` = `parent`.`root_id` AND `parent`.`lft` < `g`.`lft` AND `parent`.`rgt` > `g`.`rgt` AND `parent`.`level` = `g`.`level`-1
WHERE `ohrm_goal_category`.`name` = 'key_result' AND `ohrm_goal_workflow_status`.`consider_as_approved` = 1;