Untitled

f""" declare @PhysicalStoreID dbo.BIDENT = {physical_store}, @BTUserID dbo.BIDENT = {bt_user}, @ExpressReplenishmentFromDCID dbo.BIDENT, @PhysicalStoreRechargeID dbo.BIDENT, @StoreTaskID dbo.BIDENT, @CellID dbo.BIDENT, @ItemID dbo.BIDENT, @ExemplarSerialNumber varchar(100), @QTY int, @EAMSID dbo.BIDENT = {eams_id};

begin tran

set @ExpressReplenishmentFromDCID = (select er.ID from ExpressReplenishmentFromDC er where er.EAMSID = @EAMSID)

--обход ошибки завершения конца маршрута
if not exists (select 1 from BTUserRole where BTUserID = @BTUserID and RoleID = 163)
    insert into BTUserRole( BTUserID , RoleID)
    values (@BTUserID, 163)

--добавляем права на выдачу визы
if not exists (select 1 from BTUserRole where BTUserID = @BTUserID and RoleID = 577)
    insert into BTUserRole( BTUserID , RoleID)
    values (@BTUserID, 577)

--виза старшего
--exec dbo.StoreTaskAllowAdditionalCloseSet @StoreTaskID = @StoreTaskID, @BTUserID = @BTUserID

    --начитываем задания в курсор по заявке
    declare Cur cursor local static for
    select
        psrst.StoreTaskID
    from ExpressReplenishmentFromDC er 
        left join dbo.PhysicalStoreRecharge psr on er.ID = psr.ExpressReplenishmentFromDCID
        left join dbo.PhysicalStoreRechargeStoreTask psrst on psrst.PhysicalStoreRechargeID = psr.ID
        left join dbo.StoreTask st on st.ID = psrst.StoreTaskID
    where er.EAMSID = @EAMSID and st.StoreTaskStatusID in (111,112,113);
        open Cur;
        fetch next from Cur into @StoreTaskID;
	        while @@fetch_status=0
	        begin
                --назначаем задание на себя (перейдёт из 111 или 112 статуса в 113)
                if exists (select 1 from dbo.StoreTask where id = @StoreTaskID and BTUserID is null)
                    exec StoreTaskUpdBTUser @StoreTaskID = @StoreTaskID ,@BTUserID = @BTUserID;

                    --размещаем экземпляры по заданию
                    declare CurExemplar cursor local static for
                    select sti.ItemID,sti.QTY from dbo.StoreTaskItem sti 
                    where sti.StoreTaskID = @StoreTaskID
                    open CurExemplar;
                    fetch next from CurExemplar into @ItemID,@QTY;
	                    while @@fetch_status=0
	                    begin
                            --QTY по отдельному Item
                            while @QTY != 0 
                            begin
                                --выбираем ШК экземпляра
                                select top (1)
                                    @ExemplarSerialNumber = e.SerialNumber,
                                    @CellID = c.ID
                                from dbo.Exemplar e
                                    inner join dbo.Cell c on c.ID = e.CellID and c.PhysicalStoreID = @PhysicalStoreID and c.StoreID = 1
                                where e.ItemID = @ItemID
                                --защита от повторного считывания экземпляра, когда @QTY > 1
                                and not exists (select 1 
                                                from dbo.StoreTaskItemExemplar stie
                                                inner join dbo.StoreTaskItem sti on sti.ID = stie.StoreTaskItemID
                                                where   sti.StoreTaskID = @StoreTaskID 
                                                    and stie.ExemplarID != e.ID) --and CellID = 3490386 
                                and not exists( select  top (1) 1
                                                from    ExemplarInProcessing EP with(nolock)
                                                where   EP.ExemplarID = e.ID);

                                if @ExemplarSerialNumber is not null
                                begin
                                    --пытаемся разместить экземпляр по заданию
                                    exec dbo.StoreTaskItemInsByExemplar @ID = @StoreTaskID,
                                                                        @SerialNumber = @ExemplarSerialNumber,
                                                                        @CellID = @CellID;
                                end
                                else
                                begin 
                                    exec dbo.Abort @Code = @@procID, @Message = 'Не удалось обнаружить подходящий экземпляр на складе Задание:(%s), EAMSID = (%s), ItemID = (%s), PhysicalStoreID = (%s)' , @p1 = @StoreTaskID, @p2 = @EAMSID, @p3 = @ItemID , @p4 = @PhysicalStoreID
                                end


                                set @QTY = @QTY-1;
                            end;
	                    fetch next from CurExemplar into @ItemID,@QTY;
	                    end
                    close CurExemplar;
                    deallocate CurExemplar;

            --Получаем визу на закрытие
            exec dbo.StoreTaskAllowCloseSet @StoreTaskID = @StoreTaskID, @BTUserID = @BTUserID

            ----Переводим задание из 113 в 114 и потом в 115
            exec dbo.StoreTaskConsolidate @ID = @StoreTaskID, @BTUserID = @BTUserID

            fetch next from Cur into @StoreTaskID;
	        end
    close Cur;
    deallocate Cur;

    --смена статуса всех подпиток
    declare Cur cursor local static for
    select
        psr.ID
    from ExpressReplenishmentFromDC er 
        left join dbo.PhysicalStoreRecharge psr on er.ID = psr.ExpressReplenishmentFromDCID
    where er.EAMSID = @EAMSID 
        open Cur;
        fetch next from Cur into @PhysicalStoreRechargeID;
	        while @@fetch_status=0
	        begin    
                --смена статуса подпитки
                exec dbo.PhysicalStoreRechargeStatusDispatcher  @ID = @PhysicalStoreRechargeID,
                                                                @PhysicalStoreRechargeStatusID = 5, --Подбор завершен
                                                                @Force = 1;

            fetch next from Cur into @PhysicalStoreRechargeID;
	        end
    close Cur;
    deallocate Cur;

    --смена статуса заявки
    exec dbo.ExpressReplenishmentFromDCStatusDispatcher @ExpressReplenishmentFromDCID = @ExpressReplenishmentFromDCID,
                                                        @RequestStateID = 40; --подбор завершён
    select 
        er.ID,
        er.RequestStateID,
        psr.ID,
        PhysicalStoreRechargeStatusID,
        st.ID,
        st.StoreTaskStatusID,
        b.ID,
        b.BoxingStatusID,
        t.ID,
        t.TransferStatusID 
    from dbo.ExpressReplenishmentFromDC er
        left join dbo.PhysicalStoreRecharge psr on er.ID = psr.ExpressReplenishmentFromDCID
        left join dbo.PhysicalStoreRechargeStoreTask psrst on psrst.PhysicalStoreRechargeID = psr.ID
        left join dbo.StoreTask st on st.ID = psrst.StoreTaskID
        left join dbo.StoreTaskBoxingTransfer stbt on stbt.StoreTaskID = st.ID
        left join dbo.Boxing b on b.ID = stbt.BoxingID
        left join dbo.[Transfer] t on t.ID = stbt.TransferID
    where   er.EAMSID = @EAMSID
commit tran


""")