def liveSearchInvoices(practice_uuid, patient_name):
sql = """SELECT
any_value(patients.practice_uuid) AS practice_uuid,
any_value(patients.patient_name) AS patient_name,
any_value(patients.patient_id) AS patient_id,
any_value(invoices.invoice_id) AS invoice_id,
any_value(invoices.credit_cent) AS credit_cent,
any_value(invoices.submitted_on) AS submitted_on,
any_value(invoices.date_created) AS date_created,
any_value(invoices.date_invoice) AS date_invoice,
any_value(invoices.status) AS status,
group_concat(invoice_items.item) AS item_numbers,
group_concat(invoice_items.units) AS item_units,
group_concat(invoice_items.description) AS item_descriptions,
group_concat(invoice_items.value_cent) AS item_values,
group_concat(invoice_items.post_value_cent) AS item_post_values,
group_concat(invoice_items.date) AS item_dates,
group_concat(invoice_items.status) AS item_status
FROM patients
LEFT JOIN invoices
ON invoices.patient_id = patients.patient_id
LEFT JOIN invoice_items
ON invoice_items.invoice_id = invoices.invoice_id
WHERE patients.practice_uuid = '{}' AND patients.patient_name LIKE '{}%'
GROUP BY invoices.invoice_id""".format(practice_uuid, patient_name)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
patient_invoices = cursor.fetchall()
cursor.close()
conn.close()
return patient_invoices
def updateInvoice(practice_uuid, phone, fax, hospital, diagnosis):
if phone and fax and hospital and diagnosis:
layout_code = 9
elif phone and fax and hospital:
layout_code = 6
elif phone and fax and diagnosis:
layout_code = 12
elif phone and hospital and diagnosis:
layout_code = 8
elif phone and fax:
layout_code = 3
elif phone and hospital:
layout_code = 5
elif phone and diagnosis:
layout_code = 11
elif hospital and diagnosis:
layout_code = 7
elif phone:
layout_code = 2
elif hospital:
layout_code = 4
elif diagnosis:
layout_code = 10
else:
layout_code = 1
sql = """UPDATE practices SET invoice_layout = '{}' WHERE
practice_uuid = '{}'""".format(layout_code, practice_uuid)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
conn.close()
status = True
return status
def addPractice(email, practice_form):
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(
"""INSERT INTO practices (uuid_bin, practice_admin,
practice_email, phone,
cell, fax, pob, city, country,
bank_holder, bank_account, bank,
bank_branch, practice_number, practice_name,
hpcna_number, qualification, specialisation,
namaf_profession)
VALUES(unhex(replace(uuid(),'-','')),%s,%s,%s,%s,
%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
""",
(email, practice_form.get('practice_email'),
practice_form.get('phone'), practice_form.get('cell'),
practice_form.get('fax'), practice_form.get('pob'),
practice_form.get('city'), practice_form.get('country'),
practice_form.get('bank_holder'), practice_form.get('bank_account'),
practice_form.get('bank'), practice_form.get('bank_branch'),
practice_form.get('practice_number'),
practice_form.get('practice_name'), practice_form.get('hpcna_number'),
practice_form.get('qualification'),
practice_form.get('specialisation'),
practice_form.get('namaf_profession')))
status = True
cursor.close()
conn.close()
return status
def mergeUserPractice(practice_uuid, practice_name, user_uuid, user_email,
user_name, role):
try:
conn = pool.connection()
cursor = conn.cursor()
sql = """UPDATE users SET current_practice_uuid = '{}', current_practice_role = '{}' WHERE uuid_text = '{}'""".format(
practice_uuid, role, user_uuid)
cursor.execute(sql)
cursor.execute(
"""SELECT * FROM practice_connections WHERE practice_uuid = %s AND user_uuid = %s""",
(practice_uuid, user_uuid))
row = cursor.fetchone()
if not row:
cursor.execute(
"""INSERT INTO practice_connections
(practice_uuid, practice_name,
user_uuid, user_email, user_name, practice_role)
VALUES(%s,%s,%s,%s,%s,%s)""",
(practice_uuid, practice_name, user_uuid, user_email,
user_name, role))
cursor.close()
conn.close()
except Exception as e:
app.logger.info(str(e))
return False
return True
def checkUser(email):
conn = pool.connection()
cursor = conn.cursor()
cursor.execute("""SELECT * FROM users WHERE email = %s""", (email))
data = cursor.fetchone()
cursor.close()
conn.close()
return data
def getInvoiceFileId(invoice_file_url):
sql = """SELECT fileid from oc_filecache WHERE path like '%{}%'""".format(invoice_file_url)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
invoice_file_id = cursor.fetchone()
cursor.close()
conn.close()
return invoice_file_id['fileid']
def removeEntry(_sIdentifier, _sColumn, _sTable):
try:
conn = pool.connection()
cursor = conn.cursor()
cursor.execute("""DELETE FROM %s WHERE %s = %s""",
(_sTable, _sColumn, _sTable))
except:
return False
return True
def insertInvoice(practice_uuid, user_name, invoice_form):
conn = pool.connection()
cursor = conn.cursor()
status = {}
cursor.execute("""INSERT INTO invoices (practice_uuid, last_edited_by,
patient_id, date_invoice, medical_aid, invoice_id, invoice_file_url, tariff,
invoice_layout, po_number, hospital_name, admission_date,
discharge_date, `procedure`, procedure_date, diagnosis,
diagnosis_date, implants, intra_op, post_op)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
""",(practice_uuid,
user_name,
invoice_form['patient_id'],
invoice_form['date_invoice'],
invoice_form['medical_aid'],
invoice_form['invoice_id'],
invoice_form['invoice_file_url'],
invoice_form['tariff'],
invoice_form['invoice_layout'],
invoice_form.get('po_number') or None,
invoice_form.get('hospital_name') or None,
invoice_form.get('admission_date') or None,
invoice_form.get('discharge_date') or None,
invoice_form.get('procedure') or None,
invoice_form.get('procedure_date') or None,
invoice_form.get('diagnosis') or None,
invoice_form.get('diagnosis_date') or None,
invoice_form.get('implants') or None,
invoice_form.get('intra_op') or None,
invoice_form.get('post_op') or None))
list = []
for i in range(len(invoice_form.getlist('treatments'))):
list_item = []
list_item.extend((practice_uuid,
invoice_form['invoice_id'],
invoice_form.getlist('treatments')[i],
float(invoice_form.getlist('units')[i]) * 100,
invoice_form.getlist('description')[i],
float(invoice_form.getlist('value')[i]) * 100,
float(invoice_form.getlist('post_value')[i]) * 100,
invoice_form.getlist('date')[i],
invoice_form.getlist('modifier')[i]))
list_item = tuple(list_item)
list.append(list_item)
sql_individual_item = """INSERT INTO invoice_items (practice_uuid,
invoice_id, item, units, description, value_cent, post_value_cent,
date, modifier) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
cursor.executemany(sql_individual_item, list)
status['db_status'] = 'Success'
status['db_description'] = 'New invoice created ' + invoice_form['invoice_id']
status['invoice_id'] = invoice_form['invoice_id']
cursor.close()
conn.close()
return status
def updateInvoice(practice_uuid, user_name, invoice_form):
conn = pool.connection()
cursor = conn.cursor()
status = {}
sql = """UPDATE invoices SET po_number = %s, hospital_name = %s,
admission_date = %s, discharge_date = %s, `procedure` = %s,
procedure_date = %s, diagnosis = %s, diagnosis_date = %s, implants = %s,
intra_op = %s, post_op = %s, last_edited = NOW(), last_edited_by = %s WHERE practice_uuid =
%s AND invoice_id = %s
"""
invoice_form_tuple = (invoice_form.get('po_number') or None,
invoice_form.get('hospital_name') or None,
invoice_form.get('admission_date') or None,
invoice_form.get('discharge_date') or None,
invoice_form.get('procedure') or None,
invoice_form.get('procedure_date') or None,
invoice_form.get('diagnosis') or None,
invoice_form.get('diagnosis_date') or None,
invoice_form.get('implants') or None,
invoice_form.get('intra_op') or None,
invoice_form.get('post_op') or None,
user_name,
practice_uuid,
invoice_form['invoice_id'])
cursor.execute(sql, invoice_form_tuple)
sql_rm_invoice_items = """DELETE FROM invoice_items WHERE practice_uuid = '{}'
AND invoice_id = '{}'""".format(practice_uuid,
invoice_form['invoice_id'])
cursor.execute(sql_rm_invoice_items)
list = []
for i in range(len(invoice_form.getlist('treatments'))):
list_item = []
list_item.extend((practice_uuid,
invoice_form['invoice_id'],
invoice_form.getlist('treatments')[i],
float(invoice_form.getlist('units')[i]) * 100,
invoice_form.getlist('description')[i],
float(invoice_form.getlist('value')[i]) * 100,
float(invoice_form.getlist('post_value')[i]) * 100,
invoice_form.getlist('date')[i],
invoice_form.getlist('modifier')[i]))
list_item = tuple(list_item)
list.append(list_item)
sql_individual_item = """INSERT INTO invoice_items (practice_uuid,
invoice_id, item, units, description, value_cent, post_value_cent,
date, modifier) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
cursor.executemany(sql_individual_item, list)
status['db_status'] = 'Success'
status['db_description'] = 'Updated invoice'
status['invoice_id'] = invoice_form['invoice_id']
cursor.close()
conn.close()
return status
def getValueTreatments(item, tariff):
sql = """SELECT * FROM namaf_tariffs WHERE item = {} AND tariff = '{}'""".format(
item, tariff)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
filtered_result = cursor.fetchone()
cursor.close()
conn.close()
return filtered_result
def queryR(practice_uuid, c_option):
sql = """SELECT DISTINCT {} FROM invoices WHERE practice_uuid = '{}'
""".format(c_option, practice_uuid)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
r_option = cursor.fetchall()
cursor.close()
conn.close()
return r_option
def removePatient(practice_uuid, patient_id):
conn = pool.connection()
cursor = conn.cursor()
sql = """ DELETE FROM patients WHERE practice_uuid = '{}' AND
patient_id = '{}'""".format(practice_uuid, patient_id)
cursor.execute(sql)
cursor.close()
conn.close()
status = True
return status
def getItems(practice_uuid, invoice_id):
sql = """SELECT * FROM invoice_items WHERE practice_uuid = '{}' AND invoice_id
= '{}'""".format(practice_uuid, invoice_id)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
items = cursor.fetchall()
cursor.close()
conn.close()
return items
def updateSubmitted(practice_uuid, invoice_id):
sql= """UPDATE invoices SET submitted_on = NOW(), status = 'due' WHERE practice_uuid = '{}' and
invoice_id = '{}'""".format(practice_uuid, invoice_id)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
conn.close()
status = True
return status
def getTariffs(namaf_profession):
sql = """ SELECT tariff FROM namaf_tariffs WHERE tariff LIKE '%{}%' GROUP BY
tariff""".format(namaf_profession)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
tariffs = cursor.fetchall()
cursor.close()
conn.close()
return tariffs
def getInvoiceURL(practice_uuid, name_patient, date):
sql = """SELECT invoice_file_url FROM invoices WHERE practice_uuid = '{}' AND name_patient = '{}'
AND date_created = '{}'""".format(practice_uuid, name_patient, date)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
invoice_file_url = cursor.fetchone()
cursor.close()
conn.close()
return invoice_file_url
def getAssistants(practice_uuid):
conn = pool.connection()
cursor = conn.cursor()
sql = """SELECT * FROM practice_connections WHERE practice_uuid =
'{}' AND practice_role = 'assistant'""".format(practice_uuid)
cursor.execute(sql)
assistants = cursor.fetchall()
cursor.close()
conn.close()
return assistants
def checkConnections(user_uuid):
conn = pool.connection()
cursor = conn.cursor()
sql = """SELECT * FROM practice_connections WHERE user_uuid =
'{}'""".format(user_uuid)
cursor.execute(sql)
practices = cursor.fetchall()
cursor.close()
conn.close()
return practices
def addUser(first_name, second_name, email):
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(
"""INSERT INTO users (uuid_bin, first_name, second_name, email)
VALUES(unhex(replace(uuid(),'-','')),%s,%s,%s)
""", (first_name, second_name, email))
status = True
cursor.close()
conn.close()
return status
def lastFive(uuid_text, practice_uuid, work_type):
conn = pool.connection()
cursor = conn.cursor()
sql = """SELECT work_quality FROM user_workbench WHERE uuid_text = '{0}'
AND practice_uuid = '{1}' AND work_type = '{2}' ORDER BY created_on DESC LIMIT
5""".format(uuid_text, practice_uuid, work_type)
cursor.execute(sql)
last_five = cursor.fetchall()
cursor.close()
conn.close()
return last_five
def updateCredit(practice_uuid, invoice_id, credit_cent):
sql = """UPDATE invoices
SET credit_cent = credit_cent + '{}'
WHERE practice_uuid = '{}' AND invoice_id =
'{}'""".format(credit_cent, practice_uuid, invoice_id)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
conn.close()
status = True
return status
def getTreatmentByGroup(items, tariff):
treatment_list = []
connection = pool.connection()
cursor = connection.cursor()
for i in items.split(","):
sql = """SELECT description FROM namaf_tariffs WHERE item = {} AND tariff = '{}'""".format(
i, tariff)
cursor.execute(sql)
q = cursor.fetchone()
treatment_list.append(q)
cursor.close()
connection.close()
return treatment_list
def get_index(practice_uuid, medical_aid):
today = date.today()
month = today.month
year = today.year
sql = """SELECT COUNT(*) FROM invoices WHERE practice_uuid = '{}' AND medical_aid = '{}' AND YEAR
(date_created) = '{}' AND MONTH(date_created) = '{}'""".format(practice_uuid, medical_aid, year, month)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
index = cursor.fetchone()
cursor.close()
conn.close()
return index['COUNT(*)'] + 1
def getMultipleValues(items, tariff):
value_list = []
connection = pool.connection()
cursor = connection.cursor()
for i in items.split(","):
sql = """SELECT description, value_cent FROM namaf_tariffs WHERE item = {} AND tariff = '{}'""".format(
i, tariff)
cursor.execute(sql)
q = cursor.fetchone()
value_list.append(q)
cursor.close()
connection.close()
return value_list
def getTreatmentByItem(treatments, tariff):
treatment_list = []
connection = pool.connection()
cursor = connection.cursor()
for i in treatments:
sql = """SELECT description, units, value_cent FROM namaf_tariffs WHERE item = {} AND tariff = '{}'""".format(
i, tariff)
cursor.execute(sql)
q = cursor.fetchone()
treatment_list.append(q)
cursor.close()
connection.close()
return treatment_list
def updateScalar(_sVal, _sCol, _sIdentifier, _sLkpCol, _sLkpTbl):
try:
conn = pool.connection()
cursor = conn.cursor()
sql = 'UPDATE ' + _sLkpTbl
sql += ' SET ' + _sCol + '=%s WHERE ' + _sLkpCol + '= %s'
cursor.execute(sql, (_sVal, _sIdentifier))
cursor.close()
conn.close()
except Exception as e:
print(str(e))
return False
return True
def checkDuplicate(practice_uuid, patient):
sql = """SELECT * FROM patients WHERE practice_uuid = '{}' AND
(medical_number = '{}' OR case_number = '{}' OR medical_number = '{}' OR case_number = '{}')
""".format(practice_uuid, patient.get('medical_number'),
patient.get('case_number'), patient.get('case_number'),
patient.get('medical_number'))
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
row = cursor.fetchone()
cursor.close()
conn.close()
return row
def getSingleInvoice(practice_uuid, invoice_id):
sql = """SELECT invoices.*, patients.*
FROM invoices
LEFT JOIN patients ON patients.patient_id = invoices.patient_id
AND patients.practice_uuid = '{}' WHERE invoices.practice_uuid = '{}' AND
invoices.invoice_id = '{}'
""".format(practice_uuid, practice_uuid, invoice_id)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
invoice = cursor.fetchone()
cursor.close()
conn.close()
return invoice
def getTreatments(tariff, featured=None):
if (featured is None):
sql = """SELECT * FROM
namaf_tariffs WHERE tariff = '{}' ORDER BY id""".format(tariff)
connection = pool.connection()
cursor = connection.cursor()
cursor.execute(sql)
filtered_result = cursor.fetchall()
cursor.close()
connection.close()
return filtered_result
elif (featured is not None):
featured = tuple(featured)
sql = """SELECT LPAD(item, 3, 0) AS item, description FROM
namaf_tariffs WHERE item IN {} AND tariff = '{}' ORDER BY id""".format(
featured, tariff)
connection = pool.connection()
cursor = connection.cursor()
cursor.execute(sql)
featured_result = cursor.fetchall()
cursor.close()
connection.close()
return featured_result
def getScalar(_sIdentifier, _sLkpVal, _sLkpCol, _sLkpTbl, _sOrderBy=None):
try:
conn = pool.connection()
cursor = conn.cursor()
sql = 'SELECT ' + _sIdentifier + ' FROM ' + _sLkpTbl
sql += ' WHERE ' + _sLkpCol + ' = %s '
if _sOrderBy:
sql += ' ORDER BY ' + _sOrderBy + ' DESC '
cursor.execute(sql, (_sLkpVal))
_sScalar = cursor.fetchone()
cursor.close()
conn.close()
except Exception as e:
return str(e)
return _sScalar
