/// <summary> /// 判断是否存在历史聚集数据 /// </summary> /// <param name="DateTimeId">时间Id</param> /// <param name="SensorId">传感器Id</param> /// <param name="type">聚集数据类型</param> /// <returns></returns> private bool IsAggDataExist(int DateTimeId, int SensorId, AggType type) { string sql = String.Format( @"select count([Id]) count from [T_DATA_AGGREGATION] where [SensorId]={0} and [DateTimeId]={1} and [AggDataTypeId] = {2}", SensorId, DateTimeId, Convert.ToInt32(type)); try { DataSet ds = this.helper.Query(sql); if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1) { return(false); } int num = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]); return(num > 0); } catch (Exception e) { Log.ErrorFormat( "IsAggDataExist查询失败,sql:{0}, error:{1},trace{2}", sql, e.Message, e.StackTrace); return(false); } }
/// <summary> /// 根据时间编号获取Id /// </summary> /// <param name="type">类型</param> /// <param name="timeTag">时间Code</param> /// <returns>Id</returns> private int GetDateTimeId(AggType type, string timeTag) { string sql = String.Format(@"select top 1 DATETIME_ID from T_DIM_DATETIME where {0} = '{1}' order by DATETIME_ID", this.DataTimeTableColNames[type], timeTag); try { DataSet ds = this.helper.Query(sql); if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1) { return(-1); } int id = Convert.ToInt32(ds.Tables[0].Rows[0]["DATETIME_ID"]); return(id); } catch (Exception e) { Log.ErrorFormat( "获取DateTimeId失败,Type:{0},code:{1},sql:{2}, error:{3},trace{4}", type.ToString(), timeTag, sql, e.Message, e.StackTrace); return(-1); } }
/// <summary> /// 获取上次聚集的时间ID /// </summary> /// <param name="structId"></param> /// <param name="safeFactorId"></param> /// <param name="type"></param> /// <returns>-1:表示失败,其他表示聚集时间Id</returns> public int GetLastestDateTimeId(int structId, int safeFactorId, AggType type) { string sql = String.Format( @"select top 1 ([DateTimeId]) from {3} where StructureId = {0} and SafeFactorId = {1} and [AggDataTypeId] = {2} order by [DateTimeId] desc", structId, safeFactorId, Convert.ToInt16(type), AggDataTableName); try { DataSet ds = this.helper.Query(sql); if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1) { return(-1); } int id = Convert.ToInt32(ds.Tables[0].Rows[0]["DateTimeId"]); return(id); } catch (Exception e) { Log.ErrorFormat( "{0}结构物,监测因素{1},{2}聚集数据获取失败,sql:{3}, error:{4},trace{5}", structId, safeFactorId, type, sql, e.Message, e.StackTrace); return(-1); } }
public AggResult(int structId, int factorId, string timeTag, AggType type, int configId) { this.StructId = structId; this.SafeFactorId = factorId; this.TimeTag = timeTag; this.AggType = type; this.ConfigId = configId; }
public static List <BaseAggConfig> Create(DataTable dt) { List <BaseAggConfig> configs = new List <BaseAggConfig>(); if (dt == null) { return(configs); } int id = 0; foreach (DataRow dr in dt.Rows) { try { id = Convert.ToInt32(dr["Id"]); AggType type; AggWay way; if (!AggType.TryParse(Convert.ToString(dr["AggTypeId"]), out type)) { log.WarnFormat("create config failed,aggtype wrong.ConfigId:{0}", id); continue; } if (!AggWay.TryParse(Convert.ToString(dr["AggWayId"]), out way)) { log.WarnFormat("create config failed,aggway wrong.ConfigId:{0}", id); continue; } AggTaskKey key = new AggTaskKey(Convert.ToInt32(dr["StructureId"]), Convert.ToInt32(dr["FacotrId"]), type); BaseAggConfig config = new BaseAggConfig(key); config.TimeRange = new AggTimeRange { DataBeginHour = Convert.ToInt32(dr["DataBeginHour"]), DataEndHour = Convert.ToInt32(dr["DataEndHour"]), //DateBegin = Convert.ToInt32(dr["DateBegin"]), //DateEnd = Convert.ToInt32(dr["DateEnd"]) }; if (dr["DateBegin"].ToString() != string.Empty) { config.TimeRange.DateBegin = Convert.ToInt32(dr["DateBegin"]); } if (dr["DateEnd"].ToString() != string.Empty) { config.TimeRange.DateEnd = Convert.ToInt32(dr["DateEnd"]); } config.TimingMode = Convert.ToString(dr["TimeMode"]); config.Way = way; config.ConfigId = id; configs.Add(config); } catch (Exception e) { log.WarnFormat("create config error,configId:{0},error:{1},trace:{2}", id, e.Message, e.StackTrace); continue; } } return(configs); }
private static BaseAggConfig CreateCommonConfig(AggType type) { BaseAggConfig config = new BaseAggConfig(new AggTaskKey(90, 42, type)); /// 家乐福项目配置 // config.TimeRange = new AggTimeRange(); return(config); }
/// <summary> /// 产生聚集数据Sql /// </summary> /// <param name="structureId"></param> /// <param name="factorId"></param> /// <param name="dateTimeId"></param> /// <param name="type"></param> /// <param name="aggData"></param> /// <param name="aggDataChange"></param> /// <returns></returns> private string CreateAddOrUpdateAggDataSql( int structureId, int factorId, int dateTimeId, AggType type, AggData aggData, AggData aggDataChange, int configId) { string sql; string[] aggCol = AggDataColNames.Split(','); string[] aggChangeCol = AggDataChangeColNames.Split(','); if (this.IsAggDataExist(dateTimeId, aggData.SensorId, type)) { StringBuilder sb = new StringBuilder(); sb.Append(this.GetUpdateAggDataString(aggCol, aggData.Values)); sb.Append(","); sb.Append(this.GetUpdateAggDataString(aggChangeCol, aggDataChange.Values)); sb.Append(","); sb.Append(string.Format("[AggCofigId]={0}", configId)); sql = String.Format( @"update [T_DATA_AGGREGATION] set {0} where [SensorId]={1} and [DateTimeId]={2} and [AggDataTypeId] = {3}", sb, aggData.SensorId, dateTimeId, Convert.ToInt32(type)); } else { StringBuilder col = new StringBuilder(); StringBuilder val = new StringBuilder(); for (int i = 0; i < aggData.Values.Count; i++) { col.AppendFormat("{0},{1}", aggCol[i], aggChangeCol[i]); val.AppendFormat("{0},{1}", aggData.Values[i], aggDataChange.Values[i]); if (i != aggData.Values.Count - 1) { col.Append(","); val.Append(","); } } sql = String.Format( @"insert into [T_DATA_AGGREGATION] ({0},[DateTimeId],[StructureId],[SafeFactorId],[SensorId],[AggDataTypeId],[AggCofigId]) values({1},{2},{3},{4},{5},{6},{7})", col, val, dateTimeId, structureId, factorId, aggData.SensorId, Convert.ToInt32(type), configId); } return(sql); }
public static string GetAggName <T, K>(this Expression <Func <T, K> > exp, AggType type) { var body = exp.Body as MemberExpression; if (body == null) { var ubody = (UnaryExpression)exp.Body; body = ubody.Operand as MemberExpression; } return(type + body.Member.Name); }
public DBWebAggregateControl() : base() { FTextBox = new TextBox(); FTextBox.ReadOnly = true; FLabel = new Label(); FPanel = new System.Web.UI.WebControls.Panel(); FTextBoxPortion = defaultTextBoxPortion; FLabelPosition = LabelPosition.LabelToLeft; FColumnLink = new DBWebColumnLink(this); IColumnLink = (FColumnLink as IDBWebColumnLink); base.Width = new Unit(minWidth); // TODO: remove from object inspector FAggregateType = AggType.aggAvg; }
/// <summary> /// 获取最新的聚集数据 /// </summary> /// <param name="sensorId">传感器Id</param> /// <param name="type">聚集类型</param> /// <param name="dateTimeId">日期Id</param> /// <returns></returns> public AggData GetLastestAggData(int sensorId, AggType type, int dateTimeId) { string sql = String.Format( @"select {0} from {1} where [SensorId] = {2} and [AggDataTypeId] = {3} and [DateTimeId] = {4}", AggDataColNames, AggDataTableName, sensorId, Convert.ToInt32(type), dateTimeId); try { DataSet ds = this.helper.Query(sql); if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1) { return(null); } AggData aggData = new AggData(); string[] colNames = AggDataColNames.Split(','); aggData.SensorId = sensorId; DataRow row = ds.Tables[0].Rows[0]; foreach (var colName in colNames) { if (row[colName].ToString() != string.Empty) { aggData.Values.Add(Convert.ToDouble(row[colName])); } } return(aggData); } catch (Exception e) { Log.ErrorFormat( "传感器{0},{1}的{2}聚集数据获取失败,sql:{3}, error:{4},trace{5}", sensorId, dateTimeId, type, sql, e.Message, e.StackTrace); return(null); } }
public static DateTime GetAggDate(AggType type, AggTimeRange timeRange, DateTime nowTime) { DateTime date; int day; switch (type) { case AggType.Day: date = timeRange.DataEndHour < nowTime.Hour ? nowTime : nowTime.AddDays(-1); break; case AggType.Week: day = DateTimeHelper.GetDayOfWeekByWeekFirstDayMon(nowTime); if ((day > timeRange.DateEnd) || (day == timeRange.DateEnd && timeRange.DataEndHour < nowTime.Hour)) { date = nowTime; } else { date = nowTime.AddDays(-7); } break; case AggType.Month: day = nowTime.Day; if ((day > timeRange.DateEnd) || (day == timeRange.DateEnd && timeRange.DataEndHour < nowTime.Hour)) { date = nowTime; } else { date = nowTime.AddMonths(-1); } break; default: date = nowTime; break; } return(date); }
public static AggResult CreateData(string timeTag, AggType type) { AggResult result = new AggResult(90, 42, timeTag, type, 1); result.AggDatas = new List <AggData>(); result.LastAggDatas = new List <AggData>(); AggData data = new AggData(); AggData lastData = new AggData(); data.SensorId = 1758; data.Values = new List <double>(); lastData.SensorId = 1758; lastData.Values = new List <double>(); for (int i = 0; i < 2; i++) { data.Values.Add((i + 1) * 10); lastData.Values.Add((i + 1) * 20); } result.AggDatas.Add(data); result.LastAggDatas.Add(lastData); return(result); }
public DataTable CalcCost2(string type, string formula, string costFilter, int costFilterValue, DateTime period, int recordId, int clientId, AggType agg = AggType.None, bool exp = false, string tableNamePrefix = "") { // strange, but makes things consistent if (type == "Misc") { var dtMisc = DataCommand.Create() .Param("Action", "ByPeriod") .Param("Period", period) .Param(costFilter, !string.IsNullOrEmpty(costFilter), costFilterValue) .FillDataTable("dbo.ExternalMiscExp_Select"); // leave the description column if (dtMisc.Rows.Count > 0 && agg != AggType.None) { dtMisc.Columns.Remove(dtMisc.Columns["ExpID"]); dtMisc.Columns["CalcCost"].ColumnName = "TotalCalcCost"; } return(dtMisc); } else { string costType = ""; int typeKey = 0; switch (type) { case "Room": costType = "Room"; typeKey = 0; break; case "Tool": costType = "Tool"; typeKey = 1; break; case "StoreInv": costType = "Store"; typeKey = 2; break; case "StoreJE": //From FinOps's Material JE costType = "Store"; typeKey = 3; break; } //4 tables will be returned from the code below //1st table - all instance of client order history in a specific month //2nd table - Client table - list all clients who had ordered in a specific month //3rd table - table contains three columns - ClientID, DebitAccountID, CreditAccountID //4th table - one column called colName, two rows, CalcCost, ItemCost //5th table - One column called CapCost, just one row value = 0 DataTable dt; DataTable dtClient; DataTable dtCliAcctType; DataTable dtAggCols; var ds = DataCommand.Create() .Param("CostType", costType) .Param("Period", period) .Param(costFilter, !string.IsNullOrEmpty(costFilter), costFilterValue) .Param("RecordID", recordId > 0, recordId) .Param("ClientID", clientId > 0, clientId) .Param("CostToday", exp, true) .Param("PartialAgg", agg == AggType.CliAcctType, true) .FillDataSet($"dbo.{tableNamePrefix}CostData_Select2"); dt = ds.Tables[0].Copy(); //dt table for room type has 16 columns as below //0 "ClientID" //1 "AccountID" //2 "RoomID" //3 "Entries" //4 "Hours" //5 "Days" //6 "Months" //7 "Period" //8 "CostPeriod" //9 "PerEntry" //10 "PerPeriod" //11 "ChargeTypeID" //12 "RoomCapCostAdd" //13 "RoomCapCostMul" //14 "CalcCost" //15 "BillingType" //dt table for tool type has 26 columns as below //0 ClientID //1 AccountID //2 ResourceID //3 Uses //4 SchedDuration //5 ActDuration //6 OverTime //7 Days //8 Months //9 IsStarted //10 ToolChargeMultiplierMul //11 Period //12 CostPeriod //13 PerUse //14 PerPeriod //15 ChargeTypeID //16 ToolCapCostAdd //17 ToolCapCostMul //18 ToolCreateReservCostAdd //19 ToolCreateReservCostMul //20 ToolMissedReservCostAdd //21 ToolMissedReservCostMul //22 ToolOvertimeCostAdd //23 ToolOvertimeCostMul //24 CalcCost //25 IsStarted1 //26 BillingType //It will be true only when no one orders anything in that specific month if (dt.Rows.Count == 0) { return(dt); } dtClient = ds.Tables[1].Copy(); dtCliAcctType = ds.Tables[2].Copy(); dtAggCols = ds.Tables[3].Copy(); CapCost = Convert.ToDouble(ds.Tables[4].Rows[0]["CapCost"]); // get formula from DB if (formula.Length == 0) { formula = DataCommand.Create() .Param(new { FormulaType = type, sDate = period, CostToday = exp }) .ExecuteScalar <string>($"dbo.{tableNamePrefix}CostFormula_Select2").Value; } CompileCode(typeKey, formula); // will barf if CalcSingleItem returns nothing - needs to be cleaned up //Every single row in the first table has CalcCost as column to hold the newly calculated value double lineTotalCost = 0.0; foreach (DataRow dr in dt.Rows) { lineTotalCost = CalcSingleItem(typeKey, dr); //2008-09-05 IF it's tool cost, we also have to consider the forgiven charges if (typeKey == 1) { dr["CalcCost"] = lineTotalCost * Convert.ToDouble(dr["ToolChargeMultiplierMul"]); } else { dr["CalcCost"] = lineTotalCost; } } // to allow calling this again mAssembly = null; scriptType = null; instance = null; // when agging, the only thing of true interest is the summed cost if (agg == AggType.None) { return(dt); } else { //Create a new table dtRet with all columns of dtCliAcctType table and dtAggCols's rows (CalcCost, ItemCost) //This code is execuated whenever aggregate data is needed - and it's all grouped by the columns in dtCliAcctType DataRow ndr = null; DataTable dtRet = new DataTable(); foreach (DataColumn dc in dtCliAcctType.Columns) { dtRet.Columns.Add(dc.ColumnName, typeof(int)); } foreach (DataRow dr in dtAggCols.Rows) { dtRet.Columns.Add("Total" + dr["colName"].ToString(), typeof(double)); } string strCompute = null; foreach (DataRow drCliAcctType in dtCliAcctType.Rows) { strCompute = ""; ndr = dtRet.NewRow(); foreach (DataColumn dc in dtCliAcctType.Columns) { ndr[dc.ColumnName] = drCliAcctType[dc.ColumnName]; if (strCompute.Length > 0) { strCompute += " AND "; } strCompute += dc.ColumnName + "=" + drCliAcctType[dc.ColumnName].ToString(); } foreach (DataRow dr in dtAggCols.Rows) { ndr["Total" + dr["colName"].ToString()] = dt.Compute("sum(" + dr["colName"].ToString() + ")", strCompute); } dtRet.Rows.Add(ndr); } return(dtRet); } } }
protected virtual string ToTimeMode(AggType type, string timeString) { string timemode = string.Empty; int hour; int day; int weekIndex; string[] tmp; switch (type) { case AggType.Day: if (int.TryParse(timeString.Trim(), out hour)) { if (isCornTimeHourValid(hour)) { timemode = string.Format("0 0 {0} * * ?", timeString); } } break; case AggType.Week: tmp = timeString.Trim().Split(','); if (tmp.Count() == 2 && int.TryParse(tmp[1].Trim(), out hour) && int.TryParse(tmp[0].Trim(), out day)) { if (isCornTimeHourValid(hour) && isCornTimeDayOfWeekValid(day)) { timemode = string.Format("0 0 {0} ? * {1}", hour, day); } } break; case AggType.Month: tmp = timeString.Trim().Split(','); if (tmp.Count() == 2 && int.TryParse(tmp[1].Trim(), out hour) && int.TryParse(tmp[0].Trim(), out day)) { if (isCornTimeHourValid(hour) && isCornTimeDayOfMonthValid(day)) { timemode = string.Format("0 0 {0} {1} * ?", hour, day == -1 ? "L" : day.ToString()); } } else if (tmp.Count() == 3 && int.TryParse(tmp[2].Trim(), out hour) && int.TryParse(tmp[1].Trim(), out day) && int.TryParse(tmp[0].Trim(), out weekIndex)) { if (isCornTimeHourValid(hour) && isCornTimeDayOfWeekValid(day)) { if (weekIndex == -1) ///最后一个星期 { timemode = string.Format("0 0 {0} ? * {1}L", hour, day); } else { timemode = string.Format("0 0 {0} ? * {1}#{2}", hour, day, weekIndex); } } } break; default: break; } return(timemode); }
public AggTaskKey(int StructId, int FactorId, AggType Type) { this.FactorId = FactorId; this.StructId = StructId; this.Type = Type; }
static Func <AggregationContainerDescriptor <T>, AggregationContainerDescriptor <T> > GetAggregationFuncFromGetFieldNamed <T>(Expression <Func <T, object> > fieldGetter, AggType aggType) where T : class { var name = Names.GetNameFromGetFieldNamed(fieldGetter.Body); if (name == null) { return(null); } var namedField = new Field(name); var aggName = fieldGetter.GetAggName(aggType); switch (aggType) { case AggType.Sum: return(x => x.Sum(aggName, field => field.Field(namedField))); case AggType.Count: return(x => x.ValueCount(aggName, field => field.Field(namedField))); case AggType.Average: return(x => x.Average(aggName, field => field.Field(namedField))); case AggType.Cardinality: return(x => x.Cardinality(aggName, field => field.Field(namedField))); case AggType.Stats: return(x => x.Stats(aggName, field => field.Field(namedField))); case AggType.Max: return(x => x.Max(aggName, field => field.Field(namedField))); case AggType.Min: return(x => x.Min(aggName, field => field.Field(namedField))); case AggType.First: return(x => x.Terms(aggName, field => field.Field(namedField))); case AggType.Percentile: return(x => x.Percentiles(aggName, field => field.Field(namedField))); } throw new NotImplementedException(); }
public static AggregationContainerDescriptor <T> GetStatsDescriptor <T>(this AggregationContainerDescriptor <T> agg, Expression <Func <T, object> > fieldGetter, AggType aggType, Expression <Func <T, bool> > filterRule = null) where T : class { var aggFunc = GetAggregationFunc <T>(fieldGetter, aggType); if (filterRule == null) { return(aggFunc(agg)); } var filterName = filterRule.GenerateFilterName(); agg.Filter(filterName, f => f.Filter(fd => filterRule.Body.GenerateFilterDescription <T>()).Aggregations(aggFunc)); return(agg); }
public static string GetAggName <T, TK>(this Expression <Func <T, TK> > exp, AggType type) { var name = GetName(exp); return(type + name); }