/// <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);
}
}
public void Update_Point_Measure_Fact(Point_Measure_Fact tbl_Point_Measure_Fact)
{
datawarehousecontext = new InnonAnalyticsWarehouseEntities();
Point_Measure_Fact point_measure_fact = datawarehousecontext.Point_Measure_Fact.Find(tbl_Point_Measure_Fact.ID);
point_measure_fact.Raw_Value = tbl_Point_Measure_Fact.Raw_Value;
point_measure_fact.Point_Status = tbl_Point_Measure_Fact.Point_Status;
datawarehousecontext.SaveChanges();
}
public IList <Point_Measure_Fact> Get_Point_Measure_Fact_By_Element_ID(long Element_ID)
{
//good_status_code check the status of the data, updating from Interpolation
datawarehousecontext = new InnonAnalyticsWarehouseEntities();
IList <Point_Measure_Fact> point_measure_facts = new List <Point_Measure_Fact>();
point_measure_facts = datawarehousecontext.Point_Measure_Fact.Where(z => z.Point_ID == Element_ID &&
z.Point_Status == Interpolation.good_status_code
).OrderBy(point => point.Timestamp_From).ToList();
return(point_measure_facts);
}
/// <summary>
/// This function is used for generating the missing data when the data is not in the Datasource, for prevent the spikes
/// This function only when the data 15 mins difference
/// </summary>
private static IList <RawDataDTO> GenerateMissingData(ElementDTO element)
{
IList <RawDataDTO> rawdata = new List <RawDataDTO>();
InnonAnalyticsWarehouseEntities ctx = new InnonAnalyticsWarehouseEntities();
ctx.Database.CommandTimeout = 300;
double days = double.Parse(ConfigurationManagerHelper.AppSettings["missing_data_days"].ToString());
DateTime dt = DateTime.Now.AddDays(-days);
List <Point_Measure_Fact> point_measure_fact = ctx.Point_Measure_Fact.Where(point_measure_Fact =>
point_measure_Fact.Point_ID == element.ID && point_measure_Fact.Timestamp_From >= dt).OrderBy(order => order.Timestamp_From).ToList();
TimeSpan ts;
DateTime Previous_DateTime;
DateTime Current_DateTime;
int External_ID;
double minute_difference = 0;
for (int i = 0; i < point_measure_fact.Count; i++)
{
if (i > 0)
{
ts = point_measure_fact[i].Timestamp_From.Subtract(point_measure_fact[i - 1].Timestamp_From);
minute_difference = Math.Truncate(ts.TotalMinutes);
if (minute_difference > 15)
{
//Get the previous record from the point_measure_fact table and add "minus sign" with ID to make it unique.
External_ID = -point_measure_fact[i - 1].ID; //We need unique External ID (In normal case External_ID is the ID from the Datasource Table ID, But here we are generating the missing data we need Unique External ID).
Previous_DateTime = new DateTime(point_measure_fact[i - 1].Timestamp_From.Year, point_measure_fact[i - 1].Timestamp_From.Month, point_measure_fact[i - 1].Timestamp_From.Day, point_measure_fact[i - 1].Timestamp_From.Hour, point_measure_fact[i - 1].Timestamp_From.Minute, 0);
Current_DateTime = new DateTime(point_measure_fact[i].Timestamp_From.Year, point_measure_fact[i].Timestamp_From.Month, point_measure_fact[i].Timestamp_From.Day, point_measure_fact[i].Timestamp_From.Hour, point_measure_fact[i].Timestamp_From.Minute, 0);
for (DateTime dtfrom = Previous_DateTime.AddMinutes(15); dtfrom < Current_DateTime; dtfrom = dtfrom.AddMinutes(15))
{
if (element.Value_Type == DataValueType.Totalised)
{
rawdata.Add(new RawDataDTO {
ID = External_ID--, STATUS_TAG = "{fault,down,stale}", TIMESTAMP = dtfrom, VAL = 0, VALUE = point_measure_fact[i - 1].Raw_Value
});
}
else
{
rawdata.Add(new RawDataDTO {
ID = External_ID--, STATUS_TAG = "{fault,down,stale}", TIMESTAMP = dtfrom, VAL = point_measure_fact[i - 1].Raw_Value, VALUE = point_measure_fact[i - 1].Raw_Value
});
}
}
}
}
}
return(rawdata);
}
public void Save_Point_Measure_Fact_Service(IList <RawDataDTO> list_raw_data_dto, ElementDTO element)
{
try
{
datawarehousecontext = new InnonAnalyticsWarehouseEntities();
Point_Measure_Fact point_measure_fact;
foreach (RawDataDTO raw_dto in list_raw_data_dto)
{
point_measure_fact = new Point_Measure_Fact();
try
{
point_measure_fact.ID = 1;
point_measure_fact.Client_ID = 0;
point_measure_fact.DataRecordingFrequency = element.Recorded_Freq;
point_measure_fact.Date_ID = System.DateTime.Now.Date;
point_measure_fact.External_ID = raw_dto.ID.ToString();
point_measure_fact.Hour_ID = System.DateTime.Now.Date;
point_measure_fact.Last_Update_Time = System.DateTime.Now;
point_measure_fact.Max_Value = element.Max_Value;
point_measure_fact.Min_Value = element.Min_Value;
point_measure_fact.Point_ID = element.ID;
point_measure_fact.Schedule_ID = 0;
point_measure_fact.Timestamp_From = Convert.ToDateTime(raw_dto.TIMESTAMP);
point_measure_fact.Timestamp_To = Convert.ToDateTime(raw_dto.TIMESTAMP);
point_measure_fact.Unit_Of_Measurement = element.Unit.Unit_Name;
point_measure_fact.Value = Convert.ToDouble(raw_dto.VALUE);
datawarehousecontext.Point_Measure_Fact.Add(point_measure_fact);
}
catch (Exception ex)
{
throw ex;
}
}
datawarehousecontext.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
//datawarehousecontext.Point_Measure_Fact_Entities.Add()
}
/// <summary>
/// This function when the service accedentally fail or closed than we need the follwing function to get the last record datetime value to start it again
/// </summary>
/// <param name="element_id"></param>
private static DateTime CheckLastRecordInsterted(long element_id)
{
InnonAnalyticsWarehouseEntities ctx = new InnonAnalyticsWarehouseEntities();
DateTime from_dt = new DateTime();
if (ctx.Point_Measure_Fact.Any(element => element.Point_ID == element_id))
{
from_dt = Convert.ToDateTime(ctx.Point_Measure_Fact.Where(element => element.Point_ID == element_id).OrderByDescending(t => t.Timestamp_From).First().Timestamp_From.AddMilliseconds(2).ToString("yyyy-MM-dd,HH:mm:ss.fff"));
}
else
{
from_dt = Convert.ToDateTime(System.DateTime.Now.AddYears(-1).ToShortDateString() + " 00:00:00");
}
return(from_dt);
}
private static void Delete_Duplicate(ElementDTO element)
{
try
{
Console.WriteLine("Delete duplicate data " + element.ID + "--" + element.Source_Element_Name_History);
InnonAnalyticsWarehouseEntities ctx_delete_duplicate = new InnonAnalyticsWarehouseEntities();
ctx_delete_duplicate.Database.CommandTimeout = 300;
ctx_delete_duplicate.Database.ExecuteSqlCommand("EXEC Delete_Duplicate_Data " + element.ID);
ctx_delete_duplicate.Database.Connection.Close();
Console.WriteLine("Finished delete duplicate data ");
}
catch (Exception ex)
{
throw ex;
}
}
private static IList <RawDataDTO> GetRawMissingData(ElementDTO element, ConnectorDTO _dto_connector)
{
try
{
//Checking the missing data
InnonAnalyticsWarehouseEntities ctx = new InnonAnalyticsWarehouseEntities();
ctx.Database.CommandTimeout = 300;
IList <RawDataDTO> rawdata = ctx.Database.SqlQuery <RawDataDTO>("EXEC GetRawMissingData '" + _dto_connector.ServerOrIP + "', '" +
_dto_connector.ServerUserName + "' , '" + _dto_connector.ServerPassword + "', '" + _dto_connector.DatabaseName + "' , '" + element.Source_Element_Name_History + "' ,'" + Convert.ToDateTime(System.DateTime.Now.AddYears(-1).ToShortDateString() + " 00:00:00").ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," + element.ID).ToList();
ctx.Database.Connection.Close();
return(rawdata);
}
catch (Exception ex)
{
throw ex;
}
}
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 static void Final_Update_Point_Measure_Fact_For_WareHouse(DateTime from_date, DateTime to_date, ElementDTO element)
{
IInnonAnalyticsWarehouseEntities _db_context = new InnonAnalyticsWarehouseEntities();
IList <Point_Measure_Fact> tbl_point_measgure_facts = _db_context.Point_Measure_Fact.Where(point => point.Timestamp_From >= from_date &&
point.Timestamp_To <= to_date &&
point.Point_Status == good_status_code &&
point.Point_ID == element.ID).OrderBy(p => p.Timestamp_From).ToList();
if (tbl_point_measgure_facts.Any())
{
//check very first record if very last_previous_value=null, than it mean the value would be zero for the first record
Point_Measure_Fact last_previous_value = _db_context.Point_Measure_Fact.Where(pt => pt.Timestamp_From < from_date && pt.Point_ID == element.ID).OrderByDescending(p => p.Timestamp_From).FirstOrDefault();
if (element.Value_Type == DataValueType.Totalised)
{
for (int i = 0; i < tbl_point_measgure_facts.Count(); i++)
{
if (i == 0 && last_previous_value == null)
{
tbl_point_measgure_facts[i].Value = 0;
}
else if (i == 0)
{
tbl_point_measgure_facts[i].Value = tbl_point_measgure_facts[i].Raw_Value - last_previous_value.Raw_Value;
}
else
{
tbl_point_measgure_facts[i].Value = tbl_point_measgure_facts[i].Raw_Value - tbl_point_measgure_facts[i - 1].Raw_Value;
}
}
}
else
{
for (int i = 0; i < tbl_point_measgure_facts.Count(); i++)
{
tbl_point_measgure_facts[i].Value = tbl_point_measgure_facts[i].Raw_Value;
}
}
_db_context.SaveChanges();
}
}
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);
}
}
/// <summary>
/// This function returns the last and next good value(good value mean point_status "ok") for interpolation
/// </summary>
/// <param name="from_date"></param>
/// <param name="point_Id"></param>
public static Interpolated_Good_Value Get_Previous_and_Next_Good_Value(DateTime from_date, long point_Id)
{
IInnonAnalyticsWarehouseEntities _db_context = new InnonAnalyticsWarehouseEntities();
Interpolated_Good_Value Good_Values = new Interpolated_Good_Value();
//Last previous good value
Good_Values.Previous_Good_Value = _db_context.Point_Measure_Fact.Where(point => point.Timestamp_From <= from_date &&
point.Point_Status == good_status_code &&
point.Point_ID == point_Id).OrderByDescending(pointorder => pointorder.Timestamp_From).FirstOrDefault();
//Next previous good value
Good_Values.Next_Good_Value = _db_context.Point_Measure_Fact.Where(point => point.Timestamp_From >= from_date &&
point.Point_Status == good_status_code &&
point.Point_ID == point_Id).OrderBy(pointorder => pointorder.Timestamp_From).FirstOrDefault();
if (Good_Values.Previous_Good_Value != null && Good_Values.Next_Good_Value != null)
{
Good_Values.Number_Of_Records = _db_context.Point_Measure_Fact.Where(point => point.Timestamp_From > Good_Values.Previous_Good_Value.Timestamp_From &&
point.Timestamp_From < Good_Values.Next_Good_Value.Timestamp_From &&
point.Point_ID == point_Id).OrderBy(pointorder => pointorder.Timestamp_From).Count();
}
return(Good_Values);
}
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);
}
}
/// <summary>
/// Datawarehouse finalinsert into the Point_Mesaure_Fact
/// </summary>
/// <param name="point_measure_facts"></param>
private static void Final_Insert_In_To_DatawareHouse_Table(IList <Point_Measure_Fact> point_measure_facts)
{
try
{
BlockingCollection <Point_Measure_Fact> entityQueue = new BlockingCollection <Point_Measure_Fact>();
List <Task> writers = new List <Task>();
if (point_measure_facts.Count > 0)
{
Action writerAction = new Action(() =>
{
InnonAnalyticsWarehouseEntities ctx = new InnonAnalyticsWarehouseEntities();
ctx.Database.CommandTimeout = 300;
int totalAdded = 0;
try
{
while (true)
{
Point_Measure_Fact my = entityQueue.Take();
my.Last_Update_Time = System.DateTime.Now;
ctx.Point_Measure_Fact.Add(my);
totalAdded++;
if (totalAdded > 500)
{
//Console.WriteLine("Saving changes, TID: " + Thread.CurrentThread.ManagedThreadId);
try
{
ctx.SaveChanges();
}
catch (Exception ex)
{
Helper.WriteToFile("Error in saving: " + ex.InnerException.Message);
}
ctx.Database.Connection.Close();
ctx = new InnonAnalyticsWarehouseEntities();
}
}
}
catch (InvalidOperationException inv)
{
//WriteToFile("Saving final changes --- " + inv.Message);
//Console.WriteLine("Saving final changes, TID: " + Thread.CurrentThread.ManagedThreadId);
// Console.WriteLine("Error Saving final " + inv.Message);
ctx.SaveChanges(); // Save any pending changes
ctx.Database.Connection.Close();
totalAdded = 0;
}
});
for (int i = 0; i < 1000; i++)
{
writers.Add(Task.Factory.StartNew(writerAction));
}
foreach (Point_Measure_Fact point_measure_fact in point_measure_facts)
{
entityQueue.Add(point_measure_fact);
}
}
entityQueue.CompleteAdding();
Console.WriteLine("Done generating data.");
Task.WaitAll(writers.ToArray());
Console.WriteLine("Finished");
}
catch (Exception ex)
{
string innerexp = (ex.InnerException != null) ? ex.InnerException.Message : "";
Helper.WriteToFile("Error in the FinalInsert " + ex.Message + "--" + innerexp);
}
}
public void Update_Point_Dim(IList <ElementDTO> list_element_dto, IList <Tag_Type_Mapping_DTO> list_tag_type_mapping_dto)
{
try
{
_datawarehousecontext = new InnonAnalyticsWarehouseEntities();
IEnumerable <long> point_ids = list_element_dto.Select(element => element.ID);
IEnumerable <Point_Dim> point_dims = _datawarehousecontext.Point_Dim.Where(p_d => point_ids.Contains(p_d.Point_ID));
string columns = Helper.ConvertIEnumerableToString(list_tag_type_mapping_dto.Select(z => z.Point_Dim_Mapping), ",");
string str_insert_point_dim_query = "";
string str_update_point_dim_query = "";
string column_name = "", column_value = "";
foreach (ElementDTO element_dto in list_element_dto)
{
try
{
if (point_dims.Any(point_dim => point_dim.Point_ID == element_dto.ID))
{
str_update_point_dim_query += "update Point_Dim set ";
//str_update_point_dim_query += " Point_ID=" + element_dto.ID;
str_update_point_dim_query += " Equipment_ID=0";
str_update_point_dim_query += ",Site_ID=0";
str_update_point_dim_query += ",Client_ID=0";
//str_update_point_dim_query += ",Country=null";
//str_update_point_dim_query += ",City=null";
foreach (TagDTO tag in element_dto.Tags)
{
Tag_Type_Mapping_DTO dto_mapping = list_tag_type_mapping_dto.SingleOrDefault(z => z.Tag_Type == tag.Tag_Type_String);
str_update_point_dim_query += "," + dto_mapping.Point_Dim_Mapping + "=" + "'" + tag.Tag_Value.Trim() + "'";
}
foreach (Tag_Type_Mapping_DTO dto in list_tag_type_mapping_dto)
{
if (dto.Tag_Type != TagType.point.ToString() && dto.Tag_Type != TagType.equip.ToString() && dto.Tag_Type != TagType.site.ToString() && dto.Tag_Type != TagType.client.ToString())
{
try
{
TagDTO tag = element_dto.Tags.SingleOrDefault(t => t.Tag_Type_String.Equals(dto.Tag_Type));
if (tag == null)
{
str_update_point_dim_query += "," + dto.Point_Dim_Mapping + "=" + "null";
}
}
catch (Exception ex)
{
Helper.WriteToFile(string.Format("Multiple tag with same tag type: Element ID : {0}, Element Name {1}, Source History Name {2}, Tag_Type: {3} ", element_dto.ID, element_dto.Element_Name, element_dto.Source_Element_Name_History, dto.Tag_Type));
}
}
}
str_update_point_dim_query += " where Point_ID=" + element_dto.ID + ";";
}
else
{
column_name = "insert into Point_Dim(Point_ID,Equipment_ID,Site_ID,Client_ID";
column_value = element_dto.ID + "," + 0 + "," + 0 + "," + 0;
foreach (TagDTO tag in element_dto.Tags)
{
Tag_Type_Mapping_DTO dto = list_tag_type_mapping_dto.SingleOrDefault(z => z.Tag_Type == tag.Tag_Type_String);
column_name += "," + dto.Point_Dim_Mapping;
column_value += ",'" + tag.Tag_Value.Trim() + "'";
}
column_name += ") Values(";
column_value += ");";
str_insert_point_dim_query += column_name + column_value;
}
}
catch (Exception ex)
{
Helper.WriteToFile("Error in point dim: " + ex.Message + "---" + ex.InnerException.Message);
}
}
try
{
_datawarehousecontext.Database.ExecuteSqlCommand(@"" + str_insert_point_dim_query + str_update_point_dim_query);
}
catch (Exception ex)
{
Helper.WriteToFile("Error in point dim saving: " + ex.Message + "---" + ex.InnerException.Message);
Helper.WriteToFile("Query " + str_insert_point_dim_query + str_update_point_dim_query);
}
}
catch (Exception ex)
{
Helper.WriteToFile("Error in point dim: " + ex.Message + "---" + ex.InnerException.Message);
}
}
/// <summary>
/// If status of point is not "{ok}" we need to interpolation, for this two values required for every point
/// 1. last good value
/// 2. next good value
/// Interpolation = (b-a)/total_wrong_records+1; b is the next good value, a is last good value,
/// </summary>
/// <param name="from_date"></param>
/// <param name="to_date"></param>
/// <param name="element"></param>
/// <returns>bool</returns> if return true its mean warehouse aggregation will be run after interpolation
public static bool InterPolateData(DateTime from_date, DateTime to_date, ElementDTO element)
{
bool Is_Ready_For_Aggregation = false;
string[] bad_data_status_codes = ConfigurationManagerHelper.AppSettings["bad_data_status"].ToString().Split(',');
string actual_status_code;
IInnonAnalyticsWarehouseEntities _db_context = new InnonAnalyticsWarehouseEntities();
//First here we check last good record avalaible for interpolation, if not, than we do not need to go further, last record in the Point measure fact should be ok, other wise interpolation will not do.
var tbl_point_measgure_facts_Good_Values = _db_context.Point_Measure_Fact.Where(point =>
point.Point_ID == element.ID).OrderByDescending(p => p.Timestamp_From).Take(1).Where(point => point.Point_Status == good_status_code);
var tbl_point_measgure_facts = _db_context.Point_Measure_Fact.Where(point =>
//point.Timestamp_From >= from_date
//&& point.Timestamp_To <= to_date
point.Point_Status != good_status_code &&
point.Point_ID == element.ID).OrderBy(p => p.Timestamp_From).ToList();
if (tbl_point_measgure_facts.Any() && tbl_point_measgure_facts_Good_Values.Any())
{
from_date = tbl_point_measgure_facts.First().Timestamp_From;
foreach (var tbl_point_measure_fact in tbl_point_measgure_facts)
{
//Intially assume all data status code is ok
actual_status_code = good_status_code;
//this foreach loop check the point status is ok, if the status is not ok means the value is not correct we have to interpolate the value
foreach (string data_status_code in bad_data_status_codes)
{
if (tbl_point_measure_fact.Point_Status != null)
{
if (tbl_point_measure_fact.Point_Status.ToUpper().Contains(data_status_code.Trim()))
{
actual_status_code = data_status_code;
break;
}
}
}
if (actual_status_code != good_status_code)
{
if (element.Value_Type == DataValueType.Totalised)
{
Is_Ready_For_Aggregation = Database_Update_Interpolated_Values(tbl_point_measure_fact);
}
else
{
Is_Ready_For_Aggregation = Database_Update_Interpolated_Values(tbl_point_measure_fact);
}
}
}
//if (Is_Ready_For_Aggregation)
// Final_Update_Point_Measure_Fact_For_WareHouse(from_date, to_date, element);
}
else if (tbl_point_measgure_facts.Any())
{
Is_Ready_For_Aggregation = false;
}
else //If there is no record for interpolation than set Is_Ready_For_Aggregation true for aggregating the data
{
Is_Ready_For_Aggregation = true;
}
Final_Update_Point_Measure_Fact_For_WareHouse(from_date, to_date, element);
return(Is_Ready_For_Aggregation);
}
