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

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

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        ORDER   "+'{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, ranOrder, 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=ranOrder,fill=' ',align='<',width=15)+'{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():
	file_path_xls = os.path.join(dir_path, '../vimsin/order.xlsx')
	if os.path.isfile(file_path_xls):
		wb = open_workbook(file_path_xls)

		ts = getts()
		print('Time:'+str(ts))
		file_path_txt = os.path.join(dir_path, '../vimsin/ORDER'+str(ts)+'.txt')
		of= open(file_path_txt,'a')

		values = []
# Loop through the Sheets
		for s in wb.sheets():
			print ('Sheet:',s.name)
			c = 0
			i = 0
			ranFlag = False;
# Loop through the Sheet Rows
			for row in range(s.nrows):
				value  = (s.cell(row,0).value)
				try : value = str(int(value))
				except : pass
# test for header row
				if (value.lower() == "ref"):
					if (c > 0):
						of.write(footer())
						c = 0
						ranFlag = False;
					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			
					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
# test for detail row
						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
							if (ranFlag == True):
								ranOrder  = (s.cell(row,2).value)
								try : ranOrder = str(int(ranOrder))
								except : pass
								line  = (s.cell(row,3).value)
								try : line = str(int(line))
								except : pass
							else:
								ranOrder  =''
								line  = (s.cell(row,2).value)
								try : line = str(int(line))
								except : pass
				
							of.write(body(partNumber, qty, ranOrder, line))
# test if RAN column is present
						else:
							if (s.cell(row,2).value.lower == "ran order") or (s.cell(row,2).value.lower == "ran_order") or (s.cell(row,2).value.lower == "ranorder"):
								ranFlag = True
										
			if (c > 0):
				of.write(footer())
		of.close()
	else:
		print ("File not Found - ",file_path_txt)
		
if __name__ == '__main__':
	main()