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