# Create Order interface file from input excel order files.

import os
import time
import datetime
import openpyxl
import mysql.connector
 
import default_setting


# Excel Class
class excel:
    def _init_(self):
        fis = ""
        kenn = ""
        lineNo = 0
        perlChain = ""
        extSeq = ""
        assemblySeq = ""
        customerRef = ""
        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 updateOrder(connection,excel):
    date = datetime.datetime.now()
    cursor = 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[0]
    
# Test for Existing Reference       
        dataQuery = "SELECT id, document_reference FROM order_header WHERE customer_reference="+excel.customerRef+" LIMIT 1"
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if row 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','"+excel.customerRef+"','"+str(date)+"','"+str(date)+"',"+str(documentStsId)+",'STD',now(),'system',now(),'system')"
                try:
                    cursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
                hdrId = cursor.lastrowid
                docRef = "000000"+str(hdrId)
                docRef = "COR"+docRef[-6:]
                dataQuery = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
                try:
                    cursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
            else:
                hdrId = row[0]
                if (row[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:
                        cursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        raise 'Update Error'
                else:
                    docRef = row[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 = '"+excel.partNumber+"' LIMIT 1"
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if row is not None:
                partId = row[0]
                vendorId = row[1]
                conversionFactor = row[2]
        except:
            print ("Update Error",dataQuery)
            
        qty = float(excel.expectedQty)
        qty = float(qty*conversionFactor)
        
        dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+excel.partNumber+"' AND line_no="+excel.lineNo+" LIMIT 1"
#        print ("bdyQry-",dataQuery)
        try:
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if row 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,date_created,created_by,last_updated_date,last_updated_by)"
                if (partId == ""):
                    dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+excel.partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+excel.lineNo+",null,"+str(hdrId)+","+excel.customerRef+",now(),'system',now(),'system')"
                else:
                    dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+excel.partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+excel.lineNo+","+str(partId)+","+str(hdrId)+","+excel.customerRef+",now(),'system',now(),'system')"
                try:
                    cursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
                bdyId = cursor.lastrowid
            else:
                bdyId = row[0]
                if ((qty != "") and (qty is not None)):
                    dataQuery = "UPDATE order_body SET document_reference='"+docRef+"',qty_expected=qty_expected+"+str(qty)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(bdyId)+" LIMIT 1"
                    try:
                        cursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        raise 'Update Error'
        except UnboundLocalError as e:
            print(e)
            
    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'])

    file_path_xls = 'c:/upload/order.xlsx'
    if os.path.isfile(file_path_xls):
        wb =openpyxl.load_workbook(file_path_xls) 

        ts = getts()
        print('Time:'+str(ts))

        values = []
# Loop through the Sheets
        sheet = wb.active
#            print ('Sheet:',sheet.name)
        c = 0
        i = 0
# Loop through the Sheet Rows
        for row in sheet.iter_rows():
            value  = row[0].value
            try : value = str(int(value))
            except : pass
            if (value.lower() == "creation date"):
                continue
# test for header row
            excel.fis  = row[1].value
            try : excel.fis = str(int(excel.fis))
            except : pass
            excel.kenn  = row[2].value
            try : excel.kenn = str(int(excel.kenn))                 
            except : pass
            excel.lineNo  = row[3].value
            try : excel.lineNo = str(int(exel.lineNo))
            except : pass
            excel.perlChain  = row[4].value
            try : excel.perlChain = str(int(exel.perlChain))
            except : pass
            excel.extSeq  = row[5].value
            try : excel.extSeq = str(int(exel.extSeq))
            except : pass
            excel.assemblySeq  = row[6].value
            try : excel.assemblySeq = str(int(exel.assemblySeq))
            except : pass
            excel.customerRef  = row[7].value
            try : excel.customerRef = str(int(exel.customerRef))
            except : pass
            excel.psa  = row[8].value
            try : excel.psa = str(int(exel.psa))
            except : pass
            excel.partNumber  = row[9].value
            excel.partNumber = excel.partNumber.replace("'"," ")
            try : 
                excel.partNumber = str(int(exel.partNumber))
            except : pass
            excel.expectedQty = 1
            updateOrder(connection,excel)
    else:
        print ("File not Found - ",file_path_xls)
        
if __name__ == '__main__':
    main()