<?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.mmc.pms.dao.WebProductMallDao">
    <resultMap id="resultMapGoodsInfo" type="com.mmc.pms.entity.GoodsInfoDO">
        <id property="id" column="id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="createTime" column="create_time"/>
        <result property="shelfStatus" column="shelf_status"/>
        <result property="masterTypeId" column="master_type_id"/>
        <result property="slaveTypeId" column="slave_type_id"/>
        <result property="sortTypeId" column="sort_type_id"/>
        <result property="mainImg" column="img_url"/>
        <result property="pid" column="pid"/>
        <result property="goodsNo" column="goods_no"/>
        <result property="sort" column="sort"/>
        <result property="directoryName" column="sort_name"/>
        <association property="goodsMasterType" resultMap="resultMasterType"/>
        <association property="goodsSlaveType" resultMap="resultSlaveType"/>
    </resultMap>

    <select id="productCategory" resultType="com.mmc.pms.entity.ProductCategory">
        select id, `name`
        from product_category;
    </select>
    <select id="productParts" resultType="com.mmc.pms.entity.ProductCategory">
        select id, `name`
        from product_parts;
    </select>
    <select id="productQuality" resultType="com.mmc.pms.entity.ProductCategory">
        select id, `name`
        from product_quality;
    </select>

    <select id="countListGoodsInfo" resultType="java.lang.Integer">
        SELECT
        count(*)
        FROM
        goods_info gi
        LEFT JOIN goods_img img ON gi.id = img.goods_info_id
        AND img.img_type = 0
        AND img.is_deleted = 0
        LEFT JOIN goods_type gt ON gt.id = gi.master_type_id
        LEFT JOIN goods_type gty ON gty.id = gi.slave_type_id
        <where>
            gi.is_deleted = 0
            <if test="districtId != null">
                and (gi.district_id = #{districtId})
            </if>
            <if test="brandId != null">
                and (gi.brand_id = #{brandId})
            </if>
            <if test="partsId != null">
                and (gi.parts_id = #{partsId})
            </if>
            <if test="modelId != null">
                and (gi.mode_id = #{modelId})
            </if>
            <if test="qualityId != null">
                and (gi.quality_id = #{qualityId})
            </if>
            <if test="productCategoryId != null">
                and (gi.sort_type_id = #{productCategoryId})
            </if>
        </where>
    </select>

    <select id="listGoodsInfo" resultMap="resultMapGoodsInfo">
        SELECT
        gi.id,
        gi.goods_name,
        gi.shelf_status,
        gi.create_time,
        gi.sort_type_id,
        gi.district_id,
        gi.brand_id,
        gi.parts_id,
        gi.mode_id,
        gi.quality_id,
        img.img_url,
        gt.type_name AS master_type_name,
        gty.type_name AS slave_type_name,
        st.sort_name
        FROM
        goods_info gi
        LEFT JOIN goods_img img ON gi.id = img.goods_info_id
        AND img.img_type = 0
        AND img.is_deleted = 0
        LEFT JOIN goods_type gt ON gt.id = gi.master_type_id
        LEFT JOIN goods_type gty ON gty.id = gi.slave_type_id
        LEFT JOIN sort_type st ON st.id = gi.sort_type_id
        <where>
            gi.is_deleted = 0
            <if test="districtId != null">
                and (gi.district_id = #{districtId})
            </if>
            <if test="brandId != null">
                and (gi.brand_id = #{brandId})
            </if>
            <if test="partsId != null">
                and (gi.parts_id = #{partsId})
            </if>
            <if test="modelId != null">
                and (gi.mode_id = #{modelId})
            </if>
            <if test="qualityId != null">
                and (gi.quality_id = #{qualityId})
            </if>
            <if test="productCategoryId != null">
                and (gi.sort_type_id = #{productCategoryId})
            </if>
        </where>
        ORDER BY
        gi.shelf_status DESC , gi.create_time DESC
        limit #{pageNo},#{pageSize}
    </select>

    <resultMap id="resultMasterType" type="com.mmc.pms.entity.GoodsTypeDO">
        <id property="id" column="id"/>
        <result property="typeName" column="master_type_name"/>
        <result property="icon" column="icon"/>
        <result property="pid" column="pid"/>
        <result property="sort" column="sort"/>
        <result property="remark" column="remark"/>
        <result property="createTime" column="create_time"/>
        <result property="description" column="description"/>
    </resultMap>
    <resultMap id="resultSlaveType" type="com.mmc.pms.entity.GoodsTypeDO">
        <id property="id" column="id"/>
        <result property="typeName" column="slave_type_name"/>
        <result property="icon" column="icon"/>
        <result property="pid" column="pid"/>
        <result property="sort" column="sort"/>
        <result property="remark" column="remark"/>
        <result property="createTime" column="create_time"/>
        <result property="description" column="description"/>
    </resultMap>

    <select id="listGoodsTypeByTwoLevel" resultMap="resultSlaveType">
        SELECT gty.type_name as slave_type_name,
               gty.id,
               gty.pid,
               gty.description
        FROM goods_type gt
                 INNER JOIN goods_type gty ON gt.id = gty.pid
        WHERE gty.is_deleted = 0
    </select>
    <select id="listGoodsTypeByTwoLevelList" resultMap="resultSlaveType">
        SELECT gty.type_name AS slave_type_name,
               gty.id,
               gty.pid,
               gty.description,
               gty.remark,
               gty.sort,
               gty.create_time
        FROM goods_type gt
                 INNER JOIN goods_type gty ON gt.id = gty.pid
        WHERE gty.is_deleted = 0
          AND gty.sort_type_id = #{type}
    </select>

    <select id="findProduct" resultType="java.lang.Integer">
        SELECT count(1)
        FROM goods_info gi
                 INNER JOIN mall_prod_sku_info mpsi ON mpsi.goods_info_id = gi.id
                 INNER JOIN product_sku ps ON mpsi.prod_sku_id = ps.id
            AND ps.is_deleted = 0
                 INNER JOIN brand_info bi ON ps.brand_info_id = bi.id
            AND bi.is_delete = 0
        WHERE gi.is_deleted = 0
          AND gi.id = #{id}
    </select>
    <select id="ad" resultType="com.mmc.pms.entity.AdDO">
        SELECT
            id,
            image_url AS imageUrl
        FROM
            product_ad
    </select>

</mapper>
