USE [CRM0100_SMARTTECH]
GO

/****** Object:  StoredProcedure [dbo].[synchronizacja_wersji]    Script Date: 17/08/2018 16:23:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[synchronizacja_wersji]
AS
BEGIN

	SET NOCOUNT ON;

/*
use [CRM0110_SMARTTECH]



delete from [user].[userLoginHistory]
delete from [MRP].[requisition]
delete from [document].[cooperationRelation]
delete from [wms].[stockBalance]
delete from [wms].[commision]
delete from [wms].[plan]
delete from [common].[clipboard]
delete from [log].[wallGroup]
delete from [document].[order]
delete from [common].[Numerator]
delete from [wms].[commisionLine]
delete from [document].[offer]
delete from [event].[ievent]
delete from [search].[searchHistory]
delete from [item].[discount]
delete from [common].[timesheet]
delete from [accounting].[customeAccounting]
delete from [document].[offerLine]
delete from [document].[invoice]
delete from [dbo].[msg]
delete from [common].[attributeGroupComponentRelation]
delete from [common].[attributeValue]
delete from [document].[orderLine]
delete from [wms].[commisionServiceProtocole]
delete from [document].[documentLine]
delete from [document].[document]
delete from [document].[Agreement]
delete from [wms].[technologyLine]
delete from [wms].[structureUsage]
delete from [common].[comment]
delete from [document].[documentRelation]
delete from [common].[paymentSchedule]
delete from [product].[productSpecification]
delete from [common].[attribute]
delete from [wms].[stockBalanceDate]
delete from [firm].[firmRelation]


do syncrhonizacji
wms.item
wms.itemclass
wms.itemreplacement
wms.itemunitconvert

wms.structure
wms.structureline
wms.technology
wms.technologyline

*/

 /*tabela do przechowywania co było zmienione nie usuwać nie komentować 
   używana przy przeindeksowaniu indeksów materiałowych po zmianie na grupach asortymentowych
 */
CREATE TABLE #report
    (
        [action] varchar(50),
        [target] varchar(150),
        [source] varchar(150),
        [remoteid] uniqueidentifier
    );
 
/**********************************************************************************************************************************************/
/***            wms.item                ****/
/***            wms.item                ****/
/***            wms.item                ****/
	SET IDENTITY_INSERT [CRM0100_SMARTTECH].[wms].[item] ON

	MERGE INTO [CRM0100_SMARTTECH].[wms].[item]  AS target
	USING [CRM_METALBUD_2016].[wms].[item]  AS source
		ON target.itemid = source.itemid
	WHEN MATCHED AND target.moddate <> source.moddate THEN 
		UPDATE SET 
 --                   target.itemCode = source.itemCode,
                    target.itemName = source.itemName,
                    target.itemNameShort = source.itemNameShort,
                    target.itemNameCommercial = source.itemNameCommercial,
                    target.itemNameCommercialForeign = source.itemNameCommercialForeign,
                    target.itemMemo = source.itemMemo,
                    target.itemLastPrice = source.itemLastPrice,
                    target.itemMaxPrice = source.itemMaxPrice,
                    target.itemMaterial = source.itemMaterial,
                    --target.itemStockQuantity = source.itemStockQuantity,
                    target.itemGroup = source.itemGroup,
                    target.itemUnitID = source.itemUnitID,
                    target.itemUnitProductionID = source.itemUnitProductionID,
                    target.itemUnitOrderID = source.itemUnitOrderID,
                    --target.itemOrderQuantity = source.itemOrderQuantity,
                    target.itemWeight = source.itemWeight,
                    target.dimensionX = source.dimensionX,
                    target.dimensionY = source.dimensionY,
                    target.dimensionZ = source.dimensionZ,
                    target.itemTaxRate = source.itemTaxRate,
                    target.itemTaxRateForeign = source.itemTaxRateForeign,
                    target.isStorable = source.isStorable,
                    target.isDivision = source.isDivision,
                    target.isSeries = source.isSeries,
                    target.isQualityControlReq = source.isQualityControlReq,
                    target.isServiceComponent = source.isServiceComponent,
                    target.isServicePRoduct = source.isServicePRoduct,
                    target.isImportedFromCad = source.isImportedFromCad,
                    target.remoteSource = source.remoteSource,
                    target.remoteID = source.remoteID,
                    target.groupID = source.groupID,
                    target.modDate = source.modDate,
                    target.modUsername = source.modUsername,
                    target.fullTextKeyword = source.fullTextKeyword,
                    target.fullTextCat = 0,--source.fullTextCat,
