<select id="findList" parameterClass="java.util.Map"
resultClass="CapacityReport">
select t1.areaName as title,
t1.areaId as areaId,
t1.totalAmount as totalAmount,
t2.usedAmount as usedAmount,
t1.totalAmount - t2.usedAmount as freeAmount
from
(
select a.area_name as areaName,
a.area_id as areaId,
a.sort as sort,
nvl(t.capacity, 0) as totalAmount
from proof_area a
left join
(
select r.area_id,
sum(t.capacity) as capacity
from proof_room r,
proof_cabinet c,
(
select s.proof_cabinet_id,
count(1) as capacity
from proof_cabinet_struct s
left join proof_drawer d
on s.proof_cabinet_struct_id = d.proof_cabinet_struct_id
left join proof_grid g
on d.proof_drawer_id = g.proof_drawer_id
group by s.proof_cabinet_id
) t
where r.proof_room_id = c.proof_room_id
and c.proof_cabinet_id = t.proof_cabinet_id
group by r.area_id
) t
on a.area_id = t.area_id
) t1,
(
select a.area_name as areaName,a.area_id as areaId, nvl(t.capacity,0) as usedAmount
from proof_area a
left join
(
select t.area_id, sum(capacity) as capacity
from
(
select r.area_id, count(distinct g.proof_grid_id) as capacity
from proof_room r,
proof_cabinet c,
proof_cabinet_struct s,
proof_drawer d,
proof_grid g,
proof_location l,
proof_info i,
archives_info ai
where r.proof_room_id = c.proof_room_id
and c.proof_cabinet_id = s.proof_cabinet_id
and s.proof_cabinet_struct_id = d.proof_cabinet_struct_id
and d.proof_drawer_id = g.proof_drawer_id
and g.proof_grid_id = l.proof_grid_id
and ((l.proof_info_id = i.proof_info_id
and i.proof_exist_status <![CDATA[>=]]>4
and i.proof_exist_status <![CDATA[<=]]>7)
or(
ai.ARCHIVES_INFO_ID=l.proof_info_id
and ai.ARCHIVES_EXIST_STATUS <![CDATA[>=]]>4
and ai.ARCHIVES_EXIST_STATUS <![CDATA[<=]]>7))
group by r.area_id
union all
select r.area_id, count(distinct d.proof_drawer_id) as capacity
from proof_room r,
proof_cabinet c,
proof_cabinet_struct s,
proof_drawer d,
proof_location l,
proof_info i,
archives_info ai
where r.proof_room_id = c.proof_room_id
and c.proof_cabinet_id = s.proof_cabinet_id
and s.proof_cabinet_struct_id = d.proof_cabinet_struct_id
and d.proof_drawer_id = l.proof_drawer_id
and ((l.proof_info_id = i.proof_info_id
and i.proof_exist_status <![CDATA[>=]]>4
and i.proof_exist_status <![CDATA[<=]]>7)
or(
ai.ARCHIVES_INFO_ID=l.proof_info_id
and ai.ARCHIVES_EXIST_STATUS <![CDATA[>=]]>4
and ai.ARCHIVES_EXIST_STATUS <![CDATA[<=]]>7))
group by r.area_id
union all
select r.area_id, count(distinct s.proof_cabinet_struct_id) as capacity
from proof_room r,
proof_cabinet c,
proof_cabinet_struct s,
proof_location l,
proof_info i,
archives_info ai
where r.proof_room_id = c.proof_room_id
and c.proof_cabinet_id = s.proof_cabinet_id
and s.proof_cabinet_struct_id = l.proof_cabinet_struct_id
and ((l.proof_info_id = i.proof_info_id
and i.proof_exist_status <![CDATA[>=]]>4
and i.proof_exist_status <![CDATA[<=]]>7)
or(
ai.ARCHIVES_INFO_ID=l.proof_info_id
and ai.ARCHIVES_EXIST_STATUS <![CDATA[>=]]>4
and ai.ARCHIVES_EXIST_STATUS <![CDATA[<=]]>7))
group by r.area_id
) t
group by t.area_id
) t
on a.area_id = t.area_id
) t2
where t1.areaName = t2.areaName
and t1.areaId=t2.areaId
<isNotEmpty property="areaIds">and t1.areaid in($areaIds$)</isNotEmpty>
order by t1.sort
</select>
分享
