package restaurant;
import java.io.*;
import java.sql.*;

public class RestaurantMenu4 extends JFrame{
	Connection con = null;
	Statement stmt = null;
	ResultSet foodRs = null;
	ResultSet searchFoodRs = null;
	ResultSet hotFoodRs = null;
	
	ResultSet operatorRs = null;
	ResultSet clientRs = null;
	ResultSet roomRs = null;
	
	public void InitCheck( Connection con )
	{
		if ( this.con != con )
		{
			this.con = con;
		}
		
		try
		{
			stmt = con.createStatement();
			
			String strSQL1 = "select * from food";
			String strSQL2 = "select top 10 saleNumber * from food order by saleNumber desc"; //顯示前10名熱銷商品
            String strSQL3 = "select * from operator";
            String strSQL4 = "select * from client";
            String strSQL5 = "select * from room"; 
            
			foodRs = stmt.executeQuery( strSQL1 );
			hotFoodRs = stmt.executeQuery( strSQL2 );
			operatorRs = stmt.executeQuery( strSQL3 );
			clientRs = stmt.executeQuery( strSQL4 );
            roomRs = stmt.executeQuery( strSQL5 ); 
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
	}
	
	public boolean foodNext()
	{
		boolean result = false;
		
		try
		{
			result = foodRs.next();
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
		return result;
		
	} 
	
	public boolean hotFoodNext()
	{
		boolean result = false;
		
		try
		{
			result = hotFoodRs.next();
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
		return result;		
	} 
	
	public boolean operaterNext()
	{
		boolean result = false;
		
		try
		{
			result = operaterRs.next();
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
		return result;		
	} 

	public boolean clientNext()
	{
		boolean result = false;
		
		try
		{
			result = clientRs.next();
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
		return result;		
	} 
	
	public boolean roomNext()
	{
		boolean result = false;
		
		try
		{
			result = roomRs.next();
		}
		
		catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		
		return result;		
	}	
///////////////////////////////////////////////////////////
	public String searchFood(String searchFood){
		String sql = "select * from food where name='"+ searchFood +"'";
		try{
			searchFoodRs = stmt.executeQuery(sql); 
			searchFoodRs.next();
		}catch ( SQLException sex )
		{
			System.out.println( sex.toString() );
		}
		return searchFoodRs.getString("name");
	}
	
    public String getFoodName(){
		String result = null;
		try{
			result = foodRs.getString("name");
		}catch(SQLException id){
			System.out.println( id.toString() );
		}
		
		return result;
	}
	
	public int getExistAmount(){
		String sql="select exist from food";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("exist"));
		return count;
	}
	
	public int getSaleAmount(){
		String sql="select sale from food";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("sale"));
		return count;
	}
	    
	public int getExistFoodNumber(String name){
		String sql="select exist from food where name='"+name+"'";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("exist"));
		return count;
	}
	
	public int getSaleFoodNumber(String name){
		String sql="select sale from food where name='"+name+"'";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("sale"));
		return count;
	}
	
    public String getClientName(){
		String result = null;
		try{
			result = foodRs.getString("name");
		}catch(SQLException id){
			System.out.println( id.toString() );
		}
		
		return result;
	}

    public long getClientTypeResult(String clientType){
		String sql="select * from client where type='"+clientType+"'";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("result"));
		return count;    	
    }

    public long getClientAmount(){
		String sql="select pay from client";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("pay"));
		return count;    	
    }
    
    public long getOperatorResult(){
		String result = null;
		try{
			result = Integer.parseInt(foodRs.getString("result"));
		}catch(SQLException id){
			System.out.println( id.toString() );
		}
		
		return result;
	}
	
	public long getResultAmount(){
		String sql="select result from operator";
		int count=0;
		stmt = con.createStatement();
		ResultSet temp = null;
		temp=stmt.executeQuery( sql );
		
		while(temp.next())
		   count+=Integer.parseInt(temp.getString("result"));
		return count;
}