def index():
cur = g.db.execute('''
SELECT title, description
FROM projects
ORDER BY id DESC;''')
projs = [r for r in dict_gen(cur)]
cur = g.db.execute('''
SELECT name, department
FROM clients
ORDER BY id DESC;''')
clients = [r for r in dict_gen(cur)]
return render_template('index.html', clients=clients, projs=projs)
def clients():
form = forms.ClientCreateForm(request.form)
if request.method == 'POST' and form.validate():
name = form.name.data
department = form.department.data
g.db.execute('''
INSERT INTO
clients (name, department, created, updated)
VALUES (?,?,?,?)''',
(name, department, datetime.now(), datetime.now())
)
flash('Client added')
g.db.commit()
return redirect(url_for('clients'))
cur = g.db.execute('''
SELECT
name, department, created, updated
FROM
clients
ORDER BY
id DESC;''')
clients = [r for r in dict_gen(cur)]
return render_template('clients.html', clients=clients, form=form)
def edit_client():
''' Delete/edit a client. '''
if 'user_id' not in session:
abort(401)
cur = g.db.execute('''
SELECT
id, name, department
FROM
clients
ORDER BY
id DESC;''')
clients = [r for r in dict_gen(cur)]
if request.method == 'POST':
if 'delete' in request.form:
cur = g.db.execute('''DELETE FROM clients WHERE id = ?''',
request.form['id'])
g.db.commit()
flash('Client deleted')
return redirect(url_for('index'))
id = request.form['id']
name = request.form['name']
department = request.form['department']
g.db.execute('''
UPDATE clients
SET name = ?, department = ?, updated = ?
WHERE id = ?''',
(name, department, datetime.now(), id)
)
g.db.commit()
flash('Client edited')
return redirect(url_for('edit_client'))
return render_template('edit_client.html', clients=clients)
def edit_project():
''' Delete/edit a project. '''
if 'user_id' not in session:
abort(401)
cur = g.db.execute('''
SELECT
id, title, description, client_id
FROM
projects
ORDER BY
id DESC;''')
projs = [r for r in dict_gen(cur)]
qry = '''
SELECT id, name
FROM clients
WHERE id IN (%s)''' % ','.join('?'*len(projs))
cur = g.db.execute(qry, [proj['id'] for proj in projs])
clients = [r for r in dict_gen(cur)]
if request.method == 'POST':
if 'delete' in request.form:
cur = g.db.execute('''DELETE FROM projects WHERE id = ?''',
request.form['id'])
g.db.commit()
flash('Project deleted')
return redirect(url_for('index'))
id = request.form['id']
title = request.form['title']
description = request.form['description']
client_id = request.form['client_id']
g.db.execute('''
UPDATE projects
SET title = ?, description = ?,
client_id = ?, updated = ?
WHERE id = ?''',
(title, description, client_id, datetime.now(), id)
)
g.db.commit()
flash('Project edited')
return redirect(url_for('edit_project'))
return render_template('edit_project.html', projs=projs, clients=clients)
def projects():
''' Interface to add a project, and optionally a client.
Unfortunately this view has some slightly complex logic.
'''
form = forms.ProjectCreateForm(request.form)
cur = g.db.execute('''
SELECT
id, name
FROM
clients
ORDER BY
name;''')
# blank option
# so that a project without a client can be created
choices = [(0, '(choose/insert a client)')]
try:
choices.extend((row[0], row[1]) for row in cur.fetchall())
except TypeError:
# nothing in db yet!
pass
# populate choices for <select> element of form
form.client_id.choices = choices
if request.method == 'POST' and form.validate():
if len(form.name.data) != 0 or len(form.department.data) != 0:
name = form.name.data
department = form.department.data
g.db.execute('''
INSERT INTO
clients (name, department, created, updated)
VALUES (?,?,?,?)''',
(name, department, datetime.now(), datetime.now())
)
g.db.commit()
# fetch most recently inserted client.id
max_cid = g.db.execute('''
SELECT MAX(id)
FROM clients;''').fetchone()[0]
title = form.title.data
description = form.description.data
# get the most recently inserted client;
# if that doesn't work, grab data from form
try:
client_id = max_cid
except NameError:
client_id = form.client_id.data
# don't insert a client id
# if name is empty
try:
g.db.execute('''
INSERT INTO
projects (title, description, client_id,
created, updated)
VALUES (?,?,?,?,?)''',
(title, description, client_id, datetime.now(),
datetime.now())
)
except NameError: # no `client_id` yet!
g.db.execute('''
INSERT INTO
projects (title, description, created, updated)
VALUES (?,?,?,?)''',
(title, description, datetime.now(), datetime.now())
)
flash('Project added')
g.db.commit()
return redirect(url_for('projects'))
cur = g.db.execute('''
SELECT
p.title, p.description, c.name, p.created, p.updated
FROM
projects p
LEFT JOIN clients c
ON c.id = p.client_id
ORDER BY
p.id DESC;''')
projs = [r for r in dict_gen(cur)]
return render_template('projects.html', projs=projs, form=form)