--                    target.fullTextID = source.fullTextID,
                    target.status = source.status,
                    target.itemCodeTemplate = source.itemCodeTemplate,
                    target.itemCatalogPrice = source.itemCatalogPrice,
                    target.itemLotSize = source.itemLotSize,
                    target.trashItemID = source.trashItemID,
                    target.itemLastAdditionalPrice = source.itemLastAdditionalPrice,
                    target.rotation = source.rotation,
                    target.isStored = source.isStored
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (itemID, itemCode, itemName, itemNameShort, itemNameCommercial, itemNameCommercialForeign, itemMemo, itemLastPrice, itemMaxPrice, itemMaterial/*, itemStockQuantity*/, itemGroup, 
        itemUnitID, itemUnitProductionID, itemUnitOrderID/*, itemOrderQuantity*/, itemWeight, dimensionX, dimensionY, dimensionZ, itemTaxRate, itemTaxRateForeign, isStorable, isDivision, isSeries, 
        isQualityControlReq, isServiceComponent, isServicePRoduct, isImportedFromCad, remoteSource, remoteID, addDate, addUsername, groupID, modDate, modUsername, fullTextKeyword, fullTextCat, 
        fullTextID, status, itemCodeTemplate, itemCatalogPrice, itemLotSize, trashItemID, itemLastAdditionalPrice, rotation, externalItemID, isStored)
		VALUES (source.itemID, 'S01'+source.itemCode, source.itemName, source.itemNameShort, source.itemNameCommercial, source.itemNameCommercialForeign, source.itemMemo, source.itemLastPrice, 
        source.itemMaxPrice, source.itemMaterial/*, source.itemStockQuantity*/, source.itemGroup, source.itemUnitID, source.itemUnitProductionID, source.itemUnitOrderID/*, source.itemOrderQuantity*/, 
        source.itemWeight, source.dimensionX, 
		source.dimensionY, source.dimensionZ, source.itemTaxRate, source.itemTaxRateForeign, source.isStorable, source.isDivision, source.isSeries, source.isQualityControlReq, 
        source.isServiceComponent, source.isServicePRoduct, source.isImportedFromCad, source.remoteSource, source.remoteID, source.addDate, source.addUsername, source.groupID, 
		source.modDate, source.modUsername, source.fullTextKeyword, 0 /*fulltextcat*/, source.fullTextID, source.status, source.itemCodeTemplate, source.itemCatalogPrice, source.itemLotSize, 
        source.trashItemID, source.itemLastAdditionalPrice, source.rotation, source.externalItemID, source.isStored)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[item]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[item]'  'SOURCE', 
              source.itemID into #report
    ;
	SET IDENTITY_INSERT [CRM0100_SMARTTECH].[wms].[item] OFF;

/**********************************************************************************************************************************************/
/***            wms.itemclass                ****/
/***            wms.itemclass                ****/
/***            wms.itemclass                ****/
/*TYLKO NOWE GRUPY NOWYCH INDEKSÓW*/
    INSERT INTO [CRM0100_SMARTTECH].[wms].[itemclass]
    SELECT X.* FROM [CRM_METALBUD_2016].[wms].[itemclass] X
    INNER JOIN #report R ON R.remoteid =  X.itemID
    WHERE
        R.[action] = 'INSERT'
    AND R.[target] = '[CRM0100_SMARTTECH].[wms].[item]'
    AND R.[source] = '[CRM_METALBUD_2016].[wms].[item]'     
	--MERGE INTO [CRM0100_SMARTTECH].[wms].[itemclass]  AS target
	--USING [CRM_METALBUD_2016].[wms].[itemclass]  AS source
	--	ON target.itemClassID = source.itemClassID
	--WHEN MATCHED AND target.adddate <> source.adddate THEN 
	--	UPDATE SET 
 --                   target.itemID = source.itemID,
 --                   target.itemCategoryID = source.itemCategoryID,
 --                   target.itemSubCategoryID = source.itemSubCategoryID,
 --                   target.itemGroupID = source.itemGroupID,
 --                   target.addusername = source.addusername,
 --                   target.adddate = source.adddate,
 --                   target.discountGroup = source.discountGroup,
 --                   target.mainGroup = source.mainGroup                    
	--WHEN NOT MATCHED BY TARGET THEN
	--	INSERT ([itemClassID],[itemID],[itemCategoryID],[itemSubCategoryID],[itemGroupID],[addusername],
 --               [adddate],[discountGroup],[mainGroup])
	--	VALUES (source.itemClassID, source.itemID, source.itemCategoryID, source.itemSubCategoryID, source.itemGroupID, source.addusername, 
 --               source.adddate, source.discountGroup, source.mainGroup)   
 --      OUTPUT $action ,
 --            '[CRM0100_SMARTTECH].[wms].[itemclass]' 'TARGET', 
 --            '[CRM_METALBUD_2016].[wms].[itemclass]'  'SOURCE', 
 --             source.itemClassID into #report
 --   ;

    /***ważne zmiany w itemclass wymagaja restu na ideksie indeksu ****/

        --update wms.item SET 
        --                    fullTextCat = 0
        --from   wms.item i 
        --inner  join 
        --            (
        --                SELECT distinct itemid
        --                from wms.itemClass ic
        --                inner join #report r ON r.remoteid = ic.itemClassID
        --                where r.[target]='[CRM0100_SMARTTECH].[wms].[itemclass]'
        --            )x on x.itemID=i.itemID

