Skip to content
logo
platformaerp.com

Grupy asortymentowe

Sprawdzenie duplikatów grup asortymentowcyh

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Znalezienie powtórzonych
select itemid,count(*),min(itemclassid) from wms.itemclass
where maingroup=1
group by mainGroup , itemid
having count(*) >1

-- Pobranie
select itemclassid,* from wms.itemclass where maingroup=1 and  itemid='3019CD14-5C65-4FA4-84FC-0A4356789FF3'

--Usuniecie wybrnaej grupy
delete from wms.itemclass where itemclassid='9A6C976E-B9C8-ED11-9C10-2C768A5328B2'

-- W przypadku metalbud trzeba uruchomic (procedura w bazie smarttech) , poniewaz grupy moga byc dodawane
-- [dbo].[synchronizacja_wersji]

Usunięcie duplikatów grupy glownej, bezpieczne

SQL
1
2
3
4
5
6
7
8
    delete from wms.itemclass where itemclassid in (
    select min(itemclassid) itemclassid
    -- itemid,count(*),
    from wms.itemclass
    where maingroup=1
    group by mainGroup , itemid
    having count(*) >1
)

Zabezpieczenie przed dodaniem duplikatu grupy asortymentowej

SQL
1
2
3
4
5
6
7
8
-- W pprocedurze  [wms].[itemGroup_InsertUpdate]
declare @itemid uniqueidentifier ='A7B7DB55-3249-E411-9407-D89D6719E0C9'
if exists (select itemid,count(*) from wms.itemclass where maingroup=1 and itemid =  @itemid group by itemid having count(*) >1 )
Begin

delete from wms.itemClass  where itemid =@itemid  and itemclassid =
(select top 1 itemclassid from wms.itemclass where itemid =@itemid and maingroup=1)
End

Zmiana grupy asortymentowej

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
begin tran dupa

declare @itemGroupClothes uniqueidentifier = '8367486C-DAEE-48CB-9707-E335871CCA1D'
declare @itemGroupLiquids uniqueidentifier = 'D9BCAA72-EBE5-4BF7-8F05-65D9AE02C4B9'

declare @itemID uniqueidentifier = ''
declare @username varchar(50) = 'demo'

declare @discountGroupID uniqueidentifier = @itemGroupClothes
declare @changeMainGroupID uniqueidentifier = @itemGroupClothes
declare @itemGroup varchar(max) = (select itemGroup from wms.item where itemID = @itemID and itemGroup not like cast(@itemGroupClothes as varchar(max))) + ',' + cast(@itemGroupClothes as varchar(max))
declare @checkItemCodeTemplate nvarchar(50)

select itemCode from wms.item where itemID = @itemID

if @itemGroup is not null begin
    select
        @checkItemCodeTemplate =
                                 (
                                    SELECT TOP 1 dict.dictionaryUrl
                                    from dbo.f_TOOLS_Split(@itemGroup,',') gr
                                    inner join common.dictionary dict on gr.idP = dict.dictionaryKeyGuid
                                    where @changeMainGroupID is null or @changeMainGroupID = gr.idP
                                 )

    update [wms].[item]
    set
        [itemGroup]             = CASE
                                        when @checkItemCodeTemplate is not null then @itemGroup else [itemGroup]
                                  end,
        [modDate]               = getutcdate(),
        [modUsername]           = @username,
        fulltextcat             = 0 /*reset indexu*/
    where
        [itemID] = @itemID

    IF @itemGroup is not null and @itemGroup<>''
        begin
            EXEC wms.itemGroup_InsertUpdate @itemID,@username,@discountGroupID,@changeMainGroupID
        end

end

select itemCode from wms.item where itemID = @itemID

rollback tran