def search(page=0, items=10):
"""加入搜尋條件的play列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form['s_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total FROM play p " \
u"INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
u"INNER JOIN player t2 ON t2.player_id = p.batter_id " + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args:
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0':
sort_condition += u" DESC"
else:
sort_condition += u" ASC"
else:
sort_condition = "ORDER BY play_id ASC"
sql2 = u"SELECT play_id, t1.player_name as pitcher_name, t2.player_name as batter_name, match_id, inning, half " \
u"FROM play p " \
u"INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
u"INNER JOIN player t2 ON t2.player_id = p.batter_id " + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
plays = []
with conn.cursor() as cursor:
cursor.execute(sql2)
plays = cursor.fetchall()
print(plays)
misc = items_pagebar(total, page, items, 'play_id') # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('play/list.html', plays=plays, misc=misc, action='search')
else:
return redirect(url_for('play.simple_list'))
def search(page=0, items=10):
"""加入搜尋條件的team列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form[
's_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total FROM team" + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args:
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0':
sort_condition += u" DESC"
else:
sort_condition += u" ASC"
sql2 = u"SELECT * FROM team" + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
teams = []
with conn.cursor() as cursor:
cursor.execute(sql2)
teams = cursor.fetchall()
print(teams)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('team/list.html',
teams=teams,
misc=misc,
action='search')
else:
return redirect(url_for('team.simple_list'))
def simple_list(page=0, items=10):
"""簡單的match列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
# 先取得符合條件的 record 一共有多少筆
sql1 = u"SELECT COUNT(*) as total FROM `match`"
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT match_id, t3.name as place, t1.team_name as Home, t2.team_name as Away, m_date " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
matches = []
with conn.cursor() as cursor:
cursor.execute(sql2)
matches = cursor.fetchall()
print(matches)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
return render_template('match/list.html',
matches=matches,
misc=misc,
action='simple_list')
def simple_list(page=0, items=10):
"""簡單的game列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
sql1 = u"SELECT COUNT(*) as total FROM play"
# 先取得符合條件的 record 一共有多少筆
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = ""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = " ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += " DESC"
else: # 表示要asc
sort_condition += " ASC"
else:
sort_condition = "ORDER BY play_id ASC"
sql2 = "SELECT play_id, t1.player_name as pitcher_name, t2.player_name as batter_name, p.match_id, inning, half, M.m_date, F.name " \
"FROM play p INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
"INNER JOIN player t2 ON t2.player_id = p.batter_id " \
"JOIN `match` AS M ON p.match_id = M.match_id " \
"JOIN `field` AS F ON M.field_id = F.field_id " + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += " LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
plays = []
with conn.cursor() as cursor:
cursor.execute(sql2)
plays = cursor.fetchall()
print(plays)
misc = items_pagebar(total, page, items, 'play_id') # 計算pagebar需要之參數
return render_template('play/list.html', plays=plays, misc=misc, action='simple_list')
def simple_list(page=0, items=10):
"""簡單的team列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
# 先取得符合條件的 record 一共有多少筆
sql1 = u"SELECT COUNT(*) as total FROM team" # u 表示unicode
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT * FROM team" + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
teams = []
with conn.cursor() as cursor:
cursor.execute(sql2)
teams = cursor.fetchall()
print(teams)
misc = items_pagebar(total, page, items, 'team_id') # 計算pagebar需要之參數
return render_template('team/list.html',
teams=teams,
misc=misc,
action='simple_list')
def search(page=0, items=10):
"""加入搜尋條件的field列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form[
's_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT match_id, t3.name as place, t1.team_name as Home, t2.team_name as Away, m_date " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
matches = []
with conn.cursor() as cursor:
cursor.execute(sql2)
matches = cursor.fetchall()
print(matches)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('match/list.html',
matches=matches,
misc=misc,
action='search')
else:
return redirect(url_for('match.simple_list'))
