//----------------------------BEGIN LICENSE----------------------------
/* 
 * Willow : the Open Source WorkFlow Project
 * Distributable under GNU LGPL license by gun.org
 *
 * Copyright (C) 2004-2010 huihoo.org 
 * Copyright (C) 2004-2010  ZosaTapo <dertyang@hotmail.com> 
 * 
 * ====================================================================
 * Project Homepage : http://www.huihoo.org/willow
 * Source Forge     : http://sourceforge.net/projects/huihoo
 * Mailing list     : willow@lists.sourceforge.net 
 */
//----------------------------END  LICENSE----------------------------

package org.huihoo.workflow.coreImpl.store;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;

import org.huihoo.commons.jdbc.JDBCFactory;
import org.huihoo.commons.jdbc.JDBCUtilities;
import org.huihoo.commons.jdbc.JResultSet;
import org.huihoo.commons.jdbc.JStatement;
import org.huihoo.workflow.SchemaContext;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowCategoryImpl;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowDepartmentImpl;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowGroupImpl;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowParticipantImpl;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowRankImpl;
import org.huihoo.workflow.coreImpl.usermodel.WorkflowRoleImpl;
import org.huihoo.workflow.server.WorkflowSession;
import org.huihoo.workflow.usermodel.WorkflowCategory;
import org.huihoo.workflow.usermodel.WorkflowDepartment;
import org.huihoo.workflow.usermodel.WorkflowGroup;
import org.huihoo.workflow.usermodel.WorkflowParticipant;
import org.huihoo.workflow.usermodel.WorkflowRank;
import org.huihoo.workflow.usermodel.WorkflowRole;

public class InternalUserDatabase
{
	public InternalUserDatabase()
	{
	}

	//------------------------------------------------------------
	//
	//------------------------------------------------------------ 

