#!/usr/bin/env python __author__ = 'holger.knust' import codecs import csv from dateutil.parser import parse import logging import os import shutil import time SKU_GOODS = '302351074' SKU_FEES = '302351076' SKU_CRED = '302351075' SKU_COMM = '302351077' SKU_OTHR = '302426130' SKU_SSW = '302351078' path_in = None path_out = None path_proc = None path_err = None path_log = None def move_files_in_group(dest_folder, source_folder, file_list): try: for f in file_list: full_source_path = os.path.join(source_folder, f) full_dest_path = os.path.join(dest_folder, f) print("Moving from {0} to {1}".format(full_source_path, full_dest_path)) shutil.move(full_source_path, full_dest_path) except Exception as ex: logging.error(ex) def write_output(customer_code, order_date, customer_id, country_code, group_dict): global path_out ext_order_id = "{0}_{1}".format(customer_code, order_date.strftime("%m-%d-%Y")) out_file = os.path.join(path_out, "{0}_{1}_{2}.csv".format(customer_code, order_date.strftime("%m-%d-%Y"), country_code)) with codecs.open(out_file, "w", encoding='utf-8') as of: writer = csv.writer(of, delimiter=';') # Write header writer.writerow(['ExternalOrderID', 'PlentyCustomerID', 'PlentyDeliveryAddresseID', 'PlentyShippingProfileID', 'PlentyShippingMethodID', 'PlentyMethodOfPaymentID', 'PlentyOrderStatus', 'PlentyReferrerID', 'ItemSKU', 'ItemQuantity', 'ItemWarehouseID', 'ItemPrice']) # write 1 row per item sku for k, v in group_dict.items(): writer.writerow([ext_order_id, customer_id, 0, 80, 6, 2, 13.8, 0, k, 1, 9, v]) def calculate_file_total(in_file, enc): header_read = False total_index = -1 with codecs.open(in_file, "r", encoding=enc) as f: reader = csv.reader(f, delimiter=';') running_sum = 0.0 for row in reader: if header_read: # print(row) try: total = float(row[total_index]) except ValueError: total = 0 running_sum += total else: header_read = True if "Gesamt" in row: total_index = row.index("Gesamt") if "SSW" in row: total_index = row.index("SSW") if total_index < 0: raise Exception("Could not find needed columns") return round(running_sum, 2) def get_item_sku(file_name): file_name_upper = file_name.upper() if "GOODS" in file_name_upper: return SKU_GOODS elif "FEES" in file_name_upper: return SKU_FEES elif "CREDIT_MEM" in file_name_upper: return SKU_CRED elif "OTHER_TRA" in file_name_upper: return SKU_OTHR elif "SSW" in file_name_upper: return SKU_SSW else: logging.error("Could not identify item sku from file name {0}".format(file_name)) return None def summarize_files_in_group(file_list, source_folder): totals_dict = dict() for gf in file_list: logging.debug("Calculating total for file {0}".format(gf)) file_total = 0.0 success = False encs = ['cp1252', 'utf-8', 'cp850'] for enc in encs: try: file_total = calculate_file_total(os.path.join(source_folder, gf), enc) success = True except Exception as ex: logging.error("Failed to summarize {0} with encoding {1}".format(gf, enc)) logging.error(ex) if success: sku = get_item_sku(gf) if sku: if sku == SKU_GOODS or sku == SKU_OTHR: totals_dict[sku] = file_total elif sku == SKU_FEES: totals_dict[sku] = -1.19 * file_total elif sku == SKU_SSW: totals_dict[sku] = -0.5 * file_total else: totals_dict[sku] = -1.0 * file_total else: logging.warning("Could not find SKU from file name for {0}".format(gf)) break if not success: logging.error("Failed to summarize {0} with any of the encoders.".format(gf)) return False, None return True, totals_dict def process_group(customer_code, customer_id, order_date, country_code, group_list, source_folder): global path_err, path_proc proc_folder = os.path.join(path_proc, customer_code) err_folder = os.path.join(path_err, customer_code) if len(group_list) == 4 or len(group_list) == 5: # process files in group success, total_dict = summarize_files_in_group(group_list, source_folder) if success: # Calculate commission # Formula : COMMIS = GOODS - CREDIT MEM * 2,9 commission = round((total_dict[SKU_GOODS] + total_dict[SKU_CRED]) * 0.029 * -1.19,2) total_dict[SKU_COMM] = commission # Write summary file write_output(customer_code, order_date, customer_id, country_code, total_dict) move_files_in_group(proc_folder, source_folder, group_list) else: # Move group to error folder logging.error("Failed to calculate totals for files : {0}".format(",".join(group_list))) move_files_in_group(err_folder, source_folder, group_list) else: # something went wrong logging.error("Not enough files ({0}) in group : {1}".format(len(group_list), ",".join(group_list))) move_files_in_group(err_folder, source_folder, group_list) def process_source_files(customer_code, customer_id, source_folder): for (dir_path, dir_names, file_names) in os.walk(source_folder): files_in_group = [] last_parsed_date = None last_country_code = None for f in sorted(file_names): current_parsed_date = parse(f[:10]) current_country_code = f[10:12] if not last_parsed_date: # first file in group last_parsed_date = current_parsed_date last_country_code = current_country_code files_in_group.append(f) elif last_parsed_date != current_parsed_date or current_country_code != last_country_code: process_group(customer_code, customer_id, last_parsed_date, last_country_code, files_in_group, source_folder) last_parsed_date = None last_country_code = None files_in_group = [f] else: # Still in same group files_in_group.append(f) # Process last group of files if len(files_in_group) > 0: process_group(customer_code, customer_id, last_parsed_date, last_country_code, files_in_group, source_folder) def process(root_folder, data_source_root): global path_in, path_out, path_proc, path_err, path_log path_in = data_source_root path_out = os.path.join(root_folder, "out") path_proc = os.path.join(root_folder, "processed") path_err = os.path.join(root_folder, "error") path_log = os.path.join(root_folder, "log") log_file_name = time.strftime("%Y%m%d-%H%M%S") + '.log' log_file = os.path.join(path_log, log_file_name) logging.basicConfig(filename=log_file, level=logging.DEBUG) console = logging.StreamHandler() console.setLevel(logging.INFO) # set a format which is simpler for console use formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s') # tell the handler to use this format console.setFormatter(formatter) # add the handler to the root logger logging.getLogger('').addHandler(console) for dir_path, dir_names, file_names in os.walk(path_in): for sub_folder in dir_names: if "_" in sub_folder: customer_code, customer_id = sub_folder.split("_") # Use original path invoice_folder = os.path.join(dir_path, sub_folder) processed_folder = os.path.join(path_proc, customer_code) error_folder = os.path.join(path_err, customer_code) if not os.path.exists(processed_folder): try: os.mkdir(processed_folder) except Exception as ex: logging.error(ex) # Abort raise Exception("Could not create processed folder {0}".format(processed_folder)) if not os.path.exists(error_folder): try: os.mkdir(error_folder) except Exception as ex: logging.error(ex) # Abort raise Exception("Could not create error folder {0}".format(error_folder)) logging.debug("Processing customer {0} with id {1}".format(customer_code, customer_id)) process_source_files(customer_code, customer_id, invoice_folder) else: logging.error("Sub folder {0} does not contain an underscore".format(sub_folder)) # Skip folder altogether continue def main(): # data_root = r"/home/holger/git/thelen/data/" data_root = r"/ftproot/" # data_root = r"c:/temp/thelen/" data_source_root = os.path.join(data_root, "Account_Abrechnung") destination_root = os.path.join(data_root, "Matching_Abrechnung_2") try: process(destination_root, data_source_root) except Exception as ex: print(ex) print("Done.") if __name__ == "__main__": main()