http://www.mediawiki.org/wiki/Special:Code/MediaWiki/82951
Revision: 82951
Author: rfaulk
Date: 2011-02-28 20:15:30 +0000 (Mon, 28 Feb 2011)
Log Message:
-----------
Adding reporting functionality to track major gift donors.
Modified Paths:
--------------
trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
trunk/fundraiser-statistics/fundraiser-scripts/test_reporting.py
Added Paths:
-----------
trunk/fundraiser-statistics/bash/report_major_gifts.sh
trunk/fundraiser-statistics/fundraiser-scripts/mine_contacts.py
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_contact.sql
Added: trunk/fundraiser-statistics/bash/report_major_gifts.sh
===================================================================
--- trunk/fundraiser-statistics/bash/report_major_gifts.sh
(rev 0)
+++ trunk/fundraiser-statistics/bash/report_major_gifts.sh 2011-02-28
20:15:30 UTC (rev 82951)
@@ -0,0 +1,8 @@
+#!/bin/bash
+
+cd /home/rfaulk/fundraiser-statistics/fundraiser-scripts
+
+python mine_contacts.py c
+
+cp
/home/rfaulk/fundraiser-statistics/fundraiser-scripts/html/report_ecomm_by_contact.html
/srv/org.wikimedia.fundraising/stats/
+
Modified: trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
===================================================================
--- trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
2011-02-28 19:47:39 UTC (rev 82950)
+++ trunk/fundraiser-statistics/fundraiser-scripts/fundraiser_reporting.py
2011-02-28 20:15:30 UTC (rev 82951)
@@ -25,43 +25,12 @@
import query_store as qs
import miner_help as mh
-"""
-CLASS :: ^FundraiserReporting^
-
-Base class for reporting fundraiser analytics. Methods that are intended to
be extended in derived classes include:
-
-run_query()
-run()
-gen_plot()
-publish_google_sheet()
-write_to_html_table()
-
-
-"""
-class FundraiserReporting:
-
- # Database and Cursor objects
- db = None
- cur = None
+class TimestampProcesser(object):
- def init_db(self):
- """ Establish connection """
- #db = MySQLdb.connect(host='db10.pmtpa.wmnet', user='rfaulk',
db='faulkner')
- #self.db = MySQLdb.connect(host='127.0.0.1', user='rfaulk',
db='faulkner', port=3307)
- self.db = MySQLdb.connect(host='storage3.pmtpa.wmnet',
user='rfaulk', db='faulkner')
-
- """ Create cursor """
- self.cur = self.db.cursor()
-
- def close_db(self):
- self.cur.close()
- self.db.close()
-
-
"""
- Takes as input and converts it to a set of hours counting back
from 0
+ Takes a list of timestamps as input and converts it to a set of
hours counting back from 0
time_lists - a list of timestamp lists
time_norm - a dictionary of normalized times
@@ -206,6 +175,44 @@
return [start_time, end_time]
+
+
+"""
+
+CLASS :: ^FundraiserReporting^
+
+Base class for reporting fundraiser analytics. Methods that are intended to
be extended in derived classes include:
+
+run_query()
+run()
+gen_plot()
+publish_google_sheet()
+write_to_html_table()
+
+
+"""
+class FundraiserReporting(TimestampProcesser):
+
+ # Database and Cursor objects
+ db = None
+ cur = None
+
+ def init_db(self):
+ """ Establish connection """
+ #db = MySQLdb.connect(host='db10.pmtpa.wmnet', user='rfaulk',
db='faulkner')
+ #self.db = MySQLdb.connect(host='127.0.0.1', user='rfaulk',
db='faulkner', port=3307)
+ self.db = MySQLdb.connect(host='storage3.pmtpa.wmnet',
user='rfaulk', db='faulkner')
+
+ """ Create cursor """
+ self.cur = self.db.cursor()
+
+ def close_db(self):
+ self.cur.close()
+ self.db.close()
+
+
+
+
"""
def smooth::
Added: trunk/fundraiser-statistics/fundraiser-scripts/mine_contacts.py
===================================================================
--- trunk/fundraiser-statistics/fundraiser-scripts/mine_contacts.py
(rev 0)
+++ trunk/fundraiser-statistics/fundraiser-scripts/mine_contacts.py
2011-02-28 20:15:30 UTC (rev 82951)
@@ -0,0 +1,264 @@
+
+"""
+
+mine_contacts.py
+
+wikimediafoundation.org
+Ryan Faulkner
+February 11th, 2010
+
+"""
+
+# =============================================
+# Pulls metrics from database to perform statistical analysis
+# =============================================
+
+import sys
+import getopt
+import re
+import datetime
+
+import MySQLdb
+import HTML
+
+import test_reporting as tr
+import miner_help as mh
+import query_store as qs
+import fundraiser_reporting as fr
+
+
+
+class contact_handler(tr.data_handler, fr.TimestampProcesser):
+
+ __query_obj = qs.query_store()
+ __file_name = './csv/Rebeccas_Contacts_Donation_Alerts_mod.csv'
+ __sql_path = './sql/'
+ __html_path = './html/'
+ __query_handle_contact = 'report_ecomm_by_contact'
+ __query_handle_amount = 'report_ecomm_by_amount'
+
+
+
+ def __init__(self):
+ super(tr.data_handler, self).__init__()
+
+ """
+
+ method :: parse_contacts:
+
+ Constants:
+ =========
+
+ file_name = ./csv/Rebeccas_Contacts_Donation_Alerts.csv
+
+ """
+
+ def parse_contacts(self):
+
+ # Initialization - open the file
+ # FileName = sys.argv[1];
+ if (re.search('\.gz',self.__file_name)):
+ file_obj = gzip.open(self.__file_name, 'r')
+ else:
+ file_obj = open(self.__file_name, 'r')
+
+
+ # Sample from csv file
+ # Internal Contact ID, Sort Name, First Name, Last
Name, Contact Type
+ # 120842, "Abramowicz, David", David, Abramowicz,
Individual
+ index_first_name = 3
+ index_last_name = 4
+ contact_list = list()
+
+
+ # PROCESS LOG FILE
+ # ================
+ line = file_obj.readline()
+ while (line != ''):
+
+ lineArgs = line.split(',')
+ # print lineArgs[index_first_name] + ' ' +
lineArgs[index_last_name]
+ contact_list.append([lineArgs[index_first_name],
lineArgs[index_last_name]])
+ line = file_obj.readline()
+
+ return contact_list
+
+
+ """
+
+ Civi Reporting - Create a table from a list of contacts
+
+ This method looks at
+
+ Constants:
+ =========
+
+ file_name = ./sql/Rebeccas_Contacts_Donation_Alerts.csv
+
+
+ """
+ def build_html_table_by_contact(self):
+
+
+ # get the contacts from the list
+ list = self.parse_contacts()
+
+ table_data = []
+ sql_stmnt = mh.read_sql(self.__sql_path +
self.__query_handle_contact + '.sql');
+
+ # open the html files for writing
+ f = open(self.__html_path + self.__query_handle_contact +
'.html', 'w')
+
+ # construct contact where string
+ # iterate through the list
+ where_str = 'where '
+ first_item = 0
+ for i in list[1:]:
+
+ # only process People now - not organizations or
Households
+ if not(re.search('Organization', i[0]) or
re.search('Organization', i[1]) or re.search('Household', i[0]) or
re.search('Household', i[1])):
+ if not(first_item):
+ first_item = 1
+ else:
+ where_str = where_str + ' or '
+
+ where_str = where_str + '(first_name = \'' +
i[0].strip() + '\' and last_name = \'' + i[1].strip() + '\')'
+
+ # Formats the statement according to query type
+ select_stmnt =
self.__query_obj.format_query(self.__query_handle_contact, sql_stmnt,
[where_str])
+ print select_stmnt
+
+ # initialize the db and execute the query
+ self.init_db()
+
+ try:
+
+ # execute statement gathering amounts
+ err_msg = select_stmnt
+ self._cur.execute(select_stmnt)
+ results = self._cur.fetchall()
+
+ for row in results:
+ cpRow = self.listify(row)
+ table_data.append(cpRow)
+
+ except:
+ self._db.rollback()
+ sys.exit("Database Interface Exception:\n" + err_msg)
+
+ self.close_db()
+
+ # Construct the html table
+ header =
self.__query_obj.get_query_header(self.__query_handle_contact)
+ t = HTML.table(table_data, header_row=header)
+ htmlcode = str(t)
+
+ f.write(htmlcode)
+ f.close()
+
+ return htmlcode
+
+
+
+ """
+
+ Civi Reporting - Create a table from a list of contacts
+ Creates two html tables
+
+ """
+ def build_html_table_by_amount(self):
+
+
+ # Initialize times
+ now = datetime.datetime.now()
+ hours_back = 24 * 7
+
+ start_time, end_time = self.gen_date_strings_hr(now, hours_back)
+
+ # Prepare SQL statements and tables
+ table_data = []
+ sql_stmnt = mh.read_sql(self.__sql_path +
self.__query_handle_amount + '.sql');
+
+ # open the html files for writing
+ f = open(self.__html_path + self.__query_handle_amount +
'.html', 'w')
+
+ # Formats the statement according to query type
+ select_stmnt =
self.__query_obj.format_query(self.__query_handle_amount, sql_stmnt,
[start_time, end_time])
+
+ # initialize the db and execute the query
+ self.init_db()
+
+ try:
+
+ # execute statement gathering amounts
+ err_msg = select_stmnt
+ self._cur.execute(select_stmnt)
+ results = self._cur.fetchall()
+
+ for row in results:
+ cpRow = self.listify(row)
+ table_data.append(cpRow)
+
+
+ except:
+ self._db.rollback()
+ sys.exit("Database Interface Exception:\n" + err_msg)
+
+ self.close_db()
+
+ # Construct the html table
+ header =
self.__query_obj.get_query_header(self.__query_handle_amount)
+ t = HTML.table(table_data, header_row=header)
+ htmlcode = str(t)
+
+ f.write(htmlcode)
+ f.close()
+
+ return htmlcode
+
+
+
+"""
+
+method :: main
+
+to parse contact names invoke:
+
+rfaulkner@wmf128:~/trunk/projects/fundraiser-statistics/fundraiser-scripts$
python mine_contacts.py
/home/rfaulkner/trunk/docs/Rebeccas_Contacts_Donation_Alerts.csv
+
+"""
+
+class Usage(Exception):
+ def __init__(self, msg):
+ self.msg = msg
+
+def main(argv=None):
+ if argv is None:
+ argv = sys.argv
+ try:
+ try:
+ opts, args = getopt.getopt(argv[1:], "h", ["help"])
+ except getopt.error, msg:
+ raise Usage(msg)
+
+ # more code, unchanged
+ except Usage, err:
+ print >>sys.stderr, err.msg
+ print >>sys.stderr, "for help use --help"
+ return 2
+
+ contact_handler_obj = contact_handler()
+
+ # Construct HTML table - conditioned on input
+ if len(args) > 0:
+ if args[0] == 'c':
+ contact_handler_obj.build_html_table_by_contact()
+ elif args[0] == 'a':
+ contact_handler_obj.build_html_table_by_amount()
+ else:
+ print 'Invalid option: enter "c" for civi contacts or
"a" for amounts'
+ else:
+ print 'Invalid args: need at least one argument; enter "c" for
civi contacts or "a" for amounts'
+
+if __name__ == "__main__":
+ sys.exit(main())
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_amount.sql
2011-02-28 20:15:30 UTC (rev 82951)
@@ -0,0 +1,30 @@
+
+
+select
+
+ecomm.stamp as stamp,
+civicrm.civicrm_contact.first_name as first_name,
+civicrm.civicrm_contact.last_name as last_name,
+civicrm.civicrm_country.name as name,
+civicrm.civicrm_country.iso_code as iso_code,
+converted_amount as amount
+
+
+from
+(
+select
+DATE_FORMAT(ts, '%sY-%sm-%sd %sH') as stamp,
+contribution_tracking.contribution_id,
+converted_amount,
+contact_id
+
+from drupal.contribution_tracking left join civicrm.public_reporting on
contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id
+where ts > '%s' and ts <= '%s'
+) as ecomm
+
+join civicrm.civicrm_contact on ecomm.contact_id = civicrm.civicrm_contact.id
+join civicrm.civicrm_address on civicrm.civicrm_contact.id =
civicrm.civicrm_address.contact_id
+join civicrm.civicrm_country on civicrm.civicrm_address.country_id =
civicrm.civicrm_country.id
+
+where ecomm.converted_amount >= 100
+group by 1;
Added:
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_contact.sql
===================================================================
---
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_contact.sql
(rev 0)
+++
trunk/fundraiser-statistics/fundraiser-scripts/sql/report_ecomm_by_contact.sql
2011-02-28 20:15:30 UTC (rev 82951)
@@ -0,0 +1,29 @@
+
+
+select
+
+ecomm.stamp as stamp,
+civicrm.civicrm_contact.first_name as first_name,
+civicrm.civicrm_contact.last_name as last_name,
+civicrm.civicrm_country.name as name,
+civicrm.civicrm_country.iso_code as iso_code,
+converted_amount as amount
+
+
+from
+(
+select
+DATE_FORMAT(ts, '%sY-%sm-%sd %sH') as stamp,
+contribution_tracking.contribution_id,
+converted_amount,
+contact_id
+
+from drupal.contribution_tracking left join civicrm.public_reporting on
contribution_tracking.contribution_id = civicrm.public_reporting.contribution_id
+) as ecomm
+
+join civicrm.civicrm_contact on ecomm.contact_id = civicrm.civicrm_contact.id
+join civicrm.civicrm_address on civicrm.civicrm_contact.id =
civicrm.civicrm_address.contact_id
+join civicrm.civicrm_country on civicrm.civicrm_address.country_id =
civicrm.civicrm_country.id
+
+%s group by 1;
+
Modified: trunk/fundraiser-statistics/fundraiser-scripts/test_reporting.py
===================================================================
--- trunk/fundraiser-statistics/fundraiser-scripts/test_reporting.py
2011-02-28 19:47:39 UTC (rev 82950)
+++ trunk/fundraiser-statistics/fundraiser-scripts/test_reporting.py
2011-02-28 20:15:30 UTC (rev 82951)
@@ -22,7 +22,7 @@
import query_store as qs
-
+
def build_html_table_from_list(db, cur, header, sql_path, html_path,
query_type, start_time, end_time):
query_obj = qs.query_store()
@@ -88,12 +88,52 @@
return htmlcode
-# workaround for issue with tuple objects in HTML.py
-# MySQLdb returns unfamiliar tuple elements from its fetchall method
-# this is probably a version problem since the issue popped up in 2.5 but not
2.6
-def listify(row):
- l = []
- for i in row:
- l.append(i)
- return l
+
+class data_handler(object):
+
+ _db = None
+ _cur = None
+
+ def __init__(self):
+ pass
+
+ """
+
+ INITIALIZE DB ACCESS
+
+ """
+ def init_db(self):
+
+ """ Establish connection """
+ # self._db = MySQLdb.connect(host='db10.pmtpa.wmnet',
user='rfaulk', db='faulkner')
+ # self._db = MySQLdb.connect(host='127.0.0.1', user='rfaulk',
db='faulkner', port=3307)
+ self._db = MySQLdb.connect(host='storage3.pmtpa.wmnet',
user='rfaulk', db='faulkner')
+
+ """ Create cursor """
+ self._cur = self._db.cursor()
+
+
+ """
+
+ Close the db connection
+
+ """
+ def close_db(self):
+ self._cur.close()
+ self._db.close()
+
+ """
+
+ workaround for issue with tuple objects in HTML.py
+ MySQLdb returns unfamiliar tuple elements from its fetchall method
+ this is probably a version problem since the issue popped up in 2.5 but
not 2.6
+
+ """
+ def listify(self, row):
+ l = []
+ for i in row:
+ l.append(i)
+ return l
+
+
_______________________________________________
MediaWiki-CVS mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-cvs
