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