# Create Order interface file from input excel order files.

import os
import time
import datetime
import mysql.connector
 
import default_setting


# Excel Class
class lineText:
    def _init_(self):
        dateCreated = ""
        kenn = ""
        line = 0
        pcSeq = ""
        extSeq = ""
        assemblySeq = ""
        order = ""
        psa = ""
        partNumber = ""
        expectedQty = 1

# 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 updateOrderUpload(connection,lineText):
    date = datetime.datetime.now()
    cursor = connection.cursor()
    
# Test for Existing Reference       
    dataQuery = "SELECT id FROM order_upload WHERE kenn='"+lineText.kenn+"' AND description='"+lineText.partNumber+"' LIMIT 1"
    try:
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        if row is None:
            dataQuery = "INSERT INTO order_upload (id,kenn,line,pc_seq,ext_seq,fixed_seq,order_reference,supply_area,description,qty,date_created,created_by,last_updated,last_updated_by,processed,status_id,current_stage)"
            dataQuery += " VALUES(DEFAULT,'"+lineText.kenn+"',"+str(lineText.line)+",'"+lineText.pcSeq+"','"+lineText.extSeq+"','"+lineText.assemblySeq+"','"+lineText.order+"','"+lineText.psa+"','"+lineText.partNumber+"',"+str(lineText.expectedQty)+","+"now(),'system',now(),'system',0,1,'ORDER')"
            try:
                cursor.execute(dataQuery)
                connection.commit()
            except UnboundLocalError:
                print ('Update Error',dataQuery)
            ordId = cursor.lastrowid
        else:
            ordId = row[0]
    except UnboundLocalError:
        print ('Connection Error')
            
    finally:
        cursor.close()
    
#    print("orderUpload")
    
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'])

    file_path_txt = 'order.txt'
    if os.path.isfile(file_path_txt):
        iDocTran = open(file_path_txt,"r")

        ts = getts()
        print('Time:'+str(ts))

        values = []
# Loop through the Sheets
#            print ('Sheet:',sheet.name)
        c = 0
        i = 0
# Loop through the Sheet Rows
        for line in iDocTran:
            line = line.replace('\n','')
            line = line.replace(',',' ')
#		    print("LINE:"+line)
            offset = 0
            value  = line[0:7]
            if (value.lower() == "created"):
                continue
# test for header row
            lineText.kenn  = line[11:18]
            lineText.line  = line[19:21]
            lineText.pcSeq  = line[22:34]            
            lineText.extSeq  = line[35:41]           
            lineText.assemblySeq  = line[42:45]            
            lineText.order  = line[46:54]            
            lineText.psa  = line[55:65]           
            lineText.partNumber  = line[66:100]
            lineText.partNumber = lineText.partNumber.replace("'"," ")           
            lineText.dateCreated  = line[0:10]            
            lineText.expectedQty = 1
            if i == 0:
                print(lineText.kenn,lineText.line,lineText.pcSeq,lineText.extSeq,lineText.assemblySeq,lineText.order,lineText.psa,lineText.partNumber)
            i=i+1
            updateOrderUpload(connection,lineText)
    else:
        print ("File not Found - ",file_path_xls)
    return
    
if __name__ == '__main__':
    main()