/*
	File : BBSDBManager.java
*/

import java.util.*;
import java.io.*;
import java.lang.*;
import sun.misc.*;
import java.sql.*;

import com.etri.groupware.*;
import com.etri.groupware.bbs. *;

public class PandaSoftBBSDBManager implements BBSDBManagerInterface
{
	// DBConnectionPool을 사용하기 위한 전역 변수들.....
	static DBConnectionManager connectionManager = null ;
	int servletInits = 0 ;
	static Item item;
	static int getDataNumber;	
	static BBSResultStatus result = new BBSResultStatus();
	
    public void init()
    {
       	connectionManager = DBConnectionManager.getInstance();
    }

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: addItem<br>
	 [기능]: 게시물을 등록<br>
	 [파라미터]: Item item<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus addItem(Item item)
	{
	
		Statement stmt = null;
		Connection con = null;			
		
		try
		{			
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();
							
			//새로추가되는 게시물인지 답변인지를 확인하여 reference, step, lev 을 입력한다.
			//새로운 게시물인경우 : reference =0, step = 0, lev=0이다.
			//답변 게시물인 경우 : 부모게시물의 reference, step, lev 을 가져온다.

			if( item.getReference() == 0)
			{
				//새로 작성된 게시물
				//reference로 자신의 num를 갖는다.				
				String query = "Select max(num) as top from bbslist";
				ResultSet topNu = stmt.executeQuery( query );
				topNu.next();
				int number=0;

				if( topNu.getInt("top") == 0 )
					number = 1;
				number = topNu.getInt("top");
				item.setReference(++number);
				System.out.println("reference로 자신의 number( " +number + " )를 갔는다.");
							
				//setp = 1
				item.setStep(1);	
				
				//lev = 1
				item.setLev(1);				
			}
			else
			{
				//답변게시물
				//reference = 부모 게시물의 reference을 물려받는다.
				
				// step은 부모게시물의 step이 max이면 step+1을 하면되고요,
				// max가 아니면 게시물중에서 step은 부모보다 크고, 같은 lev에 있는 step을 찾아서,
				// 그 step을 자신의 것으로하고, 나머지 step은 하나씩 증가시키는 update 를 수행한다.				
				
				// 마지막 게시물의 lev를 찾아서, 그것과 자신의 lev를 비교하여서 
				// lev가 자신보다 같거나 크면, 마지막으로 
				// 작으면, 자신의 부모 step 보다 큰 것중에서 부모와 같은 lev을 찾아서, 그것의 step을 내것으로 하고
				// 나머지 step을 update 시킨다. 
				int lev=0;
				String levQuery = "select max(lev) as top from bbslist where reference=" + item.getReference();
				ResultSet levMax= stmt.executeQuery( levQuery);
				levMax.next();
				lev = levMax.getInt("top");				
				
				if(lev == 1)
				{
					item.setLev(2);
					item.setStep(2);
					System.out.println("답변 게시물이 처음입니다.");
				}
				else 
				{
					String query = "select min(step) as nu from bbslist where reference="+item.getReference() 
						+ " and lev<=" + item.getLev() + " and step>" + item.getStep() ;
					ResultSet rs = stmt.executeQuery( query );
					rs.next();
					int nu = rs.getInt("nu");
					
					if ( nu == 0 )
					{
						item.setLev( item.getLev()+1 );

						String stepQuery = "select max(step) as top from bbslist where reference="+item.getReference() ;
						ResultSet step = stmt.executeQuery( stepQuery );
						step.next();
						int stepMax = step.getInt("top");
						item.setStep( stepMax+1 );							
					}
					else
					{
						item.setLev( item.getLev() + 1 );
						item.setStep( nu );
						String stepQuery = "update bbslist set step=step+1 where reference=" + item.getReference()+ " and step>=" +nu;
						int answer = stmt.executeUpdate( stepQuery );				
						if ( answer == 0 )
							System.out.println("update 성공");
						else System.out.println( "update 실패!!!");						
					}	
				}
			}
			
			stmt.executeUpdate("Insert BBSList values ('" + item.getTeamID() +"','"+ item.getBBSBoardName()+"','"
					+ item.getAuthor()+"','"+ item.getEmail()+"','"+ item.getTitle()+"','"
					+ item.getItemType()+"','"+ item.getContent()+"','"+ item.getAttachedFile()+"','"+ item.getDate()+"',"
					+ item.getViewCount()+","+ item.getReference() + "," + item.getStep() +"," + item.getLev()+ ")" );
			
			con.commit();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			System.out.println( ex.toString() );

			result.setResultStatus(1);
			return result;
		}
		
		finally
		{
			try
			{
				con.setAutoCommit(true);
				if ( stmt != null )
					{ stmt.close(); }
			}
			catch(SQLException sqle)
			{
				while(sqle != null)
				{
					System.out.println("SQLState   : " + sqle.getSQLState() );
					System.out.println("Message   : " + sqle.getMessage() );
					System.out.println("Error Code : " + sqle.getErrorCode() );
					sqle = sqle.getNextException();
					System.out.println("");
				};
				connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}

		result.setResultStatus(0);
		return result;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: listItem<br>
	 [기능]: option에 맞는 item 배열과 total 갯수<br>
	 [파라미터]: String boardName, OptionProperty option<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */	

	public Item[] listItem(String boardName, OptionProperty option) 		
	{
		Item[] items = null;
		Statement stmt = null;
		Connection con = null;			
		
		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();

			// 리스트를 찾아야할 게시판의 이름
			String board="" ;
			if ( boardName == null )
				board = "and BBSBoardName='"+ option.getFirstBoard() + "'";
			else
				board = "and BBSBoardName='" + boardName + "'";
			
			String total =  "Select count(*) as total from bbslist where dateval >= '" + option.getStartDate() 
				+ "' and dateval <= '" + option.getEndDate() +"' " +board;
			ResultSet number = stmt.executeQuery( total ) ;
			
			// list의 총 갯수를 count 한다.
			number.next();
			getDataNumber = number.getInt ( "total" );
			
			//필요한 갯수많큼의 item의 array를 잡는다.
			items = new Item[getDataNumber+1];
						
			//order by (오름 차순 : asc, 내림차순 desc )
			String query = "Select * from bbslist where dateval>='" + option.getStartDate() + "' and dateval<='" + option.getEndDate() +"' " 
				+ board + "  order by reference " + option.getSortMethod() + ", step";
			ResultSet list = stmt.executeQuery( query );
			
			int no = 0;			
			while( list.next() )
			{	
				items[++no] = new Item( list.getString( "TeamID"), list.getString("BBSBoardName"),
						list.getInt("num"), list.getString("author"), list.getString("email"), 
						list.getString("title"), list.getString("itemType"), list.getString("content"),
						list.getString("attachedFile"), list.getString("dateval"), list.getInt("viewCount"),
						list.getInt("reference"),list.getInt("step"), list.getInt("lev") );
			}

			con.commit();
		}
		catch(Exception ex)
		{
			System.out.println("=========> listItem()");
			System.out.println( ex.toString() );
			return items;
		}
		finally
		{
			try
			{
				con.setAutoCommit( true );
				if ( stmt != null )
				{
					stmt.close();
				}
			}
			catch(SQLException sqle)
			{
				while(sqle != null)
				{
					System.out.println("SQLState   : " + sqle.getSQLState() );
					System.out.println("Message   : " + sqle.getMessage() );
					System.out.println("Error Code : " + sqle.getErrorCode() );
					sqle = sqle.getNextException();
					System.out.println("");
				};
				connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
		return items;
	}


	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: searchItems<br>
	 [기능]: searchProperty에 적합한 데이타 검색<br>
	 [파라미터]: String boardName, SearchProperty search, OptionProperty option<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */		
	public Item[] searchItems(String boardName, SearchProperty search, OptionProperty option)
	{
		Item[] items = null;
		Statement stmt = null;
		Connection con = null;			
		
		try
		{			
			con = connectionManager.getConnection("access");			
			con.setAutoCommit( false );
			stmt = con.createStatement();
				
			String title, author, content ;
			String board = "and bbsboardName='" + boardName + "'";
			
			if ( search.getTitle() != null )
				if ( search.getAuthor() != null || search.getContent() != null)
					title = " and ( title like '%" + search.getTitle() +"%'";
				else
					title = " and ( title like '%" + search.getTitle() +"%')";

			else title = "";
			
			if ( search.getAuthor() != null )
			{
				if( title == "" )
				{
					if( search.getContent() != null )
						author = " and ( author like '%" + search.getAuthor() +"%'";
					else 
						author = " and ( author like '%" + search.getAuthor() +"%')";
				}
				else
				{
					if(search.getContent() != null )
						author = " or author like '%" + search.getAuthor() +"%'";
					else
						author = " or author like '%" + search.getAuthor() +"%')";
				}
			}
			else author = "";
			
			if ( search.getContent() != null )
			{
				if ( title == "" )
				{
					if ( author == "" )
						content = " and ( content like '%" + search.getContent() +"%' )";
					else
						content = " or content like '%" + search.getContent() +"%' )";						
				}
				else
					content = " or content like '%" + search.getContent() +"%' )";
			}
			else content = "";			
	
			String total = "Select count(*) as total from bbslist where (davteval>='" + option.getStartDate() 
				+ "' and dateval<='" + option.getEndDate() +"' " + board + " ) " + title + author + content ;
			ResultSet number = stmt.executeQuery( total ) ;
			
			// list의 총 갯수를 count 한다.
			number.next();
			getDataNumber = number.getInt ( "total" );
			
			//필요한 갯수많큼의 item의 array를 잡는다.
			items = new Item[getDataNumber+1];
							
			String query = "Select * from bbslist where ( dateval>='" + option.getStartDate() + "' and dateval<='" + option.getEndDate() 
				+"' " + board + " ) " + title + author + content + " order by reference " + option.getSortMethod() + ", step";
			ResultSet list = stmt.executeQuery( query );
			int no = 0;
			
			while( list.next() )
			{	
				items[++no] = new Item( list.getString( "TeamID"), list.getString("BBSBoardName"),
					list.getInt("num"), list.getString("author"), list.getString("email"), 
					list.getString("title"), list.getString("itemType"), list.getString("content"),
					list.getString("attachedFile"), list.getString("dateval"), list.getInt("viewCount"),
					list.getInt("reference"),list.getInt("step"), list.getInt("lev") );
			}
			con.commit();
		}
		catch(Exception ex)
		{
			System.out.println( ex.toString() );
			return items;
		}		
		finally
		{
			try
			{
				con.setAutoCommit( true );
				if ( stmt != null )
				{
					stmt.close(); 
				}
			}
			catch(SQLException sqle)
			{
				while(sqle != null)
				{
					System.out.println("SQLState   : " + sqle.getSQLState() );
					System.out.println("Message   : " + sqle.getMessage() );
					System.out.println("Error Code : " + sqle.getErrorCode() );
					sqle = sqle.getNextException();
					System.out.println("");
				};
				connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
		return items;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: readItem<br>
	 [기능]: number의 게시물 읽어 오기<br>
	 [파라미터]: String id, String boardName,int number, boolean increate<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */	
	public Item readItem(String id, String boardName, int number, boolean increate)
	{
		System.out.println( "read item 시작합니다.");
		System.out.println(" boardname = " + boardName + ", number= " + number + ", boolean= " + increate ) ;
		
		Statement stmt = null;
		Connection con = null;
		Item tmpItem = new Item();

		try
		{
			con = connectionManager.getConnection("access");			
			con.setAutoCommit( false );			
			stmt = con.createStatement();
			
			//Read one record
			String readQuery = "select * from BBSList where BBSBoardName='" + boardName + "' and num=" +number;
			ResultSet rst = stmt.executeQuery(readQuery);		
			rst.next();
				
			// fill information for item
			tmpItem.setTeamID(rst.getString("TeamID"));
			tmpItem.setBBSBoardName(rst.getString("BBSBoardName"));
			tmpItem.setNumber(rst.getInt("num"));
			tmpItem.setAuthor(rst.getString("Author"));
			tmpItem.setEmail(rst.getString("Email"));
			tmpItem.setTitle(rst.getString("Title"));
			tmpItem.setItemType(rst.getString("Itemtype"));
			tmpItem.setContent(rst.getString("Content"));
			tmpItem.setAttachedFile(rst.getString("AttachedFile"));
			tmpItem.setDate(rst.getString("dateval"));
			tmpItem.setViewCount(rst.getInt("ViewCount"));
			tmpItem.setReference(rst.getInt("Reference"));
			tmpItem.setStep(rst.getInt("Step"));
			tmpItem.setLev(rst.getInt("Lev"));
						
			// boolean == trune => viewcount++ , false(modify) => viewcount 
			if ( increate == true )
			{
				String increaseQuery = "update BBSList set ViewCount=ViewCount+1 where BBSBoardName='" + boardName + "' and num=" + number;
				int result = stmt.executeUpdate(increaseQuery);			
			}	
			
			// 개봉한 item의 number 를 저장한다.
			String check = "select * from BBSDocumentState where id = '"+ id +"' and num=" + number;
			ResultSet state = stmt.executeQuery ( check );
			if ( !state.next() )
			{
				String insert = "insert BBSDocumentState values ( '" + id +"', " + number + ")";
				int result = stmt.executeUpdate ( insert );
			}
			con.commit();

		}catch(Exception e){
			System.out.println("-----------> Exception in readItem()");
			System.out.println(e.toString());

			tmpItem = null;
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		return tmpItem;	
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: checkDocumentOpen<br>
	 [기능]: number의 게시물을 사용자가 읽었었나, 안읽었었나<br>
	 [파라미터]: String id, int number<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus checkDocumentOpen(String id, int number)
	{
		Statement stmt = null;
		Connection con = null;
		Item tmpItem = new Item();

		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();
			
			String check = "select * from BBSDocumentState where id='" + id + "' and num="+ number ;
			ResultSet state = stmt.executeQuery ( check );
						
			con.commit();
		
			if ( state.next() )
				result.setResultStatus( 0 );
			else
				result.setResultStatus( 1 );
				
		}catch(Exception e){
			System.out.println("-----------> Exception in checkDocumentOpen()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus( 1 );
			return result;
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		
		return result;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: modifyItem<br>
	 [기능]: 게시물을 수정<br>
	 [파라미터]: String boardName, Item newItem, int number<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */	
	public BBSResultStatus modifyItem(String boardName, Item newItem, int number)
	{
		BBSResultStatus result = new BBSResultStatus();		
		Statement stmt = null;
		Connection con = null;		
		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();
			
			String attachedFile = "";
			if ( newItem.getAttachedFile() != null )
				attachedFile = newItem.getAttachedFile();

			String query = "update BBSList set " +
				"Author = '" + newItem.getAuthor() + "', " +
				"Email = '" + newItem.getEmail() + "', " +
				"Title = '" + newItem.getTitle() + "', " +
				"ItemType = '" + newItem.getItemType() + "', " +
				"Content = '" + newItem.getContent() + "', " +
				"AttachedFile = '" + attachedFile + "', " +
				"Date = '" + newItem.getDate() +  "' " +
				"where BBSBoardName='" + newItem.getBBSBoardName() + "' and num=" + newItem.getNumber() ;				
			
			int r = stmt.executeUpdate(query);
			if ( r == 1)
				result.setResultStatus( 0 );				
			else
				result.setResultStatus( 1 );
			con.commit();

		}catch(Exception e){
			System.out.println("-----------> in modifyItem()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}			
		
		return result;
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: deleteItem<br>
	 [기능]: 게시물을 삭제<br>
	 [파라미터]: String boardName, int number<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus deleteItem(String boardName, int number)
	{
		
		BBSResultStatus result = new BBSResultStatus();
		Statement stmt = null;
		Connection con = null;
		
		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();

			String query = "delete from BBSlist where BBSBoardName='" + boardName + "' and num=" + Integer.toString(number) ;
			int re = stmt.executeUpdate(query);					
			if( re == 1)
				result.setResultStatus( 0 );
			else
				result.setResultStatus( 1 );
			con.commit();

		}catch(Exception e){
			System.out.println("-----------> in deleteItem()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}				
		return result;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: setPersonalOption<br>
	 [기능]: 개인 정보 입력<br>
	 [파라미터]: String id,OptionProperty option<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus setPersonalOption(String id,OptionProperty option)
	{
		Statement stmt = null;
		Connection con = null;			
		
		try
		{			
			con = connectionManager.getConnection("access");			
			con.setAutoCommit ( false );
			stmt = con.createStatement();
			
			String idQuery = "select * from BBSOptionProperty where ID='" + id + "'";
			ResultSet idResult = stmt.executeQuery( idQuery );
			
			String query="";
			if( idResult.next() )
			{
				query = " update BBSOptionProperty set FirstBoard='" + option.getFirstBoard() 
					+ "', Period= " + option.getPeriod() 
					+ ", SortMethod='" + option.getSortMethod() 
					+ "', numOfItemsInPage=" +  option.getNumOfItemsInPage() 
					+ ", title='" +  option.getTitle() 
					+ "', author='" +  option.getAuthor() 
					+ "', content='" +  option.getContent() 
					+ "'  where ID='" + id + "'";
			}
			else
			{
				query = "insert BBSOptionProperty values('"+ id +"', '" + option.getFirstBoard() +"', " 
					+ option.getPeriod()  +", '" + option.getSortMethod() +"', " + option.getNumOfItemsInPage()
					+", '" + option.getTitle() +"', '"  + option.getAuthor() +"', '" + option.getContent() +"' )";	
			}
				
			int re = stmt.executeUpdate(query);					
			if( re == 1)
				result.setResultStatus( 0 );
			else
				result.setResultStatus( 1 );

			con.commit();				
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			System.out.println( ex.toString() );
			result.setResultStatus( 1 );
			return result;
		}
		
		finally
		{
			try
			{
				con.setAutoCommit( true );
				if ( stmt != null )
					{ stmt.close(); }
			}
			catch(SQLException sqle)
			{
				while(sqle != null)
				{
					System.out.println("SQLState   : " + sqle.getSQLState() );
					System.out.println("Message   : " + sqle.getMessage() );
					System.out.println("Error Code : " + sqle.getErrorCode() );
					sqle = sqle.getNextException();
					System.out.println("");
				};
				connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		} 
							
		return result;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: getPersonalOption<br>
	 [기능]: 개인정보 가져오기<br>
	 [파라미터]: String id<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public OptionProperty getPersonalOption(String id)
	{
		OptionProperty option = new OptionProperty();
		Statement stmt = null;
		Connection con = null;			
		
		try
		{				
			con = connectionManager.getConnection("access");			
			con.setAutoCommit( false );
			stmt = con.createStatement();
			
			String idQuery = "select * from BBSOptionProperty where ID='" + id + "'";
			ResultSet idResult = stmt.executeQuery( idQuery );
			
			if( idResult.next() )
			{
				option.setSortMethod( idResult.getString("sortMethod" ) );
				option.setFirstBoard( idResult.getString( "firstBoard" ) );
				option.setPeriod( idResult.getInt( "period") );
				option.setNumOfItemsInPage( idResult.getInt( "numOfItemsInPage") );
				option.setTitle( idResult.getString( "title" ) );
				option.setAuthor( idResult.getString( "author" ) );
				option.setContent( idResult.getString( "content") );			
			}
			else
			{
				option = null;
			}			
			con.commit ();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			System.out.println( ex.toString() );
			option = null;
			return option;
		}		
		finally
		{
			try
			{
				con.setAutoCommit( true );
				if ( stmt != null )
					{ stmt.close(); }
			}
			catch(SQLException sqle)
			{
				while(sqle != null)
				{
					System.out.println("SQLState   : " + sqle.getSQLState() );
					System.out.println("Message   : " + sqle.getMessage() );
					System.out.println("Error Code : " + sqle.getErrorCode() );
					sqle = sqle.getNextException();
					System.out.println("");
				};
				connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
		return option;
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: createBBS<br>
	 [기능]: 새로운 게시판을 만들자.<br>
	 [파라미터]: String DepartmentID, String TeamID, String BBSBoardName<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus createBBS(String Department, String Team, String BoardName)
	{
		BBSResultStatus result = new BBSResultStatus();		
		Statement stmt = null;
		Connection con = null;
		
		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();
			
			ResultSet rslt = stmt.executeQuery( "select * from bbsboardname where boardname='"+ BoardName +"'");
			if ( rslt.next() )
			{
				result.setResultStatus(ResultStatus.SUCCESS);
				System.out.println(" exits");
			}
			else
			{			
				String query = "insert BBSBoardName (Team, Department, BoardName) values ('" + Team + "', '" + Department+ "', '" + BoardName + "')";
				int i = stmt.executeUpdate(query);
				if ( i == 1 )
					result.setResultStatus(ResultStatus.SUCCESS);
				else
					result.setResultStatus(ResultStatus.FAILURE);
			}
			con.commit();			

		}catch(Exception e){
			System.out.println("-----------> in createBBS()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		return result;
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: deleteBBS<br>
	 [기능]: 게시판 삭제<br>
	 [파라미터]: String BBSBoardName<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus deleteBBS(String BoardName)
	{
		BBSResultStatus result = new BBSResultStatus();
		Statement stmt = null;
		Connection con = null;
		
		try
		{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();

			String queryBoardName = "delete from BBSBoardName where boardname='"+ BoardName + "' ";
			System.out.println( queryBoardName );
			int name = stmt.executeUpdate( queryBoardName );
			if ( name == 1)
				System.out.println("성공입니다.");
			else
				System.out.println("실패입니다.");
			
			int list = 0; 			
			String bbsList = "select count(*) as total from bbslist where bbsboardname='"+BoardName +"' ";
			ResultSet rs = stmt.executeQuery( bbsList );
			
			rs.next();
			if ( rs.getInt("total") >= 1 )
			{
				String queryBBSList = "delete from bbslist where bbsboardname ='" + BoardName + "' ";
				System.out.println( queryBBSList );
				list = stmt.executeUpdate( queryBBSList );
				if ( list == 1 )
					System.out.println("성공입니다.");
				else
					System.out.println("실패입니다.");
			}
			else
				list = 1;		
							
			if ( name == 1 && list == 1)
			{
				result.setResultStatus(ResultStatus.SUCCESS);
				System.out.println( "성공입니다.");
			}
			else
			{
				result.setResultStatus(ResultStatus.FAILURE);
				System.out.println("실패입니다");
			}
			con.commit();		
		}catch(Exception e){
			System.out.println("-----------> in deleteBBS()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		return result;
	}
	
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: renameBBS<br>
	 [기능]: 게시판이름 변경<br>
	 [파라미터]: String oldBBSBoardName, String newBBSBoardName<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus renameBBS(String oldBoardName, String newBoardName)
	{
		BBSResultStatus result = new BBSResultStatus();			
		Statement stmt = null;
		Connection con = null;
		
		try{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();

			String queryBoardName = "update BBSBoardName set boardname='"+ newBoardName+ "' where boardname='"+ oldBoardName + "' ";
			int name = stmt.executeUpdate(queryBoardName);

			int list = 1; 			
			String bbsList = "select count(*) as total from bbslist where bbsboardname='"+oldBoardName +"' ";
			ResultSet rs = stmt.executeQuery( bbsList );
			
			rs.next();
			if ( rs.getInt("total") >= 1 )
			{
				String queryBBSList = "update BBSList set bbsboardname='" + newBoardName + "' where bbsboardname='"+ oldBoardName + "' ";
				list = stmt.executeUpdate(queryBBSList);
			}
			
			int club=1;	
			if ( oldBoardName.substring( 0,3).equals("동아리") )
			{
				String clubList = "select count(*) as total from club where name='"+oldBoardName.substring(4) +"' ";
				rs = stmt.executeQuery( clubList );
				
				rs.next();
				if ( rs.getInt("total") >= 1 )
				{
					String clubUpdate = "update club set  name='"+newBoardName.substring( 4 ) +"' where name='"+oldBoardName.substring( 4 )+"'";
					club = stmt.executeUpdate ( clubUpdate);
				}
			}
			
			result.setResultStatus(ResultStatus.SUCCESS);
			con.commit();
		}catch(Exception e){
			System.out.println("-----------> in renameBBS()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		return result;
	}


	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: checkBBS<br>
	 [기능]: Option에 설정된 게시판의 유무 확인<br>
	 [파라미터]: 없음<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus checkBBS(String boardName)
	{
		BBSResultStatus result = new BBSResultStatus();
		Statement stmt = null;
		Connection con = null;
		
		try{
			con = connectionManager.getConnection("access");
			con.setAutoCommit(false);
			stmt = con.createStatement();

			String queryBoardName = "select * from bbsboardName where boardName='" + boardName + "'";
			ResultSet rslt = stmt.executeQuery( queryBoardName );
			
			if ( rslt.next() )
			{
				result.setResultStatus(ResultStatus.SUCCESS);
			}
			else
			{
				result.setResultStatus(ResultStatus.FAILURE);
			}
			con.commit();
			

		}catch(Exception e){
			System.out.println("-----------> in checkBBS()");
			System.out.println(e.toString());
			e.printStackTrace();
			result.setResultStatus(ResultStatus.FAILURE);
		}finally{
			try{
				con.setAutoCommit(true);
				stmt.close();
				connectionManager.freeConnection ("access", con ) ;  
			}catch(SQLException es){
				System.out.println(es.toString());
			}
		}
		return result;
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: getBBStree<br>
	 [기능]: 게시판 트리구조를 표여주기 위해서 필요한 함수.<br>
	 [파라미터]: 없음<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public String[][][] getBBStree ( ) 
	{
		Connection con = null ;
		Statement stmt = null ;
		
		try 
		{
			con = connectionManager.getConnection("access");          
          	con.setAutoCommit( false );          
          	stmt = con.createStatement ();   
			
			// 부서의 숫자를 알아낸다.
			ResultSet rslt = stmt.executeQuery( "SELECT department FROM bbsboardName group by department" ) ;
			int depNum = 0;
			while ( rslt.next() )
				++depNum;

			// 팀의 숫자를 알아낸다.
			rslt = stmt.executeQuery( "SELECT team FROM bbsboardName group by Team" ) ;
			int teamNum = 0;
			while ( rslt.next() )
				++teamNum;
			
			// 게시판 숫자를 알아낸다.
			rslt = stmt.executeQuery( "SELECT boardname FROM bbsboardname group by boardname" ) ;
			int boardNum = 0;
			while ( rslt.next() )
				++boardNum;

			String[][][] bbs = new String[depNum+1][teamNum+2][boardNum+2] ;
			int index = 0 ;
			
			// 부서를 가져온다..
			rslt = stmt.executeQuery ( "SELECT department FROM bbsboardname group by department" ) ;
						
			while ( rslt.next ( ) )
			{
				bbs[index][0][0] = rslt.getString ( "department" ) ;
				bbs[index++][1][0] = "null" ;
			}
			bbs[ index ][0][0] = "null" ;
			
			// 팀을 가져온다.
			rslt = stmt.executeQuery ( "SELECT department, team FROM bbsboardname group by department, team" ) ;
			while ( rslt.next ( ) )
			{
				String dep = rslt.getString ( "department" ) ;
				for ( int i = 0 ; i < depNum ; i++ )
				{
					// 같은 부서가 나올때까지, 부서배열을 돌린다.
					if ( bbs[i][0][0].equals( dep ) )
					{
						// 같은 부서가 나타나면 팀배열의 빈자리를 체크하여 거기다 입력하고,
						// 다음 배열이 마지막임을 알리기 위하여 null을 입력한다.
						for ( int j = 1 ; j < (teamNum+2) ; j++ )
						{
							if ( bbs[i][j][0].equals( "null" ) )
							{
								bbs[i][j][0] = rslt.getString ( "team" ) ;
								bbs[i][j][1] = "null";
								bbs[i][j+1][0] = "null" ;
								break ;
							}
						}
					}
				}
			}
			
			// 게시판 이름을 가져온다.
			rslt = stmt.executeQuery ( "SELECT * FROM bbsboardname" ) ;			
			while ( rslt.next ( ) )
			{
				String dep = rslt.getString ( "department" );
				String team = rslt.getString ( "team" );
				for ( int i = 0 ; i < depNum ; i++)
				{
					for ( int j=1 ; j < (teamNum+1) ; j++)
					{
						if ( bbs[i][0][0].equals( dep ) && bbs[i][j][0].equals( team ) )
						{
							for ( int k = 1; k < (boardNum+2) ; k++)
							{
								if ( bbs[i][j][k].equals( "null" ) )
								{
									bbs[i][j][k] = rslt.getString ( "boardname" ) ;
									bbs[i][j][k+1] = "null" ;
									break ;
								}
							}
							break;
						}
					}
				}
			}
			rslt.close ( ) ;			
			con.commit();
			return bbs ;
			
		} catch ( Exception e ) {
			System.out.println("-----------> in BBStree()");
			System.out.println ( e.toString ( ) ) ;
			return null;
		} finally {
			try { 
				con.setAutoCommit( true );
				if ( stmt != null ) { stmt.close ( ) ; } 
			} catch ( SQLException sqle ) {
				while ( sqle != null ) {
					System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
					System.out.println ( "Message : " + sqle.getMessage ( ) ) ; 
					System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
					sqle = sqle.getNextException ( ) ;
					System.out.println ( " " ) ;
				} ;
			  	connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: getClubTree<br>
	 [기능]: 동아리 트리 가져오기...<br>
	 [파라미터]: 없음<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public String[][] getClubTree ( ) 
	{	
		Connection con = null ;
		Statement stmt = null ;		
		try 
		{
			con = connectionManager.getConnection("access");          
          	con.setAutoCommit( false );
          	stmt = con.createStatement ();   
			
			// 동아리의 숫자를 알아낸다.
			ResultSet rslt = stmt.executeQuery( "SELECT name FROM club group by name" ) ;
			int clubNum = 0;
			while ( rslt.next() )
				++clubNum;
			
			// 팀의 숫자를 알아낸다.
			rslt = stmt.executeQuery( "SELECT id FROM club group by id" ) ;
			int memeberNum = 0;
			while ( rslt.next() )
				++memeberNum;
				
			String[][] club = new String[clubNum+1][memeberNum+2];
			int index = 0 ;
			
			// 동아리 이름을 가져온다..
			rslt = stmt.executeQuery ( "SELECT name FROM club group by name" ) ;						
			while ( rslt.next ( ) )
			{
				club[index][0] = rslt.getString ( "name" ) ;
				club[index++][1] = "null" ;
			}
			club[ index ][0] = "null" ;
			
			// 멤버들의 id, name을 가져온다.
			rslt = stmt.executeQuery ( "SELECT name, id FROM club group by name, id" ) ;
			while ( rslt.next ( ) )
			{
				String name = rslt.getString ( "name" ) ;
				for ( int i = 0 ; i < clubNum ; i++ )
				{
					// 같은 동아리가 나올때까지, 동아리배열을 돌린다.
					if ( club[i][0].equals( name ) )
					{
						// 같은 동아리가 나타나면 멤버배열의 빈자리를 체크하여 거기다 입력하고,
						// 다음 배열이 마지막임을 알리기 위하여 null을 입력한다.
						for ( int j = 1 ; j < (memeberNum+2) ; j++ )
						{
							if ( club[i][j].equals( "null" ) )
							{
								String memeberId = rslt.getString("id");
								club[i][j] = memeberId;
								club[i][j+1] = "null" ;
								break ;
							}
						}
					}
				}
			}	

			for( int i = 0 ; !club[i][0].equals("null") ; i ++ )
				for ( int j = 1 ; !club[i][j].equals("null"); j ++ )
				{
					String memeberId = club[i][j];
					rslt = stmt.executeQuery( "select memeberName from club where id='"+ memeberId + "'");
					rslt.next();
					String memeberName = rslt.getString("memeberName");
					String temp = memeberName + "(" + memeberId + ")";
					club[i][j] = temp;
				}
			rslt.close ( ) ;			
			con.commit();
			return club ;
						
		} catch ( Exception e ) {
			System.out.println("-----------> in getClubTree()");
			System.out.println ( e.toString ( ) ) ;
			return null;
		} finally {
			try { 
				con.setAutoCommit( true );
				if ( stmt != null ) { stmt.close ( ) ; } 
			} catch ( SQLException sqle ) {
				while ( sqle != null ) {
					System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
					System.out.println ( "Message : " + sqle.getMessage ( ) ) ; 
					System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
					sqle = sqle.getNextException ( ) ;
					System.out.println ( " " ) ;
				} ;
			  	connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
	}

	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: clubInsert<br>
	 [기능]: 동아리 회원을 추가하는 기능<br>
	 [파라미터]: String name, String memeber<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus clubInsert ( String name, String memeber ) 		
	{
		Connection con = null ;
		Statement stmt = null ;		
		try 
		{
			con = connectionManager.getConnection("access");
          	con.setAutoCommit( false );
          	stmt = con.createStatement ();   
          
          	for ( int i = 0 ; i < memeber.length(); i ++ )
          	{	
          		String temp = memeber.substring ( 0, memeber.indexOf( ";") );
          		int j = temp.indexOf("(");
	          	String memeberName = temp.substring( 0, j );
    	      	String id = temp.substring ( j+1, temp.length()-1 );
        	  	ResultSet  rslt = stmt.executeQuery ( "Select * from club where name='"+name+"' and id='"+ id +"'");
          		if ( !rslt.next() )
	          	{
    	      		int re = stmt.executeUpdate( " insert club values('"+ id+"', '"+ name +"', '"+memeberName +"')" );
        	  		if ( re == 0 )
        	  		{
        	  			result.setResultStatus( 1 );
          				return result;
          			}
          		}
          		memeber = memeber.substring ( memeber.indexOf(";")+1, memeber.length() );
          	}			
			con.commit();			
			result.setResultStatus( 0 );
			
		} catch ( Exception e ) {
			System.out.println("-----------> in clubInsert()");
			System.out.println ( e.toString ( ) ) ;
			result.setResultStatus( 1 );
			return result;
		} finally {
			try { 
				con.setAutoCommit( true );
				if ( stmt != null ) { stmt.close ( ) ; } 
			} catch ( SQLException sqle ) {
				while ( sqle != null ) {
					System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
					System.out.println ( "Message : " + sqle.getMessage ( ) ) ; 
					System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
					sqle = sqle.getNextException ( ) ;
					System.out.println ( " " ) ;
				} ;
			  	connectionManager.freeConnection ("access", con ) ;  
			}
			connectionManager.freeConnection ("access", con ) ;  
		}
		return result ;		
	}
	
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: clubDelete<br>
	 [기능]: 동아리 회원을 탈퇴하는 기능<br>
	 [파라미터]: String name, String memeber<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public BBSResultStatus clubDelete ( String name, String memeber ) 	
	{
		Connection con = null ;
		Statement stmt = null ;		
		try 
		{
			con = connectionManager.getConnection("access");
        	con.setAutoCommit( false );        	  
	        stmt = con.createStatement ();   
	        
    	    ResultSet rslt = stmt.executeQuery ("select count(*) as total from club where name='" + name + "'");
    	    rslt.next();    	    
    	    if ( rslt.getInt("total") >= 1 )
    	    {    	        	    
	    	  	if ( memeber.equals("all") )
	    	  	{
	    	  		int re = stmt.executeUpdate("Delete from club where name='" + name + "'");
	    	  		if ( re == 0 )
		        		{
		          			result.setResultStatus(1);
		          			return result;
		          		} 
	    	  	}
	    	  	else
	    	  	{    	     
		    	    for ( int i = 0; i < memeber.length() ; i++ )
		    	    {
		    	    	System.out.println( memeber );
		          		String temp = memeber.substring ( 0, memeber.indexOf( ";") );
		          		int j = temp.indexOf("(");
			          	String memeberName = temp.substring( 0, j );
		    	      	String id = temp.substring ( j+1, temp.length()-1 );
		        	  	rslt = stmt.executeQuery ( "Select * from club where name='"+name+"' and id='"+ id +"'");
		          		if ( rslt.next() )
			          	{
		    	      		int re = stmt.executeUpdate( " delete from club where name='"+name+"' and id='"+ id +"'");
		        	  		if ( re == 0 )
		        	  		{
		          				result.setResultStatus(1);
		          				return result;
		          			}
		          		}    	    	
		          		memeber = memeber.substring ( memeber.indexOf(";")+1, memeber.length() );
		    	    }
				}
			}
			con.commit();
								 
			} catch ( Exception e ) {
				System.out.println("-----------> in clubDelete()");
				System.out.println ( e.toString ( ) ) ;
				result.setResultStatus(1);
				return result;
			} finally {
				try { 
					con.setAutoCommit( true );
					if ( stmt != null ) { stmt.close ( ) ; } 
				} catch ( SQLException sqle ) {
					while ( sqle != null ) {
						System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
						System.out.println ( "Message : " + sqle.getMessage ( ) ) ; 
						System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
						sqle = sqle.getNextException ( ) ;
						System.out.println ( " " ) ;
					} ;
				  	connectionManager.freeConnection ("access", con ) ;  
				}
				connectionManager.freeConnection ("access", con ) ;  
			}
		result.setResultStatus(0);
		return result;
	}
		
	/*----------------------------------------------------------------- */
	/**
	 [메소드이름]: getClub<br>
	 [기능]: 사용자가 들어있는 동아리 리스트를 가져오는 기능<br>
	 [파라미터]:  String id<br>
	 [호출자] : BBSController<br>
	 */
	/*--------------------------------------------------------------- */
	public String[] getClub( String id ) 
	{		
		Connection con = null ;
		Statement stmt = null ;
		String[] club=null;
		
		try 
		{
			con = connectionManager.getConnection("access");
        	con.setAutoCommit( false );
	        stmt = con.createStatement ();   
	        
    	    String query = "select count(*) as total from club where id='" + id + "'";
    	    ResultSet rslt = stmt.executeQuery ( query );
    	    rslt.next();
    	        	    
    	    int total = rslt.getInt("total");
    	    
    	    if ( total >= 1 )
    	    {    
    	    	club = new String[ total ];
    	    	String query2 = "select name from club where id='" + id + "'";
    	    	rslt = stmt.executeQuery ( query2 );
    	    	
    	    	int index=0;
    	    	while ( rslt.next() )
	    	    {
	    	    	club[index++] = rslt.getString( "name");	
	    	    }
			}
			con.commit();
					 
			} catch ( Exception e ) {
				System.out.println("-----------> in getClub()");
				System.out.println ( e.toString ( ) ) ;
				return club;
			} finally {
				try { 
					con.setAutoCommit( true );
					if ( stmt != null ) { stmt.close ( ) ; } 
				} catch ( SQLException sqle ) {
					while ( sqle != null ) {
						System.out.println ( "SQLState : " + sqle.getSQLState ( ) ) ;
						System.out.println ( "Message : " + sqle.getMessage ( ) ) ; 
						System.out.println ( "Error Code : " + sqle.getErrorCode ( ) ) ;
						sqle = sqle.getNextException ( ) ;
						System.out.println ( " " ) ;
					} ;
				  	connectionManager.freeConnection ("access", con ) ;  
				}
				connectionManager.freeConnection ("access", con ) ;  
			}
		return club;
		}
}	