RG SQL

rg.sql

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

ALTER TABLE dbo.a_claim
ADD AGENT_PERCENTAGE DECIMAL NULL

ALTER TABLE dbo.a_claim
ADD CO_AGENT_PERCENTAGE DECIMAL NULL

-- 04/07/2018 Add new column to table a_rentalcollection
ALTER TABLE dbo.a_rentalcollection
ADD STATUS VARCHAR(150) NULL,
PAYMENT_METHOD INT NULL

UPDATE dbo.a_rentalcollection
SET STATUS = 'ONGOING'
WHERE STATUS IS NULL

UPDATE dbo.a_rentalcollection
SET PAYMENT_METHOD = 1 /* DIRECT DEBIT */
WHERE PAYMENT_METHOD IS NULL

-- Summary report
SELECT COUNT(*) AS NUM_LANDLORD
FROM
(
    SELECT LANDLORD_ID
    FROM dbo.a_booking
    WHERE MONTH(CREATED) >= 2
          AND YEAR(CREATED) = YEAR(GETDATE())
          AND OFFER_TYPE = 'BOOKING'
          AND AGENCY_ID NOT IN ( 15, 30 )
    GROUP BY LANDLORD_ID
) AS TB;

SELECT COUNT(*) AS NUM_VENDOR
FROM
(
    SELECT LANDLORD_ID
    FROM dbo.a_booking
    WHERE MONTH(CREATED) >= 2
          AND YEAR(CREATED) = YEAR(GETDATE())
          AND OFFER_TYPE = 'PURCHASE'
          AND AGENCY_ID NOT IN ( 15, 30 )
    GROUP BY LANDLORD_ID
) AS TB;

SELECT COUNT(*) AS NUM_TENANT
FROM
(
    SELECT TENANT_ID
    FROM dbo.a_booking
    WHERE MONTH(CREATED) >= 2
          AND YEAR(CREATED) = YEAR(GETDATE())
          AND OFFER_TYPE = 'BOOKING'
          AND AGENCY_ID NOT IN ( 15, 30 )
    GROUP BY TENANT_ID
) AS TB;

SELECT COUNT(*) AS NUM_PURCHASER
FROM
(
    SELECT TENANT_ID
    FROM dbo.a_booking
    WHERE MONTH(CREATED) >= 2
          AND YEAR(CREATED) = YEAR(GETDATE())
          AND OFFER_TYPE = 'PURCHASE'
          AND AGENCY_ID NOT IN ( 15, 30 )
    GROUP BY TENANT_ID
) AS TB;

SELECT COUNT(*) AS JUNE_NEW_AGENT
FROM dbo.a_account
WHERE ACCNT_TYPE_CD = 'Agent'
      AND MONTH(CREATED) = MONTH(GETDATE())
      AND YEAR(CREATED) = YEAR(GETDATE())
      AND AGENCY_ID NOT IN ( 15, 30 );

SELECT COUNT(*) AS JUNE_NEW_ONLINE_BOOKING
FROM dbo.a_booking
WHERE BOOKING_MODE = 0
      AND MONTH(CREATED) = MONTH(GETDATE())
      AND YEAR(CREATED) = YEAR(GETDATE())
      AND AGENCY_ID NOT IN ( 15, 30 );

SELECT COUNT(*) AS JUNE_NEW_OFFLINE_BOOKING
FROM dbo.a_booking
WHERE BOOKING_MODE = 1
      AND MONTH(CREATED) = MONTH(GETDATE())
      AND YEAR(CREATED) = YEAR(GETDATE())
      AND AGENCY_ID NOT IN ( 15, 30 );

SELECT SUM(BOOKING_FEE) AS JUNE_NEW_ONLINE_BOOKING_BOOKINGFEE
FROM dbo.a_booking
WHERE BOOKING_MODE = 0
      AND MONTH(CREATED) = MONTH(GETDATE())
      AND AGENCY_ID NOT IN ( 15, 30 )
      AND YEAR(CREATED) = YEAR(GETDATE());


SELECT SUM((EARNEST_DEPOSIT / 100) * PAYMENT_AMOUNT) AS JUNE_NEW_OFFLINE_BOOKING_EARNEST
FROM dbo.a_booking
WHERE BOOKING_MODE = 1
      AND MONTH(CREATED) = MONTH(GETDATE())
      AND AGENCY_ID NOT IN ( 15, 30 )
      AND YEAR(CREATED) = YEAR(GETDATE());

