KAK

List<RptCapMachroom> queryIDCFilterListPage(PageBean<RptCapMachroom> page);

    <select id="queryIDCFilterListPage" parameterType="PageBean" resultType="RptCapMachroom">
      
      /*合计能耗*/
SELECT 
   tmp.*,
   <![CDATA[  case when lv.alarmLimit_flag1 >= tmp.pue and tmp.pue>= 1 then 1
          when lv.alarmLimit_flag2 >= tmp.pue and tmp.pue > lv.alarmLimit_flag1 then 2
          when lv.alarmLimit_flag3 >= tmp.pue and tmp.pue > lv.alarmLimit_flag2 then 3
          when lv.alarmLimit_flag3 < tmp.pue then 4
          else 0
   end pueFlag ,
    case when lv.alarmLimit_flag1 >= tmp.pue and tmp.pue>= 1 then '正常'
          when lv.alarmLimit_flag2 >= tmp.pue and tmp.pue > lv.alarmLimit_flag1 then lv.alarmLimit_msg1
          when lv.alarmLimit_flag3 >= tmp.pue and tmp.pue > lv.alarmLimit_flag2 then lv.alarmLimit_msg2
          when lv.alarmLimit_flag3 < tmp.pue  then lv.alarmLimit_msg3
          else '异常'
   end pueFlagStr   ]]>
 from
