/// <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(""));
}
