/*Usunięcie dokuemntów magazynowych*/
truncate table document.document
truncate table document.documentline
/*Zamówien*/
truncate table document.[order]
truncate table document.[orderline]
/*Usuniecie relacji*/
truncate table common.relation
/*Usuniecie*/
update product.productSpecification set itemID = null
truncate table wms.stockBalance
truncate table wms.stockBalanceDate
truncate table item.pricing
truncate table [item].[discount]
delete from wms.item where
itemid not in (
select itemid from document.[orderline] union all
select itemid from document.documentline union all
select itemid from document.invoiceline union all
select itemid from wms.commisionLine where itemid is not null )
truncate table [interface].[importInventor]
truncate table [wms].[itemClass]
truncate table [wms].[itemReplacement]
truncate table [wms].[itemUnitConvert]
truncate table [wms].[stockMinimum]
truncate table [wms].[structureLine]
delete from [wms].[structure]
truncate table [wms].[structureUsage]
truncate table [wms].[technology]
truncate table [wms].[technologyLine]
truncate table [wms].[technologyMade]
truncate table wms.itemsupplier
/*Usuni�cie zlece�*/
truncate table wms.commisionServiceProtocole
delete from wms.commision
truncate table [wms].[commision_docrelrecalc_log]
truncate table [wms].[commisionLine]
truncate table [wms].[commisionServiceProtocole]
truncate table [common].[timesheet]
truncate table [MRP].[requisition]
truncate table wms.[plan]
/*Usuni�cie user�w opr�cz naszych (do customizacji)*/
--TODO
delete from [user].[user]
/*Usuniecie powiazan miedzy uprawnieniami a grupami, ktorych juz nie ma*/
delete from rp
from [user].[rolePermission] rp left join [user].[group] g on rp.groupID = g.groupID where g.groupID is null
/*Usuniecie powiazan miedzy uzytkownikami a grupami, ktorych juz nie ma*/
delete from ug
from [user].[usergroup] ug left join [user].[group] g on ug.groupID = g.groupID where g.groupID is null
/*Usuni�cie powi�za� mi�dzy grupami a u�ytkownikami, kt�rych ju� nie ma*/
delete from ug
from [user].[userGroup] ug left join [user].[user] u on ug.userID = u.userID
where u.userID is null
/*Wyczyszczenie jednostek organizacyjnych do nieistniejacych grup*/
update u set organizationalGroupID = null
from [user].[user] u left join [user].[group] g on u.organizationalGroupID = g.groupID where g.groupID is null
/*To samo z grupami domyslnymi*/
update u set defaultGroupID = null
from [user].[user] u left join [user].[group] g on u.defaultGroupID = g.groupID where g.groupID is null and u.defaultGroupID is not null
/*Usuniecie faktur*/
truncate table document.documentRelation
delete from document.invoice
truncate table document.invoiceLine
truncate table document.invoiceVatTable
truncate table common.paymentSchedule
truncate table [common].[teamcost]
truncate table [document].[Agreement]
/*Usuni�cie dokument�w magazynowych*/
truncate table [document].[cooperationRelation]
truncate table [document].[document]
truncate table [document].[documentLine]
truncate table [document].[toolRegistry]
truncate table [interface].[import]
/*Usuni�cie inwentaryzacji*/
truncate table [item].[inventory]
truncate table [item].[inventorySheet]
delete from [item].[inventorySheetLine]
/*Usuni�cie maili*/
truncate table [mail].[mail]
truncate table [mail].[mailNotification]
truncate table [mail].[mailRelation]
truncate table [mail].[mailTemplate]
/*Usuni�cie zdarze�*/
truncate table [event].ievent
truncate table [event].[iAttendee]
truncate table [event].[icalendar]
/*Usuniecie zada�*/
truncate table [event].task
/*Usuni�cie szans sprzeda�y*/
truncate table [event].[opportunity]
/*Jakie� dyskusje*/
truncate table [event].[discussion]
/*Usuniecie zam�wie�*/
delete from document.[order]
truncate table document.[orderLine]
/*Usuni�cie plik�w*/
truncate table common.filerepository
truncate table common.filecad
/*Usuni�cie urz�dze�*/
truncate table product.productSpecification
truncate table product.productRelation
delete from product.product
truncate table [product].[motohourssheet]
/*Usuni�cie projekt�w*/
truncate table [project].[project]
/*Usuni�cie historii wyszukiwania*/
truncate table [search].[searchHistory]
/*Usuni�cie komentarzy*/
truncate table common.comment
truncate table common.memo
truncate table common.news
truncate table [common].[tag]
/*Usuni�cie wpis�w aktywno�ci, wiadomo�ci*/
truncate table log.wall
truncate table common.history
truncate table [common].[stats]
truncate table [dbo].[msg]
truncate table [dbo].[synchronizationLog]
/*Usuni�cie wpis�w czasu pracy*/
truncate table common.timesheet
/*Usuni�cie magazyn�w i lokacji*/
delete from wms.location
truncate table [wms].[locationTask]
truncate table [wms].[locationTaskLaser]
/*Usuni�cie numerator�w*/
truncate table common.Numerator
/*Usuni�cie ofert*/
truncate table document.offer
truncate table document.offerline
/*Usuni�cie scren�w niepowi�zanych z aplikacjami jakie zosta�y pozostawione*/
delete from scr
from configuration.screen scr left join [user].[group] gr on gr.groupID = scr.groupID where gr.groupID is null
/*Usuni�cie relacji z u�ytkownikami, kt�rych ju� nie ma i z obiektami, kt�rych ju� nie ma*/
delete from ur
from [user].[userRelation] ur left join [user].[user] u on ur.userID = u.userID where u.userID is null
delete from [user].[userRelation] where remoteSource in ('product', 'commisionservice', 'ievent', 'mailnotification')
/*Usuni�cie konfiguracji kolumn cgs z u�ytkownikami, kt�rych ju� nie ma w bazie*/
delete from cc
from [user].[columnConfiguration] cc
left join [user].[user] u on u.username = cc.username
where u.userID is null
/*Usuni�cie grup relation */
delete from [user].[groupRelation] where remoteSource = 'news'
/*Jakie� wakacje*/
truncate table [user].[userHoliday]
/*Historia logowania przed wyznaczona dat�*/
delete from [user].[userLoginHistory] where addDate < '2016-03-21'
/*Usuni�cie r�cznie wprowadzonych danych ksi�gowania i konfiguracji ksi�gowej*/
truncate table accounting.customeAccounting
truncate table [accounting].[salesConfiguration]
truncate table [accounting].[warehouseConfiguration]
/*Usuni�cie atrybut�w*/
truncate table common.attribute
truncate table common.attributeGroupComponentRelation
truncate table common.attributeGroupRelation
truncate table common.attributeValue
/*Usuni�cie kategorii*/
truncate table common.category
/*Usuni�cie danych spersonalizowanych cgs*/
truncate table common.cgsSheduler
truncate table common.cgsUserConfiguration
/*Usuni�cie danych schowka*/
truncate table common.clipboard
/*Usuni�cie wprowadzonych danych cms*/
truncate table common.content
/*Usuni�cie danych dashboard*/
truncate table common.dashboard
/*Usuni�cie niestandardowych s�ownik�w lub ich warto�ci*/
delete from common.dictionary where [status] = -1
delete from common.dictionary where dictionaryName in ('aatest', N'Agregat chlodniczy', 'Agregat WL', 'area', 'artForm', 'artMedium', 'artworkSurface'
, 'attributegroup', 'attributeRelationtype', 'bankAccount', 'departments', 'Dozownik', 'Filtr obrotowy FBN', 'groupcategory', 'ieventtags', 'Igly'
, 'itemcategory', 'itemCodeTemplate', 'itemCodeTemplate', 'itemgroup', 'itemSpecAdditionalHead', 'itemSpecCarrier', 'itemSpecContainer', 'itemSpecDriver'
, 'itemSpecDTRKAT', 'itemSpecLogo', 'itemSpecMechLoad', 'itemSpecNeedlesType', 'itemSpecPackage', 'itemSpecPomp', 'itemSpecPurpose', 'itemSpecRotaryFilter'
, 'itemSpecVoltage', 'itemsubcategory', 'KlasyfikacjaDostawcy', 'Language_jezyk', 'leaduser', 'Logo', 'mastercategory', 'memo_document', 'memo_invoice'
, 'memo_offer', 'memo_order', 'MPK', 'Noze', 'offerrelationtype', 'organisationUnit', 'Otw�r', 'packtype', 'Panel MA', 'Parownik', 'Pompa MH'
, 'POMPA ZMS', 'Pompa-myjki','productFaultType', 'Profil produkcji', 'projectjira', 'Przenosnik', 'servicetype', 'servicetypes', 'SK'
, 'Slimak', 'Sterowanie', 'storeArea', 'subcategory', 'system', 'System gotowania', 'taskarea', 'team', 'Temperatury', 'test', 'Wielkosc produkcji'
, 'Zabrudzenia', 'Zaladunek', 'Zaladunek MH'
)
delete from common.dictionary where dictionaryValue = 'intermik'
update common.dictionary set dictionaryValue = null where dictionaryName in ('system.sale', 'system.service')
/*custom*/
truncate table custom.magazyn
truncate table custom.zakup
truncate table custom.sprzedaz
drop table custom.MPKfromFK
/*Usuni�cie zb�dnych wpis�w konfiguracyjnych*/
--wms.planlist.filters dla nieistniej�cych u�ytkownik�w
delete from cc
from [configuration].[configuration] cc
left join [user].[user] u on u.userName = substring(configurationID, 22, 100)
where configurationID like 'wms.planlist.filters%' and u.username is null
--accounting.list.filters dla nieistniej�cych u�ytk.
delete from cc
from [configuration].[configuration] cc
left join [user].[user] u on u.userName = substring(configurationID, 25, 100)
where configurationID like 'accounting.list.filters%' and u.username is null
--filenavigator
delete from [configuration].[configuration] where configurationID like 'instance.filenavigator.%'
--domyslna grupa na razie common
update configuration.configuration set value = 'D58DE8A6-4173-E311-B9E9-001A64689F03' where configurationID like 'instance.defaultGroup.%'
--reset ustawie� cgs
delete from configuration.configuration where configurationID like 'cgs.%'
delete from configuration.configuration where [description] = 'domyslna wartosc cgs parametru per uzytkownik'
delete from configuration.configuration where configurationID like '%chattargetetuser'
delete from c
from configuration.configuration c
left join [user].[user] u on u.username = SUBSTRING(configurationID, CHARINDEX('_columns_', configurationID) + 9, LEN(configurationID))
where configurationID like '%_columns_%' and CHARINDEX('_columns_', configurationID) != 0
and u.username is null
delete from c
from configuration.configuration c
left join [user].[user] u on u.username = SUBSTRING(configurationID, CHARINDEX('itemPrompt_', configurationID) + 11, LEN(configurationID))
where configurationID like 'itemPrompt_%' and CHARINDEX('itemPrompt_', configurationID) != 0
and u.username is null
delete from configuration.configuration where configurationID like 'PRINTOUT.%'
declare @pattern varchar(max) = 'mrp.requisition.filters.'
delete from c
from configuration.configuration c
left join [user].[user] u on u.username = SUBSTRING(configurationID, CHARINDEX(@pattern, configurationID) + len(@pattern), LEN(configurationID))
where configurationID like @pattern + '%' and CHARINDEX(@pattern, configurationID) != 0
and u.username is null
set @pattern = 'technology.dashboard.filters.'
delete from c
from configuration.configuration c
left join [user].[user] u on u.username = SUBSTRING(configurationID, CHARINDEX(@pattern, configurationID) + len(@pattern), LEN(configurationID))
where configurationID like @pattern + '%' and CHARINDEX(@pattern, configurationID) != 0
and u.username is null
set @pattern = 'user.'
delete from c
from configuration.configuration c
left join [user].[user] u on u.username = SUBSTRING(configurationID, CHARINDEX(@pattern, configurationID) + len(@pattern) + 1, CHARINDEX(']', configurationID, CHARINDEX(@pattern, configurationID) + len(@pattern) + 3) - len(@pattern) - 2)
where configurationID like @pattern + '%' and CHARINDEX(@pattern, configurationID) != 0
and u.username is null
delete from configuration.configuration where configurationID like 'przemyslaw.kochanek%'
delete from configuration.configuration where configurationID like '%_przemyslaw.kochanek'
--TODO na razie pozostawiam te wpisy poza current i cgs ale
--[configuration].[configuration]
/*select * from configuration.configuration where configurationID not like 'instance.%' and configurationID not like 'current.%'
and configurationID not like '%adam.cieslakowski%'
and configurationID not like '%marcin.kotynia%'
and configurationID not like '%agnieszka.bartoszek%'*/
--[document].[documentType]
delete from document.documentType where componentName = 'documentHybrid'
delete from document.documentType where documentType = 'CAR'
delete from document.documentType where componentName = 'brak'
delete from document.documentType where documentType in ('WNS-', 'KOWNS-')
delete from document.documentType where documentType in ('WNS-', 'KOWNS-', 'IR')
update document.documentType set documentType = 'ZI-', correctiveDocumentType = 'KO_ZI-' where documentType = 'PI-'
update document.documentType set documentType = 'ZS-', correctiveDocumentType = 'KO_ZS-' where documentType = 'PS-'
update document.documentType set documentType = 'KO_ZI-' where documentType = 'KO_PI-'
update document.documentType set documentType = 'KO_ZS-' where documentType = 'KO_PS-'
update document.documentType set [description] = 'Faktura Proforma' where documentType = 'FP'
