# ------------------------------------------------------------------------------
# Convert JSON to Orders
# 2021-01-04 - test that DNS exists on vendor table
# 2021-02-26 - increase timeslot-dock to 2 chars.
# 2021-03-10 - get product_type_id from time_slot_master
# 2021-03-15 - pass order_type to getTimeSlot
# ------------------------------------------------------------------------------
import sys
import os
import time
import datetime
import json
import mysql.connector
from collections import OrderedDict 

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

def header(connection,hdr,timeSlot,dock):
    hdrId = ""
    docRef = ""
    bdyLine = 0
    try:
        print("Delivery Date ",hdr.dateTime)

    #		cursor = connection.cursor()
        cursor = connection.cursor(dictionary=True)
		
# 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']
# Get product_type_id from timeslot
        productType = ""
        dataQuery = "SELECT time_slot_master.product_type_id AS product_type_id, product_type.product_type_code AS product_type_code FROM time_slot_master LEFT JOIN product_type ON time_slot_master.product_type_id=product_type.id WHERE destination = '"+str(dock) [0:2]+"' AND time_slot='"+timeSlot[11:19]+"' LIMIT 1"
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        if row is None:
            dataQuery = "SELECT time_slot_master.product_type_id AS product_type_id, product_type.product_type_code AS product_type_code FROM time_slot_master LEFT JOIN product_type ON time_slot_master.product_type_id=product_type.id WHERE destination = '"+str(dock) [0:1]+"' AND time_slot='"+timeSlot[11:19]+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
        if row is not None:
            productTypeId = row['product_type_id']
            productType = row['product_type_code']
        else:
            productTypeId = 0
            productType = ""
        print("PRODTYPE",productType,productTypeId)
# Test for Existing Reference	
        print("HDR:"+str(timeSlot)+" "+str(dock)[0:2])
        try:
            customerReference = timeSlot[0:4]+timeSlot[5:7]+timeSlot[8:10]+" "+timeSlot[11:13]+timeSlot[14:16]+str(dock)[0:2]
        except:
            customerReference = None
        dataQuery = "SELECT id, document_reference FROM order_header WHERE time_slot = '"+str(timeSlot)+"' AND dock_destination = '"+str(dock)[0:2]+"' 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,consignee,ship_to,dock_destination,product_type,date_created,created_by,last_updated_date,last_updated_by)"
                if (customerReference == None):
                    dataQuery += " VALUES(DEFAULT,'COR000000',null,'"+str(hdr.dateTime)+"','"+str(timeSlot)+"',"+str(documentStsId)+",'"+str(hdr.orderType)+"','NI001','NI001','"+str(dock)[0:2]+"','"+productType+"',now(),'system',now(),'system')"
                else:
                    dataQuery += " VALUES(DEFAULT,'COR000000','"+str(customerReference)+"','"+str(hdr.dateTime)+"','"+str(timeSlot)+"',"+str(documentStsId)+",'"+str(hdr.orderType)+"','NI001','NI001','"+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+"', last_updated_date=now(), last_updated_by='system' WHERE id="+str(hdrId)+" LIMIT 1"
                try:
                    cursor.execute(dataQuery)
                    connection.commit()
                except UnboundLocalError:
                    raise 'Update Error'
            else:
                hdrId = row['id']
                if (row['document_reference'] == "COR000000"):
                    docRef = "000000"+str(hdrId)
                    docRef = "COR"+docRef[-6:]
                    dataQuery = "UPDATE order_header SET document_reference='"+docRef+"', last_updated_date=now(), last_updated_by='system' WHERE id="+str(hdrId)+" LIMIT 1"
                    try:
                        cursor.execute(dataQuery)
                        connection.commit()
                    except UnboundLocalError:
                        raise 'Update Error'
                else:
                    docRef = row['document_reference']
                dataQuery = "SELECT line_no AS line_no FROM order_body WHERE order_header_id="+str(hdrId)+" ORDER BY line_no DESC LIMIT 1"
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                if row is not None:
                    bdyLine = row['line_no']
                else:
                    bdyLine = 0
        finally:
            cursor.close()
    except UnboundLocalError as e:
        print(e)

    print("hdrDocRef-",docRef)
#	print("hdrId-",hdrId)
    hdr.id = hdrId
    if (customerReference is not None):
        hdr.customerRef = customerReference
    hdr.docRef = docRef
    hdr.bdyLine = bdyLine+1
    if (productTypeId is None):
        productTypeId =0
    hdr.productTypeId = productTypeId
    return hdr

