SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GETFULLDATE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author     :Ajith
-- Create date: 2007-9-27
-- Description: RETURN DATE TIME 
-- =============================================
CREATE FUNCTION [dbo].[GW_GETFULLDATE] 
	(@TIME varchar(5))
RETURNS DATETIME
AS
BEGIN
   DECLARE @HOUR int
   DECLARE @MINUTE int
   DECLARE @PASSINGTIME DATETIME
   DECLARE @strFULLTIMEE VARCHAR(MAX)
   SET @HOUR= CONVERT(int,(SUBSTRING(@TIME,1,2)))
   SET @MINUTE= CONVERT(int,(SUBSTRING(@TIME,4,2)))
   SET @strFULLTIMEE= CONVERT(VARCHAR(MAX),MONTH(GETDATE()))+''/''+CONVERT(VARCHAR(MAX),DAY(GETDATE()))+''/''+CONVERT(VARCHAR(MAX),YEAR(GETDATE()))
   SET @PASSINGTIME= CONVERT(DATETIME,@strFULLTIMEE)
  SET @PASSINGTIME = DATEADD(hour,@HOUR, @PASSINGTIME)
  SET @PASSINGTIME =  DATEADD(minute,@MINUTE ,@PASSINGTIME)
   RETURN @PASSINGTIME
END
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_MergeDateAndTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Create date: 2007-9-28
-- Description: Merge Date and time portion 
-- =============================================
CREATE FUNCTION [dbo].[GW_MergeDateAndTime] 
	(@DATE datetime,@TIME varchar(5))
RETURNS DATETIME
AS
BEGIN
   DECLARE @HOUR int
   DECLARE @MINUTE int
   DECLARE @PASSINGTIME DATETIME
   DECLARE @strFULLTIMEE VARCHAR(MAX)
   SET @HOUR= CONVERT(int,(SUBSTRING(@TIME,1,2)))
   SET @MINUTE= CONVERT(int,(SUBSTRING(@TIME,4,2)))
   SET @strFULLTIMEE= CONVERT(VARCHAR(MAX),MONTH(@DATE))+''/''+CONVERT(VARCHAR(MAX),DAY(@DATE))+''/''+CONVERT(VARCHAR(MAX),YEAR(@DATE))
   SET @PASSINGTIME= CONVERT(DATETIME,@strFULLTIMEE)
   SET @PASSINGTIME = DATEADD(hour,@HOUR, @PASSINGTIME)
   SET @PASSINGTIME =  DATEADD(minute,@MINUTE ,@PASSINGTIME)
   RETURN @PASSINGTIME
END
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADManager_Category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ADManager_Category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NULL,
	[Name] [varchar](200) NULL,
	[Height] [smallint] NULL,
	[Width] [smallint] NULL,
 CONSTRAINT [PK_ADManager_Category] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADManager_Ads]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ADManager_Ads](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AdManagerCategoryID] [int] NOT NULL,
	[Advertisement_Name] [varchar](100) NOT NULL,
	[Add_Src_Path] [varchar](255) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[StartTime] [float] NULL,
	[EndTime] [float] NULL,
	[Redirect_URL] [varchar](255) NOT NULL,
	[Active] [bit] NOT NULL,
	[Alt_Text] [varchar](255) NULL,
	[Weight] [smallint] NOT NULL,
	[IsImageAdd] [bit] NOT NULL,
	[OpenNewWindow] [bit] NULL,
 CONSTRAINT [PK_ADManager_Ads] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADManager_AdCompetitors]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ADManager_AdCompetitors](
	[AddID] [int] NULL,
	[CompetitorAddId] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADManager_Banner_AdTracking]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ADManager_Banner_AdTracking](
	[Ad_Id] [int] NULL,
	[IP_Address] [varchar](50) NULL,
	[Date_Time] [smalldatetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_INSERT_Ad_Tracking]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GW_INSERT_Ad_Tracking]
(      
	@Ad_Id int,
	@IP_Address varchar(20),
	@URL varchar(255) output
)

AS
BEGIN
INSERT INTO ADManager_Banner_AdTracking
(
	Ad_Id,
	IP_Address,
     Date_Time
)
VALUES
(
	@Ad_Id,
	@IP_Address,
	GETDATE()

)
Set @URL = (SELECT URL=Redirect_URL  FROM dbo.ADManager_Ads WHERE ID=@Ad_Id)
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_Get_Banner_Ad]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


