CREATE PROCEDURE [InsertItem]
@StrUserID varchar(30),
@Source tinyint,
@dwID int,
@StackSize int
AS
DECLARE
@length int,
@row int,
@i int,
@dwid int,
@pos int,
@dur int,
@dur1 int,
@ext int,
@StackSize1 int,
@strName varchar(30),
@strExtName varchar(30),
@Cdwid varbinary(4),
@cdur varchar(2),
@cstack varchar(2)
BEGIN TRAN
If (@StackSize > 9999) or (@StackSize < 1)
begin
select @StrUserID, 0, 0, 0, 'Invalid StackSize', 'Stack size 1 > 9999'
ROLLBACK TRAN
RETURN
end
If (@Source > 2) or (@Source < 1)
begin
select @StrUserID, 0, 0, 0, 'Invalid source', '1=Inventory, 2=Warehouse'
ROLLBACK TRAN
RETURN
end
If @Source = 1
Begin
set @i = 14*8
set @length = 401
Select @row = count(*) From UserData Where strUserID like @StrUserID
If @row = 0 or @row > 1
Begin
Select @StrUserID, 0, 0, 0, 'Invalid ID', 'Please check the name against the datasource'
ROLLBACK TRAN
RETURN
End
Select @row = count(*) From UserData Where (strUserID like @StrUserID) and (strItem is NULL)
If @row > 0
Begin
Select @StrUserID, 0, 0, 0, 'No Data', 'Inventory is NULL'
ROLLBACK TRAN
RETURN
End
End
If @Source = 2
Begin
set @i = 1
set @length =1601
Select @row = count(*) From Warehouse Where strAccountID like @StrUserID
If @row = 0 or @row > 1
Begin
Select @StrUserID, 0, 0, 0, 'Invalid ID', 'Please check the name against the datasource'
ROLLBACK TRAN
RETURN
End
Select @row = count(*) From Warehouse Where (strAccountID like @StrUserID) and (WarehouseData is NULL)
If @row > 0
Begin
Select @StrUserID, 0, 0, 0, 'No Data', 'Warehouse is NULL'
ROLLBACK TRAN
RETURN
End
End
select @row = count(*) from InventoryView where strUserID = @StrUserId
select @row = count(*) from InventoryView where strUserID = @StrUserId
if @row > 0
begin
select @StrUserID, 0, 0, 0, 'In Use', 'Please refresh'
ROLLBACK TRAN
RETURN
end
WHILE @i < @length
Begin
If @source = 1
Select @dwid=cast(cast(substring(cast(substring(strItem, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int(4)),
@dur = cast(cast(cast( substring(strItem, @i+5, 1) as varbinary(1))+cast(substring(strItem, @i+4, 1) as varbinary(1)) as varbinary(2)) as smallint),
@StackSize1 = cast(cast(cast( substring(strItem, @i+7, 1) as varbinary(1))+cast(substring(strItem, @i+6, 1) as varbinary(1)) as varbinary(2)) as smallint),
@StrUserID = strUserID
From UserData
Where strUserID = @StrUserID
If @source = 2
Select @dwid=cast(cast(substring(cast(substring(WareHouseData, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(WareHouseData, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(WareHouseData, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(WareHouseData, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int(4)),
@dur = cast(cast(cast( substring(WareHouseData, @i+5, 1) as varbinary(1))+cast(substring(WareHouseData, @i+4, 1) as varbinary(1)) as varbinary(2)) as smallint),
@StackSize1 = cast(cast(cast( substring(WareHouseData, @i+7, 1) as varbinary(1))+cast(substring(WareHouseData, @i+6, 1) as varbinary(1)) as varbinary(2)) as smallint),
@StrUserID = strAccountID
From Warehouse
Where strAccountID = @StrUserID
If @dwid = 0
begin
If @dur = 0
Begin
If @StackSize1 = 0
Begin
select @ext = byExtIndex, @dur1 = siMaxDurability from item_basic where dwid = cast(substring(cast(@dwid as varchar(9)), 1, 6)+'000' as int(4))
Set @dur = -1
If @ext = 0
select @dur = siMaxDurability from itemext0 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 1
select @dur = siMaxDurability from itemext1 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 2
select @dur = siMaxDurability from itemext2 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 3
select @dur = siMaxDurability from itemext3 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 4
select @dur = siMaxDurability from itemext4 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 5
select @dur = siMaxDurability from itemext5 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 6
select @dur = siMaxDurability from itemext6 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 7
select @dur = siMaxDurability from itemext7 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 8
select @dur = siMaxDurability from itemext8 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 9
select @dur = siMaxDurability from itemext9 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 10
select @dur = siMaxDurability from itemext10 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 11
select @dur = siMaxDurability from itemext11 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 12
select @dur = siMaxDurability from itemext12 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 13
select @dur = siMaxDurability from itemext13 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 14
select @dur = siMaxDurability from itemext14 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 15
select @dur = siMaxDurability from itemext15 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 16
select @dur = siMaxDurability from itemext16 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 17
select @dur = siMaxDurability from itemext17 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 18
select @dur = siMaxDurability from itemext18 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 19
select @dur = siMaxDurability from itemext19 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 20
select @dur = siMaxDurability from itemext20 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 21
select @dur = siMaxDurability from itemext21 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @ext = 22
select @dur = 1
If @ext = 23
select @dur = siMaxDurability from itemext23 where dwid = cast(substring(cast(@dwid as varchar(9)), 7, 9) as int(4))
If @dur = -1
Begin
Select @StrUserID, 0, 0, 0, 'Invalid Item', 'Please check the item code'
ROLLBACK TRAN
RETURN
End
Set @dur = @dur + @dur1
--Set @StackSize = 1
If @StackSize > 1
Set @dur = 1
Set @Cdwid = Substring(cast(@dwid as varbinary(4)), 4, 1) + Substring(cast(@dwid as varbinary(4)), 3, 1) + Substring(cast(@dwid as varbinary(4)), 2, 1) + Substring(cast(@dwid as varbinary(4)), 1, 1)
Set @Cdur = cast(Substring(cast(@dur as varbinary(2)), 2, 1)+Substring(cast(@dur as varbinary(2)), 1, 1) as varchar(2))
Set @Cstack = cast(Substring(cast(@StackSize as varbinary(2)), 2, 1)+Substring(cast(@StackSize as varbinary(2)), 1, 1) as varchar(2))
If @Source = 1
Begin
update UserData set strItem = cast( substring(strItem, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(strItem, @i+8, 401-@i) as binary(400)) where strUserID = @strUserID
COMMIT TRAN
RETURN
End
If @Source = 2
Begin
--select cast(warehousedata as varbinary(1600)) from warehouse where strAccountID = @strUserID
--select cast( substring(WareHouseData, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(WareHouseData, @i+8, 1601-@i) as binary(1600)) from warehouse where strAccountID = @strUserID
update WareHouse set WareHouseData = cast( substring(WareHouseData, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(WareHouseData, @i+8, 1601-@i) as binary(1600)) where strAccountID = @strUserID
COMMIT TRAN
RETURN
End
End
End
End
set @i = @i + 8
End
GO |
