----Не работает на sql младше 2019
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

--if exists (select * from dbo.sysobjects where id = object_id(N'akuz.sp_CreateStorageInvoiceFromTTMF') and xtype = N'P')
--drop procedure akuz.sp_CreateStorageInvoiceFromTTMF
--GO

---- =============================================
---- Author:		Anatoliy Pokus
---- Create date: 2026-03-27
---- Description:	Создание накладной прихода из накладной ТТМФ
---- =============================================
--CREATE PROCEDURE akuz.sp_CreateStorageInvoiceFromTTMF
--	 @Json varchar(max)
--	,@Storage uniqueidentifier
--	,@InvoiceId uniqueidentifier
--    ,@Mark bit --0 маркирован 1 нет
--    ,@User uniqueidentifier
--    ,@ResponsiblePerson uniqueidentifier
--AS 
--BEGIN 
--	set nocount on 

--    -- Таблица основных данных документа
--    IF OBJECT_ID('tempdb..#Ttn') IS NOT NULL DROP TABLE #Ttn;
--    CREATE TABLE #Ttn (
--        docId BIGINT,
--        docNum NVARCHAR(50),
--        docDate DATETIME2,
--        docShipmentDate DATETIME2,
--        postName NVARCHAR(200),
--        postId INT,
--        gruzoName NVARCHAR(200),
--        senderName NVARCHAR(200),
--        senderInn NVARCHAR(20),
--        storeName NVARCHAR(200),
--        ttnSumWithNds DECIMAL(18,2),
--        ttnSumWithoutNds DECIMAL(18,2),
--        ndsSum DECIMAL(18,2),
--        dogovorNum NVARCHAR(50),
--        dogovorDate DATETIME2,
--        zayavName NVARCHAR(50),
--        isMark BIT,
--        markDocumentId UNIQUEIDENTIFIER,
--        markAddressId NVARCHAR(50),
--        supplyerTypeId INT,
--        markingShchema INT
--    );

--    -- Таблица спецификаций (товарные позиции)
--    IF OBJECT_ID('tempdb..#Specs') IS NOT NULL DROP TABLE #Specs;
--    CREATE TABLE #Specs (
--        docId BIGINT,
--        prepId BIGINT,
--        prepName NVARCHAR(500),
--        producerName NVARCHAR(200),
--        tradeName NVARCHAR(200),
--        seria NVARCHAR(100),
--        sgod DATETIME2,
--        quant DECIMAL(18,3),
--        quantInPack INT,
--        ndsValue INT,
--        priceWithNds DECIMAL(18,2),
--        sumWithNds DECIMAL(18,2),
--        isJnv BIT,
--        supplierPvsId BIGINT
--    );

--    -- Таблица маркировки (коды маркировки)
--    IF OBJECT_ID('tempdb..#Marks') IS NOT NULL DROP TABLE #Marks;
--    CREATE TABLE #Marks (
--        docId BIGINT,
--        prepId BIGINT,
--        pvsId BIGINT,
--        pvsQuant DECIMAL(18,3),
--        pvsSgtin NVARCHAR(100)
--    );

---- Загрузка заголовка документа
--    INSERT INTO #Ttn (
--        docId, docNum, docDate, docShipmentDate, postName, postId,
--        gruzoName, senderName, senderInn, storeName,
--        ttnSumWithNds, ttnSumWithoutNds, ndsSum,
--        dogovorNum, dogovorDate, zayavName, isMark,
--        markDocumentId, markAddressId, supplyerTypeId,markingShchema
--    )
--    SELECT 
--        JSON_VALUE(@json, '$.docId'),
--        JSON_VALUE(@json, '$.docNum'),
--        JSON_VALUE(@json, '$.docDate'),
--        JSON_VALUE(@json, '$.docShipmentDate'),
--        JSON_VALUE(@json, '$.postName'),
--        JSON_VALUE(@json, '$.postId'),
--        JSON_VALUE(@json, '$.gruzoName'),
--        JSON_VALUE(@json, '$.senderName'),
--        JSON_VALUE(@json, '$.senderInn'),
--        JSON_VALUE(@json, '$.storeName'),
--        JSON_VALUE(@json, '$.ttnSumWithNds'),
--        JSON_VALUE(@json, '$.ttnSumWithoutNds'),
--        JSON_VALUE(@json, '$.ndsSum'),
--        JSON_VALUE(@json, '$.dogovorNum'),
--        JSON_VALUE(@json, '$.dogovorDate'),
--        JSON_VALUE(@json, '$.zayavName'),
--        JSON_VALUE(@json, '$.isMark'),
--        JSON_VALUE(@json, '$.markDocumentId'),
--        JSON_VALUE(@json, '$.markAddressId'),
--        JSON_VALUE(@json, '$.supplyerTypeId'),
--        JSON_VALUE(@json, '$.markingShchema');

