| Index: civicrm/trunk/sites/all/bin/public_reporting/triggers.sql |
| — | — | @@ -5,11 +5,13 @@ |
| 6 | 6 | DROP TRIGGER IF EXISTS public_reporting_insert; |
| 7 | 7 | DROP TRIGGER IF EXISTS public_reporting_update; |
| 8 | 8 | DROP TRIGGER IF EXISTS public_reporting_contact_update; |
| | 9 | +DROP TRIGGER IF EXISTS public_reporting_note_insert; |
| | 10 | +DROP TRIGGER IF EXISTS public_reporting_note_update; |
| 9 | 11 | DELIMITER // |
| 10 | 12 | CREATE TRIGGER public_reporting_insert AFTER INSERT ON civicrm_contribution FOR EACH ROW |
| 11 | 13 | BEGIN |
| 12 | 14 | DECLARE public_name VARCHAR(128) charset utf8; |
| 13 | | - IF SUBSTRING(NEW.source, 1, 3) != 'RFD' THEN |
| | 15 | + IF SUBSTRING(NEW.source, 1, 3) != 'RFD' THEN -- don't trigger for refunds |
| 14 | 16 | SET public_name := (SELECT IF(do_not_trade = 1 or NEW.payment_instrument_id = 4, NULL, SUBSTRING_INDEX(display_name, "@", 1)) |
| 15 | 17 | FROM civicrm_contact WHERE id = NEW.contact_id); |
| 16 | 18 | INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| — | — | @@ -23,7 +25,7 @@ |
| 24 | 26 | BEGIN |
| 25 | 27 | DECLARE anonymous INTEGER; |
| 26 | 28 | DECLARE public_name VARCHAR(128) charset utf8; |
| 27 | | - IF SUBSTRING(NEW.source, 1, 3) = 'RFD' THEN |
| | 29 | + IF SUBSTRING(NEW.source, 1, 3) = 'RFD' THEN -- trigger for refunds |
| 28 | 30 | DELETE from public_reporting |
| 29 | 31 | WHERE public_reporting.contribution_id = NEW.id; |
| 30 | 32 | ELSE |
| — | — | @@ -45,4 +47,14 @@ |
| 46 | 48 | UPDATE public_reporting pr SET pr.name = public_name WHERE pr.contact_id = NEW.id; |
| 47 | 49 | END |
| 48 | 50 | // |
| | 51 | +CREATE TRIGGER public_reporting_note_insert AFTER INSERT ON civicrm_value_1_note_11 FOR EACH ROW |
| | 52 | +BEGIN |
| | 53 | + UPDATE public_reporting pr SET pr.note = NEW.donor_comment WHERE pr.contribution_id = NEW.entity_id; |
| | 54 | +END |
| | 55 | +// |
| | 56 | +CREATE TRIGGER public_reporting_note_update AFTER UPDATE ON civicrm_value_1_note_11 FOR EACH ROW |
| | 57 | +BEGIN |
| | 58 | + UPDATE public_reporting pr SET pr.note = NEW.donor_comment WHERE pr.contribution_id = NEW.entity_id; |
| | 59 | +END |
| | 60 | +// |
| 49 | 61 | DELIMITER ; |
| Index: civicrm/trunk/sites/all/bin/public_reporting/synchronize.sql |
| — | — | @@ -3,8 +3,30 @@ |
| 4 | 4 | INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| 5 | 5 | original_amount, note, received) |
| 6 | 6 | SELECT cn.id, ct.id, IF(ct.do_not_trade, NULL, SUBSTRING_INDEX(ct.display_name, "@", 1)), cn.total_amount, |
| 7 | | - SUBSTRING(cn.source, 1, 3), CONVERT(SUBSTRING(cn.source, 5), DECIMAL(20,2)), cn.note, UNIX_TIMESTAMP(cn.receive_date) |
| | 7 | + SUBSTRING(cn.source, 1, 3), CONVERT(SUBSTRING(cn.source, 5), DECIMAL(20,2)), nt.note, UNIX_TIMESTAMP(cn.receive_date) |
| 8 | 8 | FROM civicrm_contribution cn |
| 9 | 9 | INNER JOIN civicrm_contact ct ON cn.contact_id = ct.id |
| | 10 | + LEFT JOIN civicrm_value_1_note_11 nt ON cn.id = nt.entity_id |
| 10 | 11 | WHERE cn.total_amount >= 1; |
| 11 | 12 | COMMIT; |
| | 13 | + |
| | 14 | +/* Use if donor comments are stored in notes table instead of custom field |
| | 15 | +BEGIN; |
| | 16 | +DELETE FROM public_reporting; |
| | 17 | +CREATE TEMPORARY TABLE temp_notes ( |
| | 18 | + contribution_id int(10) unsigned NOT NULL, |
| | 19 | + note text collate utf8_unicode_ci, |
| | 20 | + PRIMARY KEY (`contribution_id`) |
| | 21 | + ); |
| | 22 | +INSERT INTO temp_notes (contribution_id, note) |
| | 23 | +SELECT entity_id, note FROM civicrm_note WHERE entity_table = 'civicrm_contribution'; |
| | 24 | +INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| | 25 | + original_amount, note, received) |
| | 26 | +SELECT cn.id, ct.id, IF(ct.do_not_trade, NULL, SUBSTRING_INDEX(ct.display_name, "@", 1)), cn.total_amount, |
| | 27 | + SUBSTRING(cn.source, 1, 3), CONVERT(SUBSTRING(cn.source, 5), DECIMAL(20,2)), tn.note, UNIX_TIMESTAMP(cn.receive_date) |
| | 28 | + FROM civicrm_contribution cn |
| | 29 | + INNER JOIN civicrm_contact ct ON cn.contact_id = ct.id |
| | 30 | + LEFT JOIN temp_notes tn ON cn.id = tn.contribution_id |
| | 31 | + WHERE cn.total_amount >= 1; |
| | 32 | +COMMIT; |
| | 33 | +*/ |
| \ No newline at end of file |