import java.sql.*; 
import javax.swing.*; 
//import javax.swing.event.*;
import java.awt.*; 
import java.awt.event.*; 
import java.util.*; 

public class VipQueryFrame extends JDialog 
{
	private Connection queryconnection; 
   	private Statement querystatement; 
   	private Statement commoditystatement;
   	
   
   	private ResultSet queryresultSet; 
   	private ResultSet commodityresultSet;
   
   	
   	private ResultSetMetaData rsMetaData;
   	private ResultSetMetaData commodityrsMetaData;
    
    //GUI變量定義
	private JFrame parent; 
	private JPanel labelPanel, tablePanel;//定義兩塊面板對象
	//labelPanel用來放標簽，fieldsPanel用來放文本區
	private String labels = "請輸入 VIP 號：";
	private JTable table;  
	private JTable commoditytable; 
	private JButton submitQuery;     
	private JButton cancelQuery;     
            JTextField VIP_ID;
	
    public VipQueryFrame(JFrame parent, boolean modal) 
    {
        super(parent, modal);
        this.parent = parent;
        pack();
        //GridBagLayout layout = new GridBagLayout();
        //Container contents = getContentPane();
        //GridBagConstraints constraints = new GridBagConstraints();
        
        //super( "輸入SQL語句，按提交按鈕查看結果。" ); 
    	//url中指定ODBC中設置的DSN名稱
    	String url = "jdbc:odbc:VipQuery"; 
    	String username = ""; 
    	String password = ""; 
    	//加載驅動程序以連接數據庫
      	try 
      	{ 
        	Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); 
        	queryconnection = DriverManager.getConnection( url, username, password ); 
      	} 
      	//捕獲加載驅動程序異常
      	catch ( ClassNotFoundException cnfex ) 
      	{ 
        	System.err.println("裝載 JDBC/ODBC 驅動程序失敗。" ); 
        	cnfex.printStackTrace(); 
         	System.exit( 1 );  // terminate program 
      	} 
      	//捕獲連接數據庫異常
      	catch ( SQLException sqlex ) 
      	{ 
         	System.err.println( "無法連接數據庫" ); 
         	sqlex.printStackTrace(); 
         	System.exit( 1 );  // terminate program 
      	} 
        
        
        
        submitQuery = new JButton( "查  詢" );
        submitQuery.setPreferredSize(new Dimension(120, 25));
    	submitQuery.addActionListener( 
         new ActionListener() 
         { 
            public void actionPerformed( ActionEvent e ) 
            { 
               	getTable();
            } 
         }   
        ); 
        
        cancelQuery = new JButton( "返  回" );
        cancelQuery.setPreferredSize(new Dimension(120, 25));
    	cancelQuery.addActionListener( 
         new ActionListener() 
         { 
            public void actionPerformed( ActionEvent e ) 
            { 
               	setVisible(false);
               	dispose();
            } 
         }   
        ); 
       
        GridBagConstraints constraints = new GridBagConstraints();
        
        VIP_ID = new JTextField();
        VIP_ID.setPreferredSize(new Dimension(120, 25));
              
        
    	//VIP_NAME = new JTextField();
    	//VIP_NAME.setPreferredSize(new Dimension(120, 25));
    	
    	labelPanel = new JPanel();
		labelPanel.setLayout( new GridBagLayout());
		
		constraints.weightx = 100;
        constraints.weighty = 100;
        constraints.gridx = 0;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(new JLabel( labels), constraints);
        
        
        constraints.gridx =1;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(VIP_ID, constraints);
        
        
       
        
      
        constraints.gridx =2;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(submitQuery, constraints);
        
    
        constraints.gridx =3;
        constraints.gridy = 0;
        constraints.gridwidth = 1;
        constraints.gridheight = 1;
        labelPanel.add(cancelQuery, constraints);
        
		//labelPanel.add( new JLabel( labels) );
		//labelPanel.add( VIP_ID );
		//labelPanel.add( VIP_NAME );
		//labelPanel.add( submitQuery );
		
		table = new JTable(); 
		commoditytable = new JTable();
		
		JScrollPane scroller = new JScrollPane(); 
		//scroller.setLayout( new BorderLayout() );
		scroller.setPreferredSize(new Dimension(100, 50));
        //scroller.setSize(800, 20);
        JScrollPane commodityscroller = new JScrollPane( commoditytable ); 
		
		tablePanel = new JPanel();
		tablePanel.setLayout( new BorderLayout() );
		tablePanel.setPreferredSize(new Dimension(100, 20));
		tablePanel.setBorder(BorderFactory.createEtchedBorder());
 		tablePanel.add( scroller ,BorderLayout.CENTER);
 		tablePanel.add( commodityscroller ,BorderLayout.SOUTH);
 		