--    -- Загрузка спецификаций
--    INSERT INTO #Specs (
--        docId, prepId, prepName, producerName, tradeName, seria,
--        sgod, quant, quantInPack, ndsValue, priceWithNds,
--        sumWithNds, isJnv, supplierPvsId
--    )
--    SELECT 
--        JSON_VALUE(@json, '$.docId') AS docId,
--        JSON_VALUE(specs.value, '$.prepId'),
--        JSON_VALUE(specs.value, '$.prepName'),
--        JSON_VALUE(specs.value, '$.producerName'),
--        JSON_VALUE(specs.value, '$.tradeName'),
--        JSON_VALUE(specs.value, '$.seria'),
--        JSON_VALUE(specs.value, '$.sgod'),
--        JSON_VALUE(specs.value, '$.quant'),
--        JSON_VALUE(specs.value, '$.quantInPack'),
--        JSON_VALUE(specs.value, '$.ndsValue'),
--        JSON_VALUE(specs.value, '$.priceWithNds'),
--        JSON_VALUE(specs.value, '$.sumWithNds'),
--        JSON_VALUE(specs.value, '$.isJnv'),
--        JSON_VALUE(specs.value, '$.supplierPvsId')
--    FROM OPENJSON(@json, '$.specs') AS specs;

--    if(@Mark = 0)
--    begin
--        -- Загрузка маркировки
--        INSERT INTO #Marks (
--            docId, prepId, pvsId, pvsQuant, pvsSgtin
--        )
--        SELECT 
--            JSON_VALUE(@json, '$.docId') AS docId,
--            JSON_VALUE(specs.value, '$.prepId') AS prepId,
--            JSON_VALUE(marks.value, '$.pvsId'),
--            JSON_VALUE(marks.value, '$.pvsQuant'),
--            JSON_VALUE(marks.value, '$.pvsSgtin')
--        FROM OPENJSON(@json, '$.specs') AS specs
--        CROSS APPLY OPENJSON(specs.value, '$.marks') AS marks;

--        update #Marks set pvsQuant = 1;
--    end

--    declare @Error bit

--    if(exists(select * from #Ttn ttn
--                where not exists(select 1
--                                    from STOR.T_SUPPLIER_CONTRACT sc
--                                        left join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = sc.SupplierContractAbr
--                                    where sc.Number = ttn.dogovorNum and sc.Date = ttn.dogovorDate)))
--    begin
--        set @Error = 1
--		raiserror('Не удалось найти договор!', 16, 10)
--        return;
--    end
    
--    if(exists(select * from #Specs s
--                where not exists(select 1
--                                    from PHARM.T_BILL_OF_GOODS bg
--                                    where bg.Active = 1 and bg.OtherID = s.prepId
--                                    )))
--    begin
--        set @Error = 1
--		raiserror('Не удалось найти препараты в справочнике!', 16, 10)
--        return;
--    end

--    --SELECT * FROM #Ttn;
    
--    --SELECT * FROM #Specs;
    
--    --SELECT * FROM #Marks;
--    if(@Error = 0)
--    begin
--        INSERT INTO STOR.T_STORAGE_INVOICE
--	       (InvoiceID
--	       ,Number
--	       ,Date
--	       ,State
--	       ,Storage
--	       ,Creator
--	       ,CreateDate
--	       ,HoldingUser
--	       ,HoldingDate
--	       ,ResponsiblePerson
--	       ,PaymentType
--	       ,InventoryDocument
--	       ,type_
--	       ,Comment
--	       ,Loaded
--	       ,SupplierContract
--	       ,MarkSchemes
--	       ,ShipmentDate
--	       ,ShipmentDocumentNumber
--	       ,StorageApplication)
--	select @InvoiceId
--	       ,ttn.docNum
--	       ,ttn.docDate
--	       ,0
--	       ,@Storage
--	       ,@User
--	       ,GETDATE()
--	       ,null
--	       ,null
--	       ,@ResponsiblePerson
--	       ,sc.PaymentType
--	       ,null
--	       ,(select top 1 EntityID from VCLib.T_ENTITIES where EntName = 'STORAGE_INVOICE_ADD')
--	       ,null
--	       ,1
--	       ,sc.SupplierContractID
--	       ,(case when ttn.markingShchema = 415 then 601 else null end)
--	       ,ttn.docShipmentDate
--	       ,ttn.docNum
--	       ,null
--    from #Ttn ttn
--            outer apply(
--                select top 1 sc.SupplierContractID, abr.PaymentType
--                from STOR.T_SUPPLIER_CONTRACT sc
--                    left join STOR.T_SUPPLIER_CONTRACT_ABR abr on abr.Abr = sc.SupplierContractAbr
--                where sc.Number = ttn.dogovorNum and sc.Date = ttn.dogovorDate
--            ) sc

