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));
        }
Example #2
0
        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;
            }
        }
Example #4
0
        /// <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;
        }
Example #6
0
 /// <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));
 }
Example #7
0
        /// <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);
        }
Example #8
0
        /// <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;
        }
Example #12
0
 /// <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;
        }
Example #14
0
 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 );
 }
Example #15
0
        /// <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);
        }
Example #16
0
 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);
        }