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()