/// <summary> /// 保存添加的专业班级信息 /// </summary> /// <param name="majorClass"></param> /// <returns></returns> public int AddSaveMajorClass(Model.T_Base_MajorClass MajorClass) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "insert into T_Base_MajorClass values ('" + MajorClass.MajorClassName + "'," + MajorClass.ArchitectureId + ")"; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 保存修改后建筑的信息 /// </summary> /// <param name="Architecture"></param> /// <returns></returns> public int EditSaveArchitecture(Model.T_Base_Architecture Architecture) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "update T_Base_Architecture set ArchitectureName = '" + Architecture.ArchitectureName + "',IsCollege = " + Architecture.IsCollege + " where Id = " + Architecture.Id; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 保存添加的用户信息 /// </summary> /// <param name="User"></param> /// <returns></returns> public int AddSaveUser(Model.T_Base_User User) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "insert into T_Base_User values('" + User.Num + "','" + User.Name + "'," + User.Sex + "," + User.MajorClassId + ",'" + User.PhoneNum + "','" + User.Num + "',0," + User.Role + ")"; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 删除场地 /// </summary> /// <param name="Ids"></param> /// <returns></returns> public int Delete(string Ids) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "delete from T_Base_Place where ArchitectureId in (" + Ids + ")"; cmd.ExecuteNonQuery(); cmd.CommandText = "delete from T_Base_Architecture where id in (" + Ids + ")"; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 保存修改后的场地信息 /// </summary> /// <param name="Place"></param> /// <returns></returns> public int EditSavePlace(Model.T_Base_Place Place) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "update T_Base_Place set PlaceName = '" + Place.PlaceName + "',PeopleNum = " + Place.PeopleNum + ",ArchitectureId = " + Place.ArchitectureId + " where Id = " + Place.Id; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 保存修改后的信息 /// </summary> /// <param name="MajorClass"></param> /// <returns></returns> public int EditSaveMajorClass(Model.T_Base_MajorClass MajorClass) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "update T_Base_MajorClass set majorClassName = '" + MajorClass.MajorClassName + "',ArchitectureId = " + MajorClass.ArchitectureId + " where Id = " + MajorClass.Id; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// /// </summary> /// <param name="Resource"></param> /// <returns></returns> public int AddSaveResource(Model.T_Base_Resource Resource) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "insert into T_Base_Resource values('" + Resource.Num + "'," + Resource.LectureId + ",'" + Resource.Content + "','" + Resource.FilePosition1 + "','" + Resource.FilePosition2 + "','" + Resource.FilePosition3 + "','" + DateTime.Now + "',0,0)"; int result = cmd.ExecuteNonQuery(); config.Close(); return(result); }
/// <summary> /// 根据Id获取指定建筑信息 /// </summary> /// <param name="Id"></param> /// <returns></returns> public Model.T_Base_Architecture GetArchitecture(int Id) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_Architecture where Id = " + Id; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["Id"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); architecture.IsCollege = Convert.ToInt32(reader["IsCollege"]); reader.Close(); config.Close(); return(architecture); }
/// <summary> /// 保存修改后的信息 /// </summary> /// <param name="Lecture"></param> /// <returns></returns> public int EditSaveLecture(string EditNum, int EditApplyId, Model.T_Base_Lecture Lecture, int EditPlaceId) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); int result = -1; try { cmd.Transaction = config.getSqlConnection().BeginTransaction(); if (CheckDateTime(Lecture.Id, Lecture.LectureTime, Lecture.Span, EditPlaceId)) { //修改地点信息 cmd.CommandText = "update T_Base_Lecture set " + "Subject = '" + Lecture.Subject + "', Summary = '" + Lecture.Summary + "',State = 3,DeathLine = '" + Lecture.DeathLine + "',LectureTime = '" + Lecture.LectureTime + "',Span = " + Lecture.Span + ",ExpectPeople = " + Lecture.ExpectPeople + ",Score = " + Lecture.Score + ",AlertFlag = 1 where Id = " + Lecture.Id; int result1 = cmd.ExecuteNonQuery(); cmd.CommandText = "update T_Base_Apply set " + "PlaceId = " + EditPlaceId + "where Id = " + EditApplyId; int result2 = cmd.ExecuteNonQuery(); if (result1 == -1 && result2 == -1) { cmd.Transaction.Rollback(); result = -3; //没有任何更新变化 } else { cmd.Transaction.Commit(); result = 1; } } else { cmd.Transaction.Rollback(); return(-2); } } catch { result = -1; } config.Close(); return(result); }
public Model.T_Base_Admin GetAdmin(string Admin) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); Model.T_Base_Admin admin = new Model.T_Base_Admin(); cmd.CommandText = "select * from T_Base_Admin where Admin = " + Admin; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); admin.Id = Convert.ToInt32(reader["Id"]); admin.Admin = Convert.ToString(reader["Admin"]); admin.PassWord = Convert.ToString(reader["PassWord"]); admin.Role = Convert.ToInt32(reader["Role"]); config.Close(); return(admin); }
/// <summary> /// 按查询信息给出分页学生信息 /// </summary> /// <param name="PageSize"></param> /// <param name="PageNumber"></param> /// <param name="StudentNum"></param> /// <param name="StudentName"></param> /// <param name="ClassName"></param> /// <returns></returns> public List <Model.T_Base_User> GetAllUser(int PageSize, int PageNumber, string Num, string Name, string MajorClassName) { Num = "'%" + Num + "%'"; Name = "'%" + Name + "%'"; MajorClassName = "'%" + MajorClassName + "%'"; SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select top " + PageSize + " * from [V_User_MajorClass_Architecture] where Id not in (select top " + (PageNumber - 1) * PageSize + " Id from [V_User_MajorClass_Architecture] where MajorClassName like " + MajorClassName + " and Num like " + Num + " and Name like " + Name + ")and MajorClassName like " + MajorClassName + " and Num like " + Num + " and Name like " + Name; SqlDataReader reader = cmd.ExecuteReader(); List <Model.T_Base_User> list = new List <Model.T_Base_User>(); while (reader.Read()) { Model.T_Base_User user = new Model.T_Base_User(); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); user.Id = Convert.ToInt32(reader["Id"]); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt32(reader["Sex"]); user.MajorClassId = Convert.ToInt32(reader["MajorClassId"]); majorClass.Id = Convert.ToInt32(reader["MajorClassId"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); majorClass.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); majorClass.Architecture = architecture; user.MajorClass = majorClass; user.Role = Convert.ToInt16(reader["Role"]); user.PhoneNum = Convert.ToString(reader["PhoneNum"]); list.Add(user); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 保存添加的讲座信息 /// </summary> /// <param name="majorClass"></param> /// <returns></returns> public int AddSaveLecture(string AddNum, Model.T_Base_Lecture Lecture, int AddPlaceId) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); int result = -1; try { cmd.Transaction = config.getSqlConnection().BeginTransaction(); if (CheckDateTime(Lecture.Id, Lecture.LectureTime, Lecture.Span, AddPlaceId)) { cmd.CommandText = "insert into T_Base_Lecture " + "values('" + Lecture.Subject + "','" + Lecture.Summary + "',0,-1,'" + Lecture.DeathLine + "','" + Lecture.LectureTime + "'," + Lecture.Span + "," + Lecture.ExpectPeople + ",0," + Lecture.Score + ",0)"; try { cmd.ExecuteNonQuery(); } catch { cmd.Transaction.Rollback(); return(-3); //请确保按照格式填入全部信息 } cmd.CommandText = "select top 1 Id from T_Base_Lecture order by Id desc"; result = (int)cmd.ExecuteScalar(); cmd.CommandText = "insert into T_Base_Apply values('" + AddNum + "'," + result + "," + AddPlaceId + ",'" + DateTime.Now + "','')"; cmd.ExecuteNonQuery(); cmd.Transaction.Commit(); } else { cmd.Transaction.Rollback(); return(-2); //时间冲突 } } catch { result = -1; } config.Close(); return(result); }
/// <summary> /// 获取指定Id的专业班级信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public Model.T_Base_MajorClass GetMajorClass(int id) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_MajorClass where Id = " + id; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["Id"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); majorClass.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); reader.Close(); config.Close(); return(majorClass); }
public int SaveExcelUser(List <Model.T_Base_User> User) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.Transaction = config.getSqlConnection().BeginTransaction(); int result = 0; //添加的学生数 for (int i = 0; i < User.Count; i++) { //判断该生是否存在 cmd.CommandText = "select count(1) from T_Base_User where Num='" + User[i].Num + "'"; if ((int)cmd.ExecuteScalar() == 1) { continue; } //判断学院是否存在 cmd.CommandText = "select count(1) from T_Base_Architecture where ArchitectureName='" + User[i].MajorClass.Architecture.ArchitectureName + "'"; if ((int)cmd.ExecuteScalar() == 0) { cmd.CommandText = "insert into T_Base_Architecture values('" + User[i].MajorClass.Architecture.ArchitectureName + "',1)"; cmd.ExecuteNonQuery(); } cmd.CommandText = "select Id from T_Base_Architecture where ArchitectureName='" + User[i].MajorClass.Architecture.ArchitectureName + "'"; int architectureId = (int)cmd.ExecuteScalar(); //判断班级是否存在 cmd.CommandText = "select count(1) from T_Base_MajorClass where MajorClassName='" + User[i].MajorClass.MajorClassName + "'"; if ((int)cmd.ExecuteScalar() == 0) { cmd.CommandText = "insert into T_Base_MajorClass values('" + User[i].MajorClass.MajorClassName + "'," + architectureId + ")"; cmd.ExecuteNonQuery(); } cmd.CommandText = "select Id from T_Base_MajorClass where MajorClassName='" + User[i].MajorClass.MajorClassName + "'"; int majorClassId = (int)cmd.ExecuteScalar(); //添加用户 cmd.CommandText = "insert into T_Base_User values('" + User[i].Num + "','" + User[i].Name + "'," + User[i].Sex + "," + majorClassId + ",'" + User[i].PhoneNum + "','" + User[i].Num + "',0,0,3)"; result += cmd.ExecuteNonQuery(); } cmd.Transaction.Commit(); config.Close(); return(result); }
public List <Model.T_Base_Resource> GetAllResource(int LectureId) { List <Model.T_Base_Resource> list = new List <Model.T_Base_Resource>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_Resource where LectureId = " + LectureId; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Resource resource = new Model.T_Base_Resource(); resource.Id = Convert.ToInt32(reader["Id"]); resource.Num = Convert.ToString(reader["Num"]); resource.LectureId = Convert.ToInt32(reader["LectureId"]); resource.Content = Convert.ToString(reader["Content"]); if (reader["FilePosition1"].Equals(DBNull.Value)) { resource.FilePosition1 = Convert.ToString(DBNull.Value); } else { resource.FilePosition1 = Convert.ToString(reader["FilePosition1"]); } if (reader["ResourceDate"].Equals(DBNull.Value)) { resource.ResourceDate = Convert.ToDateTime(null); } else { resource.ResourceDate = Convert.ToDateTime(reader["ResourceDate"]); } resource.ReviewFlag = Convert.ToInt16(reader["ReviewFlag"]); list.Add(resource); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 查询建筑内可举办讲座地点(Id,地点名称,容纳人数) /// </summary> /// <param name="ArchitectureId"></param> /// <returns></returns> public List <Model.T_Base_Place> GetAllPlace(int ArchitectureId) { List <Model.T_Base_Place> list = new List <Model.T_Base_Place>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_Place where ArchitectureId = " + ArchitectureId; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Place place = new Model.T_Base_Place(); place.Id = Convert.ToInt32(reader["Id"]); place.PlaceName = Convert.ToString(reader["PlaceName"]); place.PeopleNum = Convert.ToInt32(reader["PeopleNum"]); list.Add(place); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 获取指定Id的地点信息 /// </summary> /// <param name="Id"></param> /// <returns></returns> public Model.T_Base_Place GetPlace(int Id) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from V_Place_Architecture where Id = " + Id; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); Model.T_Base_Place place = new Model.T_Base_Place(); place.Id = Convert.ToInt32(reader["Id"]); place.PlaceName = Convert.ToString(reader["PlaceName"]); place.PeopleNum = Convert.ToInt32(reader["PeopleNum"]); place.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); place.Architecture = architecture; config.Close(); return(place); }
/// <summary> /// 根据指定建筑Id获取学院专业 /// </summary> /// <returns></returns> public List <Model.T_Base_MajorClass> GetMajorClass(int ArchitectureId) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_MajorClass where ArchitectureId = " + ArchitectureId; SqlDataReader reader = cmd.ExecuteReader(); List <Model.T_Base_MajorClass> list = new List <Model.T_Base_MajorClass>(); while (reader.Read()) { Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["Id"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); majorClass.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); list.Add(majorClass); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 获取全部建筑 /// </summary> /// <param name="PageSize"></param> /// <param name="PageNumber"></param> /// <returns></returns> public List <Model.T_Base_Architecture> GetAllArchitecture() { List <Model.T_Base_Architecture> list = new List <Model.T_Base_Architecture>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from T_Base_Architecture"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["Id"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); architecture.IsCollege = Convert.ToInt32(reader["IsCollege"]); list.Add(architecture); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 查询出席的讲座 /// </summary> /// <param name="Num"></param> /// <returns></returns> public List <Model.T_Base_Statistic> GetAllAttendance(string Num, int State) { List <Model.T_Base_Statistic> list = new List <Model.T_Base_Statistic>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); if (State == 0) { cmd.CommandText = "select * from V_Lecture_Statistic where Num = '" + Num + "'"; } else if (State == 1) { cmd.CommandText = "select * from V_Lecture_Statistic where Num = '" + Num + "' and EndTime <> '1900/1/1 0:00:00'"; } SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Statistic statistic = new Model.T_Base_Statistic(); Model.T_Base_Lecture lecture = new Model.T_Base_Lecture(); lecture.Id = Convert.ToInt32(reader["Id"]); lecture.Subject = Convert.ToString(reader["Subject"]); lecture.Score = Convert.ToDouble(reader["Score"]); lecture.RealPeople = Convert.ToInt32(reader["RealPeople"]); lecture.LectureTime = Convert.ToDateTime(reader["LectureTime"]); statistic.Id = Convert.ToInt32(reader["StatisticId"]); statistic.Num = Convert.ToString(reader["Num"]); statistic.StartTime = Convert.ToDateTime(reader["StartTime"]); statistic.EndTime = Convert.ToDateTime(reader["EndTime"]); statistic.Lecture = lecture; list.Add(statistic); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 报名功能显示(报名,以报名,截止报名 /// </summary> /// <param name="Num"></param> /// <param name="LectureId"></param> /// <returns></returns> /// 0报名 1以报名 2人满未处理 3时间截止 public int OrderSelect(string Num, int LectureId) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); int result = -1; cmd.CommandText = "select DeathLine from T_Base_Lecture where Id = " + LectureId; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); DateTime DeathLine = Convert.ToDateTime(reader["DeathLine"]); reader.Close(); if (DeathLine < DateTime.Now) { config.Close(); return(3); //报名已截止 } cmd.CommandText = "select count(1) from T_Base_Order where Num = " + Num + " and LectureId = " + LectureId; result = (int)cmd.ExecuteScalar(); if (result == 1) { cmd.CommandText = "select Result from T_Base_Order where Num = " + Num + " and LectureId = " + LectureId; result = (int)cmd.ExecuteScalar(); if (result == 2) { return(2); //报名成功 } else if (result == 3) { return(1); //等后续通知 } } config.Close(); return(result); }
/// <summary> /// 时间冲突检测 /// </summary> /// <param name="StartTime"></param> /// <param name="Span"></param> /// <param name="PlaceId"></param> /// <returns></returns> public Boolean CheckDateTime(int LectureId, DateTime StartTime, double Span, int PlaceId) { DateTime EndTime = StartTime.AddHours(Span); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); if (LectureId == 0) { cmd.CommandText = "select * from V_User_Lecture_Place where PlaceId = " + PlaceId + " and '" + StartTime.ToString("yyyy/MM/dd") + "' = FORMAT(LectureTime,'yyyy/MM/dd') and State = 1"; } else { cmd.CommandText = "select * from V_User_Lecture_Place where PlaceId = " + PlaceId + " and '" + StartTime.ToString("yyyy/MM/dd") + "' = FORMAT(LectureTime,'yyyy/MM/dd') and State = 1 and LectureId <> " + LectureId; } SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //以添加的场地时间 DateTime checkStart = Convert.ToDateTime(reader["LectureTime"]); double checkSpan = Convert.ToDouble(reader["Span"]); DateTime checkEnd = checkStart.AddHours(checkSpan); if ((checkStart < StartTime && StartTime <= checkEnd) || //起始时间位于上一场之中 (StartTime <= checkEnd && checkEnd <= EndTime) || //待加入时间中以有一场 (checkStart < EndTime && EndTime <= checkEnd)) { config.Close(); return(false); } } config.Close(); return(true); }
public int Check(string Num, string Password) { int result = -1; SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select count(1) from T_Base_User where Num = '" + Num + "' and Password = '******'"; result = (int)cmd.ExecuteScalar(); if (result == 1) { config.Close(); return(-2); //普通学生 } cmd.CommandText = "select count(1) from T_Base_Admin where Admin = '" + Num + "' and Password = '******'"; result = (int)cmd.ExecuteScalar(); if (result == 1) { config.Close(); return(-3); //管理员 } return(result); }
/// <summary> /// 查询参与讲座人员信息 /// </summary> /// <param name="LectureId"></param> /// <returns></returns> public List <Model.T_Base_Statistic> SavePeopleExcel(int LectureId) { List <Model.T_Base_Statistic> list = new List <Model.T_Base_Statistic>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from V_Excel where LectureId = " + LectureId; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Statistic statistic = new Model.T_Base_Statistic(); Model.T_Base_User user = new Model.T_Base_User(); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt16(reader["Sex"]); user.PhoneNum = Convert.ToString(reader["PhoneNum"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Architecture = architecture; majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); user.MajorClass = majorClass; statistic.StartTime = Convert.ToDateTime(reader["StartTime"]); statistic.EndTime = Convert.ToDateTime(reader["EndTime"]); statistic.User = user; list.Add(statistic); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 获取指定Num的用户信息 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public Model.T_Base_User GetUser(string Num) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from V_User_MajorClass_Architecture where Num = '" + Num + "'"; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); //所属学院信息 Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); //所属专业班级信息 Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["MajorClassId"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); majorClass.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); majorClass.Architecture = architecture; //用户信息 Model.T_Base_User user = new Model.T_Base_User(); user.Id = Convert.ToInt32(reader["Id"]); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt32(reader["Sex"]); user.MajorClassId = Convert.ToInt32(reader["MajorClassId"]); user.PhoneNum = Convert.ToString(reader["PhoneNum"]); user.PassWord = Convert.ToString(reader["PassWord"]); user.MajorClass = majorClass; user.Role = Convert.ToInt32(reader["Role"]); reader.Close(); config.Close(); return(user); }
/// <summary> /// 签到 /// </summary> /// <param name="Num"></param> /// <param name="LectureId"></param> /// <param name="Ip"></param> /// <returns></returns> public int AddSaveStatistic(string Num, int LectureId, string Ip) { int result = -1; SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select LectureTime,Span from T_Base_Lecture where Id = " + LectureId; SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); DateTime LectureTime = Convert.ToDateTime(reader["LectureTime"]); double Span = Convert.ToDouble(reader["Span"]); reader.Close(); if (DateTime.Now >= LectureTime.AddMinutes(-30) && DateTime.Now <= LectureTime.AddMinutes(5)) { //签到,提前三十分钟到开始五分钟区间内可签到 cmd.CommandText = "select count(1) from T_Base_Statistic where LectureId = " + LectureId + " and Ip = '" + Ip + "'"; result = (int)cmd.ExecuteScalar(); if (result == 0) { cmd.CommandText = "insert into T_Base_Statistic values('" + Num + "'," + LectureId + ",'" + DateTime.Now + "','" + DBNull.Value + "','" + Ip + "')"; try { result = cmd.ExecuteNonQuery(); config.Close(); return(1); //签到成功 } catch { return(-4); //学号输入不正确或不存在该学号 } } else { config.Close(); return(-3); //存在代签情况 } } else if (DateTime.Now >= LectureTime.AddMinutes(Span * 60 * 0.8) && DateTime.Now <= LectureTime.AddMinutes(Span * 60 + 30)) { //签退 cmd.CommandText = "select count(1) from T_Base_Statistic where LectureId = " + LectureId + " and Ip = '" + Ip + "'"; result = (int)cmd.ExecuteScalar(); if (result <= 1) { cmd.CommandText = "select count(1) from T_Base_Statistic where Num = '" + Num + "' and LectureId = " + LectureId; result = (int)cmd.ExecuteScalar(); if (result < 1) { config.Close(); return(-2); //未签到 } cmd.CommandText = "update T_Base_Statistic set EndTime = '" + DateTime.Now + "' where Num = '" + Num + "' and LectureId = " + LectureId; result = cmd.ExecuteNonQuery(); config.Close(); return(2); //签退成功 } else { config.Close(); return(-3); //存在代签情况 } } config.Close(); return(-1); //签到失败 }
/// <summary> /// 审核处理 /// </summary> /// <param name="ApplyId"></param> /// <param name="LectureId"></param> /// <param name="State"></param> /// <param name="Num"></param> /// <param name="Reason"></param> /// <returns></returns> public int Review_Updata(int ApplyId, int LectureId, int State, string Num, string ReviewNum, string Reason = "") { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); SqlDataReader reader; cmd.CommandText = "select State from T_Base_Lecture Where Id = " + LectureId; int oldState = (int)cmd.ExecuteScalar(); cmd.CommandText = "update T_Base_Lecture set State = " + State + " where T_Base_Lecture.Id = " + "(select LectureId from T_Base_Apply where T_Base_Apply.Id = " + ApplyId + ")"; int result = cmd.ExecuteNonQuery(); if (State == 2) { cmd.CommandText = "insert into T_Base_Audit values('" + Num + "'," + LectureId + ",'" + Reason + "','" + DateTime.Now + "','" + ReviewNum + "')"; result = cmd.ExecuteNonQuery(); } if (State == 1) { //更新二维码 QRCodeSave("" + LectureId); cmd.CommandText = "update T_Base_Lecture set QRCode = " + LectureId + " where Id = " + LectureId; cmd.ExecuteNonQuery(); //更新处理人账号 cmd.CommandText = "update T_Base_Apply set ReviewNum = '" + ReviewNum + "' where Id=" + ApplyId; cmd.ExecuteNonQuery(); } if (oldState == 3 && State == 1) { //通知报名人员 cmd.CommandText = "select Subject,ExpectPeople,RealPeople from T_Base_Lecture where Id = " + LectureId; reader = cmd.ExecuteReader(); reader.Read(); string subject = Convert.ToString(reader["Subject"]); int expectPeople = Convert.ToInt16(reader["ExpectPeople"]); int realPeople = Convert.ToInt16(reader["RealPeople"]); reader.Close(); //获取报名参加人员 cmd.CommandText = "select Num from T_Base_Order where LectureId = " + LectureId; reader = cmd.ExecuteReader(); List <Model.T_Base_Order> listOrder = new List <Model.T_Base_Order>(); while (reader.Read()) { Model.T_Base_Order order = new Model.T_Base_Order(); Model.T_Base_User user = new DAL.T_Base_User().GetUser(Convert.ToString(reader["Num"])); order.User = user; listOrder.Add(order); } reader.Close(); //发送消息 for (int i = 0; i < listOrder.Count; i++) { MailMessage mailMessage = new MailMessage(); mailMessage.From = new MailAddress("*****@*****.**"); mailMessage.To.Add(new MailAddress("*****@*****.**")); //mailMessage.To.Add(listOrder[i].Num+"@stu.wzu.edu.cn"); mailMessage.Subject = subject + "地点变更通知"; mailMessage.Body = "您所报名参加的讲座" + subject + "信息发生变更,请注意查看"; SmtpClient client = new SmtpClient(); client.Host = "smtp.wzu.edu.cn"; client.EnableSsl = false; client.UseDefaultCredentials = false; client.Credentials = new NetworkCredential("*****@*****.**", "b5ZF36PPMMmCF883"); client.Send(mailMessage); } } return(result); }
/// <summary> /// 获取指定id的讲座 /// </summary> /// <param name="Id"></param> /// <returns></returns> public List <Model.T_Base_Apply> GetLecture(int LectureId, int State) { List <Model.T_Base_Apply> list = new List <Model.T_Base_Apply>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); if (State == 1) { cmd.CommandText = "select * from V_User_Lecture_Place where LectureId = " + LectureId; } else if (State == 2) { cmd.CommandText = "select * from V_User_Lecture_Place_Audit where LectureId = " + LectureId; } SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Model.T_Base_Apply apply = new Model.T_Base_Apply(); apply.Id = Convert.ToInt32(reader["Id"]); apply.Num = Convert.ToString(reader["Num"]); apply.LectureId = Convert.ToInt32(reader["LectureId"]); apply.PlaceId = Convert.ToInt32(reader["PlaceId"]); apply.ApplyTime = Convert.ToDateTime(reader["ApplyTime"]); Model.T_Base_User user = new Model.T_Base_User(); user.Id = Convert.ToInt32(reader["Id"]); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt32(reader["Sex"]); user.MajorClassId = Convert.ToInt32(reader["MajorClassId"]); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["MajorClassId"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); user.MajorClass = majorClass; user.PhoneNum = Convert.ToString(reader["PhoneNum"]); apply.User = user; Model.T_Base_Lecture lecture = new Model.T_Base_Lecture(); lecture.Id = Convert.ToInt32(reader["LectureId"]); lecture.Subject = Convert.ToString(reader["Subject"]); lecture.Summary = Convert.ToString(reader["Summary"]); lecture.State = Convert.ToInt32(reader["State"]); lecture.QRCode = Convert.ToString(reader["QRCode"]); lecture.DeathLine = Convert.ToDateTime(reader["DeathLine"]); lecture.LectureTime = Convert.ToDateTime(reader["LectureTime"]); lecture.Span = Convert.ToDouble(reader["Span"]); lecture.ExpectPeople = Convert.ToInt32(reader["ExpectPeople"]); lecture.RealPeople = Convert.ToInt32(reader["RealPeople"]); lecture.Score = Convert.ToDouble(reader["Score"]); if (State == 2) { if (reader["Reason"].Equals(DBNull.Value)) { lecture.Reason = ""; } else { lecture.Reason = Convert.ToString(reader["Reason"]); } } apply.Lecture = lecture; Model.T_Base_Place place = new Model.T_Base_Place(); place.Id = Convert.ToInt32(reader["PlaceId"]); place.PlaceName = Convert.ToString(reader["PlaceName"]); place.PeopleNum = Convert.ToInt32(reader["PeopleNum"]); place.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); place.Architecture = architecture; apply.Place = place; list.Add(apply); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 获取全部讲座信息 /// </summary> /// <param name="ArchitectureId"></param> /// <returns></returns> public List <Model.T_Base_Apply> GetAllLecture(string ParamLecture, int PageSize, int PageNumber, string State) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select top " + PageSize + " * from V_User_Lecture_Place" + " where Id not in (select top " + (PageSize * (PageNumber - 1)) + " Id from V_User_Lecture_Place where (V_User_Lecture_Place.Subject" + " like '%" + ParamLecture + "%' or V_User_Lecture_Place.Name like '%" + ParamLecture + "%') and V_User_Lecture_Place.State " + State + ") and (V_User_Lecture_Place.Subject" + " like '%" + ParamLecture + "%' or V_User_Lecture_Place.Name like '%" + ParamLecture + "%') and State " + State; SqlDataReader reader = cmd.ExecuteReader(); List <Model.T_Base_Apply> list = new List <Model.T_Base_Apply>(); while (reader.Read()) { Model.T_Base_Apply apply = new Model.T_Base_Apply(); apply.Id = Convert.ToInt32(reader["Id"]); apply.Num = Convert.ToString(reader["Num"]); apply.LectureId = Convert.ToInt32(reader["LectureId"]); apply.PlaceId = Convert.ToInt32(reader["PlaceId"]); apply.ApplyTime = Convert.ToDateTime(reader["ApplyTime"]); Model.T_Base_User user = new Model.T_Base_User(); user.Id = Convert.ToInt32(reader["UserId"]); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt32(reader["Sex"]); user.MajorClassId = Convert.ToInt32(reader["MajorClassId"]); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["MajorClassId"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); user.MajorClass = majorClass; user.PhoneNum = Convert.ToString(reader["PhoneNum"]); apply.User = user; Model.T_Base_Lecture lecture = new Model.T_Base_Lecture(); lecture.Id = Convert.ToInt32(reader["LectureId"]); lecture.Subject = Convert.ToString(reader["Subject"]); lecture.Summary = Convert.ToString(reader["Summary"]); lecture.State = Convert.ToInt32(reader["State"]); lecture.QRCode = Convert.ToString(reader["QRCode"]); lecture.DeathLine = Convert.ToDateTime(reader["DeathLine"]); lecture.LectureTime = Convert.ToDateTime(reader["LectureTime"]); lecture.Span = Convert.ToDouble(reader["Span"]); lecture.ExpectPeople = Convert.ToInt32(reader["ExpectPeople"]); lecture.RealPeople = Convert.ToInt32(reader["RealPeople"]); lecture.Score = Convert.ToDouble(reader["Score"]); apply.Lecture = lecture; Model.T_Base_Place place = new Model.T_Base_Place(); place.Id = Convert.ToInt32(reader["PlaceId"]); place.PlaceName = Convert.ToString(reader["PlaceName"]); place.PeopleNum = Convert.ToInt32(reader["PeopleNum"]); place.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); place.Architecture = architecture; apply.Place = place; list.Add(apply); } reader.Close(); config.Close(); return(list); }
/// <summary> /// 获取个人所申请的全部讲座信息 /// </summary> /// <param name="Num"></param> /// <returns></returns> public List <Model.T_Base_Apply> GetPersonalAllLecture(string Num, int Role) { SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); if (Role == 1 || Role == 2) { cmd.CommandText = "select * from V_User_Lecture_Place order by Id desc"; } else if (Role == 3) { cmd.CommandText = "select * from V_User_Lecture_Place where Num = '" + Num + "' order by Id desc"; } SqlDataReader reader = cmd.ExecuteReader(); List <Model.T_Base_Apply> list = new List <Model.T_Base_Apply>(); while (reader.Read()) { Model.T_Base_Apply apply = new Model.T_Base_Apply(); apply.Id = Convert.ToInt32(reader["Id"]); apply.Num = Convert.ToString(reader["Num"]); apply.LectureId = Convert.ToInt32(reader["LectureId"]); apply.PlaceId = Convert.ToInt32(reader["PlaceId"]); apply.ApplyTime = Convert.ToDateTime(reader["ApplyTime"]); Model.T_Base_User user = new Model.T_Base_User(); user.Id = Convert.ToInt32(reader["Id"]); user.Num = Convert.ToString(reader["Num"]); user.Name = Convert.ToString(reader["Name"]); user.Sex = Convert.ToInt32(reader["Sex"]); user.MajorClassId = Convert.ToInt32(reader["MajorClassId"]); Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = Convert.ToInt32(reader["MajorClassId"]); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); user.MajorClass = majorClass; user.PhoneNum = Convert.ToString(reader["PhoneNum"]); apply.User = user; Model.T_Base_Lecture lecture = new Model.T_Base_Lecture(); lecture.Id = Convert.ToInt32(reader["LectureId"]); lecture.Subject = Convert.ToString(reader["Subject"]); lecture.Summary = Convert.ToString(reader["Summary"]); lecture.State = Convert.ToInt32(reader["State"]); lecture.QRCode = Convert.ToString(reader["QRCode"]); lecture.DeathLine = Convert.ToDateTime(reader["DeathLine"]); lecture.LectureTime = Convert.ToDateTime(reader["LectureTime"]); lecture.Span = Convert.ToDouble(reader["Span"]); lecture.ExpectPeople = Convert.ToInt32(reader["ExpectPeople"]); lecture.RealPeople = Convert.ToInt32(reader["RealPeople"]); lecture.Score = Convert.ToDouble(reader["Score"]); apply.Lecture = lecture; Model.T_Base_Place place = new Model.T_Base_Place(); place.Id = Convert.ToInt32(reader["PlaceId"]); place.PlaceName = Convert.ToString(reader["PlaceName"]); place.PeopleNum = Convert.ToInt32(reader["PeopleNum"]); place.ArchitectureId = Convert.ToInt32(reader["ArchitectureId"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.Id = Convert.ToInt32(reader["ArchitectureId"]); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); place.Architecture = architecture; apply.Place = place; list.Add(apply); } reader.Close(); config.Close(); return(list); }