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); } }