/****************BEGIN STORED PROCEDURES*****************************/

CREATE  PROCEDURE [dbo].[GW_Get_Banner_Ad]
(
	@Ad_Id int,
	@Click_Count int output
)

AS
BEGIN
Set @Click_Count = (Select Count(*) From dbo.ADManager_Banner_AdTracking Where Ad_Id = @Ad_Id)

Select * From tbl_NRCA_Banner_Ads Where Ad_Id  = @Ad_Id
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GETADSDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--SELECT * FROM dbo.ADManager_Ads

CREATE PROCEDURE [dbo].[GW_GETADSDetails]
AS
BEGIN
	SELECT AddCategoryName=Name,AdName=Advertisement_Name,[Count],StartDate=CONVERT(VARCHAR(12),StartDate,103),EndDate=CONVERT(VARCHAR(12),EndDate,103) FROM ADManager_Category ac 
	INNER JOIN ADManager_Ads ads ON ads.AdmanagerCategoryID=ac.ID
	INNER JOIN (SELECT ADID=Ad_ID,[COUNT]=SUM(Ad_ID) FROM ADManager_Banner_AdTracking GROUP BY Ad_ID ) t
	ON ads.ID=t.ADID
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_Get_Random_Banner]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
/*
[GW_Get_Random_Banner] ''TOP_BANNER''
''TOP_BANNER''
''LEFT_TOP1''
''LEFT_TOP2''
''LEFT_TOP3''
*/
/*******************************************************************/

CREATE PROCEDURE [dbo].[GW_Get_Random_Banner]
(
	@ADCategoryCode varchar(20),@AdsIDList varchar(200)
)
AS
BEGIN
Declare @Count smallint
Declare @MaxWeight smallint
Declare @RecordCount int
Declare @RandNum int
DECLARE @Category int


SELECT @Category=ID FROM  dbo.ADManager_Category WHERE Code=@ADCategoryCode

CREATE TABLE #BannerAdTempTable
(
	Temp_Id int identity(1,1),
	Ad_Id int
)
Set @Count = 0

 SELECT *  INTO #ValidAdds FROM dbo.ADManager_Ads WHERE AdManagerCategoryID = @Category AND StartDate < getdate()  AND EndDate > getdate() 
 AND dbo.GW_MergeDateAndTime(getdate(),StartTime)<getdate() AND dbo.GW_MergeDateAndTime(getdate(),EndTime)>getdate()                  
					 AND Active = 1

------------Remove Already Displaying Ads and  Competitors ads------------------------------
----------Process Ad ID List--------
CREATE TABLE #CurentDisplayingAdds
(
	Temp_Id int identity(1,1),
	Ad_Id int
)


