def do_issue_new_session_id(uid, sid):
models.db().execute("""
DELETE FROM MobileSession
WHERE user_id = ?""", (uid, ))
models.db().execute("""
INSERT INTO MobileSession (user_id, session_id, expire)
VALUES (?, ?, datetime('now', '+1 month'))""", (uid, sid))
def post(body):
models.db().execute("""
INSERT INTO BBS (user_id, when_, body) VALUES (?, ?, ?)""", (
models.user().id,
datetime.now(),
sanitize_html(body)))
models.db().commit()
def insert_payment_histories(action, seasons):
updater = models.user().id
models.db().executemany("""
INSERT INTO TaxPaymentHistory (
year, user_id, season, action, updater_user_id)
VALUES (?, ?, ?, ?, ?)""",
[(year, user_id, s, action, updater) for s in seasons])
def update(sid, when_, body):
models.db().execute("""
UPDATE Schedule
SET when_ = ?,
body = ?
WHERE id = ?""", (when_, body, sid))
models.db().commit()
def update(id, title, begin_show, end_show, body):
models.db().execute("""
UPDATE Notice
SET title = ?,
begin_show = ?,
end_show = ?,
body = ?
WHERE id = ?""", (title, begin_show, end_show, body, id))
models.db().commit()
def handle_join_request(id):
models.db().execute(
"""
UPDATE JoinRequest
SET handled = 1
WHERE id = ?""",
(id,),
)
models.db().commit()
def update_entry(sid, comment, entry):
models.db().execute("""
UPDATE Entry
SET is_entry = ?,
comment = ?,
when_ = CURRENT_TIMESTAMP
WHERE user_id = ?
AND schedule_id = ?""", (entry, comment, models.user().id, sid))
models.db().commit()
def insert(name, home, email, sex, age, car, has_racket, holiday, experience, comment):
models.db().execute(
"""
INSERT INTO JoinRequest (when_, name, home, email, sex, age,
car, has_racket, holiday, experience, comment)
VALUES (datetime('now', 'localtime'), ?, ?, ?, ?, ?,
?, ?, ?, ?, ?)""",
(name, home, email, sex, age, car, has_racket, holiday, experience, comment),
)
models.db().commit()
def insert(title, feature_image_url, description, body):
with models.db():
models.db().execute("""
INSERT INTO Report (when_, author_id, title, feature_image_url,
description, description_html, body, body_html)
VALUES (datetime('now', 'localtime'), ?, ?, ?, ?, ?, ?, ?)""",
(models.user().id, title, feature_image_url,
description, sanitize_markdown(description),
body, sanitize_markdown(body)))
return models.db().execute("""
SELECT id
FROM Report
WHERE when_ = (SELECT MAX(when_) FROM Report)""").fetchone()[0]
def count_schedules(type):
cur = models.db().execute("""
SELECT COUNT(*)
FROM Schedule
WHERE type = ?
AND when_ >= datetime('now', 'localtime')""", (type, ))
return cur.fetchone()[0]
def update_logged_in(self):
db = models.db()
db.execute("""
UPDATE User
SET logged_in = datetime('now', 'localtime')
WHERE id = ?""", (self.id, ))
db.commit()
def find_scheduled():
cur = models.db().execute("""
SELECT *
FROM Notice
WHERE end_show >= date('now', 'localtime')
ORDER BY begin_show""")
return [from_row(r) for r in cur.fetchall()]
def find_guest_by_id(guest_id):
return models.db().execute("""
SELECT GuestEntry.id, GuestEntry.name,
GuestEntry.schedule_id, GuestEntry.comment,
GuestEntry.inviter_id, User.name AS inviter_name
FROM GuestEntry, User
WHERE GuestEntry.id = ?
AND inviter_id = User.id""", (guest_id, )).fetchone()
def is_valid_session_id(uid, sid):
cur = models.db().execute("""
SELECT user_id
FROM MobileSession
WHERE user_id = ?
AND session_id = ?
AND expire > CURRENT_TIMESTAMP""", (uid, sid))
return cur.fetchone() is not None
def issue_new_session_id(uid):
for sid in generate_session_id(6):
try:
with models.db():
do_issue_new_session_id(uid, sid)
return sid
except IntegrityError:
pass # not unique
def is_entered(self, schedule):
cur = models.db().execute("""
SELECT user_id
FROM Entry
WHERE user_id = ?
AND schedule_id = ?
AND is_entry = 1""", (self.id, schedule['id']))
return cur.fetchone() is not None
def update_payments(year, user_id, new_paid):
records = models.db().execute("""
SELECT Tax.season
FROM Tax
WHERE Tax.year = ?
AND Tax.user_id = ?""", (year, user_id)).fetchall()
curr_paid = [r['season'] for r in records]
insertions = [s for s in new_paid if s not in curr_paid]
deletions = [s for s in curr_paid if s not in new_paid]
# Tax テーブルの更新
models.db().executemany("""
INSERT INTO Tax (year, user_id, season) VALUES (?, ?, ?)""",
[(year, user_id, s) for s in insertions])
models.db().executemany("""
DELETE FROM Tax
WHERE year = ?
AND user_id = ?
AND season = ?""", [(year, user_id, s) for s in deletions])
# TaxPaymentHistory テーブルの更新
def insert_payment_histories(action, seasons):
updater = models.user().id
models.db().executemany("""
INSERT INTO TaxPaymentHistory (
year, user_id, season, action, updater_user_id)
VALUES (?, ?, ?, ?, ?)""",
[(year, user_id, s, action, updater) for s in seasons])
insert_payment_histories(1, insertions)
insert_payment_histories(2, deletions)
models.db().commit()
def find_not_handled():
cur = models.db().execute(
"""
SELECT *
FROM JoinRequest
WHERE handled = 0
ORDER BY when_ DESC"""
)
return cur.fetchall()
def find_posts(begin):
records = app.config['BBS_PER_PAGE']
return models.db().execute("""
SELECT User.name AS user_name,
BBS.when_,
BBS.body
FROM User, BBS
WHERE BBS.user_id = User.id
ORDER BY BBS.when_ DESC
LIMIT ?, ?""", (begin, records))
def find_group_by_sex():
# 特殊ユーザは一覧上に表示させないように無視する
users = models.db().execute('''
SELECT *
FROM User
WHERE id <> ?
ORDER BY sex, id''', (SPECIAL_USER, )).fetchall()
bysex = {}
for sex, us in groupby(users, lambda u: u['sex']):
bysex[sex] = [from_row(u) for u in us]
return bysex.get(SEX_MALE, []), bysex.get(SEX_FEMALE, [])
def count_has_not_handled():
ret = (
models.db()
.execute(
"""
SELECT COUNT(*)
FROM JoinRequest
WHERE handled = 0"""
)
.fetchone()
)
return ret[0]
def update(self, title, feature_image_url, description, body):
desc_html = sanitize_markdown(description)
body_html = sanitize_markdown(body)
models.db().execute("""
UPDATE Report
SET when_ = datetime('now', 'localtime'),
title = ?,
feature_image_url = ?,
description = ?,
description_html = ?,
body = ?,
body_html = ?
WHERE id = ?""", (title, feature_image_url,
description, desc_html, body, body_html, self.id))
models.db().commit()
self.title = title
self.description = description
self.description_html = desc_html
self.body = body
self.body_html = body_html
def newer_id(self):
if self._newer_id_gotten:
return self._newer_id
newer = models.db().execute("""
SELECT id
FROM Report
WHERE when_ > ?
ORDER BY when_""", (self.when, )).fetchone()
self._newer_id = newer['id'] if newer else None
self._newer_id_gotten = True
return self._newer_id
def older_id(self):
if self._older_id_gotten:
return self._older_id
older = models.db().execute("""
SELECT id
FROM Report
WHERE when_ < ?
ORDER BY when_ DESC""", (self.when, )).fetchone()
self._older_id = older['id'] if older else None
self._older_id_gotten = True
return self._older_id
def insert(name, password, sex, permission):
check_unique_password(password)
try:
db = models.db()
c = db.cursor()
c.execute("""
INSERT INTO User (name, password, sex, permission)
VALUES (?, ?, ?, ?)""", (name, password, sex, permission))
db.commit()
return c.lastrowid
except IntegrityError, e:
raise NotUniquePassword() # そうとは限らないけど。。。
def find_all_group_by_publication():
sayings = models.db().execute("""
SELECT *
FROM Saying
ORDER BY private""").fetchall()
bypublication = {}
for private, ss in groupby(sayings, lambda s: s['private']):
bypublication[private] = list(ss)
public = bypublication.get(0, [])
private = bypublication.get(1, [])
return public, private
def update(id, password, sex, permission):
check_unique_password(password, id)
try:
db = models.db()
db.execute("""
UPDATE User
SET password = ?,
sex = ?,
permission = ?
WHERE id = ?""", (password, sex, permission, id))
db.commit()
except IntegrityError, e:
raise NotUniquePassword() # そうとは限らないけど。。。
def find_by_id(sid, with_entry=True):
cur = models.db().execute("""
SELECT *
FROM Schedule
WHERE id = ?""", (sid, ))
schedule = dict(cur.fetchone())
if with_entry:
es, gs = entries.find_by_schedule(sid)
schedule['entries'] = es
schedule['guests'] = gs
return schedule
def has_not_registered_schedule_yet(self):
cur = models.db().execute("""
SELECT Schedule.id
FROM Schedule
LEFT OUTER JOIN (SELECT *
FROM Entry
WHERE user_id = ?) AS Entry ON
Schedule.id = Entry.schedule_id
WHERE Schedule.when_ >= datetime('now', 'localtime')
AND Schedule.type = ?
GROUP BY Schedule.id
HAVING COUNT(Entry.user_id) = 0""",
(self.id, scheds.TYPE_PRACTICE))
return cur.fetchone() is not None
def find_by_year(year):
taxes = models.db().execute("""
SELECT User.name,
User.id AS user_id,
Tax.year,
Tax.season
FROM User
LEFT OUTER JOIN (SELECT * FROM Tax WHERE year = ?) AS Tax ON
User.id = Tax.user_id
WHERE User.id <> -1
ORDER BY User.sex, User.id""", (year, )).fetchall()
payments = []
id_and_name = lambda x: (x['user_id'], x['name'])
for (user_id, name), paid_seasons in groupby(taxes, id_and_name):
p = Payment(user_id, name)
for paid_season in paid_seasons:
p.pay(year, paid_season['season'])
payments.append(p)
histories = models.db().execute("""
SELECT User.name,
Updater.name AS updater,
TaxPaymentHistory.year,
TaxPaymentHistory.season,
TaxPaymentHistory.action,
TaxPaymentHistory.when_
FROM TaxPaymentHistory
INNER JOIN User ON
User.id = TaxPaymentHistory.user_id
INNER JOIN User AS Updater ON
Updater.id = TaxPaymentHistory.updater_user_id
WHERE TaxPaymentHistory.year = ?
ORDER BY TaxPaymentHistory.when_ DESC
LIMIT 20""", (year, )).fetchall()
return PaymentStats(year, payments, histories)
