-- Dirk Strauss - 21 July 2016
-- Changes for Company Setup

PRINT N'Altering [dbo].[pal_Customer]...';
GO

IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'contactMobile'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [contactMobile]  VARCHAR (30) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'typeOfBusiness'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [typeOfBusiness]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'country'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [country]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'countryCode'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [countryCode]  VARCHAR (10) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'gmtTimeZone'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [gmtTimeZone]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalStreet'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [postalStreet]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalSuburb'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [postalSuburb]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalCity'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [postalCity]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalCode'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [postalCode]  VARCHAR (10) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatRegistered'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [vatRegistered] BIT NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatRegistrationNumber'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [vatRegistrationNumber]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'logoUploaded'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [logoUploaded] VARCHAR (500) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'financialYearEnd'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [financialYearEnd]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatPeriodStartMonth'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [vatPeriodStartMonth]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalStreet'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [physicalStreet]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalSuburb'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [physicalSuburb]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalCity'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [physicalCity]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalCode'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
ALTER TABLE [dbo].[pal_Customer]
    ADD [physicalCode]  VARCHAR (10) NULL;
END
GO



PRINT N'Altering [dbo].[pal_Setup]...';
GO



IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'contactMobile'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [contactMobile]  VARCHAR (30) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'typeOfBusiness'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [typeOfBusiness]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'country'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [country]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'countryCode'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [countryCode]  VARCHAR (10) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'gmtTimeZone'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [gmtTimeZone]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalStreet'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [postalStreet]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalSuburb'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [postalSuburb]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalCity'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [postalCity]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'postalCode'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [postalCode]  VARCHAR (10) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatRegistered'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [vatRegistered] BIT NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatRegistrationNumber'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [vatRegistrationNumber]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'logoUploaded'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [logoUploaded] VARCHAR (500) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'financialYearEnd'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [financialYearEnd]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'vatPeriodStartMonth'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [vatPeriodStartMonth]  VARCHAR (50) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalStreet'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [physicalStreet]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalSuburb'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [physicalSuburb]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalCity'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [physicalCity]  VARCHAR (100) NULL;
END
GO


IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'physicalCode'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
ALTER TABLE [dbo].[pal_Setup]
    ADD [physicalCode]  VARCHAR (10) NULL;
END
GO


PRINT N'Update complete.';

GO

