Untitled

USE [LLAB]
GO

/****** Object:  StoredProcedure [dbo].[sp_Boss_Countrys]    Script Date: 1/12/2024 9:15:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Boss_Countrys]
(
    @UserID int = 0
)
AS
BEGIN
	--declare @UserID int = 2
	declare @admin int = 0

	select @admin = 1 from users where userid = @userid and Tier = 5
	print @admin
	create Table #tCounrties(country nvarchar(255), CountryID int, ParentID int);

	if @userID = 0 or @admin = 1
	begin
		with cats1 as(
			select bc.TemplateCatID CategoryID, lc.countryName Country, CountryID
				, 0 LEvelID, TemplateStatus
			from BossTemplateCategories bc
				inner join BossListCountries lc on lc.id = CountryID
			where ParentID = 0
			union all
			select c.TemplateCatID, Country, p.CountryID
				, LevelID +1, p.TemplateStatus
			from BossTemplateCategories c
				inner join cats1 p on c.parentID = p.CategoryID
		)
		insert into #tCounrties
		select distinct Country, CountryID, 0
		from cats1
		where exists(select * from BossTemplateTasks t where cats1.categoryID = t.categoryID);
		


		with cte as (
			select c2.categoryName as Country, count(distinct c2.ParentID) p
			from BossCategories c1
				inner join BossCategories c2 on c1.id = c2.parentID
			where c1.parentID = -1
				and exists(select * from BossListProducts p where p.productName = c1.categoryName)
			group by c2.categoryName
		)
		update t
		set ParentID = p
		from cte c
			inner join #tCounrties t on t.country = c.Country
	end
	else
		insert into #tCounrties
		select c2.categoryName as Country, lc.id, count(distinct c2.ParentID) 
		from BossCategories c1
			inner join BossCategories c2 on c1.id = c2.parentID
			inner join BossListCountries lc on lc.countryName = c2.categoryName
		where c1.parentID = -1
			and exists(select * from BossListProducts p where p.productName = c1.categoryName)
			and exists(select * from BossCategoriesLeads where @userID = leadid and catid = c2.id)
		group by c2.categoryName, lc.id

	
	select Country, Parentid Products, isnull(STRING_AGG(u.Userid, ','), '') Users, isnull(STRING_AGG(g.Userid, ','), '') Guests
	from #tCounrties c1
			left join BossCountryAdmins u on u.Countryid = c1.CountryID
			left join BossCountryGuests G on g.Countryid = c1.CountryID
	group by Country, Parentid
	order by Country, Parentid


	drop Table #tCounrties

end
GO