user
80 lines
2.3 KiB
PL/PgSQL
80 lines
2.3 KiB
PL/PgSQL
USE [Database]
|
|
|
|
IF OBJECT_ID('[Procedures].[insert_or_update_barcode]', 'P') IS NOT NULL
|
|
DROP PROCEDURE [Procedures].[insert_or_update_barcode];
|
|
GO
|
|
CREATE PROCEDURE [Procedures].[insert_or_update_barcode]
|
|
@product_id AS INT,
|
|
@barcode_attribute_id AS INT,
|
|
@barcode AS BIGINT = NULL,
|
|
@record_id AS INT = NULL OUTPUT
|
|
AS
|
|
BEGIN TRANSACTION;
|
|
SET NOCOUNT ON;
|
|
|
|
IF @barcode IS NOT NULL AND EXISTS(
|
|
SELECT *
|
|
FROM [Tables].[product_values]
|
|
WHERE [attribute_id] = @barcode_attribute_id
|
|
AND CAST([value] AS BIGINT) = @barcode
|
|
)
|
|
BEGIN;
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW 53000, 'The barcode is not unique', 1;
|
|
END;
|
|
|
|
CREATE TABLE [Tables].[#existing_hash_codes](
|
|
[hash_code] NVARCHAR(13)
|
|
);
|
|
INSERT INTO [#existing_hash_codes]([hash_code])
|
|
SELECT [value]
|
|
FROM [Tables].[product_attributes] AS [pa]
|
|
JOIN [Tables].[product_values] AS [pv]
|
|
ON [pv].[product_id] = [pa].[product_id]
|
|
AND [pv].[attribute_id] = [pa].[attribute_id]
|
|
WHERE [pa].[attribute_id] = @barcode_attribute_id
|
|
|
|
-- begin creating barcode
|
|
IF @barcode IS NULL
|
|
EXECUTE [Procedures].[get_next_barcode] @next_barcode = @barcode OUTPUT;
|
|
|
|
WHILE EXISTS(
|
|
SELECT *
|
|
FROM [#existing_hash_codes]
|
|
WHERE CAST([hash_code] AS BIGINT) = @barcode
|
|
)
|
|
OR EXISTS(
|
|
SELECT *
|
|
FROM [Tables].[product_values]
|
|
WHERE [attribute_id] = @barcode_attribute_id
|
|
AND CAST([value] AS BIGINT) = @barcode
|
|
)
|
|
BEGIN
|
|
SET @barcode += 1;
|
|
END
|
|
-- end creating barcode
|
|
|
|
IF EXISTS(
|
|
SELECT *
|
|
FROM [Tables].[product_values]
|
|
WHERE [attribute_id] = @barcode_attribute_id
|
|
AND [product_id] = @product_id
|
|
AND [value] IS NOT NULL
|
|
)
|
|
BEGIN
|
|
UPDATE [Tables].[product_values]
|
|
SET @record_id = [id],
|
|
[value] = [Functions].[to_barcode_ean_13](@barcode)
|
|
WHERE [product_id] = @product_id
|
|
AND [attribute_id] = @barcode_attribute_id;
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO [Tables].[product_values]([product_id], [attribute_id], [value])
|
|
VALUES(@product_id, @barcode_attribute_id, [Functions].[to_barcode_ean_13](@barcode));
|
|
SET @record_id = SCOPE_IDENTITY();
|
|
END
|
|
|
|
COMMIT TRANSACTION;
|
|
GO
|