def loadString(self,
stringToLoad,
host_name="v-dev-ubusvr-1",
db_name="cloud_access_logs",
user_name="ryan.dai",
password="******"):
stringToLoad = stringToLoad.rstrip()
if stringToLoad == "":
return
connection = None
try:
connection = psycopg2.connect(
"dbname='%s' user='******' host='%s' password='******'" %
(db_name, user_name, host_name, password))
connection.autocommit = True
cursor = connection.cursor()
cursor.execute("SET search_path TO dbo,public;")
log = ""
linenumber = 0
f = stringToLoad.splitlines()
for row in f:
linenumber += 1
try:
self.loadRow(str(row), cursor)
except (psycopg2.DatabaseError, self.ParseError) as e:
# raise self.ParseError(e)
log += "line " + str(linenumber) + ': ' + str(e) + '\n'
# connection.commit()
if log != "":
raise psycopg2.DatabaseError(log)
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(e)
def get_all(self) -> List[Friend_record]:
cursor = self._connection.cursor(
cursor_factory = psycopg2.extras.DictCursor)
sql = '''SELECT id, user_id, friend_id
FROM friend_record;'''
try:
cursor.execute(sql)
friend_record_list = list()
for row in cursor:
friend_record = Friend_record()
friend_record.id = row['id']
friend_record.user_id = row['user_id']
friend_record.friend_id = row['friend_id']
friend_record_list.append(friend_record)
return friend_record_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def rm_details(rm_name=None, date=None):
'''
Query to get rms' details
:param rm_id: rms to filter by
:param date: date to filter by
:return: list of dictionaries
'''
conditions = []
if rm_name is not None:
conditions.append("rm_name = '{0}')".format(rm_name))
if date is not None:
conditions.append(
"startdate <= '{0}' and enddate > '{0}'".format(date))
query = '''
select rm_username, rm_name, employee_id from rms {0}
'''.format(handyman.aggregate_conditions(conditions))
try:
result = conn.fetch(query)
info = dict()
if len(result) > 0:
for rm_username, rm_name, employee_id in result:
info[rm_username] = {
params.rm_name: rm_name,
params.id: employee_id
}
return info
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_overridden_rates(client_id=None, date=None):
'''
Gets overriden rates
:param date: the date for which the rates should be retrieved for
:param client_id: the clientid(s) the rates should be retrieved for
:return: dictionary -> lists of dictionaries | keyed on clientid
'''
conditions = []
if client_id is not None:
conditions.append(" clientid in ({0}) ".format(
handyman.convert_int_to_string(client_id)))
if date is not None:
conditions.append(
" startdate <= '{0}' and enddate > '{0}' ".format(date))
query = '''
select clientid, product_id, startdate, enddate, buyer_rate, supplier_rate
from rate_overrides
{0}
'''.format(handyman.aggregate_conditions(conditions))
try:
results = conn.fetch(query)
data = dict()
for clientid, productid, startdate, enddate, buyer_rate, supplier_rate in results:
if clientid not in data.keys():
data[clientid] = []
data[clientid].append({
params.financing_product_id: productid,
params.start_date: startdate,
params.end_date: enddate,
params.buyer_rate: buyer_rate,
params.supplier_rate: supplier_rate
})
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_admin_login(username, date=times.current_date()):
'''
Gets login details for admins given a username
:param username: user's username
:return: dictionary with pwd hash, pwd and id
'''
assert isinstance(username, str)
query = '''
select rm_pwd_salt, rm_pwd_hash, employee_id
from rms
where rm_username = '******' and
startdate <= '{1}' and
enddate > '{1}'
'''.format(username, date)
try:
result = conn.fetch(query)
data = dict()
for pwd_salt, pwd_hash, id_no in result:
data = {
params.salt: pwd_salt,
params.hash_password: pwd_hash,
params.id: id_no
}
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_loging_failed) from e
except TypeError as e:
err = 'Username: expected string; received ' + type(username)
raise TypeError(err) from e
def get_login_details(username, date=times.current_date()):
'''
Gets login details for users given a username
:param username: user's username
:param date: date to filter by
:return: dictionary with pwd hash, pwd and id
'''
assert isinstance(username, str)
query = '''
select pwd_salt, pwd_hash, id_no
from authorized_signatories
where username = '******' and
access_start <= '{1}' and
access_end > '{1}'
'''.format(username, date)
try:
result = conn.fetch(query)
data = dict()
for pwd_salt, pwd_hash, id_no in result:
data = {
params.salt: pwd_salt,
params.hash_password: pwd_hash,
params.id: id_no
}
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_loging_failed) from e
except TypeError as e:
err = 'Username: expected string; received ' + type(username)
raise TypeError(err) from e
def get_by_id(self, id: int) -> Product_record:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, order_id, product_id
FROM product_record WHERE id = '{id}';'''
sql = sql.format(id=id)
try:
cursor.execute(sql)
row = cursor.fetchone()
product_record = Product_record()
product_record.id = row['id']
product_record.order_id = row['order_id']
product_record.product_id = row['product_id']
return product_record
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_by_id(self, id: str) -> P_user:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, firstname, surname, middlename, fio, sex, age
FROM p_user WHERE id = '{id}';'''
sql = sql.format(id=id)
try:
cursor.execute(sql)
row = cursor.fetchone()
p_user = P_user()
p_user.id = row['id']
p_user.firstname = row['firstname']
p_user.surname = row['surname']
p_user.middlename = row['middlename']
p_user.fio = row['fio']
p_user.sex = row['sex']
p_user.age = row['age']
return p_user
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def make(self):
'''
Log a financing payment in the database.
Financed amounts are booked as a -ve number.
'''
if self.portfolio.invoice_count() != 1:
err = 'Financing payments can only be made per invoice. ' +\
'Expected 1 invoice; received ' + str(self.portfolio.invoice_count())
raise TypeError(err)
else:
invoice = self.portfolio.get_invoices()[0]
assert isinstance(invoice, Invoice)
payment_to = self.paid_to
if payment_to is None:
payment_to = invoice.supplier_id()
invoice_percentage = round(
self.amount / invoice.invoice_total() * 100, 2)
if 0 < self.amount <= invoice.financing_due():
try:
inject_queries.add_forwarded_payment(
self.payment_date, self.currency, -self.amount,
self.fees, self.payment_method,
payment_to, self.updated_by, self.notes,
invoice.invoice_id(), invoice_percentage)
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(
messages.error_db_query) from e
elif self.amount == 0:
raise ValueError(messages.error_financing_zero)
else:
raise ValueError(messages.error_financing_amount)
def execute(self, connection: 'psycopg2.extensions.connection') -> None:
"""
Execute the batch using the psycopg2 cursor retrieved from the given connection
:raises psycopg2.DatabaseError: if an error is encountered while running the batch's query
"""
self._execution_start_time = datetime.now()
if self._batch_events and self._batch_events._on_execution_started:
self._batch_events._on_execution_started(self)
cursor = self.get_cursor(connection)
try:
cursor.execute(self.batch_text)
self.after_execute(cursor)
except psycopg2.DatabaseError as error:
self._has_error = True
# We just raise the error with primary message and not the cursor stacktrace
raise psycopg2.DatabaseError(error.diag.message_primary) from error
finally:
# We are doing this because when the execute fails for named cursors
# cursor is not activated on the server which results in failure on close
# Hence we are checking if the cursor was really executed for us to close it
if cursor.rowcount != -1 and cursor.rowcount is not None:
cursor.close()
self._has_executed = True
self._execution_end_time = datetime.now()
self._notices = cursor.connection.notices
cursor.connection.notices = []
if self._batch_events and self._batch_events._on_execution_completed:
self._batch_events._on_execution_completed(self)
def get_by_id(self, id: str) -> Price:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, product_id, amount, type_currency_id
FROM price WHERE id = '{id}';'''
sql = sql.format(id=id)
try:
cursor.execute(sql)
row = cursor.fetchone()
price = Price()
price.id = row['id']
price.product_id = row['product_id']
price.amount = row['amount']
price.type_currency_id = row['type_currency_id']
return price
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_all(self) -> List[Price]:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, product_id, amount, type_currency_id
FROM price;'''
try:
cursor.execute(sql)
price_list = list()
for row in cursor:
price = Price()
price.id = row['id']
price.product_id = row['product_id']
price.amount = row['amount']
price.type_currency_id = row['type_currency_id']
price_list.append(price)
return price_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def update(self, price: Price) -> Price:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''UPDATE price
SET product_id = '{product_id}',
amount = '{amount}', type_currency_id = '{type_currency_id}'
WHERE id = {id};'''
sql = sql.format(id=price.id,
product_id=price.product_id,
amount=price.amount,
type_currency_id=price.type_currency_id)
try:
cursor.execute(sql)
self._connection.commit()
return price
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_by_product_id(self, product_id: str) -> List[Price]:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, product_id, amount, type_currency_id
FROM price WHERE product_id = '{product_id}';'''
sql = sql.format(product_id=product_id)
try:
cursor.execute(sql)
price_list = list()
for row in cursor:
price = Price()
price.id = row['id']
price.product_id = row['product_id']
price.amount = row['amount']
price.type_currency_id = row['type_currency_id']
price_list.append(price)
return price_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
def get_by_user_friend_id(self, user_id: str,
friend_id: str) -> Friend_record:
cursor = self._connection.cursor(
cursor_factory = psycopg2.extras.DictCursor)
sql = '''SELECT id, user_id, friend_id
FROM friend_record
WHERE user_id = '{user_id}' and friend_id = '{friend_id}';'''
sql = sql.format(user_id = user_id, friend_id = friend_id)
try:
cursor.execute(sql)
row = cursor.fetchone()
friend_record = Friend_record()
friend_record.id = row['id']
friend_record.user_id = row['user_id']
friend_record.friend_id = row['friend_id']
return friend_record
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def add(self, user: P_user) -> bool:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''INSERT INTO p_user(
firstname, surname, middlename, sex, age)
VALUES ('{firstname}', '{surname}',
'{middlename}', '{sex}', {age})'''
sql = sql.format(firstname=user.firstname,
surname=user.surname,
middlename=user.middlename,
sex=user.sex,
age=user.age)
try:
cursor.execute(sql)
self._connection.commit()
return True
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_all(self) -> List[P_user]:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, firstname, surname, middlename, fio, sex, age
FROM p_user;'''
try:
cursor.execute(sql)
p_user_list = list()
for row in cursor:
p_user = P_user()
p_user.id = row['id']
p_user.firstname = row['firstname']
p_user.surname = row['surname']
p_user.middlename = row['middlename']
p_user.fio = row['fio']
p_user.sex = row['sex']
p_user.age = row['age']
p_user_list.append(p_user)
return p_user_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def receive(self):
'''
Log a repayment of an invoice in the database.
'''
self.portfolio.sort(params.submitted_on)
if self.paid_by is None or type(self.paid_by) is not int:
raise TypeError(messages.error_invalid_payee)
if self.amount == 0:
raise ValueError(messages.error_repayment_zero)
elif self.amount > self.portfolio.total_repayment_due():
raise ValueError(messages.error_repayment_amount)
else:
allocations = []
closures = []
remaining = self.amount
performance_fee = info_queries.get_performance_fee() / 100
invoice_list = self.portfolio.get_invoices()
i = 0
while i < len(invoice_list) and remaining > 0:
invoice = invoice_list[i]
assert isinstance(invoice, Invoice)
if self.amount < invoice.repayment_due():
remaining = 0
principal_to_pay = (self.amount -
invoice.transaction_cost_due()) / (
1 + (invoice.applied_rate() / 100))
discount_fees = round(self.amount - principal_to_pay, 2)
numbers = [
invoice.invoice_id, self.amount,
round(
principal_to_pay / invoice.total_financed() * 100,
2),
invoice.accrual_period(),
invoice.applied_rate(), discount_fees,
discount_fees * performance_fee, principal_to_pay
]
else:
remaining -= invoice.repayment_due()
numbers = [
invoice.invoice_id,
invoice.principal_due(),
round(
invoice.principal_due() /
invoice.total_financed() * 100, 2),
invoice.accrual_period(),
invoice.applied_rate(),
invoice.total_discount_fees(),
invoice.total_discount_fees() * performance_fee,
invoice.principal_due()
]
closures.append(invoice.invoice_id)
i += 1
allocations.append(numbers)
try:
inject_queries.add_received_payment(
self.payment_date, self.currency, self.amount, self.fees,
self.payment_method, self.paid_by, self.updated_by,
self.notes, allocations, closures)
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(e)
def get_by_id(self, id: int) -> Type_currency:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, name FROM type_currency
WHERE id = '{id}' '''
sql = sql.format(id=id)
try:
cursor.execute(sql)
row = cursor.fetchone()
type_currency = Type_currency()
type_currency.id = row['id']
type_currency.name = row['name']
return type_currency
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def executeCommandSelect(cursor, command):
cursor.execute(command)
result = cursor.fetchone()
if result is None:
raise psycopg2.DatabaseError("Command '{0}' return null".format(command))
else:
return result[0]
def get_by_product_id(self, product_id: str) -> List[Product_record]:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''SELECT id, order_id, product_id
FROM product_record WHERE product_id = '{product_id}';'''
sql = sql.format(product_id=product_id)
try:
cursor.execute(sql)
product_record_list = list()
for row in cursor:
product_record = Product_record()
product_record.id = row['id']
product_record.order_id = row['order_id']
product_record.product_id = row['product_id']
product_record_list.append(product_record)
return product_record_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_by_id(self, id: str) -> P_order:
cursor = self._connection.cursor(
cursor_factory = psycopg2.extras.DictCursor)
sql = '''SELECT id, "number", user_id
FROM p_order WHERE id = '{id}';'''
sql = sql.format(id = id)
try:
cursor.execute(sql)
row = cursor.fetchone()
p_order = P_order()
p_order.id = row['id']
p_order.number = row['number']
p_order.user_id = row['user_id']
return p_order
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_client_type(clientid=None, username=None, date=None):
'''
Gets the type of the client
:param clientid: client's id
:param username: username
:param date: the date to look for
:return: list --> dictionary containing details
'''
conditions = []
if date is None:
date = times.current_date()
conditions.append("startdate <= '{0}' and enddate > '{0}'".format(date))
if clientid is not None:
conditions.append('clientid in ({0})'.format(
handyman.convert_int_to_string(clientid)))
if username is not None:
conditions.append('clientid in ({0})'.format(
with_clientid_from_username_query(username)))
query = '''
select clientid, buyer, supplier
from clients
{0}
'''.format(handyman.aggregate_conditions(conditions))
try:
results = conn.fetch(query)
data = []
for item in results:
data.append({
params.client_id: item[0],
params.buyer: item[1],
params.supplier: item[2]
})
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_by_user_id(self, user_id: str) -> List[P_order]:
cursor = self._connection.cursor(
cursor_factory = psycopg2.extras.DictCursor)
sql = '''SELECT id, "number", user_id
FROM p_order WHERE user_id = '{user_id}';'''
sql = sql.format(user_id = user_id)
try:
cursor.execute(sql)
p_order_list = list()
for row in cursor:
p_order = P_order()
p_order.id = row['id']
p_order.number = row['number']
p_order.user_id = row['user_id']
p_order_list.append(p_order)
return p_order_list
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def get_rates(date):
'''
Gets the rates for all products that
are/were valid on a given date
:param date: date for which the rates should be fetched
:return: dictionary -> lists of rates; keyed on product id
'''
query = '''
select product_id, startperiod, endperiod, base_rate
from product_rates
where rate_startdate <= '{0}' and
rate_enddate > '{0}'
'''.format(date)
try:
results = conn.fetch(query)
rates_map = dict()
if len(results) > 0:
for productid, startperiod, endperiod, base_rate in results:
if productid not in rates_map:
rates_map[productid] = []
rates_map[productid].append(
[startperiod, endperiod, base_rate])
return rates_map
else:
raise LookupError('Financing product rates are not available')
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def client_relations(username, client_type, date=None):
'''
Gets the relations that a client has with counterparts
:param username: username of the user
:param client_type: the type of the client (buyer or supplier)
:param date: date to check for
:return: list --> list[id, client_name]
'''
if date is None:
date = datetime.datetime.now().date()
date = date.strftime('%Y%m%d')
try:
if client_type == params.buyer:
attributes = [params.supplier_id, params.supplier_name]
results = info_queries.get_relations(buyer_user=username,
on_date=date)
else:
attributes = [params.buyer_id, params.buyer_name]
results = info_queries.get_relations(supplier_user=username,
on_date=date)
relations = []
for item in results:
relations.append([item[attributes[0]], item[attributes[1]]])
return relations
except psycopg2.DatabaseError as e:
err = 'Could not retrieve relations from database'
raise psycopg2.DatabaseError(err)
def name_from_clientid(clientid=None, date=None):
'''
Query to get a client's id and name
:param clientid: client id to filter by
:param date: date to filter by
:return: dictionary [client id] --> client name
'''
conditions = []
if clientid is not None:
conditions.append('clientid in ({0})'.format(
handyman.convert_int_to_string(clientid)))
if date is not None:
conditions.append(
"startdate <= '{0}' and enddate > '{0}'".format(date))
query = '''
select clientid, client_name from clients {0}
'''.format(handyman.aggregate_conditions(conditions))
try:
print(query)
result = conn.fetch(query)
info = dict()
if len(result) > 0:
for clientid, name in result:
info[clientid] = name
print(info)
return info
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_client_type(clientid=None, username=None):
'''
Gets the type of client given a clientid or username
:param clientid: client's id
:param username: username of the user
:return: list --> buyer and/or supplier
'''
try:
details = info_queries.get_client_type(clientid=clientid,
username=username)
if len(details) > 0:
client_type = details[0]
if client_type[params.buyer] and not client_type[params.supplier]:
return [params.buyer]
elif not client_type[params.buyer] and client_type[
params.supplier]:
return [params.supplier]
elif client_type[params.buyer] and client_type[params.supplier]:
return [params.buyer, params.supplier]
else:
raise LookupError(messages.error_client_type_invalid)
else:
raise LookupError(messages.error_client_type_invalid)
except psycopg2.DatabaseError as e:
err = 'Failed to query client type from database'
raise psycopg2.DatabaseError(err) from e
except Exception as e:
raise Exception(e)
def update(self, user: P_user) -> P_user:
cursor = self._connection.cursor(
cursor_factory=psycopg2.extras.DictCursor)
sql = '''UPDATE p_user
SET firstname = '{firstname}',
surname = '{surname}', middlename = '{middlename}',
fio = '{fio}', sex = '{sex}', age = '{age}'
WHERE id = '{id}';'''
sql = sql.format(id=user.id,
firstname=user.firstname,
surname=user.surname,
middlename=user.middlename,
fio=user.fio,
sex=user.sex,
age=user.age)
try:
cursor.execute(sql)
self._connection.commit()
return user
except psycopg2.DatabaseError as ex:
raise psycopg2.DatabaseError(ex)
finally:
cursor.close()
if self._connection is not None:
self._connection.close()
def add_relation(data):
'''
Adds a new relation
:param data: data for the relation
'''
query = '''
begin;
insert into relations values
('{0}', '99990101', {1}, '{2}',
{3}, '{4}', {5}, {6},
{7}, {8}, '{9}');
insert into relation_limits values
('{0}', '99990101', {1}, {3}, 7, 'relation_max_giv', 'KES', 0),
('{0}', '99990101', {1}, {3}, 8, 'relation_max_single_iv', 'KES', 0),
('{0}', '99990101', {1}, {3}, 9, 'relation_max_invoice_count', 'KES', 0);
end;
'''.format(data[params.start_date],
str(data[params.buyer_id]),
data[params.buyer_name],
str(data[params.supplier_id]),
data[params.supplier_name],
data[params.buyer_fraction],
data[params.supplier_fraction],
True if data[params.buyer_approval] == 0 else False,
True if data[params.supplier_approval] == 0 else False,
data[params.rm_name])
try:
print(query)
conn.execute(query)
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
