| Index: civicrm/trunk/sites/all/bin/public_reporting/triggers.sql |
| — | — | @@ -6,12 +6,7 @@ |
| 7 | 7 | BEGIN |
| 8 | 8 | DECLARE anonymous INTEGER; |
| 9 | 9 | DECLARE display_name VARCHAR(128); |
| 10 | | - SET anonymous := (SELECT do_not_trade FROM civicrm_contact WHERE id = NEW.contact_id); |
| 11 | | - IF anonymous THEN |
| 12 | | - SET display_name := (SELECT display_name FROM civicrm_contact WHERE id = NEW.contact_id); |
| 13 | | - ELSE |
| 14 | | - SET display_name := NULL; |
| 15 | | - END IF; |
| | 10 | + SET display_name := (SELECT IF(do_not_trade, NULL, SUBSTRING_INDEX(display_name, "@", 1)) FROM civicrm_contact WHERE id = NEW.contact_id); |
| 16 | 11 | INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| 17 | 12 | original_amount, note, received) |
| 18 | 13 | VALUES (NEW.id, NEW.contact_id, display_name, NEW.total_amount, SUBSTRING(NEW.source, 1, 3), |
| — | — | @@ -22,12 +17,7 @@ |
| 23 | 18 | BEGIN |
| 24 | 19 | DECLARE anonymous INTEGER; |
| 25 | 20 | DECLARE display_name VARCHAR(128); |
| 26 | | - SET anonymous := (SELECT do_not_trade FROM civicrm_contact WHERE id = NEW.contact_id); |
| 27 | | - IF anonymous THEN |
| 28 | | - SET display_name := (SELECT display_name FROM civicrm_contact WHERE id = NEW.contact_id); |
| 29 | | - ELSE |
| 30 | | - SET display_name := NULL; |
| 31 | | - END IF; |
| | 21 | + SET display_name := (SELECT IF(do_not_trade, NULL, SUBSTRING_INDEX(display_name, "@", 1)) FROM civicrm_contact WHERE id = NEW.contact_id); |
| 32 | 22 | UPDATE public_reporting pr |
| 33 | 23 | SET pr.contact_id = NEW.contact_id, pr.name = display_name, pr.converted_amount = NEW.total_amount, |
| 34 | 24 | pr.original_currency = SUBSTRING(NEW.source, 1, 3), pr.original_amount = SUBSTRING(NEW.source, 5), |
| — | — | @@ -37,9 +27,7 @@ |
| 38 | 28 | // |
| 39 | 29 | CREATE TRIGGER public_reporting_contact_update AFTER UPDATE ON civicrm_contact FOR EACH ROW |
| 40 | 30 | BEGIN |
| 41 | | - IF NEW.do_not_trade THEN |
| 42 | | - UPDATE public_reporting pr SET pr.name = NULL WHERE pr.contact_id = NEW.id; |
| 43 | | - ELSE |
| 44 | | - UPDATE public_reporting pr SET pr.name = NEW.display_name WHERE pr.contact_id = NEW.id; |
| 45 | | - END IF; |
| | 31 | + DECLARE display_name VARCHAR(128); |
| | 32 | + SET display_name := (SELECT IF(do_not_trade, NULL, SUBSTRING_INDEX(display_name, "@", 1)) FROM civicrm_contact WHERE id = NEW.id); |
| | 33 | + UPDATE public_reporting pr SET pr.name = display_name WHERE pr.contact_id = NEW.id; |
| 46 | 34 | END |
| Index: civicrm/trunk/sites/all/bin/public_reporting/synchronize.sql |
| — | — | @@ -2,7 +2,7 @@ |
| 3 | 3 | DELETE FROM public_reporting; |
| 4 | 4 | INSERT INTO public_reporting (contribution_id, contact_id, name, converted_amount, original_currency, |
| 5 | 5 | original_amount, note, received) |
| 6 | | -SELECT cn.id, ct.id, IF(ct.do_not_trade, NULL, ct.display_name), cn.total_amount, |
| | 6 | +SELECT cn.id, ct.id, IF(ct.do_not_trade, NULL, SUBSTRING_INDEX(ct.display_name, "@", 1)), cn.total_amount, |
| 7 | 7 | SUBSTRING(cn.source, 1, 3), CONVERT(SUBSTRING(cn.source, 5), DECIMAL(20,2)), cn.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 |