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);
}