<?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.younike.system.mapper.GoodsMapper">


    <resultMap type="com.younike.system.domain.Goods" id="GoodsResult">
        <result property="goodsId" column="goods_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="cateId" column="cate_id"/>
        <result property="name" column="name"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="otherUnit" column="other_unit"/>
        <result property="singleTime" column="single_time"/>
        <result property="serviceDesc" column="service_desc"/>
        <result property="priceDesc" column="price_desc"/>
        <result property="coin" column="coin"/>
        <result property="score" column="score"/>
        <result property="sales" column="sales"/>
        <result property="maxNum" column="max_num"/>
        <result property="isSale" column="is_sale"/>
        <result property="unshelveTime" column="unshelve_time"/>
        <result property="unshelveReason" column="unshelve_reason"/>
        <result property="sorting" column="sorting"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip"/>
        <result property="loginDate" column="login_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="column1" column="column1"/>
        <result property="column2" column="column2"/>
        <result property="column3" column="column3"/>
    </resultMap>

    <resultMap type="com.younike.system.domain.vo.GoodsVo" id="GoodsVoResult">
        <result property="goodsId" column="goods_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="cateId" column="cate_id"/>
        <result property="name" column="name"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="otherUnit" column="other_unit"/>
        <result property="singleTime" column="single_time"/>
        <result property="serviceDesc" column="service_desc"/>
        <result property="priceDesc" column="price_desc"/>
        <result property="coin" column="coin"/>
        <result property="score" column="score"/>
        <result property="sales" column="sales"/>
        <result property="maxNum" column="max_num"/>
        <result property="isSale" column="is_sale"/>
        <result property="unshelveTime" column="unshelve_time"/>
        <result property="unshelveReason" column="unshelve_reason"/>
        <result property="sorting" column="sorting"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip"/>
        <result property="loginDate" column="login_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="column1" column="column1"/>
        <result property="column2" column="column2"/>
        <result property="column3" column="column3"/>
        <result property="monad" column="Decode_Value"/>
        <result property="rate" column="rate"/>
        <result property="merchantType" column="merchant_type"/>
        <result property="storeAge" column="store_age"/>
        <result property="merchantName" column="merchant_name"/>
        <result property="serverAge" column="server_age"/>
        <result property="serverLevel" column="server_level"/>
        <result property="realName" column="real_name"/>
        <result property="orderCount" column="order_count"/>
        <result property="businessState" column="business_state"/>
        <result property="desc" column="desc"/>
        <result property="serverScore" column="serverScore"/>
        <result property="goodsServerPrice" column="goods_server_price"/>
        <result property="phone" column="phone"/>
        <result property="serverId" column="server_id"/>

        <collection property="goodsImageList" select="goodsImageGoodsId" column="goods_id"
                    ofType="com.younike.system.domain.GoodsImage">
        </collection>
        <collection property="merchant" resultMap="com.younike.system.mapper.MerchantMapper.MerchantResult"
                    notNullColumn="merchant_id"/>
        <collection property="goodsServerDimensionList" select="goodsServerDimensionGoodsId" column="goods_id"
                    ofType="com.younike.system.domain.vo.GoodsServerDimensionVo">
        </collection>

    </resultMap>

    <resultMap type="com.younike.system.domain.vo.GoodsVo" id="MerchantGoodsResult">
        <result property="goodsId" column="goods_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="cateId" column="cate_id"/>
        <result property="name" column="name"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="otherUnit" column="other_unit"/>
        <result property="singleTime" column="single_time"/>
        <result property="serviceDesc" column="service_desc"/>
        <result property="priceDesc" column="price_desc"/>
        <result property="coin" column="coin"/>
        <result property="score" column="score"/>
        <result property="sales" column="sales"/>
        <result property="maxNum" column="max_num"/>
        <result property="isSale" column="is_sale"/>
        <result property="unshelveTime" column="unshelve_time"/>
        <result property="unshelveReason" column="unshelve_reason"/>
        <result property="sorting" column="sorting"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip"/>
        <result property="loginDate" column="login_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="column1" column="column1"/>
        <result property="column2" column="column2"/>
        <result property="column3" column="column3"/>
        <collection property="goodsImageList" select="goodsImageGoodsId" column="goods_id"
                    ofType="com.younike.system.domain.GoodsImage">
        </collection>


        <collection property="merchant" resultMap="com.younike.system.mapper.MerchantMapper.MerchantResult"
                    column="merchant_id"/>

    </resultMap>

    <resultMap type="com.younike.system.domain.vo.GoodsVo" id="GoodsListResult">
        <result property="goodsId" column="goods_id"/>
        <result property="merchantId" column="merchant_id"/>
        <result property="cateId" column="cate_id"/>
        <result property="name" column="name"/>
        <result property="goodsUnit" column="goods_unit"/>
        <result property="otherUnit" column="other_unit"/>
        <result property="singleTime" column="single_time"/>
        <result property="serviceDesc" column="service_desc"/>
        <result property="priceDesc" column="price_desc"/>
        <result property="coin" column="coin"/>
        <result property="score" column="score"/>
        <result property="sales" column="sales"/>
        <result property="maxNum" column="max_num"/>
        <result property="isSale" column="is_sale"/>
        <result property="unshelveTime" column="unshelve_time"/>
        <result property="unshelveReason" column="unshelve_reason"/>
        <result property="sorting" column="sorting"/>
        <result property="delFlag" column="del_flag"/>
        <result property="loginIp" column="login_ip"/>
        <result property="loginDate" column="login_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="merchantName" column="merchant_name"/>
        <result property="serverUserCount" column="serverUserCount"/>
        <result property="categoryName" column="category_name"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="column1" column="column1"/>
        <result property="column2" column="column2"/>
        <result property="column3" column="column3"/>
    </resultMap>

    <sql id="selectGoodsVo">
        select goods_id, merchant_id, cate_id, name, goods_unit, other_unit, single_time, service_desc, price_desc, coin, score, sales, max_num, is_sale, unshelve_time, unshelve_reason, sorting, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, column1, column2, column3 from goods
    </sql>

    <select id="pageList" resultMap="GoodsListResult">
        select g.*,m.merchant_name, c.category_name, count(su.server_id) as serverUserCount, u.phonenumber
        from goods g
        left join goods_server gs on g.goods_id = gs.goods_id and gs.del_flag = 0
        left join category c on g.cate_id = c.category_id
        left join server_user su on gs.server_id = su.server_id and su.del_flag = 0
        left join merchant m on g.merchant_id = m.merchant_id and m.del_flag = 0
        left join sys_user u on m.admin_id = u.user_id and u.del_flag = 0
        where g.del_flag = 0 and m.del_flag = 0
        <if test="categoryId != null and categoryId != ''">and g.cate_id = #{categoryId}</if>
        <if test="merchantType != null and merchantType != ''">and m.merchant_type = #{merchantType}</if>
        <if test="name != null  and name != ''">and g.name like concat('%', #{name}, '%')</if>
        <if test="merchantName != null  and merchantName != ''">and m.merchant_name like concat('%', #{merchantName}, '%')</if>
        <if test="merchantId != null and merchantId != ''">and g.merchant_id = #{merchantId}</if>
        <if test="isSale != null ">and g.is_sale = #{isSale}</if>
        <if test="phonenumber != null and phonenumber != ''">and u.phonenumber = #{phonenumber}</if>
        <if test="beginTime != null and beginTime != ''">
            and date_format(g.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
        </if>
        <if test="endTime != null and endTime != ''">
            and date_format(g.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
        </if>
        group by g.goods_id
    </select>

    <select id="selectGoodsList" parameterType="com.younike.system.domain.Goods" resultMap="GoodsResult">
        <include refid="selectGoodsVo"/>
        <where>
            del_flag = 0
            <if test="merchantId != null "> and merchant_id = #{merchantId}</if>
            <if test="cateId != null "> and cate_id = #{cateId}</if>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="goodsUnit != null  and goodsUnit != ''"> and goods_unit = #{goodsUnit}</if>
            <if test="otherUnit != null  and otherUnit != ''"> and other_unit = #{otherUnit}</if>
            <if test="singleTime != null "> and single_time = #{singleTime}</if>
            <if test="serviceDesc != null  and serviceDesc != ''"> and service_desc = #{serviceDesc}</if>
            <if test="priceDesc != null  and priceDesc != ''"> and price_desc = #{priceDesc}</if>
            <if test="coin != null "> and coin = #{coin}</if>
            <if test="score != null "> and score = #{score}</if>
            <if test="sales != null "> and sales = #{sales}</if>
            <if test="maxNum != null "> and max_num = #{maxNum}</if>
            <if test="isSale != null "> and is_sale = #{isSale}</if>
            <if test="unshelveTime != null "> and unshelve_time = #{unshelveTime}</if>
            <if test="unshelveReason != null  and unshelveReason != ''"> and unshelve_reason = #{unshelveReason}</if>
            <if test="sorting != null "> and sorting = #{sorting}</if>
            <if test="loginIp != null  and loginIp != ''"> and login_ip = #{loginIp}</if>
            <if test="loginDate != null "> and login_date = #{loginDate}</if>
            <if test="column1 != null  and column1 != ''"> and column1 = #{column1}</if>
            <if test="column2 != null  and column2 != ''"> and column2 = #{column2}</if>
            <if test="column3 != null  and column3 != ''"> and column3 = #{column3}</if>
        </where>
    </select>

    <select id="selectGoodsByGoodsId" parameterType="Long" resultMap="GoodsResult">
        <include refid="selectGoodsVo"/>
        where goods_id = #{goodsId}
        and del_flag=0
    </select>

    <insert id="insertGoods" parameterType="com.younike.system.domain.Goods" useGeneratedKeys="true" keyProperty="goodsId">
        insert into goods
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="goodsId != null">goods_id,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="cateId != null">cate_id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="goodsUnit != null">goods_unit,</if>
            <if test="otherUnit != null">other_unit,</if>
            <if test="singleTime != null">single_time,</if>
            <if test="serviceDesc != null">service_desc,</if>
            <if test="priceDesc != null">price_desc,</if>
            <if test="coin != null">coin,</if>
            <if test="score != null">score,</if>
            <if test="sales != null">sales,</if>
            <if test="maxNum != null">max_num,</if>
            <if test="isSale != null">is_sale,</if>
            <if test="unshelveTime != null">unshelve_time,</if>
            <if test="unshelveReason != null">unshelve_reason,</if>
            <if test="sorting != null">sorting,</if>
            <if test="delFlag != null and delFlag != ''">del_flag,</if>
            <if test="loginIp != null">login_ip,</if>
            <if test="loginDate != null">login_date,</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>
            <if test="column1 != null">column1,</if>
            <if test="column2 != null">column2,</if>
            <if test="column3 != null">column3,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="goodsId != null">#{goodsId},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="cateId != null">#{cateId},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="goodsUnit != null">#{goodsUnit},</if>
            <if test="otherUnit != null">#{otherUnit},</if>
            <if test="singleTime != null">#{singleTime},</if>
            <if test="serviceDesc != null">#{serviceDesc},</if>
            <if test="priceDesc != null">#{priceDesc},</if>
            <if test="coin != null">#{coin},</if>
            <if test="score != null">#{score},</if>
            <if test="sales != null">#{sales},</if>
            <if test="maxNum != null">#{maxNum},</if>
            <if test="isSale != null">#{isSale},</if>
            <if test="unshelveTime != null">#{unshelveTime},</if>
            <if test="unshelveReason != null">#{unshelveReason},</if>
            <if test="sorting != null">#{sorting},</if>
            <if test="delFlag != null and delFlag != ''">#{delFlag},</if>
            <if test="loginIp != null">#{loginIp},</if>
            <if test="loginDate != null">#{loginDate},</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>
            <if test="column1 != null">#{column1},</if>
            <if test="column2 != null">#{column2},</if>
            <if test="column3 != null">#{column3},</if>
        </trim>
    </insert>

    <update id="updateGoods" parameterType="com.younike.system.domain.Goods">
        update goods
        <trim prefix="SET" suffixOverrides=",">
            <if test="merchantId != null">merchant_id = #{merchantId},</if>
            <if test="cateId != null">cate_id = #{cateId},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="goodsUnit != null">goods_unit = #{goodsUnit},</if>
            <if test="otherUnit != null">other_unit = #{otherUnit},</if>
            <if test="singleTime != null">single_time = #{singleTime},</if>
            <if test="serviceDesc != null">service_desc = #{serviceDesc},</if>
            <if test="priceDesc != null">price_desc = #{priceDesc},</if>
            <if test="coin != null">coin = #{coin},</if>
            <if test="score != null">score = #{score},</if>
            <if test="sales != null">sales = #{sales},</if>
            <if test="maxNum != null">max_num = #{maxNum},</if>
            <if test="isSale != null">is_sale = #{isSale},</if>
            <if test="unshelveTime != null">unshelve_time = #{unshelveTime},</if>
            <if test="unshelveReason != null">unshelve_reason = #{unshelveReason},</if>
            <if test="sorting != null">sorting = #{sorting},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="loginIp != null">login_ip = #{loginIp},</if>
            <if test="loginDate != null">login_date = #{loginDate},</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>
            <if test="column1 != null">column1 = #{column1},</if>
            <if test="column2 != null">column2 = #{column2},</if>
            <if test="column3 != null">column3 = #{column3},</if>
        </trim>
        where goods_id = #{goodsId}
    </update>

    <delete id="deleteGoodsByGoodsId" parameterType="Long">
        delete from goods where goods_id = #{goodsId}
    </delete>

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

    <!--    查询服务人员产品列表-->
    <select id="seekCrewList" resultMap="GoodsVoResult" resultType="com.younike.system.param.GoodsParam">
        select a.*,
        d.merchant_type,
        d.merchant_name,
        d.business_state,
        e.Decode_Value ,
        g.server_id,
        g.server_age,
        g.rate,
        g.server_level,
        g.real_name,
        f.goods_server_price,
        h.order_count
        from goods a
        LEFT JOIN category b on b.category_id=a.cate_id and b.del_flag=0
        LEFT JOIN merchant d on d.merchant_id=a.merchant_id
        LEFT JOIN sys_code e on a.goods_unit=e.Decode_Id and e.Code_Id=011
        LEFT JOIN goods_server f on f.goods_id=a.goods_id
        LEFT JOIN server_user g on g.server_id=f.server_id
        LEFT JOIN (
        select aa.goods_id,
        COUNT(DISTINCT bb.order_id) as order_count
        from goods aa
        LEFT JOIN order_goods bb on bb.goods_id =aa.goods_id
        ) h on h.goods_id =a.goods_id
        where a.del_flag=0
        and a.is_sale=1
        and d.del_flag=0
        and f.del_flag=0
        and g.del_flag=0
        and b.biz_type=#{goods.bizType}
        <if test="goods.cateId != null and goods.cateId != ''">
            AND b.category_id =#{goods.cateId}
        </if>
        <if test="goods.parentId != null and goods.parentId != ''">
            AND b.parent_id =#{goods.parentId}
        </if>
        <if test="goods.name != null and goods.name != ''">
            AND a.name LIKE CONCAT('%',#{goods.name},'%')
        </if>
        <if test="goods.merchantId != null and goods.merchantId != ''">
            AND a.merchant_id =#{goods.merchantId}
        </if>
        ORDER BY g.rate,h.order_count DESC
    </select>
    <!--    查询商家产品列表-->
    <select id="seekMerchantList" resultMap="GoodsVoResult" resultType="com.younike.system.param.GoodsParam">
        select a.*,
        d.merchant_type,
        d.merchant_name,
        d.store_age,
        d.rate,
        h.order_count,
        e.Decode_Value
        from goods a
        LEFT JOIN category b on b.category_id=a.cate_id and b.del_flag=0
        LEFT JOIN merchant d on d.merchant_id=a.merchant_id
        LEFT JOIN sys_code e on a.goods_unit=e.Decode_Id and e.Code_Id=011
        LEFT JOIN (
        select aa.goods_id,
        COUNT(DISTINCT bb.order_id) as order_count
        from goods aa
        LEFT JOIN order_goods bb on bb.goods_id =aa.goods_id
        ) h on h.goods_id =a.goods_id
        where a.del_flag=0
        and a.is_sale=1
        and d.business_state=001
        and d.del_flag=0
        and b.biz_type=#{goods.bizType}
        <if test="goods.cateId != null and goods.cateId != ''">
            AND b.category_id = #{goods.cateId}
        </if>
        <if test="goods.parentId != null and goods.parentId != ''">
            AND b.parent_id = #{goods.parentId}
        </if>
        <if test="goods.name != null and goods.name != ''">
            AND a.name LIKE CONCAT('%',#{goods.name},'%')
        </if>
        ORDER BY d.rate ,h.order_count DESC
    </select>

    <!--    首页：商家主页-->
    <select id="merchantGoods" resultMap="MerchantGoodsResult" resultType="com.younike.system.param.GoodsParam">
    select a.*,d.*
        from goods a
        LEFT JOIN category b on b.category_id=a.cate_id and b.del_flag=0
        LEFT JOIN merchant d on d.merchant_id=a.merchant_id
        where a.del_flag=0
        and a.is_sale=1
        and d.business_state=001
        and d.del_flag=0
        and b.biz_type=#{goods.bizType}
        and a.goods_id=#{goods.goodsId}

    </select>

    <!--    首页:服务人员主页-->
    <select id="serverGoods" resultMap="GoodsVoResult" resultType="com.younike.system.param.GoodsParam">
        select a.*,
        d.*,
        e.Decode_Value ,
        g.server_age,
        g.rate,
        g.server_level,
        g.real_name,
        g.order_count,
        g.desc,
        g.phone,
        g.server_id,
        g.score as serverScore,
        f.goods_server_price,
        f.server_id
        from goods a
        LEFT JOIN category b on b.category_id=a.cate_id and b.del_flag=0
        LEFT JOIN merchant d on d.merchant_id=a.merchant_id and d.del_flag=0
        LEFT JOIN sys_code e on a.goods_unit=e.Decode_Id and e.Code_Id=011
        LEFT JOIN goods_server f on f.goods_id=a.goods_id
        LEFT JOIN server_user g on g.server_id=f.server_id
        where a.del_flag=0
        and a.is_sale=1
        and f.del_flag=0
        and g.del_flag=0
        and b.biz_type=#{goods.bizType}
        and a.goods_id =#{goods.goodsId}
        and g.server_id=#{goods.serverId}

    </select>

    <select id="goodsImageGoodsId" resultMap="com.younike.system.mapper.GoodsImageMapper.GoodsImageResult">
        select * from  goods_image where del_flag=0
        and goods_id=#{goods_id}
    </select>

    <select id="goodsServerDimensionGoodsId" resultMap="com.younike.system.mapper.GoodsServerDimensionMapper.GoodsServerDimensionVoResult">
        select h.* ,  i.name as dimensionName from   goods_server_dimension h
         LEFT JOIN dimension i on h.dimension_id=i.dimension_id and i.del_flag=0
         where
         h.goods_id =#{goods_id=}
         and h.is_del=0

    </select>
</mapper>
