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 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 BaseMapper<Bookings> {



    @Select(" select a.id , 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 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} ")
    IPage<MeetingListResponse> myMeetingPage(IPage<MeetingListResponse> page, @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 ," +
            " 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 ," +
            " 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  " +
            " 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} ")
    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 = 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 = 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);
}