Monitor your Web Services usage via .NET SOAP Extensions

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WSDM_UserSubscription_WSDM_Services]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WSDM_UserSubscription] DROP CONSTRAINT FK_WSDM_UserSubscription_WSDM_Services
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WSDM_UserSubscription_WSDM_SubscriptionType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WSDM_UserSubscription] DROP CONSTRAINT FK_WSDM_UserSubscription_WSDM_SubscriptionType
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WSDM_ServiceUsageTracking_WSDM_UserSubscription]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WSDM_ServiceUsageTracking] DROP CONSTRAINT FK_WSDM_ServiceUsageTracking_WSDM_UserSubscription
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WSDM_UserSubscription_WSDM_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WSDM_UserSubscription] DROP CONSTRAINT FK_WSDM_UserSubscription_WSDM_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_GetLatestUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_GetLatestUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_PopulateStaticTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_PopulateStaticTables]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_ProcessUserSubscriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_ProcessUserSubscriptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_RegisterUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_RegisterUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_SendRegistrationMail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_SendRegistrationMail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_SubscribeUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_SubscribeUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_UsageAccounting]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_UsageAccounting]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_WSDM_ValidateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_WSDM_ValidateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_Configuration]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_Configuration]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_LoggerTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_LoggerTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_ServiceUsageTracking]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_ServiceUsageTracking]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_Services]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_Services]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_SubscriptionType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_SubscriptionType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_UserSubscription]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_UserSubscription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WSDM_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WSDM_Users]
GO

