<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.yunfang.nyys.mapper.NyysJzjlDeviceTreeMapper"><resultMaptype="NyysJzjlDeviceTree"id="NyysJzjlDeviceTreeResult"><resultproperty="unitId"column="unit_id"/><resultproperty="unitName"column="unit_name"/><resultproperty="areaId"column="area_id"/><resultproperty="areaName"column="area_name"/><resultproperty="deviceId"column="device_id"/><resultproperty="deviceDesc"column="device_desc"/><resultproperty="deptId"column="dept_id"/><resultproperty="status"column="status"/><resultproperty="areaCount"column="area_count"/><resultproperty="deviceCount"column="device_count"/><resultproperty="itemCount"column="item_count"/><resultproperty="itemArea"column="item_area"/><resultproperty="billCount"column="bill_count"/><resultproperty="billAmount"column="bill_amount"/><resultproperty="billAmount"column="bill_amount"/><resultproperty="quotaAmount"column="quota_amount"/><resultproperty="quotaYear"column="quota_year"/><resultproperty="quotaAmountSum"column="quota_amount_sum"/></resultMap><sqlid="selectNyysJzjlUnitListVo">
SELECT DISTINCT f.*
FROM (select (SELECT count(area_id) from nyys_jzjl_area area where unit.unit_id = area.unit_id) as area_count,
(select count(1)
from nyys_jzjl_area_device device
where unit.unit_id = device.unit_id) as device_count,
(select count(1)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_item item on device.device_id = item.device_id
where unit.unit_id = device.unit_id) as item_count,
(select sum(fitem.land_area)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_item item on device.device_id = item.device_id
join nyys_farmer_item fitem on item.item_id = fitem.item_id
where unit.unit_id = device.unit_id) as item_area,
(select count(bill_id)
from nyys_jzjl_bill bill
WHERE bill.unit_id = unit.unit_id) as bill_count,
(select sum(bill_amount)
from nyys_jzjl_bill bill
WHERE bill.unit_id = unit.unit_id) as bill_amount,
(select sum(quota.quota_amount)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_quota quota on device.device_id = quota.device_id
where unit.unit_id = device.unit_id) as quota_amount_sum,
(select quota.quota_year
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_quota quota on device.device_id = quota.device_id
where unit.unit_id = device.unit_id) as quota_year,
unit_id,
unit_name,
dept_id,
status
from nyys_jzjl_unit unit) f
join nyys_jzjl_area n on f.unit_id = n.unit_id
</sql><sqlid="selectNyysJzjlAreaVo">
select area_id,
area_name,
unit_id,
dept_id,
status,
(select unit_name from nyys_jzjl_unit unit where unit.unit_id = area.unit_id) as unit_name,
(select count(1) from nyys_jzjl_area_device device where area.area_id = device.area_id) as device_count,
(select count(1)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_item item on device.device_id = item.device_id
where area.area_id = device.area_id) as item_count,
(select sum(fitem.land_area)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_item item on device.device_id = item.device_id
join nyys_farmer_item fitem on item.item_id = fitem.item_id
where area.area_id = device.area_id) as item_area,
(select count(bill_id) from nyys_jzjl_bill bill WHERE bill.area_id = area.area_id) as bill_count,
(select sum(bill_amount) from nyys_jzjl_bill bill WHERE bill.area_id = area.area_id) as bill_amount,
(select sum(quota.quota_amount)
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_quota quota on device.device_id = quota.device_id
where area.area_id = device.area_id) as quota_amount,
(select quota.quota_year
from nyys_jzjl_area_device device
join nyys_jzjl_area_device_quota quota on device.device_id = quota.device_id
where area.area_id = device.area_id) as quota_year
from nyys_jzjl_area area
</sql><sqlid="selectNyysJzjlAreaDeviceVo">
select device_id,
device_desc,
area_id,
unit_id,
dept_id,
status,
(select area_name from nyys_jzjl_area area where area.area_id = areadevice.area_id) as area_name,
(select unit_name
from nyys_jzjl_unit unit
where unit.unit_id = areadevice.unit_id) as unit_name,
(select count(1)
from nyys_jzjl_area_device_item item
where areadevice.device_id = item.device_id) as item_count,
(select sum(fitem.land_area)
from nyys_jzjl_area_device_item item
join nyys_farmer_item fitem on item.item_id = fitem.item_id
where areadevice.device_id = item.device_id) as item_area,
(select count(bill_id)
from nyys_jzjl_bill bill
WHERE bill.device_id = areadevice.device_id) as bill_count,
(select sum(bill_amount)
from nyys_jzjl_bill bill
WHERE bill.device_id = areadevice.device_id) as bill_amount,
(select quota_amount
from nyys_jzjl_area_device_quota quota
where quota.device_id = areadevice.device_id) as quota_amount,
(select quota_year
from nyys_jzjl_area_device_quota quota
where quota.device_id = areadevice.device_id) as quota_year
from nyys_jzjl_area_device areadevice
</sql><selectid="selectNyysJzjlUnitList"parameterType="NyysJzjlDeviceTree"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlUnitListVo"/><where><iftest="unitName != null and unitName != ''">and f.unit_name like concat('%', #{unitName}, '%')</if><iftest="deptId != null ">and f.dept_id = #{deptId}</if><iftest="status != null and status != ''">and f.status = #{status}</if><iftest="status != null and status != ''">and f.quota_amount_sum = #{quotaAmountSum}</if><iftest="status != null and status != ''">and f.quota_year = #{quotaYear}</if><iftest="params.level != null and params.level != ''"><iftest="params.level == 0">
and f.unit_id= #{params.id}
</if></if><!-- 数据范围过滤 -->
${params.dataScope}
</where>
group by f.unit_id
</select><selectid="selectNyysJzjlUnitById"parameterType="Long"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlUnitListVo"/>
where f.unit_id = #{unitId}
</select><selectid="selectNyysJzjlAreaList"parameterType="NyysJzjlDeviceTree"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlAreaVo"/><where><iftest="areaName != null and areaName != ''">and area_name like concat('%', #{areaName}, '%')</if><iftest="unitId != null ">and unit_id = #{unitId}</if><iftest="deptId != null ">and dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
find_in_set(#{deptId}, ancestors))
</if><iftest="status != null and status != ''">and status = #{status}</if><iftest="status != null and status != ''">and quota_amount_sum = #{quotaAmountSum}</if><iftest="status != null and status != ''">and quota_year = #{quotaYear}</if><iftest="params.level != null and params.level != ''"><iftest="params.level == 0">
and area.unit_id= #{params.id}
</if><iftest="params.level == 1">
and area.area_id= #{params.id}
</if></if><!-- 数据范围过滤 -->
${params.dataScope}
</where></select><selectid="selectNyysJzjlAreaById"parameterType="Long"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlAreaVo"/>
where area_id = #{areaId}
</select><selectid="selectNyysJzjlAreaDeviceList"parameterType="NyysJzjlDeviceTree"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlAreaDeviceVo"/><where><iftest="areaId != null ">and area_id = #{areaId}</if><iftest="unitId != null ">and unit_id = #{unitId}</if><iftest="deptId != null ">and dept_id = #{deptId}</if><iftest="status != null and status != ''">and status = #{status}</if><iftest="status != null and status != ''">and quota_amount = #{quotaAmount}</if><iftest="status != null and status != ''">and quota_year = #{quotaYear}</if><iftest="params.level != null and params.level != ''"><iftest="params.level == 0">
and areadevice.unit_id= #{params.id}
</if><iftest="params.level == 1">
and areadevice.area_id= #{params.id}
</if><iftest="params.level == 2">
and areadevice.device_id= #{params.id}
</if></if><!-- 数据范围过滤 -->
${params.dataScope}
</where></select><selectid="selectNyysJzjlAreaDeviceById"parameterType="String"resultMap="NyysJzjlDeviceTreeResult"><includerefid="selectNyysJzjlAreaDeviceVo"/>
where device_id = #{deviceId}
</select></mapper>
90
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
