# Create Order interface file from input excel order files.

import os
import time
import datetime
import openpyxl
import mysql.connector

# 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 load_dotenv_simple(path):
    env = {}
    with open(path, "r", encoding="utf-8") as f:
        for raw in f:
            line = raw.strip()
            if not line or line.startswith("#"):
                continue
            if "=" not in line:
                continue
            key, val = line.split("=", 1)
            key = key.strip()
            val = val.strip()
            # remove optional surrounding quotes
            if (val.startswith('"') and val.endswith('"')) or (val.startswith("'") and val.endswith("'")):
                val = val[1:-1]
            env[key] = val
#            print("env=",key,val)
    return env

def strip_quotes(v):
    if not v:
        return v
    v = v.strip()
    if (v.startswith('"') and v.endswith('"')) or (v.startswith("'") and v.endswith("'")):
        return v[1:-1]
    return v

def orderHeader(connection,customerReference,line,kitBag):
    global gDocRef
    global gHdrId
    docRef = None
    ordType = ""
    if (line.strip() == "1"):
        ordType = "GT"
    if (line.strip() == "4"):
        ordType = "SUV"
    if (line.strip() == "1" and (kitBag.strip() == "ZKIT" or kitBag.strip() == "X")):
        ordType = "GTKB"
    if (line.strip() == "4" and (kitBag.strip() == "ZKIT" or kitBag.strip() == "X")):
        ordType = "SUVKB"
    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,ordId=None):
    cursor = connection.cursor(dictionary=True)
# Read Outstanding Upload Rows
    if (ordId is None):
        dataQuery = "SELECT * FROM order_upload WHERE processed=0"
    else:
        dataQuery = "SELECT * FROM order_upload WHERE id = "+str(ordId)+" AND processed = 0 LIMIT 1"
    try:
        cursor.execute(dataQuery)
        rows = cursor.fetchall()
        for row in rows:
#            print('row:',str(row))
            id = row['id']
            
#            print("Order Process:",row['description']+" "+row['kenn'])
            updCursor = connection.cursor()
                   
# Test for Existing Part
            partId=""
            kitBag = ""
            conversionFactor = 1
            if row['kitbag'] is None or (isinstance(row['kitbag'], str) and row['kitbag'].strip() == ""):
                dataQuery = "SELECT id, vendor_id, conversion_factor FROM part WHERE part_number = '"+row['description']+"' AND wi_code='IFL' LIMIT 1"
                kitBag = ''
            else:
                dataQuery = "SELECT id, vendor_id, conversion_factor FROM part WHERE part_number = '"+row['description']+"' LIMIT 1"
                kitBag = 'ZKIT'
            try:
                cursor.execute(dataQuery) 
                updRow = cursor.fetchone()
                if updRow is not None:
                    partId = updRow['id']
                    vendorId = updRow['vendor_id']
                    conversionFactor = updRow['conversion_factor']
            
                    if conversionFactor is None:
                        conversionFactor = 1
                    qty = float(row['qty'])
                    qty = float(qty*conversionFactor)
                    
                    ts = getts()
                    
                    customerRef = ts+"_"+str(row['line']+kitBag)
                    
                    print("customerRef:",customerRef)
                    
                    orderHeader(connection,customerRef,row['line'],kitBag)
                    
# delete existing ZKIT order line
                    if (kitBag == 'ZKIT'):
                        dataQuery = "DELETE FROM order_body WHERE order_body.id > 0 AND part_number='"+row['description']+"' AND ran_order='"+row['kenn']+"' AND fixed_seq='"+row['fixed_seq']+"' LIMIT 1"
                        try:
                            updCursor.execute(dataQuery)
                            connection.commit()
                        except Exception as e:
                            print ('Delete Error',e)
# Look for existing order_body                            
                    dataQuery = "SELECT id FROM order_body WHERE order_header_id = "+str(gHdrId)+" AND part_number='"+row['description']+"' AND fixed_seq='"+row['fixed_seq']+"' LIMIT 1"
            #        print ("bdyQry-",dataQuery)
                    try:
                        cursor.execute(dataQuery)
                        updRow = cursor.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,sequence,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['fixed_seq']+","+row['pick_seq']+",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['fixed_seq']+","+row['pick_seq']+","+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
                            print("order line added:",gDocRef,row['fixed_seq'],bdyId)
                        else:
                            bdyId = updRow['id']
                            if ((qty != "") and (qty is not None)):
                                dataQuery = "UPDATE order_body SET document_reference='"+gDocRef+"',qty_expected="+str(qty)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(bdyId)+" AND qty_transacted=0 LIMIT 1"
                                try:
                                    updCursor.execute(dataQuery)
                                    connection.commit()
                                except Exception as e:
                                    print ('Update Error',e)
                            print("order line updated:",gDocRef,row['fixed_seq'])
                    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
    env = load_dotenv_simple('f:/webroot/kukupload/.env')
    db_host = env.get("DB_HOST") or env.get("db_host")
    db_name = env.get("DB_DATABASE") or env.get("db_database")
    db_user = env.get("DB_USERNAME") or env.get("db_username")
    db_pass = env.get("DB_PASSWORD") or env.get("db_password")
    db_port = env.get("DB_PORT")
    db_host = strip_quotes(db_host)
    db_name = strip_quotes(db_name)
    db_user = strip_quotes(db_user)
    db_pass = strip_quotes(db_pass)

    defaults = {}
    defaults['dbhost']=db_host
    defaults['dbuser']=db_user
    defaults['dbpwd']=db_pass
    defaults['dbase']=db_name

    cnx = mysql.connector.connect(
        host=db_host,
        database=db_name,
        user=db_user,
        password=db_pass
#        port=db_port
    )
# mySql Connector
    updateOrder(cnx)
    
        
if __name__ == '__main__':
    main()