public void AttendancefromFile(AspNetAttendance aspNetAttendance)
{
var dbTransection = db.Database.BeginTransaction();
String ErrorID = null;
try
{
HttpPostedFileBase file = Request.Files["Attendance"];
AspNetAttendance TodayAttendance = db.AspNetAttendances.Where(x => x.SubjectID == aspNetAttendance.SubjectID && x.Date == aspNetAttendance.Date).FirstOrDefault();
List <student_attendance> Student_Attendance = new List <student_attendance>();
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 3; rowIterator <= noOfRow; rowIterator++)
{
student_attendance stu_atten = new student_attendance();
string username;
try
{
username = workSheet.Cells[rowIterator, 1].Value.ToString();
}
catch (Exception)
{
username = "";
}
try
{
stu_atten.Status = workSheet.Cells[rowIterator, 3].Value.ToString();
}
catch (Exception)
{
stu_atten.Status = "Present";
}
try
{
stu_atten.Reason = workSheet.Cells[rowIterator, 4].Value.ToString();
}
catch (Exception)
{
stu_atten.Reason = "";
}
stu_atten.Id = db.AspNetUsers.Where(x => x.UserName == username).Select(x => x.Id).FirstOrDefault();
Student_Attendance.Add(stu_atten);
}
}
if (TodayAttendance == null)
{
AspNetAttendance Attendance = new AspNetAttendance();
Attendance.Date = aspNetAttendance.Date;
Attendance.SubjectID = aspNetAttendance.SubjectID;
db.AspNetAttendances.Add(Attendance);
db.SaveChanges();
int attendanceID = db.AspNetAttendances.Max(x => x.Id);
foreach (var studentattendance in Student_Attendance)
{
AspNetStudent_Attendance stu_attend = new AspNetStudent_Attendance();
stu_attend.StudentID = studentattendance.Id;
stu_attend.Status = studentattendance.Status;
stu_attend.Reason = studentattendance.Reason;
stu_attend.AttendanceID = attendanceID;
db.AspNetStudent_Attendance.Add(stu_attend);
}
}
else
{
foreach (var student_attendance in Student_Attendance)
{
AspNetStudent_Attendance stu_attend = db.AspNetStudent_Attendance.Where(x => x.AttendanceID == TodayAttendance.Id && x.StudentID == student_attendance.Id).FirstOrDefault();
if (stu_attend != null)
{
stu_attend.Status = student_attendance.Status;
stu_attend.Reason = student_attendance.Reason;
}
}
}
db.SaveChanges();
dbTransection.Commit();
}
catch (Exception)
{
dbTransection.Dispose();
ViewBag.Error = "Error Adding ID: " + ErrorID;
}
}
public JsonResult Attendance(attendance attendances)
{
var dbTransection = db.Database.BeginTransaction();
String ErrorID = null;
try
{
char check = attendances.SubjectID[0];
if (check != '0')
{
var date = DateTime.Now.Date;
int subjectID = Convert.ToInt32(attendances.SubjectID);
AspNetAttendance TodayAttendance = db.AspNetAttendances.Where(x => x.SubjectID == subjectID && x.Date == date).FirstOrDefault();
if (TodayAttendance == null)
{
AspNetAttendance Attendance = new AspNetAttendance();
Attendance.Date = DateTime.Now.Date;
Attendance.SubjectID = subjectID;
db.AspNetAttendances.Add(Attendance);
db.SaveChanges();
int attendanceID = db.AspNetAttendances.Max(x => x.Id);
foreach (var student_attendance in attendances.studentAttendance)
{
AspNetStudent_Attendance stu_attend = new AspNetStudent_Attendance();
stu_attend.StudentID = student_attendance.Id;
stu_attend.Status = student_attendance.Status;
stu_attend.Reason = student_attendance.Reason;
stu_attend.AttendanceID = attendanceID;
db.AspNetStudent_Attendance.Add(stu_attend);
}
}
else
{
foreach (var student_attendance in attendances.studentAttendance)
{
AspNetStudent_Attendance stu_attend = db.AspNetStudent_Attendance.Where(x => x.AttendanceID == TodayAttendance.Id && x.StudentID == student_attendance.Id).FirstOrDefault();
if (stu_attend != null)
{
stu_attend.Status = student_attendance.Status;
stu_attend.Reason = student_attendance.Reason;
}
}
}
}
else // Second Phase
{
var date = DateTime.Now.Date;
int ClassID = Convert.ToInt32(attendances.SubjectID);
var sub = db.AspNetSubjects.Where(x => x.ClassID == ClassID).Select(x => x.Id).ToList();
AspNetAttendance TodayAttendance = db.AspNetAttendances.Where(x => x.Date == date && sub.Contains(x.SubjectID)).FirstOrDefault();
if (TodayAttendance == null)
{
string Class = attendances.SubjectID;
Class = Class.Remove(0, 1);
int ClassId = Convert.ToInt32(Class);
var subject = (from subjects in db.AspNetSubjects
orderby subjects.Id descending
where subjects.ClassID == ClassId
select new { subjects.Id, subjects.SubjectName }).ToList();
foreach (var item in subject)
{
AspNetAttendance Attendance = new AspNetAttendance();
Attendance.Date = DateTime.Now.Date;
Attendance.SubjectID = item.Id;
db.AspNetAttendances.Add(Attendance);
db.SaveChanges();
int attendanceID = db.AspNetAttendances.Max(x => x.Id);
foreach (var student_attendance in attendances.studentAttendance)
{
AspNetStudent_Attendance stu_attend = new AspNetStudent_Attendance();
stu_attend.StudentID = student_attendance.Id;
stu_attend.Status = student_attendance.Status;
stu_attend.Reason = student_attendance.Reason;
stu_attend.AttendanceID = attendanceID;
db.AspNetStudent_Attendance.Add(stu_attend);
}
}
}
else
{
foreach (var student_attendance in attendances.studentAttendance)
{
AspNetStudent_Attendance stu_attend = db.AspNetStudent_Attendance.Where(x => x.AttendanceID == TodayAttendance.Id && x.StudentID == student_attendance.Id).FirstOrDefault();
if (stu_attend != null)
{
stu_attend.Status = student_attendance.Status;
stu_attend.Reason = student_attendance.Reason;
}
}
}
}
db.SaveChanges();
dbTransection.Commit();
}
catch (Exception)
{
dbTransection.Dispose();
ViewBag.Error = "Error Adding ID: " + ErrorID;
}
return(Json("Saved", JsonRequestBehavior.AllowGet));
}