RG SQL

rg.sql

{"posts":[{"a":1,"b":2,"__id":"jgm1lltd"},{"a":4,"b":3,"__id":"jgm1mzv5"},{"a":10,"b":3,"__id":"jgm1we7z"}]}

ALTER PROCEDURE [dbo].[SP_GetSalesReport]
    -- Add the parameters for the stored procedure here
    @agencyId BIGINT,
    @from DATETIME,
    @to DATETIME,
    @agentName VARCHAR(MAX)
AS
BEGIN
    SELECT *
    FROM
    (
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                b.CREATED,
                agc.PREFIX,
                b.RUNNING_NO,
                b.OFFER_TYPE,
                b.DISPLAY_BOOKING_NO,
                ag.FULL_NAME AS AGENT_NAME,
                CAST(1 AS BIT) AS ONLINE,
                CAST(0 AS BIT) AS OFFLINE,
                b.AGENCY_FEE
         FROM dbo.a_booking b
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
             LEFT JOIN dbo.a_account ag
                 ON b.AGENT_ID = ag.ROW_ID
             LEFT JOIN dbo.a_agency agc
                 ON b.AGENCY_ID = agc.ROW_ID
         WHERE b.BOOKING_TYPE = 'BOOKING_PAID'
               AND b.AGENCY_ID = @agencyId
               AND b.BOOKING_MODE = 0
               AND b.LAST_UPDATED >= @from
               AND b.LAST_UPDATED <= @to
               AND ag.FULL_NAME = (CASE
                                       WHEN @agentName = '' THEN
                                           ag.FULL_NAME
                                       ELSE
                                           @agentName
                                   END
                                  ))
        UNION
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                b.CREATED,
                '' AS PREFIX,
                0 AS RUNNING_NO,
                b.OFFER_TYPE,
                b.DISPLAY_BOOKING_NO,
                ag.FULL_NAME AS AGENT_NAME,
                CAST(0 AS BIT) AS ONLINE,
                CAST(1 AS BIT) AS OFFLINE,
                b.AGENCY_FEE
         FROM dbo.a_booking b
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
             LEFT JOIN dbo.a_account ag
                 ON b.AGENT_ID = ag.ROW_ID
         WHERE (
                   b.BOOKING_TYPE <> 'MANUAL'
                   OR b.BOOKING_TYPE <> 'PENDING_DOCUMENTS_APPROVAL'
               )
               AND b.AGENCY_ID = @agencyId
               AND b.BOOKING_MODE = 1
               AND b.LAST_UPDATED >= @from
               AND b.LAST_UPDATED <= @to
               AND ag.FULL_NAME = (CASE
                                       WHEN @agentName = '' THEN
                                           ag.FULL_NAME
                                       ELSE
                                           @agentName
                                   END
                                  ))
    ) AS TB1
    ORDER BY CREATED;
END;

-- 12/07/2018 Add new column to table a_booking
ALTER TABLE dbo.a_booking
ADD TENANCY_CHARGE_PAY_BY INT NULL

-- 13/07/2018 Add new column to table a_booking_manual
ALTER TABLE dbo.a_booking_manual
ADD TENANCY_CHARGE_PAY_BY INT NULL