import os import sys import time import datetime import mysql.connector import hashlib from xlrd import open_workbook import tkinter as tk from tkinter import filedialog import default_setting # Create a Tkinter root window (it won't be shown) root = tk.Tk() root.withdraw() # Hide the root window # Get Current Path dir_path = os.path.dirname(os.path.realpath(__file__)) def formatDate(date): if ((date == None) or (len(date) < 14)): date = "0001-01-01 00:00:01" else: dd = date[0:2] mm = date[3:5] yy = date[6:8] tm = date[9:14] date = '20'+yy+"-"+mm+"-"+dd+" "+tm+":00" return date def parts(connection,data): try: cursor = connection.cursor(dictionary=True) try: dataQuery = "SELECT id FROM site WHERE site_name='"+data[5].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): siteId = row['id'] else: siteId = None dataQuery = "SELECT id FROM location_type WHERE location_type_code='"+data[3].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): locTypeId = row['id'] else: dataQuery = "INSERT INTO location_type (id,version,active,date_created,created_by,last_updated,last_updated_by,is_pickable,default_inventory_status_id,loose_parts,location_area,location_type_code,location_type_description,task_group,display_sites)" dataQuery += " VALUES(DEFAULT,0,1,now(),'update',now(),'update',1,1,0,'storage','"+data[3].strip()+"','"+data[3].strip()+"','"+data[3].strip()+"'," if (siteId == None): dataQuery += "null)" else: dataQuery += str(siteId)+")" cursor.execute(dataQuery) connection.commit() locTypeId = cursor.lastrowid dataQuery = "SELECT id FROM location_sub_type WHERE location_sub_type_code='"+data[2].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): locSubTypeId = row['id'] else: dataQuery = "INSERT INTO location_sub_type (id,version,active,date_created,created_by,last_updated,last_updated_by,location_sub_type_code,location_sub_type_description)" dataQuery += " VALUES(DEFAULT,0,1,now(),'update',now(),'update','"+data[2].strip()+"','"+data[2].strip()+"')" cursor.execute(dataQuery) connection.commit() locSubTypeId = cursor.lastrowid vendorId = None dataQuery = "SELECT id,vendor_reference_code FROM vendor WHERE vendor_name='"+data[1].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): vendorId = row['id'] vendorRef = row['vendor_reference_code'] else: vendorRef = data[1].split("-")[0] dataQuery = "SELECT id,vendor_reference_code FROM vendor WHERE vendor_reference_code='"+vendorRef+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): vendorId = row['id'] vendorRef = row['vendor_reference_code'] if (vendorId == None): vendorId = 1 vendorRef = "P99999" if (data[6] == "YES"): as400 = 1 else: as400 = 0 partNumber = data[0].strip() weight = data[7].strip() if (data[4] == "YES"): fullBox = 1 else: fullBox = 0 dataQuery = "SELECT * FROM part WHERE part_number='"+partNumber+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): partId = row['id'] else: partId = None if (partId == None): # print("Part Added:",partNumber) dataQuery = "INSERT INTO part (id,version,date_created,created_by,effective_from,effective_to,fixed_location_type_id,fixed_sub_type_id,weight,full_box_pick,isAS400,vendor_id,vendor_name,imaginary_part,last_updated,last_updated_by,part_description,part_number,site_id)" dataQuery +=" VALUES(DEFAULT,0,now(),'upload',now(),'2035-12-31 23:59:59'," if (locTypeId == None): dataQuery += "null,null," else: dataQuery +=str(locTypeId)+","+str(locSubTypeId)+"," dataQuery +=str(weight)+","+str(fullBox)+","+str(as400)+","+str(vendorId).strip()+",'"+str(vendorRef).strip()+"',0,now(),'upload','"+str(partNumber).strip()+"','"+str(partNumber).strip()+"'," if (siteId == None): dataQuery += "null)" else: dataQuery += str(siteId)+")" else: # print("Part Updated:",partNumber) dataQuery = "UPDATE part SET last_updated=now(),last_updated_by='upload',fixed_location_type_id="+str(locTypeId)+",fixed_sub_type_id="+str(locSubTypeId)+",weight="+str(weight)+",full_box_pick="+str(fullBox)+",isAS400="+str(as400) if (siteId == None): dataQuery += ",site_id=null" else: dataQuery += ",site_id="+str(siteId) dataQuery +=" WHERE id="+str(partId) dataQuery +=" LIMIT 1" cursor.execute(dataQuery) connection.commit() except Exception as e: # print ('Update Error',str(e),dataQuery) pass except Exception as e: # print("FAIL-",str(e),dataQuery) pass finally: cursor.close() # print("TEST4") return # timestamp value def getts(): ts = time.time() ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S') return ts def connection(): dbhost = "" if (len(sys.argv) > 1): dbhost = sys.argv[1] if (len(sys.argv) > 2): dbuser = sys.argv[2] if (len(sys.argv) > 3): dbpwd = sys.argv[3] if (len(sys.argv) > 4): dbase = sys.argv[4] # mySql Connector if (len(sys.argv) > 1): connection = mysql.connector.connect(user=dbuser, password=dbpwd,host=dbhost,database=dbase) else: # Default Connection / System Settings defaults = default_setting.defaultSettings() connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase']) # print("connection",dbhost,dbuser,dbase) return connection def main(): try: connect = connection() if (len(sys.argv) > 5): file_path = sys.argv[5] else: # Prompt the user to select a file file_path = filedialog.askopenfilename(title="Select a file") # Check if a file was selected if file_path: print(f"Selected file: {file_path}") else: print("No file selected.") file_path = "F:/kukfiles/adhoc/parts upload.xlsx" file_path_xls = file_path if os.path.isfile(file_path_xls): wb = open_workbook(file_path_xls) # Loop through the Sheets for s in wb.sheets(): # print('Sheet:', s.name) data = [] # Initialize data for each sheet # Loop through the Sheet Rows for row in range(s.nrows): # print("Row:", row) row_data = [] for col in range(s.ncols): # Loop through columns try: if (s.cell(row,0).value == "" or s.cell(row,0).value == None): continue value = s.cell(row, col).value if isinstance(value, (int, float)): # Check if value is a number row_data.append(str(int(value))) else: row_data.append(str(value)) # Convert to string directly except ValueError: row_data.append("") # Handle conversion errors except Exception as e: print(f"Error processing cell ({row}, {col}): {e}") # data.append(row_data) # Append the row data to the main data list try: parts(connect,row_data) except Exception as e: print("data error:",str(e),str(row_data)) pass else: print ("FAIL-upload File "+file_path_xls+" not found") return print ("OK -Upload Complete "+file_path) except Exception as e: print("FAIL-",str(e)) if __name__ == '__main__': main()