<?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.yami.shop.dao.ShopCategoryMapper">


    <select id="treeShopCategory" resultType="com.yami.shop.bean.vo.FrontCategoryVO">
        SELECT s.*
        FROM tz_shop_category s
                 left join tz_front_category f on s.code = f.code
        where s.shop_id = #{shopId}
          and s.is_delete = 0
          and f.is_delete = 0
    </select>


    <select id="selectAppListAll" resultType="com.yami.shop.bean.model.ShopCategory">
        SELECT fc.*, tl.name as labelName
        FROM tz_shop_category fc
        left join tz_category_prod cp on fc.code = cp.code  and cp.is_delete = 0 and cp.shop_id = #{shopId}
        left join tz_prod p on p.prod_id = cp.prod_id and p.is_delete = 0
        LEFT JOIN tz_sku sku ON p.prod_id = sku.prod_id AND sku.is_delete = 0
        LEFT JOIN tz_channel_prod cha ON sku.sku_id = cha.sku_id AND cha.is_delete = 0 AND cha.shop_id = #{shopId} AND cha.channel_id =  #{channelId}
        left join tz_label tl on fc.label = tl.id
        WHERE fc.is_delete = 0 and fc.is_delete = 0
        and fc.shop_id = #{shopId}
        AND (
        fc.level = 1
        OR (
        fc.level != 1
        AND p.prod_id IS NOT NULL
        AND cp.prod_id IS NOT NULL
        AND cha.sku_id IS NOT NULL
        )
        )
        group by fc.id order by fc.num asc
    </select>

    <select id="selectAppCount" resultType="java.lang.Integer">
        SELECT count(fc.id) as prodCount
        FROM tz_shop_category fc
                 left join tz_category_prod cp on fc.code = cp.code  and cp.is_delete = 0 and cp.shop_id = #{shopId}
                 left join tz_prod p on p.prod_id = cp.prod_id and p.is_delete = 0
        WHERE fc.is_delete = 0 and fc.is_delete = 0
          AND p.prod_id IS NOT NULL
          AND cp.prod_id IS NOT NULL
          and fc.shop_id = #{shopId}
          and fc.parent_code = #{code}
    </select>



    <select id="selectListAll" resultType="com.yami.shop.bean.model.ShopCategory">
        SELECT fc.*, count(cp.id) as prodCount,tl.name as labelName
        FROM tz_shop_category fc
        left join tz_category_prod cp on fc.code = cp.code and cp.shop_id = #{shopId}
        left join tz_label tl on fc.label = tl.id
        WHERE fc.is_delete = 0
        <if test="shopId">and fc.shop_id = #{shopId}</if>
        <if test="name != null and name != ''">
            AND (
            fc.name like concat('%',#{name},'%')

            OR fc.level = 2 AND fc.parent_code IN (
            SELECT code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 1
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            )
            OR fc.level = 3 AND fc.parent_code IN (
            SELECT code FROM tz_shop_category
            WHERE parent_code IN (
            SELECT code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 1
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            ) AND is_delete = 0
            )

            OR fc.level = 1 AND fc.code IN (
            SELECT parent_code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 2
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            )
            OR fc.level = 3 AND fc.parent_code IN (
            SELECT code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 2
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            )

            OR fc.level = 2 AND fc.code IN (
            SELECT parent_code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 3
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            )
            OR fc.level = 1 AND fc.code IN (
            SELECT parent_code FROM tz_shop_category
            WHERE code IN (
            SELECT parent_code FROM tz_shop_category
            WHERE name like concat('%',#{name},'%')
            AND level = 3
            AND is_delete = 0
            <if test="shopId">AND shop_id = #{shopId}</if>
            ) AND is_delete = 0
            )
            )
        </if>
        group by fc.id
        ORDER BY fc.num ASC
    </select>


    <update id="deleteByCode">
        UPDATE tz_front_category
        SET is_delete = 1
        WHERE code = #{code}
    </update>

    <update id="deleteByParentCode">
        UPDATE tz_front_category
        SET is_delete = 1
        WHERE parent_code = #{parentCode}
    </update>
</mapper>
