public ArtistListViewModel(TypeListEnum type, string filter = "All")
{
ArtistStatuses = DataAccess.GetStatusList();
ArtistScores = DataAccess.GetScoreList();
TypeList = type;
ArtistList = DataAccess.GetArtistList(TypeList, filter);
FirstLetterFilterList = Enumerable.Range('A', 26).Select(x => (char)x).Select(c => c.ToString()).ToList();
FirstLetterFilterList.Insert(0, "All");
FirstLetterFilterList.Add("0-9");
FirstLetterFilterList.Add("*");
ArtistSelected = new ArtistModel();
UpdateArtistUpToDateCommand = new RelayCommand(UpdateArtistUpToDate, param => canExecute);
FirstLetterFilterCommand = new RelayCommand(FirstLetterFilter, param => canExecute);
}
public static ObservableCollection <ArtistModel> GetArtistList(TypeListEnum type, string filter)
{
ObservableCollection <ArtistModel> artistList = new ObservableCollection <ArtistModel>();
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand();
selectCommand.Connection = db;
selectCommand.CommandText = "SELECT a.id, a.name, a.alias, a.upToDate, a.date, st.id, st.label, st.color, sc.id, sc.label FROM Artist a LEFT JOIN Status st ON st.id = a.statusId LEFT JOIN Score sc ON sc.id = a.scoreId ";
switch (type)
{
case TypeListEnum.Listened:
selectCommand.CommandText += "WHERE a.statusId <> 6 ";
break;
case TypeListEnum.Unlistened:
selectCommand.CommandText += "WHERE a.statusId = 6 ";
break;
case TypeListEnum.Searched:
selectCommand.CommandText += "WHERE 0 = 0 ";
break;
}
if (!string.IsNullOrEmpty(filter) && filter != "All")
{
if (filter == "0-9")
{
selectCommand.CommandText += "AND a.name GLOB '[0-9]*' ";
}
else if (filter == "*")
{
selectCommand.CommandText += "AND a.name NOT GLOB '[0-9]*' AND a.name NOT GLOB '[A-Z]*' ";
}
else
{
selectCommand.CommandText += "AND a.name LIKE '" + filter + "%' ";
}
}
switch (type)
{
case TypeListEnum.Listened:
selectCommand.CommandText += "ORDER BY a.name ";
break;
case TypeListEnum.Unlistened:
selectCommand.CommandText += "ORDER BY a.date ";
break;
}
selectCommand.CommandText += " ";
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
artistList.Add(new ArtistModel()
{
ArtistId = query.GetInt32(0),
ArtistName = query.GetString(1),
ArtistAlias = query.GetString(2),
ArtistUpToDate = query.GetBoolean(3),
ArtistDate = query.GetDateTime(4),
ArtistStatus = new ArtistStatusModel()
{
StatusId = query.GetInt32(5), StatusLabel = query.GetString(6), StatusColor = (Brush) new BrushConverter().ConvertFromString(query.GetString(7))
},
ArtistScore = new ArtistScoreModel()
{
ScoreId = query.GetInt32(8), ScoreLabel = query.GetString(9)
}
});
}
db.Close();
}
return(artistList);
}
