Пожалуйста, сначала прочитайте статью полностью и только потом приступайте к выполнению шагов!
Для того, чтобы скопировать программу страхования Витакарты из одной базы данных в другую нужно проделать следующие действия:
1. Выполнить скрипт — Warranty_Program_ExportToSQL_SP. Он добавит хранимую процедуру, которая на основе выбранной программы страхования сформирует XML-документ.
Содержимое скрипта приведено ниже:
USE [AKUZDB] GO /****** Object: StoredProcedure [dbo].[spx_Warranty_Program_ExportToSQL] Script Date: 18.02.2018 18:03:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spx_Warranty_Program_ExportToSQL] @WarrantyProgramID uniqueidentifier, @xml varchar(max) out AS BEGIN SET @xml = ( SELECT wp.[WarrantyProgramID] , wp.[Name] , wp.[ValidThrough] , wp.[PackName] , wp.[PackNamePrefix] , ( SELECT wpg.[WarrantyProgramGroupID] , wpg.[Name] , wpg.[Entity] , wpg.[WarrantyProgram] , wpg.[Criteria] , wpg.[DatePath] , wpg.[DateLexem] , wpg.[CriteriaExist] , wpg.[PackNameLexem] , wpg.[CorrCriteria] , ( SELECT wpi.[WarrantyProgramItemID] , wpi.[Code] , wpi.[WarrantyProgramGroup] , wpi.[Name] , wpi.[FullName] , wpi.[Criteria] , wpi.[Priority] , wpi.[ServiceCode] , wpi.[Code2] , ( SELECT wpir.[WarrantyProgramItemRateID] , wpir.[EffectiveDate] , wpir.[Rate] , wpir.[WarrantyProgramItem] , wpir.[Ratio] , wpir.[RateExtra] FROM [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] wpir WHERE wpir.WarrantyProgramItem = wpi.WarrantyProgramItemID FOR xml PATH('WARRANTY_PROGRAM_ITEM_RATE'), TYPE ) FROM akuz.T_WARRANTY_PROGRAM_ITEM wpi WHERE wpi.WarrantyProgramGroup = wpg.WarrantyProgramGroupID FOR xml PATH('WARRANTY_PROGRAM_ITEM'), TYPE ) , ( SELECT wpgr.[WarrantyProgramGroupRateID] , wpgr.[EffectiveDate] , wpgr.[WarrantyProgramGroup] , wpgr.[Ratio2] FROM [AKUZ].[T_WARRANTY_PROGRAM_GROUP_RATE] wpgr WHERE wpgr.WarrantyProgramGroup = wpg.WarrantyProgramGroupID FOR xml PATH('WARRANTY_PROGRAM_GROUP_RATE'), TYPE ) , ( SELECT wpf.[WarrantyProgramFieldID] , wpf.[Name] , wpf.[Lexem] , wpf.[WarrantyProgramGroup] , wpf.[DataType] , wpf.[Condition] FROM [AKUZ].[T_WARRANTY_PROGRAM_FIELD] wpf WHERE wpf.WarrantyProgramGroup = wpg.WarrantyProgramGroupID FOR xml PATH('WARRANTY_PROGRAM_FIELD'), TYPE ) FROM [AKUZ].[T_WARRANTY_PROGRAM_GROUP] wpg WHERE wpg.WarrantyProgram = wp.WarrantyProgramID FOR xml PATH('WARRANTY_PROGRAM_GROUP'), TYPE ) ,( SELECT wpr.[WarrantyProgramRatioID] , wpr.[WarrantyProgram] , wpr.[Ratio] , wpr.[BeginDate] FROM [AKUZ].[T_WARRANTY_PROGRAM_RATIO] wpr WHERE wpr.WarrantyProgram = wp.WarrantyProgramID FOR xml PATH('WARRANTY_PROGRAM_RATIO'), TYPE ) FROM [AKUZ].[T_WARRANTY_PROGRAM] wp WHERE wp.WarrantyProgramID = @WarrantyProgramID FOR xml PATH('WARRANTY_PROGRAM'), root('root') ) END
2. В самой программе Витакарта нужно зайти в «Администрирование» — «Дополнительные команды пользовательского интерфейса» и создать новую команду.
3. Для вновь созданной команды добавляем два параметра:
Первый параметр:
Второй параметр:
XSLT-шаблон для выходного параметра (xml), этот шаблон преобразует XML-файл, полученный в результате выполнения хранимой процедуры spx_Warranty_Program_ExportToSQL, в готовый к применению SQL-файл, который при выполнении на другой базе данных добавит программу страхования или если программа страхования с таким идентификатором уже была в базе, то выполнится update:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://mycompany.com/mynamespace" xmlns:js="urn:the-xml-files:xslt-csharp" xmlns:ntd="http://www.ntd.ru/ntd/" version="1.0"> <xsl:output method="text"/> <xsl:template match="root"> <xsl:for-each select="WARRANTY_PROGRAM"> declare @WarrantyProgramID uniqueidentifier set @WarrantyProgramID = '<xsl:value-of select="WarrantyProgramID"/>' IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM] WHERE WarrantyProgramID = @WarrantyProgramID) INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM] (WarrantyProgramID, Name, ValidThrough, PackName, PackNamePrefix) VALUES ( @WarrantyProgramID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="ValidThrough"/>', <xsl:choose> <xsl:when test="string(PackName)">'<xsl:value-of select="PackName"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">'<xsl:value-of select="PackNamePrefix"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE [AKUZ].[T_WARRANTY_PROGRAM] SET Name = '<xsl:value-of select="Name"/>', ValidThrough = '<xsl:value-of select="ValidThrough"/>', <xsl:choose> <xsl:when test="string(PackName)">PackName = '<xsl:value-of select="PackName"/>',</xsl:when> <xsl:otherwise>PackName = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">PackNamePrefix = '<xsl:value-of select="PackNamePrefix"/>'</xsl:when> <xsl:otherwise>PackNamePrefix = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramID = @WarrantyProgramID <xsl:for-each select="WARRANTY_PROGRAM_GROUP"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_GROUP] WHERE WarrantyProgramGroupID = '<xsl:value-of select="WarrantyProgramGroupID"/>') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP (WarrantyProgramGroupID, Name, Entity, WarrantyProgram, Criteria, DatePath, DateLexem, CriteriaExist, PackNameLexem, CorrCriteria) VALUES ( '<xsl:value-of select="WarrantyProgramGroupID"/>', '<xsl:value-of select="Name"/>', '<xsl:value-of select="Entity"/>', '<xsl:value-of select="WarrantyProgram"/>', <xsl:choose> <xsl:when test="string(Criteria)">'<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">'<xsl:value-of select="DatePath"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">'<xsl:value-of select="DateLexem"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">'<xsl:value-of select="CriteriaExist"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">'<xsl:value-of select="PackNameLexem"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">'<xsl:value-of select="CorrCriteria"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_GROUP SET Name = '<xsl:value-of select="Name"/>', Entity = '<xsl:value-of select="Entity"/>', WarrantyProgram = '<xsl:value-of select="WarrantyProgram"/>', <xsl:choose> <xsl:when test="string(Criteria)">Criteria = '<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>Criteria = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">DatePath = '<xsl:value-of select="DatePath"/>',</xsl:when> <xsl:otherwise>DatePath = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">DateLexem = '<xsl:value-of select="DateLexem"/>',</xsl:when> <xsl:otherwise>DateLexem = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">CriteriaExist = '<xsl:value-of select="CriteriaExist"/>',</xsl:when> <xsl:otherwise>CriteriaExist = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">PackNameLexem = '<xsl:value-of select="PackNameLexem"/>',</xsl:when> <xsl:otherwise>PackNameLexem = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">CorrCriteria = '<xsl:value-of select="CorrCriteria"/>'</xsl:when> <xsl:otherwise>CorrCriteria = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramGroupID = '<xsl:value-of select="WarrantyProgramGroupID"/>' <xsl:for-each select="WARRANTY_PROGRAM_ITEM"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_ITEM] WHERE WarrantyProgramItemID = '<xsl:value-of select="WarrantyProgramItemID"/>') INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM] (WarrantyProgramItemID, Code, WarrantyProgramGroup, Name, FullName, Criteria, Priority, ServiceCode, Code2) VALUES ( '<xsl:value-of select="WarrantyProgramItemID"/>', '<xsl:value-of select="Code"/>', '<xsl:value-of select="WarrantyProgramGroup"/>', '<xsl:value-of select="Name"/>', '<xsl:value-of select="FullName"/>', <xsl:choose> <xsl:when test="string(Criteria)">'<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> '<xsl:value-of select="Priority"/>', <xsl:choose> <xsl:when test="string(ServiceCode)">'<xsl:value-of select="ServiceCode"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> '<xsl:value-of select="Code2"/>' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_ITEM SET Code = '<xsl:value-of select="Code"/>', WarrantyProgramGroup = '<xsl:value-of select="WarrantyProgramGroup"/>', Name = '<xsl:value-of select="Name"/>', FullName = '<xsl:value-of select="FullName"/>', <xsl:choose> <xsl:when test="string(Criteria)">Criteria = '<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>Criteria = NULL,</xsl:otherwise> </xsl:choose> Priority = '<xsl:value-of select="Priority"/>', <xsl:choose> <xsl:when test="string(ServiceCode)">ServiceCode = '<xsl:value-of select="ServiceCode"/>',</xsl:when> <xsl:otherwise>ServiceCode = NULL,</xsl:otherwise> </xsl:choose> Code2 = '<xsl:value-of select="Code2"/>' WHERE WarrantyProgramItemID = '<xsl:value-of select="WarrantyProgramItemID"/>' <xsl:for-each select="WARRANTY_PROGRAM_ITEM_RATE"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] WHERE WarrantyProgramItemRateID = '<xsl:value-of select="WarrantyProgramItemRateID"/>') INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] (WarrantyProgramItemRateID, EffectiveDate, Rate, WarrantyProgramItem, Ratio, RateExtra) VALUES ( '<xsl:value-of select="WarrantyProgramItemRateID"/>', '<xsl:value-of select="EffectiveDate"/>', '<xsl:value-of select="Rate"/>', '<xsl:value-of select="WarrantyProgramItem"/>', '<xsl:value-of select="Ratio"/>', <xsl:choose> <xsl:when test="string(RateExtra)">'<xsl:value-of select="RateExtra"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_ITEM_RATE SET EffectiveDate = '<xsl:value-of select="EffectiveDate"/>', Rate = '<xsl:value-of select="Rate"/>', WarrantyProgramItem = '<xsl:value-of select="WarrantyProgramItem"/>', Ratio = '<xsl:value-of select="Ratio"/>', <xsl:choose> <xsl:when test="string(RateExtra)">RateExtra = '<xsl:value-of select="RateExtra"/>'</xsl:when> <xsl:otherwise>RateExtra = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramItemRateID = '<xsl:value-of select="WarrantyProgramItemRateID"/>' </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_GROUP_RATE"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE WHERE WarrantyProgramGroupRateID = '<xsl:value-of select="WarrantyProgramGroupRateID"/>') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE (WarrantyProgramGroupRateID, EffectiveDate, WarrantyProgramGroup, Ratio2) VALUES ( '<xsl:value-of select="WarrantyProgramGroupRateID"/>', '<xsl:value-of select="EffectiveDate"/>', '<xsl:value-of select="WarrantyProgramGroup"/>', '<xsl:value-of select="Ratio2"/>' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE SET EffectiveDate = '<xsl:value-of select="EffectiveDate"/>', WarrantyProgramGroup = '<xsl:value-of select="WarrantyProgramGroup"/>', Ratio2 = '<xsl:value-of select="Ratio2"/>' WHERE WarrantyProgramGroupRateID = '<xsl:value-of select="WarrantyProgramGroupRateID"/>' </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_FIELD"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_FIELD WHERE WarrantyProgramFieldID = '<xsl:value-of select="WarrantyProgramFieldID"/>') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_FIELD(WarrantyProgramFieldID, Name, Lexem, WarrantyProgramGroup, DataType, Condition) VALUES ( '<xsl:value-of select="WarrantyProgramFieldID"/>', '<xsl:value-of select="Name"/>', '<xsl:value-of select="Lexem"/>', '<xsl:value-of select="WarrantyProgramGroup"/>', '<xsl:value-of select="DataType"/>', <xsl:choose> <xsl:when test="string(Condition)">'<xsl:value-of select="Condition"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_FIELD SET Name = '<xsl:value-of select="Name"/>', Lexem = '<xsl:value-of select="Lexem"/>', WarrantyProgramGroup = '<xsl:value-of select="WarrantyProgramGroup"/>', DataType = '<xsl:value-of select="DataType"/>', <xsl:choose> <xsl:when test="string(Condition)">Condition = '<xsl:value-of select="Condition"/>'</xsl:when> <xsl:otherwise>Condition = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramFieldID = '<xsl:value-of select="WarrantyProgramFieldID"/>' </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_RATIO"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_RATIO WHERE WarrantyProgramRatioID = '<xsl:value-of select="WarrantyProgramRatioID"/>') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_RATIO (WarrantyProgramRatioID, WarrantyProgram, Ratio, BeginDate) VALUES ( '<xsl:value-of select="WarrantyProgramRatioID"/>', '<xsl:value-of select="WarrantyProgram"/>', '<xsl:value-of select="Ratio"/>', '<xsl:value-of select="BeginDate"/>' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_RATIO SET WarrantyProgram = '<xsl:value-of select="WarrantyProgram"/>', Ratio = '<xsl:value-of select="Ratio"/>', BeginDate = '<xsl:value-of select="BeginDate"/>' WHERE WarrantyProgramRatioID = '<xsl:value-of select="WarrantyProgramRatioID"/>' </xsl:for-each> GO </xsl:for-each> </xsl:template> </xsl:stylesheet>
Скачать в виде XSLT-файла — Warranty_Program_ExportToSQL(update mode)_XSLT
Следующий вариант XSLT шаблона подготовит скрипт, который каждый раз будет добавлять новую программу страхования, дописывая в конце имени текущую дату и время. Удобно в том случае, если нужно оставить предыдущую программу страхования без изменений и, просто, добавить новую.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://mycompany.com/mynamespace" xmlns:js="urn:the-xml-files:xslt-csharp" xmlns:ntd="http://www.ntd.ru/ntd/" version="1.0"> <xsl:output method="text"/> <xsl:template match="root"> <xsl:for-each select="WARRANTY_PROGRAM"> USE AKUZDB declare @WarrantyProgramID uniqueidentifier declare @WarrantyProgramGroupID uniqueidentifier declare @WarrantyProgramItemID uniqueidentifier declare @WarrantyProgramItemRateID uniqueidentifier declare @WarrantyProgramGroupRateID uniqueidentifier declare @WarrantyProgramFieldID uniqueidentifier declare @WarrantyProgramRatioID uniqueidentifier set @WarrantyProgramID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM] (WarrantyProgramID, Name, ValidThrough, PackName, PackNamePrefix) VALUES ( @WarrantyProgramID, '<xsl:value-of select="Name"/> РѕС‚ '+CAST(GETDATE() AS nvarchar(30)), '<xsl:value-of select="ValidThrough"/>', <xsl:choose> <xsl:when test="string(PackName)">'<xsl:value-of select="PackName"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">'<xsl:value-of select="PackNamePrefix"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) <xsl:for-each select="WARRANTY_PROGRAM_GROUP"> set @WarrantyProgramGroupID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_GROUP] (WarrantyProgramGroupID, Name, Entity, WarrantyProgram, Criteria, DatePath, DateLexem, CriteriaExist, PackNameLexem, CorrCriteria) VALUES ( @WarrantyProgramGroupID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="Entity"/>', @WarrantyProgramID, <xsl:choose> <xsl:when test="string(Criteria)">'<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">'<xsl:value-of select="DatePath"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">'<xsl:value-of select="DateLexem"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">'<xsl:value-of select="CriteriaExist"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">'<xsl:value-of select="PackNameLexem"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">'<xsl:value-of select="CorrCriteria"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) <xsl:for-each select="WARRANTY_PROGRAM_ITEM"> set @WarrantyProgramItemID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM] (WarrantyProgramItemID, Code, WarrantyProgramGroup, Name, FullName, Criteria, Priority, ServiceCode, Code2) VALUES ( @WarrantyProgramItemID, '<xsl:value-of select="Code"/>', @WarrantyProgramGroupID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="FullName"/>', <xsl:choose> <xsl:when test="string(Criteria)">'<xsl:value-of select="Criteria"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> '<xsl:value-of select="Priority"/>', <xsl:choose> <xsl:when test="string(ServiceCode)">'<xsl:value-of select="ServiceCode"/>',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> '<xsl:value-of select="Code2"/>' ) <xsl:for-each select="WARRANTY_PROGRAM_ITEM_RATE"> set @WarrantyProgramItemRateID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] (WarrantyProgramItemRateID, EffectiveDate, Rate, WarrantyProgramItem, Ratio, RateExtra) VALUES ( @WarrantyProgramItemRateID, '<xsl:value-of select="EffectiveDate"/>', '<xsl:value-of select="Rate"/>', @WarrantyProgramItemID, '<xsl:value-of select="Ratio"/>', <xsl:choose> <xsl:when test="string(RateExtra)">'<xsl:value-of select="RateExtra"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_GROUP_RATE"> set @WarrantyProgramGroupRateID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE (WarrantyProgramGroupRateID, EffectiveDate, WarrantyProgramGroup, Ratio2) VALUES ( @WarrantyProgramGroupRateID, '<xsl:value-of select="EffectiveDate"/>', @WarrantyProgramGroupID, '<xsl:value-of select="Ratio2"/>' ) </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_FIELD"> set @WarrantyProgramFieldID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_FIELD(WarrantyProgramFieldID, Name, Lexem, WarrantyProgramGroup, DataType, Condition) VALUES ( @WarrantyProgramFieldID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="Lexem"/>', @WarrantyProgramGroupID, '<xsl:value-of select="DataType"/>', <xsl:choose> <xsl:when test="string(Condition)">'<xsl:value-of select="Condition"/>'</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_RATIO"> set @WarrantyProgramRatioID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_RATIO (WarrantyProgramRatioID, WarrantyProgram, Ratio, BeginDate) VALUES ( @WarrantyProgramRatioID, @WarrantyProgramID, '<xsl:value-of select="Ratio"/>', '<xsl:value-of select="BeginDate"/>' ) </xsl:for-each> GO </xsl:for-each> </xsl:template> </xsl:stylesheet>
Скачать в виде XSLT-файла — Warranty_Program_ExportToSQL(insert mode)_XSLT
Примечание:Дополнительную команду пользовательского интерфейса, c XSLT шаблоном в режиме обновления (update mode), также можно добавить скриптом, но потребуется Refresh, сначала серверного холдера ExtraUICommandHolder, а затем клиентского холдера с таким же именем:
USE AKUZDB declare @ENTITY_WARRANTY_PROGRAM int; declare @AdministratorRoleID uniqueidentifier; select top 1 @ENTITY_WARRANTY_PROGRAM = EntityID from VCLib.T_ENTITIES where Entname = 'WARRANTY_PROGRAM' and [Schema] = 'AKUZ'; select top 1 @AdministratorRoleID = RoleID from VCLib.T_ROLES where RoleName = 'Администратор' IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_COMMAND] WHERE ExtraUiCommandID = 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3') INSERT INTO [VCLib].[T_EXTRA_UI_COMMAND] (ExtraUiCommandID, Name, FilterValue, Context, OpenContext, Priority, StyleType, Entities, CommandEntity, CommandType, SameObject, StoredProcedure, DisplayAttributes, ExecutionType, RemoteCommandID, Credentials) VALUES ( 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'Экспорт в SQL (update mode)', NULL, '*', '', '0', NULL, @ENTITY_WARRANTY_PROGRAM, @ENTITY_WARRANTY_PROGRAM, '4', '0', 'spx_Warranty_Program_ExportToSql', NULL, '0', NULL, NULL ) IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_ACTION_PARAMS] WHERE ExtraUiActionParamsID = 'C6E71FA2-CAE7-4F8E-90F5-45F877A1E853') INSERT INTO [VCLib].[T_EXTRA_UI_ACTION_PARAMS] (ExtraUiActionParamsID, ExtraUiCommand, Name, ParamDescription, IsOutput, ParamValue, ResultType, OutputOrder, FileName, Verb) VALUES ( 'C6E71FA2-CAE7-4F8E-90F5-45F877A1E853', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'xml', 'xml', '1', '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://mycompany.com/mynamespace" xmlns:js="urn:the-xml-files:xslt-csharp" xmlns:ntd="http://www.ntd.ru/ntd/" version="1.0"> <xsl:output method="text"/> <xsl:template match="root"> <xsl:for-each select="WARRANTY_PROGRAM"> declare @WarrantyProgramID uniqueidentifier set @WarrantyProgramID = ''<xsl:value-of select="WarrantyProgramID"/>'' IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM] WHERE WarrantyProgramID = @WarrantyProgramID) INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM] (WarrantyProgramID, Name, ValidThrough, PackName, PackNamePrefix) VALUES ( @WarrantyProgramID, ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="ValidThrough"/>'', <xsl:choose> <xsl:when test="string(PackName)">''<xsl:value-of select="PackName"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">''<xsl:value-of select="PackNamePrefix"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE [AKUZ].[T_WARRANTY_PROGRAM] SET Name = ''<xsl:value-of select="Name"/>'', ValidThrough = ''<xsl:value-of select="ValidThrough"/>'', <xsl:choose> <xsl:when test="string(PackName)">PackName = ''<xsl:value-of select="PackName"/>'',</xsl:when> <xsl:otherwise>PackName = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">PackNamePrefix = ''<xsl:value-of select="PackNamePrefix"/>''</xsl:when> <xsl:otherwise>PackNamePrefix = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramID = @WarrantyProgramID <xsl:for-each select="WARRANTY_PROGRAM_GROUP"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_GROUP] WHERE WarrantyProgramGroupID = ''<xsl:value-of select="WarrantyProgramGroupID"/>'') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP (WarrantyProgramGroupID, Name, Entity, WarrantyProgram, Criteria, DatePath, DateLexem, CriteriaExist, PackNameLexem, CorrCriteria) VALUES ( ''<xsl:value-of select="WarrantyProgramGroupID"/>'', ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="Entity"/>'', ''<xsl:value-of select="WarrantyProgram"/>'', <xsl:choose> <xsl:when test="string(Criteria)">''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">''<xsl:value-of select="DatePath"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">''<xsl:value-of select="DateLexem"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">''<xsl:value-of select="CriteriaExist"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">''<xsl:value-of select="PackNameLexem"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">''<xsl:value-of select="CorrCriteria"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_GROUP SET Name = ''<xsl:value-of select="Name"/>'', Entity = ''<xsl:value-of select="Entity"/>'', WarrantyProgram = ''<xsl:value-of select="WarrantyProgram"/>'', <xsl:choose> <xsl:when test="string(Criteria)">Criteria = ''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>Criteria = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">DatePath = ''<xsl:value-of select="DatePath"/>'',</xsl:when> <xsl:otherwise>DatePath = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">DateLexem = ''<xsl:value-of select="DateLexem"/>'',</xsl:when> <xsl:otherwise>DateLexem = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">CriteriaExist = ''<xsl:value-of select="CriteriaExist"/>'',</xsl:when> <xsl:otherwise>CriteriaExist = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">PackNameLexem = ''<xsl:value-of select="PackNameLexem"/>'',</xsl:when> <xsl:otherwise>PackNameLexem = NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">CorrCriteria = ''<xsl:value-of select="CorrCriteria"/>''</xsl:when> <xsl:otherwise>CorrCriteria = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramGroupID = ''<xsl:value-of select="WarrantyProgramGroupID"/>'' <xsl:for-each select="WARRANTY_PROGRAM_ITEM"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_ITEM] WHERE WarrantyProgramItemID = ''<xsl:value-of select="WarrantyProgramItemID"/>'') INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM] (WarrantyProgramItemID, Code, WarrantyProgramGroup, Name, FullName, Criteria, Priority, ServiceCode, Code2) VALUES ( ''<xsl:value-of select="WarrantyProgramItemID"/>'', ''<xsl:value-of select="Code"/>'', ''<xsl:value-of select="WarrantyProgramGroup"/>'', ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="FullName"/>'', <xsl:choose> <xsl:when test="string(Criteria)">''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> ''<xsl:value-of select="Priority"/>'', <xsl:choose> <xsl:when test="string(ServiceCode)">''<xsl:value-of select="ServiceCode"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> ''<xsl:value-of select="Code2"/>'' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_ITEM SET Code = ''<xsl:value-of select="Code"/>'', WarrantyProgramGroup = ''<xsl:value-of select="WarrantyProgramGroup"/>'', Name = ''<xsl:value-of select="Name"/>'', FullName = ''<xsl:value-of select="FullName"/>'', <xsl:choose> <xsl:when test="string(Criteria)">Criteria = ''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>Criteria = NULL,</xsl:otherwise> </xsl:choose> Priority = ''<xsl:value-of select="Priority"/>'', <xsl:choose> <xsl:when test="string(ServiceCode)">ServiceCode = ''<xsl:value-of select="ServiceCode"/>'',</xsl:when> <xsl:otherwise>ServiceCode = NULL,</xsl:otherwise> </xsl:choose> Code2 = ''<xsl:value-of select="Code2"/>'' WHERE WarrantyProgramItemID = ''<xsl:value-of select="WarrantyProgramItemID"/>'' <xsl:for-each select="WARRANTY_PROGRAM_ITEM_RATE"> IF NOT EXISTS (SELECT * FROM [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] WHERE WarrantyProgramItemRateID = ''<xsl:value-of select="WarrantyProgramItemRateID"/>'') INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] (WarrantyProgramItemRateID, EffectiveDate, Rate, WarrantyProgramItem, Ratio, RateExtra) VALUES ( ''<xsl:value-of select="WarrantyProgramItemRateID"/>'', ''<xsl:value-of select="EffectiveDate"/>'', ''<xsl:value-of select="Rate"/>'', ''<xsl:value-of select="WarrantyProgramItem"/>'', ''<xsl:value-of select="Ratio"/>'', <xsl:choose> <xsl:when test="string(RateExtra)">''<xsl:value-of select="RateExtra"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_ITEM_RATE SET EffectiveDate = ''<xsl:value-of select="EffectiveDate"/>'', Rate = ''<xsl:value-of select="Rate"/>'', WarrantyProgramItem = ''<xsl:value-of select="WarrantyProgramItem"/>'', Ratio = ''<xsl:value-of select="Ratio"/>'', <xsl:choose> <xsl:when test="string(RateExtra)">RateExtra = ''<xsl:value-of select="RateExtra"/>''</xsl:when> <xsl:otherwise>RateExtra = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramItemRateID = ''<xsl:value-of select="WarrantyProgramItemRateID"/>'' </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_GROUP_RATE"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE WHERE WarrantyProgramGroupRateID = ''<xsl:value-of select="WarrantyProgramGroupRateID"/>'') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE (WarrantyProgramGroupRateID, EffectiveDate, WarrantyProgramGroup, Ratio2) VALUES ( ''<xsl:value-of select="WarrantyProgramGroupRateID"/>'', ''<xsl:value-of select="EffectiveDate"/>'', ''<xsl:value-of select="WarrantyProgramGroup"/>'', ''<xsl:value-of select="Ratio2"/>'' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE SET EffectiveDate = ''<xsl:value-of select="EffectiveDate"/>'', WarrantyProgramGroup = ''<xsl:value-of select="WarrantyProgramGroup"/>'', Ratio2 = ''<xsl:value-of select="Ratio2"/>'' WHERE WarrantyProgramGroupRateID = ''<xsl:value-of select="WarrantyProgramGroupRateID"/>'' </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_FIELD"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_FIELD WHERE WarrantyProgramFieldID = ''<xsl:value-of select="WarrantyProgramFieldID"/>'') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_FIELD(WarrantyProgramFieldID, Name, Lexem, WarrantyProgramGroup, DataType, Condition) VALUES ( ''<xsl:value-of select="WarrantyProgramFieldID"/>'', ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="Lexem"/>'', ''<xsl:value-of select="WarrantyProgramGroup"/>'', ''<xsl:value-of select="DataType"/>'', <xsl:choose> <xsl:when test="string(Condition)">''<xsl:value-of select="Condition"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_FIELD SET Name = ''<xsl:value-of select="Name"/>'', Lexem = ''<xsl:value-of select="Lexem"/>'', WarrantyProgramGroup = ''<xsl:value-of select="WarrantyProgramGroup"/>'', DataType = ''<xsl:value-of select="DataType"/>'', <xsl:choose> <xsl:when test="string(Condition)">Condition = ''<xsl:value-of select="Condition"/>''</xsl:when> <xsl:otherwise>Condition = NULL</xsl:otherwise> </xsl:choose> WHERE WarrantyProgramFieldID = ''<xsl:value-of select="WarrantyProgramFieldID"/>'' </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_RATIO"> IF NOT EXISTS (SELECT * FROM AKUZ.T_WARRANTY_PROGRAM_RATIO WHERE WarrantyProgramRatioID = ''<xsl:value-of select="WarrantyProgramRatioID"/>'') INSERT INTO AKUZ.T_WARRANTY_PROGRAM_RATIO (WarrantyProgramRatioID, WarrantyProgram, Ratio, BeginDate) VALUES ( ''<xsl:value-of select="WarrantyProgramRatioID"/>'', ''<xsl:value-of select="WarrantyProgram"/>'', ''<xsl:value-of select="Ratio"/>'', ''<xsl:value-of select="BeginDate"/>'' ) ELSE UPDATE AKUZ.T_WARRANTY_PROGRAM_RATIO SET WarrantyProgram = ''<xsl:value-of select="WarrantyProgram"/>'', Ratio = ''<xsl:value-of select="Ratio"/>'', BeginDate = ''<xsl:value-of select="BeginDate"/>'' WHERE WarrantyProgramRatioID = ''<xsl:value-of select="WarrantyProgramRatioID"/>'' </xsl:for-each> GO </xsl:for-each> </xsl:template> </xsl:stylesheet>', '1', '0', '"WarrantyProgram.sql"', NULL ) ,( '19747175-173B-4737-85C3-4B9820F31730', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'WarrantyProgramID', 'Идентификатор', '0', '<lx:Path OwnerQueryLevel="0" xmlns:lx="lexem"> <lx:StepString name="WarrantyProgramID" /> </lx:Path>', NULL, NULL, NULL, NULL ) IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_COMMAND_ROLES] WHERE ExtraUiCommandRolesID = 'ED904C94-EF61-4C09-921D-7BF8101CDF2E') INSERT INTO [VCLib].[T_EXTRA_UI_COMMAND_ROLES] (ExtraUiCommandRolesID, ExtraUiCommand, Roles) VALUES ( 'ED904C94-EF61-4C09-921D-7BF8101CDF2E', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', @AdministratorRoleID )
Скачать скрипт для добавления команды пользовательского интерфейса файлом c XSLT шаблоном в режиме обновления (update mode): Warranty_Program_ExportToSQL_AddExtraUICommand(update mode)
Следующий скрипт добавит дополнительную команду пользовательского интерфейса, c XSLT шаблоном в режиме вставки (insert mode), здесь также потребуется Refresh, сначала серверного холдера ExtraUICommandHolder, а затем клиентского холдера с таким же именем, чтобы пункт меню «Экспорт в SQL» отображался в контекстном меню при щелчке правой кнопкой мыши на элементе сущности:
USE AKUZDB declare @ENTITY_WARRANTY_PROGRAM int; declare @AdministratorRoleID uniqueidentifier; select top 1 @ENTITY_WARRANTY_PROGRAM = EntityID from VCLib.T_ENTITIES where Entname = 'WARRANTY_PROGRAM' and [Schema] = 'AKUZ'; select top 1 @AdministratorRoleID = RoleID from VCLib.T_ROLES where RoleName = 'Администратор' IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_COMMAND] WHERE ExtraUiCommandID = 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3') INSERT INTO [VCLib].[T_EXTRA_UI_COMMAND] (ExtraUiCommandID, Name, FilterValue, Context, OpenContext, Priority, StyleType, Entities, CommandEntity, CommandType, SameObject, StoredProcedure, DisplayAttributes, ExecutionType, RemoteCommandID, Credentials) VALUES ( 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'Экспорт в SQL (insert mode)', NULL, '*', '', '0', NULL, @ENTITY_WARRANTY_PROGRAM, @ENTITY_WARRANTY_PROGRAM, '4', '0', 'spx_Warranty_Program_ExportToSql', NULL, '0', NULL, NULL ) IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_ACTION_PARAMS] WHERE ExtraUiActionParamsID = 'C6E71FA2-CAE7-4F8E-90F5-45F877A1E853') INSERT INTO [VCLib].[T_EXTRA_UI_ACTION_PARAMS] (ExtraUiActionParamsID, ExtraUiCommand, Name, ParamDescription, IsOutput, ParamValue, ResultType, OutputOrder, FileName, Verb) VALUES ( 'C6E71FA2-CAE7-4F8E-90F5-45F877A1E853', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'xml', 'xml', '1', '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://mycompany.com/mynamespace" xmlns:js="urn:the-xml-files:xslt-csharp" xmlns:ntd="http://www.ntd.ru/ntd/" version="1.0"> <xsl:output method="text"/> <xsl:template match="root"> <xsl:for-each select="WARRANTY_PROGRAM"> USE AKUZDB declare @WarrantyProgramID uniqueidentifier declare @WarrantyProgramGroupID uniqueidentifier declare @WarrantyProgramItemID uniqueidentifier declare @WarrantyProgramItemRateID uniqueidentifier declare @WarrantyProgramGroupRateID uniqueidentifier declare @WarrantyProgramFieldID uniqueidentifier declare @WarrantyProgramRatioID uniqueidentifier set @WarrantyProgramID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM] (WarrantyProgramID, Name, ValidThrough, PackName, PackNamePrefix) VALUES ( @WarrantyProgramID, ''<xsl:value-of select="Name"/> от ''+CAST(GETDATE() AS nvarchar(30)), ''<xsl:value-of select="ValidThrough"/>'', <xsl:choose> <xsl:when test="string(PackName)">''<xsl:value-of select="PackName"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNamePrefix)">''<xsl:value-of select="PackNamePrefix"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) <xsl:for-each select="WARRANTY_PROGRAM_GROUP"> set @WarrantyProgramGroupID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_GROUP] (WarrantyProgramGroupID, Name, Entity, WarrantyProgram, Criteria, DatePath, DateLexem, CriteriaExist, PackNameLexem, CorrCriteria) VALUES ( @WarrantyProgramGroupID, ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="Entity"/>'', @WarrantyProgramID, <xsl:choose> <xsl:when test="string(Criteria)">''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DatePath)">''<xsl:value-of select="DatePath"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(DateLexem)">''<xsl:value-of select="DateLexem"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CriteriaExist)">''<xsl:value-of select="CriteriaExist"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(PackNameLexem)">''<xsl:value-of select="PackNameLexem"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="string(CorrCriteria)">''<xsl:value-of select="CorrCriteria"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) <xsl:for-each select="WARRANTY_PROGRAM_ITEM"> set @WarrantyProgramItemID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM] (WarrantyProgramItemID, Code, WarrantyProgramGroup, Name, FullName, Criteria, Priority, ServiceCode, Code2) VALUES ( @WarrantyProgramItemID, ''<xsl:value-of select="Code"/>'', @WarrantyProgramGroupID, ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="FullName"/>'', <xsl:choose> <xsl:when test="string(Criteria)">''<xsl:value-of select="Criteria"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> ''<xsl:value-of select="Priority"/>'', <xsl:choose> <xsl:when test="string(ServiceCode)">''<xsl:value-of select="ServiceCode"/>'',</xsl:when> <xsl:otherwise>NULL,</xsl:otherwise> </xsl:choose> ''<xsl:value-of select="Code2"/>'' ) <xsl:for-each select="WARRANTY_PROGRAM_ITEM_RATE"> set @WarrantyProgramItemRateID = NEWID(); INSERT INTO [AKUZ].[T_WARRANTY_PROGRAM_ITEM_RATE] (WarrantyProgramItemRateID, EffectiveDate, Rate, WarrantyProgramItem, Ratio, RateExtra) VALUES ( @WarrantyProgramItemRateID, ''<xsl:value-of select="EffectiveDate"/>'', ''<xsl:value-of select="Rate"/>'', @WarrantyProgramItemID, ''<xsl:value-of select="Ratio"/>'', <xsl:choose> <xsl:when test="string(RateExtra)">''<xsl:value-of select="RateExtra"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_GROUP_RATE"> set @WarrantyProgramGroupRateID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_GROUP_RATE (WarrantyProgramGroupRateID, EffectiveDate, WarrantyProgramGroup, Ratio2) VALUES ( @WarrantyProgramGroupRateID, ''<xsl:value-of select="EffectiveDate"/>'', @WarrantyProgramGroupID, ''<xsl:value-of select="Ratio2"/>'' ) </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_FIELD"> set @WarrantyProgramFieldID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_FIELD(WarrantyProgramFieldID, Name, Lexem, WarrantyProgramGroup, DataType, Condition) VALUES ( @WarrantyProgramFieldID, ''<xsl:value-of select="Name"/>'', ''<xsl:value-of select="Lexem"/>'', @WarrantyProgramGroupID, ''<xsl:value-of select="DataType"/>'', <xsl:choose> <xsl:when test="string(Condition)">''<xsl:value-of select="Condition"/>''</xsl:when> <xsl:otherwise>NULL</xsl:otherwise> </xsl:choose> ) </xsl:for-each> </xsl:for-each> <xsl:for-each select="WARRANTY_PROGRAM_RATIO"> set @WarrantyProgramRatioID = NEWID(); INSERT INTO AKUZ.T_WARRANTY_PROGRAM_RATIO (WarrantyProgramRatioID, WarrantyProgram, Ratio, BeginDate) VALUES ( @WarrantyProgramRatioID, @WarrantyProgramID, ''<xsl:value-of select="Ratio"/>'', ''<xsl:value-of select="BeginDate"/>'' ) </xsl:for-each> GO </xsl:for-each> </xsl:template> </xsl:stylesheet>', '1', '0', '"WarrantyProgram.sql"', NULL ) ,( '19747175-173B-4737-85C3-4B9820F31730', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', 'WarrantyProgramID', 'Идентификатор', '0', '<lx:Path OwnerQueryLevel="0" xmlns:lx="lexem"> <lx:StepString name="WarrantyProgramID" /> </lx:Path>', NULL, NULL, NULL, NULL ) IF NOT EXISTS (SELECT * FROM [VCLib].[T_EXTRA_UI_COMMAND_ROLES] WHERE ExtraUiCommandRolesID = 'ED904C94-EF61-4C09-921D-7BF8101CDF2E') INSERT INTO [VCLib].[T_EXTRA_UI_COMMAND_ROLES] (ExtraUiCommandRolesID, ExtraUiCommand, Roles) VALUES ( 'ED904C94-EF61-4C09-921D-7BF8101CDF2E', 'B0F3D2E9-B93F-4A91-8B70-334565EB7DD3', @AdministratorRoleID )
Скачать скрипт для добавления команды пользовательского интерфейса файлом c XSLT шаблоном в режиме вставки (insert mode): Warranty_Program_ExportToSQL_AddExtraUICommand(insert mode)
После проделанных действий при щелчке правой кнопкой кнопкой на элементе сущности «WARRANTY_PROGRAM»(Программа страхования) добавится пункт «Экспорт в SQL».
Останется только экспортировать в SQL нужную программу страхования и прогнать полученный скрипт на базе, куда эту программу страхования нужно добавить.