| Index: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py |
| — | — | @@ -177,6 +177,106 @@ |
| 178 | 178 | |
| 179 | 179 | |
| 180 | 180 | |
| | 181 | + """ |
| | 182 | + |
| | 183 | + Formats date string to match the form of civicrm.civicrm_contribution.recieve_date |
| | 184 | + |
| | 185 | + format 1 - 20080101000606 |
| | 186 | + format 2 - 2008-01-01 00:06:06 |
| | 187 | + |
| | 188 | + input: |
| | 189 | + |
| | 190 | + now - python datetime object |
| | 191 | + hours_back - the amount of time the |
| | 192 | + format - the format of the returned timestamp strings |
| | 193 | + resolution - the |
| | 194 | + |
| | 195 | + |
| | 196 | + returns - formatted datetime strings |
| | 197 | + |
| | 198 | + """ |
| | 199 | + def gen_date_strings(self, now, hours_back, format, resolution): |
| | 200 | + |
| | 201 | + delta = datetime.timedelta(hours=-hours_back) |
| | 202 | + |
| | 203 | + time_obj = now + delta |
| | 204 | + now = now + datetime.timedelta(hours=-1) # Move an hour back to terminate at 55 minute |
| | 205 | + |
| | 206 | + # Cast the start and end time strings in the proper format |
| | 207 | + start_time = self.timestamp_from_obj(time_obj, format, resolution) |
| | 208 | + end_time = self.timestamp_from_obj(now, format, resolution) |
| | 209 | + |
| | 210 | + return [start_time, end_time] |
| | 211 | + |
| | 212 | + |
| | 213 | + |
| | 214 | + """ |
| | 215 | + |
| | 216 | + Formats date string to match the form of civicrm.civicrm_contribution.recieve_date |
| | 217 | + |
| | 218 | + format 1 - 20080101000606 |
| | 219 | + format 2 - 2008-01-01 00:06:06 |
| | 220 | + |
| | 221 | + input: |
| | 222 | + |
| | 223 | + time_obj - python datetime object |
| | 224 | + hours_back - the amount of time the |
| | 225 | + format - the format of the returned timestamp strings |
| | 226 | + resolution - |
| | 227 | + |
| | 228 | + |
| | 229 | + returns - formatted datetime strings |
| | 230 | + |
| | 231 | + """ |
| | 232 | + def timestamp_from_obj(self, time_obj, format, resolution): |
| | 233 | + |
| | 234 | + if time_obj.month < 10: |
| | 235 | + month = '0' + str(time_obj.month) |
| | 236 | + else: |
| | 237 | + month = str(time_obj.month) |
| | 238 | + |
| | 239 | + if time_obj.day < 10: |
| | 240 | + day = '0' + str(time_obj.day) |
| | 241 | + else: |
| | 242 | + day = str(time_obj.day) |
| | 243 | + |
| | 244 | + if time_obj.hour < 10: |
| | 245 | + hour = '0' + str(time_obj.hour) |
| | 246 | + else: |
| | 247 | + hour = str(time_obj.hour) |
| | 248 | + |
| | 249 | + if time_obj.minute < 10: |
| | 250 | + minute = '0' + str(time_obj.minute) |
| | 251 | + else: |
| | 252 | + minute = str(time_obj.minute) |
| | 253 | + |
| | 254 | + if time_obj.second < 10: |
| | 255 | + second = '0' + str(time_obj.second) |
| | 256 | + else: |
| | 257 | + second = str(time_obj.second) |
| | 258 | + |
| | 259 | + # Cast the start and end time strings in the proper format |
| | 260 | + if format == 1: |
| | 261 | + |
| | 262 | + if resoution == 1: |
| | 263 | + timestamp = str(time_obj.year) + month + day + hour + '0000' |
| | 264 | + elif resoution == 2: |
| | 265 | + timestamp = str(time_obj.year) + month + day + hour + minute + '00' |
| | 266 | + elif resoution == 3: |
| | 267 | + timestamp = str(time_obj.year) + month + day + hour + minute + second |
| | 268 | + |
| | 269 | + elif format == 2: |
| | 270 | + |
| | 271 | + if resolution == 1: |
| | 272 | + timestamp = str(time_obj.year) + '-' + month + '-' + day + ' ' + hour + ':00:00' |
| | 273 | + elif resolution == 2: |
| | 274 | + timestamp = str(time_obj.year) + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':00' |
| | 275 | + elif resolution == 3: |
| | 276 | + timestamp = str(time_obj.year) + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second |
| | 277 | + |
| | 278 | + return timestamp |
| | 279 | + |
| | 280 | + |
| 181 | 281 | """ |
| 182 | 282 | |
| 183 | 283 | CLASS :: ^FundraiserReporting^ |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_contact.sql |
| — | — | @@ -13,12 +13,12 @@ |
| 14 | 14 | from |
| 15 | 15 | ( |
| 16 | 16 | select |
| 17 | | -DATE_FORMAT(ts, '%sY-%sm-%sd %sH') as stamp, |
| 18 | | -contribution_tracking.contribution_id, |
| | 17 | +DATE_FORMAT(receive_date, '%sY-%sm-%sd %sH') as stamp, |
| | 18 | +civicrm.civicrm_contribution.id, |
| 19 | 19 | converted_amount, |
| 20 | | -contact_id |
| | 20 | +civicrm.public_reporting.contact_id |
| 21 | 21 | |
| 22 | | -from drupal.contribution_tracking left join civicrm.public_reporting on contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id |
| | 22 | +from civicrm.civicrm_contribution left join civicrm.public_reporting on civicrm.civicrm_contribution.id = civicrm.public_reporting.contribution_id |
| 23 | 23 | ) as ecomm |
| 24 | 24 | |
| 25 | 25 | join civicrm.civicrm_contact on ecomm.contact_id = civicrm.civicrm_contact.id |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql |
| — | — | @@ -13,13 +13,13 @@ |
| 14 | 14 | from |
| 15 | 15 | ( |
| 16 | 16 | select |
| 17 | | -DATE_FORMAT(ts, '%sY-%sm-%sd %sH') as stamp, |
| 18 | | -contribution_tracking.contribution_id, |
| | 17 | +DATE_FORMAT(receive_date, '%sY-%sm-%sd %sH') as stamp, |
| | 18 | +civicrm.civicrm_contribution.id, |
| 19 | 19 | converted_amount, |
| 20 | | -contact_id |
| | 20 | +civicrm.public_reporting.contact_id |
| 21 | 21 | |
| 22 | | -from drupal.contribution_tracking left join civicrm.public_reporting on contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id |
| 23 | | -where ts > '%s' and ts <= '%s' |
| | 22 | +from civicrm.civicrm_contribution left join civicrm.public_reporting on civicrm.civicrm_contribution.id = civicrm.public_reporting.contribution_id |
| | 23 | +where receive_date > '%s' and receive_date <= '%s' |
| 24 | 24 | ) as ecomm |
| 25 | 25 | |
| 26 | 26 | join civicrm.civicrm_contact on ecomm.contact_id = civicrm.civicrm_contact.id |
| — | — | @@ -28,3 +28,4 @@ |
| 29 | 29 | |
| 30 | 30 | where ecomm.converted_amount >= 100 |
| 31 | 31 | group by 1; |
| | 32 | + |
| Index: trunk/fundraiser-statistics/fundraiser-scripts/mine_contacts.py |
| — | — | @@ -173,8 +173,10 @@ |
| 174 | 174 | now = datetime.datetime.now() |
| 175 | 175 | hours_back = 24 * 7 |
| 176 | 176 | |
| 177 | | - start_time, end_time = self.gen_date_strings_hr(now, hours_back) |
| | 177 | + # start_time, end_time = self.gen_date_strings_hr(now, hours_back) |
| | 178 | + start_time, end_time = self.gen_date_strings(now, hours_back, 2, 3) |
| 178 | 179 | |
| | 180 | + |
| 179 | 181 | # Prepare SQL statements and tables |
| 180 | 182 | table_data = [] |
| 181 | 183 | sql_stmnt = mh.read_sql(self.__sql_path + self.__query_handle_amount + '.sql'); |
| — | — | @@ -184,7 +186,7 @@ |
| 185 | 187 | |
| 186 | 188 | # Formats the statement according to query type |
| 187 | 189 | select_stmnt = self.__query_obj.format_query(self.__query_handle_amount, sql_stmnt, [start_time, end_time]) |
| 188 | | - |
| | 190 | + print select_stmnt |
| 189 | 191 | # initialize the db and execute the query |
| 190 | 192 | self.init_db() |
| 191 | 193 | |
| Index: trunk/fundraiser-statistics/reporting/fundraiser_analytics.html |
| — | — | @@ -33,7 +33,7 @@ |
| 34 | 34 | <h2>Major Donations</h2> |
| 35 | 35 | |
| 36 | 36 | <h3><a href="http://fundraising.wikimedia.org/stats/report_ecomm_by_amount.html">$100+ donors in the last 7 days</a></h3> |
| 37 | | -<h3><a href="http://fundraising.wikimedia.org/stats/report_ecomm_by_contact.html">Major Donor History</a></h3> - derived from the major donor<a href="http://fundraising.wikimedia.org/stats/">list</a> |
| | 37 | +<h3><a href="http://fundraising.wikimedia.org/stats/report_ecomm_by_contact.html">Major Donor History</a> - derived from the major donor <a href="http://fundraising.wikimedia.org/Rebeccas_Contacts_Donation_Alerts.csv">list</a></h3> |
| 38 | 38 | |
| 39 | 39 | <br> |
| 40 | 40 | |