 		Container c = getContentPane(); 
      	setTitle("VIP消費查詢窗口");
      	c.setLayout( new BorderLayout()); 
      	//將"topPanel"編輯框布置到 "NORTH"
      	c.add( labelPanel ,BorderLayout.NORTH);
      	c.add( tablePanel ,BorderLayout.CENTER);
      	//c.add( commoditytable ,BorderLayout.SOUTH);
      	getTable(); 
      	setSize( 500, 300 );
      	//setResizable(false);
      	setLocation(200,100);
      	//show(); 
    }
    
    private void getTable() 
   { 
      try { 
      	 //執行SQL語句
      	 String query;
      	 String commodityquery;
 
 
    	 if (VIP_ID.getText().equals( "" ) )
      	 {
         	 
         	 	query = "SELECT * FROM VIPID WHERE VIP編號=1"; 
         	 	commodityquery = "SELECT * FROM CONSUME WHERE VIP編號=1 ";
         	
         }
         else 
         {   	 
         	 query = "SELECT * FROM VIPID WHERE VIP編號="+VIP_ID.getText()+""; 
         	 commodityquery = "SELECT * FROM CONSUME WHERE VIP編號="+
         	 				  VIP_ID.getText()+"";
         }
         querystatement = queryconnection.createStatement(); 
         commoditystatement = queryconnection.createStatement(); 
         queryresultSet = querystatement.executeQuery( query );      
         commodityresultSet = commoditystatement.executeQuery( commodityquery );       
         //在表格中顯示查詢結果
         displayResultSet( queryresultSet,commodityresultSet);
      } 
      catch ( SQLException sqlex ) { 
         sqlex.printStackTrace(); 
      } 
   }  	
   
   private void displayResultSet( ResultSet rs ,ResultSet commodityrs ) 
      throws SQLException 
   { 
      //定位到達第一條記錄
      boolean moreRecords = rs.next();  
      boolean commoditymoreRecords = commodityrs.next();
      //如果沒有記錄，則提示一條消息
      if ( ! moreRecords ) 
      { 
         JOptionPane.showMessageDialog( this,"無此會員記錄" ); 
         //setTitle( "無記錄顯示" ); 
         return; 
      } 
      if ( ! commoditymoreRecords ) 
      { 
         JOptionPane.showMessageDialog( this,"無此會員購物記錄" ); 
         //setTitle( "無記錄顯示" ); 
        
      } 
      Vector columnHeads = new Vector(); 
      Vector rows = new Vector(); 
      Vector commoditycolumnHeads = new Vector(); 
      Vector commodityrows = new Vector(); 
      try
      {
      	 
         //獲取字段的名稱
         ResultSetMetaData rsmd = rs.getMetaData(); 
         for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         { 
            columnHeads.addElement( rsmd.getColumnName( i ) ); 
         }
          //獲取記錄集
         do 
         { 
            rows.addElement( getNextRow( rs, rsmd ) ); 
         } while ( rs.next() ); 
         
         ResultSetMetaData commodityrsmd = commodityrs.getMetaData(); 
        
         
         //在表格中顯示查詢結果
         table = new JTable( rows, columnHeads ); 
         
         JScrollPane scroller = new JScrollPane( table ); 
         scroller.setPreferredSize(new Dimension(500, 50));
                   
         Container c = getContentPane(); 
         c.remove(1); 
     	 tablePanel = new JPanel();
     	 tablePanel.setLayout( new BorderLayout() );
         tablePanel.add( scroller, BorderLayout.NORTH ); 
        if( commoditymoreRecords)
         {
         	for ( int i = 1; i <= commodityrsmd.getColumnCount(); ++i )
         	{ 
         	   commoditycolumnHeads.addElement( commodityrsmd.getColumnName( i ) ); 
         	}
         	 do 
         	{ 
           	 commodityrows.addElement( getNextRow( commodityrs, commodityrsmd ) ); 
         	} while ( commodityrs.next() ); 
         	
         	commoditytable = new JTable( commodityrows, commoditycolumnHeads );
         	JScrollPane commodityscroller = new JScrollPane( commoditytable );
         	commodityscroller.setPreferredSize(new Dimension(500, 200));
         	
         	tablePanel.add( commodityscroller, BorderLayout.CENTER );
         }
         else
         {
         	for ( int i = 1; i <= commodityrsmd.getColumnCount(); ++i )
         	{ 
         	   commoditycolumnHeads.addElement( commodityrsmd.getColumnName( i ) ); 
         	}
         	
         	commoditytable = new JTable(6,0);
         	JScrollPane commodityscroller = new JScrollPane();
         	commodityscroller.setPreferredSize(new Dimension(500, 200));
         	tablePanel.add( commodityscroller, BorderLayout.CENTER );
         }
     
         
         c.add(tablePanel,BorderLayout.CENTER);
         //刷新Table
         c.validate(); 
      } 
      catch ( SQLException sqlex )
	  { 
         sqlex.printStackTrace(); 
      } 
   } 
   
  
   
   private Vector getNextRow( ResultSet rs, 
                              ResultSetMetaData rsmd ) 
       throws SQLException 
   { 
      Vector currentRow = new Vector(); 
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) 
         currentRow.addElement( rs.getString( i ) ); 
      //返回一條記錄   
      return currentRow; 
   } 
}