productDev/dmvisit.git

			@@ -9,6 +9,7 @@
			import com.doumee.dao.system.vo.UserStatisticsVo;
			import com.doumee.dao.web.response.MeetingDetailResponse;
			import com.doumee.dao.web.response.MeetingListResponse;
			import com.github.yulichang.base.MPJBaseMapper;
			import org.apache.ibatis.annotations.Param;
			import org.apache.ibatis.annotations.Select;

			@@ -18,29 +19,46 @@
			* @author 江蹄蹄
			* @date 2023/05/04 18:18
			*/
			public interface BookingsMapper extends BaseMapper<Bookings> {
			public interface BookingsMapper extends MPJBaseMapper<Bookings> {



			@Select(" select a.id , b.`NAME` as roomName , a.`NAME` as meetingName ,date_format(a.START_TIME,'%Y-%m-%d') as meetingDate ," +
			@Select(" select a.id , a.START_TIME , a.END_TIME , b.`NAME` as roomName , a.`NAME` as meetingName ,date_format(a.START_TIME,'%Y-%m-%d') as meetingDate , a.START_TIME as startTime, a.status ," +
			" CONCAT(date_format(a.START_TIME,'%H:%i') , ' ~ ',date_format(a.END_TIME,'%H:%i')) as meetingTime, c.REALNAME as bookingUser ," +
			" CASE WHEN a.START_TIME > now() and a.`STATUS` = 0 THEN 1 WHEN a.END_TIME < now() or a.`STATUS` = 1 THEN 3 ELSE 2 END meetingStatus , b.IMGURL as imgUrl " +
			" from bookings a inner join rooms b on a.ROOM_ID = b.ID " +
			" inner join system_user c on a.CREATOR = c.id " +
			//" CASE WHEN a.START_TIME > now() AND a.`STATUS` = 0 THEN 1 WHEN ( (a.END_TIME < now() AND a.`STATUS` = 0 ) or a.`STATUS` = 2 ) THEN 3 WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" CASE WHEN a.START_TIME_REAL IS NULL AND a.`STATUS` = 0 THEN 1 WHEN ( a.`STATUS` = 2 ) THEN 3 WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" b.IMGURL as imgUrl ," +
			" a.remark " +
			" from meeting_book a" +
			" left join meeting_rooms b on a.ROOM_ID = b.ID " +
			" left join system_user c on a.CREATOR = c.id " +
			" ${ew.customSqlSegment} ")
			IPage<MeetingListResponse> myMeetingPage(IPage<MeetingListResponse> page, @Param(Constants.WRAPPER) Wrapper wrapper);

			@Select(" select a.id , a.START_TIME , a.END_TIME , b.`NAME` as roomName , a.`NAME` as meetingName ,date_format(a.START_TIME,'%Y-%m-%d') as meetingDate , a.START_TIME as startTime, a.status ," +
			" CONCAT(date_format(a.START_TIME,'%H:%i') , ' ~ ',date_format(a.END_TIME,'%H:%i')) as meetingTime, c.REALNAME as bookingUser ," +
			//" CASE WHEN a.START_TIME > now() AND a.`STATUS` = 0 THEN 1 WHEN ( (a.END_TIME < now() AND a.`STATUS` = 0 ) or a.`STATUS` = 2 ) THEN 3 WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" CASE WHEN a.START_TIME_REAL IS NULL AND a.`STATUS` = 0 THEN 1 " +
			//" WHEN ( (a.END_TIME < now() AND a.`STATUS` = 0 ) or a.`STATUS` = 2 ) THEN 3 " +
			" WHEN ( a.`STATUS` = 2 ) THEN 3 " +
			"WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" b.IMGURL as imgUrl ," +
			" a.remark " +
			" from meeting_book a inner join meeting_rooms b on a.ROOM_ID = b.ID " +
			" inner join system_user c on a.CREATOR = c.id " +
			" ${ew.customSqlSegment} ")
			List<MeetingListResponse> meetingList(@Param(Constants.WRAPPER) Wrapper wrapper);


			@Select(" select a.id , b.id as roomId, b.`NAME` as roomName , a.`NAME` as meetingName ,date_format(a.START_TIME,'%Y年%m月%d日') as meetingDate ," +
			@Select(" select a.id ,a.ISDELETED, a.START_TIME , a.END_TIME , b.id as roomId, b.`NAME` as roomName , a.`NAME` as meetingName ,date_format(a.START_TIME,'%Y年%m月%d日') as meetingDate ," +
			" CONCAT(date_format(a.START_TIME,'%H:%i') , ' ~ ',date_format(a.END_TIME,'%H:%i')) as meetingTime, c.REALNAME as bookingUserName ," +
			" CASE WHEN a.START_TIME > now() and a.`STATUS` = 0 THEN 1 WHEN a.END_TIME < now() or a.`STATUS` = 1 THEN 3 ELSE 2 END meetingStatus ," +
			//" CASE WHEN a.START_TIME > now() AND a.`STATUS` = 0 THEN 1 WHEN a.END_TIME < now() AND a.`STATUS` = 0 THEN 3 WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" CASE WHEN a.START_TIME_REAL IS NULL AND a.`STATUS` = 0 THEN 1 WHEN ( a.status = 2 ) THEN 3 WHEN a.`STATUS` = 1 THEN 5 ELSE 2 END meetingStatus ," +
			" a.CONTENT as meetingContent, c.MOBILE as bookingUserMobile , e.`NAME` as bookingUserDepartment , a.CREATOR as bookingUserId , a.remark " +
			" from bookings a inner join rooms b on a.ROOM_ID = b.ID " +
			" inner join system_user c on a.CREATOR = c.id " +
			" inner join system_department_user d on c.id = d.USER_ID " +
			" INNER JOIN system_department e on d.DEPARTMENT_ID = e.ID " +
			" where a.id = #{id} ")
			" from meeting_book a inner join meeting_rooms b on a.ROOM_ID = b.ID " +
			" left join system_user c on a.CREATOR = c.id " +
			" left join system_department_user d on c.id = d.USER_ID " +
			" left JOIN system_department e on d.DEPARTMENT_ID = e.ID " +
			" where a.id = #{id} and a.isdeleted=0")
			MeetingDetailResponse meetingDetail(@Param("id") Integer id);


			@@ -82,9 +100,9 @@
			"b.ROOM_ID,\n" +
			"r.NAME,\n" +
			"SUM( CONVERT( (UNIX_TIMESTAMP(b.END_TIME) - UNIX_TIMESTAMP(b.START_TIME))/(60*60),DECIMAL(5,1))) c\n" +
			"FROM bookings b\n" +
			"LEFT JOIN rooms r ON r.ID = b.ROOM_ID \n" +
			"WHERE DATE_FORMAT(b.START_TIME,'%Y') = #{yearNum} AND b.STATUS = 0 \n" +
			"FROM meeting_book b\n" +
			"LEFT JOIN meeting_rooms r ON r.ID = b.ROOM_ID \n" +
			"WHERE DATE_FORMAT(b.START_TIME,'%Y') = #{yearNum} AND b.STATUS in(0,2) and b.isdeleted=0\n" +
			"GROUP BY b.ROOM_ID , DATE_FORMAT(b.START_TIME,'%m')\n" +
			") t) t1 GROUP BY t1.name")
			List<RoomStatisticsVo> getRoomStatistics(@Param("yearNum") Integer yearNum);
			@@ -128,10 +146,10 @@
			"SUM(" +
			"CONVERT( (UNIX_TIMESTAMP(b.END_TIME) - UNIX_TIMESTAMP(b.START_TIME))/(60*60), DECIMAL(5,1))) c\n" +
			"FROM \n" +
			"user_rel ur\n" +
			"meeting_user_rel ur\n" +
			"LEFT JOIN `SYSTEM_USER` su ON ur.USER_ID = su.id \n" +
			"LEFT JOIN bookings b ON ur.OBJ_ID = b.id \n" +
			"WHERE DATE_FORMAT(b.START_TIME,'%Y') = #{yearNum} AND b.STATUS = 0" +
			"LEFT JOIN meeting_book b ON ur.OBJ_ID = b.id \n" +
			"WHERE DATE_FORMAT(b.START_TIME,'%Y') = #{yearNum} AND b.STATUS in(0,2) and b.isdeleted=0" +
			"<if test='userId != null'>"+
			"and ur.USER_ID = #{userId}\n" +
			"</if>"+