# 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__))

global gDocRef
global gHdrId

def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('%Y%m%d')
    return ts

def orderHeader(connection,customerReference,line):
    global gDocRef
    global gHdrId
    docRef = None
    ordType = ""
    if (line.strip() == "1"):
        ordType = "GT"
    if (line.strip() == "4"):
        ordType = "SUV"
    date = datetime.datetime.now()
    cursor = connection.cursor(dictionary=True)
    updCursor = connection.cursor()
    try:
# Get id for 'open' document status     
        dataQuery = "SELECT id FROM document_status WHERE lower(document_status_code) = 'open' LIMIT 1"
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        documentStsId = row['id']
    except Exception as e:
        documentStsId = None
    try:
# Test for Existing Reference
        dataQuery = "SELECT id, document_reference FROM order_header WHERE customer_reference='"+customerReference+"' LIMIT 1"
        try:
            updCursor.execute(dataQuery) 
            updRow = updCursor.fetchone()
            if updRow is None:
                dataQuery = "INSERT INTO order_header (id,document_reference,customer_reference,expected_delivery_time,time_slot,document_status_id,order_type,ship_to,date_created,created_by,last_updated_date,last_updated_by)"
                dataQuery += " VALUES(DEFAULT,'COR000000','"+customerReference+"','"+str(date)+"','"+str(date)+"',"+str(documentStsId)+",'"+ordType+"','"+str(line)+"',now(),'system',now(),'system')"
                try:
                    updCursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
                hdrId = updCursor.lastrowid
                docRef = "000000"+str(hdrId)
                docRef = "COR"+docRef[-6:]
                dataQuery = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
                try:
                    updCursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
            else:
                hdrId = updRow[0]
                if (updRow[1] == "COR000000"):
                    docRef = "000000"+str(hdrId)
                    docRef = "COR"+docRef[-6:]
                    dataQuery = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
                    try:
                        updCursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        raise 'Update Error'
                else:
                    docRef = updRow[1]
        except UnboundLocalError:
            raise 'Connection Error'
    except Exception as e:
        print("Order Header Error",e)
    gHdrId = hdrId
    gDocRef = docRef
    
    return "OK  -"
    
def updateOrder(connection):
    cursor = connection.cursor(dictionary=True)
# Read Outstanding Upload Rows
    dataQuery = "SELECT * FROM order_upload WHERE processed=0"
    try:
        cursor.execute(dataQuery)
        rows = cursor.fetchall()
        for row in rows:
            id = row['id']
            updCursor = connection.cursor()
                   
# Test for Existing Part
            partId=""
            conversionFactor = 1
            dataQuery = "SELECT id, vendor_id, conversion_factor FROM part WHERE part_number = '"+row['description']+"' AND wi_code='IFL' LIMIT 1"
            try:
                updCursor.execute(dataQuery) 
                updRow = updCursor.fetchone()
                if updRow is not None:
                    partId = updRow[0]
                    vendorId = updRow[1]
                    conversionFactor = updRow[2]
            
                    if conversionFactor is None:
                        conversionFactor = 1
                    qty = float(row['qty'])
                    qty = float(qty*conversionFactor)
                    
                    ts = getts()
                    customerRef = ts+"_"+str(row['line'])
                    orderHeader(connection,customerRef,row['line'])
                    
                    dataQuery = "SELECT id FROM order_body WHERE order_header_id = "+str(gHdrId)+" AND part_number='"+row['description']+"' AND line_no="+row['line']+" AND fixed_seq='"+row['fixed_seq']+"' LIMIT 1"
            #        print ("bdyQry-",dataQuery)
                    try:
                        updCursor.execute(dataQuery) 
                        updRow = updCursor.fetchone()
                        if updRow is None:
                            dataQuery = "INSERT INTO order_body (id,document_reference,product_type_id,part_number,qty_expected,qty_transacted,difference,line_no,part_id,order_header_id,customer_reference,ran_order,perl_seq,fixed_seq,date_created,created_by,last_updated_date,last_updated_by)"
                            if (partId == ""):
                                dataQuery += " VALUES(DEFAULT,'"+gDocRef+"',null,'"+row['description']+"',"+str(qty)+",0,0-"+str(qty)+","+row['line']+",null,"+str(gHdrId)+",'"+row['order_reference']+"','"+row['kenn']+"','"+row['ext_seq']+"','"+row['fixed_seq']+"',now(),'system',now(),'system')"
                            else:
                                dataQuery += " VALUES(DEFAULT,'"+gDocRef+"',null,'"+row['description']+"',"+str(qty)+",0,0-"+str(qty)+","+row['line']+","+str(partId)+","+str(gHdrId)+",'"+row['order_reference']+"','"+row['kenn']+"','"+row['ext_seq']+"','"+row['fixed_seq']+"',now(),'system',now(),'system')"
                            try:
                                updCursor.execute(dataQuery)
                                connection.commit()
                            except Exception as e:
                                print ('Update Error',e)
                            bdyId = updCursor.lastrowid
                        else:
                            bdyId = updRow[0]
                            if ((qty != "") and (qty is not None)):
                                dataQuery = "UPDATE order_body SET document_reference='"+docRef+"',qty_expected="+str(qty)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(bdyId)+" LIMIT 1"
                                try:
                                    updCursor.execute(dataQuery)
                                    connection.commit()
                                except Exception as e:
                                    print ('Update Error',e)
                    except Exception as e:
                        print("FAIL-Error",dataQuery,str(e))
                        
                dataQuery = "UPDATE order_upload SET processed=1 WHERE id="+str(id)+" LIMIT 1"
                try:
                    updCursor.execute(dataQuery)
                    connection.commit()
                except Exception as e:
                    print ('Update Error1',dataQuery)    
            except Exception as e:
                print ("Update Error2",e,dataQuery)
    except Exception as e:
            print("FAIL-Error",e,dataQuery)
    finally:
        cursor.close()
    
    print("hdrDocRef-")
    
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'])
    updateOrder(connection)
    
        
if __name__ == '__main__':
    main()