<?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.lemon.lifecenter.mapper.PatientMapper">
    <select id="selectPatientCount" parameterType="PatientDTO" resultType="int">
        <![CDATA[
            SELECT count(*) AS total
              FROM PATIENT_CARE
             WHERE 1 = 1
        ]]>
        <if test='state != "ALL"'>
            <![CDATA[
               AND STATE = #{state}
            ]]>
        </if>
        <if test='patientName != null and patientName != ""'>
            <![CDATA[
               AND PATIENT_NAME LIKE CONCAT('%', #{patientName}, '%')
            ]]>
        </if>
        <if test='startDate != null and startDate != "" and endDate != null and endDate != ""'>
            <![CDATA[
               AND DATE_FORMAT(FINAL_CLINIC_DATE, '%Y-%m-%d') BETWEEN #{startDate} AND #{endDate}
            ]]>
        </if>
    </select>
    
    <select id="selectPatientList" parameterType="PatientDTO" resultType="PatientDTO">
        <![CDATA[
            SELECT ROWNUM                                                             AS num,
                   PATIENT_NAME                                                       AS patientName,
                   GENDER                                                             AS gender,
                   (SELECT TRUNC((SYSDATE-TO_DATE(19 || JUMIN, 'YYYYMMDD'))/365) + 1) AS age,
                   ward_number                                                        AS wardNumber,
                   room_number                                                        AS roomNumber,
                   DATE_FORMAT(FINAL_CLINIC_DATE, '%Y-%m-%d %H:%i')                   AS finamClinicDate,
                   DATE_FORMAT(HOSPITALIZATION_DATE, '%Y-%m-%d %H:%i')                AS hospitalizationDate,
                   DATE_FORMAT(DISISOLATION_DATE, '%Y-%m-%d %H:%i')                   AS disisolationDate,
                   STATE                                                              AS state
              FROM PATIENT_CARE
             WHERE 1 = 1
        ]]>
    <if test='state != "ALL"'>
        <![CDATA[
            AND STATE = #{state}
        ]]>
    </if>
    <if test='patientName != null and patientName != ""'>
        <![CDATA[
            AND PATIENT_NAME LIKE CONCAT('%', #{patientName}, '%')
        ]]>
    </if>
    <if test='startDate != null and startDate != "" and endDate != null and endDate != ""'>
        <![CDATA[
            AND DATE_FORMAT(FINAL_CLINIC_DATE, '%Y-%m-%d') BETWEEN #{startDate} AND #{endDate}
        ]]>
    </if>
        <![CDATA[
             ORDER BY HOSPITALIZATION_DATE DESC
             LIMIT ${limit}, ${limitMax}
        ]]>
    </select>
</mapper>