public Result Get(int id)
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Query = @"SELECT s.SectorId, s.Name, s.LinkId, s.Title, s.Published,
v.SectorViewId, v.Type, v.Framework, v.Active
FROM Sectors as s
JOIN SectorViews as v
on s.SectorId = v.SectorId
where s.SectorId = @SectorId
ORDER BY s.Name ASC";
query.Entity = new { SectorId = id };
var result = con.ExecuteQuery(query);
if (!result.Success)
{
result.Message = "Sector not found";
return(result);
}
var r = (IEnumerable <dynamic>)result.Entity;
if (!r.Any())
{
return(result);
}
var sectorEntity = new SectorEntity();
sectorEntity.SectorId = r.First().SectorId;
sectorEntity.Name = r.First().Name;
sectorEntity.Title = r.First().Title;
List <SectorViewEntity> list = new List <SectorViewEntity>();
foreach (var item in r)
{
var view = new SectorViewEntity()
{
Type = item.Type,
Framework = item.Framework,
SectorViewId = item.SectorViewId,
Active = item.Active
};
list.Add(view);
}
sectorEntity.SectorEn = list.FirstOrDefault(x => x.Type == "EN");
sectorEntity.SectorRn = list.FirstOrDefault(x => x.Type == "RN");
result.Entity = sectorEntity;
return(result);
}
private List <SectorEntity> FormatSectors(IEnumerable <dynamic> sectors)
{
var sectorsList = new List <SectorEntity>();
foreach (var item in sectors)
{
SectorEntity entity = sectorsList.FirstOrDefault(x => x.SectorId == item.SectorId);
if (entity == null)
{
entity = new SectorEntity();
entity.SectorId = item.SectorId;
entity.Title = item.Title;
entity.Name = item.Name;
sectorsList.Add(entity);
}
if (item.SectorViewId == null)
{
continue;
}
var view = new SectorViewEntity
{
Intro = item.Intro,
SectorViewId = item.SectorViewId,
Video = item.Video,
Type = item.Type,
CareerOpportunities = item.CareerOpportunities,
CareerPathways = item.CareerPathways,
ContactText = item.ContactText,
EducationOpportunities = item.EducationOpportunities,
Framework = item.Framework,
MoreStories = item.MoreStories,
WorkEnvironments = item.WorkEnvironments,
SectorId = item.SectorId,
OnlineResources = item.OnlineResources,
Active = item.Active
};
if (item.Type == "EN")
{
entity.SectorEn = view;
}
if (item.Type == "RN")
{
entity.SectorRn = view;
}
}
return(sectorsList);
}
public Result Update(SectorViewEntity entity)
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Entity = entity;
query.Query = @"UPDATE SectorViews set
Intro=@Intro,
Video=@Video,
MoreStories=@MoreStories,
CareerPathways=@CareerPathways,
WorkEnvironments=@WorkEnvironments,
CareerOpportunities=@CareerOpportunities,
EducationOpportunities=@EducationOpportunities,
ContactText=@ContactText,
OnlineResources=@OnlineResources
WHERE SectorViewId = @SectorViewId";
var result = con.ExecuteQuery(query);
result.Message = result.Success? "The view has been updated" : "An error occurred";
return(result);
}
public Result GetAll(int id)
{
var con = new DapperConnectionManager();
var query = new QueryEntity();
query.Query = @"SELECT s.SectorId, s.Name, s.LinkId, s.Title, s.Published,
v.SectorViewId, v.Type, v.Framework, v.Intro,
,v.Video
,v.MoreStories
,v.CareerPathways
,v.WorkEnvironments
,v.CareerOpportunities
,v.EducationOpportunities
,v.ContactText
FROM Sectors
JOIN SectorViews as v
on s.SectorId = v.SectorId
where s.SectorId = @SectorId
ORDER BY c.Position ASC";
query.Entity = new { SectorId = id };
var result = con.ExecuteQuery(query);
if (!result.Success)
{
result.Message = "Sector not found";
return(result);
}
var r = (IEnumerable <dynamic>)result.Entity;
if (!r.Any())
{
return(result);
}
var sectorEntity = new SectorEntity();
sectorEntity.SectorId = r.First().SectorId;
sectorEntity.Name = r.First().Name;
sectorEntity.Title = r.First().Title;
List <SectorViewEntity> list = new List <SectorViewEntity>();
foreach (var item in r)
{
var view = new SectorViewEntity()
{
Type = item.Type,
CareerOpportunities = item.CareerOpportunities,
CareerPathways = item.CareerPathways,
ContactText = item.ContactText,
EducationOpportunities = item.EducationOppotunities,
Framework = item.Framework,
Intro = item.Intro,
MoreStories = item.MoreStories,
SectorId = item.SectorId,
SectorViewId = item.SectorViewId,
Video = item.Video,
WorkEnvironments = item.WorkEnviroments
};
list.Add(view);
}
sectorEntity.SectorEn = list.FirstOrDefault(x => x.Type == "EN");
sectorEntity.SectorRn = list.FirstOrDefault(x => x.Type == "RN");
result.Entity = sectorEntity;
return(result);
}
public Result Insert(SectorEntity 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 = LinksTypes.SECTOR.ToString(),
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)
{
result.Message = "An error occurred";
return(result);
}
var linkId = (int)result.Entity;
entity.LinkId = linkId;
query.Entity = entity;
query.Query = @"INSERT INTO Sectors (Name, Title, Published, LinkId) VALUES(@Name, @Title, @Published, @LinkId)";
result = con.InsertQueryUnScoped(query);
if (!result.Success)
{
result.Message = "An error occurred";
return(result);
}
var sectorId = (int)result.Entity;
var sectorViewRn = new SectorViewEntity
{
Type = "RN",
Framework = "rn",
SectorId = sectorId
};
query.Entity = sectorViewRn;
query.Query = @"INSERT INTO SectorViews (Type, Framework, SectorId) VALUES(@Type, @Framework, @SectorId)";
result = con.InsertQueryUnScoped(query);
if (!result.Success)
{
result.Message = "An error occurred";
return(result);
}
var sectorViewEn = new SectorViewEntity
{
Type = "EN",
Framework = "en",
SectorId = sectorId
};
query.Entity = sectorViewEn;
query.Query = @"INSERT INTO SectorViews (Type, Framework, SectorId) VALUES(@Type, @Framework, @SectorId)";
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/" + sectorId, 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.Entity = sectorId;
result.Message = "The sector has been created";
}
return(result);
}
/// <summary>
/// Not needed
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public Result Insert(SectorViewEntity entity)
{
throw new NotImplementedException();
}
