package cg;

import java.util.*;
import java.sql.*;

public class odbc
{
    private Connection con;
    
    public odbc()
    {
        con = getConnection();
    }
    
    public Connection getConnection()
    {
        Connection conn = null;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:myOdbc", "sa", "");
        }
        catch (ClassNotFoundException cnfe)
        {
            cnfe.printStackTrace();
            System.out.println ("出錯");
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            System.out.println ("出錯");
        }
        
        return (conn);
    }
    
    public Vector<String> getColumnNamesByTabname()
    {
        Vector<String> cols = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "SELECT * FROM dingfangxinxi WHERE 1=2";
            ResultSet rs = sta.executeQuery(strSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            for (int i = 1; i <= colCount; i++)
            {
                cols.add(rsmd.getColumnName(i));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (cols);
    }
    
    public Vector<String> getColumnNamesByTabname1()
    {
        Vector<String> cols = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "SELECT 客房編號,客房種類,客房位置,是否入住 FROM footinfo WHERE 是否入住=1";
            ResultSet rs = sta.executeQuery(strSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            for (int i = 1; i <= colCount; i++)
            {
                cols.add(rsmd.getColumnName(i));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (cols);
    }
    
    public Vector<String> getColumnNamesByTabname2()
    {
        Vector<String> cols = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "SELECT 客房編號,客房種類,客房位置,是否入住 FROM footinfo WHERE 是否入住=0";
            ResultSet rs = sta.executeQuery(strSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            for (int i = 1; i <= colCount; i++)
            {
                cols.add(rsmd.getColumnName(i));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (cols);
    }
    
    public Vector<Object> getDataByTabname()
    {
        Vector<Object> data = new Vector<Object>();
        try
        {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
            String strSql = "SELECT * FROM dingfangxinxi";
            ResultSet rs = sta.executeQuery(strSql);
            int cols = rs.getMetaData().getColumnCount();
            while (rs.next())
            {
                Vector<Object> vec = new Vector<Object>();
                for (int i = 1; i <= cols; i++)
                {
                    vec.add(rs.getObject(i));
                }
                data.add(vec);
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (data);
    }
    
    public Vector<Object> getDataByTabname1()
    {
        Vector<Object> data = new Vector<Object>();
        try
        {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
            String strSql = "SELECT 客房編號,客房種類,客房位置,是否入住 FROM footinfo WHERE 是否入住=1";
            ResultSet rs = sta.executeQuery(strSql);
            int cols = rs.getMetaData().getColumnCount();
            while (rs.next())
            {
                Vector<Object> vec = new Vector<Object>();
                for (int i = 1; i <= cols; i++)
                {
                    vec.add(rs.getObject(i));
                }
                data.add(vec);
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (data);
    }
    
    public Vector<Object> getDataByTabname2()
    {
        Vector<Object> data = new Vector<Object>();
        try
        {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
            String strSql = "SELECT 客房編號,客房種類,客房位置,是否入住 FROM footinfo WHERE 是否入住=0";
            ResultSet rs = sta.executeQuery(strSql);
            int cols = rs.getMetaData().getColumnCount();
            while (rs.next())
            {
                Vector<Object> vec = new Vector<Object>();
                for (int i = 1; i <= cols; i++)
                {
                    vec.add(rs.getObject(i));
                }
                data.add(vec);
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (data);
    }
    
    
    public Vector<String> getAllTabelName()
    {
        Vector<String> vec = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "SELECT [name] AS tab_name FROM sysobjects WHERE xtype='U' ORDER BY [name]";
            ResultSet rs = sta.executeQuery(strSql);
            while (rs.next())
            {
                vec.add(rs.getString("tab_name"));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (vec);
    }
    
    
    public Vector<String> getAllTabelName1()
    {
        Vector<String> vec = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "select 客房編號 from footinfo";
            ResultSet rs = sta.executeQuery(strSql);
            while (rs.next())
            {
                vec.add(rs.getString("客房編號"));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (vec);
    }
    
    
        
    public Vector<String> getAllTabelName2()
    {
        Vector<String> vec = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "select 客房編號 from footinfo where 是否入住=1";
            ResultSet rs = sta.executeQuery(strSql);
            while (rs.next())
            {
                vec.add(rs.getString("客房編號"));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (vec);
    }
    
       public Vector<String> getAllTabelName3()    //帳單流水號
    {
        Vector<String> vec = new Vector<String>();
        try
        {
            Statement sta = con.createStatement();
            String strSql = "select 賬單流水號 from dingfangxinxi";
            ResultSet rs = sta.executeQuery(strSql);
            while (rs.next())
            {
                vec.add(rs.getString("賬單流水號"));
            }
            rs.close();
            sta.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        
        return (vec);
    }
    
    public void closeConnection()
    {
        try
        {
            con.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
    }
}