SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT *
           FROM dbo.[SYSOBJECTS]
           WHERE [ID] = OBJECT_ID(N'dbo.[rc_putftsfactinformation]') AND 
                 OBJECTPROPERTY(ID, N'IsProcedure') = 1)
BEGIN
   DROP PROCEDURE dbo.[rc_putftsfactinformation]
END
GO
/*******************************************************************************

   © Кейсистемс (Keysystems), 2014

*******************************************************************************/
CREATE PROCEDURE dbo.[rc_putftsfactinformation]
    @vcXML nvarchar(max),
    @vcRequestMessageID nvarchar(1000),
    @vcPrefix varchar(8000) = '',
    @msgLink INT = NULL,
    @vcResultXml VARCHAR(MAX) OUT
AS
SET NOCOUNT ON

declare @ixml int, @vcXML2 nvarchar(max), @vcMessage varchar(1000), @path varchar(8000), @iLINK_SAVED_FORM INT, @xml xml
declare @d datetime, @s varchar(8000)

--set @vcXML2 = replace(replace(@vcXML, '<?xml version="1.0" encoding="utf-8"?>', '<?xml version="1.0" encoding="utf-16"?>'), 'RPNSUR:', '')

--exec SP_XML_PREPAREDOCUMENT @ixml OUTPUT, @vcXML2
/*
if object_id('tempdb..#tabInput') is null
begin
    create table #tabInput(
        InputXml nvarchar(max),
        ReqMessageID nvarchar(max),
		MessageID nvarchar(max),
        CertificateNumber nvarchar(max),
        CertificateDate nvarchar(max),
        GoodsDescriptionTNVED nvarchar(max),
        GoodsDescription nvarchar(max),
        OriginManufactureName nvarchar(max),
        OriginManufactureAddress nvarchar(max),
        ManufactureCountryIdentifier nvarchar(max),
        OriginCountryIdentifier nvarchar(max),
        ExporterName nvarchar(max),
        ExporterCountry nvarchar(max),
        ImporterName nvarchar(max),
        TSInfoTransportNumber nvarchar(max),
        TSInfoTypeIdentifier nvarchar(max),
        CMRNumber nvarchar(max),
        CustomsDepartmentName nvarchar(max),
        Packages nvarchar(max),
        GoodsQuantityMainUnits nvarchar(max),
        GoodsQuantityPurpose nvarchar(max),
        GoodsQuantityDomain nvarchar(max),
        GoodsFoodCharacteristics nvarchar(max),
        GoodsManufactureCharacterisics nvarchar(max),
        BorderCheckpointName nvarchar(max),
        BorderCheckpointID nvarchar(max),
        PreliminaryInformationID nvarchar(max),
        IncorrectPreliminaryInformationFlag nvarchar(MAX),
        IncidentID nvarchar(max),
        CustomsOfficeName nvarchar(max),
        CustomsPostCode nvarchar(max),
        CustomsType nvarchar(max),
        prodid      varchar(500))
end*/

--SET @path = @vcPrefix + 'Request/PutFTSFactInformationTKMVContent/PreliminaryInformationProd'
--select @vcMessage = [ChainMessageID]
--FROM OPENXML(@ixml, @path, 2)
--          WITH ([ChainMessageID]           NVARCHAR(1000) '../ChainMessageID/text()')

set @xml = cast(@vcXML as xml)

IF @vcPrefix <> ''
select @vcMessage = b.value( '*:ChainMessageID[1]/text()[1]', 'NVARCHAR(1000)')
FROM @xml.nodes('/*:Envelope/*:Body/*:GetRequestResponse/*:RequestMessage/*:Request/*:SenderProvidedRequestData/*:MessagePrimaryContent/*:Request/*:PutFTSFactInformationTKMVContent') a (b)
ELSE
select @vcMessage = b.value( '*:ChainMessageID[1]/text()[1]', 'NVARCHAR(1000)')
FROM @xml.nodes('/*:Request/*:PutFTSFactInformationTKMVContent') a (b)

