Skip to content

/ 4007CEM-Resit-2

Permalink
master
Switch branches/tags

4007CEM-Resit-2/DBClassFirst.h

Go to file
 
 
Cannot retrieve contributors at this time
363 lines (358 sloc) 16.4 KB
Raw Blame
#include <iostream>
#include <string>
#include <sqlite3.h>
using namespace std;
class TimeTable {
private:
sqlite3 *db;
char *zErrMsg;
int rc;
const char* sql;
sqlite3_stmt *stmt;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i = 0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[1] : "NULL");
}
printf("\n");
return 0;
}
void DBErrors(){
if(rc){
cout<<"Data Base Error:"<<sqlite3_errmsg(db)<<endl;
closeDB();
}
}
void LecturersTable(){ //To be discarded
sql = "CREATE TABLE LECTURERS(" \
"LECTURER_ID NUMBER(8) PRIMARY KEY NOT NULL,"\
"PREFIX CHAR(5) NOT NULL,"\
"FIRST_NAME CHAR(15) NOT NULL,"\
"LAST_NAME CHAR(15) NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ModulesTable(){ //To be updated
sql = "CREATE TABLE MODULE("\
"MODULE_ID VARCHAR(10) PRIMARY KEY NOT NULL,"\
"MODULE_NAME CHAR(50) NOT NULL,"\
"STAGE VARCHAR(15) NOT NULL,"\
"CAPACITY NUMBER(3) NOT NULL,"\
"CREDITS NUMBER(3) NOT NULL,"\
"FACULTY CHAR(50) NOT NULL,"\
"COURSE_ID NUMBER(8),"\
"FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID),"\
"LECTURER_ID NUMBER(8),"\
"FOREIGN KEY (LECTURER_ID) REFERENCES LECTURERS(LECTURER_ID));";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void CoursesTable(){ //To be updated
sql = "CREATE TABLE COURSE("\
"COURSE_ID NUMBER(8) PRIMARY KEY NOT NULL,"\
"COURSE_NAME CHAR(50) NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ClassesTable(){ //To be Updated
sql = "CREATE TABLE CLASSES("\
"CLASS_NAME VARCHAR(25) PRIMARY KEY NOT NULL,"\
"DURATION VARCHAR(35) NOT NULL,"\
"TIME VARCHAR(10) NOT NULL,"\
"DATE VARCHAR(10) NOT NULL,"\
"MODULE_ID VARCHAR(10),"\
"FOREIGN KEY (MODULE_ID) REFERENCES MODULE(MODULE_ID));"
;
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void RoomsTable(){ //To be discarded
sql = "CREATE TABLE ROOM("\
"ROOM_NUMBER VARCHAR(7) PRIMARY KEY NOT NULL,"\
"CAPACITY NUMBER(3) NOT NULL,"\
"LOCATION VARCHAR(40) NOT NULL,"\
"CLASS_NAME VARCHAR(25),"\
"FOREIGN KEY (CLASS_NAME) REFERENCES CLASSES(CLASS_NAME));"
;
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void StudentsTable(){ // To be updated
sql = "CREATE TABLE STUDENT("\
"STUDENT_ID NUMBER(8) PRIMARY KEY NOT NULL,"\
"FIRST_NAME CHAR(15) NOT NULL,"\
"LAST_NAME CHAR(15) NOT NULL,"\
"DATE_OF_BIRTH VARCHAR(10) NOT NULL,"\
"NATIONALITY CHAR(20) NOT NULL,"\
"COURSE CHAR(50) NOT NULL,"\
"FOREIGN KEY (COURSE) REFERENCES COURSE(COURSE_NAME));"
;
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void IntoLecturers(const char* ID ,const char* PX, const char* FN, const char* LN){ // To be discarded
char *query = nullptr;
asprintf(&query, "INSERT INTO LECTURERS (LECTURER_ID,PREFIX,FIRST_NAME,LAST_NAME) "
"VALUES ('%s', '%s', '%s', '%s');",ID,PX,FN,LN );
ChecksNSubmission(query);
}
void IntoModules(const char* ID, const char* NOM, const char* STG, const char* CAP, const char* CRD,
const char* FAQ,const char* CID, const char* LID){ // To Be updated
char *query = nullptr;
asprintf(&query, "INSERT INTO MODULE (MODULE_ID, MODULE_NAME, STAGE, CAPACITY, "
"CREDITS, FACULTY, COURSE_ID, LECTURER_ID) "
"VALUES ('%s','%s','%s','%s','%s','%s','%s','%s');",ID,NOM,STG,CAP,CRD,FAQ,CID,LID);
ChecksNSubmission(query);
}
void IntoClasses(const char* NOM,const char* DUR,const char* TIM,const char* DAT,const char* MID){ //To be updated
char *query = nullptr;
asprintf(&query, "INSERT INTO CLASSES (CLASS_NAME, DURATION, TIME, DATE, MODULE_ID) "
"VALUES ('%s','%s','%s','%s','%s');",NOM,DUR,TIM,DAT,MID);
ChecksNSubmission(query);
}
void IntoRooms(const char* RN, const char* CAP, const char* LN, const char* CN){ //To be discarded
char *query = nullptr;
asprintf(&query, "INSERT INTO ROOM (ROOM_NUMBER, CAPACITY, LOCATION, CLASS_NAME) "
"VALUES ('%s','%s','%s','%s');",RN,CAP,LN,CN);
ChecksNSubmission(query);
}
void IntoStudents(const char* ID, const char* FN, const char* LN, const char* DOB,
const char* NN, const char* CS){ //To be updated
char *query = nullptr;
asprintf(&query, "INSERT INTO STUDENT (STUDENT_ID,FIRST_NAME,LAST_NAME, DATE_OF_BIRTH, "
"NATIONALITY,COURSE,MODULE_NAME) "
"VALUES ('%s','%s','%s','%s','%s','%s');",ID,FN,LN,DOB,NN,CS);
ChecksNSubmission(query);
}
void IntoCourses(const char* CID, const char* CN){ //To be Updated
char *query = nullptr;
asprintf(&query, "INSERT INTO COURSE (COURSE_ID,COURSE_NAME) VALUES ('%s','%s');",CID,CN);
ChecksNSubmission(query);
}
void CourseContents(const char* CID){ // To be discarded
char *query = nullptr;
asprintf(&query, "SELECT COURSE_ID, COURSE_NAME, MODULE_NAME FROM COURSE, MODULE "
"FROM COURSE, MODULE "
"WHERE COURSE_ID = '%s' "
"AND COURSE.COURSE_ID "
"= MODULE.COURSE_ID;",CID);
ChecksNSubmission(query);
}
void UniqStudentTT(const char* SID){ //To be discarded
char *query = nullptr;
asprintf(&query, "SELECT STUDENT_ID, COURSE_NAME, COURSE_ID, MODULE_NAME, CLASS_NAME, TIME, DATE, LOCATION "
"FROM COURSE, STUDENT, MODULE, ROOM, CLASSES "
"WHERE STUDENT_ID = '%s' "
"AND STUDENT.COURSE = COURSE.COURSE_NAME "
"AND COURSE.COURSE_ID = MODULE.COURSE_ID ",SID);
ChecksNSubmission(query);
}
public:
TimeTable(){
//This Portion allows user to name the database
cout<<"What would you like to name the Database? : "<<endl;
string FileName ;
cin >> FileName;
rc = sqlite3_open( FileName.c_str(), &db);
DBErrors();
//cout<<"Going into Courses"<<endl;
//CoursesTable();
DBErrors();
// cout<<"Going into Lecturers"<<endl;
//LecturersTable();
DBErrors();
cout<<"Going into Modules"<<endl;
ModulesTable();
DBErrors();
cout<<"Going into Classes"<<endl;
//ClassesTable();
DBErrors();
cout<<"Going into Rooms"<<endl;
// RoomsTable();
DBErrors();
cout<<"Going into Students"<<endl;
StudentsTable();
DBErrors();
}
void ChecksNSubmission(char *query){
sqlite3_prepare(db, query, strlen(query), &stmt, nullptr);
rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
free(query);
}
void FillLecturersTable(){
int NumL;
int i = 0;
cout<<"How many lecturers would you like to add to the record today?"<<endl;
cin>>NumL;
while (i != NumL){
i++;
string ID,PX,FN,LN;
cout<<"Please enter the ID number for lecturer number "<<i<<endl;
cin>>ID;
cout<<"Please enter the prefix for lecturer number "<<i<<endl;
cin>>PX;
cout<<"Please enter only the first name for lecturer number"<<i<<endl;
cin>>FN;
cout<<"Please enter only the last name for lecturer number"<<i<<endl;
cin>>LN;
const char* I_D = ID.c_str();
const char* P_X = PX.c_str();
const char* F_N = FN.c_str();
const char* L_N = LN.c_str();
IntoLecturers(I_D,P_X,F_N,L_N);
}
}
void FillModulesTable() {
int NumM;
int i = 0;
cout << "How many modules would you like to add to the record today? "<<endl;
cin >> NumM;
while (i != NumM) {
i++;
string I_D, N_OM, S_TG, C_AP, C_RD, F_AQ, L_ID, C_ID;
cout << "Please enter the module ID number for module number " << i << endl;
cin >>I_D ;
cout << "Please enter the name for module number " << i << endl;
cin >>N_OM ;
cout << "Please enter the stage in which a student would undertake module number " << i << endl;
cin >>S_TG ;
cout << "Please enter the capacity of module number " << i << endl;
cin >>C_AP ;
cout << "Please enter the amount of credits to be achieved for module number " << i << endl;
cin >>C_RD ;
cout << "Please enter relevant faculty for module number " << i << endl;
cin >>F_AQ;
cout << "Please enter the course ID number for module number " << i << endl;
cin >>C_ID;
cout << "Please enter the ID # for the lecturer that teaches module number " << i << endl;
cin >>L_ID;
const char* ID = I_D.c_str();
const char* NOM = N_OM.c_str();
const char* STG = S_TG.c_str();
const char* CAP = C_AP.c_str();
const char* CRD = C_RD.c_str();
const char* FAQ = F_AQ.c_str();
const char* LID = L_ID.c_str();
const char* CID = C_ID.c_str();
IntoModules(ID,NOM,STG,CAP,CRD,FAQ,CID,LID);
}
}
void FillClassesTable(){
int NumC;
int i = 0;
cout<<"How many classes would you like to add to the record today? "<<endl;
cin>>NumC;
while (i != NumC){
i++;
string N_OM,D_UR,T_IM,D_AT,M_ID;
cout<<"Please enter the name for class number "<<i<<endl;
cin>>N_OM;
cout<<"Please enter the duration for class number "<<i<<endl;
cin>>D_UR;
cout<<"Please enter thr time for class number "<<i<<endl;
cin>>T_IM;
cout<<"Please enter the date for class number "<<i<<endl;
cin>>D_AT;
cout<<"Please enter the module ID for class number "<<i<<endl;
cin>>M_ID;
const char* NOM = N_OM.c_str();
const char* DUR = D_UR.c_str();
const char* TIM = T_IM.c_str();
const char* DAT = D_AT.c_str();
const char* MID = M_ID.c_str();
IntoClasses(NOM,DUR,TIM,DAT,MID);
}
}
void FillRoomsTable(){
int NumR;
int i = 0;
cout<<"How many rooms would you like to add to the record today? "<<endl;
cin>>NumR;
while (i != NumR){
i++;
string R_N,C_AP,L_N,C_N;
cout<<"Please enter the room number for room number "<<i<<endl;
cin>> R_N;
cout<<"Please enter the capacity for room number "<<i<<endl;
cin>> C_AP;
cout<<"Please enter the location for room number "<<i<<endl;
cin>> L_N;
cout<<"Please enter the class name taking place in room number "<<i<<endl;
cin>> C_N;
const char* RN = R_N.c_str();
const char* CAP = C_AP.c_str();
const char* LN = L_N.c_str();
const char* CN = C_N.c_str();
IntoRooms(RN,CAP,LN,CN);
}
}
void FillStudentsTable(){
int NumS;
int i = 0;
cout<<"How many students would you like to add to the record today? "<<endl;
cin>>NumS;
while (i != NumS){
i++;
string id,fn,ln,dob,nn,cs,mn;
cout<<"Please enter the ID number for student number "<<i<<endl;
cin>> id;
cout<<"Please enter only the first name for student number "<<i<<endl;
cin>> fn;
cout<<"Please enter only the last name for student number "<<i<<endl;
cin>> ln;
cout<<"Please enter the date of birth for student number "<<i<<" (dd-mm-yy)"<<endl;
cin>> dob;
cout<<"Please enter the nationality for student number "<<i<<endl;
cin>> nn;
cout<<"Please enter the course name of student number "<<i<<endl;
cin>> cs;
const char* ID = id.c_str();
const char* FN = fn.c_str();
const char* LN = ln.c_str();
const char* DOB = dob.c_str();
const char* NN = nn.c_str();
const char* CS = cs.c_str();
IntoStudents(ID,FN,LN,DOB,NN,CS);
}
}
void FillCoursesTable(){
int NumC;
int i = 0;
cout <<"How many courses would you like to add to the record today? "<<endl;
cin>>NumC;
while (i != NumC){
i++;
string cid,cn;
cout<<"Please enter the course ID for course number "<<i<<endl;
cin >> cid;
cout<<"Please enter the course name for course number "<<i<<endl;
cin >> cn;
const char* CID = cid.c_str();
const char* CN = cn.c_str();
IntoCourses(CID,CN);
}
}
void CheckCourse(){
string cid;
cout<<"Please enter the course ID that you would like to know more about: "<<endl;
cin >> cid;
const char* CID = cid.c_str();
CourseContents(CID);
} //Check All Courses And Their Corresponding Modules
void EnrolledStudents(){
sql = "SELECT STUDENT_ID, COURSE_NAME, COURSE_ID FROM STUDENT, COURSE "
"FROM STUDENT,COURSE "
"WHERE STUDENT.COURSE = COURSE.COURSE_NAME;";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
} //Check All Enrolled Students
void CheckUniqStudent(){
string sid;
cout<<"Please enter the the student's ID number for their corresponding timetable: "<<endl;
cin >> sid;
const char* SID = sid.c_str();
UniqStudentTT(SID);
} //Check For A Specific student's Time Table
void DeleteRow(const char* TableName, const char* ColoumnName, const char* ID){
char *query = nullptr;
asprintf(&query, "DELETE FROM ''%s'' WHERE '%s' = '%s';",TableName,ColoumnName,ID);
ChecksNSubmission(query);
}
void closeDB(){
sqlite3_close(db);
}
};