-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-12-15
-- Description:    Загрузка накладных
-- =============================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STOR].[sp_LoadImportInvoice]') AND type in (N'P', N'PC'))
DROP PROCEDURE [STOR].[sp_LoadImportInvoice]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [STOR].[sp_LoadImportInvoice] 
    @UserID uniqueidentifier
AS
BEGIN

declare @IdEnt int,@Add int, @Bet int
select top 1 @IdEnt = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_INVOICE'
select top 1 @Bet = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'INVOICE_WRITE_OFF_BETWEEN_STORAGE'
select top 1 @Add = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'STORAGE_INVOICE_ADD'


    -- Генерируем идентификаторы для накладных
    declare @invoiceIds table
        (
            fName varchar(max),
            id uniqueidentifier
        )
    insert into @invoiceIds(fName, id)
    select si.FileNameColumn, NEWID()
    from idb.STORAGE_INVOICE si
    group by si.FileNameColumn
    update si
    set si.InvoiceID = ii.id
    from idb.STORAGE_INVOICE si
        join @invoiceIds ii on ii.fName = si.FileNameColumn

    -- Добавляем уникальный идентификатор
    Update idb.STORAGE_INVOICE set ID = newid()
    Update idb.STORAGE_INVOICE set gtin = null where gtin = ''
    Update idb.STORAGE_INVOICE set sgtin = null where sgtin = ''

    -- Импорт
    INSERT INTO [STOR].[T_IMPORT_ELEMENTS]
               ([ImportElementsID]
               ,[Error]
               ,[Users]
               ,[LoadDate]
               ,[type_]
               ,[FileName])
         SELECT
               si.ID
               ,null
               ,@UserID
               ,getdate()
               ,@IdEnt
               ,si.FileNameColumn
    from idb.STORAGE_INVOICE si

    INSERT INTO [STOR].[T_IMPORT_INVOICE]
               ([InvoiceId]
               ,[InvoiceNumber]
               ,[InvoiceDate]
               ,[Storage]
               ,[Post]
               ,[Amount]
               ,[LotNumber]
               ,[Series]
               ,[Price]
               ,[Sum]
               ,[ExpirationDate]
               ,[Comment]
               ,[BillOfGoods]
               ,[PackageType]
               ,[ImportElementsID]
               ,[SupplierContract]
			   ,[VAT]
			   ,[gtin]
			   ,[sgtin]
			   ,Sender
			   ,ShipmentDate
			   ,ShipmentDocumentNumber)
         SELECT
               si.InvoiceID
               ,si.DNUMBER
               ,si.DDATE
               ,(select TOP 1 sto.StorageID from [STOR].T_STORAGE sto
               where sto.Number = Cast(si.ORG_TO as varchar(100)))
               ,(select TOP 1 ap.ResponsiblePerson from [APS].T_USERS u               
               join [STOR].T_STORAGE sto on sto.StorageID = u.Storage
			   join APS.T_POST ap on ap.PostID = u.Post
               where sto.Number = Cast(si.ORG_TO as varchar(100)))
      --         ,(select TOP 1 ap.ResponsiblePerson 
			   --from [AKUZ].T_USERS u
			   --join AKUZ.T_POST ap on ap.ResourceID = u.Post
      --         join [STOR].T_USER_STORAGE_LINK sto on sto.Users = u.UserID
			   --join STOR.T_STORAGE sss on sss.StorageID = sto.Storage
      --         where sss.Number = Cast(si.ORG_TO as varchar(100)))
               ,si.KO_ALL
               ,si.Part
               ,si.SERIES
               ,si.PRICE
               ,si.KO_ALL * si.PRICE
               ,si.EXPDATE
               ,''
               ,BG.BillOfGoodsID
               ,(Select TOP 1 pf.PackageType from [PHARM].T_PRODUCT_FORM pf 
                   where pf.BillOfGoods = BG.BillOfGoodsID and pf.UnitPrice = 1)
               ,si.ID
               ,(select TOP 1 sc.SupplierContractID from [STOR].T_SUPPLIER_CONTRACT sc where sc.OtherID = si.IDGK)
			   ,si.VAT
			   ,si.gtin
			   ,si.sgtin
			   ,(select TOP 1 sto.StorageID from [STOR].T_STORAGE sto where sto.Number = si.ORG_FROM)
               ,si.DDATE
               ,si.DNUMBER
    from idb.STORAGE_INVOICE si
           OUTER APPLY (select top 1 b.BillOfGoodsID from [PHARM].T_BILL_OF_GOODS b where b.OtherID=si.NOMK_LS) BG

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Номер накладной.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.InvoiceNumber is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле НДС.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.VAT is null or ii.VAT > 3

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Дата накладной.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.InvoiceDate is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Количество.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.Amount is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Серия.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.Series is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Цена.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.Price is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Срок годности.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.ExpirationDate is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден склад по указанным данным - ' + Cast(si.ORG_TO as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
join idb.STORAGE_INVOICE si on si.ID = ie.ImportElementsID
where ii.Storage is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден сотрудник по указанным данным.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.Post is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден препарат по указанным данным - ' + Cast(si.NOMK_LS as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
join idb.STORAGE_INVOICE si on si.ID = ie.ImportElementsID
where ii.BillOfGoods is null


