USE master;
GO

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ActionTireCoCrm')
	ALTER DATABASE [ActionTireCoCrm] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ActionTireCoCrm')
	DROP DATABASE [ActionTireCoCrm];
GO

CREATE DATABASE ActionTireCoCrm;
GO

USE ActionTireCoCrm
GO

CREATE TABLE [dbo].[Store] (
	[Id] INT NOT NULL,
	[Name] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Store_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Store_Active DEFAULT (1),
	CONSTRAINT PK_Store_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_Strore_Name UNIQUE ([Name])
);
GO

CREATE TABLE [dbo].[User] (
	[Id] INT NOT NULL,
	[StoreId] INT NOT NULL,
	[Name] VARCHAR(64) NOT NULL,
	[ADName] VARCHAR(64) NULL,
	[IsAdmin] BIT NOT NULL CONSTRAINT DF_User_IsAdmin DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_User_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_User_Active DEFAULT (1),
	CONSTRAINT PK_User_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_User_StoreId_To_Store_Id FOREIGN KEY ([StoreId]) REFERENCES [dbo].[Store]([Id])
);
GO

CREATE TABLE [dbo].[CustomerType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_CustomerType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_CustomerType_Active DEFAULT (1),
	CONSTRAINT PK_CustomerType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_CustomerType_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[Customer] (
	[Id] INT NOT NULL,
	[CustomerTypeId] INT NOT NULL,
	[UserId] INT NOT NULL,
	[Name] VARCHAR(64) NULL,
	[AddressLine1] VARCHAR(128) NULL,
	[AddressLine2] VARCHAR(128) NULL,
	[City] VARCHAR(64) NULL,
	[State] CHAR(2) NULL,
	[Zip] VARCHAR(16) NULL,
	[Phone] VARCHAR(16) NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Customer_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Customer_Active DEFAULT (1),
	CONSTRAINT PK_Customer_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_Customer_CustomerTypeId_To_CustomerType_Id FOREIGN KEY ([CustomerTypeId]) REFERENCES [dbo].[CustomerType]([Id]),
	CONSTRAINT FK_Customer_UserId_To_User_Id FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([Id])
);
GO



CREATE TABLE [dbo].[PhoneNumberType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_PhoneNumberType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_PhoneNumberType_Active DEFAULT (1),
	CONSTRAINT PK_PhoneNumberType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_PhoneNumberType_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[PhoneNumber] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[PhoneNumberTypeId] INT NOT NULL,
	[Value] VARCHAR(16) NOT NULL,
	[Extension] VARCHAR(16) NULL CONSTRAINT DF_PhoneNumber_Extension DEFAULT (NULL),
	[IsAsa] BIT NOT NULL CONSTRAINT DF_PhoneNumber_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_PhoneNumber_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_PhoneNumber_Active DEFAULT (1),
	CONSTRAINT PK_PhoneNumber_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_PhoneNumber_PhoneNumberTypeId_To_PhoneNumberType_Id FOREIGN KEY ([PhoneNumberTypeId]) REFERENCES [dbo].[PhoneNumberType]([Id])
);
GO

CREATE TABLE [dbo].[EmailAddressType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_EmailAddressType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_EmailAddressType_Active DEFAULT (1),
	CONSTRAINT PK_EmailAddressType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_EmailAddressType_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[EmailAddress] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[EmailAddressTypeId] INT NOT NULL,
	[Value] VARCHAR(256) NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_EmailAddress_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_EmailAddress_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_EmailAddress_Active DEFAULT (1),
	CONSTRAINT PK_EmailAddress_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_EmailAddress_EmailAddressTypeId_To_EmailAddressType_Id FOREIGN KEY ([EmailAddressTypeId]) REFERENCES [dbo].[EmailAddressType]([Id])
);
GO

CREATE TABLE [dbo].[PostalAddressType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_PostalAddressType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_PostalAddressType_Active DEFAULT (1),
	CONSTRAINT PK_PostalAddressType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_PostalAddressType_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[PostalAddress] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[PostalAddressTypeId] INT NOT NULL,
	[AddressLine1] VARCHAR(255) NOT NULL,
	[AddressLine2] VARCHAR(255) NULL,
	[AddressLine3] VARCHAR(255) NULL,
	[City] VARCHAR(64) NOT NULL,
	[State] CHAR(2) NOT NULL,
	[ZipCode] VARCHAR(16) NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_PostalAddress_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_PostalAddress_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_PostalAddress_Active DEFAULT (1),
	CONSTRAINT PK_PostalAddress_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_PostalAddress_PostalAddressTypeId_To_PostalAddressType_Id FOREIGN KEY ([PostalAddressTypeId]) REFERENCES [dbo].[PostalAddressType]([Id])
);
GO

CREATE TABLE [dbo].[NoteType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(16) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_NoteType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_NoteType_Active DEFAULT (1),
	CONSTRAINT PK_NoteType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_NoteType_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[Note] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[NoteTypeId] INT NOT NULL,
	[CustomerId] INT NOT NULL,
	[UserId] INT NOT NULL,
	[Value] VARCHAR(MAX) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Note_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Note_Active DEFAULT (1),
	CONSTRAINT PK_Note_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_Note_NoteTypeId_To_NoteType_Id FOREIGN KEY ([NoteTypeId]) REFERENCES [dbo].[NoteType]([Id]),
	CONSTRAINT FK_Note_CustomerId_To_Customer_Id FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer]([Id]),
	CONSTRAINT FK_Note_UserId_To_User_Id FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([Id])
);
GO

