# ------------------------------------------------------------------------------
# Convert JSON to Orders
# 2021-03-18 - added orderTye to getTimeSlot
# 2021-04-06 - Don't update order_header if already picked.
# ------------------------------------------------------------------------------
import os
import time
import datetime
import json
import mysql.connector 

import default_setting
import gettimeslot

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

# hdr class
class hdr:
    def _init_(self):
        id = ""
        docRef = ""
        customerRef = ""
        doc = ""
        productTypeId = 1
def header(connection,customerRef,orderType,date,dock,vendorCode):
    hdrId = ""
    docRef = ""

    cursor = connection.cursor()
    
# test for valid DNS Code    
    if (vendorCode != ""):	
        i = 0
        dataQuery = "SELECT id FROM vendor WHERE duns_code='"+str(vendorCode)+"' LIMIT 1"
        try:
            cursor.execute(dataQuery) 
            for row in cursor.fetchall():
                i=i+1
        except Exception as Err:
            print ('Select Error 1'+str(Err)+dataQuery)
            
        if (i==0 or vendorCode == "" or vendorCode is None):
            hdr.id = ""
            return hdr

    try:
        print("Delivery Date ",date)
        try:
            timeSlot = gettimeslot.timeSlot(str(date)[0:10], str(date)[11:19], dock[0:2],orderType)
        except Exception as err:
            print("FAIL-"+str(err))
            date = getts()
            timeSlot = gettimeslot.timeSlot(str(date)[0:10], str(date)[11:19], dock[0:2],orderType)
        print("Time Slot",timeSlot)
		
# 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]
        
# Get id for 'PROD' document status	
        productTypeId = 1
        productType = "W1-PRD"
        dataQuery = "SELECT id,product_type_code FROM product_type WHERE lower(product_type_code) = 'w1-prd' LIMIT 1"
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        productTypeIdId = row[0]
        productType=row[1]
		
# Test for Existing Reference	
        if ((customerRef == "") or (customerRef == None) or (customerRef == '""')):
            customerRef = timeSlot[0:4]+timeSlot[5:7]+timeSlot[8:10]+" "+timeSlot[11:13]+timeSlot[14:16]+str(dock)[0:2]
        print("CustomerRef",customerRef)
        dataQuery = "SELECT id, document_reference FROM order_header WHERE customer_reference='"+customerRef+"'"
        dataQuery +=" AND EXISTS(SELECT id FROM document_status WHERE order_header.document_status_id=document_status.id AND lower(document_status.document_status_code) IN('open','allocated'))"
        dataQuery +=" 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,dock_destination,product_type,date_created,created_by,last_updated_date,last_updated_by)"
                dataQuery += " VALUES(DEFAULT,'COR000000','"+customerRef+"','"+str(date)+"','"+str(timeSlot)+"',"+str(documentStsId)+",'"+str(orderType)+"','"+str(dock)[0:2]+"','"+productType+"',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]
        finally:
            cursor.close()
    except UnboundLocalError as e:
        print(e)
	
    print("hdrDocRef-",docRef)

    hdr.id = hdrId
    hdr.docRef = docRef
    hdr.customerRef = customerRef
    hdr.dock = dock
    hdr.productTypeId = productTypeId
    return hdr

def body(connection,hdrId,docRef,customerRef,segments,bdyId,date,dock,productTypeId):
#	bdyId = ""
    vendorId = ""
    partNumber= ""
    advisedQty = 0
    sequence = ""
    storage = ""
    ranOrder = ""
    segnam = ""
    conversionFactor = 1
    expectedDate = ""
    expectedTime = ""
    zone = ""
	
#	print("bdyDocRef-",docRef)
	
    for key in segments:
#		print("key:"+str(key)+":"+str(segments)+"-"+str(bdyId))
        try:
            value = segments[key]
#			print("value"+str(value))
        except:
            pass
        if (key == "segnam"):
            segnam = value.strip()
        elif (key == "partNumber"):
            partNumber = value.strip()
        elif (key == "ranOrder"):
            ranOrder = value.strip()
        elif (key == "serialReference"):
            serialReference = value.strip()
        elif (key == "expectedQty"):
            expectedQty = value.strip()
        elif (key == "lineNo"):
            lineNo = value.strip()
        elif (key == "orderLine"):
            lineNo = value.strip()
        elif (key == "expectedDate"):
            expectedDate = value.strip()
        elif (key == "expectedTime"):
            expectedTime = value.strip()
	
#	if (partNumber != ""):
    if ((segnam == "E2LTORI") or (partNumber != "")):
        try:
