protected override async void OnAppearing()
{
if (_model.Loading)
{
_authors = await AudioLibrary.Instance.GetAuthors(1, PAGE_SIZE);
if (_authors == null)
{
UserDialogs.Instance.HideLoading();
return;
}
var sorted = _authors.Authors
.OrderBy(o => o.Name)
.GroupBy(g => g.NameSort)
.Select(s => new Grouping <string, AuthorModel>(s.Key, s));
//create a new collection of groups
listView.SetBinding(ListView.ItemsSourceProperty, new Binding("."));
listView.BindingContext = _model.Items;
listView.IsGroupingEnabled = true;
listView.GroupDisplayBinding = new Binding("Key");
listView.GroupShortNameBinding = new Binding("Key");
listView.BeginRefresh();
sorted.ToList().ForEach(item => _model.Items.Add(item));
listView.EndRefresh();
_model.Loading = false;
}
}
//public async Task<TitleResult> SearchBooksByTitle(string text, int index, int count)
//{
//}
public async Task <AuthorsResult> GetAuthors(int index, int count)
{
AuthorsResult result = new AuthorsResult
{
Authors = new List <AuthorModel>()
};
using (SqlConnection connection = new SqlConnection(
_connectionString))
{
SqlCommand commandCount = new SqlCommand(@"SELECT count(*) total
FROM SI_autores SIA
WHERE SIA.id IN (
SELECT DISTINCT(LHA.id_autor) id
FROM LH_audioteca LHA
INNER JOIN SI_autores SIA ON SIA.id = LHA.id_autor
INNER JOIN LH_formatosdisponibles LHF on LHF.id_audioteca = LHA.id
WHERE LHF.id_formato=4 AND LHF.activo='True' AND LHA.activo='True')",
connection);
connection.Open();
using (SqlDataReader reader = await commandCount.ExecuteReaderAsync())
{
while (reader.Read())
{
result.Total = Convert.ToInt32(reader[0]);
}
}
SqlCommand commandAuthors = new SqlCommand($@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY nombre) AS idx, SIA.id, SIA.nombre
FROM SI_autores SIA
WHERE SIA.id IN (
SELECT DISTINCT(LHA.id_autor) id
FROM LH_audioteca LHA
INNER JOIN SI_autores SIA ON SIA.id = LHA.id_autor
INNER JOIN LH_formatosdisponibles LHF on LHF.id_audioteca = LHA.id
WHERE LHF.id_formato=4 AND LHF.activo='True' AND LHA.activo='True')
) as tbl
WHERE idx BETWEEN ${index} AND ${index + count - 1}",
connection);
using (SqlDataReader reader = await commandAuthors.ExecuteReaderAsync())
{
while (reader.Read())
{
result.Authors.Add(new AuthorModel
{
Id = reader[1].ToString().Trim(),
Name = reader[2].ToString().Trim()
});
}
}
}
return(result);
}
public async Task <AuthorsResult> GetAuthors(int index, int count)
{
if (_authors == null || _expireAuthors < DateTime.Now)
{
_authors = await this.Get <AuthorsResult>("authors", null, index, count, null);
_expireAuthors = DateTime.Now.AddSeconds(ExpirySecs);
}
return(_authors);
}