<?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.CenterMapper">
    <select id="selectLocation" resultType="LocationDTO">
        <![CDATA[
            SELECT LOCATION_CODE AS locationCode,
                   LOCATION_NAME AS locationName
              FROM LOCATION_INFO
             WHERE 1 = 1
               AND LOCATION_CODE != '00'
        ]]>
    </select>
    
    <insert id="insertCenter" parameterType="CenterInfoDTO" useGeneratedKeys="true">
        <selectKey keyProperty="centerCode" resultType="int" order="AFTER">
            <![CDATA[
                SELECT CURRENT_VAL AS centerCode FROM db_serial WHERE NAME = 'center_info_ai_center_code'
            ]]>
        </selectKey>
        <![CDATA[
            INSERT INTO CENTER_INFO
                        (CENTER_NAME,   LOCATION_CODE,   CENTER_ADDRESS, CENTER_NUMBER,   TOTAL_CAPACITY,   TOTAL_STAFF,   CREATE_DATE)
                 VALUES (#{centerName}, #{locationCode}, #{centerAddress},     #{centerNumber}, #{totalCapacity}, #{totalStaff}, NOW())
        ]]>
    </insert>
    
    <select id="selectCenterInfo" parameterType="CenterInfoDTO" resultType="CenterInfoDTO">
        <![CDATA[
            SELECT CI.CENTER_CODE                                AS centerCode,
                   CI.CENTER_NAME                                AS centerName,
                   CI.LOCATION_CODE                              AS locationCode,
                   (SELECT LOCATION_NAME
                      FROM LOCATION_INFO LI
                     WHERE LI.LOCATION_CODE = CI.LOCATION_CODE)  AS locationName,
                   CI.CENTER_ADDRESS                             AS centerAddress,
                   CI.CENTER_NUMBER                              AS centerNumber,
                   CI.TOTAL_CAPACITY                             AS totalCapacity,
                   CI.TOTAL_STAFF                                AS totalStaff,
                   DATE_FORMAT(CI.CREATE_DATE, '%Y-%m-%d %H:%i') AS createDate,
                   DATE_FORMAT(CI.UPDATE_DATE, '%Y-%m-%d %H:%i') AS updateDate,
                   M.ID                                          AS staffId,
                   M.NAME                                        AS staffName,
                   M.PHONE_NUMBER                                AS staffPhoneNumber
              FROM CENTER_INFO CI
              LEFT OUTER JOIN MEMBER M
                ON CI.CENTER_CODE = M.CENTER_CODE
             WHERE CI.CENTER_CODE = #{centerCode}
        ]]>
    </select>
    
    <select id="selectCenterCount" parameterType="CenterInfoDTO" resultType="int">
        <![CDATA[
            SELECT COUNT(*) AS total
              FROM CENTER_INFO CI
              LEFT OUTER JOIN MEMBER M
                ON CI.CENTER_CODE = M.CENTER_CODE
             WHERE 1 = 1
        ]]>
        <if test='locationCode != null and locationCode != ""'>
            <![CDATA[
                   AND CI.LOCATION_CODE = #{locationCode}
            ]]>
        </if>
        <if test='centerName != null and centerName != ""'>
            <![CDATA[
                AND CI.CENTER_NAME LIKE CONCAT('%', #{centerName}, '%')
            ]]>
        </if>
        <if test='startDate != null and startDate != "" and endDate != null and endDate != ""'>
            <![CDATA[
                AND DATE_FORMAT(CI.CREATE_DATE, '%Y-%m-%d') BETWEEN #{startDate} AND #{endDate}
            ]]>
        </if>
    </select>
    
    <select id="selectCenterList" parameterType="CenterInfoDTO" resultType="CenterInfoDTO">
        <![CDATA[
            SELECT ROWNUM                                        AS num,
                   CI.CENTER_CODE                                AS centerCode,
                   CI.CENTER_NAME                                AS centerName,
                   (SELECT LOCATION_NAME
                      FROM LOCATION_INFO LI
                     WHERE LI.LOCATION_CODE = CI.LOCATION_CODE)  AS locationName,
                   M.NAME                                        AS staffName,
                   M.PHONE_NUMBER                                AS staffPhoneNumber,
                   CI.CENTER_NUMBER                              AS centerNumber,
                   CI.TOTAL_PATIENT                              AS totalPatient,
                   CI.TOTAL_CAPACITY                             AS totalCapacity,
                   CI.TOTAL_STAFF                                AS totalStaff,
                   DATE_FORMAT(CI.CREATE_DATE, '%Y-%m-%d %H:%i') AS createDate
              FROM CENTER_INFO CI
              LEFT OUTER JOIN MEMBER M
                ON CI.CENTER_CODE = M.CENTER_CODE
             WHERE 1 = 1
        ]]>
        <if test='locationCode != null and locationCode != ""'>
            <![CDATA[
                   AND CI.LOCATION_CODE = #{locationCode}
            ]]>
        </if>
        <if test='centerName != null and centerName != ""'>
            <![CDATA[
                AND CI.CENTER_NAME LIKE CONCAT('%', #{centerName}, '%')
            ]]>
        </if>
        <if test='startDate != null and startDate != "" and endDate != null and endDate != ""'>
            <![CDATA[
                AND DATE_FORMAT(CI.CREATE_DATE, '%Y-%m-%d') BETWEEN #{startDate} AND #{endDate}
            ]]>
        </if>
        <![CDATA[
             ORDER BY num DESC
             LIMIT ${limit}, ${limitMax}
        ]]>
    </select>
    
    <update id="updateCenterInfo" parameterType="CenterInfoDTO">
        <![CDATA[
            UPDATE CENTER_INFO
               SET LOCATION_CODE = #{locationCode},
                   CENTER_ADDRESS = #{centerAddress},
                   CENTER_NUMBER = #{centerNumber},
                   TOTAL_CAPACITY = #{totalCapacity},
                   TOTAL_STAFF = #{totalStaff}
             WHERE CENTER_CODE = ${centerCode}
        ]]>
    </update>
    
</mapper>