CREATE TABLE [dbo].[MilitaryDPM]
(
[MilitaryDPMID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[MilitaryDPMContractFID] [int] NULL,
[DateShipmentReceived] [datetime] NULL,
[DateBaseNotified] [datetime] NULL,
[DateShipmentReleased] [datetime] NULL,
[DatePaperworkSubmitted] [datetime] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO


create trigger [dbo].[MilitaryDPMAuditFieldsChanged] on [dbo].[MilitaryDPM] 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 OrderAuditInfo
        from Deleted
        inner join OrderAuditInfo ON Deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        return  
    end
    else
    begin
        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 = convert(varchar,inserted.DatePaperworkSubmitted,126),
            ChangedIn =        case
                                when OrderAuditInfo.OrderAuditInfoID IS NULL then 'Direct SQL'
                                else OrderAuditInfo.UpdateSource
                            end
        from inserted
        inner join deleted on
            inserted.MilitaryDPMID = deleted.MilitaryDPMID and
            1 = dbo.DateFieldChanged( inserted.DatePaperworkSubmitted, deleted.DatePaperworkSubmitted )
        inner join OrderHistoryField on OrderHistoryField.FieldName = 'DatePaperworkSubmitted'
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID

         if not (update(OrderAuditInfoManualCleanup))
         begin
             -- we're done with the metadata, so clean it up.
            update MilitaryDPM
            set OrderAuditInfoFID = NULL
            from inserted
            inner join MilitaryDPM on inserted.MilitaryDPMID = MilitaryDPM.MilitaryDPMID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
         end
    end
GO

create TRIGGER [dbo].[MilitaryDPMAuditFieldsInitiallySet] ON [dbo].[MilitaryDPM] after insert
AS
set nocount on

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

    DECLARE @theAuditInfosByOrderID TABLE
    (
        OrderID INT,
        OrderAuditInfoFID BIGINT      
    )


    DECLARE @theExtAppUser INT
    
    --no reason to look up this info if it doesn't get used.
    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 = convert(varchar,inserted.DatePaperworkSubmitted,126),
        ChangedIn =         CASE
                                WHEN OrderAuditInfo.OrderAuditInfoID IS NULL THEN 'Direct SQL'
                                ELSE OrderAuditInfo.UpdateSource
                            END
    FROM inserted
    LEFT OUTER JOIN OrderAuditInfo ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    INNER JOIN OrderHistoryField ON OrderHistoryField.FieldName = 'DatePaperworkSubmitted'
    WHERE DatePaperworkSubmitted IS NOT NULL

     IF NOT EXISTS ( SELECT OrderAuditInfoFID FROM inserted WHERE OrderAuditInfoManualCleanup IS NOT NULL )
     begin
        -- we're done with the metadata, so clean it up.
        UPDATE [MilitaryDPM]
        SET OrderAuditInfoFID = NULL
        FROM inserted
        INNER JOIN MilitaryDPM ON inserted.MilitaryDPMID = MilitaryDPM.MilitaryDPMID
     end
GO

CREATE trigger [dbo].[MilitaryDPMTaskFieldChanged] on [dbo].[MilitaryDPM]
after insert, update
as
set nocount on
    if( update( DateShipmentReceived ) or update( DateBaseNotified ) or update( DateShipmentReleased ) )
    begin
        update ToDo set ToDo.DueDate =
            case DependencyDate.FieldName
                when 'DateShipmentReceived' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.DateShipmentReceived )
                when 'DateBaseNotified' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.DateBaseNotified )
                when 'DateShipmentReleased' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.DateShipmentReleased )
            end
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'MilitaryDPM'
    end
GO
ALTER TABLE [dbo].[MilitaryDPM] ADD CONSTRAINT [PK_MilitaryDPM] PRIMARY KEY CLUSTERED  ([MilitaryDPMID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryDPM_OrderAuditInfoFID] ON [dbo].[MilitaryDPM] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryDPM_OrdersFID] ON [dbo].[MilitaryDPM] ([OrdersFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryDPM] ADD CONSTRAINT [FK_MilitaryDPM_MilitaryDPMContract] FOREIGN KEY ([MilitaryDPMContractFID]) REFERENCES [dbo].[MilitaryDPMContract] ([MilitaryDPMContractID])
GO
ALTER TABLE [dbo].[MilitaryDPM] ADD CONSTRAINT [FK_MilitaryDPM_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[MilitaryDPM] ADD CONSTRAINT [FK_MilitaryDPM_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[MilitaryDPM] TO [MssExec]
GRANT INSERT ON  [dbo].[MilitaryDPM] TO [MssExec]
GRANT DELETE ON  [dbo].[MilitaryDPM] TO [MssExec]
GRANT UPDATE ON  [dbo].[MilitaryDPM] TO [MssExec]
GO