-- CREATE THE TABLES
-- stores the report name
CREATE TABLE [dbo].[ReportHeader](
	[ReportID] [int] NOT NULL,
	[ReportName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

-- stores the fee name
CREATE TABLE [dbo].[FeeHeader](
	[FeeID] [int] NOT NULL,
	[FeeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

-- links the reports to the fees. a report can have multiple fees
CREATE TABLE [dbo].[FeeLink](
	[ReportID] [int] NULL,
	[FeeID] [int] NULL
) ON [PRIMARY]
GO

-- the fee details including the rule
CREATE TABLE [dbo].[FeeDetails](
	[FeeID] [int] NULL,
	[FeeDollar] [money] NULL CONSTRAINT [DF_a-Billing_GlobalFee_BG_FeeDollar]  DEFAULT ((0)),
	[FeeRule] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

-- user information
CREATE TABLE [dbo].[UserTable](
	[UserID] [int] NULL,
	[Handed] [varchar](5),
	[ProRate] [decimal](5,2)
) ON [PRIMARY]
GO

-- LOAD THE TABLES
INSERT INTO [dbo].[ReportHeader] (ReportID, ReportName)
VALUES (1, 'Management Fee Report')
INSERT INTO [dbo].[ReportHeader] (ReportID, ReportName)
VALUES (2, 'Misc Fee Report')

INSERT INTO [dbo].[FeeHeader] (FeeID, FeeName)
VALUES (1, 'Right-Handed Fee')
INSERT INTO [dbo].[FeeHeader] (FeeID, FeeName)
VALUES (2, 'Left-Handed Fee')
INSERT INTO [dbo].[FeeHeader] (FeeID, FeeName)
VALUES (3, 'Misc Fee')

INSERT INTO [dbo].[FeeLink] (ReportID, FeeID)
VALUES (1,1)
INSERT INTO [dbo].[FeeLink] (ReportID, FeeID)
VALUES (1,2)
INSERT INTO [dbo].[FeeLink] (ReportID, FeeID)
VALUES (2,3)

-- this fee is only applied to right-handed users
INSERT INTO [dbo].[FeeDetails] (FeeID, FeeDollar, FeeRule)
VALUES (1, 20.00, '(UserTable.Handed = ''LEFT'')')
-- this fee is only applied to left-handed users
INSERT INTO [dbo].[FeeDetails] (FeeID, FeeDollar, FeeRule)
VALUES (2, 10.00, '(UserTable.Handed = ''RIGHT'')')
-- this fee is applied to everyone
INSERT INTO [dbo].[FeeDetails] (FeeID, FeeDollar, FeeRule)
VALUES (3, 5.00, null)

INSERT INTO [dbo].[UserTable] (UserID, Handed, ProRate)
VALUES (1, 'LEFT', 1.00)
INSERT INTO [dbo].[UserTable] (UserID, Handed, ProRate)
VALUES (2, 'RIGHT', 1.50)
INSERT INTO [dbo].[UserTable] (UserID, Handed, ProRate)
VALUES (3, 'LEFT', 0.50)
INSERT INTO [dbo].[UserTable] (UserID, Handed, ProRate)
VALUES (4, 'RIGHT', 1.00)
INSERT INTO [dbo].[UserTable] (UserID, Handed, ProRate)
VALUES (5, 'LEFT', 1.00)

-- STORED PROCEDURE
CREATE PROCEDURE [dbo].[BillGenerate]
	-- Add the parameters for the stored procedure here
	@ReportId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        declare @userid int
	declare @prorate decimal(5,2)

	declare @feedollar decimal(16,2)
	declare @feerule varchar(max)
	declare @sql varchar(max)

	-- temp table to hold the results
	CREATE TABLE #FeeReport (
	UserID int,
        BillAmount money 
	)

	-- select all the users to bill
	DECLARE c CURSOR FOR 
	select UserID, ProRate from UserTable 
	OPEN c
	FETCH NEXT FROM c 
	INTO @userid, @prorate

	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- select all the fees for the report we are generating
		DECLARE d CURSOR FOR
		SELECT FD.FeeDollar, FD.FeeRule FROM FeeDetails FD inner join
		FeeLink FL on FD.FeeID = FL.FeeID inner join
		ReportHeader RH on FL.ReportID = RH.ReportID
		WHERE RH.ReportID = @ReportId
		open d
		FETCH NEXT FROM d
		INTO @feedollar, @feerule
		
		WHILE @@FETCH_STATUS = 0
		BEGIN

			-- build the dynamic sql. if the rule is null (meaning it applies to everyone)
			-- I use '1=1' to always make it a true condition
			set @sql = 'INSERT INTO #FeeReport(UserID, BillAmount) Select UserId, '
			set @sql = @sql + '(ProRate * ' + convert(varchar, @feedollar)+ ') as ToBill ' 
			set @sql = @sql + ' from UserTable'
			set @sql = @sql + ' where UserID = ' + convert(varchar,@userid) +' AND ('
			set @sql = @sql + isnull(@feerule, '1=1')
			set @sql = @sql + ')'

			-- print for testing
			print (@sql)
			exec (@sql)

			FETCH NEXT FROM d
			INTO @feedollar, @feerule
		
		END
		CLOSE d
		DEALLOCATE d

		FETCH NEXT FROM c 
		INTO @userid, @prorate
	END 

	CLOSE c
	DEALLOCATE c

-- create the output. I need to sum the results and only have one record per user
SELECT UserId, Sum(BillAmount) as BillAmount
FROM (SELECT DISTINCT * from #FeeReport) As FeeTable
GROUP BY UserID


END
GO

-- TEST the process by executing these:
exec BillGenerate 1
exec BillGenerate 2

Declare @table Table (RowId Int identity(1,1), Userid int, Prorate [decimal](5,2), Processed bit)
Insert into @table select UserID, ProRate, 0 from UserTable 

Declare @rowid int
WHILE EXISTS (SELECT * FROM @table Where Processed = 0)
	BEGIN
		Select @rowid = MIN(rowid)
		FROM @table
		WHERE Processed = 0
		
		Select @userid = Userid
			,@prorate = Prorate
		FROM @table
		WHERE rowid = @rowid
		
		-- Do the processing
		
		update @table 
		set Processed = 1
		where rowid = @rowid
	END