indexing
	description: "Database access to project data."
	author: "Patrick Ruckstuhl <patrick@tario.org>"
	date: "$Date$"
	revision: "$Revision$"

class
	PROJECT_ACCESS

inherit
	DATABASE_ACCESS

create
	make

feature -- Status

	is_found: BOOLEAN
			-- Did the last retrieve query find a project?

feature -- Access

	last_project: PROJECT
			-- Last retrieved project by a retrieve query.

	last_project_list: LIST [PROJECT]
			-- Last retrieved project list.

	last_member_list: LIST [USER]
			-- Last retrieved member list.

	last_bookmarker_list: LIST [USER]
			-- Last retrieved bookmarker list.

	last_project_request: PROJECT_REQUEST
			-- Last retrieved project request by a retrieve query.

feature -- Select queries

	retrieve_project_by_name (a_name: STRING) is
			-- Retrieve a project by a_name.
		require
			a_name_ok: a_name /= Void and then not a_name.is_empty
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
			l_projects: LIST [PROJECT]
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_name, "name")
			l_selection.set_query ("SELECT * FROM project WHERE name=:name")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			l_projects := l_list_filling.list
			if l_projects.count = 1 then
				is_found := True
				last_project := l_projects.first
			else
				is_found := False
			end
		ensure
			is_found_implies_project: is_found implies last_project /= Void
		end

	retrieve_project_by_id (a_id: INTEGER) is
			-- Retrieve a project by a_id.
		require
			a_id_ok: a_id > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
			l_projects: LIST [PROJECT]
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_id, "project_id")
			l_selection.set_query ("SELECT * FROM project WHERE project_id=:project_id")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			l_projects := l_list_filling.list
			if l_projects.count = 1 then
				is_found := True
				last_project := l_projects.first
			else
				is_found := False
			end
		ensure
			is_found_implies_project: is_found implies last_project /= Void
		end

	retrieve_all_projects (a_show_hidden: BOOLEAN) is
			-- Retrieve all projects.
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
		do
			create l_obj.make

			create l_selection.make
			if a_show_hidden then
				l_selection.set_query ("SELECT * FROM project")
			else
				l_selection.set_query ("SELECT * FROM project WHERE hidden='false'")
			end
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_project_list := l_list_filling.list
		ensure
			last_project_list: last_project_list /= Void
		end

	retrieve_partial_projects (a_show_hidden: BOOLEAN; a_start_index: INTEGER; a_count: INTEGER) is
			-- Retrieve partial project list.
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_start_index, "start_index")
			l_selection.set_map_name (a_count, "amount")
			if a_show_hidden then
				if a_start_index = 0 then
					--- XXX Workaround for a bug in eiffel store which maps 0 to NULL in the query variables
					l_selection.set_query ("SELECT * FROM project ORDER BY name ASC LIMIT 0, :amount")
				else
					l_selection.set_query ("SELECT * FROM project ORDER BY name ASC LIMIT :start_index, :amount")
				end
			else
				if a_start_index = 0 then
					--- XXX Workaround for a bug in eiffel store which maps 0 to NULL in the query variables
					l_selection.set_query ("SELECT * FROM project WHERE hidden='false' ORDER BY name ASC LIMIT 0, :amount")
				else
					l_selection.set_query ("SELECT * FROM project WHERE hidden='false' ORDER BY name ASC LIMIT :start_index, :amount")
				end
			end
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_project_list := l_list_filling.list
		ensure
			last_project_list: last_project_list /= Void
		end

	retrieve_projects_of_user (a_user: STRING): ARRAYED_LIST[TUPLE[project_id: INTEGER; project_name: STRING; group: INTEGER]] is
			-- Retrieve all projects of which a_user is developer or owner
		require
			a_user_valid: a_user /= Void and then not a_user.is_empty
		local
			l_qres: SELECTION_RESULT
			l_tuple: TUPLE[project_id: INTEGER; project_name: STRING; group: INTEGER]
		do
			select_query.set_map_name (a_user, "user_name")
			l_qres := select_query.execute_query ("[
					SELECT p.project_id, p.name, a.group_id
					FROM project p
					LEFT JOIN user_project_association a USING (project_id)
					LEFT JOIN user u USING (user_id)
					WHERE u.name =:user_name
				]")

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_qres.count)
				l_qres.start
			until
				l_qres.after
			loop
				create l_tuple
				l_tuple.project_id := l_qres.read_integer ("project_id")
				l_tuple.project_name := l_qres.read_string ("name")
				l_tuple.group := l_qres.read_integer ("group_id")
				Result.force (l_tuple)
				l_qres.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_bookmark_projects_of_user (a_user: INTEGER) is
			-- Retrieve all projects a_user has bookmarked
		require
			a_user_valid: a_user > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [PROJECT]
			l_obj: PROJECT
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_user, "user_id")
			l_selection.set_query ("[
					SELECT p.*
					FROM project p
					LEFT JOIN project_bookmark b USING (project_id)
					WHERE b.user_id =:user_id
				]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_project_list := l_list_filling.list
		ensure
			last_project_list: last_project_list /= Void
		end

	retrieve_members (a_project: INTEGER; a_access_group: INTEGER) is
			-- Retrieve members of a_project that are in a_access_group.
		require
			a_project_valid: a_project > 0
			a_access_group_valid: a_access_group > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_obj: USER
		do
			create l_obj.make

			create l_selection.make
			l_selection.set_map_name (a_project, "project_id")
			l_selection.set_map_name (a_access_group, "group_id")
			l_selection.set_query ("[
					SELECT u.* FROM user_project_association a
					JOIN user u USING(user_id)
					WHERE project_id=:project_id AND group_id=:group_id
					]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_obj)
			create l_list_filling.make (l_selection, l_obj)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_member_list := l_list_filling.list
		ensure
			last_member_list: last_member_list /= Void
		end

	retrieve_bookmarkers (a_project_id: INTEGER) is
			-- Retrieve bookmarkers of a project with a_project_id.
		require
			a_project_id_valid: a_project_id > 0
		local
			l_selection: DB_SELECTION
			l_list_filling: DB_ACTION [USER]
			l_user: USER
		do
			create l_user.make

			create l_selection.make
			l_selection.set_map_name (a_project_id, "project_id")
			l_selection.set_query ("[
					SELECT u.* FROM project_bookmark pb
					JOIN user u USING (user_id)
					WHERE pb.project_id=:project_id
					]")
			l_selection.execute_query
			handle_errors_and_warnings
			l_selection.object_convert (l_user)
			create l_list_filling.make (l_selection, l_user)
			l_selection.set_action (l_list_filling)

			l_selection.load_result
			l_selection.terminate

			last_bookmarker_list := l_list_filling.list
		ensure
			last_bookmarker_list: last_bookmarker_list /= Void
		end

	is_projects_closed_source (a_project: INTEGER): BOOLEAN is
			-- Retrieve if a_project is closed source
		require
			a_project_valid: a_project > 0
		local
			l_qres: SELECTION_RESULT
		do
			select_query.set_map_name (a_project, "project_id")
			l_qres := select_query.execute_query ("[
					SELECT closed_source FROM project
					WHERE project_id = :project_id
					]")

			if l_qres.count = 1 then
				l_qres.start
				Result := l_qres.read_string ("closed_source").out.is_equal("true")
			else
				Result := True
			end
		end

	retrieve_project_request_by_id (a_request_id: INTEGER) is
			-- Retrieve project request with id a_request_id
		require
			a_request_id_valid: a_request_id > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_request_id, "request_id")
			l_selection := select_query.execute_query ("[
					SELECT p.request_id, p.user_id, p.name, p.description, p.closed_source, p.message FROM project_request p
					WHERE p.request_id=:request_id
					]")


			if l_selection.count = 1 then
				is_found := True
				l_selection.start
				create last_project_request.make
				last_project_request.set_request_id (l_selection.read_integer ("request_id"))
				last_project_request.set_user_id (l_selection.read_integer ("user_id"))
				last_project_request.set_name (l_selection.read_string ("name"))
				last_project_request.set_description (l_selection.read_string ("description"))
				last_project_request.set_closed_source (l_selection.read_string ("closed_source").is_equal ("true"))
				last_project_request.set_message (l_selection.read_string ("message"))
			else
				is_found := False
			end

		ensure
			is_found_implies_project_request: is_found implies last_project_request /= Void
		end

	retrieve_project_request_by_name (a_request_project_name: STRING) is
			-- Retrieve project request with name a_request_project_name
		require
			a_request_project_name_valid: a_request_project_name /= Void
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_request_project_name, "name")
			l_selection := select_query.execute_query ("[
					SELECT p.request_id, p.user_id, p.name, p.description, p.closed_source, p.message FROM project_request p
					WHERE p.name=:name
					]")


			if l_selection.count = 1 then
				is_found := True
				l_selection.start
				create last_project_request.make
				last_project_request.set_request_id (l_selection.read_integer ("request_id"))
				last_project_request.set_user_id (l_selection.read_integer ("user_id"))
				last_project_request.set_name (l_selection.read_string ("name"))
				last_project_request.set_description (l_selection.read_string ("description"))
				last_project_request.set_closed_source (l_selection.read_string ("closed_source").is_equal ("true"))
				last_project_request.set_message (l_selection.read_string ("message"))
			else
				is_found := False
			end

		ensure
			is_found_implies_project_request: is_found implies last_project_request /= Void
		end

	retrieve_statistics : DS_HASH_TABLE[INTEGER, STRING] is
			-- Get general information about Origo projects
		local
			l_selection: SELECTION_RESULT
			l_project_count: INTEGER
			l_open_source_count: INTEGER
