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

nice query keep it comeing

very good for beginners Keep Going :)

Nice query
:D