#!/usr/bin/env python

__author__ = 'holger.knust'

import codecs
import csv
import logging
import os
import shutil
import time
import xlrd
from xml.sax import saxutils
from xml.sax import parse

class ExcelHandler(saxutils.handler.ContentHandler):
    def __init__(self):
        super().__init__()
        self.chars = []
        self.cells = []
        self.rows = []
        self.tables = []
        self.worksheets = {}
        self.current_sheet = ""

    def characters(self, content):
        self.chars.append(content)

    def startElement(self, name, atts):
        if name == "Worksheet":
            self.current_sheet = atts.getValue("ss:Name")
            print("Reading worksheet " + self.current_sheet)
            self.tables = []
        if name == "Cell":
            self.chars = []
        elif name == "Row":
            self.cells = []
        elif name == "Table":
            self.rows = []

    def endElement(self, name):
        if name == "Cell":
            self.cells.append(''.join(self.chars))
        elif name == "Row":
            self.rows.append(self.cells)
        elif name == "Table":
            self.tables.append(self.rows)
        if name == "Worksheet":
            self.worksheets[self.current_sheet] = self.tables

def read_warehouse_lookup(ref_path):
    ref_dict = {}

    header_row = []
    header_read = False
    full_ref_path = os.path.join(ref_path, "Lager_Referenz.csv")
    with codecs.open(full_ref_path, "rb", encoding='utf-8') as f:

        reader = csv.reader(f, delimiter=';')
        wh_col_no = -1
        for row in reader:
            if header_read:
                warehouse_no = row.pop(wh_col_no)
                ref_dict[warehouse_no] = row
            else:
                header_row = row
                wh_col_no = header_row.index("Lager_Referenz")
                header_row.pop(wh_col_no)
                header_read = True

    return ref_dict, header_row

def read_ean_lookup(ref_path):
    ref_dict = {}

    header_row = []
    header_read = False
    full_ref_path = os.path.join(ref_path, "Referenz_Matching_Vendor.csv")
    with codecs.open(full_ref_path, "rb", encoding='utf-8') as f:

        reader = csv.reader(f, delimiter=';')
        ean_col_no = -1
        for row in reader:
            if header_read:
                ean = row.pop(ean_col_no)
                ref_dict[ean] = row
            else:
                header_row = row
                ean_col_no = header_row.index("EAN")
                header_row.pop(ean_col_no)
                id_col_no = header_row.index("ItemID")
                header_row.pop(id_col_no)
                price_col_no = header_row.index("PriceID")
                header_row.pop(price_col_no)
                set_col_no = header_row.index("AttributeValueSetID")
                header_row.pop(set_col_no)
                header_read = True

    return ref_dict, header_row

def is_xml_file(in_file):
    with open(in_file) as f:
        identifier = f.read(5)

    return identifier == '<?xml'

def read_excel_xml(in_file):
    xml_file = open(in_file)
    excelHandler = ExcelHandler()
    parse(xml_file, excelHandler)

    if "Line Items" in excelHandler.worksheets:
        worksheet = excelHandler.worksheets["Line Items"]

        return worksheet[0][3:]
    return None

def read_excel_open_xml(in_file):
    book = xlrd.open_workbook(in_file)

    # Read values of the first sheet
    worksheet = book.sheet_by_index(0)

    if worksheet.nrows < 3:
        raise Exception("Sheet should have more than 3 rows. Aboorting...")

    # Real values start at line 3
    row_start = 3

    rows = []
    for row_index in range(row_start, worksheet.nrows):
        row = []
        for column_index in range(worksheet.ncols):
            row.append(str(worksheet.cell(row_index, column_index).value))
        rows.append(row)

    return rows

