-- =============================================
-- 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;
$$;