public void add(UserAccountsModel model)
{
db.Database.ExecuteSqlCommand(@"
-- INCREMENT LAST HEX NUMBER
DECLARE @HexLength int = 5, @LastHex_String varchar(5), @NewNo varchar(5)
SELECT @LastHex_String = ISNULL(MAX(No),'') From UserAccounts
DECLARE @LastHex_Int int
SELECT @LastHex_Int = CONVERT(INT, CONVERT(VARBINARY, REPLICATE('0', LEN(@LastHex_String)%2) + @LastHex_String, 2)) --@LastHex_String length must be even number of digits to convert to int
SET @NewNo = RIGHT(CONVERT(NVARCHAR(10), CONVERT(VARBINARY(8), @LastHex_Int + 1), 1),@HexLength)
INSERT INTO UserAccounts (Id, No, Fullname, Username, Password, Birthday, Branches_Id, ResetPassword, Active, Roles, Branches)
VALUES( @Id,@NewNo,@Fullname,@Username,@Password,@Birthday,@Branches_Id,@ResetPassword,@Active,@Roles,@Branches);
",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Username.Name, model.Username),
DBConnection.getSqlParameter(UserAccountsModel.COL_Password.Name, model.Password),
DBConnection.getSqlParameter(UserAccountsModel.COL_Fullname.Name, model.Fullname),
DBConnection.getSqlParameter(UserAccountsModel.COL_Birthday.Name, model.Birthday),
DBConnection.getSqlParameter(UserAccountsModel.COL_ResetPassword.Name, model.ResetPassword),
DBConnection.getSqlParameter(UserAccountsModel.COL_Active.Name, model.Active),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Roles.Name, model.Roles),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches.Name, model.Branches)
);
ActivityLogsController.AddCreateLog(db, Session, model.Id);
}
public ActionResult ChangePassword(Guid Id, string CurrentPassword, string NewPassword, string ConfirmPassword, string returnUrl)
{
UserAccountsModel model = get((Guid)Id);
string SanitizedNewPassword = Util.sanitizeString(NewPassword);
if (HashPassword(CurrentPassword) != model.Password)
{
ModelState.AddModelError("", "Invalid current password");
}
else if (string.IsNullOrEmpty(SanitizedNewPassword) || SanitizedNewPassword.Length < 6)
{
ModelState.AddModelError("", "Invalid new password. Must be at least 6 characters");
}
else if (string.IsNullOrEmpty(ConfirmPassword) || SanitizedNewPassword != ConfirmPassword)
{
ModelState.AddModelError("", "Invalid confirm password");
}
else
{
model.Password = HashPassword(SanitizedNewPassword);
model.ResetPassword = false;
updatePassword(model, "Password changed");
return(RedirectToLocal(returnUrl));
}
ViewBag.ReturnUrl = returnUrl;
ViewBag.NewPassword = NewPassword;
ViewBag.ConfirmPassword = ConfirmPassword;
return(View(model));
}
public ActionResult Create(UserAccountsModel model, string FILTER_Keyword, int?FILTER_Active, Guid?FILTER_Languages_Id)
{
if (ModelState.IsValid)
{
model.Fullname = model.Fullname.Replace(" ", " ").Replace(" ", " ");
if (isExists(model.Fullname, model.Birthday))
{
ModelState.AddModelError(UserAccountsModel.COL_Fullname.Name, $"{model.Fullname} dengan tanggal lahir yang sama sudah terdaftar");
}
else
{
model.Password = HashPassword(SettingsController.get().ResetPassword);
model.Branches_Id = Helper.getActiveBranchId(Session);
model.Branches = model.Branches_Id.ToString();
model.Username = generateUsername(model.Fullname, model.Birthday);
if (!getUserAccess(Session).UserAccounts_EditRoles)
{
model.Roles = SettingsController.get().StudentRole.ToString();
}
add(model);
db.SaveChanges();
return(RedirectToAction(nameof(Edit), new { id = model.Id, FILTER_Keyword = FILTER_Keyword, FILTER_Active = FILTER_Active, FILTER_Languages_Id = FILTER_Languages_Id }));
}
}
setViewBag(FILTER_Keyword, FILTER_Active, FILTER_Languages_Id);
return(View(model));
}
public static List <UserAccountRolesModel> get(Guid?Id, UserAccountsModel UserAccount)
{
string UserAccountRolesClause = "";
if (UserAccount != null && !string.IsNullOrEmpty(UserAccount.Roles))
{
UserAccountRolesClause = string.Format(" AND UserAccountRoles.Id IN ({0}) ", LIBWebMVC.UtilWebMVC.convertToSqlIdList(UserAccount.Roles));
}
string sql = string.Format(@"
SELECT UserAccountRoles.*
FROM UserAccountRoles
WHERE 1=1
AND (@Id IS NULL OR UserAccountRoles.Id = @Id)
AND (@Id IS NOT NULL OR (
1=1
{0}
))
ORDER BY UserAccountRoles.Name ASC
", UserAccountRolesClause);
return(new DBContext().Database.SqlQuery <UserAccountRolesModel>(sql,
DBConnection.getSqlParameter(UserAccountRolesModel.COL_Id.Name, Id)
).ToList());
}
public ActionResult Edit(int id)
{
UserAccountsModel userAccountsModel = new UserAccountsModel();
DataTable dt = new DataTable();
using (SqlConnection sqlcon = new SqlConnection(connectionString))
{
sqlcon.Open();
string query = "SELECT * FROM UserAccountsTable WHERE id=@id";
SqlDataAdapter sqlDa = new SqlDataAdapter(query, sqlcon);
sqlDa.SelectCommand.Parameters.AddWithValue("@id", id);
sqlDa.Fill(dt);
}
if (dt.Rows.Count == 1)
{
userAccountsModel.id = Convert.ToInt32(dt.Rows[0][0].ToString());
userAccountsModel.account = dt.Rows[0][1].ToString();
userAccountsModel.password = dt.Rows[0][2].ToString();
userAccountsModel.name = dt.Rows[0][3].ToString();
userAccountsModel.age = Convert.ToInt32(dt.Rows[0][4].ToString());
userAccountsModel.gender = dt.Rows[0][5].ToString();
userAccountsModel.birthday = dt.Rows[0][6].ToString();
userAccountsModel.email = dt.Rows[0][7].ToString();
return(View(userAccountsModel));
}
else
{
return(RedirectToAction("Index"));
}
}
public static void setLoginSession(HttpSessionStateBase Session, UserAccountsModel model, bool?ConnectToLiveDatabase)
{
if (model != null)
{
Session[SESSION_UserAccount] = model;
Session[SESSION_ActiveBranches_Id] = model.Branches_Id;
Session[SESSION_Branches_Models] = BranchesController.get(1, model.Branches);
Session[SESSION_UserAccountAccess] = UserAccountRolesController.getAccesses(model);
if (ConnectToLiveDatabase != null)
{
Session[SESSION_ConnectToLiveDatabase] = ConnectToLiveDatabase;
}
Session[SESSION_ShowOnlyUserData] = true;
string ShowOnlyOwnUserData = SettingsController.get().ShowOnlyOwnUserData;
string Roles = model.Roles;
foreach (string role in Roles.Split(','))
{
if (!ShowOnlyOwnUserData.Contains(role))
{
Session[SESSION_ShowOnlyUserData] = false;
break;
}
}
}
}
public void updatePassword(UserAccountsModel model, string log)
{
WebDBConnection.Update(db.Database, "UserAccounts",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Password.Name, model.Password),
DBConnection.getSqlParameter(UserAccountsModel.COL_ResetPassword.Name, model.ResetPassword)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
/* RESET PASSWORD *************************************************************************************************************************************/
public JsonResult Ajax_ResetPassword(Guid id)
{
UserAccountsModel model = new UserAccountsModel();
model.Id = id;
model.Password = HashPassword(SettingsController.get().ResetPassword);
model.ResetPassword = true;
updatePassword(model, "Password reset by admin");
return(Json(new { Message = "Password has been reset" }));
}
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 ActionResult Edit(UserAccountsModel modifiedModel, string FILTER_Keyword, int?FILTER_Active, Guid?FILTER_Languages_Id)
{
if (ModelState.IsValid)
{
modifiedModel.Fullname = modifiedModel.Fullname.Replace(" ", " ").Replace(" ", " ");
if (isExists(modifiedModel.Id, modifiedModel.Username))
{
ModelState.AddModelError(UserAccountsModel.COL_Username.Name, $"{modifiedModel.Username} sudah terdaftar");
}
else
{
UserAccountsModel originalModel = get(modifiedModel.Id);
string log = string.Empty;
log = Helper.append(log, originalModel.Username, modifiedModel.Username, UserAccountsModel.COL_Username.LogDisplay);
log = Helper.append(log, originalModel.Fullname, modifiedModel.Fullname, UserAccountsModel.COL_Fullname.LogDisplay);
log = Helper.append(log, originalModel.Birthday, modifiedModel.Birthday, UserAccountsModel.COL_Birthday.LogDisplay);
log = Helper.append <BranchesModel>(log, originalModel.Branches_Id, modifiedModel.Branches_Id, UserAccountsModel.COL_Branches_Id.LogDisplay);
log = Helper.append(log, originalModel.Active, modifiedModel.Active, UserAccountsModel.COL_Active.LogDisplay);
log = Helper.append(log, originalModel.ResetPassword, modifiedModel.ResetPassword, UserAccountsModel.COL_ResetPassword.LogDisplay);
log = Helper.append(log, originalModel.Email, modifiedModel.Email, UserAccountsModel.COL_Email.LogDisplay);
log = Helper.append(log, originalModel.Address, modifiedModel.Address, UserAccountsModel.COL_Address.LogDisplay);
log = Helper.append(log, originalModel.Phone1, modifiedModel.Phone1, UserAccountsModel.COL_Phone1.LogDisplay);
log = Helper.append(log, originalModel.Phone2, modifiedModel.Phone2, UserAccountsModel.COL_Phone2.LogDisplay);
log = Helper.append(log, originalModel.Notes, modifiedModel.Notes, UserAccountsModel.COL_Notes.LogDisplay);
log = Helper.append <PromotionEventsModel>(log, originalModel.PromotionEvents_Id, modifiedModel.PromotionEvents_Id, UserAccountsModel.COL_PromotionEvents_Id.LogDisplay);
log = Helper.addLogForList <BranchesModel>(log, originalModel.Branches_List, modifiedModel.Branches_List, UserAccountsModel.COL_Branches.LogDisplay);
log = Helper.addLogForList <UserAccountRolesModel>(log, originalModel.Roles_List, modifiedModel.Roles_List, UserAccountsModel.COL_Roles.LogDisplay);
log = Helper.addLogForList <LanguagesModel>(log, originalModel.Interest_List, modifiedModel.Interest_List, UserAccountsModel.COL_Interest.LogDisplay);
if (!string.IsNullOrEmpty(log))
{
update(modifiedModel, log);
}
return(RedirectToAction(nameof(Index), new { FILTER_Keyword = FILTER_Keyword, FILTER_Active = FILTER_Active, FILTER_Languages_Id = FILTER_Languages_Id }));
}
}
setViewBag(FILTER_Keyword, FILTER_Active, FILTER_Languages_Id);
return(View(modifiedModel));
}
public ActionResult Register(UserAccountsModel userAccountsModel)
{
var lista = _userAccountAppService.GetAll().Where(ua => ua.Username == userAccountsModel.Username && ua.Password == userAccountsModel.Password).FirstOrDefault();
if (lista == null)
{
_userAccountAppService.Insert(userAccountsModel.GetDocumentoTipo());
}
else
{
ModelState.AddModelError(string.Empty, "Ya existe un Usuario con ese Alias/Contraseña");
}
if (ModelState.IsValid)
{
_userAccountAppService.Save();
return(RedirectToAction("Login"));
}
return(View(userAccountsModel));
}
public ActionResult Edit(UserAccountsModel userAccountmodel)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
string query = "UPDATE UserAccountsTable SET account=@account,password=@password,name=@name,age=@age,gender=@gender,birthday=@birthday,email=@email WHERE id=@id";
SqlCommand sqlComm = new SqlCommand(query, sqlCon);
sqlComm.Parameters.AddWithValue("@id", userAccountmodel.id);
sqlComm.Parameters.AddWithValue("@account", userAccountmodel.account);
sqlComm.Parameters.AddWithValue("@password", userAccountmodel.password);
sqlComm.Parameters.AddWithValue("@name", userAccountmodel.name);
sqlComm.Parameters.AddWithValue("@age", userAccountmodel.age);
sqlComm.Parameters.AddWithValue("@gender", userAccountmodel.gender);
sqlComm.Parameters.AddWithValue("@birthday", userAccountmodel.birthday);
sqlComm.Parameters.AddWithValue("@email", userAccountmodel.email);
sqlComm.ExecuteNonQuery();
}
return(RedirectToAction("Index"));
}
public ActionResult Create(UserAccountsModel userAccountmodel)
{
using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
sqlCon.Open();
string query = "INSERT INTO UserAccountsTable VALUES(@account,@password,@name,@age,@gender,@birthday,@email)";
SqlCommand sqlComd = new SqlCommand(query, sqlCon);
sqlComd.Parameters.AddWithValue("@account", userAccountmodel.account);
sqlComd.Parameters.AddWithValue("@password", userAccountmodel.password);
sqlComd.Parameters.AddWithValue("@name", userAccountmodel.name);
sqlComd.Parameters.AddWithValue("@age", userAccountmodel.age);
sqlComd.Parameters.AddWithValue("@gender", userAccountmodel.gender);
sqlComd.Parameters.AddWithValue("@birthday", userAccountmodel.birthday);
sqlComd.Parameters.AddWithValue("@email", userAccountmodel.email);
sqlComd.ExecuteNonQuery();
}
return(RedirectToAction("Index"));
}
public ActionResult StorageAccounts(int?id)
{
if (id == null)
{
throw new HttpException((int)HttpStatusCode.NotFound, string.Empty);
}
UserAccountsModel userAccountsModel = null;
var resultAction = Condition()
.DoIfNotAjax(() => View(userAccountsModel))
.DoIfAjax(() => Json(new AjaxResult
{
MainPanelHtml = this.RenderViewToString("~/Views/User/Controls/StorageAccountsControl.ascx", userAccountsModel)
}, JsonRequestBehavior.AllowGet));
Identity identity = User.Identity;
if (identity.UserId != id && !HttpContext.User.IsInRole(Constants.RoleAdmin))
{
ModelState.AddModelError("forbidden", ValidationResources.NoPermissionsError);
return(resultAction);
}
User targetUser = _userService.Load(id.Value);
if (targetUser == null)
{
ModelState.AddModelError("usernotfound", ValidationResources.UserNotFoundError);
return(resultAction);
}
userAccountsModel = new UserAccountsModel();
userAccountsModel.CanAddAcounts = identity.UserId == id;
userAccountsModel.CanDeleteAcounts = identity.UserId == id;
userAccountsModel.CanEditAcounts = identity.UserId == id;
StorageAccountsCollection targetAccountsCollection = new StorageAccountsCollection().Init(_accountService, _storageService, id.Value);
userAccountsModel.AccountsCollection = targetAccountsCollection;
return(resultAction);
}
public ActionResult Login(UserAccountsModel model, bool ConnectToLiveDatabase, string returnUrl)
{
//bypass login
if (Server.MachineName == Helper.DEVCOMPUTERNAME)
{
if (string.IsNullOrEmpty(model.Username))
{
model.Username = "******";
}
if (string.IsNullOrEmpty(model.Password))
{
model.Password = "******";
}
}
string hashedPassword = HashPassword(model.Password);
UserAccountsModel userAccount = get(model.Username, hashedPassword);
if (userAccount == null || string.IsNullOrWhiteSpace(hashedPassword))
{
ModelState.AddModelError("", "Invalid username or password");
ViewBag.Username = model.Username;
ViewBag.ReturnUrl = returnUrl;
return(View(model));
}
else
{
setLoginSession(Session, userAccount, ConnectToLiveDatabase);
if (!userAccount.ResetPassword)
{
return(RedirectToLocal(returnUrl));
}
else
{
TempData["UserAccountsModel"] = userAccount;
return(RedirectToAction(nameof(UserAccountsController.ChangePassword), CONTROLLERNAME, new { returnUrl = returnUrl }));
}
}
}
public void update(UserAccountsModel model, string log)
{
if (model.Roles_List != null)
{
model.Roles = string.Join(",", model.Roles_List.ToArray());
}
if (model.Interest_List != null)
{
model.Interest = string.Join(",", model.Interest_List.ToArray());
}
if (model.Branches_List != null)
{
model.Branches = string.Join(",", model.Branches_List.ToArray());
}
WebDBConnection.Update(db.Database, "UserAccounts",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Username.Name, model.Username),
DBConnection.getSqlParameter(UserAccountsModel.COL_Fullname.Name, model.Fullname),
DBConnection.getSqlParameter(UserAccountsModel.COL_Birthday.Name, model.Birthday),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches.Name, model.Branches),
DBConnection.getSqlParameter(UserAccountsModel.COL_Active.Name, model.Active),
DBConnection.getSqlParameter(UserAccountsModel.COL_ResetPassword.Name, model.ResetPassword),
DBConnection.getSqlParameter(UserAccountsModel.COL_Email.Name, model.Email),
DBConnection.getSqlParameter(UserAccountsModel.COL_Address.Name, model.Address),
DBConnection.getSqlParameter(UserAccountsModel.COL_Phone1.Name, model.Phone1),
DBConnection.getSqlParameter(UserAccountsModel.COL_Phone2.Name, model.Phone2),
DBConnection.getSqlParameter(UserAccountsModel.COL_Notes.Name, model.Notes),
DBConnection.getSqlParameter(UserAccountsModel.COL_Interest.Name, model.Interest),
DBConnection.getSqlParameter(UserAccountsModel.COL_PromotionEvents_Id.Name, model.PromotionEvents_Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Roles.Name, model.Roles)
);
updateLoginSession(Session);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
/* CHANGE PASSWORD PAGE *******************************************************************************************************************************/
public ActionResult ChangePassword(string returnUrl)
{
ViewBag.ReturnUrl = returnUrl;
UserAccountsModel model = (UserAccountsModel)TempData["UserAccountsModel"];
if (model != null && model.ResetPassword)
{
ViewBag.CurrentPassword = SettingsController.get().ResetPassword;
}
object Id = getUserId(Session);
if (model == null)
{
if (Id == null)
{
return(RedirectToAction(nameof(Login)));
}
else
{
model = get((Guid)Id);
}
}
return(View(model));
}
public static UserAccountRolesModel getAccesses(UserAccountsModel UserAccount)
{
UserAccountRolesModel model = new UserAccountRolesModel();
foreach (UserAccountRolesModel item in get(null, UserAccount))
{
//Reminders
if (item.Reminders_Add)
{
model.Reminders_Add = true;
}
if (item.Reminders_Edit)
{
model.Reminders_Edit = true;
}
if (item.Reminders_View)
{
model.Reminders_View = true;
}
//Birthdays
if (item.Birthdays_View)
{
model.Birthdays_View = true;
}
//UserAccounts
if (item.UserAccounts_Add)
{
model.UserAccounts_Add = true;
}
if (item.UserAccounts_Edit)
{
model.UserAccounts_Edit = true;
}
if (item.UserAccounts_View)
{
model.UserAccounts_View = true;
}
if (item.UserAccounts_ResetPassword)
{
model.UserAccounts_ResetPassword = true;
}
if (item.UserAccounts_EditRoles)
{
model.UserAccounts_EditRoles = true;
}
if (item.UserAccounts_ViewAllRoles)
{
model.UserAccounts_ViewAllRoles = true;
}
//UserAccountRoles
if (item.UserAccountRoles_Add)
{
model.UserAccountRoles_Add = true;
}
if (item.UserAccountRoles_Edit)
{
model.UserAccountRoles_Edit = true;
}
if (item.UserAccountRoles_View)
{
model.UserAccountRoles_View = true;
}
//Settings
if (item.Settings_Edit)
{
model.Settings_Edit = true;
}
if (item.Settings_View)
{
model.Settings_View = true;
}
//Branches
if (item.Branches_Add)
{
model.Branches_Add = true;
}
if (item.Branches_Edit)
{
model.Branches_Edit = true;
}
if (item.Branches_View)
{
model.Branches_View = true;
}
//PromotionEvents
if (item.PromotionEvents_Add)
{
model.PromotionEvents_Add = true;
}
if (item.PromotionEvents_Edit)
{
model.PromotionEvents_Edit = true;
}
if (item.PromotionEvents_View)
{
model.PromotionEvents_View = true;
}
//PettyCashRecordsCategories
if (item.PettyCashRecordsCategories_Add)
{
model.PettyCashRecordsCategories_Add = true;
}
if (item.PettyCashRecordsCategories_Edit)
{
model.PettyCashRecordsCategories_Edit = true;
}
if (item.PettyCashRecordsCategories_View)
{
model.PettyCashRecordsCategories_View = true;
}
//Languages
if (item.Languages_Add)
{
model.Languages_Add = true;
}
if (item.Languages_Edit)
{
model.Languages_Edit = true;
}
if (item.Languages_View)
{
model.Languages_View = true;
}
//LessonTypes
if (item.LessonTypes_Add)
{
model.LessonTypes_Add = true;
}
if (item.LessonTypes_Edit)
{
model.LessonTypes_Edit = true;
}
if (item.LessonTypes_View)
{
model.LessonTypes_View = true;
}
//LessonPackages
if (item.LessonPackages_Add)
{
model.LessonPackages_Add = true;
}
if (item.LessonPackages_Edit)
{
model.LessonPackages_Edit = true;
}
if (item.LessonPackages_View)
{
model.LessonPackages_View = true;
}
//Consignments
if (item.Consignments_Add)
{
model.Consignments_Add = true;
}
if (item.Consignments_Edit)
{
model.Consignments_Edit = true;
}
if (item.Consignments_View)
{
model.Consignments_View = true;
}
//Vouchers
if (item.Vouchers_Add)
{
model.Vouchers_Add = true;
}
if (item.Vouchers_Edit)
{
model.Vouchers_Edit = true;
}
if (item.Vouchers_View)
{
model.Vouchers_View = true;
}
//Suppliers
if (item.Suppliers_Add)
{
model.Suppliers_Add = true;
}
if (item.Suppliers_Edit)
{
model.Suppliers_Edit = true;
}
if (item.Suppliers_View)
{
model.Suppliers_View = true;
}
//Units
if (item.Units_Add)
{
model.Units_Add = true;
}
if (item.Units_Edit)
{
model.Units_Edit = true;
}
if (item.Units_View)
{
model.Units_View = true;
}
//ExpenseCategories
if (item.ExpenseCategories_Add)
{
model.ExpenseCategories_Add = true;
}
if (item.ExpenseCategories_Edit)
{
model.ExpenseCategories_Edit = true;
}
if (item.ExpenseCategories_View)
{
model.ExpenseCategories_View = true;
}
//Services
if (item.Services_Add)
{
model.Services_Add = true;
}
if (item.Services_Edit)
{
model.Services_Edit = true;
}
if (item.Services_View)
{
model.Services_View = true;
}
//Products
if (item.Products_Add)
{
model.Products_Add = true;
}
if (item.Products_Edit)
{
model.Products_Edit = true;
}
if (item.Products_View)
{
model.Products_View = true;
}
//SaleInvoices
if (item.SaleInvoices_Add)
{
model.SaleInvoices_Add = true;
}
if (item.SaleInvoices_Edit)
{
model.SaleInvoices_Edit = true;
}
if (item.SaleInvoices_View)
{
model.SaleInvoices_View = true;
}
if (item.SaleInvoices_Approve)
{
model.SaleInvoices_Approve = true;
}
if (item.SaleInvoices_TutorTravelCost_View)
{
model.SaleInvoices_TutorTravelCost_View = true;
}
//Payments
if (item.Payments_Add)
{
model.Payments_Add = true;
}
if (item.Payments_Edit)
{
model.Payments_Edit = true;
}
if (item.Payments_View)
{
model.Payments_View = true;
}
if (item.Payments_Approve)
{
model.Payments_Approve = true;
}
//PettyCashRecords
if (item.PettyCashRecords_Add)
{
model.PettyCashRecords_Add = true;
}
if (item.PettyCashRecords_Edit)
{
model.PettyCashRecords_Edit = true;
}
if (item.PettyCashRecords_View)
{
model.PettyCashRecords_View = true;
}
if (item.PettyCashRecords_Approve)
{
model.PettyCashRecords_Approve = true;
}
//Inventory
if (item.Inventory_Add)
{
model.Inventory_Add = true;
}
if (item.Inventory_Edit)
{
model.Inventory_Edit = true;
}
if (item.Inventory_View)
{
model.Inventory_View = true;
}
//LessonSessions
if (item.LessonSessions_Add)
{
model.LessonSessions_Add = true;
}
if (item.LessonSessions_Edit)
{
model.LessonSessions_Edit = true;
}
if (item.LessonSessions_EditReviewAndInternalNotes)
{
model.LessonSessions_EditReviewAndInternalNotes = true;
}
if (item.LessonSessions_View)
{
model.LessonSessions_View = true;
}
if (item.LessonSessions_InternalNotes_View)
{
model.LessonSessions_InternalNotes_View = true;
}
//HourlyRates
if (item.HourlyRates_Add)
{
model.HourlyRates_Add = true;
}
if (item.HourlyRates_Edit)
{
model.HourlyRates_Edit = true;
}
if (item.HourlyRates_View)
{
model.HourlyRates_View = true;
}
//PayrollPayments
if (item.PayrollPayments_Add)
{
model.PayrollPayments_Add = true;
}
if (item.PayrollPayments_Edit)
{
model.PayrollPayments_Edit = true;
}
if (item.PayrollPayments_View)
{
model.PayrollPayments_View = true;
}
if (item.PayrollPayments_Approve)
{
model.PayrollPayments_Approve = true;
}
//TutorSchedules
if (item.TutorSchedules_Add)
{
model.TutorSchedules_Add = true;
}
if (item.TutorSchedules_Edit)
{
model.TutorSchedules_Edit = true;
}
if (item.TutorSchedules_View)
{
model.TutorSchedules_View = true;
}
//StudentSchedules
if (item.StudentSchedules_Add)
{
model.StudentSchedules_Add = true;
}
if (item.StudentSchedules_Edit)
{
model.StudentSchedules_Edit = true;
}
if (item.StudentSchedules_View)
{
model.StudentSchedules_View = true;
}
//ClubSchedules
if (item.ClubSchedules_Add)
{
model.ClubSchedules_Add = true;
}
if (item.ClubSchedules_Edit)
{
model.ClubSchedules_Edit = true;
}
if (item.ClubSchedules_View)
{
model.ClubSchedules_View = true;
}
//Files
if (item.Files_Add)
{
model.Files_Add = true;
}
if (item.Files_Edit)
{
model.Files_Edit = true;
}
if (item.Files_View)
{
model.Files_View = true;
}
if (item.Files_EditGlobal)
{
model.Files_EditGlobal = true;
}
//Income Statement
if (item.IncomeStatement_View)
{
model.IncomeStatement_View = true;
}
if (item.IncomeStatement_ViewProfit)
{
model.IncomeStatement_ViewProfit = true;
}
}
return(model);
}
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 void setLoginSession(HttpSessionStateBase Session, UserAccountsModel model)
{
setLoginSession(Session, model, null);
}
public List <UserAccountsModel> get(int?skip, int?take, Guid?Default_Branches_Id, bool showAllBranches, Guid?Id, string Username, string Password, int?Active, Guid?UserAccountRoles_Id, int?BirthdayListMonth, string FILTER_Keyword, Guid?Language_Id, string Role, bool ShowOnlyOwnUserData)
{
UserAccountsModel UserAccount = getUserAccount(Session);
Guid?UserAccountId = null;
if (UserAccount != null)
{
UserAccountId = UserAccount.Id;
}
string BranchClause = null;
if (!showAllBranches)
{
BranchClause = string.Format(" AND UserAccounts.Branches LIKE '%{0}%' ", Helper.getActiveBranchId(Session));
}
string RoleClause = null;
if (!string.IsNullOrEmpty(Role))
{
RoleClause = string.Format(" AND UserAccounts.Roles LIKE '%{0}%' ", Role);
}
string sql = string.Format(@"
SELECT UserAccounts.*,
COALESCE(LessonPackages.ActiveLessonPackages,0) AS ActiveLessonPackages
FROM UserAccounts
LEFT JOIN (
SELECT SaleInvoices.Customer_UserAccounts_Id, COUNT(SaleInvoiceItems.Id) AS ActiveLessonPackages
FROM SaleInvoiceItems
LEFT JOIN SaleInvoices ON SaleInvoices.Id = SaleInvoiceItems.SaleInvoices_Id
WHERE SaleInvoiceItems.SessionHours_Remaining > 0 AND SaleInvoices.Cancelled = 0
GROUP BY SaleInvoices.Customer_UserAccounts_Id
) LessonPackages ON LessonPackages.Customer_UserAccounts_Id = UserAccounts.Id
WHERE 1=1
AND (@Id IS NULL OR UserAccounts.Id = @Id)
AND (@Id IS NOT NULL OR (
@Username IS NULL OR UserAccounts.Username = @Username)
AND (@Password IS NULL OR UserAccounts.Password = @Password)
AND (@Branches_Id IS NULL OR UserAccounts.Branches_Id = @Branches_Id)
AND (@Languages_Id IS NULL OR UserAccounts.Interest LIKE '%'+CONVERT(varchar(MAX),@Languages_Id)+'%')
AND (@Active IS NULL OR UserAccounts.Active = @Active)
AND (@UserAccountRoles_Id IS NULL OR UserAccounts.Roles LIKE '%'+CONVERT(varchar(MAX),@UserAccountRoles_Id)+'%')
AND (@BirthdayListMonth IS NULL OR (
MONTH(UserAccounts.Birthday) = @BirthdayListMonth
AND (MONTH(GETDATE()) <> @BirthdayListMonth OR DAY(UserAccounts.Birthday) >= DAY(GETDATE()))
)
)
AND (@FILTER_Keyword IS NULL OR (
UserAccounts.Fullname LIKE '%'+@FILTER_Keyword+'%'
OR UserAccounts.Username LIKE '%'+@FILTER_Keyword+'%'
OR UserAccounts.No LIKE '%'+@FILTER_Keyword+'%'
))
AND (@ShowOnlyOwnUserData = 0 OR (UserAccounts.Id = @UserAccounts_Id))
{0}{1}
)
ORDER BY UserAccounts.Fullname ASC
OFFSET COALESCE(@SKIP,0) ROWS
FETCH NEXT COALESCE(CONVERT(int, @TAKE),0x7ffffff) ROWS ONLY
", BranchClause, RoleClause
);
List <UserAccountsModel> models = db.Database.SqlQuery <UserAccountsModel>(sql,
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Username.Name, Username),
DBConnection.getSqlParameter(UserAccountsModel.COL_Password.Name, Password),
DBConnection.getSqlParameter(UserAccountsModel.COL_Active.Name, Active),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches_Id.Name, Default_Branches_Id),
DBConnection.getSqlParameter("BirthdayListMonth", BirthdayListMonth),
DBConnection.getSqlParameter("UserAccountRoles_Id", UserAccountRoles_Id),
DBConnection.getSqlParameter("Languages_Id", Language_Id),
DBConnection.getSqlParameter("FILTER_Keyword", FILTER_Keyword),
DBConnection.getSqlParameter("SKIP", skip),
DBConnection.getSqlParameter("TAKE", take),
DBConnection.getSqlParameter("ShowOnlyOwnUserData", ShowOnlyOwnUserData),
DBConnection.getSqlParameter("UserAccounts_Id", UserAccountId)
).ToList();
foreach (UserAccountsModel model in models)
{
if (!string.IsNullOrEmpty(model.Roles))
{
model.Roles_List = model.Roles.Split(',').ToList();
}
if (!string.IsNullOrEmpty(model.Interest))
{
model.Interest_List = model.Interest.Split(',').ToList();
}
if (!string.IsNullOrEmpty(model.Branches))
{
model.Branches_List = model.Branches.Split(',').ToList();
}
}
return(models);
}