--*********************************************************************************************
--1 Create SSIS job to move data from PUMPKIN.CookieSubProd.GeographicRegionAssignment table to JANUS.CookieSubProd.GeographicRegionAssignment table
-- The table needs to be created on JANUS as it does not presently exist
--*********************************************************************************************

--***************************************************************
--2 CREATE USER DEFINED TABLE TYPE CALLED 'CountryList'
--***************************************************************
USE [SubwayPortal]
GO

/****** Object:  UserDefinedTableType [dbo].[CountryList]    Script Date: 05/22/2014 11:35:38 ******/
CREATE TYPE [dbo].[CountryList] AS TABLE(
	[country] [varchar](30) NOT NULL
)
GO


--***************************************************************
--3 CREATE STORED PROC TO GET GEOGRAPHIC REGION ASSIGNMENT 
--***************************************************************
USE [SubwayPortal]
GO

/****** Object:  StoredProcedure [dbo].[proc_SSO_GetGeographicRegionAssignment]    Script Date: 05/22/2014 11:37:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =================================================================================================
-- Used In      : SubwayPartners > SSO
-- =================================================================================================
-- Created date : 05/22/2014
-- Created by   : Travis Wells
-- PBI#			: 37918
-- Task#        : 
-- Description  : Get all Geographic RegionAssignmentID for each country a user is associated to
-- =================================================================================================
-- Updated date : 
-- Updated by   : 
-- Task#        : 
-- Description  : 
-- =================================================================================================

CREATE PROCEDURE [dbo].[proc_SSO_GetGeographicRegionAssignment]
@UserCountries CountryList READONLY
AS
BEGIN

	SET NOCOUNT ON;

	SELECT GeographicRegionAssignmentID FROM [CookieSubProd].[dbo].[GeographicRegionAssignment]
	WHERE GeographicEntityValue in (SELECT * FROM @UserCountries)
	
END

GO

--***************************************************************
--4 CREATE Stored Proc > Get User's Nickname
--***************************************************************

USE [SubwayPortal]
GO

/****** Object:  StoredProcedure [dbo].[proc_SSO_Get_UsersNickName]    Script Date: 05/22/2014 11:44:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =================================================================================================
-- Used In      : SubwayPartners > SSO
-- =================================================================================================
-- Created date : 05/22/2014
-- Created by   : Travis Wells
-- PBI#			: 37918
-- Task#        : 
-- Description  : Get User's NickName via PersonNumber
-- =================================================================================================
-- Updated date : 
-- Updated by   : 
-- Task#        : 
-- Description  : 
-- =================================================================================================
CREATE PROCEDURE [dbo].[proc_SSO_Get_UsersNickName]
@int_InPersonNum INT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT NickName FROM [Common].[dbo].[Person] WHERE PersonNum = @int_InPersonNum	
END
GO

--***************************************************************
--5 CREATE Stored Proc > Get Geographic Region State/Prov Data
--***************************************************************

USE [SubwayPortal]
GO

/****** Object:  StoredProcedure [dbo].[proc_SSO_GetGeographicRegionStateProvAssignment]    Script Date: 05/22/2014 15:44:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =================================================================================================
-- Used In      : SubwayPartners > SSO
-- =================================================================================================
-- Created date : 05/22/2014
-- Created by   : Travis Wells
-- PBI#			: 37918
-- Task#        : 
-- Description  : Get all Geographic State/Prov Region Code for each country a user is associated to
-- =================================================================================================
-- Updated date : 
-- Updated by   : 
-- Task#        : 
-- Description  : 
-- =================================================================================================

CREATE PROCEDURE [dbo].[proc_SSO_GetGeographicRegionStateProvAssignment]
@UserCountries CountryList READONLY
AS
BEGIN

	SELECT ISOStateCode FROM [Common].[dbo].[State]
	WHERE CountryCode IN (SELECT * FROM @UserCountries)
	AND (ISOStateCode is not null AND ISOStateCode != ' ')
	ORDER BY ISOStateCode
	
END

GO