def get_episodes_by_person(id=None, name=None):
sql = 'SELECT \
PRO.id AS program_id,\
PRO.title AS program_title,\
EPI.id AS episode_id,\
EPI.title AS episode_title,\
EPI.content AS episode_content,\
EPI.airdate AS episode_airdate,\
PER.name AS person_name\
FROM\
reviewus.ru_program AS PRO,\
reviewus.ru_episode AS EPI,\
reviewus.ru_person AS PER,\
reviewus.ru_cast AS CA\
WHERE\
CA.episode_id = EPI.id AND EPI.program_id = PRO.id AND CA.person_id = PER.id'\
if id is not None:
sql += ' AND CA.person_id = %s'
return DB.execute_and_fetch_all(sql, param=(id), as_list=True)
else:
# search by name
sql += ' AND PER.name like %s'
search_name = '%' + name + '%'
return DB.execute_and_fetch_all(sql, param=(search_name), as_list=True)
def view(request, id, page=1):
eid = int(id or 0)
page = int(page or 1)
episode = API.get_episode(eid)
if not episode:
raise Http404
episode['reviews'] = get_review_info(eid)
program = API.get_program(episode['program_id'])
sql = 'SELECT R.*, U.username, U.first_name, U.last_name, E.title as episode_title, E.program_id, \
P.title as program_title \
FROM ru_review as R, auth_user as U, ru_episode as E, ru_program as P \
WHERE R.author_id = U.id AND R.episode_id = E.id AND E.program_id = P.id \
AND E.id = %s \
ORDER BY R.creation_time desc \
LIMIT %s, %s'
data = (eid, (page - 1) * 10, 10)
reviews = DB.execute_and_fetch_all(sql, param=data, as_list=True)
for r in reviews:
u = r['last_name']
u += '*' * min(3, len(r['first_name'] or []))
r['display_name'] = u
sql = 'SELECT distinct P.id, P.name\
FROM ru_cast AS C, ru_episode AS E, ru_person AS P\
WHERE C.episode_id = E.id AND C.person_id = P.id AND E.id = %s\
ORDER BY P.name'
casting = DB.execute_and_fetch_all(sql, param=(eid, ), as_list=True)
print(casting)
return render(
request, 'episode/view.html', {
'episode': episode,
'program': program,
'reviews': reviews,
'casting': casting,
'page': {
'prev': max(1, page - 1),
'cur': page,
'next': page + 1
}
})
def get_reviews(page=1, num=50, author=None, program=None, episode=None):
cond = ''
condd = ()
if program:
cond += ' AND P.id = %s'
condd += (program,)
if author:
cond += ' AND R.author_id = %s'
condd += (author,)
if episode:
cond += ' AND E.id = %s'
condd += (episode,)
sql = 'SELECT R.*, E.title as episode_title, P.id as program_id, P.title as program_title\
FROM\
ru_review AS R,\
ru_episode AS E,\
ru_program AS P\
WHERE\
R.episode_id = E.id\
AND E.program_id = P.id' + cond + '\
ORDER BY creation_time DESC\
LIMIT %s, %s'
data = condd + (
num * (page-1), num
)
return DB.execute_and_fetch_all(sql, param=data, as_list=True)
def get_programs(page=1, nums=20):
page = max(0, int(page or 1) - 1)
nums = max(0, int(nums or 20))
sql = 'SELECT P.*, G.name AS genre_name, B.name AS broad_name, \
COUNT(E.id) AS num_episodes, \
AVG(E.avg_star) AS avg_star \
FROM \
ru_program AS P, \
( \
SELECT epi.*, AVG(R.star) AS avg_star \
FROM ru_episode AS epi \
LEFT JOIN ru_review AS R \
ON R.episode_id = epi.id \
GROUP BY epi.id \
) AS E, \
ru_genre AS G, ru_broadcast_system AS B \
WHERE \
P.broadcast_id = B.id \
AND E.program_id = P.id \
AND P.genre_id = G.id \
GROUP BY P.id \
ORDER BY start_date IS NULL DESC, start_date DESC, end_date IS NULL DESC, end_date DESC, title \
LIMIT %s OFFSET %s'
params = (nums, page * nums)
programs = DB.execute_and_fetch_all(sql, param=params, as_list=True)
return programs
def get_people():
sql = 'SELECT P.id as id, P.name as name, J.name as job_name, J.id as job_id\
FROM\
ru_person AS P\
LEFT JOIN\
ru_job AS J ON P.job_id = J.id\
ORDER BY P.name, J.name'
return DB.execute_and_fetch_all(sql, as_list=True)
def delete_broadcastsystem(id):
sql = 'DELETE FROM ru_broadcast_system WHERE id = %s'
try:
res = DB.execute(sql, param=(id, ))
return res > 0
except:
pass
return False
def get_episodes(page=1, nums=10):
page = max(0, int(page or 1) - 1)
nums = max(0, int(nums or 10))
sql = 'SELECT * FROM ru_episode WHERE title != "ALL" ORDER BY airdate IS NULL DESC, airdate DESC LIMIT %s OFFSET %s'
params = (nums, page * nums)
return DB.execute_and_fetch_all(sql, param=params, as_list=True)
def delete_review(id):
sql = 'DELETE FROM ru_review WHERE id = %s'
try:
res = DB.execute(sql, param=(id, ))
return res > 0
except:
pass
return False
def get_review_info(episode_id):
sql = 'SELECT AVG(star) AS avg_star, COUNT(star) AS total_star \
FROM ru_review \
WHERE episode_id = %s'
try:
res = DB.execute_and_fetch(sql, param=(episode_id), as_row=True)
except:
pass
return res
def view(request, id):
bs = API.get_broadcastsystem(id)
if not bs:
raise Http404
sql = 'SELECT P.* \
FROM ru_broadcast_system AS B, ru_program AS P \
WHERE B.id = P.broadcast_id AND B.id = %s'
programs = None
try:
programs = DB.execute_and_fetch_all(sql, param=(id, ), as_list=True)
except:
pass
sql = 'SELECT avg(star) as avg_star\
FROM\
ru_review AS R,\
ru_broadcast_system AS B,\
ru_episode AS E,\
ru_program AS P\
WHERE\
R.episode_id = E.id\
AND E.program_id = P.id\
AND P.broadcast_id = B.id\
AND B.id = %s\
GROUP BY broadcast_id'
avg_star = None
try:
row = DB.execute_and_fetch(sql, param=(id, ), as_row=True)
avg_star = row['avg_star']
except:
pass
bs['avg_star'] = avg_star
return render(request, 'broadcastsystem/view.html', {
'broadcast': bs,
'programs': programs
})
def view(request, id):
sql = 'SELECT P.*, J.id as job_id, J.name as job_name\
FROM\
ru_person AS P\
LEFT JOIN\
ru_job AS J ON P.job_id = J.id\
WHERE P.id = %s'
p = DB.execute_and_fetch(sql, param=(id, ), as_row=True)
"""
sql = 'SELECT DISTINCT E.* \
FROM ru_cast AS C, ru_episode AS E \
WHERE C.episode_id = E.id AND C.person_id = %s'
"""
sql = 'SELECT C.*, P.*, E.id as episode_id, E.title as episode_title \
FROM ru_cast AS C, ru_episode AS E, ru_program AS P \
WHERE C.episode_id = E.id AND E.program_id = P.id AND C.person_id = %s \
ORDER BY P.title'
epi = DB.execute_and_fetch_all(sql, param=(id, ), as_list=True)
return render(request, 'person/view.html', {'person': p, 'episodes': epi})
def get_broadcastsystems():
sql = 'SELECT \
B.id AS broadcast_id,\
B.name AS broadcast_name,\
COUNT(P.id) AS program_count\
FROM\
ru_broadcast_system AS B\
LEFT JOIN\
ru_program AS P ON P.broadcast_id = B.id\
GROUP BY B.id\
ORDER BY B.name'
return DB.execute_and_fetch_all(sql, as_list=True)
def create_broadcastsystem(req):
query = query_from_request(req)
sql = 'INSERT INTO ru_broadcast_system (name) VALUES (%s)'
data = (
query.get('name')
)
res = DB.execute(sql, param=data, cursor=True)
try:
return res.lastrowid
except:
pass
return None
def index(request):
if not request.user.is_authenticated():
return render(request, 'home/index.html')
sql = 'SELECT R.*, U.username, U.first_name, U.last_name, E.title as episode_title, E.program_id, \
P.title as program_title \
FROM ru_review as R, auth_user as U, ru_episode as E, ru_program as P \
WHERE R.author_id = U.id AND R.episode_id = E.id AND E.program_id = P.id \
ORDER BY R.creation_time desc \
LIMIT 10'
user_reviews = DB.execute_and_fetch_all(sql, as_list=True)
statistic = {'reviews': 0, 'episodes': 0, 'stars': 0}
for row in user_reviews:
name = row['last_name']
for i in row['first_name'] or []:
name += '*'
row['display_name'] = name
try:
sql = 'SELECT count(id) as count FROM ru_episode'
statistic['episodes'] = DB.execute_and_fetch(sql, as_row=True)['count']
sql = 'SELECT count(id) as count, sum(star) as stars FROM ru_review'
row = DB.execute_and_fetch(sql, as_row=True)
statistic['reviews'] = row['count']
statistic['stars'] = row['stars']
except:
pass
return render(request, 'home/main.html', {
'user': request.user,
'user_reviews': user_reviews,
'statistic': statistic
})
def get_broadcastsystem(id):
sql = 'SELECT \
B.id AS id,\
B.name AS name,\
COUNT(P.id) AS program_count\
FROM\
ru_broadcast_system AS B\
LEFT JOIN\
ru_program AS P ON P.broadcast_id = B.id\
WHERE B.id = %s'
row = DB.execute_and_fetch(sql, param=(id,), as_row=True)
if not row['id']:
return None
return row
def get_episodes_by_program(program_id, top=False):
topcond = ''
if top:
topcond = ' avg_star DESC, '
program_id = int(program_id or 0)
sql = 'SELECT E.*, \
AVG(R.star) as avg_star, \
COUNT(R.id) as total_reviews \
FROM ru_episode AS E \
LEFT JOIN ru_review AS R \
ON R.episode_id = E.id \
WHERE E.program_id = %s \
GROUP BY E.id \
ORDER BY ' + topcond + ' airdate IS NULL DESC, airdate DESC'
return DB.execute_and_fetch_all(sql, param=(program_id), as_list=True)
def update_review(req, id):
query = query_from_request(req)
sql = 'UPDATE ru_broadcast_system \
SET name = %s \
WHERE id = %s'
data = (
query.get('name') or '',
int(id or 0)
)
try:
res = DB.execute(sql, param=data)
return id
except:
pass
return None
def create_person(req):
query = query_from_request(req)
sql = 'INSERT INTO ru_person (name, job_id, birth_date, details) VALUES (%s, %s, %s, %s)'
data = (
query.get('name'),
int(query.get('job_id')),
parse_date(query.get('birth_date')),
query.get('details')
)
print(data)
res = DB.execute(sql, param=data, cursor=True)
try:
return res.lastrowid
except:
pass
return None
def delete(request, id):
eid = int(id or 0)
episode = API.get_episode(eid)
if not episode:
raise Http404
if request.method == 'POST':
API.delete_episode(eid)
return redirect('episode:list')
sql = 'SELECT COUNT(id) as reviews FROM ru_review WHERE episode_id = %s'
res = DB.execute_and_fetch(sql, param=(eid), as_row=True)
episode['reviews'] = res['reviews']
return render(request, 'episode/delete.html', {
'episode': episode,
'program': API.get_program(episode['program_id'])
})
def create_review(req, author_id):
query = query_from_request(req)
sql = 'INSERT INTO ru_review (author_id, episode_id, comment, star) VALUES (%s, %s, %s, %s)'
data = (
author_id,
int(query.get('episode_id')),
query.get('comment'),
int(query.get('star') or 1)
)
print(data)
res = DB.execute(sql, param=data, cursor=True)
try:
return res.lastrowid
except:
pass
return None
def update_person(req, id):
query = query_from_request(req)
sql = 'UPDATE ru_person \
SET name=%s, job_id=%s, birth_date=%s, details=%s \
WHERE id = %s'
data = (
query.get('name'),
int(query.get('job_id')),
parse_date(query.get('birth_date')),
query.get('details') or '',
int(id or 0)
)
try:
res = DB.execute(sql, param=data)
return id
except:
pass
return None
def create_episode(req):
query = query_from_request(req)
sql = 'INSERT INTO ru_episode \
(program_id, title, content, airdate) \
VALUES \
(%s, %s, %s, %s)'
data = (
int(query.get('program_id')),
query.get('title'),
query.get('content') or '',
parse_date(query.get('airdate'))
)
res = DB.execute(sql, param=data, cursor=True)
try:
return res.lastrowid
except:
pass
return None
def update_episode(req, id):
query = query_from_request(req)
sql = 'UPDATE ru_episode \
SET program_id=%s, title=%s, content=%s, airdate=%s \
WHERE id = %s'
data = (
int(query.get('program_id') or 0),
query.get('title'),
query.get('content'),
parse_date(query.get('airdate')),
int(id)
)
try:
res = DB.execute(sql, param=data)
return id
except:
pass
return None
def update_program(req, id):
query = query_from_request(req)
sql = 'UPDATE ru_program \
SET title=%s, content=%s, broadcast_id=%s, genre_id=%s, start_date=%s, end_date=%s \
WHERE id = %s'
data = (
query.get('title'),
query.get('content'),
int(query.get('broadcast_id') or 0),
int(query.get('genre_id') or 0),
parse_date(query.get('start_date')),
parse_date(query.get('end_date')),
int(id)
)
try:
res = DB.execute(sql, param=data)
return id
except:
pass
return None
def get_jobs():
sql = 'SELECT * FROM ru_job ORDER BY name'
return DB.execute_and_fetch_all(sql, as_list=True)