CREATE TABLE [dbo].[SITInformation]
(
[SITInformationID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[SITTypeFID] [int] NOT NULL,
[AgentFID] [int] NULL,
[MilitaryCarrierFID] [int] NULL,
[VendorID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Authorization] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Days] [int] NULL,
[EstimatedInDate] [datetime] NULL,
[ActualInDate] [datetime] NULL,
[EstimatedOutDate] [datetime] NULL,
[ActualOutDate] [datetime] NULL,
[SITToPermDate] [datetime] NULL,
[DrayageMiles] [int] NULL,
[Weight] [int] NULL,
[SITNumRequestedDate] [datetime] NULL,
[SITNumReturnedDate] [datetime] NULL,
[SITNumToAgentDate] [datetime] NULL,
[DPSArrivalDate] [datetime] NULL,
[ArrivalDate] [datetime] NULL,
[Overflow] [bit] NULL,
[DANotificationDate] [datetime] NULL,
[TCNotificationDate] [datetime] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

-- Create a trigger that records audit information when a SITInformation record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
CREATE trigger [dbo].[SITInformationDeletedAudit] on [dbo].[SITInformation]
after delete
as
    set nocount on

    declare @theAuditFields table
    (
        SITInformationFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )
    declare @theMinDate datetime
    select @theMinDate = '1900-01-01'
    insert into @theAuditFields
    (
        SITInformationFID,
        FieldName,
        [Value]
    )
    select deleted.SITInformationID, 'ActualOutDate', null
    from deleted
    where isnull( deleted.ActualOutDate, @theMinDate ) != @theMinDate
    union all
    select deleted.SITInformationID, 'Authorization', null
    from deleted
    where isnull( deleted.[Authorization], '' ) <> ''
    union all
    select deleted.SITInformationID, 'ActualInDate', null
    from deleted
    where isnull( deleted.ActualInDate, @theMinDate ) != @theMinDate

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from deleted where OrderAuditInfoFID is null )
    begin
      select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end   

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
        OrderFID = deleted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =        case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
    from @theAuditFields theAuditFields
    inner join deleted on theAuditFields.SITInformationFID = deleted.SITInformationID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join SITType on deleted.SITTypeFID = SITType.SITTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and  
        SITType.[Type] = OrderHistoryField.TableType

    -- we're done with the metadata, so clean it up.
    update SITInformation
    set OrderAuditInfoFID = NULL
    from deleted
    inner join SITInformation on deleted.SITInformationID = SITInformation.SITInformationID
    where deleted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where deleted.OrderAuditInfoManualCleanup is null
GO

-- Create a trigger that records audit information when a SITInformation record is inserted.
-- Only records audit information for fields that have a value.
CREATE trigger [dbo].[SITInformationInsertedAudit] on [dbo].[SITInformation]
after insert
as
set nocount on
    declare @theAuditFields table
    (
        SITInformationFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        SITInformationFID,
        FieldName,
        [Value]
    )
    select inserted.SITInformationID, 'ActualOutDate', convert( varchar(256), inserted.ActualOutDate )
    from inserted
    where update( ActualOutDate )
    union all
    select inserted.SITInformationID, 'Authorization', inserted.[Authorization]
    from inserted
    where update( [Authorization] )
    union all
    select inserted.SITInformationID, 'ActualInDate', convert( varchar(256), inserted.ActualInDate )
    from inserted
    where update( ActualInDate )

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
    begin
      select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end   

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
    OrderFID = inserted.OrdersFID,
    ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
    ChangedOn = @theChangedOn,
    OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
    ChangedTo = theAuditFields.[Value],
    ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                    end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.SITInformationFID = inserted.SITInformationID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join SITType on inserted.SITTypeFID = SITTYpe.SITTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
        SITType.[Type] = OrderHistoryField.TableType
    where theAuditFields.[Value] is not null

    -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
    -- which case its the responsibility of the inserter to clean up this record ) .
    update SITInformation
    set OrderAuditInfoFID = NULL
    from inserted
    inner join SITInformation on inserted.SITInformationID = SITInformation.SITInformationID
    where inserted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is null
GO

