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 Get(int id)
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Query = @"SELECT * FROM Questions
where QuestionId = @QuestionId";
query.Entity = new { QuestionId = id };
var result = con.ExecuteQuery <QuestionEntity>(query);
if (!result.Success)
{
result.Message = "Question not found";
return(result);
}
var r = (IEnumerable <QuestionEntity>)result.Entity;
var question = r.FirstOrDefault();
if (result.Entity == null)
{
result.Success = false;
result.Message = "Question not found";
return(result);
}
query.Query = @"SELECT * FROM Answers WHERE QuestionId = @QuestionId";
query.Entity = new { QuestionId = id };
result = con.ExecuteQuery <AnswerEntity>(query);
if (!result.Success)
{
result.Message = "An error occurred";
return(result);
}
question.Answers = ((IEnumerable <AnswerEntity>)result.Entity).ToList();
query.Query = @"SELECT * FROM SectorsQuestions WHERE QuestionId = @QuestionId";
query.Entity = new { QuestionId = id };
result = con.ExecuteQuery <SectorsQuestionsEntity>(query);
if (!result.Success)
{
result.Message = "An error occurred";
return(result);
}
question.SectorsQuestions = ((IEnumerable <SectorsQuestionsEntity>)result.Entity).ToList();
result.Entity = question;
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 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 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 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 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 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 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 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 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 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 GetJobApplicationByListingId(int listingId)
{
Result result = new Result();
try
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Entity = new { JobListingId = listingId };
query.Query = @"
SELECT * FROM JobApplications WHERE JobListingId = @JobListingId
";
result = con.ExecuteQuery <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 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 Get(int id)
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { AspectId = id },
Query = @"SELECT * FROM Aspects WHERE AspectId = @AspectId"
};
var result = con.ExecuteQuery <AspectEntity>(query);
if (!result.Success)
{
result.Message = "Aspect not found";
return(result);
}
var r = (IEnumerable <AspectEntity>)result.Entity;
var aspect = r.FirstOrDefault();
if (result.Entity == null)
{
result.Success = false;
return(result);
}
var queryActions = new QueryEntity()
{
Query = @"SELECT A.ActionId, A.Title, C.LevelAction, C.Position, A.Type FROM AspectsActions as C
JOIN Actions as A ON C.ActionId = A.ActionId
where C.AspectId = @AspectId",
Entity = new { AspectId = id }
};
var resultActions = con.ExecuteQuery <ActionEntity>(queryActions);
if (!resultActions.Success)
{
result.Message = "An error occurred";
return(result);
}
var actions = (IEnumerable <ActionEntity>)resultActions.Entity;
aspect.ActionsList = actions.ToList();
result.Entity = aspect;
return(result);
}
public Result Get(int id)
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Query = @"SELECT * FROM Domains
where DomainId = @DomainId";
query.Entity = new { DomainId = id };
var result = con.ExecuteQuery <DomainEntity>(query);
if (!result.Success)
{
result.Message = "Domain not found";
return(result);
}
var r = (IEnumerable <DomainEntity>)result.Entity;
var domain = r.FirstOrDefault();
if (result.Entity == null)
{
result.Success = false;
}
var queryActions = new QueryEntity()
{
Query = @"SELECT A.ActionId, A.Title, C.Position FROM DomainsActions as C
JOIN Actions as A ON C.ActionId = A.ActionId
where C.DomainId = @DomainId ORDER BY C.Position ASC",
Entity = new { DomainId = id }
};
var resultActions = con.ExecuteQuery <ActionEntity>(queryActions);
if (!resultActions.Success)
{
result.Message = "An error occurred";
return(result);
}
var actions = (IEnumerable <ActionEntity>)resultActions.Entity;
domain.ActionsList = actions.ToList();
result.Entity = domain;
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);
}
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 Delete(int id)
{
var con = new DapperConnectionManager();
var queryCheck = new QueryEntity()
{
Query = @"SELECT SectionId FROM Sections where SectionId = @SectionId and Sealed = 0",
Entity = new { SectionId = id }
};
var resCheck = con.ExecuteQuery <int>(queryCheck);
if (!resCheck.Success)
{
resCheck.Message = "An error has ocurred";
return(resCheck);
}
var entity = (IEnumerable <int>)resCheck.Entity;
if (!entity.Any())
{
resCheck.Success = false;
resCheck.Message = "That section can not be deleted";
return(resCheck);
}
var query = new QueryEntity()
{
Query = @"DECLARE @linkId int;
SELECT @linkId = Sections.LinkId
FROM Sections WHERE SectionId = @SectionId;
Update Sections Set Active = 0 WHERE SectionId = @SectionId;
Update Links set Active = 0 Where LinkId = @linkId;",
Entity = new { SectionId = id }
};
var result = con.ExecuteQuery(query);
result.Message = result.Success ? "The section has been deleted" : "An error occurred";
return(result);
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM Actions ORDER BY Title ASC"
};
return(con.ExecuteQuery <ActionEntity>(query));
}
private void SaveImageData(string name)
{
var con = new DapperConnectionManager();
var query = new QueryEntity()
{
Query = @"INSERT into Images (Name, Date) VALUES(@Name, @Date)",
Entity = new { Name = name, Date = DateTime.Now }
};
con.ExecuteQuery(query);
}
public Result GetByType(string type)
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { Type = type },
Query = @"SELECT * FROM Menus WHERE Type = @Type ORDER BY Position ASC"
};
return(con.ExecuteQuery <MenuEntity>(query));
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM Categories ORDER BY Name ASC"
};
return(con.ExecuteQuery <CategoryEntity>(query));
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM Administrators"
};
return(con.ExecuteQuery <AdministratorEntity>(query));
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM PostCards"
};
return(con.ExecuteQuery <PostCardEntity>(query));
}
public Result GetLike(string name)
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { Name = string.Concat("%", name, "%") },
Query = @"SELECT TOP 10 * FROM Actions Where Title like @Name ORDER BY Title ASC"
};
return(con.ExecuteQuery <ActionEntity>(query));
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM EndorsedLogos"
};
return(con.ExecuteQuery <EndorsedLogoEntity>(query));
}
public Result Get()
{
var con = new DapperConnectionManager();
var query = new QueryEntity
{
Entity = new { },
Query = @"SELECT * FROM Sectors where Active = 1 ORDER BY Name ASC "
};
return(con.ExecuteQuery <SectorEntity>(query));
}