<?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.familydoctor.applet.mapper.ResidentSignApplyMapper">

    <resultMap id="BaseResultMap" type="com.xinelu.familydoctor.applet.pojo.entity.ResidentSignApplyEntity">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="bookingNo" column="booking_no" jdbcType="VARCHAR"/>
        <result property="patientId" column="patient_id" jdbcType="VARCHAR"/>
        <result property="residentName" column="resident_name" jdbcType="VARCHAR"/>
        <result property="gender" column="gender" jdbcType="VARCHAR"/>
        <result property="birthday" column="birthday" jdbcType="DATE"/>
        <result property="nation" column="nation" jdbcType="VARCHAR"/>
        <result property="identity" column="identity" jdbcType="VARCHAR"/>
        <result property="phone" column="phone" jdbcType="VARCHAR"/>
        <result property="province" column="province" jdbcType="VARCHAR"/>
        <result property="provinceName" column="province_name" jdbcType="VARCHAR"/>
        <result property="city" column="city" jdbcType="VARCHAR"/>
        <result property="cityName" column="city_name" jdbcType="VARCHAR"/>
        <result property="county" column="county" jdbcType="VARCHAR"/>
        <result property="countyName" column="county_name" jdbcType="VARCHAR"/>
        <result property="town" column="town" jdbcType="VARCHAR"/>
        <result property="townName" column="town_name" jdbcType="VARCHAR"/>
        <result property="committee" column="committee" jdbcType="VARCHAR"/>
        <result property="committeeName" column="committee_name" jdbcType="VARCHAR"/>
        <result property="address" column="address" jdbcType="VARCHAR"/>
        <result property="crowdsNo" column="crowds_no" jdbcType="VARCHAR"/>
        <result property="crowdsName" column="crowds_name" jdbcType="VARCHAR"/>
        <result property="packagesNo" column="packages_no" jdbcType="VARCHAR"/>
        <result property="packagesName" column="packages_name" jdbcType="VARCHAR"/>
        <result property="orgNo" column="org_no" jdbcType="VARCHAR"/>
        <result property="orgName" column="org_name" jdbcType="VARCHAR"/>
        <result property="teamNo" column="team_no" jdbcType="VARCHAR"/>
        <result property="teamName" column="team_name" jdbcType="VARCHAR"/>
        <result property="userNo" column="user_no" jdbcType="VARCHAR"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR"/>
        <result property="signTime" column="sign_time" jdbcType="TIMESTAMP"/>
        <result property="signYears" column="sign_years" jdbcType="INTEGER"/>
        <result property="residentAutographPath" column="resident_autograph_path" jdbcType="VARCHAR"/>
        <result property="relationshipWithHouseholder" column="relationship_with_householder" jdbcType="VARCHAR"/>
        <result property="householderName" column="householder_name" jdbcType="VARCHAR"/>
        <result property="householderIdentity" column="householder_identity" jdbcType="VARCHAR"/>
        <result property="applyTime" column="apply_time" jdbcType="TIMESTAMP"/>
        <result property="bookingStatus" column="booking_status" jdbcType="VARCHAR"/>
        <result property="cancelTime" column="cancel_time" jdbcType="TIMESTAMP"/>
        <result property="approvalStatus" column="approval_status" jdbcType="VARCHAR"/>
        <result property="refuseReason" column="refuse_reason" jdbcType="VARCHAR"/>
        <result property="approvalTime" column="approval_time" jdbcType="TIMESTAMP"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
        <result property="cityCode" column="city_code" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id
        ,booking_no,patient_id,
        resident_name,gender,birthday,
        nation,identity,phone,
        province,province_name,city,
        city_name,county,county_name,
        town,town_name,committee,
        committee_name,address,crowds_no,
        crowds_name,packages_no,packages_name,
        org_no,org_name,team_no,
        team_name,user_no,user_name,
        sign_time,sign_years,resident_autograph_path,relationship_with_householder,
        householder_name,householder_identity,apply_time,
        booking_status,cancel_time,approval_status,
        refuse_reason,approval_time,remark,city_code
    </sql>

    <sql id="Base_Column_List_fd">
        p.id,p.booking_no,p.patient_id,
        p.resident_name,p.gender,p.birthday,
        p.nation,p.identity,p.phone,
        p.province,p.province_name,p.city,
        p.city_name,p.county,p.county_name,
        p.town,p.town_name,p.committee,
        p.committee_name,p.address,p.crowds_no,
        p.crowds_name,p.packages_no,p.packages_name,
        p.org_no,p.org_name,p.team_no,
        p.team_name,p.user_no,p.user_name,
        p.sign_time,p.sign_years,p.resident_autograph_path,p.relationship_with_householder,
        p.householder_name,p.householder_identity,p.apply_time,
        p.booking_status,p.cancel_time,p.approval_status,
        p.refuse_reason,p.approval_time,p.remark,city_code
    </sql>

    <!-- 新增 -->
    <insert id="insert">
        insert into resident_sign_apply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bookingNo != null and bookingNo != ''">booking_no,
            </if>
            <if test="patientId != null and patientId != ''">patient_id,
            </if>
            <if test="residentName != null and residentName != ''">resident_name,
            </if>
            <if test="gender != null and gender != ''">gender,
            </if>
            <if test="birthday != null">birthday,
            </if>
            <if test="nation != null and nation != ''">nation,
            </if>
            <if test="identity != null and identity != ''">identity,
            </if>
            <if test="phone != null and phone != ''">phone,
            </if>
            <if test="province != null and province != ''">province,
            </if>
            <if test="provinceName != null and provinceName != ''">province_name,
            </if>
            <if test="city != null and city != ''">city,
            </if>
            <if test="cityName != null and cityName != ''">city_name,
            </if>
            <if test="county != null and county != ''">county,
            </if>
            <if test="countyName != null and countyName != ''">county_name,
            </if>
            <if test="town != null and town != ''">town,
            </if>
            <if test="townName != null and townName != ''">town_name,
            </if>
            <if test="committee != null and committee != ''">committee,
            </if>
            <if test="committeeName != null and committeeName != ''">committee_name,
            </if>
            <if test="address != null and address != ''">address,
            </if>
            <if test="crowdsNo != null">crowds_no,
            </if>
            <if test="crowdsName != null">crowds_name,
            </if>
            <if test="packagesNo != null">packages_no,
            </if>
            <if test="packagesName != null">packages_name,
            </if>
            <if test="orgNo != null and orgNo != ''">org_no,
            </if>
            <if test="orgName != null and orgName != ''">org_name,
            </if>
            <if test="teamNo != null and teamNo != ''">team_no,
            </if>
            <if test="teamName != null and teamName != ''">team_name,
            </if>
            <if test="userNo != null and userNo != ''">user_no,
            </if>
            <if test="userName != null and userName != ''">user_name,
            </if>
            <if test="signTime != null">sign_time,
            </if>
            <if test="signYears != null">
                sign_years,
            </if>
            <if test="residentAutographPath != null">resident_autograph_path,
            </if>
            <if test="residentAvatar != null">resident_avatar,
            </if>
            <if test="relationshipWithHouseholder != null and relationshipWithHouseholder != ''">
                relationship_with_householder,
            </if>
            <if test="householderName != null">householder_name,
            </if>
            <if test="householderIdentity != null">householder_identity,
            </if>
            <if test="applyTime != null">apply_time,
            </if>
            <if test="bookingStatus != null and bookingStatus != ''">booking_status,
            </if>
            <if test="cancelTime != null">cancel_time,
            </if>
            <if test="approvalStatus != null and approvalStatus != ''">approval_status,
            </if>
            <if test="refuseReason != null">refuse_reason,
            </if>
            <if test="approvalTime != null">approval_time,
            </if>
            <if test="remark != null">remark,
            </if>
            <if test="cityCode != null">
                city_code,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bookingNo != null and bookingNo != ''">#{bookingNo},
            </if>
            <if test="patientId != null and patientId != ''">#{patientId},
            </if>
            <if test="residentName != null and residentName != ''">#{residentName},
            </if>
            <if test="gender != null and gender != ''">#{gender},
            </if>
            <if test="birthday != null">#{birthday},
            </if>
            <if test="nation != null and nation != ''">#{nation},
            </if>
            <if test="identity != null and identity != ''">#{identity},
            </if>
            <if test="phone != null and phone != ''">#{phone},
            </if>
            <if test="province != null and province != ''">#{province},
            </if>
            <if test="provinceName != null and provinceName != ''">#{provinceName},
            </if>
            <if test="city != null and city != ''">#{city},
            </if>
            <if test="cityName != null and cityName != ''">#{cityName},
            </if>
            <if test="county != null and county != ''">#{county},
            </if>
            <if test="countyName != null and countyName != ''">#{countyName},
            </if>
            <if test="town != null and town != ''">#{town},
            </if>
            <if test="townName != null and townName != ''">#{townName},
            </if>
            <if test="committee != null and committee != ''">#{committee},
            </if>
            <if test="committeeName != null and committeeName != ''">#{committeeName},
            </if>
            <if test="address != null and address != ''">#{address},
            </if>
            <if test="crowdsNo != null">#{crowdsNo},
            </if>
            <if test="crowdsName != null">#{crowdsName},
            </if>
            <if test="packagesNo != null">#{packagesNo},
            </if>
            <if test="packagesName != null">#{packagesName},
            </if>
            <if test="orgNo != null and orgNo != ''">#{orgNo},
            </if>
            <if test="orgName != null and orgName != ''">#{orgName},
            </if>
            <if test="teamNo != null and teamNo != ''">#{teamNo},
            </if>
            <if test="teamName != null and teamName != ''">#{teamName},
            </if>
            <if test="userNo != null and userNo != ''">#{userNo},
            </if>
            <if test="userName != null and userName != ''">#{userName},
            </if>
            <if test="signTime != null">#{signTime},
            </if>
            <if test="signYears != null">
                #{signYears},
            </if>
            <if test="residentAutographPath != null">#{residentAutographPath},
            </if>
            <if test="residentAvatar != null">#{residentAvatar},
            </if>
            <if test="relationshipWithHouseholder != null and relationshipWithHouseholder != ''">
                #{relationshipWithHouseholder},
            </if>
            <if test="householderName != null">#{householderName},
            </if>
            <if test="householderIdentity != null">#{householderIdentity},
            </if>
            <if test="applyTime != null">#{applyTime},
            </if>
            <if test="bookingStatus != null and bookingStatus != ''">#{bookingStatus},
            </if>
            <if test="cancelTime != null">#{cancelTime},
            </if>
            <if test="approvalStatus != null and approvalStatus != ''">#{approvalStatus},
            </if>
            <if test="refuseReason != null">#{refuseReason},
            </if>
            <if test="approvalTime != null">#{approvalTime},
            </if>
            <if test="remark != null">#{remark},
            </if>
            <if test="cityCode != null">
                #{cityCode},
            </if>
        </trim>
    </insert>

    <!-- 取消签约申请 -->
    <update id="cancel">
        update resident_sign_apply
        set booking_status = '1',
            cancel_time    = now()
        where booking_no = #{bookingNo}
    </update>

    <select id="findByBody" resultType="com.xinelu.familydoctor.applet.pojo.vo.ResidentSignApplyVo">
        select <include refid="Base_Column_List_fd"></include>
        from resident_sign_apply p
        where 1=1
        <if test="identity != null and identity != ''">
            and p.identity = #{identity}
        </if>
        <if test="residentName != null and residentName != ''">
            and p.resident_name like concat(#{residentName},'%')
        </if>
        <if test="orgNo != null and orgNo != ''">
            and p.org_no = #{orgNo}
        </if>
        <if test="teamNo != null and teamNo != ''">
            and p.team_no = #{teamNo}
        </if>
        <if test="userNo != null and userNo != ''">
            and p.user_no = #{userNo}
        </if>
        <if test="startDate != null">
            and p.sign_time >= #{startDate}
        </if>
        <if test="endDate != null">
            and p.sign_time &lt;= #{endDate}
        </if>
        <if test="bookingStatus != null and bookingStatus != ''">
            and p.booking_status = #{bookingStatus}
        </if>
        <if test="approvalStatus != null and approvalStatus != ''">
            and p.approval_status = #{approvalStatus}
        </if>
        <if test="address != null and address != ''">
            and p.address like concat('%', #{address},'%')
        </if>
        <if test="completed != null and completed != ''">
            <choose>
                <!-- 已完成 -->
                <when test="completed == 1">
                    and (p.booking_status = '1' or p.approval_status in ('1','2'))
                </when>
                <!-- 未完成 -->
                <when test="completed == 2">
                    and (p.booking_status = '0' and p.approval_status = '0')
                </when>
                <otherwise></otherwise>
            </choose>
        </if>
        order by p.apply_time desc
    </select>

    <!-- 查询未完成的签约申请 -->
    <select id="getSignBookingByIdentity" resultType="com.xinelu.familydoctor.applet.pojo.vo.ResidentSignApplyVo">
        select <include refid="Base_Column_List"></include> from resident_sign_apply where identity = #{identity} and
        booking_status = '0' and approval_status = '0'
        <if test="region != null and region != ''">
            and city_code = #{region}
        </if>
        order by apply_time desc limit 1
    </select>

    <!-- 签约申请详情 -->
    <select id="detail" resultType="com.xinelu.familydoctor.applet.pojo.vo.ResidentSignApplyVo">
        select <include refid="Base_Column_List"></include> from resident_sign_apply where booking_no = #{bookingNo}
    </select>

    <update id="updateEx">
        update resident_sign_apply
        <set>
            <if test="approvalStatus != null and approvalStatus != ''">
                approval_status = #{approvalStatus},
            </if>
            <if test="refuseReason != null and refuseReason != ''">
                refuse_reason = #{refuseReason},
            </if>
            approval_time = now()
        </set>
        where booking_no = #{applyNo};
    </update>

</mapper>