CREATE TABLE [dbo].[Gender] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(32) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Gender_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Gender_Active DEFAULT (1),
	CONSTRAINT PK_Gender_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_Gender_Value UNIQUE ([Value])
);
GO

CREATE TABLE [dbo].[Contact] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[GenderId] INT NOT NULL,
	[FirstName] VARCHAR(128) NOT NULL,
	[MiddleName] VARCHAR(128) NULL,
	[LastName] VARCHAR(128) NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_Contact_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Contact_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Contact_Active DEFAULT (1),
	CONSTRAINT PK_Contact_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_Contact_GenderId_To_Gender_Id FOREIGN KEY ([GenderId]) REFERENCES [dbo].[Gender]([Id])
);
GO

CREATE TABLE [dbo].[CustomerContact] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[CustomerId] INT NOT NULL,
	[ContactId] INT NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_CustomerContact_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_CustomerContact_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_CustomerContact_Active DEFAULT (1),
	CONSTRAINT PK_CustomerContact_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_CustomerContact_CustomerId_To_Customer_Id FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer]([Id]),
	CONSTRAINT FK_CustomerContact_ContactId_To_Contact_Id FOREIGN KEY ([ContactId]) REFERENCES [dbo].[Contact]([Id])
);
GO

CREATE TABLE [dbo].[ContactPhoneNumber] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[ContactId] INT NOT NULL,
	[PhoneNumberId] INT NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_ContactPhoneNumber_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_ContactPhoneNumber_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_ContactPhoneNumber_Active DEFAULT (1),
	CONSTRAINT PK_ContactPhoneNumber_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_ContactPhoneNumber_ContactId_To_Contact_Id FOREIGN KEY ([ContactId]) REFERENCES [dbo].[Contact]([Id]),
	CONSTRAINT FK_ContactPhoneNumber_PhoneNumberId_To_PhoneNumber_Id FOREIGN KEY ([PhoneNumberId]) REFERENCES [dbo].[PhoneNumber]([Id])
);
GO

CREATE TABLE [dbo].[ContactEmailAddress] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[ContactId] INT NOT NULL,
	[EmailAddressId] INT NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_ContactEmailAddress_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_ContactEmailAddress_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_ContactEmailAddress_Active DEFAULT (1),
	CONSTRAINT PK_ContactEmailAddress_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_ContactEmailAddress_ContactId_To_Contact_Id FOREIGN KEY ([ContactId]) REFERENCES [dbo].[Contact]([Id]),
	CONSTRAINT FK_ContactEmailAddress_EmailAddressId_To_EmailAddress_Id FOREIGN KEY ([EmailAddressId]) REFERENCES [dbo].[EmailAddress]([Id])
);
GO

CREATE TABLE [dbo].[ContactPostalAddress] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[ContactId] INT NOT NULL,
	[PostalAddressId] INT NOT NULL,
	[IsAsa] BIT NOT NULL CONSTRAINT DF_ContactPostalAddress_IsAsa DEFAULT (0),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_ContactPostalAddress_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_ContactPostalAddress_Active DEFAULT (1),
	CONSTRAINT PK_ContactPostalAddress_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_ContactPostalAddress_ContactId_To_Contact_Id FOREIGN KEY ([ContactId]) REFERENCES [dbo].[Contact]([Id]),
	CONSTRAINT FK_ContactPostalAddress_PostalAddressId_To_PostalAddress_Id FOREIGN KEY ([PostalAddressId]) REFERENCES [dbo].[PostalAddress]([Id])
);
GO

