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