/* 创建者:菜刀居士的博客 * 创建日期:2014年07月09号 */
namespace Net.CRM.FetchXml { using System; using Microsoft.Xrm.Sdk; using Microsoft.Xrm.Sdk.Query;
/// <summary> /// 使用FetchXml聚合查询,分组依据 /// </summary> public class FetchXmlExtension { /// <summary> /// 分组聚合 /// sql: select count(*),ownerid from account group by ownerid /// </summary> public void Group(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='name' alias='name_count' aggregate='count' /> <attribute name='ownerid' alias='ownerid' groupby='true' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 decimal value = ((Money)((AliasedValue)en["name_count"]).Value).Value; EntityReference ownerEr = (EntityReference)((AliasedValue)en["ownerid"]).Value; } }
/// <summary> /// 分组聚合,按年分组 /// </summary> public void GroupByYear(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_year = (Int32)((AliasedValue)en["year"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } }
/// <summary> /// 分组聚合,按季度分组 /// </summary> public void GroupByQuarter(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_quarter = (Int32)((AliasedValue)en["quarter"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } }
/// <summary> /// 分组聚合,按月分组 /// </summary> public void GroupByMonth(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='month' alias='month' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_month = (Int32)((AliasedValue)en["month"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } }
/// <summary> /// 分组聚合,按周分组 /// </summary> public void GroupByWeek(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='week' alias='week' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_week = (Int32)((AliasedValue)en["week"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } }
/// <summary> /// 分组聚合,按日分组 /// </summary> public void GroupByDay(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='day' alias='day' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_day = (Int32)((AliasedValue)en["day"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } }
/// <summary> /// 分组聚合,多个分组依据 /// </summary> public void GroupByYearAndQuarter(IOrganizationService service) { string fetchXml = @"<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='account'> <attribute name='accountid' alias='account_count' aggregate='count'/> <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' /> <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' /> </entity> </fetch>"; EntityCollection ec = service.RetrieveMultiple(new FetchExpression(fetchXml)); if (ec != null && ec.Entities.Count > 0) { Entity en = ec.Entities[0]; //获取结果 int value_year = (Int32)((AliasedValue)en["year"]).Value; int value_quarter = (Int32)((AliasedValue)en["quarter"]).Value; int value_count = (Int32)((AliasedValue)en["account_count"]).Value; decimal value_sum = ((Money)((AliasedValue)en["estimatedvalue_sum"]).Value).Value; } } } } (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|