/* Dodanie/Zmiana grupy rabatowej bez zmieniania grupy glownej */
declare @itemid uniqueidentifier= 'd17e8a46-afe5-43ac-a5b4-fe02516165bf',
@newDiscountGroupID uniqueidentifier = '3BD7FB5A-0E6C-461C-855B-3C1C479228B3'
/* 1. update wms.item doklejamy do itemgroup uuid nowej grupy */
--Tworzenie zapytań
/*
select 'update wms.item set itemGroup =''' +itemGroup+',3BD7FB5A-0E6C-461C-855B-3C1C479228B3'' '+ 'where itemid = '''+cast(itemid as varchar(36)) +''''
from wms.item
where
itemGroup not like '%3BD7FB5A-0E6C-461C-855B-3C1C479228B3%'
and
itemid ='d17e8a46-afe5-43ac-a5b4-fe02516165bf'
*/
begin tran rg
--UPDATE
update wms.item set itemGroup ='e723623a-9cf6-4f72-9c1d-ea9914934503,3BD7FB5A-0E6C-461C-855B-3C1C479228B3' where itemid = 'D17E8A46-AFE5-43AC-A5B4-FE02516165BF'
/* 2. updatujemy wms.itemClass ustawiajac dla interesujacych nas itemid, wartość discountGroup = 0 */
update wms.itemClass set discountGroup = 0 where itemid = @itemid
/* 3. dodajemy do wms.itemClass nową grupę dla itemid */
--Sprawdzamy item category i subcategory
declare @itemcategory uniqueidentifier, @itemsubcategory uniqueidentifier;
SELECT
@itemcategory=d3.dictionaryKey,
@itemsubcategory=d2.dictionaryKey
from common.dictionary d1
LEFT JOIN common.dictionary d2 ON d2.dictionaryKey = d1.dictionaryMasterKey and d2.dictionaryName='itemsubcategory'
LEFT JOIN common.dictionary d3 ON d3.dictionaryKey = d2.dictionaryMasterKey and d3.dictionaryName='itemcategory'
where d1.dictionaryKeyGuid = @newDiscountGroupID
and d1.dictionaryName='itemgroup'
-- tworzenie zapytan
select 'insert into wms.itemClass ( [itemID], [itemCategoryID], [itemSubCategoryID], [itemGroupID], [addUsername], [discountGroup], [mainGroup])
values (''' + cast(itemid as varchar(36))+''','''+cast(@itemcategory as varchar(36))+''','''+cast(@itemsubcategory as varchar(36))+''','''+cast(@newDiscountGroupID as varchar(36))+''',''sys'',0,0)'
from wms.item
where
itemGroup not like '%'+cast(@newDiscountGroupID as varchar(36))+'%'
and
itemid = @itemid
--insert
insert into wms.itemClass ( [itemID], [itemCategoryID], [itemSubCategoryID], [itemGroupID], [addUsername], [discountGroup], [mainGroup])
values (@itemid, @itemcategory,@itemsubcategory,@newDiscountGroupID,'sys',0,0)
/* 3a. ustawiamy discountGroup = 1 ( oddzielny krok z racji że cześc podanych indeksów ma juz ta grupę) */
update wms.itemClass set discountGroup = 1 where itemGroupID=@newDiscountGroupID and itemid = @itemid
commit tran rg
