/// <summary>
/// 取消报名
/// </summary>
/// <param name="Num"></param>
/// <param name="LectureId"></param>
/// <returns></returns>
public int OrderDelete(string Num, int LectureId)
{
SqlConfig config = new SqlConfig();
SqlCommand cmd = config.getSqlCommand();
int result = -1;
result = OrderSelect(Num, LectureId); //查询是否有报名
if (result == 1)
{
cmd.Transaction = config.getSqlConnection().BeginTransaction();
cmd.CommandText = "select Result from T_Base_Order where Num='" + Num + "' and LectureId=" + LectureId;
int orderResult = (int)cmd.ExecuteScalar(); //报名者的状态
cmd.CommandText = "delete from T_Base_Order where Num = " + Num + " and LectureId = " + LectureId;
cmd.ExecuteNonQuery();
cmd.CommandText = "select ExpectPeople,RealPeople,Subject from T_Base_Lecture where Id = " + LectureId;
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int ExpectPeople = Convert.ToInt32(reader["ExpectPeople"]);
int RealPeople = Convert.ToInt32(reader["RealPeople"]);
string subject = Convert.ToString(reader["Subject"]);
reader.Close();
cmd.CommandText = "update T_Base_Lecture set RealPeople = " + (RealPeople - 1) + " where Id = " + LectureId;
result = cmd.ExecuteNonQuery(); //修改讲座当前人数
if (result == 1) //取消者报名的状态为报名成功
{
if (RealPeople >= ExpectPeople && orderResult == 1)
{
cmd.CommandText = "update T_Base_Order set Result=3,MsgFlag=0 where LectureId=" + LectureId +
" and Result=2 and Num=(select top(1) Num from T_Base_Order where LectureId=" + LectureId + " and Result=2 order by Id asc)";
cmd.ExecuteNonQuery();
MailMessage mailMessage = new MailMessage();
mailMessage.From = new MailAddress("*****@*****.**");
mailMessage.To.Add(new MailAddress("*****@*****.**"));
mailMessage.Subject = "报名" + subject + "成功";
mailMessage.Body = "因前面预约人员取消报名，报名预约成功";
SmtpClient client = new SmtpClient();
client.Host = "smtp.wzu.edu.cn";
client.EnableSsl = false;
client.UseDefaultCredentials = false;
client.Credentials = new NetworkCredential("*****@*****.**", "b5ZF36PPMMmCF883");
client.Send(mailMessage);
}
cmd.Transaction.Commit();
config.Close();
return(1); //取消成功
}
else
{
config.Close();
return(-1); //取消失败
}
}
else
{
config.Close();
return(-2); //取消失败，未报名
}
}
/// <summary>
/// 报名
/// </summary>
/// <param name="Num"></param>
/// <param name="LectureId"></param>
/// <returns></returns>
public int Order(string Num, int LectureId)
{
SqlConfig config = new SqlConfig();
SqlCommand cmd = config.getSqlCommand();
cmd.Transaction = config.getSqlConnection().BeginTransaction();
int result = -1;
cmd.CommandText = "select count(1) from T_Base_Order where Num = " + Num +
" and LectureId = " + LectureId;
result = (int)cmd.ExecuteScalar();
if (result == 1)
{
cmd.Transaction.Rollback();
config.Close();
return(-2); //以报名
}
cmd.CommandText = "select ExpectPeople,RealPeople from T_Base_Lecture where Id = " + LectureId;
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int ExpectPeople = Convert.ToInt32(reader["ExpectPeople"]);
int RealPeople = Convert.ToInt32(reader["RealPeople"]);
reader.Close();
cmd.CommandText = "update T_Base_Lecture set RealPeoPle = " + (RealPeople + 1) +
" where Id = " + LectureId;
cmd.ExecuteNonQuery();
if (RealPeople < ExpectPeople)
{
cmd.CommandText = "insert into T_Base_Order values('" +
Num + "'," + LectureId + ",'" + DateTime.Now + "',1,0)"; //报名成功
result = cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = "insert into T_Base_Order values('" +
Num + "'," + LectureId + ",'" + DateTime.Now + "',2,0)"; //待处理
result = cmd.ExecuteNonQuery();
result = 2;
//cmd.CommandText = "";
//MailMessage mailMessage = new MailMessage();
//mailMessage.From = new MailAddress("*****@*****.**");
//mailMessage.To.Add(new MailAddress("*****@*****.**"));
//mailMessage.Subject = "报名" + subject + "成功";
//mailMessage.Body = "因前面预约人员取消报名，报名预约成功";
//SmtpClient client = new SmtpClient();
//client.Host = "smtp.wzu.edu.cn";
//client.EnableSsl = false;
//client.UseDefaultCredentials = false;
//client.Credentials = new NetworkCredential("*****@*****.**", "b5ZF36PPMMmCF883");
//client.Send(mailMessage);
}
cmd.Transaction.Commit();
config.Close();
return(result);
}
/// <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);
}
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);
}