Dues Reporting Issues

[Glenalth]

Dues reports for some kingdoms and some chapters are missing some or all dues paid members. In some cases these paid members show up on other reports, like the player attendance report, and sometimes on none of them.

Some discussion... https://www.facebook.com/groups/orkupdates/permalink/921813014647818/

An example... Kingdom of Polaris Dues Paid report, currently empty: https://amtgard.com/ork/orkui/index.php?Route=Reports/duespaid/Kingdom&id=27

Kingdom of Polaris Player Attendance report, showing 54 dues paid players: https://amtgard.com/ork/orkui/index.php?Route=Reports/active_duespaid/Kingdom&id=27

[jrtaylor-com]

They are two different reports and use different queries all together with some similarities.

Something I noticed checking into the records though was that the account_id on the split records aren't matching up with Polaris but are assigned to kingdom 10 (Rising Winds).

This is probably residual from when Polaris was a principality. It needs more digging, in the end I believe we will be needing to update data vs updating the report code.

For example: Duke Akyo from Crimson Circle is dues paid, I see that in the DB that he's paid for the past 2 years $20 each time. The account_id for that split record for both transactions is 4271

The record for that account shows: account_id parent_id type name kingdom_id park_id unit_id event_id 4271 4270 Income Dues Paid 10 233 0 0

kingdom_id 10 is RW, park_id 233 is Crimson Circle

I believe this was caused by the conversion of the principality to a kingdom. Since there are no foreign keys setup between this related data the kingdom_id was updated on the park record but that didn't carry over into the "treasury" accounts that already existed. I'm not sure what the process is for converting a principality into a kingdom, it's likely manual.

@esdraelon Does this sound about right?

