#!/usr/bin/env python import codecs import csv import logging import os import shutil import time __author__ = 'holger.knust' 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 # missing_ean = [] 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=';') # total = 0.0 # 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 qty == 0: continue if sku: if sku.find("_") > 0: ean = sku[:sku.find("_")].lower().strip() # ext = sku[:sku.find("_")] 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 and ean in ref_dict: 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 {0} with price {1}".format(ean, purchase_price)) writer.writerow([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"]) else: exc_writer.writerow([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"]) else: logging.warning("%s not found ", sku) else: header_read = True if "Händler-SKU" in row: sku_index = row.index("Händler-SKU") if "ASIN" in row: asin_index = row.index("ASIN") if "AFN-Lagerbestand" in row: qty_index = row.index("AFN-Lagerbestand") if "Titel" in row: prod_name_index = row.index("Titel") if sku_index < 0 or asin_index < 0 or qty_index < 0 or prod_name_index < 0: raise Exception("Could not find needed columns: SKU, ASIN, Titel, AFN-Lagerbestand") 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 ean in missing_ean: # writer.writerow(['ERROR', '', '', '', '', '', '', '', ean, '', '', '', '', '', '', '', '', '', '', '', '', # '', '',]) 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' # prefix = 'dg_' if f[:3].lower() == 'dg_' else '' # out_file_name = prefix + 'fba_artikelanlage.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.error(e) print(e) shutil.move(in_file, err_file) os.remove(out_file) def main(): # data_root = r"/home/holger/git/bitbucket/thelen/data/" data_root = r"/ftproot/" project_data_root = os.path.join(data_root, "Matching_FBA_Artikelanlage") process(project_data_root) print("Done.") if __name__ == "__main__": main()