public List <VideoItem> Contains(string column, string value)
{
using (connection.Lock())
{
return(connection.Query <VideoItem>($"SELECT * FROM {nameof(VideoItem)} WHERE {column} LIKE '%{value}%';", new string[] { }));
}
}
public List <T> Execute <T>(string query, params object[] args) where T : EntityBase
{
using (Connection.Lock())
{
return(Connection.Query <T>(query, args));
}
}
public List <AlbumItem> LoadAlbumsFromColumnValue(string column, string value)
{
using (connection.Lock())
{
return(connection.Query <AlbumItem>($"SELECT * FROM {nameof(AlbumItem)} WHERE {column} LIKE '%{value}%';", new string[] { }));
}
}
/// <summary>
/// 导出所有统计数据
/// </summary>
public JObject DumpStatisticsData()
{
var data = new JObject();
var statisticsList = new List <Type>()
{
typeof(GunDevelopTotal),
typeof(GunDevelopHeavyTotal),
typeof(EquipProduceTotal),
typeof(EquipDevelopTotal),
typeof(EquipDevelopHeavyTotal),
typeof(MissionBattleTotal),
typeof(MissionFinishTotal),
};
foreach (var type in statisticsList)
{
var mapping = _db.GetMapping(type);
var resultList = _db.Query(mapping, $"SELECT * FROM '{mapping.TableName}';");
var obj = new JObject();
// 这些统计类型都有time_id
var property = type.GetProperty("time_id");
foreach (var r in resultList)
{
var t = JsonConvert.SerializeObject(r);
obj[property.GetValue(r)] = t;
}
data[nameof(type)] = obj;
}
return(data);
}
public static Item GetItemSync(long id)
{
SQLiteConnectionWithLock con = _connection.GetConnection();
using (con.Lock())
{
return(con.Query <Item>("SELECT * FROM items WHERE _id=?", id).FirstOrDefault <Item>());
}
}
public List <Recipe> SearchRecipes(RecipeQuery query)
{
if (_isDisposed)
{
throw new ObjectDisposedException(nameof(SearchService));
}
var parameters = new List <object>();
var filters = new List <string>();
if (query.CategoryId != null)
{
filters.Add("(RecipeTextField.Type = 0 AND RecipeTextField.Value = ?)");
parameters.Add(query.CategoryId.Value.ToString());
}
if (query.OnlyFavorite)
{
filters.Add("(RecipeTextField.Type = 1 AND RecipeTextField.Value LIKE '%Favorite%')");
}
if (!string.IsNullOrEmpty(query.SearchTerm))
{
var q = _parser.Parse(query.SearchTerm);
parameters.Add(q);
Mvx.Trace($"Query: {q}");
filters.Add("RecipeTextSearch MATCH ?");
}
var filtersSql = filters.Count > 0
? $"WHERE {string.Join("AND", filters)}"
: string.Empty;
var sql = $@"SELECT Recipe.Id, Recipe.Document,
MIN(CASE RecipeTextField.Type
WHEN 2 THEN 0
WHEN 0 THEN 1
WHEN 1 THEN 2
WHEN 3 THEN 4
WHEN 5 THEN 6
ELSE 7
END) AS Priority
FROM RecipeTextField
JOIN Recipe ON Recipe.Id = RecipeTextField.RecipeId
JOIN RecipeTextSearch ON RecipeTextSearch.docid = RecipeTextField.Id
{filtersSql}
GROUP BY Recipe.Id
ORDER BY Priority";
var recipes = _connection.Query <RecipeRow>(sql, parameters.ToArray());
return(recipes.Select(x => x.ToRecipe(this)).ToList());
}
public Task <List <T> > QueryAsync <T>(string sql, params object[] args)
where T : new()
{
if (sql == null)
{
throw new ArgumentNullException("sql");
}
if (args == null)
{
throw new ArgumentNullException("args");
}
return(Task.Factory.StartNew(() =>
{
SQLiteConnectionWithLock conn = GetConnection();
using (conn.Lock())
{
return conn.Query <T>(sql, args);
}
}, CancellationToken.None, _taskCreationOptions, _taskScheduler ?? TaskScheduler.Default));
}
public Task <List <T> > QueryAsync <T>(string sql, params object[] args)
where T : new()
{
if (sql == null)
{
throw new ArgumentNullException("sql");
}
if (args == null)
{
throw new ArgumentNullException("args");
}
return(_taskFactory.StartNew(() =>
{
SQLiteConnectionWithLock conn = GetConnection();
using (conn.Lock())
{
return conn.Query <T>(sql, args);
}
}));
}
