Fundraising/Internal-facing/CiviCRM

From Wikitech
Jump to navigation Jump to search

CiviCRM (Drupal)

WMF fundraising uses CiviCRM to track donor data.

CiviCRM requires a 'host CMS' and to that end we use Drupal7. Drupal 7 is EOL in November 2022 and next year we plan to upgrade to Drupal 9 - or maybe even 10. Our goal, however, is that we do not use any CMS-specific code going forwards. While we currently expect to stick with Drupal in we should be equally able to move to Wordpress. To this end we are in the process of migrating our drupal modules to CiviCRM extensions.

drush

Drush is a really useful drupal command line utility. There is a lot of documentation about drush on the internet but a few things to know with regards to WMF.

  • On production, staging and in our docker dev set up we have an alias 'wmff' which tells drush details about where the code is and to use user 1.
  • Common usage:
Command Where What
`drush @wmff updb` local dev and prod Run any database updates that need to be run
`drush @wmff up --security-only` local dev Download and install any security updates (these are then checked into git to deploy)
`drush @wmff cvapi Contact.get version=4 checkPermissions=0` local dev and prod Run a civicrm api - the Contact.get action is probably not in itself useful but it does show how a api version 4 call would look
`drush @wmff cvapi WMFDataManagement.CleanInvalidLanguageOptions version=4` local dev and prod clean up invalid languages option value if no contact is using that language and option label equal name

Troubleshooting setup

  • Make sure the settings directory (src/civi-sites/wmff/drupal/sites/default/) is writeable by the user that runs setup.sh
  • Make sure you have the latest version of civicrm-buildkit in src/civicrm-buildkit.
  • If you see problems finding classes while enabling the civicrm extension, this may be due to stale metadata cached in redis. You can use the queues-redis-clear.sh script to clear that out (note you will also lose all messages in the donation and job queues).

Log Tables

We use CiviCRM logging to create log tables which we use for

- Threshold queries - query log_civicrm_contribution for contributon updates in the last 5 minutes (not includes any sort of update)

- Forensics - eg https://phabricator.wikimedia.org/T311438 involved figuring out history of a contact around 18 months ago. Importantly determined they never had any contributions - ie didn't have them & then they were deleted, but never existing

Or best practices

  • CiviCRM uses the drupal whitespace standards (sadly adopted before the world co-alesced on something different) so all drupal modules & civicrm extensions (including the CiviCRM codebase) should use that. When using phpstorm installing the drupal plugin will add this standard. Within the vendor directory PSR2 is likely more appropriate
  • We agreed to name our civicrm extensions with the prefix 'wmf-` where they are WMF specific and just the name if they are to be shared - ie `wmf-civicrm` and `deduper` respectively. This applies going forwards (we may or may not fix existing ones)

Running CiviCRM behind a firewall

In order to run CiviCRM behind a firewall we have disabled some SystemChecks on production (but not on our local developer environments) and use a ckeditor image uploader that embeds images. These are documented in the main CiviCRM firewall documentation. In our case the use of browser certificates for authentication means the server cannot reach it's own urls during crons

Our CiviCRM customisations

We have customised CiviCRM using both CiviCRM Extensions and drupal modules and by creating relevant Managed Entities and setting Settings.

Custom fields

Custom fields in CiviCRM can be created through the user interface. In order to allow flexibility to our users the arrangement we have with our super-users (Nora, Rosie) is that they can create custom fields through the UI but they should create a phab task so that fr-tech can follow up ( add field to advance search and so on ).

The follow up by frtech is in 2 parts - ensuring the fields are present in our dev environments and creating triggers.

Ensuring the fields are present in our dev environments

Keeping our dev environment fields in sync is a best-efforts endeavor rather than something we keep 100% in sync, but it does make it easier for us to develop locally. There is currently an 'old method' and a 'new method. The new method is to use the CiviCRM managed entities functionalityas reflected in `CustomGroupMatchingGiftInformation.mgd.php` file. The previous barriers to this standardised approach, around the use of the more efficient BulkSave action and matching existing entities, are now resolved. However we DO set the action on update & cleanup to 'never' to avoid anything unexpected on production. The command to reconcile these is

drush @wmff cvapi Managed.reconcile version=4

