Actions

  DECLARE @date_val date, @date_raw nvarchar(max)
  SET @date_raw = dbo.fn_de_act_get_arg(@query, 'dateField')
  SELECT @date_val = DATEFROMPARTS(year_val, month_val, day_val)
    FROM OPENJSON(JSON_QUERY(@date_raw))
	WITH ( year_val int '$.year', month_val int '$.month', day_val int '$.day' )

{
    "clientData": {
        "gridSelection": "all",
        "actionArgs": {
            "refreshGridAfterAction": true,
            "includeAffected": true,
            "serieOverride": {
                "readFromArg": "viewSerie"
            }
        }
    },
    "operations": [
        {
            "type": "modal",
            "fields": [
                {
                    "name": "viewSerie",
                    "caption": "View serie",
                    "type": "select",
                    "optionValues": "viewDataSeries",
                    "description": "Selection of any of the data series visible in the view"
                },
                {
                    "name": "planSerie",
                    "caption": "Plan serie",
                    "type": "select",
                    "optionValues": "planDataSeries",
                    "description": "Selection of any of the data series in the plan",
                    "required": true
                },
                {
                    "name": "customOptions",
                    "caption": "A custom list of options",
                    "type": "select",
                    "optionValues": [
                        {
                            "name": "val1",
                            "caption": "Value 1"
                        },
                        {
                            "name": "val2",
                            "caption": "Value 2"
                        },
                        {
                            "name": "val3",
                            "caption": "Value 3"
                        }
                    ],
                    "defaultValue": "val2",
                    "description": "Another long description of this field"
                },
                {
                    "name": "intField",
                    "caption": "Running time (sec)",
                    "type": "int",
                    "description": "This is an integer value"
                },
                {
                    "name": "stringField",
                    "caption": "String Value",
                    "type": "string",
                    "description": "This is a string value"
                }
            ]
        },
        {
            "type": "sp",
            "name": "test"
        }
    ]
}

CREATE PROCEDURE [dbo].[de_act_example_a]
-- the following parameters must be included when creating all 'workspace' actions that are used in dbo.de_action_sps
(
    @query NVARCHAR(MAX),
    @user_id INT,
	@debug BIT = 0
)
AS
BEGIN

	SET NOCOUNT ON
	BEGIN TRY

	-- Uncomment this below for debugging and comment out all above. Remember you must use the query from your action
	-- which you find by running your action in the system and catching the action from watching live data in Extended Events

	--DECLARE @query NVARCHAR(MAX) = N'{"args":{"includeAffected":true,"serieOverride":{"readFromArg":"copyTo"},"copyFrom":"forecast","copyTo":"planner_baseline","displaySuccessfulNotification":true},"query":{"filters":[{"column":{"name":"date_month_id"},"values":[-6,12],"operator":"RELPERIODS","ui":{"selectedSubtypeId":"caption_month_short","column":{"name":"period"},"fixed":false}},{"column":{"name":"location_id"},"operator":"=","values":10}],"columns":[{"name":"item_no"},{"name":"primary_vendor"}],"series":[{"name":"planner_baseline"},{"name":"planner_promotions"},{"name":"demand"},{"name":"sale"}],"pivot":{"column":"caption_month_short","paging":{"offset":0,"limit":1000}}}}',
	--@user_id INT = 1,
	--@debug BIT = 0

