<?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.xinelu.manage.mapper.screeningrecord.ScreeningRecordMapper">

    <resultMap id="BaseResultMap" type="com.xinelu.manage.domain.screeningrecord.ScreeningRecord">
            <id property="id" column="id" jdbcType="BIGINT"/>
            <result property="screeningId" column="screening_id" jdbcType="VARCHAR"/>
            <result property="patientId" column="patient_id" jdbcType="VARCHAR"/>
            <result property="screeningStatus" column="screening_status" jdbcType="VARCHAR"/>
            <result property="disease" column="disease" jdbcType="VARCHAR"/>
            <result property="hospitalId" column="hospital_id" jdbcType="BIGINT"/>
            <result property="hospitalName" column="hospital_name" jdbcType="VARCHAR"/>
		    <result property="projectId" column="project_id" jdbcType="VARCHAR"/>
		    <result property="projectName" column="project_name" jdbcType="VARCHAR"/>
		    <result property="applyStartTime" column="apply_start_time" jdbcType="TIMESTAMP"/>
		    <result property="applyEndTime" column="apply_end_time" jdbcType="TIMESTAMP"/>
		    <result property="screeningDate" column="screening_date" jdbcType="DATE"/>
		    <result property="content" column="content" jdbcType="VARCHAR"/>
		    <result property="diagnosticResult" column="diagnostic_result" jdbcType="VARCHAR"/>
		    <result property="attachment" column="attachment" jdbcType="VARCHAR"/>
		    <result property="attachmentTwo" column="attachment_two" jdbcType="VARCHAR"/>
		    <result property="doctorId" column="doctor_id" jdbcType="BIGINT"/>
            <result property="doctorName" column="doctor_name" jdbcType="VARCHAR"/>
		    <result property="applyCode" column="apply_code" jdbcType="VARCHAR"/>
		    <result property="applyBarcode" column="apply_barcode" jdbcType="VARCHAR"/>
	        <result property="registrationDate" column="registration_date" jdbcType="TIMESTAMP"/>
		    <result property="registrationCode" column="registration_code" jdbcType="VARCHAR"/>
		    <result property="registrationBarcode" column="registration_barcode" jdbcType="VARCHAR"/>
	        <result property="screeningType" column="screening_type" jdbcType="VARCHAR"/>
	        <result property="pushDate" column="push_date" jdbcType="TIMESTAMP"/>
	        <result property="assessRecordId" column="assess_record_id" jdbcType="VARCHAR"/>
		    <result property="remark" column="remark" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,screening_id,patient_id,screening_status,
        disease,hospital_id,IFNULL(hospital_name, '') as hospital_name,
        project_id, project_name,
        apply_start_time,apply_end_time, screening_date,
        content,diagnostic_result,attachment,attachment_two,
        doctor_id,IFNULL(doctor_name,'') as doctor_name,apply_barcode,registration_date,screening_type,push_date,assess_record_id,
        remark
    </sql>

    <sql id="Insert_Column">
        screening_id,patient_id,screening_status,
        disease,hospital_id,hospital_name,
        project_id, project_name,
        apply_start_time, apply_end_time, screening_date,
        content,diagnostic_result,attachment,attachment_two,
        doctor_id,doctor_name,apply_code,apply_barcode,registration_date,screening_type,push_date,assess_record_id,remark
    </sql>

    <!-- 新增 -->
    <insert id="insert">
        insert into screening_record (<include refid="Insert_Column"></include>)
        values (#{screeningId}, #{patientId}, #{screeningStatus},
        #{disease}, #{hospitalId}, #{hospitalName},
	    #{projectId},#{projectName},
	    #{applyStartTime}, #{applyEndTime},#{screeningDate},
	    #{content}, #{diagnosticResult},#{attachment},#{attachmentTwo},
        #{doctorId}, #{doctorName},#{applyCode},#{applyBarcode},#{registrationDate},#{screeningType},#{pushDate},#{assessRecordId},#{remark})
    </insert>

    <!-- 修改 -->
    <update id="update">
        update screening_record
	    <set>
		    <if test="disease != null">
			    disease = #{disease},
		    </if>
		    <if test="screeningStatus != null">
			    screening_status = #{screeningStatus},
		    </if>
		    <if test="hospitalId != null">
			    hospital_id = #{hospitalId},
		    </if>
		    <if test="hospitalName != null">
			    hospital_name = #{hospitalName},
		    </if>
		    <if test="projectId != null">
			    project_id = #{projectId},
		    </if>
		    <if test="projectName != null">
			    project_name = #{projectName},
		    </if>
		    <if test="applyStartTime != null">
			    apply_start_time = #{applyStartTime},
		    </if>
		    <if test="applyEndTime != null">
			    apply_end_time = #{applyEndTime},
		    </if>
		    <if test="screeningDate != null">
			    screening_date = #{screeningDate},
		    </if>
		    <if test="content != null">
			    content = #{content},
		    </if>
		    <if test="diagnosticResult != null">
			    diagnostic_result = #{diagnosticResult},
		    </if>
		    <if test="attachment != null">
			    attachment = #{attachment},
		    </if>
		    <if test="attachmentTwo != null">
			    attachment_two = #{attachmentTwo},
		    </if>
            <if test="doctorId != null">
			    doctor_id = #{doctorId},
		    </if>
		    <if test="doctorName != null">
			    doctor_name = #{doctorName},
		    </if>
		    <if test="applyCode != null">
			    apply_code = #{applyCode},
		    </if>
		    <if test="applyBarcode != null">
			    apply_barcode = #{applyBarcode},
		    </if>
		    <if test="registrationDate != null">
			    registration_date = #{registrationDate},
		    </if>
		    <if test="registrationCode != null">
			    registration_code = #{registrationCode},
		    </if>
		    <if test="registrationBarcode != null">
			    registration_barcode = #{registrationBarcode},
		    </if>
		    <if test="screeningType != null">
			    screening_type = #{screeningType},
		    </if>
		    <if test="pushDate != null">
			    push_date = #{pushDate},
		    </if>
		    <if test="remark != null">
			    remark = #{remark},
		    </if>
	    </set>
        where screening_id = #{screeningId}
    </update>

    <!-- 更新预约状态 -->
    <update id="updateStatus">
        update screening_record set screening_status = #{screeningStatus}
        <if test="screeningDate != null">
            ,screening_date = #{screeningDate}
        </if>
        where screening_id = #{screeningId}
    </update>

    <!-- 获取预约详情 -->
    <select id="detail" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
        select <include refid="Base_Column_List"></include> from screening_record where screening_id = #{screeningId}
    </select>

    <!-- 获取当前居民预约信息 -->
    <select id="getScreeningByPatientId" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
        select <include refid="Base_Column_List"></include> from screening_record
                where patient_id = #{patientId} and screening_status in ('1', '3')
                and screening_type = '1'
                limit 1
    </select>

    <sql id="where">
        <if test="screeningStatus != null and screeningStatus != ''">
            and screening_status = #{screeningStatus}
        </if>
        <if test="patientId != null and patientId != ''">
            and patient_id = #{patientId}
        </if>
        <if test="patientName != null and patientName != ''">
            and patient_name like concat(#{patientName}, '%')
        </if>
        <if test="identity != null and identity != ''">
            and `identity` = #{identity}
        </if>
        <if test="hospitalId != null and hospitalId != ''">
            and hospital_id = #{hospitalId}
        </if>
        <if test="hospitalName != null and hospitalName != ''">
            and hospital_name = #{hospitalName}
        </if>
	    <if test="projectId != null and projectId != ''">
		    and project_id = #{projectId}
	    </if>
        <if test="doctorId != null and doctorId != ''">
            and doctor_id = #{doctorId}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and doctor_name = #{doctorName}
        </if>
        <if test="applyStartDate != null">
            and date_format(apply_start_time, '%y%m%d') &gt;= date_format(#{applyStartDate}, '%y%m%d')
        </if>
        <if test="applyEndDate != null">
	        and date_format(apply_end_time, '%y%m%d') &lt;= date_format(#{applyEndDate}, '%y%m%d')
        </if>
        <if test="screeningStartDate != null">
            and screening_date &gt;= #{screeningStartDate}
        </if>
        <if test="screeningEndDate != null">
            and screening_date &lt;= #{screeningEndDate}
        </if>
	    <if test="screeningType != null">
		    and screening_type = #{screeningType}
	    </if>
	    <if test="assessRecordId != null">
		    and assess_record_id = #{assessRecordId}
	    </if>
    </sql>
	<select id="screeningList" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
		select r.patient_name,r.sex as gender,r.birth_date as birthday,r.phone,r.card_no as `identity`
	    ,d.screening_id,d.patient_id,d.project_id,d.project_name
        ,d.hospital_id,d.hospital_name,d.apply_start_time,d.apply_end_time,
		d.diagnostic_result,d.attachment,d.attachment_two,
		d.registration_date,d.registration_code, d.registration_barcode,d.screening_type,d.push_date,
		case when date_format(apply_end_time, '%y%m%d%H%i') &lt; date_format(now(), '%y%m%d%H%i') then '1'
		else '0' end as exceedStatus,screening_status,
        case when attachment is not null or attachment_two is not null then '1'
		else '0' end as hasResult,
		case when diagnostic_result is not null then '1'
		else '0' end as hasDiagnose
		from screening_record d left join patient_info r on d.patient_id = r.id
		<where>
		    d.screening_status != '2' and r.del_flag = '0'
			<if test="screeningStatus != null and screeningStatus != ''">
				and d.screening_status = #{screeningStatus}
			</if>
			<if test="patientName != null and patientName != ''">
				and r.patient_name like concat(#{patientName}, '%')
			</if>
			<if test="identity != null and identity != ''">
				and r.card_no = #{identity}
			</if>
			<if test="address != null and address != ''">
				and r.address like concat('%', #{address}, '%')
			</if>
			<if test="hospitalId != null and hospitalId != ''">
				and d.hospital_id = #{hospitalId}
			</if>
			<if test="hospitalName != null and hospitalName != ''">
				and d.hospital_name = #{hospitalName}
			</if>
			<if test="projectId != null and projectId != ''">
				and d.project_id = #{projectId}
			</if>
			<if test="applyStartDate != null">
				and d.apply_start_time &gt;= #{applyStartDate}
			</if>
			<if test="applyEndDate != null">
				and d.apply_end_time &lt;= #{applyEndDate}
			</if>
			<if test="applyCode != null and applyCode != ''">
				and d.apply_code = #{applyCode}
			</if>
			<if test="registrationStatus != null and registrationStatus != ''">
				<choose>
					<when test="registrationStatus == '0'.toString()">
						and d.screening_status &lt; '2'
					</when>
					<when test="registrationStatus == '1'.toString()">
						and d.screening_status > '2'
					</when>
				</choose>
			</if>
			<if test="exceedStatus != null and exceedStatus != ''">
				<choose>
					<when test="exceedStatus == '0'.toString()">
						and date_format(d.apply_end_time, '%Y%m%d%H%i') >= date_format(now(), '%Y%m%d%H%i')
					</when>
					<when test="exceedStatus == '1'.toString()">
						and date_format(d.apply_end_time, '%Y%m%d%H%i') &lt; date_format(now(), '%Y%m%d%H%i')
					</when>
				</choose>
			</if>
			<if test="hasResult != null and hasResult != ''">
				<choose>
					<when test="hasResult == '0'.toString()">
						and d.attachment is null and d.attachment_two is null
					</when>
					<when test="hasResult == '1'.toString()">
						and (d.attachment is not null or d.attachment_two is not null)
					</when>
				</choose>
			</if>
			<if test="hasDiagnose != null and hasDiagnose != ''">
				<choose>
					<when test="hasDiagnose == '1'.toString()">
						and d.diagnostic_result is not null
					</when>
					<when test="hasDiagnose == '0'.toString()">
						and d.diagnostic_result is null
					</when>
				</choose>
			</if>
			<if test="screeningType != null">
				and screening_type = #{screeningType}
			</if>
		</where>
		order by apply_start_time desc
	</select>
    <!-- 获取预约记录 -->
    <select id="list" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
        select <include refid="Base_Column_List"></include>,
            case when date_format(apply_end_time, '%y%m%d%H%i') &lt; date_format(now(), '%y%m%d%H%i') then '1'
        else '0' end as exceedStatus
            from screening_record record
        where screening_status != '2'
        <include refid="where"></include>
        order by apply_start_time desc
    </select>

    <!-- 获取筛查结果记录 -->
    <select id="record" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
        select  ssr.id,ssr.screening_id,ssr.patient_id,ssr.screening_status,
		ssr.disease,ssr.hospital_id,IFNULL(ssr.hospital_name, '') as hospital_name,ssr.project_id, ssr.project_name,
		ssr.apply_start_time,ssr.apply_end_time, ssr.screening_date,ssr.content,ssr.diagnostic_result,ssr.attachment,ssr.attachment_two,
		ssr.doctor_id,IFNULL(ssr.doctor_name,'') as doctor_name,ssr.apply_barcode,ssr.registration_date,ssr.screening_type,ssr.push_date,ssr.assess_record_id,
		ssr.remark,substring_index(ssr.attachment, '.', -1) as fileType
        from screening_record ssr
		where 1=1
        <include refid="where"></include>
        order by screening_date desc
    </select>

    <!-- 获取最新一次筛查结果 -->
    <select id="getScreeningOfLast" resultType="com.xinelu.manage.vo.screeningrecord.ScreeningRecordVo">
        select <include refid="Base_Column_List"></include> from screening_record
        where patient_id = #{patientId}
	    <if test="screeningType != null">
		    and screening_type = #{screeningType}
	    </if>
		<if test="projectId != null and projectId != ''">
		    and project_id = #{projectId}
	    </if>
          <choose>
	          <when test="screeningStatus != null and screeningStatus != ''">
		          and screening_status = #{screeningStatus}
	          </when>
	          <otherwise>
		          and screening_status in ('1','3', '4')
	          </otherwise>
          </choose>
        order by id desc limit 1
    </select>

	<select id="selectByScreeningId" resultMap="BaseResultMap">
		select <include refid="Base_Column_List"></include> from screening_record
		where screening_id = #{screeningId}
	</select>
	<select id="selectTimeLineList" parameterType="java.lang.String" resultType="com.xinelu.common.core.domain.TimelineVo">
		select
			screening_id as id, date_format(apply_start_time,'%Y-%m-%d') as createTime
		from screening_record
		where
			patient_id = #{patientId} and screening_status in('1','3','4') and screening_type = '1'
		order by apply_start_time desc
	</select>
</mapper>
