X850MCDF

1
2DROP TABLE IF EXISTS `veda_pcpcivisualize_adventurist_temp_data`;
3
4CREATE TABLE IF NOT EXISTS `veda_pcpcivisualize_adventurist_temp_data` (
5  `contrib_contact_id` int(10) ,
6  `total_amount` decimal(20,2) ,
7  `receive_date` datetime DEFAULT NULL,
8  `received_year` varchar(255),
9  `contribution_status_id` int(10),
10  `contribution_currency` int(10),
11  `payment_instrument_id` int(10),
12  `payment_instrument_name` varchar(255),
13  `is_test` tinyint(4),
14  `soft_id` int(10),
15  `contribution_id` int(10),
16  `soft_contact_id` int(10),
17  `amount` decimal(20,2),
18  `currency` varchar(3),
19  `pcp_id` int(10),
20  `pcp_block_entity_id` int(10),
21  `pcp_block_entity_table` int(10),
22  `event_id` int(10),
23  `event_title` varchar(255),
24  `event_type_id` int(10),
25  `participant_id` int(10),
26  `participant_contact_id` int(10),
27  PRIMARY KEY (`soft_id`)
28);
29
30-- Base table, insert all soft credit record 
31INSERT INTO `veda_pcpcivisualize_adventurist_temp_data` (soft_id, contribution_id, soft_contact_id, amount, currency, pcp_id)
32  SELECT id, contribution_id, contact_id, amount, currency, pcp_id FROM civicrm_contribution_soft;
33
34-- Contribution data
35UPDATE `veda_pcpcivisualize_adventurist_temp_data` a
36INNER JOIN civicrm_contribution b ON (b.id = a.contribution_id)
37LEFT JOIN civicrm_option_value c ON (c.value = b.payment_instrument_id AND c.option_group_id = '10')
38SET a.contrib_contact_id = b.contact_id
39  , a.total_amount = b.total_amount
40  , a.receive_date = b.receive_date
41  , a.received_year = YEAR(b.receive_date)
42  , a.contribution_status_id = b.contribution_status_id
43  , a.payment_instrument_id = b.payment_instrument_id
44  , a.payment_instrument_name = c.label;
45
46
47-- PCP block data
48UPDATE `veda_pcpcivisualize_adventurist_temp_data` a
49LEFT JOIN civicrm_pcp pcp ON (pcp.id = a.pcp_id)
50LEFT JOIN civicrm_pcp_block pcp_block ON (pcp_block.id = pcp.pcp_block_id)
51SET a.pcp_block_entity_id = pcp_block.entity_id
52  , a.pcp_block_entity_table = pcp_block.entity_table
53;
54
55-- Event data
56UPDATE `veda_pcpcivisualize_adventurist_temp_data` a
57LEFT JOIN civicrm_event event ON (event.id = a.pcp_block_entity_id AND a.pcp_block_entity_table = 'civicrm_event')
58SET a.event_id = event.id
59  , a.event_title = event.title
60  , a.event_type_id = event.event_type_id
61;
62
63
64-- Participant data
65UPDATE `veda_pcpcivisualize_adventurist_temp_data` a
66LEFT JOIN civicrm_participant participant ON (participant.contact_id = a.soft_contact_id AND participant.event_id = a.event_id)
67SET a.participant_id = participant.id
68  , a.participant_contact_id = participant.contact_id
69;