public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, TransactionGraphBy graphBy = TransactionGraphBy.Total,
DateTime?startDate = null, DateTime?endDate = null, decimal?minAmount = null, decimal?maxAmount = null,
string currencyTypeIds = null, string sourceTypeIds = null, string accountIds = null, int?dataViewId = null)
{
var numericCurrencyTypeIds = new List <int>();
if (currencyTypeIds != null)
{
currencyTypeIds.Split(',').ToList().ForEach(i => numericCurrencyTypeIds.Add(i.AsInteger()));
}
var numericSourceTypeIds = new List <int>();
if (sourceTypeIds != null)
{
sourceTypeIds.Split(',').ToList().ForEach(i => numericSourceTypeIds.Add(i.AsInteger()));
}
var numericAccountIds = new List <int>();
if (accountIds != null)
{
accountIds.Split(',').ToList().ForEach(i => numericAccountIds.Add(i.AsInteger()));
}
return(new FinancialTransactionDetailService(new RockContext()).GetChartData(
groupBy, graphBy, startDate, endDate, minAmount, maxAmount, numericCurrencyTypeIds,
numericSourceTypeIds, numericAccountIds, dataViewId));
}
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, TransactionGraphBy graphBy = TransactionGraphBy.Total,
DateTime? startDate = null, DateTime? endDate = null, decimal? minAmount = null, decimal? maxAmount = null,
string currencyTypeIds = null, string sourceTypeIds = null, string accountIds = null, int? dataViewId = null )
{
var numericCurrencyTypeIds = new List<int>();
if ( currencyTypeIds != null )
{
currencyTypeIds.Split( ',' ).ToList().ForEach( i => numericCurrencyTypeIds.Add( i.AsInteger() ) );
}
var numericSourceTypeIds = new List<int>();
if ( sourceTypeIds != null )
{
sourceTypeIds.Split( ',' ).ToList().ForEach( i => numericSourceTypeIds.Add( i.AsInteger() ) );
}
var numericAccountIds = new List<int>();
if ( accountIds != null )
{
accountIds.Split( ',' ).ToList().ForEach( i => numericAccountIds.Add( i.AsInteger() ) );
}
return new FinancialTransactionDetailService( new RockContext() ).GetChartData(
groupBy, graphBy, startDate, endDate, minAmount, maxAmount, numericCurrencyTypeIds,
numericSourceTypeIds, numericAccountIds, dataViewId );
}
/// <summary>
/// Gets the financial transaction detail with summary date time.
/// </summary>
/// <param name="qry">The qry.</param>
/// <param name="summarizeBy">The summarize by.</param>
/// <returns></returns>
public static IQueryable<FinancialTransactionDetailService.FinancialTransactionDetailWithSummaryDateTime> GetFinancialTransactionDetailWithSummaryDateTime( this IQueryable<FinancialTransactionDetail> qry, ChartGroupBy summarizeBy )
{
//// for Date SQL functions, borrowed some ideas from http://stackoverflow.com/a/1177529/1755417 and http://stackoverflow.com/a/133101/1755417 and http://stackoverflow.com/a/607837/1755417
if ( summarizeBy == ChartGroupBy.Week )
{
var knownSunday = new DateTime( 1966, 1, 30 ); // Because we can't use the @@DATEFIRST option in Linq to query how DATEPART("weekday",) will work, use a known Sunday date instead.
var qryWithSundayDate = qry.Select( d => new
{
FinancialTransactionDetail = d,
SundayDate = SqlFunctions.DateAdd(
"day",
SqlFunctions.DateDiff( "day",
"1900-01-01",
SqlFunctions.DateAdd( "day",
( ( ( SqlFunctions.DatePart( "weekday", knownSunday ) + 7 ) - SqlFunctions.DatePart( "weekday", d.Transaction.TransactionDateTime ) ) % 7 ),
d.Transaction.TransactionDateTime
)
),
"1900-01-01"
)
} );
var qryGroupedBy = qryWithSundayDate.Select( d => new FinancialTransactionDetailService.FinancialTransactionDetailWithSummaryDateTime
{
SummaryDateTime = (DateTime)d.SundayDate,
FinancialTransactionDetail = d.FinancialTransactionDetail
} );
return qryGroupedBy;
}
else
{
var qryGroupedBy = qry.Select( d => new FinancialTransactionDetailService.FinancialTransactionDetailWithSummaryDateTime
{
// Build a CASE statement to group by week, or month, or year
SummaryDateTime = (DateTime)(
// GroupBy Month
summarizeBy == ChartGroupBy.Month ? SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "day", d.Transaction.TransactionDateTime ) + 1, DbFunctions.TruncateTime( d.Transaction.TransactionDateTime.Value ) ) :
// GroupBy Year
summarizeBy == ChartGroupBy.Year ? SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "dayofyear", d.Transaction.TransactionDateTime ) + 1, DbFunctions.TruncateTime( d.Transaction.TransactionDateTime.Value ) ) :
// shouldn't happen
null
),
FinancialTransactionDetail = d
} );
return qryGroupedBy;
}
}
/// <summary>
/// Creates the dynamic attendance grid columns.
/// </summary>
/// <param name="groupBy">The group by.</param>
private void CreateDynamicAttendanceGridColumns()
{
//ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
ChartGroupBy groupBy = ChartGroupBy.Week;
// Ensure the columns for the Attendance Checkmarks are there
var attendanceSummaryFields = gAttendeesAttendance.Columns.OfType <BoolFromArrayField <DateTime> >().Where(a => a.DataField == "AttendanceSummary").ToList();
var existingSummaryDates = attendanceSummaryFields.Select(a => a.ArrayKey).ToList();
if (existingSummaryDates.Any(a => !_possibleAttendances.Contains(a)) || _possibleAttendances.Any(a => !existingSummaryDates.Contains(a)))
{
foreach (var oldField in attendanceSummaryFields.Reverse <BoolFromArrayField <DateTime> >())
{
// remove all these fields if they have changed
gAttendeesAttendance.Columns.Remove(oldField);
}
// limit to 520 checkmark columns so that we don't blow up the server (just in case they select every week for the last 100 years or something).
var maxColumns = 520;
foreach (var summaryDate in _possibleAttendances.Take(maxColumns))
{
var boolFromArrayField = new BoolFromArrayField <DateTime>();
boolFromArrayField.ArrayKey = summaryDate;
boolFromArrayField.DataField = "AttendanceSummary";
switch (groupBy)
{
case ChartGroupBy.Year:
boolFromArrayField.HeaderText = summaryDate.ToString("yyyy");
break;
case ChartGroupBy.Month:
boolFromArrayField.HeaderText = summaryDate.ToString("MMM yyyy");
break;
case ChartGroupBy.Week:
boolFromArrayField.HeaderText = summaryDate.ToString("M/d/yy");
break;
default:
// shouldn't happen
boolFromArrayField.HeaderText = summaryDate.ToString();
break;
}
gAttendeesAttendance.Columns.Add(boolFromArrayField);
}
}
}
/// <summary>
/// Gets the attendance with a SummaryDateTime column by Week, Month, or Year
/// </summary>
/// <param name="qryAttendance">The qry attendance.</param>
/// <param name="summarizeBy">The group by.</param>
/// <returns></returns>
public static IQueryable<AttendanceService.AttendanceWithSummaryDateTime> GetAttendanceWithSummaryDateTime( this IQueryable<Attendance> qryAttendance, ChartGroupBy summarizeBy )
{
IQueryable<AttendanceService.AttendanceWithSummaryDateTime> qryAttendanceGroupedBy;
if ( summarizeBy == ChartGroupBy.Week )
{
qryAttendanceGroupedBy = qryAttendance.Select( a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = a.SundayDate,
Attendance = a
} );
}
else if ( summarizeBy == ChartGroupBy.Month )
{
qryAttendanceGroupedBy = qryAttendance.Select( a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = (DateTime)SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "day", a.SundayDate ) + 1, a.SundayDate ),
Attendance = a
} );
}
else if ( summarizeBy == ChartGroupBy.Year )
{
qryAttendanceGroupedBy = qryAttendance.Select( a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = (DateTime)SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "dayofyear", a.SundayDate ) + 1, a.SundayDate ),
Attendance = a
} );
}
else
{
// shouldn't happen
qryAttendanceGroupedBy = qryAttendance.Select( a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = a.SundayDate,
Attendance = a
} );
}
return qryAttendanceGroupedBy;
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy, AttendanceGraphBy graphBy, DateTime?startDate, DateTime?endDate, string groupIds, string campusIds, int?dataViewId)
{
return(GetChartData(groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, null));
}
/// <summary>
/// Gets the attendance with a SummaryDateTime column by Week, Month, or Year
/// </summary>
/// <param name="qryAttendance">The qry attendance.</param>
/// <param name="summarizeBy">The group by.</param>
/// <returns></returns>
public static IQueryable <AttendanceService.AttendanceWithSummaryDateTime> GetAttendanceWithSummaryDateTime(this IQueryable <Attendance> qryAttendance, ChartGroupBy summarizeBy)
{
IQueryable <AttendanceService.AttendanceWithSummaryDateTime> qryAttendanceGroupedBy;
if (summarizeBy == ChartGroupBy.Week)
{
qryAttendanceGroupedBy = qryAttendance.Select(a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = a.SundayDate,
Attendance = a
});
}
else if (summarizeBy == ChartGroupBy.Month)
{
qryAttendanceGroupedBy = qryAttendance.Select(a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = (DateTime)SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("day", a.SundayDate) + 1, a.SundayDate),
Attendance = a
});
}
else if (summarizeBy == ChartGroupBy.Year)
{
qryAttendanceGroupedBy = qryAttendance.Select(a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = (DateTime)SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("dayofyear", a.SundayDate) + 1, a.SundayDate),
Attendance = a
});
}
else
{
// shouldn't happen
qryAttendanceGroupedBy = qryAttendance.Select(a => new AttendanceService.AttendanceWithSummaryDateTime
{
SummaryDateTime = a.SundayDate,
Attendance = a
});
}
return(qryAttendanceGroupedBy);
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, int?dataViewId = null, string scheduleIds = null)
{
var qryAttendance = Queryable().AsNoTracking()
.Where(a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null);
if (startDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime < endDate.Value);
}
if (dataViewId.HasValue)
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var personService = new PersonService(rockContext);
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sort)
.Select(p => p.Id);
qryAttendance = qryAttendance.Where(a => dataViewPersonIdQry.Contains(a.PersonAlias.PersonId));
}
}
if (!string.IsNullOrWhiteSpace(groupIds))
{
var groupIdList = groupIds.Split(',').AsIntegerList();
qryAttendance = qryAttendance.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value));
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = (campusIds ?? "").Split(',').ToList().Any(a => a.Equals("null", StringComparison.OrdinalIgnoreCase));
var campusIdList = (campusIds ?? "").Split(',').AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove(0);
if (campusIdList.Any())
{
if (includeNullCampus)
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where(a => (a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)) || !a.CampusId.HasValue);
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value));
}
}
else if (includeNullCampus)
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where(a => !a.CampusId.HasValue);
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = (scheduleIds ?? "").Split(',').AsIntegerList();
scheduleIdList.Remove(0);
if (scheduleIdList.Any())
{
qryAttendance = qryAttendance.Where(a => a.ScheduleId.HasValue && scheduleIdList.Contains(a.ScheduleId.Value));
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime(groupBy);
var summaryQry = qryAttendanceWithSummaryDateTime.Select(a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
});
List <SummaryData> result = null;
if (graphBy == AttendanceGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Group)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Schedule)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Location)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Location }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
return(result);
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null, int? dataViewId = null, string scheduleIds = null )
{
var qryAttendance = Queryable().AsNoTracking()
.Where( a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null );
if ( startDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime < endDate.Value );
}
if ( dataViewId.HasValue )
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService( rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var personService = new PersonService( rockContext );
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
qryAttendance = qryAttendance.Where( a => dataViewPersonIdQry.Contains( a.PersonAlias.PersonId ) );
}
}
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
var groupIdList = groupIds.Split( ',' ).AsIntegerList();
qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) );
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = ( campusIds ?? "" ).Split( ',' ).ToList().Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
var campusIdList = ( campusIds ?? "" ).Split( ',' ).AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove( 0 );
if ( campusIdList.Any() )
{
if ( includeNullCampus )
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue );
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) );
}
}
else if ( includeNullCampus )
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue );
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = ( scheduleIds ?? "" ).Split( ',' ).AsIntegerList();
scheduleIdList.Remove( 0 );
if ( scheduleIdList.Any() )
{
qryAttendance = qryAttendance.Where( a => a.ScheduleId.HasValue && scheduleIdList.Contains( a.ScheduleId.Value ) );
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy );
var summaryQry = qryAttendanceWithSummaryDateTime.Select( a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
} );
List<SummaryData> result = null;
if ( graphBy == AttendanceGraphBy.Total )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Campus )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Campus } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Group )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Group } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Schedule )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Schedule } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Location )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Location } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
return result;
}
GetFinancialTransactionDetailWithSummaryDateTime(this IQueryable <FinancialTransactionDetail> qry, ChartGroupBy summarizeBy)
{
if (summarizeBy == ChartGroupBy.Week)
{
var qryWithSundayDate = qry.Select(d => new
{
FinancialTransactionDetail = d,
SundayDate = d.Transaction.SundayDate
});
var qryGroupedBy = qryWithSundayDate.Select(d => new FinancialTransactionDetailService.FinancialTransactionDetailWithSummaryDateTime
{
SummaryDateTime = (DateTime)d.SundayDate,
FinancialTransactionDetail = d.FinancialTransactionDetail
});
return(qryGroupedBy);
}
else
{
var qryGroupedBy = qry.Select(d => new FinancialTransactionDetailService.FinancialTransactionDetailWithSummaryDateTime
{
// Build a CASE statement to group by week, or month, or year
SummaryDateTime = (DateTime)(
// GroupBy Month
summarizeBy == ChartGroupBy.Month ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("day", d.Transaction.TransactionDateTime) + 1, DbFunctions.TruncateTime(d.Transaction.TransactionDateTime.Value)) :
// GroupBy Year
summarizeBy == ChartGroupBy.Year ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("dayofyear", d.Transaction.TransactionDateTime) + 1, DbFunctions.TruncateTime(d.Transaction.TransactionDateTime.Value)) :
// shouldn't happen
null
),
FinancialTransactionDetail = d
});
return(qryGroupedBy);
}
}
/// <summary>
/// Gets the possible attendances for the date range.
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by type.</param>
/// <returns></returns>
public List<DateTime> GetPossibleAttendancesForDateRange( DateRange dateRange, ChartGroupBy attendanceGroupBy )
{
TimeSpan dateRangeSpan = dateRange.End.Value - dateRange.Start.Value;
var result = new List<DateTime>();
if ( attendanceGroupBy == ChartGroupBy.Week )
{
var endOfFirstWeek = dateRange.Start.Value.EndOfWeek( RockDateTime.FirstDayOfWeek );
var endOfLastWeek = dateRange.End.Value.EndOfWeek( RockDateTime.FirstDayOfWeek );
var weekEndDate = endOfFirstWeek;
while ( weekEndDate <= endOfLastWeek )
{
// Weeks are summarized as the last day of the "Rock" week (Sunday)
result.Add( weekEndDate );
weekEndDate = weekEndDate.AddDays( 7 );
}
}
else if ( attendanceGroupBy == ChartGroupBy.Month )
{
var endOfFirstMonth = dateRange.Start.Value.AddDays( -( dateRange.Start.Value.Day - 1 ) ).AddMonths( 1 ).AddDays( -1 );
var endOfLastMonth = dateRange.End.Value.AddDays( -( dateRange.End.Value.Day - 1 ) ).AddMonths( 1 ).AddDays( -1 );
//// Months are summarized as the First Day of the month: For example, 5/1/2015 would include everything from 5/1/2015 - 5/31/2015 (inclusive)
var monthStartDate = new DateTime( endOfFirstMonth.Year, endOfFirstMonth.Month, 1 );
while ( monthStartDate <= endOfLastMonth )
{
result.Add( monthStartDate );
monthStartDate = monthStartDate.AddMonths( 1 );
}
}
else if ( attendanceGroupBy == ChartGroupBy.Year )
{
var endOfFirstYear = new DateTime( dateRange.Start.Value.Year, 1, 1 ).AddYears( 1 ).AddDays( -1 );
var endOfLastYear = new DateTime( dateRange.End.Value.Year, 1, 1 ).AddYears( 1 ).AddDays( -1 );
//// Years are summarized as the First Day of the year: For example, 1/1/2015 would include everything from 1/1/2015 - 12/31/2015 (inclusive)
var yearStartDate = new DateTime( endOfFirstYear.Year, 1, 1 );
while ( yearStartDate <= endOfLastYear )
{
result.Add( yearStartDate );
yearStartDate = yearStartDate.AddYears( 1 );
}
}
// only include current and previous dates
var currentDateTime = RockDateTime.Now;
result = result.Where( a => a <= currentDateTime.Date ).ToList();
return result;
}
/// <summary>
/// Updates the possible attendance summary dates
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by.</param>
public void UpdatePossibleAttendances(DateRange dateRange, ChartGroupBy attendanceGroupBy)
{
_possibleAttendances = GetPossibleAttendancesForDateRange(dateRange, attendanceGroupBy);
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData(
ChartGroupBy groupBy, TransactionGraphBy graphBy, DateTime? start, DateTime? end, decimal? minAmount, decimal? maxAmount,
List<int> currencyTypeIds, List<int> sourceTypeIds, List<int> accountIds, int? dataViewId )
{
var qry = GetGifts( start, end, minAmount, maxAmount, currencyTypeIds, sourceTypeIds, accountIds, dataViewId );
var qryWithSummaryDateTime = qry.GetFinancialTransactionDetailWithSummaryDateTime( groupBy );
var summaryQry = qryWithSummaryDateTime.Select( d => new
{
d.SummaryDateTime,
Campus = new
{
Id = d.FinancialTransactionDetail.Account.CampusId ?? 0,
Name = d.FinancialTransactionDetail.Account.Campus != null ?
d.FinancialTransactionDetail.Account.Campus.Name : "None"
},
Account = new
{
Id = d.FinancialTransactionDetail.AccountId,
Name = d.FinancialTransactionDetail.Account.Name
},
Amount = d.FinancialTransactionDetail.Amount
} );
List<SummaryData> result = null;
if ( graphBy == TransactionGraphBy.Total )
{
var groupByQry = summaryQry.GroupBy( d => new { d.SummaryDateTime } ).Select( s => new { s.Key, Amount = s.Sum( a => a.Amount ) } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = "Total",
YValue = d.Amount
} ).ToList();
}
else if ( graphBy == TransactionGraphBy.Campus )
{
var groupByQry = summaryQry.GroupBy( d => new { d.SummaryDateTime, Series = d.Campus } ).Select( s => new { s.Key, Amount = s.Sum( a => a.Amount ) } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = d.Key.Series.Name,
YValue = d.Amount
} ).ToList();
}
else if ( graphBy == TransactionGraphBy.FinancialAccount )
{
var groupByQry = summaryQry.GroupBy( d => new { d.SummaryDateTime, Series = d.Account } ).Select( s => new { s.Key, Amount = s.Sum( a => a.Amount ) } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = d.Key.Series.Name,
YValue = d.Amount
} ).ToList();
}
if ( result.Count == 1 )
{
var dummyZeroDate = start ?? DateTime.MinValue;
result.Insert( 0, new SummaryData { DateTime = dummyZeroDate, DateTimeStamp = dummyZeroDate.ToJavascriptMilliseconds(), SeriesId = result[0].SeriesId, YValue = 0 } );
}
return result;
}
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null, int? dataViewId = null )
{
return new AttendanceService( new RockContext() ).GetChartData( groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId );
}
/// <summary>
/// Gets the possible attendances for the date range.
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by type.</param>
/// <returns></returns>
public List <DateTime> GetPossibleAttendancesForDateRange(DateRange dateRange, ChartGroupBy attendanceGroupBy)
{
var result = new List <DateTime>();
result.AddRange(_possibleAttendances);
var startDay = dateRange.Start.Value;
var firstAttendanceDate = startDay;
if (_possibleAttendances.Any())
{
firstAttendanceDate = _possibleAttendances.FirstOrDefault();
startDay = dateRange.Start.Value.StartOfWeek(firstAttendanceDate.DayOfWeek);
}
var endDate = dateRange.End.Value;
var endDay = endDate.StartOfWeek(firstAttendanceDate.DayOfWeek);
if (endDay.Date == endDate.Date)
{
endDay = endDay.AddDays(-7);
}
else
{
startDay = startDay.AddDays(7);
}
if (attendanceGroupBy == ChartGroupBy.Week)
{
var weekEndDate = startDay;
while (weekEndDate <= endDay)
{
if (!result.Contains(weekEndDate))
{
result.Add(weekEndDate);
}
weekEndDate = weekEndDate.AddDays(7);
}
}
else if (attendanceGroupBy == ChartGroupBy.Month)
{
var endOfFirstMonth = startDay.AddDays(-(startDay.Day - 1)).AddMonths(1).AddDays(-1);
var endOfLastMonth = endDay.AddDays(-(endDay.Day - 1)).AddMonths(1).AddDays(-1);
//// Months are summarized as the First Day of the month: For example, 5/1/2015 would include everything from 5/1/2015 - 5/31/2015 (inclusive)
var monthStartDate = new DateTime(endOfFirstMonth.Year, endOfFirstMonth.Month, 1);
while (monthStartDate <= endOfLastMonth)
{
result.Add(monthStartDate);
monthStartDate = monthStartDate.AddMonths(1);
}
}
else if (attendanceGroupBy == ChartGroupBy.Year)
{
var endOfFirstYear = new DateTime(startDay.Year, 1, 1).AddYears(1).AddDays(-1);
var endOfLastYear = new DateTime(endDay.Year, 1, 1).AddYears(1).AddDays(-1);
//// Years are summarized as the First Day of the year: For example, 1/1/2015 would include everything from 1/1/2015 - 12/31/2015 (inclusive)
var yearStartDate = new DateTime(endOfFirstYear.Year, 1, 1);
while (yearStartDate <= endOfLastYear)
{
result.Add(yearStartDate);
yearStartDate = yearStartDate.AddYears(1);
}
}
// only include current and previous dates
var currentDateTime = RockDateTime.Now;
result = result.Where(a => a <= currentDateTime.Date).OrderBy(a => a).ToList();
return(result);
}
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, string scheduleIds = null, int?dataViewId = null)
{
return(new AttendanceService(new RockContext()).GetChartData(groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, scheduleIds));
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy, AttendanceGraphBy graphBy, DateTime? startDate, DateTime? endDate, string groupIds, string campusIds, int? dataViewId )
{
return GetChartData( groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, null );
}
/// <summary>
/// Updates the possible attendance summary dates
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by.</param>
public void UpdatePossibleAttendances( DateRange dateRange, ChartGroupBy attendanceGroupBy )
{
_possibleAttendances = GetPossibleAttendancesForDateRange( dateRange, attendanceGroupBy );
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(
ChartGroupBy groupBy, TransactionGraphBy graphBy, DateTime?start, DateTime?end, decimal?minAmount, decimal?maxAmount,
List <int> currencyTypeIds, List <int> sourceTypeIds, List <int> accountIds, int?dataViewId)
{
var qry = GetGifts(start, end, minAmount, maxAmount, currencyTypeIds, sourceTypeIds, accountIds, dataViewId);
var qryWithSummaryDateTime = qry.GetFinancialTransactionDetailWithSummaryDateTime(groupBy);
var summaryQry = qryWithSummaryDateTime.Select(d => new
{
d.SummaryDateTime,
Campus = new
{
Id = d.FinancialTransactionDetail.Account.CampusId ?? 0,
Name = d.FinancialTransactionDetail.Account.Campus != null ?
d.FinancialTransactionDetail.Account.Campus.Name : "None"
},
Account = new
{
Id = d.FinancialTransactionDetail.AccountId,
Name = d.FinancialTransactionDetail.Account.Name
},
Amount = d.FinancialTransactionDetail.Amount
});
List <SummaryData> result = null;
if (graphBy == TransactionGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(d => new { d.SummaryDateTime }).Select(s => new { s.Key, Amount = s.Sum(a => a.Amount) }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = "Total",
YValue = d.Amount
}).ToList();
}
else if (graphBy == TransactionGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(d => new { d.SummaryDateTime, Series = d.Campus }).Select(s => new { s.Key, Amount = s.Sum(a => a.Amount) }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = d.Key.Series.Name,
YValue = d.Amount
}).ToList();
}
else if (graphBy == TransactionGraphBy.FinancialAccount)
{
var groupByQry = summaryQry.GroupBy(d => new { d.SummaryDateTime, Series = d.Account }).Select(s => new { s.Key, Amount = s.Sum(a => a.Amount) }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(d => new SummaryData
{
DateTimeStamp = d.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = d.Key.SummaryDateTime,
SeriesId = d.Key.Series.Name,
YValue = d.Amount
}).ToList();
}
return(result);
}
