-
Notifications
You must be signed in to change notification settings - Fork 3
/
database.py
215 lines (174 loc) · 10.6 KB
/
database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
import mysql.connector
def recreatedb(bool):
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
if bool:
mycursor.execute("DROP DATABASE Raddb")
mycursor.execute("CREATE DATABASE Raddb")
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="Raddb"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE PATIENTS (patientFname VARCHAR(100), patientLname VARCHAR(100), PID int NOT NULL AUTO_INCREMENT, patientpassword VARCHAR(100),age INT DEFAULT NULL, gender VARCHAR(10), mobilephone VARCHAR(12), Email VARCHAR(30), medicine VARCHAR(3), surgery VARCHAR(3), bloodTransfer VARCHAR(3), virusCorB VARCHAR(3), disease VARCHAR(3) ,PRIMARY KEY(PID))")
mycursor.execute("CREATE TABLE ADMINS (adminFname VARCHAR(100), adminLname VARCHAR(100), AID VARCHAR(20), adminpassword VARCHAR(100), age INT DEFAULT NULL, gender VARCHAR(10), mobilephone VARCHAR(12), Email VARCHAR(30),PRIMARY KEY(AID))")
mycursor.execute("CREATE TABLE DOCTORS (doctorFname VARCHAR(100), doctorLname VARCHAR(100), DID VARCHAR(20), doctorpassword VARCHAR(100), age INT DEFAULT NULL, gender VARCHAR(10), mobilephone VARCHAR(12), Email VARCHAR(30), clinicname VARCHAR(100), PRIMARY KEY(DID), AID VARCHAR(20), FOREIGN KEY (AID) REFERENCES ADMINS(AID) )")
mycursor.execute("CREATE TABLE REPORT (RPID int NOT NULL AUTO_INCREMENT, DoctorName VARCHAR(200),DID VARCHAR(20), PatientName VARCHAR(200),PID int, Date DATE NOT NULL, Diagnosis VARCHAR(300), Procedures VARCHAR(300), img VARCHAR(300),FOREIGN KEY (PID) REFERENCES PATIENTS(PID),FOREIGN KEY (DID) REFERENCES DOCTORS(DID),PRIMARY KEY(RPID))")
mycursor.execute("CREATE TABLE COMPLAINS (CONTACTNUMBER VARCHAR(12), SUBJECT VARCHAR(100), MESSAGE VARCHAR(300), CNUMBER int NOT NULL AUTO_INCREMENT, PRIMARY KEY(CNUMBER), PID int, FOREIGN KEY (PID) REFERENCES PATIENTS(PID) )")
mycursor.execute("CREATE TABLE UpdateDoctor (Salary VARCHAR(100), DID VARCHAR(20), FOREIGN KEY (DID) REFERENCES DOCTORS(DID), AID VARCHAR(20), FOREIGN KEY (AID) REFERENCES ADMINS(AID))")
mycursor.execute("CREATE TABLE APPOINTMENT (PFname VARCHAR(100), PLname VARCHAR(100), Date VARCHAR(100), Time VARCHAR(100), mobilephone VARCHAR(12), ClinicName VARCHAR(100), Email VARCHAR(30), APPNUMBER int NOT NULL AUTO_INCREMENT,PRIMARY KEY(APPNUMBER), DID VARCHAR(20), PID int, FOREIGN KEY (PID) REFERENCES PATIENTS(PID),FOREIGN KEY (DID) REFERENCES DOCTORS(DID) ) ")
# mycursor.execute("Show tables;")
# myresult = mycursor.fetchall()
# for x in myresult:
# print(x)
# *********************************** ADD TO ADMIN *******************************************
sql = "INSERT INTO ADMINS (adminFname , adminLname , AID , adminpassword , age , gender , mobilephone ,Email) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
value = [
('Maha', 'Mohammed', 'A1', '3333', '22', 'female',
'012111111111', '[email protected]'),
('Mohamed', 'Gamal', 'A2', '9999', '29', 'male',
'010555777777', '[email protected]'),
]
mycursor.executemany(sql, value)
mydb.commit()
# *********************************** ADD TO DOCTOR *******************************************
sql = "INSERT INTO DOCTORS (doctorFname , doctorLname , DID , doctorpassword ,clinicname ,age , gender, mobilephone, Email, AID ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
value = [
('Dina', 'Salama', 'D1', '1234', 'X-Ray', '21', 'Female',
'011266672701', '[email protected]', 'A1'),
('Fady', 'Nour', 'D2', '1324', 'CT', '33', 'Male',
'010555672701', '[email protected]', 'A2'),
('Mohamed', 'Ismail', 'D10', '9999', 'MRI', '21', 'Male',
'01142068704', '[email protected]', 'A2'),
('Mo', 'Moustafa', 'D3', '1111', 'UltraSound', '22',
'Male', '01115674821', '[email protected]', 'A1'),
]
mycursor.executemany(sql, value)
mydb.commit()
# *********************************** ADD TO PATIENT *******************************************
sql = "INSERT INTO PATIENTS (patientFname , patientLname , PID , patientpassword , age , gender , mobilephone, Email, medicine, surgery , bloodTransfer , virusCorB , disease ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
value = [
('Ereny', 'Eleya', '1', '9876', '21', 'female', '012888888888',
'[email protected]', 'NO', 'YES', 'NO', 'C', 'YES'),
('Ahmed', 'Mohammed', '2', '1368', '25', 'male', '012123456789',
'[email protected]', 'YES', 'NO', 'NO', 'B', 'YES'),
('Maryam', 'Ahmed', '3', '1111', '30', 'female', '010888834888',
'[email protected]', 'NO', 'YES', 'NO', 'C', 'YES'),
('Nour', 'Emad', '4', '1212', '23', 'female', '011888888222',
'[email protected]', 'NO', 'NO', 'NO', 'NO', 'NO'),
]
mycursor.executemany(sql, value)
mydb.commit()
# *********************************** ADD TO REPORT *******************************************
sql = "INSERT INTO REPORT (DoctorName, DID, PatientName, PID, Date, Diagnosis, Procedures, img) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
value = [
('Dina Salama', 'D1', 'Ereny', '1', '2022-06-03', 'Danger!!', 'Increase in cuteness level you HAVE to do something',
'../static/uploads/No17.jpg'),
('Fady Nour', 'D2', 'Ereny', '1', '2022-06-04', 'Trivial Report', 'noʎ ǝɹɐ ʍoɥ',
'../static/uploads/no5.jpg'),
('Mo Moustafa', 'D3', 'Ereny', '1', '2022-06-05', 'UltraSound', 'What is the sound of the sea?',
'../static/uploads/no4.jpg'),
]
mycursor.executemany(sql, value)
mydb.commit()
# *********************************** ADD TO APPOINTMENT *******************************************
sql = "INSERT INTO APPOINTMENT (PFname, PLname, Date, Time, mobilephone, ClinicName, Email, DID, PID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
value = [
('Ereny', 'Eleya', '2022-06-12', '12:00',
'012888888888', 'CT', '[email protected]', 'D2', '1'),
('Ereny', 'Eleya', '2022-06-03', '12:00',
'012888888888', 'CT', '[email protected]', 'D2', '1'),
('Ahmed', 'Mohammed', '2022-06-11', '12:00',
'012123456789', 'X-Ray', '[email protected]', 'D1', '2'),
('Ereny', 'Eleya', '2022-06-05', '12:00', '012888888888',
'X-Ray', '[email protected]', 'D1', '1'),
('Ereny', 'Eleya', '2022-06-03', '12:00', '012888888888',
'MRI', '[email protected]', 'D10', '1'),
('Ahmed', 'Mohammed', '2022-06-11', '01:00',
'012123456789', 'CT', '[email protected]', 'D2', '2'),
('Ereny', 'Eleya', '2022-06-04', '12:00',
'012888888888', 'CT', '[email protected]', 'D2', '1'),
('Ereny', 'Eleya', '2022-06-03', '12:00', '012888888888',
'X-Ray', '[email protected]', 'D1', '1'),
('Ahmed', 'Mohammed', '2022-06-11', '12:00',
'012123456789', 'MRI', '[email protected]', 'D10', '2'),
('Ereny', 'Eleya', '2022-06-11', '12:00', '012888888888',
'X-Ray', '[email protected]', 'D1', '1'),
('Maryam', 'Ahmed', '2022-06-03', '12:00',
'010888834888', 'CT', '[email protected]', 'D2', '3'),
('Maryam', 'Ahmed', '2022-06-05', '12:00', '010888834888',
'X-Ray', '[email protected]', 'D1', '3'),
('Maryam', 'Ahmed', '2022-06-12', '12:00',
'010888834888', 'MRI', '[email protected]', 'D10', '3'),
('Ereny', 'Eleya', '2022-06-03', '12:00', '012888888888', 'CT', '[email protected]', 'D2', '1'),
('Ereny', 'Eleya', '2022-06-04', '12:00', '012888888888', 'CT', '[email protected]', 'D2', '1'),
('Ahmed', 'Mohammed', '2022-06-05', '12:00', '012123456789', 'X-Ray', '[email protected]', 'D1', '2'),
('Ereny', 'Eleya', '2022-06-06', '12:00', '012888888888', 'X-Ray', '[email protected]', 'D1', '1'),
('Ereny', 'Eleya', '2022-06-07', '12:00', '012888888888', 'MRI', '[email protected]', 'D10', '1'),
('Ahmed', 'Mohammed', '2022-06-08', '01:00', '012123456789', 'CT', '[email protected]', 'D2', '2'),
('Nour', 'Emad', '2022-06-09', '12:00', '011888888222', 'CT', '[email protected]', 'D2', '4'),
('Nour', 'Emad', '2022-06-10', '12:00', '011888888222', 'X-Ray', '[email protected]', 'D1', '4'),
('Nour', 'Emad', '2022-06-06', '12:30', '011888888222', 'MRI', '[email protected]', 'D10', '4'),
('Nour', 'Emad', '2022-06-11', '1:30', '011888888222', 'X-Ray', '[email protected]', 'D1', '4'),
('Nour', 'Emad', '2022-06-03', '1:00', '011888888222', 'CT', '[email protected]', 'D2', '4'),
('Nour', 'Emad', '2022-06-05', '5:00', '011888888222', 'X-Ray', '[email protected]', 'D1', '4'),
('Nour', 'Emad', '2022-06-12', '4:00', '011888888222', 'MRI', '[email protected]', 'D10', '4'),
('Ahmed', 'Mohammed', '2022-06-07', '3:00', '012123456789', 'X-Ray', '[email protected]', 'D1', '2'),
('Ahmed', 'Mohammed', '2022-06-08', '2:00', '012123456789', 'X-Ray', '[email protected]', 'D1', '2'),
('Ahmed', 'Mohammed', '2022-06-10', '1:00', '012123456789', 'X-Ray', '[email protected]', 'D1', '2'),
]
mycursor.executemany(sql, value)
mydb.commit()
# *********************************** ADD TO UPDATE *******************************************
sql = "INSERT INTO UPDATEDOCTOR (Salary, AID, DID) VALUES (%s, %s, %s)"
value = [
('10000', 'A1', 'D1'),
('10000', 'A1', 'D3'),
('10000', 'A2', 'D2'),
('10000', 'A2', 'D10'),
]
mycursor.executemany(sql, value)
mydb.commit()
def connect():
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="Raddb"
)
mycursor = mydb.cursor()
return mycursor, mydb
def isEmail(email,cursor,isDoc=0):
# for no duplicate emails
if isDoc:
cursor.execute("SELECT Email FROM DOCTORS")
else:
cursor.execute("SELECT Email FROM patients")
allemails = cursor.fetchall()
for x in range(0, len(allemails)):
em = allemails[x]
if em[0] == email:
flag = False # -- Email already exists we shall display a message for that
break
else:
flag = True
return flag
def isID(id,cursor):
# for no duplicate emails
cursor.execute("SELECT DID FROM DOCTORS")
allIDs = cursor.fetchall()
for x in range(0, len(allIDs)):
em = allIDs[x]
if em[0] == id:
flag = False # -- Email already exists we shall display a message for that
break
else:
flag = True
return flag
recreatedb(1)