<?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.DmsMajorDormitoryMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.srs.dormitory.domain.DmsMajorDormitory">
        <id column="id" property="id" />
        <result column="major_id" property="majorId" />
        <result column="dormitory_id" property="dormitoryId" />
    </resultMap>
	<select id="selectUnassigned" resultType="com.srs.dormitory.domain.Vo.UnassignedVo">
		SELECT majors.major_id,
		       major_name,
		       sg.grade_id,
		       COUNT(*) as `count`
		FROM srs_majors majors
			     LEFT JOIN srs_class class ON majors.major_id = class.major_id
			     LEFT JOIN srs_student stu ON stu.class_id = class.class_id
			     left join srs_grade sg on class.grade_id = sg.grade_id
		WHERE college_id = #{deptId}
		  AND gender = #{gender}
		  and sg.grade_id = #{gradeId}
		GROUP BY major_id, major_name,grade_id
	</select>
	<select id="selectDormitory" resultType="java.util.Map">
		SELECT
		    d.id,
		    u.`name` unitName,
		    d.room_no roomNO
		FROM dms_dormitory d
			     LEFT JOIN dms_dormitory_floor f ON f.id = d.floor_id
			     LEFT JOIN dms_dormitory_unit u ON u.id = d.unit_id
			     LEFT JOIN dms_dormitory_building b ON f.building_id = b.id
			${ew.customSqlSegment}
	</select>

</mapper>
