#!/usr/bin/env python import os import codecs import csv import locale import logging import operator import shutil import xlsxwriter __author__ = 'Holger Knust' def parse_float(value): if type(value) is str: value = value.replace('.','').replace(',','.') try: total = float(value) except ValueError: total = 0.0 return round(total,2) def sum_dicts(key, dct, amount): if key in dct: dct[key] += amount else: dct[key] = amount def summarize_transactions(in_file, out_file): header_read = False type_index = -1 ship_index = -1 revenue_index = -1 ship_refund_index = -1 gift_wrap_refund_index = -1 market_refund_index = -1 sales_fee_index = -1 amazon_ship_fee_index = -1 misc_tx_fees_index = -1 total_index = -1 type_dict = dict() with codecs.open(in_file, "r", encoding='utf-8') as f, codecs.open(out_file, "w", encoding='utf-8') as of: reader = csv.reader(f, delimiter=',') writer = csv.writer(of, delimiter=';') for row in reader: if header_read: type = row[type_index] ship = row[ship_index] revenue = parse_float(row[revenue_index]) ship_refund = parse_float(row[ship_refund_index]) gift_wrap = parse_float(row[gift_wrap_refund_index]) market_refund = parse_float(row[market_refund_index]) sales_fee = parse_float(row[sales_fee_index]) amazon_ship_fee = parse_float(row[amazon_ship_fee_index]) misc_tx_fees = parse_float(row[market_refund_index]) misc = parse_float(row[misc_index]) total = parse_float(row[total_index]) if type in type_dict: current_type = type_dict[type] if ship in current_type: e1,e2,e3,revenue_total, ship_refund_total, gift_wrap_total, market_refund_total, sales_fee_total, \ amazon_ship_fee_total, misc_tx_fees_total, misc_total, total_total = current_type[ship] current_type[ship] = ['',ship,'', round(revenue_total + revenue, 2), round(ship_refund_total + ship_refund, 2), round(gift_wrap_total + gift_wrap, 2), round(market_refund_total + market_refund, 2), round(sales_fee_total + sales_fee, 2), round(amazon_ship_fee_total + amazon_ship_fee, 2), round(misc_tx_fees_total + misc_tx_fees, 2), round(misc_total + misc, 2), round(total_total + total, 2)] else: current_type[ship] = ['',ship,'',revenue, ship_refund, gift_wrap, market_refund, sales_fee, amazon_ship_fee, misc_tx_fees, misc, total] else: ship_type = dict() ship_type[ship] = ['',ship,'',revenue, ship_refund, gift_wrap, market_refund, sales_fee, amazon_ship_fee, misc_tx_fees, misc, total] type_dict[type] = ship_type else: if 'Datum/Uhrzeit' in row: header_read = True if 'Typ' in row: type_index = row.index('Typ') if 'Versand' in row: ship_index = row.index('Versand') if 'Umsätze' in row: revenue_index = row.index('Umsätze') if 'Gutschrift für Versandkosten' in row: ship_refund_index = row.index('Gutschrift für Versandkosten') if 'Gutschrift für Geschenkverpackung' in row: gift_wrap_refund_index = row.index('Gutschrift für Geschenkverpackung') if 'Rabatte aus Werbeaktionen' in row: market_refund_index = row.index('Rabatte aus Werbeaktionen') if 'Verkaufsgebühren' in row: sales_fee_index = row.index('Verkaufsgebühren') if 'Gebühren zu Versand durch Amazon' in row: amazon_ship_fee_index = row.index('Gebühren zu Versand durch Amazon') if 'Andere Transaktionsgebühren' in row: misc_tx_fees_index = row.index('Andere Transaktionsgebühren') if 'Andere' in row: misc_index = row.index('Andere') if 'Gesamt' in row: total_index = row.index('Gesamt') if type_index < 0 or ship_index < 0 or revenue_index < 0 or ship_refund_index < 0 or \ gift_wrap_refund_index < 0 or market_refund_index < 0 or sales_fee_index <0 or \ amazon_ship_fee_index < 0 or misc_tx_fees_index < 0 or misc_index <0 or \ total_index < 0: raise Exception("Could not find needed columns") writer.writerow(['Abrechnung','','','','','','','','','','','','']) for k in type_dict: writer.writerow([k,'','','Umsätze','Gutschrift für Versandkosten','Gutschrift für Geschenkverpackung','Rabatte aus Werbeaktionen','Verkaufsgebühren','Gebühren zu Versand durch Amazon','Andere Transaktionsgebühren','Andere','Gesamt']) ship_summary = type_dict[k] for k2 in ship_summary: writer.writerow(ship_summary[k2]) writer.writerow(['','','','','','','','','','','','','']) writer.writerow(['','','','','','','','','','','','','']) writer.writerow(['Gesamtsummen','','','','','','','','','','','','']) writer.writerow(['','','','Umsätze','Gutschrift für Versandkosten','Gutschrift für Geschenkverpackung','Rabatte aus Werbeaktionen','Verkaufsgebühren','Gebühren zu Versand durch Amazon','Andere Transaktionsgebühren','Andere','Gesamt']) ship_list = list() revenue_dict = dict() ship_refund_dict = dict() gift_wrap_total_dict = dict() market_refund_dict = dict() sales_fee_dict = dict() amazon_ship_fee_dict = dict() misc_tx_fee_dict = dict() misc_dict = dict() total_dict = dict() for type in type_dict: # print(type) summary_by_ship_type = type_dict[type] # print(summary_by_ship_type) for k2 in summary_by_ship_type: if k2 not in ship_list: ship_list.append(k2) # print("K2 : {}".format(k2)) current_ship = summary_by_ship_type[k2] # print("Current ship : {}".format(current_ship)) e1,e2,e3,revenue_total, ship_refund_total, gift_wrap_total, market_refund_total, sales_fee_total, \ amazon_ship_fee_total, misc_tx_fees_total, misc_total, total_total = current_ship sum_dicts(k2, revenue_dict, revenue_total) sum_dicts(k2, ship_refund_dict, ship_refund_total) sum_dicts(k2, gift_wrap_total_dict, gift_wrap_total) sum_dicts(k2, market_refund_dict, market_refund_total) sum_dicts(k2, sales_fee_dict, sales_fee_total) sum_dicts(k2, amazon_ship_fee_dict, amazon_ship_fee_total) sum_dicts(k2, misc_tx_fee_dict, misc_tx_fees_total) sum_dicts(k2, misc_dict, misc_total) sum_dicts(k2, total_dict, total_total) # print(total_total) for k in sorted(ship_list): current_ship = k revenue_rs = revenue_dict[k] if k in revenue_dict else 0.0 ship_refund_rs = ship_refund_dict[k] if k in ship_refund_dict else 0.0 gift_wrap_rs = gift_wrap_total_dict[k] if k in gift_wrap_total_dict else 0.0 market_refund_rs = market_refund_dict[k] if k in market_refund_dict else 0.0 sales_fee_rs = sales_fee_dict[k] if k in sales_fee_dict else 0.0 amazon_ship_fee_rs = amazon_ship_fee_dict[k] if k in amazon_ship_fee_dict else 0.0 misc_tx_fee_rs = misc_tx_fee_dict[k] if k in misc_tx_fee_dict else 0.0 misc_rs = misc_dict[k] if k in misc_dict else 0.0 total_rs = total_dict[k] if k in total_dict else 0.0 writer.writerow([current_ship, '' ,'', round(revenue_rs,2), round(ship_refund_rs,2), round(gift_wrap_rs,2), round(market_refund_rs,2), round(sales_fee_rs, 2), round(amazon_ship_fee_rs, 2), round(misc_tx_fee_rs, 2), round(misc_rs, 2), round(total_rs, 2)]) def process_files(data_root): path_in = os.path.join(data_root, "in") path_out = os.path.join(data_root, "out") path_prc = os.path.join(data_root, "processed") path_err = os.path.join(data_root, "error") path_log = os.path.join(data_root, "log") path_ref = os.path.join(data_root, "ref") files = os.listdir(path_in) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return for f in files: log_file_name = os.path.splitext(f)[0] + '.log' summary_file_name = os.path.splitext(f)[0] + '.csv' in_file = os.path.join(path_in, f) out_file = os.path.join(path_out, summary_file_name) prc_file = os.path.join(path_prc, f) err_file = os.path.join(path_err, f) log_file = os.path.join(path_log, log_file_name) if os.path.isfile(in_file): logging.basicConfig(filename=log_file, level=logging.DEBUG) logging.debug("Summarizing Amazon Invoice {0}...".format(in_file)) try: summarize_transactions(in_file, out_file) shutil.move(in_file, prc_file) except Exception as e: logging.error(e) print("Error : {}".format(e)) shutil.move(in_file, err_file) def main(): data_root = r"/ftproot/" #data_root = r"/ftproot/" # data_root = r"/home/holger/git/thelen/data/" # data_root = r"c:/temp/thelen/" invoice_data_root = os.path.join(data_root, "Matching_Abrechnung_3") process_files(invoice_data_root) if __name__ == "__main__": main()