public Result InsertAnswers(int userId, List <NurseSelfAssessmentAnswersEntity> answer_list) /* * Insert a lisy of Answers into the database * */ { Result result = new Result(); //TODO: try { var con = new DapperConnectionManager(); var query = new QueryEntity(); foreach (NurseSelfAssessmentAnswersEntity entity in answer_list) { // Temporary solution need to impove to become a batch process result = InsertAnswer(userId, entity); } result = con.ExecuteQuery <UsersQuizzesEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); } return(new Result(false)); }
public Result GetAnswersbyUserQuizzId(int userQuizzId) /* * Get all answers of a UserQuizzId * */ { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = new { UserQuizId = userQuizzId }; query.Query = @"SELECT * FROM NurseSelfAssessmentAnswers WHERE UserQuizId = @UserQuizId "; result = con.ExecuteQuery <NurseSelfAssessmentAnswersEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); } return(new Result(false)); }
public Result ShortListApplication(int applicationId) { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = new { ApplicationId = applicationId, ApplicationStatus = "SHORTLISTED", IsShortlisted = true, ShortListedDate = DateTime.Now, IsDeclined = false }; query.Query = @" Update JobApplications SET IsShortlisted = @IsShortlisted, ShortListedDate = @ShortListedDate, IsDeclined = @IsDeclined WHERE ApplicationId = @ApplicationId "; result = con.ExecuteGetOneItemQuery <JobApplicationEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result Update(AdministratorEntity entity) { var con = new DapperConnectionManager(); var query = new QueryEntity(); var result = new Result(); if (!string.IsNullOrEmpty(entity.Password)) { if (entity.Password.Length < 6) { result = new Result(false); result.Message = "Password length invalid"; return(result); } var credentials = new CredentialsManager(); var hash = credentials.GenerateSalt(); entity.Password = credentials.EncodePassword(entity.Password, hash); entity.Hash = hash; query.Query = @"UPDATE Administrators set Name = @Name, Password = @Password, Hash = @Hash where AdministratorId = @AdministratorId"; } else { query.Query = @"UPDATE Administrators set Name = @Name where AdministratorId = @AdministratorId"; } query.Entity = entity; result = con.ExecuteQuery(query); result.Message = result.Success ? "The Adminsitrator has been updated" : "An error occurred"; result.Entity = entity.AdministratorId; return(result); }
public Result Delete(int id) { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT * FROM Articles where CategoryId = @CategoryId"; query.Entity = new { CategoryId = id }; var result = con.ExecuteQuery <CategoryEntity>(query); if (!result.Success) { result.Message = "Category not found"; return(result); } var r = (IEnumerable <CategoryEntity>)result.Entity; if (r.Any()) { result.Success = false; result.Entity = null; result.Message = "Articles are using this category, it can not be deleted"; return(result); } query.Query = @"DELETE FROM Categories where CategoryId = @CategoryId"; return(con.ExecuteQuery(query)); }
public Result ShortlistOrDeclineJobApplication(JobApplicationEntity jobApplication) { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = jobApplication; query.Query = @" UPDATE JobApplications SET ApplicationStatus = @ApplicationStatus , IsShortlisted = @IsShortlisted, IsDeclined = @IsDeclined, ShortListedDate = ISNULL(@ShortListedDate, NULL), DeclinedDate = ISNULL(@DeclinedDate, NULL) WHERE JobApplicationId = @JobApplicationId AND JobListingId = @JobListingId AND UserId = @UserId; SELECT * FROM JobApplications WHERE JobApplicationId = @JobApplicationId AND JobListingId = @JobListingId and UserId = @UserId; "; result = con.ExecuteGetOneItemQuery <JobApplicationEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result Delete(UserEntity entity) { var credentials = new CredentialsManager(); var result = credentials.ValidateUserToken(entity.Token); if (!result.Success) { return(result); } var user = (UserEntity)result.Entity; if (user.UserId != entity.UserId) { result.Message = "Forbidden operation"; result.Success = false; return(result); } var con = new DapperConnectionManager(); var query = new QueryEntity(); entity.Email = entity.Email.Trim().ToLower(); query.Query = @"Update Users Set Active = 0 where UserId = @UserId"; query.Entity = new { UserId = entity.UserId }; result = con.ExecuteQuery <UserEntity>(query); result.Message = result.Success ? "The user has been deleted" : "An error has occurred"; return(result); }
public Result GetEmployerById(int employerId) { var result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT * FROM Employers where EmployerId = @EmployerId"; query.Entity = new { EmployerId = employerId }; result = con.ExecuteGetOneItemQuery <EmployerEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result = result ?? new Result(false); result.Message = "An error occurred" + ex.Message; } return(result); }
public Result Get(int id) { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT a.ArticleId, a.Name as ArticleName, a.Title as ArticleTitle, a.CategoryId, a.Published, a.Type as ArticleType, c.Name, c.Title, c.Type, c.ContentItemId, c.Position, c.Text from Articles as a LEFT JOIN ContentItems as c on a.ArticleId = c.ArticleId where a.ArticleId = @ArticleId and a.Active = 1 ORDER BY c.Position ASC"; query.Entity = new { ArticleId = id }; var result = con.ExecuteQuery(query); if (!result.Success) { result.Message = "Article not found"; return(result); } var r = (IEnumerable <dynamic>)result.Entity; if (!r.Any()) { return(result); } var f = r.First(); var articleEntity = new ArticleEntity(); articleEntity.ArticleId = f.ArticleId; articleEntity.Name = f.ArticleName; articleEntity.Title = f.ArticleTitle; articleEntity.CategoryId = f.CategoryId; articleEntity.Published = f.Published; articleEntity.Type = f.ArticleType; articleEntity.ContentItems = new List <ContentItemEntity>(); foreach (var item in r) { if (item.ContentItemId == null) { continue; } var contentItem = new ContentItemEntity { Name = item.Name, ContentItemId = item.ContentItemId, Text = item.Text, Position = item.Position, Type = item.Type, ArticleId = item.ArticleId, Title = item.Title, TextShort = (item.Text as string ?? string.Empty).Truncate() }; articleEntity.ContentItems.Add(contentItem); } result.Entity = articleEntity; return(result); }
public Result Get(int id) { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT * FROM Actions where ActionId = @ActionId"; query.Entity = new { ActionId = id }; var result = con.ExecuteQuery <ActionEntity>(query); if (!result.Success) { result.Message = "Action not found"; return(result); } var r = (IEnumerable <ActionEntity>)result.Entity; result.Entity = r.FirstOrDefault(); if (result.Entity == null) { result.Success = false; } return(result); }
public Result UpdateReasons(List <ReasonEntity> list) { var con = new DapperConnectionManager(); var query = new QueryEntity(); var result = new Result(); using (var scope = new TransactionScope()) { query.Query = @"DELETE FROM Reasons"; query.Entity = new { }; result = con.ExecuteQueryUnScoped(query); if (!result.Success) { result.Message = "An error occurred"; return(result); } foreach (var item in list) { query.Query = @"INSERT into Reasons (Ix, Text, Title, TextPrev) VALUES(@Ix, @Text, @Title, @TextPrev)"; query.Entity = item; result = con.ExecuteQueryUnScoped(query); if (!result.Success) { result.Message = "An error occurred"; return(result); } } scope.Complete(); } result.Message = "Reasons updated"; return(result); }
public Result GetConfiguration(string name) { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT * FROM Configurations where Name = @Name"; query.Entity = new { Name = name }; var result = con.ExecuteQuery <ConfigurationEntity>(query); if (!result.Success) { result.Message = "Configuration not found"; return(result); } var r = (IEnumerable <ConfigurationEntity>)result.Entity; result.Entity = r.FirstOrDefault(); if (result.Entity == null) { result.Success = false; } return(result); }
public Result Update(List <MenuEntity> list, string type) { var con = new DapperConnectionManager(); var queryList = new List <QueryEntity>(); var query = new QueryEntity { Query = @"DELETE from Menus WHERE Type = @Type", Entity = new { Type = type }, }; queryList.Add(query); foreach (var item in list) { item.Type = type; var queryItem = new QueryEntity() { Entity = item, Query = @"INSERT into Menus (Title, Href, Position, Type, Submenu, Separator) VALUES(@Title, @Href, @Position, @Type, @Submenu, @Separator)" }; queryList.Add(queryItem); } var result = con.ExecuteQueries(queryList); result.Message = result.Success ? "The menu have been modifed" : "An error has ocurred"; return(result); }
public Result GetArticles(int id) { var result = new Result(); try { var con = new DapperConnectionManager(); ///Sections var query = new QueryEntity(); query.Query = @"SELECT s.ArticleId, s.Name as ArticleName, s.Title as ArticleTitle, s.CategoryId, s.Date, cc.Name as CategoryName, c.Name, c.Title, c.Type, c.ContentItemId, c.Position, c.Text, c.ButtonLink, c.Image, c.Carousel, c.TitleImage, c.Video, c.Link from Articles as s LEFT JOIN ContentItems as c on s.ArticleId = c.ArticleId LEFT JOIN Categories as cc on s.CategoryId = cc.CategoryId where s.Published = 1 and s.ArticleId = @ArticleId ORDER BY s.ArticleId, c.Position ASC"; query.Entity = new { ArticleId = id }; result = con.ExecuteQuery(query); var articles = FormatArticles((IEnumerable <dynamic>)result.Entity); result.Entity = articles.FirstOrDefault(); } catch (Exception e) { Logger.Log(e); return(new Result(false)); } return(result ?? new Result(false)); }
public Result Get() { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT * FROM( SELECT *, RANK() OVER (PARTITION BY a.QuestionId , a.Value ORDER BY a.AnswerId) RK FROM Answers as a ) as d WHERE d.RK = 1 and d.Type IS NULL "; query.Entity = new { }; var result = con.ExecuteQuery <AnswerEntity>(query); if (!result.Success) { result.Message = "Answers not found"; return(result); } return(result); }
public Result Insert(EndorsedLogoEntity entity) { ImageManager imageManager = new ImageManager(); if (entity.ImageFile != null) { var resImgCheck = imageManager.CheckFileImageAndSave(entity.ImagePath, entity.ImageFile, 100, 100, 3000, 3000); if (!resImgCheck.Success) { return(resImgCheck); } entity.Image = resImgCheck.Entity.ToString(); //entity.Text = "Image EndorsedLogo"; } var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = entity; query.Query = @"INSERT INTO EndorsedLogos (Name, Image) VALUES(@Name, @Image)"; var result = con.InsertQuery(query); result.Message = result.Success ? "The EndorsedLogo has been created" : "An error occurred"; return(result); }
public Result GetCriteriaByListingId(int jobListingId) /* * Get all criteria of a listing * */ { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = new { JobListingId = jobListingId }; query.Query = @"SELECT * FROM JobListingCriteria WHERE JobListingId = @JobListingId "; result = con.ExecuteQuery <JobListingCriteriaEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result UpdateMembership(EmployerEntity entity) { var result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); entity.ModifyDate = DateTime.Now; query.Query = @" BEGIN TRAN Update Employers Set [MembershipType] = ISNULL( @MembershipType , MembershipType ) , [MembershipStartDate] = ISNULL( @MembershipStartDate , MembershipStartDate ) , [MembershipEndDate] = ISNULL( @MembershipEndDate , MembershipEndDate ) where EmployerId = @EmployerId; SELECT * FROM EMPLOYERS WHERE EmployerId = @EmployerId; COMMIT TRAN "; query.Entity = entity; result = con.ExecuteQuery <EmployerEntity>(query); result.Message = result.Success ? "The user details has been updated" : "An error has occurred"; } catch (Exception ex) { Logger.Log(ex); result = result ?? new Result(false); result.Message = "An error occurred"; throw; } return(result); }
public Result GetAnswerbyListingbyAspect(int listingId, int aspectId) /* * Get one aspect of a listing * */ { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = new { JobListingId = listingId, AspectId = aspectId }; query.Query = @"SELECT * FROM JobListingCriteria WHERE JobListingId = @JobListingId AND AspectId = @AspectId "; result = con.ExecuteQuery <JobListingCriteriaEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result UpdateFeedbackFromEmployer(JobApplicationEntity entity) { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = entity; query.Query = @" BEGIN TRAN Update JobApplications SET FeedbackFromEmployer = @FeedbackFromEmployer WHERE JobApplicationId = @JobApplicationId; Select * From JobApplications WHERE JobApplicationId = @JobApplicationId; COMMIT TRAN "; result = con.ExecuteGetOneItemQuery <JobApplicationEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result Insert(AdministratorEntity entity, bool seal = false) { var result = new Result(); if (entity.Password.Length < 6) { result = new Result(false); result.Message = "Password length invalid"; return(result); } var con = new DapperConnectionManager(); var query = new QueryEntity(); var credentials = new CredentialsManager(); var hash = credentials.GenerateSalt(); entity.Password = credentials.EncodePassword(entity.Password, hash); entity.Hash = hash; entity.Username = entity.Username.Trim().ToLower(); entity.Sealed = seal; query.Entity = entity; query.Query = @"INSERT INTO Administrators (Username, Password, Hash, Sealed, Name) VALUES(@Username, @Password, @Hash, @Sealed, @Name)"; result = con.InsertQuery(query); result.Message = result.Success ? "The administrator has been created" : "An error occurred"; result.Entity = entity; return(result); }
public Result GetJobApplicationByApplicationId(int applicationId) { Result result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = new { ApplicationId = applicationId }; query.Query = @" SELECT * FROM JobApplications WHERE JobApplicationId = @ApplicationId "; result = con.ExecuteGetOneItemQuery <JobApplicationEntity>(query); return(result); } catch (Exception ex) { Logger.Log(ex); result.Success = false; result.Message = ex.Message; } return(result); }
public Result Update(EndorsedLogoEntity entity) { ImageManager imageManager = new ImageManager(); if (entity.ImageFile != null) { var resImgCheck = imageManager.CheckFileImageAndSave(entity.ImagePath, entity.ImageFile, 30, 30, 3000, 3000); if (!resImgCheck.Success) { return(resImgCheck); } entity.Image = resImgCheck.Entity.ToString(); //entity.Text = "Image EndorsedLogo"; } var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Entity = entity; query.Query = @"UPDATE EndorsedLogos set name = @Name, Image = @Image where EndorsedLogoId = @EndorsedLogoId"; var result = con.ExecuteQuery(query); result.Message = result.Success ? "The EndorsedLogo has been updated" : "An error occurred"; result.Entity = entity.EndorsedLogoId; return(result); }
public Result Insert(RoleEntity entity) { var con = new DapperConnectionManager(); var query = new QueryEntity(); Result result; using (var scope = new TransactionScope()) { var linkEntity = new LinkEntity { Href = " ", Name = entity.Title, Type = "ROLE", Active = false }; query.Entity = linkEntity; query.Query = @"INSERT INTO Links (Name, Type, Href, Active) VALUES(@Name, @Type, @Href, @Active)"; result = con.InsertQueryUnScoped(query); if (!result.Success) { return(result); } var linkId = (int)result.Entity; entity.LinkId = linkId; query.Entity = entity; query.Query = @"INSERT INTO Roles (Name, LinkId, WhatIs, WhatIsTheirRole, Accountabilities, Examples, FurtherInformation, Title, LinkName, Pathways) VALUES(@Name, @LinkId, @WhatIs, @WhatIsTheirRole, @Accountabilities, @Examples, @FurtherInformation, @Title, @LinkName, @Pathways)"; result = con.InsertQueryUnScoped(query); if (!result.Success) { result.Message = "An error occurred"; return(result); } //LINK HREF UPDATE var queryUpdateLink = new QueryEntity() { Entity = new { Href = "/roles/" + (int)result.Entity, LinkId = linkId }, Query = "UPDATE Links set Href = @Href where LinkId = @LinkId" }; var resultUpdateLink = con.ExecuteQueryUnScoped(queryUpdateLink); if (!resultUpdateLink.Success) { result.Message = "An error occurred"; result.Success = false; return(result); } scope.Complete(); } result.Message = "The role has been created"; return(result); }
public Result Insert(SectionEntity entity) { var con = new DapperConnectionManager(); var query = new QueryEntity(); var result = new Result(); using (var scope = new TransactionScope()) { var linkEntity = new LinkEntity { Href = " ", Name = entity.Title, Type = "SECTION", Active = false }; query.Entity = linkEntity; query.Query = @"INSERT INTO Links (Name, Type, Href, Active) VALUES(@Name, @Type, @Href, @Active)"; result = con.InsertQueryUnScoped(query); if (!result.Success) { return(result); } var linkId = (int)result.Entity; entity.LinkId = linkId; query.Entity = entity; query.Query = @"INSERT INTO Sections (Name, Title, Sealed, Published, LinkId) VALUES(@Name, @Title, @Sealed, @Published, @LinkId)"; result = con.InsertQueryUnScoped(query); if (!result.Success) { result.Message = "An error occurred"; return(result); } //LINK HREF UPDATE var queryUpdateLink = new QueryEntity() { Entity = new { Href = "/sections/" + (int)result.Entity, LinkId = linkId }, Query = "UPDATE Links set Href = @Href where LinkId = @LinkId" }; var resultUpdateLink = con.ExecuteQueryUnScoped(queryUpdateLink); if (!resultUpdateLink.Success) { result.Message = "An error occurred"; result.Success = false; return(result); } scope.Complete(); } result.Message = "The section has been created"; return(result); }
public Result Get(int id) { var con = new DapperConnectionManager(); var query = new QueryEntity(); query.Query = @"SELECT s.SectionId, s.Name as SectionName, s.Title as SectionTitle, c.Name, c.Title, c.Type, c.ContentItemId, c.Position, c.Text from Sections as s LEFT JOIN ContentItems as c on s.SectionId = c.SectionId where s.SectionId = @SectionId ORDER BY c.Position ASC"; query.Entity = new { SectionId = id }; var result = con.ExecuteQuery(query); if (!result.Success) { result.Message = "Section not found"; return(result); } var r = (IEnumerable <dynamic>)result.Entity; if (!r.Any()) { return(result); } var sectionEntity = new SectionEntity(); sectionEntity.SectionId = r.First().SectionId; sectionEntity.Name = r.First().SectionName; sectionEntity.Title = r.First().SectionTitle; sectionEntity.ContentItems = new List <ContentItemEntity>(); foreach (var item in r) { if (item.ContentItemId == null) { continue; } var contentItem = new ContentItemEntity { Name = item.Name, ContentItemId = item.ContentItemId, Text = item.Text, Position = item.Position, Type = item.Type, SectionId = item.SectionId, Title = item.Title, TextShort = (item.Text as string ?? string.Empty).Truncate() }; sectionEntity.ContentItems.Add(contentItem); } result.Entity = sectionEntity; return(result); }
private Result Insert(QuizEntity entity, DapperConnectionManager con) { var query = new QueryEntity(); query.Entity = entity; query.Query = @"INSERT INTO Quizzes (Name, Type , DomainId) VALUES(@Name, @Type , @DomainId)"; var result = con.InsertQuery(query); return(result); }
public Result GetSecuredUserDetails(int userId) { var result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); var credentials = new CredentialsManager(); query.Query = @"SELECT [UserId] ,[Name] ,[Email] ,[NurseType] ,[ActiveWorking] ,[Area] ,[Country] ,[Suburb] ,[PostalCode] ,[State] ,[defaultQuizId] ,[salary] FROM Users where UserId = @UserId and Active = 1 and ApnaUser = 0"; query.Entity = new { UserId = userId }; result = con.ExecuteGetOneItemQuery <UserEntity>(query); if (!result.Success) { result.Message = "Login error"; return(result); } var user = (UserEntity)result.Entity; if (user == null) { result.Message = "Invalid user"; result.Success = false; result.Entity = null; return(result); } } catch (Exception ex) { Logger.Log(ex); result.Entity = null; result = result ?? new Result(false); result.Message = "An error occurred"; } return(result); }
public Result Login(UserEntity entity) { var result = new Result(); try { var con = new DapperConnectionManager(); var query = new QueryEntity(); var credentials = new CredentialsManager(); entity.Email = entity.Email.Trim().ToLower(); query.Query = @"SELECT * FROM Users where Email = @Email and Active = 1 and ApnaUser = 0"; query.Entity = entity; result = con.ExecuteQuery <UserEntity>(query); if (!result.Success) { result.Message = "Login error"; return(result); } var r = (IEnumerable <UserEntity>)result.Entity; var user = r.FirstOrDefault(); if (user == null) { result.Message = "Invalid password or user"; result.Success = false; result.Entity = null; return(result); } var password = credentials.EncodePassword(entity.Password, user.Hash); if (password == user.Password) { user.Hash = null; user.Password = null; user.Token = credentials.GenerateUserToken(user); result.Entity = user; return(result); } result.Entity = null; result.Message = "User not found"; result.Success = false; } catch (Exception ex) { Logger.Log(ex); result.Entity = null; result = result ?? new Result(false); result.Message = "An error occurred"; } return(result); }
public Result Get(string spName, string extractType, DateTime dateStart, DateTime dateEnd) { var con = new DapperConnectionManager(); var query = new QueryEntity { Query = spName, Entity = new { ExtractType = extractType, DateStart = dateStart.Date.ToString("yyyyMMdd"), DateEnd = dateEnd.Date.ToString("yyyyMMdd") } }; return(con.ExecuteStoredProcedure(query)); }