create or alter function GetCustomerList()
    returns table AS
        return
            (
                select distinct customer_name
                from orders
            );
go;

--drop function SeregaTheDed_SQLLogin_1.GetRollbackCustomer

create or alter function GetRollbackCustomer()
    returns @RollbackCustomerTable TABLE
                                   (
                                       Customer NVARCHAR(255) NOT NULL,
                                       [01]     float default (0),
                                       [02]     float default (0),
                                       [03]     float default (0),
                                       [04]     float default (0),
                                       [05]     float default (0),
                                       [06]     float default (0),
                                       [07]     float default (0),
                                       [08]     float default (0),
                                       [09]     float default (0),
                                       [10]     float default (0),
                                       [11]     float default (0),
                                       [12]     float default (0)
                                   )
AS
begin
    declare @CurrentCustomerName nvarchar(255);
    declare CustomersCursor cursor scroll for select * from SeregaTheDed_SQLLogin_1.GetCustomerList();

    open CustomersCursor;


    FETCH NEXT FROM CustomersCursor INTO @CurrentCustomerName
    while @@fetch_status = 0
        begin
            insert into @RollbackCustomerTable(Customer)
            values (@CurrentCustomerName);

            declare @AmountPercent float = 0.1;
            declare @CurrentMonthNumber int = 1;
            while @CurrentMonthNumber <= 12
                begin
                    declare @TotalMonthAmount float;
                    select @TotalMonthAmount = sum(p5.price + p5.extra)
                    from orders o
                             join rel_orders_products rop5 on o.id = rop5.order_id
                             join products p5 on p5.id = rop5.product_id
                    where customer_name = @CurrentCustomerName
                      and month(date) = @CurrentMonthNumber;

                    declare @KostilQuery nvarchar(max);
                    set @KostilQuery =
                                'update @RollbackCustomerTable
                                set [' + FORMAT(@CurrentMonthNumber, '00') + '] = ' +
                                CAST((@TotalMonthAmount * 0.1) as nvarchar(255)) +
                                'where Customer = N''' + @CurrentCustomerName + '''';
                    --exec(@KostilQuery);
                    --sp_executesql @KostilQuery;
                    --exec @KostilQuery;
                    --exec testtest

                    if @CurrentMonthNumber = 1
                        update @RollbackCustomerTable
                        set [01] = ISNULL( @TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 2
                        update @RollbackCustomerTable
                        set [02] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 3
                        update @RollbackCustomerTable
                        set [03] = ISNULL( @TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 4
                        update @RollbackCustomerTable
                        set [04] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 5
                        update @RollbackCustomerTable
                        set [05] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 6
                        update @RollbackCustomerTable
                        set [06] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 7
                        update @RollbackCustomerTable
                        set [07] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 8
                        update @RollbackCustomerTable
                        set [08] =ISNULL( @TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 9
                        update @RollbackCustomerTable
                        set [09] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 10
                        update @RollbackCustomerTable
                        set [10] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 11
                        update @RollbackCustomerTable
                        set [11] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName
                    else if @CurrentMonthNumber = 12
                        update @RollbackCustomerTable
                        set [12] = ISNULL(@TotalMonthAmount * @AmountPercent, 0)
                        where Customer = @CurrentCustomerName


                    set @CurrentMonthNumber = @CurrentMonthNumber + 1;
                end

            FETCH NEXT FROM CustomersCursor INTO @CurrentCustomerName
        end

    close CustomersCursor;
    deallocate CustomersCursor;
    return
end;
go;


select *
from SeregaTheDed_SQLLogin_1.GetRollbackCustomer()