/**********************************************************************************************************************************************/
/***            wms.itemreplacement                ****/
/***            wms.itemreplacement                ****/
/***            wms.itemreplacement                ****/

	MERGE INTO [CRM0100_SMARTTECH].[wms].[itemreplacement]  AS target
	USING [CRM_METALBUD_2016].[wms].[itemreplacement]  AS source
		ON target.itemReplacement = source.itemReplacement
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET 
                    target.itemID = source.itemID,
                    target.replacementItemID = source.replacementItemID,
                    target.replacementQuantity = source.replacementQuantity,
                    target.isActive = source.isActive,
                    target.status = source.status,
                    target.modDate = source.modDate,
                    target.modUsername = source.modUsername                 
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ([itemReplacement],[itemID],[replacementItemID],[replacementQuantity],[isActive],
                [status],[addDate],[addUsername],[modDate],[modUsername])
		VALUES (source.itemReplacement, source.itemID, source.replacementItemID, source.replacementQuantity, 
                source.isActive, source.status, source.addDate, source.addUsername,source.modDate, source.modUsername)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[itemreplacement]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[itemreplacement]'  'SOURCE', 
              source.itemReplacement into #report
    ;

/**********************************************************************************************************************************************/
/***            wms.itemreplacement                ****/
/***            wms.itemreplacement                ****/
/***            wms.itemreplacement                ****/

	MERGE INTO [CRM0100_SMARTTECH].[wms].[itemunitconvert]  AS target
	USING [CRM_METALBUD_2016].[wms].[itemunitconvert]  AS source
		ON target.itemUnitConvertID = source.itemUnitConvertID
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET 
                target.itemID = source.itemID,
                target.itemUnitID = source.itemUnitID,
                target.unitConvertID = source.unitConvertID,
                target.convertQuantity = source.convertQuantity,
                target.status = source.status,
                target.modDate = source.modDate,
                target.modUsername = source.modUsername                
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ([itemUnitConvertID],[itemID],[itemUnitID],[unitConvertID],[convertQuantity],
                [status],[addDate],[addUsername],[modDate],[modUsername])
		VALUES (source.itemUnitConvertID, source.itemID, source.itemUnitID, source.unitConvertID, 
                source.convertQuantity, source.status, source.addDate, source.addUsername,source.modDate, source.modUsername)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[itemunitconvert]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[itemunitconvert]'  'SOURCE', 
              source.itemUnitConvertID into  #report
    ;

