a = [['foo', 'bar', 'baz'],
['A', 1, True],
['C', 7, False],
['B', 2, False],
['C', 9, True]]
b = [['x', 'y', 'z'],
['B', 2, False],
['A', 9, False],
['B', 3, True],
['C', 9, True]]
from petl import complement, look
look(a)
look(b)
aminusb = complement(a, b)
look(aminusb)
bminusa = complement(b, a)
look(bminusa)
# recordcomplement
a = (('foo', 'bar', 'baz'),
('A', 1, True),
('C', 7, False),
('B', 2, False),
('C', 9, True))
b = (('bar', 'foo', 'baz'),
(2, 'B', False),
(9, 'A', False),
a = [['foo', 'bar', 'baz'],
['A', 1, True],
['C', 7, False],
['B', 2, False],
['C', 9, True]]
b = [['x', 'y', 'z'],
['B', 2, False],
['A', 9, False],
['B', 3, True],
['C', 9, True]]
from petl import complement, look
look(a)
look(b)
aminusb = complement(a, b)
look(aminusb)
bminusa = complement(b, a)
look(bminusa)
# recordcomplement
a = (('foo', 'bar', 'baz'),
('A', 1, True),
('C', 7, False),
('B', 2, False),
('C', 9, True))
b = (('bar', 'foo', 'baz'),
(2, 'B', False),
(9, 'A', False),
def append_TicketLeap_fees(paypal, iif_path):
"""
Take a paypal csv file (already sucked into PETL) and append the
the TicketLeap payments (the fees they charge us for using TicketLeap)
to the .IIF file
Return a slimmer paypal PETL table instance, with the rows associated
with TicketLeap payments removed.
"""
source_fields = ['Type', 'Date', 'Gross']
trns_fields = [
'!TRNS', 'TRNSID', 'TRNSTYPE', 'DATE', 'ACCNT', 'NAME', 'CLASS',
'AMOUNT', 'DOCNUM', 'CLEAR', 'TOPRINT', 'NAMEISTAXABLE', 'ADDR1',
'ADDR2', 'ADDR3', 'ADDR4', 'ADDR5'
]
spl_fields = [
'!SPL', 'SPLID', 'TRNSTYPE', 'DATE', 'ACCNT', 'NAME', 'CLASS',
'AMOUNT', 'DOCNUM', 'CLEAR', 'QNTY', 'PRICE', 'INVITEM', 'PAYMETH',
'TAXABLE', 'VALADJ', 'REIMBEXP'
]
# Here's how the QuickBooks file really maps to PayPal
trns_map = {}
trns_map['!TRNS'] = lambda r: 'TRNS'
trns_map['TRNSID'] = lambda r: ' '
trns_map['DOCNUM'] = lambda r: ' '
trns_map['NAMEISTAXABLE'] = lambda r: ' '
trns_map['NAME'] = lambda r: 'TicketLeap'
trns_map['TRNSTYPE'] = lambda r: 'CHECK'
trns_map['DATE'] = lambda r: r['Date'].strftime(
'%m/%d/%Y') #'{dt.month}/{dt.day}/{dt.year}'.format(dt=r['Date'])
trns_map['ACCNT'] = lambda r: 'PayPal Account'
trns_map['CLASS'] = lambda r: 'Other'
# For some reason QuickBooks requires that the cheque total amount be
# negative, but each item is positive.
trns_map['AMOUNT'] = lambda r: -abs(r['Gross'])
trns_map['CLEAR'] = lambda r: 'N'
trns_map['TOPRINT'] = lambda r: 'N'
spl_map = {}
spl_map['!SPL'] = lambda r: 'SPL'
spl_map['SPLID'] = lambda r: ' '
spl_map['TRNSTYPE'] = lambda r: 'CHECK'
spl_map['DATE'] = lambda r: r['Date'].strftime(
'%m/%d/%Y') #'{dt.month}/{dt.day}/{dt.year}'.format(dt=r['Date'])
spl_map['ACCNT'] = lambda r: 'Operational Expenses:Association ' + \
'Administration:Bank Fees:PayPal Fees'
spl_map['CLASS'] = lambda r: 'Other'
spl_map['AMOUNT'] = lambda r: abs(r['Gross'])
spl_map['CLEAR'] = lambda r: 'N'
spl_map['REIMBEXP'] = lambda r: 'NOTHING'
fees = paypal.selecteq('Type', 'Preapproved Payment Sent')
fees_cut = fees.cut(*source_fields)
trns_table = get_tables_from_mapping(fees_cut, trns_fields, trns_map)
spl_table = get_tables_from_mapping(fees_cut, spl_fields, spl_map)
iif_file = open(iif_path, 'a')
writer = csv.writer(iif_file, delimiter='\t', lineterminator='\n')
# .IIF HEADER
writer.writerow(list(trns_table.header()) + [''] * 15)
writer.writerow(list(spl_table.header()) + [''] * 15)
writer.writerow(['!ENDTRNS'] + [''] * 31)
trns_data = trns_table.data()
spl_data = spl_table.data()
# Now write each transaction one at a time
for row_num in range(len(trns_data)):
writer.writerow(list(trns_data[row_num]) + [''] * 15)
writer.writerow(list(spl_data[row_num]) + [''] * 15)
writer.writerow(['ENDTRNS'])
iif_file.close()
return etl.complement(paypal, fees)
if DEV:
print(etl.look(source_dor_condo_rows))
# Read DOR Parcel rows from engine db
print("Reading parcels...")
dor_parcel_read_stmt = '''
select parcel_id, street_address, address_low, address_low_suffix, address_low_frac, address_high, street_predir,
street_name, street_suffix, street_postdir, street_full from {dor_parcel_table}
'''.format(dor_parcel_table='dor_parcel')
engine_dor_parcel_rows = etl.fromdb(pg_db, dor_parcel_read_stmt)
if DEV:
print(etl.look(engine_dor_parcel_rows))
# Get duplicate parcel_ids:
non_unique_parcel_id_rows = engine_dor_parcel_rows.duplicates(key='parcel_id')
unique_parcel_id_rows = etl.complement(engine_dor_parcel_rows,
non_unique_parcel_id_rows)
# Get address comps for condos by joining to dor_parcel with unique parcel_id on parcel_id:
print("Relating condos to parcels...")
joined = etl.join(source_dor_condo_rows, unique_parcel_id_rows, key='parcel_id') \
.convert('street_address', lambda a, row: row.street_address + ' # ' + row.unit_num, pass_row=True)
print("joined rowcount: ", etl.nrows(joined))
if DEV:
print(etl.look(joined))
# Calculate errors
print("Calculating errors...")
unjoined = etl.antijoin(source_dor_condo_rows, joined, key='source_object_id')
print("unjoined rowcount: ", etl.nrows(unjoined))
dor_condos_unjoined_unmatched = etl.antijoin(unjoined,
non_unique_parcel_id_rows,
def append_sales_as_deposits(paypal, iif_path):
"""
Take a paypal csv file (already sucked into PETL) and spit out
the deposits
"""
# SPECIFY SOURCE/DEST FIELD NAMES
payment_source_fields = [
'Date', 'Name', 'Email', 'Gross', 'Fee', 'Transaction ID'
]
item_source_fields = [
'Date', 'Name', 'Item Title', 'Item ID', 'Quantity', 'Gross',
'Transaction ID'
]
trns_fields = [
'!TRNS', 'TRNSID', 'TRNSTYPE', 'DATE', 'ACCNT', 'NAME', 'CLASS',
'AMOUNT', 'DOCNUM', 'MEMO', 'CLEAR', 'PAYMETH'
]
# "spl" for "split", a term in QuickBooks for how a transaction is broken
# down (or "split") into the items underlying the transaction
# Like if you spent $10, the split might be two rows: $2 for a banana and
# $8 for a magazine.
spl_fields = [
'!SPL', 'SPLID', 'TRNSTYPE', 'DATE', 'ACCNT', 'NAME', 'CLASS',
'AMOUNT', 'DOCNUM', 'MEMO', 'CLEAR', 'PAYMETH'
]
#fee_acct = 'Operational Expenses:Association Administration:Bank Fees:PayPal Fees'
fee_acct = 'Competition Expenses:Sales:Ticketing:PayPal Fees'
discount_acct = 'Competition Expenses:Advertising & Sponsorship:Promotions:Early Bird'
# SPECIFY SOURCE/DEST MAPPINGS
# Here's how the QuickBooks file really maps to PayPal
trns_map = {}
trns_map['!TRNS'] = lambda r: 'TRNS'
trns_map['TRNSID'] = lambda r: ' '
trns_map['TRNSTYPE'] = lambda r: 'DEPOSIT'
trns_map['NAME'] = lambda r: r['Name']
trns_map['DATE'] = lambda r: r['Date'].strftime(
'%m/%d/%Y') #'{dt.month}/{dt.day}/{dt.year}'.format(dt=r['Date'])
trns_map['ACCNT'] = lambda r: 'PayPal Account'
trns_map['CLASS'] = lambda r: '' # The real class is in the split items
trns_map['AMOUNT'] = lambda r: round(r['Gross'] - abs(r['Fee']), 2)
trns_map['MEMO'] = lambda r: 'TicketLeap ticket sale'
trns_map['CLEAR'] = lambda r: 'N'
spl_map = {}
spl_map['!SPL'] = lambda r: 'SPL'
spl_map['TRNSTYPE'] = lambda r: 'DEPOSIT'
spl_map['DATE'] = lambda r: r['Date'].strftime(
'%m/%d/%Y') #'{dt.month}/{dt.day}/{dt.year}'.format(dt=r['Date'])
spl_map['CLEAR'] = lambda r: 'N'
spl_map['PAYMETH'] = lambda r: 'Paypal'
# The ticket sale
spl_map_sale = spl_map.copy()
spl_map_sale['NAME'] = lambda r: r['Name']
spl_map_sale['MEMO'] = lambda r: r['Item Title'] + ' ' + r['Item ID']
# For some reason QuickBooks wants the sale amount to be negative and the
# FEE (see spl_map_fee below) to be positive! Ah, QuickBooks...
spl_map_sale['AMOUNT'] = lambda r: round(-abs(r['Gross']), 2)
# The fee (associated with the payment)
spl_map_fee = spl_map.copy()
spl_map_fee['ACCNT'] = lambda r: fee_acct
spl_map_fee[
'MEMO'] = lambda r: 'Standard PayPal $0.30 + 2.9% for TicketLeap ticket sale fulfillment'
spl_map_fee['AMOUNT'] = lambda r: round(abs(r['Fee']), 2)
# The discount (associated with the payment)
spl_map_discount = spl_map.copy()
spl_map_discount['NAME'] = lambda r: r['Name']
spl_map_discount['ACCNT'] = lambda r: discount_acct
spl_map_discount['MEMO'] = lambda r: 'Discount for buying early'
# PREPARE CART PAYMENTS TABLE
# Sales receipts are organized in the CSV file as a row to summarize,
# (cart payment), plus one or more rows for each of the items purchased.
cart_payments = paypal.selecteq('Type', 'Shopping Cart Payment Received')
# Ignore refunds
cart_payments = cart_payments.selecteq('Status', 'Completed')
# Abort if no sales occurred
if cart_payments.nrows() == 0:
return paypal
cart_payments_cut = cart_payments.cut(*payment_source_fields)
# PREPARE CART ITEMS TABLE
cart_items = paypal.selecteq('Type', 'Shopping Cart Item')
cart_items_cut = cart_items.cut(*item_source_fields)
# WRITE THE IIF FILE
iif_file = open(iif_path, 'a')
writer = csv.writer(iif_file, delimiter='\t', lineterminator='\n')
# Write the .IIF header
writer.writerow(trns_fields + [''] * 22)
writer.writerow(spl_fields + [''] * 22)
writer.writerow(['!ENDTRNS'] + [''] * 32)
# Write each transaction to the IIF file
for tranID in cart_payments.columns()['Transaction ID']:
cur_cart_payment = cart_payments_cut.selecteq('Transaction ID', tranID)
cur_cart_items = cart_items_cut.selecteq('Transaction ID', tranID)
#---------------
# I think there should just be one payment line per transaction ID
assert (cur_cart_payment.nrows() == 1)
trns_table = get_tables_from_mapping(cur_cart_payment, trns_fields,
trns_map)
trns_total = trns_table.values('AMOUNT')[0]
# Write the master payment line for the transaction
# We assume there's one row (see assert above)
writer.writerow(list(trns_table.data()[0]) + [''] * 22)
#---------------
# Handle the split lines: (1) the fee, and (2) the cart items,
# and the (3) discount, if any
# Figure out the class (assume the cart is full of items from only
# one competition. If not the only problem will be the fee will be
# partly misallocated. That's not a big deal!)
item_class, item_account = qb_account(
cur_cart_items.values('Item Title')[0])
spl_map_fee['CLASS'] = lambda r: item_class
# (1) The fee associated with the whole transaction.
spl_fee_table = get_tables_from_mapping(cur_cart_payment, spl_fields,
spl_map_fee)
# Again we can assume there's one row (see assert above)
writer.writerow(list(spl_fee_table.data()[0]) + [''] * 22)
# (2) Handle the split lines for the cart items
spl_sale_table = get_tables_from_mapping(cur_cart_items, spl_fields,
spl_map_sale)
spl_total = spl_fee_table.values('AMOUNT')[0]
spl_sale_data = spl_sale_table.records()
for item in spl_sale_data:
item_as_list = list(item)
# Figure out the account and class for this item
item_class, item_account = qb_account(item['MEMO'])
item_as_list[item.flds.index('CLASS')] = item_class
item_as_list[item.flds.index('ACCNT')] = item_account
# Record the sale lines itemizing what was in the cart
writer.writerow(item_as_list + [''] * 22)
spl_total += item_as_list[item.flds.index('AMOUNT')]
# (3) The discount associated with the whole transaction. (if any)
# since paypal does not actually provide this as a separate field
# we must infer it from the difference between the transaction
# payment total and the split total
if (abs(trns_total + spl_total) >= 0.01):
spl_map_discount['CLASS'] = lambda r: item_class
spl_map_discount['AMOUNT'] = \
lambda r: -round(trns_total + spl_total, 2)
spl_discount_table = get_tables_from_mapping(
cur_cart_payment, spl_fields, spl_map_discount)
# Again we can assume there's one row (see assert above)
writer.writerow(list(spl_discount_table.data()[0]) + [''] * 22)
#---------------
# Write each transactions' closing statement in the IIF
writer.writerow(['ENDTRNS'] + [''] * 32)
iif_file.close()
# RETURN UNUSED ROWS
# Return the original paypal table, minus all the entries
# we just processed
paypal_without_cart_sales = etl.complement(paypal, cart_payments)
paypal_without_cart_sales = etl.complement(paypal_without_cart_sales,
cart_items)
return paypal_without_cart_sales
# complement()
##############
import petl as etl
a = [['foo', 'bar', 'baz'],
['A', 1, True],
['C', 7, False],
['B', 2, False],
['C', 9, True]]
b = [['x', 'y', 'z'],
['B', 2, False],
['A', 9, False],
['B', 3, True],
['C', 9, True]]
aminusb = etl.complement(a, b)
aminusb
bminusa = etl.complement(b, a)
bminusa
# recordcomplement()
####################
import petl as etl
a = [['foo', 'bar', 'baz'],
['A', 1, True],
['C', 7, False],
['B', 2, False],
['C', 9, True]]
b = [['bar', 'foo', 'baz'],
if DEV:
print(etl.look(source_dor_condo_rows))
# Read DOR Parcel rows from engine db
print("Reading parcels...")
dor_parcel_read_stmt = '''
select parcel_id, street_address, address_low, address_low_suffix, address_low_frac, address_high, street_predir,
street_name, street_suffix, street_postdir, street_full from {dor_parcel_table}
'''.format(dor_parcel_table='dor_parcel')
engine_dor_parcel_rows = etl.fromdb(pg_db, dor_parcel_read_stmt)
if DEV:
print(etl.look(engine_dor_parcel_rows))
# Get duplicate parcel_ids:
non_unique_parcel_id_rows = engine_dor_parcel_rows.duplicates(key='parcel_id')
unique_parcel_id_rows = etl.complement(engine_dor_parcel_rows, non_unique_parcel_id_rows)
# Get address comps for condos by joining to dor_parcel with unique parcel_id on parcel_id:
print("Relating condos to parcels...")
joined = etl.join(source_dor_condo_rows, unique_parcel_id_rows, key='parcel_id') \
.convert('street_address', lambda a, row: row.street_address + ' # ' + row.unit_num, pass_row=True)
print("joined rowcount: ", etl.nrows(joined))
if DEV:
print(etl.look(joined))
# Calculate errors
print("Calculating errors...")
unjoined = etl.antijoin(source_dor_condo_rows, joined, key='source_object_id')
print("unjoined rowcount: ", etl.nrows(unjoined))
dor_condos_unjoined_unmatched = etl.antijoin(unjoined, non_unique_parcel_id_rows, key='parcel_id').addfield('reason', 'non-active/remainder mapreg')
print("non-active/remainder mapreg error rowcount: ", etl.nrows(dor_condos_unjoined_unmatched))
def eliminate_cancellations(paypal_given):
"""
Eliminate the cancellations, except for the Cancelled Fee amounts
associated with refunded Shopping Cart Payments Received.
Those remain, in the amount of $0.30.
"""
# Type = 'Payment Sent', Status = 'Canceled'
# cancels with
# Type = 'Cancelled Payment', Status = 'Complete'
paypal = paypal_given.select(lambda r: r['Status'] in ['Canceled'] and r[
'Type'] in ['Payment Sent'],
complement=True)
paypal = paypal.select(lambda r: r['Status'] in ['Completed'] and r['Type']
in ['Cancelled Payment'],
complement=True)
# Type = 'Shopping Cart Payment Received', Status = 'Refunded'
# PLUS
# Type = 'Payment Sent', Status = 'Refunded'
# cancels with
# Type = 'Refund', Status = 'Complete'
# + $0.30 * count(Type = 'Shopping Cart Payment Received',
# Status = 'Refunded')
# but that little difference is handled by revising the amount of
# the PayPal Cancelled Fee
paypal = paypal.select(lambda r: r['Status'] in ['Refunded'] and r[
'Type'] in ['Shopping Cart Payment Received', 'Payment Sent'],
complement=True)
paypal = paypal.select(
lambda r: r['Status'] in ['Completed'] and r['Type'] in ['Refund'],
complement=True)
# Grab a copy of just the cancelled trades so we can verify they net to 0
# We have to obtain the cancelled transactions before changing the
# PayPal fee so the complement operation will work correctly
cancelled_transactions = etl.complement(paypal_given, paypal)
paypal = paypal.convert(
{
'Fee': lambda v: -0.3,
'Gross': lambda v: 0
},
where=lambda r: r['Type'] == 'Cancelled Fee' and r[
'Name'] == 'PayPal' and r['Status'] == 'Completed')
num_fee_refunds = paypal.select(
lambda r: r['Type'] == 'Cancelled Fee' and r['Name'] == 'PayPal' and r[
'Status'] == 'Completed').nrows()
# DEBUG: list the cancelled trades explicitly
#input_folder = 'C:\\Users\\Michael\\Desktop\\TicketLeapToQuickBooks'
#cancel_path = os.path.join(input_folder, 'cancelled_trades.csv')
#cancelled_transactions.tocsv(cancel_path, write_header=True)
# Verify that our cancelled trades all net to 0
# (We simply can't assert that the sum == 0 because of machine epsilon)
assert (abs(sum(cancelled_transactions.values('Gross').toarray())) < 0.01)
# The fees are supposed to cancel except for -num_fee_refunds * 0.3
assert (abs(
sum(cancelled_transactions.values('Fee').toarray()) -
-num_fee_refunds * 0.3) < 0.01)
# Finally, let's eliminate things that don't net against anything else
# but should still not appear in the transaction list:
# Ignore cancelled invoices (these don't net with anything but instead
# should never appear against the balance at all)
paypal = paypal.select(lambda r: r['Status'] == 'Canceled' and r['Type'] in
['Invoice Sent', 'Invoice item'],
complement=True)
# Ignore refunded shopping cart items (again these don't net with
# anything, they just serve to double-count the amount since they
# double with Shopping Cart Payment Received, so we must eliminate them)
paypal = paypal.select(lambda r: r['Type'] in ['Shopping Cart Item'] and r[
'Status'] in ['Canceled', 'Refunded'],
complement=True)
return paypal
from __future__ import absolute_import, print_function, division
# complement()
##############
import petl as etl
a = [['foo', 'bar', 'baz'], ['A', 1, True], ['C', 7, False], ['B', 2, False],
['C', 9, True]]
b = [['x', 'y', 'z'], ['B', 2, False], ['A', 9, False], ['B', 3, True],
['C', 9, True]]
aminusb = etl.complement(a, b)
aminusb
bminusa = etl.complement(b, a)
bminusa
# recordcomplement()
####################
import petl as etl
a = [['foo', 'bar', 'baz'], ['A', 1, True], ['C', 7, False], ['B', 2, False],
['C', 9, True]]
b = [['bar', 'foo', 'baz'], [2, 'B', False], [9, 'A', False], [3, 'B', True],
[9, 'C', True]]
aminusb = etl.recordcomplement(a, b)
aminusb
bminusa = etl.recordcomplement(b, a)
bminusa
# diff()
