SET @system_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'SYSTEM');
SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_role_id` = @system_user_role_id);

SET @folder_id = (SELECT id FROM ohrm_report_folder WHERE name = 'Request Desk');

UPDATE `ohrm_advanced_report`
SET `folder_id` = @folder_id, `custom_url` = 'report_view({"id": "helpdesk_hiring_requisition_detailed_report", "filters": null})', `created_by`= @system_user_id
WHERE `unique_name` = 'helpdesk_hiring_requisition_detailed_report';

UPDATE `ohrm_advanced_report`
SET `folder_id` = @folder_id, `custom_url` = 'report_view({"id": "helpdesk_general_request_detailed_report", "filters": null})', `created_by`= @system_user_id
WHERE `unique_name` = 'helpdesk_general_request_detailed_report';

UPDATE `ohrm_advanced_report` SET `custom_url` = '/#/help_desk/dashboard' WHERE `ohrm_advanced_report`.`unique_name` = 'helpdesk_request_dashboard_summary_1';

DELIMITER $$
DROP FUNCTION IF EXISTS get_requestdesk_custom_field_value$$

CREATE FUNCTION get_requestdesk_custom_field_value(requestid int,customfieldid int) RETURNS TEXT
    DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE result_value TEXT;
    DECLARE stringValue varchar(255);
    DECLARE textValue TEXT;
    DECLARE numberValue double;
    DECLARE dateValue DATE;
    SET result_value = NULL;
    SET stringValue = (SELECT `string_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);
    SET textValue = (SELECT `text_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);
    SET numberValue = (SELECT `number_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);
    SET dateValue = (SELECT `date_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);
    IF stringValue IS NOT NULL THEN
        SET result_value = stringValue;
    ELSEIF textValue IS NOT NULL THEN
        SET result_value = textValue;
    ELSEIF numberValue IS NOT NULL THEN
        SET result_value = CONCAT(numberValue);
    ELSEIF dateValue IS NOT NULL THEN
        SET result_value = DATE_FORMAT(dateValue, "%Y-%m-%d");
    END IF;
    RETURN (result_value);
END$$

DELIMITER $$
DROP FUNCTION IF EXISTS get_requestdesk_approver_name$$

CREATE FUNCTION get_requestdesk_approver_name(requestid int,aproverlevel int) RETURNS TEXT
    DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE resultValue TEXT;
    DECLARE entityProcessId INT(11);
    DECLARE requestProcessId INT(11);
    DECLARE actorTypeId INT(11);
    DECLARE workflowRequestId INT(11);
    DECLARE empNumber INT(11);
    DECLARE fname VARCHAR(100);
    DECLARE lname VARCHAR(100);
    DECLARE terminationId INT(4);
    SET entityProcessId = (SELECT id  FROM `ohrm_advanced_workflow_entity_process` WHERE `entity` LIKE 'Request' LIMIT 1);
    SET workflowRequestId = (SELECT workflow_request_id  FROM `ohrm_request` WHERE `id` = requestid);
    SET requestProcessId = (SELECT id FROM `ohrm_advanced_workflow_request_process` WHERE `request_id` = workflowRequestId AND `entity_process_id` = entity_process_id LIMIT 1);
    SET actorTypeId = (SELECT id  FROM `ohrm_advanced_workflow_entity_process_actor_type` WHERE `entity_process_id` = entityProcessId AND `name` LIKE 'RequestApprover');
    SET empNumber = (SELECT emp_number FROM `ohrm_advanced_workflow_request_process_actor` WHERE `request_process_id` = requestProcessId AND `level` = aproverlevel AND `actor_type_id` = actorTypeId LIMIT 1);
    SET fname = (SELECT emp_firstname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET lname = (SELECT emp_lastname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET terminationId = (SELECT termination_id FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET resultValue = (SELECT CONCAT_WS(" ", fname, lname, IF(terminationId IS NULL, '', ' (Past Employee)')));
RETURN (resultValue);
END$$