#!/usr/bin/env python3.7 import codecs import csv import logging import os import shutil import time HDR_RETAIL_SKU="sku" # "Händler-SKU" HDR_ASIN="asin" # "ASIN" HDR_PROD_NAME="product-name" # "Titel" HDR_WH_QTY="afn-fulfillable-quantity" # "AFN-Lagerbestand" def fix_nulls(s): for line in s: yield line.replace('\0', '') def read_ean_lookup(ref_path): ref_dict = {} header_read = False header_names = [] full_ref_path = os.path.join(ref_path, "Ref_SSW_FBA.csv") with codecs.open(full_ref_path, "rU", encoding='utf-8') as f: reader = csv.reader(fix_nulls(f), delimiter=';') ean_idx = -1 price_idx = -1 item_no_idx = -1 item_prod_idx = -1 for row in reader: if header_read: ean = row[ean_idx].strip() price_euro = row[price_idx] item_no = row[item_no_idx] item_prod = row[item_prod_idx] price_conv = price_euro.replace(".", "").replace(",", ".") try: price = float(price_conv) ref_dict[ean] = [price, item_no, item_prod] except ValueError as ve: logging.error(ve) logging.error("Failed row %s with price %s", ean, price_conv) else: ean_idx = row.index("VariationEAN") price_idx = row.index("PurchasePrice") item_no_idx = row.index("ItemNo") item_prod_idx = row.index("ItemProducer") row.pop(0) row.pop(0) header_names = row header_read = True return ref_dict, header_names def convert_fba(in_file, out_file, ref_dict, headers, extension, exception_file): header_read = False sku_index = -1 qty_index = -1 asin_index = -1 prod_name_index = -1 output_dict = dict() with codecs.open(in_file, "r", encoding='utf-8') as f, \ codecs.open(out_file, "w", encoding='utf-8') as of, \ codecs.open(exception_file, "w", encoding='utf-8') as ef: reader = csv.reader(f, delimiter='\t') writer = csv.writer(of, delimiter=';') exc_writer = csv.writer(ef, delimiter=';') qty = 0 for row in reader: if header_read: sku = row[sku_index] asin = row[asin_index] prod_name = row[prod_name_index] try: qty = int(row[qty_index]) except ValueError: qty = 0 if sku: if sku.find("_") > 0: ean = sku[:sku.find("_")].lower().strip() elif sku.find("-") > 0: ean = sku[:sku.find("-")].lower().strip() else: ean = sku.lower().strip() if len(ean) == 12: ean = "0" + ean new_sku = ean + '-' + extension retail_price = 0 purchase_price_rounded = 0 purchase_price_half = 0 other_fields = ['', ''] if len(ean) != 13 or ean not in ref_dict: exc_writer.writerow([new_sku, asin, prod_name, 'New_Fba', 2, 0, qty, 19, ean, 0, 0, 0] + other_fields + ["5,0", "FBA_SV24", "1", "0", "10", "20", "30", "40", "50"]) continue ref_values = ref_dict[ean] other_fields = ref_values[1:] purchase_price = ref_values[0] try: retail_price = round(purchase_price * 2.0 + 3.5, 2) purchase_price_rounded = round(purchase_price, 2) purchase_price_half = round(purchase_price / 2.0, 2) except Exception as ex: logging.error(ex) logging.error("Failed to write %s with price %s", ean, purchase_price) key = str(qty).zfill(8) out_row = [new_sku, asin, prod_name, 'New_Fba', 2, 0, qty, 19, ean, retail_price, purchase_price_rounded, purchase_price_half] \ + other_fields + \ ["5,0", "FBA_SV24", "1", "0", "10", "20", "30", "40", "50"] if key in output_dict: output_dict[key].append(out_row) else: output_dict[key] = [out_row] else: logging.warning("%s not found ", sku) else: header_read = True if HDR_RETAIL_SKU in row: sku_index = row.index(HDR_RETAIL_SKU) if HDR_ASIN in row: asin_index = row.index(HDR_ASIN) if HDR_WH_QTY in row: qty_index = row.index(HDR_WH_QTY) if HDR_PROD_NAME in row: prod_name_index = row.index(HDR_PROD_NAME) if sku_index < 0 or asin_index < 0 or qty_index < 0 \ or prod_name_index < 0: exc_msg = f"Could not find required columns:" \ + "{HDR_RETAIL_SKU}, {HDR_ASIN}, {HDR_TITLE}," \ + "{HDR_QTY}" raise Exception(exc_msg) writer.writerow(['sku', 'asin', 'product-name', 'Zustand', 'FPB', 'Versandkosten', 'Bestand', 'MWST', 'EAN', "VK", "EK", "EK_halb"] + headers + ["Neu5", "Lagerplatz", "Discount_an", "Discount_aus", "Discount_10", "Discount_20", "Discount_30", "Discount_40", "Discount_50"]) for k in sorted(output_dict.keys()): for r in output_dict[k]: writer.writerow(r) def process(data_root): path_ref = os.path.join(data_root, "ref") path_in = os.path.join(data_root, "in") path_out = os.path.join(data_root, "out") path_proc = os.path.join(data_root, "processed") path_err = os.path.join(data_root, "error") path_log = os.path.join(data_root, "log") files = os.listdir(path_in) num_files = len(files) print("Found {0} files...".format(num_files)) if num_files == 0: return ref_dict, headers = read_ean_lookup(path_ref) for f in files: log_file_name = time.strftime("%Y%m%d-%H%M%S") + '.log' in_file = os.path.join(path_in, f) out_file_name = os.path.splitext(f)[0] + '.csv' exception_file_name = os.path.splitext(f)[0] + '_error.csv' out_file = os.path.join(path_out, out_file_name) exception_file = os.path.join(path_out, exception_file_name) proc_file = os.path.join(path_proc, f) err_file = os.path.join(path_err, f) log_file = os.path.join(path_log, log_file_name) logging.basicConfig(filename=log_file, level=logging.DEBUG) logging.debug("Calculating inventory for %s...", in_file) if os.path.isfile(in_file): try: logging.debug("Converting %s to %s...", in_file, out_file) full_filename = os.path.basename(in_file) if full_filename.find(".") > -1: extension = full_filename[:full_filename.find(".")] else: extension = full_filename convert_fba(in_file, out_file, ref_dict, headers, extension, exception_file) shutil.move(in_file, proc_file) except Exception as e: logging.exception("error") print(e) shutil.move(in_file, err_file) os.remove(out_file) 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 project_data_root = os.path.join(data_root, "Matching_FBA_Artikelanlage") process(project_data_root) print("Done.") if __name__ == "__main__": main()