ALTER PROCEDURE [dbo].[SP_Admin_PendingCounts]
    -- Add the parameters for the stored procedure here
    @agencyId BIGINT
AS
BEGIN
    SELECT
        (
            SELECT COUNT(*)
            FROM dbo.a_booking B
            WHERE B.CO_BROKE_SCENARIO <> 'NONE'
                  AND (
                          (
                              B.AGENCY_ID = @agencyId
                              AND B.BOOKING_TYPE = 'PENDING_ADMIN'
                          )
                          OR (
                                 B.CO_BROKE_AGENCY_ID = @agencyId
                                 AND B.BOOKING_TYPE = 'PENDING_COBROKE_ADMIN'
                             )
                      )
        ) AS CoAgencies,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking B
            WHERE B.AGENCY_ID = @agencyId
                  AND B.BOOKING_TYPE = 'PENDING_VERIFICATION'
        ) AS Offlines,
        (
            SELECT SUM(TB.CLAIM)
            FROM
            (
                SELECT COUNT(*) AS CLAIM
                FROM dbo.a_claim C
                    JOIN dbo.a_booking B
                        ON C.BOOKING_ID = B.ROW_ID
                WHERE B.AGENCY_ID = @agencyId
                      AND C.STATUS = 'SUBMITTED'
            ) AS TB
        ) AS Claims,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking BM
                JOIN dbo.a_claim C
                    ON C.BOOKING_ID = BM.ROW_ID
            WHERE BM.AGENCY_ID = @agencyId
                  AND BM.BOOKING_MODE = 1 -- OFFLINE BOOKING
                  AND BM.BOOKING_TYPE = 'PENDING_DOCUMENTS_APPROVAL'
        ) AS OfflineDocs;

END;

ALTER PROCEDURE [dbo].[SP_Dsb_AdminDeals] @agencyId BIGINT
AS
BEGIN
    SELECT
        (
            SELECT COUNT(*)
            FROM
            (
                (SELECT B.ROW_ID
                 FROM dbo.a_booking B
                 WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                       AND MONTH(B.CREATED) = MONTH(GETDATE())
                       AND B.BOOKING_TYPE = 'BOOKING_PAID'
					   AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
                       AND B.AGENCY_ID = @agencyId)
                UNION
                (SELECT MB.ROW_ID
                 FROM dbo.a_booking MB
                 WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                       AND MONTH(MB.CREATED) = MONTH(GETDATE())
                       AND MB.BOOKING_TYPE = 'ACCEPT_ADMIN'
					   AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
                       AND MB.AGENCY_ID = @agencyId)
            ) AS TB1
        ) AS CLOSED,
        (
            SELECT COUNT(*)
            FROM
            (
                (SELECT B.ROW_ID
                 FROM dbo.a_booking B
                 WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                       AND MONTH(B.CREATED) = MONTH(GETDATE())
                       AND B.BOOKING_TYPE <> 'NEW'
					   AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
                       AND B.AGENCY_ID = @agencyId)
                UNION
                (SELECT MB.ROW_ID
                 FROM dbo.a_booking MB
                 WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                       AND MONTH(MB.CREATED) = MONTH(GETDATE())
                       AND MB.BOOKING_TYPE <> 'MANUAL'
                       AND MB.BOOKING_TYPE IS NOT NULL
					   AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
                       AND MB.AGENCY_ID = @agencyId)
            ) AS TB2
        ) AS CREATED;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_AdminTopCases]
    @agencyId BIGINT,
    @month INT,
    @year INT
AS
BEGIN
    SELECT TB1.ROW_ID,
           TB1.FULL_NAME,
           SUM(TB1.VALUE) AS VALUE
    FROM
    (
        SELECT A.ROW_ID,
               A.FULL_NAME,
               COUNT(B.ROW_ID) AS VALUE
        FROM dbo.a_booking B
            JOIN dbo.a_account A
                ON A.ROW_ID = B.AGENT_ID
        WHERE MONTH(B.CREATED) = @month
              AND YEAR(B.CREATED) = @year
              AND B.AGENCY_ID = @agencyId
			  AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
        GROUP BY A.ROW_ID,
                 A.FULL_NAME
        UNION
        SELECT A.ROW_ID,
               A.FULL_NAME,
               COUNT(MB.ROW_ID) AS VALUE
        FROM dbo.a_booking MB
            JOIN dbo.a_account A
                ON A.ROW_ID = MB.AGENT_ID
        WHERE MONTH(MB.CREATED) = @month
              AND YEAR(MB.CREATED) = @year
              AND MB.AGENCY_ID = @agencyId
			  AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
        GROUP BY A.ROW_ID,
                 A.FULL_NAME
    ) AS TB1
    GROUP BY TB1.ROW_ID,
             TB1.FULL_NAME
    ORDER BY VALUE DESC;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_AdminTopSales]
    @agencyId INT,
    @month INT,
    @year INT
