SELECT
    `p`.`tenant_userId` AS `id`,
    `p`.`head_photo` AS `ITEM_pirture`,
    `p`.`sex` AS `ITEM_sex`,
    `p`.`id` AS `ITEM_userID`,
    `p`.`name` AS `ITEM_securityName`,
    `p`.`depart_name` AS `ITEM_DeptName`,
    `p`.`depart_id` AS `ITEM_DeptId`,
    `p`.`occupation_type` AS `ITEM_jobType`,
    (
        CASE `p`.`occupation_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`,
    `p`.`credential_number` AS `ITEM_documentID`,
    `p`.`telephone` AS `ITEM_phone`,
    `p`.`birth_date` AS `ITEM_birthdate`,
    '' AS `ITEM_nationality`,
    (
        CASE `p`.`education`
            WHEN '小学' THEN
                '1'
            WHEN '初中' THEN
                '2'
            WHEN '高中' THEN
                '3'
            WHEN '中专' THEN
                '4'
            WHEN '大专' THEN
                '5'
            WHEN '本科' THEN
                '6'
            WHEN '硕士研究生' THEN
                '7'
            WHEN '博士' THEN
                '8'
            WHEN '博士后' THEN
                '9'
            WHEN '其他' THEN
                '10'
            END
        ) AS `ITEM_education`,
    `p`.`stature` AS `ITEM_height`,
    `p`.`nation` AS `ITEM_nation`,
    `p`.`politics_status` AS `ITEM_politicalOutlook`,
    `p`.`household_type` AS `ITEM_householdType`,
    `p`.`id_address` AS `ITEM_homeAddress`,
    `p`.`military_status` AS `ITEM_isVeteran`,
    `p`.`work_years` AS `ITEM_workYears`,
    `p`.`checked_status` AS `ITEM_isCertified`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
				reverse(`p`.`front_photo`),
				(
					locate(
						'/',
						reverse(`p`.`front_photo`)
					) - 1
				)
			)
		),
            '",',
            '"path": "',
            `p`.`front_photo`,
            '"}]'
        ) AS `ITEM_IDFacePhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
				reverse(`p`.`reverse_photo`),
				(
					locate(
						'/',
						reverse(`p`.`reverse_photo`)
					) - 1
				)
			)
		),
            '",',
            '"path": "',
            `p`.`reverse_photo`,
            '"}]'
        ) AS `ITEM_IDNationPhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
				reverse(`p`.`head_photo`),
				(
					locate(
						'/',
						reverse(`p`.`head_photo`)
					) - 1
				)
			)
		),
            '",',
            '"path": "',
            `p`.`head_photo`,
            '"}]'
        ) AS `ITEM_IDHeaderPhoto`,
    concat(
            '[{"name":"',
            reverse(
                LEFT (
				reverse(
					`p`.`one_inch_color_white_photo`
				),
				(
					locate(
						'/',
						reverse(
							`p`.`one_inch_color_white_photo`
						)
					) - 1
				)
			)
		),
            '",',
            '"path": "',
            `p`.`one_inch_color_white_photo`,
            '"}]'
        ) AS `ITEM_headPhoto`,
    `p`.`status` AS `ITEM_isInMyCompany`,
    `p`.`hired_date` AS `ITEM_entryDate`,
    `p`.`leave_time` AS `ITEM_leaveDate`,
    `p`.`organization_id` AS `DOMAINID`,
    '' AS `ITEM_insure`,
    `p`.`authenticated_status` AS `ITEM_shiming`,
    '' AS `ITEM_maritalStatus`,
    `p`.`id_address` AS `ITEM_IDAddress`,
    '0' AS `attendanceSiteStatus`,
    '' AS `licenseLevel`,
    `p`.`company_name` AS `ITEM_companyName`,
    `p`.`location` AS `ITEM_LonLatJson`,
    '' AS `ITEM_cid`
FROM
    (
        SELECT
            `r`.`tenant_user_id` AS `tenant_userId`,
            `r`.`id` AS `id`,
            `r`.`id_header_photo` AS `id_header_photo`,
            `r`.`sex` AS `sex`,
            `r`.`name` AS `name`,
            `r`.`depart_name` AS `depart_name`,
            `r`.`depart_id` AS `depart_id`,
            `r`.`occupation_type` AS `occupation_type`,
            `r`.`credential_type` AS `credential_type`,
            `r`.`credential_number` AS `credential_number`,
            `r`.`telephone` AS `telephone`,
            `r`.`birth_date` AS `birth_date`,
            `r`.`nationality` AS `nationality`,
            `r`.`education` AS `education`,
            `r`.`stature` AS `stature`,
            `r`.`nation` AS `nation`,
            `r`.`politics_status` AS `politics_status`,
            `r`.`household_type` AS `household_type`,
            `r`.`id_address` AS `id_address`,
            `r`.`military_status` AS `military_status`,
            `r`.`work_years` AS `work_years`,
            `r`.`is_certified` AS `checked_status`,
            `r`.`id_face_photo` AS `front_photo`,
            `r`.`id_nation_photo` AS `reverse_photo`,
            `r`.`id_header_photo` AS `head_photo`,
            `r`.`one_inch_color_white_photo` AS `one_inch_color_white_photo`,
            `r`.`is_in_my_company` AS `status`,
            `r`.`hired_date` AS `hired_date`,
            `r`.`leave_date` AS `leave_time`,
            `r`.`organization_id` AS `organization_id`,
            `r`.`shi_ming` AS `authenticated_status`,
            `r`.`location` AS `location`,
            `r`.`is_certified` AS `is_certified`,
            `r`.`company_name` AS `company_name`
        FROM
            `obpm2`.`report_employees` `r`
    ) `p`