Rotate your "Featured Items"

SmithCart > How To

 

I have written a little bit of code that is external to the cart that rotates your "Featured Items" based on a date.

Here is how it works:
 
There is a separate table that needs to be built.... you can do that with THIS bit of code run in your SQL Management Studio:
 
USE [DotNetNuke]
GO
 
/****** Object: Table [dbo].[AES_FeaturedItems] Script Date: 09/23/2010 12:08:30 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[AES_FeaturedItems](
[SKU] [nvarchar](20) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
CONSTRAINT [PK_AES_FeaturedItems] PRIMARY KEY CLUSTERED 
(
[SKU] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
.... that will create a table called dbo.AES_FeaturedItems
 
Next, populate that table with the SKU (not the item number), the Start Date and the End Date that you wish the item to be featured. READ
BELOW FOR CAVEATS.
 
Now, create this SP by running the script below:
 
USE [DotNetNuke]
CREATE PROC dbo.AES_UpdateFeaturedItems AS
BEGIN
 
SELECT SKU, StartDate, EndDate 
INTO #tmp_Featured 
FROM dbo.AES_FeaturedItems t WHERE
(DATEPART(month,t.StartDate) <= DATEPART(month,GETDATE())
AND DATEPART(day,t.StartDate) <= DATEPART(day,GETDATE()))
AND
(DATEPART(month,t.EndDate) >= DATEPART(Month,GETDATE())
AND DATEPART(day,t.EndDate) >= DATEPART(day,GETDATE()));
 
/* Clear the Featured Items flag in the Smith_Products table */
 
UPDATE dbo.Smith_Products SET Featured=0;
 
/* Set the Featured Item flag in the database accordingly */
 
UPDATE dbo.Smith_Products 
SET Featured=1 
FROM dbo.Smith_Products sp 
INNER JOIN #tmp_Featured tf 
ON sp.ModelNumber = tf.SKU;
END
 
..... now, here's the caveats.
  1. It does NOT pay attention to the YEAR. I wrote this routine because we have items that are featured the same time every year; for example, christmas stuff during christmas. The routine IGNORES the year, allowing you to "set it and forget it".
  2. Don't cross new years with the dates.... I haven't re-written the stored procedure yet for something to begin in November and end in January.... (because 11 would be greater than 1, it doesn't work well). If something is going to be featured from November through the end of January, put the same item in for November through Dec. 31, then Jan 1 through the end. That will keep things straight.
  3. The update script needs to run. This is kind of obvious.... but to make it do so, you can either make a task in SQL Server (not available in Express), OR, you can put the following code in a batch file, and set it to run as a Scheduled Task under windows: osql -UUser -PPassword -SDB2010 -h-1 -Q"DotNetNuke.dbo.AES_UpdateFeaturedItems" -w65535
... that's all there is to it! I run my batch file nightly, so that it updates my featured items on my web page, depending on the dates in the database.
 
-Greg Baughman