4th

use master
drop database yuliyayakovenko
create database yuliyayakovenko
use yuliyayakovenko


create table Providers (
    id int identity(1, 1) primary key,
    pricePerBrick float,
    discountPriceThreshold float,
    discount float,
    deliveryPrice float
)


go
create function GetProviderPrice(@providerId int, @bricks float) returns float
begin
    declare @ppb float, @dpt float, @discount float, @dp float;
    select
        @ppb = P.pricePerBrick,
        @discount = P.discount,
        @dpt = P.discountPriceThreshold,
        @dp = P.deliveryPrice
    from Providers P where P.id = @providerId;
    return (
        case when (@ppb * @bricks > @dpt) then
            @ppb * @bricks + @dp * ((100 - @discount) / 100.0)
        else
            @ppb * @bricks + @dp
        end
    )
end
go


go
create function GetBestProviders(@brickWeight float, @totalWeight float)
returns @result table (id int, price float) as
begin
    declare @bricks int, @minPrice float
    set @bricks = ceiling(@totalWeight / @brickWeight)
    select @minPrice = min(dbo.GetProviderPrice(P.id, @bricks)) from Providers P
    insert @result select id, @minPrice as price from Providers
        where dbo.GetProviderPrice(id, @bricks) = @minPrice
    return
end
go


go
create function GetBreakPoint(@providerId int) returns float
begin
    return (select (discountPriceThreshold / pricePerBrick) from Providers where id = @providerId)
end
go


go
create function GetDiscountDelivery(@providerId int) returns float
begin
    return (select ((100 - discount) / 100) * deliveryPrice from Providers where id = @providerId)
end
go


go
create function GetSegments()
returns @result table (segmentS int, segmentE int) as
begin
    declare @coords table (coordX float)
    declare @scoords table (id int identity(1, 1) primary key, coordX float)
    declare @LENGTH int
    set @LENGTH = 1

    -- Здесь расставляются точки на оси X (кирпичи):
    insert @coords
        select abs(A.deliveryPrice - B.deliveryPrice) / abs(A.pricePerBrick - B.pricePerBrick)
        from Providers A cross join Providers B
        where A.id != B.id

    insert @coords
        select abs(dbo.GetDiscountDelivery(A.id) - dbo.GetDiscountDelivery(B.id)) / abs(A.pricePerBrick - B.pricePerBrick)
        from Providers A cross join Providers B
        where A.id != B.id

    insert @coords
        select abs(dbo.GetDiscountDelivery(A.id) - B.deliveryPrice) / abs(A.pricePerBrick - B.pricePerBrick)
        from Providers A cross join Providers B
        where A.id != B.id

    insert @coords select dbo.GetBreakPoint(id) from Providers
    insert @coords values (0), (9999999)
    insert @scoords select distinct * from @coords order by coordX
    select @LENGTH = count(*) - 1 from @coords
    insert @result select A.coordX, B.coordX from
        (select * from @scoords order by coordX OFFset 0 rows fetch next @LENGTH rows onLY) A join
        (select * from @scoords order by coordX OFFset 1 rows) B on A.id + 1 = B.id
        order by A.coordX
    return
end
go


go
create function GetProvideronSegment(@segmentS int, @segmentE int) returns int
begin
    declare @minPrice float
    select @minPrice = min(dbo.GetProviderPrice(id, (@segmentS+@segmentE) / 2 )) from Providers
    return (select top 1 id from Providers where dbo.GetProviderPrice(id, (@segmentS+@segmentE) / 2) = @minPrice)
end
go


go
create function Getoptimaltable()
returns @result table (id int, segmentS int, segmentE int, providerId int) as
begin
    declare @lastId int, @lastProviderId int
    declare @tmp table (id int identity(1, 1) primary key, segmentS int, segmentE int, providerId int)

    insert @tmp
        select segmentS, segmentE, dbo.GetProvideronSegment(segmentS, segmentE)
        from dbo.GetSegments() order by segmentS
    select @lastId = count(*) from @tmp
    set @lastProviderId = -99999999
    while (@lastId > 0)
    begin
        if ((select providerId from @tmp where id = @lastId) = @lastProviderId)
        begin
            with LR as (select top 1 * from @result order by id asC)
            update LR set segmentS = (select segmentS from @tmp where id = @lastId)
        end
        else
        begin
            insert @result select * from @tmp where id = @lastId
        end
        select @lastProviderId = providerId from @tmp where id = @lastId
        set @lastId -= 1
    end
    return
end
go


insert into Providers values (10, 0, 0, 7000)
insert into Providers values (12, 30000, 100, 6000)
insert into Providers values (14, 33000, 70, 5500)


select id as N'Продавец', dbo.GetProviderPrice(id, 2700) as N'Цена' from Providers
select id as N'Продавец', price as N'Цена' from dbo.GetBestProviders(1, 2700)
select
    formatmessage('%d-%d', ceiling(segmentS), ceiling(segmentE)) as N'Количество кирпичей',
    providerId as N'Продавец',
    dbo.GetProviderPrice(P.id, segmentS) as N'Стартовая цена',
    dbo.GetProviderPrice(P.id, segmentE) as N'Конечная цена'
    from dbo.Getoptimaltable() join Providers P on P.id = providerId