public async Task <Logic.ViewModels.UserVM> GetUser(Interfaces.IConnectionUtility connectionUtility, string email_address)
{
using (var conn = connectionUtility.GetConnection())
{
return(await getUser(conn, email_address));
}
}
public async Task <DBModels.SessionsEntity> GetSessionModel(Interfaces.IConnectionUtility connectionUtility, string sessionToken)
{
using (var conn = connectionUtility.GetConnection())
{
return(await GetSessionModel(conn, sessionToken));
}
}
public async Task <Logic.Models.CreateUserResult> CreateUser(Interfaces.IConnectionUtility connectionUtility, DBModels.UserEntity userEntity, string password)
{
using (var conn = connectionUtility.GetConnection())
{
return(await CreateUser(conn, userEntity, password));
}
}
public async Task <Models.SessionValidResult> IsSessionValid(Interfaces.IConnectionUtility connectionUtility, string sessionToken, string user_id)
{
using (var conn = connectionUtility.GetConnection())
{
return(await IsSessionValid(conn, sessionToken, user_id));
}
}
public static async Task <bool> Update <T>(Interfaces.IConnectionUtility connUtility, IEnumerable <T> entitiesToUpdate)
where T : class, Interfaces.IDBEntity
{
if (entitiesToUpdate == null || entitiesToUpdate.Count() == 0)
{
return(false);
}
foreach (var entity in entitiesToUpdate)
{
entity.modified_on = DateTime.UtcNow;
}
using (var conn = connUtility.GetConnection())
{
bool result = true;
foreach (var entity in entitiesToUpdate)
{
result = result && await conn.UpdateAsync <T>(entity);
}
return(result);
}
}
public async Task <DateTime?> RenewSession(Interfaces.IConnectionUtility connectionUtility, ViewModels.SessionVM session, int?timeoutInSeconds = null)
{
using (var connection = connectionUtility.GetConnection())
{
return(await RenewSession(connection, session, timeoutInSeconds));
}
}
public async Task <Logic.Models.DBSaveResult> SaveResult(Interfaces.IConnectionUtility connectionUtility, Models.QuestionSaveRequest saveRequest)
{
var result = new Logic.Models.DBSaveResult();
try
{
if (saveRequest != null && !saveRequest.IsEmpty)
{
if (saveRequest.entitesToUpdate != null && saveRequest.entitesToUpdate.Length > 0)
{
var itemsToUpdate = Mappers.ObjectMapper.Instance.Mapper.Map <DBModels.QuestionEntity[]>(saveRequest.entitesToUpdate);
result.TotalRecordSentToSave += itemsToUpdate.Length;
if (itemsToUpdate != null && itemsToUpdate.Length > 0)
{
if (await DB.DBUtility.Update <DBModels.QuestionEntity>(connectionUtility, itemsToUpdate))
{
result.TotalRecordUpdated = itemsToUpdate.Length;
}
}
}
if (saveRequest.entitesToDelete != null && saveRequest.entitesToDelete.Length > 0)
{
var itemsToDelete = Mappers.ObjectMapper.Instance.Mapper.Map <DBModels.QuestionEntity[]>(saveRequest.entitesToDelete);
result.TotalRecordSentToSave += itemsToDelete.Length;
if (itemsToDelete != null && itemsToDelete.Length > 0)
{
if (await DB.DBUtility.Delete <DBModels.QuestionEntity>(connectionUtility, itemsToDelete))
{
result.TotalRecordDeleted = itemsToDelete.Length;
}
}
}
if (saveRequest.entitesToInsert != null && saveRequest.entitesToInsert.Length > 0)
{
var itemsToInsert = Mappers.ObjectMapper.Instance.Mapper.Map <DBModels.QuestionEntity[]>(saveRequest.entitesToInsert);
result.TotalRecordSentToSave += itemsToInsert.Length;
if (itemsToInsert != null && itemsToInsert.Length > 0)
{
object i = await DB.DBUtility.Insert <DBModels.QuestionEntity>(connectionUtility, itemsToInsert);
if (i == null && !string.IsNullOrEmpty(i.ToString()))
{
result.TotalRecordInserted += 1;
}
}
}
}
result.Success = true;
}
finally
{
}
return(result);
}
public static async Task <T> GetScalar <T>(Interfaces.IConnectionUtility connUtility, string sql, Dictionary <string, object> parameters)
{
if (string.IsNullOrEmpty(sql))
{
return(default(T));
}
return(await GetScalar <T>(connUtility.GetConnection(), sql, parameters));
}
public static async Task <IEnumerable <T> > GetData <T>(Interfaces.IConnectionUtility connUtility, string sql, Dictionary <string, object> parameters)
where T : class, Interfaces.IDBEntity
{
if (string.IsNullOrEmpty(sql))
{
return(null);
}
using (var connection = connUtility.GetConnection())
{
return(await GetData <T>(connection, sql, parameters));
}
}
public async Task <Validators.StudentValidationResult> SaveStudent(Interfaces.IConnectionUtility connection, ViewModels.StudentVM student)
{
Validators.StudentValidator valid = new Validators.StudentValidator();
var result = await valid.ValidateStudent(this, connection, student);
if (!result.HasError)
{
bool dbResult = await saveStudent(connection, student);
result.ValidationSuccess = dbResult;
}
return(result);
}
public async Task <bool> Logout(Interfaces.IConnectionUtility connectionUtility, string sessionToken)
{
if (string.IsNullOrEmpty(sessionToken))
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
string logoutSql = @"
SET @user_id = '';
Select @user_id = user_id From
sessions where session_token = @session_token;
Update users
SET last_log_out = UTC_TIMESTAMP()
Where id = @user_id;
INSERT INTO session_archives(id, user_id, session_token, login_time, last_activity_time, login_method, log_out_time, is_deleted, modified_on)
Select id, user_id, session_token, login_time, last_activity_time, login_method, UTC_TIMESTAMP(), 0, UTC_TIMESTAMP()
from sessions
Where session_token = @session_token;
delete from sessions
Where session_token = @session_token;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@session_token", sessionToken);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
finally
{
}
}
return(false);
}
public async Task <ViewModels.AuthenticateResultVM> Login(Interfaces.IConnectionUtility connectionUtility, string email, string password,
Interfaces.ISessionTokenProvider provider,
int?sessionTimeoutInSeconds = null)
{
if (string.IsNullOrEmpty(email) || string.IsNullOrEmpty(password))
{
return(null);
}
sessionTimeoutInSeconds = sessionTimeoutInSeconds.HasValue ? sessionTimeoutInSeconds.Value : Constants.Constants.SESSION_TIMEOUT_IN_SECONDS;
using (var conn = connectionUtility.GetConnection())
{
try
{
var entity = await authenticate(conn, email, password);
if (entity == null)
{
return(new ViewModels.AuthenticateResultVM {
ResultType = Enums.AuthenticateResultType.UserDoesNotExists,
Session = null
});
}
DBModels.SessionViewEntity sessionEntity = null;
if (entity.ResultType == Enums.AuthenticateResultType.Success)
{
sessionEntity = await createSession(conn, entity.id, provider.GetToken(), sessionTimeoutInSeconds.Value);
}
else if (entity.ResultType == Enums.AuthenticateResultType.PasswordDoesNotMatch)
{
await increasePasswordFailCount(conn, entity.id);
}
ViewModels.AuthenticateResultVM result = new ViewModels.AuthenticateResultVM()
{
ResultType = entity.ResultType,
Session = Mappers.ObjectMapper.Instance.Mapper.Map <ViewModels.SessionVM>(sessionEntity)
};
return(result);
}
finally
{
}
}
}
public static async Task <IEnumerable <T> > GetDataFromTable <T>(Interfaces.IConnectionUtility connUtility, string tableName, Dictionary <string, object> parameters)
where T : class, Interfaces.IDBEntity
{
if (string.IsNullOrEmpty(tableName))
{
return(null);
}
using (var connection = connUtility.GetConnection())
{
var data = await Dapper.SqlMapper.QueryAsync <T>(connection, $"Select * From {tableName}", parameters);
return(data);
}
}
public static async Task <object> Insert <T>(Interfaces.IConnectionUtility connUtility, IEnumerable <T> entitesToInsert)
where T : class, Interfaces.IDBEntity
{
object result = 0;
if (entitesToInsert == null || entitesToInsert.Count() == 0)
{
return(result);
}
using (var connection = connUtility.GetConnection())
{
result = await Insert <T>(connection, entitesToInsert);
}
return(result);
}
public async Task <bool> ChangePassword(Interfaces.IConnectionUtility connectionUtility, ViewModels.SessionVM session, string newPassword)
{
if (session == null)
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
var sessionValidResult = await IsSessionValid(conn, session.SessionToken, session.UserId);
if (sessionValidResult.IsValid)
{
string logoutSql = @"
Update users
SET login_password = aes_encrypt(@password, @token),
modified_on = UTC_TIMESTAMP()
Where id = @user_id;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@user_id", session.UserId);
parameters.Add("@sessionid", session.Id);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
}
finally
{
}
}
return(false);
}
public async Task <bool> ChangePassword(Interfaces.IConnectionUtility connectionUtility, string userId, string oldPassword, string newPassword)
{
if (string.IsNullOrEmpty(userId))
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
string logoutSql = @"
Update users
SET login_password = aes_encrypt(@password, @token),
modified_on = UTC_TIMESTAMP()
Where id = @user_id AND cast(aes_decrypt(login_password, @token) AS CHAR(50)) = @oldPassword;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@user_id", userId);
parameters.Add("@oldPassword", oldPassword);
parameters.Add("@password", newPassword);
parameters.Add("@token", Constants.Constants.TOKEN_TEXT);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
finally
{
}
}
return(false);
}
private async Task <bool> saveStudent(Interfaces.IConnectionUtility connection, ViewModels.StudentVM student)
{
student.Id = Guid.NewGuid().ToString();
using (var conn = connection.GetConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
await DB.DBUtility.Insert <DBModels.StudentEntity>(tran.Connection,
new DBModels.StudentEntity[] { Mappers.ObjectMapper.Instance.Mapper.Map <DBModels.StudentEntity>(student) });
var userEntity = new DBModels.UserEntity();
userEntity.id = student.Id;
userEntity.user_email = student.StudentEmail;
userEntity.is_confirmed = true;
userEntity.is_locked_out = false;
userEntity.num_of_failed_password_attempt = 0;
var createUserResult = await CreateUser(tran.Connection, userEntity, student.Password);
tran.Commit();
return(createUserResult.Success);
}
catch
{
tran.Rollback();
}
finally
{
tran.Dispose();
}
}
return(false);
}
async Task <Enums.AvailiabilityEnum> checkEmailAvailability(Providers.StudentProvider provider, Interfaces.IConnectionUtility connection, string email)
{
try
{
var result = await provider.CheckEmailAlreadyRegistered(connection, email);
if (result)
{
return(Enums.AvailiabilityEnum.NotAvailable);
}
return(Enums.AvailiabilityEnum.Available);
}
catch
{
return(Enums.AvailiabilityEnum.Unknown);
}
}
public async Task <StudentValidationResult> ValidateStudent(Providers.StudentProvider provider, Interfaces.IConnectionUtility conn, ViewModels.StudentVM studentVM)
{
var result = new StudentValidationResult();
result.DisplayNameError = !checkDisplayName(studentVM.StudentDisplayName);
result.NameError = !checkName(studentVM.StudentName);
result.EmailError = !checkEmail(studentVM.StudentEmail);
if (!result.EmailError)
{
result.EmailAvailiability = await checkEmailAvailability(provider, conn, studentVM.StudentEmail);
}
if (!result.DisplayNameError)
{
result.DisplayNameAvaliability = await checkDisplayNameAvailability(provider, conn, studentVM.StudentDisplayName);
}
result.PasswordError = !checkPassword(studentVM.Password);
return(result);
}
async Task <Enums.AvailiabilityEnum> checkDisplayNameAvailability(Providers.StudentProvider provider, Interfaces.IConnectionUtility connection, string displayName)
{
try
{
var result = await provider.CheckDisplayNameAlreadyUsed(connection, displayName);
if (result)
{
return(Enums.AvailiabilityEnum.NotAvailable);
}
return(Enums.AvailiabilityEnum.Available);
}
catch
{
return(Enums.AvailiabilityEnum.Unknown);
}
}
public async Task <Logic.ViewModels.QuestionVM[]> Get(Interfaces.IConnectionUtility connectionUtility)
{
var entities = await DB.DBUtility.GetData <DBModels.QuestionEntity>(connectionUtility, "select * from questions where is_deleted = 0", null);
return(Mappers.ObjectMapper.Instance.Mapper.Map <Logic.ViewModels.QuestionVM[]>(entities));
}
