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 # Function to generate a SHA-256 hash limited to 6 characters def generate_short_hash(input_string): # Create a new sha256 hash object sha256_hash = hashlib.sha256() # Update the hash object with the bytes of the input string sha256_hash.update(input_string.encode()) # Get the hexadecimal representation of the full digest full_hash = sha256_hash.hexdigest() # Return the first 6 characters of the hash return full_hash[:6].upper() def location(connection,data): global csv_path global csv_header hashCode = generate_short_hash(data[0].strip()) try: cursor = connection.cursor(dictionary=True) maxWeight = 0 multiPart = 1 invStatusId = 1 if (len(data)>3): maxWeight = data[3] if (len(data)>4): if (data[4].lower() == "no"): multiPart = 0 else: multipart = 1 if (len(data)>5): dataQuery = "SELECT id FROM inventory_status WHERE inventory_status_code='"+data[5].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): invStatusId = row['id'] try: locSubTypeId = None 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 except Exception as e: print ("Insert Error:",str(e),data[2]) try: locTypeId = None dataQuery = "SELECT id FROM location_type WHERE location_type_code='"+data[1].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): locTypeId = row['id'] else: dataQuery = "INSERT INTO location_type (id,version,active,default_inventory_status_id,loose_parts,date_created,created_by,last_updated,last_updated_by,location_type_code,location_type_description)" dataQuery += " VALUES(DEFAULT,0,1,1,0,now(),'update',now(),'update','"+data[1].strip()+"','"+data[1].strip()+"')" cursor.execute(dataQuery) connection.commit() locTypeId = cursor.lastrowid except Exception as e: print ("Insert Error:",str(e),dataQuery,data[1]) try: dataQuery = "SELECT id FROM location WHERE location_code='"+data[0].strip()+"' LIMIT 1" cursor.execute(dataQuery) row = cursor.fetchone() if (row): locId = row['id'] dataQuery = "UPDATE location SET last_updated=now(),last_updated_by='update',location_sub_type_id="+str(locSubTypeId)+", location_type_id="+str(locTypeId) if (len(data)>3): dataQuery +=", max_weight="+str(maxWeight) if (len(data)>4): dataQuery +=", multi_part_location="+str(multiPart) if (len(data)>5): dataQuery +=", inventory_status_id="+str(invStatusId) dataQuery +=" WHERE id="+str(locId) dataQuery +=" LIMIT 1" cursor.execute(dataQuery) connection.commit() else: dataQuery = "INSERT INTO location (id,version,active,location_closed,multi_vendor_location,filling_status_id,date_created,created_by,last_updated,last_updated_by,location_code,location_code_description,location_hash,location_sub_type_id,location_type_id,max_weight,multi_part_location,inventory_status_id)" dataQuery += " VALUES(DEFAULT,0,1,0,1,1.now(),'update',now(),'update','"+data[0].strip()+"','"+data[0].strip()+"','"+hashCode+"',"+str(locSubTypeId)+","+str(locTypeId)+","+str(maxWeight)+","+str(multiPart)+","+str(invStatusId)+")" cursor.execute(dataQuery) connection.commit() locId = cursor.lastrowid except Exception as e: print ('Update Error',str(e),dataQuery) except Exception as e: print("FAIL-",str(e),dataQuery) 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']) return connection def main(): try: connect = connection() if (len(sys.argv) > 5): file_path = sys.argv[5] else: 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/location 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: 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}") try: location(connect,row_data) except Exception as e: print("data error:",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-1-",str(e)) return if __name__ == '__main__': main()