public void PipelineDefinitionCanBeBuild(string groupByArguments)
{
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = GroupDefinitionBuilder.Build <BsonDocument>(groupByArguments), Throws.Nothing);
Assert.That(stage, Is.Not.Null);
}
//关联查询查询数据
public List <BsonDocument> Get_Aggregate(string coll, List <string> key, List <string> value, List <string> coll_name, List <string> localField, List <string> foreignField, List <string> ASS)
{
try
{
var oll = database.GetCollection <BsonDocument>(coll);
IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>();
string match = "{$match:{";
for (int i = 0; i < key.Count; i++)
{
match += "'" + key[i] + "':'" + value[i] + "',";
}
match += "}}";
PipelineStageDefinition <BsonDocument, BsonDocument> stage1 =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(match);
stages.Add(stage1);
for (int i = 0; i < localField.Count; i++)
{
string pipelineJson = "{$lookup:{from: '" + coll_name[i] + "',localField: '" +
localField[i] + "',foreignField: '" + foreignField[i] + "',as: '" +
ASS[i] + "'}}";
PipelineStageDefinition <BsonDocument, BsonDocument> stage =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(pipelineJson);
stages.Add(stage);
}
PipelineDefinition <BsonDocument, BsonDocument> pipeline = new PipelineStagePipelineDefinition <BsonDocument, BsonDocument>(stages);
return(oll.Aggregate(pipeline).ToList());
}
catch
{
return(null);
}
}
/// <summary>
/// 从歌单中删除某些歌曲
/// </summary>
/// <param name="userId"></param>
/// <param name="listId"></param>
/// <param name="sids">Sid，(int)DataSource_(string)Id格式</param>
/// <returns></returns>
public async Task <Result> RemoveMusicFromList(long userId, string listId, List <string> sids)
{
if (!await ValidPlaylistOwner(userId, listId))
{
return(Result.FailResult("您不是歌单的拥有者哦", 403));
}
//聚合查询元素所在索引位置
IPipelineStageDefinition stage0 = new JsonPipelineStageDefinition <UserMusic, UserMusic>
("{\"$match\":{\"_id\" :" + userId.ToString() + "}}");
IPipelineStageDefinition stage1 = new JsonPipelineStageDefinition <UserMusic, IndexInfo>
("{ \"$project\": { \"index\": { \"$indexOfArray\":[\"$playlists._id\",ObjectId(\"" + listId + "\")] },\"_id\":0}}");
var pipeline = new PipelineStagePipelineDefinition <UserMusic, IndexInfo>
(new List <IPipelineStageDefinition>()
{
stage0, stage1
});
var indexResult = (await this.userMusicCollection.AggregateAsync(pipeline)).FirstOrDefault();
if (indexResult == null)
{
return(Result.FailResult("操作失败，目标不存在！", 404));
}
var update = Builders <UserMusic> .Update.PullFilter <MusicInfo>(
$"playlists.{indexResult.Index}.tracks",
Builders <MusicInfo> .Filter.In(mic => mic.Sid, sids));
var updateResult = await this.userMusicCollection.UpdateManyAsync(
Builders <UserMusic> .Filter.Eq(usr => usr.UserId, userId), update);
return(updateResult.MatchedCount > 0
? Result.SuccessReuslt("操作成功") : Result.FailResult("操作失败，目标不存在！", 404));
}
/// <summary>
/// 获取用户创建的歌单,不包含音乐列表
/// </summary>
/// <param name="userId"></param>
/// <param name="limit"></param>
/// <param name="offset"></param>
/// <returns></returns>
public async Task <PageResult <UserPlaylist> > GetUserPlaylist(long userId, int limit, int offset)
{
//查询用户歌单，排除不需要的字段
var projection = Builders <UserMusic> .Projection.ElemMatch(usr => usr.UserPlaylists,
Builders <UserPlaylist> .Filter.Eq(list => list.UserId, userId))
.Slice(usr => usr.UserPlaylists, offset, limit)
.Exclude(usr => usr.Likes)
.Exclude(usr => usr.SubscribePlaylists)
.Exclude("playlists.tracks");
Task <UserMusic> list_task = this.userMusicCollection.Find(item => item.UserId == userId)
.Project <UserMusic>(projection).FirstOrDefaultAsync();
//统计总数
var aggregateOption = new AggregateOptions();
//管道件一，输入类型UserMusic，筛选出对应_id的用户
IPipelineStageDefinition stage0 = new JsonPipelineStageDefinition <UserMusic, UserMusic>
("{\"$match\":{\"_id\" :" + userId.ToString() + "}}");
//管道件二，通过$size聚合得到数组长度，输出类型为内部类CountInfo
IPipelineStageDefinition stage1 = new JsonPipelineStageDefinition <UserMusic, CountInfo>
("{ \"$project\": { \"count\": { \"$size\":\"$playlists\" },\"_id\":0}}");
//生成管道
var pipeline = new PipelineStagePipelineDefinition <UserMusic, CountInfo>
(new List <IPipelineStageDefinition>()
{
stage0, stage1
});
var count_task = this.userMusicCollection.AggregateAsync(pipeline);
List <UserPlaylist> playlists = (await list_task)?.UserPlaylists;
var countInfo = (await count_task).FirstOrDefault();
return(playlists == null ? PageResult <UserPlaylist> .FailResult("用户不存在！", 404)
: PageResult <UserPlaylist> .SuccessResult(playlists, countInfo != null?countInfo.Count : 0));
}
public void PipelineDefinitionCanBeBuild()
{
var orderByArguments = "index ASC, timestamp DESC";
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = SortDefinitionBuilder.Build <BsonDocument>(orderByArguments), Throws.Nothing);
Assert.That(stage, Is.Not.Null);
}
public void PipelineDefinitionCanBeBuild()
{
var limitArguments = "31";
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = LimitDefinitionBuilder.Build <BsonDocument>(limitArguments), Throws.Nothing);
Assert.That(stage, Is.Not.Null);
}
public void LikeClauseCanHandleRegexCharacters()
{
var whereArguments = "Data.BatchId LIKE '%|%'";
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = FilterDefinitionBuilder.Build <BsonDocument>(whereArguments), Throws.Nothing);
Assert.That(stage.Json.Contains(".*\\\\|.*"));
}
public void PipelineDefinitionAsExpected(string whereArguments, string expected)
{
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = FilterDefinitionBuilder.Build <BsonDocument>(whereArguments), Throws.Nothing);
Assert.That(stage, Is.Not.Null);
Assert.That(stage.Json, Is.EqualTo(expected));
}
public void PipelineDefinitionCanBeBuild()
{
var selectArguments = "id, test.subtest, product.price.tax as tax";
JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null;
Assert.That(() => stage = ProjectionDefinitionBuilder.Build <BsonDocument>(selectArguments), Throws.Nothing);
Assert.That(stage, Is.Not.Null);
Assert.That(stage.Json, Is.EqualTo("{ $project : { \"id\" : true, \"test_subtest\" : \"$test.subtest\", \"tax\" : \"$product.price.tax\" } }"));
}
public IAsyncCursor <T> _GroupBy <T>(string collName, string[] strStag) //where T : class, new()
{
IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>();
AggregateOptions aggs = new AggregateOptions();
for (int i = 0; i < strStag.Length; i++)
{
PipelineStageDefinition <T, T> stage = new JsonPipelineStageDefinition <T, T>(strStag[i]);
stages.Add(stage);
}
PipelineDefinition <T, T> pipeline = new PipelineStagePipelineDefinition <T, T>(stages);
//排序查询
try
{
var client = _database.GetCollection <T>(collName);
return(client.Aggregate(pipeline));
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// 根据管道获取数据[异步]
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public async Task <BaseResultModel <T> > GetListAggregateAsync(ManagePostAttPageSearch search)
{
Dictionary <string, string> searchDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SearchDic.ToString());
Dictionary <string, string> sortDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SortDic.ToString());
PipeLineBsonHelper pipeLineBsonHelper = new PipeLineBsonHelper();
List <string> pipeLinelist = new List <string>();
#region 过滤（条件查询）
pipeLineBsonHelper.AddMatchList(searchDic, pipeLinelist);
#endregion
#region 分组(用 $last排序，后续改为属性映射独立方法 )
if (string.IsNullOrEmpty(search.GroupField))
{
throw new ArgumentNullException($"分组参数{nameof(search.GroupField)}不能为空！");
}
string group = "{'" + search.GroupField + "':'$" + search.GroupField + "'}";
string pipelineGroup = @"{$group:
{
_id:" + group + @",
'id':{ '$first':'$_id'},
'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'},
'ProjectName':{ '$last' :'$ProjectName'},
'ProjectGuid':{ '$last' :'$ProjectGuid'},
'Company':{ '$last':'$Company'},
'OrganizationCode':{ '$last':'$OrganizationCode'},
'CorpType':{ '$last':'$CorpType'},
'SegmentAddressArea':{ '$last':'$SegmentAddressArea'},
'AttendanceTime':{ '$last':'$AttendanceTime'},
'AttendanceId':{ '$last':'$AttendanceId'},
'PersonGUID':{ '$last':'$PersonGUID'},
'PersonName':{ '$last':'$PersonName'},
'IdCard':{ '$last':'$IdCard'},
'PostType':{ '$last':'$PostType'},
'ImageBuffer':{ '$last':'$ImageBuffer'},
'SupervisionDepartment':{ '$last':'$SupervisionDepartment'},
'SupervisionDepartmentGUID':{ '$last':'$SupervisionDepartmentGUID'},
'CreateTime':{ '$last':'$CreateTime'}
}
}";
pipeLinelist.Add(pipelineGroup);
#endregion
#region 总数（根据条件查询总数）
string pipelineCount = "{$count:'total'}";
pipeLinelist.Add(pipelineCount);
IList <IPipelineStageDefinition> stageList = new List <IPipelineStageDefinition>();
foreach (string item in pipeLinelist)
{
PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item);
stageList.Add(stageGroup);
}
BaseResultModel <T> baseResultModel = new BaseResultModel <T>();
BsonDocument bsonDocument = await mongodbBsonService.GetAggregateAsync(stageList);
if (null != bsonDocument)
{
baseResultModel = BsonSerializer.Deserialize <BaseResultModel <T> >(bsonDocument);
}
else
{
baseResultModel.total = 0;
baseResultModel.rows = new List <T>();
baseResultModel.success = false;
return(baseResultModel);
}
#endregion
//移除总数
pipeLinelist.Clear();
stageList.RemoveAt(stageList.Count - 1);
#region 排序
pipeLineBsonHelper.AddSortList(sortDic, pipeLinelist);
#endregion
#region 分页
string pipelineSkip = "{$skip:" + ((search.PageIndex - 1) * search.PageSize) + "}";
string pipelineLimit = "{$limit:" + search.PageSize + "}";
pipeLinelist.Add(pipelineSkip);
pipeLinelist.Add(pipelineLimit);
#endregion
#region 射
string pipelineProject = "{$project:{'_id':0,'id':0}}";
pipeLinelist.Add(pipelineProject);
#endregion
foreach (string item in pipeLinelist)
{
PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item);
stageList.Add(stageGroup);
}
//baseResultModel.rows = await mongodbBsonService.GetListAggregateAsync(stageList);
List <T> list = new List <T>();
foreach (var item in await mongodbBsonService.GetListAggregateAsync(stageList))
{
var d = BsonSerializer.Deserialize <BsonDocument>(item);
list.Add(BsonSerializer.Deserialize <T>(item));
}
baseResultModel.rows = list;
baseResultModel.success = true;
return(baseResultModel);
}
/// <summary>
/// 根据施工许可证号码、身份证号码、日期分组统计考勤人数
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public async Task <IEnumerable <ManagePostAttStatistics> > GetListStatisticsAsync(ManagePostAttPageSearch search)
{
if (null == search)
{
throw new ArgumentNullException($"查询条件{nameof(search.SearchDic)}不能为空！");
}
Dictionary <string, string> searchDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SearchDic.ToString());
PipeLineBsonHelper pipeLineBsonHelper = new PipeLineBsonHelper();
List <string> pipeLinelist = new List <string>();
#region 过滤（条件查询）
pipeLineBsonHelper.AddMatchList(searchDic, pipeLinelist);
#endregion
#region 分组(用 $last排序，后续改为属性映射独立方法 )
string group = "{'ConstructPermitNum':'$ConstructPermitNum'";
group += ",'IdCard':'$IdCard'";
group += ",'day':{'$substr':[{'$add':['$AttendanceTime', 28800000]},0,10]}";
group += "}";
string pipelineGroup1 = @"{$group:
{
_id:" + group + @",
'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'},
'ProjectName':{ '$last' :'$ProjectName'},
'ProjectGuid':{ '$last' :'$ProjectGuid'},
'AttendanceTime':{ '$last':'$AttendanceTime'}
}
}";
pipeLinelist.Add(pipelineGroup1);
string pipelineGroup2 = @"{$group:
{
_id:" + group + @",
'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'},
'ProjectName':{ '$last' :'$ProjectName'},
'ProjectGuid':{ '$last' :'$ProjectGuid'},
'AttendanceTime':{ '$last':'$AttendanceTime'},
'AttPersonCount':{'$sum':1}
}
}";
pipeLinelist.Add(pipelineGroup2);
#endregion
#region 排序
if (null != search.SortDic)
{
Dictionary <string, string> sortDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SortDic.ToString());
pipeLineBsonHelper.AddSortList(sortDic, pipeLinelist);
}
#endregion
#region 射
string pipelineProject = "{$project:{'_id':0}}";
pipeLinelist.Add(pipelineProject);
#endregion
IList <IPipelineStageDefinition> stageList = new List <IPipelineStageDefinition>();
foreach (string item in pipeLinelist)
{
PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item);
stageList.Add(stageGroup);
}
//baseResultModel.rows = await mongodbBsonService.GetListAggregateAsync(stageList);
List <ManagePostAttStatistics> list = new List <ManagePostAttStatistics>();
foreach (var item in await mongodbBsonService.GetListAggregateAsync(stageList))
{
var d = BsonSerializer.Deserialize <BsonDocument>(item);
list.Add(BsonSerializer.Deserialize <ManagePostAttStatistics>(item));
}
return(list);
}
//按照指定的元属性值查找
public IActionResult query_zc_1(IFormCollection f)
{
var ysx_name = f["ysx_name"];
var ysx_value = f["ysx_value"];
try
{
//查询管理者维度
M_Db_Operate m_Db_Operate = new M_Db_Operate();
var collection1 = m_Db_Operate.database.GetCollection <Dimension_Information_Model>("Dimension_Information");
List <string> key1 = new List <string>();
key1.Add("TYPE");
List <string> value1 = new List <string>();
value1.Add("管理者维度");
var Result = m_Db_Operate.Inquire_Data(collection1, key1, value1);
List <string> name = new List <string>();
for (var i = 0; i < Result.Count; i++)
{
name.Add(Result[i].NAME);
}
ViewData["glz_list"] = name;
//在这里查询数据库并整合数据
M_Db_Operate m_Db_ = new M_Db_Operate();
var coll = m_Db_.database.GetCollection <YSX_Model>("Meta_attribute");
var res = m_Db_.Inquire_Data(coll, new List <string> {
"Name"
}, new List <string> {
ysx_name.ToString()
})[0];
//var collection = m_Db_Operate.database.GetCollection<SJ_ZC_Model>("ZC_data");
//List<string> key = new List<string>();
//List<string> value = new List<string>();
//key.Add("ZC_SX.mp");
//key.Add("ZC_SX.V");
//value.Add(res.ID);
//value.Add(ysx_value.ToString());
//List <BsonDocument> result = m_Db_Operate.Get_Aggregate(
// "ZC_data",
// key,
// value,
// new List<string>() { "Meta_attribute" },
// new List<string>() { "ZC_SX.mp" },
// new List<string>() { "ID" },
// new List<string>() { "ZC_SXN" }
// );
#region 聚合查询方法
IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>();
string match = "{$match:{ 'ZC_SX':{ 'V':'" + ysx_value.ToString() + "','mp':'" + res.ID + "'}}}";
string lookup = "{$lookup:{from: 'Meta_attribute',localField: 'ZC_SX.mp',foreignField: 'ID', as: 'ZC_SXN'}}";
string skip = "{$skip: 0}";
string limit = "{$limit:30}";
PipelineStageDefinition <BsonDocument, BsonDocument> stage1 =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(match);
PipelineStageDefinition <BsonDocument, BsonDocument> stage2 =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(lookup);
PipelineStageDefinition <BsonDocument, BsonDocument> stage3 =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(skip);
PipelineStageDefinition <BsonDocument, BsonDocument> stage4 =
new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(limit);
stages.Add(stage1);
stages.Add(stage2);
stages.Add(stage3);
stages.Add(stage4);
PipelineDefinition <BsonDocument, BsonDocument> pipeline = new PipelineStagePipelineDefinition <BsonDocument, BsonDocument>(stages);
var oll = m_Db_Operate.database.GetCollection <BsonDocument>("ZC_data");
List <BsonDocument> result = oll.Aggregate(pipeline).ToList();
List <List <string> > ZC_datas = new List <List <string> >();
for (var i = 0; i < result.Count; i++)
{
List <string> ZC_data = new List <string>();
string kz_sx = "";
int j = 0;
while (true)
{
try
{
if (result[i]["ZC_SXN"][j]["Dimension_dic"]["资产类别维度"].ToString().Contains("通用集"))
{
string uu = result[i]["ZC_SXN"][j]["Name"].ToString() + ":";
int k = 0;
while (true)
{
try
{
if (result[i]["ZC_SXN"][j]["ID"].ToString() == result[i]["ZC_SX"][k]["mp"].ToString())
{
uu += result[i]["ZC_SX"][k]["V"].ToString();
break;
}
k++;
}
catch
{
;
}
}
ZC_data.Add(uu);
}
else
{
string uu = result[i]["ZC_SXN"][j]["Name"].ToString() + ":";
int k = 0;
while (true)
{
try
{
if (result[i]["ZC_SXN"][j]["ID"].ToString() == result[i]["ZC_SX"][k]["mp"].ToString())
{
uu += result[i]["ZC_SX"][k]["V"].ToString();
break;
}
k++;
}
catch
{
;
}
}
ZC_data.Add(uu);
}
}
catch
{
break;
}
j++;
}
ZC_data.Add(kz_sx);
ZC_datas.Add(ZC_data);
}
#endregion
#region 其他方法
//var collection = m_Db_Operate.database.GetCollection<BsonDocument>("ZC_data");
//FilterDefinitionBuilder<BsonDocument> builderFilter = Builders<BsonDocument>.Filter;
//var filter = builderFilter.ElemMatch("ZC_SX", builderFilter.And(builderFilter.Eq("V", ysx_value.ToString()), builderFilter.Eq("mp", res.ID)));
//var result = collection.Find(filter).ToList();
//List <List<string>> ZC_datas = new List<List<string>>();
//FilterDefinitionBuilder<YSX_Model> builderFilter1 = Builders<YSX_Model>.Filter;
//FilterDefinition<YSX_Model> filt = builderFilter1.And(builderFilter1.Ne("ID", ""));
//var res2 = coll.Find(filt).ToList();
//for (var i = 0; i < result.Count; i++)
//{
// List<string> ZC_data = new List<string>();
// string kz_sx = "";
// int j = 0;
// while (true)
// {
// try
// {
// YSX_Model q = res2.TakeWhile(x => x.ID == result[i]["ZC_SX"][j]["mp"].ToString()).First();
// if (q.Dimension_dic["资产类别维度"].ToString().Contains("通用集"))
// {
// ZC_data.Add(q.Name.ToString() + ":" + result[i]["ZC_SX"][j]["V"].ToString());
// }
// else
// {
// kz_sx += q.Name.ToString() + ":" + result[i]["ZC_SX"][j]["V"].ToString();
// }
// }
// catch
// {
// break;
// }
// j++;
// }
// ZC_data.Add(kz_sx);
// ZC_datas.Add(ZC_data);
//}
#endregion
ViewData["ZC_datas"] = ZC_datas;
ViewData["sx_name"] = sx_name;
}
catch (InvalidCastException e)
{
ViewData["e"] = e.ToString();
return(View("query_ERROR"));
}
ViewData["wd_type"] = null;
ViewData["wd_xx"] = null;
ViewData["zc_name"] = null;
ViewData["ysx_name"] = ysx_name;
ViewData["ysx_value"] = ysx_value;
return(View("query_zc"));
}