CREATE TABLE [dbo].[CallType] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[Value] VARCHAR(16) NOT NULL,
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_CallType_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_CallType_Active DEFAULT (1),
	CONSTRAINT PK_CallType_Id PRIMARY KEY ([Id]),
	CONSTRAINT UQ_CallType_Value UNIQUE ([Value])
);

CREATE TABLE [dbo].[Call] (
	[Id] INT NOT NULL IDENTITY(1,1),
	[CallTypeId] INT NOT NULL,
	[UserId] INT NOT NULL,
	[CustomerId] INT NOT NULL,
	[Value] VARCHAR(MAX),
	[DateCreated] DATETIME NOT NULL CONSTRAINT DF_Call_DateCreated DEFAULT (GETDATE()),
	[Active] BIT NOT NULL CONSTRAINT DF_Call_Active DEFAULT (1),
	CONSTRAINT PK_Call_Id PRIMARY KEY ([Id]),
	CONSTRAINT FK_Call_CallTypeId_To_CallType_Id FOREIGN KEY ([CallTypeId]) REFERENCES [dbo].[CallType]([Id]),
	CONSTRAINT FK_Call_UserId_To_User_Id FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([Id]),
	CONSTRAINT FK_Call_CustomerId_To_Customer_Id FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer]([Id]),
);
GO


/* Load some default data */
INSERT INTO [dbo].[CustomerType] ([Value]) VALUES ('Customer');
INSERT INTO [dbo].[CustomerType] ([Value]) VALUES ('Prospect');

INSERT INTO [dbo].[NoteType] ([Value]) VALUES ('Unspecified');

INSERT INTO [dbo].[CallType] ([Value]) VALUES ('Unspecified');

INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Unspecified');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('ASA');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Work');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Fax');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Mobile');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Home');
INSERT INTO [dbo].[PhoneNumberType] ([Value]) VALUES ('Location');
GO

INSERT INTO [dbo].[EmailAddressType] ([Value]) VALUES ('Unspecified');
INSERT INTO [dbo].[EmailAddressType] ([Value]) VALUES ('ASA');
INSERT INTO [dbo].[EmailAddressType] ([Value]) VALUES ('Work');
INSERT INTO [dbo].[EmailAddressType] ([Value]) VALUES ('Personal');
INSERT INTO [dbo].[EmailAddressType] ([Value]) VALUES ('Location');
GO

INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('Unspecified');
INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('ASA');
INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('Office');
INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('Home');
INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('Work');
INSERT INTO [dbo].[PostalAddressType] ([Value]) VALUES ('Location');
GO

INSERT INTO [dbo].[Gender] ([Value]) VALUES ('Unspecified');
INSERT INTO [dbo].[Gender] ([Value]) VALUES ('Male');
INSERT INTO [dbo].[Gender] ([Value]) VALUES ('Female');
GO

CREATE INDEX IDX_Customer_Name ON [dbo].[Customer]([Name]);
CREATE INDEX IDX_Customer_Phone ON [dbo].[Customer]([Phone]);
CREATE INDEX IDX_Customer_AddressLine1 ON [dbo].[Customer]([AddressLine1]);
CREATE INDEX IDX_Customer_AddressLine2 ON [dbo].[Customer]([AddressLine2]);
CREATE INDEX IDX_Customer_City ON [dbo].[Customer]([City]);
CREATE INDEX IDX_Customer_State ON [dbo].[Customer]([State]);
GO

INSERT INTO [dbo].[Store] ([Id],[Name])
	SELECT  
		CAST([STNUM] AS INT) AS [Id],
		UPPER(RTRIM(LTRIM([STNAM]))) AS [Name]
	FROM 
		[XFODBC]..[PUBLIC].[STORE];
GO

INSERT INTO [dbo].[User] ([Id],[StoreId],[Name],[IsAdmin])
	SELECT DISTINCT 
		CAST([SLNUM] AS INT) AS [Id],
		CAST([SLSTO] AS INT) AS [StoreId],
		UPPER(RTRIM(LTRIM([SLNAM]))) AS [Name],
		0 AS [IsAdmin]
	FROM 
		[XFODBC]..[PUBLIC].[SLMFIL] WHERE CAST([SLSTO] AS INT) = 1;
GO

UPDATE [dbo].[User] SET [ADName] = 'wbrooks', IsAdmin = 1 WHERE [Id] = (SELECT [Id] FROM [dbo].[User] WHERE [Name] = 'BROOKS, WILLIAM');
UPDATE [dbo].[User] SET [ADName] = 'testuser', IsAdmin = 1 WHERE [Id] = (SELECT [Id] FROM [dbo].[User] WHERE [Name] = 'BBDS TEST USER');
GO