--			l_hidden_count: INTEGER
		do
			create Result.make(2)

			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM project WHERE hidden='false'")
			l_selection.start

			l_project_count := l_selection.read_integer ("count")
			Result.force(l_project_count, "project_count")

			l_selection := select_query.execute_query ("[
					SELECT COUNT(*) AS count FROM project
					WHERE closed_source = 'false' AND hidden='false'
					]")
			l_selection.start
			l_open_source_count := l_selection.read_integer ("count")
			Result.force(l_open_source_count, "open_source_count")

--			l_selection := select_query.execute_query ("SELECT COUNT(*) AS count FROM project WHERE hidden='true'")
--			l_selection.start
--			l_hidden_count := l_selection.read_integer ("count")
--			Result.force(l_open_source_count, "hidden_count")
		end

feature -- Insert queries

	insert_project (a_project: PROJECT) is
			-- Add a_project as a new project.
		require
			a_project_not_void: a_project /= Void
		local
			l_store: DB_STORE
		do
			create l_store.make
			l_store.set_repository (db_handler.db_repositories.item ("project"))
			l_store.put (a_project)
			handle_errors_and_warnings
		end

	insert_project_request (a_project_request: PROJECT_REQUEST) is
			-- Add a_project_request as a new project request.
		require
			a_project_request_not_void: a_project_request /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project_request.name, "name")
			l_store.set_map_name (a_project_request.user_id.out, "user_id")
			l_store.set_map_name (a_project_request.description, "description")
			if a_project_request.closed_source then
				l_store.set_map_name ("true", "closed_source")
			else
				l_store.set_map_name ("false", "closed_source")
			end
			if a_project_request.message.is_empty then
				l_store.set_map_name (a_project_request.message, "message")
				l_store.modify ("INSERT INTO project_request SET name=:name, user_id=:user_id, description=:description, closed_source=:closed_source, message=''")
			else
				l_store.set_map_name (a_project_request.message, "message")
				l_store.modify ("INSERT INTO project_request SET name=:name, user_id=:user_id, description=:description, closed_source=:closed_source, message=:message")
			end

			handle_errors_and_warnings
		end

	insert_project_settings (a_project_id: INTEGER) is
			-- Add a new project_settings for project with `a_project_id', only used at project creation
		require
			a_project_id_valid: a_project_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_project_id, "project_id")
			l_store.modify ("INSERT INTO `project_settings` (`project_id`) VALUES (:project_id)")
			handle_errors_and_warnings
		end

feature -- Update queries

	update_project_description (a_project_id: INTEGER; a_description: STRING) is
			-- Update the description of a_project_id to the new value a_description
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_description, "description")

			l_change.modify ("UPDATE project SET description=:description WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

	update_project_logo (a_project_id: INTEGER; a_logo: STRING) is
			-- Update the logo of a_project_id to the new value a_logo
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_logo, "logo")

			l_change.modify ("UPDATE project SET logo=:logo WHERE project_id=:project_id")
			handle_errors_and_warnings
		end

	update_project_settings (a_project_id: INTEGER; a_setting_type: STRING; a_setting_value: STRING) is
			-- Update the project settings.
		require
			a_project_id_ok: a_project_id > 0
			setting_type_ok: a_setting_type /= Void and then not a_setting_type.is_empty
			setting_value_ok: a_setting_value /= Void
		local
			l_change: DB_CHANGE
		do
				-- check for information_type and call corresponding query
			create l_change.make
			l_change.set_map_name (a_project_id, "project_id")
			l_change.set_map_name (a_setting_value, "svn_path_doc")

			if a_setting_type.is_equal ("svn_path_doc") then
				l_change.modify ("UPDATE project_settings SET svn_path_doc=:svn_path_doc WHERE project_id=:project_id")
			end

			handle_errors_and_warnings
		end


end