v_employee_with_dept - 数据库字典

v_employee_with_dept 视图 · 24 字段

VIEW

视图定义

CREATE ALGORITHM=UNDEFINED DEFINER=`neuron_new`@`%` SQL SECURITY DEFINER VIEW `v_employee_with_dept` AS select `e`.`id` AS `employee_id`,`e`.`userid` AS `dingtalk_userid`,`e`.`name` AS `employee_name`,`e`.`mobile` AS `mobile_phone`,`e`.`email` AS `email`,`e`.`title` AS `position`,`e`.`job_number` AS `job_number`,`e`.`hired_date` AS `hired_date`,`e`.`work_place` AS `work_place`,`e`.`remark` AS `remark`,`e`.`active` AS `is_active`,`e`.`admin` AS `is_admin`,`e`.`boss` AS `is_boss`,`e`.`leader` AS `is_leader`,cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned) AS `main_dept_id`,(select substring_index(`d`.`full_path`,'/',1) from `department_mapping` `d` where ((`d`.`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` = cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned)) and (`d`.`is_active` = 1)) limit 1) AS `所属部门`,(select `d`.`full_path` from `department_mapping` `d` where ((`d`.`dept_id` = cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned)) and (`d`.`is_active` = 1)) limit 1) AS `dept_full_path`,(select `d`.`dept_level` from `department_mapping` `d` where ((`d`.`dept_id` = cast(json_extract(`e`.`dept_id_list`,'$[0]') as unsigned)) and (`d`.`is_active` = 1)) limit 1) AS `main_dept_level`,`e`.`dept_id_list` AS `dept_id_list`,`e`.`sync_source` AS `sync_source`,`e`.`sync_time` AS `sync_time`,`e`.`created_at` AS `created_at`,`e`.`updated_at` AS `updated_at` from `dingtalk_employee_raw` `e`

字段信息

字段名	类型	必填	说明	关联关系
employee_id	int(11)	否	主键ID	-
dingtalk_userid	varchar(100)	否	钉钉用户ID-唯一标识	-
employee_name	varchar(100)	否	员工姓名	-
mobile_phone	varchar(20)	否	手机号	v_employee_with_dept.mobile_phone ↔ employee_roster.mobile_phonev_employee_with_dept.mobile_phone → crm_member_orders.sales_id
email	varchar(100)	是	邮箱地址	-
position	varchar(100)	是	职位	-
job_number	varchar(50)	是	工号	-
hired_date	datetime	是	入职日期-从毫秒时间戳转换	-
work_place	varchar(200)	是	工作地点	-
remark	varchar(500)	是	备注	-
is_active	tinyint(1)	是	是否激活-0否-1是	-
is_admin	tinyint(1)	是	是否管理员-0否-1是	-
is_boss	tinyint(1)	是	是否老板-0否-1是	-
is_leader	tinyint(1)	是	是否领导-0否-1是	-
main_dept_id	bigint(21) unsigned	是	-	-
所属公司	varchar(500)	是	-	-
所属部门	varchar(500)	是	-	-
dept_full_path	varchar(500)	是	-	-
main_dept_level	bigint(11)	是	-	-
dept_id_list	json	是	部门ID列表-JSON数组格式-如[567655024,657421344,1]	-
sync_source	varchar(50)	是	数据来源-list=批量接口-get=详情接口-webhook=钉钉推送	-
sync_time	datetime	是	同步时间	-
created_at	datetime	是	创建时间	-
updated_at	datetime	是	更新时间	-

索引信息

无索引