GoLunar, a student registration system using SQL

==========================================================================

//Xiaoyuan Suo
//CSC4710
//hw5

import jpb.*;
import java.sql.*;
import java.lang.*;
import java.util.*;
import java.io.*;

public class GoLunar {
public static String userID = null;
public static String userName = null;
public static String semester = null;
public static void main(String[] args) throws SQLException, IOException {

System.out.println("\n\n\n\n\n*******************WELCOME!************************");
GoLunar data = new GoLunar();
System.out.println();

char response;
do {
response = data.loginMenu();
switch (response) {
case '1': data.studentLogin(); break;
case '2': data.stuffLogin(); break;
case '3': data.registarLogin(); break;
}
} while(response != 'q');
System.out.println("logged out");
}
char loginMenu() throws SQLException{
SimpleIO.prompt("please enter ur userID: ");
userID = SimpleIO.readLine();
char response = 'q';
String staff = "1000";
String registar = "2000";
String query1 = "select * from students where sid = '" +userID+"'";
String query2 = "select * from staff where tid = '" +userID+"'";
Connection conn = getConnection();
Statement stmt = conn.createStatement();
try {
ResultSet rset1 = stmt.executeQuery(query1);

if (rset1.next() ) {
response = '1';
userName = rset1.getString("fname") + " " + rset1.getString("lname");
}else {
ResultSet rset2 = stmt.executeQuery(query2);

if (rset2.next() ){
if(staff.equals(rset2.getString("tid"))){
response = '2';
userName = rset2.getString("fname") + " " + rset2.getString("lname");}
else if(registar.equals(rset2.getString("tid"))){
response = '3';
userName = rset2.getString("fname") + " " + rset2.getString("lname");}
else
System.out.println("UserID not found");
}
}
} catch (Exception e) {
e.printStackTrace();
}
return response;
}
void studentLogin() throws SQLException {
String password = null;
System.out.println();

SimpleIO.prompt("Enter password: ");
password = SimpleIO.readLine();
SimpleIO.prompt("Semester (e.g. FA2003,SP2003,SU2003): ");
semester = SimpleIO.readLine();
String query = "select * from students where sid = '" +userID+"'";
Connection conn = getConnection();
Statement stmt = conn.createStatement();
try {
ResultSet rset = stmt.executeQuery(query);

if (rset.next() ) {
if (password.equals(rset.getString("password")) ){
System.out.println("logged in");
StudentMainMenuHandler handler = new StudentMainMenuHandler();
} else {
System.out.println("password is not correct");
studentLogin();
}
} else {
System.out.println("UserID not found");
studentLogin();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

void registarLogin() throws SQLException {
String password = null;
System.out.println();

SimpleIO.prompt("Enter password: ");
password = SimpleIO.readLine();
SimpleIO.prompt("Semester (e.g. FA2003,SP2003,SU2003): ");
semester = SimpleIO.readLine();

String query = "select * from staff where tid = '" +userID+"'";
Connection conn = getConnection();
Statement stmt = conn.createStatement();
try {
ResultSet rset = stmt.executeQuery(query);

if (rset.next() ) {
if (password.equals(rset.getString("password")) ){
System.out.println("logged in");
RegistarMainMenuHandler handler = new RegistarMainMenuHandler();
} else {
System.out.println("password is not correct");
registarLogin();
}
} else {
System.out.println("UserID not found");
studentLogin();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

void stuffLogin() throws SQLException {
String password = null;
System.out.println();

SimpleIO.prompt("Enter password: ");
password = SimpleIO.readLine();
SimpleIO.prompt("Semester (e.g. FA2003,SP2003,SU2003): ");
semester = SimpleIO.readLine();

String query = "select * from staff where tid = '" +userID+"'";
Connection conn = getConnection();
Statement stmt = conn.createStatement();
try {
ResultSet rset = stmt.executeQuery(query);

if (rset.next() ) {
if (password.equals(rset.getString("password")) ){
System.out.println("logged in");
StuffMainMenuHandler handler = new StuffMainMenuHandler();
} else {
System.out.println("password is not correct");
stuffLogin();
}
} else {
System.out.println("UserID not found");
stuffLogin();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static Connection getConnection() throws SQLException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Could not load the driver");
}
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@tinman.cs.gsu.edu:1521:sid9ir2","cscxnsx","cscxnsx");
return conn;
}

}

class StudentMainMenuHandler {
GoLunar student = new GoLunar();
String name = student.userName;
StudentMainMenuHandler() throws SQLException, Exception{
initial();
}
void initial() throws SQLException, Exception{
char response;
do{
response = this.studentMainMenu();
switch(response){
case '1': this.addSection(); break;
case '2': this.dropSection() ; break;
case '3': this.schedule(); break;
case '4': this.feeDetail(); break;
case '5': this.transcript(); break;
}
}while (response != '6');
System.out.println();
System.out.println("you have logged out\n\n");
int select = -1;
do{
System.out.print("please press Enter to go back to main menu");
try{
select = System.in.read();
}catch(Exception e){e.printStackTrace();}
}while (select != '\n');
}
char studentMainMenu(){
String semester = new String();

String response = new String();
System.out.println("**********************************************************************");
System.out.println("*** ***");
System.out.println("*** Welcome to the GoLunar - Online Registration System ***");
System.out.println("*** "+name+"- student ***");
System.out.println("*** ***");
System.out.println("**********************************************************************");

SimpleIO.prompt(" 1. Add a Section \n\n"+
" 2. Drop a Section\n\n"+
" 3. See Schedule for a Term\n\n"+
" 4. See Fee detail\n\n"+
" 5. See Transcript\n\n"+
" 6. Quit\n ur option? ");

response = SimpleIO.readLine().toLowerCase();
return response.charAt(0);
}
void addSection() throws SQLException, Exception{
Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet resection = null;
ResultSet rset = null;

String [] crn = new String[20];
SimpleIO.prompt("\nCRN: ");
String crn_in = SimpleIO.readLine();
String ID = student.userID;
String sem = student.semester;
String term = sem.substring(0,2).trim();
String year = sem.substring(2).trim();
String query1 = "select * from sections where crn = "+crn_in+" and year = '"+year+"' and term = '"
+term+"'";
String query2 = "select s.sType, t.cno from students s, enrolls e, sections t where s.sid = "+ ID+
" and t.crn = "+crn_in+" and t.term = '"+term+"' and t.year= "+year;
try{
rset = stmt.executeQuery(query1);
}catch(SQLException e){ System.out.println("invalid CRN");
addSection();}

int i=0;
if(!rset.next()){
System.out.println("no such a class exist in database");
addSection();}

rset = null;
try{
resection = stmt.executeQuery(query2);
}catch(SQLException e){ System.out.println("invalid CRN");
addSection();}

resection.next();

String sT = resection.getString("sType").trim();
int cN = Integer.parseInt(resection.getString("cno").trim());

if(sT.equalsIgnoreCase("UGRAD") && (cN>5000)){
System.out.println("undergraduate student can not register for this class");
addSection();
}
resection = null;
String query3 = "select c.ctitle, c.cno, c.cprefix from courses c, sections s where s.crn = "+crn_in+
" and s.cno = c.cno and s.cprefix = c.cprefix "+
" and s.year = "+year+" and s.term = '"+term+"'";
String cprefix="", cno="", ctitle="";
try{
resection = stmt.executeQuery(query3);
resection.next();
cprefix = resection.getString("cprefix").trim();
cno = resection.getString("cno").trim();
ctitle = resection.getString("ctitle").trim();
}catch(SQLException e){System.out.println("ERROR, wrong CRN");
addSection();}
resection = null;
String q = "insert into enrolls values ("+ID+", '"+term+"', "+year+", "+crn_in+", null)";

boolean test = false;

try {
rset = stmt.executeQuery(q);
System.out.println(cprefix+cno+" "+ctitle+" added");
} catch (SQLException e){System.out.println("course can not be added");
addSection();}

rset = null;
stmt.close();
conn.close();

}
void dropSection() throws SQLException, Exception{
Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet resection = null;
ResultSet rset = null;

String [] crn = new String[20];
SimpleIO.prompt("\nCRN: ");
String crn_in = SimpleIO.readLine();
String ID = student.userID;
String sem = student.semester;
String term = sem.substring(0,2).trim();
String year = sem.substring(2).trim();
String query1 = "select * from enrolls s where s.sid = '"+ID+"' and"+
" s.year = '"+year +"' and s.term = '"+term+"' and s.crn = '"+crn_in+"'";
String q = "delete from enrolls where sid = "+ID+" and crn = "+crn_in;
String query3 = "select c.ctitle, c.cno, c.cprefix from courses c, sections s where s.crn = "+crn_in+
" and s.cno = c.cno and s.cprefix = c.cprefix "+
" and s.year = "+year+" and s.term = '"+term+"'";
String cprefix="", cno="", ctitle="";
try{
resection = stmt.executeQuery(query3);
resection.next();
cprefix = resection.getString("cprefix").trim();
cno = resection.getString("cno").trim();
ctitle = resection.getString("ctitle").trim();
}catch(SQLException e){System.out.println("ERROR, wrong CRN");
addSection();}

resection = null;

try{
resection = stmt.executeQuery(query1);
}catch(SQLException e){ System.out.println("invalid crn"); }

int i=0;
if(!resection.next()){
System.out.println("no such a class exist in ur file");
dropSection();}
else{
try {
rset = stmt.executeQuery(q);
System.out.println(cprefix+cno+" "+ctitle+" deleted");
} catch (Exception e){System.out.println("course can not be deleted");
dropSection();}
}


stmt.close();
conn.close();

}
void transcript() throws SQLException, Exception{
String data1 = new String();
int totalHour = 0;
int semHour = 0;
double Gpa = 0.00;
double courseGrade =0.00;
char lettGrade;
int hour = 4;
double semesterGpa = 0.00;
int earnedHour = 0;
String[][] semester = new String[20][20];
String[][] transcript = new String[20][20];
int numOfSem = 0;
String id = student.userID;
Connection conn = getConnection();
Statement stmt = conn.createStatement();
String query1 = "select term, year from enrolls where sid = '"+id+"' group by term, year order by year, "+
"decode(term, 'SP',1,'SU',2,'FA',3)";
String query = "select s.term, s.year, s.cprefix, c.cno, s.crn, c.ctitle, c.chours, e.grade "+
"from enrolls e, sections s, courses c where e.sid = '"+id+"' and e.crn = s.crn and s.cno=c.cno";

ResultSet rset = null;
try{
rset = stmt.executeQuery(query1);
}catch(SQLException e1){
while(e1 !=null){
System.out.println("Message: "+e1.getMessage());
e1 = e1.getNextException();
}
}
if(!rset.next()){
System.out.println("unknown student");
return;
}
do{
semester[numOfSem][0] = rset.getString(1).trim();
semester[numOfSem][1] = rset.getString(2).trim();
numOfSem++;
}while(rset.next());
rset = null;
try{
rset = stmt.executeQuery(query);
}catch(SQLException e2){
while(e2!=null){
System.out.println("Message: "+e2.getMessage());
}
System.exit(0);
}
String G = "FDCBA";
int index = 0;
double [] credit = new double[20];
int grade1= 0;
while(rset.next()){
grade1=G.indexOf(rset.getString(8).trim().toUpperCase());

if(grade1 != -1){
transcript[index][0] = rset.getString(1).trim();
transcript[index][1] = rset.getString(2).trim();
transcript[index][2] = rset.getString(3).trim();
transcript[index][3] = rset.getString(4).trim();
transcript[index][4] = rset.getString(5).trim();
transcript[index][5] = rset.getString(6).trim();
transcript[index][6] = rset.getString(7).trim();
transcript[index][7] = rset.getString(8).trim();
credit[index] = Double.parseDouble(transcript[index][6])*grade1;
index++;
}
}

for(int u=0; u<numOfSem; u++){
double semPoint = 0.0;
double seHour = 0.0;
if(semester[u][0].equals("SU")){
System.out.println("Summer "+ semester[u][1]);
}
else if(semester[u][0].equals("SP")){
System.out.println("Spring "+semester[u][1]);
}
else if(semester[u][0].equals("FA")){
System.out.println("Fall " + semester[u][1]);
}
for(int j=0; j<index; j++){
if(transcript[j][0].equals(semester[u][0])&&transcript[j][1].equals(semester[u][1])){
System.out.print(" "+transcript[j][2]);
for(int k=0; k<5-transcript[j][2].length(); k++)
System.out.print(" ");
System.out.print(transcript[j][3]+" "+transcript[j][4]+" "+transcript[j][5]);
for(int k=0; k<45-transcript[j][5].length(); k++)
System.out.print(" ");
System.out.print(transcript[j][6]+" "+transcript[j][7]);
for(int k=0; k<2-transcript[j][7].length(); k++)
System.out.print(" ");
semPoint = semPoint + credit[j];
seHour = seHour + Double.parseDouble(transcript[j][6]);
courseGrade += credit[j];
earnedHour += Double.parseDouble(transcript[j][6]);
System.out.println();
}
semesterGpa = Math.round((semPoint/seHour)*100);
}
//System.out.println(courseGrade + " "+earnedHour+" "+semPoint+" "+seHour);
Gpa = Math.round((courseGrade/earnedHour)*100);
//semesterGpa = Math.round((semPoint/seHour)*100);
System.out.println("Semester GPA: " + semesterGpa/100 + " GPA: "+Gpa/100);
}

stmt.close();
conn.close();
}


void feeDetail() throws SQLException, Exception{
Connection conn = getConnection();
Statement stmt = conn.createStatement();

String ID = student.userID;
ResultSet resection = null;
ResultSet rset = null;

String inSt = new String();
String stat = new String();
SimpleIO.prompt("\nterm: ");
String sem = SimpleIO.readLine();
String term = sem.substring(0,2).trim().toUpperCase();
String year = sem.substring(2);
String term1 = new String();
if (term.equals("FA")){
term1 = "Fall";}
else if (term.equals("SP")){
term1 = "Spring";}
else if (term.equals("SU")){
term1 = "Summer";}

System.out.println("\n"+term1 + " " + year);

String query = "select s.inState from students s where s.sid = '"+ID+"'";

try{
resection = stmt.executeQuery(query);
resection.next();
inSt = resection.getString("inState").trim();
}catch(SQLException e){ e.printStackTrace(); }

if(inSt.equalsIgnoreCase("Y"))
stat="INSTATE";
else if(inSt.equalsIgnoreCase("N"))
stat = "OUTOFSTATE";

String q = "SELECT F.FEE FROM STUDENTS S, VARIABLEFEERATE F WHERE S.SID = '"+ID+"'"+
"AND S.STYPE=F.STYPE AND F.INOROUTOFSTATE = '"+stat+"'";

resection = null;

try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

rset.next();
int fee = Integer.parseInt(rset.getString("fee"));

rset=null;
query = "select c.chours "+
"from courses c, sections s, enrolls e "+
"where e.sid = "+ID+" and s.term = '"+term+"' and s.year = '"+year+"'"+
"and e.crn = s.crn and c.cprefix = s.cprefix and c.cno = s.cno" ;
int totalHours = 0;
try{
resection = stmt.executeQuery(query);
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
totalHours +=Integer.parseInt(resection.getString("chours").trim());}
resection = null;
double tuition = Math.round((totalHours*fee)*100)/100;
double otherFee=0.00;
query = "select * from fixedfee";

System.out.println("\n");
System.out.println("Tuition - "+stat+"\n"+
totalHours + " hours\t\t"+tuition+"\n");

try{
resection = stmt.executeQuery(query);
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
System.out.println(resection.getString("feeName")+":\t\t"+
Math.round(Integer.parseInt((resection.getString("fee")).trim())*100)/100);
otherFee += Math.round((Integer.parseInt((resection.getString("fee")).trim()))*100)/100;
}
resection = null;
System.out.println("\t\t\t---------\n"+
"\t\t\t"+(tuition+otherFee)+"\n"+
"\t\t\t---------\n");

stmt.close();
conn.close();

}
void schedule() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
SimpleIO.prompt("\nterm: ");
String sem = SimpleIO.readLine();
String term = sem.substring(0,2);
String year = sem.substring(2);
String id = student.userID;
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q = "select e.crn, s.cprefix, s.cno, c.ctitle, s.startTime, s.endTime, s.days, s.room, "+
"s.instructor from enrolls e, sections s, courses c "+
"where e.sid = '"+ id +"' and s.cprefix = c.cprefix and s.cno = c.cno "+
"and e.crn = s.crn and s.term = '"+term+"' and s.year = '"+year+"' ";


int crn = 0;
try{
resection = stmt.executeQuery(q);
System.out.println("CRN\tCourse\t\tTitle\t\t\t\t\tDays\tTime\t\tRoom Instructor");
System.out.println("-------------------------------------------------------------------------------");
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
data1 = resection.getString("crn")+"\t"+resection.getString("cprefix")+
resection.getString("cno")+"\t"+ resection.getString("ctitle")+"\t"+
resection.getString("days")+"\t"+
resection.getString("startTime") +"-"+resection.getString("endTime")+"\t"+
resection.getString("room")+"\t "+resection.getString("instructor");

System.out.println(data1);

}
stmt.close();
conn.close();

}
public static Connection getConnection() throws SQLException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Could not load the driver");
}
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@tinman.cs.gsu.edu:1521:sid9ir2","cscxnsx","cscxnsx");
return conn;
}
}

class RegistarMainMenuHandler {
String sem = new String();
RegistarMainMenuHandler() throws SQLException, Exception{
initial();
}
void initial() throws SQLException, Exception{
char response;
do{
response = this.registarMainMenu();
switch(response){
case '1': this.loadSection(); break;
case '2': this.loadGrade(); break;
case '3': this.increase() ; break;
case '4': this.term(); break;
case '5': this.sTranscript(); break;
case '6': this.sScheFee(); break;
}
}while (response != '7');
System.out.println();
System.out.println("you have logged out\n\n");
int select = -1;
do{
System.out.print("please press Enter to go back to main menu");
try{
select = System.in.read();
}catch(Exception e){e.printStackTrace();}
}while (select != '\n');
}
char registarMainMenu(){
GoLunar registar = new GoLunar();
String name = registar.userName;
sem = registar.semester;
String response = new String();
System.out.println("**********************************************************************");
System.out.println("*** ***");
System.out.println("*** Welcome to the GoLunar - Online Registration System ***");
System.out.println("*** "+ name+" - registar ***");
System.out.println("*** ***");
System.out.println("**********************************************************************");

SimpleIO.prompt(" 1. Load Sections from File\n\n"+
" 2. Load Grades from File\n\n"+
" 3. Increase Section Cap\n\n"+
" 4. Display Term Schedule\n\n"+
" 5. Display Student Transcript\n\n"+
" 6. Display Student Schedule and Fee Detail\n\n"+
" 7. Quit\n your option?");

response = SimpleIO.readLine().toLowerCase();
return response.charAt(0);
}
void loadSection() throws SQLException, Exception, IOException{

Connection conn = getConnection();
Statement stmt = conn.createStatement();
FileReader fr;
BufferedReader inFile = null;
StringBuffer query, s;
String str;
String term = new String();
String year = new String();
query = new StringBuffer("");
String fileName = new String();
SimpleIO.prompt("File Name: ");
fileName = SimpleIO.readLine();
try{ fr = new FileReader(fileName);
inFile = new BufferedReader(fr);
}catch(Exception e){
System.out.println("Error opening file");
System.exit(1);
}
term = inFile.readLine();
year = inFile.readLine();
while((str = inFile.readLine()) != null) {
query.append("insert into sections values" +"('"+term+"','"+year);
s = new StringBuffer(str);
for(int i=0; i<10; i++){
query.append("','"+s.substring(0, s.toString().indexOf(",")));
s.delete(0, s.toString().indexOf(",")+1);
}
query.append("','"+s.toString()+"')");
stmt.executeUpdate(query.toString());
query.replace(0, query.length(), "");
}
stmt.close();
conn.close();
System.out.println("Sections Loaded\n\n");
}
void loadGrade() throws SQLException, Exception{
Connection conn = getConnection();
Statement stmt = conn.createStatement();
FileReader fr;
BufferedReader inFile = null;
StringBuffer query, s;
String str, sid, crn, grade;
String term = new String();
String year = new String();
query = new StringBuffer("");
String fileName = new String();
SimpleIO.prompt("File Name: ");
fileName = SimpleIO.readLine();
try{ fr = new FileReader(fileName);
inFile = new BufferedReader(fr);
}catch(Exception e){
System.out.println("Error opening file");
System.exit(1);
}
term = inFile.readLine();
year = inFile.readLine();
while((str = inFile.readLine()) != null) {
query.append("update enrolls set grade = '");
sid = str.substring(0,4);
crn = str.substring(5, 10);
grade = str.substring(11,12);
s = new StringBuffer(str);
query.append(grade + "' where sid = "+sid+" and term = '"+term+"' and year ="+
year + " and crn="+crn);
stmt.executeQuery(query.toString());
query.replace(0, query.length(), "");
}
stmt.close();
conn.close();
System.out.println("grades Loaded\n\n");


}
void increase() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
SimpleIO.prompt("crn: ");
String crn = SimpleIO.readLine().trim();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q1= "select cap from sections where crn = '"+crn+"'";

int newCap = 0;
int oldCap = 0;
try{
resection = stmt.executeQuery(q1);
resection.next();
oldCap = Integer.parseInt((resection.getString("cap")).trim());
}catch(SQLException e){ e.printStackTrace(); }

SimpleIO.prompt("Old Capacity is "+oldCap+"\n"+ "New Capacity: ");
newCap = Integer.parseInt(SimpleIO.readLine().trim());
String q = "update sections "+"set cap = "+newCap+ " where crn = '"+crn+"'";
resection = null;

try{
System.out.println("Cap Updated for CRN "+crn+".\n\n");
resection = stmt.executeQuery(q);
}catch(SQLException e){ e.printStackTrace();}

stmt.close();
conn.close();


}
void term() throws SQLException, Exception{
Connection conn = getConnection();
Statement stmt = conn.createStatement();

String term = sem.substring(0,2);
String year = sem.substring(2);
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;

int crn = 0;
try{
resection = stmt.executeQuery("select * from sections where term = '" + term +
"' and year = '"+year+"'");
resection.next();
crn= Integer.parseInt((resection.getString("crn")).trim());
}catch(SQLException e){ e.printStackTrace(); }

String q = "select count(sid) from enrolls where crn = " + crn +"and term = '" +
term+"'and year = "+ year;
resection = null;
String num;
int cur;
try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

rset.next();
num = rset.getString(1).trim();
cur = Integer.parseInt(num);
rset=null;

try{
resection = stmt.executeQuery("select * from sections where term = '" + term +
"' and year = '"+year+"'");
System.out.println("CRN Course Sec Days\tTime\t\tRoom\tCap\tCur\tAvail\tInstructor\tAuth");
System.out.println("-------------------------------------------------------------------------------------");
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
crn = Integer.parseInt((resection.getString("crn")).trim());

String cprefix = resection.getString("cprefix");
String cno = resection.getString("cno").trim();
String section = resection.getString("section").trim();
String days = resection.getString("days").trim();
String startTime = resection.getString("startTime").trim();
String endTime = resection.getString("endTime").trim();
String room = resection.getString("room").trim();
int cap = Integer.parseInt((resection.getString("cap")).trim());
String instructor = resection.getString("instructor").trim();
String auth = resection.getString("auth").trim();

data1 = crn +" "+cprefix+" "+cno+" "+section+" "+days+"\t"+startTime+"-"+endTime+"\t"+room
+"\t"+cap+"\t"+cur+"\t"+(cap-cur)+"\t "+instructor+"\t\t"+auth;

System.out.println(data1);

}

stmt.close();
conn.close();

}
void sTranscript() throws SQLException, Exception{
SimpleIO.prompt("student ID ");
String id = SimpleIO.readLine();
String data1 = new String();
int totalHour = 0;
int semHour = 0;
double Gpa = 0.00;
double courseGrade =0.00;
char lettGrade;
int hour = 4;
double semesterGpa = 0.00;
int earnedHour = 0;
String[][] semester = new String[20][20];
String[][] transcript = new String[20][20];
int numOfSem = 0;

Connection conn = getConnection();
Statement stmt = conn.createStatement();
String query1 = "select term, year from enrolls where sid = '"+id+"' group by term, year order by year, "+
"decode(term, 'SP',1,'SU',2,'FA',3)";
String query = "select s.term, s.year, s.cprefix, c.cno, s.crn, c.ctitle, c.chours, e.grade "+
"from enrolls e, sections s, courses c where e.sid = '"+id+"' and e.crn = s.crn and s.cno=c.cno";

ResultSet rset = null;
try{
rset = stmt.executeQuery(query1);
}catch(SQLException e1){
while(e1 !=null){
System.out.println("Message: "+e1.getMessage());
e1 = e1.getNextException();
}
}
if(!rset.next()){
System.out.println("unknown student");
return;
}
do{
semester[numOfSem][0] = rset.getString(1).trim();
semester[numOfSem][1] = rset.getString(2).trim();
numOfSem++;
}while(rset.next());
rset = null;
try{
rset = stmt.executeQuery(query);
}catch(SQLException e2){
while(e2!=null){
System.out.println("Message: "+e2.getMessage());
}
System.exit(0);
}
String G = "FDCBA";
int index = 0;
double [] credit = new double[20];
int grade1= 0;
while(rset.next()){
grade1=G.indexOf(rset.getString(8).trim().toUpperCase());

if(grade1 != -1){
transcript[index][0] = rset.getString(1).trim();
transcript[index][1] = rset.getString(2).trim();
transcript[index][2] = rset.getString(3).trim();
transcript[index][3] = rset.getString(4).trim();
transcript[index][4] = rset.getString(5).trim();
transcript[index][5] = rset.getString(6).trim();
transcript[index][6] = rset.getString(7).trim();
transcript[index][7] = rset.getString(8).trim();
credit[index] = Double.parseDouble(transcript[index][6])*grade1;
index++;
}
}

for(int u=0; u<numOfSem; u++){
double semPoint = 0.0;
double seHour = 0.0;
if(semester[u][0].equals("SU")){
System.out.println("Summer "+ semester[u][1]);
}
else if(semester[u][0].equals("SP")){
System.out.println("Spring "+semester[u][1]);
}
else if(semester[u][0].equals("FA")){
System.out.println("Fall " + semester[u][1]);
}
for(int j=0; j<index; j++){
if(transcript[j][0].equals(semester[u][0])&&transcript[j][1].equals(semester[u][1])){
System.out.print(" "+transcript[j][2]);
for(int k=0; k<5-transcript[j][2].length(); k++)
System.out.print(" ");
System.out.print(transcript[j][3]+" "+transcript[j][4]+" "+transcript[j][5]);
for(int k=0; k<45-transcript[j][5].length(); k++)
System.out.print(" ");
System.out.print(transcript[j][6]+" "+transcript[j][7]);
for(int k=0; k<2-transcript[j][7].length(); k++)
System.out.print(" ");
semPoint = semPoint + credit[j];
seHour = seHour + Double.parseDouble(transcript[j][6]);
courseGrade += credit[j];
earnedHour += Double.parseDouble(transcript[j][6]);
System.out.println();
}
}
System.out.println(courseGrade + " "+earnedHour+" "+semPoint+" "+seHour);
Gpa = Math.round((courseGrade/earnedHour)*100);
semesterGpa = Math.round((semPoint/seHour)*100);
System.out.println("Semester GPA: " + semesterGpa/100 + " GPA: "+Gpa/100);
}

stmt.close();
conn.close();


}
void sScheFee() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
SimpleIO.prompt("\nterm: ");
String sem = SimpleIO.readLine();
String term = sem.substring(0,2).toUpperCase();
String year = sem.substring(2);
SimpleIO.prompt("student ID: ");
String id = SimpleIO.readLine();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q = "select e.crn, s.cprefix, s.cno, c.ctitle, s.startTime, s.endTime, s.days, s.room, "+
"s.instructor from enrolls e, sections s, courses c "+
"where e.sid = '"+ id +"' and s.cprefix = c.cprefix and s.cno = c.cno "+
"and e.crn = s.crn and s.term = '"+term+"' and s.year = '"+year+"' ";


int crn = 0;
try{
resection = stmt.executeQuery(q);
System.out.println("CRN\tCourse\t\tTitle\t\t\t\t\tDays\tTime\t\tRoom Instructor");
System.out.println("-------------------------------------------------------------------------------");
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
data1 = resection.getString("crn")+"\t"+resection.getString("cprefix")+
resection.getString("cno")+"\t"+ resection.getString("ctitle")+"\t"+
resection.getString("days")+"\t"+
resection.getString("startTime") +"-"+resection.getString("endTime")+"\t"+
resection.getString("room")+"\t "+resection.getString("instructor");

System.out.println(data1);

}

resection = null;

String inSt = new String();
String stat = new String();
String term1 = new String();
if (term.equals("FA")){
term1 = "Fall";}
else if (term.equals("SP")){
term1 = "Spring";}
else if (term.equals("SU")){
term1 = "Summer";}

System.out.println("\n"+term1 + " " + year);

String query = "select s.inState from students s where s.sid = '"+id+"'";

try{
resection = stmt.executeQuery(query);
resection.next();
inSt = resection.getString("inState").trim();
}catch(SQLException e){ e.printStackTrace(); }

if(inSt.equalsIgnoreCase("Y"))
stat="INSTATE";
else if(inSt.equalsIgnoreCase("N"))
stat = "OUTOFSTATE";

q = "SELECT F.FEE FROM STUDENTS S, VARIABLEFEERATE F WHERE S.SID = '"+id+"'"+
"AND S.STYPE=F.STYPE AND F.INOROUTOFSTATE = '"+stat+"'";

resection = null;

try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

rset.next();
int fee = Integer.parseInt(rset.getString("fee"));

rset=null;
query = "select c.chours "+
"from courses c, sections s, enrolls e "+
"where e.sid = "+id+" and s.term = '"+term+"' and s.year = '"+year+"'"+
"and e.crn = s.crn and c.cprefix = s.cprefix and c.cno = s.cno" ;
int totalHours = 0;
try{
resection = stmt.executeQuery(query);
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
totalHours +=Integer.parseInt(resection.getString("chours").trim());}
resection = null;
double tuition = Math.round((totalHours*fee)*100)/100;
double otherFee=0.00;
query = "select * from fixedfee";

System.out.println("\n");
System.out.println("Tuition - "+stat+"\n"+
totalHours + " hours\t\t"+tuition+"\n");

try{
resection = stmt.executeQuery(query);
}catch(SQLException e){ e.printStackTrace(); }

while(resection.next()){
System.out.println(resection.getString("feeName")+":\t\t"+
Math.round(Integer.parseInt((resection.getString("fee")).trim())*100)/100);
otherFee += Math.round((Integer.parseInt((resection.getString("fee")).trim()))*100)/100;
}
resection = null;
System.out.println("\t\t\t---------\n"+
"\t\t\t"+(tuition+otherFee)+"\n"+
"\t\t\t---------\n");

stmt.close();
conn.close();

}
public static Connection getConnection() throws SQLException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Could not load the driver");
}
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@tinman.cs.gsu.edu:1521:sid9ir2","cscxnsx","cscxnsx");
return conn;
}
}

