public virtual List <dynamic> Execute(AggregateExpression agg)
{
var queryResolver = _queryResolverFactory.Get(agg);
var sqlString = queryResolver.ToSqlString();
List <string> selectFields = new List <string>();
foreach (var a in agg.AggregateFields)
{
selectFields.Add(DataFieldExpressionHelper.GetAggregationExpression(a.AggregateType, a.AttributeName) + " AS " + a.AttributeName);
}
sqlString = string.Format("SELECT {0} FROM ({1}) a", string.Join(",", selectFields).ToLower(), sqlString);
return(_dataRepository.ExecuteQuery(sqlString, queryResolver.Parameters.Args.ToArray()));
}
public bool UpdateControlMap(EntityMap entityMap, IEnumerable <AttributeMap> attributeMaps, Guid sourceRecordId, Schema.Domain.Attribute refAttribute, IEnumerable <Schema.Domain.Attribute> sourceAttributeMeta, IEnumerable <Schema.Domain.Attribute> targetAttributeMeta, bool onDelete = false)
{
Sql s = Sql.Builder.Append("UPDATE [" + entityMap.SourceEnttiyName + "] SET ");
foreach (var ca in attributeMaps)
{
//查找所有目标单据，钩稽字段的总和
s.Append(string.Format("[{0}]=(SELECT SUM([{1}]) AS SumValue FROM [{2}] WHERE [{3}] = '{4}')"
, ca.RemainAttributeName, ca.TargetAttributeName, entityMap.TargetEnttiyName, refAttribute.Name, sourceRecordId));
s.Append(string.Format(",[{0}]=(CASE WHEN ([{1}]-(SELECT SUM([{2}]) AS SumValue FROM [{3}] WHERE [{4}] = '{5}'))<=0 THEN 1 ELSE 0 END)"
, ca.ClosedAttributeName, ca.SourceAttributeName, ca.TargetAttributeName, entityMap.TargetEnttiyName, refAttribute.Name, sourceRecordId));
if (!onDelete && entityMap.MapType == MapType.ForceControl)
{
var data = _repository.ExecuteQuery(string.Format("SELECT 1 FROM [{0}] a LEFT JOIN (SELECT SUM([{1}]) AS SumValue,[{3}] FROM [{2}] WHERE [{3}] = '{4}' GROUP BY [{3}]) b ON a.{0}id=b.[{3}] WHERE a.{0}id='{4}' AND b.SumValue>a.[{5}]"
, entityMap.SourceEnttiyName, ca.TargetAttributeName, entityMap.TargetEnttiyName, refAttribute.Name, sourceRecordId, ca.SourceAttributeName));
if (data.NotEmpty())
{
var attr = sourceAttributeMeta.First(n => n.AttributeId.Equals(ca.SourceAttributeId));
var attr2 = targetAttributeMeta.First(n => n.AttributeId.Equals(ca.TargetAttributeId));
_repository.RollBackTransaction();
throw new XmsException(string.Format("'{0}' " + _loc["entitymap_greaterthan_error"] + " '{1}'", attr2.LocalizedName, attr.LocalizedName));
}
}
}
s.Append(" WHERE " + entityMap.SourceEnttiyName + "id='" + sourceRecordId + "'");
_repository.Execute(s);
return(true);
}
public long QueryHandlingCount(Guid handlerId, Guid?entityid)
{
string s = @"select count(1) as Count
from WorkFlowProcess a
inner join WorkFlowInstance b on a.WorkFlowInstanceId = b.WorkFlowInstanceId
inner join Entity c on b.EntityId = c.EntityId
inner join SystemUser d on b.ApplicantId = d.SystemUserId
inner join WorkFlow e on b.WorkFlowId = e.WorkFlowId
where a.HandlerId=@0 and a.StateCode = 1
";
if (entityid.HasValue && !entityid.Value.Equals(Guid.Empty))
{
s += " and b.EntityId='" + entityid.Value + "'";
}
var dataRepository = new DataRepositoryBase <dynamic>(DbContext);
var result = dataRepository.ExecuteQuery(s, handlerId);
if (result.NotEmpty())
{
var value = (result[0] as IDictionary <string, object>).Values.First();
return(value != null?long.Parse(value.ToString()) : 0);
}
return(0);
}
public List <dynamic> Query(bool ignorePermissions = false, List <Schema.Domain.Attribute> noneReadFields = null)
{
var result = _repository.ExecuteQuery(ToSqlString(ignorePermissions: ignorePermissions, noneReadFields: noneReadFields), this.Parameters.Args.ToArray());
return(result);
}
/// <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()));
}
