public bool PostDeleteDbLogs(List <DBLogModel> logsToDelete)
{
try
{
using (var db = HkDbHelper.ResolveDatabase())
{
var sqlDeleteLog = "DELETE FROM umbracoLog WHERE umbracoLog.id in (";
var iCount = 1;
foreach (var logItem in logsToDelete)
{
sqlDeleteLog += logItem.LogId.ToString();
sqlDeleteLog += iCount < logsToDelete.Count ? ", " : string.Empty;
iCount++;
}
sqlDeleteLog += ");";
db.Execute(sqlDeleteLog);
}
return(true);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(false);
}
}
public HKVersionsModel GetPublishedNodes()
{
try
{
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; ";
using (var db = HkDbHelper.ResolveDatabase())
{
ListCurrentPublishedVersions = db.Fetch <CurrentPublishedVersionModel>(sqlVersions);
CurrentPublishedVersionsModel.ListCurrentPublishedVersions = ListCurrentPublishedVersions;
}
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(CurrentPublishedVersionsModel);
}
return(CurrentPublishedVersionsModel);
}
public HKVersionsModel GetPublishedNodes(string search = "", int itemsPerPage = 10, int pageNumber = 1)
{
try {
var request = new HKVersionsModel()
{
Search = search,
ItemsPerPage = itemsPerPage,
CurrentPage = pageNumber,
};
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; ";
using (var db = HkDbHelper.ResolveDatabase())
{
ListCurrentPublishedVersions = db.Fetch <CurrentPublishedVersionModel>(sqlVersions);
}
if (!string.IsNullOrEmpty(request.Search))
{
ListCurrentPublishedVersions = ListCurrentPublishedVersions.Where(tl => tl.NodeId > 0 && tl.NodeId.ToString().Contains(request.Search.ToLower()) ||
!String.IsNullOrEmpty(tl.NodeName) && tl.NodeName.ToLower().Contains(request.Search.ToLower()) ||
!String.IsNullOrEmpty(tl.NodeUser) && tl.NodeUser.ToLower().Contains(request.Search.ToLower())).ToList();
}
request.ListCurrentPublishedVersions = ListCurrentPublishedVersions;
var paged = CreatePagination(request);
//var paged = versionsService.GetVersions(request);
VersionsModel.CurrentPage = int.Parse(paged.PageNumber.ToString());
VersionsModel.ItemsPerPage = int.Parse(paged.PageSize.ToString());
VersionsModel.ListCurrentPublishedVersions = paged.Items.ToList();
VersionsModel.TotalItems = int.Parse(paged.TotalItems.ToString());
VersionsModel.TotalPages = int.Parse(paged.TotalPages.ToString());
return(VersionsModel);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}
public bool PostDeleteSelectedUsers(List <HKUsersModel> selectedUsersToDelete)
{
try
{
using (var db = HkDbHelper.ResolveDatabase())
{
foreach (var user in selectedUsersToDelete)
{
if (user.Selected)
{
// All documents related to selected user(s) will change to the administrator
var sqlDelChangeUmbracoNodeUser = "******" + user.Id + ")";
db.Execute(sqlDelChangeUmbracoNodeUser);
var sqlDelChangeCmsDocumentUser = "******" + user.Id + ")";
db.Execute(sqlDelChangeCmsDocumentUser);
// Delete user from cmsTask
var sqlDelLogCmsTask = "DELETE FROM cmsTask WHERE UserId IN (" + user.Id + ") OR parentUserID IN (" + user.Id + ")";
db.Execute(sqlDelLogCmsTask);
// Delete all selected user(s) references
var sqlDelUserUmbracoUser2App = "DELETE FROM umbracoUser2app WHERE [user] IN (" + user.Id + ")";
db.Execute(sqlDelUserUmbracoUser2App);
// Delete user from umbracoUser2NodeNotify
var sqlDelUserUmbracoUser2NodeNotify = "DELETE FROM umbracoUser2NodeNotify WHERE userId IN (" + user.Id + ")";
db.Execute(sqlDelUserUmbracoUser2NodeNotify);
// Delete user from umbracoUser2NodePermission
var sqlDelUserUmbracoUser2NodePermission = "DELETE FROM umbracoUser2NodePermission WHERE userId IN (" + user.Id + ")";
db.Execute(sqlDelUserUmbracoUser2NodePermission);
// Delete user by Id
UserService.Delete(UserService.GetUserById(int.Parse(user.Id)), true);
}
}
}
return(true);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(false);
}
}
public bool PostDeleteDbLogsBeforeMonths()
{
try
{
using (var db = HkDbHelper.ResolveDatabase())
{
const string sqlDeleteLog = "DELETE FROM umbracoLog WHERE Datestamp < DATEADD(MONTH, -6, GETDATE())";
db.Execute(sqlDeleteLog);
}
return(true);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(false);
}
}
public HistoryVersionsModel GetVersionsByNodeId(int publishedNodeId)
{
try
{
string sqlVersions = "SELECT CAST(versionId AS NVARCHAR(50)) AS VersionGUID, updateDate AS VersionDate, CAST(published AS INT) AS Published, CAST(newest AS INT) AS Newest ";
sqlVersions += "FROM CMSDocument ";
sqlVersions += "WHERE (nodeId = " + publishedNodeId + ") ";
sqlVersions += "ORDER BY VersionDate DESC, published DESC, newest DESC, VersionGUID DESC; ";
using (var db = HkDbHelper.ResolveDatabase())
{
ListHistoryVersions = db.Fetch <HistoryVersionModel>(sqlVersions);
HistoryVersionsModel.ListNodeVersions = ListHistoryVersions;
}
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(HistoryVersionsModel);
}
return(HistoryVersionsModel);
}
public HKDBLogsModel GetDbLogs()
{
ListDbLogs = new List <DBLogModel>();
try
{
var sqlLog = "SELECT umbracoLog.id AS logId, umbracoLog.userId AS UserId, umbracoUser.userName AS UserName, umbracoUser.userLogin AS UserLogin, umbracoLog.NodeId AS NodeId, umbracoNode.text AS NodeName, umbracoLog.DateStamp AS LogDate, umbracoLog.logHeader AS LogHeader, umbracoLog.logComment AS LogComment ";
sqlLog += "FROM umbracoLog INNER JOIN umbracoUser ON umbracoLog.userId = umbracoUser.id LEFT OUTER JOIN umbracoNode ON umbracoLog.NodeId = umbracoNode.id ";
sqlLog += "ORDER BY umbracoLog.DateStamp DESC";
using (var db = HkDbHelper.ResolveDatabase())
{
ListDbLogs = db.Fetch <DBLogModel>(sqlLog);
DbLogsModel.ListDBLogs = ListDbLogs;
}
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(DbLogsModel);
}
return(DbLogsModel);
}
public HKMediaModel GetMediaToDelete(string userLocale)
{
ListMediaWarnings = new List <MediaWarningModel>();
ListMediaToDelete = new List <MediaToDeleteModel>();
var currentUserCultureInfo = CultureInfo.GetCultureInfo(userLocale);
try
{
// Find all media to be deleted and relative warning messages
var filePath = HttpContext.Current.Server.MapPath(GlobalSettings.Path + "/../media/");
// Check if the files are stored in the /media folder root with a unique ID prefixed to the filename
if (UmbracoConfig.For.UmbracoSettings().Content.UploadAllowDirectories)
{
var strSqlGetMedia = "SELECT SUBSTRING(cmsPropertyData.dataNvarchar, 8, CHARINDEX('/', cmsPropertyData.dataNvarchar, 8) - 8) AS pId ";
strSqlGetMedia += "FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyData.propertytypeid = cmsPropertyType.id ";
strSqlGetMedia += "WHERE (dataNvarchar LIKE '%media/%') AND (cmsPropertyType.Alias = 'umbracoFile') ";
strSqlGetMedia += "UNION ";
strSqlGetMedia += "SELECT SUBSTRING(";
strSqlGetMedia += " SUBSTRING(";
strSqlGetMedia += " cmsPropertyData.dataNtext, ";
strSqlGetMedia += " CHARINDEX('/media/', cmsPropertyData.dataNtext) + 7, ";
strSqlGetMedia += " LEN(RTRIM(CAST(cmsPropertyData.dataNtext as NVARCHAR(4000))))";
strSqlGetMedia += " ), ";
strSqlGetMedia += " 0, ";
strSqlGetMedia += " CHARINDEX('/', ";
strSqlGetMedia += " SUBSTRING(";
strSqlGetMedia += " cmsPropertyData.dataNtext, ";
strSqlGetMedia += " CHARINDEX('/media/', cmsPropertyData.dataNtext) + 7, ";
strSqlGetMedia += " LEN(RTRIM(CAST(cmsPropertyData.dataNtext as NVARCHAR(4000))))";
strSqlGetMedia += " )";
strSqlGetMedia += " )";
strSqlGetMedia += ")";
strSqlGetMedia += "FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyData.propertytypeid = cmsPropertyType.id ";
strSqlGetMedia += "WHERE (dataNtext LIKE '%media/%') AND (cmsPropertyType.Alias = 'umbracoFile') ";
strSqlGetMedia += "ORDER BY pId";
// Show orphan directories
using (var db = HkDbHelper.ResolveDatabase())
{
var allMediaPId = db.Fetch <MediaPIdModel>(strSqlGetMedia);
// Create an array with the list of media directories
var dir = new DirectoryInfo(filePath);
var subDirs = dir.GetDirectories();
var mediaToSkip = new List <string>();
// Sort Directories by name
Array.Sort(subDirs, delegate(DirectoryInfo d1, DirectoryInfo d2)
{
int n1, n2;
if (int.TryParse(d1.Name, out n1) && int.TryParse(d2.Name, out n2))
{
return(n1 - n2);
}
return(string.CompareOrdinal(d1.Name, d2.Name));
});
foreach (var subDir in subDirs)
{
// Do check only if the folder have a number as a name (STANDARD FOLDER)
MediaWarningModel mediaWarningModel;
int iDirectoryName, pId;
if (int.TryParse(subDir.Name, out iDirectoryName))
{
var mediaAlreadyAddedToDeleteList = false;
MediaToDeleteModel mediaToDeleteModel;
if (allMediaPId.Count > 0)
{
foreach (var media in allMediaPId)
{
if (int.TryParse(media.PId, out pId))
{
if (!mediaToSkip.Contains(pId.ToString()))
{
if (iDirectoryName == pId)
{
mediaToSkip.Add(subDir.Name);
break;
}
if (iDirectoryName < pId)
{
// Check if the folder is used by data type that not store image informations (like Image Cropper)
int mediaCount = 0;
mediaCount += GetCountFromCmsContentXml(db, subDir.Name);
mediaCount += GetCountFromCmsPropertyData(db, subDir.Name);
// The Media is deletable if it isn't used
if ((mediaCount == 0) && (!mediaAlreadyAddedToDeleteList))
{
// MEDIA FOUND IN FILE SYSTEM BUT NO CORRISPONDATION INTO DB OR BY DATATYPE UPLOAD
// ### DELETEBLE ###
mediaToDeleteModel = new MediaToDeleteModel
{
Entry = subDir.Name,
Message = Services.TextService.Localize("FALM/MediaManager.Cleanup.Contains", currentUserCultureInfo) + " " + subDir.GetFileSystemInfos().Length + " " + Services.TextService.Localize("FALM/MediaManager.Cleanup.Items", currentUserCultureInfo)
};
ListMediaToDelete.Add(mediaToDeleteModel);
mediaToSkip.Add(subDir.Name);
mediaAlreadyAddedToDeleteList = true;
}
else
{
if (!mediaAlreadyAddedToDeleteList)
{
mediaWarningModel = new MediaWarningModel
{
Entry = subDir.Name,
Message = "###" + Services.TextService.Localize("FALM/MediaManager.Cleanup.FolderFoundInFileSystemAndUploadedTroughDatatype", currentUserCultureInfo)
};
ListMediaWarnings.Add(mediaWarningModel);
mediaToSkip.Add(subDir.Name);
break;
}
}
}
}
}
else
{
// MEDIA IGNORED - NO MATCH STANDARD FORMAT INTO DB
mediaWarningModel = new MediaWarningModel
{
Entry = pId.ToString(),
Message = Services.TextService.Localize("FALM/MediaManager.Cleanup.DBNotMatchFormat", currentUserCultureInfo) + " '/media/<propertyid>/'"
};
ListMediaWarnings.Add(mediaWarningModel);
}
}
}
if (!mediaToSkip.Contains(subDir.Name))
{
// Check if the folder is used by data type that not store image informations (like Image Cropper)
var mediaCount = 0;
mediaCount += GetCountFromCmsContentXml(db, subDir.Name);
mediaCount += GetCountFromCmsPropertyData(db, subDir.Name);
// If the media is not used...it is deletable
if (mediaCount == 0)
{
// MEDIA FOUND IN FILE SYSTEM BUT NO CORRISPONDATION INTO DB OR BY DATATYPE UPLOAD
// ### DELETEBLE ###
mediaToDeleteModel = new MediaToDeleteModel
{
Entry = subDir.Name,
Message = Services.TextService.Localize("FALM/MediaManager.Cleanup.Contains", currentUserCultureInfo) + " " + subDir.GetFileSystemInfos().Length + " " + Services.TextService.Localize("FALM/MediaManager.Cleanup.Items", currentUserCultureInfo)
};
ListMediaToDelete.Add(mediaToDeleteModel);
}
else
{
// MEDIA FOUND IN FILE SYSTEM AND UPLOADED TROUGH DATATYPE
mediaWarningModel = new MediaWarningModel
{
Entry = subDir.Name,
Message = Services.TextService.Localize("FALM/MediaManager.Cleanup.FoundInFileSystemAndUploadedTroughDatatype", currentUserCultureInfo)
};
ListMediaWarnings.Add(mediaWarningModel);
}
}
}
else
{
// MEDIA IGNORED - NON STANDARD FOLDER
mediaWarningModel = new MediaWarningModel
{
Entry = subDir.Name,
Message = Services.TextService.Localize("FALM/MediaManager.Cleanup.FolderNameNotNumber", currentUserCultureInfo)
};
ListMediaWarnings.Add(mediaWarningModel);
}
}
//}
//else
//{
// return null;
//}
}
}
MediaModel.ListMediaToDelete = ListMediaToDelete;
MediaModel.ListMediaWarnings = ListMediaWarnings;
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(MediaModel);
}
return(MediaModel);
}
public List <CleanupResultModel> PostDeleteVersionsByNodeId(int publishedNodeId, int versionsToKeep)
{
try
{
HttpContext.Current.Server.ScriptTimeout = 10000;
var cleanupSummary = new List <CleanupResultModel>();
using (var db = HkDbHelper.ResolveDatabase())
{
db.CommandTimeout = 0;
var _dbContext = ApplicationContext.Current.DatabaseContext;
var _dbHelper = new DatabaseSchemaHelper(_dbContext.Database, LoggerResolver.Current.Logger, _dbContext.SqlSyntax);
CleanupResultModel cleanupResult = new CleanupResultModel();
// Begin Transaction
db.BeginTransaction();
// Delete versions from cmsPreviewXml
if (_dbHelper.TableExist("cmsPreviewXml"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsPreviewXml",
Result = _dbContext.Database.Execute("DELETE FROM cmsPreviewXml WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE nodeId = @0 AND versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT TOP(1000000000000) DD.nodeId, CV.versionId, DD.published, DD.newest, COUNT (CV.versionDate) RowNum FROM(cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId), (cmsContentVersion CV2 JOIN cmsDocument DD2 ON CV2.versionId = DD2.versionId) WHERE DD.nodeId = D.nodeId AND CV.versionDate <= CV2.versionDate GROUP BY DD.nodeId, CV.versionId, DD.published, DD.newest, CV.versionDate ORDER BY DD.nodeId, CV.versionDate DESC) AS tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete versions from cmsContentVersion
if (_dbHelper.TableExist("cmsContentVersion"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsContentVersion",
Result = _dbContext.Database.Execute("DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE nodeId = @0 AND versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT TOP(1000000000000) DD.nodeId, CV.versionId, DD.published, DD.newest, COUNT (CV.versionDate) RowNum FROM (cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId), (cmsContentVersion CV2 JOIN cmsDocument DD2 ON CV2.versionId = DD2.versionId) WHERE DD.nodeId = D.nodeId AND CV.versionDate <= CV2.versionDate GROUP BY DD.nodeId, CV.versionId, DD.published, DD.newest, CV.versionDate ORDER BY DD.nodeId, CV.versionDate DESC) AS tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete all properties data of each versions to delete from cmsPropertyData
if (_dbHelper.TableExist("cmsPropertyData"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsPropertyData",
Result = _dbContext.Database.Execute("DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE nodeId = @0 AND versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT TOP(1000000000000) DD.nodeId, CV.versionId, DD.published, DD.newest, COUNT (CV.versionDate) RowNum FROM (cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId), (cmsContentVersion CV2 JOIN cmsDocument DD2 ON CV2.versionId = DD2.versionId) WHERE DD.nodeId = D.nodeId AND CV.versionDate <= CV2.versionDate GROUP BY DD.nodeId, CV.versionId, DD.published, DD.newest, CV.versionDate ORDER BY DD.nodeId, CV.versionDate DESC) AS tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete versions from cmsDocument
if (_dbHelper.TableExist("cmsDocument"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsDocument",
Result = _dbContext.Database.Execute("DELETE FROM cmsDocument WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE nodeId = @0 AND versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT TOP(1000000000000) DD.nodeId, CV.versionId, DD.published, DD.newest, COUNT (CV.versionDate) RowNum FROM (cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId), (cmsContentVersion CV2 JOIN cmsDocument DD2 ON CV2.versionId = DD2.versionId) WHERE DD.nodeId = D.nodeId AND CV.versionDate <= CV2.versionDate GROUP BY DD.nodeId, CV.versionId, DD.published, DD.newest, CV.versionDate ORDER BY DD.nodeId, CV.versionDate DESC) AS tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// End Transaction
db.CompleteTransaction();
}
return(cleanupSummary);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}
public List <CleanupResultModel> PostDeleteVersionsByCount(int versionsToKeep)
{
try
{
var cleanupSummary = new List <CleanupResultModel>();
using (var db = HkDbHelper.ResolveDatabase())
{
// Begin Transaction
db.BeginTransaction();
// Delete versions from cmsPreviewXml
const string sqlDeletePreviewXml = @"DELETE FROM cmsPreviewXml WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId) AS tmp WHERE tmp.RowNum <= @versionsToKeep OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);";
var dbComm = db.CreateCommand(db.Connection, sqlDeletePreviewXml, new { versionsToKeep });
dbComm.CommandTimeout = 100000;
int iResultCount = dbComm.ExecuteNonQuery();
var cleanupResult = new CleanupResultModel
{
Type = "cmsPreviewXml",
Result = iResultCount
};
cleanupSummary.Add(cleanupResult);
dbComm.Dispose();
// Delete versions from cmsContentVersion
const string sqlDeleteContentVersions = @"DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId) AS tmp WHERE tmp.RowNum <= @versionsToKeep OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);";
dbComm = db.CreateCommand(db.Connection, sqlDeleteContentVersions, new { versionsToKeep });
dbComm.CommandTimeout = 100000;
iResultCount = dbComm.ExecuteNonQuery();
cleanupResult = new CleanupResultModel
{
Type = "cmsContentVersion",
Result = iResultCount
};
cleanupSummary.Add(cleanupResult);
dbComm.Dispose();
// Delete all properties data of each versions to delete from cmsPropertyData
const string sqlDeletePropertyData = @"DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId) AS tmp WHERE tmp.RowNum <= @versionsToKeep OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);";
dbComm = db.CreateCommand(db.Connection, sqlDeletePropertyData, new { versionsToKeep });
dbComm.CommandTimeout = 100000;
iResultCount = dbComm.ExecuteNonQuery();
cleanupResult = new CleanupResultModel
{
Type = "cmsPropertyData",
Result = iResultCount
};
cleanupSummary.Add(cleanupResult);
dbComm.Dispose();
// Delete versions from cmsDocument
const string sqlDeleteDocument = @"DELETE FROM cmsDocument WHERE VersionId IN (SELECT FALMtmp1.VersionId FROM (SELECT nodeId, published, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId, newest FROM cmsDocument WHERE versionID NOT IN (SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId) AS tmp WHERE tmp.RowNum <= @versionsToKeep OR tmp.published = 1 OR tmp.newest = 1))) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0);";
dbComm = db.CreateCommand(db.Connection, sqlDeleteDocument, new { versionsToKeep });
dbComm.CommandTimeout = 100000;
iResultCount = dbComm.ExecuteNonQuery();
cleanupResult = new CleanupResultModel
{
Type = "cmsDocument",
Result = iResultCount
};
cleanupSummary.Add(cleanupResult);
dbComm.Dispose();
// End Transaction
db.CompleteTransaction();
}
return(cleanupSummary);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}
public bool PostDeleteSelectedUsers(List <HKUsersModel> selectedUsersToDelete)
{
try
{
using (var db = HkDbHelper.ResolveDatabase())
{
var _dbContext = ApplicationContext.Current.DatabaseContext;
var _dbHelper = new DatabaseSchemaHelper(_dbContext.Database, LoggerResolver.Current.Logger, _dbContext.SqlSyntax);
foreach (var user in selectedUsersToDelete)
{
if (user.Selected)
{
// All documents related to selected user(s) will change to the administrator
var sqlDelChangeUmbracoNodeUser = "******" + user.Id + ");";
db.Execute(sqlDelChangeUmbracoNodeUser);
var sqlDelChangeCmsDocumentUser = "******" + user.Id + ");";
db.Execute(sqlDelChangeCmsDocumentUser);
// Delete all selected user(s) from cmsTask (for Umbraco all verions)
if (_dbHelper.TableExist("cmsTask"))
{
//var sqlDelLogCmsTask = "DELETE FROM cmsTask WHERE UserId IN (" + user.Id + ") OR parentUserID IN (" + user.Id + ");";
//db.Execute(sqlDelLogCmsTask);
_dbContext.Database.Execute("DELETE FROM cmsTask WHERE UserId IN (@0) OR parentUserID IN (@0);", user.Id);
}
// Delete all selected user(s) from umbracoUser2app (for Umbraco < v7.7)
if (_dbHelper.TableExist("umbracoUser2app"))
{
//var sqlDelUserUmbracoUser2App = "DELETE FROM umbracoUser2app WHERE [user] IN (" + user.Id + ");";
//db.Execute(sqlDelUserUmbracoUser2App);
_dbContext.Database.Execute("DELETE FROM umbracoUser2app WHERE [user] IN (@0);", user.Id);
}
// Delete all selected user(s) from umbracoUser2NodeNotify (for Umbraco all verions)
if (_dbHelper.TableExist("umbracoUser2NodeNotify"))
{
//var sqlDelUserUmbracoUser2NodeNotify = "DELETE FROM umbracoUser2NodeNotify WHERE userId IN (" + user.Id + ");";
//db.Execute(sqlDelUserUmbracoUser2NodeNotify);
_dbContext.Database.Execute("DELETE FROM umbracoUser2NodeNotify WHERE [userId] IN (@0);", user.Id);
}
// Delete all selected user(s) from umbracoUser2NodePermission (for Umbraco < v7.7)
if (_dbHelper.TableExist("umbracoUser2NodePermission"))
{
//var sqlDelUserUmbracoUser2NodePermission = "DELETE FROM umbracoUser2NodePermission WHERE userId IN (" + user.Id + ");";
//db.Execute(sqlDelUserUmbracoUser2NodePermission);
_dbContext.Database.Execute("DELETE FROM umbracoUser2NodePermission WHERE [userId] IN (@0);", user.Id);
}
// Delete all selected user(s) from umbracoUser2UserGroup (for Umbraco v7.7+)
if (_dbHelper.TableExist("umbracoUser2UserGroup"))
{
//var sqlDelUserumbracoUser2UserGroup = "DELETE FROM umbracoUser2UserGroup WHERE userId IN (" + user.Id + ");";
//db.Execute(sqlDelUserumbracoUser2UserGroup);
_dbContext.Database.Execute("DELETE FROM umbracoUser2UserGroup WHERE [userId] IN (@0);", user.Id);
}
// Delete all selected user(s) by Id
UserService.Delete(UserService.GetUserById(int.Parse(user.Id)), true);
}
}
}
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(false);
}
return(true);
}
public List <CleanupResultModel> PostDeleteVersionsByNodeId(int publishedNodeId, int versionsToKeep)
{
try
{
HttpContext.Current.Server.ScriptTimeout = 10000;
var cleanupSummary = new List <CleanupResultModel>();
using (var db = HkDbHelper.ResolveDatabase())
{
db.CommandTimeout = 0;
var _dbContext = ApplicationContext.Current.DatabaseContext;
var _dbHelper = new DatabaseSchemaHelper(_dbContext.Database, LoggerResolver.Current.Logger, _dbContext.SqlSyntax);
CleanupResultModel cleanupResult = new CleanupResultModel();
// Begin Transaction
db.BeginTransaction();
// Delete versions from cmsPreviewXml
if (_dbHelper.TableExist("cmsPreviewXml"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsPreviewXml",
Result = _dbContext.Database.Execute("DELETE b FROM (SELECT FALMtmp1.VersionId,FALMtmp1.nodeId FROM(SELECT published, versionId, newest, nodeId FROM cmsDocument WHERE versionId NOT IN(SELECT VersionId FROM(SELECT TOP(1000000000000) DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, ROW_NUMBER() OVER(PARTITION BY nodeId ORDER BY nodeId, versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId GROUP BY DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, CV.versionDate)tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1)) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0) a INNER JOIN cmsPreviewXml b ON a.versionId = b.versionId AND a.nodeId=@0;", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete versions from cmsContentVersion
if (_dbHelper.TableExist("cmsContentVersion"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsContentVersion",
Result = _dbContext.Database.Execute("DELETE b FROM (SELECT FALMtmp1.VersionId,FALMtmp1.nodeId FROM(SELECT published, versionId, newest, nodeId FROM cmsDocument WHERE versionId NOT IN(SELECT VersionId FROM(SELECT TOP(1000000000000) DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, ROW_NUMBER() OVER(PARTITION BY nodeId ORDER BY nodeId, versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId GROUP BY DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, CV.versionDate)tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1)) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0) a INNER JOIN cmsContentVersion b ON a.versionId = b.versionId AND a.nodeId=@0;", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete all properties data of each versions to delete from cmsPropertyData
if (_dbHelper.TableExist("cmsPropertyData"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsPropertyData",
Result = _dbContext.Database.Execute("DELETE b FROM (SELECT FALMtmp1.VersionId,FALMtmp1.nodeId FROM(SELECT published, versionId, newest, nodeId FROM cmsDocument WHERE versionId NOT IN(SELECT VersionId FROM(SELECT TOP(1000000000000) DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, ROW_NUMBER() OVER(PARTITION BY nodeId ORDER BY nodeId, versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId GROUP BY DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, CV.versionDate)tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1)) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0) a INNER JOIN cmsPropertyData b ON a.versionId = b.versionId AND a.nodeId=@0;", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// Delete versions from cmsDocument
if (_dbHelper.TableExist("cmsDocument"))
{
cleanupResult = new CleanupResultModel
{
Type = "cmsDocument",
Result = _dbContext.Database.Execute("DELETE b FROM (SELECT FALMtmp1.VersionId,FALMtmp1.nodeId FROM(SELECT published, versionId, newest, nodeId FROM cmsDocument WHERE versionId NOT IN(SELECT VersionId FROM(SELECT TOP(1000000000000) DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, ROW_NUMBER() OVER(PARTITION BY nodeId ORDER BY nodeId, versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId GROUP BY DD.nodeId, CV.versionId, CV.versionDate, DD.published, DD.newest, CV.versionDate)tmp WHERE tmp.RowNum <= @1 OR tmp.published = 1 OR tmp.newest = 1)) AS FALMtmp1 WHERE FALMtmp1.published = 0 AND FALMtmp1.newest = 0) a INNER JOIN cmsDocument b ON a.versionId = b.versionId AND a.nodeId=@0;", publishedNodeId, versionsToKeep)
};
cleanupSummary.Add(cleanupResult);
}
// End Transaction
db.CompleteTransaction();
}
return(cleanupSummary);
}
catch (Exception ex)
{
LogHelper.Error <Exception>(ex.Message, ex);
return(null);
}
}
