<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bonus.canteen.core.health.mapper.HealthPersonInfoMapper">
    <resultMap type="com.bonus.canteen.core.health.domain.HealthPersonInfo" id="HealthPersonInfoResult">
        <result property="id"    column="id"    />
        <result property="userId"    column="user_id"    />
        <result property="registerDate"    column="register_date"    />
        <result property="bloodType"    column="blood_type"    />
        <result property="maritalStatus"    column="marital_status"    />
        <result property="pregnantStatus"    column="pregnant_status"    />
        <result property="pregnancyDate"    column="pregnancy_date"    />
        <result property="doctorAdvice"    column="doctor_advice"    />
        <result property="ifHospitalized"    column="if_hospitalized"    />
        <result property="doctorNum"    column="doctor_num"    />
        <result property="hospitalNum"    column="hospital_num"    />
        <result property="inpatientDate"    column="inpatient_date"    />
        <result property="inpatientResult"    column="inpatient_result"    />
        <result property="weightControl"    column="weight_control"    />
        <result property="allergen"    column="allergen"    />
        <result property="labourIntensity"    column="labour_intensity"    />
        <result property="healthState"    column="health_state"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectHealthPersonInfoVo">
        select id, user_id, register_date, blood_type, marital_status, pregnant_status, pregnancy_date, doctor_advice, if_hospitalized, doctor_num, hospital_num, inpatient_date, inpatient_result, weight_control, allergen, labour_intensity, health_state, create_by, create_time, update_by, update_time from health_person_info
    </sql>

    <select id="selectHealthPersonInfoList" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo" resultMap="HealthPersonInfoResult">
