-
Shop query
If you have problems with items when you want to add more on npc or item mall you can run this query to clean tables for invalid items and to rewrite slotindex but keeping old order that apear in the client
Hope this will help someone
Code:
DELETE from _RefScrapOfPackageItem where RefItemCodeName not in (SELECT CodeName128 from _RefObjCommon where Service=1)
DELETE from _RefShopGoods where RefPackageItemCodeName not in (SELECT CodeName128 from _RefPackageItem)
DELETE from _RefShopGoods where RefPackageItemCodeName not in (SELECT RefPackageItemCodeName from _RefPricePolicyOfItem)
DELETE from _RefShopGoods where RefPackageItemCodeName not in (SELECT RefPackageItemCodeName from _RefScrapOfPackageItem)
DELETE from _RefPackageItem where CodeName128 not in (SELECT RefPackageItemCodeName FROM _RefShopGoods )
DELETE from _RefPricePolicyOfItem where RefPackageItemCodeName not in (SELECT RefPackageItemCodeName FROM _RefShopGoods )
DELETE from _RefScrapOfPackageItem where RefPackageItemCodeName not in (SELECT RefPackageItemCodeName FROM _RefShopGoods )
UPDATE _RefPackageItem set Service=0
UPDATE _RefPricePolicyOfItem set Service=0
UPDATE _RefScrapOfPackageItem set Service=0
UPDATE _RefPackageItem set Service=1 where CodeName128 in (SELECT RefPackageItemCodeName FROM _RefShopGoods where Service=1)
UPDATE _RefPricePolicyOfItem set Service=1 where RefPackageItemCodeName in (SELECT RefPackageItemCodeName FROM _RefShopGoods where Service=1)
UPDATE _RefScrapOfPackageItem set Service=1 where RefPackageItemCodeName in (SELECT RefPackageItemCodeName FROM _RefShopGoods where Service=1)
GO
ALTER TABLE [dbo].[_RefShopGoods] ADD [Order] int NULL
GO
UPDATE _RefShopGoods set [Order]=0
DECLARE @stop INT
DECLARE @counter INT
DECLARE @maxi INT
Set @stop=0
WHILE @stop=0
BEGIN
SELECT @maxi = max([order]) from _RefShopGoods
UPDATE [dbo].[_RefShopGoods] SET [order]=@maxi+1 where RefTabCodeName in (SELECT TOP (1) RefTabCodeName FROM [dbo].[_RefShopGoods] where [Order]=0 ORDER BY [RefTabCodeName], [SlotIndex])
and RefPackageItemCodeName in (SELECT TOP (1) RefPackageItemCodeName FROM [dbo].[_RefShopGoods] where [Order]=0 ORDER BY [RefTabCodeName], [SlotIndex])
SELECT @counter= count(*) from _RefShopGoods where [order] =0
if @counter=0
BEGIN
Set @stop=1
END
eND
DECLARE @tabname varchar(129)
DECLARE @ctabname varchar(129)
DECLARE @slot INT
Set @stop=0
Set @slot=0
Set @tabname=''
UPDATE _RefShopGoods set slotindex=0
WHILE @stop=0
BEGIN
SELECT TOP (1) @ctabname= RefTabCodeName FROM [dbo].[_RefShopGoods] where [Order]>0 ORDER BY [Order]
IF (@tabname<>@ctabname)
BEGIN
Set @slot=0
END
set @tabname=@ctabname
UPDATE [dbo].[_RefShopGoods] SET [slotindex]=@slot, [order] = 0 where RefTabCodeName in (SELECT TOP (1) RefTabCodeName FROM [dbo].[_RefShopGoods] where [Order]>0 ORDER BY [Order])
and RefPackageItemCodeName in (SELECT TOP (1) RefPackageItemCodeName FROM [dbo].[_RefShopGoods] where [Order]>0 ORDER BY [Order])
Set @slot=@slot+1
SELECT @counter= count(*) from _RefShopGoods where [order] <>0
if @counter=0
BEGIN
Set @stop=1
END
eND
GO
ALTER TABLE [dbo].[_RefShopGoods] DROP COLUMN [Order]
GO
have fun
-
Re: Shop query
nice query keep it comeing
-
Re: Shop query
very good for beginners Keep Going :)
-
Re: Shop query