AS
BEGIN
    SELECT TB1.ROW_ID,
           TB1.FULL_NAME,
           SUM(TB1.VALUE) AS VALUE
    FROM
    (
        SELECT A.ROW_ID,
               A.FULL_NAME,
               SUM(B.AGENCY_FEE) AS VALUE
        FROM dbo.a_booking B
            JOIN dbo.a_account A
                ON A.ROW_ID = B.AGENT_ID
        WHERE MONTH(B.CREATED) = @month
              AND YEAR(B.CREATED) = @year
              AND B.BOOKING_TYPE = 'BOOKING_PAID'
			  AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
              AND B.AGENCY_ID = @agencyId
        GROUP BY A.ROW_ID,
                 A.FULL_NAME
        UNION
        SELECT A.ROW_ID,
               A.FULL_NAME,
               SUM(MB.AGENCY_FEE) AS VALUE
        FROM dbo.a_booking MB
            JOIN dbo.a_account A
                ON A.ROW_ID = MB.AGENT_ID
        WHERE MONTH(MB.CREATED) = @month
              AND YEAR(MB.CREATED) = @year
              AND MB.BOOKING_TYPE = 'ACCEPT_ADMIN'
			  AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
              AND MB.AGENCY_ID = @agencyId
        GROUP BY A.ROW_ID,
                 A.FULL_NAME
    ) AS TB1
    GROUP BY TB1.ROW_ID,
             TB1.FULL_NAME
    ORDER BY VALUE DESC;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_RootAdminActiveAgents]
AS
BEGIN
    SELECT COUNT(*) AS ACTIVE_AGENTS
    FROM dbo.a_account
    WHERE AGENCY_ID != 30
          AND AGENCY_ID != 15
          AND ACCNT_TYPE_CD = 'Agent'
          AND ACCNT_STATUS = 'ACTIVE'
          AND LAST_LOGIN <= GETDATE()
          AND LAST_LOGIN >= GETDATE() - 30;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_RootAdminDeals]
AS
BEGIN
    SELECT CLOSED.TOTAL_ROW AS CLOSED,
           CREATED.TOTAL_ROW AS CREATED,
           (CLOSED.AGENCY_FEE + CREATED.AGENCY_FEE) AS TOTAL_AGENCY_FEE
    FROM
    (
        SELECT COUNT(*) AS TOTAL_ROW,
               SUM(TB1.AGENCY_FEE) AS AGENCY_FEE
        FROM
        (
            (SELECT B.ROW_ID,
                    B.AGENCY_FEE
             FROM dbo.a_booking B
             WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                   AND MONTH(B.CREATED) = MONTH(GETDATE())
                   AND B.BOOKING_TYPE = 'BOOKING_PAID'
                   AND B.AGENCY_ID != 30
                   AND B.AGENCY_ID != 15
                   AND B.BOOKING_MODE = 0)
            UNION
            (SELECT MB.ROW_ID,
                    MB.AGENCY_FEE
             FROM dbo.a_booking MB
             WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                   AND MONTH(MB.CREATED) = MONTH(GETDATE())
                   AND MB.BOOKING_TYPE = 'ACCEPT_ADMIN'
                   AND MB.AGENCY_ID != 30
                   AND MB.AGENCY_ID != 15
                   AND MB.BOOKING_MODE = 1)
        ) AS TB1
    ) AS CLOSED ,
    (
        SELECT COUNT(*) AS TOTAL_ROW,
               0 AS AGENCY_FEE
        FROM
        (
            (SELECT B.ROW_ID,
                    B.AGENCY_FEE
             FROM dbo.a_booking B
             WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                   AND MONTH(B.CREATED) = MONTH(GETDATE())
                   AND B.BOOKING_TYPE <> 'NEW'
                   AND B.AGENCY_ID != 30
                   AND B.AGENCY_ID != 15
                   AND B.BOOKING_MODE = 0)
            UNION
            (SELECT MB.ROW_ID,
                    MB.AGENCY_FEE
             FROM dbo.a_booking MB
             WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                   AND MONTH(MB.CREATED) = MONTH(GETDATE())
                   AND MB.BOOKING_TYPE <> 'MANUAL'
                   AND MB.BOOKING_TYPE IS NOT NULL
                   AND MB.AGENCY_ID != 30
                   AND MB.AGENCY_ID != 15
                   AND MB.BOOKING_MODE = 1)
        ) AS TB2
    ) AS CREATED;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_RootAdminTopCaseAgency]