class StuffMainMenuHandler {
GoLunar staff = new GoLunar();
StuffMainMenuHandler() throws SQLException, Exception{
initial();
}
void initial() throws SQLException, Exception{
char response;
do{
response = this.stuffMainMenu();
switch(response){
case '1': this.authorize(); break;
case '2': this.overflow(); break;
case '3': this.addAssit(); break;
case '4': this.classList(); break;
}
}while (response != '5');
System.out.println();
System.out.println("you have logged out\n\n");
int select = -1;
do{
System.out.print("please press Enter to go back to main menu");
try{
select = System.in.read();
}catch(Exception e){e.printStackTrace();}
}while (select != '\n');
}
char stuffMainMenu(){
String name = staff.userName;
String response = new String();
System.out.println("**********************************************************************");
System.out.println("*** ***");
System.out.println("*** Welcome to the GoLunar - Online Registration System ***");
System.out.println("*** "+name+" - Department Staff ***");
System.out.println("*** ***");
System.out.println("**********************************************************************");

SimpleIO.prompt(" 1. Authorize Student into Section \n"+
" 2. Overflow Student into Section\n"+
" 3. Add Assistantship on System\n"+
" 4. Generate Class List\n"+
" 5. Quit\n\n"+"ur option: \n");
response = SimpleIO.readLine().toLowerCase();
return response.charAt(0);
}
void authorize() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
String sem = staff.semester;
String term = sem.substring(0,2).trim();
String year = sem.substring(2).trim();

SimpleIO.prompt("CRN: ");
String crn = SimpleIO.readLine();
SimpleIO.prompt("SID: ");
String sid = SimpleIO.readLine().trim();
String auth = new String();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;

try{
resection = stmt.executeQuery("select auth, cno, cprefix from sections where crn = "+crn);
resection.next();
auth = (resection.getString("auth")).trim();
}catch(SQLException e){ e.printStackTrace(); }

resection = null;
String q = "insert into enrolls values ("+sid+",'"+term+"',"+year+","+crn+", null)";

if(auth.equals("N"))
System.out.println("No need to authorize - This section does not need authorization.");
else{

try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

String cprefix = new String();
String cno = new String();
String fname = new String();
String lname = new String();
while(resection.next()){
cprefix = rset.getString("cprefix").trim();
cno = rset.getString("cno").trim();
}
rset=null;
try{
resection = stmt.executeQuery("select fname, lname from students where sid = "+sid);
}catch (Exception e) {
e.printStackTrace();}
resection.next();
fname = resection.getString("fname");
lname = resection.getString("lname");
System.out.println(" Student "+fname+" "+lname+" authorized into CRN "+crn+", "+cprefix+" "+cno+".");
}
stmt.close();
conn.close();

}
void overflow() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
String sem = staff.semester;
String term = sem.substring(0,2).trim();
String year = sem.substring(2).trim();

