<?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.victoriatourist_json,
            de.dept_name
        FROM
            stock s
                LEFT JOIN bytesailing_item.product_info pi ON s.product_id = pi.id
                LEFT JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_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,
            sum(json_unquote( s.victoriatourist_json -> '$.frozenQuantity' )) frozenQuantity,
            sum(json_unquote( s.victoriatourist_json -> '$.defectiveQuantity' )) defectiveQuantity,
            de.dept_name
        FROM
            stock s
                LEFT JOIN bytesailing_item.product_info pi ON s.product_id = pi.id
                LEFT JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_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,
            pi.victoriatourist_json,
            de.dept_name
        FROM
            stock s
                LEFT JOIN bytesailing_item.product_info pi ON s.product_id = pi.id
                LEFT JOIN bytesailing_item.product_spu ps ON pi.product_spu_id = ps.id
                LEFT JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_id
            ${ew.customSqlSegment}
    </select>
    <select id="getDepts" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        SELECT
            de.dept_id,
            de.dept_name
        FROM
            stock s
                JOIN bytesailing_item.product_info pi ON s.product_id = pi.id
                JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_id
        GROUP BY
            de.dept_id
    </select>
    <select id="getStockAnalysisPage" resultType="com.fjhx.wms.entity.stock.po.StockAnalysis">
        SELECT
            s.product_id,
            sum( ifnull( s.quantity, 0 ) ) AS quantity,
            sum( ifnull( json_unquote( s.victoriatourist_json -> '$.frozenQuantity' ), 0 )) AS frozenQuantity,
            sum( ifnull( json_unquote( s.victoriatourist_json -> '$.defectiveQuantity' ), 0 )) AS defectiveQuantity,
            ifnull( pi.unit_price, 0 ) AS unitPrice,
            de.dept_id deptId,
            de.dept_name deptName,
            pi.definition productDefinition,
            pi.type AS productType,
            pi.custom_code AS productCustomCode,
            pi.name AS productName,
            pi.spec AS productSpec,
            ifnull( json_unquote( pi.victoriatourist_json -> '$.combination' ), 0 ) isCombination
        FROM
            stock s
                LEFT JOIN bytesailing_item.product_info pi ON s.product_id = pi.id
                LEFT JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_id
        ${ew.customSqlSegment}
    </select>

</mapper>
