Untitled

--Reset 
UPDATE sh_products SET SaleCount = 0 
--Run
DECLARE cursor_orders CURSOR FOR SELECT o.StoreID,od.Quantity,od.ProductID,od.JSONField FROM SH_Orders as o
join SH_OrderDetails as od on o.ID = od.OrderID  WHERE o.IsDeleted=0 and o.OrderStatus != 255 ;

DECLARE  @storeId INT;
DECLARE  @quantity DECIMAL;
DECLARE  @productId INT;
DECLARE @jsonField NVARCHAR(MAX)

OPEN cursor_orders;
FETCH NEXT FROM cursor_orders INTO @storeId,@quantity,@productId,@jsonField;

WHILE @@FETCH_STATUS = 0
BEGIN 
	UPDATE sh_products SET SaleCount = SaleCount + @quantity WHERE ID =@productId and StoreID = @storeId ;
 
	IF (LEN(@jsonField) > 0)
			BEGIN
				DECLARE cursor_addons_list CURSOR FOR SELECT * FROM OPENJSON(@jsonField);	
					DECLARE @key  NVARCHAR(MAX);
					DECLARE @value NVARCHAR(MAX);
					DECLARE @type INT;

					OPEN cursor_addons_list;
					FETCH NEXT FROM cursor_addons_list INTO @key, @value,@type
					WHILE @@FETCH_STATUS = 0  
					BEGIN  
						IF (@key like '%Addons%')
						BEGIN
							DECLARE cursor_addon CURSOR FOR SELECT * FROM OPENJSON (@value)  
							WITH (   
								ID INT '$.ID' ,  
								Title NVARCHAR(1000) '$.Title',  
								NewPrice DECIMAL '$.NewPrice'
							) 
	
							DECLARE @addonProductId INT;
							DECLARE @productTitle NVARCHAR(MAX);
							DECLARE @productPrice DECIMAL;

							OPEN cursor_addon;
							FETCH NEXT FROM cursor_addon INTO @addonProductId, @productTitle,@productPrice
							WHILE @@FETCH_STATUS = 0  
							BEGIN
								UPDATE sh_products SET SaleCount = SaleCount + @quantity WHERE ID =@addonProductId and StoreID = @storeId 
								FETCH NEXT FROM cursor_addon INTO @addonProductId, @productTitle,@productPrice
							END;
							CLOSE cursor_addon;
							DEALLOCATE cursor_addon;
						END;

						FETCH NEXT FROM cursor_addons_list INTO @key, @value,@type
					END;
					CLOSE cursor_addons_list;
					DEALLOCATE cursor_addons_list;
			END;
	FETCH NEXT FROM cursor_orders INTO @storeId,@quantity,@productId,@jsonField;
END;
CLOSE cursor_orders;
DEALLOCATE cursor_orders;
GO