/*
 * Created on 2004-12-2
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package dalu.pui.model.filetype;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import dalu.pui.model.access.*;

/**
 * @author xu_yihua
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class AccessFileType 
{
	private Connection con = null;
	private List lstFileType = new ArrayList();
	private List lstFileTypeTemp = new ArrayList();
	
	public AccessFileType()
	{
		this.con = DataBaseConnection.getConnection();
	}
	
	//功能說明：采用composite(組合)設計模式返回一個文件類型樹
	public AbstractNode getFileTypeTree() throws Exception
	{
		FileType fileType = new FileType();
		fileType.setFileTypeId("-1");
		fileType.setFileTypeName("所有類型");
		
		AbstractNode nodeRet = new FunctionNode(fileType);
		
		//加載所有的文件類型記錄
		GetFileTypeRows();
		
		lstFileTypeTemp.addAll(lstFileType);
		Iterator it = lstFileType.iterator();
		while (it.hasNext())
		{
			FunctionNode funNode = (FunctionNode)it.next();
			if (((FileType)funNode.getObj()).getFileTypeId().length() == 3) //根節點
				BuildFileTypeTreeNode(nodeRet, funNode);
		}
		lstFileTypeTemp.clear();
		return nodeRet;
	}
	
	//功能說明：根據文件類型代碼，得到子節點
	public Collection getSubFileType(String parFileTypeId) throws Exception
	{
		String strSql = "";
		Collection subFileTypeRet = new ArrayList();
		if (parFileTypeId.equals("-1"))
			strSql = "SELECT DOCTYPE_NO, DOCTYPE_ID, DOCTYPE, DOCTYPE_LEVEL, "
				+ "ISDEFAULT, PREFIX, REMARK from T_PUI_DOCTYPE "
				+ "where len(DOCTYPE_ID) = 3 "
				+ "order by DOCTYPE_ID, DOCTYPE_LEVEL";
		else
			strSql = "SELECT DOCTYPE_NO, DOCTYPE_ID, DOCTYPE, DOCTYPE_LEVEL, "
				+ "ISDEFAULT, PREFIX, REMARK from T_PUI_DOCTYPE "
				+ "where left(DOCTYPE_ID, len(DOCTYPE_ID) - 3) = '" + parFileTypeId + "' "
				+ "order by DOCTYPE_ID, DOCTYPE_LEVEL";
		Statement stmt = con.createStatement();
		ResultSet rstSubFileType = stmt.executeQuery(strSql);
		while (rstSubFileType.next())
		{
			FileType fileType = new FileType();
			fileType.setDbIncrement(rstSubFileType.getInt(1));
			fileType.setFileTypeId(rstSubFileType.getString(2));
			fileType.setFileTypeName(rstSubFileType.getString(3));
			fileType.setSerial(rstSubFileType.getInt(4));
			
			if (rstSubFileType.getInt(5) >= 1)
				fileType.setIsDefault(true);
			else
				fileType.setIsDefault(false);
			fileType.setPrefix(rstSubFileType.getString(6));
			fileType.setRemark(rstSubFileType.getString(7));
			
			if (rstSubFileType.getString(2).length() > 3)
				fileType.setParentTypeId(rstSubFileType.getString(2).substring(0, 
						rstSubFileType.getString(2).length() - 3));
			else
				fileType.setParentTypeId("-1");
			subFileTypeRet.add(fileType);
		}
		stmt.close();
		rstSubFileType.close();
		return subFileTypeRet;
	}
	
	//新增一個文件類型
	public int InsertFileType(FileType fileType) throws Exception
	{
		String maxFileTypeIdTemp = "", maxFileTypeIdLast = "";
		String strSql = "";
		int maxId = 0;
		try
		{
			maxFileTypeIdTemp = "000";
			//得到一個最大值，并加1
			if (fileType.getParentTypeId().equals("-1"))
			{
				strSql = "SELECT ISNULL(max(DOCTYPE_ID), 0) from T_PUI_DOCTYPE "
					+ "where len(DOCTYPE_ID) = 3";
				//在Access中判斷是否為空并賦值的函數是NZ用來替代碼SQL中的ISNULL
//				strSql = "SELECT NZ(max(DOCTYPE_ID), 0) from T_PUI_DOCTYPE "
//					+ "where len(DOCTYPE_ID) = 3";
			}
			else
			{
				strSql = "SELECT ISNULL(max(DOCTYPE_ID), 0) from T_PUI_DOCTYPE "
					+ "where left(DOCTYPE_ID, len(DOCTYPE_ID) - 3) = '" 
					+ fileType.getParentTypeId() + "' ";
//				strSql = "SELECT NZ(max(DOCTYPE_ID), 0) from T_PUI_DOCTYPE "
//					+ "where left(DOCTYPE_ID, len(DOCTYPE_ID) - 3) = '" 
//					+ fileType.getParentTypeId() + "' ";
//				for (int i = 0; i < fileType.getParentTypeId().length() + 3; i++)
//				{
//					maxFileTypeIdTemp += "0";
//				}
			}
			Statement stmt = con.createStatement();
			ResultSet rstMaxFileTypeId = stmt.executeQuery(strSql);
			while (rstMaxFileTypeId.next())
			{
				if (fileType.getParentTypeId().equals("-1"))
					maxId = rstMaxFileTypeId.getInt(1) + 1;
				else
				{
					String strTemp = String.valueOf(rstMaxFileTypeId.getInt(1));
					//因為字符串從0開始，所以只需要減4
					if (strTemp.length() > 3)
						strTemp = strTemp.substring(strTemp.length() - 4, 3);
					maxId = Integer.parseInt(strTemp) + 1;
				}
			}
			
			if (String.valueOf(maxId).length() == 4)
				throw new Exception("溢出錯誤！");
			
			rstMaxFileTypeId.close();
			stmt.close();
			
			maxFileTypeIdLast = maxFileTypeIdTemp.substring(0, 
					maxFileTypeIdTemp.length() - String.valueOf(maxId).length());
			
			if (fileType.getParentTypeId().equals("-1"))
				maxFileTypeIdLast += String.valueOf(maxId);
			else
				maxFileTypeIdLast = fileType.getParentTypeId() + maxFileTypeIdLast + String.valueOf(maxId);
				
			String strInsertSql = "insert into T_PUI_DOCTYPE(DOCTYPE_ID, DOCTYPE, DOCTYPE_LEVEL, "
				+ "ISDEFAULT, PREFIX, REMARK) values(?,?,?,?,?,?)";
			PreparedStatement pstat = con.prepareStatement(strInsertSql);
			pstat.setString(1, maxFileTypeIdLast);
			pstat.setString(2, fileType.getFileTypeName());
			pstat.setInt(3, fileType.getSerial());
			if (fileType.getIsDefault())
				pstat.setInt(4, 1);
			else
				pstat.setInt(4, 0);
			pstat.setString(5, fileType.getPrefix());
			pstat.setString(6, fileType.getRemark());
			pstat.executeUpdate();
			pstat.close();
			return 0;
		}
		catch(Exception e)
		{
			return 1;
		}
	}
	
	//刪除一個文件類型
	public int DeleteFileType(FileType fileType)
	{
		try
		{
			String strDelSql = "delete from T_PUI_DOCTYPE where DOCTYPE_ID like '"
				+ fileType.getFileTypeId() + "%'";
			Statement stmt = con.createStatement();
			stmt.execute(strDelSql);
			stmt.close();
			return 0;
		}
		catch(Exception e)
		{
			return 1;
		}
	}
	
	//更新一個文件類型
	public int UpdateFileType(FileType fileType)
	{
		try
		{
			String strInsertSql = "update T_PUI_DOCTYPE set DOCTYPE = ?, DOCTYPE_LEVEL = ?, "
				+ "ISDEFAULT = ?, PREFIX = ?, REMARK = ? where DOCTYPE_NO = ?";
			PreparedStatement pstat = con.prepareStatement(strInsertSql);
			pstat.setString(1, fileType.getFileTypeName());
			pstat.setInt(2, fileType.getSerial());
			if (fileType.getIsDefault())
				pstat.setInt(3, 1);
			else
				pstat.setInt(3, 0);
			pstat.setString(4, fileType.getPrefix());
			pstat.setString(5, fileType.getRemark());
			pstat.setInt(6, fileType.getDbIncrement());
			pstat.executeUpdate();
			pstat.close();
			return 0;
		}
		catch(Exception e)
		{
			return 1;
		}
	}
	
	//功能說明：根據文件類型Id，得到該文件類型的明細信息
	public FileType getFileTypeDetail(String fileTypeId) throws Exception
	{
		FileType fileType = new FileType();
		String strSql = "SELECT DOCTYPE_NO, DOCTYPE_ID, DOCTYPE, DOCTYPE_LEVEL, "
			+ "ISDEFAULT, PREFIX, REMARK from T_PUI_DOCTYPE "
			+ "where DOCTYPE_ID = '" + fileTypeId + "'";
		Statement stmt = con.createStatement();
		ResultSet rstFileTypeDetail = stmt.executeQuery(strSql);
		while (rstFileTypeDetail.next())
		{
			fileType.setDbIncrement(rstFileTypeDetail.getInt(1));
			fileType.setFileTypeId(rstFileTypeDetail.getString(2));
			fileType.setFileTypeName(rstFileTypeDetail.getString(3));
			fileType.setSerial(rstFileTypeDetail.getInt(4));
			
			if (rstFileTypeDetail.getInt(5) >= 1)
				fileType.setIsDefault(true);
			else
				fileType.setIsDefault(false);
			fileType.setPrefix(rstFileTypeDetail.getString(6));
			fileType.setRemark(rstFileTypeDetail.getString(7));
			if (rstFileTypeDetail.getString(2).length() > 3)
				fileType.setParentTypeId(rstFileTypeDetail.getString(2).substring(0, rstFileTypeDetail.getString(2).length() - 3));
			else
				fileType.setParentTypeId("-1");
		}
		stmt.close();
		rstFileTypeDetail.close();
		return fileType;
	}
	
	private void GetFileTypeRows() throws Exception
	{
		lstFileType.clear();
		String strSql = "SELECT DOCTYPE_ID, DOCTYPE, DOCTYPE_NO from T_PUI_DOCTYPE "
			+ "order by DOCTYPE_ID, DOCTYPE_LEVEL";
		Statement stmt = con.createStatement();
		ResultSet rstFileType = stmt.executeQuery(strSql);
		while (rstFileType.next())
		{
			FileType fileType = new FileType();
			fileType.setFileTypeId(rstFileType.getString(1));
			fileType.setFileTypeName(rstFileType.getString(2));
			fileType.setDbIncrement(rstFileType.getInt(3));
			FunctionNode funNode = new FunctionNode(fileType);
			lstFileType.add(funNode);
		}
		stmt.close();
		rstFileType.close();
	}
	
	private void BuildFileTypeTreeNode(AbstractNode parNode, AbstractNode childNode) throws Exception
	{
		String id = ((FileType)childNode.getObj()).getFileTypeId(); 
		parNode.add(childNode);
		for(int i = 0; i < lstFileTypeTemp.size(); i++)
		{
			FunctionNode funNode = (FunctionNode)lstFileTypeTemp.get(i);
			
			//3位表示一級
			if ((id.length() < ((FileType)funNode.getObj()).getFileTypeId().length())
					&& (id.equals(((FileType)funNode.getObj()).getFileTypeId().substring(0, ((FileType)funNode.getObj()).getFileTypeId().length() - 3))))
				BuildFileTypeTreeNode(childNode, funNode);
		}
	}
}