USE OLAP
--Question 1
--Rearrange our loading script to make sure that product has been capitalised
--a. We will need to create a capitalise function in our OLAP database. We have
--already create the necessary function in OLTP, so simply rerun this code in OLAP
GO
CREATE FUNCTION Proper_Case(@string VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @int_posn INT				--index
  DECLARE @len_str INT				--input length
  DECLARE @cur_chr NCHAR(1)			--current char
  DECLARE @first_chr INT			--first letter flag (1/0)
  DECLARE @output_str VARCHAR(255)	--output string
  DECLARE @w_space VARCHAR(10)		--characters considered as white space
									-- carriage return, linefeed,
									-- tab or space

  SET @w_space = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) +  CHAR(32)  + ']'	--define the set of characters respected as white space e.g. carriage return
  SET @int_posn = 1				--assign a value to the first integer position
  SET @len_str = LEN(@string)	--establish the length of the string to be parsed
  SET @first_chr = 1			--initialise the first_chr 				
  SET @output_str = ''			--assign the output_str to an empty string

  WHILE @int_posn <= @len_str	--loop through all characters in the string
  BEGIN
    SET @cur_chr = SUBSTRING(@string, @int_posn, 1)
    IF @first_chr = 1				--test if the variable has been assigned to one (first character in the delimited string)
		BEGIN
			SET @output_str = @output_str + UPPER(@cur_chr)		--any character with a flag (@first_chr) of 1 will be returned in Upper Case
			SET @first_chr = 0		--reassign the variable to zero after the first character has been converted
		END
    ELSE
		BEGIN
			SET @output_str = @output_str + LOWER(@cur_chr)		--executed if the flag is zero (not the first character in the current delimited string 'word')
		END
		IF @cur_chr LIKE @w_space SET @first_chr = 1		--test if the current character is a declared white space character
--			SET @first_chr = 1				--if true, reassign the variable to one (the first character in the new word)
		SET @int_posn = @int_posn + 1	-- increment the counter
	END
  RETURN @output_str				--return the rebuilt string in Proper Case
END;
GO

--Testing
SELECT dbo.proper_case('tEsT TheSe WoRdS') as result