/**********************************************************************************************************************************************/
/***            common.fileRepository where remoteSource = 'item'             ****/
/***            common.fileRepository where remoteSource = 'item'             ****/
/***            common.fileRepository where remoteSource = 'item'             ****/
--Osobno synchronizacja ścieżek
	MERGE INTO [CRM0100_SMARTTECH].[common].[fileRepository]  AS target
	USING [CRM_METALBUD_2016].[common].[fileRepository]  AS source
		ON target.filerepositoryid = source.filerepositoryid
	WHEN MATCHED AND target.moddate <> source.moddate AND source.remoteSource = 'item' and source.filePath is not null THEN 
		UPDATE SET 
			target.[fileRepositoryID] = source.[fileRepositoryID],
			target.[remoteSource] = source.[remoteSource],
			target.[remoteID] = source.[remoteID],
			target.[parentRemoteSource] = source.[parentRemoteSource],
			target.[parentRemoteID] = source.[parentRemoteID],
			target.[packID] = source.[packID],
			target.[packType] = source.[packType],
			target.[packDescription] = source.[packDescription],
			target.[fileDate] = source.[fileDate],
			target.[fileName] = source.[fileName],
			target.[fileExtension] = source.[fileSize],
			target.[contentType] = source.[contentType],
			target.[groupID] = source.[groupID],
			target.[status] = source.[status],
			target.[addDate] = source.[addDate],
			target.[addUsername] = source.[addUsername],
			target.[modDate] = source.[modDate],
			target.[modUsername] = source.[modUsername],
			target.[fullTextKeyword] = source.[fullTextKeyword],
			target.[fullTextCat] = source.[fullTextCat],
			target.[filePath] = source.[filePath],
			target.[mailAttachment] = source.[mailAttachment],
			target.[fullTextExtracted] = source.[fullTextExtracted],
			target.[packTypeID] = source.[packTypeID]
	WHEN NOT MATCHED BY TARGET AND source.filePath is not null AND source.remoteSource = 'item' THEN
		INSERT ([fileRepositoryID],[remoteSource],[remoteID],[parentRemoteSource],[parentRemoteID]
			,[packID],[packType],[packDescription]
			,[fileDate],[fileName],[fileExtension],[fileSize],[contentType]
			,[groupID],[status],[addDate],[addUsername],[modDate],[modUsername]
			,[fullTextKeyword],[fullTextCat],[filePath]
			,[mailAttachment],[fullTextExtracted],[packTypeID])
		VALUES (source.[fileRepositoryID],source.[remoteSource],source.[remoteID],source.[parentRemoteSource],source.[parentRemoteID]
			,source.[packID],source.[packType],source.[packDescription]
			,source.[fileDate],source.[fileName],source.[fileExtension],source.[fileSize],source.[contentType]
			,source.[groupID],source.[status],source.[addDate],source.[addUsername],source.[modDate],source.[modUsername]
			,source.[fullTextKeyword],0,source.[filePath]
			,source.[mailAttachment],source.[fullTextExtracted],source.[packTypeID])
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[common].[filerepository] where remotesource = ''item'' and filepath is not null' 'TARGET', 
             '[CRM_METALBUD_2016].[common].[filerepository]'  'SOURCE', 
              source.fileRepositoryID into  #report
		;

