def create():
if request.method == 'POST':
provider_id = request.form['provider']
product_id = request.form['product']
quantity = request.form['quantity']
amount = request.form['amount']
expense_date = request.form['expense_date']
error = None
if not provider_id:
error = 'Provider is required.'
if not receipt:
error = 'Receipt number is required.'
if not product_id:
error = 'Product is required.'
if not amount:
error = 'Amount is required.'
if not expense_date:
error = 'Expense date is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO expense (provider_id, receipt_number, product_id, quantity, amount, expense_date)'
' VALUES (?, ?, ?, ?, ?)', (provider_id, receipt, product_id,
quantity, amount, expense_date))
db.commit()
return redirect(url_for('expense.index'))
db = get_db()
providers = db.execute('SELECT id, name'
' FROM provider'
' ORDER BY name').fetchall()
products = db.execute('SELECT id, description'
' FROM product'
' ORDER BY description').fetchall()
return render_template('expense/create.html',
providers=providers,
products=products)
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
cursor = db.cursor()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
cursor.execute(
"SELECT `id` FROM `user` WHERE `username` = %s", [username]
)
if cursor.fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
cursor.execute(
"INSERT INTO `user` (`username`, `password`) VALUES (%s, %s)",[username, generate_password_hash(password)]
)
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def register():
# if method=GET take user to login page
if request.method == 'GET':
return render_template('login/register.html', page_header="Register")
# if method=POST create a new username and password row in the users table
elif request.method == 'POST':
username = request.form.get('username')
password = request.form.get('password')
# do validation on page
# check if user already exists
cnx = get_db()
cursor = cnx.cursor()
query = '''
SELECT login FROM users
WHERE login = %s
'''
cursor.execute(query, (username, ))
if cursor.fetchone() is not None:
return render_template('login/register.html',
page_header="Login",
error_msg="Username already exits",
username=request.form.get('username'))
# if all good, insert new user into users table
query = '''
INSERT INTO users(login, password)
VALUES (%s,%s)'''
cursor.execute(query, (username, password))
cnx.commit()
return redirect(url_for('login'), code=307)
def delete(id):
##payment = get_payment(id)
db = get_db()
db.execute('DELETE FROM payment WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('payment.index'))
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
cursor = db.cursor()
error = None
cursor.execute(
"SELECT * FROM `user` WHERE `username` = %s",
[username]
)
user = cursor.fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def amount():
db = get_db()
cursor = db.cursor()
cursor.execute(
"SELECT `type`, `name`, `model`, `amount` FROM `amount_count` ORDER BY `amount` DESC"
)
cursor.close()
records = cursor.fetchall()
return render_template('count/amount.html', records=records)
def index():
db = get_db()
expenses = db.execute(
'SELECT expense_date, receipt_number, amount, p.name as provider, pr.description as product'
' FROM expense e'
' JOIN provider p on e.provider_id = p.id'
' JOIN product pr on e.product_id = pr.id'
' ORDER BY expense_date DESC').fetchall()
return render_template('expense/index.html', expenses=expenses)
def delete(id):
receipt = get_receipt(id)
db = get_db()
db.execute('DELETE FROM receipt WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('receipt.index'))
def update(id):
if request.method == 'POST':
##issue_date = datetime.strptime(request.form['issue_date'], '%d/%m/%Y')
issue_date = request.form['issue_date']
##iss_date = datetime.strptime(issue_date, '%d/%m/%Y')
status = request.form['status']
error = None
if not issue_date:
error = 'Issue date is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE receipt SET issue_date = ?, status = ?'
' WHERE id = ?', (issue_date, status, id))
db.commit()
return redirect(url_for('receipt.index'))
db = get_db()
receipt = get_receipt(id)
receivers = db.execute('SELECT id, prop_alias, name'
' FROM occupant'
' ORDER BY name').fetchall()
concepts = db.execute('SELECT id, description'
' FROM concept'
#' WHERE status = 1'
' ORDER BY id').fetchall()
return render_template('receipt/update.html',
receipt=receipt,
receivers=receivers,
concepts=concepts)
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
db = get_db()
cursor = db.cursor()
cursor.execute(
"SELECT * FROM `user` WHERE `id` = %s",
[int(user_id)]
)
g.user = cursor.fetchone()
def create():
if request.method == 'POST':
number = request.form['number']
concept = request.form['concept']
receiver = request.form['receiver']
amount = request.form['amount']
issue_date = request.form['issue_date']
status = request.form['status']
error = None
if not number:
error = 'Receipt number is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO receipt (number, concept_id, person_id, amount, issue_date, status)'
' VALUES (?, ?, ?, ?, ?, ?)',
(number, concept, receiver, amount, issue_date, status))
db.commit()
return redirect(url_for('receipt.index'))
db = get_db()
receivers = db.execute('SELECT *'
' FROM occupant'
' ORDER BY name').fetchall()
concepts = db.execute('SELECT id, description'
' FROM concept'
' ORDER BY id').fetchall()
current_app.logger.debug('found concepts: ' + str(len(receivers)))
return render_template('receipt/create.html',
receivers=receivers,
concepts=concepts)
def index():
db = get_db()
receipts = db.execute(
'SELECT r.id, o.id as receiver_id,'
' o.name as receiver, o.prop_alias as property,'
' r.number, c.description concept, r.amount, r.issue_date, r.status'
' FROM receipt r'
' LEFT JOIN occupant o ON r.person_id = o.id'
' LEFT JOIN concept c ON r.concept_id = c.id'
' ORDER BY issue_date DESC').fetchall()
current_app.logger.debug('receipts counter: ' + str(len(receipts)))
return render_template('receipt/index.html', receipts=receipts)
def index():
db = get_db()
payments = db.execute(
'SELECT p.id, o.prop_alias property, o.name as receiver_name,'
' c.description as concept,'
' r.number, r.amount, r.issue_date receipt_date, p.payment_date, p.amount, r.status'
' FROM payment p'
' LEFT JOIN receipt r ON p.receipt_id = r.id'
' LEFT JOIN concept c ON r.concept_id = c.id'
' LEFT JOIN occupant o ON r.person_id = o.id'
' WHERE r.status = "C"'
' ORDER BY p.payment_date DESC').fetchall()
return render_template('payment/index.html', payments=payments)
def update(id):
if request.method == 'POST':
amount = request.form['amount']
payment_date = request.form['payment_date']
status = request.form['status']
error = None
if not payment_date:
error = 'Issue date is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE payment SET payment_date = ?, amount = ?'
' WHERE id = ?', (payment_date, amount, id))
db.commit()
return redirect(url_for('payment.index'))
db = get_db()
payment = get_payment(id)
receivers = db.execute('SELECT id, name, prop_alias'
' FROM occupant'
' ORDER BY name').fetchall()
concepts = db.execute('SELECT id, description'
' FROM concept'
' ORDER BY id').fetchall()
#' WHERE status = 1'
return render_template('payment/update.html',
payment=payment,
receivers=receivers,
concepts=concepts)
def get_receipt(id, check_user=True):
receipt = get_db().execute(
'SELECT r.id, number, c.id as concept_id, c.description as concept_desc,'
' p.id as receiver_id, p.first_name || " " || p.last_name as receiver,'
' pr.description, amount, issue_date, r.status'
' FROM receipt r'
' JOIN person p on r.person_id = p.id'
' JOIN owner o on r.person_id = o.person_id'
' JOIN property pr on o.property_id = pr.id'
' JOIN concept c on r.concept_id = c.id'
' WHERE r.id = ?', (id, )).fetchone()
if receipt is None:
abort(404, "Receipt id {0} does not exist.".format(id))
return receipt
def populate_receipts():
person_id = request.args.get('person_id', 0)
current_app.logger.debug('person_id: ' + person_id)
receipts = get_db().execute(
'SELECT r.id, number || " - " || c.description as concept'
' FROM receipt r'
' JOIN concept c ON r.concept_id = c.id'
' WHERE r.person_id = ?', (person_id, )).fetchall()
current_app.logger.debug('found receipts: ' + str(len(receipts)))
#dict_receipts = [dict(rec) for rec in receipts]
return jsonify(result=[dict(rec) for rec in receipts])
def get_payment(id, check_user=True):
payment = get_db().execute(
'SELECT py.id, py.amount, py.payment_date,'
' c.id as concept_id, c.description as concept_desc,'
' o.id receiver_id, o.name as receiver_name, o.property,'
' r.id as receipt_id, r.number receipt_number, r.amount receipt_amount,'
' r.issue_date as receipt_date, r.status'
' FROM payment py'
' LEFT JOIN receipt r ON py.receipt_id = r.id'
' LEFT JOIN concept c ON r.concept_id = c.id'
' LEFT JOIN occupant o ON r.person_id = o.id'
' WHERE py.id = ?', (id, )).fetchone()
if payment is None:
abort(404, "Receipt id {0} does not exist.".format(id))
return payment
def get_pdf_receipt(id):
receipt = get_db().execute(
'SELECT r.id, number, c.description as concept,'
' p.first_name || " " || p.last_name as owner,'
' pr.description as property, pm.amount, payment_date,'
' pn.text as note, r.status'
' FROM receipt r'
' LEFT JOIN person p ON r.person_id = p.id'
' LEFT JOIN owner o ON r.person_id = o.person_id'
' LEFT JOIN tenant t ON r.person_id = t.person_id'
' LEFT JOIN property pr ON ( o.property_id = pr.id OR t.property_id = pr.id )'
' LEFT JOIN concept c ON r.concept_id = c.id'
' LEFT JOIN payment pm ON r.id = pm.receipt_id'
' LEFT JOIN payment_note pn ON pm.id = pn.payment_id'
' WHERE r.id = ?', (id, )).fetchone()
current_app.logger.debug(receipt)
if receipt is None:
abort(404, "Receipt id {0} does not exist.".format(id))
return receipt
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif db.execute('SELECT id FROM user WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
db.execute('INSERT INTO user (username, password) VALUES (?, ?)',
(username, generate_password_hash(password)))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def create():
if request.method == 'POST':
receipt_id = request.form['receipt']
amount = request.form['amount']
payment_date = request.form['payment_date']
note = request.form['payment_note']
error = None
if not receipt_id:
error = 'Receipt number is required.'
if error is not None:
flash(error)
else:
db = get_db()
##c = db.cursor()
payment_id = db.execute(
'INSERT INTO payment (receipt_id, amount, payment_date)'
' VALUES (?, ?, ?)',
(receipt_id, amount, payment_date)).lastrowid
##payment_id = c.lastrowid
db.commit()
db.execute('UPDATE receipt SET status = ?'
' WHERE id = ?', ('C', receipt_id))
db.commit()
current_app.logger.debug('Payment note: ' + note)
if note:
db.execute(
'INSERT INTO payment_note (payment_id, text)'
' VALUES (?, ?)', (payment_id, note))
db.commit()
return redirect(url_for('payment.index'))
db = get_db()
receipt_id = request.args.get('receipt_id')
if not receipt_id:
receipts = db.execute(
'SELECT r.id, number, c.description, amount, r.status'
' FROM receipt r'
' JOIN concept c ON r.concept_id = c.id'
' ORDER BY number').fetchall()
else:
receipts = db.execute(
'SELECT r.id, number, c.description, amount, r.status'
' FROM receipt r'
' JOIN concept c ON r.concept_id = c.id'
' WHERE r.id = ?', (receipt_id, )).fetchall()
receiver_id = request.args.get('receiver_id')
if not receiver_id:
receivers = db.execute('SELECT id, name, property'
' FROM occupant o'
' ORDER BY name').fetchall()
else:
current_app.logger.debug('receiver_id: ' + str(receiver_id))
receivers = db.execute(
'SELECT id, name, property'
' FROM occupant'
' WHERE id = ?', (receiver_id, )).fetchall()
current_app.logger.debug('receivers: ' + str(len(receivers)))
return render_template('payment/create.html',
receipts=receipts,
receivers=receivers)
