-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-12-15
-- Description:    Загрзка гос. контрактов
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_LoadImportContract;

CREATE PROCEDURE stor.sp_LoadImportContract
(
	"UserID" uuid
)
    language plpgsql
as
$$
Begin 
    declare 
        "IdEnt" integer := (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_CONTRACT'limit 1);
        "ConId" uuid;
    
    Begin  
        
        -- Добавляем уникальный идентификатор
        Update IDB.SUPPLIER_CONTRACT set ID = gen_random_uuid();
        Update IDB.SUPPLIER_CONTRACT set gtin = null where gtin = '';
        Update IDB.SUPPLIER_CONTRACT set sgtin = null where sgtin = '';
        
        DROP TABLE IF EXISTS tTable;
		CREATE TEMP TABLE tTable(
		    GK text NOT NULL
		  , ID uuid NOT NULL
		);

        insert into tTable(GK,ID)
        select sc.IDGK, gen_random_uuid() 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"
                   ,now()
                   ,"IdEnt"
                   ,sc.FileNameColumn
            from IDB.SUPPLIER_CONTRACT sc
        where not exists(
            select * 
            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 limit 1);
        
        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
                   ,coalesce(spc.SupplierContractID, T.ID)
                   ,sc.comment
                   ,(select GoodsSupplierID from STOR.T_GOODS_SUPPLIER 
                        where code = sc.owner limit 1)
                   ,(select p.Code from STOR.T_PAYMENT_TYPE p where p.Finance = sc.tiplg limit 1)
                   ,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 pf.PackageType from PHARM.T_PRODUCT_FORM pf where pf.BillOfGoods = BG.BillOfGoodsID and pf.UnitPrice = 1 limit 1)
                   ,BG.BillOfGoodsID
                   ,sc.ID
                   ,sc.Lot
                   ,sc.IDGK
                   ,sc.VIDOPLAT
                   ,sc.Abr
        		   ,sc.VAT
        		   ,sc.gtin
        		   ,sc.sgtin
                   from IDB.SUPPLIER_CONTRACT sc
	               left join lateral (select b.BillOfGoodsID from PHARM.T_BILL_OF_GOODS b where b.OtherID=sc.Nomk_LS  limit 1) BG on true
                   join tTable T on T.GK = sc.IDGK
                   left join STOR.T_SUPPLIER_CONTRACT spc on spc.OtherID = sc.IDGK 
        where not exists(
            select * 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  limit 1);
                    
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле Дата договора.
        '
        from STOR.T_IMPORT_CONTRACT ic 
        where ie.ImportElementsID = ic.ImportElementsID and ic.Date is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле НДС.
        '
        from STOR.T_IMPORT_CONTRACT ic
        where ie.ImportElementsID = ic.ImportElementsID and ic.VAT is null or ic.VAT > 5;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле Номер договора.
        '
        from STOR.T_IMPORT_CONTRACT ic
        where ie.ImportElementsID = ic.ImportElementsID and ic.Number is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле Номер лота.
        '
        from STOR.T_IMPORT_CONTRACT ic  
        where ie.ImportElementsID = ic.ImportElementsID and ic.LotNumber is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не найден поставщик по указанным данным - ' || Cast(sc.owner as text) || '
        '
        from STOR.T_IMPORT_CONTRACT ic  
            join IDB.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
        where ie.ImportElementsID = ic.ImportElementsID and ic.GoodsSupplier is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не найден вид оплаты по указанным данным - ' || Cast(sc.tiplg as text) || '
        '
        from STOR.T_IMPORT_CONTRACT ic 
            join IDB.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
        where ie.ImportElementsID = ic.ImportElementsID and ic.PaymentType is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле Количество.
        '
        from STOR.T_IMPORT_CONTRACT ic 
        where ie.ImportElementsID = ic.ImportElementsID and ic.Amount is null and ic.Price is not null and ic.PackageType is not null and ic.BillOfGoods is not null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не найдена номенклатура товаров по указанным данным - ' || Cast(sc.Nomk_LS as text) || Cast(sc.NAME_MED as text) ||'
        '
        from STOR.T_IMPORT_CONTRACT ic 
            join IDB.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
        where ie.ImportElementsID = ic.ImportElementsID and ic.Amount is not null and ic.Price is not null and ic.BillOfGoods is null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'У препарата не указан gtin или не равен тому что указан в dbf. Препарат - ' || Cast(sc.Nomk_LS as text) || Cast(sc.NAME_MED as text) ||'
        '
        from STOR.T_IMPORT_CONTRACT ic 
            join IDB.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
            join PHARM.T_BILL_OF_GOODS bg on ic.BillOfGoods = bg.BillOfGoodsID
        where ie.ImportElementsID = ic.ImportElementsID and ic.gtin is not null and (bg.Gtin is null or bg.Gtin <> ic.gtin);
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не указано поле Цена
        '
        from STOR.T_IMPORT_CONTRACT ic
        where ie.ImportElementsID = ic.ImportElementsID and ic.Amount is not null and ic.Price is null and ic.PackageType is not null and ic.BillOfGoods is not null;
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Не найден вид упаковки по указанным данным. Препарат - ' || Cast(sc.Nomk_LS as text) || Cast(sc.NAME_MED as text) ||'
        '
        from STOR.T_IMPORT_CONTRACT ic 
            join IDB.SUPPLIER_CONTRACT sc on sc.ID = ic.ImportElementsID
        where ie.ImportElementsID = ic.ImportElementsID and ic.Amount is not null and ic.Price is not null and ic.PackageType is null;
                 
        update  STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Данный лот уже загружен и различается цена.
        '
        from STOR.T_IMPORT_CONTRACT ic
        where ie.ImportElementsID = ic.ImportElementsID and 
        exists(
            select * 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  limit 1);
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Данный лот уже загружен и различается НДС.
        '
        from STOR.T_IMPORT_CONTRACT ic 
        where ie.ImportElementsID = ic.ImportElementsID and 
        exists(
            select * 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 limit 1);
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Данный лот уже загружен.
        '
        from STOR.T_IMPORT_CONTRACT ic
        where ie.ImportElementsID = ic.ImportElementsID and 
        exists(
            select * 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 limit 1);
        
        update STOR.T_IMPORT_ELEMENTS ie
        set Error = coalesce(ie.Error, '') || 'Дублирование лотов.
        '
        from STOR.T_IMPORT_CONTRACT ic 
        where ie.ImportElementsID = ic.ImportElementsID and 
        exists(
            select * 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 limit 1);
        
        drop table tTable;
     end; 
End;
$$;