protected virtual void RequestData() { DateTimeRange range = Mode == PerformanceViewMode.Daily ? DateTimeUtils.GetDayRange(SelectedDate) : DateTimeUtils.GetMonthRange(SelectedDate); GroupingPeriod groupingPeriod = Mode == PerformanceViewMode.Daily ? GroupingPeriod.Hour : GroupingPeriod.Day; RequestData("ChartDataSource", x => x.GetSales(range, groupingPeriod), x => ChartDataSource = x); }
/// <summary> /// Make groups go without gaps. /// </summary> private static void UnsparseGroups(IList <PeriodGroup> groups, GroupingPeriod groupBy) { // Just exit if empty collection if (groups.Count == 0) { return; } int prevInterval = groups.Count > 0 ? groups[0].Interval : -1; for (int i = 1; i < groups.Count; i++) { int interval = prevInterval + 1; // If gap is found if (groups[i].Interval > interval) { // Insert new group DateTime intervalStart, intervalEnd; GetIntervalDates(groupBy, interval, out intervalStart, out intervalEnd); PeriodGroup group = new PeriodGroup(interval, intervalStart, intervalEnd); groups.Insert(i, group); } prevInterval = interval; } }
/// <summary> /// Constructs reporter. /// </summary> public ProjectStatisticsReporter(Revision[] revisions, GroupingPeriod groupBy, DateTime fromDate, DateTime toDate) { m_revisions = revisions; m_groupBy = groupBy; m_fromDate = fromDate; m_toDate = toDate; }
protected string BuildJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { string groupBy = GetGroupBy(period, "Sales.Sale_Date"); string groupByField = string.Empty; string groupByCondition = string.Empty; if(!string.IsNullOrEmpty(groupBy)) { groupByField = ", " + groupBy + " AS SaleDate"; groupByCondition = ", " + groupBy; } string keyColumn = string.Empty; string tableName = string.Empty; string columnName = string.Empty; if(column != GroupColumn.None) { string name = Enum.GetName(typeof(GroupColumn), column); keyColumn = name + "Id"; tableName = name + "s"; columnName = name + "_Name"; } string query = string.Format(@"SELECT {1} as Name, SUM(Sales.Total_Cost) as TotalCost, SUM(Sales.Units) as Units {2} FROM Sales INNER JOIN {3} as LinkedTable on Sales.{0} = LinkedTable.Id WHERE Sales.Sale_Date BETWEEN @PeriodStart and @PeriodEnd GROUP BY {1} {4}", keyColumn, Cstr("LinkedTable." + columnName), groupByField, tableName, groupByCondition); return query; }
protected string BuildQueryBySales(DateTime start, DateTime end, GroupingPeriod period) { string groupBy = GetGroupBy(period, "Sale_Date"); string groupByExpression = ""; if (!string.IsNullOrEmpty(groupBy)) { groupByExpression = string.Format(@", {0} AS SaleDate", groupBy); } string query = String.Format(@"SELECT 'TOTAL' AS Name, SUM(Total_Cost) AS TotalCost, SUM(Units) AS Units {0} FROM Sales WHERE Sale_Date BETWEEN @PeriodStart and @PeriodEnd ", groupByExpression); if (!string.IsNullOrEmpty(groupBy)) { query += " GROUP BY " + groupBy; } return(query); }
/// <summary> /// Returns group for specified revision. /// </summary> /// <remarks> /// Each group has index of perid it covers. /// Duration of period is specified as enumerated value. /// This is because if specified in days it may vary /// because number of days isn't ecual for some periods, like months /// years and quaters. /// /// Reference point is Jan-1, 1990. /// </remarks> private static PeriodGroup GetGroup(List <PeriodGroup> groups, GroupingPeriod groupBy, Revision revision) { DateTime date = revision.Date; DateTime refDate = new DateTime(1990, 1, 1); TimeSpan offset = revision.Date - refDate; int index = -1; // Day if (groupBy == GroupingPeriod.Day) { // For days it's just number of days // from reference point till revision date index = offset.Days; } else if (groupBy == GroupingPeriod.Week) // Week { // For weeks it's quotient of division days by 7 index = offset.Days / 7; } else if (groupBy == GroupingPeriod.Month) // Month { // 01.1990 - 0 = (1990 - 1990)*12 + (01 - 01) = 0*12 + 0 // 12.1990 - 11 = (1990 - 1990)*12 + (12 - 01) = 0*12 + 11 // 01.1991 - 12 = (1991 - 1990)*12 + (01 - 01) = 1*12 + 0 // 06.1991 - 17 = (1991 - 1990)*12 + (06 - 01) = 1*12 + 5 // 06.1992 - 29 = (1992 - 1990)*12 + (06 - 01) = 2*12 + 5 // Number of full years plus index = (date.Year - refDate.Year) * 12 + (date.Month - refDate.Month); } else if (groupBy == GroupingPeriod.Quater) // Quater { // Same as months but divided by 3 index = ((date.Year - refDate.Year) * 12 + (date.Month - refDate.Month)) / 3; } else if (groupBy == GroupingPeriod.Year) // Year { // Offset of year index = date.Year - refDate.Year; } // Check that it's been caluclated Debug.Assert(index > 0); PeriodGroup group = groups.Find(item => item.Interval == index); if (group == null) { DateTime intervalStart, intervalEnd; // Get dates GetIntervalDates(groupBy, index, out intervalStart, out intervalEnd); // Create new group group = new PeriodGroup(index, intervalStart, intervalEnd); groups.Add(group); } return(group); }
protected virtual DbCommand CreateQueryBySales(DateTime start, DateTime end, GroupingPeriod period) { string query = BuildQueryBySales(start, end, period); OleDbCommand command = new OleDbCommand(query); command.Parameters.Add(new OleDbParameter("@PeriodStart", OleDbType.Date)).Value = start; command.Parameters.Add(new OleDbParameter("@PeriodEnd", OleDbType.Date)).Value = end; command.Parameters.Add(new OleDbParameter("@DiffBase", OleDbType.BSTR)).Value = "0"; return command; }
protected virtual DbCommand CreateJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { string query = BuildJoinQuery(start, end, period, column); OleDbCommand command = new OleDbCommand(query); command.Parameters.Add(new OleDbParameter("@PeriodStart", OleDbType.Date)).Value = start; command.Parameters.Add(new OleDbParameter("@PeriodEnd", OleDbType.Date)).Value = end; return(command); }
protected virtual DbCommand CreateQueryBySales(DateTime start, DateTime end, GroupingPeriod period) { string query = BuildQueryBySales(start, end, period); OleDbCommand command = new OleDbCommand(query); command.Parameters.Add(new OleDbParameter("@PeriodStart", OleDbType.Date)).Value = start; command.Parameters.Add(new OleDbParameter("@PeriodEnd", OleDbType.Date)).Value = end; command.Parameters.Add(new OleDbParameter("@DiffBase", OleDbType.BSTR)).Value = "0"; return(command); }
/// <summary> /// Returns start and end dates of interval. /// </summary> private static void GetIntervalDates(GroupingPeriod groupBy, int interval, out DateTime intervalStart, out DateTime intervalEnd) { DateTime refDate = new DateTime(1990, 1, 1); intervalStart = DateTime.MinValue; intervalEnd = DateTime.MinValue; // Day if (groupBy == GroupingPeriod.Day) { // Shift reference date by interval days intervalStart = refDate + new TimeSpan(interval, 0, 0, 0); // Interval end is the same as start intervalEnd = intervalStart; } else if (groupBy == GroupingPeriod.Week) // Week { // Shift reference date by interval weeks intervalStart = refDate.AddDays(interval * 7); intervalEnd = intervalStart.AddDays(6); } else if (groupBy == GroupingPeriod.Month) // Month { // 01.1990 - 0 = (1990 - 1990)*12 + (01 - 01) = 0*12 + 0 // 12.1990 - 11 = (1990 - 1990)*12 + (12 - 01) = 0*12 + 11 // 01.1991 - 12 = (1991 - 1990)*12 + (01 - 01) = 1*12 + 0 // 06.1991 - 17 = (1991 - 1990)*12 + (06 - 01) = 1*12 + 5 // 06.1992 - 29 = (1992 - 1990)*12 + (06 - 01) = 2*12 + 5 // Number of full years to years, modulo to months intervalStart = new DateTime(refDate.Year + interval / 12, refDate.Month + interval % 12, 1); intervalEnd = intervalStart.AddMonths(1) - new TimeSpan(1, 0, 0, 0); } else if (groupBy == GroupingPeriod.Quater) // Quater { // Multiply by 4 to get months int months1 = interval * 3; int months2 = (interval + 1) * 3; // Similar as for months intervalStart = new DateTime(refDate.Year + months1 / 12, refDate.Month + months1 % 12, 1); intervalEnd = new DateTime(refDate.Year + months2 / 12, refDate.Month + months2 % 12, 1) - new TimeSpan(1, 0, 0, 0); } else if (groupBy == GroupingPeriod.Year) // Year { // Shift years intervalStart = new DateTime(refDate.Year + interval, 1, 1); intervalEnd = new DateTime(refDate.Year + interval, 12, 31); } else { Debug.Fail("Unknow interval."); } }
/// <summary> /// Creates balance report. /// </summary> public List <PeriodGroup> CreateReport(GroupingPeriod groupBy, bool ignorePostponed) { // Input data: // +) revisions (filter for bugs) // +) interval duration (by day, by week, by month, by quater, by year) // +) additional options (e.g. how to treat Postponed) // Resulting groups List <PeriodGroup> groups = new List <PeriodGroup>(); // Get bugs for specified filter Bug[] bugs = m_provider.GetBugs(); // Process all bugs foreach (Bug bug in bugs) { // Get all revisions for selected bugs (they should be sorted) Revision[] revisions = m_provider.GetRevisions(bug.Number); Revision prevRevision = null; // Go through revisions of one bug foreach (Revision revision in revisions) { // Compare current and previous revision StatusTransition trans = GetStatusTransition(prevRevision, revision); // When combination of states corresponds to state transition if (trans != StatusTransition.None) { // Get record for period correspondent to the date of current revision PeriodGroup group = GetGroup(groups, groupBy, revision); // Update correspondent values group.Added += trans == StatusTransition.Added ? 1 : 0; group.Removed += trans == StatusTransition.Removed ? 1 : 0; group.Postponed += trans == StatusTransition.Postponed ? 1 : 0; group.Reactivated += trans == StatusTransition.Reactivated ? 1 : 0; } // Remember current revision prevRevision = revision; } } // Sort groups by index because they go in arbitrary order groups.Sort((x, y) => x.Interval - y.Interval); // Make groups go without gaps UnsparseGroups(groups, groupBy); // Return report return(groups); }
DateTime GetEndDate(DateTime startDate, DateTime end, GroupingPeriod period) { if (period == GroupingPeriod.Day) { return(startDate.AddDays(1).AddMilliseconds(-1)); } else if (period == GroupingPeriod.Hour) { return(startDate.AddHours(1).AddMilliseconds(-1)); } return(end); }
IEnumerable <SalesGroup> ExecuteQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { if (start < MinDate) { start = MinDate; } if (end > MaxDate) { end = MaxDate; } IList <SalesGroup> result = null; if (connection.State != ConnectionState.Open && connection.State != ConnectionState.Connecting) { connection.Open(); } DbCommand command; if (column == GroupColumn.None) { command = CreateQueryBySales(start, end, period); } else { command = CreateJoinQuery(start, end, period, column); } command.Connection = connection; command.Prepare(); result = new List <SalesGroup>(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { string name = reader["Name"].ToString(); decimal totalCost = 0; int units = 0; DateTime startDate = start; DateTime endDate = end; if (!(reader["TotalCost"] is DBNull)) { totalCost = Convert.ToDecimal(reader["TotalCost"]); units = Convert.ToInt32(reader["Units"]); if (reader.FieldCount > 3) { startDate = Convert.ToDateTime(reader["SaleDate"]); endDate = GetEndDate(startDate, end, period); } } result.Add(new SalesGroup(name, totalCost, units, startDate, endDate)); } } return(result); }
static string GetGroupBy(GroupingPeriod period, string column) { switch (period) { case GroupingPeriod.Hour: return(String.Format("DATEADD(\"h\", DATEDIFF(\"h\", 0," + column + "), 0)")); case GroupingPeriod.Day: return(String.Format("DATEADD(\"d\", DATEDIFF(\"d\", 0," + column + "), 0)")); } return(string.Empty); }
public IEnumerable <SalesGroup> GetSales(DateTime start, DateTime end, GroupingPeriod period) { TimeSpan step; DateTime actualStart; int randomStart; int randomEnd; switch (period) { case GroupingPeriod.Hour: step = new TimeSpan(1, 0, 0); actualStart = new DateTime(start.Year, start.Month, start.Day, start.Hour, 0, 0); randomStart = 50000; randomEnd = 250000; break; case GroupingPeriod.Day: step = new TimeSpan(24, 0, 0); actualStart = new DateTime(start.Year, start.Month, start.Day, 0, 0, 0); randomStart = 900000; randomEnd = 150000000; break; case GroupingPeriod.All: return(null); default: throw new Exception(); } List <SalesGroup> list = new List <SalesGroup>(); for (DateTime date = actualStart; date <= end; date += step) { if (period == GroupingPeriod.Hour && (date.Hour < 8 || date.Hour > 18)) { continue; } decimal amount = rnd.Next(randomStart, randomEnd); int units = (int)amount / 15; SalesGroup sg = new SalesGroup(period.ToString(), amount, units, date, date + step); list.Add(sg); } return(list); }
protected string BuildQueryBySales(DateTime start, DateTime end, GroupingPeriod period) { string groupBy = GetGroupBy(period, "Sale_Date"); string groupByExpression = ""; if(!string.IsNullOrEmpty(groupBy)) groupByExpression = string.Format(@", {0} AS SaleDate", groupBy); string query = String.Format(@"SELECT 'TOTAL' AS Name, SUM(Total_Cost) AS TotalCost, SUM(Units) AS Units {0} FROM Sales WHERE Sale_Date BETWEEN @PeriodStart and @PeriodEnd ", groupByExpression); if(!string.IsNullOrEmpty(groupBy)) query += " GROUP BY " + groupBy; return query; }
protected string BuildJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { string groupBy = GetGroupBy(period, "Sales.Sale_Date"); string groupByField = string.Empty; string groupByCondition = string.Empty; if (!string.IsNullOrEmpty(groupBy)) { groupByField = ", " + groupBy + " AS SaleDate"; groupByCondition = ", " + groupBy; } string keyColumn = string.Empty; string tableName = string.Empty; string columnName = string.Empty; if (column != GroupColumn.None) { string name = Enum.GetName(typeof(GroupColumn), column); keyColumn = name + "Id"; tableName = name + "s"; columnName = name + "_Name"; } string query = string.Format(@"SELECT {1} as Name, SUM(Sales.Total_Cost) as TotalCost, SUM(Sales.Units) as Units {2} FROM Sales INNER JOIN {3} as LinkedTable on Sales.{0} = LinkedTable.Id WHERE Sales.Sale_Date BETWEEN @PeriodStart and @PeriodEnd GROUP BY {1} {4}", keyColumn, Cstr("LinkedTable." + columnName), groupByField, tableName, groupByCondition); return(query); }
/// <summary> /// Returns group for specified revision. /// </summary> /// <remarks> /// Each group has index of perid it covers. /// Duration of period is specified as enumerated value. /// This is because if specified in days it may vary /// because number of days isn't ecual for some periods, like months /// years and quaters. /// /// Reference point is Jan-1, 1990. /// </remarks> private static PeriodGroup GetGroup(GroupingPeriod groupBy, Revision revision) { DateTime date = revision.Date; DateTime refDate = new DateTime(1990, 1, 1); TimeSpan offset = revision.Date - refDate; int index = -1; // Day if( groupBy == GroupingPeriod.Day ) { // For days it's just number of days // from reference point till revision date index = offset.Days; } else if( groupBy == GroupingPeriod.Week ) // Week { // For weeks it's quotient of division days by 7 index = offset.Days/7; } else if( groupBy == GroupingPeriod.Month ) // Month { // 01.1990 - 0 = (1990 - 1990)*12 + (01 - 01) = 0*12 + 0 // 12.1990 - 11 = (1990 - 1990)*12 + (12 - 01) = 0*12 + 11 // 01.1991 - 12 = (1991 - 1990)*12 + (01 - 01) = 1*12 + 0 // 06.1991 - 17 = (1991 - 1990)*12 + (06 - 01) = 1*12 + 5 // 06.1992 - 29 = (1992 - 1990)*12 + (06 - 01) = 2*12 + 5 // Number of full years plus index = (date.Year - refDate.Year)*12 + (date.Month - refDate.Month); } else if( groupBy == GroupingPeriod.Quater ) // Quater { // Same as months but divided by 3 index = ((date.Year - refDate.Year)*12 + (date.Month - refDate.Month))/3; } else if( groupBy == GroupingPeriod.Year ) // Year { // Offset of year index = date.Year - refDate.Year; } // Check that it's been calculated Debug.Assert( index > 0 ); DateTime intervalStart, intervalEnd; // Get dates GetIntervalDates(groupBy, index, out intervalStart, out intervalEnd); // Create new group PeriodGroup group = new PeriodGroup(index, intervalStart, intervalEnd); return group; }
public IEnumerable<SalesGroup> GetSalesBySector(DateTime start, DateTime end, GroupingPeriod period) { return ExecuteQuery(start, end, period, GroupColumn.Sector); }
public IEnumerable <SalesGroup> GetSalesBySector(DateTime start, DateTime end, GroupingPeriod period) { return(ExecuteQuery(start, end, period, GroupColumn.Sector)); }
protected virtual DbCommand CreateJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { string query = BuildJoinQuery(start, end, period, column); OleDbCommand command = new OleDbCommand(query); command.Parameters.Add(new OleDbParameter("@PeriodStart", OleDbType.Date)).Value = start; command.Parameters.Add(new OleDbParameter("@PeriodEnd", OleDbType.Date)).Value = end; return command; }
public IEnumerable <SalesGroup> GetSalesBySector(DateTime start, DateTime end, GroupingPeriod period) { List <SalesGroup> list = new List <SalesGroup>(); foreach (string sector in sectors) { decimal amount = rnd.Next(4750, 87756); int units = (int)amount / 15; SalesGroup sg = new SalesGroup(sector, amount, units, start, end); list.Add(sg); } return(list); }
public static IEnumerable<SalesGroup> GetSalesBySector(this IDataProvider dataProvider, DateTimeRange range, GroupingPeriod period) { Verify(dataProvider); return dataProvider.GetSalesBySector(range.Start, range.End, period); }
DateTime GetEndDate(DateTime startDate, DateTime end, GroupingPeriod period) { if(period == GroupingPeriod.Day) return startDate.AddDays(1).AddMilliseconds(-1); else if(period == GroupingPeriod.Hour) return startDate.AddHours(1).AddMilliseconds(-1); return end; }
/// <summary> /// Returns start and end dates of interval. /// </summary> private static void GetIntervalDates(GroupingPeriod groupBy, int interval, out DateTime intervalStart, out DateTime intervalEnd) { DateTime refDate = new DateTime(1990, 1, 1); intervalStart = DateTime.MinValue; intervalEnd = DateTime.MinValue; // Day if( groupBy == GroupingPeriod.Day ) { // Shift reference date by interval days intervalStart = refDate + new TimeSpan(interval, 0, 0, 0); // Interval end is the same as start intervalEnd = intervalStart; } else if( groupBy == GroupingPeriod.Week ) // Week { // Shift reference date by interval weeks intervalStart = refDate.AddDays(interval*7); intervalEnd = intervalStart.AddDays(6); } else if( groupBy == GroupingPeriod.Month ) // Month { // 01.1990 - 0 = (1990 - 1990)*12 + (01 - 01) = 0*12 + 0 // 12.1990 - 11 = (1990 - 1990)*12 + (12 - 01) = 0*12 + 11 // 01.1991 - 12 = (1991 - 1990)*12 + (01 - 01) = 1*12 + 0 // 06.1991 - 17 = (1991 - 1990)*12 + (06 - 01) = 1*12 + 5 // 06.1992 - 29 = (1992 - 1990)*12 + (06 - 01) = 2*12 + 5 // Number of full years to years, modulo to months intervalStart = new DateTime(refDate.Year + interval/12, refDate.Month + interval%12, 1); intervalEnd = intervalStart.AddMonths(1) - new TimeSpan(1, 0, 0, 0); } else if( groupBy == GroupingPeriod.Quater ) // Quater { // Multiply by 4 to get months int months1 = interval*3; int months2 = (interval + 1)*3; // Similar as for months intervalStart = new DateTime(refDate.Year + months1/12, refDate.Month + months1%12, 1); intervalEnd = new DateTime(refDate.Year + months2/12, refDate.Month + months2%12, 1) - new TimeSpan(1, 0, 0, 0); } else if( groupBy == GroupingPeriod.Year ) // Year { // Shift years intervalStart = new DateTime(refDate.Year + interval, 1, 1); intervalEnd = new DateTime(refDate.Year + interval, 12, 31); } else { Debug.Fail("Unknow interval."); } }
static string GetGroupBy(GroupingPeriod period, string column) { switch(period) { case GroupingPeriod.Hour: return String.Format("DATEADD(\"h\", DATEDIFF(\"h\", 0," + column + "), 0)"); case GroupingPeriod.Day: return String.Format("DATEADD(\"d\", DATEDIFF(\"d\", 0," + column + "), 0)"); } return string.Empty; }
public static IEnumerable <SalesGroup> GetSalesBySector(this IDataProvider dataProvider, DateTimeRange range, GroupingPeriod period) { Verify(dataProvider); return(dataProvider.GetSalesBySector(range.Start, range.End, period)); }
IEnumerable<SalesGroup> ExecuteQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) { if(start < MinDate) start = MinDate; if(end > MaxDate) end = MaxDate; IList<SalesGroup> result = null; if(connection.State != ConnectionState.Open) connection.Open(); DbCommand command; if(column == GroupColumn.None) command = CreateQueryBySales(start, end, period); else command = CreateJoinQuery(start, end, period, column); command.Connection = connection; command.Prepare(); result = new List<SalesGroup>(); using(var reader = command.ExecuteReader()) { while(reader.Read()) { string name = reader["Name"].ToString(); decimal totalCost = 0; int units = 0; DateTime startDate = start; DateTime endDate = end; if(!(reader["TotalCost"] is DBNull)) { totalCost = Convert.ToDecimal(reader["TotalCost"]); units = Convert.ToInt32(reader["Units"]); if(reader.FieldCount > 3) { startDate = Convert.ToDateTime(reader["SaleDate"]); endDate = GetEndDate(startDate, end, period); } } result.Add(new SalesGroup(name, totalCost, units, startDate, endDate)); } } return result; }