/// <summary>
/// Gets the basic page content for the site
/// </summary>
/// <param name="page">The pagination page</param>
/// <param name="itemsPerPage">The pagination items per page</param>
/// <param name="criteria">The filter criteria</param>
/// <param name="orderBy">The order by clause</param>
/// <returns>A list of content items</returns>
public Page <ContentItem> GetContent(long page, long itemsPerPage, ContentCriteria criteria = null, string orderBy = "N.id")
{
string sql = @"SELECT N.Id, N.ParentId, N.Level, CT.icon, N.Trashed, CT.alias, D.Text as Name, N.createDate, D.updateDate, Creator.Id AS CreatorId, Creator.userName as CreatorName, Updater.Id as UpdaterId, Updater.userName as UpdaterName
FROM cmsContent C
INNER JOIN umbracoNode N ON N.Id = C.nodeId
INNER JOIN cmsContentType CT ON C.contentType = CT.nodeId
INNER JOIN cmsDocument D ON D.nodeId = C.nodeId
INNER JOIN umbracoUser AS Creator ON Creator.Id = N.nodeUser
INNER JOIN umbracoUser AS Updater ON Updater.Id = D.documentUser
WHERE D.Newest = 1 ";
Sql query = new Sql(sql);
if (criteria != null)
{
if (!String.IsNullOrEmpty(criteria.Alias))
{
query = query.Append(" AND CT.alias = @0", criteria.Alias);
}
if (!String.IsNullOrEmpty(criteria.Name))
{
query = query.Append(" AND D.text LIKE @0", "%" + criteria.Name + "%");
}
if (criteria.Id.HasValue)
{
query = query.Append(" AND N.id = @0", criteria.Id.Value);
}
if (criteria.Level.HasValue)
{
query = query.Append(" AND N.Level = @0", criteria.Level.Value);
}
if (criteria.Trashed.HasValue)
{
query = query.Append(" AND N.Trashed = @0", criteria.Trashed.Value);
}
if (criteria.CreatorId.HasValue)
{
query = query.Append(" AND Creator.Id = @0", criteria.CreatorId.Value);
}
if (criteria.UpdaterId.HasValue)
{
query = query.Append(" AND Updater.Id = @0", criteria.UpdaterId.Value);
}
}
if (!String.IsNullOrEmpty(orderBy))
{
query.OrderBy(orderBy);
}
var paged = db.Page <ContentItem>(page, itemsPerPage, query);
return(paged);
}
/// <summary>
/// Searches the Audit Log table
/// </summary>
/// <param name="request">The search request criteria</param>
/// <returns>A page of log results</returns>
public Page <LogEntry> SearchAuditLog(LogSearchRequest request)
{
if (!IsValidOrderByParameter(request.SortColumn))
{
throw new ArgumentOutOfRangeException("The order by value '" + request.SortColumn + "' is not a valid sort parameter.");
}
if (!IsValidOrderByDirectionParameter(request.SortOrder))
{
throw new ArgumentOutOfRangeException("The order by value '" + request.SortOrder + "' is not a valid sort order.");
}
string sql = @"SELECT L.Id, N.[text] as Name, U.userName as UserName, L.Datestamp as DateStamp, L.logHeader as LogType, L.logComment as Comment, L.nodeId
FROM umbracoLog L
LEFT JOIN umbracoUser U ON L.userId = U.id
LEFT JOIN umbracoNode N ON N.id = L.NodeId
WHERE 1 = 1";
Sql query = new Sql(sql);
if (!String.IsNullOrEmpty(request.LogType))
{
query = query.Append(" AND L.LogHeader = @0", request.LogType);
}
if (!String.IsNullOrEmpty(request.UserName))
{
query = query.Append(" AND U.userName = @0", request.UserName);
}
if (request.DateFrom.HasValue)
{
query = query.Append(" AND L.Datestamp >= @0", request.DateFrom.Value);
}
if (request.DateTo.HasValue)
{
query = query.Append(" AND L.Datestamp <= @0", request.DateTo.Value.AddDays(1));
}
if (!String.IsNullOrEmpty(request.SearchTerm))
{
query = query.Append(" AND (L.logComment LIKE @0 OR N.[text] LIKE @0)", "%" + request.SearchTerm + "%");
}
if (request.NodeId.HasValue)
{
query = query.Append(" AND L.NodeId = @0", request.NodeId.Value);
}
if (request.SortColumn.InvariantEquals("id"))
{
request.SortColumn = "L.id";
}
query = query.Append(" ORDER BY " + request.SortColumn + " " + request.SortOrder);
return(db.Page <LogEntry>(request.PageNumber, request.ItemsPerPage, query));
}
/// <summary>
/// Gets a list of company inventories for the umbraco dashboard
/// </summary>
/// <param name="request">Page data</param>
public Page <CompanyInventory> GetInventoryList(ItemsSearchRequest request)
{
const string sql = @"SELECT sci.CompanyName,
sci.Availability, sci.DateOfIncorporation, sci.ReservedDate, sci.SoldDate, sci.CompanyStruckOffDate,
CONVERT(datetime, sci.DateOfIncorporation, 103) as Incorporation,
CONVERT(datetime, sci.ReservedDate, 103) as Reserved,
CONVERT(datetime, sci.SoldDate, 103) as Sold,
CONVERT(datetime, sci.CompanyStruckOffDate, 103) as StruckOffDate
FROM CompanyInventory sci";
Sql query = new Sql(sql);
if (request.SortColumn.InvariantEquals("id"))
{
request.SortColumn = "sci.Id";
}
var sortColumn = request.SortColumn;
switch (request.SortColumn)
{
case "sci.DateOfIncorporation":
sortColumn = "Incorporation";
break;
case "sci.ReservedDate":
sortColumn = "Reserved";
break;
case "sci.SoldDate":
sortColumn = "Sold";
break;
case "sci.CompanyStruckOffDate":
sortColumn = "StruckOffDate";
break;
default:
sortColumn = request.SortColumn;
break;
}
query = query.Append(" ORDER BY " + sortColumn + " " + request.SortOrder);
return(db.Page <CompanyInventory>(request.PageNumber, request.ItemsPerPage, query));
}
public Page <Order> GetPaged(int page, int pageSize, string orderBy)
{
var sql = new Sql().Select("*")
.From <Order>(ctx.SqlSyntax)
.Where <Order>(o => o.IsDeleted == false, ctx.SqlSyntax)
.OrderBy(orderBy ?? "Id");
return(db.Page <Order>(page, pageSize, sql));
}
//public List<Category> GetPaged(int page, int pageSize)
//{
// var sql = new Sql().Select("*")
// .From<Category>(ctx.SqlSyntax)
// .OrderBy<Category>(c => c.Id, ctx.SqlSyntax);
// return db.Fetch<Category>(page, pageSize, sql);
//}
//public Page<Category> GetPaged(int page, int pageSize)
//{
// var sql = new Sql().Select("*")
// .From<Category>(ctx.SqlSyntax)
// .OrderBy<Category>(c => c.Id, ctx.SqlSyntax);
// return db.Page<Category>(page, pageSize, sql);
//}
public Page <Category> GetPaged(int page, int pageSize,
CategoryFilter filter, string sortColumn = "Id", string sortOrder = "asc")
{
var sql = new Sql().Select("*")
.From <Category>(ctx.SqlSyntax);
if (!string.IsNullOrEmpty(filter.Name))
{
sql = sql.Where <Category>(
c => c.Name.ToLower().Contains(filter.Name.ToLower()),
ctx.SqlSyntax);
}
RepositoryHelpers.ApplyOrdering(sql, sortColumn, sortOrder);
return(db.Page <Category>(page, pageSize, sql));
}
/// <summary>
/// Get Versions
/// </summary>
/// <returns>Paged Versions Model</returns>
public Page <CurrentPublishedVersionModel> GetVersions(HKVersionsModel request)
{
var sqlVersions = "SELECT CurDoc.nodeId, CurDoc.text AS NodeName, umbracoUser.userName AS NodeUser, CurDoc.updateDate AS PublishedDate, HistDoc.VersionsCount AS VersionsCount ";
sqlVersions += "FROM cmsDocument AS CurDoc ";
sqlVersions += "INNER JOIN umbracoUser ON CurDoc.documentUser = umbracoUser.id ";
sqlVersions += "LEFT OUTER JOIN (";
sqlVersions += "SELECT COUNT(1) as VersionsCount, nodeId ";
sqlVersions += "FROM cmsDocument ";
sqlVersions += "WHERE (published = 0) ";
sqlVersions += "GROUP BY nodeid ";
sqlVersions += ") AS HistDoc ON CurDoc.nodeId = HistDoc.nodeId ";
sqlVersions += "WHERE (CurDoc.published = 1 AND curdoc.nodeid = curdoc.nodeid) ";
sqlVersions += "ORDER BY CurDoc.nodeId; ";
return(db.Page <CurrentPublishedVersionModel>(request.CurrentPage, request.ItemsPerPage, sqlVersions));
}
/// <summary>
/// Get DB Logs
/// </summary>
/// <returns>IEnumerable of TraceLogFileModel</returns>
public Page <DBLogModel> GetDBLog(HKDBLogsModel request)
{
var sqlLog = "SELECT umbracoLog.id AS Id, umbracoLog.userId AS UserId, umbracoUser.userName AS UserName, umbracoUser.userLogin AS UserLogin, umbracoLog.NodeId AS NodeId, umbracoNode.text AS NodeName, umbracoLog.DateStamp AS Date, umbracoLog.logHeader AS Header, umbracoLog.logComment AS Comment ";
sqlLog += "FROM umbracoLog INNER JOIN umbracoUser ON umbracoLog.userId = umbracoUser.id LEFT OUTER JOIN umbracoNode ON umbracoLog.NodeId = umbracoNode.id ";
if (!String.IsNullOrEmpty(request.Search))
{
sqlLog += "WHERE (umbracoLog.logHeader LIKE '%" + request.Search.ToLower() + "%') OR ";
sqlLog += "(umbracoUser.userName LIKE '%" + request.Search.ToLower() + "%') OR ";
sqlLog += "(umbracoNode.text LIKE '%" + request.Search.ToLower() + "%') OR ";
sqlLog += "(umbracoLog.logComment LIKE '%" + request.Search.ToLower() + "%') ";
}
sqlLog += "ORDER BY umbracoLog.DateStamp DESC";
return(db.Page <DBLogModel>(request.CurrentPage, request.ItemsPerPage, sqlLog));
}