import os
import sys
import xlrd
import shutil
# import xlutils
import openpyxl
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.colors import RED, BLUE, GREEN, YELLOW, DARKGREEN

import datetime, time
from datetime import timedelta as td

redfill = PatternFill("solid", fgColor=RED)
bluefill = PatternFill("solid", fgColor=BLUE)
greenfill = PatternFill("solid", fgColor=GREEN)
yellowfill = PatternFill("solid", fgColor=YELLOW)
dgreenfill = PatternFill("solid", fgColor=DARKGREEN)
customcolors = {"RED": redfill, "GREEN": greenfill, "BLUE": bluefill, "YELLOW":yellowfill, "DARKGREEN":dgreenfill}

def file_list_(source_path, ext):
	eu = ext.upper()
	base_names = [s for s in os.listdir(source_path) if s.upper().endswith(eu)]
	abs_paths = [os.path.abspath(os.path.join(source_path, s)) for s in base_names]
	return base_names, abs_paths

def ensure_dir(f):
	try:
		os.makedirs(f)
	except OSError:
		if not os.path.isdir(f):
			raise

class outputData(object):  ## Updated by SDR
	def __init__(self, mpidName, ntg1, ntg2, Loss1, Loss2, munit1, munit2, comments, solutionCheck, color, comment):
		self.mpidName = mpidName
		self.ntg1 = ntg1
		self.ntg2 = ntg2
		self.Loss1 = Loss1
		self.Loss2 = Loss2
		self.munit1 = munit1
		self.munit2 = munit2
		self.comments = comments
		self.solutionCheck = solutionCheck
		self.color = color
		self.comment = comment

