import os
import time
import datetime
import mysql.connector
import hashlib
from xlrd import open_workbook

import default_setting

# 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[4].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[5].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[5].strip()+"','"+data[5].strip()+"','"+data[5].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[3].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[3].strip()+"','"+data[3].strip()+"')"
                cursor.execute(dataQuery)
                connection.commit()
                locSubTypeId = cursor.lastrowid
            vendorId = None
            dataQuery = "SELECT id,vendor_reference_code FROM vendor WHERE vendor_name='"+data[2].strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                vendorId = row['id']
                vendorRef = row['vendor_reference_code']
            else:
                vendorRef = data[2].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[7] == "YES"):
                data[7] = 1
            else:
                data[7] = 0
            if (data[8] == "YES"):
                data[8] = 1
            else:
                data[8] = 0
            partNumber = data[0].strip()
            dataQuery = "SELECT id 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(data[6]).strip()+","+str(data[7]).strip()+","+str(data[8]).strip()+","+str(vendorId).strip()+",'"+str(data[2]).strip()+"',0,now(),'upload','"+str(data[1]).strip()+"','"+str(partNumber).strip()+"',"
                if (siteId == None):
                        dataQuery += "null)"
                else:
                    dataQuery += str(siteId)+")"
                cursor.execute(dataQuery)
                connection.commit()
        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 main():
    try:
# 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 = "E:/kukfiles/adhoc/parts upload.xlsx"
        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}")
                
    #            data.append(row_data)  # Append the row data to the main data list
                try:
                    parts(connection,row_data)
                except Exception as e:
                    print("data error:",str(row_data))
                    pass
    except Exception as e:
        print("FAIL-1-",str(e))
if __name__ == '__main__':
    main()