The old method involves tracked/synced fields being declared in the CustomGroups.php file in the Managed directory in the `wmf_civicrm` extension. '. This file follows the conventions of the CiviCRM managed entities functionality and the fields declared in the file are added to our developer builds on install.

However, because the file is not a direct match to the Custom fields on prod, we have not registered this file with the civicrm_managed hook, and instead we have a custom wmf command which adds any declared fields in the CustomGroups.php, that are missing for the dev site.

As of May 2022 this command is about to be changed via https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/801443 The new command will be

drush @wmff cvapi WMFConfig.syncCustomFields version=4

The old command was

drush @wmff ucf

This command adds CustomGroups and CustomFields to dev sites if missing, but does not update them. It only creates option values if the field did not previously exist or it is being run in a development environment - ie we want to add but not update on live.

Note that

  • Php-output.png
    When declaring the fields in the CustomGroups array an easy way to get the data from live is to use the API v4 explorer - once the criteria are selected & execute has been hit the field data is listed in a json format and there is even an option next to it to switch to a php format. Fields that do not differ from the defaults (including is_active) should be removed from the resulting array, along with the id field. The explorer can be used in a similar way to get the CustomFields in the group and any option values (using the option_group_id from the custom field definition). Do not include option_group_id or custom_group_id in the checked in array
  • In CivICRM all field types can be extended with custom groups - however, CiviCRM must know that they can be extended. CiviCRM has a hard coded mapping of the common entities (Contribution, Contact etc) but also maintains an option group ` cg_extends` with other entities. When extending an entity type that is not extendable by default we need to ensure the option value exists.
    CRM_Core_BAO_OptionValue::ensureOptionValueExists([
      'option_group_id' => 'cg_extend_objects',
      'name' => 'civicrm_relationship',
      'label' => ts('Relationship'),
      'value' => 'Relationship',
    ]);
    
  • In some cases the functionality of the custom fields are owned by extensions rather than WMF user driven. In these cases the fields are declared in the relevant extension (e.g the Omnimail extension installs 2 custom groups and the relationship block extension installs one). These are written into the Upgrade classes in the relevant extensions and extension upgrades are run using the following command.
drush @wmff cvapi Extension.upgrade
Update triggers on production

We use mysql triggers to log civicrm database updates to the log tables. These triggers are managed by CiviCRM. However, our production user does not have enough mysql permissions to create the triggers within mysql. To get around this we use a CiviCRM setting on production to log the sql to update the triggers to a file rather than live update them. We then check this file into our crm repo (sites/all/modules/wmf_civicrm/scripts/triggers.mysql currently) and fr-Ops run the file on live.

On development environments triggers are automatically updated in the database - which is generally easier - but to make your local output the triggers as live does the logging_no_trigger_permission setting can be enabled

drush @wmff cvapi Setting.create logging_no_trigger_permission=1

Trigger generation needs to be done on production as the fields differ slightly on staging / dev environments. There are a few methods but turning logging off & back on generally generates consistent output. ie

 drush @wmff cvapi Setting.create logging=0
 drush @wmff cvapi Setting.create logging=1

This will generate a file named something like CiviCRM.trigger62451ae5ab5a5mYm67702126718965e4a41105a08d6202e60.sql that will be in drupal/sites/default/files/civicrm/ConfigAndLog/ - copy this back to your home drive and scp it back to your local machine as sites/all/modules/wmf_civicrm/scripts/triggers.mysql. Changes to this files are committed, reviewed and deployed but they will not be 'live' until fr-tech-ops loads them so once deployed they need to be engages to run the latest triggers.mysql file

Automated emails from CiviCRM

We send out the following automated emails

  • Recurring failure notifications - these are send when a monthly recurring email is failing and encourages people to set up a new one. It is not sent if they have an active recurring email.
  • Thank you letters - these are send by an automated job for every donation in CiviCRM, unless the 'no_thankyou' field is populated or it is a recurring donations
  • End of year emails - these are sent at the start of the year to cover all the recurring contributions in the previous year. These can also be sent ad hoc to individual donors (in which case they include all donations, not just the recurring ones)

Creating extensions in Civicrm to manage tables in other WMFF Databases

Performance tracking

Queries & timing

You can run the following drush command on dev or staging to import (60) contributions and get timings.

