# 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 updateOrder(connection):
    docRef = None
    date = datetime.datetime.now()
    cursor = connection.cursor(dictionary=True)
    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']
# 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 Reference
                dataQuery = "SELECT id, document_reference FROM order_header WHERE customer_reference="+row['order_reference']+" 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,date_created,created_by,last_updated_date,last_updated_by)"
                        dataQuery += " VALUES(DEFAULT,'COR000000','"+row['order_reference']+"','"+str(date)+"','"+str(date)+"',"+str(documentStsId)+",'STD',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'
                    
        # 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)
                        
                        dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+row['description']+"' AND line_no="+row['line']+" 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,date_created,created_by,last_updated_date,last_updated_by)"
                                if (partId == ""):
                                    dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+row['description']+"',"+str(qty)+",0,0-"+str(qty)+","+row['line']+",null,"+str(hdrId)+","+row['order_reference']+","+row['kenn']+",now(),'system',now(),'system')"
                                else:
                                    dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+row['description']+"',"+str(qty)+",0,0-"+str(qty)+","+row['line']+","+str(partId)+","+str(hdrId)+","+row['order_reference']+","+row['kenn']+",now(),'system',now(),'system')"
                                try:
                                    updCursor.execute(dataQuery)
                                    connection.commit()
                                except UnboundLocalError:
                                    raise 'Update Error'
                                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 UnboundLocalError:
                                        raise 'Update Error'
                        except Exception as e:
                            print("FAIL-Error",dataQuery)
                            
                    dataQuery = "UPDATE order_upload SET processed=1 WHERE id="+str(id)+" LIMIT 1"
                    try:
                        updCursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        print ('Update Error',dataQuery)    
                except:
                    print ("Update Error",dataQuery)
        except Exception as e:
            print("FAIL-Error",e,dataQuery)
    except Exception as e:
            print("FAIL-Error",e,dataQuery)
    finally:
        cursor.close()
    
    print("hdrDocRef-",docRef)
    
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()