public bool Update(Entity entity, IQueryResolver queryResolver, bool ignorePermissions = false) { entity = UnwrapAttributeValue(entity); string primaryKey = entity.IdName; entity.Remove(primaryKey); List <string> sets = new List <string>(); List <object> values = new List <object>(); //query string //query.ColumnSet.Columns.Clear(); //query.ColumnSet.Columns.Add(primaryKey); var queryString = queryResolver.ToSqlString(false, ignorePermissions); values.AddRange(queryResolver.Parameters.Args); StringBuilder sql = new StringBuilder(); sql.AppendLine(string.Format("UPDATE [{0}]", entity.Name)); sql.AppendLine("SET"); int i = values.Count; foreach (var k in entity.Keys) { values.Add(entity[k]); sets.Add(string.Format("[{0}]={1}", k, "@" + i)); i++; } sql.AppendLine(string.Join(",\n", sets)); sql.AppendLine(string.Format("WHERE [{0}] IN({1})", primaryKey, queryString)); var flag = _repository.Execute(sql.ToString(), values.ToArray()) > 0; return(flag); }
private string GetChartDataSqlString(ReportDescriptor report, IQueryResolver queryTranslator) { //drillthrough, aggregation var columnField = report.CustomReport.Chart.ColumnAxis.Field; var nameField = GetFieldValueName(queryTranslator, columnField); if (!columnField.IsCaseInsensitiveEqual(nameField)) { nameField = "," + nameField; } else { nameField = string.Empty; } columnField = GetGroupingName(report, queryTranslator, columnField); var sql = "SELECT "; var orderby = ""; if (report.CustomReport.Filter != null) { sql += " TOP " + report.CustomReport.Filter.Value; orderby = " ORDER BY " + report.CustomReport.Filter.Field + (report.CustomReport.Filter.Operator == FilterOperator.TopN ? " DESC" : ""); } sql += columnField + " AS " + report.CustomReport.Chart.ColumnAxis.Field + nameField; foreach (var item in report.CustomReport.Chart.ValueAxes) { var column = report.CustomReport.Columns.Find(n => n.Field.IsCaseInsensitiveEqual(item.Field)); sql += string.Format(",{0} AS {1}", DataFieldExpressionHelper.GetAggregationExpression(column.SummaryValue.Value, item.Field), item.Field); } sql += string.Format(" FROM ({0}) a GROUP BY {1} {2}", queryTranslator.ToSqlString(), columnField + nameField, orderby); return(sql); }
public DataTable GetData(ReportDescriptor report, IQueryResolver queryTranslator, FilterExpression filter = null) { if (filter != null) { //设置过滤条件中特殊字段的名称 //foreach (var item in filter.Conditions) //{ // item.AttributeName = GetFieldValueName(queryTranslator, item.AttributeName); //} report.CustomReport.Query.Criteria = filter;//.AddFilter(filter); } var sql = queryTranslator.ToSqlString(); //获取数据 var ds = new DataVisitor(DbContext).ExecuteQueryDataSet(sql, queryTranslator.Parameters.Args.ToArray()); return(ds.Tables[0]); }
/// <summary> /// 获取图表需要的数据 /// </summary> /// <param name="chartData"></param> /// <param name="query"></param> /// <param name="queryResolver"></param> /// <param name="attributes"></param> /// <returns></returns> public List <dynamic> GetChartDataSource(ChartDataDescriptor chartData, QueryExpression query, IQueryResolver queryResolver) { query.Orders.Clear(); //清除视图原有的排序 query.ColumnSet.AllColumns = false; //清除视图原有的列 query.ColumnSet.Columns.Clear(); var invalidLinkEntities = new List <LinkEntity>(); //不需要的关联实体 if (query.LinkEntities.NotEmpty()) { foreach (var le in query.LinkEntities) { if (le.LinkCriteria == null || (le.LinkCriteria.Filters.IsEmpty() && le.LinkCriteria.Conditions.IsEmpty())) { invalidLinkEntities.Add(le); } le.Columns.AllColumns = false; le.Columns.Columns.Clear(); } } if (invalidLinkEntities.NotEmpty()) { foreach (var item in invalidLinkEntities) { query.LinkEntities.Remove(item); } } //统计类型:汇总、平均值、最大值、最小值 //X轴、分类 //group by 日期、状态等,select count(@field),avg(@field),max(@field),min(@field) from (@sqlstring) group by @field //系列(多条)、数据 //获取生成的sqlstring,select count(@field),avg(@field),max(@field),min(@field) from (@sqlstring) var attrbuteNames = chartData.Fetch.Select(f => f.Attribute); var attributes = _attributeRepository.Query(x => x.EntityName == query.EntityName && x.Name.In(attrbuteNames)).ToList(); var selectString = new List <string>(); var groupbyString = new List <string>(); var orderbyString = new List <string>(); var topCount = -1; foreach (var item in chartData.Fetch) { var attr = attributes.Find(n => n.Name.IsCaseInsensitiveEqual(item.Attribute)); var name = item.Attribute; if (item.Type == ChartItemType.Series) { if (item.TopCount.HasValue && item.TopCount.Value > 0) { if (item.TopCount.Value > topCount) { topCount = item.TopCount.Value; } orderbyString.Add(item.Attribute + (Enum.GetName(typeof(AggregateType), item.Aggregate)) + (item.TopDirection == TopDirectionType.Desc ? " desc" : "")); } selectString.Add(string.Format("{0} AS {1}", DataFieldExpressionHelper.GetAggregationExpression(item.Aggregate, name), item.Attribute + (Enum.GetName(typeof(AggregateType), item.Aggregate)))); } else if (item.Type == ChartItemType.Category) { var groupField = string.Empty; if (attr.TypeIsDateTime() && item.DateGrouping.HasValue)//date group { groupField = DataFieldExpressionHelper.GetDateGroupingExpression(item.DateGrouping.Value, item.Attribute); } else { groupField = name; } if (!groupbyString.Exists(n => n.IsCaseInsensitiveEqual(groupField))) { groupbyString.Add(groupField); } selectString.Add(groupField + " AS " + name); if (attr.TypeIsPrimaryKey()) { var primaryField = _attributeRepository.Find(n => n.EntityName == query.EntityName && n.IsPrimaryField == true); query.ColumnSet.AddColumn(primaryField.Name); selectString.Add(string.Format("{0} AS {1}name", primaryField.Name, item.Attribute)); groupbyString.Add(primaryField.Name); attributes.Add(primaryField); } else if (attr.TypeIsLookUp() || attr.TypeIsOwner() || attr.TypeIsCustomer()) { query.ColumnSet.AddColumn(name + "name"); groupbyString.Add(name + "name"); selectString.Add(name + "name"); } } if (!query.ColumnSet.Columns.Exists(n => n.IsCaseInsensitiveEqual(name))) { query.ColumnSet.AddColumn(name); } if (attr.TypeIsPickList() || attr.TypeIsStatus()) { attr.OptionSet = new OptionSet(); attr.OptionSet.Items = _optionSetDetailRepository.Query(x => x.OptionSetId == attr.OptionSetId.Value).ToList(); } else if (attr.TypeIsState() || attr.TypeIsBit()) { attr.PickLists = _stringMapRepository.Query(f => f.AttributeId == attr.AttributeId)?.ToList(); } attributes.Add(attr); //如果不指定前后X项,则默认按日期的正序排序 if (topCount <= 0 && attr.TypeIsDateTime()) { orderbyString.Add(attr.Name + " ASC"); } } queryResolver.AttributeList = attributes; var sqlstring = queryResolver.ToSqlString(); sqlstring = string.Format("SELECT {2} {0} FROM (" + sqlstring + ") a GROUP BY {1} {3}" , string.Join(",", selectString) , string.Join(",", groupbyString) , topCount > 0 ? " TOP " + topCount : string.Empty , (orderbyString.NotEmpty() ? " ORDER BY " + string.Join(",", orderbyString) : string.Empty) ); return(_dataRepository.ExecuteQuery(sqlstring, queryResolver.Parameters.Args.ToArray())); }