<?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.persagy.dmp.rwd.simple.dao.EquipmentReportMapper">

    <select id="queryExpectScrapCountByProjects" resultType="java.util.HashMap">
        select pj.id as projectId, pj.name as projectName, count(eq.id) as equipmentTotal,
          sum(case when
            JSON_UNQUOTE(ifnull(json_extract(eq.infos, '$.expectScrapDate'), '9999-12-31')) &lt; #{scrapDate}
            then 1 else 0 end) as expectScrapTotal
        from dt_object pj left join dt_object eq on eq.project_id = pj.id
          and eq.obj_type = 'equipment' and eq.valid = 1
        where pj.obj_type = 'project' and pj.valid = 1
        <if test="projectIds != null and projectIds.size() &gt; 0" >
            and pj.project_id in
            <foreach collection="projectIds" item="projectId" open="(" close=")" separator="," >
                #{projectId}
            </foreach>
        </if>
        group by pj.id, pj.name
	</select>
    <select id="queryEquipmentCountByType" resultType="java.util.HashMap">
        select dt_define_type.major_code as majorCode, dt_define_type.system_code as systemCode,
          dt_define_type.code as code, dt_define_type.name as name,
          count(distinct dt_object.id) as total -- 容错处理，避免字典定义重复时数量异常
        from dt_define_type left join dt_object on dt_object.class_code = dt_define_type.code
          and dt_object.obj_type = 'equipment' and dt_object.valid = 1
        <if test="projectIds != null and projectIds.size() &gt; 0" >
            and dt_object.project_id in
            <foreach collection="projectIds" item="projectId" open="(" close=")" separator="," >
                #{projectId}
            </foreach>
        </if>
        where dt_define_type.obj_type = 'equipment' and dt_define_type.code != 'equipment'
          and dt_define_type.valid = 1
        <if test="projectIds != null and projectIds.size() &gt; 0" >
            and dt_define_type.project_id in
            <foreach collection="projectIds" item="projectId" open="(" close=")" separator="," >
                #{projectId}
            </foreach>
        </if>
        group by dt_define_type.major_code, dt_define_type.system_code,
          dt_define_type.code, dt_define_type.name;
    </select>
    <select id="queryObjectCountByObjInfos" resultType="java.util.Map">
        SELECT
            <foreach collection="groups" item="group" separator=",">
                <if test="null!=group.fieldFlag and group.fieldFlag">
                    ${group.column} AS #{group.field}
                </if>
                <if test="null!=group.fieldFlag and !group.fieldFlag">
                    JSON_UNQUOTE(JSON_EXTRACT(infos, CONCAT('$.',#{group.column}))) AS #{group.field}
                </if>
            </foreach>,
            COUNT(id) AS count
        FROM dt_object
        WHERE valid=TRUE
        <if test="null!=param.objTypes and param.objTypes.size()>0">
            <foreach collection="param.objTypes" item="objType" open="AND obj_type IN(" close=")" separator=",">
                #{objType}
            </foreach>
        </if>
        <if test="null!=param.classCodes and param.classCodes.size()>0">
            <foreach collection="param.classCodes" item="classCode" open="AND class_code IN(" close=")" separator=",">
                #{classCode}
            </foreach>
        </if>
        <if test="null!=param.projectId and param.projectId.length>0">
            AND project_id=#{param.projectId}
        </if>
        <if test="null!=param.groupCode and param.groupCode.length>0">
            AND group_code=#{param.groupCode}
        </if>
        <foreach collection="groups" item="group">
            <if test="null!=group.fieldFlag and group.fieldFlag">
                AND ${group.column} IS NOT NULL
            </if>
            <if test="null!=group.fieldFlag and !group.fieldFlag">
                AND JSON_UNQUOTE(JSON_EXTRACT(infos, CONCAT('$.',#{group.column}))) IS NOT NULL
            </if>
        </foreach>
        GROUP BY
        <foreach collection="groups" item="group" separator=",">
            <if test="null!=group.fieldFlag and group.fieldFlag">
                ${group.column}
            </if>
            <if test="null!=group.fieldFlag and !group.fieldFlag">
                JSON_UNQUOTE(JSON_EXTRACT(infos, CONCAT('$.',#{group.column})))
            </if>
        </foreach>
        ORDER BY count DESC
    </select>

    <select id="queryComponentsCountByEquipId" resultType="java.util.Map">
        SELECT re.obj_from as 'id',count(1) as count,obj.class_code, df.equipment_code as 'equip_category',df.`name` AS category_name
        FROM dt_object obj
        join dt_relation re on obj.id = re.obj_to
        join dt_define_type df on df.code = obj.class_code
        where obj.valid=TRUE AND obj.obj_type='component' AND re.rel_code='Eq2Ec'
        <if test="projectIds != null and projectIds.size() &gt; 0" >
            and df.project_id in
            <foreach collection="projectIds" item="projectId" open="(" close=")" separator="," >
                #{projectId}
            </foreach>
        </if>
        <if test="null!=equipmentId and equipmentId.length>0">
            AND re.obj_from=#{equipmentId}
        </if>
        <if test="null!=projectId and projectId.length>0">
            AND obj.project_id=#{projectId}
        </if>
        GROUP BY obj.class_code,re.obj_from,df.equipment_code,df.name
    </select>

    <select id="queryEquipComponentsByProjectId" resultType="java.util.Map">
        SELECT dt_define_type.`code`,dt_define_type.`name`,IFNULL(temp.count,0) AS count FROM dt_define_type
        LEFT JOIN (SELECT dt_object.class_code,COUNT(dt_object.id) AS count FROM dt_object
            INNER JOIN dt_relation ON (dt_relation.obj_to=dt_object.id AND dt_object.obj_type='component'
        <if test="null!=equipmentId and equipmentId.length>0">
        AND dt_relation.obj_from=#{equipmentId}
        </if>
        AND dt_relation.graph_code='MechSubset' AND dt_relation.rel_code='Eq2Ec'
        <if test="null!=projectId and projectId.length>0">
            AND dt_object.project_id=#{projectId}
        </if>
        <if test="null!=groupCode and groupCode.length>0">
            AND dt_object.group_code=#{groupCode}
        </if>
        AND dt_object.valid=TRUE AND dt_relation.valid=TRUE) GROUP BY dt_object.class_code) AS temp
            ON(temp.class_code=dt_define_type.`code`) WHERE dt_define_type.obj_type='component'
        <if test="null!=category and category.length>0">
          AND dt_define_type.`code` LIKE CONCAT(#{category},'%')
        </if>
          AND dt_define_type.project_id='0' AND dt_define_type.group_code = '0' AND dt_define_type.valid=TRUE;
    </select>

    <select id="queryDefineInfoByObjType" resultType="java.util.Map">
        SELECT class_code as code ,(SELECT name FROM dt_define_type WHERE  code = class_code LIMIT 1 ) as name
        FROM dt_object
        WHERE valid=TRUE
            <if test="null!=param.objTypes and param.objTypes.size()>0">
                <foreach collection="param.objTypes" item="objType" open="AND obj_type IN(" close=")" separator=",">
                    #{objType}
                </foreach>
            </if>
            <if test="null!=param.objType and param.objType.length>0">
                AND obj_type=#{param.objType}
            </if>
            <if test="null!=param.projectId and param.projectId.length>0">
                AND project_id=#{param.projectId}
            </if>
            <if test="null!=param.groupCode and param.groupCode.length>0">
                AND group_code=#{param.groupCode}
            </if>
        GROUP BY class_code;
    </select>
</mapper>