def body(connection,hdr,segments,bdyId):
#	bdyId = ""
    vendorId = ""
    vendorCode = ""
    partNumber= ""
    advisedQty = 0
    sequence = ""
    storage = ""
    conversionFactor = 1
    lineNo = 0
    segnam = ""
    zone = ""
    toLoc = ""

    print("bdyDocRef-",hdr.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 == "ran_order"):
            ranOrder = value.strip()
        elif (key == "part_number"):
            partNumber = value.strip()
        elif (key == "serialReference"):
            serialReference = value.strip()
        elif (key == "quantity"):
            expectedQty = value.strip()
        elif (key == "lineNo"):
            lineNo = value.strip()
        elif (key == "date"):
            expectedDate = value.strip()
        elif (key == "time"):
            expectedTime = value.strip()
        elif (key == "dock"):
            dock = value.strip()
        elif (key == "zone"):
            zone = value.strip()
        elif (key == "status"):
            sts = value.strip()
        elif (key == "vendor_code"):
            vendorCode = value.strip()
        elif (key == "to_location"):
            toLoc = value.strip()
            
    cursor = connection.cursor(dictionary=True)
    cursor1 = connection.cursor(dictionary=True)

# test for valid DNS Code	
    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):
        return ""
		
# Get Timeslot based on date/time & dock
#    print("TEST1-",hdr.orderType)
    dateTime = expectedDate[0:4]+"-"+expectedDate[4:6]+'-'+expectedDate[6:8]+' '+expectedTime[0:2]+':'+expectedTime[2:4]+':'+'00'
    timeSlot = gettimeslot.timeSlot(str(dateTime)[0:10], str(dateTime)[11:19], dock[0:2],hdr.orderType)

    if (timeSlot == ""):
        return ""
    if (timeSlot != ""):
        hdr.dateTime = expectedDate[0:4]+"-"+expectedDate[4:6]+'-'+expectedDate[6:8]+' '+expectedTime[0:2]+':'+expectedTime[2:4]+':'+'00'
        hdr = header(connection,hdr,timeSlot,dock)
    if (partNumber != ""):
        try:
            print("PartNumber",partNumber,hdr.productTypeId)
# Delete existing order_body if the statis is cancel (c)
            if ((sts is not None) and (sts == "C")):
                dataQuery = "UPDATE order_body SET blocked = 1 WHERE part_number='"+partNumber+"' AND ran_order='"+ranOrder+"'"
                try:
                    cursor.execute(dataQuery)
                    connection.commit()
                except Exception as Err:
                    print ('Update Error 1'+str(Err)+dataQuery)
            else:
# Delete existing order_body if the timeslot has changed.
                dataQuery = "SELECT id FROM order_body WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' AND qty_transacted=0 AND order_header_id<>"+str(hdr.id)
                try:
                    cursor.execute(dataQuery) 
                    for row in cursor.fetchall():
                        ordBdyId = row['id']
                        try:
                            dataQueryPck = "DELETE FROM pick_detail WHERE id > 0 AND EXISTS(SELECT id FROM pick_body WHERE pick_detail.pick_body_id=pick_body.id AND pick_body.order_body_id="+str(ordBdyId)+")"
                            cursor1.execute(dataQueryPck)
                            connection.commit()
                        except Exception as Err:
                            print ('Update Error 2'+str(Err)+dataQueryPck)
                        try:
                            dataQueryPck = "DELETE FROM pick_body WHERE id > 0 AND pick_body.order_body_id="+str(ordBdyId)
                            cursor1.execute(dataQueryPck) 
                            connection.commit()
                        except Exception as Err:
                            print ('Update Error 3'+str(Err)+dataQueryPck)
                except Exception as Err:
                        print ('Read Error '+str(Err)+dataQuery)
                dataQuery = "DELETE FROM order_body WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' AND qty_transacted=0 AND order_header_id<>"+str(hdr.id)
                try:
                    cursor1.execute(dataQuery)
                    connection.commit()
                except Exception as Err:
                    print ('Update Error 4'+str(Err)+dataQuery)
					
# Test for Existing Part
                partId=""
#			cursor = connection.cursor(dictionary=True)
                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['id']
                        vendorId = row['vendor_id']
                        conversionFactor = row['conversion_factor']
                except Exception as Err:
                        print ('Update Error 5'+str(Err)+dataQuery)
                    
                qty = float(expectedQty.strip())
                qty = float(qty*conversionFactor)

                dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdr.id)+" AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' LIMIT 1"
                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,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 (hdr.productTypeId != 0):
                            dataQuery +=","+str(hdr.productTypeId)
                        else:
                            dataQuery +=",null"
                        dataQuery +=",'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(hdr.bdyLine)+",'"+ranOrder+"'"
                        if (partId != ""):
                            dataQuery +=","+str(partId)
                        else:
                            dataQuery +=",null"
                        dataQuery +=","+str(hdr.id)+",'"+hdr.customerRef+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+str(dateTime)+"','"+toLoc+"')"
