/* INDEX **********************************************************************************************************************************************/
// GET: LessonSessions
public ActionResult Index(int?rss, string FILTER_Keyword, string FILTER_InvoiceNo, int?FILTER_Cancelled,
bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo)
{
if (!UserAccountsController.getUserAccess(Session).LessonSessions_View)
{
return(RedirectToAction(nameof(HomeController.Index), "Home"));
}
if (UtilWebMVC.hasNoFilter(FILTER_Keyword, FILTER_InvoiceNo, FILTER_Cancelled, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo))
{
FILTER_chkDateFrom = true;
FILTER_DateFrom = Helper.getCurrentDateTime();
if (UserAccountsController.getUserAccount(Session).Roles_List.Contains(SettingsController.get().StudentRole.ToString()))
{
FILTER_Cancelled = 0;
}
}
setViewBag(FILTER_Keyword, FILTER_InvoiceNo, FILTER_Cancelled, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo);
if (rss != null)
{
ViewBag.RemoveDatatablesStateSave = rss;
return(View());
}
else
{
List <LessonSessionsModel> models = get(FILTER_Keyword, FILTER_InvoiceNo, FILTER_Cancelled, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo)
.OrderByDescending(x => x.Timestamp).ToList();
return(View(models));
}
}
/* INDEX **********************************************************************************************************************************************/
// GET: SaleInvoices
public ActionResult Index(int?rss, string FILTER_Keyword, string FILTER_PaymentNo, int?FILTER_Cancelled, int?FILTER_Approved, int?FILTER_HasDueAmount,
bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo)
{
if (!UserAccountsController.getUserAccess(Session).SaleInvoices_View)
{
return(RedirectToAction(nameof(HomeController.Index), "Home"));
}
if (UtilWebMVC.hasNoFilter(FILTER_Keyword, FILTER_PaymentNo, FILTER_Cancelled, FILTER_Approved, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo))
{
//FILTER_chkDateFrom = true;
//FILTER_DateFrom = Helper.getCurrentDateTime();
FILTER_Cancelled = 0;
if (UserAccountsController.getUserAccount(Session).Roles_List.Contains(SettingsController.get().StudentRole.ToString()))
{
FILTER_HasDueAmount = null;
}
else
{
FILTER_HasDueAmount = 1;
}
}
setViewBag(FILTER_Keyword, FILTER_PaymentNo, FILTER_Cancelled, FILTER_Approved, FILTER_HasDueAmount, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo);
ViewBag.RemoveDatatablesStateSave = rss;
List <SaleInvoicesModel> models = get(FILTER_Keyword, FILTER_PaymentNo, FILTER_Cancelled, FILTER_Approved, FILTER_HasDueAmount, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo)
.OrderByDescending(x => x.No).ToList();
return(View(models));
}
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());
}
public static bool ShowOnlyOwnUserData(HttpSessionStateBase Session)
{
return(ShowOnlyOwnUserData(UserAccountsController.getUserAccount(Session).Roles_List));
}
