
CREATE TABLE  `ohrm_reports_catalog` (
`id` INT NOT NULL AUTO_INCREMENT ,
`report_id` INT NOT NULL ,
`data_group_id` INT NOT NULL ,
`active` TINYINT(1) NOT NULL DEFAULT '1' ,
PRIMARY KEY (`id`),
 CONSTRAINT `fk_ohrm_reports_catalog_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_reports_catalog_data_group_id` FOREIGN KEY (`data_group_id`) REFERENCES `ohrm_data_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE = InnoDB;

SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'admin');
SET @global_admin_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Reports Catalog', @module_id , 'reportsCatalog');

SET @view_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@global_admin_id, @view_report_screen_id, 1, 1, 1, 1);

SET @user_role_type_id := (SELECT  `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(@user_role_type_id, @view_report_screen_id);

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status` ) VALUES (NULL, 'Reports Catalog', NULL, @view_report_screen_id, NULL, '1', '2750', NULL, NULL, 'admin.reports_catalog', '1');


Drop view if EXISTS  documents ;

CREATE VIEW documents AS
SELECT
    da.id,
    'documents' AS TYPE,
    da.`topic`,
    da.`description`,
    da.`published_date`,
    da.`status`,
    da.`is_acknowledgable`,
    dapl.location_id,
    daps.sub_unit_id,
    dapur.user_role_id,
    dapjt.job_title_id,
    dapes.employee_status_id,
    dapee.employee_id
FROM
    ohrm_document_article da
LEFT JOIN ohrm_document_article_published_location dapl ON
    dapl.document_id = da.id
LEFT JOIN ohrm_document_article_published_sub_units daps ON
    daps.document_id = da.id
LEFT JOIN ohrm_document_article_published_user_roles dapur ON
    dapur.document_id = da.id
LEFT JOIN ohrm_document_article_published_job_titles dapjt ON
    dapjt.document_id = da.id
LEFT JOIN ohrm_document_article_published_employee_status dapes ON
    dapes.document_id = da.id
LEFT JOIN ohrm_document_article_published_employee dapee ON
    dapee.document_id = da.id
WHERE da.deleted_at IS NULL
UNION
SELECT
    da.id,
    'news' AS TYPE,
    da.`topic`,
    da.`description`,
    da.`published_date`,
    da.`status`,
    da.`is_acknowledgable`,
    dapl.location_id,
    daps.sub_unit_id,
    dapur.user_role_id,
    dapjt.job_title_id,
    dapes.employee_status_id,
    dapee.employee_id
FROM
    ohrm_news_article da
LEFT JOIN ohrm_news_article_published_location dapl ON
    dapl.news_id = da.id
LEFT JOIN ohrm_news_article_published_sub_units daps ON
    daps.news_id = da.id
LEFT JOIN ohrm_news_article_published_user_roles dapur ON
    dapur.news_id = da.id
LEFT JOIN ohrm_news_article_published_job_titles dapjt ON
    dapjt.news_id = da.id
LEFT JOIN ohrm_news_article_published_employee_status dapes ON
    dapes.news_id = da.id
LEFT JOIN ohrm_news_article_published_employee dapee ON
    dapee.news_id= da.id
WHERE da.deleted_at IS NULL ;

Drop view if EXISTS  ohrm_documents_view;

CREATE VIEW ohrm_documents_view AS SELECT
    CONCAT(d.type, '-', d.id) AS entity_id,
    d.*,
    ack.is_acknowledge,
    ack.date,
    IF(ack.is_acknowledge = 1, "1", "2") AS acknowledged,
    e.emp_number AS emp_number,
    e.emp_firstname,
    e.emp_middle_name,
    e.emp_lastname,
    e.employee_id AS emp_id,
    uur.user_role_id emp_user_role
FROM
    `hs_hr_employee` e
LEFT JOIN hs_hr_emp_locations el ON
    el.emp_number = e.emp_number
LEFT JOIN ohrm_user ur ON
    ur.emp_number = e.emp_number
LEFT JOIN ohrm_user_user_role uur ON
    ur.id = uur.user_id
CROSS JOIN documents d LEFT JOIN ohrm_acknowledgement ack ON
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
WHERE
    uur.user_role_type_id NOT IN(3) AND(
        d.location_id = el.location_id OR d.location_id IS NULL
    ) AND(
        d.sub_unit_id = e.work_station OR d.sub_unit_id IS NULL
    ) AND(
        d.user_role_id = uur.user_role_id OR d.user_role_id IS NULL
    ) AND(
        d.job_title_id = e.job_title_code OR d.job_title_id IS NULL
    ) AND(
        d.employee_status_id = e.emp_status OR d.employee_status_id IS NULL
    ) AND(
        d.employee_id = e.`emp_number` OR d.employee_id IS NULL
    ) OR(
        d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
    )
UNION
SELECT
    CONCAT(d.type, '-', d.id) AS entity_id,
    d.*,
    ack.is_acknowledge,
    ack.date,
    IF(ack.is_acknowledge = 1, "1", "2") AS acknowledged,
    e.emp_number AS emp_number,
    e.emp_firstname,
    e.emp_middle_name,
    e.emp_lastname,
    e.employee_id AS emp_id,
    uur.user_role_id emp_user_role
FROM
    `hs_hr_employee` e
LEFT JOIN hs_hr_emp_locations el ON
    el.emp_number = e.emp_number
LEFT JOIN ohrm_user ur ON
    ur.emp_number = e.emp_number
LEFT JOIN ohrm_user_user_role uur ON
    ur.id = uur.user_id
CROSS JOIN documents d LEFT JOIN ohrm_acknowledgement ack ON
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
WHERE
    uur.user_role_type_id IN(3) AND e.emp_number IN(
    SELECT
        `erep_sup_emp_number`
    FROM
        `hs_hr_emp_reportto`
) AND(
    d.location_id = el.location_id OR d.location_id IS NULL
) AND(
    d.sub_unit_id = e.work_station OR d.sub_unit_id IS NULL
) AND(
    d.user_role_id = uur.user_role_id OR d.user_role_id IS NULL
) AND(
    d.job_title_id = e.job_title_code OR d.job_title_id IS NULL
) AND(
    d.employee_status_id = e.emp_status OR d.employee_status_id IS NULL
) AND(
    d.employee_id = e.`emp_number` OR d.employee_id IS NULL
) OR(
    d.id = ack.entity_id AND d.`type` = ack.`entity_type` AND e.`emp_number` = ack.`employee_id`
);

SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'news');
SET @global_admin_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Announcement Acknowledgement Report', @module_id , 'announcementAcknowledgementReport');

SET @view_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@global_admin_id , @view_report_screen_id, 1, 1, 1, 1);


SET @parent_menu_Id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Report Catalog');

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`,`uisref`) VALUES
('Acknowledgement Report', @view_report_screen_id, @parent_menu_Id, 2, 2000, '', 0 ,'report_view({"id": "Employee_Acknowledgment_Report", "filters": null})');


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('announcement_acknowledgement_report', 'Reports Catalog - Employee Acknowledgements Report', 1, 0, 0, 0);
SET @report_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
 VALUES (@global_admin_id, @report_data_group_id, '1', '0', '0', '0', '0');


INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@report_data_group_id, @view_report_screen_id, 1);

INSERT INTO `ohrm_advanced_report` ( `name`, `unique_name`, `definition`, `enabled`) VALUES ('Employee Acknowledgements Report', 'Employee_Acknowledgment_Report', '', '1');

SET @report_id := (SELECT id FROM `ohrm_advanced_report` WHERE `unique_name` = 'Employee_Acknowledgment_Report');

INSERT INTO `ohrm_reports_catalog` (  `report_id`, `data_group_id`, `active`) VALUES (  @report_id, @report_data_group_id, '1');

SET @user_role_type_id := (SELECT  `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@user_role_type_id,@report_data_group_id , '1', NULL, NULL, NULL);


