<?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.nursingstationgoods.NursingStationGoodsMapper">

    <resultMap type="com.xinelu.applet.dto.nursingstationgoods.GoodDetails" id="GoodDetailsResult">
        <result property="storeInfoId" column="store_info_id"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="goodsInfoId" column="goodsInfoId"/>
        <result property="goodsCategoryId" column="goods_category_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="goodsPictureUrl" column="goods_picture_url"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="goodsRemark" column="goods_remark"/>
        <result property="goodsPurpose" column="goods_purpose"/>
        <collection property="goodAttributeDetailsLists" javaType="java.util.List" resultMap="goodDetailsListsResult"/>
    </resultMap>

    <resultMap type="com.xinelu.applet.dto.nursingstationgoods.GoodAttributeDetailsList" id="goodDetailsListsResult">
        <result property="goodsAttributeId" column="goodsAttributeId"/>
        <result property="attributeDetailsId" column="attributeDetailsId"/>
        <result property="attributeDetailsName" column="attribute_details_name"/>
        <result property="attributeName" column="attribute_name"/>
        <result property="attributePitureUrl" column="attribute_piture_url"/>
        <result property="goodsPrice" column="goods_price"/>
        <result property="goodsStock" column="goods_stock"/>
        <result property="attributeDetailsSort" column="attributeDetailsSort"/>
    </resultMap>

    <select id="getGoodsCategoryList" parameterType="GoodsCategory" resultType="GoodsCategory">
        select id,
        parent_id,
        goods_category_name,
        goods_category_code,
        goods_category_picture,
        category_remark,
        category_level,
        sort,
        create_by,
        create_time,
        update_by,
        update_time
        from goods_category
        <where>
            <if test="id != null">
                and id =#{id}
            </if>
            <if test="parentId != null">
                and parent_id =#{parentId}
            </if>
            <if test="goodsCategoryName != null  and goodsCategoryName != ''">
                and goods_category_name like concat('%', #{goodsCategoryName}, '%')
            </if>
            <if test="goodsCategoryCode != null  and goodsCategoryCode != ''">
                and goods_category_code = #{goodsCategoryCode}
            </if>
            <if test="goodsCategoryPicture != null  and goodsCategoryPicture != ''">
                and goods_category_picture = #{goodsCategoryPicture}
            </if>
            <if test="categoryRemark != null  and categoryRemark != ''">
                and category_remark = #{categoryRemark}
            </if>
            <if test="categoryLevel != null ">
                and category_level = #{categoryLevel}
            </if>
            <if test="sort != null ">
                and sort = #{sort}
            </if>
        </where>
        ORDER BY
        sort
    </select>


    <select id="getChildrenCategoryIds" parameterType="long" resultType="string">
        SELECT getChildrenCategoryIds(#{parentId}) goodChildrenCategoryIds
    </select>


    <select id="getGoodsCategoryNameList" parameterType="com.xinelu.manage.vo.goodsCategory.GoodsCategoryNameVO"
            resultType="com.xinelu.manage.vo.goodsCategory.GoodsCategoryNameVO">
        select id,
        parent_id,
        goods_category_name,
        goods_category_code,
        goods_category_picture,
        category_remark,
        category_level,
        sort
        from goods_category
        <where>
            <if test="goodsCategoryNameId != null and goodsCategoryNameId.size() > 0">
                and id in
                <foreach item="goodsCategoryNameId" collection="goodsCategoryNameId" open="(" separator="," close=")">
                    #{goodsCategoryNameId}
                </foreach>
            </if>
            and category_level != #{categoryLevel}
        </where>
        ORDER BY
        id
    </select>


    <select id="getGoodsInfoList" parameterType="com.xinelu.applet.dto.nursingstationgoods.GoodsList"
            resultType="com.xinelu.applet.dto.nursingstationgoods.GoodsList">
        SELECT
        gi.id goodsInfoId,
        ga.id goodsAttributeId,
        gad.id attributeDetailsId,
        gi.nurse_station_id,
        gi.goods_category_id,
        gi.goods_name,
        ga.attribute_name,
        gad.attribute_details_name,
        gi.goods_picture_url,
        gi.goods_unit,
        gi.sort,
        gad.attribute_piture_url,
        IFNULL((gad.goods_price) ,0) goods_price
        FROM
        goods_info gi
        LEFT JOIN store_info si ON gi.store_info_id = si.id
        LEFT JOIN goods_attribute ga ON ga.goods_id = gi.id
        LEFT JOIN goods_attribute_details gad ON gad.goods_attribute_id = ga.id
        <where>
            <if test="goodsCategoryIds != null and goodsCategoryIds.size() > 0">
                and gi.goods_category_id in
                <foreach item="goodsCategoryIds" collection="goodsCategoryIds" open="(" separator="," close=")">
                    #{goodsCategoryIds}
                </foreach>
            </if>
            <if test="goodsName != null  and goodsName != ''">
                and gi.goods_name like concat( #{goodsName}, '%')
            </if>
            and gi.whether_shelf = 1
            and gi.nurse_station_id is NULL
        </where>
        ORDER BY gi.sort DESC, gi.id, gad.goods_price
    </select>


    <select id="getGoodsDetailsList" parameterType="com.xinelu.applet.dto.nursingstationgoods.GoodDetails" resultMap="GoodDetailsResult">
        SELECT
        gi.id goodsInfoId,
        ga.id goodsAttributeId,
        gad.id attributeDetailsId,
        gi.store_info_id,
        gi.nurse_station_id,
        gi.goods_category_id,
        gi.goods_name,
        gi.goods_picture_url,
        gi.goods_unit,
        gi.goods_remark,
        gi.goods_purpose,
        ga.attribute_name,
        ga.attribute_remark,
        ga.sort goodsAttributeSort,
        gad.attribute_details_name,
        gad.attribute_piture_url,
        gad.goods_stock,
        gad.sort attributeDetailsSort,
        gad.goods_price
        FROM
        goods_info gi
        LEFT JOIN store_info si ON gi.store_info_id = si.id
        LEFT JOIN goods_attribute ga ON ga.goods_id = gi.id
        LEFT JOIN goods_attribute_details gad ON gad.goods_attribute_id = ga.id
        <where>
            <if test="goodsInfoId != null ">
                and gi.id = #{goodsInfoId}
            </if>
        </where>
        ORDER BY gad.sort DESC
    </select>

    <select id="getReceiveAddressInfoList" parameterType="com.xinelu.manage.vo.receiveAddressInfo.ReceiveAddressInfoVO"
            resultType="com.xinelu.manage.vo.receiveAddressInfo.ReceiveAddressInfoVO">
        select id,
        patient_id,
        openid,
        unionid,
        receive_name,
        receive_phone,
        receive_address,
        area_code,
        create_by,
        create_time,
        update_by,
        update_time,
        default_address_flag
        from receive_address_info
        <where>
            <if test="patientId != null ">
                and patient_id = #{patientId}
            </if>
        </where>
        ORDER BY create_time DESC
    </select>

    <select id="selectReceiveAddressInfoByPatientId" parameterType="Long"
            resultType="ReceiveAddressInfo">
        select id,
        patient_id,
        openid,
        unionid,
        receive_name,
        receive_phone,
        receive_address,
        area_code,
        create_by,
        create_time,
        update_by,
        update_time
        from receive_address_info
        <where>
            <if test="patientId != null ">
                and patient_id = #{patientId}
            </if>
        </where>
    </select>


    <select id="selectReceiveAddressInfoById" parameterType="Long"
            resultType="ReceiveAddressInfo">
        select id,
               patient_id,
               openid,
               unionid,
               receive_name,
               receive_phone,
               receive_address,
               area_code,
               create_by,
               create_time,
               update_by,
               update_time
        from receive_address_info
        where id = #{id}
    </select>

    <insert id="insertReceiveAddressInfo" parameterType="ReceiveAddressInfo">
        insert into receive_address_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,
            </if>
            <if test="patientId != null">patient_id,
            </if>
            <if test="openid != null">openid,
            </if>
            <if test="unionid != null">unionid,
            </if>
            <if test="receiveName != null">receive_name,
            </if>
            <if test="receivePhone != null">receive_phone,
            </if>
            <if test="receiveAddress != null">receive_address,
            </if>
            <if test="areaCode != null">area_code,
            </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="id != null">#{id},
            </if>
            <if test="patientId != null">#{patientId},
            </if>
            <if test="openid != null">#{openid},
            </if>
            <if test="unionid != null">#{unionid},
            </if>
            <if test="receiveName != null">#{receiveName},
            </if>
            <if test="receivePhone != null">#{receivePhone},
            </if>
            <if test="receiveAddress != null">#{receiveAddress},
            </if>
            <if test="areaCode != null">#{areaCode},
            </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="updateReceiveAddressInfo" parameterType="ReceiveAddressInfo">
        update receive_address_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="patientId != null">patient_id =
                #{patientId},
            </if>
            <if test="openid != null">openid =
                #{openid},
            </if>
            <if test="unionid != null">unionid =
                #{unionid},
            </if>
            <if test="receiveName != null">receive_name =
                #{receiveName},
            </if>
            <if test="receivePhone != null">receive_phone =
                #{receivePhone},
            </if>
            <if test="receiveAddress != null">receive_address =
                #{receiveAddress},
            </if>
            <if test="areaCode != null">area_code =
                #{areaCode},
            </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>

    <delete id="deleteReceiveAddressInfoByIds" parameterType="String">
        delete from receive_address_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


    <select id="getSubordinateRegionsFindSuperiorRegions" parameterType="String"
            resultType="com.xinelu.manage.vo.sysarea.SysAreaVO">
        SELECT province.area_name province_name,
               province.area_code province_code,
               city.area_name     city_name,
               city.area_code     city_code,
               region.area_name   region_name,
               region.area_code   region_code,
               street.area_name   street_name,
               street.area_code   street_code
        FROM sys_area province,
             sys_area city,
             sys_area region,
             sys_area street
        WHERE city.parent_code = province.area_code
          AND region.parent_code = city.area_code
          AND street.parent_code = region.area_code
          AND street.area_code = #{areaCode}
        UNION ALL
        SELECT province.area_name province_name,
               province.area_code province_code,
               city.area_name     city_name,
               city.area_code     city_code,
               region.area_name   region_name,
               region.area_code   region_code,
               '' as              street_name,
               '' as              street_code
        FROM sys_area province,
             sys_area city,
             sys_area region
        WHERE city.parent_code = province.area_code
          AND region.parent_code = city.area_code
          AND region.area_code = #{areaCode} limit 1
    </select>

    <select id="selectGoodsOrderPatient" parameterType="com.xinelu.applet.dto.nursingstationgoods.GoodsOrderPatient"
            resultType="com.xinelu.applet.dto.nursingstationgoods.GoodsOrderPatient">
        SELECT
        gr.id goodsId,
        gr.nurse_station_id,
        gr.patient_id,
        gr.order_no,
        gr.order_status,
        gr.total_price,
        gr.receiver,
        gr.receive_address,
        gr.phone,
        gr.order_time,
        gr.order_channel,
        gr.buy_source,
        god.goods_order_id,
        god.goods_attribute_id,
        god.goods_attribute_details_id,
        god.order_no goodOrderNo,
        god.goods_name,
        god.goods_count,
        god.goods_attribute_name,
        god.goods_attribute_content,
        god.goods_price,
        god.total_price goodTotalPrice,
        god.discount_price,
        god.transport_price,
        god.give_integral,
        pi.id patientId,
        pi.community_code,
        pi.area_code,
        pi.patient_code,
        pi.patient_name,
        pi.card_no,
        pi.home_longitude,
        pi.home_latitude,
        pi.head_picture_url,
        pi.integral
        FROM goods_order gr
        LEFT JOIN goods_order_details god ON gr.id = god.goods_order_id
        LEFT JOIN patient_info pi ON pi.id = gr.patient_id
        <where>
            <if test="orderNo != null  and orderNo != ''">
                and gr.order_no = #{orderNo}
            </if>
        </where>
    </select>

    <select id="selectGoodsAttributeDetailsGoodsStockById" parameterType="Long"
            resultType="GoodsAttributeDetails">
        select id,
               goods_attribute_id,
               attribute_details_name,
               goods_stock,
               goods_price,
               attribute_piture_url,
               sort,
               create_by,
               create_time,
               update_by,
               update_time,
               integral_exchange_flag,
               integral_exchange_sill,
               integral_exchange_count
        from goods_attribute_details
        where id = #{id}
    </select>

    <update id="updateGoodsAttributeDetails" parameterType="GoodsAttributeDetails">
        update goods_attribute_details
        <trim prefix="SET" suffixOverrides=",">
            <if test="goodsAttributeId != null">goods_attribute_id =
                #{goodsAttributeId},
            </if>
            <if test="attributeDetailsName != null">attribute_details_name =
                #{attributeDetailsName},
            </if>
            <if test="goodsStock != null">goods_stock =
                #{goodsStock},
            </if>
            <if test="goodsPrice != null">goods_price =
                #{goodsPrice},
            </if>
            <if test="attributePitureUrl != null">attribute_piture_url =
                #{attributePitureUrl},
            </if>
            <if test="sort != null">sort =
                #{sort},
            </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>

    <select id="selectCouponListByPatient" resultType="com.xinelu.applet.vo.coupon.CouponVO">
        SELECT
        coupon_id couponId,
        coupon_title,
        coupon_price,
        coupon_consume_price,
        coupon_description,
        id patientCouponReceiveId,
        patient_id,
        receive_source,
        expiration_start_time,
        expiration_end_time,
        receive_time,
        use_status
        FROM
        patient_coupon_receive
        <where>
            <if test="patientId != null">
                patient_id = #{patientId}
            </if>
            <if test="useStatus != null and useStatus !=''">
                and use_status = #{useStatus}
            </if>
        </where>
        order by coupon_price asc, coupon_consume_price asc
    </select>
</mapper>
