/// <summary> /// 获取学院下全部学生 /// </summary> /// <param name="ArchitectureId"></param> /// <returns></returns> public List <Model.T_Base_User> GetAllUser(int ArchitectureId) { List <Model.T_Base_User> list = new List <Model.T_Base_User>(); SqlConfig config = new SqlConfig(); SqlCommand cmd = config.getSqlCommand(); cmd.CommandText = "select * from V_User_MajorClass_Architecture where ArchitectureId = " + ArchitectureId + " order by MajorClassName"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { 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_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.MajorClassName = Convert.ToString(reader["MajorClassName"]); Model.T_Base_Architecture architecture = new Model.T_Base_Architecture(); architecture.ArchitectureName = Convert.ToString(reader["ArchitectureName"]); majorClass.Architecture = architecture; user.MajorClass = majorClass; list.Add(user); } reader.Close(); config.Close(); return(list); }
/// <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="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="AddMajorClassName"></param> /// <param name="AddArchitectureId"></param> /// <returns></returns> public JsonResult AddSaveMajorClass(string AddMajorClassName, int AddArchitectureId) { Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.MajorClassName = AddMajorClassName; majorClass.ArchitectureId = AddArchitectureId; int result = new BLL.T_Base_MajorClass().AddSaveMajorClass(majorClass); if (result == 1) { return(Json("添加成功")); } else { return(Json("添加失败")); } }
/// <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="AddMajorClassName"></param> /// <param name="AddPeopleNum"></param> /// <param name="AddArchitectureId"></param> /// <returns></returns> public JsonResult EditSaveMajorClass(int EditId, string EditMajorClassName, int EditArchitectureId) { Model.T_Base_MajorClass majorClass = new Model.T_Base_MajorClass(); majorClass.Id = EditId; majorClass.MajorClassName = EditMajorClassName; majorClass.ArchitectureId = EditArchitectureId; majorClass.ArchitectureId = EditArchitectureId; int result = new BLL.T_Base_MajorClass().EditSaveMajorClass(majorClass); if (result == 1) { return(Json("修改成功")); } else { return(Json("修改失败")); } }
/// <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); }
/// <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="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="Lecture"></param> /// <returns></returns> public int EditSaveMajorClass(Model.T_Base_MajorClass MajorClass) { return(new DAL.T_Base_MajorClass().EditSaveMajorClass(MajorClass)); }
/// <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); }
/// <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> /// 获取指定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); }
public JsonResult SaveExcelUser() { var file = Request.Files["AddFile"]; if (file.ContentLength == 0) { return(Json("请添加文件")); } string path = Server.MapPath("\\upLoad\\"); string[] strfile = file.FileName.Split('.'); string type = "." + strfile[strfile.Length - 1]; string fileName = ""; for (int i = 0; i < strfile.Length - 1; i++) { fileName += strfile[i]; } fileName += DateTime.Now.ToString("yyyyMMddHHmmssffff") + type; file.SaveAs(path + fileName); FileStream fs = new FileStream(path + fileName, FileMode.Open); if (fs.Length == 0) { return(Json("文件不存在")); } HSSFWorkbook wk = new HSSFWorkbook(fs); ISheet sheet = wk.GetSheetAt(0); List <Model.T_Base_User> list = new List <Model.T_Base_User>(); for (int i = 1; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { for (int j = 0; j < row.LastCellNum; j++) { ICell cell = row.GetCell(j); if (cell == null) { return(Json("第" + (i + 1) + "行" + (j + 1) + "列单元格内缺失内容")); } } 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.Num = row.GetCell(0).ToString(); user.Name = row.GetCell(1).ToString(); if (row.GetCell(2).ToString() == "女") { user.Sex = 0; } else if (row.GetCell(2).ToString() == "男") { user.Sex = 1; } majorClass.MajorClassName = row.GetCell(3).ToString(); architecture.ArchitectureName = row.GetCell(4).ToString(); user.PhoneNum = row.GetCell(5).ToString(); majorClass.Architecture = architecture; user.MajorClass = majorClass; list.Add(user); } } int result = new BLL.T_Base_User().SaveExcelUser(list); return(Json("")); }