Last active
January 1, 2016 07:29
-
-
Save zv0r/8111651 to your computer and use it in GitHub Desktop.
Переносит записи из АФ5 в АИС Архив
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| IF EXISTS ( | |
| SELECT * FROM sysobjects WHERE id = object_id(N'dbo.getUnitRubric') | |
| AND xtype IN (N'FN', N'IF', N'TF') | |
| ) | |
| DROP FUNCTION dbo.getUnitRubric | |
| GO | |
| IF EXISTS ( | |
| SELECT * FROM sysobjects WHERE id = object_id(N'dbo.convertDocType') | |
| AND xtype IN (N'FN', N'IF', N'TF') | |
| ) | |
| DROP FUNCTION dbo.convertDocType | |
| GO | |
| IF EXISTS ( | |
| SELECT * FROM sysobjects WHERE id = object_id(N'dbo.convertYearToDate') | |
| AND xtype IN (N'FN', N'IF', N'TF') | |
| ) | |
| DROP FUNCTION dbo.convertYearToDate | |
| GO | |
| /* | |
| * | |
| * Функция, вычисляющая иерархию уровней разделов описи. | |
| * В "АИС Архив" в карточке дела есть 4 текстовых поля, куда заносятся разделы описи. | |
| * В базе АФ5 разделы описи организованы в виде дерева. | |
| * Данная функция на входе принимает ID раздела описи, | |
| * а на выходе выдает название раздела определенного уровня, указываемого в параметрах. | |
| * Параметры: | |
| * @isn_inventory_cls int ID раздела описи | |
| * @level int Уровень описи, из которого будет возвращаться название раздела описи | |
| * | |
| */ | |
| CREATE FUNCTION dbo.getUnitRubric (@isn_inventory_cls int, @level int) | |
| RETURNS nvarchar(MAX) | |
| WITH EXECUTE AS CALLER | |
| AS | |
| BEGIN | |
| DECLARE @retVal nvarchar(MAX); | |
| WITH CTE AS | |
| ( | |
| --инициализация | |
| SELECT ISN_INVENTORY_CLS, NAME, ISN_HIGH_INVENTORY_CLS, 1 as Level | |
| FROM [ArchiveFund5].[dbo].[tblINVENTORY_STRUCTURE] | |
| WHERE ISN_INVENTORY_CLS = @isn_inventory_cls AND (NAME <> '...' AND NAME <> 'Структура описи') | |
| UNION ALL | |
| --рекурсия | |
| SELECT e.ISN_INVENTORY_CLS,e.NAME, e.ISN_HIGH_INVENTORY_CLS, m.Level + 1 | |
| FROM [ArchiveFund5].[dbo].[tblINVENTORY_STRUCTURE] e INNER JOIN CTE m | |
| ON e.ISN_INVENTORY_CLS = m.ISN_HIGH_INVENTORY_CLS AND (e.NAME <> '...' AND e.NAME <> 'Структура описи') | |
| ) | |
| SELECT @retVal = NAME FROM (SELECT ROW_NUMBER() OVER(ORDER BY Level Desc) AS InventoryLevel, * FROM CTE) as Levels where InventoryLevel = @level; | |
| RETURN(@retVal); | |
| END; | |
| GO | |
| /* | |
| * | |
| * Функция конвертирует ID типа документов из занчения, | |
| * указанного в АФ5 в соответствующее значение в АИС Архив. | |
| * | |
| * Соответствие типа документации в АФ5 и АИС | |
| * | |
| * АФ5 АИС Наименование | |
| * 1 1 Управленческая документация | |
| * 2 4 Документы по личному составу | |
| * 3 2 Документы личного происхождения | |
| * 4 3 Научно-техническая документация | |
| * 5 6 Фотодокументы | |
| * 6 7 Фонодокументы | |
| * 7 5 Кинодокументы | |
| * 8 8 Видеодокументы | |
| * 9 12 Микроформы на правах подлинника (НО В АИС ЭТО БУДЕТ НАЗЫВАТЬСЯ ПРЕДМЕТЫ!!!) | |
| * 10 9 Машиночитаемые документы | |
| * | |
| * Параметры: | |
| * af5_doc_type int ID типа документации в БД АФ5 | |
| */ | |
| CREATE FUNCTION dbo.convertDocType (@af5_doc_type int) | |
| RETURNS int | |
| WITH EXECUTE AS CALLER | |
| AS | |
| BEGIN | |
| DECLARE @retVal int; | |
| SELECT @retVal = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(NULLIF(@af5_doc_type, ''), '-'), '-', 'one'), 2, 'four'), 3, 'two'), 4, 'three'), 5, 'six'), 6, 'seven'), 7, 'five'), 9, 'twelve'), 10, 'nine'), 'one', 1), 'two', 2), 'three', 3), 'four', 4), 'five', 5), 'six', 6), 'seven', 7), 'nine', 9), 'twelve', 12); | |
| RETURN(@retVal); | |
| END; | |
| GO | |
| /* | |
| * | |
| * Функция преобразует года из крайних дат АФ5 в полные даты для АИС Архив | |
| * | |
| * Параметры: | |
| * @year sql_variant Год крайней даты из базы АФ5 | |
| * @month int Номер месяца, к которому преобразуется дата | |
| * @day int День, к которому преобразуется дата | |
| * | |
| */ | |
| CREATE FUNCTION dbo.convertYearToDate (@cYear varchar(4), @cMonth int, @cDay int) | |
| RETURNS datetime | |
| WITH EXECUTE AS CALLER | |
| AS | |
| BEGIN | |
| DECLARE @retVal datetime; | |
| SET @cYear = | |
| CASE | |
| WHEN (@cYear LIKE '%*%') THEN 0 | |
| ELSE CAST(ISNULL(@cYear, '') as INT) | |
| END; | |
| SET @retVal = | |
| CASE | |
| WHEN (@cYear < 1753) THEN NULL | |
| ELSE CONVERT(datetime, CAST(@cYear AS VARCHAR(4)) + '-' + CAST(@cMonth AS VARCHAR(2)) + '-' + CAST(@cDay AS VARCHAR(2)) + ' 00:00:00', 20) | |
| END; | |
| RETURN(@retVal); | |
| END; | |
| GO | |
| -- ID архива, в который заливаются данные | |
| DECLARE @archID INT | |
| SET @archID = 2 | |
| -- Рубрика, в которую заливаются фонды | |
| DECLARE @rubID INT | |
| SET @rubID = 2 | |
| BEGIN TRANSACTION AfAisConvert; | |
| -- Загрузка фондов в АИС | |
| INSERT INTO [AISArchive].[dbo].[tblFunds] ( | |
| RubricId | |
| ,Name | |
| ,ShortName | |
| ,Number | |
| ,Letter1 | |
| ,Letter2 | |
| ,DocumentsStartDate | |
| ,DocumentsLastDate | |
| ,ArchiveFilesQuantity | |
| ,InventoriesQuantity | |
| ,HistoryInformation | |
| ,Annotation | |
| ,FundCategoryId | |
| ,DocumentationTypeId | |
| ,Remark | |
| ,CreationTime | |
| ,DeletionTime | |
| ,Deleted | |
| ,ArchiveId | |
| ) | |
| SELECT | |
| @rubID as RubricId | |
| ,ISNULL(NULLIF(aff.[FUND_NAME_FULL], ''), '-') as Name | |
| ,ISNULL(NULLIF(aff.[FUND_NAME_SHORT], ''), '-') as ShortName | |
| ,aff.[FUND_NUM_2] as Number | |
| ,ISNULL(aff.[FUND_NUM_1], '') as Letter1 | |
| ,ISNULL(aff.[FUND_NUM_3], '') as Letter2 | |
| ,dbo.convertYearToDate(aff.[DOC_START_YEAR], 1, 1) as DocumentsStartDate | |
| ,dbo.convertYearToDate(aff.[DOC_END_YEAR], 12, 31) as DocumentsLastDate | |
| ,0 as ArchiveFilesQuantity | |
| ,0 as InventoriesQuantity | |
| ,ISNULL(aff.[FUND_HISTORY], '') as HistoryInformation | |
| ,ISNULL(aff.[ANNOTATE], '') as Annotation | |
| ,NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(aff.[FUND_CATEGORY], 'a'), '1'), 'b'), '2'), 'c'), '3'), 'd') as FundCategoryId | |
| ,dbo.convertDocType(aff.[ISN_DOC_TYPE]) as DocumentationTypeId | |
| ,ISNULL(aff.[NOTE], '') as Remark | |
| ,CURRENT_TIMESTAMP as CreationTime | |
| ,NULL as DeletionTime | |
| ,0 as Deleted | |
| ,@archID as ArchiveId | |
| FROM | |
| [ArchiveFund5].[dbo].[tblFUND] aff | |
| WHERE | |
| aff.[Deleted] = 0 | |
| -- Едва ли будут закрытые фонды, но мало ли | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| -- Выбывшие фонды не включаются | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| AND NOT EXISTS ( | |
| SELECT | |
| * | |
| FROM | |
| [AISArchive].[dbo].[tblFunds] aisf | |
| WHERE | |
| aisf.[Number] = aff.[FUND_NUM_2] | |
| AND ISNULL(NULLIF(aisf.[Letter1], ''), 0) = ISNULL(NULLIF(aff.[FUND_NUM_1], ''), 0) | |
| AND ISNULL(NULLIF(aisf.[Letter2], ''), 0) = ISNULL(NULLIF(aff.[FUND_NUM_3], ''), 0) | |
| AND aisf.[ArchiveId] = @archID | |
| ) | |
| -- Загрузка описей в АИС | |
| INSERT INTO [AISArchive].[dbo].[tblInventories] ( | |
| FundId | |
| ,Name | |
| ,Number | |
| ,Letter | |
| ,DocumentsStartDate | |
| ,DocumentsLastDate | |
| ,ArchiveFilesQuantity | |
| ,DocumentationTypeId | |
| ,Annotation | |
| ,CreationTime | |
| ,DeletionTime | |
| ,Deleted | |
| ,ArchiveId | |
| ,IsHidden | |
| ) | |
| SELECT | |
| aisf.[Id] as FundId | |
| ,ISNULL(NULLIF(afi.[INVENTORY_NAME], ''), '-') as Name | |
| ,afi.[INVENTORY_NUM_1] as Number | |
| ,(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + ISNULL(afi.[INVENTORY_NUM_3], ''), ' т. '), ''), '')) as Letter | |
| ,dbo.convertYearToDate(afi.[DOC_START_YEAR], 1, 1) as DocumentsStartDate | |
| ,dbo.convertYearToDate(afi.[DOC_END_YEAR], 12, 31) as DocumentsLastDate | |
| ,0 as ArchiveFilesQuantity | |
| ,dbo.convertDocType(aff.[ISN_DOC_TYPE]) as DocumentationTypeId | |
| ,ISNULL(afi.[ANNOTATE], '') as Annotation | |
| ,CURRENT_TIMESTAMP as CreationTime | |
| ,NULL as DeletionTime | |
| ,0 as Deleted | |
| ,@archID as ArchiveId | |
| ,0 as IsHidden | |
| FROM | |
| [ArchiveFund5].[dbo].[tblINVENTORY] afi | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[ISN_FUND] = afi.[ISN_FUND] | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[Number] = aff.[FUND_NUM_2] | |
| AND aisf.[Deleted] = 0 | |
| AND aisf.[Letter1] = ISNULL(aff.[FUND_NUM_1], '') | |
| AND aisf.[Letter2] = ISNULL(aff.[FUND_NUM_3], '') | |
| AND aisf.[ArchiveId] = @archID | |
| WHERE | |
| afi.[Deleted] <> 1 | |
| -- Описи с грифом "Секретно" и "Частично секретно" не включаются | |
| -- Здесь русская ЭС и английская СИ | |
| AND (ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%C%' AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%С%') | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| AND afi.[ISN_SECURLEVEL] <> 2 | |
| -- Описи из выбывших фондов не включаются | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| -- Выбывшие описи не включаются | |
| AND ISNULL(afi.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afi.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') LIKE '%проверено%')) | |
| AND NOT EXISTS ( | |
| SELECT | |
| * | |
| FROM | |
| [AISArchive].[dbo].[tblInventories] aisi | |
| WHERE | |
| aisi.[FundId] = aisf.[Id] | |
| --AND aisi.[Name] = ISNULL(NULLIF(afi.[INVENTORY_NAME], ''), '-') | |
| AND aisi.[Number] = afi.[INVENTORY_NUM_1] | |
| AND ISNULL(NULLIF(aisi.[Letter], ''), '_') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + CAST(afi.[INVENTORY_NUM_3] AS NVARCHAR), ' т. '), ''), ''), '_') | |
| AND aisi.ArchiveId = @archID | |
| ) | |
| -- Загрузка единиц хранения в АИС | |
| INSERT INTO [AISArchive].[dbo].[tblArchiveFiles] ( | |
| InventoryId | |
| ,Name | |
| ,Number | |
| ,Letter | |
| ,DocumentsStartDate | |
| ,DocumentsLastDate | |
| ,DocumentsQuantity | |
| ,PagesQuantity | |
| ,DocumentationTypeId | |
| ,ValueId | |
| ,MediaType | |
| ,Rubric1 | |
| ,Rubric2 | |
| ,Rubric3 | |
| ,Rubric4 | |
| ,Remark | |
| ,ShowToAll | |
| ,CreationTime | |
| ,DeletionTime | |
| ,Deleted | |
| ,ArchiveId | |
| ,ArchiveStorageId | |
| ,IsHidden | |
| ) | |
| SELECT | |
| aisi.[Id] as InventoryId | |
| ,ISNULL(NULLIF(afu.[NAME], ''), '-') as Name | |
| ,afu.[UNIT_NUM_1] as Number | |
| ,NULLIF(afu.[UNIT_NUM_2], '') as Letter | |
| ,dbo.convertYearToDate(afu.[START_YEAR], 1, 1) as DocumentsStartDate | |
| ,dbo.convertYearToDate(afu.[END_YEAR], 12, 31) as DocumentsLastDate | |
| ,0 as DocumentsQuantity | |
| ,ISNULL(NULLIF(afu.[PAGE_COUNT], ''), 0) as PagesQuantity | |
| ,dbo.convertDocType(aff.[ISN_DOC_TYPE]) as DocumentationTypeId | |
| /* | |
| * Соответствие категории дела в АФ5 и АИС | |
| * | |
| * АФ5 АИС Наименование | |
| * NULL 1 В АФ5 наименования нет, в АИС значится как "обычное" | |
| * 1 В АФ5 наименования нет, в АИС значится как "обычное" | |
| * a 4 уникальная | |
| * b 2 ценная | |
| * c 3 ОЦД | |
| */ | |
| ,REPLACE(REPLACE(REPLACE(ISNULL(NULLIF(afu.[UNIT_CATEGORY], ''), 1), 'a', 4), 'b', 2), 'c', 3) as ValueId | |
| /* | |
| * Соответствие типа носителя дела в АФ5 и АИС | |
| * | |
| * АФ5 АИС Наименование | |
| * NULL 1 традиционный | |
| * 1 традиционный | |
| * T 1 традиционный | |
| * E 2 электронный | |
| */ | |
| ,REPLACE(REPLACE(ISNULL(NULLIF(afu.[MEDIUM_TYPE], ''), 'T'), 'T', 1), 'E', 2) as MediaType | |
| ,dbo.getUnitRubric(afu.ISN_INVENTORY_CLS, 1) as Rubric1 | |
| ,dbo.getUnitRubric(afu.ISN_INVENTORY_CLS, 2) as Rubric2 | |
| ,dbo.getUnitRubric(afu.ISN_INVENTORY_CLS, 3) as Rubric3 | |
| ,dbo.getUnitRubric(afu.ISN_INVENTORY_CLS, 4) as Rubric4 | |
| ,NULLIF(afu.[NOTE], '') as Remark | |
| ,0 as ShowToAll | |
| ,CURRENT_TIMESTAMP as CreationTime | |
| ,NULL as DeletionTime | |
| ,0 as Deleted | |
| ,@archID as ArchiveId | |
| -- Архивохранилище по умолчанию не задано | |
| ,1 as ArchiveStorageId | |
| ,0 as IsHidden | |
| FROM | |
| [ArchiveFund5].[dbo].[tblUNIT] afu | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblINVENTORY] afi ON | |
| afi.[ISN_INVENTORY] = afu.[ISN_INVENTORY] | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[ISN_FUND] = afi.[ISN_FUND] | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[ArchiveId] = @archID | |
| AND aisf.[Deleted] = 0 | |
| AND aisf.[Number] = aff.[FUND_NUM_2] | |
| AND aisf.[Letter1] = ISNULL(aff.[FUND_NUM_1], '') | |
| AND aisf.[Letter2] = ISNULL(aff.[FUND_NUM_3], '') | |
| LEFT JOIN [AISArchive].[dbo].[tblInventories] aisi ON | |
| aisi.[ArchiveId] = @archID | |
| AND aisi.[Deleted] = 0 | |
| AND aisi.[FundId] = aisf.[Id] | |
| AND aisi.[Number] = afi.[INVENTORY_NUM_1] | |
| AND ISNULL(NULLIF(aisi.[Letter], ''), '_') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + CAST(afi.[INVENTORY_NUM_3] AS NVARCHAR), ' т. '), ''), ''), '_') | |
| WHERE | |
| -- Единицы хранения с пометкой "Удалено" не включаются | |
| afu.[Deleted] <> 1 | |
| -- Выбывшие единицы хранения не включаются | |
| AND afu.[IS_LOST] = 'N' | |
| -- Описи с грифом "Секретно" и "Частично секретно" не включаются | |
| -- Здесь русская ЭС и английская СИ | |
| AND (ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%C%' AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%С%') | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| AND afi.[ISN_SECURLEVEL] <> 2 | |
| AND afu.[ISN_SECURLEVEL] <> 2 | |
| -- Единицы хранения из выбывших фондов не включаются | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| -- Единицы хранения из выбывших описей не включаются | |
| AND ISNULL(afi.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afi.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afu.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afu.[NOTE], '') LIKE '%параграф%' AND ISNULL(afu.[NOTE], '') LIKE '%проверено%')) | |
| AND NOT EXISTS( | |
| SELECT | |
| * | |
| FROM | |
| [AISArchive].[dbo].[tblArchiveFiles] aisu | |
| WHERE | |
| aisu.[Number] = afu.[UNIT_NUM_1] | |
| AND ISNULL(NULLIF(aisu.[Letter], ''), '_') = ISNULL(NULLIF(afu.[UNIT_NUM_2], ''), '_') | |
| AND aisu.[InventoryId] = aisi.[Id] | |
| AND aisu.[ArchiveId] = @archID | |
| ) | |
| -- Удалить засекреченные и выбывшие единицы хранения из АИС Архив | |
| -- Также удалить единицы хранения, которые есть в АИС, но нет в АФ5, | |
| -- ибо нехрен всюду свои руки совать - пусть только в АФ заполняют | |
| UPDATE | |
| [AISArchive].[dbo].[tblArchiveFiles] | |
| SET | |
| Deleted = 1 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisu.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblArchiveFiles] aisu | |
| LEFT JOIN [AISArchive].[dbo].[tblInventories] aisi ON | |
| aisi.[Id] = aisu.[InventoryId] | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[Id] = aisi.[FundId] | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblINVENTORY] afi ON | |
| afi.[ISN_FUND] = aff.[ISN_FUND] | |
| AND afi.[INVENTORY_NUM_1] = aisi.[Number] | |
| AND ISNULL(aisi.[Letter], '') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + ISNULL(afi.[INVENTORY_NUM_3], ''), ' т. '), ''), ''), '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblUNIT] afu ON | |
| afu.[ISN_INVENTORY] = afi.[ISN_INVENTORY] | |
| AND afu.[Deleted] <> 1 | |
| AND ISNULL(afu.[UNIT_NUM_1], '') = ISNULL(aisu.[Number], '') | |
| AND ISNULL(afu.[UNIT_NUM_2], '') = ISNULL(aisu.[Letter], '') | |
| WHERE | |
| aisu.[ArchiveId] = @archID | |
| AND ( | |
| afu.[ISN_UNIT] IS NULL | |
| OR afu.[IS_LOST] = 'Y' | |
| -- Это "ЭС" | |
| OR ISNULL(afi.[INVENTORY_NUM_2], '') LIKE '%С%' | |
| -- Это "СИ" | |
| OR ISNULL(afi.[INVENTORY_NUM_2], '') LIKE '%C%' | |
| OR aff.[ISN_SECURLEVEL] = 2 | |
| OR afi.[ISN_SECURLEVEL] = 2 | |
| OR afu.[ISN_SECURLEVEL] = 2 | |
| OR ISNULL(aff.[PRESENCE_FLAG], '') = 'b' | |
| OR ISNULL(afi.[PRESENCE_FLAG], '') = 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') NOT LIKE '%проверено%') | |
| OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') NOT LIKE '%проверено%') | |
| OR (ISNULL(afu.[NOTE], '') LIKE '%параграф%' AND ISNULL(afu.[NOTE], '') NOT LIKE '%проверено%') | |
| ) | |
| ) | |
| -- Удалить засекреченные и выбывшие описи из АИС Архив | |
| -- Также удалить описи, которые есть в АИС, но нет в АФ5. | |
| UPDATE | |
| [AISArchive].[dbo].[tblInventories] | |
| SET | |
| Deleted = 1 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisi.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblInventories] aisi | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[Id] = aisi.[FundId] | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblINVENTORY] afi ON | |
| afi.[ISN_FUND] = aff.[ISN_FUND] | |
| AND afi.[Deleted] <> 1 | |
| AND afi.[INVENTORY_NUM_1] = aisi.[Number] | |
| AND ISNULL(aisi.[Letter], '') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + ISNULL(afi.[INVENTORY_NUM_3], ''), ' т. '), ''), ''), '') | |
| WHERE | |
| aisi.[ArchiveId] = @archID | |
| AND ( | |
| afi.[ISN_INVENTORY] IS NULL | |
| -- Это "ЭС" | |
| OR ISNULL(afi.[INVENTORY_NUM_2], '') LIKE '%С%' | |
| -- Это "СИ" | |
| OR ISNULL(afi.[INVENTORY_NUM_2], '') LIKE '%C%' | |
| OR aff.[ISN_SECURLEVEL] = 2 | |
| OR afi.[ISN_SECURLEVEL] = 2 | |
| OR ISNULL(aff.[PRESENCE_FLAG], '') = 'b' | |
| OR ISNULL(afi.[PRESENCE_FLAG], '') = 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') NOT LIKE '%проверено%') | |
| OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') NOT LIKE '%проверено%') | |
| ) | |
| ) | |
| -- Удалить засекреченные и выбывшие фонды из АИС Архив | |
| -- Также удалить фонды, которые есть в АИС, но нет в АФ5. | |
| UPDATE | |
| [AISArchive].[dbo].[tblFunds] | |
| SET | |
| Deleted = 1 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisf.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblFunds] aisf | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| AND aff.[Deleted] <> 1 | |
| WHERE | |
| aisf.[ArchiveId] = @archID | |
| AND ( | |
| aff.[ISN_FUND] IS NULL | |
| OR aff.[ISN_SECURLEVEL] = 2 | |
| OR ISNULL(aff.[PRESENCE_FLAG], '') = 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') NOT LIKE '%проверено%') | |
| ) | |
| ) | |
| -- Вернуть фонды в АИС Архив. Возвращаются фонды, у которых | |
| -- параметр Deleted сменился с 1 на 0, у которых сменилась | |
| -- характеристика секретности на открытую, | |
| -- а также фонды, с которых снялась отметка "Выбыл" | |
| UPDATE | |
| [AISArchive].[dbo].[tblFunds] | |
| SET | |
| Deleted = 0 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisf.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblFunds] aisf | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| WHERE | |
| aisf.[ArchiveId] = @archID | |
| AND aisf.[Deleted] = 1 | |
| AND aff.[Deleted] <> 1 | |
| AND aff.[ISN_FUND] IS NOT NULL | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| ) | |
| -- Вернуть описи в АИС Архив. Возвращаются описи, у которых | |
| -- параметр Deleted сменился с 1 на 0, у которых сменилась | |
| -- характеристика секретности на открытую, | |
| -- а также описи, с которых снялась отметка "Выбыл" | |
| UPDATE | |
| [AISArchive].[dbo].[tblInventories] | |
| SET | |
| Deleted = 0 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisi.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblInventories] aisi | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[Id] = aisi.[FundId] | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblINVENTORY] afi ON | |
| afi.[ISN_FUND] = aff.[ISN_FUND] | |
| AND afi.[INVENTORY_NUM_1] = aisi.[Number] | |
| AND ISNULL(aisi.[Letter], '') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + ISNULL(afi.[INVENTORY_NUM_3], ''), ' т. '), ''), ''), '') | |
| WHERE | |
| aisi.[ArchiveId] = @archID | |
| AND aisi.[Deleted] = 1 | |
| AND afi.[Deleted] <> 1 | |
| AND afi.[ISN_INVENTORY] IS NOT NULL | |
| -- Это "ЭС" | |
| AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%С%' | |
| -- Это "СИ" | |
| AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%C%' | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| AND afi.[ISN_SECURLEVEL] <> 2 | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| AND ISNULL(afi.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afi.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') LIKE '%проверено%')) | |
| ) | |
| -- Вернуть единицы хранения в АИС Архив. Возвращаются дела, у которых | |
| -- параметр Deleted сменился с 1 на 0, у которых сменилась | |
| -- характеристика секретности на открытую, | |
| -- а также дела, с которых снялась отметка "Выбыл" | |
| UPDATE | |
| [AISArchive].[dbo].[tblArchiveFiles] | |
| SET | |
| Deleted = 0 | |
| WHERE | |
| Id IN ( | |
| SELECT | |
| aisu.[Id] | |
| FROM | |
| [AISArchive].[dbo].[tblArchiveFiles] aisu | |
| LEFT JOIN [AISArchive].[dbo].[tblInventories] aisi ON | |
| aisi.[Id] = aisu.[InventoryId] | |
| LEFT JOIN [AISArchive].[dbo].[tblFunds] aisf ON | |
| aisf.[Id] = aisi.[FundId] | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblFUND] aff ON | |
| aff.[FUND_NUM_2] = aisf.[Number] | |
| AND ISNULL(aff.[FUND_NUM_1], '') = ISNULL(aisf.[Letter1], '') | |
| AND ISNULL(aff.[FUND_NUM_3], '') = ISNULL(aisf.[Letter2], '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblINVENTORY] afi ON | |
| afi.[ISN_FUND] = aff.[ISN_FUND] | |
| AND afi.[INVENTORY_NUM_1] = aisi.[Number] | |
| AND ISNULL(aisi.[Letter], '') = ISNULL(NULLIF(ISNULL(afi.[INVENTORY_NUM_2], '') + ISNULL(NULLIF(' т. ' + ISNULL(afi.[INVENTORY_NUM_3], ''), ' т. '), ''), ''), '') | |
| LEFT JOIN [ArchiveFund5].[dbo].[tblUNIT] afu ON | |
| afu.[ISN_INVENTORY] = afi.[ISN_INVENTORY] | |
| AND afu.[Deleted] <> 1 | |
| AND ISNULL(afu.[UNIT_NUM_1], '') = ISNULL(aisu.[Number], '') | |
| AND ISNULL(afu.[UNIT_NUM_2], '') = ISNULL(aisu.[Letter], '') | |
| WHERE | |
| aisu.[ArchiveId] = @archID | |
| AND aisu.[Deleted] = 1 | |
| AND afu.[Deleted] <> 1 | |
| AND afu.[ISN_UNIT] IS NOT NULL | |
| AND ISNULL(afu.[IS_LOST], '') <> 'Y' | |
| -- Это "ЭС" | |
| AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%С%' | |
| -- Это "СИ" | |
| AND ISNULL(afi.[INVENTORY_NUM_2], '') NOT LIKE '%C%' | |
| AND aff.[ISN_SECURLEVEL] <> 2 | |
| AND afi.[ISN_SECURLEVEL] <> 2 | |
| AND afu.[ISN_SECURLEVEL] <> 2 | |
| AND ISNULL(aff.[PRESENCE_FLAG], '') <> 'b' | |
| AND ISNULL(afi.[PRESENCE_FLAG], '') <> 'b' | |
| -- если присутствует примечание "параграф", то с ним обязательно должно быть проставлено примечание "проверено" | |
| AND (ISNULL(aff.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(aff.[NOTE], '') LIKE '%параграф%' AND ISNULL(aff.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afi.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afi.[NOTE], '') LIKE '%параграф%' AND ISNULL(afi.[NOTE], '') LIKE '%проверено%')) | |
| AND (ISNULL(afu.[NOTE], '') NOT LIKE '%параграф%' OR (ISNULL(afu.[NOTE], '') LIKE '%параграф%' AND ISNULL(afu.[NOTE], '') LIKE '%проверено%')) | |
| ) | |
| -- Обновить данные о количестве занесенных описей и дел | |
| -- в карточки фондов архивного учреждения. | |
| -- В принципе, здесь не задействуется база АФ5, поэтому можно | |
| -- пересчитать данные вообще для всех записей всех архивов. | |
| -- Для этого в блоке WHERE запроса нужно будет убрать условие | |
| -- ArchiveId = @archId | |
| UPDATE | |
| [AISArchive].[dbo].[tblFunds] | |
| SET | |
| InventoriesQuantity = (SELECT COUNT(*) FROM [AISArchive].[dbo].[tblInventories] WHERE FundId = aisf.[Id] AND Deleted <> 1) | |
| ,ArchiveFilesQuantity = (SELECT COUNT(*) FROM [AISArchive].[dbo].[tblArchiveFiles] WHERE InventoryId IN (SELECT Id FROM [AISArchive].[dbo].[tblInventories] WHERE FundId = aisf.[Id] AND Deleted <> 1) AND Deleted <> 1) | |
| FROM [AISArchive].[dbo].[tblFunds] aisf | |
| WHERE | |
| aisf.[Id] = Id | |
| AND aisf.[ArchiveId] = @archId | |
| -- Обновить данные о количестве занесенных дел | |
| -- в карточки описей архивного учреждения. | |
| -- В принципе, здесь не задействуется база АФ5, поэтому можно | |
| -- пересчитать данные вообще для всех записей всех архивов. | |
| -- Для этого в блоке WHERE запроса нужно будет убрать условие | |
| -- ArchiveId = @archId | |
| UPDATE | |
| [AISArchive].[dbo].[tblInventories] | |
| SET | |
| ArchiveFilesQuantity = (SELECT COUNT(*) FROM [AISArchive].[dbo].[tblArchiveFiles] WHERE InventoryId = aisi.[Id] AND Deleted <> 1) | |
| FROM [AISArchive].[dbo].[tblInventories] aisi | |
| WHERE | |
| aisi.[Id] = Id | |
| AND aisi.[ArchiveId] = @archId | |
| COMMIT TRANSACTION AfAisConvert; | |
| GO | |
| -- Удалим функции, чтобы не светились | |
| DROP FUNCTION dbo.getUnitRubric | |
| DROP FUNCTION dbo.convertDocType | |
| DROP FUNCTION dbo.convertYearToDate |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment