def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(sqlite3.ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e.value)
def app():
db_fd, db_path = tempfile.mkstemp()
app = create_app({
'TESTING': True,
'DATABASE': db_path,
})
with app.app_context():
init_db()
get_db().executescript(_data_sql)
yield app
os.close(db_fd)
os.unlink(db_path)
def show_today():
db = get_db()
todos = db.execute(
' SELECT i.id, note, due_date, done, user_id'
' FROM item i JOIN user u ON i.user_id = u.id '
' WHERE i.due_date IS (?) AND user_id = (?)', (datetime.today().strftime('%Y-%m-%d'), g.user['id'])
).fetchall()
return render_template('todo/today.html', todos = todos)
def show_report():
db = get_db()
dates = db.execute(
' SELECT i.due_date, COUNT(*) count_all, SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) count_done, '
' ROUND((1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100, 1) AS percentage '
' FROM item i JOIN user u ON i.user_id = u.id '
' WHERE i.user_id = (?) '
' GROUP BY due_date '
' ORDER BY due_date DESC', (g.user['id'], )).fetchall()
return render_template('reports/list.html', dates=dates)
def apex_charts_daily_data():
db = get_db()
today = date.today().strftime("%Y-%m-%d")
data = db.execute(
' SELECT (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 percentage '
' FROM item i JOIN user u ON i.user_id = u.id WHERE i.due_date = (?) AND i.user_id = (?)',
(today, g.user['id'])).fetchone()
data = dict(data)
data["percentage"] = round(data["percentage"], 1)
return jsonify(data)
def apex_charts_data():
db = get_db()
data = db.execute(
' SELECT i.due_date, (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 percentage '
' FROM item i JOIN user u ON i.user_id = u.id WHERE i.user_id = (?) GROUP BY due_date ',
(g.user['id'], )).fetchall()
data = dict(data)
new_data = {}
for k, v in zip(data.keys(), data.values()):
new_data[k] = round(v, 1)
return jsonify(new_data)
def test_register(client, app):
assert client.get('/auth/register').status_code == 200
response = client.post('/auth/register',
data={
'username': '******',
'password': '******'
})
assert 'http://localhost/auth/login' == response.headers['Location']
with app.app_context():
assert get_db().execute(
"select * from user where username = '******'", ).fetchone() is not None
def export_to_csv():
date_from = request.form['date_from']
date_to = request.form['date_to']
db = get_db()
dates = db.execute(
' SELECT i.due_date, COUNT(*) count_all, SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) count_done, '
' (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 AS percentage '
' FROM item i JOIN user u ON i.user_id = u.id '
' WHERE i.user_id = (?) AND (i.due_date BETWEEN (?) AND (?))'
' GROUP BY due_date '
' ORDER BY due_date DESC',
(g.user['id'], date_from, date_to)).fetchall()
io_file = io.StringIO()
csv_writer = csv.writer(io_file)
csv_writer.writerows(dates)
output = make_response(io_file.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=export.csv"
output.headers["Content-type"] = "text/csv"
return output
def create():
if request.method == 'POST':
note = request.form['note']
due_date = request.form['due_date']
error = None
if not note:
error = 'Note is required.'
if not due_date:
error = 'Due date is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO item (note, due_date, user_id)'
' VALUES (?, ?, ?)', (note, due_date, g.user['id'])
)
db.commit()
return redirect(url_for('todo.index'))
return render_template('todo/create.html')
def undone(id):
db = get_db()
db.execute('UPDATE item SET done = ? WHERE id = ?', (0, id))
db.commit()
return redirect(url_for('todo.show_today'))
def delete(id):
db = get_db()
db.execute('DELETE FROM item WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('todo.index'))
def index():
db = get_db()
todos = db.execute(
'SELECT i.id, note, due_date, done, user_id'
' FROM item i JOIN user u ON i.user_id = u.id WHERE user_id = (?)', (g.user['id'],)).fetchall()
return render_template('todo/index.html', todos = todos)
