# 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):
        partNumber = ""
        code = ""
        system = ""
        warehouse = ""
        include = ""

# 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 updatePart(connection,excel):
    date = datetime.datetime.now()
    cursor = connection.cursor()
    try:
        try:
#            print(excel.partNumber,excel.code,excel.system,excel.warehouse,excel.include)
            dataQuery = "INSERT INTO part_upload (id,part_number,code,sys_code,warehouse,include,date_created,created_by,last_updated,last_updated_by)"
            if (excel.include is None):
                dataQuery += " VALUES(DEFAULT,'"+excel.partNumber+"','"+excel.code+"','"+excel.system+"','"+excel.warehouse+"',null,now(),'system',now(),'system')"
            else:
                dataQuery += " VALUES(DEFAULT,'"+excel.partNumber+"','"+excel.code+"','"+excel.system+"','"+excel.warehouse+"','"+excel.include+"',now(),'system',now(),'system')"
            try:
                cursor.execute(dataQuery)
                connection.commit()
            except UnboundLocalError:
                print('Update Error',dataQuery)
        except UnboundLocalError:
            print ('Connection Error')         
    finally:
        cursor.close()
    
    print("OK  -partUpload")
    
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 = 'c:/upload/part.xlsx'
    if os.path.isfile(file_path_xls):
        wb =openpyxl.load_workbook(file_path_xls) 

        ts = getts()
        print('Time:'+str(ts))
        
# Clear table
        try:
            cursor = connection.cursor()
            dataQuery = "TRUNCATE part_upload"
            cursor.execute(dataQuery)
            connection.commit()
        except Exception as err:
            dataQuery = "DELETE FROM part_update WHERE id>0"
            cursor.execute(dataQuery)
            connection.commit()
        
        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().strip() == "commodity"):
                continue
# test for header row
            excel.partNumber  = row[0].value
            try : excel.partNumber = str(int(excel.partNumber))
            except : pass
            excel.code  = row[1].value
            try : excel.code = str(int(excel.code))                 
            except : pass
            excel.system  = row[2].value
            try : excel.system = str(int(exel.system))
            except : pass
            excel.warehouse  = row[3].value
            try : excel.warehouse= str(int(exel.warehouse))
            except : pass
            excel.include  = row[4].value
            try : excel.include = str(int(exel.include))
            except : pass
            updatePart(connection,excel)
    else:
        print ("File not Found - ",file_path_xls)
        
if __name__ == '__main__':
    main()