--	    INSERT INTO STOR.T_STORAGE_INVOICE_ADD
--	           (InvoiceWriteOffBetweenStorage
--	           ,InvoiceID
--	           ,Sender)
--	select  null
--	       ,@InvoiceId
--	       ,null
--    from #Ttn t

--        INSERT INTO STOR.T_INVOICE_ITEM
--                   (StorageInvoice
--                   ,Remains
--                   ,ElementID
--                   ,SupplierContract
--                   ,PrepareRemains
--                   ,StorageInvoiceAdd
--                   ,SupplierContractAbr
--                   ,ProducerPrice
--                   ,InvoiceItem
--                   ,MarkCheck
--                   ,ScanerCheck
--                   ,Barcode
--                   ,SSCC
--                   ,QuantityInPackage
--                   ,Amount
--                   ,LotNumber
--                   ,Series
--                   ,Price
--                   ,ExpirationDate
--                   ,Comment
--                   ,TotalApplicationCount
--                   ,GTINSerialNumber
--                   ,BillOfGoods
--                   ,PackageType
--                   ,TotalPackageType
--                   ,VAT
--                   ,OrdinalNumber
--                   ,ConversionId
--                   ,type_
--                   ,SellingPrice
--                   ,StorageShelf)
--             select @InvoiceId
--                   ,null
--                   ,newid()
--                   ,si.SupplierContract
--                   ,null
--                   ,@InvoiceId
--                   ,sc.SupplierContractAbr
--                   ,null
--                   ,null
--                   ,0
--                   ,0
--                   ,null
--                   ,null
--                   ,Isnull(tot.QuantityInPackage,1)
--                   ,ISNULL(m.pvsQuant, s.quant)
--                   ,0
--                   ,s.seria
--                   ,s.priceWithNds
--                   ,s.sgod
--                   ,null
--                   ,Isnull(s.quant * Isnull(tot.QuantityInPackage, 1), s.quant)
--                   ,ISNULL(m.pvsSgtin, null)
--                   ,bg.BillOfGoodsID
--                   ,bg.UnitPrice
--                   ,bg.WriteOff
--                   ,(case   when s.ndsValue = 10 then 1
--                            when s.ndsValue = 20 then 3
--                            when s.ndsValue = 22 then 6
--                            else 0 end)
--                   ,ROW_NUMBER() over(order by s.docid)
--                   ,null
--                   ,(select top 1 EntityID from VCLib.T_ENTITIES where EntName = 'INVOICE_ITEM')
--                   ,0
--                   ,null
--            from #Specs s
--                join STOR.T_STORAGE_INVOICE si on si.InvoiceID = @InvoiceId
--                left join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = si.SupplierContract
--                left join #Marks m on m.prepId = s.prepId
--                outer apply(
--                    select top 1 bg.BillOfGoodsID, pfU.PackageType as UnitPrice, pfU.OrdinalNumber as UnitPriceON, pfW.PackageType as WriteOff, pfW.OrdinalNumber as WriteOffON
--                    from PHARM.T_BILL_OF_GOODS bg
--                        join PHARM.T_PRODUCT_FORM pfU on pfU.BillOfGoods = bg.BillOfGoodsID and pfU.UnitPrice = 1
--                        join PHARM.T_PRODUCT_FORM pfW on pfW.BillOfGoods = bg.BillOfGoodsID and pfW.WriteOff = 1
--                    where bg.Active = 1 and bg.OtherID = s.prepId
--                ) bg
--                outer apply(
--                    select exp(SUM(log(abs(pf.Amount)))) as QuantityInPackage
--                    from PHARM.T_PRODUCT_FORM pf 
--                    where pf.BillOfGoods = bg.BillOfGoodsID
--                    and pf.OrdinalNumber >= bg.WriteOffON and pf.OrdinalNumber < bg.UnitPriceON
--                    group by pf.BillOfGoods
--                ) tot
--    end
--END
--GO
