v_钉钉员工表 视图

CREATE ALGORITHM=UNDEFINED DEFINER=`neuron_new`@`%` SQL SECURITY DEFINER VIEW `v_钉钉员工表` AS select `e`.`name` AS `员工姓名`,`e`.`mobile` AS `手机号`,(select substring_index(`d`.`full_path`,'/',1) from `department_mapping` `d` where ((`d`.`dept_id` = coalesce(`e`.`main_dept_id`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned))) and (`d`.`is_active` = 1)) limit 1) AS `所属公司`,(select substring_index(`d`.`full_path`,'/',-(1)) from `department_mapping` `d` where ((`d`.`dept_id` = coalesce(`e`.`main_dept_id`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned))) and (`d`.`is_active` = 1)) limit 1) AS `所属部门`,`e`.`userid` AS `钉钉用户ID`,`e`.`email` AS `邮箱`,`e`.`title` AS `职位`,`e`.`job_number` AS `工号`,`e`.`hired_date` AS `入职日期`,`e`.`work_place` AS `工作地点`,`e`.`remark` AS `备注`,`e`.`active` AS `是否在职`,`e`.`admin` AS `是否管理员`,`e`.`boss` AS `是否老板`,`e`.`leader` AS `是否领导`,coalesce(`e`.`main_dept_id`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned)) AS `主部门ID`,(select `d`.`full_path` from `department_mapping` `d` where ((`d`.`dept_id` = coalesce(`e`.`main_dept_id`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned))) and (`d`.`is_active` = 1)) limit 1) AS `部门完整路径`,(select `d`.`dept_level` from `department_mapping` `d` where ((`d`.`dept_id` = coalesce(`e`.`main_dept_id`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned))) and (`d`.`is_active` = 1)) limit 1) AS `部门层级`,`e`.`dept_id_list` AS `所有部门ID列表`,`e`.`sync_source` AS `同步来源`,`e`.`sync_time` AS `同步时间`,`e`.`created_at` AS `创建时间`,`e`.`updated_at` AS `更新时间` from `dingtalk_employee_raw` `e`