import os
import time
import datetime
import mysql.connector 

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 inventory_master(connection,data):
    try:
        cursor = connection.cursor(dictionary=True)
        try:
            dataQuery = "SELECT id FROM location WHERE location_code='"+str(data[9]).strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                locId = row['id']
            else:
                locId = None
            partNumber = data[7].strip()
            serialReference = data[20].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
            dataQuery = "SELECT id FROM inventory_master WHERE part_number='"+partNumber+"' AND serial_reference='"+serialReference+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                invId = row['id']
            else:
                invId = None
            if (invId == None):
                dataQuery = "INSERT INTO inventory_master (id,version,allocated_qty,hold_qty,current_inventory_status_id,current_location_id,location_id,location_code,date_created,created_by,expiry_date,inventory_qty,available_qty,last_updated,last_updated_by,parent_tag_id,part_id,part_number,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,inventory_status_id,product_type_id)" 
                storeDate = formatDate(data[21])
                dataQuery += " VALUES(DEFAULT,0,0,0,null,"
                if (locId != None):
                    dataQuery +=str(locId)+","+str(locId)+",'"
                else:
                    dataQuery +="null,null,'"
                dataQuery += str(data[9]).strip()+"',now(),'upload',null,"+str(data[11]).strip()+","+str(data[11]).strip()+",now(),'upload',null,"+str(partId)+",'"+partNumber+"',null,0,0,0,'"+serialReference+"',null,1,'"+str(storeDate)+"',1,1,null)"
                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_map = "E:/kukfiles/adhoc/finvntp.txt"
        fileInput = open(file_path_map,"r")
# for each line split out fields.
        i = 0
        for lines in fileInput:
            lines = lines.replace('\n','')
            lines = lines.replace('"','')
            data = lines.split("|")
            if ((data) and (len(data)>0) and (data[0] != '')):
                inventory_master(connection,data)
        fileInput.close()
    except Exception as e:
        print("FAIL-",str(e))
if __name__ == '__main__':
    main()