public IEnumerable <ModelRecord> GetBookingData(string sdate, string userid, List <string> roomid) { List <string> sqls = new List <string>(); DateTime dt1 = DateTime.Parse(sdate); string dayofWeek = ((int)dt1.DayOfWeek).ToString(); // 一般預約 sqls.Add(string.Format(@" select t.roomid, t.RECORDID, t.starttime, t.endtime, decode(lower(t.applierid),'{2}','APPL',t.booktype) booktype, t.updatet, t.applierid, t.applier, t.applierext, '' as dateday from MBS_RECORD t where t.starttime <= to_date('{0}', 'yyyy/mm/dd') + 1 and t.endtime >= to_date('{0}', 'yyyy/mm/dd') and t.roomid in ('{1}') ", sdate, string.Join("','", roomid.ToArray()), userid)); // 系統預約 // sqls.Add(string.Format(@" // select t.roomid, // t.sid as RECORDID, // to_date('{0} ' || SUBSTR(starttime, 1, 2) || ':' || // SUBSTR(starttime, 3, 2) || ':' || '00', // 'yyyy/mm/dd hh24:mi:ss') as starttime, // to_date('{0} ' || SUBSTR(endtime, 1, 2) || ':' || // SUBSTR(endtime, 3, 2) || ':' || '00', // 'yyyy/mm/dd hh24:mi:ss') as endtime, // sysdate as UPDATET, // 'SYSL' as booktype, // 'SYSTEM' as APPLIER, // 'SYSTEM' as applierid, // '' as applierext, // dateday // from MBS_LOCKROOM t // where t.roomid in ('{1}') // and t.dateday = '{2}' // ", sdate, string.Join("','", roomid.ToArray()), dayofWeek)); DataSet ds = DALService.ExecuteBatchQuery(sqls.ToArray()); IEnumerable <ModelRecord> ret = GetBRecordList(ds.Tables[0]); ret = ret.Concat( GenRangeSysBookRecord(roomid, sdate, sdate)); ds.Dispose(); return(ret); }
public string BookRoomEvent([FromBody] ModelRecord recordobj) { string ret = "成功預約"; DateTime out1; try { APIHelper ws = new APIHelper(); UserInfo vUserInfo = new UserInfo(); string roomid = recordobj.ROOMID; ModelMR mr = ws.GetMeetingRoomByID(roomid); if (mr == null) { throw new Exception("找不到會議室"); } DateTime sdt = DateTime.Parse(recordobj.STARTTIME); DateTime edt = DateTime.Parse(recordobj.ENDTIME); // .ToString("yyyy/MM/dd HH:mm:ss"), List <int> minuInt = new List <int> { 0, 15, 30, 45 }; if (!minuInt.Contains(sdt.Minute) || !minuInt.Contains(edt.Minute)) { throw new Exception("時間格式錯誤"); } TimeSpan ts = edt.Subtract(sdt); if (ts.TotalSeconds == 0) { throw new Exception("起始結束時間不可一樣"); } if (DateTime.Compare(DateTime.Today, sdt) > 0) { throw new Exception("預約日期不可小於今日"); } // 檢查預約天數 if (DateTime.Compare(sdt, DateTime.Today.AddDays(mr.POSTPERIOD)) > 0) { throw new Exception("預約日期不可大於 (" + mr.POSTPERIOD + ") 天"); } // 檢查會議室時間是否為系統預約 int dateday = (int)sdt.DayOfWeek; List <string> sqls = new List <string>(); #region 檢查 是否被預約 sqls.Add(string.Format(@" select * from MBS_RECORD t where t.roomid = '{0}' and( t.starttime between to_date('{1}', 'yyyy/mm/dd hh24:mi:ss') AND to_date('{2}', 'yyyy/mm/dd hh24:mi:ss') or t.endtime between to_date('{1}', 'yyyy/mm/dd hh24:mi:ss') AND to_date('{2}', 'yyyy/mm/dd hh24:mi:ss')) ", roomid, sdt.AddSeconds(1).ToString("yyyy/MM/dd HH:mm:ss"), edt.AddSeconds(-1).ToString("yyyy/MM/dd HH:mm:ss"))); #endregion #region 檢查系統預約 sql sqls.Add(string.Format(@"select a.dateday,a.starttime,a.endtime from MBS_LOCKROOM a where a.roomid = '{0}' and (a.starttime between '{2}' AND '{3}' or a.endtime between '{4}' AND '{5}') and a.dateday = '{1}' ", roomid , dateday.ToString(), sdt.ToString("HHmm"), edt.AddMinutes(-1).ToString("HHmm"), sdt.AddMinutes(1).ToString("HHmm"), edt.ToString("HHmm") )); #endregion DataSet ds = DALService.ExecuteBatchQuery(sqls.ToArray()); IEnumerable <DataRow> drow = ds.Tables[0].AsEnumerable(); if (drow.Count() > 0) { string sRepeat = string.Join("\n", drow.Select( x => x["applier"].ToString() + " From : " + x["starttime"].ToString() + " To :" + x["endtime"].ToString()).ToArray()); throw new Exception("重覆預約 \n" + sRepeat); } IEnumerable <DataRow> Srow = ds.Tables[1].AsEnumerable(); ds.Dispose(); if (Srow.Count() > 0) { string sRepeat = string.Join("\n", Srow.Select( x => Enum.GetName(typeof(DayOfWeek), int.Parse(x["dateday"].ToString())) + " : " + x["starttime"].ToString() + " To :" + x["endtime"].ToString() ).ToArray()); throw new Exception("此會議室已被系統預約 時間 \n" + sRepeat); } recordobj.RECORDID = Guid.NewGuid().ToString(); recordobj.BOOKTYPE = "USER"; recordobj.APPLIER = vUserInfo.CName; recordobj.APPLIEREXT = vUserInfo.Ext; recordobj.APPLIERID = vUserInfo.UserId; recordobj.UPDATET = "sysdate"; ws.BookingEvent(new [] { recordobj }); } catch (Exception ex) { ret = ex.Message; } //檢查預約事件 格式是否異常 return(ret); }