--Categorise the expense transactions

USE expenses_db_azure;
GO


---------------------------------------------------------------


DROP PROCEDURE IF EXISTS sp_expense_03_categorise_tables;
GO

CREATE PROCEDURE sp_expense_03_categorise_tables 
AS
/*
Assign an expense category to each transaction in the expense table
by looking for key words in the expense description field

The Categories table contains a list of key words and their respective categories

Given a table of expense data, loop through that table
pickup the description field, then search that description for key words
by then looping (second loop) through the Category table containing all the 
key words

*/

--the expenses databse
--USE expensessql;

--sql var for holding the search string
DECLARE @find_str as NVARCHAR(100);


-- var for holding the description
DECLARE @exp_desc as NVARCHAR(100);
-- var for holding the category
-- !! SHOULD this be an id??
DECLARE @exp_cat_value as NVARCHAR(100);
DECLARE @exp_cat_id as INT;

-- cursor var for the outer loop to run through the expense table
DECLARE @exp_main_data as CURSOR;

-- Initialise (define?) the outer loop cursor
--which fields will be included in the cursor
SET @exp_main_data = CURSOR FOR
SELECT exp_desc, exp_cat, exp_cat_id
 FROM stage02_master02_category
 FOR UPDATE;


--Open the outer loop cursor
OPEN @exp_main_data;

SET NOCOUNT ON
PRINT 'START CATEGORISING......'

--Fetch next record (first record) from the expense table (outer loop)
FETCH NEXT FROM @exp_main_data INTO @exp_desc, @exp_cat_value, @exp_cat_id;

--Internal flag? for EOF????
WHILE @@FETCH_STATUS = 0
BEGIN
 	
 	--PRINT @exp_desc + ' - ' +  @exp_cat;

	DECLARE @exp_cat_value_out nvarchar(20)
	DECLARE @exp_cat_id_out nvarchar(20)
	EXEC sp_expense_lookup_desc_in_cat @exp_desc, @exp_cat_value_out OUTPUT, @exp_cat_id_out  OUTPUT

	--PRINT @exp_cat_key_out
	--PRINT @exp_cat_out

	UPDATE stage02_master02_category
	--SET exp_cat = @exp_cat_value, exp_cat_id = @exp_cat_id
	SET exp_cat = @exp_cat_value_out, exp_cat_id = @exp_cat_id_out
	WHERE CURRENT OF @exp_main_data

	--fetch the next expense transaction row - outer loop - expense table
	FETCH NEXT FROM @exp_main_data INTO @exp_desc, @exp_cat_value, @exp_cat_id;
END

--close the outer loop expense table/cursor
CLOSE @exp_main_data;
DEALLOCATE @exp_main_data;
SET NOCOUNT OFF
PRINT 'END CATEGORISING'

--look at the results
--SELECT * FROM amex02_clean;