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>
KAK
最新推荐文章于 2022-12-16 14:01:59 发布