Obsolete:Fundraising/tech/deployments/recurring globalcollect

This page is kept for historical interest. Don't expect its contents to be relevant to the current day.

Show Stoppers

None right now

Deploying Recurring GlobalCollect

Several of the steps for this deployment have already been completed:

Code has been deployed to production.
The Recurring GlobalCollect Processor has been installed on production.
Standalone adapter has been installed.

Setting up the Module in CiviCRM

Please edit the settings for Recurring GlobalCollect

Items to be set:

Email Notification Address: Will this be fr-tech or something else?
Global Collect Merchant ID: Set this to the wikimedia id granted by GlobalCollect
Maximum number of payments to batch: We will probably keep this low, at first, for testing.
Cron batch size: We will probably keep this low, at first, for testing.

SQL Migration query

Check the numbers in `civicrm_contribution`

SELECT COUNT(*) FROM `civicrm_contribution` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';
+----------+
| COUNT(*) |
+----------+
|     1440 |
+----------+

SELECT COUNT(*) FROM `civicrm_contribution` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' AND contribution_recur_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|     1435 |
+----------+

SELECT COUNT(*) FROM `civicrm_contribution` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' AND contribution_recur_id IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

Current total of Recurring GlobalCollect contributions.

SELECT SUM(total_amount) FROM `civicrm_contribution` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';
+-------------------+
| SUM(total_amount) |
+-------------------+
|          13298.44 |
+-------------------+

SELECT SUM(total_amount) FROM `civicrm_contribution` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' AND contribution_recur_id IS NULL;
+-------------------+
| SUM(total_amount) |
+-------------------+
|          13213.44 |
+-------------------+

Check the numbers in `civicrm_contribution_recur`

SELECT SUM(amount) FROM `civicrm_contribution_recur` WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';
+-------------------+
| SUM(total_amount) |
+-------------------+
|          13298.44 |
+-------------------+

SELECT COUNT(*) 
FROM `civicrm_contribution_recur` 
WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';
+----------+
| COUNT(*) |
+----------+
|     1440 |
+----------+

Should this be 1435?

Not a big deal, but we are going to have to backfill all of the contribution_recur_id.

SELECT COUNT(*) 
FROM `civicrm_contribution_recur` 
   LEFT JOIN `civicrm_contribution` 
      ON (`civicrm_contribution`.`contribution_recur_id` = `civicrm_contribution_recur`.`id`) 
WHERE `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' AND
   `civicrm_contribution`.`contribution_recur_id` IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|     1440 |
+----------+

This query will send all of the existing recurring contributions to the table `civicrm_contribution_recur` so they can be processed.

This query appears to have already been ran.

INSERT INTO `civicrm_contribution_recur` (
	`civicrm_contribution_recur`.`contact_id`, 
	`civicrm_contribution_recur`.`amount`, 
	`civicrm_contribution_recur`.`currency`, 
	`civicrm_contribution_recur`.`frequency_unit`, 
	`civicrm_contribution_recur`.`frequency_interval`, 
	`civicrm_contribution_recur`.`installments`, 
	`civicrm_contribution_recur`.`start_date`, 
	`civicrm_contribution_recur`.`create_date`, 
	`civicrm_contribution_recur`.`modified_date`, 
	`civicrm_contribution_recur`.`processor_id`, 
	`civicrm_contribution_recur`.`trxn_id`, 
	`civicrm_contribution_recur`.`contribution_status_id`, 
	`civicrm_contribution_recur`.`cycle_day`, 
	`civicrm_contribution_recur`.`next_sched_contribution`
) SELECT 
	`civicrm_contribution`.`contact_id`, 
	`civicrm_contribution`.`total_amount` AS `amount`, 
	`civicrm_contribution`.`currency`, 
	'month' as `frequency_unit`,
	1 AS `frequency_interval`,
	0 AS `installments`,
	`civicrm_contribution`.`receive_date` AS `start_date`,
	`civicrm_contribution`.`receive_date` AS `create_date`, 
	NOW() AS `modified_date`, 
	1 AS `processor_id`,
	SUBSTRING_INDEX(`civicrm_contribution`.`trxn_id`, ' ', 3 ) AS `trxn_id`, 
	`civicrm_contribution`.`contribution_status_id`, 
	DATE_FORMAT(`civicrm_contribution`.`receive_date`, '%e') AS `cycle_day`, 
	(`civicrm_contribution`.`receive_date` + INTERVAL 1 MONTH) AS `next_sched_contribution`
FROM `civicrm_contribution` 
WHERE `civicrm_contribution`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
	AND contribution_recur_id IS NULL;

It appears that all of the GC recurring contributions are set to run on 2012-05-16:

SELECT COUNT(*) 
FROM `civicrm_contribution_recur` 
WHERE `trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' 
   AND next_sched_contribution LIKE '2012-05-16%';
+----------+
| COUNT(*) |
+----------+
|     1440 |
+----------+

Updates for r1619

There are few SQL updates that will need to be run after r1619 has been released to production.

These queries have been tested.

