Creating a TierPrice import based on nopCommerce Sync plugin

In this tutorial, I'll be using the nopCommerce Sync plugin to implement a TierPrice import.

We will start coding with the creating DTO, move on to the Map class, and finally end on SQL import script.

Getting started - create base nopCommerce plugin

Create a new class library project "DevPartner.Nop.Plugin.Sync.DynamicsCrm". Add the following folders and plugin.json file. You can view the plugin.json file content below:

{
  "Group": "Dev-Partner",
  "FriendlyName": "DevPartner Sync.DynamicsCrm",
  "SystemName": "DevPartner.Sync.DynamicsCrm",
  "Version": "1.0",
  "SupportedVersions": [ "4.20" ],
  "Author": "Dev-Partner.biz",
  "DisplayOrder": 3,
  "FileName": "DevPartner.Nop.Plugin.Sync.DynamicsCrm.dll",
  "Description": "This plugin allows to send orders and customers to DynamicsCrm"
}

Then add references to the DevPartner.Nop.Plugin.Sync projects. This will be enough for us, as other dependencies, such as Nop.Web.Framework, Nop.Core and Nop.Data, will be connected automatically?

Creating new DTO

Inside of the "domain" namespace we're going to create a public class named DTOTierPrice. This class extends TierPrice.

[SystemName("DTOTierPrice")]
[Table("DP_Sync_TierPrice")]
[ImportScript("~/Plugins/DevPartner.Sync/SqlScripts/Catalog/DP_MergeTierPriceScript.sql")]
public class DTOTierPrice : TierPrice, IDTOBaseEntity
{
    [Required]
    [MaxLength(400)]
    public string ProductExtId { get; set; }

    [Required]
    [MaxLength(400)]
    public string StoreExtId { get; set; }

    [Required]
    [MaxLength(400)]
    public string CustomerRoleExtId { get; set; }
}

Map class

The next class to create is the mapping class.

public class SyncTierPriceMap : NopEntityTypeConfiguration, IDTOBaseMap
{

}

SQL import script

The next important step for us will be the migration script, which update TierPrice directly in the database.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

    MERGE [dbo].[TierPrice] trgt
    USING (SELECT DISTINCT p.[Id] as ProductId, s.[Id] as StoreId, cr.[Id] as CustomerRoleId
	      FROM [dbo].[DP_Sync_TierPrice] tp
		     inner join [dbo].[Product] p on p.[ExtId] = tp.[ProductExtId]
		     inner join [dbo].[Store] s on s.[ExtId] = tp.[StoreExtId]
		     inner join [dbo].[CustomerRole] cr on cr.[ExtId] = tp.[CustomerRoleExtId]
		     ) AS src
    ON trgt.[ProductId]=src.ProductId AND trgt.[StoreId]=src.StoreId AND trgt.[CustomerRoleId]=src.CustomerRoleId
    WHEN NOT MATCHED
        THEN INSERT 
               ([ProductId]
               ,[StoreId]
               ,[CustomerRoleId]
               ,[Quantity]
               ,[Price]
               ,[StartDateTimeUtc]
               ,[EndDateTimeUtc])
         VALUES
               (src.[ProductId]
               ,src.[StoreId]
               ,src.[CustomerRoleId]
               ,src.[Quantity]
               ,src.[Price]
               ,src.[StartDateTimeUtc]
               ,src.[EndDateTimeUtc])
	    WHEN MATCHED AND (trgt.[Quantity] <> src.[Quantity]
            OR trgt.[Price] <> src.[Price]
            OR trgt.[StartDateTimeUtc] <> src.[StartDateTimeUtc]
            OR trgt.[EndDateTimeUtc] <> src.[EndDateTimeUtc])
	    THEN UPDATE
		    SET [Quantity] = src.[Quantity],
            [Price] = src.[Price],
            [StartDateTimeUtc] = src.[StartDateTimeUtc],
            [EndDateTimeUtc] = src.[EndDateTimeUtc]; 
COMMIT;