-- SP to select Job Profiles For List


ALTER PROCEDURE [dbo].[uspf_JobProfiles_selectJobProfilesByCountryID]
(
	@CountryID as INT
)

AS
	DECLARE @cID int, @pID int
	DECLARE @RowNum int

	CREATE Table #jpt 
	(
		id int, 
		categoryID int,
		baseCategoryID int,
		profileTitle nvarchar(255),
		profileShowCompany bit, 
		profileCompanyName nvarchar(255),
		profileCountryID int,
		profileRating int, 
		profileOverview nvarchar(max),
		imageName nvarchar(255),
		imageCaption nvarchar(4000),
		username nvarchar(255)
	)
        
        -- Select Base Cateories (Parentid = 0) 

	DECLARE base_cat CURSOR FOR
		Select lut_category.CategoryId, lut_category_parents.ParentId
		from   lut_category,lut_category_parents
		WHERE	   (lut_category.CategoryID = lut_Category_parents.CategoryID) AND
			   (lut_category_parents.ParentId = 0) AND (lut_category.enabled = 1) 
	

	OPEN base_cat
	FETCH NEXT FROM base_cat 
	INTO @cID, @pID
	SET @RowNum = 0 
	WHILE @@FETCH_STATUS = 0
	BEGIN

        -- For every base category I want to get job profiles which exist in sub categories. As there are multiple levels of categories I need to use a recursive function.


	--Recursive Bit

	WITH subCats(categoryID) AS
	(
		SELECT lcp.categoryID
		FROM lut_category_parents AS lcp
		WHERE lcp.parentid = @cID
			  
		UNION ALL

		SELECT lcp2.categoryID
		FROM lut_category_parents AS lcp2
			INNER JOIN subCats AS sc
			ON lcp2.parentID = sc.CategoryID
	)

		--Insert into Results Table
		INSERT INTO #jpt (id, categoryID, baseCategoryID, profileTitle, profileShowCompany, 
			profileCompanyName, profileCountryID, profileRating, 
			 profileOverview, imageName, imageCaption, username)
		

		SELECT	tjp.id, tjp.categoryID, @cID as baseCategoryID, tjp.profileTitle, tjp.profileShowCompany, 
			tjp.profileCompanyName, tjp.profileCountryID, tjp.profileRating, 
			 tjp.profileOverview, tjpi.imageName, tjpi.imageCaption, au.username

			FROM [dbo].[tbl_jobprofile] tjp 
				
				left outer Join dbo.tbl_jobprofile_images tjpi 
				ON tjpi.profileID = tjp.id AND tjpi.imageType='Overview'
				
				left outer join dbo.aspnet_Users au 
				ON au.userid = tjp.userid
			
			WHERE tjp.profileCountryID = @countryID AND tjp.active = 1 AND tjp.enabled=1
			AND tjp.categoryID in 
				(SELECT categoryID
					FROM subCats AS sc)

				
			ORDER by tjp.profileTitle

				


		FETCH NEXT FROM base_cat
		INTO @cID, @pID
	END 
	CLOSE base_cat
	DEALLOCATE base_cat

SELECT * fROM  #jpt 

DROP table #jpt

	WITH subCats(categoryID) AS
	(
		SELECT lcp.categoryID
		FROM lut_category_parents AS lcp
		WHERE lcp.parentid = @cID
			  
		UNION ALL

		SELECT lcp2.categoryID
		FROM lut_category_parents AS lcp2
			INNER JOIN subCats AS sc
			ON lcp2.parentID = sc.CategoryID
	)

INSERT INTO #jpt (id, categoryID, baseCategoryID, profileTitle, profileShowCompany, 
	profileCompanyName, profileCountryID, profileRating, 
	 profileOverview, imageName, imageCaption, username)

SELECT	tjp.id, tjp.categoryID, @cID as baseCategoryID, tjp.profileTitle, tjp.profileShowCompany, 
	tjp.profileCompanyName, tjp.profileCountryID, tjp.profileRating, 
	tjp.profileOverview, tjpi.imageName, tjpi.imageCaption, au.username

FROM 
	[dbo].[tbl_jobprofile] AS tjp 
INNER JOIN
	subCats
	ON tjp.categoryID = subCats.categoryID
INNER JOIN
	(
		Select lut_category.CategoryId
		from   lut_category,lut_category_parents
		WHERE	   (lut_category.CategoryID = lut_Category_parents.CategoryID) AND
			   (lut_category_parents.ParentId = 0) AND (lut_category.enabled = 1) 
	) AS temp
	ON tjp.profileCountryID = tem.CategoryId
LEFT OUTER JOIN 
	dbo.tbl_jobprofile_images AS tjpi 
	ON tjpi.profileID = tjp.id 
	AND tjpi.imageType='Overview'
LEFT OUTER JOIN 
	dbo.aspnet_Users AS au 
	ON au.userid = tjp.userid
WHERE 
	tjp.active = 1 
	AND tjp.enabled=1
ORDER BY 
	tjp.profileTitle