docs/fsf-drupal-auth.md

   1 # About fsfdrupalauth / README
   2
   3 This code is based off of the [sqlauth][1] module in upstream SimpleSAMLphp. It
   4 queries whether people are FSF CAS users, and/or whether they are FSF members
   5 or staff.
   6
   7 SQL queries in `config/authsources.php` should be something like the following:
   8
   9     'my-drupalauth' => [
  10
  11         // custom fsf authentication source wrapped by ratelimit auth source
  12         'fsfdrupalauth:FSFDrupalAuth',
  13
  14         'dsn' => 'mysql:host=example.com;port=3306;dbname=drupal',
  15         'username' => '$DB_USERNAME',
  16         'password' => '$DB_PASSWORD',
  17
  18         'query_main' => 'SELECT pass, mail FROM users WHERE name = :username AND status = "1" limit 1;',
  19         // don't filter with 'and c.is_test = 0' because these may be useful for FSF staff
  20         '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;",
  21         '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;",
  22
  23         '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;",
  24         '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;",
  25         '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;",
  26
  27         'query_discussion_process_old_membership' => "select count(*) 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.end_date >= :start_date and c.start_date <= :end_date 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;",
  28         'query_discussion_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 >= :start_date and d.receive_date <= :end_date;",
  29         'query_discussion_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;",
  30
  31
  32         'fsf_org_id' => '739106',
  33         'gift_redeem_page_id' => '63',
  34         'membership_monthly_rate' => '10',
  35         'student_membership_monthly_rate' => '5',
  36
  37         'nomination_process_active' => 'true',
  38         'nomination_process_contrib_start_date' => '2017-05-01',
  39         'nomination_process_contrib_end_date' => '2022-02-01',
  40         'nomination_process_adhoc_access_group_id' => '1786',
  41
  42         'discussion_process_active' => 'true',
  43         'discussion_process_contrib_start_date' => '2018-01-19',
  44         'discussion_process_contrib_end_date' => '2022-10-19',
  45         'discussion_process_adhoc_access_group_id' => '1786',
  46         'discussion_process_adhoc_no_access_group_id' => '1787',
  47         'discussion_process_donation_amount' => '120',
  48
  49         'discussion_moderator_access_group_id' => '1788',
  50
  51     ],
  52
  53 ## License
  54
  55 See COPYING file.
  56
  57 ---
  58
  59 [1]: https://github.com/simplesamlphp/simplesamlphp-module-sqlauth
  60