-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-12-15
-- Description:    Загрзка гос. контрактов
-- =============================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[idb].[SUPPLIER_CONTRACT]') AND type in (N'U'))
DROP TABLE [idb].[SUPPLIER_CONTRACT]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STOR].[sp_LoadImportContract]') AND type in (N'P', N'PC'))
DROP PROCEDURE [STOR].[sp_LoadImportContract]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

declare @IdEnt int
select @IdEnt = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_CONTRACT'

declare @ConId uniqueidentifier

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

declare @t table(
    [GK] [varchar](max) NOT NULL,
    [ID] [uniqueidentifier] NOT NULL)

insert into @t([GK],[ID])
select sc.[IDGK], newID() ID from idb.SUPPLIER_CONTRACT sc 
group by sc.[IDGK]


INSERT INTO [STOR].[T_IMPORT_ELEMENTS]
           ([ImportElementsID]
           ,[Error]
           ,[Users]
           ,[LoadDate]
           ,[type_]
           ,[FileName])
     SELECT
           sc.ID
           ,null
           ,@UserID
           ,getdate()
           ,@IdEnt
           ,sc.FileNameColumn
    from idb.SUPPLIER_CONTRACT sc
where not exists(
    select TOP 1 * from [STOR].T_CONTRACT_ELEMENTS ce
    join [STOR].T_SUPPLIER_CONTRACT ssc on ssc.SupplierContractID = ce.SupplierContract
         where ce.LotNumber = sc.Lot and sc.Price = ce.Price and ssc.OtherID = sc.IDGK)

INSERT INTO [STOR].[T_IMPORT_CONTRACT]
           ([Date]
           ,[SupplierContractID]
           ,[Comment]
           ,[GoodsSupplier]
           ,[PaymentType]
           ,[Number]
           ,[Amount]
           ,[Price]
           ,[Sum]
           ,[PackageType]
           ,[BillOfGoods]
           ,[ImportElementsID]
           ,[LotNumber]
           ,[OtherID]
           ,[PayType]
           ,[Abr]
		   ,[VAT]
		   ,[gtin]
		   ,[sgtin])
     SELECT 
           sc.dt
           ,ISNULL(spc.SupplierContractID, T.ID)
           ,sc.comment
           ,(select top 1 GoodsSupplierID from [STOR].T_GOODS_SUPPLIER 
                where code = sc.[owner])
           ,(case
                when sc.tiplg = 1 then 201
                when sc.tiplg = 2 then 301
                when sc.tiplg = 3 then 401
                when sc.tiplg = 4 then 501
                when sc.tiplg = 7 then 601
                when sc.tiplg = 8 then 602
                when sc.tiplg = 9 then 703
                end)
           ,sc.nGK
           ,sc.Ko_All
           ,sc.Price
           ,round(Cast(sc.Ko_All as decimal(12,2)) * Cast(sc.Price as decimal(12,2)),4)
           ,(Select TOP 1 pf.PackageType from [PHARM].T_PRODUCT_FORM pf 
               where pf.BillOfGoods = BG.BillOfGoodsID and pf.UnitPrice = 1)
           ,BG.BillOfGoodsID
           ,sc.ID
           ,sc.Lot
           ,sc.IDGK
           ,sc.VIDOPLAT
           ,sc.Abr
		   ,sc.VAT
		   ,sc.gtin
		   ,sc.sgtin
           from idb.SUPPLIER_CONTRACT sc
           OUTER APPLY (select top 1 b.BillOfGoodsID from [PHARM].T_BILL_OF_GOODS b where b.OtherID=sc.Nomk_LS) BG
            join @t T on T.[GK] = sc.IDGK
            left join [STOR].T_SUPPLIER_CONTRACT spc on spc.OtherID = sc.IDGK 
where not exists(
    select TOP 1 * from [STOR].T_CONTRACT_ELEMENTS ce
    join [STOR].T_SUPPLIER_CONTRACT ssc on ssc.SupplierContractID = ce.SupplierContract
         where ce.LotNumber = sc.Lot and sc.Price = ce.Price and ssc.OtherID = sc.IDGK)
            
update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Дата договора.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
where ic.[Date] is null

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

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

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Номер лота.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
where ic.LotNumber is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден поставщик по указанным данным - ' + Cast(sc.[owner] as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
join idb.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
where ic.[GoodsSupplier] is null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден вид оплаты по указанным данным - ' + Cast(sc.tiplg as varchar(max)) + '
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
join idb.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
where ic.PaymentType is null

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

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найдена номенклатура товаров по указанным данным - ' + Cast(sc.Nomk_LS as varchar(max)) + Cast(sc.NAME_MED as varchar(max)) +'
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
join idb.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
where ic.Amount is not null and ic.Price is not null and ic.BillOfGoods is null

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

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не указано поле Цена
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
where ic.Amount is not null and ic.Price is null and ic.PackageType is not null and ic.BillOfGoods is not null

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Не найден вид упаковки по указанным данным. Препарат - ' + Cast(sc.Nomk_LS as varchar(max)) + Cast(sc.NAME_MED as varchar(max)) +'
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].[T_IMPORT_CONTRACT] ic on ie.ImportElementsID = ic.ImportElementsID
join idb.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
where ic.Amount is not null and ic.Price is not null and ic.PackageType is null

         
update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данный лот уже загружен и различается цена.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_CONTRACT ic on ie.ImportElementsID = ic.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_CONTRACT_ELEMENTS ce
         where ce.LotNumber = ic.LotNumber and ic.Price <> ce.Price and ic.VAT = ce.VAT and ce.SupplierContract = ic.SupplierContractID and ic.BillOfGoods = ce.BillOfGoods)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данный лот уже загружен и различается НДС.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_CONTRACT ic on ie.ImportElementsID = ic.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_CONTRACT_ELEMENTS ce
         where ce.LotNumber = ic.LotNumber and ic.Price = ce.Price and ic.VAT <> ce.VAT and ce.SupplierContract = ic.SupplierContractID and ic.BillOfGoods = ce.BillOfGoods)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Данный лот уже загружен.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_CONTRACT ic on ie.ImportElementsID = ic.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_CONTRACT_ELEMENTS ce
         where ic.Price = ce.Price and ic.VAT = ce.VAT and ce.SupplierContract = ic.SupplierContractID and ic.BillOfGoods = ce.BillOfGoods)

update ie
set ie.Error = ISNULL(ie.Error, '') + 'Дублирование лотов.
'
from [STOR].T_IMPORT_ELEMENTS ie
join [STOR].T_IMPORT_CONTRACT ic on ie.ImportElementsID = ic.ImportElementsID
where exists(
    select TOP 1 * from [STOR].T_IMPORT_ELEMENTS ie1
	join [STOR].T_IMPORT_CONTRACT ic1 on ie1.ImportElementsID = ic1.ImportElementsID
         where ic1.Price = ic.Price and ic.VAT = ic1.VAT and ic.SupplierContractID = ic1.SupplierContractID and ic1.BillOfGoods = ic.BillOfGoods and ie.ImportElementsID <> ie1.ImportElementsID)


END