drush @wmff qperf-d 60 "your comment"

If you wish to see what queries run you can add this line to your civicrm.settings.php file - the 'n' is just part of the file name, in case you want to do separate runs

define('CIVICRM_DEBUG_LOG_QUERY', 'n');

The output will be in drupal/sites/default/files/civicrm/ConfigAndLog/ - eg. the bottom file below (with the 'n') - see https://docs.civicrm.org/dev/en/latest/tools/debugging/

docker@civicrm:/srv/civi-sites/wmff$ ls -altr drupal/sites/default/files/civicrm/ConfigAndLog/
total 27816
-rw-r--r-- 1 1000 1000      202 Dec 22  2021 .htaccess
drwxr-xr-x 8 1000 1000     4096 Mar  8 02:51 ..
-rw-r--r-- 1 1000 1000   215636 Jun 30 03:50 CiviCRM.b4f78a4e4ce41be7806fadbb.log.202207050615
-rw-r--r-- 1 1000 1000      623 Jul  9 00:41 CiviCRM.b4f78a4e4ce41be7806fadbb.log.202208030206
-rw-r--r-- 1 1000 1000    31549 Aug 23 23:11 CiviCRM.b4f78a4e4ce41be7806fadbb.log
drwxr-xr-x 2 1000 1000     4096 Aug 25 06:14 .
-rw-r--r-- 1 1000 1000 28205254 Aug 25 06:14 CiviCRM.sql_logn.b4f78a4e4ce41be7806fadbb.log

The extension org.wikimedia.systemtools provides a script / api to help analyse this file - you may need to installl it - in the UI it is called "Home for WMF helpers". Once enabled you can run the following

echo '{"fileName":"/srv/civi-sites/wmff/drupal/sites/default/files/civicrm/ConfigAndLog/CiviCRM.sql_logn.b4f78a4e4ce41be7806fadbb.log", "version":4}' | drush @wmff cvapi Querylog.parse --in=json

