def fetch(date_from=None, date_to=None, limit=None): db = get_db() base_query = 'SELECT * FROM entries {0} ORDER BY start_at{1}' clauses = ['WHERE'] limit_clause = '' values = [] date_from = date_from or str(date.today()) start_at_from = timing.parse(date_from, '00:00') start_at_to = timing.parse(date_to or str(date.today()), '23:59') if start_at_from > start_at_to: raise ValueError('invalid date range') if start_at_from: clauses.append('start_at >= ? AND') values.append(start_at_from) clauses.append('start_at <= ?') values.append(start_at_to) if limit: limit_clause = ' LIMIT ?;' values.append(int(limit)) query = base_query.format(' '.join(clauses), limit_clause) with db: return (start_at_from, start_at_to, db.execute(query, values))
def operate_on_current(func): db = get_db() with db: entry = get_current(db) if not entry: raise ValueError('cannot find current tracking entry') return func(db, entry)
def delete(entry_id): """ delete an entry specified by entry_id from database returns True if deleted """ entry_id = int(entry_id) db = get_db() with db: cursor = db.execute('DELETE FROM entries WHERE id = ?', (entry_id,)) return (cursor.rowcount > 0)
def update_description(entry_id, event): """ updates the description of an existing tracking entry returns True if updated successfully """ entry_id = int(entry_id) db = get_db() with db: cursor = db.execute('UPDATE entries SET event = ? WHERE id = ?;', (event, entry_id)) return (cursor.rowcount > 0)
def start(event, start_at=None): if start_at: start_at = timing.parse(str(date.today()), start_at) else: start_at = datetime.now() db = get_db() with db: cursor = db.execute('INSERT INTO entries (event, start_at, end_at) VALUES (?, ?, ?);', (event, start_at, NOT_FINISHED)) return cursor.lastrowid
def find(cls, beg, end): beg = datetime.strptime(beg, '%Y%m%d') if end != 'now': end = datetime.strptime(end, '%Y%m%d') else: end = datetime.now() db = get_db() with db: rs = db.execute("select * from billdb where datetime(time) > ? " " and datetime(time) <= ?;", (beg, end)).fetchall() return [cls(*r) for r in rs] if rs else []
def create(event, start_at, end_at, date_str=None): """ creates a new tracking entry and saves to database returns the id of newly created entry if success, otherwise None """ start_at = timing.parse(date_str, start_at) end_at = timing.parse(date_str, end_at) duration = timing.calc_duration(start_at, end_at) db = get_db() with db: cursor = db.execute( 'INSERT INTO entries (event, start_at, end_at, duration) VALUES (?, ?, ?, ?);', (event, start_at, end_at, duration) ) return cursor.lastrowid
def update(entry_id, event, start_at, end_at, date_str=None): """ updates an existing tracking entry in the database returns True if updated successfully """ entry_id = int(entry_id) start_at = timing.parse(date_str, start_at) end_at = timing.parse(date_str, end_at) duration = timing.calc_duration(start_at, end_at) db = get_db() with db: cursor = db.execute( 'UPDATE entries SET event = ?, start_at = ?, end_at = ?, duration = ? WHERE id = ?;', (event, start_at, end_at, duration, entry_id) ) return (cursor.rowcount > 0)
def find(cls, beg, end): db = get_db() with db: rs = db.execute("select * from billdb where datetime(time) > ? and datetime(time) <= ?;", (datetime.strptime(beg, '%Y-%m-%d'), datetime.strptime(end, '%Y-%m-%d'))).fetchall() return [Bill(r[0], r[1], r[2], r[3]) for r in rs] if rs else []
def add(cls, cost, comment): db = get_db() with db: rs = db.execute("insert into billdb (time, cost, comment) values (?, ?, ?);", (datetime.now(), cost, comment)) return rs.lastrowid if rs else None
def get(cls, id): db = get_db() with db: rs = db.execute("select `id`, `time`, `cost`, `comment` from billdb where `id`= ?", (id,)).fetchone() return Bill(rs[0], rs[1], rs[2], rs[3]) if rs else None
def get(cls, id): db = get_db() with db: rs = db.execute("select `id`, `time`, `cost`, " " `comment` from billdb where `id`= ?", (id,)).fetchone() return cls(*rs) if rs else None