import mysql.connector
import os
import time
import datetime
import xlwt   

import default_setting
import vimsmail

def inventory(defaults,reportpath):
	cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
	
	book = xlwt.Workbook(encoding="utf-8")
	sheet1 = book.add_sheet("Sheet 1")
	style1 = xlwt.XFStyle()
	style1.num_format_str = 'DD-MM-YYYY HH:MM:SS'

	sheet1.write(0,0, "Part_Number")
	sheet1.write(0,1, "Serial_Reference")
	sheet1.write(0,2, "Location_Code")
	sheet1.write(0,3, "Inventory_Qty")
	sheet1.write(0,4, "Allocated_Qty")
	sheet1.write(0,5, "Inventory_Status")
	sheet1.write(0,6, "Plt_Number")
	sheet1.write(0,7, "Stock_Date")
	sheet1.write(0,8, "Ran_Order")
	
	try:
		dataQuery = "SELECT inventory_master.serial_reference AS serial_reference, inventory_master.plt_number AS plt_number, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, inventory_master.date_created AS date_created, inventory_master.ran_order AS ran_order, inventory_status.status_code AS status_code, part.part_number AS part_number, location.location_code AS location_code FROM inventory_master LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN part ON inventory_master.part_id=part.id LEFT JOIN location ON inventory_master.location_id=location.id WHERE inventory_master.inventory_qty > 0"
		cursor = cnx.cursor(dictionary=True)
		cursor.execute(dataQuery)
		data = cursor.fetchall()
		r = 1
		for row in data:
			print (row['part_number'])
			print (row['serial_reference'])
			sheet1.write(r,0,row['part_number'])
			sheet1.write(r,1,row['serial_reference'])
			sheet1.write(r,2,row['location_code'])
			sheet1.write(r,3,row['inventory_qty'])
			sheet1.write(r,4,row['allocated_qty'])
			sheet1.write(r,5,row['status_code'])
			sheet1.write(r,6,row['plt_number'])
			sheet1.write(r,7,row['date_created'],style1)
			sheet1.write(r,8,row['ran_order'])
			r = r+1
			
	except UnboundLocalError:
		raise 'MySql Error'
		
	finally:
		try:
			cursor.close()
			cnx.close()
			if not os.path.isdir(reportpath):
				os.makedirs(reportpath)
			book.save(reportpath+"\\inventory.xls")
		except:
			pass
			
#	return defaults

if __name__ == '__main__':
	reportpath = "c:\\upload\\inventory"
	sender = "vims@vanteceurope.com"
	defaults = default_setting.defaultSettings()
	inventory(defaults,reportpath)
	recipients = vimsmail.getRecipients(defaults,"inventory")	
	vimsmail.createMail(reportpath, sender, recipients,"")
	
#	print(defaults)