| Index: civicrm/trunk/sites/all/bin/public_reporting/triggers.sql |
| — | — | @@ -5,20 +5,23 @@ |
| 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 | 9 | DROP TRIGGER IF EXISTS public_reporting_note_update; |
| 11 | 10 | DELIMITER // |
| 12 | | -CREATE TRIGGER public_reporting_insert AFTER INSERT ON civicrm_contribution FOR EACH ROW |
| | 11 | +CREATE TRIGGER public_reporting_insert AFTER INSERT ON civicrm_value_1_note_11 FOR EACH ROW |
| 13 | 12 | BEGIN |
| | 13 | + DECLARE cc_payment_instrument_id INT(10) unsigned DEFAULT NULL; |
| | 14 | + DECLARE cc_contact_id INT(10) unsigned; |
| 14 | 15 | DECLARE public_name VARCHAR(128) charset utf8; |
| 15 | | - IF SUBSTRING(NEW.source, 1, 3) != 'RFD' THEN -- don't trigger for refunds |
| 16 | | - SET public_name := (SELECT IF(do_not_trade = 1 or NEW.payment_instrument_id = 4, NULL, SUBSTRING_INDEX(display_name, "@", 1)) |
| 17 | | - FROM civicrm_contact WHERE id = NEW.contact_id); |
| 18 | | - INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| 19 | | - original_amount, note, received) |
| 20 | | - VALUES (NEW.id, NEW.contact_id, public_name, NEW.total_amount, SUBSTRING(NEW.source, 1, 3), |
| 21 | | - SUBSTRING(NEW.source, 5), NEW.note, UNIX_TIMESTAMP(NEW.receive_date)); |
| 22 | | - END IF; |
| | 16 | + SET cc_payment_instrument_id := (SELECT payment_instrument_id FROM civicrm_contribution WHERE id = NEW.entity_id); |
| | 17 | + SET cc_contact_id := (SELECT contact_id FROM civicrm_contribution WHERE id = NEW.entity_id); |
| | 18 | + IF SUBSTRING((SELECT source FROM civicrm_contribution WHERE id = NEW.entity_id LIMIT 1), 1, 3) != 'RFD' THEN -- don't trigger for refunds |
| | 19 | + SET public_name := (SELECT IF(do_not_trade = 1 or cc_payment_instrument_id = 4, NULL, SUBSTRING_INDEX(display_name, "@", 1)) |
| | 20 | + FROM civicrm_contact WHERE id = cc_contact_id); |
| | 21 | + INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, original_amount, note, received) |
| | 22 | + SELECT cc.id, cc.contact_id, public_name, cc.total_amount, |
| | 23 | + SUBSTRING(cc.source, 1, 3), SUBSTRING(cc.source, 5), NEW.donor_comment, UNIX_TIMESTAMP(cc.receive_date) |
| | 24 | + FROM civicrm_contribution cc WHERE cc.id = NEW.entity_id; |
| | 25 | + END IF; |
| 23 | 26 | END |
| 24 | 27 | // |
| 25 | 28 | CREATE TRIGGER public_reporting_update AFTER UPDATE ON civicrm_contribution FOR EACH ROW |
| — | — | @@ -47,11 +50,6 @@ |
| 48 | 51 | UPDATE public_reporting pr SET pr.name = public_name WHERE pr.contact_id = NEW.id; |
| 49 | 52 | END |
| 50 | 53 | // |
| 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 | 54 | CREATE TRIGGER public_reporting_note_update AFTER UPDATE ON civicrm_value_1_note_11 FOR EACH ROW |
| 57 | 55 | BEGIN |
| 58 | 56 | UPDATE public_reporting pr SET pr.note = NEW.donor_comment WHERE pr.contribution_id = NEW.entity_id; |
| Index: civicrm/trunk/sites/all/bin/public_reporting/synchronize.sql |
| — | — | @@ -3,14 +3,14 @@ |
| 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)), nt.note, UNIX_TIMESTAMP(cn.receive_date) |
| | 7 | + SUBSTRING(cn.source, 1, 3), CONVERT(SUBSTRING(cn.source, 5), DECIMAL(20,2)), nt.donor_comment, 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 | 10 | LEFT JOIN civicrm_value_1_note_11 nt ON cn.id = nt.entity_id |
| 11 | 11 | WHERE cn.total_amount >= 1; |
| 12 | 12 | COMMIT; |
| 13 | 13 | |
| 14 | | -/* Use if donor comments are stored in notes table instead of custom field |
| | 14 | +/* Use if donor comments are stored in notes table instead of custom data field |
| 15 | 15 | BEGIN; |
| 16 | 16 | DELETE FROM public_reporting; |
| 17 | 17 | CREATE TEMPORARY TABLE temp_notes ( |