Import Final Mile Facilities

Final mile facilities are provided as a Excel file and should be imported to a WhiteGlove OrganisationRegistry database. The below is a spet-by-step instruction how to do this:

1. Create an intermediate SQL table (ensure the order of columns in Excel and the table is the same):

   IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinalMileFacilityImport]') AND type in (N'U'))
   DROP TABLE [dbo].[FinalMileFacilityImport]
   GO
   
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE TABLE [dbo].[FinalMileFacilityImport](
   	[CompanyCode] [nvarchar](100) NULL,
   	[CompanyDescription] [nvarchar](100) NULL,
   	[FinalMileFacilityCode] [nvarchar](50) NULL,
   	[FinalMileFacilityDescription] [nvarchar](100) NULL,
   	[Title] [nvarchar](100) NULL,
   	[FirstName] [nvarchar](100) NULL,
   	[LastName] [nvarchar](100) NULL,
   	[Line1] [nvarchar](100) NULL,
   	[Line2] [nvarchar](100) NULL,
   	[Line3] [nvarchar](100) NULL,
   	[City] [nvarchar](100) NULL,
   	[Country] [nvarchar](100) NULL,
   	[PostalZip] [nvarchar](100) NULL,
   	[CountryCode] [nvarchar](100) NULL,
   	[PhoneNumber] [nvarchar](100) NULL,
   	[EmailAddress] [nvarchar](100) NULL
   ) ON [PRIMARY]
   GO

2. Go to Edit FinalMileFacilityImport table data mode in SSMS.

3. Copy data from the excel file an past it to the table.

4. Run the following script:

   BEGIN TRAN LOAD_COMPANIES;
   
   INSERT INTO OrganisationRegistry.Organisations
   SELECT 
   	CompanyCode as ExternalCode,
   	CompanyCode as Name,
   	'WHITE_GLOVE_PARTNER' as Role,
   	'N/A' as City,
   	'N/A' as Country,
   	GETUTCDATE() as CreatedDate,
   	GETUTCDATE() as LastModifiedDate,
   	NULL as DisabledDate,
   	CompanyCode as Code
   FROM dbo.FinalMileFacilityImport fm
   WHERE NOT EXISTS (SELECT 1 FROM OrganisationRegistry.Organisations o where fm.CompanyCode = o.ExternalCode)
   GROUP BY CompanyCode
   
   UPDATE OrganisationRegistry.Organisations SET Name = fm.CompanyDescription
   FROM dbo.FinalMileFacilityImport fm
   INNER JOIN OrganisationRegistry.Organisations o on o.ExternalCode = fm.CompanyCode
   
   INSERT INTO OrganisationRegistry.Locations
   SELECT
   FinalMileFacilityCode as Code,
   FinalMileFacilityCode as ExternalCode,
   FinalMileFacilityDescription as Name,
   'FINAL_MILE_FACILITY' as Type,
   Line1 as AddressLine1,
   Line2 as AddressLine2,
   Line3 as AddressLine3,
   fm.City as City,
   NULL as Region,
   PostalZip as postcode,
   CountryCode as CountryCode,
   o.OrganisationId as OrganisationId,
   GETUTCDATE() as CreatedDate,
   GETUTCDATE() as LastUpdatedDate,
   NULL as DisabledDate
   FROM dbo.FinalMileFacilityImport fm
   LEFT JOIN OrganisationRegistry.Organisations o on o.ExternalCode = fm.CompanyCode
   WHERE NOT EXISTS (SELECT 1 FROM OrganisationRegistry.Locations l where l.ExternalCode = fm.FinalMileFacilityCode)
   
   COMMIT TRAN LOAD_COMPANIES;