public TRoomInfo GetRoomById(int roomid)
{
TRoomInfo room = null;
try
{
string sql = string.Format("SELECT " +
" id,user_name,date," +
"start_time,end_time," +
"meeting_subject," +
"meeting_title, meeting_remark," +
"meeting_attendee " +
"FROM " +
"T_room " +
"WHERE " +
"id={0}", roomid);
SqlDataReader dr = opt.ExecReader(sql);
if (dr.Read())
{
room = opt.SetRoom(dr);
}
dr.Close();
opt.CloseConn();
}
catch (Exception)
{
throw;
}
return(room);
}
public List <TRoomInfo> GetRoomByDate(string date)
{
List <TRoomInfo> roomlist = new List <TRoomInfo>();
TRoomInfo room = null;
try
{
string sql = string.Format("SELECT " +
" id,user_name,date," +
"start_time,end_time," +
"meeting_subject," +
"meeting_title, meeting_remark," +
"meeting_attendee " +
"FROM " +
"T_room " +
"WHERE " +
"date='{0}'", date);
SqlDataReader dr = opt.ExecReader(sql);
while (dr.Read())
{
room = opt.SetRoom(dr);
roomlist.Add(room);
}
dr.Close();
opt.CloseConn();
}
catch (Exception)
{
throw;
}
return(roomlist);
}
public TRoomInfo GetRoomByMDSE(string id, string date, string times)
{
TRoomInfo room = new TRoomInfo();
string sql = string.Format("select * from t_room " +
"where " +
"meeting_remark = '{0}' and " +
"date = '{1}' and " +
"start_time <= '{2}' and " +
"'{2}'<=end_time" + " union " +
"select * from t_room " +
"where " +
"meeting_remark = '{0}' and " +
"date = '{1}' and " +
"start_time > '{2}' and " +
"'{2}'<=end_time"
,
id, date, times);
try
{
SqlDataReader dr = opt.ExecReader(sql);
if (dr.Read())
{
room = opt.SetRoom(dr);
}
dr.Close();
opt.CloseConn();
}
catch (Exception)
{
throw;
}
return(room);
}
public IActionResult GetRoomByMDSE(string id, string date, string times)
{
TRoomInfo room = null;
try
{
room = SQLServerDAL.TRoom.GetInstance().GetRoomByMDSE(id, date, times);
}
catch (Exception)
{
throw;
}
return(Json(room));
}
public int SaveRoom(TRoomInfo room)
{
int i = 0;
try
{
string sql = "";
if (room.Id > 0)
{
sql = string.Format("UPDATE " +
"t_room " +
"SET " +
"user_name='{0}', " +
"Date='{1}', " +
"start_time='{2}', " +
"end_time='{3}', " +
"meeting_subject='{4}', " +
"meeting_title='{5}', " +
"meeting_remark='{6}', " +
"meeting_attendee='{7}' " +
"WHERE id='{8}'",
room.User_Name, room.Date, room.Start_Time,
room.End_Time, room.Meeting_Subject,
room.Meeting_Title, room.Meeting_Remark,
room.Meeting_Attendee, room.Id);
}
else
{
sql = "INSERT INTO " +
"t_room " +
"( user_name, date, start_time, end_time, meeting_subject, " +
"meeting_title, meeting_remark, meeting_attendee )" +
"VALUES ('" +
room.User_Name + "','" +
room.Date + "','" +
room.Start_Time + "','" +
room.End_Time + "','" +
room.Meeting_Subject + "','" +
room.Meeting_Title + "','" +
room.Meeting_Remark + "','" +
room.Meeting_Attendee + "')";
}
i = opt.ExecNoQuery(sql);
}
catch (Exception)
{
throw;
}
return(i);
}
public TRoomInfo SetRoom(SqlDataReader dr)
{
TRoomInfo room = new TRoomInfo();
room.Id = int.Parse(dr["Id"].ToString());
room.User_Name = dr["User_Name"].ToString();
room.Date = dr["Date"].ToString();
room.Start_Time = dr["Start_Time"].ToString();
room.End_Time = dr["End_Time"].ToString();
room.Meeting_Subject = dr["Meeting_Subject"].ToString();
room.Meeting_Title = dr["Meeting_Title"].ToString();
room.Meeting_Remark = dr["Meeting_Remark"].ToString();
room.Meeting_Attendee = dr["Meeting_Attendee"].ToString();
return(room);
}
public IActionResult AddRoomService(string jsonString)
{
TRoomInfo room = JsonConvert.DeserializeObject <TRoomInfo>(jsonString);
try
{
if (SQLServerDAL.TRoom.GetInstance().SaveRoom(room) > 0)
{
return(Json("ok"));
}
}
catch (Exception)
{
throw;
}
return(Json("msg:error"));
}
public IActionResult GetRoomByIdService(int roomid)
{
TRoomInfo room = null;
if (roomid <= 0)
{
return(Json("error"));
}
try
{
room = SQLServerDAL.TRoom.GetInstance().GetRoomById(roomid);
}
catch (Exception)
{
throw;
}
return(Json(room));
}