--Osobno synchronizacja plików z repozytorium platformy (konwertowane na ścieżki aby się ładowały w smarttechu)
	DECLARE @repositoryRoot nvarchar(max) = (select datastore from CRM0000_CONFIG.dbo.tblConfig where domain = 'platformametalbud')

	MERGE INTO [CRM0100_SMARTTECH].[common].[fileRepository]  AS target
	USING [CRM_METALBUD_2016].[common].[fileRepository]  AS source
		ON target.filerepositoryid = source.filerepositoryid
	WHEN MATCHED AND target.moddate <> source.moddate AND source.remoteSource = 'item' and source.filePath is null THEN 
		UPDATE SET 
			target.[fileRepositoryID] = source.[fileRepositoryID],
			target.[remoteSource] = source.[remoteSource],
			target.[remoteID] = source.[remoteID],
			target.[parentRemoteSource] = source.[parentRemoteSource],
			target.[parentRemoteID] = source.[parentRemoteID],
			target.[packID] = source.[packID],
			target.[packType] = source.[packType],
			target.[packDescription] = source.[packDescription],
			target.[fileDate] = source.[fileDate],
			target.[fileName] = source.[fileName],
			target.[fileExtension] = source.[fileSize],
			target.[contentType] = source.[contentType],
			target.[groupID] = source.[groupID],
			target.[status] = source.[status],
			target.[addDate] = source.[addDate],
			target.[addUsername] = source.[addUsername],
			target.[modDate] = source.[modDate],
			target.[modUsername] = source.[modUsername],
			target.[fullTextKeyword] = source.[fullTextKeyword],
			target.[fullTextCat] = source.[fullTextCat],
			target.[filePath] = @repositoryRoot + 'documents\' + cast(source.fileRepositoryID as varchar(36)) + isnull(source.fileExtension, ''),
			target.[mailAttachment] = source.[mailAttachment],
			target.[fullTextExtracted] = source.[fullTextExtracted],
			target.[packTypeID] = source.[packTypeID]
	WHEN NOT MATCHED BY TARGET AND source.filePath is null AND source.remoteSource = 'item' THEN
		INSERT ([fileRepositoryID],[remoteSource],[remoteID],[parentRemoteSource],[parentRemoteID]
			,[packID],[packType],[packDescription]
			,[fileDate],[fileName],[fileExtension],[fileSize],[contentType]
			,[groupID],[status],[addDate],[addUsername],[modDate],[modUsername]
			,[fullTextKeyword],[fullTextCat],[filePath]
			,[mailAttachment],[fullTextExtracted],[packTypeID])
		VALUES (source.[fileRepositoryID],source.[remoteSource],source.[remoteID],source.[parentRemoteSource],source.[parentRemoteID]
			,source.[packID],source.[packType],source.[packDescription]
			,source.[fileDate],source.[fileName],source.[fileExtension],source.[fileSize],source.[contentType]
			,source.[groupID],source.[status],source.[addDate],source.[addUsername],source.[modDate],source.[modUsername]
			,source.[fullTextKeyword],0,@repositoryRoot + 'documents\' + cast(source.fileRepositoryID as varchar(36)) + isnull(source.fileExtension, '')
			,source.[mailAttachment],source.[fullTextExtracted],source.[packTypeID])
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[common].[filerepository] where remotesource = ''item'' and filepath is null' 'TARGET', 
             '[CRM_METALBUD_2016].[common].[filerepository]'  'SOURCE', 
              source.fileRepositoryID into  #report
	   ;



/**********************************************************************************************************************************************/
/***            wms.structure                ****/
/***            wms.structure                ****/
/***            wms.structure                ****/

	MERGE INTO [CRM0100_SMARTTECH].[wms].[structure]  AS target
	USING [CRM_METALBUD_2016].[wms].[structure]  AS source
		ON target.structureID = source.structureID
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET 
                    target.remoteSource = source.remoteSource,
                    target.remoteID = source.remoteID,
                    target.structureName = source.structureName,
                    target.structureOrder = source.structureOrder,
                    target.modDate = source.modDate,
                    target.modUsername = source.modUsername,
                    target.status = source.status
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ( [structureID],[remoteSource],[remoteID],[structureName],[structureOrder],
                 [addDate],[addUsername],[modDate],[modUsername],[status],[externalStructureID])
		VALUES (source.structureID, source.remoteSource, source.remoteID, source.structureName, 
                source.structureOrder, source.addDate, source.addUsername,source.modDate, source.modUsername ,
                source.status, source.externalStructureID)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[structure]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[structure]'  'SOURCE', 
              source.structureID into  #report
    ;

/**********************************************************************************************************************************************/
/***            wms.structureline                ****/
/***            wms.structureline                ****/
/***            wms.structureline                ****/

    

	MERGE INTO [CRM0100_SMARTTECH].[wms].[structureline]  AS target
	USING [CRM_METALBUD_2016].[wms].[structureline]  AS source
		ON target.structureLineID = source.structureLineID
	WHEN MATCHED AND target.modDate <> source.modDate OR target.structureLineOrder <> source.structureLineOrder THEN 
		UPDATE SET 
                    target.structureID = source.structureID,
                    target.parentItemID = source.parentItemID,
                    target.itemID = source.itemID,
                    target.structureLineOrder = source.structureLineOrder,
                    target.itemQuantity = source.itemQuantity,
                    target.modDate = source.modDate,
                    target.modUsername = source.modUsername,
                    target.status = source.status
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ( [structureLineID],[structureID],[parentItemID],[itemID],[structureLineOrder],[itemQuantity],
                 [addDate],[addUsername],[modDate],[modUsername],[status],[externalStructureLineID],
                 [externalStructureID],[unitID],[quanityTETA])
		VALUES (source.structureLineID, source.structureID, source.parentItemID, source.itemID, 
                source.structureLineOrder, source.itemQuantity, source.addDate, source.addUsername,source.modDate, 
                source.modUsername, source.status, source.externalStructureLineID, source.externalStructureID,
                source.unitID, source.quanityTETA)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[structureline]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[structureline]'  'SOURCE', 
              source.structureLineID into  #report
    ;

  delete from [CRM0100_SMARTTECH].[wms].[structureline]  
  OUTPUT
              'DELETE' ,
             '[CRM0100_SMARTTECH].[wms].[structureline]  ' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[structureline]  '  'SOURCE', 
              deleted.structurelineID  into  #report
