/// <summary>
/// 保存添加的讲座信息
/// </summary>
/// <param name="place"></param>
/// <returns></returns>
public int AddSaveLecture(string AddNum, Model.T_Base_Lecture Lecture, int AddPlaceId)
{
int result = 1;
result = new DAL.T_Base_Lecture().AddSaveLecture(AddNum, Lecture, AddPlaceId);
return(result);
}
/// <summary>
/// 保存添加的讲座信息
/// </summary>
/// <param name="AddLectureName"></param>
/// <param name="AddArchitectureId"></param>
/// <returns></returns>
public JsonResult AddSaveLecture(string AddNum, string AddSubject, string AddSummary,
DateTime AddLectureTime, DateTime AddDeathLine, float AddSpan, int AddExpectPeople,
int AddArchitectureId, int AddPlaceId, double AddScore)
{
Model.T_Base_Lecture lecture = new Model.T_Base_Lecture();
lecture.Subject = AddSubject;
lecture.Summary = AddSummary;
lecture.LectureTime = AddLectureTime;
lecture.DeathLine = AddDeathLine;
lecture.Span = AddSpan;
lecture.ExpectPeople = AddExpectPeople;
lecture.Score = AddScore;
int result = new BLL.T_Base_Lecture().AddSaveLecture(AddNum, lecture, AddPlaceId);
if (result > -1)
{
return(Json("添加成功"));
}
else if (result == -1)
{
return(Json("添加失败"));
}
else if (result == -2)
{
return(Json("该时间段内存在其他讲座"));
}
else if (result == -3)
{
return(Json("请确保按照格式填入全部信息"));
}
else
{
return(Json("请联系管理员上报错误"));
}
}
/// <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);
}
/// <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>
/// 查询出席的讲座
/// </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);
}
//public int GetOrderCount(string Num)
//{
// SqlConfig config = new DAL.SqlConfig();
// SqlCommand cmd = config.getSqlCommand();
// cmd.CommandText = "select count(*) from V_Lecture_Order where Num='" + Num + "' and MsgFlag=1";
// int result = (int)cmd.ExecuteScalar();
// config.Close();
// return result;
//}
/// <summary>
/// 实际人数超出预期人数的讲座个数
/// </summary>
/// <param name="LcetureId"></param>
/// <returns></returns>
public List <Model.T_Base_Lecture> GetLectureMsg(string Num)
{
List <Model.T_Base_Lecture> list = new List <Model.T_Base_Lecture>();
SqlConfig config = new DAL.SqlConfig();
SqlCommand cmd = config.getSqlCommand();
cmd.CommandText = "select Id, Subject from T_Base_Lecture where Id in" +
"(select LectureId from T_Base_Apply where Num = '" + Num + "') and " +
"RealPeople > ExpectPeople";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.T_Base_Lecture lecture = new Model.T_Base_Lecture();
lecture.Id = Convert.ToInt32(reader["Id"]);
lecture.Subject = Convert.ToString(reader["Subject"]);
list.Add(lecture);
}
reader.Close();
config.Close();
return(list);
}
/// <summary>
/// 保存修改后的信息
/// </summary>
/// <param name="EditNum"></param>
/// <param name="EditId"></param>
/// <param name="EditSubject"></param>
/// <param name="EditSummary"></param>
/// <param name="EditLectureTime"></param>
/// <param name="EditDeathLine"></param>
/// <param name="EditSpan"></param>
/// <param name="EditExpectPeople"></param>
/// <param name="EditArchitectureId"></param>
/// <param name="EditPlaceId"></param>
/// <param name="EditScore"></param>
/// <returns></returns>
public JsonResult EditSaveLecture(string EditNum, int EditApplyId, int EditLectureId, string EditSubject, string EditSummary,
DateTime EditLectureTime, DateTime EditDeathLine, float EditSpan, int EditExpectPeople,
int EditArchitectureId, int EditPlaceId, double EditScore)
{
Model.T_Base_Lecture lecture = new Model.T_Base_Lecture();
lecture.Id = EditLectureId;
lecture.Subject = EditSubject;
lecture.Summary = EditSummary;
lecture.LectureTime = EditLectureTime;
lecture.DeathLine = EditDeathLine;
lecture.Span = EditSpan;
lecture.ExpectPeople = EditExpectPeople;
lecture.Score = EditScore;
int result = new BLL.T_Base_Lecture().EditSaveLecture(EditNum, EditApplyId, lecture, EditPlaceId);
if (result > -1)
{
return(Json("修改成功"));
}
else if (result == -1)
{
return(Json("修改失败"));
}
else if (result == -2)
{
return(Json("该时间段内存在其他讲座"));
}
else if (result == -3)
{
return(Json("未进行信息的修改"));
}
else
{
return(Json("请联系管理员上报错误"));
}
}
/// <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);
}
/// <summary>
/// 保存修改后的信息
/// </summary>
/// <param name="Lecture"></param>
/// <returns></returns>
public int EditSaveLecture(string EditNum, int EditApplyId, Model.T_Base_Lecture Lecture, int EditPlaceId)
{
return(new DAL.T_Base_Lecture().EditSaveLecture(EditNum, EditApplyId, Lecture, EditPlaceId));
}
