CREATE  TABLE IF NOT EXISTS `ohrm_training_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) DEFAULT NULL ,
  `version` VARCHAR(45) DEFAULT NULL ,
  `company` VARCHAR(255) DEFAULT NULL ,
  `subunit_id` int(11) DEFAULT NULL ,
  `coordinator_id` int(11) DEFAULT NULL ,
  `status` int(11) DEFAULT NULL ,
  `duration` time DEFAULT NULL ,
  `cost` VARCHAR(255) DEFAULT NULL ,
  `currency_id` char(3) DEFAULT NULL ,
  `description` text DEFAULT NULL ,
  `created_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `archived_time` TIMESTAMP DEFAULT 0,
  `archived_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_student` (
  `emp_number` int(11) NOT NULL ,
  `course_id` int(11) NOT NULL ,
  PRIMARY KEY (`emp_number`, `course_id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  `company` VARCHAR(255)  DEFAULT NULL ,
  `cost` VARCHAR(255) DEFAULT NULL ,
  `status` int(11) DEFAULT NULL,
  `code` VARCHAR(255) DEFAULT NULL,
  `currency_id` char(3) DEFAULT NULL ,
  `description` text DEFAULT NULL ,
  `created_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `archived_time` TIMESTAMP DEFAULT 0,
  `archived_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_package` (
  `course_id` int(11) NOT NULL ,
  `package_id` int(11) NOT NULL ,
  PRIMARY KEY (`course_id`, `package_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_student` (
  `emp_number` int(11) NOT NULL ,
  `package_id` int(11) NOT NULL ,
  `due_date` DATE DEFAULT NULL ,
  `status` int(11) NOT NULL ,
  PRIMARY KEY (`emp_number`, `package_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_student_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  `course_id` int(11) DEFAULT NULL ,
  `scheduled_date` DATE DEFAULT NULL ,
  `due_date` DATE DEFAULT NULL ,
  `delivery_method` int(11) DEFAULT NULL ,
  `description` TEXT DEFAULT NULL ,
  `status` int(11) DEFAULT NULL ,
  `duration` VARCHAR(255) DEFAULT NULL ,
  `delivery_location` VARCHAR(255) DEFAULT NULL ,
  `created_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `updated_time` TIMESTAMP DEFAULT 0,
  `updated_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_delivery_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer` (
  `emp_number` int(11) NOT NULL ,
  `session_id` int(11) NOT NULL ,
  `attendance` int(11) DEFAULT NULL ,
  `attend_date` DATE DEFAULT NULL ,
  `rating` int(11) DEFAULT NULL ,
  `comment` TEXT DEFAULT NULL ,
  PRIMARY KEY (`emp_number`, `session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer_rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student` (
  `emp_number` int(11) NOT NULL ,
  `session_id` int(11) NOT NULL ,
  `attendance` int(11) DEFAULT NULL ,
  `attend_date` DATE DEFAULT NULL ,
  `rating` int(11) DEFAULT NULL ,
  `score` INT DEFAULT NULL ,
  `comment` TEXT DEFAULT NULL ,
  PRIMARY KEY (`emp_number`, `session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student_rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `ohrm_course_attachment` (
    `course_id` INT,
    `attach_id` BIGINT,
    `attach_size` INT DEFAULT '0', 
    `attach_desc` VARCHAR(200), 
    `attach_filename` VARCHAR(100), 
    `attach_attachment` LONGBLOB,
    `attach_type` VARCHAR(200), 
    `screen` VARCHAR(100), 
    `attached_by` INT, 
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(course_id,attach_id)
  ) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `ohrm_session_attachment` (
    `session_id` INT,
    `attach_id` BIGINT,
    `attach_size` INT DEFAULT '0', 
    `attach_desc` VARCHAR(200), 
    `attach_filename` VARCHAR(100), 
    `attach_attachment` LONGBLOB,
    `attach_type` VARCHAR(200), 
    `screen` VARCHAR(100), 
    `attached_by` INT, 
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(session_id,attach_id)
  ) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

ALTER TABLE `ohrm_training_session_student`
  ADD CONSTRAINT `ohrm_training_session_student_session` FOREIGN KEY (`session_id`) REFERENCES `ohrm_training_session` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_rating` FOREIGN KEY (`rating`) REFERENCES `ohrm_training_session_student_rating` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_attendance` FOREIGN KEY (`attendance`) REFERENCES `ohrm_training_session_student_attendance` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_session_trainer`
  ADD CONSTRAINT `ohrm_training_session_trainer_session` FOREIGN KEY (`session_id`) REFERENCES `ohrm_training_session` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_rating` FOREIGN KEY (`rating`) REFERENCES `ohrm_training_session_trainer_rating` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_attendance` FOREIGN KEY (`attendance`) REFERENCES `ohrm_training_session_trainer_attendance` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_session`
  ADD CONSTRAINT `ohrm_training_session_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_session_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_delivery_method` FOREIGN KEY (`delivery_method`) REFERENCES `ohrm_training_session_delivery_method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_course` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_updated_user` FOREIGN KEY (`updated_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
ALTER TABLE `ohrm_training_course`
  ADD CONSTRAINT `ohrm_training_course_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_course_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_coordinator` FOREIGN KEY (`coordinator_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_subunit` FOREIGN KEY (`subunit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_currency` FOREIGN KEY (`currency_id`) REFERENCES `hs_hr_currency_type` (`currency_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_archived_user` FOREIGN KEY (`archived_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_package`
  ADD CONSTRAINT `ohrm_training_package_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_archived_user` FOREIGN KEY (`archived_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
ALTER TABLE `ohrm_training_course_student`
  ADD CONSTRAINT `ohrm_training_course_student_emp_number` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_student_course_id` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_course_package`
  ADD CONSTRAINT `ohrm_training_course_package_course_id` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_package_package` FOREIGN KEY (`package_id`) REFERENCES `ohrm_training_package` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_package_student`
  ADD CONSTRAINT `ohrm_training_package_student_course_id` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_student_package` FOREIGN KEY (`package_id`) REFERENCES `ohrm_training_package` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_student_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_package_student_status` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- Insert Data

INSERT INTO `ohrm_user_role` (`name` ,`display_name` ,`is_assignable` ,`is_predefined`)
VALUES ('CourseCoordinator', 'CourseCoordinator', '0', '1'),
('SessionTrainer', 'SessionTrainer', '0', '1'),
('SessionStudent', 'SessionStudent', '0', '1');

INSERT INTO `ohrm_training_course_status` (`id`, `name`) VALUES
(1, 'Active'),
(2, 'Archived');

INSERT INTO `ohrm_training_package_status` (`id`, `name`) VALUES
(1, 'Active'),
(2, 'Archived');

INSERT INTO `ohrm_training_package_student_status` (`id`, `name`) VALUES
(1, 'Pending'),
(2, 'Pass'),
(3, 'Fail');

INSERT INTO `ohrm_training_session_trainer_rating` (`id`, `name`) VALUES
(1, 'Poor'),
(2, 'Fair'),
(3, 'Good'),
(4, 'Very Good'),
(5, 'Excellent');

INSERT INTO `ohrm_training_session_trainer_attendance` (`id`, `name`) VALUES
(1, 'Scheduled'),
(2, 'Yes'),
(3, 'No');

INSERT INTO `ohrm_training_session_student_rating` (`id`, `name`) VALUES
(1, 'Poor'),
(2, 'Fair'),
(3, 'Good'),
(4, 'Very Good'),
(5, 'Excellent');

INSERT INTO `ohrm_training_session_student_attendance` (`id`, `name`) VALUES
(1, 'Scheduled'),
(2, 'Yes'),
(3, 'No');

INSERT INTO `ohrm_training_session_status` (`id`, `name`) VALUES
(1, 'Pending Approval'),
(2, 'Scheduled'),
(3, 'Completed'),
(4, 'Cancelled');

INSERT INTO `ohrm_training_session_delivery_method` (`id`, `name`) VALUES
(1, 'Classroom'),
(2, 'WebEx'),
(3, 'Self-study');

INSERT INTO `ohrm_training_report` (`id`, `name`) VALUES
(20, 'Student Session Information'),
(21, 'Trainer Session Information');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @coordinator_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'CourseCoordinator');
SET @trainer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'SessionTrainer');
SET @session_student_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'SessionStudent');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('training', 1);
SET @training_module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'training'); 

-- Insert Screens

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewCourseList', @training_module_id, 'viewCourseList');
SET @view_course_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddCourse', @training_module_id, 'addCourse');
SET @add_course_screen_id := (SELECT LAST_INSERT_ID()); 

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineCoursePackage', @training_module_id, 'defineCoursePackage');
SET @define_course_package_screen_id := (SELECT LAST_INSERT_ID()); 

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Packages', @training_module_id, 'viewPackageList');
SET @view_packages_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddPackage', @training_module_id, 'addPackage');
SET @add_package_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewSessionList', @training_module_id, 'viewSessionList');
SET @view_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddSession', @training_module_id, 'addSession');
SET @add_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineSessionTrainer', @training_module_id, 'defineSessionTrainer');
SET @define_session_trainer_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineSessionStudent', @training_module_id, 'defineSessionStudent');
SET @define_session_student_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewTrainerSessionList', @training_module_id, 'viewTrainerSessionList');
SET @view_trainer_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditTrainerSession', @training_module_id, 'editTrainerSession');
SET @edit_trainer_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewMyTrainingSessionList', @training_module_id, 'viewMyTrainingSessionList');
SET @view_my_training_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditStudentSession', @training_module_id, 'editStudentSession');
SET @edit_student_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewDefinedReportList', @training_module_id, 'viewDefinedReportList');
SET @view_defined_report_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewTrainingReport', @training_module_id, 'viewTrainingReport');
SET @view_training_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewPackageSessionList', @training_module_id, 'viewPackageSessionList');
SET @view_package_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditPackageSessionList', @training_module_id, 'editPackageSessionList');
SET @edit_package_session_list_screen_id := (SELECT LAST_INSERT_ID());

-- Insert Menu items

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Training', NULL, NULL, 1, 800, '', 1);
SET @training_menu_id := (SELECT LAST_INSERT_ID());

SET @max_report_id = (SELECT MAX(`id`) FROM ohrm_advanced_report);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES 
('Packages', @view_packages_screen_id, @training_menu_id, 2, 100, '', 1),
('Courses', @view_course_screen_id, @training_menu_id, 2, 200, '', 1),
('Sessions', @view_session_screen_id, @training_menu_id, 2, 300, '', 1),
('Trainer Sessions', @view_trainer_session_list_screen_id, @training_menu_id, 2, 400, '', 1),
('My Training Sessions', @view_my_training_session_list_screen_id, @training_menu_id, 2, 500, '', 1),
('Reports', @view_training_report_screen_id, @training_menu_id, 2, 600, concat('?reportId=', @max_report_id + 1), 1);

-- Insert User Role base access to screens

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @view_course_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_course_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_course_package_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_packages_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_package_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @view_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @add_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_session_trainer_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @define_session_trainer_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_session_student_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @define_session_student_screen_id, 1, 1, 1, 1),
(@trainer_role_id, @view_trainer_session_list_screen_id, 1, 1, 1, 1),
(@trainer_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@session_student_role_id, @view_my_training_session_list_screen_id, 1, 1, 1, 1),
(@ess_role_id, @view_my_training_session_list_screen_id, 1, 1, 1, 1),
(@session_student_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_defined_report_list_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_training_report_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_package_session_list_screen_id, 1, 1, 1, 1),
(@admin_role_id, @edit_package_session_list_screen_id, 1, 1, 1, 1);

-- Insert Data Groups

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_course_list', 'Advanced Training - Course List', 1, 1, 1, 1);
SET @manage_course_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_session_list', 'Advanced Training - Session List', 1, 1, 1, 1);
SET @manage_session_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('edit_session_attachments', 'Advanced Training - Edit Session Attachments', 1, 1, 1, 1);
SET @edit_session_attachments_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_trainer_session_list', 'Advanced Training - Trainer Session List', 1, NULL, 1, NULL);
SET @manage_trainer_session_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_my_training_session_list', 'Advanced Training - My Training Session List', 1, NULL, 1, NULL);
SET @manage_my_training_session_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('edit_package_details', 'Advanced Training - Package List', 1, 1, 1, 1);
SET @edit_package_details_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_training_report_details', 'Advanced Training - Manage Report Details', 1, NULL, NULL, NULL);
SET @manage_training_report_details_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('edit_course_attachment', 'Advanced Training - Edit Course Attachment', 1, 1, 1, 1);
SET @edit_course_attachment_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
(@admin_role_id, @manage_course_list_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_course_list_data_group_id, 1, 1, 1, 0, 0),
(@admin_role_id, @manage_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_session_list_data_group_id, 1, 1, 1, 1, 0),
(@trainer_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@session_student_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
(@ess_role_id, @manage_my_training_session_list_data_group_id, 1, 0, 0, 0, 0),
(@trainer_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 0, 0),
(@session_student_role_id, @edit_session_attachments_data_group_id, 1, 0, 0, 0, 0),
(@admin_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @edit_package_details_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @manage_training_report_details_data_group_id, 1, 0, 0, 0, 0),
(@admin_role_id, @edit_course_attachment_data_group_id, 1, 1, 1, 1, 0);


INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@manage_course_list_data_group_id, @view_course_screen_id, 1),
(@manage_course_list_data_group_id, @view_course_screen_id, 2),
(@manage_course_list_data_group_id, @view_course_screen_id, 3),
(@manage_course_list_data_group_id, @view_course_screen_id, 4),
(@manage_course_list_data_group_id, @add_course_screen_id, 1),
(@manage_course_list_data_group_id, @add_course_screen_id, 2),
(@manage_course_list_data_group_id, @add_course_screen_id, 3),
(@manage_course_list_data_group_id, @define_course_package_screen_id, 1),
(@manage_course_list_data_group_id, @define_course_package_screen_id, 2),
(@manage_course_list_data_group_id, @define_course_package_screen_id, 3),
(@manage_session_list_data_group_id, @view_session_screen_id, 1),
(@manage_session_list_data_group_id, @view_session_screen_id, 2),
(@manage_session_list_data_group_id, @view_session_screen_id, 3),
(@manage_session_list_data_group_id, @view_session_screen_id, 4),
(@manage_session_list_data_group_id, @add_session_screen_id, 1),
(@manage_session_list_data_group_id, @add_session_screen_id, 2),
(@manage_session_list_data_group_id, @add_session_screen_id, 3),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 1),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 2),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 3),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 1),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 2),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 3),
(@edit_package_details_data_group_id, @view_packages_screen_id, 1),
(@edit_package_details_data_group_id, @view_packages_screen_id, 2),
(@edit_package_details_data_group_id, @view_packages_screen_id, 3),
(@edit_package_details_data_group_id, @view_packages_screen_id, 4),
(@edit_package_details_data_group_id, @add_package_screen_id, 1),
(@edit_package_details_data_group_id, @add_package_screen_id, 2),
(@edit_package_details_data_group_id, @add_package_screen_id, 3),
(@edit_package_details_data_group_id, @view_package_session_list_screen_id, 1),
(@edit_package_details_data_group_id, @view_package_session_list_screen_id, 2),
(@edit_package_details_data_group_id, @view_package_session_list_screen_id, 3),
(@edit_package_details_data_group_id, @view_package_session_list_screen_id, 4),
(@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 1),
(@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 2),
(@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 3),
(@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 4),
(@manage_trainer_session_list_data_group_id, @view_trainer_session_list_screen_id, 1),
(@manage_trainer_session_list_data_group_id, @view_trainer_session_list_screen_id, 3),
(@manage_trainer_session_list_data_group_id, @edit_trainer_session_screen_id, 1),
(@manage_trainer_session_list_data_group_id, @edit_trainer_session_screen_id, 3),
(@manage_my_training_session_list_data_group_id, @view_my_training_session_list_screen_id, 1),
(@manage_my_training_session_list_data_group_id, @view_my_training_session_list_screen_id, 3),
(@manage_my_training_session_list_data_group_id, @edit_student_session_screen_id, 1),
(@manage_my_training_session_list_data_group_id, @edit_student_session_screen_id, 3),
(@manage_training_report_details_data_group_id, @view_training_report_screen_id, 1);