def writeResults(resultsData, outputWB, output_Final):
	wb_readonly = openpyxl.load_workbook(outputWB, data_only = True)
	# wb = openpyxl.load_workbook(outputWB, data_only = False)

	# w1 = wb.get_sheet_by_name("8(5) Volume Increase (MW)")
	# w2 = wb.get_sheet_by_name("8(4) Initial Losses (MW)")
	# w3 = wb.get_sheet_by_name("8(5) Redispatched Losses (MW)")
	
	# winfo = wb.get_sheet_by_name("Facility Info Details")
	winfo = wb_readonly.get_sheet_by_name("5(1) Locations")
	
	wsrd1 = wb_readonly.get_sheet_by_name("8(5) Volume Increase (MW)")
	wsrd2 = wb_readonly.get_sheet_by_name("8(4) Initial Losses (MW)")
	wsrd3 = wb_readonly.get_sheet_by_name("8(5) Redispatched Losses (MW)")
	
	
	winfo_max_row = 134
	mpids = []
	for row in range(2, winfo_max_row+1):
		cellindex = 'A' + str(row)
		temp_mpidname = winfo[cellindex].value
		if type(temp_mpidname) is int or type(temp_mpidname) is float:
			temp_mpidname = str(int(temp_mpidname))
		else:
			temp_mpidname = str(temp_mpidname)
		mpids.append(temp_mpidname)
	
	########### Write into 'NTG' Sheet #################
	def writeNTG():
		print "===Writing into NTG Sheet....."
		starting_col =  2
		ending_col = wsrd1.max_column-1
		date_row = 3
		# for col in range(starting_col, ending_col+1):
		for col in range(starting_col, 8762):
			cellIndex = openpyxl.utils._get_column_letter(col) + str(date_row )
			date = (wsrd1[cellIndex].value)
			# print date
			# raw_input()
			# date = roundTime(date, roundTo=60)
			# date = date.strftime("%Y/%m/%d %H:%M")
			# print(col, cellIndex, date)
			
			if date in resultsData.keys():
				# print date
				data = resultsData[date]
				value_row = 4
				for mpid in mpids:
					# print mpid
					# raw_input()
					cell = openpyxl.utils._get_column_letter(col) + str(value_row)
					for eachData in data:
						if eachData.mpidName == mpid:
							# print mpid
							# if mpid in data.keys():
							if eachData.color == "GREEN":
								# w1[cell] = eachData.ntg1 - eachData.ntg2
								wsrd1[cell] = eachData.ntg1 - eachData.ntg2
							else:
								# w1[cell] = ""
								wsrd1[cell] = ""
							# w1[cell].fill = customcolors[eachData.color]
							wsrd1[cell].fill = customcolors[eachData.color]
							break
					value_row += 1
						# else:
							# w1[cell] = 
							
	
	
	########### Write into 'Loss-1' Sheet - Loss 1 and MU1
	def writeLoss1(sum_ntgs):
		print "\n===Writing into Loss-1 Sheet....."
		starting_col =  2
		ending_col = wsrd2.max_column-1
		date_row = 2			
		# for col in range(starting_col, ending_col+1):
		for col in range(starting_col, 8762):
			cellIndex = openpyxl.utils._get_column_letter(col) + str(date_row)
			date = (wsrd2[cellIndex].value)
			# print wsrd2[cellIndex].fill
			# raw_input()
			
			# date = roundTime(date, roundTo=60)
			# date = date.strftime("%Y/%m/%d %H:%M")
			value_row = 3
			loss1_cell = openpyxl.utils._get_column_letter(col) + str(value_row)
			mu_cell = openpyxl.utils._get_column_letter(col) + str(value_row+1)
			totalNTG_cell = openpyxl.utils._get_column_letter(col) + str(value_row+2)
			if date in resultsData.keys():
				# print date
				data = resultsData[date]
				wsrd2[totalNTG_cell] = sum_ntgs[date]
				for eachData in data:
					# if eachData.color == "GREEN":
					# w2[loss1_cell] = eachData.Loss1
					wsrd2[loss1_cell] = eachData.Loss1
					# w2[mu_cell] = eachData.munit1
					wsrd2[mu_cell] = eachData.munit1
					# else:
						# w2[loss1_cell] = ""
						# w2[mu_cell] = ""
					# w2[loss1_cell].fill = customcolors["GREEN"]
					# wsrd2[loss1_cell].fill = customcolors["GREEN"]
					# w2[mu_cell].fill = customcolors["GREEN"]
					# wsrd2[mu_cell].fill = customcolors["GREEN"]
					break
				
	
	
	########### Write into 'Loss-2' Sheet########################
	def writeNtg_Loss2():
		print "\n===Writing into NTG and Loss-2 Sheet....."
		starting_col =  2
		# ending_col = wsrd3.max_column-1
		date_row = 3			
		sum_ntgs = dict()
		# for col in range(starting_col, ending_col+1):
		for col in range(starting_col, 8762):
			cellIndex = openpyxl.utils._get_column_letter(col) + str(date_row)
			date = (wsrd3[cellIndex].value)
			# date = roundTime(date, roundTo=60)
			# date = date.strftime("%Y/%m/%d %H:%M")
			# print(col, cellIndex, date)
			
			if date in resultsData.keys():
				# print date
				data = resultsData[date]
				lf_mpids = []
				sum_ntg = 0 
				
				for eachData in data:
					lf_mpids.append(eachData.mpidName)
				
				value_row = 4
				for mpid in mpids:
					cell = openpyxl.utils._get_column_letter(col) + str(value_row)
					if mpid in lf_mpids:
						if mpid == "CES2":
							mpid = "CES1"
						for eachData in data:
							if eachData.mpidName == mpid:
								# if mpid in data.keys():
								if eachData.color == "GREEN":
									# w3[cell] = eachData.Loss2
									wsrd3[cell] = eachData.Loss2
									wsrd1[cell] = eachData.ntg1 - eachData.ntg2
									sum_ntg += eachData.ntg1 - eachData.ntg2
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.comment
									wsrd1[cell] = ""
									# w3[cell] = ""
									# wsrd3[cell] = ""
								
								# w3[cell].fill = customcolors[eachData.color]
								# wsrd3[cell].fill = customcolors[eachData.color]
								break
					elif mpid == "CES2":
						for eachData in data:
							if eachData.mpidName == "CES1":
								# if mpid in data.keys():
								if eachData.color == "GREEN":
									# w3[cell] = eachData.Loss2
									wsrd3[cell] = eachData.Loss2
									wsrd1[cell] = eachData.ntg1 - eachData.ntg2
									sum_ntg += eachData.ntg1 - eachData.ntg2
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.comment
									wsrd1[cell] = ""
									# w3[cell] = ""
									# wsrd3[cell] = ""
								
								# w3[cell].fill = customcolors[eachData.color]
								# wsrd3[cell].fill = customcolors[eachData.color]
								break
					else:
						# wsrd3[cell] = "XA-MISSIN"
						wsrd3[cell] = "XS-MISSIN"
					
					value_row += 1
				sum_ntgs[date] = sum_ntg
			else:
				value_row = 4
				for mpid in mpids:
					cell = openpyxl.utils._get_column_letter(col) + str(value_row)
					wsrd3[cell] = "XS-UNSOL1"
					value_row += 1
		return sum_ntgs
	# writeNTG()
	# wb.save(output_Final + "_NTG.xlsx")

	
	# wb.save(output_Final + "_NTG_Loss1.xlsx")
	# raw_input("......")
	sum_ntgs = writeNtg_Loss2()
	writeLoss1(sum_ntgs)
	# wb.save(output_Final + ".xlsx")

	# wb.remove_sheet(w1)
	# wb.remove_sheet(w2)
	# wb.save(output_Final + "_Loss2.xlsx")
	# wb_readonly.save(output_Final + "_Loss2.xlsx")
	wb_readonly.save(output_Final + ".xlsx")
	
	
	
	
	
def progress(toPrint):
	# time.sleep(1)
	sys.stdout.write("\r%s" %toPrint)
	sys.stdout.flush()

def adjust_DST(fullresults, dst_day1, dst_day2):
	hour = "02"
	dst_day1 = dst_day1 + " 02:00"
	dst_day2 = dst_day2 + " 02:00"
	# date_time = date.strftime("%Y/%m/%d %H:%M")
	
	
	

