amfoterius

Untitled

Jun 23rd, 2021
182
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*======================================================================================================
  2. Creator: ?
  3. Create date: ?
  4. Last Modify: Rostovsky
  5. Last Modify date: 2021-06-08
  6.  
  7. Процедура вызывается в веб-кабинете при нажатии кнопок Открыть или Скачать
  8.  
  9. 2021-06-08 Rostovsky: Добавлен параметр @UserName для таблицы логирования. Задача NPF-697
  10.  
  11. ========================================================================================================*/
  12. ALTER   PROCEDURE [dbo].[PRC_GET_IMAGE_WS] (
  13.  @ID_DOC_CARD int
  14. ,@ID_LOGIN int = null
  15. ,@IMG_CONTENT varbinary(max) OUT
  16. ,@XML_CONTENT xml            OUT
  17. ,@ID_TEMPLATE int = 0        OUT
  18. ,@PATH_FILE VARCHAR(1024) OUT
  19. ,@NAME_FILE VARCHAR(1024) OUT
  20. ,@MIME_TYPE VARCHAR(10) OUT
  21. )
  22. AS
  23. BEGIN
  24. /*
  25. DECLARE @IMG_CONTENT varbinary(max)
  26. ,@XML_CONTENT xml            
  27. ,@ID_TEMPLATE int =0      
  28. ,@PATH_FILE VARCHAR(1024)
  29. ,@NAME_FILE VARCHAR(1024)
  30. ,@MIME_TYPE VARCHAR(10)
  31.  
  32. EXEC [PRC_GET_IMAGE_WS]
  33.  @ID_DOC_CARD = 1591
  34. ,@ID_LOGIN = 1104
  35. ,@IMG_CONTENT = @IMG_CONTENT OUT
  36. ,@XML_CONTENT = @XML_CONTENT OUT
  37. ,@ID_TEMPLATE = @ID_TEMPLATE OUT
  38. ,@PATH_FILE  = @PATH_FILE OUT
  39. ,@NAME_FILE  = @NAME_FILE OUT
  40. ,@MIME_TYPE = @MIME_TYPE OUT
  41.  
  42. SELECT @IMG_CONTENT,@XML_CONTENT,@ID_TEMPLATE,@PATH_FILE,@NAME_FILE,@MIME_TYPE
  43.  
  44. select * from CT_DOC_CARDS where ID_DOC_CARD = 1700
  45. */
  46.     SET NOCOUNT ON
  47.    
  48.     DECLARE @UserName VARCHAR(128)
  49.  
  50.     SELECT @UserName = LOGIN_VALUE FROM ST_LOGINS WITH(NOLOCK) WHERE ID_LOGIN = @ID_LOGIN
  51.     IF @@ROWCOUNT = 0
  52.         SET @UserName = SUSER_NAME()
  53.  
  54.     --Логируем вызов процедуры
  55.     BEGIN TRY
  56.         INSERT INTO [FUTURE_ARCHIVE_LOG].dbo.DT_LOG_GET_IMAGES (ID_LOGIN, ID_DOC_CARD, LOGIN_NAME) VALUES (@ID_LOGIN, @ID_DOC_CARD, @UserName)
  57.     END TRY
  58.  
  59.     BEGIN CATCH
  60.         PRINT ERROR_MESSAGE()
  61.     END CATCH
  62.  
  63. DECLARE @ID_DOC_IMAGE int
  64. DECLARE @NAME_DATABASE varchar(255)
  65. DECLARE @SQLString nvarchar(4000)
  66. DECLARE @k int
  67. DECLARE @i int
  68.  
  69.     /*----------------------------------------------------------------------*/
  70.  
  71.     exec [pWebActionGetCardName] @ID_DOC_CARD, @FILE_NAME = @NAME_FILE out
  72.  
  73.     /*----------------------------------------------------------------------*/
  74.  
  75.     -- Есть ли вообще записи по выбранной карточке
  76.     SELECT @k = COUNT(ID_DOC_IMAGE)
  77.     FROM CT_DOC_IMAGES WITH (NOLOCK)
  78.     WHERE ID_DOC_CARD = @ID_DOC_CARD
  79.  
  80.     SELECT  @PATH_FILE = [PATH_FILE] + '\' + [IMG_FILE],
  81.             @MIME_TYPE = [IMG_FORMAT]
  82.     FROM CT_DOC_IMAGES WITH(NOLOCK)
  83.     WHERE ID_DOC_CARD = @ID_DOC_CARD
  84.    
  85.     IF @k > 0
  86.     BEGIN
  87.  
  88.         -- Есть ли непустые образы документов
  89.         SELECT @k = COUNT(ID_DOC_IMAGE)
  90.         FROM CT_DOC_IMAGES WITH (NOLOCK)
  91.         WHERE ID_DOC_CARD = @ID_DOC_CARD
  92.         AND (IMG_CONTENT IS NOT NULL /*OR XML_CONTENT IS NOT NULL*/)
  93.    
  94.  
  95.         IF @k > 0
  96.         BEGIN
  97.  
  98.             -- Пробуем отыскать шаблон
  99.             --SELECT TOP 1
  100.             --   @ID_TEMPLATE = isnull(ID_TEMPLATE, 0)
  101.             --  ,@XML_CONTENT = [XML_CONTENT]
  102.             --FROM CT_DOC_IMAGES WITH (NOLOCK)
  103.             --WHERE ID_DOC_CARD = @ID_DOC_CARD
  104.             --ORDER BY DATE_CREATE DESC
  105.  
  106.             --if @ID_TEMPLATE > 0                         -- Найден шаблон
  107.             --  SELECT
  108.             --       @IMG_CONTENT = [TEMPLATE]
  109.             --    FROM [dbo].[DT_DOC_TEMPLATE]
  110.             --    WHERE  [ID_TEMPLATE] = @k
  111.     --      ELSE                                        -- Шаблона нет, возвращается образ документа
  112.                 SELECT TOP 1 @IMG_CONTENT = IMG_CONTENT                        
  113.                 FROM CT_DOC_IMAGES WITH (NOLOCK)
  114.                 WHERE ID_DOC_CARD = @ID_DOC_CARD
  115.                 AND IMG_CONTENT IS NOT NULL
  116.                 ORDER BY DATE_CREATE DESC
  117.  
  118.         END
  119.         ELSE
  120.         BEGIN
  121.  
  122.             -- Если нет в т. CT_DOC_IMAGES, предпринимается попытка отыскать в других базах данных
  123.             SELECT @k = COUNT(ID_DOC_IMAGE)
  124.             FROM CT_DOC_IMAGES WITH(NOLOCK)
  125.             WHERE ID_DOC_CARD = @ID_DOC_CARD
  126.             AND ID_DATABASE IS NOT NULL
  127.  
  128.             SET @i = 1
  129.             SET @IMG_CONTENT = NULL
  130.  
  131.             WHILE @i <= @k AND @IMG_CONTENT IS NULL
  132.             BEGIN
  133.                 SELECT TOP 1 @ID_DOC_IMAGE = c.ID_DOC_IMAGE,
  134.                              @NAME_DATABASE = d.NAME_DATABASE
  135.                 FROM CT_DOC_IMAGES c WITH (NOLOCK)
  136.                 LEFT OUTER JOIN CT_DATABASES d
  137.                 ON ISNULL(c.ID_DATABASE, 0) = d.ID_DATABASE
  138.                 WHERE c.ID_DOC_CARD = @ID_DOC_CARD
  139.                 ORDER BY c.DATE_CREATE
  140.  
  141.                 SET @SQLString = 'SELECT @IMG_CONTENT = IMG_CONTENT FROM ' + CONCAT(@NAME_DATABASE, '.dbo.CT_DOC_IMAGES') + ' WHERE ID_DOC_IMAGE = ' + CONVERT(varchar, @ID_DOC_IMAGE)
  142.                 --print @SQLString
  143.                 EXEC sp_executesql @SQLString, N'@IMG_CONTENT varbinary(max) OUTPUT', @IMG_CONTENT OUTPUT
  144.                
  145.                 SET @i = @i + 1
  146.             END
  147.         END
  148.     END
  149.     ELSE
  150.     BEGIN
  151.         SET @IMG_CONTENT = NULL
  152.         SET @XML_CONTENT = NULL
  153.     END
  154. END
RAW Paste Data