#!/usr/bin/env python3.6 import os import codecs import csv import logging import logging.config import shutil import xlwt import common logger = None def parse_float(value, round_to=2): try: total = float(value) except ValueError: total = 0.0 return round(total, round_to) def format_value(value): return str(round(value, 2)).replace(".", ",") def read_ean_lookup(ref_path): ref_dict = {} header_read = False full_ref_path = os.path.join(ref_path, "Ref_SSW_FBA.csv") with codecs.open(full_ref_path, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') ean_idx = -1 price_idx = -1 for row in reader: if header_read: ean = row[ean_idx].strip() price_euro = row[price_idx] price_conv = price_euro.replace(".", "").replace(",", ".") try: price = float(price_conv) ref_dict[ean] = price except ValueError as ve: logger.error(ve) msg = "Failed row {0} with price {1}".format(ean, price_conv) logger.error(msg) else: ean_idx = row.index("VariationEAN") price_idx = row.index("PurchasePrice") header_read = True return ref_dict def read_foreign_orders(foreign_path): ref_dict = {} files = os.listdir(foreign_path) for f in files: order_idx = -1 recipient_idx = -1 addr1_idx = -1 addr2_idx = -1 addr3_idx = -1 city_idx = -1 zip_idx = -1 country_idx = -1 header_read = False full_ref_path = os.path.join(foreign_path, f) with codecs.open(full_ref_path, "rb", encoding='cp1252') as rf: reader = csv.reader(rf, delimiter=';') for row in reader: if header_read: order_id = row[order_idx] recipient = row[recipient_idx] addr1 = row[addr1_idx] addr2 = row[addr2_idx] addr3 = row[addr3_idx] city = row[city_idx] postal = row[zip_idx] country = row[country_idx] if order_id in ref_dict: logger.warning("Duplicate order id %s found in %s", order_id, f) else: ref_dict[order_id] = [recipient, addr1, addr2, addr3, city, postal, country] else: order_idx = row.index("order-id") recipient_idx = row.index("recipient-name") addr1_idx = row.index("ship-address-1") addr2_idx = row.index("ship-address-2") addr3_idx = row.index("ship-address-3") city_idx = row.index("ship-city") zip_idx = row.index("ship-postal-code") country_idx = row.index("ship-country") if order_idx < 0 or recipient_idx < 0 or addr1_idx < 0 or addr2_idx < 0 or addr3_idx < 0 \ or city_idx < 0 or zip_idx < 0 or country_idx < 0: raise Exception("Not all columns found in {}".format(f)) header_read = True return ref_dict def add_price(in_file, out_file, ref_dict, foreign_orders_dict): header_read = False sku_index = -1 order_index = -1 line = 0 wb = xlwt.Workbook() ws = wb.add_sheet('Worksheet 1') with codecs.open(in_file, "r", encoding='utf-8') as f: reader = csv.reader(f, delimiter='\t', quotechar='|') for row in reader: if header_read: sku = row[sku_index] order_id = row[order_index] sku = sku[:13] if len(sku) > 13 else sku price = ref_dict[sku] if sku in ref_dict else 20.00 if order_id in foreign_orders_dict: rec_info = foreign_orders_dict[order_id] else: rec_info = ["not in foreign orders", "", "", "", "", "", ""] row = row + [str(price)] + rec_info for c in range(len(row)): ws.write(line, c, row[c]) else: if "SELLER_SKU" in row: sku_index = row.index("SELLER_SKU") if sku_index < 0: raise Exception("Could not find SELLER_SKU in {}".format(in_file)) if "TRANSACTION_EVENT_ID" in row: order_index = row.index("TRANSACTION_EVENT_ID") if sku_index < 0: raise Exception("Could not find TRANSACTION_EVENT_ID in {}".format(in_file)) row = row + ["PurchasePrice", "Recipient", "Address 1", "Address 2", "Address 3", "City", \ "Postal Code", "Country"] for c in range(len(row)): ws.write(line, c, row[c]) header_read = True line += 1 wb.save(out_file) def process_files(data_root, secondary_folder): global logger path_log = os.path.join(data_root, "log") log_file = os.path.join(path_log, __file__ + ".log") logging.config.dictConfig(common.get_logging_config(log_file)) logger = logging.getLogger(__file__) path_in = os.path.join(data_root, "in") path_out = os.path.join(data_root, "out") path_out2 = os.path.join(secondary_folder, "in") path_prc = os.path.join(data_root, "processed") path_err = os.path.join(data_root, "error") path_ref = os.path.join(data_root, "ref") path_for = os.path.join(data_root, "foreign") files = os.listdir(path_in) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return ean_ref = read_ean_lookup(path_ref) foreign_ref = read_foreign_orders(path_for) for f in files: excel_file_name = os.path.splitext(f)[0] + '.xls' in_file = os.path.join(path_in, f) out_file = os.path.join(path_out, excel_file_name) out2_file = os.path.join(path_out2, f) prc_file = os.path.join(path_prc, f) err_file = os.path.join(path_err, f) if os.path.isfile(in_file): msg = "Summarizing Amazon Invoice {0}...".format(in_file) logger.debug(msg) try: add_price(in_file, out_file, ean_ref, foreign_ref) shutil.copy(in_file, out2_file) shutil.move(in_file, prc_file) except Exception as e: logger.error(e) print("Error : {}".format(e)) shutil.move(in_file, err_file) # Move foreign order files foreign_order_files = os.listdir(path_for) for f in foreign_order_files: shutil.move(os.path.join(path_for, f), os.path.join(path_prc, f)) def main(): if os.path.exists(r"/ftproot/"): data_root = r"/ftproot/" elif os.path.exists(r"/home/holger/git/bitbucket/thelen/data/"): data_root = r"/home/holger/git/bitbucket/thelen/data/" else: print("Unknown environment. Please set data_root to data folder location. Aborting.") return meridal_data_root = os.path.join(data_root, "Matching_Meridal_Ust") weight_data_root = os.path.join(data_root, "Matching_Gewichte") process_files(meridal_data_root, weight_data_root) if __name__ == "__main__": main()