<?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="cn.iocoder.yudao.module.smartfactory.dal.mysql.factorydata.FactoryDataSizeMapper">

    <!--
        一般情况下，尽可能使用 Mapper 进行 CRUD 增删改查即可。
        无法满足的场景，例如说多表关联查询，才使用 XML 编写 SQL。
        代码生成器暂时只生成 Mapper XML 文件本身，更多推荐 MybatisX 快速开发插件来生成查询。
        文档可见：https://www.iocoder.cn/MyBatis/x-plugins/
     -->

    <select id="selectDetailList" resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.factorydata.vo.FactoryDataInfoRespVO">
        SELECT
            c.factory_id AS factoryId,
            c.date AS date,
            c.size_id AS sizeId,
            c.size_name AS sizeName,
            MAX(CASE WHEN c.type = 4 THEN c.id END) AS inNumId,
            MAX(CASE WHEN c.type = 4 THEN c.num END) AS inNum,
            MAX(CASE WHEN c.type = 4 THEN c.square_num END) AS squareNum,
            MAX(CASE WHEN c.type = 1 THEN c.id END) AS inDamageNumId,
            MAX(CASE WHEN c.type = 1 THEN c.num END) AS inDamageNum,
            MAX(CASE WHEN c.type = 2 THEN c.id END) AS outNumId,
            MAX(CASE WHEN c.type = 2 THEN c.num END) AS outNum,
            MAX(CASE WHEN c.type = 3 THEN c.id END) AS outDamageNumId,
            MAX(CASE WHEN c.type = 3 THEN c.num END) AS outDamageNum,
            MAX(c.create_time) AS createTime
        FROM
            sf_factory_data_size c
        WHERE
            c.deleted = 0
            <if test="factoryId != null">
                AND c.factory_id = #{factoryId}
            </if>
            <if test="date != null and date.length > 0">
                <if test="date[0] != null">
                    AND c.date &gt;= #{date[0]}
                </if>
                <if test="date[1] != null">
                    AND c.date &lt;= #{date[1]}
                </if>
            </if>
            <if test="dateTime != null and dateTime.length > 0">
                <if test="dateTime[0] != null">
                    AND c.create_time &gt;= #{dateTime[0]}
                </if>
                <if test="dateTime[1] != null">
                    AND c.create_time &lt;= #{dateTime[1]}
                </if>
            </if>
        GROUP BY
            c.factory_id,
            c.date,
            c.size_name
        ORDER BY
            c.date DESC,
            c.factory_id,
            c.size_name
    </select>

    <select id="selectSUMByDateTimes" resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.factorydata.vo.FactoryDataInfoRespVO">
        SELECT
            factory_id AS factoryId,
            date AS date,
            SUM( CASE WHEN type = 4 THEN num END ) AS inNum,
            SUM( CASE WHEN type = 1 THEN num END ) AS inDamageNum,
            SUM( CASE WHEN type = 2 THEN num END ) AS outNum,
            SUM( CASE WHEN type = 3 THEN num END ) AS outDamageNum
        FROM
            sf_factory_data_size
        WHERE
            create_time BETWEEN #{dateTime[0]} AND #{dateTime[1]}
            AND deleted = 0
        GROUP BY
            factory_id,
            date
    </select>

    <select id="selectLastSize" resultType="cn.iocoder.yudao.module.smartfactory.dal.dataobject.factorydata.FactoryDataSizeDO">
        SELECT DISTINCT
            a.size_id AS sizeId,
            a.size_name AS sizeName
        FROM
            sf_factory_data_size a,
            ( SELECT MAX( date ) AS d FROM sf_factory_data_size ) b
        WHERE
            a.date = b.d
            AND a.deleted = 0
            AND a.factory_id = #{ factoryId }
            AND a.type IN
            <foreach collection="types" item="types" open="(" close=")" separator=",">
                #{types}
            </foreach>
    </select>


    <select id="selectSizeDataSumByFactory" resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.factorydata.vo.sizeData.FactoryDataSizeTotalVO">
        SELECT
            factory_id AS factoryId,
            size_name AS sizeName,
            SUM( CASE WHEN type = 4 THEN num END ) AS inTotal,
            SUM( CASE WHEN type = 2 THEN num END ) AS outTotal,
            IFNULL( SUM( CASE WHEN type = 1 THEN num END ), 0 ) + IFNULL( SUM( CASE WHEN type = 3 THEN  num END ), 0 ) AS damageNum
        FROM
            sf_factory_data_size
        WHERE
            deleted = 0
            <if test="factoryId != null">
                AND factory_id = #{factoryId}
            </if>
            <if test="date != null and date.length > 0">
                <if test="date[0] != null">
                    AND date &gt;= #{date[0]}
                </if>
                <if test="date[1] != null">
                    AND date &lt;= #{date[1]}
                </if>
            </if>
        GROUP BY
            factory_id,
            size_name
        ORDER BY
            factory_id,
            size_name
    </select>
</mapper>