public async Task <ImportStats> UpdateSongPlays(SetlistShow show, IEnumerable <SetlistSong> songs)
{
var stats = new ImportStats();
await db.WithConnection(async con => {
stats.Created += await con.ExecuteAsync(@"
INSERT
INTO
setlist_songs_plays
(
played_setlist_song_id,
played_setlist_show_id
)
VALUES
(
@songId,
@showId
)
ON CONFLICT
ON CONSTRAINT setlist_songs_plays_song_id_show_id_key
DO NOTHING
", songs.Select(song => new { showId = show.id, songId = song.id }));
stats.Removed += await con.ExecuteAsync(@"
DELETE
FROM
setlist_songs_plays
WHERE
played_setlist_show_id = @showId
AND NOT(played_setlist_song_id = ANY(@songIds))
", new { showId = show.id, songIds = songs.Select(s => s.id).ToList() });
});
return(stats);
}
public async Task <SetlistShow> Save(SetlistShow show)
{
var p = new {
show.id,
show.artist_id,
show.venue_id,
show.date,
show.tour_id,
show.upstream_identifier,
show.updated_at,
};
if (show.id != 0)
{
return(await db.WithConnection(con => con.QuerySingleAsync <SetlistShow>(@"
UPDATE
setlist_shows
SET
artist_id = @artist_id,
venue_id = @venue_id,
date = @date,
tour_id = @tour_id,
upstream_identifier = @upstream_identifier,
updated_at = @updated_at,
uuid = md5(@artist_id || '::setlist_show::' || @upstream_identifier)::uuid
WHERE
id = @id
RETURNING *
", p)));
}
else
{
return(await db.WithConnection(con => con.QuerySingleAsync <SetlistShow>(@"
INSERT INTO
setlist_shows
(
artist_id,
venue_id,
date,
tour_id,
upstream_identifier,
updated_at,
uuid
)
VALUES
(
@artist_id,
@venue_id,
@date,
@tour_id,
@upstream_identifier,
@updated_at,
md5(@artist_id || '::setlist_show::' || @upstream_identifier)::uuid
)
RETURNING *
", p)));
}
}
public async Task <int> RemoveSongPlays(SetlistShow show)
{
return(await db.WithConnection(con => con.ExecuteAsync(@"
DELETE
FROM
setlist_songs_plays
WHERE
played_setlist_show_id = @showId
", new { showId = show.id })));
}
private void Dlg_SetlistSelected(object sender, SetlistSelectedEventArgs e)
{
if (SetlistShowDlg == null || SetlistShowDlg.IsDisposed)
{
SetlistShowDlg = new SetlistShow(e.SetlistId);
OpenSetlist = SetlistShowDlg;
SetlistShowDlg.SongSelected += new EventHandler <SongSelectedEventArgs>(Dlg_SongSelected);
}
else
{
SetlistShowDlg.SetlistId = e.SetlistId;
SetlistShowDlg.LoadSetlist();
}
SetlistShowDlg.Show();
SetlistShowDlg.BringToFront();
}
public async Task <SetlistShow> Save(SetlistShow show)
{
if (show.id != 0)
{
return(await db.WithConnection(con => con.QuerySingleAsync <SetlistShow>(@"
UPDATE
setlist_shows
SET
artist_id = @artist_id,
venue_id = @venue_id,
date = @date,
tour_id = @tour_id,
upstream_identifier = @upstream_identifier,
updated_at = @updated_at
WHERE
id = @id
RETURNING *
", show)));
}
else
{
return(await db.WithConnection(con => con.QuerySingleAsync <SetlistShow>(@"
INSERT INTO
setlist_shows
(
artist_id,
venue_id,
date,
tour_id,
upstream_identifier,
updated_at
)
VALUES
(
@artist_id,
@venue_id,
@date,
@tour_id,
@upstream_identifier,
@updated_at
)
RETURNING *
", show)));
}
}
public async Task <int> AddSongPlays(SetlistShow show, IEnumerable <SetlistSong> songs)
{
return(await db.WithConnection(con => con.ExecuteAsync(@"
INSERT
INTO
setlist_songs_plays
(
played_setlist_song_id,
played_setlist_show_id
)
VALUES
(
@songId,
@showId
)
", songs.Select(song => new { showId = show.id, songId = song.id }))));
}