def convert_ean(in_file, out_file, ref_dict, header_row, wh_dict, wh_hdr):
    header_read = False
    no_index = -1
    wh_index = -1
    order_no_index = -1
    title_index = -1

    if is_xml_file(in_file):
        # Older 2003 Excel XML format can be parsed with the mini parser.
        rows = read_excel_xml(in_file)
    else:
        # Use library to read Open XML format.
        rows = read_excel_open_xml(in_file)

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

        for row in rows:
            if header_read:
                if title_index > -1 and order_no_index > -1:
                    row[title_index] = row[title_index] + " - PO-Nummer " + row[order_no_index]

                ean = row[no_index]
                wh = row[wh_index]

                del row[order_date_index]

                start_pos = len(row) + 1

                if len(ean) == 14:
                    ean = ean[1:]
                if len(ean) == 12:
                    ean = '0' + ean

                if ean in ref_dict:
                    row2 = list(ref_dict[ean])  # ref_dict[ean].copy()
                    if len(row2) > 1:
                        item_id = row2.pop(0)
                        price_id = row2.pop(0)
                        set_id = row2.pop(0)
                        row.insert(0, "{0}-{1}-{2}".format(item_id, price_id, set_id))
                        for col in row2:
                            row.insert(start_pos, col)
                            start_pos += 1
                else:
                    logging.warning("Could not find EAN {0}".format(ean))
                    row2 = list(ref_dict['1000000000580'])
                    if len(row2) > 1:
                        item_id = row2.pop(0)
                        price_id = row2.pop(0)
                        set_id = row2.pop(0)
                        row.insert(0, "{0}-{1}-{2}".format(item_id, price_id, set_id))
                        for col in row2:
                            row.insert(start_pos, col)
                            start_pos += 1

                if wh in wh_dict:
                    row2 = list(wh_dict[wh])  # wh_dict[wh].copy()
                    if len(row2) > 1:
                        start_pos = len(row) + 1
                        for col in row2:
                            row.insert(start_pos, col)
                            start_pos += 1
                    else:
                        row.insert(start_pos, "ERROR")
                        logging.warning("Could not find warehouse {0}".format(wh))
            else:
                header_read = True

                no_index = row.index('Externe ID')

                # Rename for output
                row[no_index] = "EAN"

                wh_index = row.index('Versandzentrum')

                if "Bestell-/PO-Nummer" in row:
                    order_no_index = row.index("Bestell-/PO-Nummer")

                if "Titel" in row:
                    title_index = row.index("Titel")

                if "Bestelldatum" in row:
                    order_date_index = row.index("Bestelldatum")
                    del row[order_date_index]

                row.insert(0, 'ItemId')

                start_pos = len(row) + 1

                for col in header_row:
                    row.insert(start_pos, col)
                    start_pos += 1

                for col in wh_hdr:
                    row.insert(start_pos, col)
                    start_pos += 1

            writer.writerow(row)

def process_ean(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")
    path_ret = os.path.join(data_root, "Rueckmeldung")

    files = os.listdir(path_in)
    num_files = len(files)

    print("Found {0} files...".format(num_files))

    if num_files == 0:
        return

    ref_dict, header_row = read_ean_lookup(path_ref)
    wh_dict, wh_hdr = read_warehouse_lookup(path_ref)

    for f in files:
        log_file_name = time.strftime("%Y%m%d-%H%M%S") + '.log'
        log_file = os.path.join(path_log, log_file_name)

        in_file = os.path.join(path_in, f)
        out_file_name = 'PO_' + os.path.splitext(f)[0] + '.csv'
        out_file = os.path.join(path_out, out_file_name)
        proc_file = os.path.join(path_proc, f)
        ret_file = os.path.join(path_ret, 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("Aggregating quantities in  {0}...".format(in_file))

        if os.path.isfile(in_file):
            try:
                print("Converting {0} to {1}...".format(in_file, out_file))
                convert_ean(in_file, out_file, ref_dict, header_row, wh_dict, wh_hdr)
                shutil.copy(in_file, ret_file)
                shutil.move(in_file, proc_file)
            except Exception as e:
                logging.error(e)
                shutil.move(in_file, err_file)

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

    ean_data_root = os.path.join(data_root, "Matching_Vendor")

    process_ean(ean_data_root)

    print("Done.")


if __name__ == "__main__":
    main()