AS
BEGIN
    SELECT TB1.ROW_ID,
           TB1.AGENCY_NAME AS FULL_NAME,
           SUM(TB1.VALUE) AS VALUE
    FROM
    (
        SELECT A.ROW_ID,
               A.AGENCY_NAME,
               COUNT(B.ROW_ID) AS VALUE
        FROM dbo.a_booking B
            JOIN dbo.a_agency A
                ON A.ROW_ID = B.AGENCY_ID
        WHERE MONTH(B.CREATED) = MONTH(GETDATE())
              AND YEAR(B.CREATED) = YEAR(GETDATE())
              AND B.AGENCY_ID != 15
              AND B.AGENCY_ID != 30
        GROUP BY A.ROW_ID,
                 A.AGENCY_NAME
    ) AS TB1
    GROUP BY TB1.ROW_ID,
             TB1.AGENCY_NAME
    ORDER BY VALUE DESC;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_RootAdminTopSaleAgency]
AS
BEGIN
    SELECT TB1.ROW_ID,
           TB1.AGENCY_NAME AS FULL_NAME,
           SUM(TB1.VALUE) AS VALUE
    FROM
    (
        SELECT A.ROW_ID,
               A.AGENCY_NAME,
               SUM(B.AGENCY_FEE) AS VALUE
        FROM dbo.a_booking B
            JOIN dbo.a_agency A
                ON A.ROW_ID = B.AGENCY_ID
        WHERE MONTH(B.CREATED) = MONTH(GETDATE())
              AND YEAR(B.CREATED) = YEAR(GETDATE())
              AND B.BOOKING_TYPE = 'BOOKING_PAID'
              AND B.AGENCY_ID != 15
              AND B.AGENCY_ID != 30
              AND B.BOOKING_MODE = 0
        GROUP BY A.ROW_ID,
                 A.AGENCY_NAME
        UNION
        SELECT A.ROW_ID,
               A.AGENCY_NAME,
               SUM(MB.AGENCY_FEE) AS VALUE
        FROM dbo.a_booking MB
            JOIN dbo.a_agency A
                ON A.ROW_ID = MB.AGENCY_ID
        WHERE MONTH(MB.CREATED) = MONTH(GETDATE())
              AND YEAR(MB.CREATED) = YEAR(GETDATE())
              AND MB.BOOKING_TYPE = 'ACCEPT_ADMIN'
              AND MB.AGENCY_ID != 15
              AND MB.AGENCY_ID != 30
              AND MB.BOOKING_MODE = 1
        GROUP BY A.ROW_ID,
                 A.AGENCY_NAME
    ) AS TB1
    GROUP BY TB1.ROW_ID,
             TB1.AGENCY_NAME
    ORDER BY VALUE DESC;
END;

ALTER PROCEDURE [dbo].[SP_Dsb_RootAdminTotalAgents]
AS
BEGIN
    SELECT COUNT(*) AS TOTAL_AGENTS
    FROM dbo.a_account
    WHERE AGENCY_ID != 30
          AND AGENCY_ID != 15
          AND ACCNT_TYPE_CD = 'Agent'
          AND ACCNT_STATUS != 'DELETE'
		  AND DELETE_FLG = 'false'
          AND CREATED <= GETDATE()
          AND CREATED >= GETDATE() - 30;
END;

