# Create Order interface file from input excel order files.

import os
import time
import datetime
import openpyxl
import mysql.connector
 
import default_setting


# Excel Class
class excel:
    def _init_(self):
        kenn = ""
        line = 0
        pcSeq = ""
        extSeq = ""
        assemblySeq = ""
        order = ""
        psa = ""
        partNumber = ""
        dateCreated = ""
        expectedQty = 1

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
    return ts

def updateOrderUpload(connection,excel):
    date = datetime.datetime.now()
    cursor = connection.cursor()
    
# Test for Existing Reference       
    dataQuery = "SELECT id FROM order_upload WHERE kenn='"+excel.kenn+"' AND description='"+excel.partNumber+"' LIMIT 1"
    try:
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        if row is None:
            dataQuery = "INSERT INTO order_upload (id,kenn,line,pc_seq,ext_seq,fixed_seq,order_reference,supply_area,description,qty,date_created,created_by,last_updated,last_updated_by,processed)"
            dataQuery += " VALUES(DEFAULT,'"+excel.kenn+"',"+str(excel.line)+",'"+excel.pcSeq+"','"+excel.extSeq+"','"+excel.assemblySeq+"','"+excel.order+"','"+excel.psa+"','"+excel.partNumber+"',"+str(excel.expectedQty)+","+"now(),'system',now(),'system',0)"
            try:
                cursor.execute(dataQuery)
                connection.commit()
            except UnboundLocalError:
                print ('Update Error',dataQuery)
            ordId = cursor.lastrowid
        else:
            ordId = row[0]
    except UnboundLocalError:
        print ('Connection Error')
            
    finally:
        cursor.close()
    
#    print("orderUpload")
    
def main():
# Default Connection / System Settings
    defaults = default_setting.defaultSettings()
# mySql Connector
    connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])

    file_path_xls = 'order.xlsx'
    if os.path.isfile(file_path_xls):
        wb =openpyxl.load_workbook(file_path_xls) 

        ts = getts()
        print('Time:'+str(ts))

        values = []
# Loop through the Sheets
        sheet = wb.active
#            print ('Sheet:',sheet.name)
        c = 0
        i = 0
# Loop through the Sheet Rows
        for row in sheet.iter_rows():
            value  = row[0].value
            try : value = str(int(value))
            except : pass
            if (value.lower() == "kenn"):
                continue
# test for header row
            excel.kenn  = row[0].value
            try : excel.kenn = str(int(excel.kenn))                 
            except : pass
            excel.line  = row[1].value
            try : excel.line = str(int(exel.line))
            except : pass
            excel.pcSeq  = row[2].value
            try : excel.pcSeq = str(int(exel.pcSeq))
            except : pass
            excel.extSeq  = row[3].value
            try : excel.extSeq = str(int(exel.extSeq))
            except : pass
            excel.assemblySeq  = row[4].value
            try : excel.assemblySeq = str(int(exel.assemblySeq))
            except : pass
            excel.order  = row[5].value
            try : excel.order = str(int(exel.order))
            except : pass
            excel.psa  = row[6].value
            try : excel.psa = str(int(exel.psa))
            except : pass
            excel.partNumber  = row[7].value
            excel.partNumber = excel.partNumber.replace("'"," ")
            try : 
                excel.partNumber = str(int(exel.partNumber))
            except : pass
            excel.dateCreated  = row[8].value
            try : excel.dateCreated = str(int(exel.dateCreated))
            except : pass
            excel.expectedQty = 1
            updateOrderUpload(connection,excel)
    else:
        print ("File not Found - ",file_path_xls)
    return
    
if __name__ == '__main__':
    main()