CREATE PACKAGE BODY "RELEASE_MANAGER"."RM_ISSUES" AS

-- Private Implementation -----------------------------------------------------


	FUNCTION VerIsGrtrThanOrEqlToStart( XXstart IN NUMBER, XX IN NUMBER, YYstart IN NUMBER, YY IN NUMBER, ZZstart IN NUMBER, ZZ IN NUMBER ) RETURN NUMBER
	AS

	BEGIN
		-- start boundary case
		IF ( XXstart < XX ) THEN
			RETURN 1;

		ELSIF ( XXstart = XX ) THEN

			-- need to consider YY
			IF ( YYstart < YY ) THEN
				RETURN 1;

			ELSIF ( YYstart = YY ) THEN

					-- need to consider ZZ
					IF ( ZZstart <= ZZ ) THEN
						RETURN 1;

					ELSE
						RETURN 0;
					END IF;

			ELSE
				RETURN 0;
			END IF;
		ELSE
			RETURN 0;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			RETURN -1;
	END;


	FUNCTION VerIsLessThanOrEqlToEnd( XXend IN NUMBER, XX IN NUMBER, YYend IN NUMBER, YY IN NUMBER, ZZend IN NUMBER, ZZ IN NUMBER ) RETURN NUMBER
	AS

	BEGIN
		-- end boundary case
		IF ( XX < XXend ) THEN
			RETURN 1;

		ELSIF ( XX = XXend ) THEN

			-- need to consider YY
			IF ( YY < YYend ) THEN
				RETURN 1;

			ELSIF ( YY = YYend ) THEN

					-- need to consider ZZ
					IF ( ZZ <= ZZend ) THEN
						RETURN 1;

					ELSE
						RETURN 0;
					END IF;
			ELSE

				RETURN 0;
			END IF;
		ELSE
			RETURN 0;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			RETURN -1;
	END;

	/*
	-	version format:		XX.YY.ZZ.abc
	*/
	FUNCTION VersionIsBetween( version IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR ) RETURN NUMBER
	AS
		XXstart		NUMBER	:=	0;
		XXend		NUMBER	:=	0;
		XX			NUMBER	:=	0;
		YYstart		NUMBER	:=	0;
		YYend		NUMBER	:=	0;
		YY			NUMBER	:=	0;
		ZZstart		NUMBER	:=	0;
		ZZend		NUMBER	:=	0;
		ZZ			NUMBER	:=	0;
		first_dot	NUMBER	:=	0;
		second_dot	NUMBER	:=	0;
		third_dot	NUMBER	:=	0;

		ProjExtstart	VARCHAR2(10);
		ProjExtend		VARCHAR2(10);
		ProjExt			VARCHAR2(10);

	BEGIN
		-- strip the version number
		first_dot :=  INSTR(version_start, '.', 1, 1);
		second_dot :=  INSTR(version_start, '.', 1, 2);
		third_dot :=  INSTR(version_start, '.', 1, 3);

		XXstart := TO_NUMBER( SUBSTR( version_start, 1, first_dot - 1 ) );
		YYstart := TO_NUMBER( SUBSTR( version_start, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
		ZZstart := TO_NUMBER( SUBSTR( version_start, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
		ProjExtstart := SUBSTR( version_start, third_dot + 1, LENGTH(version_start) - third_dot );

		first_dot :=  INSTR(version_end, '.', 1, 1);
		second_dot :=  INSTR(version_end, '.', 1, 2);
		third_dot :=  INSTR(version_end, '.', 1, 3);

		XXend := TO_NUMBER( SUBSTR( version_end, 1, first_dot - 1 ) );
		YYend := TO_NUMBER( SUBSTR( version_end, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
		ZZend := TO_NUMBER( SUBSTR( version_end, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
		ProjExtend := SUBSTR( version_end, third_dot + 1, LENGTH(version_end) - third_dot );

		first_dot :=  INSTR(version, '.', 1, 1);
		second_dot :=  INSTR(version, '.', 1, 2);
		third_dot :=  INSTR(version, '.', 1, 3);

		XX := TO_NUMBER( SUBSTR( version, 1, first_dot - 1 ) );
		YY := TO_NUMBER( SUBSTR( version, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
		ZZ := TO_NUMBER( SUBSTR( version, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
		ProjExt := SUBSTR( version, third_dot + 1, LENGTH(version) - third_dot );

		-- only include versions if all project extensions are the same
		IF ( ProjExtstart = ProjExt AND ProjExt = ProjExtend ) THEN

			IF ( VerIsGrtrThanOrEqlToStart( XXstart, XX, YYstart, YY, ZZstart, ZZ ) = 1 AND VerIsLessThanOrEqlToEnd( XXend, XX, YYend, YY, ZZend, ZZ ) = 1 ) THEN
				RETURN 1;
			ELSE
				RETURN 0;
			END IF;
		ELSE
			RETURN 0;
		END IF;

	EXCEPTION
		WHEN OTHERS THEN
			RETURN -1;
	END;

	FUNCTION GetPkgId( pkgName IN VARCHAR2 ) RETURN NUMBER
	AS
	    pkg_id NUMBER;
	BEGIN
		SELECT
			p.PKG_ID
		INTO
			pkg_id
		FROM
			PACKAGES p
		WHERE
			p.PKG_NAME = pkgName;

		RETURN pkg_id;
	EXCEPTION
	    WHEN OTHERS THEN
			dbms_output.put_line('GetPkgId exception: ' || SQLERRM );
			RETURN -1;
	END;


-- Public Implementation ------------------------------------------------------

	/*
	-- Proc: AllIssues - Gets all issues for a package from dependent packages one level deep
	--
	-- INPUT PARAMETERS:
	--
	--		pkg_name  	-  	The name of the top level package to get issues for
	--		version_start	-	The start version for the comparison
	--		version_end	-	The end version for the comparison
	*/
	PROCEDURE AllIssues( vCursor OUT T_Cur, pkg_name IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR2 )
	AS
		pkgId	NUMBER;
	BEGIN
		-- get pkg_id of the input package:
		pkgId := GetPkgId( pkg_name );

		OPEN vCursor FOR
		SELECT
			  pv.PKG_ID,
			  pv.PKG_VERSION,
			  pv.PV_ID,
			  i_pkg.ISS_ID,
			  ( SELECT pkg_name FROM PACKAGES WHERE pkg_id = pd.DPKG_ID ) AS DPV_NAME,
			  pd.DPKG_ID,
			  ( SELECT pkg_version FROM PACKAGE_VERSIONS WHERE pv_id = pd.DPV_ID ) AS DPV_VER,
			  pd.DPV_ID,
			  i_dpkg.ISS_ID AS ISSUE_ID
		FROM
			 PACKAGE_VERSIONS pv
							  LEFT OUTER JOIN CQ_ISSUES i_pkg ON pv.PV_ID = i_pkg.PV_ID
							  LEFT OUTER JOIN PACKAGE_DEPENDENCIES pd ON pv.PV_ID = pd.PV_ID
							  LEFT OUTER JOIN CQ_ISSUES i_dpkg ON pd.DPV_ID = i_dpkg.PV_ID
		WHERE
   			 pv.PKG_ID = pkgId
		AND
			VersionIsBetween( pv.PKG_VERSION, version_start, version_end ) = 1
		AND
			 ( i_dpkg.ISS_ID IS NOT NULL OR i_pkg.ISS_ID IS NOT NULL )
		ORDER BY
			 pv.PKG_ID,
			 pv.PV_ID,
			 pd.DPKG_ID,
			 pd.DPV_ID;
	EXCEPTION
		WHEN OTHERS THEN
			dbms_output.put_line('AllIssues exception: ' || SQLERRM );
	END;


	-- NOTE: make RM_PACKAGE_ISSUES a temporary table when tested ok

	/*
	-- Proc: LoadIssuesTable
	--
	-- Populates Package_Issues table with details of issues from all dependent packages.
	-- This will be for all package versions of the input pkg_name between the
	-- version_start and version_end.
	--
	-- INPUT PARAMETERS:
	--
	--		pkg_name  	-  	The name of the top level package to get issues for
	--		version_start	-	The start version for the comparison
	--		version_end	-	The end version for the comparison
	*/
	PROCEDURE LoadIssuesTable( vCursor OUT T_Cur, pkg_name IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR2 )
	AS
		pkgId	NUMBER;

		CURSOR pack_vers_cur IS
			SELECT
				pv.PV_ID,
				pv.PKG_VERSION,
				pv.PKG_ID,
				p.PKG_NAME
			FROM
				PACKAGE_VERSIONS pv
					JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
			WHERE
   				 pv.PKG_ID = pkgId
			AND
				VersionIsBetween( pv.PKG_VERSION, version_start, version_end ) = 1;

	BEGIN
		DELETE FROM RELEASE_MANAGER.RM_PKG_ISSUES; /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/

		-- get the pkg_id we are finding issues for
		pkgId := GetPkgId( pkg_name );

		-- find all the top level packages we are dealing with and recursively process their issues and their dependent package issues.
		FOR pack_ver_rec IN  pack_vers_cur
		LOOP
			InsertIssuesForDepends( pack_ver_rec.PV_ID, pack_ver_rec.PKG_ID, pack_ver_rec.PKG_NAME, pack_ver_rec.PV_ID, pack_ver_rec.PKG_VERSION );
			COMMIT;
		END LOOP;

		-- The output cursor - shows the individual versions of the top level package then were reported on
		OPEN vCursor FOR
		SELECT DISTINCT ISS_ID, PKG_VERSION FROM RM_PKG_ISSUES WHERE ISS_ID IS NOT NULL; /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
/*removed
		SELECT
			PKG_VERSION
		FROM
			RELEASE_MANAGER.RM_PACKAGE_ISSUES;
*/


	EXCEPTION
		WHEN OTHERS THEN
			dbms_output.put_line('LoadIssuesTable exception: ' || SQLERRM );
	END;


	/*
	-- Proc: InsertIssuesForDepends
	--
	-- Used by LoadIssuesTable to recursively find issues for all dependent packages
	--
	-- INPUT PARAMETERS:
	--
	--		pvID  		-  	The package version id of the package to get issues for
	--		top_pkgID	-  	The package id of the top level package to get issues for - should be the same as pvID when called from LoadIssuesTable (different when called recursively)
	--		top_pkgName -  	The package name of the top level package
	--		top_pvID  	-  	The package version id of the top level package
	--		top_pkgVer  -  	The package version description of the top level package
	*/
	PROCEDURE InsertIssuesForDepends( pvID IN NUMBER, top_pkgID IN NUMBER, top_pkgName IN VARCHAR2, top_pvID IN NUMBER, 
top_pkgVer IN VARCHAR2 )
	AS
		pkgId		NUMBER;
		issCnt		NUMBER := 0;
		pkgCheck	NUMBER := 0;
		depCheck	NUMBER := 0;

		dpkgName	VARCHAR(50);
		dpkgVersion	VARCHAR(50);

		CURSOR dep_packs_cur IS
			SELECT
				DPV_ID
			FROM
				PACKAGE_DEPENDENCIES
			WHERE
				PV_ID = pvID;

	BEGIN

		-- check to see if the package has been processed previously
		SELECT
			COUNT(*)
		INTO
			pkgCheck
		FROM
			RM_PKG_ISSUES /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
		WHERE
			DPV_ID = pvID;

		dbms_output.put_line('pkgCheck: ' || pkgCheck );

		-- Package not already processed (no cyclic dependency) - process it
		IF ( pkgCheck = 0 ) THEN

			-- check to see if this package version has any issues assigned to it
			SELECT
				COUNT(*)
			INTO
				issCnt
			FROM
				CQ_ISSUES i
			WHERE
				i.PV_ID = pvID;

			dbms_output.put_line('issCnt: ' || issCnt );

			-- Always enter a marker row into the table even if there are no issues for the package.
			-- This allows us to pick up any cyclic dependencies.
			IF ( issCnt > 0 ) THEN
				-- get issues and insert into RM_PACKAGE_ISSUES
				/*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/

				INSERT INTO RM_PKG_ISSUES ( PKG_ID, PKG_NAME, PV_ID, PKG_VERSION, DPV_ID, DPKG_NAME, DPKG_VERSION, ISS_DB, ISS_ID )
					SELECT DISTINCT
						top_pkgID		AS PKG_ID,
						top_pkgName		AS PKG_NAME,
						top_pvID		AS PV_ID,
						top_pkgVer		AS PKG_VERSION,
						pv.PV_ID		AS DPV_ID,
						p.PKG_NAME		AS DPKG_NAME,
						pv.PKG_VERSION	AS DPKG_VERSION,
						ci.ISS_DB,
						ci.ISS_ID
					FROM
						PACKAGE_VERSIONS pv
							JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
							JOIN CQ_ISSUES ci ON pv.PV_ID = ci.PV_ID
					WHERE
						pv.PV_ID = pvID;

			ELSE
				-- get the dpkg details - there will always be a row returned here
				SELECT
					p.PKG_NAME
				INTO
					dpkgName
				FROM
					PACKAGE_VERSIONS pv
						JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
				WHERE
	   				 pv.PV_ID = pvID;

				SELECT
					pv.PKG_VERSION
				INTO
					dpkgVersion
				FROM
					PACKAGE_VERSIONS pv
				WHERE
	   				 pv.PV_ID = pvID;

				-- enter a marker row
				/*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
				INSERT INTO RM_PKG_ISSUES ( PKG_ID, PKG_NAME, PV_ID, PKG_VERSION, DPV_ID, DPKG_NAME, DPKG_VERSION, ISS_DB, ISS_ID )
				VALUES (
					top_pkgID,
					top_pkgName,
					top_pvID,
					top_pkgVer,
					pvID,
					dpkgName,
					dpkgVersion,
					NULL,
					NULL );

			END IF;

			-- If this package version has dependencies then recurse
			SELECT
				COUNT(*)
			INTO
				depCheck
			FROM
				PACKAGE_DEPENDENCIES
			WHERE
				PV_ID = pvID;

			IF ( depCheck > 0 ) THEN
				-- get dependencies and call this function recursively for each one
				FOR dep_rec IN  dep_packs_cur
				LOOP
					InsertIssuesForDepends( dep_rec.DPV_ID, top_pkgID, top_pkgName, top_pvID, top_pkgVer );
				END LOOP;

			END IF;

		END IF;

	/*EXCEPTION
		WHEN OTHERS THEN
			no exception handling required
	*/
	END;

END Rm_Issues;
/
ALTER PACKAGE "RELEASE_MANAGER"."RM_ISSUES" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE
 REUSE SETTINGS TIMESTAMP '2008-05-26 11:58:46'
/
