From f38130e1376c459a391d0c54cd61138b5ec8d4c3 Mon Sep 17 00:00:00 2001 From: Andrew Engelbrecht Date: Wed, 27 Apr 2022 16:08:09 -0400 Subject: [PATCH] analyze memberships that started before time frame ... and that continued into a given time frame. this way someone who bought an annual membership 6.5 years ago will be considered a member as of 5 years ago. donation are considred in isolation from each other, so overlapping donations are not cumulative. we do calculate how much the donation / membership would last based on the amount in a given donation relative to the base monthly rate for associate and student associate members. --- docs/fsf-drupal-auth.md | 10 ++-- lib/Auth/Source/FSFDrupalAuth.php | 91 ++++++++++++++++++++++++------- 2 files changed, 75 insertions(+), 26 deletions(-) diff --git a/docs/fsf-drupal-auth.md b/docs/fsf-drupal-auth.md index aa9a56b..fc0c079 100644 --- a/docs/fsf-drupal-auth.md +++ b/docs/fsf-drupal-auth.md @@ -19,17 +19,17 @@ SQL queries in `config/authsources.php` should be something like the following: // don't filter with 'and c.is_test = 0' because these may be useful for FSF staff 'query_membership' => "select c.status_id from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_membership c on b.contact_id=c.contact_id inner join civicrm.civicrm_contact d on c.contact_id=d.id where a.status = 1 and d.is_deleted = 0 and c.status_id is not NULL and a.name = :username and (c.status_id = 1 or c.status_id = 2 or c.status_id = 3 or c.status_id = 4) order by c.status_id limit 10;", 'query_staff' => "select a.name as is_fsf_staff from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_relationship d on c.id=d.contact_id_a where a.name=:username and a.status=1 and c.is_deleted=0 and d.relationship_type_id=4 and d.contact_id_b = :fsf_org_id and d.is_active=1 and (d.end_date>NOW() or d.end_date is NULL) limit 1;", - 'query_nomination_process_donations' => "select sum(d.total_amount) from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_contribution d on c.id=d.contact_id where a.name = :username and (d.financial_type_id = '2' or :all_contribs) and d.receive_date > :start_date and d.receive_date < :end_date;", - 'query_nomination_process_gift_receipt' => "select count(*) from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_contribution d on c.id=d.contact_id where a.name = :username and d.contribution_page_id = :gift_redeem_page_id and d.receive_date > :start_date and d.receive_date < :end_date;", - 'query_nomination_process_adhoc' => "select count(*) from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_group_contact d on c.id=d.contact_id where a.name=:username and a.status=1 and c.is_deleted=0 and d.group_id = :adhoc_access_group_id and d.status = 'Added' limit 1;", + 'query_nomination_process_donations' => "select d.total_amount as amount, d.receive_date as receive_date, f.membership_type_id as member_type_id from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_contribution d on c.id=d.contact_id left join civicrm.civicrm_membership_payment e on d.id=e.contribution_id left join civicrm.civicrm_membership f on e.membership_id=f.id where a.name = :username and d.contribution_status_id = 1 and d.receive_date > subdate(:start_date, interval 1 year) and d.receive_date < :end_date;", + 'query_nomination_process_gift_receipt' => "select count(*) as gift_memberships_count from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_contribution d on c.id=d.contact_id where a.name = :username and d.contribution_page_id = :gift_redeem_page_id and d.receive_date > :start_date and d.receive_date < :end_date;", + 'query_nomination_process_adhoc' => "select count(*) as is_adhoc_member from drupal.users a inner join civicrm.civicrm_uf_match b on a.uid=b.uf_id inner join civicrm.civicrm_contact c on b.contact_id=c.id inner join civicrm.civicrm_group_contact d on c.id=d.contact_id where a.name=:username and a.status=1 and c.is_deleted=0 and d.group_id = :adhoc_access_group_id and d.status = 'Added' limit 1;", 'fsf_org_id' => '739106', 'nomination_process_contrib_start_date' => '2017-01-01', 'nomination_process_contrib_end_date' => '2022-01-01', 'gift_redeem_page_id' => '63', - 'membership_donation_threshold' => '5', - 'donation_threshold' => '5', + 'membership_monthly_rate' => '10', + 'student_membership_monthly_rate' => '5', 'adhoc_access_group_id' => '1786', ], diff --git a/lib/Auth/Source/FSFDrupalAuth.php b/lib/Auth/Source/FSFDrupalAuth.php index 6eafa53..8441a1b 100644 --- a/lib/Auth/Source/FSFDrupalAuth.php +++ b/lib/Auth/Source/FSFDrupalAuth.php @@ -56,8 +56,8 @@ class FSFDrupalAuth extends \SimpleSAML\Module\core\Auth\UserPassBase private $nomination_process_contrib_start_date; private $nomination_process_contrib_end_date; private $gift_redeem_page_id; - private $donation_threshold; - private $membership_donation_threshold; + private $membership_monthly_rate; + private $student_membership_monthly_rate; private $adhoc_access_group_id; /** @@ -80,8 +80,8 @@ class FSFDrupalAuth extends \SimpleSAML\Module\core\Auth\UserPassBase 'query_nomination_process_donations', 'query_nomination_process_gift_receipt', 'query_nomination_process_adhoc', 'gift_redeem_page_id', - 'fsf_org_id', 'donation_threshold', - 'membership_donation_threshold', + 'fsf_org_id', 'membership_monthly_rate', + 'student_membership_monthly_rate', 'nomination_process_contrib_start_date', 'nomination_process_contrib_end_date', 'adhoc_access_group_id'] as $param) { @@ -294,12 +294,10 @@ class FSFDrupalAuth extends \SimpleSAML\Module\core\Auth\UserPassBase /** * @param string $query_name Name of query in authsources - * @param number $amount Amount to compare result to - * @param boolean $all_contribs Pass as 'all_contribs' param. The var may also be null to exclude it - * @param boolean $gift_redeem_page_id Pass as 'gift_redeem_page_id' param. The var may also be null to exclude it + * @param array $extra_params Associative array of parameters to include in query */ - $meets_a_donation_criterion = function ($query_name, $extra_params, $amount) - use ($username, $start_date, $end_date, $attributes) { + $donation_query = function ($query_name, $extra_params) + use ($username) { $parameters = ['username' => $username]; @@ -307,30 +305,81 @@ class FSFDrupalAuth extends \SimpleSAML\Module\core\Auth\UserPassBase $parameters[$key] = $value; } - $result = $this->query_db($query_name, $parameters); + return $this->query_db($query_name, $parameters); + }; + $compare_res = function ($result, $amount) { foreach ($result[0] as $key => $value) { - if (intval($value) >= $amount) { - return true; - } + if (intval($value) >= $amount) { + return true; + } + } + return false; + }; + + // looks for memberships / comparable donations in time window. also + // looks for a membership or donation (included as a param) that + // occurred up to a year before, and that would have carried over into + // the time window with a single donation. this approximates whether + // the person was, or would have been, a member during the configured + // time window. + $analyze_history = function ($selective_donations_history) + use ($start_date, $end_date) { + + $eligible = false; + + $start_date_obj = new \DateTime($start_date); + $end_date_obj = new \DateTime($end_date); + + foreach ($selective_donations_history as $row) { + + $amount = intval($row['amount']); + $member_type_id = $row['member_type_id']; + $receive_date_obj = new \DateTime($row['receive_date']); + + if ($amount < 5) { + continue; + + } elseif ($receive_date_obj >= $start_date_obj and $receive_date_obj <= $end_date_obj) { + return true; + + } elseif ($receive_date_obj < $start_date_obj) { + switch ($member_type_id) { + case '1': + case '2': + $rate = intval($this->student_membership_monthly_rate); + break; + case '8': + case '9': + case null: + default: + $rate = intval($this->membership_monthly_rate); + break; + } + $membership_end_date_obj = new \DateTime($row['receive_date']); + $membership_end_date_obj->add(new \DateInterval("P" . ceil($amount / $rate) . "M")); + + if ($membership_end_date_obj >= $start_date_obj) { + return true; + } + } } return false; }; - $donation_params = ['start_date' => $start_date, 'end_date' => $end_date, 'all_contribs' => true]; - $membership_params = ['start_date' => $start_date, 'end_date' => $end_date, 'all_contribs' => false]; + $donation_params = ['start_date' => $start_date, 'end_date' => $end_date]; $gift_member_params = ['start_date' => $start_date, 'end_date' => $end_date, 'gift_redeem_page_id' => intval($this->gift_redeem_page_id)]; $adhoc_params = ['adhoc_access_group_id' => intval($this->adhoc_access_group_id)]; - if (($meets_a_donation_criterion('query_nomination_process_donations', $donation_params, intval($this->donation_threshold)) - || $meets_a_donation_criterion('query_nomination_process_donations', $membership_params, intval($this->membership_donation_threshold)) - || $meets_a_donation_criterion('query_nomination_process_gift_receipt', $gift_member_params, 1) - || $meets_a_donation_criterion('query_nomination_process_adhoc', $adhoc_params, 1) - ) - && ($attributes['is_member'] == ['true'])) { + if (($analyze_history($donation_query('query_nomination_process_donations', $donation_params)) + || $compare_res($donation_query('query_nomination_process_gift_receipt', $gift_member_params), 1) + || $compare_res($donation_query('query_nomination_process_adhoc', $adhoc_params), 1) + ) && ($attributes['is_member'] == ['true'])) { $attributes['nomination_process'] = ['true']; } else { + Logger::debug('fsfdrupalauth:'.$this->authId. + ': Not a member / comparable donor during window for board process.'); $attributes['nomination_process'] = ['false']; } -- 2.25.1