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