<?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.srs.dormitory.mapper.DmsDormitoryClassMapper">

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap" type="com.srs.dormitory.domain.DmsDormitoryClass">
		<id column="id" property="id"/>
		<result column="BJDM" property="BJDM"/>
		<result column="dormitory_id" property="dormitoryId"/>
		<result column="is_lock" property="isLock"/>
	</resultMap>

	<select id="listFdyRoom" resultType="DomInfo" parameterType="DmsSearch">
		select b.*,c.class_name,d.employee_id as t_no,d.`name` as teacher_name,a.is_mixed,c.class_code,a.bed_count,a.is_lock
		from dms_dormitory_class as a
				 left join view_dorm_info as b on a.dormitory_id = b.id
				 left join srs_class as c on a.BJDM = c.class_code
				 left join cph_teacher as d on a.t_no = d.employee_id
		<where>
			a.is_lock = 1
			<if test="tNo != null">AND d.employee_id = #{tNo}</if>
			<if test="classCode != null">AND a.BJDM = #{classCode}</if>
		</where>
		ORDER BY b.id asc
	</select>

	<select id="selectXwClass" parameterType="CphSearch" resultType="ClassInfo">
		SELECT a.class_name,b.major_name,c.dept_name,a.class_id,a.class_code
		from srs_class as a
		left join srs_majors as b on a.major_id = b.major_id
		left join sys_dept as c on b.college_id = c.dept_id
		<where>
			<if test="deptId != null">AND c.dept_id = #{deptId}</if>
		</where>
		ORDER BY a.grade_id desc
	</select>

	<select id="selectXwDom" parameterType="SelectDormDto" resultType="DomInfo">
		select a.*,b.room_no,c.floor,d.`name` as building_name,b.gender,g.`name` as campus_name,f.`name` as park_name
		from dms_dormitory_dept as a
				 left join dms_dormitory as b on a.dormitory_id = b.id
				 left join dms_dormitory_floor as c on b.floor_id = c.id
				 left join dms_dormitory_building as d on c.building_id = d.id
				 left join dms_dormitory_building_info as e on d.id = e.building_id
				 left join dms_dormitory_park as f on d.park_id = f.id
				 left join dms_campus as g on f.campus_id = g.id
		<where>
			a.is_lock = 1
			<if test="buildingId != null">AND d.id = #{buildingId}</if>
			<if test="floorId != null">AND c.id = #{floorId}</if>
			<if test="xydm != null">AND a.XYDM = #{xydm}</if>
			<if test="campusId != null">AND g.id = #{campusId}</if>
			<if test="parkId != null">AND f.id = #{parkId}</if>
			<if test="buildingName != null">AND d.`name` = #{buildingName}</if>
			<if test="gender != null">AND e.gender = #{gender}</if>
		</where>
		order by d.id,b.room_no
	</select>


	<select id="selectClass" resultType="java.util.Map">
		SELECT XYDM,
		       XY,
		       COUNT(*) AS `count`,
		       IFNULL((SELECT SUM(bed_num)
		               FROM dms_dormitory
		               WHERE id IN (SELECT dormitory_id
		                            FROM dms_dormitory_class
		                            WHERE BJDM IN
		                                  (SELECT BJDM FROM srs_stu_reg reg WHERE reg.XYDM = srs_stu_reg.XYDM))),
		              0)   bedSelected
		FROM `srs_stu_reg`
		GROUP BY XYDM, XY
	</select>
	<select id="selectMajor" resultType="java.util.Map">
		SELECT class_code classCode, class_name className
		FROM srs_class sc
			     join srs_stu_reg reg on sc.class_code = reg.BJDM
			     join cph_teacher ct on sc.teacher_id = ct.teacher_id
		where employee_id = #{username}
		group by class_code, class_name
	</select>

	<select id="selectGender" resultType="java.util.Map">
		SELECT BJDM,
		       XB,
		       COUNT(*) AS `count`,
		       IFNULL((SELECT SUM(bed_num)
		               FROM dms_dormitory
		               WHERE id IN (SELECT dormitory_id
		                            FROM dms_dormitory_class
		                            WHERE BJDM = srs_stu_reg.BJDM
			                          AND gender = srs_stu_reg.XB)), 0)
		                AS bedSelected
		FROM srs_stu_reg
		GROUP BY BJDM, XB
	</select>
	<select id="selectBuilding" resultType="java.util.Map">
		SELECT campus.name campus, park.name park, build.id, build.`name`, gender, COUNT(*) AS `count`
		FROM dms_dormitory dorm
		LEFT JOIN dms_dormitory_floor floor ON dorm.floor_id = floor.id
		LEFT JOIN dms_dormitory_building build ON build.id = floor.building_id
		LEFT JOIN dms_dormitory_building_info info ON build.id = info.building_id
		LEFT JOIN dms_dormitory_park park ON build.park_id = park.id
		LEFT JOIN dms_campus campus ON park.campus_id = campus.id
		<where>
			<if test="name != null">AND build.`name` = #{name}</if>
			<if test="gender != null">AND gender = #{gender}</if>
			<if test="campusId != null">AND campus.id = #{campusId}</if>
			<if test="parkId != null">AND park.id = #{parkId}</if>
		</where>
		GROUP BY campus.name, park.name, build.id, build.`name`, gender
	</select>


	<select id="selectDorm" resultType="java.util.Map" parameterType="SelectDormDto">
		select a.*,b.room_no,c.floor,d.`name`,e.gender,g.`name` as campus_name,f.`name` as park_name
		from dms_dormitory_dept as a
		left join dms_dormitory as b on a.dormitory_id = b.id
		left join dms_dormitory_floor as c on b.floor_id = c.id
		left join dms_dormitory_building as d on c.building_id = d.id
		left join dms_dormitory_building_info as e on d.id = e.building_id
		left join dms_dormitory_park as f on d.park_id = f.id
		left join dms_campus as g on f.campus_id = g.id
		<where>
			<if test="floorId != null">and c.id = #{floorId}</if>
			<if test="xydm != null">and a.XYDM = #{xydm}</if>
		</where>
		order by d.id,b.room_no
	</select>
	<select id="selectBJDM" resultType="java.util.Map">
		select BJDM, XB
		from srs_stu_reg
		where KSH = #{ksh}
	</select>
	<select id="selectBeds" resultType="java.util.Map">
		SELECT bed.id,bed.bed_code,bed.dormitory_id dormId, CASE WHEN st.bed_id IS NULL THEN 0 ELSE 1 END AS
		hasStudent,XSXM
		FROM `dms_dormitory_bed` bed
		LEFT JOIN srs_dormitory_student st ON bed.id = st.bed_id
		left join srs_stu_reg stu on st.stu_no = stu.XH
		<where>
			and bed.del_flag = '0'
			<if test="id != null">and bed.dormitory_id = #{id}</if>
		</where>
	</select>
	<select id="selectDormByIds" resultType="com.srs.dormitory.domain.Vo.SelectDormByStuVo">
		SELECT dorm.id,
		room_no,
		build.name building,
		park.name park,
		bed_num,
		dorm.occupancy
		FROM dms_dormitory dorm
		JOIN dms_dormitory_floor floor ON dorm.floor_id = floor.id
		JOIN dms_dormitory_building build ON build.id = floor.building_id
		join dms_dormitory_park park on build.park_id = park.id
		join dms_dormitory_class ddc on dorm.id = ddc.dormitory_id
		<where>
			dorm.id IN
			<foreach collection="dorms" item="dormId" open="(" separator="," close=")">
				#{dormId}
			</foreach>
			and ddc.gender = #{xb}
		</where>
	</select>
	<select id="selectBJ" resultType="java.util.Map">
		SELECT BJ
		FROM srs_stu_reg
		WHERE BJDM in (SELECT BJDM FROM dms_dormitory_class WHERE dormitory_id = #{dormId})
		group by BJ
	</select>
	<select id="selectFdyModel" resultType="com.srs.dormitory.domain.Dto.DormitoryModelDto">
		select b.id,concat( b.park_name,' ',b.building_name,' ',b.room_no ,'(',b.gender,')') as dorm,a.is_lock
		from dms_dormitory_class as a
		left join view_dorm_info as b on a.dormitory_id = b.id
		left join srs_class as c on a.BJDM = c.class_code
		left join cph_teacher as d on a.t_no = d.employee_id
		<where>
			a.is_lock = 1
				<if test="tNo != null">AND d.employee_id = #{tNo}</if>
		</where>
	</select>
	<select id="getcount" resultType="com.srs.dormitory.domain.DmsDormitory">
		select a.id,a.bed_num,
			   case when b.occupancy is null then 0 else b.occupancy end as occupancy
		from view_dorm_info as a
				 left join view_dorm_stu_count as b on a.id = b.dormitory_id
		<where>
			<if test="id != null">and a.id = #{id}</if>
		</where>
	</select>
</mapper>
