def check_uniqeid_exist(uniqeid):
uniqeid = uniqeid.upper()
if FileOperator.csv_or_db() == "db":
server_name, user_name, user_password, database_name = FileOperator.app_config_reader(
)
db = sql.connect(host=server_name,
user=user_name,
password=user_password,
charset='utf8',
use_unicode=True,
autocommit=True)
cursor = db.cursor()
query_in_users_exist = "SELECT unique_identifier FROM BloodDonationStorage.Donor WHERE unique_identifier = '" + uniqeid + "';"
cursor.execute(query_in_users_exist)
allusers = cursor.fetchall()
if allusers:
print("Already in the database.")
return False
else:
return True
else:
file = open("Data/donors.csv", "r", encoding="utf-8")
reader = csv.reader(file)
for line in reader:
if len(line) != 0 and uniqeid == line[6].upper():
print("Already in the database.")
file.close()
return False
file.close()
return True
def listing_database_db(which_file):
server_name, user_name, user_password, database_name = FileOperator.app_config_reader()
if 'donors' in which_file:
table_name = '.Donor'
else:
table_name = '.Event'
sql_command, result, header = [], [], []
sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \
"`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';")
sql_command.append("SELECT * FROM " + database_name + table_name)
dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name)
cursor = dbcon.cursor()
for i, one_command in enumerate(sql_command):
cursor.execute(one_command)
for cursor_message in cursor:
if i == 0:
header.append(cursor_message[0])
else:
result.append(cursor_message)
if len(result) != 0:
if len(result) == 1:
print("There is only one result:")
elif len(result) > 1:
print("There are " + str(len(result)) + " results:")
print("-" * 52)
for i in range(len(result)):
ListingDataBase.printer(i + 1, header, result[i])
else:
print("There is no data corresponding to this query...")
dbcon.close()
getch()
def search_in_file_db(which_file, search_term):
num_of_find = 1
server_name, user_name, user_password, database_name = FileOperator.app_config_reader()
sql_search_term = " LIKE '%" + search_term + "%' OR "
if 'donors' in which_file:
table_name = '.Donor'
else:
table_name = '.Event'
sql_command, result, header = [], [], []
sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \
"`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';")
sql_command.append("SELECT * FROM " + database_name + table_name + " WHERE ")
dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name)
cursor = dbcon.cursor()
cursor.execute(sql_command[0])
for cursor_message in cursor:
header.append(cursor_message[0])
for one_message in cursor_message:
sql_command[1] += one_message + sql_search_term
sql_command[1] = sql_command[1][0:len(sql_command[1]) - 4]
cursor.execute(sql_command[1])
for cursor_message in cursor:
if Search.search_and_print(search_term, header, cursor_message, num_of_find):
num_of_find += 1
if num_of_find == 1:
print("There is no data corresponding to this query...")
dbcon.close()
def search_in_ids_db(id):
server_name, user_name, user_password, database_name = FileOperator.app_config_reader()
original, changed, sql_command, line, header, table_name = [], [], [], [], [], ""
if id.isdigit():
table_name, id_name = ".Event", "id"
elif (id[:6].isdigit() and id[6:8].isalpha() and len(id) == 8) or (id[:6].isalpha() and id[6:8].isdigit() and len(id) == 8):
table_name, id_name = ".Donor", "unique_identifier"
else:
print("The input is not correct.")
time.sleep(1.5)
return
sql_command.append("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " + \
"`TABLE_SCHEMA`='" + database_name + "' AND `TABLE_NAME`='" + table_name[1:] + "';")
sql_command.append("SELECT * FROM " + database_name + table_name + " WHERE " + id_name + " = '" + id + "';")
dbcon = mysql.connector.connect(user=user_name, password=user_password, host=server_name, database=database_name)
cursor = dbcon.cursor()
cursor.execute(sql_command[0])
for cursor_message in cursor:
header.append(cursor_message[0])
cursor.execute(sql_command[1])
for cursor_message in cursor:
for one_message in cursor_message:
line.append(str(one_message))
if id_name == "id":
original.append(EventObject(line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8],line[9]))
changed.append(EventObject(line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8],line[9]))
line = []
else:
original.append(DonorObject(line[0],line[1],line[2],line[3],str(line[4]).replace("None", "Never"),line[5],line[6],line[7],line[8],line[9],line[10],line[11],line[12]))
changed.append(DonorObject(line[0],line[1],line[2],line[3],str(line[4]).replace("None", "Never"),line[5],line[6],line[7],line[8],line[9],line[10],line[11],line[12]))
line = []
if id_name == 'id' and original:
ChangeClass.change_process_event(original, changed, [id, header])
elif original:
ChangeClass.change_process_donor(original, changed, [id, header])
else:
print("Not included in the database.")
time.sleep(1.5)