/*
 * 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 server.jdbc.sinhvien;
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.net.ServerSocket;
import java.net.Socket;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 *
 * @author Phungg
 */
public class ServerJDBCSinhVien {

    /**
     * @param args the command line arguments
     */
    public static class LoginUser extends Thread
    {

        Socket s;
        LoginUser(Socket sv)
        {
            this.s=sv;
        }
        public void run()
        {
            Connection conn = null;
            DataInputStream receive;
            DataOutputStream send;
            try {
                receive = new DataInputStream(s.getInputStream());
                send = new DataOutputStream(s.getOutputStream());
                try
                {
                    String user = "sa";
                    String pass = "123456";
                    String url = "jdbc:sqlserver://localhost:1433;databaseName=QLSV;";
                    conn = DriverManager.getConnection(url,user,pass);
                    Statement stm;
                    ResultSet rs;
                    ResultSet rs1;
                    ResultSet rs2;
                    try
                    {
                        while(true)
                        {
                            String UserReceive = receive.readUTF();
                            String PasswordReceive = receive.readUTF();
                            stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                            rs = stm.executeQuery("select * from SinhVien where UserName = '"+UserReceive+"' and Password ='"+PasswordReceive+"'");
                            if(rs.next())
                            {
                                send.writeUTF("Menu: \n 1.Xem Diem \n 2.Nhap Diem \n 3.Thoat");
                                break;
                            }
                                else
                                    send.writeUTF("user hoac password khong dung!");
                        }
                        while(true)
                        {
                            send.writeUTF("nhap vao lua chon cua ban: ");
                            int choice = receive.readInt();
                            if(choice==1)
                            {
                                send.writeUTF("Nhap vao ma sinh vien: ");
                                String mssv = receive.readUTF();
                                stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                                rs1 = stm.executeQuery("select * from ThongTin where MaSV = '"+mssv+"'");
                                if(rs1.next())
                                {
                                    send.writeUTF(rs1.getString("MaSV")+" "+rs1.getString("HoTen")+" "+rs1.getString("DiemTB"));
                                }
                                    else
                                    send.writeUTF("Ma sinh vien khong ton tai!");
                            }
                            else
                                if(choice==2)
                                {
                                    send.writeUTF("Nhap vao ma sinh vien: ");
                                    String ms = receive.readUTF();
                                    send.writeUTF("Nhap vao ho va ten: ");
                                    String hoten = receive.readUTF();
                                    send.writeUTF("Nhap vao diem trung binh: ");
                                    String diem = receive.readUTF();
                                    stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                                    rs2 = stm.executeQuery("select MaSV from ThongTin where MaSV = '"+ms+"'");
                                    if(rs2.next())
                                    {
                                        send.writeUTF("ma sinh vien da ton tai. cap nhat that bai!");
                                    }
                                    else
                                    {
                                        send.writeUTF("cap nhat thanh cong");
                                        // them querry tai day !!!
                                    }
                                }
                        }
                        //rs.close();
                        //stm.close();
                    }catch(Exception e)
                        {
                            e.printStackTrace();
                        }
                }catch (SQLException ex)
                    {
                        ex.printStackTrace();
                    }
                finally
                    {
                    try {
                        if (conn != null && !conn.isClosed())
                        {
                            conn.close();
                        }
                        } catch (SQLException ex)
                            {
                                ex.printStackTrace();
                            }
                    }
                } catch (IOException ex)
                {
                    Logger.getLogger(ServerJDBCSinhVien.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }

    public static synchronized void main (String[] args) throws IOException{
        ServerSocket ss = new ServerSocket(1234);
        while(true)
        {
            Socket sv = ss.accept();
            LoginUser a = new LoginUser(sv);
            a.start();
        }
        // TODO code application logic here
    }

}