DECLARE @tempIndex int
DECLARE @tempId int
DECLARE @tempIdList varchar(200)
SET @tempIndex=0
SET @tempId=0
SET @tempIdList=@AdsIDList
WHILE(len(@tempIdList)>0)
BEGIN
	SET @tempIndex=0
	SET @tempIndex=CHARINDEX(''='',@tempIdList)
    IF(@tempIndex>0)
	BEGIN
	SET @tempId=CONVERT(INT,SUBSTRING(@tempIdList,1,@tempIndex-1))
    SET @tempIdList=SUBSTRING(@tempIdList,@tempIndex+1,(LEN(@tempIdList)-@tempIndex))
	END
    ELSE
	BEGIN
		SET @tempId=@tempIdList
		SET @tempIdList=''''
    END	

   INSERT INTO #CurentDisplayingAdds(Ad_Id)
   VALUES(@tempId)
	
	INSERT INTO #CurentDisplayingAdds(Ad_Id)---- Add Competitors list so that we can use it for remove adds
    SELECT CompetitorAddID FROM dbo.ADManager_AdCompetitors WHERE AddID=@tempId

END

DELETE #ValidAdds WHERE ID IN(SELECT Ad_Id FROM #CurentDisplayingAdds)



DROP TABLE #CurentDisplayingAdds
------------------------------------------
SELECT @MaxWeight=Max(Weight) FROM #ValidAdds


WHILE (@MaxWeight) > @Count
BEGIN
  -- INSERT INTO #BannerAdTempTable Select Id From ADManager_Ads  Where AdManagerCategoryID = @Category AND StartDate < getdate()  AND  EndDate > getdate() AND Active = 1 AND Weight > @Count
   INSERT INTO #BannerAdTempTable 
   Select Id From #ValidAdds  Where Weight > @Count
   Set @Count = @Count + 1
END

Set @RecordCount = (Select Count(*) From #BannerAdTempTable)

Set @RandNum = (Round(((@RecordCount - 1) * Rand() + 1), 0))

Select a.ID, Add_Src_Path, Height=c.Height, Width=c.Width, Alt_Text,IsImageAdd,NewWindow=OpenNewWindow From ADManager_Ads a INNER JOIN ADManager_Category c
 ON a.AdManagerCategoryID=c.ID 
 WHERE a.Id = (Select Ad_Id From #BannerAdTempTable Where Temp_Id = @RandNum)

DROP TABLE #ValidAdds
DROP TABLE #BannerAdTempTable
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_UpdateAd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*******************************************************************/

CREATE PROCEDURE [dbo].[GW_UpdateAd]
(    
	@AdID int,  	
	@AdManagerCategoryID INT,
	@Advertisement_Name varchar (100),
	@Add_Src_Path varchar (255),	
	@StartDate datetime,
	@EndDate datetime,
	@StartTime varchar(25),
	@EndTime varchar(25),
	@Redirect_URL varchar (255),
	@Active bit,
	@Alt_Text varchar (255),
	@Weight smallint,
	@IsImageAdd bit,
	@OpenNewWindow bit,
    @CompetitorsList varchar(200)
)

AS
BEGIN
BEGIN TRANSACTION SaveRecord

DECLARE @tempId INT
DECLARE @tempIndex int
DECLARE @tempIdList varchar(400)

UPDATE  dbo.ADManager_Ads SET 
AdManagerCategoryID=@AdManagerCategoryID,
	Advertisement_Name=@Advertisement_Name,
	Add_Src_Path=@Add_Src_Path,
	StartDate=@StartDate,
	EndDate=@EndDate,
	StartTime=@StartTime,
	EndTime=@EndTime,
	Redirect_URL=@Redirect_URL,
	Active=@Active,
	Alt_Text=@Alt_Text,
	Weight=@Weight,
	IsImageAdd=@IsImageAdd,    
    OpenNewWindow=@OpenNewWindow
WHERE ID=@AdID

DELETE ADManager_AdCompetitors WHERE AddID=@AdID
----------Process CompetitorsList--------
SET @tempIndex=0
SET @tempId=0
SET @tempIdList=@CompetitorsList
WHILE(len(@tempIdList)>0)
BEGIN
	SET @tempIndex=0
	SET @tempIndex=CHARINDEX(''='',@tempIdList)
    IF(@tempIndex>0)
	BEGIN
	SET @tempId=CONVERT(INT,SUBSTRING(@tempIdList,1,@tempIndex-1))
    SET @tempIdList=SUBSTRING(@tempIdList,@tempIndex+1,(LEN(@tempIdList)-@tempIndex))
	END
    ELSE
	BEGIN
		SET @tempId=@tempIdList
		SET @tempIdList=''''
    END	
   INSERT INTO dbo.ADManager_AdCompetitors(AddID,CompetitorAddId)
   VALUES(@AdID,@tempId)
END


IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION SaveRecord
END

COMMIT TRANSACTION SaveRecord
	
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GetAdCompetitorsByAdID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GW_GetAdCompetitorsByAdID](@ADID AS INT)
AS
BEGIN
SELECT ID FROM ADManager_Ads a INNER JOIN dbo.ADManager_AdCompetitors ac ON a.ID=ac.AddID
WHERE a.ID=@ADID
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_InsertAds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*******************************************************************/