update ie
set ie.Error = ISNULL(ie.Error, '') + 'У препарата не указан gtin или не равен тому что указан в dbf.Препарат - ' + Cast(si.NOMK_LS as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
join idb.STORAGE_INVOICE si on si.ID = ie.ImportElementsID
join PHARm.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = ii.BillOfGoods
where ii.sgtin is not null and (ii.gtin is null or bg.Gtin is null or bg.Gtin <> ii.gtin)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден вид упаковки товара по указанным данным.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where ii.PackageType is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден гос.контракт по указанным данным - ' + Cast(si.IDGK as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
join idb.STORAGE_INVOICE si on si.ID = ie.ImportElementsID
where ii.SupplierContract is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данная накладная уже загружена.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_STORAGE_INVOICE s
         where s.Number = ii.InvoiceNumber and DATEPART(YEAR, s.[Date]) = DATEPART(YEAR, ii.InvoiceDate) and s.Storage = ii.Storage)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данная накладная уже загружена, но склады не равны.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_STORAGE_INVOICE s
         where s.Number = ii.InvoiceNumber and DATEPART(YEAR, s.[Date]) = DATEPART(YEAR, ii.InvoiceDate) and s.Storage <> ii.Storage)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данный элемент уже загружен, но цены не равны.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_INVOICE_ITEM iitem
    join [STOR].T_STORAGE_INVOICE si on si.InvoiceID = iitem.StorageInvoice
         where si.Number = ii.InvoiceNumber and DATEPART(YEAR, si.[Date]) = DATEPART(YEAR, ii.InvoiceDate) and si.Storage = ii.Storage
         and iitem.BillOfGoods = ii.BillOfGoods and ii.Price <> iitem.Price and ii.VAT = iitem.VAT)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данный элемент уже загружен, но НДС не равны.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_INVOICE_ITEM iitem
    join [STOR].T_STORAGE_INVOICE si on si.InvoiceID = iitem.StorageInvoice
         where si.Number = ii.InvoiceNumber and DATEPART(YEAR, si.[Date]) = DATEPART(YEAR, ii.InvoiceDate) and si.Storage = ii.Storage
         and iitem.BillOfGoods = ii.BillOfGoods and ii.Price = iitem.Price and ii.VAT <> iitem.VAT)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найдена соответствующая запись по препарату, цене и НДС в гос-контракте
'
from [STOR].T_IMPORT_ELEMENTS ie
    join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
where not exists
    (
        select *
        from [STOR].T_CONTRACT_ELEMENTS ce
        where ce.SupplierContract = ii.SupplierContract
                and
            ce.BillOfGoods = ii.BillOfGoods
                and
            ce.Price = ii.Price
                and
            ce.VAT = ii.VAT
    )

SELECT T.*
INTO #T_INVOICE_ITEM
FROM STOR.T_INVOICE_ITEM T
join STOR.T_STORAGE_INVOICE s on s.InvoiceID = T.StorageInvoice
WHERE s.type_ in (@Add, @Bet) AND EXISTS( SELECT TOP 1 * 
					FROM [STOR].T_IMPORT_ELEMENTS IE
						join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
					WHERE II.SupplierContract = T.SupplierContract)

