package com.doumee.dao.business;
|
|
import com.baomidou.mybatisplus.core.conditions.Wrapper;
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.core.toolkit.Constants;
|
import com.doumee.dao.business.model.Bookings;
|
import com.doumee.dao.business.vo.RoomStatisticsVo;
|
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;
|
|
import java.util.List;
|
|
/**
|
* @author 江蹄蹄
|
* @date 2023/05/04 18:18
|
*/
|
public interface BookingsMapper extends MPJBaseMapper<Bookings> {
|
|
|
|
@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.`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 ,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() 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 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);
|
|
|
|
|
@Select("SELECT \n" +
|
"t1.name `roomName`,\n" +
|
"MAX(t1.`01`) `januaryCount`,\n" +
|
"MAX(t1.`02`) `februaryCount`,\n" +
|
"MAX(t1.`03`) `marchCount`,\n" +
|
"MAX(t1.`04`) `aprilCount`,\n" +
|
"MAX(t1.`05`) `mayCount`,\n" +
|
"MAX(t1.`06`) `juneCount`,\n" +
|
"MAX(t1.`07`) `julyCount`,\n" +
|
"MAX(t1.`08`) `augustCount`,\n" +
|
"MAX(t1.`09`) `septemberCount`,\n" +
|
"MAX(t1.`10`) `octoberCount`,\n" +
|
"MAX(t1.`11`) `novemberCount`,\n" +
|
"MAX(t1.`12`) `decemberCount`\n" +
|
"FROM\n" +
|
"(SELECT \n" +
|
"t.name,\n" +
|
"CASE WHEN t.yue='01' THEN t.c ELSE 0 END AS `01`,\n" +
|
"CASE WHEN t.yue='02' THEN t.c ELSE 0 END AS `02`,\n" +
|
"CASE WHEN t.yue='03' THEN t.c ELSE 0 END AS `03`,\n" +
|
"CASE WHEN t.yue='04' THEN t.c ELSE 0 END AS `04`,\n" +
|
"CASE WHEN t.yue='05' THEN t.c ELSE 0 END AS `05`,\n" +
|
"CASE WHEN t.yue='06' THEN t.c ELSE 0 END AS `06`,\n" +
|
"CASE WHEN t.yue='07' THEN t.c ELSE 0 END AS `07`,\n" +
|
"CASE WHEN t.yue='08' THEN t.c ELSE 0 END AS `08`,\n" +
|
"CASE WHEN t.yue='09' THEN t.c ELSE 0 END AS `09`,\n" +
|
"CASE WHEN t.yue='10' THEN t.c ELSE 0 END AS `10`,\n" +
|
"CASE WHEN t.yue='11' THEN t.c ELSE 0 END AS `11`,\n" +
|
"CASE WHEN t.yue='12' THEN t.c ELSE 0 END AS `12`\n" +
|
"FROM\n" +
|
"(\n" +
|
"SELECT\n" +
|
"DATE_FORMAT(b.START_TIME,'%m') yue,\n" +
|
"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 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);
|
|
|
@Select("<script> SELECT \n" +
|
"t1.REALNAME `REALNAME`,\n" +
|
"MAX(t1.`01`) `januaryCount`,\n" +
|
"MAX(t1.`02`) `februaryCount`,\n" +
|
"MAX(t1.`03`) `marchCount`,\n" +
|
"MAX(t1.`04`) `aprilCount`,\n" +
|
"MAX(t1.`05`) `mayCount`,\n" +
|
"MAX(t1.`06`) `juneCount`,\n" +
|
"MAX(t1.`07`) `julyCount`,\n" +
|
"MAX(t1.`08`) `augustCount`,\n" +
|
"MAX(t1.`09`) `septemberCount`,\n" +
|
"MAX(t1.`10`) `octoberCount`,\n" +
|
"MAX(t1.`11`) `novemberCount`,\n" +
|
"MAX(t1.`12`) `decemberCount`\n" +
|
"FROM(\n" +
|
"SELECT \n" +
|
"t.id,\n" +
|
"t.REALNAME,\n" +
|
"CASE WHEN t.yue='01' THEN t.c ELSE 0 END AS `01`,\n" +
|
"CASE WHEN t.yue='02' THEN t.c ELSE 0 END AS `02`,\n" +
|
"CASE WHEN t.yue='03' THEN t.c ELSE 0 END AS `03`,\n" +
|
"CASE WHEN t.yue='04' THEN t.c ELSE 0 END AS `04`,\n" +
|
"CASE WHEN t.yue='05' THEN t.c ELSE 0 END AS `05`,\n" +
|
"CASE WHEN t.yue='06' THEN t.c ELSE 0 END AS `06`,\n" +
|
"CASE WHEN t.yue='07' THEN t.c ELSE 0 END AS `07`,\n" +
|
"CASE WHEN t.yue='08' THEN t.c ELSE 0 END AS `08`,\n" +
|
"CASE WHEN t.yue='09' THEN t.c ELSE 0 END AS `09`,\n" +
|
"CASE WHEN t.yue='10' THEN t.c ELSE 0 END AS `10`,\n" +
|
"CASE WHEN t.yue='11' THEN t.c ELSE 0 END AS `11`,\n" +
|
"CASE WHEN t.yue='12' THEN t.c ELSE 0 END AS `12` \n" +
|
"FROM \n" +
|
"(SELECT \n" +
|
"su.id,\n" +
|
"su.REALNAME,\n" +
|
"DATE_FORMAT(b.START_TIME,'%m') yue,\n" +
|
"SUM(" +
|
"CONVERT( (UNIX_TIMESTAMP(b.END_TIME) - UNIX_TIMESTAMP(b.START_TIME))/(60*60), DECIMAL(5,1))) c\n" +
|
"FROM \n" +
|
"meeting_user_rel ur\n" +
|
"LEFT JOIN `SYSTEM_USER` su ON ur.USER_ID = su.id \n" +
|
"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>"+
|
"GROUP BY su.id, DATE_FORMAT(b.START_TIME,'%m')) t) t1 GROUP BY t1.id" +
|
"</script>")
|
IPage<UserStatisticsVo> getUserStatistics(IPage page, @Param("yearNum") Integer yearNum, @Param("userId") Integer userId);
|
}
|
