<?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.lframework.xingyun.basedata.mappers.ProductMapper">

    <sql id="ProductDto_sql">
        SELECT
            g.*
        FROM base_data_product AS g
        LEFT JOIN base_data_product_brand AS b ON b.id = g.brand_id
        LEFT JOIN base_data_product_category AS c ON c.id = g.category_id
        LEFT JOIN recursion_mapping AS rm ON rm.node_id = g.category_id and rm.node_type = 2
    </sql>

    <select id="query" resultType="com.lframework.xingyun.basedata.entity.Product">
        <include refid="ProductDto_sql"/>
        <where>
            <if test="vo != null">
                <if test="vo.code != null and vo.code != ''">
                    AND g.code = #{vo.code}
                </if>
                <if test="vo.skuCode != null and vo.skuCode != ''">
                    AND g.sku_code = #{vo.skuCode}
                </if>
                <if test="vo.brandId != null and vo.brandId != ''">
                    AND g.brand_id = #{vo.brandId}
                </if>
                <if test="vo.categoryId != null and vo.categoryId != ''">
                    AND (g.category_id = #{vo.categoryId} OR FIND_IN_SET(#{vo.categoryId}, rm.path))
                </if>
                <if test="vo.name != null and vo.name != ''">
                    AND g.name LIKE CONCAT('%', #{vo.name}, '%')
                </if>
                <if test="vo.shortName != null and vo.shortName != ''">
                    AND g.short_name LIKE CONCAT('%', #{vo.shortName}, '%')
                </if>
                <if test="vo.productType != null">
                    AND g.product_type = #{vo.productType}
                </if>
                <if test="vo.available != null">
                    AND g.available = #{vo.available}
                </if>
                <if test="vo.startTime != null">
                    AND g.create_time >= #{vo.startTime}
                </if>
                <if test="vo.endTime != null">
                    <![CDATA[
                AND g.create_time <= #{vo.endTime}
                ]]>
                </if>
            </if>
        </where>
        ORDER BY g.code
    </select>

    <select id="findById" resultType="com.lframework.xingyun.basedata.entity.Product">
        <include refid="ProductDto_sql"/>
        WHERE g.id = #{id}
    </select>
    <select id="getByCategoryIds" resultType="com.lframework.xingyun.basedata.entity.Product">
        <include refid="ProductDto_sql"/>
        WHERE c.id IN <foreach collection="categoryIds" open="(" separator="," close=")" item="item">#{item}</foreach>
        <if test="productType != null">
            AND g.product_type = #{productType}
        </if>
        ORDER BY g.code
    </select>
    <select id="getByBrandIds" resultType="com.lframework.xingyun.basedata.entity.Product">
        <include refid="ProductDto_sql"/>
        WHERE b.id IN <foreach collection="brandIds" open="(" separator="," close=")" item="item">#{item}</foreach>
        <if test="productType != null">
            AND g.product_type = #{productType}
        </if>
        ORDER BY g.code
    </select>
    <select id="queryCount" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM base_data_product AS g
        LEFT JOIN recursion_mapping AS rm ON rm.node_id = g.category_id and rm.node_type = 2
        <where>
            <if test="vo != null">
                <if test="vo.code != null and vo.code != ''">
                    AND g.code = #{vo.code}
                </if>
                <if test="vo.name != null and vo.name != ''">
                    AND g.name LIKE CONCAT('%', #{vo.name}, '%')
                </if>
                <if test="vo.skuCode != null and vo.skuCode != ''">
                    AND g.sku_code = #{vo.skuCode}
                </if>
                <if test="vo.brandId != null and vo.brandId != ''">
                    AND g.brand_id = #{vo.brandId}
                </if>
                <if test="vo.categoryId != null and vo.categoryId != ''">
                    AND (g.category_id = #{vo.categoryId} OR FIND_IN_SET(#{vo.categoryId}, rm.path))
                </if>
                <if test="vo.productType != null">
                    AND g.product_type = #{vo.productType}
                </if>
                <if test="vo.available != null">
                    AND g.available = #{vo.available}
                </if>
                <if test="vo.startTime != null">
                    AND g.create_time >= #{vo.startTime}
                </if>
                <if test="vo.endTime != null">
                    <![CDATA[
                AND g.create_time <= #{vo.endTime}
                ]]>
                </if>
            </if>
        </where>
        ORDER BY g.code
    </select>
    <select id="getIdNotInProductProperty" resultType="java.lang.String">
        SELECT p.id
        FROM base_data_product AS p
        LEFT JOIN base_data_product_property_relation AS pr ON p.id = pr.product_id AND pr.property_id = #{propertyId}
        WHERE pr.product_id IS NULL
    </select>
    <select id="getIdByCategoryId" resultType="java.lang.String">
        SELECT id
        FROM base_data_product AS p
        WHERE p.category_id = #{categoryId}
    </select>

    <select id="selector" resultType="com.lframework.xingyun.basedata.entity.Product">
        <include refid="ProductDto_sql"/>
        <where>
          <if test="vo != null">
              <if test="vo.code != null and vo.code != ''">
                  AND g.code = #{vo.code}
              </if>
              <if test="vo.skuCode != null and vo.skuCode != ''">
                  AND g.sku_code = #{vo.skuCode}
              </if>
              <if test="vo.brandId != null and vo.brandId != ''">
                  AND g.brand_id = #{vo.brandId}
              </if>
              <if test="vo.categoryId != null and vo.categoryId != ''">
                  AND (g.category_id = #{vo.categoryId} OR FIND_IN_SET(#{vo.categoryId}, rm.path))
              </if>
              <if test="vo.name != null and vo.name != ''">
                  AND g.name LIKE CONCAT('%', #{vo.name}, '%')
              </if>
              <if test="vo.shortName != null and vo.shortName != ''">
                  AND g.short_name LIKE CONCAT('%', #{vo.shortName}, '%')
              </if>
              <if test="vo.productType != null">
                  AND g.product_type = #{vo.productType}
              </if>
              <if test="vo.available != null">
                  AND g.available = #{vo.available}
              </if>
              <if test="vo.startTime != null">
                  AND g.create_time >= #{vo.startTime}
              </if>
              <if test="vo.endTime != null">
                  <![CDATA[
                  AND g.create_time <= #{vo.endTime}
                  ]]>
              </if>
          </if>
        </where>
        ORDER BY g.code
    </select>

    <select id="getCodeSku"  resultType="java.lang.String">
        SELECT sku_code FROM base_data_product
        ORDER BY id DESC
        LIMIT 1
    </select>

    <select id="getCode"  resultType="java.lang.String">
        SELECT `code` FROM base_data_product
        WHERE `code` LIKE 'sp%'
        ORDER BY `code` DESC
        LIMIT 1;
    </select>
</mapper>
