/// <summary>
/// Gets a list of the tabs that the given module is on.
/// </summary>
/// <param name="desktopModuleId">The desktop module id.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>A list of the tabs on which the given module is</returns>
public override IDataReader GetTabsForModule(int desktopModuleId, int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT tm.ModuleID, tm.TabModuleID, tm.TabID, t.TabName, m.ModuleTitle, t.TabPath ");
sql.AppendFormat("FROM {0}TabModules tm ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}Modules m ON m.ModuleId = tm.ModuleID ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}Tabs t ON tm.TabID = t.TabID ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}ModuleDefinitions md ON md.ModuleDefID = m.ModuleDefID ", this.DnnPrefix);
sql.Append("WHERE m.portalId = @portalId ");
sql.Append(" AND md.desktopModuleId = @desktopModuleId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId),
Utility.CreateIntegerParam("@desktopModuleId", desktopModuleId)));
}
/// <summary>
/// Gets a list of the Text/HTML modules in the given portal with content matching the search string.
/// </summary>
/// <param name="searchValue">The search string.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>The Text/HTML modules in the given portal with content matching the search string</returns>
public override IDataReader GetMatchingHtmlTextModules(string searchValue, int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT ht.ModuleID, tm.TabID, ht.DesktopHtml, ht.DesktopSummary, m.ModuleTitle, t.TabName ");
sql.AppendFormat("FROM {0}HtmlText ht ", this.DnnPrefix);
sql.AppendFormat(" JOIN {0}TabModules tm on (tm.ModuleID = ht.ModuleID)", this.DnnPrefix);
sql.AppendFormat(" JOIN {0}Modules m on (m.ModuleID = tm.ModuleID)", this.DnnPrefix);
sql.AppendFormat(" JOIN {0}Tabs t on (t.TabID = tm.TabID)", this.DnnPrefix);
sql.Append("WHERE ht.DesktopHtml collate SQL_Latin1_General_CP1_CS_AS LIKE @searchValue ");
sql.Append(" AND m.PortalId = @portalId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId),
Utility.CreateVarcharParam("@searchValue", "%" + searchValue + "%")));
}
/// <summary>
/// Gets the pages in the given portal that are only visible to administrators.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The pages in the given portal that are only visible to administrators</returns>
public override IDataReader GetAdminOnlyPages(int portalId)
{
StringBuilder sql = new StringBuilder(256);
sql.Append("DECLARE @adminRoleId int ");
sql.AppendFormat("SET @adminRoleId = (SELECT r.RoleID FROM {0}Roles r WHERE r.RoleName = 'Administrators' AND r.PortalID = @portalId) ", this.DnnPrefix);
sql.Append("DECLARE @viewTabPermissionId int ");
sql.AppendFormat("SET @viewTabPermissionId = (SELECT p.PermissionID FROM {0}Permission p WHERE p.PermissionKey = 'VIEW' AND p.PermissionCode = 'SYSTEM_TAB') ", this.DnnPrefix);
sql.Append("DECLARE @adminTabId int ");
sql.AppendFormat("SET @adminTabId = (SELECT t.TabID FROM {0}Tabs t WHERE t.TabName = 'Admin' AND t.PortalId = @portalId) ", this.DnnPrefix);
sql.AppendFormat("SELECT t.TabId, t.TabName FROM {0}vw_TabPermissions vtp ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}Tabs t ON t.TabID = vtp.TabID ", this.DnnPrefix);
sql.Append("WHERE vtp.PortalID = @portalId ");
sql.Append(" AND vtp.PermissionId = @viewTabPermissionId ");
sql.Append(" AND vtp.RoleId = @adminRoleId ");
sql.Append(" AND NOT EXISTS (");
sql.AppendFormat("SELECT t2.TabId, t2.TabName FROM {0}vw_TabPermissions vtp2 ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}Tabs t2 ON t2.TabID = vtp2.TabID ", this.DnnPrefix);
sql.Append("WHERE vtp2.PortalID = @portalId ");
sql.Append(" AND vtp2.PermissionId = @viewTabPermissionId ");
sql.Append(" AND vtp2.RoleId = @adminRoleId ");
sql.Append(" AND t2.ParentID = @adminTabId ");
sql.Append(" AND t.TabID = t2.TabID ");
sql.Append("OR vtp2.PortalID = @portalId ");
sql.Append(" AND vtp2.PermissionId = @viewTabPermissionId ");
sql.Append(" AND vtp2.RoleId = @adminRoleId ");
sql.Append(" AND t2.TabID = @adminTabId ");
sql.Append(" AND t.TabID = t2.TabID ");
sql.Append("OR vtp2.PortalID = @portalId ");
sql.Append(" AND vtp2.PermissionId = @viewTabPermissionId ");
sql.Append(" AND vtp2.RoleId <> @adminRoleId ");
sql.Append(" AND t.TabID = t2.TabID");
sql.Append(")");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets the number of deleted module and pages in the recycle bin fir the given portal.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The number of deleted module and pages in the recycle bin for the given portal</returns>
public override int CountRecycleBin(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("DECLARE @DeletedModules int ");
sql.Append("DECLARE @DeletedPages int ");
sql.Append("DECLARE @RecycleBinCount int ");
sql.AppendFormat("SET @DeletedModules = (SELECT COUNT(*) FROM {0}Modules m WHERE m.IsDeleted = '1' AND m.PortalID = @portalId) ", this.DnnPrefix);
sql.AppendFormat("SET @DeletedPages = (SELECT COUNT(*) FROM {0}Tabs t WHERE t.IsDeleted = '1' AND t.PortalID = @portalId) ", this.DnnPrefix);
sql.Append("SET @RecycleBinCount = (@DeletedModules + @DeletedPages) ");
sql.Append("SELECT @RecycleBinCount");
return((int)SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets a list of the number of users registered each day over the given date span.
/// </summary>
/// <param name="startDate">The begin date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>
/// A list of the number of users registered each day over the given date span
/// </returns>
public override IDataReader GetUserRegistrationsInDateSpan(DateTime startDate, DateTime endDate, int portalId)
{
StringBuilder sql = new StringBuilder(512);
sql.Append("SELECT COUNT(*) AS 'Users Registered', DATEADD(dd, DATEDIFF(dd, 0, up.CreatedDate), 0) AS 'Date' ");
sql.AppendFormat("FROM {0}Users u ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}UserPortals up ON u.UserID = up.UserId ", this.DnnPrefix);
sql.Append("WHERE up.CreatedDate < @endDate + 1 ");
sql.Append(" AND up.CreatedDate >= @startDate ");
sql.Append(" AND up.PortalID = @portalId ");
sql.Append("GROUP BY DATEADD(dd, DATEDIFF(dd, 0, up.CreatedDate), 0) ");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateDateTimeParam("@startDate", startDate),
Utility.CreateDateTimeParam("@endDate", endDate),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets information about the HTML/Text modules in the given portal that don't have a search summary defined.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>Information about the HTML/Text modules in the given portal that don't have a search summary defined</returns>
public override IDataReader GetHtmlTextModulesWithoutSummary(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT tm.ModuleID, m.ModuleTitle, t.TabID, t.TabName, t.TabPath ");
sql.AppendFormat("FROM {0}TabModules tm ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}HtmlText ht ON tm.ModuleID = ht.ModuleID ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}Modules m ON ht.ModuleID = m.ModuleID ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}Tabs t ON tm.TabID = t.TabID ", this.DnnPrefix);
// using LIKE because we can't use = against DesktopSummary since it's an NText field
sql.Append("WHERE ht.DesktopSummary LIKE ''");
sql.Append(" AND m.PortalID = @portalId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets the number of pages in the given portal. Counts only pages that aren't deleted or admin pages.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The number of pages in the given portal</returns>
public override int CountPages(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("DECLARE @AdminTabID int ");
sql.AppendFormat("SET @AdminTabID = (SELECT t.TabID FROM {0}Tabs t WHERE t.TabName = 'Admin' AND t.ParentID IS NULL AND t.Level = 0 AND t.PortalID = @portalId) ", this.DnnPrefix);
sql.Append("SELECT COUNT(*) ");
sql.AppendFormat("FROM {0}Tabs t ", this.DnnPrefix);
sql.Append("WHERE t.PortalID = @portalId");
sql.Append(" AND (t.ParentId <> @AdminTabID OR t.ParentId IS NULL)");
sql.Append(" AND t.TabName <> 'Admin'");
sql.Append(" AND t.IsDeleted <> 1");
return((int)SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets the number of user registrations in a date span.
/// </summary>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>The number of user registrations in a date span</returns>
public override int GetNumberOfUserRegistrationsInDateSpan(DateTime startDate, DateTime endDate, int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT COUNT(*) ");
sql.AppendFormat("FROM {0}Users u ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}UserPortals up ON u.UserID = up.UserId ", this.DnnPrefix);
sql.Append("WHERE up.CreatedDate < @endDate + 1 ");
sql.Append(" AND up.CreatedDate >= @startDate ");
sql.Append(" AND up.PortalID = @portalId");
return(Convert.ToInt32(
SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateDateTimeParam("@startDate", startDate),
Utility.CreateDateTimeParam("@endDate", endDate),
Utility.CreateIntegerParam("@portalId", portalId)),
CultureInfo.InvariantCulture));
}
/// <summary>
/// Gets the number of unique user logins for the given date span.
/// </summary>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>The number of unique user logins for the given date span</returns>
public override int GetNumberOfUserLoginsInDateSpan(DateTime startDate, DateTime endDate, int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT COUNT(*) ");
sql.AppendFormat("FROM {0}aspnet_Membership m ", this.DatabaseOwner);
sql.AppendFormat(" INNER JOIN {0}aspnet_Users au ON au.UserId = m.UserId ", this.DatabaseOwner);
sql.AppendFormat(" INNER JOIN {0}Users u on u.UserName = au.UserName ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}UserPortals up on up.UserId = u.UserId ", this.DnnPrefix);
sql.Append("WHERE m.LastLoginDate < @endDate + 1 ");
sql.Append(" AND m.LastLoginDate >= @startDate ");
sql.Append(" AND up.PortalID = @portalId");
return(Convert.ToInt32(
SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateDateTimeParam("@startDate", startDate),
Utility.CreateDateTimeParam("@endDate", endDate),
Utility.CreateIntegerParam("@portalId", portalId)),
CultureInfo.InvariantCulture));
}
