SELECT * FROM (
|
SELECT
|
CONCAT('t="', aggregated.withdrawalId, '" q=10003 p1="', aggregated.withdrawalId, '"') as link_ID,
|
aggregated.dateStarted AS 'Date Started',
|
maxLedger.eventDate AS `Last Change`,
|
'Withdrawal' AS `Type`,
|
maxLedger.playerUsername AS 'Player',
|
maxLedger.methodName AS 'Payment Method',
|
maxLedger.currencyName AS Currency,
|
ROUND(completeLedger.amount / 100.0, 2) AS Amount,
|
ROUND(COALESCE(feeLedger.amount, 0) / 100, 2) AS Fee,
|
CASE
|
WHEN maxLedger.transactionType IN ('Withdrawal Requested', 'Withdrawal Fee Reserved') THEN 'Pending Approval'
|
WHEN maxLedger.transactionType = 'Withdrawal Approved' THEN 'Pending Submission'
|
WHEN maxLedger.transactionType = 'Withdrawal Submitted' THEN 'In-progress'
|
WHEN maxLedger.transactionType IN ('Withdrawal Completed', 'Withdrawal Fee Completed') THEN 'Completed'
|
WHEN maxLedger.transactionType IN ('Withdrawal Canceled', 'Withdrawal Fee Canceled') THEN 'Canceled'
|
END as Status,
|
operatorInfo.`Operator ID` AS 'Operator ID',
|
operatorInfo.`Operator First Name` AS 'Operator First Name',
|
operatorInfo.`Operator Last Name` AS 'Operator Last Name',
|
operatorInfo.`Operator Username` AS 'Operator Username',
|
operatorInfo.`Operator Title` AS 'Operator Title',
|
maxLedger.memo AS 'Memo'
|
FROM
|
(
|
SELECT withdrawalId, MIN(eventDate) AS dateStarted, MAX(ledgerId) AS lastLedgerId
|
FROM common.ViewWithdrawals w
|
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
GROUP BY withdrawalId
|
) aggregated
|
INNER JOIN (
|
SELECT * FROM common.ViewWithdrawals w
|
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
AND "2014-01-22 00:00:00" <= eventDate
|
AND "2014-01-22 23:59:59" > eventDate
|
) maxLedger ON aggregated.lastLedgerId = maxLedger.ledgerId
|
INNER JOIN (
|
SELECT withdrawalId, transactionType, referenceId, amount FROM common.ViewWithdrawals w
|
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
AND "2014-01-22 00:00:00" <= eventDate
|
) completeLedger ON aggregated.withdrawalId = completeLedger.withdrawalId AND completeLedger.transactionType = 'Withdrawal Completed'
|
LEFT JOIN (
|
SELECT withdrawalId, transactionType, amount FROM common.ViewWithdrawals w
|
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
AND "2014-01-22 00:00:00" <= eventDate
|
) feeLedger ON aggregated.withdrawalId = feeLedger.withdrawalId AND feeLedger.transactionType = 'Withdrawal Fee Completed'
|
LEFT JOIN
|
common.AccountLedgerAttribute ala
|
ON ala.account_ledger_id = maxLedger.ledgerId AND ala.name = 'operatorId'
|
LEFT JOIN (
|
SELECT
|
op.id AS 'Operator ID',
|
op.firstname AS 'Operator First Name',
|
op.lastname AS 'Operator Last Name',
|
op.username AS 'Operator Username',
|
op.title AS 'Operator Title'
|
FROM
|
common.Operator op
|
LEFT OUTER JOIN OperatorGroups ogs ON op.id = ogs.operator_id
|
LEFT OUTER JOIN OperatorGroup og ON ogs.group_id = og.id
|
GROUP BY
|
op.id, op.firstname, op.lastname, op.created
|
) operatorInfo ON ala.value = operatorInfo.`Operator ID`
|
UNION ALL
|
SELECT
|
CONCAT('t="', t.depositId, '" q=10003 p1="', t.depositId, '"') as link_ID,
|
t.dateStarted AS 'Date Started',
|
q.eventDate AS `Last Change`,
|
'Deposit' AS `Type`,
|
playerUsername AS 'Player',
|
methodName AS 'Payment Method',
|
currencyName AS Currency,
|
ROUND(amount / 100.0, 2) AS Amount,
|
'n/a' AS Fee,
|
CASE
|
WHEN q.transactionType = 'Deposit Started' THEN 'In-progress'
|
WHEN q.transactionType = 'Deposit Canceled' THEN 'Canceled'
|
WHEN q.transactionType = 'Deposit Completed' THEN 'Completed'
|
END AS Status,
|
operatorInfo.`Operator ID` AS 'Operator ID',
|
operatorInfo.`Operator First Name` AS 'Operator First Name',
|
operatorInfo.`Operator Last Name` AS 'Operator Last Name',
|
operatorInfo.`Operator Username` AS 'Operator Username',
|
operatorInfo.`Operator Title` AS 'Operator Title',
|
q.memo AS 'Memo'
|
FROM
|
(
|
SELECT depositId, MIN(eventDate) AS dateStarted, MAX(ledgerId) AS lastLedgerId
|
FROM common.ViewDeposits d
|
WHERE IF("all" IN ('all', 'Deposit'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
GROUP BY depositId
|
) t
|
INNER JOIN (
|
SELECT * FROM common.ViewDeposits d
|
WHERE IF("all" IN ('all', 'Deposit'), 1, 0) = 1
|
AND IF("all" = "all", 1, methodName = "all") = 1
|
AND IF("USD" = "all", 1, currencyName = "USD") = 1
|
AND "2014-01-22 00:00:00" <= eventDate
|
AND "2014-01-22 23:59:59" > eventDate
|
AND transactionType = 'Deposit Completed'
|
) q ON t.lastLedgerId = q.ledgerId
|
LEFT JOIN
|
common.AccountLedgerAttribute ala
|
ON ala.account_ledger_id = q.ledgerId AND ala.name = 'operatorId'
|
LEFT JOIN (
|
SELECT
|
op.id AS 'Operator ID',
|
op.firstname AS 'Operator First Name',
|
op.lastname AS 'Operator Last Name',
|
op.username AS 'Operator Username',
|
op.title AS 'Operator Title'
|
FROM
|
common.Operator op
|
LEFT OUTER JOIN OperatorGroups ogs ON op.id = ogs.operator_id
|
LEFT OUTER JOIN OperatorGroup og ON ogs.group_id = og.id
|
GROUP BY
|
op.id, op.firstname, op.lastname, op.created
|
) operatorInfo ON ala.value = operatorInfo.`Operator ID`
|
) p
|
ORDER BY `Last Change`
|