{
  "facility_sql": "SET @billing_day := 1;\r\nSET @freeze_fee := 8.00;\r\n\r\nSELECT /*FACILITY_TAG*/\r\n    bd.bill_date AS Billing_Month,\r\n    c.CUSTOMER_ID,\r\n    CONCAT(c.LASTNAME, ', ', c.FIRSTNAME) AS Member,\r\n    c.CUSTOMER_TYPE,\r\n    c.BDAY AS DOB,\r\n    CASE\r\n        WHEN c.MEMBERSHIP_FORM_OF_PAYMENT = 'BILLTO'\r\n             AND c.RESPONSIBLE_PARTY_ID IS NOT NULL\r\n        THEN CONCAT(rp.FIRSTNAME, ' ', rp.LASTNAME)\r\n        ELSE NULL\r\n    END AS BillTo_ResponsibleParty,\r\n    c.CURRENT_STATUS,\r\n    CONCAT(\r\n        IFNULL(us.Upcoming_Status, ''),\r\n        CASE\r\n            WHEN c.DUES_CHANGE_DATE = bd.bill_date\r\n                 AND (us.Upcoming_Status IS NULL OR us.Upcoming_Status NOT IN ('TERMINATE','FREEZE'))\r\n            THEN ' (Future Dues Change)'\r\n            ELSE ''\r\n        END\r\n    ) AS Status_Change,\r\n    us.Status_Start_Date,\r\n    IFNULL(ab.Account_Balance, 0) AS Account_Balance,\r\n    CASE\r\n        WHEN us.Upcoming_Status = 'TERMINATE'\r\n             AND us.Status_Start_Date = bd.bill_date\r\n        THEN 0\r\n\r\n        WHEN us.Upcoming_Status = 'FREEZE'\r\n             AND us.Status_Start_Date = bd.bill_date\r\n        THEN CASE\r\n                 WHEN c.ALT_FREEZE_FEE IS NOT NULL AND c.ALT_FREEZE_FEE >= 0\r\n                 THEN c.ALT_FREEZE_FEE\r\n                 ELSE @freeze_fee\r\n             END\r\n\r\n        WHEN c.CURRENT_STATUS = 'FROZEN'\r\n             AND NOT EXISTS (\r\n                 SELECT 1\r\n                 FROM status s2\r\n                 WHERE s2.CUSTOMER_ID = c.CUSTOMER_ID\r\n                   AND s2.STATUS = 'OK'\r\n                   AND s2.START_DATE >= bd.bill_date\r\n             )\r\n        THEN CASE\r\n                 WHEN c.ALT_FREEZE_FEE IS NOT NULL AND c.ALT_FREEZE_FEE >= 0\r\n                 THEN c.ALT_FREEZE_FEE\r\n                 ELSE @freeze_fee\r\n             END\r\n\r\n        WHEN c.DUES_CHANGE_DATE = bd.bill_date\r\n             AND (us.Upcoming_Status IS NULL OR us.Upcoming_Status NOT IN ('TERMINATE','FREEZE'))\r\n        THEN IFNULL(c.DUES_CHANGE_AMOUNT, 0) + IFNULL(ao.AddOn_Total, 0)\r\n\r\n        WHEN c.CURRENT_STATUS = 'OK'\r\n             AND us.Upcoming_Status = 'FREEZE'\r\n             AND us.Status_Start_Date > bd.bill_date\r\n        THEN IFNULL(c.EFT_DUES_AMOUNT, 0) + IFNULL(ao.AddOn_Total, 0)\r\n\r\n        ELSE IFNULL(c.EFT_DUES_AMOUNT, 0) + IFNULL(ao.AddOn_Total, 0)\r\n    END AS Dues_Amount\r\n\r\nFROM customers c\r\n\r\nJOIN (\r\n    SELECT DATE(\r\n        CONCAT(\r\n            DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-'),\r\n            LPAD(@billing_day, 2, '0')\r\n        )\r\n    ) AS bill_date\r\n) bd\r\n\r\nLEFT JOIN customers rp\r\n    ON c.RESPONSIBLE_PARTY_ID = rp.CUSTOMER_ID\r\n\r\nLEFT JOIN (\r\n    SELECT s.CUSTOMER_ID,\r\n           s.STATUS AS Upcoming_Status,\r\n           s.START_DATE AS Status_Start_Date\r\n    FROM status s\r\n    JOIN (\r\n        SELECT s1.CUSTOMER_ID,\r\n               MIN(s1.START_DATE) AS next_start_date\r\n        FROM status s1\r\n        WHERE s1.START_DATE >= DATE(\r\n            CONCAT(\r\n                DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-'),\r\n                LPAD(@billing_day, 2, '0')\r\n            )\r\n        )\r\n        GROUP BY s1.CUSTOMER_ID\r\n    ) ns\r\n      ON s.CUSTOMER_ID = ns.CUSTOMER_ID\r\n     AND s.START_DATE = ns.next_start_date\r\n    JOIN (\r\n        SELECT s2.CUSTOMER_ID,\r\n               s2.START_DATE,\r\n               MAX(s2.POSTDATE) AS latest_postdate\r\n        FROM status s2\r\n        WHERE s2.START_DATE >= DATE(\r\n            CONCAT(\r\n                DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-'),\r\n                LPAD(@billing_day, 2, '0')\r\n            )\r\n        )\r\n        GROUP BY s2.CUSTOMER_ID, s2.START_DATE\r\n    ) lp\r\n      ON s.CUSTOMER_ID = lp.CUSTOMER_ID\r\n     AND s.START_DATE = lp.START_DATE\r\n     AND s.POSTDATE = lp.latest_postdate\r\n) us\r\n    ON c.CUSTOMER_ID = us.CUSTOMER_ID\r\n\r\nLEFT JOIN (\r\n    SELECT i.CUSTOMER_ID,\r\n           SUM(i.AMOUNT) AS Account_Balance\r\n    FROM invoices i\r\n    LEFT JOIN payments p\r\n      ON i.PAYMENT_ID = p.PAYMENT_ID\r\n     AND IFNULL(p.DECLINED,0) = 0\r\n     AND IFNULL(p.VOIDEDPAYMENT,0) = 0\r\n    WHERE IFNULL(i.VOIDEDINVOICE,0) = 0\r\n      AND p.PAYMENT_ID IS NULL\r\n    GROUP BY i.CUSTOMER_ID\r\n) ab\r\n    ON c.CUSTOMER_ID = ab.CUSTOMER_ID\r\n\r\nLEFT JOIN (\r\n    SELECT\r\n        CUSTOMER_ID,\r\n        IFNULL(CAST(SUBSTRING_INDEX(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', 1) AS DECIMAL(10,2)), 0)\r\n        +\r\n        IF(\r\n            LENGTH(TRIM(EXTRA_DUES_AMOUNT_LIST)) - LENGTH(REPLACE(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', '')) >= 1,\r\n            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', 2), ' ', -1) AS DECIMAL(10,2)),\r\n            0\r\n        )\r\n        +\r\n        IF(\r\n            LENGTH(TRIM(EXTRA_DUES_AMOUNT_LIST)) - LENGTH(REPLACE(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', '')) >= 2,\r\n            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', 3), ' ', -1) AS DECIMAL(10,2)),\r\n            0\r\n        )\r\n        +\r\n        IF(\r\n            LENGTH(TRIM(EXTRA_DUES_AMOUNT_LIST)) - LENGTH(REPLACE(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', '')) >= 3,\r\n            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', 4), ' ', -1) AS DECIMAL(10,2)),\r\n            0\r\n        )\r\n        +\r\n        IF(\r\n            LENGTH(TRIM(EXTRA_DUES_AMOUNT_LIST)) - LENGTH(REPLACE(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', '')) >= 4,\r\n            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(EXTRA_DUES_AMOUNT_LIST), ' ', 5), ' ', -1) AS DECIMAL(10,2)),\r\n            0\r\n        ) AS AddOn_Total\r\n    FROM customers\r\n) ao\r\n    ON c.CUSTOMER_ID = ao.CUSTOMER_ID\r\n\r\nWHERE c.CUSTOMER_TYPE IN ('MEMBER','STAFF')\r\n  AND c.MEMBERSHIP_FORM_OF_PAYMENT IN ('BILLME','BILLTO')\r\n  AND c.CURRENT_STATUS IN ('OK','FROZEN')\r\n  AND c.NEXT_BILL_DATE = bd.bill_date\r\n\r\nORDER BY c.FACILITY_ID, c.LASTNAME, c.FIRSTNAME;",
  "merge_sql": "",
  "json": "{\r\n  \"facility_column_tag\": \"/*FACILITY_TAG*/\",\r\n  \"billing_date\": {\r\n    \"tag\": \"/*BILLING_DATE*/\",\r\n    \"description\": \"Automatically set to the tenth day of the next month\"\r\n  }\r\n}",
  "name": "Preview Billing - adjust @billing_day and @freeze_fee to match your settings"
}