-- declaring some variables and then parsing the query string
	DECLARE @series de_qb_data_series,
	@filters de_qb_filters, @columns de_qb_columns, @table_joins NVARCHAR(MAX),  @where_filter NVARCHAR(MAX), @sql NVARCHAR(MAX)



	-- only for debugging
    DECLARE @msg NVARCHAR(500)
    IF @debug = 1 PRINT 'VALIDATING JSON:'
	IF ISJSON(@query) = 0
	BEGIN
		SET @msg = 'Invalid JSON request'
		SELECT dbo.json_error(@msg) as result
		EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		RETURN
	END

	-- I recommend that you comment out the select statements below each parsing part to see what is stored in the @filters/@seris/@columns
	-- variables then you can see that all the filters/drilldowns/series/columns from your view in the workspace are stored in the variables.

	-- parsing filters
	INSERT INTO @filters
	SELECT * FROM [dbo].[fn_de_qp_filters](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @filters

	-- parsing series
	INSERT INTO @series
	SELECT * FROM [dbo].[fn_de_qp_series](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @series

	--parsing columns
	INSERT INTO @columns
	SELECT * FROM [dbo].[fn_de_qp_columns](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @columns

	--now let's get all the joins and filters on string format. You can comment out the select statements (@table_joins/@where_filter)
	-- to see what is in the string variables

	EXEC [de_qb_sql_table_joins] @columns,NULL,@filters,@user_id, @table_joins OUT

	--SELECT @table_joins

	EXEC [dbo].[de_qb_sql_where_filter_clause] @filters, 1, @where_filter OUT

	--SELECT @where_filter


	-- YOU SHOULD ONLY NEED TO ADJUST/CHANGE THE CODE BELOW THIS COMMENT

	-- Finally the parsed data must be combined and I recommend using temp table which stores all the data in the view/grid
	-- and then you can do whatever you want to do/update regarding the data.

	--creating temp table that in my case I call update_table. Adjust the name and columns to your need based on what is in your view/grid
	IF OBJECT_ID('tempdb..#update_table') IS NOT NULL
	DROP TABLE #update_table
	CREATE TABLE #update_table([item_id] INT,
							   [item_no] NVARCHAR(MAX),
							   [location] NVARCHAR(100),
							   [primary_vendor] NVARCHAR(100),
							   [first_date_id_of_month] INT)


	-- Now I use the sql string to insert into the temp table. Here you have to change the columns you are inserting into based on the columns
	-- in the table you created above as well as selecting the right columns from right tables.
	-- The table names always start with 'DET' and then the id of the table in data_element_ref_tables. For example the items table has id=1 in
	-- dbo.data_element_ref_tables which means that all the columns you want and need from the items table come from DET1 e.g. DET1.id is
	-- the item_id

	SELECT @sql = 'INSERT INTO #update_table ([item_id], [item_no], [location], [primary_vendor], [first_date_id_of_month])
				   SELECT DISTINCT DET1.id, DET2.item_no, DET4.name, DET5.name, DET0.first_date_id_of_month' -- change this row and the row above
				   + CHAR(10) + @table_joins + CHAR(10) + CASE WHEN LEN(@where_filter) = 0 THEN '' ELSE @where_filter END -- should not need to change this row

	-- comment out the statement below to see your complete string and try running it if you want
	--PRINT @sql

	EXEC sp_executesql @sql -- this will run what is in the sql string. So in this case we are inserting into the update_table

	-- comment out the statement below to see that you have all the required data from your view/grid now stored in a temp table
	--SELECT * FROM #update_table


	-- do whatever you need to do (update values, insert new rows, run some procedures) below by
	-- joining with/using the data in the temp table you created above.
	----------------------------------------------------------------------


	----------------------------------------------------------------------
	SELECT dbo.json_ok('Everything executed fine!') as result  -- the procedure must return a result, like this. You can change the result message if you want.

    END TRY
    BEGIN CATCH
		    DECLARE @sp_name NVARCHAR(500)
		    SELECT @sp_name = s.[name]+'.'+OBJECT_NAME(@@PROCID), @msg = 'Action could not be executed:'+ERROR_MESSAGE()
		    FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
		    WHERE OBJECT_ID = @@PROCID
            EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		    SELECT dbo.json_error('Action could not be executed') AS result
    END CATCH;
END
GO

 {"clientData":{
      "gridSelection":"all",
      "actionArgs":{
         "includeAffected":true,
         "serieOverride":{
            "readFromArg":"viewSerie"
         }
      }
   },
   "operations":[
      {
         "type":"modal",
         "fields":[
            {
               "name":"customOptions",
               "caption":"A custom list of options",
               "type":"select",
               "optionValues":[
                  {
                     "name":"optionA",
                     "caption":"A"
                  },
                  {
                     "name":"optionB",
                     "caption":"B"
                  },
                  {
                     "name":"optionC",
                     "caption":"C"
                  }
               ],
               "defaultValue":"optionA",
               "description":"Another long description of this field"
            }
         ]
      },
      {
         "type":"sp",
         "name":"update_sales_plan_by_locations"
      }
   ]
}

CREATE PROCEDURE [dbo].[de_act_example_b]
-- the following parameters must be included when creating all 'workspace' actions that are used in dbo.de_action_sps
(
    @query NVARCHAR(MAX),
    @user_id INT,
	@debug BIT = 0
)
AS
BEGIN

	SET NOCOUNT ON
	BEGIN TRY

	-- Uncomment this below for debugging and comment out all above. Remember you must use the query from your action 
	-- which you find by running your action in the system and catching the action from watching live data in Extended Events

	--DECLARE @query NVARCHAR(MAX) = N'{"args":{"includeAffected":true,"serieOverride":{"readFromArg":"viewSerie"},"customOptions":"optionA","displaySuccessfulNotification":true},"query":{"filters":[{"column":{"name":"date_month_id"},"values":[1,12],"operator":"RELPERIODS","ui":{"selectedSubtypeId":"caption_month_short","column":{"name":"period"},"fixed":false,"class":"Oe"}},{"column":{"name":"location_id"},"operator":"=","values":[10]}],"columns":[],"series":[{"name":"planner_baseline"},{"name":"planner_promotions"},{"name":"demand"},{"name":"sale"}],"pivot":{"column":"caption_month_short","paging":{"offset":0,"limit":1000}}}}',
	--@user_id INT = 1,
	--@debug BIT = 0

-- declaring some variables and then parsing the query string
	DECLARE  @series de_qb_data_series,
	@filters de_qb_filters, @columns de_qb_columns, @table_joins NVARCHAR(MAX),  @where_filter NVARCHAR(MAX), @sql NVARCHAR(MAX)


	-- only for debugging
    DECLARE @msg NVARCHAR(500)
    IF @debug = 1 PRINT 'VALIDATING JSON:'
	IF ISJSON(@query) = 0
	BEGIN
		SET @msg = 'Invalid JSON request'
		SELECT dbo.json_error(@msg) as result
		EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		RETURN
	END


	-- I recommend that you comment out the select statements below each parsing part to see what is stored in the @filters/@seris/@columns 
	-- variables then you can see that all the filters/drilldowns/series/columns from your view in the workspace are stored in the variables.

	-- parsing filters
	INSERT INTO @filters
	SELECT * FROM [dbo].[fn_de_qp_filters](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @filters

	-- parsing series
	INSERT INTO @series
	SELECT * FROM [dbo].[fn_de_qp_series](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @series

	--parsing columns
	INSERT INTO @columns
	SELECT * FROM [dbo].[fn_de_qp_columns](JSON_QUERY(@query, '$.query'))
	--SELECT * FROM @columns

	--now let's get all the joins and filters on string format. You can comment out the select statements (@table_joins/@where_filter) 
	-- to see what is in the string variables

	EXEC [de_qb_sql_table_joins] @columns,NULL,@filters,@user_id, @table_joins OUT

	--SELECT @table_joins

	EXEC [dbo].[de_qb_sql_where_filter_clause] @filters, 1, @where_filter OUT

	--SELECT @where_filter

	-- YOU SHOULD ONLY NEED TO ADJUST/CHANGE THE CODE BELOW THIS COMMENT

	----------------------------------------------------------------------------------------------
	-- HERE YOU HAVE TO DO DO CHANGES RELATED TO THE PARAMETERS IN YOUR ACTION

	-- Declaring variable that will store the option chosen in the action (A,B,C in my case)
	-- If your action has int/string variables and more than one parameter you can declare your variables here for that
	DECLARE @option NVARCHAR(MAX)

	-- Here I'm looking at the arguments from the query and storing the option chosen into the @option variable
	-- Example: if the user has chosen option A when running the action, the @option variable would be 'A'
	-- If you have other types of parameters I recommend that you run the following query: SELECT * FROM @q_args
	-- There you should see your parameters and in that way figure out how to catch them

    SET @option = JSON_VALUE(@query, '$.args.customOptions')

	--------------------------------------------------------------------------------------------------------


	-- Finally the parsed data must be combined and I recommend using temp table which stores all the data in the view/grid 
	-- and then you can do whatever you want to do/update regarding the data.

	--creating temp table that in my case I call item_list. Adjust the name and columns to your need based on what is in your view/grid
	IF OBJECT_ID('tempdb..#item_list') IS NOT NULL
	DROP TABLE #item_list
	CREATE TABLE #item_list([item_id] INT)


	-- Now I use the procedure [dbo].[de_qb_sql_specific_columns_select] to give me the column item_id (dbo.data_element_ref_columns) using the @query and insert into the ids into the temp table. Here you have to change the columns you are inserting into based on the columns
	-- in the table you created.

    EXEC dbo.de_qb_sql_specific_columns_select @query=@query, @user_id= @user_id, @debug = @debug, @columns_comma_list = 'item_id', @distinct = 1, @sql = @sql OUT

    SET @sql = 'INSERT INTO #item_list (item_id) ' + @sql + ';'

	-- comment out the statement below to see your complete string and try running it if you want
	PRINT @sql
	
	EXEC sp_executesql @sql -- this will run what is in the sql string. So in this case we are inserting into the item_list

	-- comment out the statement below to see that you have all the required data from your view/grid now stored in a temp table
	--SELECT * FROM #item_list


	-- do whatever you need to do (update values, insert new rows, run some procedures) below by 
	--joining with/using the data in the temp table you created above.
	----------------------------------------------------------------------
	-- Uncomment below if you are using the parameters from the action


	--IF @option = 'A'
	--BEGIN
	--	-- do something
	--END

	--IF @option = 'B'
	--BEGIN
	--	-- do something
	--END

	--IF @option = 'C'
	--BEGIN
	--	-- do something
	--END

    -- the procedure must return a result message. Lets have the result message display what option was selected and the number of item_ids behind the grid data.

    DECLARE @result_msg NVARCHAR(MAX)

    SELECT @result_msg = 'item_id count: '+ CAST(COUNT(il.item_id) AS NVARCHAR)+CHAR(10)+'option selected: '+@option
    FROM #item_list il
	----------------------------------------------------------------------
	--SELECT dbo.json_ok('Everything executed fine!') as result  
    SELECT dbo.json_ok(@result_msg) as result 

    END TRY
    BEGIN CATCH
		    DECLARE @sp_name NVARCHAR(500)
		    SELECT @sp_name = s.[name]+'.'+OBJECT_NAME(@@PROCID), @msg = 'Action could not be executed:'+ERROR_MESSAGE()
		    FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
		    WHERE OBJECT_ID = @@PROCID
            EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		    SELECT dbo.json_error('Action could not be executed') AS result
    END CATCH;
END
GO

CREATE PROCEDURE [dbo].[de_act_example_c]
-- the following parameters must be included when creating all 'planner' actions that are used in dbo.de_action_sps
(
    @query NVARCHAR(MAX),
    @user_id INT,
	@debug BIT = 0
)
AS
BEGIN

	SET NOCOUNT ON
	BEGIN TRY

	-- Uncomment this below for debugging and comment out all above. Remember you must use the query from your action
	-- which you find by running your action in the system and catching the action from watching live data in Extended Events

	--DECLARE @query NVARCHAR(MAX) = N'{"args":{"includeAffected":true,"serieOverride":{"readFromArg":"copyTo"},"copyFrom":"forecast","copyTo":"planner_baseline","displaySuccessfulNotification":true},"query":{"filters":[{"column":{"name":"date_month_id"},"values":[-6,12],"operator":"RELPERIODS","ui":{"selectedSubtypeId":"caption_month_short","column":{"name":"period"},"fixed":false}},{"column":{"name":"location_id"},"operator":"=","values":10}],"columns":[{"name":"item_no"},{"name":"primary_vendor"}],"series":[{"name":"planner_baseline"},{"name":"planner_promotions"},{"name":"demand"},{"name":"sale"}],"pivot":{"column":"caption_month_short","paging":{"offset":0,"limit":1000}}}}',
	--@user_id INT = 1,
	--@debug BIT = 0

-- declaring some variables and then parsing the query string
	DECLARE  @series de_qb_data_series,
	@filters de_qb_filters, @columns de_qb_columns, @table_joins NVARCHAR(MAX),  @where_filter NVARCHAR(MAX), @sql NVARCHAR(MAX)

	-- only for debugging
    DECLARE @msg NVARCHAR(500)
    IF @debug = 1 PRINT 'VALIDATING JSON:'
	IF ISJSON(@query) = 0
	BEGIN
		SET @msg = 'Invalid JSON request'
		SELECT dbo.json_error(@msg) as result
		EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		RETURN
	END


	-- the easiest way to use proportion from another data series is to do the following:
	-- 1. copy from the data series you want to use the proportion from into your data series by using copy values
	-- 2. then use the standard update action to spread proportionally.

	-- creating two new variables which we use when we call the copy values action
	DECLARE @new_query NVARCHAR(MAX), @q_arg_new NVARCHAR(MAX)

	-- change the copyFrom and copyTo data series if you are not using forecast and promotions
	SET @q_arg_new = '{"args":{"includeAffected":true,"serieOverride":{"readFromArg":"copyTo"},"copyFrom":"forecast","copyTo":"planner_promotions","displaySuccessfulNotification":true}'

	-- extract only the query part (not the arguments since we are using the arguments in @q_arg_new
	-- uncomment the 'select @new_query' below and compare it with 'select @query' to see the difference

	SELECT @new_query =  JSON_QUERY(@query, '$.query')
	-- select @new_query

	-- combine the arguments and query parts
	SET @new_query = @q_arg_new + ',"query":' + @new_query + '}'

	-- run the copy values action with the new query
	exec de_execute_action @de_action_name=N'copy_serie',@query = @new_query, @user_id = @user_id

	-- finally run the batch update with the original query.
	exec de_execute_action @de_action_name=N'batch_update',@query=@query,@user_id=@user_id



	----------------------------------------------------------------------
	SELECT dbo.json_ok('Everything executed fine!') as result  -- the procedure must return a result, like this. You can change the result message if you want.

    END TRY
    BEGIN CATCH
		    DECLARE @sp_name NVARCHAR(500)
		    SELECT @sp_name = s.[name]+'.'+OBJECT_NAME(@@PROCID), @msg = 'Action could not be executed:'+ERROR_MESSAGE()
		    FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
		    WHERE OBJECT_ID = @@PROCID
            EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		    SELECT dbo.json_error('Action could not be executed') AS result
    END CATCH;
END
GO

CREATE PROCEDURE [dbo].[de_act_example_d]
-- the following parameters must be included when creating all 'planner' actions that are used in dbo.de_action_sps
(
    @query NVARCHAR(MAX),
    @user_id INT,
	@debug BIT = 0
)
AS
BEGIN

	SET NOCOUNT ON
	BEGIN TRY

	-- Uncomment this below for debugging and comment out all above. Remember you must use the query from your action
	-- which you find by running your action in the system and catching the action from watching live data in Extended Events

--	DECLARE @query NVARCHAR(MAX) = N'{"args":{"newValue":"A","oldValue":"C","targetColumn":"abc","clientColumnChanged":"abc"},"query":{"filters":[{"column":{"name":"location_id"},"operator":"=","values":10},{"column":{"name":"master_item_id"},"operator":"=","values":86},{"column":{"name":"master_item_id"},"operator":"=","values":86}],"columns":[{"name":"item_no"},{"name":"name"},{"name":"abc"}],"series":[]}}',
--	@user_id INT = 1,
--	@debug BIT = 0


-- declaring some variables and then parsing the query string
	DECLARE  @series de_qb_data_series,
	@filters de_qb_filters, @columns de_qb_columns, @table_joins NVARCHAR(MAX),  @where_filter NVARCHAR(MAX), @sql NVARCHAR(MAX),@q_createdObj pjsonData


	-- only for debugging
    DECLARE @msg NVARCHAR(500)
    IF @debug = 1 PRINT 'VALIDATING JSON:'
	IF ISJSON(@query) = 0
	BEGIN
		SET @msg = 'Invalid JSON request'
		SELECT dbo.json_error(@msg) as result
		EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		RETURN
	END



	-- I recommend that you comment out the select queries below each parsing to see what is stored in the variables

	-- parsing filters
--	INSERT INTO @filters
--	SELECT * FROM [dbo].[fn_de_qp_filters](JSON_QUERY(@query, '$.query'))
--	SELECT * FROM @filters

	--parsing columns
--	INSERT INTO @columns
--	SELECT * FROM [dbo].[fn_de_qp_columns](JSON_QUERY(@query, '$.query'))
--	SELECT * FROM @columns

	--creating temp table that in my case I call item_list. Adjust the name and columns to your need based on what is in your view/grid
	IF OBJECT_ID('tempdb..#item_list') IS NOT NULL
	DROP TABLE #item_list
	CREATE TABLE #item_list([item_id] INT)


	-- Now I use the procedure [dbo].[de_qb_sql_specific_columns_select] to give me the column item_id (dbo.data_element_ref_columns) using the @query and insert into the ids into the temp table. Here you have to change the columns you are inserting into based on the columns
	-- in the table you created.

    EXEC dbo.de_qb_sql_specific_columns_select @query=@query, @user_id= @user_id, @debug = @debug, @columns_comma_list = 'item_id', @distinct = 1, @sql = @sql OUT

    SET @sql = 'INSERT INTO #item_list (item_id) ' + @sql + ';'

    EXEC sp_executesql @sql -- this will run what is in the sql string. So in this case we are inserting into the item_list


	-- Finally we update the forecast table, making sure that the valid field is set to 0 for only the item that was updated.


        UPDATE f
        SET f.valid = 0
        FROM dbo.forecasts f
        INNER JOIN #item_list il ON f.item_id = il.item_id


	----------------------------------------------------------------------
	-- refreshing and returning the results
	SELECT '{"result": "OK", "caption": "ABC was updated and Forecast made Invalid"}' AS result


    END TRY
    BEGIN CATCH
		    DECLARE @sp_name NVARCHAR(500)
		    SELECT @sp_name = s.[name]+'.'+OBJECT_NAME(@@PROCID), @msg = 'Action could not be executed:'+ERROR_MESSAGE()
		    FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
		    WHERE OBJECT_ID = @@PROCID
            EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		    SELECT dbo.json_error('Action could not be executed') AS result
    END CATCH;
END

{
  "insert": {
    "preInsertQuery": {
      "columns": [
        {
          "name": "location"
        }
      ]
    },
  "enabled": true,
  "requiredColumns": [
    "location"
    ],
  "action": "add_location"
},
  "row": {
    "autoRefresh": true,
    "refreshWholeGrid": true
  },
  "name": "new-locations"
}

CREATE PROCEDURE [dbo].[de_act_example_e]
-- the following parameters must be included when creating all 'workspace' actions that are used in dbo.de_action_sps
(
    @query NVARCHAR(MAX),
    @user_id INT,
	@debug BIT = 0
)
AS
BEGIN

	SET NOCOUNT ON
	BEGIN TRY

	-- Uncomment this below for debugging and comment out all above. Remember you must use the query from your action
	-- which you find by running your action in the system and catching the action from watching live data in Extended Events

--	DECLARE @query NVARCHAR(MAX) = N'{"args":{"createdObj":{"location":"new location","location_id":10,"rowNodeId":23,"inserted":true,"submitting":true,"isFirstLine":true}},"query":{"filters":[],"columns":[{"name":"location"}],"series":[]}}',
--	@user_id INT = 1,
--	@debug BIT = 0


-- declaring some variables and then parsing the query string
	DECLARE  @series de_qb_data_series,
	@filters de_qb_filters, @columns de_qb_columns, @table_joins NVARCHAR(MAX),  @where_filter NVARCHAR(MAX), @sql NVARCHAR(MAX)



	-- only for debugging
    DECLARE @msg NVARCHAR(500)
    IF @debug = 1 PRINT 'VALIDATING JSON:'
	IF ISJSON(@query) = 0
	BEGIN
		SET @msg = 'Invalid JSON request'
		SELECT dbo.json_error(@msg) as result
		EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		RETURN
	END

	--parsing query

	-- I recommend uncommenting out the select query below this comment. There you will see what you need to catch from the system.
	-- SELECT * FROM @q_createdObj


	-- Declaring variables to store the new location name and the highest location id in the location table
	-- so that we make sure that we add higher location id into the table.
	DECLARE @max_location_id INT,@new_location NVARCHAR(255), @new_location_id INT

	-- storing the new location name that was entered in the system into the @new_location variable.
	SET @new_location = JSON_VALUE(@query, '$.args.createdObj.location')
	-- finding highest location id in the location table
	SET @max_location_id = (SELECT MAX(id) FROM dbo.locations)

	-- The new location id will get one higher integer number than the highest location id
	SET @new_location_id = @max_location_id + 1

	-- Adding the new location into the locations table
	INSERT INTO dbo.locations
	VALUES(@new_location_id,'N0' + CAST(@new_location_id AS NVARCHAR(100)), @new_location,'store',NULL,GETDATE(),GETDATE())


	----------------------------------------------------------------------
	-- refreshing and returning the results
	SELECT '{"result": "OK", "caption": "Location Added", "data": ["refreshedRow"]}' AS result
	SELECT @new_location_id AS location_id, @new_location AS location

    END TRY
    BEGIN CATCH
		    DECLARE @sp_name NVARCHAR(500)
		    SELECT @sp_name = s.[name]+'.'+OBJECT_NAME(@@PROCID), @msg = 'Action could not be executed:'+ERROR_MESSAGE()
		    FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
		    WHERE OBJECT_ID = @@PROCID
            EXEC core.event_insert @task_name = @@PROCID, @event_type_id = 106, @event_text = @msg
		    SELECT dbo.json_error('Action could not be executed') AS result
    END CATCH;
END
GO