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 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_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_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_received_payments(invoice_id=None,
buyer_id=None,
supplier_id=None,
first_date=None,
last_date=None):
'''
Gets repayment details of invoices
:param invoice_id: invoice id (list or int); default=None
:param buyer_id: buyer's client id; default=None
:param supplier_id: supplier's client id; default=None
:param first_date: first date to fetch; default=None
:param last_date: last date to fetch; default=None
:return: dictionary -> list[dictionaries]; keyed on invoice id
'''
conditions = []
if invoice_id is not None:
conditions.append('invoice_id in ({0})'.format(
handyman.convert_int_to_string(invoice_id)))
if buyer_id is not None:
conditions.append('invoice_id in ({0})'.format(
with_invoiceid_from_clientid(params.buyer, buyer_id)))
if supplier_id is not None:
conditions.append('invoice_id in ({0})'.format(
with_invoiceid_from_clientid(params.supplier, supplier_id)))
if first_date is not None:
conditions.append("payment_date >= '{0}'".format(first_date))
if last_date is not None:
conditions.append("payment_date <= '{0}'".format(last_date))
try:
query = '''
select invoice_id, payment_date, received_amount, principal_repaid, discount_fees
from received_payments
{0}
order by payment_date
'''.format(handyman.aggregate_conditions(conditions))
repayments = dict()
result = conn.fetch(query)
if len(result) > 0:
for invoice_id, payment_date, repaid_amount, principal_repaid, discount_fees in result:
if invoice_id not in repayments:
repayments[invoice_id] = []
repayments[invoice_id].append({
params.repayment_date:
payment_date,
params.principal_repaid:
principal_repaid,
params.repaid_amount:
repaid_amount,
params.discount_fees:
discount_fees
})
return repayments
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_relation_limits(buyer_id=None, supplier_id=None, date=None):
conditions = []
if buyer_id is not None:
conditions.append('rl.buyer_clientid in ({0})'.format(
handyman.convert_int_to_string(buyer_id)))
if supplier_id is not None:
conditions.append('rl.supplier_clientid in ({0})'.format(
handyman.convert_int_to_string(supplier_id)))
if date is not None:
conditions.append('''
rl.startdate <= '{0}' and
rl.enddate > '{0}' and
cl1.startdate <= '{0}' and
cl1.enddate > '{0}' and
cl2.startdate <= '{0}' and
cl2.enddate > '{0}'
'''.format(date))
query = '''
select rl.startdate, rl.enddate,
rl.buyer_clientid, cl1.client_name as buyer_name,
rl.supplier_clientid, cl2.client_name as supplier_name,
rl.limit_id, rl.limit_type,
rl.currency, rl.amount
from relation_limits as rl
join clients as cl1 on rl.buyer_clientid = cl1.clientid
join clients as cl2 on rl.supplier_clientid = cl2.clientid
{0}
'''.format(handyman.aggregate_conditions(conditions))
try:
results = conn.fetch(query)
holder = []
for start_, end_, buyer_clientid, buyer_name, supplier_clientid, supplier_name,\
limit_id, limit_type, currency, amount in results:
holder.append({
params.start_date: start_,
params.end_date: end_,
params.buyer_id: buyer_clientid,
params.buyer_name: buyer_name,
params.supplier_id: supplier_clientid,
params.supplier_name: supplier_name,
params.limit_id: limit_id,
params.limit_type: limit_type,
params.currency: currency,
params.amount: amount
})
return holder
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_client_limits(clientid=None, date=None):
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('''client_limits.startdate <= '{0}' and
client_limits.enddate > '{0}' and
clients.startdate <= '{0}' and
clients.enddate > '{0}'
'''.format(date))
query = '''
select client_limits.startdate, client_limits.enddate,
client_limits.clientid, clients.client_name,
client_limits.limit_id, client_limits.limit_type,
client_limits.currency, client_limits.amount
from client_limits
join clients using(clientid)
{0}
'''.format(handyman.aggregate_conditions(conditions))
try:
results = conn.fetch(query)
holder = []
for start_, end_, client_id, name_, limit_id, limit_type, currency, amount in results:
holder.append({
params.start_date: start_,
params.end_date: end_,
params.client_id: client_id,
params.client_name: name_,
params.limit_id: limit_id,
params.limit_type: limit_type,
params.currency: currency,
params.amount: amount
})
return holder
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def client_name_search(date, name=None, ids=None):
conditions = []
if name is not None:
conditions.append("lower(client_name) like '%{0}%'".format(
str(name).lower()))
if ids is not None:
conditions.append('clientid in ({0})'.format(
handyman.convert_int_to_string(ids)))
query = '''
select clientid, client_name, case
when buyer = true and supplier = false then 'Buyer'
when buyer = false and supplier = true then 'Supplier'
when buyer = true and supplier = true then 'Both'
end as client_type, industry, case
when startdate <= '{0}' and enddate > '{0}' then 'Active'
else 'Dead'
end as status
from clients
{1}
order by client_name
'''.format(date, handyman.aggregate_conditions(conditions))
try:
result = conn.fetch(query)
data = []
if len(result) > 0:
for client_id, name, client_type, industry, status in result:
data.append({
params.client_id: client_id,
params.client_name: name,
params.client_type: client_type,
params.industry: industry,
params.client_status: status
})
return data
except Exception as e:
raise Exception(e)
def get_relations(buyer_id=None,
buyer_user=None,
supplier_id=None,
supplier_user=None,
on_date=None):
'''
Gets relations between clients
:param buyer_id: clientid of the buyer
:param buyer_user: username of an authorized signatory of the buyer
:param supplier_id: clientid of the supplier
:param supplier_user: username of an authorized signatory of the supplier
:param on_date: date to check for relations
:return: list --> dictionaries with relation details
'''
conditions = []
if buyer_id is not None:
conditions.append('buyer_clientid in ({0})'.format(
handyman.convert_int_to_string(buyer_id)))
if buyer_user is not None:
conditions.append('buyer_clientid in ({0})'.format(
with_clientid_from_username_query(buyer_user)))
if supplier_id is not None:
conditions.append('supplier_clientid in ({0})'.format(
handyman.convert_int_to_string(supplier_id)))
if supplier_user is not None:
conditions.append('supplier_clientid in ({0})'.format(
with_clientid_from_username_query(supplier_user)))
if on_date is not None:
conditions.append('''
rel.startdate <= '{0}' and
rel.enddate > '{0}' and
cl.startdate <= '{0}' and
cl.enddate > '{0}' and
cl2.startdate <= '{0}' and
cl2.enddate > '{0}'
'''.format(on_date))
query = '''
select rel.buyer_clientid, cl.client_name as buyer,
rel.supplier_clientid, cl2.client_name as supplier,
buyer_fraction, supplier_fraction,
buyer_approval_allowed, supplier_approval_allowed, rm_name,
rel.startdate, rel.enddate
from relations as rel
join clients as cl
on cl.clientid = buyer_clientid
join clients as cl2
on cl2.clientid = supplier_clientid
{0}
order by enddate desc, buyer_name, supplier_name
'''.format(handyman.aggregate_conditions(conditions))
try:
result = conn.fetch(query)
data = []
for buyer_id, buyer_name, supplier_id, supplier_name, buyer_fraction, supplier_fraction, \
buyer_approval, supplier_approval, rm_name, start_, end_ in result:
data.append({
params.buyer_id: buyer_id,
params.buyer_name: buyer_name,
params.supplier_id: supplier_id,
params.supplier_name: supplier_name,
params.buyer_fraction: buyer_fraction,
params.supplier_fraction: supplier_fraction,
params.buyer_approval: buyer_approval,
params.supplier_approval: supplier_approval,
params.rm_name: rm_name,
params.start_date: start_,
params.end_date: end_
})
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
except Exception as e:
raise Exception(e)
def giv_data(buyer_id=None,
supplier_id=None,
first_date=None,
last_date=None,
invoice_id=None,
breakdown=True):
'''
Gets the GIV value or the breakdown of the GIV value
:param buyer_id: client id of the buyer
:param supplier_id: client id of the supplier
:param first_date: the minimum date to fetch for (inclusive)
:param last_date: the maximum date to fetch for (incusive)
:param invoice_id: invoice id(s)
:param breakdown: True if breakdown of day to day GIV is required; False if the sum is required
:return:
'''
invoice_conditions = ['cancelled=False']
if invoice_id is not None:
invoice_conditions.append('invoice_id in ({0})'.format(
handyman.convert_int_to_string(invoice_id)))
if buyer_id is not None:
invoice_conditions.append('invoice_id in ({0})'.format(
with_invoiceid_from_clientid(params.buyer, buyer_id)))
if supplier_id is not None:
invoice_conditions.append('invoice_id in ({0})'.format(
with_invoiceid_from_clientid(params.supplier, supplier_id)))
str_payment_conditions = ''
if first_date is not None:
str_payment_conditions += " and " + "payment_date >= '{0}'".format(
first_date)
if last_date is not None:
str_payment_conditions += " and " + "payment_date <= '{0}'".format(
last_date)
main_query = 'select payment_date, sum(amount) from t2 group by payment_date order by payment_date'
if not breakdown:
main_query = 'select sum(amount) from t2'
try:
query = '''
with t1 as(
select invoice_id from invoices {0}
),
t2 as(
select payment_date, sum(paid_amount) as amount from forwarded_payments
where invoice_id in (select invoice_id from t1) {1}
group by payment_date
union
select payment_date, -sum(principal_repaid) as amount from received_payments
where invoice_id in (select invoice_id from t1) {1}
group by payment_date
)
{2}
'''.format(handyman.aggregate_conditions(invoice_conditions),
str_payment_conditions, main_query)
result = conn.fetch(query)
if breakdown:
holder = []
for payment_date, amount in result:
holder.append([payment_date, amount])
return holder
else:
if len(result) > 0:
return result[0][0]
return 0
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def get_invoice(invoice_id=None,
buyer_id=None,
supplier_id=None,
status=None,
limit_rows=0,
offset=None):
'''
Gets the details of invoice(s)
:param invoice_id: the invoice id; default=None
:param buyer_id: client id of the buyer; default=None
:param supplier_id: supplier id of the supplier; default=None
:param status: status of the invoice (OPEN, CLOSED, etc); default None
:param limit_rows: number of rows to return
:param offset: number of rows of offsets to apply
:return: dictionary --> dictionaries; [invoide id] --> details
'''
conditions = []
if invoice_id is not None:
conditions.append('invoice_id in ({0})'.format(
handyman.convert_int_to_string(invoice_id)))
if buyer_id is not None:
conditions.append('buyer_id in ({0})'.format(
handyman.convert_int_to_string(buyer_id)))
if supplier_id is not None:
conditions.append('supplier_id in ({0})'.format(
handyman.convert_int_to_string(supplier_id)))
if status is not None:
conditions.append("status = '{0}'".format(status))
query = '''
select invoice_id, reference_id,
buyer_id, buyer_name,
supplier_id, supplier_name,
financing_product_id, financing_product,
submission_date, invoice_date, approval_date,
item_description, currency, invoice_total, status
from invoices
{0}
order by submission_date desc
{1} {2}
'''.format(
handyman.aggregate_conditions(conditions),
' limit {0} '.format(str(limit_rows)) if limit_rows != 0 else '',
'' if offset is None else ' offset {0} '.format(offset))
try:
result = conn.fetch(query)
cols = [
params.invoice_id, params.invoice_ref_id, params.buyer_id,
params.buyer_name, params.supplier_id, params.supplier_name,
params.financing_product_id, params.financing_product,
params.submitted_on, params.invoice_date, params.approved_on,
params.description, params.currency, params.invoice_total,
params.invoice_status
]
all_invoices = dict()
if len(result) > 0:
for item in result:
invoice = dict()
if len(item) == len(cols):
for i in range(0, len(cols)):
invoice[cols[i]] = item[i]
all_invoices[invoice[params.invoice_id]] = invoice
else:
raise Exception('Incorrect number of columns passed')
return all_invoices
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e
def fetch_payments(invoice_id=None,
buyer_id=None,
supplier_id=None,
payment_type=None,
cancelled=None,
side=None,
limit=None,
offset=None):
'''
Gets details about payments.
:param invoice_id: invoice id(s) to filter with
:param buyer_id: buyer id(s) to filter with
:param supplier_id: supplier id(s) to filter with
:param payment_type: payment type to filter with
:param cancelled: True or False; filter by whether a payment is cancelled or not
:param side: 1 or 2; 1 if the payment is coming in to ZuriCap; 2 if it is going out
:param limit: the number of rows to limit by
:param offset: the number of rows to skip
:return: list of dictionaries with payment details
'''
invoice_conditions = []
if invoice_id is not None:
invoice_conditions.append('invoice_id in ({0})'.format(
handyman.convert_int_to_string(invoice_id)))
if buyer_id is not None:
invoice_conditions.append('buyer_id in ({0})'.format(
handyman.convert_int_to_string(buyer_id)))
if supplier_id is not None:
invoice_conditions.append('supplier_id in ({0})'.format(
handyman.convert_int_to_string(supplier_id)))
payment_conditions = []
if payment_type is not None:
payment_conditions.append("payment_type = '{0}'".format(payment_type))
if cancelled is not None:
if type(cancelled) is bool:
payment_conditions.append('cancelled = {0}'.format(cancelled))
if side is not None:
if side in [1, 2]:
payment_conditions.append('side = {0}'.format(side))
query = '''
with t1 as(
select invoice_id from invoices {0}
)
,t2 as(
select payment_id from forwarded_payments where invoice_id in (select invoice_id from t1)
union
select payment_id from received_payments where invoice_id in (select invoice_id from t1)
)
, t3 as(
select * from payments where payment_id in (select payment_id from t2)
order by payment_date desc
{1} {2}
)
, t4 as(
select payment_id, payment_date, side, currency, amount, transaction_cost,
cancelled, paid_by, paid_to, client_name as paid_by_name
from t3 left join clients on paid_by = clientid
)
select t4.*, client_name as paid_to_name
from t4 left join clients on paid_to = clientid
order by payment_date desc, payment_id desc
'''.format(
handyman.aggregate_conditions(invoice_conditions),
' limit {0} '.format(limit) if limit is not None and type(limit) is int
else '', ' offset {0} '.format(offset)
if offset is not None and type(offset) is int else '')
try:
results = conn.fetch(query)
data = []
for id_, date_, side_, currency_, amount_, cost_, cancelled_status,\
paid_by, paid_to, paid_by_name, paid_to_name in results:
data.append({
params.payment_id: id_,
params.payment_date: date_,
params.side: side_,
params.currency: currency_,
params.amount: amount_,
params.transaction_cost: cost_,
params.cancelled_status: cancelled_status,
params.paid_by: paid_by,
params.paid_to: paid_to,
params.buyer_name: paid_by_name,
params.supplier_name: paid_to_name
})
return data
except psycopg2.DatabaseError as e:
raise psycopg2.DatabaseError(messages.error_db_query) from e