DECLARE @CustomerId INT
DECLARE @SalesmanId INT
DECLARE @CustomerName VARCHAR(MAX)
DECLARE @CustomerAddressLine1 VARCHAR(MAX)
DECLARE @CustomerAddressLine2 VARCHAR(MAX)
DECLARE @CustomerCity VARCHAR(MAX)
DECLARE @CustomerState VARCHAR(MAX)
DECLARE @CustomerZip VARCHAR(MAX)
DECLARE @CustomerPhone VARCHAR(MAX)
DECLARE @CustomerXPhone VARCHAR(MAX)
DECLARE @CustomerFax VARCHAR(MAX)
DECLARE @CustomerEmail VARCHAR(MAX)
DECLARE @CustomerFax2 VARCHAR(MAX)
DECLARE @CustomerEmail2 VARCHAR(MAX)

	SELECT 
		CAST([CUST] AS INT) AS [CustomerId],
		CAST([SLM] AS INT) AS [SalesmanId],
		UPPER(RTRIM(LTRIM([NAME]))) AS [CustomerName], 
		UPPER(RTRIM(LTRIM([ADDR1]))) AS [CustomerAddressLine1],
		UPPER(RTRIM(LTRIM([ADDR2]))) AS [CustomerAddressLine2],
		UPPER(RTRIM(LTRIM([CITY]))) AS [CustomerCity],
		UPPER(RTRIM(LTRIM([STATE]))) AS [CustomerState],
		UPPER(RTRIM(LTRIM([ZIP]))) AS [CustomerZip],
		UPPER(RTRIM(LTRIM([PHONE]))) AS [CustomerPhone],
		UPPER(RTRIM(LTRIM([XPHON]))) AS [CustomerXPhone],
		UPPER(RTRIM(LTRIM([CUSFAX]))) AS [CustomerFax],
		UPPER(RTRIM(LTRIM([CEMAIL]))) AS [CustomerEmail],
		UPPER(RTRIM(LTRIM([CUSFAX2]))) AS [CustomerFax2],
		UPPER(RTRIM(LTRIM([CEMAIL2]))) AS [CustomerEmail2]
	INTO #tmpCustomer
	FROM [XFODBC]..[PUBLIC].[CUSMAS]

	

DECLARE CustomerCursor CURSOR FOR
	SELECT * FROM #tmpCustomer

OPEN CustomerCursor

FETCH NEXT FROM CustomerCursor INTO
	@CustomerId, @SalesmanId, @CustomerName, @CustomerAddressLine1, @CustomerAddressLine2, @CustomerCity, @CustomerState, @CustomerZip, @CustomerPhone, @CustomerXPhone, @CustomerFax, @CustomerEmail, @CustomerFax2, @CustomerEmail2

