ggsir

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package enrollment;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author rickygwapo
 */
public class Database {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USERNAME = "DBProject";
    private static final String PASSWORD = "database";
    private static final String INSERTSTUDENT = "INSERT INTO STUDENT VALUES(";
    private static final String INSERTTEACHER = "INSERT INTO TEACHER VALUES(";
    private static final String INSERTUSER = "INSERT INTO USER_ACCOUNT VALUES(";
    private static final String STUDENTNUMSEQ = "studentNumSeq.nextval";
    private static final String TEACHERNUMSEQ = "teacherNumSeq.nextval";
    private static final String DELETEQUERY = " ";
    private static boolean loggedin = false;
    Connection con;


    public void connect(){
        try {
            con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void disconnect(){
        try {
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

//            Statement statement = con.createStatement();
//            ResultSet rs2 = statement.executeQuery("SELECT COUNT(*) AS total FROM EMPLOYEE");
//            rs2.next();
//            int countOfDB = rs2.getInt(1);
//            MaxUserArray = countOfDB;
//            ResultSet rs = statement.executeQuery("SELECT * FROM EMPLOYEE");
//            int ctr = 0;
//
//            while(rs.next()){
//                UserID[ctr] = rs.getInt("employeeid");
//                lastName[ctr] = rs.getString("lastname");
//                firstName[ctr] = rs.getString("firstname");
//                middleName[ctr] = rs.getString("middlename");
//                salary[ctr] = rs.getString("salary");
//                position[ctr] = rs.getString("position");
//                ctr++;
//            }


    public boolean enroll(String lastname, String firstname, String middlename, String birthday, String gender, String nationality
    , String street, String barangay, String city, String province, String zipcode , String gradeLevel, String guardianFName,
    String guardianLName, String guardianMName, String guardianPhoneNo, String section, String paymentMode){
        try {
            Statement statement = con.createStatement();
            PreparedStatement ps = null;
            System.out.println("worked");
            //String SQL = "insert into student (student_lastname,firstnam"
            ps = con.prepareStatement(INSERTSTUDENT+STUDENTNUMSEQ+",'"+lastname+"','"+firstname+"','"+middlename+"','"
            +birthday+"','"+gender+"','"+nationality+"','"+street+"','"+barangay+"','"+city+"','"+province+"',"+zipcode+",'"+gradeLevel+"','"+guardianFName
            +"','"+guardianLName+"','"+guardianMName+"',"+guardianPhoneNo+",'"+section+"','"+paymentMode+"')");
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }


    public int sectionSize(String section){
        try {
            Statement statement = con.createStatement();
            PreparedStatement ps = null;
            System.out.println("worked");
            //String SQL = "insert into student (student_lastname,firstnam"
            ResultSet rs = statement.executeQuery("SELECT COUNT(STUDENT_ID) FROM STUDENT WHERE SECTION_NAME = '"+section+"'");
            rs.next();
            int studentCount = rs.getInt(1);
            return studentCount;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;



    }

    public boolean addTeacher(String lastname, String firstname, String middlename, String birthday,
            String gender, String hiredate, String salary, String contactNo){

        try {
            Statement statement = con.createStatement();
            PreparedStatement ps = null;
            System.out.println("worked");
            //String SQL = "insert into student (student_lastname,firstnam"


            ps = con.prepareStatement(INSERTTEACHER+TEACHERNUMSEQ+",'"+lastname+"','"+firstname+"','"+middlename+"','"
            +birthday+"','"+gender+"','"+hiredate+"',"+salary+","+contactNo+")");
            ps.executeUpdate();
        } catch (SQLException ex) {

            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean checkAccount(String username, String password){
        try {
            Statement statement = con.createStatement();
            System.out.println("user "+username);
            System.out.println("pass "+password);
            //String SQL = "insert into student (student_lastname,firstnam"
            ResultSet rs = statement.executeQuery("SELECT * FROM USER_ACCOUNT WHERE USERNAME = '"+username+"' AND PASSWORD = '"+password+"'");

            if(rs.next()){
                return true;
            }else{
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean checkUsername(String username){
        try {
            Statement statement = con.createStatement();
            System.out.println("user "+username);
            //String SQL = "insert into student (student_lastname,firstnam"
            ResultSet rs = statement.executeQuery("SELECT * FROM USER_ACCOUNT WHERE USERNAME = '"+username+"'");

            if(rs.next()){
                return true;
            }else{
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean createUser(String username, String password){
        try {
            Statement statement = con.createStatement();
            PreparedStatement ps = null;
            System.out.println("worked");
            //String SQL = "insert into student (student_lastname,firstnam"


            ps = con.prepareStatement(INSERTUSER+"'"+username+"','"+password+"')");
            ps.executeUpdate();
        } catch (SQLException ex) {

            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public void login(){
        loggedin = true;
    }

    public boolean loginStatus(){
        return loggedin;

    }
    // day-month-year







}