CREATE TABLE [dbo].[tblOpenDocs] (

	[docid] [int] NOT NULL ,

	[spid] [smallint] NOT NULL ,

	[hostname] [nchar] (128) NOT NULL ,

	[hostprocess] [nchar] (8) NOT NULL ,

	[nt_username] [nchar] (128) NOT NULL ,

	[login_time] [datetime] NOT NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[tblOpenDocs] WITH NOCHECK ADD 

	CONSTRAINT [PK_tblOpenDocs] PRIMARY KEY  NONCLUSTERED 

	(

		[docid]

	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

GO

CREATE PROCEDURE usp_UpdateDocEditingStatus @docid int,

                                            @action tinyint,

					    @hostname varchar(128) out,

                                            @nt_username varchar(128) out,

					    @user_description varchar(128) out

--Процедура отслеживания документов, открытых в режиме редактирования


--Входные параметры:


--  @docid - идентификатор документа


--  @action - 1-документ открывается; 2- документ закрывается, 3 - документ удаляется


--Выходные параметры:


--  @hostname - рабочая станция


--  @nt_username - имя пользователя


--  @user_description - описание пользователя: Фамилия И.О. (цех)


--Результат выполнения


--  0 - документ свободен


--  -1 - документ уже используется




AS

  SET NOCOUNT ON

  IF @action = 1 BEGIN

    IF EXISTS(SELECT tblOpenDocs.*

              FROM tblOpenDocs

                   INNER JOIN master..sysprocesses sysproc ON

                   tblOpenDocs.spid = sysproc.spid AND

                   tblOpenDocs.hostname = sysproc.hostname AND

                   tblOpenDocs.hostprocess = sysproc.hostprocess AND

                   tblOpenDocs.nt_username = sysproc.nt_username AND

                   tblOpenDocs.login_time = sysproc.login_time

              WHERE tblOpenDocs.docid = @docid) BEGIN

      SELECT @hostname = tblOpenDocs.hostname,

             @nt_username = tblOpenDocs.nt_username,

             @user_description = (tblPersonalCards.colFamilyName + ' ' +

                                  UPPER(LEFT(tblPersonalCards.colName, 1)) + '.' +

                                  UPPER(LEFT(tblPersonalCards.colPatronymic, 1)) + '.') 

      FROM tblOpenDocs

           LEFT OUTER JOIN tblUsers ON

           UPPER(tblOpenDocs.nt_username) = UPPER(tblUsers.colUserName)

           LEFT OUTER JOIN tblPersonalCards ON

           tblUsers.colPersonalID = tblPersonalCards.colPersonalID

      WHERE docid = @docid

      RETURN(-1)

    END

    ELSE BEGIN

      DELETE tblOpenDocs

      WHERE docid = @docid

      INSERT INTO tblOpenDocs(docid, spid, hostname, hostprocess, nt_username, login_time)

      SELECT @docid,

             sysproc.spid,

             sysproc.hostname,

             sysproc.hostprocess,

             sysproc.nt_username,

             sysproc.login_time

      FROM master..sysprocesses sysproc

      WHERE sysproc.spid = @@spid

      RETURN(0)

    END

  END



  IF @action = 2 BEGIN

    DELETE tblOpenDocs

    WHERE docid = @docid AND

          spid = @@spid



    RETURN(0)

  END



  IF @action = 3 BEGIN

    IF EXISTS(SELECT tblOpenDocs.*

              FROM tblOpenDocs

                   INNER JOIN master..sysprocesses sysproc ON

                   tblOpenDocs.spid = sysproc.spid AND

                   tblOpenDocs.hostname = sysproc.hostname AND

                   tblOpenDocs.hostprocess = sysproc.hostprocess AND

                   tblOpenDocs.nt_username = sysproc.nt_username AND

                   tblOpenDocs.login_time = sysproc.login_time

              WHERE tblOpenDocs.docid = @docid) BEGIN

      SELECT @hostname = tblOpenDocs.hostname,

             @nt_username = tblOpenDocs.nt_username,

             @user_description = (tblPersonalCards.colFamilyName + ' ' +

                                  UPPER(LEFT(tblPersonalCards.colName, 1)) + '.' +

                                  UPPER(LEFT(tblPersonalCards.colPatronymic, 1)) + '.') 

      FROM tblOpenDocs

           LEFT OUTER JOIN tblUsers ON

           UPPER(tblOpenDocs.nt_username) = UPPER(tblUsers.colUserName)

           LEFT OUTER JOIN tblPersonalCards ON

           tblUsers.colPersonalID = tblPersonalCards.colPersonalID

      WHERE docid = @docid

      RETURN(-1)

    END

    ELSE BEGIN

      DELETE tblOpenDocs

      WHERE docid = @docid

      RETURN(0)

    END

  END

  

  SET NOCOUNT OFF

RETURN(0)



GO