WHILE(@@FETCH_STATUS = 0)
BEGIN
	DECLARE @ContactId INT = NULL;
	DECLARE @PostalAddressId INT = NULL;
	DECLARE @CustomerPhoneId INT = NULL;
	DECLARE @CustomerXPhoneId INT = NULL;
	DECLARE @CustomerFaxId INT = NULL;
	DECLARE @CustomerFax2Id INT = NULL;
	DECLARE @CustomerEmailId INT = NULL;
	DECLARE @CustomerEmail2Id INT = NULL;
	DECLARE @CustomerTypeId INT = NULL;

	SELECT @CustomerTypeId = [Id] FROM [dbo].[CustomerType] WHERE [Value] = 'Customer';

	/* Add customer */
	INSERT INTO [dbo].[Customer] ([Id], [CustomerTypeId], [UserId], [Name], [AddressLine1], [AddressLine2], [City], [State], [Zip], [Phone]) VALUES (@CustomerId, @CustomerTypeId, @SalesmanId, @CustomerName, @CustomerAddressLine1, @CustomerAddressLine2, @CustomerCity, @CustomerState, @CustomerZip, @CustomerPhone);
	
	/* Add Contact and get ContactId */
	INSERT INTO [dbo].[Contact] ([GenderId], [FirstName], [IsAsa]) VALUES (1, @CustomerName, 1);
	SET @ContactId = SCOPE_IDENTITY();
	
	/* Add Postal Address and get PostalAddressId */
	IF(@CustomerAddressLine1 IS NOT NULL AND @CustomerCity IS NOT NULL AND @CustomerState IS NOT NULL AND @CustomerZip IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[PostalAddress] ([PostalAddressTypeId],[AddressLine1], [AddressLine2], [AddressLine3],[City], [State], [ZipCode], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[PostalAddressType] WHERE [Value] = 'Location'),@CustomerAddressLine1, @CustomerAddressLine2, NULL, @CustomerCity, @CustomerState, @CustomerZip, 1);
		SET @PostalAddressId = SCOPE_IDENTITY();
		INSERT INTO [dbo].[ContactPostalAddress] ([ContactId],[PostalAddressId],[IsAsa]) VALUES (@ContactId, @PostalAddressId, 1);
	END	

	IF(@CustomerPhone IS NOT NULL AND LEN(@CustomerPhone) = 10)
	BEGIN
		INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value],[IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Location'), @CustomerPhone,1);
		SET @CustomerPhoneId = SCOPE_IDENTITY();
		INSERT INTO [ContactPhoneNumber] ([ContactId], [PhoneNumberId], [IsAsa]) VALUES (@ContactId, @CustomerPhoneId, 1);
	END
	
	IF(@CustomerXPhone IS NOT NULL AND LEN(@CustomerXPhone) = 10)
	BEGIN
		INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Location'), @CustomerXPhone, 1);
		SET @CustomerXPhoneId = SCOPE_IDENTITY();
		INSERT INTO [ContactPhoneNumber] ([ContactId], [PhoneNumberId], [IsAsa]) VALUES (@ContactId, @CustomerXPhoneId, 1);
	END
	
	IF(@CustomerFax IS NOT NULL AND LEN(@CustomerFax) = 10)
	BEGIN
		INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Fax'), @CustomerFax, 1);
		SET @CustomerFaxId = SCOPE_IDENTITY();
		INSERT INTO [ContactPhoneNumber] ([ContactId], [PhoneNumberId], [IsAsa]) VALUES (@ContactId, @CustomerFaxId, 1);
	END

	IF(@CustomerFax2 IS NOT NULL AND LEN(@CustomerPhone) = 10)
	BEGIN
		INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Fax'), @CustomerFax2, 1);
		SET @CustomerFax2Id = SCOPE_IDENTITY();
		INSERT INTO [ContactPhoneNumber] ([ContactId], [PhoneNumberId], [IsAsa]) VALUES (@ContactId, @CustomerFax2Id, 1);
	END

	IF(@CustomerEmail IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[EmailAddress] ([EmailAddressTypeId], [Value], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[EmailAddressType] WHERE [Value] = 'Location'), @CustomerEmail, 1);
		SET @CustomerEmailId = SCOPE_IDENTITY();
		INSERT INTO [ContactEmailAddress] ([ContactId], [EmailAddressId], [IsAsa]) VALUES (@ContactId, @CustomerEmailId, 1);
	END

	IF(@CustomerEmail2 IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[EmailAddress] ([EmailAddressTypeId], [Value], [IsAsa]) VALUES ((SELECT [Id] FROM [dbo].[EmailAddressType] WHERE [Value] = 'Location'), @CustomerEmail2, 1);
		SET @CustomerEmail2Id = SCOPE_IDENTITY();
		INSERT INTO [ContactEmailAddress] ([ContactId], [EmailAddressId], [IsAsa]) VALUES (@ContactId, @CustomerEmail2Id, 1);
	END

	IF(@CustomerId IS NOT NULL AND @ContactId IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[CustomerContact] ([CustomerId],[ContactId],[IsAsa]) VALUES (@CustomerId, @ContactId, 1);
	END

	FETCH NEXT FROM CustomerCursor INTO
		@CustomerId, @SalesmanId, @CustomerName, @CustomerAddressLine1, @CustomerAddressLine2, @CustomerCity, @CustomerState, @CustomerZip, @CustomerPhone, @CustomerXPhone, @CustomerFax, @CustomerEmail, @CustomerFax2, @CustomerEmail2
END

CLOSE CustomerCursor

DEALLOCATE CustomerCursor

DROP TABLE #tmpCustomer
GO

/***************************************************************************************************************************************************************************************************************/
DECLARE @ProspectId INT
DECLARE @ProspectCustomerTypeId INT
DECLARE @PropspectUserId INT
DECLARE @ProspectName VARCHAR(64)
DECLARE @ProspectAddressLine1 VARCHAR(128)
DECLARE @ProspectAddressLine2 VARCHAR(128)
DECLARE @ProspectCity  VARCHAR(64)
DECLARE @ProspectState CHAR(2)
DECLARE @ProspectZip  VARCHAR(16)
DECLARE @ProspectPhone  VARCHAR(16)
DECLARE @ProspectDateCreated DATETIME
DECLARE @ProspectActive BIT

DECLARE @Prospect_Name VARCHAR(255)
DECLARE @ProspectCellNumber VARCHAR(255)
DECLARE @ProspectEmail VARCHAR(255)
DECLARE @ProspectNumber VARCHAR(50)
DECLARE @Prospect2Name VARCHAR(255)
DECLARE @Prospect2CellNumber VARCHAR(255)
DECLARE @Prospect2Email VARCHAR(255)
DECLARE @Prospect2Number VARCHAR(50)
DECLARE @Prospect3Name VARCHAR(255)
DECLARE @Prospect3CellNumber VARCHAR(255)
DECLARE @Prospect3Email VARCHAR(255)
DECLARE @Prospect3Number VARCHAR(50)
/***************************************************************************************************************************************************************************************************************/

DECLARE ProspectCursor CURSOR FOR
	SELECT
		CAST([ID] AS INT) * -1 AS [Id],
		2 AS [CustomerTypeId],
		(SELECT CASE WHEN [Owner] IN (SELECT [Id] FROM [dbo].[User]) THEN [Owner] ELSE (SELECT [Id] FROM [dbo].[User] WHERE [Name] = 'BROOKS, WILLIAM') END) AS [UserId],
		CAST(UPPER(RTRIM(LTRIM([Name]))) AS VARCHAR(64)) AS [Name],
		CAST(UPPER(RTRIM(LTRIM([Address]))) AS VARCHAR(128)) AS [AddressLine1],
		CAST(NULL AS VARCHAR(128)) AS [AddressLine2],
		CAST(UPPER(RTRIM(LTRIM([City]))) AS VARCHAR(64)) AS [City],
		CAST(UPPER(RTRIM(LTRIM([State]))) AS CHAR(2)) AS [State],
		CAST(REPLACE(REPLACE(RTRIM(LTRIM([Zip])),' ',''),'-','') AS VARCHAR(16)) AS [Zip],
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([Phone])),' ',''),'-',''),'(',''),')',''),'.','') AS VARCHAR(16)) AS [Phone],
		[LastModified] AS [DateCreated],
		CAST(1 AS BIT) AS [Active],
		CAST(UPPER(RTRIM(LTRIM([ContactName]))) AS VARCHAR(128)) AS [Prospect_Name],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([ContactCellNumber])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16)) AS [ProspectCellNumber],
		CAST(UPPER(RTRIM(LTRIM([ContactEmail]))) AS VARCHAR(256)) AS [ProspectEmail],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([ContactNumber])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16)) AS [ProspectNumber],
		CAST(UPPER(RTRIM(LTRIM([Contact2Name]))) AS VARCHAR(128)) AS [Prospect2Name],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([Contact2CellNumber])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16)) AS [Prospect2CellNumber],
		CAST(UPPER(RTRIM(LTRIM([Contact2Email]))) AS VARCHAR(256)) AS [Prospect2Email],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([Contact2Number])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16)) AS [Prospect2Number],
		CAST(UPPER(RTRIM(LTRIM([Contact3Name]))) AS VARCHAR(128)) AS [Prospect3Name],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([Contact3CellNumber])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16))AS [Prospect3CellNumber],
		CAST(UPPER(RTRIM(LTRIM([Contact3Email]))) AS VARCHAR(256)) AS [Prospect3Email],
		CAST(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM([Contact3Number])),' ' ,''),'-',''),'(',''),')',''),'.','')) AS VARCHAR(16)) AS [Prospect3Number]
	FROM 
		[CRM].[dbo].[Prospects]

