It is easy but long one. It just insert data product and product variant data into related tables. For updating i have another one(see end). I have SP for importing pictures, prices, stock availability too(send me email -
[email protected])
CREATE PROCEDURE [dbo].[Gen_HpTronicModule_CreateProduct](
@HpProductID as int,
@CategoryID As int,
@ManufacturerID As int,
/* Product - mandatory*/
@Product_Name As nvarchar(400),
@Product_ShortDescription As nvarchar(max),
@Product_FullDescription As nvarchar(max),
@Product_AdminComment As nvarchar(max),
@Product_TemplateID As int,
/* ProductVariant - mandatory*/
@ProductVariant_SKU As nvarchar(100),
@ProductVariant_ManufacturerPartNumber As nvarchar(100),
@ProductVariant_TaxCategoryID As int,
@ProductVariant_Weight As decimal(18,4),
@ProductVariant_Length As decimal(18,4),
@ProductVariant_Width As decimal(18,4),
@ProductVariant_Height As decimal(18,4)
) AS BEGIN SET NOCOUNT ON
--#region Defaults
Declare
/* Product - rest*/
@Product_ShowOnHomePage As bit = 0,
@Product_MetaKeywords As nvarchar(400) = '',
@Product_MetaDescription As nvarchar(4000) = '',
@Product_MetaTitle As nvarchar(400) = '',
@Product_SEName As nvarchar(100) = '',
@Product_AllowCustomerReviews As bit = 1,
@Product_AllowCustomerRatings As bit = 1,
@Product_RatingSum As int = 0,
@Product_TotalRatingVotes As int = 0,
@Product_Published As bit = 1,
@Product_Deleted As bit = 0,
/* ProductVariant - rest*/
@ProductVariant_Name As nvarchar(400) = '',
@ProductVariant_Description As nvarchar(4000) = '',
@ProductVariant_AdminComment As nvarchar(4000) = 'Created by GENiTOR HP Import Module. Please, do not delete this Product Variant.',
@ProductVariant_IsGiftCard As bit = 0,
@ProductVariant_IsDownload As bit = 0,
@ProductVariant_DownloadID As int = 0,
@ProductVariant_UnlimitedDownloads As bit = 0,
@ProductVariant_MaxNumberOfDownloads As int = 0,
@ProductVariant_DownloadExpirationDays As int = 0,
@ProductVariant_DownloadActivationType As int = 0,
@ProductVariant_HasSampleDownload As bit = 0,
@ProductVariant_SampleDownloadID As int = 0,
@ProductVariant_HasUserAgreement As bit = 0,
@ProductVariant_UserAgreementText As nvarchar(max) = '',
@ProductVariant_IsRecurring As bit = 0,
@ProductVariant_CycleLength As int = 1,
@ProductVariant_CyclePeriod As int = 0,
@ProductVariant_TotalCycles As int = 1,
@ProductVariant_IsShipEnabled As bit = 1,
@ProductVariant_IsFreeShipping As bit = 0,
@ProductVariant_AdditionalShippingCharge As money = 0,
@ProductVariant_IsTaxExempt As bit = 0,
@ProductVariant_ManageInventory As int = 1,
@ProductVariant_StockQuantity As int = 0,
@ProductVariant_DisplayStockAvailability As bit = 1,
@ProductVariant_MinStockQuantity As int = 0,
@ProductVariant_LowStockActivityID As int = 0,
@ProductVariant_NotifyAdminForQuantityBelow As int = 1,
@ProductVariant_OrderMinimumQuantity As int = 1,
@ProductVariant_OrderMaximumQuantity As int = 99,
@ProductVariant_WarehouseID As int = 0,
@ProductVariant_DisableBuyButton As bit = 0,
@ProductVariant_Price As money = 0,
@ProductVariant_OldPrice As money = 0,
@ProductVariant_ProductCost As money = 0,
@ProductVariant_CustomerEntersPrice As bit = 0,
@ProductVariant_MinimumCustomerEnteredPrice As money = 0,
@ProductVariant_MaximumCustomerEnteredPrice As money = 0,
@ProductVariant_PictureID As int = 0,
@ProductVariant_AvailableStartDateTime As datetime = null,
@ProductVariant_AvailableEndDateTime As datetime = null,
@ProductVariant_Published As bit = 0,
@ProductVariant_Deleted As bit = 0,
@ProductVariant_DisplayOrder As int = 1,
@ProductVariant_DisplayStockQuantity As bit = 0,
@ProductVariant_Backorders As int = 0,
@ProductVariant_GiftCardType As int = 0,
@ProductVariant_CallForPrice As bit = 0,
@now As datetime = GETDATE();
--#endregion
INSERT INTO [dbo].[Nop_Product](
[Name]
,[ShortDescription]
,[FullDescription]
,[AdminComment]
,[TemplateID]
,[ShowOnHomePage]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[SEName]
,[AllowCustomerReviews]
,[AllowCustomerRatings]
,[RatingSum]
,[TotalRatingVotes]
,[Published]
,[Deleted]
,[CreatedOn]
,[UpdatedOn]
) VALUES (
@Product_Name,
@Product_ShortDescription,
@Product_FullDescription,
@Product_AdminComment,
@Product_TemplateID,
@Product_ShowOnHomePage,
@Product_MetaKeywords,
@Product_MetaDescription,
@Product_MetaTitle,
@Product_SEName,
@Product_AllowCustomerReviews,
@Product_AllowCustomerRatings,
@Product_RatingSum,
@Product_TotalRatingVotes,
@Product_Published,
@Product_Deleted,
@now,
@now);
Declare @ProductID As int;
Select @ProductID = SCOPE_IDENTITY();
INSERT INTO [dbo].[Nop_ProductVariant](
[ProductID]
,[Name]
,[SKU]
,[Description]
,[AdminComment]
,[ManufacturerPartNumber]
,[IsGiftCard]
,[IsDownload]
,[DownloadID]
,[UnlimitedDownloads]
,[MaxNumberOfDownloads]
,[DownloadExpirationDays]
,[DownloadActivationType]
,[HasSampleDownload]
,[SampleDownloadID]
,[HasUserAgreement]
,[UserAgreementText]
,[IsRecurring]
,[CycleLength]
,[CyclePeriod]
,[TotalCycles]
,[IsShipEnabled]
,[IsFreeShipping]
,[AdditionalShippingCharge]
,[IsTaxExempt]
,[TaxCategoryID]
,[ManageInventory]
,[StockQuantity]
,[DisplayStockAvailability]
,[MinStockQuantity]
,[LowStockActivityID]
,[NotifyAdminForQuantityBelow]
,[OrderMinimumQuantity]
,[OrderMaximumQuantity]
,[WarehouseID]
,[DisableBuyButton]
,[Price]
,[OldPrice]
,[ProductCost]
,[CustomerEntersPrice]
,[MinimumCustomerEnteredPrice]
,[MaximumCustomerEnteredPrice]
,[Weight]
,[Length]
,[Width]
,[Height]
,[PictureID]
,[AvailableStartDateTime]
,[AvailableEndDateTime]
,[Published]
,[Deleted]
,[DisplayOrder]
,[CreatedOn]
,[UpdatedOn]
,[DisplayStockQuantity]
,[Backorders]
,[GiftCardType]
,[CallForPrice]
) VALUES (
@ProductID,
@ProductVariant_Name,
@ProductVariant_SKU,
@ProductVariant_Description,
@ProductVariant_AdminComment,
@ProductVariant_ManufacturerPartNumber,
@ProductVariant_IsGiftCard,
@ProductVariant_IsDownload,
@ProductVariant_DownloadID,
@ProductVariant_UnlimitedDownloads,
@ProductVariant_MaxNumberOfDownloads,
@ProductVariant_DownloadExpirationDays,
@ProductVariant_DownloadActivationType,
@ProductVariant_HasSampleDownload,
@ProductVariant_SampleDownloadID,
@ProductVariant_HasUserAgreement,
@ProductVariant_UserAgreementText,
@ProductVariant_IsRecurring,
@ProductVariant_CycleLength,
@ProductVariant_CyclePeriod,
@ProductVariant_TotalCycles,
@ProductVariant_IsShipEnabled,
@ProductVariant_IsFreeShipping,
@ProductVariant_AdditionalShippingCharge,
@ProductVariant_IsTaxExempt,
@ProductVariant_TaxCategoryID,
@ProductVariant_ManageInventory,
@ProductVariant_StockQuantity,
@ProductVariant_DisplayStockAvailability,
@ProductVariant_MinStockQuantity,
@ProductVariant_LowStockActivityID,
@ProductVariant_NotifyAdminForQuantityBelow,
@ProductVariant_OrderMinimumQuantity,
@ProductVariant_OrderMaximumQuantity,
@ProductVariant_WarehouseID,
@ProductVariant_DisableBuyButton,
@ProductVariant_Price,
@ProductVariant_OldPrice,
@ProductVariant_ProductCost,
@ProductVariant_CustomerEntersPrice,
@ProductVariant_MinimumCustomerEnteredPrice,
@ProductVariant_MaximumCustomerEnteredPrice,
@ProductVariant_Weight,
@ProductVariant_Length,
@ProductVariant_Width,
@ProductVariant_Height,
@ProductVariant_PictureID,
@ProductVariant_AvailableStartDateTime,
@ProductVariant_AvailableEndDateTime,
@ProductVariant_Published,
@ProductVariant_Deleted,
@ProductVariant_DisplayOrder,
@now,
@now,
@ProductVariant_DisplayStockQuantity,
@ProductVariant_Backorders,
@ProductVariant_GiftCardType,
@ProductVariant_CallForPrice);
Declare @ProductVariantID As int;
Select @ProductVariantID = SCOPE_IDENTITY();
INSERT INTO [dbo].[Gen_HpTronicModule_ProductMapping] ( HpProductID, NopProductID, NopProductVariantId, IsLocked, NopCategoryID, NopManufacturerID ) VALUES ( @HpProductID, @ProductID, @ProductVariantID, 0, @CategoryID, @ManufacturerID);
INSERT INTO [dbo].[Nop_Product_Category_Mapping] (
ProductID,
CategoryID,
IsFeaturedProduct,
DisplayOrder
) VALUES ( @ProductID, @CategoryID, 0, 1 ) ;
INSERT INTO [dbo].[Nop_Product_Manufacturer_Mapping] (
ProductID,
ManufacturerID,
IsFeaturedProduct,
DisplayOrder
) VALUES ( @ProductID, @ManufacturerID, 0, 1 ) ;
RETURN @ProductID;
END
------------------------- PRODUCT UPDATE
CREATE PROCEDURE [dbo].[Gen_HpTronicModule_UpdateProduct]
(
@CategoryID As int,
@ManufacturerID As int,
@ProductID As int,
@Name As nvarchar(400),
@FullDescription As nvarchar(max),
@AdminComment As nvarchar(max),
@ProductVariantID As int,
@Width As decimal(18,4),
@Height As decimal(18,4),
@Length As decimal(18,4),
@Weight As decimal(18,4),
@ManufacturerPartNumber as nvarchar(100)
)AS
BEGIN SET NOCOUNT ON;
Declare @oldCategoryID as int, @oldManufacturerID as int;
Select @oldCategoryID = NopCategoryID, @oldManufacturerID = NopManufacturerID From dbo.Gen_HpTronicModule_ProductMapping Where NopProductID = @ProductID;
If @oldCategoryID <> @CategoryID -- nova ani puvodni nemuze byt null, tak staci update
Update [dbo].[Nop_Product_Category_Mapping] Set CategoryID = @CategoryID Where ProductID = @ProductID And CategoryID = @oldCategoryID;
If @oldManufacturerID <> @ManufacturerID -- novy anipuvodni nemuze byt null, tak staci update
Update [dbo].[Nop_Product_Manufacturer_Mapping] Set ManufacturerID = @ManufacturerID Where ProductID = @ProductID And ManufacturerID = @oldManufacturerID;
UPDATE dbo.Gen_HpTronicModule_ProductMapping Set NopCategoryID = @CategoryID, NopManufacturerID = @ManufacturerID Where NopProductID = @ProductID;
UPDATE dbo.Nop_Product Set [Name] = @Name, FullDescription = @FullDescription, AdminComment = @AdminComment Where ProductID = @ProductID;
UPDATE dbo.Nop_ProductVariant Set [Width] = @Width, Height = @Height, [Length] = @Length, [Weight] = @Weight, ManufacturerPartNumber = ManufacturerPartNumber Where ProductVariantId = @ProductVariantID;
END