CREATE TABLE [dbo].[IP_Images](
	[ImageSysID] [int] IDENTITY(1,1) NOT NULL,
	[SysDate] [datetime] NOT NULL,
	[StatusID] [int] NOT NULL,
	[StatusDate] [datetime] NOT NULL,
	[PathFileName] [varchar](200) NOT NULL,
	[FolderSysID] [int] NULL,
	[ImageTypeSysID] [int] NULL,
	[BarcodeString] [varchar](32) NULL,
	[LockedBySysID] [int] NULL,
	[LockDateTime] [datetime] NULL,
 CONSTRAINT [PK_IP_Images] PRIMARY KEY CLUSTERED 
(
	[ImageSysID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[IP_Images] ADD  CONSTRAINT [DF_IP_Images_SysDate]  DEFAULT (getdate()) FOR [SysDate]
GO

CREATE NONCLUSTERED INDEX [idx_IP_ImagesSelectIndex] ON [dbo].[IP_Images] 
(
	[StatusID] ASC,
	[LockedBySysID] ASC,
	[ImageTypeSysID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE  trigger [dbo].[tr_AuditImages] on [dbo].[IP_Images] for update, delete
as
declare @bit int ,
	@field int ,
	@maxfield int ,
	@char int ,
	@fieldname varchar(128) ,
	@TableName varchar(128) ,
	@PKCols varchar(1000) ,
	@sql varchar(2000), 
	@UpdateDate varchar(21) ,
	@UserID varchar(1000) ,
	@Type char(1) ,
	@PKSelect varchar(1000)
	select @TableName = 'IP_Images'
	-- date
	select 	@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
	-- User
	select 	@UserID = Coalesce(LastUpdatedByID, 0) From inserted;
	-- Action
	if exists (select * from inserted)
		select @Type = 'U'
	else
		select @Type = 'D'
	-- get list of columns
	select * into #ins from inserted
	select * into #del from deleted
	
	-- Get primary key columns for full outer join
	select	@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
	from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	where 	pk.TABLE_NAME = @TableName
	and	CONSTRAINT_TYPE = 'PRIMARY KEY'
	and	c.TABLE_NAME = pk.TABLE_NAME
	and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	-- Get primary key select for insert
	select @PKSelect = coalesce(@PKSelect+'+','') + '''' + '''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''''' 
	from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	where 	pk.TABLE_NAME = @TableName
	and	CONSTRAINT_TYPE = 'PRIMARY KEY'
	and	c.TABLE_NAME = pk.TABLE_NAME
	and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	if @PKCols is null
	begin
		raiserror('no PK on table %s', 16, -1, @TableName)
		return
	end
	
	select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
	while @field < @maxfield
	begin
		select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
		select @bit = (@field - 1 )% 8 + 1
		select @bit = power(2,@bit - 1)
		select @char = ((@field - 1) / 8) + 1
		if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
		begin

			select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
			select @sql = 		'insert IP_Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
			select @sql = @sql + 	' select ''' + @Type + ''''
			select @sql = @sql + 	',''' + @TableName + ''''
			select @sql = @sql + 	',' + @PKSelect
			select @sql = @sql + 	',''' + @fieldname + ''''
			select @sql = @sql + 	',convert(varchar(1000),d.' + @fieldname + ')'
			select @sql = @sql + 	',convert(varchar(1000),i.' + @fieldname + ')'
			select @sql = @sql + 	',''' + @UpdateDate + ''''
			select @sql = @sql + 	',' + @UserID 
			select @sql = @sql + 	' from #ins i full outer join #del d'
			select @sql = @sql + 	@PKCols
			select @sql = @sql + 	' where i.' + @fieldname + ' <> d.' + @fieldname 
			select @sql = @sql + 	' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
			select @sql = @sql + 	' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
			exec (@sql)
		end
	end

GO

CREATE           PROCEDURE [dbo].[msp_SelectNextIndexImageByFolder]
 	@LockedBySysID int,
	@StatusID int
AS
SET NOCOUNT ON
	BEGIN TRANSACTION 
Set RowCount 1
If Not exists( Select ImageSysID From IP_Images Where LockedBySysID = @LockedBySysID and  StatusID = @StatusID)
begin
		update IP_Images with(updlock, holdlock)
			Set LockedBySysID = @LockedBySysID,
				LockDateTime = GetDate(),
				LastUpdatedByID = @LockedBySysID
		Where ImageSysID = (Select Top 1 ImageSysID
					From IP_Images with(updlock, holdlock)
					Where StatusID = @StatusID AND 
					(LockedBySysID = @LockedBySysID OR LockedBySysID IS NULL Or 
					DateDiff(mi, LockDateTime, GetDate()) > 60 )
					Order By IP_Images.SysDate Asc);
End
	Select Top 1 ImageSysID, PathFileName, BarcodeString, SysDate
	From dbo.IP_Images with (NoLock) 
	Where LockedBySysID = @LockedBySysID AND StatusID = @StatusID
	Order By IP_Images.SysDate Asc;
Set RowCount 0
	COMMIT TRANSACTION
	-- Get Count of items in index.
	Select Count(*) as IndexCnt From IP_Images with (NoLock) Where StatusID = @StatusID  AND (LockedBySysID is NULL Or LockedBySysID = @LockedBySysID);

GO


INSERT INTO [dbo].[IP_Images]([StatusID],[StatusDate],[PathFileName],[FolderSysID],[BarcodeString])
VALUES (5, GetDate(), '\\Server\Folder\20060203\05.tif', 1, '')

INSERT INTO [dbo].[IP_Images]([StatusID],[StatusDate],[PathFileName],[FolderSysID],[BarcodeString])
VALUES (5, GetDate(), '\\Server\Folder\20060203\08.tif', 1, '')

INSERT INTO [dbo].[IP_Images]([StatusID],[StatusDate],[PathFileName],[FolderSysID],[BarcodeString])
VALUES (5, GetDate(), '\\Server\Folder\20060203\09.tif', 2, '')

INSERT INTO [dbo].[IP_Images]([StatusID],[StatusDate],[PathFileName],[FolderSysID],[BarcodeString])
VALUES (5, GetDate(), '\\Server\Folder\20060203\15.tif', 2, '')