/// <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;
}
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);
}
