# 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,partNo):
	try:
		piId = 0
# connection
		cursor = cnx.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 pi reason	
		dataQuery = "SELECT id FROM pi_reason_code WHERE lower(short_code) = 'customer_rqst' LIMIT 1"
		cursor.execute(dataQuery) 
		row = cursor.fetchone()
		piStsId = row['id']
		
# Test for Existing Part
		dataQuery = "SELECT id FROM pi_header WHERE part_number='"+partNo+"' AND document_status_id="+str(documentStsId)+" LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "INSERT INTO pi_header (id,part_number,document_status_id,pi_reason_code_id,date_created,created_by,last_updated_date,last_updated_by)"
				dataQuery += " VALUES(DEFAULT,'"+partNo+"',"+str(documentStsId)+","+str(piStsId)+",now(),'system',now(),'system')"
				try:
					cursor.execute(dataQuery)
					cnx.commit()
					piId = cursor.lastrowid
					print("Inserted:",str(piId),":",partNo)
				except UnboundLocalError:
					raise 'Update Error'
				
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)

	return piId

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

def getXls(wb,cnx):
	prev = None
	for sheet in wb.sheets():
#	for sheet = wb.sheet_by_index(0)
		print ('Sheet:',sheet.name)
		if (sheet.name == "Archive"):
			break
		c = 0
		i = 0
# Headings
		for row in range(sheet.nrows):
			value  = (sheet.cell(row,0).value)
			try : 
				value = str(int(value))
			except : 
				pass
# Headings
			if value == "Part Number":
				continue
# Test for end
			if (value == '') or (value == None):
				if (prev == ''):
					break
				else:
					prev = ''
# Details
			partNo  = (sheet.cell(row,0).value)
			try : partNo = str(int(partNo))
			except : pass
			partNo = partNo.replace('-','')
			
#			print("Test ",partNo)
# Receipt Header
			piId = header(cnx,partNo)
			
def main():
	ts = getts()
	print('Time:'+str(ts))
# open Excel Workbook
	file_path_xls = os.path.join(dir_path, '../vimsin/piupload.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()