<?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.fjhx.wms.mapper.stock.StockMapper">
    <select id="getPage" resultType="com.fjhx.wms.entity.stock.vo.StockVo">
        SELECT s.id,
               pi.definition,
               s.warehouse_id,
               s.product_id,
               s.quantity,
               s.create_user,
               s.create_time,
               s.update_user,
               s.update_time,
               s.company_id
        FROM stock s
                 LEFT JOIN warehouse w on s.warehouse_id = w.id
                 LEFT JOIN product_info pi ON s.product_id = pi.id
            ${ew.customSqlSegment}
    </select>
    <select id="pageByProduct" resultType="com.fjhx.wms.entity.stock.vo.StockVo">
        SELECT
            s.id,
            s.warehouse_id,
            s.product_id,
            sum(s.quantity)                                                     quantity,
            s.create_user,
            s.create_time,
            s.update_user,
            s.update_time
        FROM stock s
                 LEFT JOIN product_info pi ON s.product_id = pi.id
            ${ew.customSqlSegment}
    </select>
    <select id="pageByProductSpu" resultType="com.fjhx.wms.entity.stock.vo.StockVo">
        SELECT s.id,
               ps.id           productSpuId,
               ps.`code`       productSpuCode,
               ps.`name`       productSpuName,
               sum(s.quantity) quantity
        FROM stock s
                 LEFT JOIN product_info pi ON s.product_id = pi.id
                 LEFT JOIN product_spu ps ON pi.product_spu_id = ps.id
            ${ew.customSqlSegment}
    </select>
    <select id="stockWarningPage" resultType="com.fjhx.wms.entity.stock.vo.StockVo">
        SELECT *
        FROM (SELECT pi.id                      AS productId,
                     pi.custom_code             AS productCode,
                     pi.`name`                  AS productName,
                     pi.stock_threshold,
                     IFNULL(sum(s.quantity), 0) AS quantity
              FROM product_info pi
                       LEFT JOIN stock s ON s.product_id = pi.id
              WHERE pi.stock_threshold != 0
		            AND s.company_id = #{companyId}
              GROUP BY
                  pi.id) t1
        WHERE t1.quantity &lt; t1.stock_threshold
    </select>

</mapper>
