/// <summary>
/// Save Aggregation Group by Day/Date
/// </summary>
/// <param name="Element_ID"></param>
protected void Save_Day_Aggregation(long Element_ID)
{
try
{
_db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
//Get last inserted record in the table Point_Agg_Day
Point_Agg_Day tbl_point_Agg_Day = _db_datawarehouse_context.Point_Agg_Day.Where(point_day => point_day.Point_ID == Element_ID).OrderByDescending(point_day => point_day.Date_ID).AsEnumerable().FirstOrDefault();
List <Aggregate_Raw_Data> _list_aggregation = new List <Aggregate_Raw_Data>();
if (tbl_point_Agg_Day != null)
{
_list_aggregation = Get_Aggregate_Raw_Data(Element_ID, Helper.Get_Last_Day(tbl_point_Agg_Day.Date_ID), AggerationType.Day);
}
else
{
_list_aggregation = Get_Aggregate_Raw_Data(Element_ID, null, AggerationType.Day);
}
//Finally Save Aggregation in to the warehouse
Save_Aggregate_To_WareHouse(_list_aggregation, AggerationType.Day, Element_ID);
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameter element_id:- " + Element_ID);
}
}
/// <summary>
/// This function calulate the aggregate on the basis of provided parameters
/// </summary>
/// <param name="Element_ID"></param>
/// <param name="Timestamp_From"></param>
/// <param name="aggregate_type">Aggregate is for Group by</param>
/// <returns></returns>
private List <Aggregate_Raw_Data> Get_Aggregate_Raw_Data(long Element_ID, DateTime?Timestamp_From, AggerationType aggregate_type)
{
try
{
_db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
IPoint_Measure_Fact_Service point_measure_fact_service = new Point_Measure_Fact_Service();
IList <Point_Measure_Fact> list = new List <Point_Measure_Fact>();
if (Timestamp_From.HasValue)
{
list = point_measure_fact_service.Get_Point_Measure_Fact_By_Element_ID_From_Date(Element_ID, Timestamp_From.GetValueOrDefault());
}
else
{
list = point_measure_fact_service.Get_Point_Measure_Fact_By_Element_ID(Element_ID);
}
List <Aggregate_Raw_Data> _list_aggregation = new List <Aggregate_Raw_Data>();
_list_aggregation.AddRange(CalculateAggregation(aggregate_type, list));
return(_list_aggregation);
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, string.Format("passing parameter element_id:-{0}, Timestamp_From:- {1}, aggregate_type:- {2}", Element_ID, Timestamp_From, aggregate_type));
return(null);
}
}
//InnonAnalyticsWarehouseEntities _db_datawarehouse_context;
/// <summary>
/// this method calulate the aggregation (Sum, Min, Max, Cum)
/// Group by Hour, Day, Month, Year
/// Element_ID and Point_ID is same
/// </summary>
/// <param name="Element_ID"></param>
public void Calculate_and_Save_Aggregation_WareHouse_By_ElementID(long Element_ID)
{
try
{
//We have four group by aggregation Hour, Day, Week, Month, Year thats why we creat four task to run
Task[] taskArray = new Task[5];
taskArray[0] = Task.Factory.StartNew(() =>
{
Save_Hour_Aggregation(Element_ID);
Update_Hour_Aggregation(Element_ID);
});
taskArray[1] = Task.Factory.StartNew(() =>
{
Save_Day_Aggregation(Element_ID);
Update_Day_Aggregation(Element_ID);
});
taskArray[2] = Task.Factory.StartNew(() =>
{
//Week number is not implemented
Save_Week_Aggregation(Element_ID);
});
taskArray[3] = Task.Factory.StartNew(() =>
{
Save_Month_Aggregation(Element_ID);
Update_Month_Aggregation(Element_ID);
});
taskArray[4] = Task.Factory.StartNew(() =>
{
Save_Year_Aggregation(Element_ID);
Update_Year_Aggregation(Element_ID);
});
try
{
// Wait for all the tasks to finish.
Task.WaitAll(taskArray);
}
catch (AggregateException e)
{
for (int j = 0; j < e.InnerExceptions.Count; j++)
{
File_Log.SaveLog_ToFile(e.InnerExceptions[j], LoggingActions.Error, "Inner catch AggregateException wait all ");
}
}
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, LoggingActions.Error, "Outer catch");
}
}
public static DateTime?Get_Last_Day(DateTime Date_ID)
{
try
{
DateTime last_Date = new DateTime(Date_ID.Year, Date_ID.Month, Date_ID.Day, 23, 59, 59);
return(last_Date);
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameters " + Date_ID);
throw ex;
}
}
public static DateTime?Get_Last_Hour(DateTime Hour_ID)
{
try
{
DateTime last_Hour = new DateTime(Hour_ID.Year, Hour_ID.Month, Hour_ID.Day, Hour_ID.Hour, 59, 59);
return(last_Hour);
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameters " + Hour_ID);
throw ex;
}
}
public static DateTime?Get_Last_Day_Of_Year(long year)
{
try
{
int _year;
int.TryParse(year.ToString(), out _year);
DateTime last_date_of_year = new DateTime(_year, 12, 31, 23, 59, 59);
return(last_date_of_year);
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameters " + year);
throw ex;
}
}
/// <summary>
/// This is the function to convert the month to last date of the month, If returns null it means the pass parameter is invalid
/// </summary>
/// <param name="year_month">Formate for the year_month parameter should be 201501 mean Jan 2015</param>
public static DateTime?Get_Last_Day_Of_Month(long year_month)
{
try
{
var newDate = DateTime.ParseExact(year_month.ToString() + " 23:59:59",
"yyyyMM HH:mm:ss",
CultureInfo.InvariantCulture);
//http://stackoverflow.com/questions/4655143/how-can-i-get-the-last-day-of-the-month-in-c
return(newDate.AddDays(1 - (newDate.Day)).AddMonths(1).AddDays(-1));
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameter:- " + year_month);
throw ex;
}
}
/// <summary>
/// Sunday is the last day fo the week, we are following the Sql server
/// SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) AS END_OF_WEEK
/// </summary>
/// <param name="year"></param>
/// <param name="weekOfYear"></param>
/// <returns></returns>
public static DateTime?LastDateOfWeekISO8601(long year_week)
{
try
{
int year, weekOfYear;
int.TryParse(year_week.ToString().Substring(0, 4), out year);
int.TryParse(year_week.ToString().Substring(4, 2), out weekOfYear);
if (weekOfYear < 54 && weekOfYear > 0)
{
DateTime jan1 = new DateTime(year, 1, 1, 23, 59, 59);
int daysOffset = DayOfWeek.Thursday - jan1.DayOfWeek;
DateTime firstThursday = jan1.AddDays(daysOffset);
var cal = CultureInfo.CurrentCulture.Calendar;
int firstWeek = cal.GetWeekOfYear(firstThursday, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
var weekNum = weekOfYear;
if (firstWeek <= 1)
{
weekNum -= 1;
}
var result = firstThursday.AddDays(weekNum * 7);
//Add three day for the last day of the week which will be sunday
result = result.AddDays(3);
if (result.DayOfWeek == DayOfWeek.Sunday)
{
return(result);
}
else
{
throw new Exception("Invalid week number, Provided Week Number:- " + weekOfYear);
}
}
else
{
throw new Exception("Week number can not greater than 53 and less than 1, Provided Week Number:- " + weekOfYear);
}
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "passing parameters " + year_week);
throw ex;
}
}
public static List <Point_Agg_Year> Convert_List_Aggregate_Raw_Data_To_Point_Agg_Year_List(IEnumerable <Aggregate_Raw_Data> dto_list_Aggregate_Raw_Data)
{
var tbl_list_Point_Year = new List <Point_Agg_Year>();
foreach (Aggregate_Raw_Data dto_Aggregate_Raw_Data in dto_list_Aggregate_Raw_Data)
{
try
{
tbl_list_Point_Year.Add(Convert_Aggregate_Raw_Data_To_Point_Agg_Year(dto_Aggregate_Raw_Data));
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "");
}
}
return(tbl_list_Point_Year);
}
private void Save_Aggregate_To_WareHouse(List <Aggregate_Raw_Data> _list_aggregation, AggerationType _aggerationType, long Element_ID)
{
if (_list_aggregation != null)
{
if (_list_aggregation.Any())
{
try
{
_db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
switch (_aggerationType)
{
case AggerationType.Hour:
_db_datawarehouse_context.Point_Agg_Hour.AddRange(Point_Agg_Hour_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Hour_List(_list_aggregation));
break;
case AggerationType.Day:
_db_datawarehouse_context.Point_Agg_Day.AddRange(Point_Agg_Day_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Day_List(_list_aggregation));
break;
case AggerationType.Week:
//_db_datawarehouse_context.Point_Agg_Week.AddRange(Point_Agg_Week_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Week_List(_list_aggregation));
break;
case AggerationType.Month:
_db_datawarehouse_context.Point_Agg_Month.AddRange(Point_Agg_Month_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Month_List(_list_aggregation));
break;
case AggerationType.Year:
_db_datawarehouse_context.Point_Agg_Year.AddRange(Point_Agg_Year_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Year_List(_list_aggregation));
break;
default:
File_Log.SaveLog_ToFile(new Exception("Invalid AggregationType"), LoggingActions.Error, "Point_ID " + Element_ID);
break;
}
_db_datawarehouse_context.SaveChanges();
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, LoggingActions.Error, "Aggragation Type " + _aggerationType + " Point_ID " + Element_ID);
}
}
}
}
private IList <Aggregate_Raw_Data> CalculateAggregation(AggerationType GroupBy, IEnumerable <Point_Measure_Fact> point_measure_fact_list_dto)
{
try
{
var Result = from t in point_measure_fact_list_dto
group t by new
{
//Date_ID = t.Date_ID,
Hour = (int)GroupBy >= (int)AggerationType.Hour ? t.Hour_ID : new DateTime(),
Day = (int)GroupBy >= (int)AggerationType.Day ? t.Timestamp_From.Date : new DateTime(),
Month = (int)GroupBy >= (int)AggerationType.Month ? t.Timestamp_From.Month : 0,
Month_Year = (int)GroupBy >= (int)AggerationType.Month ? t.Timestamp_From.Year : 0,
Year = (int)GroupBy >= (int)AggerationType.Year ? t.Timestamp_From.Year : 0,
Client_ID = t.Client_ID,
Point_ID = t.Point_ID
} into g
select new Aggregate_Raw_Data()
{
Hour_ID = g.Key.Hour,
Date_ID = g.Key.Day,
//Week_ID = g.Key.
Month_ID = g.Key.Month,
Month_Year_ID = g.Key.Month_Year,
Year_ID = g.Key.Year,
SumValue = g.Sum(m => m.Value),
MinValue = g.Min(m => m.Value),
MaxValue = g.Max(m => m.Value),
AverageValue = g.Average(m => m.Value),
Cummulative = 0,
Client_ID = g.Key.Client_ID,
Point_ID = g.Key.Point_ID,
TotalCount = g.Count()
};
return(Result.ToList());
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "");
return(null);
}
}
public static Point_Agg_Hour Convert_Aggregate_Raw_Data_To_Point_Agg_Hour(Aggregate_Raw_Data dto_Aggregate_Raw_Data)
{
var dto_Point_Hour_tbl = new Point_Agg_Hour();
try
{
if (dto_Aggregate_Raw_Data != null)
{
try
{
dto_Point_Hour_tbl.Avg_Value = dto_Aggregate_Raw_Data.AverageValue;
dto_Point_Hour_tbl.Client_ID = dto_Aggregate_Raw_Data.Client_ID;
dto_Point_Hour_tbl.Cum_Value = dto_Aggregate_Raw_Data.Cummulative;
dto_Point_Hour_tbl.Date_ID = dto_Aggregate_Raw_Data.Date_ID;
dto_Point_Hour_tbl.Hour_ID = dto_Aggregate_Raw_Data.Hour_ID;
dto_Point_Hour_tbl.Max_Value = dto_Aggregate_Raw_Data.MaxValue;
dto_Point_Hour_tbl.Min_Value = dto_Aggregate_Raw_Data.MinValue;
dto_Point_Hour_tbl.Point_ID = dto_Aggregate_Raw_Data.Point_ID;
dto_Point_Hour_tbl.Sum_Value = dto_Aggregate_Raw_Data.SumValue;
dto_Point_Hour_tbl.Hour_Count = dto_Aggregate_Raw_Data.TotalCount;
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "dto_Aggregate_Raw_Data Point_Id" + dto_Aggregate_Raw_Data.Point_ID);
}
}
else
{
throw new Exception("dto_Aggregate_Raw_Data is null");
}
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "");
}
return(dto_Point_Hour_tbl);
}
protected void Update_Month_Aggregation(long Element_ID)
{
try
{
InnonAnalyticsWarehouseEntities db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
//Get last inserted record in the table Point_Agg_Hour
int months = int.Parse(ConfigurationManagerHelper.AppSettings["aggregate_months"].ToString());
DateTime datetime = DateTime.Now.AddMonths(-months);
long MonthID = Helper.Get_Month_ID(datetime.Year, datetime.Month);
IList <Point_Agg_Month> tbl_point_Agg_Months = db_datawarehouse_context.Point_Agg_Month.Where(point_Months => point_Months.Point_ID == Element_ID && point_Months.Month_ID >= MonthID).ToList();
if (tbl_point_Agg_Months.Count() > 0)
{
List <Aggregate_Raw_Data> _list_aggregation = new List <Aggregate_Raw_Data>();
_list_aggregation = Get_Aggregate_Raw_Data(Element_ID, Helper.Get_Last_Day_Of_Month(MonthID), AggerationType.Month);
//Compare the raw data and aggregate data
_list_aggregation = _list_aggregation.Where(aggregate => tbl_point_Agg_Months.Any(aggr_Months =>
Helper.Get_Month_ID(aggregate.Month_Year_ID, aggregate.Month_ID) == aggr_Months.Month_ID &&
(aggregate.TotalCount != aggr_Months.Month_Count || aggregate.SumValue != aggr_Months.Sum_Value))).ToList();
if (_list_aggregation.Count() > 0)
{
tbl_point_Agg_Months = Point_Agg_Month_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Month_List(_list_aggregation);
db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
foreach (Point_Agg_Month tbl_point_Agg_Month in tbl_point_Agg_Months)
{
db_datawarehouse_context.Point_Agg_Month.Attach(tbl_point_Agg_Month);
db_datawarehouse_context.Entry(tbl_point_Agg_Month).State = System.Data.Entity.EntityState.Modified;
}
db_datawarehouse_context.SaveChanges();
}
}
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "Update_Month_Aggregation passing parameter element_id:- " + Element_ID);
}
}
protected void Update_Day_Aggregation(long Element_ID)
{
try
{
InnonAnalyticsWarehouseEntities db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
//Get last inserted record in the table Point_Agg_Hour
double days = double.Parse(ConfigurationManagerHelper.AppSettings["aggregate_days"].ToString());
DateTime datetime = DateTime.Now.AddDays(-(days + 1)); // add 1 because Date_ID in Point_Agg_Day 00:00:00 time
IList <Point_Agg_Day> tbl_point_Agg_Days = db_datawarehouse_context.Point_Agg_Day.Where(point_Days => point_Days.Point_ID == Element_ID && point_Days.Date_ID >= datetime).ToList();
if (tbl_point_Agg_Days.Count() > 0)
{
List <Aggregate_Raw_Data> _list_aggregation = new List <Aggregate_Raw_Data>();
_list_aggregation = Get_Aggregate_Raw_Data(Element_ID, Helper.Get_Last_Day(datetime), AggerationType.Day);
//Compare the raw data and aggregate data
_list_aggregation = _list_aggregation.Where(aggregate => tbl_point_Agg_Days.Any(aggr_Days =>
aggregate.Date_ID == aggr_Days.Date_ID &&
(aggregate.TotalCount != aggr_Days.Day_Count || aggregate.SumValue != aggr_Days.Sum_Value))).ToList();
if (_list_aggregation.Count() > 0)
{
tbl_point_Agg_Days = Point_Agg_Day_Convert.Convert_List_Aggregate_Raw_Data_To_Point_Agg_Day_List(_list_aggregation);
db_datawarehouse_context = new InnonAnalyticsWarehouseEntities();
foreach (Point_Agg_Day tbl_point_Agg_Day in tbl_point_Agg_Days)
{
db_datawarehouse_context.Point_Agg_Day.Attach(tbl_point_Agg_Day);
db_datawarehouse_context.Entry(tbl_point_Agg_Day).State = System.Data.Entity.EntityState.Modified;
}
db_datawarehouse_context.SaveChanges();
}
}
}
catch (Exception ex)
{
File_Log.SaveLog_ToFile(ex, Common.Enums.LoggingActions.Error, "Update_Day_Aggregation passing parameter element_id:- " + Element_ID);
}
}