#!/usr/bin/env python
import os
import shutil
import codecs
import csv
import logging
import xlrd

__author__ = 'Holger Knust'

def convert_excel(in_file, out_file, is_first, ean_set):
    header_read = False
    ean_index = -1
    upc_index = -1
    inventory_index = -1

    mode = "w"
    if not is_first:
        mode = "a"

    with codecs.open(out_file, mode, encoding='utf-8') as of:
        writer = csv.writer(of, delimiter=';')

        wb = xlrd.open_workbook(in_file)
        sh = wb.sheet_by_index(0)
        for row_num in range(sh.nrows):
            row = sh.row_values(row_num)

            if header_read:
                upc = row[upc_index]
                inv = row[inventory_index]

                if type(upc) == float:
                    upc = str(int(upc))

                if type(inv) == float:
                    row[inventory_index] = str(int(inv))
                
                if len(upc) < 13:
                    upc = upc.zfill(13)

                row[ean_index] = str(upc)

                if upc not in ean_set:
                    writer.writerow(row)
            else:
                if 'EAN' in row:
                    ean_index = row.index('EAN')

                if 'UPC' in row:
                    upc_index = row.index('UPC')

                if 'Inventory' in row:
                    inventory_index = row.index('Inventory')

                if is_first:
                    writer.writerow(row)

                header_read = True


def process_files(data_root):
    path_in = os.path.join(data_root, "in")
    path_out = os.path.join(data_root, "out")
    path_prc = 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

    is_first = True
    ean_set = set()

    for f in files:
        base_name,extension = os.path.splitext(f)

        # we are only interested in XLSX files
        if extension.lower() != '.xls':
            continue

        log_file_name = base_name + '.log'
        out_file_name = 'UA_Gesamt.csv'

        in_file = os.path.join(path_in, f)
        out_file = os.path.join(path_out, out_file_name)
        prc_file = os.path.join(path_prc, f)
        err_file = os.path.join(path_err, f)
        log_file = os.path.join(path_log, log_file_name)

        if os.path.isfile(in_file):
            logging.basicConfig(filename=log_file,level=logging.DEBUG)

            logging.debug("Converting {0}.".format(in_file.encode("ascii","ignore")))
            try:
                convert_excel(in_file, out_file, is_first, ean_set)
                shutil.move(in_file, prc_file)
            except Exception as e:
                logging.error(e)
                shutil.move(in_file, err_file)

        is_first = False


def main():
    data_root = r"/ftproot/"
    # data_root = r"/home/holger/git/thelen/data/"

    inventory_data_root = os.path.join(data_root, "UnderArmour")
    process_files(inventory_data_root)

if __name__ == "__main__":
    main()