import os
import time
import datetime
import json
from xlrd import open_workbook

def header(ref, date, time, dest, orderType):
	try:
# pad / format date/time
		date = '{message:{fill}{align}{width}}'.format(message=date,fill=0,align='>',width=8)
		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]
# pad / format header
		headerLine = "HD        NMUKBPOR"+'{message:{fill}{align}{width}}'.format(message=ref,fill=' ',align='<',width=10)+str(dateTime)+'{message:{fill}{align}{width}}'.format(message=dest,fill=' ',align='<',width=10)+str(orderType)+"\n"
		print(headerLine)
		return headerLine
		
	except: pass

def body(partNumber, qty, line):
	try:
# pad / format body
		bodyLine = "DT"+'{message:{fill}{align}{width}}'.format(message=partNumber,fill=' ',align='<',width=15)+'{message:{fill}{align}{width}}'.format(message=qty,fill=0,align='>',width=8)+'{message:{fill}{align}{width}}'.format(message=line,fill=0,align='>',width=6)+"\n"
		print(str(bodyLine))
		return str(bodyLine)
	except: pass

def footer():
	try:
# footer
		footerLine = "FT"+"\n"
		print(footerLine)
		return footerLine
	except: pass

def getts():
	ts = time.time()
	ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
	return ts
	
def main():
	if os.path.isfile('c:\\web\\veldb\\rdtnsk\\vimsinterface\\vimsin\\nmukbp.xlsx'):
		wb = open_workbook('c:\\web\\veldb\\rdtnsk\\vimsinterface\\vimsin\\nmukbp.xlsx')

#		ts = time.time()
#		ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S_1')
# of= open("c:\\python\\vimsin\\IDOCTRANIN.txt","w+")
		ts = getts()
		print('Time:'+str(ts))
		of= open("c:\\web\\veldb\\rdtnsk\\vimsinterface\\vimsin\\NMUKBP"+str(ts)+".txt","a")

		values = []
		for s in wb.sheets():
			print ('Sheet:',s.name)
			c = 0
			i = 0
			for row in range(s.nrows):
				value  = (s.cell(row,0).value)
				try : value = str(int(value))
				except : pass
				if (value.lower() == "ref"):
					if (c > 0):
						of.write(footer())
#						of.close()
						c = 0
					ref  = (s.cell(row,1).value)
					try : ref = str(int(ref))
					except : pass
					date  = (s.cell(row,3).value)
					try : date = str(int(date))					
					except : pass
					time  = (s.cell(row,5).value)
					try : time = str(int(time))
					except : pass
					dest  = (s.cell(row,7).value)
					try :  type = str(int(dest))
					except : pass
					type  = (s.cell(row,9).value)
					try :  type = str(int(type))
					except : pass
			
#					i = i+1
#					of= open("c:\\python\\vimsin\\NMUKBP"+str(ts)+str(i)+".txt","w+")
					of.write(header(ref, date, time, dest, type))
					c=1			
				else:
					if (c > 0):
						partNumber  = (s.cell(row,0).value)
						try : partNumber = str(int(partNumber))
						except : pass
						if (partNumber != "") and (partNumber.lower() != "part number")  and (partNumber.lower() != "part_number") and (partNumber.lower() != "partnumber"):
							qty  = (s.cell(row,1).value)
							try : qty = str(int(qty))
							except : pass
							line  = (s.cell(row,3).value)
							try : line = str(int(line))
							except : pass
				
							of.write(body(partNumber, qty, line))
				
			if (c > 0):
				of.write(footer())		
#				of.close()
		of.close()
	else:
		print ("File not Found - c:\\web\\veldb\\rdtnsk\\vimsinterface\\vimsin\\nmukbp.xlsx")
		
if __name__ == '__main__':
	main()