CREATE TABLE [dbo].[MilitaryOrder]
(
[MilitaryOrderID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[GBLNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1840Score] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1840Submitted] [datetime] NULL,
[1840Scored] [datetime] NULL,
[SSNNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RankGrade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MilitaryBranchOfServiceFID] [int] NULL,
[MilitaryCodeOfServiceFID] [int] NULL,
[ContractLotNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceOrderNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MilitaryCarrierFID] [int] NULL,
[OrderingBaseFID] [int] NULL,
[OrderingStateFID] [int] NULL,
[OriginBaseFID] [int] NULL,
[OriginStateFID] [int] NULL,
[DestinationBaseFID] [int] NULL,
[DestinationStateFID] [int] NULL,
[Channel] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SurveyCompleted] [datetime] NULL,
[TspScore] [smallint] NULL,
[PackQuality] [smallint] NULL,
[LoadCourtesy] [smallint] NULL,
[PickupTimeliness] [smallint] NULL,
[DestCourtesy] [smallint] NULL,
[DelvryTimeliness] [smallint] NULL,
[OverallStatisfaction] [smallint] NULL,
[PackAgentFID] [int] NULL,
[PickupAgentFID] [int] NULL,
[DelvryAgentFID] [int] NULL,
[FollowUpSurvey] [bit] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[SsnLast4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[MilitaryOrderAuditFieldsInitiallySet] on [dbo].[MilitaryOrder]
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 = inserted.GBLNumber,
        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 = 'GBLNumber'
    where GBLNumber is not null

    -- we're done with the metadata, so clean it up.
    update MilitaryOrder
    set OrderAuditInfoFID = NULL
    from inserted
    inner join MilitaryOrder on inserted.MilitaryOrderID = MilitaryOrder.MilitaryOrderID

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

CREATE trigger [dbo].[MilitaryOrderOrderAuditFieldsChanged] on [dbo].[MilitaryOrder]
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
        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 = inserted.GBLNumber,
            ChangedIn =        case
                                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                                else OrderAuditInfo.UpdateSource
                            end
        from inserted
        inner join deleted on
            inserted.MilitaryOrderID = deleted.MilitaryOrderID and
            isnull( inserted.GBLNumber, '' ) != isnull( deleted.GBLNumber, '' )
        inner join OrderHistoryField on OrderHistoryField.FieldName = 'GBLNumber'
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID

        -- we're done with the metadata, so clean it up.
        update MilitaryOrder
        set OrderAuditInfoFID = NULL
        from inserted
        inner join MilitaryOrder on inserted.MilitaryOrderID = MilitaryOrder.MilitaryOrderID

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

CREATE trigger [dbo].[MilitaryOrderTaskFieldChanged] on [dbo].[MilitaryOrder]
after insert, update
as
set nocount on
    if( update( [1840Scored] ) or update( [1840Submitted] ) )
    begin
        update ToDo set ToDo.DueDate =
            case DependencyDate.FieldName
                when '1840Scored' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.[1840Scored] )
                when '1840Submitted' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.[1840Submitted] )
            end
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'MilitaryOrder'
    end
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [PK_MilitaryOrder] PRIMARY KEY NONCLUSTERED  ([MilitaryOrderID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [IX_MilitaryOrder_OrdersFID] UNIQUE NONCLUSTERED  ([OrdersFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryOrder_GBLNumber] ON [dbo].[MilitaryOrder] ([GBLNumber]) INCLUDE ([OrdersFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryOrder_OrderAuditInfoFID] ON [dbo].[MilitaryOrder] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_DestinationBase] FOREIGN KEY ([DestinationBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_DestinationState] FOREIGN KEY ([DestinationStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryBranchOfService] FOREIGN KEY ([MilitaryBranchOfServiceFID]) REFERENCES [dbo].[MilitaryBranchOfService] ([MilitaryBranchOfServiceID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryCarrier] FOREIGN KEY ([MilitaryCarrierFID]) REFERENCES [dbo].[MilitaryCarrier] ([MilitaryCarrierID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryCodeOfServiceFID] FOREIGN KEY ([MilitaryCodeOfServiceFID]) REFERENCES [dbo].[MilitaryCodeOfService] ([MilitaryCodeOfServiceID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderingBase] FOREIGN KEY ([OrderingBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderingState] FOREIGN KEY ([OrderingStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OriginBase] FOREIGN KEY ([OriginBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OriginState] FOREIGN KEY ([OriginStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
GRANT SELECT ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT INSERT ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT DELETE ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT UPDATE ON  [dbo].[MilitaryOrder] TO [MssExec]
GO