def get_composition_recordings(composition_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
r.id,
r.user_id,
r.audio_url,
r.producer,
r.artist,
r.recording_type,
r.date_recorded,
r.is_mixed,
r.is_mastered,
r.is_delivered,
r.composition_id,
r.image_url,
r.ownership_split,
STRFTIME('%m/%d/%Y',r.date_recorded) as date_refactored
FROM songwrytrapp_composition c
JOIN songwrytrapp_recording r
ON r.composition_id = c.id
WHERE c.id = ?
ORDER BY r.date_recorded
""", (composition_id, ))
return db_cursor.fetchall()
def get_composition_publishers(composition_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
pc.id,
pc.user_id,
pc.name,
pc.pro_id,
pc.pro_acct_num,
pc.admin,
cpc.percentage,
cpc.pro_work_num,
p.name as pro,
cpc.id as compositionpublishing_id
FROM songwrytrapp_publishingcompany pc
JOIN songwrytrapp_pro p
ON pc.pro_id = p.id
JOIN songwrytrapp_compositionpublishing cpc
ON cpc.publishing_company_id = pc.id
JOIN songwrytrapp_composition c
ON c.id = cpc.composition_id
WHERE c.id = ?
ORDER BY pc.name
""", (composition_id, ))
return db_cursor.fetchall()
def get_recording(recording_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Recording)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
r.id,
r.user_id,
r.audio_url,
r.producer,
r.artist,
r.recording_type,
r.date_recorded,
r.is_mixed,
r.is_mastered,
r.is_delivered,
r.composition_id,
r.image_url,
r.ownership_split
FROM songwrytrapp_recording r
WHERE id = ?
""", (recording_id, ))
return db_cursor.fetchone()
def publishingcompany_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(PublishingCompany)
user_id = request.user.id
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
pc.id,
pc.user_id,
pc.name,
pc.pro_id,
pc.pro_acct_num,
pc.admin,
p.name as 'PRO_Name',
p.city as 'PRO_City',
p.state as 'PRO_State',
p.zipcode as 'PRO_Zipcode'
FROM songwrytrapp_publishingcompany pc
JOIN songwrytrapp_pro p
ON pc.pro_id = p.id
WHERE pc.user_id = ?
ORDER BY pc.name
""", (user_id, ))
all_publishingcompanies = db_cursor.fetchall()
template = 'publishingcompanies/list.html'
context = {'all_publishingcompanies': all_publishingcompanies}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO songwrytrapp_publishingcompany
(
name, pro_id, pro_acct_num, admin, user_id
)
VALUES (?, ?, ?, ?, ?)
""", (form_data['name'], form_data['pro'],
form_data['pro_acct_num'], form_data['admin'],
request.user.id))
return redirect(reverse('songwrytrapp:publishingcompanies'))
def composition_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
user_id = request.user.id
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.user_id,
c.title,
c.alt_titles,
c.lyrics,
c.notes,
c.date_created
FROM songwrytrapp_composition c
WHERE c.user_id = ?
ORDER BY c.title
""", (user_id,))
all_compositions = db_cursor.fetchall()
template = 'compositions/list.html'
context = {
'all_compositions': all_compositions
}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO songwrytrapp_composition
(
title, alt_titles, lyrics, notes, date_created, user_id
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(form_data['title'], form_data['alt_titles'],
form_data['lyrics'], form_data['notes'],
form_data['date_created'],
request.user.id))
return redirect(reverse('songwrytrapp:compositions'))
def writer_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Writer)
user_id = request.user.id
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
w.id,
w.user_id,
w.first_name,
w.last_name,
w.publishing_notes,
w.pro_id,
w.pro_ipi
FROM songwrytrapp_writer w
WHERE w.user_id = ?
ORDER BY w.last_name
""", (user_id,))
all_writers = db_cursor.fetchall()
template = 'writers/list.html'
context = {
'all_writers': all_writers
}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO songwrytrapp_writer
(
first_name, last_name, pro_id, pro_ipi, publishing_notes, user_id
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(form_data['first_name'], form_data['last_name'],
form_data['pro'], form_data['pro_ipi'],
form_data['publishing_notes'],
request.user.id))
return redirect(reverse('songwrytrapp:writers'))
def get_compositionwriter(compositionwriter_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(CompositionWriter)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
cw.id,
cw.composition_id,
cw.writer_id,
cw.percentage
FROM songwrytrapp_compositionwriter cw
WHERE cw.id = ?
""", (compositionwriter_id, ))
return db_cursor.fetchone()
def get_pros():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(PRO)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
p.id,
p.name,
p.address,
p.city,
p.state,
p.zipcode,
p.website
FROM songwrytrapp_pro p
""")
return db_cursor.fetchall()
def get_publishingcompany(publishingcompany_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(PublishingCompany)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
pc.id,
pc.user_id,
pc.name,
pc.pro_id,
pc.pro_acct_num,
pc.admin
FROM songwrytrapp_publishingcompany pc
WHERE pc.id = ?
""", (publishingcompany_id,))
return db_cursor.fetchone()
def get_compositionpublishing(compositionpublishing_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(CompositionPublishing)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
cp.id,
cp.composition_id,
cp.publishing_company_id,
cp.percentage,
cp.pro_work_num
FROM songwrytrapp_compositionpublishing cp
WHERE cp.id = ?
""", (compositionpublishing_id, ))
return db_cursor.fetchone()
def get_compositions():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.user_id,
c.title,
c.alt_titles,
c.lyrics,
c.notes,
c.date_created
FROM songwrytrapp_composition c
""")
return db_cursor.fetchall()
def get_publishers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Writer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
pc.id,
pc.user_id,
pc.name,
pc.pro_id,
pc.pro_acct_num,
pc.admin
FROM songwrytrapp_publishingcompany pc
ORDER BY pc.name
""")
return db_cursor.fetchall()
def get_writers():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Writer)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
w.id,
w.user_id,
w.first_name,
w.last_name,
w.publishing_notes,
w.pro_id,
w.pro_ipi
FROM songwrytrapp_writer w
ORDER BY w.last_name
""")
return db_cursor.fetchall()
def get_composition(composition_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
c.id,
c.title,
c.alt_titles,
c.lyrics,
c.notes,
c.date_created,
STRFTIME('%m/%d/%Y',c.date_created) as date_refactored,
c.user_id
FROM songwrytrapp_composition c
WHERE c.id = ?
""", (composition_id, ))
return db_cursor.fetchone()
def get_publishingcompanies():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(PublishingCompany)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
pc.id,
pc.user_id,
pc.name,
pc.pro_id,
pc.pro_acct_num,
pc.admin,
p.name as 'PRO_Name',
p.city as 'PRO_City',
p.state as 'PRO_State',
p.zipcode as 'PRO_Zipcode'
FROM songwrytrapp_publishingcompany pc
JOIN songwrytrapp_pro p
ON pc.pro_id = p.id
""")
return db_cursor.fetchall()
def get_composition_writers(composition_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Composition)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
w.id,
w.first_name,
w.last_name,
w.user_id,
cw.percentage,
cw.id as compositionwriter_id
FROM songwrytrapp_composition c
JOIN songwrytrapp_compositionwriter cw
ON cw.composition_id = c.id
JOIN songwrytrapp_writer w
ON w.id = cw.writer_id
WHERE c.id = ?
ORDER BY w.last_name
""", (composition_id, ))
return db_cursor.fetchall()