CREATE trigger [dbo].[SITInformationTaskFieldChanged] on [dbo].[SITInformation]
after insert, update
as
set nocount on
    if( update( EstimatedInDate ) or update( EstimatedOutDate ) or update( ActualInDate ) or update( ActualOutDate ) or update( SITToPermDate ) )
    begin
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetSITDateByOrderAndType( ToDo.OrderID, DependencyDate.FieldName, DependencyDate.[Description] ) )
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join SITType on SITType.SITTypeID = inserted.SITTypeFID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'SITInformation' and
            SITType.[Type] = case
                when DependencyDate.[Description] like '%Orig%' then 'Origin'
                else 'Destination'
            end
    end
GO

create trigger [dbo].[SITInformationTaskFieldDeleted] on [dbo].[SITInformation]
after delete
as
set nocount on
    update ToDo set ToDo.DueDate = null
    from ToDo
    inner join deleted on deleted.OrdersFID = ToDo.OrderID
    inner join SITType on SITType.SITTypeID = deleted.SITTypeFID
    inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
    where DependencyDate.TableName = 'SITInformation' and
        SITType.[Type] = case
            when DependencyDate.[Description] like '%Orig%' then 'Origin'
            else 'Destination'
        end
GO

-- Create a trigger that records audit information when a SITInformation record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[SITInformationUpdatedAudit] on [dbo].[SITInformation]
after update
as
set nocount on
    set nocount on

    -- When we clear the OrderAuditInfoFID field, no auditing is needed.
    if( update( OrderAuditInfoFID ) and not exists ( select OrderAuditInfoFID from inserted where OrderAuditInfoFID is not null ) )
    begin
        return  
    end
    else
    begin
        declare @theAuditFields table
        (
            SITInformationFID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        declare @theMinDate datetime
        SELECT @theMinDate = '1900-01-01'
        insert into @theAuditFields
        (
            SITInformationFID,
            FieldName,
            [Value]
        )
        select inserted.SITInformationID, 'ActualOutDate', CONVERT( varchar(256), inserted.ActualOutDate )
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.ActualOutDate, @theMinDate ) != isnull( deleted.ActualOutDate, @theMinDate )
        where update( ActualOutDate )
        union all
        select inserted.SITInformationID, 'Authorization', inserted.[Authorization]
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.[Authorization], '' ) != isnull( deleted.[Authorization], '' )
        where update( [Authorization] )
        union all
        select inserted.SITInformationID, 'ActualInDate', CONVERT( varchar(256), inserted.ActualInDate )
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.ActualInDate, @theMinDate ) != isnull( deleted.ActualInDate, @theMinDate )
        where update( ActualInDate )

        declare @theChangedOn datetime
        set @theChangedOn = dbo.GetMssDateTime()

        declare @theExtAppUser int
        if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
        begin
          select @theExtAppUser = SysUser.SysUserID
            from dbo.Sysuser
            where SysUser.FIRSTNAME = 'External' and
            SysUser.LASTNAME = 'Application'
        end   

        insert into OrderHistory
        (
            OrderFID,
            ChangedBy,
            ChangedOn,
            OrderHistoryFieldFID,
            ChangedTo,
            ChangedIn
        )
        select
        OrderFID = inserted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.SITInformationFID = inserted.SITInformationID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join SITType on inserted.SITTypeFID = SITType.SITTypeID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
            SITType.[Type] = OrderHistoryField.TableType

        -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
        -- which case its the responsibility of the updater to clean up this record )
        if not(update( OrderAuditInfoManualCleanup ) )
        begin
            update SITInformation
            set OrderAuditInfoFID = NULL
            from inserted
            inner join SITInformation on inserted.SITInformationID = SITInformation.SITInformationID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [CK_SITInformation_StorageAgent] CHECK (([AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NOT NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NOT NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NOT NULL))
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [PK_SITInformation] PRIMARY KEY NONCLUSTERED  ([SITInformationID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [IX_SITInformation_OrderSITType] UNIQUE NONCLUSTERED  ([OrdersFID], [SITTypeFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SITInformation_OrderAuditInfoFID] ON [dbo].[SITInformation] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_Agent] FOREIGN KEY ([AgentFID]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_MilitaryCarrier] FOREIGN KEY ([MilitaryCarrierFID]) REFERENCES [dbo].[MilitaryCarrier] ([MilitaryCarrierID])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_SITType] FOREIGN KEY ([SITTypeFID]) REFERENCES [dbo].[SITType] ([SITTypeID])
GO
GRANT SELECT ON  [dbo].[SITInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[SITInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[SITInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[SITInformation] TO [MssExec]
GO