#                        if (partId == ""):
#                            dataQuery += " VALUES(DEFAULT,'"+hdr.docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(hdr.bdyLine)+",'"+ranOrder+"',null,"+str(hdr.id)+",'"+hdr.customerRef+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+str(dateTime)+"','"+toLoc+"')"
#                        else:
#                            dataQuery += " VALUES(DEFAULT,'"+hdr.docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(hdr.bdyLine)+",'"+ranOrder+"',"+str(partId)+","+str(hdr.id)+",'"+hdr.customerRef+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+str(dateTime)+"','"+toLoc+"')"
                        try:
                            cursor.execute(dataQuery)
                            connection.commit()
                        except Exception as Err:
                            print ('Update Error 6'+str(Err)+dataQuery)
                        bdyId = cursor.lastrowid
                    else:
                        bdyId = row['id']
                        if ((qty != "") and (qty is not None)):
#                            dataQuery = "UPDATE order_body SET document_reference='"+hdr.docRef+"',qty_expected=qty_expected+"+str(qty)+",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='system',to_location='"+toLoc+"' WHERE id="+str(bdyId)+" LIMIT 1"
                            dataQuery = "UPDATE order_body SET document_reference='"+hdr.docRef+"',qty_expected="+str(qty)+",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='system',to_location='"+toLoc+"' WHERE id="+str(bdyId)+" LIMIT 1"
                            try:
                                cursor.execute(dataQuery)
                                connection.commit()
                            except Exception as Err:
                                print ('Update Error 7'+str(Err)+dataQuery)
                except mysql.connector.Error as err:
                    print("mysql Error: {}".format(err))
                finally:
                    cursor.close()
        except Exception as Err:
            print ("Update Error 8",str(Err),dataQuery)
    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,hdr,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():
# hdr class
    class hdr_class:
        id = ""
        docRef = ""
        customerRef = ""
        orderType = ""
        bdyLine = 0
        dock = ""
        dateTime = ""
        productTypeId = 0

#	global bdyId, dtlId
    hdr = hdr_class
    bdyId = ''
    bdyLine = 0
    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/lromsgin.json')
    try:
        jsonFile = open(file_path_json)
    except:
        return
    jsonObject = json.load(jsonFile, object_pairs_hook=OrderedDict)
#    print("jsonObject:",jsonObject)	
    for key in jsonObject:
        values = jsonObject[key]
# Order Header
        if (key == "lroHeader"):
            hdr.customerRef = values['hdrref']
            hdr.orderType = 'STD'
            if (values['time'][0:4]=="1234"):
                hdr.orderType = "EMG"
#			dateTime = getts()
            hdr.dateTime = values['date'][0:4]+"-"+values['date'][4:6]+'-'+values['date'][6:8]+' '+values['time'][0:2]+':'+values['time'][2:4]+':'+values['time'][4:6]
#			hdr = header(connection,customerRef,orderType,dateTime,hdr)
            bdyId = ""
            bdyLine = 0
# Order Body			
        if ((key == "lroBody") and (hdr.customerRef != "")):
            if (type(values) == list):
                for segments in values:
                    bdyId = body(connection,hdr,segments,bdyId)
            else:
                bdyId = body(connection,hdr,values,bdyId)
                
                print('bdyLine:',hdr.bdyLine)
# Order Detail		
        if ((key == "lroDetail") and (bdyId != "")):
            if (type(values) == list):
                for segments in values:
                    dtlId = detail(connection,hdr,segments)
            else:
                    dtlId = detail(connection,hdr,values,bdyId,bdyLine)
# Order Footer
        if (key == "lroFooter"):
            hdr.customerRef = ""
# Order Header
        if (key == "vimsorderHeader"):
            hdr.customerRef = values['customerReference']
            hdr.orderType = values['orderType']
            dateTime = getts()
#			hdr = header(connection,customerRef,orderType,dateTime)
            bdyId = ""
            bdyLine = 0
# Order Body			
        if ((key == "vimsorderBody") and (hdr.customerRef != "")):
            if (type(values) == list):
                for segments in values:
                    bdyId = body(connection,hdr,segments,bdyId)
            else:
                    bdyId = body(connection,hdr,values,bdyId)
# Order Detail		
        if ((key == "vimsorderDetail") and (bdyId != "")):
            if (type(values) == list):
                for segments in values:
                    dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,segments,bdyId)
            else:
                    dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,values,bdyId)
            
        try:
            os.remove(file_path_json)
        except:
            pass
			
if __name__ == '__main__':
    main()