CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `staff_basic` AS
    select 
        `u`.`login_id` AS `login_id`,
        `s`.`surname` AS `surname`,
        `s`.`firstname` AS `firstname`,
        `u`.`chinese_name` AS `chinese_name`,
        `u`.`gender` AS `gender`,
        `s`.`position` AS `position`,
        `u`.`role` AS `role`,
        `s`.`marital_status` AS `marital_status`,
        `g`.`id` AS `group_id`,
        `s`.`is_full_time` AS `is_full_time`,
        `s`.`responsibility` AS `responsibility`,
        `u`.`mobile` AS `mobile`,
        `s`.`tel` AS `tel`,
        `s`.`first_aids_no` AS `first_aids_no`,
        `s`.`first_aids_date` AS `first_aids_date`,
        `s`.`kg_date` AS `kg_date`,
        `s`.`kg_no` AS `kg_no`,
        `s`.`entry_date` AS `entry_date`,
        `s`.`turnover_date` AS `turnover_date`,
        `s`.`seniority_before_entry` AS `seniority_before_entry`,
        `s`.`address` AS `address`,
        `u`.`email` AS `email`,
        `s`.`photo` AS `photo`,
        `u`.`english_name` AS `english_name`,
        `s`.`religion` AS `religion`,
        `s`.`religion_date` AS `religion_date`,
        `u`.`class_id` as `class_id`
    from
        (((`users` `u`
        left join `staff` `s` ON ((`u`.`login_id` = `s`.`login_id`)))
        left join `user_group` `ug` ON (((`ug`.`login_id` = `u`.`login_id`)
            and (`ug`.`year` = (select 
                `setting`.`setting_value`
            from
                `setting`
            where
                (`setting`.`setting_name` = 'current_year'))))))
        left join `groups` `g` ON (((`g`.`id` = `ug`.`group_id`)
            and (`g`.`related_class` is not null))))
    where
        (`u`.`role` not in (1 , 2));