SimpleIO.prompt("CRN: ");
String crn = SimpleIO.readLine();
SimpleIO.prompt("SID: ");
String sid = SimpleIO.readLine().trim();
String auth = new String();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q = "select count(sid) from enrolls where crn = " + crn +"and term = '" +
term+"'and year = "+ year;
String num;
int cur;
try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

rset.next();
num = rset.getString(1).trim();
cur = Integer.parseInt(num);
rset=null;


int avai=0;
try{
resection = stmt.executeQuery("select cap from sections where crn = "+crn);
resection.next();
avai = Integer.parseInt(resection.getString("cap").trim())-cur;
}catch(SQLException e){ e.printStackTrace(); }
resection = null;
q = "insert into enrolls values ("+sid+",'"+term+"',"+year+","+crn+", null)";

if(avai>0)
System.out.println("No need to overflow - Space still available in this section.");
else{

try {
rset = stmt.executeQuery(q);
} catch (Exception e) {
e.printStackTrace();}

String cprefix = new String();
String cno = new String();
String fname = new String();
String lname = new String();
while(resection.next()){
cprefix = rset.getString("cprefix").trim();
cno = rset.getString("cno").trim();
}
rset=null;
try{
resection = stmt.executeQuery("select fname, lname from students where sid = "+sid);
}catch (Exception e) {
e.printStackTrace();}
resection.next();
fname = resection.getString("fname");
lname = resection.getString("lname");
System.out.println(" Student "+fname+" "+lname+" overflowed into CRN "+crn+", "+cprefix+" "+cno+".");
}
stmt.close();
conn.close();

}
void addAssit() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
String sem = staff.semester;
String term = sem.substring(0,2).trim();
String year = sem.substring(2).trim();

