CREATE TABLE [dbo].[OrderConsign]
(
[OrderConsignID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[Consign] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsignContact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsignTypeFID] [int] NOT NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO


CREATE trigger [dbo].[OrderConsignAuditFieldsChanged] ON [dbo].[OrderConsign] after update
as
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
     delete dbo.OrderAuditInfo
        from deleted
        inner join OrderAuditInfo ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    return;
end;
else
begin
    declare @theAuditFields table
    (
        OrderConsignID int,
        FieldName varchar(128),
        Value varchar(256),
        ConsignTypeTypeFID int
    );

    insert into @theAuditFields
    (
        OrderConsignID,
        FieldName,
        Value,
        ConsignTypeTypeFID
    )
    select
            inserted.OrderConsignID,
            OrderHistoryField.FieldName,
            inserted.Consign,
            inserted.ConsignTypeFID
        from inserted
        inner join deleted on inserted.OrderConsignID = deleted.OrderConsignID and
            isnull( inserted.Consign, '' ) != isnull( deleted.Consign, '' )
        inner join OrderHistoryField on OrderHistoryField.FieldName = 'Consign'
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        where UPDATE(Consign)
    union all
        select
            inserted.OrderConsignID,
            OrderHistoryField.FieldName,
            inserted.ConsignContact,
            inserted.ConsignTypeFID
        from inserted
        inner join deleted on inserted.OrderConsignID = deleted.OrderConsignID and
            isnull( inserted.ConsignContact, '' ) != isnull( deleted.ConsignContact, '' )
        inner join OrderHistoryField on OrderHistoryField.FieldName = 'ConsignContact'
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        where UPDATE(ConsignContact)

    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,
        CustomDescription
    )
    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,
            CustomDescription = concat( iif(ConsignType.[Description] = 'Origin', 'Consignor','Consignee'), iif(OrderHistoryField.DisplayName <> 'Consign',' '+ OrderHistoryField.DisplayName,''))
    FROM @theAuditFields theAuditFields
        INNER JOIN OrderHistoryField  ON theAuditFields.FieldName = OrderHistoryField.FieldName
        INNER JOIN inserted ON theAuditFields.OrderConsignID = inserted.OrderConsignID
        INNER JOIN dbo.OrderConsign ON theAuditFields.OrderConsignID = OrderConsign.OrderConsignID
        INNER JOIN dbo.ConsignType ON ConsignType.ConsignTypeID = OrderConsign.ConsignTypeFID
        LEFT OUTER JOIN OrderAuditInfo ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;


    -- 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 dbo.OrderConsign
        set OrderAuditInfoFID = NULL
        from inserted
        inner join dbo.OrderConsign ON inserted.OrderConsignID = dbo.OrderConsign.OrderConsignID;

        delete dbo.OrderAuditInfo
        from inserted
        inner join OrderAuditInfo ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    end;

end; -- if update( OrderAuditInfoFID )
GO


CREATE trigger [dbo].[OrderConsignAuditFieldsInitiallySet] on [dbo].[OrderConsign]
after insert
as
set nocount ON
    
    declare @theAuditFields table
    (
        OrderConsignID int,
        FieldName varchar(128),
        value varchar(256)
    )

    insert into @theAuditFields
    (
        OrderConsignID,
        FieldName,
        value
    )
    select inserted.OrderConsignID, 'Consign', inserted.Consign
        from inserted
        where isnull(inserted.consign,'') <> ''
    union all
    select inserted.OrderConsignID, 'ConsignContact', inserted.ConsignContact
        from inserted
        where isnull(inserted.ConsignContact,'') <> ''

    
    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,
        CustomDescription
    )
    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,
        CustomDescription = concat( iif(ConsignType.[Description] = 'Origin', 'Consignor','Consignee'), iif(OrderHistoryField.DisplayName <> 'Consign',' '+ OrderHistoryField.DisplayName,''))
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.OrderConsignID = inserted.OrderConsignID
    inner join dbo.OrderHistoryField on OrderHistoryField.FieldName = theAuditFields.FieldName
    inner join dbo.ConsignType ON inserted.ConsignTypeFID = ConsignType.ConsignTypeID
    left outer join dbo.OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID


    -- 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 OrderConsign
    set OrderAuditInfoFID = NULL
    from inserted
    inner join OrderConsign on inserted.OrderConsignID = OrderConsign.OrderConsignID
    where inserted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is NULL
GO
ALTER TABLE [dbo].[OrderConsign] ADD CONSTRAINT [PK_OrderConsign] PRIMARY KEY CLUSTERED  ([OrderConsignID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderConsign] ADD CONSTRAINT [IX_OrderConsign_ConsignType] UNIQUE NONCLUSTERED  ([OrdersFID], [ConsignTypeFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderConsign_OrderAuditInfoFID] ON [dbo].[OrderConsign] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderConsign] ADD CONSTRAINT [FK_OrderConsign_ConsignTypeFID] FOREIGN KEY ([ConsignTypeFID]) REFERENCES [dbo].[ConsignType] ([ConsignTypeID])
GO
ALTER TABLE [dbo].[OrderConsign] ADD CONSTRAINT [FK_OrderConsign_OrdersFID] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[OrderConsign] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderConsign] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderConsign] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderConsign] TO [MssExec]
GO