def get_balance(idAcc):
conn = mysql.connect()
cursor = conn.cursor()
sql = """SELECT balance FROM accounts WHERE idAccounts = %s """
cursor.execute(sql, [idAcc])
data = cursor.fetchone()
return data[0]
def get_account_of_idCreditCards(idCreditCard):
conn = mysql.connect()
cursor = conn.cursor()
sql = """select idAccounts from credit_cards where idCreditCards= %s """
cursor.execute(sql, [idCreditCard])
data = cursor.fetchone()
return data[0]
def limit():
if not is_input_json(request, ['idCard', 'limit']):
return jsonify({"msg": "Missing or bad JSON in request."}), 400
try:
idCard = int(request.json['idCard'])
limit = float(request.json['limit'])
except ValueError:
return jsonify({"msg": "Bad type"})
if not isinstance(idCard, int):
return jsonify({'msg': 'Bad type'}), 401
if limit <= 0:
return jsonify({'msg': 'Limit must be a number.'}), 401
idAcc = get_account_of_idCreditCards(idCard)
if not isinstance(idAcc, int):
return jsonify({"msg": "This card does not exist."}), 401
if not isOwner(get_jwt_identity(), idAcc):
return jsonify({"msg": "Access restricted."}), 401
try:
conn = mysql.connect()
cursor = conn.cursor()
sql = """SELECT limit """
# Dodawanie karty do bd
sql = """UPDATE credit_cards SET maximumLimit = %s WHERE idCreditCards = %s"""
cursor.execute(sql, [limit, idCard])
# commit zmian
conn.commit()
except mysql.connect.Error as error:
# przy wystąpieniu jakiegoś błędu, odrzucenie transakcji
cursor.rollback()
return jsonify({
'msg': "Connect with Data Base unsuccessfully.",
'error': error
}), 401
finally:
cursor.close()
conn.close()
return jsonify({'msg': "The limit has been changed."}), 200
def get_info_about_transcation(idTransactions, type):
conn = mysql.connect()
cursor = conn.cursor()
# spradzanie czy na wejściu jest int czy lista
idTrans = idTransactions
if isinstance(idTrans, int):
idTransactions = []
idTransactions.append(idTrans)
myJson = []
simpleData = []
for id in idTransactions:
sql = """SELECT idAccounts, idAccountsOfRecipient, amountOfTransaction, date, old_balance, new_balance,
message FROM transactions WHERE idTransactions = %s """
cursor.execute(sql, [id])
data = cursor.fetchone()
userData = []
for row in data:
userData.append(row)
simpleData.append(row)
myJson.append({
'idTransactions ':
id,
'accountNumber':
idAccount_to_account_number(userData[0]),
'accountNumberOfRecipient':
idAccount_to_account_number(userData[1]),
'amountOfTransaction':
userData[2],
'date':
userData[3],
'old_balance':
userData[4],
'new_balance':
userData[5],
'message':
userData[6]
})
if type == 'JSON':
return jsonify(myJson)
else:
return simpleData
def get_info_about_cards(idCards):
myJson = []
conn = mysql.connect()
cursor = conn.cursor()
for id in idCards:
sql = """select idAccounts, maximumLimit, expiryDate from credit_cards where idCreditCards= %s """
cursor.execute(sql, [id])
data = cursor.fetchone()
userData = []
for row in data:
userData.append(row)
myJson.append({
'idCreditCards': id,
'idAccounts': userData[0],
'maximumLimit': userData[1],
'expiryDate': userData[2]
})
return jsonify(myJson)
def credit_cards():
conn = mysql.connect()
cursor = conn.cursor()
if request.method == 'GET':
accountsIDs = get_active_idAccounts_Of_Customer(get_jwt_identity())
idCards = []
for id in accountsIDs:
idCards = idCards + get_idCreditCards_of_Account(id)
return get_info_about_cards(idCards)
# Usuwanie karty
elif request.method == 'DELETE':
if not is_input_json(request, ['idCard']):
return jsonify({"msg": "Missing or bad JSON in request."}), 400
idCard = request.json['idCard']
if not isinstance(idCard, int):
return jsonify({'msg': 'Bad type'}), 401
idAcc = get_account_of_idCreditCards(idCard)
if not isinstance(idAcc, int):
return jsonify({"msg": "This card does not exist."}), 401
if not isOwner(get_jwt_identity(), idAcc):
return jsonify({"msg": "Access restricted"}), 401
# rozpoczęcie transakcji
try:
# Usuwanie karty z bd
sql = """DELETE FROM credit_cards where idCreditCards = %s"""
cursor.execute(sql, [idCard])
# commit zmian
conn.commit()
except mysql.connect.Error as error:
# przy wystąpieniu jakiegoś błędu, odrzucenie transakcji
cursor.rollback()
return jsonify({
'msg': "Connect with Data Base unsuccessfully.",
'error': error
}), 401
finally:
cursor.close()
conn.close()
return jsonify({'msg': "The card has been deleted."}), 200
elif request.method == 'POST':
if not is_input_json(request, ['accountNumber']):
return jsonify({"msg": "Missing or bad JSON in request."}), 400
try:
accountNumber = int(request.json['accountNumber'])
except TypeError:
return jsonify({"msg": "Account id must be a integer"})
idAcc = account_number_to_idAccounts(accountNumber)
if not isOwner(get_jwt_identity(), idAcc):
return jsonify({"msg": "Access restricted."}), 401
# rozpoczęcie transakcji
try:
# Dodawanie karty do bd
sql = """INSERT INTO credit_cards (maximumLimit, expiryDate, idAccounts ) VALUES (%s, %s, %s)"""
cursor.execute(sql, [
5000,
datetime.datetime.now() + datetime.timedelta(days=2 * 365),
idAcc
])
# commit zmian
conn.commit()
except mysql.connect.Error as error:
# przy wystąpieniu jakiegoś błędu, odrzucenie transakcji
cursor.rollback()
return jsonify({
'msg': "Connect with Data Base unsuccessfully.",
'error': error
}), 401
finally:
cursor.close()
conn.close()
return jsonify({'msg': "The card has been added."}), 200
def transfer():
if not is_input_json(request,
['title', 'accountNumber', 'fromAccount', 'amount']):
return jsonify({"msg": "Missing or bad JSON in request."}), 400
title = str(request.json['title'])
if not re.match('^[\s.,?()ąćęśńłóżźa-zA-Z0-9]+$', title):
return jsonify({"msg": "Allowed special characters are ,.?()"}), 401
# Rzutowanie numerów kont na int
try:
toAccountNum = int(request.json['accountNumber'])
fromAccountNum = int((request.json['fromAccount']))
except ValueError:
return jsonify({'msg': 'The account number must be a number.'}), 401
if toAccountNum == fromAccountNum:
return jsonify(
{'msg': 'Transfer between the same accounts is not allowed.'}), 401
# sprawdzanie czy kwota ma odpowiedni typ i jest dodatnia
try:
amount = float(request.json['amount'])
amount = round_down(amount, 2)
if amount <= 0:
return jsonify(
{'msg':
'Amount of the transfer must be a positive number.'}), 401
except ValueError:
return jsonify({'msg':
'Amount of the transfer must be a number.'}), 401
# przypisanie idAccount na podstawie numeru konta
senderId = account_number_to_idAccounts(fromAccountNum)
recipientId = account_number_to_idAccounts(toAccountNum)
# sprawdzanie czy do numerów są przypisane jakieś konta
if recipientId is None or senderId is None:
return jsonify({'msg': 'This account does not exist.'}), 401
if not is_active_account(recipientId):
return jsonify({'msg': 'This account does not exist.'}), 401
# sprawdzanie czy dane konto należy do zalogowanego użytkownika
if not isOwner(get_jwt_identity(), senderId):
return jsonify({'msg': 'Restricted access.'}), 401
# rozpoczęcie transakcji
try:
conn = mysql.connect()
cursor = conn.cursor()
# sprawdzenie czy na kocie jest wystarczająco pięniędzy
if not has_money(senderId, amount):
return jsonify({'msg': "Not enough money on the account."}), 401
else:
old_balance = get_balance(senderId)
new_balance = get_balance_after_transfer(senderId, amount)
# aktualizacja stanu konta u wysyłającego
sql = """UPDATE accounts SET balance=(balance-%s) where idAccounts = %s"""
cursor.execute(sql, [amount, senderId])
# aktualizacja stanu konta u odbiorcy
sql = """UPDATE accounts SET balance=(balance+%s) where idAccounts = %s"""
cursor.execute(sql, [amount, recipientId])
# dodanie do wpisu o transakcji
sql = """INSERT INTO transactions (date, amountOfTransaction, idAccounts, idAccountsOfRecipient,
old_balance, new_balance, message, idCreditCards) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""
cursor.execute(sql, [
datetime.now(), amount, senderId, recipientId, old_balance,
new_balance, title, None
])
conn.commit()
cursor.close()
conn.close()
return jsonify({'msg': "Transfer approved"}), 200
except mysql.connect.Error as error:
# przy wystąpieniu jakiegoś błędu, odrzucenie transakcji
cursor.rollback()
cursor.close()
conn.close()
return jsonify({'msg': "Transfer rejected", 'error': error}), 401
def accountsOfCustomer():
conn = mysql.connect()
cursor = conn.cursor()
if request.method == 'GET':
# wpisanie do tablicy wszyskich informacji o koncie o danym id
accountsIDs = get_active_idAccounts_Of_Customer(get_jwt_identity())
myJson = []
for idAcc in accountsIDs:
sql = """select number, dataOpened, balance from accounts where idAccounts= %s """
cursor.execute(sql, [idAcc])
data = cursor.fetchone()
userData = []
for row in data:
userData.append(row)
myJson.append({
'idAccounts': idAcc,
'number': userData[0],
'dataOpened': userData[1],
'balance': userData[2]
})
return jsonify(myJson)
# Usuwanie konta
elif request.method == 'DELETE':
if not is_input_json(request, ['idAccounts']):
return jsonify({"msg": "Missing or bad JSON in request."}), 400
try:
idAccounts = int(request.json['idAccounts'])
except ValueError:
return jsonify({"msg": "Bad type."}), 400
if not isOwner(get_jwt_identity(), idAccounts):
return jsonify({'msg': 'Restricted access'}), 401
# rozpoczęcie transakcji
ACCOUNT_DELETED = False
try:
sql = """SELECT balance FROM accounts WHERE idAccounts = %s"""
cursor.execute(sql, [idAccounts])
data = cursor.fetchone()
if data[0] == 0:
# Usuwanie konta - triggery w BD zadbają, żeby usunąć wszystkie wpisy powiązane z tym kontem
sql = """DELETE FROM owners WHERE idAccounts = %s"""
cursor.execute(sql, [idAccounts])
# commit zmian
conn.commit()
ACCOUNT_DELETED = True
except mysql.connect.Error as error:
cursor.rollback()
return jsonify({
'msg': "Connect with Data Base unsuccessfully.",
'error': error
}), 401
finally:
cursor.close()
conn.close()
if ACCOUNT_DELETED:
return jsonify({'msg': "The account has been deleted."}), 200
else:
return jsonify({
"msg":
"The account cannot be deleted. Transfer the money first."
}), 401
# Dodawanie kont
elif request.method == 'POST':
try:
# Dodawanie konta do bd
sql = """INSERT INTO owners (idCustomers) VALUES (%s)"""
cursor.execute(sql, [get_jwt_identity()])
conn.commit()
cursor = conn.cursor()
sql = """UPDATE accounts SET dataOpened = %s WHERE dataOpened IS NULL"""
cursor.execute(sql, [datetime.now()])
conn.commit()
except mysql.connect.Error as error:
cursor.rollback()
return jsonify({
'msg': "Connect with Data Base unsuccessfully.",
'error': error
}), 401
finally:
cursor.close()
conn.close()
return jsonify({'msg': "The account has been added"}), 200
def transactionsFilter():
if is_input_json(request, ['limit', 'offset']):
try:
limit = int(request.json['limit'])
offset = int(request.json['offset'])
if limit < 0 or offset < 0:
return jsonify(
{'msg': 'Limit and offset must be positive numbers.'}), 401
except ValueError:
return jsonify({'msg': 'Limit and offset must be numbers..'}), 401
else:
return jsonify({"msg": "Missing or bad JSON in request."}), 400
CUSTOMER_NUMBER_FILTER = False
if is_input_json(request, ['customerNumber']):
try:
customerNumber = int(request.json['customerNumber'])
idAccCustomer = account_number_to_idAccounts(customerNumber)
if isOwner(get_jwt_identity(), idAccCustomer):
CUSTOMER_NUMBER_FILTER = True
else:
return jsonify({'msg': 'Access restricted.'}), 401
except ValueError:
return jsonify({'msg':
'The account number must be a number.'}), 401
else:
return jsonify({"msg": "Missing or bad JSON in request."}), 400
FOREIGN_NUMBER_FILTER = False
if is_input_json(request, ['foreignNumber']):
try:
foreignNumber = int(request.json['foreignNumber'])
idAccForeign = account_number_to_idAccounts(foreignNumber)
FOREIGN_NUMBER_FILTER = True
except ValueError:
return jsonify({'msg':
'The account number must be a number.'}), 401
FROM_DATE_FILTER = False
if is_input_json(request, ['fromDate']):
try:
fromDate = datetime.datetime.strptime(request.json['fromDate'],
'%Y-%m-%d %H:%M')
if isinstance(fromDate, datetime.date):
FROM_DATE_FILTER = True
else:
return jsonify({"msg": "Bad type of date."}), 400
except ValueError:
return jsonify({"msg": "Date is required."}), 400
TO_DATE_FILTER = False
if is_input_json(request, ['toDate']):
try:
toDate = datetime.datetime.strptime(request.json['toDate'],
'%Y-%m-%d %H:%M')
if isinstance(toDate, datetime.date):
TO_DATE_FILTER = True
else:
return jsonify({"msg": "Bad type of date."}), 400
except ValueError:
return jsonify({"msg": "Date is required."}), 400
CREDIT_CARD_FILTER = False
if is_input_json(request, ['idCreditCard']):
try:
idCreditCard = int(request.json['idCreditCard'])
CREDIT_CARD_FILTER = True
except ValueError:
return jsonify({'msg': 'The card id must be a number.'}), 401
FROM_AMOUNT_FILTER = False
if is_input_json(request, ['fromAmount']):
try:
fromAmount = float(request.json['fromAmount'])
if fromAmount >= 0:
FROM_AMOUNT_FILTER = True
else:
return jsonify(
{'msg': 'The amount must be a positive number.'}), 401
except ValueError:
return jsonify({'msg': 'The amount must be a number.'}), 401
TO_AMOUNT_FILTER = False
if is_input_json(request, ['toAmount']):
try:
toAmount = float(request.json['toAmount'])
if toAmount > 0:
TO_AMOUNT_FILTER = True
else:
return jsonify(
{'msg': 'The amount must be a positive number.'}), 401
except ValueError:
return jsonify({'msg': 'The amount must be a number.'}), 401
conn = mysql.connect()
cursor = conn.cursor()
bindingTable = []
mainSQL = """SELECT idAccounts, idAccountsOfRecipient, amountOfTransaction, date, old_balance, new_balance,
message, idTransactions, idCreditCards FROM transactions WHERE """
countSQL = """SELECT count(idTransactions) FROM transactions WHERE """
sql = ''
if FROM_DATE_FILTER and TO_DATE_FILTER:
if fromDate < toDate:
sql += """ (date BETWEEN %s AND %s) AND """
bindingTable.append(fromDate)
bindingTable.append(toDate)
else:
return jsonify({"msg": "ToDate must be a later date."}), 401
else:
if FROM_DATE_FILTER:
sql += " (date >= %s) AND "
bindingTable.append(fromDate)
elif TO_DATE_FILTER:
sql += " (date <= %s) AND "
bindingTable.append(toDate)
if FOREIGN_NUMBER_FILTER:
sql += """ (idAccounts = %s OR idAccountsOfRecipient = %s) AND """
bindingTable.append(idAccForeign)
bindingTable.append(idAccForeign)
if CREDIT_CARD_FILTER:
sql += """ (idCreditCards = %s) AND """
bindingTable.append(idCreditCard)
if FROM_AMOUNT_FILTER and TO_AMOUNT_FILTER:
if fromAmount < toAmount:
sql += """ (amountOfTransaction BETWEEN %s AND %s) AND """
bindingTable.append(fromAmount)
bindingTable.append(toAmount)
else:
return jsonify({"msg": "ToAmount must be a bigger number."}), 401
else:
if FROM_AMOUNT_FILTER:
sql += """ (amountOfTransaction > %s) AND """
bindingTable.append(fromAmount)
elif TO_AMOUNT_FILTER:
sql += """ (amountOfTransaction < %s) AND """
bindingTable.append(toAmount)
if CUSTOMER_NUMBER_FILTER:
idAccCustomer = account_number_to_idAccounts(customerNumber)
sql += """ (idAccounts = %s OR idAccountsOfRecipient = %s) """
bindingTable.append(idAccCustomer)
bindingTable.append(idAccCustomer)
myJson = []
# pobranie liczby wierszy, które przeszły przez filtr
countSQL += sql
cursor.execute(countSQL, bindingTable)
record = cursor.fetchone()
myJson.append({'rowsNumber': record[0]})
mainSQL += sql
# wiersz, które spełniają filtry
mainSQL += """ ORDER BY date DESC LIMIT %s OFFSET %s"""
bindingTable.append(limit)
bindingTable.append(offset)
cursor.execute(mainSQL, bindingTable)
records = cursor.fetchall()
for row in records:
myJson.append({
'idAccounts':
idAccount_to_account_number(row[0]),
'idAccountsOfRecipient':
idAccount_to_account_number(row[1]),
'amountOfTransaction':
row[2],
'date':
row[3],
'old_balance':
row[4],
'new_balance':
row[5],
'message':
row[6],
'idTransactions':
row[7],
'idCreditCards':
row[8]
})
return jsonify(myJson), 200