-- Reports
INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(@max_report_id + 1, "Student Session Information", '
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
    <input_field type="select" name="report_type" label="Report Type" allow-multiple="false">
            <options>
                <excludeAllOption>true</excludeAllOption>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_advanced_report WHERE name in ("Student Session Information", "Trainer Session Information") ORDER BY id</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="emp_numbers" label="Employee Name" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
        <input_field type="select" name="student_session_status" label="Trained Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_student_attendance ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="date_comparision" name="student_trained_date" label="Attend Date" required="false" allow-multiple="false">   
        </input_field>
        <input_field type="select" name="session_names" label="Session Name" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_scheduled_date" label="Session Scheduled Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="date_comparision" name="session_due_date" label="Session Due Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="select" name="delivery_methods" label="Session Delivery Method" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_delivery_method ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="delivery_location" label="Session Delivery Location" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT delivery_location FROM ohrm_training_session ORDER BY delivery_location</query>
                    <value>delivery_location</value>
                    <label>delivery_location</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="session_status" label="Session Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="sesion_created_by" label="Session Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_session s LEFT JOIN ohrm_user u ON u.id = s.created_user_id GROUP BY s.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_created_date" label="Session Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="course_title" label="Course Title" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, title FROM ohrm_training_course ORDER BY title</query>
                    <value>id</value>
                    <label>title</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_version" label="Course Version" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT version FROM ohrm_training_course ORDER BY version</query>
                    <value>version</value>
                    <label>version</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_company" label="Course Company" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT company FROM ohrm_training_course ORDER BY company</query>
                    <value>company</value>
                    <label>company</label>
                </choices>
            </options>
        </input_field>
        <input_field type="sub_unit" name="sub_unit" label="Sub Unit" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name, level FROM ohrm_subunit where id != 1 ORDER BY lft</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="coordinators" label="Session Coordinator" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
    <input_field type="number_comparision" name="course_cost" label="Course Cost" allow-multiple="false">
        </input_field>
    <input_field type="select" name="currency" label="Currency" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT currency_id, currency_name FROM hs_hr_currency_type ORDER BY currency_name</query>
                    <value>currency_id</value>
                    <label>currency_name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_status" label="Course Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_course_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_created_by" label="Course Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_course c LEFT JOIN ohrm_user u ON u.id = c.created_user_id GROUP BY c.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="course_created_date" label="Course Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="package" label="Package" required="true" allow-multiple="false">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_package ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="package_created_by" label="Package Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_package p LEFT JOIN ohrm_user u ON u.id = p.created_user_id GROUP BY p.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="package_created_date" label="Package Created Date" required="false" allow-multiple="false">
        </input_field>
</filter_fields>

<sub_report type="sql" name="pendingQuery">
<query>FROM hs_hr_employee e
    LEFT JOIN ohrm_training_session_student es ON e.emp_number = es.emp_number
    LEFT JOIN ohrm_training_session_student_attendance sst ON sst.id = es.attendance
    LEFT JOIN ohrm_training_session_student_rating ssr ON ssr.id = es.rating
    LEFT JOIN ohrm_training_session s ON s.id = es.session_id
    LEFT JOIN ohrm_training_session_delivery_method dm ON dm.id = s.delivery_method
    LEFT JOIN ohrm_training_session_status ss ON ss.id = s.status
    LEFT JOIN ohrm_user u ON u.id = s.created_user_id
    LEFT JOIN ohrm_training_course c ON c.id = s.course_id
    LEFT JOIN hs_hr_employee cc ON cc.emp_number = c.coordinator_id
    LEFT JOIN ohrm_subunit su ON su.id = c.subunit_id
    LEFT JOIN hs_hr_currency_type ccu ON ccu.currency_id  = c.currency_id
    LEFT JOIN ohrm_training_course_status cs ON cs.id  = c.status
    LEFT JOIN ohrm_user cu ON cu.id = c.created_user_id
    LEFT JOIN ohrm_training_course_package cp ON cp.course_id = c.id
    LEFT JOIN ohrm_training_package p ON p.id = cp.package_id
    LEFT JOIN ohrm_training_package_status ps ON p.status = ps.id
    LEFT JOIN ohrm_user pu ON pu.id = p.created_user_id
WHERE  $X{IN,e.emp_number,emp_numbers} AND
$X{IN,sst.id,student_session_status}  AND 
$X{FROM_WIDGET,es.attend_date,student_trained_date} AND 
$X{IN,s.id,session_names}  AND 
$X{FROM_WIDGET,s.scheduled_date,session_scheduled_date} AND 
$X{FROM_WIDGET,s.due_date,session_due_date} AND
$X{IN,s.delivery_method,delivery_methods} AND
$X{IN,s.delivery_location,delivery_location} AND
$X{IN,s.status,session_status} AND
$X{IN,s.created_user_id,sesion_created_by} AND
$X{FROM_WIDGET,s.created_time,session_created_date} AND
$X{IN,c.id,course_title} AND
$X{IN,c.version,course_version} AND
$X{IN,c.company,course_company} AND
$X{IN,c.subunit_id,sub_unit} AND 
$X{IN,cc.emp_number,coordinators} AND
$X{IN,c.created_user_id,course_created_by} AND
$X{FROM_WIDGET,c.created_time,course_created_date} AND
$X{FROM_WIDGET,c.cost,course_cost} AND
$X{IN,c.currency_id,currency} AND
$X{IN,c.status,course_status} AND
$X{IN,p.id,package} AND
$X{IN,p.created_user_id,package_created_by} AND
$X{FROM_WIDGET,p.created_time,package_created_date} AND
$X{NULL_OR_NOT_NULL,e.termination_id,includeEmployees}
    GROUP BY e.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="employeeInformation" type="one" display="true">
             <group_header></group_header>
             <fields>
                <field display="false">
                    <field_name>e.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>    
                </field>                
                <field display="true">
                    <field_name>CONCAT(e.emp_firstname, " ", e.emp_lastname)</field_name>
                    <field_alias>employeeName</field_alias>
                    <display_name>Employee Name</display_name>
                    <width>150</width>
                </field>
            </fields>
        </display_group>
        <display_group name="mySessionInformation" type="many" display="true" show_header="true">
             <group_header>Student Session Information</group_header>
             <fields>
        <field display="true">
                    <field_name>sst.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Attendance</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>es.attend_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Attend Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>ssr.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Rating</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>es.score</field_name>
                    <field_alias></field_alias>
                    <display_name>Score</display_name>
                    <width>150</width>
                </field>
            </fields>
        </display_group>
        <display_group name="sessionInformation" type="many" display="true" show_header="true">
             <group_header>Session Information</group_header>
             <fields>
               <field display="true">
                    <field_name>s.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Name</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>s.scheduled_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Scheduled Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>s.due_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Due Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>dm.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Delivery Method</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>ss.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>u.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(s.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="courseInformation" type="many" display="true" show_header="true">
             <group_header>Course Information</group_header>
             <fields>
               <field display="true">
                    <field_name>c.title</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Title</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.version</field_name>
                    <field_alias></field_alias>
                    <display_name>Version</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>CONCAT(cc.emp_firstname, " ", cc.emp_lastname)</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Coordinator</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>su.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Subunit</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.company</field_name>
                    <field_alias></field_alias>
                    <display_name>Company</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Cost</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>ccu.currency_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Currency</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cs.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(c.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="packageInformation" type="many" display="true" show_header="true">
             <group_header>Package Information</group_header>
             <fields>
                <field display="true">
                    <field_name>p.code</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Code</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>p.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Name</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>ps.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
                <field display="true">
                    <field_name>p.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Cost</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>pu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>DATE(p.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
            </fields>
        </display_group>
    </display_groups>
</sub_report>
    <page_limit>20</page_limit>       
</report>'),
(@max_report_id + 2, "Trainer Session Information", '
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
    <input_field type="select" name="report_type" label="Report Type" allow-multiple="false">
            <options>
                <excludeAllOption>true</excludeAllOption>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_advanced_report WHERE name in ("Student Session Information", "Trainer Session Information") ORDER BY id</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="emp_numbers" label="Employee Name" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
        <input_field type="select" name="student_session_status" label="Trained Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_trainer_attendance ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="date_comparision" name="student_trained_date" label="Attend Date" required="false" allow-multiple="false">   
        </input_field>
        <input_field type="select" name="session_names" label="Session Name" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_scheduled_date" label="Session Scheduled Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="date_comparision" name="session_due_date" label="Session Due Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="select" name="delivery_methods" label="Session Delivery Method" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_delivery_method ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="delivery_location" label="Session Delivery Location" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT delivery_location FROM ohrm_training_session ORDER BY delivery_location</query>
                    <value>delivery_location</value>
                    <label>delivery_location</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="session_status" label="Session Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="sesion_created_by" label="Session Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_session s LEFT JOIN ohrm_user u ON u.id = s.created_user_id GROUP BY s.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_created_date" label="Session Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="course_title" label="Course Title" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, title FROM ohrm_training_course ORDER BY title</query>
                    <value>id</value>
                    <label>title</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_version" label="Course Version" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT version FROM ohrm_training_course ORDER BY version</query>
                    <value>version</value>
                    <label>version</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_company" label="Course Company" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT company FROM ohrm_training_course ORDER BY company</query>
                    <value>company</value>
                    <label>company</label>
                </choices>
            </options>
        </input_field>
        <input_field type="sub_unit" name="sub_unit" label="Sub Unit" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name, level FROM ohrm_subunit where id != 1 ORDER BY lft</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="coordinators" label="Session Coordinator" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
    <input_field type="number_comparision" name="course_cost" label="Course Cost" allow-multiple="false">
        </input_field>
    <input_field type="select" name="currency" label="Currency" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT currency_id, currency_name FROM hs_hr_currency_type ORDER BY currency_name</query>
                    <value>currency_id</value>
                    <label>currency_name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_status" label="Course Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_course_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_created_by" label="Course Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_course c LEFT JOIN ohrm_user u ON u.id = c.created_user_id GROUP BY c.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="course_created_date" label="Course Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="package" label="Package" required="true" allow-multiple="false">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_package ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="package_created_by" label="Package Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_package p LEFT JOIN ohrm_user u ON u.id = p.created_user_id GROUP BY p.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="package_created_date" label="Package Created Date" required="false" allow-multiple="false">
        </input_field>
</filter_fields>

<sub_report type="sql" name="pendingQuery">
<query>FROM hs_hr_employee e
    LEFT JOIN ohrm_training_session_trainer es ON e.emp_number = es.emp_number
    LEFT JOIN ohrm_training_session_trainer_attendance sst ON sst.id = es.attendance
    LEFT JOIN ohrm_training_session_trainer_rating ssr ON ssr.id = es.rating
    LEFT JOIN ohrm_training_session s ON s.id = es.session_id
    LEFT JOIN ohrm_training_session_delivery_method dm ON dm.id = s.delivery_method
    LEFT JOIN ohrm_training_session_status ss ON ss.id = s.status
    LEFT JOIN ohrm_user u ON u.id = s.created_user_id
    LEFT JOIN ohrm_training_course c ON c.id = s.course_id
    LEFT JOIN hs_hr_employee cc ON cc.emp_number = c.coordinator_id
    LEFT JOIN ohrm_subunit su ON su.id = c.subunit_id
    LEFT JOIN hs_hr_currency_type ccu ON ccu.currency_id  = c.currency_id
    LEFT JOIN ohrm_training_course_status cs ON cs.id  = c.status
    LEFT JOIN ohrm_user cu ON cu.id = c.created_user_id
    LEFT JOIN ohrm_training_course_package cp ON cp.course_id = c.id
    LEFT JOIN ohrm_training_package p ON p.id = cp.package_id
    LEFT JOIN ohrm_training_package_status ps ON p.status = ps.id
    LEFT JOIN ohrm_user pu ON pu.id = p.created_user_id
WHERE  $X{IN,e.emp_number,emp_numbers} AND
$X{IN,sst.id,student_session_status}  AND 
$X{FROM_WIDGET,es.attend_date,student_trained_date} AND 
$X{IN,s.id,session_names}  AND 
$X{FROM_WIDGET,s.scheduled_date,session_scheduled_date} AND 
$X{FROM_WIDGET,s.due_date,session_due_date} AND
$X{IN,s.delivery_method,delivery_methods} AND
$X{IN,s.delivery_location,delivery_location} AND
$X{IN,s.status,session_status} AND
$X{IN,s.created_user_id,sesion_created_by} AND
$X{FROM_WIDGET,s.created_time,session_created_date} AND
$X{IN,c.id,course_title} AND
$X{IN,c.version,course_version} AND
$X{IN,c.company,course_company} AND
$X{IN,c.subunit_id,sub_unit} AND 
$X{IN,cc.emp_number,coordinators} AND
$X{IN,c.created_user_id,course_created_by} AND
$X{FROM_WIDGET,c.created_time,course_created_date} AND
$X{FROM_WIDGET,c.cost,course_cost} AND
$X{IN,c.currency_id,currency} AND
$X{IN,c.status,course_status} AND
$X{IN,p.id,package} AND
$X{IN,p.created_user_id,package_created_by} AND
$X{FROM_WIDGET,p.created_time,package_created_date} AND
$X{NULL_OR_NOT_NULL,e.termination_id,includeEmployees}
    GROUP BY e.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="employeeInformation" type="one" display="true">
             <group_header></group_header>
             <fields>
                <field display="false">
                    <field_name>e.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>    
                </field>                
                <field display="true">
                    <field_name>CONCAT(e.emp_firstname, " ", e.emp_lastname)</field_name>
                    <field_alias>employeeName</field_alias>
                    <display_name>Employee Name</display_name>
                    <width>150</width>
                </field>
            </fields>
        </display_group>
        <display_group name="mySessionInformation" type="many" display="true" show_header="true">
             <group_header>Trainer Session Information</group_header>
             <fields>
        <field display="true">
                    <field_name>sst.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Attendance</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>es.attend_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Attend Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>ssr.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Rating</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
            </fields>
        </display_group>
        <display_group name="sessionInformation" type="many" display="true" show_header="true">
             <group_header>Session Information</group_header>
             <fields>
               <field display="true">
                    <field_name>s.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Name</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>s.scheduled_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Scheduled Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>s.due_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Due Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>dm.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Delivery Method</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>ss.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>u.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(s.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="courseInformation" type="many" display="true" show_header="true">
             <group_header>Course Information</group_header>
             <fields>
               <field display="true">
                    <field_name>c.title</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Title</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.version</field_name>
                    <field_alias></field_alias>
                    <display_name>Version</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>CONCAT(cc.emp_firstname, " ", cc.emp_lastname)</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Coordinator</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>su.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Subunit</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.company</field_name>
                    <field_alias></field_alias>
                    <display_name>Company</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Cost</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>ccu.currency_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Currency</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cs.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(c.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="packageInformation" type="many" display="true" show_header="true">
             <group_header>Package Information</group_header>
             <fields>
        <field display="true">
                    <field_name>p.code</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Code</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>p.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Name</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>ps.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>p.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Cost</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>pu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>DATE(p.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
            </fields>
        </display_group>
    </display_groups>
</sub_report>
    <page_limit>20</page_limit>       
</report>');
