<?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.patientinfo.PatientInfoMapper">
    <resultMap type="com.xinelu.manage.domain.patientinfo.PatientInfo" id="PatientInfoResult">
        <result property="id" column="id"/>
        <result property="residentId" column="resident_id"/>
        <result property="patientName" column="patient_name"/>
        <result property="patientPhone" column="patient_phone"/>
        <result property="familyMemberPhone" column="family_member_phone"/>
        <result property="birthDate" column="birth_date"/>
        <result property="cardNo" column="card_no"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <result property="patientType" column="patient_type"/>
        <result property="signStatus" column="sign_status"/>
        <result property="signPatientRecordId" column="sign_patient_record_id"/>
        <result property="serviceStatus" column="service_status"/>
        <result property="signTime" column="sign_time"/>
        <result property="visitMethod" column="visit_method"/>
        <result property="attendingPhysicianId" column="attending_physician_id"/>
        <result property="attendingPhysicianName" column="attending_physician_name"/>
        <result property="mainDiagnosis" column="main_diagnosis"/>
        <result property="hospitalAgencyId" column="hospital_agency_id"/>
        <result property="hospitalAgencyName" column="hospital_agency_name"/>
        <result property="campusAgencyId" column="campus_agency_id"/>
        <result property="campusAgencyName" column="campus_agency_name"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="wardId" column="ward_id"/>
        <result property="wardName" column="ward_name"/>
        <result property="responsibleNurse" column="responsible_nurse"/>
        <result property="patientVisitRecordId" column="patient_visit_record_id"/>
        <result property="visitSerialNumber" column="visit_serial_number"/>
        <result property="admissionTime" column="admission_time"/>
        <result property="dischargeTime" column="discharge_time"/>
        <result property="patientPreHospitalizationId" column="patient_pre_hospitalization_id"/>
        <result property="appointmentTreatmentGroup" column="appointment_treatment_group"/>
        <result property="registrationNo" column="registration_no"/>
        <result property="registrationDate" column="registration_date"/>
        <result property="appointmentDate" column="appointment_date"/>
        <result property="inHospitalNumber" column="in_hospital_number"/>
        <result property="visitDate" column="visit_date"/>
        <result property="dischargeMethod" column="discharge_method"/>
        <result property="patientSource" column="patient_source"/>
        <result property="surgicalName" column="surgical_name"/>
        <result property="delFlag" column="del_flag"/>
        <result property="sn" column="sn"/>
        <result property="patientInfoImportId" column="patient_info_import_id"/>
        <result property="age" column="age"/>
        <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="selectPatientInfoVo">
        select p.id,
               p.resident_id,
               p.patient_name,
               p.patient_phone,
               p.family_member_phone,
               p.birth_date,
               p.card_no,
               p.sex,
               p.address,
               p.patient_type,
               p.sign_status,
               p.sign_patient_record_id,
               p.service_status,
               p.sign_time,
               p.visit_method,
               p.attending_physician_id,
               p.attending_physician_name,
               p.main_diagnosis,
               p.hospital_agency_id,
               p.hospital_agency_name,
               p.campus_agency_id,
               p.campus_agency_name,
               p.department_id,
               p.department_name,
               p.ward_id,
               p.ward_name,
               p.responsible_nurse,
               p.patient_visit_record_id,
               p.visit_serial_number,
               p.admission_time,
               p.patient_pre_hospitalization_id,
               p.discharge_time,
               p.patient_pre_hospitalization_id,
               p.appointment_treatment_group,
               p.registration_no,
               p.registration_date,
               p.appointment_date,
               p.in_hospital_number,
               p.visit_date,
               p.discharge_method,
               p.patient_source,
               p.surgical_name,
               p.del_flag,
               p.sn,
               p.patient_info_import_id,
               p.age,
               p.create_by,
               p.create_time,
               p.update_by,
               p.update_time
        from patient_info p
    </sql>

    <select id="selectPatientInfoList" parameterType="com.xinelu.manage.dto.patientinfo.PatientInfoDto"
            resultMap="PatientInfoResult">
        <include refid="selectPatientInfoVo"/>
        <where>
            p.del_flag = 0
            <if test="residentId != null">
                and p.resident_id = #{residentId}
            </if>
            <if test="patientName != null  and patientName != ''">
                and p.patient_name = #{patientName}
            </if>
            <if test="patientPhone != null  and patientPhone != ''">
                and p.patient_phone like concat('%', #{patientPhone}, '%')
            </if>
            <if test="cardNo != null  and cardNo != ''">
                and p.card_no = #{cardNo}
            </if>
            <if test="patientType != null  and patientType != ''">
                <choose>
                    <when test="patientType == 'OUTPATIENT_DISCHARGE'.toString()">
                        and (p.patient_type = 'OUTPATIENT' or p.patient_type = 'DISCHARGED_PATIENT')
                    </when>
                    <otherwise>
                        and p.patient_type = #{patientType}
                    </otherwise>
                </choose>
            </if>
            <if test="attendingPhysicianId != null">
                and p.attending_physician_id = #{attendingPhysicianId}
            </if>
            <if test="mainDiagnosis != null  and mainDiagnosis != ''">
                and p.main_diagnosis like concat('%', #{mainDiagnosis}, '%')
            </if>
            <if test="hospitalAgencyId != null ">
                and p.hospital_agency_id = #{hospitalAgencyId}
            </if>
            <if test="campusAgencyId != null ">
                and p.campus_agency_id = #{campusAgencyId}
            </if>
            <if test="departmentId != null ">
                and p.department_id = #{departmentId}
            </if>
            <if test="wardId != null ">
                and p.ward_id = #{wardId}
            </if>
            <if test="visitMethod != null  and visitMethod != ''">
                and p.visit_method = #{visitMethod}
            </if>
            <if test="visitDateStart != null ">
                and date_format(p.visit_date, '%y%m%d') >= date_format(#{visitDateStart}, '%y%m%d')
            </if>
            <if test="visitDateEnd != null ">
                and date_format(p.visit_date, '%y%m%d') &lt;= date_format(#{visitDateEnd}, '%y%m%d')
            </if>
            <if test="admissionTimeStart != null ">
                and date_format(p.admission_time,'%y%m%d') >= date_format(#{admissionTimeStart}, '%y%m%d')
            </if>
            <if test="admissionTimeEnd != null ">
                and date_format(p.admission_time,'%y%m%d') &lt;= date_format(#{admissionTimeEnd}, '%y%m%d')
            </if>
            <if test="dischargeTimeStart != null ">
                and date_format(p.discharge_time,'%y%m%d') >= date_format(#{dischargeTimeStart}, '%y%m%d')
            </if>
            <if test="dischargeTimeEnd != null ">
                and date_format(p.discharge_time,'%y%m%d') &lt;= date_format(#{dischargeTimeEnd}, '%y%m%d')
            </if>
            <if test="appointmentDateStart != null ">
                and date_format(p.appointment_date, '%y%m%d') >= date_format(#{appointmentDateStart}, '%y%m%d')
            </if>
            <if test="appointmentDateEnd != null ">
                and date_format(p.appointment_date, '%y%m%d') &lt;= date_format(#{appointmentDateEnd}, '%y%m%d')
            </if>
            <if test="appointmentTreatmentGroup != null  and appointmentTreatmentGroup != ''">
                and p.appointment_treatment_group = #{appointmentTreatmentGroup}
            </if>
            <if test="registrationNo != null  and registrationNo != ''">
                and p.registration_no = #{registrationNo}
            </if>
            <if test="visitSerialNumber != null  and visitSerialNumber != ''">
                and p.visit_serial_number = #{visitSerialNumber}
            </if>
            <if test="responsibleNurse != null  and responsibleNurse != ''">
                and p.responsible_nurse = #{responsibleNurse}
            </if>
            <if test="patientSource != null  and patientSource != ''">
                and p.patient_source = #{patientSource}
            </if>
            <if test="surgicalName != null  and surgicalName != ''">
                and p.surgical_name = #{surgicalName}
            </if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by p.id desc
    </select>

	<select id="getPatientList" parameterType="com.xinelu.manage.dto.patientinfo.PatientInfoDto"
		resultType="com.xinelu.manage.vo.patientinfo.PatientInfoVo">
		select p.id AS id,
		p.resident_id AS resident_id,
		p.patient_name AS patient_name,
		p.patient_phone AS patient_phone,
		p.birth_date AS birth_date,
		p.card_no AS card_no,
		p.sex AS sex,
		p.address AS address,
		p.patient_type AS patient_type,
		p.visit_method AS visit_method,
		p.visit_serial_number AS visit_serial_number,
		p.in_hospital_number AS in_hospital_number,
		p.attending_physician_id AS attending_physician_id,
		p.attending_physician_name AS attending_physician_name,
		p.main_diagnosis AS main_diagnosis,
		p.hospital_agency_id AS hospital_agency_id,
		p.hospital_agency_name AS hospital_agency_name,
		p.campus_agency_id AS campus_agency_id,
		p.campus_agency_name AS campus_agency_name,
		p.department_id AS department_id,
		p.department_name AS department_name,
		p.ward_id AS ward_id,
		p.ward_name AS ward_name,
		p.patient_visit_record_id AS patient_visit_record_id,
		p.sign_patient_record_id,
		p.visit_date AS visit_date,
		p.patient_source AS patient_source,
        p.age AS age,
		CASE
		WHEN isnull( p.sign_patient_record_id) THEN '新建'
		WHEN sign.service_status = 'INTENTIONAL_SIGNING' THEN '意向签约'
		WHEN sign.service_status = 'SERVICE_CENTER' AND sign.route_check_status = 'AGREE' THEN '服务中'
		WHEN sign.service_status = 'SERVICE_END' THEN '已归档' ELSE '已签约' END AS patientSignStatus,
		case when isnull(pts.task_num) then 0 else pts.task_num end as taskNum,
		case when isnull(pts.task_execute_num) then 0 else pts.task_execute_num end as taskExecuteNum,
		case when isnull(pts.task_finish_num) then 0 else pts.task_finish_num end as taskFinishNum,
		pts.next_task_name as nextTaskName,
		pts.next_task_time as nextTaskTime,
		case
		when pts.task_num = 0 then 0
		else
		ROUND(pts.task_finish_num / pts.task_num * 100, 2) end as taskFinishRate,
		case
		when pts.task_num = 0 then 0
		else
		ROUND(pts.task_execute_num / pts.task_num * 100, 2) end as taskExecuteRate
		from
		patient_info p
		left join sign_patient_record sign on p.sign_patient_record_id = sign.id
		left join patient_task_statistics pts on p.id = pts.patient_id

-- 		select p.*, count(p.manage_route_node_id) as taskNum, SUM(p.task_finish_status) as taskFinishNum, SUM(p.task_execute_status) as taskExecuteNum,
-- 		case
-- 		when count(p.manage_route_node_id) = 0 then 0
-- 		else
-- 		ROUND(SUM(p.task_finish_status) / count(p.manage_route_node_id) * 100, 2) end as taskFinishRate,
-- 		case
-- 		when count(p.manage_route_node_id) = 0 then 0
-- 		else
-- 		ROUND(SUM(p.task_execute_status) / count(p.manage_route_node_id) * 100, 2) end as taskExecuteRate
-- 		from
-- 		patient_task_view p
		<where>
		p.del_flag = 0
			<if test="residentId != null">
				and p.resident_id = #{residentId}
			</if>
            <if test="patientInfoImportId != null">
                and p.patient_info_import_id = #{patientInfoImportId}
            </if>
            <if test="sn != null and sn != ''">
                and p.sn = #{sn}
            </if>
			<if test="patientName != null and patientName != ''">
				and p.patient_name = #{patientName}
			</if>
			<if test="patientPhone != null and patientPhone != ''">
				and p.patient_phone like concat('%', #{patientPhone}, '%')
			</if>
			<if test="cardNo != null and cardNo != ''">
				and p.card_no = #{cardNo}
			</if>
			<if test="patientType != null  and patientType != ''">
				<choose>
					<when test="patientType == 'OUTPATIENT_DISCHARGE'.toString()">
						and (p.patient_type = 'OUTPATIENT' or p.patient_type = 'DISCHARGED_PATIENT')
					</when>
					<otherwise>
						and p.patient_type = #{patientType}
					</otherwise>
				</choose>
			</if>
			<if test="attendingPhysicianId != null">
				and p.attending_physician_id = #{attendingPhysicianId}
			</if>
			<if test="mainDiagnosis != null  and mainDiagnosis != ''">
				and p.main_diagnosis like concat('%', #{mainDiagnosis}, '%')
			</if>
			<if test="hospitalAgencyId != null ">
				and p.hospital_agency_id = #{hospitalAgencyId}
			</if>
			<if test="campusAgencyId != null ">
				and p.campus_agency_id = #{campusAgencyId}
			</if>
			<if test="departmentId != null ">
				and p.department_id = #{departmentId}
			</if>
			<if test="wardId != null ">
				and p.ward_id = #{wardId}
			</if>
			<if test="visitMethod != null  and visitMethod != ''">
				and p.visit_method = #{visitMethod}
			</if>
			<if test="visitDateStart != null ">
				and date_format(p.visit_date, '%y%m%d') >= date_format(#{visitDateStart}, '%y%m%d')
			</if>
			<if test="visitDateEnd != null ">
				and date_format(p.visit_date, '%y%m%d') &lt;= date_format(#{visitDateEnd}, '%y%m%d')
			</if>
			<if test="admissionTimeStart != null ">
				and date_format(p.admission_time,'%y%m%d') >= date_format(#{admissionTimeStart}, '%y%m%d')
			</if>
			<if test="admissionTimeEnd != null ">
				and date_format(p.admission_time,'%y%m%d') &lt;= date_format(#{admissionTimeEnd}, '%y%m%d')
			</if>
			<if test="dischargeTimeStart != null ">
				and date_format(p.discharge_time,'%y%m%d') >= date_format(#{dischargeTimeStart}, '%y%m%d')
			</if>
			<if test="dischargeTimeEnd != null ">
				and date_format(p.discharge_time,'%y%m%d') &lt;= date_format(#{dischargeTimeEnd}, '%y%m%d')
			</if>
			<if test="appointmentDateStart != null ">
				and date_format(p.appointment_date, '%y%m%d') >= date_format(#{appointmentDateStart}, '%y%m%d')
			</if>
			<if test="appointmentDateEnd != null ">
				and date_format(p.appointment_date, '%y%m%d') &lt;= date_format(#{appointmentDateEnd}, '%y%m%d')
			</if>
			<if test="appointmentTreatmentGroup != null  and appointmentTreatmentGroup != ''">
				and p.appointment_treatment_group = #{appointmentTreatmentGroup}
			</if>
			<if test="registrationNo != null  and registrationNo != ''">
				and p.registration_no = #{registrationNo}
			</if>
			<if test="visitSerialNumber != null  and visitSerialNumber != ''">
				and p.visit_serial_number = #{visitSerialNumber}
			</if>
			<if test="responsibleNurse != null  and responsibleNurse != ''">
				and p.responsible_nurse = #{responsibleNurse}
			</if>
			<if test="patientSource != null  and patientSource != ''">
				and p.patient_source = #{patientSource}
			</if>
			<if test="surgicalName != null  and surgicalName != ''">
				and p.surgical_name = #{surgicalName}
			</if>
			<!-- 数据范围过滤 -->
			${params.dataScope}
		</where>
		order by p.id desc
	</select>

    <select id="getPatientNextTask" parameterType="Long"
            resultType="com.xinelu.manage.vo.patientinfo.PatientNextTaskVo">
        select p.task_name, p.execute_date_time
        from patient_task_view p
        where p.id = #{patientId}
          and p.node_execute_status = 'UNEXECUTED'
        order by p.execute_date_time limit 1
    </select>

    <select id="selectPatientInfoById" parameterType="Long" resultMap="PatientInfoResult">
        <include refid="selectPatientInfoVo"/>
        where id = #{id}
    </select>

    <select id="getPatientBaseInfo" parameterType="Long"
            resultType="com.xinelu.manage.vo.patientinfo.PatientBaseInfoVo">
        select id,
               resident_id,
               patient_name,
               patient_phone,
               family_member_phone,
               birth_date,
               card_no,
               sex,
               address,
               age
        from patient_info
        where id = #{id}
    </select>

    <insert id="insertPatientInfo" parameterType="com.xinelu.manage.domain.patientinfo.PatientInfo"
            useGeneratedKeys="true" keyProperty="id">
        insert into patient_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="residentId != null">
                resident_id,
            </if>
            <if test="patientName != null">
                patient_name,
            </if>
            <if test="patientPhone != null">patient_phone,
            </if>
            <if test="familyMemberPhone != null">family_member_phone,
            </if>
            <if test="birthDate != null">birth_date,
            </if>
            <if test="cardNo != null">card_no,
            </if>
            <if test="sex != null">sex,
            </if>
            <if test="address != null">address,
            </if>
            <if test="patientType != null">patient_type,
            </if>
            <if test="signStatus != null">sign_status,
            </if>
            <if test="signPatientRecordId != null">sign_patient_record_id,
            </if>
            <if test="serviceStatus != null">service_status,
            </if>
            <if test="signTime != null">sign_time,
            </if>
            <if test="visitMethod != null">visit_method,
            </if>
            <if test="attendingPhysicianId != null">attending_physician_id,
            </if>
            <if test="attendingPhysicianName != null">attending_physician_name,
            </if>
            <if test="mainDiagnosis != null">main_diagnosis,
            </if>
            <if test="hospitalAgencyId != null">hospital_agency_id,
            </if>
            <if test="hospitalAgencyName != null">hospital_agency_name,
            </if>
            <if test="campusAgencyId != null">campus_agency_id,
            </if>
            <if test="campusAgencyName != null">campus_agency_name,
            </if>
            <if test="departmentId != null">department_id,
            </if>
            <if test="departmentName != null">department_name,
            </if>
            <if test="wardId != null">ward_id,
            </if>
            <if test="wardName != null">ward_name,
            </if>
            <if test="responsibleNurse != null">
                responsible_nurse,
            </if>
            <if test="patientVisitRecordId != null">
                patient_visit_record_id,
            </if>
            <if test="visitSerialNumber != null">
                visit_serial_number,
            </if>
            <if test="admissionTime != null">
                admission_time,
            </if>
            <if test="dischargeTime != null">
                discharge_time,
            </if>
            <if test="patientPreHospitalizationId != null">
                patient_pre_hospitalization_id,
            </if>
            <if test="appointmentTreatmentGroup != null">appointment_treatment_group,
            </if>
            <if test="registrationNo != null">registration_no,
            </if>
            <if test="registrationDate != null">registration_date,
            </if>
            <if test="appointmentDate != null">appointment_date,
            </if>
            <if test="inHospitalNumber != null">in_hospital_number,
            </if>
            <if test="visitDate != null">visit_date,
            </if>
            <if test="dischargeMethod != null">discharge_method,
            </if>
            <if test="patientSource != null">patient_source,
            </if>
            <if test="surgicalName != null">surgical_name,
            </if>
            <if test="delFlag != null">del_flag,
            </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="residentId != null">#{residentId},
            </if>
            <if test="patientName != null">#{patientName},
            </if>
            <if test="patientPhone != null">#{patientPhone},
            </if>
            <if test="familyMemberPhone != null">#{familyMemberPhone},
            </if>
            <if test="birthDate != null">#{birthDate},
            </if>
            <if test="cardNo != null">#{cardNo},
            </if>
            <if test="sex != null">#{sex},
            </if>
            <if test="address != null">#{address},
            </if>
            <if test="patientType != null">#{patientType},
            </if>
            <if test="signStatus != null">#{signStatus},
            </if>
            <if test="signPatientRecordId != null">#{signPatientRecordId},
            </if>
            <if test="serviceStatus != null">#{serviceStatus},
            </if>
            <if test="signTime != null">#{signTime},
            </if>
            <if test="visitMethod != null">#{visitMethod},
            </if>
            <if test="attendingPhysicianId != null">#{attendingPhysicianId},
            </if>
            <if test="attendingPhysicianName != null">#{attendingPhysicianName},
            </if>
            <if test="mainDiagnosis != null">#{mainDiagnosis},
            </if>
            <if test="hospitalAgencyId != null">#{hospitalAgencyId},
            </if>
            <if test="hospitalAgencyName != null">#{hospitalAgencyName},
            </if>
            <if test="campusAgencyId != null">#{campusAgencyId},
            </if>
            <if test="campusAgencyName != null">#{campusAgencyName},
            </if>
            <if test="departmentId != null">#{departmentId},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="wardId != null">#{wardId},
            </if>
            <if test="wardName != null">#{wardName},
            </if>
            <if test="responsibleNurse != null">#{responsibleNurse},
            </if>
            <if test="patientVisitRecordId != null">#{patientVisitRecordId},
            </if>
            <if test="visitSerialNumber != null">#{visitSerialNumber},
            </if>
            <if test="admissionTime != null">#{admissionTime},
            </if>
            <if test="dischargeTime != null">#{dischargeTime},
            </if>
            <if test="patientPreHospitalizationId != null">
                #{patientPreHospitalizationId},
            </if>
            <if test="appointmentTreatmentGroup != null">#{appointmentTreatmentGroup},
            </if>
            <if test="registrationNo != null">#{registrationNo},
            </if>
            <if test="registrationDate != null">#{registrationDate},
            </if>
            <if test="appointmentDate != null">#{appointmentDate},
            </if>
            <if test="inHospitalNumber != null">#{inHospitalNumber},
            </if>
            <if test="visitDate != null">#{visitDate},
            </if>
            <if test="dischargeMethod != null">#{dischargeMethod},
            </if>
            <if test="patientSource != null">#{patientSource},
            </if>
            <if test="surgicalName != null">#{surgicalName},
            </if>
            <if test="delFlag != null">#{delFlag},
            </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="updatePatientInfoSelective" parameterType="com.xinelu.manage.domain.patientinfo.PatientInfo">
        update patient_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="residentId != null">
                resident_id = #{residentId},
            </if>
            <if test="patientName != null">
                patient_name = #{patientName},
            </if>
            <if test="patientPhone != null">
                patient_phone = #{patientPhone},
            </if>
            <if test="familyMemberPhone != null">
                family_member_phone = #{familyMemberPhone},
            </if>
            <if test="birthDate != null">
                birth_date = #{birthDate},
            </if>
            <if test="cardNo != null">
                card_no = #{cardNo},
            </if>
            <if test="sex != null">
                sex = #{sex},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="patientType != null">
                patient_type = #{patientType},
            </if>
            <if test="signStatus != null">sign_status =
                #{signStatus},
            </if>
            <if test="signPatientRecordId != null">sign_patient_record_id = #{signPatientRecordId},
            </if>
            <if test="serviceStatus != null">service_status = #{serviceStatus},
            </if>
            <if test="signTime != null">sign_time =
                #{signTime},
            </if>
            <if test="visitMethod != null">visit_method =
                #{visitMethod},
            </if>
            <if test="attendingPhysicianId != null">attending_physician_id =
                #{attendingPhysicianId},
            </if>
            <if test="attendingPhysicianName != null">attending_physician_name =
                #{attendingPhysicianName},
            </if>
            <if test="mainDiagnosis != null">main_diagnosis =
                #{mainDiagnosis},
            </if>
            <if test="hospitalAgencyId != null">hospital_agency_id =
                #{hospitalAgencyId},
            </if>
            <if test="hospitalAgencyName != null">hospital_agency_name =
                #{hospitalAgencyName},
            </if>
            <if test="campusAgencyId != null">campus_agency_id =
                #{campusAgencyId},
            </if>
            <if test="campusAgencyName != null">campus_agency_name =
                #{campusAgencyName},
            </if>
            <if test="departmentId != null">department_id =
                #{departmentId},
            </if>
            <if test="departmentName != null">department_name =
                #{departmentName},
            </if>
            <if test="wardId != null">ward_id =
                #{wardId},
            </if>
            <if test="wardName != null">ward_name =
                #{wardName},
            </if>
            <if test="responsibleNurse != null">responsible_nurse =
                #{responsibleNurse},
            </if>
            <if test="patientVisitRecordId != null">patient_visit_record_id =
                #{patientVisitRecordId},
            </if>
            <if test="visitSerialNumber != null">visit_serial_number =
                #{visitSerialNumber},
            </if>
            <if test="admissionTime != null">
                admission_time = #{admissionTime},
            </if>
            <if test="dischargeTime != null">
                discharge_time = #{dischargeTime},
            </if>
            <if test="patientPreHospitalizationId != null">
                patient_pre_hospitalization_id = #{patientPreHospitalizationId},
            </if>
            <if test="appointmentTreatmentGroup != null">appointment_treatment_group =
                #{appointmentTreatmentGroup},
            </if>
            <if test="registrationNo != null">registration_no =
                #{registrationNo},
            </if>
            <if test="registrationDate != null">registration_date =
                #{registrationDate},
            </if>
            <if test="appointmentDate != null">appointment_date =
                #{appointmentDate},
            </if>
            <if test="inHospitalNumber != null">in_hospital_number =
                #{inHospitalNumber},
            </if>
            <if test="visitDate != null">visit_date =
                #{visitDate},
            </if>
            <if test="dischargeMethod != null">discharge_method =
                #{dischargeMethod},
            </if>
            <if test="patientSource != null">patient_source =
                #{patientSource},
            </if>
            <if test="surgicalName != null">surgical_name =
                #{surgicalName},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </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>

    <update id="updatePatientInfo" parameterType="com.xinelu.manage.domain.patientinfo.PatientInfo">
        update patient_info
        set resident_id                    = #{residentId},
            patient_name                   = #{patientName},
            patient_phone                  = #{patientPhone},
            family_member_phone            = #{familyMemberPhone},
            birth_date                     = #{birthDate},
            card_no                        = #{cardNo},
            sex                            = #{sex},
            address                        = #{address},
            patient_type                   = #{patientType},
            sign_status                    = #{signStatus},
            sign_patient_record_id         = #{signPatientRecordId},
            service_status                 = #{serviceStatus},
            sign_time                      = #{signTime},
            visit_method                   = #{visitMethod},
            attending_physician_id         = #{attendingPhysicianId},
            attending_physician_name       = #{attendingPhysicianName},
            main_diagnosis                 = #{mainDiagnosis},
            hospital_agency_id             = #{hospitalAgencyId},
            hospital_agency_name           = #{hospitalAgencyName},
            campus_agency_id               = #{campusAgencyId},
            campus_agency_name             = #{campusAgencyName},
            department_id                  = #{departmentId},
            department_name                = #{departmentName},
            ward_id                        = #{wardId},
            ward_name                      = #{wardName},
            responsible_nurse              = #{responsibleNurse},
            patient_visit_record_id        = #{patientVisitRecordId},
            visit_serial_number            = #{visitSerialNumber},
            admission_time                 = #{admissionTime},
            discharge_time                 = #{dischargeTime},
            patient_pre_hospitalization_id = #{patientPreHospitalizationId},
            appointment_treatment_group    = #{appointmentTreatmentGroup},
            registration_no                = #{registrationNo},
            registration_date              = #{registrationDate},
            appointment_date               = #{appointmentDate},
            in_hospital_number             = #{inHospitalNumber},
            visit_date                     = #{visitDate},
            discharge_method               = #{dischargeMethod},
            patient_source                 = #{patientSource},
            surgical_name                  = #{surgicalName},
            del_flag                       = #{delFlag},
            create_by                      = #{createBy},
            create_time                    = #{createTime},
            update_by                      = #{updateBy},
            update_time                    = #{updateTime}
        where id = #{id}
    </update>
    <update id="deletePatientInfoById" parameterType="Long">
        update patient_info
        set del_flag = 1
        where id = #{id}
    </update>

    <update id="deletePatientInfoByIds" parameterType="String">
        update patient_info set del_flag = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="getPatientInfoByOpenId" parameterType="string" resultType="com.xinelu.manage.domain.residentinfo.ResidentInfo">
        SELECT
        id,
        patient_name,
        patient_phone,
        family_member_phone,
        birth_date,
        card_no,
        sex,
        address,
        open_id,
        union_id,
        official_account_openid,
        avatar_picture_url
        FROM
        resident_info
        <where>
            del_flag = 0
            <if test="openId != null and openId != ''">
                and open_id = #{openId}
            </if>
        </where>
    </select>

    <select id="getPatientInfoCountByCreateTime" resultType="java.lang.Integer">
        select
        count(1)
        from patient_info
        where
        del_flag = 0
        <if test="firstDay != null">
            and DATE(create_time) >= #{firstDay}
        </if>
        <if test="firstDay != null">
            and DATE(create_time) &lt;= #{now}
        </if>
    </select>

    <select id="selectPatientInfoCountBySignTime" resultType="java.lang.Integer">
        select
        count(1)
        from patient_info
        where
        del_flag = 0
        <if test="firstDay != null">
            and DATE(sign_time) >= #{firstDay}
        </if>
        <if test="firstDay != null">
            and DATE(sign_time) &lt;= #{now}
        </if>
    </select>

    <select id="selectPatientSignTotalCount" resultType="java.lang.Integer">
        select count(1)
        from patient_info
        where del_flag = 0
          and (service_status = 'SERVICE_CENTER' or service_status = 'SERVICE_END')
    </select>

    <select id="selectPatientSignServiceCount" resultType="java.lang.Integer">
        select count(1)
        from patient_info
        where del_flag = 0
          and service_status = 'SERVICE_CENTER'
    </select>

    <select id="selectPatientInfoByPatientName" resultType="com.xinelu.manage.domain.patientinfo.PatientInfo">
        select
        patient_name,
        patient_phone,
        create_time
        from patient_info
        <where>
            (patient_name, patient_phone) IN
            <foreach item="list" index="index" collection="list" open="(" separator="," close=")">
                (#{list.patientName}, #{list.patientPhone})
            </foreach>
            AND date_format(create_time,'%y%m%d') = date_format(#{nowDate},'%y%m%d')
        </where>
    </select>

    <insert id="insertPatientInfoList"  useGeneratedKeys="true" keyProperty="id">
        insert into patient_info(
        resident_id,
        patient_info_import_id,
        visit_date,
        in_hospital_number,
        patient_name,
        card_no,
        age,
        main_diagnosis,
        patient_phone,
        department_id,
        department_name,
        sn,
        del_flag,
        hospital_agency_id,
        hospital_agency_name,
        create_time,
        create_by
        ) values
        <foreach item="PatientInfoImport" index="index" collection="list" separator=",">
            (
            #{PatientInfoImport.id},
            #{PatientInfoImport.patientInfoImportId},
            #{PatientInfoImport.visitDate},
            #{PatientInfoImport.inHospitalNumber},
            #{PatientInfoImport.patientName},
            #{PatientInfoImport.cardNo},
            #{PatientInfoImport.age},
            #{PatientInfoImport.mainDiagnosis},
            #{PatientInfoImport.patientPhone},
            #{PatientInfoImport.departmentId},
            #{PatientInfoImport.departmentName},
            #{PatientInfoImport.sn},
            0,
            #{PatientInfoImport.hospitalAgencyId},
            #{PatientInfoImport.hospitalAgencyName},
            #{PatientInfoImport.createTime},
            #{PatientInfoImport.createBy}
            )
        </foreach>
    </insert>
</mapper>