def collectresultFiles(sourceDir):
	# sourceDir =  os.path.join(os.getcwd(), "State-2 Results")
	# sourceDir =  os.path.join(os.getcwd(), r"2017 LF Results")
	# sourceDir =  os.path.dirname(sourceDir)
	sourceDir =  os.path.normpath(sourceDir)
	print "LF Results Path : ", 
	print sourceDir

	roots = []
	
	for root, dirs, files in os.walk(sourceDir):
		roots.append(root)
		# print root

	resultfiles = []
	
	for root in roots:
		filenames, filepaths = file_list_(root, ".xls")
		for file in filepaths:
			# shutil.copy(file, outDir)
			resultfiles.append(file)

	print "Number of output files : ", len(resultfiles)
	
	# raw_input(" ========================== ")
	print " ========================== "
	resultfiles.sort()
	# for result in resultfiles:
		# print result
		# raw_input()
	return resultfiles	
			
def ilf_main(sourceDir, templateFile):
# def main():
	
	resultfiles = collectresultFiles(sourceDir)
	# # sourceDir = r"C:\Users\sdr\Desktop\ILF SW Simulation\10 April 2017\DEC\State-2 1-5 -----\Outputs-10Apr2017-17-05"
	# dir = r"Results"
	# filelocation = os.path.join(os.getcwd(), dir)
	# filenames, filepaths = file_list_(filelocation, ".xls")
	# print filepaths
	# # raw_input()
	
	
	fullresults = dict()
	redcounts = 0
	yellowcounts = 0
	greencounts = 0 
	if len(resultfiles) != 0:
		print "\nCollecting Results....."
		# for filename, filepath in zip(filenames, filepaths):
		for result in resultfiles:
			# excelFile = "01_DEC_2017_Hour_00.xls"
			tempfilename = os.path.basename(result).split(".xls")[0]
			[day, month, year, temp, hour] = tempfilename.split("_")
			# print day, month, year, temp, hour
			# raw_input()
			dt = day+" "+month+" "+year+" "+hour 
			date = datetime.datetime.strptime(dt, "%d %b %Y %H")
			date_time = date.strftime("%Y/%m/%d %H:%M")
			# print date_time
			
			progress(date_time)
			# raw_input()
			# ensure_dir(outDir)
			# print result
			wb = xlrd.open_workbook(result)
			ws = wb.sheet_by_name("Results")
			
			ilfresults = []
			
			for row in range(1, ws.nrows):
				# for col in range(0, sheet_xls.ncols):
				mpidName = ws.cell(row, 0).value
				ntg1 = ws.cell(row, 1).value
				ntg2 = ws.cell(row, 10).value
				Loss1 = ws.cell(row, 4).value
				Loss2 = ws.cell(row, 13).value
				munit1 = ws.cell(row, 5).value
				munit2 = ws.cell(row, 14).value
				comments = ws.cell(row, 21).value
				solutionCheck = ws.cell(row, 22).value
				# ntg = ''
				# if ntg2 != '-':
					# ntg = ntg1 - ntg2 
				# else:
					# ntg = ntg1
				
				# if Loss2 == '-':
					# Loss2 = ''
				# if munit2 == '-':
					# munit2 = 'N/A'
				
				# print comments
				colorCode = ""
				# if "Error Ocurred while preparing State-2" in comments:
					# color = "RED"
				# if "Initial NTG is less than 1 MW" in comments:
				if "NTG < 1 MW" in comments:
					colorCode = "YELLOW"
					yellowcounts += 1 
					comment = "XS-NODISP"
				elif "State-2 Successful" in comments:
					colorCode = "GREEN"
					greencounts += 1 
					# comment = "XS-NODISP"
				else:
					colorCode = "RED"
					redcounts += 1
					comment = "XS-UNSOL2"
				# print colorCode
				# print mpidName, ntg1, ntg2, Loss1, Loss2, munit1, munit2, comments, solutionCheck, colorCode
				
				# raw_input()
				ilfresults.append(outputData(mpidName, ntg1, ntg2, Loss1, Loss2, munit1, munit2, comments, solutionCheck, colorCode, comment))
			
			fullresults[date_time] = ilfresults
		
		# print "\nredcounts, yellowcounts, greencounts : ", redcounts, yellowcounts, greencounts
		
		# dst_day1 = "2017/3/12"
		# dst_day2 = "2017/11/05"
		# adjust_DST(fullresults, dst_day1, dst_day2)
		
		
		timeNow = datetime.datetime.now()
		timeNow = timeNow.strftime("%Y%b%d-%H%M")
		# outputWB = "2017 LF Results Template.xlsx"
		# outputWB = templateFile
		output_Final = "2017 LF Results - " + timeNow
		writeResults(fullresults, templateFile, output_Final)
	else:
		print "\nNo State-2 result files found"
	
	print "===================END OF PROGRAM===================="
	
	
# main()
	
	
	
	
	
	
	
	
	
	
	
	