# Create Order interface file from input excel order files.

import os
import time
import datetime
import openpyxl
import mysql.connector
 
import default_setting

# 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):
    date = datetime.datetime.now()
    cursor = connection.cursor(dictionary=True)
    try:
# Read Outstanding Upload Rows
        dataQuery = "SELECT * FROM part_upload WHERE LOWER(include)='yes'"
        try:
            cursor.execute(dataQuery)
            rows = cursor.fetchall()
            for row in rows:
                id = row['id']
                updCursor = connection.cursor()
# Get id for part   
                updQuery = "SELECT id FROM part WHERE part_number='"+row['part_number']+"' LIMIT 1"
                try:
                    updCursor.execute(updQuery) 
                    updRow = updCursor.fetchone()
                except Exception as Err:
                    print ('Update Error',updQuery,Err)   
                if updRow is None:
                    updQuery = "INSERT INTO part (id,version,part_number,part_description,vendor_part_code,effective_from,effective_to,conversion_factor,fixed_location_id,fixed_location_type_id,fixed_sub_type_id,date_created,created_by,last_updated,last_updated_by)"
                    updQuery += " VALUES(DEFAULT,0,'"+row['part_number']+"','"+row['part_number']+"','"+row['code']+"','1990-01-01 00:00:01','2035-12-31 23:59:59',1,null,null,null,now(),'system',now(),'system')"
                    try:
                        updCursor.execute(updQuery)
                        connection.commit()
                    except UnboundLocalError:
                        print ('Update Error',updQuery)
                    partId = updCursor.lastrowid
                else:
                    partId = updRow[0]
                    updQuery = "UPDATE part SET conversion_factor=1,vendor_part_code='"+row['code']+"' WHERE id="+str(partId)+" AND (conversion_factor IS null OR vendor_part_code IS null) LIMIT 1";
                    try:
                        updCursor.execute(updQuery)
                        connection.commit()
                    except UnboundLocalError:
                        print ('Update Error',updQuery)
        except Exception as Err:
            print ('Select Error',dataQuery,Err)   
    finally:
        cursor.close()
        
    return "OK  -partUpdate"
    
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'])
    ts = getts()
    print('Time:'+str(ts))
    updatePart(connection)
    
        
if __name__ == '__main__':
    main()