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`,(
        CASE
            `duc`.`credential_type`
            WHEN '0' THEN
                '1'
            WHEN '1' THEN
                '4'
            WHEN '2' THEN
                '5'
            WHEN '3' THEN
                '6'
            WHEN '4' THEN
                '8'
            WHEN '5' THEN
                '2'
            WHEN '6' THEN
                '3'
            END
        ) 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`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
					reverse( `duc`.`front_photo` ),(
						locate(
							'/',
						reverse( `duc`.`front_photo` )) - 1
					))),
            '",',
            '"path": "',
            `duc`.`front_photo`,
            '"}]'
        ) AS `ITEM_IDFacePhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
					reverse( `duc`.`reverse_photo` ),(
						locate(
							'/',
						reverse( `duc`.`reverse_photo` )) - 1
					))),
            '",',
            '"path": "',
            `duc`.`reverse_photo`,
            '"}]'
        ) AS `ITEM_IDNationPhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
					reverse( `duc`.`head_photo` ),(
						locate(
							'/',
						reverse( `duc`.`head_photo` )) - 1
					))),
            '",',
            '"path": "',
            `duc`.`head_photo`,
            '"}]'
        ) AS `ITEM_IDHeaderPhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
					reverse( `t`.`one_inch_color_white_photo` ),(
						locate(
							'/',
						reverse( `t`.`one_inch_color_white_photo` )) - 1
					))),
            '",',
            '"path": "',
            `t`.`one_inch_color_white_photo`,
            '"}]'
        ) AS `ITEM_headPhoto`,(
        CASE
            `e`.`status`
            WHEN '0' THEN
                '1'
            WHEN '1' THEN
                '0'
            END
        ) 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`,(
        CASE

            WHEN ( `att`.`count_attendance` > 0 ) THEN
                '1' ELSE '0'
            END
        ) AS `attendanceSiteStatus`,
    '' AS `licenseLevel`,
    `org`.`name` AS `ITEM_companyName`,
    `t`.`lon_lat_json` AS `ITEM_LonLatJson`,
    `t`.`cid` AS `ITEM_cid`
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`
            )))
        LEFT JOIN (
        SELECT
            count( 1 ) AS `count_attendance`,
            `k`.`ITEM_securityId` AS `ITEM_securityId`,
            `k`.`DOMAINID` AS `DOMAINID`
        FROM
            `baibaodunflow`.`tlk_attendance_site_arrange_info` `k`
        GROUP BY
            `k`.`ITEM_securityId`,
            `k`.`DOMAINID`
    ) `att` ON (((
                         `att`.`ITEM_securityId` = `e`.`tenant_user_id`
                     )
        AND ( `att`.`DOMAINID` = `e`.`organization_id` ))))