if  @vcMessage = '00000000-cb54-40fa-8378-1eaf7926999d' -- это тествый сценарий
begin
    set @vcResultXml = '<RPNSUR:Response xmlns:RPNSUR="http://ns.rospotrebnadzor.ru/RPNSUR/3.0.0">
	<RPNSUR:PutFTSFactInformationResultTKMVContent>

		<RPNSUR:ChainMessageID>00000000-cb54-40fa-8378-1eaf7926999d</RPNSUR:ChainMessageID>

		<RPNSUR:PreliminaryResult RPNSUR:ProdId="1">требуется оценка (осмотр/досмотр)</RPNSUR:PreliminaryResult>
		<RPNSUR:PreliminaryResult RPNSUR:ProdId="2">ввоз разрешен</RPNSUR:PreliminaryResult>

	</RPNSUR:PutFTSFactInformationResultTKMVContent>
</RPNSUR:Response>'
return 
end               
   

if exists(select * from  dbo.r_ftsfactinformation b where b.MessageID = @vcMessage)
begin        
       set @vcMessage = 'ChainMessageID: ' + @vcMessage + ' уже используется при ФИ/ПИ, необходимо использование другого UID'
       raiserror(@vcMessage,11,0);
       return
end  

create table #tabLink1(link int,
                        MessageID nvarchar(max),
                        prodid           varchar(500))

--SET @path = @vcPrefix + 'Request/PutFTSFactInformationTKMVContent/PreliminaryInformationProd'
--insert into dbo.r_ftsfactinformation (
--        InputXml,
--		ReqMessageID,
--        MessageID,
--        CertificateNumber,
--        CertificateDate,
--        GoodsDescriptionTNVED,
--        GoodsDescription,
--        OriginManufactureName,
--        OriginManufactureAddress,
----        ManufactureCountryIdentifier,
--        OriginCountryIdentifier,
--        ExporterName,
--        ExporterCountry,
--        ImporterName,
----        TSInfoTransportNumber,
----        TSInfoTypeIdentifier,
--        CMRNumber,
--        CMRDate,
----        CustomsDepartmentName,
----        Packages,
----        GoodsQuantityMainUnits,
--        GoodsQuantityPurpose,
--        GoodsQuantityDomain,
----        GoodsFoodCharacteristics,
----        GoodsManufactureCharacterisics,
--        BorderCheckpointName,
--        BorderCheckpointID,
--        PreliminaryInformationID,
----        CustomsOfficeName,
----        CustomsPostCode,
--        CustomsType,
----        InfoType,
--        FormnumberId,
--        DeclarantDetailsName,
--        DeclarantDetailsAddress,
--        ExporterAddress,
--        DestinationCountry,
--        SubjectAddressDetails,
--        ContainerDetails,
--        OriginCertificate,
--        prodid,
--        message_link)
--output inserted.[LINK], inserted.MessageID, inserted.prodid into #tabLink1
--select 
--        @vcXML,
--        @vcRequestMessageID,
--		ChainMessageID,
--        L.CertificateNumber,
--        L.CertificateDate,
--        L.GoodsDescriptionTNVED,
--        L.GoodsDescription,
--        L.OriginManufactureName,
--        L.OriginManufactureAddress,
----        ManufactureCountryIdentifier,
--        L.OriginCountryIdentifier,
--        L.ExporterName,
--        L.ExporterCountry,
--        L.ImporterName,
----        TSInfoTransportNumber,
----        TSInfoTypeIdentifier,
--        L.CMRNumber,
--        L.CMRDate,
----        CustomsDepartmentName,
----        Packages,
----        GoodsQuantityMainUnits,
--        L.GoodsQuantityPurpose,
--        L.GoodsQuantityDomain,
----        GoodsFoodCharacteristics,
----        GoodsManufactureCharacterisics,
--        L.BorderCheckpointName,
--        L.BorderCheckpointID,
--        L.PreliminaryInformationID,
----        CustomsOfficeName,
----        CustomsPostCode,
--        L.CustomsType,
----        InfoType,
--        L.FormnumberId,
--        L.DeclarantDetailsName,
--        L.DeclarantDetailsAddress,
--        L.ExporterAddress,
--        L.DestinationCountry,
--        L.SubjectAddressDetails,
--        L.ContainerDetails,
--        L.OriginCertificate,
--        L.prodid,
--        @msgLink
--FROM OPENXML(@ixml, @path, 2)
--          WITH ([ChainMessageID]           NVARCHAR(1000) '../ChainMessageID/text()',
--                PreliminaryInformationID   NVARCHAR(1000) '../PreliminaryInformationID/text()',
--                BorderCheckpointName   NVARCHAR(1000) '../BorderCheckpoint/BorderCheckpointName/text()',
--                BorderCheckpointID   NVARCHAR(1000) '../BorderCheckpoint/BorderCheckpointID/text()',
--                CustomsType   NVARCHAR(1000) '../BorderCheckpoint/CustomsType/text()',
--                ProdID        NVARCHAR(1000) '@ProdId',
--                CertificateNumber   NVARCHAR(1000) 'CertificateNumber/text()',
--                CertificateDate   NVARCHAR(1000) 'CertificateDate/text()',
--                FormnumberId   NVARCHAR(1000) 'FormnumberId/text()',
--                DeclarantDetailsName   NVARCHAR(1000) 'DeclarantDetailsName/text()',
--                DeclarantDetailsAddress   NVARCHAR(1000) 'DeclarantDetailsAddress/text()',
--                GoodsDescriptionTNVED   NVARCHAR(1000) 'GoodsDescriptionTNVED/text()',
--                GoodsDescription   NVARCHAR(1000) 'GoodsDescription/text()',
--                OriginManufactureName   NVARCHAR(1000) 'OriginManufactureName/text()',
--                OriginManufactureAddress   NVARCHAR(1000) 'OriginManufactureAddress/text()',
--                OriginCountryIdentifier   NVARCHAR(1000) 'OriginCountryIdentifier/text()',
--                ExporterName   NVARCHAR(1000) 'ExporterName/text()',
--                ExporterAddress   NVARCHAR(1000) 'ExporterAddress/text()',
--                ExporterCountry   NVARCHAR(1000) 'ExporterCountry/text()',
--                DestinationCountry   NVARCHAR(1000) 'DestinationCountry/text()',
--                ImporterName   NVARCHAR(1000) 'ImporterName/text()',
--                SubjectAddressDetails   NVARCHAR(1000) 'SubjectAddressDetails/text()',
--                GoodsQuantityPurpose   NVARCHAR(1000) 'GoodsQuantityPurpose/text()',
--                GoodsQuantityDomain   NVARCHAR(1000) 'GoodsQuantityDomain/text()',
--                OriginCertificate   NVARCHAR(1000) 'OriginCertificate/text()',
--                CMRNumber   NVARCHAR(1000) 'CMRNumber/text()',
--                CMRDate   NVARCHAR(1000) 'CMRDate/text()',
--                ContainerDetails   NVARCHAR(1000) 'ContainerDetails/text()'               
--                ) L

