ÿþIF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_StevensAddPassReferralDiscounts') AND type='P')

	DROP PROCEDURE dbo.sp_StevensAddPassReferralDiscounts

GO



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE PROCEDURE sp_StevensAddPassReferralDiscounts

AS

BEGIN



-- Dennis Kelly <dennis.kelly@stevenspass.com>

-- Copyright (C) 2010 Dennis Kelly

-- Copyright (C) 2010 Stevens Pass

--

-- This program is free software; you can redistribute it and/or

-- modify it under the terms of the GNU General Public License

-- as published by the Free Software Foundation; either version 2

-- of the License, or (at your option) any later version.

--

-- This program is distributed in the hope that it will be useful,

-- but WITHOUT ANY WARRANTY; without even the implied warranty of

-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

-- GNU General Public License for more details.

--

-- You should have received a copy of the GNU General Public License

-- along with this program; if not, write to the Free Software

-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.



SET NOCOUNT ON



DECLARE @StartDate DATETIME

DECLARE @DiscountCodePrefix NVARCHAR(32)

DECLARE @DiscountTemplateCode NVARCHAR(255)

DECLARE @OperatorID NVARCHAR(32)



-- UPDATE SEASONALLY

SET @StartDate = '2010-08-15'  -- Start date for transactions

SET @DiscountCodePrefix = 'PRP-2010-11-'

SET @DiscountTemplateCode = '90000'

SET @OperatorID = 'BULK'



-- Customers eligible for the referral program

CREATE TABLE #IPCode (

	IPCode				INT

)



INSERT INTO #IPCode

SELECT DISTINCT tlip.IPCode

FROM TransactionHeader th WITH(NOLOCK) 

JOIN TransactionLine tl WITH(NOLOCK)

	ON tl.TransactionID = th.TransactionID

		AND tl.LineStatusCode IN (1, 3)

		AND tl.LineTypeCode = 1

JOIN TransactionLineIP tlip WITH(NOLOCK)

	ON tlip.TransactionID = tl.TransactionID

		AND tlip.TransactionLine = tl.TransactionLine 

WHERE th.TransactionTypeCode = 1

	AND th.TransactionDate > @StartDate

	AND tl.ProductHeaderCode IN (

		SELECT ph.ProductHeaderCode

		FROM ProductHeader ph WITH(NOLOCK)

		JOIN ProductHeaderProductDisplayCategory phpdc WITH(NOLOCK)

			ON ph.ProductHeaderCode = phpdc.ProductHeaderCode

				AND phpdc.StatusCode = 1

		WHERE phpdc.ProductDisplayCategoryCode IN (10300, 10290, 10320) -- Pass Renewal Products

		--WHERE ProductHeaderCode = '10958' -- College Pass

	)

	AND NOT EXISTS (

		SELECT *

		FROM Discount WITH(NOLOCK)

		WHERE DiscountCode = @DiscountCodePrefix + CAST(tlip.IPCode AS NVARCHAR)

	)



-- Add discounts (Referral codes)

INSERT INTO Discount (

	DiscountCode,

	StatusCode,

	DisplayOrder,

	Description,

	DisplayInd,

	SecurityLevel,

	DiscountTypeCode,

	DiscountCategoryCode,

	NetAccountingInd,

	DefaultEarnedSegment1,

	DefaultEarnedSegment2,

	DefaultEarnedSegment3,

	DefaultEarnedSegment4,

	DefaultUnearnedSegment1,

	DefaultUnearnedSegment2,

	DefaultUnearnedSegment3,

	DefaultUnearnedSegment4,

	ReceiptLabel,

	CommentText,

	DiscountReference,

	OperatorID,

	UpdateDate)

SELECT 

	@DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	d.StatusCode,

	d.DisplayOrder,

	'Referral Code: ' + @DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	d.DisplayInd,

	d.SecurityLevel,

	d.DiscountTypeCode,

	d.DiscountCategoryCode,

	d.NetAccountingInd,

	d.DefaultEarnedSegment1,

	d.DefaultEarnedSegment2,

	d.DefaultEarnedSegment3,

	d.DefaultEarnedSegment4,

	d.DefaultUnearnedSegment1,

	d.DefaultUnearnedSegment2,

	d.DefaultUnearnedSegment3,

	d.DefaultUnearnedSegment4,

	'Referral Code: ' + @DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	CommentText,

	DiscountReference,

	@OperatorID,

	GETDATE()

FROM #IPCode ip 

JOIN Discount d WITH(NOLOCK)

	ON d.DiscountCode = @DiscountTemplateCode



-- Add sale locations to discount

INSERT INTO DiscountSaleLocation (

	DiscountCode,

	SaleLocationCode,

	OperatorID,

	UpdateDate)

SELECT 	

	@DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	dsl.SaleLocationCode,

	@OperatorID,

	GETDATE()

FROM #IPCode ip

JOIN DiscountSaleLocation dsl WITH(NOLOCK)

	ON dsl.DiscountCode = @DiscountTemplateCode



-- Add discount to product headers

INSERT INTO ProductHeaderDiscount (

	ProductHeaderCode,

	DiscountCode,

	EffectiveDate,

	ExpirationDate,

	CalculationMethodCode,

	DiscountAmount,

	OperatorID,

	UpdateDate)

SELECT 

	phd.ProductHeaderCode,

	@DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	phd.EffectiveDate,

	phd.ExpirationDate,

	phd.CalculationMethodCode,

	phd.DiscountAmount,

	@OperatorID,

	GETDATE()

FROM #IPCode ip

JOIN ProductHeaderDiscount phd WITH(NOLOCK)

	ON phd.DiscountCode = @DiscountTemplateCode

	

INSERT INTO Stevens.dbo.PassReferralDiscount (

	IPCode,

	DiscountCode,

	EmailedInd,

	UpdateDate)

SELECT

	ip.IPCode,

	@DiscountCodePrefix + CAST(ip.IPCode AS NVARCHAR),

	0,

	GETDATE()

FROM #IPCode ip



DROP TABLE #IPCode



END