/*
 * 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 jdbc_manh;

import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author letro
 */
public class JDBC_server {

    /**
     * @param args the command line arguments
     */
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    static final String DB_URL = "jdbc:sqlserver://localhost:9090; databaseName=JDBC_SinhVien;";
    //  Database credentials
    static final String DB_USER = "sa";
    static final String DB_PASS = "lapTopvaio0305";
    static final int PORT=1337;
    static String menu= "MENU: \n1.Xem diem. \n2.Nhap diem \n3.Sua diem \n3.Ket thuc.";
    public static class ClientHandler extends Thread
    {
        Socket sock;
        DataInputStream rec;
        DataOutputStream send;
        public ClientHandler(Socket sock,DataOutputStream send,DataInputStream rec) {
            this.sock = sock;
            this.send = send;
            this.rec= rec;
        }

        @Override
        public void run() {
            super.run(); //To change body of generated methods, choose Tools | Templates.
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                //STEP 2: Register JDBC driver
                Class.forName(JDBC_DRIVER);
                //STEP 3: Open a connection
                System.out.println("Connecting to a selected database...");
                conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
                System.out.println("Connected database successfully...");
                //STEP 4: Start working with menu
                String username, password, sqlString;
                ResultSet rs = null;
                boolean check = false;
                while(check)
                {
                    username = rec.readUTF();
                    password = rec.readUTF();
                    sqlString= "SELECT username, passsword FROM TaiKhoan WHERE username=? and password =?;";
                    ps = conn.prepareStatement(sqlString);
                    ps.setString(1,username);
                    ps.setString(2, password);
                    rs = ps.executeQuery();
                    if(rs.isBeforeFirst())
                        check =true;
                    else
                        send.writeUTF("Tai Khoan/Mat Khau khong dung.");
                }
                send.writeUTF(menu);
                boolean tieptuc= true;
                while (tieptuc==true)
                {
                    int choose;
                    do
                    {
                        choose=rec.readInt();
                        if(choose<1||choose>4)
                            send.writeUTF("Nhap khong hop le. Nhap lai!!");
                    }while(choose<1||choose>4);
                    switch(choose)
                    {
                        case 1:
                            String mssv;
                            mssv = rec.readUTF();
                            sqlString ="SELECT * FROM ThongTin WHERE MaSV= ?;";
                            ps = conn.prepareStatement(sqlString);
                            ps.setString(1, mssv);
                            rs = ps.executeQuery();
                            if(!rs.isBeforeFirst())
                            {
                                send.writeUTF("Ma So Sinh Vien Khong Dung!!");
                            }
                            else
                            {
                                rs.next();
                                String xemDiem = "THONG TIN SINH VIEN: \n 1.MSSV: "+ rs.getString(1).trim()+
                                        "\n 2.Ho va ten: "+rs.getString(2).trim()+ "\n 3.Diem Trung Binh: "+ rs.getFloat(3);
                                send.writeUTF(xemDiem);
                            }
                            break;
                        case 2:
                            String maSV,kq="";
                            maSV = rec.readUTF();
                            sqlString = "SELECT * FROM ThongTin WHERE MaSV= ?;";
                            ps = conn.prepareStatement(sqlString);
                            ps.setString(1, maSV);
                            rs = ps.executeQuery();
                            if(!rs.isBeforeFirst())
                            {
                                String hoTen = rec.readUTF();
                                float diemTB = rec.readFloat();
                                String sqlStringInsert ="INSERT INTO ThongTin(MaSV,HoTen,DiemTb) VALUES(?,?,?);";
                                ps = conn.prepareStatement(sqlStringInsert);
                                ps.setString(1, maSV);
                                ps.setString(2, hoTen);
                                ps.setFloat(3, diemTB);
                                try {
                                    if (ps.executeUpdate()>0)
                                        kq= "Nhap Diem Thanh Cong!!";
                                    else
                                        kq ="Nhap Diem That Bai@@";
                                } catch (Exception e) {
                                    System.err.println("Loi SQL");
                                }
                            }
                            else
                            {
                                kq="MA SO SINH VIEN Da Co!!. Moi sang Sua Thong Tin!!!";
                            }
                            send.writeUTF(kq);
                            break;
                        case 3:
                            kq="";
                            maSV = rec.readUTF();
                            sqlString = "SELECT * FROM ThongTin WHERE MaSV= ?;";
                            ps = conn.prepareStatement(sqlString);
                            ps.setString(1, maSV);
                            rs = ps.executeQuery();
                            if(rs.isBeforeFirst())
                            {
                                String hoTen = rec.readUTF();
                                float diemTB = rec.readFloat();
                                String sqlStringInsert ="UPDATE ThongTin SET HoTen=?,DiemTB=? WHERE MaSV=?";
                                ps = conn.prepareStatement(sqlStringInsert);
                                ps.setString(1, hoTen);
                                ps.setFloat(2, diemTB);
                                ps.setString(3, maSV);
                                try {
                                    if (ps.executeUpdate()>0)
                                        kq= "Cap Nhat Thanh Cong!!";
                                    else
                                        kq ="Cap Nhat That Bai@@";
                                } catch (Exception e) {
                                    System.err.println("Loi SQL");
                                }
                            }
                            else
                            {
                                kq="Ma Sinh Vien Khong Dung.";
                            }
                            send.writeUTF(kq);
                            break;
                        case 4:
                            tieptuc= false;
                        default:
                            break;
                    }
                }
                //STEP 5: Clean-up environment
                if(rs!=null) rs.close();
                ps.close();
                conn.close();
            } catch (Exception e) {
                System.err.println("Loi database!!");
            }
            finally{
                try{
                    if(ps!=null) ps.close();
                    if(conn!=null && !conn.isClosed()) conn.close();
                }
                catch(Exception e){}
            }
        }

    }
    public static void main(String[] args) throws IOException {
        // TODO code application logic here
        ServerSocket ss = new ServerSocket(PORT);
        System.out.println("Server Dang mo PORT: "+ PORT);
        while(true)
        {
            Socket sv= ss.accept();
            System.out.println("Co 1 yeu cau ket noi!!!");
            DataInputStream rec= new DataInputStream(sv.getInputStream());
            DataOutputStream send = new DataOutputStream(sv.getOutputStream());
            ClientHandler a = new ClientHandler(sv,send,rec);
            a.start();
        }

    }

}