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 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 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 func(db, entry):
start_at = entry['start_at']
if _end_at:
end_at = timing.parse(start_at.strftime('%Y-%m-%d'), _end_at)
else:
end_at = datetime.now()
if end_at.day == start_at.day:
duration = timing.calc_duration(start_at, end_at)
return db.execute('UPDATE entries SET end_at = ?, duration = ? WHERE id = ?', (end_at, duration, entry['id']))
else:
prev_end_at = start_at.replace(hour=23, minute=59, second=00)
duration = timing.calc_duration(start_at, prev_end_at)
cursor = db.execute('UPDATE entries SET end_at = ?, duration = ? WHERE id = ?', (prev_end_at, duration, entry['id']))
create(entry['event'], '00:00', end_at.strftime('%H:%M'), end_at.strftime('%Y-%m-%d'))
return cursor