It will output a csv with a cleaned up version of the queries to the directory you are in - the output will give the filename (spoiler - it's gonna be query_log_parsed.csv).


Also note the extension has a Readme.md....


At this point I generally figure out where a single row starts & ends in the file & pick one from the middle of the file & discard the rest....

Redis monitoring

We use Redis for our caching service. Within CiviCRM values retrieved from Redis are 'mostly' cached within the php layer so Redis is hit once per process for most keys. This matters quite a lot for large arrays - as the serialization & unserialization of them has turned out to be slow. In general each process should not be doing `GET` requests for the same key multiple times (unless the cache has been flushed / the key deleted). To see what Redis is hitting you can do the following


- Add to your ~/.profile

export REDIS_PWD=*************
alias redis_monitor='redis-cli -a $REDIS_PWD monitor'


Note the **** password is in civicrm.settings.php


At this point you can run

redis-monitor > redis.log


If you scp the file down locally there is a script to help make sense of it in the systemtools extension (which might need to be enabled) - e.g

drush @wmff cvapi Redislog.parse version=4 fileName=/srv/civi-sites/wmff/redis.log


This command does 2 things

- provides some summary data

- outputs '`' (backtick) separated file which can be imported into mysql (backtick seems to be otherwise not present so usable but we could make the separator a parameter)


Preferred Language

Historically our code just added together the language string 'en' and the country string 'DK' to get 'en_DK' - since that wasn't in the database it was just added. so we would love to clean up the language mess.

So we first view civicrm option values from search kit and find out that we have different types of the invalid language options, like typo 1 or a one as xx which represents nothing, or the one added historically.

First we write a drush commend which can filter out the languages that contain the same value and the name ( means they are added to db since wasn't there as en_DK ) then compare with the contacts, that if we do not have any contacts that use this option value, then it's ok to delete it from the civicrm_option_value table.

Initially want to replace them all in db with the civi install, as this ticket, then find we might encounter the issue of deadlock, so then decides to use the cron table.

In order to prevent the db deadlock, we use the cron table with batch size to run the contacts query, and then update the invalid language to the default one, as "update_language."

Used this process control cron job to update the invalid prefer languages for contact based on their current prefer languages as `sh -c "echo '{\"values\":{\"preferred_language\":\"en_US\"},\"where\":[[\"preferred_language\",\"NOT IN\",[\"en_US\", \"en_CA\",\"en_ZA\", \"en_AU\", \"en_GB\", \"en_NZ\", \"en_IN\"]], [\"preferred_language\",\"LIKE\",\"en_%\"]],\"limit\":5000, \"version\":4}' | drush @wmff cvapi Contact.update --in=json"`, refer to this ticket

then we clean up the option values with this patch and run `drush @wmff cvapi WMFDataManagement.CleanInvalidLanguageOptions version=4` to actually delete from civicrm_otpion_value table;

Used this api explorer to update the in_active option value to active: Edit civicrm option value from api explorer, refer to this ticket

eventually, we have the following language options:

MariaDB [civicrm]> select id, value, label, name, is_active, is_default from civicrm_option_value where option_group_id=86 and is_active = 1 order by value;

id value label name is_active is_default
586 aa Afar aa_ET 1 0
585 ab Abkhaz ab_GE 1 0
596 ae Avestan ae_XX 1 0
587 af Afrikaans (af) af_ZA 1 0
588 ak Akan ak_GH 1 0
590 am Amharic (am) am_ET 1 0
592 an Aragonese an_ES 1 0
591 ar Arabic (Egypt) ar_EG 1 0
594 as Assamese as_IN 1 0
595 av Avaric av_RU 1 0
597 ay Aymara ay_BO 1 0
598 az Azerbaijani (az) az_AZ 1 0
600 ba Bashkir ba_RU 1 0
602 be Belarusian (be) be_BY 1 0
608 bg Bulgarian bg_BG 1 0
604 bh Bihari bh_IN 1 0
605 bi Bislama bi_VU 1 0
599 bm Bambara bm_ML 1 0
603 bn Bengali (bn) bn_BD 1 0
743 bo Tibetan Standard, Tibetan, Central bo_CN 1 0
607 br Breton br_FR 1 0
606 bs Bosnian bs_BA 1 0
610 ca Catalan; Valencian ca_ES 1 0
612 ce Chechen ce_RU 1 0
611 ch Chamorro ch_GU 1 0
617 co Corsican co_FR 1 0
618 cr Cree cr_CA 1 0
620 cs Czech cs_CZ 1 0
704 cu Old Church Slavonic, Church Slavic, Church Slavonic, Old Bulgarian, Old Slavonic cu_BG 1 0
615 cv Chuvash cv_RU 1 0
761 cy Welsh cy_GB 1 0
621 da Danish da_DK 1 0
5852 da Danish (Greenland) da_GL 1 0
1540 de German (Luxembourg) de_LU 1 0
1575 de German (Belgium) de_BE 1 0
1354 de German (Austria) de_AT 1 0
2427 de German (Liechtenstein) de_LI 1 0
636 de German (Germany) de_DE 1 0
945 de German (Switzerland) de_CH 1 0
622 dv Divehi; Dhivehi; Maldivian; dv_MV 1 0
624 dz Dzongkha dz_BT 1 0
628 ee Ewe ee_GH 1 0
637 el Greek, Modern el_GR 1 0
808 en English (Australia) en_AU 1 0
809 en English (Canada) en_CA 1 0
810 en English (United Kingdom) en_GB 1 0
625 en English (United States) en_US 1 1
9116 en English (New Zealand) en_NZ 1 0
9117 en English (India) en_IN 1 0
9119 en English (Singapore) en_SG 1 0
1261 en English (South Africa) en_ZA 1 0
626 eo Esperanto eo_XX 1 0
1299 es Spanish (Latin America) es_MX 1 0
946 es Spanish; Castilian (Puerto Rico) es_PR 1 0
733 es Spanish; Spain es_ES 1 0
627 et Estonian et_EE 1 0
601 eu Basque (eu) eu_ES 1 0
710 fa Persian (Iran) fa_IR 1 0
633 ff Fula; Fulah; Pulaar; Pular ff_SN 1 0
631 fi Finnish fi_FI 1 0
630 fj Fijian fj_FJ 1 0
629 fo Faroese fo_FO 1 0
811 fr French (Canada) fr_CA 1 0
632 fr French (France) fr_FR 1 0
763 fy Western Frisian fy_NL 1 0
650 ga Irish ga_IE 1 0
726 gd Scottish Gaelic; Gaelic gd_GB 1 0
634 gl Galician gl_ES 1 0
638 gn Guaranà gn_PY 1 0
639 gu Gujarati gu_IN 1 0
682 gv Manx gv_IM 1 0
641 ha Hausa ha_NG 1 0
642 he Hebrew (modern) he_IL 1 0
644 hi Hindi hi_IN 1 0
645 ho Hiri Motu ho_PG 1 0
619 hr Croatian hr_HR 1 0
640 ht Haitian; Haitian Creole ht_HT 1 0
646 hu Hungarian hu_HU 1 0
593 hy Armenian (hy) hy_AM 1 0
643 hz Herero hz_NA 1 0
647 ia Interlingua ia_XX 1 0
648 id Indonesian id_ID 1 0
649 ie Interlingue ie_XX 1 0
651 ig Igbo ig_NG 1 0
700 ii Nuosu ii_CN 1 0
652 ik Inupiaq ik_US 1 0
653 io Ido io_XX 1 0
654 is Icelandic is_IS 1 0
3149 it Italian (Vatican City) it_VA 1 0
655 it Italian (Italy) it_IT 1 0
1458 it Italian (San Marino) it_SM 1 0
1476 it Italian (Switzerland) it_CH 1 0
656 iu Inuktitut iu_CA 1 0
657 ja Japanese ja_JP 1 0
658 jv Javanese jv_ID 1 0
635 ka Georgian ka_GE 1 0
669 kg Kongo kg_CD 1 0
665 ki Kikuyu, Gikuyu ki_KE 1 0
672 kj Kwanyama, Kuanyama kj_NA 1 0
663 kk Kazakh kk_KZ 1 0
659 kl Kalaallisut, Greenlandic kl_GL 1 0
664 km Khmer km_KH 1 0
660 kn Kannada kn_IN 1 0
670 ko Korean ko_KR 1 0
661 kr Kanuri kr_NE 1 0
662 ks Kashmiri ks_IN 1 0
671 ku Kurdish ku_IQ 1 0
668 kv Komi kv_RU 1 0
616 kw Cornish kw_GB 1 0
667 ky Kirghiz, Kyrgyz ky_KG 1 0
673 la Latin la_VA 1 0
674 lb Luxembourgish, Letzeburgesch lb_LU 1 0
675 lg Luganda lg_UG 1 0
676 li Limburgish, Limburgan, Limburger li_NL 1 0
677 ln Lingala ln_CD 1 0
678 lo Lao lo_LA 1 0
679 lt Lithuanian lt_LT 1 0
680 lu Luba-Katanga lu_CD 1 0
681 lv Latvian lv_LV 1 0
684 mg Malagasy mg_MG 1 0
690 mh Marshallese mh_MH 1 0
688 mi Māori mi_NZ 1 0
683 mk Macedonian mk_MK 1 0
686 ml Malayalam ml_IN 1 0
691 mn Mongolian mn_MN 1 0
689 mr Marathi mr_IN 1 0
685 ms Malay ms_MY 1 0
687 mt Maltese mt_MT 1 0
609 my Burmese my_MM 1 0
692 na Nauru na_NR 1 0
694 nb Norwegian Bokmål nb_NO 1 0
695 nd North Ndebele nd_ZW 1 0
696 ne Nepali ne_NP 1 0
697 ng Ndonga ng_NA 1 0
1314 nl Dutch (Belgium) nl_BE 1 0
623 nl Dutch (Netherlands) nl_NL 1 0
2487 nl Dutch (Suriname) nl_SR 1 0
2506 nl Dutch (South Africa) nl_ZA 1 0
698 nn Norwegian Nynorsk nn_NO 1 0
699 no Norwegian no_NO 1 0
701 nr South Ndebele nr_ZA 1 0
693 nv Navajo, Navaho nv_US 1 0
613 ny Chichewa; Chewa; Nyanja ny_MW 1 0
6526 oc Occitan (Spain) oc_ES 1 0
702 oc Occitan (after 1500) oc_FR 1 0
703 oj Ojibwa oj_CA 1 0
705 om Oromo om_ET 1 0
706 or Oriya or_IN 1 0
707 os Ossetian, Ossetic os_GE 1 0
708 pa Panjabi, Punjabi pa_IN 1 0
709 pi Pāli pi_KH 1 0
2344 pl Polish (Slovakia) pl_SK 1 0
2869 pl Polish (Ukraine) pl_UA 1 0
2400 pl Polish (Czech Republic) pl_CZ 1 0
3992 pl Polish (Lithuania) pl_LT 1 0
4003 pl Polish (Romania) pl_RO 1 0
2986 pl Polish (Hungary) pl_HU 1 0
711 pl Polish (Poland) pl_PL 1 0
712 ps Pashto, Pushto ps_AF 1 0
812 pt Portuguese (Brazil) pt_BR 1 0
9122 pt Portuguese (Angola) pt_AO 1 0
9123 pt Portuguese (Guinea-Bissau) pt_GW 1 0
9124 pt Portuguese (Cabo Verde) pt_CV 1 0
9125 pt Portuguese (Equatorial Guinea) pt_GQ 1 0
9126 pt Portuguese (Mozambique) pt_MZ 1 0
9127 pt Portuguese (Timor-Leste) pt_TL 1 0
713 pt Portuguese (Portugal) pt_PT 1 0
714 qu Quechua qu_PE 1 0
715 rm Romansh rm_CH 1 0
716 rn Kirundi rn_BI 1 0
717 ro Romanian, Moldavian, Moldovan ro_RO 1 0
2606 ru Russian (Kazakhstan) ru_KZ 1 0
6051 ru Russian (Belarus) ru_BY 1 0
718 ru Russian ru_RU 1 0
6124 ru Russian (Kyrgyzstan) ru_KG 1 0
666 rw Kinyarwanda rw_RW 1 0
719 sa Sanskrit sa_IN 1 0
720 sc Sardinian sc_IT 1 0
721 sd Sindhi sd_IN 1 0
722 se Northern Sami se_NO 1 0
724 sg Sango sg_CF 1 0
728 si Sinhala, Sinhalese si_LK 1 0
729 sk Slovak sk_SK 1 0
730 sl Slovene sl_SI 1 0
723 sm Samoan sm_WS 1 0
727 sn Shona sn_ZW 1 0
731 so Somali so_SO 1 0
589 sq Albanian (sq) sq_AL 1 0
725 sr Serbian sr_RS 1 0
736 ss Swati ss_ZA 1 0
732 st Southern Sotho st_ZA 1 0
734 su Sundanese su_ID 1 0
6066 sv Swedish (Finland) sv_FI 1 0
2242 sv Swedish (Åland Islands) sv_AX 1 0
737 sv Swedish sv_SE 1 0
735 sw Swahili sw_TZ 1 0
738 ta Tamil ta_IN 1 0
739 te Telugu te_IN 1 0
740 tg Tajik tg_TJ 1 0
741 th Thai th_TH 1 0
742 ti Tigrinya ti_ET 1 0
744 tk Turkmen tk_TM 1 0
745 tl Tagalog tl_PH 1 0
746 tn Tswana tn_ZA 1 0
747 to Tonga (Tonga Islands) to_TO 1 0
4308 tr Turkish (Cyprus) tr_CY 1 0
748 tr Turkish tr_TR 1 0
749 ts Tsonga ts_ZA 1 0
750 tt Tatar tt_RU 1 0
751 tw Twi tw_GH 1 0
752 ty Tahitian ty_PF 1 0
753 ug Uighur, Uyghur ug_CN 1 0
754 uk Ukrainian uk_UA 1 0
755 ur Urdu ur_PK 1 0
756 uz Uzbek uz_UZ 1 0
757 ve Venda ve_ZA 1 0
758 vi Vietnamese vi_VN 1 0
759 vo Volapük vo_XX 1 0
760 wa Walloon wa_BE 1 0
762 wo Wolof wo_SN 1 0
764 xh Xhosa xh_ZA 1 0
765 yi Yiddish yi_US 1 0
766 yo Yoruba yo_NG 1 0
807 zh Chinese (Taiwan) zh_TW 1 0
1597 zh Chinese (Hong Kong) zh_HK 1 0
614 zh Chinese (China) zh_CN 1 0
768 zu Zulu zu_ZA 1 0

227 rows in set (0.002 sec)



Subpages