ALTER PROCEDURE [dbo].[SP_GetAgencyAgent]
    -- Add the parameters for the stored procedure here
    @agencyId BIGINT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT acc.ROW_ID,
           acc.USERNAME,
           acc.FULL_NAME,
           acc.ID_NUM,
           acc.ACCNT_STATUS,
           acc.LAST_UPDATED,
           acc.AGENCY_ID,
           acc.DELETE_FLG,
           acc.ACCNT_TYPE_CD,
           acc.CREATED,
		   COUNT(abooking.ROW_ID) AS TOTAL_BOOKING
    FROM dbo.a_account acc
        LEFT JOIN dbo.a_booking abooking
            ON acc.ROW_ID = abooking.AGENT_ID
               OR acc.ROW_ID = abooking.CO_BROKE_AGENT_ID
    WHERE acc.DELETE_FLG = 0
          AND acc.ACCNT_STATUS != 'DELETED'
          AND acc.ACCNT_TYPE_CD = 'AGENT'
          AND acc.AGENCY_ID = @agencyId
    GROUP BY acc.ROW_ID,
             acc.USERNAME,
             acc.FULL_NAME,
             acc.ID_NUM,
             acc.ACCNT_STATUS,
             acc.LAST_UPDATED,
             acc.AGENCY_ID,
             acc.DELETE_FLG,
             acc.ACCNT_TYPE_CD,
             acc.CREATED
    ORDER BY acc.CREATED DESC;
END;

ALTER PROCEDURE [dbo].[SP_GetAgentPendingResponds]
    -- Add the parameters for the stored procedure here
    @agentId BIGINT
AS
BEGIN
    SELECT
        (
            SELECT COUNT(*)
            FROM dbo.a_booking
            WHERE (
                      BOOKING_TYPE = 'SENT_TO_OWNER'
                      OR BOOKING_TYPE = 'ACCEPT_TENANT'
                  )
                  AND OFFER_TYPE = 'PURCHASE'
                  AND BOOKING_MODE = 0 -- ONLINE BOOKING
                  AND AGENT_ID = @agentId
        ) AS VENDORS,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking
            WHERE (
                      BOOKING_TYPE = 'SENT_TO_TENANT'
                      OR BOOKING_TYPE = 'ACCEPT_OWNER'
                  )
                  AND OFFER_TYPE = 'PURCHASE'
                  AND BOOKING_MODE = 0 -- ONLINE BOOKING
                  AND AGENT_ID = @agentId
        ) AS PURCHASERS,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking
            WHERE (
                      BOOKING_TYPE = 'SENT_TO_OWNER'
                      OR BOOKING_TYPE = 'ACCEPT_TENANT'
                  )
                  AND OFFER_TYPE = 'BOOKING'
                  AND BOOKING_MODE = 0 -- ONLINE BOOKING
                  AND AGENT_ID = @agentId
        ) AS LANDLORDS,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking
            WHERE (
                      BOOKING_TYPE = 'SENT_TO_TENANT'
                      OR BOOKING_TYPE = 'ACCEPT_OWNER'
                  )
                  AND OFFER_TYPE = 'BOOKING'
                  AND BOOKING_MODE = 0 -- ONLINE BOOKING
                  AND AGENT_ID = @agentId
        ) AS TENANTS,
        (
            SELECT COUNT(*)
            FROM dbo.a_booking
            WHERE BOOKING_TYPE = 'ACCEPT_OWNER_TENANT'
                  AND PAYMENT_STATUS = 'PENDING'
                  AND BOOKING_MODE = 0 -- ONLINE BOOKING
                  AND AGENT_ID = @agentId
        ) AS PAYMENT_COLLECTIONS;
END;

ALTER PROCEDURE [dbo].[SP_GetCustomerDataByAgency]
    -- Add the parameters for the stored procedure here
    @agencyId INT = 0,
    @from DATETIME,
    @to DATETIME
