The Query panel contains an SQL query intended to collect data for calculating a discount or markup:

The panel contains a multi-line text box to enter an SQL query.

All necessary information required for calculation of a discount or markup is retrieved from the database. SQL queries in the Transact SQL language determine which data must be used to calculate a certain discount or markup.

The Query context menu contains the Test query command. The command is available if the Query panel contains a query text. Test queries check query texts for errors. Then test reports are displayed.

Before testing a query the user should specify filter parameters for each filter step. If a query is successfully implemented, the success message will be returned.

If any errors are detected in a query, the error message will be displayed.

Here is a list of parameters which can be used in queries:

    CREATE TABLE #Guest(
        g_ID                UNIQUEIDENTIFIER                -- Guest tab ID
    )
    
    CREATE TABLE #Orders(
        ordr_ID             UNIQUEIDENTIFIER PRIMARY KEY,   -- Order ID
        ordr_orst_ID        INT,                            -- Order status ID
        ordr_Date           DATETIME,                       -- Order date
        ordr_sprv_ID        UNIQUEIDENTIFIER NULL,          -- Order promotion category ID
        ordr_Time           INT,                            -- 
        ordr_WeekTime       INT                             --
    )

    CREATE TABLE #OrderItems ( -- order items
        -- information fields and fields for adding field options
        i_ID                UNIQUEIDENTIFIER PRIMARY KEY,   -- Order item ID
        i_ordr_ID           UNIQUEIDENTIFIER,               -- Order ID
        i_PriceOriginal     NUMERIC(18,4),                  -- Price without discounts and markups
        -- information fields
        i_Date              DATETIME,                       -- Order submission date
        i_pcit_ID           UNIQUEIDENTIFIER,               -- Bill item ID
        i_LastPriceDiscount NUMERIC(18,4) NULL,             -- Previously calculated discount. It is used if only i_pcit_ID is not NULL
        i_LastPriceMargin   NUMERIC(18,4) NULL,             -- Previously calculated markup. It is used if only i_pcit_ID is not NULL
        i_LastPrice         NUMERIC(18,4) NULL,             -- Price adjusted for the previously discount or markup. It is used if only i_pcit_ID is not NULL
        -- information fields, configurable fields and fields for adding field options
        i_master_ID         UNIQUEIDENTIFIER,               -- Parent item ID
        i_mitm_ID           UNIQUEIDENTIFIER,               -- Menu item ID
        i_slgr_ID           UNIQUEIDENTIFIER,               -- Sales department ID
        i_mvtp_ID           UNIQUEIDENTIFIER,               -- Menu item of measurement ID
        i_Order             INT,                            -- Index
        i_Count             INT,                            -- Quantity
        i_Volume            NUMERIC(18,6),                  -- Volume
        i_PriceDiscount     NUMERIC(18,4) DEFAULT 0,        -- Discount
        i_PriceMargin       NUMERIC(18,4) DEFAULT 0,        -- Markup
        i_VAT               NUMERIC(18,3) DEFAULT 0,        -- VAT rate
        i_SeatNumber        INT NULL,                       -- Seat number
        i_Comment           NVARCHAR(MAX),                  -- Comment
        -- control fields
        i_ModifyAction      INT DEFAULT 1,                  -- 1 - add, 2 - edit, 3 - delete
        -- service fields
        i_Price AS (i_PriceOriginal - i_PriceDiscount 
            + i_PriceMargin),                               -- Price
        i_ForUpdate         BIT DEFAULT 0,                  -- Denotes that the item needs to be recalculated
        i_IsPayed           BIT DEFAULT 0                   -- Denotes that the item has been paid
    )

    CREATE TABLE #OrderTimeItems( -- order timed service
        -- informational fields
        t_ID                UNIQUEIDENTIFIER PRIMARY KEY,   -- ID of timed service charge in order
        t_orti_ID           UNIQUEIDENTIFIER,               -- Order timed service ID
        t_ordr_ID           UNIQUEIDENTIFIER,               -- Order ID 
        t_plac_ID           UNIQUEIDENTIFIER,               -- Table ID
        t_pcit_ID           UNIQUEIDENTIFIER,               -- Bill item ID
        t_LastPriceDiscount NUMERIC(18,4) NULL,             -- Previously calculated discount. It is used if only i_pcit_ID is not NULL
        t_LastPriceMargin   NUMERIC(18,4) NULL,             -- Previously calculated markup. It is used if only i_pcit_ID is not NULL
        t_LastPrice         NUMERIC(18,4) NULL,             -- Price adjusted for the previously discount or markup. It is used if only i_pcit_ID is not NULL
        t_PriceOriginal     NUMERIC(18,4),                  -- Price without discounts and markups
        -- informational and configurable fields
        t_master_ID         UNIQUEIDENTIFIER,               -- Parent item ID
        t_mitm_ID           UNIQUEIDENTIFIER,               -- Menu item ID
        t_slgr_ID           UNIQUEIDENTIFIER,               -- Sales department ID
        t_mvtp_ID           UNIQUEIDENTIFIER,               -- Menu item of measurement ID
        t_Order             INT,                            -- Idex
        t_DateBegin         DATETIME,                       -- Start date 
        t_Duration          INT,                            -- Duration
        t_PriceDiscount     NUMERIC(18,4) DEFAULT 0,        -- Discount
        t_PriceMargin       NUMERIC(18,4) DEFAULT 0,        -- Markup
        t_VAT               NUMERIC(18,3) DEFAULT 0,        -- VAT rate
        -- control fields
        t_ModifyAction      INT DEFAULT 1,                  -- 1 - add, 2 - edit, 3 - delete
        -- service fields
        t_Price AS (t_PriceOriginal - t_PriceDiscount + t_PriceMargin), -- Price
        t_ForUpdate         BIT DEFAULT 0,                  -- Denotes that the item needs to be recalculated
        t_IsPayed           BIT DEFAULT 0                   -- Denotes that the item has been paid
    )

    CREATE TABLE #GuestDiscountPointOperations( -- points
        p_ID                UNIQUEIDENTIFIER PRIMARY KEY,   -- Record ID
        p_dspt_ID           UNIQUEIDENTIFIER,               -- Point type ID
        p_Value             INT DEFAULT 0                   -- Number of points
    )