CREATE VIEW `vw_employee` AS select `t`.`head_photo` AS `ITEM_pirture`,`t`.`sex` AS `ITEM_sex`,`t`.`id` AS `ITEM_userID`,`t`.`name` AS `ITEM_securityName`,(select `d1`.`name` from (`tenant_departments` `d1` join `tenant_department_employee_relations` `de` on((`d1`.`id` = `de`.`department_id`))) limit 1) AS `ITEM_DeptName`,`e`.`occupation_type` AS `ITEM_jobType`,`duc`.`number` AS `ITEM_documentID`,`t`.`telephone` AS `ITEM_phone`,`t`.`birthdate` AS `ITEM_birthdate`,'' AS `ITEM_nationality`,`t`.`education` AS `ITEM_education`,`t`.`stature` AS `ITEM_height`,`t`.`nation` AS `ITEM_nation`,`t`.`politics_status` AS `ITEM_politicalOutlook`,`t`.`household_type` AS `ITEM_householdType`,`duc`.`address` AS `ITEM_homeAddress`,`t`.`military_status` AS `ITEM_isVeteran`,'' AS `ITEM_workYears`,`t`.`checked_status` AS `ITEM_isCertified`,`duc`.`front_photo` AS `ITEM_IDFacePhoto`,`duc`.`reverse_photo` AS `ITEM_IDNationPhoto`,`duc`.`head_photo` AS `ITEM_IDHeaderPhoto`,`t`.`one_inch_color_white_photo` AS `ITEM_headPhoto`,`e`.`status` AS `ITEM_isInMyCompany`,`e`.`hired_date` AS `ITEM_entryDate`,`e`.`leave_time` AS `ITEM_leaveDate`,`e`.`organization_id` AS `DOMAINID`,'' AS `ITEM_insure`,`t`.`authenticated_status` AS `ITEM_shiming`,`t`.`marital_status` AS `ITEM_maritalStatus`,`duc`.`address` AS `ITEM_IDAddress`,'' AS `attendanceSiteStatus`,'' AS `licenseLevel`,`org`.`name` AS `ITEM_companyName`,`t`.`lon_lat_json` AS `ITEM_LonLatJson` from (((`tenant_users` `t` join `tenant_employees` `e` on((`t`.`id` = `e`.`tenant_user_id`))) join `tenant_user_credentials` `duc` on((`duc`.`id` = `t`.`selected_credential_id`))) join `tenant_organizations` `org` on((`org`.`id` = `e`.`organization_id`))) ;

CREATE VIEW `vm_export_employee` AS select `t1`.`id` AS `id`,`t2`.`name` AS `name`,`t2`.`telephone` AS `telephone`,`t5`.`credential_type` AS `credential_type`,`t5`.`number` AS `number`,`t2`.`checked_status` AS `checked_status`,`t2`.`authenticated_status` AS `authenticated_status`,`t4`.`name` AS `depart_name`,`t1`.`occupation_type` AS `occupation_type`,`tu`.`name` AS `superior_name`,`t1`.`position` AS `position`,`t1`.`hired_date` AS `hired_date`,`t1`.`positive_date` AS `positive_date`,`t1`.`organization_id` AS `organ_id`,`t2`.`sex` AS `sex`,`t2`.`birthdate` AS `birthdate`,`t2`.`nation` AS `nation`,`t2`.`education` AS `education`,`t2`.`politics_status` AS `politics_status`,`t2`.`household_type` AS `household_type`,`t2`.`stature` AS `stature`,`t2`.`native_place` AS `native_place`,`t2`.`military_status` AS `military_status`,`t2`.`marital_status` AS `marital_status`,`t5`.`valid_date_from` AS `valid_date_from`,`t5`.`valid_date_to` AS `valid_date_to`,`t5`.`address` AS `address`,max(if(isnull(`t9`.`id`),0,1)) AS `contract_status`,max(if(isnull(`t6`.`number`),0,1)) AS `cer_status`,`t6`.`number` AS `cer_no`,`t1`.`insure` AS `insure`,`t1`.`created_time` AS `created_time`,`t1`.`is_domain_admin` AS `is_domain_admin`,max(if(isnull(`t8`.`id`),0,1)) AS `depar_admin`,group_concat(distinct `t8`.`name` separator ',') AS `manage_departs` from (((((((((`tenant_employees` `t1` join `tenant_users` `t2` on((`t1`.`tenant_user_id` = `t2`.`id`))) left join `tenant_employees` `t3` on((`t1`.`superior_id` = `t3`.`id`))) left join `tenant_users` `tu` on((`t3`.`tenant_user_id` = `tu`.`id`))) left join `tenant_departments` `t4` on((`t1`.`department_id` = `t4`.`id`))) left join `tenant_user_credentials` `t5` on((`t2`.`selected_credential_id` = `t5`.`id`))) left join `tenant_user_credentials` `t6` on(((`t2`.`id` = `t6`.`tenant_user_id`) and (`t6`.`credential_type` = 7)))) left join `tenant_department_admins` `t7` on((`t1`.`id` = `t7`.`employee_id`))) left join `tenant_departments` `t8` on((`t7`.`department_id` = `t8`.`id`))) left join `tenant_contracts` `t9` on(((`t1`.`id` = `t9`.`employee_id`) and (`t9`.`status` = 1)))) where (`t1`.`status` <> 1) group by `t1`.`id` ;