AS
BEGIN
    SELECT *
    FROM
    (
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                ll.FULL_NAME,
                ll.EMAIL_ADDR,
                ll.MOBILE_NO,
                CASE
                    WHEN b.OFFER_TYPE = 'PURCHASE' THEN
                        'VENDOR'
                    ELSE
                        'LANDLORD'
                END AS TYPE
         FROM dbo.a_booking b
             JOIN dbo.a_account ll
                 ON b.LANDLORD_ID = ll.ROW_ID
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
         WHERE b.AGENCY_ID = @agencyId
               AND b.CREATED >= @from
               AND b.CREATED <= @to)
        UNION
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                ll.FULL_NAME,
                ll.EMAIL_ADDR,
                ll.MOBILE_NO,
                CASE
                    WHEN b.OFFER_TYPE = 'PURCHASE' THEN
                        'PURCHASER'
                    ELSE
                        'TENANT'
                END AS TYPE
         FROM dbo.a_booking b
             JOIN dbo.a_account ll
                 ON b.TENANT_ID = ll.ROW_ID
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
         WHERE b.AGENCY_ID = @agencyId
               AND b.CREATED >= @from
               AND b.CREATED <= @to)
    --UNION
    --(SELECT bm.ROW_ID,
    --        u.DISPLAY_NAME,
    --        bm.LNAME,
    --        bm.LEMAIL_ADDR,
    --        bm.LMOBILE_NO,
    --        CASE
    --            WHEN bm.OFFER_TYPE = 'PURCHASE' THEN
    --                'VENDOR'
    --            ELSE
    --                'LANDLORD'
    --        END AS TYPE
    -- FROM dbo.a_booking bm
    --     JOIN dbo.a_unit u
    --         ON bm.UNIT_ID = u.ROW_ID
    -- WHERE bm.AGENCY_ID = @agencyId
    --       AND bm.CREATED >= @from
    --       AND bm.CREATED <= @to)
    --UNION
    --(SELECT bm.ROW_ID,
    --        u.DISPLAY_NAME,
    --        bm.TNAME,
    --        bm.TEMAIL_ADDR,
    --        bm.TMOBILE_NO,
    --        CASE
    --            WHEN bm.OFFER_TYPE = 'PURCHASE' THEN
    --                'PURCHASER'
    --            ELSE
    --                'TENANT'
    --        END AS TYPE
    -- FROM dbo.a_booking bm
    --     JOIN dbo.a_unit u
    --         ON bm.UNIT_ID = u.ROW_ID
    -- WHERE bm.AGENCY_ID = @agencyId
    --       AND bm.CREATED >= @from
    --       AND bm.CREATED <= @to)
    ) AS TB1
    ORDER BY ROW_ID;
END;

ALTER PROCEDURE [dbo].[SP_GetDealsByAgent]
    -- Add the parameters for the stored procedure here
    @agentId BIGINT
AS
BEGIN
    SELECT
        (
            SELECT COUNT(*)
            FROM
            (
                (SELECT B.ROW_ID
                 FROM dbo.a_booking B
                 WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                       AND MONTH(B.CREATED) = MONTH(GETDATE())
                       AND B.BOOKING_TYPE = 'BOOKING_PAID'
					   AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
                       AND B.AGENT_ID = @agentId)
                UNION
                (SELECT MB.ROW_ID
                 FROM dbo.a_booking MB
                 WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                       AND MONTH(MB.CREATED) = MONTH(GETDATE())
                       AND MB.BOOKING_TYPE = 'ACCEPT_ADMIN'
					   AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
                       AND MB.AGENT_ID = @agentId)
            ) AS TB1
        ) AS CLOSED,
        (
            SELECT COUNT(*)
            FROM
            (
                (SELECT B.ROW_ID
                 FROM dbo.a_booking B
                 WHERE YEAR(B.CREATED) = YEAR(GETDATE())
                       AND MONTH(B.CREATED) = MONTH(GETDATE())
                       AND B.BOOKING_TYPE <> 'NEW'
					   AND B.BOOKING_MODE = 0 -- ONLINE BOOKING
                       AND B.AGENT_ID = @agentId)
                UNION
                (SELECT MB.ROW_ID
                 FROM dbo.a_booking MB
                 WHERE YEAR(MB.CREATED) = YEAR(GETDATE())
                       AND MONTH(MB.CREATED) = MONTH(GETDATE())
                       AND MB.BOOKING_TYPE <> 'MANUAL'
                       AND MB.BOOKING_TYPE IS NOT NULL
					   AND MB.BOOKING_MODE = 1 -- OFFLINE BOOKING
                       AND MB.AGENT_ID = @agentId)
            ) AS TB2
        ) AS CREATED;
END;

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,
                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,
                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;

ALTER PROCEDURE [dbo].[SP_Report_CustomerDataByMonthly]
    -- Add the parameters for the stored procedure here
    @agencyId BIGINT,
    @year INT,
    @month INT
