<?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.czg.service.product.mapper.ProductStockFlowMapper">

    <select id="getProductStatistics" resultType="com.czg.product.vo.ProductStatisticsVo">
        SELECT
            sum( CASE WHEN t1.in_out_type = 'in' then t1.in_out_number end) AS inSumTotal,
            sum( CASE WHEN t1.in_out_item = 'win-in' THEN t1.in_out_number END ) AS winInNum,
            sum( CASE WHEN t1.in_out_item = 'order-out' and t1.in_out_number > 0 THEN t1.in_out_number END ) AS refundInNum,
            sum( CASE WHEN t1.in_out_type = 'out' then abs(t1.in_out_number) end) AS outSumTotal,
            sum( CASE WHEN t1.in_out_item = 'loss-out' THEN abs(t1.in_out_number) END ) AS lossOutNum,
            abs(sum( CASE WHEN t1.in_out_item = 'order-out' THEN t1.in_out_number END )) AS salesNum,
            sum( CASE WHEN t1.in_out_item = 'damage-out' THEN abs(t1.in_out_number) END ) AS damageNum
        FROM
            tb_product_stock_flow t1
        LEFT JOIN tb_product t2 on t1.product_id = t2.id
        where t1.shop_id = #{shopId}
        <if test="id != null">
            and t1.product_id = #{id}
        </if>
        <if test="categoryId != null">
            and t2.category_id = #{categoryId}
        </if>
        <if test="name != null and name != ''">
            and t2.name like concat('%', #{name}, '%') or t1.product_name like concat('%', #{name}, '%')
        </if>
        <if test="type != null and type != ''">
            and t2.type = #{type}
        </if>
    </select>
    <select id="getProductStockFlowList" resultType="com.czg.product.entity.ProductStockFlow">
        select
            t1.*,
            t2.order_no,
            t4.name as unit_name
        from tb_product_stock_flow t1
        left join tb_order_info t2 on t1.order_id = t2.id
        left join tb_product t3 on t1.product_id = t3.id
        left join tb_shop_prod_unit t4 on t3.unit_id = t4.id
        where t1.shop_id = #{shopId}
        <if test="inOutType != null and inOutType != ''">
            and t1.in_out_type = #{inOutType}
        </if>
        <if test="inOutItem != null and inOutItem != ''">
            and t1.in_out_item = #{inOutItem}
        </if>
        <if test="isGreaterZero != null">
            and t1.in_out_number > 0
        </if>
        <if test="productId != null">
            and t1.product_id = #{productId}
        </if>
        <if test="beginDate != null and beginDate != ''">
            and t1.create_time >= str_to_date(#{beginDate}, '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_time <= str_to_date(#{endDate}, '%Y-%m-%d %H:%i:%s')
            ]]>
        </if>
        order by t1.id desc
    </select>
</mapper>