# ------------------------------------------------------------------------------
# Allocate TT Orders to Inventory
# 2021-03-16 - set inventory_master.inventory_status_id to 'ok'
# 2021-03-19 - extra processing for noran status
# 2021-04-21 - Supress allocation of 'T' Type RANs
# ------------------------------------------------------------------------------
import os
import time
import datetime
import json
import mysql.connector 
import requests
 
import default_setting

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

def tt_allocate(connection,path):
    docRef = ""
    try:
        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 id for 'ok' inventory status
        invStsId = 0
        dataQuery = "SELECT id FROM inventory_status WHERE lower(inventory_status_code) = 'ok' LIMIT 1"
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        invStsId = row['id']
        if (invStsId == None):
            invStsId = 0
        ranStsId = 10
        dataQuery = "SELECT id FROM inventory_status WHERE lower(inventory_status_code) = 'noran' LIMIT 1"
        cursor.execute(dataQuery) 
        row = cursor.fetchone()
        ranStsId = row['id']
        if (ranStsId == None):
            ranStsId = 0
			
        cursor.close()
# Read open TT Orders		
        try:
            cursor = connection.cursor(dictionary=True)
            dataQuery = "SELECT tt_ran_order.id AS id, tt_ran_order.ran_order, tt_ran_order.expected_qty-tt_ran_order.scanned_qty AS quantity,tt_ran_order.required_date AS required_date, part.id AS part_id"
            dataQuery +=" FROM tt_ran_order"
            dataQuery +=" JOIN part ON tt_ran_order.part_number=part.part_number"
            dataQuery +=" JOIN vendor ON tt_ran_order.vendor=vendor.duns_code"
            dataQuery +=" WHERE"
            dataQuery +=" substr(tt_ran_order.ran_order,1,1)<>'T'"
            dataQuery +=" AND tt_ran_order.scanned_qty<tt_ran_order.expected_qty AND tt_ran_order.completed=0"
            dataQuery +=" AND (vendor.tt_ran_fwd=1 OR DATE(tt_ran_order.required_date)<=date(now()))"
            dataQuery +=" ORDER BY tt_ran_order.part_number, tt_ran_order.required_date"
            cursor.execute(dataQuery) 
            data = cursor.fetchall()
            for row in data:
                ordId = row['id']
                partId = row['part_id']
                qty = row['quantity']
                ranOrder = row['ran_order']
                reqDate = row['required_date']
                                
                invQty = 0
                if qty > 0:
                    invQuery = "SELECT inventory_master.id AS inv_id, inventory_master.inventory_qty-inventory_master.allocated_qty AS inv_qty,inventory_master.serial_reference AS serial_reference"
                    invQuery +=", receipt_detail.id AS dtl_id,product_type.product_type_code"
                    invQuery +=" FROM inventory_master"
#                    invQuery +=" LEFT JOIN receipt_detail ON inventory_master.part_id=receipt_detail.part_id AND inventory_master.serial_reference=receipt_detail.serial_reference" 
                    invQuery +=" LEFT JOIN receipt_detail ON (receipt_detail.id = (SELECT MIN(receipt_detail.id) FROM receipt_detail WHERE (inventory_master.part_id=receipt_detail.part_id AND inventory_master.serial_reference=receipt_detail.serial_reference) LIMIT 1))"
                    invQuery +=" LEFT JOIN receipt_body ON receipt_detail.receipt_body_id=receipt_body.id"
                    invQuery +=" LEFT JOIN receipt_header ON receipt_body.receipt_header_id=receipt_header.id"
                    invQuery +=" LEFT JOIN document_status ON receipt_header.document_status_id=document_status.id"
                    invQuery +=" LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id"
                    invQuery +=" WHERE inventory_master.part_id="+str(partId)
                    invQuery +=" AND (inventory_master.ran_or_order='NO-RAN' OR inventory_master.ran_or_order='' OR inventory_master.ran_or_order IS null)"
                    invQuery +=" AND (inventory_master.inventory_qty-inventory_master.allocated_qty>0) AND inventory_master.inventory_status_id IN("+str(invStsId)+","+str(ranStsId)+",10)"
                    invQuery +=" AND (document_status.document_status_code IS null OR lower(document_status.document_status_code) IN('closed','transmitted'))"
                    invQuery +=" AND upper(product_type.product_type_code)='W1-PRD'"
                    invQuery +=" ORDER BY inventory_master.date_created"
                    cursor.execute(invQuery) 
                    invData = cursor.fetchall()
                    for invRow in invData:
                        updQuery = "UPDATE inventory_master SET ran_or_order = '"+ranOrder+"',last_updated=now(),last_updated_by='TTRAN',inventory_status_id="+str(invStsId)+" WHERE id = "+str(invRow['inv_id'])+" LIMIT 1"
                        try:
                            cursor.execute(updQuery)
                            connection.commit()
                        except UnboundLocalError:
                            print ('FAIL-Update Error')
                        updQuery = "UPDATE tt_ran_order SET scanned_qty = scanned_qty+"+str(invRow['inv_qty'])+",completed = CASE WHEN expected_qty>scanned_qty THEN 1 ELSE 1 END,last_updated=now() WHERE id="+str(ordId)+" LIMIT 1"
                        try:
                            cursor.execute(updQuery)
                            connection.commit()
                        except UnboundLocalError:
                            print ('FAIL-Update Error')
                        invQty = invQty + invRow['inv_qty']
                        qty = qty-invRow['inv_qty']
                        
                        if (invRow['dtl_id'] is not None):
                            updQuery = "UPDATE receipt_detail SET ran_order = '"+ranOrder+"',last_updated_date=now(),last_updated_by='TTRAN' WHERE id = "+str(invRow['dtl_id'])+" LIMIT 1"
                            try:
                                cursor.execute(updQuery)
                                connection.commit()
                            except UnboundLocalError:
                                raise 'Update Error'
                        
                        updQuery = "UPDATE transaction_history SET ran_or_order = '"+ranOrder+"',last_updated=now(),last_updated_by='TTRAN' WHERE LEFT(short_code,2)='RB' AND ran_or_order='NO-RAN' AND serial_reference = '"+str(invRow['serial_reference'])+"' LIMIT 1"
                        try:
                            cursor.execute(updQuery)
                            connection.commit()
                        except UnboundLocalError:
                            print ('FAIL-Update Error')
                        
                        if (qty <= 0):
                            break
                if ((invQty > 0) and (invQty <= 1000000)):
#                    updQuery = "UPDATE tt_ran_order SET scanned_qty = scanned_qty+"+str(invQty)+",completed = 1,last_updated=now() WHERE id="+str(ordId)+" LIMIT 1"
#                    try:
#                        cursor.execute(updQuery)
#                        connection.commit()
#                    except UnboundLocalError:
#                        print ('FAIL-Update Error')
                        
                    ranConfirm(path,ranOrder)
                    
                    print ('RAN Allocated-',ranOrder)
                        
        except Exception as e:
            print("Error:",e)
        finally:
            cursor.close()
    except UnboundLocalError as e:
        print(e,dataQuery)

    return
# send RAN confirm message
def ranConfirm(path,ranOrder):
    connect = path.replace(':8081/vantec/',':8092/screen')
    url = connect+"/ttRanReceiptConfirmMessage?ranOrder="+ranOrder
    r = requests.post(url)
    print ('response',r,r.status_code,url)
    time.sleep(1)
    
# timestamp value	
def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    return ts

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'])

    status = tt_allocate(connection,defaults['path'])
			
if __name__ == '__main__':
    main()