create view sync_employee_view
as
select
    `id`, `domain_id`, `id_face_photo`, `id_header_photo`, `id_nation_photo`, `last_auth_time`, `attendance_site_status`, `birth_date`, `blood_type`, `certification`, `company_name`, `created_time`, `credential_number`, `credential_type`, `data_restrict`, `depart_id`, `depart_name`, `disease_history`, `document_id_bool`, `education`, `emergency_contact`, `emergency_phone`, `hired_date`, `home_address`, `household_type`, `id_address`, `id_validation_e`, `id_validation_s`, `insure`, `is_certified`, `is_deleted`, `is_in_my_company`, `is_in_mycompany`, `is_state`, `is_veteran`, `job_type`, `last_search_past_time`, `last_updated_time`, `leave_date`, `license_level`, `location`, `military_status`, `name`, `nation`, `nationality`, `occupation_type`, `one_inch_color_white_photo`, `organization_id`, `politics_status`, `remark`, `sex`, `shi_ming`, `stature`, `superior_id`, `telephone`, `tenant_user_id`, `two_inch_color_blue_photo`, `version`, `work_years`,`last_updated_time` as LASTMODIFIED
from report_employees;

create view sync_department_view
as
select
    `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time` as LASTMODIFIED, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`,`organization_id` as domainId
from tenant_departments a;



alter view vw_employee
as
SELECT
    `t`.`head_photo` AS `ITEM_pirture`,
    `t`.`sex` AS `ITEM_sex`,
    `e`.`id` AS `ITEM_userID`,
    `t`.`name` AS `ITEM_securityName`,
    `dt`.`name`  AS `ITEM_DeptName`,
    `dt`.`id` 	AS `ITEM_DeptId`,
    `e`.`occupation_type` AS `ITEM_jobType`,
    `duc`.`credential_type` AS `ITEM_documentType`,
    `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
    `obpm2`.`tenant_users` `t`
        JOIN `obpm2`.`tenant_employees` `e` ON `t`.`id` = `e`.`tenant_user_id`
        join  `obpm2`.`tenant_user_credentials` duc on `duc`.`id` = `t`.`selected_credential_id`
        join `obpm2`.`tenant_organizations` `org` on `org`.`id` = `e`.`organization_id`
        join obpm2.tenant_departments dt on dt.ID= e.department_id

/**
  其他
 */
select `report_employees`.`id` AS `id`,`report_employees`.`creator_created_time` AS `creator_created_time`,`report_employees`.`creator_id` AS `creator_id`,`report_employees`.`creator_name` AS `creator_name`,`report_employees`.`last_modifier_created_time` AS `last_modifier_created_time`,`report_employees`.`last_modifier_id` AS `last_modifier_id`,`report_employees`.`last_modifier_name` AS `last_modifier_name`,`report_employees`.`organ_id` AS `organ_id`,`report_employees`.`organ_id` AS `domainId`,`report_employees`.`idface_photo` AS `idface_photo`,`report_employees`.`idheader_photo` AS `idheader_photo`,`report_employees`.`idnation_photo` AS `idnation_photo`,`report_employees`.`last_auth_time` AS `last_auth_time`,`report_employees`.`attendance_site_status` AS `attendance_site_status`,`report_employees`.`birth_date` AS `birth_date`,`report_employees`.`blood_type` AS `blood_type`,`report_employees`.`certification` AS `certification`,`report_employees`.`company_name` AS `company_name`,`report_employees`.`credential_number` AS `credential_number`,`report_employees`.`credential_type` AS `credential_type`,`report_employees`.`data_restrict` AS `data_restrict`,`report_employees`.`depart_id` AS `depart_id`,`report_employees`.`depart_name` AS `depart_name`,`report_employees`.`disease_history` AS `disease_history`,`report_employees`.`documentidbool` AS `documentidbool`,`report_employees`.`education` AS `education`,`report_employees`.`emergency_contact` AS `emergency_contact`,`report_employees`.`emergency_phone` AS `emergency_phone`,`report_employees`.`hired_date` AS `hired_date`,`report_employees`.`home_address` AS `home_address`,`report_employees`.`household_type` AS `household_type`,`report_employees`.`id_address` AS `id_address`,`report_employees`.`id_validatione` AS `id_validatione`,`report_employees`.`id_validations` AS `id_validations`,`report_employees`.`insure` AS `insure`,`report_employees`.`is_certified` AS `is_certified`,`report_employees`.`is_in_my_company` AS `is_in_my_company`,`report_employees`.`is_state` AS `is_state`,`report_employees`.`is_veteran` AS `is_veteran`,`report_employees`.`job_type` AS `job_type`,`report_employees`.`last_search_past_time` AS `last_search_past_time`,`report_employees`.`last_updated_time` AS `last_updated_time`,`report_employees`.`leave_date` AS `leave_date`,`report_employees`.`license_level` AS `license_level`,`report_employees`.`location` AS `location`,`report_employees`.`military_status` AS `military_status`,`report_employees`.`name` AS `name`,`report_employees`.`nation` AS `nation`,`report_employees`.`nationality` AS `nationality`,`report_employees`.`occupation_type` AS `occupation_type`,`report_employees`.`politics_status` AS `politics_status`,`report_employees`.`remark` AS `remark`,`report_employees`.`sex` AS `sex`,`report_employees`.`shi_ming` AS `shi_ming`,`report_employees`.`stature` AS `stature`,`report_employees`.`telephone` AS `telephone`,`report_employees`.`version` AS `version`,`report_employees`.`work_years` AS `work_years`,`report_employees`.`domain_id` AS `domain_id`,`report_employees`.`is_deleted` AS `is_deleted`,`report_employees`.`last_updated_time` AS `LASTMODIFIED` from `report_employees`