/// <summary>
/// Whether the database supports SQL Server 2005 functionality.
/// </summary>
/// <returns><c>true</c> if the database supports SQL Server 2005 functionality; otherwise, <c>false</c></returns>
public override bool SupportsSql2005Functionality()
{
string version = SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, "SELECT SERVERPROPERTY('ProductVersion')").ToString();
List <int> versionNumbers = Utility.ParseIntegerList(version.Split('.'), CultureInfo.InvariantCulture);
return(versionNumbers.Count > 0 && versionNumbers[0] > SqlServer2000MajorVersionNumber);
}
/// <summary>
/// Gets a count of the pages that don't have either a description or keywords defined.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>A count of the pages that don't have a description or keywords defined</returns>
public override IDataReader CountPagesWithoutDescriptionOrKeywords(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 ");
sql.Append(" AND (t.Description = '' or t.Keywords = '') ");
sql.Append(Environment.NewLine);
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(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
protected void ApplicationStatusDropDownList_SelectedIndexChanged(object sender, EventArgs e)
{
var applicationStatusDropDownList = (DropDownList)sender;
var applicationId = (int)Utility.FindParentControl <GridDataItem>(applicationStatusDropDownList).GetDataKeyValue("ApplicationId");
var application = JobApplication.Load(applicationId);
int statusId;
if (!Utility.HasValue(applicationStatusDropDownList.SelectedValue))
{
application.StatusId = null;
}
else if (int.TryParse(applicationStatusDropDownList.SelectedValue, NumberStyles.Integer, CultureInfo.InvariantCulture, out statusId))
{
application.StatusId = statusId;
}
else
{
Exceptions.LogException(new InvalidOperationException("During update of application status, StatusId could not be parsed."));
}
application.Save(this.UserId);
////this.BindApplicationStatusDropDownList(applicationStatusDropDownList, application.StatusId);
}
protected void UserStatusDropDownList_SelectedIndexChanged(object sender, EventArgs e)
{
var userStatusDropDownList = (DropDownList)sender;
var applicationRow = Utility.FindParentControl <GridDataItem>(userStatusDropDownList);
var userId = (int)applicationRow.GetDataKeyValue("UserId");
int statusId;
int?statusIdValue = null;
if (int.TryParse(userStatusDropDownList.SelectedValue, NumberStyles.Integer, CultureInfo.InvariantCulture, out statusId))
{
statusIdValue = statusId;
}
else if (Utility.HasValue(userStatusDropDownList.SelectedValue))
{
Exceptions.LogException(new InvalidOperationException("During update of application status, StatusId could not be parsed."));
}
UserStatusInfo.UpdateUserStatus(this.PortalSettings, userId, statusIdValue);
// collapse all other application grids, so that the user's status isn't out of sync
foreach (GridDataItem item in this.JobsGrid.MasterTableView.Items.Cast <GridDataItem>()
.Where(item => item.Expanded && item != applicationRow.OwnerTableView.ParentItem))
{
item.Expanded = false;
}
}
/// <summary>
/// Gets the name of the database.
/// </summary>
/// <returns>The name of the database</returns>
private string GetDatabaseName()
{
if (!Utility.HasValue(this.databaseName))
{
this.databaseName = SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, "SELECT DB_NAME()").ToString();
}
return(this.databaseName);
}
/// <summary>
/// Updates the given Text/HTML module with the given HTML and summary.
/// </summary>
/// <param name="moduleId">The module id.</param>
/// <param name="desktopHtml">The desktop HTML.</param>
/// <param name="desktopSummary">The desktop summary.</param>
/// <param name="userId">The user id.</param>
public override void ReplaceTextHtml(int moduleId, string desktopHtml, string desktopSummary, int userId)
{
SqlHelper.ExecuteNonQuery(
this.ConnectionString,
this.DatabaseOwner + this.ObjectQualifier + "UpdateHtmlText",
Utility.CreateIntegerParam("@ModuleId", moduleId),
Utility.CreateTextParam("@DesktopHtml", desktopHtml),
Utility.CreateTextParam("@DesktopSummary", desktopSummary),
Utility.CreateIntegerParam("@UserID", userId));
}
/// <summary>
/// Gets the number of roles in the given portal.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The number of roles in the given portal</returns>
public override int CountRoles(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT COUNT(*) ");
sql.AppendFormat("FROM {0}Roles r", this.DnnPrefix);
sql.Append(" WHERE r.PortalID = @portalId");
return((int)SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets the tab module settings for the given tab module id.
/// </summary>
/// <param name="tabModuleId">The tab module id.</param>
/// <returns>The tab module settings for the given tab module id</returns>
public override IDataReader GetTabModuleSettings(int tabModuleId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT tms.SettingName, tms.SettingValue ");
sql.AppendFormat("FROM {0}TabModuleSettings tms ", this.DnnPrefix);
sql.Append("WHERE tms.TabModuleID = @tabModuleId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@tabModuleId", tabModuleId)));
}
/// <summary>
/// Gets a list of the pages that are set not to be visible in the menu.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The pages that are set not to be visible in the menu</returns>
public override IDataReader GetPagesNotVisible(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT t.TabID, t.TabName, t.TabPath ");
sql.AppendFormat("FROM {0}Tabs t ", this.DnnPrefix);
sql.Append("WHERE t.IsVisible = 'False' ");
sql.Append(" AND t.TabName <> 'Search Results' ");
sql.Append(" AND t.PortalID = @portalId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets a list of the Text/HTML modules with content matching the search string.
/// </summary>
/// <param name="searchValue">The search string.</param>
/// <returns>The Text/HTML modules with content matching the search string</returns>
public override IDataReader GetMatchingHtmlTextModules(string searchValue)
{
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");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateVarcharParam("@searchValue", "%" + searchValue + "%")));
}
/// <summary>
/// Gets a summary of the event log by log entry type.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>
/// A summary of the event log by log entry type
/// </returns>
public override IDataReader GetEventLogSummaryByType(int?portalId)
{
StringBuilder sql = new StringBuilder(256);
sql.Append("SELECT COUNT(*) AS Count, elt.LogTypeCssClass ");
sql.AppendFormat("FROM {0}EventLog el ", this.DnnPrefix);
sql.AppendFormat(" INNER JOIN {0}EventLogTypes elt ON (el.LogTypeKey = elt.LogTypeKey) ", this.DnnPrefix);
sql.Append("WHERE el.LogPortalId = @portalId OR @portalId IS NULL ");
sql.Append("GROUP BY elt.LogTypeCssClass ");
sql.Append("ORDER BY LogTypeCssClass ");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets a list of Engage: Publish articles in the given portal that match the search string
/// </summary>
/// <param name="searchValue">The search string.</param>
/// <param name="portalId">The portal id.</param>
/// <returns>A list of Engage: Publish articles in the given portal that match the search string</returns>
public override IDataReader GetPublishLinks(string searchValue, int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT va.name, va.ItemId, va.articletext, va.displaytabid, t.TabName ");
sql.AppendFormat("FROM {0}publish_vwarticles va ", this.DnnPrefix);
sql.AppendFormat(" JOIN {0}Tabs t on t.TabID = va.displaytabid ", this.DnnPrefix);
sql.Append("WHERE va.articletext collate SQL_Latin1_General_CP1_CS_AS LIKE @searchValue ");
sql.Append(" and va.IsCurrentVersion = 1 and va.PortalId = @portalId");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId),
Utility.CreateVarcharParam("@searchValue", "%" + searchValue + "%")));
}
/// <summary>
/// Gets the modules in the current portal that are only visible to administrators.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>The modules in the current portal that are only visible to administrators</returns>
public override IDataReader GetAdminOnlyModules(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 @viewModulePermissionId int ");
sql.AppendFormat("SET @viewModulePermissionId = (SELECT p.PermissionID FROM {0}Permission p WHERE p.PermissionKey = 'VIEW' AND p.PermissionCode = 'SYSTEM_MODULE_DEFINITION') ", 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 vmp.ModuleId, m.ModuleTitle, tm.TabID, t.TabName, t.TabPath FROM {0}vw_ModulePermissions vmp ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}Modules m ON m.ModuleId = vmp.ModuleId ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}TabModules tm ON tm.ModuleID = vmp.ModuleId ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}Tabs t ON t.TabID = tm.TabID ", this.DnnPrefix);
sql.Append("WHERE m.PortalID = @portalId ");
sql.Append(" AND vmp.PermissionId = @viewModulePermissionId ");
sql.Append(" AND vmp.RoleId = @adminRoleId ");
sql.Append(" AND NOT EXISTS (");
sql.AppendFormat("SELECT * FROM {0}vw_ModulePermissions vmp2 ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}Modules m2 ON m2.ModuleId = vmp2.ModuleId ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}TabModules tm2 ON tm2.ModuleId = vmp2.ModuleId ", this.DnnPrefix);
sql.AppendFormat("INNER JOIN {0}TabPermission tp2 ON tp2.TabID = tm2.TabID ", this.DnnPrefix);
sql.Append("WHERE m2.PortalID = @portalId ");
sql.Append(" AND vmp2.PermissionId = @viewModulePermissionId ");
sql.Append(" AND vmp2.RoleId <> @adminRoleId ");
sql.Append(" AND vmp.ModuleId = vmp2.ModuleId ");
sql.Append("OR m2.PortalID = @portalId ");
sql.Append(" AND m2.InheritViewPermissions = 1 ");
sql.Append(" AND tp2.PermissionId = @viewTabPermissionId ");
sql.Append(" AND tp2.RoleId = @adminRoleId ");
sql.Append(" AND vmp.ModuleId = vmp2.ModuleId ");
sql.Append(")");
return(SqlHelper.ExecuteReader(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateIntegerParam("@portalId", portalId)));
}
/// <summary>
/// Gets a list of the pages in the given portal that have no modules on them.
/// </summary>
/// <param name="portalId">The portal id.</param>
/// <returns>A list of the pages in the given portal that have no modules on them</returns>
public override IDataReader GetEmptyPages(int portalId)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT t.TabID, t.TabName, t.TabPath ");
sql.AppendFormat("FROM {0}Tabs t ", this.DnnPrefix);
sql.Append("WHERE NOT EXISTS (SELECT t.TabID, t.TabName, t.TabPath ");
sql.AppendFormat(" FROM {0}TabModules tm ", this.DnnPrefix);
sql.Append(" WHERE t.TabID = tm.TabID) ");
sql.Append(" AND TabName <> 'Admin' ");
sql.Append(" AND PortalID = @portalId");
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 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 the number of failed scheduler tasks in the given date span.
/// </summary>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <returns>The number of failed scheduler tasks in the given date span</returns>
public override int GetFailedSchedulesInDateSpan(DateTime startDate, DateTime endDate)
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT COUNT(*) ");
sql.AppendFormat("FROM {0}ScheduleHistory sh ", this.DnnPrefix);
sql.Append("WHERE sh.EndDate < @endDate");
sql.Append(" AND sh.EndDate > @startDate");
sql.Append(" AND sh.Succeeded = 0");
return(Convert.ToInt32(
SqlHelper.ExecuteScalar(
this.ConnectionString,
CommandType.Text,
sql.ToString(),
Utility.CreateDateTimeParam("@startDate", startDate),
Utility.CreateDateTimeParam("@endDate", endDate)),
CultureInfo.InvariantCulture));
}
/// <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 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 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>Creates a <c>div</c> tag for the given <paramref name="tag" />.
/// Then sets the tag to be the container for a pager item (which, when clicked, rotates to its associated slide).</summary>
/// <param name="tag">The tag whose content is being represented.</param>
/// <param name="slide">The object from which to get the property.</param>
/// <param name="resourceFile">The resource file from which to get localized resources.</param>
/// <returns>The created pager item</returns>
private Control CreatePagerItem(Tag tag, ITemplateable slide, string resourceFile)
{
Panel pagerItemWrapper = null;
try
{
pagerItemWrapper = this.CreateRotatorContainer(tag, slide, resourceFile);
pagerItemWrapper.CssClass = Utility.AddCssClass(pagerItemWrapper.CssClass, "pager-item-" + slide.GetValue("INDEX"));
return(pagerItemWrapper);
}
catch
{
if (pagerItemWrapper != null)
{
pagerItemWrapper.Dispose();
}
throw;
}
}
/// <summary>Creates a <c>div</c> tag for the given <paramref name="tag" />.
/// Then sets the tag to pause rotation when clicked.</summary>
/// <param name="tag">The tag whose content is being represented.</param>
/// <param name="slide">The object from which to get the property.</param>
/// <param name="resourceFile">The resource file from which to get localized resources.</param>
/// <returns>The created pause button</returns>
private Control CreatePauseButton(Tag tag, ITemplateable slide, string resourceFile)
{
Panel pauseButton = null;
try
{
pauseButton = this.CreateRotatorContainer(tag, slide, resourceFile);
pauseButton.CssClass = Utility.AddCssClass(pauseButton.CssClass, "rotator-pause");
return(pauseButton);
}
catch
{
if (pauseButton != null)
{
pauseButton.Dispose();
}
throw;
}
}
/// <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>Creates a <see cref="Label" /> whose content is the total number of <see cref="Slide" />s for this rotator.</summary>
/// <param name="tag">The tag whose content is being represented.</param>
/// <param name="slide">The object from which to get the property.</param>
/// <param name="resourceFile">The resource file from which to get localized resources.</param>
/// <returns>The <see cref="Label" /> instance that was created</returns>
private static Control CreateTotalCountControl(Tag tag, ITemplateable slide, string resourceFile)
{
Label totalCountLabel = null;
try
{
totalCountLabel = new Label();
totalCountLabel.CssClass = TemplateEngine.GetAttributeValue(tag, slide, null, resourceFile, "CssClass", "class");
totalCountLabel.CssClass = Utility.AddCssClass(totalCountLabel.CssClass, "total-slide-count");
return(totalCountLabel);
}
catch
{
if (totalCountLabel != null)
{
totalCountLabel.Dispose();
}
throw;
}
}
/// <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>Creates a <see cref="Label" /> whose content is the (1-based) index of the currently displayed <see cref="Slide" /></summary>
/// <param name="tag">The tag whose content is being represented.</param>
/// <param name="slide">The object from which to get the property.</param>
/// <param name="resourceFile">The resource file from which to get localized resources.</param>
/// <returns>The <see cref="Label" /> instance that was created</returns>
private static Control CreateCurrentIndexControl(Tag tag, ITemplateable slide, string resourceFile)
{
Label currentSlideIndexWrapper = null;
try
{
currentSlideIndexWrapper = new Label();
currentSlideIndexWrapper.CssClass = TemplateEngine.GetAttributeValue(tag, slide, null, resourceFile, "CssClass", "class");
currentSlideIndexWrapper.CssClass = Utility.AddCssClass(currentSlideIndexWrapper.CssClass, "current-slide-index");
currentSlideIndexWrapper.Text = 1.ToString(CultureInfo.CurrentCulture);
return(currentSlideIndexWrapper);
}
catch
{
if (currentSlideIndexWrapper != null)
{
currentSlideIndexWrapper.Dispose();
}
throw;
}
}
/// <summary>Creates a <c>div</c> tag for the given <paramref name="tag" />.
/// Then sets the tag to be the container for an auto-generated pager.</summary>
/// <param name="tag">The tag whose content is being represented.</param>
/// <param name="slide">The object from which to get the property.</param>
/// <param name="resourceFile">The resource file from which to get localized resources.</param>
/// <returns>The created pager container</returns>
private Control CreatePager(Tag tag, ITemplateable slide, string resourceFile)
{
Panel pagerContainer = null;
try
{
pagerContainer = this.CreateRotatorContainer(tag, slide, resourceFile);
pagerContainer.CssClass = Utility.AddCssClass(pagerContainer.CssClass, "rotator-pager");
this.CycleOptions.PagerEvent = TemplateEngine.GetAttributeValue(tag, slide, (ITemplateable)null, resourceFile, "Event") ?? "click";
return(pagerContainer);
}
catch
{
if (pagerContainer != null)
{
pagerContainer.Dispose();
}
throw;
}
}
/// <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));
}
