<?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.applet.mapper.nearbynursingstation.NearbyNursingStationMapper">

    <resultMap type="NurseStationPerson" id="NurseStationPersonResult">
        <result property="id" column="id"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="departmentCode" column="department_code"/>
        <result property="userId" column="user_id"/>
        <result property="nursePersonCode" column="nurse_person_code"/>
        <result property="nursePersonName" column="nurse_person_name"/>
        <result property="nursePersonType" column="nurse_person_type"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <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>

    <!-- 护理站基本信息映射结果集 -->
    <resultMap type="com.xinelu.applet.vo.nearbynursingstation.NearbyNursingStationVO" id="NurseStationInfoResult">
        <result property="nurseStationId" column="nurseStationId"/>
        <result property="areaCode" column="area_code"/>
        <result property="userId" column="user_id"/>
        <result property="nurseStationCode" column="nurse_station_code"/>
        <result property="nurseStationName" column="nurse_station_name"/>
        <result property="nurseStationType" column="nurse_station_type"/>
        <result property="agencyIntroduce" column="agency_introduce"/>
        <result property="nurseStationDescription" column="nurse_station_description"/>
        <result property="longitude" column="longitude"/>
        <result property="latitude" column="latitude"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="dutyPerson" column="duty_person"/>
        <result property="dutyPhone" column="duty_phone"/>
        <result property="stationPictureUrl" column="station_picture_url"/>
        <result property="stationIntroducePictureUrl" column="station_introduce_picture_url"/>
        <result property="sort" column="sort"/>
        <result property="nurseGrade" column="nurse_grade"/>
        <result property="openingHoursDescribe" column="opening_hours_describe"/>
        <result property="morningOpenStartTime" column="morning_open_start_time"/>
        <result property="morningOpenEndTime" column="morning_open_end_time"/>
        <result property="afternoonOpenStartTime" column="afternoon_open_start_time"/>
        <result property="afternoonOpenEndTime" column="afternoon_open_end_time"/>
        <collection property="nurseStationLabelList" javaType="java.util.List" resultMap="NurseStationLabelInfoResult"/>
        <collection property="poserInfoList" javaType="java.util.List" resultMap="PoserInfoResult"/>
    </resultMap>

    <!-- 护理站标签信息结果集映射 -->
    <resultMap type="NurseStationLabel" id="NurseStationLabelInfoResult">
        <result property="id" column="id"/>
        <result property="labelCode" column="label_code"/>
        <result property="labelDescription" column="label_description"/>
    </resultMap>

    <!-- 海报模块信息结果映射 -->
    <resultMap type="com.xinelu.applet.vo.nearbynursingstation.PoserInfoHomeVO" id="PoserInfoResult">
        <result property="id" column="poserInfoId"/>
        <result property="poserName" column="poser_name"/>
        <result property="posterPictureUrl" column="poster_picture_url"/>
        <result property="posterVideoUrl" column="poster_video_url"/>
        <result property="moduleType" column="module_type"/>
        <result property="jumpLink" column="jump_link"/>
        <result property="dictCode" column="dict_code"/>
        <result property="dictLabel" column="dict_label"/>
        <result property="dictValue" column="dict_value"/>
        <result property="dictType" column="dict_type"/>
    </resultMap>

    <sql id="selectNurseStationVo">
        select id,
               area_code,
               user_id,
               nurse_station_code,
               nurse_station_name,
               nurse_station_type,
               agency_introduce,
               nurse_station_description,
               longitude,
               latitude,
               phone,
               address,
               duty_person,
               duty_phone,
               station_picture_url,
               sort,
               create_by,
               create_time,
               update_by,
               update_time
        from nurse_station
    </sql>


    <select id="getInformationCategoryList" parameterType="string"
            resultType="com.xinelu.applet.vo.nearbynursingstation.InformationCategoryVO">
        SELECT
        IFNULL( ic.information_category_name, '' ) information_category_name,
        IFNULL( ic.information_category_type, '' ) information_category_type,
        i.id,
        IFNULL( i.information_title, '' ) information_title,
        IFNULL( i.information_code, '' ) information_code,
        IFNULL( i.information_content, '' ) information_content,
        IFNULL( i.information_jump_link, '' ) information_jump_link,
        IFNULL( i.information_source, '' ) information_source,
        IFNULL( i.lead_thumbnail_url, '' ) lead_thumbnail_url
        FROM
        information i
        LEFT JOIN information_category ic ON i.information_category_id = ic.id
        <where>
            <if test="informationCategoryType != null  and informationCategoryType != ''">
                and ic.information_category_type = #{informationCategoryType}
            </if>
        </where>
        ORDER BY i.information_sort
    </select>

    <select id="selectNursingStationIntroductionList" parameterType="list"
            resultType="com.xinelu.applet.vo.nearbynursingstation.NursingStationAndClassifyVO">
        SELECT
        ns.id nurseStationId,
        ns.nurse_station_code,
        ns.nurse_station_name,
        ns.longitude,
        ns.latitude,
        ns.station_picture_url,
        ns.sort,
        nci.id classifyId,
        nci.classify_name
        FROM
        nurse_station ns
        LEFT JOIN nurse_station_item nsi ON ns.id = nsi.nurse_station_id
        LEFT JOIN nurse_station_classify_relation nscr ON ns.id = nscr.nurse_station_id
        LEFT JOIN nurse_classify_info nci ON nscr.nurse_classify_id = nci.id
        <where>
            <if test="dto.nurseStationId != null ">
                and ns.id = #{dto.nurseStationId}
            </if>
            <if test="dto.nurseStationName != null and dto.nurseStationName != ''">
                and ns.nurse_station_name like concat('%', #{dto.nurseStationName}, '%')
            </if>
            <!-- 只选择了护理项目分类作为赛选条件 -->
            <if test="dto.nurseItemClassifyInfoId != null and dto.nurseClassifyInfoId == null">
                and nsi.nurse_classify_id = #{dto.nurseItemClassifyInfoId}
            </if>
            <!-- 只选择了护理站分类作为赛选条件 -->
            <if test="dto.nurseClassifyInfoId != null and dto.nurseItemClassifyInfoId == null">
                and nscr.nurse_classify_id = #{dto.nurseClassifyInfoId}
            </if>
            <!-- 护理站分类和护理项目分类同时为赛选条件 -->
            <if test="dto.nurseClassifyInfoId != null and dto.nurseItemClassifyInfoId != null">
                and nsi.nurse_classify_id = #{dto.nurseItemClassifyInfoId} and nscr.nurse_classify_id =
                #{dto.nurseClassifyInfoId}
            </if>
            <!-- 输入框名称 -->
            <if test="dto.searchName != null and dto.searchName != ''">
                and (nci.classify_name like concat(#{dto.searchName}, '%')
                or ns.nurse_station_name like concat(#{dto.searchName}, '%'))
            </if>
        </where>
        GROUP BY
        ns.id
    </select>

    <select id="getNurseItemList" parameterType="com.xinelu.applet.vo.nearbynursingstation.NearbyNursingStationItem"
            resultType="com.xinelu.applet.vo.nearbynursingstation.NearbyNursingStationItem">
        select
        nsi.nurse_item_code,
        nsi.nurse_item_name,
        nsi.nurse_item_content,
        nsi.advance_appoint_duration,
        nsi.nurse_station_id stationId,
        nsi.id stationItemId,
        nsip.id stationItemPriceId,
        nsi.nurse_type_id,
        IFNULL(nsip.serve_duration_unit, '') serve_duration_unit,
        nsip.price,
        IFNULL(nsi.item_picture_url, '') item_picture_url,
        IFNULL(nsip.description, '') description,
        IFNULL(nt.nurse_type_name, '') nurse_type_name
        from nurse_station_item nsi
        LEFT JOIN nurse_station_item_price nsip ON nsip.nurse_station_item_id = nsi.id
        LEFT JOIN nurse_type nt ON nsi.nurse_type_id = nt.id
        <where>
            <if test="nurseStationId != null ">
                and nsi.nurse_station_id = #{nurseStationId}
            </if>
        </where>
    </select>

    <select id="getNursePersonList" parameterType="NurseStationPerson" resultType="NurseStationPerson">
        select nurse_station_id,
        department_code,
        nurse_person_code,
        nurse_person_name,
        nurse_person_type,
        phone,
        address
        from nurse_station_person
        <where>
            <if test="nurseStationId != null ">
                and nurse_station_id = #{nurseStationId}
            </if>
        </where>
    </select>

    <select id="getNurseStationLabelByList" parameterType="NurseStationLabel"
            resultType="NurseStationLabel">
        SELECT
        id,
        nurse_station_id,
        user_id,
        label_code,
        label_description,
        sort,
        create_by,
        create_time,
        update_by,
        update_time
        FROM
        nurse_station_label
        <where>
            <if test="nurseStationIdList != null and nurseStationIdList.size() > 0">
                and nurse_station_id in
                <foreach item="nurseStationId" collection="nurseStationIdList" open="(" separator="," close=")">
                    #{nurseStationId}
                </foreach>
            </if>
        </where>
    </select>


    <select id="selectNurseTypeList" resultType="NurseType">
        SELECT id,
               user_id,
               nurse_type_code,
               nurse_type_name,
               create_by,
               create_time,
               update_by,
               update_time
        FROM nurse_type
        order by id asc
    </select>

    <select id="selectNursingStationIntroductionListTwo" resultMap="NurseStationInfoResult">
        SELECT
        ns.id nurseStationId,
        IFNULL(ns.area_code, '') area_code,
        IFNULL(ns.nurse_station_code, '') nurse_station_code,
        IFNULL(ns.nurse_station_name, '') nurse_station_name,
        IFNULL(ns.nurse_station_type, '') nurse_station_type,
        IFNULL(ns.agency_introduce, '') agency_introduce,
        IFNULL(ns.nurse_station_description, '') nurse_station_description,
        IFNULL(ns.longitude, '') longitude,
        IFNULL(ns.latitude, '') latitude,
        IFNULL(ns.phone, '') phone,
        IFNULL(ns.address, '') address,
        IFNULL(ns.duty_person, '') duty_person,
        IFNULL(ns.duty_phone, '') duty_phone,
        IFNULL(ns.station_picture_url, '') station_picture_url,
        IFNULL(ns.station_introduce_picture_url, '') station_introduce_picture_url,
        ns.sort,
        ns.nurse_grade,
        ns.opening_hours_describe,
        ns.morning_open_start_time,
        ns.morning_open_end_time,
        ns.afternoon_open_start_time,
        ns.afternoon_open_end_time,
        nsl.id,
        nsl.label_code,
        nsl.label_description,
        pi.id poserInfoId,
        pi.poster_picture_url,
        pi.poster_video_url,
        pi.module_type,
        pi.jump_link
        FROM nurse_station ns
        LEFT JOIN nurse_station_label nsl ON ns.id = nsl.nurse_station_id
        LEFT JOIN poser_info pi ON ns.id = pi.nurse_station_id
        <where>
            <if test="nurseStationId != null ">
                ns.id = #{nurseStationId}
            </if>
        </where>
    </select>

    <select id="getNurseGoodsList" parameterType="Long"
            resultType="com.xinelu.applet.vo.goodinfo.GoodInfoAndItemVO">
        SELECT
        gi.id goodsInfoId,
        gi.goods_name,
        gi.goods_picture_url,
        gi.goods_unit,
        gi.goods_purpose,
        gi.sort goodSort,
        gad.goods_attribute_id,
        gs.attribute_name,
        gad.id goods_attribute_details_id,
        gad.attribute_details_name,
        gad.goods_price,
        gad.goods_stock
        FROM
        goods_info gi
        LEFT JOIN goods_attribute gs ON gi.id = gs.goods_id
        LEFT JOIN goods_attribute_details gad ON gs.id = gad.goods_attribute_id
        <where>
            <if test="nurseStationId != null ">
                and nurse_station_id = #{nurseStationId}
            </if>
            and whether_shelf = 1
        </where>
        ORDER BY gi.sort DESC
    </select>

    <select id="getNurseStationItemList" parameterType="Long"
            resultType="com.xinelu.applet.vo.goodinfo.GoodInfoAndItemVO">
        SELECT
        ns.id AS stationId,
        nsi.id AS stationItemId,
        np.id AS stationItemPriceId,
        nsi.nurse_item_name,
        nsi.item_picture_url,
        nsi.sort AS itemSort,
        nsi.advance_appoint_duration,
        np.serve_duration_unit,
        np.price AS nurseItemPrice
        FROM nurse_station_item nsi
        LEFT JOIN nurse_station_item_price np ON np.nurse_station_item_id = nsi.id
        LEFT JOIN nurse_station ns ON ns.id = nsi.nurse_station_id
        LEFT JOIN nurse_classify_info nci ON nci.id = nsi.nurse_classify_id
        <where>
            <if test="nurseStationId != null ">
                and nsi.nurse_station_id = #{nurseStationId}
            </if>
            <if test="nurseClassifyInfoId != null ">
                and nsi.nurse_classify_id = #{nurseClassifyInfoId}
            </if>
            and nsi.shelf_status= #{shelfStatus}
        </where>
        ORDER BY nsi.sort
    </select>

    <select id="getNurseClassifyList"
            parameterType="com.xinelu.applet.vo.nurseclassifyinfo.NurseClassifyInfoList"
            resultType="com.xinelu.applet.vo.nurseclassifyinfo.NurseClassifyInfoList">
        select id,
        parent_id,
        classify_code,
        classify_name,
        classify_level,
        classify_type,
        classify_picture_url,
        classify_sort
        from nurse_classify_info
        <where>
            <if test="parentId != null ">
                and parent_id = #{parentId}
            </if>
            <if test="classifyCode != null  and classifyCode != ''">
                and classify_code = #{classifyCode}
            </if>
            <if test="classifyName != null  and classifyName != ''">
                and classify_name like concat('%', #{classifyName}, '%')
            </if>
            <if test="classifyLevel != null ">
                and classify_level = #{classifyLevel}
            </if>
            <if test="classifyType != null  and classifyType != ''">
                and classify_type = #{classifyType}
            </if>
            <if test="classifyPictureUrl != null  and classifyPictureUrl != ''">
                and classify_picture_url = #{classifyPictureUrl}
            </if>
            <if test="classifySort != null ">
                and classify_sort = #{classifySort}
            </if>
            <if test="typeList != null and typeList.size() > 0">
                and classify_type in
                <foreach item="type" collection="typeList" open="(" separator="," close=")">
                    #{type}
                </foreach>
            </if>
        </where>
        ORDER BY classify_sort
    </select>


    <select id="getNursingStationIntroductionList"
            parameterType="com.xinelu.applet.vo.nearbynursingstation.NearbyNursingStationVO"
            resultType="com.xinelu.applet.vo.nearbynursingstation.NearbyNursingStationVO">
        SELECT
        nci.classify_name,
        nci.classify_type,
        ni.id nurseStationItemId,
        ni.nurse_classify_id nurseItemClassifyInfoId,
        nsce.id nurseStationClassifyRelationId,
        nci.id nurseClassifyInfoId,
        ns.id nurseStationId,
        ns.area_code,
        ns.nurse_station_code,
        ns.nurse_station_name,
        ns.nurse_station_type,
        ns.agency_introduce,
        ns.nurse_station_description,
        ns.longitude,
        ns.latitude,
        ns.phone,
        ns.address,
        ns.duty_person,
        ns.duty_phone,
        ns.station_picture_url,
        ns.nurse_grade,
        ns.sort
        FROM nurse_station ns
        LEFT JOIN nurse_station_item ni on ni.nurse_station_id = ns.id
        LEFT JOIN nurse_station_classify_relation nsce ON ns.id = nsce.nurse_station_id
        LEFT JOIN nurse_classify_info nci ON nci.id = nsce.nurse_classify_id
        <where>
            <if test="nurseStationId != null ">
                and ns.id = #{nurseStationId}
            </if>
            <if test="nurseStationName != null  and nurseStationName != '' or classifyName != null  and classifyName != '' ">
                and ns.nurse_station_name like concat('%', #{nurseStationName}, '%') or nci.classify_name like
                concat('%', #{classifyName}, '%')
            </if>
            <if test="nurseItemClassifyInfoId != null ">
                and ni.nurse_classify_id = #{nurseItemClassifyInfoId}
            </if>
            <if test="nurseClassifyInfoId != null ">
                and nci.id = #{nurseClassifyInfoId}
            </if>
        </where>
        GROUP BY
        ns.id
        order by ns.sort asc
    </select>


    <select id="getNurseStationClassifyByList"
            parameterType="com.xinelu.applet.vo.nearbynursingstation.NurseStationClassifyVO"
            resultType="com.xinelu.applet.vo.nearbynursingstation.NurseStationClassifyVO">
        SELECT
        nsi.nurse_station_id,
        nsi.nurse_classify_id,
        nci.id,
        nci.parent_id,
        nci.classify_code,
        nci.classify_name,
        nci.classify_level,
        nci.classify_type,
        nci.classify_picture_url,
        nci.classify_sort
        FROM
        nurse_classify_info nci
        LEFT JOIN nurse_station_item nsi ON nsi.nurse_classify_id = nci.id
        <where>
            <if test="nurseStationIdList != null and nurseStationIdList.size() > 0">
                and nsi.nurse_station_id in
                <foreach item="nurseStationId" collection="nurseStationIdList" open="(" separator="," close=")">
                    #{nurseStationId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getHomePagePoserInfoList" parameterType="com.xinelu.applet.vo.nearbynursingstation.PoserInfoHomeVO"
            resultType="com.xinelu.applet.vo.nearbynursingstation.PoserInfoHomeVO">
        SELECT
        pi.id,
        pi.poser_name,
        pi.nurse_station_id,
        pi.nurse_item_id,
        pi.poster_introduce,
        pi.poster_picture_url,
        pi.poster_video_url,
        pi.module_type,
        pi.poser_sort,
        pi.jump_link,
        pi.jump_type,
        pi.jump_dict_id,
        sdd.dict_code,
        sdd.dict_label,
        sdd.dict_value,
        sdd.dict_type
        FROM
        poser_info pi
        left join sys_dict_data sdd on sdd.dict_code = pi.jump_dict_id
        <where>
            <if test="moduleType != null  and moduleType != ''">
                and pi.module_type = #{moduleType}
            </if>
            <if test="nurseStationId != null ">
                and pi.nurse_station_id = #{nurseStationId}
            </if>
        </where>
        ORDER BY pi.poser_sort
    </select>

    <select id="selectNurseClassifyByClassifyType"
            resultType="com.xinelu.manage.domain.nurseclassifyinfo.NurseClassifyInfo">
        select id,
               classify_code,
               classify_name,
               classify_type,
               classify_sort
        from nurse_classify_info
        where classify_type = #{classifyType}
    </select>

    <select id="getStationClassifyInfoList" parameterType="long"
            resultType="com.xinelu.applet.vo.specialdisease.StationClassifyInfoVO">
        SELECT
        nsi.nurse_station_id,
        nsi.nurse_classify_id classifyId,
        nci.classify_name classifyName
        FROM
        nurse_classify_info nci
        INNER JOIN nurse_station_item nsi ON nci.id = nsi.nurse_classify_id
        WHERE
        nsi.nurse_station_id in
        <foreach item="stationId" collection="nurseStationIdList" open="(" separator="," close=")">
            #{stationId}
        </foreach>
        GROUP BY nsi.nurse_station_id, nci.id
    </select>
</mapper>