/*
 * Singer.java
 *
 * Created on 2008年6月9日, 下午3:17
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package serverBag;

import java.sql.*;

import publicBag.*;

/**
 *
 * @author Administrator
 */
public class Singer implements UpdateDB{
    private Connection con;
    //JavaBean屬性
    private int singerID;
    private String singerName;
    private String singerSex;
    private String region;
    private int voteCount;
    private String photo;
    
    public int getSingerID(){ return singerID;}
    public void setSingerID(int inSingerID){ singerID = inSingerID;}
    public String getSingerSex(){ return singerSex;}
    public void setSingerSex(String inSingerSex){ singerSex = inSingerSex;} 
    public String getRegion(){ return region;}
    public void setRegion(String inRegion){ region = inRegion;}
    public String getSingerName(){ return singerName;}
    public void setSingerName(String inSingerName){ singerName = inSingerName;}
    public int getVoteCount(){return voteCount;}
    public void setVoteCount(int inVoteCount){voteCount = inVoteCount;}
    public String getPhoto(){return photo;}
    public void setPhoto(String inPhoto){photo = inPhoto;}    
    
    /** Creates a new instance of UserInfo */
    public Singer() {
        try
        {
            con = ConnectDB.connectMusicDB();            	
        }
        catch(Exception e)
        {
            e.printStackTrace();           
        }
    }
    public ResultSet showAllInfo()
    {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("SELECT * FROM Singer");
            ResultSet res = stat.executeQuery();    
            return res;                       
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        } 		
    }
    public ResultSet showAllVoteInfo()
    {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("SELECT iSingerID,cSingerName,iVoteCount FROM Singer ORDER BY iVoteCount DESC");
            ResultSet res = stat.executeQuery();    
            return res;                       
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        } 		
    }
    public ResultSet showTopSinger()
    {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("SELECT TOP 10 * FROM Singer ORDER BY iVoteCount DESC");
            ResultSet res = stat.executeQuery();    
            return res;                       
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        } 		
    }
    public boolean updateVoteCount()
    {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("UPDATE Singer SET iVoteCount=iVoteCount+1 WHERE iSingerID=?");
            stat.setInt(1,this.singerID);
            int res = stat.executeUpdate();
            if(res > 0) return true;
            else return false;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return false;
        } 		
    }
    public boolean insertInfo() {    	
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("INSERT Singer(cSingerName,cSex,cRegion,vPhoto) VALUES(?,?,?,?)");
            stat.setString(1,this.singerName);
            stat.setString(2,this.singerSex);
            stat.setString(3,this.region);
            stat.setString(4,this.photo);
            int res = stat.executeUpdate();
            if(res > 0) return true;
            else return false;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return false;
        } 
    }
    public boolean deleteInfo() {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("DELETE Singer WHERE iSingerID=?");
            stat.setInt(1,this.getSingerID());            
            int res = stat.executeUpdate();
            if(res > 0) return true;
            else return false;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return false;
        } 
    }

    public boolean updateInfo() {
    	try
        {        	
            PreparedStatement stat = 
            	con.prepareStatement("UPDATE Singer SET cSingerName=?,cSex=?,cRegion=?,vPhoto=? WHERE iSingerID=?");
            stat.setString(1,this.getSingerName());
            stat.setString(2,this.getSingerSex());
            stat.setString(3,this.getRegion());
            stat.setString(4,this.getPhoto());
            stat.setInt(5,this.getSingerID());            
            int res = stat.executeUpdate();
            if(res > 0) return true;
            else return false;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return false;
        } 
    }
}