	/**
	 * Return the set of {@link Group}s defined in this user database.
	 */
	public Iterator getGroupsImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap groups = new HashMap();
		genGroupTree(session,gUserDatabaseImpl, groups, null);
		return groups.values().iterator();
	}

	/**
	 * Return the set of {@link Department}s defined in this user database.
	 */
	public Iterator getDepartmentsImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap departments = new HashMap();
		genDepartmentTree(session,gUserDatabaseImpl, departments, null);
		return departments.values().iterator();
	}

	/**
	 * Return the set of {@link WorkflowRole}s defined in this user database.
	 */
	public Iterator getRolesImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap roles = new HashMap();
		genRoleTree(session,gUserDatabaseImpl, roles);
		return roles.values().iterator();
	}

	/**
	 * Return the set of {@link WorkflowParticipant}s defined in this user database.
	 */
	public Iterator getParticipantsImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap participants = new HashMap();
		genParticipantTree(session,gUserDatabaseImpl, participants);
		return participants.values().iterator();
	}

	/**
	 * Return the set of {@link Rank}s defined in this user database.
	 */
	public Iterator getRanksImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap ranks = new HashMap();
		genRankTree(session,gUserDatabaseImpl, ranks);
		return ranks.values().iterator();
	}

	/**
	 * Return the set of {@link Category}s defined in this user database.
	 */
	public Iterator getCategoriesImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl)
	{
		HashMap categories = new HashMap();
		genCategoryTree(session,gUserDatabaseImpl, categories);
		return categories.values().iterator();
	}

	/**
	 * Return the {@link Group} with the specified group id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param groupid id of the group to return
	 */
	public WorkflowGroup findGroupImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String groupid)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowGroup group = null;

		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description,vc_parent,vc_departmentid,vc_presidentid FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_DEPARTMENT)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, groupid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				WorkflowGroup parentGroup = null;
				String parentGroup_id = rs.getString("vc_parent");

				if (parentGroup_id != null)
				{
					parentGroup = findGroupImpl(session,gUserDatabaseImpl, parentGroup_id);
				}
				group =
					createGroupImpl(session,
						gUserDatabaseImpl,
						rs.getString("vc_uuid"),
						rs.getString("vc_name"),
						rs.getString("vc_description"),
						parentGroup);
				group.setDepartment(findDepartmentImpl(session,gUserDatabaseImpl, rs.getString("vc_departmentid")));
				group.setPresident(findParticipantImpl(session,gUserDatabaseImpl, rs.getString("vc_presidentid")));
			}
		}
		catch (SQLException ex)
		{
			;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return group;
	}

	/**
	 * Return the {@link Department} with the specified dept id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param deptid id of the dept to return
	 */
	public WorkflowDepartment findDepartmentImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String deptid)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowDepartment department = null;

		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description,vc_parent,vc_categoryid,vc_presidentid FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_DEPARTMENT)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, deptid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				WorkflowDepartment parentDept = null;
				String parentDept_id = rs.getString("vc_parent");

				if (parentDept_id != null)
				{
					parentDept = findDepartmentImpl(session,gUserDatabaseImpl, parentDept_id);
				}
				department =
					createDepartmentImpl(session,
						gUserDatabaseImpl,
						rs.getString("vc_uuid"),
						rs.getString("vc_name"),
						rs.getString("vc_description"),
						parentDept);
				department.setCategory(findCategoryImpl(session,gUserDatabaseImpl, rs.getString("vc_categoryid")));
				department.setPresident(findParticipantImpl(session,gUserDatabaseImpl, rs.getString("vc_presidentid")));
			}
		}
		catch (SQLException ex)
		{
			;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return department;
	}
	/**
	 * Return the {@link WorkflowRole} with the specified role id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param roleid id of the role to return
	 */
	public WorkflowRole findRoleImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl, String roleid)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowRole role = null;

		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_ROLE)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, roleid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				role =
					createRoleImpl(session,
						gUserDatabaseImpl,
						rs.getString("vc_uuid"),
						rs.getString("vc_name"),
						rs.getString("vc_description"));
			}
		}
		catch (SQLException ex)
		{
			;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return role;
	}

	/**
	 * Return the {@link WorkflowParticipant} with the specified user id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param userid id of the user to return
	 */
	public WorkflowParticipant findParticipantImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String userid)
	{
		//System.err.println("@@@@@@@@@@@@========>"+userid);
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowParticipant participant = null;

		String strSQL =
			"SELECT vc_uuid,vc_name,vc_password,vc_fullname,vc_description,vc_rankid "
				+ " "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_USER)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, userid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				participant =
					createParticipantImpl(session,
						gUserDatabaseImpl,
						rs.getString("vc_uuid"),
						rs.getString("vc_name"),
						rs.getString("vc_password"),
						rs.getString("vc_fullname"),
						rs.getString("vc_description"));

				participant.setRank(findRankImpl(session,gUserDatabaseImpl, rs.getString("vc_rankid")));

				JDBCUtilities.cleanupNoThrow(pstmt);

				strSQL =
					"SELECT vc_packageId "
						+ " "
						+ " FROM "
						+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_ADMIN)
						+ " WHERE vc_userid=?";
				pstmt = conn.prepareStatement(strSQL);
				pstmt.setString(1, userid);
				rs = pstmt.executeQuery();

				//System.err.println("strSQL : "+strSQL);

				while (rs.next())
				{
					//System.err.println("participant : "+userid+",vc_packageId : "+rs.getString("vc_packageId"));
					if (WorkflowParticipant.SUPER_ADMINISTRATOR_PACKAGE.equalsIgnoreCase(rs.getString("vc_packageId")))
					{
						((WorkflowParticipantImpl) participant).setSuperAdministrator(true);
					}
					else
					{
						((WorkflowParticipantImpl) participant).addPackage(rs.getString("vc_packageId"));
					}
				}
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return participant;
	}

	/**
	 * Return the {@link Rank} with the specified rank id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param rankid id of the rank to return
	 */
	public WorkflowRank findRankImpl(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl, String rankid)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowRank rank = null;

		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_RANK)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, rankid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				rank = new WorkflowRankImpl(rs.getString("vc_uuid"), rs.getString("vc_name"), rs.getString("vc_description"));
			}
		}
		catch (SQLException ex)
		{
			;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return rank;
	}

	/**
	 * Return the {@link Category} with the specified rank id, if any;
	 * otherwise return <code>null</code>.
	 *
	 * @param cetegoryid id of the Category to return
	 */
	public WorkflowCategory findCategoryImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String cetegoryid)
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		WorkflowCategory cetegory = null;

		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_CATEGORY)
				+ " WHERE vc_uuid=?";
		try
		{
			conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
			pstmt = conn.prepareStatement(strSQL);
			pstmt.setString(1, cetegoryid);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				cetegory =
					new WorkflowCategoryImpl(rs.getString("vc_uuid"), rs.getString("vc_name"), rs.getString("vc_description"));
			}
		}
		catch (SQLException ex)
		{
			;
		}
		finally
		{
			JDBCUtilities.cleanupNoThrow(conn, pstmt);
		}

		return cetegory;
	}

	//---------------------------------------------------------------------------------
	// generate Rank tree
	//---------------------------------------------------------------------------------	
	protected void genRankTree(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl, HashMap ranks)
	{
		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_RANK);

		JResultSet jrs = null;

		try
		{
			jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

			int sizeRank = jrs.getRowCount();
			for (int i = 1; i <= sizeRank; ++i)
			{
				jrs.absolute(i);
				ranks.put(
					jrs.getString("vc_uuid"),
					new WorkflowRankImpl(jrs.getString("vc_uuid"), jrs.getString("vc_name"), jrs.getString("vc_description")));
			}
			jrs.release();
		}
		catch (SQLException ex)
		{
			;
		}
	}

	//---------------------------------------------------------------------------------
	// generate Category tree
	//---------------------------------------------------------------------------------	
	protected void genCategoryTree(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		HashMap categories)
	{
		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_CATEGORY);

		JResultSet jrs = null;

		try
		{
			jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

			int sizeCategory = jrs.getRowCount();
			for (int i = 1; i <= sizeCategory; ++i)
			{
				jrs.absolute(i);
				categories.put(
					jrs.getString("vc_uuid"),
					new WorkflowCategoryImpl(
						jrs.getString("vc_uuid"),
						jrs.getString("vc_name"),
						jrs.getString("vc_description")));
			}
			jrs.release();
		}
		catch (SQLException sqlex)
		{
			;
		}
	}
	//---------------------------------------------------------------------------------
	// generate WorkflowParticipant tree
	//---------------------------------------------------------------------------------	
	protected void genParticipantTree(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		HashMap participants)
	{
		String strSQL =
			"SELECT vc_uuid,vc_name,vc_password,vc_fullname,vc_description,vc_rankid "
				+ " "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_USER);

		JResultSet jrs = null;

		try
		{
			jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

			int sizeUser = jrs.getRowCount();
			WorkflowParticipant participant = null;
			for (int i = 1; i <= sizeUser; ++i)
			{
				jrs.absolute(i);
				participant =
					createParticipantImpl(
						session,
						gUserDatabaseImpl,
						jrs.getString("vc_uuid"),
						jrs.getString("vc_name"),
						jrs.getString("vc_password"),
						jrs.getString("vc_fullname"),
						jrs.getString("vc_description"));

				participant.setRank(findRankImpl(session, gUserDatabaseImpl, jrs.getString("vc_rankid")));
				participants.put(jrs.getString("vc_uuid"), participant);
			}

			jrs.release();
		}
		catch (SQLException sqlex)
		{
			;
		}

		strSQL =
			"SELECT vc_userid,vc_packageId "
				+ " "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_ADMIN);

		try
		{
			jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

			int sizeAdmin = jrs.getRowCount();
			for (int i = 1; i <= sizeAdmin; ++i)
			{
				jrs.absolute(i);
				WorkflowParticipantImpl participantImpl =
					(WorkflowParticipantImpl) findParticipantImpl(session, gUserDatabaseImpl, jrs.getString("vc_userid"));

				if (WorkflowParticipant.SUPER_ADMINISTRATOR_PACKAGE.equalsIgnoreCase(jrs.getString("vc_packageId")))
				{
					participantImpl.setSuperAdministrator(true);
				}
				else
				{
					participantImpl.addPackage(jrs.getString("vc_packageId"));
				}
			}

			jrs.release();
		}
		catch (SQLException sqlex)
		{
			;
		}
	}

	//---------------------------------------------------------------------------------
	// generate WorkflowRole tree
	//---------------------------------------------------------------------------------	
	protected void genRoleTree(WorkflowSession session, WorkflowUserDatabaseImpl gUserDatabaseImpl, HashMap roles)
	{
		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description "
				+ " FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_ROLE);

		JResultSet jrs = null;

		try
		{
			jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

			int sizeRole = jrs.getRowCount();
			for (int i = 1; i <= sizeRole; ++i)
			{
				jrs.absolute(i);
				roles.put(
					jrs.getString("vc_uuid"),
					createRoleImpl(
						session,
						gUserDatabaseImpl,
						jrs.getString("vc_uuid"),
						jrs.getString("vc_name"),
						jrs.getString("vc_description")));
			}
			jrs.release();
		}
		catch (SQLException sqlex)
		{
			;
		}
	}

	//---------------------------------------------------------------------------------
	// generate department tree
	//---------------------------------------------------------------------------------	
	protected void genDepartmentTree(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		HashMap departments,
		WorkflowDepartment parentDept)
	{
		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description,vc_categoryid,vc_presidentid FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_DEPARTMENT);

		if (parentDept == null)
		{
			strSQL += " WHERE vc_parent IS NULL";

			try
			{
				JResultSet jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

				int sizeGroup = jrs.getRowCount();
				for (int i = 1; i <= sizeGroup; ++i)
				{
					jrs.absolute(i);
					WorkflowDepartment workflowDepartment =
						createDepartmentImpl(session,
							gUserDatabaseImpl,
							jrs.getString("vc_uuid"),
							jrs.getString("vc_name"),
							jrs.getString("vc_description"),
							parentDept);
					workflowDepartment.setCategory(findCategoryImpl(session, gUserDatabaseImpl, jrs.getString("vc_categoryid")));
					workflowDepartment.setPresident(
						findParticipantImpl(session, gUserDatabaseImpl, jrs.getString("vc_presidentid")));
					departments.put(jrs.getString("vc_uuid"), workflowDepartment);
					genDepartmentTree(session, gUserDatabaseImpl, departments, workflowDepartment);
				}

				jrs.release();
			}
			catch (SQLException sqlex)
			{
				;
			}
		}
		else
		{
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;

			try
			{
				strSQL += " WHERE vc_parent=?";

				conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
				pstmt = conn.prepareStatement(strSQL);
				pstmt.setString(1, parentDept.getUUID());
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					WorkflowDepartment workflowDepartment =
						createDepartmentImpl(session,
							gUserDatabaseImpl,
							rs.getString("vc_uuid"),
							rs.getString("vc_name"),
							rs.getString("vc_description"),
							parentDept);
					workflowDepartment.setCategory(findCategoryImpl(session, gUserDatabaseImpl, rs.getString("vc_categoryid")));
					workflowDepartment.setPresident(
						findParticipantImpl(session, gUserDatabaseImpl, rs.getString("vc_presidentid")));
					departments.put(rs.getString("vc_uuid"), workflowDepartment);
					genDepartmentTree(session, gUserDatabaseImpl, departments, workflowDepartment);
				}
			}
			catch (SQLException sqlex)
			{
				;
			}
			finally
			{
				JDBCUtilities.cleanupNoThrow(conn, pstmt);
			}

		}

	}

	//---------------------------------------------------------------------------------
	// generate group tree
	//---------------------------------------------------------------------------------
	protected void genGroupTree(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		HashMap groups,
		WorkflowGroup parentGroup)
	{
		String strSQL =
			"SELECT  vc_uuid,vc_name,vc_description,vc_departmentid,vc_presidentid FROM "
				+ gUserDatabaseImpl.getSchemaContext().getTableName(SchemaContext.SCHEMA_GROUP);

		if (parentGroup == null)
		{
			try
			{
				strSQL += " WHERE vc_parent IS NULL";
				JResultSet jrs = JStatement.executeQuery(gUserDatabaseImpl.getStore(), strSQL);

				int sizeGroup = jrs.getRowCount();
				for (int i = 1; i <= sizeGroup; ++i)
				{
					jrs.absolute(i);
					WorkflowGroup workflowGroup =
						createGroupImpl(
							session,
							gUserDatabaseImpl,
							jrs.getString("vc_uuid"),
							jrs.getString("vc_name"),
							jrs.getString("vc_description"),
							parentGroup);
					workflowGroup.setDepartment(findDepartmentImpl(session, gUserDatabaseImpl, jrs.getString("vc_departmentid")));
					workflowGroup.setPresident(findParticipantImpl(session, gUserDatabaseImpl, jrs.getString("vc_presidentid")));
					groups.put(jrs.getString("vc_uuid"), workflowGroup);
					genGroupTree(session, gUserDatabaseImpl, groups, workflowGroup);

				}
				jrs.release();
			}
			catch (SQLException sqlex)
			{
				;
			}

		}
		else
		{
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;

			try
			{
				strSQL += " WHERE vc_parent=?";

				conn = JDBCFactory.getConnection(gUserDatabaseImpl.getStore());
				pstmt = conn.prepareStatement(strSQL);
				pstmt.setString(1, parentGroup.getName());
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					WorkflowGroup workflowGroup =
						createGroupImpl(
							session,
							gUserDatabaseImpl,
							rs.getString("vc_uuid"),
							rs.getString("vc_name"),
							rs.getString("vc_description"),
							parentGroup);
					workflowGroup.setDepartment(findDepartmentImpl(session, gUserDatabaseImpl, rs.getString("vc_departmentid")));
					workflowGroup.setPresident(findParticipantImpl(session, gUserDatabaseImpl, rs.getString("vc_presidentid")));
					groups.put(rs.getString("vc_uuid"), workflowGroup);
					genGroupTree(session, gUserDatabaseImpl, groups, workflowGroup);
				}
			}
			catch (SQLException ex)
			{
				;
			}
			finally
			{
				JDBCUtilities.cleanupNoThrow(conn, pstmt);
			}

		}

	}

	//-------------------------------------------------------------------------------------
	//
	//-------------------------------------------------------------------------------------	
	/**
	 * Create and return a new {@link Group} defined in this user database.
	 *
	 * @param groupid     The group id of the new group (must be unique)
	 * @param groupname   The group name of the new group
	 * @param description The description of this group
	 */
	protected WorkflowGroup createGroupImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String groupid,
		String groupname,
		String description)
	{
		WorkflowGroup group = new WorkflowGroupImpl(session, gUserDatabaseImpl, groupid, groupname, description);
		return (group);
	}

	protected WorkflowGroup createGroupImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String groupid,
		String groupname,
		String description,
		WorkflowGroup parentGroup)
	{
		WorkflowGroup group =
			new WorkflowGroupImpl(session, gUserDatabaseImpl, groupid, groupname, description, parentGroup);

		return (group);
	}

	/**
	 * Create and return a new {@link Department} defined in this user database.
	 *
	 * @param departmentid     The department id of the new department (must be unique)
	 * @param departmentname   The department name of the new department
	 * @param description The description of this department
	 */
	protected WorkflowDepartment createDepartmentImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String departmentid,
		String departmentname,
		String description)
	{
		WorkflowDepartment department =
			new WorkflowDepartmentImpl(session, gUserDatabaseImpl, departmentid, departmentname, description);
		return (department);
	}

	protected WorkflowDepartment createDepartmentImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String departmentid,
		String departmentname,
		String description,
		WorkflowDepartment parentDepartment)
	{
		WorkflowDepartment department =
			new WorkflowDepartmentImpl(
				session,
				gUserDatabaseImpl,
				departmentid,
				departmentname,
				description,
				parentDepartment);

		return (department);
	}

	/**
	 * Create and return a new {@link WorkflowRole} defined in this user database.
	 *
	 * @param roleid     The role id of the new role (must be unique)
	 * @param rolename   The role name of the new role
	 * @param description The description of this role
	 */
	protected WorkflowRole createRoleImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String roleid,
		String rolename,
		String description)
	{
		WorkflowRole role = new WorkflowRoleImpl(session, gUserDatabaseImpl, roleid, rolename, description);

		return (role);
	}

	/**
	 * Create and return a new {@link WorkflowParticipant} defined in this user database.
	 *
	 * @param userid The logon userid of the new user
	 * @param username The logon username of the new user (must be unique)
	 * @param password The logon password of the new user
	 * @param fullName The full name of the new user
	 */
	protected WorkflowParticipant createParticipantImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String userid,
		String username,
		String password,
		String fullName)
	{
		WorkflowParticipantImpl user =
			new WorkflowParticipantImpl(session, gUserDatabaseImpl, userid, username, password, fullName);

		return (user);
	}

	protected WorkflowParticipant createParticipantImpl(
		WorkflowSession session,
		WorkflowUserDatabaseImpl gUserDatabaseImpl,
		String userid,
		String username,
		String password,
		String fullName,
		String description)
	{
		WorkflowParticipantImpl user =
			new WorkflowParticipantImpl(session, gUserDatabaseImpl, userid, username, password, fullName, description);
		return (user);
	}
}
