SELECT
    concat(
            ifnull(`r`.`tenant_user_id`, ''),
            '___',
            `r`.`id`,
            '--__qFCcCnozYNwzoG3O6Ms'
        ) AS `id`,
    '__DXZvc8mnEmDqMARhK7G' AS `applicationId`,
    `r`.`organization_id` AS `domain_id`,
    (
        CASE
            WHEN (
                    (
                            ifnull(`c`.`front_photo`, '') = ''
                        )
                    OR (`c`.`front_photo` = 'null')
                ) THEN
                NULL
            ELSE
                concat(
                        '[{"name":"","path":"',
                        `c`.`front_photo`,
                        '"}]'
                    )
            END
        ) AS `id_face_photo`,
    (
        CASE
            WHEN (
                    (
                            ifnull(`c`.`head_photo`, '') = ''
                        )
                    OR (`c`.`head_photo` = 'null')
                ) THEN
                NULL
            ELSE
                concat(
                        '[{"name":"","path":"',
                        `c`.`head_photo`,
                        '"}]'
                    )
            END
        ) AS `id_header_photo`,
    (
        CASE
            WHEN (
                    (
                            ifnull(`c`.`reverse_photo`, '') = ''
                        )
                    OR (`c`.`reverse_photo` = 'null')
                ) THEN
                NULL
            ELSE
                concat(
                        '[{"name":"","path":"',
                        `c`.`reverse_photo`,
                        '"}]'
                    )
            END
        ) AS `id_nation_photo`,
    `u`.`authenticated_time` AS `last_auth_time`,
    '' AS `attendance_site_status`,
    `u`.`birthdate` AS `birth_date`,
    '' AS `blood_type`,
    (
        CASE `c`.`credential_type`
            WHEN 0 THEN
                '居民身份证/户口簿'
            WHEN 1 THEN
                '香港特区护照/身份证'
            WHEN 2 THEN
                '澳门特区护照/身份证明'
            WHEN 3 THEN
                '台湾居民来往大陆通行证'
            WHEN 4 THEN
                '外国护照'
            WHEN 5 THEN
                '退伍军人证'
            WHEN 6 THEN
                '警号'
            WHEN 7 THEN
                '资格证'
            WHEN 8 THEN
                '等级证'
            END
        ) AS `certification`,
    `o`.`name` AS `company_name`,
    '' AS `document_id_bool`,
    `r`.`superior_id` AS `superior_id`,
    `d`.`id` AS `depart_id`,
    `d`.`name` AS `depart_name`,
    `u`.`name` AS `name`,
    `r`.`occupation_type` AS `occupation_type`,
    (
        CASE
            WHEN (
                    `u`.`last_sync_time` > `r`.`last_sync_time`
                ) THEN
                `u`.`last_sync_time`
            ELSE
                `r`.`last_sync_time`
            END
        ) AS `LASTMODIFIED`,
    (
        CASE `r`.`occupation_type`
            WHEN 1 THEN
                1
            WHEN 0 THEN
                3
            WHEN 2 THEN
                3
            END
        ) AS `job_type`,
    `u`.`telephone` AS `telephone`,
    `u`.`id` AS `tenant_user_id`,
    `r`.`hired_date` AS `hired_date`,
    `u`.`sex` AS `sex`,
    `u`.`native_place` AS `nationality`,
    `u`.`stature` AS `stature`,
    `u`.`nation` AS `nation`,
    `u`.`education` AS `education`,
    `u`.`politics_status` AS `politics_status`,
    `u`.`marital_status` AS `marital_status`,
    `u`.`household_type` AS `household_type`,
    `u`.`name` AS `emergency_contact`,
    `u`.`telephone` AS `emergency_phone`,
    `u`.`marital_status` AS `military_status`,
    '' AS `license_level`,
    (
        CASE
            WHEN (`u`.`user_type` = 2) THEN
                1
            ELSE
                0
            END
        ) AS `is_veteran`,
    '' AS `work_years`,
    '' AS `disease_history`,
    `r`.`organization_id` AS `organization_id`,
    (
        CASE `u`.`authenticated_status`
            WHEN 1 THEN
                '认证中'
            WHEN 0 THEN
                '未认证'
            WHEN 2 THEN
                '认证失败'
            WHEN 3 THEN
                '已认证'
            END
        ) AS `shi_ming`,
    (
        CASE `u`.`checked_status`
            WHEN 1 THEN
                '已核验'
            WHEN 0 THEN
                '未提交'
            WHEN 2 THEN
                '已提交'
            END
        ) AS `is_certified`,
    `c`.`number` AS `credential_number`,
    `c`.`credential_type` AS `credential_type`,
    `c`.`address` AS `home_address`,
    `c`.`valid_date_from` AS `id_validation_s`,
    `c`.`valid_date_to` AS `id_validation_e`,
    `c`.`address` AS `id_address`,
    (
        CASE `r`.`status`
            WHEN 1 THEN
                0
            ELSE
                1
            END
        ) AS `is_in_mycompany`,
    `r`.`leave_time` AS `leave_date`,
    `r`.`insure` AS `insure`,
    `u`.`lon_lat_json` AS `location`,
    '' AS `is_state`,
    `u`.`authenticated_result` AS `remark`,
    (
        CASE
            WHEN (
                    (
                            ifnull(
                                    `u`.`one_inch_color_white_photo`,
                                    ''
                                ) = ''
                        )
                    OR (
                            `u`.`one_inch_color_white_photo` = 'null'
                        )
                ) THEN
                NULL
            ELSE
                concat(
                        '[{"name":"","path":"',
                        `u`.`one_inch_color_white_photo`,
                        '"}]'
                    )
            END
        ) AS `one_inch_color_white_photo`,
    `r`.`created_time` AS `created_time`
FROM
    (
        (
            (
                (
                    `tenant_users` `u`
                        LEFT JOIN `tenant_user_credentials` `c` ON (
                        (
                                `c`.`id` = `u`.`selected_credential_id`
                            )
                        )
                    )
                    JOIN `tenant_employees` `r` ON (
                    (
                            `u`.`id` = `r`.`tenant_user_id`
                        )
                    )
                )
                JOIN `tenant_organizations` `o` ON (
                (
                        `o`.`id` = `r`.`organization_id`
                    )
                )
            )
            LEFT JOIN `tenant_departments` `d` ON (
            (
                    `d`.`id` = `r`.`department_id`
                )
            )
        )