public ActionResult AddStaff_Subject_Detail(string[] Class_Ids, string[] Subject_Ids, string Staff_Id)
{
string[] nClassSectionIds = Class_Ids[0].Split(',');
string[] nSubjectIds = Subject_Ids[0].Split(',');
string sReturnText = string.Empty;
int nClassIds;
long nYear = GetAcademicYear();
Staff_Subject_Detail newStaffSubjectDetail = new Staff_Subject_Detail();
int nUser_Id;
using (var dbcontext = new SchoolERPDBContext())
{
//nUser_Id = dbcontext.Users.Where(x => x.User_Id == User.Identity.Name).ToList()[0].Id; ;
nUser_Id = 4;
}
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
for (int nClassSubLoopCount = 0; nClassSubLoopCount < nClassSectionIds.Count(); nClassSubLoopCount++)
{
int nSectionId = Convert.ToInt16(nClassSectionIds[nClassSubLoopCount]);
for (int nSubjectLoopCount = 0; nSubjectLoopCount < nSubjectIds.Count(); nSubjectLoopCount++)
{
nClassIds = dbcontext.Section.Where(x => x.Id == nSectionId).ToList()[0].Class_Id;
newStaffSubjectDetail.Class_Id = nClassIds;
newStaffSubjectDetail.Subject_Id = Convert.ToInt16(nSubjectIds[nSubjectLoopCount]);
newStaffSubjectDetail.Section_Id = Convert.ToInt16(nClassSectionIds[nClassSubLoopCount]);
newStaffSubjectDetail.Staff_Id = Convert.ToInt16(Staff_Id);
newStaffSubjectDetail.Academic_Year = GetAcademicYear();
newStaffSubjectDetail.Is_Active = true;
newStaffSubjectDetail.Created_By = nUser_Id;
newStaffSubjectDetail.Created_On = DateTime.Now;
if (dbcontext.Staff_Subject_Detail.Where(a => a.Class_Id == nClassIds && a.Subject_Id == newStaffSubjectDetail.Subject_Id && a.Section_Id == newStaffSubjectDetail.Section_Id && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Academic_Year == nYear && a.Staff_Id == newStaffSubjectDetail.Staff_Id).Count() == 0)
{
dbcontext.Staff_Subject_Detail.Add(newStaffSubjectDetail);
dbcontext.SaveChanges();
//return Json("OK", JsonRequestBehavior.AllowGet);
if (nClassSubLoopCount == nClassSectionIds.Count() - 1 && nSubjectLoopCount == nSubjectIds.Count() - 1)
{
transaction.Commit();
sReturnText = "OK";
}
}
else
{
return(Json("Already Exists", JsonRequestBehavior.AllowGet));
}
}
}
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
return(Json(sReturnText.ToString(), JsonRequestBehavior.AllowGet));
}
public ActionResult SaveExamTimeTable_ForClass(string[][] exam_timetable)
{
string sReturnText = string.Empty;
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
if (exam_timetable.Length > 0)
{
for (int nRowCount = 0; nRowCount < exam_timetable.Length; nRowCount++)
{
Exam_TimeTable newexam_TimeTable = new Exam_TimeTable();
int nSection_Id = Convert.ToInt32(exam_timetable[nRowCount][2]);
long nAcademic_Year = Convert.ToInt64(exam_timetable[nRowCount][5]);
int nClass_Id = Convert.ToInt32(exam_timetable[nRowCount][3]);
int nExam_Id = Convert.ToInt32(exam_timetable[nRowCount][4]);
int nSubject_Id = Convert.ToInt32(exam_timetable[nRowCount][1]);
DateTime dtExamDate = Convert.ToDateTime(exam_timetable[nRowCount][0]);
int nExam_Session = Convert.ToInt16(exam_timetable[nRowCount][6]);
if (dbcontext.Exam_TimeTable.Where(x => x.Section_Id == nSection_Id && x.Academic_Year == nAcademic_Year && x.Exam_Id == nExam_Id && x.Subject_Id == nSubject_Id && (x.Is_Deleted == null || x.Is_Deleted == false)).Count() == 0)
{
newexam_TimeTable.Academic_Year = nAcademic_Year;
newexam_TimeTable.Section_Id = nSection_Id;
newexam_TimeTable.Class_Id = dbcontext.Section.Where(x => x.Id == nSection_Id).FirstOrDefault().Class_Id;
newexam_TimeTable.Section_Id = nSection_Id;
newexam_TimeTable.Exam_Id = nExam_Id;
newexam_TimeTable.Exam_Date = dtExamDate;
newexam_TimeTable.Subject_Id = nSubject_Id;
newexam_TimeTable.Created_By = 5;
newexam_TimeTable.Created_On = DateTime.Now;
newexam_TimeTable.Is_Active = true;
newexam_TimeTable.Exam_Session = nExam_Session;
dbcontext.Exam_TimeTable.Add(newexam_TimeTable);
dbcontext.SaveChanges();
}
else
{
var exam_TimeTable_Id_ToBeModified = dbcontext.Exam_TimeTable.Where(x => x.Section_Id == nSection_Id && x.Academic_Year == nAcademic_Year && x.Exam_Id == nExam_Id && x.Subject_Id == nSubject_Id && (x.Is_Deleted == null || x.Is_Deleted == false)).FirstOrDefault().Id;
var exam_TimeTable_ToBeModified = dbcontext.Exam_TimeTable.Find(exam_TimeTable_Id_ToBeModified);
exam_TimeTable_ToBeModified.Exam_Date = dtExamDate;
exam_TimeTable_ToBeModified.Updated_By = 5;
exam_TimeTable_ToBeModified.Updated_On = DateTime.Now;
exam_TimeTable_ToBeModified.Exam_Session = nExam_Session;
dbcontext.Entry(exam_TimeTable_ToBeModified).State = EntityState.Modified;
dbcontext.SaveChanges();
}
if (nRowCount == (exam_timetable.Length - 1))
{
transaction.Commit();
sReturnText = "OK";
}
}
}
}
catch (Exception ex)
{
transaction.Rollback();
sReturnText = ex.InnerException.Message.ToString();
}
}
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
public ActionResult SaveTimeTable_For_Staff(string[][] data)
{
string sReturnText = string.Empty;
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
if (data.Length > 0)
{
int nStaff_Id = Convert.ToInt32(data[0][8]);
long nAcademic_Year = Convert.ToInt64(data[0][9]);
for (int nRowCount = 0; nRowCount < data.Length; nRowCount++)
{
Staff_TimeTable staff_TimeTable = new Staff_TimeTable();
if (dbcontext.Staff_TimeTable.Where(x => x.Staff_Id == nStaff_Id && x.Academic_Year == nAcademic_Year && (x.Is_Deleted == null || x.Is_Deleted == false) && x.Week == (nRowCount + 1)).Count() == 0)
{
staff_TimeTable.Academic_Year = nAcademic_Year;
staff_TimeTable.Staff_Id = nStaff_Id;
//staff_TimeTable.Class_Id = dbcontext.Section.Where(x => x.Id == nSection_Id).FirstOrDefault().Class_Id;
staff_TimeTable.Section_Id_Period1 = Convert.ToInt16(data[nRowCount][0]);
staff_TimeTable.Section_Id_Period2 = Convert.ToInt16(data[nRowCount][1]);
staff_TimeTable.Section_Id_Period3 = Convert.ToInt16(data[nRowCount][2]);
staff_TimeTable.Section_Id_Period4 = Convert.ToInt16(data[nRowCount][3]);
staff_TimeTable.Section_Id_Period5 = Convert.ToInt16(data[nRowCount][4]);
staff_TimeTable.Section_Id_Period6 = Convert.ToInt16(data[nRowCount][5]);
staff_TimeTable.Section_Id_Period7 = Convert.ToInt16(data[nRowCount][6]);
staff_TimeTable.Section_Id_Period8 = Convert.ToInt16(data[nRowCount][7]);
staff_TimeTable.Week = nRowCount + 1;
staff_TimeTable.Created_By = 5;
staff_TimeTable.Created_On = DateTime.Now;
staff_TimeTable.Is_Active = true;
dbcontext.Staff_TimeTable.Add(staff_TimeTable);
dbcontext.SaveChanges();
}
else
{
var staff_TimeTable_Id_ToBeModified = dbcontext.Staff_TimeTable.Where(x => x.Staff_Id == nStaff_Id && x.Academic_Year == nAcademic_Year && (x.Is_Deleted == null || x.Is_Deleted == false) && x.Week == (nRowCount + 1)).FirstOrDefault().Id;
var Staff_TimeTable_ToBeModified = dbcontext.Staff_TimeTable.Find(staff_TimeTable_Id_ToBeModified);
Staff_TimeTable_ToBeModified.Section_Id_Period1 = Convert.ToInt16(data[nRowCount][0]);
Staff_TimeTable_ToBeModified.Section_Id_Period2 = Convert.ToInt16(data[nRowCount][1]);
Staff_TimeTable_ToBeModified.Section_Id_Period3 = Convert.ToInt16(data[nRowCount][2]);
Staff_TimeTable_ToBeModified.Section_Id_Period4 = Convert.ToInt16(data[nRowCount][3]);
Staff_TimeTable_ToBeModified.Section_Id_Period5 = Convert.ToInt16(data[nRowCount][4]);
Staff_TimeTable_ToBeModified.Section_Id_Period6 = Convert.ToInt16(data[nRowCount][5]);
Staff_TimeTable_ToBeModified.Section_Id_Period7 = Convert.ToInt16(data[nRowCount][6]);
Staff_TimeTable_ToBeModified.Section_Id_Period8 = Convert.ToInt16(data[nRowCount][7]);
Staff_TimeTable_ToBeModified.Updated_By = 5;
Staff_TimeTable_ToBeModified.Updated_On = DateTime.Now;
dbcontext.Entry(Staff_TimeTable_ToBeModified).State = EntityState.Modified;
dbcontext.SaveChanges();
}
if (nRowCount == (data.Length - 1))
{
transaction.Commit();
sReturnText = "OK";
}
}
}
}
catch (Exception ex)
{
transaction.Rollback();
sReturnText = ex.InnerException.Message.ToString();
}
}
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
public JsonResult AddClass_TimeTable(int Section_Id, long Academic_Year)
{
//List<Class_TimeTableList_ViewModel> Class_TimeTableList_ViewModelobj = new List<Model.ViewModel.Class_TimeTableList_ViewModel>();
List <Class_TimeTable> Class_TimeTableList_ViewModelobj = new List <Class_TimeTable>();
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
PropertyInfo[] proeprties = typeof(Class_TimeTable).GetProperties();
if (dbcontext.Class_TimeTable.Where(x => x.Section_Id == Section_Id && x.Academic_Year == Academic_Year && (x.Is_Deleted == null || x.Is_Deleted == false)).Count() == 0)
{
for (int nCount = 0; nCount <= 5; nCount++)
{
Class_TimeTable classTimeTableViewModel = new Class_TimeTable();
foreach (PropertyInfo property in proeprties)
{
if (property.Name == "Week")
{
if (nCount == 0)
{
classTimeTableViewModel.Week = 1;
}
else if (nCount == 1)
{
classTimeTableViewModel.Week = 2;
}
else if (nCount == 2)
{
classTimeTableViewModel.Week = 3;
}
else if (nCount == 3)
{
classTimeTableViewModel.Week = 4;
}
else if (nCount == 4)
{
classTimeTableViewModel.Week = 5;
}
else if (nCount == 5)
{
classTimeTableViewModel.Week = 6;
}
}
//if (nCount == 0)
//{
//}
//else
//{
//}
}
Class_TimeTableList_ViewModelobj.Add(classTimeTableViewModel);
}
return(Json(Class_TimeTableList_ViewModelobj.ToArray(), JsonRequestBehavior.AllowGet));
}
else
{
var existingClassTimeTable = dbcontext.Class_TimeTable.Where(x => x.Section_Id == Section_Id && x.Academic_Year == Academic_Year && (x.Is_Deleted == null || x.Is_Deleted == false)).ToList();
return(Json(existingClassTimeTable.ToArray(), JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
return(Json(ex.InnerException.Message.ToString(), JsonRequestBehavior.AllowGet));
}
}
}
}
public ActionResult AddHoliday(string Name, string From_Date, string To_Date, string Year)
{
DataTable dt = new DataTable();
int nUser_Id;
int nYear = Convert.ToInt16(Year);
using (var dbcontext = new SchoolERPDBContext())
{
//nUser_Id = dbcontext.Users.Where(x => x.User_Id == User.Identity.Name).ToList()[0].Id; ;
nUser_Id = 5;
}
DateTime dtFromDate = DateTime.ParseExact(From_Date, "dd/MM/yyyy", null);
DateTime dtToDate = DateTime.ParseExact(To_Date, "dd/MM/yyyy", null);
TimeSpan ts = dtToDate - dtFromDate;
int nDays = Convert.ToInt16(ts.TotalDays) + 1;
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
for (int nHolidayCount = 0; nHolidayCount < nDays; nHolidayCount++)
{
Holiday newHoliday = new Holiday();
newHoliday.Name = Name;
newHoliday.Academic_Year = nYear;
newHoliday.Is_Active = true;
newHoliday.Created_By = nUser_Id;
newHoliday.Created_On = DateTime.Now;
newHoliday.Holiday_Date = dtFromDate.AddDays(nHolidayCount);
newHoliday.From_Date = dtFromDate;
newHoliday.To_Date = dtToDate;
//if (dbcontext.Holiday.Where(a => a.Name.Replace(" ", "").Trim().ToString() == Name.Replace(" ", "").Trim().ToString() && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Academic_Year == nYear).Count() == 0)
if (dbcontext.Holiday.Where(a => a.From_Date >= newHoliday.Holiday_Date && a.To_Date <= newHoliday.Holiday_Date && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Academic_Year == nYear).Count() == 0)
{
dbcontext.Holiday.Add(newHoliday);
dbcontext.SaveChanges();
//return Json("OK", JsonRequestBehavior.AllowGet);
}
else
{
return(Json("Holiday Already Exists.", JsonRequestBehavior.AllowGet));
}
if (nHolidayCount == (nDays - 1))
{
transaction.Commit();
}
}
}
catch (Exception e)
{
transaction.Rollback();
return(Json("Failure", JsonRequestBehavior.AllowGet));
}
}
return(Json("OK", JsonRequestBehavior.AllowGet));
}
//return View();
}
public ActionResult AddStaffAttendance(string Staff_Id, string From_Date, string To_Date, string Reason)
{
DataTable dt = new DataTable();
int nUser_Id;
string sReturnText = string.Empty;
long nYear = GetAcademicYear();
using (var dbcontext = new SchoolERPDBContext())
{
//nUser_Id = dbcontext.Users.Where(x => x.User_Id == User.Identity.Name).ToList()[0].Id; ;
nUser_Id = 5;
}
DateTime dtFromDate = DateTime.ParseExact(From_Date, "dd/MM/yyyy", null);
DateTime dtToDate = DateTime.ParseExact(To_Date, "dd/MM/yyyy", null);
TimeSpan ts = dtToDate - dtFromDate;
int nDays = Convert.ToInt16(ts.TotalDays) + 1;
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
for (int nHolidayCount = 0; nHolidayCount < nDays; nHolidayCount++)
{
Staff_Attendance newStaffAttendance = new Staff_Attendance();
newStaffAttendance.Staff_Id = Convert.ToInt16(Staff_Id);
newStaffAttendance.Academic_Year = nYear;
newStaffAttendance.Is_Active = true;
newStaffAttendance.Created_By = nUser_Id;
newStaffAttendance.Created_On = DateTime.Now;
newStaffAttendance.Leave_Date = dtFromDate.AddDays(nHolidayCount);
newStaffAttendance.From_Date = dtFromDate;
newStaffAttendance.To_Date = dtToDate;
newStaffAttendance.Reason = Reason;
//if (dbcontext.Holiday.Where(a => a.Name.Replace(" ", "").Trim().ToString() == Name.Replace(" ", "").Trim().ToString() && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Academic_Year == nYear).Count() == 0)
if (dbcontext.Staff_Attendance.Where(x => x.Staff_Id == newStaffAttendance.Staff_Id && x.Leave_Date == newStaffAttendance.Leave_Date && (x.Is_Deleted == null || x.Is_Deleted == null) && x.Academic_Year == nYear).Count() == 0)
//if (dbcontext.Holiday.Where(a => a.From_Date >= newStaffAttendance.Leave_Date && a.To_Date <= newStaffAttendance.Leave_Date && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Academic_Year == nYear).Count() == 0)
{
dbcontext.Staff_Attendance.Add(newStaffAttendance);
dbcontext.SaveChanges();
sReturnText = "OK";
//return Json("OK", JsonRequestBehavior.AllowGet);
}
else
{
return(Json("Holiday Already Exists.", JsonRequestBehavior.AllowGet));
}
if (nHolidayCount == (nDays - 1))
{
transaction.Commit();
}
}
}
catch (Exception e)
{
transaction.Rollback();
return(Json("Failure", JsonRequestBehavior.AllowGet));
}
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetAttendanceForClass(string class_Id, string section_Id, string term_Id, string year)
{
List <Student> studentList = new List <Student>();
int nNoOfTermDays;
DataTable dtStudentAttendance = new DataTable();
TempData["Class_Id_For_Attendance"] = class_Id;
TempData.Keep("Class_Id_For_Attendance");
TempData["Section_Id_For_Attendance"] = section_Id;
TempData.Keep("Section_Id_For_Attendance");
TempData["Term_Id_For_Attendance"] = term_Id;
TempData.Keep("Term_Id_For_Attendance");
TempData["Year_For_Attendance"] = year;
TempData.Keep("Year_For_Attendance");
int nYear = Convert.ToInt16(year);
int nTermId = Convert.ToInt16(term_Id);
int nClassId = Convert.ToInt16(class_Id);
int nSectionId = Convert.ToInt16(section_Id);
DateTime termStartDate;
DateTime termEndDate;
List <Attendance_ViewModel> attendance_ViewModelList = new List <Attendance_ViewModel>();
using (var dbcontext = new SchoolERPDBContext())
{
studentList = (from stu in dbcontext.Student
where stu.Academic_Year == nYear && (stu.Is_Deleted == false) && stu.Class_Id == nClassId && stu.Section_Id == nSectionId
select new
{
Id = stu.Student_Id,
Name = stu.First_Name + " " + stu.Last_Name,
Roll_No = stu.Roll_No
}).ToList().Select(x => new Student()
{
Student_Id = x.Id,
First_Name = x.Name,
Roll_No = x.Roll_No
}).ToList();
}
int nStudentCount = studentList.Count;
using (var dbcontext = new SchoolERPDBContext())
{
termEndDate = dbcontext.Term.Where(x => x.Id == nTermId).ToList()[0].To_Date;
termStartDate = dbcontext.Term.Where(x => x.Id == nTermId).ToList()[0].From_Date;
}
TimeSpan difference = termEndDate - termStartDate;
int nDays = Convert.ToInt16(difference.TotalDays);
nDays = nDays + 1;
long nStudent_Id;
PropertyInfo[] proeprties = typeof(Attendance_ViewModel).GetProperties();
for (int nCount = 0; nCount <= studentList.Count; nCount++)
{
Attendance_ViewModel attendanceViewModel = new Attendance_ViewModel();
int nValue;
DateTime dDateToBeCompared;
if (nCount > 0)
{
nStudent_Id = studentList[nCount - 1].Student_Id;
}
else
{
nStudent_Id = 0;
}
foreach (PropertyInfo property in proeprties)
{
string sDay = property.Name;
if (property.Name == "Roll_No")
{
if (nCount == 0)
{
property.SetValue(attendanceViewModel, property.Name);
}
else
{
attendanceViewModel.Roll_No = studentList[nCount - 1].Roll_No;
}
}
else if (property.Name == "Student_Name")
{
if (nCount == 0)
{
property.SetValue(attendanceViewModel, property.Name);
}
else
{
attendanceViewModel.Student_Name = studentList[nCount - 1].First_Name + " " + studentList[nCount - 1].Last_Name;
}
}
else if (property.Name == "Student_Id")
{
nStudentIdArr = studentList.ToList().Select(l => l.Student_Id).Distinct().ToArray();
if (nCount == 0)
{
property.SetValue(attendanceViewModel, "STUDENT ID");
}
else
{
attendanceViewModel.Student_Id = Convert.ToString(studentList[nCount - 1].Student_Id);
}
TempData["StudentIdArr"] = nStudentIdArr;
TempData.Keep("StudentIdArr");
continue;
}
if (sDay.Contains("Day_"))
{
string sHolidayReason = string.Empty;;
sDay = sDay.Replace("Day_", "");
dDateToBeCompared = termStartDate.AddDays(Convert.ToInt16(sDay) - 1);
if (nDays >= Convert.ToInt16(sDay))
{
if (nCount == 0)
{
property.SetValue(attendanceViewModel, dDateToBeCompared.Day + "-" + dDateToBeCompared.ToString("MMM").ToUpper());
continue;
}
else
{
using (var dbcontext = new SchoolERPDBContext())
{
if (dbcontext.Holiday.Where((x => x.Holiday_Date == dDateToBeCompared && x.Academic_Year == nYear && (x.Is_Deleted == false || x.Is_Deleted == null))).ToList().Count() > 0)
{
sHolidayReason = dbcontext.Holiday.Where((x => x.Holiday_Date == dDateToBeCompared && x.Academic_Year == nYear && (x.Is_Deleted == false || x.Is_Deleted == null))).ToList()[0].Name;
sHolidayReason = "H" + "-" + sHolidayReason;
}
else
{
if (dbcontext.Attendance.Where(x => x.Leave_Date == dDateToBeCompared && x.Academic_Year == nYear && (x.Is_Deleted == false || x.Is_Deleted == null) && x.Student_Id == nStudent_Id).ToList().Count() > 0)
{
sHolidayReason = dbcontext.Attendance.Where(x => x.Leave_Date == dDateToBeCompared && x.Academic_Year == nYear && (x.Is_Deleted == false || x.Is_Deleted == null) && x.Student_Id == nStudent_Id).ToList()[0].Leave_Reason;
}
}
}
}
if (sHolidayReason == string.Empty)
{
property.SetValue(attendanceViewModel, "P");
}
else
{
property.SetValue(attendanceViewModel, sHolidayReason);
}
}
}
}
attendance_ViewModelList.Add(attendanceViewModel);
}
return(Json(attendance_ViewModelList.ToArray(), JsonRequestBehavior.AllowGet));
}
public JsonResult SaveAttendanceForClass(List <string[]> myData)
{
int nUser_Id;
nStudentIdArr = (long[])TempData.Peek("StudentIdArr");
string sReturnText = string.Empty;
DateTime termEndDate;
DateTime termStartDate;
using (var dbcontext = new SchoolERPDBContext())
{
//nUser_Id = dbcontext.Users.Where(x => x.User_Id == User.Identity.Name).ToList()[0].Id; ;
nUser_Id = 4;
}
int nCount = myData.ToList().Count;
int nTerm_Id = Convert.ToInt16(TempData.Peek("Term_Id_For_Attendance"));
int nLoopCount = 0;
using (var dbcontext = new SchoolERPDBContext())
{
termEndDate = dbcontext.Term.Where(x => x.Id == nTerm_Id).ToList()[0].To_Date;
termStartDate = dbcontext.Term.Where(x => x.Id == nTerm_Id).ToList()[0].From_Date;
}
TimeSpan difference = termEndDate - termStartDate;
int nDays = Convert.ToInt16(difference.TotalDays);
nDays = nDays + 1;
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
for (int iAttendanceLoopCount = 1; iAttendanceLoopCount < myData.ToList().Count(); iAttendanceLoopCount++)
{
for (int nColumnCount = 3; nColumnCount < 3 + nDays; nColumnCount++)
{
Attendance newAttendance = new Attendance();
newAttendance.Academic_Year = Convert.ToInt32(GetAcademicYear());
if (Convert.ToString(myData[iAttendanceLoopCount][nColumnCount]) != "P" && Convert.ToString(myData[iAttendanceLoopCount][nColumnCount]) != "H" && Convert.ToString(myData[iAttendanceLoopCount][nColumnCount]) != "PH" && Convert.ToString(myData[iAttendanceLoopCount][nColumnCount]) != null)
{
newAttendance.Class_Id = Convert.ToInt16(TempData.Peek("Class_Id_For_Attendance"));
newAttendance.Section_Id = Convert.ToInt16(TempData.Peek("Section_Id_For_Attendance"));
newAttendance.Leave_Date = termStartDate.AddDays(nColumnCount - 3);
newAttendance.Student_Id = nStudentIdArr[iAttendanceLoopCount - 1];
newAttendance.Leave_Reason = myData[iAttendanceLoopCount][nColumnCount];
newAttendance.Is_Active = true;
//newAttendance.Is_Deleted = false;
newAttendance.Term_Id = Convert.ToInt16(TempData.Peek("Term_Id_For_Attendance"));
newAttendance.Created_By = 4;
newAttendance.Created_On = DateTime.Now;
newAttendance.Academic_Year = Convert.ToInt16(TempData.Peek("Year_For_Attendance"));
if (dbcontext.Attendance.Where(a => a.Student_Id == newAttendance.Student_Id && a.Academic_Year == newAttendance.Academic_Year && a.Leave_Date == newAttendance.Leave_Date && (a.Is_Deleted == false || a.Is_Deleted == null)).Count() == 0)
{
dbcontext.Attendance.Add(newAttendance);
dbcontext.SaveChanges();
}
else
{
var attendanceId = dbcontext.Attendance.Where(a => a.Student_Id == newAttendance.Student_Id && a.Academic_Year == newAttendance.Academic_Year && a.Leave_Date == newAttendance.Leave_Date && (a.Is_Deleted == false || a.Is_Deleted == null)).ToList()[0].Id;
Attendance attendanceToBeUpdated = dbcontext.Attendance.Find(attendanceId);
attendanceToBeUpdated.Leave_Reason = myData[iAttendanceLoopCount][nColumnCount];
attendanceToBeUpdated.Is_Active = true;
dbcontext.Entry(attendanceToBeUpdated).State = EntityState.Modified;
dbcontext.SaveChanges();
}
}
else
{
newAttendance.Leave_Date = termStartDate.AddDays(nColumnCount - 3);
string LeaveReason = myData[iAttendanceLoopCount][nColumnCount];
if (dbcontext.Attendance.Where(a => a.Student_Id == newAttendance.Student_Id && a.Academic_Year == newAttendance.Academic_Year && a.Leave_Date == newAttendance.Leave_Date && (a.Is_Deleted == false || a.Is_Deleted == null) && a.Leave_Reason != LeaveReason).Count() > 0)
{
var attendanceId = dbcontext.Attendance.Where(a => a.Student_Id == newAttendance.Student_Id && a.Academic_Year == newAttendance.Academic_Year && a.Leave_Date == newAttendance.Leave_Date && (a.Is_Deleted == false || a.Is_Deleted == null)).ToList()[0].Id;
Attendance attendanceToBeUpdated = dbcontext.Attendance.Find(attendanceId);
attendanceToBeUpdated.Leave_Reason = myData[iAttendanceLoopCount][nColumnCount];
attendanceToBeUpdated.Is_Active = false;
attendanceToBeUpdated.Is_Deleted = true;
dbcontext.Entry(attendanceToBeUpdated).State = EntityState.Modified;
dbcontext.SaveChanges();
}
}
if (iAttendanceLoopCount == myData.ToList().Count() - 1 && nColumnCount == 3 + (nDays - 1))
{
transaction.Commit();
sReturnText = "OK";
}
}
}
}
catch (Exception ex)
{
transaction.Rollback();
}
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
}
public JsonResult PayFeesForStudent(Fee_Payment fee_Payment)
{
long nYear = GetAcademicYear();
string sReturnText = string.Empty;
fee_Payment.Created_On = DateTime.Now;
fee_Payment.Created_By = 4;
fee_Payment.Academic_Year = nYear;
fee_Payment.Is_Active = true;
fee_Payment.Collected_by = "devi";
//fee_Payment.Next_due_date = (fee_Payment.Next_due_date == :
fee_Payment.File_Name = "FEE_RECIPT" + fee_Payment.Student_id + "_" + fee_Payment.Recipt_no + ".pdf";
try
{
using (var dbcontext = new SchoolERPDBContext())
{
if (dbcontext.Fee_Payment.Where(a => a.Student_id == fee_Payment.Student_id && a.Frequency == fee_Payment.Frequency && a.Academic_Year == nYear && (a.Is_Deleted == false || a.Is_Deleted == null)).Count() == 0)
{
dbcontext.Fee_Payment.Add(fee_Payment);
dbcontext.SaveChanges();
System.IO.FileStream fs;
Document pdfDoc;
pdfDoc = new Document(PageSize.A2, 0f, 0f, 80f, 30f);
string severFilePath = Server.MapPath("~/views//billing//");
if (!Directory.Exists(severFilePath))
{ // if it doesn't exist, create
System.IO.Directory.CreateDirectory(severFilePath);
}
//fs = new FileStream(severFilePath + "//" + ""+ "_" + "FEE_RECIPT" + fee_Payment.Student_id + "_" + "" + "_Triplicate" + ".pdf", FileMode.Create);
fs = new FileStream(severFilePath + "//" + fee_Payment.File_Name, FileMode.Create);
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, fs);
writer.CloseStream = false;
iTextSharp.text.Font NormalFont = FontFactory.GetFont("Arial", 12, BaseColor.BLUE);
Paragraph paragraph = new Paragraph(" INVOICE (Original)");
pdfDoc.Open();
pdfDoc.Add(PDFGenerateController.GenerateFeesPaymentRecipt(fee_Payment));
pdfDoc.Close();
// Close the writer instance
writer.Close();
// Always close open filehandles explicity
fs.Close();
//oPDFGenerateController.GenerateFeesPaymentRecipt(fee_Payment);
sReturnText = "OK";
}
else
{
sReturnText = "Already Paid";
}
}
}
catch (Exception ex)
{
sReturnText = ex.Message.ToString();
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
public JsonResult SaveFeeConfiguration(List <string[]> myData, string Academic_Year)
{
Fee_Configuration newFeeConfig = new Fee_Configuration();
int nUser_Id;
nFeeIdArr = (int[])TempData.Peek("FeeIdArr");
string sReturnText = string.Empty;
using (var dbcontext = new SchoolERPDBContext())
{
//nUser_Id = dbcontext.Users.Where(x => x.User_Id == User.Identity.Name).ToList()[0].Id; ;
nUser_Id = 4;
}
int nCount = myData.ToList().Count;
int nLoopCount = 0;
//var matchingvalues = myData.Where(stringToCheck => stringToCheck.Contains("Hostel Fees"));
string hostelFeesIindex = Convert.ToString(myData.FindIndex(s => s.Contains("Hostel Fees")));
//var hostelFeesIndex = myData.FindIndex(matchingvalues);
string schoolBusIndex = Convert.ToString(myData.FindIndex(s => s.Contains("School Bus")));
using (var dbcontext = new SchoolERPDBContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
for (int i = 0; i < nCount - 1; i++)
{
if (myData[i][0] != string.Empty && myData[i][0] != null)
{
for (int j = 0; j < 4; j++)
{
newFeeConfig.Class_Id = Convert.ToInt16(TempData.Peek("Class_Id"));
newFeeConfig.Academic_Year = Convert.ToInt64(myData[i][1]);
newFeeConfig.Fee_Id = Convert.ToInt16(nFeeIdArr[i]);
newFeeConfig.Is_Active = true;
if (j == 0)
{
newFeeConfig.Frequency = 1;
newFeeConfig.Amount = (myData[i][2] == "") ? 0 : Convert.ToDecimal(myData[i][2]);
newFeeConfig.Total = Convert.ToDecimal(myData[nCount - 1][2]);
if (hostelFeesIindex != string.Empty)
{
int nhostelFeesIindex = Convert.ToInt16(hostelFeesIindex);
newFeeConfig.Total_Excluding_HostelFees = Convert.ToDecimal(myData[nCount - 1][2]) - Convert.ToDecimal(myData[nhostelFeesIindex][2]);
}
if (schoolBusIndex != string.Empty)
{
int nschoolBusIndex = Convert.ToInt16(schoolBusIndex);
newFeeConfig.Total_Excluding_Bus_Fees = Convert.ToDecimal(myData[nCount - 1][2]) - Convert.ToDecimal(myData[nschoolBusIndex][2]);
}
}
else if (j == 1)
{
newFeeConfig.Frequency = 2;
newFeeConfig.Amount = (myData[i][3] == "") ? 0 : Convert.ToDecimal(myData[i][3]);
newFeeConfig.Total = Convert.ToDecimal(myData[nCount - 1][3]);
if (hostelFeesIindex != string.Empty)
{
int nhostelFeesIindex = Convert.ToInt16(hostelFeesIindex);
newFeeConfig.Total_Excluding_HostelFees = Convert.ToDecimal(myData[nCount - 1][3]) - Convert.ToDecimal(myData[nhostelFeesIindex][3]);
}
if (schoolBusIndex != string.Empty)
{
int nschoolBusIndex = Convert.ToInt16(schoolBusIndex);
newFeeConfig.Total_Excluding_Bus_Fees = Convert.ToDecimal(myData[nCount - 1][3]) - Convert.ToDecimal(myData[nschoolBusIndex][3]);
}
}
else if (j == 2)
{
newFeeConfig.Frequency = 3;
newFeeConfig.Amount = (myData[i][4] == "") ? 0 : Convert.ToDecimal(myData[i][4]);
newFeeConfig.Total = Convert.ToDecimal(myData[nCount - 1][4]);
if (hostelFeesIindex != string.Empty)
{
int nhostelFeesIindex = Convert.ToInt16(hostelFeesIindex);
newFeeConfig.Total_Excluding_HostelFees = Convert.ToDecimal(myData[nCount - 1][4]) - Convert.ToDecimal(myData[nhostelFeesIindex][4]);
}
if (schoolBusIndex != string.Empty)
{
int nschoolBusIndex = Convert.ToInt16(schoolBusIndex);
newFeeConfig.Total_Excluding_Bus_Fees = Convert.ToDecimal(myData[nCount - 1][4]) - Convert.ToDecimal(myData[nschoolBusIndex][4]);
}
}
else
{
newFeeConfig.Frequency = 4;
newFeeConfig.Amount = (myData[i][5] == "") ? 0 : Convert.ToDecimal(myData[i][5]);
newFeeConfig.Total = Convert.ToDecimal(myData[nCount - 1][5]);
if (hostelFeesIindex != string.Empty)
{
int nhostelFeesIindex = Convert.ToInt16(hostelFeesIindex);
newFeeConfig.Total_Excluding_HostelFees = Convert.ToDecimal(myData[nCount - 1][5]) - Convert.ToDecimal(myData[nhostelFeesIindex][5]);
}
if (schoolBusIndex != string.Empty)
{
int nschoolBusIndex = Convert.ToInt16(schoolBusIndex);
newFeeConfig.Total_Excluding_Bus_Fees = Convert.ToDecimal(myData[nCount - 1][5]) - Convert.ToDecimal(myData[nschoolBusIndex][5]);
}
}
newFeeConfig.Created_By = nUser_Id;
newFeeConfig.Created_On = DateTime.Now;
if (dbcontext.Fee_Configuration.Where(a => a.Class_Id == newFeeConfig.Class_Id && a.Fee_Id == newFeeConfig.Fee_Id && a.Academic_Year == newFeeConfig.Academic_Year && a.Frequency == newFeeConfig.Frequency && (a.Is_Deleted == false || a.Is_Deleted == null)).Count() == 0)
{
dbcontext.Fee_Configuration.Add(newFeeConfig);
dbcontext.SaveChanges();
if (nCount - 3 == i && j == 3)
{
transaction.Commit();
sReturnText = "OK";
}
}
else
{
var feeConfigId = dbcontext.Fee_Configuration.Where(x => x.Fee_Id == newFeeConfig.Fee_Id && x.Class_Id == newFeeConfig.Class_Id && x.Academic_Year == newFeeConfig.Academic_Year && x.Frequency == newFeeConfig.Frequency && (x.Is_Deleted == false || x.Is_Deleted == null)).FirstOrDefault().Id;
Fee_Configuration feeConfigToBeUpdated = dbcontext.Fee_Configuration.Find(feeConfigId);
feeConfigToBeUpdated.Fee_Id = newFeeConfig.Fee_Id;
feeConfigToBeUpdated.Class_Id = newFeeConfig.Class_Id;
feeConfigToBeUpdated.Frequency = newFeeConfig.Frequency;
feeConfigToBeUpdated.Amount = newFeeConfig.Amount;
feeConfigToBeUpdated.Academic_Year = newFeeConfig.Academic_Year;
feeConfigToBeUpdated.Total = newFeeConfig.Total;
feeConfigToBeUpdated.Is_Active = true;
feeConfigToBeUpdated.Total_Excluding_Bus_Fees = newFeeConfig.Total_Excluding_Bus_Fees;
feeConfigToBeUpdated.Total_Excluding_HostelFees = newFeeConfig.Total_Excluding_HostelFees;
dbcontext.Entry(feeConfigToBeUpdated).State = EntityState.Modified;
dbcontext.SaveChanges();
if (nCount - 3 == i && j == 3)
{
transaction.Commit();
sReturnText = "Updated";
}
}
}
}
}
}
catch (Exception ex)
{
transaction.Rollback();
}
}
return(Json(sReturnText, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetFeeConfiguration(string Class_Id, string Academic_Year)
{
List <FeeConfiguration_ViewModel> feeConfiguration_ViewModel = new List <Model.ViewModel.FeeConfiguration_ViewModel>();
FeeConfiguration_ViewModel emptyFeeConfiguration_ViewModel = new FeeConfiguration_ViewModel();
List <FeeConfiguration_ViewModel> editModefeeConfiguration_ViewModel = new List <Model.ViewModel.FeeConfiguration_ViewModel>();
List <FeeConfiguration_ViewModel> newlyAddedfeeConfiguration_ViewModel = new List <Model.ViewModel.FeeConfiguration_ViewModel>();
ViewData["Class_Id"] = Class_Id;
long nAcademicYear = Convert.ToInt64(Academic_Year);
//long nAcademicYear = (DateTime.Now.Month <= 4) ? DateTime.Now.Year - 1 : DateTime.Now.Year;
int nClass_Id = Convert.ToInt16(Class_Id);
using (var dbcontext = new SchoolERPDBContext())
{
//If fees not configuration , we need retrieve fee component from "Fee" master table otherwise need to retrieve from "Fee_Configuration" table
if (dbcontext.Fee_Configuration.Where(a => a.Class_Id == nClass_Id && a.Academic_Year == nAcademicYear && (a.Is_Deleted == false || a.Is_Deleted == null)).Count() == 0)
{
feeConfiguration_ViewModel = (from usr in dbcontext.Users
join fee in dbcontext.Fee on usr.Id equals fee.Created_By
where (fee.Is_Deleted == null || fee.Is_Deleted == false)
select new FeeConfiguration_ViewModel
{
Id = fee.Id,
Name = fee.Name,
Academic_Year = nAcademicYear,
User_Id = usr.User_Id,
Yearly_Amount = null,
First_Term_Amount = null,
Second_Term_Amount = null,
Third_Term_Amount = null
}).ToList();
//emptyFeeConfiguration_ViewModel.Academic_Year = nAcademicYear;
//emptyFeeConfiguration_ViewModel.Name = "TOTAL";
feeConfiguration_ViewModel.Add(emptyFeeConfiguration_ViewModel);
feeConfiguration_ViewModel.Add(emptyFeeConfiguration_ViewModel);
nFeeIdArr = feeConfiguration_ViewModel.ToList().Select(l => l.Id).Distinct().ToArray();
TempData["FeeIdArr"] = nFeeIdArr;
TempData.Keep("FeeIdArr");
TempData["Class_Id"] = Class_Id;
TempData.Keep("Class_Id");
}
//If fee already configured ,we need to retrieve from "Fee_Configuartion" table
else
{
feeConfiguration_ViewModel = (from usr in dbcontext.Users
join fee in dbcontext.Fee on usr.Id equals fee.Created_By
join fc in dbcontext.Fee_Configuration on fee.Id equals fc.Fee_Id
where (fc.Is_Deleted == null || fc.Is_Deleted == false) && fc.Class_Id == nClass_Id && fc.Academic_Year == nAcademicYear
select new FeeConfiguration_ViewModel
{
Id = fee.Id,
Name = fee.Name,
Academic_Year = nAcademicYear,
User_Id = usr.User_Id,
Frequency = fc.Frequency,
Amount = fc.Amount
// Total = fc.Total
}).ToList();
//If new fee is added in "Fee" table but fees structure is already configured for the section
var newFeeComponent = (from fc in dbcontext.Fee_Configuration where fc.Class_Id == nClass_Id && fc.Academic_Year == nAcademicYear && (fc.Is_Deleted == null || fc.Is_Deleted == false) select fc.Fee_Id).Distinct().ToList();
List <int> lstOtherFeeComponent = (from e in dbcontext.Fee
join fc in dbcontext.Fee_Configuration on e.Id equals fc.Fee_Id
select e.Id).Except(newFeeComponent).ToList();
for (int i = 0; i < lstOtherFeeComponent.Count(); i++)
{
FeeConfiguration_ViewModel newlyAddedFeeComponent = new FeeConfiguration_ViewModel();
newlyAddedFeeComponent.Id = lstOtherFeeComponent[i];
int nTemp_ClassId = lstOtherFeeComponent[i];
newlyAddedFeeComponent.Name = Convert.ToString(dbcontext.Fee.Where(x => x.Id == nTemp_ClassId).ToList()[0].Name);
newlyAddedFeeComponent.Academic_Year = nAcademicYear;
newlyAddedFeeComponent.First_Term_Amount = null;
newlyAddedFeeComponent.Second_Term_Amount = null;
newlyAddedFeeComponent.Third_Term_Amount = null;
newlyAddedFeeComponent.Yearly_Amount = null;
newlyAddedfeeConfiguration_ViewModel.Add(newlyAddedFeeComponent);
}
int[] nFeeIdArr = feeConfiguration_ViewModel.Select(l => l.Id).Distinct().ToArray();
int[] nFreqArr = feeConfiguration_ViewModel.Select(l => l.Frequency).Distinct().ToArray();
for (int nrecordLoopCount = 0; nrecordLoopCount < nFeeIdArr.Count(); nrecordLoopCount++)
{
FeeConfiguration_ViewModel oFeeConfiguration_ViewModel = new FeeConfiguration_ViewModel();
oFeeConfiguration_ViewModel = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount]).ToList()[0];
FeeConfiguration_ViewModel temp_oFeeConfiguration_ViewModel = new FeeConfiguration_ViewModel();
temp_oFeeConfiguration_ViewModel.Id = oFeeConfiguration_ViewModel.Id;
temp_oFeeConfiguration_ViewModel.Name = oFeeConfiguration_ViewModel.Name;
temp_oFeeConfiguration_ViewModel.Academic_Year = oFeeConfiguration_ViewModel.Academic_Year;
for (int nfreqLoopCount = 1; nfreqLoopCount <= nFreqArr.Count(); nfreqLoopCount++)
{
if (nfreqLoopCount == 1)
{
temp_oFeeConfiguration_ViewModel.Yearly_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 1).ToList()[0].Amount;
//emptyFeeConfiguration_ViewModel.Yearly_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 1).ToList()[0].Total;
continue;
}
if (nfreqLoopCount == 2)
{
temp_oFeeConfiguration_ViewModel.First_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 2).ToList()[0].Amount;
//emptyFeeConfiguration_ViewModel.First_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 2).ToList()[0].Total;
continue;
}
if (nfreqLoopCount == 3)
{
temp_oFeeConfiguration_ViewModel.Second_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 3).ToList()[0].Amount;
// emptyFeeConfiguration_ViewModel.Second_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 3).ToList()[0].Total;
continue;
}
else
{
temp_oFeeConfiguration_ViewModel.Third_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 4).ToList()[0].Amount;
//emptyFeeConfiguration_ViewModel.Third_Term_Amount = feeConfiguration_ViewModel.Where(a => a.Id == nFeeIdArr[nrecordLoopCount] && a.Frequency == 4).ToList()[0].Total;
}
}
editModefeeConfiguration_ViewModel.Add(temp_oFeeConfiguration_ViewModel);
}
feeConfiguration_ViewModel.Clear();
feeConfiguration_ViewModel.AddRange(editModefeeConfiguration_ViewModel);
//emptyFeeConfiguration_ViewModel.Academic_Year = nAcademicYear;
//emptyFeeConfiguration_ViewModel.Name = "TOTAL";
feeConfiguration_ViewModel.AddRange(newlyAddedfeeConfiguration_ViewModel);
nFeeIdArr = feeConfiguration_ViewModel.ToList().Select(l => l.Id).Distinct().ToArray();
TempData["FeeIdArr"] = nFeeIdArr;
TempData.Keep("FeeIdArr");
TempData["Class_Id"] = Class_Id;
TempData.Keep("Class_Id");
feeConfiguration_ViewModel.Add(emptyFeeConfiguration_ViewModel);
feeConfiguration_ViewModel.Add(emptyFeeConfiguration_ViewModel);
}
}
return(Json(feeConfiguration_ViewModel.ToArray(), JsonRequestBehavior.AllowGet));
}
