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()