private static void AssignValuesFromJson(JObject Lesson, DataModels.Context Context, DataModels.Lesson newLesson)
{
newLesson.ParentId = Lesson["prevId"] != null ? Lesson["prevId"].Value <int>() : 0;
newLesson.Start = Lesson["start"].Value <DateTime>();
newLesson.End = Lesson["end"].Value <DateTime>();
newLesson.Instructor_Id = Lesson["resourceId"] != null ? Lesson["resourceId"].Value <int>() : 0;
newLesson.Details = Lesson["details"] != null ? Lesson["details"].Value <string>() : "";
//var oldDetails = newLesson.Details;
//if (Lesson["details"] != null)
//{
// string DetailsFromUser = Lesson["details"].Value<string>();
// if (oldDetails == null || oldDetails == "")
// {
// //oldDetails
// newLesson.Details = " דוד: " + DetailsFromUser;
// }
// else
// {
// //var oldNewDetails = DetailsFromUser.Replace(oldDetails.Trim(), "");
// var newDetails = DetailsFromUser.Replace(oldDetails.Trim(), "");
// newLesson.Details = newDetails;
// }
//}
//else
//{
// newLesson.Details = "";
//}
}
private static void UpdateExistingLesson(JObject Lesson, DataModels.Context Context)
{
int LessonId = Lesson["id"].Value <int>();
var newLesson = Context.Lessons.SingleOrDefault(l => l.Id == LessonId);
AssignValuesFromJson(Lesson, Context, newLesson);
Context.Entry(newLesson).State = System.Data.Entity.EntityState.Modified;
Context.SaveChanges();
}
private static void CreateNewLesson(JObject Lesson, DataModels.Context Context)
{
var newLesson = new Lesson();
AssignValuesFromJson(Lesson, Context, newLesson);
Context.Lessons.Add(newLesson);
Context.SaveChanges();
Lesson["id"] = newLesson.Id;
InsertIntoLog(newLesson.Id, 1, Context, "שיעור חדש", null);
Context.SaveChanges();
}
private static void PopulateReturnLessonsToComplete(JArray ReturnLessons, DataModels.Context Context, User CurrentUser, int?StudentId, string startDate, string endDate, bool IsFromCompletion)
{
var dt = new DataTable();
var conn = Context.Database.Connection;
var connectionState = conn.State;
if (connectionState != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
Select t3.*,u.FirstName + ' ' + u.LastName as FullName,ui.FirstName + ' ' + ui.LastName as InstructorName from (
Select t1.Id,completionReq = t1.CounterStatus - coalesce(t2.CounterStatus, 0) from (
Select st.User_Id as Id,Count(Status) as CounterStatus from StudentLessons st
where (st.Status in('completionReq','completionReqCharge'))
Group by st.User_Id
) t1
left join (
Select st.User_Id as Id,Count(Status) as CounterStatus from StudentLessons st
where (st.Status = 'completion')
Group by st.User_Id
)t2 on t2.Id=t1.Id
)t3
inner join Users u on t3.Id = u.Id and u.Deleted=0 and u.Active='active'
inner join ( Select *,ROW_NUMBER() OVER(Partition by User_Id ORDER BY Lesson_Id desc) as RowNum from StudentLessons where Status in('completionReq','completionReqCharge')) t4 on t4.User_Id=t3.Id and t4.RowNum = 1
inner join Lessons l on l.Id = t4.Lesson_Id
inner join Users ui on ui.Id = l.Instructor_Id
where (t3.completionReq > 0) and('" + CurrentUser.Role.ToString() + "'!='instructor' or " + CurrentUser.Id.ToString() + "=l.Instructor_Id ) and (u.Farm_Id=" + CurrentUser.Farm_Id + " Or 0=" + CurrentUser.Farm_Id + ") order by Instructor_Id";
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
conn.Close();
foreach (DataRow row in dt.Rows)
{
ReturnLessons.Add(JObject.FromObject(new
{
Id = row["Id"],
FullName = row["FullName"],
completionReq = row["completionReq"],
InstructorName = row["InstructorName"]
}));
}
}
}
private static void ReassignStudentLessons(JObject Lesson, DataModels.Context Context)
{
int LessonId = Lesson["id"].Value <int>();
int onlyMultiple = 0;
if (Lesson["onlyMultiple"] != null)
{
onlyMultiple = Lesson["onlyMultiple"].Value <int>();
}
var TempStudentLessonsList = Context.StudentLessons.Where(sl => sl.Lesson_Id == LessonId).ToList();
var CurrentUserRole = UsersService.GetCurrentUser().Role;
if (CurrentUserRole == "instructor")
{
Context.StudentLessons.RemoveRange(Context.StudentLessons.Where(sl => sl.Lesson_Id == LessonId && sl.Status != "completionReq" && sl.Status != "completionReqCharge"));
}
else
{
Context.StudentLessons.RemoveRange(Context.StudentLessons.Where(sl => sl.Lesson_Id == LessonId));
}
if (Lesson["students"] != null)
{
var StudentIds = Lesson["students"].Values <int>().ToList();
foreach (var StudentId in StudentIds)
{
var StatusData = GetStatusDataFromJson(Lesson, StudentId);
string Matarot = "", Mahalak = "", HearotStatus = "", Mashov = "";
var TempStudentLessons = TempStudentLessonsList.Where(x => x.User_Id == StudentId).FirstOrDefault();
if (TempStudentLessons != null)
{
Matarot = TempStudentLessons.Matarot;
Mahalak = TempStudentLessons.Mahalak;
HearotStatus = TempStudentLessons.HearotStatus;
Mashov = TempStudentLessons.Mashov;
}
if ((StatusData[0] == "completionReq" || StatusData[0] == "completionReqCharge") && (StatusData[2] == "0" || StatusData[2] == null))
{
StatusData[2] = "1";
}
if (StatusData[0] == "completion" && StatusData[2] == "2")
{
StatusData[0] = "completion";
StatusData[2] = "5";
var conn = Context.Database.Connection;
var connectionState = conn.State;
if (connectionState != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = " UPDATE StudentLessons SET IsComplete = 2 "
+ " WHERE Lesson_Id = (SELECT top 1 Lesson_Id FROM StudentLessons WHERE Lesson_Id < " + LessonId + " and User_Id = " + StudentId
+ " and Status in('completionReq','completionReqCharge') and IsComplete = 1 order by Lesson_Id desc) and User_Id = " + StudentId;
cmd.ExecuteNonQuery();
}
conn.Close();
}
if ((StatusData[0] == "" || StatusData[0] == null || StatusData[0] == "completion") && (StatusData[2] == "3" || StatusData[2] == "4" || StatusData[2] == "5" || StatusData[2] == "6"))
{
StatusData[0] = "completion";
StatusData[2] = "5";
}
if ((StatusData[0] == "notAttended") && (StatusData[2] == "3" || StatusData[2] == "4" || StatusData[2] == "5" || StatusData[2] == "6"))
{
StatusData[0] = "completion";
StatusData[2] = "3";
}
if ((StatusData[0] == "attended") && (StatusData[2] == "3" || StatusData[2] == "4" || StatusData[2] == "5" || StatusData[2] == "6"))
{
StatusData[0] = "completion";
StatusData[2] = "4";
}
if ((StatusData[0] == "notAttendedCharge") && (StatusData[2] == "3" || StatusData[2] == "4" || StatusData[2] == "5" || StatusData[2] == "6"))
{
StatusData[0] = "completion";
StatusData[2] = "6";
}
if ((StatusData[0] != "completion") && (StatusData[0] != "completionReq") && (StatusData[0] != "completionReqCharge"))
{
StatusData[2] = "0";
}
StudentLessons sl = new StudentLessons()
{
Lesson_Id = LessonId,
User_Id = StudentId,
Status = StatusData[0],
Details = StatusData[1],
IsComplete = Int32.Parse((StatusData[2] == null) ? "0" : StatusData[2]),
HorseId = Int32.Parse((StatusData[3] == null) ? "0" : StatusData[3]),
OfficeDetails = StatusData[4],
Matarot = Matarot,
Mahalak = Mahalak,
HearotStatus = HearotStatus,
Mashov = Mashov
};
Context.StudentLessons.Add(sl);
InsertIntoLog(LessonId, 2, Context, " עדכון סטטוס " + StatusData[2], sl);
}
Context.SaveChanges();
// if (StudentIds.Count() > 1)
if (onlyMultiple == 1)
{
AddOrRemvoveFromGroup(Context, StudentIds, LessonId);
}
}
}
private static void PopulateReturnLessons(JArray ReturnLessons, DataModels.Context Context, User CurrentUser, int?StudentId, string startDate, string endDate, bool IsFromCompletion)
{
bool IsPrice = false;
if (CurrentUser.Role == "sysAdmin" || CurrentUser.Role == "farmAdmin")
{
IsPrice = true;
}
DateTime StartDate = startDate != null?DateTime.Parse(startDate) : DateTime.Now.Date;
DateTime EndDate = endDate != null?DateTime.Parse(endDate).AddDays(1) : DateTime.Now.Date.AddDays(1);
SqlParameter StudentIdPara = new SqlParameter("StudentId", (StudentId == null) ? -1 : StudentId);
SqlParameter Farm_IdPara = new SqlParameter("Farm_Id", CurrentUser.Farm_Id);
SqlParameter RolePara = new SqlParameter("Role", CurrentUser.Role);
SqlParameter StartDatePara = new SqlParameter("StartDate", StartDate);
SqlParameter EndDatePara = new SqlParameter("EndDate", EndDate);
SqlParameter IsPricePara = new SqlParameter("IsPrice", IsPrice);
var query = Context.Database.SqlQuery <LessonsResult>
("GetStudentsLessonsList @StudentId,@Farm_Id,@Role,@StartDate,@EndDate,@IsPrice",
StudentIdPara, Farm_IdPara, RolePara, StartDatePara, EndDatePara, IsPricePara);
//try
//{
// var lessons3 = query.ToList();
//}
//catch(Exception ex)
//{
//}
//try
//{
var lessons = query.ToList();
int lastId = 0;
foreach (var Lesson in lessons)
{
try
{
if (Lesson.Id != lastId)
{
var students = lessons.Where(l => l.Id == Lesson.Id && l.User_Id != null).Select(l => new
{
StudentId = l.User_Id,
Status = l.Status,
StudentName = l.StudentName,
Details = l.StatusDetails,
OfficeDetails = l.OfficeDetails,
IsComplete = l.IsComplete,
HorseId = l.HorseId,
Matarot = l.Matarot,
Mahalak = l.Mahalak,
HearotStatus = l.HearotStatus,
Mashov = l.Mashov,
LessPrice = l.Price,
HMO = l.HMO
}).Distinct().ToArray();
var studentsArray = students.Select(s => s.StudentName).ToArray();
var horsesArray = lessons.Where(l => l.Id == Lesson.Id && l.User_Id != null && l.HorseName != null).Select(l => new { HorseName = l.HorseName }).ToArray();
ReturnLessons.Add(JObject.FromObject(new
{
id = Lesson.Id,
prevId = Lesson.ParentId,
start = Lesson.Start,
end = Lesson.End,
horsenames = horsesArray.Select(s => s.HorseName).ToArray(),
editable = true,
resourceId = Lesson.Instructor_Id,
lessprice = Lesson.Price,
lessonpaytype = Lesson.LessonPayType,
details = Lesson.Details,
students = students.Select(s => s.StudentId).ToArray(),
statuses = students.Select(s => new
{
StudentId = s.StudentId,
Status = s.Status,
Details = s.Details,
IsComplete = s.IsComplete,
HorseId = s.HorseId,
OfficeDetails = s.OfficeDetails,
Matarot = s.Matarot,
Mahalak = s.Mahalak,
HearotStatus = s.HearotStatus,
Mashov = s.Mashov,
LessPrice = s.LessPrice,
HMO = s.HMO
}).ToArray(),
title = (studentsArray.Length > 0 ? string.Join("", studentsArray) : "") + (!string.IsNullOrEmpty(Lesson.Details) ? (studentsArray.Length > 0 ? ". " : "") + Lesson.Details : ""),
PrevNext = Lesson.PrevNext,
IsMazkirut = Lesson.IsMazkirut
//Matarot = Lesson.Matarot,
//Mahalak = Lesson.Mahalak,
//HearotStatus = Lesson.HearotStatus,
//Mashov = Lesson.Mashov
}));
}
lastId = Lesson.Id;
}
catch (Exception)
{
}
}
//}
//catch (Exception ex)
//{
//}
}
