<?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.MiniProgramProductMallDao">
    <resultMap id="resultGoodsInfo" type="com.mmc.pms.entity.GoodsInfoDO">
        <id property="id" column="id"/>
        <result property="pid" column="pid"/>
        <result property="goodsCategoryId" column="goods_category_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="createTime" column="create_time"/>
        <result property="masterTypeId" column="master_type_id"/>
        <result property="slaveTypeId" column="slave_type_id"/>
        <result property="shelfStatus" column="shelf_status"/>
        <result property="sortTypeId" column="sort_type_id"/>
        <result property="remark" column="remark"/>
        <result property="shareFlyServiceId" column="share_fly_service_id"/>
        <result property="repoId" column="repo_id"/>
        <result property="ecoLabel" column="eco_label"/>
        <result property="goodsAttr" column="goods_attr"/>
        <result property="showCode" column="is_show_code"/>
        <result property="sort" column="sort"/>
        <result property="tag" column="tag"/>
        <result property="goodsType" column="goods_type"/>
        <result property="standardProduct" column="is_standard_product"/>
        <result property="goodsVideoId" column="videoId"/>
        <result property="videoUrl" column="videoUrl"/>
        <association property="goodsVideoDO" resultMap="resultVideoInfo"/>
    </resultMap>
    <resultMap id="resultVideoInfo" type="com.mmc.pms.entity.GoodsVideoDO">
        <id property="id" column="videoId"/>
        <result property="videoUrl" column="videoUrl"/>
    </resultMap>


    <resultMap id="resultGoodsImg" type="com.mmc.pms.entity.GoodsImgDO">
        <id property="id" column="id"/>
        <result property="imgUrl" column="img_url"/>
        <result property="imgType" column="img_type"/>
    </resultMap>
    <select id="getGoodsInfoByGoodsId" resultMap="resultGoodsInfo">
        SELECT gi.id,
               gi.pid,
               gi.create_time,
               gi.goods_name,
               gi.goods_attr,
               gi.eco_label,
               gi.master_type_id,
               gi.shelf_status,
               gi.slave_type_id,
               gi.repo_id,
               gi.goods_type,
               gi.goods_category_id,
               gi.share_fly_service_id,
               gi.is_standard_product,
               gv.id        AS videoId,
               gv.video_url AS videoUrl,
               gt.remark    AS remark,
               gt.sort_type_id,
               gi.sort,
               gi.is_show_code,
               gi.tag
        FROM goods_info gi
                 LEFT JOIN goods_video gv ON gi.id = gv.goods_info_id
            AND gv.is_deleted = 0
                 LEFT JOIN goods_type gt ON gi.master_type_id = gt.id
            AND gt.is_deleted = 0
        WHERE gi.is_deleted = 0
          AND gi.id = #{id}
    </select>

    <select id="listGoodsInfoByGoodsId" resultMap="resultGoodsImg">
        SELECT id,
               img_url,
               img_type
        FROM goods_img
        WHERE is_deleted = 0
          AND goods_info_id = #{id}
        ORDER BY img_type DESC
    </select>

    <resultMap id="resultGoodsDetail" type="com.mmc.pms.entity.GoodsDetailDO">
        <id column="id" property="id"/>
        <result property="goodsDesc" column="goods_desc"/>
        <result property="content" column="content"/>
        <result property="remark" column="remark"/>
    </resultMap>
    <select id="getGoodsDetailByGoodsId" resultMap="resultGoodsDetail">
        SELECT id,
               goods_desc,
               content,
               remark
        FROM goods_detail
        WHERE goods_info_id = #{id}
    </select>
    <resultMap id="resultGoodsQa" type="com.mmc.pms.entity.GoodsQaDO">
        <id property="id" column="id"/>
        <result property="question" column="question"/>
        <result property="answer" column="answer"/>
    </resultMap>
    <resultMap id="resultGoodsService" type="com.mmc.pms.entity.GoodsServiceDO">
        <id property="id" column="id"/>
        <result property="saleServiceId" column="saleServiceId"/>
        <result property="serviceName" column="serviceName"/>
    </resultMap>
    <select id="listGoodsServiceByGoodsId" resultMap="resultGoodsService">
        SELECT gc.id,
               gc.sale_service_id AS saleServiceId,
               ss.service_name    AS serviceName
        FROM goods_service gc
                 LEFT JOIN sale_service ss ON gc.sale_service_id = ss.id
        WHERE gc.goods_info_id = #{id}
    </select>

    <select id="listGoodsQaInfoByGoodsId" resultMap="resultGoodsQa">
        SELECT id,
               question,
               answer
        FROM goods_qa
        WHERE goods_info_id = #{id}
          AND is_deleted = 0
    </select>

    <select id="getMallProdInfoByGoodsId" resultType="com.mmc.pms.entity.MallProdSkuInfoDO">
        SELECT mp.id,
               mp.prod_sku_id        prodSkuId,
               mp.prod_sku_spec_name prodSkuSpecName,
               mp.goods_type_id      goodsTypeId,
               mp.choose_type        chooseType,
               mp.is_must            must,
               mp.sku_unit_id        skuUnitId,
               su.unit_name          unitName,
               ps.product_name       productSkuName,
               ps.brand_info_id      brandInfoId,
               gt.type_name          typeName
        FROM mall_prod_sku_info mp
                 INNER JOIN sku_unit su ON su.id = mp.sku_unit_id
                 INNER JOIN product_sku ps ON ps.id = mp.prod_sku_id
                 LEFT JOIN goods_type gt ON mp.goods_type_id = gt.id
        WHERE mp.goods_info_id = #{goodsId}
          AND mp.is_deleted = 0
    </select>

    <select id="listMallProdSkuInfoSpec" resultType="com.mmc.pms.entity.MallProdSkuInfoSpecDO">
        SELECT id,
               mall_prod_sku_info_id mallProdSkuInfoId,
               product_spec_id       productSpecId
        FROM mall_prod_sku_info_spec
        WHERE goods_info_id = #{goodsId}
          AND is_deleted = 0
    </select>

    <select id="listProductSpecInfo" resultType="com.mmc.pms.entity.ProductSpecDO">
        SELECT
        id,
        product_sku_id productSkuId,
        spec_name specName,
        spec_image specImage,
        part_no partNo,
        version_desc versionDesc
        FROM
        product_spec
        WHERE
        id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>


    <select id="getMallIndustrySkuInfo" resultType="com.mmc.pms.entity.MallIndustrySkuInfoDO">
        SELECT mi.id,
               mi.industry_sku_id        industrySkuId,
               mi.industry_sku_spec_name industrySkuSpecName,
               mi.choose_type            chooseType,
               mi.sku_unit_id            skuUnitId,
               mi.goods_type_id          goodsTypeId,
               mi.is_must                must,
               gt.type_name              typeName,
               su.unit_name              unitName,
               ins.solution_name         industrySkuName
        FROM mall_industry_sku_info mi
                 INNER JOIN goods_type gt ON gt.id = mi.goods_type_id
                 INNER JOIN sku_unit su ON su.id = mi.sku_unit_id
                 INNER JOIN industry_sku ins ON mi.industry_sku_id = ins.id
        WHERE mi.goods_info_id = #{id}
          AND mi.is_deleted = 0
    </select>
    <resultMap id="resultIndustrySkuInfoSpecMap" type="com.mmc.pms.entity.MallIndustrySkuInfoSpecDO">
        <id column="id" property="id"/>
        <result column="mall_industry_sku_info_id" property="mallIndustrySkuInfoId"/>
        <result column="industry_spec_id" property="industrySpecId"/>
        <association property="industrySpecDO" resultMap="resultIndustrySpecMap"/>
    </resultMap>
    <resultMap id="resultIndustrySpecMap" type="com.mmc.pms.entity.IndustrySpecDO">
        <result column="industry_sku_id" property="industrySkuId"/>
        <result column="spec_image" property="specImage"/>
        <result column="spec_name" property="specName"/>
    </resultMap>
    <select id="getIndustrySkuInfoSpec" resultMap="resultIndustrySkuInfoSpecMap">
        SELECT mi.id,
               mi.mall_industry_sku_info_id,
               mi.industry_spec_id,
               ins.industry_sku_id,
               ins.spec_image,
               ins.spec_name
        FROM mall_industry_sku_info_spec mi
                 INNER JOIN industry_spec ins ON ins.id = mi.industry_spec_id and mi.is_deleted = 0
        WHERE goods_info_id = #{id}
    </select>


    <select id="countListGoodsByQO" resultType="int" parameterType="com.mmc.pms.model.qo.GoodsInfoQO">
        SELECT count(*)
        FROM
        goods_info gi
        LEFT JOIN goods_img img ON img.goods_info_id = gi.id
        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 goods_detail gd ON gd.goods_info_id = gi.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>
    <resultMap id="resultMapAppGoodsInfo" type="com.mmc.pms.entity.GoodsInfoDO">
        <id property="id" column="id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="shelfStatus" column="shelf_status"/>
        <result property="mainImg" column="mainImg"/>
        <result property="goodsDesc" column="goods_desc"/>
        <result property="masterTypeId" column="master_type_id"/>
        <result property="slaveTypeId" column="slave_type_id"/>
        <result property="goodsAttr" column="goods_attr"/>
        <result property="ecoLabel" column="eco_label"/>
        <result property="showCode" column="is_show_code"/>
        <result property="sort" column="sort"/>
        <result property="tag" column="tag"/>
    </resultMap>


    <select id="listGoodsByQO" resultMap="resultMapAppGoodsInfo" parameterType="com.mmc.pms.model.qo.GoodsInfoQO">
        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>

    <select id="listIndustryIdBySort" resultType="com.mmc.pms.entity.GoodsTypeDO">
        SELECT id
        FROM goods_type
        WHERE is_deleted = 0
          AND sort_type_id = 2
          AND pid = 0
        ORDER BY sort ASC
    </select>

    <select id="getIndustryProductInventory" resultType="com.mmc.pms.service.Impl.IndustryProductInventoryDO">
        SELECT id,
               industry_spec_id industrySpecId,
               product_sku_id   productSkuId,
               is_selected      selected
        FROM industry_product_inventory
        WHERE industry_spec_id = #{industrySpecId}
          AND is_deleted = 0
    </select>

    <select id="listInventorySpec" resultType="com.mmc.pms.entity.InventorySpecDO">
        select id, industry_product_inventory_id industryProductInventoryId, product_spec_id productSpecId
        from inventory_spec
        where industry_product_inventory_id in (
        <foreach collection="list" separator="," index="index" item="d">
            #{d}
        </foreach>)
    </select>

    <select id="getProductSpecDetail" resultType="com.mmc.pms.entity.ProductSpecDO">
        select id,
               product_sku_id productSkuId,
               spec_name      specName,
               spec_image     specImage,
               part_no        partNo,
               version_desc   versionDesc,
               create_time    createTime
        from product_spec
        where id = #{id}
    </select>

    <select id="getProductSkuDetail" resultType="com.mmc.pms.entity.ProductSkuDO">
        SELECT ps.id,
               ps.product_name productName,
               ps.model,
               ps.create_time  createTime,
               gt.id           goodsTypeId,
               gt.type_name    typeName,
               bi.brand_name   productBrand,
               ps.sort_type_id sortTypeId,
               st.sort_name    sortName
        FROM product_sku ps
                 INNER JOIN goods_type gt ON ps.goods_type_id = gt.id
                 LEFT JOIN brand_info bi ON bi.id = ps.brand_info_id
                 LEFT JOIN sort_type st ON ps.goods_type_id = st.id
        WHERE ps.id = #{id}
    </select>
</mapper>