(
      SELECT
      tmp1.machroomID,
      tmp1.machName,
      tmp1.totalUsage,
      NVL(IF(tmp1.mainUsage>0,tmp1.mainUsage,0),0) as mainUsage,
      NVL(tmp1.airconUsage,0) as airconUsage,
      NVL(tmp1.otherUsage,0) as otherUsage,
      case when tmp1.mainUsage = 0 or ISNULL(tmp1.mainUsage) then 0 ELSE
             NVL(round((tmp1.totalUsage / IF(tmp1.mainUsage>0,tmp1.mainUsage,0)),2),0)
         end pue,

      NVL(tmp2.mainRead,0) as mainRead,
      NVL(tmp2.airconRead,0) as airconRead,
      NVL(tmp2.otherRead,0) as otherRead,
      NVL(tmp2.totalRead,0) as totalRead,
      NVL(tmp2.temperature,0) as temperature,
      tmp2.readtime/*最后一天日期*/
      from 
      (
         select 
                  t2.machName as machName,
                  ROUND(NVL(SUM(IF(t1.total_usage >0,t1.total_usage,null)),NVL(sum(t1.main_usage+t1.aircon_usage+t1.other_usage),0)),2)  as totalUsage,/*总耗电量*/
                  -- ROUND(NVL(sum(t1.main_usage),0),2)  as mainUsage2,/*主设备耗电量*/
                  ROUND(
                     IF(
                        (sum(t1.main_usage)=0 OR ISNULL(sum(t1.main_usage))),
                        IF(sum(t1.aircon_usage)=0 or ISNULL(sum(t1.aircon_usage)),
                           0,
                           ROUND(NVL(SUM(IF(t1.total_usage >0,t1.total_usage,null)),NVL(sum(t1.main_usage+t1.aircon_usage+t1.other_usage),0)),2)
                           -ROUND(NVL(sum(t1.aircon_usage),0),2)
                           -ROUND(NVL(sum(t1.other_usage),0),2)
                        ),
                        sum(t1.main_usage)
                     )
                  ,2) as mainUsage,/*主设备耗电量*/  
                  ROUND(NVL(sum(t1.aircon_usage),0),2)  as airconUsage,/*空调设备耗电量*/
                  ROUND(NVL(sum(t1.other_usage),0),2)  as otherUsage,/*其他设备耗电量*/
                  t1.machroomID as machroomID,
                  to_char(t1.readtime,'yyyy-MM-dd') readtime,
                  5 buildtype
               from rpt_cap_machroom t1
               left join res_machroom t2 on t1.machroomID = t2.zgID
               <!-- 权限 -->
               <where>
                  <if test="params.userIdForPerm != null and params.userIdForPerm != '' ">
                     EXISTS (
                        select 'X' from view_user_region_perm vurp where vurp.user_id = #{params.userIdForPerm} and vurp.region_perm_id = t2.regionID
                        /*同时传递的查询区域过滤条件*/
                        <if test="params.regionid != null and params.regionid != '' ">
                           and vurp.region_perm_id like CONCAT(${params.regionid},'%')
                        </if>
                     )
                  </if>
                  and t2.machroomType = 3
                  <if test="params.readtimeFrom != null and params.readtimeFrom != '' ">  <![CDATA[ and t1.readtime >= #{params.readtimeFrom} ]]></if>
                  <if test="params.readtimeTo != null and params.readtimeTo != '' ">  <![CDATA[ and t1.readtime <= #{params.readtimeTo} ]]></if>
                  <if test="(params.readtimeFrom == null or params.readtimeFrom == '' ) and (params.readtimeTo == null or params.readtimeTo == '')"> 
                      <![CDATA[ and t1.readtime = CURDATE() ]]>
                   </if>
                  <if test="params.machname != null and params.machname != '' ">  <![CDATA[ and t2.machName like CONCAT('%',#{params.machname},'%') ]]></if>
               </where>
         group by t2.zgid 
         order by t2.zgID,t1.readtime desc
      )tmp1 left join 
      (
         /*传递日期的那天 最后读数:最后温度 最后读数*/
         SELECT tmp1.machroomID,NVL(tmp1.total_read,0) as totalRead,NVL(tmp1.main_read,0) as mainRead,NVL(tmp1.aircon_read,0) as airconRead,NVL(tmp1.other_read,0) as otherRead,NVL(tmp1.temperature,0) temperature,to_char(tmp1.readtime,'yyyy-MM-dd') readtime FROM rpt_cap_machroom tmp1,
         (
            SELECT t1.machroomID, MAX(t1.readtime) readtime FROM rpt_cap_machroom t1 left join res_machroom t2 on t1.machroomID = t2.zgID  
             <where>
                  <if test="params.userIdForPerm != null and params.userIdForPerm != '' ">
                     EXISTS (
                        select 'X' from view_user_region_perm vurp where vurp.user_id = #{params.userIdForPerm} and vurp.region_perm_id = t2.regionID
                        /*同时传递的查询区域过滤条件*/
                        <if test="params.regionid != null and params.regionid != '' ">
                           and vurp.region_perm_id like CONCAT(${params.regionid},'%')
                        </if>
                     )
                  </if>
                  and t2.machroomType = 3
                  <if test="params.readtimeFrom != null and params.readtimeFrom != '' ">  <![CDATA[ and t1.readtime >= #{params.readtimeFrom} ]]></if>
                  <if test="params.readtimeTo != null and params.readtimeTo != '' ">  <![CDATA[ and t1.readtime <= #{params.readtimeTo} ]]></if>
                  <if test="(params.readtimeFrom == null or params.readtimeFrom == '' ) and (params.readtimeTo == null or params.readtimeTo == '')"> 
                      <![CDATA[ and t1.readtime = CURDATE() ]]>
                   </if>
            </where>
            GROUP BY t1.machroomID
         ) tmp2
         WHERE tmp1.machroomID = tmp2.machroomID AND tmp1.readtime = tmp2.readtime
      )tmp2 on tmp1.machroomID = tmp2.machroomID
      order by tmp1.machroomID 
)tmp ,view_pue_alarm lv
order by tmp.machroomID 
   </select>
   
   
   <select id="queryFilterListPage" parameterType="PageBean" resultType="RptCapMachroom">
      SELECT
         tmp.*,
         <![CDATA[
         (case when 1.4 >= tmp.pue and tmp.pue>= 1 then 1
             when 1.8 >= tmp.pue and tmp.pue > 1.4 then 2
             when 2.5 >= tmp.pue and tmp.pue > 1.8 then 3
             when 2.5 < tmp.pue then 4
             else 0
         end) pueFlag ,
         (case when 1.4 >= tmp.pue and tmp.pue>= 1 then '正常'
             when 1.8 >= tmp.pue and tmp.pue > 1.4 then '一般'
             when 2.5 >= tmp.pue and tmp.pue > 1.8 then '重要'
             when 2.5 < tmp.pue  then '严重'
             else '异常'
         end) pueFlagStr
         ]]>
      FROM (
         SELECT
            tmp1.machroomID,
            tmp1.zgid,
            tmp1.machName,
            tmp1.totalUsage,
            tmp1.buildtype,
            tmp1.extend extend,
            NVL(tmp1.mainUsage,0) as mainUsage,
            NVL(tmp1.airconUsage,0) as airconUsage,
            NVL(tmp1.otherUsage,0) as otherUsage,
            (case
            WHEN tmp1.mainUsage = 0 or tmp1.mainUsage IS NULL then 0
            ELSE
            NVL(round((tmp1.totalUsage / tmp1.mainUsage),2),0)
            end) pue,

            NVL(tmp2.mainRead,0) as mainRead,
            NVL(tmp2.airconRead,0) as airconRead,
            NVL(tmp2.otherRead,0) as otherRead,
            NVL(tmp2.totalRead,0) as totalRead,
            NVL(tmp2.temperature,0) as temperature,
            tmp2.readtime readtimeStr/*最后一天日期*/
         from
         (
            select
               MAX(t1.roomName) as machName,
               ROUND(NVL(sum(t1.totalUsage),0),2) as totalUsage,/*总耗电量*/
               -- ROUND(NVL(sum(t1.main_usage),0),2)  as mainUsage2,/*主设备耗电量*/
               ROUND(NVL(sum(t1.mainUsage),0),2) as mainUsage,/*主设备耗电量*/
               ROUND(NVL(sum(t1.airconUsage),0),2)  as airconUsage,/*空调设备耗电量*/
               ROUND(NVL(sum(t1.otherUsage),0),2)  as otherUsage,/*其他设备耗电量*/
               t2.zgid as machroomID,
               MAX(t2.machroomID) zgid,
               MAX(t2.extend) as extend,
               -- t1."date" readtime,
               5 buildtype
            from -- view_room_day_not_build
            <include refid="com.crowdcrystal.mapper.RptCapBuildMapper.onOFDayMarchroom" ></include>
            t1
            <!-- 权限控制 -->
            <if test="params.userIdForPerm != null and params.userIdForPerm != '' ">
               INNER JOIN "view_user_region_perm" vurp ON vurp.user_id = #{params.userIdForPerm} and vurp.region_perm_id = t1.regionID
               /*同时传递的查询区域过滤条件*/
               <if test="params.regionid != null and params.regionid != '' ">
                  INNER JOIN
                  (
                  SELECT DISTINCT r."ID" FROM SYS_REGION r START WITH r.PARENTID = #{params.regionid} OR r."ID" = #{params.regionid} CONNECT BY PRIOR r."ID" = r.PARENTID
                  ) r ON vurp.REGION_PERM_ID_BK=r."ID"
               </if>
            </if>
            left join res_machroom t2 on t1.zgid = t2.zgID  AND t1.powertype=5
            <!-- 权限 -->
            <where>
               <if test="params.machname != null and params.machname != '' ">  <![CDATA[ and t1.roomName like ('%'||#{params.machname}||'%') ]]></if>
               <if test="params.buildid != null and params.buildid != '' ">  <![CDATA[ and t2.buildID = #{params.buildid} ]]></if>
               <if test="params.extend != null and params.extend != '' and  params.extend!=4">  <![CDATA[ and t2.extend = #{params.extend} ]]></if>
               <if test="params.extend != null and params.extend != '' and  params.extend==4">  <![CDATA[ and t2.zgid <97 ]]></if>
               <if test="params.machroomtype != null and params.machroomtype != '' ">  <![CDATA[ and t2.machroomType =#{params.machroomtype} ]]></if>
            </where>
            group by t2.zgid
            order by t2.zgid
         )tmp1 left join
         (
            /*传递日期的那天 最后读数:最后温度 最后读数*/
            SELECT tmp1.machroomID,NVL(tmp1.total_read,0) as totalRead,NVL(tmp1.main_read,0) as mainRead,NVL(tmp1.aircon_read,0) as airconRead,NVL(tmp1.other_read,0) as otherRead,NVL(tmp1.temperature,0) temperature,to_char(tmp1.readtime,'yyyy-MM-dd') readtime FROM rpt_cap_machroom tmp1,
            (
            SELECT t1.machroomID, MAX(t1.readtime) readtime FROM rpt_cap_machroom t1 left join res_machroom t2 on t1.machroomID = t2.zgID
            <where>
               <if test="params.readtimeFrom != null and params.readtimeFrom != '' ">  <![CDATA[ and t1.readtime >= TO_DATE(#{params.readtimeFrom},'yyyy-MM-dd') ]]></if>
               <if test="params.readtimeTo != null and params.readtimeTo != '' ">  <![CDATA[ and t1.readtime <= TO_DATE(#{params.readtimeTo},'yyyy-MM-dd') ]]></if>
               <if test="(params.readtimeFrom == null or params.readtimeFrom == '' ) and (params.readtimeTo == null or params.readtimeTo == '')">
                  <![CDATA[ and t1.readtime = SYSDATE ]]>
               </if>
            </where>
            GROUP BY t1.machroomID
            ) tmp2
            WHERE tmp1.machroomID = tmp2.machroomID AND tmp1.readtime = tmp2.readtime
         )tmp2 on tmp1.machroomID = tmp2.machroomID
         order by tmp1.machroomID
      ) tmp
   </select>