Created
August 6, 2025 20:27
-
-
Save dave-swift/ac61130d322110dd1836aa6a5c2bde5a to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @dateStart = '2024-11-01 04:00:00'; | |
| SET @dateEnd = '2024-12-23 03:59:59'; | |
| WITH host_earnings AS ( | |
| SELECT i.order_id, | |
| ROUND(SUM(IF(i.`status`="unpaid" OR i.`status`="unapplied", i.base_amount, 0)),2) AS open_amount, | |
| ROUND(SUM(IF(i.`status`="paid" OR i.`status`="applied", i.base_amount, 0)),2) AS amount_paid_to_host | |
| FROM host_statement_items i | |
| JOIN oms.events AS e ON e.order_id = i.order_id | |
| WHERE (e.event_date >= @dateStart AND e.event_date <= @dateEnd) | |
| GROUP BY i.order_id | |
| ) | |
| SELECT | |
| e.status AS event_status, | |
| o.status AS order_status, | |
| o.id, | |
| IFNULL(h.payment_terms, '-none-') AS payment_terms, | |
| DATE(CONVERT_TZ(e.event_date,'UTC','EST')) AS OMS_event_date, | |
| DATE(CONVERT_TZ(e.event_date,'UTC','EST')) AS EM_event_date, | |
| he.open_amount, | |
| he.amount_paid_to_host, | |
| 'Active' AS 'Active?', | |
| e.host_name AS 'Vendor Name*', | |
| e.host_id AS 'Vendor ID', | |
| o.order_identifier AS 'Invoice #*', | |
| DATE(CONVERT_TZ(e.event_date,'UTC','EST')) AS 'Invoice Date*', | |
| DATE_ADD(DATE(CONVERT_TZ(e.event_date,'UTC','EST')), INTERVAL 7 DAY) AS 'Due Date*', | |
| ' ' AS 'GL Posting DATE', | |
| ' ' AS 'Exchange Rate', | |
| TRIM(CONCAT(IFNULL(c.name, CONCAT(u.firstname, ' ', u.lastname)), ' ', IFNULL(DATE(e.event_date), '-no event-'), ' ', IFNULL(oi.identifier, '-no invoice-'))) AS 'Description', | |
| ' ' AS 'P.O. Number', | |
| ' ' AS 'Payment Terms Name', | |
| ' ' AS 'Auto Pay Setup', | |
| ' ' AS 'Created from an eBill', | |
| ' ' AS 'Item Name', | |
| ' ' AS 'Item ID', | |
| ' ' AS 'Source of bill creation', | |
| ' ' AS 'Use Bill Description for Expense Description', | |
| ' ' AS 'Default Invoice Number to Invoice Date', | |
| ' ' AS 'Bill is Auto-Saved', | |
| he.open_amount as 'Bill Line Item ', | |
| 'Revenue to hosts' AS 'Bill Line Item Account NAME', | |
| ' ' AS 'Bill Line Item Account NUMBER', | |
| ' ' AS 'Bill Line Item Department NAME', | |
| ' ' AS 'Bill Line Item Department Short NAME', | |
| ' ' AS 'Bill Line Item Location ID', | |
| ' ' AS 'Bill Line Item Location NAME', | |
| ' ' AS 'Bill Line Item Job NAME', | |
| ' ' AS 'Bill Line Item Job ID', | |
| ' ' AS 'Bill Line Item Customer NAME', | |
| ' ' AS 'Bill Line Item Customer ID', | |
| ' ' AS 'Bill Line Item Billable?', | |
| TRIM(CONCAT(IFNULL(c.name, CONCAT(u.firstname, ' ', u.lastname)), ' ', IFNULL(DATE(CONVERT_TZ(e.event_date,'UTC','EST')), ''))) AS 'Bill Line Item Description', | |
| ' ' AS 'Bill Line Item Line Item TYPE', | |
| ' ' AS 'Bill Line Item Item NAME', | |
| ' ' AS 'Bill Line Item Item ID', | |
| ' ' AS 'Bill Line Item Qty', | |
| ' ' AS 'Bill Line Item Unit Cost', | |
| ' ' AS 'Bill Line Item Employee ID', | |
| ' ' AS 'Bill Line Item Employee Last NAME', | |
| ' ' AS 'Bill Line Item Class ID', | |
| ' ' AS 'Bill Line Item Class NAME', | |
| '1' AS 'Bill Line Item Order*' | |
| FROM oms.orders as o | |
| LEFT JOIN host_earnings AS he ON he.order_id = o.id | |
| LEFT JOIN oms.order_invoices AS oi ON oi.order_id = o.id | |
| LEFT JOIN oms.users AS u ON o.user_id = u.id | |
| LEFT JOIN oms.companies AS c ON u.company_id = c.id | |
| LEFT JOIN oms.events AS e ON e.order_id = o.id | |
| LEFT JOIN hosts.hosts AS h ON h.uuid = e.host_id | |
| WHERE | |
| (e.event_date >= @dateStart AND e.event_date <= @dateEnd) | |
| AND o.deleted_at IS NULL | |
| AND he.open_amount > 0 | |
| AND o.status <> 'cancelled' | |
| GROUP BY o.id | |
| ORDER BY e.status, e.host_name ASC, e.event_date |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment