/* INDEX **********************************************************************************************************************************************/
// GET: TutorSchedules
public ActionResult Index(int?rss, string FILTER_Keyword, int?FILTER_Active)
{
if (!UserAccountsController.getUserAccess(Session).TutorSchedules_View)
{
return(RedirectToAction(nameof(HomeController.Index), "Home"));
}
setViewBag(FILTER_Keyword, FILTER_Active);
if (rss != null && !SettingsController.ShowOnlyOwnUserData(Session))
{
ViewBag.RemoveDatatablesStateSave = rss;
return(View());
}
else
{
return(View(get(FILTER_Keyword, FILTER_Active)));
}
}
public JsonResult Ajax_GetDDLItems(string keyword, int page, int take, string key)
{
int skip = take * (page - 1);
List <UserAccountsModel> models = get(skip, take, keyword, 1, null, key, SettingsController.ShowOnlyOwnUserData(Session));
List <Select2Pagination.Select2Results> results = new List <Select2Pagination.Select2Results>();
results.AddRange(models.Select(model => new Select2Pagination.Select2Results
{
id = model.Id,
text = model.Fullname
}));
Select2Pagination.Select2Page pagination = new Select2Pagination.Select2Page
{
more = results.Count() == take ? true : false
};
return(Json(new { results, pagination }, JsonRequestBehavior.AllowGet));
}
public static List <TutorSchedulesModel> get(HttpSessionStateBase Session, Guid?Id, Guid?Tutor_UserAccounts_Id, DayOfWeekEnum?DayOfWeek, DateTime?StartTime, DateTime?EndTime,
Guid?Languages_Id, int?Active, string FILTER_Keyword)
{
UserAccountsModel UserAccount = UserAccountsController.getUserAccount(Session);
List <TutorSchedulesModel> models = new DBContext().Database.SqlQuery <TutorSchedulesModel>(@"
SELECT TutorSchedules.*,
UserAccounts.Fullname AS Tutor_UserAccounts_Name,
UserAccounts.No AS Tutor_UserAccounts_No,
UserAccounts.Interest AS UserAccounts_Interest
FROM TutorSchedules
LEFT JOIN UserAccounts ON UserAccounts.Id = TutorSchedules.Tutor_UserAccounts_Id
WHERE 1=1
AND (@Id IS NULL OR TutorSchedules.Id = @Id)
AND (@Id IS NOT NULL OR (
(@Active IS NULL OR TutorSchedules.Active = @Active)
AND (@Tutor_UserAccounts_Id IS NULL OR TutorSchedules.Tutor_UserAccounts_Id = @Tutor_UserAccounts_Id)
AND (@ShowOnlyOwnUserData = 0 OR (TutorSchedules.Tutor_UserAccounts_Id = @UserAccounts_Id))
AND (@DayOfWeek IS NULL OR TutorSchedules.[DayOfWeek] = @DayOfWeek)
AND ((@StartTime IS NULL OR (@StartTime >= TutorSchedules.StartTime OR @StartTime <= TutorSchedules.EndTime))
OR (@EndTime IS NULL OR (@EndTime >= TutorSchedules.StartTime OR @EndTime <= TutorSchedules.EndTime))
)
AND (@Languages_Id IS NULL OR UserAccounts.Interest LIKE '%'+ convert(nvarchar(50), @Languages_Id) + '%')
AND (@FILTER_Keyword IS NULL OR (
UserAccounts.Fullname LIKE '%'+@FILTER_Keyword+'%'
OR TutorSchedules.DayOfWeek LIKE '%'+@FILTER_Keyword+'%'
))
AND (@Branches_Id IS NULL OR UserAccounts.Branches LIKE '%'+ convert(nvarchar(50), @Branches_Id) + '%')
))
ORDER BY UserAccounts.Fullname ASC, TutorSchedules.DayOfWeek ASC, TutorSchedules.StartTime ASC, TutorSchedules.EndTime ASC
",
DBConnection.getSqlParameter(TutorSchedulesModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(TutorSchedulesModel.COL_Active.Name, Active),
DBConnection.getSqlParameter(TutorSchedulesModel.COL_Tutor_UserAccounts_Id.Name, Tutor_UserAccounts_Id),
DBConnection.getSqlParameter(TutorSchedulesModel.COL_DayOfWeek.Name, DayOfWeek),
DBConnection.getSqlParameter(TutorSchedulesModel.COL_StartTime.Name, StartTime),
DBConnection.getSqlParameter(TutorSchedulesModel.COL_EndTime.Name, EndTime),
DBConnection.getSqlParameter("Branches_Id", Helper.getActiveBranchId(Session)),
DBConnection.getSqlParameter("Languages_Id", Languages_Id),
DBConnection.getSqlParameter("FILTER_Keyword", FILTER_Keyword),
DBConnection.getSqlParameter("ShowOnlyOwnUserData", SettingsController.ShowOnlyOwnUserData(UserAccount.Roles_List)),
DBConnection.getSqlParameter("UserAccounts_Id", UserAccount.Id)
).ToList();
foreach (TutorSchedulesModel model in models)
{
if (!string.IsNullOrEmpty(model.UserAccounts_Interest))
{
model.UserAccounts_Interest_List = model.UserAccounts_Interest.Split(',').ToList();
}
}
return(models);
}
public static List <StudentSchedulesModel> get(HttpSessionStateBase Session, Guid?Id, Guid?Tutor_UserAccounts_Id, Guid?Student_UserAccounts_Id, DayOfWeekEnum?DayOfWeek, DateTime?StartTime, DateTime?EndTime, Guid?SaleInvoiceItems_Id,
Guid?Languages_Id, string FILTER_Keyword, string FILTER_UserAccounts_Name)
{
UserAccountsModel UserAccount = UserAccountsController.getUserAccount(Session);
return(new DBContext().Database.SqlQuery <StudentSchedulesModel>(@"
SELECT StudentSchedules.*,
StudentSchedules.LessonLocation AS LessonLocationRadioButton,
Tutor_UserAccounts.Fullname AS Tutor_UserAccounts_Name,
Tutor_UserAccounts.No AS Tutor_UserAccounts_No,
Student_UserAccounts.Fullname AS Student_UserAccounts_Name,
Student_UserAccounts.No AS Student_UserAccounts_No,
Student_UserAccounts.Branches AS Student_UserAccounts_Branches,
SaleInvoices.No AS SaleInvoices_No,
SaleInvoiceItems.Description AS SaleInvoiceItems_Description,
Languages.Name AS Languages_Name,
SaleInvoiceItems.SessionHours_Remaining AS SessionHours_Remaining
FROM StudentSchedules
LEFT JOIN UserAccounts Tutor_UserAccounts ON Tutor_UserAccounts.Id = StudentSchedules.Tutor_UserAccounts_Id
LEFT JOIN UserAccounts Student_UserAccounts ON Student_UserAccounts.Id = StudentSchedules.Student_UserAccounts_Id
LEFT JOIN SaleInvoiceItems ON SaleInvoiceItems.Id = StudentSchedules.SaleInvoiceItems_Id
LEFT JOIN LessonPackages ON LessonPackages.Id = SaleInvoiceItems.LessonPackages_Id
LEFT JOIN Languages ON Languages.Id = LessonPackages.Languages_Id
LEFT JOIN SaleInvoices ON SaleInvoices.Id = SaleInvoiceItems.SaleInvoices_Id
WHERE 1=1
AND (@Id IS NULL OR StudentSchedules.Id = @Id)
AND (@Id IS NOT NULL OR (
(@Tutor_UserAccounts_Id IS NULL OR StudentSchedules.Tutor_UserAccounts_Id = @Tutor_UserAccounts_Id)
AND (@Student_UserAccounts_Id IS NULL OR StudentSchedules.Student_UserAccounts_Id = @Student_UserAccounts_Id)
AND (@ShowOnlyOwnUserData = 0 OR (StudentSchedules.Student_UserAccounts_Id = @UserAccounts_Id OR StudentSchedules.Tutor_UserAccounts_Id = @UserAccounts_Id))
AND (@DayOfWeek IS NULL OR StudentSchedules.[DayOfWeek] = @DayOfWeek)
AND ((@StartTime IS NULL OR (@StartTime >= StudentSchedules.StartTime OR @StartTime <= StudentSchedules.EndTime))
OR (@EndTime IS NULL OR (@EndTime >= StudentSchedules.StartTime OR @EndTime <= StudentSchedules.EndTime))
)
AND (@Languages_Id IS NULL OR Languages.Id = @Languages_Id)
AND (@SaleInvoiceItems_Id IS NULL OR StudentSchedules.SaleInvoiceItems_Id = @SaleInvoiceItems_Id)
AND (@FILTER_Keyword IS NULL OR (
Student_UserAccounts.Fullname LIKE '%'+@FILTER_Keyword+'%'
OR SaleInvoices.No LIKE '%'+@FILTER_Keyword+'%'
OR StudentSchedules.DayOfWeek LIKE '%'+@FILTER_Keyword+'%'
OR StudentSchedules.LessonLocation LIKE '%'+@FILTER_Keyword+'%'
))
AND (@FILTER_UserAccounts_Name IS NULL OR (
Tutor_UserAccounts.Fullname LIKE '%'+@FILTER_UserAccounts_Name+'%'
))
AND (@Branches_Id IS NULL OR Student_UserAccounts.Branches LIKE '%'+ convert(nvarchar(50), @Branches_Id) + '%')
))
ORDER BY Student_UserAccounts.Fullname ASC, StudentSchedules.DayOfWeek ASC, StudentSchedules.StartTime ASC, StudentSchedules.EndTime ASC, Tutor_UserAccounts.Fullname ASC
",
DBConnection.getSqlParameter(StudentSchedulesModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_Tutor_UserAccounts_Id.Name, Tutor_UserAccounts_Id),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_Student_UserAccounts_Id.Name, Student_UserAccounts_Id),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_DayOfWeek.Name, DayOfWeek),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_StartTime.Name, StartTime),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_EndTime.Name, EndTime),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_SaleInvoiceItems_Id.Name, SaleInvoiceItems_Id),
DBConnection.getSqlParameter(StudentSchedulesModel.COL_Languages_Id.Name, Languages_Id),
DBConnection.getSqlParameter("Branches_Id", Helper.getActiveBranchId(Session)),
DBConnection.getSqlParameter("FILTER_Keyword", FILTER_Keyword),
DBConnection.getSqlParameter("FILTER_UserAccounts_Name", FILTER_UserAccounts_Name),
DBConnection.getSqlParameter("ShowOnlyOwnUserData", SettingsController.ShowOnlyOwnUserData(UserAccount.Roles_List)),
DBConnection.getSqlParameter("UserAccounts_Id", UserAccount.Id)
).ToList());
}