# Test for Existing Part
            partId=""
            cursor = connection.cursor()
            dataQuery = "SELECT id, vendor_id, conversion_factor FROM part WHERE part_number = '"+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 UnboundLocalError:
                raise 'Update Error'
                
            qty = float(expectedQty.strip())
            qty = float(qty*conversionFactor)

            dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' 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,ran_order,part_id,order_header_id,customer_reference,expected_delivery_time,date_created,created_by,last_updated_date,last_updated_by)"
     #               if (partId == ""):
     #                   dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+lineNo+",'"+ranOrder+"',null,"+str(hdrId)+",'"+customerRef+"','"+str(date)+"',now(),'system',now(),'system')"
     #               else:
     #                   dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+lineNo+",'"+ranOrder+"',"+str(partId)+","+str(hdrId)+",'"+customerRef+"','"+str(date)+"',now(),'system',now(),'system')"
                    dataQuery = "INSERT INTO order_body (id,document_reference,product_type_id,part_number,qty_expected,qty_transacted,difference,line_no,ran_order,part_id,order_header_id,customer_reference,zone_destination,dock_code,sequence,date_created,created_by,last_updated_date,last_updated_by,expected_delivery_time,to_location)"
                    dataQuery +=" VALUES(DEFAULT,'"+hdr.docRef+"'"
                    if (productTypeId != 0):
                        dataQuery +=","+str(productTypeId)
                    else:
                        dataQuery +=",null"
                    dataQuery +=",'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(lineNo)+",'"+ranOrder+"'"
                    if (partId != ""):
                        dataQuery +=","+str(partId)
                    else:
                        dataQuery +=",null"
                    dataQuery +=","+str(hdrId)+",'"+customerRef+"','"+zone+"','"+dock+"',"+str(lineNo)+",now(),'system',now(),'system','"+str(date)+"',null)"
                    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'
            finally:
                cursor.close()
        except UnboundLocalError as e:
            print(e)
            
    if (segnam == "Z2LTORH"):
        try:
            dateTime = expectedDate[0:4]+"-"+expectedDate[4:6]+'-'+expectedDate[6:8]+' '+expectedTime[0:2]+':'+expectedTime[2:4]+':'+expectedTime[4:6]
            cursor = connection.cursor()
            dataQuery = "UPDATE order_header SET expected_delivery_time='"+dateTime+"',last_updated_date=now(),last_updated_by='system' WHERE id="+str(hdrId)+" LIMIT 1"
    #			print ("hdrQry-",dataQuery)
            try:
                cursor.execute(dataQuery)
                connection.commit()
            except UnboundLocalError:
                raise 'Update Error'
            finally:
                cursor.close()
        except UnboundLocalError as e:
            print(e)
        
    return bdyId

def detail(connection,hdrId,docRef,customerRef,segments,bdyId):
    dtlId = ""
    return dtlId
	
# timestamp value	
def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    return ts

def main():
    global hdr, bdyId, dtlId
# 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_json = os.path.join(dir_path, '../vimsjson/idoctranout.json')
    jsonFile = open(file_path_json)
    jsonObject = json.load(jsonFile)
    
    vendorCode = ""
    for key in jsonObject:
        values = jsonObject[key]
# Order Header
        if (key == "vimsorderHeader"):
            customerRef = values['customerReference']
            orderType = values['orderType']
            if (values['expectedDate'] is not None):
                dateTime = values['expectedDate']
            else:
                dateTime = getts()
            if (values['destination'] is not None):
                dock = values['destination']
            else:
                dock = ""
            try:
                vendorCode = values['vendorCode']
            except:
                vendorCode = ""
            hdr = header(connection,customerRef,orderType,dateTime,dock,vendorCode)
            print("Vendor:",hdr.id);
            if (hdr.id == ""):
                print("Inactive Vendor:",vendorCode)
                return
                
            bdyId = ""
# Order Body			
        if (key == "vimsorderBody"):
            if (type(values) == list):
                for segments in values:
                    bdyId = body(connection,hdr.id,hdr.docRef,hdr.customerRef,segments,bdyId,dateTime,dock,hdr.productTypeId)
            else:
                    bdyId = body(connection,hdr.id,hdr.docRef,hdr.customerRef,values,bdyId,dateTime,dock,hdr.productTypeId)
# Order Detail		
        if (key == "vimsorderDetail"):
            if (type(values) == list):
                for segments in values:
                    dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,segments,bdyId,dateTime)
            else:
                    dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,values,bdyId,dateTime)
            
        try:
            os.remove(file_path_json)
        except:
            pass
			
if __name__ == '__main__':
    main()