package trainticketprj;
import java.sql.*;
import java.util.Vector;


public class DBHelper {
    public DBHelper() {
    }

    public Connection getConn(){
        Connection  conn = null;
        try
        {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=train","sa","123456");
        } catch (Exception ex) {
            System.out.println("連接失敗 "+ex.getMessage());
        }
        return conn;


    }

    //根據車次查出這個車次的 票的所有 信息 返回一個 集合 (Vector)
    public Vector getTicketByTrainno(String trainno){
        Vector v = new Vector();
        Connection conn = this.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement("select * from trainticket where trainno = ?");
            ps.setString(1,trainno);

            rs = ps.executeQuery();
            if(rs.next())
            {
                v.add(rs.getString(2));
                v.add(rs.getString(3));
                v.add(rs.getString(4));
                v.add(rs.getString(5));
            }

        } catch (Exception ex) {
            System.out.println("查詢失敗"+ex.getMessage());
        } finally {

        }
        return v;

    }
    //跟新硬座的 票數
    public boolean updateTicket(String trainno,int ticketnum){
        boolean  f = false;
        Connection conn = this.getConn();
     PreparedStatement ps = null;
     ResultSet rs = null;
     try {

         ps = conn.prepareStatement("update trainTicket  set seatnum=seatnum-? where trainno=?");
         ps.setInt(1,ticketnum);
         ps.setString(2,trainno);
         int i = ps.executeUpdate();
         if(i>0){
             f = true;
         }

     } catch (Exception ex) {
         System.out.println("更新失敗"+ex.getMessage());
     } finally {

     }
     return f;


    }


    //跟新硬臥的 票數
public boolean updateTicketbed(String trainno,int ticketnum){
    boolean  f = false;
    Connection conn = this.getConn();
 PreparedStatement ps = null;
 ResultSet rs = null;
 try {

     ps = conn.prepareStatement("update trainTicket  set seatnum=seatnum-? where trainno=?");
     ps.setInt(1,ticketnum);
     ps.setString(2,trainno);
     int i = ps.executeUpdate();
     if(i>0){
         f = true;
     }

 } catch (Exception ex) {
     System.out.println("更新失敗"+ex.getMessage());
 } finally {

 }
 return f;


}











}