public ProgramStatusReport CurrentStatus() { var report = new ProgramStatusReport(); var parameters = new List<SqlParameter>(); StringBuilder query = new StringBuilder("SELECT SUM(pp.[NumPoints]) AS [PointsEarned]," + " SUM(CASE WHEN pp.[IsBookList] = 1 THEN 1 ELSE 0 END) AS [ChallengesCompleted]" + " FROM [PatronPoints] pp" + " INNER JOIN [Patron] p on pp.[PID] = p.[PID] AND p.[TenID] = @TenId"); parameters.Add(new SqlParameter("TenId", this.TenantId)); if(this.StartDate != null && this.StartDate > DateTime.MinValue) { query.Append(" WHERE CAST(pp.[AwardDate] AS DATE) >= CAST(@startDate AS DATE)"); report.Since = this.StartDate.ToShortDateString(); parameters.Add(new SqlParameter("startDate", report.Since)); } var result = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, query.ToString(), parameters.ToArray()); if(result.Read()) { report.PointsEarned = result["PointsEarned"] as int? ?? 0; //report.BadgesAwarded = result["BadgesAwarded"] as int? ?? 0; report.ChallengesCompleted = result["ChallengesCompleted"] as int? ?? 0; } else { throw new Exception("No data returned."); } query = new StringBuilder("SELECT COUNT(ba.[PBID]) AS [BadgesAwarded] " + " FROM [PatronBadges] ba" + " INNER JOIN [Patron] p on ba.[PID] = p.[PID] AND p.[TenID] = @TenId"); parameters.Clear(); parameters.Add(new SqlParameter("TenId", this.TenantId)); if(this.StartDate != null && this.StartDate > DateTime.MinValue) { query.Append(" WHERE CAST(ba.[DateEarned] AS DATE) >= CAST(@startDate AS DATE)"); parameters.Add(new SqlParameter("startDate", report.Since)); } result = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, query.ToString(), parameters.ToArray()); if(result.Read()) { report.BadgesAwarded = result["BadgesAwarded"] as int? ?? 0; } else { throw new Exception("No badge data returned."); } return report; }
public ProgramStatusReport CurrentStatus() { var report = new ProgramStatusReport(); var parameters = new List <SqlParameter>(); StringBuilder query = new StringBuilder("SELECT SUM([NumPoints]) AS [PointsEarned]," + " SUM(CASE WHEN[BadgeAwardedFlag] = 1 THEN 1 ELSE 0 END) AS [BadgesAwarded]," + " SUM(CASE WHEN[IsBookList] = 1 THEN 1 ELSE 0 END) AS [ChallengesCompleted]" + " FROM [PatronPoints]"); // re issue #35: WHERE [PID] IN (SELECT [PID] FROM [Patron] WHERE [TenId] = 1) if (this.StartDate != null && this.StartDate > DateTime.MinValue) { query.Append(" WHERE CAST([AwardDate] AS DATE) >= CAST(@startDate AS DATE)"); report.Since = this.StartDate.ToShortDateString(); parameters.Add(new SqlParameter("startDate", report.Since)); } var result = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, query.ToString(), parameters.ToArray()); if (result.Read()) { report.PointsEarned = result["PointsEarned"] as int? ?? 0; report.BadgesAwarded = result["BadgesAwarded"] as int? ?? 0; report.ChallengesCompleted = result["ChallengesCompleted"] as int? ?? 0; } else { throw new Exception("No data returned."); } return(report); }
public ProgramStatusReport CurrentStatus() { var report = new ProgramStatusReport(); var parameters = new List <SqlParameter>(); StringBuilder query = new StringBuilder("SELECT SUM(pp.[NumPoints]) AS [PointsEarned]," + " SUM(CASE WHEN pp.[IsBookList] = 1 THEN 1 ELSE 0 END) AS [ChallengesCompleted]" + " FROM [PatronPoints] pp" + " INNER JOIN [Patron] p on pp.[PID] = p.[PID] AND p.[TenID] = @TenId"); parameters.Add(new SqlParameter("TenId", this.TenantId)); if (this.StartDate != null && this.StartDate > DateTime.MinValue) { query.Append(" WHERE CAST(pp.[AwardDate] AS DATE) >= CAST(@startDate AS DATE)"); report.Since = this.StartDate.ToShortDateString(); parameters.Add(new SqlParameter("startDate", report.Since)); } var result = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, query.ToString(), parameters.ToArray()); if (result.Read()) { report.PointsEarned = result["PointsEarned"] as int? ?? 0; //report.BadgesAwarded = result["BadgesAwarded"] as int? ?? 0; report.ChallengesCompleted = result["ChallengesCompleted"] as int? ?? 0; } else { throw new Exception("No data returned."); } query = new StringBuilder("SELECT COUNT(ba.[PBID]) AS [BadgesAwarded] " + " FROM [PatronBadges] ba" + " INNER JOIN [Patron] p on ba.[PID] = p.[PID] AND p.[TenID] = @TenId"); parameters.Clear(); parameters.Add(new SqlParameter("TenId", this.TenantId)); if (this.StartDate != null && this.StartDate > DateTime.MinValue) { query.Append(" WHERE CAST(ba.[DateEarned] AS DATE) >= CAST(@startDate AS DATE)"); parameters.Add(new SqlParameter("startDate", report.Since)); } result = SqlHelper.ExecuteReader(conn, System.Data.CommandType.Text, query.ToString(), parameters.ToArray()); if (result.Read()) { report.BadgesAwarded = result["BadgesAwarded"] as int? ?? 0; } else { throw new Exception("No badge data returned."); } return(report); }