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