from [CRM0100_SMARTTECH].[wms].[structureline] x
  where not EXISTS
  (
    select 1 from  [CRM_METALBUD_2016].[wms].[structureline] y where y.structureLineID =x.structureLineID
  )



/**********************************************************************************************************************************************/
/***            wms.technology                ****/
/***            wms.technology                ****/
/***            wms.technology                ****/

	MERGE INTO [CRM0100_SMARTTECH].[wms].[technology]  AS target
	USING [CRM_METALBUD_2016].[wms].[technology]  AS source
		ON target.technologyID = source.technologyID
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET 
                target.technologyName = source.technologyName,
                target.technologyTypeID = source.technologyTypeID,
                target.modDate = source.modDate,
                target.modUsername = source.modUsername,
                target.status = source.status
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ( [technologyID],[technologyName],[technologyTypeID],[addDate],
                 [addUsername],[modDate],[modUsername],[status])
		VALUES (source.technologyID, source.technologyName, source.technologyTypeID, source.addDate, 
                source.addUsername, source.modDate, source.modUsername, source.status)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[technology]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[technology]'  'SOURCE', 
              source.technologyID into  #report
    ;

/**********************************************************************************************************************************************/
/***            wms.technologyline                ****/
/***            wms.technologyline                ****/
/***            wms.technologyline                ****/

	MERGE INTO [CRM0100_SMARTTECH].[wms].[technologyline]  AS target
	USING [CRM_METALBUD_2016].[wms].[technologyline]  AS source
		ON target.technologyLineID = source.technologyLineID
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET 
                target.remoteSource = source.remoteSource,
                target.remoteID = source.remoteID,
                target.technologyOrder = source.technologyOrder,
                target.description = source.description,
                target.operationTypeID = source.operationTypeID,
                target.areaID = source.areaID,
                target.estimatedTime = source.estimatedTime,
                target.modDate = source.modDate,
                target.modUsername = source.modUsername,
                target.status = source.status
	WHEN NOT MATCHED BY TARGET THEN
		INSERT ( [technologyLineID],[remoteSource],[remoteID],[technologyOrder],[description],
                 [operationTypeID],[areaID],[estimatedTime],[addDate],[addUsername],
                 [modDate],[modUsername],[status])
		VALUES (source.technologyLineID, source.remoteSource, source.remoteID, source.technologyOrder, 
                source.description, source.operationTypeID, source.areaID, source.estimatedTime,
                source.addDate,source.addUsername, source.modDate, source.modUsername, source.status)   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[wms].[technologyline]' 'TARGET', 
             '[CRM_METALBUD_2016].[wms].[technologyline]'  'SOURCE', 
              source.technologyLineID into  #report
    ;

	--Słownik operacji do technologyLine
	MERGE INTO [CRM0100_SMARTTECH].[common].[dictionary] AS target
	USING [CRM_METALBUD_2016].[common].[dictionary] AS source
		ON target.dictionaryID = source.dictionaryID AND target.dictionaryName = 'operationType' AND source.dictionaryName = 'operationType'
	WHEN MATCHED AND target.modDate <> source.modDate THEN 
		UPDATE SET
				target.dictionaryName = source.dictionaryName,
				target.dictionaryKey = source.dictionaryKey,
				target.dictionaryKeyGuid = source.dictionaryKeyGuid,
				target.dictionaryValue = source.dictionaryValue,
				target.dictionaryOrder = source.dictionaryOrder,
				target.dictionaryXML = source.dictionaryXML,
				target.dictionaryDefaultValueInGroup = source.dictionaryDefaultValueInGroup,
				target.dictionaryMasterKey = source.dictionaryMasterKey,
				target.adddate = source.adddate,
				target.addusername = source.addusername,
				target.moddate = source.moddate,
				target.modusername = source.modusername,
				target.[status] = source.[status],
				target.[colorCode] = source.[colorCode],
				target.[isSystem] = source.[isSystem],
				target.attrValRelatedItem = source.attrValRelatedItem,
				target.attrValRelatedPrice = source.attrValRelatedPrice,
				target.translate = source.translate,
				target.remotesource = source.remotesource,
				target.lastStep = source.lastStep,
				target.decimalValue = source.decimalValue,
				target.textValue = source.textValue,
				target.translationIDChecksum = source.translationIDChecksum
	WHEN NOT MATCHED BY TARGET AND source.dictionaryName = 'operationType' THEN
		INSERT (dictionaryID, dictionaryName, dictionaryKey, dictionaryKeyGuid, dictionaryValue, dictionaryOrder, dictionaryXML, 
		dictionaryDefaultValueInGroup, dictionaryMasterKey, adddate, addusername, moddate, modusername, [status], [colorCode], [isSystem],
		attrValRelatedItem, attrValRelatedPrice, translate, remotesource, lastStep, decimalValue, textValue, translationIDChecksum)
		VALUES (source.dictionaryID, source.dictionaryName, source.dictionaryKey, source.dictionaryKeyGuid, source.dictionaryValue,
		source.dictionaryOrder, source.dictionaryXML, source.dictionaryDefaultValueInGroup, source.dictionaryMasterKey, 
		source.addDate, source.addUsername, source.modDate, source.modUsername, source.[status], source.colorCode, source.isSystem,
		source.attrValRelatedItem, source.attrValRelatedPrice, source.[translate], source.remoteSource, source.lastStep,
		source.decimalValue, source.textValue, source.translationIDChecksum)
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[common].[dictionary] WHERE dictionaryName = ''operationType''' 'TARGET', 
             '[CRM_METALBUD_2016].[common].[dictionary] WHERE dictionaryName = ''operationType''' 'SOURCE', 
              source.dictionaryID into  #report
	;

