PEO: Recommended Events

This feature can be cumbersome, but here are some ideas to make it easier to read:
1:
When adding anything to this list, ALL shows display in alphabetical order. this is dumb.
to fix:
go to System Setup / Column Rules >> find the entry where name = rcshowcode and table = Recommended
Open the record, and change the "Source" to:

SELECT Shows.shCode, CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName AS shname
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE Events.evDateTime > GETDATE()
GROUP BY Shows.shCode, Shows.shName
ORDER BY min(evdatetime)


Change the SQL SELECT to:

rcShowCode = ISNULL(( SELECT TOP 1 CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE shCode = rcShowCode GROUP BY Shows.shCode, Shows.shName), 0)

Ok. Now you can use the feature.
Go back to recommended events, and add a new show to a category. it should work. bada bing, easy to use, its the way the world should be.
There is also a 'Site level' type of entry, which governs which events show up on the default 'all events' page, the page the users see when they first get to the site. We like to have the next 7 non-rental events on this page at all times. To manage this, I use a nightly sql job to populate the Site Level events.
Here it is:


CREATE PROCEDURE Kustom_recommended AS
--Thats Kustom with a kapital K

--for testing...
--delete from recommended where rctype = 1
--get show list
SELECT
TOP 7
min(Recommended.rcCategoryCode) as cat,
Recommended.rcShowCode as show
into #ev
FROM Recommended INNER JOIN
Shows ON Recommended.rcShowCode = Shows.shCode INNER JOIN
Category ON Recommended.rcCategoryCode = Category.cgCode inner join
events on events.evshowcode = shows.shcode
WHERE
events.evendsaledate > GETDATE()
and Events.evDateTime > GETDATE()
AND Recommended.rcType = 2 --category level entries
AND Category.cgName NOT LIKE '%rental%'
group by Recommended.rcShowCode
ORDER BY min(Events.evDateTime)

--insert into recommended
declare @next int, @show int, @cat int

declare r cursor for select cat, show from #ev
open r
fetch next from r into @cat, @show
while @@fetch_status = 0
begin
if not exists (select * from recommended where rctype = 1 and rcshowcode = @show )
begin
set @next = (SELECT cnValue + 1 FROM Counters WHERE (cnName = 'NewRecommend'))
update counters set cnvalue = @next, cnlastupdate = getdate() where cnname = 'NewRecommend'
insert into recommended select @next, 1, @cat, @show, getdate()
end

fetch next from r into @cat, @show
end
close r
deallocate r
drop table #ev

--clean up recommended table:
delete from --select * from
recommended
where rcshowcode in (SELECT shCode FROM Shows WHERE shMaxDate < GETDATE())
GO


This also will delete old entries that are no longer needed.
Disclaimer: use at your own risk. Always test before using. i am not responsible for anything that may happen to you. Don't run with scissors.