<!--        <include refid="selectHealthPersonInfoVo"/>-->
<!--        <where>  -->
<!--            <if test="userId != null "> and user_id = #{userId}</if>-->
<!--            <if test="registerDate != null "> and register_date = #{registerDate}</if>-->
<!--            <if test="bloodType != null "> and blood_type = #{bloodType}</if>-->
<!--            <if test="maritalStatus != null "> and marital_status = #{maritalStatus}</if>-->
<!--            <if test="pregnantStatus != null "> and pregnant_status = #{pregnantStatus}</if>-->
<!--            <if test="pregnancyDate != null "> and pregnancy_date = #{pregnancyDate}</if>-->
<!--            <if test="doctorAdvice != null "> and doctor_advice = #{doctorAdvice}</if>-->
<!--            <if test="ifHospitalized != null "> and if_hospitalized = #{ifHospitalized}</if>-->
<!--            <if test="doctorNum != null  and doctorNum != ''"> and doctor_num = #{doctorNum}</if>-->
<!--            <if test="hospitalNum != null  and hospitalNum != ''"> and hospital_num = #{hospitalNum}</if>-->
<!--            <if test="inpatientDate != null "> and inpatient_date = #{inpatientDate}</if>-->
<!--            <if test="inpatientResult != null  and inpatientResult != ''"> and inpatient_result = #{inpatientResult}</if>-->
<!--            <if test="weightControl != null "> and weight_control = #{weightControl}</if>-->
<!--            <if test="allergen != null  and allergen != ''"> and allergen = #{allergen}</if>-->
<!--            <if test="labourIntensity != null "> and labour_intensity = #{labourIntensity}</if>-->
<!--            <if test="healthState != null "> and health_state = #{healthState}</if>-->
<!--        </where>-->
        SELECT a.user_id,
        a.nick_name,
        a.phonenumber as mobile,
        a.photo_url as custPhotoUrl,
        a.sex,
        d.height,
        d.weight,
        d.bmi,
        b.doctor_advice,
        b.allergen,
        b.labour_intensity,
        b.pregnant_status,
        b.doctor_num,
        b.hospital_num,
        b.inpatient_date,
        GROUP_CONCAT(c.chronic_name ORDER BY c.chronic_id desc) AS chronic_names,
        GROUP_CONCAT(c.chronic_id ORDER BY c.chronic_id desc)   AS chronic_ids
        from sys_user a
        left join health_person_info b on a.user_id = b.user_id
        left join health_person_info_chronic c on a.user_id = c.user_id
        left join health_person_body_record d on a.user_id = d.user_id and d.if_latest = 1
        where 1 = 1
        <if test="articleTitle != null and articleTitle != ''">
            and(a.nick_name like concat('%', #{articleTitle}, '%') or a.phonenumber like concat('%', #{articleTitle}, '%') or b.doctor_num like concat('%', #{articleTitle}, '%'))
        </if>
        <if test="chronicIds != null and chronicIds != ''">
            and c.chronic_id in (${chronicIds})
        </if>
        <if test="doctorAdvices != null and doctorAdvices != ''">
            and b.doctor_advice in (${doctorAdvices})
        </if>
        group by a.user_id,
        a.nick_name,
        a.dept_id,
        a.phonenumber,
        a.photo_url,
        a.sex,
        d.height,
        d.weight,
        d.bmi,
        b.doctor_advice,
        b.allergen,
        b.labour_intensity,
        b.pregnant_status
    </select>

    <select id="selectHealthPersonInfoById" parameterType="Long" resultMap="HealthPersonInfoResult">
        select a.user_id,
               a.nick_name,
               a.phonenumber as mobile,
               a.sex,
               a.dept_id as orgId,
               a.user_type,
               b.marital_status,
               b.pregnant_status,
               b.pregnancy_date,
               b.doctor_advice,
               b.if_hospitalized,
               b.doctor_num,
               b.hospital_num,
               b.inpatient_date,
               b.inpatient_result,
               b.weight_control,
               b.allergen,
               b.labour_intensity,
               b.health_state,
               b.blood_type,
               c.height,
               c.weight,
               c.bmi,
               c.shape,
               c.bust,
               c.waistline,
               c.blood_sugar,
               c.blood_fat,
               c.blood_pressure_high,
               c.blood_pressure_low,
               c.heart_rate,
               c.blood_oxygen,
               c.uric_acid,
               c.blood_pressure,
               GROUP_CONCAT(d.chronic_name ORDER BY d.chronic_id desc) AS chronic_names,
               GROUP_CONCAT(d.chronic_id ORDER BY d.chronic_id desc)   AS chronic_ids
        from sys_user a
                 left join health_person_info b on a.user_id = b.user_id
                 left join health_person_body_record c on a.user_id = c.user_id and c.if_latest = 1
                 left join health_person_info_chronic d on a.user_id = d.user_id
        where a.user_id = #{userId}
        group by a.user_id,
                 a.user_name,
                 a.phonenumber,
                 a.sex,
                 a.dept_id,
                 a.user_type,
                 b.marital_status,
                 b.pregnant_status,
                 b.pregnancy_date,
                 b.doctor_advice,
                 b.if_hospitalized,
                 b.doctor_num,
                 b.hospital_num,
                 b.inpatient_date,
                 b.inpatient_result,
                 b.weight_control,
                 b.allergen,
                 b.labour_intensity,
                 b.health_state,
                 b.blood_type,
                 c.height,
                 c.weight,
                 c.bmi,
                 c.shape,
                 c.bust,
                 c.waistline,
                 c.blood_sugar,
                 c.blood_fat,
                 c.blood_pressure_high,
                 c.blood_pressure_low,
                 c.heart_rate,
                 c.blood_oxygen,
                 c.uric_acid,
                 c.blood_pressure
    </select>

    <insert id="insertHealthPersonInfo" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        insert into health_person_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="userId != null">user_id,</if>
            <if test="registerDate != null">register_date,</if>
            <if test="bloodType != null">blood_type,</if>
            <if test="maritalStatus != null">marital_status,</if>
            <if test="pregnantStatus != null">pregnant_status,</if>
            <if test="pregnancyDate != null">pregnancy_date,</if>
            <if test="doctorAdvice != null">doctor_advice,</if>
            <if test="ifHospitalized != null">if_hospitalized,</if>
            <if test="doctorNum != null">doctor_num,</if>
            <if test="hospitalNum != null">hospital_num,</if>
            <if test="inpatientDate != null">inpatient_date,</if>
            <if test="inpatientResult != null and inpatientResult != ''">inpatient_result,</if>
            <if test="weightControl != null">weight_control,</if>
            <if test="allergen != null and allergen != ''">allergen,</if>
            <if test="labourIntensity != null">labour_intensity,</if>
            <if test="healthState != null">health_state,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="userId != null">#{userId},</if>
            <if test="registerDate != null">#{registerDate},</if>
            <if test="bloodType != null">#{bloodType},</if>
            <if test="maritalStatus != null">#{maritalStatus},</if>
            <if test="pregnantStatus != null">#{pregnantStatus},</if>
            <if test="pregnancyDate != null">#{pregnancyDate},</if>
            <if test="doctorAdvice != null">#{doctorAdvice},</if>
            <if test="ifHospitalized != null">#{ifHospitalized},</if>
            <if test="doctorNum != null">#{doctorNum},</if>
            <if test="hospitalNum != null">#{hospitalNum},</if>
            <if test="inpatientDate != null">#{inpatientDate},</if>
            <if test="inpatientResult != null and inpatientResult != ''">#{inpatientResult},</if>
            <if test="weightControl != null">#{weightControl},</if>
            <if test="allergen != null and allergen != ''">#{allergen},</if>
            <if test="labourIntensity != null">#{labourIntensity},</if>
            <if test="healthState != null">#{healthState},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateHealthPersonInfo" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        update health_person_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="registerDate != null">register_date = #{registerDate},</if>
            <if test="bloodType != null">blood_type = #{bloodType},</if>
            <if test="maritalStatus != null">marital_status = #{maritalStatus},</if>
            <if test="pregnantStatus != null">pregnant_status = #{pregnantStatus},</if>
            <if test="pregnancyDate != null">pregnancy_date = #{pregnancyDate},</if>
            <if test="doctorAdvice != null">doctor_advice = #{doctorAdvice},</if>
            <if test="ifHospitalized != null">if_hospitalized = #{ifHospitalized},</if>
            <if test="doctorNum != null">doctor_num = #{doctorNum},</if>
            <if test="hospitalNum != null">hospital_num = #{hospitalNum},</if>
            <if test="inpatientDate != null">inpatient_date = #{inpatientDate},</if>
            <if test="inpatientResult != null and inpatientResult != ''">inpatient_result = #{inpatientResult},</if>
            <if test="weightControl != null">weight_control = #{weightControl},</if>
            <if test="allergen != null and allergen != ''">allergen = #{allergen},</if>
            <if test="labourIntensity != null">labour_intensity = #{labourIntensity},</if>
            <if test="healthState != null">health_state = #{healthState},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteHealthPersonInfoById" parameterType="Long">
        delete from health_person_info where id = #{id}
    </delete>

    <delete id="deleteHealthPersonInfoByIds" parameterType="String">
        delete from health_person_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


    <select id="selectHealthChronicList" resultType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        SELECT distinct chronic_id as chronic_ids,chronic_name as chronic_names from  health_chronic where chronic_id in (${chronicIds})
    </select>

    <select id="getSimpleInfo" resultType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        SELECT a.user_id,
               a.nick_name,
               a.phonenumber as mobile,
               a.photo_url as custPhotoUrl,
               a.sex,
               d.height,
               d.weight,
               d.bmi,
               b.doctor_advice,
               b.allergen,
               b.labour_intensity,
               b.pregnant_status,
               GROUP_CONCAT(c.chronic_name ORDER BY c.chronic_id desc) AS chronic_names,
               GROUP_CONCAT(c.chronic_id ORDER BY c.chronic_id desc)   AS chronic_ids
        from sys_user a
                 left join health_person_info b on a.user_id = b.user_id
                 left join health_person_info_chronic c on a.user_id = c.user_id
                 left join health_person_body_record d on a.user_id = d.user_id and d.if_latest = 1
        where 1 = 1
          and a.user_id = #{userId}
        group by a.user_id,
                 a.nick_name,
                 a.dept_id,
                 a.phonenumber,
                 a.photo_url,
                 a.sex,
                 d.height,
                 d.weight,
                 d.bmi,
                 b.doctor_advice,
                 b.allergen,
                 b.labour_intensity,
                 b.pregnant_status
    </select>

    <select id="fullHealthInfo" resultType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        select a.user_id,
               a.nick_name,
               a.phonenumber as mobile,
               a.sex,
               a.dept_id as orgId,
               a.user_type,
               b.marital_status,
               b.pregnant_status,
               b.pregnancy_date,
               b.doctor_advice,
               b.if_hospitalized,
               b.doctor_num,
               b.hospital_num,
               b.inpatient_date,
               b.inpatient_result,
               b.weight_control,
               b.allergen,
               b.labour_intensity,
               b.health_state,
               b.blood_type,
               c.height,
               c.weight,
               c.bmi,
               c.shape,
               c.bust,
               c.waistline,
               c.blood_sugar,
               c.blood_fat,
               c.blood_pressure_high,
               c.blood_pressure_low,
               c.heart_rate,
               c.blood_oxygen,
               c.uric_acid,
               c.blood_pressure,
               GROUP_CONCAT(d.chronic_name ORDER BY d.chronic_id desc) AS chronic_names,
               GROUP_CONCAT(d.chronic_id ORDER BY d.chronic_id desc)   AS chronic_ids
        from sys_user a
                 left join health_person_info b on a.user_id = b.user_id
                 left join health_person_body_record c on a.user_id = c.user_id and c.if_latest = 1
                 left join health_person_info_chronic d on a.user_id = d.user_id
        where a.user_id = #{userId}
        group by a.user_id,
                 a.user_name,
                 a.phonenumber,
                 a.sex,
                 a.dept_id,
                 a.user_type,
                 b.marital_status,
                 b.pregnant_status,
                 b.pregnancy_date,
                 b.doctor_advice,
                 b.if_hospitalized,
                 b.doctor_num,
                 b.hospital_num,
                 b.inpatient_date,
                 b.inpatient_result,
                 b.weight_control,
                 b.allergen,
                 b.labour_intensity,
                 b.health_state,
                 b.blood_type,
                 c.height,
                 c.weight,
                 c.bmi,
                 c.shape,
                 c.bust,
                 c.waistline,
                 c.blood_sugar,
                 c.blood_fat,
                 c.blood_pressure_high,
                 c.blood_pressure_low,
                 c.heart_rate,
                 c.blood_oxygen,
                 c.uric_acid,
                 c.blood_pressure
    </select>
    <update id="updateHealthInfo" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        update  health_person_info
        <set>
            <if test="bloodType != null and bloodType != ''">
                blood_type = #{bloodType},
            </if>
            <if test="maritalStatus != null and maritalStatus != ''">
                marital_status = #{maritalStatus},
            </if>
            <if test="pregnantStatus != null and pregnantStatus != ''">
                pregnant_status = #{pregnantStatus},
            </if>
            <if test="pregnancyDate != null ">
                pregnancy_date = #{pregnancyDate},
            </if>
            <if test="doctorAdvice != null and doctorAdvice != ''">
                doctor_advice = #{doctorAdvice},
            </if>
            <if test="ifHospitalized != null and ifHospitalized != ''">
                if_hospitalized = #{ifHospitalized},
            </if>
            <if test="doctorNum != null and doctorNum != ''">
                doctor_num = #{doctorNum},
            </if>
            <if test="hospitalNum != null and hospitalNum != ''">
                hospital_num = #{hospitalNum},
            </if>
            <if test="inpatientDate != null ">
                inpatient_date = #{inpatientDate},
            </if>
            <if test="weightControl != null and weightControl != ''">
                weight_control = #{weightControl},
            </if>
            <if test="allergen != null and allergen != ''">
                allergen = #{allergen},
            </if>
            <if test="labourIntensity != null and labourIntensity != ''">
                labour_intensity = #{labourIntensity},
            </if>
        </set>
        where user_id = #{userId}  and del_flag='0'
    </update>

    <select id="getHealthBodyRecordNum" resultType="java.lang.Integer">
        select count(1) from health_person_body_record where user_id=#{userId} and if_latest = 1
    </select>

    <insert id="insertHealthBodyRecord" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        insert into  health_person_body_record(user_id,if_latest)
        values
            (#{userId},1)
    </insert>

    <update id="updateHealthBodyRecord" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        update health_person_body_record
        <set>
            <if test="height != null and height != ''">
                height = #{height},
            </if>
            <if test="weight != null and weight != ''">
                weight = #{weight},
            </if>
            <if test="bmi != null and bmi != ''">
                bmi = #{bmi},
            </if>
        </set>
        where user_id = #{userId} and if_latest = 1
    </update>
    <update id="deleteHealthInfoChronic" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        delete from health_person_info_chronic
        where user_id = #{userId}
    </update>
    <update id="delHealthInfo" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        delete from  health_person_info
        where user_id = #{userId}
    </update>
    <insert id="insertHealthInfoChronic" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        insert into health_person_info_chronic(user_id, chronic_id, chronic_name)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.userId}, #{entity.chronicIds}, #{entity.chronicNames})
        </foreach>
    </insert>
    <insert id="insertHealthInfo" parameterType="com.bonus.canteen.core.health.domain.HealthPersonInfo">
        insert into  health_person_info(user_id)
        values
            (#{userId})
    </insert>
</mapper>