AS
BEGIN
    SELECT *
    FROM
    (
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                ll.FULL_NAME,
                ll.EMAIL_ADDR,
                ll.MOBILE_NO,
                CASE
                    WHEN b.OFFER_TYPE = 'PURCHASE' THEN
                        'VENDOR'
                    ELSE
                        'LANDLORD'
                END AS TYPE
         FROM dbo.a_booking b
             JOIN dbo.a_account ll
                 ON b.LANDLORD_ID = ll.ROW_ID
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
         WHERE b.AGENCY_ID = @agencyId
               AND MONTH(b.CREATED) = @month
               AND YEAR(b.CREATED) = @year
               AND b.BOOKING_TYPE = 'BOOKING_PAID'
               AND b.BOOKING_MODE = 0)
        UNION
        (SELECT b.ROW_ID,
                u.DISPLAY_NAME,
                ll.FULL_NAME,
                ll.EMAIL_ADDR,
                ll.MOBILE_NO,
                CASE
                    WHEN b.OFFER_TYPE = 'PURCHASE' THEN
                        'PURCHASER'
                    ELSE
                        'TENANT'
                END AS TYPE
         FROM dbo.a_booking b
             JOIN dbo.a_account ll
                 ON b.TENANT_ID = ll.ROW_ID
             JOIN dbo.a_unit u
                 ON b.UNIT_ID = u.ROW_ID
         WHERE b.AGENCY_ID = @agencyId
               AND MONTH(b.CREATED) = @month
               AND YEAR(b.CREATED) = @year
               AND b.BOOKING_TYPE = 'BOOKING_PAID'
               AND b.BOOKING_MODE = 0
         UNION
         SELECT mb.ROW_ID,
                u.DISPLAY_NAME,
                mb.LNAME AS FULL_NAME,
                mb.LEMAIL_ADDR AS EMAIL_ADDR,
                mb.LMOBILE_NO AS MOBILE_NO,
                CASE
                    WHEN mb.OFFER_TYPE = 'PURCHASE' THEN
                        'VENDOR'
                    ELSE
                        'LANDLORD'
                END AS TYPE
         FROM dbo.a_booking mb
             JOIN dbo.a_unit u
                 ON mb.UNIT_ID = u.ROW_ID
         WHERE mb.AGENCY_ID = @agencyId
               AND MONTH(mb.CREATED) = @month
               AND YEAR(mb.CREATED) = @year
               AND mb.BOOKING_TYPE = 'ACCEPT_ADMIN'
               AND mb.BOOKING_MODE = 1
         UNION
         SELECT mb.ROW_ID,
                u.DISPLAY_NAME,
                mb.TNAME AS FULL_NAME,
                mb.TEMAIL_ADDR AS EMAIL_ADDR,
                mb.TMOBILE_NO AS MOBILE_NO,
                CASE
                    WHEN mb.OFFER_TYPE = 'PURCHASE' THEN
                        'PURCHASER'
                    ELSE
                        'TENANT'
                END AS TYPE
         FROM dbo.a_booking mb
             JOIN dbo.a_unit u
                 ON mb.UNIT_ID = u.ROW_ID
         WHERE mb.AGENCY_ID = @agencyId
               AND MONTH(mb.CREATED) = @month
               AND YEAR(mb.CREATED) = @year
               AND mb.BOOKING_TYPE = 'ACCEPT_ADMIN'
               AND mb.BOOKING_MODE = 1)
    ) AS TB1
    ORDER BY ROW_ID;
END;

ALTER PROCEDURE [dbo].[SP_Report_MonthlyTotalCommsion]
    @agencyId BIGINT,
    @month INT,
    @year INT
AS
BEGIN
    SELECT TB1.ROW_ID,
           TB1.COMMISSION AS VALUE
    FROM
    (
        SELECT CL.ROW_ID,
               CL.COMMISSION
        FROM dbo.a_claim CL
            JOIN dbo.a_booking BK
                ON CL.BOOKING_ID = BK.ROW_ID
        WHERE CL.STATUS = 'VERIFIED'
              AND MONTH(CL.CREATED) = @month
              AND YEAR(CL.CREATED) = @year
              AND BK.AGENCY_ID = @agencyId
              AND BK.BOOKING_MODE = 0
        UNION
        SELECT CL.ROW_ID,
               CL.COMMISSION
        FROM dbo.a_claim CL
            JOIN dbo.a_booking MB
                ON CL.BOOKING_ID_MANUAL = MB.ROW_ID
        WHERE CL.STATUS = 'VERIFIED'
              AND MONTH(CL.CREATED) = @month
              AND YEAR(CL.CREATED) = @year
              AND MB.AGENCY_ID = @agencyId
              AND MB.BOOKING_MODE = 1
    ) AS TB1;

