Shop query

[nikkyandrei]

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

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

[MentaL]

[lemoniscool]

nice query keep it comeing

[LeMbo]

very good for beginners Keep Going :)

[xxNukertube]

Nice query
:D