SELECT * 
INTO #T_STORAGE_INVOICE
FROM [STOR].T_STORAGE_INVOICE s
WHERE s.InvoiceID in ( SELECT T.StorageInvoice  FROM #T_INVOICE_ITEM T)

update main
set main.Error = ISNULL(main.Error, '') + 'Итоговая сумма элементов, данной накладной и уже загруженных накладных, превышает итоговое количество по гос. контракту.
'
from [STOR].T_IMPORT_ELEMENTS main
join [STOR].T_IMPORT_INVOICE impInv on main.ImportElementsID = impInv.ImportElementsID
where (main.Error is null or main.Error = '') and exists (
    select * from [STOR].t_CONTRACT_ELEMENTS ce
    left join (
        select ii.Price, SUM(ii.Amount) as [sum], ii.SupplierContract, ii.BillOfGoods , ii.VAT
        from #T_INVOICE_ITEM ii
            join #T_STORAGE_INVOICE si on si.InvoiceID = ii.StorageInvoice
        where si.type_ = @Add -- прибавляем только приход
        group by ii.Price, ii.SupplierContract, ii.BillOfGoods, ii.VAT) 
    inc on inc.SupplierContract = ce.SupplierContract and inc.BillOfGoods = ce.BillOfGoods and inc.Price = ce.Price and inc.VAT = ce.VAT
    left join (
        select ii1.Price, SUM(ii1.Amount) as [sum], ii1.SupplierContract, ii1.BillOfGoods, ii1.VAT
        from #T_INVOICE_ITEM ii1
            join #T_STORAGE_INVOICE si on si.InvoiceID = ii1.StorageInvoice
        where si.type_ = @Bet -- отнимаем только возвратные
        group by ii1.Price, ii1.SupplierContract, ii1.BillOfGoods, ii1.VAT) 
    outc on outc.SupplierContract = ce.SupplierContract and outc.BillOfGoods = ce.BillOfGoods and outc.Price = ce.Price and outc.VAT = ce.VAT
    left join (
        select impI.Price, SUM(impI.Amount) as [sum], impI.SupplierContract, impI.BillOfGoods , impI.VAT
        from [STOR].T_IMPORT_ELEMENTS impE
            join [STOR].T_IMPORT_INVOICE impI on impE.ImportElementsID = impI.ImportElementsID
			where impE.Error is null
        group by impI.Price, impI.SupplierContract, impI.BillOfGoods, impI.VAT) 
    inpE on inpE.SupplierContract = ce.SupplierContract and inpE.BillOfGoods = ce.BillOfGoods and inpE.Price = ce.Price
    where ce.Amount < (isnull(inc.[sum], 0) + isnull(inpE.[sum], 0) - isnull(outc.[sum], 0)) and ce.SupplierContract = impInv.SupplierContract and ce.Price = impInv.Price and ce.VAT = impInv.VAT and ce.BillOfGoods = impInv.BillOfGoods
)

update main
set main.Error = ISNULL(main.Error, '') + 'Итоговая стоимость элементов, данной накладной и уже загруженных накладных, превышает итоговую сумму по гос. контракту.
'
from [STOR].T_IMPORT_ELEMENTS main
join [STOR].T_IMPORT_INVOICE impInv on main.ImportElementsID = impInv.ImportElementsID
where (main.Error is null or main.Error = '') and exists 
(
    select * from [STOR].T_SUPPLIER_CONTRACT sc
    left join (
        select ii.Price, SUM(ii.Amount * ii.Amount) as [sum], ii.SupplierContract, ii.BillOfGoods 
        from #T_INVOICE_ITEM ii
            join #T_STORAGE_INVOICE si on si.InvoiceID = ii.StorageInvoice
        where si.type_ = @Add -- прибавляем только приход
        group by ii.Price, ii.SupplierContract, ii.BillOfGoods) 
    inc on inc.SupplierContract = sc.SupplierContractID
    left join (
        select ii1.Price, SUM(ii1.Amount * ii1.Price) as [sum], ii1.SupplierContract, ii1.BillOfGoods 
        from #T_INVOICE_ITEM ii1
            join #T_STORAGE_INVOICE si on si.InvoiceID = ii1.StorageInvoice
        where si.type_ = @Bet -- отнимаем только возвратные
        group by ii1.Price, ii1.SupplierContract, ii1.BillOfGoods) 
    outc on outc.SupplierContract = sc.SupplierContractID
    left join (
        select impI.Price, SUM(impI.Amount * impI.Price) as [sum], impI.SupplierContract, impI.BillOfGoods 
        from [STOR].T_IMPORT_ELEMENTS impE
            join [STOR].T_IMPORT_INVOICE impI on impE.ImportElementsID = impI.ImportElementsID
			where impE.Error is null
        group by impI.Price, impI.SupplierContract, impI.BillOfGoods) 
    inpE on inpE.SupplierContract = sc.SupplierContractID
    where sc.Sum is not null and sc.Sum > 0 and sc.Sum < (isnull(inc.[sum], 0) + isnull(inpE.[sum], 0) - isnull(outc.[sum], 0)) and sc.SupplierContractID = impInv.SupplierContract
)

drop table  #T_STORAGE_INVOICE
drop table  #T_INVOICE_ITEM

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Указанное количество - '+ Cast(ii.Amount as varchar(max)) + ', для препарата '  + Cast(bg.OtherID as varchar(max)) + ', не соответствует коэффициенту дробления - ' + Cast(bg.Rate as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_INVOICE ii on ie.ImportElementsID = ii.ImportElementsID
join [PHARM].T_BILL_OF_GOODS bg on bg.BillOfGoodsID = ii.BillOfGoods
where (ii.Amount - Cast(ii.Amount as int)) > 0 and 
(
	bg.Rate = 0 or 
	bg.Rate = 1 or 
	((ii.Amount - Cast(ii.Amount as int)) % (1 / CAST(bg.Rate as decimal))) <> 0
 )
END

