# Create / Update Receipt Data from ASN Interface Files

import os
import time
import datetime
import json
import mysql.connector
import datetime
import xlrd
# from xlrd import open_workbook

import default_setting

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

def header(cnx,ref, date):
	hdrId = ""
	try:
# pad / format date/time
#		date = '{message:{fill}{align}{width}}'.format(message=date,fill=0,align='>',width=10)
#		time = '{message:{fill}{align}{width}}'.format(message=time,fill=0,align='>',width=6)
#		dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+time[4:6]
#		dateTime = date[6:9]+"-"+date[3:5]+'-'+date[0:3]+' 00:00:01'
		print("Delivery Date ",date)
# Test for Existing Reference
		cursor = cnx.cursor()
		dataQuery = "SELECT id, document_reference FROM receipt_header WHERE customer_reference="+ref+" LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "INSERT INTO receipt_header (id,document_reference,product_type_id,customer_reference,haulier_reference,vendor_id,expected_delivery_date,document_status_id,inspect,date_created,created_by,last_updated_date,last_updated_by)"
				dataQuery += " VALUES(DEFAULT,'RCP000000',null,'"+ref+"','"+ref+"',null,'"+str(date)+"',1,null,now(),'system',now(),'system')"
				try:
					cursor.execute(dataQuery)
					cnx.commit()
				except UnboundLocalError:
					raise 'Update Error'
				hdrId = cursor.lastrowid
				docRef = "000000"+str(hdrId)
				docRef = "RCP"+docRef[-6:]
				dataQuery = "UPDATE receipt_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
				try:
					cursor.execute(dataQuery)
					cnx.commit()
				except UnboundLocalError:
					raise 'Update Error'
			else:
				hdrId = row[0]
				if (row[1] == "RCP000000"):
					docRef = "000000"+str(hdrId)
					docRef = "RCP"+docRef[-6:]
					dataQuery = "UPDATE receipt_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
					try:
						cursor.execute(dataQuery)
						cnx.commit()
					except UnboundLocalError:
						raise 'Update Error'
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)

	return hdrId

def body(cnx,ref,hdrId,partNumber, qty):
	bdyId = ""
	vendorId = ""
	try:
# Test for Existing Part
		partId=""
		cursor = cnx.cursor()
		dataQuery = "SELECT id, vendor_id 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]
		except UnboundLocalError:
			raise 'Update Error'
		
# Receipt Header Vendor Id	
		if (vendorId != ""):
			dataQuery = "UPDATE receipt_header SET vendor_id="+str(vendorId)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(hdrId)+" AND vendor_id IS null LIMIT 1"
			try:
				cursor.execute(dataQuery)
				cnx.commit()
			except UnboundLocalError:
				raise 'Update Error'
		
		dataQuery = "SELECT id FROM receipt_body WHERE receipt_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "INSERT INTO receipt_body (id,document_reference,product_type_id,part_number,advised_qty,received_qty,difference,part_id,receipt_header_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by)"
				if (partId == ""):
					dataQuery += " VALUES(DEFAULT,'"+ref+"',null,'"+partNumber+"',"+qty+",0,0,null,"+str(hdrId)+",1,now(),'system',now(),'system')"
				else:
					dataQuery += " VALUES(DEFAULT,'"+ref+"',null,'"+partNumber+"',"+qty+",0,0,"+str(partId)+","+str(hdrId)+",1,now(),'system',now(),'system')"
				try:
					cursor.execute(dataQuery)
					cnx.commit()
				except UnboundLocalError:
					raise 'Update Error'
				bdyId = cursor.lastrowid
			else:
				bdyId = row[0]
				if ((qty != "") and (qty is not None)):
					dataQuery = "UPDATE receipt_body SET advised_qty=advised_qty+"+qty+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(bdyId)+" LIMIT 1"
					try:
						cursor.execute(dataQuery)
						cnx.commit()
					except UnboundLocalError:
						raise 'Update Error'
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)

	return bdyId

def getts():
	ts = time.time()
	ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
	return ts

def getXls(wb,cnx):
	for sheet in wb.sheets():
		print ('Sheet:',sheet.name)
		c = 0
		i = 0
		prvRef = ""
# Headings
		for row in range(sheet.nrows):
			value  = (sheet.cell(row,0).value)
			try : 
				value = str(int(value))
			except : 
				pass
# Headings
			if value == "Delivery":
				continue
# Details
			ref  = (sheet.cell(row,0).value)
			try : ref = str(int(ref))
			except : pass
# Test for Date Format
			if sheet.cell(row, 12).ctype == 3: # 3 means 'xldate' , 1 means 'text'
				ms_date_number = (sheet.cell(row, 12).value)
# Convert Date to Timestamp
			year, month, day, hour, minute, second = xlrd.xldate_as_tuple(ms_date_number, wb.datemode)
			date = datetime.datetime(year, month, day, hour, minute, second)
#			try : date = str(int(date))					
#			except : pass
			partNo  = (sheet.cell(row,11).value)
			try : partNo = str(int(partNo))
			except : pass
			qty  = (sheet.cell(row,9).value)
			try :  qty = str(int(qty))
			except : pass
			
			print("Test "+ref+":"+partNo+":"+qty+":"+str(date))
# Receipt Header
			hdrId = header(cnx,ref,date)
# Receipt Body			
			bdyId = body(cnx,ref,hdrId,partNo,qty)
			
def main():
	ts = getts()
	print('Time:'+str(ts))
# open Excel Workbook
	file_path_xls = os.path.join(dir_path, '../vimsin/asn.xlsx')
	if os.path.isfile(file_path_xls):
		wb = xlrd.open_workbook(file_path_xls)
# Default Connection / System Settings
		defaults = default_setting.defaultSettings()
# mySql Connector
		cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
# read Xls & update Receipt
		getXls(wb,cnx)
	else:
		print ("File not Found - ",file_path_xls)
		
if __name__ == '__main__':
	main()