Whitelightsoft

alter PROCEDURE [dbo].[BarLoad_getDisplay]
@EventID int,
@Attempt varchar(25)
AS

create table #BarLoadDisplay
(
RowNumber int identity (1, 1),
OutLine varchar(500)
)

insert into #BarLoadDisplay
(OutLine)
values
(' Attempting: ' + @Attempt + ' lbs')

insert into #BarLoadDisplay
(OutLine)
values
('')

declare @WeightWork numeric(8,2)
declare @BarWeight2 numeric(8,2)
declare @LockWeight2 numeric(8,2)
declare @Plates numeric(15,8)
declare @Plates2 integer
declare @PlateWord varchar(20)

set @BarWeight2 = isnull(( select top 1 a.BarWeight
from Accounts a join
Users u on u.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u.UserID
where e.EventID = @EventID
),45.0)

set @LockWeight2 = isnull(( select top 1
                              a.LockWeight
from Accounts a join
Users u on u.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u.UserID
where e.EventID = @EventID
),2.5)

set @WeightWork =
  isnull(convert(numeric(8,2),@Attempt) -
     @BarWeight2 - @LockWeight2*2,0)

insert into #BarLoadDisplay
(OutLine)
values
('  Load each side of the bar as follows...')

insert into #BarLoadDisplay
(OutLine)
values
('')

DECLARE @weight numeric(8,2),
        @Color varchar(25),
        @Count int,
        @barweight numeric(8,2),
        @lockweight numeric(8,2)

DECLARE BarLoadList_cursor CURSOR FOR
select bl.weight, bl.color, bl.count,
       a.barweight, a.lockweight
from BarLoad bl join
Users u on u.UserID = bl.CreatedByUserID join
Accounts a on a.AccountID = u.AccountID join
Users u2 on u2.AccountID = a.AccountID join
Events e on e.CreatedByUserID = u2.UserID
where e.EventID = @EventID
  and isnull(bl.lbsFlag,'N') = 'Y'
order by bl.weight desc

OPEN BarLoadList_cursor

FETCH NEXT FROM BarLoadList_cursor
INTO @weight, @Color, @Count,
     @barweight, @lockweight

WHILE @@FETCH_STATUS = 0 and
      @WeightWork > 0
BEGIN

if @WeightWork >= (@Weight * 2.0)
begin

  set @Plates =
      ROUND((@WeightWork/@Weight),0,1)

  if @Plates/2 <> ROUND((@Plates/2),0,1)
  begin
    set @Plates = @Plates - 1
  end

  if @Plates > @Count
  begin
    set @Plates = @Count
  end

  if @Plates/2 <> ROUND((@Plates/2),0,1)
  begin
    set @Plates = @Plates - 1
  end

  set @Plates2 = ROUND((@Plates/2),0,1)

  if @Plates2 > 1
  begin
    set @PlateWord = 'Plates'
  end
  else
  begin
    set @PlateWord = 'Plate'
  end

  insert into #BarLoadDisplay
  (OutLine)
  values
  ('      ' + convert(varchar(50),@Plates2) + ' ' +
      @Color + ' ' + convert(varchar(50),@weight) +
      ' lb ' + @PlateWord)

  set @WeightWork =
      @WeightWork - (@weight * @Plates)
end

FETCH NEXT FROM BarLoadList_cursor
INTO @weight, @Color, @Count, @barweight,
     @lockweight

END

CLOSE BarLoadList_cursor
DEALLOCATE BarLoadList_cursor

select OutLine
from #BarLoadDisplay
order by RowNumber