def search_albums(genre: str, artist_name: str, name: str) -> list:
from klap4.db import Session
session = Session()
album_list = session.query(Album) \
.join(Artist, and_(Artist.id == Album.artist_id, Artist.name.like(artist_name+'%'))
) \
.join(
Genre, and_(Genre.id == Artist.genre_id, Genre.name.like(genre+'%'))
) \
.filter(
Album.name.like(name+'%'),
) \
.all()
serialized_list = []
for album in album_list:
serialized_album = {
"id": album.ref,
"name": album.name,
"artist_ref": album.artist.ref,
"artist": album.artist.name,
"genre": album.artist.genre.name,
"format": album.format_bitfield,
"missing": album.missing,
"new_album": album.is_new,
}
serialized_list.append(serialized_album)
return serialized_list
def new_album_list() -> list:
from datetime import datetime, timedelta
from klap4.db import Session
session = Session()
new_album_limit = datetime.now() - timedelta(days=30 * 6)
new_album_list = session.query(Album) \
.filter(Album.date_added > new_album_limit).all()
serialized_list = []
for album in new_album_list:
serialized_album = {
"id": album.ref,
"name": album.name,
"artist_ref": album.artist.ref,
"artist": album.artist.name,
"genre": album.artist.genre.name,
"format": album.format_bitfield,
"missing": album.missing,
"new_album": album.is_new,
}
serialized_list.append(serialized_album)
return serialized_list
def delete_playlist_entry(dj_id: str, p_name: str, index: int) -> None:
from klap4.db import Session
session = Session()
playlist_entries = session.query(PlaylistEntry) \
.join(Playlist, and_(Playlist.id == PlaylistEntry.playlist_id, Playlist.name == p_name)) \
.join(DJ, and_(DJ.id == Playlist.dj_id, DJ.id == dj_id)) \
.order_by(PlaylistEntry.index) \
.all()
to_delete = session.query(PlaylistEntry) \
.join(Playlist, and_(Playlist.id == PlaylistEntry.playlist_id, Playlist.name == p_name)) \
.join(DJ, and_(DJ.id == Playlist.dj_id, DJ.id == dj_id)) \
.filter(PlaylistEntry.index == index) \
.one()
to_delete.index = -1
for entry in playlist_entries[index:]:
entry.index = entry.index - 1
session.commit()
session.delete(to_delete)
session.commit()
return
def get_program_slots():
from klap4.db import Session
session = Session()
from datetime import datetime
tdy = datetime.today().weekday()
tmrw = tdy + 1
ystr = tdy - 1
if datetime.today().weekday() == 6:
tmrw = 0
elif datetime.today().weekday() == 0:
ystr = 6
tdy_slots = session.query(ProgramSlot) \
.filter(ProgramSlot.day == tdy).all()
ystr_slots = session.query(ProgramSlot) \
.filter(ProgramSlot.day == ystr).all()
tmrw_slots = session.query(ProgramSlot) \
.filter(ProgramSlot.day == tmrw).all()
program_slots = {
"today": format_object_list(tdy_slots),
"yesterday": format_object_list(ystr_slots),
"tomorrow": format_object_list(tmrw_slots)
}
for category in program_slots.items():
for slot in category[1]:
slot['time'] = str(slot['time'])
return program_slots
def list_playlists(dj_id: str) -> list:
from klap4.db import Session
session = Session()
playlists = session.query(Playlist) \
.filter(Playlist.dj_id == dj_id).all()
return format_object_list(playlists)
def display_program(prog_typ: str) -> SQLBase:
from klap4.db import Session
session = Session()
#query for program object from type and name
program = session.query(ProgramFormat) \
.filter_by(type = prog_typ).first()
return program
def change_album_fcc(ref, fcc):
from klap4.db import Session
session = Session()
album = get_entity_from_tag(ref)
for song in album.songs:
song.fcc_status = fcc
session.commit()
return get_json(album)
def find_genre_id(genre_abbr: str):
entity = None
try:
from klap4.db import Session
session = Session()
entity = session.query(Genre).filter(
Genre.abbreviation == genre_abbr).one()
return entity.id
except:
raise "error"
def delete_program_log(program_type, timestamp, dj_id):
from klap4.db import Session
session = Session()
from datetime import datetime
#converted_timestamp = datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S")
delQuer = session.query(ProgramLogEntry) \
.filter(and_(ProgramLogEntry.program_type == program_type, ProgramLogEntry.timestamp.contains(timestamp), ProgramLogEntry.dj_id == dj_id)) \
.delete(synchronize_session='fetch')
session.commit()
return
def find_artist_id(genre_abbr: str, artist_num: int):
entity = None
try:
from klap4.db import Session
session = Session()
entity = session.query(Artist) \
.join(Genre, and_(Genre.id == Artist.genre_id, Genre.abbreviation == genre_abbr)) \
.filter(Artist.number == artist_num).one()
return entity.id
except:
raise "error"
def find_album(genre_abbr: str, artist_num: int, album_letter: str):
entity = None
try:
from klap4.db import Session
session = Session()
entity = session.query(Album) \
.join(Artist, and_(Artist.id == Album.artist_id, Artist.number == artist_num)) \
.join(Genre, and_(Genre.id == Artist.genre_id, Genre.abbreviation == genre_abbr)) \
.filter(Album.letter == album_letter).one()
return entity
except:
raise "error"
def find_album_id(genre_abbr: str, artist_num: int, album_letter: str):
entity = None
try:
from klap4.db import Session
session = Session()
artist_id = find_artist_id(genre_abbr, artist_num)
entity = session.query(Album) \
.filter(Album.artist_id == artist_id, Album.letter == album_letter).one()
return entity.id
except:
raise "error"
def find_playlist_id(dj_id: str, playlist_name: str):
entity = None
try:
from klap4.db import Session
session = Session()
entity = session.query(Playlist) \
.join(DJ, DJ.id == Playlist.dj_id) \
.filter(Playlist.name == playlist_name).one()
return entity.id
except:
raise "error"
def search_programming(p_type: str, name: str) -> SQLBase:
from klap4.db import Session
session = Session()
serialized_list = []
program_list = session.query(Program) \
.join(ProgramFormat, and_(ProgramFormat.id == Program.format_id, ProgramFormat.type.like(p_type+'%'))) \
.filter(Program.name.like(name+'%')
) \
.all()
for program in program_list:
serialized_list.append(program.serialize())
return serialized_list
def change_single_fcc(ref, song_number, fcc):
from klap4.db import Session
session = Session()
album = get_entity_from_tag(ref)
album_id = album.id
update_song = session.query(Song) \
.join(Album, and_(Album.id == Song.album_id, album.id == album_id)) \
.filter(Song.number == song_number) \
.one()
update_song.fcc_status = fcc
session.commit()
return get_json(update_song)
def update_program_log(program_type, program_name, slot_id, dj_id, new_name):
from klap4.db import Session
session = Session()
from datetime import datetime
update_entry = session.query(ProgramLogEntry) \
.filter(
and_(ProgramLogEntry.program_type == program_type, ProgramLogEntry.program_name == program_name,
ProgramLogEntry.slot_id == slot_id, ProgramLogEntry.dj_id == dj_id)
) \
.one()
update_entry.timestamp = datetime.now()
update_entry.program_name = new_name
session.commit()
return update_entry
def update_playlist(dj_id: str, name: str, show: str, new_name: str,
new_show: str) -> SQLBase:
from klap4.db import Session
session = Session()
playlist_update = session.query(Playlist) \
.filter(
and_(Playlist.dj_id == dj_id, Playlist.name == name)
) \
.one()
playlist_update.name = new_name
playlist_update.show = new_show
session.commit()
serialized_playlist = {"dj_id": dj_id, "name": new_name, "show": new_show}
return serialized_playlist
def generate_chart(format: str, weeks: int) -> list:
from datetime import datetime, timedelta
from klap4.db import Session
session = Session()
chart_list = None
weeks_ago = datetime.now() - timedelta(weeks=int(weeks))
new_album_limit = datetime.now() - timedelta(days=30 * 6)
if format == "all":
chart_list = session.query(Song, func.sum(Song.times_played)) \
.filter(Song.last_played > weeks_ago
) \
.group_by(Song.id, Song.album_id
) \
.all()
elif format == "new":
chart_list = session.query(Song, func.sum(Song.times_played)) \
.join(Album, and_(Album.date_added > new_album_limit, Song.album_id == Album.id)) \
.filter(Song.last_played > weeks_ago) \
.group_by(Song.id) \
.all()
better_charts = []
ref_list = []
for item in chart_list:
genre_abbr = decompose_tag(item[0].ref).genre_abbr
artist_num = decompose_tag(item[0].ref).artist_num
album_letter = decompose_tag(item[0].ref).album_letter
ref = genre_abbr + str(artist_num) + album_letter
if ref in ref_list:
continue
else:
better_charts.append(
(genre_abbr, artist_num, album_letter, item[1]))
ref_list.append(ref)
sorted_charts = sorted(better_charts,
key=lambda x: (-x[3], x[0], x[1], x[2]))
return sorted_charts
def check_user(userid, name, is_admin):
from klap4.db import Session
from klap4.db_entities.dj import DJ
session = Session()
if session.query(DJ).filter_by(id=userid).first() is None:
user = DJ(id=userid, name=name, is_admin=is_admin)
session.add(user)
session.commit()
else:
user = session.query(DJ).filter_by(id=userid).first()
return user
def get_program_log():
from klap4.db import Session
session = Session()
from datetime import datetime
tdy = datetime.today().weekday()
tmrw = tdy + 1
ystr = tdy - 1
if datetime.today().weekday() == 6:
tmrw = 0
elif datetime.today().weekday() == 0:
ystr = 6
tdy_logs = session.query(ProgramLogEntry) \
.join(
ProgramSlot, and_(ProgramSlot.id == ProgramLogEntry.slot_id, ProgramSlot.day == tdy)
) \
.all()
ystr_logs = session.query(ProgramLogEntry) \
.join(
ProgramSlot, and_(ProgramSlot.id == ProgramLogEntry.slot_id, ProgramSlot.day == ystr)
) \
.all()
tmrw_logs = session.query(ProgramLogEntry) \
.join(
ProgramSlot, and_(ProgramSlot.id == ProgramLogEntry.slot_id, ProgramSlot.day == tmrw)
) \
.all()
program_log_entries = {
"today": format_object_list(tdy_logs),
"yesterday": format_object_list(ystr_logs),
"tomorrow": format_object_list(tmrw_logs)
}
return program_log_entries
def display_playlist_entries(dj_id: str, p_name: str) -> SQLBase:
from klap4.db import Session
session = Session()
try:
u_playlist = session.query(Playlist) \
.filter(and_(Playlist.dj_id == dj_id, Playlist.name == p_name)).one()
except:
return {"error": "ERROR"}
playlist = get_json(u_playlist)
playlist_entries = session.query(PlaylistEntry) \
.join(Playlist, and_(Playlist.id == PlaylistEntry.playlist_id, Playlist.name == p_name)) \
.join(DJ, and_(DJ.id == Playlist.dj_id, DJ.id == dj_id)) \
.order_by(PlaylistEntry.index) \
.all()
info_list = format_object_list(playlist_entries)
obj = {"playlist": playlist, "playlist_entries": info_list}
return obj
def search_artists(genre: str, name: str) -> list:
from klap4.db import Session
session = Session()
artist_list = session.query(Artist) \
.join(
Genre, and_(Genre.id == Artist.genre_id, Genre.name.like(genre+'%'))
) \
.filter(
Artist.name.like(name+'%')
) \
.all()
serialized_list = []
for artist in artist_list:
serialized_artist = {
"id": artist.ref,
"name": artist.name,
"genre": artist.genre.name
}
serialized_list.append(serialized_artist)
return serialized_list
def new_artist_list():
from klap4.db import Session
session = Session()
from datetime import datetime, timedelta
new_album_limit = datetime.now() - timedelta(days=30 * 6)
new_artist_list = session.query(Artist) \
.join(
Album, and_(Album.artist_id == Artist.id, Album.date_added > new_album_limit)
) \
.all()
serialized_list = []
for artist in new_artist_list:
serialized_artist = {
"id": artist.ref,
"name": artist.name,
"genre": artist.genre.name
}
serialized_list.append(serialized_artist)
return serialized_list
def delete_playlist(dj_id: str, name: str) -> None:
from klap4.db import Session
session = Session()
session.query(Playlist).filter(
and_(Playlist.dj_id == dj_id, Playlist.name == name)).delete()
session.commit()
return
def report_problem(album_ref: str, dj_id: str, content: str) -> SQLBase:
from klap4.db import Session
session = Session()
newProblem = AlbumProblem(id=album_ref, dj_id=dj_id, content=content)
session.add(newProblem)
session.commit()
return newProblem
def add_review(album_ref: str, dj_id: str, content: str) -> SQLBase:
from datetime import datetime
from klap4.db import Session
session = Session()
newReview = AlbumReview(id=album_ref, dj_id=dj_id, content=content)
session.add(newReview)
session.commit()
return newReview.serialize
def add_playlist(dj_id: str, name: str, show: str) -> SQLBase:
from klap4.db import Session
session = Session()
new_playlist = Playlist(dj_id=dj_id, name=name, show=show)
session.add(new_playlist)
session.commit()
serialized_playlist = {"dj_id": dj_id, "name": name, "show": show}
return serialized_playlist
def add_program_log(program_type, program_name, slot_id, dj_id):
from klap4.db import Session
session = Session()
from datetime import datetime
new_log = ProgramLogEntry(program_type=program_type,
program_name=program_name,
slot_id=int(slot_id),
timestamp=datetime.now(),
dj_id=dj_id)
session.add(new_log)
session.commit()
return new_log
def update_playlist_entry(dj_id: str, p_name: str, index: int, entry,
new_index: int, new_entry):
from klap4.db import Session
session = Session()
from datetime import datetime
if new_index is None and entry is not None and new_entry is not None:
try:
song_entry = session.query(Song) \
.join(Album, and_(Album.id == Song.album_id, Album.name == new_entry["album"])) \
.join(Artist, and_(Artist.id == Album.artist_id, Artist.name == new_entry["artist"])) \
.filter(Song.name == new_entry["song"]).one()
old_times_played = song_entry.times_played
song_entry.last_played = datetime.now()
session.commit()
song_entry.times_played = old_times_played + 1
session.commit()
reference_type = REFERENCE_TYPE.IN_KLAP4
reference = song_entry.album.artist.genre.abbreviation + str(
song_entry.album.artist.number) + song_entry.album.letter
except:
reference_type = REFERENCE_TYPE.MANUAL
reference = str(new_entry)
update_entry = session.query(PlaylistEntry) \
.join(Playlist, and_(Playlist.id == PlaylistEntry.playlist_id, Playlist.name == p_name)) \
.join(DJ, and_(DJ.id == Playlist.dj_id, DJ.id == dj_id)) \
.filter(
and_(PlaylistEntry.index == index,
PlaylistEntry.entry == entry)
) \
.one()
update_entry.entry = new_entry
update_entry.reference = reference
update_entry.reference_type = reference_type
session.commit()
else:
old_index = index
playlist_entries = session.query(PlaylistEntry) \
.join(Playlist, and_(Playlist.id == PlaylistEntry.playlist_id, Playlist.name == p_name)) \
.join(DJ, and_(DJ.id == Playlist.dj_id, DJ.id == dj_id)) \
.order_by(PlaylistEntry.index) \
.all()
if new_index > old_index:
playlist_entries[old_index - 1].index = -1
for entry in playlist_entries[old_index:new_index]:
entry.index = entry.index - 1
session.commit()
playlist_entries[old_index - 1].index = new_index
session.commit()
elif new_index < old_index:
playlist_entries[new_index - 1].index = -1
playlist_entries[old_index - 1].index = new_index
session.commit()
for num in range(old_index - 1, new_index, -1):
playlist_entries[num -
1].index = playlist_entries[num - 1].index + 1
session.commit()
session.commit()
playlist_entries[new_index - 1].index = new_index + 1
session.commit()
else:
pass
return
def add_playlist_entry(dj_id: str, p_name: str, entry) -> SQLBase:
from klap4.db import Session
session = Session()
from datetime import datetime
try:
song_entry = session.query(Song) \
.join(Album, and_(Album.id == Song.album_id, Album.name == entry["album"])) \
.join(Artist, and_(Artist.id == Album.artist_id, Artist.name == entry["artist"])) \
.filter(Song.name == entry["song"]).one()
old_times_played = song_entry.times_played
song_entry.last_played = datetime.now()
session.commit()
song_entry.times_played = old_times_played + 1
session.commit()
reference_type = REFERENCE_TYPE.IN_KLAP4
reference = song_entry.album.artist.genre.abbreviation + str(
song_entry.album.artist.number) + song_entry.album.letter
except:
reference_type = REFERENCE_TYPE.MANUAL
reference = str(entry)
newPlaylistEntry = PlaylistEntry(dj_id=dj_id,
playlist_name=p_name,
reference=reference,
reference_type=reference_type,
entry=entry)
session.add(newPlaylistEntry)
session.commit()
return get_json(newPlaylistEntry)