public async Task SearchAsyncTest_血氣方剛_找出所有小於30歲的男性角色()
{
// Arrange
var parameter = new CharacterSearchParameter()
{
Sex = "M",
UpAge = 30.0f
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Age < 30.0f && x.Sex == "M");
}
public async Task SearchAsyncTest_人在江湖_找出所有笑傲江湖的角色()
{
// Arrange
var parameter = new CharacterSearchParameter()
{
Novels = new List <string>()
{
"笑傲江湖"
}
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Novel == "笑傲江湖");
}
public async Task SearchAsyncTest_無產階級_找出所有丐幫的角色()
{
// Arrange
var parameter = new CharacterSearchParameter()
{
Factions = new List <string>()
{
"丐幫"
}
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Faction == "丐幫");
}
public async Task SearchAsyncTest_大宋子民_找出所有宋代出生的角色()
{
// Arrange
var start = new DateTime(960, 02, 04);
var end = new DateTime(1279, 3, 19);
var parameter = new CharacterSearchParameter()
{
UpBirthday = end,
DownBirthday = start
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Birthday > start && x.Birthday < end);
}
public async Task SearchAsyncTest_中間選民_找出所有沒有門派的腳色()
{
// Arrange
var parameter = new CharacterSearchParameter()
{
Factions = new List <string>()
{
string.Empty
}
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Faction == string.Empty);
}
public async Task SearchAsyncTest_日月荏苒_找出倚天屠龍記明教_和_笑傲江湖日月神教的角色()
{
// Arrange
var nameList = new List <string>()
{
"郭靖", "黃蓉", "楊過"
};
var parameter = new CharacterSearchParameter()
{
Names = nameList
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => nameList.Contains(x.Name));
}
public async Task SearchAsyncTest_峨嵋耆老_找出所有峨嵋派大於60歲的角色()
{
// Arrange
var parameter = new CharacterSearchParameter()
{
Factions = new List <string>()
{
"峨嵋派"
},
DownAge = 50
};
var sut = this.GetSystemUnderTest();
// Act
var result = await sut.SearchAsync(parameter);
// Assert
result.Should().OnlyContain(x => x.Faction == "峨嵋派" && x.Age > 50);
}
public async Task <IEnumerable <Character> > SearchAsync(CharacterSearchParameter parameter)
{
var mustClauses = new List <QueryContainer>();
var shouldCluause = new List <QueryContainer>();
if (parameter.Names.HasValue())
{
mustClauses.Add(new TermsQuery
{
Field = Infer.Field <Character>(c => c.Name.Suffix("keyword")),
Terms = parameter.Names
});
}
if (parameter.LastName.HasValue())
{
mustClauses.Add(new MatchPhrasePrefixQuery
{
Field = Infer.Field <Character>(c => c.Name),
Query = parameter.LastName
});
}
if (parameter.Factions.HasValue())
{
mustClauses.Add(new TermsQuery
{
// IsVerbatim預設為false，在轉換為查詢的json時，
// 會忽略沒有用的條件，例如包含空字串的陣列
// 導致查詢錯誤
IsVerbatim = true,
Field = Infer.Field <Character>(c => c.Faction),
Terms = parameter.Factions
});
}
if (parameter.Novels.HasValue())
{
mustClauses.Add(new TermsQuery
{
Field = Infer.Field <Character>(c => c.Novel),
Terms = parameter.Novels
});
}
if (parameter.UpAge > 0)
{
mustClauses.Add(new NumericRangeQuery
{
Field = Infer.Field <Character>(c => c.Age),
LessThanOrEqualTo = parameter.UpAge
});
}
if (parameter.DownAge > 0)
{
mustClauses.Add(new NumericRangeQuery
{
Field = Infer.Field <Character>(c => c.Age),
GreaterThanOrEqualTo = parameter.DownAge
});
}
if (parameter.UpBirthday != null)
{
mustClauses.Add(new DateRangeQuery
{
Field = Infer.Field <Character>(c => c.Birthday),
LessThanOrEqualTo = parameter.UpBirthday
});
}
if (parameter.DownBirthday != null)
{
mustClauses.Add(new DateRangeQuery
{
Field = Infer.Field <Character>(c => c.Birthday),
GreaterThanOrEqualTo = parameter.DownBirthday
});
}
if (parameter.Sex.HasValue())
{
mustClauses.Add(new TermQuery()
{
Field = Infer.Field <Character>(c => c.Sex),
Value = parameter.Sex
});
}
if (parameter.Targets.HasValue())
{
foreach (var target in parameter.Targets)
{
var novelQuery = new TermQuery()
{
Field = Infer.Field <Character>(c => c.Novel),
Value = target.Novel
};
var factionQuery = new TermQuery()
{
Field = Infer.Field <Character>(c => c.Faction),
Value = target.Faction
};
shouldCluause.Add(novelQuery && factionQuery);
}
}
var searchRequest = new SearchRequest("character")
{
Size = 100,
Query = new BoolQuery
{
Must = mustClauses,
Should = shouldCluause
},
};
var response = await this._elasticClient.SearchAsync <Character>(searchRequest);
return(response.Documents);
}
public async Task <IEnumerable <Character> > SearchAsync(CharacterSearchParameter parameter)
{
var sql = @"
SELECT *
FROM Character WITH(NOLOCK)
WHERE 1= 1 ";
var dynamicParametert = new DynamicParameters();
if (parameter.Factions.HasValue())
{
sql += "AND Faction IN @Factions ";
dynamicParametert.Add("@Factions", parameter.Factions);
}
if (parameter.Names.HasValue())
{
sql += "AND Name IN @Name";
dynamicParametert.Add("@Name", parameter.Names);
}
if (parameter.LastName.HasValue())
{
sql += $"AND Name LIKE N'{parameter.LastName}%' ";
}
if (parameter.Novels.HasValue())
{
sql += "AND Novel IN @Novels ";
dynamicParametert.Add("@Novels", parameter.Novels);
}
if (parameter.UpBirthday != null)
{
// 參數若小於1753年，必須指定型別為Datetime2
sql += "AND Birthday < @UpBirthday ";
dynamicParametert.Add
(
name: "@UpBirthday",
parameter.UpBirthday,
dbType: System.Data.DbType.DateTime2
);
}
if (parameter.DownBirthday != null)
{
// 參數若小於1753年，必須指定型別為Datetime2
sql += "AND Birthday > @DownBirthday ";
dynamicParametert.Add
(
name: "@DownBirthday",
parameter.DownBirthday,
dbType: System.Data.DbType.DateTime2
);
}
if (parameter.UpAge > 0)
{
sql += "AND Age < @Age ";
dynamicParametert.Add("@Age", parameter.UpAge);
}
if (parameter.DownAge > 0)
{
sql += "AND Age > @Age ";
dynamicParametert.Add("@Age", parameter.DownAge);
}
if (parameter.Sex.HasValue())
{
sql += "AND Sex = @Sex ";
dynamicParametert.Add("@Sex", parameter.Sex);
}
if (parameter.Targets.HasValue())
{
int index = 0;
var conditonList = new List <string>();
foreach (var target in parameter.Targets)
{
// 轉為 (Novel= @Novel0 AND Faction = @Faction0)
var condition = $"(Novel = @Novel{index} AND " +
$" Faction = @Faction{index} )";
conditonList.Add(condition);
dynamicParametert.Add($"@Novel{index}", target.Novel);
dynamicParametert.Add($"@Faction{index}", target.Faction);
index += 1;
}
// 加上 AND (condtion1 OR condtion2 Or condtion3 ... )
var targetClause = string.Join("OR", conditonList);
sql += $"AND ({targetClause}) ";
}
using (var conn = this._connectionHelper.Character)
{
return(await conn.QueryAsync <Character>(sql, dynamicParametert));
}
}
