#!/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):
    header_read = False
    ean_index = -1
    atp_index = -1

    with codecs.open(out_file, "w", 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)
            csv_row = list()
            if header_read:
                ean = row[ean_index]

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

                # pad with zeros if we lost some
                if len(ean) < 13:
                    ean = ean.zfill(13)

                if len(ean) == 14:
                    ean = ean[1:]

                csv_row.append(ean)

                inv_amount = row[atp_index]

                if type(inv_amount) == float:
                    inv_amount = str(int(inv_amount))
                elif type(inv_amount) != str:
                    inv_amount = str(inv_amount)

                if inv_amount[0] == '>':
                    inv_amount = inv_amount[1:]

                csv_row.append(inv_amount)

                # append hard-coded items
                csv_row.append("11")
                csv_row.append("1")
            else:
                if 'EAN' in row:
                    ean_index = row.index('EAN')
                if 'ATP' in row:
                    atp_index = row.index('ATP')
                if ean_index < 0 or atp_index < 0:
                    if ean_index < 0 and atp_index < 0:
                        logging.error("Could not find EAN or ATP columns.")
                    elif ean_index < 0:
                        logging.error("Could not find EAN column.")
                    else:
                        logging.error("Could not find ATP column.")
                    # exit processing
                    break
                else:
                    csv_row.append("ean")
                    csv_row.append("stock")
                    csv_row.append("storehouse_id")
                    csv_row.append("storing_position")
                header_read = True
            writer.writerow(csv_row)

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

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

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

        log_file_name = base_name + '.log'
        out_file_name = 'out_nike_teamsport.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)
                shutil.move(in_file, prc_file)
            except Exception as e:
                logging.error(e)
                shutil.move(in_file, err_file)


def main():
    data_root = r"/ftproot/"
    # data_root = r"/ftproot/"

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

if __name__ == "__main__":
    main()