<?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.FactoryDataMapper">

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

    <select id="selectDetailPage" 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 AS c
        WHERE
            c.deleted = 0
            <if test="reqVO.factoryId != null">
                AND c.factory_id = #{reqVO.factoryId}
            </if>
            <if test="reqVO.date != null and reqVO.date.length > 0">
                <if test="reqVO.date[0] != null">
                    AND c.date &gt;= #{reqVO.date[0]}
                </if>
                <if test="reqVO.date[1] != null">
                AND c.date &lt;= #{reqVO.date[1]}
                </if>
            </if>
            <if test="reqVO.createTime != null and reqVO.createTime.length > 0">
                <if test="reqVO.createTime[0] != null">
                    AND c.create_time &gt;= #{reqVO.createTime[0]}
                </if>
                <if test="reqVO.createTime[1] != null">
                    AND c.create_time &lt;= #{reqVO.createTime[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="selectDataSum" resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.factorydata.vo.FactoryDataTotalVO">
        SELECT
            IFNULL( SUM( CASE WHEN data_type = 2 THEN IFNULL(total_num, 0) END ), 0 ) AS inTotal,
            IFNULL( SUM( CASE WHEN data_type = 1 THEN IFNULL(total_num, 0) END ), 0 ) AS outTotal,
            IFNULL( SUM( CASE WHEN data_type = 1 OR data_type = 2 THEN IFNULL(damage_num, 0) END ), 0 ) AS damageNum
        FROM
            sf_factory_data
        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>
    </select>

    <select id="selectDataSumByFactory" resultType="cn.iocoder.yudao.module.smartfactory.controller.admin.factorydata.vo.FactoryDataTotalVO">
        SELECT
            a.factory_id AS factoryId,
            b.short_name AS factoryName,
            SUM( CASE WHEN a.data_type = 2 THEN IFNULL( a.total_num, 0 ) END ) AS inTotal,
            SUM( CASE WHEN a.data_type = 1 THEN IFNULL( a.total_num, 0 ) END ) AS outTotal,
            SUM( CASE WHEN a.data_type = 1 OR a.data_type = 2 THEN IFNULL( a.damage_num, 0 ) END ) AS damageNum
        FROM
            sf_factory_data a,
            sf_factory_info b
        WHERE
            a.deleted = 0
            AND a.factory_id = b.id
        GROUP BY
            a.factory_id
    </select>
</mapper>