-- Fix empty `civicrm_contribution`.contribution_recur_id --
SELECT COUNT(*) FROM `civicrm_contribution`
 WHERE (`civicrm_contribution`.contribution_recur_id = '' || `civicrm_contribution`.contribution_recur_id IS NULL)
 AND `civicrm_contribution`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';

SELECT * FROM `civicrm_contribution`
 WHERE (`civicrm_contribution`.contribution_recur_id = '' || `civicrm_contribution`.contribution_recur_id IS NULL)
 AND `civicrm_contribution`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
 LIMIT 10;

-- Cannot use LIMIT or ORDER BY when joining tables with UPDATE --

UPDATE `civicrm_contribution`
 JOIN `civicrm_contribution_recur` ON (`civicrm_contribution`.`trxn_id` = `civicrm_contribution_recur`.`trxn_id`)
 SET `civicrm_contribution`.`contribution_recur_id` = `civicrm_contribution_recur`.`id`
 WHERE (`civicrm_contribution`.contribution_recur_id IS NULL)
 AND `civicrm_contribution`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';
 

UPDATE `civicrm_contribution`
 JOIN `civicrm_contribution_recur` ON ( `civicrm_contribution`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%' && `civicrm_contribution`.`trxn_id` = `civicrm_contribution_recur`.`trxn_id` && `civicrm_contribution`.contribution_recur_id IS NULL)
 SET `civicrm_contribution`.`contribution_recur_id` = `civicrm_contribution_recur`.`id`;
  
-- Fix `civicrm_contribution_recur`.next_sched_contribution --
-- This only fixes Recurring GlobalCollect and not PayPal or PayfloPro

SELECT DISTINCT(`next_sched_contribution`) FROM `civicrm_contribution_recur`;

SELECT COUNT(*) FROM `civicrm_contribution_recur`
 WHERE `civicrm_contribution_recur`.next_sched_contribution = '2012-05-13 00:00:00'
 AND `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%';

SELECT COUNT(*) FROM `civicrm_contribution_recur`
 WHERE `civicrm_contribution_recur`.next_sched_contribution = '2012-05-13 00:00:00';
 
SELECT * FROM `civicrm_contribution_recur`
 WHERE `civicrm_contribution_recur`.next_sched_contribution = '2012-05-13 00:00:00'
 AND `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
 LIMIT 10;

UPDATE `civicrm_contribution_recur`
 SET `civicrm_contribution_recur`.`next_sched_contribution` = (`civicrm_contribution_recur`.`create_date` + INTERVAL 1 MONTH)
 WHERE `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
LIMIT 1;

Testing on production

After settings for Recurring GlobalCollect have been edited properly, we can begin testing:

Running drush

drush rg --batch=1

We will run one at a time to verify everything is processing properly.

Catching up on missed payments

We have set `civicrm_contribution_recur`.`next_sched_contribution` to NULL.

The first step is to set next_sched_contribution:

Note: remove the LIMIT statement.

UPDATE `civicrm_contribution_recur`
 SET `civicrm_contribution_recur`.`next_sched_contribution` = (`civicrm_contribution_recur`.`create_date` + INTERVAL 1 MONTH)
 WHERE `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
 AND `civicrm_contribution_recur`.`next_sched_contribution` IS NULL
LIMIT 1;

UPDATE based on `civicrm_contribution_recur`.`cycle_day` = DAYOFMONTH(CURRENT_DATE):

UPDATE `civicrm_contribution_recur`
 SET `civicrm_contribution_recur`.`next_sched_contribution` = (`civicrm_contribution_recur`.`create_date` + INTERVAL 1 MONTH)
 WHERE `civicrm_contribution_recur`.`trxn_id` LIKE 'RECURRING GLOBALCOLLECT%'
 AND `civicrm_contribution_recur`.`cycle_day` = DAYOFMONTH(CURRENT_DATE)
 AND `civicrm_contribution_recur`.`next_sched_contribution` IS NULL
LIMIT 1;

Important: We may want to set

Catch up missed days: 1

Since we want to maintain the cycle day, we will batch all payments this way until we are caught up:

drush rg --date=2011-12-17

drush rg --date=2012-01-17

drush rg --date=2012-02-17

drush rg --date=2012-03-17

drush rg --date=2012-04-17

CiviCRM status codes

Make sure you have the appropriate status codes for recurring. These should be installed by default.

Get the `option_group_id` for your install.

Only install fields that are missing:

INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `domain_id`, `visibility_id`) VALUES
(11, 'Completed', '1', 'Completed', NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL, NULL, NULL),
(11, 'Pending', '2', 'Pending', NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL, NULL, NULL),
(11, 'Cancelled', '3', 'Cancelled', NULL, 0, NULL, 3, NULL, 0, 1, 1, NULL, NULL, NULL),
(11, 'Failed', '4', 'Failed', NULL, 0, NULL, 4, NULL, 0, 1, 1, NULL, NULL, NULL),
(11, 'In Progress', '5', 'In Progress', NULL, 0, NULL, 5, NULL, 0, 1, 1, NULL, NULL, NULL),
(11, 'Overdue', '6', 'Overdue', NULL, 0, NULL, 6, NULL, 0, 1, 1, NULL, NULL, NULL);