package com.bookstore.music;

import com.bookstore.db.DBSource;
import com.bookstore.BookStoreConst;
import com.bookstore.util.StringUtil;

import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.Map;

/**
 * Created on 2006-5-17
 * @author zhangh
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class BsMusicInfo
{

	DBSource		dbconn	= null;

	public BsMusicInfo(String pool) throws SQLException, IOException
	{
		dbconn = new DBSource();
		dbconn.Init(pool);
		if(pool==null)
		    pool = BookStoreConst.BOOKSTORESPOOL;
	}
	
	/**
	 * 添加音樂大類
	 * @param classifyName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void addMusicClassifyBig(String classifyName,String sort)
	throws SQLException,IOException
	{
		String sql = "";
		sql = "insert into music_classify_big(classifyName,classifySort) " +
				"VALUES('"+classifyName+"','"+sort+"')";
		dbconn.execute(sql);
	}
	
	/**
	 * 列出所有的音樂大類
	 * @return Vector
	 * @throws SQLException
	 * @throws IOException
	 */
	public Vector getAllMusicClassifyBig()throws SQLException,IOException
	{
		Vector v = null;
		String sql = "";	
		sql = "select * from music_classify_big order by classifySort ";
		v = dbconn.ListOfMapData(sql);
		return v;
	}
	
	/**
	 * 根據館名列出音樂大類
	 * @return String
	 * @param name 館名
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getMusicClassifyBigByLibraryName(String name)throws SQLException,IOException
	{
		String sql = "";	
		sql = "select * from music_classify_big where classifyName='"+name+"' order by classifySort ";
		ResultSet result = dbconn.executeQuery(sql);
		if(result.next())
			return result.getString(1);
		return null;
	}
	
	/**
	 * 修改音樂大類
	 * @param id
	 * @param classifyName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void modifyMusicClassifyBig(String id,
										String classifyName,
										String sort)throws SQLException,IOException
	{
		String sql = "";
		sql = "update music_classify_big set classifyName = '"+classifyName+"'," +
				"classifySort='"+sort+"' where id='"+id+"' ";	
		dbconn.execute(sql);
	}
	
	/**
	 * 根據ID找出相對應的音樂大類名稱
	 * @param bigId
	 * @return String
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getNameByClassifyBigId(String bigId) throws SQLException,IOException
	{
		Vector v = null;
		String classifyName = null;
		String sql = "";	
		sql = "select classifyName from music_classify_big where id = '"+bigId+"' ";		
		v = dbconn.ListOfMapData(sql);
		if(v.size()>0)
		{
			Map map = (Map)v.get(0);
			classifyName = (String)map.get("classifyName");
		}
		
		return classifyName;
	}
	
	/**
	 * 根據ID找出相對應的音樂小類
	 * @param smallId
	 * @return String
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getNameByClassifySmallId(String smallId) throws SQLException,IOException
	{
		Vector v = null;
		String classifyName = "";
		String sql = "";	
		sql = "select classifyName from music_classify_small where id = '"+smallId+"' ";		
		v = dbconn.ListOfMapData(sql);
		if(v.size()>0)
		{
			Map map = (Map)v.get(0);
			classifyName = (String)map.get("classifyName");
		}
		
		return classifyName;
	}
	
	/**
	 * 根據ID找到相對應的館區名稱
	 * @param id
	 * @return String
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getNameByLibraryId(String id) throws SQLException,IOException
	{
		Vector v = null;
		String classifyName = "";
		String sql = "";	
		sql = "select libraryName from music_library_manager where id = '"+id+"' ";		
		v = dbconn.ListOfMapData(sql);
		if(v.size()>0)
		{
			Map map = (Map)v.get(0);
			classifyName = (String)map.get("libraryName");
		}
		
		return classifyName;
	}
	
	/**
	 * 刪除音樂大類
	 * @param id
	 * @throws SQLException
	 * @throws IOException
	 */
	public void deleteMusicClassifyBig(String id)throws SQLException,IOException
	{
		String sql = "";	
		sql = "delete from music_classify_big where id = '"+id+"' ";		
		dbconn.execute(sql);
	}
	
	/**
	 * 取出音樂大類的最大序號
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getMaxSortClassifyBig() throws SQLException,IOException
	{
		Vector v = null;
		String sort = "";
		String sql = "";	
		sql = "select max(classifySort) as classifySort from music_classify_big ";
		v = dbconn.ListOfMapData(sql);
		Map map = (Map)v.get(0);
		sort = (String)map.get("classifySort");
		if(sort == null)sort="0";
		return sort;
	}
	
	/**
	 * 列出所有音樂大類對應的小類
	 * @return Vector
	 * @throws SQLException
	 * @throws IOException
	 */
	public Vector getMusicClassifySmallByBigId(String classifyBigId)throws SQLException,IOException
	{
		Vector v = null;
		String sql = "";	
		sql = "select * from music_classify_small where classifyBigId="+classifyBigId+" " +
				"order by classifySort ";
		v = dbconn.ListOfMapData(sql);
		return v;
	}
	
	/**
	 * 列出音樂小類的最大序號
	 * @return String
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getMaxSortClassifySmall() throws SQLException,IOException
	{
		Vector v = null;
		String sort = "";
		String sql = "";	
		sql = "select max(classifySort) as classifySort from music_classify_small ";
		v = dbconn.ListOfMapData(sql);
		Map map = (Map)v.get(0);
		sort = (String)map.get("classifySort");
		if(sort == null)sort="0";
		return sort;
	}
	
	/**
	 * 添加音樂小類
	 * @param classifyBigId
	 * @param classifyName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void addMusicClassifySmall(String classifyBigId,String classifyName,String sort)
		throws SQLException,IOException
	{
		String sql = "";
		sql = "insert into music_classify_small(classifyBigId,classifyName,classifySort) " +
				"VALUES('"+classifyBigId+"','"+classifyName+"','"+sort+"')";
		dbconn.execute(sql);
	}
	
	/**
	 * 修改音樂小類
	 * @param id
	 * @param classifyName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void modifyMusicClassifySmall(String id,
											String classifyName,
											String sort)throws SQLException,IOException
	{
		String sql = "";
		sql = "update music_classify_small set classifyName = '"+classifyName+"'," +
			"classifySort='"+sort+"' where id='"+id+"' ";	
		dbconn.execute(sql);
	}

	/**
	 * 刪除音樂小類
	 * @param id
	 * @throws SQLException
	 * @throws IOException
	 */
	public void deleteMusicClassifySmall(String id)throws SQLException,IOException
	{
		String sql = "";	
		sql = "delete from music_classify_small where id = '"+id+"' ";		
		dbconn.execute(sql);
	}
	/**
	 * 添加館區信息
	 * @param libraryName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void addLibrary(String libraryName,
			String sort) throws SQLException,IOException
	{
		String sql = "";
		sql = "insert into music_library_manager(libraryName,librarySort) " +
		"VALUES('"+libraryName+"','"+sort+"')";
		dbconn.execute(sql);
	}
	
	/**
	 * 列出所有的館區信息
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
	public Vector getAllLibrary()throws SQLException,IOException
	{
		Vector v = null;
		String sql = "";	
		sql = "select * from music_library_manager order by librarySort ";
		
		v = dbconn.ListOfMapData(sql);
	 
		return v;
	}
	
	/**
	 * 取出館區信息的最大序號
	 * @return String
	 * @throws SQLException
	 * @throws IOException
	 */
	public String getMaxSortLibrary() throws SQLException,IOException
	{
		Vector v = null;
		String sort = "";
		String sql = "";	
		sql = "select max(librarySort) as classifySort from music_library_manager ";
		v = dbconn.ListOfMapData(sql);
		Map map = (Map)v.get(0);
		sort = (String)map.get("classifySort");
		if(sort == null)sort="0";
		return sort;
	}
	
	/**
	 * 修改館區信息
	 * @param id
	 * @param libraryName
	 * @param sort
	 * @throws SQLException
	 * @throws IOException
	 */
	public void modifyLibrary(String id,
				String libraryName,
				String sort)throws SQLException,IOException
	{
		String sql = "";
		sql = "update music_library_manager set libraryName = '"+libraryName+"'," +
			"librarySort='"+sort+"' where id='"+id+"' ";	
		dbconn.execute(sql);
	}
	/**
	 * 刪除館區信息
	 * @param id
	 * @throws SQLException
	 * @throws IOException