#!/usr/bin/env python # -*- coding: utf-8 -*- import csv import codecs from datetime import datetime import logging import logging.config import os import shutil import time def get_column_index(row, col_name): if not col_name: return -1 if not row: return -1 row_lower = [x.lower() for x in row] col_name_lower = col_name.lower() if col_name_lower in row_lower: return row_lower.index(col_name_lower) return -1 def setup_logging(log_path): # Set up logging log_file = os.path.join(log_path, '{}.log'.format(time.strftime("%Y%m%d-%H%M%S"))) logging.config.dictConfig({ 'version': 1, 'disable_existing_loggers': False, 'formatters': { 'standard': { 'format': '%(asctime)s [%(levelname)s] %(name)s: %(message)s' }, }, 'handlers': { 'default': { 'level': 'NOTSET', 'class': 'logging.StreamHandler', 'formatter': 'standard' }, 'logfile': { 'filename': log_file, 'class': 'logging.FileHandler', 'formatter': 'standard' } }, 'loggers': { '': { 'handlers': ['default', 'logfile'], 'level': 'NOTSET', 'propagate': True } } }) def read_ref(ref_path): ref_dict = dict() header_read = False ean_index = -1 item_index = -1 ref_filename = "Referenz_Matching_Vendor_Refs.csv" ref_file = os.path.join(ref_path, ref_filename) logging.debug("Reading ref file %s...", ref_file) with codecs.open(ref_file, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') for row in reader: if header_read: item_code = row[item_index].strip() ean = row[ean_index].strip() if not item_code: continue if item_code not in ref_dict: if len(ean) == 14: ean = ean[1:] ref_dict[item_code] = ean else: if ean != ref_dict[item_code]: logging.warning("Item code %s already read as ean %s. Ignoring %s ", item_code, ref_dict[item_code], ean) else: # EAN;VariationNo ean_index = get_column_index(row, "EAN") item_index = get_column_index(row, "VariationNo") if ean_index < 0 or item_index < 0: raise Exception("Cannot find all columns") header_read = True logging.debug("Read a total of %d ref keys.", len(ref_dict)) return ref_dict def read_ref_substitution(ref_path): ref_dict = dict() header_read = False order_size_index = -1 right_size_index = -1 ref_filename = "Referenz_Matching_Vendor_Sizes.csv" ref_file = os.path.join(ref_path, ref_filename) logging.debug("Reading substitution ref file %s...", ref_file) with codecs.open(ref_file, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') for row in reader: if header_read: order_size = row[order_size_index].strip() right_size = row[right_size_index].strip() if not order_size or not right_size: continue if order_size not in ref_dict: ref_dict[order_size] = right_size else: # Order_Size;Right_Size order_size_index = get_column_index(row, "Order_Size") right_size_index = get_column_index(row, "Right_Size") if order_size_index < 0 or right_size_index < 0: raise Exception("Cannot find all columns") header_read = True logging.debug("Read a total of %d substitution ref keys.", len(ref_dict)) return ref_dict def read_orders(in_path, proc_path, err_path, ref_dict, ref_subst_dict): order_qty_dict = dict() deliv_dt_dict = dict() bad_list = list() bad_files = list() first_file = True files = os.listdir(in_path) logging.debug("Reading orders from %s", in_path) for filename in files: header_read = False product_id_index = -1 size_index = -1 qty_index = -1 deliv_dt_index = -1 in_file = os.path.join(in_path, filename) proc_file = os.path.join(proc_path, filename) err_file = os.path.join(err_path, filename) logging.debug("Reading order file %s...", filename) try: with codecs.open(in_file, "rb", encoding='utf-8') as f: reader = csv.reader(f, delimiter=';') line_num = 0 for row in reader: line_num += 1 if header_read: product_id = row[product_id_index].strip() size = row[size_index].strip() if not product_id: logging.warning("Blank product id on line %d in %s", line_num, filename) row.append("Blank product id") bad_list.append(row) continue if not size: logging.warning("Blank size on line %d in %s", line_num, filename) row.append("Blank size") bad_list.append(row) continue if size in ref_subst_dict: new_size = ref_subst_dict[size] size = new_size key = product_id + "#" + size if key in ref_dict: ean = ref_dict[key].strip() qty = 0 try: qty = int(row[qty_index]) except ValueError: logging.warning("%s is not a numeric quantity in file %s", row[qty_index], filename) if not ean in order_qty_dict: order_qty_dict[ean] = qty else: order_qty_dict[ean] += qty if not ean in deliv_dt_dict: deliv_dt_dict[ean] = row[deliv_dt_index] else: new_date = datetime.strptime(row[deliv_dt_index].replace(".", "-"), "%d-%m-%Y") old_date = datetime.strptime(deliv_dt_dict[ean].replace(".", "-"), "%d-%m-%Y") if new_date < old_date: deliv_dt_dict[ean] = new_date else: logging.warning("Item %s not found in ref", key) else: product_id_index = get_column_index(row, "ProductID") size_index = get_column_index(row, "Size") qty_index = get_column_index(row, "Value34") if qty_index < 0: qty_index = get_column_index(row, "Value35") deliv_dt_index = get_column_index(row, "DeliveryDate33") if deliv_dt_index < 0: deliv_dt_index = get_column_index(row, "DeliveryDate34") if product_id_index < 0 or size_index < 0 or qty_index < 0 or deliv_dt_index < 0: bad_files.append(err_file) raise Exception("Cannot find all columns in file {}. Aborting...".format(filename)) header_read = True if first_file: row.append("Problem") bad_list.append(row) first_file = False shutil.move(in_file, proc_file) except Exception as ex: logging.error(ex) shutil.move(in_file, err_file) if len(bad_list) == 1: bad_list = list() logging.debug("Finished with orders.") return order_qty_dict, deliv_dt_dict, bad_files, bad_list def write_list(items, filename): if not items or len(items) < 1: return with open(filename, 'wb') as fp: for item in items: fp.write("{}\n".format(item).encode("utf-8")) def write_refs(out_path, order_qty_dict, delivery_dt_dict): with codecs.open(os.path.join(out_path, "Ref_Temp_Bestellmenge.csv"), "w", encoding='utf-8') as of: writer = csv.writer(of, delimiter=';', quoting=csv.QUOTE_NONNUMERIC) writer.writerow(["sku", "Quant"]) for ean in order_qty_dict.keys(): writer.writerow([ean, order_qty_dict[ean]]) with codecs.open(os.path.join(out_path, "Ref_Temp_Lieferdatum.csv"), "w", encoding='utf-8') as of: writer = csv.writer(of, delimiter=';', quoting=csv.QUOTE_NONNUMERIC) writer.writerow(["sku", "DeliveryDate33"]) for ean in order_qty_dict.keys(): writer.writerow([ean, delivery_dt_dict[ean]]) def process_files(data_root): 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") path_ref = os.path.join(data_root, "ref") files = os.listdir(path_in) num_files = len(files) setup_logging(path_log) logging.info("Found %d files...", num_files) if num_files == 0: return ref_dict = None try: ref_dict = read_ref(path_ref) ref_subst_dict = read_ref_substitution(path_ref) except Exception as e: logging.error(e) if ref_dict: try: order_qty_dict, delivery_dt_dict, bad_files_list, bad_in_list = read_orders(path_in, path_proc, path_err, ref_dict, ref_subst_dict) write_list(bad_files_list, os.path.join(path_out, "missing_column_headers.txt")) write_list(bad_in_list, os.path.join(path_out, "blank_id_or_size.txt")) write_refs(path_out, order_qty_dict, delivery_dt_dict) except Exception as e: logging.error(e) logging.shutdown() def main(): data_root = r"/ftproot/" # data_root = r"/home/holger/git/bitbucket/thelen/data/" # data_root = r"c:/temp/thelen/" data_root = os.path.join(data_root, "Matching_Vendor_Temp_Refs") process_files(data_root) if __name__ == "__main__": main()