package StudyBbs;

import java.util.*;
import java.sql.Connection;
import java.sql.ResultSet;

public class Sort{
	
	private int id;
	private String name;
	private String master;
	private int topicNum;
	private int lastTopicId;
	private String lastTopic="";
	private String lastTopicOwner="";
	private String lastTopicTime="";
	
	public Sort(int id,String name,String master,int topicNum,int lastTopicId,String lastTopic,String lastTopicOwner,String lastTopicTime){
		this.id = id;
		this.name = name;
		this.master = master;
		this.topicNum = topicNum;
		this.lastTopicId = lastTopicId;
		this.lastTopic = lastTopic;
		this.lastTopicOwner = lastTopicOwner;
		this.lastTopicTime = lastTopicTime;
			
	}
	
	public int getId(){
		return id;
	}
	
	public String getName(){
		return name;
	}
	
	public String getMaster(){
		return master;
	}
	
	public int getTopicNum(){
		return topicNum;
	}
	
	public int getLastTopicId(){
		
		return lastTopicId;
		
	}
	
	public String getLastTopic(){
		
		return lastTopic;
		
	}
	
	public String getLastTopicOwner(){
		
		return lastTopicOwner;
		
	}
	
	public String getLastTopicTime(){
		
		return lastTopicTime;
		
	}
	
	public static Vector Search(DB db) throws Exception{
		Vector Sorts = new Vector();
		ResultSet rs,rsTopic;
		int topicNum=0;
		int lastTopicId;
		String lastTopic;
		String lastTopicOwner;
		String lastTopicTime;
        String strSql;
		int sortid;
		
        strSql = "select * from sort";
		rs = db.OpenSql(strSql);
			
		while(rs.next()){
			lastTopicId =0;
			lastTopic=null;
			lastTopicOwner=null;
			lastTopicTime=null;

			sortid = rs.getInt("id");
//查詢主題數量						
        	strSql = "select count(*) from topic where sortid=" + sortid;
			rsTopic = db.OpenSql(strSql);
			if (rsTopic.next()){
				topicNum = rsTopic.getInt(1);
			}
				
        	strSql = "select * from topic where sortid=" + sortid + " order by time desc" ;
			rsTopic = db.OpenSql(strSql);
			if (rsTopic.next()){
				lastTopicId = rsTopic.getInt("id");	
				lastTopic = rsTopic.getString("topicname");	
				lastTopicTime = rsTopic.getString("time");	
				lastTopicOwner = rsTopic.getString("owner");	
			}
				
			Sorts.add(new Sort(
					sortid,
					rs.getString("sortname"),
					rs.getString("master"),
					topicNum,
					lastTopicId,
					lastTopic,
					lastTopicTime,
					lastTopicOwner				
					));
						
		}			
		return Sorts;
	}
	
	public static boolean Insert(DB db,String sortname) throws Exception{
        String strSql;
		ResultSet rs;
		int iMaxId;
        strSql = "select max(id) from sort";
		rs = db.OpenSql(strSql);  
		if ( rs.next()) {
			iMaxId=rs.getInt(1)+1;
		}
		else{
			iMaxId=1;
		}
		
        strSql = "insert into sort values('" 
        		+ iMaxId 	 +"','"
				+ sortname 	 +"','')";
		if ( db.ExecSql(strSql)==0) {
			return false;
		}
		else{
			return true;
		}
	}	
	public static boolean Delete(DB db,int sortid) throws Exception{
        String strSql;
        strSql = "delete from sort where id='"+sortid+"'";
		if ( db.ExecSql(strSql)==0) {
			return false;
		}
		else{
			return true;
		}
	}	
	public static boolean Edit(DB db,int sortid,String sortname,String master) throws Exception{
        String strSql;
        strSql = "update sort set sortname='"+sortname+"',master='"+ master +"'where id="+sortid;
		if ( db.ExecSql(strSql)==0) {
			return false;
		}
		else{
				return true;
		}
	}	
	
}