<?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.manage.mapper.goodsInfo.GoodsInfoMapper">

    <resultMap type="GoodsInfo" id="GoodsInfoResult">
        <result property="id" column="id"/>
        <result property="storeInfoId" column="store_info_id"/>
        <result property="nurseStationId" column="nurse_station_id"/>
        <result property="goodsCategoryId" column="goods_category_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="goodsCode" column="goods_code"/>
        <result property="whetherShelf" column="whether_shelf"/>
        <result property="shelfTime" column="shelf_time"/>
        <result property="goodsPictureUrl" column="goods_picture_url"/>
        <result property="goodsRemark" column="goods_remark"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="goodsPurpose" column="goods_purpose"/>
        <result property="sort" column="sort"/>
        <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.manage.vo.goodsInfo.GetGoodDetails" id="GoodDetailsResult">
        <result property="goodsInfoId" column="goodsInfoId"/>
        <result property="goodsCategoryId" column="goods_category_id"/>
        <result property="goodsCategoryName" column="goods_category_name"/>
        <result property="nurseStationId" column="nurse_station_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"/>
        <result property="sort" column="sort"/>
        <collection property="goodAttributeDetailsLists" javaType="java.util.List" resultMap="goodDetailsListsResult"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.goodsInfo.GetGoodAttributeDetailsList" id="goodDetailsListsResult">
        <result property="attributeDetailsId" column="attributeDetailsId"/>
        <result property="goodsAttributeId" column="goodsAttributeId"/>
        <result property="attributeName" column="attribute_name"/>
        <result property="attributeDetailsName" column="attribute_details_name"/>
        <result property="attributePitureUrl" column="attribute_piture_url"/>
        <result property="goodsPrice" column="goods_price"/>
        <result property="goodsStock" column="goods_stock"/>
        <result property="integralExchangeFlag" column="integral_exchange_flag"/>
        <result property="integralExchangeSill" column="integral_exchange_sill"/>
        <result property="integralExchangeCount" column="integral_exchange_count"/>
        <result property="attributeDetailsSort" column="attributeDetailsSort"/>
    </resultMap>


    <sql id="selectGoodsInfoVo">
        select id,
               store_info_id,
               nurse_station_id,
               goods_category_id,
               goods_name,
               goods_code,
               whether_shelf,
               shelf_time,
               goods_picture_url,
               goods_remark,
               goods_unit,
               goods_purpose,
               sort,
               create_by,
               create_time,
               update_by,
               update_time
        from goods_info
    </sql>

    <select id="selectGoodsInfoList" parameterType="GoodsInfo" resultMap="GoodsInfoResult">
        <include refid="selectGoodsInfoVo"/>
        <where>
            <if test="storeInfoId != null ">
                and store_info_id = #{storeInfoId}
            </if>
            <if test="nurseStationId != null ">
                and nurse_station_id = #{nurseStationId}
            </if>
            <if test="goodsCategoryId != null ">
                and goods_category_id = #{goodsCategoryId}
            </if>
            <if test="goodsName != null  and goodsName != ''">
                and goods_name like concat('%', #{goodsName}, '%')
            </if>
            <if test="goodsCode != null  and goodsCode != ''">
                and goods_code = #{goodsCode}
            </if>
            <if test="whetherShelf != null ">
                and whether_shelf = #{whetherShelf}
            </if>
            <if test="shelfTime != null ">
                and shelf_time = #{shelfTime}
            </if>
            <if test="goodsPictureUrl != null  and goodsPictureUrl != ''">
                and goods_picture_url = #{goodsPictureUrl}
            </if>
            <if test="goodsRemark != null  and goodsRemark != ''">
                and goods_remark = #{goodsRemark}
            </if>
            <if test="goodsUnit != null  and goodsUnit != ''">
                and goods_unit = #{goodsUnit}
            </if>
            <if test="goodsPurpose != null  and goodsPurpose != ''">
                and goods_purpose = #{goodsPurpose}
            </if>
            <if test="sort != null ">
                and sort = #{sort}
            </if>
        </where>
        ORDER BY create_time
    </select>

    <select id="selectGoodsInfoById" parameterType="Long"
            resultMap="GoodsInfoResult">
        <include refid="selectGoodsInfoVo"/>
        where id = #{id}
    </select>

    <insert id="insertGoodsInfo" parameterType="GoodsInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into goods_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="storeInfoId != null">store_info_id,
            </if>
            <if test="nurseStationId != null">nurse_station_id,
            </if>
            <if test="goodsCategoryId != null">goods_category_id,
            </if>
            <if test="goodsName != null">goods_name,
            </if>
            <if test="goodsCode != null">goods_code,
            </if>
            <if test="whetherShelf != null">whether_shelf,
            </if>
            <if test="shelfTime != null">shelf_time,
            </if>
            <if test="goodsPictureUrl != null">goods_picture_url,
            </if>
            <if test="goodsRemark != null">goods_remark,
            </if>
            <if test="goodsUnit != null">goods_unit,
            </if>
            <if test="goodsPurpose != null">goods_purpose,
            </if>
            <if test="sort != null">sort,
            </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="storeInfoId != null">#{storeInfoId},
            </if>
            <if test="nurseStationId != null">#{nurseStationId},
            </if>
            <if test="goodsCategoryId != null">#{goodsCategoryId},
            </if>
            <if test="goodsName != null">#{goodsName},
            </if>
            <if test="goodsCode != null">#{goodsCode},
            </if>
            <if test="whetherShelf != null">#{whetherShelf},
            </if>
            <if test="shelfTime != null">#{shelfTime},
            </if>
            <if test="goodsPictureUrl != null">#{goodsPictureUrl},
            </if>
            <if test="goodsRemark != null">#{goodsRemark},
            </if>
            <if test="goodsUnit != null">#{goodsUnit},
            </if>
            <if test="goodsPurpose != null">#{goodsPurpose},
            </if>
            <if test="sort != null">#{sort},
            </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="updateGoodsInfo" parameterType="GoodsInfo">
        update goods_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="storeInfoId != null">store_info_id =
                #{storeInfoId},
            </if>
            <if test="nurseStationId != null">nurse_station_id =
                #{nurseStationId},
            </if>
            <if test="goodsCategoryId != null">goods_category_id =
                #{goodsCategoryId},
            </if>
            <if test="goodsName != null">goods_name =
                #{goodsName},
            </if>
            <if test="goodsCode != null">goods_code =
                #{goodsCode},
            </if>
            <if test="whetherShelf != null">whether_shelf =
                #{whetherShelf},
            </if>
            <if test="shelfTime != null">shelf_time =
                #{shelfTime},
            </if>
            <if test="goodsPictureUrl != null">goods_picture_url =
                #{goodsPictureUrl},
            </if>
            <if test="goodsRemark != null">goods_remark =
                #{goodsRemark},
            </if>
            <if test="goodsUnit != null">goods_unit =
                #{goodsUnit},
            </if>
            <if test="goodsPurpose != null">goods_purpose =
                #{goodsPurpose},
            </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>

    <delete id="deleteGoodsInfoById" parameterType="Long">
        delete
        from goods_info
        where id = #{id}
    </delete>

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


    <select id="getGoodsInfoList" parameterType="com.xinelu.manage.vo.goodsInfo.GoodsInfoVO"
            resultType="com.xinelu.manage.vo.goodsInfo.GoodsInfoVO">
        SELECT
        gi.id goodsInfoId,
        gi.goods_category_id,
        gi.goods_code,
        gi.goods_name,
        gc.goods_category_name,
        ns.nurse_station_name,
        gi.goods_picture_url,
        gi.goods_purpose,
        gi.whether_shelf,
        gi.shelf_time,
        gi.create_by,
        gi.create_time,
        gi.update_by,
        gi.update_time,
        ns.nurse_station_name
        FROM
        goods_info gi
        LEFT JOIN goods_category gc ON gi.goods_category_id = gc.id
        LEFT JOIN nurse_station ns ON gi.nurse_station_id = ns.id
        <where>
            <if test="goodsName != null  and goodsName != ''">
                and gi.goods_name like concat('%', #{goodsName}, '%')
            </if>
            <if test="whetherShelf != null ">
                and gi.whether_shelf = #{whetherShelf}
            </if>
            <if test="goodsPurpose != null  and goodsPurpose != ''">
                and gi.goods_purpose = #{goodsPurpose}
            </if>
            <if test="goodsCategoryName != null  and goodsCategoryName != ''">
                and gc.goods_category_name like concat('%', #{goodsCategoryName}, '%')
            </if>
            <if test="nurseStationName != null  and nurseStationName != ''">
                and ns.nurse_station_name like concat('%', #{nurseStationName}, '%')
            </if>
            <if test="nurseStationId != null ">
                and ns.id = #{nurseStationId}
            </if>
        </where>
        ORDER BY gi.create_time DESC
    </select>

    <select id="getGoodsDetailsList" parameterType="com.xinelu.manage.vo.goodsInfo.GetGoodDetails"
            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,
        gi.sort,
        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,
        gc.goods_category_name,
        gad.integral_exchange_flag,
        gad.integral_exchange_sill,
        gad.integral_exchange_count
        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
        LEFT JOIN goods_category gc ON gi.goods_category_id = gc.id
        <where>
            <if test="goodsInfoId != null ">
                and gi.id = #{goodsInfoId}
            </if>
        </where>
    </select>


    <update id="updateGoodsWhetherShelf" parameterType="GoodsInfo">
        update goods_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="storeInfoId != null">store_info_id =
                #{storeInfoId},
            </if>
            <if test="nurseStationId != null">nurse_station_id =
                #{nurseStationId},
            </if>
            <if test="goodsCategoryId != null">goods_category_id =
                #{goodsCategoryId},
            </if>
            <if test="goodsName != null">goods_name =
                #{goodsName},
            </if>
            <if test="goodsCode != null">goods_code =
                #{goodsCode},
            </if>
            <if test="whetherShelf != null">whether_shelf =
                #{whetherShelf},
            </if>
            <if test="shelfTime != null">shelf_time =
                #{shelfTime},
            </if>
            <if test="goodsPictureUrl != null">goods_picture_url =
                #{goodsPictureUrl},
            </if>
            <if test="goodsRemark != null">goods_remark =
                #{goodsRemark},
            </if>
            <if test="goodsUnit != null">goods_unit =
                #{goodsUnit},
            </if>
            <if test="goodsPurpose != null">goods_purpose =
                #{goodsPurpose},
            </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="getStoreInfoById" parameterType="Long"
            resultType="StoreInfo">
        select id,
               nurse_station_id,
               store_name,
               store_code,
               store_address,
               phone,
               store_logo_url,
               store_operator,
               opening_status,
               opening_time,
               occupation_start_time,
               occupation_end_time,
               sort,
               create_by,
               create_time,
               update_by,
               update_time
        from store_info
        where nurse_station_id = #{nurseStationId}
    </select>

    <insert id="insertStoreInfo" parameterType="StoreInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into store_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nurseStationId != null">nurse_station_id,
            </if>
            <if test="storeName != null">store_name,
            </if>
            <if test="storeCode != null">store_code,
            </if>
            <if test="storeAddress != null">store_address,
            </if>
            <if test="phone != null">phone,
            </if>
            <if test="storeLogoUrl != null">store_logo_url,
            </if>
            <if test="storeOperator != null">store_operator,
            </if>
            <if test="openingStatus != null">opening_status,
            </if>
            <if test="openingTime != null">opening_time,
            </if>
            <if test="occupationStartTime != null">occupation_start_time,
            </if>
            <if test="occupationEndTime != null">occupation_end_time,
            </if>
            <if test="sort != null">sort,
            </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="nurseStationId != null">#{nurseStationId},
            </if>
            <if test="storeName != null">#{storeName},
            </if>
            <if test="storeCode != null">#{storeCode},
            </if>
            <if test="storeAddress != null">#{storeAddress},
            </if>
            <if test="phone != null">#{phone},
            </if>
            <if test="storeLogoUrl != null">#{storeLogoUrl},
            </if>
            <if test="storeOperator != null">#{storeOperator},
            </if>
            <if test="openingStatus != null">#{openingStatus},
            </if>
            <if test="openingTime != null">#{openingTime},
            </if>
            <if test="occupationStartTime != null">#{occupationStartTime},
            </if>
            <if test="occupationEndTime != null">#{occupationEndTime},
            </if>
            <if test="sort != null">#{sort},
            </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>

    <select id="getNurseStationById" parameterType="Long"
            resultType="NurseStation">
        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,
               station_introduce_picture_url,
               sort,
               create_by,
               create_time,
               update_by,
               update_time
        from nurse_station
        where id = #{id}
    </select>


    <select id="getGoodsByIdList" parameterType="com.xinelu.manage.dto.goodsInfo.GoodsAttributeDTO"
            resultType="com.xinelu.manage.dto.goodsInfo.GoodsAttributeDTO">
        SELECT
        gi.id goodsId,
        ga.id goodsAttributeId,
        gad.id goodAttributeDetailsId,
        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="id != null ">
                and gi.id = #{id}
            </if>
        </where>
    </select>


    <select id="getGoodsByIdLists" parameterType="com.xinelu.manage.dto.goodsInfo.GoodsAttributeDTO"
            resultType="com.xinelu.manage.dto.goodsInfo.GoodsAttributeDTO">
        SELECT
        gi.id,
        ga.id goodsAttributeId,
        gad.id goodAttributeDetailsId,
        gi.goods_remark,
        gi.goods_picture_url,
        gad.attribute_piture_url
        FROM
        goods_info gi
        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="goodsInfoIdList != null  and goodsInfoIdList.size()>0 ">
                and gi.id in
                <foreach item="goodsInfoId" collection="goodsInfoIdList" open="(" separator="," close=")">
                    #{goodsInfoId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getStoreInfoByName" parameterType="string" resultType="StoreInfo">
        select id, store_code, store_name
        from store_info
        where store_name = #{storeName}
    </select>
</mapper>