`Active dues paid

SELECT main_summary.*, total_monthly_credits, credit_counts.daily_credits, credit_counts.rop_limited_credits FROM (SELECT COUNT(week) AS weeks_attended, Sum(weekly_attendance) AS park_days_attended, Sum(daily_attendance) AS days_attended, Sum(credits_earned) total_credits, attendance_summary.mundane_id, mundane.persona, kingdom.kingdom_id, park.park_id, kingdom.name kingdom_name, kingdom.parent_kingdom_id, park.name park_name, attendance_summary.waivered, Ifnull(split_id, 0) AS duespaid FROM (SELECT a.park_id > 0 AS weekly_attendance, COUNT(a.park_id > 0) AS daily_attendance, a.mundane_id, Week(a.DATE, 3) AS week, YEAR(a.DATE) AS YEAR, a.kingdom_id, a.park_id, Max(credits) AS credits_earned, m.waivered FROM ork_attendance a LEFT JOIN ork_mundane m ON a.mundane_id = m.mundane_id WHERE m.suspended = 0 AND DATE > Adddate(Curdate(), interval - 6 MONTH) AND m.kingdom_id = '27' GROUP BY Week(DATE, 3), YEAR(DATE), mundane_id) attendance_summary LEFT JOIN ork_mundane mundane ON mundane.mundane_id = attendance_summary.mundane_id LEFT JOIN ork_kingdom kingdom ON kingdom.kingdom_id = mundane.kingdom_id LEFT JOIN ork_park park ON park.park_id = mundane.park_id LEFT JOIN (SELECT DISTINCT CASE split_id WHEN NULL THEN 0 ELSE 1 END AS split_id, src_mundane_id FROM ork_split s LEFT JOIN ork_account a ON s.account_id = a.account_id AND a.kingdom_id = '27' AND s.is_dues = 1 WHERE s.dues_through > Curdate()) dues ON attendance_summary.mundane_id = dues.src_mundane_id GROUP BY mundane_id HAVING weeks_attended >= '0' AND days_attended >= '6' AND total_credits >= '6' ORDER BY duespaid desc, kingdom_name, park_name, persona) main_summary LEFT JOIN (SELECT mundane_id, Sum(monthly_credits) AS total_monthly_credits FROM (SELECT Least(Sum(credits), 6) AS monthly_credits, a.mundane_id FROM ork_attendance a LEFT JOIN ork_mundane m ON a.mundane_id = m.mundane_id WHERE m.suspended = 0 AND DATE > Adddate(Curdate(), interval - 6 MONTH) AND m.kingdom_id = '27' GROUP BY MONTH(DATE), YEAR(DATE), mundane_id) monthly_list GROUP BY monthly_list.mundane_id) monthly_summary ON main_summary.mundane_id = monthly_summary.mundane_id LEFT JOIN (SELECT mundane_id, Sum(daily_credits) AS daily_credits, Sum(rop_limited_credits) AS rop_limited_credits FROM (SELECT Least(6, Sum(daily_credits)) AS daily_credits, Least(6, Sum(rop_credits)) rop_limited_credits, mundane_id FROM (SELECT Max(credits) AS daily_credits, 1 AS rop_credits, a.mundane_id, a.DATE FROM ork_attendance a LEFT JOIN ork_mundane m ON a.mundane_id = m.mundane_id WHERE m.suspended = 0 AND DATE > Adddate(Curdate(), interval - 6 MONTH) AND m.kingdom_id = '27' GROUP BY Dayofyear(DATE), YEAR(DATE), mundane_id) credit_list_source GROUP BY mundane_id, MONTH(DATE )) credit_list GROUP BY credit_list.mundane_id) credit_counts ON main_summary.mundane_id = credit_counts.mundane_id

duespaid

SELECT m.mundane_id, m.persona, m.park_id, m.kingdom_id, m.restricted, m.waivered, m.given_name, m.surname, m.other_name, m.suspended, m.suspended_at, m.suspended_until, m.suspension, suspended_by.persona suspendator, p.NAME AS park_name, k.NAME AS kingdom_name, m.penalty_box, split_id AS duespaid, dues_through AS duesthrough, k.parent_kingdom_id FROM ork_mundane m LEFT JOIN ork_kingdom k ON m.kingdom_id = k.kingdom_id LEFT JOIN ork_park p ON m.park_id = p.park_id LEFT JOIN ork_mundane suspended_by ON m.suspended_by_id = suspended_by.mundane_id INNER JOIN (SELECT dues_through, CASE split_id WHEN NULL THEN 0 ELSE 1 END AS split_id, src_mundane_id FROM ork_split s INNER JOIN ork_account a ON s.account_id = a.account_id AND ( a.kingdom_id = '27' OR a.park_id = '27' ) AND s.is_dues = 1 WHERE s.dues_through > Curdate()) dues ON m.mundane_id = dues.src_mundane_id WHERE k.kingdom_id = '27' GROUP BY m.mundane_id ORDER BY duespaid DESC, k.NAME, p.NAME, m.persona, m.surname, m.given_name`

[jrtaylor-com]

I've tested this query locally and it appears to resolve the issue with the ork_account.kingdom_id being out of sync with the ork_park.kingdom_id which occurs when a freehold joins a kingdom and when principalities transition.

UPDATE ork_account oa 
 JOIN ork_park op ON op.park_id = oa.park_id
SET 
oa.kingdom_id = op.kingdom_id
WHERE oa.kingdom_id != op.kingdom_id
AND oa.park_id != 0
AND op.active = 'Active'

To keep from having to run this query all the time we should setup foreign keys between the ork_park.kingdom_id -> ork_account.kingdom_id so that on update we cascade the value

[esdraelon]

Hey, without digging too much, does this address the issue of funds being held by the wrong entity?

My concern is if a park moves between kingdoms, do dues paid statuses really stay? For instance, if I pay insurance to one company, then move to another later in the year, they don't "transfer" my contract.

[Glenalth]

That feels like a situation that would totally be up to the chapter and the kingdom when it happens. At least locally, dues are just a chapter level thing but I know it was exactly the opposite in Tal Dagore where all dues went to the kingdom and were then redistributed to the chapters for use but still belonged to the kingdom.

[jkat718]

Until this issue is resolved, a quick fix might be to disable the broken report(s) w/ a note of "Use XYZ report instead."

[jrtaylor-com]

In the process of confirming some stuff then will be making a PR which will require DB migrations to be performed