# 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,excel):
    date = datetime.datetime.now()
    cursor = connection.cursor()
    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   
                dataQuery = "SELECT id FROM part WHERE part_number='"+row['part_number']+"' LIMIT 1"
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                if row is None:
                    dataQuery = "INSERT INTO part (id,version,part_number,part_description,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)"
                    dataQuery += " VALUES(DEFAULT,0,'"+row['partNumber']+"','"+row['part_number']+"','1990-01-01 00:00:01','2035-12-31 23:59:59',1,null,null,null,now(),'system',now(),'system')"
                    try:
                        cursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        print ('Update Error',dataQuery)
                    partId = cursor.lastrowid
                else:
                    partId = row[0]
                    dataQuery = "UPDATE part SET conversion_factor=1 WHERE id="+str(partId)+" AND conversion_factor IS null LIMIT 1";
                    try:
                        cursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        print ('Update Error',dataQuery)
                print('Part',dataQuery)    
    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))
    updateOrder(connection)
    
        
if __name__ == '__main__':
    main()