insert into dbo.r_ftsfactinformation (
        InputXml,
		ReqMessageID,
        MessageID,
        CertificateNumber,
        CertificateDate,
        GoodsDescriptionTNVED,
        GoodsDescription,
        OriginManufactureName,
        OriginManufactureAddress,
        OriginCountryIdentifier,
        ExporterName,
        ExporterCountry,
        ImporterName,
        CMRNumber,
        CMRDate,
        GoodsQuantityPurpose,
        GoodsQuantityDomain,
        BorderCheckpointName,
        BorderCheckpointID,
        PreliminaryInformationID,
        CustomsType,
        FormnumberId,
        DeclarantDetailsName,
        DeclarantDetailsAddress,
        ExporterAddress,
        DestinationCountry,
        SubjectAddressDetails,
        ContainerDetails,
        OriginCertificate,
        prodid,
        message_link)
output inserted.[LINK], inserted.MessageID, inserted.prodid into #tabLink1
select 
        @vcXML,
        @vcRequestMessageID,
		b.value('../*:ChainMessageID[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:CertificateNumber[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:CertificateDate[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:GoodsDescriptionTNVED[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:GoodsDescription[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:OriginManufactureName[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:OriginManufactureAddress[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:OriginCountryIdentifier[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:ExporterName[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:ExporterCountry[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:ImporterName[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:CMRNumber[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:CMRDate[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:GoodsQuantityPurpose[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:GoodsQuantityDomain[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('../*:BorderCheckpoint[1]/*:BorderCheckpointName[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('../*:BorderCheckpoint[1]/*:BorderCheckpointID[1]/text()[1]', 'NVARCHAR(1000)'),
        COALESCE(
            b.value('../*:PreliminaryInformationID[1]/text()[1]', 'NVARCHAR(1000)'),
            CONVERT(NVARCHAR(5), 'AUTO_') + CONVERT(NVARCHAR(995), NEWID())
        ),
        b.value('../*:BorderCheckpoint[1]/*:CustomsType[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:FormnumberId[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:DeclarantDetailsName[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:DeclarantDetailsAddress[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:ExporterAddress[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:DestinationCountry[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:SubjectAddressDetails[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:ContainerDetails[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('*:OriginCertificate[1]/text()[1]', 'NVARCHAR(1000)'),
        b.value('declare namespace p1="http://ns.rospotrebnadzor.ru/RPNSUR/3.0.0"; @p1:ProdId', 'NVARCHAR(1000)'),
        @msgLink
FROM @xml.nodes('/*:Envelope/*:Body/*:GetRequestResponse/*:RequestMessage/*:Request/*:SenderProvidedRequestData/*:MessagePrimaryContent/*:Request/*:PutFTSFactInformationTKMVContent/*:PreliminaryInformationProd') a (b)

--SET @path = @vcPrefix + 'Request/PutFTSFactInformationTKMVContent/TSInfoTransport'
--INSERT [dbo].[r_PutFTSVEDInformationTKMVContent_fact] (MessageID,        
--                                                  PreliminaryInformationID,
--                                                  TSInfoTypeIdentifier,
--                                                  auto_TransportMeansRegId,
--                                                  ElementNick,
--                                                  TSInfoTypeIdentifier_NA,
--                                                  air_NationalIdentitySign,                                                    
--                                                  air_JetRegNumber,
--                                                  air_RouteNumber,
--	                                              sea_ShipName,
--	                                              sea_ShipRegNumber,
--	                                              sea_RouteNumber,
--	                                              sea_IMO,
--	                                              sea_ShipType,
--	                                              rail_TrainNumber,
--	                                              rail_VanNumber)

--select [ChainMessageID],       
--       PreliminaryInformationID,
--       TSInfoTypeIdentifier,
--       auto_TransportMeansRegId,
--       ElementNick,
--       TSInfoTypeIdentifier_NA,
--       air_NationalIdentitySign,         
--       air_JetRegNumber,
--       air_RouteNumber,
--       sea_ShipName,
--       sea_ShipRegNumber,
--       sea_RouteNumber,
--       sea_IMO,
--       sea_ShipType,
--       rail_TrainNumber,
--       rail_VanNumber
--FROM OPENXML(@ixml, @path, 2)
--          WITH ([ChainMessageID]           NVARCHAR(1000) '../ChainMessageID/text()',
--                PreliminaryInformationID   NVARCHAR(1000) '../PreliminaryInformationID/text()',
--                TSInfoTypeIdentifier   NVARCHAR(1000) 'TSInfoTypeIdentifier/text()',
--                auto_TransportMeansRegId   NVARCHAR(1000) 'auto_TransportMeansRegId/text()',
--                ElementNick           VARCHAR(100) '@ElementNick',
--                TSInfoTypeIdentifier_NA   NVARCHAR(1000) 'TSInfoTypeIdentifier_NA/text()',
--                air_NationalIdentitySign   NVARCHAR(1000) 'air_NationalIdentitySign/text()',
--                air_JetRegNumber NVARCHAR(1000) 'air_JetRegNumber/text()',
--                air_RouteNumber NVARCHAR(1000) 'air_RouteNumber/text()',
--                sea_ShipName NVARCHAR(1000) 'sea_ShipName/text()',
--                sea_ShipRegNumber NVARCHAR(1000) 'sea_ShipRegNumber/text()',
--                sea_RouteNumber NVARCHAR(1000) 'sea_RouteNumber/text()',
--                sea_IMO NVARCHAR(1000) 'sea_IMO/text()',
--                sea_ShipType NVARCHAR(1000) 'sea_ShipType/text()',
--                rail_TrainNumber NVARCHAR(1000) 'rail_TrainNumber/text()',
--                rail_VanNumber NVARCHAR(1000) 'rail_VanNumber/text()')

--exec sp_xml_removedocument @ixml

INSERT [dbo].[r_PutFTSVEDInformationTKMVContent_fact] (MessageID,        
                                                  PreliminaryInformationID,
                                                  TSInfoTypeIdentifier,
                                                  auto_TransportMeansRegId,
                                                  ElementNick,
                                                  TSInfoTypeIdentifier_NA,
                                                  air_NationalIdentitySign,                                                    
                                                  air_JetRegNumber,
                                                  air_RouteNumber,
	                                              sea_ShipName,
	                                              sea_ShipRegNumber,
	                                              sea_RouteNumber,
	                                              sea_IMO,
	                                              sea_ShipType,
	                                              rail_TrainNumber,
	                                              rail_VanNumber)

select b.value('../*:ChainMessageID[1]/text()[1]', 'NVARCHAR(1000)'),
       b.value('../*:PreliminaryInformationID[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:TSInfoTypeIdentifier[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:auto_TransportMeansRegId[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('@ElementNick', 'NVARCHAR(1000)'), 
       b.value('*:TSInfoTypeIdentifier_NA[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:air_NationalIdentitySign[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:air_JetRegNumber[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:air_RouteNumber[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:sea_ShipName[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:sea_ShipRegNumber[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:sea_RouteNumber[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:sea_IMO[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:sea_ShipType[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:rail_TrainNumber[1]/text()[1]', 'NVARCHAR(1000)'), 
       b.value('*:rail_VanNumber[1]/text()[1]', 'NVARCHAR(1000)')
FROM @xml.nodes('/*:Envelope/*:Body/*:GetRequestResponse/*:RequestMessage/*:Request/*:SenderProvidedRequestData/*:MessagePrimaryContent/*:Request/*:PutFTSFactInformationTKMVContent/*:TSInfoTransport') a (b)

declare @messageId nvarchar(max), @vcResult VARCHAR(250), @vcResultOur VARCHAR(MAX), @prodid varchar(500)
declare @link1 int
declare @link2 int

if object_id('tempdb..#tabLink2') IS NULL
create table #tabLink2(link int)

declare cur cursor local static for select link, MessageID, prodid
from #tablink1

open cur

fetch next from cur into @link1, @messageId, @prodid
set @d = getdate()
while @@fetch_status = 0
begin
--set @s =  convert(varchar(50), getdate() - @d, 121) 
--print @prodid
--print @s
    exec dbo.[RO_SUR_REGULATIONS_CHECK] @tMode = 3, @link = @link1, @vcResult = @vcResult OUTPUT, @vcResultOur = @vcResultOur OUTPUT

  --  exec dbo.RO_SUR_PI_SAVE @tMode = 3, @link = @link1, @vcResultCheck = @vcResultOur, @vcResult = @vcResult, @iLINK_SAVED_FORM = @iLINK_SAVED_FORM OUTPUT


    insert into dbo.r_ftsfactinformationresult(
        Link_Request,
        MessageID,
        PreliminaryResultID,
        PreliminaryResult,
        PreliminaryResultOur,
        ChainMessageID,
        prodid, link_saved_form)
    output inserted.[LINK] into #tabLink2
    values(
        @link1,
        cast(newid() as nvarchar(max)),
        cast(newid() as nvarchar(max)),
        @vcResult,
        @vcResultOur,
        @messageId,
        @prodid, @iLINK_SAVED_FORM
    )
  fetch next from cur into @link1, @messageId, @prodid
end


select top 1 @vcResultXml = '<RPNSUR:Response xmlns:RPNSUR = "http://ns.rospotrebnadzor.ru/RPNSUR/3.0.0">
<RPNSUR:PutFTSFactInformationResultTKMVContent>
<RPNSUR:ChainMessageID>' + ChainMessageID +  '</RPNSUR:ChainMessageID>
'
from dbo.r_ftsfactinformationresult a
join #tablink2 b on a.link = b.link

select @vcResultXml = @vcResultXml + '<RPNSUR:PreliminaryResult RPNSUR:ProdId="' + ProdID + '">' + PreliminaryResult + '</RPNSUR:PreliminaryResult>
' 
from dbo.r_ftsfactinformationresult a
join #tablink2 b on a.link = b.link

set @vcResultXml = @vcResultXml + '</RPNSUR:PutFTSFactInformationResultTKMVContent>
</RPNSUR:Response>'

IF @msgLink is NULL
    select @vcResultXml as [xml],
        a.link,
        a.Link_Request,
        a.MessageID,
        a.ChainMessageID
    from dbo.r_ftsfactinformationresult a
    join #tablink2 b on a.link = b.link

drop table #tablink1

RETURN 0


GO
GRANT ALL ON dbo.[rc_putftsfactinformation] TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