update wms.item set itemcode = 'S01' + itemcode,fullTextCat=0  where itemCode not like 'S01%' and len(itemcode) <16
  


/**********************************************************************************************************************************************/
/***            [firm].[firm]                ****/
/***            [firm].[firm]                ****/
/***            [firm].[firm]               ****/



SET IDENTITY_INSERT [CRM0100_SMARTTECH].[firm].[firm] on;
	MERGE INTO [CRM0100_SMARTTECH].[firm].[firm]  AS target
	USING [CRM_METALBUD_2016].[firm].[firm]  AS source
		ON target.firmID = source.firmID
	WHEN MATCHED AND target.moddate <> source.moddate THEN 
		UPDATE SET 
                    target.firmNR = source.firmNR,
                    target.firmName = source.firmName,
                    target.firmNameShort = source.firmNameShort,
                    target.firmTypeID = source.firmTypeID,
                    target.leadUserID = source.leadUserID,
                    target.countryCode = source.countryCode,
                    target.www = source.www,
                    target.groupID = source.groupID,
                    target.streetName = source.streetName,
                    target.houseNumber = source.houseNumber,
                    target.flatNumber = source.flatNumber,
                    target.postCode = source.postCode,
                    target.city = source.city,
                    target.countryState = source.countryState,
                    target.phoneNumber = source.phoneNumber,
                    target.taxCode = source.taxCode,
                    target.taxRegistration1 = source.taxRegistration1,
                    target.taxRegistration2 = source.taxRegistration2,
                    target.email = source.email,
                    target.faxNumber = source.faxNumber,
                    target.memo = source.memo,
                    target.account = source.account,
                    target.accountInsurance = source.accountInsurance,
                    target.paymentTypeID = source.paymentTypeID,
                    target.deliveryWayID = source.deliveryWayID,
                    target.currency = source.currency,
                    target.paymentTime = source.paymentTime,
                    target.status = source.status,
                    target.addDate = source.addDate,
                    target.addUsername = source.addUsername,
                    target.modDate = source.modDate,
                    target.modUsername = source.modUsername,
                    target.fullTextKeyword = source.fullTextKeyword,
                    target.fullTextCat = 0,
                  --  target.fullTextID = 0,
                    target.classifications = source.classifications,
                    target.externalSystemID = source.externalSystemID,
                    target.discountCategoryID = source.discountCategoryID,
                    target.discountValue = source.discountValue
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (firmID, firmNR, firmName, firmNameShort, firmTypeID, leadUserID, countryCode, www, groupID, streetName, houseNumber, flatNumber, 
                postCode, city, countryState, phoneNumber, taxCode, taxRegistration1, taxRegistration2, email, faxNumber, memo, account, 
                accountInsurance, paymentTypeID, deliveryWayID, currency, paymentTime, status, addDate, addUsername, modDate, modUsername, 
                fullTextKeyword, fullTextCat, fullTextID, classifications, externalSystemID, discountCategoryID, discountValue)
		VALUES (source.firmID, source.firmNR, source.firmName, source.firmNameShort, source.firmTypeID, source.leadUserID, source.countryCode, source.www, 
                source.groupID, source.streetName, source.houseNumber, source.flatNumber, source.postCode, source.city, source.countryState, 
                source.phoneNumber, source.taxCode, source.taxRegistration1, source.taxRegistration2, source.email, source.faxNumber, 
                source.memo, source.account, source.accountInsurance, source.paymentTypeID, source.deliveryWayID, source.currency, 
                source.paymentTime, source.status, source.addDate, source.addUsername, source.modDate, source.modUsername, source.fullTextKeyword, 
                0  /*source.fullTextCat*/, source.fullTextID, source.classifications, source.externalSystemID, source.discountCategoryID, source.discountValue )   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[firm].[firm] ' 'TARGET', 
             '[CRM_METALBUD_2016].[firm].[firm] '  'SOURCE', 
              source.firmID into #report;