CREATE PROCEDURE [dbo].[GW_InsertAds]
(      	
	@AdManagerCategoryID INT,
	@Advertisement_Name varchar (100),
	@Add_Src_Path varchar (255),	
	@StartDate datetime,
	@EndDate datetime,
	@StartTime varchar(25),
	@EndTime varchar(25),
	@Redirect_URL varchar (255),
	@Active bit,
	@Alt_Text varchar (255),
	@Weight smallint,
	@IsImageAdd bit,
	@OpenNewWindow bit,
    @CompetitorsList varchar(200)
)

AS
BEGIN

DECLARE @NewAddId int
DECLARE @tempId INT
DECLARE @tempIndex int
DECLARE @tempIdList varchar(400)
BEGIN TRANSACTION SaveRecord
 
INSERT INTO dbo.ADManager_Ads
(
	AdManagerCategoryID,
	Advertisement_Name,
	Add_Src_Path,
	StartDate,
	EndDate,
	StartTime,
	EndTime,
	Redirect_URL,
	Active,
	Alt_Text,
	Weight,
	IsImageAdd,
    OpenNewWindow
)
VALUES
(
	@AdManagerCategoryID,
	@Advertisement_Name,
	@Add_Src_Path,	
	@StartDate,
	@EndDate,
	@StartTime,
	@EndTime,
	@Redirect_URL,
	@Active,
	@Alt_Text,
	@Weight,
	@IsImageAdd,
	@OpenNewWindow
)

SELECT @NewAddId = @@identity

