CREATE TABLE [dbo].[MilitaryWeight]
(
[MilitaryWeightID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[Date] [datetime] NULL,
[TruckNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalGross] [int] NULL,
[OriginalTare] [int] NULL,
[OriginalNet] [int] NULL,
[OriginalProGear] [int] NULL,
[ReweighDate] [datetime] NULL,
[ReweighNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReweighGross] [int] NULL,
[ReweighTare] [int] NULL,
[ReweighNet] [int] NULL,
[ReweighProGear] [int] NULL,
[OrderAuditInfoFID] [bigint] NULL
) ON [PRIMARY]
GO

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

    declare @theAuditFields table
    (
        MilitaryWeightFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )
    insert into @theAuditFields
    (
        MilitaryWeightFID,
        FieldName,
        [Value]
    )
    select deleted.MilitaryWeightID, 'OriginalGross', null
    from deleted
    where isnull( deleted.OriginalGross, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'OriginalNet', null
    from deleted
    where isnull( deleted.OriginalNet, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'ReweighGross', null
    from deleted
    where isnull( deleted.ReweighGross, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'ReweighNet', null
    from deleted
    where isnull( deleted.ReweighNet, -1 ) != -1

    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.MilitaryWeightFID = deleted.MilitaryWeightID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

    -- we're done with the metadata, so clean it up.
    update MilitaryWeight
    set OrderAuditInfoFID = NULL
    from deleted
    inner join MilitaryWeight on deleted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

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

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

    insert into @theAuditFields
    (
        MilitaryWeightFID,
        FieldName,
        [Value]
    )
    select inserted.MilitaryWeightID, 'OriginalGross', convert(varchar,inserted.OriginalGross)
    from inserted
    where update( OriginalGross )
    union all
    select inserted.MilitaryWeightID, 'OriginalNet', convert(varchar,inserted.OriginalNet)
    from inserted
    where update( OriginalNet )
    union all
    select inserted.MilitaryWeightID, 'ReweighGross', convert(varchar,inserted.ReweighGross)
    from inserted
    where update( ReweighGross )
    union all
    select inserted.MilitaryWeightID, 'ReweighNet', convert(varchar,inserted.ReweighNet)
    from inserted
    where update( ReweighNet )    
    union all
    select inserted.MilitaryWeightID, 'OriginalDate', convert(varchar, inserted.[Date], 126 )
    from inserted
    where update( [Date] )  
    union all
    select inserted.MilitaryWeightID, 'ReweighDate', convert(varchar, inserted.ReweighDate, 126 )
    from inserted
    where update( [ReweighDate] )
    
    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 = Value,
    ChangedIn =
        case
            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
            else OrderAuditInfo.UpdateSource
        end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.MilitaryWeightFID = inserted.MilitaryWeightID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
    where theAuditFields.[Value] is not null

    -- we're done with the metadata, so clean it up.
    update MilitaryWeight
    set OrderAuditInfoFID = NULL
    from Inserted
    inner join MilitaryWeight on Inserted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

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

CREATE trigger [dbo].[MilitaryWeightTaskFieldChanged] on [dbo].[MilitaryWeight]
after insert, update
as
set nocount on
    if( update( [Date] ) or update( ReweighDate ) )
    begin
        update ToDo set ToDo.DueDate =
            case DependencyDate.FieldName
                when 'Date' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.Date )
                when 'ReweighDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ReweighDate )
            end
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'MilitaryWeight'
    end
GO

-- Create a trigger that records audit information when a MilitaryWeight record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[MilitaryWeightUpdatedAudit] on [dbo].[MilitaryWeight] 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
        (
            MilitaryWeightFID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        insert into @theAuditFields
        (
            MilitaryWeightFID,
            FieldName,
            [Value]
        )
        select inserted.MilitaryWeightID, 'OriginalGross', convert(varchar,inserted.OriginalGross)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.OriginalGross, -1 ) != isnull( deleted.OriginalGross, -1 )
        where update( OriginalGross )
        union all
        select inserted.MilitaryWeightID, 'OriginalNet', convert(varchar,inserted.OriginalNet)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.OriginalNet, -1 ) != isnull( deleted.OriginalNet, -1 )
        where update( OriginalNet )
        union all
        select inserted.MilitaryWeightID, 'ReweighGross', convert(varchar,inserted.ReweighGross)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.ReweighGross, -1 ) != isnull( deleted.ReweighGross, -1 )
        where update( ReweighGross )
        union all
        select inserted.MilitaryWeightID, 'ReweighNet', convert(varchar,inserted.ReweighNet)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.ReweighNet, -1 ) != isnull( deleted.ReweighNet, -1 )
        where update( ReweighNet )
        union all
            select inserted.MilitaryWeightID, 'OriginalDate', convert( varchar, inserted.[Date], 126 )
            from inserted
            inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
                1 = dbo.DateFieldChanged( deleted.[Date], inserted.[Date] )
            where update ([Date])
        union all
            select inserted.MilitaryWeightID, 'ReweighDate', convert( varchar, inserted.ReweighDate, 126 )
            from inserted
            inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
                1 = dbo.DateFieldChanged( deleted.ReweighDate, inserted.ReweighDate )
            where update (ReweighDate)

        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.MilitaryWeightFID = inserted.MilitaryWeightID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

        -- we're done with the metadata, so clean it up.        
        update MilitaryWeight
        set OrderAuditInfoFID = NULL
        from Inserted
        inner join MilitaryWeight on Inserted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

        delete OrderAuditInfo
        from inserted
        inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [PK_MilitaryWeight] PRIMARY KEY NONCLUSTERED  ([MilitaryWeightID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryWeight_OrderAuditInfoFID] ON [dbo].[MilitaryWeight] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryWeight_OrdersFID] ON [dbo].[MilitaryWeight] ([OrdersFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [FK_MilitaryWeight_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [FK_MilitaryWeight_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT INSERT ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT DELETE ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT UPDATE ON  [dbo].[MilitaryWeight] TO [MssExec]
GO