| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql |
| — | — | @@ -7,11 +7,14 @@ |
| 8 | 8 | civicrm.civicrm_contact.last_name as last_name, |
| 9 | 9 | civicrm.civicrm_country.name as name, |
| 10 | 10 | civicrm.civicrm_country.iso_code as iso_code, |
| 11 | | -converted_amount as amount |
| | 11 | +converted_amount as amount, |
| | 12 | +if(first_don.first_don_date < ecomm.stamp, 'no','yes') as is_first_donation, |
| | 13 | +first_don.first_don_date as date_of_first |
| 12 | 14 | |
| 13 | 15 | |
| 14 | 16 | from |
| 15 | 17 | ( |
| | 18 | + |
| 16 | 19 | select |
| 17 | 20 | DATE_FORMAT(receive_date, '%sY-%sm-%sd %sH') as stamp, |
| 18 | 21 | civicrm.civicrm_contribution.id, |
| — | — | @@ -26,6 +29,24 @@ |
| 27 | 30 | join civicrm.civicrm_address on civicrm.civicrm_contact.id = civicrm.civicrm_address.contact_id |
| 28 | 31 | join civicrm.civicrm_country on civicrm.civicrm_address.country_id = civicrm.civicrm_country.id |
| 29 | 32 | |
| 30 | | -where ecomm.converted_amount >= 100 |
| | 33 | +left join |
| | 34 | + |
| | 35 | +(select |
| | 36 | + |
| | 37 | +first_name, |
| | 38 | +last_name, |
| | 39 | +min(receive_date) as first_don_date |
| | 40 | + |
| | 41 | +from civicrm.civicrm_contribution left join civicrm.public_reporting on civicrm.civicrm_contribution.id = civicrm.public_reporting.contribution_id |
| | 42 | +join civicrm.civicrm_contact on civicrm.public_reporting.contribution_id = civicrm.civicrm_contact.id |
| | 43 | +join civicrm.civicrm_address on civicrm.civicrm_contact.id = civicrm.civicrm_address.contact_id |
| | 44 | +join civicrm.civicrm_country on civicrm.civicrm_address.country_id = civicrm.civicrm_country.id |
| | 45 | +where receive_date < '%s' |
| | 46 | +group by 1,2) as first_don |
| | 47 | + |
| | 48 | +on first_don.first_name = civicrm.civicrm_contact.first_name and first_don.last_name = civicrm.civicrm_contact.last_name |
| | 49 | + |
| | 50 | + |
| | 51 | +where ecomm.converted_amount >= 100 |
| | 52 | + |
| 31 | 53 | group by 1; |
| 32 | | - |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/query_store.py |
| — | — | @@ -122,7 +122,7 @@ |
| 123 | 123 | elif query_name == 'report_ecomm_by_amount': |
| 124 | 124 | start_time = args[0] |
| 125 | 125 | end_time = args[1] |
| 126 | | - sql_stmnt = sql_stmnt % ('%', '%', '%', '%', start_time, end_time) |
| | 126 | + sql_stmnt = sql_stmnt % ('%', '%', '%', '%', start_time, end_time, end_time) |
| 127 | 127 | |
| 128 | 128 | elif query_name == 'report_ecomm_by_contact': |
| 129 | 129 | where_str = args[0] |
| — | — | @@ -145,7 +145,7 @@ |
| 146 | 146 | if query_name == 'report_contribution_tracking': |
| 147 | 147 | return ['Time','Banner','Landing Page','Campaign','Converted Amount', 'Suffix'] |
| 148 | 148 | elif query_name == 'report_ecomm_by_amount': |
| 149 | | - return ['Timestamp','First Name','Last Name','Country','ISO Code', 'Amount'] |
| | 149 | + return ['Timestamp','First Name','Last Name','Country','ISO Code', 'Amount', 'First Donation?', 'Date of First'] |
| 150 | 150 | elif query_name == 'report_ecomm_by_contact': |
| 151 | 151 | return ['Timestamp','First Name','Last Name','Country','ISO Code', 'Amount'] |
| 152 | 152 | else: |