/// <summary>
/// CreateInitPlaylist method.
/// according to the areas that the user choose, create
/// a playlist that is built from all songs that are from
/// there areas. execute a query to resolve that and then convert
/// the results to the appropriate fields.
/// </summary>
/// <returns>true if the user choose at least one area, else false</returns>
public bool CreateInitPlaylist()
{
string str = KeysToString();
if (String.IsNullOrEmpty(str))
{
return(false);
}
StringBuilder query = new StringBuilder();
query.Append("SELECT idSongs,song_name,year,song_hotness,song_duration,song_tempo,artists_from_areas.idArtists,artists_from_areas.artist_name,artists_from_areas.genre,album_name,idAlbum ");
query.Append("FROM songs JOIN (SELECT idArtists, artist_name, genre FROM artists WHERE Area_LocationId in (" + str + ") GROUP BY idArtists) AS artists_from_areas ");
query.Append("join album ");
query.Append("WHERE songs.Artists_idArtists = artists_from_areas.idArtists and songs.Album_idAlbum = album.idAlbum GROUP BY idSongs order by idSongs");
try
{
DataTable dt = executer.ExecuteCommandWithResults(query.ToString());
ObservableCollection <string> list = JsonConvert.DeserializeObject <ObservableCollection <string> >(QueryInterpreter.Instance.getQueryEntitesObject(QueryInterpreter.QueryType.ResolveInitialPlaylist, dt));
ObservableCollection <Song> songs = new ObservableCollection <Song>();
foreach (string item in list)
{
songs.Add(JsonConvert.DeserializeObject <Song>(item));
}
model_playlist.Songs = songs;
model_playlist.User = User;
return(true);
} catch (Exception ex)
{
Console.WriteLine(ex.Data);
}
return(false);
}
/// <summary>
/// getIDFromTable method.
/// with a given username and password from properties -
/// extract his id in table.
/// </summary>
public void getIDFromTable()
{
try
{
String query = "SELECT idUsers FROM users WHERE user_name = '" + Username + "' AND password = '******'";
DataTable dt = executer.ExecuteCommandWithResults(query);
ID = dt.Rows[0].Field <int>(0);
}
catch (Exception ex)
{
Console.WriteLine(ex.Data);
}
}
/// <summary>
/// create a new playlist in the database and get its id.
/// </summary>
private void createPlaylist()
{
try
{
StringBuilder query = new StringBuilder();
query.Append("Insert into playlist(playlist_name, Users_idUsers) values('" + playlist.User.Name + "'," + playlist.User.ID + ")");
int n = executer.ExecuteCommandWithoutResult(query.ToString());
query.Clear();
query.Append("select idSongsPlaylist from playlist where playlist_name = '" + playlist.User.Name + "' and Users_idUsers = " + playlist.User.ID.ToString());
DataTable dt = executer.ExecuteCommandWithResults(query.ToString());
playlist.ID = dt.Rows[0].Field <int>(0);
}
catch (Exception ex)
{
Console.WriteLine(ex.Data);
}
}
/// <summary>
/// FindUser method.
/// bind from the user the username and password, and confirm if
/// he exists in table or not.
/// </summary>
/// <returns></returns>
public bool FindUser()
{
try
{
String query = "SELECT * FROM users WHERE users.user_name = '" + User.Name + "' AND users.password = '******'";
DataTable dt = dataBase.ExecuteCommandWithResults(query);
if (dt.Rows.Count != 0)
{
user.ID = dt.Rows[0].Field <int>(0);
return(true);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Data);
}
return(false);
}