Example #1
0
 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])
Example #4
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
Example #9
0
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
Example #10
0
    def __init__(self, path):
        self.path = path
        self.crm = Civicrm(config.civicrm_db)

        self.pending_data = None
        self.pending_supplemental_data = None
Example #11
0
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])