IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'IncomeCodes' AND type = 'P')
   DROP PROCEDURE IncomeCodes
GO

CREATE PROCEDURE IncomeCodes AS
SET NOCOUNT ON
DECLARE @income_code int, @totalcost money, @eyeglass money, @expenditure money
DECLARE income_cursor CURSOR FOR 
	SELECT tabEGPurchase.Code
	FROM tabEGPurchase INNER JOIN
        tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
        tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN
        tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
	WHERE (DATEDIFF(Month, tabEarningRecords.DateEarned, GETDATE()) > 0)
	GROUP BY tabEGPurchase.Code

UPDATE  tabEarningRecords SET Expenditure = 0
	WHERE (Sequence IN
		(SELECT tabEarningRecords.Sequence
			FROM tabEGPurchase INNER JOIN
			tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
			tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence))
	
OPEN income_cursor

FETCH NEXT FROM income_cursor 
INTO @income_code

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @totalcost=ISNULL(tabFrameList.BuyingPrice, 0) + ISNULL(tabEGPurchase.MTC, 0) FROM tabEGPurchase LEFT OUTER JOIN
	tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
	WHERE tabEGPurchase.Code = @income_code

	DECLARE income2_cursor CURSOR FOR 
		SELECT  tabEarningRecords.EyeGlass
		FROM tabEGPurchase INNER JOIN
		tabEGPayments ON tabEGPurchase.Code = tabEGPayments.Code INNER JOIN
		tabEarningRecords ON tabEGPayments.IncomeCode = tabEarningRecords.Sequence LEFT OUTER JOIN
		tabFrameList ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode
		WHERE (DateDiff(Month, tabEarningRecords.DateEarned, GETDATE()) > 0) AND (tabEGPurchase.Code = @income_code)

	   OPEN income2_cursor
	   FETCH NEXT FROM income2_cursor INTO @eyeglass
	
	   WHILE @@FETCH_STATUS = 0
	   BEGIN		
		If (@totalcost >= @eyeglass)
			BEGIN
				SET @totalcost = @totalcost - @eyeglass
				SET @expenditure = @eyeglass
			END
		Else
			BEGIN
				SET @expenditure = @totalcost
				SET @totalcost = 0
			END
		
		--UPDATE tabEarningRecords SET Expenditure = @expenditure WHERE 
		UPDATE tabEarningRecords SET Expenditure = @expenditure
		WHERE CURRENT OF income2_cursor

		PRINT 'eyeglass    : ' + Convert(NVarChar,@eyeglass)
		PRINT 'expenditure : ' + Convert(NVarChar,@expenditure)
		PRINT 'remaining   : ' +  Convert(NVarChar,@totalcost)
		PRINT ''
		
		IF (@totalcost = 0) BREAK

		FETCH NEXT FROM income2_cursor INTO @eyeglass	   
	   END

   CLOSE income2_cursor
   DEALLOCATE income2_cursor
   
   -- Get the next author.
   FETCH NEXT FROM income_cursor 
   INTO @income_code
END

CLOSE income_cursor
DEALLOCATE income_cursor
GO