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