package flight;

import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

public class DBConnect {
    private Connection conn = null;

    public DBConnect() {
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:flight");
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    public boolean isConnected() {
        boolean flag = false;
        if(conn != null) {
            flag = true;
        }
        return flag;
    }

    public void disconnect() {
        try {
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    public Vector getCityName(String cityType) {
        Vector cityName = new Vector();
        try {
            Statement stmt = conn.createStatement();
            String strsql = "select distinct " + cityType + " from flight where state = 1";
            ResultSet rs = stmt.executeQuery(strsql);
            while(rs.next()) {
                cityName.add(rs.getString(1));
            }
            rs.close();
            stmt.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
        return cityName;
    }

    public void showFlightTable(String bCity,String eCity,JTable jTable) {
        Statement stmt = null;
        ResultSet rs = null;
        Vector data = new Vector();
        Vector row;
        Vector column = new Vector();
        DefaultTableModel model;
        String strsql = "select * from view_flight where beginCity = '"+bCity+"' and endCity = '"+eCity+"'";

        column.add("航班編號");
        column.add("飛機編號");
        column.add("起飛城市");
        column.add("終點城市");
        column.add("總載客量");
        column.add("所剩機票數");
        column.add("票價");
        column.add("起飛時間");
        column.add("到達時間");

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(strsql);
            while(rs.next()) {
                row = new Vector();
                row.add(rs.getInt(1));
                row.add(rs.getString(2));
                row.add(rs.getString(3));
                row.add(rs.getString(4));
                row.add(rs.getInt(5));
                row.add(rs.getInt(6));
                row.add(rs.getFloat(7));
                row.add(rs.getString(8));
                row.add(rs.getString(9));
                data.add(row);
            }
            rs.close();
            stmt.close();
        }catch(SQLException se) {
            se.printStackTrace();
        }

        model = new DefaultTableModel(data,column);
        jTable.setModel(model);
    }

    public int getTicketID(int flightID) {
        Statement stmt = null;
        ResultSet rs = null;
        String strsql = "select TID from flight where FID = " + flightID;
        int ticketID = 0;

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(strsql);
            while(rs.next()) {
                ticketID = rs.getInt(1);
            }
            rs.close();
            stmt.close();
        }catch(SQLException se) {
            se.printStackTrace();
        }

        return ticketID;
    }

    public boolean buyTicket(int ticketID,int num,String identify) {
        Statement stmt = null;
        String strsql = "insert into buy (buyTID,buyQuantity,identifyID) values ("+ticketID+","+num+",'"+identify+"')";
        int rowCount = 0;
        try{
            stmt = conn.createStatement();
            rowCount = stmt.executeUpdate(strsql);
        }catch(SQLException se) {
            se.printStackTrace();
        }

        if(rowCount != 0) {
            return true;
        }else {
            return false;
        }
    }

    public boolean hasThisTicketID(String ticketID) {
        boolean flag = false;

        if(ticketID.equals("")) {
            flag = false;
        }
        if(CheckFlightDlg.strCheck(ticketID)){
            Statement stmt = null;
            ResultSet rs = null;
            String strsql = "select * from ticket where ticketID = "+ticketID;

            try{
                stmt = conn.createStatement();
                rs = stmt.executeQuery(strsql);

                if(rs.next()) {
                    flag = true;
                }

                rs.close();
                stmt.close();
            }
            catch(SQLException se) {
                se.printStackTrace();
            }
        }

        return flag;
    }

    public boolean login(String name,String pwd) {
        boolean flag = false;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strsql = "select * from manager where managerName = ? and managerPwd = ?";

        try{
            stmt = conn.prepareStatement(strsql);
            stmt.setString(1,name);
            stmt.setString(2,pwd);
            rs = stmt.executeQuery();
            if(rs.next()) {
                flag = true;
            }
        }catch(SQLException se) {
            se.printStackTrace();
        }

        return flag;
    }
}