CREATE TABLE [dbo].[WSDM_Configuration] (
	[TrialVersionDays] [int] NOT NULL ,
	[TrialVersionCalls] [int] NOT NULL ,
	[PromoStartHour] [int] NOT NULL ,
	[PromoEndHour] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_LoggerTable] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[LogTime] [datetime] NULL ,
	[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_ServiceUsageTracking] (
	[UserSubscriptionID] [int] NOT NULL ,
	[TimeUsed] [datetime] NOT NULL ,
	[Success] [bit] NOT NULL ,
	[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_Services] (
	[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
	[ServiceName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ServiceDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_SubscriptionType] (
	[SubscriptionTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[SubscriptionType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SubscriptionSubType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SubscriptionDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_UserSubscription] (
	[UserSubscriptionID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[ServiceID] [int] NOT NULL ,
	[SubscriptionTypeID] [int] NOT NULL ,
	[SubscribedOn] [datetime] NOT NULL ,
	[SubscriptionParam] [int] NULL ,
	[Active] [bit] NOT NULL ,
	[SubscriptionDisabledAt] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WSDM_Users] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RegistrationKey] [uniqueidentifier] NOT NULL ,
	[RegistrationDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WSDM_Services] WITH NOCHECK ADD 
	CONSTRAINT [PK_WSDM_Services] PRIMARY KEY  CLUSTERED 
	(
		[ServiceID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WSDM_SubscriptionType] WITH NOCHECK ADD 
	CONSTRAINT [PK_WSDM_SubscriptionType] PRIMARY KEY  CLUSTERED 
	(
		[SubscriptionTypeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WSDM_UserSubscription] WITH NOCHECK ADD 
	CONSTRAINT [PK_WSDM_UserSubscription] PRIMARY KEY  CLUSTERED 
	(
		[UserSubscriptionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WSDM_Users] WITH NOCHECK ADD 
	CONSTRAINT [PK_WSDM_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_WSDM_ServiceUsageTracking] ON [dbo].[WSDM_ServiceUsageTracking]([UserSubscriptionID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WSDM_LoggerTable] ADD 
	CONSTRAINT [DF__WSDM_Logg__LogTi__4C0144E4] DEFAULT (getdate()) FOR [LogTime]
GO

ALTER TABLE [dbo].[WSDM_Users] ADD 
	CONSTRAINT [IX_WSDM_First_Last_Email] UNIQUE  NONCLUSTERED 
	(
		[FirstName],
		[LastName],
		[EmailAddress]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[WSDM_ServiceUsageTracking] ADD 
	CONSTRAINT [FK_WSDM_ServiceUsageTracking_WSDM_UserSubscription] FOREIGN KEY 
	(
		[UserSubscriptionID]
	) REFERENCES [dbo].[WSDM_UserSubscription] (
		[UserSubscriptionID]
	)
GO

ALTER TABLE [dbo].[WSDM_UserSubscription] ADD 
	CONSTRAINT [FK_WSDM_UserSubscription_WSDM_Services] FOREIGN KEY 
	(
		[ServiceID]
	) REFERENCES [dbo].[WSDM_Services] (
		[ServiceID]
	),
	CONSTRAINT [FK_WSDM_UserSubscription_WSDM_SubscriptionType] FOREIGN KEY 
	(
		[SubscriptionTypeID]
	) REFERENCES [dbo].[WSDM_SubscriptionType] (
		[SubscriptionTypeID]
	),
	CONSTRAINT [FK_WSDM_UserSubscription_WSDM_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[WSDM_Users] (
		[UserID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC proc_WSDM_GetLatestUsers
AS
BEGIN
	
	SELECT 	U.EmailAddress, S.ServiceName, ST.SubscriptionType, ST.SubscriptionSubType,
		US.SubscribedOn, US.SubscriptionParam, US.Active
	FROM WSDM_Users U
	INNER JOIN WSDM_UserSubscription US
	ON U.UserID = US.UserID
	INNER JOIN WSDM_Services S
	ON US.ServiceID = S.ServiceID
	INNER JOIN WSDM_SubscriptionType ST
	ON ST.SubscriptionTypeID = US.SubscriptionTypeID
	WHERE US.Active = 1
	ORDER BY US.SubscribedOn DESC

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE         PROCEDURE proc_WSDM_PopulateStaticTables
AS
BEGIN

	-- Services 
	INSERT INTO WSDM_Services ( ServiceName, ServiceDescription )
	VALUES ('GetStockQuote', 'This service provides current stock quote value.')

	INSERT INTO WSDM_Services ( ServiceName, ServiceDescription )
	VALUES ('GetTemperatureByZipCode', 'This service provides temperature by zip code.')

	INSERT INTO WSDM_Services ( ServiceName, ServiceDescription )
	VALUES ('GetCurrencyExchangeRate', 'This service provides current exchange rate for foreign currency against US$.')


	-- Substription types
	INSERT INTO WSDM_SubscriptionType (SubscriptionType, SubscriptionSubType, SubscriptionDescription)
	VALUES ('Trial', 'None', 'Use of trial version of service for X number f days with Y calls at most per method')


	INSERT INTO WSDM_SubscriptionType (SubscriptionType, SubscriptionSubType, SubscriptionDescription)
	VALUES ('Full', 'Standard', 'Use of full version of service for Y number of calls')

	INSERT INTO WSDM_SubscriptionType (SubscriptionType, SubscriptionSubType, SubscriptionDescription)
	VALUES ('Full','Premium', 'Use of Full version of service for X number of days')


	INSERT INTO WSDM_SubscriptionType (SubscriptionType, SubscriptionSubType, SubscriptionDescription)
	VALUES ('Full', 'Promo', 'Use of full version of service for Y number of calls during pre-declared hours')

	-- Configuration parameters
	INSERT INTO WSDM_Configuration (TrialVersionDays, TrialVersionCalls, PromoStartHour, PromoEndHour)
	VALUES ( 3, 1000, 18, 23)

END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE  PROC proc_WSDM_ProcessUserSubscriptions
AS
BEGIN

	DECLARE @NumOfDaysAllowedInTrial		INT
	DECLARE @NumOfCallsAllowedInTrial		INT

	SELECT @NumOfDaysAllowedInTrial = TrialVersionDays, @NumOfCallsAllowedInTrial = TrialVersionCalls
	FROM WSDM_Configuration
	
	UPDATE US
	SET Active = 0, SubscriptionDisabledAt = GetUTCDate()
	FROM WSDM_UserSubscription US
	INNER JOIN 
		(
		-- Get current active subscription under trial mode with either number of allowed calls 
		-- or number of days allowed in trial mode reached
		SELECT UserSubscriptionID
		FROM
			(
			SELECT UT.UserSubscriptionID, T.SubscribedOn, COUNT(*) CallsMadeSoFar, 
				DATEDIFF( dd, T.SubscribedOn, GetUTCDate() ) DaysUsedSoFar
			FROM WSDM_ServiceUsageTracking UT
			INNER JOIN
				(
				SELECT 	US.UserSubscriptionID, ST.SubscriptionType, ST.SubscriptionSubType, 
					US.SubscribedOn, US.SubscriptionParam, US.Active
				FROM WSDM_UserSubscription US
				INNER JOIN WSDM_SubscriptionType ST
				ON ST.SubscriptionTypeID = US.SubscriptionTypeID
				WHERE US.Active = 1 AND ST.SubscriptionType = 'TRIAL'
				) T
			ON UT.UserSubscriptionID = T.UserSubscriptionID
			GROUP BY UT.UserSubscriptionID, T.SubscribedOn
			) UG
		WHERE CallsMadeSoFar > @NumOfCallsAllowedInTrial OR DaysUsedSoFar > @NumOfDaysAllowedInTrial

		-- Get current active subscription under full mode with either standard or promo sub-type
		-- and number of calls subscribed for is reached
		UNION ALL
		SELECT UserSubscriptionID
		FROM
			(
			SELECT UT.UserSubscriptionID,  FSP.SubscriptionParam, COUNT(*) CallsMadeSoFar 
			FROM WSDM_ServiceUsageTracking UT
			INNER JOIN
				(
				SELECT 	US.UserSubscriptionID, US.SubscribedOn, US.SubscriptionParam
				FROM WSDM_UserSubscription US
				INNER JOIN WSDM_SubscriptionType ST
				ON ST.SubscriptionTypeID = US.SubscriptionTypeID
				WHERE US.Active = 1 AND ST.SubscriptionType = 'FULL' 
				AND ( ST.SubscriptionSubType = 'Standard' OR ST.SubscriptionSubType = 'Promo' )
				) FSP
			ON UT.UserSubscriptionID = FSP.UserSubscriptionID
			GROUP BY UT.UserSubscriptionID, FSP.SubscriptionParam
			) UG
		WHERE CallsMadeSoFar >=  SubscriptionParam

		-- Get current active subscription under full mode with premium service
		-- and number of days subscribed for is reached
		UNION ALL
		SELECT UserSubscriptionID
		FROM
			(
			SELECT 	US.UserSubscriptionID, DATEDIFF( dd, US.SubscribedOn, GetUTCDate() ) DaysUsedSoFar,
				US.SubscriptionParam DaysAllowed
			FROM WSDM_UserSubscription US
			INNER JOIN WSDM_SubscriptionType ST
			ON ST.SubscriptionTypeID = US.SubscriptionTypeID
			WHERE US.Active = 1 AND ST.SubscriptionType = 'FULL' 
			AND ST.SubscriptionSubType = 'Premium'
			) UG
		WHERE DaysUsedSoFar >=  DaysAllowed
		) EX
	ON US.UserSubscriptionID = EX.UserSubscriptionID


END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE     PROCEDURE proc_WSDM_RegisterUser
	@FirstName		VARCHAR(50),
	@LastName		VARCHAR(50),
	@EMailAddress		VARCHAR(50)
AS
BEGIN

	INSERT INTO WSDM_Users (FirstName, LastName, EmailAddress, RegistrationKey, RegistrationDate)
	VALUES ( @FirstName, @LastName, @EMailAddress, NewID(), GetUTCDate() )

	SELECT @@IDENTITY

END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE proc_WSDM_SendRegistrationMail
	@UserID		INT
AS
BEGIN

	DECLARE @Subject	VARCHAR(100)
	DECLARE @Message	VARCHAR(2000)
	DECLARE @Email		VARCHAR(100)
	DECLARE @FirstName	VARCHAR(50)
	DECLARE @LastName	VARCHAR(50)
	DECLARE @RegKey		UNIQUEIDENTIFIER


	SELECT @FirstName = FirstName, @LastName = LastName, @Email = EmailAddress, @RegKey = RegistrationKey
	FROM WSDM_Users
	WHERE UserID = @UserID


	SET @Subject = 'Registration for using Web-Services'
	SET @Message = 'Hi ' + @LastName +  ', ' + @FirstName + CHAR(13) + CHAR(10)
	SET @Message = @Message + 'You have been successfully registered. ' + CHAR(13) + CHAR(10)
	SET @Message = @Message + 'You Registration key is  ' + CONVERT(VARCHAR(50), @RegKey) + CHAR(13) + CHAR(10)
	SET @Message = @Message + 'Regards ' + CHAR(13) + CHAR(10)
	SET @Message = @Message + 'Web Master ' + CHAR(13) + CHAR(10)
	
	--PRINT @Message

	EXEC master.dbo.xp_sendmail @recipients = @Email, 
	   @message = @Message,
	   @subject = @Subject
	


END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE      PROCEDURE proc_WSDM_SubscribeUser
	@RegKey				VARCHAR(50),
	@ServiceName			VARCHAR(50),
	@SubscriptionType		VARCHAR(15),
	@SubscriptionSubType		VARCHAR(10),
	@SubscriptionParam		INT
AS
BEGIN
	DECLARE @UserID			INT
	DECLARE @ServiceID		INT
	DECLARE @SubscriptionTypeID	INT
	
	DECLARE @LogMessage	VARCHAR(500)
	SET @LogMessage = 'proc_WSDM_SubscribeUser: Subscription request for RegKey = ' + @RegKey + ', ServiceName = ' + @ServiceName
	SET @LogMessage = @LogMessage + ', SubscriptionType = ' + @SubscriptionType + ', SubscriptionSubType = ' + @SubscriptionSubType 
	SET @LogMessage = @LogMessage + ', SubscriptionParam = ' + CONVERT(VARCHAR(5), @SubscriptionParam)
	INSERT INTO WSDM_LoggerTable (Description) VALUES (@LogMessage)

	-- Get User
	SELECT @UserID = UserID
	FROM WSDM_Users
	WHERE CONVERT(VARCHAR(50), RegistrationKey) = @RegKey

	PRINT @UserID
	IF @UserID IS NULL
	BEGIN
		RAISERROR('Invalid Registration Key', 18, 1, 18)
		PRINT 'Invalid User'
		 RETURN 5001
	END

	-- Get Service Type
	SELECT @ServiceID = ServiceID
	FROM WSDM_Services
	WHERE ServiceName = @ServiceName

	PRINT @ServiceID
	IF @ServiceID IS NULL
	BEGIN
		PRINT 'Invalid Service'
		 RETURN 5002
	END

	-- Get subscription ID
	SELECT @SubscriptionTypeID = SubscriptionTypeID
	FROM WSDM_SubscriptionType
	WHERE SubscriptionType = @SubscriptionType AND SubscriptionSubType = @SubscriptionSubType 

	PRINT @SubscriptionTypeID
	IF @SubscriptionTypeID IS NULL
	BEGIN
		PRINT 'Invalid SubscriptionTypeID'
		 RETURN 5003
	END


	DECLARE @PreviousID INT

	SELECT @PreviousID = UserSubscriptionID 
	FROM WSDM_UserSubscription
	WHERE UserID = @UserID AND ServiceID = @ServiceID AND SubscriptionTypeID = @SubscriptionTypeID AND Active = 1

	SET @LogMessage = 'previous service id found to be ' + CONVERT(VARCHAR(5), @PreviousID)
	INSERT INTO WSDM_LoggerTable (Description) VALUES (@LogMessage)

	IF @PreviousID IS NOT NULL
	BEGIN
		UPDATE WSDM_UserSubscription
		SET Active = 0, SubscriptionDisabledAt = GetUTCDate()
		WHERE UserSubscriptionID = @PreviousID
	END

	INSERT INTO WSDM_UserSubscription ( UserID, ServiceID, SubscriptionTypeID, SubscribedOn,  SubscriptionParam, Active)
	VALUES (@UserID, @ServiceID, @SubscriptionTypeID, GetUTCDate(),  @SubscriptionParam, 1)

	SELECT @@IDENTITY AS SubscriptionID

END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE          PROCEDURE proc_WSDM_UsageAccounting
	@ServiceName			VARCHAR(25),
	@RegKey				VARCHAR(50)
AS
BEGIN

	DECLARE @LogMessage	VARCHAR(100)
	SET @LogMessage = 'Accouting Request for RegKey = ' + @RegKey + ', ServiceName = ' + @ServiceName
	INSERT INTO WSDM_LoggerTable (Description) VALUES (@LogMessage)


	DECLARE @ServiceID			INT
	DECLARE @UserID				INT
	DECLARE @UserSubscriptionID		INT
	DECLARE @SubscriptionTypeID		INT
	DECLARE @SubscriptionType		VARCHAR(10)
	DECLARE @SubscriptionSubType		VARCHAR(10)
	DECLARE @SubscriptionStartDate		DATETIME
	DECLARE @SubscriptionParam		INT


	-- Get UserID
	SELECT @UserID = UserID 
	FROM WSDM_Users
	WHERE CONVERT(VARCHAR(50), RegistrationKey) = @RegKey

	IF @UserID IS NULL
	BEGIN
		-- Invalid license key
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('Invalid User')
		RETURN 5001
	END

	-- Get ServiceID
	SELECT @ServiceID = ServiceID 
	FROM WSDM_Services
	WHERE ServiceName = @ServiceName

	IF @ServiceID IS NULL
	BEGIN
		-- Invalid license key
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('Invalid ServiceName')
		RETURN 5002
	END

	-- See if user for this particular method has a valid subscription
	SELECT @UserSubscriptionID = UserSubscriptionID
	FROM WSDM_UserSubscription
	WHERE UserID = @Userid AND ServiceID = @ServiceID AND Active = 1

	IF @UserSubscriptionID IS NULL
	BEGIN
		-- User does not have any valid subscription
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('No valid subscription exists')
		RETURN 5004
	END

	INSERT INTO WSDM_ServiceUsageTracking
	VALUES ( @UserSubscriptionID, CURRENT_TIMESTAMP, 1, 'Success')

END














GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE           PROCEDURE proc_WSDM_ValidateUser
	@ServiceName			VARCHAR(25),
	@RegKey				VARCHAR(50)
AS
BEGIN
	DECLARE @ServiceID			INT
	DECLARE @UserID				INT
	DECLARE @UserSubscriptionID		INT
	DECLARE @SubscriptionTypeID		INT
	DECLARE @SubscriptionType		VARCHAR(10)
	DECLARE @SubscriptionSubType		VARCHAR(10)
	DECLARE @SubscriptionStartDate		DATETIME
	DECLARE @SubscriptionParam		INT
	DECLARE @NumOfCallsMadeSoFar		INT
	DECLARE @NumOfHoursUsedSoFar		INT		

	DECLARE @bExpired			BIT

	SET @bExpired = 0

	DECLARE @LogMessage	VARCHAR(100)
	SET @LogMessage = 'Validation Request for RegKey = ' + @RegKey + ', ServiceName = ' + @ServiceName
	INSERT INTO WSDM_LoggerTable (Description) VALUES (@LogMessage)

	-- Get UserID
	SELECT @UserID = UserID 
	FROM WSDM_Users
	WHERE CONVERT(VARCHAR(50), RegistrationKey) = @RegKey

	IF @UserID IS NULL
	BEGIN
		-- Invalid license key
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('Invalid User')
		RETURN 5001
	END

	-- Get ServiceID
	SELECT @ServiceID = ServiceID 
	FROM WSDM_Services
	WHERE ServiceName = @ServiceName

	IF @ServiceID IS NULL
	BEGIN
		-- Invalid license key
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('Invalid ServiceName')
		RETURN 5002
	END


	SELECT @UserSubscriptionID = UserSubscriptionID, @SubscriptionTypeID = SubscriptionTypeID
	FROM WSDM_UserSubscription
	WHERE UserID = @Userid AND ServiceID = @ServiceID AND Active = 1

	IF @UserSubscriptionID IS NULL
	BEGIN
		-- user not subscribed for this service
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('Invalid subscription')
		RETURN 5003
	END
	
	SELECT @SubscriptionType = SubscriptionType, @SubscriptionSubType = SubscriptionSubType 
	FROM WSDM_SubscriptionType
	WHERE SubscriptionTypeID = @SubscriptionTypeID


	IF ( @SubscriptionType = 'TRIAL' )
	BEGIN
		PRINT 'TRIAL VERSION'

		--Read Trial Configuration
		DECLARE @TrialDaysLimit		INT
		DECLARE @TrialCallsLimit	INT

		SELECT @TrialDaysLimit = TrialVersionDays, @TrialCallsLimit = TrialVersionCalls
		FROM WSDM_Configuration

		-- Number of days past for user so far in trial mode
		SELECT @NumOfHoursUsedSoFar = DATEDIFF(hh, SubscribedOn, GetUTCDate() )
		FROM WSDM_UserSubscription
		WHERE UserSubscriptionID = @UserSubscriptionID 
		
		print @NumOfHoursUsedSoFar

		-- Number of calls user have made so far for this method
		SELECT @NumOfCallsMadeSoFar = COUNT(*) 
		FROM WSDM_ServiceUsageTracking
		WHERE  UserSubscriptionID = @UserSubscriptionID AND Success = 1

		IF @NumOfHoursUsedSoFar > @TrialDaysLimit*24 OR @NumOfCallsMadeSoFar > @TrialCallsLimit
		BEGIN
			SET @bExpired = 1
		END
		
	END
	ELSE IF ( @SubscriptionType = 'FULL' )
	BEGIN
		DECLARE @NumOfCallsAllowed 	INT
		DECLARE @NumOfHoursAllowed	INT
		DECLARE @PromoStartHour		INT
		DECLARE @PromoEndHour		INT

		IF @SubscriptionSubType = 'Standard' 
		BEGIN
			-- Calls allowed
			SELECT @NumOfCallsAllowed = SubscriptionParam
			FROM WSDM_UserSubscription
			WHERE UserSubscriptionID = @UserSubscriptionID 

			-- Calls made so far
			SELECT @NumOfCallsMadeSoFar = COUNT(*) 
			FROM WSDM_ServiceUsageTracking
			WHERE  UserSubscriptionID = @UserSubscriptionID AND Success = 1
		
			IF ( @NumOfCallsMadeSoFar >= @NumOfCallsAllowed )
			BEGIN
				SET @bExpired = 1
			END

		END 
		ELSE IF @SubscriptionSubType = 'Premium' 
		BEGIN
			-- Calls allowed
			SELECT @NumOfHoursAllowed = SubscriptionParam*24
			FROM WSDM_UserSubscription
			WHERE UserSubscriptionID = @UserSubscriptionID 

			-- Days used so far
			SELECT @NumOfHoursUsedSoFar = DATEDIFF(hh, SubscribedOn, GetUTCDate() )
			FROM WSDM_UserSubscription
			WHERE UserSubscriptionID = @UserSubscriptionID 

			IF ( @NumOfHoursUsedSoFar >= @NumOfHoursAllowed  )
			BEGIN

				SET @bExpired = 1
			END

		END 
		ELSE IF @SubscriptionSubType = 'Promo' 
		BEGIN
			SELECT @PromoStartHour = PromoStartHour, @PromoEndHour = PromoEndHour 
			FROM WSDM_Configuration

			-- First check if current hour falls in promo usage hours
			IF ( DATEPART(hh, GetUTCDate()) BETWEEN @PromoStartHour AND @PromoEndHour ) 
			BEGIN
				-- Calls allowed
				SELECT @NumOfCallsAllowed = SubscriptionParam
				FROM WSDM_UserSubscription
				WHERE UserSubscriptionID = @UserSubscriptionID 
	
				-- Calls made so far
				SELECT @NumOfCallsMadeSoFar = COUNT(*) 
				FROM WSDM_ServiceUsageTracking
				WHERE  UserSubscriptionID = @UserSubscriptionID AND Success = 1
			
				IF ( @NumOfCallsMadeSoFar >= @NumOfCallsAllowed )
				BEGIN
					SET @bExpired = 1
				END
			END
			ELSE
			BEGIN
				SET @bExpired = 1
			END
		END 
	END



	IF @bExpired = 1
	BEGIN
		-- User subscription has expired
		RETURN 5005
	END
	ELSE
	BEGIN
		-- Valid user
		INSERT INTO WSDM_LoggerTable (Description) VALUES ('User validated')
		RETURN 0
	END


END














GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- Script generated on 12/5/2004 11:57 PM
-- By: sa
-- Server: (LOCAL)

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Process User Subscriptions')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Process User Subscriptions'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Process User Subscriptions' 
    SELECT @JobID = NULL
  END 

BEGIN 
  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Process User Subscriptions', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Process User Subscriptions', @command = N'EXEC proc_WSDM_ProcessUserSubscriptions', @database_name = N'WSDM_DB', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Process User Subscriptions', @enabled = 1, @freq_type = 4, @active_start_date = 20041205, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: