public ModelMR GetRoomInfoByID(string roomid)
{
APIHelper ws = new APIHelper();
ModelMR lis = ws.GetMeetingRoomByID(roomid);
return(lis);
}
public bool UpdateMRRoom(ModelMR mr)
{
clsSQLBuilder cls = new clsSQLBuilder();
cls.AddField("ACCESSRIGHT", clsSQLBuilder.FieldType.FD_STRING, mr.ACCESSRIGHT, true);
cls.AddField("CNPC", clsSQLBuilder.FieldType.FD_STRING, mr.CNPC, true);
cls.AddField("EQIPMENT", clsSQLBuilder.FieldType.FD_STRING, mr.EQIPMENT, true);
cls.AddField("EXT", clsSQLBuilder.FieldType.FD_STRING, mr.EXT, true);
// cls.AddField("FLOOR", clsSQLBuilder.FieldType.FD_STRING, mr.FLOOR, true);
cls.AddField("IMGPATH", clsSQLBuilder.FieldType.FD_STRING, mr.IMGPATH, true);
cls.AddField("IP", clsSQLBuilder.FieldType.FD_STRING, mr.IP, true);
cls.AddField("MAXP", clsSQLBuilder.FieldType.FD_NUMBER, mr.MAXP.ToString(), true);
cls.AddField("MINP", clsSQLBuilder.FieldType.FD_NUMBER, mr.MINP.ToString(), true);
cls.AddField("POSTPERIOD", clsSQLBuilder.FieldType.FD_NUMBER, mr.POSTPERIOD.ToString(), true);
cls.AddField("PLANT", clsSQLBuilder.FieldType.FD_STRING, mr.PLANT.ToString(), true);
cls.AddField("ROOMABBR", clsSQLBuilder.FieldType.FD_STRING, mr.ROOMABBR.ToString(), true);
cls.AddField("ROOMID", clsSQLBuilder.FieldType.FD_STRING, mr.ROOMID.ToString(), false);
cls.AddField("ROOMNAME", clsSQLBuilder.FieldType.FD_STRING, mr.ROOMNAME.ToString(), true);
cls.AddField("SITE", clsSQLBuilder.FieldType.FD_STRING, mr.SITE.ToString(), true);
cls.AddField("SITEID", clsSQLBuilder.FieldType.FD_STRING, GetSiteid(mr.SITE, mr.PLANT, mr.FLOOR), true);
string sqls = string.Format(@"MERGE INTO {0} USING DUAL
ON (ROOMID = '{1}')
WHEN MATCHED THEN
UPDATE SET {2}
WHEN NOT MATCHED THEN
INSERT {3}"
, "MBS_ROOM "
, mr.ROOMID
, cls.GetString_Update()
, cls.GetString_Insert()
);
DALService.ExecuteNonQuery(sqls);
return(true);
}
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);
}
