<?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.sc.mappers.ProductStockMapper">
    <resultMap id="ProductStock" type="com.lframework.xingyun.sc.entity.ProductStock">
        <id column="id" property="id"/>
        <result column="product_id" property="productId"/>
        <result column="sc_id" property="scId"/>
        <result column="stock_num" property="stockNum"/>
        <result column="tax_price" property="taxPrice"/>
        <result column="tax_amount" property="taxAmount"/>
    </resultMap>

    <sql id="ProductStockDto_sql">
        SELECT
            gs.id,
            gs.product_id,
            gs.sc_id,
            gs.stock_num,
            gs.tax_price,
            gs.tax_amount
        FROM tbl_product_stock AS gs
        INNER JOIN base_data_product AS g ON g.id = gs.product_id AND g.product_type = 1
        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
    </sql>

    <update id="addStock">
        UPDATE tbl_product_stock
        SET stock_num = stock_num + #{stockNum}, tax_amount = tax_amount + #{taxAmount}
        <if test="reCalcCostPrice">,tax_price = CASE WHEN stock_num = 0 THEN 0 ELSE tax_amount / stock_num END</if>
        WHERE product_id = #{productId}
        AND sc_id = #{scId}
        AND stock_num = #{oriStockNum}
        AND tax_amount = #{oriTaxAmount}
    </update>
    <update id="subStock">
        UPDATE tbl_product_stock
        SET stock_num = stock_num - #{stockNum}, tax_amount = tax_amount - #{taxAmount}
        <if test="reCalcCostPrice">,tax_price = CASE WHEN stock_num = 0 THEN tax_price ELSE tax_amount / stock_num END</if>
        WHERE product_id = #{productId}
        AND sc_id = #{scId}
        AND stock_num = #{oriStockNum}
        AND tax_amount = #{oriTaxAmount}
        AND stock_num >= #{stockNum}
    </update>

    <select id="getByProductIdAndScId" resultMap="ProductStock">
        <include refid="ProductStockDto_sql"/>
        WHERE gs.product_id = #{productId}
        AND gs.sc_id = #{scId}
    </select>

    <select id="getByProductId" resultMap="ProductStock">
        <include refid="ProductStockDto_sql"/>
        WHERE gs.product_id = #{productId}
    </select>

    <select id="query" resultMap="ProductStock">
        <include refid="ProductStockDto_sql"/>
        INNER JOIN base_data_store_center AS sc ON sc.id = gs.sc_id
        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.scId != null and vo.scId != ''">
                    AND gs.sc_id = #{vo.scId}
                </if>
                <if test="vo.productCode != null and vo.productCode != ''">
                    AND g.code = #{vo.productCode}
                </if>
                <if test="vo.productName != null and vo.productName != ''">
                    AND g.name LIKE CONCAT('%', #{vo.productName}, '%')
                </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>
        </where>
        ORDER BY sc.code, g.code
    </select>
    <select id="getByProductIdsAndScId" resultMap="ProductStock">
        <include refid="ProductStockDto_sql"/>
        WHERE gs.product_id IN <foreach collection="productIds" open="(" separator="," close=")" item="item">#{item}</foreach>
        <if test="productType != null">
            AND g.product_type = #{productType}
        </if>
        AND gs.sc_id = #{scId}
    </select>

    <select id="getByProductIds" resultMap="ProductStock">
        <include refid="ProductStockDto_sql"/>
        WHERE gs.product_id IN <foreach collection="productIds" open="(" separator="," close=")" item="item">#{item}</foreach>
        <if test="productType != null">
            AND g.product_type = #{productType}
        </if>
    </select>

    <select id="getProductStockProportion" resultType="com.lframework.xingyun.sc.vo.chart.ProductStockDataVo">
        SELECT
		ts.product_id AS productId,
		bp.`name` AS productName,
		SUM(ts.stock_num) AS stockNum
        FROM tbl_product_stock AS ts,base_data_product AS bp WHERE ts.product_id=bp.id
        GROUP BY ts.product_id, bp.`name`
    </select>
</mapper>