----------Process CompetitorsList--------
SET @tempIndex=0
SET @tempId=0
SET @tempIdList=@CompetitorsList
WHILE(len(@tempIdList)>0)
BEGIN
	SET @tempIndex=0
	SET @tempIndex=CHARINDEX(''='',@tempIdList)
    IF(@tempIndex>0)
	BEGIN
	SET @tempId=CONVERT(INT,SUBSTRING(@tempIdList,1,@tempIndex-1))
    SET @tempIdList=SUBSTRING(@tempIdList,@tempIndex+1,(LEN(@tempIdList)-@tempIndex))
	END
    ELSE
	BEGIN
		SET @tempId=@tempIdList
		SET @tempIdList=''''
    END	
   INSERT INTO dbo.ADManager_AdCompetitors(AddID,CompetitorAddId)
   VALUES(@NewAddId,@tempId)
END

IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION SaveRecord
END

COMMIT TRANSACTION SaveRecord


END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GetAdsCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GW_GetAdsCategory]
AS
BEGIN
SELECT ID,[Name] FROM dbo.ADManager_Category ORDER BY ID
END
--
--
--SELECT * FROM dbo.ADManager_Ads' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GetAds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GW_GetAds]
AS
BEGIN
SELECT ID,CategoryID=AdManagerCategoryID,Name=Advertisement_Name,Redirect_URL,startdate=convert(varchar(12),startdate) ,EndDate=convert(varchar(12),EndDate) FROM ADManager_Ads ORDER BY Startdate desc
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GW_GetAdByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GW_GetAdByID](@ID as INT)
AS
BEGIN
SELECT CategoryID=AdManagerCategoryID,Add_Src_Path, Name=Advertisement_Name,Redirect_URL,startdate=convert(varchar(12),startdate) ,
EndDate=convert(varchar(12),EndDate),Starttime,Endtime,Alt_text,Weight,openNewWindow
FROM ADManager_Ads WHERE ID=@ID
END

' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ADManager_Ads_ADManager_Category]') AND parent_object_id = OBJECT_ID(N'[dbo].[ADManager_Ads]'))
ALTER TABLE [dbo].[ADManager_Ads]  WITH CHECK ADD  CONSTRAINT [FK_ADManager_Ads_ADManager_Category] FOREIGN KEY([AdManagerCategoryID])
REFERENCES [dbo].[ADManager_Category] ([ID])
GO
ALTER TABLE [dbo].[ADManager_Ads] CHECK CONSTRAINT [FK_ADManager_Ads_ADManager_Category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ADManager_AdCompetitors_ADManager_Ads]') AND parent_object_id = OBJECT_ID(N'[dbo].[ADManager_AdCompetitors]'))
ALTER TABLE [dbo].[ADManager_AdCompetitors]  WITH CHECK ADD  CONSTRAINT [FK_ADManager_AdCompetitors_ADManager_Ads] FOREIGN KEY([AddID])
REFERENCES [dbo].[ADManager_Ads] ([ID])
GO
ALTER TABLE [dbo].[ADManager_AdCompetitors] CHECK CONSTRAINT [FK_ADManager_AdCompetitors_ADManager_Ads]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ADManager_AdCompetitors_ADManager_Ads1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ADManager_AdCompetitors]'))
ALTER TABLE [dbo].[ADManager_AdCompetitors]  WITH CHECK ADD  CONSTRAINT [FK_ADManager_AdCompetitors_ADManager_Ads1] FOREIGN KEY([CompetitorAddId])
REFERENCES [dbo].[ADManager_Ads] ([ID])
GO
ALTER TABLE [dbo].[ADManager_AdCompetitors] CHECK CONSTRAINT [FK_ADManager_AdCompetitors_ADManager_Ads1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ADManager_Banner_AdTracking_ADManager_Ads]') AND parent_object_id = OBJECT_ID(N'[dbo].[ADManager_Banner_AdTracking]'))
ALTER TABLE [dbo].[ADManager_Banner_AdTracking]  WITH CHECK ADD  CONSTRAINT [FK_ADManager_Banner_AdTracking_ADManager_Ads] FOREIGN KEY([Ad_Id])
REFERENCES [dbo].[ADManager_Ads] ([ID])
GO
ALTER TABLE [dbo].[ADManager_Banner_AdTracking] CHECK CONSTRAINT [FK_ADManager_Banner_AdTracking_ADManager_Ads]

GO
/*Sample Data for adds catagory*/
INSERT INTO [ADManager_Category]([Code],[Name],[Height],[Width])
SELECT t.* FROM 
(SELECT Code='TopBanner',[Name]='Top Banner Bar',Height=100,Width=100--Change banner sizes
UNION
SELECT Code='SkyScrapper',[Name]='Sky Scrapper Banner',Height=100,Width=100--Change banner sizes
UNION
SELECT Code='BottomTemplate1',[Name]='BottomTemplate 1',Height=100,Width=100--Change banner sizes
UNION
SELECT Code='BottomTemplate2',[Name]='BottomTemplate 2',Height=100,Width=100--Change banner sizes 
)  t
LEFT JOIN [ADManager_Category] m ON m.Code=t.COde 
WHERE m.ID IS NULL 

GO

/*Sample Data for adds*/
INSERT INTO [ADManager_Ads]([AdManagerCategoryID],[Advertisement_Name],[Add_Src_Path],[StartDate],[EndDate],[StartTime],[EndTime],[Redirect_URL] ,[Active] ,[Alt_Text] ,[Weight],[IsImageAdd],[OpenNewWindow])
SELECT t.* FROM
(SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='TopBanner'),[Advertisement_Name]='Sample1',[Add_Src_Path]='AddBanner/1.png',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 1' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='SkyScrapper'),[Advertisement_Name]='Sample2',[Add_Src_Path]='AddBanner/2.gif',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='BottomTemplate1'),[Advertisement_Name]='Sample3',[Add_Src_Path]='AddBanner/3.png',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='BottomTemplate2'),[Advertisement_Name]='Sample4',[Add_Src_Path]='AddBanner/4.jpg',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='TopBanner'),[Advertisement_Name]='Sample5',[Add_Src_Path]='AddBanner/2.gif',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='SkyScrapper'),[Advertisement_Name]='Sample6',[Add_Src_Path]='AddBanner/3.png',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='BottomTemplate1'),[Advertisement_Name]='Sample7',[Add_Src_Path]='AddBanner/4.jpg',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1
UNION
SELECT [AdManagerCategoryID]=(SELECT ID FROM [ADManager_Category] WHERE Code='BottomTemplate2'),[Advertisement_Name]='Sample8',[Add_Src_Path]='AddBanner/1.png',[StartDate]=GETDATE(),[EndDate]=DATEADD(month,1,GETDATE()),[StartTime]=1,[EndTime]=23.5,[Redirect_URL]='http://sample.com' ,[Active]=1 ,[Alt_Text]='Sample banner 2' ,[Weight]=5,[IsImageAdd]=1,[OpenNewWindow]=1) t
LEFT JOIN ADManager_Ads a ON a.[Advertisement_Name]=t.[Advertisement_Name] 
WHERE a.ID IS NULL
GO