SimpleIO.prompt("SID: ");
String sid = SimpleIO.readLine().trim();
String gassi = new String();
String sType = new String();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q ="select fname, lname, sType, gradAssistant from students where sid = "+sid;
String lname= new String();
String fname = new String();
try{
resection = stmt.executeQuery(q);
resection.next();
gassi = (resection.getString("gradAssistant")).trim();
sType = (resection.getString("sType")).trim();
fname = resection.getString("fname");
lname = resection.getString("lname");
}catch(SQLException e){ e.printStackTrace(); }

resection = null;
q = " update students set gradAssistant = 'Y' where sid = "+sid;

if(sType.equalsIgnoreCase("UGRAD"))
System.out.println("this student can not be an assitant");
else {
try {
rset = stmt.executeQuery(q);
System.out.println(fname+ " " +lname+" ("+sid+") has been added to the Assistantship List.");
} catch (Exception e) { e.printStackTrace();}
}
stmt.close();
conn.close();

}
void classList() throws SQLException, Exception {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
SimpleIO.prompt("CRN: ");
String crn = SimpleIO.readLine().trim();
ResultSet resection = null;
String data1 = new String();
ResultSet rset = null;
String q = "select e.sid, s.cprefix, s.cno, t.fname, t.lname, c.ctitle, "+
"s.term, s.year, s.instructor "+
"from enrolls e, sections s, courses c, students t "+
"where e.sid = t.sid and e.crn = '"+crn+"' and s.cprefix = c.cprefix "+
"and s.cno = c.cno and e.crn = s.crn ";

try{
resection = stmt.executeQuery(q);
}catch(SQLException e){ e.printStackTrace(); }
resection.next();
String cprefix = resection.getString("cprefix").trim();
String cno = resection.getString("cno").trim();
String term = resection.getString("term").trim();
String year = resection.getString("year").trim();
String title = resection.getString("ctitle").trim();
String instructor = resection.getString("instructor").trim();
System.out.println(cprefix + " " + cno + ", "+title);
System.out.println(term + " " +year);
System.out.println("Instructor: "+instructor);
System.out.println("SID LNAME FNAME");
System.out.println("-----------------------");
//System.out.println(resection.getString("sid").length());
String hold_pref, hold_cno, hold_term, hold_year;
if(resection.next()){
while(resection.next()){
//for(int i=0; i<(resection.getString("sid").length()); i++){
data1 = resection.getString("sid")+ "\t"+resection.getString("lname")+"\t"+resection.getString("fname");
System.out.println(data1);}

}
else{System.out.println("No students enrolls in the class");}
stmt.close();
conn.close();

}
public static Connection getConnection() throws SQLException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Could not load the driver");
}
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@tinman.cs.gsu.edu:1521:sid9ir2","cscxnsx","cscxnsx");
return conn;
}
}