def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
def main():
global config, messaging, options, civi
parser = OptionParser(usage="usage: %prog [options]")
parser.add_option("-c",
"--config",
dest='configFile',
default=["paypal-audit.cfg"],
action='append',
help='Path to configuration file')
parser.add_option("-f",
"--auditFile",
dest='auditFile',
default=None,
help='CSV of transaction history')
parser.add_option(
'-l',
"--logFile",
dest='logFile',
default="audit.log",
help='Destination logfile. New messages will be appended.')
parser.add_option("-n",
"--no-effect",
dest='noEffect',
default=False,
action="store_true",
help="Dummy no-effect mode")
(options, args) = parser.parse_args()
path = options.auditFile
if re.search(r'[.]gz$', path):
f = gzip.open(path, "rb")
else:
f = open(path, "rU")
infile = csv.DictReader(f)
config = SafeConfigParser()
config.read(options.configFile)
if options.noEffect:
log("*** Dummy mode! Not injecting stomp messages ***")
messaging = Stomp(config)
civi = Civicrm(config.items('Db'))
locale.setlocale(locale.LC_NUMERIC, "")
# fix spurious whitespace around column header names
infile.fieldnames = [name.strip() for name in infile.fieldnames]
ignore_types = [
"Authorization",
"Cancelled Fee",
# currency conversion is an explanation of amounts which appear elsewhere
"Currency Conversion",
# TODO: handle in IPN
"Temporary Hold",
# seems to be the cancellation of a temporary hold
"Update to Reversal",
"Website Payments Pro API Solution",
]
audit_dispatch = {
"Reversal": handle_refund,
"Chargeback Settlement": handle_refund,
"Refund": handle_refund,
"Subscription Payment Received": handle_payment,
"Web Accept Payment Received": handle_payment,
"Shopping Cart Payment Received": handle_payment,
"Virtual Debt Card Credit Received": handle_payment,
"Payment Received": handle_payment,
"Update to eCheck Received": handle_payment,
}
for line in infile:
if line['Type'] in ignore_types:
log("Ignoring %s of type %s" %
(line['Transaction ID'], line['Type']))
continue
if line['Type'] in audit_dispatch:
audit_dispatch[line['Type']](line)
else:
handle_unknown(line)
class TrrFile(object):
VERSION = [4, 8]
stomp = None
# FIXME: these are version 8 headers, we would fail on multi-part v4 files...
column_headers = [
"Column Type",
"Transaction ID",
"Invoice ID",
"PayPal Reference ID",
"PayPal Reference ID Type",
"Transaction Event Code",
"Transaction Initiation Date",
"Transaction Completion Date",
"Transaction Debit or Credit",
"Gross Transaction Amount",
"Gross Transaction Currency",
"Fee Debit or Credit",
"Fee Amount",
"Fee Currency",
"Transactional Status",
"Insurance Amount",
"Sales Tax Amount",
"Shipping Amount",
"Transaction Subject",
"Transaction Note",
"Payer's Account ID",
"Payer Address Status",
"Item Name",
"Item ID",
"Option 1 Name",
"Option 1 Value",
"Option 2 Name",
"Option 2 Value",
"Auction Site",
"Auction Buyer ID",
"Auction Closing Date",
"Shipping Address Line1",
"Shipping Address Line2",
"Shipping Address City",
"Shipping Address State",
"Shipping Address Zip",
"Shipping Address Country",
"Shipping Method",
"Custom Field",
"Billing Address Line1",
"Billing Address Line2",
"Billing Address City",
"Billing Address State",
"Billing Address Zip",
"Billing Address Country",
"Consumer ID",
"First Name",
"Last Name",
"Consumer Business Name",
"Card Type",
"Payment Source",
"Shipping Name",
"Authorization Review Status",
"Protection Eligibility",
"Payment Tracking ID",
]
@staticmethod
def handle(path):
obj = TrrFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
def parse(self):
# FIXME: encapsulation issues
ppreport.read(self.path, self.VERSION, self.parse_line, self.column_headers)
def parse_line(self, row):
if row['Billing Address Line1']:
addr_prefix = 'Billing Address '
else:
addr_prefix = 'Shipping Address '
out = {
'gateway_txn_id': row['Transaction ID'],
'date': row['Transaction Initiation Date'],
'settled_date': row['Transaction Completion Date'],
'gross': float(row['Gross Transaction Amount']) / 100.0,
'currency': row['Gross Transaction Currency'],
'gateway_status': row['Transactional Status'],
'gateway': 'paypal',
'note': row['Transaction Note'],
'email': row['Payer\'s Account ID'],
'street_address': row[addr_prefix + 'Line1'],
'supplemental_address_1': row[addr_prefix + 'Line2'],
'city': row[addr_prefix + 'City'],
'state_province': row[addr_prefix + 'State'],
'postal_code': row[addr_prefix + 'Zip'],
'country': row[addr_prefix + 'Country'],
}
if row['Fee Amount']:
out['fee'] = float(row['Fee Amount']) / 100.0
if row['Fee Currency'] and row['Gross Transaction Currency'] != row['Fee Currency']:
raise RuntimeError("Failed to import because multiple currencies for one transaction is not handled.")
if 'First Name' in row:
out['first_name'] = row['First Name']
if 'Last Name' in row:
out['last_name'] = row['Last Name']
if 'Payment Source' in row:
out['payment_method'] = row['Payment Source']
if 'Card Type' in row:
out['payment_submethod'] = row['Card Type']
if row['PayPal Reference ID Type'] == 'SUB':
out['subscr_id'] = row['PayPal Reference ID']
event_type = row['Transaction Event Code'][0:3]
queue = None
if event_type in ('T00', 'T03', 'T05', 'T07', 'T22'):
if row['Transaction Event Code'] == 'T0002':
queue = 'recurring'
out = self.normalize_recurring(out)
elif row['Transaction Debit or Credit'] == 'DR':
# sic: double-space is coming from the upstream
log.info("-Debit\t{id}\t{date}\tPayment to".format(id=out['gateway_txn_id'], date=out['date']))
# This payment is from us! Do not send to the CRM.
return
else:
queue = 'donations'
elif event_type in ('T11', 'T12'):
out['gateway_refund_id'] = out['gateway_txn_id']
out['gross_currency'] = out['currency']
if row['PayPal Reference ID Type'] == 'TXN':
out['gateway_parent_id'] = row['PayPal Reference ID']
if row['Transaction Event Code'] == 'T1106':
out['type'] = 'reversal'
elif row['Transaction Event Code'] == 'T1107':
out['type'] = 'refund'
elif row['Transaction Event Code'] == 'T1201':
out['type'] = 'chargeback'
else:
log.info("-Unknown\t{id}\t{date}\t(Refundish type {type})".format(id=out['gateway_txn_id'], date=out['date'], type=row['Transaction Event Code']))
return
queue = 'refund'
if not queue:
log.info("-Unknown\t{id}\t{date}\t(Type {type})".format(id=out['gateway_txn_id'], date=out['date'], type=event_type))
return
if self.crm.transaction_exists(gateway_txn_id=out['gateway_txn_id'], gateway='paypal'):
log.info("-Duplicate\t{id}\t{date}\t{type}".format(id=out['gateway_txn_id'], date=row['Transaction Initiation Date'], type=queue))
return
if 'last_name' not in out and queue != 'refund':
out['first_name'], out['last_name'] = self.fetch_donor_name(out['gateway_txn_id'])
if config.no_thankyou:
out['thankyou_date'] = 0
log.info("+Sending\t{id}\t{date}\t{type}".format(id=out['gateway_txn_id'], date=row['Transaction Initiation Date'], type=queue))
self.send(queue, out)
def send(self, queue, msg):
if not self.stomp:
self.stomp = Stomp()
self.stomp.send(queue, msg)
def normalize_recurring(self, msg):
'Synthesize a raw PayPal message'
if 'fee' not in msg:
msg['fee'] = 0
out = {
'gateway': 'paypal',
'txn_type': 'subscr_payment',
'gateway_txn_id': msg['gateway_txn_id'],
'txn_id': msg['gateway_txn_id'],
'subscr_id': msg['subscr_id'],
'payment_date': msg['date'],
'payer_email': msg['email'],
'mc_currency': msg['currency'],
'mc_gross': msg['gross'],
'mc_fee': msg['fee'],
'address_street': "\n".join([msg['street_address'], msg['supplemental_address_1']]),
'address_city': msg['city'],
'address_zip': msg['postal_code'],
'address_state': msg['state_province'],
'address_country_code': msg['country'],
}
return out
def fetch_donor_name(self, txn_id):
api = PaypalApiClassic()
response = api.call('GetTransactionDetails', TRANSACTIONID=txn_id)
if 'FIRSTNAME' not in response:
raise RuntimeError("Failed to get transaction details for {id}, repsonse: {response}".format(id=txn_id, response=response))
return (response['FIRSTNAME'][0], response['LASTNAME'][0])
class SarFile(object):
VERSION = 2
redis = None
column_headers = [
"Column Type",
"Subscription ID",
"Subscription Action Type",
"Subscription Currency",
"Subscription Creation Date",
"Subscription Period 1",
"Period 1 Amount",
"Subscription Period 2",
"Period 2 Amount",
"Subscription Period 3",
"Period 3 Amount",
"Recurring",
"Recurrence number",
"Subscription Payer PayPal Account ID",
"Subscription Payer email address",
"Subscription Payer Name",
"Subscription Payer Business Name",
"Shipping Address Line1",
"Shipping Address City",
"Shipping Address State",
"Shipping Address Zip",
"Shipping Address Country",
"Subscription Description",
"Subscription Memo",
"Subscription Custom Field",
]
@staticmethod
def handle(path):
obj = SarFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
def parse(self):
ppreport.read(self.path, self.VERSION, self.parse_line, self.column_headers)
def parse_line(self, row):
required_fields = [
"Period 3 Amount",
"Subscription Currency",
"Subscription ID",
"Subscription Payer Name",
"Subscription Period 3",
]
missing_fields = []
for field in required_fields:
if field not in row or row[field] == '':
missing_fields.append(field)
if missing_fields:
raise RuntimeError("Message is missing some important fields: [{fields}]".format(fields=", ".join(missing_fields)))
names = row['Subscription Payer Name'].split(' ')
out = {
'subscr_id': row['Subscription ID'],
'mc_currency': row['Subscription Currency'],
'mc_amount3': float(row['Period 3 Amount']) / 100,
'period3': row['Subscription Period 3'],
'subscr_date': row['Subscription Creation Date'],
'payer_email': row['Subscription Payer email address'],
'first_name': names[0],
'last_name': " ".join(names[1:]),
'address_street': row['Shipping Address Line1'],
'address_city': row['Shipping Address City'],
'address_zip': row['Shipping Address Zip'],
'address_state': row['Shipping Address State'],
'address_country_code': row['Shipping Address Country'],
'gateway': 'paypal',
}
# FIXME what historical evil caused...
if row['Subscription Period 3'] != "1 M":
raise RuntimeError("Unknown subscription period {period}".format(period=row['Subscription Period 3']))
if row['Subscription Action Type'] == 'S0000':
out['txn_type'] = 'subscr_signup'
if self.crm.subscription_exists(out['subscr_id']):
log.info("-Duplicate\t{id}\t{date}\tsubscr_signup".format(id=out['subscr_id'], date=out['subscr_date']))
return
elif row['Subscription Action Type'] == 'S0100':
log.info("-Ignored\t{id}\t{date}\tsubscr_modify".format(id=out['subscr_id'], date=out['subscr_date']))
return
elif row['Subscription Action Type'] == 'S0200':
out['txn_type'] = 'subscr_cancel'
out['cancel_date'] = out['subscr_date']
elif row['Subscription Action Type'] == 'S0300':
out['txn_type'] = 'subscr_eot'
if config.no_thankyou:
out['thankyou_date'] = 0
log.info("+Sending\t{id}\t{date}\t{type}".format(id=out['subscr_id'], date=out['subscr_date'], type=out['txn_type']))
self.send(out)
def send(self, msg):
if not self.redis:
self.redis = Redis()
self.redis.send('recurring', msg)
class SarFile(object):
VERSION = 2
stomp = None
column_headers = [
"Column Type",
"Subscription ID",
"Subscription Action Type",
"Subscription Currency",
"Subscription Creation Date",
"Subscription Period 1",
"Period 1 Amount",
"Subscription Period 2",
"Period 2 Amount",
"Subscription Period 3",
"Period 3 Amount",
"Recurring",
"Recurrence number",
"Subscription Payer PayPal Account ID",
"Subscription Payer email address",
"Subscription Payer Name",
"Subscription Payer Business Name",
"Shipping Address Line1",
"Shipping Address City",
"Shipping Address State",
"Shipping Address Zip",
"Shipping Address Country",
"Subscription Description",
"Subscription Memo",
"Subscription Custom Field",
]
@staticmethod
def handle(path):
obj = SarFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
def parse(self):
ppreport.read(self.path, self.VERSION, self.parse_line,
self.column_headers)
def parse_line(self, row):
required_fields = [
"Period 3 Amount",
"Subscription Currency",
"Subscription ID",
"Subscription Payer Name",
"Subscription Period 3",
]
missing_fields = []
for field in required_fields:
if not field in row or row[field] == '':
missing_fields.append(field)
if missing_fields:
raise RuntimeError(
"Message is missing some important fields: [{fields}]".format(
fields=", ".join(missing_fields)))
names = row['Subscription Payer Name'].split(' ')
out = {
'subscr_id': row['Subscription ID'],
'mc_currency': row['Subscription Currency'],
'mc_amount3': float(row['Period 3 Amount']) / 100,
'period3': row['Subscription Period 3'],
'subscr_date': row['Subscription Creation Date'],
'payer_email': row['Subscription Payer email address'],
'first_name': names[0],
'last_name': " ".join(names[1:]),
'address_street': row['Shipping Address Line1'],
'address_city': row['Shipping Address City'],
'address_zip': row['Shipping Address Zip'],
'address_state': row['Shipping Address State'],
'address_country_code': row['Shipping Address Country'],
'gateway': 'paypal',
}
# FIXME what historical evil caused...
if row['Subscription Period 3'] != "1 M":
raise RuntimeError("Unknown subscription period {period}".format(
period=row['Subscription Period 3']))
if row['Subscription Action Type'] == 'S0000':
out['txn_type'] = 'subscr_signup'
if self.crm.subscription_exists(out['subscr_id']):
log.info("-Duplicate\t{id}\t{date}\tsubscr_signup".format(
id=out['subscr_id'], date=out['subscr_date']))
return
elif row['Subscription Action Type'] == 'S0100':
log.info("-Ignored\t{id}\t{date}\tsubscr_modify".format(
id=out['subscr_id'], date=out['subscr_date']))
return
elif row['Subscription Action Type'] == 'S0200':
out['txn_type'] = 'subscr_cancel'
out['cancel_date'] = out['subscr_date']
elif row['Subscription Action Type'] == 'S0300':
out['txn_type'] = 'subscr_eot'
if config.no_thankyou:
out['thankyou_date'] = 0
log.info(
"+Sending\t{id}\t{date}\t{type}".format(id=out['subscr_id'],
date=out['subscr_date'],
type=out['txn_type']))
self.send(out)
def send(self, msg):
if not self.stomp:
self.stomp = Stomp()
self.stomp.send('recurring', msg)
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
class TransactionReconciliationFile(object):
filename_re = r"^MA\.PISCESSW\.#M\.RECON\..*"
stomp = None
row_header_segment = [
("record_type", 2),
("sequence_no", 8),
]
credit_debit_summary_segment = [
("accepted_debits", 11),
("rejected_debits", 11),
("pending_debits", 11),
("accepted_credits", 11),
("rejected_credits", 11),
("pending_credits", 11),
("accepted_debits_count", 7),
("rejected_debits_count", 7),
("pending_debits_count", 7),
("accepted_credits_count", 7),
("rejected_credits_count", 7),
("pending_credits_count", 7),
]
file_header = FixedRecordType("00",
[
("record_type", 2),
("sequence_no", 8),
("file_id", 13),
("count", 7),
],
credit_debit_summary_segment,
[
("file_creation_date", 6),
("file_sequence_number", 3),
("site_id", 3),
]
)
reconciliation_merchant_company_header = FixedRecordType("05",
[
("record_type", 2),
("sequence_no", 8),
("company_number", 13),
("count", 7),
],
credit_debit_summary_segment
)
reconciliation_merchant_outlet = FixedRecordType("10",
[
("record_type", 2),
("sequence_no", 8),
("merchant_id", 13),
("trading_day", 6),
("processing_date", 6),
],
credit_debit_summary_segment
)
reconciliation_transaction_data = FixedRecordType("15", [
("record_type", 2),
("sequence_no", 8),
("pan", 19),
("expiry_date", 4),
("transaction_value", 11),
("transaction_date", 6),
("transaction_time", 6),
("transaction_type", 1),
("transaction_source", 1),
("receipt_number", 6),
("status", 1),
("reserved1", 2),
("local_value", 9),
("local_currency_code", 3),
("local_exponent", 1),
("settlement_value", 9),
("settlement_currency_code", 3),
("settlement_exponent", 1),
("acquired_processed_indicator", 1),
("card_type", 5),
])
reconciliation_transaction_supplementary_data = FixedRecordType("16", [
("record_type", 2),
("sequence_no", 8),
("auth_code", 6),
("auth_method", 1),
("card_issue_number", 2),
("card_start_date", 4),
("cash_amount", 7),
("originators_transaction_reference", 20),
("ticket_number", 14),
])
known_record_types = [
file_header,
reconciliation_merchant_company_header,
reconciliation_merchant_outlet,
reconciliation_transaction_data,
reconciliation_transaction_supplementary_data,
]
@staticmethod
def is_mine(path):
filename = os.path.basename(path)
return re.match(TransactionReconciliationFile.filename_re, filename)
@staticmethod
def handle(path):
obj = TransactionReconciliationFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
self.pending_data = None
self.pending_supplemental_data = None
def parse(self):
"""Parse the file"""
self.file = file(self.path)
for line in self.file:
self.parse_line(line.rstrip("\r\n"))
self.flush_data()
def parse_line(self, line):
"""Parse one line and send it to the appropriate queue
There is a crazy thing happening here where we need to coordinate
sequential lines, and merge supplemental records into the main transaction
data. See add_transaction_data and add_supplementary_data.
"""
# Peek at row header to determine its type
row_info = unpack_fixed_width_line(self.row_header_segment, line[:10])
# Find the corresponding line format and parse the contents
record = None
for record_type in self.known_record_types:
if record_type.record_type == row_info["record_type"]:
record = unpack_fixed_width_line(record_type.fields, line)
if not record:
raise RuntimeError("Unknown record type {type} while processing {path}, aborting!".format(type=row_info["record_type"], path=self.path))
# Dispatch to a handler
if record["record_type"] == self.reconciliation_transaction_data.record_type:
self.add_transaction_data(record)
elif record["record_type"] == self.reconciliation_transaction_supplementary_data.record_type:
self.add_supplementary_data(record)
else:
# ignore other crap.
# TODO: assertions for checksummy things built into the file
pass
def send(self, queue, msg):
"""Send over the wire"""
if not self.stomp:
self.stomp = Stomp()
self.stomp.send(queue, msg)
def add_transaction_data(self, record):
self.flush_data()
self.pending_data = record
def add_supplementary_data(self, record):
if not self.pending_data:
raise RuntimeError("Cannot eat supplementary transaction data because there no unconsumed base data. Line {line}".format(line=record["sequence_no"]))
if self.pending_supplemental_data:
raise RuntimeError("Cannot eat supplementary data because there is already unconsumed supplemental data. Line {line}".format(line=record["sequence_no"]))
self.pending_supplemental_data = record
def flush_data(self):
if self.pending_data:
self.send_transaction()
def send_transaction(self):
record = self.pending_data
# Verify that the data and supplemental data are a pair
if self.pending_supplemental_data:
if int(self.pending_supplemental_data["sequence_no"]) != int(self.pending_data["sequence_no"]) + 1:
raise RuntimeError("Mismatched data and supplemental data!")
record.update(self.pending_supplemental_data)
self.normalize_and_send(record)
self.pending_data = None
self.pending_supplemental_data = None
def normalize_and_send(self, record):
"""Transform the record into a WMF queue message
See https://wikitech.wikimedia.org/wiki/Fundraising/Queue_messages"""
msg = {}
if record["transaction_type"] == "0":
queue = "donations"
elif record["transaction_type"] == "5":
queue = "refund"
else:
raise RuntimeError("Don't know how to handle transaction type {type}.".format(type=record["transaction_type"]))
msg["date"] = to_timestamp(datetime.strptime(record["transaction_date"] + record["transaction_time"], "%d%m%y%H%M%S").utctimetuple())
iso_date = datetime.fromtimestamp(msg["date"]).isoformat()
msg["gateway"] = "worldpay"
# FIXME: is this the CustomerId or what?
if "originators_transaction_reference" in record:
msg["gateway_txn_id"] = record["originators_transaction_reference"].strip()
else:
raise RuntimeError("We're gonna die: no gateway_txn_id available.")
# The default currency is GBP, don't make me explain why the amount
# comes from a different field when currency != GBP :(
if record["local_currency_code"].strip():
msg["currency"] = record["local_currency_code"]
msg["gross"] = int(record["local_value"]) * exponent_to_multiplier(record["local_exponent"])
else:
msg["currency"] = "GBP"
msg["gross"] = int(record["transaction_value"]) * exponent_to_multiplier(2)
if queue == "refund":
msg["gross_currency"] = msg["currency"]
msg["gateway_parent_id"] = msg["gateway_txn_id"]
# Note that we do not have a new txn id for the refund
msg["gateway_refund_id"] = msg["gateway_txn_id"]
# FIXME: chargeback vs refund info is not available in this file.
msg["type"] = "refund"
log.info("+Sending\t{id}\t{date}\t{type}".format(id=msg["gateway_parent_id"], date=iso_date, type=msg["type"]))
self.send(queue, msg)
return
if self.crm.transaction_exists(gateway_txn_id=msg["gateway_txn_id"], gateway="worldpay"):
log.info("-Duplicate\t{id}\t{date}\t{type}".format(id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
# Switch behavior depending on the status. We only like "accepted" transactions.
status = record["status"].strip()
if status == "P":
log.info("-Pending\t{id}\t{date}\t{type}".format(id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
elif status == "R":
log.info("-Rejection\t{id}\t{date}\t{type}".format(id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
elif status != "A":
raise RuntimeError("Unknown gateway status: {code}".format(code=status))
# Include settlement details if they are available.
if record["settlement_value"].strip():
if record["settlement_currency_code"].strip():
msg["settlement_currency"] = record["settlement_currency_code"]
else:
msg["settlement_currency"] = "GBP"
msg["settlement_amount"] = int(record["settlement_value"]) * exponent_to_multiplier(record["settlement_exponent"])
msg["email"] = "*****@*****.**"
msg["payment_method"] = "cc"
msg["payment_submethod"] = reference_data.decode_card_type(record["card_type"].strip())
# custom values
msg["raw_card_type"] = record["card_type"].strip()
log.info("+Sending\t{id}\t{date}\t{type}".format(id=msg["gateway_txn_id"], date=iso_date, type=queue))
self.send(queue, msg)
def normalize_transaction(self, record):
"""Transform a raw reconciliation record into a donation queue message"""
# TODO
return record
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
self.pending_data = None
self.pending_supplemental_data = None
class TransactionReconciliationFile(object):
filename_re = r"^MA\.PISCESSW\.#M\.RECON\..*"
stomp = None
row_header_segment = [
("record_type", 2),
("sequence_no", 8),
]
credit_debit_summary_segment = [
("accepted_debits", 11),
("rejected_debits", 11),
("pending_debits", 11),
("accepted_credits", 11),
("rejected_credits", 11),
("pending_credits", 11),
("accepted_debits_count", 7),
("rejected_debits_count", 7),
("pending_debits_count", 7),
("accepted_credits_count", 7),
("rejected_credits_count", 7),
("pending_credits_count", 7),
]
file_header = FixedRecordType("00", [
("record_type", 2),
("sequence_no", 8),
("file_id", 13),
("count", 7),
], credit_debit_summary_segment, [
("file_creation_date", 6),
("file_sequence_number", 3),
("site_id", 3),
])
reconciliation_merchant_company_header = FixedRecordType(
"05", [
("record_type", 2),
("sequence_no", 8),
("company_number", 13),
("count", 7),
], credit_debit_summary_segment)
reconciliation_merchant_outlet = FixedRecordType(
"10", [
("record_type", 2),
("sequence_no", 8),
("merchant_id", 13),
("trading_day", 6),
("processing_date", 6),
], credit_debit_summary_segment)
reconciliation_transaction_data = FixedRecordType("15", [
("record_type", 2),
("sequence_no", 8),
("pan", 19),
("expiry_date", 4),
("transaction_value", 11),
("transaction_date", 6),
("transaction_time", 6),
("transaction_type", 1),
("transaction_source", 1),
("receipt_number", 6),
("status", 1),
("reserved1", 2),
("local_value", 9),
("local_currency_code", 3),
("local_exponent", 1),
("settlement_value", 9),
("settlement_currency_code", 3),
("settlement_exponent", 1),
("acquired_processed_indicator", 1),
("card_type", 5),
])
reconciliation_transaction_supplementary_data = FixedRecordType(
"16", [
("record_type", 2),
("sequence_no", 8),
("auth_code", 6),
("auth_method", 1),
("card_issue_number", 2),
("card_start_date", 4),
("cash_amount", 7),
("originators_transaction_reference", 20),
("ticket_number", 14),
])
known_record_types = [
file_header,
reconciliation_merchant_company_header,
reconciliation_merchant_outlet,
reconciliation_transaction_data,
reconciliation_transaction_supplementary_data,
]
@staticmethod
def is_mine(path):
filename = os.path.basename(path)
return re.match(TransactionReconciliationFile.filename_re, filename)
@staticmethod
def handle(path):
obj = TransactionReconciliationFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
self.pending_data = None
self.pending_supplemental_data = None
def parse(self):
"""Parse the file"""
self.file = file(self.path)
for line in self.file:
self.parse_line(line.rstrip("\r\n"))
self.flush_data()
def parse_line(self, line):
"""Parse one line and send it to the appropriate queue
There is a crazy thing happening here where we need to coordinate
sequential lines, and merge supplemental records into the main transaction
data. See add_transaction_data and add_supplementary_data.
"""
# Peek at row header to determine its type
row_info = unpack_fixed_width_line(self.row_header_segment, line[:10])
# Find the corresponding line format and parse the contents
record = None
for record_type in self.known_record_types:
if record_type.record_type == row_info["record_type"]:
record = unpack_fixed_width_line(record_type.fields, line)
if not record:
raise RuntimeError(
"Unknown record type {type} while processing {path}, aborting!"
.format(type=row_info["record_type"], path=self.path))
# Dispatch to a handler
if record[
"record_type"] == self.reconciliation_transaction_data.record_type:
self.add_transaction_data(record)
elif record[
"record_type"] == self.reconciliation_transaction_supplementary_data.record_type:
self.add_supplementary_data(record)
else:
# ignore other crap.
# TODO: assertions for checksummy things built into the file
pass
def send(self, queue, msg):
"""Send over the wire"""
if not self.stomp:
self.stomp = Stomp()
self.stomp.send(queue, msg)
def add_transaction_data(self, record):
self.flush_data()
self.pending_data = record
def add_supplementary_data(self, record):
if not self.pending_data:
raise RuntimeError(
"Cannot eat supplementary transaction data because there no unconsumed base data. Line {line}"
.format(line=record["sequence_no"]))
if self.pending_supplemental_data:
raise RuntimeError(
"Cannot eat supplementary data because there is already unconsumed supplemental data. Line {line}"
.format(line=record["sequence_no"]))
self.pending_supplemental_data = record
def flush_data(self):
if self.pending_data:
self.send_transaction()
def send_transaction(self):
record = self.pending_data
# Verify that the data and supplemental data are a pair
if self.pending_supplemental_data:
if int(self.pending_supplemental_data["sequence_no"]) != int(
self.pending_data["sequence_no"]) + 1:
raise RuntimeError("Mismatched data and supplemental data!")
record.update(self.pending_supplemental_data)
self.normalize_and_send(record)
self.pending_data = None
self.pending_supplemental_data = None
def normalize_and_send(self, record):
"""Transform the record into a WMF queue message
See https://wikitech.wikimedia.org/wiki/Fundraising/Queue_messages"""
msg = {}
if record["transaction_type"] == "0":
queue = "donations"
elif record["transaction_type"] == "5":
queue = "refund"
else:
raise RuntimeError(
"Don't know how to handle transaction type {type}.".format(
type=record["transaction_type"]))
msg["date"] = to_timestamp(
datetime.strptime(
record["transaction_date"] + record["transaction_time"],
"%d%m%y%H%M%S").utctimetuple())
iso_date = datetime.fromtimestamp(msg["date"]).isoformat()
msg["gateway"] = "worldpay"
# FIXME: is this the CustomerId or what?
if "originators_transaction_reference" in record:
msg["gateway_txn_id"] = record[
"originators_transaction_reference"].strip()
else:
raise RuntimeError("We're gonna die: no gateway_txn_id available.")
# The default currency is GBP, don't make me explain why the amount
# comes from a different field when currency != GBP :(
if record["local_currency_code"].strip():
msg["currency"] = record["local_currency_code"]
msg["gross"] = int(record["local_value"]) * exponent_to_multiplier(
record["local_exponent"])
else:
msg["currency"] = "GBP"
msg["gross"] = int(
record["transaction_value"]) * exponent_to_multiplier(2)
if queue == "refund":
msg["gross_currency"] = msg["currency"]
msg["gateway_parent_id"] = msg["gateway_txn_id"]
# Note that we do not have a new txn id for the refund
msg["gateway_refund_id"] = msg["gateway_txn_id"]
# FIXME: chargeback vs refund info is not available in this file.
msg["type"] = "refund"
log.info("+Sending\t{id}\t{date}\t{type}".format(
id=msg["gateway_parent_id"], date=iso_date, type=msg["type"]))
self.send(queue, msg)
return
if self.crm.transaction_exists(gateway_txn_id=msg["gateway_txn_id"],
gateway="worldpay"):
log.info("-Duplicate\t{id}\t{date}\t{type}".format(
id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
# Switch behavior depending on the status. We only like "accepted" transactions.
status = record["status"].strip()
if status == "P":
log.info("-Pending\t{id}\t{date}\t{type}".format(
id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
elif status == "R":
log.info("-Rejection\t{id}\t{date}\t{type}".format(
id=msg["gateway_txn_id"], date=iso_date, type=queue))
return
elif status != "A":
raise RuntimeError(
"Unknown gateway status: {code}".format(code=status))
# Include settlement details if they are available.
if record["settlement_value"].strip():
if record["settlement_currency_code"].strip():
msg["settlement_currency"] = record["settlement_currency_code"]
else:
msg["settlement_currency"] = "GBP"
msg["settlement_amount"] = int(
record["settlement_value"]) * exponent_to_multiplier(
record["settlement_exponent"])
msg["email"] = "*****@*****.**"
msg["payment_method"] = "cc"
msg["payment_submethod"] = reference_data.decode_card_type(
record["card_type"].strip())
# custom values
msg["raw_card_type"] = record["card_type"].strip()
log.info("+Sending\t{id}\t{date}\t{type}".format(
id=msg["gateway_txn_id"], date=iso_date, type=queue))
self.send(queue, msg)
def normalize_transaction(self, record):
"""Transform a raw reconciliation record into a donation queue message"""
# TODO
return record
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
self.pending_data = None
self.pending_supplemental_data = None
class TrrFile(object):
VERSION = [4, 8]
stomp = None
# FIXME: these are version 8 headers, we would fail on multi-part v4 files...
column_headers = [
"Column Type",
"Transaction ID",
"Invoice ID",
"PayPal Reference ID",
"PayPal Reference ID Type",
"Transaction Event Code",
"Transaction Initiation Date",
"Transaction Completion Date",
"Transaction Debit or Credit",
"Gross Transaction Amount",
"Gross Transaction Currency",
"Fee Debit or Credit",
"Fee Amount",
"Fee Currency",
"Transactional Status",
"Insurance Amount",
"Sales Tax Amount",
"Shipping Amount",
"Transaction Subject",
"Transaction Note",
"Payer's Account ID",
"Payer Address Status",
"Item Name",
"Item ID",
"Option 1 Name",
"Option 1 Value",
"Option 2 Name",
"Option 2 Value",
"Auction Site",
"Auction Buyer ID",
"Auction Closing Date",
"Shipping Address Line1",
"Shipping Address Line2",
"Shipping Address City",
"Shipping Address State",
"Shipping Address Zip",
"Shipping Address Country",
"Shipping Method",
"Custom Field",
"Billing Address Line1",
"Billing Address Line2",
"Billing Address City",
"Billing Address State",
"Billing Address Zip",
"Billing Address Country",
"Consumer ID",
"First Name",
"Last Name",
"Consumer Business Name",
"Card Type",
"Payment Source",
"Shipping Name",
"Authorization Review Status",
"Protection Eligibility",
"Payment Tracking ID",
]
@staticmethod
def handle(path):
obj = TrrFile(path)
obj.parse()
def __init__(self, path):
self.path = path
self.crm = Civicrm(config.civicrm_db)
def parse(self):
# FIXME: encapsulation issues
ppreport.read(self.path, self.VERSION, self.parse_line,
self.column_headers)
def parse_line(self, row):
if row['Billing Address Line1']:
addr_prefix = 'Billing Address '
else:
addr_prefix = 'Shipping Address '
out = {
'gateway_txn_id': row['Transaction ID'],
'date': row['Transaction Initiation Date'],
'settled_date': row['Transaction Completion Date'],
'gross': float(row['Gross Transaction Amount']) / 100.0,
'currency': row['Gross Transaction Currency'],
'gateway_status': row['Transactional Status'],
'gateway': 'paypal',
'note': row['Transaction Note'],
'email': row['Payer\'s Account ID'],
'street_address': row[addr_prefix + 'Line1'],
'supplemental_address_1': row[addr_prefix + 'Line2'],
'city': row[addr_prefix + 'City'],
'state_province': row[addr_prefix + 'State'],
'postal_code': row[addr_prefix + 'Zip'],
'country': row[addr_prefix + 'Country'],
}
if row['Fee Amount']:
out['fee'] = float(row['Fee Amount']) / 100.0
if row['Fee Currency'] and row[
'Gross Transaction Currency'] != row['Fee Currency']:
raise RuntimeError(
"Failed to import because multiple currencies for one transaction is not handled."
)
if 'First Name' in row:
out['first_name'] = row['First Name']
if 'Last Name' in row:
out['last_name'] = row['Last Name']
if 'Payment Source' in row:
out['payment_method'] = row['Payment Source']
if 'Card Type' in row:
out['payment_submethod'] = row['Card Type']
if row['PayPal Reference ID Type'] == 'SUB':
out['subscr_id'] = row['PayPal Reference ID']
event_type = row['Transaction Event Code'][0:3]
queue = None
if event_type in ('T00', 'T03', 'T05', 'T07', 'T22'):
if row['Transaction Event Code'] == 'T0002':
queue = 'recurring'
out = self.normalize_recurring(out)
elif row['Transaction Debit or Credit'] == 'DR':
# sic: double-space is coming from the upstream
log.info("-Debit\t{id}\t{date}\tPayment to".format(
id=out['gateway_txn_id'], date=out['date']))
# This payment is from us! Do not send to the CRM.
return
else:
queue = 'donations'
elif event_type in ('T11', 'T12'):
out['gateway_refund_id'] = out['gateway_txn_id']
out['gross_currency'] = out['currency']
if row['PayPal Reference ID Type'] == 'TXN':
out['gateway_parent_id'] = row['PayPal Reference ID']
if row['Transaction Event Code'] == 'T1106':
out['type'] = 'reversal'
elif row['Transaction Event Code'] == 'T1107':
out['type'] = 'refund'
elif row['Transaction Event Code'] == 'T1201':
out['type'] = 'chargeback'
else:
log.info(
"-Unknown\t{id}\t{date}\t(Refundish type {type})".format(
id=out['gateway_txn_id'],
date=out['date'],
type=row['Transaction Event Code']))
return
queue = 'refund'
if not queue:
log.info("-Unknown\t{id}\t{date}\t(Type {type})".format(
id=out['gateway_txn_id'], date=out['date'], type=event_type))
return
if self.crm.transaction_exists(gateway_txn_id=out['gateway_txn_id'],
gateway='paypal'):
log.info("-Duplicate\t{id}\t{date}\t{type}".format(
id=out['gateway_txn_id'],
date=row['Transaction Initiation Date'],
type=queue))
return
if 'last_name' not in out and queue != 'refund':
out['first_name'], out['last_name'] = self.fetch_donor_name(
out['gateway_txn_id'])
if config.no_thankyou:
out['thankyou_date'] = 0
log.info("+Sending\t{id}\t{date}\t{type}".format(
id=out['gateway_txn_id'],
date=row['Transaction Initiation Date'],
type=queue))
self.send(queue, out)
def send(self, queue, msg):
if not self.stomp:
self.stomp = Stomp()
self.stomp.send(queue, msg)
def normalize_recurring(self, msg):
'Synthesize a raw PayPal message'
if 'fee' not in msg:
msg['fee'] = 0
out = {
'gateway':
'paypal',
'txn_type':
'subscr_payment',
'gateway_txn_id':
msg['gateway_txn_id'],
'txn_id':
msg['gateway_txn_id'],
'subscr_id':
msg['subscr_id'],
'payment_date':
msg['date'],
'payer_email':
msg['email'],
'mc_currency':
msg['currency'],
'mc_gross':
msg['gross'],
'mc_fee':
msg['fee'],
'address_street':
"\n".join([msg['street_address'], msg['supplemental_address_1']]),
'address_city':
msg['city'],
'address_zip':
msg['postal_code'],
'address_state':
msg['state_province'],
'address_country_code':
msg['country'],
}
return out
def fetch_donor_name(self, txn_id):
api = PaypalApiClassic()
response = api.call('GetTransactionDetails', TRANSACTIONID=txn_id)
if 'FIRSTNAME' not in response:
raise RuntimeError(
"Failed to get transaction details for {id}, repsonse: {response}"
.format(id=txn_id, response=response))
return (response['FIRSTNAME'][0], response['LASTNAME'][0])