def prescribe(patient_username, doctor_username, items):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor2 = db_mysql.newCursor()
cursor.execute(
'INSERT INTO prescription (docter_id,patient_id,date) VALUES (%s,%s,CURRENT_TIMESTAMP);',
(doctor_username, patient_username,))
prescription = cursor.lastrowid
print(prescription)
db.commit()
for item in items:
if item["typelord"] == "d":
cursor.execute(
'INSERT INTO labratory (`doctor`, `patient`, `type`, `result`, `paid`, `description`) VALUES (%s,%s,%s,%s,%s,%s);',
(prescription, item['id'], item['type'], "", False, item['desc']))
else:
cursor.execute(
'INSERT INTO prescription_item (prescription_id,medicine_id, dose) VALUES (%s,%s,%s);',
(prescription, item['id'], item['dose']))
cursor2.execute("SELECT price FROM medicine WHERE medicine_id = %s", item['id'])
price = cursor2.fetchone()['price']
cursor.execute(
"INSERT INTO `invoice_item`(`name`, `unit`, `origin`, `patient`, `type`, `unit_price`, `date`, `paid`) VALUES\
(%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP, FALSE)", item['id'], item['dose'], doctor_username, patient_username, "prescription", price,)
db.commit()
return {'OK': True}
def prescribe(patient_username):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor2 = db_mysql.newCursor()
cursor2.execute(
"SELECT * FROM labratory WHERE paid = TRUE AND patient = %s and result IS NOT NULL", patient_username)
db.commit()
return {'OK': True, results= cursor.fetchall()}
def accept_reserve(reserve_id, doctor_username):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'SELECT users.* FROM (patient LEFT OUTER JOIN users ON '
'(patient.username = users.username) ),'
'time_reserve LEFT OUTER JOIN time_request ON '
'(time_reserve.id = time_request.time_reserve_id) WHERE time_reserve.id = %s '
'AND time_reserve.doctor_username = %s'
' AND time_request.time_reserve_id IS NOT NULL;',
(reserve_id, doctor_username))
user = cursor.fetchone()
print(user)
db.commit()
cursor.execute(
'UPDATE time_request set active=1 WHERE time_reserve_id= %s', (reserve_id,))
db.commit()
cursor.execute(
"INSERT INTO `invoice_item`(`name`, `unit`, `origin`, `patient`, `type`, `unit_price`, `date`, `paid`) VALUES\
(%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP, FALSE)", "reservation", 1, doctor_username, user['username'], "reservation", 45000,)
send_email(user['email'], user['name'] +
", your reservation is accepted by doctor " + doctor_username)
return {'OK': True}
def reserve_doctor_time(reserve_id, patient_username):
db = db_mysql.db_users['patient']
cursor = db_mysql.newCursor("patient")
cursor.execute(
'SELECT time_reserve.* FROM time_reserve LEFT OUTER JOIN time_request ON (time_reserve.id = time_request.time_reserve_id) '
' WHERE time_reserve.id = %s AND time_request.time_reserve_id IS NULL;',
(reserve_id, ))
print(cursor.rowcount)
if cursor.rowcount == 0:
return {
'OK': False,
'Error': 'no reserve with id found id %s' % reserve_id
}
else:
time_reserve = cursor.fetchone()
print(time_reserve)
cursor.execute(
"INSERT INTO time_request(time_reserve_id, payed, patient_username) VALUES (%s, %s, %s);",
(time_reserve['id'], 0, patient_username))
db.commit()
dict = {'OK': True}
return dict
def get_medicine_bydate():
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute(
'SELECT `id`, `name`, `price`, unix_timestamp(exp_date) FROM medicine order by exp_date;', ())
db.commit()
return {'OK': True, 'medicines': cursor.fetchall()}
def add_medicine(name, price, exp_date):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute(
'INSERT INTO medicine(name,price,exp_date) values (%s,%s,%s);', (name, price, exp_date))
db.commit()
return {'OK': True, }
def get_medicine(id):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute(
'SELECT `id`, `name`, `price`, unix_timestamp(exp_date) FROM medicine WHERE id = %s ;', (id,))
db.commit()
return {'OK': True, 'medicine': cursor.fetchall()}
def update_medicine(id, price, exp_date):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute(
'UPDATE medicine SET price = %s , exp_date =%s WHERE id =%s;', (price, exp_date, id))
db.commit()
return {'OK': True, 'medicine': cursor.fetchall()}
def see_bed(patient_username):
db = db_mysql.db_users['doctor']
cursor = db_mysql.newCursor('doctor')
cursor.execute('SELECT FROM bed WHERE patient_username = %s;',
(patient_username, ))
user = cursor.fetchall()
print(user)
db.commit()
return {'OK': True, 'beds': user}
def get_medicine_history(patient_username):
db = db_mysql.db_users['doctor']
cursor = db_mysql.newCursor('doctor')
cursor.execute(
'SELECT patient_id, medicine.Name ,unix_timestamp(prescription.date) FROM prescription_item INNER JOIN prescription ON prescription.id = prescription_item.prescription_id INNER JOIN medicine ON medicine.id = prescription_item.medicine_id WHERE prescription.patient_id = %s',
(patient_username,))
user = cursor.fetchall()
print(user)
db.commit()
return {'OK': True, 'prescription': user}
def get_prescription_details(items):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
sql = "select `id`, `name`, `price`, unix_timestamp(exp_date) from medicine where id in (%s)" % (
', '.join(str(id) for id in items))
cursor.execute(sql)
db.commit()
return {'OK': True, 'prescription': cursor.fetchall()}
def cancel_reserve(reserve_id):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'DELETE FROM time_request WHERE time_reserve_id = %s;',
(reserve_id,))
print(cursor.rowcount)
db.commit()
return {'OK': True}
def hospitalize(patient_username, doctor_username):
db = db_mysql.db_users['doctor']
cursor = db_mysql.newCursor('doctor')
cursor.execute(
'INSERT INTO bed(patient_username,doctor_username)'
'VALUES (%s,%s)',
(patient_username, doctor_username))
user = cursor.fetchall()
print(user)
db.commit()
return {'OK': True}
def check_login(session):
t = datetime.datetime.now()
cursor = db_mysql.newCursor()
cursor.execute(
"SELECT * FROM api_keys WHERE api_key = %s AND exp_date > %s ;",
(session, t))
if cursor.rowcount <= 0:
return False
row = cursor.fetchone()
username = row['username']
return username
def check_login(session):
db = db_mysql.db_users['signing']
cursor = db_mysql.newCursor("signing")
cursor.execute(
"SELECT * FROM api_keys, users WHERE api_key.username = users.username AND api_key = %s AND exp_date > %s ;",
(session, t))
if cursor.rowcount <= 0:
return False
row = cursor.fetchone()
username = row['username']
return username, db_mysql.db_users[row['role']]
def add_doctor_time(doctor_username, week_day, hour,price):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'INSERT INTO time_reserve (doctor_username,week_day,hour,price)'
'VALUES (%s,%s,%s, %s);',
(doctor_username, week_day, hour,price))
print(cursor.rowcount)
db.commit()
return {'OK': True}
def login(j):
db = db_mysql.db_users['signing']
cursor = db_mysql.newCursor("signing")
username = j['username']
password = j['password']
if username is None or password is None:
return {'OK': False, 'Error': "not a valid json"}
cursor.execute("SELECT * FROM users WHERE username = %s ;", (username, ))
db.commit()
if cursor.rowcount <= 0:
cursor.execute("SELECT * FROM users WHERE email = %s ;", (username, ))
db.commit()
if cursor.rowcount <= 0:
return {'OK': False, 'Error': "Wrong username or password"}
row = cursor.fetchone()
salt = row['salt']
dbPassword = row['password']
del row['password']
del row['salt']
hash = hashlib.sha512()
hash.update((salt + password).encode('utf-8'))
enteredPassword = hash.hexdigest()
if dbPassword == enteredPassword:
# T = int(time.time())
Session = secrets.token_hex(16)
cursor = db_mysql.newCursor("signing")
SessionExp = datetime.datetime.now() + datetime.timedelta(days=50)
cursor.execute(
"INSERT INTO api_keys (username, api_key, exp_date) VALUES (%s, %s, %s);",
(username, Session, SessionExp))
db.commit()
return {'OK': True, 'api_key': Session, 'User': row}
return {'OK': False, 'Error': "Wrong username or password"}
def edit_profile(j):
db = db_mysql.db_users['patient']
cursor = db_mysql.newCursor("patient")
api_key = j['api_key']
name = j['name']
phone_number = j['phone_number']
birth_year = j['birth_year']
postal_code = j['postal_code']
address = j['address']
weight = j['weight']
gender = j['gender']
height = j['height']
password = j['password']
if api_key is None:
return {'OK': False, 'Error': "You are not logged in"}
username = check_login(api_key)
if username is False:
return {'OK': False}
if password is not None and password != "":
salt = secrets.token_hex(16)
temp = (salt + password)
hash = hashlib.sha512()
hash.update(temp.encode('utf-8'))
password = hash.hexdigest()
cursor.execute(
"UPDATE `users` SET password = %s, salt = %s WHERE username = %s",
(
password,
salt,
username,
))
db.commit()
cursor.execute(
"UPDATE `users` SET name = %s, phone_number =%s, birth_year = %s, postal_code = %s, address = %s, weight = %s,\
gender = %s, height = %s WHERE username = %s", (
name,
phone_number,
birth_year,
postal_code,
address,
weight,
gender,
height,
username,
))
db.commit()
dict = {'OK': True}
return dict
def api_key_to_user(api_key):
db = db_mysql.db_users['api_key']
cursor = db_mysql.newCursor('api_key')
cursor.execute(
'SELECT users.* FROM users INNER JOIN api_keys ON '
'users.username = api_keys.username WHERE api_keys.api_key =%s',
(api_key, ))
db.commit()
if cursor.rowcount == 0:
return {'OK': False}
return {'OK': True, "user": cursor.fetchone()}
def delete_doctor_time(id):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'DELETE time_request FROM time_request INNER JOIN time_reserve ON time_reserve.id='
'time_request.time_reserve_id WHERE time_reserve_id = %s;',
(id,))
cursor.execute(
'DELETE time_reserve FROM time_reserve WHERE id = %s;',
(id,))
print(cursor.rowcount)
db.commit()
return {'OK': True}
def pay(j):
db = db_mysql.db_users['accounting']
cursor = db_mysql.newCursor("accounting")
api_key = j['api_key']
items = j['items']
username = profile.check_login(api_key)
for id in items:
cursor.execute("UPDATE invoice_item SET pain = True WHERE id = %s",
(id, ))
db.commit()
dict = {'OK': True, 'invoice_items': cursor.fetchall()}
return dict
def search_medicine(medicine_name):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute('SELECT `id`, `name`, `price`, unix_timestamp(exp_date) FROM medicine WHERE name like %s ;',
('%' + medicine_name + '%',))
print(medicine_name)
print(cursor.rowcount)
db.commit()
if cursor.rowcount == 0:
return {'OK': False, 'Error': 'no medicine with the name %s found' % medicine_name}
else:
for row in cursor:
print(row)
return {'OK': True, 'medicines': cursor.fetchall()}
def get_invoice(j):
db = db_mysql.db_users['accounting']
cursor = db_mysql.newCursor("accounting")
patient_name = j['patient_username']
api_key = j['api_key']
username = profile.check_login(api_key)
cursor.execute("SELECT FROM invoice_item WHERE patient = %s",
(patient_name, ))
db.commit()
dict = {'OK': True, 'invoice_items': cursor.fetchall()}
return dict
def search_doctor(docter_username):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute('SELECT * FROM doctor WHERE username like %s ;',
('%' + docter_username + '%',))
print((docter_username))
print(cursor.rowcount)
db.commit()
if cursor.rowcount == 0:
return {'OK': False, 'Error': 'no doctor with id found %s already exists in system ' % docter_username}
else:
for row in cursor:
print(row)
return {'OK': True, 'doctors': cursor.fetchall()}
def get_messages(j):
db = db_mysql.db_users['message']
cursor = db_mysql.newCursor("message")
api_key = j['api_key']
if api_key is None:
return {'OK': False, 'Error': "not a valid json"}
username = check_login(api_key)
cursor.execute("SELECT * FROM messages WHERE `reciever` = %s)", (username))
db.commit()
dict = {'OK': True, "messages": cursor.fetchall()}
return dict
def cancel_reserve(reserve_id, doctor_username):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'SELECT users.* FROM (patient LEFT OUTER JOIN users ON '
'(patient.username = users.username) ),'
'time_reserve LEFT OUTER JOIN time_request ON '
'(time_reserve.id = time_request.time_reserve_id) WHERE time_reserve.id = %s AND time_reserve.doctor_username = %s'
' AND time_request.time_reserve_id IS NOT NULL;',
(reserve_id, doctor_username))
user = cursor.fetchone()
print(user)
db.commit()
send_email(user['email'], user['name'] +
", your reservation is cancelled by doctor " + doctor_username)
request_management.user.receptor.cancel_reserve(reserve_id)
return {'OK': True}
def get_user_prescription(patient_username):
db = db_mysql.db_users['pharmacy']
cursor = db_mysql.newCursor("pharmacy")
cursor.execute(
'SELECT patient_id, medicine.Name ,unix_timestamp(prescription.date)'
' FROM prescription_item INNER JOIN prescription '
'ON prescription.id = prescription_item.prescription_id'
' INNER JOIN medicine ON medicine.id = prescription_item.medicine_id '
'WHERE prescription.patient_id = %s AND prescription.prescribed = 0',
(patient_username,))
prescriptions = cursor.fetchall()
print(prescriptions)
cursor.execute(
'UPDATE prescription SET prescribed = 1 where prescription.patient_id =%s',
(patient_username,))
db.commit()
return {'OK': True, 'prescription': prescriptions}
def forget_password(j):
db = db_mysql.db_users['signing']
cursor = db_mysql.newCursor("signing")
if 'email' in j:
email = j['email']
else:
return {'OK': False, 'Error': "not a valid json"}
cursor.execute('SELECT * FROM users WHERE email = %s ;', (email, ))
print(" ")
print(cursor.rowcount)
db.commit()
if cursor.rowcount == 0:
return {
'OK':
False,
'Error':
'Email %s already exists in system ' % cursor.fetchone()['email']
}
new_password = "".join(
random.choices(string.ascii_uppercase + string.digits, k=6))
salt = secrets.token_hex(16)
temp = (salt + new_password)
hash = hashlib.sha512()
hash.update(temp.encode('utf-8'))
password = hash.hexdigest()
cursor.execute(
"UPDATE users SET"
" password = %s , salt = %s WHERE email = %s",
(password, salt, email))
db.commit()
try:
send_password_by_email(email, new_password)
pass
except smtplib.SMTPRecipientsRefused as e:
print("email not sent: Bad Recipient" + e) # inform user
ok = False
error = "Email address not correct"
dict = {'OK': True}
return dict
def see_doctor_times(docter_username):
db = db_mysql.db
cursor = db_mysql.newCursor()
cursor.execute(
'SELECT time_reserve.*,(select time_request.time_reserve_id IS NOT NULL) as reserved FROM time_reserve LEFT OUTER JOIN time_request ON (time_reserve.id = time_request.time_reserve_id) WHERE time_reserve.doctor_username = %s ;',
(docter_username,))
print(cursor.rowcount)
db.commit()
if cursor.rowcount == 0:
return {'OK': False,
'Error': 'doctor %s not found' % docter_username}
else:
for row in cursor:
print(row)
dict = {'OK': True}
dict['reserve'] = cursor.fetchall()
return dict
def make_username(role):
alphabet = string.ascii_lowercase
db = db_mysql.db_users['signing']
cursor = db_mysql.newCursor("signing")
role = role_lookup(role)
while True:
if "error" not in role:
username = ''.join(secrets.choice(alphabet) for i in range(4))
username = role[:1].join("%s" % username)
print("hello new username is %s" % username)
# todo
cursor.execute('SELECT * FROM users WHERE username = %s ;',
(username, ))
db.commit()
if cursor.rowcount == 0:
break
return username
