void AddCourseUD::loadByCareer(void) { int program=ddCareer.GetSelectedData(); Sql::SqlConnection conn; wstring cmd; ddCourse.Items.DeleteAll(); ddProfessor.Items.DeleteAll(); int dept; Sys::Format(cmd,L"SELECT c.course_id, c.descr\ FROM course c, prog_course pg, program p\ WHERE c.course_id=pg.course_id\ AND pg.program_id=p.program_id\ AND p.program_id=%d", program); try { conn.OpenSession(DSN,USERNAME,PASSWORD); conn.ExecuteSelect(cmd, 100, ddCourse); Sys::Format(cmd,L"SELECT department_id FROM program WHERE program_id=%d",program); dept=conn.GetInt(cmd); if(dept!=1) { Sys::Format(cmd,L"SELECT p.professor_id, p.last_name_p+' '+p.last_name_m+', '+p.name\ FROM professor p, department d, program pg\ WHERE p.department_id=d.department_id AND pg.department_id=d.department_id AND pg.program_id=%d\ ORDER BY p.last_name_p",program); } else { Sys::Format(cmd,L"SELECT p.professor_id, p.last_name_p+' '+p.last_name_m+', '+p.name\ FROM professor p ORDER BY p.last_name_p"); } conn.ExecuteSelect(cmd, 100, ddProfessor); conn.CloseSession(); }
void CoordDlg::UpdateddProf() { ddProfessor.SetRedraw(false); ddProfessor.Items.DeleteAll(); wstring cmd; int program_id,dept; program_id=ddProgram.SelectedData; Sql::SqlConnection conn; try { conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase //conn.OpenSession(hWnd, CONNECTION_STRING); Sys::Format(cmd,L"SELECT department_id FROM program WHERE program_id=%d",program_id); dept=conn.GetInt(cmd); if(dept!=1) { Sys::Format(cmd,L"SELECT p.professor_id,p.last_name_p+' '+p.last_name_m+', '+p.name,p.email FROM professor p, program pr, department d \ WHERE p.department_id=d.department_id AND d.department_id=pr.department_id AND pr.program_id=%d ORDER BY p.last_name_p",program_id); } else { Sys::Format(cmd,L"SELECT p.professor_id,p.last_name_p+' '+p.last_name_m+', '+p.name FROM professor p ORDER BY p.last_name_p"); } conn.ExecuteSelect(cmd, 100, ddProfessor); ddProfessor.SelectedIndex=0; }
void CoordMng::Window_Open(Win::Event& e) { this->AlwaysOnTop(true); int dept; wstring cmd; //________________________________________________________ lvProfessor lvProfessor.Cols.Add(0, LVCFMT_LEFT, 250, L"Name"); lvProfessor.Cols.Add(1, LVCFMT_RIGHT, 150, L"Email"); lvProfessor.Cols.Add(2, LVCFMT_RIGHT, 80, L"Extension"); //________________________________________________________ lvCourse lvCourse.Cols.Add(0, LVCFMT_LEFT, 150, L"Course Key"); lvCourse.Cols.Add(1, LVCFMT_LEFT, 300, L"Course"); try { Sql::SqlConnection conn; conn.OpenSession(DSN,USERNAME,PASSWORD); Sys::Format(cmd,L"SELECT department_id FROM program WHERE program_id=%d",career_id); dept=conn.GetInt(cmd); if(dept!=1) { Sys::Format(cmd,L"SELECT p.professor_id,p.last_name_p+' '+p.last_name_m+', '+p.name,p.email,p.extension FROM professor p, program pr, department d \ WHERE p.department_id=d.department_id AND d.department_id=pr.department_id AND pr.program_id=%d ORDER BY p.last_name_p",career_id); } else { Sys::Format(cmd,L"SELECT p.professor_id,p.last_name_p+' '+p.last_name_m+', '+p.name,p.email,p.extension FROM professor p ORDER BY p.last_name_p"); } conn.ExecuteSelect(cmd, 100, lvProfessor); Sys::Format(cmd,L"SELECT c.course_id, c.course_key, c.descr FROM course c, prog_course pc, program p WHERE c.course_id=pc.course_id AND pc.program_id=p.program_id AND pc.program_id=%d",career_id); conn.ExecuteSelect(cmd, 100, lvCourse); }
void CoordDlg::Window_Open(Win::Event& e) { this->Text=L"Coordinator Insert/Edit"; int prof_id; wstring cmd; Sql::SqlConnection conn; try { conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase //conn.OpenSession(hWnd, CONNECTION_STRING); conn.ExecuteSelect(L"SELECT program_id, career_name FROM program", 100, ddProgram); ddProgram.SelectedIndex=0; UpdateddProf(); if (coordinator_id < 0) return; Sys::Format(cmd, L"SELECT professor_id, program_id, username, pass, isAdmin FROM coordinator WHERE coordinator_id=%d", coordinator_id); conn.ExecuteSelect(cmd); conn.BindColumn(1, ddProfessor); conn.BindColumn(2, ddProgram); conn.BindColumn(3, tbxUsername, 128); conn.BindColumn(4, tbxPass, 128); conn.BindColumn(5, ckIsadmin); if (conn.Fetch() == false) { this->MessageBox(L"No data was returned", L"Error", MB_OK); } UpdateddProf(); Sys::Format(cmd, L"SELECT professor_id FROM coordinator WHERE coordinator_id=%d", coordinator_id); prof_id=conn.GetInt(cmd); ddProfessor.SelectedData=prof_id; } catch (Sql::SqlException e) { this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR); } }
void AddCourseUD::btAccept_Click(Win::Event& e) { if(isEditing!=1) { int course_id=ddCourse.GetSelectedData(); int professor_id=ddProfessor.GetSelectedData(); int classroom_id=ddClassroom.GetSelectedData(); int week_id=ddClassdays.GetSelectedIndex()+1; int classtime_id=ddClasstime.GetSelectedData(); if(course_id>1 || professor_id>1 || classroom_id>1 || week_id >1 || classtime_id >1) { this->MessageBox(L"You haven't selected all the required data", L"Error", MB_OK | MB_ICONERROR); return; } wstring cmd; char group='A'; Sql::SqlConnection conn; int no_groups; try { conn.OpenSession(DSN, USERNAME, PASSWORD); Sys::Format(cmd,L"SELECT COUNT(*) FROM schedule WHERE course_id=%d AND period_id=%d AND week_day_id BETWEEN 1 AND 2",course_id, current_period); no_groups=conn.GetInt(cmd); group+=no_groups; if(week_id==1) { Sys::Format(cmd, L"INSERT INTO schedule(period_id,professor_id,course_id,grupo,classroom_id,classtime_id,week_day_id)VALUES(%d,%d,%d,'%c',%d,%d,1)",current_period,professor_id,course_id,group, classroom_id,classtime_id); conn.ExecuteNonQuery(cmd); Sys::Format(cmd, L"INSERT INTO schedule(period_id,professor_id,course_id,grupo,classroom_id,classtime_id,week_day_id)VALUES(%d,%d,%d,'%c',%d,%d,3)",current_period,professor_id,course_id,group, classroom_id,classtime_id); conn.ExecuteNonQuery(cmd); Sys::Format(cmd, L"INSERT INTO schedule(period_id,professor_id,course_id,grupo,classroom_id,classtime_id,week_day_id)VALUES(%d,%d,%d,'%c',%d,%d,5)",current_period,professor_id,course_id,group, classroom_id,classtime_id); conn.ExecuteNonQuery(cmd); } else { Sys::Format(cmd, L"INSERT INTO schedule(period_id,professor_id,course_id,grupo,classroom_id,classtime_id,week_day_id)VALUES(%d,%d,%d,'%c',%d,%d,2)",current_period,professor_id,course_id,group, classroom_id,classtime_id); conn.ExecuteNonQuery(cmd); Sys::Format(cmd, L"INSERT INTO schedule(period_id,professor_id,course_id,grupo,classroom_id,classtime_id,week_day_id)VALUES(%d,%d,%d,'%c',%d,%d,4)",current_period,professor_id,course_id,group, classroom_id,classtime_id); conn.ExecuteNonQuery(cmd); } conn.CloseSession(); } catch (Sql::SqlException e) { this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR); } } else { classroom_id=ddClassroom.GetSelectedData(); classtime_id=ddClasstime.GetSelectedData(); wstring cmd; int rows=0; Sql::SqlConnection conn; try { conn.OpenSession(DSN, USERNAME, PASSWORD); Sys::Format(cmd, L"SELECT COUNT(*) FROM schedule WHERE professor_id=%d AND classtime_id=%d",professor_id,classtime_id); rows=conn.GetInt(cmd); if(rows>0) { this->MessageBoxW(L"This Professor is already teaching at this hour", L"Error", MB_OK | MB_ICONERROR); return; } Sys::Format(cmd, L"SELECT COUNT(*) FROM schedule WHERE classroom_id=%d AND classtime_id=%d",classroom_id,classtime_id); rows=conn.GetInt(cmd); if(rows>0) { this->MessageBoxW(L"This CLassroom is already occupied", L"Error", MB_OK | MB_ICONERROR); return; } Sys::Format(cmd, L"SELECT isbase FROM professor WHERE professor_id=%d",professor_id); rows=conn.GetInt(cmd); if(rows==1 && classtime_id>=5) { this->MessageBoxW(L"This Professor is fulltime", L"Error", MB_OK | MB_ICONERROR); return; } Sys::Format(cmd, L"UPDATE schedule SET classroom_id=%d WHERE course_id=%d AND grupo='%c'",classroom_id,course_id,grupo); conn.ExecuteNonQuery(cmd); Sys::Format(cmd, L"UPDATE schedule SET classtime_id=%d WHERE course_id=%d AND grupo='%c'",classtime_id,course_id,grupo); conn.ExecuteNonQuery(cmd); conn.CloseSession(); } catch (Sql::SqlException e) { this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR); } } this->EndDialog(TRUE); }