SET IDENTITY_INSERT [CRM0100_SMARTTECH].[firm].[firm] OFF;
      --select [TARGET], [action] , count(*) row_affected
      --  from #report 
      --  group by [TARGET],[action]
      --  order by [TARGET],[action]
    

/**********************************************************************************************************************************************/
/***            [common].[address]                ****/
/***            [common].[address]                ****/
/***            [common].[address]               ****/
MERGE INTO [CRM0100_SMARTTECH].[common].[address]  AS target
	USING [CRM_METALBUD_2016].[common].[address] AS source
		ON target.addressID = source.addressID
	WHEN MATCHED AND target.moddate <> source.moddate THEN 
		UPDATE SET 
                target.remoteID= source.remoteID, 
                target.remoteSource= source.remoteSource, 
                target.streetName= source.streetName, 
                target.houseNumber= source.houseNumber, 
                target.flatNumber= source.flatNumber, 
                target.postCode= source.postCode, 
                target.city= source.city, 
                target.countryCode= source.countryCode, 
                target.countryState= source.countryState, 
                target.groupID= source.groupID, 
                target.status= source.status, 
                target.addDate= source.addDate, 
                target.addUsername= source.addUsername, 
                target.modDate= source.modDate, 
                target.modUsername= source.modUsername, 
                target.fullTextCat= 0, 
                target.addressName= source.addressName, 
                target.main= source.main, 
                target.distanceKM= source.distanceKM, 
                target.externaladdressID= source.externaladdressID
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (addressID, remoteID, remoteSource, streetName, houseNumber, flatNumber, postCode, city, countryCode, 
                countryState, groupID, status, addDate, addUsername, modDate, modUsername, fullTextCat, addressName, 
                main, distanceKM, externaladdressID)
		VALUES (source.addressID, source.remoteID, source.remoteSource, source.streetName, source.houseNumber, source.flatNumber, source.postCode, 
                source.city, source.countryCode, source.countryState, source.groupID, source.status, source.addDate, source.addUsername, 
                source.modDate, source.modUsername, 0/*source.fullTextCat*/, source.addressName, source.main, source.distanceKM, source.externaladdressID )   
       OUTPUT $action ,
             '[CRM0100_SMARTTECH].[common].[address] ' 'TARGET', 
             '[CRM_METALBUD_2016].[common].[address] '  'SOURCE', 
              source.addressID into #report;




        select [TARGET], [action] , count(*) row_affected
        from #report 
        group by [TARGET],[action]
        order by [TARGET],[action]

		

  update [CRM0100_SMARTTECH].[common].[fileRepository] set filePath = 
  replace(filepath,'E:\DATASTORE\CRM0100_METALBUD\','C:\DATASTORE\CRM0100_METALBUD\')
  where filepath like 'E:\DATASTORE\CRM0100_METALBUD\%'

 /**********************************************************************************************************************************************/    
/***            zaktualizowanie cache operacji technologicznych na bilansie                ****/
EXEC [MRP].[requisition_repair] 
/**********************************************************************************************************************************************/       
 
END 
GO


