public IActionResult getSemester(string indexNumber, int semester)
{
int id = int.Parse(indexNumber);
using (SqlConnection con = new SqlConnection("Data Source=10.1.1.36; Initial Catalog=s8346;Integrated Security=True"))
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
com.CommandText = " select Semester, IdStudy, StartDate from Student inner join Enrollment on Student.IdEnrollment = Enrollment.IdEnrollment where Enrollment.Semester=@semester and Student.IndexNumber = @id;";
com.Parameters.AddWithValue("id", id);
com.Parameters.AddWithValue("semester", semester);
con.Open();
SqlDataReader dr = com.ExecuteReader();
if (dr.Read())
{
Models.Enrollment enrollment = new Models.Enrollment();
enrollment.IdStudy = int.Parse(dr["IdStudy"].ToString());
enrollment.Semester = int.Parse(dr["Semester"].ToString());
enrollment.StartDate = DateTime.Parse(dr["StartDate"].ToString());
return(Ok(enrollment));
}
return(NotFound());
}
}
public Models.Enrollment EnrollStudent(StudentEnrollmentRequest request)
{
using var connection = new SqlConnection(_databaseString);
connection.Open();
var transaction = connection.BeginTransaction();
var studiesId = getStudiesId(request.Studies, connection, transaction);
if (studiesId == -1)
{
return(null);
}
var enrollmentId = getEnrollmentId(studiesId, connection, transaction);
if (isIndexNumberOccupied(connection, transaction, request.IndexNumber))
{
return(null);
}
insertStudent(connection, transaction, request, enrollmentId);
var enrollment = new Models.Enrollment();
enrollment.Semester = 1;
enrollment.Studies = request.Studies;
return(enrollment);
}
public IActionResult GetStudent(string index)
{
using (var client = new SqlConnection("Data Source=db-mssql.pjwstk.edu.pl;Initial Catalog=2019SBD;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = client;
com.CommandText = "select Enrollment.IdEnrollment,Enrollment.Semester,Enrollment.IdStudy,Enrollment.StartDate from Enrollment,Student where Student.IdEnrollment=Enrollment.IdEnrollment and Student.IndexNumber=@index";
com.Parameters.AddWithValue("index", index);
client.Open();
var dr = com.ExecuteReader();
lista2 = new List <Models.Enrollment>();
while (dr.Read())
{
var st = new Models.Enrollment();
st.Idenrollment = Convert.ToInt32(dr["IdEnrollment"]);
st.semester = (int)dr["Semester"];
st.IdStudy = Convert.ToInt32(dr["IdStudy"]);
st.StartDate = dr["StartDate"].ToString();
lista2.Add(st);
}
}
return(Ok(lista2));
}
public async Task <IActionResult> Post([FromBody] Models.Enrollment value)
{
try
{
var result = await Enroll.UpdateEnrollment(value);
if (!result.subject)
{
return(BadRequest("Subject doesn't exist"));
}
if (!result.updated)
{
return(BadRequest("Student doesn't exist"));
}
if (result.reachedTheatreCap)
{
return(BadRequest("Lecture Theatre is full"));
}
return(Ok("Successfully Enrolled"));
}
catch (Exception ex)
{
return(StatusCode(500, ex.Message));
}
}
public IActionResult GetStudentEnrollment(string indexNumber)
{
Models.Enrollment enrollment = _dbService.GetEnrollment(indexNumber);
if (enrollment == null)
{
return(NotFound("Student not found"));
}
return(Ok(enrollment));
}
public IActionResult GetEnrollment(int idEnrollment)
{
Models.Enrollment enrollment = _dbService.GetEnrollment(idEnrollment);
if (enrollment == null)
{
return(NotFound("Enrollment not found"));
}
return(Ok(enrollment));
}
public void RemoveEnrollment(Models.ApplicationUser user, int sectionId)
{
Models.Enrollment enrollment = _context.Enrollment.FirstOrDefault(e => e.StudentId == user.Id && e.SectionId == sectionId);
var assignments = _context.Assignment.Where(a => a.SectionId == enrollment.SectionId);
foreach (var assignment in assignments)
{
_context.StudentAssignment.Remove(_context.StudentAssignment.First(s => s.AssignmentId == assignment.Id && s.StudentId == enrollment.StudentId));
}
_context.Enrollment.Remove(enrollment);
_context.SaveChanges();
}
public void AddEnrollment(Models.Enrollment enrollment)
{
var assignments = _context.Assignment.Where(a => a.SectionId == enrollment.SectionId).ToList();
foreach (var assignment in assignments)
{
StudentAssignment studentAssignment = new StudentAssignment
{
StudentId = enrollment.StudentId,
AssignmentId = assignment.Id
};
_context.StudentAssignment.Add(studentAssignment);
}
_context.Enrollment.Add(enrollment);
_context.SaveChanges();
}
public async Task <IActionResult> PurchasePlanPost(int?id, IFormCollection collection)
{
if (id == null)
{
return(NotFound());
}
var subscriberToUpdate = await _context.Subscribers.SingleOrDefaultAsync(s => s.SubscriberID == Convert.ToInt32(collection["SubscriberId"]));
if (await TryUpdateModelAsync(
subscriberToUpdate,
"",
s => s.FirstName, s => s.MiddleName, s => s.LastName, s => s.AddressLine1, s => s.AddressLine2, s => s.City, s => s.State, s => s.ZipCode, s => s.County, s => s.PhoneNumber, s => s.EmailAddress, s => s.SocialSecurityNumber, s => s.IsUSCitizen, s => s.IsMilitary, s => s.IsStudent, s => s.IsOnMedicare, s => s.IsOnDisability, s => s.EmploymentIncome, s => s.InvestmentIncome, s => s.AlimonyChildSupport))
{
try
{
var _confirmation = RandomString(10);
var _enrollment = new Models.Enrollment {
InsurancePlanID = (int)id, PlanYear = DateTime.Now.Year, SubscriberID = Convert.ToInt32(collection["SubscriberId"]), ConfirmationCode = _confirmation, TimeStamp = DateTime.Now
};
TempData["confirmation"] = _confirmation;
_context.Add(_enrollment);
await _context.SaveChangesAsync();
var filteredData = FullEnrollmentData.GetFullEnrollmentData(_context, _enrollment.EnrollmentID);
AzureStorageHelper helper = new AzureStorageHelper(ConfigurationManager.AppSettings["AzS_StorageConnectionString"], ConfigurationManager.AppSettings["AzS_StorageContainerName"]);
helper.UploadDataToAzureStorage(
$"{filteredData.EnrollmentID}-{filteredData.SubscriberID}-{filteredData.HouseholdMemberID}.json",
JsonConvert.SerializeObject(filteredData, Formatting.Indented));
return(RedirectToAction("Confirm"));
}
catch (DbUpdateException ex)
{
//Log the error (uncomment ex variable name and write a log.)
ModelState.AddModelError("", "Unable to save changes. " +
"Try again, and if the problem persists, " +
"see your system administrator." + ex.Message);
}
}
return(View(subscriberToUpdate));
}
public DAL.Models.Enrollment Chain(Cybirst.Enrollment item)
{
DAL.Models.Enrollment enrollment = new Models.Enrollment();
enrollment.ID = item.ID;
enrollment.IsComplete = item.IsComplete;
enrollment.TimeStart = item.TimeStart;
enrollment.OrderState = item.OrderState;
enrollment.LastActive = item.LastActive;
enrollment.Course = this.Chain(item.Course);
return(enrollment);
}
//PROMOTING STUDENTS
public Models.Enrollment PromoteStudents(PromotionRequest request)
{
using (var connection = new SqlConnection(_databaseString))
{
connection.Open();
var transaction = connection.BeginTransaction();
var idEnrollment = getEnrollmentIdBySemester(request, connection, transaction);
if (idEnrollment == -1)
{
return(null);
}
promote(connection, transaction, request.Studies, request.Semester);
Models.Enrollment enrollment = new Models.Enrollment();
enrollment.Studies = request.Studies;
enrollment.Semester = request.Semester + 1;
return(enrollment);
}
}
//In the add/delete assignment functions, make sure to add/delete a studentAssignment
// entry in the studentAssignment table for every student in the section.
//public void AddStudentAssignment()
//{
// _context.
// _context.SaveChanges();
//}
//Enrollment Methods
public void AddEnrollment(Models.ApplicationUser user, int sectionid)
{
Models.Enrollment enrollment = new Models.Enrollment();
enrollment.SectionId = sectionid;
enrollment.StudentId = user.Id;
var assignments = _context.Assignment.Where(a => a.SectionId == enrollment.SectionId).ToList();
foreach (var assignment in assignments)
{
StudentAssignment studentAssignment = new StudentAssignment
{
StudentId = enrollment.StudentId,
AssignmentId = assignment.Id
};
_context.StudentAssignment.Add(studentAssignment);
}
_context.Enrollment.Add(enrollment);
_context.SaveChanges();
}
public IActionResult Promote(PromoteModel enroll)
{
using (var client = new SqlConnection(MyDatabase))
{
using (var command = new SqlCommand())
{
var enrollment = new Models.Enrollment();
command.Connection = client;
command.CommandText = "SELECT * from Enrollment inner join studies on Studies.idstudy = Enrollment.idstudy where Semester = @sem and Studies.name = @stud";
command.Parameters.AddWithValue("stud", enroll.Studies);
command.Parameters.AddWithValue("sem", enroll.Semester);
client.Open();
var qr = command.ExecuteReader();
if (qr.HasRows)
{
qr.Close();
command.CommandText = "exec Promote @stud, @sem";
command.ExecuteNonQuery();
qr.Close();
command.CommandText = "select IdEnrollment, StartDate, Semester from Enrollment inner join Studies on Enrollment.IdStudy=Studies.IdStudy where semester= @sem + 1 and name=@stud";
qr = command.ExecuteReader();
if (qr.Read())
{
enrollment.idEnrollment = qr["idEnrollment"].ToString();
enrollment.Semester = qr["Semester"].ToString();
enrollment.StartDate = qr["StartDate"].ToString();
}
qr.Close();
return(StatusCode(201, enrollment));
}
else
{
return(NotFound());
}
}
}
}
public EnrollmentResponse EnrollStudent(Models.Student student)
{
if (student.IndexNumber == null || student.Firstname == null || student.Lastname == null || student.BirthDate == null || student.Studies == null)
{
return(null);
}
else
{
int result1;
//int idEnrollment;
//client.Open();
//com.Connection = client;
//var tran = client.BeginTransaction();
//com.Transaction = tran;
//com.CommandText = "select * from Studies where Name=@index";
//com.Parameters.AddWithValue("index", student.Studies);
var result = _context.Studies.Select(e => new {
e.IdStudy,
e.Name
}).Where(d => d.Name.Equals(student.Studies));
//var dr = com.ExecuteReader();
if (result == null)
{
return(null);
}
int idStudies = result.Select(e => e.IdStudy).First();
//dr.Close();
var fromEnr = _context.Enrollment.Select(e => e).Where(e => e.Semester == 1 && e.IdStudy == idStudies);
//com.CommandText = "SELECT * FROM Enrollment WHERE Semester=1 AND IdStudy=@IdStud";
//com.Parameters.AddWithValue("IdStud", idStudies);
//var dr2 = com.ExecuteReader();
if (fromEnr == null)
{
//dr2.Close();
//com.CommandText = "Select max(idenrollment) from enrollment";
result1 = _context.Enrollment.Max(e => e.IdEnrollment);
//var dr3 = com.ExecuteReader();
//dr3.Read();
//idEnrollment = dr3.GetInt32(0);
//dr3.Close();
var newEnroll = new Entities.Enrollment()
{
IdEnrollment = result1 + 1,
Semester = 1,
IdStudy = idStudies,
StartDate = DateTime.Now
};
_context.Enrollment.Add(newEnroll);
_context.SaveChanges();
//com.CommandText = "INSERT INTO Enrollment(idEnrollment, semester, idStudy,StartDate) VALUES (@idE, @Semester,@IdStudy,@sd)";
//com.Parameters.AddWithValue("idE", idEnrollment + 1);
//com.Parameters.AddWithValue("Semester", 1);
//com.Parameters.AddWithValue("IdStudy", idStudies);
//com.Parameters.AddWithValue("sd", DateTime.Now.ToString());
//var dr6 = com.ExecuteNonQuery();
}
else
{
result1 = fromEnr.Select(e => e.IdEnrollment).First();
}
var dont2 = _context.Student.Select(e => new { e.IndexNumber }).Where(d => d.IndexNumber.Equals(student.IndexNumber)).FirstOrDefault();
//com.CommandText = "SELECT * FROM Student WHERE IndexNumber =@indexs";
//com.Parameters.AddWithValue("indexs", student.IndexNumber);
//var dr5 = com.ExecuteReader();
if (dont2 != null)
{
return(null);
}
//com.Parameters.AddWithValue("IdStuds", idStudies);
_context.Add(new Entities.Student()
{
FirstName = student.Firstname,
IndexNumber = student.IndexNumber,
LastName = student.Lastname,
BirthDate = DateTime.Now,
IdEnrollment = result1
});
_context.SaveChanges();
//com.CommandText = "INSERT INTO Student(IndexNumber, FirstName, LastName,Birthdate,IdEnrollment) VALUES (@Indexn,@FirstName, @LastName,@Birthdate,@Studies)";
////...
//com.Parameters.AddWithValue("FirstName", student.Firstname);
//com.Parameters.AddWithValue("Indexn", student.IndexNumber);
//com.Parameters.AddWithValue("LastName", student.Lastname);
//com.Parameters.AddWithValue("Birthdate", DateTime.Parse(student.BirthDate));
//com.Parameters.AddWithValue("Studies", idEnrollment);
////...
//com.ExecuteNonQuery();
var hello = _context.Enrollment.Select(e => e).Where(e => e.IdStudy == idStudies && e.Semester == 1 && e.StartDate == _context.Enrollment.Where(e => e.IdStudy == idStudies).Max(e => e.StartDate));
//com.CommandText = "Select * from enrollment where idstudy=@iDstuds and semester=1 and StartDate=(select max(StartDate) from Enrollment where IdStudy=@IdStuds)";
//tran.Commit();
//var dr4 = com.ExecuteReader();
var enrollment = new Models.Enrollment();
enrollment.Idenrollment = hello.First().IdEnrollment;
enrollment.semester = hello.First().Semester;
enrollment.IdStudy = hello.First().IdStudy;
enrollment.StartDate = hello.First().StartDate.ToString();
var er = new EnrollmentResponse(enrollment);
return(er);
}
}
public PromoteResponse PromoteStudents(PromoteRequest request)
{
//using (var client = new SqlConnection("Data Source=db-mssql.pjwstk.edu.pl;Initial Catalog=2019SBD;Integrated Security=True"))
//using (var com = new SqlCommand())
{
//client.Open();
//com.Connection = client;
var result = _context.Enrollment.Join(_context.Studies, p => p.IdStudy, v => v.IdStudy, (p, v) => new { p, v }).Where(d => d.p.Semester == request.Semester && d.v.Name.Equals(request.Studies)).FirstOrDefault();
//com.CommandText = "select * from Enrollment,Studies where Enrollment.IdStudy=Studies.IdStudy and Enrollment.semester=@semester and Studies.Name=@Studies";
//com.Parameters.AddWithValue("semester", request.Semester);
//com.Parameters.AddWithValue("Studies", request.Studies);
//var dr = com.ExecuteReader();
if (result == null)
{
return(null);
}
//dr.Close();
var name = new SqlParameter("@name", request.Studies);
var semester = new SqlParameter("@semester", request.Semester);
var list1 = _context.Database.ExecuteSqlCommand("exec Promotion @name, @semester", name, semester);
//com.CommandType = System.Data.CommandType.StoredProcedure;
//com.CommandText = "Promotion";
//com.ExecuteNonQuery();
//com.CommandType = System.Data.CommandType.Text;
var result3 = _context.Enrollment.Join(_context.Studies, p => p.IdStudy, v => v.IdStudy, (p, v) => new { p, v }).Where(d => d.p.Semester == request.Semester + 1 && d.v.Name.Equals(request.Studies)).FirstOrDefault();
//com.CommandText = "select * from Enrollment,Studies where Enrollment.IdStudy=Studies.IdStudy and Name=@Studies and Semester=@semestern";
//com.Parameters.AddWithValue("semestern", request.Semester + 1);
//var dr2 = com.ExecuteReader();
//dr2.Read();
var enrollment = new Models.Enrollment();
enrollment.IdStudy = result3.p.IdStudy;
enrollment.semester = result3.p.Semester;
var StartDate = result3.p.StartDate;
enrollment.StartDate = StartDate.ToString();
var promotion = new PromoteResponse(enrollment);
return(promotion);
}
}
//Create DB if none exists and populate test data
public static void initializeDB()
{
SQLiteConnection con = new SQLiteConnection(dbPath);
DateTime now = DateTime.Now;
try
{
con.CreateTable <Models.Assessment>();
con.CreateTable <Models.Course>();
con.CreateTable <Models.Degree>();
con.CreateTable <Models.Enrollment>();
con.CreateTable <Models.Instructor>();
con.CreateTable <Models.Student>();
con.CreateTable <Models.Term>();
}
catch (Exception x)
{
Console.WriteLine("Error creating tables: " + x.Message);
}
try
{
if (con.Table <Models.Student>().Count() == 0)
{
//Get start and end dates for test data
DateTime start = new DateTime(now.Year, now.Month, 1);
DateTime end = start.AddMonths(6).AddHours(23).AddMinutes(59).AddSeconds(59);
DateTime courseStart = start.AddDays(7);
DateTime courseEnd = courseStart.AddDays(14);
DateTime paAssessmentStart = courseStart.AddDays(12).AddHours(15).AddMinutes(30);
DateTime paAssessmentEnd = paAssessmentStart.AddDays(1);
DateTime oaAssessmentStart = paAssessmentEnd.AddHours(2).AddMinutes(30);
DateTime oaAssessmentEnd = oaAssessmentStart.AddHours(2);
List <DateTime> dates = new List <DateTime> {
start, end, courseStart, courseEnd, paAssessmentStart, paAssessmentEnd, oaAssessmentStart, oaAssessmentEnd
};
List <string> sqlDates = convertDates(dates);
//Insert test student
Models.Student student = new Models.Student
{
Name = "test",
Username = "******",
Password = "******"
};
con.Insert(student);
int studentId = student.StudentId;
//Insert test degree
Models.Degree degree = new Models.Degree
{
StudentId = studentId,
Name = "My Degree",
Active = 1
};
con.Insert(degree);
int degreeId = degree.DegreeId;
//Insert test term
Models.Term term = new Models.Term
{
DegreeId = degreeId,
Name = "First Term",
Start = sqlDates[0],
End = sqlDates[1]
};
con.Insert(term);
int termId = term.TermId;
//Insert test instructor
Models.Instructor instructor = new Models.Instructor()
{
Name = "Reg Garrett",
Email = "*****@*****.**",
Phone = "801-623-8070"
};
con.Insert(instructor);
int instructorId = instructor.InstructorId;
//Insert test course
Models.Course course = new Models.Course()
{
InstructorId = instructorId,
Name = "Intro to Xamarin Forms",
Status = "Enrolled",
Notes = "",
Start = sqlDates[2],
End = sqlDates[3],
StartNotification = 0,
EndNotification = 0
};
con.Insert(course);
int courseId = course.CourseId;
//Insert Assessments
Models.Assessment pa = new Models.Assessment()
{
CourseId = courseId,
Type = "Performance Assessment",
Start = sqlDates[4],
End = sqlDates[5],
StartNotification = 0,
EndNotification = 0
};
con.Insert(pa);
Models.Assessment oa = new Models.Assessment()
{
CourseId = courseId,
Type = "Objective Assessment",
Start = sqlDates[6],
End = sqlDates[7],
StartNotification = 0,
EndNotification = 0
};
con.Insert(oa);
Models.Enrollment enrollment = new Models.Enrollment()
{
TermId = termId,
CourseId = courseId
};
con.Insert(enrollment);
}
}
catch (Exception x)
{
Console.WriteLine("Error populating test data: " + x.Message);
}
finally
{
con.Close();
}
}
public async Task <(bool subject, bool updated, bool reachedTheatreCap)> UpdateEnrollment(Models.Enrollment enroll)
{
//check if subject exists
var subresult = await _dbContext.Subjects()
.FindOneAndUpdateAsync(
Builders <Models.Subject> .Filter.Eq(e => e.Name, enroll.Subject),
Builders <Models.Subject> .Update.Push(e => e.StudentEmail, enroll.Email)
);
if (subresult != null)
{
//check if theatre exists
var theatre = await _dbContext.Theatres().Find(x => x.Name == subresult.Lecture.Theatre)
.FirstOrDefaultAsync();
if (theatre != null && theatre.Capacity > 0)
{
//reduce theatre capacity
var theatreResult = await _dbContext.Theatres()
.FindOneAndUpdateAsync(
Builders <Models.Theatre> .Filter.Eq(e => e.Name, subresult.Lecture.Theatre),
Builders <Models.Theatre> .Update.Set(e => e.Capacity, theatre.Capacity - 1)
);
var result = await _dbContext.Students()
.FindOneAndUpdateAsync(
Builders <Models.Student> .Filter.Eq(e => e.Email, enroll.Email),
Builders <Models.Student> .Update.Push(e => e.Enrollments, enroll.Subject)
);
return(result != null ? (true, true, false) : (true, false, false));
}
return(false, false, true);
}
return(false, false, false);
}
public IActionResult AddStudent(Models.EnrollStudClass enroll)
{
var st = new Models.Student();
st.IndexNumber = enroll.IndexNumber;
st.FirstName = enroll.FirstName;
st.LastName = enroll.LastName;
st.BirthDate = DateTime.Parse(enroll.BirthDate);
using (var client = new SqlConnection(MyDatabase))
using (var command = new SqlCommand())
{
command.Connection = client;
var enrollment = new Models.Enrollment();
client.Open();
SqlTransaction tran = client.BeginTransaction();
try
{
command.CommandText = "SELECT Name FROM Studies where Name = @stud";
command.Parameters.AddWithValue("stud", enroll.Studies);
command.Parameters.AddWithValue("Fname", enroll.FirstName);
command.Parameters.AddWithValue("Lname", enroll.LastName);
command.Parameters.AddWithValue("Bdate", enroll.BirthDate);
command.Parameters.AddWithValue("id", enroll.IndexNumber);
command.Transaction = tran;
SqlDataReader qr = command.ExecuteReader();
while (!qr.Read())
{
tran.Rollback();
return(NotFound(400 + " - Nie ma takich studiów"));
}
qr.Close();
command.CommandText = "SELECT * FROM Enrollment inner join studies on Studies.IdStudy = Enrollment.IdStudy where Studies.Name = @stud and semester = 1";
qr = command.ExecuteReader();
while (!qr.HasRows)
{
qr.Close();
command.CommandText = "INSERT into enrollment(idEnrollment,semester,idstudy,startdate)values((select Max(idEnrollment)+1 from enrollment),1,(select idstudy from studies where name=@stud),GETDATE())";
command.ExecuteNonQuery();
tran.Commit();
}
qr.Close();
command.CommandText = "select * from student where IndexNumber=@id";
qr = command.ExecuteReader();
if (!qr.HasRows)
{
qr.Close();
command.CommandText = "insert into Student(IndexNumber,FirstName,LastName,BirthDate,IdEnrollment)values(@id,@Fname,@Lname, @Bdate,(select idEnrollment from enrollment inner join studies on enrollment.IdStudy=studies.IdStudy where studies.Name=@stud and semester=1))";
command.ExecuteNonQuery();
}
else
{
tran.Rollback();
return(NotFound("Id nie jest unikalne"));
}
qr.Close();
command.CommandText = "select IdEnrollment,StartDate,Semester from Enrollment inner join Studies on Enrollment.IdStudy=Studies.IdStudy where name=@stud and semester= 1";
qr = command.ExecuteReader();
if (qr.Read())
{
enrollment.idEnrollment = qr["idEnrollment"].ToString();
enrollment.Semester = qr["Semester"].ToString();
enrollment.StartDate = qr["StartDate"].ToString();
}
qr.Close();
tran.Commit();
}
catch (SqlException ex) {
tran.Rollback();
}
return(StatusCode(201, enrollment));
}
}
public void RemoveEnrollment(Models.Enrollment enrollment)
{
_context.Enrollment.Remove(enrollment);
_context.SaveChanges();
}