OPEN ProspectCursor

FETCH NEXT FROM ProspectCursor INTO
	@ProspectId, @ProspectCustomerTypeId, @PropspectUserId, @ProspectName, 
	@ProspectAddressLine1, @ProspectAddressLine2, @ProspectCity, @ProspectState, 
	@ProspectZip, @ProspectPhone, @ProspectDateCreated, @ProspectActive,
	@Prospect_Name, @ProspectCellNumber, @ProspectEmail, @ProspectNumber, 
	@Prospect2Name, @Prospect2CellNumber, @Prospect2Email, @Prospect2Number, 
	@Prospect3Name, @Prospect3CellNumber, @Prospect3Email, @Prospect3Number

/***************************************************************************************************************************************************************************************************************/

WHILE(@@FETCH_STATUS = 0)
BEGIN

	DECLARE @ContactId INT
	DECLARE @PostalAddressId INT
	DECLARE @ProspectPhoneId INT

	/* Add the Prospect */
	INSERT INTO [dbo].[Customer] ([Id], [CustomerTypeId], [UserId], [Name], [AddressLine1], [AddressLine2], [City], [State], [Zip], [Phone]) VALUES (@ProspectId, @ProspectCustomerTypeId, @PropspectUserId, @ProspectName, @ProspectAddressLine1, @ProspectAddressLine2, @ProspectCity, @ProspectState, @ProspectZip, @ProspectPhone);

	/* Add Contact and get ContactId */
	INSERT INTO [dbo].[Contact] ([GenderId], [FirstName], [IsAsa]) VALUES (1, @ProspectName, 1);
	SET @ContactId = SCOPE_IDENTITY();

	/* Add Postal Address and get PostalAddressId */
	IF(@ProspectAddressLine1 IS NOT NULL AND @ProspectCity IS NOT NULL AND @ProspectState IS NOT NULL AND @ProspectZip IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[PostalAddress] ([PostalAddressTypeId],[AddressLine1], [AddressLine2], [AddressLine3],[City], [State], [ZipCode], [IsAsa]) VALUES (
			(SELECT [Id] FROM [dbo].[PostalAddressType] WHERE [Value] = 'Location'),
			@ProspectAddressLine1, 
			@ProspectAddressLine2, 
			NULL,
			@ProspectCity, 
			@ProspectState, 
			@ProspectZip, 
			1
		);
		SET @PostalAddressId = SCOPE_IDENTITY();
		INSERT INTO [dbo].[ContactPostalAddress] ([ContactId],[PostalAddressId],[IsAsa]) VALUES (@ContactId, @PostalAddressId, 1);
	END	

	IF(@ProspectPhone IS NOT NULL AND LEN(@ProspectPhone) = 10)
	BEGIN
		INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value],[IsAsa]) VALUES (
			(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Location'), 
			@ProspectPhone,
			1
		);
		SET @ProspectPhoneId = SCOPE_IDENTITY();
		INSERT INTO [ContactPhoneNumber] ([ContactId], [PhoneNumberId], [IsAsa]) VALUES (@ContactId, @ProspectPhoneId, 1);
	END


	/*****************************************************************************************************************/

	IF((@Prospect_Name IS NOT NULL) AND (LEN(@Prospect_Name) > 0))
	BEGIN

		DECLARE @Prospect_Id INT

		INSERT INTO [dbo].[Contact] ([GenderId], [FirstName], [IsAsa]) VALUES (1, @Prospect_Name, 1);

		SET @Prospect_Id = SCOPE_IDENTITY();

		IF((@ProspectCellNumber IS NOT NULL) AND (LEN(@ProspectCellNumber) = 10))
		BEGIN
			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Mobile'),
				@ProspectCellNumber
			)

			DECLARE @PropspectCellNumberId INT

			SET @PropspectCellNumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect_Id, @PropspectCellNumberId);

		END

		IF((@ProspectNumber IS NOT NULL) AND (LEN(@ProspectNumber) = 10))
		BEGIN
			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Unspecified'),
				@ProspectNumber
			)

			DECLARE @PropspectNumberId INT

			SET @PropspectNumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect_Id, @PropspectNumberId);

		END

		IF((@ProspectEmail IS NOT NULL) AND (@ProspectEmail != ''))
		BEGIN
			INSERT INTO [dbo].[EmailAddress] ([EmailAddressTypeId],[Value]) VALUES (
				1,
				@ProspectEmail
			)

			DECLARE @ProspectEmailId INT

			SET @ProspectEmailId = SCOPE_IDENTITY();

			INSERT INTO [dbo].[ContactEmailAddress] ([ContactId],[EmailAddressId]) VALUES (@Prospect_Id, @ProspectEmailId);

		END

		IF(@ProspectId IS NOT NULL AND @Prospect_Id IS NOT NULL)
		BEGIN
			INSERT INTO [dbo].[CustomerContact] ([CustomerId],[ContactId]) VALUES (@ProspectId, @Prospect_Id);
		END

	END


	/*****************************************************************************************************************/

	IF((@Prospect2Name IS NOT NULL) AND (LEN(@Prospect2Name) > 0))
	BEGIN
		DECLARE @Prospect2_Id INT

		INSERT INTO [dbo].[Contact] ([GenderId], [FirstName], [IsAsa]) VALUES (1, @Prospect2Name, 1);

		SET @Prospect2_Id = SCOPE_IDENTITY();

		IF((@Prospect2CellNumber IS NOT NULL) AND (LEN(@Prospect2CellNumber) = 10))
		BEGIN

			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Mobile'),
				@Prospect2CellNumber
			)

			DECLARE @Propspect2CellNumberId INT

			SET @Propspect2CellNumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect2_Id, @Propspect2CellNumberId);

		END

		IF((@Prospect2Number IS NOT NULL) AND (LEN(@Prospect2Number) = 10))
		BEGIN

			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Unspecified'),
				@Prospect2Number
			)

			DECLARE @Propspect2NumberId INT

			SET @Propspect2NumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect2_Id, @Propspect2NumberId);
		END

		IF((@Prospect2Email IS NOT NULL) AND (@Prospect2Email != ''))
		BEGIN

			INSERT INTO [dbo].[EmailAddress] ([EmailAddressTypeId],[Value]) VALUES (
				1,
				@Prospect2Email
			)

			DECLARE @Prospect2EmailId INT

			SET @Prospect2EmailId = SCOPE_IDENTITY();

			INSERT INTO [dbo].[ContactEmailAddress] ([ContactId],[EmailAddressId]) VALUES (@Prospect2_Id, @Prospect2EmailId);

		END

		IF(@ProspectId IS NOT NULL AND @Prospect2_Id IS NOT NULL)
		BEGIN
			INSERT INTO [dbo].[CustomerContact] ([CustomerId],[ContactId]) VALUES (@ProspectId, @Prospect2_Id);
		END

	END

	/*****************************************************************************************************************/

	IF((@Prospect3Name IS NOT NULL) AND (LEN(@Prospect3Name) > 0))
	BEGIN

		DECLARE @Prospect3_Id INT

		INSERT INTO [dbo].[Contact] ([GenderId], [FirstName], [IsAsa]) VALUES (1, @Prospect3Name, 1);

		SET @Prospect3_Id = SCOPE_IDENTITY();

		IF((@Prospect3CellNumber IS NOT NULL) AND (LEN(@Prospect3CellNumber) = 10))
		BEGIN

			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Mobile'),
				@ProspectCellNumber
			)

			DECLARE @Propspect3CellNumberId INT

			SET @Propspect3CellNumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect3_Id, @Propspect3CellNumberId);

		END

		IF((@Prospect3Number IS NOT NULL) AND (LEN(@Prospect3Number) = 10))
		BEGIN

			INSERT INTO [dbo].[PhoneNumber] ([PhoneNumberTypeId], [Value]) VALUES 
			(
				(SELECT [Id] FROM [dbo].[PhoneNumberType] WHERE [Value] = 'Unspecified'),
				@Prospect3Number
			)

			DECLARE @Propspect3NumberId INT

			SET @Propspect3NumberId = SCOPE_IDENTITY()

			INSERT INTO [dbo].[ContactPhoneNumber] ([ContactId],[PhoneNumberId]) VALUES (@Prospect3_Id, @Propspect3NumberId);
		END

		IF((@Prospect3Email IS NOT NULL) AND (@Prospect3Email != ''))
		BEGIN

			INSERT INTO [dbo].[EmailAddress] ([EmailAddressTypeId],[Value]) VALUES (
				1,
				@Prospect3Email
			)

			DECLARE @Prospect3EmailId INT

			SET @Prospect3EmailId = SCOPE_IDENTITY();

			INSERT INTO [dbo].[ContactEmailAddress] ([ContactId],[EmailAddressId]) VALUES (@Prospect3_Id, @Prospect3EmailId);
		END

		IF(@ProspectId IS NOT NULL AND @Prospect3_Id IS NOT NULL)
		BEGIN
			INSERT INTO [dbo].[CustomerContact] ([CustomerId],[ContactId]) VALUES (@ProspectId, @Prospect3_Id);
		END

	END

	/*****************************************************************************************************************/

	IF(@ProspectId IS NOT NULL AND @ContactId IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[CustomerContact] ([CustomerId],[ContactId],[IsAsa]) VALUES (@ProspectId, @ContactId, 1);
	END

	FETCH NEXT FROM ProspectCursor INTO
		@ProspectId, @ProspectCustomerTypeId, @PropspectUserId, @ProspectName, 
		@ProspectAddressLine1, @ProspectAddressLine2, @ProspectCity, @ProspectState, 
		@ProspectZip, @ProspectPhone, @ProspectDateCreated, @ProspectActive,
		@Prospect_Name, @ProspectCellNumber, @ProspectEmail, @ProspectNumber, 
		@Prospect2Name, @Prospect2CellNumber, @Prospect2Email, @Prospect2Number, 
		@Prospect3Name, @Prospect3CellNumber, @Prospect3Email, @Prospect3Number
END

CLOSE ProspectCursor

DEALLOCATE ProspectCursor
GO