END;

ALTER PROCEDURE [dbo].[SP_Report_SalesByDaily]
    @year INT,
    @month INT,
    @agencyId BIGINT
AS
BEGIN
    SELECT TB1.POINT,
           SUM(TB1.ON_AGENCY_FEE) AS ON_AGENCY_FEE,
           SUM(TB1.OFF_AGENCY_FEE) AS OFF_AGENCY_FEE
    FROM
    (
        (SELECT B.ROW_ID,
                RIGHT('0' + CAST(DAY(T.CREATED) AS VARCHAR(10)), 2) + '-'
                + RIGHT('0' + CAST(MONTH(T.CREATED) AS VARCHAR(10)), 2) + '-' + CAST(YEAR(T.CREATED) AS VARCHAR(10)) AS POINT,
                B.AGENCY_FEE AS ON_AGENCY_FEE,
                0 AS OFF_AGENCY_FEE
         FROM dbo.a_booking B
             JOIN dbo.a_transaction T
                 ON T.BOOKING_ID = B.ROW_ID
         WHERE BOOKING_TYPE = 'BOOKING_PAID'
               AND YEAR(T.CREATED) = @year
               AND MONTH(T.CREATED) = @month
               AND B.AGENCY_ID = @agencyId
               AND B.BOOKING_MODE = 0)
        UNION
        (SELECT MB.ROW_ID,
                RIGHT('0' + CAST(DAY(MB.CREATED) AS VARCHAR(10)), 2) + '-'
                + RIGHT('0' + CAST(MONTH(MB.CREATED) AS VARCHAR(10)), 2) + '-' + CAST(YEAR(MB.CREATED) AS VARCHAR(10)) AS POINT,
                0 AS ON_AGENCY_FEE,
                MB.AGENCY_FEE AS OFF_AGENCY_FEE
         FROM dbo.a_booking MB
         WHERE BOOKING_TYPE = 'ACCEPT_ADMIN'
               AND YEAR(MB.CREATED) = @year
               AND MONTH(MB.CREATED) = @month
               AND MB.AGENCY_ID = @agencyId
               AND MB.BOOKING_MODE = 1)
    ) AS TB1
    GROUP BY TB1.POINT;
END;

ALTER PROCEDURE [dbo].[SP_Report_SalesByMonthly]
    -- Add the parameters for the stored procedure here
    @year INT,
    @agencyId BIGINT
AS
BEGIN
    SELECT TB1.POINT,
           SUM(TB1.ON_AGENCY_FEE) AS ON_AGENCY_FEE,
           SUM(TB1.OFF_AGENCY_FEE) AS OFF_AGENCY_FEE
    FROM
    (
        (SELECT B.ROW_ID,
                RIGHT('00' + CAST(MONTH(T.CREATED) AS VARCHAR(10)), 2) + '-' + CAST(YEAR(T.CREATED) AS VARCHAR(10)) AS POINT,
                B.AGENCY_FEE AS ON_AGENCY_FEE,
                0 AS OFF_AGENCY_FEE
         FROM dbo.a_booking B
             JOIN dbo.a_transaction T
                 ON T.BOOKING_ID = B.ROW_ID
         WHERE BOOKING_TYPE = 'BOOKING_PAID'
               AND YEAR(T.CREATED) = @year
               AND B.AGENCY_ID = @agencyId
               AND B.BOOKING_MODE = 0)
        UNION
        (SELECT MB.ROW_ID,
                RIGHT('00' + CAST(MONTH(MB.CREATED) AS VARCHAR(10)), 2) + '-' + CAST(YEAR(MB.CREATED) AS VARCHAR(10)) AS POINT,
                0 AS ON_AGENCY_FEE,
                MB.AGENCY_FEE AS OFF_AGENCY_FEE
         FROM dbo.a_booking MB
         WHERE BOOKING_TYPE = 'ACCEPT_ADMIN'
               AND YEAR(MB.CREATED) = @year
               AND MB.AGENCY_ID = @agencyId
               AND MB.BOOKING_MODE = 1)
    ) AS TB1
    GROUP BY TB1.POINT;
END;

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