def proof(db, val, uid, todoid, note, visible, img = None):
# check if valid
c = db.cursor()
c.execute('select val, goal, rate, name from todo_sport join todo where iid = ? and todo.id = todo_sport.id and todo_sport.id = ?',(uid, todoid))
row = c.fetchone()
#print(row)
if None == row:
return False
# valid, update value
c.execute('update todo_sport set val = val + ? where id = ?', (val, todoid))
# check if it has been finished
if val + row[0] >= row[1]:
# value overflow
# update todo to finished
c.execute('update todo set is_finished = 1 where id = ?', (todoid,))
# release pending todos
c.execute('update todo set dependency = -1 where dependency = ?', (todoid,))
# update credit
c.execute('update user set hold = hold + ? where id = ?', (row[2] * val, uid))
# proof
c.execute('select name from user where id = ?', (uid,))
name = decode(c.fetchone()[0])
c.execute('insert into pow(uid, todoid, note, proof, is_public, timestamp) values(?, ?, ?, ?, ?, datetime("now", "localtime"))',
(uid, todoid, encode(note), encode('Sport Proof by '+name+': '+decode(row[3])+' from %lf to %lf with %lf credit'%(row[0], row[0] + val, row[2] * val)), visible))
if img is not None:
c.execute('insert into pow_img(id, base64) values(?, ?)', (c.lastrowid, encode(img)))
db.commit()
def get_info(db, todoid):
c = db.cursor()
c.execute('select todo.name, rate, user.name, iid, dependency, is_finished, goal, val from todo join todo_sport join user where user.id = todo.iid and todo.id = todo_sport.id and todo.id = ?', (todoid,))
try:
name, rate, iname, iid, dependency, is_finished, goal, val = c.fetchone()
except TypeError:
return None
return decode(name), rate, decode(iname), iid, dependency, is_finished, goal, val
def get_user(db, uid):
c = db.cursor()
c.execute('select name, motto from user where id = ?', (uid, ))
u = c.fetchone()
if None == u:
return False
return decode(u[0]), decode(u[1])
def get_info(db, todoid):
c = db.cursor()
c.execute(
'select todo.name, rate, user.name, iid, dependency, is_finished, open, close, span, repeat, val_span, val_repeat, is_loop from todo join todo_keep join user where user.id = todo.iid and todo.id = todo_keep.id and todo.id = ?',
(todoid, ))
try:
name, rate, iname, iid, dependency, is_finished, open, close, span, repeat, val_span, val_repeat, is_loop = c.fetchone(
)
except TypeError:
return None
return decode(name), rate, decode(
iname
), iid, dependency, is_finished, open, close, span, repeat, val_span, val_repeat, is_loop
def get_by_uid_finished_daily(db, uid):
c = db.cursor()
c.execute(
'select todo.id, name, span, val_span, repeat, val_repeat from todo join todo_keep where dependency = -1 and span <> val_span and repeat = val_repeat and todo.id = todo_keep.id and tid = ? and uid = ?',
(tid, uid))
return [[id, decode(name), span, val_span, repeat, val_repeat]
for id, name, span, val_span, repeat, val_repeat in c.fetchall()]
def dump_pow_todo_fence(db, todoid):
c = db.cursor()
c.execute(
'select id, proof, timestamp from pow where is_public <> 0 and todoid = ?',
(todoid, ))
return [[id, decode(proof), timestamp]
for id, proof, timestamp in c.fetchall()]
def credit_get_finished(db, uid):
c = db.cursor()
c.execute(
'select name, price, timestamp from credit where is_spent <> 0 and uid = ?',
(uid, ))
return [[decode(name), price, timestamp]
for name, price, timestamp in c.fetchall()]
def get_by_uid_finished(db, uid):
c = db.cursor()
c.execute(
'select todo.id, name, start, val, end from todo join todo_book where end = val and todo.id = todo_book.id and tid = ? and uid = ?',
(tid, uid))
return [[id, decode(name), start, val, end]
for id, name, start, val, end in c.fetchall()]
def get_by_uid_instructed(db, uid):
c = db.cursor()
c.execute(
'select todo.id, name, start, val, end from todo join todo_book where dependency = -1 and val < end and todo.id = todo_book.id and tid = ? and uid <> iid and uid = ?',
(tid, uid))
return [[id, decode(name), start, val, end]
for id, name, start, val, end in c.fetchall()]
def dump_pow_me_fence(db, uid):
c = db.cursor()
# by default only fetch first 30
c.execute(
'select distinct(pow.id), proof, timestamp from pow join user_bond where (pow.uid = ?) or (pow.uid = user_bond.iid and user_bond.uid = ? and is_public <> 0) order by pow.id desc limit 30',
(uid, uid))
return [[id, decode(proof), timestamp]
for id, proof, timestamp in c.fetchall()]
def get_todo_with_type(db, todoid):
c = db.cursor()
c.execute(
'select todo.name, todo_t.name from todo join todo_t where todo_t.id = todo.tid and todo.id = ?',
(todoid, ))
name, tname = c.fetchone()
return decode(name), tname
def auth_user(db, name, passwd):
c = db.cursor()
c.execute('select motto, id, hold from user where name = ? and passwd = ?', (encode(name), passwd))
row = c.fetchone()
if row != None:
return decode(row[0]), row[1], row[2]
else:
return None, None, None
def get_by_uid_inprogress(db, uid):
c = db.cursor()
c.execute(
'select todo.id, name, span, val_span, repeat, val_repeat, open, close from todo join todo_keep where dependency = -1 and span <> val_span and repeat <> val_repeat and todo.id = todo_keep.id and tid = ? and iid = ?',
(tid, uid))
return [[
id, decode(name), span, val_span, repeat, val_repeat, open, close
] for id, name, span, val_span, repeat, val_repeat, open, close in
c.fetchall()]
def get_pow(db, pid):
c = db.cursor()
# check
c.execute('select proof, note, timestamp from pow where id = ?', (pid, ))
row = c.fetchone()
if None == row:
return False
proof, note, timestamp = row
c.execute('select base64 from pow_img where id = ?', (pid, ))
img = c.fetchone()
if None == img:
return decode(proof), decode(note), timestamp, hash(proof), hash(
note), None
else:
return decode(proof), decode(note), timestamp, hash(proof), hash(
note), img[0]
def dump_user_visible_hub(db):
c = db.cursor()
c.execute(
'select id, name from user where name is not null and visible = 1')
return [[id, decode(name)] for id, name in c.fetchall()]
def proof(db, uid, todoid, note, visible, img=None):
c = db.cursor()
# validate instructor and non-finished
c.execute(
'select span, val_span, repeat, val_repeat, open, close, rate, name, is_loop from todo_keep join todo where iid = ? and val_span <> span and val_repeat <> repeat and todo.id = todo_keep.id and todo_keep.id = ?',
(uid, todoid))
row = c.fetchone()
#print(row)
if None == row:
return False
# check tim
span, val_span, repeat, val_repeat, open, close, rate, name, is_loop = row
curr = int(time.strftime('%H%M'))
if curr < open or curr > close:
return False
# check if finished
if val_repeat + 1 == repeat:
# daily finished
if val_span + 1 == span:
# all finished
if 0 != is_loop:
# loop, update to zero
c.execute(
'update todo_keep set val_span = 0, val_repeat = val_repeat + 1 where id = ?',
(todoid, ))
else:
# non-loop, set to finished
c.execute(
'update todo_keep set val_span = val_span + 1, val_repeat = val_repeat + 1 where id = ?',
(todoid, ))
c.execute('update todo set is_finished = 1 where id = ?',
(todoid, ))
# release dependency anyway
c.execute('update todo set dependency = -1 where dependency = ?',
(todoid, ))
# add credit
c.execute('update user set hold = hold + ? where id = ?',
(rate, uid))
else:
# only daily finished
c.execute(
'update todo_keep set val_span = val_span + 1, val_repeat = val_repeat + 1 where id = ?',
(todoid, ))
else:
# regular update
c.execute(
'update todo_keep set val_repeat = val_repeat + 1 where id = ?',
(todoid, ))
# proof
c.execute('select name from user where id = ?', (uid, ))
uname = decode(c.fetchone()[0])
if val_span + 1 == span and val_repeat + 1 == repeat:
# daily finished
c.execute(
'insert into pow(uid, todoid, note, proof, is_public, timestamp) values(?, ?, ?, ?, ?, datetime("now", "localtime"))',
(uid, todoid, encode(note),
encode('Keep Proof by ' + uname + ': ' + decode(name) +
'day %d of %d, daily %d of %d with %lf credit(s).' %
(val_span + 1, span, val_repeat + 1, repeat, rate)),
visible))
else:
# half way done
c.execute(
'insert into pow(uid, todoid, note, proof, is_public, timestamp) values(?, ?, ?, ?, ?, datetime("now", "localtime"))',
(uid, todoid, encode(note),
encode('Keep Proof by ' + uname + ': ' + decode(name) +
'day %d of %d, daily %d of %d with no credit.' %
(val_span + 1, span, val_repeat + 1, repeat)), visible))
if img is not None:
c.execute('insert into pow_img(id, base64) values(?, ?)',
(c.lastrowid, encode(img)))
db.commit()
return True
def get_friend(db, uid):
c = db.cursor()
c.execute('select user_bond.iid, name from user join user_bond where user.id = iid and uid = ?', (uid,))
return [[id, decode(name)] for id, name in c.fetchall()]
def get_by_uid_instructed(db, uid):
c = db.cursor()
c.execute('select todo.id, name, val, goal from todo join todo_sport where dependency = -1 and todo.id = todo_sport.id and tid = ? and uid <> iid and uid = ?', (tid, uid))
return [[id, decode(name), val, goal] for id, name, val, goal in c.fetchall()]
def dump_pow_user(db, uid):
c = db.cursor()
c.execute('select id, proof, timestamp, trash from pow where uid = ?',
(uid, ))
return [[id, decode(proof), timestamp, trash]
for id, proof, timestamp, trash in c.fetchall()]
def get_uinfo(db, uid):
c = db.cursor()
c.execute('select name, motto, hold from user where id = ?', (uid, ))
name, motto, hold = c.fetchone()
return decode(name), decode(motto), hold
def credit_get_inprogress(db, uid, hold):
c = db.cursor()
c.execute(
'select name, price, ? from credit where dependency = -1 and is_spent = 0 and ? < price and uid = ?',
(hold, hold, uid))
return [[decode(name), price, u] for name, price, u in c.fetchall()]
def dump_credit_user(db, uid):
c = db.cursor()
c.execute('select id, name, trash from credit where uid = ?', (uid, ))
return [[id, decode(name), trash] for id, name, trash in c.fetchall()]
def credit_get_pending(db, uid):
c = db.cursor()
c.execute(
'select name, price from credit where dependency <> -1 and uid = ?',
(uid, ))
return [[decode(name), price] for name, price in c.fetchall()]
def credit_get_avaliable(db, uid, hold):
c = db.cursor()
c.execute(
'select id, name, price from credit where dependency = -1 and is_spent = 0 and ? >= price and uid = ?',
(hold, uid))
return [[id, decode(name), price] for id, name, price in c.fetchall()]
def dump_todo_user_finished(db, uid):
c = db.cursor()
c.execute(
'select todo.id, todo_t.name, todo.name from todo join todo_t where todo.tid = todo_t.id and is_finished <> 0 and uid = ?',
(uid, ))
return [[id, tname, decode(name)] for id, tname, name in c.fetchall()]
def get_user_avali_credit(db, uid):
c = db.cursor()
c.execute('select id, name from credit where is_spent = 0 and uid = ?',
(uid, ))
return [[id, decode(name)] for id, name in c.fetchall()]
def get_nfinished_by_uid(db, uid):
c = db.cursor()
c.execute('select id, name from todo where is_finished = 0 and uid = ?',
(uid, ))
return [[id, decode(name)] for id, name in c.fetchall()]
def dump_user(db):
c = db.cursor()
c.execute('select name, motto from user where name is not null')
return [[decode(name), motto] for name, motto in c.fetchall()]
def dump_user_visible(db):
c = db.cursor()
c.execute(
'select name, motto from user where name is not null and visible = 1')
return [[decode(name), decode(motto)] for name, motto in c.fetchall()]