--b. alter our ETL procedure and change the INSERT statement that loads the products to make sure 
--that our product column is captialised
go
ALTER PROCEDURE ETL @replace bit = null as
/* A procedure to extract, transform and load data from our OLTP database to our OLAP 
   database.

parameter:
@replace - values 0 or null will not replace existing rows in the production table
           the data will instead be appended to the tables.
         - a value of 1 will truncate existing data before loading the rows.
*/
BEGIN
	--Create the temporary tables
		DROP TABLE IF EXISTS ##Countries
		DROP TABLE IF EXISTS ##Products
		DROP TABLE IF EXISTS ##Sales

		SELECT * INTO ##Countries FROM OLTP.DBO.Countries
		SELECT * INTO ##Products FROM OLTP.DBO.Products
		SELECT * INTO ##Sales FROM OLTP.DBO.Sales

	--Transform the data
		--Dropping columns
		ALTER TABLE ##Countries
			DROP COLUMN F3,F4,F5,F6,F7,F8,F9

		--blank rows in sales
		DELETE FROM ##sales
		WHERE [Date] is null

		--Changing datatypes
			--Countries table
				ALTER TABLE ##Countries ALTER COLUMN country char(120) --change datatype to char(20)
				ALTER TABLE ##Countries ALTER COLUMN city char(120) --change datatype to char(20)
			--Products table
				ALTER TABLE ##Products ALTER COLUMN code char(15) --change size to char(5)
				ALTER TABLE ##Products ALTER COLUMN product_line char(120) --change datatype to char(120)
				ALTER TABLE ##Products ALTER COLUMN product_cost money 
				ALTER TABLE ##Products ADD product char(240) --add a new column when we split product and product type
			--Sales table
				ALTER TABLE ##sales ALTER COLUMN [date] date
				ALTER TABLE ##sales ALTER COLUMN Retailer_city char(120)
				ALTER TABLE ##sales ALTER COLUMN Order_method_type char(120)
				--select max(len(urgent)) from ##sales
				ALTER TABLE ##sales ALTER COLUMN Urgent char(10)
				ALTER TABLE ##sales ALTER COLUMN Retailer_type char(120)
				--select max(len(product_code)) from ##sales
				ALTER TABLE ##sales ALTER COLUMN Product_code char(15)
				ALTER TABLE ##sales ALTER COLUMN Sale_price money
				ALTER TABLE ##sales ALTER COLUMN Quantity_sold int
		--Modifying the contents of the tables
			--Sales
				--Get rid of spaces
				UPDATE ##sales
				SET retailer_city=trim(retailer_city)
				--Correct 'Lodon', 'Londonn'
				UPDATE ##sales
				SET retailer_city='London'
				WHERE retailer_city in ('Lodon', 'Londonn')
				--Handling nulls
				DELETE ##sales
				WHERE quantity_sold is null

				--Retailer Type
				--Trim the spaces
				UPDATE ##sales
				SET retailer_type=trim(retailer_type)
			    --
				--Remove non-printable characters
				UPDATE ##sales
				SET retailer_type =
						REPLACE(
						REPLACE(
						REPLACE(
						REPLACE(
						REPLACE(retailer_type,char(9), '')--horizontal tab
						,char(10), '') --Line Feed
						,char(11), '') --vertical tab
						,char(12), '') --Form Feed
						,char(13), '') --Carriage Return
				FROM ##Sales
				--Fix the names of the following retailer types
				--   "Direct Mark." should be "Direct Marketing"
				UPDATE ##sales
				SET retailer_type = 'Direct Marketing'
				WHERE retailer_type = 'Direct Mark.'
				--   "Dept. Store" should be "Department Store"
				UPDATE ##sales
				SET retailer_type = 'Department Store'
				WHERE retailer_type = 'Dept. Store'

			-- fill down
				BEGIN
					DECLARE @DB_CURSOR as CURSOR;
					DECLARE @previous_product_line CHAR(120);
					DECLARE @current_product_line CHAR(120);
					BEGIN 
						BEGIN TRANSACTION
						SET @DB_CURSOR = CURSOR FOR
							select trim(product_line)
							from ##products
						OPEN @DB_CURSOR;
						FETCH NEXT FROM @DB_CURSOR INTO @current_product_line
						WHILE @@FETCH_STATUS =0
						BEGIN
						  IF @current_product_line is null
							  BEGIN
								UPDATE ##products
								SET product_line = @previous_product_line
								WHERE CURRENT OF @DB_CURSOR
							  END
						  ELSE
							BEGIN
								SET @previous_product_line=@current_product_line
							END
						  FETCH NEXT FROM @DB_CURSOR INTO @current_product_line
						END
						PRINT 'Finished'
						COMMIT TRANSACTION
					END;
					CLOSE @DB_CURSOR;
					DEALLOCATE @DB_CURSOR;
				END

		--Getting rid of rows
		DELETE FROM ##products
		WHERE product_line IN ('Golf Equipment','Personal Accessories')

	--Loading the data into production tables
		--if @replace is 1 then we will remove all the rows in production table
		IF @replace=1
			BEGIN
				truncate table countries
				truncate table products
				truncate table sales_2016
				truncate table sales_2017
			END

		--load the data into production tables

			--Insert Countries 
			INSERT INTO countries
				SELECT distinct country,city
				FROM ##countries
				EXCEPT
		        SELECT * FROM Countries

			--Insert products
			INSERT INTO products(Code, Product_line
									,Product_type
									,Product
									,Product_cost)
			SELECT Code, Product_line
					,OLTP.dbo.findstringto([Product_type/Product],'/') --Product_type
					,dbo.proper_case(OLTP.dbo.findstringfrom([Product_type/Product],'/')) -- Product in proper case
					,Product_cost
			FROM ##products
			EXCEPT
		    SELECT * FROM products 

			--Insert sales into appropriate table
			INSERT INTO sales_2016
			SELECT * FROM ##sales
			WHERE datepart(year,[Date])=2016
			EXCEPT
		    SELECT * FROM sales_2016

			INSERT INTO sales_2017
			SELECT * FROM ##sales
			WHERE datepart(year,date)=2017
			EXCEPT
		    SELECT * FROM sales_2017
END
GO

--c. run our ETL producedure so that it REPLACES the existing data in our warehouse tables.
--Data before loading
SELECT count(*) as products_before_loading FROM products 
SELECT count(*) as countries_before_loading FROM countries 
SELECT count(*) as sales_2016_before_loading FROM sales_2016
SELECT count(*) as sales_2017_before_loading FROM sales_2017

--ETL 
exec ETL 1 --The parameter 1 is used to tell use to truncate the production tables

--Data after loading
SELECT count(*) as products_after_loading FROM products 
SELECT count(*) as countries_after_loading FROM countries 
SELECT count(*) as sales_2016_after_loading FROM sales_2016
SELECT count(*) as sales_2017_after_loading FROM sales_2017

--d. check that the product names have been capitalised.
SELECT * FROM products