public ActionResult UploadAttendance(int semesterId)
{
try
{
if (Request.Files.Count > 0)
{
using (var context = new CapstoneProjectEntities())
{
context.Configuration.AutoDetectChangesEnabled = false;
foreach (string file in Request.Files)
{
var fileContent = Request.Files[file];
if (fileContent != null && fileContent.ContentLength > 0)
{
var stream = fileContent.InputStream;
using (ExcelPackage package = new ExcelPackage(stream))
{
var ws = package.Workbook.Worksheets.First();
var totalCol = ws.Dimension.Columns;
var totalRow = ws.Dimension.Rows;
var studentCodeCol = 1;
var titleRow = 1;
var firstRecordRow = 2;
var savePoint = 0;
Dictionary <String, Student> wtf = new Dictionary <String, Student>();
var studentList = context.Students.AsNoTracking().ToList();
var semsesterName = context.RealSemesters.Find(semesterId).Semester.ToUpper();
var courseList = context.Courses.AsNoTracking().Where(q => q.Semester.ToUpper().Equals(semsesterName)).ToList();
var context2 = new CapstoneProjectEntities();
context2.Configuration.AutoDetectChangesEnabled = false;
for (int i = firstRecordRow; i <= totalRow; i++)
{
savePoint++;
var cellStudentRoll = ws.Cells[i, 1].Text.ToUpper();
var cellSubject = ws.Cells[i, 3].Text.ToUpper();
//var course = context.Courses.Where(q => q.Semester.ToUpper().Equals("FALL2017") && q.SubjectCode.ToUpper().Equals(cellSubject)).FirstOrDefault();
bool status = false;
if (ws.Cells[i, 2].Text.Equals("1"))
{
status = true;
}
var taker = ws.Cells[i, 4].Text;
var numberOfSlots = int.Parse(ws.Cells[i, 5].Text);
DateTime recoredTime = DateTime.Now;
try
{
recoredTime = DateTime.ParseExact(ws.Cells[i, 6].Text, "M/d/yy H:mm",
System.Globalization.CultureInfo.InvariantCulture);
}
catch (Exception ex)
{
return(Json(new { error = ex.Message, message = "Errors in uploaded file. Please recheck" }));
}
//savePoint++;
var student = studentList.Where(q => q.RollNumber.ToUpper().Equals(cellStudentRoll.ToUpper())).FirstOrDefault();
if (student == null)
{
if (!wtf.ContainsKey(cellStudentRoll))
{
//Thieu student
Student stu = new Student();
stu.RollNumber = cellStudentRoll;
wtf.Add(cellStudentRoll, stu);
context2.Students.Add(stu);
context2.SaveChanges();
}
}
var course = courseList.Where(q => q.SubjectCode.ToUpper().Equals(cellSubject)).FirstOrDefault();
//DELETE
//var listRemove = context.Attendances.Where(q => q.StudentId == student.Id && q.CourseId == course.Id && q.RecordTime == q.RecordTime).ToList();
//if (listRemove != null)
//{
// foreach(var att in listRemove)
// {
// context.Attendances.Remove(att);
// recordDel++;
// }
//}
//ADD
CapstoneProject.Attendance att = new CapstoneProject.Attendance();
att.CourseId = course.Id;
//att.NumberOfSlots = numberOfSlots;
att.StudentId = student.Id;
att.RecordTime = recoredTime;
att.Status = status;
att.Taker = taker;
context2.Attendances.Add(att);
if (savePoint == 1000)
{
context2.SaveChanges();
savePoint = 0;
context2.Dispose();
context2 = new CapstoneProjectEntities();
context2.Configuration.AutoDetectChangesEnabled = false;
}
if (i == totalRow)
{
context2.SaveChanges();
}
}
}
}
}
}
}
}
catch (Exception ex)
{
Response.StatusCode = (int)HttpStatusCode.BadRequest;
return(Json(new { error = ex.Message, message = "Errors in uploaded file. Please recheck" }));
}
return(Json(new { success = true, message = "Successful!" }));
}
public ActionResult ImportFromFap()
{
try
{
var dt = new DataTable();
var conn = new SqlConnection();
conn.ConnectionString =
//"Data Source=116.193.67.20;" +
"Data Source=10.23.0.77;" +
"Initial Catalog=AP_HCM;" +
"User Id=aphcm;" +
"Password=Kh@nhKT123456&;";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT a.RollNumber,a.Status,sub.SubjectCode,a.Taker,c.NumberOfSlots,a.RecordTime"
+ " FROM[AP_HCM].[dbo].[Attendances] a"
+ " Inner Join Schedules s on s.ScheduleID = a.ScheduleID"
+ " Inner Join Courses c on s.CourseID = c.CourseID"
+ " Inner Join Terms t on t.TermID = c.TermID"
+ " Inner Join Subjects sub on sub.SubjectID = c.SubjectID"
+ " Where t.SemesterName = 'Spring2018'";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
conn.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
var list = dt.AsEnumerable().Select(r => new TempAttendance()
{
RollNumber = (string)r["RollNumber"],
Status = (bool)r["Status"],
SubjectCode = (string)r["SubjectCode"],
Taker = (string)r["Taker"],
NumberOfSlots = (Byte?)r["NumberOfSlots"],
RecordTime = (DateTime)r["RecordTime"],
//TakeAttendance = (bool)r["TakeAttendance"],
}).ToList();
conn.Close();
var savePoint = 0;
using (var context = new CapstoneProjectEntities())
{
var studentList = context.Students.ToList();
var recordDel = 0;
var courseList = context.Courses.Where(q => q.Semester.ToUpper().Equals("SPRING2018_1")).ToList();
var last = list.Last();
Dictionary <String, Student> wtf = new Dictionary <String, Student>();
foreach (var item in list)
{
using (var context2 = new CapstoneProjectEntities())
{
savePoint++;
var student = studentList.Where(q => q.RollNumber.ToUpper().Equals(item.RollNumber.ToUpper())).FirstOrDefault();
if (student == null)
{
if (!wtf.ContainsKey(item.RollNumber))
{
Student stu = new Student();
stu.RollNumber = item.RollNumber;
wtf.Add(item.RollNumber, stu);
context2.Students.Add(stu);
context2.SaveChanges();
}
}
var course = courseList.Where(q => q.SubjectCode.ToUpper().Equals(item.SubjectCode)).FirstOrDefault();
var status = item.Status;
var recordTime = item.RecordTime;
var taker = item.Taker;
var numberOfSlots = item.NumberOfSlots;
//DELETE
//var listRemove = context.Attendances.Where(q => q.StudentId == student.Id && q.CourseId == course.Id && q.RecordTime == q.RecordTime).ToList();
//if (listRemove != null)
//{
// foreach(var att in listRemove)
// {
// context.Attendances.Remove(att);
// recordDel++;
// }
//}
//ADD
CapstoneProject.Attendance att = new CapstoneProject.Attendance();
att.CourseId = course.Id;
//att.NumberOfSlots = numberOfSlots;
att.StudentId = student.Id;
att.RecordTime = recordTime;
att.Status = status;
att.Taker = taker;
context2.Attendances.Add(att);
context2.SaveChanges();
}
}
}
}
catch (Exception ex)
{
return(Json(new { success = false, message = "Error in update." }));
}
return(Json(new { success = true, message = "Successful!" }));
}
