// PUT api/Student_Course/5
public IHttpActionResult PutStudent_Course(int id, Student_Course student_course)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != student_course.SC_ID)
{
return(BadRequest());
}
db.Entry(student_course).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!Student_CourseExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
protected void Button2_Click(object sender, EventArgs e)
{
List <Song.Entities.LearningCard> lcs = Business.Do <ISystemPara>().ForSql <Song.Entities.LearningCard>("select * from LearningCard where Lc_State=1");
foreach (LearningCard lc in lcs)
{
int acid = lc.Ac_ID; //学员Id
int span = lc.Lc_Span; //要增加的学习时间
//获取学习卡关联的课程
Song.Entities.LearningCardSet lset = Business.Do <ILearningCard>().SetSingle(lc.Lcs_ID);
Song.Entities.Course[] cours = Business.Do <ILearningCard>().CoursesGet(lset);
foreach (Song.Entities.Course c in cours)
{
Student_Course sc = Business.Do <ISystemPara>()
.ScalarSql <Student_Course>(string.Format("select * from student_course where ac_id={0} and cou_id={1}", acid, c.Cou_ID));
if (sc != null)
{
DateTime end = sc.Stc_StartTime.AddDays(span); //应该结束的学习时效
if (end > sc.Stc_EndTime)
{
sc.Stc_EndTime = end;
Business.Do <ISystemPara>().ExecuteSql(string.Format("update student_course set stc_endtime='{0}' where ac_id={1} and cou_id={2}",
end.ToString("yyyy-MM-dd"), acid, c.Cou_ID));
}
}
}
}
this.Response.Write(this.Button2.Text + "---操作完成！");
}
public void Add(Course newCourse, PocoCourse course)
{
if (course.InstructorsId != null)
{
foreach (int id in course.InstructorsId)
{
Instructor_Course ic = new Instructor_Course()
{
fk_instructorid = id,
fk_courseid = newCourse.id
};
ctx.Instructor_Course.Add(ic);
ctx.SaveChanges();
}
}
if (course.StudentsId != null)
{
foreach (int id in course.StudentsId)
{
Student_Course sc = new Student_Course()
{
fk_studentid = id,
fk_courseid = newCourse.id
};
ctx.Student_Course.Add(sc);
ctx.SaveChanges();
}
}
}
public async Task <IActionResult> StudentList()
{
List <Student_Course> ilIst = new List <Student_Course>(); //Creiamo una lista di Studente_Corso
var listData = await(from emp in _context.Student
join pro in _context.Course on emp.CorsoId equals pro.CorsoId
select new
{
emp.StudenteId,
emp.Nome,
emp.Cognome,
emp.AnnoIscrizione,
emp.Matricola,
pro.Titolo
}
).ToListAsync();
listData.ForEach(x =>
{
Student_Course Obj = new Student_Course();
Obj.StudenteId = x.StudenteId;
Obj.Nome = x.Nome;
Obj.Cognome = x.Cognome;
Obj.Matricola = x.Matricola;
Obj.AnnoIscrizione = x.AnnoIscrizione;
Obj.Course = x.Titolo;
ilIst.Add(Obj);
});
return(Json(ilIst));
}
//get full Student_Courselist of database
public static List <Student_Course> GetFullStudent_Courselist()
{
List <Student_Course> Student_Courselist = new List <Student_Course>();
try
{
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["MyMoodleDB"];
string connectionString = css.ConnectionString;
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "SELECT * FROM Student_Course ORDER BY Id";
SqlCommand cmd = new SqlCommand(query, cn);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Student_Course s_course = new Student_Course((int)dr[0], (int)dr[1], (int)dr[2], dr[3].ToString());
Student_Courselist.Add(s_course);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(Student_Courselist);
}
public void Add(Course c, POCOCourse pocoCourse)
{
if (pocoCourse.StudentsId != null)
{
foreach (int id in pocoCourse.StudentsId)
{
Student_Course sc = new Student_Course()
{
student_id = id,
course_id = c.id
};
db.Student_Course.Add(sc);
db.SaveChanges();
}
}
if (pocoCourse.InstructorsId != null)
{
foreach (int id in pocoCourse.InstructorsId)
{
Instructor_Course ic = new Instructor_Course()
{
instructor_id = id,
course_id = c.id
};
db.Instructor_Course.Add(ic);
db.SaveChanges();
}
}
}
internal void add(Student x, PocoStudent student)
{
if (student.CoursesId != null)
{
foreach (int id in student.CoursesId)
{
Student_Course sc = new Student_Course()
{
fk_courseid = id,
fk_studentid = x.id
};
ctx.Student_Course.Add(sc);
ctx.SaveChanges();
}
if (student.InstructorsId != null)
{
foreach (int id in student.InstructorsId)
{
Student_Instructor si = new Student_Instructor()
{
fk_instructorid = id,
fk_studentid = x.id
};
ctx.Student_Instructor.Add(si);
ctx.SaveChanges();
}
}
}
}
/// <summary>
/// 获取课程的购买信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
protected string getBuyInfo(object id)
{
int couid = 0;
int.TryParse(id.ToString(), out couid);
Student_Course sc = Business.Do <ICourse>().StudentCourse(Extend.LoginState.Accounts.CurrentUser.Ac_ID, couid);
if (sc == null)
{
return("");
}
if (sc.Stc_IsFree && sc.Stc_EndTime > sc.Stc_StartTime.AddYears(100))
{
return("免费（无限期）");
}
if (sc.Stc_IsFree && sc.Stc_EndTime < sc.Stc_StartTime.AddYears(100))
{
return(string.Format("免费到{0}", sc.Stc_EndTime.ToString("yyyy年M月d日 HH:mm:ss")));
}
if (sc.Stc_IsTry)
{
return("试用");
}
return(sc.Stc_StartTime.ToString("yyyy年MM月dd日") + " - " + sc.Stc_EndTime.ToString("yyyy年MM月dd日 HH:mm:ss"));
}
public void Edit(PocoCourse course)
{
if (course.InstructorsId.Count > 0)
{
var obj = ctx.Instructor_Course.Find(course.Id);
foreach (int id in course.InstructorsId)
{
Instructor_Course ic = new Instructor_Course()
{
id = obj.id,
fk_instructorid = id,
};
ctx.Instructor_Course.Attach(ic);
ctx.Entry(ic).State = EntityState.Modified;
ctx.SaveChanges();
}
}
if (course.StudentsId.Count > 0)
{
var obj = ctx.Student_Course.Find(course.Id);
foreach (int id in course.StudentsId)
{
Student_Course sc = new Student_Course()
{
id = obj.id,
fk_studentid = id,
fk_courseid = course.Id
};
ctx.Entry(sc).State = EntityState.Modified;
ctx.SaveChanges();
}
}
}
public ActionResult DeleteConfirmed(int id)
{
Student_Course student_Course = db.Student_Courses.Find(id);
db.Student_Courses.Remove(student_Course);
db.SaveChanges();
return(RedirectToAction("Index"));
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Student_Course student_Course = new Student_Course();
Student_CourseBLL bll = new Student_CourseBLL();
student_Course.CourseNO = Int32.Parse(GridView1.Rows[e.RowIndex].Cells[2].Text);
student_Course.StudentNO = Int32.Parse(GridView1.Rows[e.RowIndex].Cells[0].Text);
bll.DeleteStudent_Course(student_Course);
}
protected void ButtonAdd_Click(object sender, EventArgs e)
{
Student_Course student_Course = new Student_Course();
student_Course.CourseNO = Int32.Parse(DropDownListCourse.SelectedValue);
student_Course.StudentNO = Int32.Parse(DropDownListStudent.SelectedValue);
Student_CourseBLL bll = new Student_CourseBLL();
bll.AddStudent_Course(student_Course);
}
public ActionResult InStudentCourseEnrolReport(Course course)
{
string sql = "SELECT sc.*, c.Name, s.StudentName FROM Student_Course sc JOIN Courses c ON sc.CourseId = c.CourseId JOIN Students s ON sc.StudentId = s.StudentId WHERE sc.courseId = " + course.CourseId;
db.List(sql);
var dt = db.List(sql);
var model = new Student_Course().List(dt);
ViewBag.CourseId = new SelectList(db.Courses, "CourseId", "Name");
return(View(model));
}
public bool ChooseCourse(String stuname, String course_num) //学生选课
{
DataClassesDataContext da = new DataClassesDataContext();
Student_Course sc = new Student_Course();
sc.course = course_num;
sc.student = stuname;
da.Student_Course.InsertOnSubmit(sc);
da.SubmitChanges();
return(true);
}
public IHttpActionResult GetStudent_Course([FromUri] int id)
{
Student_Course student_course = db.Student_Course.Find(id);
if (student_course == null)
{
return(NotFound());
}
return(Ok(student_course));
}
public IHttpActionResult PostStudent_Course([FromBody] Student_Course student_course)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
db.Student_Course.Add(student_course);
db.SaveChanges();
return(CreatedAtRoute("DefaultApi", new { id = student_course.SC_ID }, student_course));
}
public ActionResult Edit([Bind(Include = "Student_CourseID,CourseID,StudentID")] Student_Course student_Course)
{
if (ModelState.IsValid)
{
db.Entry(student_Course).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CourseID = new SelectList(db.Courses, "CourseID", "Title", student_Course.CourseID);
ViewBag.StudentID = new SelectList(db.Students, "StudentID", "Name", student_Course.StudentID);
return(View(student_Course));
}
public void DeleteStudent_course(Student_Course student_Course)
{
string SQLServerConnectString = "Data Source=HXL-PC;Initial Catalog=WebAppDevClass;Integrated Security=True;Pooling=False";
SqlConnection SQLConnection = new SqlConnection(SQLServerConnectString);
string SQLCommandText = "DELETE [dbo].[Student_Course] where [CourseNO] = @CourseNO and [StudentNO] = @StudentNO";
SqlCommand SQLCommand = new SqlCommand(SQLCommandText, SQLConnection);
SQLCommand.Parameters.Add(new SqlParameter("@CourseNO", student_Course.CourseNO));
SQLCommand.Parameters.Add(new SqlParameter("@StudentNO", student_Course.StudentNO));
SQLConnection.Open();
SQLCommand.ExecuteNonQuery();
SQLConnection.Close();
}
// GET: Student_Course
public ActionResult Index()
{
//var student_Course = db.Student_Course.Include(s => s.Courses).Include(s => s.Students);
//return View(student_Course.ToList());
String sql = "SELECT sc.*, c.Name, s.StudentName FROM Student_Course sc JOIN Courses c ON sc.CourseId = c.CourseId JOIN Students s ON sc.StudentId = s.StudentId";
db.List(sql);
var dt = db.List(sql);
var model = new Student_Course().List(dt);
return(View(model));
}
public ActionResult Create([Bind(Include = "ID,StudentID,CourseID")] Student_Course student_Course)
{
if (ModelState.IsValid)
{
db.Student_Course.Add(student_Course);
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CourseID = new SelectList(db.Courses, "ID", "Title", student_Course.CourseID);
ViewBag.StudentID = new SelectList(db.Students, "ID", "Name", student_Course.StudentID);
return(View(student_Course));
}
public void AddStudent_Course(Student_Course student_Course)
{
string SQLServerConnectString = "Data Source=HXL-PC;Initial Catalog=WebAppDevClass;Integrated Security=True;Pooling=False";
SqlConnection SQLConnection = new SqlConnection(SQLServerConnectString);
string SQLCommandText = "INSERT INTO [dbo].[Student_Course] ([CourseNO], [StudentNO]) VALUES (@CourseNO, @StudentNO)";
SqlCommand SQLCommand = new SqlCommand(SQLCommandText, SQLConnection);
SQLCommand.Parameters.Add(new SqlParameter("@CourseNO", student_Course.CourseNO));
SQLCommand.Parameters.Add(new SqlParameter("@StudentNO", student_Course.StudentNO));
SQLConnection.Open();
SQLCommand.ExecuteNonQuery();
SQLConnection.Close();
}
public IHttpActionResult DeleteStudent_Course(int id)
{
Student_Course student_course = db.Student_Course.Find(id);
if (student_course == null)
{
return(NotFound());
}
db.Student_Course.Remove(student_course);
db.SaveChanges();
return(Ok(student_course));
}
// GET: Student_Course/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
Student_Course student_Course = db.Student_Courses.Find(id);
if (student_Course == null)
{
return(HttpNotFound());
}
return(View(student_Course));
}
public ActionResult Edit([Bind(Include = "StudentCourseId,CourseId,StudentId")] Student_Course student_Course)
{
if (ModelState.IsValid)
{
db.Entry(student_Course).State = EntityState.Modified;
db.SaveChanges();
//String sql = "Update Student_Course set CourseId='" + student_Course.CourseId + "', StudentId='" + student_Course.StudentId+ "' where StudentCourseId = " + student_Course.StudentCourseId;
//db.Edit(sql);
return(RedirectToAction("Index"));
}
ViewBag.CourseId = new SelectList(db.Courses, "CourseId", "Name", student_Course.CourseId);
ViewBag.StudentId = new SelectList(db.Students, "StudentId", "StudentName", student_Course.StudentId);
return(View(student_Course));
}
// GET: Student_Course/Edit/5
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
Student_Course student_Course = db.Student_Courses.Find(id);
if (student_Course == null)
{
return(HttpNotFound());
}
ViewBag.CourseID = new SelectList(db.Courses, "CourseID", "Title", student_Course.CourseID);
ViewBag.StudentID = new SelectList(db.Students, "StudentID", "Name", student_Course.StudentID);
return(View(student_Course));
}
public ActionResult Create([Bind(Include = "StudentCourseId,CourseId,StudentId")] Student_Course student_Course)
{
if (ModelState.IsValid)
{
//db.Student_Course.Add(student_Course);
//db.SaveChanges();
string sql = "INSERT INTO Student_Course(CourseId, StudentId) " +
"values( '" + student_Course.CourseId + "', '" + student_Course.StudentId + "')";
db.Create(sql);
return(RedirectToAction("Index"));
}
ViewBag.CourseId = new SelectList(db.Courses, "CourseId", "Name", student_Course.CourseId);
ViewBag.StudentId = new SelectList(db.Students, "StudentId", "StudentName", student_Course.StudentId);
return(View(student_Course));
}
public ActionResult CreateNewStudent(FormCollection col)
{
var username = User.Identity.GetUserName();
var currentUser = ur.GetCurrentUser(username);
Person newStudent = new Person()
{
FirstName = col["firstName"],
LastName = col["lastName"],
PersonType = "Student",
Phone = col["phone"],
Email = col["email"]
};
db.People.Add(newStudent);
db.SaveChanges();
if (col.AllKeys.Contains("selectedCourses"))
{
string[] courses = col["selectedCourses"].Split(',');
foreach (var course in courses)
{
Student_Course newStudentCourse = new Student_Course()
{
StudentId = newStudent.PersonId,
RegistrationStatus = "Prospective",
CourseId = Convert.ToInt32(course)
};
db.Student_Courses.Add(newStudentCourse);
db.SaveChanges();
}
}
if (col["notes"] != "")
{
Reminder newReminder = new Reminder()
{
StudentId = newStudent.PersonId,
AdvisorId = currentUser.EmployeeId,
Date = Convert.ToDateTime(col["date"]),
Notes = col["notes"],
Completed = false
};
db.Reminders.Add(newReminder);
db.SaveChanges();
}
return(RedirectToAction("AllStudents"));
}
protected void Button1_Click(object sender, EventArgs e)
{
//取结束时间
Regex rxEnd = new Regex(@"\d{4}-\d{2}-\d{2}$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
Regex rxName = new Regex(@"(?<=:)(.[^；]*)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
//
List <Song.Entities.MoneyAccount> mas = Business.Do <ISystemPara>().ForSql <Song.Entities.MoneyAccount>("select * from moneyaccount where ma_type=1");
foreach (Song.Entities.MoneyAccount m in mas)
{
string info = m.Ma_Info;
//取课程名称与结束时间
string endstr = string.Empty, cour = string.Empty;
int acid = m.Ac_ID; //学员Id
MatchCollection matcheEnd = rxEnd.Matches(info);
if (matcheEnd.Count > 0)
{
endstr = matcheEnd[0].Value;
}
MatchCollection matcheName = rxName.Matches(info);
if (matcheName.Count > 0)
{
cour = matcheName[0].Value;
}
//取当前课程
Song.Entities.Course course = Business.Do <ISystemPara>().ScalarSql <Song.Entities.Course>("select * from Course where cou_name='" + cour + "'");
if (course != null)
{
Student_Course sc = Business.Do <ISystemPara>()
.ScalarSql <Student_Course>(string.Format("select * from student_course where ac_id={0} and cou_id={1}", acid, course.Cou_ID));
if (sc != null)
{
DateTime end = DateTime.Now; //资金流水中记录的过期时间
DateTime.TryParse(endstr, out end);
if (end > sc.Stc_EndTime)
{
sc.Stc_EndTime = end;
Business.Do <ISystemPara>().ExecuteSql(string.Format("update student_course set stc_endtime='{0}' where ac_id={1} and cou_id={2}",
endstr, acid, course.Cou_ID));
}
}
}
}
//
this.Response.Write(this.Button1.Text + "---操作完成！");
}
public void Add(Student st, POCOStudent pocoStudent)
{
if (pocoStudent.CourseId != null)
{
foreach (int id in pocoStudent.CourseId)
{
Student_Course sc = new Student_Course()
{
course_id = id,
student_id = st.id
};
db.Student_Course.Add(sc);
db.SaveChanges();
}
}
}
// GET: Student_Course/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
//Student_Course student_Course = db.Student_Course.Find(id);
String sql = "SELECT sc.*, c.Name, s.StudentName FROM Student_Course sc JOIN Courses c ON sc.CourseId = c.CourseId JOIN Students s ON sc.StudentId = s.StudentId WHERE sc.StudentCourseId = " + id;
db.List(sql);
var dt = db.List(sql);
var model = new Student_Course().List(dt).FirstOrDefault();
if (model == null)
{
return(HttpNotFound());
}
return(View(model));
}
