
/*Skrypt scala firmy. 
W @firmToDelete nalezy uzupelnic ID firmy, ktora ma byc usunieta, a w @firmForReplace ID firmy, ktora ma ja zastapic.
Status pierwszej z firm zostanie ustawiony na -1, podobnie jak status jej adresu.*/

BEGIN TRAN
DECLARE @firmToDelete uniqueidentifier,		--Te firme kasujemy
		@addressToDelete uniqueidentifier,	
		@firmForReplace uniqueidentifier,	--Ta firma ma zostac
		@addressForReplace uniqueidentifier
SET @firmToDelete = ''
SET @firmForReplace = ''
SET @addressToDelete = (SELECT addressID FROM [common].[address] WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm')
SET @addressForReplace = (SELECT addressID FROM [common].[address] WHERE [remoteID] = @firmForReplace AND [remoteSource] = 'firm')

UPDATE [common].[relation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete

UPDATE [common].[history] SET [parentRemoteID] = @firmForReplace WHERE ([parentRemoteSource] = 'firm' AND [parentRemoteID] = @firmToDelete) --AND [status] > -1
UPDATE [common].[history] SET [navigateRemoteID] = @firmForReplace WHERE ([navigateRemoteSource] = 'firm' AND [navigateRemoteID] = @firmToDelete) --AND [status] > -1
UPDATE [common].[history] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm') --AND [status] > -1
UPDATE [document].[invoice] SET [recipientID] = @firmForReplace WHERE [recipientID] = @firmToDelete --AND [status] > -1	
UPDATE [document].[invoice] SET [payerID] = @firmForReplace WHERE [payerID] = @firmToDelete --AND [status] > -1	
UPDATE [document].[invoice] SET [issuerID] = @firmForReplace WHERE [issuerID] = @firmToDelete --AND [status] > -1	
UPDATE [document].[invoice] SET [remoteID] = @firmForReplace WHERE ([remoteSource] = 'firm' AND [remoteID] = @firmToDelete) --AND [status] > -1	
UPDATE [document].[order] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete
UPDATE [document].[order] SET [deliveryFirmID] = @firmForReplace WHERE [deliveryFirmID]	 = @firmToDelete
UPDATE [log].[wall] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm')
UPDATE [log].[wall] SET [parentRemoteID] = @firmForReplace WHERE ([parentRemoteSource] = 'firm' AND [parentRemoteID] = @firmToDelete)
UPDATE [product].[product] SET [ownerFirmID] = @firmForReplace WHERE [ownerFirmID] = @firmToDelete --AND [status] > -1
UPDATE [product].[product] SET [dealerFirmID] = @firmForReplace WHERE [dealerFirmID] = @firmToDelete --AND [status] > -1
UPDATE [wms].[commision] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [wms].[commision] SET [addressID] = @addressForReplace WHERE [addressID] = @addressToDelete	 --AND [status] > -1

UPDATE [document].[Agreement] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [document].[document] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [document].[toolRegistry] SET[firmID]  = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [firm].[contact] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [wms].[itemSupplier] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1

UPDATE [common].[paymentSchedule] SET [payerID] = @firmForReplace WHERE [payerID] = @firmToDelete --AND [status] > -1
UPDATE [common].[paymentSchedule] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm') --AND [status] > -1

UPDATE [document].[offer] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [document].[offer] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm') --AND [status] > -1
UPDATE [event].[opportunity] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [event].[opportunity] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm') --AND [status] > -1
UPDATE [firm].[campaignLine] SET [firmID] = @firmForReplace WHERE [firmID] = @firmToDelete --AND [status] > -1
UPDATE [firm].[campaignLine] SET [remoteID] = @firmForReplace WHERE ([remoteID] = @firmToDelete AND [remoteSource] = 'firm') --AND [status] > -1

UPDATE [dbo].[synchronizationLog] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete

UPDATE [accounting].[customeAccounting] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'
UPDATE [common].[clipboard] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'
UPDATE [common].[stats] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'
UPDATE [common].[tag] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'
UPDATE [product].[productRelation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'

UPDATE [common].[Memo] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[bankAccount] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[attributeGroupComponentRelation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[classification] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[comment] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[fileCad] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[fileRepository] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[teamcost] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [common].[timesheet] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [event].[iAttendee] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [event].[icalendar] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [event].[ievent] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [event].[task] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [mail].[mail] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [mail].[mailNotification] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [mail].[mailRelation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [project].[project] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [user].[groupRelation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [user].[userRelation] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [wms].[item] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [wms].[location] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [wms].[structure] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [wms].[technologyLine] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1
UPDATE [wms].[technologyMade] SET [remoteID] = @firmForReplace WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm' --AND [status] > -1

UPDATE [firm].[firm] SET [status] = -1 WHERE [firmID] = @firmToDelete
UPDATE [common].[address] SET [status] = -1 WHERE [remoteID] = @firmToDelete AND [remoteSource] = 'firm'
COMMIT


/*
declare @oldFirmID varchar(36)= '4c32696f-6f44-4b60-a1c8-8ce2f7347041'
declare @newFirmID varchar(36)= '20987669-a6b6-e411-93f3-f01fafe8ab47'

update firm.firm set status = -1 where firmid =@oldFirmID
update common.comment set remoteid = @newFirmID where remoteid = @oldFirmID
update common.filerepository set remoteid = @newFirmID where remoteid = @oldFirmID
update [document].[invoice] set [recipientID] = @newFirmID where [recipientID] = @oldFirmID
update [document].[invoice] set [payerID] = @newFirmID where [payerID] = @oldFirmID




SELECT 

'update [' +  table_schema +  '].[' +table_name+'] set firmid = ''' +@newFirmID+ ''' where firmid= ''' + @oldFirmID + ''' '
 
      FROM      
          sys.columns c
          LEFT OUTER JOIN
          sys.extended_properties ex
          ON
          ex.major_id = c.object_id
          AND ex.minor_id = c.column_id
          AND ex.name = 'MS_Description'
          Left JOIN
          INFORMATION_SCHEMA.COLUMNS isc
          ON
          isc.TABLE_CATALOG = db_name()
          AND isc.TABLE_NAME = OBJECT_NAME(c.object_id)
          AND c.name = isc.column_name
WHERE    
          OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
		  and c.name ='firmid'
		  and column_default is null
          ORDER
          BY c.column_id
*/