# ------------------------------------------------------------------------------
# Allocate TT Orders to Inventory
# ------------------------------------------------------------------------------
import os
import time
import datetime
import json
import mysql.connector 

import default_setting

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

def allocate(connection):
	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

		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, part.id AS part_id  FROM tt_ran_order JOIN part ON tt_ran_order.part_number=part.part_number WHERE tt_ran_order.scanned_qty<tt_ran_order.expected_qty AND tt_ran_order.completed=0"
			cursor.execute(dataQuery) 
			data = cursor.fetchall()
			for row in data:
				ordId = row['id']
				partId = row['part_id']
				qty = row['quantity']
				ranOrder = row['ran_order']
				
				invQty = 0
				while {qty > 0}:
					invQuery = "SELECT id AS id, inventory_qty-allocated_qty AS inv_qty FROM inventory_master WHERE part_id="+str(partId)+" AND (inventory_qty-allocated_qty>0) AND inventory_status_id="+str(invStsId)
					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' WHERE id = "+str(invRow['id'])+" LIMIT 1"
						try:
							cursor.execute(updQuery)
							connection.commit()
						except UnboundLocalError:
							raise 'Update Error'
						invQty = invQty + invRow['inv_qty']
						qty = qty-invRow['inv_qty']
					else:
						break
				if (invQty > 0):
					updQuery = "UPDATE tt_ran_order SET scanned_qty = scanned_qty+"+str(invQty)+", completed = (CASE WHEN expected_qty <= scanned_qty THEN 1 ELSE 0 END) WHERE id="+str(ordId)+" LIMIT 1"
					try:
						cursor.execute(updQuery)
						connection.commit()
					except UnboundLocalError:
						raise 'Update Error'
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)
	
	return
	
# 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 = allocate(connection)
			
if __name__ == '__main__':
	main()