# Create Order interface file from input excel order files.

import os
import time
import datetime
import mysql.connector
import csv


# Excel Class
class lineText:
    def _init_(self):
        dateCreated = ""
        kenn = ""
        line = ""
        fis = ""
        supplyArea = ""
        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 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 updateCarBodyUpload(connection,lineText):
    date = datetime.datetime.now()
    cursor = connection.cursor()
    updCursor = connection.cursor()
    
    print("lineText.line:",str(lineText.line))
# Test for Existing Reference       
    dataQuery = "SELECT id FROM car_body_upload WHERE kenn='"+lineText.kenn+"' AND line='"+lineText.line+"' LIMIT 1"
    try:
        cursor.execute(dataQuery)
        row = cursor.fetchone()
        if (row is None):
            if (lineText.line is None or lineText.line == ''):
                lineText.line = '0000'
            if (lineText.line == '0000'):
                status = 'RETURN'
            elif (lineText.line == 'A9'):
                status = 'RETURN'
            elif (lineText.line == 'KLT'):
                status = 'RETURN'
            elif (lineText.line == 'KTL'):
                status = 'RETURN'
            else:
                status = "PICK"
            dataQuery = "INSERT INTO car_body_upload (id,kenn,line,fis,supply_area,qty,type,date_created,created_by,last_updated,last_updated_by,processed,status_id)"
            dataQuery += " VALUES(DEFAULT,'"+lineText.kenn+"','"+lineText.line+"','"+lineText.fis+"','"+lineText.supplyArea+"',"+str(lineText.expectedQty)+",'"+status+"',"+"now(),'system',now(),'system',0,1)"
            try:
                cursor.execute(dataQuery)
                connection.commit()
            except Exception as e:
                print ('Update Error',dataQuery,e)
            ordId = cursor.lastrowid
        else:
            ordId = row[0]
    except UnboundLocalError:
        print ('Connection Error')
            
    finally:
        cursor.close()
    return ordId
#    print("orderUpload")

def main():
# Default Connection / System Settings
    # Default Connection / System Settings
    env = load_dotenv_simple('c:/webroot/rdtcarbody/.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
    )   
    file_path_txt = 'car_body.txt'
    if os.path.isfile(file_path_txt):
        ts = getts()
        print('Time:'+str(ts))
        
        c = 0
        i = 0
    
        with open(file_path_txt, 'r', newline='') as file:
# Create a CSV reader object using tab as the delimiter
            reader = csv.reader(file, delimiter='\t')
# Iterate over each row in the file
            for row in reader:
                if (row[0] == "Created On"):
                    continue
                if (i == 0):
                    print(row)
                    i=i+1
                lineText.kenn  = row[1]
                lineText.line  = row[2]
                lineText.fis  = row[3]
                lineText.supplyArea  = row[4]
                lineText.dateCreated  = row[0]
                lineText.expectedQty = 1
        
                ordId = updateCarBodyUpload(cnx,lineText)
                print ("Car_Body_Upload:",lineText.kenn)
#                updateOrder(connection,ordId)
    else:
        print ("File not Found - ",file_path_txt)
    return
    
if __name__ == '__main__':
    main()