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_booking ADD PREVIOUS_MANUAL_BOOKING_ID BIGINT NULL;

-- Update claim with offline booking
DECLARE @RowId BIGINT;
DECLARE @PrevId BIGINT;

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT ROW_ID,
       PREVIOUS_MANUAL_BOOKING_ID
FROM dbo.a_booking
WHERE BOOKING_MODE = 1;

OPEN MY_CURSOR;
FETCH NEXT FROM MY_CURSOR
INTO @RowId,
     @PrevId;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.a_claim
    SET BOOKING_ID = @RowId
    WHERE BOOKING_ID_MANUAL = @PrevId;
    FETCH NEXT FROM MY_CURSOR
    INTO @RowId,
         @PrevId;
END;
CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

-- Set if this booking is convert from online to offline
ALTER TABLE dbo.a_booking
ADD IS_CONVERTED BIT NULL

ALTER TABLE dbo.a_booking
ALTER COLUMN BOOKING_MODE INT NULL

--LIVE--

ALTER TABLE dbo.a_booking
ADD REAL_CREATED_DATE DATETIME NULL

ALTER TABLE dbo.a_claim
ADD COMMISSION_STATUS INT NULL

-- Update claim base on commission
DECLARE @RowId BIGINT;
DECLARE @ClaimId BIGINT;
DECLARE @Status BIGINT;

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT ROW_ID,
       CLAIM_ID,
       STATUS
FROM dbo.a_agent_commission
WHERE STATUS <> 0;

OPEN MY_CURSOR;
FETCH NEXT FROM MY_CURSOR
INTO @RowId,
     @ClaimId,
     @Status;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.a_claim
    SET COMMISSION_STATUS = @Status
    WHERE ROW_ID = @ClaimId;
    FETCH NEXT FROM MY_CURSOR
    INTO @RowId,
         @ClaimId,
		 @Status;
END;
CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

ALTER TABLE dbo.a_files
ADD EXTERNAL_DATA VARCHAR(1000) NULL

-- 30/5/2018 Add new column to table a_claim
ALTER TABLE dbo.a_claim
ADD BANK_ACC_NO NVARCHAR(255) NULL,
    BANK_ACC_NAME NVARCHAR(255) NULL;

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

SELECT COUNT(*) AS ONLINE_BOOKING,
       SUM(a.BOOKING_FEE) AS ONLINE_DEPOSIT,
       SUM(a.PAYMENT_AMOUNT * a.EARNEST_DEPOSIT_RENTAL / 100) AS ONLINE_DEPOSIT_PURCHASE
FROM dbo.a_booking a
WHERE MONTH(a.CREATED) = MONTH(GETDATE()) - 1
      AND YEAR(a.CREATED) = YEAR(GETDATE())
      AND a.BOOKING_MODE = 0
      AND a.AGENCY_ID NOT IN ( 15, 30 );

SELECT COUNT(*) AS OFFLINE_BOOKING,
       SUM(a.BOOKING_FEE) AS OFFLINE_DEPOSIT_RENTAL,
       SUM(a.PAYMENT_AMOUNT * a.EARNEST_DEPOSIT / 100) AS OFFLINE_DEPOSIT_PURCHASE
FROM dbo.a_booking a
WHERE MONTH(a.CREATED) = MONTH(GETDATE()) - 1
      AND YEAR(a.CREATED) = YEAR(GETDATE())
      AND a.BOOKING_MODE = 1
      AND a.AGENCY_ID NOT IN ( 15, 30 );

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
                            AND BM.BOOKING_TYPE = 'PENDING_DOCUMENTS_APPROVAL'
                ) AS OfflineDocs

    END

-- 07/06/2018 Add new column to table a_agent_commission
ALTER TABLE dbo.a_agent_commission
ADD UPDATED_BY BIGINT NULL
UPDATE dbo.a_agent_commission
SET UPDATED_BY = 0

ALTER TABLE	dbo.a_claim
ADD REFUND_TYPE INT NULL, FORFEIT_PERCENT DECIMAL(18,2) NULL

ALTER TABLE dbo.a_claim ADD FORFEIT_AMOUNT DECIMAL(18, 2) NULL;

ALTER TABLE dbo.a_claim
ADD EXCESS_AMOUNT DECIMAL(18, 2) NULL,
EXCESS_OWNER_NAME NVARCHAR(50) NULL,
EXCESS_BANK_NAME VARCHAR(50) NULL,
EXCESS_BANK_ACC VARCHAR(20) NULL

CREATE NONCLUSTERED INDEX [booking_index] ON [dbo].[a_booking]
(
	[ROW_ID] ASC,
	[BOOKING_TYPE] ASC,
	[AGENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [claim_indexes] ON [dbo].[a_claim]
(
	[ROW_ID] ASC,
	[BOOKING_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE dbo.a_claim ADD COBROKE_COMMISSION_STATUS INT NULL;
ALTER TABLE dbo.a_claim ADD MADE_COBROKE_COMMISSION BIT NULL;
ALTER TABLE dbo.a_agent_commission
ADD IS_COBROKE BIT NULL

USE [rg_sb]
GO

/****** Object:  StoredProcedure [dbo].[SP_GetAgencyAgent]    Script Date: 22/6/2018 11:34:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ===============================================================
-- Author:		Jeffrey
-- Create date: 19/06/2018
-- Description:	SQL to get agency's agent with total booking count
-- ===============================================================
CREATE 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;

GO

-- 26/06/2018 Add new column to table a_claim
ALTER TABLE dbo.a_claim
ADD INTRCER_BANK_NAME NVARCHAR(255) NULL,
    INTRCER_BANK_ACC_NO NVARCHAR(255) NULL

CREATE TABLE [dbo].[a_rentalcollection](
	[ROW_ID] [BIGINT] NOT NULL,
	[CREATED] [DATETIME] NULL,
	[CREATED_BY] [BIGINT] NULL,
	[LAST_UPDATED] [DATETIME] NULL,
	[LAST_UPDATED_BY] [BIGINT] NULL,
	[LANDLORD_ID] [BIGINT] NULL,
	[TENANT_ID] [BIGINT] NULL,
	[UNIT_ID] [BIGINT] NULL,
	[BILLING_CYCLES] [INT] NULL,
	[BILLING_START_DATE] [DATETIME] NULL,
	[BILLING_END_DATE] [DATETIME] NULL,
	[MONTHLY_RENTAL] [DECIMAL](18, 2) NULL,
	[BANK_NAME] [VARCHAR](200) NULL,
	[BANK_ACCOUNT] [VARCHAR](200) NULL,
	[BANK_HOLDER_NAME] [VARCHAR](200) NULL,
	[ACTIVE] [BIT] NULL,
 CONSTRAINT [PK_a_rentalcollection] PRIMARY KEY CLUSTERED 
(
	[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[a_rentalcollection_tran](
	[ROW_ID] [BIGINT] NOT NULL,
	[CREATED] [DATETIME] NULL,
	[CREATED_BY] [BIGINT] NULL,
	[LAST_UPDATED] [DATETIME] NULL,
	[LAST_UPDATED_BY] [BIGINT] NULL,
	[RENTAL_COLLECTION_ID] [BIGINT] NOT NULL,
	[PAY_ORDER] [INT] NULL,
	[PAID_DATE] [DATETIME] NULL,
	[ACTIVE] [BIT] NULL,
 CONSTRAINT [PK_a_rentalcollection_tran] PRIMARY KEY CLUSTERED 
(
	[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE dbo.a_files
ADD RC_ID BIGINT NULL

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