protected override IAggregateFluent <EmployeeView> ConvertToViewAggreagate(IAggregateFluent <Employee> mappings, IExpressionContext <Employee, EmployeeView> context)
{
var afState = @"{
""$addFields"": {
""has_user"": { ""$cond"": [ { ""$ne"": [""$user"", undefined ] }, true, false ] },
""username"": ""$user.username"",
""permission"": { ""$cond"": [ { ""$ne"": [""$user.permission"", undefined ] }, ""$user.permission"", 0 ] }
}
}";
var pjState = @"{
""user"": 0
}";
var unwind = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
return(mappings
.Lookup("user", "_id", "code", "user").Unwind("user", unwind)
.Lookup("facutly", "facutly_code", "_id", "facutly").Unwind("facutly", unwind)
.Lookup("level", "level_code", "_id", "level").Unwind("level", unwind)
.Lookup("major", "major_code", "_id", "major").Unwind("major", unwind)
.AppendStage <BsonDocument>(BsonDocument.Parse(afState))
.Project(BsonDocument.Parse(pjState))
.As <EmployeeView>()
.Match(context.GetPostExpression()));
}
protected override IAggregateFluent <EmployeeView> ConvertToViewAggreagate(IAggregateFluent <Employee> mappings, IExpressionContext <Employee, EmployeeView> context)
{
var afState = @"{
""$addFields"": {
""has_user"": { ""$cond"": [{ ""$ne"": [ ""$user"", undefined ] }, true, false ] },
""username"": ""$user.username"",
""uermission"": ""$user.uermission""
}
}";
var pjState = @"{
""User"": 0
}";
var unwind = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
// TODO: Mapping them JobPosition + Department + JobTitle
return(mappings
.Lookup("user", "user_code", "code", "user").Unwind("user", unwind)
.Lookup("job_position", "job_position_code", "code", "job_position").Unwind("job_position", unwind)
.Lookup("department", "job_position.department_code", "code", "department").Unwind("department", unwind)
.Lookup("job_title", "job_position.job_title_code", "code", "job_title").Unwind("job_title", unwind)
.AppendStage <BsonDocument>(BsonDocument.Parse(afState))
.Project(BsonDocument.Parse(pjState))
.As <EmployeeView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <MaterialSubgroupView> ConvertToViewAggreagate(IAggregateFluent <MaterialSubgroup> mappings, IExpressionContext <MaterialSubgroup, MaterialSubgroupView> context)
{
var unwind = new AggregateUnwindOptions <MaterialSubgroupView> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("material_group", "material_group_code", "code", "material_group")
.Unwind("material_group", unwind).As <MaterialSubgroupView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <CustomerView> ConvertToViewAggreagate(IAggregateFluent <Customer> mappings, IExpressionContext <Customer, CustomerView> context)
{
var unwind = new AggregateUnwindOptions <CustomerView> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("employee", "employee_code", "code", "employee").Unwind("employee", unwind)
.As <CustomerView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <MaterialReceivingVoucherView> ConvertToViewAggreagate(IAggregateFluent <MaterialReceivingVoucher> mappings, IExpressionContext <MaterialReceivingVoucher, MaterialReceivingVoucherView> context)
{
var unwind = new AggregateUnwindOptions <MaterialReceivingVoucher> {
PreserveNullAndEmptyArrays = true
};
var group = @"
{
_id: {
_id : ""$_id"",
code : ""$Code"",
def_code : ""$DefCode"",
name : ""$name"",
value_to_search : ""$value_to_search"",
is_published : ""$is_published"",
created_date : ""$created_date"",
last_modified : ""$last_modified"",
delivery_code: ""$delivery_code"",
supplier_code: ""$supplier_code"",
category : ""$category"",
description: ""$description"",
document_date : ""$document_date""
},
infos: { ""$addToSet"": ""$infos"" }
}";
var projection = @"
{
_id : ""$_id._id"",
code : ""$_id.Code"",
def_code : ""$_id.DefCode"",
name : ""$_id.name"",
value_to_search : ""$_id.value_to_search"",
is_published : ""$_id.is_published"",
created_date : ""$_id.created_date"",
last_modified : ""$_id.last_modified"",
delivery_code: ""$_id.delivery_code"",
supplier_code: ""$_id.supplier_code"",
category : ""$_id.category"",
description: ""$_id.description"",
document_date : ""$_id.document_date""
infos: ""infos""
}";
return(mappings
.Unwind("infos", unwind)
.Lookup("material", "infos.code", "code", "infos.store.material")
.Unwind("infos.store.material", unwind)
.Lookup("material_group", "infos.store.material.material_group_code", "code", "infos.store.material_group")
.Unwind("infos.store.material_group", unwind)
.Lookup("material_subgroup", "infos.store.material.material_subgroup_code", "code", "infos.store.material_subgroup")
.Unwind("infos.store.material_subgroup", unwind)
.Group(BsonDocument.Parse(group))
.Project(BsonDocument.Parse(projection))
.Lookup("supplier", "supplier_code", "code", "supplier")
.Unwind("supplier", unwind)
.As <MaterialReceivingVoucherView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <DepartmentView> ConvertToViewAggreagate(IAggregateFluent <Department> mappings, IExpressionContext <Department, DepartmentView> context)
{
var unwind = new AggregateUnwindOptions <DepartmentView> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("department", "parent_code", "code", "parent")
.Unwind("parent", unwind)
.As <DepartmentView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <StudentView> ConvertToViewAggreagate(IAggregateFluent <Student> mappings, IExpressionContext <Student, StudentView> context)
{
var unwind = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("center", "center_code", "_id", "center").Unwind("center", unwind)
.Lookup("major", "major_code", "_id", "major").Unwind("major", unwind)
.Lookup("classroom", "classroom_code", "_id", "classroom").Unwind("classroom", unwind)
.As <StudentView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <EmployeeCourseView> ConvertToViewAggreagate(IAggregateFluent <EmployeeCourse> mappings, IExpressionContext <EmployeeCourse, EmployeeCourseView> context)
{
var unwind = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("employee", "employee_code", "_id", "employee").Unwind("employee", unwind)
.Lookup("course", "course_code", "_id", "course").Unwind("course", unwind)
.Lookup("facutly", "facutly_code", "_id", "facutly").Unwind("facutly", unwind)
.Lookup("major", "major_code", "_id", "major").Unwind("major", unwind)
.As <EmployeeCourseView>().Match(context.GetPostExpression()));
}
protected override IAggregateFluent <WarehouseInventoryView> ConvertToViewAggreagate(IAggregateFluent <WarehouseInventory> mappings, IExpressionContext <WarehouseInventory, WarehouseInventoryView> context)
{
var unwind = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
return(mappings.Lookup("warehouse", "warehouse_code", "code", "warehouse").Unwind("warehouse", unwind)
.Lookup("material", "material_code", "code", "material").Unwind("material", unwind)
.Lookup("material_group", "material_group_code", "code", "material_group").Unwind("material_group", unwind)
.Lookup("material_subgroup", "material_sub_group_code", "code", "material_sub_group").Unwind("material_sub_group", unwind)
.Lookup("material_group_type", "material_group_type_code", "code", "material_group_type").Unwind("material_group_type", unwind)
.Lookup("unit", "unit_code", "code", "unit").Unwind("unit", unwind)
.As <WarehouseInventoryView>().Match(context.GetPostExpression()));
}
public List <BsonDocument> GetAll()
{
AggregateUnwindOptions <Role> unwindOptions = new AggregateUnwindOptions <Role>()
{
PreserveNullAndEmptyArrays = true
};
var data = _Users.Aggregate()
.Lookup("Role", "RoleId", "_id", "Role")
.Unwind("Role", unwindOptions)
.Lookup("Permission", "Role.PermissionId", "_id", "Role.Permission")
.ToList();
return(data);
}
public BsonDocument GetUserDetail(string id)
{
AggregateUnwindOptions <Role> unwindOptions = new AggregateUnwindOptions <Role>()
{
PreserveNullAndEmptyArrays = true
};
var data = _Users.Aggregate()
.Match(x => x.Id == id)
.Lookup("Role", "RoleId", "_id", "Role")
.Unwind("Role", unwindOptions)
.Lookup("Permission", "Role.PermissionId", "_id", "Role.Permission")
.FirstOrDefault();
return(data);
}
public static IAggregateFluent <TSource> ApplyLookup <TSource>(this IAggregateFluent <TSource> lookupWrapper, Dictionary <string, EntityLookup> lookups)
{
var aggregateUnwindOptions = new AggregateUnwindOptions <TSource>
{
PreserveNullAndEmptyArrays = true
};
foreach (var lookup in lookups)
{
lookups.Remove(lookup.Key);
return(ApplyLookup(lookupWrapper
.Lookup(lookup.Value.From, lookup.Value.LocalField, lookup.Value.ForeignField, lookup.Value.As)
.Unwind($"{lookup.Value.As}", aggregateUnwindOptions)
.As <TSource>(), lookups));
}
return(lookupWrapper);
}
static void Main(string[] args)
{
//used v 2.4.3 of C# driver and v 3.4.1 of the db engine for this example
var client = new MongoClient();
IMongoDatabase db = client.GetDatabase("agg_example");
var collectionName = "points";
db.DropCollection(collectionName);
IMongoCollection <BsonDocument> collection = db.GetCollection <BsonDocument>(collectionName);
IEnumerable <BsonDocument> data = GetDummyData().Select(d => d.ToBsonDocument());
collection.InsertMany(data);
//some seasons to filter by - note transformation to zero based
var seasons = new[] { 6, 7 };
//This is the query body:
var seasonIndex = seasons.Select(i => i - 1);
//This shall remove all un-necessary seasons from aggregation pipeline
var bsonFilter = new BsonDocument {
new BsonElement("Season", new BsonDocument("$in", new BsonArray(seasonIndex)))
};
var groupBy = new BsonDocument // think of this as a grouping with an anonyous object declaration
{
new BsonElement("_id", "$_id"), //This denotes the key by which to group - in this case the player's id
new BsonElement("playerSum", new BsonDocument("$sum", "$SeasonalPoints")), //We aggregate the player's points
new BsonElement("player", new BsonDocument("$first", "$$CURRENT")), // preserve player reference for projection stage
};
var sort = Builders <BsonDocument> .Sort.Descending(doc => doc["playerSum"]);
var unwindOptions = new AggregateUnwindOptions <BsonDocument>
{
IncludeArrayIndex = new StringFieldDefinition <BsonDocument>("Season")
};
var projection = Builders <BsonDocument> .Projection.Expression((doc => doc["player"]));
List <BsonValue> sorted = collection
.Aggregate()
.Unwind(x => x["SeasonalPoints"], unwindOptions)
.Match(bsonFilter)
.Group(groupBy)
.Sort(sort)
.Project(projection)
.ToList();
}
public List <BsonDocument> GetUserWithFilter(string searchKey)
{
AggregateUnwindOptions <Role> unwindOptions = new AggregateUnwindOptions <Role>()
{
PreserveNullAndEmptyArrays = true
};
var filter = Builder.Or(
Builder.Where(x => x.UserName.ToLower().Contains(searchKey)),
Builder.Where(x => x.Name.ToLower().Contains(searchKey)),
Builder.Where(x => x.Email.ToLower().Contains(searchKey)),
Builder.Where(x => x.Address.ToLower().Contains(searchKey))
);
var data = _Users.Aggregate()
.Match(filter)
.Lookup("Role", "RoleId", "_id", "Role")
.Unwind("Role", unwindOptions)
.Lookup("Permission", "Role.PermissionId", "_id", "Role.Permission")
.ToList();
return(data);
}
public async Task <CustomerCheckListRequestModel> GetCustomerCheckListAsync(string id)
{
try
{
var filter = Builders <Customer> .Filter.Eq(c => c.Id, id);
var unwindOption = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
var projectMapping = new BsonDocument()
{
{ "_id", 0 },
{ "MobileSchemaProductCode", "$Loan.Product.ProductCodeMC" },
{ "MobileTemResidence", new BsonDocument("$toInt", "$IsTheSameResidentAddress") },
{ "LoanAmountAfterInsurrance", "$Loan.Amount" },
{ "ShopCode", "$Loan.SignAddress" },
{ "CustomerName", "$Personal.Name" },
{ "CitizenId", "$Personal.IdCard" },
{ "LoanTenor", "$Loan.Term" },
{ "HasInsurance", "$Loan.BuyInsurance" },
{ "CompanyTaxNumber", "$Working.TaxId" },
};
BsonDocument document = await _customer
.Aggregate()
.Match(filter)
.Lookup("Product", "Loan.ProductId", "ProductId", "Loan.Product")
.Unwind("Loan.Product", unwindOption)
.Project(projectMapping)
.FirstOrDefaultAsync();
var result = BsonSerializer.Deserialize <CustomerCheckListRequestModel>(document);
return(result);
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
throw;
}
}
protected override IAggregateFluent <MaterialDeliveryVoucherView> ConvertToViewAggreagate(IAggregateFluent <MaterialDeliveryVoucher> mappings, IExpressionContext <MaterialDeliveryVoucher, MaterialDeliveryVoucherView> context)
{
var unwind = new AggregateUnwindOptions <MaterialDeliveryVoucher> {
PreserveNullAndEmptyArrays = true
};
var group = @"
{
_id: {
_id : ""$_id"",
code : ""$Code"",
def_code : ""$DefCode"",
name : ""$name"",
value_to_search : ""$value_to_search"",
is_published : ""$is_published"",
created_date : ""$created_date"",
last_modified : ""$last_modified"",
delivery_type: ""$delivery_type"",
other_type: ""$other_type"",
customer_code : ""$customer_code"",
other_customer_code : ""$other_customer_code"",
supplier_code : ""$supplier_code"",
invoice_code : ""$invoice_code"",
reason : ""$reason"",
description: ""$description"",
document_date : ""$document_date""
},
infos: { ""$addToSet"": ""$infos"" }
}";
var projection = @"
{
_id: ""$_id._id"",
code: ""$_id.code"",
def_code: ""$_id.def_code"",
name : ""$_id.name"",
delivery_type: ""$_id.delivery_type"",
other_type: ""$_id.other_type"",
description: ""$_id.description"",
value_to_search: ""$_id.value_to_search"",
is_published: ""$_id.is_published"",
created_date: ""$_id.created_date"",
last_modified: ""$_id.last_modified"",
customer_code : ""$_id.customer_code"",
other_customer_code : ""$_id.other_customer_code"",
supplier_code : ""$_id.supplier_code"",
invoice_code : ""$_id.invoice_code"",
reason : ""$_id.reason"",
document_date: ""$_id.document_date"",
infos: ""infos""
}";
var projection_store = @"
{
""delivery"": 0,
""infos.store.material.receiving"": 0,
""infos.store.material.material_group"": 0,
""infos.store.material.material_subgroup"": 0
}";
var append_receivingmap = @"
{
$addFields: {
""receiving_map"": { $concat: [ ""$infos.receiving_code"", ""_"", ""$infos.code"" ] }
}
}";
var append_store = @"
{
$addFields: {
""infos.store.material"": ""$delivery"",
""infos.store.material_group"": ""$delivery.material_group"",
""infos.store.material_subgroup"": ""$delivery.material_subgroup"",
""infos.store.inventory"": ""$delivery.inventory"",
""infos.store.price"": ""$delivery.Price"",
""infos.store.receiving"": ""$delivery.receiving"",
""infos.store.last"": ""$Infos.quantity""
}
}";
return(mappings
.Unwind("infos", unwind)
.AppendStage <BsonDocument>(BsonDocument.Parse(append_receivingmap))
.Lookup("material_for_delivery_view", "receiving_map", "receiving_map", "delivery")
.Unwind("delivery", unwind)
.AppendStage <BsonDocument>(BsonDocument.Parse(append_store))
.Project(BsonDocument.Parse(projection_store))
.Group(BsonDocument.Parse(group))
.Project(BsonDocument.Parse(projection))
.Lookup("customer", "customer_code", "code", "customer")
.Unwind("customer", unwind)
.Lookup("customer", "other_customer_code", "code", "other_customer")
.Unwind("other_customer", unwind)
.Lookup("supplier", "supplier_code", "code", "supplier")
.Unwind("supplier", unwind)
.As <MaterialDeliveryVoucherView>().Match(context.GetPostExpression()));
}
private IAggregateFluent <ClothKind> GetAggregationFluentForAggregation(bool includeDeleted = false,
FilterDefinition <ClothKind> filter = null, FilterDefinition <Order> filterOrder = null)
{
var instancesProjectDef = @"{
ClothKind : '$Instances.ClothKind',
Amount: '$Instances.Amount'
}";
PipelineDefinition <Order, BsonDocument> innerpipeline = PipelineDefinition <Order, BsonDocument>
.Create(new IPipelineStageDefinition[]
{
PipelineStageDefinitionBuilder.Match(filterOrder ?? Builders <Order> .Filter.Empty),
PipelineStageDefinitionBuilder.Unwind <Order>("Instances"),
PipelineStageDefinitionBuilder.Project <BsonDocument>(instancesProjectDef),
PipelineStageDefinitionBuilder.Match <BsonDocument>(new BsonDocument("$expr",
new BsonDocument("$eq",
new BsonArray
{
"$ClothKind",
"$$kindid"
}))),
});
var groupDef = @"{
_id: ""$_id"",
Name :{ $first: ""$Name""},
MeasureKind: { $first:""$MeasureKind""},
Price :{ $first: ""$Price""},
Parent:{$first:""$Parent""},
Count: {
$sum:""$ClothInstances.Amount""
},
SumPrice:{
$sum: {$multiply : [""$Price"", ""$ClothInstances.Amount""]}
}
}";
var projectDef = @"{
Name: '$Name',
MeasureKind: '$MeasureKind',
Price : '$Price',
Parent : '$Parent',
Count : '$Count',
SumPrice: '$SumPrice',
ChildrenCount : {$size: '$Children'}
}";
var aggregateUnwindOptions = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
return(base.GetAggregationFluent(includeDeleted, filter)
.Lookup <Order, BsonDocument, IList <BsonDocument>, ClothKind>(
this.Collection.Database.GetCollection <Order>("orders"),
new BsonDocument("kindid", "$_id"), innerpipeline, "ClothInstances")
.Unwind("ClothInstances", aggregateUnwindOptions)
.Group(groupDef)
.Lookup("clothkinds", "_id", "Parent", "Children")
.Project(projectDef)
.As <ClothKind>()
.SortBy(x => x.Id));
}
private async Task SendRecvAsync()
{
AggregateUnwindOptions <ReceiveTransferBlock> unwindOptions = new AggregateUnwindOptions <ReceiveTransferBlock>()
{
PreserveNullAndEmptyArrays = true
};
var allSend = _dbContext.Blocks.OfType <SendTransferBlock>().AsQueryable();
var allRecv = _dbContext.Blocks.OfType <ReceiveTransferBlock>().AsQueryable();
var q = _dbContext.Blocks.OfType <SendTransferBlock>()
.Aggregate()
.Lookup(NetworkId + "_blocks", "Hash", "SourceHash", "asSource")
.Project(x => new
{
SendHash = x["Hash"],
DstAccountId = x["DestinationAccountId"],
RecvHash = x["asSource"]
})
.ToList();
//Console.WriteLine($"Total {q.Count()} tx.");
var dict = await FindAllBalanceAsync();
foreach (var item in q)
{
if (!item.RecvHash.AsBsonArray.Values.Any())
{
//Console.WriteLine($"{item}");
var snd = await FindBlockByHashAsync(item.SendHash.AsString) as SendTransferBlock;
var prev = await FindBlockByHashAsync(snd.PreviousHash) as TransactionBlock;
var chgs = snd.GetBalanceChanges(prev);
if (chgs.Changes.ContainsKey("LYR"))
{
if (chgs.Changes["LYR"] == 0.000001m)
{
Console.WriteLine($"Unrecv: {chgs.Changes["LYR"]}");
}
if (dict.ContainsKey(item.DstAccountId.AsString))
{
var dualb = dict[item.DstAccountId.AsString];
dualb.UnRecv += chgs.Changes["LYR"];
dict[item.DstAccountId.AsString] = dualb;
}
else
{
dict.Add(item.DstAccountId.AsString, new DualBalance {
Normal = 0m, UnRecv = chgs.Changes["LYR"]
});
}
}
}
//var h1 = item.Elements.First(x => x.Name == "Hash").Value;
//var src = item.Elements.First(x => x.Name == "asSource");
//var vls = src.Value.AsBsonArray.Values;
//if(vls.Any())
//{
// var h2 = src.Value.AsBsonArray.Values.First().AsBsonDocument.FirstOrDefault(x => x.Name == "Hash");
// //if (.IsBsonNull)
// Console.WriteLine($"{h1} => {h2}");
//}
//else
//{
// Console.WriteLine($"{h1} => ");
//}
}
var total = 0m;
foreach (var kvp in dict.OrderByDescending(x => x.Value.Total))
{
total += kvp.Value.Normal + kvp.Value.UnRecv;
//Console.WriteLine($"{kvp.Key}: {Math.Round(kvp.Value.Normal + kvp.Value.UnRecv)}, Total {Math.Round(total)}");
}
var latest = await FindLatestBlockAsync();
var db = dbCtx.Database;
var list = new TotalBalance
{
AllAccounts = dict.OrderByDescending(x => x.Value.Total)
.ToDictionary(kvp => kvp.Key, kvp => kvp.Value)
};
var coll = db.GetCollection <TotalBalance>("TotalBalance");
if (coll.FindAll().Any())
{
coll.DeleteAll();
}
coll.Insert(list);
db.Commit();
}
public async Task <string> Aggregate(string name, int age)
{
AggregateUnwindOptions <UserBehavior> options = new AggregateUnwindOptions <UserBehavior>();
var aaa = new BsonDocument[]
{
new BsonDocument("$group",
new BsonDocument
{
{ "_id",
new BsonDocument("userId", "$UserId") },
{ "Behaviors",
new BsonDocument("$push", "$Behaviors") }
}),
new BsonDocument("$unwind",
new BsonDocument("path", "$Behaviors")),
new BsonDocument("$unwind",
new BsonDocument("path", "$Behaviors")),
new BsonDocument("$unwind",
new BsonDocument("path", "$Behaviors.Tags")),
new BsonDocument("$group",
new BsonDocument
{
{ "_id",
new BsonDocument
{
{ "userid", "$_id.userId" },
{ "Tag", "$Behaviors.Tags" }
} },
{ "dianzan",
new BsonDocument("$sum", "$Behaviors.Seek") },
{ "dianzan1",
new BsonDocument("$sum", "$Behaviors.Seek") },
{ "dianzan2",
new BsonDocument("$sum", "$Behaviors.Seek") },
{ "dianzan3",
new BsonDocument("$sum", "$Behaviors.Seek") }
}),
new BsonDocument("$group",
new BsonDocument
{
{ "_id",
new BsonDocument("userid", "$_id.userid") },
{ "summer",
new BsonDocument("$push",
new BsonDocument
{
{ "Tag", "$_id.Tag" },
{ "dianzan", "$dianzan" }
}) }
})
};
var pipelinedef = PipelineDefinition <UserBehaviorGroup, xxx> .Create(aaa);
var pipeline = _userBehaviorGroupRepository.Collection.Aggregate(pipelinedef, new AggregateOptions {
AllowDiskUse = true
});
//.Unwind<UserBehavior, UserBehaviorTemp>(o => o.ContentTag).Group(o => new { Channel = o.Channel, Type = o.Type, ContentTag = o.ContentTag, UserId = o.UserId }, g => new { key = g.Key, Count = g.Count() })
//.Project(x => new xx { Channel = x.key.Channel, Type = x.key.Type, ContentTag = x.key.ContentTag, UserId = x.key.UserId, Count = x.Count })
//.Limit(10000).Out("test1");
var result = await pipeline.ToListAsync();
return("OK");
}
public async Task <BandejaESDTOR> ObtenerBandeja(string usuario)
{
BsonArray subpipeline = new BsonArray();
subpipeline.Add(
new BsonDocument("$match", new BsonDocument(
"$expr", new BsonDocument(
"$eq", new BsonArray {
"$_id", new BsonDocument("$toObjectId", "$$expedienteId")
})
)
));
var lookup = new BsonDocument("$lookup",
new BsonDocument("from", "expedientes")
.Add("let", new BsonDocument("expedienteId", "$bandejasalida.idexpediente"))
.Add("pipeline", subpipeline)
.Add("as", "bandejadocumento")
);
/* LookUp para los documentos */
BsonArray subpipeline2 = new BsonArray();
subpipeline2.Add(
new BsonDocument("$match", new BsonDocument(
"$expr", new BsonDocument(
"$eq", new BsonArray {
"$_id", new BsonDocument("$toObjectId", "$$documentoId")
})
)
));
var lookup2 = new BsonDocument("$lookup",
new BsonDocument("from", "documentos")
.Add("let", new BsonDocument("documentoId", "$bandejadocumento.documentos.iddocumento"))
.Add("pipeline", subpipeline2)
.Add("as", "documentosobj")
);
/* Group para agrupar los documentos en un array */
var group = new BsonDocument("_id", "$bandejadocumento._id")
.Add("cliente", new BsonDocument("$first", "$bandejadocumento.cliente"))
.Add("tipo", new BsonDocument("$first", "$bandejadocumento.tipo"))
.Add("documentosobj", new BsonDocument("$push", "$documentosobj"))
.Add("bandejasalida", new BsonDocument("$first", "$bandejasalida"))
.Add("bandejaentrada", new BsonDocument("$first", "$bandejaentrada"))
.Add("idbandeja", new BsonDocument("$first", "$_id"));
/* Proyección para devolver los datos que se necesitan */
var condicionalFiltro = new BsonDocument("$eq", new BsonArray {
"$$item._id", new BsonDocument("$toObjectId", "$bandejasalida.iddocumento")
});
var filtro = new BsonDocument("$filter", new BsonDocument("input", "$documentosobj")
.Add("as", "item")
.Add("cond", condicionalFiltro));
var declararVariable = new BsonDocument("$let", new BsonDocument("vars", new BsonDocument("documento", filtro))
.Add("in", new BsonDocument("$arrayElemAt", new BsonArray {
"$$documento", 0
})));
var project = new BsonDocument("$project",
new BsonDocument("documento", declararVariable)
.Add("documentosobj", "$documentosobj")
.Add("tipo", "$tipo")
.Add("_id", "$_id")
.Add("cliente", "$cliente")
.Add("bandejaentrada", 1)
.Add("idbandeja", 1));
/* Proyección para crear el atributo de expediente salida*/
var project1 = new BsonDocument("$project",
new BsonDocument("_id", 0)
.Add("idbandeja", 1)
.Add("bandejaentrada", 1)
.Add("expedientesalida",
new BsonDocument("idexpediente", "$_id")
.Add("cliente", "$cliente")
.Add("tipo", "$tipo")
.Add("documentosobj", "$documentosobj")
.Add("documento", "$documento")));
/* Group para unificar todos los expedientes de la bandeja de salida*/
var group1 = new BsonDocument("_id", "$idbandeja")
.Add("bandejaentrada", new BsonDocument("$first", "$bandejaentrada"))
.Add("expedientesalida", new BsonDocument("$push", "$expedientesalida"));
/* Lookup para los expedientes de la bandeja de entrada*/
BsonArray subpipelineE = new BsonArray();
subpipelineE.Add(
new BsonDocument("$match", new BsonDocument(
"$expr", new BsonDocument(
"$eq", new BsonArray {
"$_id", new BsonDocument("$toObjectId", "$$expedienteId")
})
)
));
var lookupe = new BsonDocument("$lookup",
new BsonDocument("from", "expedientes")
.Add("let", new BsonDocument("expedienteId", "$bandejaentrada.idexpediente"))
.Add("pipeline", subpipelineE)
.Add("as", "bandejadocumento")
);
/* Group para la bandeja de entrada y agrupos los elementos en un array*/
var groupe = new BsonDocument("_id", "$bandejadocumento._id")
.Add("cliente", new BsonDocument("$first", "$bandejadocumento.cliente"))
.Add("tipo", new BsonDocument("$first", "$bandejadocumento.tipo"))
.Add("documentosobj", new BsonDocument("$push", "$documentosobj"))
.Add("expedientesalida", new BsonDocument("$first", "$expedientesalida"))
.Add("bandejaentrada", new BsonDocument("$first", "$bandejaentrada"))
.Add("idbandeja", new BsonDocument("$first", "$_id"));
/* Proyección para encontrar el documento de entrada */
var condicionalFiltroE = new BsonDocument("$eq", new BsonArray {
"$$item._id", new BsonDocument("$toObjectId", "$bandejaentrada.iddocumento")
});
var filtroE = new BsonDocument("$filter", new BsonDocument("input", "$documentosobj")
.Add("as", "item")
.Add("cond", condicionalFiltroE));
var declararVariableE = new BsonDocument("$let", new BsonDocument("vars", new BsonDocument("documento", filtroE))
.Add("in", new BsonDocument("$arrayElemAt", new BsonArray {
"$$documento", 0
})));
var projecte = new BsonDocument("$project",
new BsonDocument("documento", declararVariableE)
.Add("documentosobj", "$documentosobj")
.Add("tipo", "$tipo")
.Add("_id", "$_id")
.Add("cliente", "$cliente")
.Add("bandejaentrada", 1)
.Add("idbandeja", 1)
.Add("expedientesalida", 1));
/* Proyección para crear el expedienteentrada*/
var project1e = new BsonDocument("$project",
new BsonDocument("_id", 0)
.Add("idbandeja", 1)
.Add("expedientesalida", 1)
.Add("expedienteentrada",
new BsonDocument("idexpediente", "$_id")
.Add("cliente", "$cliente")
.Add("tipo", "$tipo")
.Add("documentosobj", "$documentosobj")
.Add("documento", "$documento")));
/* Group final para la unión de los expedientes */
var groupfinal = new BsonDocument("_id", "$idbandeja")
.Add("expedienteentrada", new BsonDocument("$push", "$expedienteentrada"))
.Add("expedientesalida", new BsonDocument("$first", "$expedientesalida"));
var filtroUsuario = Builders <Bandeja> .Filter.Eq("usuario", usuario);
var opcionesunwind1 = new AggregateUnwindOptions <BandejaDTODocumentoExpediente>();
opcionesunwind1.PreserveNullAndEmptyArrays = true;
var opcionesunwind2 = new AggregateUnwindOptions <BandejaExpedienteDTO>();
opcionesunwind2.PreserveNullAndEmptyArrays = true;
var opcionesunwind3 = new AggregateUnwindOptions <BandejaExpDocUndDTO>();
opcionesunwind3.PreserveNullAndEmptyArrays = true;
var opcionesunwind4 = new AggregateUnwindOptions <BandejaDTOE>();
opcionesunwind4.PreserveNullAndEmptyArrays = true;
var opcionesunwind5 = new AggregateUnwindOptions <BandejaDTOEDocumentoExpediente>();
opcionesunwind5.PreserveNullAndEmptyArrays = true;
var opcionesunwind6 = new AggregateUnwindOptions <BandejaExpedienteDTOE>();
opcionesunwind6.PreserveNullAndEmptyArrays = true;
var opcionesunwind7 = new AggregateUnwindOptions <BandejaExpDocUndDTOE>();
opcionesunwind7.PreserveNullAndEmptyArrays = true;
var opcionesunwind0 = new AggregateUnwindOptions <BandejaDTO>();
opcionesunwind0.PreserveNullAndEmptyArrays = true;
BandejaESDTOR listabandejas = new BandejaESDTOR();
listabandejas = await _bandejas.Aggregate()
.Match(filtroUsuario)
.Unwind <Bandeja, BandejaDTO>(b => b.bandejasalida, opcionesunwind0)
.AppendStage <BandejaDTODocumento>(lookup)
.Unwind <BandejaDTODocumento, BandejaDTODocumentoExpediente>(b => b.bandejadocumento, opcionesunwind1)
.Unwind <BandejaDTODocumentoExpediente, BandejaExpedienteDTO>(be => be.bandejadocumento.documentos, opcionesunwind2)
.AppendStage <BandejaExpDocDTO>(lookup2)
.Unwind <BandejaExpDocDTO, BandejaExpDocUndDTO>(bed => bed.documentosobj, opcionesunwind3)
.Group <BandejaExpDocGroupDTO>(group)
.AppendStage <BandejaDTOR>(project)
.AppendStage <BandejaRPDTO>(project1)
.Group <BandejaRV1DTO>(group1)
.Unwind <BandejaRV1DTO, BandejaDTOE>(be => be.bandejaentrada, opcionesunwind4)
.AppendStage <BandejaDTOEDocumento>(lookupe)
.Unwind <BandejaDTOEDocumento, BandejaDTOEDocumentoExpediente>(b => b.bandejadocumento, opcionesunwind5)
.Unwind <BandejaDTOEDocumentoExpediente, BandejaExpedienteDTOE>(bee => bee.bandejadocumento.documentos, opcionesunwind6)
.AppendStage <BandejaExpDocDTOE>(lookup2)
.Unwind <BandejaExpDocDTOE, BandejaExpDocUndDTOE>(bed => bed.documentosobj, opcionesunwind7)
.Group <BandejaExpDocGroupDTOE>(groupe)
.AppendStage <BandejaESDTO>(projecte)
.AppendStage <BandejaESDTOP>(project1e)
.Group <BandejaESDTOR>(groupfinal)
.FirstAsync();
return(listabandejas);
}
/// <summary>
/// Метод, получающий данные по аналитике филиалов
/// </summary>
/// <returns>Ридонли лист с результатами для каждого филиала</returns>
public IReadOnlyList <SubsidiaryAggregationResult> AggregateSubsidiaries(FilterDefinition <Order> filter = null)
{
var projectDef = @"{
Price: {$sum: '$Orders.Price'},
Orders : '$Orders',
Signature: {$concat :
[
{$toString:'$_id'}, ' ',
'$Name', ' ',
'$Street', ' ', '$House'
]}
}";
var groupDef = @"
{
_id: '$_id',
Signature: {$first : '$Signature'},
Price: {$first :'$Price'},
Count : {
$sum : {
$cond:[
{$in: ['$ClothKind.MeasureKind', [0,2]]},
'$Orders.Instances.Amount',
0]
}
},
UnCountableCount : {
$sum : {
$cond:[
{$eq: ['$ClothKind.MeasureKind', 1]},
'$Orders.Instances.Amount',
0]
}
},
}";
//Вложенный pipeline для pipeline-let lookup-а заказов в аналитике филиалов
var pipeline = PipelineDefinition <Order, Order> .Create(new[]
{
PipelineStageDefinitionBuilder.Match <Order>(new BsonDocument("$expr",
new BsonDocument("$or",
new BsonArray
{
new BsonDocument("$eq",
new BsonArray
{
"$InSubsidiary",
"$$kindid"
}),
new BsonDocument("$eq",
new BsonArray
{
"$OutSubsidiary",
"$$kindid"
})
}))),
PipelineStageDefinitionBuilder.Match(filter ?? Builders <Order> .Filter.Empty)
}
);
var aggregateUnwindOptions = new AggregateUnwindOptions <BsonDocument> {
PreserveNullAndEmptyArrays = true
};
var aggregation =
this.GetAggregationFluent()
.Lookup <Order, Order, List <Order>, Subsidiary>(
this.Collection.Database.GetCollection <Order>("orders"), new BsonDocument("kindid", "$_id"), pipeline,
"Orders")
.Project(projectDef)
.Unwind("Orders", aggregateUnwindOptions)
.Unwind("Orders.Instances", aggregateUnwindOptions)
.Lookup("clothkinds", "Orders.Instances.ClothKind", "_id", "ClothKind")
.Unwind("ClothKind", aggregateUnwindOptions)
.Group(groupDef).As <SubsidiaryAggregationResult>().SortBy(x => x.SubsidiaryId).ToList();
return(aggregation);
}
public IAggregateFluent <TNewResult> Unwind <TNewResult>(FieldDefinition <BsonDocument> field, AggregateUnwindOptions <TNewResult> options = null)
{
return(BlockProvider.Unwind <TNewResult>(field, options));
}
